PO Purchase Order Detail
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Purchase Order Detail Report
Application: Purchasing
Source: Purchase Order Detail Report (XML)
Short Name: POXPOSTD_XML
DB package: PO_POXPOSTD_XMLP_PKG
Description: Purchase Order Detail Report
Application: Purchasing
Source: Purchase Order Detail Report (XML)
Short Name: POXPOSTD_XML
DB package: PO_POXPOSTD_XMLP_PKG
Run
PO Purchase Order Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT poh.segment1 PO_Number , poh.creation_date Creation_Date , plc_sta.displayed_field || decode(poh.cancel_flag, 'Y',', '||plc_can.displayed_field,NULL) || decode(nvl(poh.closed_code, 'OPEN'), 'OPEN', NULL, ', '||plc_clo.displayed_field) || decode(poh.frozen_flag, 'Y',', '||plc_fro.displayed_field,NULL) || decode(poh.user_hold_flag, 'Y', ', '||plc_hld.displayed_field, NULL) Status , poh.revision_num Rev , poh.printed_date Printed_Date , pdt.type_name Type , ppf.full_name Buyer , poh.revised_date Revised_Date , poh.currency_code C_CURRENCY , pov.vendor_name Vendor , poh.acceptance_due_date Acceptance_Date , decode(poh.user_hold_flag,'Y',:yes,'N',:no,null) On_Hold , pvs.vendor_site_code Vendor_Site , decode(poh.acceptance_required_flag, 'Y',:yes, 'N',:no,null) Acceptance_Required , SUM (DECODE (POL.order_type_lookup_code, 'RATE', PLL.amount - NVL(PLL.amount_cancelled, 0), 'FIXED PRICE', PLL.amount - NVL(PLL.amount_cancelled, 0), PLL.price_override * (PLL.quantity - NVL(PLL.quantity_cancelled, 0)))) C_AMOUNT_AGR , poh.po_header_id , fc.precision c_fnd_precision, SUM ( DECODE ( POL.order_type_lookup_code , 'RATE' , PO_POXPOSTD_XMLP_PKG.c_amount_agr_round( PLL.amount - NVL ( PLL.amount_cancelled , 0 ), fc.precision ), 'FIXED PRICE' , PO_POXPOSTD_XMLP_PKG.c_amount_agr_round( PLL.amount - NVL ( PLL.amount_cancelled , 0 ), fc.precision ), PO_POXPOSTD_XMLP_PKG.c_amount_agr_round( PLL.price_override * ( PLL.quantity - NVL ( PLL.quantity_cancelled , 0 ) ), fc.precision ) ) ) C_AMOUNT_AGR_ROUND FROM po_line_locations pll , fnd_currencies fc , po_headers poh , po_lines POL , po_vendors pov , po_vendor_sites pvs , per_people_f ppf , po_system_parameters psp , po_lookup_codes plc_sta , po_lookup_codes plc_can , po_lookup_codes plc_clo , po_lookup_codes plc_fro , po_lookup_codes plc_hld , po_document_types pdt WHERE poh.vendor_id = pov.vendor_id (+) AND poh.currency_code = fc.currency_code AND pdt.document_type_code = 'PO' AND pdt.document_subtype = poh.type_lookup_code AND plc_sta.lookup_code = nvl(poh.authorization_status, 'INCOMPLETE') AND POL.po_header_id (+) = POH.po_header_id AND PLL.po_line_id (+) = POL.po_line_id AND nvl(pll.shipment_type,'STANDARD') in ('STANDARD', 'PLANNED') AND poh.agent_id = ppf.person_id AND poh.vendor_site_id = pvs.vendor_site_id(+) AND poh.type_lookup_code in ('STANDARD','PLANNED') AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND plc_sta.lookup_type in ('PO APPROVAL','DOCUMENT STATE') AND plc_can.lookup_code = 'CANCELLED' AND plc_can.lookup_type = 'DOCUMENT STATE' AND plc_clo.lookup_code = nvl(poh.closed_code,'OPEN') AND plc_clo.lookup_type = 'DOCUMENT STATE' AND plc_fro.lookup_code = 'FROZEN' AND plc_fro.lookup_type = 'DOCUMENT STATE' AND plc_hld.lookup_code = 'ON HOLD' AND plc_hld.lookup_type = 'DOCUMENT STATE' AND (:P_status is NULL OR :P_status = nvl(poh.authorization_status,'INCOMPLETE') OR (:P_status = 'RESERVED' AND EXISTS ( select 'do we have uncancelled/not_f_closed shipments' FROM PO_LINE_LOCATIONS POLL1 WHERE POLL1.po_header_id = poh.po_header_id AND nvl(POLL1.cancel_flag, 'N') = 'N' AND nvl(POLL1.closed_code, 'OPEN') != 'FINALLY CLOSED' ) AND NOT EXISTS ( SELECT 'Do we have uncancelled/not f_closed shipments' FROM PO_LINE_LOCATIONS POLL2 WHERE POLL2.po_header_id = poh.po_header_id AND nvl(POLL2.encumbered_flag, 'N') = 'N' AND POLL2.shipment_type in ('STANDARD', 'PLANNED') AND nvl(POLL2.cancel_flag, 'N') != 'Y' AND nvl(POLL2.closed_code, 'OPEN') != 'FINALLY CLOSED'))) AND (:P_buyer is NULL OR :P_buyer = ppf.person_id) AND (:P_vendor_from is NULL OR pov.vendor_name >= :P_vendor_from) AND (:P_vendor_to is NULL OR pov.vendor_name <= :P_vendor_to) &WHERE_PERFORMANCE AND EXISTS (SELECT 'Category Flexfield Range' FROM po_lines pol , mtl_categories mca WHERE pol.category_id = mca.category_id AND &P_WHERE_CAT AND poh.po_header_id = pol.po_header_id) AND EXISTS (SELECT 'Item Flexfield Range' FROM po_lines pol , mtl_system_items msi WHERE pol.item_id = msi.inventory_item_id (+) AND nvl(msi.organization_id,:c_organization_id) = :c_organization_id AND &P_WHERE_ITEM AND poh.po_header_id = pol.po_header_id) GROUP BY poh.segment1 , poh.creation_date , poh.revision_num , poh.printed_date , pdt.type_name , ppf.full_name , poh.revised_date , poh.currency_code , pov.vendor_name , poh.acceptance_due_date , decode(poh.user_hold_flag,'Y',:yes,'N',:no,null) , pvs.vendor_site_code , decode(poh.acceptance_required_flag, 'Y',:yes, 'N',:no,null) , poh.po_header_id , decode(psp.manual_po_num_type,'NUMERIC', null,poh.segment1) , decode(psp.manual_po_num_type,'NUMERIC', decode(rtrim(poh.segment1,'0123456789'),NULL, to_number(poh.segment1),null),null) , plc_sta.displayed_field || decode(poh.cancel_flag, 'Y',', '||plc_can.displayed_field,NULL) || decode(nvl(poh.closed_code,'OPEN'), 'OPEN', NULL, ', '||plc_clo.displayed_field) || decode(poh.frozen_flag, 'Y',', '||plc_fro.displayed_field,NULL) || decode(poh.user_hold_flag, 'Y', ', '||plc_hld.displayed_field, NULL) , fc.precision ORDER BY pov.vendor_name , decode(psp.manual_po_num_type,'NUMERIC', null,poh.segment1) , decode(psp.manual_po_num_type,'NUMERIC', decode(rtrim(poh.segment1,'0123456789'),NULL, to_number(poh.segment1),null),null) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Buyer Name |
|
LOV Oracle | |
Items From |
|
Char | |
To |
|
Char | |
Categories From |
|
Char | |
To 2 |
|
Char | |
Vendors From |
|
LOV Oracle | |
To 3 |
|
LOV Oracle | |
PO Numbers From |
|
LOV Oracle | |
To 4 |
|
LOV Oracle | |
Status |
|
LOV Oracle | |
Dynamic Precision Option |
|
LOV Oracle |