[Home] [Help]
| Product: | OPI - Operations Intelligence (Obsolete) |
|---|---|
| Description: | This view contains the extraction logic for data to the Resource Utilization fact from process manufacturing side. |
| Implementation/DBA Data: | Not implemented in this database |
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