PO Purchase Order and Releases Detail
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Purchase Order and Releases Detail Report
Application: Purchasing
Source: Purchase Order and Releases Detail Report (XML)
Short Name: POXBLREL_XML
DB package: PO_POXBLREL_XMLP_PKG
Description: Purchase Order and Releases Detail Report
Application: Purchasing
Source: Purchase Order and Releases Detail Report (XML)
Short Name: POXBLREL_XML
DB package: PO_POXBLREL_XMLP_PKG
Run
PO Purchase Order and Releases Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT distinct poh.segment1 PO_Number , por.creation_date Creation_Date , plc.displayed_field Status , por.revision_num Rev , por.printed_date Printed_Date , pdt.type_name Type , hre.full_name Buyer , por.revised_date Revised_Date , poh.currency_code C_CURRENCY , pov.vendor_name Vendor , por.acceptance_due_date Acceptance_Date , pvs.vendor_site_code Vendor_Site , decode(poh.user_hold_flag,'Y',:yes,'N',:no,null) On_Hold , decode(por.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), NVL(PLL.price_override, 0)*(NVL(PLL.quantity, 0) - NVL(PLL.quantity_cancelled, 0)))) C_AMOUNT_RELEASE , poh.po_header_id po_header_id2 , por.release_num Release , poh.blanket_total_amount C_AMOUNT_AGREED , poh.amount_limit C_AMOUNT_LIMIT , por.release_date Release_Date , pll.po_release_id , decode(psp1.manual_po_num_type,'NUMERIC', null,poh.segment1) , decode(psp1.manual_po_num_type,'NUMERIC', to_number(poh.segment1),null) FROM po_line_locations pll , po_lines pol , po_releases por , po_headers poh , po_vendor_sites pvs , po_vendors pov , hr_employees hre , po_system_parameters psp1 , po_document_types pdt , po_lookup_codes plc WHERE poh.vendor_id = pov.vendor_id (+) AND poh.agent_id = hre.employee_id AND pvs.vendor_site_id (+) = poh.vendor_site_id AND poh.po_header_id = pol.po_header_id AND pol.po_line_id = pll.po_line_id(+) AND pll.po_release_id = por.po_release_id(+) AND nvl(pll.shipment_type, 'BLANKET') in ('BLANKET', 'SCHEDULED') AND poh.type_lookup_code in ('BLANKET','PLANNED') AND plc.lookup_type = 'DOCUMENT STATE' AND plc.lookup_code = nvl(poh.authorization_status, 'INCOMPLETE') AND pdt.document_type_code in ('PA', 'PO') AND pdt.document_subtype = poh.type_lookup_code AND (:P_buyer is NULL OR :P_buyer = hre.employee_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) &P_Where_Query AND EXISTS (SELECT 'Category Flexfield Range' FROM mtl_categories mca WHERE pol.category_id = mca.category_id(+) AND &P_WHERE_CAT) AND ( ((pol.item_id IS NULL ) AND ( (:P_item_from IS NULL AND :P_item_to IS NULL) OR (:P_item_from IS NULL AND :P_item_to IS NOT NULL)) ) OR EXISTS (SELECT 'Item Flexfield Range' FROM mtl_system_items msi WHERE pol.item_id = msi.inventory_item_id AND nvl(msi.organization_id, :organization_id) = :organization_id AND nvl(pll.shipment_type, 'BLANKET') in ('BLANKET','SCHEDULED') AND &P_WHERE_ITEM)) GROUP BY pov.vendor_name , poh.segment1 , por.creation_date , plc.displayed_field , por.revision_num , por.printed_date , pdt.type_name , hre.full_name , por.revised_date , poh.currency_code , por.acceptance_due_date , pvs.vendor_site_code , decode(poh.user_hold_flag,'Y',:yes,'N',:no,null) , decode(por.acceptance_required_flag, 'Y',:yes, 'N',:no,null) , poh.po_header_id , poh.blanket_total_amount , poh.amount_limit , por.release_num , por.release_date , pll.po_release_id , decode(psp1.manual_po_num_type,'NUMERIC', null,poh.segment1) , decode(psp1.manual_po_num_type,'NUMERIC', to_number(poh.segment1),null) --ORDER BY &orderby_clause Order by 22,23,1,2,3,4,5,6,7,9,10,11,13,12,14,18,15,19,8,16,17,20,21 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Buyer Name |
|
LOV Oracle | |
Categories From |
|
Char | |
To |
|
Char | |
Items From |
|
Char | |
Items To |
|
Char | |
Vendors From |
|
LOV Oracle | |
To 2 |
|
LOV Oracle | |
PO Numbers From |
|
LOV Oracle | |
To 3 |
|
LOV Oracle |