PO Purchase Summary Report By Category

Description
Categories: BI Publisher
Application: Purchasing
Source: Purchase Summary Report By Category (XML)
Short Name: POXSUCAT_XML
DB package: PO_POXSUCAT_XMLP_PKG
SELECT null                    C_FLEX_CAT
,      poh.segment1                   PO_Number             
,      T.type_name                 PO_Type
,      ppf.full_name                  Buyer
,      pov.vendor_name                Vendor
,      poh.creation_date              Creation_Date                
,      poh.currency_code              C_CURRENCY            
,     SUM ( DECODE ( POL.order_type_lookup_code,               
                                 'RATE', POLL.amount,
                                 'FIXED PRICE', POLL.amount,
                                 NVL (POLL.price_override, 0) * (NVL(POLL.quantity, 0) - NVL(POLL.quantity_cancelled, 0)))
              ) C_AMOUNT
  ,    ROUND(SUM ( DECODE ( POL.order_type_lookup_code,              
                                 'RATE', POLL.amount,
                                 'FIXED PRICE', POLL.amount,
                                 NVL (POLL.price_override, 0) * (NVL(POLL.quantity, 0) - NVL(POLL.quantity_cancelled, 0)))) *
                               NVL(POH.rate, 1), :C_PRECISION) C_AMOUNT_FUNC, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_sort', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') C_FLEX_CAT_SORT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP
FROM   per_people_f                   ppf
,      po_vendors                     pov
,      mtl_categories                 mca
,      po_line_locations           poll
,      po_lines                       pol
,      po_headers                     poh
,      po_document_types_all_tl   T
,      po_document_types_all_b    B
,      po_releases                    por
WHERE  pol.category_id                = mca.category_id
AND    pol.po_header_id               = poh.po_header_id
AND    poll.po_header_id              = poh.po_header_id
AND    poll.po_release_id              = por.po_release_id(+)
AND    poll.po_line_id	= pol.po_line_id
AND    pov.vendor_id  (+)             = poh.vendor_id 
AND    pov.vendor_name is not null
&WHERE_PERFORMANCE
AND    ppf.person_id                  = poh.agent_id
AND    trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
AND    B.document_type_code        in ('PO','PA')
AND    B.document_subtype          = poh.type_lookup_code 
and      poll.shipment_type not in ('PRICE BREAK','PLANNED')
 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    &P_WHERE_CAT     
GROUP BY  poh.segment1                       
,      T.type_name                
,      ppf.full_name                 
,      pov.vendor_name              
,      poh.creation_date             
,      poh.currency_code   
,     nvl(poh.rate,1),
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_sort', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') ,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
order by 10, 11, 2, 3, 4, 5, 6, 7, 8, 9
Parameter Name SQL text Validation
Title
 
Char
Categories From
 
Char
To
 
Char
Buyers From
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
To
 
LOV Oracle
Creation Dates From
 
Date
To
 
Date
P_STRUCT_NUM
 
Number
P_BASE_CURRENCY
 
LOV Oracle