PO Purchase Agreement Audit

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