Total Pageviews

July 21, 2016

7/21/2016 09:34:00 AM

Oracle Purchasing 11i to R12 upgrade


Which table holds the Requisition header and line attachments?


2. Using the SQL:

    select * from FND_DOCUMENTS
    where document_id=
     (select requisition_header_id
     from po_requisition_headers_all
     where segment1='<req_num>')

The above returns ID's but not the text written as attachment.  The table FND_DOCUMENTS_SHORT_TEXT requires and media_id; how to determine the value for this?

SOLUTION


1. Starting with Release 11i and continuing in Release 12, attachments are stored in FND tables


   FND_DOCUMENTS stores language independent information about a document.

   FND_DOCUMENTS_LONG_RAW stores images and OLE Objects, such as Word Documents
and Excel spreadsheets, in the database.

   FND_DOCUMENTS_LONG_TEXT stores information about long text documents.

   FND_ATTACHED_DOCUMENTS stores information relating a document to an application
entry.

  FND_DOCUMENTS_SHORT_TEXT stores information about short text documents.


Specifically for Requisitions, created in both Core Purchasing and iProcurement, the following apply:

FND_ATTACHED_DOCUMENTS.ENTITY_NAME = 'REQ_HEADERS'

FND_ATTACHED_DOCUMENTS.PK1_VALUE = PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID

Note that the values for column PK1_VALUE above is stored in FND_ATTACHED_DOCUMENTS as VARCHAR; this is important as PO schema stores REQUISITION_HEADER_ID column as NUMBER in PO_REQUISITION_HEADERS_ALL.

Using a test case example Requisition 14731, having REQUISITION_HEADER_ID = 142074, use the following SQL:

select * from FND_ATTACHED_DOCUMENTS
where PK1_VALUE = '12334'
and ENTITY_NAME = 'REQ_HEADERS';

For additional details on attachments, utilize the data from FND_ATTACHED_DOCUMENTS to query the other tables mentioned above.

2. The FND_DOCUMENTS_SHORT_TEXT.MEDIA_ID = fnd_documents_tl.media_id

The value can be obtained by running the following SQL:

select * from fnd_attached_documents where pk1_value=<requisition_header_id>;

select media_id from fnd_documents_tl where document_id = <document_id from 1st sql>;

select * from fnd_documents_short_text where media_id = <media_id from 2nd sql>;
 
Related Posts Plugin for WordPress, Blogger...