Sunday 2 June 2013

How to use Hook to print custom PO Report???

When PO is submitted for approval , in the PDF generation flow standard XML is generated by the routine POXMLGEN in package PO_COMMUNICATION_PVT (file: POXVCOMB.pls). In order to support the custom extensions in the XML generation logic a new custom hook GENERATE_XML_FRAGMENT in package PO_CUSTOM_XMLGEN_PKG is added. This new procedure will be called from the standard XML generation logic - POXMLGEN. In order to have this hook available in the PO_COMMUNICATION_PVT the Patch set: 14677799:R12.PO.B [Metal ink ID: 1505737.1] should be available in environment.

The custom hook has the following logic:
-          Take the DOCUMENT_ID, REVISION_NUM, DOCUMENT_TYPE, and DOCUMENT_SUBTYPE as input.
-          Generate XML from the custom source under the tag PO_CUSTOM_DATA.
The returned XML will be merged into the standard XML and it will be sent to the publisher for rendering the template.

Guidelines

1.       Before extending the custom hook, check if the requirement can be met by the standard XML itself. One option could be to use the DFF attributes to capture the required attribute. It is recommended to use the standard XML attribute instead of extending the hook wherever applicable. In this case, the customer is trying to determine the Legal Entity name based on a different attribute (instead of using the default legal entity for that operating unit).
2.       Backup the custom hook source files. Modify the custom hook source files to generate the custom XML based on the custom source (refer the example below). The XML can be either hand built or generated using the dbms_xml routines. Make sure that
a)     the XML is complete (match the tags)
b)     No invalid characters in the XML
Exceptions are handled gracefully

Below is the sample code for generating Custom XML for Std PO 
   PROCEDURE generate_xml_fragment (
      p_document_id        IN              NUMBER,
      p_revision_num       IN              NUMBER,
      p_document_type      IN              VARCHAR2,
      p_document_subtype   IN              VARCHAR2,
      x_custom_xml         OUT NOCOPY      CLOB
   )
   IS
      --1). Declare context
      CONTEXT           DBMS_XMLGEN.ctxhandle;
      context1          DBMS_XMLGEN.ctxhandle;
      w_document_type   VARCHAR2 (1000);
   BEGIN
      -- Custom Function
      w_document_type := PO_COMMUNICATION_PVT.getdocumenttype ();

             --- Query for Standard PO
         CONTEXT := DBMS_XMLGEN.newcontext
               ('SELECT       
       TO_CHAR(ph.creation_date,''fmddth Month YYYY'')      xx_order_date,
       TO_CHAR(ph.start_date,''fmddth Month YYYY'')         xx_aggrement_start_date,
       TO_CHAR(ph.end_date,''fmddth Month YYYY'')           xx_aggrement_end_date ,
       DECODE
          (NVL (ph.bill_to_location_id, -1),
           -1, NULL,
           PO_COMMUNICATION_PVT.getlocationinfo (ph.bill_to_location_id)
          ) bill_to_location_id, 
       DECODE (NVL (ph.ship_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getlocationname ()
              )||chr(10) bill_to_location_name,
       ''Email : ''||pap1.email_address ap_email,       
       DECODE (NVL (ph.bill_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getaddressline1 ()
              )||chr(10) bill_to_address_line1,
       DECODE (NVL (ph.bill_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getaddressline2 ()
              )||chr(10) bill_to_address_line2,
       DECODE (NVL (ph.bill_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getaddressline3 ()
              )||chr(10) bill_to_address_line3,
       DECODE (NVL (ph.bill_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getaddressline4 ()
              ) bill_to_address_line4,
       DECODE (NVL (ph.bill_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getaddressinfo ()
              )||chr(10) bill_to_address_info,
       DECODE (NVL (ph.bill_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getterritoryshortname ()
              )||chr(10) bill_to_country,                   
       DECODE (NVL (ph.ship_to_location_id, -1),
           -1, NULL,
           PO_COMMUNICATION_PVT.getlocationinfo (ph.ship_to_location_id)
          ) ship_to_location_id,
       DECODE (NVL (ph.ship_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getaddressline1 ()
              )||chr(10) ship_to_address_line1,
       DECODE (NVL (ph.ship_to_location_id, -1),
               -1, NULL,
               PO_COMMUNICATION_PVT.getaddressline2 ()
              )||chr(10) ship_to_address_line2,
       CURSOR(SELECT xx_po_long_text_f(fad.media_id, ''SHORTTEXT'') xx_to_supplier_text 
                 FROM fnd_attached_docs_form_vl fad
                WHERE fad.pk1_value(+)     = TO_CHAR(ph.po_header_id)
                  AND entity_name          =''PO_HEADERS''
                  AND Category_Description =''To Supplier''
                  AND function_name        =''PO_POXPOEPO''
                  AND datatype_name        = ''Short Text''
              ORDER BY fad.seq_num ASC
               )                                                                       hdr_short_text,
       CURSOR (SELECT xx_po_long_text_f(fad.media_id, ''LONGTEXT'') xx_to_supplier_text
                 FROM fnd_attached_docs_form_vl fad
                WHERE fad.pk1_value(+)     = TO_CHAR(ph.po_header_id)
                  AND entity_name          = ''PO_HEADERS''
                  AND category_description = ''To Supplier''
                  AND function_name        = ''PO_POXPOEPO''
                  AND datatype_name        = ''Long Text''
              ORDER BY fad.seq_num ASC
               )                                                                        hdr_long_text,
       CURSOR(
              SELECT pl.line_num                                                        line_num, 
                     item_description                                                   item_description, 
                     (pl.note_to_vendor||chr(10))                                       line_note_to_vendor,
                     pl.unit_meas_lookup_code                                           unit_meas_lookup_code,
                     TRIM(TO_CHAR(pl.unit_price,''999,999,999,999.99''))                unit_price, 
                     TRIM(TO_CHAR(pl.quantity,''999,999,999,999.99''))                  quantity, 
                     pl.quantity_committed,
                     DECODE
                           (NVL (pll.ship_to_location_id, -1),
                            -1, NULL,
                            PO_COMMUNICATION_PVT.getlocationinfo (pll.ship_to_location_id)
                            )                                                           ship_to_location_id,
                     DECODE (NVL (pll.ship_to_location_id, -1),
                             -1, NULL,
                             PO_COMMUNICATION_PVT.getlocationname ()
                            )                                                           xx_ship_to_location_name,
                     DECODE (NVL (pll.ship_to_location_id, -1),
                             -1, NULL,
                             PO_COMMUNICATION_PVT.getaddressline1 ()
                            )                                                           xx_ship_to_address_line1,
                     DECODE (NVL (pll.ship_to_location_id, -1),
                             -1, NULL,
                             PO_COMMUNICATION_PVT.getaddressline2 ()
                            )                                                           xx_ship_to_address_line2,
                     DECODE (NVL (pll.ship_to_location_id, -1),
                             -1, NULL,
                             PO_COMMUNICATION_PVT.getaddressline3 ()
                            )                                                           xx_ship_to_address_line3,
                     DECODE (NVL (pll.ship_to_location_id, -1),
                             -1, NULL,
                             PO_COMMUNICATION_PVT.getaddressline4 ()
                            )                                                           xx_ship_to_address_line4,
                     DECODE (NVL (pll.ship_to_location_id, -1),
                             -1, NULL,
                             PO_COMMUNICATION_PVT.getaddressinfo ()
                            )                                                           xx_ship_to_address_info,
                     DECODE (NVL (pll.ship_to_location_id, -1),
                             -1, NULL,
                             PO_COMMUNICATION_PVT.getterritoryshortname ()
                            )                                                           xx_ship_to_country,
                     TO_CHAR (pl.committed_amount)                                      committed_amount,
                     TRIM(TO_CHAR((pl.QUANTITY * pl.unit_price),''999,999,999,999.99''))line_amount,
                     TO_CHAR (NVL (pll.need_by_date, pll.promised_date), ''dd/mm/yyyy'') due_date,
                     CURSOR (
                             SELECT xx_po_long_text_f(fad.media_id,''SHORTTEXT'') to_supplier_line_text
                               FROM fnd_attached_docs_form_vl fad
                              WHERE fad.pk1_value(+)     = TO_CHAR(pl.po_line_id)
                                AND entity_name          = ''PO_LINES''
                                AND category_description = ''To Supplier''
                                AND function_name        = ''PO_POXPOEPO''
                                AND datatype_name        = ''Short Text''
                           ORDER BY fad.seq_num ASC
                            ) line_short_text,
                     CURSOR (
                             SELECT xx_po_long_text_f(fad.media_id,''LONGTEXT'') to_supplier_line_text
                               FROM fnd_attached_docs_form_vl fad
                              WHERE fad.pk1_value(+)     = TO_CHAR(pl.po_line_id)
                                AND entity_name          = ''PO_LINES''
                                AND category_description = ''To Supplier''
                                AND function_name        = ''PO_POXPOEPO''
                                AND datatype_name        = ''Long Text''
                           ORDER BY fad.seq_num ASC
                            ) line_long_text
                FROM po_line_types_b       plt,
                     po_lines_all          pl,
                     po_line_locations_all pll
               WHERE pl.line_type_id           = plt.line_type_id
                 AND pl.po_line_id             = pll.po_line_id
                 AND ph.po_header_id           = pl.po_header_id
                 AND pll.po_header_id          = pl.po_header_id
                 AND pll.po_header_id          = ph.po_header_id
                 AND NVL(pl.cancel_flag,''N'') = ''N''
                 AND NVL(pll.cancel_flag,''N'')= ''N''
              ) as xx_lines
       FROM  po_headers_all              ph,
             hr_operating_units          hou,
             fnd_lookup_values           plc,
             ap_suppliers                vn,
             ap_supplier_sites_all       pvs,
             hr_all_organization_units   hao,
             hr_locations                hl,
             fnd_territories_vl          ftv,
             fnd_common_lookups          fcl,
             fnd_territories_tl          fte3,
             po_agents                   pa,
             fnd_currencies_tl           fcc,
             ap_terms                    at,
             apps.fnd_attached_documents fadf,
             apps.fnd_lobs               fl,
             fnd_documents               fd,
             per_phones                  pph,
             per_phones                  pph1,
             per_phones                  pph2,
             per_all_people_f            pap,
             per_all_assignments_f       paa,
             per_jobs                    pj,
             per_all_people_f            pap1,
             (
              SELECT fll2.attribute10, 
                     fll2.lookup_code ou_code
                FROM fnd_lookup_values fll2
               WHERE fll2.lookup_type   = ''xx_CUSTOM_LOOKUP''
                 AND fll2.enabled_flag  = ''Y''
                 AND TRUNC(SYSDATE)     BETWEEN TRUNC(fll2.start_date_active) 
                                           AND TRUNC(NVL(fll2.end_date_active, SYSDATE))
             ) ap_email,
             (
              SELECT fl3.lookup_code,
                     fl3.description
                FROM fnd_lookup_values fl3
               WHERE fl3.lookup_type     = ''xx_PO_PAY_ON_TO_PREFIX_MAP''
                 AND fl3.enabled_flag    = ''Y''
                 AND TRUNC(SYSDATE)      BETWEEN TRUNC(fl3.start_date_active) 
                                                AND TRUNC(NVL(fl3.end_date_active, SYSDATE))
              ) pay_lookup
       WHERE   ph.org_id                     = hou.organization_id
         AND   hou.name                      = plc.lookup_code(+)
         AND   plc.lookup_type(+)            =''xx_CUSTOM_LOOKUP''
         AND   NVL(plc.enabled_flag,''N'')   = ''Y''
         AND   TRUNC(SYSDATE)                BETWEEN NVL(plc.start_date_active,TRUNC(SYSDATE))
                                                    AND NVL(plc.end_date_active,TRUNC(SYSDATE))
         AND   vn.vendor_id                  = ph.vendor_id
         AND   pvs.vendor_site_id            = ph.vendor_site_id
         AND   hou.organization_id           = hao.organization_id
         AND   hao.location_id               = hl.location_id
         AND   hl.country                    = ftv.territory_code
         AND   hl.region_1                   = fcl.lookup_code(+)
         AND   fcl.lookup_type(+)            = ''IE_COUNTY''
         AND   fcl.language(+)               = USERENV(''LANG'')
         AND   NVL(fcl.enabled_flag,''N'')   = ''Y''
         AND   TRUNC(SYSDATE)                BETWEEN NVL(fcl.start_date_active,TRUNC(SYSDATE)) 
                                                    AND NVL(fcl.start_date_active,TRUNC(SYSDATE)) 
         AND   fcl.lookup_type               = ''IE_COUNTY''
         AND   pvs.country                   = fte3.territory_code(+)
         AND   fte3.language                 = USERENV(''LANG'')
         AND   pa.agent_id                   = ph.agent_id
         AND   fcc.currency_code             = ph.currency_code
         AND   fcc.language                  = USERENV(''LANG'')
         AND   ph.terms_id                   = at.term_id(+)
         AND   pay_lookup.lookup_code(+)     = pvs.pay_on_code
         AND   fadf.pk1_value(+)             = ph.agent_id
         AND   fd.document_id(+)             = fadf.document_id
         AND   fd.media_id                   = fl.file_id(+)
         AND   fadf.entity_name(+)           = ''Xxx_PO_BUYER_SIGNATURE''
         AND   pph.parent_id(+)              = pap.person_id
         AND   pap.person_id                 = ph.agent_id
         AND   pph.parent_table(+)           = ''PER_ALL_PEOPLE_F''
         AND   pph.phone_type(+)             = ''W1''
         AND   pph1.parent_id(+)             = pap.person_id
         AND   pph1.parent_table(+)          = ''PER_ALL_PEOPLE_F''
         AND   pph1.phone_type(+)            = ''M''
         AND   pph2.parent_id(+)             = pap.person_id
         AND   pph2.parent_table(+)          = ''PER_ALL_PEOPLE_F''
         AND   pph2.phone_type(+)            = ''WF''
         AND   ap_email.attribute10          = pap1.employee_number(+)
         AND   hou.name                      = ap_email.ou_code(+)
         AND   ph.authorization_status       = ''APPROVED''
         AND   NVL(ph.cancel_flag,''N'')     = ''N''
         AND   pap.person_id                 = paa.person_id
         AND   paa.job_id                    = pj.job_id
         AND   TRUNC (SYSDATE)               BETWEEN NVL(pap1.effective_start_date,TRUNC(SYSDATE)) 
                                                    AND NVL(pap1.effective_end_date,TRUNC(SYSDATE))
         AND   TRUNC (SYSDATE)               BETWEEN NVL(pap.effective_start_date,TRUNC(SYSDATE))  
                                                    AND NVL(pap.effective_end_date,TRUNC(SYSDATE))
         AND   TRUNC (SYSDATE)               BETWEEN NVL (pph.date_from, TRUNC (SYSDATE))   
                                                    AND NVL (pph.date_to, TRUNC (SYSDATE))
         AND   TRUNC (SYSDATE)               BETWEEN NVL (pph2.date_from, TRUNC (SYSDATE))  
                                                    AND NVL (pph1.date_to, TRUNC (SYSDATE))
         AND   TRUNC (SYSDATE)               BETWEEN NVL (pph2.date_from, TRUNC (SYSDATE))  
                                                    AND NVL (pph2.date_to, TRUNC (SYSDATE))                  
         AND   ph.po_header_id               = PO_COMMUNICATION_PVT.getDocumentId()
         AND   EXISTS (SELECT ''X''
                         FROM  fnd_user fu
                              ,po_agents buyer
                              ,per_all_people_f pap_buyer
                        WHERE  fu.user_id     = FND_GLOBAL.user_id
                          AND  fu.employee_id = buyer.agent_id
                          AND  buyer.agent_id = pap_buyer.person_id
                          AND  TRUNC (SYSDATE) BETWEEN NVL(pap_buyer.effective_start_date,TRUNC(SYSDATE)) 
                                                      AND NVL(pap_buyer.effective_end_date,TRUNC(SYSDATE))
        )'
        );
         --3). Set XML tag of the XML fragment for the result set
         DBMS_XMLGEN.setrowsettag (CONTEXT, 'PO_CUSTOM_DATA');
         --4). Set XML tag for each row of the result set
         DBMS_XMLGEN.setrowtag (CONTEXT, 'CUSTOM_PO');
         DBMS_XMLGEN.setconvertspecialchars (CONTEXT, TRUE);
         --5). Call dbms_xmlgen to get XML and assign it to output CLOB
         x_custom_xml := DBMS_XMLGEN.getxml (CONTEXT, DBMS_XMLGEN.NONE);
         DBMS_XMLGEN.closecontext (CONTEXT);

2 comments:

  1. Can you share the script of the function xx_po_long_text_f

    ReplyDelete
  2. Can you share the XML generated by the above sql

    ReplyDelete