[Home] [Help]
23: l_sec_currency_code := bis_common_parameters.get_secondary_currency_code;
24:
25: IF (p_refresh_flag = 'INITIAL')
26: THEN
27: INSERT INTO eni_currency_conv_rates_stg
28: (currency_code,
29: effective_date,
30: primary_rate,
31: secondary_rate)
41: effective_date FROM eni_dbi_item_cost_stg tmp);
42:
43: ELSIF (p_refresh_flag = 'INCREMENTAL')
44: THEN
45: INSERT INTO eni_currency_conv_rates_stg
46: (currency_code,
47: effective_date,
48: primary_rate,
49: secondary_rate)
100:
101: eni_dbi_util_pkg.log('Truncating the cost staging, rates staging and cost fact tables');
102: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_item_cost_f';
103: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_item_cost_stg';
104: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
105:
106: l_start_date := trunc (bis_common_parameters.get_global_start_date);
107: l_end_date := trunc (SYSDATE);
108: /** COMMENTING these parameter values
322: edicstg.last_updated_by,
323: edicstg.creation_date,
324: edicstg.created_by,
325: edicstg.last_update_login
326: from eni_dbi_item_cost_stg edicstg, eni_currency_conv_rates_stg eccrstg
327: where edicstg.currency_code = eccrstg.currency_code
328: and edicstg.effective_date = eccrstg.effective_date;
329:
330: COMMIT;
333: eni_dbi_util_pkg.log('Initial cost collection Complete and Successful');
334: o_error_code := 0;
335: o_error_msg := 'Initial Cost Collection is Complete and Successful';
336: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_item_cost_stg';
337: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
338: COMMIT;
339:
340: /* Bug: 4956685
341: Store the max transaction_id from mmt into bis_refresh_log table
367:
368: ELSE
369: eni_dbi_util_pkg.log('Initial cost collection has completed with errors in the conversion rates.');
370: eni_dbi_util_pkg.log('Please modify the conversion rates and execute the incremental collection.');
371: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
372: o_error_code := 1;
373: o_error_msg := 'Initial Cost Collection has completed with conversion rate errors';
374: END IF;
375:
399: primary_rate conversion_rate,
400: secondary_rate conversion_rate_s--,
401: -- decode(primary_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') PRIMARY_STATUS,
402: -- decode(secondary_rate, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') SECONDARY_STATUS
403: FROM eni_currency_conv_rates_stg
404: WHERE (nvl(primary_rate,99) < 0 OR nvl(secondary_rate,99) < 0)
405: AND effective_date IS NOT NULL;
406:
407: /* cursor get_missing_rate_c is
657: edicstg.last_updated_by,
658: edicstg.creation_date,
659: edicstg.created_by,
660: edicstg.last_update_login
661: from eni_dbi_item_cost_stg edicstg, eni_currency_conv_rates_stg eccrstg
662: where edicstg.currency_code = eccrstg.currency_code
663: and edicstg.effective_date = eccrstg.effective_date;
664: --group by
665: -- edicstg.effective_date,
669: COMMIT;
670: eni_dbi_util_pkg.log('Initial cost collection Complete and Successful');
671: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_item_cost_stg';
672: -- Bug#3994228 This table should be truncated too
673: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
674: COMMIT;
675: ELSIF (l_report_missing_rate = -1) THEN -- there were missing rates.
676: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
677: eni_dbi_util_pkg.log('Initial cost collection of previous runs has completed with errors.');
672: -- Bug#3994228 This table should be truncated too
673: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
674: COMMIT;
675: ELSIF (l_report_missing_rate = -1) THEN -- there were missing rates.
676: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
677: eni_dbi_util_pkg.log('Initial cost collection of previous runs has completed with errors.');
678: eni_dbi_util_pkg.log('Please modify the conversion rates and execute the incremental collection again');
679: o_error_code := 1;
680: o_error_msg := 'Initial Cost Collection has completed with conversion rate errors';
893: edicstg.last_updated_by last_updated_by,
894: edicstg.creation_date creation_date,
895: edicstg.created_by created_by,
896: edicstg.last_update_login last_update_login
897: from eni_dbi_item_cost_stg edicstg, eni_currency_conv_rates_stg eccrstg
898: where edicstg.currency_code = eccrstg.currency_code
899: and edicstg.effective_date = eccrstg.effective_date
900: -- group by
901: -- effective_date,
976: p_attribute3 => 'cst_elemental_costs',
977: p_attribute4 => l_processed_cost_id
978: );
979: --execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_item_cost_stg';
980: --execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
981:
982: ELSE
983: eni_dbi_util_pkg.log('Incremental cost collection has completed with errors in the conversion rates.');
984: eni_dbi_util_pkg.log('Please modify the conversion rates and execute the incremental collection again.');
986: o_error_msg := 'Incremental Cost Collection has completed with conversion rate errors';
987: END IF;
988:
989: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_item_cost_stg';
990: execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_currency_conv_rates_stg';
991: COMMIT;
992:
993: EXCEPTION
994: