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

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