[Home] [Help]
37: decode(l_sec_currency_code, NULL, TO_NUMBER(NULL),
38: fii_currency.get_rate(currency_code, l_sec_currency_code,
39: effective_date, l_sec_rate_type)) secondary_rate
40: FROM (SELECT /*+ PARALLEL(tmp) */ DISTINCT currency_code ,
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
55: decode(l_sec_currency_code, NULL, TO_NUMBER(NULL),
56: fii_currency.get_rate(currency_code, l_sec_currency_code,
57: effective_date, l_sec_rate_type)) secondary_rate
58: FROM (SELECT DISTINCT currency_code ,
59: effective_date FROM eni_dbi_item_cost_stg);
60: END IF;
61:
62: eni_dbi_util_pkg.log('Inserted ' ||sql%ROWCOUNT || ' currency rates into rates table');
63:
99: END IF;
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);
148: -- Get the cost history for standard costing orgs from cst_elemental_costs
149: if (l_exists_sc_orgs = 1) then
150: eni_dbi_util_pkg.log('There are Standard Costing orgs, hence starting initial cost collection into stage table for them');
151:
152: insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
153: (effective_date,
154: inventory_item_id,
155: organization_id,
156: item_cost,
214: if (l_exists_ac_orgs = 1) then
215:
216: eni_dbi_util_pkg.log('There are Avg/LIFO/FIFO Costing orgs, hence starting initial cost collection into stage table for them');
217:
218: insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
219: (effective_date,
220: inventory_item_id, organization_id, item_cost, material_cost,
221: material_overhead_cost, resource_cost, outside_processing_cost,
222: overhead_cost, last_update_date, last_updated_by, creation_date,
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;
332:
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
405: AND effective_date IS NOT NULL;
406:
407: /* cursor get_missing_rate_c is
408: select distinct currency_code, effective_date, conversion_rate, conversion_rate_s
409: from eni_dbi_item_cost_stg
410: where (NVL(conversion_rate,-99) < 0
411: OR NVL(conversion_rate_s,-99) < 0)
412: AND effective_date IS NOT NULL;*/
413:
607: from sys.dual;
608:
609: select NVL(max(1),0)
610: into rows_in_stage
611: from eni_dbi_item_cost_stg;
612:
613: IF (rows_in_stage = 0) THEN
614: run_incremental := TRUE;
615: ELSE
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,
667: -- edicstg.organization_id;
668:
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.
723: END IF; -- l_processed_cost_id
724:
725: eni_dbi_util_pkg.log('Processing cst_elemental_costs.cost_update_id greater than ' || l_processed_cost_id);
726:
727: insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
728: (effective_date,
729: inventory_item_id,
730: organization_id,
731: item_cost,
805: with a predicate on transaction_id instead of last_run_date
806: */
807: eni_dbi_util_pkg.log('Processing mtl_material_transactions.transaction_id greater than ' || l_processed_txn_id);
808:
809: insert /*+ append parallel(a) */ into eni_dbi_item_cost_stg a
810: (effective_date,
811: inventory_item_id, organization_id, item_cost, material_cost,
812: material_overhead_cost, resource_cost, outside_processing_cost,
813: overhead_cost, last_update_date, last_updated_by, creation_date,
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,
975: p_attribute2 => l_processed_txn_id,
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.');
985: o_error_code := 1;
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