XDO Tables - XML Publisher report queries

XDO_DS_DEFINITIONS_B
XDO_DS_DEFINITIONS_TL

XDO_TEMPLATES_B
XDO_TEMPLATES_TL
XDO_LOBS


XDO_FONT_MAPPINGS
XDO_FONT_MAPPING_SETS_B
XDO_FONT_MAPPING_SETS_TL

select * from XDO_DS_DEFINITIONS_B where data_source_code = 'Data source short code'
select * from XDO_DS_DEFINITIONS_TL where rownum < 3

select * from XDO_TEMPLATES_B where TEMPLATE_CODE = 'Template Short code'
select * from XDO_TEMPLATES_TL where TEMPLATE_CODE = 'Template Short code'
select * from XDO_LOBS where lob_code = 'Template Short code'

TABLE NAME	DESCRIPTION
XDO_CONFIG_PROPERTIES_B	Stores the XML Publisher Administration configuration properties that are accessible from the OA Framework interface.
XDO_CONFIG_PROPERTIES_TL	Translation table for XDO_CONFIG_PROPERTIES_B.
XDO_CONFIG_VALUES	Stores the values assigned to the property in Administration Configuration Data
XDO_CURRENCY_FORMATS	Stores the format masks for various currencies. A collection of these formats forms a currency format set.
XDO_CURRENCY_FORMAT_SETS_B	Stores the Currency Format Sets
XDO_CURRENCY_FORMAT_SETS_TL	Stores the Currency Format Sets
XDO_DS_DEFINITIONS_B	Stores data source definition represented by XML Schema Definition (XSD). Each data source has one or more elements, and these information are stored in XDO_DS_ELEMENTS_B
XDO_DS_DEFINITIONS_TL	Translation table for XDO_DS_DEFINITIONS_B
XDO_FONT_MAPPINGS	Stores the mappings from a base font to a target Truetype or Type 1 font. A collection of these mappings forms a font mapping set
XDO_FONT_MAPPING_SETS_B	Stores the header information for a font mapping set, which is a collection of font mappings
XDO_FONT_MAPPING_SETS_TL	Translation table for XDO_FONT_MAPPING_SETS_B
XDO_LOBS	Stores Template(RTF File), XML File, XML Schema File, locale(langauge and territory) sensitive binary and text files. It is mainly used for storing language layout templates.
XDO_TEMPLATES_B	Stores template information. Each template has a corresponding data source definition stored in the XDO_DS_DEFINITIONS_B. Each translation of a certain template, not each template, has a corresponding physical template file. The physical template file information are stored in the XDO_TEMPLATE_FILES.
XDO_TEMPLATES_TL	Translatable table for XDO_TEMPLATES_B
XDO_TEMPLATE_FIELDS	Stores information of the fields of template file. Each field belongs to one of physical template files
XDO_TRANS_UNITS	Stores the header information regarding each segment of translatable text in layout templates
XDO_TRANS_UNIT_PROPS	Stores any untranslatable values embedded within a segment of text. These values will be merged back into the text translations
XDO_TRANS_UNIT_VALUES	Stores any untranslatable values embedded within a segment of text. These values will be merged back into the text translations

Click here to know basic of How to develop XML report

XML Publisher Reports oracle Apps

Overview

XML Publisher, which is also called Oracle Business Intelligence Publisher (BI Publisher), is a template-based reporting tool that leverages standard technologies for data extraction and display. Business users can build the layout and rules for the report themselves by using common desktop tools. Development engineers can concentrate on extracting data in the most efficient manner. Deployment time is faster. Also, business users with minimal training can design templates and reports based on the data sources provided by engineers, thus greatly reducing the cost of ownership.

XML Publisher separates a reports data, layout and translation components into three manageable pieces at design time; at runtime all the three pieces are brought back together by XML Publisher to generate the final formatted, translated outputs like PDF, HTML, XLS and RTF. In future, if any there is any change in layout we just need to add/modify the Layout file.

Data Logic: Data extracted from database and converted into an XML string.

Layout: The layout templates to be used for the final output are stored and managed in the Template Manager.

Translation: The translation handler will manage the translation that is required at runtime

In brief the steps are as follows:-
a.    Create a procedure and register it as Concurrent Program so that we write   

       XML  tags  into output file.
b.    Build a Data Definition & XML Template using XML Publisher.
c.    Create a relation between XML Template & Concurrent Program and run the concurrent

       program

Requirements for XML Data Object Reports

1. Oracle XML Publisher
2. Template Builder

When you download the XML Publisher Desktop edition you get a Zip file containing setup for XML Publisher Desktop Install Shield, this installs some components into Microsoft Word. After installing, the Word Add-Ins is attached to the menu bar for the word document. This menu lets you attach an XML data source document, add the XML data to your template, set preferences and preview the output.

So let us take a look at how we can create xml report from xml data template.

The XML data template is an XML document that consists of four basic sections:

1. Parameters
2. Triggers
3. Data Query
4. Data Structure

This structure is shown in the following graphic: 

If you see the above XML Data Template, we have specified the parameters that are being used and also the SQL Query that will be fetching the data. The SQL Query is using the parameters that are being defined under the Parameters Section.  I have also included a jpeg image of the XML File Definition. Save the same as “.xml” file in your local system. After saving the file, you should be able to open it in the browser. Double click the xml file and the file should open.

Now, navigate to System Administrator > Concurrent > Program > Define.

Create a new Concurrent Program and give the details. The Executable should always be XDODTEXE and output format should XML. After creation of concurrent program assign the program to the request group. 

Now, Go to XML Publisher Administrator responsibility and create a new Data Definition. Give the following for the fields:

Code name = concurrent program short name

Application – Provisioning (or your custom application)

Start Date – Automatically Populated

Press “Add File” next to Data Template. Browse and upload the XML Data Template that we have created here. 

Go to Data Templates tab and create a new Data Template. Give the following details:

Code name = concurrent program short name

Select the Data Definition that we had created prior to this.

Select Language as US English and upload the RTF File that we have created (Template_file.rtf)

Now, all set to get your report output. Go to responsibility where you have assign the program and run the report by providing required parameter.

Hope this helps. Let me know your thoughts and feel free to ask any questions that you might have. Also, check out the next blog which has Triggers being called from the XML Template file.

How to Use FNDLOAD?

It is merely time consuming task to create same setup data on each instance separately. Hence to migrate setup data from 1 instance to other (Eg. From DEV to

PROD), ldt & lct files are used.

LDT (Data Loader Files)- This files are Used to upload & download setup data on different instances.

LCT (Data Config Files)- This files are used to create LDT Files. There are difference lct files for different sets of data.

Eg: Value Set- afffload.lct,Request Set- afcpreqg.lct, Concurrent Program- afcpprog.lct etc.

** Now lets take a simple example where you want to migrate 1 concurrent program from Development (DEV) instance to Production (PROD).

Step 1: Login to Unix Development server and change the directory using following Command where you can store your LDT files:

cd $CUST_TOP/ patch/115/import/US/ (where CUST_TOP is your custom top on server)

Step 2: Run the following command by supplying the parameters given in {} as per your need

FNDLOAD {userid/password@connect_string} O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct {file_name.ldt} PROGRAM

APPLICATION_SHORT_NAME="{your_application_short_name}" CONCURRENT_PROGRAM_NAME="{your_concurrent_name}"

The above command will generate ldt file in your custom top directory ($CUST_TOP/ patch/115/import/US/ )

Step 3: Copy the LDT file from Development Server to your local desktop

Step 4: Login to Unix Production Server and copy the LDT files from local desktop to Production Server on following path:

$CUST_TOP/ patch/115/import/US/ {file_name.ldt}

Step 5: Finally Run the following command by supplying all parameters in {} to upload concurrent program definition on production application server.

$ FNDLOAD {userid/password@connect_string} 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $CUST_TOP/patch/115/import/US/{file_name}.ldt

Note:

1) All above FNDLOAD commands are single line commands.

2) All above steps will remain exactly same for all kind of setup data (Eg: Value Set, CP, Req.group, DFF…etc) except the name of LCT file (.lct). LCT file

will be different for every different component because the setup data is different.

Refer to below link for list of various FNDLOAD commands:

FNDLOAD commands

How to Uncheck ignore for planning flag using "wsh_deliveries_pub.delivery_action" API

DECLARE
    l_delivery_id          NUMBER;
    l_delivery_detail_id   NUMBER;

--   MO_GLOBAL.init('ONT');
    l_init_msg_list        VARCHAR2(1000);
    l_return_status        VARCHAR2(1000);
    l_msg_count            NUMBER;
    l_msg_data             VARCHAR2(1000);
    l_trip_id              VARCHAR2(1000);
    l_trip_name            VARCHAR2(1000);
    v_counter              NUMBER;
    p_api_version          NUMBER;
    p_init_msg_list        VARCHAR2(200);
    p_commit               VARCHAR2(200);
    p_validation_level     NUMBER;
    p_tabofdeldets         apps.wsh_delivery_details_pub.id_tab_type;
    p_action               VARCHAR2(200);
    p_delivery_id          NUMBER;
    p_delivery_name        VARCHAR2(200);
    p_count                NUMBER;
    v_context              VARCHAR2(100);
    x_return_status        VARCHAR2(200);
    x_msg_count            NUMBER;
    x_msg_data             VARCHAR2(4000);
    CURSOR ignore_for_plan_cur IS SELECT DISTINCT
        wnd.organization_id,
        wnd.ship_method_code,
        wnd.status_code,
        wnd.name,
        wnd.delivery_id,
        wdd.delivery_detail_id
                                  FROM
        wsh_new_deliveries wnd,
        wsh_delivery_details wdd,
        fnd_lookup_values_vl flv,
        wsh_delivery_assignments wda
                                  WHERE
        wnd.organization_id =:p_org_id
        AND wnd.ignore_for_planning = 'Y'
        AND upper(flv.lookup_code) = upper(wnd.ship_method_code)
        AND flv.lookup_type = 'SHIP_METHOD'
        AND upper(flv.attribute1) <> upper('yes')
        AND wdd.released_status IN (
            'N',
            'R',
            'S',
            'Y',
            'B',
            'X'
        )
        AND upper(wnd.status_code) <> upper('CL')
        AND wda.delivery_id = wnd.delivery_id
        AND wda.delivery_detail_id = wdd.delivery_detail_id;
   -- AND wnd.DELIVERY_ID IN (126064);

BEGIN
    FOR ignore_plan_rec IN ignore_for_plan_cur LOOP

        fnd_file.put_line(fnd_file.log,'Unchecking the flag' || ignore_plan_rec.delivery_id);

wsh_deliveries_pub.delivery_action(
p_api_version_number => 1
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
,p_action_code => 'INCLUDE_PLAN'
,p_delivery_id => ignore_plan_rec.DELIVERY_ID
,p_delivery_name=>ignore_plan_rec.NAME
,p_asg_trip_id=> null
,p_asg_trip_name=> null
,p_asg_pickup_stop_id=> null
,p_asg_pickup_loc_id=> null
,p_asg_pickup_stop_seq=> null
,p_asg_pickup_loc_code=> null
,p_asg_pickup_arr_date=> null
,p_asg_pickup_dep_date=> null
,p_asg_dropoff_stop_id=> null
,p_asg_dropoff_loc_id=> null
,p_asg_dropoff_stop_seq=> null
,p_asg_dropoff_loc_code=> null
,p_asg_dropoff_arr_date=> null
,p_asg_dropoff_dep_date=> null
,p_sc_action_flag=> null
,p_sc_intransit_flag=> null
,p_sc_close_trip_flag=> null
,p_sc_create_bol_flag=> null
,p_sc_stage_del_flag=> null
,p_sc_trip_ship_method=> null
,p_sc_actual_dep_date=> null
,p_sc_report_set_id=> null
,p_sc_report_set_name=> null
,p_sc_defer_interface_flag=> null
,p_sc_send_945_flag=> null
,p_sc_rule_id=> null
,p_sc_rule_name=> null
,p_wv_override_flag=> null
,x_trip_id => l_trip_id
,x_trip_name => l_trip_name
);
    COMMIT;
    END LOOP;

    END;
END;

Oracle Apps Interview question related to Multi Org

1. What is Multi Org Architecture?
The Multiorg Architecture is meant to allow multiple companies or subsidiaries to store their records within a single database.
The multiple Organization architecture allows this by partitioning data through views in the APPS schema. Multiorg also allows you to maintain multiple sets of books. Implementation of multiorg generally includes defining more than one Business Group.

2. How can I know if I am using Multi Org?
Run the SQL statement:
select multi_org_flag
from fnd_product_groups;
The result ‘Y’ means your database is setup for multiorg.

3. What are the relationships I can define in a multi org environment?
You define the relationships among inventory organizations, operating units, legal entities, Business Groups, and sets of books to create a multilevel company structure:
. Business Groups separate major segments of a business. Each can have it’s own set of books. Each Group will also have a structure of other organizations classifications assigned to it.
– Legal Entities post to a Set of Books
– Operating Units are part of a Legal Entity
– Inventory Organizations are part of an Operating Unit
– Inventory Organizations define and maintain items used by other manufacturing modules (Order Entry, Purchasing, MRP, etc.). They also collect and pass data to the Financials modules.

4. I want to Setup Additional Organizations, do I have to setup Multiorg?
No, you do not need to setup multiorg. You do not have to be multi-org to have multiple organizations only if you intend to have multiple sets of books.

5. Is there any documentation on how to setup an organization?
A. – Refer to “Oracle Manufacturing Implementation Manual” part#A50765.
– Refer to “Multiple Organizations in Oracle Applications” part#A58478 for Release 11 and part#50771 for Release 10.
– Also refer to note 76440.1 for Organization setup steps.

6. When my organization hierarchy changes, can I move an organization from one set of books or legal entity to another?
No, you should not try to move an organization from one set of books or legal entity to another because your data may not be valid for the new set of books or legal entity. Instead, you should disable the old organization and create a new organization for the appropriate set of books or legal entity. The new organization will contain your new data, and the disabled organization will act as an “old” or “history” entity that stores past transactions.

7. How can I setup a child org to be its own costing organization?
Steps:
– Change the attribute control for “default category set” to organization level
– Change the attribute control for “costing enabled” to organization level
– Change the attribute control for “inventory asset value” to organization control
Then you can make a child organization it’s own costing organization by entering the organization name in the column labeled “Costing Organization”

8. How can I define organization restriction?
Use the Organization Access form (INVSDORA) to restrict the list of organizations displayed for each responsibility level.
Refer to Oracle Inventory User’s Guide for steps and important notes.
*WARNING* If you populate any rows in this form, you MUST populate a row for EACH responsibility that you wish to have access to that Organization.

9. What responsibility do I need to use to setup organization?
Use the General Ledger responsibility to define the Set of Books Use the Inventory Responsibility to define Organizations and other related information such as Inventory Key Flexfields, Locations, Workday calendar, other Organization Classifications, and other inventory information.

10. What are the main profile options relating to Organization setup and what are they used for?
– HR:User Type = HR User
This is necessary to allow the Inventory responsibility to complete the organization setup. Setting the profile to a value of ‘User’ as opposed to ‘Payroll & User’ will restrict the Inventory user from accessing any Payroll information if Oracle Payroll is installed.
– HR: Business Group = {the users Business Group name}
This points the responsibility to the appropriate Business Group.
When multiple Business Groups are defined, you must associate each responsibility with one and only one Business Group.
A responsibility can not see organization data from more than one Business Group.
– MO: Operating Unit = {the users Operating Unit name}
Used primarily in a multiorg environment.
This points the responsibility to the appropriate Operating Unit.
Set the site level to the desired default operating unit.
If there is more than 1 Operating Unit Defined, this profile option must be set at the responsibility level for each responsibility.

Purchase Requisition without a Purchase Order

==============================================

SELECT   prh.segment1 "PR NUM", TRUNC (prh.creation_date) "CREATED ON",
         TRUNC (prl.creation_date) "Line Creation Date", prl.line_num "Seq #",
         msi.segment1 "Item Num", prl.item_description "Description",
         prl.quantity "Qty", TRUNC (prl.need_by_date) "Required By",
         ppf1.full_name "REQUESTOR", ppf2.agent_name "BUYER"
    FROM po.po_requisition_headers_all prh,
         po.po_requisition_lines_all prl,
         apps.per_people_f ppf1,
         (SELECT DISTINCT agent_id, agent_name
                     FROM apps.po_agents_v) ppf2,
         po.po_req_distributions_all prd,
         inv.mtl_system_items_b msi,
         po.po_line_locations_all pll,
         po.po_lines_all pl,
         po.po_headers_all ph
   WHERE prh.requisition_header_id = prl.requisition_header_id
     AND prl.requisition_line_id = prd.requisition_line_id
     AND ppf1.person_id = prh.preparer_id
     AND prh.creation_date BETWEEN ppf1.effective_start_date
                               AND ppf1.effective_end_date
     AND ppf2.agent_id(+) = msi.buyer_id
     AND msi.inventory_item_id = prl.item_id
     AND msi.organization_id = prl.destination_organization_id
     AND pll.line_location_id(+) = prl.line_location_id
     AND pll.po_header_id = ph.po_header_id(+)
     AND pll.pl_line_id = pl.po_line_id(+)
     AND prh.authorization_status = 'APPROVED'
     AND pll.line_location_id IS NULL
     AND prl.closed_code IS NULL
     AND NVL (prl.cancel_flag, 'N') <> 'Y'
ORDER BY 1, 2 

PURCHASE REQUISITION WITHOUT PO

SELECT prh.segment1 "PR NUM",
TRUNC (prh.creation_date) "CREATED ON",
TRUNC (prl.creation_date) "Line Creation Date",
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
TRUNC (prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
FROM po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
( SELECT DISTINCT agent_id, agent_name FROM apps.po_agents_v) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date BETWEEN ppf1.effective_start_date
AND ppf1.effective_end_date
AND ppf2.agent_id(+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id(+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id(+)
AND pll.po_line_id = pl.po_line_id(+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND NVL (prl.cancel_flag, 'N') <> 'Y'

ORDER BY 1, 2, 4

PO details query in oracle apps R12

SELECT HOU.SHORT_CODE OU,

             PHA.CREATION_DATE PO_CREATION_DATE,

             PHA.APPROVED_DATE PO_APPROVED_DATE,

             NVL(PRH.INTERFACE_SOURCE_CODE, PRH.APPS_SOURCE_CODE)                           

             REQUISITION_SOURCE,

             PHA.TYPE_LOOKUP_CODE PO_TYPE,

             APS.VENDOR_NAME SUPPLIER_NAME,

             APS.SEGMENT1 SUPPLIER_NUMBER,

             REPLACE(PHA.COMMENTS, CHR(10), '  ') PO_HEADER_DESCRIPTION,

             NVL(PRH.SEGMENT1, pda.req_header_reference_num) PR_NUMBER,

             PHA.SEGMENT1 PO_NUMBER,

             PLA.LINE_NUM PO_LINE_NUMBER,

             (SELECT SEGMENT1

                FROM apps.MTL_SYSTEM_ITEMS_B

               WHERE INVENTORY_ITEM_ID = PLA.ITEM_ID

                 AND ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID) ITEM,

             pda.destination_type_code DESTINATION_TYPE,

             REPLACE(REPLACE(PLA.ITEM_DESCRIPTION, CHR(13), '  '),

                     CHR(10),

                     '  ') PO_LINE_DESCRIPTION,

             TO_CHAR(NVL(PLA.UNIT_PRICE * PLA.QUANTITY, '0.00'),

                     '999,999,999,999.99') PO_LINE_AMOUNT,

             PO_INQ_SV.GET_PO_TOTAL(PHA.TYPE_LOOKUP_CODE,

                                    PHA.PO_HEADER_ID,

                                    NULL) PO_TOTAL_AMOUNT,

             PHA.CURRENCY_CODE PO_CURRENCY,

             PLA.PURCHASE_BASIS PO_LINE_TYPE,

             MCK.CONCATENATED_SEGMENTS HERMES_CATEGORY,

             PLLA.PROMISED_DATE PROMISED_DATE,

             PLLA.NEED_BY_DATE NEED_BY_DATE --LPAD(TO_CHAR(PLLA.NEED_BY_DATE,'MM/DD/YYYY '),20) "NEED_BY_DATE",

             PPX1.FULL_NAME REQUESTER,

             PPX2.FULL_NAME PREPARER,

             PPF2.AGENT_NAME BUYER,

             HRL.LOCATION_CODE SHIP_TO_LOC,

             GCCK.CONCATENATED_SEGMENTS GL_ACCOUNT,

             PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,

             PLA.UNIT_PRICE UNIT_PRICE,

             (PRL.unit_price * PRL.quantity)Req_line_amt,

             PLLA.QUANTITY QTY_ORDERED,

             PLLA.QUANTITY_BILLED QTY_BILLED,

             PLLA.QUANTITY_RECEIVED QTY_RECD,

             (PLLA.QUANTITY_BILLED * PLA.UNIT_PRICE) AMOUNT_BILLED,

             (PLLA.QUANTITY_RECEIVED * PLA.UNIT_PRICE) AMOUNT_RECD,

             RCD.RCV_CREATION_DATE RECEIVING_CREATION_DATE,

             PA.segment1 PROJECT,

             pa.project_type PROJECT_TYPE,

             AT.NAME PAYMENT_TERMS,

             DECODE(PLLA.INSPECTION_REQUIRED_FLAG ||

                    PLLA.RECEIPT_REQUIRED_FLAG,

                    'NN',

                    '2 WAY',

                    'NY',

                    '3 WAY',

                    'YY',

                    '4 WAY',

                    'Not Specified') MATCHING_TYPE,

             PDA.ACCRUE_ON_RECEIPT_FLAG ACCRUE_AT_RECEIPT,

             PHA.AUTHORIZATION_STATUS PO_STATUS,

             PLLA.CLOSED_CODE SHIPMENT_STATUS,

             (SELECT ORGANIZATION_CODE

                FROM apps.MTL_PARAMETERS

               WHERE ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID) SHIP_TO_ORG,

             PRH.description Description_Headers,

             PRL.LINE_NUM Line#,

             TO_CHAR(TRUNC(PRL.CREATION_DATE), 'DD-MON-YYYY') Creation_date_of_Req_line,

             (SELECT distinct papf.full_name

                FROM apps.po.po_action_history pah,

                     applsys.fnd_user     fu,

                     hr.per_all_people_f  papf

               WHERE object_id = prh.requisition_header_id

                 AND pah.employee_id = fu.employee_id

                 AND fu.employee_id = papf.person_id

                 AND SYSDATE BETWEEN papf.effective_start_date AND

                     papf.effective_end_date

                 AND pah.object_type_code = 'REQUISITION'

                    --  AND pah.action_code = 'APPROVE'

                 AND pah.sequence_num =

                     (SELECT MAX(sequence_num)

                        FROM po.po_action_history pah1

                       WHERE pah1.object_id = pah.object_id

                         AND pah1.object_type_code = 'REQUISITION'

                      --  AND pah1.action_code = 'APPROVE'

                      )) Performed_By,

             PRH.CREATION_DATE PR_CREATION_DATE,

             (SELECT distinct pah.ACTION_DATE

                FROM apps.po.po_action_history pah,

                     applsys.fnd_user     fu,

                     hr.per_all_people_f  papf

               WHERE object_id = prh.requisition_header_id

                 AND pah.employee_id = fu.employee_id

                 AND fu.employee_id = papf.person_id

                 AND SYSDATE BETWEEN papf.effective_start_date AND

                     papf.effective_end_date

                 AND pah.object_type_code = 'REQUISITION'

                    --  AND pah.action_code = 'APPROVE'

                 AND pah.sequence_num =

                     (SELECT MAX(sequence_num)

                        FROM po.po_action_history pah1

                       WHERE pah1.object_id = pah.object_id

                         AND pah1.object_type_code = 'REQUISITION'

                      --  AND pah1.action_code = 'APPROVE'

                      )) Approval_Date,

             prl.line_num Line,

             (SELECT distinct PRIMARY_UNIT_OF_MEASURE

                FROM apps.mtl_system_items_b

               where INVENTORY_ITEM_ID(+) = PLA.item_id) UOM,

             (SELECT distinct DESCRIPTION

                FROM apps.hr_locations

               where location_id = pha.BILL_TO_LOCATION_ID) BILL_TO,

             (SELECT displayed_field

                FROM APPS.po_lookup_codes fob

               WHERE fob.lookup_type = 'FREIGHT TERMS'

                 AND fob.lookup_code = pha.freight_terms_lookup_code) FOB_Terms,

             PDA.ATTRIBUTE2 SIngle_Bid,

             PDA.ATTRIBUTE9 Emergency_PO,

             PDA.ATTRIBUTE5 After_The_fact,

             PDA.ATTRIBUTE6 Expedited_PO,

             PDA.ATTRIBUTE7 O2_Cleaning,

             PDA.ATTRIBUTE10 Critical_Item,

             PDA.ATTRIBUTE3 OEM_SOurced,

             PLA.CANCEL_DATE Cancelled_DATE,

             PRH.description Line_Description,

             PRL.CURRENCY_CODE PR_CURRENCY,

             PDA.DESTINATION_ORGANIZATION_ID deliver_to_organization,

             PRL.CURRENCY_UNIT_PRICE PR_Unit_Price,

             PHA.PO_HEADER_ID PO_HEADER,

             PRL.CURRENCY_UNIT_PRICE Currancy_Price,

              por_view_reqs_pkg.get_note_to_agent

                                     (prh.requisition_header_id)

                                                                Note_to_Buyer

        FROM APPS.PO_HEADERS_ALL PHA,

             APPS.PO_LINES_ALL PLA,

             APPS.PO_LINE_LOCATIONS_ALL PLLA,

             APPS.HR_LOCATIONS_ALL HRL,

             APPS.PO_DISTRIBUTIONS_ALL PDA,

             (SELECT DISTINCT AGENT_ID, AGENT_NAME from APPS.PO_AGENTS_V) PPF2,

             APPS.AP_SUPPLIERS APS,

             APPS.MTL_CATEGORIES_KFV MCK,

             APPS.PO_REQUISITION_HEADERS_ALL PRH,

             APPS.PO_REQUISITION_LINES_ALL PRL,

             APPS.PO_REQ_DISTRIBUTIONS_ALL PRD,

             APPS.HR_OPERATING_UNITS HOU,

             APPS.PER_PEOPLE_X PPX1,

             APPS.PER_PEOPLE_X PPX2,

             APPS.GL_CODE_COMBINATIONS_KFV GCCK,

             APPS.PO_LINE_TYPES PLT,

             APPS.GL_LEDGERS GL,

             APPS.PA_PROJECTS_ALL PA,

             APPS.AP_TERMS AT,

             (SELECT MAX(RSL.CREATION_DATE) RCV_CREATION_DATE,

                     pla.po_line_id

                FROM APPS.RCV_SHIPMENT_LINES RSL,

                     APPS.PO_HEADERS_ALL     PHA,

                     APPS.PO_LINES_ALL       PLA

               WHERE 1 = 1

                 AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

                 AND RSL.PO_line_ID(+) = PlA.PO_line_ID

               GROUP BY pla.po_line_id) RCD

       WHERE 1 = 1

         AND RCD.PO_LINE_ID(+) = PLA.PO_LINE_ID

         AND PHA.TERMS_ID = AT.TERM_ID

         AND PHA.VENDOR_ID = APS.VENDOR_ID

         AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID

         AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID

         AND PLLA.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID

         AND nvl(PLLA.CANCEL_FLAG, 'N') = 'N'

         AND PHA.AGENT_ID = PPF2.AGENT_ID(+)

         AND PLA.CATEGORY_ID = MCK.CATEGORY_ID

         AND PHA.PO_HEADER_ID = PDA.PO_HEADER_ID

         AND PLA.PO_LINE_ID = PDA.PO_LINE_ID

         AND PA.PROJECT_ID(+) = PDA.PROJECT_ID

         AND PRH.REQUISITION_HEADER_ID(+) = PRL.REQUISITION_HEADER_ID

         AND PRL.REQUISITION_LINE_ID(+) = PRD.REQUISITION_LINE_ID

         AND PDA.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID(+)

         AND PHA.ORG_ID = HOU.ORGANIZATION_ID

         AND PDA.DELIVER_TO_PERSON_ID = PPX1.PERSON_ID(+)

         AND PRH.PREPARER_ID = PPX2.PERSON_ID(+)

         AND PDA.CODE_COMBINATION_ID = GCCK.CODE_COMBINATION_ID(+)

         AND PLT.LINE_TYPE_ID = PLA.LINE_TYPE_ID(+)

        -- AND PHA.AUTHORIZATION_STATUS = 'APPROVED'

        AND PRH.AUTHORIZATION_STATUS= 'APPROVED'

         AND HOU.SET_OF_BOOKS_ID = GL.LEDGER_ID

         AND GL.LEDGER_ID = NVL(P_SET_OF_BOOK_ID, GL.LEDGER_ID)

         AND HOU.ORGANIZATION_ID =

             NVL(P_ORGANIZATION_ID, HOU.ORGANIZATION_ID)

         AND PHA.AGENT_ID = NVL(P_BUYER_ID, PHA.AGENT_ID)

         AND HRL.LOCATION_ID = NVL(P_LOCATION_ID, HRL.LOCATION_ID)

         AND ((P_REQUESTOR_ID is null and 1 = 1) OR

             (P_REQUESTOR_ID is not null and

             PPX1.PERSON_ID = P_REQUESTOR_ID))

         AND TRUNC(PHA.APPROVED_DATE) BETWEEN

             NVL(TRUNC(fnd_date.canonical_to_date(PO_APPR_DATE_FROM)),

                 TRUNC(PHA.APPROVED_DATE)) AND

             NVL(TRUNC(fnd_date.canonical_to_date(PO_APPR_DATE_TO)),

                 TRUNC(PHA.APPROVED_DATE))

       ORDER BY PHA.SEGMENT1,

                PLA.LINE_NUM,

                PLLA.QUANTITY,

                GCCK.CODE_COMBINATION_ID;