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

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