PO Blanket and Planned PO Status

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Blanket and Planned PO Status Report (XML)
Short Name: POXPOBPS_XML
DB package: PO_POXPOBPS_XMLP_PKG
SELECT 
 min(poh.segment1)                         PO_Number
,        min(apt.name)                             Terms
,        min(poh.blanket_total_amount)             C_AMOUNT_AGR
,        min(poh.type_lookup_code)        po_type
,        min(papf.full_name)                       Buyer
,        min(poh.start_date)                      Effective_Date
,        min(poh.amount_limit)                 C_AMOUNT_LIM
,        min(pov.vendor_name)                 Vendor
,        min(poh.end_date)                        Expiration_date
,        min(pvs.vendor_site_code)                 Vendor_Site
,        min(decode(poh.cancel_flag,'Y',:yes,'N',:no,null)) Cancelled
,        min(T.type_name)                        Type
,        min(poh.global_agreement_flag)    global_agreement_flag
,        min(plc1.displayed_field)                 Approved
,        min(poh.currency_code)                    C_CURRENCY
,        min(poh.po_header_id)                     po_header_id
,        min(decode(psp1.manual_po_num_type,'NUMERIC',
                         to_number(poh.segment1), null))
,        min(decode(psp1.manual_po_num_type,'NUMERIC',
                         null, poh.segment1)), 
	PO_POXPOBPS_XMLP_PKG.c_amount_rem(min ( poh.po_header_id )) C_AMOUNT_REM, 
	PO_POXPOBPS_XMLP_PKG.c_amount_rel(min ( poh.po_header_id )) C_AMOUNT_REL, 
	PO_POXPOBPS_XMLP_PKG.cur_planned_amt_agreed(min ( poh.type_lookup_code ), min ( poh.po_header_id )) C_planned_po_amt_agreed, 
	PO_POXPOBPS_XMLP_PKG.cur_planned_amt_released(min ( poh.type_lookup_code ), min ( poh.po_header_id )) C_planned_po_amt_released
FROM     po_line_locations           pll
,        po_headers    poh
,        po_vendor_sites             pvs
,        po_vendors           pov
,        per_all_people_f   papf
,        po_lookup_codes     plc1
,        ap_terms           apt
,        po_system_parameters_all      psp1
,        PO_DOCUMENT_TYPES_ALL_TL   T
,        PO_DOCUMENT_TYPES_ALL_B      B
WHERE PAPF.BUSINESS_GROUP_ID = (SELECT NVL(MAX(FSP.BUSINESS_GROUP_ID),0) 
FROM FINANCIALS_SYSTEM_PARAMETERS FSP) 
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', 
HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F',PAPF.PERSON_ID, PAPF.PERSON_TYPE_ID,
PAPF.EMPLOYEE_NUMBER,PAPF.APPLICANT_NUMBER
,PAPF.NPW_NUMBER )) = 'TRUE'         
AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', PAPF.BUSINESS_GROUP_ID ,
HR_GENERAL.GET_BUSINESS_GROUP_ID) = PAPF.BUSINESS_GROUP_ID
AND B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE 
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE 
AND B.ORG_ID = T.ORG_ID   
AND B.ORG_ID = poh.ORG_ID  
AND T.LANGUAGE = USERENV('LANG')
AND  poh.type_lookup_code  in ( 'BLANKET','PLANNED')
AND      poh.type_lookup_code  = B.document_subtype
AND      B.document_type_code in ('PA', 'PO')
AND      poh.vendor_site_id    = pvs.vendor_site_id(+)
AND      poh.terms_id          = apt.term_id(+)
AND      poh.agent_id   = papf.person_id
AND      poh.vendor_id         = pov.vendor_id(+)
AND      poh.po_header_id      = pll.po_header_id(+)
AND      plc1.lookup_type      = 'DOCUMENT STATE'
AND      plc1.lookup_code      = nvl(poh.authorization_status,'INCOMPLETE')
AND       ((decode(psp1.manual_po_num_type,
                                      'NUMERIC',decode(ltrim(poh.segment1,'1234567890'),
                                    null,poh.segment1,
                                    null),
                   null)
           BETWEEN decode(psp1.manual_po_num_type,'NUMERIC',
                    to_number(nvl(:P_po_num_from,poh.segment1)),null)
           AND     decode(psp1.manual_po_num_type,'NUMERIC',
                    to_number(nvl(:P_po_num_to,poh.segment1)),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   psp1.ORG_ID = poh.ORG_ID   
AND      papf.full_name         = nvl(:P_buyer, papf.full_name)
AND      nvl(pov.vendor_name,'A') between
                               nvl(:P_vendor_from, nvl(pov.vendor_name,'A')) and
                               nvl(:P_vendor_to, nvl(pov.vendor_name,'A'))
AND      (poh.end_date  <= :P_expiration_date OR  :P_expiration_date is null)
AND      (:P_CATEGORY_FROM is null
          OR
           (EXISTS (SELECT 'Lines with correct items'
                    FROM    po_lines pol
                    ,       mtl_categories mca
                    WHERE   poh.po_header_id = pol.po_header_id
                    AND     pol.category_id  = mca.category_id
                    AND     &P_WHERE_CAT )))
GROUP BY pll.po_header_id
,        decode(psp1.manual_po_num_type, 'NUMERIC', null, poh.segment1)
,        decode(psp1.manual_po_num_type, 'NUMERIC', to_number(poh.segment1),
                null)
,        pov.vendor_name
ORDER BY &orderby_clause
Parameter Name SQL text Validation
P_STRUCT_NUM
 
Number
Dynamic Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
Expired Date
 
Date
To
 
Categories From
 
To
 
LOV Oracle
Vendors From
 
LOV Oracle
Buyer Name
 
LOV Oracle
To
 
LOV Oracle
PO Numbers From
 
LOV Oracle
Title