Oracle ATP based on Collected Data - Challenges

Like many organizations, we are also using Oracle eBusiness Release 12.2, more specifically 12.2.7. But, as per Oracle we are far behind from latest stable release. Because of on-going priority tasks, we cannot upgrade our system, thus missing many critical bug fixes as well as productivity and performance fixes. 

If your application is recording high volume transactions and not using Oracle Advanced Supply Chain Planning (ASCP) but using ATP for Sales Orders Scheduling and Global Order Promising, then you are in the same boat like us. We have third party cloud-based planning application called O9. Demand and Supply planning are performed there based on the collected data from multiple ERP systems including Oracle eBS. Though we are not using Oracle Configurator for configured items, we have developed a home-grown Item on the Fly (IOTF) solution which creates an item based on the user preferences during Order Entry. This along with multiple warehouses across the globe created millions of items in our system. 

Since, we want to provide customers most accurate planned schedule ship date, we are using ATP. To schedule these newly created items, we have the requirement to run ATP Data Collection and Schedule Order Program quite frequently. On the other hand, 90% of our orders are fulfilled within a week or so. This means that if we do not run ATP Data Collection for Complete Refresh quite frequently then MSC demand table entries become obsolete. These two dual critical requirements have put us in a challenging situation. 

Our installation is centralized, meaning data is collected from transactional system and ATP calculation is done on the same system. Overall process could be depicted by simple picture like below

When eBS is first installed, based on the selected options and products licensed Oracle creates the framework, associated database objects and programs to collect and process data. The objects and associated programs are provisioned by series of setup requests. Once setup is done then to calculate availability and derive fulfillment warehouse, master and transactional data need to be collected based on ATP rules. Fundamentally there are two processes for collecting data.

1. Net Change Refresh - This is Collecting the changed records. It is accomplished by identifying, recording and subsequently processing the changed records in an efficient way. Oracle does it by using Database mechanism - MLOG tables. These are Materialized View Log tables associated with base eBusiness master and transactional tables like OE_ORDER_LINES_ALL, MTL_SYSTEM_ITEMS_B, MTL_TXN_REQUEST_LINES etc. When Net Change Refresh is performed, Oracle scans these MLOG tables and then pushes them to Snapshot tables or Materialized Views (MV). Refresh Collection Snapshot program is the one which performs this activity. Once done, the processed records are deleted from MLOG tables. 
2. Complete Refresh - Collecting fresh set of data from Oracle base application tables based on the criteria defined by Oracle. These criteria are maintained in Snapshot tables which are nothing but Materialized Views. Each of the Materialized Views is having an underlying SQL Query with filter criteria. Complete refresh bypasses MLOG tables. It refreshes the snapshot tables (MVs) by running the query. Again, same Refresh Collection Snapshot program does this job. Once done, it then deletes the MLOG tables to mark the starting point for changed records.

When data are pushed to Snapshot tables, ATP Data Pull program then submits Planning Data Pull Workers to select data from Snapshot tables and dump them to Staging tables. Finally, Planning ODS Load program processes these data from Staging Tables to MSC base tables for ATP calculation.

So, you can imagine that if your system is processing high volume orders/items/transactions every day, the MLOG tables will start growing exponentially if they are not deleted frequently either by Net Change or Complete Refresh. Since underlying base tables are also, huge and Oracle brings almost everything using snapshot queries, the processing data set for Complete Refresh will also be humongous. Most customers face performance issues with ATP Data Pull program especially with Order Line, System Items and BOM Components Snapshot.

To address these challenges, we have to come up with optimum MLOG table size and ATP Data Pull program frequency. There is no magic number for size and frequency, it varies based on volume of transactions and business requirements. Oracle recommends keeping MLOG size below 10MB. So, the first task is to monitor the growth of these tables. MOS document - How To Improve the Performance of the Refresh Collection Snapshots When Running Very High Volumes (550005.1) has detailed steps and scripts to monitor MLOG growth.

As we fall into this category, we created automated processing using DBMS jobs to run the scripts and collect data in a custom table. For continuous monitoring, we have developed a simple user interface using Oracle Application Express (APEX). I will publish the detailed steps to create simple productive UI.

Below are the statistics of MLOG and Snapshot tables from our system.

MLOG Tables

Snapshot Tables

Snapshot Table Growth over time

Clearly you can see from above pictures that there are couple of MLOG tables which are growing very fast. Few Snapshot tables have become extremely large of the order of GB. This is why our Net Change and Complete Refresh program processes are extremely slow and sometimes never complete.

When started to analyze requests submitted by ATP Data Pull, we found that Refresh Collection Snapshot for Order Lines OE_ODR_LINES_SN is taking hours to complete. For us this was the source of performance bottleneck. This snapshot table is storing millions of order lines including closed one. Even though profile MSC: Sales Orders Offset Days, collects closed sales order within the offset days, it does not stop ATP Data Pull program from bringing closed lines from OE_ORDER_LINES_ALL table. The profile value is only considered when populating data from Staging table to MSC base table -  MSC_SALES_ORDERS. 

Our first goal was to find a way to bring sales order lines that we need to calculate ATP. Since, we were not using ASCP, we verified that only open Sales Order demand is needed. As mentioned earlier that Snapshots are the ones which collect data from application tables. To find the snapshot, MLOG table names simply run the below query

SELECT DISTINCT
amdr.MVIEW_NAME "Snapshot",
amdr.OWNER "Snapshot Owner",
amdr.DETAILOBJ_NAME "Base Table Name",
amdr.DETAILOBJ_OWNER "Base Table Owner",
log_table mlog$_name
FROM ALL_MVIEW_DETAIL_RELATIONS amdr,
dba_snapshot_logs dsl
where DETAILOBJ_TYPE = 'TABLE'
and (detailobj_owner, detaiLobj_name) not in (('MSC','MSC_COLLECTED_ORGS'))
AND amdr.DETAILOBJ_NAME = dsl.MASTER
and DETAILOBJ_NAME in
('AHL_SCHEDULE_MATERIALS','BOM_COMPONENTS_B','BOM_CTO_ORDER_DEMAND','BOM_DEPARTMENTS','BOM_OPERATIONAL_ROUTINGS','BOM_OPERATION_NETWORKS',
'BOM_OPERATION_RESOURCES','BOM_RESOURCE_CHANGES','BOM_RES_INSTANCE_CHANGES','BOM_STRUCTURES_B','BOM_SUBSTITUTE_COMPONENTS',
'BOM_SUB_OPERATION_RESOURCES','CSP_REPAIR_PO_HEADERS','EAM_WO_RELATIONSHIPS','MRP_FORECAST_DATES','MRP_FORECAST_DESIGNATORS','MRP_FORECAST_ITEMS',
'MRP_SCHEDULE_DATES','MTL_DEMAND','MTL_ITEM_CATEGORIES','MTL_MATERIAL_TRANSACTIONS_TEMP','MTL_ONHAND_QUANTITIES_DETAIL','MTL_RELATED_ITEMS',
'MTL_RESERVATIONS','MTL_SUPPLY','MTL_SYSTEM_ITEMS_B','MTL_TXN_REQUEST_LINES','MTL_USER_DEMAND','MTL_USER_SUPPLY','OE_ORDER_LINES_ALL','PO_ACCEPTANCES',
'PO_CHANGE_REQUESTS','PO_SUPPLIER_ITEM_CAPACITY','WIP_DISCRETE_JOBS','WIP_FLOW_SCHEDULES','WIP_LINES','WIP_MSC_OPEN_JOB_STATUSES','WIP_OPERATIONS',
'WIP_OPERATION_NETWORKS','WIP_OPERATION_RESOURCES','WIP_OP_RESOURCE_INSTANCES','WIP_REPETITIVE_ITEMS','WIP_REPETITIVE_SCHEDULES','WIP_REQUIREMENT_OPERATIONS',
'WIP_SUB_OPERATION_RESOURCES','WSH_DELIVERY_DETAILS','WSH_TRIPS','WSH_TRIP_STOPS','WSM_COPY_OPERATIONS','WSM_COPY_OP_NETWORKS','WSM_COPY_OP_RESOURCES',
'WSM_COPY_OP_RESOURCE_INSTANCES','WSM_COPY_REQUIREMENT_OPS')
order by MVIEW_NAME; -- replace this line to use OPTION B or C
-- order by log_table, mview_name -- OPTION B sort by MLOG Name
-- order by DETAILOBJ_NAME, mview_name -- OPTION C sort by Base Table Name

Once you get the snapshot table name you can then find the program which is responsible for creating this snapshot. Oracle Advanced Supply Chain Planning Implementation and User Guide has detailed list of Concurrent Programs for each of the Snaphost Tables. We found that Create OE Snapshot program and immediately found the executable.

Concurrent Program

Executable

Simply grab the MSCONTSN.sql file from $MSC_TOP/sql folder and copy it to your local machine.

Based on your requirements add filter criteria to the dynamic sql. We added 

||' AND OOL.OPEN_FLAG = ''Y'' ' like below

Take the backup of the original file mv MSC_TOP/sql/MSCONTSN.sql  $MSC_TOP/sql/MSCONTSN.sql.orig.

FTP the modified file from your local machine to $MSC_TOP/sql. Now to re-create the snapshot  tables based on the new filter criteria there are two ways

Navigation:

Responsibility - Advanced Supply Chain Planer - > Setup -> Run Request -> Select any Org from LOV

1. Run Create OE Snapshots. Once the program completes, it sets the MSC: Source Setup Required  to Yes. You need to manually set this back to No. Otherwise ATP Data Pull program will Submit Create AHL Snapshots to re-create all snapshots. Since, we modified only OE one we don't need to re-run it again.

1. Set the profile option MSC: Source Setup Required  to Yes and then Submit ATP Data Pull program. This will re-create all snapshots based on new definitions. Four our case only OE Snapshot definition has changed.

In next few blogs I will discuss how to troubleshoot further and stop unnecessary processing of irrelevant data.

How to find Onhand Quantity of a Lot Controlled Item in a locator Controlled Subinventory ??

Had a requirement to get total Available to Transact (ATT) quantity of a lot controlled item in a locator controlled subinventory. Searched extensively in MOS and google to find API. We knew INV_Quantity_Tree_PUB.Query_Quantities API does that but could not find out appropriate parameter values. As the item was lot controlled, we were passing p_is_lot_control parameter as TRUE. API was returning 0 as ATT because we were not passing lot number.

We needed to find ATT of all lots in a locator inside a subinventory. After playing with the parameter values I realized that to get total ATT of the item we actually needed to pass FALSE to p_is_lot_control parameter. And to our surprise it worked like a magic. Here is a sample script which can be used.

DECLARE
        L_api_return_status  VARCHAR2(1);
        l_qty_oh             NUMBER;
        l_qty_res_oh         NUMBER;
        l_qty_res            NUMBER;
        l_qty_sug            NUMBER;
        l_qty_att            NUMBER;
        l_qty_atr            NUMBER;
        l_msg_count          NUMBER;
        l_msg_data           VARCHAR2(1000);
        l_item_id            NUMBER :=3917224;
        l_org_id             NUMBER :=284;
        BEGIN
       
        inv_quantity_tree_grp.clear_quantity_cache;
        dbms_output.put_line('Transaction Mode');
          apps.INV_Quantity_Tree_PUB.Query_Quantities (
            p_api_version_number => 1.0
          , p_init_msg_lst       => apps.fnd_api.g_false
          , x_return_status      => L_api_return_status
          , x_msg_count          => l_msg_count
          , x_msg_data           => l_msg_data
          , p_organization_id    => l_org_id
          , p_inventory_item_id  => l_item_id
          , p_tree_mode          => apps.INV_Quantity_Tree_PUB.g_transaction_mode
          , p_onhand_source      => 3
          , p_is_revision_control=> false
          , p_is_lot_control     => FALSE
          , p_is_serial_control  => FALSE
          , p_revision           => NULL
          , p_lot_number         => NULL
          , p_subinventory_code  => 'SSTOCK'
          , p_locator_id         => 11285
          , x_qoh                => l_qty_oh
          , x_rqoh               => l_qty_res_oh
          , x_qr                 => l_qty_res
          , x_qs                 => l_qty_sug
          , x_att                => l_qty_att
          , x_atr                => l_qty_atr );

dbms_output.put_line('Quantity ATT'||to_char(l_qty_att));
dbms_output.put_line('Quantity ATR'||to_char(l_qty_atr));
end;
/

It may be known to others but I did not know this. So thought of sharing this for people like me.
Happy reading.

How to retrigger PO Requisition Account Generator (in iProcurement)?

Summary

“PO Requisition Account Generator Workflow” (POWFRQAG) gets triggered to default the distribution accounts – and usually this WF is customized in most of the EBS implementation projects to support the business rule for defaulting charge account.

This process also gets retriggered automatically for change in some entities like (category, requestor etc.) depending on the line type of the PR. But the process does not retrigger for update in any other field (or DFF) in PR line and that’s where this extension is required.

This solution below will describe how to retrigger the “PO Requisition Account Generator Workflow” for change in ‘Quantity’ and this is limited to the iProcurement page [iProcurement >> Shopping Cart >> Checkout >> Requisition (Lines)] only – and it is not applicable to the ‘Requisitions’ Forms.

Solution Approach

All the fields for PR line are available in the entity object oracle.apps.icx.por.schema.server.PoRequisitionLineEO. The account generator gets retriggered for the attributes where the property AccountLineBizAttrSet is set to ‘Y’

So, here we need to extend the PoRequisitionLineEO to xxcusPoRequisitionLineEO to set the AccountLineBizAttrSet property of ‘Unit Price’ attribute to ‘Y’

Technical Details

Following steps to be done to address the customization:

1. Copy and download entire directory structure and files from $JAVA_TOP/oracle/apps/icx
2. To extend the PoRequisitionLineEO, create a new project and then open the package oracle.apps.icx.por.schema.server in the project. Now select the EO in the “Extends Entity” field.Save the new EO as xxcusPoRequisitionLineEO.
   
   
    

3. Entity Object Wizard, click the “New from Table…” button and select your target attribute(s) [e.g. QUANTITY].  Click “OK”.  Accept the defaults for the rest of the steps in the wizard.
   

4. In the System Navigator panel, highlight the EO created above.  The Structures panel will list all the attributes in the EO.
   

5. Highlight the attribute QUANTITY in the Structures panel and the right-click to select the “Edit” option.  The Attribute Editor will come up. 
6. Select Properties from the left-hand panel. Enter “AccountLineBizAttrSet” in the name field and “Y” in the value field.  Click “Add”, then Hit “OK” and save the changes.
   

7. Now create the substitution for the parent (seeded) EO.
   

Installation

1. Copy xxcusPoRequisitionLineEODefImpl.java and xxcusPoRequisitionLineEOImpl.java to $JAVA_TOP/oracle/apps/icx/por/schema/server.
2. Compile java files using javac command and change the permission of the class files to 777
3. Copy xxcusPoRequisitionLineEO.xml $JAVA_TOP/oracle/apps/icx/por/schema/server and change the permission to 777
4. FTP the substitution file xxcusIProc.jpx (in general this file is created under ‘MyClasses’ folder where the project is created) and execute the following command:
   java oracle.jrad.tools.xml.importer.JPXImporter  $JAVA_TOP/oracle/apps/icx/por/schema/server/xxeocIProc.jpx -username apps -password <password> -dbconnection "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS= (COMMUNITY=TCP_COMMUNITY)(PROTOCOL=TCP)(Host= <host>)(Port=<port>)))(CONNECT_DATA=(SID= <sid>)))"

5. Bounce OACORE and OC4J in the middle tire.

How to Send "To Supplier" Attachments with Purchasing Notifications

How can Header and Line level attachments of the type To Supplier be sent with the Supplier Notification, at time of Purchase Order Approval?

Solution

1.  Set-up the Purchasing Options:

    •  Navigate to Setup => Organization => Purchasing Options
    •  Set the Output format to:  PDF
    •  Set the Maximum Attachment Size in MB to any value that email server can handle(e.g. 10)
    •  Set the Email Attachment File name (for example Attachment.zip)


2.  Create an attachment file on ones system
     (Word Doc, Excel File, etc.)


3.  Create a Purchase Order (PO) with Attachments

     •  Navigate: Purchase Orders => Purchase Orders
     •  Enter the required data for the PO and save
     •  Add an attachment at the header level and attachment at line level
        (Use Attachments icon - Use  'To Supplier' as the Category)

     •  Click on Save
     •  Click on the Approval button
     •  In the Email field, enter the Supplier Email Address
     •  Submit the PO for Approval

4.  The email will be received at the address entered above
5.  Save the attached file attachment to ones desktop for review

Here is the Oracle Support Document for the same
R12: Sending "To Supplier" Attachments with Purchasing Notifications [ID 797329.1]

Cheers....

How to Create Custom Customer Statement Report??

Business Requirements

Client business needs to generate and send the statements with outstanding balance to their customers. Oracle eBS provides the functionality to generate the statement from ‘Receivables’ application:

Receivables > Print Documents > Statements

However, the output generated by the program (AR Customer Balance Statement Letter) needs to be formatted as per the business standard layout and additionally the output needs to be emailed to the customers based on the registered contacts.

Build Constraints

Following section describes the seeded process and the constraints around the extension to cater the requirements.

Study of the Seeded Process

Once the application user enters the statement data from the ‘Print Statement’ form and ‘Submit’ the program, it submits the ‘Statement Generation Program’ concurrent request.

This is a ‘Spawn’ process and it populates the temp tables based on the data entered by the user in the form.

Finally it submits the ‘AR Customer Balance Statement Letter’. This is a java concurrent program which executes the query in the temp table and generates the PDF output as per Oracle defined template. The SQL query is attached as a XML data template in its data definition in ‘XML Publisher Administrator’.

The data in the temp table is purged once the process is completed.

The navigation to Submit Customer Statement Print Documents -> Statements

Select the Operating Unit and then Select Cycle and Statement Date and Click Submit. It actually Submits AR Customer Balance Statement Program

The program is submitted in all installed languages concurrently.

Where is the Customization?

To fulfill the requirement, following customizations are required:

1.      Create a new custom XML data template to include the new field ‘EMAIL_ID’ for the customer contact.

2.      Create a bursting control file so that the output PDF is split based on the ‘EMAIL_ID’.

3.      Upload the custom XML data template and the bursting control file to the data definition of the concurrent program ‘AR Customer Balance Statement Letter’ (identified by the short name ‘ARCUSBALSL’).

4.      Create a new rich text template (RTF) based on the proposed layout and upload the document to XML template related to the above data definition.

Thus the ‘AR Customer Balance Statement Letter’ will be submitted by the seeded process but it will execute the query in the custom data template and generate the output as per the custom layout. The bursting program will split and send the email to the intended recipients.

Where is the Constraint?

Oracle has restricted to upload any custom data template or bursting control file against the data definition ‘ARCUSBALSL’. Thus generic customization approach stated in above section can not be done in this case.

A parallel custom process (like ‘XXX AR Customer Balance Statement Letter’) can not be designed because it can’t be executed alone. It has to be submitted by the parent program ‘Statement Generation Program’, spawned from the ‘Print Statement’ form to generate all the data required for the report.

Revised Technical Approach

Since there was no alternative, the technical team has completed a PoC by altering seeded Oracle process which violates the Oracle standard practice of implementation.

This section highlights the steps to be done to implement the solution:

1.      Create a custom data definition (XX_ARCUSBALSL) and add new RTF layout.

2.      Download the XML data template ARXSGPO.xml and Save it as XX_ARXSGPO.xml.

-          Modify to add the new field ‘EMAIL_ID’ element

-          Add code in “beforeReport” trigger to update the template name in the table FND_CONC_PP_ACTIONS for the current request id.

UPDATE FND_CONC_PP_ACTIONS
   SET argument1 = 'XXCUSTOM'
      ,argument2 = 'XX_ARCUSBALSL'
 WHERE action_type = 6
   AND concurrent_request_id = P_CONC_REQUEST_ID;

By updating the FND_CONC_PP_ACTIONS table you ask Post Processor to pickup the custom Data Definitions and Templates

XXCUSTOM is the Custom Application Name

-          Add the code “AfterReport” trigger to execute the bursting program.

3.      Create a bursting control file for sending email.

4.      Attach the ‘XML data template’ and ‘Bursting Control File’ with the data definition XX_ARCUSBALSL.

5.      Now ARCUSBALSL (‘AR Customer Balance Statement Letter’) executes the java class oracle.apps.xdo.oa.cp.JCP4XDODataEngine (executable name XDODTEXE)      .

Create a new java class XXJCP4XDODataEngine to call the custom data definition ‘XX_ARCUSBALSL’ instead of the seeded data template.

Create a new executable XX_XDODTEXE with the new java program and attach it with the seeded concurrent program ‘ARCUSBALSL’.

6.      Thus the seeded ‘Statement Generation Program’ will invoke the ‘AR Customer Balance Statement Letter’ and this will execute the query in the custom XML data template to create desired PDF files which will be emailed by the bursting program.

Risk and Mitigation

The approach described above does not alter any process in ‘Statement Generation Program’. It is only altering the mechanism of producing output; yet there are some potential risks in this solution.

Risks

1. The executable of the seeded concurrent program ‘ARCUSBALSL’ is changed to a custom executable – so this can be reverted after the patch application.

2. New functionality will not be available after patch application since ‘ARCUSBALSL’ will be executing a custom query. Although this is not much relevant since for a custom report (which is a copy of seeded report with extended functionality) is never upgraded with the patch application.

3. Oracle Support may not be available for this program.

Mitigations

1. A script will be provided which can be executed as a post patching activity. This script will replace the seeded executable with the custom one.

2. If ARXSGPO.xml is upgraded then XX_ARXSGPO.xml can be rebuild based on that. This is however may not require at all.

3. In case of any issue in live instance, it can be reproduced in any support instance by replacing the executable of the concurrent program with the seeded one. Then it can be observed if there is issue exists with the seeded process and accordingly it can be reported to the Oracle Support.

Cheers.....

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);