[Home] [Help]
PACKAGE BODY: APPS.CSTPCFMS
Source
1 PACKAGE BODY CSTPCFMS AS
2 /* $Header: CSTCFMSB.pls 120.1.12010000.2 2008/10/27 21:45:33 hyu ship $ */
3
4 G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 PROCEDURE debug
7 ( line IN VARCHAR2,
8 msg_prefix IN VARCHAR2 DEFAULT 'CST',
9 msg_module IN VARCHAR2 DEFAULT 'CSTPCFMS',
10 msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT)
11 IS
12 l_msg_prefix VARCHAR2(64);
13 l_msg_level NUMBER;
14 l_msg_module VARCHAR2(256);
15 l_beg_end_suffix VARCHAR2(15);
16 l_org_cnt NUMBER;
17 l_line VARCHAR2(32767);
18 BEGIN
19 l_line := line;
20 l_msg_prefix := msg_prefix;
21 l_msg_level := msg_level;
22 l_msg_module := msg_module;
23 IF (INSTRB(upper(l_line), 'EXCEPTION') <> 0) THEN
24 l_msg_level := FND_LOG.LEVEL_EXCEPTION;
25 END IF;
26 IF l_msg_level <> FND_LOG.LEVEL_EXCEPTION AND G_DEBUG = 'N' THEN
27 RETURN;
28 END IF;
29 IF ( l_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
30 FND_LOG.STRING(l_msg_level, l_msg_module, SUBSTRB(l_line,1,4000));
31 END IF;
32 EXCEPTION
33 WHEN OTHERS THEN RAISE;
34 END debug;
35
36
37
38 FUNCTION wip_cfm_cbr (
39 i_org_id NUMBER,
40 i_user_id NUMBER,
41 i_login_id NUMBER,
42 i_acct_period_id NUMBER,
43 i_wip_entity_id NUMBER,
44 err_buf OUT NOCOPY VARCHAR2)
45 RETURN INTEGER
46 IS
47 where_num NUMBER;
48 BEGIN
49 err_buf := ' ';
50
51 debug('wip_cfm_cbr+');
52
53 /*----------------------------------------------------------+
54 | Process CFM |
55 | |
56 | Create a new row in WIP_PERIOD_BALANCES |
57 | if the row does not exist for a certain acct_period_id, |
58 | wip_entity_id, org_id. |
59 | Also, create new rows in WIP_PERIOD_BALANCES |
60 | for other accounting periods that has not been created |
61 | yet for a certain wip_entity_id and org_id. |
62 +-----------------------------------------------------------*/
63 where_num := 100;
64
65 debug(where_num);
66
67 INSERT INTO wip_period_balances
68 (acct_period_id, wip_entity_id,
69 repetitive_schedule_id, last_update_date,
70 last_updated_by, creation_date,
71 created_by, last_update_login,
72 organization_id, class_type,
73 tl_resource_in, tl_overhead_in, tl_outside_processing_in,
74 pl_material_in, pl_material_overhead_in, pl_resource_in, pl_overhead_in, pl_outside_processing_in,
75 tl_material_out, tl_material_overhead_out, tl_resource_out, tl_overhead_out, tl_outside_processing_out,
76 pl_material_out, pl_material_overhead_out, pl_resource_out, pl_overhead_out, pl_outside_processing_out,
77 pl_material_var, pl_material_overhead_var, pl_resource_var, pl_outside_processing_var,pl_overhead_var,
78 tl_material_var, tl_material_overhead_var, tl_resource_var, tl_outside_processing_var,tl_overhead_var)
79 SELECT
80 oap.acct_period_id, i_wip_entity_id,
81 NULL, SYSDATE,
82 i_user_id, SYSDATE,
83 i_user_id, i_login_id,
84 i_org_id, wac.class_type,
85 0,0,0,
86 0,0,0,0,0,
87 0,0,0,0,0,
88 0,0,0,0,0,
89 0,0,0,0,0,
90 0,0,0,0,0
91 FROM wip_flow_schedules wcs,
92 wip_accounting_classes wac,
93 org_acct_periods oap
94 WHERE
95 wcs.organization_id = i_org_id
96 AND wcs.wip_entity_id = i_wip_entity_id
97 AND wac.class_code = wcs.class_code
98 AND wac.organization_id = i_org_id
99 AND oap.acct_period_id >= i_acct_period_id
100 AND oap.organization_id = i_org_id
101 AND oap.acct_period_id >
102 (SELECT nvl(max(acct_period_id),0)
103 FROM wip_period_balances
104 WHERE organization_id = i_org_id
105 AND wip_entity_id = i_wip_entity_id)
106 AND NOT EXISTS
107 (SELECT 'x' FROM wip_period_balances
108 WHERE organization_id = i_org_id
109 AND acct_period_id = i_acct_period_id
110 AND wip_entity_id = i_wip_entity_id);
111
112 debug('wip_cfm_cbr-');
113 RETURN(0); /* No error */
114
115 EXCEPTION
116 WHEN OTHERS THEN
117 ROLLBACK;
118 debug('CSTPCFMS:WIP_CFM_CBR:' || to_char(where_num) || substr(SQLERRM,1,150));
119 err_buf := 'CSTPCFMS:WIP_CFM_CBR' || to_char(where_num) || substr(SQLERRM,1,150);
120 RETURN(SQLCODE);
121 END wip_cfm_cbr;
122
123 /************************************************
124 Completion for CFM
125 ************************************************/
126 PROCEDURE wip_cfm_complete (
127 i_trx_id IN NUMBER,
128 i_org_id IN NUMBER,
129 i_inv_item_id IN NUMBER,
130 i_txn_qty IN NUMBER,
131 i_wip_entity_id IN NUMBER,
132 i_txn_src_type_id IN NUMBER,
133 i_flow_schedule IN NUMBER,
134 i_txn_action_id IN NUMBER,
135 i_user_id IN NUMBER,
136 i_login_id IN NUMBER,
137 i_request_id IN NUMBER,
138 i_prog_appl_id IN NUMBER,
139 i_prog_id IN NUMBER,
140 err_num OUT NOCOPY NUMBER,
141 err_code OUT NOCOPY VARCHAR2,
142 err_msg OUT NOCOPY VARCHAR2)
143 IS
144 stmt_num NUMBER;
145 BEGIN
146
147 debug('wip_cfm_complete+');
148
149 -- initialize variables
150 err_num := 0;
151 err_code := ' ';
152 err_msg := ' ';
153
154
155 /*-------------------------------
156 Make sure it is a CFM completion
157 --------------------------------*/
158
159 --
160 -- call by cfm scrap also
161 --
162
163 IF (i_txn_src_type_id = 5
164 AND i_flow_schedule = 1
165 AND (i_txn_action_id = 31 OR (i_txn_action_id = 30 AND i_txn_qty>0))) THEN
166
167 stmt_num := 10;
168 debug(stmt_num);
169 INSERT INTO mtl_cst_txn_cost_details
170 (
171 transaction_id,
172 organization_id,
173 inventory_item_id,
174 cost_element_id,
175 level_type,
176 transaction_cost,
177 new_average_cost,
178 percentage_change,
179 value_change,
180 last_update_date,
181 last_updated_by,
182 creation_date,
183 created_by,
184 last_update_login,
185 request_id,
186 program_application_id,
187 program_id,
188 program_update_date)
189 SELECT
190 i_trx_id,
191 i_org_id,
192 i_inv_item_id,
193 cce.cost_element_id,
194 1,
195 decode(cce.cost_element_id,
196 1,sum(0 - nvl(tl_material_out,0)),
197 2,sum(0 - nvl(tl_material_overhead_out,0)),
198 3,sum(nvl(tl_resource_in,0) - nvl(tl_resource_out,0)),
199 4,sum(nvl(tl_outside_processing_in,0) - nvl(tl_outside_processing_out,0)),
200 5,sum(nvl(tl_overhead_in,0) - nvl(tl_overhead_out,0)))/ABS(i_txn_qty),
201 NULL,
202 NULL,
203 NULL,
204 SYSDATE,
205 i_user_id,
206 SYSDATE,
207 i_user_id,
208 i_login_id,
209 i_request_id,
210 i_prog_appl_id,
211 i_prog_id,
212 SYSDATE
213 FROM
214 cst_cost_elements cce,
215 wip_period_balances wpb
216 WHERE
217 wpb.wip_entity_id = i_wip_entity_id AND
218 wpb.organization_id = i_org_id AND
219 cce.cost_element_id <> 2
220 GROUP BY
221 cce.cost_element_id
222 HAVING
223 decode(cce.cost_element_id,
224 1,sum(0 - nvl(tl_material_out,0)),
225 2,sum(0 - nvl(tl_material_overhead_out,0)),
226 3,sum(nvl(tl_resource_in,0) - nvl(tl_resource_out,0)),
227 4,sum(nvl(tl_outside_processing_in,0) - nvl(tl_outside_processing_out,0)),
228 5,sum(nvl(tl_overhead_in,0) - nvl(tl_overhead_out,0))) > 0;
229
230 stmt_num := 20;
231 debug(stmt_num);
232 INSERT INTO mtl_cst_txn_cost_details
233 (
234 transaction_id,
235 organization_id,
236 inventory_item_id,
237 cost_element_id,
238 level_type,
239 transaction_cost,
240 new_average_cost,
241 percentage_change,
242 value_change,
243 last_update_date,
244 last_updated_by,
245 creation_date,
246 created_by,
247 last_update_login,
248 request_id,
249 program_application_id,
250 program_id,
251 program_update_date)
252 SELECT
253 i_trx_id,
254 i_org_id,
255 i_inv_item_id,
256 cce.cost_element_id,
257 2,
258 decode(cce.cost_element_id,
259 1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)),
260 2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)),
261 3,sum(nvl(pl_resource_in,0) - nvl(pl_resource_out,0)),
262 4,sum(nvl(pl_outside_processing_in,0)- nvl(pl_outside_processing_out,0)),
263 5,sum(nvl(pl_overhead_in,0) - nvl(pl_overhead_out,0)))/ABS(i_txn_qty),
264 NULL,
265 NULL,
266 NULL,
267 SYSDATE,
268 i_user_id,
269 SYSDATE,
270 i_user_id,
271 i_login_id,
272 i_request_id,
273 i_prog_appl_id,
274 i_prog_id,
275 SYSDATE
276 FROM
277 cst_cost_elements cce,
278 wip_period_balances wpb
279 WHERE
280 wpb.wip_entity_id = i_wip_entity_id AND
281 wpb.organization_id = i_org_id
282 GROUP BY
283 cce.cost_element_id
284 HAVING
285 decode(cce.cost_element_id,
286 1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)),
287 2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)),
288 3,sum(nvl(pl_resource_in,0) - nvl(pl_resource_out,0)),
289 4,sum(nvl(pl_outside_processing_in,0)- nvl(pl_outside_processing_out,0)),
290 5,sum(nvl(pl_overhead_in,0) - nvl(pl_overhead_out,0))) > 0;
291
292 IF (i_txn_action_id = 30 AND i_txn_qty>0) THEN
293 debug('25');
294
295 INSERT INTO WIP_SCRAP_VALUES
296 (
297 transaction_id,
298 level_type,
299 cost_element_id,
300 cost_update_id,
301 last_update_date,
302 last_updated_by,
303 created_by,
304 creation_date,
305 last_update_login,
306 cost_element_value,
307 request_id,
308 program_application_id,
309 program_id,
310 program_update_date
311 )
312 SELECT
313 i_trx_id,
314 level_type,
315 cost_element_id,
316 NULL,
317 SYSDATE,
318 i_user_id,
319 i_user_id,
320 SYSDATE,
321 i_login_id,
322 transaction_cost,
323 i_request_id,
324 i_prog_appl_id,
325 i_prog_id,
326 SYSDATE
327 FROM
328 mtl_cst_txn_cost_details
329 WHERE
330 transaction_id = i_trx_id;
331 END IF;
332 END IF;
333 debug('wip_cfm_complete-');
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 err_num := SQLCODE;
338 err_msg := 'CSTPCFMS:' || 'wip_cfm_complete:' || to_char(stmt_num) ||
339 ' ' || substr(SQLERRM,1,150);
340 debug(err_msg);
341 END wip_cfm_complete;
342
343
344 /************************************************
345 Assembly Return for CFM
346 ************************************************/
347
348 PROCEDURE wip_cfm_assy_return (
349 i_trx_id IN NUMBER,
350 i_org_id IN NUMBER,
351 i_inv_item_id IN NUMBER,
352 i_txn_qty IN NUMBER,
353 i_wip_entity_id IN NUMBER,
354 i_txn_src_type_id IN NUMBER,
355 i_flow_schedule IN NUMBER,
356 i_txn_action_id IN NUMBER,
357 i_user_id IN NUMBER,
358 i_login_id IN NUMBER,
359 i_request_id IN NUMBER,
360 i_prog_appl_id IN NUMBER,
361 i_prog_id IN NUMBER,
362 err_num OUT NOCOPY NUMBER,
363 err_code OUT NOCOPY VARCHAR2,
364 err_msg OUT NOCOPY VARCHAR2)
365 IS
366 stmt_num NUMBER;
367 BEGIN
368
369 debug('wip_cfm_assy_return +');
370
371 -- initialize variables
372 err_num := 0;
373 err_code := ' ';
374 err_msg := ' ';
375
376
377 /*-----------------------------------
378 Make sure it is a CFM assembly return
379 -------------------------------------*/
380
381 --
382 -- call by cfm scrap return also
383 --
384 IF (i_txn_src_type_id = 5
385 AND i_flow_schedule = 1
386 AND (i_txn_action_id = 32 OR (i_txn_action_id = 30 AND i_txn_qty<0))) THEN
387
388 stmt_num := 10;
389
390 debug(stmt_num);
391
392 INSERT INTO mtl_cst_txn_cost_details
393 (
394 transaction_id,
395 organization_id,
396 inventory_item_id,
397 cost_element_id,
398 level_type,
399 transaction_cost,
400 new_average_cost,
401 percentage_change,
402 value_change,
403 last_update_date,
404 last_updated_by,
405 creation_date,
406 created_by,
407 last_update_login,
408 request_id,
409 program_application_id,
410 program_id,
411 program_update_date)
412 SELECT
413 i_trx_id,
414 i_org_id,
415 i_inv_item_id,
416 cce.cost_element_id,
417 1,
418 decode(cce.cost_element_id,
419 1,sum(nvl(tl_material_out,0) - 0),
420 2,sum(nvl(tl_material_overhead_out,0) - 0),
421 3,sum(nvl(tl_resource_out,0) - nvl(tl_resource_in,0)),
422 4,sum(nvl(tl_outside_processing_out,0)- nvl(tl_outside_processing_in,0)),
423 5,sum(nvl(tl_overhead_out,0) - nvl(tl_overhead_in,0)))/ABS(i_txn_qty),
424 NULL,
425 NULL,
426 NULL,
427 SYSDATE,
428 i_user_id,
429 SYSDATE,
430 i_user_id,
431 i_login_id,
432 i_request_id,
433 i_prog_appl_id,
434 i_prog_id,
435 SYSDATE
436 FROM
437 cst_cost_elements cce,
438 wip_period_balances wpb
439 WHERE
440 wpb.wip_entity_id = i_wip_entity_id AND
441 wpb.organization_id = i_org_id AND
442 cce.cost_element_id <> 2
443 GROUP BY
444 cce.cost_element_id
445 HAVING
446 decode(cce.cost_element_id,
447 1,sum(nvl(tl_material_out,0) - 0),
448 2,sum(nvl(tl_material_overhead_out,0) - 0),
449 3,sum(nvl(tl_resource_out,0) - nvl(tl_resource_in,0)),
450 4,sum(nvl(tl_outside_processing_out,0)- nvl(tl_outside_processing_in,0)),
451 5,sum(nvl(tl_overhead_out,0) - nvl(tl_overhead_in,0))) > 0;
452
453 stmt_num := 20;
454 debug(stmt_num);
455 INSERT INTO mtl_cst_txn_cost_details
456 (
457 transaction_id,
458 organization_id,
459 inventory_item_id,
460 cost_element_id,
461 level_type,
462 transaction_cost,
463 new_average_cost,
464 percentage_change,
465 value_change,
466 last_update_date,
467 last_updated_by,
468 creation_date,
469 created_by,
470 last_update_login,
471 request_id,
472 program_application_id,
473 program_id,
474 program_update_date)
475 SELECT
476 i_trx_id,
477 i_org_id,
478 i_inv_item_id,
479 cce.cost_element_id,
480 2,
481 decode(cce.cost_element_id,
482 1,sum(nvl(pl_material_out,0) - nvl(pl_material_in,0)),
483 2,sum(nvl(pl_material_overhead_out,0) - nvl(pl_material_overhead_in,0)),
484 3,sum(nvl(pl_resource_out,0) - nvl(pl_resource_in,0)),
485 4,sum(nvl(pl_outside_processing_out,0)- nvl(pl_outside_processing_in,0)),
486 5,sum(nvl(pl_overhead_out,0) - nvl(pl_overhead_in,0)))/ABS(i_txn_qty),
487 NULL,
488 NULL,
489 NULL,
490 SYSDATE,
491 i_user_id,
492 SYSDATE,
493 i_user_id,
494 i_login_id,
495 i_request_id,
496 i_prog_appl_id,
497 i_prog_id,
498 SYSDATE
499 FROM
500 cst_cost_elements cce,
501 wip_period_balances wpb
502 WHERE
503 wpb.wip_entity_id = i_wip_entity_id AND
504 wpb.organization_id = i_org_id
505 GROUP BY
506 cce.cost_element_id
507 HAVING
508 decode(cce.cost_element_id,
509 1,sum(nvl(pl_material_out,0) - nvl(pl_material_in,0)),
510 2,sum(nvl(pl_material_overhead_out,0) - nvl(pl_material_overhead_in,0)),
511 3,sum(nvl(pl_resource_out,0) - nvl(pl_resource_in,0)),
512 4,sum(nvl(pl_outside_processing_out,0)- nvl(pl_outside_processing_in,0)),
513 5,sum(nvl(pl_overhead_out,0) - nvl(pl_overhead_in,0))) > 0;
514
515 IF (i_txn_action_id = 30 AND i_txn_qty<0) THEN
516 debug('25');
517 INSERT INTO WIP_SCRAP_VALUES
518 (
519 transaction_id,
520 level_type,
521 cost_element_id,
522 cost_update_id,
523 last_update_date,
524 last_updated_by,
525 created_by,
526 creation_date,
527 last_update_login,
528 cost_element_value,
529 request_id,
530 program_application_id,
531 program_id,
532 program_update_date
533 )
534 SELECT
535 i_trx_id,
536 level_type,
537 cost_element_id,
538 NULL,
539 SYSDATE,
540 i_user_id,
541 i_user_id,
542 SYSDATE,
543 i_login_id,
544 transaction_cost,
545 i_request_id,
546 i_prog_appl_id,
547 i_prog_id,
548 SYSDATE
549 FROM
550 mtl_cst_txn_cost_details
551 WHERE
552 transaction_id = i_trx_id;
553 END IF;
554
555 END IF;
556 debug('wip_cfm_assy_return-');
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 err_num := SQLCODE;
561 err_msg := 'CSTPCFMS:' || 'wip_cfm_assy_return:' || to_char(stmt_num) ||
562 ' ' || substr(SQLERRM,1,150);
563 debug(err_msg);
564 END wip_cfm_assy_return;
565
566 PROCEDURE wip_cfm_var_relief (
567 i_wip_entity_id IN NUMBER,
568 i_txn_action_id IN NUMBER,
569 i_acct_period_id IN NUMBER,
570 i_org_id IN NUMBER,
571 i_txn_date IN DATE,
572 i_user_id IN NUMBER,
573 i_login_id IN NUMBER,
574 i_request_id IN NUMBER,
575 i_prog_id IN NUMBER,
576 i_prog_appl_id IN NUMBER,
577 err_num OUT NOCOPY NUMBER,
578 err_code OUT NOCOPY VARCHAR2,
579 err_msg OUT NOCOPY VARCHAR2)
580 IS
581
582 stmt_num NUMBER;
583 l_rowcount NUMBER;
584 l_txn_id NUMBER;
585 no_wpb_rows EXCEPTION;
586
587 l_trx_info CST_XLA_PVT.t_xla_wip_trx_info;
588 l_return_status VARCHAR2(10);
589 l_msg_count NUMBER;
590 l_msg_data VARCHAR2(2000);
591 l_nb NUMBER := 0;
592 BEGIN
593
594 debug('wip_cfm_var_relief+');
595
596
597 stmt_num := 10;
598
599 select count(*)
600 into
601 l_rowcount
602 from
603 wip_period_balances
604 where
605 wip_entity_id = i_wip_entity_id and
606 acct_period_id = i_acct_period_id;
607
608 IF (l_rowcount = 0) then
609 raise no_wpb_rows;
610 END IF;
611
612
613 -- Get the next value in the sequence to create a txn row
614
615 stmt_num := 20;
616
617 select wip_transactions_s.nextval
618 into
619 l_txn_id from dual;
620
621 -- Insert the elemental CFM variance.
622
623 stmt_num := 20;
624
625
626 INSERT INTO wip_transaction_accounts
627 (WIP_SUB_LEDGER_ID,
628 TRANSACTION_ID, REFERENCE_ACCOUNT,
629 LAST_UPDATE_DATE, LAST_UPDATED_BY,
630 CREATION_DATE, CREATED_BY,
631 LAST_UPDATE_LOGIN, ORGANIZATION_ID,
632 TRANSACTION_DATE, WIP_ENTITY_ID,
633 REPETITIVE_SCHEDULE_ID, ACCOUNTING_LINE_TYPE,
634 TRANSACTION_VALUE, BASE_TRANSACTION_VALUE,
635 CONTRA_SET_ID, PRIMARY_QUANTITY,
636 RATE_OR_AMOUNT, BASIS_TYPE,
637 RESOURCE_ID, COST_ELEMENT_ID,
638 ACTIVITY_ID, CURRENCY_CODE,
639 CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE,
640 CURRENCY_CONVERSION_RATE,
641 REQUEST_ID, PROGRAM_APPLICATION_ID,
642 PROGRAM_ID, PROGRAM_UPDATE_DATE)
643 SELECT
644 CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
645 l_txn_id,
646 decode(cce.cost_element_id,
647 1, wdj.material_account,
648 2, wdj.material_overhead_account,
649 3, wdj.resource_account,
650 4, wdj.outside_processing_account,
651 5, wdj.overhead_account),
652 SYSDATE,i_user_id,SYSDATE,i_user_id,i_login_id,
653 i_org_id,i_txn_date,i_wip_entity_id,
654 NULL,7,NULL,
655 decode(cce.cost_element_id,
656 1, (NVL(wpb.pl_material_out,0)
657 - NVL(wpb.pl_material_in,0)
658 + NVL(wpb.pl_material_var,0)
659 + NVL(wpb.tl_material_out,0)
660 - 0
661 + NVL(wpb.tl_material_var,0)),
662 2, (NVL(wpb.pl_material_overhead_out,0)
663 - NVL(wpb.pl_material_overhead_in,0)
664 + NVL(wpb.pl_material_overhead_var,0)
665 + NVL(wpb.tl_material_overhead_out,0)
666 - 0
667 + NVL(wpb.tl_material_overhead_var,0)),
668 3, (NVL(wpb.pl_resource_out,0)
669 - NVL(wpb.pl_resource_in,0)
670 + NVL(wpb.pl_resource_var,0)
671 + NVL(wpb.tl_resource_out,0)
672 - NVL(wpb.tl_resource_in,0)
673 + NVL(wpb.tl_resource_var,0)),
674 4, (NVL(wpb.pl_outside_processing_out,0)
675 - NVL(wpb.pl_outside_processing_in,0)
676 + NVL(wpb.pl_outside_processing_var,0)
677 + NVL(wpb.tl_outside_processing_out,0)
678 - NVL(wpb.tl_outside_processing_in,0)
679 + NVL(wpb.tl_outside_processing_var,0)),
680 5, (NVL(wpb.pl_overhead_out,0)
681 - NVL(wpb.pl_overhead_in,0)
682 + NVL(wpb.pl_overhead_var,0)
683 + NVL(wpb.tl_overhead_out,0)
684 - NVL(wpb.tl_overhead_in,0)
685 + NVL(wpb.tl_overhead_var,0))),
686 i_wip_entity_id,NULL, NULL, NULL, NULL,
687 cce.cost_element_id,
688 NULL, NULL, NULL, NULL, NULL,
689 i_request_id,i_prog_appl_id,i_prog_id,SYSDATE
690 FROM
691 wip_period_balances wpb,
692 wip_flow_schedules wdj,
693 cst_cost_elements cce
694 WHERE
695 wpb.wip_entity_id = wdj.wip_entity_id and
696 wdj.wip_entity_id = i_wip_entity_id and
697 wpb.acct_period_id = i_acct_period_id;
698
699 -- Inser the single level CFM variance
700
701 stmt_num := 30;
702
703 INSERT INTO wip_transaction_accounts
704 ( WIP_SUB_LEDGER_ID,
705 TRANSACTION_ID, REFERENCE_ACCOUNT,
706 LAST_UPDATE_DATE, LAST_UPDATED_BY,
707 CREATION_DATE, CREATED_BY,
708 LAST_UPDATE_LOGIN, ORGANIZATION_ID,
709 TRANSACTION_DATE, WIP_ENTITY_ID,
710 REPETITIVE_SCHEDULE_ID, ACCOUNTING_LINE_TYPE,
711 TRANSACTION_VALUE, BASE_TRANSACTION_VALUE,
712 CONTRA_SET_ID, PRIMARY_QUANTITY,
713 RATE_OR_AMOUNT, BASIS_TYPE,
714 RESOURCE_ID, COST_ELEMENT_ID,
715 ACTIVITY_ID, CURRENCY_CODE,
716 CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE,
717 CURRENCY_CONVERSION_RATE,
718 REQUEST_ID, PROGRAM_APPLICATION_ID,
719 PROGRAM_ID, PROGRAM_UPDATE_DATE)
720 SELECT
721 CST_WIP_SUB_LEDGER_ID_S.NEXTVAL,
722 l_txn_id,
723 decode(cce.cost_element_id,
724 1, wfs.material_variance_account,
725 3, wfs.resource_variance_account,
726 4, wfs.outside_proc_variance_account,
727 5, wfs.overhead_variance_account),
728 SYSDATE,i_user_id,SYSDATE,i_user_id,i_login_id,
729 i_org_id,i_txn_date,i_wip_entity_id,
730 NULL,8,NULL,
731 decode(cce.cost_element_id,
732 1, -1 * (NVL(wpb.pl_material_out,0)
733 - NVL(wpb.pl_material_in,0)
734 + NVL(wpb.pl_material_var,0)
735 + NVL(wpb.pl_material_overhead_out,0)
736 - NVL(wpb.pl_material_overhead_in,0)
737 + NVL(wpb.pl_material_overhead_var,0)
738 + NVL(wpb.pl_resource_out,0)
739 - NVL(wpb.pl_resource_in,0)
740 + NVL(wpb.pl_resource_var,0)
741 + NVL(wpb.pl_overhead_out,0)
742 - NVL(wpb.pl_overhead_in,0)
743 + NVL(wpb.pl_overhead_var,0)
744 + NVL(wpb.pl_outside_processing_out,0)
745 - NVL(wpb.pl_outside_processing_in,0)
746 + NVL(wpb.pl_outside_processing_var,0)
747 + NVL(wpb.tl_material_out,0)
748 - 0
749 + NVL(wpb.tl_material_var,0)
750 + NVL(wpb.tl_material_overhead_out,0)
751 - 0
752 + NVL(wpb.tl_material_overhead_var,0)),
753 3, -1 * (NVL(wpb.tl_resource_out,0)
754 - NVL(wpb.tl_resource_in,0)
755 + NVL(wpb.tl_resource_var,0)),
756 4, -1 * (NVL(wpb.tl_outside_processing_out,0)
757 - NVL(wpb.tl_outside_processing_in,0)
758 + NVL(wpb.tl_outside_processing_var,0)),
759 5, -1 * (NVL(wpb.tl_overhead_out,0)
760 - NVL(wpb.tl_overhead_in,0)
761 + NVL(wpb.tl_overhead_var,0))),
762 i_wip_entity_id,NULL, NULL, NULL, NULL,
763 cce.cost_element_id,
764 NULL, NULL, NULL, NULL, NULL,
765 i_request_id,i_prog_appl_id,i_prog_id,SYSDATE
766 FROM
767 wip_period_balances wpb,
768 wip_flow_schedules wfs,
769 cst_cost_elements cce
770 WHERE
771 wpb.wip_entity_id = wfs.wip_entity_id and
772 wpb.acct_period_id = i_acct_period_id and
773 wfs.wip_entity_id = i_wip_entity_id and
774 cce.cost_element_id <> 2;
775
776
777 l_nb := sql%rowcount;
778
779
780 -- Update WPB
781
782 stmt_num := 40;
783 /*Substraction By current Variance is removed for the Bug#1784535*/
784 UPDATE WIP_PERIOD_BALANCES wpb
785 SET (LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
786 PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
787 PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
788 PL_OVERHEAD_VAR, TL_MATERIAL_VAR,
789 TL_MATERIAL_OVERHEAD_VAR, TL_RESOURCE_VAR,
790 TL_OUTSIDE_PROCESSING_VAR, TL_OVERHEAD_VAR ) =
791 (SELECT i_user_id, SYSDATE, i_login_id,
792 NVL(PL_MATERIAL_IN,0)
793 - NVL(PL_MATERIAL_OUT,0),
794 NVL(PL_MATERIAL_OVERHEAD_IN,0)
795 - NVL(PL_MATERIAL_OVERHEAD_OUT,0),
796 NVL(PL_RESOURCE_IN,0)
797 - NVL(PL_RESOURCE_OUT,0),
798 NVL(PL_OUTSIDE_PROCESSING_IN,0)
799 - NVL(PL_OUTSIDE_PROCESSING_OUT,0),
800 NVL(PL_OVERHEAD_IN,0)
801 - NVL(PL_OVERHEAD_OUT,0),
802 0
803 - NVL(TL_MATERIAL_OUT,0),
804 0
805 - NVL(TL_MATERIAL_OVERHEAD_OUT,0),
806 NVL(TL_RESOURCE_IN,0)
807 - NVL(TL_RESOURCE_OUT,0),
808 NVL(TL_OUTSIDE_PROCESSING_IN,0)
809 - NVL(TL_OUTSIDE_PROCESSING_OUT,0),
810 NVL(TL_OVERHEAD_IN,0)
811 - NVL(TL_OVERHEAD_OUT,0)
812 FROM WIP_PERIOD_BALANCES wpb2
813 WHERE wpb2.wip_entity_id = wpb.wip_entity_id
814 AND wpb2.acct_period_id = wpb.acct_period_id)
815 WHERE
816 wpb.wip_entity_id = i_wip_entity_id AND
817 wpb.acct_period_id = i_acct_period_id;
818
819
820 -- Insert a row into WIP trnsactions table.
821
822 stmt_num := 50;
823
824 INSERT INTO WIP_TRANSACTIONS
825 (TRANSACTION_ID, LAST_UPDATE_DATE,
826 LAST_UPDATED_BY, CREATION_DATE,
827 CREATED_BY, LAST_UPDATE_LOGIN,
828 ORGANIZATION_ID, WIP_ENTITY_ID,
829 ACCT_PERIOD_ID, DEPARTMENT_ID,
830 TRANSACTION_TYPE, TRANSACTION_DATE,
831 LINE_ID, SOURCE_CODE,
832 SOURCE_LINE_ID, OPERATION_SEQ_NUM,
833 RESOURCE_SEQ_NUM, EMPLOYEE_ID,
834 RESOURCE_ID, AUTOCHARGE_TYPE,
835 STANDARD_RATE_FLAG, USAGE_RATE_OR_AMOUNT,
836 BASIS_TYPE, TRANSACTION_QUANTITY,
837 TRANSACTION_UOM, PRIMARY_QUANTITY,
838 PRIMARY_UOM, ACTUAL_RESOURCE_RATE,
839 STANDARD_RESOURCE_RATE, CURRENCY_CODE,
840 CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE,
841 CURRENCY_CONVERSION_RATE, CURRENCY_ACTUAL_RESOURCE_RATE,
842 ACTIVITY_ID, REASON_ID,
843 REFERENCE, MOVE_TRANSACTION_ID,
844 PO_HEADER_ID, PO_LINE_ID,
845 RCV_TRANSACTION_ID, PRIMARY_ITEM_ID,
846 ATTRIBUTE_CATEGORY,
847 ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,
848 ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,
849 ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
850 ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,
851 REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
852 GROUP_ID,
853 project_id,
854 task_id,
855 pm_cost_collected)
856 SELECT
857 l_txn_id, SYSDATE,
858 i_user_id, SYSDATE,
859 i_user_id, i_login_id,
860 i_org_id, i_wip_entity_id,
861 i_acct_period_id, NULL,
862 6, i_txn_date,
863 NULL, NULL,
864 NULL, NULL,
865 NULL, NULL,
866 NULL, NULL,
867 NULL, NULL,
868 NULL, NULL,
869 NULL, NULL,
870 NULL, NULL,
871 NULL, NULL,
872 NULL, NULL,
873 NULL, NULL,
874 NULL, NULL,
875 NULL, NULL,
876 NULL, NULL,
877 NULL, NULL,
878 NULL, NULL,
879 NULL, NULL,
880 NULL, NULL,
881 NULL, NULL,
882 NULL, NULL,
883 NULL, NULL,
884 NULL, NULL,
885 NULL, NULL,
886 i_request_id, i_prog_appl_id,
887 i_prog_id, SYSDATE,
888 NULL, NULL,
889 NULL, NULL
890 from dual;
891
892 /* SLA Event Seeding */
893 --{BUG#7300970
894 stmt_num := 60;
895 debug('l_nb :'||l_nb);
896
897 IF l_nb > 0 THEN
898
899 l_trx_info.TRANSACTION_ID := l_txn_id;
900 l_trx_info.INV_ORGANIZATION_ID := i_org_id;
901 l_trx_info.WIP_RESOURCE_ID := -1;
902 l_trx_info.WIP_BASIS_TYPE_ID := -1;
903 l_trx_info.TXN_TYPE_ID := 6;
904 l_trx_info.TRANSACTION_DATE := i_txn_date;
905
906 CST_XLA_PVT.Create_WIPXLAEvent (
907 p_api_version => 1,
908 p_init_msg_list => FND_API.G_FALSE,
909 p_commit => FND_API.G_FALSE,
910 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
911 x_return_status => l_return_status,
912 x_msg_count => l_msg_count,
913 x_msg_data => l_msg_data,
914 p_trx_info => l_trx_info);
915
916 END IF;
917 --}
918 debug('wip_cfm_var_relief-');
919
920 EXCEPTION
921
922
923 WHEN no_wpb_rows then
924 rollback;
925 err_num := 9999;
926 err_code := 'CST_NO_BALANCE_ROW';
927 FND_MESSAGE.set_name('BOM', 'CST_NO_BALANCE_ROW');
928 err_msg := FND_MESSAGE.Get;
929 debug(err_msg);
930
931 WHEN OTHERS THEN
932 rollback;
933 err_num := SQLCODE;
934 err_msg := 'CSTPCFMS:' || 'wip_cfm_var_relief:' || to_char(stmt_num) ||
935 ' ' || substr(SQLERRM,1,150);
936 debug(err_msg);
937 END wip_cfm_var_relief;
938
939 END CSTPCFMS; /* end package body */