CST Transaction Value Historical Summary - Average/FIFO/LIFO Costing

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Transaction Value Historical Summary - Average/FIFO/LIFO Costing
Application: Bills of Material
Source: Transaction Value Historical Summary - Average/FIFO/LIFO Costing (XML)
Short Name: CSTRTVHA_XML
DB package: BOM_CSTRTVHA_XMLP_PKG
Run CST Transaction Value Historical Summary - Average/FIFO/LIFO Costing and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT CCG.cost_group cg,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSIVL.ORGANIZATION_ID, MSIVL.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item,
       MSIVL.description description,
       MSIVL.primary_uom_code uom,
       null category,
       ROUND(
         SUM(CIQT.rollback_qty) * PAST_COST.item_cost, 
         :PV_STD_PREC
       ) oldval, 
       ROUND(
         SUM(DECODE(CIQT.txn_source_type_id, :P_STYPE1, NVL(CIQT.rollback_value,0),0)),
         :PV_STD_PREC
       )  s1val,
       ROUND(
         SUM(DECODE(CIQT.txn_source_type_id, :P_STYPE2, NVL(CIQT.rollback_value,0),0)),
         :PV_STD_PREC
       ) s2val,
       ROUND(
         SUM(DECODE(CIQT.txn_source_type_id, :P_STYPE3, NVL(CIQT.rollback_value,0),0)),
         :PV_STD_PREC
       ) s3val,
       ROUND(
         SUM(DECODE(CIQT.txn_source_type_id, :P_STYPE4, NVL(CIQT.rollback_value,0),0)),
         :PV_STD_PREC
       ) s4val,
       ROUND(
         SUM(
           DECODE(
             CIQT.qty_source,
             3,CIQT.rollback_qty, 
             4,CIQT.rollback_qty, 
             6,CIQT.rollback_qty, 
             7,CIQT.rollback_qty, 
             0) *
           CURRENT_COST.item_cost
         ),
         :PV_STD_PREC
       ) newval, 
	--BOM_CSTRTVHA_XMLP_PKG.cf_orderformula(MCK.segment1) CF_ORDER, 
	decode(:P_SORT_OPTION,1,fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MCK.STRUCTURE_ID, NULL, MCK.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),null) CF_ORDER,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MCK.STRUCTURE_ID, NULL, MCK.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CF_CATEGORY, 
	BOM_CSTRTVHA_XMLP_PKG.cf_1formula(ROUND ( SUM ( DECODE ( CIQT.qty_source , 3 , CIQT.rollback_qty , 4 , CIQT.rollback_qty , 6 , CIQT.rollback_qty , 7 , CIQT.rollback_qty , 0 ) * CURRENT_COST.item_cost ) , :PV_STD_PREC ), ROUND ( SUM ( CIQT.rollback_qty ) * PAST_COST.item_cost , :PV_STD_PREC ), ROUND ( SUM ( DECODE ( CIQT.txn_source_type_id , :P_STYPE1 , NVL ( CIQT.rollback_value , 0 ) , 0 ) ) , :PV_STD_PREC ), ROUND ( SUM ( DECODE ( CIQT.txn_source_type_id , :P_STYPE2 , NVL ( CIQT.rollback_value , 0 ) , 0 ) ) , :PV_STD_PREC ), ROUND ( SUM ( DECODE ( CIQT.txn_source_type_id , :P_STYPE3 , NVL ( CIQT.rollback_value , 0 ) , 0 ) ) , :PV_STD_PREC ), ROUND ( SUM ( DECODE ( CIQT.txn_source_type_id , :P_STYPE4 , NVL ( CIQT.rollback_value , 0 ) , 0 ) ) , :PV_STD_PREC )) s5val
FROM   cst_inv_qty_temp CIQT,
       cst_inv_cost_temp CURRENT_COST,
       cst_inv_cost_temp PAST_COST,
       mtl_system_items_vl MSIVL,
       mtl_categories_kfv MCK,  
       cst_cost_groups CCG
WHERE  CURRENT_COST.organization_id = CIQT.organization_id
AND    CURRENT_COST.inventory_item_id = CIQT.inventory_item_id
AND    CURRENT_COST.cost_group_id = CIQT.cost_group_id
AND    CURRENT_COST.cost_source = 1
AND    PAST_COST.organization_id = CIQT.organization_id
AND    PAST_COST.inventory_item_id = CIQT.inventory_item_id
AND    PAST_COST.cost_group_id = CIQT.cost_group_id
AND    PAST_COST.cost_source= 2          
AND    MSIVL.organization_id = CIQT.organization_id
AND    MSIVL.inventory_item_id = CIQT.inventory_item_id
AND    MCK.category_id  = CIQT.category_id
AND    CCG.cost_group_id = CIQT.cost_group_id
GROUP
BY     CCG.cost_group,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSIVL.ORGANIZATION_ID, MSIVL.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),
       MSIVL.description,
       MSIVL.primary_uom_code,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MCK.STRUCTURE_ID, NULL, MCK.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
       PAST_COST.item_cost,
       CURRENT_COST.item_cost
ORDER BY 1 ASC
Parameter Name SQL text Validation
Title 
 
 Char
Cost Group Option 
 
 LOV Oracle
Specific Cost Group 
 
 LOV Oracle
Sort Option 
 
 LOV Oracle
As of Date 
 
 DateTime
Category Set 
 
 LOV Oracle
Categories From 
 
 Char
To 
 
 Char
Items From 
 
 Char
To 2 
 
 Char
Source Type for Column One 
 
 LOV Oracle
Source Type for Column Two 
 
 LOV Oracle
Source Type for Column Three 
 
 LOV Oracle
Source Type for Column Four 
 
 LOV Oracle
Include Intransit 
 
 LOV Oracle
Download
Related Blitz Reports
INV Transaction Historical Summary CST Intransit Value CST Inventory Value Report - by Cost Group CST All Inventories Value Report - by Cost Group CST Elemental Inventory Value Report - by Cost Group CST Inventory Value - Multi-Organization (Item Costs) CST Inventory Value Report - by Subinventory (Item Cost) CST Margin Analysis Report with Order Management CST All Inventories Value (9)
Blitz Report™

Blitz Report™ provides multiple benefits: