PO Vendors on Hold

SELECT   poh.po_header_id            PO_header_id
,        pov.vendor_name             Vendor
,        pov.segment1                Vendor_number
,        pov.purchasing_hold_reason  Hold_reason
,        pov.hold_date               Hold_date
,        hre2.full_name              Held_by
,        poh.segment1                PO_number
,        hre1.full_name              Buyer
,        poh.creation_date           Creation_date
,        plkc.displayed_field        Status
,        poh.currency_code           C_CURRENCY
,        decode(psp1.manual_po_num_type, 'NUMERIC', null, poh.segment1)
,        decode(psp1.manual_po_num_type, 'NUMERIC',decode(rtrim(poh.segment1,'0123456789'),null,to_number(poh.segment1),-1),
                     null)
,        sum((poll.quantity-poll.quantity_cancelled) 
                * poll.price_override) C_AMOUNT
FROM     hr_employees                hre1
,        hr_employees                hre2
,        po_system_parameters        psp1
,        po_lookup_codes             plkc
,        po_vendors                  pov
,        po_headers                  poh
,        po_line_locations   poll  
WHERE    nvl(poh.authorization_status, 'INCOMPLETE') = plkc.lookup_code
AND      plkc.lookup_type            = 'DOCUMENT STATE'
AND      poh.vendor_id               = pov.vendor_id
AND      poh.po_header_id            = poll.po_header_id
AND      poh.agent_id                = hre1.employee_id
AND      hre2.employee_id (+)        = pov.hold_by
AND      nvl(poll.cancel_flag,'N')  != 'Y'
AND      nvl(poll.closed_code,'OPEN')  NOT IN ('CLOSED','CLOSED FOR RECEIVING','FINALLY CLOSED')
AND      nvl(pov.hold_flag,'Y')      = 'Y'
AND      ((decode(psp1.manual_po_num_type,'NUMERIC',decode(rtrim(poh.segment1,'0123456789'),NULL,to_number(poh.segment1),-1),null) 
         BETWEEN decode(psp1.manual_po_num_type,'NUMERIC',decode(rtrim(nvl(:P_po_num_from,poh.segment1),'0123456789'),NULL,to_number(nvl(:P_po_num_from,poh.segment1)),-1),null)
         AND     decode(psp1.manual_po_num_type,'NUMERIC',decode(rtrim(nvl(:P_po_num_to,poh.segment1),'0123456789'),NULL,to_number(nvl(:P_po_num_to,poh.segment1)),-1),null))
OR       (poh.segment1
         BETWEEN decode(psp1.manual_po_num_type,'ALPHANUMERIC',nvl(:P_po_num_from,poh.segment1),null)
         AND     decode(psp1.manual_po_num_type,'ALPHANUMERIC',nvl(:P_po_num_to,poh.segment1),null)))
AND      pov.vendor_name             
                   BETWEEN nvl(:P_vendor_from, pov.vendor_name)
                   AND     nvl(:P_vendor_to, pov.vendor_name)
GROUP BY poh.po_header_id            
,        pov.vendor_name             
,        pov.segment1                
,        pov.purchasing_hold_reason  
,        pov.hold_date              
,        hre2.full_name              
,        poh.segment1                
,        hre1.full_name              
,        poh.creation_date          
,        plkc.displayed_field        
,        poh.currency_code           
,        decode(psp1.manual_po_num_type, 'NUMERIC',decode(rtrim(poh.segment1,'0123456789'),null,to_number(poh.segment1),-1),
                     null)
,        decode(psp1.manual_po_num_type, 'NUMERIC', null, poh.segment1)
ORDER BY &orderby_clause

SELECT poh.po_header_id PO_header_id , pov.vendor_name Vendor , pov.segment1 Vendor_number , pov.purchasing_hold_reason Hold_reason , pov.hold_date Hold_date , hre2.full_name Held_by , poh.segment1 PO_number , hre1.full_name Buyer , poh.creation_date Creation_date , plkc.displayed_field Status , poh.currency_code C_CURRENCY , decode(psp1.manual_po_num_type, 'NUMERIC', null, poh.segment1) , decode(psp1.manual_po_num_type, 'NUMERIC',decode(rtrim(poh.segment1,'0123456789'),null,to_number(poh.segment1),-1), null) , sum((poll.quantity-poll.quantity_cancelled) * poll.price_override) C_AMOUNT FROM hr_employees hre1 , hr_employees hre2 , po_system_parameters psp1 , po_lookup_codes plkc , po_vendors pov , po_headers poh , po_line_locations poll WHERE nvl(poh.authorization_status, 'INCOMPLETE') = plkc.lookup_code AND plkc.lookup_type = 'DOCUMENT STATE' AND poh.vendor_id = pov.vendor_id AND poh.po_header_id = poll.po_header_id AND poh.agent_id = hre1.employee_id AND hre2.employee_id (+) = pov.hold_by AND nvl(poll.cancel_flag,'N') != 'Y' AND nvl(poll.closed_code,'OPEN') NOT IN ('CLOSED','CLOSED FOR RECEIVING','FINALLY CLOSED') AND nvl(pov.hold_flag,'Y') = 'Y' AND ((decode(psp1.manual_po_num_type,'NUMERIC',decode(rtrim(poh.segment1,'0123456789'),NULL,to_number(poh.segment1),-1),null) BETWEEN decode(psp1.manual_po_num_type,'NUMERIC',decode(rtrim(nvl(:P_po_num_from,poh.segment1),'0123456789'),NULL,to_number(nvl(:P_po_num_from,poh.segment1)),-1),null) AND decode(psp1.manual_po_num_type,'NUMERIC',decode(rtrim(nvl(:P_po_num_to,poh.segment1),'0123456789'),NULL,to_number(nvl(:P_po_num_to,poh.segment1)),-1),null)) OR (poh.segment1 BETWEEN decode(psp1.manual_po_num_type,'ALPHANUMERIC',nvl(:P_po_num_from,poh.segment1),null) AND decode(psp1.manual_po_num_type,'ALPHANUMERIC',nvl(:P_po_num_to,poh.segment1),null))) AND pov.vendor_name BETWEEN nvl(:P_vendor_from, pov.vendor_name) AND nvl(:P_vendor_to, pov.vendor_name) GROUP BY poh.po_header_id , pov.vendor_name , pov.segment1 , pov.purchasing_hold_reason , pov.hold_date , hre2.full_name , poh.segment1 , hre1.full_name , poh.creation_date , plkc.displayed_field , poh.currency_code , decode(psp1.manual_po_num_type, 'NUMERIC',decode(rtrim(poh.segment1,'0123456789'),null,to_number(poh.segment1),-1), null) , decode(psp1.manual_po_num_type, 'NUMERIC', null, poh.segment1) ORDER BY &orderby_clause