PO Vendor Purchase Summary

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Vendor Purchase Summary Report (XML)
Short Name: POXPOVPS_XML
DB package: PO_POXPOVPS_XMLP_PKG
SELECT   decode (:P_ORDERBY,'VENDOR TYPE',pov.vendor_type_lookup_code,pov.vendor_name) sort_column
,	pov.vendor_name                           Vendor
,        pov.segment1                             Vendor_Number
,        pov.vendor_type_lookup_code               Vendor_Type,
        decode(pov.small_business_flag,'Y',
                :yes,:no)                          Small_Business
,        decode(pov.women_owned_flag,'Y',
                :yes,:no)                          Women_Owned
,        decode(psp1.manual_po_num_type, 'NUMERIC',
                null, poh.segment1)
,        decode(psp1.manual_po_num_type, 'NUMERIC',
                to_number(poh.segment1), null)
,        plc1.displayed_field                      Minority_Owned
,        pvs.vendor_site_code                      Vendor_Site
,        plkc.displayed_field                      PO_Type
,        poh.segment1                              PO_Number
,        poh.revision_num                          Rev
,        trunc(poh.creation_date)                  Creation_Date
,        poh.revised_date                          Revised_Date
,        poh.currency_code                         C_CURRENCY
,        fc.precision                                     po_currency_precision
,        poh.comments                              Description
,        sum(pll.price_override * (nvl(pll.quantity,0)
                      -nvl(pll.quantity_cancelled,0)))  C_AMOUNT_PO
,        sum(pll.price_override * (nvl(pll.quantity,0)
                      -nvl(pll.quantity_cancelled,0))
                      * nvl(poh.rate,1))                C_AMOUNT_FUNCTIONAL,
	PO_POXPOVPS_XMLP_PKG.C_amount_func_vendor_round_p C_amount_func_vendor_round,
	PO_POXPOVPS_XMLP_PKG.C_amount_rep_round_p C_amount_rep_round,
	PO_POXPOVPS_XMLP_PKG.C_amount_func_site_round_p C_amount_func_site_round,
	PO_POXPOVPS_XMLP_PKG.C_amount_func_po_type_round_p C_amount_func_po_type_round,
	PO_POXPOVPS_XMLP_PKG.C_amount_po_round_p C_amount_po_round,
	PO_POXPOVPS_XMLP_PKG.C_amount_functional_round_p C_amount_functional_round
FROM        po_line_locations       pll
 ,        po_headers              poh
,        po_vendor_sites         pvs
,        po_vendors                  pov
,        po_lookup_codes         plkc
,        po_system_parameters    psp1
,        po_lookup_codes   plc1
,        fnd_currencies        fc
WHERE    pov.vendor_id           = pvs.vendor_id
AND      pov.vendor_id           = poh.vendor_id
AND      poh.po_header_id        = pll.po_header_id
AND      pvs.vendor_site_id      = poh.vendor_site_id
AND      fc.currency_code         = poh.currency_code
AND      pov.minority_group_lookup_code 	= plc1.lookup_code(+)
AND      plc1.lookup_type(+)  = 'MINORITY GROUP'
AND      poh.type_lookup_code    = plkc.lookup_code
AND      plkc.lookup_type        = 'PO TYPE'
AND      plkc.lookup_code        not in ('QUOTATION','RFQ')
AND      (pll.po_release_id is not null
               OR poh.type_lookup_code not in ('BLANKET', 'PLANNED'))
AND      ((poh.type_lookup_code  != 'PLANNED' AND
           trunc(poh.creation_date)     BETWEEN nvl(:P_creation_date_from, trunc(poh.creation_date) - 1)
                                 AND     nvl(:P_creation_date_to, trunc(poh.creation_date) + 1))
           OR
        (poh.type_lookup_code    = 'PLANNED' AND
         poh.start_date          BETWEEN nvl(:P_creation_date_from, poh.start_date - 1)
                                 AND     nvl(:P_creation_date_to, poh.start_date + 1)
         AND pll.shipment_type   = 'PLANNED'))
AND      nvl(pov.vendor_type_lookup_code,'-1') = decode(:P_vendor_type, null,
         nvl(pov.vendor_type_lookup_code,'-1'), :P_vendor_type)
AND      nvl(:P_SMALL_BUSINESS,
             pov.small_business_flag) = pov.small_business_flag
AND      nvl(:P_WOMEN_OWNED,pov.women_owned_flag) = pov.women_owned_flag
AND      ((:P_MINORITY_OWNED          = 'Y'
           and  pov.minority_group_lookup_code is not null)
     OR   (:P_MINORITY_OWNED          = 'N'
           and  pov.minority_group_lookup_code is null)
     OR   (:P_MINORITY_OWNED is null))
GROUP BY poh.po_header_id,
         poh.segment1,
         pov.small_business_flag,
         pov.women_owned_flag,
         plc1.displayed_field,
         pov.vendor_name,
         pov.vendor_id,
         pov.vendor_type_lookup_code,
         pvs.vendor_site_code,
         poh.revision_num,
         poh.creation_date,
         poh.revised_date,
         plkc.displayed_field,
         pvs.vendor_site_id,
         pov.segment1,
         poh.currency_code,
         fc.precision,
         poh.comments,
         psp1.manual_po_num_type
ORDER BY &orderby_clause
Parameter Name SQL text Validation
P_BASE_CURRENCY
 
LOV Oracle
Sort By
 
LOV Oracle
Women Owned
 
LOV Oracle
Minority Owned
 
LOV Oracle
Small Business
 
LOV Oracle
Vendor Type
 
LOV Oracle
To
 
Date
Creation Dates From
 
Date
Title
 
Ask a question