[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_UCO_LOAD_PKG
Source
1 PACKAGE BODY ENI_DBI_UCO_LOAD_PKG AS
2 /* $Header: ENIUCOLB.pls 120.1 2006/03/21 02:10:28 lparihar noship $ */
3
4 g_eni_schema VARCHAR2(30);
5 l_status VARCHAR2(30);
6 l_industry VARCHAR2(30);
7
8 -- Populate Temporary Rates table
9 PROCEDURE populate_rates_table(p_refresh_flag VARCHAR2)
10 IS
11 l_err_num NUMBER;
12 l_err_msg VARCHAR2(255);
13 l_prim_rate_type VARCHAR2(15);
14 l_prim_currency_code VARCHAR2(15);
15 l_sec_rate_type VARCHAR2(15);
16 l_sec_currency_code VARCHAR2(15);
17 BEGIN
18
19 -- Setting Up the global_rate_type and the global_start_date
20 l_prim_rate_type := bis_common_parameters.get_rate_type;
21 l_prim_currency_code := bis_common_parameters.get_currency_code;
22 l_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
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)
32 SELECT currency_code,
33 effective_date,
34 decode(l_prim_currency_code, NULL, TO_NUMBER(NULL),
35 fii_currency.get_rate(currency_code, l_prim_currency_code,
36 effective_date, l_prim_rate_type)) primary_rate,
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
46 (currency_code,
47 effective_date,
48 primary_rate,
49 secondary_rate)
50 SELECT currency_code,
51 effective_date,
52 decode(l_prim_currency_code, NULL, TO_NUMBER(NULL),
53 fii_currency.get_rate(currency_code, l_prim_currency_code,
54 effective_date, l_prim_rate_type)) primary_rate,
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
64 commit;
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 rollback;
69 l_err_num := SQLCODE;
70 l_err_msg := 'POPULATE_RATES_TABLE: ' || substr(l_err_num, 1,200);
71
72 eni_dbi_util_pkg.log('Error Number: ' || to_char(l_err_num));
73 eni_dbi_util_pkg.log('Error Message: ' || l_err_msg);
74 RAISE;
75 END populate_rates_table;
76
77
78 -- Initial collection of the cost fact
79 PROCEDURE initial_item_cost_collect
80 ( o_error_msg OUT NOCOPY VARCHAR2,
81 o_error_code OUT NOCOPY VARCHAR2,
82 p_start_date IN VARCHAR2,
83 p_end_date IN VARCHAR2
84 ) IS
85
86 l_start_date date := null;
87 l_end_date date := null;
88 l_exists_sc_orgs number;
89 l_exists_ac_orgs number;
90
91 l_application_user_id number;
92 l_report_missing_rate number;
93 l_processed_txn_id number;
94 l_processed_cost_id number;
95 BEGIN
96
97 IF(FND_INSTALLATION.GET_APP_INFO('ENI', l_status, l_industry, g_eni_schema))
98 THEN NULL;
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);
108 /** COMMENTING these parameter values
109 Bug: 4956685 Initial load should pick all records from global start date to sysdate
110 l_start_date := trunc(TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS'));
111 l_end_date := trunc(TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS'));
112 **/
113 eni_dbi_util_pkg.log('The date range for Initial cost collection is ' || l_start_date || ' to ' || l_end_date);
114
115 if BIS_COLLECTION_UTILITIES.SETUP(
116 p_object_name => 'eni_dbi_item_cost_f',
117 p_parallel => 1) = false then
118 RAISE_APPLICATION_ERROR(-20000,o_error_msg);
119 end if;
120
121 l_exists_sc_orgs := 0;
122 l_exists_ac_orgs := 0;
123
124 -- Find out if there are any standard costing orgs
125 select nvl(max(1),0)
126 into l_exists_sc_orgs
127 from sys.dual
128 where exists (
129 select 'There are standard costing orgs'
130 from mtl_parameters
131 where primary_cost_method = 1
132 );
133
134 -- Find out if there are any Avg/LIFO/FIFO orgs
135 select nvl(max(1),0)
136 into l_exists_ac_orgs
137 from sys.dual
138 where exists (
139 select 'There are Avg/LIFO/FIFO costing orgs'
140 from mtl_parameters
141 where primary_cost_method <> 1
142 );
143
144 select FND_GLOBAL.USER_ID
145 into l_application_user_id
146 from sys.dual;
147
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,
157 material_cost,
158 material_overhead_cost,
159 resource_cost,
160 outside_processing_cost,
161 overhead_cost,
162 last_update_date,
163 last_updated_by,
164 creation_date,
165 created_by,
166 last_update_login,
167 currency_code,
168 conversion_rate)
169 select effective_date,
170 inventory_item_id,
171 organization_id,
172 sum(standard_cost) item_cost,
173 nvl(sum(decode(cost_element_id, 1, standard_cost)), 0) material_cost,
174 nvl(sum(decode(cost_element_id, 2, standard_cost)), 0) material_overhead_cost,
175 nvl(sum(decode(cost_element_id, 3, standard_cost)), 0) resource_cost,
176 nvl(sum(decode(cost_element_id, 4, standard_cost)), 0) outside_processing_cost,
177 nvl(sum(decode(cost_element_id, 5, standard_cost)), 0) overhead_cost,
178 sysdate last_update_date,
179 l_application_user_id last_updated_by,
180 sysdate creation_date,
181 l_application_user_id created_by,
182 l_application_user_id last_update_login,
183 currency_code,
184 null --fii_currency.get_global_rate_primary(currency_code, effective_date) conversion_rate
185 from (
186 select /*+ parallel(cec) parallel(hoi) */
187 cec.inventory_item_id,
188 cec.organization_id,
189 trunc(cec.last_update_date) effective_date,
190 gsob.currency_code,
191 cec.cost_element_id,
192 cec.standard_cost,
193 rank() over (partition by cec.inventory_item_id, cec.organization_id, trunc(cec.last_update_date),
194 gsob.currency_code order by cec.cost_update_id desc) r
195 from cst_elemental_costs cec,
196 hr_organization_information hoi,
197 gl_sets_of_books gsob
198 where cec.organization_id = hoi.organization_id
199 and hoi.org_information_context = 'Accounting Information'
200 and hoi.org_information1 = to_char (gsob.set_of_books_id)
201 and cec.last_update_date >= l_start_date
202 and cec.last_update_date - 0 <= l_end_date + 0.99999
203 )
204 where r = 1
205 group by effective_date, inventory_item_id, organization_id, currency_code;
206
207
208 end if;
209
210 commit; -- commit the standard costing data into staging table.
211
212
213 -- Get the cost history for average/LIFO/FIFO costing orgs from mtl_cst_actual_cost_details.
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,
223 created_by, last_update_login, currency_code, conversion_rate)
224 select /*+ parallel (x) parallel (mcacd) use_hash (mcacd, hoi, gsob)
225 swap_join_inputs (gsob) pq_distribute (mcacd, hash, hash)
226 pq_distribute
227 (gsob, none, broadcast) */
228 trunc(x.asofdate),
229 mcacd.inventory_item_id,
230 mcacd.organization_id,
231 sum (mcacd.new_cost),
232 nvl(sum(decode(mcacd.cost_element_id, 1, mcacd.new_cost)), 0) mtl,
233 nvl(sum(decode(mcacd.cost_element_id, 2, mcacd.new_cost)), 0) mtl_ovh,
234 nvl(sum(decode(mcacd.cost_element_id, 3, mcacd.new_cost)), 0) res,
235 nvl(sum(decode(mcacd.cost_element_id, 4, mcacd.new_cost)), 0) osp,
236 nvl(sum(decode(mcacd.cost_element_id, 5, mcacd.new_cost)), 0) ovhd,
237 sysdate,
238 1,
239 sysdate,
240 1,
241 1,
242 gsob.currency_code,
243 null --fii_currency.get_global_rate_primary (gsob.currency_code,trunc(x.asofdate))
244 from (
245 select /*+ no_merge parallel(mmt) parallel(cql) parallel(mp) full(mmt)
246 swap_join_inputs(cql) */ mmt.inventory_item_id,
247 mmt.organization_id,
248 cql.layer_id, max(mmt.transaction_id) transaction_id,
249 trunc (mmt.transaction_date) asofdate
250 from mtl_material_transactions mmt,
251 cst_quantity_layers cql,
252 mtl_parameters mp
253 where mp.primary_cost_method <> 1
254 and mp.default_cost_group_id = mmt.cost_group_id
255 and mp.organization_id = mmt.organization_id
256 and mmt.transaction_date >= l_start_date
257 and mmt.transaction_date - 0 <= l_end_date + 0.99999
258 and mmt.inventory_item_id = cql.inventory_item_id
259 and mmt.organization_id = cql.organization_id
260 and mmt.cost_group_id = cql.cost_group_id
261 group by mmt.inventory_item_id, mmt.organization_id,
262 cql.layer_id,
263 trunc (mmt.transaction_date)) x,
264 mtl_cst_actual_cost_details mcacd,
265 hr_organization_information hoi,
266 gl_sets_of_books gsob
267 where mcacd.transaction_id = x.transaction_id
268 and mcacd.organization_id = x.organization_id
269 and mcacd.layer_id = x.layer_id
270 and x.organization_id = hoi.organization_id
271 and hoi.org_information_context = 'Accounting Information'
272 and hoi.org_information1 = to_char (gsob.set_of_books_id)
273 group by trunc (x.asofdate), mcacd.inventory_item_id,
274 mcacd.organization_id,
275 gsob.currency_code;
276
277
278 end if;
279
280 eni_dbi_util_pkg.log('Committing initial cost collection into staging table');
281 COMMIT;
282
283 eni_dbi_util_pkg.log('Retreiving currency conversion rates into rates table');
284 populate_rates_table('INITIAL');
285
286 eni_dbi_util_pkg.log('Checking if any missing conversion rates are present');
287
288 l_report_missing_rate := report_missing_rate();
289
290 IF (l_report_missing_rate = 0) THEN -- initial collection completed normally.
291
292 insert /*+ append parallel(a) */ into eni_dbi_item_cost_f a
293 (effective_date,
294 inventory_item_id,
295 organization_id,
296 item_cost,
297 material_cost,
298 material_overhead_cost,
299 resource_cost,
300 outside_processing_cost,
301 overhead_cost,
302 primary_currency_rate,
303 secondary_currency_rate,
304 last_update_date,
305 last_updated_by,
306 creation_date,
307 created_by,
308 last_update_login)
309 select /*+ parallel(edicstg) parallel(eccrstg) */
310 edicstg.effective_date,
311 edicstg.inventory_item_id,
312 edicstg.organization_id,
313 edicstg.item_cost,
314 edicstg.material_cost,
315 edicstg.material_overhead_cost,
316 edicstg.resource_cost,
317 edicstg.outside_processing_cost,
318 edicstg.overhead_cost,
319 eccrstg.primary_rate,
320 eccrstg.secondary_rate,
321 edicstg.last_update_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;
331
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
341 Store the max transaction_id from mmt into bis_refresh_log table
342 We are simply picking the max transaction id as the end date is defaulted
343 to SYSDATE now and the mmt table rows are not updated once inserted.
344 */
345 SELECT Max(TRANSACTION_ID)
346 INTO l_processed_txn_id
347 FROM MTL_MATERIAL_TRANSACTIONS mmt;
348
349 /* Bug: 4936377
350 Store the max cost_update_id from cec into bis_refresh_log table
351 We are simply picking the max cost update id as the end date is defaulted
352 to SYSDATE now and the cec table rows are not updated once inserted.
353 */
354 SELECT Max(COST_UPDATE_ID)
355 INTO l_processed_cost_id
356 FROM cst_elemental_costs cec;
357
358 BIS_COLLECTION_UTILITIES.WRAPUP(
359 p_status => true,
360 p_period_from => l_start_date,
361 p_period_to => l_end_date,
362 p_attribute1 => 'mtl_material_transactions',
363 p_attribute2 => l_processed_txn_id,
364 p_attribute3 => 'cst_elemental_costs',
365 p_attribute4 => l_processed_cost_id
366 );
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
376 EXCEPTION
377
378 WHEN OTHERS THEN
379
380 o_error_code := sqlcode;
381 o_error_msg := sqlerrm;
382
386 BIS_COLLECTION_UTILITIES.WRAPUP(
383 eni_dbi_util_pkg.log('An error prevented the initial cost collection from completing successfully');
384 eni_dbi_util_pkg.log(o_error_code||':'||o_error_msg);
385 Rollback;
387 p_status => false,
388 p_period_from => l_start_date,
389 p_period_to => l_end_date
390 );
391 RAISE_APPLICATION_ERROR(-20000,o_error_msg);
392
393 END initial_item_cost_collect;
394
395 FUNCTION Report_Missing_Rate return NUMBER IS
396 cursor get_missing_rate_c is
397 SELECT effective_date,
398 currency_code,
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
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
414 get_missing_rate_rec get_missing_rate_c%ROWTYPE;
415
416 l_stmt_num NUMBER;
417 l_no_currency_rate_flag NUMBER := 0;
418 l_err_num NUMBER;
419 l_err_msg VARCHAR2(255);
420 l_prim_euro_beg NUMBER; /* Flag to indicate if the 01-JAN-99 issue has been encountered */
421 l_sec_euro_beg NUMBER; /* Flag to indicate if the 01-JAN-99 issue has been encountered */
422 l_prim_rate_type VARCHAR2(15);
423 l_prim_currency_code VARCHAR2(15);
424 l_sec_rate_type VARCHAR2(15);
425 l_sec_currency_code VARCHAR2(15);
426 l_start_date DATE;
427 l_euro_start_date DATE := to_date('01/01/1999','DD/MM/YYYY');
428 BEGIN
429
430 l_prim_euro_beg := 0;
431 l_sec_euro_beg := 0;
432
433 l_stmt_num := 20; /* call api to get get_global_rate_primary */
434
435 -- Setting Up the global_rate_type and the global_start_date
436 l_prim_rate_type := bis_common_parameters.get_rate_type;
437 l_stmt_num := 21;
438 l_prim_currency_code := bis_common_parameters.get_currency_code;
439 l_stmt_num := 22;
440 l_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
441 l_stmt_num := 23;
442 l_sec_currency_code := bis_common_parameters.get_secondary_currency_code;
443 l_stmt_num:=24;
444 l_start_date := bis_common_parameters.get_global_start_date;
445
446 -- If no global currency code was defined
447 -- do not try reporting missing secondary currency conversion rates
448
449 IF (l_prim_currency_code IS NULL)
450 THEN
451 eni_dbi_util_pkg.log('Primary currency code has not been setup, so not checking for missing primary currency conversion rates');
452 END IF;
453
454 IF (l_sec_currency_code IS NULL)
455 THEN
456 eni_dbi_util_pkg.log('Secondary currency code has not been setup, so not checking for missing secondary currency conversion rates');
457 END IF;
458
459 -- Logging all the Missing Rates into the Output file.
460
461 l_stmt_num := 25;
462
463 OPEN get_missing_rate_c;
464 LOOP
465 l_stmt_num:=26;
466 FETCH get_missing_rate_c into get_missing_rate_rec;
467 l_stmt_num:=27;
468 EXIT WHEN get_missing_rate_c%notfound;
469
470 l_stmt_num:=28;
471 IF (l_no_currency_rate_flag = 0) THEN
472 l_no_currency_rate_flag := 1;
473 l_stmt_num:=29;
474 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
475 END IF;
476
477 -- Report missing conversion rates for primary currency
478 IF (l_prim_currency_code IS NOT NULL) AND
479 (get_missing_rate_rec.conversion_rate = -3) AND (l_prim_euro_beg = 0)
480 THEN
481 l_stmt_num:=30;
482
483 BIS_COLLECTION_UTILITIES.writemissingrate
484 (l_prim_rate_type,
485 get_missing_rate_rec.currency_code,
486 l_prim_currency_code,
487 l_euro_start_date);
488 -- Missing rate on the Start of Euro Date has been encountered
489 l_prim_euro_beg := 1;
490
491 ELSIF (l_prim_currency_code IS NOT NULL) AND
492 (get_missing_rate_rec.conversion_rate < 0)
493 THEN
494 l_stmt_num:=31;
495 BIS_COLLECTION_UTILITIES.writemissingrate
496 (l_prim_rate_type,
497 get_missing_rate_rec.currency_code,
498 l_prim_currency_code,
499 get_missing_rate_rec.effective_date);
500 END IF;
501
502 -- Report missing conversion rates for secondary currency
503 IF (l_sec_currency_code IS NOT NULL) AND
504 (get_missing_rate_rec.conversion_rate_s = -3) AND (l_sec_euro_beg = 0)
505 THEN
506 l_stmt_num:=32;
507 BIS_COLLECTION_UTILITIES.writemissingrate
508 (l_sec_rate_type,
509 get_missing_rate_rec.currency_code,
510 l_sec_currency_code,
511 l_euro_start_date);
515 ELSIF (l_sec_currency_code IS NOT NULL) AND
512 -- Missing rate on the Start of Euro Date has been encountered
513 l_sec_euro_beg := 1;
514
516 (get_missing_rate_rec.conversion_rate_s < 0)
517 THEN
518 l_stmt_num:=33;
519 BIS_COLLECTION_UTILITIES.writemissingrate
520 (l_sec_rate_type,
521 get_missing_rate_rec.currency_code,
522 l_sec_currency_code,
523 get_missing_rate_rec.effective_date);
524 END IF;
525 END LOOP;
526
527 CLOSE get_missing_rate_c;
528
529 l_stmt_num := 34; /* check l_no_currency_rate_flag */
530 IF (l_no_currency_rate_flag = 1) THEN /* missing rate found */
531 eni_dbi_util_pkg.log('Please setup conversion rate for all missing rates reported in the output file');
532 return (-1);
533 END IF;
534 return (0);
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 rollback;
539 l_err_num := SQLCODE;
540 l_err_msg := 'REPORT_MISSING_RATE (' || to_char(l_stmt_num)
541 || '): '|| substr(l_err_num, 1,200);
542
543 -- eni_dbi_util_pkg.log('ENI_DBI_UCO_LOAD_PKG.REPORT_MISSING_RATE - Error at statement ('
544 -- || to_char(l_stmt_num) || ')');
545
546 eni_dbi_util_pkg.log('Error Number: ' || to_char(l_err_num));
547 eni_dbi_util_pkg.log('Error Message: ' || l_err_msg);
548 RAISE;
549
550 END REPORT_MISSING_RATE ;
551
552 -- Incremental collection of cost
553 PROCEDURE incremental_item_cost_collect
554 (
555 o_error_msg OUT NOCOPY VARCHAR2,
556 o_error_code OUT NOCOPY VARCHAR2
557 ) IS
558
559 l_last_run_to_date_char varchar2(50);
560 l_last_run_to_date date;
561 l_exists_sc_orgs number;
562 l_exists_ac_orgs number;
563 l_application_user_id number;
564 l_login_id number;
565 rows_in_stage number;
566 run_incremental boolean := FALSE;
567 L_REPORT_MISSING_RATE number;
568 l_processed_txn_id number;
569 l_processed_cost_id number;
570 BEGIN
571
572 IF(FND_INSTALLATION.GET_APP_INFO('ENI', l_status, l_industry, g_eni_schema))
573 THEN NULL;
574 END IF;
575
576
577 if BIS_COLLECTION_UTILITIES.SETUP(
578 p_object_name => 'eni_dbi_item_cost_f',
579 p_parallel => 1) = false then
580 RAISE_APPLICATION_ERROR(-20000,o_error_msg);
581 end if;
582
583 l_last_run_to_date_char := BIS_COLLECTION_UTILITIES.get_last_refresh_period('eni_dbi_item_cost_f');
584 l_last_run_to_date := trunc(fnd_date.displayDT_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('eni_dbi_item_cost_f')));
585 /** Bug: 4956685, 4936377
586 Fetch the last Processed transaction id and cost update id from bis refresh log table.
587 **/
588 SELECT MAX(decode(brl.attribute1,'mtl_material_transactions',attribute2,NULL))
589 ,MAX(decode(brl.attribute3,'cst_elemental_costs' ,attribute4,NULL))
590 INTO l_processed_txn_id, l_processed_cost_id
591 FROM bis_refresh_log brl
592 WHERE brl.object_name = 'eni_dbi_item_cost_f';
593
594 eni_dbi_util_pkg.log('End Period of last cost collection was ' || to_char(l_last_run_to_date));
595 eni_dbi_util_pkg.log('Incremental cost collection will collect records on and after the above date');
596 eni_dbi_util_pkg.log('Last processed transaction_id from mtl_material_transactions table as stored in bis_refresh_log table is:' || l_processed_txn_id);
597 eni_dbi_util_pkg.log('Last processed cost update_id from cst_elemental_costs table as stored in bis_refresh_log table is:' || l_processed_cost_id);
598
599 --eni_dbi_util_pkg.log('End Period of last cost collection was ' || to_char(l_last_run_to_date));
600 --eni_dbi_util_pkg.log('Incremental cost collection will collect records on and after the above date');
601 select FND_GLOBAL.USER_ID
602 into l_application_user_id
603 from sys.dual;
604
605 select FND_GLOBAL.LOGiN_ID
606 into l_login_id
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
616 eni_dbi_util_pkg.log('The initial collection in the previous runs did not
617 complete successfully. Hence at first shot, trying to collect the initial collection information');
618
619 -- Calling if any of the conversion rates are still erroneous.
620
621 eni_dbi_util_pkg.log('Retreiving currency conversion rates into rates table');
622 populate_rates_table('INITIAL');
623
624 l_report_missing_Rate := report_missing_rate();
625
626 IF (l_report_missing_rate = 0) THEN -- initial collection completed normally.
627 insert into /*+ append parallel(a) +*/ eni_dbi_item_cost_f
628 (effective_date,
629 inventory_item_id,
630 organization_id,
631 item_cost,
632 material_cost,
633 material_overhead_cost,
634 resource_cost,
635 outside_processing_cost,
636 overhead_cost,
637 primary_currency_rate,
638 secondary_currency_rate,
639 last_update_date,
640 last_updated_by,
641 creation_date,
642 created_by,
643 last_update_login)
644 select /*+ parallel(edicstg) parallel(eccrstg) */
645 edicstg.effective_date,
646 edicstg.inventory_item_id,
647 edicstg.organization_id,
648 edicstg.item_cost,
649 edicstg.material_cost,
650 edicstg.material_overhead_cost,
651 edicstg.resource_cost,
652 edicstg.outside_processing_cost,
653 edicstg.overhead_cost,
654 eccrstg.primary_rate,
655 eccrstg.secondary_rate,
656 edicstg.last_update_date,
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,
666 -- edicstg.inventory_item_id,
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.
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';
681 RETURN;
682 END IF;
683 END IF;
684
685 -- Running the actual incremental collection
686 l_exists_sc_orgs := 0;
687 l_exists_ac_orgs := 0;
688
689 -- Find out if there are any standard costing orgs
690 select nvl(max(1),0)
691 into l_exists_sc_orgs
692 from sys.dual
693 where exists (
694 select 'There are standard costing orgs'
695 from mtl_parameters
696 where primary_cost_method = 1
697 );
698
699 -- Find out if there are any Avg/LIFO/FIFO orgs
700 select nvl(max(1),0)
701 into l_exists_ac_orgs
702 from sys.dual
703 where exists (
704 select 'There are Avg/LIFO/FIFO costing orgs'
705 from mtl_parameters
706 where primary_cost_method <> 1
707 );
708
709 -- Get the cost history for standard costing orgs from cst_elemental_costs
710 if (l_exists_sc_orgs = 1) then
714 /**
711 eni_dbi_util_pkg.log('There are Standard Costing orgs, hence starting incremental cost collection for them');
712
713 -- Inserting the changed records into stage
715 Bug: 4936377 If the last Processed cost id cannot be queried from bis_refresh_log table compute it
716 from cec table.
717 **/
718 IF l_processed_cost_id IS NULL THEN
719 SELECT NVL( MAX( cost_update_id), 0)
720 INTO l_processed_cost_id
721 FROM cst_elemental_costs cec
722 WHERE cec.last_update_date < l_last_run_to_date;
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,
732 material_cost,
733 material_overhead_cost,
734 resource_cost,
735 outside_processing_cost,
736 overhead_cost,
737 last_update_date,
738 last_updated_by,
739 creation_date,
740 created_by,
741 last_update_login,
742 currency_code,
743 conversion_rate)
744 select effective_date,
745 inventory_item_id,
746 organization_id,
747 sum(standard_cost) item_cost,
748 nvl(sum(decode(cost_element_id, 1, standard_cost)), 0) material_cost,
749 nvl(sum(decode(cost_element_id, 2, standard_cost)), 0) material_overhead_cost,
750 nvl(sum(decode(cost_element_id, 3, standard_cost)), 0) resource_cost,
751 nvl(sum(decode(cost_element_id, 4, standard_cost)), 0) outside_processing_cost,
752 nvl(sum(decode(cost_element_id, 5, standard_cost)), 0) overhead_cost,
753 sysdate last_update_date,
754 l_application_user_id last_updated_by,
755 sysdate creation_date,
756 l_application_user_id created_by,
757 l_application_user_id last_update_login,
758 currency_code,
759 null --fii_currency.get_global_rate_primary(currency_code, effective_date) conversion_rate
760 from (
761 select
762 cec.inventory_item_id,
763 cec.organization_id,
764 trunc(cec.last_update_date) effective_date,
765 gsob.currency_code,
766 cec.cost_element_id,
767 cec.standard_cost,
768 rank() over (partition by cec.inventory_item_id, cec.organization_id, trunc(cec.last_update_date),
769 gsob.currency_code order by cec.cost_update_id desc) r
770 from cst_elemental_costs cec,
771 hr_organization_information hoi,
772 gl_sets_of_books gsob
773 where cec.organization_id = hoi.organization_id
774 and hoi.org_information_context = 'Accounting Information'
775 and hoi.org_information1 = to_char (gsob.set_of_books_id)
776 and cec.cost_update_id >= l_processed_cost_id
777 )
778 where r = 1
779 group by effective_date, inventory_item_id, organization_id, currency_code;
780
781 end if;
782
783 commit; -- commit the standard costing data into the staging.
784
785 -- Get the cost changes for average/LIFO/FIFO costing orgs from mtl_cst_actual_cost_details.
786 if (l_exists_ac_orgs = 1) then
787
788 eni_dbi_util_pkg.log('There are Avg/LIFO/FIFO Costing orgs, hence starting incremental cost collection for them');
789
790 -- Inserting the changed records into stage
791 /**
792 Bug: 4956685 If the last Processed txn id cannot be queried from bis_refresh_log table compute it
793 from mmt table.
794 **/
795 IF l_processed_txn_id IS NULL THEN
796 SELECT NVL( MAX( transaction_id), 0)
797 INTO l_processed_txn_id
798 FROM mtl_material_transactions mmt
799 WHERE mmt.transaction_date < l_last_run_to_date;
800 END IF; -- l_processed_txn_id
801
802 /**
803 Bug: 4956685 We have the last Processed transaction_id from mmt table
804 This modified query will have a different nested query on the mmt table
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,
814 created_by, last_update_login, currency_code, conversion_rate)
815 select /*+ parallel (x) parallel (mcacd) use_hash (mcacd, hoi, gsob)
816 swap_join_inputs (gsob) pq_distribute (mcacd, hash, hash)
817 pq_distribute
818 (gsob, none, broadcast) */
819 trunc(x.asofdate),
820 mcacd.inventory_item_id,
821 mcacd.organization_id,
822 sum (mcacd.new_cost),
823 nvl(sum(decode(mcacd.cost_element_id, 1, mcacd.new_cost)), 0) mtl,
824 nvl(sum(decode(mcacd.cost_element_id, 2, mcacd.new_cost)), 0) mtl_ovh,
825 nvl(sum(decode(mcacd.cost_element_id, 3, mcacd.new_cost)), 0) res,
826 nvl(sum(decode(mcacd.cost_element_id, 4, mcacd.new_cost)), 0) osp,
827 nvl(sum(decode(mcacd.cost_element_id, 5, mcacd.new_cost)), 0) ovhd,
828 sysdate,
829 1,
830 sysdate,
831 1,
832 1,
833 gsob.currency_code,
834 null --fii_currency.get_global_rate_primary (gsob.currency_code,trunc(x.asofdate))
835 from (
836 select /*+ no_merge parallel(mmt) parallel(cql) parallel(mp)
837 swap_join_inputs(cql) */ mmt.inventory_item_id,
838 mmt.organization_id,
842 cst_quantity_layers cql,
839 cql.layer_id, max(mmt.transaction_id) transaction_id,
840 trunc (mmt.transaction_date) asofdate
841 from mtl_material_transactions mmt,
843 mtl_parameters mp
844 where mp.primary_cost_method <> 1
845 and mp.default_cost_group_id = mmt.cost_group_id
846 and mp.organization_id = mmt.organization_id
847 and mmt.transaction_id > l_processed_txn_id
848 and mmt.inventory_item_id = cql.inventory_item_id
849 and mmt.organization_id = cql.organization_id
850 and mmt.cost_group_id = cql.cost_group_id
851 group by mmt.inventory_item_id, mmt.organization_id,
852 cql.layer_id,
853 trunc (mmt.transaction_date)) x,
854 mtl_cst_actual_cost_details mcacd,
855 hr_organization_information hoi,
856 gl_sets_of_books gsob
857 where mcacd.transaction_id = x.transaction_id
858 and mcacd.organization_id = x.organization_id
859 and mcacd.layer_id = x.layer_id
860 and x.organization_id = hoi.organization_id
861 and hoi.org_information_context = 'Accounting Information'
862 and hoi.org_information1 = to_char (gsob.set_of_books_id)
863 group by trunc (x.asofdate), mcacd.inventory_item_id,
864 mcacd.organization_id,
865 gsob.currency_code;
866
867
868 end if;
869
870 commit;
871
872 eni_dbi_util_pkg.log('Retreiving currency conversion rates into rates table');
873 populate_rates_table('INCREMENTAL');
874
875 eni_dbi_util_pkg.log('Checking if any missing conversion rates are present');
876 l_report_missing_rate := report_missing_rate();
877 IF (l_report_missing_rate = 0) THEN -- initial collection completed normally.
878 merge into eni_dbi_item_cost_f old_costs
879 using
880 (select
881 edicstg.effective_date,
882 edicstg.inventory_item_id,
883 edicstg.organization_id,
884 edicstg.item_cost item_cost,
885 edicstg.material_cost material_cost,
886 edicstg.material_overhead_cost material_overhead_cost,
887 edicstg.resource_cost resource_cost,
888 edicstg.outside_processing_cost outside_processing_cost,
889 edicstg.overhead_cost overhead_cost,
890 eccrstg.primary_rate primary_rate,
891 eccrstg.secondary_rate secondary_rate,
892 edicstg.last_update_date last_update_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,
902 -- inventory_item_id,
903 -- organization_id
904 ) new_costs
905 on
906 (old_costs.inventory_item_id = new_costs.inventory_item_id and
907 old_costs.organization_id = new_costs.organization_id and
908 old_costs.effective_date = new_costs.effective_date)
909 when matched then
910 update set old_costs.item_cost = new_costs.item_cost,
911 old_costs.material_cost = new_costs.material_cost,
912 old_costs.material_overhead_cost = new_costs.material_overhead_cost,
913 old_costs.resource_cost = new_costs.resource_cost,
914 old_costs.outside_processing_cost = new_costs.outside_processing_cost,
915 old_costs.overhead_cost = new_costs.overhead_cost,
916 old_costs.primary_currency_rate = new_costs.primary_rate,
917 old_costs.secondary_currency_rate = new_costs.secondary_rate,
918 old_costs.last_update_date = sysdate,
919 old_costs.last_updated_by = l_application_user_id,
920 old_costs.last_update_login = l_application_user_id
921 when not matched then
922 insert (old_costs.effective_date
923 , old_costs.inventory_item_id
924 , old_costs.organization_id
925 , old_costs.item_cost
926 , old_costs.material_cost
927 , old_costs.material_overhead_cost
928 , old_costs.resource_cost
929 , old_costs.outside_processing_cost
930 , old_costs.overhead_cost
931 , old_costs.primary_currency_rate
932 , old_costs.secondary_currency_rate
933 , old_costs.last_update_date
934 , old_costs.last_updated_by
935 , old_costs.creation_date
936 , old_costs.created_by
937 , old_costs.last_update_login)
938 values ( new_costs.effective_date
939 , new_costs.inventory_item_id
940 , new_costs.organization_id
941 , new_costs.item_cost
942 , new_costs.material_cost
943 , new_costs.material_overhead_cost
944 , new_costs.resource_cost
945 , new_costs.outside_processing_cost
946 , new_costs.overhead_cost
947 , new_costs.primary_rate
948 , new_costs.secondary_rate
949 , sysdate
950 , l_application_user_id
951 , sysdate
952 , l_application_user_id
953 , l_application_user_id
954 );
955 COMMIT;
956 eni_dbi_util_pkg.log('Incremental cost collection is Complete and Successful');
960 --Bug: 4956685 Query the max txn id from mmt table
957 o_error_code := 0;
958 o_error_msg := 'Incremental Cost Collection is Complete and Successful';
959
961 SELECT Max(TRANSACTION_ID)
962 INTO l_processed_txn_id
963 FROM MTL_MATERIAL_TRANSACTIONS;
964
965 --Bug: 4936377 Query the max cost update id from cec table
966 SELECT Max(COST_UPDATE_ID)
967 INTO l_processed_cost_id
968 FROM cst_elemental_costs;
969
970 BIS_COLLECTION_UTILITIES.WRAPUP(
971 p_status => true,
972 p_period_from => l_last_run_to_date,
973 p_period_to => sysdate,
974 p_attribute1 => 'mtl_material_transactions',
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.');
984 eni_dbi_util_pkg.log('Please modify the conversion rates and execute the incremental collection again.');
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
994
995 WHEN OTHERS THEN
996
997 o_error_code := sqlcode;
998 o_error_msg := sqlerrm;
999
1000 eni_dbi_util_pkg.log('An error prevented the incremental cost collection from completing successfully');
1001 eni_dbi_util_pkg.log(o_error_code||':'||o_error_msg);
1002 Rollback;
1003 BIS_COLLECTION_UTILITIES.WRAPUP(
1004 p_status => false,
1005 p_period_from => l_last_run_to_date,
1006 p_period_to => l_last_run_to_date
1007 );
1008 RAISE_APPLICATION_ERROR(-20000,o_error_msg);
1009
1010 END incremental_item_cost_collect;
1011
1012 END ENI_DBI_UCO_LOAD_PKG;