[Home] [Help]
PACKAGE BODY: APPS.CSTPPLLC
Source
1 PACKAGE BODY CSTPPLLC as
2 /* $Header: CSTPLLCB.pls 120.1.12010000.2 2008/08/08 12:32:11 smsasidh ship $*/
3
4 /*--------------------------------------------------------------------------
5 PRIVATE PROCEDURE
6 get_assembly_components
7 This procedure gets components of the assembly item which has the
8 completion/return/scrap.
9
10 i_method:
11 1: use WIP Bill to get the components
12 2: use material transactions to get the components
13 --------------------------------------------------------------------------*/
14
15 PROCEDURE get_assembly_components(
16 i_pac_period_id IN NUMBER,
17 i_cost_group_id IN NUMBER,
18 i_start_date IN DATE,
19 i_end_date IN DATE,
20 i_user_id IN NUMBER,
21 i_login_id IN NUMBER,
22 i_request_id IN NUMBER,
23 i_prog_id IN NUMBER,
24 i_prog_app_id IN NUMBER,
25 i_method IN NUMBER,
26 o_err_num OUT NOCOPY NUMBER,
27 o_err_code OUT NOCOPY VARCHAR2,
28 o_err_msg OUT NOCOPY VARCHAR2)
29 IS
30 CURSOR C_assm_comp(
31 c_start_date IN DATE,
32 c_end_date IN DATE,
33 c_cost_group_id IN NUMBER)
34 IS
35 SELECT transaction_id,
36 completion_transaction_id,
37 transaction_source_id,
38 repetitive_line_id,
39 inventory_item_id,
40 organization_id,
41 flow_schedule
42 FROM mtl_material_transactions mmt
43 WHERE transaction_source_type_id = 5 /* job or schedule */
44 AND transaction_action_id IN (30, 31, 32) /* scrap,completion,return */
45 AND transaction_date BETWEEN trunc(c_start_date)
46 AND (trunc(c_end_date) + 0.99999)
47 AND EXISTS(
48 SELECT 'exists'
49 FROM cst_cost_group_assignments
50 WHERE cost_group_id = c_cost_group_id
51 AND organization_id = mmt.organization_id);
52
53 comp_rec C_assm_comp%ROWTYPE;
54 l_start_date DATE;
55 l_end_date DATE;
56 l_stmt_num NUMBER;
57 l_loop_count NUMBER;
58 BEGIN
59 o_err_num := 0;
60 o_err_code := '';
61 o_err_msg := '';
62 l_loop_count := 0;
63 l_stmt_num := 10;
64
65 FOR comp_rec in C_assm_comp(i_start_date, i_end_date, i_cost_group_id)
66 LOOP
67 l_loop_count := l_loop_count + 1;
68
69 /*----------------------------------------------------------------------
70 The (-1, inventory_item_id) combination inserted will signify that there
71 has been a completion/scrap/return for the item
72 ------------------------------------------------------------------------*/
73
74 INSERT INTO
75 cst_pac_explosion_temp (
76 pac_period_id,
77 cost_group_id,
78 assembly_item_id,
79 component_item_id,
80 deleted,
81 loop_count)
82 SELECT i_pac_period_id,
83 i_cost_group_id,
84 -1,
85 comp_rec.inventory_item_id,
86 'N',
87 1000
88 FROM dual
89 WHERE NOT EXISTS (
90 SELECT 'exists'
91 FROM cst_pac_explosion_temp
92 WHERE assembly_item_id = -1
93 AND component_item_id = comp_rec.inventory_item_id
94 AND pac_period_id = i_pac_period_id
95 AND cost_group_id = i_cost_group_id);
96
97 IF(NVL(comp_rec.flow_schedule,'N') <> 'Y') THEN /* not CFM */
98 IF (comp_rec.repetitive_line_id IS NULL) THEN /* discrete jobs */
99 IF (i_method = 1) THEN
100 INSERT INTO
101 cst_pac_explosion_temp(
102 pac_period_id,
103 cost_group_id,
104 assembly_item_id,
105 component_item_id,
106 deleted,
107 loop_count)
108 SELECT DISTINCT
109 i_pac_period_id,
110 i_cost_group_id,
111 comp_rec.inventory_item_id,
112 wro.inventory_item_id,
113 'N',
114 1000
115 FROM wip_requirement_operations wro
116 WHERE wro.wip_entity_id = comp_rec.transaction_source_id
117 AND NOT EXISTS (
118 SELECT 'exists'
119 FROM cst_pac_explosion_temp
120 WHERE assembly_item_id = comp_rec.inventory_item_id
121 AND component_item_id = inventory_item_id
122 AND pac_period_id = i_pac_period_id
123 AND cost_group_id = i_cost_group_id);
124 ELSE /* i_method = 2 */
125 INSERT INTO
126 cst_pac_explosion_temp(
127 pac_period_id,
128 cost_group_id,
129 assembly_item_id,
130 component_item_id,
131 deleted,
132 loop_count)
133 SELECT DISTINCT
134 i_pac_period_id,
135 i_cost_group_id,
136 comp_rec.inventory_item_id,
137 mmt.inventory_item_id,
138 'N',
139 1000
140 FROM mtl_material_transactions mmt
141 WHERE transaction_source_id = comp_rec.transaction_source_id
142 AND transaction_source_type_id = 5
143 AND transaction_action_id IN (1,27,33,34)
144 AND transaction_date BETWEEN trunc(i_start_date)
145 AND (trunc(i_end_date) + 0.99999)
146 AND NOT EXISTS (
147 SELECT 'exists'
148 FROM cst_pac_explosion_temp
149 WHERE assembly_item_id = comp_rec.inventory_item_id
150 AND component_item_id = inventory_item_id
151 AND pac_period_id = i_pac_period_id
152 AND cost_group_id = i_cost_group_id)
153 GROUP BY
154 mmt.inventory_item_id
155 HAVING sum(mmt.primary_quantity) <> 0;
156 END IF;
157 ELSE /* repetitive schedules */
158 IF (i_method = 1) THEN
159 INSERT INTO
160 cst_pac_explosion_temp(
161 pac_period_id,
162 cost_group_id,
163 assembly_item_id,
164 component_item_id,
165 deleted,
166 loop_count)
167 SELECT DISTINCT
168 i_pac_period_id,
169 i_cost_group_id,
170 comp_rec.inventory_item_id,
171 wro.inventory_item_id,
172 'N',
173 1000
174 FROM mtl_material_txn_allocations mmta ,
175 wip_requirement_operations wro
176 WHERE mmta.transaction_id = comp_rec.transaction_id
177 AND mmta.repetitive_schedule_id = wro.repetitive_schedule_id
178 AND wro.wip_entity_id = comp_rec.transaction_source_id
179 AND NOT EXISTS (
180 SELECT 'exists'
181 FROM cst_pac_explosion_temp
182 WHERE assembly_item_id = comp_rec.inventory_item_id
183 AND component_item_id = inventory_item_id
184 AND pac_period_id = i_pac_period_id
185 AND cost_group_id = i_cost_group_id);
186 ELSE /* i_method = 2 */
187 INSERT INTO
188 cst_pac_explosion_temp(
189 pac_period_id,
190 cost_group_id,
191 assembly_item_id,
192 component_item_id,
193 deleted,
194 loop_count)
195 SELECT DISTINCT
196 i_pac_period_id,
197 i_cost_group_id,
198 comp_rec.inventory_item_id,
199 mmt.inventory_item_id,
200 'N',
201 1000
202 FROM mtl_material_transactions mmt
203 WHERE transaction_source_id = comp_rec.transaction_source_id
204 AND repetitive_line_id = comp_rec.repetitive_line_id
205 AND transaction_source_type_id = 5
206 AND transaction_action_id IN (1,27,33,34)
207 AND transaction_date BETWEEN trunc(i_start_date)
208 AND (trunc(i_end_date) + 0.99999)
209 AND NOT EXISTS (
210 SELECT 'exists'
211 FROM cst_pac_explosion_temp
212 WHERE assembly_item_id = comp_rec.inventory_item_id
213 AND component_item_id = inventory_item_id
214 AND pac_period_id = i_pac_period_id
215 AND cost_group_id = i_cost_group_id)
216 GROUP BY
217 mmt.inventory_item_id
218 HAVING sum(mmt.primary_quantity) <> 0;
219 END IF;
220 END IF;
221 ELSE /* CFM */
222 -- For CFM we go to MMT, because BBOM, BIC might be altered all the
223 -- time, effectivity dates changed so its difficult to get the bill
224 -- back in time
225 IF (i_method = 1) THEN
226 INSERT INTO
227 cst_pac_explosion_temp(
228 pac_period_id,
229 cost_group_id,
230 assembly_item_id,
231 component_item_id,
232 deleted,
233 loop_count)
234 SELECT DISTINCT
235 i_pac_period_id,
236 i_cost_group_id,
237 comp_rec.inventory_item_id,
238 mmt.inventory_item_id,
239 'N',
240 1000
241 FROM mtl_material_transactions mmt
242 WHERE mmt.completion_transaction_id =
243 comp_rec.completion_transaction_id
244 AND transaction_date BETWEEN trunc(i_start_date)
245 AND (trunc(i_end_date) + 0.99999)
246 AND mmt.transaction_action_id in (1,27,33,34)
247 AND NOT EXISTS (
248 SELECT 'exists'
249 FROM cst_pac_explosion_temp
250 WHERE assembly_item_id = comp_rec.inventory_item_id
251 AND component_item_id = inventory_item_id
252 AND pac_period_id = i_pac_period_id
253 AND cost_group_id = i_cost_group_id);
254 ELSE /* i_method = 2 */
255 INSERT INTO
256 cst_pac_explosion_temp(
257 pac_period_id,
258 cost_group_id,
259 assembly_item_id,
260 component_item_id,
261 deleted,
262 loop_count)
263 SELECT DISTINCT
264 i_pac_period_id,
265 i_cost_group_id,
266 comp_rec.inventory_item_id,
267 mmt.inventory_item_id,
268 'N',
269 1000
270 FROM mtl_material_transactions mmt
271 WHERE mmt.completion_transaction_id =
272 comp_rec.completion_transaction_id
273 AND transaction_date BETWEEN trunc(i_start_date)
274 AND (trunc(i_end_date) + 0.99999)
275 AND mmt.transaction_action_id in (1,27,33,34)
276 AND NOT EXISTS (
277 SELECT 'exists'
278 FROM cst_pac_explosion_temp
279 WHERE assembly_item_id = comp_rec.inventory_item_id
280 AND component_item_id = inventory_item_id
281 AND pac_period_id = i_pac_period_id
282 AND cost_group_id = i_cost_group_id)
283 GROUP BY
284 mmt.inventory_item_id
285 HAVING sum(primary_quantity) <> 0;
286 END IF;
287 END IF;
288 END LOOP;
289
290 /*------------------------------------------------------------------------
291 Not really an error but still filling in error message for debugging
292 ------------------------------------------------------------------------*/
293
294 IF (l_loop_count = 0) THEN
295 o_err_msg := 'No assembly completions/returns/scrap txns in period '
296 || TO_CHAR(i_pac_period_id)
297 || ' for the cost group/legal entity '
298 || TO_CHAR(i_cost_group_id) ;
299 END IF;
300 EXCEPTION
301 WHEN others THEN
302 o_err_num := SQLCODE;
303 o_err_msg := 'CSTPPLLC:'
304 || 'get_assembly_components:'
305 || to_char(l_stmt_num)
306 || ' '
307 || substr(SQLERRM,1,150);
308
309 END get_assembly_components;
310
311 /*--------------------------------------------------------------------------
312 PRIVATE PROCEDURE
313 calc_low_level_codes
314 This procedure will actually calculate the low level codes for
315 assembly items with completion/scrap/return.
316 --------------------------------------------------------------------------*/
317
318 PROCEDURE calc_low_level_codes(
319 i_pac_period_id IN NUMBER,
320 i_cost_group_id IN NUMBER,
321 i_start_date IN DATE,
322 i_end_date IN DATE,
323 i_user_id IN NUMBER,
324 i_login_id IN NUMBER,
325 i_request_id IN NUMBER,
326 i_prog_id IN NUMBER,
327 i_prog_app_id IN NUMBER,
328 i_method IN NUMBER,
329 o_err_num OUT NOCOPY NUMBER,
330 o_err_code OUT NOCOPY VARCHAR2,
331 o_err_msg OUT NOCOPY VARCHAR2)
332 IS
333 CURSOR C_comp_not_deleted (
334 c_pac_period_id IN NUMBER,
335 c_cost_group_id IN NUMBER)
336 IS
337 SELECT DISTINCT
338 component_item_id cii
339 FROM cst_pac_explosion_temp
340 WHERE pac_period_id = c_pac_period_id
341 AND cost_group_id = c_cost_group_id
342 AND deleted = 'N';
343
344 -- cursor of job/schedules whose assemblies and components forms a loop
345
346 CURSOR C_looping_transactions (
347 c_start_date IN DATE,
348 c_end_date IN DATE,
349 c_pac_period_id IN NUMBER,
350 c_cost_group_id IN NUMBER)
351 IS
352 SELECT DISTINCT
353 msik1.inventory_item_id assembly_item_id,
354 msik1.concatenated_segments assembly_item,
355 msik2.inventory_item_id component_item_id,
356 msik2.concatenated_segments component_item,
357 we.wip_entity_id wip_entity_id,
358 we.wip_entity_name wip_entity
359 FROM cst_pac_explosion_temp cpet,
360 mtl_material_transactions mmt1,
361 mtl_material_transactions mmt2,
362 mtl_system_items_kfv msik1,
363 mtl_system_items_kfv msik2,
364 wip_entities we
365 WHERE cpet.pac_period_id = c_pac_period_id
366 AND cpet.cost_group_id = c_cost_group_id
367 AND cpet.deleted = 'N'
368 AND mmt1.inventory_item_id = cpet.assembly_item_id
369 AND mmt1.transaction_source_type_id = 5
370 AND mmt1.transaction_action_id IN (30,31,32)
371 AND mmt1.transaction_date BETWEEN trunc(c_start_date)
372 AND (trunc(c_end_date) + 0.99999)
373 AND (
374 SELECT count('exists')
375 FROM cst_cost_group_assignments
376 WHERE cost_group_id = c_cost_group_id
377 AND organization_id = mmt1.organization_id
378 and rownum < 2 ) > 0
379 AND mmt2.inventory_item_id = cpet.component_item_id
380 AND mmt2.transaction_action_id IN (1,27,33,34)
381 AND mmt2.transaction_date BETWEEN trunc(c_start_date)
382 AND (trunc(c_end_date) + 0.99999)
383 AND ( ( ( NVL(mmt1.flow_schedule,'N') <> 'Y')
384 AND mmt1.repetitive_line_id IS NULL
385 AND mmt2.transaction_source_id = mmt1.transaction_source_id
386 AND mmt2.transaction_source_type_id = 5)
387 OR ( ( NVL(mmt1.flow_schedule,'N') <> 'Y')
388 AND mmt1.repetitive_line_id IS NOT NULL
389 AND mmt2.transaction_source_id = mmt1.transaction_source_id
390 AND mmt2.repetitive_line_id = mmt1.repetitive_line_id
391 AND mmt2.transaction_source_type_id = 5)
392 OR ( ( NVL(mmt1.flow_schedule,'N') = 'Y')
393 AND mmt2.completion_transaction_id = mmt1.completion_transaction_id))
397 AND msik2.inventory_item_id = mmt2.inventory_item_id
394 AND msik1.organization_id = mmt1.organization_id
395 AND msik1.inventory_item_id = mmt1.inventory_item_id
396 AND msik2.organization_id = msik2.organization_id
398 AND we.wip_entity_id = mmt2.transaction_source_id
399 GROUP BY
400 msik1.inventory_item_id,
401 msik1.concatenated_segments,
402 msik2.inventory_item_id,
403 msik2.concatenated_segments,
404 we.wip_entity_id,
405 we.wip_entity_name
406 HAVING sum(mmt2.primary_quantity) <> 0;
407
408 l_counter NUMBER;
409 l_iteration NUMBER DEFAULT 0;
410 l_level_code NUMBER DEFAULT 1001;
411 l_update_flag BOOLEAN DEFAULT FALSE;
412 l_Processed_all_rows BOOLEAN DEFAULT FALSE;
413 l_cii cst_pac_explosion_temp.component_item_id%TYPE;
414 l_looping_transaction C_looping_transactions%ROWTYPE;
415 l_stmt_num NUMBER;
416 LOOP_ERROR EXCEPTION;
417 LOOP_WARNING EXCEPTION;
418 BEGIN
419 o_err_num := 0;
420 o_err_code := '';
421 o_err_msg := '';
422 l_stmt_num := 10;
423
424 /*------------------------------------------------------------------------
425 Marking for deletion rows which have component item same as the
426 assembly item. This is for non standard jobs
427 ------------------------------------------------------------------------*/
428
429 UPDATE cst_pac_explosion_temp
430 SET deleted = 'Y',
431 loop_count = l_iteration
432 WHERE component_item_id = assembly_item_id
433 AND deleted = 'N'
434 AND pac_period_id = i_pac_period_id
435 AND cost_group_id = i_cost_group_id;
436
437 WHILE NOT (l_Processed_all_rows) LOOP
438 l_update_flag := FALSE;
439 l_counter := 0;
440 l_level_code := l_level_code - 1;
441 l_iteration := l_iteration + 1;
442 l_stmt_num := 20;
443
444 OPEN C_comp_not_deleted(i_pac_period_id,i_cost_group_id);
445 LOOP
446 l_stmt_num := 30;
447 FETCH C_comp_not_deleted INTO l_cii;
448 EXIT when C_comp_not_deleted%NOTFOUND;
449
450 l_counter := l_counter + 1;
451
452 /*---------------------------------------------------------------------
453 Mark for deletion those rows where the component itself is not a
454 parent. Need to only check with rows that have been updated by
455 previous iterations since components can occur at different levels
456 --------------------------------------------------------------------*/
457 l_stmt_num := 40;
458
459 UPDATE /*+ index(cet1 CST_PAC_EXPLOSION_TEMP_N1) */
460 cst_pac_explosion_temp cet1
461 SET deleted = 'Y',
462 loop_count = l_iteration
463 WHERE component_item_id = l_cii
464 AND deleted = 'N'
465 AND pac_period_id = i_pac_period_id
466 AND cost_group_id = i_cost_group_id
467 AND NOT EXISTS (
468 SELECT 'exists as a parent'
469 FROM cst_pac_explosion_temp cet2
470 WHERE cet2.assembly_item_id = l_cii
471 AND pac_period_id = i_pac_period_id
472 AND cost_group_id = i_cost_group_id
473 AND loop_count >= l_iteration);
474
475 /*---------------------------------------------------------------------
476 if no rows are updated then it implies that the component exists as a
477 parent and should not for now be inserted into the CPLLC table
478 --------------------------------------------------------------------*/
479 l_stmt_num := 50;
480
481 IF (SQL%ROWCOUNT > 0) THEN
482 l_update_flag := TRUE;
483 INSERT INTO
484 cst_pac_low_level_codes (
485 pac_period_id,
486 cost_group_id,
487 inventory_item_id,
488 low_level_code,
489 last_update_date,
490 last_updated_by,
491 creation_date,
492 created_by)
493 VALUES (
494 i_pac_period_id,
495 i_cost_group_id,
496 l_cii,
497 l_level_code,
498 sysdate,
499 -1,
500 sysdate,
501 -1);
502 END IF;
503 END LOOP;
504 CLOSE C_comp_not_deleted;
505
506 IF (l_counter = 0) then
507 l_processed_all_rows := TRUE;
508 END IF;
509
510 l_stmt_num := 60;
511
512 /*------------------------------------------------------------------------
513 if "undeleted" rows still exist in cst_pac_explosion_temp and no rows
514 were updated, then this implies there is a LOOP.
515 ----------------------------------------------------------------------*/
516
517 IF (NOT l_update_flag AND l_counter > 0) THEN
518 IF (i_method = 1) THEN
519 raise LOOP_WARNING;
520 ELSE
521 raise LOOP_ERROR;
522 END IF;
523 exit;
524 END IF;
525
526 END LOOP;
527
528 /*-------------------------------------------------------------------------
529 We need to have low level codes only for assembly items which have
530 completion/return/scrap. So we delete all leaf node items from cpllc. We
534
531 make sure that assembly items with completion/scrap/return are not
532 considered as leaf nodes even if they might appear with LLC of 1000
533 ------------------------------------------------------------------------*/
535 DELETE FROM
536 cst_pac_low_level_codes cpllc
537 WHERE low_level_code = 1000
538 AND cost_group_id = i_cost_group_id
539 AND pac_period_id = i_pac_period_id
540 AND NOT EXISTS (
541 SELECT component_item_id FROM cst_pac_explosion_temp cpet
542 WHERE assembly_item_id = -1
543 AND component_item_id = cpllc.inventory_item_id
544 AND cost_group_id = i_cost_group_id
545 AND pac_period_id = i_pac_period_id);
546
547 EXCEPTION
548 WHEN LOOP_WARNING THEN
549 DELETE FROM
550 cst_pac_low_level_codes
551 WHERE cost_group_id = i_cost_group_id
552 AND pac_period_id = i_pac_period_id;
553 DELETE FROM
554 cst_pac_explosion_temp
555 WHERE cost_group_id = i_cost_group_id
556 AND pac_period_id = i_pac_period_id;
557
558 o_err_num := -999;
559 o_err_code := 'LOOP';
560 o_err_msg := 'CSTPPLLC:'
561 || 'calc_low_level_codes:'
562 || to_char(l_stmt_num)
563 || ' '
564 || 'A potential loop dependency is detected between the '
565 || 'transactions within this period. Recalculating the '
566 || 'low level codes to verify...';
567 WHEN LOOP_ERROR THEN
568 DELETE FROM
569 cst_pac_low_level_codes
570 WHERE cost_group_id = i_cost_group_id
571 AND pac_period_id = i_pac_period_id;
572
573 o_err_num := -999;
574 o_err_code := 'LOOP';
575 o_err_msg := 'CSTPPLLC:'
576 || 'calc_low_level_codes:'
577 || to_char(l_stmt_num)
578 || ' '
579 || 'A loop dependency is detected between '
580 || 'the transactions listed below (in the plsql section)';
581 OPEN C_looping_transactions(
582 i_start_date,
583 i_end_date,
584 i_pac_period_id,
585 i_cost_group_id);
586 LOOP
587 FETCH C_looping_transactions INTO l_looping_transaction;
588 EXIT when C_looping_transactions%NOTFOUND;
589 FND_FILE.PUT_LINE(FND_FILE.LOG,'Component '
590 || l_looping_transaction.component_item
591 || ' is issued for assembly '
592 || l_looping_transaction.assembly_item
593 || ' in job / repetitive assembly / flow schedule '
594 || l_looping_transaction.wip_entity);
595 END LOOP;
596 CLOSE C_looping_transactions;
597 WHEN others THEN
598 o_err_num := SQLCODE;
599 o_err_code := '';
600 o_err_msg := 'CSTPPLLC:'
601 || 'calc_low_level_codes:'
602 || to_char(l_stmt_num)
603 || ' '
604 || substr(SQLERRM,1,150);
605 END calc_low_level_codes;
606
607 /*--------------------------------------------------------------------------
608 PUBLIC PROCEDURE
609 pac_low_level_codes
610 This is the public interface to the package. It will inturn call the
611 private procedures
612 --------------------------------------------------------------------------*/
613 PROCEDURE pac_low_level_codes(
614 i_pac_period_id IN NUMBER,
615 i_cost_group_id IN NUMBER,
616 i_start_date IN DATE,
617 i_end_date IN DATE,
618 i_user_id IN NUMBER,
619 i_login_id IN NUMBER,
620 i_request_id IN NUMBER,
621 i_prog_id IN NUMBER,
622 i_prog_app_id IN NUMBER,
623 o_err_num OUT NOCOPY NUMBER,
624 o_err_code OUT NOCOPY VARCHAR2,
625 o_err_msg OUT NOCOPY VARCHAR2)
626 IS
627 l_err_num NUMBER;
628 l_err_code VARCHAR2(240);
629 l_err_msg VARCHAR2(240);
630 l_stmt_num NUMBER;
631 process_error EXCEPTION;
632 BEGIN
633 l_stmt_num := 10;
634 o_err_num := 0;
635 o_err_code := '';
636 o_err_msg := '';
637
638 -- get the assembly components using WRO for fast checking
639
640 get_assembly_components(
641 i_pac_period_id,
642 i_cost_group_id,
643 i_start_date,
644 i_end_date,
645 i_user_id,
646 i_login_id,
647 i_request_id,
648 i_prog_id,
649 i_prog_app_id,
650 1,
651 l_err_num,
652 l_err_code,
653 l_err_msg);
654
655 IF (l_err_num <> 0) THEN
656 raise process_error;
657 END IF;
658
659 calc_low_level_codes(
660 i_pac_period_id,
661 i_cost_group_id,
662 i_start_date,
663 i_end_date,
664 i_user_id,
665 i_login_id,
666 i_request_id,
667 i_prog_id,
668 i_prog_app_id,
669 1,
670 l_err_num,
671 l_err_code,
672 l_err_msg);
673
674 IF (l_err_code = 'LOOP') THEN
675 -- Potential loop, try getting the assembly components
676 -- using MMT for accurate checking
677 get_assembly_components(
678 i_pac_period_id,
679 i_cost_group_id,
680 i_start_date,
681 i_end_date,
682 i_user_id,
683 i_login_id,
684 i_request_id,
685 i_prog_id,
686 i_prog_app_id,
687 2,
688 l_err_num,
689 l_err_code,
690 l_err_msg);
691
692 IF (l_err_num <> 0) THEN
693 raise process_error;
694 END IF;
695
696 calc_low_level_codes(
697 i_pac_period_id,
698 i_cost_group_id,
699 i_start_date,
700 i_end_date,
701 i_user_id,
702 i_login_id,
703 i_request_id,
704 i_prog_id,
705 i_prog_app_id,
706 2,
707 l_err_num,
708 l_err_code,
709 l_err_msg);
710 END IF;
711
712 IF (l_err_num <> 0) THEN
713 raise process_error;
714 END IF;
715 EXCEPTION
716 WHEN process_error THEN
717 o_err_num := l_err_num;
718 o_err_msg := l_err_msg;
719 WHEN others THEN
720 o_err_num := SQLCODE;
721 o_err_msg := 'CSTPPLLC:'
722 || 'pac_low_level_codes:'
723 || to_char(l_stmt_num)
724 || ' '
725 || substr(SQLERRM,1,150);
726 END pac_low_level_codes;
727
728 END CSTPPLLC;