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

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Transaction Value Historical Summary - Average/FIFO/LIFO Costing (XML)
Short Name: CSTRTVHA_XML
DB package: BOM_CSTRTVHA_XMLP_PKG
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
Category Structure
 
Number
INV_SRS_HAN_SELECTION
 
LOV Oracle
CST_SRS_COST_GROUP_DUMMY
 
Number
Dynamic Precision Option
 
LOV Oracle
Organization
 
Number
Include Intransit
 
LOV Oracle
Source Type for Column Four
 
LOV Oracle
Source Type for Column Three
 
LOV Oracle
Source Type for Column Two
 
LOV Oracle
Source Type for Column One
 
LOV Oracle
To
 
Items From
 
To
 
Categories From
 
Category Set
 
LOV Oracle
As of Date
 
DateTime
Sort Option
 
LOV Oracle
Specific Cost Group
 
LOV Oracle
Cost Group Option
 
LOV Oracle
Title