Sunday, March 31, 2019

Query to View or Download Purchase Order PDF from Database in R12














As you all know, the “PO Output for Communication” is responsible for generating the PO PDF based on the default template configured at transaction types. Post generating the PO PDF, it get stored in database like an attachment mapped to respective PO_HEADER_ID. 

So,we can use the below query to check/download the PO PDF from database. This helps for debugging where we don't have access to front end. 

I tested the query in R12.24 instance

Query:


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 = 84
    ) a;
 
Sample Output:


Hope its helps!!

0 Responses to “Query to View or Download Purchase Order PDF from Database in R12”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.