|
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_PERRAVG_COST A , PS_CM_UCDIST_TMP B WHERE %Join(common_fields, CM_PERRAVG_COST A, CM_UCDIST_TMP B, process_instance) AND B.CM_METHOD = '9' AND A.CM_DUMMY_FLG = 'N' AND A.DT_TIMESTAMP = ( SELECT MAX(AA.DT_TIMESTAMP) FROM PS_CM_PERRAVG_COST AA WHERE %Join(COMMON_KEYS, CM_PERRAVG_COST A, CM_PERRAVG_COST AA, PROCESS_INSTANCE, DT_TIMESTAMP) AND AA.DT_TIMESTAMP <= %CurrentDateTimeIn ) AND A.CM_SEQ_COST = ( SELECT MAX(BB.CM_SEQ_COST) FROM PS_CM_PERRAVG_COST BB WHERE %Join(COMMON_KEYS, CM_PERRAVG_COST A, CM_PERRAVG_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
|