VIEW: APPS.OPI_EDW_OPM_RES_UTIL_FCV

Source

View Text - Preformatted

SELECT RSRC.TRANS_DATE||'-'||rsrc.ORGN_CODE||'-'||RSRC.RESOURCES||'-'||inst.instance_code||'-'||'OPM' RES_UTIL_PK, rsrc.ORGN_CODE||'-'||inst.instance_code||'-'||'OPM'||'-'||'PORG' LOCATOR_FK, rsrc.resources||'-'||inst.instance_code||'-'||'OPM' RSRC_FK, EDW_TIME_PKG.CAL_DAY_FK(RSRC.TRANS_DATE,GPM.SOB_ID) TRX_DATE_FK, EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE, 0) USAGE_UOM_FK, INST.INSTANCE_CODE INSTANCE_FK, 'NA_EDW','NA_EDW','NA_EDW','NA_EDW','NA_EDW', NVL(RSRC_USAGE,0) ACT_RES_USAGE, NVL(RSRC.DAILY_AVAIL,0) AVAIL_RES, NULL DEPARTMENT, RSRC.TRANS_DATE TRX_DATE, NULL,NULL,NULL,NULL,NULL,GREATEST(RSRC.TRANS_DATE,RSRC.LAST_UPDATE_DATE), NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM ( select ORGN_CODE, trunc(trans_date) trans_date , resources,sum(resource_usage) RSRC_USAGE FROM PC_TRAN_PND WHERE completed_ind=1 GROUP BY ORGN_CODE,trunc(trans_date),resources ) PCPND, ( select a.ORGN_CODE, a.resources, trunc(trans_date) trans_date, AVG(DAILY_AVAIL_USE) DAILY_AVAIL, MAX(a.USAGE_UM) USAGE_UM, MAX(GREATEST(a.LAST_UPDATE_DATE,TRANS_DATE)) LAST_UPDATE_DATE FROM ( SELECT RS.ORGN_CODE, RS.RESOURCES, RD.DAILY_AVAIL_USE, decode(RD.USAGE_UM,NULL,RS.STD_USAGE_UM,RD.USAGE_UM) USAGE_UM, RS.LAST_UPDATE_DATE FROM (SELECT ORG.ORGN_CODE, RSRC.RESOURCES, RSRC.STD_USAGE_UM, GREATEST(ORG.LAST_UPDATE_DATE,RSRC.LAST_UPDATE_DATE) LAST_UPDATE_DATE FROM SY_ORGN_MST ORG, CR_RSRC_MST RSRC ) RS, CR_RSRC_DTL RD WHERE RS.RESOURCES = RD.RESOURCES(+) AND RS.ORGN_CODE = RD.ORGN_CODE(+) ) a, PC_TRAN_PND b WHERE b.completed_ind=1 GROUP BY A.ORGN_CODE,a.resources,trunc(trans_date) ) RSRC, EDW_LOCAL_INSTANCE inst, SY_ORGN_MST SY, GL_PLCY_MST GPM, OPI_PMI_UOMS_MST UOM WHERE PCPND.orgn_code(+)= RSRC.ORGN_CODE and PCPND.trans_date(+)=RSRC.trans_date and PCPND.resources(+) = RSRC.resources and RSRC.ORGN_CODE = SY.ORGN_CODE AND SY.CO_CODE = GPM.CO_CODE AND UOM.UM_CODE = RSRC.USAGE_UM

View Text - HTML Formatted

SELECT RSRC.TRANS_DATE||'-'||RSRC.ORGN_CODE||'-'||RSRC.RESOURCES||'-'||INST.INSTANCE_CODE||'-'||'OPM' RES_UTIL_PK
, RSRC.ORGN_CODE||'-'||INST.INSTANCE_CODE||'-'||'OPM'||'-'||'PORG' LOCATOR_FK
, RSRC.RESOURCES||'-'||INST.INSTANCE_CODE||'-'||'OPM' RSRC_FK
, EDW_TIME_PKG.CAL_DAY_FK(RSRC.TRANS_DATE
, GPM.SOB_ID) TRX_DATE_FK
, EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE
, 0) USAGE_UOM_FK
, INST.INSTANCE_CODE INSTANCE_FK
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, NVL(RSRC_USAGE
, 0) ACT_RES_USAGE
, NVL(RSRC.DAILY_AVAIL
, 0) AVAIL_RES
, NULL DEPARTMENT
, RSRC.TRANS_DATE TRX_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, GREATEST(RSRC.TRANS_DATE
, RSRC.LAST_UPDATE_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM ( SELECT ORGN_CODE
, TRUNC(TRANS_DATE) TRANS_DATE
, RESOURCES
, SUM(RESOURCE_USAGE) RSRC_USAGE
FROM PC_TRAN_PND
WHERE COMPLETED_IND=1 GROUP BY ORGN_CODE
, TRUNC(TRANS_DATE)
, RESOURCES ) PCPND
, ( SELECT A.ORGN_CODE
, A.RESOURCES
, TRUNC(TRANS_DATE) TRANS_DATE
, AVG(DAILY_AVAIL_USE) DAILY_AVAIL
, MAX(A.USAGE_UM) USAGE_UM
, MAX(GREATEST(A.LAST_UPDATE_DATE
, TRANS_DATE)) LAST_UPDATE_DATE
FROM ( SELECT RS.ORGN_CODE
, RS.RESOURCES
, RD.DAILY_AVAIL_USE
, DECODE(RD.USAGE_UM
, NULL
, RS.STD_USAGE_UM
, RD.USAGE_UM) USAGE_UM
, RS.LAST_UPDATE_DATE
FROM (SELECT ORG.ORGN_CODE
, RSRC.RESOURCES
, RSRC.STD_USAGE_UM
, GREATEST(ORG.LAST_UPDATE_DATE
, RSRC.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM SY_ORGN_MST ORG
, CR_RSRC_MST RSRC ) RS
, CR_RSRC_DTL RD
WHERE RS.RESOURCES = RD.RESOURCES(+)
AND RS.ORGN_CODE = RD.ORGN_CODE(+) ) A
, PC_TRAN_PND B
WHERE B.COMPLETED_IND=1 GROUP BY A.ORGN_CODE
, A.RESOURCES
, TRUNC(TRANS_DATE) ) RSRC
, EDW_LOCAL_INSTANCE INST
, SY_ORGN_MST SY
, GL_PLCY_MST GPM
, OPI_PMI_UOMS_MST UOM
WHERE PCPND.ORGN_CODE(+)= RSRC.ORGN_CODE
AND PCPND.TRANS_DATE(+)=RSRC.TRANS_DATE
AND PCPND.RESOURCES(+) = RSRC.RESOURCES
AND RSRC.ORGN_CODE = SY.ORGN_CODE
AND SY.CO_CODE = GPM.CO_CODE
AND UOM.UM_CODE = RSRC.USAGE_UM