[Home] [Help]
4: -- Package Variables
5:
6: g_global_start_date date;
7: g_isc_schema varchar2(30);
8: g_object_name constant varchar2(30) := 'ISC_DR_MTTR_F';
9:
10: -- Initial Load
11: -- Parameters:
12: -- retcode - 0 on successful completion, -1 on error and 1 for warning.
66:
67: l_stmt_id := 20;
68: delete
69: from isc_dr_inc
70: where fact_name = 'ISC_DR_MTTR_F';
71:
72: bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
73:
74: l_stmt_id := 30;
71:
72: bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
73:
74: l_stmt_id := 30;
75: execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_MTTR_F';
76:
77: bis_collection_utilities.log( 'Truncated table ISC_DR_MTTR_F', 1 );
78:
79: l_stmt_id := 40;
73:
74: l_stmt_id := 30;
75: execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_MTTR_F';
76:
77: bis_collection_utilities.log( 'Truncated table ISC_DR_MTTR_F', 1 );
78:
79: l_stmt_id := 40;
80: if isc_depot_backlog_etl_pkg.get_last_run_date
81: ( p_fact_name => 'ISC_DR_REPAIR_ORDERS_F'
97: l_stmt_id := 50;
98: -- Insertion of Non-refurbishment repair orders for Serialized and
99: -- Non-serialized items
100:
101: insert /*+ append parallel(isc_dr_mttr_f) */
102: into isc_dr_mttr_f
103: ( repair_line_id
104: , repair_start_date
105: , repair_end_date
98: -- Insertion of Non-refurbishment repair orders for Serialized and
99: -- Non-serialized items
100:
101: insert /*+ append parallel(isc_dr_mttr_f) */
102: into isc_dr_mttr_f
103: ( repair_line_id
104: , repair_start_date
105: , repair_end_date
106: , time_to_repair
160: group by
161: bdr.repair_line_id;
162:
163: l_rowcount := sql%rowcount;
164: bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
165:
166: l_stmt_id := 60;
167: commit;
168:
169: l_stmt_id := 70;
170: -- Insertion of repair orders with Refurbishment(IO) repair type for
171: -- Non-serialized items
172:
173: insert /*+ append parallel(isc_dr_mttr_f) */
174: into isc_dr_mttr_f
175: ( repair_line_id
176: , repair_start_date
177: , repair_end_date
170: -- Insertion of repair orders with Refurbishment(IO) repair type for
171: -- Non-serialized items
172:
173: insert /*+ append parallel(isc_dr_mttr_f) */
174: into isc_dr_mttr_f
175: ( repair_line_id
176: , repair_start_date
177: , repair_end_date
178: , time_to_repair
227: group by
228: bdr.repair_line_id;
229:
230: l_temp_rowcount := sql%rowcount;
231: bis_collection_utilities.log( 'Inserted ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
232: l_rowcount := l_rowcount + l_temp_rowcount;
233:
234: l_stmt_id := 80;
235: commit;
237: l_stmt_id := 90;
238: -- Insertion of repair orders with Refurbishment(IO) repair type for
239: -- Serialized items
240:
241: insert /*+ append parallel(isc_dr_mttr_f) */
242: into isc_dr_mttr_f
243: ( repair_line_id
244: , repair_start_date
245: , repair_end_date
238: -- Insertion of repair orders with Refurbishment(IO) repair type for
239: -- Serialized items
240:
241: insert /*+ append parallel(isc_dr_mttr_f) */
242: into isc_dr_mttr_f
243: ( repair_line_id
244: , repair_start_date
245: , repair_end_date
246: , time_to_repair
297: and bdr.date_closed is not null
298: and bdr.ro_creation_date >= g_global_start_date;
299:
300: l_temp_rowcount := sql%rowcount;
301: bis_collection_utilities.log( 'Inserted ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
302: l_rowcount := l_rowcount + l_temp_rowcount;
303:
304: l_stmt_id := 100;
305: commit;
321: , program_application_id
322: , request_id
323: )
324: values
325: ( 'ISC_DR_MTTR_F'
326: , l_run_date
327: , l_user_id
328: , sysdate
329: , sysdate
439: bis_collection_utilities.log( 'Global Start Date: ' || fnd_date.date_to_displaydt(g_global_start_date), 1 );
440:
441: l_stmt_id := 20;
442: if isc_depot_backlog_etl_pkg.get_last_run_date
443: ( p_fact_name => 'ISC_DR_MTTR_F'
444: , x_run_date => l_run_date
445: , x_message => l_message
446: ) <> c_ok then
447: raise l_exception;
476: -- Insertion / Updation of repair orders with Non-refurbishment repair type
477: -- for Serialized and Non-serialized items
478:
479: merge into
480: isc_dr_mttr_f fact
481: using
482: ( select
483: bdr.repair_line_id repair_line_id
484: , min(pdr.transaction_date) repair_start_date
560: , l_request_id
561: );
562:
563: l_rowcount := sql%rowcount;
564: bis_collection_utilities.log( 'Merged ' || l_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
565:
566: l_stmt_id := 50;
567: -- Insertion/Updation of repair orders with Refurbishment repair type
568: -- for Non-serialized items
567: -- Insertion/Updation of repair orders with Refurbishment repair type
568: -- for Non-serialized items
569:
570: merge into
571: isc_dr_mttr_f fact
572: using
573: ( select
574: bdr.repair_line_id repair_line_id
575: , min(pdr.transaction_date) repair_start_date
648: , l_request_id
649: );
650:
651: l_temp_rowcount := sql%rowcount;
652: bis_collection_utilities.log( 'Merged ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
653: l_rowcount := l_rowcount + l_temp_rowcount;
654:
655: l_stmt_id := 60;
656: -- Insertion/Updation of repair orders with Refurbishment repair type for
656: -- Insertion/Updation of repair orders with Refurbishment repair type for
657: -- Serialized items
658:
659: merge into
660: isc_dr_mttr_f fact
661: using
662: ( select
663: bdr.repair_line_id repair_line_id
664: , pdr.transaction_date repair_start_date
739: , l_request_id
740: );
741:
742: l_temp_rowcount := sql%rowcount;
743: bis_collection_utilities.log( 'Merged ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
744: l_rowcount := l_rowcount + l_temp_rowcount;
745:
746: l_stmt_id := 70;
747: update isc_dr_inc
752: , program_id = l_program_id
753: , program_login_id = l_program_login_id
754: , program_application_id = l_program_application_id
755: , request_id = l_request_id
756: WHERE fact_name = 'ISC_DR_MTTR_F' ;
757:
758: bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );
759:
760: l_stmt_id := 80;