[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_PTP_BASELINE_PKG
Source
1 PACKAGE BODY OPI_DBI_PTP_BASELINE_PKG as
2 /* $Header: OPIDPTPETLB.pls 120.1 2006/02/19 22:28:38 vganeshk noship $ */
3
4 --global variables
5 g_ok CONSTANT NUMBER := 0;
6 g_error CONSTANT NUMBER := -1;
7 g_warning CONSTANT NUMBER := 1;
8 g_sysdate DATE;
9 g_created_by NUMBER;
10 g_last_update_login NUMBER;
11 g_last_updated_by NUMBER;
12 g_global_start_date DATE;
13 g_global_currency_code VARCHAR2(10);
14 g_secondary_currency_code VARCHAR2 (10);
15 g_opi_schema VARCHAR2(30);
16 g_degree NUMBER := 0;
17 g_global_rate_type VARCHAR2(15);
18 g_secondary_rate_type VARCHAR2(15);
19
20 --pre-defined oracle exceptions
21 partition_exist_exception EXCEPTION;
22 value_exist_exception EXCEPTION;
23 tablename_exist_exception EXCEPTION;
24 PRAGMA EXCEPTION_INIT (partition_exist_exception, -14013);
25 PRAGMA EXCEPTION_INIT (value_exist_exception, -14312);
26 PRAGMA EXCEPTION_INIT (tablename_exist_exception, -00955);
27
28 --ptp-defined exceptions
29 intialization_exception EXCEPTION;
30 collection_parameter_exception EXCEPTION;
31 load_exception EXCEPTION;
32 archive_cleanup_exception EXCEPTION;
33 update_log_exception EXCEPTION;
34 cost_conversion_rate_exception EXCEPTION;
35 mv_refresh_exception EXCEPTION;
36 isc_collection_exception EXCEPTION;
37
38 PRAGMA EXCEPTION_INIT (intialization_exception, -20900);
39 PRAGMA EXCEPTION_INIT (collection_parameter_exception, -20901);
40 PRAGMA EXCEPTION_INIT (load_exception, -20902);
41 PRAGMA EXCEPTION_INIT (archive_cleanup_exception, -20903);
42 PRAGMA EXCEPTION_INIT (update_log_exception, -20904);
43 PRAGMA EXCEPTION_INIT (cost_conversion_rate_exception, -20905);
44 PRAGMA EXCEPTION_INIT (mv_refresh_exception, -20906);
45 PRAGMA EXCEPTION_INIT (isc_collection_exception, -20907);
46
47 /*
48 Procedure to extract cost/conversion rate for discrete manufacturing orgs.
49 We extract cost for all items of organizations associated with the baseline being processed.
50 */
51 PROCEDURE Get_Discrete_Cost_and_Rate
52 (
53 errbuf OUT NOCOPY VARCHAR2,
54 retcode OUT NOCOPY VARCHAR2
55 )
56 IS
57 TYPE NUMBERLIST is TABLE of NUMBER;
58 l_org_list NUMBERLIST := NUMBERLIST();
59 l_existing_list NUMBERLIST := NUMBERLIST();
60 l_new_orgs VARCHAR2(1024) := NULL;
61 l_existing_orgs VARCHAR2(1024) := NULL;
62 TYPE DATELIST is TABLE of DATE;
63 l_from_dates DATELIST := DATELIST();
64 l_stmt VARCHAR2(10240);
65 l_currency_code VARCHAR2(10);
66 l_rate NUMBER;
67 l_secondary_rate NUMBER;
68 l_missing_flag NUMBER := 0;
69 l_stmt_num NUMBER := 0;
70
71 -- marker to see if primary and secondary currencies are the same
72 l_pri_sec_curr_same NUMBER;
73
74 BEGIN
75
76 l_stmt_num := 5;
77 l_pri_sec_curr_same := 0;
78 -- check if the primary and secondary currencies and rate types are
79 -- identical.
80 IF (g_global_currency_code = nvl (g_secondary_currency_code, '---') AND
81 g_global_rate_type = nvl (g_secondary_rate_type, '---') ) THEN
82 l_pri_sec_curr_same := 1;
83 END IF;
84
85
86 --get list of organizations, for which cost/conv rate need to be collected.
87 BIS_COLLECTION_UTILITIES.put_line('Get list of organizations to be processed.');
88 l_stmt_num := 10;
89
90 select org1.organization_id, nvl(org2.existing_flag, 0), org1.from_date
91 bulk collect into l_org_list, l_existing_list, l_from_dates
92 from (select /*+ no_merge use_hash(sched,setup,plan,org) */
93 distinct org.organization_id,
94 sched.from_date
95 from isc_dbi_plan_organizations org,
96 isc_dbi_plans plan,
97 opi_dbi_baseline_schedules sched,
98 opi_dbi_baseline_plans setup
99 where sched.next_collection_date <= g_sysdate
100 and sched.schedule_type = 1
101 and sched.baseline_id = setup.baseline_id
102 and setup.plan_name = plan.compile_designator
103 and setup.owning_org_id = plan.organization_id
104 and org.plan_id = plan.plan_id
105 ) org1,
106 (select distinct organization_id,
107 1 existing_flag
108 from opi_dbi_ptp_conv
109 ) org2
110 where org1.organization_id = org2.organization_id (+)
111 ;
112
113 IF l_org_list.count <> 0 THEN
114 FOR i IN l_org_list.FIRST..l_org_list.LAST LOOP
115 IF l_existing_list(i) = 0 THEN
116 l_new_orgs := l_new_orgs || l_org_list(i) || ',';
117 ELSE
118 l_existing_orgs := l_existing_orgs || l_org_list(i) || ',';
119 END IF;
120 END LOOP;
121 END IF;
122
123 IF l_new_orgs IS NOT NULL THEN
124 l_new_orgs := '(' || substrb(l_new_orgs, 1, instrb(l_new_orgs, ',', -1, 1)-1) || ')';
125 END IF;
126
127 IF l_existing_orgs IS NOT NULL THEN
128 l_existing_orgs := '(' || substrb(l_existing_orgs, 1, instrb(l_existing_orgs, ',', -1, 1)-1) || ')';
129 END IF;
130
131 BIS_COLLECTION_UTILITIES.put_line('l_new_orgs=' || l_new_orgs);
132 BIS_COLLECTION_UTILITIES.put_line('l_existing_orgs=' || l_existing_orgs);
133
134 --collect cost for new organizations
135 --two rows inserted for each new item-org as:
136 -- row 1: from_date = global_start_date, to_date = baseline from_date, cost = 0
137 -- row 2: from_date = baseline from_date, to_date = null, cost = item cost from cst_item_costs
138 IF l_new_orgs IS NOT NULL THEN
139 BIS_COLLECTION_UTILITIES.put_line('Collect item cost for new organizations.');
140 l_stmt_num := 30;
141 l_stmt := ' insert into OPI_DBI_PTP_COST
142 (
143 FROZEN_FLAG,
144 ORGANIZATION_ID,
145 INVENTORY_ITEM_ID,
146 UNIT_COST,
147 FROM_DATE,
148 TO_DATE,
149 SOURCE,
150 CREATION_DATE,
151 CREATED_BY,
152 LAST_UPDATE_DATE,
153 LAST_UPDATED_BY,
154 LAST_UPDATE_LOGIN
155 )
156 select /*+ use_hash(msi,mp) index_ffs(msi,MTL_SYSTEM_ITEMS_B_U1) parallel_index(msi,MTL_SYSTEM_ITEMS_B_U1) */
157 null l_frozen_flag,
158 msi.organization_id,
159 msi.inventory_item_id,
160 0 item_cost,
161 :g_global_start_date FROM_DATE,
162 org_dates.from_date TO_DATE,
163 :l_source,
164 :g_sysdate,
165 :g_last_updated_by,
166 :g_sysdate,
167 :g_last_updated_by,
168 :g_last_update_login
169 from
170 mtl_parameters mp,
171 mtl_system_Items_b msi,
172 (select /*+ no_merge use_hash(sched,setup,plan,orgs) */
173 orgs.organization_id,
174 sched.from_date
175 from opi_dbi_baseline_schedules sched,
176 opi_dbi_baseline_plans setup,
177 isc_dbi_plans plan,
178 isc_dbi_plan_organizations orgs
179 where sched.next_collection_date <= :g_sysdate
180 and sched.schedule_type = 1
181 and sched.baseline_id = setup.baseline_id
182 and setup.plan_name = plan.compile_designator
183 and setup.owning_org_id = plan.organization_id
184 and plan.plan_id = orgs.plan_id
185 ) org_dates
186 where mp.organization_id in ' || l_new_orgs ||
187 ' AND mp.organization_id = org_dates.organization_id
188 AND mp.process_enabled_flag <> ''Y''
189 AND mp.organization_id = msi.organization_id
190 union all
191 select /*+ use_hash(cic) index(cic CST_ITEM_COSTS_U1) parallel(io) parallel(cic) */
192 :l_frozen_flag,
193 io.organization_id,
194 io.inventory_item_id,
195 nvl(cic.item_cost, 0) item_cost,
196 org_dates.from_date FROM_DATE,
197 null TO_DATE,
198 :l_source,
199 :g_sysdate,
200 :g_last_updated_by,
201 :g_sysdate,
202 :g_last_updated_by,
203 :g_last_update_login
204 from
205 cst_item_costs cic,
206 (select /*+ no_merge use_hash(msi) parallel(mp) index_ffs(msi,MTL_SYSTEM_ITEMS_B_U1) parallel_index(msi,MTL_SYSTEM_ITEMS_B_U1) */
207 msi.organization_id,
208 msi.inventory_item_id,
209 decode (mp.primary_cost_method, 1, 1, 2) cost_type_id
210 from
211 mtl_system_items_b msi,
212 mtl_parameters mp
213 where mp.organization_id in ' || l_new_orgs || '
214 AND mp.process_enabled_flag <> ''Y''
215 AND mp.organization_id = msi.organization_id
216 ) io,
217 (select /*+ no_merge use_hash(sched,setup,plan,orgs) */
218 orgs.organization_id,
219 sched.from_date
220 from opi_dbi_baseline_schedules sched,
221 opi_dbi_baseline_plans setup,
222 isc_dbi_plans plan,
223 isc_dbi_plan_organizations orgs
224 where sched.next_collection_date <= :g_sysdate
225 and sched.schedule_type = 1
226 and sched.baseline_id = setup.baseline_id
227 and setup.plan_name = plan.compile_designator
228 and setup.owning_org_id = plan.organization_id
229 and plan.plan_id = orgs.plan_id
230 ) org_dates
231 where io.organization_id = org_dates.organization_id
232 AND io.organization_id = cic.organization_id (+)
233 AND io.inventory_item_id = cic.inventory_item_id (+)
234 AND io.cost_type_id = cic.cost_type_id (+)
235 '
236 ;
237 EXECUTE IMMEDIATE l_stmt USING g_global_start_date, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate, -1, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate;
238 --get conversion rate for new organizations
239 -- added secondary currency support
240 BIS_COLLECTION_UTILITIES.put_line('Collect conversion rate new organizations.');
241 FOR i IN l_org_list.FIRST..l_org_list.LAST LOOP
242 IF l_existing_list(i) = 0 THEN
243 l_stmt_num := 40;
244 SELECT gsob.currency_code
245 INTO l_currency_code
246 FROM hr_organization_information hoi,
247 gl_sets_of_books gsob
248 WHERE
249 hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
250 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
251 AND hoi.organization_id = l_org_list(i)
252 AND rownum < 2;
253
254 l_stmt_num := 50;
255 IF sql%rowcount = 1 THEN
256
257 -- secondary currency support and conversion rate standards based
258 -- calls to FII API's
259 IF (l_currency_code = g_global_currency_code) THEN
260 l_rate := 1;
261 ELSE
262 SELECT fii_currency.get_global_rate_primary(l_currency_code, g_sysdate)
263 INTO l_rate
264 FROM dual;
265 END IF;
266
267 IF (l_currency_code = g_secondary_currency_code) THEN
268 l_secondary_rate := 1;
269 ELSIF (l_pri_sec_curr_same = 1) THEN
270 l_secondary_rate := l_rate;
271 ELSIF (g_secondary_currency_code IS NULL) THEN
272 l_secondary_rate := NULL;
273 ELSE
274 l_secondary_rate := fii_currency.get_global_rate_secondary
275 (l_currency_code, g_sysdate);
276 END IF;
277
278 -- The FII APIs behave as follows:
279 -- Return: rate (> 0) if one exists.
280 -- -1 if no rate exists for given day
281 -- -2 if the currency code is not recognized
282 -- -3 if Euro rate is missing prior to 01-JAN-1999.
283 If ( (l_rate >= 0) AND
284 ( (l_secondary_rate >= 0) OR
285 (l_secondary_rate IS NULL AND
286 g_secondary_currency_code IS NULL) ) ) THEN
287 l_stmt_num := 60;
288 l_stmt := '
289 insert into OPI_DBI_PTP_CONV
290 (
291 FROZEN_FLAG,
292 ORGANIZATION_ID,
293 CONVERSION_RATE,
294 SEC_CONVERSION_RATE,
295 CURRENCY_CODE,
296 FROM_DATE,
297 TO_DATE,
298 SOURCE,
299 CREATION_DATE,
300 CREATED_BY,
301 LAST_UPDATE_DATE,
302 LAST_UPDATED_BY,
303 LAST_UPDATE_LOGIN
304 )
305 VALUES (
306 null,
307 :l_org_id,
308 :l_rate,
309 :l_secondary_rate,
310 :l_currency_code,
311 :g_global_start_date,
312 :l_from_date,
313 :l_source,
314 :g_sysdate,
315 :g_last_upated_by,
316 :g_sysdate,
317 :g_last_updated_by,
318 :g_last_update_login
319 )
320 '
321 ;
322 EXECUTE IMMEDIATE l_stmt USING l_org_list(i), 0, 0, l_currency_code, g_global_start_date, l_from_dates(i), 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
323
324 l_stmt := '
325 insert into OPI_DBI_PTP_CONV
326 (
327 FROZEN_FLAG,
328 ORGANIZATION_ID,
329 CONVERSION_RATE,
330 SEC_CONVERSION_RATE,
331 CURRENCY_CODE,
332 FROM_DATE,
333 TO_DATE,
334 SOURCE,
335 CREATION_DATE,
336 CREATED_BY,
337 LAST_UPDATE_DATE,
338 LAST_UPDATED_BY,
339 LAST_UPDATE_LOGIN
340 )
341 VALUES (
342 :l_frozen_flag,
343 :l_org_id,
344 :l_rate,
345 :l_secondary_rate,
346 :l_currency_code,
347 :l_from_date,
348 null,
349 :l_source,
350 :g_sysdate,
351 :g_last_upated_by,
352 :g_sysdate,
353 :g_last_updated_by,
354 :g_last_update_login
355 )
356 '
357 ;
358 EXECUTE IMMEDIATE l_stmt USING -1, l_org_list(i), l_rate, l_secondary_rate, l_currency_code, l_from_dates(i), 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
359 ELSE
360 IF (l_missing_flag = 0) THEN
361 l_missing_flag := 1;
362 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
363 END IF;
364 IF (l_rate = -1) THEN
365 BIS_COLLECTION_UTILITIES.writeMissingRate(
366 g_global_rate_type,
367 l_currency_code,
368 g_global_currency_code,
369 g_sysdate
370 );
371 END IF;
372 IF (l_secondary_rate = -1) THEN
373 BIS_COLLECTION_UTILITIES.writeMissingRate(
374 g_global_rate_type,
375 l_currency_code,
376 g_secondary_currency_code,
377 g_sysdate
378 );
379 END IF;
380 END IF;
381 END IF;
382 END IF;
383 END LOOP;
384 END IF;
385
386 --get cost/conversion rate for existing organizations
387 IF l_existing_orgs IS NOT NULL THEN
388 BIS_COLLECTION_UTILITIES.put_line('Collect item cost for existing organizations.');
389 l_stmt_num := 70;
390 l_stmt := '
391 update OPI_DBI_PTP_COST
392 set frozen_flag = null,
393 to_date = :g_sysdate,
394 last_update_date = :g_sysdate,
395 last_updated_by = :g_last_updated_by,
396 last_update_login = :g_last_update_login,
397 source = -1
398 where frozen_flag = -1
399 and source = 1
400 and organization_id in ' || l_existing_orgs
401 ;
402
403 EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_sysdate, g_last_updated_by, g_last_update_login;
404 l_stmt_num := 80;
405 l_stmt :='
406 merge into OPI_DBI_PTP_COST cost
407 using
408 (
409 select /*+ use_hash(cic) parallel(io) parallel(cic) */
410 :l_frozen_flag frozen_flag,
411 io.organization_id,
412 io.inventory_item_id,
413 nvl(cic.item_cost, 0) item_cost,
414 :g_sysdate from_date,
415 null to_date,
416 :l_source source
417 from
418 cst_item_costs cic,
419 (select /*+ no_merge use_hash(msi) parallel(mp) index_ffs(msi,MTL_SYSTEM_ITEMS_B_U1) parallel_index(msi,MTL_SYSTEM_ITEMS_B_U1) */
420 msi.organization_id,
421 msi.inventory_item_id,
422 decode (mp.primary_cost_method, 1, 1, 2) cost_type_id
423 from
424 mtl_system_items_b msi,
425 mtl_parameters mp
426 where mp.organization_id in ' || l_existing_orgs || '
427 AND mp.process_enabled_flag <> ''Y''
428 AND mp.organization_id = msi.organization_id
429 ) io
430 where io.organization_id = cic.organization_id (+)
431 AND io.inventory_item_id = cic.inventory_item_id (+)
432 AND io.cost_type_id = cic.cost_type_id (+)
433 ) new_cost
434 on
435 ( cost.organization_id = new_cost.organization_id
436 and cost.inventory_item_id = new_cost.inventory_item_id
437 and cost.unit_cost = new_cost.item_cost
438 and cost.source = -1
439 )
440 when matched then
441 update set
442 cost.frozen_flag = -1,
443 cost.to_date = null
444 when not matched then
445 insert
446 (
447 FROZEN_FLAG,
448 ORGANIZATION_ID,
449 INVENTORY_ITEM_ID,
450 UNIT_COST,
451 FROM_DATE,
452 TO_DATE,
453 SOURCE,
454 CREATION_DATE,
455 CREATED_BY,
456 LAST_UPDATE_DATE,
457 LAST_UPDATED_BY,
458 LAST_UPDATE_LOGIN
459 )
460 values
461 (
462 new_cost.frozen_flag,
463 new_cost.organization_id,
464 new_cost.inventory_item_id,
465 new_cost.item_cost,
466 new_cost.from_date,
467 new_cost.to_date,
468 new_cost.source,
469 :g_sysdate,
470 :g_last_updated_by,
471 :g_sysdate,
472 :g_last_updated_by,
473 :g_last_update_login
474 )
475 ';
476 EXECUTE IMMEDIATE l_stmt USING -1, g_sysdate, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
477
478 --shift date back to global_start_date for new items
479 l_stmt :='
480 update OPI_DBI_PTP_COST
481 set from_date = :g_global_start_date
482 where (organization_id, inventory_item_id) in
483 (select organization_id, inventory_item_id from OPI_DBI_PTP_COST
484 where source = 1 and frozen_flag = -1 and creation_date = :g_sysdate
485 minus
486 select organization_id, inventory_item_id from OPI_DBI_PTP_COST
487 where source = -1
488 )
489 ';
490 EXECUTE IMMEDIATE l_stmt USING g_global_start_date, g_sysdate;
491
492 l_stmt_num := 90;
493 update opi_dbi_ptp_cost
494 set source = 1
495 where source = -1
496 ;
497
498 --get conversion rate for existing organizations
499 BIS_COLLECTION_UTILITIES.put_line('Collect conversion rate for existing organizations.');
500 l_stmt_num := 100;
501 l_stmt := '
502 update OPI_DBI_PTP_CONV
503 set frozen_flag = null,
504 to_date = :g_sysdate,
505 last_update_date = :g_sysdate,
506 last_updated_by = :g_last_updated_by,
507 last_update_login = :g_last_update_login,
508 source = -1
509 where organization_id in ' || l_existing_orgs ||
510 ' and frozen_flag = -1
511 and source = 1
512 ';
513
514 EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_sysdate, g_last_updated_by, g_last_update_login;
515
516 FOR i IN l_org_list.FIRST..l_org_list.LAST LOOP
517 IF l_existing_list(i) = 1 THEN
518 l_stmt_num := 110;
519 SELECT gsob.currency_code
520 INTO l_currency_code
521 FROM hr_organization_information hoi,
522 gl_sets_of_books gsob
523 WHERE
524 hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
525 AND hoi.org_information1 = to_char(gsob.set_of_books_id)
526 AND hoi.organization_id = l_org_list(i)
527 AND rownum < 2;
528
529 l_stmt_num := 120;
530 IF sql%rowcount = 1 THEN
531
532 -- secondary currency support and conversion rate standards based
533 -- calls to FII API's
534 IF (l_currency_code = g_global_currency_code) THEN
535 l_rate := 1;
536 ELSE
537 SELECT fii_currency.get_global_rate_primary(l_currency_code, g_sysdate)
538 INTO l_rate
539 FROM dual;
540 END IF;
541
542 IF (l_currency_code = g_secondary_currency_code) THEN
543 l_secondary_rate := 1;
544 ELSIF (l_pri_sec_curr_same = 1) THEN
545 l_secondary_rate := l_rate;
546 ELSIF (g_secondary_currency_code IS NULL) THEN
547 l_secondary_rate := NULL;
548 ELSE
549 l_secondary_rate := fii_currency.get_global_rate_secondary
550 (l_currency_code, g_sysdate);
551 END IF;
552
553 l_stmt_num := 130;
554 -- The FII APIs behave as follows:
555 -- Return: rate (> 0) if one exists.
556 -- -1 if no rate exists for given day
557 -- -2 if the currency code is not recognized
558 -- -3 if Euro rate is missing prior to 01-JAN-1999.
559 IF ( (l_rate >= 0) AND
560 ( (l_secondary_rate >= 0) OR
561 (l_secondary_rate IS NULL AND
562 g_secondary_currency_code IS NULL) ) ) THEN
563
564 l_stmt := '
565 merge into OPI_DBI_PTP_CONV conv
566 using
567 (
568 select
569 :l_org_id organization_id,
570 :l_rate conversion_rate,
571 :l_secondary_rate sec_conversion_rate,
572 :g_sysdate from_date,
573 null to_date
574 from dual
575 ) new_conv
576 on
577 ( conv.organization_id = new_conv.organization_id
578 and conv.conversion_rate = new_conv.conversion_rate
579 and nvl (conv.sec_conversion_rate, -9999) = nvl (new_conv.sec_conversion_rate, -9999)
580 and conv.source = -1
581 )
582 when matched then
583 update set
584 conv.frozen_flag = -1,
585 conv.to_date = null
586 when not matched then
587 insert (
588 FROZEN_FLAG,
589 ORGANIZATION_ID,
590 CONVERSION_RATE,
591 SEC_CONVERSION_RATE,
592 CURRENCY_CODE,
593 FROM_DATE,
594 TO_DATE,
595 SOURCE,
596 CREATION_DATE,
597 CREATED_BY,
598 LAST_UPDATE_DATE,
599 LAST_UPDATED_BY,
600 LAST_UPDATE_LOGIN
601 )
602 values
603 (
604 :l_frozen_flag,
605 new_conv.organization_id,
606 new_conv.conversion_rate,
607 new_conv.sec_conversion_rate,
608 :l_currency_code,
609 new_conv.from_date,
610 new_conv.to_date,
611 :l_source,
612 :g_sysdate,
613 :g_last_upated_by,
614 :g_sysdate,
615 :g_last_updated_by,
616 :g_last_update_login
617 )
618 ';
619 EXECUTE IMMEDIATE l_stmt USING l_org_list(i), l_rate, l_secondary_rate, g_sysdate, -1, l_currency_code, 1, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
620 ELSE
621 IF (l_missing_flag = 0) THEN
622 l_missing_flag := 1;
623 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
624 END IF;
625 IF (l_rate = -1) THEN
626 BIS_COLLECTION_UTILITIES.writeMissingRate(
627 g_global_rate_type,
628 l_currency_code,
629 g_global_currency_code,
630 g_sysdate
631 );
632 END IF;
633 IF (l_secondary_rate = -1) THEN
634 BIS_COLLECTION_UTILITIES.writeMissingRate(
635 g_global_rate_type,
636 l_currency_code,
637 g_secondary_currency_code,
638 g_sysdate
639 );
640 END IF;
641 END IF;
642 END IF;
643 END IF;
644 END LOOP;
645
646 l_stmt_num := 140;
647 update OPI_DBI_PTP_CONV
648 set source = 1
649 where source = -1
650 ;
651 END IF;
652
653 IF l_missing_flag = 1 THEN
654 errbuf := 'Exit because of missing currency rate.';
655 RAISE cost_conversion_rate_exception;
656 END IF;
657
658 EXCEPTION
659 WHEN cost_conversion_rate_exception THEN
660 BIS_COLLECTION_UTILITIES.put_line('There are missing currency rate. Program stops. Please check output file for more details.');
661 retcode := g_error;
662 RAISE cost_conversion_rate_exception;
663 WHEN OTHERS THEN
664 BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost/conversion rate for discrete manufacturing organizations.');
665 BIS_COLLECTION_UTILITIES.put_line('Error out at stmt_num ' || l_stmt_num || ' in Get_Discrete_Cost_and_Rate.');
666 retcode := g_error;
667 errbuf := SQLERRM;
668 RAISE cost_conversion_rate_exception;
669 END Get_Discrete_Cost_and_Rate;
670
671 PROCEDURE Get_Process_Cost
672 (
673 errbuf OUT NOCOPY VARCHAR2,
674 retcode OUT NOCOPY VARCHAR2
675 )
676 IS
677 TYPE NUMBERLIST is TABLE of NUMBER;
678 l_org_list NUMBERLIST := NUMBERLIST();
679 l_existing_list NUMBERLIST := NUMBERLIST();
680 l_new_orgs VARCHAR2(1024) := NULL;
681 l_existing_orgs VARCHAR2(1024) := NULL;
682 TYPE DATELIST is TABLE of DATE;
683 l_from_dates DATELIST := DATELIST();
684 l_stmt VARCHAR2(10240);
685 l_missing_flag NUMBER := 0;
686 l_stmt_num NUMBER := 0;
687 BEGIN
688 --get list of organizations, for which cost need to be collected.
689 BIS_COLLECTION_UTILITIES.put_line('Get list of Process-enabled organizations to be processed.');
690 l_stmt_num := 10;
691
692 -- following statement is same as for discrete, except limiting organizations to process-enabled
693
694 select org1.organization_id, nvl(org2.existing_flag, 0), org1.from_date
695 bulk collect into l_org_list, l_existing_list, l_from_dates
696 from (select /*+ no_merge use_hash(sched,setup,plan,org) */
697 distinct org.organization_id,
698 sched.from_date
699 from isc_dbi_plan_organizations org,
700 isc_dbi_plans plan,
701 opi_dbi_baseline_schedules sched,
702 opi_dbi_baseline_plans setup
703 where sched.next_collection_date <= g_sysdate
704 and sched.schedule_type = 1
705 and sched.baseline_id = setup.baseline_id
706 and setup.plan_name = plan.compile_designator
707 and setup.owning_org_id = plan.organization_id
708 and org.plan_id = plan.plan_id
709 ) org1,
710 (select distinct organization_id,
711 1 existing_flag
712 from opi_dbi_ptp_conv
713 ) org2,
714 mtl_parameters mp
715 where org1.organization_id = org2.organization_id (+)
716 and org1.organization_id = mp.organization_id
717 and mp.process_enabled_flag = 'Y';
718
719 BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' orgs identified for cost determination.');
720
721 IF l_org_list.count <> 0 THEN
722 FOR i IN l_org_list.FIRST..l_org_list.LAST LOOP
723 IF l_existing_list(i) = 0 THEN
724 l_new_orgs := l_new_orgs || l_org_list(i) || ',';
725 ELSE
726 l_existing_orgs := l_existing_orgs || l_org_list(i) || ',';
727 END IF;
728 END LOOP;
729 END IF;
730
731 IF l_new_orgs IS NOT NULL THEN
732 l_new_orgs := '(' || substrb(l_new_orgs, 1, instrb(l_new_orgs, ',', -1, 1)-1) || ')';
733 BIS_COLLECTION_UTILITIES.put_line('Collect item cost for new Process-Enabled organizations.');
734 l_stmt_num := 30;
735 l_stmt := 'INSERT INTO opi_pmi_cost_param_gtmp
736 (
737 item_id,
738 whse_code,
739 orgn_code,
740 trans_date
741 )
742 SELECT
743 i.item_id,
744 w.whse_code,
745 w.orgn_code,
746 SYSDATE trans_date
747 FROM
748 ic_item_mst_b i,
749 ic_whse_mst w
750 WHERE
751 w.mtl_organization_id IN ' || l_new_orgs;
752
753 EXECUTE IMMEDIATE l_stmt;
754
755 BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' org items identified for costing.');
756
757 opi_pmi_cost.get_cost;
758
759 END IF;
760
761 IF l_existing_orgs IS NOT NULL THEN
762 l_existing_orgs := '(' || substrb(l_existing_orgs, 1, instrb(l_existing_orgs, ',', -1, 1)-1) || ')';
763 BIS_COLLECTION_UTILITIES.put_line('Adding item cost for new Process-Enabled organizations...');
764 l_stmt_num := 35;
765 l_stmt := 'INSERT INTO opi_pmi_cost_param_gtmp
766 (
767 item_id,
768 whse_code,
769 orgn_code,
770 trans_date
771 )
772 SELECT
773 i.item_id,
774 w.whse_code,
775 w.orgn_code,
776 SYSDATE trans_date
777 FROM
778 ic_item_mst_b i,
779 ic_whse_mst w
780 WHERE
781 w.mtl_organization_id IN ' || l_existing_orgs;
782
783 EXECUTE IMMEDIATE l_stmt;
784
785 BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' org items identified for costing.');
786
787 opi_pmi_cost.get_cost;
788
789 END IF;
790
791 BIS_COLLECTION_UTILITIES.put_line('l_new_orgs=' || l_new_orgs);
792 BIS_COLLECTION_UTILITIES.put_line('l_existing_orgs=' || l_existing_orgs);
793
794 --collect cost for new organizations
795 --two rows inserted for each new item-org as:
796 -- row 1: from_date = global_start_date, to_date = baseline from_date, cost = 0
797 -- row 2: from_date = baseline from_date, to_date = null, cost = item cost from cst_item_costs
798
799
800 IF l_new_orgs IS NOT NULL THEN
801
802 l_stmt := ' insert into OPI_DBI_PTP_COST
803 (
804 FROZEN_FLAG,
805 ORGANIZATION_ID,
806 INVENTORY_ITEM_ID,
807 UNIT_COST,
808 FROM_DATE,
809 TO_DATE,
810 SOURCE,
811 CREATION_DATE,
812 CREATED_BY,
813 LAST_UPDATE_DATE,
814 LAST_UPDATED_BY,
815 LAST_UPDATE_LOGIN
816 )
817 select
818 null l_frozen_flag,
819 msi.organization_id,
820 msi.inventory_item_id,
821 0 item_cost,
822 :g_global_start_date FROM_DATE,
823 org_dates.from_date TO_DATE,
824 :l_source,
825 :g_sysdate,
826 :g_last_updated_by,
827 :g_sysdate,
828 :g_last_updated_by,
829 :g_last_update_login
830 from
831 mtl_parameters mp,
832 mtl_system_Items_b msi,
833 (select orgs.organization_id,
834 sched.from_date
835 from opi_dbi_baseline_schedules sched,
836 opi_dbi_baseline_plans setup,
837 isc_dbi_plans plan,
838 isc_dbi_plan_organizations orgs
839 where sched.next_collection_date <= :g_sysdate
840 and sched.schedule_type = 1
841 and sched.baseline_id = setup.baseline_id
842 and setup.plan_name = plan.compile_designator
843 and setup.owning_org_id = plan.organization_id
844 and plan.plan_id = orgs.plan_id
845 ) org_dates
846 where mp.organization_id in ' || l_new_orgs ||
847 ' AND mp.organization_id = org_dates.organization_id
848 AND mp.process_enabled_flag = ''Y''
849 AND mp.organization_id = msi.organization_id
850 union all
851 select
852 :l_frozen_flag,
853 msi.organization_id,
854 msi.inventory_item_id,
855 nvl(cst.total_cost, 0) item_cost,
856 org_dates.from_date FROM_DATE,
857 null TO_DATE,
858 :l_source,
859 :g_sysdate,
860 :g_last_updated_by,
861 :g_sysdate,
862 :g_last_updated_by,
863 :g_last_update_login
864 from
865 (select orgs.organization_id,
866 sched.from_date
867 from opi_dbi_baseline_schedules sched,
868 opi_dbi_baseline_plans setup,
869 isc_dbi_plans plan,
870 isc_dbi_plan_organizations orgs
871 where sched.next_collection_date <= :g_sysdate
872 and sched.schedule_type = 1
873 and sched.baseline_id = setup.baseline_id
874 and setup.plan_name = plan.compile_designator
875 and setup.owning_org_id = plan.organization_id
876 and plan.plan_id = orgs.plan_id
877 ) org_dates,
878 ic_whse_mst w,
879 mtl_system_items_b msi,
880 ic_item_mst_b i,
881 opi_pmi_cost_result_gtmp cst
882 where
883 w.mtl_organization_id = org_dates.organization_id
884 AND msi.organization_id = w.mtl_organization_id
885 AND i.item_no = msi.segment1
886 AND i.item_id = cst.item_id
887 AND w.whse_code = cst.whse_code
888 '
889 ;
890 EXECUTE IMMEDIATE l_stmt USING g_global_start_date, 2, g_sysdate, g_last_updated_by, g_sysdate,
891 g_last_updated_by, g_last_update_login, g_sysdate, -1, 2,
892 g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by,
893 g_last_update_login, g_sysdate;
894
895 BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' costs collected for new Process orgs.');
896
897 END IF; -- l_new_orgs IS NOT NULL
898
899 --get cost/conversion rate for existing organizations
900 IF l_existing_orgs IS NOT NULL THEN
901 BIS_COLLECTION_UTILITIES.put_line('Collect item cost for existing organizations.');
902 l_stmt_num := 70;
903 l_stmt := '
904 update OPI_DBI_PTP_COST
905 set frozen_flag = null,
906 to_date = :g_sysdate,
907 last_update_date = :g_sysdate,
908 last_updated_by = :g_last_updated_by,
909 last_update_login = :g_last_update_login,
910 source = -2
911 where frozen_flag = -1
912 and source = 2
913 and organization_id in ' || l_existing_orgs
914 ;
915
916 EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_sysdate, g_last_updated_by, g_last_update_login;
917
918 l_stmt_num := 80;
919 l_stmt :='
920 merge into OPI_DBI_PTP_COST cost
921 using
922 (
923 select
924 :l_frozen_flag frozen_flag,
925 msi.organization_id,
926 msi.inventory_item_id,
927 nvl(cst.total_cost, 0) item_cost,
928 :g_sysdate from_date,
929 null to_date,
930 :l_source source
931 from
932 opi_pmi_cost_result_gtmp cst,
933 ic_whse_mst w,
934 ic_item_mst_b i,
935 mtl_system_items_b msi
936 where
937 w.mtl_organization_id IN ' || l_existing_orgs || '
938 AND w.whse_code = cst.whse_code
939 AND i.item_id = cst.item_id
940 AND msi.segment1 = i.item_no
941 AND msi.organization_id = w.mtl_organization_id
942 ) new_cost
943 on
944 ( cost.organization_id = new_cost.organization_id
945 and cost.inventory_item_id = new_cost.inventory_item_id
946 and cost.unit_cost = new_cost.item_cost
947 and cost.source = -1
948 )
949 when matched then
950 update set
951 cost.frozen_flag = -1,
952 cost.to_date = null
953 when not matched then
954 insert
955 (
956 FROZEN_FLAG,
957 ORGANIZATION_ID,
958 INVENTORY_ITEM_ID,
959 UNIT_COST,
960 FROM_DATE,
961 TO_DATE,
962 SOURCE,
963 CREATION_DATE,
964 CREATED_BY,
965 LAST_UPDATE_DATE,
966 LAST_UPDATED_BY,
967 LAST_UPDATE_LOGIN
968 )
969 values
970 (
971 new_cost.frozen_flag,
972 new_cost.organization_id,
973 new_cost.inventory_item_id,
974 new_cost.item_cost,
975 new_cost.from_date,
976 new_cost.to_date,
977 new_cost.source,
978 :g_sysdate,
979 :g_last_updated_by,
980 :g_sysdate,
981 :g_last_updated_by,
982 :g_last_update_login
983 )
984 ';
985 EXECUTE IMMEDIATE l_stmt USING -1, g_sysdate, 2, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login;
986
987 BIS_COLLECTION_UTILITIES.put_line(TO_CHAR(SQL%ROWCOUNT) || ' Process Org costs merged into costing fact.');
988
989 --shift date back to global_start_date for new items
990 l_stmt :='
991 update OPI_DBI_PTP_COST
992 set from_date = :g_global_start_date
993 where (organization_id, inventory_item_id) in
994 (select organization_id, inventory_item_id from OPI_DBI_PTP_COST
995 where source = 2 and frozen_flag = -1 and creation_date = :g_sysdate
996 minus
997 select organization_id, inventory_item_id from OPI_DBI_PTP_COST
998 where source = -2
999 )
1000 ';
1001
1002 EXECUTE IMMEDIATE l_stmt USING g_global_start_date, g_sysdate;
1003
1004 l_stmt_num := 90;
1005 update opi_dbi_ptp_cost
1006 set source = 2
1007 where source = -2
1008 ;
1009
1010 END IF; -- l_existing_orgs IS NOT NULL
1011
1012 EXCEPTION
1013 WHEN OTHERS THEN
1014 BIS_COLLECTION_UTILITIES.put_line('Fail to collect cost for Process Manufacturing organizations.');
1015 BIS_COLLECTION_UTILITIES.put_line('Error out at stmt_num ' || l_stmt_num || ' in Get_Process_Cost');
1016 retcode := g_error;
1017 errbuf := SQLERRM;
1018 RAISE cost_conversion_rate_exception;
1019
1020 END Get_Process_Cost;
1021
1022 PROCEDURE Extract_Baseline
1023 (
1024 errbuf IN OUT NOCOPY VARCHAR2,
1025 retcode IN OUT NOCOPY VARCHAR2
1026 )
1027 IS
1028 l_segment_num NUMBER;
1029 l_refresh NUMBER;
1030 l_list dbms_sql.varchar2_table;
1031 l_status VARCHAR2(30);
1032 l_industry VARCHAR2(30);
1033 l_from_date DATE;
1034 l_has_missing_date BOOLEAN;
1035 l_isc_return_code NUMBER;
1036 TYPE STRINGLIST is table of VARCHAR2(255);
1037 l_strings STRINGLIST := STRINGLIST();
1038 l_create_tables STRINGLIST := STRINGLIST();
1039 l_insert_tables STRINGLIST := STRINGLIST();
1040 l_add_partitions STRINGLIST := STRINGLIST();
1041 l_swap_partitions STRINGLIST := STRINGLIST();
1042 l_drop_tables STRINGLIST := STRINGLIST();
1043 TYPE DATELIST is table of DATE;
1044 l_collected_dates DATELIST := DATELIST();
1045 TYPE NUMBERLIST is table of NUMBER;
1046 l_baseline_ids NUMBERLIST := NUMBERLIST();
1047 l_stmt VARCHAR2(10240);
1048 l_count NUMBER;
1049 l_delete NUMBER;
1050 l_archive NUMBER;
1051 cursor l_baseline_info is
1052 select def.baseline_id,
1053 def.data_start_date,
1054 sched.from_date
1055 from OPI_DBI_BASELINE_DEFINITIONS def,
1056 OPI_DBI_BASELINE_SCHEDULES sched
1057 where def.baseline_id = sched.baseline_id
1058 and sched.next_collection_date <= trunc(sysdate)
1059 and sched.schedule_type = 1
1060 ;
1061 l_baseline_record l_baseline_info%ROWTYPE;
1062 BEGIN
1063 --setup
1064 BIS_COLLECTION_UTILITIES.put_line('Start baseline collection.');
1065 l_segment_num := 10;
1066 /* we don't truncate table here at first run. purge/deletion should be provided separately*/
1067
1068 IF BIS_COLLECTION_UTILITIES.SETUP(
1069 p_object_name => 'OPI_DBI_PTP_PLAN_F'
1070 ) = false then
1071 BIS_COLLECTION_UTILITIES.put_line('Fail to initialize through BIS_COLLECTION_UTILITIES.SETUP.');
1072 retcode := g_error;
1073 errbuf := 'Program stops.';
1074 RAISE intialization_exception;
1075 End if;
1076
1077 l_segment_num := 20;
1078 BIS_COLLECTION_UTILITIES.put_line('Check global variables.');
1079 l_list(1) := 'BIS_GLOBAL_START_DATE';
1080 l_list(2) := 'BIS_PRIMARY_CURRENCY_CODE';
1081 l_list(3) := 'BIS_PRIMARY_RATE_TYPE';
1082
1083 IF (NOT BIS_COMMON_PARAMETERS.CHECK_GLOBAL_PARAMETERS(l_list)) THEN
1084 BIS_COLLECTION_UTILITIES.put_line('Missing global parameters. Please setup global_start_date and primary_currency_code first.');
1085 retcode := g_error;
1086 errbuf := 'Program stops.';
1087 RAISE intialization_exception;
1088 END IF;
1089
1090 --initialize global variables
1091 BIS_COLLECTION_UTILITIES.put_line('Initialize global variables.');
1092 l_segment_num := 30;
1093 BEGIN
1094 g_sysdate := trunc(sysdate);
1095 g_created_by := nvl(fnd_global.user_id, -1);
1096 g_last_update_login := nvl(fnd_global.login_id, -1);
1097 g_last_updated_by := nvl(fnd_global.user_id, -1);
1098 SELECT BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE INTO g_global_start_date FROM DUAL;
1099 SELECT bis_common_parameters.get_currency_code INTO g_global_currency_code FROM dual;
1100 IF g_global_currency_code IS NULL THEN
1101 RAISE intialization_exception;
1102 END IF;
1103
1104 IF NOT fnd_installation.get_app_info( 'OPI', l_status, l_industry, g_opi_schema) THEN
1105 RAISE intialization_exception;
1106 END IF;
1107 g_degree := bis_common_parameters.get_degree_of_parallelism;
1108 BIS_COLLECTION_UTILITIES.put_line('global_start_date = ' || TO_CHAR(g_global_start_date, 'DD-MON-YYYY') || '.');
1109 g_global_rate_type := bis_common_parameters.get_rate_type;
1110 BIS_COLLECTION_UTILITIES.put_line('The primary rate type is ' || g_global_rate_type);
1111
1112 -- secondary currency support
1113 g_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
1114 g_secondary_currency_code :=
1115 bis_common_parameters.get_secondary_currency_code;
1116
1117 -- check that either both the secondary rate type and secondary
1118 -- rate are null, or that neither are null.
1119 IF ( (g_secondary_currency_code IS NULL AND
1120 g_secondary_rate_type IS NOT NULL)
1121 OR (g_secondary_currency_code IS NOT NULL AND
1122 g_secondary_rate_type IS NULL) ) THEN
1123
1124 BIS_COLLECTION_UTILITIES.PUT_LINE ('The global secondary currency code setup is incorrect. The secondary currency code cannot be null when the secondary rate type is defined and vice versa.');
1125
1126 RAISE intialization_exception;
1127
1128 END IF;
1129
1130
1131 EXCEPTION
1132 WHEN others THEN
1133 BIS_COLLECTION_UTILITIES.put_line('Fail to initialize global variable values. Please re-run the concurrent request set.');
1134 retcode := g_error;
1135 errbuf := SQLERRM;
1136 RAISE intialization_exception;
1137 END;
1138
1139 --if fail to refresh OPI_PTP_SUM_STG_MV last time, try to refresh it again
1140 BEGIN
1141 select stop_reason_code into l_refresh from opi_dbi_run_log_curr where etl_id = 7 and source = 1;
1142 IF l_refresh = 1 THEN
1143 BIS_COLLECTION_UTILITIES.put_line('re-synchronize OPI_PTP_SUM_STG_MV with OPI_DBI_PTP_PLAN_STG.');
1144 EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY';
1145 DBMS_MVIEW.REFRESH('OPI_PTP_SUM_STG_MV','?',parallelism => g_degree);
1146 EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL QUERY';
1147 END IF;
1148 EXCEPTION
1149 WHEN NO_DATA_FOUND THEN
1150 insert into opi_dbi_run_log_curr
1151 (organization_id,
1152 source,
1153 last_collection_date,
1154 start_txn_id,
1155 next_start_txn_id,
1156 etl_id,
1157 stop_reason_code,
1158 last_transaction_date,
1159 last_update_date,
1160 last_updated_by,
1161 creation_date,
1162 created_by,
1163 last_update_login)
1164 values
1165 (null, 1, null, null, null, 7, 2, null, g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login);
1166 commit;
1167 WHEN OTHERS THEN
1168 BIS_COLLECTION_UTILITIES.put_line('Fail to refresh materialized view OPI_PTP_SUM_STG_MV. Please fix the problem and re-run the concurrent request set.');
1169 retcode := g_error;
1170 errbuf := SQLERRM;
1171 RAISE intialization_exception;
1172 END;
1173
1174 --check missing date in time dimension
1175 --some logic here is commented out, because ISC APS extraction has checked dangling key against time dimension.
1176 --BIS_COLLECTION_UTILITIES.put_line('Check missing date against time dimension.');
1177 BIS_COLLECTION_UTILITIES.put_line('Check scheduled baseline collections.');
1178 l_segment_num := 40;
1179 BEGIN
1180 select min(from_date)
1181 into l_from_date
1182 from
1183 (
1184 select sched.baseline_id,
1185 sched.from_date
1186 from OPI_DBI_BASELINE_SCHEDULES sched,
1187 OPI_DBI_BASELINE_PLANS setup
1188 where sched.next_collection_date <= g_sysdate
1189 and sched.schedule_type = 1
1190 and sched.baseline_id = setup.baseline_id
1191 ) boundary
1192 ;
1193 EXCEPTION
1194 WHEN others THEN
1195 BIS_COLLECTION_UTILITIES.put_line('Fail to retreive following collection information: minimum from_date and maximum to_date.');
1196 retcode := g_error;
1197 errbuf := SQLERRM;
1198 RAISE intialization_exception;
1199 END;
1200
1201 /*
1202 l_from_date = null, meaning that there is no scheduled collection to be processed. just simply exit.
1203 */
1204 IF l_from_date is NULL THEN
1205 BIS_COLLECTION_UTILITIES.put_line('There is no scheduled collection. Program exits normally.');
1206 BIS_COLLECTION_UTILITIES.WRAPUP(
1207 p_status => TRUE,
1208 p_count => 0,
1209 p_message => 'There is no scheduled collection.'
1210 );
1211 retcode := g_ok;
1212 return;
1213 END IF;
1214
1215 --change per bug 3422479
1216 --call ISC APS collection program to get latest APS data
1217 BIS_COLLECTION_UTILITIES.put_line('Collect latest APS snapshot.');
1218 l_segment_num := 65;
1219 l_isc_return_code := ISC_DBI_MSC_OBJECTS_C.LOAD_BASES;
1220 IF (l_isc_return_code <> 1 ) THEN
1221 --BIS_COLLECTION_UTILITIES.put_line('Fail to collect latest APS snapshot.');
1222 retcode := g_error;
1223 errbuf := 'Fail to collect latest APS snapshot.';
1224 RAISE isc_collection_exception;
1225 END IF;
1226
1227 --validate information against baseline setup
1228 --Check if one organization exists in more than one baseline as scheduled
1229 BIS_COLLECTION_UTILITIES.put_line('Check if one organization exists in more than one baselines.');
1230 l_segment_num := 50;
1231 select count(*)
1232 into l_count
1233 from
1234 (
1235 select orgs.organization_id, count(orgs.plan_id) num_of_plans
1236 from OPI_DBI_BASELINE_SCHEDULES sched,
1237 OPI_DBI_BASELINE_PLANS setup,
1238 ISC_DBI_PLANS plan,
1239 ISC_DBI_PLAN_ORGANIZATIONS orgs
1240 where sched.next_collection_date <= g_sysdate
1241 and sched.schedule_type = 1
1242 and sched.baseline_id = setup.baseline_id
1243 and setup.plan_name = plan.compile_designator
1244 and setup.owning_org_id = plan.organization_id
1245 and plan.plan_id = orgs.plan_id
1246 group by orgs.organization_id
1247 ) org_plan
1248 where num_of_plans > 1
1249 ;
1250
1251 IF l_count > 0 THEN
1252 BIS_COLLECTION_UTILITIES.put_line('There are organizations existing in more than one baseline, which is not allowed. Please verify baseline setup.');
1253 retcode := g_error;
1254 errbuf := 'Organizations exist in more than one baseline.';
1255 RAISE collection_parameter_exception;
1256 END IF;
1257
1258 --Check from_date against plan run date
1259 BIS_COLLECTION_UTILITIES.put_line('Check from_date against plan run date.');
1260 BEGIN
1261 select count(*)
1262 into l_count
1263 from OPI_DBI_BASELINE_SCHEDULES sched,
1264 OPI_DBI_BASELINE_PLANS setup,
1265 ISC_DBI_PLANS plan
1266 where sched.baseline_id = setup.baseline_id
1267 and setup.plan_name = plan.compile_designator
1268 and setup.owning_org_id = plan.organization_id
1269 and sched.from_date < trunc(plan.data_start_date)
1270 and sched.next_collection_date <= g_sysdate
1271 and sched.schedule_type = 1
1272 ;
1273 EXCEPTION
1274 WHEN others THEN
1275 retcode := g_error;
1276 errbuf := SQLERRM;
1277 RAISE collection_parameter_exception;
1278 END;
1279
1280 IF l_count <> 0 THEN
1281 BIS_COLLECTION_UTILITIES.put_line('Some APS plans have plan run date post to associated baseline collection from_date. Please reset the from_date.');
1282 BIS_COLLECTION_UTILITIES.put_line(RPAD('BASELINE NAME', 20, ' ') || ' ' ||
1283 RPAD('PLAN NAME', 20, ' ') || ' ' ||
1284 RPAD('FROM DATE', 20, ' ') || ' ' ||
1285 RPAD('PLAN RUN DATE', 20, ' ')
1286 );
1287 BIS_COLLECTION_UTILITIES.put_line(RPAD('-', 20, '-') || ' ' ||
1288 RPAD('-', 20, '-') || ' ' ||
1289 RPAD('-', 20, '-') || ' ' ||
1290 RPAD('-', 20, '-')
1291 );
1292 select RPAD(def.baseline_name, 20, ' ') || ' ' ||
1293 RPAD(setup.plan_name, 20, ' ') || ' ' ||
1294 RPAD(TO_CHAR(sched.from_date, 'DD-MON-YYYY'), 20, ' ') || ' ' ||
1295 RPAD(TO_CHAR(plan.data_start_date, 'DD-MON-YYYY'), 20, ' ')
1296 bulk collect into l_strings
1297 from OPI_DBI_BASELINE_DEFINITIONS def,
1298 OPI_DBI_BASELINE_SCHEDULES sched,
1299 OPI_DBI_BASELINE_PLANS setup,
1300 ISC_DBI_PLANS plan
1301 where def.baseline_id = sched.baseline_id
1302 and sched.baseline_id = setup.baseline_id
1303 and setup.plan_name = plan.compile_designator
1304 and setup.owning_org_id = plan.organization_id
1305 and sched.next_collection_date <= g_sysdate
1306 and sched.schedule_type = 1
1307 ;
1308
1309 FOR i IN l_strings.FIRST..l_strings.LAST LOOP
1310 BIS_COLLECTION_UTILITIES.put_line(l_strings(i));
1311 END LOOP;
1312 retcode := g_error;
1313 errbuf := 'Program stops.';
1314 RAISE collection_parameter_exception;
1315 END IF;
1316
1317 --lock schedules to be processed
1318 BIS_COLLECTION_UTILITIES.put_line('Lock rows of schedules to be processed.');
1319 l_segment_num := 60;
1320 lock table opi_dbi_baseline_definitions in exclusive mode;
1321
1322
1323 --start loading new data
1324 BIS_COLLECTION_UTILITIES.put_line('Start loading new data');
1325 l_segment_num := 70;
1326 BEGIN
1327 select def.baseline_name, sched.baseline_id, def.last_collected_date
1328 bulk collect into l_strings, l_baseline_ids, l_collected_dates
1329 from OPI_DBI_BASELINE_SCHEDULES sched,
1330 OPI_DBI_BASELINE_DEFINITIONS def
1331 where sched.next_collection_date <= g_sysdate
1332 and sched.schedule_type = 1
1333 and def.baseline_id = sched.baseline_id
1334 ;
1335 EXCEPTION
1336 WHEN others THEN
1337 BIS_COLLECTION_UTILITIES.put_line('Fail to retreive baseline information.');
1338 retcode := g_error;
1339 errbuf := SQLERRM;
1340 RAISE load_exception;
1341 END;
1342
1343 --prepare statements
1344 BIS_COLLECTION_UTILITIES.put_line('Prepare SQL statements.');
1345 l_segment_num := 80;
1346 IF l_baseline_ids.count <> 0 THEN
1347 l_create_tables.extend(l_baseline_ids.count);
1348 l_insert_tables.extend(l_baseline_ids.count);
1349 l_add_partitions.extend(l_baseline_ids.count);
1350 l_swap_partitions.extend(l_baseline_ids.count);
1351 l_drop_tables.extend(l_baseline_ids.count);
1352 FOR i IN l_baseline_ids.FIRST..l_baseline_ids.LAST LOOP
1353 --prepare create table stmt
1354 l_create_tables(i) := 'CREATE TABLE OPI_DBI_PTP_TMP_' || l_baseline_ids(i);
1355 --prepare insert table stmt
1356 l_insert_tables(i) := 'WHEN BASELINE_ID = ''' || l_baseline_ids(i) || ''' THEN INTO OPI_DBI_PTP_TMP_' || l_baseline_ids(i);
1357 --prepare add partition stmt
1358 IF l_collected_dates(i) IS NULL THEN
1359 l_add_partitions(i) := 'ALTER TABLE '|| g_opi_schema || '.OPI_DBI_PTP_PLAN_STG ADD PARTITION BASELINE_' || l_baseline_ids(i) || ' VALUES(' || l_baseline_ids(i) ||')';
1360 END IF;
1361 --prepare swap partition stmt
1362 l_swap_partitions(i) := 'ALTER TABLE '|| g_opi_schema || '.OPI_DBI_PTP_PLAN_STG EXCHANGE PARTITION BASELINE_' || l_baseline_ids(i) || ' WITH TABLE OPI_DBI_PTP_TMP_' || l_baseline_ids(i) || ' including indexes without validation';
1363 --prepare drop table stmt
1364 l_drop_tables(i) := 'DROP TABLE OPI_DBI_PTP_TMP_' || l_baseline_ids(i);
1365 END LOOP;
1366 END IF;
1367
1368 BIS_COLLECTION_UTILITIES.put_line('Create temporary regular tables.');
1369 l_segment_num := 90;
1370 IF l_create_tables.count <> 0 THEN
1371 FOR i IN l_create_tables.FIRST..l_create_tables.LAST LOOP
1372 l_stmt := l_create_tables(i) || ' (' ||
1373 'ORGANIZATION_ID NUMBER NOT NULL, ' ||
1374 'BASELINE_ID NUMBER NOT NULL, ' ||
1375 'PLAN_NAME VARCHAR2(10) NOT NULL, ' ||
1376 'OWNING_ORG_ID NUMBER NOT NULL, ' ||
1377 'INVENTORY_ITEM_ID NUMBER NOT NULL, ' ||
1378 'TRANSACTION_DATE DATE NOT NULL, ' ||
1379 'PLANNED_QUANTITY NUMBER, ' ||
1380 'UOM_CODE VARCHAR2(3), ' ||
1381 'CREATION_DATE DATE NOT NULL, ' ||
1382 'CREATED_BY NUMBER NOT NULL, ' ||
1383 'LAST_UPDATE_DATE DATE NOT NULL, ' ||
1384 'LAST_UPDATED_BY NUMBER NOT NULL, ' ||
1385 'LAST_UPDATE_LOGIN NUMBER)';
1386 BEGIN
1387 BIS_COLLECTION_UTILITIES.put_line('...'|| l_create_tables(i));
1388 EXECUTE IMMEDIATE l_stmt;
1389 EXCEPTION
1390 WHEN tablename_exist_exception THEN
1391 BIS_COLLECTION_UTILITIES.put_line('Temporary regular table already exists for baseline ' || l_strings(i) || '. Cleanup temporary table.');
1392 EXECUTE IMMEDIATE 'truncate table opi_dbi_ptp_tmp_' || l_baseline_ids(i);
1393 WHEN others THEN
1394 BIS_COLLECTION_UTILITIES.put_line('Fail to create temporary regular table for baseline ' || l_strings(i) || '.');
1395 retcode := g_error;
1396 errbuf := SQLERRM;
1397 RAISE load_exception;
1398 END;
1399 END LOOP;
1400 END IF;
1401
1402 BIS_COLLECTION_UTILITIES.put_line('Insert new data into temporary regular tables.');
1403 l_segment_num := 100;
1404 l_stmt := 'INSERT /*+ append ';
1405 IF l_baseline_ids.count <> 0 THEN
1406 FOR i IN l_baseline_ids.FIRST..l_baseline_ids.LAST LOOP
1407 l_stmt := l_stmt || 'parallel(OPI_DBI_PTP_TMP_' || l_baseline_ids(i) || ') ';
1408 END LOOP;
1409 END IF;
1410 l_stmt := l_stmt || '*/ FIRST ';
1411 IF l_insert_tables.count <> 0 THEN
1412 FOR i IN l_create_tables.FIRST..l_create_tables.LAST LOOP
1413 l_stmt := l_stmt || l_insert_tables(i)
1414 || ' VALUES (' ||
1415 'ORGANIZATION_ID, ' ||
1416 'BASELINE_ID, ' ||
1417 'PLAN_NAME, ' ||
1418 'OWNING_ORG_ID, ' ||
1419 'INVENTORY_ITEM_ID, ' ||
1420 'TRANSACTION_DATE, ' ||
1421 'PLANNED_QUANTITY, ' ||
1422 'UOM_CODE, ' ||
1423 'CREATION_DATE, ' ||
1424 'CREATED_BY, ' ||
1425 'LAST_UPDATE_DATE, ' ||
1426 'LAST_UPDATED_BY, ' ||
1427 'LAST_UPDATE_LOGIN)';
1428 END LOOP;
1429 l_stmt := l_stmt || ' ' ||
1430 'select /*+ ordered use_nl(time) use_hash(bucket, supply) parallel(supply) */
1431 supply.organization_id ORGANIZATION_ID,
1432 bl.baseline_id BASELINE_ID,
1433 bl.compile_designator PLAN_NAME,
1434 bl.organization_id OWNING_ORG_ID,
1435 supply.sr_inventory_item_id INVENTORY_ITEM_ID,
1436 trunc(time.report_date) TRANSACTION_DATE,
1437 sum(nvl(supply.new_order_quantity, 0)/bucket.days_in_bkt) PLANNED_QUANTITY,
1438 supply.uom_code UOM_CODE,
1439 :g_sysdate CREATION_DATE,
1440 :g_last_updated_by CREATED_BY,
1441 :g_sysdate LAST_UPDATE_DATE,
1442 :g_last_updated_by LAST_UPDATED_BY,
1443 :g_last_update_login LAST_UPDATE_LOGIN
1444 from
1445 (select /*+ no_merge use_hash(sched,setup,plan) */
1446 sched.baseline_id,
1447 sched.from_date,
1448 sched.to_date,
1449 plan.plan_id,
1450 plan.compile_designator,
1451 plan.organization_id,
1452 plan.cutoff_date
1453 from
1454 opi_dbi_baseline_schedules sched,
1455 opi_dbi_baseline_plans setup,
1456 isc_dbi_plans plan
1457 where sched.next_collection_date <= :g_sysdate
1458 and sched.schedule_type = 1
1459 and sched.baseline_id = setup.baseline_id
1460 and setup.plan_name = plan.compile_designator
1461 and setup.owning_org_id = plan.organization_id
1462 ) bl,
1463 isc_dbi_plan_buckets bucket,
1464 fii_time_day_all_v time,
1465 isc_dbi_supplies_f supply
1466 where bucket.plan_id = bl.plan_id
1467 and bucket.organization_id = bl.organization_id
1468 and bucket.plan_id = supply.plan_id
1469 and supply.new_schedule_date + nvl(supply.new_processing_days, 0) between bucket.bkt_start_date and bucket.bkt_end_date
1470 AND nvl(supply.disposition_status_type, 0) <> 2
1471 and supply.in_source_plan = 2
1472 and nvl(supply.bom_item_type, 0) <> 3
1473 --and nvl(supply.r_cfm_routing_flag, 0) <> 3
1474 and (supply.order_type in (3, 14, 16, 27, 28, 30)
1475 OR
1476 (supply.order_type in (5, 17)
1477 AND supply.source_sr_instance_id = supply.sr_instance_id
1478 AND supply.source_organization_id = supply.organization_id
1479 )
1480 OR
1481 (supply.order_type in (5, 17)
1482 AND supply.source_sr_instance_id is null
1483 AND supply.source_supplier_id is null
1484 AND supply.planning_make_buy_code = 1
1485 )
1486 )
1487 and time.report_date between bucket.bkt_start_date and bucket.bkt_end_date
1488 and time.report_date between bl.from_date and nvl(bl.to_date, bl.cutoff_date)
1489 group by
1490 supply.organization_id,
1491 bl.baseline_id,
1492 bl.compile_designator,
1493 bl.organization_id,
1494 supply.sr_inventory_item_id,
1495 trunc(time.report_date),
1496 supply.uom_code
1497 ';
1498 BEGIN
1499 EXECUTE IMMEDIATE l_stmt USING g_sysdate, g_last_updated_by, g_sysdate, g_last_updated_by, g_last_update_login, g_sysdate;
1500 null;
1501 EXCEPTION
1502 WHEN others THEN
1503 BIS_COLLECTION_UTILITIES.put_line('Fail to insert new data into temporary regular table.');
1504 retcode := g_error;
1505 errbuf := SQLERRM;
1506 RAISE load_exception;
1507 END;
1508 END IF;
1509
1510 /*commit explicitly for direct load*/
1511 commit;
1512
1513 BIS_COLLECTION_UTILITIES.put_line('Add new partitions if necessary.');
1514 l_segment_num := 110;
1515 IF l_add_partitions.count <> 0 THEN
1516 FOR i IN l_add_partitions.FIRST..l_add_partitions.LAST LOOP
1517 BEGIN
1518 IF l_collected_dates(i) IS NULL THEN
1519 BIS_COLLECTION_UTILITIES.put_line('...'|| l_add_partitions(i));
1520 EXECUTE IMMEDIATE l_add_partitions(i);
1521 END IF;
1522 EXCEPTION
1523 WHEN partition_exist_exception or value_exist_exception THEN
1524 BIS_COLLECTION_UTILITIES.put_line('Partition exists already for baseline ' || l_strings(i) || '. No action.');
1525 WHEN others THEN
1526 BIS_COLLECTION_UTILITIES.put_line('Fail to add partition to baseline staging table for baseline ' || l_strings(i) || '.');
1527 retcode := g_error;
1528 errbuf := SQLERRM;
1529 RAISE load_exception;
1530 END;
1531 END LOOP;
1532 END IF;
1533
1534 BIS_COLLECTION_UTILITIES.put_line('Swap partitions with corresponding temporary regular tables.');
1535 l_segment_num := 120;
1536 IF l_swap_partitions.count <> 0 THEN
1537 FOR i IN l_swap_partitions.FIRST..l_swap_partitions.LAST LOOP
1538 BEGIN
1539 BIS_COLLECTION_UTILITIES.put_line('...'|| l_swap_partitions(i));
1540 EXECUTE IMMEDIATE l_swap_partitions(i);
1541 EXCEPTION
1542 WHEN others THEN
1543 BIS_COLLECTION_UTILITIES.put_line('Fail to exchange partition for baseline ' || l_strings(i) || '.');
1544 retcode := g_error;
1545 errbuf := SQLERRM;
1546 RAISE load_exception;
1547 END;
1548 END LOOP;
1549 END IF;
1550
1551 BIS_COLLECTION_UTILITIES.put_line('Drop temporary regular tables.');
1552 l_segment_num := 130;
1553 IF l_drop_tables.count <> 0 THEN
1554 FOR i IN l_drop_tables.FIRST..l_drop_tables.LAST LOOP
1555 BEGIN
1556 BIS_COLLECTION_UTILITIES.put_line('...'|| l_drop_tables(i));
1557 EXECUTE IMMEDIATE l_drop_tables(i);
1558 EXCEPTION
1559 WHEN others THEN
1560 retcode := g_error;
1561 errbuf := SQLERRM;
1562 RAISE load_exception;
1563 END;
1564 END LOOP;
1565 END IF;
1566
1567 --archive/cleanup data
1568 BEGIN
1569 select SUM(decode(sign(sched.from_date - def.last_from_date), -1, 1, 0)) del_cnt,
1570 SUM(decode(sign(sched.from_date - def.last_from_date), 1, 1, 0)) arv_cnt
1571 into l_delete, l_archive
1572 from OPI_DBI_BASELINE_DEFINITIONS def,
1573 OPI_DBI_BASELINE_SCHEDULES sched
1574 where sched.next_collection_date <= g_sysdate
1575 and sched.schedule_type = 1
1576 and sched.baseline_id = def.baseline_id
1577 ;
1578 EXCEPTION
1579 WHEN others THEN
1580 BIS_COLLECTION_UTILITIES.put_line('Fail to determine if there is need to cleanup/archive data.');
1581 retcode := g_error;
1582 errbuf := SQLERRM;
1583 RAISE archive_cleanup_exception;
1584 END;
1585
1586 l_segment_num := 140;
1587 IF l_delete > 0 THEN
1588 BIS_COLLECTION_UTILITIES.put_line('Clean up data in baseline fact table.');
1589 BEGIN
1590 delete from OPI_DBI_PTP_PLAN_F
1591 where
1592 rowid in
1593 (select f.rowid
1594 from OPI_DBI_PTP_PLAN_F f,
1595 OPI_DBI_BASELINE_SCHEDULES sched
1596 where sched.next_collection_date <= g_sysdate
1597 and sched.schedule_type = 1
1598 and f.baseline_id = sched.baseline_id
1599 and f.transaction_date >= sched.from_date
1600 );
1601 EXCEPTION
1602 WHEN others THEN
1603 BIS_COLLECTION_UTILITIES.put_line('Fail to delete old data from baseline fact table.');
1604 retcode := g_error;
1605 errbuf := SQLERRM;
1606 RAISE archive_cleanup_exception;
1607 END;
1608 END IF;
1609
1610 l_segment_num := 150;
1611 IF l_archive > 0 THEN
1612 BIS_COLLECTION_UTILITIES.put_line('Archive data into baseline fact table.');
1613 BEGIN
1614 insert /*+ append parallel(OPI_DBI_PTP_PLAN_F) */
1615 into OPI_DBI_PTP_PLAN_F
1616 (
1617 ORGANIZATION_ID,
1618 BASELINE_ID,
1619 PLAN_NAME,
1620 OWNING_ORG_ID,
1621 INVENTORY_ITEM_ID,
1622 TRANSACTION_DATE,
1623 PLANNED_QUANTITY,
1624 UOM_CODE,
1625 CREATION_DATE,
1626 CREATED_BY,
1627 LAST_UPDATE_DATE,
1628 LAST_UPDATED_BY,
1629 LAST_UPDATE_LOGIN
1630 )
1631 select /*+ use_hash(day,stg) parallel(stg) parallel(day) */
1632 stg.ORGANIZATION_ID,
1633 stg.BASELINE_ID,
1634 stg.PLAN_NAME,
1635 stg.OWNING_ORG_ID,
1636 stg.INVENTORY_ITEM_ID,
1637 day.report_date,
1638 stg.PLANNED_QUANTITY,
1639 stg.UOM_CODE,
1640 g_sysdate,
1641 g_last_updated_by,
1642 g_sysdate,
1643 g_last_updated_by,
1644 g_last_update_login
1645 from OPI_PTP_SUM_STG_MV stg,
1646 OPI_DBI_BASELINE_SCHEDULES sched,
1647 fii_time_day day
1648 where sched.next_collection_date <= g_sysdate
1649 and sched.schedule_type = 1
1650 and stg.baseline_id = sched.baseline_id
1651 and stg.item_cat_flag = 0
1652 and stg.period_type_id = 1
1653 and stg.day_id = day.report_date_julian
1654 and day.report_date < sched.from_date
1655 ;
1656 --only new rows inserted being reported to wrapup procedure
1657 l_count := SQL%ROWCOUNT;
1658 EXCEPTION
1659 WHEN others THEN
1660 BIS_COLLECTION_UTILITIES.put_line('Fail to archive data into baseline fact table.');
1661 retcode := g_error;
1662 errbuf := SQLERRM;
1663 RAISE archive_cleanup_exception;
1664 END;
1665 END IF;
1666
1667 --put call to get cost/conv collection here
1668 l_segment_num := 160;
1669 BIS_COLLECTION_UTILITIES.put_line('Collect cost/conversion rate information.');
1670 Get_Discrete_Cost_and_Rate(errbuf, retcode);
1671 Get_Process_Cost(errbuf, retcode);
1672
1673 --Archive collection history
1674
1675 BIS_COLLECTION_UTILITIES.put_line('Archive collection history into log table, update baseline setup tables.');
1676 BEGIN
1677 l_segment_num := 170;
1678 insert into OPI_DBI_PTP_LOG
1679 (
1680 BASELINE_ID,
1681 BASELINE_NAME,
1682 PLAN_NAME,
1683 OWNING_ORG_ID,
1684 ORGANIZATION_ID,
1685 FROM_DATE,
1686 TO_DATE,
1687 COLLECTED_DATE,
1688 PLAN_RUN_DATE,
1689 CREATION_DATE,
1690 CREATED_BY,
1691 LAST_UPDATE_DATE,
1692 LAST_UPDATED_BY,
1693 LAST_UPDATE_LOGIN
1694 )
1695 select def.BASELINE_ID,
1696 def.BASELINE_NAME,
1697 setup.PLAN_NAME,
1698 setup.OWNING_ORG_ID,
1699 orgs.ORGANIZATION_ID,
1700 sched.FROM_DATE,
1701 nvl(sched.TO_DATE, plan.cutoff_date),
1702 g_sysdate COLLECTED_DATE,
1703 plan.data_start_date PLAN_RUN_DATE,
1704 g_sysdate,
1705 g_last_updated_by,
1706 g_sysdate,
1707 g_last_updated_by,
1708 g_last_update_login
1709 from OPI_DBI_BASELINE_DEFINITIONS def,
1710 OPI_DBI_BASELINE_SCHEDULES sched,
1711 OPI_DBI_BASELINE_PLANS setup,
1712 ISC_DBI_PLANS plan,
1713 ISC_DBI_PLAN_ORGANIZATIONS orgs
1714 where def.baseline_id = sched.baseline_id
1715 and sched.next_collection_date <= g_sysdate
1716 and sched.schedule_type = 1
1717 and sched.baseline_id = setup.baseline_id
1718 and setup.plan_name = plan.compile_designator
1719 and setup.owning_org_id = plan.organization_id
1720 and plan.plan_id = orgs.plan_id
1721 ;
1722
1723 l_segment_num := 180;
1724 update OPI_DBI_BASELINE_DEFINITIONS def
1725 set (data_start_date, last_collected_date, last_from_date, last_to_date, last_update_date, last_updated_by, last_update_login)
1726 =
1727 (select nvl(def.data_start_date, sched.FROM_DATE),
1728 g_sysdate,
1729 sched.FROM_DATE,
1730 min(nvl(sched.TO_DATE, plan.cutoff_date)),
1731 g_sysdate,
1732 g_last_updated_by,
1733 g_last_update_login
1734 from OPI_DBI_BASELINE_SCHEDULES sched,
1735 OPI_DBI_BASELINE_PLANS setup,
1736 ISC_DBI_PLANS plan
1737 where def.baseline_id = sched.baseline_id
1738 and sched.baseline_id = setup.baseline_id
1739 and setup.plan_name = plan.compile_designator
1740 and setup.owning_org_id = plan.organization_id
1741 group by def.data_start_date, sched.FROM_DATE
1742 )
1743 where def.baseline_id in
1744 (select baseline_id from opi_dbi_baseline_schedules
1745 where next_collection_date <= g_sysdate
1746 and schedule_type = 1
1747 )
1748 ;
1749
1750 FOR l_baseline_record IN l_baseline_info LOOP
1751 IF (l_baseline_record.data_start_date IS NOT NULL) and (l_baseline_record.data_start_date > l_baseline_record.from_date) THEN
1752 l_segment_num := 190;
1753 update OPI_DBI_PTP_COST cost
1754 set to_date = l_baseline_record.from_date
1755 where cost.organization_id in
1756 (select orgs.organization_id
1757 from OPI_DBI_BASELINE_PLANS setup,
1758 ISC_DBI_PLANS plan,
1759 ISC_DBI_PLAN_ORGANIZATIONS orgs
1760 where setup.baseline_id = l_baseline_record.baseline_id
1761 and setup.plan_name = plan.compile_designator
1762 and setup.owning_org_id = plan.organization_id
1763 and plan.plan_id = orgs.plan_id
1764 )
1765 and cost.to_date = l_baseline_record.data_start_date
1766 ;
1767
1768 update OPI_DBI_PTP_COST cost
1769 set from_date = l_baseline_record.from_date
1770 where cost.organization_id in
1771 (select orgs.organization_id
1772 from OPI_DBI_BASELINE_PLANS setup,
1773 ISC_DBI_PLANS plan,
1774 ISC_DBI_PLAN_ORGANIZATIONS orgs
1775 where setup.baseline_id = l_baseline_record.baseline_id
1776 and setup.plan_name = plan.compile_designator
1777 and setup.owning_org_id = plan.organization_id
1778 and plan.plan_id = orgs.plan_id
1779 )
1780 and cost.from_date = l_baseline_record.data_start_date
1781 ;
1782
1783 update OPI_DBI_PTP_CONV conv
1784 set to_date = l_baseline_record.from_date
1785 where conv.organization_id in
1786 (select orgs.organization_id
1787 from OPI_DBI_BASELINE_PLANS setup,
1788 ISC_DBI_PLANS plan,
1789 ISC_DBI_PLAN_ORGANIZATIONS orgs
1790 where setup.baseline_id = l_baseline_record.baseline_id
1791 and setup.plan_name = plan.compile_designator
1792 and setup.owning_org_id = plan.organization_id
1793 and plan.plan_id = orgs.plan_id
1794 )
1795 and conv.to_date = l_baseline_record.data_start_date
1796 ;
1797
1798 update OPI_DBI_PTP_CONV conv
1799 set from_date = l_baseline_record.from_date
1800 where conv.organization_id in
1801 (select orgs.organization_id
1802 from OPI_DBI_BASELINE_PLANS setup,
1803 ISC_DBI_PLANS plan,
1804 ISC_DBI_PLAN_ORGANIZATIONS orgs
1805 where setup.baseline_id = l_baseline_record.baseline_id
1806 and setup.plan_name = plan.compile_designator
1807 and setup.owning_org_id = plan.organization_id
1808 and plan.plan_id = orgs.plan_id
1809 )
1810 and conv.from_date = l_baseline_record.data_start_date
1811 ;
1812
1813 l_segment_num := 200;
1814 update OPI_DBI_BASELINE_DEFINITIONS def
1815 set data_start_date = last_from_date
1816 where baseline_id = l_baseline_record.baseline_id
1817 ;
1818 END IF;
1819
1820 update OPI_DBI_BASELINE_SCHEDULES
1821 set from_date = null,
1822 to_date = null,
1823 schedule_type = null,
1824 next_collection_date = null
1825 where baseline_id = l_baseline_record.baseline_id
1826 ;
1827 END LOOP;
1828 EXCEPTION
1829 WHEN others THEN
1830 retcode := g_error;
1831 errbuf := SQLERRM;
1832 RAISE update_log_exception;
1833 END;
1834
1835 --explict commit
1836 COMMIT;
1837
1838 --synchronize OPI_PTP_SUM_STG_MV with OPI_DBI_PTP_PLAN_STG
1839 BIS_COLLECTION_UTILITIES.put_line('synchronize OPI_PTP_SUM_STG_MV with OPI_DBI_PTP_PLAN_STG.');
1840 l_segment_num := 180;
1841 --analyze table first per performance team's advice
1842 FND_STATS.GATHER_TABLE_STATS(errbuf,retcode,'OPI','OPI_DBI_PTP_PLAN_STG');
1843 BEGIN
1844 EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY';
1845 DBMS_MVIEW.REFRESH('OPI_PTP_SUM_STG_MV','?',parallelism => g_degree);
1846 EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL QUERY';
1847 update opi_dbi_run_log_curr
1848 set stop_reason_code = 2
1849 where etl_id = 7 and source = 1;
1850 commit;
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 update opi_dbi_run_log_curr
1854 set stop_reason_code = 1
1855 where etl_id = 7 and source = 1;
1856 commit;
1857 BIS_COLLECTION_UTILITIES.put_line('Fail to refresh materialized view OPI_PTP_SUM_STG_MV. Please fix the problem and re-run the concurrent request set.');
1858 RAISE mv_refresh_exception;
1859 END;
1860
1861 BIS_COLLECTION_UTILITIES.put_line('Successfully collect baseline data on ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY') || '.');
1862 BIS_COLLECTION_UTILITIES.WRAPUP(
1863 p_status => TRUE,
1864 p_count => l_count,
1865 p_message => 'Successfully collect baseline data on ' || TO_CHAR(g_sysdate, 'DD-MON-YYYY') || '.'
1866 );
1867
1868 retcode := g_ok;
1869 return;
1870 EXCEPTION
1871 WHEN intialization_exception or collection_parameter_exception or isc_collection_exception THEN
1872 ROLLBACK;
1873 BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1874 BIS_COLLECTION_UTILITIES.put_line(errbuf);
1875 BIS_COLLECTION_UTILITIES.WRAPUP(
1876 p_status => FALSE,
1877 p_message => 'Failed to collect baseline data.'
1878 );
1879 WHEN load_exception or archive_cleanup_exception or update_log_exception or cost_conversion_rate_exception THEN
1880 ROLLBACK;
1881 BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1882 BIS_COLLECTION_UTILITIES.put_line(errbuf);
1883 IF l_segment_num >= 90 THEN
1884 IF l_drop_tables.count <> 0 THEN
1885 FOR i IN l_drop_tables.FIRST..l_drop_tables.LAST LOOP
1886 BIS_COLLECTION_UTILITIES.put_line('...' || l_drop_tables(i));
1887 BEGIN
1888 EXECUTE IMMEDIATE l_drop_tables(i);
1889 EXCEPTION
1890 --if data has been dropped, ignore the other
1891 WHEN others THEN
1892 null;
1893 END;
1894 END LOOP;
1895 END IF;
1896 END IF;
1897 BIS_COLLECTION_UTILITIES.WRAPUP(
1898 p_status => FALSE,
1899 p_message => 'Failed to collect baseline data.'
1900 );
1901 WHEN others THEN
1902 ROLLBACK;
1903 BIS_COLLECTION_UTILITIES.put_line('Error out at segment ' || l_segment_num || '.');
1904 retcode := g_error;
1905 errbuf := SQLERRM;
1906 BIS_COLLECTION_UTILITIES.put_line(errbuf);
1907 BIS_COLLECTION_UTILITIES.WRAPUP(
1908 p_status => FALSE,
1909 p_message => 'Failed to collect baseline data.'
1910 );
1911 END Extract_Baseline;
1912
1913 PROCEDURE REFRESH_RPT_BND_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1914 IS
1915 BEGIN
1916 -- very small mv. don't need parallelism, which will introduces unnecessary overhead
1917 dbms_mview.refresh('OPI_PTP_RPT_BND_MV',
1918 '?'
1919 );
1920
1921 END REFRESH_RPT_BND_MV;
1922
1923 PROCEDURE REFRESH_CBN_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1924 IS
1925 BEGIN
1926
1927 dbms_mview.refresh('OPI_PTP_CBN_MV',
1928 '?',
1929 parallelism => g_degree -- PARALLELISM
1930 );
1931
1932 END REFRESH_CBN_MV;
1933
1934
1935 PROCEDURE REFRESH_ITEM_F_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1936 IS
1937 BEGIN
1938
1939 dbms_mview.refresh('OPI_PTP_ITEM_F_MV',
1940 '?',
1941 parallelism => g_degree -- PARALLELISM
1942 );
1943
1944 END REFRESH_ITEM_F_MV;
1945
1946
1947 PROCEDURE REFRESH_SUM_F_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1948 IS
1949 BEGIN
1950
1951 dbms_mview.refresh('OPI_PTP_SUM_F_MV',
1952 '?',
1953 parallelism => g_degree -- PARALLELISM
1954 );
1955 END REFRESH_SUM_F_MV;
1956
1957 PROCEDURE REFRESH_SUM_STG_MV(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1958 IS
1959 BEGIN
1960
1961 dbms_mview.refresh('OPI_PTP_SUM_STG_MV',
1962 '?',
1963 parallelism => g_degree -- PARALLELISM
1964 );
1965 END REFRESH_SUM_STG_MV;
1966
1967 PROCEDURE REFRESH(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
1968 IS
1969 l_stmt_num NUMBER;
1970 l_err_num NUMBER;
1971 l_err_msg VARCHAR2(255);
1972 BEGIN
1973
1974 l_stmt_num := 10;
1975 g_degree := bis_common_parameters.get_degree_of_parallelism;
1976 BIS_COLLECTION_UTILITIES.PUT_LINE('Starting Materialized Views Refresh for Production to Plan...');
1977
1978 l_stmt_num := 20;
1979 REFRESH_CBN_MV(errbuf, retcode);
1980 BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_CBN_MV finished ...');
1981
1982 l_stmt_num := 30;
1983 REFRESH_ITEM_F_MV(errbuf, retcode);
1984 BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_ITEM_F_MV finished ...');
1985
1986 l_stmt_num := 40;
1987 REFRESH_SUM_F_MV(errbuf, retcode);
1988 BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_F_MV finished ...');
1989
1990 l_stmt_num := 50;
1991 REFRESH_SUM_STG_MV(errbuf, retcode);
1992 BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_SUM_STG_MV finished ...');
1993
1994 l_stmt_num := 60;
1995 REFRESH_RPT_BND_MV(errbuf, retcode);
1996 BIS_COLLECTION_UTILITIES.PUT_LINE('Refresh OPI_PTP_RPT_BND_MV finished ...');
1997 retcode := 0;
1998
1999 EXCEPTION
2000 WHEN OTHERS THEN
2001 retcode := SQLCODE;
2002 errbuf := SQLERRM;
2003
2004 BIS_COLLECTION_UTILITIES.PUT_LINE('OPI_DBI_PTP_REFRESH_PKG.REFRESH - Error at statement ('
2005 || to_char(l_stmt_num)
2006 || ')');
2007
2008 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || retcode);
2009 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || errbuf);
2010 END REFRESH;
2011
2012 END OPI_DBI_PTP_BASELINE_PKG;