View: OPI_EDW_OPI_JOB_RSRC_FCV

Product:	OPI - Operations Intelligence
Description:	This is the source view for extracting job-resource data from the Discrete Manufacturing source tables.
Implementation/DBA Data:	APPS.OPI_EDW_OPI_JOB_RSRC_FCV

View Text

SELECT 1 VIEW_ID
, INC.SEQ_ID SEQ_ID
, TO_CHAR(WOR.ORGANIZATION_ID)||'-'||TO_CHAR(WOR.WIP_ENTITY_ID)||'-'||TO_CHAR(WOR.REPETITIVE_SCHEDULE_ID)||'-'||TO_CHAR(WOR.OPERATION_SEQ_NUM)||'-'||TO_CHAR(WOR.RESOURCE_ID)||'-'||INST.INSTANCE_CODE||'-'||'OPI' JOB_RSRC_PK
, 0 ACT_RSRC_COUNT
, 0 PLN_RSRC_COUNT
, SUM(WO.QUANTITY_COMPLETED)*EDW_UTIL.GET_UOM_CONV_RATE(MSI.PRIMARY_UOM_CODE
, WE.PRIMARY_ITEM_ID) ACT_RSRC_QTY
, 0 ACT_RSRC_VAL_B
, 0 ACT_RSRC_VAL_G
, SUM(WO.SCHEDULED_QUANTITY)*EDW_UTIL.GET_UOM_CONV_RATE(MSI.PRIMARY_UOM_CODE
, WE.PRIMARY_ITEM_ID) PLN_RSRC_QTY
, 0 PLN_RSRC_VAL_B
, 0 PLN_RSRC_VAL_G
, SUM(WOR.APPLIED_RESOURCE_UNITS) ACT_RSRC_USAGE
, DECODE( WOR.BASIS_TYPE
, 1
, SUM(WOR.USAGE_RATE_OR_AMOUNT * WO.SCHEDULED_QUANTITY)
, 2
, SUM(WOR.USAGE_RATE_OR_AMOUNT) ) PLN_RSRC_USAGE
, DECODE( WOR.BASIS_TYPE
, 1
, SUM(WOR.USAGE_RATE_OR_AMOUNT * WO.QUANTITY_COMPLETED)
, 2
, DECODE(SUM(WO.QUANTITY_COMPLETED)
, 0
, 0
, SUM(WOR.USAGE_RATE_OR_AMOUNT)) ) STND_RSRC_USAGE
, SUM(WOR.APPLIED_RESOURCE_VALUE) ACT_RSRC_USAGE_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(SUM(WOR.APPLIED_RESOURCE_VALUE)
, TO_NUMBER(NULL)
, EDW_UTIL.GET_BASE_CURRENCY(WOR.ORGANIZATION_ID)
, ''
, OPI_EDW_JOB_RSRC_PVT.GET_ACT_CMPL_DATE(WOR.ORGANIZATION_ID
, WOR.WIP_ENTITY_ID
, WOR.REPETITIVE_SCHEDULE_ID
, WOR.OPERATION_SEQ_NUM)
, '') ACT_RSRC_USAGE_VAL_G
, DECODE( WOR.BASIS_TYPE
, 1
, SUM((WOR.USAGE_RATE_OR_AMOUNT*WO.QUANTITY_COMPLETED)* DECODE(WOR.APPLIED_RESOURCE_UNITS
, 0
, 0
, (WOR.APPLIED_RESOURCE_VALUE/WOR.APPLIED_RESOURCE_UNITS) ) )
, 2
, SUM((WOR.USAGE_RATE_OR_AMOUNT)* DECODE(WOR.APPLIED_RESOURCE_UNITS
, 0
, 0
, (WOR.APPLIED_RESOURCE_VALUE/WOR.APPLIED_RESOURCE_UNITS) ) ) ) PLN_RSRC_USAGE_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( DECODE( WOR.BASIS_TYPE
, 1
, SUM((WOR.USAGE_RATE_OR_AMOUNT*WO.QUANTITY_COMPLETED)* DECODE(WOR.APPLIED_RESOURCE_UNITS
, 0
, 0
, (WOR.APPLIED_RESOURCE_VALUE/WOR.APPLIED_RESOURCE_UNITS) ) )
, 2
, SUM((WOR.USAGE_RATE_OR_AMOUNT)* DECODE(WOR.APPLIED_RESOURCE_UNITS
, 0
, 0
, (WOR.APPLIED_RESOURCE_VALUE/WOR.APPLIED_RESOURCE_UNITS) ) ) )
, TO_NUMBER(NULL)
, EDW_UTIL.GET_BASE_CURRENCY(WOR.ORGANIZATION_ID)
, ''
, OPI_EDW_JOB_RSRC_PVT.GET_ACT_CMPL_DATE(WOR.ORGANIZATION_ID
, WOR.WIP_ENTITY_ID
, WOR.REPETITIVE_SCHEDULE_ID
, WOR.OPERATION_SEQ_NUM)
, '') PLN_RSRC_USAGE_VAL_G
, 0 EXTD_RSRC_COST
, WE.WIP_ENTITY_NAME JOB_NO
, WOR.OPERATION_SEQ_NUM OPERATION_SEQ_NO
, BD.DEPARTMENT_CODE DEPARTMENT
, OPI_EDW_JOB_RSRC_PVT.GET_ACT_STRT_DATE(WOR.ORGANIZATION_ID
, WOR.WIP_ENTITY_ID
, WOR.REPETITIVE_SCHEDULE_ID
, WOR.OPERATION_SEQ_NUM) ACT_STRT_DATE
, OPI_EDW_JOB_RSRC_PVT.GET_ACT_CMPL_DATE(WOR.ORGANIZATION_ID
, WOR.WIP_ENTITY_ID
, WOR.REPETITIVE_SCHEDULE_ID
, WOR.OPERATION_SEQ_NUM) ACT_CMPL_DATE
, WO.FIRST_UNIT_START_DATE PLN_STRT_DATE
, WO.FIRST_UNIT_COMPLETION_DATE PLN_CMPL_DATE
, EDW_UTIL.GET_BASE_CURRENCY(WOR.ORGANIZATION_ID) SOB_CURRENCY_FK /*
, EDW_UTIL.GET_EDW_BASE_UOM(WOR.UOM_CODE
, WOR.WIP_ENTITY_ID) QTY_UOM_FK */
, EDW_UTIL.GET_EDW_BASE_UOM(MSI.PRIMARY_UOM_CODE
, WE.PRIMARY_ITEM_ID) QTY_UOM_FK
, INST.INSTANCE_CODE INSTANCE_FK
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK(''
, WOR.ORGANIZATION_ID
, '') LOCATOR_FK
, DECODE(NVL(WOR.ACTIVITY_ID
, -99)
, -99
, 'NA_EDW'
, WOR.ACTIVITY_ID ||'-'||INST.INSTANCE_CODE ||'-OPI') ACTIVITY_FK
, EDW_TIME_PKG.CAL_DAY_FK( OPI_EDW_JOB_RSRC_PVT.GET_ACT_CMPL_DATE(WOR.ORGANIZATION_ID
, WOR.WIP_ENTITY_ID
, WOR.REPETITIVE_SCHEDULE_ID
, WOR.OPERATION_SEQ_NUM)
, GSOB.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) TRX_DATE_FK
, DECODE(NVL(WO.OPERATION_SEQUENCE_ID
, -99)
, -99
, 'NA_EDW'
, TO_CHAR(WO.OPERATION_SEQUENCE_ID)||'-'||TO_CHAR(WOR.ORGANIZATION_ID)||'-'||INST.INSTANCE_CODE||'-OPI') OPRN_FK
, EDW_BOM_RES_PKG.RESOURCE_FK(WO.DEPARTMENT_ID
, MIN(WOR.RESOURCE_ID)) RSRC_FK /*
, WE.PRIMARY_ITEM_ID||'-'||WOR.ORGANIZATION_ID||'-'||INST.INSTANCE_CODE ITEM_FK */
, EDW_ITEMS_PKG.ITEM_ORG_FK(WE.PRIMARY_ITEM_ID
, WOR.ORGANIZATION_ID
, NULL
, TO_NUMBER(NULL)
, INST.INSTANCE_CODE) ITEM_FK
, EDW_UTIL.GET_EDW_BASE_UOM(WOR.UOM_CODE
, '') USAGE_UOM_FK
FROM OPI_EDW_OPI_JOB_RSRC_INC INC
, WIP_OPERATION_RESOURCES WOR
, WIP_OPERATIONS WO
, WIP_ENTITIES WE
, BOM_DEPARTMENTS BD
, HR_ORGANIZATION_INFORMATION HOI
, GL_SETS_OF_BOOKS GSOB
, EDW_LOCAL_INSTANCE INST
, WIP_DISCRETE_JOBS WDJ
, WIP_REPETITIVE_SCHEDULES WRS
, MTL_SYSTEM_ITEMS MSI
WHERE WOR.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WOR.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WOR.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND NVL(WOR.REPETITIVE_SCHEDULE_ID
, -99) = NVL(WO.REPETITIVE_SCHEDULE_ID
, -99)
AND WO.ORGANIZATION_ID = BD.ORGANIZATION_ID
AND WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND WO.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WO.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND HOI.ORGANIZATION_ID = WOR.ORGANIZATION_ID
AND GSOB.SET_OF_BOOKS_ID = HOI.ORG_INFORMATION1
AND HOI.ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND WDJ.WIP_ENTITY_ID (+) = WOR.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID (+) = WOR.ORGANIZATION_ID
AND WRS.REPETITIVE_SCHEDULE_ID (+)= NVL(WOR.REPETITIVE_SCHEDULE_ID
, -99)
AND WRS.ORGANIZATION_ID (+) = WOR.ORGANIZATION_ID
AND (WRS.STATUS_TYPE IN (4
, 5
, 7
, 12) OR WDJ.STATUS_TYPE IN (4
, 5
, 7
, 12))
AND MSI.ORGANIZATION_ID = WOR.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND INC.PRIMARY_KEY1 = WOR.ORGANIZATION_ID
AND INC.PRIMARY_KEY2 = WOR.WIP_ENTITY_ID
AND NVL(INC.PRIMARY_KEY3
, -99) = NVL(WOR.REPETITIVE_SCHEDULE_ID
, -99)
AND INC.PRIMARY_KEY4 = WOR.OPERATION_SEQ_NUM
AND INC.PRIMARY_KEY5 = WOR.RESOURCE_ID
AND INC.PRIMARY_KEY6 = 'OPI' GROUP BY WOR.ORGANIZATION_ID
, WOR.WIP_ENTITY_ID
, WOR.OPERATION_SEQ_NUM
, WOR.RESOURCE_ID
, WOR.REPETITIVE_SCHEDULE_ID
, WOR.ACTIVITY_ID
, WOR.UOM_CODE
, WOR.BASIS_TYPE
, INST.INSTANCE_CODE
, WE.WIP_ENTITY_NAME
, BD.DEPARTMENT_CODE
, WO.FIRST_UNIT_START_DATE
, WO.FIRST_UNIT_COMPLETION_DATE
, WO.OPERATION_SEQUENCE_ID
, WO.DEPARTMENT_ID
, GSOB.SET_OF_BOOKS_ID
, MSI.PRIMARY_UOM_CODE
, WE.PRIMARY_ITEM_ID
, INC.SEQ_ID UNION ALL SELECT 2 VIEW_ID
, INC.SEQ_ID SEQ_ID
, TO_CHAR(WT.ORGANIZATION_ID)||'-'||TO_CHAR(WT.WIP_ENTITY_ID)||'-'||TO_CHAR(NULL)||'-'||TO_CHAR(WT.OPERATION_SEQ_NUM)||'-'||TO_CHAR(WT.RESOURCE_ID)||'-'||INST.INSTANCE_CODE||'-'||'OPI' JOB_RSRC_PK
, 0 ACT_RSRC_COUNT
, 0 PLN_RSRC_COUNT
, SUM(WFS.QUANTITY_COMPLETED)*EDW_UTIL.GET_UOM_CONV_RATE(MSI.PRIMARY_UOM_CODE
, WE.PRIMARY_ITEM_ID) ACT_RSRC_QTY
, 0 ACT_RSRC_VAL_B
, 0 ACT_RSRC_VAL_G
, SUM(WFS.QUANTITY_COMPLETED)*EDW_UTIL.GET_UOM_CONV_RATE(MSI.PRIMARY_UOM_CODE
, WE.PRIMARY_ITEM_ID) PLN_RSRC_QTY
, 0 PLN_RSRC_VAL_B
, 0 PLN_RSRC_VAL_G
, DECODE( WT.BASIS_TYPE
, 1
, SUM(WT.USAGE_RATE_OR_AMOUNT * DECODE(WT.PRIMARY_QUANTITY
, NULL
, WT.TRANSACTION_QUANTITY
, WT.PRIMARY_QUANTITY))
, 2
, SUM(WT.USAGE_RATE_OR_AMOUNT) ) ACT_RSRC_USAGE
, DECODE( WT.BASIS_TYPE
, 1
, SUM(WT.USAGE_RATE_OR_AMOUNT * DECODE(WT.PRIMARY_QUANTITY
, NULL
, WT.TRANSACTION_QUANTITY
, WT.PRIMARY_QUANTITY))
, 2
, SUM(WT.USAGE_RATE_OR_AMOUNT) ) PLN_RSRC_USAGE
, DECODE( WT.BASIS_TYPE
, 1
, SUM(WT.USAGE_RATE_OR_AMOUNT * DECODE(WT.PRIMARY_QUANTITY
, NULL
, WT.TRANSACTION_QUANTITY
, WT.PRIMARY_QUANTITY))
, 2
, SUM(WT.USAGE_RATE_OR_AMOUNT) ) STND_RSRC_USAGE
, SUM(WTA.BASE_TRANSACTION_VALUE) ACT_RSRC_USAGE_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(SUM(WTA.BASE_TRANSACTION_VALUE)
, TO_NUMBER(NULL)
, EDW_UTIL.GET_BASE_CURRENCY(WT.ORGANIZATION_ID)
, ''
, WFS.DATE_CLOSED
, '') ACT_RSRC_USAGE_VAL_G
, SUM(WTA.BASE_TRANSACTION_VALUE) PLN_RSRC_USAGE_VAL_B
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT(SUM(WTA.BASE_TRANSACTION_VALUE)
, TO_NUMBER(NULL)
, EDW_UTIL.GET_BASE_CURRENCY(WT.ORGANIZATION_ID)
, ''
, WFS.DATE_CLOSED
, '') PLN_RSRC_USAGE_VAL_G
, 0 EXTD_RSRC_COST
, WE.WIP_ENTITY_NAME JOB_NO
, WT.OPERATION_SEQ_NUM OPERATION_SEQ_NO
, BD.DEPARTMENT_CODE DEPARTMENT
, WFS.SCHEDULED_START_DATE ACT_STRT_DATE
, WFS.DATE_CLOSED ACT_CMPL_DATE
, WFS.SCHEDULED_START_DATE PLN_STRT_DATE
, WFS.SCHEDULED_COMPLETION_DATE PLN_CMPL_DATE
, EDW_UTIL.GET_BASE_CURRENCY(WT.ORGANIZATION_ID) SOB_CURRENCY_FK /*
, EDW_UTIL.GET_EDW_BASE_UOM(WT.PRIMARY_UOM
, WT.WIP_ENTITY_ID) QTY_UOM_FK */
, EDW_UTIL.GET_EDW_BASE_UOM(MSI.PRIMARY_UOM_CODE
, WE.PRIMARY_ITEM_ID) QTY_UOM_FK
, INST.INSTANCE_CODE INSTANCE_FK
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK(''
, WT.ORGANIZATION_ID
, '') LOCATOR_FK
, DECODE(NVL(WT.ACTIVITY_ID
, -99)
, -99
, 'NA_EDW'
, WT.ACTIVITY_ID ||'-'||INST.INSTANCE_CODE ||'-OPI') ACTIVITY_FK
, EDW_TIME_PKG.CAL_DAY_FK( WFS.DATE_CLOSED
, GSOB.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) TRX_DATE_FK
, TO_CHAR(BOS.OPERATION_SEQUENCE_ID)||'-'||TO_CHAR(WT.ORGANIZATION_ID)||'-'||INST.INSTANCE_CODE||'-OPI' OPRN_FK
, EDW_BOM_RES_PKG.RESOURCE_FK(WT.DEPARTMENT_ID
, WT.RESOURCE_ID) RSRC_FK /*
, WE.PRIMARY_ITEM_ID||'-'||WT.ORGANIZATION_ID||'-'||INST.INSTANCE_CODE ITEM_FK*/
, EDW_ITEMS_PKG.ITEM_ORG_FK(WE.PRIMARY_ITEM_ID
, WT.ORGANIZATION_ID
, NULL
, TO_NUMBER(NULL)
, INST.INSTANCE_CODE) ITEM_FK
, EDW_UTIL.GET_EDW_BASE_UOM(WT.TRANSACTION_UOM
, '') USAGE_UOM_FK
FROM OPI_EDW_OPI_JOB_RSRC_INC INC
, WIP_ENTITIES WE
, WIP_TRANSACTIONS WT
, WIP_TRANSACTION_ACCOUNTS WTA
, BOM_DEPARTMENTS BD
, HR_ORGANIZATION_INFORMATION HOI
, GL_SETS_OF_BOOKS GSOB
, EDW_LOCAL_INSTANCE INST
, WIP_FLOW_SCHEDULES WFS
, BOM_OPERATIONAL_ROUTINGS BOR
, BOM_OPERATION_SEQUENCES BOS
, MTL_SYSTEM_ITEMS MSI
WHERE WT.TRANSACTION_TYPE IN (1
, 3)
AND WFS.STATUS = 2
AND WT.WIP_ENTITY_ID = WFS.WIP_ENTITY_ID
AND WT.ORGANIZATION_ID = WFS.ORGANIZATION_ID
AND WT.ORGANIZATION_ID = WTA.ORGANIZATION_ID
AND WT.WIP_ENTITY_ID = WTA.WIP_ENTITY_ID
AND WT.TRANSACTION_ID = WTA.TRANSACTION_ID
AND WTA.ACCOUNTING_LINE_TYPE = 7
AND WT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WT.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WT.ORGANIZATION_ID = BD.ORGANIZATION_ID
AND WT.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND HOI.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND HOI.ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND GSOB.SET_OF_BOOKS_ID = HOI.ORG_INFORMATION1
AND MSI.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = WE.PRIMARY_ITEM_ID
AND INC.PRIMARY_KEY1 = WT.ORGANIZATION_ID
AND INC.PRIMARY_KEY2 = WT.WIP_ENTITY_ID
AND INC.PRIMARY_KEY4 = WT.OPERATION_SEQ_NUM
AND INC.PRIMARY_KEY5 = WT.RESOURCE_ID
AND INC.PRIMARY_KEY6 = 'OPI' /* EXTRACTING OPERATION SEQ ID */ /* USING WFS
AND BOR (ORG_ID
, ALTERNATE_ROUTING_ID
, PRIMARY_ITEM_ID) GET ROUTING_SEQ_ID. USING BOR.ROUTING_SEQ_ID
AND WT.OPERATION_SEQ_NUM GET BOS.OPERATION_SEQ_ID */
AND WFS.ORGANIZATION_ID = BOR.ORGANIZATION_ID
AND NVL(WFS.ALTERNATE_ROUTING_DESIGNATOR
, -99) = NVL(BOR.ALTERNATE_ROUTING_DESIGNATOR
, -99)
AND WFS.PRIMARY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
AND BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
AND WT.OPERATION_SEQ_NUM = BOS.OPERATION_SEQ_NUM
AND BOS.OPERATION_TYPE = 1 GROUP BY WT.ORGANIZATION_ID
, WT.WIP_ENTITY_ID
, WT.OPERATION_SEQ_NUM
, WT.RESOURCE_ID
, WT.ACTIVITY_ID
, WFS.QUANTITY_COMPLETED
, WFS.DATE_CLOSED
, WFS.SCHEDULED_START_DATE
, WFS.SCHEDULED_COMPLETION_DATE
, WT.BASIS_TYPE
, WT.TRANSACTION_UOM
, WT.PRIMARY_UOM
, WT.DEPARTMENT_ID
, INST.INSTANCE_CODE
, WE.WIP_ENTITY_NAME
, BD.DEPARTMENT_CODE
, GSOB.SET_OF_BOOKS_ID
, INC.SEQ_ID
, BOS.OPERATION_SEQUENCE_ID
, MSI.PRIMARY_UOM_CODE
, WE.PRIMARY_ITEM_ID

Columns