CST Historical WIP Standard Cost Adjustment

SELECT  C.ORGANIZATION_ID ADJ_ORGANIZATION_ID
,       C.WIP_ENTITY_ID ADJ_WIP_ENTITY_ID
,       DECODE(C.TRANSACTION_TYPE, 3,3,4,4,5,5,10) TRANSACTION_CODE
,       LU2.MEANING TRANSACTION_TYPE
,       C.TRANSACTION_TYPE TRANS_TYPE
,       null ITEM_NUMBER
,       C.OPERATION_SEQ_NUM
,       BR.RESOURCE_CODE
,       DECODE(C.TRANSACTION_TYPE,
                4, NULL,
                5, NULL,
                LU1.MEANING) DESC_BASIS
,       DECODE(C.TRANSACTION_TYPE,
                3, MSI.PRIMARY_UOM_CODE,
                4, MSI.PRIMARY_UOM_CODE,
                5, MSI.PRIMARY_UOM_CODE,
                BR.UNIT_OF_MEASURE) UOM_CODE
,       C.RESOURCE_SEQ_NUM
,       LU3.MEANING STANDARD_RATE
,       to_number( ROUND(DECODE(C.TRANSACTION_TYPE, 7, NULL, 5, 0.5, 1)
                                      *C.ADJUSTMENT_QUANTITY, :P_QTY_PRECISION ))
ADJUSTMENT_QUANTITY
,       C.COST_ELEMENT_ID
,       NVL(LEVEL_TYPE,1) LEVEL_TYPE
,       SUBSTR(CE.COST_ELEMENT,1,11) COST_ELEMENT
,       ROUND(C.OLD_UNIT_COST, :EXT_PREC) OLD_UNIT_COST
,       ROUND(C.NEW_UNIT_COST, :EXT_PREC) NEW_UNIT_COST 
,       (ROUND(C.OLD_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) *
             :ROUND_UNIT) OLD_EXT_COST
,       (ROUND(C.NEW_UNIT_COST * C.ADJUSTMENT_QUANTITY / :ROUND_UNIT) *
             :ROUND_UNIT) 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 * C.IN_OUT_FLAG ADJUSTMENT,
       DECODE(C.COST_ELEMENT_ID,
            1, (C.NEW_UNIT_COST-C.OLD_UNIT_COST)* C.ADJUSTMENT_QUANTITY*
                C.IN_OUT_FLAG
        ,0) MTL_ADJ,
        DECODE(C.COST_ELEMENT_ID,
            2, (C.NEW_UNIT_COST-C.OLD_UNIT_COST)* C.ADJUSTMENT_QUANTITY*
                C.IN_OUT_FLAG
        ,0) MOH_ADJ,
       DECODE(C.COST_ELEMENT_ID,
            3, (C.NEW_UNIT_COST-C.OLD_UNIT_COST)* C.ADJUSTMENT_QUANTITY*
                C.IN_OUT_FLAG
        ,0) RES_ADJ,
       DECODE(C.COST_ELEMENT_ID,
            4, (C.NEW_UNIT_COST-C.OLD_UNIT_COST)* C.ADJUSTMENT_QUANTITY*
                C.IN_OUT_FLAG
        ,0) OSP_ADJ,
       DECODE(C.COST_ELEMENT_ID,
            5, (C.NEW_UNIT_COST-C.OLD_UNIT_COST)* C.ADJUSTMENT_QUANTITY*
                C.IN_OUT_FLAG
        ,0) OVH_ADJ, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('item_resource', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_RESOURCE, 
	BOM_CSTRUSJA_XMLP_PKG.new_cost2formula(C.TRANSACTION_TYPE, :NUC, :COUNT_QTY, :SUM_LEVEL) NEW_COST2, 
	BOM_CSTRUSJA_XMLP_PKG.adj_qty_displayformula(:COUNT_QTY, :ADJ_QTY_DSP, DECODE ( C.TRANSACTION_TYPE , 3 , 3 , 4 , 4 , 5 , 5 , 10 ), :SUM_LEVEL) ADJ_QTY_DISPLAY, 
	BOM_CSTRUSJA_XMLP_PKG.old_cost2formula(C.TRANSACTION_TYPE, :OUC, :COUNT_QTY, :SUM_LEVEL) OLD_COST2
FROM    MTL_SYSTEM_ITEMS MSI
,       BOM_RESOURCES BR
,       MFG_LOOKUPS LU1
,       MFG_LOOKUPS LU2
,       MFG_LOOKUPS LU3
,       CST_COST_ELEMENTS CE
,       CST_STD_COST_ADJ_VALUES C
WHERE   C.COST_UPDATE_ID = :P_UPDATE_ID
AND     C.TRANSACTION_TYPE > 2
AND     C.RESOURCE_ID = BR.RESOURCE_ID (+)
AND     LU1.LOOKUP_TYPE = 'CST_BASIS_SHORT'
AND     NVL(C.BASIS_TYPE,1) = LU1.LOOKUP_CODE
AND     C.COST_ELEMENT_ID = CE.COST_ELEMENT_ID
AND     LU2.LOOKUP_TYPE = 'CST_UPDATE_TXN_TYPE'
AND     LU2.LOOKUP_CODE = DECODE(C.TRANSACTION_TYPE,3,3,4,4,5,5,10)
AND     LU3.LOOKUP_TYPE (+) = 'SYS_YES_NO'
AND     LU3.LOOKUP_CODE (+) = C.STANDARD_RATE_FLAG
AND     C.ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND     C.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND     (C.TRANSACTION_TYPE in (8,9)
        OR C.OLD_UNIT_COST != C.NEW_UNIT_COST
        OR EXISTS (
                   SELECT NULL
                   FROM   CST_STD_COST_ADJ_VALUES C2
                   WHERE  C2.COST_UPDATE_ID = :P_UPDATE_ID
                   AND    C2.TRANSACTION_TYPE = 7
                   AND    C.TRANSACTION_TYPE = 6
                   AND    C2.BASIS_TYPE BETWEEN 3 AND 4
                   AND    C2.WIP_ENTITY_ID = C.WIP_ENTITY_ID
                   AND    C2.OPERATION_SEQ_NUM = C.OPERATION_SEQ_NUM
                   AND    C2.RESOURCE_SEQ_NUM = C.RESOURCE_SEQ_NUM
                  )
         ) 
 and C.ORGANIZATION_ID=:ORGANIZATION_ID 
 and C.WIP_ENTITY_ID=:WIP_ENTITY_ID