PO Blanket and Planned PO Status

Description
Categories: BI Publisher
Imported from BI Publisher
Description: Blanket and Planned PO Status Report
Application: Purchasing
Source: Blanket and Planned PO Status Report (XML)
Short Name: POXPOBPS_XML
DB package: PO_POXPOBPS_XMLP_PKG
Run PO Blanket and Planned PO Status and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Operating Unit
 
LOV
Title
 
Char
PO Numbers From
 
LOV Oracle
To
 
LOV Oracle
Buyer Name
 
LOV Oracle
Vendors From
 
LOV Oracle
To 2
 
LOV Oracle
Categories From
 
Char
To 3
 
Char
Expired Date
 
Date
Sort By
 
LOV Oracle
Dynamic Precision Option
 
LOV Oracle
Blitz Report™