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 |