|
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_PERPAVG_COST A , PS_CM_UCDIST_TMP B WHERE %Join(common_fields, CM_PERPAVG_COST A, CM_UCDIST_TMP B, process_instance) AND B.CM_METHOD = '6' AND A.DT_TIMESTAMP = ( SELECT MAX(AA.DT_TIMESTAMP) FROM PS_CM_PERPAVG_COST AA WHERE %Join(COMMON_KEYS, CM_PERPAVG_COST A, CM_PERPAVG_COST AA, PROCESS_INSTANCE, DT_TIMESTAMP) AND AA.DT_TIMESTAMP <= %CurrentDateTimeIn AND AA.SEQ_NBR = ( SELECT MAX(BB.SEQ_NBR) FROM PS_CM_PERPAVG_COST BB WHERE %Join(COMMON_KEYS, CM_PERPAVG_COST AA, CM_PERPAVG_COST BB, PROCESS_INSTANCE, DT_TIMESTAMP) AND AA.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
|