PO Vendor Purchase Summary

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