View: OPI_EDW_OPIINV_DAILY_STAT_FCV

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

View Text

SELECT IDS_KEY || '-' || INST.INSTANCE_CODE || '-' || 'OPI' INV_DAILY_STATUS_PK
, OIPL.BASE_CURRENCY_CODE BASE_CURRENCY_FK
, OIPL.EDW_BASE_UOM BASE_UOM_FK
, INST.INSTANCE_CODE INSTANCE_FK
, OIPL.ORGANIZATION_ID || '-' || INST.INSTANCE_CODE INV_ORG_FK
, OIPL.INVENTORY_ITEM_ID || '-' || OIPL.ORGANIZATION_ID ||'-'||INST.INSTANCE_CODE || '-IORG' ITEM_ORG_FK
, DECODE(OIPL.LOCATOR_ID
, NULL
, DECODE(OIPL.SUBINVENTORY_CODE
, NULL
, MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT'
, OIPL.SUBINVENTORY_CODE||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-SUBI')
, OIPL.LOCATOR_ID||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE) LOCATOR_FK
, DECODE(OIPL.LOT_NUMBER
, NULL
, 'NA_EDW'
, OIPL.INVENTORY_ITEM_ID || '-' || OIPL.ORGANIZATION_ID || '-' || OIPL.LOT_NUMBER || '-' || INST.INSTANCE_CODE) LOT_FK
, EDW_TIME_PKG.CAL_DAY_TO_CAL_PERIOD_FK(OIPL.TRX_DATE
, OOD.SET_OF_BOOKS_ID) PRD_DATE_FK
, EDW_TIME_PKG.CAL_DAY_FK(OIPL.TRX_DATE
, OOD.SET_OF_BOOKS_ID) TRX_DATE_FK
, OIPL.AVG_INT_QTY * OIPL.EDW_UOM_CONV_RATE AVG_INT_QTY
, OIPL.AVG_INT_VAL_B AVG_INT_VAL_B
, OIPL.AVG_INT_VAL_B * OIPL.EDW_CONV_RATE AVG_INT_VAL_G
, OIPL.AVG_ONH_QTY * OIPL.EDW_UOM_CONV_RATE AVG_ONH_QTY
, OIPL.AVG_ONH_VAL_B AVG_ONH_VAL_B
, OIPL.AVG_ONH_VAL_B * OIPL.EDW_CONV_RATE AVG_ONH_VAL_G
, OIPL.AVG_WIP_QTY * OIPL.EDW_UOM_CONV_RATE AVG_WIP_QTY
, OIPL.AVG_WIP_VAL_B AVG_WIP_VAL_B
, OIPL.AVG_WIP_VAL_B * OIPL.EDW_CONV_RATE AVG_WIP_VAL_G
, OIPL.BEG_INT_QTY * OIPL.EDW_UOM_CONV_RATE BEG_INT_QTY
, OIPL.BEG_INT_VAL_B BEG_INT_VAL_B
, OIPL.BEG_INT_VAL_B * OIPL.EDW_CONV_RATE BEG_INT_VAL_G
, OIPL.BEG_ONH_QTY * OIPL.EDW_UOM_CONV_RATE BEG_ONH_QTY
, OIPL.BEG_ONH_VAL_B BEG_ONH_VAL_B
, OIPL.BEG_ONH_VAL_B * OIPL.EDW_CONV_RATE BEG_ONH_VAL_G
, OIPL.BEG_WIP_QTY * OIPL.EDW_UOM_CONV_RATE BEG_WIP_QTY
, OIPL.BEG_WIP_VAL_B BEG_WIP_VAL_B
, OIPL.BEG_WIP_VAL_B * OIPL.EDW_CONV_RATE BEG_WIP_VAL_G
, OIPL.EDW_CONV_RATE EDW_CONV_RATE
, OIPL.END_INT_QTY * OIPL.EDW_UOM_CONV_RATE END_INT_QTY
, OIPL.END_INT_VAL_B END_INT_VAL_B
, OIPL.END_INT_VAL_B * OIPL.EDW_CONV_RATE END_INT_VAL_G
, OIPL.END_ONH_QTY * OIPL.EDW_UOM_CONV_RATE END_ONH_QTY
, OIPL.END_ONH_VAL_B END_ONH_VAL_B
, OIPL.END_ONH_VAL_B * OIPL.EDW_CONV_RATE END_ONH_VAL_G
, OIPL.END_WIP_QTY * OIPL.EDW_UOM_CONV_RATE END_WIP_QTY
, OIPL.END_WIP_VAL_B END_WIP_VAL_B
, OIPL.END_WIP_VAL_B * OIPL.EDW_CONV_RATE END_WIP_VAL_G
, OIPL.FROM_ORG_QTY * OIPL.EDW_UOM_CONV_RATE FROM_ORG_QTY
, OIPL.FROM_ORG_VAL_B FROM_ORG_VAL_B
, OIPL.FROM_ORG_VAL_B * OIPL.EDW_CONV_RATE FROM_ORG_VAL_G
, OIPL.INV_ADJ_QTY * OIPL.EDW_UOM_CONV_RATE INV_ADJ_QTY
, OIPL.INV_ADJ_VAL_B INV_ADJ_VAL_B
, OIPL.INV_ADJ_VAL_B * OIPL.EDW_CONV_RATE INV_ADJ_VAL_G
, OIPL.PO_DEL_QTY * OIPL.EDW_UOM_CONV_RATE PO_DEL_QTY
, OIPL.PO_DEL_VAL_B PO_DEL_VAL_B
, OIPL.PO_DEL_VAL_B * OIPL.EDW_CONV_RATE PO_DEL_VAL_G
, OIPL.TOTAL_REC_QTY * OIPL.EDW_UOM_CONV_RATE TOTAL_REC_QTY
, OIPL.TOTAL_REC_VAL_B TOTAL_REC_VAL_B
, OIPL.TOTAL_REC_VAL_B * OIPL.EDW_CONV_RATE TOTAL_REC_VAL_G
, OIPL.TOT_CUST_SHIP_QTY * OIPL.EDW_UOM_CONV_RATE TOT_CUST_SHIP_QTY
, OIPL.TOT_CUST_SHIP_VAL_B TOT_CUST_SHIP_VAL_B
, OIPL.TOT_CUST_SHIP_VAL_B * OIPL.EDW_CONV_RATE TOT_CUST_SHIP_VAL_G
, OIPL.TOT_ISSUES_QTY * OIPL.EDW_UOM_CONV_RATE TOT_ISSUES_QTY
, OIPL.TOT_ISSUES_VAL_B TOT_ISSUES_VAL_B
, OIPL.TOT_ISSUES_VAL_B * OIPL.EDW_CONV_RATE TOT_ISSUES_VAL_G
, OIPL.TO_ORG_QTY * OIPL.EDW_UOM_CONV_RATE TO_ORG_QTY
, OIPL.TO_ORG_VAL_B TO_ORG_VAL_B
, OIPL.TO_ORG_VAL_B * OIPL.EDW_CONV_RATE TO_ORG_VAL_G
, OIPL.WIP_ASSY_QTY * OIPL.EDW_UOM_CONV_RATE WIP_ASSY_QTY
, OIPL.WIP_ASSY_VAL_B WIP_ASSY_VAL_B
, OIPL.WIP_ASSY_VAL_B * OIPL.EDW_CONV_RATE WIP_ASSY_VAL_G
, OIPL.WIP_COMP_QTY * OIPL.EDW_UOM_CONV_RATE WIP_COMP_QTY
, OIPL.WIP_COMP_VAL_B WIP_COMP_VAL_B
, OIPL.WIP_COMP_VAL_B * OIPL.EDW_CONV_RATE WIP_COMP_VAL_G
, OIPL.WIP_ISSUE_QTY * OIPL.EDW_UOM_CONV_RATE WIP_ISSUE_QTY
, OIPL.WIP_ISSUE_VAL_B WIP_ISSUE_VAL_B
, OIPL.WIP_ISSUE_VAL_B * OIPL.EDW_CONV_RATE WIP_ISSUE_VAL_G
, OIPL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, OIPL.COMMODITY_CODE COMMODITY_CODE
, CCG.COST_GROUP COST_GROUP
, OIPL.CREATION_DATE CREATION_DATE
, OIPL.ITEM_STATUS ITEM_STATUS
, OIPL.ITEM_TYPE ITEM_TYPE
, OIPL.NETTABLE_FLAG NETTABLE_FLAG
, OIPL.PERIOD_FLAG PERIOD_FLAG
, OIPL.TRX_DATE 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
, NULL OPERATION_CODE
, PUSH_FLAG PUSH_FLAG
FROM EDW_LOCAL_INSTANCE INST
, ORG_ORGANIZATION_DEFINITIONS OOD
, CST_COST_GROUPS CCG
, OPI_IDS_PUSH_LOG OIPL
, MTL_PARAMETERS MP
WHERE OIPL.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND OIPL.COST_GROUP_ID = CCG.COST_GROUP_ID(+)
AND OIPL.ORGANIZATION_ID = MP.ORGANIZATION_ID

Columns