PO Open Purchase Orders Report(by Buyer)

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Open Purchase Orders Report(by Buyer) (XML)
Short Name: POXPOPAA_XML
DB package: PO_POXPOPAA_XMLP_PKG
SELECT distinct(pov.vendor_name||papf.full_name)
,      papf.full_name       Buyer
,      pov.vendor_name     Vendor
,      pov.vendor_id
,      papf. person_id employee_id, 
	PO_POXPOPAA_XMLP_PKG.cf_buyer_formulaformula(papf.full_name) CF_BUYER_FORMULA
FROM   PER_ALL_PEOPLE_F  PAPF
,      po_vendors          pov
,      po_headers          poh
WHERE  poh.agent_id      = papf.person_id
AND    poh.vendor_id     = pov.vendor_id
AND    poh.type_lookup_code in ('STANDARD','BLANKET','PLANNED')
AND    nvl(poh.closed_code,'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
AND    nvl(poh.cancel_flag,'N') = 'N'
AND    papf.person_id     = nvl(:P_buyer,papf.person_id)
AND    pov.vendor_name BETWEEN nvl(:P_vendor_from,pov.vendor_name)
                       AND     nvl(:P_vendor_to,pov.vendor_name)
AND EXISTS (select 'x'
            from   po_lines             pol
            ,      po_line_locations    pll
            ,      po_releases          por
            where  pol.po_header_id   = poh.po_header_id
            and    pol.po_line_id     = pll.po_line_id
            and    nvl(pll.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
            and    nvl(pol.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
            and    nvl(por.closed_code,'OPEN') not in ('FINALLY CLOSED','CLOSED')
            and    nvl(pll.cancel_flag,'N') = 'N'
            and    nvl(pol.cancel_flag,'N') = 'N'
            and    nvl(por.cancel_flag,'N') = 'N'
            and    pll.shipment_type in ('STANDARD','BLANKET','SCHEDULED')
            and    pll.po_release_id  = por.po_release_id(+))
            and    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
ORDER BY papf.full_name
,        pov.vendor_name
Parameter Name SQL text Validation
Dynamic Precision Option
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
Buyer Name
 
LOV Oracle
Title