PO Vendors on Hold

Description
Categories: BI Publisher, Procurement
Application: Purchasing
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
Dynamic Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
To
 
LOV Oracle
PO Numbers From
 
LOV Oracle
To
 
LOV Oracle
Vendor Names From
 
LOV Oracle
Title