[Home] [Help]
PACKAGE BODY: APPS.CSTPSISC
Source
1 PACKAGE BODY CSTPSISC AS
2 /* $Header: CSTSISCB.pls 120.3.12010000.2 2008/10/31 11:01:25 prashkum ship $ */
3
4 -- PROCEDURE
5 -- ins_std_cost This function inserts standard cost in mcacd,
6 -- transaction cost in mctcd and sub-elemental
7 -- costs in macs.
8 --
9 --
10
11 procedure ins_std_cost(
12 I_ORG_ID IN NUMBER,
13 I_INV_ITEM_ID IN NUMBER,
14 I_TXN_ID IN NUMBER,
15 I_TXN_ACTION_ID IN NUMBER,
16 I_TXN_SOURCE_TYPE_ID IN NUMBER,
17 I_EXP_ITEM IN NUMBER,
18 I_EXP_SUB IN NUMBER,
19 I_TXN_COST IN NUMBER,
20 I_ACTUAL_COST IN NUMBER,
21 I_PRIOR_COST IN NUMBER,
22 I_USER_ID IN NUMBER,
23 I_LOGIN_ID IN NUMBER,
24 I_REQUEST_ID IN NUMBER,
25 I_PROG_APPL_ID IN NUMBER,
26 I_PROG_ID IN NUMBER,
27 O_Err_Num OUT NOCOPY NUMBER,
28 O_Err_Code OUT NOCOPY VARCHAR2,
29 O_Err_Msg OUT NOCOPY VARCHAR2
30 )
31 is
32 l_err_num number;
33 l_err_code varchar2(240);
34 l_err_msg varchar2(240);
35 l_profile_option number;
36
37 l_org_id number;
38 l_txfr_org_id number;
39 l_txn_qty number;
40 l_fob_point number;
41 l_stmt_num number;
42 ins_std_cost_error EXCEPTION;
43
44 /* EAM Acct Enh Project */
45 l_zero_cost_flag NUMBER := -1;
46 l_return_status VARCHAR2(1);
47 l_msg_count NUMBER := 0;
48 l_msg_data VARCHAR2(8000);
49 l_api_message VARCHAR2(8000);
50 l_debug VARCHAR2(100);
51 l_earn_moh NUMBER; /* Added for bug6157916 */
52 moh_rules_error EXCEPTION; /* Added for bug6157916 */
53
54 BEGIN
55 l_return_status := fnd_api.g_ret_sts_success;
56 l_msg_data := '';
57 l_err_num := 0;
58 l_err_code := '';
59 l_err_msg := '';
60
61 o_err_num := l_err_num;
62 o_err_code := l_err_code;
63 o_err_msg := l_err_msg;
64
65
66 -- we break down into two major cases
67 -- 1. PO delivery, RTV, PO delivery adjustment are exception cases
68 -- so we group them together. within this group, asset item in
69 -- asset sub is the only case that use standard cost. otherwise,
70 -- we're using po price.
71 -- 2. all other asset item transactions are another group
72 -- wip scrap uses actual cost, and the others use standard cost.
73
74 l_stmt_num := 10;
75
76 if (i_txn_source_type_id = 1) then
77 -- PO delivery, RTV, PO delivery adjustment
78
79 l_stmt_num := 20;
80
81 /*
82 gwu@us: This profile option is obsolete. Forcing the
83 profile option to 2 (INV/WIP) for all 11i installs.
84
85 -- Check the profile option. If profile option is 2 (INV/WIP), then
86 -- no need to insert into mctcd. This insertion has been done by
87 -- transaction manager (in inltpu module). It fixes bug 837911
88 l_profile_option := fnd_profile.value('CST_AVG_COSTING_OPTION');
89 */
90 l_profile_option := 2;
91
92 if (l_profile_option <> 2) then
93
94 INSERT INTO MTL_CST_TXN_COST_DETAILS (
95 transaction_id,
96 organization_id,
97 inventory_item_id,
98 cost_element_id,
99 level_type,
100 transaction_cost,
101 new_average_cost,
102 percentage_change,
103 value_change,
104 last_update_date,
105 last_updated_by,
106 creation_date,
107 created_by,
108 last_update_login,
109 request_id,
110 program_application_id,
111 program_id,
112 program_update_date
113 )
114 VALUES(
115 i_txn_id,
116 i_org_id,
117 i_inv_item_id,
118 1, -- material
119 1, -- this level
120 i_txn_cost,
121 NULL,
122 NULL,
123 NULL,
124 sysdate,
125 i_user_id,
126 sysdate,
127 i_user_id,
128 i_login_id,
129 i_request_id,
130 i_prog_appl_id,
131 i_prog_id,
132 sysdate);
133
134 end if;
135
136 l_stmt_num := 30;
137
138 if (i_exp_item = 0 and i_exp_sub = 0) then
139 -- asset item in asset sub is an exception case, use standard cost
140
141 l_stmt_num := 40;
142
143 INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
144 transaction_id,
145 organization_id,
146 layer_id,
147 cost_element_id,
148 level_type,
149 transaction_action_id,
150 last_update_date,
151 last_updated_by,
152 creation_date,
153 created_by,
154 last_update_login,
155 request_id,
156 program_application_id,
157 program_id,
158 program_update_date,
159 inventory_item_id,
160 actual_cost,
161 prior_cost,
162 new_cost,
163 insertion_flag,
164 variance_amount,
165 user_entered)
166 SELECT
167 i_txn_id,
168 i_org_id,
169 -1, -- layer_id = -1 for std.
170 cost_element_id,
171 level_type,
172 i_txn_action_id,
173 sysdate,
174 i_user_id,
175 sysdate,
176 i_user_id,
177 i_login_id,
178 i_request_id,
179 i_prog_appl_id,
180 i_prog_id,
181 sysdate,
182 i_inv_item_id,
183 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
184 2,ITEM_COST,
185 3,ITEM_COST,
186 4,ITEM_COST,
187 5,ITEM_COST),
188 1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
189 2,ITEM_COST,
190 3,ITEM_COST,
191 4,ITEM_COST,
192 5,ITEM_COST))),
193 NULL,
194 NULL,
195 'N',
196 NULL,
197 'N'
198 FROM CST_ITEM_COST_DETAILS CICD,
199 MTL_PARAMETERS MP
200 WHERE MP.organization_id = i_org_id
201 AND CICD.organization_id = MP.cost_organization_id
202 AND CICD.inventory_item_id = i_inv_item_id
203 AND CICD.cost_type_id = 1
204 GROUP BY CICD.level_type, CICD.cost_element_id;
205
206 l_stmt_num := 45;
207 /* Bug6157916 : Check if MOH Absorption rule has been defined
208 and if the MOH absorption rule is not overridden then
209 only insert into MACS
210 */
211 l_earn_moh := 1;
212 cst_mohRules_pub.apply_moh(
213 1.0,
214 p_organization_id => i_org_id,
215 p_earn_moh =>l_earn_moh,
216 p_txn_id => i_txn_id,
217 p_item_id => i_inv_item_id,
218 x_return_status => l_return_status,
219 x_msg_count => l_msg_count,
220 x_msg_data => l_msg_data);
221 IF l_return_status <> FND_API.g_ret_sts_success THEN
222 FND_FILE.put_line(FND_FILE.log, l_msg_data);
223 raise moh_rules_error;
224 END IF;
225 IF(l_earn_moh = 0) THEN
226 IF l_debug = 'Y' THEN
227 fnd_file.put_line(fnd_file.log, '---Material Overhead Absorption Overridden--');
228 END IF;
229 ELSE
230 l_stmt_num := 46;
231
232 INSERT INTO mtl_actual_cost_subelement(
233 transaction_id,
234 organization_id,
235 layer_id,
236 cost_element_id,
237 level_type,
238 resource_id,
239 last_update_date,
240 last_updated_by,
241 creation_date,
242 created_by,
243 last_update_login,
244 request_id,
245 program_application_id,
246 program_id,
247 program_update_date,
248 actual_cost,
249 user_entered)
250 SELECT i_txn_id,
251 i_org_id,
252 -1, -- layer_id = -1 for std.
253 cost_element_id,
254 level_type,
255 resource_id,
256 sysdate,
257 i_user_id,
258 sysdate,
259 i_user_id,
260 i_login_id,
261 i_request_id,
262 i_prog_appl_id,
263 i_prog_id,
264 sysdate,
265 item_cost,
266 'N'
267 FROM CST_ITEM_COST_DETAILS CICD,
268 MTL_PARAMETERS MP
269 WHERE MP.organization_id = i_org_id
270 AND CICD.organization_id = MP.cost_organization_id
271 AND CICD.inventory_item_id = i_inv_item_id
272 AND CICD.cost_type_id = 1
273 AND CICD.level_type = 1
274 AND CICD.cost_element_id = 2;
275 END IF ; /* l_earn_moh = 0 */
276
277 else
278 -- for all others, use transaction_cost which is po price
279 -- as a this level material cost
280
281 l_stmt_num := 50;
282
283 INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
284 transaction_id,
285 organization_id,
286 layer_id,
287 cost_element_id,
288 level_type,
289 transaction_action_id,
290 last_update_date,
291 last_updated_by,
292 creation_date,
293 created_by,
294 last_update_login,
295 request_id,
296 program_application_id,
297 program_id,
298 program_update_date,
299 inventory_item_id,
300 actual_cost,
301 prior_cost,
302 new_cost,
303 insertion_flag,
304 variance_amount,
305 user_entered
306 )
307 VALUES(
308 i_txn_id,
309 i_org_id,
310 -1, -- layer_id = -1 for std.
311 1, -- material
312 1, -- this level
313 i_txn_action_id,
314 sysdate,
315 i_user_id,
316 sysdate,
317 i_user_id,
318 i_login_id,
319 i_request_id,
320 i_prog_appl_id,
321 i_prog_id,
322 sysdate,
323 i_inv_item_id,
324 i_txn_cost,
325 NULL,
326 NULL,
327 'N',
328 NULL,
329 'N');
330
331 end if;
332
333 elsif (i_exp_item = 0) then
334
335 l_stmt_num := 60;
336
337 if (i_txn_action_id = 30) then
338 -- WIP scrap for asset item
339
340 INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
341 transaction_id,
342 organization_id,
343 layer_id,
344 cost_element_id,
345 level_type,
346 transaction_action_id,
347 last_update_date,
348 last_updated_by,
349 creation_date,
350 created_by,
351 last_update_login,
352 request_id,
353 program_application_id,
354 program_id,
355 program_update_date,
356 inventory_item_id,
357 actual_cost,
358 prior_cost,
359 new_cost,
360 insertion_flag,
361 variance_amount,
362 user_entered
363 )
364 VALUES(
365 i_txn_id,
366 i_org_id,
367 -1, -- layer_id = -1 for std.
368 1, -- material
369 1, -- this level
370 i_txn_action_id,
371 sysdate,
372 i_user_id,
373 sysdate,
374 i_user_id,
375 i_login_id,
376 i_request_id,
377 i_prog_appl_id,
378 i_prog_id,
379 sysdate,
380 i_inv_item_id,
381 i_actual_cost,
382 NULL,
383 NULL,
384 'N',
385 NULL,
386 'N');
387
388 else
389 -- all other cases for asset items
390 l_stmt_num := 70;
391 SELECT primary_quantity, organization_id, transfer_organization_id
392 INTO l_txn_qty, l_org_id, l_txfr_org_id
393 FROM mtl_material_transactions
394 WHERE transaction_id = i_txn_id;
395
396 /* For intransit shipments, FOB shipment, this function is called twice -
397 once for the sending org and once for the receiving org.
398 For the receiving org we need to only make entries for MOH that gets absorbed. */
399 /* Bug 2695063 to facilitate AX translation needs rows in MCACD for
400 sending org and receiving org. This is applicable for both FOB shipment and
401 FOB receipt. Commented IF condition for bug2695063.
402 IF(i_txn_action_id <> 21 OR l_org_id = i_org_id) THEN */
403
404 l_stmt_num := 73;
405 /* EAM Acct Enh Project */
406 CST_Utility_PUB.get_zeroCostIssue_flag (
407 p_api_version => 1.0,
408 x_return_status => l_return_status,
409 x_msg_count => l_msg_count,
410 x_msg_data => l_msg_data,
411 p_txn_id => i_txn_id,
412 x_zero_cost_flag => l_zero_cost_flag
413 );
414
415 if (l_return_status <> fnd_api.g_ret_sts_success) then
416 FND_FILE.put_line(FND_FILE.log, l_msg_data);
417 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
418 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
419 FND_MESSAGE.set_token('TEXT', l_api_message);
420 FND_MSG_pub.add;
421 raise fnd_api.g_exc_unexpected_error;
422 end if;
423
424 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
425
426 /*if (l_debug = 'Y') then
427 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag:'|| to_char(l_zero_cost_flag));
428 end if;
429
430 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting into MCACD... ');*/
431
432 if (l_zero_cost_flag <> 1) then
433
434 l_stmt_num := 75;
435 INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
436 transaction_id,
437 organization_id,
438 layer_id,
439 cost_element_id,
440 level_type,
441 transaction_action_id,
442 last_update_date,
443 last_updated_by,
444 creation_date,
445 created_by,
446 last_update_login,
447 request_id,
448 program_application_id,
449 program_id,
450 program_update_date,
451 inventory_item_id,
452 actual_cost,
453 prior_cost,
454 new_cost,
455 insertion_flag,
456 variance_amount,
457 user_entered)
458 SELECT
459 i_txn_id,
460 i_org_id,
461 -1, -- layer_id = -1 for std.
462 cost_element_id,
463 level_type,
464 i_txn_action_id,
465 sysdate,
466 i_user_id,
467 sysdate,
468 i_user_id,
469 i_login_id,
470 i_request_id,
471 i_prog_appl_id,
472 i_prog_id,
473 sysdate,
474 i_inv_item_id,
475 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
476 2,ITEM_COST,
477 3,ITEM_COST,
478 4,ITEM_COST,
479 5,ITEM_COST),
480 1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
481 2,ITEM_COST,
482 3,ITEM_COST,
483 4,ITEM_COST,
484 5,ITEM_COST))),
485 NULL,
486 NULL,
487 'N',
488 NULL,
489 'N'
490 FROM CST_ITEM_COST_DETAILS CICD,
491 MTL_PARAMETERS MP
492 WHERE MP.organization_id = i_org_id
493 AND CICD.organization_id = MP.cost_organization_id
494 AND CICD.inventory_item_id = i_inv_item_id
495 AND CICD.cost_type_id = 1
496 GROUP BY CICD.level_type, CICD.cost_element_id;
497
498 else
499
500 l_stmt_num := 77;
501 /* l_zero_cost_flag = 1*/
502 INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
503 transaction_id,
504 organization_id,
505 layer_id,
506 cost_element_id,
507 level_type,
508 transaction_action_id,
509 last_update_date,
510 last_updated_by,
511 creation_date,
512 created_by,
513 last_update_login,
514 request_id,
515 program_application_id,
516 program_id,
517 program_update_date,
518 inventory_item_id,
522 insertion_flag,
519 actual_cost,
520 prior_cost,
521 new_cost,
523 variance_amount,
524 user_entered)
525 SELECT
526 i_txn_id,
527 i_org_id,
528 -1, -- layer_id = -1 for std.
529 cost_element_id,
530 level_type,
531 i_txn_action_id,
532 sysdate,
533 i_user_id,
534 sysdate,
535 i_user_id,
536 i_login_id,
537 i_request_id,
538 i_prog_appl_id,
539 i_prog_id,
540 sysdate,
541 i_inv_item_id,
542 0,
543 NULL,
544 NULL,
545 'N',
546 NULL,
547 'N'
548 FROM CST_ITEM_COST_DETAILS CICD,
549 MTL_PARAMETERS MP
550 WHERE MP.organization_id = i_org_id
551 AND CICD.organization_id = MP.cost_organization_id
552 AND CICD.inventory_item_id = i_inv_item_id
553 AND CICD.cost_type_id = 1
554 GROUP BY CICD.level_type, CICD.cost_element_id;
555
556 end if; /* l_zero_cost_flag <> 1 */
557
558 /* Bug 2695063 to facilitate AX translation needs rows in MCACD for
559 sending org and receiving org. This is applicable for both FOB shipment and
560 FOB receipt. Commented ELSE condition for bug2695063.
561 ELSE
562 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting into MCACD for receiving org... ');
563 l_stmt_num := 76;
564
565 INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
566 transaction_id,
567 organization_id,
568 layer_id,
569 cost_element_id,
570 level_type,
571 transaction_action_id,
572 last_update_date,
573 last_updated_by,
574 creation_date,
575 created_by,
576 last_update_login,
577 request_id,
578 program_application_id,
579 program_id,
580 program_update_date,
581 inventory_item_id,
582 actual_cost,
583 prior_cost,
584 new_cost,
585 insertion_flag,
586 variance_amount,
587 user_entered)
588 SELECT
589 i_txn_id,
590 i_org_id,
591 -1, -- layer_id = -1 for std.
592 cost_element_id,
593 level_type,
594 i_txn_action_id,
595 sysdate,
596 i_user_id,
597 sysdate,
598 i_user_id,
599 i_login_id,
600 i_request_id,
601 i_prog_appl_id,
602 i_prog_id,
603 sysdate,
604 i_inv_item_id,
605 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
606 2,ITEM_COST,
607 3,ITEM_COST,
608 4,ITEM_COST,
609 5,ITEM_COST),
610 1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
611 2,ITEM_COST,
612 3,ITEM_COST,
613 4,ITEM_COST,
614 5,ITEM_COST))),
615 NULL,
616 NULL,
617 'N',
618 NULL,
619 'N'
620 FROM CST_ITEM_COST_DETAILS CICD,
621 MTL_PARAMETERS MP
622 WHERE MP.organization_id = i_org_id
623 AND CICD.organization_id = MP.cost_organization_id
624 AND CICD.inventory_item_id = i_inv_item_id
625 AND CICD.cost_type_id = 1
626 AND CICD.cost_element_id = 2
627 GROUP BY CICD.level_type, CICD.cost_element_id;
628
629 END IF; */
630
631 l_stmt_num := 80;
632
633 -- Modified for fob stamping project
634 if (i_txn_action_id = 21) THEN
635 SELECT nvl(MMT.fob_point, MIP.fob_point)
636 INTO l_fob_point
637 FROM MTL_INTERORG_PARAMETERS MIP, MTL_MATERIAL_TRANSACTIONS MMT
638 WHERE MIP.from_organization_id = l_org_id
639 AND MIP.to_organization_id = l_txfr_org_id
640 AND MMT.transaction_id = i_txn_id;
641
642 elsif (i_txn_action_id = 12) THEN
643 SELECT nvl(MMT.fob_point, MIP.fob_point)
644 INTO l_fob_point
645 FROM MTL_INTERORG_PARAMETERS MIP, MTL_MATERIAL_TRANSACTIONS MMT
646 WHERE MIP.from_organization_id = l_txfr_org_id
647 AND MIP.to_organization_id = l_org_id
648 AND MMT.transaction_id = i_txn_id;
649
650 end if;
651
652 l_stmt_num := 90;
653
654 if ((i_txn_action_id = 31 and i_txn_source_type_id = 5) /* WIP completion */
655 OR
656 (i_txn_action_id = 32 and i_txn_source_type_id = 5) /* Assembly return */
657 OR
658 (i_txn_action_id = 3 and l_txn_qty >0) /* Direct interorg receipt */
659 OR
660 (i_txn_action_id = 21 and l_fob_point = 1 and l_org_id <> i_org_id)
661 OR
662 /* commented following line and added a line below for
663 bug 2695063
664 (i_txn_action_id = 12 and l_fob_point = 2 ) */
665 (i_txn_action_id = 12 and l_fob_point = 2 and l_org_id = i_org_id) /* Intransit Receipt */
666 OR
667 /* OPM INVCONV umoogala */
668 (i_txn_action_id = 15)
669 OR
670 /* OPM INVCONV umoogala */
671 (i_txn_action_id = 22)
672 ) then
673
674 /* Bug6157916 : Check if MOH Absorption rule has been defined
675 and if the MOH absorption rule is not overridden then
676 only insert into MACS
677 */
678 l_earn_moh := 1;
679 cst_mohRules_pub.apply_moh(
680 1.0,
681 p_organization_id => i_org_id,
682 p_earn_moh =>l_earn_moh,
683 p_txn_id => i_txn_id,
684 p_item_id => i_inv_item_id,
685 x_return_status => l_return_status,
686 x_msg_count => l_msg_count,
687 x_msg_data => l_msg_data);
688 IF l_return_status <> FND_API.g_ret_sts_success THEN
689 FND_FILE.put_line(FND_FILE.log, l_msg_data);
690 raise moh_rules_error;
691 END IF;
692 IF(l_earn_moh = 0) THEN
693 IF l_debug = 'Y' THEN
694 fnd_file.put_line(fnd_file.log, '---Material Overhead Absorption Overridden--');
695 END IF;
696 ELSE
697 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting into MACS... ');
698 l_stmt_num := 100;
699
700 INSERT INTO mtl_actual_cost_subelement(
701 transaction_id,
702 organization_id,
703 layer_id,
704 cost_element_id,
705 level_type,
706 resource_id,
707 last_update_date,
708 last_updated_by,
709 creation_date,
710 created_by,
711 last_update_login,
712 request_id,
713 program_application_id,
714 program_id,
715 program_update_date,
716 actual_cost,
717 user_entered)
718 SELECT i_txn_id,
719 i_org_id,
720 -1, -- layer_id = -1 for std.
721 cost_element_id,
722 level_type,
723 resource_id,
724 sysdate,
725 i_user_id,
726 sysdate,
727 i_user_id,
728 i_login_id,
729 i_request_id,
730 i_prog_appl_id,
731 i_prog_id,
732 sysdate,
733 item_cost,
734 'N'
735 FROM CST_ITEM_COST_DETAILS CICD,
736 MTL_PARAMETERS MP
737 WHERE MP.organization_id = i_org_id
738 AND CICD.organization_id = MP.cost_organization_id
739 AND CICD.inventory_item_id = i_inv_item_id
740 AND CICD.cost_type_id = 1
741 AND CICD.level_type = 1
742 AND CICD.cost_element_id = 2;
743 END IF; /*l_earn_moh = 0*/
744 end if;
745 end if;
746 end if;
747
748
749 EXCEPTION
750 when ins_std_cost_error then
751 o_err_num := l_err_num;
752 o_err_code := l_err_code;
753 o_err_msg := 'CSTPSISC.INS_STD_COST:' || l_err_msg;
754 when moh_rules_error THEN
755 o_err_num := 9999;
756 o_err_code := 'CST_RULES_ERROR';
757 FND_MESSAGE.set_name('BOM', 'CST_RULES_ERROR');
758 o_err_msg := 'CSTPSISC.INS_STD_COST:'||FND_MESSAGE.Get;
759 when others then
760 o_err_num := SQLCODE;
761 o_err_msg := 'CSTPSISC.INS_STD_COST (' || to_char(l_stmt_num) || '): '
762 || substrb(SQLERRM,1,150);
763
764 END ins_std_cost;
765
766 END CSTPSISC;
767