[Home] [Help]
249: ,p_owner => l_schema
250: ,p_retcode => retcode);
251: --
252: l_stmt_num := 30;
253: oki_dbi_scm_rsg_api_pvt.drop_index(p_table_name => 'opi_dbi_wms_op_stg'
254: ,p_owner => l_schema
255: ,p_retcode => retcode);
256: --
257: l_stmt_num := 40;
301: ,p_owner =>l_schema
302: ,p_retcode =>retcode);
303: --
304: l_stmt_num := 30;
305: oki_dbi_scm_rsg_api_pvt.create_index(p_table_name=>'opi_dbi_wms_op_stg'
306: ,p_owner =>l_schema
307: ,p_retcode =>retcode);
308: --
309: l_stmt_num := 40;
357: WHERE ETL_TYPE = 'WMS_WAA';
358: END;
359: --Truncate Staging Tables
360: l_stmt_num := 30;
361: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_op_stg';
362: l_stmt_num := 40;
363: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_tasks_stg';
364: l_stmt_num := 50;
365: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_ex_stg';
413: l_stmt_num := 20;
414: --Truncate Staging Tables
415: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_tasks_stg';
416: l_stmt_num := 30;
417: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_op_stg';
418: l_stmt_num := 40;
419: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_ex_stg';
420: l_stmt_num := 50;
421: END IF;
556: -- Gather Status for all the Staging Tables
557: --
558: gather_stats('OPI_DBI_WMS_TASKS_STG');
559: l_stmt_num := 20;
560: gather_stats('OPI_DBI_WMS_OP_STG');
561: l_stmt_num := 30;
562: gather_stats('OPI_DBI_WMS_EX_STG');
563: l_stmt_num := 40;
564: --
755: --
756: --Collect all the Operation Plans into OP Staging Table
757: --
758: INSERT /*+ append parallel(ops) */
759: INTO opi_dbi_wms_op_stg ops
760: (ops.organization_id
761: ,ops.subinventory_code
762: ,ops.inventory_item_id
763: ,ops.operation_plan_id
885: ,ops.status operation_plan_status
886: ,ops.op_plan_instance_id op_plan_instance_id
887: ,ops.plan_execution_end_date completion_date
888: ,wmx.reason_id reason_id
889: FROM opi_dbi_wms_op_stg ops
890: ,wms_exceptions wmx
891: ,opi_dbi_wms_tasks_stg tasks
892: ,mtl_transaction_reasons mtr
893: WHERE tasks.op_plan_instance_id = ops.op_plan_instance_id
1066: ,g_request_id request_id
1067: ,g_program_application_id program_application_id
1068: ,g_program_id program_id
1069: ,g_sysdate program_update_date
1070: FROM opi_dbi_wms_op_stg ops
1071: ,(SELECT /*+ parallel (ex) use_hash (ex) */
1072: NVL(ex.op_plan_instance_id,0) op_plan_instance_id
1073: ,count(ex.exception_id) ex_cnt
1074: FROM opi_dbi_wms_ex_stg ex
1286: l_stmt_num := 10;
1287: --
1288: --Collect all the Operation Plans into OP Staging Table
1289: --
1290: INSERT INTO opi_dbi_wms_op_stg ops
1291: (ops.organization_id
1292: ,ops.subinventory_code
1293: ,ops.inventory_item_id
1294: ,ops.operation_plan_id
1411: ,ops.status operation_plan_status
1412: ,ops.op_plan_instance_id op_plan_instance_id
1413: ,ops.plan_execution_end_date completion_date
1414: ,wmx.reason_id reason_id
1415: FROM opi_dbi_wms_op_stg ops
1416: ,wms_exceptions wmx
1417: ,wms_dispatched_tasks_history wdth
1418: ,mtl_transaction_reasons mtr
1419: WHERE wdth.op_plan_instance_id = ops.op_plan_instance_id
1582: ,sum(ops.plan_elapsed_time) plan_elapsed_time
1583: ,nvl(count(ops.op_plan_instance_id),0) executions
1584: ,nvl(count(exs.op_plan_instance_id),0) executions_with_exceptions
1585: ,sum(nvl(exs.ex_cnt,0)) exceptions
1586: FROM opi_dbi_wms_op_stg ops
1587: ,(SELECT NVL(ex.op_plan_instance_id,0) op_plan_instance_id
1588: ,count(ex.exception_id) ex_cnt
1589: FROM opi_dbi_wms_ex_stg ex
1590: WHERE ex.operation_plan_indicator = 2