CST Shipping Transaction Summary

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))