Product: | PMI - Process Manufacturing Intelligence (Obsolete) |
---|---|
Description: | |
Implementation/DBA Data: | Not implemented in this database |
SELECT IT.DOC_ID||'-'||IT.ITEM_ID||'-'||IT.LOT_ID||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, IT.ORGN_CODE||'-'||IT.ORGN_CODE||'-'||'PORG'||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, '0'||'-'||IM.ITEM_NO||'-'||IT.WHSE_CODE
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(IT.TRANS_DATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SUBSTR(PMI_PRODUCTION_SUM.FIND_PROD_GRADE(IT.TRANS_ID
, IT.ITEM_ID
, IT.LOT_ID)
, 1
, 4)||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, 'NA_EDW'
, INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, SUBSTR(PMI_PRODUCTION_SUM.FIND_PROD_GRADE(IT.TRANS_ID
, IT.ITEM_ID
, IT.LOT_ID)
, 1
, 4)
, IT.TRANS_QTY
, IT.TRANS_QTY*PMI_COMMON_PKG.PMICO_GET_COST(IT.ITEM_ID
, IT.WHSE_CODE
, NULL
, IT.TRANS_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
, IM.ITEM_UM
, LT.LOT_NO
, LT.SUBLOT_NO
, PM.BASE_CURRENCY_CODE
, PM.GL_COST_MTHD
, GREATEST(IT.LAST_UPDATE_DATE
, LT.LAST_UPDATE_DATE
, IM.LAST_UPDATE_DATE
, PM.LAST_UPDATE_DATE
, SOB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM IC_TRAN_PND IT
, IC_LOTS_MST LT
, IC_ITEM_MST IM
, GL_PLCY_MST PM
, GL_SETS_OF_BOOKS SOB
, EDW_LOCAL_INSTANCE INST
WHERE IT.COMPLETED_IND = 1
AND IT.ITEM_ID = IM.ITEM_ID
AND IT.ITEM_ID = LT.ITEM_ID
AND IT.LOT_ID = LT.LOT_ID
AND IT.DOC_TYPE = 'PROD'
AND IT.LINE_TYPE=1
AND IT.TRANS_QTY <> 0
AND IT.CO_CODE = PM.CO_CODE
AND PM.SET_OF_BOOKS_NAME =SOB.NAME UNION ALL SELECT IT.DOC_ID||'-'||IT.ITEM_ID||'-'||IT.LOT_ID||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, IT.ORGN_CODE||'-'||IT.ORGN_CODE||'-'||'PORG'||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, '0'||'-'||IM.ITEM_NO||'-'||IT.WHSE_CODE
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(IT.TRANS_DATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SUBSTR(PMI_PRODUCTION_SUM.FIND_PROD_GRADE(IT.TRANS_ID
, IT.ITEM_ID
, IT.LOT_ID)
, 1
, 4)||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, 'NA_EDW'
, INST.INSTANCE_CODE
, INST.INSTANCE_CODE
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, SUBSTR(PMI_PRODUCTION_SUM.FIND_PROD_GRADE(IT.TRANS_ID
, IT.ITEM_ID
, IT.LOT_ID)
, 1
, 4)
, IT.TRANS_QTY
, IT.TRANS_QTY*PMI_COMMON_PKG.PMICO_GET_COST(IT.ITEM_ID
, IT.WHSE_CODE
, NULL
, IT.TRANS_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
, IM.ITEM_UM
, LT.LOT_NO
, LT.SUBLOT_NO
, PM.BASE_CURRENCY_CODE
, PM.GL_COST_MTHD
, GREATEST(IT.LAST_UPDATE_DATE
, LT.LAST_UPDATE_DATE
, IM.LAST_UPDATE_DATE
, PM.LAST_UPDATE_DATE
, SOB.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM IC_TRAN_CMP IT
, IC_LOTS_MST LT
, IC_ITEM_MST IM
, GL_PLCY_MST PM
, GL_SETS_OF_BOOKS SOB
, EDW_LOCAL_INSTANCE INST
WHERE IT.ITEM_ID = IM.ITEM_ID
AND IT.ITEM_ID = LT.ITEM_ID
AND IT.LOT_ID = LT.LOT_ID
AND IT.DOC_TYPE = 'PROD'
AND IT.LINE_TYPE=1
AND IT.TRANS_QTY <> 0
AND IT.CO_CODE = PM.CO_CODE
AND PM.SET_OF_BOOKS_NAME =SOB.NAME