CST Historical Inventory Standard Cost Adjustment Report - Warehouse Management

SELECT  C.ORGANIZATION_ID CG_ORG_ID,
        CCG.COST_GROUP CG_COST_GROUP,
        CCG.DESCRIPTION CG_CG_DESC,
        fnd_flex_xml_publisher_apis.process_kff_combination_1('item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CG_ITEM_NUMBER,
        MSI.DESCRIPTION CG_ITEM_DESC,
        MSI.PRIMARY_UOM_CODE CG_UOM,
        null CG_MTL_ACCT,
        null CG_MOH_ACCT,
        null CG_RES_ACCT,
        null CG_OSP_ACCT,
        null CG_OVH_ACCT,
        null CG_ADJ_ACCT,
        ROUND(C.ADJUSTMENT_QUANTITY, :P_qty_precision) CG_ADJ_QUANTITY,
        C.COST_ELEMENT_ID CG_COST_ELEMENT_ID,
        SUBSTR(CE.COST_ELEMENT,1,15) CG_COST_ELEMENT,
        ROUND(C.OLD_UNIT_COST, :EXT_PREC) CG_OLD_UNIT_COST,
        ROUND(C.NEW_UNIT_COST, :EXT_PREC) CG_NEW_UNIT_COST,
        (ROUND(C.OLD_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) *
             :ROUND_UNIT) CG_OLD_EXT_COST,
        (ROUND(C.NEW_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) *
             :ROUND_UNIT) CG_NEW_EXT_COST,
        (ROUND(C.NEW_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) -
         ROUND(C.OLD_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT)) *
             :ROUND_UNIT CG_ADJUSTMENT,
        DECODE(C.COST_ELEMENT_ID,
            1, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
                ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
               :ROUND_UNIT
        ,0) CG_MAT_ADJ,
        DECODE(C.COST_ELEMENT_ID,
            2, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
                ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
               :ROUND_UNIT
        ,0) CG_MOH_ADJ,
        DECODE(C.COST_ELEMENT_ID,
            3, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
                ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
               :ROUND_UNIT
        ,0) CG_RES_ADJ,
        DECODE(C.COST_ELEMENT_ID,
            4, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
                ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
               :ROUND_UNIT
        ,0) CG_OSP_ADJ,
        DECODE(C.COST_ELEMENT_ID,
            5, (ROUND(C.NEW_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT)-
                ROUND(C.OLD_UNIT_COST*C.ADJUSTMENT_QUANTITY/:ROUND_UNIT))*
               :ROUND_UNIT
        ,0) CG_OVH_ADJ, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_mtl_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_MTL_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_moh_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_MOH_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_res_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_RES_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_osp_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_OSP_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_ovh_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_OVH_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_adj_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') CG_ADJ_ACCT_SEG, 
	BOM_CSTRUSIW_XMLP_PKG.cg_cg_adjformula(:CG_CG_MTL, :CG_CG_MOH, :CG_CG_RES, :CG_CG_OSP, :CG_CG_OVH) CG_CG_ADJ, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') CG_ITEM_PSEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CG_ITEM_SEG
FROM    MTL_SYSTEM_ITEMS_VL MSI,
        GL_CODE_COMBINATIONS CC1,
        GL_CODE_COMBINATIONS CC2,
        GL_CODE_COMBINATIONS CC3,
        GL_CODE_COMBINATIONS CC4,
        GL_CODE_COMBINATIONS CC5,
        GL_CODE_COMBINATIONS CC6,
        CST_COST_ELEMENTS CE,
        CST_COST_GROUP_ACCOUNTS CGA,
        MTL_PARAMETERS MP,
        CST_COST_GROUPS CCG,
        ORG_ORGANIZATION_DEFINITIONS OOD,
        CST_STD_COST_ADJ_VALUES C
WHERE   C.COST_UPDATE_ID = :P_UPDATE_ID
AND     OOD.ORGANIZATION_ID = C.ORGANIZATION_ID
AND     C.OLD_UNIT_COST != C.NEW_UNIT_COST
AND     CE.COST_ELEMENT_ID = C.COST_ELEMENT_ID
AND     C.COST_ELEMENT_ID > 0
AND     C.TRANSACTION_TYPE = 1
AND     C.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND     C.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND     C.COST_GROUP_ID = CCG.COST_GROUP_ID
AND     C.ORGANIZATION_ID = CGA.ORGANIZATION_ID
AND     C.COST_GROUP_ID = CGA.COST_GROUP_ID
AND     C.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND     CC1.CODE_COMBINATION_ID  = DECODE(CCG.COST_GROUP_ID, 1, MP.MATERIAL_ACCOUNT, CGA.MATERIAL_ACCOUNT)
AND     CC2.CODE_COMBINATION_ID  = DECODE(CCG.COST_GROUP_ID, 1, MP.MATERIAL_OVERHEAD_ACCOUNT, CGA.MATERIAL_OVERHEAD_ACCOUNT)
AND     CC3.CODE_COMBINATION_ID  = DECODE(CCG.COST_GROUP_ID, 1, MP.RESOURCE_ACCOUNT, CGA.RESOURCE_ACCOUNT)
AND     CC4.CODE_COMBINATION_ID  = DECODE(CCG.COST_GROUP_ID, 1, MP.OUTSIDE_PROCESSING_ACCOUNT, CGA.OUTSIDE_PROCESSING_ACCOUNT)
AND     CC5.CODE_COMBINATION_ID  = DECODE(CCG.COST_GROUP_ID, 1, MP.OVERHEAD_ACCOUNT, CGA.OVERHEAD_ACCOUNT)
AND     CC6.CODE_COMBINATION_ID  = NVL(:P_ADJ_ACCOUNT,  DECODE(CCG.COST_GROUP_ID, 1, MP.MATERIAL_ACCOUNT, CGA.MATERIAL_ACCOUNT)) 
 and C.ORGANIZATION_ID=:ORGANIZATION_ID