[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_BSA_OBJECTS_C
Source
1 package body isc_dbi_bsa_objects_c as
2 /* $Header: ISCSCFAB.pls 120.12 2006/09/15 12:03:39 achandak noship $ */
3
4 g_batch_size number;
5 g_global_currency varchar2(30);
6 g_global_rate_type varchar2(80);
7 g_sec_global_currency varchar2(30);
8 g_sec_global_rate_type varchar2(80);
9 g_global_start_date date;
10 g_treasury_rate_type varchar2(80);
11
12 g_errbuf varchar2(2000);
13 g_retcode varchar2(200);
14 g_row_count number;
15 g_push_from_date date;
16 g_push_to_date date;
17 g_incre_start_date date;
18 g_load_mode varchar2(30);
19 g_isc_schema varchar2(50);
20 g_sec_curr_def varchar2(1);
21
22 function check_setup return number is
23
24 l_list dbms_sql.varchar2_table;
25 l_status varchar2(30);
26 l_industry varchar2(30);
27 l_setup number;
28
29 begin
30
31 l_list(1) := 'BIS_GLOBAL_START_DATE';
32 if (not bis_common_parameters.check_global_parameters(l_list)) then
33 bis_collection_utilities.put_line(' ');
34 bis_collection_utilities.put_line('Error! Collection aborted because the global start date has not been set up.');
35 bis_collection_utilities.put_line(' ');
36 l_setup := -999;
37 end if;
38
39 g_sec_curr_def := isc_dbi_currency_pkg.is_sec_curr_defined;
40 if (g_sec_curr_def = 'E') then
41 bis_collection_utilities.put_line(' ');
42 bis_collection_utilities.put_line('Error! Collection aborted because the set-up of the DBI Global Parameter "Secondary Global Currency" is incomplete. Please verify the proper set-up of the Global Currency Rate Type and the Global Currency Code.');
43 bis_collection_utilities.put_line(' ');
44 l_setup := -999;
45 end if;
46
47 g_batch_size := bis_common_parameters.get_batch_size(bis_common_parameters.high);
48 bis_collection_utilities.put_line('The batch size is ' || g_batch_size);
49
50 g_global_start_date := bis_common_parameters.get_global_start_date;
51 bis_collection_utilities.put_line('The global start date is ' || g_global_start_date);
52
53 g_global_currency := bis_common_parameters.get_currency_code;
54 bis_collection_utilities.put_line('The global currency code is ' || g_global_currency);
55
56 g_global_rate_type := bis_common_parameters.get_rate_type;
57 bis_collection_utilities.put_line('The primary rate type is ' || g_global_rate_type);
58
59 g_sec_global_currency := bis_common_parameters.get_secondary_currency_code;
60 bis_collection_utilities.put_line('The secondary global currency code is ' || g_sec_global_currency);
61
62 g_sec_global_rate_type := bis_common_parameters.get_secondary_rate_type;
63 bis_collection_utilities.put_line('The secondary rate type is ' || g_sec_global_rate_type);
64
65 g_treasury_rate_type := bis_common_parameters.get_treasury_rate_type;
66 bis_collection_utilities.put_line('The treasury rate type is ' || g_treasury_rate_type);
67
68 if (not fnd_installation.get_app_info('ISC', l_status, l_industry, g_isc_schema)) then
69 bis_collection_utilities.put_line(' ');
70 bis_collection_utilities.put_line('Error! Collection aborted while retrieving schema information.');
71 bis_collection_utilities.put_line(' ');
72 l_setup := -999;
73 end if;
74
75 if (l_setup = -999) then
76 g_errbuf := 'Collection aborted because the setup has not been completed. Please refer to the log file for the details.';
77 return(-1);
78 end if;
79
80 bis_collection_utilities.put_line('Truncating the temp tables');
81 fii_util.start_timer;
82
83 execute immediate 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_BSA_ORDER_LINES';
84 execute immediate 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_CURR_BSA_ORDER_LINES';
85
86 fii_util.stop_timer;
87 fii_util.print_timer('Truncated the temp tables in');
88 bis_collection_utilities.put_line(' ');
89
90 return(1);
91
92 exception
93 when others then
94 g_errbuf := 'Error in function CHECK_SETUP : '||sqlerrm;
95 return(-1);
96
97 end check_setup;
98
99 function identify_change_init return number is
100
101 l_bsa_count number;
102
103 begin
104
105 l_bsa_count := 0;
106
107 bis_collection_utilities.put_line('Identifying blanket sales agreements');
108 fii_util.start_timer;
109
110 insert /*+ APPEND PARALLEL(F) */ into isc_dbi_tmp_bsa_order_lines f (
111 order_line_id,
112 order_line_header_id,
113 order_number,
114 line_number,
115 inventory_item_id,
116 item_inv_org_id,
117 blanket_line_id,
118 blanket_header_id,
119 blanket_number,
120 blanket_line_number,
121 org_id,
122 salesrep_id,
123 agreement_type_id,
124 sold_to_org_id,
125 time_activation_date_id,
126 time_expiration_date_id,
127 time_termination_date_id,
128 time_fulfilled_date_id,
129 time_effective_end_date_id,
130 h_start_date_active,
131 l_start_date_active,
132 h_end_date_active,
133 l_end_date_active,
134 termination_date,
135 blanket_min_amt,
136 blanket_line_min_amt,
137 fulfilled_amt_g,
138 fulfilled_amt_g1,
139 accumulated_fulfilled_amt_g,
140 accumulated_fulfilled_amt_g1,
141 h_cnt,
142 l_cnt,
143 transactional_curr_code,
144 transaction_phase_code,
145 created_by,
146 creation_date,
147 last_updated_by,
148 last_update_date,
149 last_update_login,
150 program_id,
151 program_login_id,
152 program_application_id,
153 request_id
154 )
155 select /*+ USE_HASH(bh,bhe,bl,ble,r,book) PARALLEL(bh) PARALLEL(bhe) PARALLEL(bl) PARALLEL(ble) PARALLEL(r) PARALLEL(book) FULL(bh) FULL(bhe)*/
156 book.line_id,
157 book.header_id,
158 book.order_number,
159 book.line_number,
160 book.inventory_item_id,
161 book.item_inv_org_id,
162 bl.line_id,
163 bh.header_id,
164 bh.order_number,
165 ble.line_number,
166 bh.org_id,
167 bh.salesrep_id,
168 bh.order_type_id,
169 bh.sold_to_org_id,
170 trunc(decode(bhe.blanket_min_amount, null, ble.start_date_active, bhe.start_date_active)),
171 trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active)),
172 trunc(r.creation_date),
173 book.time_fulfilled_date_id,
174 least(nvl(trunc(r.creation_date), trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active))),
175 nvl(trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active)), trunc(r.creation_date))),
176 bhe.start_date_active,
177 ble.start_date_active,
178 bhe.end_date_active,
179 ble.end_date_active,
180 r.creation_date,
181 bhe.blanket_min_amount,
182 ble.blanket_line_min_amount,
183 book.fulfilled_amt_g,
184 book.fulfilled_amt_g1,
185 sum(book.fulfilled_amt_g) over (partition by bh.header_id order by book.time_fulfilled_date_id, book.line_id range unbounded preceding),
186 sum(book.fulfilled_amt_g1) over (partition by bh.header_id order by book.time_fulfilled_date_id, book.line_id range unbounded preceding),
187 count(1) over (partition by bh.header_id),
188 count(1) over (partition by bl.line_id),
189 bh.transactional_curr_code,
190 bh.transaction_phase_code,
191 bh.created_by,
192 bh.creation_date,
193 bh.last_updated_by,
194 bh.last_update_date,
195 bh.last_update_login,
196 null,
197 null,
198 null,
199 null
200 from oe_blanket_headers_all bh,
201 oe_blanket_headers_ext bhe,
202 oe_blanket_lines_all bl,
203 oe_blanket_lines_ext ble,
204 oe_reasons r,
205 isc_book_sum2_f book
206 where bh.order_number = bhe.order_number
207 and bh.header_id = bl.header_id
208 and bl.line_id = ble.line_id
209 and r.entity_code(+) = 'BLANKET_HEADER'
210 and r.reason_type(+) = 'CONTRACT_TERMINATION'
211 and r.entity_id(+) = bh.header_id
212 and book.blanket_number(+) = ble.order_number
213 and book.blanket_line_number(+) = ble.line_number
214 and bh.transaction_phase_code = 'F'
215 and bh.sold_to_org_id is not null
216 and (bhe.blanket_min_amount is not null or ble.blanket_line_min_amount is not null)
217 and nvl(r.creation_date,bhe.start_date_active+1) >= bhe.start_date_active
218 and book.line_category_code(+) <> 'RETURN'
219 and book.order_source_id(+) <> 10
220 and book.order_source_id(+) <> 27
221 and book.ordered_quantity(+) <> 0
222 and book.unit_selling_price(+) <> 0
223 and book.charge_periodicity_code(+) is null;
224
225 l_bsa_count := sql%rowcount;
226 fii_util.stop_timer;
227 fii_util.print_timer('Identified ' || l_bsa_count || ' blanket sales agreement in');
228 commit;
229
230 insert /*+ APPEND */ into isc_curr_bsa_order_lines f (
231 from_currency,
232 conversion_date,
233 rate1,
234 rate2
235 )
236 select transactional_curr_code from_currency,
237 time_activation_date_id conversion_date,
238 decode(transactional_curr_code, g_global_currency, 1,
239 fii_currency.get_global_rate_primary(transactional_curr_code, time_activation_date_id)) rate1,
240 decode(transactional_curr_code, g_sec_global_currency, 1,
241 fii_currency.get_global_rate_secondary(transactional_curr_code, time_activation_date_id)) rate2
242 from (select /*+ PARALLEL(tmp) */ distinct transactional_curr_code, time_activation_date_id
243 from isc_dbi_tmp_bsa_order_lines tmp);
244
245 return(l_bsa_count);
246
247 exception
248 when others then
249 g_errbuf := 'Error in Function IDENTIFY_CHANGE_INIT : '||sqlerrm;
250 g_retcode := sqlcode;
251 return(-1);
252
253 end identify_change_init;
254
255 function report_missing_rate return number is
256
257 cursor missing_currency_conversion is
258 select distinct decode(rate1, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) curr_conv_date,
259 from_currency,
260 g_global_currency to_currency,
261 g_global_rate_type rate_type,
262 decode(rate1, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') status
263 FROM isc_curr_bsa_order_lines tmp
264 WHERE rate1 < 0
265 UNION
266 SELECT distinct decode(rate2, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
267 from_currency,
268 g_sec_global_currency to_currency,
269 g_sec_global_rate_type rate_type,
270 decode(rate2, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') status
271 FROM isc_curr_bsa_order_lines tmp
272 WHERE rate2 < 0
273 AND g_sec_curr_def = 'Y';
274
275
276 l_record missing_currency_conversion%rowtype;
277 l_total number;
278
279 begin
280
281 l_total := 0;
282
283 open missing_currency_conversion;
284 fetch missing_currency_conversion into l_record;
285
286 if missing_currency_conversion%rowcount <> 0 then
287 bis_collection_utilities.put_line('Collection failed because there are missing currency conversion rates.');
288 bis_collection_utilities.put_line(fnd_message.get_string('BIS', 'BIS_DBI_CURR_NO_LOAD'));
289
290 bis_collection_utilities.writeMissingRateHeader;
291 while missing_currency_conversion%found loop
292 l_total := l_total + 1;
293 bis_collection_utilities.writeMissingRate(
294 l_record.rate_type,
295 l_record.from_currency,
296 l_record.to_currency,
297 l_record.curr_conv_date);
298 fetch missing_currency_conversion into l_record;
299 end loop;
300 bis_collection_utilities.put_line_out(' ');
301 bis_collection_utilities.put_line_out(' ');
302
303 else -- missing_currency_conversion%rowcount = 0
304 bis_collection_utilities.put_line(' ');
305 bis_collection_utilities.put_line(' THERE IS NO MISSING CURRENCY CONVERSION RATE ');
306 bis_collection_utilities.put_line('+---------------------------------------------------------------------------+');
307 bis_collection_utilities.put_line(' ');
308 end if;
309
310 close missing_currency_conversion;
311
312 return(l_total);
313
314 exception
315 when others then
316 g_errbuf := 'Error in Function REPORT_MISSING_RATE : '||sqlerrm;
317 g_retcode := sqlcode;
318 return(-1);
319
320 end report_missing_rate;
321
322 function check_time_continuity_init return number is
323
324 l_min_act_date date;
325 l_max_act_date date;
326 l_min_exp_date date;
327 l_max_exp_date date;
328 l_min_trm_date date;
329 l_max_trm_date date;
330 l_min date;
331 l_max date;
332 l_is_missing boolean;
333 l_time_min date;
334 l_time_max date;
335 l_time_missing boolean;
336
337 cursor lines_missing_date is
338 select order_number,
339 line_number,
340 order_line_id,
341 blanket_number,
342 to_char(time_activation_date_id, 'MM/DD/YYYY') time_activation_date_id,
343 to_char(time_expiration_date_id, 'MM/DD/YYYY') time_expiration_date_id,
344 to_char(time_termination_date_id,'MM/DD/YYYY') time_termination_date_id
345 from isc_dbi_tmp_bsa_order_lines
346 where (least(time_activation_date_id,
347 nvl(time_expiration_date_id,time_activation_date_id),
348 nvl(time_termination_date_id,time_activation_date_id)) < l_time_min
349 or greatest(time_activation_date_id,
350 nvl(time_expiration_date_id,time_activation_date_id),
351 nvl(time_termination_date_id,time_activation_date_id)) > l_time_max);
352
353 l_line lines_missing_date%rowtype;
354
355 begin
356
357 fii_util.start_timer;
358
359 bis_collection_utilities.put_line('Begin to retrieve the time boundary for the initial load');
360 select /*+ PARALLEL(tmp) */
361 min(time_activation_date_id), max(time_activation_date_id),
362 min(time_expiration_date_id), max(time_expiration_date_id),
363 min(time_termination_date_id), max(time_termination_date_id)
364 into l_min_act_date, l_max_act_date,
365 l_min_exp_date, l_max_exp_date,
366 l_min_trm_date, l_max_trm_date
367 from isc_dbi_tmp_bsa_order_lines tmp;
368
369 l_min := least(l_min_act_date,
370 nvl(l_min_exp_date,l_min_act_date),
371 nvl(l_min_trm_date,l_min_act_date));
372 l_max := greatest(l_max_act_date,
373 nvl(l_max_exp_date,l_max_act_date),
374 nvl(l_max_trm_date, l_max_act_date));
375
376 fii_util.stop_timer;
377 fii_util.print_timer('Retrieved the time boundary in ');
378
379 fii_util.start_timer;
380
381 bis_collection_utilities.put_line_out(' ');
382 bis_collection_utilities.put_line_out(' ');
383 fii_time_api.check_missing_date(l_min, l_max, l_is_missing);
384
385 if (l_is_missing) then
386 bis_collection_utilities.put_line('Collection failed because there are dangling keys for time dimension.');
387 bis_collection_utilities.put_line('No records were loaded.');
388
389 select min(report_date), max(report_date)
390 into l_time_min, l_time_max
391 from fii_time_day;
392
393 open lines_missing_date;
394 fetch lines_missing_date into l_line;
395 bis_collection_utilities.put_line_out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
396 bis_collection_utilities.put_line_out(' ');
397 bis_collection_utilities.put_line_out(rpad(fnd_message.get_string('ISC','ISC_DBI_ORDER_NUMBER'),18,' ')
398 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_LINE_NUMBER'),18,' ')
399 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_LINE_ID'),18,' ')
400 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_BLANKET_NUMBER'),18,' ')
401 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_ACTIVATION_DATE'),15,' ')
402 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_EXPIRATION_DATE'),19,' ')
403 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_TERMINATION_DATE'),16,' '));
404 bis_collection_utilities.put_line_out('------------------ - ------------------ - ------------------ - --------------- - ------------------- - ---------------- - ------------------------');
405
406 while lines_missing_date%found loop
407 bis_collection_utilities.put_line_out(rpad(l_line.order_number,18,' ')
408 ||' - '||rpad(l_line.line_number,18,' ')
409 ||' - '||rpad(l_line.order_line_id,18,' ')
410 ||' - '||rpad(l_line.blanket_number,18,' ')
411 ||' - '||rpad(l_line.time_activation_date_id,15,' ')
412 ||' - '||rpad(nvl(l_line.time_expiration_date_id,' '),19,' ')
413 ||' - '||rpad(nvl(l_line.time_termination_date_id,' '),16,' '));
414 fetch lines_missing_date into l_line;
415 end loop;
416
417 close lines_missing_date;
418 bis_collection_utilities.put_line_out('+------------------------------------------------------------------------------------------------------------------------------------------------+');
419 return (-999);
420 else
421 bis_collection_utilities.put_line(' ');
422 bis_collection_utilities.put_line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
423 bis_collection_utilities.put_line('+---------------------------------------------------------------------------+');
424 bis_collection_utilities.put_line(' ');
425 end if;
426
427 fii_util.stop_timer;
428 fii_util.print_timer('Completed time continuity check in');
429
430 return(1);
431
432 exception
433 when others then
434 g_errbuf := 'Error in Function CHECK_TIME_CONTINUITY_INIT : '||sqlerrm;
435 g_retcode := sqlcode;
436 return(-1);
437
438 end check_time_continuity_init;
439
440 function dangling_check_init return number is
441
442 l_time_danling number;
443 l_item_count number;
444 l_miss_conv number;
445 l_dangling number;
446
447 begin
448
449 l_time_danling := 0;
450 l_item_count := 0;
451 l_miss_conv := 0;
452 l_dangling := 0;
453
454 bis_collection_utilities.put_line(' ');
455 bis_collection_utilities.put_line('Identifying the missing currency conversion rates');
456 fii_util.start_timer;
457
458 l_miss_conv := REPORT_MISSING_RATE;
459
460 fii_util.stop_timer;
461 fii_util.print_timer('Completed missing currency check in');
462
463 if (l_miss_conv = -1) then
464 return(-1);
465 elsif (l_miss_conv > 0) then
466 g_errbuf := g_errbuf || 'Collection aborted due to missing currency conversion rates. ';
467 l_dangling := -999;
468 end if;
469
470 -- bis_collection_utilities.put_line(' ');
471 -- bis_collection_utilities.put_line('Checking Time Continuity');
472 --
473 -- l_time_danling := CHECK_TIME_CONTINUITY_INIT;
474 --
475 -- if (l_time_danling = -1) then
476 -- return(-1);
477 -- elsif (l_time_danling = -999) then
478 -- g_errbuf := g_errbuf || 'Collection aborted due to dangling keys for time dimension. ';
479 -- l_dangling := -999;
480 -- end if;
481
482 if (l_dangling = -999) then
483 return(-1);
484 end if;
485
486 return(1);
487
488 exception
489 when others then
490 g_errbuf := 'Error in Function DANGLING_CHECK_INIT : '||sqlerrm;
491 g_retcode := sqlcode;
492 return(-1);
493
494 end dangling_check_init;
495
496 function insert_fact return number is
497
498 l_bsa_count number;
499
500 begin
501
502 bis_collection_utilities.put_line(' ');
503 bis_collection_utilities.put_line('Inserting data into isc_dbi_bsa_order_lines_f');
504 fii_util.start_timer;
505
506 insert /*+ APPEND PARALLEL(F) */ into isc_dbi_bsa_order_lines_f f (
507 order_line_id,
508 order_line_header_id,
509 order_number,
510 line_number,
511 inventory_item_id,
512 item_inv_org_id,
513 blanket_line_id,
514 blanket_header_id,
515 blanket_number,
516 blanket_line_number,
517 org_id,
518 salesrep_id,
519 sales_grp_id,
520 agreement_type_id,
521 sold_to_org_id,
522 customer_id,
523 time_activation_date_id,
524 time_expiration_date_id,
525 time_termination_date_id,
526 time_fulfilled_date_id,
527 time_effective_end_date_id,
528 h_start_date_active,
529 l_start_date_active,
530 h_end_date_active,
531 l_end_date_active,
532 termination_date,
533 blanket_min_amt,
534 blanket_line_min_amt,
535 fulfilled_amt_g,
536 fulfilled_amt_g1,
537 h_cnt,
538 l_cnt,
539 commit_prorated_amt_g,
540 commit_prorated_amt_g1,
541 fulfilled_outstand_amt_g,
542 fulfilled_outstand_amt_g1,
543 transaction_phase_code,
544 created_by,
545 creation_date,
546 last_updated_by,
547 last_update_date,
548 last_update_login,
549 program_id,
550 program_login_id,
551 program_application_id,
552 request_id
553 )
554 select /*+ use_hash(curr,tmp,sg,cust_acct) PARALLEL(curr) PARALLEL(tmp) PARALLEL(sg) PARALLEL(cust_acct) */
555 tmp.order_line_id,
556 tmp.order_line_header_id,
557 tmp.order_number,
558 tmp.line_number,
559 tmp.inventory_item_id,
560 tmp.item_inv_org_id,
561 tmp.blanket_line_id,
562 tmp.blanket_header_id,
563 tmp.blanket_number,
564 tmp.blanket_line_number,
565 tmp.org_id,
566 sg.resource_id,
567 sg.group_id,
568 tmp.agreement_type_id,
569 tmp.sold_to_org_id,
570 cust_acct.party_id,
571 tmp.time_activation_date_id,
572 tmp.time_expiration_date_id,
573 tmp.time_termination_date_id,
574 tmp.time_fulfilled_date_id,
575 tmp.time_effective_end_date_id,
576 tmp.h_start_date_active,
577 tmp.l_start_date_active,
578 tmp.h_end_date_active,
579 tmp.l_end_date_active,
580 tmp.termination_date,
581 tmp.blanket_min_amt,
582 tmp.blanket_line_min_amt,
583 tmp.fulfilled_amt_g,
584 tmp.fulfilled_amt_g1,
585 tmp.h_cnt,
586 tmp.l_cnt,
587 nvl(tmp.blanket_min_amt*curr.rate1/tmp.h_cnt, tmp.blanket_line_min_amt*curr.rate1/tmp.l_cnt),
588 nvl(tmp.blanket_min_amt*curr.rate2/tmp.h_cnt, tmp.blanket_line_min_amt*curr.rate2/tmp.l_cnt),
589 decode((tmp.accumulated_fulfilled_amt_g - nvl(tmp.blanket_min_amt*curr.rate1,tmp.blanket_line_min_amt*curr.rate1) - tmp.fulfilled_amt_g),
590 abs(tmp.accumulated_fulfilled_amt_g - nvl(tmp.blanket_min_amt*curr.rate1,tmp.blanket_line_min_amt*curr.rate1) - tmp.fulfilled_amt_g),
591 0,
592 decode((nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g),
593 abs(nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g),
594 tmp.fulfilled_amt_g,
595 (nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g + tmp.fulfilled_amt_g))),
596 decode((tmp.accumulated_fulfilled_amt_g1 - nvl(tmp.blanket_min_amt*curr.rate2,tmp.blanket_line_min_amt*curr.rate2) - tmp.fulfilled_amt_g1),
597 abs(tmp.accumulated_fulfilled_amt_g1 - nvl(tmp.blanket_min_amt*curr.rate2,tmp.blanket_line_min_amt*curr.rate2) - tmp.fulfilled_amt_g1),
598 0,
599 decode((nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1),
600 abs(nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1),
601 tmp.fulfilled_amt_g1,
602 (nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1 + tmp.fulfilled_amt_g1))),
603 tmp.transaction_phase_code,
604 tmp.created_by,
605 tmp.creation_date,
606 tmp.last_updated_by,
607 tmp.last_update_date,
608 tmp.last_update_login,
609 tmp.program_id,
610 tmp.program_login_id,
611 tmp.program_application_id,
612 tmp.request_id
613 from isc_dbi_tmp_bsa_order_lines tmp,
614 isc_curr_bsa_order_lines curr,
615 jtf_rs_srp_groups sg,
616 hz_cust_accounts cust_acct
617 where tmp.transactional_curr_code = curr.from_currency
618 and tmp.time_activation_date_id = curr.conversion_date
619 and tmp.salesrep_id = sg.salesrep_id
620 and tmp.org_id = sg.org_id
621 and tmp.h_start_date_active between sg.start_date and sg.end_date
622 and tmp.sold_to_org_id = cust_acct.cust_account_id;
623
624 l_bsa_count := sql%rowcount;
625 fii_util.stop_timer;
626 fii_util.print_timer('Inserted '|| l_bsa_count ||' rows into isc_dbi_bsa_order_lines_f in');
627
628 commit;
629
630 return(l_bsa_count);
631
632 exception
633 when others then
634 g_errbuf := 'Error in Function INSERT_FACT : '||sqlerrm;
635 g_retcode := sqlcode;
636 return(-1);
637
638 end insert_fact;
639
640 function wrapup return number is
641
642 begin
643
644 bis_collection_utilities.put_line('Truncating the temp tables');
645 fii_util.start_timer;
646
647 execute immediate 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_TMP_BSA_ORDER_LINES';
648 execute immediate 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_CURR_BSA_ORDER_LINES';
649
650 fii_util.stop_timer;
651 fii_util.print_timer('Truncated the temp tables in');
652 bis_collection_utilities.put_line(' ');
653
654 bis_collection_utilities.wrapup(
655 true,
656 g_row_count,
657 null,
658 isc_dbi_bsa_objects_c.g_push_from_date,
659 isc_dbi_bsa_objects_c.g_push_to_date
660 );
661
662 return (1);
663
664 exception
665 when others then
666 g_errbuf := 'Error in Function WRAPUP : '||sqlerrm;
667 g_retcode := sqlcode;
668 return(-1);
669 end wrapup;
670
671 procedure load_fact(errbuf in out nocopy varchar2,
672 retcode in out nocopy varchar2) is
673
674 l_failure exception;
675 l_start date;
676 l_end date;
677 l_period_from date;
678 l_period_to date;
679 l_row_count number;
680
681 begin
682
683 errbuf := null;
684 retcode := '0';
685 g_load_mode := 'INITIAL';
686
687 bis_collection_utilities.put_line(' ');
688 bis_collection_utilities.put_line('Begin the ' || g_load_mode || ' load');
689
690 if (not bis_collection_utilities.setup('ISC_DBI_BSA_ORDER_LINES_INIT')) then
691 raise_application_error (-20000,'Error in SETUP: ' || errbuf);
692 return;
693 end if;
694
695 if (CHECK_SETUP = -1)
696 then raise l_failure;
697 end if;
698
699 isc_dbi_bsa_objects_c.g_push_from_date := g_global_start_date;
700 isc_dbi_bsa_objects_c.g_push_to_date := sysdate;
701
702 bis_collection_utilities.put_line( 'The collection date range is from '||
703 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
704 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
705 bis_collection_utilities.put_line(' ');
706
707 execute immediate 'alter session set hash_area_size=104857600';
708 execute immediate 'alter session set sort_area_size=104857600';
709
710 l_row_count := IDENTIFY_CHANGE_INIT;
711
712 if (l_row_count = -1) then
713 raise l_failure;
714
715 elsif (l_row_count = 0) then
716 bis_collection_utilities.put_line(' ');
717 bis_collection_utilities.put_line('Truncating the fact tables');
718 fii_util.start_timer;
719
720 execute immediate 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_BSA_ORDER_LINES_F';
721
722 fii_util.stop_timer;
723 fii_util.print_timer('Truncated the fact tables in');
724 g_row_count := 0;
725
726 else
727 bis_collection_utilities.put_line(' ');
728 bis_collection_utilities.put_line('Analyzing temp tables');
729 fii_util.start_timer;
730
731 fnd_stats.gather_table_stats(ownname => g_isc_schema,
732 tabname => 'ISC_DBI_TMP_BSA_ORDER_LINES');
733 fnd_stats.gather_table_stats(ownname => g_isc_schema,
734 tabname => 'ISC_CURR_BSA_ORDER_LINES');
735
736 fii_util.stop_timer;
737 fii_util.print_timer('Analyzed the temp tables in ');
738
739 if (DANGLING_CHECK_INIT = -1) then
740 raise l_failure;
741 end if;
742
743 bis_collection_utilities.put_line(' ');
744 bis_collection_utilities.put_line('Truncating the fact tables');
745 fii_util.start_timer;
746
747 execute immediate 'TRUNCATE TABLE ' || g_isc_schema ||'.ISC_DBI_BSA_ORDER_LINES_F';
748
749 fii_util.stop_timer;
750 fii_util.print_timer('Truncated the fact tables in');
751
752 g_row_count := INSERT_FACT;
753
754 if (g_row_count = -1) then
755 raise l_failure;
756 end if;
757
758 end if;
759
760 if (WRAPUP = -1) then
761 raise l_failure;
762 end if;
763
764 retcode := g_retcode;
765 errbuf := g_errbuf;
766
767 exception
768
769 when l_failure then
770 rollback;
771 bis_collection_utilities.put_line(' ');
772 bis_collection_utilities.put_line(g_errbuf);
773 retcode := -1;
774 errbuf := g_errbuf;
775
776 bis_collection_utilities.wrapup(
777 false,
778 g_row_count,
779 g_errbuf,
780 isc_dbi_bsa_objects_c.g_push_from_date,
781 isc_dbi_bsa_objects_c.g_push_to_date
782 );
783
784 when others then
785 rollback;
786 g_errbuf := sqlerrm ||' - '||sqlcode;
787 bis_collection_utilities.put_line(' ');
788 bis_collection_utilities.put_line('Other errors : '|| g_errbuf);
789 retcode := -1;
790 errbuf := g_errbuf;
791
792 bis_collection_utilities.wrapup(
793 false,
794 g_row_count,
795 g_errbuf,
796 isc_dbi_bsa_objects_c.g_push_from_date,
797 isc_dbi_bsa_objects_c.g_push_to_date
798 );
799
800 end load_fact;
801
802 function identify_change_icrl return number is
803
804 l_total number;
805
806 begin
807
808 l_total := 0;
809
810 -- insert into log table
811
812 -- analyze log table
813
814 -- delete obsoleted records from base summary
815
816 fii_util.start_timer;
817
818 insert /*+ APPEND PARALLEL(F) */ into isc_dbi_tmp_bsa_order_lines f (
819 order_line_id,
820 order_line_header_id,
821 order_number,
822 line_number,
823 inventory_item_id,
824 item_inv_org_id,
825 blanket_line_id,
826 blanket_header_id,
827 blanket_number,
828 blanket_line_number,
829 org_id,
830 salesrep_id,
831 agreement_type_id,
832 sold_to_org_id,
833 time_activation_date_id,
834 time_expiration_date_id,
835 time_termination_date_id,
836 time_fulfilled_date_id,
837 time_effective_end_date_id,
838 h_start_date_active,
839 l_start_date_active,
840 h_end_date_active,
841 l_end_date_active,
842 termination_date,
843 blanket_min_amt,
844 blanket_line_min_amt,
845 fulfilled_amt_g,
846 fulfilled_amt_g1,
847 accumulated_fulfilled_amt_g,
848 accumulated_fulfilled_amt_g1,
849 h_cnt,
850 l_cnt,
851 transactional_curr_code,
852 transaction_phase_code,
853 created_by,
854 creation_date,
855 last_updated_by,
856 last_update_date,
857 last_update_login,
858 program_id,
859 program_login_id,
860 program_application_id,
861 request_id
862 )
863 select /*+ USE_HASH(bh,bhe,bl,ble,r,book) PARALLEL(bh) PARALLEL(bhe) PARALLEL(bl) PARALLEL(ble) PARALLEL(r) PARALLEL(book) */
864 book.line_id,
865 book.header_id,
866 book.order_number,
867 book.line_number,
868 book.inventory_item_id,
869 book.item_inv_org_id,
870 bl.line_id,
871 bh.header_id,
872 bh.order_number,
873 ble.line_number,
874 bh.org_id,
875 bh.salesrep_id,
876 bh.order_type_id,
877 bh.sold_to_org_id,
878 trunc(decode(bhe.blanket_min_amount, null, ble.start_date_active, bhe.start_date_active)),
879 trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active)),
880 trunc(r.creation_date),
881 book.time_fulfilled_date_id,
882 least(nvl(trunc(r.creation_date), trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active))),
883 nvl(trunc(decode(bhe.blanket_min_amount, null, ble.end_date_active, bhe.end_date_active)), trunc(r.creation_date))),
884 bhe.start_date_active,
885 ble.start_date_active,
886 bhe.end_date_active,
887 ble.end_date_active,
888 r.creation_date,
889 bhe.blanket_min_amount,
890 ble.blanket_line_min_amount,
891 book.fulfilled_amt_g,
892 book.fulfilled_amt_g1,
893 sum(book.fulfilled_amt_g) over (partition by bh.header_id order by book.time_fulfilled_date_id, book.line_id range unbounded preceding),
894 sum(book.fulfilled_amt_g1) over (partition by bh.header_id order by book.time_fulfilled_date_id, book.line_id range unbounded preceding),
895 count(1) over (partition by bh.header_id),
896 count(1) over (partition by bl.line_id),
897 bh.transactional_curr_code,
898 bh.transaction_phase_code,
899 bh.created_by,
900 bh.creation_date,
901 bh.last_updated_by,
902 bh.last_update_date,
903 bh.last_update_login,
904 null,
905 null,
906 null,
907 null
908 from oe_blanket_headers_all bh,
909 oe_blanket_headers_ext bhe,
910 oe_blanket_lines_all bl,
911 oe_blanket_lines_ext ble,
912 oe_reasons r,
913 isc_book_sum2_f book
914 where bh.order_number = bhe.order_number
915 and bh.header_id = bl.header_id
916 and bl.line_id = ble.line_id
917 and r.entity_code(+) = 'BLANKET_HEADER'
918 and r.reason_type(+) = 'CONTRACT_TERMINATION'
919 and r.entity_id(+) = bh.header_id
920 and book.blanket_number(+) = ble.order_number
921 and book.blanket_line_number(+) = ble.line_number
922 and bh.transaction_phase_code = 'F'
923 and bh.sold_to_org_id is not null
924 and (bhe.blanket_min_amount is not null or ble.blanket_line_min_amount is not null)
925 and nvl(r.creation_date,bhe.start_date_active+1) >= bhe.start_date_active
926 and book.line_category_code(+) <> 'RETURN'
927 and book.order_source_id(+) <> 10
928 and book.order_source_id(+) <> 27
929 and book.ordered_quantity(+) <> 0
930 and book.unit_selling_price(+) <> 0
931 and book.charge_periodicity_code(+) is null;
932
933 fii_util.stop_timer;
934 fii_util.print_timer('Identified '|| sql%rowcount || ' blanket sales agreement in');
935 commit;
936
937 fii_util.start_timer;
938
939 insert /*+ APPEND */ into isc_curr_bsa_order_lines f (
940 from_currency,
941 conversion_date,
942 rate1,
943 rate2
944 )
945 select transactional_curr_code from_currency,
946 time_activation_date_id conversion_date,
947 decode(transactional_curr_code, g_global_currency, 1,
948 fii_currency.get_global_rate_primary(transactional_curr_code, time_activation_date_id)) rate1,
949 decode(transactional_curr_code, g_sec_global_currency, 1,
950 fii_currency.get_global_rate_secondary(transactional_curr_code, time_activation_date_id)) rate2
951 from (select /*+ PARALLEL(tmp) */ distinct transactional_curr_code, time_activation_date_id
952 from isc_dbi_tmp_bsa_order_lines tmp);
953
954 fii_util.stop_timer;
955 fii_util.print_timer('Retrieved '||sql%rowcount||' currency rates in');
956 commit;
957
958 fii_util.start_timer;
959
960 update isc_dbi_tmp_bsa_order_lines set batch_id = ceil(rownum/g_batch_size);
961 l_total := sql%rowcount;
962 commit;
963
964 fii_util.stop_timer;
965 fii_util.print_timer('Updated the batch id for '|| l_total || ' rows in');
966
967 return(l_total);
968
969 exception
970 when others then
971 g_errbuf := 'Error in Function IDENTIFY_CHANGE_ICRL : '||sqlerrm;
972 g_retcode := sqlcode;
973 return(-1);
974
975 end identify_change_icrl;
976
977 function check_time_continuity_icrl return number is
978
979 l_min_act_date date;
980 l_max_act_date date;
981 l_min_exp_date date;
982 l_max_exp_date date;
983 l_min_trm_date date;
984 l_max_trm_date date;
985 l_min date;
986 l_max date;
987 l_is_missing boolean;
988 l_time_min date;
989 l_time_max date;
990 l_time_missing boolean;
991
992 cursor lines_missing_date is
993 select order_number,
994 line_number,
995 order_line_id,
996 blanket_number,
997 to_char(time_activation_date_id, 'MM/DD/YYYY') time_activation_date_id,
998 to_char(time_expiration_date_id, 'MM/DD/YYYY') time_expiration_date_id,
999 to_char(time_termination_date_id,'MM/DD/YYYY') time_termination_date_id
1000 from isc_dbi_tmp_bsa_order_lines
1001 where (least(time_activation_date_id,
1002 nvl(time_expiration_date_id,time_activation_date_id),
1003 nvl(time_termination_date_id,time_activation_date_id)) < l_time_min
1004 or greatest(time_activation_date_id,
1005 nvl(time_expiration_date_id,time_activation_date_id),
1006 nvl(time_termination_date_id,time_activation_date_id)) > l_time_max);
1007
1008 l_line lines_missing_date%rowtype;
1009
1010 begin
1011
1012 fii_util.start_timer;
1013
1014 bis_collection_utilities.put_line('Begin to retrieve the time boundary for the initial load');
1015 select /*+ PARALLEL(tmp) */
1016 min(time_activation_date_id), max(time_activation_date_id),
1017 min(time_expiration_date_id), max(time_expiration_date_id),
1018 min(time_termination_date_id), max(time_termination_date_id)
1019 into l_min_act_date, l_max_act_date,
1020 l_min_exp_date, l_max_exp_date,
1021 l_min_trm_date, l_max_trm_date
1022 from isc_dbi_tmp_bsa_order_lines tmp;
1023
1024 l_min := least(l_min_act_date,
1025 nvl(l_min_exp_date,l_min_act_date),
1026 nvl(l_min_trm_date,l_min_act_date));
1027 l_max := greatest(l_max_act_date,
1028 nvl(l_max_exp_date,l_max_act_date),
1029 nvl(l_max_trm_date, l_max_act_date));
1030
1031 fii_util.stop_timer;
1032 fii_util.print_timer('Retrieved the time boundary in ');
1033
1034 fii_util.start_timer;
1035
1036 bis_collection_utilities.put_line_out(' ');
1037 bis_collection_utilities.put_line_out(' ');
1038 fii_time_api.check_missing_date(l_min, l_max, l_is_missing);
1039
1040 if (l_is_missing) then
1041 bis_collection_utilities.put_line('Collection failed because there are dangling keys for time dimension.');
1042 bis_collection_utilities.put_line('No records were loaded.');
1043
1044 select min(report_date), max(report_date)
1045 into l_time_min, l_time_max
1046 from fii_time_day;
1047
1048 open lines_missing_date;
1049 fetch lines_missing_date into l_line;
1050 bis_collection_utilities.put_line_out(fnd_message.get_string('ISC', 'ISC_DBI_DATE_NO_LOAD'));
1051 bis_collection_utilities.put_line_out(' ');
1052 bis_collection_utilities.put_line_out(rpad(fnd_message.get_string('ISC','ISC_DBI_ORDER_NUMBER'),18,' ')
1053 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_LINE_NUMBER'),18,' ')
1054 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_LINE_ID'),18,' ')
1055 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_BLANKET_NUMBER'),18,' ')
1056 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_ACTIVATION_DATE'),15,' ')
1057 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_EXPIRATION_DATE'),19,' ')
1058 ||' - '||rpad(fnd_message.get_string('ISC','ISC_DBI_TERMINATION_DATE'),16,' '));
1059 bis_collection_utilities.put_line_out('------------------ - ------------------ - ------------------ - --------------- - ------------------- - ---------------- - ------------------------');
1060
1061 while lines_missing_date%found loop
1062 bis_collection_utilities.put_line_out(rpad(l_line.order_number,18,' ')
1063 ||' - '||rpad(l_line.line_number,18,' ')
1064 ||' - '||rpad(l_line.order_line_id,18,' ')
1065 ||' - '||rpad(l_line.blanket_number,18,' ')
1066 ||' - '||rpad(l_line.time_activation_date_id,15,' ')
1067 ||' - '||rpad(nvl(l_line.time_expiration_date_id,' '),19,' ')
1068 ||' - '||rpad(nvl(l_line.time_termination_date_id,' '),16,' '));
1069 fetch lines_missing_date into l_line;
1070 end loop;
1071
1072 close lines_missing_date;
1073 bis_collection_utilities.put_line_out('+------------------------------------------------------------------------------------------------------------------------------------------------+');
1074 return (-999);
1075 else
1076 bis_collection_utilities.put_line(' ');
1077 bis_collection_utilities.put_line(' THERE IS NO DANGLING TIME ATTRIBUTES ');
1078 bis_collection_utilities.put_line('+---------------------------------------------------------------------------+');
1079 bis_collection_utilities.put_line(' ');
1080 end if;
1081
1082 fii_util.stop_timer;
1083 fii_util.print_timer('Completed time continuity check in');
1084
1085 return(1);
1086
1087 exception
1088 when others then
1089 g_errbuf := 'Error in Function CHECK_TIME_CONTINUITY_ICRL : '||sqlerrm;
1090 g_retcode := sqlcode;
1091 return(-1);
1092
1093 END check_time_continuity_icrl;
1094
1095 function dangling_check_icrl return number is
1096
1097 l_time_danling number;
1098 l_miss_conv number;
1099 l_dangling number;
1100
1101 begin
1102
1103 l_time_danling := 0;
1104 l_miss_conv := 0;
1105 l_dangling := 0;
1106
1107 bis_collection_utilities.put_line(' ');
1108 bis_collection_utilities.put_line('Identifying the missing currency conversion rates');
1109 fii_util.start_timer;
1110
1111 l_miss_conv := REPORT_MISSING_RATE;
1112
1113 fii_util.stop_timer;
1114 fii_util.print_timer('Completed missing currency check in');
1115
1116 if (l_miss_conv = -1) then
1117 return(-1);
1118 elsif (l_miss_conv > 0) then
1119 g_errbuf := g_errbuf || 'Collection aborted due to missing currency conversion rates. ';
1120 l_dangling := -999;
1121 end if;
1122
1123 -- bis_collection_utilities.put_line(' ');
1124 -- bis_collection_utilities.put_line('Checking Time Continuity');
1125 --
1126 -- l_time_danling := CHECK_TIME_CONTINUITY_ICRL;
1127 --
1128 -- if (l_time_danling = -1) then
1129 -- return(-1);
1130 -- elsif (l_time_danling = -999) then
1131 -- g_errbuf := g_errbuf || 'Collection aborted due to dangling keys for time dimension. ';
1132 -- l_dangling := -999;
1133 -- end if;
1134
1135 if (l_dangling = -999) then
1136 return(-1);
1137 end if;
1138
1139 return(1);
1140
1141 exception
1142 when others then
1143 g_errbuf := 'Error in Function DANGLING_CHECK_ICRL : '||sqlerrm;
1144 g_retcode := sqlcode;
1145 return(-1);
1146
1147 end dangling_check_icrl;
1148
1149 function merge_fact(p_batch number) return number is
1150
1151 cursor unfulfilled_bsa is
1152 select blanket_line_id,
1153 order_line_id
1154 from (select f.blanket_line_id,
1155 t.order_line_id,
1156 rank() over (partition by f.blanket_line_id order by t.order_line_id) rnk
1157 from isc_dbi_bsa_order_lines_f f,
1158 isc_dbi_tmp_bsa_order_lines t
1159 where f.blanket_line_id = t.blanket_line_id
1160 and f.order_line_id is null
1161 and t.order_line_id is not null)
1162 where rnk = 1;
1163
1164 l_record unfulfilled_bsa%rowtype;
1165 l_count number;
1166 l_total number;
1167 l_max_batch number;
1168 l_date date;
1169
1170 begin
1171
1172 open unfulfilled_bsa;
1173 fetch unfulfilled_bsa into l_record;
1174
1175 if unfulfilled_bsa%rowcount <> 0 then
1176 while unfulfilled_bsa%found loop
1177 update isc_dbi_bsa_order_lines_f
1178 set order_line_id = l_record.order_line_id
1179 where blanket_line_id = l_record.blanket_line_id;
1180 fetch unfulfilled_bsa into l_record;
1181 end loop;
1182 end if;
1183 commit;
1184 close unfulfilled_bsa;
1185
1186 l_total := 0;
1187 l_date := to_date('01/01/0001','DD/MM/YYYY');
1188
1189 for v_batch_id in 1..p_batch loop
1190 fii_util.start_timer;
1191 bis_collection_utilities.put_line('Merging batch '||v_batch_id);
1192
1193 l_count := 0;
1194
1195 merge into isc_dbi_bsa_order_lines_f f using
1196 (select new.*
1197 from (select tmp.batch_id,
1198 tmp.order_line_id,
1199 tmp.order_line_header_id,
1200 tmp.order_number,
1201 tmp.line_number,
1202 tmp.inventory_item_id,
1203 tmp.item_inv_org_id,
1204 tmp.blanket_line_id,
1205 tmp.blanket_header_id,
1206 tmp.blanket_number,
1207 tmp.blanket_line_number,
1208 tmp.org_id,
1209 sg.resource_id salesrep_id,
1210 sg.group_id sales_grp_id,
1211 tmp.agreement_type_id,
1212 tmp.sold_to_org_id,
1213 cust_acct.party_id customer_id,
1214 tmp.time_activation_date_id,
1215 tmp.time_expiration_date_id,
1216 tmp.time_termination_date_id,
1217 tmp.time_fulfilled_date_id,
1218 tmp.time_effective_end_date_id,
1219 tmp.h_start_date_active,
1220 tmp.l_start_date_active,
1221 tmp.h_end_date_active,
1222 tmp.l_end_date_active,
1223 tmp.termination_date,
1224 tmp.blanket_min_amt,
1225 tmp.blanket_line_min_amt,
1226 tmp.fulfilled_amt_g,
1227 tmp.fulfilled_amt_g1,
1228 tmp.h_cnt,
1229 tmp.l_cnt,
1230 nvl(tmp.blanket_min_amt*curr.rate1/tmp.h_cnt, tmp.blanket_line_min_amt*curr.rate1/tmp.l_cnt) commit_prorated_amt_g,
1231 nvl(tmp.blanket_min_amt*curr.rate2/tmp.h_cnt, tmp.blanket_line_min_amt*curr.rate2/tmp.l_cnt) commit_prorated_amt_g1,
1232 decode((tmp.accumulated_fulfilled_amt_g - nvl(tmp.blanket_min_amt*curr.rate1,tmp.blanket_line_min_amt*curr.rate1) - tmp.fulfilled_amt_g),
1233 abs(tmp.accumulated_fulfilled_amt_g - nvl(tmp.blanket_min_amt*curr.rate1,tmp.blanket_line_min_amt*curr.rate1) - tmp.fulfilled_amt_g),
1234 0,
1235 decode((nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g),
1236 abs(nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g),
1237 tmp.fulfilled_amt_g,
1238 (nvl(tmp.blanket_min_amt*curr.rate1, tmp.blanket_line_min_amt*curr.rate1) - tmp.accumulated_fulfilled_amt_g + tmp.fulfilled_amt_g)))
1239 fulfilled_outstand_amt_g,
1240 decode((tmp.accumulated_fulfilled_amt_g1 - nvl(tmp.blanket_min_amt*curr.rate2,tmp.blanket_line_min_amt*curr.rate2) - tmp.fulfilled_amt_g1),
1241 abs(tmp.accumulated_fulfilled_amt_g1 - nvl(tmp.blanket_min_amt*curr.rate2,tmp.blanket_line_min_amt*curr.rate2) - tmp.fulfilled_amt_g1),
1242 0,
1243 decode((nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1),
1244 abs(nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1),
1245 tmp.fulfilled_amt_g1,
1246 (nvl(tmp.blanket_min_amt*curr.rate2, tmp.blanket_line_min_amt*curr.rate2) - tmp.accumulated_fulfilled_amt_g1 + tmp.fulfilled_amt_g1)))
1247 fulfilled_outstand_amt_g1,
1248 tmp.transaction_phase_code,
1249 tmp.created_by,
1250 tmp.creation_date,
1251 tmp.last_updated_by,
1252 tmp.last_update_date,
1253 tmp.last_update_login,
1254 tmp.program_id,
1255 tmp.program_login_id,
1256 tmp.program_application_id,
1257 tmp.request_id
1258 from isc_dbi_tmp_bsa_order_lines tmp,
1259 isc_curr_bsa_order_lines curr,
1260 jtf_rs_srp_groups sg,
1261 hz_cust_accounts cust_acct
1262 where tmp.transactional_curr_code = curr.from_currency
1263 and tmp.time_activation_date_id = curr.conversion_date
1264 and tmp.salesrep_id = sg.salesrep_id
1265 and tmp.org_id = sg.org_id
1266 and tmp.h_start_date_active between sg.start_date and sg.end_date
1267 and tmp.sold_to_org_id = cust_acct.cust_account_id) new, isc_dbi_bsa_order_lines_f old
1268 where new.batch_id = v_batch_id
1269 and new.blanket_line_id = old.blanket_line_id(+)
1270 and nvl(new.order_line_id,-1) = nvl(old.order_line_id(+),-1)
1271 and (old.blanket_line_id is null
1272 or nvl(new.order_line_header_id,-1) <> nvl(old.order_line_header_id,-1)
1273 or nvl(new.order_number,-1) <> nvl(old.order_number,-1)
1274 or nvl(new.line_number,'na') <> nvl(old.line_number,'na')
1275 or nvl(new.inventory_item_id,-1) <> nvl(old.inventory_item_id,-1)
1276 or nvl(new.item_inv_org_id,-1) <> nvl(old.item_inv_org_id,-1)
1277 or nvl(new.org_id,-1) <> nvl(old.org_id,-1)
1278 or nvl(new.salesrep_id,-1) <> nvl(old.salesrep_id,-1)
1279 or nvl(new.sales_grp_id,-1) <> nvl(old.sales_grp_id,-1)
1280 or nvl(new.agreement_type_id,-1) <> nvl(old.agreement_type_id,-1)
1281 or nvl(new.sold_to_org_id,-1) <> nvl(old.sold_to_org_id,-1)
1282 or nvl(new.customer_id,-1) <> nvl(old.customer_id,-1)
1283 or nvl(new.time_activation_date_id,l_date) <> nvl(old.time_activation_date_id,l_date)
1284 or nvl(new.time_expiration_date_id,l_date) <> nvl(old.time_expiration_date_id,l_date)
1285 or nvl(new.time_termination_date_id,l_date) <> nvl(old.time_termination_date_id,l_date)
1286 or nvl(new.time_fulfilled_date_id,l_date) <> nvl(old.time_fulfilled_date_id,l_date)
1287 or nvl(new.time_effective_end_date_id,l_date) <> nvl(old.time_effective_end_date_id,l_date)
1288 or nvl(new.h_start_date_active,l_date) <> nvl(old.h_start_date_active,l_date)
1289 or nvl(new.l_start_date_active,l_date) <> nvl(old.l_start_date_active,l_date)
1290 or nvl(new.h_end_date_active,l_date) <> nvl(old.h_end_date_active,l_date)
1291 or nvl(new.l_end_date_active,l_date) <> nvl(old.l_end_date_active,l_date)
1292 or nvl(new.termination_date,l_date) <> nvl(old.termination_date,l_date)
1293 or nvl(new.blanket_min_amt,0) <> nvl(old.blanket_min_amt,0)
1294 or nvl(new.blanket_line_min_amt,0) <> nvl(old.blanket_line_min_amt,0)
1295 or nvl(new.fulfilled_amt_g,0) <> nvl(old.fulfilled_amt_g,0)
1296 or nvl(new.fulfilled_amt_g1,0) <> nvl(old.fulfilled_amt_g1,0)
1297 or nvl(new.h_cnt,0) <> nvl(old.h_cnt,0)
1298 or nvl(new.l_cnt,0) <> nvl(old.l_cnt,0)
1299 or nvl(new.commit_prorated_amt_g,0) <> nvl(old.commit_prorated_amt_g,0)
1300 or nvl(new.commit_prorated_amt_g1,0) <> nvl(old.commit_prorated_amt_g1,0)
1301 or nvl(new.fulfilled_outstand_amt_g,0) <> nvl(old.fulfilled_outstand_amt_g,0)
1302 or nvl(new.fulfilled_outstand_amt_g1,0) <> nvl(old.fulfilled_outstand_amt_g1,0)
1303 or nvl(new.transaction_phase_code,'na') <> nvl(old.transaction_phase_code,'na'))) v
1304 ON ( nvl(f.order_line_id,-1) = nvl(v.order_line_id,-1)
1305 and f.blanket_line_id = v.blanket_line_id)
1306 WHEN MATCHED THEN UPDATE SET
1307 f.order_line_header_id = v.order_line_header_id,
1308 f.order_number = v.order_number,
1309 f.line_number = v.line_number,
1310 f.inventory_item_id = v.inventory_item_id,
1311 f.item_inv_org_id = v.item_inv_org_id,
1312 f.org_id = v.org_id,
1313 f.salesrep_id = v.salesrep_id,
1314 f.sales_grp_id = v.sales_grp_id,
1315 f.agreement_type_id = v.agreement_type_id,
1316 f.sold_to_org_id = v.sold_to_org_id,
1317 f.customer_id = v.customer_id,
1318 f.time_activation_date_id = v.time_activation_date_id,
1319 f.time_expiration_date_id = v.time_expiration_date_id,
1320 f.time_termination_date_id = v.time_termination_date_id,
1321 f.time_fulfilled_date_id = v.time_fulfilled_date_id,
1322 f.time_effective_end_date_id = v.time_effective_end_date_id,
1323 f.h_start_date_active = v.h_start_date_active,
1324 f.l_start_date_active = v.l_start_date_active,
1325 f.h_end_date_active = v.h_end_date_active,
1326 f.l_end_date_active = v.l_end_date_active,
1327 f.termination_date = v.termination_date,
1328 f.blanket_min_amt = v.blanket_min_amt,
1329 f.blanket_line_min_amt = v.blanket_line_min_amt,
1330 f.fulfilled_amt_g = v.fulfilled_amt_g,
1331 f.fulfilled_amt_g1 = v.fulfilled_amt_g1,
1332 f.h_cnt = v.h_cnt,
1333 f.l_cnt = v.l_cnt,
1334 f.commit_prorated_amt_g = v.commit_prorated_amt_g,
1335 f.commit_prorated_amt_g1 = v.commit_prorated_amt_g1,
1336 f.fulfilled_outstand_amt_g = v.fulfilled_outstand_amt_g,
1337 f.fulfilled_outstand_amt_g1 = v.fulfilled_outstand_amt_g1,
1338 f.transaction_phase_code = v.transaction_phase_code
1339 WHEN NOT MATCHED THEN INSERT(
1340 f.order_line_id,
1341 f.order_line_header_id,
1342 f.order_number,
1343 f.line_number,
1344 f.inventory_item_id,
1345 f.item_inv_org_id,
1346 f.blanket_line_id,
1347 f.blanket_header_id,
1348 f.blanket_number,
1349 f.blanket_line_number,
1350 f.org_id,
1351 f.salesrep_id,
1352 f.sales_grp_id,
1353 f.agreement_type_id,
1354 f.sold_to_org_id,
1355 f.customer_id,
1356 f.time_activation_date_id,
1357 f.time_expiration_date_id,
1358 f.time_termination_date_id,
1359 f.time_fulfilled_date_id,
1360 f.time_effective_end_date_id,
1361 f.h_start_date_active,
1362 f.l_start_date_active,
1363 f.h_end_date_active,
1364 f.l_end_date_active,
1365 f.termination_date,
1366 f.blanket_min_amt,
1367 f.blanket_line_min_amt,
1368 f.fulfilled_amt_g,
1369 f.fulfilled_amt_g1,
1370 f.h_cnt,
1371 f.l_cnt,
1372 f.commit_prorated_amt_g,
1373 f.commit_prorated_amt_g1,
1374 f.fulfilled_outstand_amt_g,
1375 f.fulfilled_outstand_amt_g1,
1376 f.transaction_phase_code,
1377 f.created_by,
1378 f.creation_date,
1379 f.last_updated_by,
1380 f.last_update_date,
1381 f.last_update_login,
1382 f.program_id,
1383 f.program_login_id,
1384 f.program_application_id,
1385 f.request_id
1386 )
1387 VALUES (
1388 v.order_line_id,
1389 v.order_line_header_id,
1390 v.order_number,
1391 v.line_number,
1392 v.inventory_item_id,
1393 v.item_inv_org_id,
1394 v.blanket_line_id,
1395 v.blanket_header_id,
1396 v.blanket_number,
1397 v.blanket_line_number,
1398 v.org_id,
1399 v.salesrep_id,
1400 v.sales_grp_id,
1401 v.agreement_type_id,
1402 v.sold_to_org_id,
1403 v.customer_id,
1404 v.time_activation_date_id,
1405 v.time_expiration_date_id,
1406 v.time_termination_date_id,
1407 v.time_fulfilled_date_id,
1408 v.time_effective_end_date_id,
1409 v.h_start_date_active,
1410 v.l_start_date_active,
1411 v.h_end_date_active,
1412 v.l_end_date_active,
1413 v.termination_date,
1414 v.blanket_min_amt,
1415 v.blanket_line_min_amt,
1416 v.fulfilled_amt_g,
1417 v.fulfilled_amt_g1,
1418 v.h_cnt,
1419 v.l_cnt,
1420 v.commit_prorated_amt_g,
1421 v.commit_prorated_amt_g1,
1422 v.fulfilled_outstand_amt_g,
1423 v.fulfilled_outstand_amt_g1,
1424 v.transaction_phase_code,
1425 -1,
1426 g_incre_start_date,
1427 -1,
1428 g_incre_start_date,
1429 -1,
1430 -1,
1431 -1,
1432 -1,
1433 -1);
1434
1435 l_count := sql%rowcount;
1436 l_total := l_total + l_count;
1437 commit;
1438
1439 fii_util.stop_timer;
1440 fii_util.print_timer('Merged '||l_count|| ' rows in ');
1441
1442 end loop;
1443
1444 return(l_total);
1445
1446 exception
1447 when others then
1448 g_errbuf := 'Error in Function MERGE_FACT : '||sqlerrm;
1449 g_retcode := sqlcode;
1450 return(-1);
1451
1452 end merge_fact;
1453
1454 procedure update_fact(errbuf in out nocopy varchar2,
1455 retcode in out nocopy varchar2) is
1456
1457 l_failure exception;
1458 l_start date;
1459 l_end date;
1460 l_period_from date;
1461 l_period_to date;
1462 l_row_count number;
1463
1464 begin
1465
1466 errbuf := null;
1467 retcode := '0';
1468 g_load_mode := 'INCREMENTAL';
1469 l_row_count := 0;
1470
1471 bis_collection_utilities.put_line(' ');
1472 bis_collection_utilities.put_line('Begin the ' || g_load_mode || ' load');
1473
1474 if (not bis_collection_utilities.setup('ISC_DBI_BSA_ORDER_LINES_INCR')) then
1475 raise_application_error(-20000,'Error in SETUP: ' || errbuf);
1476 return;
1477 end if;
1478
1479 isc_dbi_bsa_objects_c.g_push_from_date := null;
1480 isc_dbi_bsa_objects_c.g_push_to_date := sysdate;
1481
1482 if (CHECK_SETUP = -1)
1483 then raise l_failure;
1484 end if;
1485
1486 bis_collection_utilities.put_line('Identifying changed records');
1487
1488 g_incre_start_date := sysdate;
1489 bis_collection_utilities.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
1490 l_row_count := IDENTIFY_CHANGE_ICRL;
1491
1492 if (l_row_count = -1) then
1493 raise l_failure;
1494 elsif (l_row_count = 0) then
1495 g_row_count := 0;
1496 else
1497
1498 bis_collection_utilities.put_line(' ');
1499 bis_collection_utilities.put_line('Analyzing temp tables');
1500 fii_util.start_timer;
1501
1502 fnd_stats.gather_table_stats(ownname => g_isc_schema,
1503 tabname => 'ISC_DBI_TMP_BSA_ORDER_LINES');
1504 fnd_stats.gather_table_stats(ownname => g_isc_schema,
1505 tabname => 'ISC_CURR_BSA_ORDER_LINES');
1506
1507 fii_util.stop_timer;
1508 fii_util.print_timer('Analyzed the temp tables in ');
1509
1510 if (DANGLING_CHECK_ICRL = -1) then
1511 raise l_failure;
1512 end if;
1513
1514 bis_collection_utilities.put_line(' ');
1515 bis_collection_utilities.put_line('Merging data to fact tables');
1516
1517 g_row_count := MERGE_FACT(ceil(l_row_count/g_batch_size));
1518
1519 bis_collection_utilities.put_line('Merged '||nvl(g_row_count,0)||' rows into the fact tables');
1520
1521 if (g_row_count = -1) then
1522 raise l_failure;
1523 end if;
1524
1525 end if;
1526
1527 -- delete rows from log table
1528
1529 if (WRAPUP = -1) then
1530 raise l_failure;
1531 end if;
1532
1533 retcode := g_retcode;
1534 errbuf := g_errbuf;
1535
1536 exception
1537
1538 when l_failure then
1539 rollback;
1540 bis_collection_utilities.put_line(' ');
1541 bis_collection_utilities.put_line(g_errbuf);
1542 retcode := -1;
1543 errbuf := g_errbuf;
1544
1545 bis_collection_utilities.wrapup(
1546 false,
1547 g_row_count,
1548 g_errbuf,
1549 isc_dbi_bsa_objects_c.g_push_from_date,
1550 isc_dbi_bsa_objects_c.g_push_to_date
1551 );
1552
1553 when others then
1554 rollback;
1555 g_errbuf := sqlerrm ||' - '||sqlcode;
1556 bis_collection_utilities.put_line(' ');
1557 bis_collection_utilities.put_line('Other errors : '|| g_errbuf);
1558 retcode := -1;
1559 errbuf := g_errbuf;
1560
1561 bis_collection_utilities.wrapup(
1562 false,
1563 g_row_count,
1564 g_errbuf,
1565 isc_dbi_bsa_objects_c.g_push_from_date,
1566 isc_dbi_bsa_objects_c.g_push_to_date
1567 );
1568
1569 end update_fact;
1570
1571 end isc_dbi_bsa_objects_c;