[Home] [Help]
PACKAGE BODY: APPS.CSTPPWRO
Source
1 PACKAGE BODY CSTPPWRO AS
2 /* $Header: CSTPWROB.pls 120.10.12010000.2 2008/10/29 19:23:39 vjavli ship $ */
3
4 /*---------------------------------------------------------------------------*
5 | PRIVATE PROCEDURES/FUNCTIONS |
6 *----------------------------------------------------------------------------*/
7
8 PROCEDURE charge_operation(
9 p_pac_period_id IN NUMBER,
10 p_cost_group_id IN NUMBER,
11 p_cost_type_id IN NUMBER,
12 p_org_id IN NUMBER,
13 p_entity_id IN NUMBER,
14 p_entity_type IN NUMBER,
15 p_line_id IN NUMBER,
16 p_op_seq IN NUMBER,
17 p_pri_qty IN NUMBER,
18 p_forward_flag IN NUMBER,
19 p_user_id IN NUMBER,
20 p_request_id IN NUMBER,
21 p_prog_app_id IN NUMBER,
22 p_prog_id IN NUMBER,
23 p_login_id IN NUMBER,
24 x_err_num OUT NOCOPY NUMBER,
25 x_err_code OUT NOCOPY VARCHAR2,
26 x_err_msg OUT NOCOPY VARCHAR2) ;
27
28 /*---------------------------------------------------------------------------*
29 | PRIVATE PROCEDURE |
30 | charge_operation |
31 *----------------------------------------------------------------------------*/
32 PROCEDURE charge_operation(
33 p_pac_period_id IN NUMBER,
34 p_cost_group_id IN NUMBER,
35 p_cost_type_id IN NUMBER,
36 p_org_id IN NUMBER,
37 p_entity_id IN NUMBER,
38 p_entity_type IN NUMBER,
39 p_line_id IN NUMBER,
40 p_op_seq IN NUMBER,
41 p_pri_qty IN NUMBER,
42 p_forward_flag IN NUMBER,
43 p_user_id IN NUMBER,
44 p_request_id IN NUMBER,
45 p_prog_app_id IN NUMBER,
46 p_prog_id IN NUMBER,
47 p_login_id IN NUMBER,
48 x_err_num OUT NOCOPY NUMBER,
49 x_err_code OUT NOCOPY VARCHAR2,
50 x_err_msg OUT NOCOPY VARCHAR2)
51
52 IS
53
54 l_pri_qty NUMBER;
55 l_err_num NUMBER;
56 l_err_code VARCHAR2(240);
57 l_err_msg VARCHAR2(2000);
58 l_stmt_num NUMBER;
59 cst_process_error EXCEPTION;
60
61 BEGIN
62 ----------------------------------------------------------------------
63 -- Initialize Variables
64 ----------------------------------------------------------------------
65
66 l_err_num := 0;
67 l_err_code := '';
68 l_err_msg := '';
69 l_stmt_num := 5;
70
71 ----------------------------------------------------------------------
72 -- Check Forward flag
73 ----------------------------------------------------------------------
74 IF p_forward_flag = 1 THEN
75 l_pri_qty := p_pri_qty;
76 ELSE
77 l_pri_qty := -1 * p_pri_qty;
78 END IF;
79
80 ----------------------------------------------------------------------
81 -- Check and create , if necessary the WIPBAL record.
82 ----------------------------------------------------------------------
83 l_stmt_num := 10;
84
85 check_pacwip_bal_record (p_pac_period_id => p_pac_period_id,
86 p_cost_group_id => p_cost_group_id,
87 p_cost_type_id => p_cost_type_id,
88 p_org_id => p_org_id,
89 p_entity_id => p_entity_id,
90 p_entity_type => p_entity_type,
91 p_line_id => p_line_id,
92 p_op_seq => p_op_seq,
93 p_user_id => p_user_id,
94 p_request_id => p_request_id,
95 p_prog_app_id => p_prog_app_id,
96 p_prog_id => p_prog_id,
97 p_login_id => p_login_id,
98 x_err_num => l_err_num,
99 x_err_code => l_err_code,
100 x_err_msg => l_err_msg
101 );
102
103 IF (l_err_num <>0) THEN
104
105 l_err_msg := SUBSTR('Fail_check_bal_rec: ent/line/op'
106 ||TO_CHAR(p_entity_id)
107 ||'/'
108 ||TO_CHAR(p_line_id)
109 ||'/'
110 ||TO_CHAR(p_op_seq)
111 ||':'
112 ||l_err_msg,1,2000);
113
114 RAISE CST_PROCESS_ERROR;
115
116 END IF;
117
118 l_stmt_num := 15;
119
120 UPDATE wip_pac_period_balances wppb
121 SET wppb.operation_completed_units
122 = NVL(wppb.operation_completed_units,0) + l_pri_qty,
123 wppb.last_update_date = SYSDATE,
124 wppb.request_id = p_request_id,
125 wppb.program_update_date = SYSDATE
126 WHERE
127 wppb.pac_period_id = p_pac_period_id
128 AND wppb.cost_group_id = p_cost_group_id
129 AND wppb.wip_entity_id = p_entity_id
130 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
131 AND wppb.operation_seq_num = p_op_seq;
132
133 EXCEPTION
134
135 WHEN CST_PROCESS_ERROR THEN
136 x_err_num := l_err_num;
137 x_err_code := l_err_code;
138 x_err_msg := SUBSTR(l_err_msg,1,2000);
139
140 WHEN OTHERS THEN
141 ROLLBACK;
142 x_err_num := SQLCODE;
143 x_err_code := NULL;
144 x_err_msg := SUBSTR('CSTPPWRO.charge_operation('
145 || to_char(l_stmt_num)
146 || '): '
147 ||SQLERRM,1,2000);
148
149 END charge_operation;
150
151 /*---------------------------------------------------------------------------*
152 | PUBLIC PROCEDURE |
153 | check_pacwip_bal_record |
154 *----------------------------------------------------------------------------*/
155 PROCEDURE check_pacwip_bal_record (
156 p_pac_period_id IN NUMBER,
157 p_cost_group_id IN NUMBER,
158 p_cost_type_id IN NUMBER,
159 p_org_id IN NUMBER,
160 p_entity_id IN NUMBER,
161 p_entity_type IN NUMBER,
162 p_line_id IN NUMBER,
163 p_op_seq IN NUMBER,
164 p_user_id IN NUMBER,
165 p_request_id IN NUMBER,
166 p_prog_app_id IN NUMBER,
167 p_prog_id IN NUMBER,
168 p_login_id IN NUMBER,
169 x_err_num OUT NOCOPY NUMBER,
170 x_err_code OUT NOCOPY VARCHAR2,
171 x_err_msg OUT NOCOPY VARCHAR2
172 )
173 IS
174
175 l_stmt_num NUMBER;
176 l_err_num NUMBER;
177 l_err_code NUMBER;
178 l_err_msg NUMBER;
179
180 BEGIN
181
182 ----------------------------------------------------------------------
183 -- Initialize Variables
184 ----------------------------------------------------------------------
185
186 l_err_num := 0;
187 l_err_code := '';
188 l_err_msg := '';
189
190 ----------------------------------------------------------------------
191 -- Create Row if it does not exist
192 ----------------------------------------------------------------------
193
194 l_stmt_num := 5;
195
196 INSERT INTO wip_pac_period_balances
197 (
198 pac_period_id,
199 cost_group_id,
200 cost_type_id,
201 organization_id,
202 wip_entity_id,
203 wip_entity_type,
204 line_id,
205 operation_seq_num,
206 operation_completed_units,
207 relieved_assembly_units,
208 tl_resource_in,
209 tl_resource_out,
210 tl_outside_processing_in,
211 tl_outside_processing_out,
212 tl_overhead_in,
213 tl_overhead_out,
214 pl_material_in,
215 pl_material_out,
216 pl_resource_in,
217 pl_resource_out,
218 pl_overhead_in,
219 pl_overhead_out,
220 pl_outside_processing_in,
221 pl_outside_processing_out,
222 pl_material_overhead_in,
223 pl_material_overhead_out,
224 /*added for _apull columns for bug#3229515*/
225 pl_material_in_apull,
226 pl_resource_in_apull,
227 pl_overhead_in_apull,
228 pl_outside_processing_in_apull,
229 pl_material_overhead_in_apull,
230 /*end of addition for bug#3229515*/
231 last_update_date,
232 last_updated_by,
233 creation_date,
234 created_by,
235 request_id,
236 program_application_id,
237 program_id,
238 program_update_date,
239 last_update_login
240 )
241 SELECT
242 p_pac_period_id,
243 p_cost_group_id,
244 p_cost_type_id,
245 p_org_id,
246 p_entity_id,
247 p_entity_type,
248 decode(p_entity_type, 4, null, p_line_id),
249 p_op_seq,
250 0,
251 0,
252 0,
253 0,
254 0,
255 0,
256 0,
257 0,
258 0,
259 0,
260 0,
261 0,
262 0,
263 0,
264 0,
265 0,
266 0,
267 0,
268 0,
269 0,
270 0,
271 0,
272 0,
273 SYSDATE,
274 p_user_id,
275 SYSDATE,
276 p_user_id,
277 p_request_id,
278 p_prog_app_id,
279 p_prog_id,
280 SYSDATE,
281 p_login_id
282 FROM DUAL
283 WHERE NOT EXISTS
284 ( SELECT 'X'
285 FROM wip_pac_period_balances wppb2
286 WHERE wppb2.pac_period_id = p_pac_period_id
287 AND wppb2.cost_group_id = p_cost_group_id
288 AND wppb2.wip_entity_id = p_entity_id
289 AND NVL(wppb2.line_id,-99) = decode(p_entity_type,4,-99,NVL(p_line_id,-99))
290 AND wppb2.operation_seq_num = p_op_seq
291 );
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 ROLLBACK;
296 x_err_num := SQLCODE;
297 x_err_code := NULL;
298 x_err_msg := SUBSTR('CSTPPWRO.check_pacwip_bal_record('
299 || to_char(l_stmt_num)
300 || '): '
301 ||SQLERRM,1,2000);
302
303 END check_pacwip_bal_record;
304
305
306 /*---------------------------------------------------------------------------*
307 | PUBLIC PROCEDURE |
308 | get_adj_operations |
309 *----------------------------------------------------------------------------*/
310
311 PROCEDURE get_adj_operations (
312 p_entity_id IN NUMBER,
313 p_line_id IN NUMBER,
314 p_rep_sched_id IN NUMBER,
315 p_op_seq IN NUMBER,
316 x_prev_op OUT NOCOPY NUMBER,
317 x_next_op OUT NOCOPY NUMBER,
318 x_err_num OUT NOCOPY NUMBER,
319 x_err_code OUT NOCOPY VARCHAR2,
320 x_err_msg OUT NOCOPY VARCHAR2)
321 IS
322
323 l_stmt_num NUMBER;
324 l_err_num NUMBER;
325 l_err_code VARCHAR2(240);
326 l_err_msg VARCHAR2(2000);
327
328 /*Bug#3136153 - converted to cursor*/
329 CURSOR c_ops IS
330 SELECT wo.previous_operation_seq_num prev_op_seq,
331 wo.next_operation_seq_num next_op_seq
332 FROM wip_operations wo
333 WHERE wo.wip_entity_id = p_entity_id
334 AND NVL(wo.repetitive_schedule_id,-99)
335 = NVL(p_rep_sched_id,-99)
336 AND wo.operation_seq_num = p_op_seq;
337 BEGIN
338
339 l_err_num := 0;
340 l_err_code := '';
341 l_err_msg := '';
342
343 l_stmt_num := 5;
344 OPEN c_ops;
345 FETCH c_ops INTO x_prev_op,x_next_op;
346
347 /*Bug#3136153 - Added the below code to deduce adj. ops. if
348 current op. is deleted*/
349 IF c_ops%NOTFOUND THEN /*current op has been deleted.*/
350 /*Next op. is the min(op_seq). with op_seq > current op.*/
351 SELECT MIN(wo.operation_seq_num) next_op_seq
352 INTO x_next_op
353 FROM wip_operations wo
354 WHERE wo.wip_entity_id = p_entity_id
355 AND NVL(wo.repetitive_schedule_id,-99)
356 = NVL(p_rep_sched_id,-99)
357 AND wo.operation_seq_num > p_op_seq;
358 /*Prev op. is that op. with next_op_seq as the next op. of current op.*/
359 BEGIN
360 SELECT wo.operation_seq_num prev_op_seq
361 INTO x_prev_op
362 FROM wip_operations wo
363 WHERE wo.wip_entity_id = p_entity_id
364 AND NVL(wo.repetitive_schedule_id,-99)
365 = NVL(p_rep_sched_id,-99)
366 AND nvl(wo.next_operation_seq_num,-99) = nvl(x_next_op,-99);
367 EXCEPTION
368 WHEN NO_DATA_FOUND THEN /*This is first op*/
369 x_prev_op := null;
370 END;
371 END IF;
372 CLOSE c_ops;
373 /*End Bug#3136153*/
374 EXCEPTION
375
376 WHEN OTHERS THEN
377 ROLLBACK;
378 IF c_ops%ISOPEN THEN CLOSE c_ops; END IF; /*Bug#3136153*/
379 x_err_num := SQLCODE;
380 x_err_code := NULL;
381 x_err_msg := SUBSTR('CSTPPWRO.get_adj_operations('
382 || to_char(l_stmt_num)
383 || '): '
384 ||SQLERRM,1,2000);
385
386
387 END get_adj_operations;
388
389 /*---------------------------------------------------------------------------*
390 | PRIVATE FUNCTION |
391 | check_operation_exists |
392 | added for bug#3136153 |
393 *----------------------------------------------------------------------------*/
394 FUNCTION check_operation_exists(
395 p_entity_id IN NUMBER,
396 p_rep_sched_id IN NUMBER DEFAULT NULL,
397 p_op_seq IN NUMBER)
398 RETURN BOOLEAN
399 IS
400 l_count NUMBER;
401 BEGIN
402 SELECT count(*)
403 INTO l_count
404 FROM wip_operations wo
405 WHERE wo.wip_entity_id = p_entity_id
406 AND NVL(wo.repetitive_schedule_id,-99)
407 = NVL(p_rep_sched_id,-99)
408 AND wo.operation_seq_num = p_op_seq;
409
410 IF (l_count = 0) THEN
411 return FALSE;
412 ELSE
413 return TRUE;
414 END IF;
415 END;
416
417
418 /*---------------------------------------------------------------------------*
419 | PUBLIC PROCEDURE |
420 | build_wip_operation_qty |
421 *----------------------------------------------------------------------------*/
422 PROCEDURE build_wip_operation_qty(
423 p_pac_period_id IN NUMBER,
424 p_start_date IN DATE,
425 p_end_date IN DATE,
426 p_cost_group_id IN NUMBER,
427 p_cost_type_id IN NUMBER,
428 p_entity_id IN NUMBER,
429 p_entity_type IN NUMBER,
430 p_user_id IN NUMBER,
431 p_login_id IN NUMBER,
432 p_request_id IN NUMBER,
433 p_prog_id IN NUMBER,
434 p_prog_app_id IN NUMBER,
435 x_err_num OUT NOCOPY NUMBER,
436 x_err_code OUT NOCOPY VARCHAR2,
437 x_err_msg OUT NOCOPY VARCHAR2)
438 IS
439
440 l_stmt_num NUMBER;
441 l_err_num NUMBER;
442 l_err_code VARCHAR2(240);
443 l_err_msg VARCHAR2(2000);
444 l_curr_op_seq NUMBER;
445 l_rep_sched_id NUMBER;
446 l_wt NUMBER;
447 l_next_op NUMBER;
448 l_prev_op NUMBER;
449 l_forward_flag NUMBER;
450 l_done NUMBER;
451 l_same_op NUMBER;
452 l_counter NUMBER;
453 l_rec_counter NUMBER;
454 cst_process_error EXCEPTION;
455
456 -------------------------------------------------------------------------------
457 -- For repetitive entites:
458 -- Get the repetitive schedule_id for this move transaction if
459 -- the entity is a rep entity. We need to know the sched id
460 -- cause schedules can have different routing (and operations).
461 -- We need to know exactly which operations of the routing
462 -- are being charged. We can only have rollforward or rollbackward
463 -- between schedules that have the same routing. In other words,
464 -- all schedules in wip_txn_allocations tables for a given
465 -- wip_transaction_id WILL HAVE THE SAME ROUTING.
466 -- WIP_MOVE_TRANSACTIONS table does not store rep schedule_id
467 -- therefore the only way to find the schedule (and thus the
468 -- routing) for which this move transaction is being done is to
469 -- join to wip_txn and wip_txn_alloc tables and get the appropriate
470 -- schedule_id.
471 -- Consider the follwoing two schedule routings for the same line_id:
472 -- 10--->20--->30--->40-->50 :SCHED-1
473 -- 10--->20--------->40------->60 :SCHED-2
474 -- If a move is done from Op20 to Op40 we do not know whether to
475 -- charge the intermediate Op30 unless we know for which schedule
476 -- the move is being done for.
477
478 -------------------------------------------------------------------------------
479
480
481 ------------------------------------------------------------------------------
482 -- This cursor will give you all the move transactions for the given
483 -- entity/line that
484 -- 1. Occured in this period and
485 -- 2. NOT (in same Op Q/R--> Q/R or Rej/S/T --> Rej/S/T)
486 ------------------------------------------------------------------------------
487
488 CURSOR c_wmt IS
489
490 SELECT wmt.organization_id org_id,
491 wmt.transaction_id move_txn_id,
492 wmt.wip_entity_id entity_id,
493 wmt.line_id line_id,
494 wmt.fm_operation_seq_num fm_op_seq,
495 wmt.to_operation_seq_num to_op_seq,
496 wmt.fm_intraoperation_step_type fm_step,
497 wmt.to_intraoperation_step_type to_step,
498 wmt.primary_quantity pri_qty
499 FROM
500 wip_move_transactions wmt
501 WHERE wmt.wip_entity_id = p_entity_id
502 AND wmt.transaction_date BETWEEN TRUNC(p_start_date)
503 AND (TRUNC(p_end_date) + 0.99999)
504 AND NOT ( wmt.fm_operation_seq_num = wmt.to_operation_seq_num
505 AND ( (wmt.fm_intraoperation_step_type <=2
506 AND wmt.to_intraoperation_step_type <= 2)
507 OR (wmt.fm_intraoperation_step_type > 2
508 AND wmt.to_intraoperation_step_type > 2)
509 )
510 );
511
512 BEGIN
513
514 ----------------------------------------------------------------------
515 -- Initialize Variables
516 ----------------------------------------------------------------------
517
518 l_err_num := 0;
519 l_err_code := '';
520 l_err_msg := '';
521 l_rec_counter := 0;
522
523
524 ----------------------------------------------------------------------
525 -- Build operation qty based on this period's moves
526 ----------------------------------------------------------------------
527
528 l_stmt_num := 5;
529
530 FOR c_rec IN c_wmt LOOP
531
532 --------------------------------------------------------------------
533 --For each Move txn, determine whether move is forward or backward
534 --------------------------------------------------------------------
535
536 l_rec_counter := l_rec_counter + 1;
537 l_forward_flag := 1;
538 l_same_op := -1;
539 l_counter := 0;
540 l_done := -1;
541 l_rep_sched_id := -99;
542
543 l_stmt_num := 10;
544 IF c_rec.fm_op_seq > c_rec.to_op_seq THEN
545 l_forward_flag := -1;
546 ELSIF c_rec.fm_op_seq = c_rec.to_op_seq THEN
547 l_same_op := 1;
548 IF c_rec.fm_step > c_rec.to_step THEN
549 l_forward_flag := -1;
550 END IF;
551 END IF;
552
553
554 --------------------------------------------------------------------
555 -- Process all operations
556 --------------------------------------------------------------------
557
558 l_curr_op_seq := c_rec.fm_op_seq;
559
560 l_stmt_num := 15;
561
562 WHILE (l_done = -1 )LOOP
563 l_counter := l_counter + 1;
564 l_next_op := NULL;
565 l_prev_op := NULL;
566
567 /*bug#3136153 - Moved from l_stmt_num := 55*/
568 l_stmt_num := 16;
569 -------------------------------------------------------
570 -- Attempt to find schedule_id if this is a rep entity
571 -- and the schedule has not already been found for
572 -- this record.
573 -------------------------------------------------------
574
575 IF (p_entity_type = 2 AND l_rep_sched_id = -99)
576 THEN
577
578 l_stmt_num := 17;
579
580 SELECT NVL(MAX(wt.transaction_id),-99)
581 INTO l_wt
582 FROM wip_transactions wt
583 WHERE wt.wip_entity_id = p_entity_id
584 AND wt.line_id = c_rec.line_id
585 AND wt.move_transaction_id = c_rec.move_txn_id;
586
587 l_stmt_num := 18;
588
589 SELECT NVL(MAX(wip_alloc.repetitive_schedule_id),-99)
590 INTO l_rep_sched_id
591 FROM wip_txn_allocations wip_alloc
592 WHERE wip_alloc.transaction_id = l_wt;
593
594 END IF; --check to see if it is rep sched
595 /*End of Move from l_stmt_num := 55*/
596
597 --------------------------------------------------------------
598 -- Move is in the same op
599 --------------------------------------------------------------
600 IF l_same_op = 1 THEN
601
602 l_stmt_num := 20;
603
604 l_done := 1;
605
606 ------------------------------------------------------
607 -- forward: charge if fm_step: Q/R to_step: T/S/Re
608 -- back: charged if fm_step T/S/Re to_step: Q/R
609 ------------------------------------------------------
610
611 IF ((c_rec.fm_step <= 2 AND c_rec.to_step > 2 AND
612 l_forward_flag = 1) OR
613 (c_rec.fm_step > 2 AND c_rec.to_step <= 2 AND
614 l_forward_flag = -1 ))
615 THEN
616 l_stmt_num := 23;
617 /*bug#3136153 -added if clause */
618 IF ((p_entity_type = 2 AND l_rep_sched_id = -99)
619 OR
620 (check_operation_exists(p_entity_id=>c_rec.entity_id,
621 p_rep_sched_id=>l_rep_sched_id,
622 p_op_seq=>c_rec.fm_op_seq))
623 ) THEN
624 ----------------------------------------------
625 -- charge from_op_seq
626 ----------------------------------------------
627
628 l_stmt_num := 25;
629 charge_operation(p_pac_period_id,
630 p_cost_group_id,
631 p_cost_type_id,
632 c_rec.org_id,
633 c_rec.entity_id,
634 p_entity_type,
635 c_rec.line_id ,
636 c_rec.fm_op_seq ,
637 c_rec.pri_qty ,
638 l_forward_flag,
639 p_user_id,
640 p_request_id,
641 p_prog_app_id,
642 p_prog_id,
643 p_login_id,
644 l_err_num,
645 l_err_code,
646 l_err_msg);
647
648 IF (l_err_num <>0) THEN
649 RAISE CST_PROCESS_ERROR;
650 END IF;
651 END IF;
652 END IF;
653 ELSE
654
655 --------------------------------------------------------------
656 -- Move is across operations
657 --------------------------------------------------------------
658
659 ------------------------------------------------------
660 -- forward: from_op charged if fm_step Q/R
661 -- back: from_op charged if fm_step T/S/Re
662 ------------------------------------------------------
663
664 l_stmt_num := 30;
665
666 IF (c_rec.fm_op_seq = l_curr_op_seq) THEN
667 IF ((c_rec.fm_step <= 2 AND l_forward_flag = 1)
668 OR (c_rec.fm_step > 2 AND l_forward_flag = -1))
669 THEN
670 l_stmt_num := 33;
671 /*bug#3136153 -added if clase */
672 IF ((p_entity_type = 2 AND l_rep_sched_id = -99)
673 OR
674 (check_operation_exists(p_entity_id=>c_rec.entity_id,
675 p_rep_sched_id=>l_rep_sched_id,
676 p_op_seq=>c_rec.fm_op_seq))
677 )THEN
678 --------------------------------------
679 -- Charge fm_op_seq
680 --------------------------------------
681
682 l_stmt_num := 35;
683 charge_operation(p_pac_period_id,
684 p_cost_group_id,
685 p_cost_type_id,
686 c_rec.org_id,
687 c_rec.entity_id ,
688 p_entity_type,
689 c_rec.line_id ,
690 c_rec.fm_op_seq,
691 c_rec.pri_qty ,
692 l_forward_flag,
693 p_user_id,
694 p_request_id,
695 p_prog_app_id,
696 p_prog_id,
697 p_login_id,
698 l_err_num,
699 l_err_code,
700 l_err_msg);
701
702 IF (l_err_num <>0) THEN
703 RAISE CST_PROCESS_ERROR;
704 END IF;
705 END IF;
706 END IF;
707 ELSE
708
709 ----------------------------------------------
710 -- Check if curr op is the destination op
711 ----------------------------------------------
712
713 l_stmt_num := 40;
714 /*bug#3136153 -added 2 if clauses
715 l_done=1 if we reach/overshoot to_op*/
716 IF (l_curr_op_seq > c_rec.to_op_seq AND
717 l_forward_flag = 1) THEN
718 l_done := 1;
719 ELSIF (l_curr_op_seq < c_rec.to_op_seq AND
720 l_forward_flag = -1) THEN
721 l_done := 1;
722 ELSIF l_curr_op_seq = c_rec.to_op_seq THEN
723 l_done := 1;
724
725 ---------------------------------------
726 -- forward: Charge if to_step T/S/Re
727 -- back: Charge if to_step Q/R
728 ---------------------------------------
729
730 l_stmt_num := 45;
731
732 IF ((c_rec.to_step > 2 AND
733 l_forward_flag = 1 )
734 OR (c_rec.to_step <=2
735 AND l_forward_flag = -1))
736 THEN
737 l_stmt_num := 47;
738 /*bug#3136153 -added if clase */
739 IF ((p_entity_type = 2 AND l_rep_sched_id = -99)
740 OR
741 (check_operation_exists(p_entity_id=>c_rec.entity_id,
742 p_rep_sched_id=>l_rep_sched_id,
743 p_op_seq=>c_rec.to_op_seq))
744 )THEN
745 charge_operation
746 (p_pac_period_id,
747 p_cost_group_id,
748 p_cost_type_id,
749 c_rec.org_id,
750 c_rec.entity_id ,
751 p_entity_type,
752 c_rec.line_id ,
753 c_rec.to_op_seq,
754 c_rec.pri_qty ,
755 l_forward_flag,
756 p_user_id,
757 p_request_id,
758 p_prog_app_id,
759 p_prog_id,
760 p_login_id,
761 l_err_num,
762 l_err_code,
763 l_err_msg);
764
765 IF (l_err_num <>0) THEN
766 RAISE CST_PROCESS_ERROR;
767 END IF;
768 END IF;
769 END IF;
770 ELSE
771
772 ---------------------------------------
773 -- Charge this intermediate curr op
774 -- We ignore the autocharge flag.
775 -- The relief formula is:
776 -- value * txn_qty/op_qty
777 -- We may have subtle inaccuracy
778 -- when autocharge is off and
779 -- a manual move to this op was made
780 -- while there were other moves that
781 -- skipped this operation (i.e. it was
782 -- not charged.) In such case, since,
783 -- we build op_qty for all moves (
784 -- irrespective of autocharge) the
785 -- operation will be underrelieved.
786 -- In summary, the underlying
787 -- assumption is autocharge controls
788 -- op value not the qty.
789 ---------------------------------------
790 l_stmt_num := 49;
791 /*bug#3136153 -added if clase */
792 IF ((p_entity_type = 2 AND l_rep_sched_id = -99)
793 OR
794 (check_operation_exists(p_entity_id=>c_rec.entity_id,
795 p_rep_sched_id=>l_rep_sched_id,
796 p_op_seq=>l_curr_op_seq))
797 )THEN
798 l_stmt_num := 50;
799 charge_operation(p_pac_period_id,
800 p_cost_group_id,
801 p_cost_type_id,
802 c_rec.org_id,
803 c_rec.entity_id ,
804 p_entity_type,
805 c_rec.line_id ,
806 l_curr_op_seq,
807 c_rec.pri_qty ,
808 l_forward_flag,
809 p_user_id,
810 p_request_id,
811 p_prog_app_id,
812 p_prog_id,
813 p_login_id,
814 l_err_num,
815 l_err_code,
816 l_err_msg);
817
818 IF (l_err_num <>0) THEN
819 RAISE CST_PROCESS_ERROR;
820 END IF;
821 END IF;
822 END IF;
823 END IF; -- Check for first op
824
825 -------------------------------------------------------
826 -- forward: get Next operation
827 -- back: get previous operation
828 -------------------------------------------------------
829 /*bug#3136153 - Moved l_stmt_num 55,60,65 to 16,17,18 */
830 -------------------------------------------------------
831 -- Call next operation only if sched was found for line
832 -- or if the entity is not a repetitive entity
833 -------------------------------------------------------
834
835 l_stmt_num := 70;
836
837 IF (p_entity_type = 2 AND l_rep_sched_id= -99)
838 THEN
839
840 ----------------------------------------------
841 -- This means that we could not obtain
842 -- the repetitive scedule for this move.
843 -- Therefore, we will charge to_op and stop.
844 -- The fm_op has already been charged.
845 ----------------------------------------------
846
847 l_stmt_num := 75;
848 /* Bug 2670917. Changes for Bug 2624568
849 * Check forward_flag before assigning
850 * next operation */
851
852 IF l_forward_flag = 1 THEN
853
854 l_next_op := c_rec.to_op_seq;
855 l_prev_op := c_rec.fm_op_seq;
856 ELSE
857 l_next_op := c_rec.fm_op_seq;
858 l_prev_op := c_rec.to_op_seq;
859 END IF;
860
861 ELSE
862 l_stmt_num := 80;
863
864 get_adj_operations (p_entity_id,
865 c_rec.line_id,
866 l_rep_sched_id,
867 l_curr_op_seq,
868 l_prev_op,
869 l_next_op,
870 l_err_num,
871 l_err_code,
872 l_err_msg);
873
874 IF (l_err_num <> 0) THEN
875 RAISE CST_PROCESS_ERROR;
876 END IF;
877
878 END IF; -- check line_id/rep_sched_id
879
880 l_stmt_num := 85;
881 /*bug#3136153 -
882 If we are reaching here, for curr move txn we are yet to
883 reach/overshoot to_op.Need to exit if nowhere to go.
884 This scenario is possible if first/last op was deleted.*/
885 IF ((l_forward_flag=1 AND l_next_op IS NULL)
886 OR
887 (l_forward_flag=-1 AND l_prev_op IS NULL))
888 THEN
889 l_done := 1;
890 END IF;
891
892 IF l_forward_flag = 1 THEN
893 l_curr_op_seq := l_next_op;
894 ELSE
895 l_curr_op_seq := l_prev_op;
896 END IF;
897
898 END IF; -- Move is past operations
899 END LOOP; -- While loop for all intermediate ops
900 END LOOP; -- Cursor loop for each move txn
901
902
903 EXCEPTION
904
905 WHEN CST_PROCESS_ERROR THEN
906 x_err_num := l_err_num;
907 x_err_code := l_err_code;
908 x_err_msg := l_err_msg;
909
910 WHEN OTHERS THEN
911 ROLLBACK;
912 x_err_num := SQLCODE;
913 x_err_code := NULL;
914 x_err_msg := SUBSTR('CSTPPWRO.build_wip_operation_qty('
915 || to_char(l_stmt_num)
916 || '): '
917 ||SQLERRM,1,2000);
918
919 END build_wip_operation_qty;
920
921
922 /*---------------------------------------------------------------------------*
923 | PUBLIC PROCEDURE |
924 | process_wip_resovhd_txns |
925 *----------------------------------------------------------------------------*/
926 PROCEDURE process_wip_resovhd_txns(
927 p_pac_period_id IN NUMBER,
928 p_start_date IN DATE,
929 p_end_date IN DATE,
930 p_cost_group_id IN NUMBER,
931 p_cost_type_id IN NUMBER,
932 p_item_id IN NUMBER,
933 p_pac_ct_id IN NUMBER,
934 p_user_id IN NUMBER,
935 p_login_id IN NUMBER,
936 p_request_id IN NUMBER,
937 p_prog_id IN NUMBER,
938 p_prog_app_id IN NUMBER,
939 x_err_num OUT NOCOPY NUMBER,
940 x_err_code OUT NOCOPY VARCHAR2,
941 x_err_msg OUT NOCOPY VARCHAR2)
942
943 IS
944
945 l_acq_cost NUMBER;
946 l_cfm_flag NUMBER;
947 l_open_flag VARCHAR2(1);
948 l_cost_element NUMBER;
949 l_rbo_value NUMBER;
950 l_stmt_num NUMBER;
951 l_err_num NUMBER;
952 l_err_code VARCHAR2(240);
953 l_err_msg VARCHAR2(2000);
954 l_pending_txns NUMBER;
955 cst_process_error EXCEPTION;
956 cst_fail_acq_cost EXCEPTION;
957 cst_wip_pending_txns EXCEPTION;
958
959 -- Variables added for eAM Support in PAC
960 l_return_status VARCHAR2(1);
961 l_msg_count NUMBER := 0;
962 l_msg_data VARCHAR2(8000);
963 l_legal_entity_id NUMBER;
964 l_eam_cost_element NUMBER;
965
966 l_applied_value NUMBER;
967 l_actual_cost NUMBER;
968 l_del_qty NUMBER;
969
970 -- Cursor to get all wip_entities in the Cost Group that has move transactions
971
972 CURSOR c_wip_entities_mov IS
973 SELECT we.wip_entity_id entity_id,
974 we.entity_type entity_type
975 FROM wip_entities we,
976 cst_cost_group_assignments ccga
977 WHERE ccga.cost_group_id = p_cost_group_id
978 AND we.organization_id = ccga.organization_id
979 AND we.entity_type <> 4 -- NOT CFM
980 AND we.primary_item_id IS NOT NULL
981 AND EXISTS
982 ( SELECT 'X'
983 FROM wip_move_transactions wmt
984 WHERE wmt.wip_entity_id = we.wip_entity_id
985 AND wmt.transaction_date BETWEEN
986 TRUNC(p_start_date) AND
987 (TRUNC(p_end_date) + 0.99999)
988 );
989
990 -- Cursor to get all wip_entities in the Cost Group that has resource transactions
991
992 CURSOR c_wip_entities_res IS
993 SELECT we.wip_entity_id entity_id,
994 we.entity_type entity_type,
995 we.organization_id org_id
996 FROM wip_entities we,
997 cst_cost_group_assignments ccga
998 WHERE ccga.cost_group_id = p_cost_group_id
999 AND we.organization_id = ccga.organization_id
1000 AND EXISTS
1001 ( SELECT 'X'
1002 FROM wip_transactions wt
1003 WHERE wt.wip_entity_id = we.wip_entity_id
1004 AND wt.transaction_type IN (1,2,3,17)
1005 -- Direct Item txns should be picked too.
1006 AND wt.transaction_date BETWEEN
1007 TRUNC(p_start_date) AND
1008 (TRUNC(p_end_date) + 0.99999)
1009 AND ccga.organization_id = wt.organization_id
1010 );
1011
1012 -- Cursor to process resource/OSP transaction for a given wip_entity/cost_type
1013 -- It will get all resource/osp type records from WIP_TRANSACTIONS where the
1014 -- resource/osp has a non zero rate defined for the PAC_RATES cost type.
1015
1016 CURSOR c_res_txn ( p_entity_id NUMBER) IS
1017 SELECT wt.transaction_id txn_id,
1018 wt.organization_id org_id,
1019 wt.wip_entity_id entity_id,
1020 wt.line_id line_id,
1021 wt.department_id dept_id,
1022 wt.operation_seq_num op_seq,
1023 wt.resource_id resource_id,
1024 wt.resource_seq_num resource_seq_num, -- Added for eAM project
1025 crc.resource_rate actual_cost,
1026 wt.transaction_type wip_txn_type,
1027 wt.primary_quantity applied_units,
1028 wt.primary_quantity*nvl(crc.resource_rate,0) applied_value,
1029 wt.rcv_transaction_id -- Added for 4735668
1030 FROM wip_transactions wt,
1031 cst_resource_costs crc,
1032 bom_resources br /* Bug 4641635; Include resources which are to be costed */
1033
1034 WHERE wt.wip_entity_id = p_entity_id
1035 AND wt.transaction_date BETWEEN TRUNC(p_start_date)
1036 AND (TRUNC(p_end_date) + 0.99999)
1037 AND wt.transaction_type = 1 -- RES
1038 /* fix for bug 2988196 */
1039 AND crc.resource_id(+) = wt.resource_id
1040 AND crc.organization_id(+) = wt.organization_id
1041 AND crc.cost_type_id(+) = p_pac_ct_id
1042 -- AND crc.resource_rate <> 0
1043 /*Bug 4641635: Exclude resources with allow_costs_flag set to no*/
1044 AND br.resource_id = wt.resource_id
1045 AND br.allow_costs_flag = 1
1046 UNION ALL
1047 SELECT wt.transaction_id txn_id,
1048 wt.organization_id org_id,
1049 wt.wip_entity_id entity_id,
1050 wt.line_id line_id,
1051 wt.department_id dept_id,
1052 wt.operation_seq_num op_seq,
1053 wt.resource_id resource_id,
1054 wt.resource_seq_num resource_seq_num, -- Added for eAM project
1055 0 actual_cost,
1056 wt.transaction_type wip_txn_type,
1057 wt.primary_quantity applied_units,
1058 0 applied_value,
1059 wt.rcv_transaction_id -- Added for 4735668
1060 FROM wip_transactions wt,
1061 bom_resources br /* Bug 4641635; Include resources which are to be costed */
1062 WHERE wt.wip_entity_id = p_entity_id
1063 AND wt.transaction_date BETWEEN TRUNC(p_start_date)
1064 AND (TRUNC(p_end_date) + 0.99999)
1065 AND wt.transaction_type = 3 -- OSP
1066 /*Bug 4641635: Exclude resources with allow_costs_flag set to no */
1067 AND br.resource_id = wt.resource_id
1068 AND br.allow_costs_flag = 1
1069 UNION ALL
1070 /* Bug:4641635 Separating out the query for Direct Items */
1071 SELECT wt.transaction_id txn_id,
1072 wt.organization_id org_id,
1073 wt.wip_entity_id entity_id,
1074 wt.line_id line_id,
1075 wt.department_id dept_id,
1076 wt.operation_seq_num op_seq,
1077 wt.resource_id resource_id,
1078 wt.resource_seq_num resource_seq_num, -- Added for eAM project
1079 0 actual_cost,
1080 wt.transaction_type wip_txn_type,
1081 nvl(wt.primary_quantity, rt.amount) applied_units, /* Bug 4180323*/
1082 0 applied_value,
1083 wt.rcv_transaction_id -- Added for 4735668
1084 FROM wip_transactions wt,
1085 rcv_transactions rt
1086 WHERE wt.wip_entity_id = p_entity_id
1087 AND rt.transaction_id = wt.rcv_transaction_id
1088 AND wt.transaction_date BETWEEN TRUNC(p_start_date)
1089 AND (TRUNC(p_end_date) + 0.99999)
1090 AND wt.transaction_type = 17; -- Direct Items
1091
1092
1093
1094 -- Cursor to obtain all resource based overheads for a given org/dept/res/ct
1095
1096 CURSOR c_rbo ( p_resource_id NUMBER,
1097 p_dept_id NUMBER,
1098 p_org_id NUMBER,
1099 p_res_units NUMBER,
1100 p_res_value NUMBER) IS
1101
1102 SELECT cdo.overhead_id ovhd_id,
1103 cdo.rate_or_amount actual_cost,
1104 cdo.basis_type basis_type,
1105 cdo.rate_or_amount * decode(cdo.basis_type,
1106 3, p_res_units,
1107 p_res_value) applied_value,
1108 decode(cdo.basis_type, 3, p_res_units,
1109 p_res_value) basis_units
1110 FROM cst_resource_overheads cro,
1111 cst_department_overheads cdo
1112 WHERE cdo.department_id = p_dept_id
1113 AND cdo.organization_id = p_org_id
1114 AND cdo.cost_type_id = p_pac_ct_id
1115 AND cdo.basis_type IN (3,4)
1116 AND cro.cost_type_id = cdo.cost_type_id
1117 AND cro.resource_id = p_resource_id
1118 AND cro.overhead_id = cdo.overhead_id
1119 AND cro.organization_id = cdo.organization_id;
1120
1121 -- Cursor to process move based ovhd transaction for a given entity/cost_type
1122 -- It will get all overhead type records from WIP_TRANSACTIONS where the
1123 -- overhead has a non zero rate defined for the PAC_RATES cost type.
1124
1125 CURSOR c_mbo_txn( p_entity_id NUMBER) IS
1126 SELECT wt.transaction_id txn_id,
1127 wt.organization_id org_id,
1128 wt.wip_entity_id entity_id,
1129 wt.line_id line_id,
1130 wt.operation_seq_num op_seq,
1131 wt.department_id dept_id,
1132 wta.resource_id overhead_id,
1133 cdo.rate_or_amount actual_cost,
1134 wt.basis_type basis_type,
1135 wt.primary_quantity*cdo.rate_or_amount applied_value
1136 FROM
1137 wip_transactions wt,
1138 wip_transaction_accounts wta,
1139 cst_department_overheads cdo
1140 WHERE wt.wip_entity_id = p_entity_id
1141 AND wt.transaction_date BETWEEN TRUNC(p_start_date)
1142 AND (TRUNC(p_end_date) + 0.99999)
1143 AND wt.transaction_type = 2 -- MBO
1144 AND wta.transaction_id = wt.transaction_id
1145 AND wta.accounting_line_type = 3
1146 AND wta.cost_element_id = 5
1147 AND cdo.department_id = wt.department_id
1148 AND cdo.overhead_id = wta.resource_id
1149 AND cdo.cost_type_id = p_pac_ct_id
1150 AND NVL(wta.repetitive_schedule_id,-99) =
1151 (SELECT NVL(MAX(wip_alloc.repetitive_schedule_id),-99)
1152 FROM wip_txn_allocations wip_alloc
1153 WHERE wip_alloc.transaction_id = wt.transaction_id
1154 AND rownum = 1
1155 )
1156 AND cdo.rate_or_amount <> 0
1157 ORDER BY wt.transaction_id;
1158
1159 BEGIN
1160
1161 ----------------------------------------------------------------------
1162 -- Initialize Variables
1163 ----------------------------------------------------------------------
1164
1165 l_err_num := 0;
1166 l_err_code := '';
1167 l_err_msg := '';
1168 l_acq_cost := 0;
1169 l_pending_txns := 0;
1170
1171 l_return_status := FND_API.G_RET_STS_SUCCESS;
1172 l_msg_count := 0;
1173 l_msg_data := '';
1174 l_legal_entity_id := 0 ;
1175 l_eam_cost_element := 0;
1176
1177
1178
1179 ----------------------------------------------------------------------
1180 -- Error out if there are any pending txns
1181 ----------------------------------------------------------------------
1182
1183 l_stmt_num := 5;
1184
1185 SELECT count(*)
1186 INTO l_pending_txns
1187 FROM wip_cost_txn_interface wcti
1188 WHERE wcti.transaction_date BETWEEN TRUNC(p_start_date)
1189 AND (TRUNC(p_end_date) + 0.99999)
1190 AND wcti.transaction_type IN (1,2,3,6)
1191 AND EXISTS (
1192 SELECT 'X'
1193 FROM cst_cost_group_assignments ccga
1194 WHERE ccga.cost_group_id = p_cost_group_id
1195 AND ccga.organization_id = wcti.organization_id
1196 );
1197
1198 IF (l_pending_txns <> 0) THEN
1199
1200 RAISE CST_WIP_PENDING_TXNS;
1201
1202 END IF;
1203
1204 -- Select the legal entity for the pac period, to be used in eAM part
1205 SELECT legal_entity
1206 INTO l_legal_entity_id
1207 FROM cst_pac_periods
1208 WHERE pac_period_id = p_pac_period_id;
1209
1210 l_stmt_num := 7;
1211
1212 ----------------------------------------------------------------------
1213 -- Process Non-CFM WIP entities with move transactions
1214 ----------------------------------------------------------------------
1215 l_stmt_num := 10;
1216
1217 FOR c_ent_mov_rec IN c_wip_entities_mov LOOP
1218 ------------------------------------------------------
1219 -- Update op qty snapshot for this entity based on
1220 -- this period's move txns.
1221 ------------------------------------------------------
1222
1223 l_stmt_num := 15;
1224
1225 build_wip_operation_qty (
1226 p_pac_period_id => p_pac_period_id ,
1227 p_start_date => p_start_date,
1228 p_end_date => p_end_date,
1229 p_cost_group_id => p_cost_group_id,
1230 p_cost_type_id => p_cost_type_id,
1231 p_entity_id => c_ent_mov_rec.entity_id,
1232 p_entity_type => c_ent_mov_rec.entity_type,
1233 p_user_id => p_user_id,
1234 p_login_id => p_login_id,
1235 p_request_id => p_request_id,
1236 p_prog_id => p_prog_id,
1237 p_prog_app_id => p_prog_app_id,
1238 x_err_num => l_err_num,
1239 x_err_code => l_err_code,
1240 x_err_msg => l_err_msg);
1241
1242 IF (l_err_num <> 0) THEN
1243 l_err_msg := SUBSTR('fail_build_wip_op entity: '
1244 ||TO_CHAR(c_ent_mov_rec.entity_id)
1245 ||':'
1246 ||l_err_msg,1,2000);
1247 RAISE CST_PROCESS_ERROR;
1248 END IF;
1249
1250 END LOOP;
1251
1252 ----------------------------------------------------------------------
1253 -- Process WIP entities with resource transactions
1254 ----------------------------------------------------------------------
1255 l_stmt_num := 20;
1256
1257 FOR c_ent_rec IN c_wip_entities_res LOOP
1258
1259 --------------------------------------------------------------
1260 -- Get Resource/OSP (and RBO) Transactions
1261 --------------------------------------------------------------
1262
1263 l_stmt_num := 25;
1264
1265 FOR c_res_rec IN c_res_txn(c_ent_rec.entity_id) LOOP
1266
1267 l_stmt_num := 30;
1268
1269 check_pacwip_bal_record (p_pac_period_id => p_pac_period_id,
1270 p_cost_group_id => p_cost_group_id,
1271 p_cost_type_id => p_cost_type_id,
1272 p_org_id => c_res_rec.org_id,
1273 p_entity_id => c_res_rec.entity_id,
1274 p_entity_type => c_ent_rec.entity_type,
1275 p_line_id => c_res_rec.line_id,
1276 p_op_seq => c_res_rec.op_seq,
1277 p_user_id => p_user_id,
1278 p_request_id => p_request_id,
1279 p_prog_app_id => p_prog_app_id,
1280 p_prog_id => p_prog_id,
1281 p_login_id => p_login_id,
1282 x_err_num => l_err_num,
1283 x_err_code => l_err_code,
1284 x_err_msg => l_err_msg
1285 );
1286
1287 IF (l_err_num <> 0) THEN
1288 l_err_msg := SUBSTR('fail_bal_rec entity: '
1289 ||TO_CHAR(c_res_rec.entity_id)
1290 ||':'
1291 ||l_err_msg,1,2000);
1292 RAISE CST_PROCESS_ERROR;
1293 END IF;
1294
1295 l_stmt_num := 35;
1296
1297 IF c_res_rec.wip_txn_type = 1 THEN --RES
1298
1299 l_stmt_num := 40;
1300
1301 UPDATE wip_pac_period_balances wppb
1302 SET tl_resource_in = NVL(tl_resource_in,0) +
1303 c_res_rec.applied_value,
1304 request_id = p_request_id,
1305 last_update_date = SYSDATE,
1306 program_update_date = SYSDATE
1307 WHERE wppb.pac_period_id = p_pac_period_id
1308 AND wppb.cost_group_id = p_cost_group_id
1309 AND wppb.wip_entity_id = c_res_rec.entity_id
1310 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1311 AND wppb.operation_seq_num = c_res_rec.op_seq;
1312
1313 /* Bug 4180323- Initializing the variables so that correct value is inserted into
1314 wip_pac_actual_cost_details table
1315 */
1316
1317 l_actual_cost := c_res_rec.actual_cost;
1318 l_applied_value := c_res_rec.applied_value;
1319
1320 -- Check if eAM entity then compute actuals
1321 IF c_ent_rec.entity_type in (6,7) THEN /* Also include closed WO for Actuals Bug 5366094 */
1322 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
1323 p_api_version => 1.0,
1324 x_return_status => l_return_status,
1325 x_msg_count => l_msg_count,
1326 x_msg_data => l_msg_data,
1327 p_legal_entity_id => l_legal_entity_id,
1328 p_cost_group_id => p_cost_group_id,
1329 p_cost_type_id => p_cost_type_id,
1330 p_pac_period_id => p_pac_period_id,
1331 p_pac_ct_id => p_pac_ct_id,
1332 p_organization_id => c_res_rec.org_id,
1333 p_txn_mode => 2,
1334 p_txn_id => c_res_rec.txn_id,
1335 p_value => c_res_rec.applied_value,
1336 p_wip_entity_id => c_res_rec.entity_id,
1337 p_op_seq => c_res_rec.op_seq,
1338 p_resource_id => c_res_rec.resource_id,
1339 p_resource_seq_num => c_res_rec.resource_seq_num,
1340 p_user_id => p_user_id,
1341 p_request_id => p_request_id,
1342 p_prog_app_id => p_prog_app_id,
1343 p_prog_id => p_prog_id,
1344 p_login_id => p_login_id);
1345
1346 l_stmt_num := 43;
1347
1348 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1349 l_err_msg := SUBSTR('fail_RES_PAC_comt_jobAct ent/res_id : '
1350 ||TO_CHAR(c_res_rec.entity_id)
1351 ||'/'
1352 ||TO_CHAR(c_res_rec.resource_id)
1353 ||': (' || to_char(l_stmt_num) || '): '
1354 ||l_err_msg,1,2000);
1355 RAISE CST_PROCESS_ERROR;
1356 END IF;
1357
1358 End if; -- end eAM check
1359
1360 ELSIF c_res_rec.wip_txn_type = 3 THEN --OSP
1361
1362 ------------------------------------------------------------
1363 -- Call acquisition cost Procedure to get the per unit
1364 -- cost of this osp receipt transaction.
1365 ------------------------------------------------------------
1366
1367 l_stmt_num := 45;
1368 CSTPPACQ.get_acq_cost(
1369 i_cost_group_id => p_cost_group_id,
1370 i_cost_type_id => p_cost_type_id,
1371 i_txn_id => c_res_rec.txn_id,
1372 i_wip_inv_flag => 'W',
1373 o_acq_cost => l_acq_cost,
1374 o_err_num => l_err_num,
1375 o_err_code => l_err_code,
1376 o_err_msg => l_err_msg);
1377
1378
1379 IF (l_err_num <>0) THEN
1380 l_err_msg := SUBSTR('fail_acq_cost txn: '
1381 ||TO_CHAR(c_res_rec.txn_id)
1382 ||' '
1383 ||l_err_msg,1,2000);
1384 RAISE CST_FAIL_ACQ_COST;
1385 END IF;
1386
1387
1388 l_stmt_num := 47;
1389
1390 l_acq_cost := NVL(l_acq_cost,0);
1391 l_actual_cost := l_acq_cost;
1392
1393 /* Added for bug4735668 to get the primary quantity of deliver
1394 as acquistion cost would be in the UOM of receipt */
1395 SELECT rt.primary_quantity
1396 INTO l_del_qty
1397 FROM RCV_TRANSACTIONS RT
1398 WHERE rt.transaction_id = c_res_rec.rcv_transaction_id;
1399
1400 /* FP Bug 7346249 fix: check for -ve quantities */
1401 IF Sign(c_res_rec.applied_units) = -1 THEN
1402 l_del_qty := -1 * l_del_qty;
1403 END IF;
1404
1405 l_applied_value := l_acq_cost * l_del_qty;
1406
1407
1408 l_stmt_num := 50;
1409
1410 UPDATE wip_pac_period_balances wppb
1411 SET tl_outside_processing_in =
1412 NVL(tl_outside_processing_in,0) +
1413 l_applied_value,
1414 request_id = p_request_id,
1415 last_update_date = SYSDATE,
1416 program_update_date = SYSDATE
1417 WHERE wppb.pac_period_id = p_pac_period_id
1418 AND wppb.cost_group_id = p_cost_group_id
1419 AND wppb.wip_entity_id = c_res_rec.entity_id
1420 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1421 AND wppb.operation_seq_num = c_res_rec.op_seq;
1422
1423 -- Check if eAM entity then compute actuals
1424 IF c_ent_rec.entity_type in (6,7) THEN /* Also include closed WO for Actuals Bug 5366094*/
1425
1426 l_stmt_num := 52;
1427
1428 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
1429 p_api_version => 1.0,
1430 x_return_status => l_return_status,
1431 x_msg_count => l_msg_count,
1432 x_msg_data => l_msg_data,
1433 p_legal_entity_id => l_legal_entity_id,
1434 p_cost_group_id => p_cost_group_id,
1435 p_cost_type_id => p_cost_type_id,
1436 p_pac_period_id => p_pac_period_id,
1437 p_pac_ct_id => p_pac_ct_id,
1438 p_organization_id => c_res_rec.org_id,
1439 p_txn_mode => 2,
1440 p_txn_id => c_res_rec.txn_id,
1441 p_value => l_applied_value,
1442 p_wip_entity_id => c_res_rec.entity_id,
1443 p_op_seq => c_res_rec.op_seq,
1444 p_resource_id => c_res_rec.resource_id,
1445 p_resource_seq_num => c_res_rec.resource_seq_num,
1446 p_user_id => p_user_id,
1447 p_request_id => p_request_id,
1448 p_prog_app_id => p_prog_app_id,
1449 p_prog_id => p_prog_id,
1450 p_login_id => p_login_id);
1451 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1452 l_err_msg := SUBSTR('fail_OSP_PAC_comt_jobAct ent/res_id : '
1453 ||TO_CHAR(c_res_rec.entity_id)
1454 ||'/'
1455 ||TO_CHAR(c_res_rec.resource_id)
1456 ||': (' || to_char(l_stmt_num) || '): '
1457 ||l_err_msg,1,2000);
1458 RAISE CST_PROCESS_ERROR;
1459 END IF;
1460
1461 END IF; -- end eAM check
1462
1463 END IF;
1464
1465 -- Check if eAM entity and Direct Item txn
1466 IF (c_ent_rec.entity_type in (6,7) /* Also include closed WO for Actuals Bug 5366094 */
1467 AND c_res_rec.wip_txn_type = 17) THEN
1468
1469 CSTPPACQ.get_acq_cost(
1470 i_cost_group_id => p_cost_group_id,
1471 i_cost_type_id => p_cost_type_id,
1472 i_txn_id => c_res_rec.txn_id,
1473 i_wip_inv_flag => 'W',
1474 o_acq_cost => l_acq_cost,
1475 o_err_num => l_err_num,
1476 o_err_code => l_err_code,
1477 o_err_msg => l_err_msg);
1478
1479 IF (l_err_num <>0) THEN
1480 l_err_msg := SUBSTR('fail_acq_cost txn: '
1481 ||TO_CHAR(c_res_rec.txn_id) ||' '
1482 ||l_err_msg,1,2000);
1483 RAISE CST_FAIL_ACQ_COST;
1484 END IF;
1485
1486
1487 l_stmt_num := 54;
1488
1489 l_acq_cost :=NVL(l_acq_cost,0);
1490 l_actual_cost := l_acq_cost;
1491
1492 l_applied_value := l_acq_cost * c_res_rec.applied_units;
1493
1494 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
1495 p_api_version => 1.0,
1496 x_return_status => l_return_status,
1497 x_msg_count => l_msg_count,
1498 x_msg_data => l_msg_data,
1499 p_legal_entity_id => l_legal_entity_id,
1500 p_cost_group_id => p_cost_group_id,
1501 p_cost_type_id => p_cost_type_id,
1502 p_pac_period_id => p_pac_period_id,
1503 p_pac_ct_id => p_pac_ct_id,
1504 p_organization_id => c_res_rec.org_id,
1505 p_txn_mode => 17,
1506 p_txn_id => c_res_rec.txn_id,
1507 p_value => l_applied_value,
1508 p_wip_entity_id => c_res_rec.entity_id,
1509 p_op_seq => c_res_rec.op_seq,
1510 p_resource_id => c_res_rec.resource_id,
1511 p_resource_seq_num => c_res_rec.resource_seq_num,
1512 p_user_id => p_user_id,
1513 p_request_id => p_request_id,
1514 p_prog_app_id => p_prog_app_id,
1515 p_prog_id => p_prog_id,
1516 p_login_id => p_login_id);
1517
1518 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1519 l_err_msg := SUBSTR('fail_DIR_PAC_comt_jobAct ent/res_id : '
1520 ||TO_CHAR(c_res_rec.entity_id)
1521 ||'/'
1522 ||TO_CHAR(c_res_rec.resource_id)
1523 ||': (' || to_char(l_stmt_num) || '): '
1524 ||l_err_msg,1,2000);
1525 RAISE CST_PROCESS_ERROR;
1526 END IF;
1527
1528 -- get the direct item cost element
1529 CST_EAMCOST_PUB.get_CostEle_for_DirectItem (
1530 p_api_version => 1.0,
1531 x_return_status => l_return_status,
1532 x_msg_count => l_msg_count,
1533 x_msg_data => l_msg_data,
1534 p_txn_id => c_res_rec.txn_id,
1535 p_mnt_or_mfg => 2,
1536 p_pac_or_perp => 1,
1537 x_cost_element_id => l_eam_cost_element);
1538
1539 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1540 l_err_msg := SUBSTR('fail_get_CostEle_for_DirectItem txn_id : '
1541 ||TO_CHAR(c_res_rec.txn_id)
1542 ||': (' || to_char(l_stmt_num) || '): '
1543 ||l_err_msg,1,2000);
1544 RAISE CST_PROCESS_ERROR;
1545 END IF;
1546
1547 l_stmt_num := 56;
1548
1549
1550 /* Check for cost element of direct item to update the required
1551 colimns. */
1552
1553 IF l_eam_cost_element = 1 THEN
1554
1555 /* Update PL-material for materials */
1556 UPDATE wip_pac_period_balances wppb
1557 SET pl_material_in =
1558 NVL(pl_material_in,0) +
1559 l_applied_value,
1560 request_id = p_request_id,
1561 last_update_date = SYSDATE,
1562 program_update_date = SYSDATE
1563 WHERE wppb.pac_period_id = p_pac_period_id
1564 AND wppb.cost_group_id = p_cost_group_id
1565 AND wppb.wip_entity_id = c_res_rec.entity_id
1566 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1567 AND wppb.operation_seq_num = c_res_rec.op_seq;
1568
1569 l_stmt_num := 58;
1570
1571 ELSIF l_eam_cost_element = 3 THEN
1572
1573 /* Update PL-resource for Resources */
1574 UPDATE wip_pac_period_balances wppb
1575 SET pl_resource_in = NVL(pl_resource_in,0)
1576 + l_applied_value,
1577 request_id = p_request_id,
1578 last_update_date = SYSDATE,
1579 program_update_date = SYSDATE
1580 WHERE wppb.pac_period_id = p_pac_period_id
1581 AND wppb.cost_group_id = p_cost_group_id
1582 AND wppb.wip_entity_id = c_res_rec.entity_id
1583 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1584 AND wppb.operation_seq_num = c_res_rec.op_seq;
1585
1586 l_stmt_num := 60;
1587
1588 ELSE
1589
1590 /* Update PL-OSP cols for OSP */
1591 UPDATE wip_pac_period_balances wppb
1592 SET pl_outside_processing_in =
1593 NVL (pl_outside_processing_in,0) +
1594 l_applied_value,
1595 request_id = p_request_id,
1596 last_update_date = SYSDATE,
1597 program_update_date = SYSDATE
1598 WHERE wppb.pac_period_id = p_pac_period_id
1599 AND wppb.cost_group_id = p_cost_group_id
1600 AND wppb.wip_entity_id = c_res_rec.entity_id
1601 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1602 AND wppb.operation_seq_num = c_res_rec.op_seq;
1603
1604 l_stmt_num := 62;
1605
1606 END IF; -- IF l_eam_cost_element
1607
1608 END IF; -- END for Direct Items
1609
1610 --------------------------------------------------------------
1611 -- Insert Res/OSP txn costs in WPTCD
1612 --------------------------------------------------------------
1613
1614 l_stmt_num := 64;
1615
1616 INSERT INTO wip_pac_actual_cost_details
1617 (
1618 pac_period_id,
1619 cost_group_id,
1620 cost_type_id,
1621 transaction_id,
1622 level_type,
1623 cost_element_id,
1624 resource_id,
1625 basis_resource_id,
1626 transaction_costed_date,
1627 actual_cost,
1628 actual_value,
1629 last_update_date,
1630 last_updated_by,
1631 creation_date,
1632 created_by,
1633 request_id,
1634 program_application_id,
1635 program_id,
1636 program_update_date,
1637 last_update_login
1638 )
1639 SELECT
1640 p_pac_period_id,
1641 p_cost_group_id,
1642 p_cost_type_id,
1643 c_res_rec.txn_id,
1644 1, -- Level Type
1645 decode(c_res_rec.wip_txn_type,
1646 1, 3,
1647 3, 4,
1648 17,l_eam_cost_element), -- Cost element_id
1649 -- Insert Direct Item cost element id here too.
1650 c_res_rec.resource_id,
1651 NULL, -- basis_res_id
1652 SYSDATE,
1653 l_actual_cost,
1654 l_applied_value,
1655 SYSDATE,
1656 p_user_id,
1657 SYSDATE,
1658 p_user_id,
1659 p_request_id,
1660 p_prog_app_id,
1661 p_prog_id,
1662 SYSDATE,
1663 p_login_id
1664 FROM DUAL;
1665
1666 --------------------------------------------------------------
1667 -- Get all Res Based Ovhds for this resource/dept/ct
1668 --------------------------------------------------------------
1669
1670 l_stmt_num := 66;
1671
1672 FOR c_rbo_rec IN c_rbo (c_res_rec.resource_id,
1673 c_res_rec.dept_id,
1674 c_res_rec.org_id,
1675 c_res_rec.applied_units,
1676 l_applied_value)
1677 LOOP
1678 IF (c_rbo_rec.applied_value <> 0) THEN
1679
1680 l_stmt_num := 70;
1681
1682 UPDATE wip_pac_period_balances wppb
1683 SET tl_overhead_in = NVL(tl_overhead_in,0) +
1684 c_rbo_rec.applied_value,
1685 request_id = p_request_id,
1686 last_update_date = SYSDATE,
1687 program_update_date = SYSDATE
1688 WHERE wppb.pac_period_id = p_pac_period_id
1689 AND wppb.cost_group_id = p_cost_group_id
1690 AND wppb.wip_entity_id = c_res_rec.entity_id
1691 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1692 AND wppb.operation_seq_num = c_res_rec.op_seq;
1693
1694
1695 -- Check if eAM entity then compute actuals
1696 IF c_ent_rec.entity_type in (6,7) THEN /* Also include closed WO for Actuals Bug 5366094 */
1697 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
1698 p_api_version => 1.0,
1699 x_return_status => l_return_status,
1700 x_msg_count => l_msg_count,
1701 x_msg_data => l_msg_data,
1702 p_legal_entity_id => l_legal_entity_id,
1703 p_cost_group_id => p_cost_group_id,
1704 p_cost_type_id => p_cost_type_id,
1705 p_pac_period_id => p_pac_period_id,
1706 p_pac_ct_id => p_pac_ct_id,
1707 p_organization_id => c_res_rec.org_id,
1708 p_txn_mode => 2,
1709 p_txn_id => c_res_rec.txn_id,
1710 p_value => c_rbo_rec.applied_value,
1711 p_wip_entity_id => c_res_rec.entity_id,
1712 p_op_seq => c_res_rec.op_seq,
1713 p_resource_id => c_res_rec.resource_id,
1714 p_resource_seq_num => c_res_rec.resource_seq_num,
1715 p_user_id => p_user_id,
1716 p_request_id => p_request_id,
1717 p_prog_app_id => p_prog_app_id,
1718 p_prog_id => p_prog_id,
1719 p_login_id => p_login_id);
1720 End if; /* end eAM check */
1721
1722 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1723 l_err_msg := SUBSTR('fail_RBO_PAC_comt_jobAct ent/res_id : '
1724 ||TO_CHAR(c_res_rec.entity_id)
1725 ||'/'
1726 ||TO_CHAR(c_res_rec.resource_id)
1727 ||': (' || to_char(l_stmt_num) || '): '
1728 ||l_err_msg,1,2000);
1729 RAISE CST_PROCESS_ERROR;
1730 END IF;
1731
1732 l_stmt_num := 75;
1733
1734 INSERT INTO wip_pac_actual_cost_details
1735 (
1736 pac_period_id,
1737 cost_group_id,
1738 cost_type_id,
1739 transaction_id,
1740 level_type,
1741 cost_element_id,
1742 resource_id,
1743 basis_resource_id,
1744 transaction_costed_date,
1745 actual_cost,
1746 actual_value,
1747 basis_type,
1748 basis_units,
1749 last_update_date,
1750 last_updated_by,
1751 creation_date,
1752 created_by,
1753 request_id,
1754 program_application_id,
1755 program_id,
1756 program_update_date,
1757 last_update_login
1758 )
1759 SELECT
1760 p_pac_period_id,
1761 p_cost_group_id,
1762 p_cost_type_id,
1763 c_res_rec.txn_id,
1764 1, -- Level Type
1765 5, -- CE
1766 c_rbo_rec.ovhd_id,
1767 c_res_rec.resource_id,
1768 SYSDATE,
1769 c_rbo_rec.actual_cost,
1770 c_rbo_rec.applied_value,
1771 c_rbo_rec.basis_type,
1772 c_rbo_rec.basis_units,
1773 SYSDATE,
1774 p_user_id,
1775 SYSDATE,
1776 p_user_id,
1777 p_request_id,
1778 p_prog_app_id,
1779 p_prog_id,
1780 SYSDATE,
1781 p_login_id
1782 FROM DUAL;
1783
1784 END IF; -- check c_rbo_rec.applied_value <>0
1785
1786 END LOOP; -- c_rbo loop
1787
1788
1789 END LOOP; -- RES_REC loop
1790
1791 --------------------------------------------------------------
1792 -- Get Move based Overhead (MBO) Transactions
1793 --------------------------------------------------------------
1794
1795 l_stmt_num := 80;
1796
1797 FOR c_mbo_rec IN c_mbo_txn( c_ent_rec.entity_id) LOOP
1798
1799 l_stmt_num := 85;
1800
1801 check_pacwip_bal_record (p_pac_period_id => p_pac_period_id,
1802 p_cost_group_id => p_cost_group_id,
1803 p_cost_type_id => p_cost_type_id,
1804 p_org_id => c_mbo_rec.org_id,
1805 p_entity_id => c_mbo_rec.entity_id,
1806 p_entity_type => c_ent_rec.entity_type,
1807 p_line_id => c_mbo_rec.line_id,
1808 p_op_seq => c_mbo_rec.op_seq,
1809 p_user_id => p_user_id,
1810 p_request_id => p_request_id,
1811 p_prog_app_id => p_prog_app_id,
1812 p_prog_id => p_prog_id,
1813 p_login_id => p_login_id,
1814 x_err_num => l_err_num,
1815 x_err_code => l_err_code,
1816 x_err_msg => l_err_msg
1817 );
1818
1819 IF (l_err_num <> 0) THEN
1820
1821 l_err_msg := SUBSTR('fail_bal_rec ent/line/op: '
1822 ||TO_CHAR(c_mbo_rec.entity_id)
1823 ||'/'
1824 ||TO_CHAR(c_mbo_rec.line_id)
1825 ||'/'
1826 ||TO_CHAR(c_mbo_rec.op_seq)
1827 ||':'
1828 ||l_err_msg,1,2000);
1829 RAISE CST_PROCESS_ERROR;
1830 END IF;
1831
1832 l_stmt_num := 90;
1833
1834
1835 UPDATE wip_pac_period_balances wppb
1836 SET tl_overhead_in = NVL(tl_overhead_in,0) +
1837 c_mbo_rec.applied_value,
1838 request_id = p_request_id,
1839 last_update_date = SYSDATE,
1840 program_update_date = SYSDATE
1841 WHERE wppb.pac_period_id = p_pac_period_id
1842 AND wppb.cost_group_id = p_cost_group_id
1843 AND wppb.wip_entity_id = c_mbo_rec.entity_id
1844 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_mbo_rec.line_id,-99))
1845 AND wppb.operation_seq_num = c_mbo_rec.op_seq;
1846
1847 l_stmt_num := 95;
1848
1849 INSERT INTO wip_pac_actual_cost_details
1850 (
1851 pac_period_id,
1852 cost_group_id,
1853 cost_type_id,
1854 transaction_id,
1855 level_type,
1856 cost_element_id,
1857 resource_id,
1858 basis_resource_id,
1859 transaction_costed_date,
1860 actual_cost,
1861 actual_value,
1862 last_update_date,
1863 last_updated_by,
1864 creation_date,
1865 created_by,
1866 request_id,
1867 program_application_id,
1868 program_id,
1869 program_update_date,
1870 last_update_login
1871 )
1872 SELECT
1873 p_pac_period_id,
1874 p_cost_group_id,
1875 p_cost_type_id,
1876 c_mbo_rec.txn_id,
1877 1, -- Level Type
1878 5, -- CE
1879 c_mbo_rec.overhead_id,
1880 NULL, -- basis_resource_id
1881 SYSDATE,
1882 c_mbo_rec.actual_cost,
1883 c_mbo_rec.applied_value,
1884 SYSDATE,
1885 p_user_id,
1886 SYSDATE,
1887 p_user_id,
1888 p_request_id,
1889 p_prog_app_id,
1890 p_prog_id,
1891 SYSDATE,
1892 p_login_id
1893 FROM DUAL;
1894
1895 END LOOP; -- MBO_REC loop
1896
1897 END LOOP; -- ENT_REC loop
1898
1899
1900 EXCEPTION
1901
1902 WHEN CST_WIP_PENDING_TXNS THEN
1903 x_err_num := 20003;
1904 x_err_code := SUBSTR('CSTPPWRO.process_wip_resovhd_txns('
1905 || to_char(l_stmt_num)
1906 || '): '
1907 || l_err_msg
1908 || '. ',1,2000);
1909 fnd_message.set_name('BOM', 'CST_WIP_PENDING_TXNS');
1910 x_err_msg := SUBSTR(fnd_message.get,1,2000);
1911
1912 WHEN CST_FAIL_ACQ_COST THEN
1913 x_err_num := 20003;
1914 x_err_code := SUBSTR('CSTPPWRO.process_wip_resovhd_txns('
1915 || to_char(l_stmt_num)
1916 || '): '
1917 || l_err_msg
1918 || '. ',1,2000);
1919 fnd_message.set_name('BOM', 'CST_FAIL_ACQ_COST');
1920 x_err_msg := SUBSTR(fnd_message.get,1,2000);
1921
1922 WHEN CST_PROCESS_ERROR THEN
1923 x_err_num := l_err_num;
1924 x_err_code := l_err_code;
1925 x_err_msg := l_err_msg;
1926
1927 WHEN OTHERS THEN
1928 ROLLBACK;
1929 x_err_num := SQLCODE;
1930 x_err_code := NULL;
1931 x_err_msg := SUBSTR('CSTPPWRO.process_wip_resovhd_txns('
1932 || to_char(l_stmt_num)
1933 || '): '
1934 ||SQLERRM,1,2000);
1935
1936 END process_wip_resovhd_txns;
1937
1938 END cstppwro;