View: OPI_EDW_OPMINV_DAILY_STAT_FCV

Product:	OPI - Operations Intelligence
Description:	This is the source view for extracting Invetory Daily Transaction Summary data from the Process Manufacturing source tables.
Implementation/DBA Data:	APPS.OPI_EDW_OPMINV_DAILY_STAT_FCV

View Text

SELECT OPMSUM.CO_CODE||'-'||OPMSUM.ORGN_CODE||'-'||OPMSUM.WHSE_CODE||'-'||OPMSUM.LOCATION||'-'||TO_CHAR(OPMSUM.ITEM_ID)||'-'||TO_CHAR(OPMSUM.TRX_DATE
, 'DD/MM/RRRR')||'-'||TO_CHAR(OPMSUM.LOT_ID)||'-'||'OPMCOSTGROUP'||'-'||INST.INSTANCE_CODE||'-OPM' INV_DAILY_STATUS_PK
, GPM.BASE_CURRENCY_CODE BASE_CURRENCY_FK
, EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE
, MSI.INVENTORY_ITEM_ID) BASE_UOM_FK
, INST.INSTANCE_CODE INSTANCE_FK
, EDW_ORGANIZATION_PKG.INT_ORGANIZATION_FK(IWM.MTL_ORGANIZATION_ID) INV_ORG_FK
, EDW_ITEMS_PKG.ITEM_ORG_FK(MSI.INVENTORY_ITEM_ID
, IWM.MTL_ORGANIZATION_ID
, NULL
, TO_NUMBER(NULL)
, NULL) ITEM_ORG_FK
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK (ILM.INVENTORY_LOCATION_ID
, IWM.MTL_ORGANIZATION_ID
, NULL
, IWM.LOCT_CTL
, IIM.LOCT_CTL
, OPMSUM.LOCATION
, MP.ORGANIZATION_CODE
, INST.INSTANCE_CODE) LOCATOR_FK
, OPMSUM.LOT_ID||'-'||OPMSUM.ITEM_ID||'-'||'OPM'||'-'||INST.INSTANCE_CODE LOT_FK
, EDW_TIME_PKG.CAL_DAY_TO_CAL_PERIOD_FK(OPMSUM.TRX_DATE
, GPM.SOB_ID) PRD_DATE_FK
, EDW_TIME_PKG.CAL_DAY_FK(OPMSUM.TRX_DATE
, GPM.SOB_ID) TRX_DATE_FK
, NULL AVG_INT_QTY
, NULL AVG_INT_VAL_B
, NULL AVG_INT_VAL_G
, AVG_ONH_QTY
, NULL AVG_ONH_VAL_B
, NULL AVG_ONH_VAL_G
, NULL AVG_WIP_QTY
, NULL AVG_WIP_VAL_B
, NULL AVG_WIP_VAL_G
, NULL BEG_INT_QTY
, NULL BEG_INT_VAL_B
, NULL BEG_INT_VAL_G
, BEG_ONH_QTY
, BEG_ONH_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, BEG_ONH_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) BEG_ONH_VAL_G
, NULL BEG_WIP_QTY
, NULL BEG_WIP_VAL_B
, NULL BEG_WIP_VAL_G
, NULL END_INT_QTY
, NULL END_INT_VAL_B
, NULL END_INT_VAL_G
, END_ONH_QTY
, END_ONH_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, END_ONH_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) END_ONH_VAL_G
, NULL END_WIP_QTY
, NULL END_WIP_VAL_B
, NULL END_WIP_VAL_G
, FROM_ORG_QTY
, FROM_ORG_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, FROM_ORG_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) FROM_ORG_VAL_G
, INV_ADJ_QTY
, INV_ADJ_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, INV_ADJ_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) INV_ADJ_VAL_G
, PO_DEL_QTY
, PO_DEL_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, PO_DEL_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) PO_DEL_VAL_G
, TOTAL_REC_QTY
, TOTAL_REC_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, TOTAL_REC_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) TOTAL_REC_VAL_G
, TOT_CUST_SHIP_QTY
, TOT_CUST_SHIP_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, TOT_CUST_SHIP_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) TOT_CUST_SHIP_VAL_G
, TOT_ISSUES_QTY
, NULL TOT_ISSUES_VAL_B
, NULL TOT_ISSUES_VAL_G
, TO_ORG_QTY
, TO_ORG_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, TO_ORG_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) TO_ORG_VAL_G
, NULL WIP_ASSY_QTY
, NULL WIP_ASSY_VAL_B
, NULL WIP_ASSY_VAL_G
, WIP_COMP_QTY
, WIP_COMP_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, WIP_COMP_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) WIP_COMP_VAL_G
, WIP_ISSUE_QTY
, WIP_ISSUE_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, WIP_ISSUE_VAL_B
, NULL
, GPM.BASE_CURRENCY_CODE
, OPMSUM.TRX_DATE
, NULL) WIP_ISSUE_VAL_G
, OPMSUM.LAST_UPDATE_DATE
, IIM.COMMODITY_CODE
, NULL COST_GROUP
, OPMSUM.CREATION_DATE
, IIM.INACTIVE_IND
, IIM.INV_TYPE
, NULL NETTABLE_FLAG
, PERIOD_FLAG
, OPMSUM.TRX_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, NULL USER_ATTRIBUTE6
, NULL USER_ATTRIBUTE7
, NULL USER_ATTRIBUTE8
, NULL USER_ATTRIBUTE9
, NULL USER_ATTRIBUTE10
, NULL USER_ATTRIBUTE11
, NULL USER_ATTRIBUTE12
, NULL USER_ATTRIBUTE13
, NULL USER_ATTRIBUTE14
, NULL USER_ATTRIBUTE15
, TO_NUMBER(NULL) USER_MEASURE1
, TO_NUMBER(NULL) USER_MEASURE2
, TO_NUMBER(NULL) USER_MEASURE3
, TO_NUMBER(NULL) USER_MEASURE4
, TO_NUMBER(NULL) USER_MEASURE5
, NULL USER_FK1
, NULL USER_FK2
, NULL USER_FK3
, NULL USER_FK4
, NULL USER_FK5
, GPM.GL_COST_MTHD
, GPM.SOB_ID
, OPMSUM.ITEM_ID
, OPMSUM.CO_CODE
, OPMSUM.ORGN_CODE
, OPMSUM.WHSE_CODE
FROM EDW_LOCAL_INSTANCE INST
, OPI_PMI_INV_DAILY_STAT_SUM OPMSUM
, IC_WHSE_MST IWM
, IC_ITEM_MST IIM
, MTL_SYSTEM_ITEMS MSI
, GL_PLCY_MST GPM
, IC_LOCT_MST ILM
, MTL_PARAMETERS MP
, OPI_PMI_UOMS_MST UOM
WHERE OPMSUM.ITEM_ID = IIM.ITEM_ID
AND IWM.WHSE_CODE = OPMSUM.WHSE_CODE
AND ILM.WHSE_CODE(+) = OPMSUM.WHSE_CODE
AND ILM.LOCATION(+) = OPMSUM.LOCATION
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND MSI.ORGANIZATION_ID + 0 = IWM.MTL_ORGANIZATION_ID
AND MSI.SEGMENT1 ||'' = IIM.ITEM_NO
AND OPMSUM.CO_CODE = GPM.CO_CODE
AND UOM.UM_CODE = IIM.ITEM_UM

Columns