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