|
SELECT B.PROCESS_INSTANCE ,B.BUSINESS_UNIT ,B.CM_BOOK ,B.INV_ITEM_ID ,SUM(A.CM_UNIT_COST) , B.UNIT_MEASURE_STD FROM PS_CM_ACTUAL_COSTB A , PS_CM_UCDIST_TMP B WHERE %Join(common_fields, CM_ACTUAL_COSTB A, CM_UCDIST_TMP B, process_instance) AND A.EFF_STATUS = 'A' AND B.CM_METHOD = '1' AND A.DT_TIMESTAMP = ( SELECT MAX(AA.DT_TIMESTAMP) FROM PS_CM_ACTUAL_COSTB AA WHERE %Join(COMMON_KEYS, CM_ACTUAL_COSTB A, CM_ACTUAL_COSTB AA, PROCESS_INSTANCE, DT_TIMESTAMP) AND AA.DT_TIMESTAMP <= %CurrentDateTimeIn) AND A.CM_SEQ_COST = ( SELECT MAX(BB.CM_SEQ_COST) FROM PS_CM_ACTUAL_COSTB BB WHERE %Join(COMMON_KEYS, CM_ACTUAL_COST A, CM_ACTUAL_COST BB, PROCESS_INSTANCE, DT_TIMESTAMP) AND A.DT_TIMESTAMP = BB.DT_TIMESTAMP) GROUP BY B.PROCESS_INSTANCE, B.BUSINESS_UNIT, B.CM_BOOK, B.INV_ITEM_ID, B.CM_METHOD, B.UNIT_MEASURE_STD
|