CST Shipping Transaction Summary

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Shipping Transaction Summary Report (XML)
Short Name: CSTRSTSR_XML
DB package: BOM_CSTRSTSR_XMLP_PKG
select distinct
    &P_ITEM_SEG			item_number,
    &P_CAT_SEG			category,	
    msi.description			description,
    -sum(nvl(mmt.transaction_quantity, 0))	transaction_quantity,
   (round(nvl(mmt.actual_cost,0) * :PV_EXCHANGE_RATE / :PV_ROUND_UNIT ) * :PV_ROUND_UNIT) actual_cost,
    (round(-sum(nvl(mmt.transaction_quantity, 0))*nvl(mmt.actual_cost,0) * :PV_EXCHANGE_RATE / :PV_ROUND_UNIT ) * :PV_ROUND_UNIT)	extended_value, 
	BOM_CSTRSTSR_XMLP_PKG.c_orderformula(MSI.description || MSI.description || MSI.description || MSI.segment1 || MSI.segment2 || MSI.segment3, :ITEM_SEGMENT, :ITEM_PSEG, MC.segment1, :CATEGORY_SEGMENT, :CATEGORY_PSEG) C_ORDER, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY_SEGMENT, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') ITEM_PSEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') CATEGORY_PSEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_SEGMENT
from
    mtl_item_categories mic,
    mtl_categories      mc,
    mtl_system_items    msi,
    mtl_material_transactions   mmt
where
    mmt.organization_id = :P_ORG_ID and
    mmt.transaction_source_type_id  = 2 and
    nvl(mmt.logical_transaction,0) <> 1 and
    mmt.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
    &P_DATE_RANGE and
    msi.organization_id = :P_ORG_ID and
    msi.inventory_item_id = mic.inventory_item_id and
    msi.organization_id = mic.organization_id and
    mic.category_set_id = :P_CATEGORY_SET and
    mic.category_id = mc.category_id
having -sum(nvl(mmt.transaction_quantity, 0)) > 0
group by
   &P_ITEM_SEG, 
    &P_CAT_SEG,
    msi.description,
    mmt.actual_cost,fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('category_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')
order by C_ORDER,
fnd_flex_xml_publisher_apis.process_kff_combination_1('item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),
    &P_ITEM_SEG,
    &P_CAT_SEG,
    -sum(nvl(mmt.transaction_quantity, 0))
Parameter Name SQL text Validation
View Cost Privilege
 
Number
Quantity Precision
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
Chart of Accounts Id
 
Number
Organization Id
 
Number
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Category Set
 
LOV Oracle
Date To
 
Date
Date From
 
Date
Sort Option
 
LOV Oracle