Oracle Apps Knowledge Sharing

SELECT a.media_id,

       A.ou_short_code,

       a.po_header_id,

       PR_NUM,

       po_num,

       'application/pdf' content_type,

       file_name po_pdf_file_name,

       revision_num po_revision_num,

       (SELECT l.file_data

          FROM fnd_lobs  l

         WHERE l.file_id                = a.MEDIA_ID         

       ) PDF      

  FROM (SELECT DISTINCT

              hou.short_code ou_short_code,

              pha.po_header_id,

              prha.segment1 pr_num,

              pha.segment1 po_num,

              pha.revision_num,

              d.media_id,

              d.file_name

         FROM po_headers_archive_all pha,

              hr_operating_units hou,

              po_line_locations_all pll,

              po_distributions_all pd,

              po_requisition_lines_all prla,

              po_requisition_headers_all prha,

              po_req_distributions_all prda,

              fnd_document_datatypes dat,

              fnd_documents d,

              fnd_attached_documents ad      

        WHERE hou.organization_id      = pha.org_id

          AND pha.type_lookup_code     = 'STANDARD'

          AND pha.po_header_id           = pll.po_header_id

          AND pll.line_location_id       = prla.line_location_id

          AND pd.po_header_id            = pha.po_header_id

          AND pd.po_line_id              = pll.po_line_id

          AND prha.requisition_header_id = prla.requisition_header_id

          AND prla.requisition_line_id   = prda.requisition_line_id

          AND pd.req_distribution_id     = prda.distribution_id

          AND d.document_id              = ad.document_id

          AND d.datatype_id            = dat.datatype_id         

          AND dat.name                 = 'FILE'

          AND ad.pk1_value             = pha.po_header_id

          AND ad.pk2_value             = pha.revision_num

          AND ad.entity_name           = 'PO_HEAD'

          AND D.file_name like '%'||pha.segment1||'%'

          AND dat.language = 'US'

          AND pha.org_id = 840

    ) a;