[Home] [Help]
PACKAGE BODY: APPS.CSTPPWRO
Source
1 PACKAGE BODY CSTPPWRO AS
2 /* $Header: CSTPWROB.pls 120.12.12020000.3 2013/01/18 09:30:30 penpan 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 l_rcv_txn_type VARCHAR2(25);
970
971 -- Cursor to get all wip_entities in the Cost Group that has move transactions
972
973 CURSOR c_wip_entities_mov IS
974 SELECT we.wip_entity_id entity_id,
975 we.entity_type entity_type
976 FROM wip_entities we,
977 cst_cost_group_assignments ccga
978 WHERE ccga.cost_group_id = p_cost_group_id
979 AND we.organization_id = ccga.organization_id
980 AND we.entity_type <> 4 -- NOT CFM
981 AND we.primary_item_id IS NOT NULL
982 AND EXISTS
983 ( SELECT 'X'
984 FROM wip_move_transactions wmt
985 WHERE wmt.wip_entity_id = we.wip_entity_id
986 AND wmt.transaction_date BETWEEN
987 TRUNC(p_start_date) AND
988 (TRUNC(p_end_date) + 0.99999)
989 );
990
991 -- Cursor to get all wip_entities in the Cost Group that has resource transactions
992
993 CURSOR c_wip_entities_res IS
994 SELECT we.wip_entity_id entity_id,
995 we.entity_type entity_type,
996 we.organization_id org_id
997 FROM wip_entities we,
998 cst_cost_group_assignments ccga
999 WHERE ccga.cost_group_id = p_cost_group_id
1000 AND we.organization_id = ccga.organization_id
1001 AND EXISTS
1002 ( SELECT 'X'
1003 FROM wip_transactions wt
1004 WHERE wt.wip_entity_id = we.wip_entity_id
1005 AND wt.transaction_type IN (1,2,3,17)
1006 -- Direct Item txns should be picked too.
1007 AND wt.transaction_date BETWEEN
1008 TRUNC(p_start_date) AND
1009 (TRUNC(p_end_date) + 0.99999)
1010 AND ccga.organization_id = wt.organization_id
1011 );
1012
1013 -- Cursor to process resource/OSP transaction for a given wip_entity/cost_type
1014 -- It will get all resource/osp type records from WIP_TRANSACTIONS where the
1015 -- resource/osp has a non zero rate defined for the PAC_RATES cost type.
1016
1017 CURSOR c_res_txn ( p_entity_id NUMBER) IS
1018 SELECT wt.transaction_id txn_id,
1019 wt.organization_id org_id,
1020 wt.wip_entity_id entity_id,
1021 wt.line_id line_id,
1022 wt.department_id dept_id,
1023 wt.operation_seq_num op_seq,
1024 wt.resource_id resource_id,
1025 wt.resource_seq_num resource_seq_num, -- Added for eAM project
1026 crc.resource_rate actual_cost,
1027 wt.transaction_type wip_txn_type,
1028 wt.primary_quantity applied_units,
1029 wt.primary_quantity*nvl(crc.resource_rate,0) applied_value,
1030 wt.rcv_transaction_id -- Added for 4735668
1031 FROM wip_transactions wt,
1032 cst_resource_costs crc,
1033 bom_resources br /* Bug 4641635; Include resources which are to be costed */
1034
1035 WHERE wt.wip_entity_id = p_entity_id
1036 AND wt.transaction_date BETWEEN TRUNC(p_start_date)
1037 AND (TRUNC(p_end_date) + 0.99999)
1038 AND wt.transaction_type = 1 -- RES
1039 /* fix for bug 2988196 */
1040 AND crc.resource_id(+) = wt.resource_id
1041 AND crc.organization_id(+) = wt.organization_id
1042 AND crc.cost_type_id(+) = p_pac_ct_id
1043 -- AND crc.resource_rate <> 0
1044 /*Bug 4641635: Exclude resources with allow_costs_flag set to no*/
1045 AND br.resource_id = wt.resource_id
1046 AND br.allow_costs_flag = 1
1047 UNION ALL
1048 SELECT wt.transaction_id txn_id,
1049 wt.organization_id org_id,
1050 wt.wip_entity_id entity_id,
1051 wt.line_id line_id,
1052 wt.department_id dept_id,
1053 wt.operation_seq_num op_seq,
1054 wt.resource_id resource_id,
1055 wt.resource_seq_num resource_seq_num, -- Added for eAM project
1056 0 actual_cost,
1057 wt.transaction_type wip_txn_type,
1058 wt.primary_quantity applied_units,
1059 0 applied_value,
1060 wt.rcv_transaction_id -- Added for 4735668
1061 FROM wip_transactions wt,
1062 bom_resources br /* Bug 4641635; Include resources which are to be costed */
1063 WHERE wt.wip_entity_id = p_entity_id
1064 AND wt.transaction_date BETWEEN TRUNC(p_start_date)
1065 AND (TRUNC(p_end_date) + 0.99999)
1066 AND wt.transaction_type = 3 -- OSP
1067 /*Bug 4641635: Exclude resources with allow_costs_flag set to no */
1068 AND br.resource_id = wt.resource_id
1069 AND br.allow_costs_flag = 1
1070 UNION ALL
1071 /* Bug:4641635 Separating out the query for Direct Items */
1072 SELECT wt.transaction_id txn_id,
1073 wt.organization_id org_id,
1074 wt.wip_entity_id entity_id,
1075 wt.line_id line_id,
1076 wt.department_id dept_id,
1077 wt.operation_seq_num op_seq,
1078 wt.resource_id resource_id,
1079 wt.resource_seq_num resource_seq_num, -- Added for eAM project
1080 0 actual_cost,
1081 wt.transaction_type wip_txn_type,
1082 nvl(wt.primary_quantity, rt.amount) applied_units, /* Bug 4180323*/
1083 0 applied_value,
1084 wt.rcv_transaction_id -- Added for 4735668
1085 FROM wip_transactions wt,
1086 rcv_transactions rt
1087 WHERE wt.wip_entity_id = p_entity_id
1088 AND rt.transaction_id = wt.rcv_transaction_id
1089 AND wt.transaction_date BETWEEN TRUNC(p_start_date)
1090 AND (TRUNC(p_end_date) + 0.99999)
1091 AND wt.transaction_type = 17; -- Direct Items
1092
1093
1094
1095 -- Cursor to obtain all resource based overheads for a given org/dept/res/ct
1096
1097 CURSOR c_rbo ( p_resource_id NUMBER,
1098 p_dept_id NUMBER,
1099 p_org_id NUMBER,
1100 p_res_units NUMBER,
1101 p_res_value NUMBER) IS
1102
1103 SELECT cdo.overhead_id ovhd_id,
1104 cdo.rate_or_amount actual_cost,
1105 cdo.basis_type basis_type,
1106 cdo.rate_or_amount * decode(cdo.basis_type,
1107 3, p_res_units,
1108 p_res_value) applied_value,
1109 decode(cdo.basis_type, 3, p_res_units,
1110 p_res_value) basis_units
1111 FROM cst_resource_overheads cro,
1112 cst_department_overheads cdo
1113 WHERE cdo.department_id = p_dept_id
1114 AND cdo.organization_id = p_org_id
1115 AND cdo.cost_type_id = p_pac_ct_id
1116 AND cdo.basis_type IN (3,4)
1117 AND cro.cost_type_id = cdo.cost_type_id
1118 AND cro.resource_id = p_resource_id
1119 AND cro.overhead_id = cdo.overhead_id
1120 AND cro.organization_id = cdo.organization_id;
1121
1122 -- Cursor to process move based ovhd transaction for a given entity/cost_type
1123 -- It will get all overhead type records from WIP_TRANSACTIONS where the
1124 -- overhead has a non zero rate defined for the PAC_RATES cost type.
1125
1126 CURSOR c_mbo_txn( p_entity_id NUMBER) IS
1127 SELECT wt.transaction_id txn_id,
1128 wt.organization_id org_id,
1129 wt.wip_entity_id entity_id,
1130 wt.line_id line_id,
1131 wt.operation_seq_num op_seq,
1132 wt.department_id dept_id,
1133 wta.resource_id overhead_id,
1134 cdo.rate_or_amount actual_cost,
1135 wt.basis_type basis_type,
1136 wt.primary_quantity*cdo.rate_or_amount applied_value
1137 FROM
1138 wip_transactions wt,
1139 wip_transaction_accounts wta,
1140 cst_department_overheads cdo
1141 WHERE wt.wip_entity_id = p_entity_id
1142 AND wt.transaction_date BETWEEN TRUNC(p_start_date)
1143 AND (TRUNC(p_end_date) + 0.99999)
1144 AND wt.transaction_type = 2 -- MBO
1145 AND wta.transaction_id = wt.transaction_id
1146 AND wta.accounting_line_type = 3
1147 AND wta.cost_element_id = 5
1148 AND cdo.department_id = wt.department_id
1149 AND cdo.overhead_id = wta.resource_id
1150 AND cdo.cost_type_id = p_pac_ct_id
1151 AND NVL(wta.repetitive_schedule_id,-99) =
1152 (SELECT NVL(MAX(wip_alloc.repetitive_schedule_id),-99)
1153 FROM wip_txn_allocations wip_alloc
1154 WHERE wip_alloc.transaction_id = wt.transaction_id
1155 AND rownum = 1
1156 )
1157 AND cdo.rate_or_amount <> 0
1158 ORDER BY wt.transaction_id;
1159
1160 BEGIN
1161
1162 ----------------------------------------------------------------------
1163 -- Initialize Variables
1164 ----------------------------------------------------------------------
1165
1166 l_err_num := 0;
1167 l_err_code := '';
1168 l_err_msg := '';
1169 l_acq_cost := 0;
1170 l_pending_txns := 0;
1171
1172 l_return_status := FND_API.G_RET_STS_SUCCESS;
1173 l_msg_count := 0;
1174 l_msg_data := '';
1175 l_legal_entity_id := 0 ;
1176 l_eam_cost_element := 0;
1177
1178
1179
1180 ----------------------------------------------------------------------
1181 -- Error out if there are any pending txns
1182 ----------------------------------------------------------------------
1183
1184 l_stmt_num := 5;
1185
1186 SELECT count(*)
1187 INTO l_pending_txns
1188 FROM wip_cost_txn_interface wcti
1189 WHERE wcti.transaction_date BETWEEN TRUNC(p_start_date)
1190 AND (TRUNC(p_end_date) + 0.99999)
1191 AND wcti.transaction_type IN (1,2,3,6)
1192 AND EXISTS (
1193 SELECT 'X'
1194 FROM cst_cost_group_assignments ccga
1195 WHERE ccga.cost_group_id = p_cost_group_id
1196 AND ccga.organization_id = wcti.organization_id
1197 );
1198
1199 IF (l_pending_txns <> 0) THEN
1200
1201 RAISE CST_WIP_PENDING_TXNS;
1202
1203 END IF;
1204
1205 -- Select the legal entity for the pac period, to be used in eAM part
1206 SELECT legal_entity
1207 INTO l_legal_entity_id
1208 FROM cst_pac_periods
1209 WHERE pac_period_id = p_pac_period_id;
1210
1211 l_stmt_num := 7;
1212
1213 ----------------------------------------------------------------------
1214 -- Process Non-CFM WIP entities with move transactions
1215 ----------------------------------------------------------------------
1216 l_stmt_num := 10;
1217
1218 FOR c_ent_mov_rec IN c_wip_entities_mov LOOP
1219 ------------------------------------------------------
1220 -- Update op qty snapshot for this entity based on
1221 -- this period's move txns.
1222 ------------------------------------------------------
1223
1224 l_stmt_num := 15;
1225
1226 build_wip_operation_qty (
1227 p_pac_period_id => p_pac_period_id ,
1228 p_start_date => p_start_date,
1229 p_end_date => p_end_date,
1230 p_cost_group_id => p_cost_group_id,
1231 p_cost_type_id => p_cost_type_id,
1232 p_entity_id => c_ent_mov_rec.entity_id,
1233 p_entity_type => c_ent_mov_rec.entity_type,
1234 p_user_id => p_user_id,
1235 p_login_id => p_login_id,
1236 p_request_id => p_request_id,
1237 p_prog_id => p_prog_id,
1238 p_prog_app_id => p_prog_app_id,
1239 x_err_num => l_err_num,
1240 x_err_code => l_err_code,
1241 x_err_msg => l_err_msg);
1242
1243 IF (l_err_num <> 0) THEN
1244 l_err_msg := SUBSTR('fail_build_wip_op entity: '
1245 ||TO_CHAR(c_ent_mov_rec.entity_id)
1246 ||':'
1247 ||l_err_msg,1,2000);
1248 RAISE CST_PROCESS_ERROR;
1249 END IF;
1250
1251 END LOOP;
1252
1253 ----------------------------------------------------------------------
1254 -- Process WIP entities with resource transactions
1255 ----------------------------------------------------------------------
1256 l_stmt_num := 20;
1257
1258 FOR c_ent_rec IN c_wip_entities_res LOOP
1259
1260 --------------------------------------------------------------
1261 -- Get Resource/OSP (and RBO) Transactions
1262 --------------------------------------------------------------
1263
1264 l_stmt_num := 25;
1265
1266 FOR c_res_rec IN c_res_txn(c_ent_rec.entity_id) LOOP
1267
1268 l_stmt_num := 30;
1269
1270 check_pacwip_bal_record (p_pac_period_id => p_pac_period_id,
1271 p_cost_group_id => p_cost_group_id,
1272 p_cost_type_id => p_cost_type_id,
1273 p_org_id => c_res_rec.org_id,
1274 p_entity_id => c_res_rec.entity_id,
1275 p_entity_type => c_ent_rec.entity_type,
1276 p_line_id => c_res_rec.line_id,
1277 p_op_seq => c_res_rec.op_seq,
1278 p_user_id => p_user_id,
1279 p_request_id => p_request_id,
1280 p_prog_app_id => p_prog_app_id,
1281 p_prog_id => p_prog_id,
1282 p_login_id => p_login_id,
1283 x_err_num => l_err_num,
1284 x_err_code => l_err_code,
1285 x_err_msg => l_err_msg
1286 );
1287
1288 IF (l_err_num <> 0) THEN
1289 l_err_msg := SUBSTR('fail_bal_rec entity: '
1290 ||TO_CHAR(c_res_rec.entity_id)
1291 ||':'
1292 ||l_err_msg,1,2000);
1293 RAISE CST_PROCESS_ERROR;
1294 END IF;
1295
1296 l_stmt_num := 35;
1297
1298 IF c_res_rec.wip_txn_type = 1 THEN --RES
1299
1300 l_stmt_num := 40;
1301
1302 UPDATE wip_pac_period_balances wppb
1303 SET tl_resource_in = NVL(tl_resource_in,0) +
1304 c_res_rec.applied_value,
1305 request_id = p_request_id,
1306 last_update_date = SYSDATE,
1307 program_update_date = SYSDATE
1308 WHERE wppb.pac_period_id = p_pac_period_id
1309 AND wppb.cost_group_id = p_cost_group_id
1310 AND wppb.wip_entity_id = c_res_rec.entity_id
1311 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1312 AND wppb.operation_seq_num = c_res_rec.op_seq;
1313
1314 /* Bug 4180323- Initializing the variables so that correct value is inserted into
1315 wip_pac_actual_cost_details table
1316 */
1317
1318 l_actual_cost := c_res_rec.actual_cost;
1319 l_applied_value := c_res_rec.applied_value;
1320
1321 -- Check if eAM entity then compute actuals
1322 IF c_ent_rec.entity_type in (6,7) THEN /* Also include closed WO for Actuals Bug 5366094 */
1323 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
1324 p_api_version => 1.0,
1325 x_return_status => l_return_status,
1326 x_msg_count => l_msg_count,
1327 x_msg_data => l_msg_data,
1328 p_legal_entity_id => l_legal_entity_id,
1329 p_cost_group_id => p_cost_group_id,
1330 p_cost_type_id => p_cost_type_id,
1331 p_pac_period_id => p_pac_period_id,
1332 p_pac_ct_id => p_pac_ct_id,
1333 p_organization_id => c_res_rec.org_id,
1334 p_txn_mode => 2,
1335 p_txn_id => c_res_rec.txn_id,
1336 p_value => c_res_rec.applied_value,
1337 p_wip_entity_id => c_res_rec.entity_id,
1338 p_op_seq => c_res_rec.op_seq,
1339 p_resource_id => c_res_rec.resource_id,
1340 p_resource_seq_num => c_res_rec.resource_seq_num,
1341 p_user_id => p_user_id,
1342 p_request_id => p_request_id,
1343 p_prog_app_id => p_prog_app_id,
1344 p_prog_id => p_prog_id,
1345 p_login_id => p_login_id);
1346
1347 l_stmt_num := 43;
1348
1349 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1350 l_err_msg := SUBSTR('fail_RES_PAC_comt_jobAct ent/res_id : '
1351 ||TO_CHAR(c_res_rec.entity_id)
1352 ||'/'
1353 ||TO_CHAR(c_res_rec.resource_id)
1354 ||': (' || to_char(l_stmt_num) || '): '
1355 ||l_err_msg,1,2000);
1356 RAISE CST_PROCESS_ERROR;
1357 END IF;
1358
1359 End if; -- end eAM check
1360
1361 ELSIF c_res_rec.wip_txn_type = 3 THEN --OSP
1362
1363 ------------------------------------------------------------
1364 -- Call acquisition cost Procedure to get the per unit
1365 -- cost of this osp receipt transaction.
1366 ------------------------------------------------------------
1367
1368 l_stmt_num := 45;
1369 CSTPPACQ.get_acq_cost(
1370 i_cost_group_id => p_cost_group_id,
1371 i_cost_type_id => p_cost_type_id,
1372 i_txn_id => c_res_rec.txn_id,
1373 i_wip_inv_flag => 'W',
1374 o_acq_cost => l_acq_cost,
1375 o_err_num => l_err_num,
1376 o_err_code => l_err_code,
1377 o_err_msg => l_err_msg);
1378
1379
1380 IF (l_err_num <>0) THEN
1381 l_err_msg := SUBSTR('fail_acq_cost txn: '
1382 ||TO_CHAR(c_res_rec.txn_id)
1383 ||' '
1384 ||l_err_msg,1,2000);
1385 RAISE CST_FAIL_ACQ_COST;
1386 END IF;
1387
1388
1389 l_stmt_num := 47;
1390
1391 l_acq_cost := NVL(l_acq_cost,0);
1392 l_actual_cost := l_acq_cost;
1393
1394 /* Added for bug4735668 to get the primary quantity of deliver
1395 as acquistion cost would be in the UOM of receipt */
1396 SELECT rt.primary_quantity, rt.transaction_type
1397 INTO l_del_qty, l_rcv_txn_type
1398 FROM RCV_TRANSACTIONS RT
1399 WHERE rt.transaction_id = c_res_rec.rcv_transaction_id;
1400
1401 IF(l_rcv_txn_type = 'RETURN TO VENDOR' OR l_rcv_txn_type = 'RETURN TO RECEIVING') THEN --bug 16056941, this if statement check should be only for RTV or RTR. for other types,the quantity in RT is the same as WT.
1402 /* FP Bug 7346249 fix: check for -ve quantities */
1403 IF Sign(c_res_rec.applied_units) = -1 THEN
1404 l_del_qty := -1 * l_del_qty;
1405 END IF;
1406 END IF;
1407 l_applied_value := l_acq_cost * l_del_qty;
1408
1409
1410 l_stmt_num := 50;
1411
1412 UPDATE wip_pac_period_balances wppb
1413 SET tl_outside_processing_in =
1414 NVL(tl_outside_processing_in,0) +
1415 l_applied_value,
1416 request_id = p_request_id,
1417 last_update_date = SYSDATE,
1418 program_update_date = SYSDATE
1419 WHERE wppb.pac_period_id = p_pac_period_id
1420 AND wppb.cost_group_id = p_cost_group_id
1421 AND wppb.wip_entity_id = c_res_rec.entity_id
1422 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1423 AND wppb.operation_seq_num = c_res_rec.op_seq;
1424
1425 -- Check if eAM entity then compute actuals
1426 IF c_ent_rec.entity_type in (6,7) THEN /* Also include closed WO for Actuals Bug 5366094*/
1427
1428 l_stmt_num := 52;
1429
1430 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
1431 p_api_version => 1.0,
1432 x_return_status => l_return_status,
1433 x_msg_count => l_msg_count,
1434 x_msg_data => l_msg_data,
1435 p_legal_entity_id => l_legal_entity_id,
1436 p_cost_group_id => p_cost_group_id,
1437 p_cost_type_id => p_cost_type_id,
1438 p_pac_period_id => p_pac_period_id,
1439 p_pac_ct_id => p_pac_ct_id,
1440 p_organization_id => c_res_rec.org_id,
1441 p_txn_mode => 2,
1442 p_txn_id => c_res_rec.txn_id,
1443 p_value => l_applied_value,
1444 p_wip_entity_id => c_res_rec.entity_id,
1445 p_op_seq => c_res_rec.op_seq,
1446 p_resource_id => c_res_rec.resource_id,
1447 p_resource_seq_num => c_res_rec.resource_seq_num,
1448 p_user_id => p_user_id,
1449 p_request_id => p_request_id,
1450 p_prog_app_id => p_prog_app_id,
1451 p_prog_id => p_prog_id,
1452 p_login_id => p_login_id);
1453 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1454 l_err_msg := SUBSTR('fail_OSP_PAC_comt_jobAct ent/res_id : '
1455 ||TO_CHAR(c_res_rec.entity_id)
1456 ||'/'
1457 ||TO_CHAR(c_res_rec.resource_id)
1458 ||': (' || to_char(l_stmt_num) || '): '
1459 ||l_err_msg,1,2000);
1460 RAISE CST_PROCESS_ERROR;
1461 END IF;
1462
1463 END IF; -- end eAM check
1464
1465 END IF;
1466
1467 -- Check if eAM entity and Direct Item txn
1468 IF (c_ent_rec.entity_type in (6,7) /* Also include closed WO for Actuals Bug 5366094 */
1469 AND c_res_rec.wip_txn_type = 17) THEN
1470
1471 CSTPPACQ.get_acq_cost(
1472 i_cost_group_id => p_cost_group_id,
1473 i_cost_type_id => p_cost_type_id,
1474 i_txn_id => c_res_rec.txn_id,
1475 i_wip_inv_flag => 'W',
1476 o_acq_cost => l_acq_cost,
1477 o_err_num => l_err_num,
1478 o_err_code => l_err_code,
1479 o_err_msg => l_err_msg);
1480
1481 IF (l_err_num <>0) THEN
1482 l_err_msg := SUBSTR('fail_acq_cost txn: '
1483 ||TO_CHAR(c_res_rec.txn_id) ||' '
1484 ||l_err_msg,1,2000);
1485 RAISE CST_FAIL_ACQ_COST;
1486 END IF;
1487
1488
1489 l_stmt_num := 54;
1490
1491 l_acq_cost :=NVL(l_acq_cost,0);
1492 l_actual_cost := l_acq_cost;
1493
1494 l_applied_value := l_acq_cost * c_res_rec.applied_units;
1495
1496 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
1497 p_api_version => 1.0,
1498 x_return_status => l_return_status,
1499 x_msg_count => l_msg_count,
1500 x_msg_data => l_msg_data,
1501 p_legal_entity_id => l_legal_entity_id,
1502 p_cost_group_id => p_cost_group_id,
1503 p_cost_type_id => p_cost_type_id,
1504 p_pac_period_id => p_pac_period_id,
1505 p_pac_ct_id => p_pac_ct_id,
1506 p_organization_id => c_res_rec.org_id,
1507 p_txn_mode => 17,
1508 p_txn_id => c_res_rec.txn_id,
1509 p_value => l_applied_value,
1510 p_wip_entity_id => c_res_rec.entity_id,
1511 p_op_seq => c_res_rec.op_seq,
1512 p_resource_id => c_res_rec.resource_id,
1513 p_resource_seq_num => c_res_rec.resource_seq_num,
1514 p_user_id => p_user_id,
1515 p_request_id => p_request_id,
1516 p_prog_app_id => p_prog_app_id,
1517 p_prog_id => p_prog_id,
1518 p_login_id => p_login_id);
1519
1520 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1521 l_err_msg := SUBSTR('fail_DIR_PAC_comt_jobAct ent/res_id : '
1522 ||TO_CHAR(c_res_rec.entity_id)
1523 ||'/'
1524 ||TO_CHAR(c_res_rec.resource_id)
1525 ||': (' || to_char(l_stmt_num) || '): '
1526 ||l_err_msg,1,2000);
1527 RAISE CST_PROCESS_ERROR;
1528 END IF;
1529
1530 -- get the direct item cost element
1531 CST_EAMCOST_PUB.get_CostEle_for_DirectItem (
1532 p_api_version => 1.0,
1533 x_return_status => l_return_status,
1534 x_msg_count => l_msg_count,
1535 x_msg_data => l_msg_data,
1536 p_txn_id => c_res_rec.txn_id,
1537 p_mnt_or_mfg => 2,
1538 p_pac_or_perp => 1,
1539 x_cost_element_id => l_eam_cost_element);
1540
1541 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1542 l_err_msg := SUBSTR('fail_get_CostEle_for_DirectItem txn_id : '
1543 ||TO_CHAR(c_res_rec.txn_id)
1544 ||': (' || to_char(l_stmt_num) || '): '
1545 ||l_err_msg,1,2000);
1546 RAISE CST_PROCESS_ERROR;
1547 END IF;
1548
1549 l_stmt_num := 56;
1550
1551
1552 /* Check for cost element of direct item to update the required
1553 colimns. */
1554
1555 IF l_eam_cost_element = 1 THEN
1556
1557 /* Update PL-material for materials */
1558 UPDATE wip_pac_period_balances wppb
1559 SET pl_material_in =
1560 NVL(pl_material_in,0) +
1561 l_applied_value,
1562 request_id = p_request_id,
1563 last_update_date = SYSDATE,
1564 program_update_date = SYSDATE
1565 WHERE wppb.pac_period_id = p_pac_period_id
1566 AND wppb.cost_group_id = p_cost_group_id
1567 AND wppb.wip_entity_id = c_res_rec.entity_id
1568 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1569 AND wppb.operation_seq_num = c_res_rec.op_seq;
1570
1571 l_stmt_num := 58;
1572
1573 ELSIF l_eam_cost_element = 3 THEN
1574
1575 /* Update PL-resource for Resources */
1576 UPDATE wip_pac_period_balances wppb
1577 SET pl_resource_in = NVL(pl_resource_in,0)
1578 + l_applied_value,
1579 request_id = p_request_id,
1580 last_update_date = SYSDATE,
1581 program_update_date = SYSDATE
1582 WHERE wppb.pac_period_id = p_pac_period_id
1583 AND wppb.cost_group_id = p_cost_group_id
1584 AND wppb.wip_entity_id = c_res_rec.entity_id
1585 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1586 AND wppb.operation_seq_num = c_res_rec.op_seq;
1587
1588 l_stmt_num := 60;
1589
1590 ELSE
1591
1592 /* Update PL-OSP cols for OSP */
1593 UPDATE wip_pac_period_balances wppb
1594 SET pl_outside_processing_in =
1595 NVL (pl_outside_processing_in,0) +
1596 l_applied_value,
1597 request_id = p_request_id,
1598 last_update_date = SYSDATE,
1599 program_update_date = SYSDATE
1600 WHERE wppb.pac_period_id = p_pac_period_id
1601 AND wppb.cost_group_id = p_cost_group_id
1602 AND wppb.wip_entity_id = c_res_rec.entity_id
1603 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1604 AND wppb.operation_seq_num = c_res_rec.op_seq;
1605
1606 l_stmt_num := 62;
1607
1608 END IF; -- IF l_eam_cost_element
1609
1610 END IF; -- END for Direct Items
1611
1612 --------------------------------------------------------------
1613 -- Insert Res/OSP txn costs in WPTCD
1614 --------------------------------------------------------------
1615
1616 l_stmt_num := 64;
1617
1618 INSERT INTO wip_pac_actual_cost_details
1619 (
1620 pac_period_id,
1621 cost_group_id,
1622 cost_type_id,
1623 transaction_id,
1624 level_type,
1625 cost_element_id,
1626 resource_id,
1627 basis_resource_id,
1628 transaction_costed_date,
1629 actual_cost,
1630 actual_value,
1631 last_update_date,
1632 last_updated_by,
1633 creation_date,
1634 created_by,
1635 request_id,
1636 program_application_id,
1637 program_id,
1638 program_update_date,
1639 last_update_login
1640 )
1641 SELECT
1642 p_pac_period_id,
1643 p_cost_group_id,
1644 p_cost_type_id,
1645 c_res_rec.txn_id,
1646 1, -- Level Type
1647 decode(c_res_rec.wip_txn_type,
1648 1, 3,
1649 3, 4,
1650 17,l_eam_cost_element), -- Cost element_id
1651 -- Insert Direct Item cost element id here too.
1652 c_res_rec.resource_id,
1653 NULL, -- basis_res_id
1654 SYSDATE,
1655 l_actual_cost,
1656 l_applied_value,
1657 SYSDATE,
1658 p_user_id,
1659 SYSDATE,
1660 p_user_id,
1661 p_request_id,
1662 p_prog_app_id,
1663 p_prog_id,
1664 SYSDATE,
1665 p_login_id
1666 FROM DUAL;
1667
1668 --------------------------------------------------------------
1669 -- Get all Res Based Ovhds for this resource/dept/ct
1670 --------------------------------------------------------------
1671
1672 l_stmt_num := 66;
1673
1674 FOR c_rbo_rec IN c_rbo (c_res_rec.resource_id,
1675 c_res_rec.dept_id,
1676 c_res_rec.org_id,
1677 c_res_rec.applied_units,
1678 l_applied_value)
1679 LOOP
1680 IF (c_rbo_rec.applied_value <> 0) THEN
1681
1682 l_stmt_num := 70;
1683
1684 UPDATE wip_pac_period_balances wppb
1685 SET tl_overhead_in = NVL(tl_overhead_in,0) +
1686 c_rbo_rec.applied_value,
1687 request_id = p_request_id,
1688 last_update_date = SYSDATE,
1689 program_update_date = SYSDATE
1690 WHERE wppb.pac_period_id = p_pac_period_id
1691 AND wppb.cost_group_id = p_cost_group_id
1692 AND wppb.wip_entity_id = c_res_rec.entity_id
1693 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_res_rec.line_id,-99))
1694 AND wppb.operation_seq_num = c_res_rec.op_seq;
1695
1696
1697 -- Check if eAM entity then compute actuals
1698 IF c_ent_rec.entity_type in (6,7) THEN /* Also include closed WO for Actuals Bug 5366094 */
1699 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
1700 p_api_version => 1.0,
1701 x_return_status => l_return_status,
1702 x_msg_count => l_msg_count,
1703 x_msg_data => l_msg_data,
1704 p_legal_entity_id => l_legal_entity_id,
1705 p_cost_group_id => p_cost_group_id,
1706 p_cost_type_id => p_cost_type_id,
1707 p_pac_period_id => p_pac_period_id,
1708 p_pac_ct_id => p_pac_ct_id,
1709 p_organization_id => c_res_rec.org_id,
1710 p_txn_mode => 2,
1711 p_txn_id => c_res_rec.txn_id,
1712 p_value => c_rbo_rec.applied_value,
1713 p_wip_entity_id => c_res_rec.entity_id,
1714 p_op_seq => c_res_rec.op_seq,
1715 p_resource_id => c_res_rec.resource_id,
1716 p_resource_seq_num => c_res_rec.resource_seq_num,
1717 p_user_id => p_user_id,
1718 p_request_id => p_request_id,
1719 p_prog_app_id => p_prog_app_id,
1720 p_prog_id => p_prog_id,
1721 p_login_id => p_login_id);
1722 End if; /* end eAM check */
1723
1724 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1725 l_err_msg := SUBSTR('fail_RBO_PAC_comt_jobAct ent/res_id : '
1726 ||TO_CHAR(c_res_rec.entity_id)
1727 ||'/'
1728 ||TO_CHAR(c_res_rec.resource_id)
1729 ||': (' || to_char(l_stmt_num) || '): '
1730 ||l_err_msg,1,2000);
1731 RAISE CST_PROCESS_ERROR;
1732 END IF;
1733
1734 l_stmt_num := 75;
1735
1736 INSERT INTO wip_pac_actual_cost_details
1737 (
1738 pac_period_id,
1739 cost_group_id,
1740 cost_type_id,
1741 transaction_id,
1742 level_type,
1743 cost_element_id,
1744 resource_id,
1745 basis_resource_id,
1746 transaction_costed_date,
1747 actual_cost,
1748 actual_value,
1749 basis_type,
1750 basis_units,
1751 last_update_date,
1752 last_updated_by,
1753 creation_date,
1754 created_by,
1755 request_id,
1756 program_application_id,
1757 program_id,
1758 program_update_date,
1759 last_update_login
1760 )
1761 SELECT
1762 p_pac_period_id,
1763 p_cost_group_id,
1764 p_cost_type_id,
1765 c_res_rec.txn_id,
1766 1, -- Level Type
1767 5, -- CE
1768 c_rbo_rec.ovhd_id,
1769 c_res_rec.resource_id,
1770 SYSDATE,
1771 c_rbo_rec.actual_cost,
1772 c_rbo_rec.applied_value,
1773 c_rbo_rec.basis_type,
1774 c_rbo_rec.basis_units,
1775 SYSDATE,
1776 p_user_id,
1777 SYSDATE,
1778 p_user_id,
1779 p_request_id,
1780 p_prog_app_id,
1781 p_prog_id,
1782 SYSDATE,
1783 p_login_id
1784 FROM DUAL;
1785
1786 END IF; -- check c_rbo_rec.applied_value <>0
1787
1788 END LOOP; -- c_rbo loop
1789
1790
1791 END LOOP; -- RES_REC loop
1792
1793 --------------------------------------------------------------
1794 -- Get Move based Overhead (MBO) Transactions
1795 --------------------------------------------------------------
1796
1797 l_stmt_num := 80;
1798
1799 FOR c_mbo_rec IN c_mbo_txn( c_ent_rec.entity_id) LOOP
1800
1801 l_stmt_num := 85;
1802
1803 check_pacwip_bal_record (p_pac_period_id => p_pac_period_id,
1804 p_cost_group_id => p_cost_group_id,
1805 p_cost_type_id => p_cost_type_id,
1806 p_org_id => c_mbo_rec.org_id,
1807 p_entity_id => c_mbo_rec.entity_id,
1808 p_entity_type => c_ent_rec.entity_type,
1809 p_line_id => c_mbo_rec.line_id,
1810 p_op_seq => c_mbo_rec.op_seq,
1811 p_user_id => p_user_id,
1812 p_request_id => p_request_id,
1813 p_prog_app_id => p_prog_app_id,
1814 p_prog_id => p_prog_id,
1815 p_login_id => p_login_id,
1816 x_err_num => l_err_num,
1817 x_err_code => l_err_code,
1818 x_err_msg => l_err_msg
1819 );
1820
1821 IF (l_err_num <> 0) THEN
1822
1823 l_err_msg := SUBSTR('fail_bal_rec ent/line/op: '
1824 ||TO_CHAR(c_mbo_rec.entity_id)
1825 ||'/'
1826 ||TO_CHAR(c_mbo_rec.line_id)
1827 ||'/'
1828 ||TO_CHAR(c_mbo_rec.op_seq)
1829 ||':'
1830 ||l_err_msg,1,2000);
1831 RAISE CST_PROCESS_ERROR;
1832 END IF;
1833
1834 l_stmt_num := 90;
1835
1836
1837 UPDATE wip_pac_period_balances wppb
1838 SET tl_overhead_in = NVL(tl_overhead_in,0) +
1839 c_mbo_rec.applied_value,
1840 request_id = p_request_id,
1841 last_update_date = SYSDATE,
1842 program_update_date = SYSDATE
1843 WHERE wppb.pac_period_id = p_pac_period_id
1844 AND wppb.cost_group_id = p_cost_group_id
1845 AND wppb.wip_entity_id = c_mbo_rec.entity_id
1846 AND NVL(wppb.line_id,-99) = decode(wppb.wip_entity_type, 4, -99, NVL(c_mbo_rec.line_id,-99))
1847 AND wppb.operation_seq_num = c_mbo_rec.op_seq;
1848
1849 l_stmt_num := 95;
1850
1851 INSERT INTO wip_pac_actual_cost_details
1852 (
1853 pac_period_id,
1854 cost_group_id,
1855 cost_type_id,
1856 transaction_id,
1857 level_type,
1858 cost_element_id,
1859 resource_id,
1860 basis_resource_id,
1861 transaction_costed_date,
1862 actual_cost,
1863 actual_value,
1864 last_update_date,
1865 last_updated_by,
1866 creation_date,
1867 created_by,
1868 request_id,
1869 program_application_id,
1870 program_id,
1871 program_update_date,
1872 last_update_login
1873 )
1874 SELECT
1875 p_pac_period_id,
1876 p_cost_group_id,
1877 p_cost_type_id,
1878 c_mbo_rec.txn_id,
1879 1, -- Level Type
1880 5, -- CE
1881 c_mbo_rec.overhead_id,
1882 NULL, -- basis_resource_id
1883 SYSDATE,
1884 c_mbo_rec.actual_cost,
1885 c_mbo_rec.applied_value,
1886 SYSDATE,
1887 p_user_id,
1888 SYSDATE,
1889 p_user_id,
1890 p_request_id,
1891 p_prog_app_id,
1892 p_prog_id,
1893 SYSDATE,
1894 p_login_id
1895 FROM DUAL;
1896
1897 END LOOP; -- MBO_REC loop
1898
1899 END LOOP; -- ENT_REC loop
1900
1901
1902 EXCEPTION
1903
1904 WHEN CST_WIP_PENDING_TXNS THEN
1905 x_err_num := 20003;
1906 x_err_code := SUBSTR('CSTPPWRO.process_wip_resovhd_txns('
1907 || to_char(l_stmt_num)
1908 || '): '
1909 || l_err_msg
1910 || '. ',1,2000);
1911 fnd_message.set_name('BOM', 'CST_WIP_PENDING_TXNS');
1912 x_err_msg := SUBSTR(fnd_message.get,1,2000);
1913
1914 WHEN CST_FAIL_ACQ_COST THEN
1915 x_err_num := 20003;
1916 x_err_code := SUBSTR('CSTPPWRO.process_wip_resovhd_txns('
1917 || to_char(l_stmt_num)
1918 || '): '
1919 || l_err_msg
1920 || '. ',1,2000);
1921 fnd_message.set_name('BOM', 'CST_FAIL_ACQ_COST');
1922 x_err_msg := SUBSTR(fnd_message.get,1,2000);
1923
1924 WHEN CST_PROCESS_ERROR THEN
1925 x_err_num := l_err_num;
1926 x_err_code := l_err_code;
1927 x_err_msg := l_err_msg;
1928
1929 WHEN OTHERS THEN
1930 ROLLBACK;
1931 x_err_num := SQLCODE;
1932 x_err_code := NULL;
1933 x_err_msg := SUBSTR('CSTPPWRO.process_wip_resovhd_txns('
1934 || to_char(l_stmt_num)
1935 || '): '
1936 ||SQLERRM,1,2000);
1937
1938 END process_wip_resovhd_txns;
1939
1940 /*---------------------------------------------------------------------------*
1941 | BUG 9342058 |
1942 | PUBLIC PROCEDURE |
1943 | process_unrelieved_scrap_qty |
1944 *----------------------------------------------------------------------------*/
1945 PROCEDURE process_unrelieved_scrap_qty (
1946 p_pac_period_id IN NUMBER,
1947 p_start_date IN DATE,
1948 p_end_date IN DATE,
1949 p_cost_group_id IN NUMBER,
1950 p_cost_type_id IN NUMBER,
1951 x_err_num OUT NOCOPY NUMBER,
1952 x_err_code OUT NOCOPY VARCHAR2,
1953 x_err_msg OUT NOCOPY VARCHAR2)
1954
1955 IS
1956
1957
1958 l_stmt_num NUMBER;
1959 l_err_num NUMBER;
1960 l_err_code VARCHAR2(240);
1961 l_err_msg VARCHAR2(2000);
1962
1963
1964 -------------------------------------------------------------------
1965 -- This Cursor will get all scrap quantity without scrap account
1966 -- in this period for a given assembly
1967 -- Populates all the wip entities for this assembly
1968 --------------------------------------------------------------------
1969
1970 CURSOR scrap_no_account is
1971 SELECT wmt.transaction_id wip_txn_id,
1972 wmt.wip_entity_id wip_entity,
1973 wmt.line_id line,
1974 wmt.fm_operation_seq_num from_op_seq,
1975 wmt.fm_intraoperation_step_type from_op_step,
1976 wmt.to_operation_seq_num to_op_seq,
1977 wmt.to_intraoperation_step_type to_op_step,
1978 wmt.primary_quantity pri_qty
1979 FROM cst_cost_group_assignments ccga,
1980 wip_move_transactions wmt
1981 WHERE wmt.transaction_date BETWEEN TRUNC(p_start_date)
1982 AND (TRUNC(p_end_date) + 0.99999)
1983 AND wmt.organization_id = ccga.organization_id
1984 AND ccga.cost_group_id = p_cost_group_id
1985 AND wmt.primary_item_id is not NULL
1986 AND wmt.scrap_account_id is null
1987 AND (wmt.fm_intraoperation_step_type = 5 OR
1988 wmt.to_intraoperation_step_type = 5);
1989
1990
1991
1992 BEGIN
1993
1994 ----------------------------------------------------------------------
1995 -- Initialize Variables
1996 ----------------------------------------------------------------------
1997
1998 l_err_num := 0;
1999 l_err_code := '';
2000 l_err_msg := '';
2001
2002
2003
2004 FOR c_scrap_rec IN scrap_no_account LOOP
2005 l_stmt_num := 10;
2006
2007 IF (c_scrap_rec.from_op_step <> 5 and c_scrap_rec.to_op_step = 5) then
2008
2009 l_stmt_num := 20;
2010 UPDATE WIP_PAC_PERIOD_BALANCES
2011 SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) + c_scrap_rec.pri_qty
2012 WHERE wip_entity_id = c_scrap_rec.wip_entity
2013 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
2014 AND operation_seq_num <= c_scrap_rec.to_op_seq
2015 AND cost_type_id = p_cost_type_id
2016 AND pac_period_id = p_pac_period_id
2017 AND cost_group_id = p_cost_group_id;
2018
2019 END IF;
2020
2021 IF (c_scrap_rec.from_op_step = 5 and c_scrap_rec.to_op_step <> 5) then
2022
2023 l_stmt_num := 30;
2024
2025 UPDATE WIP_PAC_PERIOD_BALANCES
2026 SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) - c_scrap_rec.pri_qty
2027 WHERE wip_entity_id = c_scrap_rec.wip_entity
2028 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
2029 AND operation_seq_num <= c_scrap_rec.from_op_seq
2030 AND cost_type_id = p_cost_type_id
2031 AND pac_period_id = p_pac_period_id
2032 AND cost_group_id = p_cost_group_id;
2033
2034 END IF;
2035
2036 IF (c_scrap_rec.from_op_step = 5 and c_scrap_rec.to_op_step = 5) then
2037
2038 l_stmt_num := 40;
2039
2040 IF (c_scrap_rec.from_op_seq > c_scrap_rec.to_op_seq) THEN
2041
2042 l_stmt_num := 50;
2043 UPDATE WIP_PAC_PERIOD_BALANCES
2044 SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) - c_scrap_rec.pri_qty
2045 WHERE wip_entity_id = c_scrap_rec.wip_entity
2046 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
2047 AND operation_seq_num > c_scrap_rec.to_op_seq
2048 AND operation_seq_num <= c_scrap_rec.from_op_seq
2049 AND cost_type_id = p_cost_type_id
2050 AND pac_period_id = p_pac_period_id
2051 AND cost_group_id = p_cost_group_id;
2052
2053 ELSE
2054
2055 l_stmt_num := 60;
2056
2057 UPDATE WIP_PAC_PERIOD_BALANCES
2058 SET unrelieved_scrap_quantity = nvl(unrelieved_scrap_quantity,0) + c_scrap_rec.pri_qty
2059 WHERE wip_entity_id = c_scrap_rec.wip_entity
2060 AND nvl(line_id,-99) = decode(wip_entity_type, 4, -99, nvl(c_scrap_rec.line,-99))
2061 AND operation_seq_num > c_scrap_rec.from_op_seq
2062 AND operation_seq_num <= c_scrap_rec.to_op_seq
2063 AND cost_type_id = p_cost_type_id
2064 AND pac_period_id = p_pac_period_id
2065 AND cost_group_id = p_cost_group_id;
2066
2067 END IF;
2068 END IF;
2069
2070 END LOOP;
2071
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 ROLLBACK;
2076 x_err_num := SQLCODE;
2077 x_err_code := NULL;
2078 x_err_msg := SUBSTR('CSTPPWRO.process_unrelieved_scrap_qty('
2079 || to_char(l_stmt_num)
2080 || '): '
2081 ||SQLERRM,1,2000);
2082
2083 END process_unrelieved_scrap_qty;
2084
2085
2086 END cstppwro;