[Home] [Help]
PACKAGE BODY: APPS.ISC_DEPOT_MARGIN_ETL_PKG
Source
1 package body isc_depot_margin_etl_pkg as
2 /* $Header: iscdepotmrgetlb.pls 120.1 2006/09/21 01:21:04 kreardon noship $ */
3
4 -- File scope variables
5 g_isc_schema varchar2(30);
6 g_global_start_date date;
7 g_global_curr_code varchar2(30);
8 g_global_sec_curr_code varchar2(30);
9 g_global_rate_type varchar2(30);
10 g_global_sec_rate_type varchar2(30);
11
12 g_user_id number;
13 g_login_id number;
14 g_program_id number;
15 g_program_login_id number;
16 g_program_application_id number;
17 g_request_id number;
18
19 g_charges_object_name constant varchar2(30) := 'ISC_DR_CHARGES_F';
20 g_costs_object_name constant varchar2(30) := 'ISC_DR_COSTS_F';
21
22 -- Common Procedures Definitions
23
24 -- err_mesg
25 function err_mesg
26 ( p_mesg in varchar2
27 , p_proc_name in varchar2 default null
28 , p_stmt_id in number default -1
29 )
30 return varchar2
31 is
32
33 l_formatted_message varchar2(3000);
34
35 begin
36
37 l_formatted_message := substr( p_proc_name || ' #' || to_char (p_stmt_id) ||
38 ': ' || p_mesg
39 , 1
40 , c_errbuf_size
41 );
42 return l_formatted_message;
43
44 exception
45
46 when others then
47 -- the exception happened in the exception reporting function !!
48 -- return with ERROR.
49 l_formatted_message := 'Error in error reporting. ' || p_mesg;
50 return l_formatted_message;
51
52 end err_mesg;
53
54 -- check_initial_load_setup
55 function check_initial_load_setup
56 ( x_message out nocopy varchar2
57 )
58 return number
59 is
60
61 l_func_name constant varchar2(40) := 'check_initial_load_setup';
62 l_stmnt_id number;
63 l_setup_good boolean;
64 l_status varchar2(30);
65 l_industry varchar2(30);
66 l_exception exception;
67 l_message varchar2(1000);
68
69 begin
70
71 -- Check for the global start date setup.
72 -- These parameter must be set up prior to any DBI load.
73
74 l_stmnt_id := 0;
75 g_global_start_date := trunc(bis_common_parameters.get_global_start_date);
76
77 l_stmnt_id := 10;
78 g_global_curr_code := bis_common_parameters.get_currency_code;
79
80 l_stmnt_id := 20;
81 g_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
82
83 l_stmnt_id := 30;
84 g_global_rate_type := bis_common_parameters.get_rate_type;
85
86 l_stmnt_id := 40;
87 g_global_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
88
89 l_stmnt_id := 50;
90 if g_global_start_date is null or
91 g_global_curr_code is null or
92 g_global_rate_type is null then
93
94 l_message := 'Please check the Global Start Date, Global Currency Code, ' ||
95 'Global Rate Type. One of these variables is NULL. ' ||
96 'Please define the same and re-run the load';
97 raise l_exception;
98
99 end if;
100
101 l_stmnt_id := 60;
102 if g_global_sec_curr_code is not null and
103 g_global_sec_rate_type is null then
104
105 l_message := 'The Secondary Global Rate Type is NULL. ' ||
106 'Please define the same and re-run the load';
107 raise l_exception;
108
109 end if;
110
111 l_stmnt_id := 70;
112 l_setup_good := fnd_installation.get_app_info
113 ( 'ISC'
114 , l_status
115 , l_industry
116 , g_isc_schema
117 );
118
119 l_stmnt_id := 80;
120 if l_setup_good = false or g_isc_schema is null then
121
122 l_message := 'ISC schema not found';
123 raise l_exception;
124
125 end if;
126
127 -- Initialize the Standard WHO variables.
128 l_stmnt_id := 90;
129 g_user_id := nvl(fnd_global.user_id,-1);
130 g_login_id := nvl(fnd_global.login_id,-1);
131 g_program_id := nvl(fnd_global.conc_program_id,-1);
132 g_program_login_id := nvl(fnd_global.conc_login_id,-1);
133 g_program_application_id := nvl(fnd_global.prog_appl_id,-1);
134 g_request_id := nvl(fnd_global.conc_request_id,-1);
135
136 return c_ok;
137
138 exception
139
140 when l_exception then
141 x_message := err_mesg( l_message, l_func_name, l_stmnt_id );
142 bis_collection_utilities.put_line( x_message );
143 return c_error;
144
145 when others then
146 x_message := err_mesg( sqlerrm, l_func_name, l_stmnt_id );
147 bis_collection_utilities.put_line( x_message );
148 return c_error;
149
150 end check_initial_load_setup;
151
152
153 -- load_costs_staging
154 function load_costs_staging
155 ( p_run_date in date
156 , p_load_type in varchar2
157 , x_message out nocopy varchar2
158 )
159 return number
160 is
161
162 l_func_name constant varchar2(40) := 'load_costs_staging';
163 l_stmnt_id number;
164 l_rowcount number;
165
166 begin
167
168 bis_collection_utilities.log( 'Begin load staging table', 1 );
169
170 l_stmnt_id := 10;
171 if p_load_type = 'init_load' then -- initial load
172
173 insert /*+ append parallel(isc_dr_costs_stg) */
174 into isc_dr_costs_stg
175 ( repair_line_id
176 , work_order_id
177 , func_currency_code
178 , date_closed
179 , material_cost_b
180 , labor_cost_b
181 , expense_cost_b
182 )
183 select /*+ ordered use_hash( crjx, rof,wpb, hoi, gsob)
184 parallel (crjx) parallel(rof) parallel(wpb) parallel(hoi) */
185 rof.repair_line_id repair_line_id
186 , crjx.wip_entity_id work_order_id
187 , gsob.currency_code func_currency_code
188 , rof.dbi_date_closed date_closed
189 , sum( nvl(wpb.pl_material_in,0) ) material_cost_b
190 , sum( nvl(wpb.tl_resource_in,0) + nvl(wpb.pl_resource_in,0) ) labor_cost_b
191 , sum( nvl(pl_material_overhead_in,0)
192 + nvl(tl_overhead_in,0)
193 + nvl(pl_overhead_in,0)
194 + nvl(tl_outside_processing_in,0)
195 + nvl(pl_outside_processing_in,0) ) expense_cost_b
196 from
197 isc_dr_repair_orders_f rof
198 , csd_repair_job_xref crjx
199 , wip_period_balances wpb
200 , ( select wip_entity_id
201 from csd_repair_job_xref xref
202 group by xref.wip_entity_id
203 having count(1) = 1
204 ) crjx1
205 , hr_organization_information hoi
206 , gl_sets_of_books gsob
207 where
208 rof.repair_line_id = crjx.repair_line_id
209 and crjx.wip_entity_id = wpb.wip_entity_id
210 and hoi.org_information_context = 'Accounting Information'
211 and hoi.org_information1 = to_char(gsob.set_of_books_id)
212 and hoi.organization_id = wpb.organization_id
213 and rof.status = 'C'
214 and crjx1.wip_entity_id = wpb.wip_entity_id
215 and rof.ro_creation_date >= p_run_date
216 group by
217 crjx.wip_entity_id
218 , rof.repair_line_id
219 , gsob.currency_code
220 , rof.dbi_date_closed;
221
222 l_rowcount := sql%rowcount;
223 bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' insert staging table', 2 );
224
225
226 else -- incremental load
227
228 l_stmnt_id := 20;
229
230 insert /*+ append parallel(isc_dr_costs_stg) */
231 into isc_dr_costs_stg
232 ( repair_line_id
233 , work_order_id
234 , func_currency_code
235 , date_closed
236 , material_cost_b
237 , labor_cost_b
238 , expense_cost_b
239 )
240 select
241 rof.repair_line_id repair_line_id
242 , crjx.wip_entity_id work_order_id
243 , gsob.currency_code func_currency_code
244 , rof.dbi_date_closed date_closed
245 , sum( nvl(wpb.pl_material_in,0) ) material_cost_b
246 , sum( nvl(wpb.tl_resource_in,0) + nvl(wpb.pl_resource_in,0) ) labor_cost_b
247 , sum( nvl(pl_material_overhead_in,0)
248 + nvl(tl_overhead_in,0)
249 + nvl(pl_overhead_in,0)
250 + nvl(tl_outside_processing_in,0)
251 + nvl(pl_outside_processing_in,0)
252 ) expense_cost_b
253 from
254 isc_dr_repair_orders_f rof
255 , csd_repair_job_xref crjx
256 , wip_period_balances wpb
257 , ( select wip_entity_id
258 from csd_repair_job_xref xref
259 group by xref.wip_entity_id
260 having count (1) = 1
261 ) crjx1
262 , hr_organization_information hoi
263 , gl_sets_of_books gsob
264 where
265 rof.repair_line_id = crjx.repair_line_id
266 and crjx.wip_entity_id = wpb.wip_entity_id
267 and hoi.org_information_context = 'Accounting Information'
268 and hoi.org_information1 = to_char(gsob.set_of_books_id)
269 and hoi.organization_id = wpb.organization_id
270 and rof.status = 'C'
271 and crjx1.wip_entity_id = wpb.wip_entity_id
272 and rof.date_closed >= p_run_date
273 and rof.ro_creation_date >= g_global_start_date
274 group by
275 crjx.wip_entity_id
276 , rof.repair_line_id
277 , gsob.currency_code
278 , rof.dbi_date_closed;
279
280 l_rowcount := sql%rowcount;
281 bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' insert staging table', 2 );
282
283 end if;
284
285 l_stmnt_id := 30;
286 commit;
287
288 l_stmnt_id := 40;
289 fnd_stats.gather_table_stats
290 ( ownname => g_isc_schema
291 , tabname => 'ISC_DR_COSTS_STG'
292 , percent=> 10
293 );
294
295 bis_collection_utilities.log( 'Gather statistics onstaging table', 2 );
296
297 bis_collection_utilities.log( 'End load staging table', 1 );
298
299 return c_ok;
300
301 exception
302
303 when others then
304 x_message := err_mesg( sqlerrm, l_func_name, l_stmnt_id );
305 bis_collection_utilities.put_line( x_message );
306 return c_error;
307
308 end load_costs_staging;
309
310 -- load_costs_conv_rates
311
312 function load_costs_conv_rates
313 ( x_message out nocopy varchar2
314 )
315 return number
316 is
317
318 l_func_name constant varchar2(40) := 'load_costs_conv_rates';
319 l_stmnt_id number;
320 l_missing_rate_count number;
321 l_rowcount number;
322
323 cursor get_missing_rates_c is
324 select
325 func_currency_code
326 , date_closed
327 , g_conv_rate
328 , sg_conv_rate
329 from
330 isc_dr_costs_conv_tmp
331 where
332 g_conv_rate < 0
333 or ( sg_conv_rate < 0 and g_global_sec_curr_code is not null );
334
335 get_missing_rates_rec get_missing_rates_c%rowtype;
336
337 begin
338
339 bis_collection_utilities.log( 'Begin currency conversion', 1 );
340
341 l_stmnt_id := 10;
342 insert
343 into isc_dr_costs_conv_tmp
344 ( func_currency_code
345 , date_closed
346 , g_conv_rate
347 , sg_conv_rate
348 )
349 select
350 costs.func_currency_code
351 , costs.date_closed
352 , decode( costs.func_currency_code
353 , g_global_curr_code, 1
354 , fii_currency.get_rate( costs.func_currency_code
355 , g_global_curr_code
356 , costs.date_closed
357 , g_global_rate_type
358 )
359 ) g_conv_rate
360 , decode( g_global_sec_curr_code
361 , null, null
362 , costs.func_currency_code, 1
363 , fii_currency.get_rate( costs.func_currency_code
364 , g_global_sec_curr_code
365 , costs.date_closed
366 , g_global_sec_rate_type
367 )
368 ) sg_conv_rate
369 from
370 ( select distinct
371 func_currency_code
372 , date_closed
373 from isc_dr_costs_stg
374 order by func_currency_code, date_closed
375 ) costs;
376
377 l_rowcount := sql%rowcount;
378 bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into currency conversion table', 2 );
379
380 l_stmnt_id := 20;
381 l_missing_rate_count := 0;
382
383 l_stmnt_id := 30;
384 -- Handle Missing Currency Conversion Rates
385 open get_missing_rates_c;
386 l_stmnt_id := 40;
387 fetch get_missing_rates_c into get_missing_rates_rec;
388
389 while get_missing_rates_c%found loop
390 exit when get_missing_rates_c%notfound;
391
392 l_missing_rate_count := l_missing_rate_count + 1;
393
394 if l_missing_rate_count = 1 then
395 bis_collection_utilities.writemissingrateheader;
396 end if;
397
398 l_stmnt_id := 60;
399 if get_missing_rates_rec.g_conv_rate < 0 then
400 bis_collection_utilities.writemissingrate
401 ( g_global_rate_type
402 , get_missing_rates_rec.func_currency_code
403 , g_global_curr_code
404 , get_missing_rates_rec.date_closed
405 );
406 elsif get_missing_rates_rec.sg_conv_rate < 0 and
407 g_global_sec_curr_code is not null then
408 bis_collection_utilities.writemissingrate
409 ( g_global_sec_rate_type
410 , get_missing_rates_rec.func_currency_code
411 , g_global_sec_curr_code
412 , get_missing_rates_rec.date_closed
413 );
414 end if;
415
416 l_stmnt_id := 70;
417 fetch get_missing_rates_c into get_missing_rates_rec;
418
419 end loop;
420
421 l_stmnt_id := 80;
422 close get_missing_rates_c;
423
424 if l_missing_rate_count <> c_ok then
425 x_message := 'Missing Currency Conversion Rates';
426 end if;
427
428 bis_collection_utilities.log( 'There are ' || l_missing_rate_count || ' missing currency conversion rates', 2 );
429
430 bis_collection_utilities.log( 'End currency conversion', 1 );
431
432 return l_missing_rate_count;
433
434 exception
435
436 when others then
437 x_message := err_mesg( sqlerrm, l_func_name, l_stmnt_id );
438 bis_collection_utilities.put_line( x_message );
439 return c_error;
440
441 end load_costs_conv_rates;
442
443 -- charges_initial_load
444 -- Parameters:
445 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
446 -- errbuf - empty on successful completion, message on error or warning
447 --
448
449 procedure charges_initial_load
450 ( errbuf in out nocopy varchar2
451 , retcode in out nocopy number
452 )
453 is
454
455 l_proc_name constant varchar2(30) := 'charges_initial_load';
456 l_stmnt_id number;
457 l_ro_last_run_date date;
458 l_message varchar2(32000);
459 l_exception exception;
460 l_rowcount number;
461 l_to_date date;
462
463 begin
464
465 bis_collection_utilities.log( 'Begin Initial Load' );
466
467 l_stmnt_id := 0;
468 if not bis_collection_utilities.setup( g_charges_object_name ) then
469 l_message := isc_depot_backlog_etl_pkg.err_mesg
470 ( 'Error in BIS_COLLECTION_UTILITIES.Setup'
471 , l_proc_name
472 , l_stmnt_id
473 );
474 bis_collection_utilities.put_line( l_message );
475 raise l_exception;
476 end if;
477
478 l_stmnt_id := 10;
479 if check_initial_load_setup
480 ( x_message => l_message
481 ) <> c_ok then
482 raise l_exception;
483 end if;
484
485 bis_collection_utilities.log( 'Global Start Date: ' || fnd_date.date_to_displaydt(g_global_start_date), 1 );
486 bis_collection_utilities.log( 'Primary Global Currency: ' || g_global_curr_code, 1 );
487 bis_collection_utilities.log( 'Primary Global Currency Rate Type: ' || g_global_rate_type, 1 );
488 bis_collection_utilities.log( 'Secondary Global Currency: ' || g_global_sec_curr_code, 1 );
489 bis_collection_utilities.log( 'Secondary Global Currency Rate Type: ' || g_global_sec_rate_type, 1 );
490
491 l_stmnt_id := 20;
492 delete
493 from isc_dr_inc
494 where fact_name = 'ISC_DR_CHARGES_F';
495
496 bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
497
498 l_stmnt_id := 30;
499 execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_CHARGES_F';
500
501 bis_collection_utilities.log( 'Truncated table ISC_DR_CHARGES_F', 1 );
502
503 l_stmnt_id := 40;
504 if isc_depot_backlog_etl_pkg.get_last_run_date
505 ( p_fact_name => 'ISC_DR_REPAIR_ORDERS_F'
506 , x_run_date => l_ro_last_run_date
507 , x_message => l_message
508 ) <> c_ok then
509 raise l_exception;
510 end if;
511
512 if l_ro_last_run_date is null then
513 l_message := 'Please launch the Intial Load Request Set for the Depot Repair Management page.';
514 raise l_exception;
515 end if;
516
517 bis_collection_utilities.log( 'Repair Order To Date: ' || fnd_date.date_to_displaydt(l_ro_last_run_date), 1 );
518 l_to_date := sysdate;
519
520 l_stmnt_id := 50;
521 insert /*+ append parallel(isc_dr_charges_f) */
522 into isc_dr_charges_f
523 ( repair_line_id
524 , material_charges_g
525 , labor_charges_g
526 , expense_charges_g
527 , material_charges_sg
528 , labor_charges_sg
529 , expense_charges_sg
530 , created_by
531 , creation_date
532 , last_update_date
533 , last_updated_by
534 , last_update_login
535 , program_id
536 , program_login_id
537 , program_application_id
538 , request_id
539 )
540 select /*+ use_hash(cra, rof, cral, ced, ibsf, ctbt)
541 parallel(cra) parallel(rof) parallel(cral) parallel(ced) parallel(ibsf) parallel(ctbt) */
542 rof.repair_line_id repair_line_id
543 , sum( case
544 when cbtc.billing_category = 'M' then
545 ibsf.fulfilled_amt_g
546 else 0
547 end
548 ) material_charges_g
549 , sum( case
550 when cbtc.billing_category = 'L' then
551 ibsf.fulfilled_amt_g
552 else 0
553 end
554 ) labor_charges_g
555 , sum( case
556 when cbtc.billing_category = 'E' then
557 ibsf.fulfilled_amt_g
558 else 0
559 end
560 ) expense_charges_g
561 , sum( case
562 when cbtc.billing_category = 'M' then
563 ibsf.fulfilled_amt_g1
564 else 0
565 end
566 ) material_charges_sg
567 , sum( case
568 when cbtc.billing_category = 'L' then
569 ibsf.fulfilled_amt_g1
570 else 0
571 end
572 ) labor_charges_sg
573 , sum( case
574 when cbtc.billing_category = 'E' then
575 ibsf.fulfilled_amt_g1
576 else 0
577 end
578 ) expense_charges_sg
579 , g_user_id created_by
580 , sysdate creation_date
581 , sysdate last_update_date
582 , g_user_id last_updated_by
583 , g_login_id last_update_login
584 , g_program_id program_id
585 , g_program_login_id program_login_id
586 , g_program_application_id program_application_id
587 , g_request_id request_id
588 from
589 csd_repair_actuals cra
590 , isc_dr_repair_orders_f rof
591 , csd_repair_actual_lines cral
592 , cs_estimate_details ced
593 , isc_book_sum2_f ibsf
594 , cs_txn_billing_types ctbt
595 , cs_billing_type_categories cbtc
596 where
597 rof.repair_line_id = cra.repair_line_id
598 and cra.repair_actual_id = cral.repair_actual_id
599 and ced.estimate_detail_id = cral.estimate_detail_id
600 and ced.order_line_id = ibsf.line_id
601 and ced.txn_billing_type_id = ctbt.txn_billing_type_id
602 and ctbt.billing_type = cbtc.billing_type
603 and rof.repair_mode = 'WIP'
604 and rof.status = 'C'
605 and rof.ro_creation_date >= g_global_start_date
606 group by rof.repair_line_id;
607
608 l_rowcount := sql%rowcount;
609 bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_CHARGES_F' , 1 );
610
611 l_stmnt_id := 60;
612 commit;
613
614 l_stmnt_id := 70;
615 insert into isc_dr_inc
616 ( fact_name
617 , last_run_date
618 , created_by
619 , creation_date
620 , last_update_date
621 , last_updated_by
622 , last_update_login
623 , program_id
624 , program_login_id
625 , program_application_id
626 , request_id
627 )
628 values
629 ( 'ISC_DR_CHARGES_F'
630 , l_ro_last_run_date
631 , g_user_id
632 , sysdate
633 , sysdate
634 , g_user_id
635 , g_login_id
636 , g_program_id
637 , g_program_login_id
638 , g_program_application_id
639 , g_request_id
640 );
641
642 bis_collection_utilities.log( 'Inserted into table ISC_DR_INC', 1 );
643
644 l_stmnt_id := 80;
645 commit;
646
647 l_stmnt_id := 90;
648 bis_collection_utilities.wrapup( p_status => true
649 , p_period_from => g_global_start_date
650 , p_period_to => l_to_date
651 , p_count => l_rowcount
652 );
653
654 retcode := c_ok;
655
656 bis_collection_utilities.log( 'End Initial Load' );
657
658 exception
659
660 when l_exception then
661 rollback;
662 bis_collection_utilities.wrapup( p_status => false
663 , p_period_from => g_global_start_date
664 , p_period_to => l_to_date
665 , p_message => l_message
666 );
667 retcode := c_error;
668 errbuf := l_message;
669
670 when others then
671 rollback;
672 l_message := err_mesg( sqlerrm, l_proc_name, l_stmnt_id );
673 bis_collection_utilities.put_line( l_message );
674 bis_collection_utilities.wrapup( p_status => false
675 , p_period_from => g_global_start_date
676 , p_period_to => l_to_date
677 , p_message => l_message
678 );
679 retcode := c_error;
680 errbuf := l_message;
681
682 end charges_initial_load;
683
684
685 -- costs_initial_load
686 -- Parameters:
687 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
688 -- errbuf - empty on successful completion, message on error or warning
689 --
690
691 procedure costs_initial_load
692 ( errbuf in out nocopy varchar2
693 , retcode in out nocopy number
694 )
695 is
696
697 l_proc_name constant varchar2(30) := 'costs_initial_load';
698 l_stmnt_id number;
699 l_ro_last_run_date date;
700 l_message varchar2(32000);
701 l_exception exception;
702 l_rowcount number;
703 l_to_date date;
704
705 begin
706
707 bis_collection_utilities.log( 'Begin Initial Load' );
708
709 l_stmnt_id := 0;
710 if not bis_collection_utilities.setup( g_costs_object_name ) then
711 l_message := isc_depot_backlog_etl_pkg.err_mesg
712 ( 'Error in BIS_COLLECTION_UTILITIES.Setup'
713 , l_proc_name
714 , l_stmnt_id
715 );
716 bis_collection_utilities.put_line( l_message );
717 raise l_exception;
718 end if;
719
720 l_stmnt_id := 10;
721 if check_initial_load_setup
722 ( x_message => l_message
723 ) <> c_ok then
724 raise l_exception;
725 end if;
726
727 bis_collection_utilities.log( 'Global Start Date: ' || fnd_date.date_to_displaydt(g_global_start_date), 1 );
728 bis_collection_utilities.log( 'Primary Global Currency: ' || g_global_curr_code, 1 );
729 bis_collection_utilities.log( 'Primary Global Currency Rate Type: ' || g_global_rate_type, 1 );
730 bis_collection_utilities.log( 'Secondary Global Currency: ' || g_global_sec_curr_code, 1 );
731 bis_collection_utilities.log( 'Secondary Global Currency Rate Type: ' || g_global_sec_rate_type, 1 );
732
733 l_stmnt_id := 20;
734 delete
735 from isc_dr_inc
736 where fact_name = 'ISC_DR_COSTS_F';
737
738 bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
739
740 l_stmnt_id := 30;
741 execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_COSTS_STG';
742
743 bis_collection_utilities.log( 'Truncated table ISC_DR_COSTS_STG', 1 );
744
745 l_stmnt_id := 40;
746 execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_COSTS_CONV_TMP';
747
748 bis_collection_utilities.log( 'Truncated table ISC_DR_COSTS_CONV_TMP', 1 );
749
750 l_stmnt_id := 50;
751 execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_COSTS_F';
752
753 bis_collection_utilities.log( 'Truncated table ISC_DR_COSTS_F', 1 );
754
755 l_stmnt_id := 60;
756 if isc_depot_backlog_etl_pkg.get_last_run_date
757 ( p_fact_name => 'ISC_DR_REPAIR_ORDERS_F'
758 , x_run_date => l_ro_last_run_date
759 , x_message => l_message
760 ) <> c_ok then
761 raise l_exception;
762 end if;
763
764 if l_ro_last_run_date is null then
765 l_message := 'Please launch the Intial Load Request Set for the Depot Repair Management page.';
766 raise l_exception;
767 end if;
768
769 bis_collection_utilities.log( 'Repair Order To Date: ' || fnd_date.date_to_displaydt(l_ro_last_run_date), 1 );
770 l_to_date := sysdate;
771
772 l_stmnt_id := 70;
773 if load_costs_staging
774 ( p_run_date => g_global_start_date
775 , p_load_type => 'INIT_LOAD'
776 , x_message => l_message
777 ) <> c_ok then
778 raise l_exception;
779 end if;
780
781 l_stmnt_id := 80;
782 if load_costs_conv_rates
783 ( x_message => l_message
784 ) <> 0 then
785 raise l_exception;
786 end if;
787
788 l_stmnt_id := 90;
789 commit;
790
791 l_stmnt_id := 100;
792 insert /*+ append parallel(isc_dr_costs_f) */
793 into isc_dr_costs_f
794 ( repair_line_id
795 , material_cost_g
796 , labor_cost_g
797 , expense_cost_g
798 , material_cost_sg
799 , labor_cost_sg
800 , expense_cost_sg
801 , created_by
802 , creation_date
803 , last_update_date
804 , last_updated_by
805 , last_update_login
806 , program_id
807 , program_login_id
808 , program_application_id
809 , request_id
810 )
811 select
812 stg.repair_line_id repair_line_id
813 , nvl(sum( stg.material_cost_b * rates.g_conv_rate ), 0) material_cost_g
814 , nvl(sum( stg.labor_cost_b * rates.g_conv_rate ), 0) labor_cost_g
815 , nvl(sum( stg.expense_cost_b * rates.g_conv_rate ), 0) expense_cost_g
816 , nvl(sum( stg.material_cost_b * rates.sg_conv_rate ), 0) material_cost_sg
817 , nvl(sum( stg.labor_cost_b * rates.sg_conv_rate ), 0) labor_cost_sg
818 , nvl(sum( stg.expense_cost_b * rates.sg_conv_rate ), 0) expense_cost_sg
819 , g_user_id created_by
820 , sysdate creation_date
821 , sysdate last_update_date
822 , g_user_id last_updated_by
823 , g_login_id last_update_login
824 , g_program_id program_id
825 , g_program_login_id program_login_id
826 , g_program_application_id program_application_id
827 , g_request_id request_id
828 from
829 isc_dr_costs_stg stg
830 , isc_dr_costs_conv_tmp rates
831 where
832 stg.func_currency_code = rates.func_currency_code
833 and stg.date_closed = rates.date_closed
834 group by stg.repair_line_id;
835
836 l_rowcount := sql%rowcount;
837 bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_COSTS_F' , 1 );
838
839 l_stmnt_id := 110;
840 commit;
841
842 l_stmnt_id := 120;
843 insert into
844 isc_dr_inc
845 ( fact_name
846 , last_run_date
847 , created_by
848 , creation_date
849 , last_update_date
850 , last_updated_by
851 , last_update_login
852 , program_id
853 , program_login_id
854 , program_application_id
855 , request_id
856 )
857 values
858 ( 'ISC_DR_COSTS_F'
859 , l_ro_last_run_date
860 , g_user_id
861 , sysdate
862 , sysdate
863 , g_user_id
864 , g_login_id
865 , g_program_id
866 , g_program_login_id
867 , g_program_application_id
868 , g_request_id
869 );
870
871 bis_collection_utilities.log( 'Inserted into table ISC_DR_INC', 1 );
872
873 l_stmnt_id := 130;
874 commit;
875
876 l_stmnt_id := 140;
877 bis_collection_utilities.wrapup( p_status => true
878 , p_period_from => g_global_start_date
879 , p_period_to => l_to_date
880 , p_count => l_rowcount
881 );
882
883 retcode := c_ok;
884
885 bis_collection_utilities.log( 'End Inital Load' );
886
887 exception
888
889 when l_exception then
890 rollback;
891 bis_collection_utilities.wrapup( p_status => false
892 , p_period_from => g_global_start_date
893 , p_period_to => l_to_date
894 , p_message => l_message
895 );
896 retcode := c_error;
897 errbuf := l_message;
898
899 when others then
900 rollback;
901 l_message := err_mesg( sqlerrm, l_proc_name, l_stmnt_id );
902 bis_collection_utilities.put_line( l_message );
903 bis_collection_utilities.wrapup( p_status => false
904 , p_period_from => g_global_start_date
905 , p_period_to => l_to_date
906 , p_message => l_message
907 );
908 retcode := c_error;
909 errbuf := l_message;
910
911 end costs_initial_load;
912
913 -- charges_incr_load
914 -- Parameters:
915 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
916 -- errbuf - empty on successful completion, message on error or warning
917
918 procedure charges_incr_load
919 ( errbuf in out nocopy varchar2
920 , retcode in out nocopy number)
921 is
922
923 l_proc_name constant varchar2(30) := 'charges_incr_load';
924 l_stmnt_id number;
925 l_ro_last_run_date date;
926 l_charges_last_run_date date;
927 l_message varchar2(32000);
928 l_exception exception;
929 l_rowcount number;
930 l_to_date date;
931
932 begin
933
934 bis_collection_utilities.log( 'Begin Incremental Load' );
935
936 l_stmnt_id := 0;
937 if not bis_collection_utilities.setup( g_charges_object_name ) then
938 l_message := isc_depot_backlog_etl_pkg.err_mesg
939 ( 'Error in BIS_COLLECTION_UTILITIES.Setup'
940 , l_proc_name
941 , l_stmnt_id
942 );
943 bis_collection_utilities.put_line( l_message );
944 raise l_exception;
945 end if;
946
947 l_stmnt_id := 10;
948 if check_initial_load_setup
949 ( x_message => l_message
950 ) <> c_ok then
951 raise l_exception;
952 end if;
953
954 bis_collection_utilities.log( 'Global Start Date: ' || fnd_date.date_to_displaydt(g_global_start_date), 1 );
955 bis_collection_utilities.log( 'Primary Global Currency: ' || g_global_curr_code, 1 );
956 bis_collection_utilities.log( 'Primary Global Currency Rate Type: ' || g_global_rate_type, 1 );
957 bis_collection_utilities.log( 'Secondary Global Currency: ' || g_global_sec_curr_code, 1 );
958 bis_collection_utilities.log( 'Secondary Global Currency Rate Type: ' || g_global_sec_rate_type, 1 );
959
960 l_stmnt_id := 20;
961 if isc_depot_backlog_etl_pkg.get_last_run_date
962 ( p_fact_name => 'ISC_DR_REPAIR_ORDERS_F'
963 , x_run_date => l_ro_last_run_date
964 , x_message => l_message
965 ) <> c_ok then
966 raise l_exception;
967 end if;
968
969 if l_ro_last_run_date is null then
970 l_message := 'Please run the Intial Load Request Set for the Depot Repair Management page.';
971 raise l_exception;
972 end if;
973
974 bis_collection_utilities.log( 'Repair Order From Date: ' || fnd_date.date_to_displaydt(l_ro_last_run_date), 1 );
975
976 l_stmnt_id := 30;
977 if isc_depot_backlog_etl_pkg.get_last_run_date
978 ( p_fact_name => 'ISC_DR_CHARGES_F'
979 , x_run_date => l_charges_last_run_date
980 , x_message => l_message
981 ) <> c_ok then
982 raise l_exception;
983 end if;
984
985 if l_charges_last_run_date is null then
986 l_message := 'Please run the Intial Load Request Set for the Depot Repair Management page.';
987 raise l_exception;
988 end if;
989
990 bis_collection_utilities.log( 'Charges From Date: ' || fnd_date.date_to_displaydt(l_charges_last_run_date), 1 );
991
992 l_to_date := sysdate;
993
994 l_stmnt_id := 40;
995 merge into isc_dr_charges_f fact
996 using
997 ( select rof.repair_line_id repair_line_id
998 , sum( case
999 when cbtc.billing_category = 'M' then
1000 ibsf.fulfilled_amt_g
1001 else 0
1002 end
1003 ) material_charges_g
1004 , sum( case
1005 when cbtc.billing_category = 'L' then
1006 ibsf.fulfilled_amt_g
1007 else 0
1008 end
1009 ) labor_charges_g
1010 , sum( case
1011 when cbtc.billing_category = 'E' then
1012 ibsf.fulfilled_amt_g
1013 else 0
1014 end
1015 ) expense_charges_g
1016 , sum( case
1017 when cbtc.billing_category = 'M' then
1018 ibsf.fulfilled_amt_g1
1019 else 0
1020 end
1021 ) material_charges_sg
1022 , sum( case
1023 when cbtc.billing_category = 'L' then
1024 ibsf.fulfilled_amt_g1
1025 else 0
1026 end
1027 ) labor_charges_sg
1028 , sum( case
1029 when cbtc.billing_category = 'E' then
1030 ibsf.fulfilled_amt_g1
1031 else 0
1032 end
1033 ) expense_charges_sg
1034 , sysdate last_update_date
1035 , g_user_id last_updated_by
1036 , g_login_id last_update_login
1037 , g_program_id program_id
1038 , g_program_login_id program_login_id
1039 , g_program_application_id program_application_id
1040 , g_request_id request_id
1041 from
1042 isc_dr_repair_orders_f rof
1043 , cs_estimate_details ced
1044 , csd_repair_actuals cra
1045 , csd_repair_actual_lines cral
1046 , cs_txn_billing_types ctbt
1047 , cs_billing_type_categories cbtc
1048 , isc_book_sum2_f ibsf
1049 where
1050 rof.repair_line_id = cra.repair_line_id
1051 and cra.repair_actual_id = cral.repair_actual_id
1052 and ced.estimate_detail_id = cral.estimate_detail_id
1053 and ced.order_line_id = ibsf.line_id
1054 and ced.txn_billing_type_id = ctbt.txn_billing_type_id
1055 and ctbt.billing_type = cbtc.billing_type
1056 and rof.repair_mode = 'WIP'
1057 and rof.status = 'C'
1058 and rof.date_closed >= l_charges_last_run_date
1059 and rof.ro_creation_date >= g_global_start_date
1060 group by
1061 rof.repair_line_id
1062 ) charges
1063 on
1064 ( fact.repair_line_id = charges.repair_line_id )
1065 when matched then
1066 update
1067 set fact.material_charges_g = charges.material_charges_g
1068 , fact.labor_charges_g = charges.labor_charges_g
1069 , fact.expense_charges_g = charges.expense_charges_g
1070 , fact.material_charges_sg = charges.material_charges_sg
1071 , fact.labor_charges_sg = charges.labor_charges_sg
1072 , fact.expense_charges_sg = charges.expense_charges_sg
1073 , fact.last_update_date = charges.last_update_date
1074 , fact.last_updated_by = charges.last_updated_by
1075 , fact.last_update_login = charges.last_update_login
1076 , fact.program_id = charges.program_id
1077 , fact.program_login_id = charges.program_login_id
1078 , fact.program_application_id = charges.program_application_id
1079 , fact.request_id = charges.request_id
1080 when not matched then
1081 insert
1082 ( fact.repair_line_id
1083 , fact.material_charges_g
1084 , fact.labor_charges_g
1085 , fact.expense_charges_g
1086 , fact.material_charges_sg
1087 , fact.labor_charges_sg
1088 , fact.expense_charges_sg
1089 , fact.created_by
1090 , fact.creation_date
1091 , fact.last_update_date
1092 , fact.last_updated_by
1093 , fact.last_update_login
1094 , fact.program_id
1095 , fact.program_login_id
1096 , fact.program_application_id
1097 , fact.request_id
1098 )
1099 values
1100 ( charges.repair_line_id
1101 , charges.material_charges_g
1102 , charges.labor_charges_g
1103 , charges.expense_charges_g
1104 , charges.material_charges_sg
1105 , charges.labor_charges_sg
1106 , charges.expense_charges_sg
1107 , g_user_id
1108 , sysdate
1109 , charges.last_update_date
1110 , charges.last_updated_by
1111 , charges.last_update_login
1112 , charges.program_id
1113 , charges.program_login_id
1114 , charges.program_application_id
1115 , charges.request_id
1116 );
1117
1118 l_rowcount := sql%rowcount;
1119 bis_collection_utilities.log( 'Merged ' || l_rowcount || ' rows into ISC_DR_CHARGES_F' , 1 );
1120
1121 l_stmnt_id := 50;
1122 update isc_dr_inc
1123 set
1124 last_run_date = l_ro_last_run_date
1125 , last_update_date = sysdate
1126 , last_updated_by = g_user_id
1127 , last_update_login = g_login_id
1128 , program_id = g_program_id
1129 , program_login_id = g_program_login_id
1130 , program_application_id = g_program_application_id
1131 , request_id = g_request_id
1132 where fact_name = 'ISC_DR_CHARGES_F';
1133
1134 bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );
1135
1136 l_stmnt_id := 60;
1137 commit;
1138
1139 l_stmnt_id := 70;
1140 bis_collection_utilities.wrapup( p_status => true
1141 , p_period_from => l_charges_last_run_date
1142 , p_period_to => l_to_date
1143 , p_count => l_rowcount
1144 );
1145
1146 retcode := c_ok;
1147
1148 bis_collection_utilities.log( 'End Incremental Load' );
1149
1150 exception
1151
1152 when l_exception then
1153 rollback;
1154 bis_collection_utilities.wrapup( p_status => false
1155 , p_period_from => l_charges_last_run_date
1156 , p_period_to => l_to_date
1157 , p_message => l_message
1158 );
1159 retcode := c_error;
1160 errbuf := l_message;
1161
1162 when others then
1163 rollback;
1164 l_message := err_mesg( sqlerrm, l_proc_name, l_stmnt_id );
1165 bis_collection_utilities.put_line( l_message );
1166 bis_collection_utilities.wrapup( p_status => false
1167 , p_period_from => l_charges_last_run_date
1168 , p_period_to => l_to_date
1169 , p_message => l_message
1170 );
1171 retcode := c_error;
1172 errbuf := l_message;
1173
1174 end charges_incr_load;
1175
1176 -- costs_incr_load
1177 -- Parameters:
1178 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
1179 -- errbuf - empty on successful completion, message on error or warning
1180
1181 procedure costs_incr_load
1182 ( errbuf in out nocopy varchar2
1183 , retcode in out nocopy number
1184 )
1185 is
1186
1187 l_proc_name constant varchar2(30) := 'costs_incr_load';
1188 l_stmnt_id number;
1189
1190 l_ro_last_run_date date;
1191 l_costs_last_run_date date;
1192 l_message varchar2(32000);
1193 l_exception exception;
1194 l_rowcount number;
1195 l_to_date date;
1196
1197 begin
1198
1199 bis_collection_utilities.log( 'Begin Incremental Load' );
1200
1201 l_stmnt_id := 0;
1202 if not bis_collection_utilities.setup( g_costs_object_name ) then
1203 l_message := isc_depot_backlog_etl_pkg.err_mesg
1204 ( 'Error in BIS_COLLECTION_UTILITIES.Setup'
1205 , l_proc_name
1206 , l_stmnt_id
1207 );
1208 bis_collection_utilities.put_line( l_message );
1209 raise l_exception;
1210 end if;
1211
1212 l_stmnt_id := 10;
1213 if check_initial_load_setup
1214 ( x_message => l_message
1215 ) <> c_ok then
1216 raise l_exception;
1217 end if;
1218
1219 bis_collection_utilities.log( 'Global Start Date: ' || fnd_date.date_to_displaydt(g_global_start_date), 1 );
1220 bis_collection_utilities.log( 'Primary Global Currency: ' || g_global_curr_code, 1 );
1221 bis_collection_utilities.log( 'Primary Global Currency Rate Type: ' || g_global_rate_type, 1 );
1222 bis_collection_utilities.log( 'Secondary Global Currency: ' || g_global_sec_curr_code, 1 );
1223 bis_collection_utilities.log( 'Secondary Global Currency Rate Type: ' || g_global_sec_rate_type, 1 );
1224
1225 l_stmnt_id := 20;
1226 execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_COSTS_STG';
1227
1228 bis_collection_utilities.log( 'Truncated table ISC_DR_COSTS_STG', 1 );
1229
1230 l_stmnt_id := 30;
1231 execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_COSTS_CONV_TMP';
1232
1233 bis_collection_utilities.log( 'Truncated table ISC_DR_COSTS_CONV_TMP', 1 );
1234
1235 l_stmnt_id := 40;
1236 if isc_depot_backlog_etl_pkg.get_last_run_date
1237 ( p_fact_name => 'ISC_DR_REPAIR_ORDERS_F'
1238 , x_run_date => l_ro_last_run_date
1239 , x_message => l_message
1240 ) <> c_ok then
1241 raise l_exception;
1242 end if;
1243
1244 if l_ro_last_run_date is null then
1245 l_message := 'Please run the Intial Load Request Set for the Depot Repair Management page.';
1246 raise l_exception;
1247 end if;
1248
1249 bis_collection_utilities.log( 'Repair Order From Date: ' || fnd_date.date_to_displaydt(l_ro_last_run_date), 1 );
1250
1251 l_stmnt_id := 50;
1252 if isc_depot_backlog_etl_pkg.get_last_run_date
1253 ( p_fact_name => 'ISC_DR_COSTS_F'
1254 , x_run_date => l_costs_last_run_date
1255 , x_message => l_message
1256 ) <> c_ok then
1257 raise l_exception;
1258 end if;
1259
1260 if l_costs_last_run_date is null then
1261 l_message := 'Please run the Intial Load Request Set for the Depot Repair Management page.';
1262 raise l_exception;
1263 end if;
1264
1265 bis_collection_utilities.log( 'Cost From Date: ' || fnd_date.date_to_displaydt(l_costs_last_run_date), 1 );
1266 l_to_date := sysdate;
1267
1268 l_stmnt_id := 60;
1269 if load_costs_staging
1270 ( p_run_date => l_costs_last_run_date
1271 , p_load_type => 'INCR_LOAD'
1272 , x_message => l_message
1273 ) <> c_ok then
1274 raise l_exception;
1275 end if;
1276
1277 l_stmnt_id := 70;
1278 if load_costs_conv_rates
1279 ( x_message => l_message
1280 ) <> 0 then
1281 raise l_exception;
1282 end if;
1283
1284 l_stmnt_id := 80;
1285 merge into
1286 isc_dr_costs_f fact
1287 using
1288 ( select
1289 stg.repair_line_id repair_line_id
1290 , nvl(sum( stg.material_cost_b * rates.g_conv_rate ), 0) material_cost_g
1291 , nvl(sum( stg.labor_cost_b * rates.g_conv_rate ), 0) labor_cost_g
1292 , nvl(sum( stg.expense_cost_b * rates.g_conv_rate ), 0) expense_cost_g
1293 , nvl(sum( stg.material_cost_b * rates.sg_conv_rate ), 0) material_cost_sg
1294 , nvl(sum( stg.labor_cost_b * rates.sg_conv_rate ), 0) labor_cost_sg
1295 , nvl(sum( stg.expense_cost_b * rates.sg_conv_rate ), 0) expense_cost_sg
1296 , sysdate last_update_date
1297 , g_user_id last_updated_by
1298 , g_login_id last_update_login
1299 , g_program_id program_id
1300 , g_program_login_id program_login_id
1301 , g_program_application_id program_application_id
1302 , g_request_id request_id
1303 from
1304 isc_dr_costs_stg stg
1305 , isc_dr_costs_conv_tmp rates
1306 where
1307 stg.func_currency_code = rates.func_currency_code
1308 and stg.date_closed = rates.date_closed
1309 group by stg.repair_line_id
1310 ) costs
1311 on
1312 ( fact.repair_line_id = costs.repair_line_id )
1313 when matched then
1314 update
1315 set fact.material_cost_g = costs.material_cost_g
1316 , fact.labor_cost_g = costs.labor_cost_g
1317 , fact.expense_cost_g = costs.expense_cost_g
1318 , fact.material_cost_sg = costs.material_cost_sg
1319 , fact.labor_cost_sg = costs.labor_cost_sg
1320 , fact.expense_cost_sg = costs.expense_cost_sg
1321 , fact.last_update_date = costs.last_update_date
1322 , fact.last_updated_by = costs.last_updated_by
1323 , fact.last_update_login = costs.last_update_login
1324 , fact.program_id = costs.program_id
1325 , fact.program_login_id = costs.program_login_id
1326 , fact.program_application_id = costs.program_application_id
1327 , fact.request_id = costs.request_id
1328 when not matched then
1329 insert
1330 ( fact.repair_line_id
1331 , fact.material_cost_g
1332 , fact.labor_cost_g
1333 , fact.expense_cost_g
1334 , fact.material_cost_sg
1335 , fact.labor_cost_sg
1336 , fact.expense_cost_sg
1337 , fact.created_by
1338 , fact.creation_date
1339 , fact.last_update_date
1340 , fact.last_updated_by
1341 , fact.last_update_login
1342 , fact.program_id
1343 , fact.program_login_id
1344 , fact.program_application_id
1345 , fact.request_id
1346 )
1347 values
1348 ( costs.repair_line_id
1349 , costs.material_cost_g
1350 , costs.labor_cost_g
1351 , costs.expense_cost_g
1352 , costs.material_cost_sg
1353 , costs.labor_cost_sg
1354 , costs.expense_cost_sg
1355 , g_user_id
1356 , sysdate
1357 , costs.last_update_date
1358 , costs.last_updated_by
1359 , costs.last_update_login
1360 , costs.program_id
1361 , costs.program_login_id
1362 , costs.program_application_id
1363 , costs.request_id
1364 );
1365
1366 l_rowcount := sql%rowcount;
1367 bis_collection_utilities.log( 'Merged ' || l_rowcount || ' rows into ISC_DR_COSTS_F' , 1 );
1368
1369 l_stmnt_id := 90;
1370 update isc_dr_inc
1371 set last_run_date = l_ro_last_run_date
1372 , last_update_date = sysdate
1373 , last_updated_by = g_user_id
1374 , last_update_login = g_login_id
1375 , program_id = g_program_id
1376 , program_login_id = g_program_login_id
1377 , program_application_id = g_program_application_id
1378 , request_id = g_request_id
1379 where fact_name = 'ISC_DR_COSTS_F';
1380
1381 bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );
1382
1383 l_stmnt_id := 100;
1384 commit;
1385
1386 l_stmnt_id := 110;
1387 bis_collection_utilities.wrapup( p_status => true
1388 , p_period_from => l_costs_last_run_date
1389 , p_period_to => l_to_date
1390 , p_count => l_rowcount
1391 );
1392
1393 retcode := c_ok;
1394
1395 bis_collection_utilities.log( 'End Incremental Load' );
1396
1397 exception
1398
1399 when l_exception then
1400 rollback;
1401 bis_collection_utilities.wrapup( p_status => false
1402 , p_period_from => l_costs_last_run_date
1403 , p_period_to => l_to_date
1404 , p_message => l_message
1405 );
1406 retcode := c_error;
1407 errbuf := l_message;
1408
1409 when others then
1410 rollback;
1411 l_message := err_mesg( sqlerrm, l_proc_name, l_stmnt_id );
1412 bis_collection_utilities.put_line( l_message );
1413 bis_collection_utilities.wrapup( p_status => false
1414 , p_period_from => l_costs_last_run_date
1415 , p_period_to => l_to_date
1416 , p_message => l_message
1417 );
1418 retcode := c_error;
1419 errbuf := l_message;
1420
1421 end costs_incr_load;
1422
1423 end isc_depot_margin_etl_pkg;