eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OPI_EDW_OPI_JOB_RSRC_FCV

Source

View Text - Preformatted


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)*EDW_UTIL.get_uom_conv_rate(wor.uom_code,'') ACT_RSRC_USAGE , DECODE( wor.basis_type, 1, sum(wor.usage_rate_or_amount * wo.scheduled_quantity)*EDW_UTIL.get_uom_conv_rate(wor.uom_code,''), 2, sum(wor.usage_rate_or_amount)*EDW_UTIL.get_uom_conv_rate(wor.uom_code,'') ) PLN_RSRC_USAGE , DECODE( wor.basis_type, 1, sum(wor.usage_rate_or_amount * wo.quantity_completed)*EDW_UTIL.get_uom_conv_rate(wor.uom_code,''), 2, DECODE(sum(wo.quantity_completed), 0,0,sum(wor.usage_rate_or_amount)*EDW_UTIL.get_uom_conv_rate(wor.uom_code,'')) ) STND_RSRC_USAGE , sum(wor.applied_resource_value) ACT_RSRC_USAGE_VAL_B , 1 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 , 1 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(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, 'NON-STANDARD-OPI', DECODE(NVL(bos.operation_sequence_id, -99), -99, 'NON-STANDARD-OPI', 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 , 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 , NVL( edw_currency.get_rate ( MAX(gsob.currency_code), OPI_EDW_JOB_RSRC_PVT.get_act_cmpl_date(wor.organization_id, wor.wip_entity_id,wor.repetitive_schedule_id, wor.operation_seq_num) , NULL), -1) GLOBAL_CURRENCY_RATE 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, BOM_OPERATION_SEQUENCES bos 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 to_char(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 bos.operation_sequence_id (+) = wo.operation_sequence_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 , bos.operation_sequence_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(nvl(wt.usage_rate_or_amount,0) * DECODE(wt.primary_quantity, NULL, nvl(wt.transaction_quantity,0), nvl(wt.primary_quantity,0))), 2, sum(nvl(wt.usage_rate_or_amount,0)) ) * EDW_UTIL.get_uom_conv_rate(wt.transaction_uom,'') ACT_RSRC_USAGE , DECODE( wt.basis_type, 1, sum(nvl(wt.usage_rate_or_amount,0) * DECODE(wt.primary_quantity, NULL, nvl(wt.transaction_quantity,0), nvl(wt.primary_quantity,0))), 2, sum(nvl(wt.usage_rate_or_amount,0)) ) * EDW_UTIL.get_uom_conv_rate(wt.transaction_uom,'')PLN_RSRC_USAGE , DECODE( wt.basis_type, 1, sum(nvl(wt.usage_rate_or_amount,0) * DECODE(wt.primary_quantity, NULL, nvl(wt.transaction_quantity,0), nvl(wt.primary_quantity,0))), 2, sum(nvl(wt.usage_rate_or_amount,0)) ) * EDW_UTIL.get_uom_conv_rate(wt.transaction_uom,'') STND_RSRC_USAGE , sum(wta.base_transaction_value) ACT_RSRC_USAGE_VAL_B , 1 ACT_RSRC_USAGE_VAL_G , sum(wta.base_transaction_value) PLN_RSRC_USAGE_VAL_B , 1 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(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 , 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 , NVL( edw_currency.get_rate ( MAX(gsob.currency_code), wfs.date_closed, NULL), -1) GLOBAL_CURRENCY_RATE 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 to_char(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' 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

View Text - HTML Formatted

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)*EDW_UTIL.GET_UOM_CONV_RATE(WOR.UOM_CODE
, '') ACT_RSRC_USAGE
, DECODE( WOR.BASIS_TYPE
, 1
, SUM(WOR.USAGE_RATE_OR_AMOUNT * WO.SCHEDULED_QUANTITY)*EDW_UTIL.GET_UOM_CONV_RATE(WOR.UOM_CODE
, '')
, 2
, SUM(WOR.USAGE_RATE_OR_AMOUNT)*EDW_UTIL.GET_UOM_CONV_RATE(WOR.UOM_CODE
, '') ) PLN_RSRC_USAGE
, DECODE( WOR.BASIS_TYPE
, 1
, SUM(WOR.USAGE_RATE_OR_AMOUNT * WO.QUANTITY_COMPLETED)*EDW_UTIL.GET_UOM_CONV_RATE(WOR.UOM_CODE
, '')
, 2
, DECODE(SUM(WO.QUANTITY_COMPLETED)
, 0
, 0
, SUM(WOR.USAGE_RATE_OR_AMOUNT)*EDW_UTIL.GET_UOM_CONV_RATE(WOR.UOM_CODE
, '')) ) STND_RSRC_USAGE
, SUM(WOR.APPLIED_RESOURCE_VALUE) ACT_RSRC_USAGE_VAL_B
, 1 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
, 1 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(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
, 'NON-STANDARD-OPI'
, DECODE(NVL(BOS.OPERATION_SEQUENCE_ID
, -99)
, -99
, 'NON-STANDARD-OPI'
, 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
, 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
, NVL( EDW_CURRENCY.GET_RATE ( MAX(GSOB.CURRENCY_CODE)
, OPI_EDW_JOB_RSRC_PVT.GET_ACT_CMPL_DATE(WOR.ORGANIZATION_ID
, WOR.WIP_ENTITY_ID
, WOR.REPETITIVE_SCHEDULE_ID
, WOR.OPERATION_SEQ_NUM)
, NULL)
, -1) GLOBAL_CURRENCY_RATE
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
, BOM_OPERATION_SEQUENCES BOS
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 TO_CHAR(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 BOS.OPERATION_SEQUENCE_ID (+) = WO.OPERATION_SEQUENCE_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
, BOS.OPERATION_SEQUENCE_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(NVL(WT.USAGE_RATE_OR_AMOUNT
, 0) * DECODE(WT.PRIMARY_QUANTITY
, NULL
, NVL(WT.TRANSACTION_QUANTITY
, 0)
, NVL(WT.PRIMARY_QUANTITY
, 0)))
, 2
, SUM(NVL(WT.USAGE_RATE_OR_AMOUNT
, 0)) ) * EDW_UTIL.GET_UOM_CONV_RATE(WT.TRANSACTION_UOM
, '') ACT_RSRC_USAGE
, DECODE( WT.BASIS_TYPE
, 1
, SUM(NVL(WT.USAGE_RATE_OR_AMOUNT
, 0) * DECODE(WT.PRIMARY_QUANTITY
, NULL
, NVL(WT.TRANSACTION_QUANTITY
, 0)
, NVL(WT.PRIMARY_QUANTITY
, 0)))
, 2
, SUM(NVL(WT.USAGE_RATE_OR_AMOUNT
, 0)) ) * EDW_UTIL.GET_UOM_CONV_RATE(WT.TRANSACTION_UOM
, '')PLN_RSRC_USAGE
, DECODE( WT.BASIS_TYPE
, 1
, SUM(NVL(WT.USAGE_RATE_OR_AMOUNT
, 0) * DECODE(WT.PRIMARY_QUANTITY
, NULL
, NVL(WT.TRANSACTION_QUANTITY
, 0)
, NVL(WT.PRIMARY_QUANTITY
, 0)))
, 2
, SUM(NVL(WT.USAGE_RATE_OR_AMOUNT
, 0)) ) * EDW_UTIL.GET_UOM_CONV_RATE(WT.TRANSACTION_UOM
, '') STND_RSRC_USAGE
, SUM(WTA.BASE_TRANSACTION_VALUE) ACT_RSRC_USAGE_VAL_B
, 1 ACT_RSRC_USAGE_VAL_G
, SUM(WTA.BASE_TRANSACTION_VALUE) PLN_RSRC_USAGE_VAL_B
, 1 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(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
, 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
, NVL( EDW_CURRENCY.GET_RATE ( MAX(GSOB.CURRENCY_CODE)
, WFS.DATE_CLOSED
, NULL)
, -1) GLOBAL_CURRENCY_RATE
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 TO_CHAR(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'
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