PO Purchase Agreement Audit
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Purchase Agreement Audit Report
Application: Purchasing
Source: Purchase Agreement Audit Report (XML)
Short Name: POXPOABP_XML
DB package: PO_POXPOABP_XMLP_PKG
Description: Purchase Agreement Audit Report
Application: Purchasing
Source: Purchase Agreement Audit Report (XML)
Short Name: POXPOABP_XML
DB package: PO_POXPOABP_XMLP_PKG
Run
PO Purchase Agreement Audit and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT poh.segment1 || '-' || to_char(poh.revision_num) Blanket_PO_rev , pol.line_num Line , pov.vendor_name Vendor , poh.start_date Effective_date , poh.end_date Expiration_date , poh.currency_code C_CURRENCY , pol.unit_meas_lookup_code Unit , to_number(decode(plt.order_type_lookup_code,'AMOUNT', NULL, round(pol.quantity_committed,:P_qty_precision))) Quantity_agreed , to_number(decode(plt.order_type_lookup_code,'AMOUNT', NULL, pol.unit_price)) Unit_price , plt.line_type Line_type , pol.unit_price * pol.quantity_committed C_AMOUNT_AGR , pol.unit_price C_BLANKET_PRICE , pol.item_id FROM po_lines pol , po_line_types plt , po_headers poh , po_vendors pov , po_system_parameters psp , mtl_system_items msi WHERE pol.po_header_id = poh.po_header_id AND msi.organization_id = :organization_id AND msi.inventory_item_id = pol.item_id AND poh.type_lookup_code = 'BLANKET' AND poh.vendor_id = pov.vendor_id (+) AND ((decode(psp.manual_po_num_type,'NUMERIC',decode(rtrim(poh.segment1,'0123456789'),NULL,to_number(poh.segment1),-1),null) BETWEEN decode(psp.manual_po_num_type,'NUMERIC',decode(rtrim(nvl(:P_blanket_po_num_from,poh.segment1),'0123456789'),NULL,to_number(nvl(:P_blanket_po_num_from,poh.segment1)),-1),null) AND decode(psp.manual_po_num_type,'NUMERIC',decode(rtrim(nvl(:P_blanket_po_num_to,poh.segment1),'0123456789'),NULL,to_number(nvl(:P_blanket_po_num_to,poh.segment1)),-1),null)) OR (poh.segment1 BETWEEN decode(psp.manual_po_num_type,'ALPHANUMERIC',nvl(:P_blanket_po_num_from,poh.segment1),null) AND decode(psp.manual_po_num_type,'ALPHANUMERIC',nvl(:P_blanket_po_num_to,poh.segment1),null))) AND pol.line_type_id = plt.line_type_id AND EXISTS ( SELECT 'Find matching standard POs.' FROM po_lines pol1 , po_headers poh1 , hr_employees hre WHERE pol1.po_header_id = poh1.po_header_id AND poh1.type_lookup_code = 'STANDARD' AND pol1.item_id = pol.item_id AND pol1.line_type_id = pol.line_type_id AND pol1.creation_date BETWEEN nvl(poh.start_date, pol1.creation_date - 1) AND nvl(poh.end_date, pol1.creation_date + 1) AND nvl(pol.cancel_flag, 'N') = 'N' AND pol1.creation_date BETWEEN nvl(:P_creation_date_from,pol1.creation_date - 1) AND nvl(:P_creation_date_to, pol1.creation_date + 1) AND poh1.agent_id = hre.employee_id AND hre.full_name = nvl(:P_buyer,hre.full_name)) AND pol.item_id = :inventory_item_id and pol.item_id=:inventory_item_id ORDER BY 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),-1), null) , poh.revision_num , pol.line_num |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Title |
|
Char | |
Buyer |
|
LOV Oracle | |
Blanket PO Numbers From |
|
LOV Oracle | |
To |
|
LOV Oracle | |
Creation Dates From |
|
Date | |
To 2 |
|
Date | |
Categories From |
|
Char | |
To 3 |
|
Char | |
Items From |
|
Char | |
To 4 |
|
Char | |
Sort By |
|
LOV Oracle | |
Dynamic Precision Option |
|
LOV Oracle |