Skip to main content
Kofax

MarkView for Oracle - Query to find all attachments for an Oracle Invoice

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