, ,

Attachment in Oracle Application

What is attachment in oracle application?
The attachments feature in oracle application enables users to link unstructured data, such as images, word-processing documents, spreadsheets, or text to their application data. For example, users can link images to items or video to operations as operation instructions.
Where to find an attachment?
There is an attachment icon in the oracle application toolbar that indicates whether the Attachments feature is enabled in a form block. When the button is dimmed, the Attachment feature is not available. When the Attachment feature is enabled in a form block, the icon becomes a solid paper clip. The icon switches to a paper clip holding a paper when the Attachment feature is enabled in a form lock and the current record has at least one attachment.
Attachment types:
An attached document can be:
1] Short Text
Text stored in the database containing less than 2000 characters.
2] Long Text
Text stored in the database containing 2000 characters or more.
3] Image
An image that Oracle Forms can display, including: bmp, cals, jfif, jpeg, gif, pcd, pcx, pict, ras, and tif.
4] OLE Object
An OLE Object that requires other OLE server applications to view, such as Microsoft Word or Microsoft Excel.
5] Web Page
A URL reference to a web page which you can view with your web browser.

Tables Involved:
For Importing Attachments in oracle application one has to populate following tables.
1. FND_DOCUMENTS
2. FND_ATTACHED_DOCUMENTS
3. FND_DOCUMENTS_TL
4. FND_DOCUMENT_DATATYPES.
5. FND_DOCUMENT_CATEGORIES
6. FND_DOCUMENTS_LONG_TEXT (Long text type attachment).
7. FND_DOCUMENTS_SHORT_TEXT (Short text type attachment).
8. FND_DOCUMENTS_LONG_RAW
9. FND_LOBS (File type attachments).
FND_DOCUMENTS:
FND_DOCUMENTS stores language-independent information about a document. For example, each row contains a document identifier, a category identifier, the method of security used for the document (SECURITY_TYPE, where 1=Organization,2=Set of Books, 3=Business unit,4=None), the period in which the document is active, and a flag to indicate whether or not the document can be shared outside of the security type (PUBLISH_FLAG).
Other specifications in this table include: datatype (DATATYPE_ID, where 1=short text,2=long text, 3=image, 4=OLE object), image type, and storage type (STORAGE_TYPE, where 1=stored in the database, 2=stored in the file system).
The document can be referenced by many application entities and changed only in the define document form (USAGE_TYPE=S); it can be used as a fill-in-the-blanks document, where each time you use a template, you make a copy of it (USAGE_TYPE=T); or it can be used only one time (USAGE_TYPE=O).Images and OLE Objects cannot be used as templates.
FND_ATTACHED_DOCUMENTS:
FND_ATTACHED_DOCUMENTS stores information relating a document to an application entity.  For example, a record may link a document to a sales order or an item. Each row contains foreign keys to FND_DOCUMENTS and FND_DOCUMENT_ENTITIES. There is also a flag to indicate whether or not an attachment was created automatically.
FND_DOCUMENTS_TL:
FND_DOCUMENTS_TL stores translated information about the documents in FND_DOCUMENTS. Each row includes the document identifier, the language the row is translated to, the description of the document, the file in which the image is stored, and an identifier (MEDIA_ID) of the sub-table in which the document is saved (FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT, or FND_DOCUMENTS_LONG_RAW).
FND_DOCUMENT_DATATYPES:
FND_DOCUMENT_DATATYPES stores the document datatypes that are supported. Initial values are: short text, long text, image, and OLE Object (DATATYPE_ID=1, 2, 3, or 4). Customers can add datatypes to handle documents stored outside of Oracle and use non-native Forms applications to view/edit their documents. The table uses a “duplicate record” model for handling multi-lingual needs. That is, for each category there will be one record with the same CATEGORY_ID and CATEGORY_NAME for each language.
FND_DOCUMENT_CATEGORIES:
FND_DOCUMENT_CATEGORIES stores information about the categories in which documents are classified. For example, documents may be considered “Bill of Material Comments”, “WIP Job Comments”, etc. Document categories are used to provide a measure of security on documents. Each form that enables the attachment feature lists which categories of documents can be viewed in the form. This table uses a “duplicate record” model for handling multi-lingual needs.
FND_DOCUMENTS_LONG_TEXT:
FND_DOCUMENTS_LONG_TEXT stores information about long text documents.
FND_DOCUMENTS_SHORT_TEXT:
FND_DOCUMENTS_SHORT_TEXT stores information about short text documents.
FND_DOCUMENTS_LONG_RAW:
FND_DOCUMENTS_LONG_RAW stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database.
FND_DOCUMENT_ENTITIES:
FND_DOCUMENT_ENTITIES lists each entity to which attachments can be linked. For example, attachments can be linked to Items, Sales Orders, etc. Since the table uses a “duplicate record” model for handling multi-lingual needs, for each document entity there will be one record with the same DOCUMENT_ENTITY_ID and DATA_OBJECT_CODE for each language.

Queries:
1] To find all Long Text attachments:

SELECT
        FAD.SEQ_NUM “Seq Number”,
        FDAT.USER_NAME “Data Type”,
        FDCT.USER_NAME “Category User Name”,
        FAD.ATTACHED_DOCUMENT_ID “Attached Document Id”,
        FDET.USER_ENTITY_NAME “User Entity”,
        FD.DOCUMENT_ID “Document Id”,
        FAD.ENTITY_NAME “Entity Name”,
        FD.MEDIA_ID “Media Id”,
        FD.URL “Url”,
        FDT.TITLE “Title”,
        FDLT.LONG_TEXT “Attachment Text”
FROM
        FND_DOCUMENT_DATATYPES FDAT,
        FND_DOCUMENT_ENTITIES_TL FDET,
        FND_DOCUMENTS_TL FDT,
        FND_DOCUMENTS FD,
        FND_DOCUMENT_CATEGORIES_TL FDCT,
        FND_ATTACHED_DOCUMENTS   FAD,
        FND_DOCUMENTS_LONG_TEXT FDLT
WHERE
        FD.DOCUMENT_ID          = FAD.DOCUMENT_ID
        AND FDT.DOCUMENT_ID     = FD.DOCUMENT_ID
        AND FDCT.CATEGORY_ID    = FD.CATEGORY_ID
        AND FD.DATATYPE_ID      = FDAT.DATATYPE_ID
        AND FAD.ENTITY_NAME     = FDET.DATA_OBJECT_CODE
        AND FDLT.MEDIA_ID       = FD.MEDIA_ID
        AND FDAT.NAME           = ‘LONG_TEXT’;

 

2] To find all Short Text attachments:

SELECT
        FAD.SEQ_NUM “Seq Number”,
        FDAT.USER_NAME “Data Type”,
        FDCT.USER_NAME “Category User Name”,
        FAD.ATTACHED_DOCUMENT_ID “Attached Document Id”,
        FDET.USER_ENTITY_NAME “User Entity”,
        FD.DOCUMENT_ID “Document Id”,
        FAD.ENTITY_NAME “Entity Name”,
        FD.MEDIA_ID “Media Id”,
        FD.URL “Url”,
        FDT.TITLE “Title”,
        FDST.SHORT_TEXT “Attachment Text”
FROM
        FND_DOCUMENT_DATATYPES FDAT,
        FND_DOCUMENT_ENTITIES_TL FDET,
        FND_DOCUMENTS_TL FDT,
        FND_DOCUMENTS FD,
        FND_DOCUMENT_CATEGORIES_TL FDCT,
        FND_ATTACHED_DOCUMENTS   FAD,
        FND_DOCUMENTS_SHORT_TEXT FDST
WHERE
        FD.DOCUMENT_ID          = FAD.DOCUMENT_ID
        AND FDT.DOCUMENT_ID     = FD.DOCUMENT_ID
        AND FDCT.CATEGORY_ID    = FD.CATEGORY_ID
        AND FD.DATATYPE_ID      = FDAT.DATATYPE_ID
        AND FAD.ENTITY_NAME     = FDET.DATA_OBJECT_CODE
        AND FDST.MEDIA_ID       = FD.MEDIA_ID
        AND FDAT.NAME           = ‘SHORT_TEXT’;

Attachment upload through API:
Attachments can also be uploaded through an oracle provided API called  FND_ATTACHED_DOCUMENTS_PKG.
It consist of three procedures
1)  Insert Row
2)  Update Row
3)  Lock Row
Names of these procedures are self explanatory. insert row is used to insert a new row for attachment data, update row is used to update existing row for a particular row and Lock Row is used to lock a existing row for further modification.
 

, , ,

Few Important AP Tables

AP_SUPPLIERS:

  • This table replaces the old PO_VENDORS table.
  • It stores information about your supplier level attributes.
  • Each row includes the purchasing, receiving, invoice, tax, classification, and general information.
  • Oracle Purchasing uses this information to determine active suppliers.
  • The supplier name, legal identifiers of the supplier will be stored in TCA and a reference to the party created in TCA will be stored in AP_SUPPLIERS.PARTY_ID, to link the party record in TCA.

AP_SUPPLIER_SITES_ALL:

  • This table replaces the old PO_VENDOR_SITES_ALL table.
  • It stores information about your supplier site level attributes.
  • There is a row for unique combination of supplier address, operating unit and the business relationship that you have with the supplier.
  • The supplier address information is not maintained in this table and is maintained in TCA. The reference to the internal identifier of address in TCA will be stored in AP_SUPPLIER_SITES_ALL.LOCATION_ID, to link the address record in TCA.
  • Each row includes the supplier reference, purchasing, invoice, and general information.

AP_INVOICES_ALL:

  • It contains records for invoices you enter.
  • There is one row for each invoice you enter.
  • An invoice can have one or more invoice distribution lines and can have one or more scheduled payments.  

AP_INVOICE_LINES_ALL:

  • It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
  • An invoice can have one or more invoice lines.
  • An invoice line represents goods (direct or indirect materials), service(s), and/or associated tax/freight/miscellaneous charges invoiced from a supplier.
  • An invoice line should contain all the attributes that are present on the physical or electronic invoice presented by the supplier.

AP_INVOICE_DISTRIBUTIONS_ALL:

  • It holds the distribution information that is manually entered or system-generated.
  • There is one row for each invoice distribution and a distribution must be associated with an invoice.
  • An invoice can have multiple distributions.

AP_INVOICE_PAYMENTS_ALL:

  • It contains records of invoice payments that you made to suppliers.
  • There is one row for each payment you make for each invoice and there is one payment and one invoice for each payment in this table.
  • Oracle Payables application updates this table when you confirm an automatic payment batch, enter a manual payment, or process a Quick payment.
  • When you void a payment, your Oracle Payables inserts an additional payment line that is the negative of the original payment line.

AP_PAYMENT_SCHEDULES_ALL:

  • This table stores information about scheduled payment information on invoices.

AP_PAYMENT_HISTORY_ALL:

  • It stores the clearing/unclearing history for payments.
  • It also stores the maturity history for future dated payments.
  • The table contains a row for each future dated payment, once the future dated payment matures, i.e. becomes negotiable.
  • Any time a payment is cleared or uncleared, a row is inserted into this table for the payment.

AP_BATCHES_ALL:

  • It contains summary information about invoices you enter in batches if you enable the Batch Control Payables option.
  • There is one row for each batch of invoices you enter.
  •  If you enable Batch Control, each invoice must correspond to a record in this table.
  •  Your Oracle Payables application uses this information to group together invoices that one person entered in a batch.

AP_CHECKS_ALL:

  • It stores information about payments issued to suppliers or refunds received from suppliers. 
  • There is one row for each payment you issue to a supplier or refund received from a supplier.
  • Oracle Payables application uses this information to record payments you make to suppliers or refunds you receive from suppliers.
  • Oracle Payables application stores the supplier name and bank account name for auditing purposes, in case either one is changed after you create the payment. Oracle Payables application also stores address information for all payments.

AP_HOLDS_ALL:

  • It contains information about holds that you or your Oracle Payables application place on an invoice.
  • For non-matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice-shipment match.
  • An invoice may have one or more corresponding rows in this table.
  • Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table.

AP_BANK_ACCOUNTS_ALL:

  • It contains information about your bank accounts.
  • There is one row for each bank account you define and each bank account must be affiliated with one bank branch.

AP_BANK_ACCOUNT_USES_ALL:

  • It stores information for the internal and external bank accounts you define in Oracle Payables and Oracle Receivables applications.

AP_CARDS_ALL:

  • It stores information about the corporate credit cards issued to your employees by your corporate credit card providers.

AP_TRIAL_BALANCE:

  • It contains denormalized information about invoices and payments posted to the accrual set of books.
, ,

Script to get all the Concurrent Program Request details

Below is the script to get the Concurrent Program Request details by Various Users in a Particular Day.

select
request_id,
parent_request_id,
fcpt.user_concurrent_program_name Request_Name,
fcpt.user_concurrent_program_name program_name,
DECODE(fcr.phase_code,
‘C’,’Completed’,
‘I’,’Incactive’,
‘P’,’Pending’,
‘R’,’Running’) phase,
DECODE(fcr.status_code,
‘D’,’Cancelled’,
‘U’,’Disabled’,
‘E’,’Error’,
‘M’,’No Manager’,
‘R’,’Normal’,
‘I’,’Normal’,
‘C’,’Normal’,
‘H’,’On Hold’,
‘W’,’Paused’,
‘B’,’Resuming’,
‘P’,’Scheduled’,
‘Q’,’Standby’,
‘S’,’Suspended’,
‘X’,’Terminated’,
‘T’,’Terminating’,
‘A’,’Waiting’,
‘Z’,’Waiting’,
‘G’,’Warning’,’N/A’) status,
round((fcr.actual_completion_date – fcr.actual_start_date),3) * 1440 as Run_Time,
round(avg(round(to_number(actual_start_date – fcr.requested_start_date),3) * 1440),2) wait_time,
fu.User_Name Requestor,
fcr.argument_text parameters,
to_char (fcr.requested_start_date, ‘MM/DD HH24:mi:SS’) requested_start,
to_char(actual_start_date, ‘MM/DD/YY HH24:mi:SS’) ACT_START,
to_char(actual_completion_date, ‘MM/DD/YY HH24:mi:SS’) ACT_COMP,
fcr.completion_text
From
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
Where 1=1
— and fu.user_name = ‘DJKOCH’ ‘
— and fcr.request_id = 1565261
— and fcpt.user_concurrent_program_name = ‘Payables Open Interface Import”
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcpt.application_id
and fcr.requested_by = fu.user_id
and fcpt.language = ‘US’
and fcr.actual_start_date like sysdate
— and fcr.phase_code = ‘C’
— and hold_flag = ‘Y’
— and fcr.status_code = ‘C’
GROUP BY
request_id,
parent_request_id,
fcpt.user_concurrent_program_name,
fcr.requested_start_date,
fu.User_Name,
fcr.argument_text,
fcr.actual_completion_date,
fcr.actual_start_date,
fcr.phase_code,
fcr.status_code,
fcr.resubmit_interval,
fcr.completion_text,
fcr.resubmit_interval,
fcr.resubmit_interval_unit_code,
fcr.description
Order by 1 desc;