PO Purchase Agreement Audit

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Purchase Agreement Audit Report (XML)
Short Name: POXPOABP_XML
DB package: PO_POXPOABP_XMLP_PKG
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
P_STRUCT_NUM
 
Number
Dynamic Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
To
 
Items From
 
To
 
Categories From
 
To
 
Date
Creation Dates From
 
Date
To
 
LOV Oracle
Blanket PO Numbers From
 
LOV Oracle
Buyer
 
LOV Oracle
Title