VIEW: APPS.OPI_EDW_OPMINV_DAILY_STAT_FCV

Source

View Text - Preformatted

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 ,DECODE(BEG_ONH_VAL_B,NULL,NULL,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 ,DECODE(END_ONH_VAL_B,NULL,NULL,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 ,DECODE(FROM_ORG_VAL_B,NULL,NULL,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 ,DECODE(INV_ADJ_VAL_B,NULL,NULL,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 ,DECODE(PO_DEL_VAL_B,NULL,NULL,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 ,DECODE(PO_DEL_VAL_B,NULL,NULL,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 ,DECODE(TOT_CUST_SHIP_VAL_B,NULL,NULL,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 ,DECODE(TO_ORG_VAL_B,NULL,NULL,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 ,DECODE(WIP_COMP_VAL_B,NULL,NULL,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 ,DECODE(WIP_ISSUE_VAL_B,NULL,NULL,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 ,'NO COST GROUP' 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.LOT_ID ,OPMSUM.ITEM_ID ,OPMSUM.CO_CODE ,OPMSUM.ORGN_CODE ,OPMSUM.WHSE_CODE ,OPMSUM.LOCATION ,INC.VIEW_ID ,INC.SEQ_ID 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, OPI_EDW_OPMINV_DAILY_STAT_INC INC 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 AND OPMSUM.CO_CODE=INC.PRIMARY_KEY AND OPMSUM.ORGN_CODE =INC.PRIMARY_KEY1 AND OPMSUM.WHSE_CODE=INC.PRIMARY_KEY2 AND OPMSUM.LOCATION =INC.PRIMARY_KEY3 AND OPMSUM.ITEM_ID =INC.PRIMARY_KEY4 AND OPMSUM.LOT_ID =INC.PRIMARY_KEY5 AND OPMSUM.TRX_DATE =INC.PRIMARY_KEY6

View Text - HTML Formatted

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
, DECODE(BEG_ONH_VAL_B
, NULL
, NULL
, 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
, DECODE(END_ONH_VAL_B
, NULL
, NULL
, 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
, DECODE(FROM_ORG_VAL_B
, NULL
, NULL
, 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
, DECODE(INV_ADJ_VAL_B
, NULL
, NULL
, 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
, DECODE(PO_DEL_VAL_B
, NULL
, NULL
, 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
, DECODE(PO_DEL_VAL_B
, NULL
, NULL
, 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
, DECODE(TOT_CUST_SHIP_VAL_B
, NULL
, NULL
, 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
, DECODE(TO_ORG_VAL_B
, NULL
, NULL
, 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
, DECODE(WIP_COMP_VAL_B
, NULL
, NULL
, 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
, DECODE(WIP_ISSUE_VAL_B
, NULL
, NULL
, 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
, 'NO COST GROUP' 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.LOT_ID
, OPMSUM.ITEM_ID
, OPMSUM.CO_CODE
, OPMSUM.ORGN_CODE
, OPMSUM.WHSE_CODE
, OPMSUM.LOCATION
, INC.VIEW_ID
, INC.SEQ_ID
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
, OPI_EDW_OPMINV_DAILY_STAT_INC INC
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
AND OPMSUM.CO_CODE=INC.PRIMARY_KEY
AND OPMSUM.ORGN_CODE =INC.PRIMARY_KEY1
AND OPMSUM.WHSE_CODE=INC.PRIMARY_KEY2
AND OPMSUM.LOCATION =INC.PRIMARY_KEY3
AND OPMSUM.ITEM_ID =INC.PRIMARY_KEY4
AND OPMSUM.LOT_ID =INC.PRIMARY_KEY5
AND OPMSUM.TRX_DATE =INC.PRIMARY_KEY6