·
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>;
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>;
