[Home] [Help]
35: , p_message varchar2
36: )
37: as
38: begin
39: bis_collection_utilities.log( g_pkg_name || '.' || p_proc_name ||
40: ' #' || p_stmt_id || ' ' ||
41: p_message
42: , 3 );
43: end logger;
165: l_rowcount number;
166:
167: begin
168:
169: bis_collection_utilities.log( 'Begin Currency Conversion', 1 );
170:
171: -- get the primary global currency code
172: l_stmt_id := 10;
173: l_global_currency_code1 := bis_common_parameters.get_currency_code;
176: logger( l_proc_name, l_stmt_id, l_error_message );
177: raise l_exception;
178: end if;
179:
180: bis_collection_utilities.log( 'Primary global currency code: ' || l_global_currency_code1, 2 );
181:
182: -- get the primary global rate type
183: l_stmt_id := 20;
184: l_global_rate_type1 := bis_common_parameters.get_rate_type;
187: logger( l_proc_name, l_stmt_id, l_error_message );
188: raise l_exception;
189: end if;
190:
191: bis_collection_utilities.log( 'Primary global rate type: ' || l_global_rate_type1, 2 );
192:
193: -- get the secondary global currency code
194: l_stmt_id := 30;
195: l_global_currency_code2 := bis_common_parameters.get_secondary_currency_code;
194: l_stmt_id := 30;
195: l_global_currency_code2 := bis_common_parameters.get_secondary_currency_code;
196:
197: if l_global_currency_code2 is not null then
198: bis_collection_utilities.log( 'Secondary global currency code: ' || l_global_currency_code2, 2 );
199: else
200: bis_collection_utilities.log( 'Secondary global currency code is not defined', 2 );
201: end if;
202:
196:
197: if l_global_currency_code2 is not null then
198: bis_collection_utilities.log( 'Secondary global currency code: ' || l_global_currency_code2, 2 );
199: else
200: bis_collection_utilities.log( 'Secondary global currency code is not defined', 2 );
201: end if;
202:
203: -- get the secondary global rate type
204: l_stmt_id := 40;
209: raise l_exception;
210: end if;
211:
212: if l_global_currency_code2 is not null then
213: bis_collection_utilities.log( 'Secondary global rate type: ' || l_global_rate_type2, 2 );
214: end if;
215:
216: -- truncate the conversion rates work table
217: l_stmt_id := 50;
222: logger( l_proc_name, l_stmt_id, l_error_message );
223: raise l_exception;
224: end if;
225:
226: bis_collection_utilities.log( 'Currency conversion table truncated', 2 );
227:
228: -- Get all the distinct organization and date pairs and the
229: -- base currency codes for the orgs into the conversion rates
230: -- work table.
311: l_rowcount := sql%rowcount;
312:
313: commit;
314:
315: bis_collection_utilities.log( l_rowcount || ' rows inserted into currency conversion table', 2 );
316:
317: l_all_rates_found := true;
318:
319: -- gather statistics on conversion rates table before returning
325: logger( l_proc_name, l_stmt_id, l_error_message );
326: raise l_exception;
327: end if;
328:
329: bis_collection_utilities.log( 'Currency conversion table analyzed', 2 );
330:
331: -- Check that all rates have been found and are non-negative.
332: -- If there is a problem, notify user.
333: l_stmt_id := 80;
334: for invalid_rate_rec in c_invalid_rates loop
335:
336: -- print the header out
337: if c_invalid_rates%rowcount = 1 then
338: bis_collection_utilities.writeMissingRateHeader;
339: end if;
340:
341: l_all_rates_found := false;
342:
340:
341: l_all_rates_found := false;
342:
343: if invalid_rate_rec.primary_rate < 0 then
344: bis_collection_utilities.writeMissingRate
345: ( l_global_rate_type1
346: , invalid_rate_rec.base_currency_code
347: , l_global_currency_code1
348: , invalid_rate_rec.transaction_date );
348: , invalid_rate_rec.transaction_date );
349: end if;
350:
351: if invalid_rate_rec.secondary_rate < 0 then
352: bis_collection_utilities.writeMissingRate
353: ( l_global_rate_type2
354: , invalid_rate_rec.base_currency_code
355: , l_global_currency_code2
356: , invalid_rate_rec.transaction_date );
364: logger( l_proc_name, l_stmt_id, l_error_message );
365: raise l_exception;
366: end if;
367:
368: bis_collection_utilities.log( 'End Currency Conversion', 1 );
369:
370: return 0;
371:
372: exception
392: l_refresh_date date;
393:
394: begin
395:
396: l_refresh_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period(g_object_name));
397: if l_refresh_date = g_global_start_date then
398: x_error_message := 'Incremental Load can only be run after a completed initial or incremental load';
399: return -1;
400: end if;
439: begin
440:
441: local_init;
442:
443: bis_collection_utilities.log( 'Begin Initial Load' );
444:
445: l_stmt_id := 0;
446: if not bis_collection_utilities.setup( g_object_name ) then
447: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
442:
443: bis_collection_utilities.log( 'Begin Initial Load' );
444:
445: l_stmt_id := 0;
446: if not bis_collection_utilities.setup( g_object_name ) then
447: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
448: logger( l_proc_name, l_stmt_id, l_error_message );
449: raise g_bis_setup_exception;
450: end if;
443: bis_collection_utilities.log( 'Begin Initial Load' );
444:
445: l_stmt_id := 0;
446: if not bis_collection_utilities.setup( g_object_name ) then
447: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
448: logger( l_proc_name, l_stmt_id, l_error_message );
449: raise g_bis_setup_exception;
450: end if;
451:
459:
460: l_collect_from_date := g_global_start_date;
461: l_collect_to_date := sysdate;
462:
463: bis_collection_utilities.log( 'From ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
464: bis_collection_utilities.log( 'To ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
465:
466: -- get the isc schema name
467: l_stmt_id := 20;
460: l_collect_from_date := g_global_start_date;
461: l_collect_to_date := sysdate;
462:
463: bis_collection_utilities.log( 'From ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
464: bis_collection_utilities.log( 'To ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
465:
466: -- get the isc schema name
467: l_stmt_id := 20;
468: if get_schema_name
481: logger( l_proc_name, l_stmt_id, l_error_message );
482: raise l_exception;
483: end if;
484:
485: bis_collection_utilities.log( 'Staging table truncated', 1 );
486:
487: -- ------------------------------------------------------------
488: -- this inserts into the staging table the "current"
489: -- total actual and estimated costs for completed or closed
563: , nvl(wo.completion_date,wo.closed_date);
564:
565: l_rowcount := sql%rowcount;
566:
567: bis_collection_utilities.log( l_rowcount || ' rows inserted into staging table', 1 );
568:
569: -- gather statistics on staging table before computing
570: -- conversion rates
571: l_stmt_id := 50;
576: logger( l_proc_name, l_stmt_id, l_error_message );
577: raise l_exception;
578: end if;
579:
580: bis_collection_utilities.log( 'Staging table analyzed', 1 );
581:
582: -- check currency conversion rates
583: l_stmt_id := 60;
584: if compute_cost_conv_rates
597: logger( l_proc_name, l_stmt_id, l_error_message );
598: raise l_exception;
599: end if;
600:
601: bis_collection_utilities.log( 'Base summary table truncated', 1 );
602:
603: -- insert into base fact from staging table
604: l_stmt_id := 80;
605: insert /*+ append parallel(f) */
664: l_rowcount := sql%rowcount;
665:
666: commit;
667:
668: bis_collection_utilities.log( l_rowcount || ' rows inserted into base summary', 1 );
669:
670: -- house keeping -- cleanup staging/currency conversion tables
671: l_stmt_id := 90;
672: if truncate_table
676: logger( l_proc_name, l_stmt_id, l_error_message );
677: raise l_exception;
678: end if;
679:
680: bis_collection_utilities.log( 'Staging table truncated', 1 );
681:
682: l_stmt_id := 100;
683: if truncate_table
684: ( l_isc_schema
687: logger( l_proc_name, l_stmt_id, l_error_message );
688: raise l_exception;
689: end if;
690:
691: bis_collection_utilities.log( 'Currency conversion table truncated', 1 );
692:
693: l_stmt_id := 110;
694: bis_collection_utilities.wrapup( p_status => true
695: , p_period_from => l_collect_from_date
690:
691: bis_collection_utilities.log( 'Currency conversion table truncated', 1 );
692:
693: l_stmt_id := 110;
694: bis_collection_utilities.wrapup( p_status => true
695: , p_period_from => l_collect_from_date
696: , p_period_to => l_collect_to_date
697: , p_count => l_rowcount
698: );
696: , p_period_to => l_collect_to_date
697: , p_count => l_rowcount
698: );
699:
700: bis_collection_utilities.log('End Initial Load');
701:
702: errbuf := null;
703: retcode := g_success;
704:
712: rollback;
713: if l_error_message is null then
714: l_error_message := substr(sqlerrm,1,4000);
715: end if;
716: bis_collection_utilities.wrapup( p_status => false
717: , p_message => l_error_message
718: , p_period_from => l_collect_from_date
719: , p_period_to => l_collect_to_date
720: );
745: begin
746:
747: local_init;
748:
749: bis_collection_utilities.log( 'Begin Incremental Load' );
750:
751: l_stmt_id := 0;
752: if not bis_collection_utilities.setup( g_object_name ) then
753: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
748:
749: bis_collection_utilities.log( 'Begin Incremental Load' );
750:
751: l_stmt_id := 0;
752: if not bis_collection_utilities.setup( g_object_name ) then
753: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
754: logger( l_proc_name, l_stmt_id, l_error_message );
755: raise g_bis_setup_exception;
756: end if;
749: bis_collection_utilities.log( 'Begin Incremental Load' );
750:
751: l_stmt_id := 0;
752: if not bis_collection_utilities.setup( g_object_name ) then
753: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
754: logger( l_proc_name, l_stmt_id, l_error_message );
755: raise g_bis_setup_exception;
756: end if;
757:
763: end if;
764: l_collect_from_date := l_collect_to_date + 1/86400;
765: l_collect_to_date := sysdate;
766:
767: bis_collection_utilities.log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
768: bis_collection_utilities.log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
769:
770: -- get the isc schema name
771: l_stmt_id := 20;
764: l_collect_from_date := l_collect_to_date + 1/86400;
765: l_collect_to_date := sysdate;
766:
767: bis_collection_utilities.log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
768: bis_collection_utilities.log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
769:
770: -- get the isc schema name
771: l_stmt_id := 20;
772: if get_schema_name
785: logger( l_proc_name, l_stmt_id, l_error_message );
786: raise l_exception;
787: end if;
788:
789: bis_collection_utilities.log( 'Staging table truncated', 1 );
790:
791: -- ------------------------------------------------------------
792: -- this inserts into the staging table the "current"
793: -- total actual and estimated costs for completed or closed
986: , 'N' );
987:
988: l_rowcount := sql%rowcount;
989:
990: bis_collection_utilities.log( l_rowcount || ' rows inserted into staging table', 1 );
991:
992: -- gather statistics on staging table before computing
993: -- conversion rates
994: l_stmt_id := 50;
999: logger( l_proc_name, l_stmt_id, l_error_message );
1000: raise l_exception;
1001: end if;
1002:
1003: bis_collection_utilities.log( 'Staging table analyzed', 1 );
1004:
1005: -- check currency conversion rates
1006: l_stmt_id := 60;
1007: if compute_cost_conv_rates
1146: l_rowcount := sql%rowcount;
1147:
1148: commit;
1149:
1150: bis_collection_utilities.log( l_rowcount || ' rows merged into base summary', 1 );
1151:
1152: -- house keeping -- cleanup staging/currency conversion tables
1153: l_stmt_id := 80;
1154: if truncate_table
1158: logger( l_proc_name, l_stmt_id, l_error_message );
1159: raise l_exception;
1160: end if;
1161:
1162: bis_collection_utilities.log( 'Staging table truncated', 1 );
1163:
1164: l_stmt_id := 90;
1165: if truncate_table
1166: ( l_isc_schema
1169: logger( l_proc_name, l_stmt_id, l_error_message );
1170: raise l_exception;
1171: end if;
1172:
1173: bis_collection_utilities.log( 'Currency conversion table truncated', 1 );
1174:
1175: l_stmt_id := 100;
1176: bis_collection_utilities.wrapup( p_status => true
1177: , p_period_from => l_collect_from_date
1172:
1173: bis_collection_utilities.log( 'Currency conversion table truncated', 1 );
1174:
1175: l_stmt_id := 100;
1176: bis_collection_utilities.wrapup( p_status => true
1177: , p_period_from => l_collect_from_date
1178: , p_period_to => l_collect_to_date
1179: , p_count => l_rowcount
1180: );
1178: , p_period_to => l_collect_to_date
1179: , p_count => l_rowcount
1180: );
1181:
1182: bis_collection_utilities.log('End Incremental Load');
1183:
1184: errbuf := null;
1185: retcode := g_success;
1186:
1195: rollback;
1196: if l_error_message is null then
1197: l_error_message := substr(sqlerrm,1,4000);
1198: end if;
1199: bis_collection_utilities.wrapup( p_status => false
1200: , p_message => l_error_message
1201: , p_period_from => l_collect_from_date
1202: , p_period_to => l_collect_to_date
1203: );