PO Vendors on Hold
Description
Categories: BI Publisher
Application: Purchasing
Source: Vendors on Hold Report (XML)
Short Name: POXVDVOH_XML
DB package: PO_POXVDVOH_XMLP_PKG
Source: Vendors on Hold Report (XML)
Short Name: POXVDVOH_XML
DB package: PO_POXVDVOH_XMLP_PKG
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Title |
|
Char | |
Vendor Names From |
|
LOV Oracle | |
To |
|
LOV Oracle | |
To |
|
LOV Oracle | |
PO Numbers From |
|
LOV Oracle | |
Sort By |
|
LOV Oracle | |
Dynamic Precision Option |
|
LOV Oracle |