CST Historical Inventory Standard Cost Adjustment

SELECT  C.ORGANIZATION_ID SI_ORG_ID,
        C.SUBINVENTORY SI_SUBINV,
        SI.DESCRIPTION SI_SUBINV_DESC,
        null SI_ITEM_NUMBER,
        MSI.DESCRIPTION SI_ITEM_DESC,
        MSI.PRIMARY_UOM_CODE SI_UOM,
        null SI_MTL_ACCT,
        null SI_MOH_ACCT,
        null SI_RES_ACCT,
        null SI_OSP_ACCT,
        null SI_OVH_ACCT,
        null SI_ADJ_ACCT,
        ROUND(C.ADJUSTMENT_QUANTITY, :P_qty_precision) SI_ADJ_QUANTITY,
        C.COST_ELEMENT_ID SI_COST_ELEMENT_ID,
        SUBSTR(CE.COST_ELEMENT,1,15) SI_COST_ELEMENT,
        ROUND(C.OLD_UNIT_COST, :EXT_PREC) SI_OLD_UNIT_COST,
        ROUND(C.NEW_UNIT_COST, :EXT_PREC) SI_NEW_UNIT_COST,
        (ROUND(C.OLD_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) *
             :ROUND_UNIT) SI_OLD_EXT_COST,
        (ROUND(C.NEW_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) *
             :ROUND_UNIT) SI_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 SI_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) SI_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) SI_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) SI_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) SI_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) SI_OVH_ADJ, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('si_mtl_acct_seg', 'SQLGL', 'GL#', CC1.CHART_OF_ACCOUNTS_ID, NULL, CC1.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') SI_MTL_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('si_moh_acct_seg', 'SQLGL', 'GL#', CC2.CHART_OF_ACCOUNTS_ID, NULL, CC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') SI_MOH_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('si_res_acct_seg', 'SQLGL', 'GL#', CC3.CHART_OF_ACCOUNTS_ID, NULL, CC3.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') SI_RES_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('si_osp_acct_seg', 'SQLGL', 'GL#', CC4.CHART_OF_ACCOUNTS_ID, NULL, CC4.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') SI_OSP_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('si_ovh_acct_seg', 'SQLGL', 'GL#', CC5.CHART_OF_ACCOUNTS_ID, NULL, CC5.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') SI_OVH_ACCT_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('si_adj_acct_seg', 'SQLGL', 'GL#', CC6.CHART_OF_ACCOUNTS_ID, NULL, CC6.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') SI_ADJ_ACCT_SEG, 
	BOM_CSTRUSIA_XMLP_PKG.si_si_adjformula(:SI_SI_MTL, :SI_SI_MOH, :SI_SI_RES, :SI_SI_OSP, :SI_SI_OVH) SI_SI_ADJ, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('si_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') SI_ITEM_PSEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('si_item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') SI_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,
        MTL_SECONDARY_INVENTORIES SI,
        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.ORGANIZATION_ID = SI.ORGANIZATION_ID
AND     C.SUBINVENTORY = SI.SECONDARY_INVENTORY_NAME
AND     CC1.CODE_COMBINATION_ID (+) = SI.MATERIAL_ACCOUNT
AND     CC2.CODE_COMBINATION_ID (+) = SI.MATERIAL_OVERHEAD_ACCOUNT
AND     CC3.CODE_COMBINATION_ID (+) = SI.RESOURCE_ACCOUNT
AND     CC4.CODE_COMBINATION_ID (+) = SI.OUTSIDE_PROCESSING_ACCOUNT
AND     CC5.CODE_COMBINATION_ID (+) = SI.OVERHEAD_ACCOUNT
AND     CC6.CODE_COMBINATION_ID (+) = NVL(:P_ADJ_ACCOUNT,SI.MATERIAL_ACCOUNT) 
 and C.ORGANIZATION_ID=:ORGANIZATION_ID