[Home] [Help]
PACKAGE BODY: APPS.CSTPACIT
Source
1 package body cstpacit as
2 /* $Header: CSTACITB.pls 120.3.12000000.2 2007/09/17 11:12:04 rgangara ship $ */
3
4 procedure cost_det_validate (
5 i_txn_interface_id in number,
6 i_org_id in number,
7 i_item_id in number,
8 i_new_avg_cost in number,
9 i_per_change in number,
10 i_val_change in number,
11 i_mat_accnt in number,
12 i_mat_ovhd_accnt in number,
13 i_res_accnt in number,
14 i_osp_accnt in number,
15 i_ovhd_accnt in number,
16 o_err_num out NOCOPY number,
17 o_err_code out NOCOPY varchar2,
18 o_err_msg out NOCOPY varchar2
19 )
20 is
21 l_err_num number;
22 l_err_code varchar2(240);
23 l_err_msg varchar2(240);
24
25 l_stmt_num number;
26 l_layer_id number;
27 l_num_detail number;
28 l_inv_asset_flag varchar2(1);
29 l_cost_elmt_id number;
30 cost_det_validate_error EXCEPTION;
31
32 cursor cost_elmt_ids is
33 SELECT COST_ELEMENT_ID
34 FROM MTL_TXN_COST_DET_INTERFACE
35 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
36
37 begin
38 /*
39 ** initialize local variables
40 */
41 l_err_num := 0;
42 l_err_code := '';
43 l_err_msg := '';
44
45
46 /*
47 * If it is an expense item, then exit without any processes.
48 */
49
50 SELECT INVENTORY_ASSET_FLAG
51 INTO l_inv_asset_flag
52 FROM MTL_SYSTEM_ITEMS
53 WHERE INVENTORY_ITEM_ID = i_item_id
54 AND ORGANIZATION_ID = i_org_id;
55
56 if (l_inv_asset_flag = 'N') then
57 -- Error occured
58
59 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_EXP_ITEM');
60 l_err_msg := 'Expense Item.';
61 l_err_msg := FND_MESSAGE.Get;
62
63 raise cost_det_validate_error;
64 end if;
65
66
67 SELECT count(*)
68 INTO l_num_detail
69 FROM MTL_TXN_COST_DET_INTERFACE
70 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
71
72 l_stmt_num := 10;
73
74
75 /* l_num_detail = 0 : No corresponding rows in MTL_TXN_COST_DET_INTERFACE
76 * OR i_txn_interface_id is null.
77 * In this case, skip the validation of row in MTL_TXN_COST_DET_INTERFACE.
78 */
79
80
81 if (l_num_detail = 0) then
82
83 /*
84 * Validate one and only one type of cost change is requested.
85 * 1) change through new_averge_cost - new_avg_cost >= 0.
86 * 2) change through percentage - per_change >= -100.
87 */
88
89 if not (( NVL(i_new_avg_cost, -999) >= 0 AND
90 i_per_change is null AND i_val_change is null) OR
91 ( NVL(i_per_change, -999) >= -100 AND
92 i_new_avg_cost is null AND i_val_change is null) OR
93 (i_val_change is not null AND
94 i_new_avg_cost is null AND i_per_change is null)) then
95 -- Error occured
96
97 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_COST_CHANGE');
98 l_err_msg := 'Invalid cost changes.';
99 l_err_msg := FND_MESSAGE.Get;
100
101 raise cost_det_validate_error;
102 end if;
103
104
105
106 elsif (l_num_detail > 0) then
107 /* Matching row does exist in MTL_TXN_COST_DET_INTERFACE so
108 * validate the values.
109 */
113 SELECT count(*)
110
111 l_stmt_num := 15;
112
114 INTO l_err_num
115 FROM mtl_parameters mp,
116 hr_all_organization_units_tl haout,
117 mtl_txn_cost_det_interface mtcdi
118 WHERE mp.organization_id = i_org_id
119 AND haout.organization_id = mp.organization_id
120 AND haout.LANGUAGE = userenv('LANG')
121 AND mtcdi.transaction_interface_id = i_txn_interface_id
122 AND NVL(mtcdi.organization_id, mp.organization_id) = mp.organization_id
123 AND NVL(mtcdi.organization_code, mp.organization_code) = mp.organization_code
124 AND NVL(mtcdi.organization_name, haout.name) = haout.name
125 AND ( mtcdi.organization_id IS NOT NULL
126 OR mtcdi.organization_code IS NOT NULL
127 OR mtcdi.organization_name IS NOT NULL);
128
129 if (l_err_num <> l_num_detail) then
130 -- Error occured
131 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ORG');
132 l_err_msg := 'Invalid Organization.';
133 l_err_msg := FND_MESSAGE.Get;
134
135 raise cost_det_validate_error;
136 end if;
137
138 l_stmt_num := 20;
139
140 SELECT count(*)
141 INTO l_err_num
142 FROM CST_COST_ELEMENTS CCE,
143 MTL_TXN_COST_DET_INTERFACE MTCDI
144 WHERE MTCDI.TRANSACTION_INTERFACE_ID = i_txn_interface_id
145 AND CCE.COST_ELEMENT_ID = NVL(MTCDI.COST_ELEMENT_ID, CCE.COST_ELEMENT_ID)
146 AND CCE.COST_ELEMENT = NVL(MTCDI.COST_ELEMENT, CCE.COST_ELEMENT)
147 AND (MTCDI.COST_ELEMENT_ID IS NOT NULL OR
148 MTCDI.COST_ELEMENT IS NOT NULL);
149
150 if (l_err_num = 0) then
151 -- Error occured
152
153 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_COST_ELEMENT');
154 l_err_msg := 'Invalid Cost Element.';
155 l_err_msg := FND_MESSAGE.Get;
156
157 raise cost_det_validate_error;
158 end if;
159
160
161
162 l_stmt_num := 30;
163
164 SELECT count(*)
165 INTO l_err_num
166 FROM MFG_LOOKUPS ML,
167 MTL_TXN_COST_DET_INTERFACE MTCDI
168 WHERE MTCDI.TRANSACTION_INTERFACE_ID = i_txn_interface_id
169 AND ML.LOOKUP_TYPE = 'CST_LEVEL'
170 AND ML.LOOKUP_CODE = NVL(MTCDI.LEVEL_TYPE, ML.LOOKUP_CODE)
171 AND ML.MEANING = NVL(MTCDI.LEVEL_NAME, ML.MEANING)
172 AND (MTCDI.LEVEL_TYPE IS NOT NULL OR
173 MTCDI.LEVEL_NAME IS NOT NULL);
174
175 if (l_err_num = 0) then
176 -- Error occured
177
178 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_COST_LEVEL');
179 l_err_msg := 'Invalid Level.';
180 l_err_msg := FND_MESSAGE.Get;
181
182 raise cost_det_validate_error;
183 end if;
184
185
186
187 l_stmt_num := 40;
188
189 SELECT count(*)
190 INTO l_err_num
191 FROM MTL_TXN_COST_DET_INTERFACE
192 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id
193 AND NOT ((NEW_AVERAGE_COST >= 0 AND -- Bug 4759820
194 (PERCENTAGE_CHANGE IS NULL AND VALUE_CHANGE IS NULL)) OR
195 (PERCENTAGE_CHANGE >= -100 AND
196 (NEW_AVERAGE_COST IS NULL AND VALUE_CHANGE IS NULL)) OR
197 (VALUE_CHANGE IS NOT NULL AND
198 (NEW_AVERAGE_COST IS NULL AND PERCENTAGE_CHANGE IS NULL)));
199
200 if (l_err_num <> 0) then -- modified for bug#4759820 from = to <> so that even if 1 error rec
201 -- exists it should through exception.
202 -- Error occured
203
204 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_COST_CHANGE');
205 l_err_msg := 'Invalid Cost changes.';
206 l_err_msg := FND_MESSAGE.Get;
207
208 raise cost_det_validate_error;
209 end if;
210
211
212
213 l_stmt_num := 50;
214
215 UPDATE MTL_TXN_COST_DET_INTERFACE MTCDI
216 SET ORGANIZATION_ID = i_org_id
217 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id
218 AND ORGANIZATION_ID IS NULL;
219
220
221 UPDATE MTL_TXN_COST_DET_INTERFACE MTCDI
222 SET COST_ELEMENT_ID =
223 (SELECT CCE.COST_ELEMENT_ID
224 FROM CST_COST_ELEMENTS CCE
225 WHERE CCE.COST_ELEMENT = NVL(MTCDI.COST_ELEMENT, CCE.COST_ELEMENT))
226 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id
227 AND COST_ELEMENT_ID IS NULL;
228
229
230 UPDATE MTL_TXN_COST_DET_INTERFACE MTCDI
231 SET LEVEL_TYPE =
232 (SELECT ML.LOOKUP_CODE
233 FROM MFG_LOOKUPS ML
234 WHERE ML.LOOKUP_TYPE = 'CST_LEVEL'
235 AND ML.MEANING = NVL(MTCDI.LEVEL_NAME, ML.MEANING))
236 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id
237 AND LEVEL_TYPE IS NULL;
238
239
240
241 l_stmt_num := 60;
242
243 SELECT count(*)
244 INTO l_err_num
245 FROM MTL_TXN_COST_DET_INTERFACE
246 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id
247 AND ORGANIZATION_ID = i_org_id;
248
249 if (l_err_num = 0) then
250 -- Error occured
251
252 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ORG');
253 l_err_msg := 'Organization_id does not match.';
254 l_err_msg := FND_MESSAGE.Get;
255
256 raise cost_det_validate_error;
257 end if;
258
259
260 l_stmt_num := 70;
261
262
263 open cost_elmt_ids;
264
265 loop
266 fetch cost_elmt_ids into l_cost_elmt_id;
267 exit when cost_elmt_ids%NOTFOUND;
268
269 if ((l_cost_elmt_id = 1 and i_mat_accnt is null) or
273 (l_cost_elmt_id = 5 and i_ovhd_accnt is null)) then
270 (l_cost_elmt_id = 2 and i_mat_ovhd_accnt is null) or
271 (l_cost_elmt_id = 3 and i_res_accnt is null) or
272 (l_cost_elmt_id = 4 and i_osp_accnt is null) or
274 -- Error occured
275
276 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
277 l_err_code := 'Invalid accounts.';
278 l_err_msg := FND_MESSAGE.Get;
279 l_err_num := 999;
280
281 raise cost_det_validate_error;
282 end if;
283
284 end loop;
285
286
287 end if; -- END if for detail rows exist in MTL_TXN_COST_DET_INTERFACE
288
289
290 EXCEPTION
291 when cost_det_validate_error then
292 o_err_num := l_err_num;
293 o_err_code := l_err_code;
294 o_err_msg := 'CSTPACIT.COST_DET_VALIDATE:' || l_err_msg;
295 when OTHERS then
296 o_err_num := SQLCODE;
297 o_err_msg := 'CSTPACIT.COST_DET_VALIDATE: (' || to_char(l_stmt_num) || '): '
298 || substr(SQLERRM,1,150);
299
300 end cost_det_validate;
301
302
303
304
305 procedure cost_det_move (
306 i_txn_id in number,
307 i_txn_interface_id in number,
308 i_txn_action_id in number,
309 i_org_id in number,
310 i_item_id in number,
311 i_cost_group_id in number,
312 i_txn_cost in number,
313 i_new_avg_cost in number,
314 i_per_change in number,
315 i_val_change in number,
316 i_mat_accnt in number,
317 i_mat_ovhd_accnt in number,
318 i_res_accnt in number,
319 i_osp_accnt in number,
320 i_ovhd_accnt in number,
321 i_user_id in number,
322 i_login_id in number,
323 i_request_id in number,
324 i_prog_appl_id in number,
325 i_prog_id in number,
326 o_err_num out NOCOPY number,
327 o_err_code out NOCOPY varchar2,
328 o_err_msg out NOCOPY varchar2
329 )
330 is
331 l_err_num number;
332 l_err_code varchar2(240);
333 l_err_msg varchar2(240);
334 l_num_detail number;
335 l_layer_id number;
336 cost_det_move_error EXCEPTION;
337 cost_no_layer_error EXCEPTION;
338
339 begin
340 /*
341 ** initialize local variables
342 */
343 l_err_num := 0;
344 l_err_code := '';
345 l_err_msg := '';
346
347 o_err_num := 0;
348 o_err_code := '';
349 o_err_msg := '';
350
351 SELECT count(*)
352 INTO l_num_detail
353 FROM MTL_TXN_COST_DET_INTERFACE
354 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
355
356 /* l_num_detail = 0 : No corresponding rows in MTL_TXN_COST_DET_INTERFACE
357 * OR i_txn_interface_id is null.
358 * In this case, call cstpacit.cost_det_new_insert.
359 */
360
361 if (l_num_detail = 0) then
362 cstpacit.cost_det_new_insert(i_txn_id, i_txn_action_id, i_org_id,
363 i_item_id, i_cost_group_id, i_txn_cost,
364 i_new_avg_cost, i_per_change, i_val_change,
365 i_mat_accnt, i_mat_ovhd_accnt, i_res_accnt,
366 i_osp_accnt, i_ovhd_accnt,
367 i_user_id, i_login_id, i_request_id,
368 i_prog_appl_id, i_prog_id,
369 l_err_num, l_err_code, l_err_msg);
370 if (l_err_num <> 0) then
371 raise cost_det_move_error;
372 end if;
373
374 else
375
376 l_layer_id := cstpaclm.layer_det_exist(i_org_id, i_item_id, i_cost_group_id,
377 l_err_num, l_err_code, l_err_msg);
378
379 if (l_err_num <> 0) then
380 raise cost_no_layer_error;
381 end if;
382
383 if (l_layer_id <> 0) then
384
385 INSERT INTO MTL_CST_TXN_COST_DETAILS (
386 TRANSACTION_ID,
387 ORGANIZATION_ID,
388 INVENTORY_ITEM_ID,
389 COST_ELEMENT_ID,
390 LEVEL_TYPE,
391 TRANSACTION_COST,
392 NEW_AVERAGE_COST,
393 PERCENTAGE_CHANGE,
394 VALUE_CHANGE,
395 LAST_UPDATE_DATE,
396 LAST_UPDATED_BY,
397 CREATION_DATE,
398 CREATED_BY,
399 LAST_UPDATE_LOGIN,
400 REQUEST_ID,
401 PROGRAM_APPLICATION_ID,
402 PROGRAM_ID,
403 PROGRAM_UPDATE_DATE
404 )
405 SELECT
406 i_txn_id,
407 i_org_id,
408 i_item_id,
409 COST_ELEMENT_ID,
410 LEVEL_TYPE,
411 ITEM_COST,
412
413 NULL, /*changed from item_cost to NULL for bug 6404902 as for CL/LE not in
414 MTCDI, new avg cost should be taken as NULL*/
415
416 NULL,
417 0, /* changed from NULL to o for but 6404902 so that for CL/LE combination
418 not in MTCDI, it would not have any effect*/
419 sysdate,
420 i_user_id,
421 sysdate,
422 i_user_id,
423 i_login_id,
424 i_request_id,
425 i_prog_appl_id,
426 i_prog_id,
427 sysdate
428 FROM CST_LAYER_COST_DETAILS CLCD
429 WHERE CLCD.LAYER_ID = l_layer_id;
430
431 UPDATE MTL_CST_TXN_COST_DETAILS mctcd
432 set (VALUE_CHANGE,
433 PERCENTAGE_CHANGE,
434 NEW_AVERAGE_COST)
435 =
436 (select
437 mtcdi.VALUE_CHANGE,
438 mtcdi.PERCENTAGE_CHANGE,
439 mtcdi.NEW_AVERAGE_COST
440 from MTL_TXN_COST_DET_INTERFACE mtcdi
441 where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
442 and mctcd.transaction_id = i_txn_id
443 and mtcdi.level_type = mctcd.level_type
447 mctcd.transaction_id = i_txn_id
444 and mtcdi.cost_element_id = mctcd.cost_element_id
445 )
446 where
448 and exists (select 1
449 from MTL_TXN_COST_DET_INTERFACE mtcdi
450 where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
451 and mtcdi.level_type = mctcd.level_type
452 and mtcdi.cost_element_id = mctcd.cost_element_id);
453 /*Added for Bug#2509196*/
454
455 INSERT INTO MTL_CST_TXN_COST_DETAILS (
456 TRANSACTION_ID,
457 ORGANIZATION_ID,
458 INVENTORY_ITEM_ID,
459 COST_ELEMENT_ID,
460 LEVEL_TYPE,
461 TRANSACTION_COST,
462 NEW_AVERAGE_COST,
463 PERCENTAGE_CHANGE,
464 VALUE_CHANGE,
465 LAST_UPDATE_DATE,
466 LAST_UPDATED_BY,
467 CREATION_DATE,
468 CREATED_BY,
469 LAST_UPDATE_LOGIN,
470 REQUEST_ID,
471 PROGRAM_APPLICATION_ID,
472 PROGRAM_ID,
473 PROGRAM_UPDATE_DATE
474 )
475 SELECT
476 i_txn_id,
477 i_org_id,
478 i_item_id,
479 mtcdi.COST_ELEMENT_ID,
480 mtcdi.LEVEL_TYPE,
481 mtcdi.TRANSACTION_COST,
482 mtcdi.NEW_AVERAGE_COST,
483 mtcdi.PERCENTAGE_CHANGE,
484 mtcdi.VALUE_CHANGE,
485 sysdate,
486 i_user_id,
487 sysdate,
488 i_user_id,
489 i_login_id,
490 i_request_id,
491 i_prog_appl_id,
492 i_prog_id,
493 sysdate
494 FROM MTL_TXN_COST_DET_INTERFACE MTCDI
495 WHERE mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
496 and ( (MTCDI.cost_element_id, MTCDI.level_type) not in
497 (select mctcd1.cost_element_id,mctcd1.level_type
498 from mtl_cst_txn_cost_details mctcd1
499 where mctcd1.transaction_id=i_txn_id
500 )
501 );
502 /*End of Addition for Bug#2509196*/
503 else
504 /* Changed the following for Bug#2509196 as follows so as to
505 take the elemental details from mtcdi*/
506 /* No layer exists , hence use THIS level MATERIAL row */
507
508 /*INSERT INTO MTL_CST_TXN_COST_DETAILS (
509 TRANSACTION_ID,
510 ORGANIZATION_ID,
511 INVENTORY_ITEM_ID,
512 COST_ELEMENT_ID,
513 LEVEL_TYPE,
514 TRANSACTION_COST,
515 NEW_AVERAGE_COST,
516 PERCENTAGE_CHANGE,
517 VALUE_CHANGE,
518 LAST_UPDATE_DATE,
519 LAST_UPDATED_BY,
520 CREATION_DATE,
521 CREATED_BY,
522 LAST_UPDATE_LOGIN,
523 REQUEST_ID,
524 PROGRAM_APPLICATION_ID,
525 PROGRAM_ID,
526 PROGRAM_UPDATE_DATE
527 )
528 values (
529 i_txn_id,
530 i_org_id,
531 i_item_id,
532 1, -- Hard coded to This level Material
533 1,
534 i_txn_cost,
535 i_new_avg_cost,
536 i_per_change,
537 i_val_change,
538 sysdate,
539 i_user_id,
540 sysdate,
541 i_user_id,
542 i_login_id,
543 i_request_id,
544 i_prog_appl_id,
545 i_prog_id,
546 sysdate);*/
547 INSERT INTO MTL_CST_TXN_COST_DETAILS (
548 TRANSACTION_ID,
549 ORGANIZATION_ID,
550 INVENTORY_ITEM_ID,
551 COST_ELEMENT_ID,
552 LEVEL_TYPE,
553 TRANSACTION_COST,
554 NEW_AVERAGE_COST,
555 PERCENTAGE_CHANGE,
556 VALUE_CHANGE,
557 LAST_UPDATE_DATE,
558 LAST_UPDATED_BY,
559 CREATION_DATE,
560 CREATED_BY,
561 LAST_UPDATE_LOGIN,
562 REQUEST_ID,
563 PROGRAM_APPLICATION_ID,
564 PROGRAM_ID,
565 PROGRAM_UPDATE_DATE
566 )
567 SELECT
568 i_txn_id,
569 i_org_id,
570 i_item_id,
571 mtcdi.COST_ELEMENT_ID,
572 mtcdi.LEVEL_TYPE,
573 mtcdi.TRANSACTION_COST,
574 mtcdi.NEW_AVERAGE_COST,
575 mtcdi.PERCENTAGE_CHANGE,
576 mtcdi.VALUE_CHANGE,
577 sysdate,
578 i_user_id,
579 sysdate,
580 i_user_id,
581 i_login_id,
582 i_request_id,
583 i_prog_appl_id,
584 i_prog_id,
585 sysdate
586 FROM MTL_TXN_COST_DET_INTERFACE MTCDI
587 WHERE mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id;
588
589 end if; /* if layer exists */
590
591 end if; /* if l_num_detail = 0 */
592
593 EXCEPTION
594 when cost_det_move_error then
595 o_err_num := l_err_num;
596 o_err_code := l_err_code;
597 o_err_msg := 'CSTPACIT.COST_DET_MOVE:' || l_err_msg;
598 when cost_no_layer_error then
599 o_err_num := l_err_num;
600 o_err_code := l_err_code;
601 o_err_msg := 'CSTPACIT.COST_DET_MOVE: No layer exists' || l_err_msg;
602 when OTHERS then
603 o_err_num := SQLCODE;
604 o_err_msg := 'CSTPACIT.COST_DET_MOVE:' || substr(SQLERRM,1,150);
605
606 end cost_det_move;
607
608
609
610 procedure cost_det_new_insert (
611 i_txn_id in number,
612 i_txn_action_id in number,
613 i_org_id in number,
614 i_item_id in number,
615 i_cost_group_id in number,
616 i_txn_cost in number,
617 i_new_avg_cost in number,
618 i_per_change in number,
619 i_val_change in number,
620 i_mat_accnt in number,
624 i_ovhd_accnt in number,
621 i_mat_ovhd_accnt in number,
622 i_res_accnt in number,
623 i_osp_accnt in number,
625 i_user_id in number,
626 i_login_id in number,
627 i_request_id in number,
628 i_prog_appl_id in number,
629 i_prog_id in number,
630 o_err_num out NOCOPY number,
631 o_err_code out NOCOPY varchar2,
632 o_err_msg out NOCOPY varchar2
633 )
634 is
635 l_err_num number;
636 l_err_code varchar2(240);
637 l_err_msg varchar2(240);
638
639 cl_item_cost number;
640 cost_element_count number;
641
642 l_cost_elmt_id number;
643 l_layer_id number;
644 cost_det_new_insert_error EXCEPTION;
645
646
647 cursor cost_elmt_ids is
648 SELECT CLCD.COST_ELEMENT_ID
649 FROM CST_QUANTITY_LAYERS CL,
650 CST_LAYER_COST_DETAILS CLCD
651 WHERE CL.LAYER_ID = l_layer_id
652 AND CLCD.LAYER_ID = l_layer_id;
653
654
655 begin
656 /*
657 ** initialize local variables
658 */
659 l_err_num := 0;
660 l_err_code := '';
661 l_err_msg := '';
662
663
664 l_layer_id := cstpaclm.layer_det_exist(i_org_id, i_item_id, i_cost_group_id,
665 l_err_num, l_err_code, l_err_msg);
666
667 if (l_err_num <> 0) then
668 raise cost_det_new_insert_error;
669 end if;
670
671 /* If layer detail exist, then calculate proportional costs and
672 * insert each elements into MTL_CST_TXN_COST_DETAILS.
673 */
674
675 if (l_layer_id <> 0) then
676
677 if (i_txn_action_id = 24) then
678 -- checking the existence of accounts for average cost update case
679 open cost_elmt_ids;
680
681 loop
682 fetch cost_elmt_ids into l_cost_elmt_id;
683 exit when cost_elmt_ids%NOTFOUND;
684
685 if ((l_cost_elmt_id = 1 and i_mat_accnt is null) or
686 (l_cost_elmt_id = 2 and i_mat_ovhd_accnt is null) or
687 (l_cost_elmt_id = 3 and i_res_accnt is null) or
688 (l_cost_elmt_id = 4 and i_osp_accnt is null) or
689 (l_cost_elmt_id = 5 and i_ovhd_accnt is null)) then
690 -- Error occured
691
692 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
693 l_err_code := 'Invalid accounts.';
694 l_err_msg := FND_MESSAGE.Get;
695 l_err_num := 999;
696
697 raise cost_det_new_insert_error;
698 end if;
699
700 end loop;
701 end if;
702
703 SELECT ITEM_COST
704 INTO cl_item_cost
705 FROM CST_QUANTITY_LAYERS
706 WHERE LAYER_ID = l_layer_id;
707
708 /* for the case of item cost equal zero */
709 /* split cost evenly among cost elements */
710
711 if (cl_item_cost = 0) then
712 SELECT count(COST_ELEMENT_ID)
713 INTO cost_element_count
714 FROM CST_LAYER_COST_DETAILS
715 WHERE LAYER_ID = l_layer_id;
716 end if;
717
718 INSERT INTO MTL_CST_TXN_COST_DETAILS (
719 TRANSACTION_ID,
720 ORGANIZATION_ID,
721 INVENTORY_ITEM_ID,
722 COST_ELEMENT_ID,
723 LEVEL_TYPE,
724 TRANSACTION_COST,
725 NEW_AVERAGE_COST,
726 PERCENTAGE_CHANGE,
727 VALUE_CHANGE,
728 LAST_UPDATE_DATE,
729 LAST_UPDATED_BY,
730 CREATION_DATE,
731 CREATED_BY,
732 LAST_UPDATE_LOGIN,
733 REQUEST_ID,
734 PROGRAM_APPLICATION_ID,
735 PROGRAM_ID,
736 PROGRAM_UPDATE_DATE
737 )
738 SELECT
739 i_txn_id,
740 i_org_id,
741 i_item_id,
742 CLCD.COST_ELEMENT_ID,
743 CLCD.LEVEL_TYPE,
744 DECODE(CL.ITEM_COST, 0, i_txn_cost / cost_element_count,
745 i_txn_cost * CLCD.ITEM_COST / CL.ITEM_COST),
746 DECODE(CL.ITEM_COST, 0, i_new_avg_cost / cost_element_count,
747 i_new_avg_cost * CLCD.ITEM_COST / CL.ITEM_COST),
748 i_per_change,
749 DECODE(CL.ITEM_COST, 0, i_val_change / cost_element_count,
750 i_val_change * CLCD.ITEM_COST / CL.ITEM_COST),
751 sysdate,
752 i_user_id,
753 sysdate,
754 i_user_id,
755 i_login_id,
756 i_request_id,
757 i_prog_appl_id,
758 i_prog_id,
759 sysdate
760 FROM CST_QUANTITY_LAYERS CL, CST_LAYER_COST_DETAILS CLCD
761 WHERE CL.LAYER_ID = l_layer_id
762 AND CLCD.LAYER_ID = l_layer_id;
763
764 /* If layer detail does not exist, then insert a new row
765 * as a this level material.
766 */
767 else
768
769 if (i_txn_action_id = 24 and i_mat_accnt is null) then
770 -- Error occured only for average cost update
771
772 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
773 l_err_code := 'Invalid accounts.';
774 l_err_msg := FND_MESSAGE.Get;
775 l_err_num := 999;
776
777 raise cost_det_new_insert_error;
778 end if;
779
780
781 INSERT INTO MTL_CST_TXN_COST_DETAILS (
782 TRANSACTION_ID,
783 ORGANIZATION_ID,
784 INVENTORY_ITEM_ID,
785 COST_ELEMENT_ID,
786 LEVEL_TYPE,
787 TRANSACTION_COST,
788 NEW_AVERAGE_COST,
792 LAST_UPDATED_BY,
789 PERCENTAGE_CHANGE,
790 VALUE_CHANGE,
791 LAST_UPDATE_DATE,
793 CREATION_DATE,
794 CREATED_BY,
795 LAST_UPDATE_LOGIN,
796 REQUEST_ID,
797 PROGRAM_APPLICATION_ID,
798 PROGRAM_ID,
799 PROGRAM_UPDATE_DATE
800 )
801 values (
802 i_txn_id,
803 i_org_id,
804 i_item_id,
805 1, /* Hard coded to This level Material */
806 1,
807 i_txn_cost,
808 i_new_avg_cost,
809 i_per_change,
810 i_val_change,
811 sysdate,
812 i_user_id,
813 sysdate,
814 i_user_id,
815 i_login_id,
816 i_request_id,
817 i_prog_appl_id,
818 i_prog_id,
819 sysdate);
820
821 end if;
822
823 EXCEPTION
824 when cost_det_new_insert_error then
825 o_err_num := l_err_num;
826 o_err_code := l_err_code;
827 o_err_msg := 'CSTPACIT.COST_DET_NEW_INSERT:' || l_err_msg;
828 when OTHERS then
829 o_err_num := SQLCODE;
830 o_err_msg := 'CSTPACIT.COST_DET_NEW_INSERT:' || substr(SQLERRM,1,150);
831
832 end cost_det_new_insert;
833
834 end cstpacit;