CST Shipping Transaction Summary

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Shipping Transaction Summary Report
Application: Bills of Material
Source: Shipping Transaction Summary Report (XML)
Short Name: CSTRSTSR_XML
DB package: BOM_CSTRSTSR_XMLP_PKG
Run CST Shipping Transaction Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Sort Option 
 
 LOV Oracle
Date From 
 
 Date
Date To 
 
 Date
Category Set 
 
 LOV Oracle
Currency 
 
 LOV Oracle
Exchange Rate 
 
 LOV Oracle
Download
 
Related Blitz Reports
MAD1 MAD CST Intransit Value COPY OF: INV Material Account Distribution Detail from Eng CAC Intercompany SO Price List vs. Item Cost Comparison INV Inactive items (2) INV Inactive items GW-Material Account Distributions GW Material Account Distribution Inventory mad
Blitz Report™

Blitz Report™ provides multiple benefits: