VIEW: APPS.OPI_EDW_OPIINV_DAILY_STAT_FCV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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