PO Vendor Purchase Summary

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Vendor Purchase Summary Report
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
Operating Unit 
 
 LOV
Title 
 
 Char
Creation Dates From 
 
 Date
To 
 
 Date
Vendor Type 
 
 LOV Oracle
Small Business 
 
 LOV Oracle
Minority Owned 
 
 LOV Oracle
Women Owned 
 
 LOV Oracle
Sort By 
 
 LOV Oracle
Download
Related Blitz Reports
PO Purchase Order Detail PO Overshipments PO Receipt Adjustments PO Substitute Receipts AP Invoice on Hold 11i PO Purchase Order and Releases Detail PO Quotation Action Required PO Blanket and Planned PO Status CAC Intercompany SO Price List vs. Item Cost Comparison
Blitz Report™

Blitz Report™ provides multiple benefits: