[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MIN(creation_date)
FROM wms_dispatched_tasks_history
WHERE transaction_temp_id IS NOT NULL;
SELECT last_run_date
FROM opi_dbi_conc_prog_run_log
WHERE etl_type = 'WMS_WAA_GSD';
UPDATE opi_dbi_conc_prog_run_log log
SET log.last_run_date = x_gsd
,log.last_update_date = sysdate
,log.last_updated_by = g_user_id
,log.last_update_login = g_login_id
WHERE log.etl_type = 'WMS_WAA_GSD';
INSERT INTO opi_dbi_conc_prog_run_log log
(log.etl_type
,log.last_run_date
,log.created_by
,log.creation_date
,log.last_update_date
,log.last_updated_by
,log.last_update_login
,log.program_id
,log.program_login_id
,log.program_application_id
,log.request_id)
VALUES ('WMS_WAA_GSD'
,x_gsd
,g_user_id
,sysdate
,sysdate
,g_user_id
,g_login_id
,g_program_id
,g_login_id
,g_program_application_id
,g_request_id);
INSERT INTO opi_dbi_conc_prog_run_log
(etl_type
,last_run_date
,created_by
,creation_date
,last_update_date
,last_updated_by
,last_update_login
,program_id
,program_login_id
,program_application_id
,request_id)
VALUES ('WMS_WAA'
,g_last_run_date
,g_user_id
,sysdate
,sysdate
,g_user_id
,g_login_id
,g_program_id
,g_login_id
,g_program_application_id
,g_request_id);
bis_collection_utilities.put_line('Updated the information of '
||'Last Collection Date');
bis_collection_utilities.put_line('Failed to update collection date'
||' in log table. Aborting');
UPDATE opi_dbi_conc_prog_run_log
SET last_run_date = g_last_run_date
,last_update_date = SYSDATE
,last_updated_by = g_user_id
,last_update_login = g_login_id
,program_id = g_program_id
,program_login_id = g_login_id
,program_application_id = g_program_application_id
,request_id = g_request_id
WHERE etl_type = 'WMS_WAA';
bis_collection_utilities.put_line('Failed to update collection date'
||' in log table. Aborting');
DELETE FROM opi_dbi_conc_prog_run_log
WHERE ETL_TYPE = 'WMS_WAA';
SELECT table_owner
FROM USER_SYNONYMS
WHERE synonym_name = p_table_name;
SELECT last_run_date
FROM opi_dbi_conc_prog_run_log
WHERE etl_type = 'WMS_WAA';
| 07-APR-2005 MOHIT Updated the performance hints on OLTP tables |
******************************************************************************/
PROCEDURE init_tasks(errbuf IN OUT NOCOPY VARCHAR2
,retcode IN OUT NOCOPY VARCHAR2) IS
--
l_procedure VARCHAR2(100);
INSERT /*+ append parallel(tasks) */
INTO opi_dbi_wms_tasks_stg tasks
(tasks.task_id
,tasks.organization_id
,tasks.inventory_item_id
,tasks.task_type
,tasks.completion_date
,tasks.op_plan_instance_id
,tasks.is_parent
,tasks.subinventory_code
,tasks.transaction_temp_id
)
select /*+ ordered parallel (wdth) parallel (msi)
use_hash (sinv,wdth,msi) */
wdth.task_id task_id
,wdth.organization_id organization_id
,wdth.inventory_item_id inventory_item_id
,wdth.task_type task_type
,wdth.drop_off_time completion_date
,wdth.op_plan_instance_id op_plan_instance_id
,nvl(wdth.is_parent,'Y')
,CASE WHEN wdth.task_type = 1 THEN wdth.source_subinventory_code
WHEN wdth.task_type in (2,8) THEN wdth.dest_subinventory_code
END subinventory_code
,wdth.transaction_temp_id transaction_temp_id
from wms_dispatched_tasks_history wdth
,mtl_system_items_b msi
,mtl_secondary_inventories sinv
where wdth.inventory_item_id = msi.inventory_item_id
AND wdth.organization_id = msi.organization_id
AND decode(wdth.task_type
,1,wdth.source_subinventory_code
,wdth.dest_subinventory_code) = sinv.secondary_inventory_name
AND wdth.organization_id = sinv.organization_id
AND wdth.drop_off_time >= g_gsd
AND wdth.drop_off_time <= g_last_run_date
AND wdth.transaction_temp_id IS NOT NULL
AND wdth.task_type in (1,2,8)
AND wdth.status in (6,11);
INSERT /*+ append parallel(ops) */
INTO opi_dbi_wms_op_stg ops
(ops.organization_id
,ops.subinventory_code
,ops.inventory_item_id
,ops.operation_plan_id
,ops.op_plan_instance_id
,ops.status
,ops.plan_execution_start_date
,ops.plan_execution_end_date
,ops.plan_elapsed_time
)
SELECT /*+ parallel (tasks) parallel (woiph)
parallel (wop) parallel (msi) parallel (sinv)
use_hash (tasks) use_hash (woiph)
use_hash (wop) use_hash (msi) use_hash (sinv) */
woiph.organization_id organization_id
,tasks.subinventory_code subinventory_code
,tasks.inventory_item_id inventory_item_id
,wop.operation_plan_id operation_plan_id
,woiph.op_plan_instance_id op_plan_instance_id
,woiph.status status
,woiph.plan_execution_start_date plan_execution_start_date
,woiph.plan_execution_end_date plan_execution_end_date
,( woiph.plan_execution_end_date
- woiph.plan_execution_start_date)*24 plan_elapsed_time
FROM opi_dbi_wms_tasks_stg tasks
,wms_op_plan_instances_hist woiph
,wms_op_plans_b wop
,mtl_system_items_b msi
,mtl_secondary_inventories sinv
WHERE tasks.op_plan_instance_id = woiph.op_plan_instance_id
AND woiph.operation_plan_id = wop.operation_plan_id
AND tasks.subinventory_code = sinv.secondary_inventory_name
AND tasks.organization_id = sinv.organization_id
AND tasks.inventory_item_id = msi.inventory_item_id
AND tasks.organization_id = msi.organization_id
AND woiph.status in (3,4,5)
AND wop.activity_type_id = 1
AND woiph.plan_execution_start_date >= g_gsd
AND woiph.plan_execution_end_date <= g_last_run_date
AND tasks.is_parent = 'Y'
AND tasks.task_type in (2,8);
INSERT /*+ append (exs) */
INTO opi_dbi_wms_ex_stg exs
(exs.exception_id
,exs.task_id
,exs.organization_id
,exs.inventory_item_id
,exs.subinventory_code
,exs.operation_plan_id
,exs.operation_plan_indicator
,exs.operation_plan_status
,exs.op_plan_instance_id
,exs.completion_date
,exs.reason_id
)
SELECT /*+ parallel (tasks) parallel (wmx) parallel (mtr)
use_hash (tasks) use_hash (wmx) use_hash (mtr) */
wmx.sequence_number exception_id
,wmx.task_id task_id
,tasks.organization_id organization_id
,tasks.inventory_item_id inventory_item_id
,tasks.subinventory_code subinventory_code
,NULL operation_plan_id
,1 operation_plan_indicator
,NULL operation_plan_status
,NULL op_plan_instance_id
,tasks.completion_date completion_date
,wmx.reason_id reason_id
FROM opi_dbi_wms_tasks_stg tasks
,wms_exceptions wmx
,mtl_transaction_reasons mtr
WHERE wmx.task_id = tasks.transaction_temp_id
AND tasks.task_type = 1
AND tasks.is_parent = 'Y'
AND mtr.reason_id = wmx.reason_id
AND mtr.reason_type = 1
UNION ALL
SELECT /*+ parallel (ops) parallel (wmx) parallel (tasks) parallel (mtr)
use_hash (ops) use_hash (wmx) use_hash (tasks) use_hash (mtr) */
wmx.sequence_number exception_id
,wmx.task_id task_id
,ops.organization_id organization_id
,ops.inventory_item_id inventory_item_id
,ops.subinventory_code subinventory_code
,ops.operation_plan_id operation_plan_id
,2 operation_plan_indicator
,ops.status operation_plan_status
,ops.op_plan_instance_id op_plan_instance_id
,ops.plan_execution_end_date completion_date
,wmx.reason_id reason_id
FROM opi_dbi_wms_op_stg ops
,wms_exceptions wmx
,opi_dbi_wms_tasks_stg tasks
,mtl_transaction_reasons mtr
WHERE tasks.op_plan_instance_id = ops.op_plan_instance_id
AND tasks.task_type in (2,8)
AND wmx.task_id = tasks.transaction_temp_id
AND tasks.organization_id = ops.organization_id
AND mtr.reason_id = wmx.reason_id;
INSERT /*+ append parallel(taskf) */
INTO opi_dbi_wms_tasks_f taskf
(taskf.organization_id
,taskf.subinventory_code
,taskf.inventory_item_id
,taskf.completion_date
,taskf.picks
,taskf.picks_with_exceptions
,taskf.pick_exceptions
,taskf.creation_date
,taskf.last_update_date
,taskf.created_by
,taskf.last_updated_by
,taskf.last_update_login
,taskf.request_id
,taskf.program_application_id
,taskf.program_id
,taskf.program_update_date
)
SELECT /*+ parallel (tasks) parallel (exs)
use_hash (tasks) use_hash (exs) */
tasks.organization_id organization_id
,tasks.subinventory_code subinventory_code
,tasks.inventory_item_id inventory_item_id
,TRUNC(tasks.completion_date) completion_date
,COUNT(tasks.task_id) picks
,COUNT(exs.task_id) picks_with_exceptions
,SUM(exs.ex_cnt) pick_exceptions
,SYSDATE creation_date
,SYSDATE last_update_date
,g_user_id created_by
,g_user_id last_updated_by
,g_login_id last_update_login
,g_request_id request_id
,g_program_application_id program_application_id
,g_program_id program_id
,g_sysdate program_update_date
FROM opi_dbi_wms_tasks_stg tasks
,(SELECT /*+ parallel (ex) use_hash (ex) */
ex.task_id
,COUNT(ex.exception_id) ex_cnt
FROM opi_dbi_wms_ex_stg ex
WHERE ex.operation_plan_indicator = 1
GROUP BY task_id ) exs
WHERE tasks.transaction_temp_id = exs.task_id(+)
AND tasks.task_type = 1
GROUP BY tasks.organization_id
,tasks.subinventory_code
,tasks.inventory_item_id
,TRUNC(tasks.completion_date);
INSERT /*+ append parallel(opf) */
INTO opi_dbi_wms_op_f opf
(opf.organization_id
,opf.subinventory_code
,opf.inventory_item_id
,opf.operation_plan_id
,opf.status
,opf.plan_execution_end_date
,opf.plan_elapsed_time
,opf.executions
,opf.executions_with_exceptions
,opf.exceptions
,opf.creation_date
,opf.last_update_date
,opf.created_by
,opf.last_updated_by
,opf.last_update_login
,opf.request_id
,opf.program_application_id
,opf.program_id
,opf.program_update_date
)
SELECT /*+ parallel (ops) parallel (exs)
use_hash (ops) use_hash (exs) */
ops.organization_id organization_id
,ops.subinventory_code subinventory_code
,ops.inventory_item_id inventory_item_id
,ops.operation_plan_id operation_plan_id
,ops.status status
,trunc(ops.plan_execution_end_date) plan_execution_end_date
,sum(ops.plan_elapsed_time) plan_elapsed_time
,count(ops.op_plan_instance_id) executions
,count(exs.op_plan_instance_id) executions_with_exceptions
,sum(nvl(exs.ex_cnt,0)) exceptions
,SYSDATE creation_date
,SYSDATE last_update_date
,g_user_id created_by
,g_user_id last_updated_by
,g_login_id last_update_login
,g_request_id request_id
,g_program_application_id program_application_id
,g_program_id program_id
,g_sysdate program_update_date
FROM opi_dbi_wms_op_stg ops
,(SELECT /*+ parallel (ex) use_hash (ex) */
NVL(ex.op_plan_instance_id,0) op_plan_instance_id
,count(ex.exception_id) ex_cnt
FROM opi_dbi_wms_ex_stg ex
WHERE ex.operation_plan_indicator = 2
GROUP BY nvl(ex.op_plan_instance_id,0)) exs
WHERE ops.op_plan_instance_id = exs.op_plan_instance_id(+)
GROUP BY ops.organization_id
,ops.subinventory_code
,ops.inventory_item_id
,ops.operation_plan_id
,ops.status
,TRUNC(ops.plan_execution_end_date);
INSERT /*+ append (exf) */
INTO opi_dbi_wms_ex_f exf
(exf.organization_id
,exf.subinventory_code
,exf.inventory_item_id
,exf.operation_plan_id
,exf.operation_plan_indicator
,exf.operation_plan_status
,exf.reason_id
,exf.completion_date
,exf.exceptions
,exf.creation_date
,exf.last_update_date
,exf.created_by
,exf.last_updated_by
,exf.last_update_login
,exf.request_id
,exf.program_application_id
,exf.program_id
,exf.program_update_date
)
SELECT /*+ parallel (exs) use_hash (exs) */
exs.organization_id organization_id
,exs.subinventory_code subinventory_code
,exs.inventory_item_id inventory_item_id
,exs.operation_plan_id operation_plan_id
,exs.operation_plan_indicator operation_plan_indicator
,exs.operation_plan_status operation_plan_status
,exs.reason_id reason_id
,trunc(exs.completion_date) completion_date
,COUNT(exs.exception_id) exceptions
,SYSDATE creation_date
,SYSDATE last_update_date
,g_user_id created_by
,g_user_id last_updated_by
,g_login_id last_update_login
,g_request_id request_id
,g_program_application_id program_application_id
,g_program_id program_id
,g_sysdate program_update_date
FROM opi_dbi_wms_ex_stg exs
GROUP BY exs.organization_id
,exs.subinventory_code
,exs.inventory_item_id
,exs.operation_plan_id
,exs.operation_plan_indicator
,exs.operation_plan_status
,exs.reason_id
,TRUNC(exs.completion_date) ;
INSERT INTO opi_dbi_wms_tasks_stg tasks
(tasks.task_id
,tasks.organization_id
,tasks.inventory_item_id
,tasks.task_type
,tasks.completion_date
,tasks.op_plan_instance_id
,tasks.is_parent
,tasks.subinventory_code
,tasks.transaction_temp_id
)
select wdth.task_id task_id
,wdth.organization_id organization_id
,wdth.inventory_item_id inventory_item_id
,wdth.task_type task_type
,wdth.drop_off_time completion_date
,wdth.op_plan_instance_id op_plan_instance_id
,nvl(wdth.is_parent,'Y') is_parent
,wdth.source_subinventory_code subinventory_code
,wdth.transaction_temp_id transaction_temp_id
from wms_dispatched_tasks_history wdth
,mtl_system_items_b msi
,mtl_secondary_inventories sinv
,opi_dbi_conc_prog_run_log log
where wdth.inventory_item_id = msi.inventory_item_id
AND wdth.organization_id = msi.organization_id
AND decode(wdth.task_type
,1,source_subinventory_code
,dest_subinventory_code) = sinv.secondary_inventory_name
AND wdth.organization_id = sinv.organization_id
AND wdth.drop_off_time >= g_gsd
AND wdth.drop_off_time <= g_last_run_date
AND wdth.drop_off_time >= log.last_run_date
AND log.etl_type = 'WMS_WAA'
AND wdth.transaction_temp_id IS NOT NULL
and nvl(wdth.is_parent,'Y') = 'Y'
AND wdth.task_type = 1
AND wdth.status in (6,11);
INSERT INTO opi_dbi_wms_op_stg ops
(ops.organization_id
,ops.subinventory_code
,ops.inventory_item_id
,ops.operation_plan_id
,ops.op_plan_instance_id
,ops.status
,ops.plan_execution_start_date
,ops.plan_execution_end_date
,ops.plan_elapsed_time
)
SELECT wopih.organization_id organization_id
,wdth.dest_subinventory_code subinventory_code
,wdth.inventory_item_id inventory_item_id
,wop.operation_plan_id operation_plan_id
,wopih.op_plan_instance_id op_plan_instance_id
,wopih.status status
,wopih.plan_execution_start_date plan_execution_start_date
,wopih.plan_execution_end_date plan_execution_end_date
,( wopih.plan_execution_end_date
- wopih.plan_execution_start_date)*24 plan_elapsed_time
FROM wms_dispatched_tasks_history wdth
,opi_dbi_conc_prog_run_log log
,wms_op_plan_instances_hist wopih
,wms_op_plans_b wop
,mtl_system_items_b msi
,mtl_secondary_inventories sinv
WHERE wdth.op_plan_instance_id = wopih.op_plan_instance_id
AND wopih.operation_plan_id = wop.operation_plan_id
AND wdth.dest_subinventory_code = sinv.secondary_inventory_name
AND wdth.organization_id = sinv.organization_id
AND wdth.inventory_item_id = msi.inventory_item_id
AND wdth.organization_id = msi.organization_id
AND wopih.status in (3,4,5)
AND wop.activity_type_id = 1
AND wopih.plan_execution_start_date >= g_gsd
AND wopih.plan_execution_end_date <= g_last_run_date
AND wopih.plan_execution_end_date >= log.last_run_date
AND log.etl_type = 'WMS_WAA'
AND nvl(wdth.is_parent,'Y') = 'Y'
AND wdth.transaction_temp_id IS NOT NULL
AND wdth.drop_off_time >= g_gsd
AND wdth.task_type in (2,8);
INSERT INTO opi_dbi_wms_ex_stg exs
(exs.exception_id
,exs.task_id
,exs.organization_id
,exs.inventory_item_id
,exs.subinventory_code
,exs.operation_plan_id
,exs.operation_plan_indicator
,exs.operation_plan_status
,exs.op_plan_instance_id
,exs.completion_date
,exs.reason_id
)
SELECT wmx.sequence_number exception_id
,wmx.task_id task_id
,tasks.organization_id organization_id
,tasks.inventory_item_id inventory_item_id
,tasks.subinventory_code subinventory_code
,NULL operation_plan_id
,1 operation_plan_indicator
,NULL operation_plan_status
,NULL op_plan_instance_id
,tasks.completion_date completion_date
,wmx.reason_id reason_id
FROM opi_dbi_wms_tasks_stg tasks
,wms_exceptions wmx
,mtl_transaction_reasons mtr
WHERE wmx.task_id = tasks.transaction_temp_id
AND tasks.task_type = 1
AND tasks.is_parent = 'Y'
AND mtr.reason_id = wmx.reason_id
AND mtr.reason_type = 1
UNION ALL
SELECT wmx.sequence_number exception_id
,wmx.task_id task_id
,ops.organization_id organization_id
,ops.inventory_item_id inventory_item_id
,ops.subinventory_code subinventory_code
,ops.operation_plan_id operation_plan_id
,2 operation_plan_indicator
,ops.status operation_plan_status
,ops.op_plan_instance_id op_plan_instance_id
,ops.plan_execution_end_date completion_date
,wmx.reason_id reason_id
FROM opi_dbi_wms_op_stg ops
,wms_exceptions wmx
,wms_dispatched_tasks_history wdth
,mtl_transaction_reasons mtr
WHERE wdth.op_plan_instance_id = ops.op_plan_instance_id
AND wdth.task_type in (2,8)
AND wdth.transaction_temp_id IS NOT NULL
AND wmx.task_id = wdth.transaction_temp_id
AND wdth.organization_id = ops.organization_id
AND mtr.reason_id = wmx.reason_id;
SELECT tasks.organization_id organization_id
,tasks.subinventory_code subinventory_code
,tasks.inventory_item_id inventory_item_id
,TRUNC(tasks.completion_date) completion_date
,COUNT(tasks.task_id) picks
,COUNT(exs.task_id) picks_with_exceptions
,SUM(exs.ex_cnt) pick_exceptions
FROM opi_dbi_wms_tasks_stg tasks
,(SELECT ex.task_id
,COUNT(ex.exception_id) ex_cnt
FROM opi_dbi_wms_ex_stg ex
WHERE ex.operation_plan_indicator = 1
GROUP BY task_id ) exs
WHERE tasks.transaction_temp_id = exs.task_id(+)
AND tasks.task_type = 1
GROUP BY tasks.organization_id
,tasks.subinventory_code
,tasks.inventory_item_id
,TRUNC(tasks.completion_date)
) s
ON ( taskf.organization_id = s.organization_id
AND taskf.subinventory_code = s.subinventory_code
AND taskf.inventory_item_id = s.inventory_item_id
AND taskf.completion_date = s.completion_date
)
WHEN MATCHED THEN
UPDATE SET taskf.picks = taskf.picks + s.picks
,taskf.picks_with_exceptions
= taskf.picks_with_exceptions
+ s.picks_with_exceptions
,taskf.pick_exceptions = taskf.pick_exceptions
+ s.pick_exceptions
,taskf.last_update_date = SYSDATE
,taskf.last_updated_by = g_user_id
,taskf.last_update_login = g_login_id
WHEN NOT MATCHED THEN
INSERT (taskf.organization_id
,taskf.subinventory_code
,taskf.inventory_item_id
,taskf.completion_date
,taskf.picks
,taskf.picks_with_exceptions
,taskf.pick_exceptions
,taskf.creation_date
,taskf.last_update_date
,taskf.created_by
,taskf.last_updated_by
,taskf.last_update_login
,taskf.request_id
,taskf.program_application_id
,taskf.program_id
,taskf.program_update_date
)VALUES
(s.organization_id
,s.subinventory_code
,s.inventory_item_id
,s.completion_date
,s.picks
,s.picks_with_exceptions
,s.pick_exceptions
,SYSDATE
,SYSDATE
,g_user_id
,g_user_id
,g_login_id
,g_request_id
,g_program_application_id
,g_program_id
,g_sysdate
);
SELECT ops.organization_id organization_id
,ops.subinventory_code subinventory_code
,ops.inventory_item_id inventory_item_id
,ops.operation_plan_id operation_plan_id
,ops.status status
,trunc(ops.plan_execution_end_date) plan_execution_end_date
,sum(ops.plan_elapsed_time) plan_elapsed_time
,nvl(count(ops.op_plan_instance_id),0) executions
,nvl(count(exs.op_plan_instance_id),0) executions_with_exceptions
,sum(nvl(exs.ex_cnt,0)) exceptions
FROM opi_dbi_wms_op_stg ops
,(SELECT NVL(ex.op_plan_instance_id,0) op_plan_instance_id
,count(ex.exception_id) ex_cnt
FROM opi_dbi_wms_ex_stg ex
WHERE ex.operation_plan_indicator = 2
GROUP BY nvl(ex.op_plan_instance_id,0)) exs
WHERE ops.op_plan_instance_id = exs.op_plan_instance_id(+)
GROUP BY ops.organization_id
,ops.subinventory_code
,ops.inventory_item_id
,ops.operation_plan_id
,ops.status
,TRUNC(ops.plan_execution_end_date)
) s
ON ( opf.organization_id = s.organization_id
AND opf.subinventory_code = s.subinventory_code
AND opf.inventory_item_id = s.inventory_item_id
AND opf.operation_plan_id = s.operation_plan_id
AND opf.status = s.status
AND opf.plan_execution_end_date = s.plan_execution_end_date
)
WHEN MATCHED THEN
UPDATE SET opf.plan_elapsed_time = opf.plan_elapsed_time
+ s.plan_elapsed_time
,opf.executions = opf.executions
+ s.executions
,opf.executions_with_exceptions = opf.executions_with_exceptions
+ s.executions_with_exceptions
,opf.exceptions = opf.exceptions
+ s.exceptions
,opf.last_update_date = SYSDATE
,opf.last_updated_by = g_user_id
,opf.last_update_login = g_login_id
WHEN NOT MATCHED THEN
INSERT (opf.organization_id
,opf.subinventory_code
,opf.inventory_item_id
,opf.operation_plan_id
,opf.status
,opf.plan_execution_end_date
,opf.plan_elapsed_time
,opf.executions
,opf.executions_with_exceptions
,opf.exceptions
,opf.creation_date
,opf.last_update_date
,opf.created_by
,opf.last_updated_by
,opf.last_update_login
,opf.request_id
,opf.program_application_id
,opf.program_id
,opf.program_update_date
)VALUES
(s.organization_id
,s.subinventory_code
,s.inventory_item_id
,s.operation_plan_id
,s.status
,s.plan_execution_end_date
,s.plan_elapsed_time
,s.executions
,s.executions_with_exceptions
,s.exceptions
,SYSDATE
,SYSDATE
,g_user_id
,g_user_id
,g_login_id
,g_request_id
,g_program_application_id
,g_program_id
,g_sysdate
);
SELECT exs.organization_id organization_id
,exs.subinventory_code subinventory_code
,exs.inventory_item_id inventory_item_id
,exs.operation_plan_id operation_plan_id
,exs.operation_plan_indicator operation_plan_indicator
,exs.operation_plan_status operation_plan_status
,exs.reason_id reason_id
,trunc(exs.completion_date) completion_date
,COUNT(exs.exception_id) exceptions
FROM opi_dbi_wms_ex_stg exs
GROUP BY exs.organization_id
,exs.subinventory_code
,exs.inventory_item_id
,exs.operation_plan_id
,exs.operation_plan_indicator
,exs.operation_plan_status
,exs.reason_id
,TRUNC(exs.completion_date)
) s
ON ( exf.organization_id = s.organization_id
AND exf.subinventory_code = s.subinventory_code
AND exf.inventory_item_id = s.inventory_item_id
AND exf.operation_plan_id = s.operation_plan_id
AND exf.operation_plan_indicator = s.operation_plan_indicator
AND exf.operation_plan_status = s.operation_plan_status
AND exf.reason_id = s.reason_id
AND exf.completion_date = s.completion_date
)
WHEN MATCHED THEN UPDATE SET exf.exceptions = exf.exceptions + s.exceptions
,exf.last_update_date = SYSDATE
,exf.last_updated_by = g_user_id
,exf.last_update_login = g_login_id
WHEN NOT MATCHED THEN
INSERT (exf.organization_id
,exf.subinventory_code
,exf.inventory_item_id
,exf.operation_plan_id
,exf.operation_plan_indicator
,exf.operation_plan_status
,exf.reason_id
,exf.completion_date
,exf.exceptions
,exf.creation_date
,exf.last_update_date
,exf.created_by
,exf.last_updated_by
,exf.last_update_login
,exf.request_id
,exf.program_application_id
,exf.program_id
,exf.program_update_date
)VALUES
(s.organization_id
,s.subinventory_code
,s.inventory_item_id
,s.operation_plan_id
,s.operation_plan_indicator
,s.operation_plan_status
,s.reason_id
,s.completion_date
,s.exceptions
,SYSDATE
,SYSDATE
,g_user_id
,g_user_id
,g_login_id
,g_request_id
,g_program_application_id
,g_program_id
,g_sysdate
);