MarkView for Oracle - Query to find all attachments for an Oracle Invoice
3977
Summary
3977
Applies To
- ERP System: Oracle
- MarkView Version: All Versions
Behavior
- In certain cases a direct database query may need to be performed in order to provide a listing of all attachments associated with an Oracle Invoice.
Resolution
- Oracle Applications 11i:
select aia.invoice_id, substr(aia.invoice_num, 1, 20) invoice_num, substr(to_char(aia.creation_date, 'dd-mon-yy hh:mi:ssam'), 1, 20) invoice_creation_date, to_number(fdt.file_name) mv_document_id, substr(to_char(fdt.creation_date, 'dd-mon-yy hh:mi:ssam'), 1, 20) attachment_creation_date, fu.user_id created_by, fu.user_name created_by_user_name, ppv.workitem_instance_id, ppv.property_name, ppv.property_value, wi.workflow_name, wi.queue_name from ap_invoices_all aia, fnd_attached_documents fad, fnd_documents_tl fdt, fnd_user fu, mv_document d, sf_workitem_property_values_v ppv, sf_workitem_info wi where aia.invoice_id = &InvoiceID and to_char(aia.invoice_id) = fad.pk1_value and fad.entity_name = 'AP_INVOICES' and fad.document_id = fdt.document_id and fdt.created_by = fu.user_id and fdt.file_name = to_char(d.document_id) and fdt.file_name = ppv.property_value and ppv.property_name = 'MVDocumentID' and ppv.workitem_instance_id = wi.workitem_instance_id(+) and fdt.language = USERENV('LANG') order by ppv.workitem_instance_id, fdt.file_name;
- Oracle Applications R12
select aia.invoice_id, substr(aia.invoice_num, 1, 20) invoice_num, substr(to_char(aia.creation_date, 'dd-mon-yy hh:mi:ssam'), 1, 20) invoice_creation_date, to_number(fd.file_name) mv_document_id, substr(to_char(fd.creation_date, 'dd-mon-yy hh:mi:ssam'), 1, 20) attachment_creation_date, fu1.user_id created_by, fu1.user_name created_by_user_name, ppv.workitem_instance_id, ppv.property_name, ppv.property_value, wi.workflow_name, wi.queue_name from sf_workitem_info wi, sf_workitem_property_values_v ppv, ap_invoices_all aia, mvt_document_workitem_instance dwi, fnd_attached_documents fad, fnd_documents fd, fnd_user fu1, fnd_user fu2 where ppv.PROPERTY_NAME = 'InvoiceID' and ppv.WORKITEM_INSTANCE_ID = wi.WORKITEM_INSTANCE_ID and to_char(aia.invoice_id) = ppv.PROPERTY_VALUE and dwi.workitem_instance_id = wi.WORKITEM_INSTANCE_ID and fad.pk1_value = to_char(aia.invoice_id) and fad.document_id = fd.document_id and fad.created_by = fu1.user_id and fd.created_by = fu2.user_id and aia.invoice_id = &InvoiceID;
Keywords: attachment, fnd_document, fnd_attached_document