[Home] [Help]
PACKAGE BODY: APPS.CSTPLENG
Source
1 PACKAGE BODY CSTPLENG AS
2 /* $Header: CSTLENGB.pls 120.22.12020000.3 2013/01/11 05:04:12 yuyun ship $ */
3
4 ----------------------------------------------------------------------------
5 -- PROCEDURE --
6 -- compute_layer_actual_cost --
7 -- --
8 -- DESCRIPTION --
9 -- This procedure is analogous to the compute_layer_actual_cost( ) in --
10 -- average costing. It is called by the cost processor for each --
11 -- transaction, so that MCLACD can be populated, and --
12 -- FIFO/LIFO layers consumed or created as necessary --
13 -- --
14 -- PURPOSE: --
15 -- FIFO/LIFO layer cost processing for Oracle Applications Rel 11i.2 --
16 -- --
17 -- PARAMETERS: --
18 -- i_org_id : worker organization ID --
19 -- i_layer_id : layer ID from CQL --
20 -- (for organization, item, cost group) --
21 -- i_cost_method : FIFO or LIFO cost method --
22 -- i_cost_hook : presence of actual cost hook --
23 -- --
24 -- HISTORY: --
25 -- 04/20/00 Anitha B Created --
26 ----------------------------------------------------------------------------
27 FUNCTION compute_layer_actual_cost(
28 i_org_id IN NUMBER,
29 i_cost_method IN NUMBER,
30 i_txn_id IN NUMBER,
31 i_layer_id IN NUMBER,
32 i_cost_hook IN NUMBER,
33 i_cost_type IN NUMBER,
34 i_mat_ct_id IN NUMBER,
35 i_avg_rates_id IN NUMBER,
36 i_item_id IN NUMBER,
37 i_txn_qty IN NUMBER,
38 i_txn_action_id IN NUMBER,
39 i_txn_src_type IN NUMBER,
40 i_interorg_rec IN NUMBER,
41 i_exp_flag IN NUMBER,
42 i_user_id IN NUMBER,
43 i_login_id IN NUMBER,
44 i_req_id IN NUMBER,
45 i_prg_appl_id IN NUMBER,
46 i_prg_id IN NUMBER,
47 o_err_num OUT NOCOPY NUMBER,
48 o_err_code OUT NOCOPY VARCHAR2,
49 o_err_msg OUT NOCOPY VARCHAR2
50 )
51 return integer IS
52 l_cost_hook NUMBER;
53 l_txn_cost_exists NUMBER;
54 l_ret_val NUMBER;
55 l_err_num NUMBER;
56 l_err_code VARCHAR2(240);
57 l_err_msg VARCHAR2(240);
58 l_stmt_num NUMBER;
59 process_error EXCEPTION;
60 rows_not_found EXCEPTION;
61 l_debug VARCHAR2(80);
62
63 BEGIN
64 /* initialize variables */
65 l_stmt_num := 0;
66 l_err_num := 0;
67 l_err_code := '';
68 l_err_msg := '';
69 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
70
71 if (l_debug = 'Y') then
72 FND_FILE.PUT_LINE(FND_FILE.LOG,'Compute layer actual cost ...');
73 FND_FILE.PUT_LINE(FND_FILE.LOG,'layer ID : ' || to_char(i_layer_id));
74 FND_FILE.PUT_LINE(FND_FILE.LOG,'cost hook : ' || to_char(i_cost_hook));
75 end if;
76
77
78 /* For WIP component issue and negative component return,layers have already
79 been consumed. WIP calls consume_create_layers, to consume the inventory
80 layers, populate MCLACD and MCACD, depending on cost hook existence
81 or not
82 */
83 l_stmt_num := 10;
84 if (i_txn_src_type = 5 and (i_txn_action_id IN (1,34))) then
85 l_ret_val := 1;
86 return l_ret_val;
87 end if;
88
89 /* If cost hook is used, then ensure that rows are present in MCACD */
90 l_stmt_num := 20;
91 if (i_cost_hook = 1) then
92 select count(*)
93 into l_txn_cost_exists
94 from mtl_cst_actual_cost_details
95 where transaction_id = i_txn_id
96 and organization_id = i_org_id
97 and actual_cost >= 0;
98
99 /* Raise error if no cost hook data found in MCACD */
100 l_stmt_num := 30;
101 if (l_txn_cost_exists = 0) then
102 raise rows_not_found;
103 end if;
104 end if;
105
106 /* Call layer engine for creating or consuming inventory layers */
107 l_stmt_num := 40;
108 consume_create_layers(i_org_id,
109 i_txn_id,
110 i_layer_id,
111 i_cost_hook,
112 i_item_id,
113 i_txn_qty,
114 i_cost_method,
115 i_txn_src_type,
116 i_txn_action_id,
117 i_interorg_rec,
118 i_cost_type,
119 i_mat_ct_id,
120 i_avg_rates_id,
121 i_exp_flag,
122 i_user_id,
123 i_login_id,
124 i_req_id,
125 i_prg_appl_id,
126 i_prg_id,
127 l_err_num,
128 l_err_code,
129 l_err_msg);
130
131 if (l_err_num <> 0) then
132 raise process_error;
133 end if;
134
135 l_ret_val := 1;
136 return l_ret_val;
137
138 EXCEPTION
139 when process_error then
140 o_err_num := l_err_num;
141 o_err_code := l_err_code;
142 o_err_msg := l_err_msg;
143 l_ret_val := 0;
144 return l_ret_val;
145 when rows_not_found then
146 o_err_num := l_err_num;
147 o_err_code := 'CST_NO_COST_HOOK_DATA';
148 FND_MESSAGE.set_name('BOM','CST_NO_COST_HOOK_DATA');
149 o_err_msg := FND_MESSAGE.get;
150 l_ret_val := 0;
151 return l_ret_val;
152 when others then
153 rollback;
154 o_err_num := SQLCODE;
155 o_err_msg := 'CSTPLENG.compute_layer_actual_cost (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
156 l_ret_val := 0;
157 return l_ret_val;
158 END compute_layer_actual_cost;
159
160 ----------------------------------------------------------------------------
161 -- PROCEDURE --
162 -- consume_create_layers --
163 -- --
164 -- DESCRIPTION --
165 -- This procedure determines whether to create or consume inventory --
166 -- layers depending on the transaction action and primary quantity --
167 -- --
168 -- PURPOSE: --
169 -- * Differentiate consumption and receipt transactions --
170 -- * For scrap transactions, it merely populates MCACD, since no --
171 -- inventory layers are involved --
172 -- * If expense flag is 1, then pick up current cost from CQL (similar --
173 -- to average costing). Only MCACD is populated since no inventory --
174 -- layers are involved. --
175 -- --
176 -- PARAMETERS: --
177 -- i_org_id : organization ID --
178 -- i_layer_id : layer ID from CQL --
179 -- (for organization, item, cost group) --
180 -- i_txn_action_id : Transaction action ID --
181 -- i_txn_qty : primary quantity --
182 -- i_exp_flag : Expense flag for item/subinventory --
183 -- --
184 -- HISTORY: --
185 -- 04/20/00 Anitha B Created --
186 ----------------------------------------------------------------------------
187 PROCEDURE consume_create_layers(
188 i_org_id IN NUMBER,
189 i_txn_id IN NUMBER,
190 i_layer_id IN NUMBER,
191 i_cost_hook IN NUMBER,
192 i_item_id IN NUMBER,
193 i_txn_qty IN NUMBER,
194 i_cost_method IN NUMBER,
195 i_txn_src_type IN NUMBER,
196 i_txn_action_id IN NUMBER,
197 i_interorg_rec IN NUMBER,
198 i_cost_type IN NUMBER,
199 i_mat_ct_id IN NUMBER,
200 i_avg_rates_id IN NUMBER,
201 i_exp_flag IN NUMBER,
202 i_user_id IN NUMBER,
203 i_login_id IN NUMBER,
204 i_req_id IN NUMBER,
205 i_prg_appl_id IN NUMBER,
206 i_prg_id IN NUMBER,
207 o_err_num OUT NOCOPY NUMBER,
208 o_err_code OUT NOCOPY VARCHAR2,
209 o_err_msg OUT NOCOPY VARCHAR2
210 ) IS
211 l_stmt_num NUMBER;
212 l_err_num NUMBER;
213 l_err_code VARCHAR2(240);
214 l_err_msg VARCHAR2(240);
215 process_error EXCEPTION;
216 l_debug VARCHAR2(80);
217
218 Begin
219 /* Initialize variables */
220 l_stmt_num := 0;
221 l_err_num := 0;
222 l_err_code := '';
223 l_err_msg := '';
224 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
225
226 if (l_debug = 'Y') then
227 FND_FILE.PUT_LINE(FND_FILE.LOG,'Consume/Create Layers...');
228 end if;
229
230 if ((i_txn_action_id = 1) /* Issue from stores */
231 OR (i_interorg_rec = 0) /* interorg shipment */
232 OR ((i_txn_action_id = 21) and (i_txn_qty < 0))
233 OR ((i_txn_action_id = 12) and (i_txn_qty < 0))
234 OR ((i_txn_action_id = 2) and (i_txn_qty < 0)) /* Subinv send Org */
235 OR ((i_txn_action_id = 5) and (i_txn_qty < 0)) /* VMI Planning Transfer send Org */
236 OR ((i_txn_action_id = 28) and (i_txn_qty < 0)) /* Staging transfer */
237 OR ((i_txn_action_id = 55) and (i_txn_qty < 0)) /* Cost Group transfer for WMS*/
238 OR ((i_txn_action_id = 29) and (i_txn_qty < 0)) /* negative delivery adj*/
239 OR ((i_txn_action_id = 4 OR i_txn_action_id = 8) and (i_txn_qty < 0)) /*physical inv, cycle count -ve adj*/
240 OR (i_txn_action_id = 32) /* assembly return */
241 OR (i_txn_action_id = 34) /* negative component return */
242 OR (i_txn_action_id = 6 and i_txn_src_type = 13) /* Reverse change of
243 ownership */
244 OR ( i_txn_action_id = 9 and i_txn_qty < 0 ) /* Logical IC Sales Issue */
245 /* Logical IC Sales Issues consume layers only when they are
246 in the organization of the Physical Issue
247 This function is called only in that case, hence that check is
248 redundant */
249 ) then
250 l_stmt_num := 10;
251 if (l_debug = 'Y') then
252 FND_FILE.PUT_LINE(FND_FILE.LOG,'Consumption Transaction ...');
253 FND_FILE.PUT_LINE(FND_FILE.LOG,'Action ID : ' || to_char(i_txn_action_id));
254 FND_FILE.PUT_LINE(FND_FILE.LOG,'Primary Qty : ' || to_char(i_txn_qty));
255 end if;
256 consume_layers(
257 i_org_id,
258 i_txn_id,
259 i_layer_id,
260 i_item_id,
261 i_txn_qty,
262 i_cost_method,
263 i_txn_src_type,
264 i_txn_action_id,
265 i_cost_hook,
266 i_interorg_rec,
267 i_cost_type,
268 i_mat_ct_id,
269 i_avg_rates_id,
270 i_exp_flag,
271 i_user_id,
272 i_login_id,
273 i_req_id,
274 i_prg_appl_id,
275 i_prg_id,
276 l_err_num,
277 l_err_code,
278 l_err_msg
279 );
280
281 if (l_err_num <> 0) then
282 raise process_error;
283 end if;
284
285 elsif ((i_interorg_rec = 1) /* interorg receipt */
286 OR ((i_txn_action_id = 12) and (i_txn_qty > 0))
287 OR ((i_txn_action_id = 21) and (i_txn_qty > 0))
288 OR ((i_txn_action_id = 2) and (i_txn_qty > 0)) /* sub transfer rcv org */
289 OR ((i_txn_action_id = 5) and (i_txn_qty > 0)) /* VMI planning transfer rcv org */
290 OR ((i_txn_action_id = 28) and (i_txn_qty > 0)) /* Staging transfer */
291 OR ((i_txn_action_id = 55) and (i_txn_qty > 0)) /* Cost Group transfer */
292 OR (i_txn_action_id = 27) /* receipt into stores */
293 OR ((i_txn_action_id = 29) and (i_txn_qty > 0)) /* positive delivery adjustment */
294 OR ((i_txn_action_id = 4 OR i_txn_action_id = 8) and (i_txn_qty > 0)) /* +ve cycle count or physical inv adjustment */
295 OR (i_txn_action_id = 31) /* assembly completion */
296 OR (i_txn_action_id = 33) /* negative component issue */
297 OR (i_txn_action_id = 6 and i_txn_src_type = 1) /* Change of ownership
298 */
299 --{BUG#6902140
300 OR ( i_txn_action_id = 14 and i_txn_qty > 0 ) /*logical IC Sales Return*/
301 --}
302 /*Bug 7381166*/
303 OR ( i_txn_action_id = 26 and i_txn_qty > 0 ) /*logical Receipt*/
304 ) then
305 l_stmt_num := 20;
306 if (l_debug = 'Y') then
307 FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction creates inventory layers ...');
308 FND_FILE.PUT_LINE(FND_FILE.LOG,'Action ID : ' || to_char(i_txn_action_id));
309 FND_FILE.PUT_LINE(FND_FILE.LOG,'Primary Qty : ' || to_char(i_txn_qty));
310 end if;
311 create_layers(
312 i_org_id,
313 i_txn_id,
314 i_layer_id,
315 i_item_id,
316 i_txn_qty,
317 i_cost_method,
318 i_txn_src_type,
319 i_txn_action_id,
320 i_cost_hook,
321 i_interorg_rec,
322 i_cost_type,
323 i_mat_ct_id,
324 i_avg_rates_id,
325 i_exp_flag,
326 i_user_id,
327 i_login_id,
328 i_req_id,
329 i_prg_appl_id,
330 i_prg_id,
331 l_err_num,
332 l_err_code,
333 l_err_msg
334 );
335
336 if (l_err_num <> 0) then
337 raise process_error;
338 end if;
339
340 elsif (i_txn_action_id = 30) then
341 /* scrap transaction */
342 l_stmt_num := 30;
343 if (i_cost_hook = 1) then
344 if (l_debug = 'Y') then
345 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scrap transaction with cost hook');
346 end if;
347 return;
348 else
349 if (l_debug = 'Y') then
350 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scrap transaction inserts into MCACD');
351 end if;
352 insert into mtl_cst_actual_cost_details (
353 transaction_id,
354 organization_id,
355 layer_id,
356 cost_element_id,
357 level_type,
358 transaction_action_id,
359 last_update_date,
360 last_updated_by,
361 creation_date,
362 created_by,
363 last_update_login,
364 request_id,
365 program_application_id,
366 program_id,
367 program_update_date,
368 inventory_item_id,
369 actual_cost,
370 prior_cost,
371 new_cost,
372 insertion_flag,
373 variance_amount,
374 user_entered)
375 select
376 i_txn_id,
377 i_org_id,
378 i_layer_id,
379 mctcd.cost_element_id,
380 mctcd.level_type,
381 i_txn_action_id,
382 sysdate,
383 i_user_id,
384 sysdate,
385 i_user_id,
386 i_login_id,
387 i_req_id,
388 i_prg_appl_id,
389 i_prg_id,
390 sysdate,
391 mctcd.inventory_item_id,
392 mctcd.transaction_cost,
393 0,
394 NULL,
395 'N',
396 0,
397 'N'
398 from mtl_cst_txn_cost_details mctcd
399 where mctcd.transaction_id = i_txn_id
400 and mctcd.organization_id = i_org_id
401 and mctcd.transaction_cost >= 0;
402 end if;
403 end if;
404
405 if (l_err_num <> 0) then
406 raise process_error;
407 end if;
408
409 EXCEPTION
410 when process_error then
411 o_err_num := l_err_num;
412 o_err_code := l_err_code;
413 o_err_msg := l_err_msg;
414
415 when others then
416 rollback;
417 o_err_num := SQLCODE;
418 o_err_msg := 'CSTPLENG.consume_create_layers (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
419
420 End consume_create_layers;
421
422 ----------------------------------------------------------------------------
423 -- FUNCTION --
424 -- get_source_number --
425 -- --
426 -- DESCRIPTION --
427 -- This function is used to obtain the transaction source --
428 -- for user identification of each transaction that creates an inventory --
429 -- layer --
430 -- The transaction_source is identified based on the transaction --
431 -- source type. It defaults to the transaction_id --
432 -- --
433 -- PURPOSE: --
434 -- obtain user identifiable transaction source to identify inventory --
435 -- layers --
436 -- --
437 -- PARAMETERS: --
438 -- i_txn_id : transaction id --
439 -- i_src_id : transaction source id --
440 -- i_src_type : transaction source type --
441 -- --
442 -- --
443 -- HISTORY: --
444 -- 04/20/00 Anitha B Created --
445 ----------------------------------------------------------------------------
446 FUNCTION get_source_number (i_txn_id IN NUMBER,
447 i_txn_src_type IN NUMBER,
448 i_src_id IN NUMBER
449 )
450 return VARCHAR2 IS
451 l_src_number VARCHAR2(240);
452 l_debug VARCHAR2(80);
453
454 Begin
455 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
456 if (i_txn_src_type = 1) then
457 /* Purchase Order, use RCV transaction ID */
458 /* Bug 3291126, we should use PO number */
459 select po.segment1
460 into l_src_number
461 from mtl_material_transactions mmt, po_headers_all po
462 where mmt.transaction_id = i_txn_id
463 and mmt.transaction_source_id = po.po_header_id;
464 elsif (i_txn_src_type = 2) then
465 /* Sales Order, use Order Number */
466 select segment1
467 into l_src_number
468 from mtl_sales_orders
469 where sales_order_id = i_src_id;
470 elsif (i_txn_src_type = 3) then
471 /* Account, use Account # */
472 select concatenated_segments
473 into l_src_number
474 from gl_code_combinations_kfv
475 where code_combination_id = i_src_id;
476 elsif (i_txn_src_type = 5) then
477 /* job or Schedule, use Job name */
478 select wip_entity_name
479 into l_src_number
480 from wip_entities
481 where wip_entity_id = i_src_id;
482 elsif (i_txn_src_type = 6) then
483 /* Account alias, use account number */
484 select concatenated_segments
485 into l_src_number
486 from gl_code_combinations_kfv
487 where code_combination_id = (select distribution_account
488 from mtl_generic_dispositions
489 where disposition_id = i_src_id);
490 elsif (i_txn_src_type = 7) then
491 /* internal requisition, use Requisition number */
492 select segment1
493 into l_src_number
494 from po_requisition_headers_all
495 where requisition_header_id = i_src_id;
496 elsif (i_txn_src_type = 8) then
497 /* internal order, use order number */
498 select segment1
499 into l_src_number
500 from mtl_sales_orders
501 where sales_order_id = i_src_id;
502 elsif (i_txn_src_type = 9) then
503 /* Cycle count, use cycle count header name */
504 select cycle_count_header_name
505 into l_src_number
506 from mtl_cycle_count_headers
507 where cycle_count_header_id = i_src_id;
508 elsif (i_txn_src_type = 10) then
509 /* physical inventory adjustment, use physical Inv name */
510 select physical_inventory_name
511 into l_src_number
512 from mtl_physical_inventories
513 where physical_inventory_id = i_src_id;
514 elsif (i_txn_src_type = 12) then
515 /* RMA, use sales order number */
516 select segment1
517 into l_src_number
518 from mtl_sales_orders
519 where sales_order_id = i_src_id;
520 elsif (i_txn_src_type = 13) then
521 /* Inventory, use distribution account ID */
522 select concatenated_segments
523 into l_src_number
524 from gl_code_combinations_kfv
525 where code_combination_id = (select
526 nvl(mmt.distribution_account_id,mmt.transaction_source_id)
527 from mtl_material_transactions mmt
528 where transaction_id = i_txn_id);
529 else
530 l_src_number := to_char(i_txn_id);
531 end if;
532
533 if (l_debug = 'Y') then
534 FND_FILE.PUT_LINE(FND_FILE.LOG,'get_source_number ...');
535 FND_FILE.PUT_LINE(FND_FILE.LOG,'Txn Src Type : ' || to_char(i_txn_src_type));
536 FND_FILE.PUT_LINE(FND_FILE.LOG,'Txn Src ID : ' || to_char(i_src_id));
537 FND_FILE.PUT_LINE(FND_FILE.LOG,'Txn Source : ' || l_src_number);
538 end if;
539
540 return l_src_number;
541 EXCEPTION
542 when no_data_found then
543 l_src_number := to_char(i_txn_id);
544 FND_FILE.PUT_LINE(FND_FILE.LOG,'No data found, using transaction ID');
545 return l_src_number;
546
547 END get_source_number;
548
549 ----------------------------------------------------------------------------
550 -- PROCEDURE --
551 -- insert_mclacd --
552 -- --
553 -- DESCRIPTION --
554 -- * Based on the actual_cost_table flag, this procedure picks up costs --
555 -- from the view cst_layer_actual_costs_v and inserts the cost --
556 -- details into MCLACD --
557 -- * The view is built on top of MCACD,MCTCD and CILCD, with a table --
558 -- flag that indicates which portion of the UNION clause needs to be --
559 -- execuated during each select against the view. --
560 -- --
561 -- PURPOSE: --
562 -- A single function used to insert transaction cost details into --
563 -- MCLACD. --
564 -- --
565 -- PARAMETERS: --
566 -- i_actual_cost_table : table from which actual costs are obtained --
567 -- i_layer_cost_table : table from which layers costs are obtained --
568 -- i_cur_layer_id : the inventory layer inserted into MCLACD --
569 -- i_actual_layer_id : inventory layer whose costs are used --
570 -- i_mode : (CREATE,CONSUME,REPLENISH), determines if --
571 -- outer join is required --
572 -- --
573 -- HISTORY: --
574 -- 04/20/00 Anitha B Created --
575 ----------------------------------------------------------------------------
576
577 PROCEDURE insert_mclacd (
578 i_txn_id IN NUMBER,
579 i_org_id IN NUMBER,
580 i_item_id IN NUMBER,
581 i_layer_id IN NUMBER,
582 i_cur_layer_id IN NUMBER,
583 i_qty IN NUMBER,
584 i_txn_action_id IN NUMBER,
585 i_user_id IN NUMBER,
586 i_login_id IN NUMBER,
587 i_req_id IN NUMBER,
588 i_prg_id IN NUMBER,
589 i_prg_appl_id IN NUMBER,
590 i_actual_cost_table IN VARCHAR2,
591 i_layer_cost_table IN VARCHAR2,
592 i_actual_layer_id IN NUMBER,
593 i_mode IN VARCHAR2,
594 o_err_num OUT NOCOPY NUMBER,
595 o_err_code OUT NOCOPY VARCHAR2,
596 o_err_msg OUT NOCOPY VARCHAR2
597 )
598 /* i_cur_layer_id is the inv layer which is being used in MCLACD, to get the
599 layer costs
600 i_actual_layer_id is used to get the actual costs
601 */
602 IS
603 l_stmt_num NUMBER;
604 l_err_num NUMBER;
605 l_txn_type_id NUMBER;
606 l_err_code VARCHAR2(240);
607 l_err_msg VARCHAR2(240);
608 process_error EXCEPTION;
609 l_mclacd_exists NUMBER;
610 l_debug VARCHAR2(80);
611
612 /* EAM Acct Enh Project */
613 l_zero_cost_flag NUMBER := -1;
614 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
615 l_msg_count NUMBER := 0;
616 l_msg_data VARCHAR2(8000) := '';
617 l_api_message VARCHAR2(8000);
618
619 Begin
620 l_stmt_num := 0;
621 l_err_num := 0;
622 l_err_code := '';
623 l_err_msg := '';
624 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
625
626 select transaction_type_id
627 into l_txn_type_id
628 from mtl_material_transactions
629 where transaction_id = i_txn_id;
630
631 /* Write to log file */
632 if (l_debug = 'Y') then
633 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert Row in MCLACD...');
634 FND_FILE.PUT_LINE(FND_FILE.LOG,'layer_id:' || to_char(i_layer_id));
635 FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_layer:' || to_char(i_cur_layer_id));
636 FND_FILE.PUT_LINE(FND_FILE.LOG,'qty :'|| to_char(i_qty));
637 FND_FILE.PUT_LINE(FND_FILE.LOG,'actual_cost_table:' || (i_actual_cost_table));
638 FND_FILE.PUT_LINE(FND_FILE.LOG,'layer_cost_table:' || (i_layer_cost_table));
639 FND_FILE.PUT_LINE(FND_FILE.LOG,'actual_layer_id :' || to_char(i_actual_layer_id));
640 end if;
641
642 select count(*)
643 into l_mclacd_exists
644 from mtl_cst_layer_act_cost_details
645 where transaction_id = i_txn_id
646 and layer_id = i_layer_id
647 and inv_layer_id = i_cur_layer_id;
648
649 if (l_mclacd_exists > 0) then
650 l_stmt_num := 2;
651 update mtl_cst_layer_act_cost_details
652 set layer_quantity = nvl(layer_quantity,0) + i_qty,
653 variance_amount = nvl(variance_amount,0) + (nvl(actual_cost,0)-nvl(layer_cost,0))*i_qty
654 where transaction_id = i_txn_id
655 and layer_id = i_layer_id
656 and inv_layer_id = i_actual_layer_id;
657 /* when in layer is driven negative, then apply_layer_material_ovhd should not be called again */
658 if (i_mode = 'CONSUME') then
659 o_err_num := 999;
660 end if;
661 else
662 /* changed the logic for the bug 5016055
663 combinations:
664 mode actual_cost_table layer_cost_table
665 ----- ----------------- ----------------
666 1) CREATE MCACD MCACD
667 2) CREATE MCTCD MCTCD
668 3) CREATE CILCD CILCD
669 4) CREATE None None
670 5) UDPATE CILCD CILCD
671 6) REPLENISH CILCD CILCD
672 7) CONSUME MCACD CILCD
673 8) CONSUME MCTCD CILCD
674 9) CONSUME CILCD CILCD
675
676 */
677 -- case 1 CREATE MCACD MCACD
678 if(i_mode = 'CREATE' and i_actual_cost_table = 'MCACD' and i_layer_cost_table = 'MCACD') then
679
680 /* EAM Acct Enh Project */
681 CST_Utility_PUB.get_zeroCostIssue_flag (
682 p_api_version => 1.0,
683 x_return_status => l_return_status,
684 x_msg_count => l_msg_count,
685 x_msg_data => l_msg_data,
686 p_txn_id => i_txn_id,
687 x_zero_cost_flag => l_zero_cost_flag
688 );
689
690 if (l_return_status <> fnd_api.g_ret_sts_success) then
691 FND_FILE.put_line(FND_FILE.log, l_msg_data);
692 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
693 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
694 FND_MESSAGE.set_token('TEXT', l_api_message);
695 FND_MSG_pub.add;
696 raise fnd_api.g_exc_unexpected_error;
697 end if;
698
699 if (l_debug = 'Y') then
700 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
701 end if;
702
703 insert into mtl_cst_layer_act_cost_details (
704 transaction_id,
705 organization_id,
706 layer_id,
707 inv_layer_id,
708 cost_element_id,
709 level_type,
710 layer_quantity,
711 layer_cost,
712 actual_cost,
713 variance_amount,
714 inventory_item_id,
715 user_entered,
716 last_update_date,
717 last_updated_by,
718 creation_date,
719 created_by,
720 last_update_login,
721 request_id,
722 program_application_id,
723 program_id,
724 program_update_date)
725 select i_txn_id, -- txn id
726 i_org_id, -- org id
727 i_layer_id,
728 i_cur_layer_id,
729 mcacd.cost_element_id,
730 mcacd.level_type,
731 i_qty,
732 decode(l_zero_cost_flag, 1, 0,mcacd.actual_cost),
733 decode(l_zero_cost_flag, 1, 0,mcacd.actual_cost),
734 0,
735 i_item_id,
736 'N',
737 sysdate,
738 i_user_id,
739 sysdate,
740 i_user_id,
741 i_login_id,
742 i_req_id,
743 i_prg_appl_id,
744 i_prg_id,
745 sysdate
746 from mtl_cst_actual_cost_details mcacd
747 where mcacd.organization_id = i_org_id
748 and mcacd.transaction_id = i_txn_id
749 and mcacd.user_entered = 'Y';
750
751 -- case 2 CREATE MCTCD MCTCD
752 elsif(i_mode = 'CREATE' and i_actual_cost_table = 'MCTCD' and i_layer_cost_table = 'MCTCD') then
753
754 /* EAM Acct Enh Project */
755 CST_Utility_PUB.get_zeroCostIssue_flag (
756 p_api_version => 1.0,
757 x_return_status => l_return_status,
758 x_msg_count => l_msg_count,
759 x_msg_data => l_msg_data,
760 p_txn_id => i_txn_id,
761 x_zero_cost_flag => l_zero_cost_flag
762 );
763
764 if (l_return_status <> fnd_api.g_ret_sts_success) then
765 FND_FILE.put_line(FND_FILE.log, l_msg_data);
766 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
767 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
768 FND_MESSAGE.set_token('TEXT', l_api_message);
769 FND_MSG_pub.add;
770 raise fnd_api.g_exc_unexpected_error;
771 end if;
772
773 if (l_debug = 'Y') then
774 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
775 end if;
776
777 insert into mtl_cst_layer_act_cost_details (
778 transaction_id,
779 organization_id,
780 layer_id,
781 inv_layer_id,
782 cost_element_id,
783 level_type,
784 layer_quantity,
785 layer_cost,
786 actual_cost,
787 variance_amount,
788 inventory_item_id,
789 user_entered,
790 last_update_date,
791 last_updated_by,
792 creation_date,
793 created_by,
794 last_update_login,
795 request_id,
796 program_application_id,
797 program_id,
798 program_update_date)
799 select i_txn_id,
800 i_org_id,
801 i_layer_id,
802 i_cur_layer_id,
803 mctcd.cost_element_id,
804 mctcd.level_type,
805 i_qty,
806 decode(l_zero_cost_flag, 1, 0,mctcd.transaction_cost),
807 decode(l_zero_cost_flag, 1, 0,mctcd.transaction_cost),
808 0,
809 i_item_id,
810 'N',
811 sysdate,
812 i_user_id,
813 sysdate,
814 i_user_id,
815 i_login_id,
816 i_req_id,
817 i_prg_appl_id,
818 i_prg_id,
819 sysdate
820 from mtl_cst_txn_cost_details mctcd
821 where mctcd.organization_id = i_org_id
822 and mctcd.transaction_id = i_txn_id ;
823
824 -- case 4 CREATE None None
825 elsif(i_mode = 'CREATE' and i_actual_cost_table = 'NONE' and i_layer_cost_table = 'NONE') then
826
827 insert into mtl_cst_layer_act_cost_details (
828 transaction_id,
829 organization_id,
830 layer_id,
831 inv_layer_id,
832 cost_element_id,
833 level_type,
834 layer_quantity,
835 layer_cost,
836 actual_cost,
837 variance_amount,
838 inventory_item_id,
839 user_entered,
840 last_update_date,
841 last_updated_by,
842 creation_date,
843 created_by,
844 last_update_login,
845 request_id,
846 program_application_id,
847 program_id,
848 program_update_date)
849 select i_txn_id,
850 i_org_id,
851 i_layer_id,
852 i_cur_layer_id,
853 1, --CE
854 1, --LT
855 i_qty,
856 0, --layer cost
857 0, -- actual cost
858 0, -- var amount
859 i_item_id,
860 'N',
861 sysdate,
862 i_user_id,
863 sysdate,
864 i_user_id,
865 i_login_id,
866 i_req_id,
867 i_prg_appl_id,
868 i_prg_id,
869 sysdate
870 from dual;
871 -- case 6 REPLENISH CILCD CILCD
872 elsif(i_mode = 'REPLENISH' and i_actual_cost_table = 'CILCD' and i_layer_cost_table = 'CILCD') then
873
874 insert into mtl_cst_layer_act_cost_details (
875 transaction_id,
876 organization_id,
877 layer_id,
878 inv_layer_id,
879 cost_element_id,
880 level_type,
881 layer_quantity,
882 layer_cost,
883 actual_cost,
884 variance_amount,
885 payback_variance_amount,
886 inventory_item_id,
887 user_entered,
888 last_update_date,
889 last_updated_by,
890 creation_date,
891 created_by,
892 last_update_login,
893 request_id,
894 program_application_id,
895 program_id,
896 program_update_date)
897 select i_txn_id,
898 i_org_id,
899 i_layer_id,
900 i_cur_layer_id,
901 cilcd2.cost_element_id,
902 cilcd2.level_type,
903 i_qty,
904 nvl(cilcd2.layer_cost,0),
905 nvl(cilcd1.layer_cost,0),
906 decode(l_txn_type_id,68,0,(nvl(cilcd1.layer_cost,0)-nvl(cilcd2.layer_cost,0))*i_qty),
907 decode(l_txn_type_id,68,(nvl(cilcd1.layer_cost,0)-nvl(cilcd2.layer_cost,0))*i_qty,0),
908 i_item_id,
909 'N',
910 sysdate,
911 i_user_id,
912 sysdate,
913 i_user_id,
914 i_login_id,
915 i_req_id,
916 i_prg_appl_id,
917 i_prg_id,
918 sysdate
919 FROM cst_inv_layer_cost_details cilcd1,
920 cst_inv_layer_cost_details cilcd2
921 where cilcd1.inv_layer_id (+) = i_actual_layer_id
922 and cilcd2.inv_layer_id = i_cur_layer_id
923 and cilcd1.cost_element_id(+) = cilcd2.cost_element_id
924 and cilcd1.level_type (+) = cilcd2.level_type
925 UNION
926 select i_txn_id,
927 i_org_id,
928 i_layer_id,
929 i_cur_layer_id,
930 cilcd1.cost_element_id,
931 cilcd1.level_type,
932 i_qty,
933 nvl(cilcd2.layer_cost,0),
934 nvl(cilcd1.layer_cost,0),
935 decode(l_txn_type_id,68,0,(nvl(cilcd1.layer_cost,0)-nvl(cilcd2.layer_cost,0))*i_qty),
936 decode(l_txn_type_id,68,(nvl(cilcd1.layer_cost,0)-nvl(cilcd2.layer_cost,0))*i_qty,0),
937 i_item_id,
938 'N',
939 sysdate,
940 i_user_id,
941 sysdate,
942 i_user_id,
943 i_login_id,
944 i_req_id,
945 i_prg_appl_id,
946 i_prg_id,
947 sysdate
948 FROM cst_inv_layer_cost_details cilcd1,
949 cst_inv_layer_cost_details cilcd2
950 where cilcd1.inv_layer_id = i_actual_layer_id
951 and cilcd2.inv_layer_id(+) = i_cur_layer_id
952 and cilcd1.cost_element_id = cilcd2.cost_element_id (+)
953 and cilcd1.level_type = cilcd2.level_type(+);
954
955 -- case 7 CONSUME MCACD CILCD
956 elsif(i_mode = 'CONSUME' and i_actual_cost_table = 'MCACD' and i_layer_cost_table = 'CILCD') then
957
958 insert into mtl_cst_layer_act_cost_details (
959 transaction_id,
960 organization_id,
961 layer_id,
962 inv_layer_id,
963 cost_element_id,
964 level_type,
965 layer_quantity,
966 layer_cost,
967 actual_cost,
968 variance_amount,
969 payback_variance_amount,
970 inventory_item_id,
971 user_entered,
972 last_update_date,
973 last_updated_by,
974 creation_date,
975 created_by,
976 last_update_login,
977 request_id,
978 program_application_id,
979 program_id,
980 program_update_date)
981 select i_txn_id,
982 i_org_id,
983 i_layer_id,
984 i_cur_layer_id,
985 cilcd.cost_element_id,
986 cilcd.level_type,
987 i_qty,
988 nvl(cilcd.layer_cost,0),
989 nvl(mcacd.actual_cost,0),
990 decode(l_txn_type_id,68,0,(nvl(mcacd.actual_cost,0)-nvl(cilcd.layer_cost,0))*i_qty),
991 decode(l_txn_type_id,68,(nvl(mcacd.actual_cost,0)-nvl(cilcd.layer_cost,0))*i_qty,0),
992 i_item_id,
993 'N',
994 sysdate,
995 i_user_id,
996 sysdate,
997 i_user_id,
998 i_login_id,
999 i_req_id,
1000 i_prg_appl_id,
1001 i_prg_id,
1002 sysdate
1003 FROM cst_inv_layer_cost_details cilcd,mtl_cst_actual_cost_details mcacd
1004 where mcacd.organization_id (+) = i_org_id
1005 and mcacd.transaction_id(+) = i_txn_id
1006 and cilcd.inv_layer_id = i_cur_layer_id
1007 and cilcd.cost_element_id = mcacd.cost_element_id (+)
1008 and cilcd.level_type = mcacd.level_type (+)
1009 AND mcacd.user_entered(+) = 'Y'
1010 UNION
1011 select i_txn_id,
1012 i_org_id,
1013 i_layer_id,
1014 i_cur_layer_id,
1015 mcacd.cost_element_id,
1016 mcacd.level_type,
1017 i_qty,
1018 nvl(cilcd.layer_cost,0),
1019 nvl(mcacd.actual_cost,0),
1020 decode(l_txn_type_id,68,0,(nvl(mcacd.actual_cost,0)-nvl(cilcd.layer_cost,0))*i_qty),
1021 decode(l_txn_type_id,68,(nvl(mcacd.actual_cost,0)-nvl(cilcd.layer_cost,0))*i_qty,0),
1022 i_item_id,
1023 'N',
1024 sysdate,
1025 i_user_id,
1026 sysdate,
1027 i_user_id,
1028 i_login_id,
1029 i_req_id,
1030 i_prg_appl_id,
1031 i_prg_id,
1032 sysdate
1033 FROM cst_inv_layer_cost_details cilcd,mtl_cst_actual_cost_details mcacd
1034 where mcacd.organization_id = i_org_id
1035 and mcacd.transaction_id = i_txn_id
1036 and cilcd.inv_layer_id(+) = i_cur_layer_id
1037 and cilcd.cost_element_id(+) = mcacd.cost_element_id
1038 and cilcd.level_type (+) = mcacd.level_type
1039 AND mcacd.user_entered = 'Y' ;
1040
1041
1042 -- case 8 CONSUME MCTCD CILCD
1043 elsif(i_mode = 'CONSUME' and i_actual_cost_table = 'MCTCD' and i_layer_cost_table = 'CILCD') then
1044 insert into mtl_cst_layer_act_cost_details (
1045 transaction_id,
1046 organization_id,
1047 layer_id,
1048 inv_layer_id,
1049 cost_element_id,
1050 level_type,
1051 layer_quantity,
1052 layer_cost,
1053 actual_cost,
1054 variance_amount,
1055 payback_variance_amount,
1056 inventory_item_id,
1057 user_entered,
1058 last_update_date,
1059 last_updated_by,
1060 creation_date,
1061 created_by,
1062 last_update_login,
1063 request_id,
1064 program_application_id,
1065 program_id,
1066 program_update_date)
1067 select i_txn_id,
1068 i_org_id,
1069 i_layer_id,
1070 i_cur_layer_id,
1071 cilcd.cost_element_id,
1072 cilcd.level_type,
1073 i_qty,
1074 nvl(cilcd.layer_cost,0),
1075 nvl(mctcd.transaction_cost,0),
1076 decode(l_txn_type_id,68,0,(nvl(mctcd.transaction_cost,0)-nvl(cilcd.layer_cost,0))*i_qty),
1077 decode(l_txn_type_id,68,(nvl(mctcd.transaction_cost,0)-nvl(cilcd.layer_cost,0))*i_qty,0),
1078 i_item_id,
1079 'N',
1080 sysdate,
1081 i_user_id,
1082 sysdate,
1083 i_user_id,
1084 i_login_id,
1085 i_req_id,
1086 i_prg_appl_id,
1087 i_prg_id,
1088 sysdate
1089 FROM cst_inv_layer_cost_details cilcd,mtl_cst_txn_cost_details mctcd
1090 where mctcd.organization_id(+) = i_org_id
1091 and mctcd.transaction_id (+) = i_txn_id
1092 and cilcd.inv_layer_id = i_cur_layer_id
1093 and cilcd.cost_element_id = mctcd.cost_element_id (+)
1094 and cilcd.level_type = mctcd.level_type (+)
1095 UNION
1096 select i_txn_id,
1097 i_org_id,
1098 i_layer_id,
1099 i_cur_layer_id,
1100 mctcd.cost_element_id,
1101 mctcd.level_type,
1102 i_qty,
1103 nvl(cilcd.layer_cost,0),
1104 nvl(mctcd.transaction_cost,0),
1105 decode(l_txn_type_id,68,0,(nvl(mctcd.transaction_cost,0)-nvl(cilcd.layer_cost,0))*i_qty),
1106 decode(l_txn_type_id,68,(nvl(mctcd.transaction_cost,0)-nvl(cilcd.layer_cost,0))*i_qty,0),
1107 i_item_id,
1108 'N',
1109 sysdate,
1110 i_user_id,
1111 sysdate,
1112 i_user_id,
1113 i_login_id,
1114 i_req_id,
1115 i_prg_appl_id,
1116 i_prg_id,
1117 sysdate
1118 FROM cst_inv_layer_cost_details cilcd,mtl_cst_txn_cost_details mctcd
1119 where mctcd.organization_id = i_org_id
1120 and mctcd.transaction_id = i_txn_id
1121 and cilcd.inv_layer_id (+) = i_cur_layer_id
1122 and cilcd.cost_element_id(+) = mctcd.cost_element_id
1123 and cilcd.level_type(+) = mctcd.level_type ;
1124
1125 -- case 3 CREATE CILCD CILCD
1126 -- case 5 UDPATE CILCD CILCD
1127 -- case 9 CONSUME CILCD CILCD
1128 elsif( (i_mode = 'CREATE' and i_actual_cost_table = 'CILCD' and i_layer_cost_table = 'CILCD')
1129 OR(i_mode = 'UPDATE' and i_actual_cost_table = 'CILCD' and i_layer_cost_table = 'CILCD')
1130 OR(i_mode = 'CONSUME' and i_actual_cost_table = 'CILCD' and i_layer_cost_table = 'CILCD')
1131 ) then
1132
1133 /* EAM Acct Enh Project */
1134 CST_Utility_PUB.get_zeroCostIssue_flag (
1135 p_api_version => 1.0,
1136 x_return_status => l_return_status,
1137 x_msg_count => l_msg_count,
1138 x_msg_data => l_msg_data,
1139 p_txn_id => i_txn_id,
1140 x_zero_cost_flag => l_zero_cost_flag
1141 );
1142
1143 if (l_return_status <> fnd_api.g_ret_sts_success) then
1144 FND_FILE.put_line(FND_FILE.log, l_msg_data);
1145 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
1146 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
1147 FND_MESSAGE.set_token('TEXT', l_api_message);
1148 FND_MSG_pub.add;
1149 raise fnd_api.g_exc_unexpected_error;
1150 end if;
1151
1152 if (l_debug = 'Y') then
1153 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
1154 end if;
1155 insert into mtl_cst_layer_act_cost_details (
1156 transaction_id,
1157 organization_id,
1158 layer_id,
1159 inv_layer_id,
1160 cost_element_id,
1161 level_type,
1162 layer_quantity,
1163 layer_cost,
1164 actual_cost,
1165 variance_amount,
1166 inventory_item_id,
1167 user_entered,
1168 last_update_date,
1169 last_updated_by,
1170 creation_date,
1171 created_by,
1172 last_update_login,
1173 request_id,
1174 program_application_id,
1175 program_id,
1176 program_update_date)
1177 select i_txn_id,
1178 i_org_id,
1179 i_layer_id,
1180 i_cur_layer_id,
1181 cilcd.cost_element_id,
1182 cilcd.level_type,
1183 i_qty,
1184 decode(l_zero_cost_flag, 1, 0,cilcd.layer_cost),
1185 decode(l_zero_cost_flag, 1, 0,cilcd.layer_cost),
1186 0,
1187 i_item_id,
1188 'N',
1189 sysdate,
1190 i_user_id,
1191 sysdate,
1192 i_user_id,
1193 i_login_id,
1194 i_req_id,
1195 i_prg_appl_id,
1196 i_prg_id,
1197 sysdate
1198 from cst_inv_layers cil, cst_inv_layer_cost_details cilcd
1199 where cil.inv_layer_id = cilcd.inv_layer_id
1200 and cil.layer_id = cilcd.layer_id
1201 and cil.inv_layer_id = i_actual_layer_id;
1202
1203 else /* Else Case */
1204 l_stmt_num := 15;
1205 /* EAM Acct Enh Project */
1206 CST_Utility_PUB.get_zeroCostIssue_flag (
1207 p_api_version => 1.0,
1208 x_return_status => l_return_status,
1209 x_msg_count => l_msg_count,
1210 x_msg_data => l_msg_data,
1211 p_txn_id => i_txn_id,
1212 x_zero_cost_flag => l_zero_cost_flag
1213 );
1214
1215 if (l_return_status <> fnd_api.g_ret_sts_success) then
1216 FND_FILE.put_line(FND_FILE.log, l_msg_data);
1217 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
1218 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
1219 FND_MESSAGE.set_token('TEXT', l_api_message);
1220 FND_MSG_pub.add;
1221 raise fnd_api.g_exc_unexpected_error;
1222 end if;
1223
1224 if (l_debug = 'Y') then
1225 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
1226 end if;
1227
1228 l_stmt_num := 20;
1229
1230 insert into mtl_cst_layer_act_cost_details (
1231 transaction_id,
1232 organization_id,
1233 layer_id,
1234 inv_layer_id,
1235 cost_element_id,
1236 level_type,
1237 layer_quantity,
1238 layer_cost,
1239 actual_cost,
1240 variance_amount,
1241 inventory_item_id,
1242 user_entered,
1243 last_update_date,
1244 last_updated_by,
1245 creation_date,
1246 created_by,
1247 last_update_login,
1248 request_id,
1249 program_application_id,
1250 program_id,
1251 program_update_date)
1252 select i_txn_id,
1253 i_org_id,
1254 i_layer_id,
1255 i_cur_layer_id,
1256 cilcd.cost_element_id,
1257 cilcd.level_type,
1258 i_qty,
1259 decode(l_zero_cost_flag, 1, 0, cilcd.layer_cost),
1260 decode(l_zero_cost_flag, 1, 0, cilcd.layer_cost),
1261 0,
1262 i_item_id,
1263 'N',
1264 sysdate,
1265 i_user_id,
1266 sysdate,
1267 i_user_id,
1268 i_login_id,
1269 i_req_id,
1270 i_prg_appl_id,
1271 i_prg_id,
1272 sysdate
1273 from cst_inv_layers cil, cst_inv_layer_cost_details cilcd
1274 where cil.inv_layer_id = cilcd.inv_layer_id
1275 and cil.layer_id = cilcd.layer_id
1276 and cil.inv_layer_id = i_actual_layer_id;
1277
1278
1279 end if;
1280 end if;
1281 if (l_debug = 'Y') then
1282 FND_FILE.PUT_LINE(FND_FILE.LOG,sql%rowcount || ' records inserted using stmt : ' || to_char(l_stmt_num));
1283 end if;
1284
1285 if ((l_err_num <> 0) and (l_err_num <> 999)) then
1286 raise process_error;
1287 end if;
1288
1289 EXCEPTION
1290 when process_error then
1291 o_err_num := l_err_num;
1292 o_err_code := l_err_code;
1293 o_err_msg := l_err_msg;
1294 when others then
1295 rollback;
1296 o_err_num := SQLCODE;
1297 o_err_msg := 'CSTPLENG.insert_mclacd (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
1298 End insert_mclacd;
1299
1300 ----------------------------------------------------------------------------
1301 -- PROCEDURE --
1302 -- create_layers --
1303 -- --
1304 -- DESCRIPTION --
1305 -- * Create inventory layer for i_txn_qty (maybe negative) OR add --
1306 -- i_txn_qty to the inventory layer created last --
1307 -- * Insert into CIL,CILCD,MCLACD using costs from MCACD(cost hook), --
1308 -- MCTCD(if available), CILCD(latest layer cost,the layer may or --
1309 -- may not have positive qty), 0 cost if no costs available --
1310 -- update CIL layer cost, burden cost and unburdened cost --
1311 -- * If layer created has positive quantity, then replenish all --
1312 -- negative inventory layers --
1313 -- --
1314 -- PURPOSE: --
1315 -- create inventory layers using the sequence cst_inv_layers_s --
1316 -- --
1317 -- PARAMETERS: --
1318 -- i_txn_qty : primary quantity --
1319 -- i_interorg_rec : interorg shimpment (= 0), --
1320 -- interorg receipt (= 1) --
1321 -- subinv transfer with no layer change (= 3) --
1322 -- otherwise (= null) --
1323 -- --
1324 -- HISTORY: --
1325 -- 04/20/00 Anitha B Created --
1326 -- 09/14/02 Ray Changes for Layer Minimization (BOM.I) --
1327 ----------------------------------------------------------------------------
1328 procedure create_layers(
1329 i_org_id IN NUMBER,
1330 i_txn_id IN NUMBER,
1331 i_layer_id IN NUMBER,
1332 i_item_id IN NUMBER,
1333 i_txn_qty IN NUMBER,
1334 i_cost_method IN NUMBER,
1335 i_txn_src_type IN NUMBER,
1336 i_txn_action_id IN NUMBER,
1337 i_cost_hook IN NUMBER,
1338 i_interorg_rec IN NUMBER,
1339 i_cost_type IN NUMBER,
1340 i_mat_ct_id IN NUMBER,
1341 i_avg_rates_id IN NUMBER,
1342 i_exp_flag IN NUMBER,
1343 i_user_id IN NUMBER,
1344 i_login_id IN NUMBER,
1345 i_req_id IN NUMBER,
1346 i_prg_appl_id IN NUMBER,
1347 i_prg_id IN NUMBER,
1348 o_err_num OUT NOCOPY NUMBER,
1349 o_err_code OUT NOCOPY VARCHAR2,
1350 o_err_msg OUT NOCOPY VARCHAR2
1351 ) IS
1352 TYPE LayerCurType IS REF CURSOR;
1353
1354 negative_layer_cursor LayerCurType;
1355 sql_stmt VARCHAR2(240);
1356 l_stmt_num NUMBER;
1357 l_err_num NUMBER;
1358 l_err_code VARCHAR2(240);
1359 l_err_msg VARCHAR2(240);
1360 l_src_id NUMBER;
1361 l_src_number VARCHAR2(240);
1362 l_inv_layer_id NUMBER;
1363 l_neg_layer_id NUMBER;
1364 l_neg_layer_qty NUMBER;
1365 l_qty_available NUMBER;
1366 l_qty NUMBER;
1367 l_count NUMBER;
1368 l_actual_cost_table VARCHAR2(20);
1369 l_from_org NUMBER;
1370 process_error EXCEPTION;
1371 l_debug VARCHAR2(80);
1372 l_create NUMBER;
1373 l_last_txn_id NUMBER;
1374 l_last_txn_type_id NUMBER;
1375 l_last_rcv_txn_id NUMBER;
1376 l_last_moh NUMBER;
1377 l_txn_type_id NUMBER;
1378 l_rcv_txn_id NUMBER;
1379 l_moh NUMBER;
1380 l_last_layer_cost NUMBER;
1381 l_layer_cost NUMBER;
1382 l_merge NUMBER;
1383 BEGIN
1384 /* Initialize */
1385 l_stmt_num := 0;
1386 l_err_num := 0;
1387 l_err_code := '';
1388 l_err_msg := '';
1389 l_from_org := 0;
1390 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
1391 l_create := 1;
1392
1393 /* If expense item, then insert into MCACD using current costs. No inventory layer created */
1394 IF (i_exp_flag = 1) THEN
1395 IF (l_debug = 'Y') THEN
1396 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for expense flag of 1...');
1397 END IF;
1398
1399 l_stmt_num := 5;
1400 SELECT COUNT(*)
1401 INTO l_count
1402 FROM mtl_cst_txn_cost_details
1403 WHERE transaction_id = i_txn_id
1404 AND organization_id = i_org_id;
1405
1406 IF (l_count > 0) THEN
1407 l_stmt_num := 10;
1408 INSERT
1409 INTO mtl_cst_actual_cost_details (
1410 transaction_id,
1411 organization_id,
1412 layer_id,
1413 cost_element_id,
1414 level_type,
1415 transaction_action_id,
1416 last_update_date,
1417 last_updated_by,
1418 creation_date,
1419 created_by,
1420 last_update_login,
1421 request_id,
1422 program_application_id,
1423 program_id,
1424 program_update_date,
1425 inventory_item_id,
1426 actual_cost,
1427 prior_cost,
1428 new_cost,
1429 insertion_flag,
1430 variance_amount,
1431 user_entered)
1432 SELECT i_txn_id,
1433 i_org_id,
1434 i_layer_id,
1435 ctcd.cost_element_id,
1436 ctcd.level_type,
1437 i_txn_action_id,
1438 sysdate,
1439 i_user_id,
1440 sysdate,
1441 i_user_id,
1442 i_login_id,
1443 i_req_id,
1444 i_prg_appl_id,
1445 i_prg_id,
1446 sysdate,
1447 ctcd.inventory_item_id,
1448 ctcd.transaction_cost,
1449 0,
1450 0,
1451 'Y',
1452 0,
1453 'N'
1454 FROM mtl_cst_txn_cost_details ctcd
1455 WHERE ctcd.transaction_id = i_txn_id
1456 AND ctcd.organization_id = i_org_id
1457 /*AND ctcd.transaction_cost >= 0*/; -- commented for bug#3835412
1458 ELSE
1459 l_stmt_num := 15;
1460 SELECT count(*)
1461 INTO l_count
1462 FROM cst_layer_cost_details
1463 WHERE layer_id = i_layer_id;
1464
1465 IF (l_count > 0) THEN
1466 l_stmt_num := 20;
1467 INSERT
1468 INTO mtl_cst_actual_cost_details (
1469 transaction_id,
1470 organization_id,
1471 layer_id,
1472 cost_element_id,
1473 level_type,
1474 transaction_action_id,
1475 last_update_date,
1476 last_updated_by,
1477 creation_date,
1478 created_by,
1479 last_update_login,
1480 request_id,
1481 program_application_id,
1482 program_id,
1483 program_update_date,
1484 inventory_item_id,
1485 actual_cost,
1486 prior_cost,
1487 new_cost,
1488 insertion_flag,
1489 variance_amount,
1490 user_entered)
1491 SELECT i_txn_id,
1492 i_org_id,
1493 i_layer_id,
1494 clcd.cost_element_id,
1495 clcd.level_type,
1496 i_txn_action_id,
1497 sysdate,
1498 i_user_id,
1499 sysdate,
1500 i_user_id,
1501 i_login_id,
1502 i_req_id,
1503 i_prg_appl_id,
1504 i_prg_id,
1505 sysdate,
1506 i_item_id,
1507 clcd.item_cost,
1508 clcd.item_cost,
1509 clcd.item_cost,
1510 'N',
1511 0,
1512 'N'
1513 FROM cst_layer_cost_details clcd
1514 WHERE layer_id = i_layer_id;
1515 ELSE
1516 l_stmt_num := 25;
1517 INSERT
1518 INTO mtl_cst_actual_cost_details (
1519 transaction_id,
1520 organization_id,
1521 layer_id,
1522 cost_element_id,
1523 level_type,
1524 transaction_action_id,
1525 last_update_date,
1526 last_updated_by,
1527 creation_date,
1528 created_by,
1529 last_update_login,
1530 request_id,
1531 program_application_id,
1532 program_id,
1533 program_update_date,
1534 inventory_item_id,
1535 actual_cost,
1536 prior_cost,
1537 new_cost,
1538 insertion_flag,
1539 variance_amount,
1540 user_entered)
1541 SELECT i_txn_id,
1542 i_org_id,
1543 i_layer_id,
1544 1,
1545 1,
1546 i_txn_action_id,
1547 sysdate,
1548 i_user_id,
1549 sysdate,
1550 i_user_id,
1551 i_login_id,
1552 i_req_id,
1553 i_prg_appl_id,
1554 i_prg_id,
1555 sysdate,
1556 i_item_id,
1557 0,
1558 0,
1559 0,
1560 'Y',
1561 0,
1562 'N'
1563 FROM dual;
1564 END IF; /* l_count > 0 */
1565 END IF; /* l_count > 0 */
1566 RETURN;
1567 END IF; /* i_exp_flag = 1 */
1568
1569 /* Find the inventory layer last created */
1570 l_stmt_num := 30;
1571 SELECT nvl(MAX(inv_layer_id),-1)
1572 INTO l_inv_layer_id
1573 FROM cst_inv_layers
1574 WHERE layer_id = i_layer_id;
1575
1576 IF (l_debug = 'Y') THEN
1577 FND_FILE.PUT_LINE(FND_FILE.LOG,'Last Inventory Layer : ' || l_inv_layer_id);
1578 END IF;
1579
1580 /* Obtain cost table, whose costs need to be used to insert into MCLACD
1581 If cost_hook is present, use MCACD, else use costs from MCTCD, or the latest
1582 inventory layer with positive quantity, 0 cost otherwise */
1583 IF (i_cost_hook = 1) THEN
1584 l_actual_cost_table := 'MCACD';
1585 ELSE
1586 l_stmt_num := 35;
1587 SELECT count(*)
1588 INTO l_count
1589 FROM mtl_cst_txn_cost_details
1590 WHERE transaction_id = i_txn_id
1591 AND organization_id = i_org_id
1592 /* AND transaction_cost >= 0 */; -- commented for bug#3835412
1593
1594 IF (l_count > 0) THEN
1595 l_actual_cost_table := 'MCTCD';
1596 ELSE
1597 IF (l_inv_layer_id = -1) THEN
1598 l_actual_cost_table := 'NONE';
1599 ELSE
1600 l_actual_cost_table := 'CILCD';
1601 END IF;
1602 END IF;
1603 END IF;
1604
1605 IF (l_debug = 'Y') THEN
1606 FND_FILE.PUT_LINE(FND_FILE.LOG,'Actual cost table : ' || l_actual_cost_table);
1607 END IF;
1608
1609 /* Insert into MCLACD */
1610 l_stmt_num := 40;
1611 insert_mclacd(
1612 i_txn_id,
1613 i_org_id,
1614 i_item_id,
1615 i_layer_id,
1616 l_inv_layer_id,
1617 i_txn_qty,
1618 i_txn_action_id,
1619 i_user_id,
1620 i_login_id,
1621 i_req_id,
1622 i_prg_id,
1623 i_prg_appl_id,
1624 l_actual_cost_table,
1625 l_actual_cost_table,
1626 l_inv_layer_id,
1627 'CREATE',
1628 l_err_num,
1629 l_err_code,
1630 l_err_msg);
1631
1632 IF (l_err_num <> 0) THEN
1633 RAISE process_error;
1634 END IF;
1635
1636 /* Apply material overhead to certain transactions for asset items
1637 and asset subinventories */
1638 l_stmt_num := 45;
1639 IF ((i_exp_flag <> 1) AND
1640 (i_txn_qty > 0) AND
1641 ((i_txn_action_id = 27 AND i_txn_src_type = 1) OR /* PO Receipt */
1642 (i_txn_action_id = 1 AND i_txn_src_type = 1) OR /* RTV */
1643 (i_txn_action_id = 29 AND i_txn_src_type = 1) OR /* Delivery Adj */
1644 (i_txn_action_id = 31 AND i_txn_src_type = 5) OR /* WIP completion */
1645
1646 (i_txn_action_id = 6) OR /* Change of ownership */
1647 (i_txn_action_id = 32 AND i_txn_src_type = 5) OR /* Assembly completion */
1648 (i_interorg_rec = 1))) /* Interorg receipt */
1649 THEN
1650 IF (l_debug = 'Y') then
1651 FND_FILE.PUT_LINE(FND_FILE.LOG,'Apply layer material overhead ...');
1652 END IF;
1653 l_stmt_num := 50;
1654 apply_layer_material_ovhd(
1655 i_org_id,
1656 i_txn_id,
1657 i_layer_id,
1658 l_inv_layer_id,
1659 i_txn_qty,
1660 i_cost_type,
1661 i_mat_ct_id,
1662 i_avg_rates_id,
1663 i_item_id,
1664 i_txn_qty,
1665 i_txn_action_id,
1666 1,
1667 i_user_id,
1668 i_login_id,
1669 i_req_id,
1670 i_prg_appl_id,
1671 i_prg_id,
1672 i_interorg_rec,
1673 l_err_num,
1674 l_err_code,
1675 l_err_msg);
1676
1677 IF (l_err_num <> 0) THEN
1678 RAISE process_error;
1679 END IF;
1680 END IF;
1681
1682 IF (l_debug = 'Y') THEN
1683 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_inv_layer_id ' ||l_inv_layer_id);
1684 END IF;
1685 /* Check if a layer need to be created */
1686 l_merge := CSTPACHK.LayerMerge_Hook(
1687 i_txn_id => i_txn_id,
1688 o_err_num => l_err_num,
1689 o_err_code => l_err_code,
1690 o_err_msg => l_err_msg
1691 );
1692
1693 IF (l_merge <> 0 AND l_merge <> 1) OR (l_err_num <> 0) THEN
1694 IF l_debug = 'Y' THEN
1695 l_stmt_num := 15;
1696 fnd_file.put_line(
1697 fnd_file.log,
1698 'CSTPACHK.layer_hook errors out with '||
1699 'l_merge ='||l_merge||','||
1700 'l_err_num = '||l_err_num||','||
1701 'l_err_code = '||l_err_code||','||
1702 'l_err_msg = '||l_err_msg
1703 );
1704 END IF;
1705 IF l_err_num = 0 THEN
1706 l_err_num := -1;
1707 END IF;
1708 RAISE process_error;
1709 END IF;
1710
1711 IF (l_merge = 1) AND (l_inv_layer_id <> -1) THEN
1712 /* Check that there is no negative layer other than the last inventory layer that is negative */
1713 l_stmt_num := 55;
1714 SELECT COUNT(*)
1715 INTO l_count
1716 FROM cst_inv_layers cil,
1717 cst_quantity_layers cql
1718 WHERE cql.layer_id = i_layer_id
1719 AND cil.inv_layer_id = l_inv_layer_id
1720 AND cil.layer_quantity < 0
1721 AND cil.layer_quantity > cql.layer_quantity;
1722
1723 IF (l_debug = 'Y') THEN
1724 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_count '||l_count);
1725 END IF;
1726
1727 IF (l_count = 0) THEN
1728 /* Check the type of the current transaction and the transaction that
1729 created the last inventory layer */
1730 l_stmt_num := 60;
1731 SELECT create_transaction_id
1732 INTO l_last_txn_id
1733 FROM cst_inv_layers
1734 WHERE inv_layer_id = l_inv_layer_id;
1735
1736 IF (l_debug = 'Y') THEN
1737 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_last_txn_id '||l_last_txn_id);
1738 END IF;
1739
1740 l_stmt_num := 65;
1741 BEGIN
1742 SELECT mmt.transaction_type_id,
1743 decode(rt2.parent_transaction_id,-1,rt2.transaction_id,rt2.parent_transaction_id)
1744 INTO l_last_txn_type_id,
1745 l_last_rcv_txn_id
1746 FROM mtl_material_transactions mmt,
1747 rcv_transactions rt1,
1748 rcv_transactions rt2
1749 WHERE mmt.transaction_id = l_last_txn_id
1750 AND mmt.rcv_transaction_id = rt1.transaction_id (+)
1751 AND rt1.parent_transaction_id = rt2.transaction_id (+);
1752
1753 IF (l_debug = 'Y') THEN
1754 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_last_txn_type_id '||l_last_txn_type_id);
1755 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_last_rcv_txn_id '||l_last_rcv_txn_id);
1756 END IF;
1757 EXCEPTION
1758 when no_data_found then
1759 l_last_txn_type_id := -1;
1760 l_last_rcv_txn_id := -1;
1761 END;
1762
1763 FND_FILE.PUT_LINE(FND_FILE.LOG,'i_txn_id '||i_txn_id);
1764 l_stmt_num := 70;
1765 SELECT mmt.transaction_type_id,
1766 decode(rt2.parent_transaction_id,-1,rt2.transaction_id,rt2.parent_transaction_id)
1767 INTO l_txn_type_id,
1768 l_rcv_txn_id
1769 FROM mtl_material_transactions mmt,
1770 rcv_transactions rt1,
1771 rcv_transactions rt2
1772 WHERE mmt.transaction_id = i_txn_id
1773 AND mmt.rcv_transaction_id = rt1.transaction_id (+)
1774 AND rt1.parent_transaction_id = rt2.transaction_id (+);
1775
1776 IF (l_debug = 'Y') THEN
1777 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_txn_type_id '||l_txn_type_id);
1778 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_rcv_txn_id '||l_rcv_txn_id);
1779 END IF;
1780
1781 l_stmt_num := 75;
1782 IF ( ( (l_txn_type_id = l_last_txn_type_id)
1783 AND ( (l_txn_type_id = 4)
1784 OR (l_txn_type_id = 8)
1785 OR (l_txn_type_id = 15)
1786 OR (l_txn_type_id = 40)
1787 OR (l_txn_type_id = 41)
1788 OR (l_txn_type_id = 42)))
1789 OR ( ( (l_txn_type_id = 18)
1790 OR (l_txn_type_id = 71))
1791 AND ( (l_last_txn_type_id = 18)
1792 OR (l_last_txn_type_id = 71))
1793 AND (l_rcv_txn_id = l_last_rcv_txn_id))) THEN
1794
1795 IF(l_txn_type_id = 18 OR l_txn_type_id = 71) THEN
1796 /* Check the MOH rates of the two transactions */
1797 l_stmt_num := 80;
1798 SELECT nvl(SUM(actual_cost),0)
1799 INTO l_last_moh
1800 FROM mtl_cst_layer_act_cost_details
1801 WHERE transaction_id = l_last_txn_id
1802 AND organization_id = i_org_id
1803 AND layer_id = i_layer_id
1804 AND inv_layer_id = l_inv_layer_id
1805 AND cost_element_id = 2
1806 AND level_type = 1;
1807
1808 IF (l_debug = 'Y') THEN
1809 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_last_moh '||l_last_moh);
1810 END IF;
1811 l_stmt_num := 85;
1812
1813 SELECT nvl(SUM(actual_cost),0)
1814 INTO l_moh
1815 FROM mtl_cst_layer_act_cost_details
1816 WHERE transaction_id = i_txn_id
1817 AND organization_id = i_org_id
1818 AND layer_id = i_layer_id
1819 AND inv_layer_id = l_inv_layer_id
1820 AND cost_element_id = 2
1821 AND level_type = 1;
1822
1823 IF (l_debug = 'Y') THEN
1824 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_moh '||l_moh);
1825 END IF;
1826
1827 IF (l_last_moh=l_moh) THEN
1828 l_create := 0;
1829 else
1830 l_create := 1;
1831 END IF;
1832
1833 if (l_create = 0) THEN
1834 /* Further check the layer cost of the current transaction and last layer;
1835 layer cost update could happen before the current transaction */
1836 l_stmt_num := 86;
1837
1838
1839 SELECT nvl(SUM(layer_cost),0)
1840 INTO l_last_layer_cost
1841 FROM cst_inv_layers
1842 WHERE organization_id = i_org_id
1843 AND layer_id = i_layer_id
1844 AND inv_layer_id = l_inv_layer_id;
1845
1846
1847
1848
1849 IF (l_debug = 'Y') THEN
1850 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_last_layer_cost '||l_last_layer_cost);
1851 END IF;
1852 l_stmt_num := 87;
1853
1854
1855
1856 SELECT nvl(SUM(layer_cost),0)
1857 INTO l_layer_cost
1858 FROM mtl_cst_layer_act_cost_details
1859 WHERE transaction_id = i_txn_id
1860 AND organization_id = i_org_id
1861 AND layer_id = i_layer_id
1862 AND inv_layer_id = l_inv_layer_id
1863 AND level_type = 1;
1864
1865
1866 IF (l_debug = 'Y') THEN
1867 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_layer_cost '||l_layer_cost);
1868 END IF;
1869
1870
1871 IF (l_last_layer_cost=l_layer_cost) THEN
1872 l_create := 0;
1873 else
1874 l_create := 1;
1875 END IF;
1876 END IF;
1877 ELSE
1878 IF (l_debug = 'Y') THEN
1879 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_actual_cost_table '||l_actual_cost_table);
1880 END IF;
1881 IF (l_actual_cost_table = 'CILCD') THEN
1882 l_create := 0;
1883 END IF;
1884 END IF;
1885 END IF;
1886 END IF;
1887 END IF;
1888
1889 IF (l_debug = 'Y') THEN
1890 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_create '||l_create);
1891 END IF;
1892
1893 IF (l_create = 0) THEN
1894 IF (l_debug = 'Y') THEN
1895 FND_FILE.PUT_LINE(FND_FILE.LOG,'Adding inventory layers ...');
1896 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory Layer Number : ' || to_char(l_inv_layer_id));
1897 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory Layer Quantity : ' || to_char(i_txn_qty));
1898 END IF;
1899
1900 /* Get transaction source ID for the transaction */
1901 l_stmt_num := 86;
1902 SELECT transaction_source_id
1903 INTO l_src_id
1904 FROM mtl_material_transactions
1905 WHERE transaction_id = i_txn_id;
1906
1907 /* Get transaction source name */
1908 l_stmt_num := 89;
1909 l_src_number := get_source_number(i_txn_id,i_txn_src_type,l_src_id);
1910
1911 /* Update last created inventory layer */
1912 l_stmt_num := 90;
1913 UPDATE cst_inv_layers
1914 SET creation_quantity = creation_quantity + i_txn_qty,
1915 layer_quantity = layer_quantity + i_txn_qty,
1916 transaction_source_id = decode(transaction_source_id, l_src_id, l_src_id, null),
1917 transaction_source = decode(transaction_source, l_src_number, l_src_number, null),
1918 last_update_date = sysdate,
1919 last_updated_by = i_user_id,
1920 creation_date = sysdate,
1921 created_by = i_user_id,
1922 last_update_login = i_login_id,
1923 request_id = i_req_id,
1924 program_application_id = i_prg_appl_id,
1925 program_id = i_prg_id,
1926 program_update_date = sysdate
1927 WHERE inv_layer_id = l_inv_layer_id;
1928 ELSE
1929 /* Generate Inv Layer ID */
1930 l_stmt_num := 95;
1931 SELECT cst_inv_layers_s.nextval
1932 INTO l_inv_layer_id
1933 FROM dual;
1934
1935 IF (l_debug = 'Y') THEN
1936 FND_FILE.PUT_LINE(FND_FILE.LOG,'Creating inventory layers ...');
1937 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory Layer Number : ' || to_char(l_inv_layer_id));
1938 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inventory Layer Quantity : ' || to_char(i_txn_qty));
1939 END IF;
1940
1941 /* Update MCLACD entries */
1942 l_stmt_num := 100;
1943 UPDATE mtl_cst_layer_act_cost_details
1944 SET inv_layer_id = l_inv_layer_id
1945 WHERE transaction_id = i_txn_id
1946 AND organization_id = i_org_id
1947 AND layer_id = i_layer_id;
1948
1949 FND_FILE.PUT_LINE(FND_FILE.LOG, sql%rowcount || ' records updated in mclacd for ' || l_inv_layer_id);
1950
1951 /* Get transaction source ID for the transaction */
1952 l_stmt_num := 105;
1953 SELECT transaction_source_id
1954 INTO l_src_id
1955 FROM mtl_material_transactions
1956 WHERE transaction_id = i_txn_id;
1957
1958 /* Get transaction source name */
1959 l_stmt_num := 110;
1960 l_src_number := get_source_number(i_txn_id,i_txn_src_type,l_src_id);
1961
1962 /* Create inventory layer with 0 cost in CST_INV_LAYERS */
1963 l_stmt_num := 115;
1964 INSERT
1965 INTO cst_inv_layers (
1966 layer_id,
1967 inv_layer_id,
1968 organization_id,
1969 inventory_item_id,
1970 creation_quantity,
1971 layer_quantity,
1972 layer_cost,
1973 create_transaction_id,
1974 transaction_source_id,
1975 transaction_action_id,
1976 transaction_source_type_id,
1977 transaction_source,
1978 unburdened_cost,
1979 burden_cost,
1980 last_update_date,
1981 last_updated_by,
1982 creation_date,
1983 created_by,
1984 last_update_login,
1985 request_id,
1986 program_application_id,
1987 program_id,
1988 program_update_date)
1989 VALUES (i_layer_id,
1990 l_inv_layer_id,
1991 i_org_id,
1992 i_item_id,
1993 i_txn_qty,
1994 i_txn_qty,
1995 0,
1996 i_txn_id,
1997 l_src_id,
1998 i_txn_action_id,
1999 i_txn_src_type,
2000 l_src_number,
2001 0,
2002 0,
2003 sysdate,
2004 i_user_id,
2005 sysdate,
2006 i_user_id,
2007 i_login_id,
2008 i_req_id,
2009 i_prg_appl_id,
2010 i_prg_id,
2011 sysdate);
2012
2013 IF (l_debug = 'Y') THEN
2014 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inventory layer created');
2015 END IF;
2016
2017 /* Delete cost details for the inventory layer from CILCD. No rows should
2018 be present. Just a safety check */
2019 l_stmt_num := 120;
2020 DELETE
2021 FROM cst_inv_layer_cost_details
2022 WHERE inv_layer_id = l_inv_layer_id;
2023
2024 /* Copy cost details by cost element and level into CILCD */
2025 l_stmt_num := 125;
2026 INSERT
2027 INTO cst_inv_layer_cost_details (
2028 layer_id,
2029 inv_layer_id,
2030 level_type,
2031 cost_element_id,
2032 layer_cost,
2033 last_update_date,
2034 last_updated_by,
2035 creation_date,
2036 created_by,
2037 last_update_login,
2038 request_id,
2039 program_application_id,
2040 program_id,
2041 program_update_date)
2042 SELECT i_layer_id,
2043 l_inv_layer_id,
2044 mclacd.level_type,
2045 mclacd.cost_element_id,
2046 SUM(mclacd.actual_cost),
2047 sysdate,
2048 i_user_id,
2049 sysdate,
2050 i_user_id,
2051 i_login_id,
2052 i_req_id,
2053 i_prg_appl_id,
2054 i_prg_id,
2055 sysdate
2056 FROM mtl_cst_layer_act_cost_details mclacd
2057 WHERE transaction_id = i_txn_id
2058 AND inv_layer_id = l_inv_layer_id
2059 AND organization_id = i_org_id
2060 GROUP
2061 BY transaction_id,
2062 inv_layer_id,
2063 cost_element_id,
2064 level_type;
2065
2066 FND_FILE.PUT_LINE(FND_FILE.LOG, sql%rowcount || ' records copied from mclacd for ' || l_inv_layer_id);
2067 /* Update layer cost in CIL */
2068 l_stmt_num := 130;
2069 IF (nvl(i_interorg_rec,-1) <> 3) THEN
2070 UPDATE cst_inv_layers
2071 SET layer_cost = (
2072 SELECT SUM(layer_cost)
2073 FROM cst_inv_layer_cost_details
2074 WHERE inv_layer_id = l_inv_layer_id
2075 GROUP
2076 BY inv_layer_id),
2077 (unburdened_cost,burden_cost) = (
2078 SELECT SUM(decode(cost_element_id,
2079 2,decode(level_type,2,layer_cost,0),
2080 layer_cost)),
2081 SUM(decode(cost_element_id,
2082 2,decode(level_type,1,layer_cost,0),
2083 0))
2084 FROM cst_inv_layer_cost_details
2085 WHERE inv_layer_id = l_inv_layer_id
2086 GROUP
2087 BY inv_layer_id)
2088 WHERE layer_id = i_layer_id
2089 AND inv_layer_id = l_inv_layer_id;
2090 END IF;
2091
2092 IF (l_debug = 'Y') THEN
2093 FND_FILE.PUT_LINE(FND_FILE.LOG,'CIL cost updated from CILCD');
2094 END IF;
2095 END IF; /* l_create = 0 */
2096
2097 /* Create cursor to find any negative layers, order in FIFO/LIFO method */
2098 IF (i_txn_qty > 0) THEN
2099 sql_stmt := 'select inv_layer_id, layer_quantity from cst_inv_layers ' ||
2100 'where layer_id = :i and layer_quantity < 0 order by creation_date';
2101
2102 IF (i_cost_method = 6) THEN
2103 sql_stmt := sql_stmt || ' desc,inv_layer_id desc';
2104 ELSE
2105 sql_stmt := sql_stmt || ',inv_layer_id';
2106 END IF;
2107
2108 /* Open cursor, set total available quantity for replenishment */
2109 l_stmt_num := 135;
2110
2111 OPEN negative_layer_cursor FOR sql_stmt USING i_layer_id;
2112
2113 l_qty_available := abs(i_txn_qty);
2114
2115 IF (l_debug = 'Y') then
2116 FND_FILE.PUT_LINE(
2117 FND_FILE.LOG,
2118 'Qty available for replenishment : ' || to_char(l_qty_available));
2119 END IF;
2120
2121 /* Loop while positive quantity is available, get the next negative layer and
2122 insert rows into MCLACD for replenishment */
2123 l_stmt_num := 140;
2124
2125 WHILE (l_qty_available > 0) LOOP
2126
2127 /* If no negative layers are found, exit While LOOP */
2128 l_stmt_num := 145;
2129 FETCH negative_layer_cursor into l_neg_layer_id, l_neg_layer_qty;
2130 EXIT WHEN negative_layer_cursor%NOTFOUND;
2131
2132 /* Quantity to be replenished depends on available quantity. */
2133 IF ((l_qty_available+l_neg_layer_qty) > 0) THEN
2134 /* Layer can be completely replenished */
2135 l_qty := abs(l_neg_layer_qty);
2136 ELSE
2137 l_qty := l_qty_available;
2138 END IF;
2139
2140 /* Insert into MCLACD for the negative layer, using actual cost from
2141 positive layer and layer cost from the negative layer
2142 Verify Insert_mclacd( ) code for layer costs and actual costs */
2143 l_stmt_num := 150;
2144 insert_mclacd (
2145 i_txn_id,
2146 i_org_id,
2147 i_item_id,
2148 i_layer_id,
2149 l_neg_layer_id,
2150 l_qty,
2151 i_txn_action_id,
2152 i_user_id,
2153 i_login_id,
2154 i_req_id,
2155 i_prg_id,
2156 i_prg_appl_id,
2157 'CILCD',
2158 'CILCD',
2159 l_inv_layer_id,
2160 'REPLENISH',
2161 l_err_num,
2162 l_err_code,
2163 l_err_msg);
2164
2165 IF (l_err_num <> 0) THEN
2166 raise process_error;
2167 END IF;
2168
2169 /* Update quantity for the negative layer and the quantity available
2170 for replenishment */
2171 IF (nvl(i_interorg_rec,-1) <> 3) THEN
2172 l_stmt_num := 140;
2173 UPDATE cst_inv_layers
2174 SET layer_quantity = l_neg_layer_qty + l_qty
2175 WHERE inv_layer_id = l_neg_layer_id;
2176 END IF;
2177
2178 l_qty_available := l_qty_available - l_qty;
2179 END LOOP;
2180
2181 CLOSE negative_layer_cursor;
2182
2183 /* For the current layer */
2184 IF (l_qty_available <> i_txn_qty) THEN
2185
2186 /* Obtain quantity used in replenishment */
2187 l_qty := i_txn_qty - l_qty_available;
2188
2189 /* Insert into MCLACD using negative quantity for current layer */
2190 l_stmt_num := 155;
2191
2192 insert_mclacd(
2193 i_txn_id,
2194 i_org_id,
2195 i_item_id,
2196 i_layer_id,
2197 l_inv_layer_id,
2198 -1*l_qty,
2199 i_txn_action_id,
2200 i_user_id,
2201 i_login_id,
2202 i_req_id,
2203 i_prg_id,
2204 i_prg_appl_id,
2205 'CILCD',
2206 'CILCD',
2207 l_inv_layer_id,
2208 'UPDATE',
2209 l_err_num,
2210 l_err_code,
2211 l_err_msg);
2212
2213 IF (l_err_num <> 0) THEN
2214 RAISE process_error;
2215 END IF;
2216
2217 /* Update layer quantity for current layer in CIL */
2218 l_stmt_num := 160;
2219 /*ADDED IF CONDITION FOR #BUG6722228*/
2220 IF (l_neg_layer_iD=l_inv_layer_id) THEN
2221
2222 IF (nvl(i_interorg_rec,-1) <> 3) THEN
2223
2224 UPDATE cst_inv_layers
2225 SET layer_quantity=layer_quantity-l_qty
2226 WHERE inv_layer_id = l_inv_layer_id;
2227 END IF;
2228
2229 ELSE
2230
2231 IF (nvl(i_interorg_rec,-1) <> 3) THEN
2232 UPDATE cst_inv_layers
2233 SET layer_quantity = l_qty_available
2234 WHERE inv_layer_id = l_inv_layer_id;
2235 END IF;
2236 END IF;/* END OF #BUG6722228 */
2237 END IF; /* l_qty_available <> i_txn_qty */
2238 END IF; /* i_txn_qty > 0 */
2239 EXCEPTION
2240
2241 WHEN process_error THEN
2242 o_err_num := l_err_num;
2243 o_err_code := l_err_code;
2244 o_err_msg := l_err_msg;
2245
2246 WHEN OTHERS THEN
2247 ROLLBACK;
2248 o_err_num := SQLCODE;
2249 o_err_msg := 'CSTPLENG.create_layers (' ||
2250 to_char(l_stmt_num) || '): ' ||
2251 substr(SQLERRM,1,200);
2252
2253 END create_layers;
2254
2255 ----------------------------------------------------------------------------
2256 -- PROCEDURE --
2257 -- consume_layers --
2258 -- --
2259 -- DESCRIPTION --
2260 -- * Consume inventory layer, insert into MCLACD using inventory layer --
2261 -- cost. If cost hook or MCTCD is present, then drive to variance --
2262 -- * Uses inventory layer table to insert costs and update layer qty --
2263 -- --
2264 -- PURPOSE: --
2265 -- consumption of inventory layers --
2266 -- --
2267 -- PARAMETERS: --
2268 -- i_txn_qty : primary quantity --
2269 -- i_interorg_rec : interorg shimpment (= 0), --
2270 -- interorg receipt (= 1) --
2271 -- subinv transfer with no layer change (= 3) --
2272 -- otherwise (= null) --
2273 -- --
2274 -- HISTORY: --
2275 -- 04/20/00 Anitha B Created --
2276 ----------------------------------------------------------------------------
2277 PROCEDURE consume_layers(
2278 i_org_id IN NUMBER,
2279 i_txn_id IN NUMBER,
2280 i_layer_id IN NUMBER,
2281 i_item_id IN NUMBER,
2282 i_txn_qty IN NUMBER,
2283 i_cost_method IN NUMBER,
2284 i_txn_src_type IN NUMBER,
2285 i_txn_action_id IN NUMBER,
2286 i_cost_hook IN NUMBER,
2287 i_interorg_rec IN NUMBER,
2288 i_cost_type IN NUMBER,
2289 i_mat_ct_id IN NUMBER,
2290 i_avg_rates_id IN NUMBER,
2291 i_exp_flag IN NUMBER,
2292 i_user_id IN NUMBER,
2293 i_login_id IN NUMBER,
2294 i_req_id IN NUMBER,
2295 i_prg_appl_id IN NUMBER,
2296 i_prg_id IN NUMBER,
2297 o_err_num OUT NOCOPY NUMBER,
2298 o_err_code OUT NOCOPY VARCHAR2,
2299 o_err_msg OUT NOCOPY VARCHAR2
2300 ) IS
2301 l_inv_layer_table cst_layer_tbl_type := cst_layer_tbl_type();
2302 l_layer_hook NUMBER;
2303 l_src_id NUMBER;
2304 l_txn_cost_exists NUMBER;
2305 l_actual_cost_table VARCHAR2(10);
2306 l_err_num NUMBER;
2307 l_err_code VARCHAR2(240);
2308 l_err_msg VARCHAR2(240);
2309 l_stmt_num NUMBER;
2310 l_count NUMBER;
2311 l_exp_item NUMBER;
2312 process_error EXCEPTION;
2313 l_layers_exist NUMBER;
2314 l_debug VARCHAR2(80);
2315 l_subinv VARCHAR2(80);
2316 l_expsub NUMBER;
2317 l_inv_layer_exists NUMBER:=0;
2318
2319 BEGIN
2320 l_stmt_num := 0;
2321 l_err_num := 0;
2322 l_err_code := '';
2323 l_err_msg := '';
2324 l_layers_exist := 0;
2325 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
2326
2327 l_stmt_num := 5;
2328 if (l_debug = 'Y') then
2329 FND_FILE.PUT_LINE(FND_FILE.LOG,'Consuming inventory layers from CG layer : ' || to_char(i_layer_id));
2330 end if;
2331 select count(*)
2332 into l_layers_exist
2333 from cst_inv_layers
2334 where layer_id = i_layer_id;
2335
2336 if (l_layers_exist = 0) then
2337 if (l_debug = 'Y') then
2338 FND_FILE.PUT_LINE(FND_FILE.LOG,'Creating negative layer ...');
2339 end if;
2340
2341 /* Bug 2325297
2342 Create Layers is called with i_interorg_rec parameter as NULL
2343 This is due to the fact that layers have to be created if layers
2344 do not exist in the LIFO/FIFO Organization.
2345 But if the the transaction is a sending transaction, then we
2346 do not earn material overhead (which is also taken care of in create_layers()
2347 The value NULL for i_interorg_rec ensures that layers are created
2348 in the sending organization but it does not earn MOH
2349 */
2350 create_layers(i_org_id,
2351 i_txn_id,
2352 i_layer_id,
2353 i_item_id,
2354 i_txn_qty,
2355 i_cost_method,
2356 i_txn_src_type,
2357 i_txn_action_id,
2358 i_cost_hook,
2359 NULL, -- i_interorg_rec: Create Layers always if it a sending txn
2360 i_cost_type,
2361 i_mat_ct_id,
2362 i_avg_rates_id,
2363 i_exp_flag,
2364 i_user_id,
2365 i_login_id,
2366 i_req_id,
2367 i_prg_appl_id,
2368 i_prg_id,
2369 l_err_num,
2370 l_err_code,
2371 l_err_msg);
2372 return;
2373 end if;
2374
2375 /* If expense item, then insert into MCACD using current costs. No inventory
2376 layer consumed or created */
2377 l_stmt_num := 6;
2378 select decode(inventory_asset_flag,'Y',0,1)
2379 into l_exp_item
2380 from mtl_system_items
2381 where inventory_item_id = i_item_id
2382 and organization_id = i_org_id;
2383
2384 l_stmt_num := 7;
2385 if (l_exp_item = 1) then
2386 if (l_debug = 'Y') then
2387 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for expense item...');
2388 end if;
2389 select count(*) into l_count
2390 from cst_layer_cost_details
2391 where layer_id = i_layer_id;
2392
2393 if (l_count > 0) then
2394 insert into mtl_cst_actual_cost_details (
2395 transaction_id,
2396 organization_id,
2397 layer_id,
2398 cost_element_id,
2399 level_type,
2400 transaction_action_id,
2401 last_update_date,
2402 last_updated_by,
2403 creation_date,
2404 created_by,
2405 last_update_login,
2406 request_id,
2407 program_application_id,
2408 program_id,
2409 program_update_date,
2410 inventory_item_id,
2411 actual_cost,
2412 prior_cost,
2413 new_cost,
2414 insertion_flag,
2415 variance_amount,
2416 user_entered)
2417 select i_txn_id,
2418 i_org_id,
2419 i_layer_id,
2420 clcd.cost_element_id,
2421 clcd.level_type,
2422 i_txn_action_id,
2423 sysdate,
2424 i_user_id,
2425 sysdate,
2426 i_user_id,
2427 i_login_id,
2428 i_req_id,
2429 i_prg_appl_id,
2430 i_prg_id,
2431 sysdate,
2432 i_item_id,
2433 clcd.item_cost,
2434 clcd.item_cost,
2435 clcd.item_cost,
2436 'N',
2437 0,
2438 'N'
2439 from cst_layer_cost_details clcd
2440 where layer_id = i_layer_id;
2441 else
2442 insert into mtl_cst_actual_cost_details (
2443 transaction_id,
2444 organization_id,
2445 layer_id,
2446 cost_element_id,
2447 level_type,
2448 transaction_action_id,
2449 last_update_date,
2450 last_updated_by,
2451 creation_date,
2452 created_by,
2453 last_update_login,
2454 request_id,
2455 program_application_id,
2456 program_id,
2457 program_update_date,
2458 inventory_item_id,
2459 actual_cost,
2460 prior_cost,
2461 new_cost,
2462 insertion_flag,
2463 variance_amount,
2464 user_entered)
2465 select i_txn_id,
2466 i_org_id,
2467 i_layer_id,
2468 1,
2469 1,
2470 i_txn_action_id,
2471 sysdate,
2472 i_user_id,
2473 sysdate,
2474 i_user_id,
2475 i_login_id,
2476 i_req_id,
2477 i_prg_appl_id,
2478 i_prg_id,
2479 sysdate,
2480 i_item_id,
2481 0,
2482 0,
2483 0,
2484 'Y',
2485 0,
2486 'N'
2487 from dual;
2488 end if;
2489 return;
2490 end if;
2491
2492 l_stmt_num := 10;
2493 l_layer_hook := CSTPACHK.layer_hook(
2494 i_org_id,
2495 i_txn_id,
2496 i_layer_id,
2497 i_cost_method,
2498 i_user_id,
2499 i_login_id,
2500 i_req_id,
2501 i_prg_appl_id,
2502 i_prg_id,
2503 l_err_num,
2504 l_err_code,
2505 l_err_msg);
2506 IF l_err_num <> 0 THEN
2507 IF l_debug = 'Y' THEN
2508 l_stmt_num := 15;
2509 fnd_file.put_line(
2510 fnd_file.log,
2511 'CSTPACHK.layer_hook errors out with '||
2512 'l_err_num = '||l_err_num||','||
2513 'l_err_code = '||l_err_code||','||
2514 'l_err_msg = '||l_err_msg
2515 );
2516 END IF;
2517 RAISE process_error;
2518 END IF;
2519 l_stmt_num := 20;
2520 if ((l_layer_hook > 0) OR ((i_txn_action_id = 1) and (i_txn_src_type = 1))
2521 OR ((i_txn_action_id = 29) and (i_txn_qty < 0))
2522 OR ((i_txn_action_id = 32) and (i_txn_src_type = 5))) then
2523
2524 l_expsub := 0;
2525
2526 select transaction_source_id
2527 into l_src_id
2528 from mtl_material_transactions
2529 where transaction_id = i_txn_id;
2530
2531
2532 select subinventory_code
2533 into l_subinv
2534 from mtl_material_transactions
2535 where transaction_id = i_txn_id;
2536
2537 select decode(asset_inventory, 1, 0, 1)
2538 into l_expsub
2539 from mtl_secondary_inventories
2540 where organization_id = i_org_id
2541 and secondary_inventory_name = l_subinv;
2542
2543 if (l_debug = 'Y') then
2544 FND_FILE.PUT_LINE(FND_FILE.LOG,'Layer specific consumption...');
2545 FND_FILE.PUT_LINE(FND_FILE.LOG,'Layer hook : ' || to_char(l_layer_hook));
2546 FND_FILE.PUT_LINE(FND_FILE.LOG,'Source ID : ' || to_char(l_src_id));
2547 end if;
2548 if (l_expsub = 1) then
2549 IF (l_debug = 'Y') THEN
2550 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for the Item Coming from Exp Sub Inv ...');
2551 END IF;
2552
2553 l_stmt_num := 21;
2554 SELECT COUNT(*)
2555 INTO l_count
2556 FROM mtl_cst_txn_cost_details
2557 WHERE transaction_id = i_txn_id
2558 AND organization_id = i_org_id;
2559
2560 IF (l_count > 0) THEN
2561 l_stmt_num := 22;
2562 INSERT
2563 INTO mtl_cst_actual_cost_details (
2564 transaction_id,
2565 organization_id,
2566 layer_id,
2567 cost_element_id,
2568 level_type,
2569 transaction_action_id,
2570 last_update_date,
2571 last_updated_by,
2572 creation_date,
2573 created_by,
2574 last_update_login,
2575 request_id,
2576 program_application_id,
2577 program_id,
2578 program_update_date,
2579 inventory_item_id,
2580 actual_cost,
2581 prior_cost,
2582 new_cost,
2583 insertion_flag,
2584 variance_amount,
2585 user_entered)
2586 SELECT i_txn_id,
2587 i_org_id,
2588 i_layer_id,
2589 ctcd.cost_element_id,
2590 ctcd.level_type,
2591 i_txn_action_id,
2592 sysdate,
2593 i_user_id,
2594 sysdate,
2595 i_user_id,
2596 i_login_id,
2597 i_req_id,
2598 i_prg_appl_id,
2599 i_prg_id,
2600 sysdate,
2601 ctcd.inventory_item_id,
2602 ctcd.transaction_cost,
2603 0,
2604 0,
2605 'Y',
2606 0,
2607 'N'
2608 FROM mtl_cst_txn_cost_details ctcd
2609 WHERE ctcd.transaction_id = i_txn_id
2610 AND ctcd.organization_id = i_org_id ;
2611
2612 else
2613 l_stmt_num := 23;
2614 INSERT
2615 INTO mtl_cst_actual_cost_details (
2616 transaction_id,
2617 organization_id,
2618 layer_id,
2619 cost_element_id,
2620 level_type,
2621 transaction_action_id,
2622 last_update_date,
2623 last_updated_by,
2624 creation_date,
2625 created_by,
2626 last_update_login,
2627 request_id,
2628 program_application_id,
2629 program_id,
2630 program_update_date,
2631 inventory_item_id,
2632 actual_cost,
2633 prior_cost,
2634 new_cost,
2635 insertion_flag,
2636 variance_amount,
2637 user_entered)
2638 SELECT i_txn_id,
2639 i_org_id,
2640 i_layer_id,
2641 1,
2642 1,
2643 i_txn_action_id,
2644 sysdate,
2645 i_user_id,
2646 sysdate,
2647 i_user_id,
2648 i_login_id,
2649 i_req_id,
2650 i_prg_appl_id,
2651 i_prg_id,
2652 sysdate,
2653 i_item_id,
2654 0,
2655 0,
2656 0,
2657 'Y',
2658 0,
2659 'N'
2660 FROM dual;
2661 end if; /* l_count > 0 */
2662 return;
2663 else
2664 get_layers_consumed(
2665 i_txn_qty => i_txn_qty,
2666 i_cost_method => i_cost_method,
2667 i_layer_id => i_layer_id,
2668 consume_mode => 'SPECIFIC',
2669 i_layer_hook => l_layer_hook,
2670 i_src_id => l_src_id,
2671 i_txn_id => i_txn_id,
2672 l_inv_layer_table => l_inv_layer_table,
2673 o_err_num => l_err_num,
2674 o_err_code => l_err_code,
2675 o_err_msg => l_err_msg
2676 );
2677 end if; /* l_expsub =1 */
2678 else
2679 l_expsub := 0;
2680
2681 if (i_txn_action_id in (1, 27, 33, 34)) then
2682 l_stmt_num := 25;
2683 select subinventory_code
2684 into l_subinv
2685 from mtl_material_transactions
2686 where transaction_id = i_txn_id;
2687
2688 select decode(asset_inventory, 1, 0, 1)
2689 into l_expsub
2690 from mtl_secondary_inventories
2691 where organization_id = i_org_id
2692 and secondary_inventory_name = l_subinv;
2693 end if;
2694
2695 if (l_expsub = 1) then
2696 /* For WIP issue/return transactions: cost needed for consumption from
2697 expense subinv for asset item should come from only the next layer that
2698 would be consumed if it were from asset subinv */
2699 l_stmt_num := 27;
2700 if (l_debug = 'Y') then
2701 FND_FILE.PUT_LINE(FND_FILE.LOG,'Consumption from exp subinv');
2702 end if;
2703
2704 get_layers_consumed(
2705 i_txn_id => i_txn_id,
2706 i_txn_qty => i_txn_qty,
2707 i_cost_method => i_cost_method,
2708 i_layer_id => i_layer_id,
2709 consume_mode => 'EXPSUB',
2710 l_inv_layer_table => l_inv_layer_table,
2711 o_err_num => l_err_num,
2712 o_err_code => l_err_code,
2713 o_err_msg => l_err_msg
2714 );
2715 else
2716 l_stmt_num := 30;
2717 if (l_debug = 'Y') then
2718 fnd_file.put_line(fnd_file.log, 'Regular consumption ');
2719 end if;
2720
2721 get_layers_consumed(
2722 i_txn_id => i_txn_id,
2723 i_txn_qty => i_txn_qty,
2724 i_cost_method => i_cost_method,
2725 i_layer_id => i_layer_id,
2726 consume_mode => 'NORMAL',
2727 l_inv_layer_table => l_inv_layer_table,
2728 o_err_num => l_err_num,
2729 o_err_code => l_err_code,
2730 o_err_msg => l_err_msg
2731 );
2732 end if; /* l_expsub = 1 */
2733 end if;
2734 if (l_err_num <> 0) then
2735 raise process_error;
2736 end if;
2737
2738 l_stmt_num := 40;
2739 if (i_cost_hook = 1) then
2740 l_actual_cost_table := 'MCACD';
2741 else
2742 select count(*)
2743 into l_txn_cost_exists
2744 from mtl_cst_txn_cost_details
2745 where transaction_id = i_txn_id
2746 and organization_id = i_org_id;
2747
2748 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2749 BUG 12751466
2750 In case when prior onhand quantity and item cost is zero, if
2751 wip component issue transaction for an asset item is performed
2752 in asset subinventory,if inventory layer doesnot exists in
2753 table 'cst_inv_layer_cost_details' then mtl_cst_layer_actual_cost_details
2754 is inserted with zero cost against this level Material element.
2755 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
2756
2757 select count(*)
2758 into l_inv_layer_exists
2759 from cst_inv_layer_cost_details
2760 where layer_id=i_layer_id;
2761
2762 if (l_txn_cost_exists > 0) then
2763 l_actual_cost_table := 'MCTCD';
2764 elsif(l_inv_layer_exists>0) then
2765 l_actual_cost_table := 'CILCD';
2766 else
2767 l_actual_cost_table := 'NONE';
2768 end if;
2769 end if;
2770
2771 IF l_inv_layer_table.COUNT >0 THEN
2772 For i IN l_inv_layer_table.FIRST..l_inv_layer_table.LAST
2773 LOOP
2774 l_stmt_num := 50;
2775 insert_mclacd(i_txn_id,
2776 i_org_id,
2777 i_item_id,
2778 i_layer_id,
2779 l_inv_layer_table(i).inv_layer_id,
2780 -1*l_inv_layer_table(i).layer_quantity,
2781 i_txn_action_id,
2782 i_user_id,
2783 i_login_id,
2784 i_req_id,
2785 i_prg_id,
2786 i_prg_appl_id,
2787 l_actual_cost_table,
2788 'CILCD',
2789 l_inv_layer_table(i).inv_layer_id,
2790 'CONSUME',
2791 l_err_num,
2792 l_err_code,
2793 l_err_msg);
2794
2795 /* If layer is driven negative, then apply_layer_material_ovhd should not be called again, insert_mclacd return value of 999 in such a case */
2796 if (l_err_num <> 0) then
2797 if (l_err_num = 999) then
2798 l_err_num := 0;
2799 else
2800 raise process_error;
2801 end if;
2802 else
2803
2804 if ((i_exp_flag <> 1)
2805 AND
2806 ((i_txn_action_id = 27 and i_txn_src_type = 1) /* PO Receipt */
2807 OR
2808 (i_txn_action_id = 1 and i_txn_src_type = 1) /* RTV */
2809 OR
2810 (i_txn_action_id = 29 and i_txn_src_type = 1) /* Delivery Adj */
2811 OR
2812 (i_txn_action_id = 31 and i_txn_src_type = 5) /*WIP completion*/
2813 OR
2814 (i_txn_action_id = 6) /* Change of ownership */
2815
2816 OR
2817 (i_txn_action_id = 32 and i_txn_src_type = 5) /* Assembly completion*/
2818 OR
2819 (i_interorg_rec = 1) /*Interorg receipt*/
2820 )
2821 ) then
2822 l_stmt_num :=60;
2823 if (l_debug = 'Y') then
2824 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling apply_layer_material_ovhd...');
2825 end if;
2826 apply_layer_material_ovhd(
2827 i_org_id,
2828 i_txn_id,
2829 i_layer_id,
2830 l_inv_layer_table(i).inv_layer_id,
2831 l_inv_layer_table(i).layer_quantity,
2832 i_cost_type,
2833 i_mat_ct_id,
2834 i_avg_rates_id,
2835 i_item_id,
2836 i_txn_qty,
2837 i_txn_action_id,
2838 1,
2839 i_user_id,
2840 i_login_id,
2841 i_req_id,
2842 i_prg_appl_id,
2843 i_prg_id,
2844 i_interorg_rec, --bug 2280515
2845 l_err_num,
2846 l_err_code,
2847 l_err_msg
2848 );
2849
2850 if (l_err_num <> 0) then
2851 raise process_error;
2852 end if;
2853 end if;
2854 end if; /* l_err_num = 999 */
2855 l_stmt_num := 70;
2856 if ((nvl(i_interorg_rec,-1) <> 3) and (i_exp_flag <> 1)) then
2857 update cst_inv_layers
2858 set layer_quantity = nvl(layer_quantity,0)-l_inv_layer_table(i).layer_quantity
2859 where inv_layer_id = l_inv_layer_table(i).inv_layer_id;
2860 if (l_debug = 'Y') then
2861 FND_FILE.PUT_LINE(FND_FILE.LOG,'CIL.layer_qty changed by ' || to_char(l_inv_layer_table(i).layer_quantity));
2862 end if;
2863 end if;
2864 END LOOP;
2865 END IF; /* IF l_inv_layer_table.COUNT >0 THEN */
2866 EXCEPTION
2867 when process_error then
2868 o_err_num := l_err_num;
2869 o_err_code := l_err_code;
2870 o_err_msg := l_err_msg;
2871 when others then
2872 rollback;
2873 o_err_num := SQLCODE;
2874 o_err_msg := 'CSTPLENG.consume_layers (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
2875
2876 END consume_layers;
2877
2878 ----------------------------------------------------------------------------
2879 -- PROCEDURE --
2880 -- get_layers_consumed --
2881 -- --
2882 -- DESCRIPTION --
2883 -- This procedure decides which inventory layers need to be consumed --
2884 -- for the specified issue transaction. The output of this procedure is --
2885 -- a table of inventory layer ids and the quantity that should be --
2886 -- consumed from each layer. --
2887 -- --
2888 -- PARAMETERS: --
2889 -- i_txn_qty : quantity that needs to be consumed in primary --
2890 -- UOM --
2891 -- i_cost_method : cost method of the organization (5 for FIFO, 6 --
2892 -- for LIFO) --
2893 -- i_layer_id : cost group layer id --
2894 -- consume_mode : consumption mode (EXPSUB for Issues of asset --
2895 -- items from expense subinventories, SPECIFIC for --
2896 -- layer_hook, return to receiving, correction, --
2897 -- assembly return, NORMAL for all others) --
2898 -- i_layer_hook : specific custom layer that should be consumed --
2899 -- i_src_id : source id (PO Receipt for return to receiving, --
2900 -- corrections, Job for assembly return, NULL for --
2901 -- all others) --
2902 -- i_txn_id : issue transaction id --
2903 -- l_inv_layer_table : inventory layers that should be consumed --
2904 ----------------------------------------------------------------------------
2905 PROCEDURE get_layers_consumed (
2906 i_txn_qty IN NUMBER,
2907 i_cost_method IN NUMBER,
2908 i_layer_id IN NUMBER,
2909 consume_mode IN VARCHAR2,
2910 i_layer_hook IN NUMBER,
2911 i_src_id IN NUMBER,
2912 i_txn_id IN NUMBER,
2913 l_inv_layer_table IN OUT NOCOPY cst_layer_tbl_type,
2914 o_err_num OUT NOCOPY NUMBER,
2915 o_err_code OUT NOCOPY VARCHAR2,
2916 o_err_msg OUT NOCOPY VARCHAR2
2917 )
2918 IS
2919 l_stmt_num NUMBER;
2920 l_debug VARCHAR2(80);
2921 l_required_qty NUMBER;
2922 l_custom_layer NUMBER;
2923 l_source_id NUMBER;
2924 l_inv_layer_id NUMBER;
2925 l_pos_layer_exist NUMBER;
2926 l_layers_hook NUMBER;
2927 l_rtr NUMBER;
2928 l_rtr_txn_id NUMBER;
2929 l_custom_layers CSTPACHK.inv_layer_tbl;
2930 l_layers_list VARCHAR2(2000);
2931 sql_stmt VARCHAR2(2000);
2932 l_inv_layer_rec cst_layer_rec_type;
2933 inv_layer_cursor LayerCurType;
2934 l_err_num NUMBER;
2935 l_err_code VARCHAR2(240);
2936 l_err_msg VARCHAR2(240);
2937 process_error EXCEPTION;
2938 BEGIN
2939 l_stmt_num := 0;
2940 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
2941 l_debug := 'Y';
2942 l_required_qty := ABS(i_txn_qty);
2943 l_custom_layer := NVL(i_layer_hook,-1);
2944 l_source_id := NVL(i_src_id,-1);
2945 l_rtr_txn_id := 0;
2946 l_custom_layers := CSTPACHK.inv_layer_tbl();
2947 l_err_num := 0;
2948 l_err_code := '';
2949 l_err_msg := '';
2950 IF l_debug = 'Y' THEN
2951 fnd_file.put_line(
2952 fnd_file.log,
2953 'Entering get_layers_consumed for transaction '||i_txn_id||
2954 ' and a required quantity of '||l_required_qty||
2955 ' with a consumption mode of '||consume_mode
2956 );
2957 END IF;
2958 l_stmt_num := 5;
2959
2960 /* For issues of asset items from expense subinventories, we don't consume any layers
2961 Instead, we just need to get a reference cost from the earliest / latest layer */
2962
2963 IF consume_mode = 'EXPSUB' THEN
2964 IF l_debug = 'Y' THEN
2965 fnd_file.put_line(fnd_file.log,'EXPSUB consumption');
2966 END IF;
2967 IF i_cost_method = 5 THEN
2968 l_stmt_num := 10;
2969 SELECT MIN(inv_layer_id)
2970 INTO l_inv_layer_id
2971 FROM cst_inv_layers
2972 WHERE layer_id = i_layer_id
2973 AND layer_quantity > 0;
2974 ELSE
2975 l_stmt_num := 15;
2976 SELECT MAX(inv_layer_id)
2977 INTO l_inv_layer_id
2978 FROM cst_inv_layers
2979 WHERE layer_id = i_layer_id
2980 AND layer_quantity > 0;
2981 END IF;
2982 /* If no positive layers exist, pick the latest layer */
2983 IF l_inv_layer_id IS NULL THEN
2984 l_stmt_num := 20;
2985 SELECT MAX(inv_layer_id)
2986 INTO l_inv_layer_id
2987 FROM cst_inv_layers
2988 WHERE layer_id = i_layer_id;
2989 END IF;
2990 l_inv_layer_rec.inv_layer_id := l_inv_layer_id;
2991 l_inv_layer_rec.layer_quantity := l_required_qty;
2992 l_stmt_num := 25;
2993 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
2994 l_required_qty := 0;
2995 RETURN;
2996 END IF;
2997
2998 /* For issues from asset subinventories, consume the layers in the following order
2999 1. Positive quantity in the layer specified by the layer hook
3000 2. Drive the layer specified by the layer hook negative only if there are no
3001 other positive layers
3002 3. Positive quantity from the layers specified by the layers hook in the order
3003 that they are specified
3004 4. Positive quantity from the layer that was created for the delivery that this
3005 return / correction correspond to
3006 5. Positive quantity from the layers that was created for the deliveries for
3007 the same PO or completions from the same job in FIFO/LIFO manner
3008 6. Drive the earliest / latest layer that was created for the deliveries for the
3009 same PO or completions from the same job negative only if there are
3010 no other positive layers
3011 7. Positive quantity from all layers in FIFO/LIFO manner
3012 8. Drive the overall earliest / latest layer negative
3013
3014 1 and 2 are applicable only when layer hook is used.
3015 3 is applicable only when layers hook is used.
3016 4 is applicable only for returns to receiving / corrections.
3017 5 and 6 are applicable only for returns to receiving, corrections and assembly
3018 returns. */
3019
3020 /* 1. Positive quantity in the layer specified by the layer hook */
3021 IF (l_custom_layer > 0) AND (l_required_qty > 0) THEN
3022 IF l_debug = 'Y' THEN
3023 fnd_file.put_line(fnd_file.log,'Trying custom layer '||l_custom_layer);
3024 END IF;
3025 l_stmt_num := 30;
3026 SELECT inv_layer_id, layer_quantity
3027 INTO l_inv_layer_rec.inv_layer_id,l_inv_layer_rec.layer_quantity
3028 FROM cst_inv_layers
3029 WHERE inv_layer_id = l_custom_layer -- inventory layer id exists
3030 AND layer_id = i_layer_id; -- correct organization, item, cost group
3031 IF l_inv_layer_rec.layer_quantity > 0 THEN
3032 IF l_required_qty < l_inv_layer_rec.layer_quantity THEN
3033 l_inv_layer_rec.layer_quantity := l_required_qty;
3034 END IF;
3035 IF l_debug = 'Y' THEN
3036 fnd_file.put_line(
3037 fnd_file.log,
3038 'Using custom layer '||l_custom_layer||' for '||l_inv_layer_rec.layer_quantity
3039 );
3040 END IF;
3041 l_required_qty := l_required_qty - l_inv_layer_rec.layer_quantity;
3042 l_stmt_num := 35;
3043 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3044 END IF;
3045 END IF;
3046 /* End of 1 */
3047
3048 /* 2. Drive the layer specified by the layer hook negative only if there are no
3049 other positive layers */
3050 IF (l_custom_layer > 0) AND (l_required_qty > 0) THEN
3051 IF l_debug = 'Y' THEN
3052 fnd_file.put_line(fnd_file.log,'Driving custom layer '||l_custom_layer||' negative?');
3053 END IF;
3054 l_stmt_num := 40;
3055 SELECT count(*)
3056 INTO l_pos_layer_exist
3057 FROM cst_inv_layers
3058 WHERE layer_id = i_layer_id
3059 AND inv_layer_id <> l_custom_layer
3060 AND layer_quantity > 0;
3061 IF l_pos_layer_exist = 0 THEN
3062 IF l_debug = 'Y' THEN
3063 fnd_file.put_line(fnd_file.log,'Driving custom layer '||l_custom_layer||' negative');
3064 END IF;
3065 l_inv_layer_rec.inv_layer_id := l_custom_layer;
3066 l_inv_layer_rec.layer_quantity := l_required_qty;
3067 l_required_qty := 0;
3068 l_stmt_num := 45;
3069 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3070 END IF;
3071 END IF;
3072 /* End of 2 */
3073
3074 /* 3. Positive quantity from the layers specified in the layers hook in the order that they are specified */
3075 IF l_required_qty > 0 THEN
3076 IF l_debug = 'Y' THEN
3077 fnd_file.put_line(fnd_file.log,'Trying custom layers');
3078 END IF;
3079 l_stmt_num := 50;
3080 CSTPACHK.layers_hook (
3081 i_txn_id => i_txn_id,
3082 i_required_qty => l_required_qty,
3083 i_cost_method => i_cost_method,
3084 o_custom_layers => l_custom_layers,
3085 o_err_num => l_err_num,
3086 o_err_code => l_err_code,
3087 o_err_msg => l_err_msg
3088 );
3089 IF l_err_num <> 0 THEN
3090 fnd_file.put_line(fnd_file.log,'Error in calling CSTPACHK.layers_hook');
3091 RAISE process_error;
3092 END IF;
3093 l_layers_hook := 0;
3094 l_layers_list := '(-1';
3095 IF l_debug = 'Y' THEN
3096 fnd_file.put_line(fnd_file.log,'There are '||l_custom_layers.COUNT||' custom layers');
3097 END IF;
3098 IF l_custom_layers.COUNT > 0 THEN
3099 FOR i IN l_custom_layers.FIRST..l_custom_layers.LAST LOOP
3100 EXIT WHEN l_required_qty = 0;
3101 IF l_debug = 'Y' THEN
3102 fnd_file.put_line(
3103 fnd_file.log,
3104 'Trying to consume '||l_custom_layers(i).layer_quantity||
3105 ' from custom layer '||l_custom_layers(i).inv_layer_id
3106 );
3107 END IF;
3108 l_stmt_num := 55;
3109 BEGIN
3110 SELECT inv_layer_id, l_custom_layers(i).layer_quantity
3111 INTO l_inv_layer_rec.inv_layer_id, l_inv_layer_rec.layer_quantity
3112 FROM cst_inv_layers
3113 WHERE inv_layer_id = l_custom_layers(i).inv_layer_id -- valid inventory layer id
3114 AND layer_id = i_layer_id -- valid org, item, cost group
3115 AND layer_quantity >=
3116 l_custom_layers(i).layer_quantity -- enough quantity
3117 AND l_custom_layers(i).layer_quantity > 0; -- positive quanttiy
3118 EXCEPTION
3119 WHEN NO_DATA_FOUND THEN
3120 l_err_num := -1;
3121 l_err_msg := 'Custom layer '||l_custom_layers(i).inv_layer_id||
3122 ' and quantity '||l_custom_layers(i).layer_quantity||
3123 ' is not valid';
3124 fnd_file.put_line(
3125 fnd_file.log, l_err_msg
3126 );
3127 RAISE process_error;
3128 END;
3129 -- ignore the layer if it has been specified by the layer hook to avoid double counting.
3130 IF l_inv_layer_rec.inv_layer_id <> l_custom_layer THEN
3131 IF l_inv_layer_rec.layer_quantity > l_required_qty THEN
3132 l_inv_layer_rec.layer_quantity := l_required_qty;
3133 END IF;
3134 l_required_qty := l_required_qty - l_inv_layer_rec.layer_quantity;
3135 l_stmt_num := 60;
3136 IF l_debug = 'Y' THEN
3137 fnd_file.put_line(
3138 fnd_file.log,
3139 'Using custom layer '||l_custom_layers(i).inv_layer_id||
3140 ' for '||l_inv_layer_rec.layer_quantity
3141 );
3142 END IF;
3143 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3144 l_layers_hook := 1;
3145 l_layers_list := l_layers_list || ',' || l_inv_layer_rec.inv_layer_id;
3146 END IF;
3147 END LOOP;
3148 END IF;
3149 l_layers_list := l_layers_list || ')';
3150 END IF;
3151 /* End of 3 */
3152
3153 /* 4. Positive quantity from the layer that was created for the delivery that this
3154 return / correction corresponds to */
3155 IF (consume_mode = 'SPECIFIC') AND (i_src_id IS NOT NULL) AND (l_required_qty > 0) then
3156 IF l_debug = 'Y' THEN
3157 fnd_file.put_line(fnd_file.log,'Trying original delivery');
3158 END IF;
3159 -- check if the current transaction is a return to receiving or correction
3160 l_stmt_num := 65;
3161 SELECT COUNT(*)
3162 INTO l_rtr
3163 FROM mtl_material_transactions
3164 WHERE transaction_id = i_txn_id
3165 AND transaction_action_id in (1, 29)
3166 AND transaction_source_type_id = 1;
3167 -- if it is, try to first consume the inv layer created by the receipt
3168 -- that this return is performed against
3169 IF l_rtr = 1 THEN
3170 l_stmt_num := 70;
3171 BEGIN
3172 SELECT mmt_del.transaction_id
3173 INTO l_rtr_txn_id
3174 FROM mtl_material_transactions mmt_del,
3175 mtl_material_transactions mmt_rtr,
3176 rcv_transactions rt_rtr
3177 WHERE mmt_del.rcv_transaction_id = rt_rtr.parent_transaction_id
3178 AND rt_rtr.transaction_id = mmt_rtr.rcv_transaction_id
3179 AND mmt_rtr.transaction_id = i_txn_id;
3180 EXCEPTION
3181 WHEN OTHERS THEN
3182 IF (l_debug = 'Y') THEN
3183 FND_FILE.PUT_LINE(
3184 FND_FILE.LOG,
3185 'No delivery is found for transaction ' || i_txn_id
3186 );
3187 END IF;
3188 END;
3189 l_stmt_num := 75;
3190 sql_stmt := 'SELECT inv_layer_id, layer_quantity'
3191 ||' FROM cst_inv_layers'
3192 ||' WHERE create_transaction_id = :i'
3193 ||' AND layer_quantity > 0'
3194 ||' AND inv_layer_id <> :j';
3195 IF l_layers_hook > 0 THEN
3196 l_stmt_num := 80;
3197 sql_stmt := sql_stmt || ' AND inv_layer_id NOT IN '|| l_layers_list;
3198 END IF;
3199 IF l_debug = 'Y' THEN
3200 fnd_file.put_line(
3201 fnd_file.log,
3202 'Using SQL '||sql_stmt||' with '||l_rtr_txn_id||','||l_custom_layer
3203 );
3204 END IF;
3205 OPEN inv_layer_cursor FOR sql_stmt USING l_rtr_txn_id, l_custom_layer;
3206 l_stmt_num := 85;
3207 populate_layer_table(
3208 l_inv_layer_table => l_inv_layer_table,
3209 inv_layer_cursor => inv_layer_cursor,
3210 i_qty_required => l_required_qty,
3211 o_err_num => l_err_num,
3212 o_err_code => l_err_code,
3213 o_err_msg => l_err_msg
3214 );
3215 CLOSE inv_layer_cursor;
3216 END IF; -- l_rtr = 1
3217 END IF;
3218 /* End of 4 */
3219
3220 /* 5. Positive quantity from the layers that was created for the deliveries for
3221 the same PO or completions from the same job in FIFO/LIFO manner */
3222 IF (consume_mode = 'SPECIFIC') AND (i_src_id IS NOT NULL) AND (l_required_qty > 0) THEN
3223 IF l_debug = 'Y' THEN
3224 fnd_file.put_line(fnd_file.log,'Trying other layers with the same source');
3225 END IF;
3226 l_stmt_num := 90;
3227 sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers'
3228 ||' WHERE layer_id = :i AND transaction_source_id = :j AND layer_quantity > 0 '
3229 ||' AND create_transaction_id <> :k AND inv_layer_id <> :l';
3230 IF l_layers_hook > 0 THEN
3231 l_stmt_num := 95;
3232 sql_stmt := sql_stmt || ' AND inv_layer_id NOT IN ' || l_layers_list;
3233 END IF;
3234 IF i_cost_method = 6 THEN
3235 l_stmt_num := 100;
3236 sql_stmt := sql_stmt || ' ORDER BY creation_date DESC, inv_layer_id DESC';
3237 ELSE
3238 l_stmt_num := 105;
3239 sql_stmt := sql_stmt || ' ORDER BY creation_date, inv_layer_id';
3240 END IF;
3241 IF l_debug = 'Y' THEN
3242 fnd_file.put_line(
3243 fnd_file.log,
3244 'Using SQL '||sql_stmt||' with '||i_layer_id||','||l_source_id||
3245 ','||l_rtr_txn_id||','||l_custom_layer
3246 );
3247 END IF;
3248 OPEN inv_layer_cursor FOR sql_stmt USING i_layer_id,l_source_id,l_rtr_txn_id,l_custom_layer;
3249 l_stmt_num := 110;
3250 populate_layer_table(
3251 l_inv_layer_table => l_inv_layer_table,
3252 inv_layer_cursor => inv_layer_cursor,
3253 i_qty_required => l_required_qty,
3254 o_err_num => l_err_num,
3255 o_err_code => l_err_code,
3256 o_err_msg => l_err_msg
3257 );
3258 CLOSE inv_layer_cursor;
3259 END IF;
3260 /* End of 5 */
3261
3262 /* 6. Drive the earliest / latest layer that was created for the deliveries for the
3263 same PO or completions from the same job negative only if there are
3264 no other positive layers */
3265 IF (consume_mode = 'SPECIFIC') AND (i_src_id IS NOT NULL) AND (l_required_qty > 0) THEN
3266 IF l_debug = 'Y' THEN
3267 fnd_file.put_line(
3268 fnd_file.log,
3269 'Driving earliest/latest layer with the same source negative?'
3270 );
3271 END IF;
3272 l_stmt_num := 115;
3273 sql_stmt := 'SELECT inv_layer_id, layer_quantity FROM cst_inv_layers'
3274 ||' WHERE layer_id = :i AND inv_layer_id <> :j'
3275 ||' AND NVL(transaction_source_id,-2) <> :k'
3276 ||' AND layer_quantity > 0';
3277 IF l_layers_hook > 0 THEN
3278 l_stmt_num := 120;
3279 sql_stmt := sql_stmt || ' AND inv_layer_id NOT IN ' || l_layers_list;
3280 END IF;
3281 IF l_debug = 'Y' THEN
3282 fnd_file.put_line(
3283 fnd_file.log,
3284 'Using SQL '||sql_stmt||' with '||i_layer_id||','||l_custom_layer||
3285 ','||l_source_id
3286 );
3287 END IF;
3288 OPEN inv_layer_cursor FOR sql_stmt USING i_layer_id,l_custom_layer,l_source_id;
3289 FETCH inv_layer_cursor INTO l_inv_layer_rec.inv_layer_id, l_inv_layer_rec.layer_quantity;
3290 IF inv_layer_cursor%NOTFOUND THEN
3291 IF i_cost_method = 5 THEN
3292 l_stmt_num := 125;
3293 SELECT MAX(inv_layer_id)
3294 INTO l_inv_layer_rec.inv_layer_id
3295 FROM cst_inv_layers
3296 WHERE layer_id = i_layer_id
3297 AND transaction_source_id = l_source_id;
3298 ELSE
3299 l_stmt_num := 130;
3300 SELECT MIN(inv_layer_id)
3301 INTO l_inv_layer_rec.inv_layer_id
3302 FROM cst_inv_layers
3303 WHERE layer_id = i_layer_id
3304 AND transaction_source_id = l_source_id;
3305 END IF;
3306 IF l_inv_layer_rec.inv_layer_id IS NOT NULL THEN
3307 IF l_debug = 'Y' THEN
3308 fnd_file.put_line(
3309 fnd_file.log,
3310 'Driving earliest/latest layer with the same source negative'
3311 );
3312 END IF;
3313 l_inv_layer_rec.layer_quantity := l_required_qty;
3314 l_stmt_num := 135;
3315 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3316 l_required_qty := 0;
3317 END IF;
3318 END IF;
3319 CLOSE inv_layer_cursor;
3320 END IF;
3321 /* End of 6 */
3322
3323 /* 7. Positive quantity from all layers in FIFO/LIFO manner */
3324 IF l_required_qty > 0 THEN
3325 IF l_debug = 'Y' THEN
3326 fnd_file.put_line(fnd_file.log,'General consumption');
3327 END IF;
3328 l_stmt_num := 140;
3329 sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i'
3330 ||' AND inv_layer_id <> :j AND NVL(transaction_source_id,-2) <> :k'
3331 ||' AND layer_quantity > 0';
3332 l_stmt_num := 145;
3333 IF l_layers_hook > 0 THEN
3334 sql_stmt := sql_stmt || ' AND inv_layer_id NOT IN '|| l_layers_list;
3335 END IF;
3336 IF i_cost_method = 6 THEN
3337 l_stmt_num := 150;
3338 sql_stmt := sql_stmt || ' ORDER BY creation_date DESC, inv_layer_id DESC';
3339 ELSE
3340 l_stmt_num := 155;
3341 sql_stmt := sql_stmt || ' ORDER BY creation_date, inv_layer_id';
3342 END IF;
3343 IF l_debug = 'Y' THEN
3344 fnd_file.put_line(
3345 fnd_file.log,
3346 'Using SQL '||sql_stmt||' with '||i_layer_id||','||l_custom_layer||
3347 ','||l_source_id
3348 );
3349 END IF;
3350 OPEN inv_layer_cursor FOR sql_stmt USING i_layer_id,l_custom_layer,l_source_id;
3351 l_stmt_num := 160;
3352 populate_layer_table(
3353 l_inv_layer_table => l_inv_layer_table,
3354 inv_layer_cursor => inv_layer_cursor,
3355 i_qty_required => l_required_qty,
3356 o_err_num => l_err_num,
3357 o_err_code => l_err_code,
3358 o_err_msg => l_err_msg
3359 );
3360 CLOSE inv_layer_cursor;
3361 END IF;
3362 /* End of 7 */
3363
3364 /* 8. Drive the overall earliest / latest layer negative */
3365 IF l_required_qty > 0 THEN
3366 IF l_debug = 'Y' THEN
3367 fnd_file.put_line(fnd_file.log,'Driving earliest/latest layer negative');
3368 END IF;
3369 IF l_debug = 'Y' THEN
3370 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_neg_qty ' || to_char(l_required_qty));
3371 END IF;
3372 l_stmt_num := 165;
3373 sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i';
3374 IF i_cost_method = 5 THEN
3375 sql_stmt := sql_stmt || ' ORDER BY creation_date DESC,inv_layer_id DESC';
3376 ELSE
3377 sql_stmt := sql_stmt || ' ORDER BY creation_date,inv_layer_id';
3378 END IF;
3379 IF l_debug = 'Y' THEN
3380 fnd_file.put_line(fnd_file.log,'Using SQL '||sql_stmt||' with '||i_layer_id);
3381 END IF;
3382 OPEN inv_layer_cursor FOR sql_stmt USING i_layer_id;
3383 FETCH inv_layer_cursor into l_inv_layer_rec.inv_layer_id,l_inv_layer_rec.layer_quantity;
3384 l_inv_layer_rec.layer_quantity := l_required_qty;
3385 l_stmt_num := 170;
3386 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3387 CLOSE inv_layer_cursor;
3388 END IF;
3389 /* End of 8 */
3390
3391 EXCEPTION
3392 when process_error then
3393 o_err_num := l_err_num;
3394 o_err_code := l_err_code;
3395 o_err_msg := l_err_msg;
3396 when others then
3397 rollback;
3398 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLCODE ||' ' ||to_char(l_stmt_num)||' '||substr(SQLERRM,1,200));
3399 o_err_num := SQLCODE;
3400 o_err_msg := 'CSTPLENG.get_layers_consumed (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3401 END get_layers_consumed;
3402
3403 ----------------------------------------------------------------------------
3404 -- PROCEDURE --
3405 -- populate_layer_table --
3406 -- --
3407 -- DESCRIPTION --
3408 -- * This procedure loops through the records fetched by the layer --
3409 -- cursor (IN parameter), and populates the PL/SQL table with the --
3410 -- inventory layer ID and the quantity that needs to be consumed --
3411 -- from that layer --
3412 -- * If no layers are fetched, the procedure issues a return --
3413 -- --
3414 -- PURPOSE: --
3415 -- a single function to loop through records fetched by the inventory --
3416 -- layer cursor and populate the PL/SQL table with the inv layer ID --
3417 -- and the quantity to be consuemd from that layer --
3418 -- --
3419 -- PARAMETERS: --
3420 -- l_inv_layer_table : PL/SQL parameter that is populated --
3421 -- inv_layer_cursor : cursor that is used to fetch inventory layers --
3422 -- i_qty_required : total quantity left to be consumed --
3423 ----------------------------------------------------------------------------
3424 PROCEDURE populate_layer_table(l_inv_layer_table IN OUT NOCOPY cst_layer_tbl_type,
3425 inv_layer_cursor IN LayerCurType,
3426 i_qty_required IN OUT NOCOPY NUMBER,
3427 o_err_num OUT NOCOPY NUMBER,
3428 o_err_code OUT NOCOPY VARCHAR2,
3429 o_err_msg OUT NOCOPY VARCHAR2)
3430 IS
3431 l_inv_layer_rec cst_layer_rec_type;
3432 l_stmt_num NUMBER;
3433 l_err_num NUMBER;
3434 l_err_code VARCHAR2(240);
3435 l_err_msg VARCHAR2(240);
3436 process_error EXCEPTION;
3437 BEGIN
3438 l_stmt_num := 0;
3439 l_err_num := 0;
3440 l_err_code := '';
3441 l_err_msg := '';
3442
3443 while (i_qty_required > 0) LOOP
3444 l_stmt_num := 20;
3445 FETCH inv_layer_cursor into l_inv_layer_rec.inv_layer_id,
3446 l_inv_layer_rec.layer_quantity;
3447 EXIT WHEN inv_layer_cursor%NOTFOUND;
3448
3449 if (i_qty_required < l_inv_layer_rec.layer_quantity) then
3450 l_stmt_num := 30;
3451 l_inv_layer_rec.layer_quantity := i_qty_required;
3452 end if;
3453
3454 l_stmt_num := 40;
3455 i_qty_required := i_qty_required - l_inv_layer_rec.layer_quantity;
3456
3457 l_stmt_num := 50;
3458 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3459
3460 END LOOP;
3461
3462 EXCEPTION
3463 when process_error then
3464 o_err_num := l_err_num;
3465 o_err_code := l_err_code;
3466 o_err_msg := l_err_msg;
3467 when others then
3468 rollback;
3469 o_err_num := SQLCODE;
3470 o_err_msg := 'CSTPLENG.populate_layer_table (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3471 END populate_layer_table;
3472
3473 ----------------------------------------------------------------------------
3474 -- PROCEDURE --
3475 -- insert_record --
3476 -- --
3477 -- DESCRIPTION --
3478 -- This procedure extends the PL/SQL table and inserts a new record --
3479 -- --
3480 -- PURPOSE: --
3481 -- This procedure extends the PL/SQL table and inserts a new record --
3482 -- --
3483 -- PARAMETERS: --
3484 -- l_inv_layer_rec : record to be inserted --
3485 -- l_inv_layer_table : PL/SQL table into which record is inserted --
3486 ----------------------------------------------------------------------------
3487 PROCEDURE insert_record(l_inv_layer_rec IN cst_layer_rec_type,
3488 l_inv_layer_table IN OUT NOCOPY cst_layer_tbl_type,
3489 o_err_num OUT NOCOPY NUMBER,
3490 o_err_code OUT NOCOPY VARCHAR2,
3491 o_err_msg OUT NOCOPY VARCHAR2) IS
3492 l_stmt_num NUMBER;
3493 l_err_num NUMBER;
3494 l_err_code VARCHAR2(240);
3495 l_err_msg VARCHAR2(240);
3496 l_next_record NUMBER;
3497 process_error EXCEPTION;
3498
3499 BEGIN
3500 l_stmt_num := 0;
3501 l_err_num := 0;
3502 l_err_code := '';
3503 l_err_msg := '';
3504
3505 l_stmt_num := 10;
3506 l_next_record := nvl(l_inv_layer_table.LAST,0);
3507
3508 l_stmt_num := 20;
3509 l_inv_layer_table.extend;
3510
3511 l_stmt_num := 30;
3512 l_next_record := nvl(l_inv_layer_table.LAST,0);
3513
3514 l_stmt_num := 40;
3515 l_inv_layer_table(l_next_record).inv_layer_id := l_inv_layer_rec.inv_layer_id;
3516 l_inv_layer_table(l_next_record).layer_quantity := l_inv_layer_rec.layer_quantity;
3517 EXCEPTION
3518 when process_error then
3519 o_err_num := l_err_num;
3520 o_err_code := l_err_code;
3521 o_err_msg := l_err_msg;
3522 when others then
3523 rollback;
3524 o_err_num := SQLCODE;
3525 o_err_msg := 'CSTPLENG.insert_record (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3526 END insert_record;
3527
3528
3529 ---------------------------------------------------------------------------
3530 -- PROCEDURE apply_layer_material_ovh
3531 -- created by Dieu-Thuong Le 21-Apr-2000
3532
3533 -- This procedure will compute material overheads based on the rates
3534 -- defined in the rate cost type specified for the current costing org.
3535 -- The computed movh will be applied to this level material overhead of
3536 -- the specified inventory layer.
3537 -- This procedure will insert MACS rows for each applicable material
3538 -- overhead and insert or update MCLACD.
3539 -- Inserting MCLACD: populate both layer_cost and actual_cost with the
3540 -- total costs of all MACS rows created for tranx.
3541 -- Updating MCLACD: add MACS costs to actual_cost, layer_cost will
3542 -- not be touched.
3543 -- Assumption: when this procedure is called, MCLACD material row should
3544 -- already be inserted for the transaction.
3545 ---------------------------------------------------------------------------
3546 procedure apply_layer_material_ovhd(
3547 I_ORG_ID IN NUMBER,
3548 I_TXN_ID IN NUMBER,
3549 I_LAYER_ID IN NUMBER,
3550 I_INV_LAYER_ID IN NUMBER,
3551 I_LAYER_QTY IN NUMBER,
3552 I_COST_TYPE IN NUMBER,
3553 I_MAT_CT_ID IN NUMBER,
3554 I_AVG_RATES_ID IN NUMBER,
3555 I_ITEM_ID IN NUMBER,
3556 I_TXN_QTY IN NUMBER,
3557 I_TXN_ACTION_ID IN NUMBER,
3558 I_LEVEL IN NUMBER,
3559 I_USER_ID IN NUMBER,
3560 I_LOGIN_ID IN NUMBER,
3561 I_REQ_ID IN NUMBER,
3562 I_PRG_APPL_ID IN NUMBER,
3563 I_PRG_ID IN NUMBER,
3564 I_INTERORG_REC IN NUMBER, --bug 2280515:anjgupta
3565 O_Err_Num OUT NOCOPY NUMBER,
3566 O_Err_Code OUT NOCOPY VARCHAR2,
3567 O_Err_Msg OUT NOCOPY VARCHAR2
3568 ) IS
3569 l_mat_ovhds NUMBER;
3570 l_item_cost NUMBER;
3571 l_res_id NUMBER;
3572 l_err_num NUMBER;
3573 l_err_code VARCHAR2(240);
3574 l_err_msg VARCHAR2(240);
3575 l_stmt_num NUMBER;
3576 overhead_error EXCEPTION;
3577 avg_rates_no_ovhd EXCEPTION;
3578 l_mclacd_ovhd NUMBER;
3579 l_ovhd_cost NUMBER;
3580 l_macs_ovhd NUMBER;
3581 l_elemental_visible VARCHAR2(1);
3582 l_from_org NUMBER;
3583 l_to_org NUMBER;
3584 l_txn_org_id NUMBER;
3585 l_txfr_org_id NUMBER;
3586 l_txn_qty NUMBER;
3587 l_txn_type_id NUMBER;
3588 l_debug VARCHAR2(80);
3589
3590 /* moh variables */
3591 l_earn_moh NUMBER;
3592 l_return_status VARCHAR2(1);
3593 l_msg_count NUMBER;
3594 l_msg_data VARCHAR2(240);
3595 moh_rules_error EXCEPTION;
3596 l_default_MOH_subelement NUMBER;-------------------Bug 3959770
3597
3598
3599 BEGIN
3600 -- initialize local variables
3601 l_err_num := 0;
3602 l_err_code := '';
3603 l_err_msg := '';
3604 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
3605 l_earn_moh := 1;
3606 l_return_status := fnd_api.g_ret_sts_success;
3607 l_msg_count := 0;
3608
3609 /* BUG 3959770*/
3610 /* Get the Default MOH sub element of the organization*/
3611
3612 select DEFAULT_MATL_OVHD_COST_ID
3613 into l_default_MOH_subelement
3614 from mtl_parameters
3615 where organization_id= I_ORG_ID;
3616
3617
3618 -- Find out if there are any material overhead rows for the layer
3619 -- which have actual cost value.
3620
3621 if(l_debug = 'Y') then
3622 fnd_file.put_line(fnd_file.log, 'In apply_layer_material_ovhd!!!!');
3623 end if;
3624
3625 l_stmt_num := 10;
3626 /* Changes for MOH Absorption Rules */
3627
3628 cst_mohRules_pub.apply_moh(
3629 1.0,
3630 p_organization_id => i_org_id,
3631 p_earn_moh =>l_earn_moh,
3632 p_txn_id => i_txn_id,
3633 p_item_id => i_item_id,
3634 x_return_status => l_return_status,
3635 x_msg_count => l_msg_count,
3636 x_msg_data => l_msg_data);
3637
3638 IF l_return_status <> FND_API.g_ret_sts_success THEN
3639
3640 CST_UTILITY_PUB.writelogmessages
3641 ( p_api_version => 1.0,
3642 p_msg_count => l_msg_count,
3643 p_msg_data => l_msg_data,
3644 x_return_status => l_return_status);
3645 RAISE moh_rules_error;
3646 END IF;
3647
3648 IF(l_earn_moh = 0) THEN
3649 fnd_file.put_line(fnd_file.log, '--Material Overhead Absorption Overidden--'); ELSE
3650
3651 l_stmt_num := 11;
3652
3653 select count(*)
3654 into l_mat_ovhds
3655 from mtl_cst_layer_act_cost_details
3656 where transaction_id = i_txn_id
3657 and organization_id = i_org_id
3658 and layer_id = i_layer_id -- cost group layer
3659 and inv_layer_id = i_inv_layer_id -- inventory layer
3660 and cost_element_id = 2
3661 and level_type = decode(i_level,1,1,level_type)
3662 and actual_cost > 0;
3663
3664 l_stmt_num := 15;
3665
3666 select organization_id, transfer_organization_id, primary_quantity
3667 into l_txn_org_id, l_txfr_org_id, l_txn_qty
3668 from mtl_material_transactions
3669 where transaction_id = i_txn_id;
3670
3671 -- Determine the from and to org for this transaction.
3672 if (i_txn_action_id = 21) then -- intransit shipment
3673 l_from_org := l_txn_org_id;
3674 l_to_org := l_txfr_org_id;
3675 elsif (i_txn_action_id = 12) then -- intransit receipt
3676 l_from_org := l_txfr_org_id;
3677 l_to_org := l_txn_org_id;
3678 elsif (i_txn_action_id =3 and l_txn_qty <0) then -- direct org transfer
3679 l_from_org := l_txn_org_id;
3680 l_to_org := l_txfr_org_id;
3681 else
3682 l_from_org := l_txfr_org_id;
3683 l_to_org := l_txn_org_id;
3684 end if;
3685
3686
3687 l_stmt_num := 20;
3688 -- do elemental visibility check for interorg transfer
3689 if (i_txn_action_id in (12,21,3)) then
3690 select NVL(elemental_visibility_enabled,'N')
3691 into l_elemental_visible
3692 from mtl_interorg_parameters
3693 where from_organization_id = l_from_org
3694 and to_organization_id = l_to_org;
3695 end if;
3696
3697 -- Until we can support landed cost, i.e. freight, duty, etc... for PO receipt,
3698 -- we can assume that there should be no actual cost in MCLACD overhead rows
3699 -- at this time, UNLESS it is an interorg transaction.
3700
3701 if not ((i_txn_action_id in (12,21,3)) and (l_elemental_visible = 'Y')) then
3702 if (l_mat_ovhds > 0) then
3703 raise overhead_error;
3704 end if;
3705 end if;
3706
3707 -- Since RTV or Assembly Return transactions can conceivably have multiple MCLACD
3708 -- movh rows, we should check if MACS rows have already been inserted for the
3709 -- transaction. If so, there is no need to insert MACS again,
3710 -- we just have to insert or update MCLACD later.
3711
3712 l_stmt_num := 25;
3713
3714 select count(*)
3715 into l_macs_ovhd
3716 from mtl_actual_cost_subelement
3717 where transaction_id = i_txn_id
3718 and organization_id = i_org_id
3719 and layer_id = i_layer_id
3720 and cost_element_id = 2
3721 and level_type = decode (i_level, 1,1,level_type);
3722
3723 if l_macs_ovhd <= 0 then /* inserting MACS */
3724 if (i_mat_ct_id <> i_cost_type) then -- this is the common scenario since the
3725 -- seeded cost type for FIFO/LIFO should
3726 -- not be the rate cost type
3727
3728 l_stmt_num := 30;
3729
3730 -- Compute item cost of layer. This will be used to calculate
3731 -- the material overhead of 'total value' basis (basis_type = 5)
3732
3733 select nvl(sum(actual_cost),0)
3734 into l_item_cost
3735 from mtl_cst_layer_act_cost_details
3736 where transaction_id = i_txn_id
3737 and organization_id = i_org_id
3738 and layer_id = i_layer_id
3739 and inv_layer_id = i_inv_layer_id;
3740
3741 -- Ensure that resource_id is NOT NULL in CICD
3742 l_stmt_num := 32;
3743 select count(*)
3744 into l_res_id
3745 from cst_item_cost_details cicd
3746 where inventory_item_id = i_item_id
3747 and organization_id = i_org_id
3748 and cost_type_id = i_mat_ct_Id
3749 and basis_type in (1,2,5,6)
3750 and cost_element_id = 2
3751 and level_type = decode (i_level, 1,1,level_type)
3752 and resource_id IS NULL;
3753
3754 if (l_res_id > 0) then /*Changed this if block and inserted the update statement
3755 instead of raising the exception due to bugg 3959770*/
3756
3757 if (l_default_MOH_subelement is NOT NULL) then
3758 update CST_ITEM_COST_DETAILS
3759 set resource_id = l_default_MOH_subelement
3760 where inventory_item_id = i_item_id
3761 and organization_id = i_org_id
3762 and cost_type_id = i_mat_ct_Id
3763 and basis_type in (1,2,5,6)
3764 and cost_element_id = 2
3765 and level_type = decode (i_level, 1,1,level_type)
3766 and resource_id IS NULL;
3767 else
3768 raise avg_rates_no_ovhd;
3769 end if;
3770 end if;
3771
3772
3773
3774 l_stmt_num := 35;
3775
3776 if(l_debug = 'Y') then
3777 fnd_file.put_line(fnd_file.log, 'Inserting into MACS');
3778 end if;
3779
3780 Insert into mtl_actual_cost_subelement(
3781 transaction_id,
3782 organization_id,
3783 layer_id,
3784 cost_element_id,
3785 level_type,
3786 resource_id,
3787 last_update_date,
3788 last_updated_by,
3789 creation_date,
3790 created_by,
3791 last_update_login,
3792 request_id,
3793 program_application_id,
3794 program_id,
3795 program_update_date,
3796 actual_cost,
3797 user_entered)
3798 select i_txn_id,
3799 i_org_id,
3800 i_layer_id,
3801 cicd.cost_element_id,
3802 cicd.level_type,
3803 cicd.resource_id,
3804 sysdate,
3805 i_user_id,
3806 sysdate,
3807 i_user_id,
3808 i_login_id,
3809 i_req_id,
3810 i_prg_appl_id,
3811 i_prg_id,
3812 sysdate,
3813 decode(cicd.basis_type, 1, cicd.usage_rate_or_amount,
3814 2, cicd.usage_rate_or_amount/abs(i_txn_qty),
3815 5, cicd.usage_rate_or_amount * l_item_cost,
3816 6, cicd.usage_rate_or_amount * cicd.basis_factor,0),
3817 'N'
3818 from cst_item_cost_details cicd
3819 where inventory_item_id = i_item_id
3820 and organization_id = i_org_id
3821 and cost_type_id = i_mat_ct_Id
3822 and basis_type in (1,2,5,6)
3823 and cost_element_id = 2
3824 and level_type = decode(i_level, 1,1,level_type);
3825
3826 else /* material overhead cost type is average cost type */
3827 -- In this case we will charge the material overhead in the average
3828 -- cost type using the first material overhead in the average rates
3829 -- cost type. This function will error out if material overhead
3830 -- exists in average cost type and none is defined in the average rates
3831 -- cost type.
3832
3833 l_stmt_num := 40;
3834
3835 select count(*)
3836 into l_mat_ovhds
3837 from cst_layer_cost_details
3838 where layer_id = i_layer_id
3839 and cost_element_id = 2
3840 and level_type = 1;
3841
3842 if (l_mat_ovhds >0 ) then /* material overhead exists in the seeded
3843 cost type */
3844 l_stmt_num := 45;
3845 select count(*)
3846 into l_res_id
3847 from cst_item_cost_details
3848 where cost_type_id = i_avg_rates_id
3849 and inventory_item_id = i_item_id
3850 and organization_id = i_org_id;
3851
3852 if (l_res_id > 0) then
3853 l_stmt_num := 50;
3854 select resource_id
3855 into l_res_id
3856 from cst_item_cost_details
3857 where cost_type_id = i_avg_rates_id
3858 and inventory_item_id = i_item_id
3859 and organization_id = i_org_id
3860 and cost_element_id = 2
3861 and level_type = 1
3862 and rownum = 1;
3863 end if;
3864 /* Changed this check and included the elsif block which inserts the resource
3865 id instead of throwing the exception Bug 3959770*/
3866
3867 if (l_res_id = 0) then
3868 raise avg_rates_no_ovhd;
3869 elsif (l_res_id is NULL) then
3870 if (l_default_MOH_subelement IS NOT NULL) then
3871 l_res_id := l_default_MOH_subelement;
3872
3873 update cst_item_cost_details
3874 set resource_id = l_default_MOH_subelement
3875 where cost_type_id = i_avg_rates_id
3876 and inventory_item_id = i_item_id
3877 and organization_id = i_org_id
3878 and cost_element_id = 2
3879 and level_type = 1
3880 and resource_id IS NULL
3881 and rownum =1;
3882 else
3883 raise avg_rates_no_ovhd;
3884 end if;
3885 end if;
3886
3887
3888 l_stmt_num := 55;
3889 if(l_debug = 'Y') then
3890 fnd_file.put_line(fnd_file.log, 'Inserting into MACS');
3891 end if;
3892
3893 Insert into mtl_actual_cost_subelement(
3894 transaction_id,
3895 organization_id,
3896 layer_id,
3897 cost_element_id,
3898 level_type,
3899 resource_id,
3900 last_update_date,
3901 last_updated_by,
3902 creation_date,
3903 created_by,
3904 last_update_login,
3905 request_id,
3906 program_application_id,
3907 program_id,
3908 program_update_date,
3909 actual_cost,
3910 user_entered)
3911 select i_txn_id,
3912 i_org_id,
3913 i_layer_id,
3914 clcd.cost_element_id,
3915 clcd.level_type,
3916 l_res_id,
3917 sysdate,
3918 i_user_id,
3919 sysdate,
3920 i_user_id,
3921 i_login_id,
3922 i_req_id,
3923 i_prg_appl_id,
3924 i_prg_id,
3925 sysdate,
3926 clcd.item_cost,
3927 'N'
3928 from cst_layer_cost_details clcd
3929 where layer_id = i_layer_id
3930 and cost_element_id = 2
3931 and level_type = 1;
3932 end if;
3933 end if;
3934 end if; /* end of inserting MACS */
3935
3936 -- check again for existence of MACS. This time load count into l_mat_ovhds.
3937
3938 l_stmt_num := 60;
3939
3940 select count(*)
3941 into l_mat_ovhds
3942 from mtl_actual_cost_subelement
3943 where transaction_id = i_txn_id
3944 and organization_id = i_org_id
3945 and layer_id = i_layer_id
3946 and cost_element_id = 2
3947 and level_type = decode(i_level, 1,1,level_type);
3948
3949 if l_debug = 'Y' then
3950 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.l_mat_ovhds = '
3951 || to_char(l_mat_ovhds)
3952 || ' , stmt '
3953 || to_char(l_stmt_num));
3954 end if;
3955
3956 l_stmt_num := 65;
3957
3958 -- check if there is data in MCLACD (material overhead) for this layer.
3959 select count(*)
3960 into l_mclacd_ovhd
3961 from mtl_cst_layer_act_cost_details mclacd
3962 where transaction_id = i_txn_id
3963 and organization_id = i_org_id
3964 and layer_id = i_layer_id
3965 and inv_layer_id = i_inv_layer_id
3966 and cost_element_id = 2
3967 and level_type = decode(i_level,1,1,level_type);
3968
3969 if l_debug = 'Y' then
3970 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.mclacd_ovhd = '
3971 || to_char(l_mclacd_ovhd)
3972 || ' , stmt '
3973 || to_char(l_stmt_num));
3974 end if;
3975
3976 -- MACS exists :add or modify MCLACD
3977 -- No data in MACS, then we do not need to do anything.
3978
3979 if (l_mat_ovhds > 0) then /* MACS exists */
3980 -- If there is data in MCLACD then do an update, adding the
3981 -- sum of MACS.actual cost to the existing cost in mclacd.
3982 -- Otherwise, insert a row in MCLACD.
3983
3984 l_stmt_num := 70;
3985 select sum(actual_cost)
3986 into l_ovhd_cost
3987 from mtl_actual_cost_subelement
3988 where transaction_id = i_txn_id
3989 and organization_id = i_org_id
3990 and layer_id = i_layer_id
3991 and cost_element_id = 2;
3992
3993 if l_debug = 'Y' then
3994 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.l_ovhd_cost = '
3995 || to_char(l_ovhd_cost)
3996 || ' ,stmt '
3997 || to_char(l_stmt_num));
3998 end if;
3999
4000
4001 if (l_mclacd_ovhd > 0) then /* mclacd exists */
4002 l_stmt_num := 72;
4003 select transaction_type_id
4004 into l_txn_type_id
4005 from mtl_material_transactions
4006 where transaction_id = i_txn_id;
4007
4008 /* Bug 2280515 :anjgupta
4009 The variance_amount is zero in the case of interorg receipt transactions.
4010 Updating in a seperate if-else loop to prevent use of decode statements.
4011 */
4012 if(i_interorg_rec = 1) then
4013
4014 l_stmt_num := 75;
4015
4016 update mtl_cst_layer_act_cost_details mclacd
4017 set mclacd.actual_cost = nvl(mclacd.actual_cost, 0) + l_ovhd_cost,
4018 mclacd.layer_cost = nvl(mclacd.layer_cost,0) + l_ovhd_cost,
4019 mclacd.variance_amount = 0,
4020 mclacd.payback_variance_amount = 0
4021 where mclacd.transaction_id = i_txn_id
4022 and mclacd.organization_id = i_org_id
4023 and mclacd.layer_id = i_layer_id
4024 and mclacd.inv_layer_id = i_inv_layer_id
4025 and mclacd.level_type = 1
4026 and mclacd.cost_element_id = 2;
4027
4028 else
4029
4030 l_stmt_num := 76;
4031 update mtl_cst_layer_act_cost_details mclacd
4032 set mclacd.actual_cost = nvl(mclacd.actual_cost, 0) + l_ovhd_cost,
4033 mclacd.variance_amount = decode(l_txn_type_id,68,0,
4034 (nvl(mclacd.actual_cost,0) + l_ovhd_cost
4035 - nvl(mclacd.layer_cost,0)) * layer_quantity ),
4036 mclacd.payback_variance_amount = decode(l_txn_type_id,68,
4037 ((nvl(mclacd.actual_cost,0) + l_ovhd_cost
4038 - nvl(mclacd.layer_cost,0)) * layer_quantity),0)
4039
4040 where mclacd.transaction_id = i_txn_id
4041 and mclacd.organization_id = i_org_id
4042 and mclacd.layer_id = i_layer_id
4043 and mclacd.inv_layer_id = i_inv_layer_id
4044 and mclacd.level_type = 1
4045 and mclacd.cost_element_id = 2;
4046 end if;
4047
4048 if l_debug = 'Y' then
4049 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.updating mclacd...l_ovhd_cost = '
4050 || to_char(l_ovhd_cost)
4051 || ' , stmt '
4052 || to_char(l_stmt_num));
4053 end if;
4054
4055
4056 else /* mclacd does not exist */
4057 l_stmt_num := 80;
4058 insert into mtl_cst_layer_act_cost_details(
4059 transaction_id,
4060 organization_id,
4061 inventory_item_id,
4062 cost_element_id,
4063 level_type,
4064 layer_id,
4065 inv_layer_id,
4066 layer_quantity,
4067 layer_cost,
4068 actual_cost,
4069 variance_amount,
4070 user_entered,
4071 payback_variance_amount,
4072 last_update_date,
4073 last_updated_by,
4074 creation_date,
4075 created_by,
4076 last_update_login,
4077 request_id,
4078 program_application_id,
4079 program_id,
4080 program_update_date)
4081 values (
4082 i_txn_id,
4083 i_org_id,
4084 i_item_id,
4085 2,
4086 1,
4087 i_layer_id,
4088 i_inv_layer_id,
4089 decode(sign(i_txn_qty),-1,-1*i_layer_qty,i_layer_qty),
4090 decode(sign(i_txn_qty),-1,0,l_ovhd_cost), /* layer_cost */
4091 l_ovhd_cost, /* actual_cost */
4092 decode(sign(i_txn_qty),-1,(-1*l_ovhd_cost*i_layer_qty),0), /* variance_amount */
4093 'N', /* user_entered */
4094 0, /* payback_variance_amount */
4095 sysdate,
4096 i_user_id,
4097 sysdate,
4098 i_user_id,
4099 i_login_id,
4100 i_req_id,
4101 i_prg_appl_id,
4102 i_prg_id,
4103 sysdate);
4104
4105 if l_debug = 'Y' then
4106 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.insertign mclacd...l_ovhd_cost = '
4107 || to_char(l_ovhd_cost)
4108 || ',txn_lyr = '
4109 || to_char(i_layer_qty)
4110 || ' , stmt '
4111 || to_char(l_stmt_num));
4112 end if;
4113
4114 end if; /* mclacd does not exist */
4115 end if; /* macs exists */
4116 END IF;
4117
4118 EXCEPTION
4119 when avg_rates_no_ovhd then
4120 rollback;
4121 o_err_num := 9999;
4122 o_err_code := 'CST_NO_MAT_OVHDS';
4123 FND_MESSAGE.set_name('BOM', 'CST_NO_MAT_OVHDS');
4124 o_err_msg := FND_MESSAGE.Get;
4125 when overhead_error then
4126 rollback;
4127 o_err_num := 9999;
4128 o_err_code := 'CST_MAT_OVERHEAD';
4129 FND_MESSAGE.set_name('BOM', 'CST_MAT_OVERHEAD');
4130 o_err_msg := FND_MESSAGE.Get;
4131 when moh_rules_error then
4132 rollback;
4133 o_err_num := 9999;
4134 o_err_code := 'CST_RULES_ERROR';
4135 FND_MESSAGE.set_name('BOM', 'CST_RULES_ERROR');
4136 o_err_msg := FND_MESSAGE.Get;
4137 when others then
4138 rollback;
4139 o_err_num := SQLCODE;
4140 o_err_msg := 'CSTPLENG.apply_layer_material_ovhd (' || to_char(l_stmt_num) ||
4141 '): '
4142 || substr(SQLERRM, 1,200);
4143
4144 END apply_layer_material_ovhd;
4145
4146 /*********************************************************************************
4147 ** PROCEDURE **
4148 ** calc_layer_average_cost **
4149 ** **
4150 ** DESCRIPTION **
4151 ** It main function is to perform the following for the specified transaction: **
4152 ** . insert into MCACD with MCLACD's summarized costs **
4153 ** . update CLCD with CILCD's summarized costs **
4154 ** . update CQL's costs from CLCD **
4155 ** . update CICD's costs from CLCD **
4156 ** . update CIC's costs from CICD **
4157 ** This procedure assumes that all MCLACD rows have already been inserted by **
4158 ** calling program. **
4159 ** Set I_NO_UPDATE_MMT = 1 if the calling program does not want mmt to be **
4160 ** update; otherwise, set it to 0 **
4161 ** Set I_NO_UPDATE_QTY = 1 if clcd, cql, cic and cicd should not be updated; **
4162 ** otherwise, set it to 0 **
4163 ** **
4164 ** HISTORY **
4165 ** 4/24/00 Dieu-Thuong Le Creation **
4166 ** 9/05/00 Dieu-Thuong Le Fix bug 1393484: payback variance should **
4167 ** be stored in MCACD by qty unit because **
4168 ** the distribution proc. CSTPACDP.inventory_ **
4169 ** accounts will calc payback variance to be **
4170 ** posted (-1*i_pqty*l_payback_var) **
4171 *********************************************************************************/
4172
4173 procedure calc_layer_average_cost(
4174 I_ORG_ID IN NUMBER,
4175 I_TXN_ID IN NUMBER,
4176 I_LAYER_ID IN NUMBER,
4177 I_COST_TYPE IN NUMBER,
4178 I_ITEM_ID IN NUMBER,
4179 I_TXN_QTY IN NUMBER,
4180 I_TXN_ACTION_ID IN NUMBER,
4181 I_COST_HOOK IN NUMBER,
4182 I_NO_UPDATE_MMT IN NUMBER,
4183 I_NO_UPDATE_QTY IN NUMBER,
4184 I_USER_ID IN NUMBER,
4185 I_LOGIN_ID IN NUMBER,
4186 I_REQ_ID IN NUMBER,
4187 I_PRG_APPL_ID IN NUMBER,
4188 I_PRG_ID IN NUMBER,
4189 O_Err_Num OUT NOCOPY NUMBER,
4190 O_Err_Code OUT NOCOPY VARCHAR2,
4191 O_Err_Msg OUT NOCOPY VARCHAR2
4192 ) IS
4193 l_txfr_txn_id NUMBER;
4194 l_total_layer_qty NUMBER;
4195 l_layer_qty NUMBER;
4196 l_level_type NUMBER;
4197 l_txn_type_id NUMBER;
4198 l_proj_enabled NUMBER;
4199 l_mandatory_update NUMBER;
4200 l_count NUMBER;
4201 l_err_num NUMBER;
4202 l_err_code VARCHAR2(240);
4203 l_err_msg VARCHAR2(240);
4204 l_stmt_num NUMBER;
4205 process_error EXCEPTION;
4206
4207 BEGIN
4208 -- initialize local variables
4209 l_err_num := 0;
4210 l_err_code := '';
4211 l_err_msg := '';
4212
4213 /********************************************************************
4214 ** For each cost element/level type, one row of MCACD is inserted, **
4215 ** aggregating inventory layer(s) cost. The actual cost populated **
4216 ** in MCACD is the weighted average cost of all inventory layers **
4217 ** associated to the transaction. The variance amount is the sum **
4218 ** of those layers' amounts. **
4219 ** **
4220 ** Note: Unlike the Average Costing processor which uses the **
4221 ** insertion flag to signal clcd insert, the layer cost processor **
4222 ** uses CILCD for CLCD insert and not MCACD. Therefore, insertion **
4223 ** flag will always be set to 'N'. **
4224 ** **
4225 ********************************************************************/
4226
4227 -- get transaction type. It will be needed to identify payback transaction
4228 -- and calculate payback variance.
4229
4230 l_stmt_num := 5;
4231 select transaction_type_id
4232 into l_txn_type_id
4233 from mtl_material_transactions
4234 where transaction_id = i_txn_id;
4235
4236 l_stmt_num := 6;
4237 select count(*)
4238 into l_count
4239 from mtl_cst_layer_act_cost_details
4240 where transaction_id = i_txn_id
4241 and organization_id = i_org_id;
4242
4243 if (l_count = 0) then
4244 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No records in MCLACD');
4245 end if;
4246
4247 /* Insert MCACD (by summing up MCLACD) only if it's not a scrap txn.
4248 Beware: there will be time where MCACD exists, such as when cost hook is used.
4249 In such case, update MCACD with variance amounts.
4250 */
4251
4252 if (i_txn_action_id <> 30) then
4253
4254 l_stmt_num := 10;
4255 update mtl_cst_actual_cost_details mcacd
4256 set (prior_cost,
4257 new_cost,
4258 variance_amount,
4259 payback_variance_amount,
4260 onhand_variance_amount) =
4261 (select
4262 0, -- prior cost
4263 NULL, -- new cost
4264 NVL(sum(mclacd.variance_amount),0),
4265 NVL(sum(mclacd.payback_variance_amount)/abs(i_txn_qty),0), -- bugfix 1393484
4266 NVL(sum(mclacd.onhand_variance_amount),0)
4267 from mtl_cst_layer_act_cost_details mclacd
4268 where mclacd.transaction_id = i_txn_id
4269 and mclacd.organization_id = i_org_id
4270 and mclacd.layer_id = i_layer_id
4271 and mclacd.cost_element_id = mcacd.cost_element_id
4272 and mclacd.level_type = mcacd.level_type
4273 group by mclacd.cost_element_id, mclacd.level_type)
4274 where mcacd.transaction_id = i_txn_id
4275 and mcacd.organization_id = i_org_id
4276 and mcacd.layer_id = i_layer_id
4277 and mcacd.transaction_action_id = i_txn_action_id;
4278
4279 l_stmt_num := 12;
4280 insert into mtl_cst_actual_cost_details (
4281 transaction_id,
4282 organization_id,
4283 layer_id,
4284 cost_element_id,
4285 level_type,
4286 transaction_action_id,
4287 last_update_date,
4288 last_updated_by,
4289 creation_date,
4290 created_by,
4291 last_update_login,
4292 request_id,
4293 program_application_id,
4294 program_id,
4295 program_update_date,
4296 inventory_item_id,
4297 actual_cost,
4298 prior_cost,
4299 new_cost,
4300 insertion_flag,
4301 variance_amount,
4302 user_entered,
4303 payback_variance_amount,
4304 onhand_variance_amount)
4305 select i_txn_id,
4306 i_org_id,
4307 i_layer_id,
4308 mclacd.cost_element_id,
4309 mclacd.level_type,
4310 i_txn_action_id,
4311 sysdate,
4312 i_user_id,
4313 sysdate,
4314 i_user_id,
4315 i_login_id,
4316 i_req_id,
4317 i_prg_appl_id,
4318 i_prg_id,
4319 sysdate,
4320 i_item_id,
4321 decode(
4322 i_txn_qty,
4323 0,
4324 NVL((sum(mclacd.actual_cost)),0), -- modified for bug#3835412 -- NVL(abs(sum(mclacd.actual_cost)),0),
4325 NVL((sum(mclacd.actual_cost * abs(mclacd.layer_quantity)) / abs(i_txn_qty)),0)), -- modified for bug#3835412 -- NVL(abs(sum(mclacd.actual_cost * mclacd.layer_quantity) / i_txn_qty),0)),
4326 0, -- prior cost
4327 NULL, -- new cost
4328 'N', -- insertion flag
4329 NVL(sum(mclacd.variance_amount),0),
4330 'N',
4331 NVL(sum(mclacd.payback_variance_amount)/abs(i_txn_qty),0), -- bugfix 1393484
4332 NVL(sum(mclacd.onhand_variance_amount),0)
4333 from mtl_cst_layer_act_cost_details mclacd
4334 where mclacd.transaction_id = i_txn_id
4335 and mclacd.organization_id = i_org_id
4336 and mclacd.layer_id = i_layer_id
4337 and not exists
4338 (select 'MCACD does not exist'
4339 from mtl_cst_actual_cost_details mcacd
4340 where mcacd.transaction_id = i_txn_id
4341 and mcacd.organization_id = i_org_id
4342 and mcacd.layer_id = i_layer_id
4343 and mcacd.cost_element_id = mclacd.cost_element_id
4344 and mcacd.level_type = mclacd.level_type)
4345 group by mclacd.cost_element_id, mclacd.level_type;
4346
4347 end if; -- end checking for scrap transaction
4348
4349 -- Update MCACD.prior_cost with the corresponding cost in CLCD before CLCD cost
4350 -- is updated.
4351
4352 l_stmt_num := 15;
4353
4354 update mtl_cst_actual_cost_details mcacd
4355 set prior_cost =
4356 (select clcd.item_cost
4357 from cst_layer_cost_details clcd
4358 where clcd.layer_id = i_layer_id
4359 and clcd.cost_element_id = mcacd.cost_element_id
4360 and clcd.level_type = mcacd.level_type)
4361 where mcacd.transaction_id = i_txn_id
4362 and mcacd.organization_id = i_org_id
4363 and mcacd.layer_id = i_layer_id
4364 and mcacd.transaction_action_id = i_txn_action_id
4365 and exists
4366 (select 'there is details in clcd'
4367 from cst_layer_cost_details clcd
4368 where clcd.layer_id = i_layer_id
4369 and clcd.cost_element_id = mcacd.cost_element_id
4370 and clcd.level_type = mcacd.level_type);
4371
4372 -- Insert missing cost elements into mcacd (bug 2987309)
4373 l_stmt_num := 17;
4374 INSERT
4375 INTO mtl_cst_actual_cost_details (
4376 transaction_id,
4377 organization_id,
4378 layer_id,
4379 cost_element_id,
4380 level_type,
4381 transaction_action_id,
4382 last_update_date,
4383 last_updated_by,
4384 creation_date,
4385 created_by,
4386 last_update_login,
4387 request_id,
4388 program_application_id,
4389 program_id,
4390 program_update_date,
4391 inventory_item_id,
4392 actual_cost,
4393 prior_cost,
4394 new_cost,
4395 insertion_flag,
4396 variance_amount,
4397 user_entered
4398 )
4399 SELECT i_txn_id,
4400 i_org_id,
4401 i_layer_id,
4402 CLCD.cost_element_id,
4403 CLCD.level_type,
4404 i_txn_action_id,
4405 sysdate,
4406 i_user_id,
4407 sysdate,
4408 i_user_id,
4409 i_login_id,
4410 i_req_id,
4411 i_prg_appl_id,
4412 i_prg_id,
4413 sysdate,
4414 i_item_id,
4415 0,
4416 CLCD.item_cost,
4417 NULL,
4418 'N',
4419 0,
4420 'N'
4421 FROM cst_layer_cost_details CLCD
4422 WHERE layer_id = i_layer_id
4423 AND NOT EXISTS(
4424 SELECT 'this detail is not in MCACD already'
4425 FROM mtl_cst_actual_cost_details MCACD
4426 WHERE MCACD.transaction_id = i_txn_id
4427 AND MCACD.organization_id = i_org_id
4428 AND MCACD.layer_id = i_layer_id
4429 AND MCACD.cost_element_id = CLCD.cost_element_id
4430 AND MCACD.level_type = CLCD.level_type);
4431
4432 /*******************************************************************
4433 ** Update cst_layer_cost_details if i_no_update_qty is not set. **
4434 ** Since CQL quantity before this transaction is still needed by **
4435 ** CSTPAVCP.update_mmt, CQL quantity and cost information will be **
4436 ** updated later. **
4437 ********************************************************************/
4438 -- get the total layer quantity from cil
4439 select sum(cil.layer_quantity)
4440 into l_total_layer_qty
4441 from cst_inv_layers cil
4442 where cil.layer_id = i_layer_id;
4443
4444 /* Update clcd only if i_no_update_qty flag is not set and the total layer quantity is not zero */
4445
4446 if (i_no_update_qty = 0) and
4447 (l_total_layer_qty <> 0) then
4448 l_stmt_num := 20;
4449 /*Commented for bug 15979260-- get the total layer quantity from cil
4450 select sum(cil.layer_quantity)
4451 into l_total_layer_qty
4452 from cst_inv_layers cil
4453 where cil.layer_id = i_layer_id;*/
4454
4455 /* Added for Bug 15979260 */
4456 -- get the total cost layer quantity from cql
4457 select sum(cql.layer_quantity)
4458 into l_layer_qty
4459 from cst_quantity_layers cql
4460 where cql.layer_id = i_layer_id;
4461
4462
4463 l_stmt_num := 25;
4464
4465 /* Replaced stmt 25, 30 for Bug 15979260: Performance issue.
4466 Instead of resummarizing all the layers, simply use the value change
4467 of the current transaction to recalculate the cost*/
4468 update cst_layer_cost_details clcd
4469 set item_cost =
4470 (select (clcd.item_cost * l_layer_qty
4471 +
4472 nvl(sum((decode(i_txn_action_id, 24,
4473 (nvl(mcl.actual_cost,0) - nvl(mcl.layer_cost,0)),
4474 mcl.actual_cost)
4475 * mcl.layer_quantity)
4476 - decode(i_txn_action_id, 24,
4477 0,nvl(mcl.variance_amount,0))),0)
4478 ) / l_total_layer_qty
4479 from mtl_cst_layer_act_cost_details mcl
4480 where mcl.transaction_id = i_txn_id
4481 and mcl.layer_id = clcd.layer_id
4482 and mcl.level_type = clcd.level_type
4483 and mcl.cost_element_id = clcd.cost_element_id)
4484 where clcd.layer_id = i_layer_id
4485 and exists (select 1
4486 from mtl_cst_layer_act_cost_details mclacd2
4487 where mclacd2.transaction_id = i_txn_id
4488 and mclacd2.layer_id = clcd.layer_id
4489 and mclacd2.level_type = clcd.level_type
4490 and mclacd2.cost_element_id = clcd.cost_element_id);
4491
4492 l_stmt_num := 30;
4493
4494 insert into cst_layer_cost_details(
4495 layer_id,
4496 cost_element_id,
4497 level_type,
4498 last_update_date,
4499 last_updated_by,
4500 creation_date,
4501 created_by,
4502 last_update_login,
4503 request_id,
4504 program_application_id,
4505 program_id,
4506 program_update_date,
4507 item_cost)
4508 select mclacd.layer_id,
4509 mclacd.cost_element_id,
4510 mclacd.level_type,
4511 sysdate,
4512 i_user_id,
4513 sysdate,
4514 i_user_id,
4515 i_login_id,
4516 i_req_id,
4517 i_prg_appl_id,
4518 i_prg_id,
4519 sysdate,
4520 nvl(sum((decode(i_txn_action_id, 24, (nvl(mclacd.actual_cost,0) - nvl(mclacd.layer_cost,0)), nvl(mclacd.actual_cost,0))
4521 * mclacd.layer_quantity
4522 - decode(i_txn_action_id, 24, 0,nvl(mclacd.variance_amount,0))) / l_total_layer_qty),0)
4523 from mtl_cst_layer_act_cost_details mclacd
4524 where mclacd.transaction_id = i_txn_id
4525 and mclacd.layer_id = i_layer_id
4526 and not exists (select 1
4527 from cst_layer_cost_details clcd
4528 where clcd.layer_id = mclacd.layer_id
4529 and clcd.level_type = mclacd.level_type
4530 and clcd.cost_element_id = mclacd.cost_element_id)
4531 GROUP BY
4532 mclacd.layer_id,
4533 mclacd.cost_element_id,
4534 mclacd.level_type;
4535
4536
4537 /* Commented for Bug 15979260
4538 delete from cst_layer_cost_details
4539 where layer_id = i_layer_id;
4540
4541 l_stmt_num := 30;
4542
4543 insert into cst_layer_cost_details(
4544 layer_id,
4545 cost_element_id,
4546 level_type,
4547 last_update_date,
4548 last_updated_by,
4549 creation_date,
4550 created_by,
4551 last_update_login,
4552 request_id,
4553 program_application_id,
4554 program_id,
4555 program_update_date,
4556 item_cost)
4557 select cilcd.layer_id,
4558 cilcd.cost_element_id,
4559 cilcd.level_type,
4560 sysdate,
4561 i_user_id,
4562 sysdate,
4563 i_user_id,
4564 i_login_id,
4565 i_req_id,
4566 i_prg_appl_id,
4567 i_prg_id,
4568 sysdate,
4569 (sum((cilcd.layer_cost*cil.layer_quantity)/l_total_layer_qty)) -- modified for bug#3835412
4570 from cst_inv_layer_cost_details cilcd,
4571 cst_inv_layers cil
4572 where cil.layer_id = i_layer_id*/
4573 /*commented for bug 15979260
4574 and cil.organization_id = i_org_id
4575 and cil.inventory_item_id = i_item_id*/
4576 /*and cil.inv_layer_id = cilcd.inv_layer_id
4577 group by cilcd.layer_id,cost_element_id, level_type; */
4578 end if; -- end updating cost info
4579
4580 /********************************************************************
4581 ** Update MCACD with new cost **
4582 ********************************************************************/
4583 l_stmt_num := 35;
4584
4585 update mtl_cst_actual_cost_details mcacd
4586 set new_cost =
4587 (select clcd.item_cost
4588 from cst_layer_cost_details clcd
4589 where clcd.layer_id = i_layer_id
4590 and clcd.cost_element_id = mcacd.cost_element_id
4591 and clcd.level_type = mcacd.level_type)
4592 where mcacd.organization_id = i_org_id
4593 and mcacd.transaction_id = i_txn_id
4594 and mcacd.layer_id = i_layer_id
4595 and mcacd.transaction_action_id = i_txn_action_id;
4596
4597 /********************************************************************
4598 ** Update Mtl_Material_Transactions **
4599 ** Need to update prior_costed_quantity now. **
4600 ********************************************************************/
4601 l_stmt_num := 40;
4602 if (i_no_update_mmt = 0) then
4603 -- subinventory transfer for receipt side, we need to pass
4604 -- txfr_txn_id to update proper transaction in MMT.
4605 if (i_txn_action_id = 2 and i_txn_qty > 0) then
4606 select transfer_transaction_id
4607 into l_txfr_txn_id
4608 from mtl_material_transactions
4609 where transaction_id = i_txn_id;
4610 else
4611 l_txfr_txn_id := -1;
4612 end if;
4613
4614 CSTPAVCP.update_mmt(
4615 i_org_id,
4616 i_txn_id,
4617 l_txfr_txn_id,
4618 i_layer_id,
4619 0,
4620 i_user_id,
4621 i_login_id,
4622 i_req_id,
4623 i_prg_appl_id,
4624 i_prg_id,
4625 l_err_num,
4626 l_err_code,
4627 l_err_msg);
4628 if (l_err_num <> 0) then
4629 raise process_error;
4630 end if;
4631 end if; -- end updating mmt
4632
4633 /********************************************************************
4634 ** Update layer quantity and layer costs information **
4635 ** (cst_quantity_layers) **
4636 ********************************************************************/
4637 if i_no_update_qty = 0 then
4638 l_stmt_num := 45;
4639 update cst_quantity_layers cql
4640 set (last_updated_by,
4641 last_update_date,
4642 last_update_login,
4643 request_id,
4644 program_application_id,
4645 program_id,
4646 program_update_date,
4647 layer_quantity,
4648 update_transaction_id,
4649 pl_material,
4650 pl_material_overhead,
4651 pl_resource,
4652 pl_outside_processing,
4653 pl_overhead,
4654 tl_material,
4655 tl_material_overhead,
4656 tl_resource,
4657 tl_outside_processing,
4658 tl_overhead,
4659 material_cost,
4660 material_overhead_cost,
4661 resource_cost,
4662 outside_processing_cost,
4663 overhead_cost,
4664 pl_item_cost,
4665 tl_item_cost,
4666 item_cost,
4667 unburdened_cost,
4668 burden_cost) =
4669 (select
4670 i_user_id,
4671 sysdate,
4672 i_login_id,
4673 i_req_id,
4674 i_prg_appl_id,
4675 i_prg_id,
4676 sysdate,
4677 l_total_layer_qty,
4678 i_txn_id,
4679 pl_material,
4680 pl_material_overhead,
4681 pl_resource,
4682 pl_outside_processing,
4683 pl_overhead,
4684 tl_material,
4685 tl_material_overhead,
4686 tl_resource,
4687 tl_outside_processing,
4688 tl_overhead,
4689 material_cost,
4690 material_overhead_cost,
4691 resource_cost,
4692 outside_processing_cost,
4693 overhead_cost,
4694 pl_item_cost,
4695 tl_item_cost,
4696 item_cost,
4697 unburdened_cost,
4698 burden_cost
4699 from cst_quantity_layers_v v
4700 where v.layer_id = i_layer_id)
4701 where cql.layer_id = i_layer_id
4702 and exists
4703 (select 'there is detail cost'
4704 from cst_layer_cost_details clcd
4705 where clcd.layer_id = i_layer_id);
4706
4707 /********************************************************************
4708 ** Update Item Cost and Item Cost Details **
4709 ********************************************************************/
4710
4711 -- Determine the value of mandatory_update_flag.
4712 -- If project is not enabled, set the l_mandatory_update flag.
4713 -- This flag is passed to update_item_cost() routine. In that
4714 -- routine, if this flag is set to 1, the item_cost will be
4715 -- copied from clcd to cicd evenif the quantity <= 0.
4716 -- Otherwise, it will return immediately if the quantity <= 0.
4717 -- For quantity > 0, this flag is ignored, and the weighted avg
4718 -- of cost in clcd (accross different cost group) will be put
4719 -- into cicd.
4720
4721 l_stmt_num := 50;
4722
4723 -- Bug 2401323 - propagation bug for bugfix 2306923
4724 -- Bug 2306923: l_mandatory_update should be zero even if project is not enabled
4725 -- This change was made to function calls (CSTPAVCP.update_item_cost) for
4726 -- average costing as part of bug 1756613.
4727 -- In 11i.2, the cost group model has been enhanced, so that
4728 -- multiple cost groups can exist in a non project manufacturing organization,
4729 -- depending on the set of accounts. Hence updating item cost and item cost details
4730 -- is made to behave exactly like an organization with project
4731 -- references enabled. The cost will be updated in CLCD, but not in CICD.
4732
4733 /* select nvl(project_reference_enabled,0)
4734 into l_proj_enabled
4735 from mtl_parameters
4736 where organization_id = i_org_id;
4737
4738 if (l_proj_enabled = 2) then
4739 l_mandatory_update := 1;
4740 else
4741 l_mandatory_update := 0;
4742 end if;
4743 */
4744
4745 l_mandatory_update := 0;
4746
4747 CSTPAVCP.update_item_cost(
4748 i_org_id,
4749 i_txn_id,
4750 i_layer_id,
4751 i_cost_type,
4752 i_item_id,
4753 l_mandatory_update,
4754 i_user_id,
4755 i_login_id,
4756 i_req_id,
4757 i_prg_appl_id,
4758 i_prg_id,
4759 l_err_num,
4760 l_err_code,
4761 l_err_msg);
4762 if (l_err_num <> 0) then
4763 raise process_error;
4764 end if;
4765 end if; -- end updating quantity and cost info
4766
4767 EXCEPTION
4768 when process_error then
4769 o_err_num := l_err_num;
4770 o_err_code := l_err_code;
4771 o_err_msg := l_err_msg;
4772 when others then
4773 rollback;
4774 o_err_num := SQLCODE;
4775 o_err_msg := 'CSTPLENG.calc_layer_average_cost (' || to_char(l_stmt_num) ||
4776 '): '
4777 || substr(SQLERRM, 1,200);
4778
4779 END calc_layer_average_cost;
4780
4781 /************************************************************************
4782 ** PROCEDURE **
4783 ** layer_cost_update **
4784 ** **
4785 ** DESCRIPTION **
4786 ** This function is called to update inventory layer cost. **
4787 ** It will determine the new elemental costs of the layer based **
4788 ** on user-enter values and compute the adjustment amounts to **
4789 ** inventory valuation. **
4790 ** MTL_CST_LAYER_ACT_COST_DETAILS will be populated and the other **
4791 ** cost tables (CILCD, CIL, CLCD, CQL, CICD, CIC) will be updated **
4792 ** accordingly with the new cost information. **
4793 ** This function is duplicated from CSTPAVCP.average_cost_update. **
4794 ** **
4795 ** HISTORY **
4796 ** 12-MAY-2000 Dieu-Thuong Le Creation **
4797 ** **
4798 ************************************************************************/
4799
4800 procedure layer_cost_update(
4801 I_ORG_ID IN NUMBER,
4802 I_TXN_ID IN NUMBER,
4803 I_LAYER_ID IN NUMBER,
4804 I_COST_TYPE IN NUMBER,
4805 I_ITEM_ID IN NUMBER,
4806 I_TXN_QTY IN NUMBER,
4807 I_TXN_ACT_ID IN NUMBER,
4808 I_USER_ID IN NUMBER,
4809 I_LOGIN_ID IN NUMBER,
4810 I_REQ_ID IN NUMBER,
4811 I_PRG_APPL_ID IN NUMBER,
4812 I_PRG_ID IN NUMBER,
4813 O_Err_Num OUT NOCOPY NUMBER,
4814 O_Err_Code OUT NOCOPY VARCHAR2,
4815 O_Err_Msg OUT NOCOPY VARCHAR2
4816 ) IS
4817
4818 l_neg_cost NUMBER;
4819 l_proj_enabled NUMBER;
4820 l_mandatory_update NUMBER;
4821 l_inv_layer_id NUMBER;
4822 l_layer_qty NUMBER;
4823 l_mctcd_exist NUMBER;
4824 l_stmt_num NUMBER;
4825 l_err_num NUMBER;
4826 l_err_code VARCHAR2(240);
4827 l_err_msg VARCHAR2(240);
4828 process_error EXCEPTION;
4829 neg_cost_error EXCEPTION;
4830 no_mctcd_error EXCEPTION;
4831
4832 BEGIN
4833 -- Initialize variables.
4834 l_neg_cost := 0;
4835 l_layer_qty := 0;
4836 l_mctcd_exist := 0;
4837 l_err_num := 0;
4838 l_err_code := '';
4839 l_err_msg := '';
4840 o_err_num := 0;
4841 o_err_code := '';
4842 o_err_msg := '';
4843
4844 l_stmt_num := 5;
4845
4846 -- Get the inv_layer_id whose cost is being changed
4847 select transaction_source_id
4848 into l_inv_layer_id
4849 from mtl_material_transactions
4850 where transaction_id = I_TXN_ID;
4851
4852 -- check for existence of mctcd
4853 l_stmt_num := 7;
4854 select count(*)
4855 into l_mctcd_exist
4856 from mtl_cst_txn_cost_details ctcd
4857 where ctcd.transaction_id = i_txn_id;
4858
4859 /* if l_mctcd_exist = 0 then
4860 raise no_mctcd_error;
4861 end if;
4862 */
4863 if l_mctcd_exist = 0 then
4864 FND_FILE.PUT_LINE(FND_FILE.LOG,'No mctcd rows');
4865 end if;
4866
4867 l_stmt_num := 10;
4868
4869 /*********************************************************
4870 ** Insert records into mtl_cst_layer_act_cost_details. **
4871 *********************************************************/
4872
4873 insert into mtl_cst_layer_act_cost_details (
4874 transaction_id,
4875 organization_id,
4876 layer_id,
4877 inv_layer_id,
4878 layer_quantity,
4879 cost_element_id,
4880 level_type,
4881 last_update_date,
4882 last_updated_by,
4883 creation_date,
4884 created_by,
4885 last_update_login,
4886 request_id,
4887 program_application_id,
4888 program_id,
4889 program_update_date,
4890 inventory_item_id,
4891 actual_cost,
4892 layer_cost,
4893 variance_amount,
4894 user_entered,
4895 onhand_variance_amount)
4896
4897 select
4898 i_txn_id,
4899 i_org_id,
4900 i_layer_id,
4901 l_inv_layer_id,
4902 cil.layer_quantity,
4903 ctcd.cost_element_id,
4904 ctcd.level_type,
4905 sysdate,
4906 i_user_id,
4907 sysdate,
4908 i_user_id,
4909 i_login_id,
4910 i_req_id,
4911 i_prg_appl_id,
4912 i_prg_id,
4913 sysdate,
4914 ctcd.inventory_item_id,
4915 decode(ctcd.new_average_cost,NULL, -- actual cost
4916 decode(ctcd.percentage_change,NULL,
4917 /* value change formula */
4918 decode(sign(cil.layer_quantity),1,
4919 decode(sign(i_txn_qty),1,
4920 decode( sign(cil.layer_quantity-i_txn_qty),-1,
4921 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + (ctcd.value_change/i_txn_qty*cil.layer_quantity)),-1,
4922 0,
4923 (nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
4924 (ctcd.value_change/i_txn_qty*cil.layer_quantity))/nvl(cil.layer_quantity,-1)),
4925 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
4926 0,
4927 (nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
4928 ctcd.value_change)/nvl(cil.layer_quantity,-1))
4929 ),
4930 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
4931 0,
4932 (nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
4933 ctcd.value_change)/nvl(cil.layer_quantity,-1))),
4934 nvl(cilcd.layer_cost,0)),
4935 /* percentage change formula */
4936 nvl(cilcd.layer_cost,0)*(1+ctcd.percentage_change/100)),
4937 /* new average cost formula */
4938 ctcd.new_average_cost),
4939 nvl(cilcd.layer_cost,0), -- layer cost
4940 decode(ctcd.value_change,NULL,
4941 0,
4942 decode(sign(cil.layer_quantity),1,
4943 decode(sign(i_txn_qty),1,
4944 decode(sign(cil.layer_quantity-i_txn_qty),-1,
4945 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + (ctcd.value_change/i_txn_qty*cil.layer_quantity)),-1,
4946 (ctcd.value_change/i_txn_qty*cil.layer_quantity) + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
4947 0),
4948 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
4949 ctcd.value_change + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
4950 0)),
4951 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
4952 ctcd.value_change + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
4953 0)),
4954 ctcd.value_change)),
4955 'N', -- user entered
4956 /*LCM*/
4957 decode(ctcd.value_change,NULL,
4958 0,
4959 decode(sign(i_txn_qty),1,
4960 decode(sign(cil.layer_quantity),1,
4961 decode(sign(cil.layer_quantity-i_txn_qty),-1,
4962 ctcd.value_change*(1-cil.layer_quantity/i_txn_qty),
4963 0
4964 ),
4965 0
4966 ),
4967 0
4968 )
4969 )
4970 FROM mtl_cst_txn_cost_details ctcd,
4971 cst_inv_layers cil,
4972 cst_inv_layer_cost_details cilcd
4973 WHERE ctcd.transaction_id = i_txn_id
4974 AND ctcd.organization_id = i_org_id
4975 AND cil.layer_id = i_layer_id
4976 AND cil.inv_layer_id = l_inv_layer_id
4977 AND cil.inventory_item_id = ctcd.inventory_item_id
4978 AND cil.organization_id = ctcd.organization_id
4979 AND cilcd.inv_layer_id (+) = l_inv_layer_id
4980 AND cilcd.cost_element_id (+) = ctcd.cost_element_id
4981 AND cilcd.level_type (+) = ctcd.level_type;
4982
4983 -- Verify there are no negative costs!
4984 l_stmt_num := 20;
4985
4986 /* select count(*)
4987 into l_neg_cost
4988 from mtl_cst_layer_act_cost_details
4989 where transaction_id = i_txn_id
4990 and organization_id = i_org_id
4991 and layer_id = i_layer_id
4992 and inv_layer_id = l_inv_layer_id
4993 and actual_cost < 0;
4994
4995 if (l_neg_cost > 0) then
4996 raise neg_cost_error;
4997 end if; */ --removed for bug #4005770
4998
4999 /************************************************************************
5000 ** Delete from cst_inv_layer_cost_details and insert the new rows **
5001 ** from mtl_cst_actual_cost_details. **
5002 ***********************************************************************/
5003 l_stmt_num := 30;
5004
5005 Delete from cst_inv_layer_cost_details
5006 where layer_id = i_layer_id
5007 and inv_layer_id = l_inv_layer_id;
5008
5009 l_stmt_num := 40;
5010 Insert into cst_inv_layer_cost_details (
5011 layer_id,
5012 inv_layer_id,
5013 cost_element_id,
5014 level_type,
5015 layer_cost,
5016 last_update_date,
5017 last_updated_by,
5018 creation_date,
5019 created_by,
5020 last_update_login,
5021 request_id,
5022 program_application_id,
5023 program_id,
5024 program_update_date)
5025 select i_layer_id,
5026 l_inv_layer_id,
5027 mclacd.cost_element_id,
5028 mclacd.level_type,
5029 mclacd.actual_cost,
5030 sysdate,
5031 i_user_id,
5032 sysdate,
5033 i_user_id,
5034 i_login_id,
5035 i_req_id,
5036 i_prg_appl_id,
5037 i_prg_id,
5038 sysdate
5039 from mtl_cst_layer_act_cost_details mclacd
5040 where mclacd.transaction_id = i_txn_id
5041 and mclacd.organization_id = i_org_id
5042 and mclacd.layer_id = i_layer_id
5043 and mclacd.inv_layer_id = l_inv_layer_id;
5044
5045 /********************************************************************
5046 ** Update cst_inv_layers **
5047 ********************************************************************/
5048 l_stmt_num := 50;
5049
5050 update cst_inv_layers cil
5051 set (last_updated_by,
5052 last_update_date,
5053 last_update_login,
5054 request_id,
5055 program_application_id,
5056 program_id,
5057 program_update_date,
5058 layer_cost)=
5059 (select
5060 i_user_id,
5061 sysdate,
5062 i_login_id,
5063 i_req_id,
5064 i_prg_appl_id,
5065 i_prg_id,
5066 sysdate,
5067 nvl(sum(layer_cost),0)
5068 from cst_inv_layer_cost_details cilcd
5069 where cilcd.layer_id = i_layer_id
5070 and cilcd.inv_layer_id = l_inv_layer_id)
5071 where cil.layer_id = i_layer_id
5072 and cil.inv_layer_id = l_inv_layer_id
5073 and exists
5074 (select 'there is detail cost'
5075 from cst_inv_layer_cost_details cilcd
5076 where cilcd.layer_id = i_layer_id
5077 and cilcd.inv_layer_id = l_inv_layer_id);
5078
5079
5080 /*******************************************************
5081 ** Update mcacd, clcd, cql, cic, cicd and mmt **
5082 *******************************************************/
5083
5084 l_stmt_num := 60;
5085
5086 -- Get transaction quantity
5087 select cil.layer_quantity
5088 into l_layer_qty
5089 from cst_inv_layers cil
5090 where cil.layer_id = i_layer_id
5091 and cil.inv_layer_id = l_inv_layer_id;
5092
5093 FND_FILE.PUT_LINE(FND_FILE.LOG, 'layer qty = ' || to_char(l_layer_qty));
5094 l_stmt_num := 70;
5095
5096 CSTPLENG.calc_layer_average_cost(
5097 i_org_id,
5098 i_txn_id,
5099 i_layer_id,
5100 i_cost_type,
5101 i_item_id,
5102 l_layer_qty,
5103 i_txn_act_id,
5104 0, -- no cost hook
5105 0, -- i_no_update_mmt
5106 0, -- i_no_update_qty
5107 i_user_id,
5108 i_login_id,
5109 i_req_id,
5110 i_prg_appl_id,
5111 i_prg_id,
5112 l_err_num,
5113 l_err_code,
5114 l_err_msg);
5115
5116 if (l_err_num <> 0) then
5117 raise process_error;
5118 end if;
5119
5120 /* Update MMT.quantity_adjusted with update transaction quantity. */
5121
5122 update mtl_material_transactions mmt
5123 set last_update_date = sysdate,
5124 last_updated_by = i_user_id,
5125 last_update_login = i_login_id,
5126 program_application_id = i_prg_appl_id,
5127 program_id = i_prg_id,
5128 program_update_date = sysdate,
5129 quantity_adjusted = l_layer_qty
5130 where mmt.transaction_id = i_txn_id;
5131
5132 EXCEPTION
5133 when neg_cost_error then
5134 rollback;
5135 o_err_num := 9999;
5136 o_err_code := 'CST_NEG_ITEM_COST';
5137 FND_MESSAGE.set_name('BOM', 'CST_NEG_ITEM_COST');
5138 o_err_msg := FND_MESSAGE.Get;
5139
5140 /* when no_mctcd_error then
5141 rollback;
5142 o_err_num := 9999;
5143 o_err_code := 'CST_NO_MCTCD';
5144 FND_MESSAGE.set_name('BOM', 'CST_NO_MCTCD');
5145 o_err_msg := FND_MESSAGE.Get;
5146 */
5147 when process_error then
5148 o_err_num := l_err_num;
5149 o_err_code := l_err_code;
5150 o_err_msg := l_err_msg;
5151
5152 when others then
5153 rollback;
5154 o_err_num := SQLCODE;
5155 o_err_msg := 'CSTPLENG.layer_cost_update (' || to_char(l_stmt_num) ||
5156 '): '
5157 || substr(SQLERRM, 1,200);
5158 END layer_cost_update;
5159
5160 ----------------------------------------------------------------------------
5161 -- FUNCTION
5162 -- get_current_layer
5163 -- This function is called to return the inv layer id whose cost needs to be
5164 -- used if a issue is done. It is called from WIP to create layers when there
5165 -- are no layers at all in WIP. In that case WIP needs to know which layer cost-- has to be used.
5166 --
5167 -- INPUT PARAMETERS
5168 -- I_ORG_ID
5169 -- I_TXN_ID
5170 -- I_LAYER_ID
5171 -- I_ITEM_ID
5172 -- I_TXN_ACT_ID
5173 --
5174 -- RETURN VALUES
5175 -- integer 1 Successful
5176 -- 0 Error
5177 -----------------------------------------------------------------------------
5178 function get_current_layer(
5179 I_ORG_ID IN NUMBER,
5180 I_TXN_ID IN NUMBER,
5181 I_LAYER_ID IN NUMBER,
5182 I_ITEM_ID IN NUMBER,
5183 I_USER_ID IN NUMBER,
5184 I_LOGIN_ID IN NUMBER,
5185 I_REQ_ID IN NUMBER,
5186 I_PRG_APPL_ID IN NUMBER,
5187 I_PRG_ID IN NUMBER,
5188 I_TXN_SRC_TYPE_ID IN NUMBER,
5189 I_TXN_SRC_ID IN NUMBER,
5190 O_Err_Num OUT NOCOPY NUMBER,
5191 O_Err_Code OUT NOCOPY VARCHAR2,
5192 O_Err_Msg OUT NOCOPY VARCHAR2
5193 )
5194 return integer IS
5195 l_inv_layer_id NUMBER;
5196 l_cost_method NUMBER;
5197
5198 BEGIN
5199 o_err_num := 0;
5200 o_err_code := '';
5201 o_err_msg := '';
5202 l_inv_layer_id := 0;
5203
5204 /* Get the cost method for the org */
5205 select primary_cost_method
5206 into l_cost_method
5207 from mtl_parameters
5208 where organization_id = I_ORG_ID;
5209
5210 if (l_cost_method = 5) then
5211 /* Try to return the first positive layer */
5212 select nvl(min(inv_layer_id),0)
5213 into l_inv_layer_id
5214 from cst_inv_layers
5215 where layer_id = i_layer_id
5216 and layer_quantity > 0;
5217 /* If there is no positive layer, return the last layer */
5218 if l_inv_layer_id = 0 then
5219 select nvl(max(inv_layer_id),0)
5220 into l_inv_layer_id
5221 from cst_inv_layers
5222 where layer_id = i_layer_id;
5223 end if;
5224 elsif (l_cost_method = 6) then
5225 /* Try to return the last positive layer */
5226 select nvl(max(inv_layer_id), 0)
5227 into l_inv_layer_id
5228 from cst_inv_layers
5229 where layer_id = i_layer_id
5230 and layer_quantity > 0;
5231 /* If there is no positive layer, return the first layer */
5232 if l_inv_layer_id = 0 then
5233 select nvl(min(inv_layer_id),0)
5234 into l_inv_layer_id
5235 from cst_inv_layers
5236 where layer_id = i_layer_id;
5237 end if;
5238 end if;
5239
5240 if (l_inv_layer_id = 0) then
5241 /* No inv layers exist: Hence create one with 0 qty,cost */
5242
5243 select cst_inv_layers_s.nextval
5244 into l_inv_layer_id
5245 from dual;
5246
5247 insert into cst_inv_layers (
5248 create_transaction_id,
5249 layer_id,
5250 inv_layer_id,
5251 organization_id,
5252 inventory_item_id,
5253 creation_quantity,
5254 layer_quantity,
5255 layer_cost,
5256 transaction_source_type_id,
5257 transaction_source_id,
5258 last_update_date,
5259 last_updated_by,
5260 creation_date,
5261 created_by,
5262 last_update_login,
5263 request_id,
5264 program_application_id,
5265 program_id,
5266 program_update_date)
5267 select
5268 i_txn_id,
5269 i_layer_id,
5270 l_inv_layer_id,
5271 i_org_id,
5272 i_item_id,
5273 0,
5274 0,
5275 0,
5276 i_txn_src_type_id,
5277 i_txn_src_id,
5278 sysdate,
5279 i_user_id,
5280 sysdate,
5281 i_user_id,
5282 i_login_id,
5283 i_req_id,
5284 i_prg_appl_id,
5285 i_prg_id,
5286 sysdate
5287 from dual;
5288
5289 insert into cst_inv_layer_cost_details (
5290 layer_id,
5291 inv_layer_id,
5292 cost_element_id,
5293 level_type,
5294 layer_cost,
5295 last_update_date,
5296 last_updated_by,
5297 creation_date,
5298 created_by,
5299 last_update_login,
5300 request_id,
5301 program_application_id,
5302 program_id,
5303 program_update_date)
5304 select
5305 i_layer_id,
5306 l_inv_layer_id,
5307 1,
5308 1,
5309 0,
5310 sysdate,
5311 i_user_id,
5312 sysdate,
5313 i_user_id,
5314 i_login_id,
5315 i_req_id,
5316 i_prg_appl_id,
5317 i_prg_id,
5318 sysdate
5319 from dual;
5320
5321
5322 end if; /* if no layer exists */
5323
5324
5325
5326 return l_inv_layer_id;
5327
5328 EXCEPTION
5329
5330 when others then
5331 o_err_num := SQLCODE;
5332 o_err_msg := 'CSTPLENG.get_current_layer:' || substrb(SQLERRM,1,150);
5333 return 0;
5334
5335 END get_current_layer;
5336
5337 ----------------------------------------------------------------------------
5338 -- layer_cost_det_move
5339 -- This procedure inserts into MCTCD for Layer Cost Update through
5340 -- open interface
5341 ----------------------------------------------------------------------------
5342 procedure layer_cost_det_move (
5343 i_txn_id in number,
5344 i_txn_interface_id in number,
5345 i_txn_action_id in number,
5346 i_org_id in number,
5347 i_item_id in number,
5348 i_cost_group_id in number,
5349 i_inv_layer_id in number,
5350 i_txn_cost in number,
5351 i_new_avg_cost in number,
5352 i_per_change in number,
5353 i_val_change in number,
5354 i_mat_accnt in number,
5355 i_mat_ovhd_accnt in number,
5356 i_res_accnt in number,
5357 i_osp_accnt in number,
5358 i_ovhd_accnt in number,
5359 i_user_id in number,
5360 i_login_id in number,
5361 i_request_id in number,
5362 i_prog_appl_id in number,
5363 i_prog_id in number,
5364 o_err_num out NOCOPY number,
5365 o_err_code out NOCOPY varchar2,
5366 o_err_msg out NOCOPY varchar2
5367 )
5368 is
5369 l_err_num number;
5370 l_err_code varchar2(240);
5371 l_err_msg varchar2(240);
5372 l_num_detail number;
5373 l_layer_id number;
5374 cost_det_move_error EXCEPTION;
5375 cost_no_layer_error EXCEPTION;
5376 begin
5377 /*
5378 ** initialize local variables
5379 */
5380 l_err_num := 0;
5381 l_err_code := '';
5382 l_err_msg := '';
5383
5384 o_err_num := 0;
5385 o_err_code := '';
5386 o_err_msg := '';
5387
5388 SELECT count(*)
5389 INTO l_num_detail
5390 FROM MTL_TXN_COST_DET_INTERFACE
5391 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
5392
5393 /* l_num_detail = 0 : No corresponding rows in MTL_TXN_COST_DET_INTERFACE
5394 * OR i_txn_interface_id is null.
5395 * In this case, call cstpacit.cost_det_new_insert.
5396 */
5397
5398 if (l_num_detail = 0) then
5399 cstpleng.layer_cost_det_new_insert(i_txn_id, i_txn_action_id, i_org_id,
5400 i_item_id, i_cost_group_id, i_inv_layer_id, i_txn_cost,
5401 i_new_avg_cost, i_per_change, i_val_change,
5402 i_mat_accnt, i_mat_ovhd_accnt, i_res_accnt,
5403 i_osp_accnt, i_ovhd_accnt,
5404 i_user_id, i_login_id, i_request_id,
5405 i_prog_appl_id, i_prog_id,
5406 l_err_num, l_err_code, l_err_msg);
5407 if (l_err_num <> 0) then
5408 raise cost_det_move_error;
5409 end if;
5410
5411 else
5412
5413 l_layer_id := cstpaclm.layer_det_exist(i_org_id, i_item_id, i_cost_group_id,
5414 l_err_num, l_err_code, l_err_msg);
5415
5416 if (l_err_num <> 0) then
5417 raise cost_no_layer_error;
5418 end if;
5419
5420 if (l_layer_id <> 0) then
5421
5422 INSERT INTO MTL_CST_TXN_COST_DETAILS (
5423 TRANSACTION_ID,
5424 ORGANIZATION_ID,
5425 INVENTORY_ITEM_ID,
5426 COST_ELEMENT_ID,
5427 LEVEL_TYPE,
5428 TRANSACTION_COST,
5429 NEW_AVERAGE_COST,
5430 PERCENTAGE_CHANGE,
5431 VALUE_CHANGE,
5432 LAST_UPDATE_DATE,
5433 LAST_UPDATED_BY,
5434 CREATION_DATE,
5435 CREATED_BY,
5436 LAST_UPDATE_LOGIN,
5437 REQUEST_ID,
5438 PROGRAM_APPLICATION_ID,
5439 PROGRAM_ID,
5440 PROGRAM_UPDATE_DATE
5441 )
5442 SELECT
5443 i_txn_id,
5444 i_org_id,
5445 i_item_id,
5446 COST_ELEMENT_ID,
5447 LEVEL_TYPE,
5448 LAYER_COST,
5449 LAYER_COST,
5450 NULL,
5451 NULL,
5452 sysdate,
5453 i_user_id,
5454 sysdate,
5455 i_user_id,
5456 i_login_id,
5457 i_request_id,
5458 i_prog_appl_id,
5459 i_prog_id,
5460 sysdate
5461 FROM CST_INV_LAYER_COST_DETAILS CILCD
5462 WHERE CILCD.LAYER_ID = l_layer_id
5463 AND CILCD.INV_LAYER_ID = i_inv_layer_id;
5464
5465 UPDATE MTL_CST_TXN_COST_DETAILS mctcd
5466 set (VALUE_CHANGE,
5467 PERCENTAGE_CHANGE,
5468 NEW_AVERAGE_COST)
5469 =
5470 (select
5471 mtcdi.VALUE_CHANGE,
5472 mtcdi.PERCENTAGE_CHANGE,
5473 mtcdi.NEW_AVERAGE_COST
5474 from MTL_TXN_COST_DET_INTERFACE mtcdi
5475 where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
5476 and mctcd.transaction_id = i_txn_id
5477 and mtcdi.level_type = mctcd.level_type
5478 and mtcdi.cost_element_id = mctcd.cost_element_id
5479 )
5480 where
5481 mctcd.transaction_id = i_txn_id
5482 and exists (select 1
5483 from MTL_TXN_COST_DET_INTERFACE mtcdi
5484 where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
5485 and mtcdi.level_type = mctcd.level_type
5486 and mtcdi.cost_element_id = mctcd.cost_element_id);
5487
5488 else
5489
5490 /* No layer exists , hence use THIS level MATERIAL row */
5491
5492 INSERT INTO MTL_CST_TXN_COST_DETAILS (
5493 TRANSACTION_ID,
5494 ORGANIZATION_ID,
5495 INVENTORY_ITEM_ID,
5496 COST_ELEMENT_ID,
5497 LEVEL_TYPE,
5498 TRANSACTION_COST,
5499 NEW_AVERAGE_COST,
5500 PERCENTAGE_CHANGE,
5501 VALUE_CHANGE,
5502 LAST_UPDATE_DATE,
5503 LAST_UPDATED_BY,
5504 CREATION_DATE,
5505 CREATED_BY,
5506 LAST_UPDATE_LOGIN,
5507 REQUEST_ID,
5508 PROGRAM_APPLICATION_ID,
5509 PROGRAM_ID,
5510 PROGRAM_UPDATE_DATE
5511 )
5512 values (
5513 i_txn_id,
5514 i_org_id,
5515 i_item_id,
5516 1, /* Hard coded to This level Material */
5517 1,
5518 i_txn_cost,
5519 i_new_avg_cost,
5520 i_per_change,
5521 i_val_change,
5522 sysdate,
5523 i_user_id,
5524 sysdate,
5525 i_user_id,
5526 i_login_id,
5527 i_request_id,
5528 i_prog_appl_id,
5529 i_prog_id,
5530 sysdate);
5531
5532
5533 end if; /* if layer exists */
5534
5535 end if; /* if l_num_detail = 0 */
5536
5537 EXCEPTION
5538 when cost_det_move_error then
5539 o_err_num := l_err_num;
5540 o_err_code := l_err_code;
5541 o_err_msg := 'CSTPLENG.LAYER_COST_DET_MOVE:' || l_err_msg;
5542 when cost_no_layer_error then
5543 o_err_num := l_err_num;
5544 o_err_code := l_err_code;
5545 o_err_msg := 'CSTPLENG.LAYER_COST_DET_MOVE: No layer exists' || l_err_msg;
5546 when OTHERS then
5547 o_err_num := SQLCODE;
5548 o_err_msg := 'CSTPLENG.LAYER_COST_DET_MOVE:' || substr(SQLERRM,1,150);
5549
5550 end layer_cost_det_move;
5551
5552 --------------------------------------------------------------------------
5553 -- PROCEDURE layer_cost_det_new_insert
5554 -- procedure used by layer cost update through open interface
5555 --------------------------------------------------------------------------
5556 procedure layer_cost_det_new_insert (
5557 i_txn_id in number,
5558 i_txn_action_id in number,
5559 i_org_id in number,
5560 i_item_id in number,
5561 i_cost_group_id in number,
5562 i_inv_layer_id in number,
5563 i_txn_cost in number,
5564 i_new_avg_cost in number,
5565 i_per_change in number,
5566 i_val_change in number,
5567 i_mat_accnt in number,
5568 i_mat_ovhd_accnt in number,
5569 i_res_accnt in number,
5570 i_osp_accnt in number,
5571 i_ovhd_accnt in number,
5572 i_user_id in number,
5573 i_login_id in number,
5574 i_request_id in number,
5575 i_prog_appl_id in number,
5576 i_prog_id in number,
5577 o_err_num out NOCOPY number,
5578 o_err_code out NOCOPY varchar2,
5579 o_err_msg out NOCOPY varchar2
5580 )
5581 is
5582 l_err_num number;
5583 l_err_code varchar2(240);
5584 l_err_msg varchar2(240);
5585
5586 cl_item_cost number;
5587 cost_element_count number;
5588
5589 l_cost_elmt_id number;
5590 l_layer_id number;
5591 cil_layer_cost number;
5592 cost_det_new_insert_error EXCEPTION;
5593
5594
5595 cursor cost_elmt_ids is
5596 SELECT CILCD.COST_ELEMENT_ID
5597 FROM CST_INV_LAYERS CIL,
5598 CST_INV_LAYER_COST_DETAILS CILCD
5599 WHERE CIL.LAYER_ID = l_layer_id
5600 AND CIL.INV_LAYER_ID = i_inv_layer_id
5601 AND CILCD.LAYER_ID = l_layer_id
5602 AND CILCD.INV_LAYER_ID = i_inv_layer_id;
5603
5604
5605 begin
5606 /*
5607 ** initialize local variables
5608 */
5609 l_err_num := 0;
5610 l_err_code := '';
5611 l_err_msg := '';
5612
5613 l_layer_id := cstpaclm.layer_det_exist(i_org_id, i_item_id, i_cost_group_id,
5614 l_err_num, l_err_code, l_err_msg);
5615
5616 if (l_err_num <> 0) then
5617 raise cost_det_new_insert_error;
5618 end if;
5619
5620 /* If layer detail exist, then calculate proportional costs and
5621 * insert each elements into MTL_CST_TXN_COST_DETAILS.
5622 */
5623
5624 if (l_layer_id <> 0) then
5625
5626 if (i_txn_action_id = 24) then
5627 -- checking the existence of accounts for layer cost update case
5628 open cost_elmt_ids;
5629
5630 loop
5631 fetch cost_elmt_ids into l_cost_elmt_id;
5632 exit when cost_elmt_ids%NOTFOUND;
5633
5634 if ((l_cost_elmt_id = 1 and i_mat_accnt is null) or
5635 (l_cost_elmt_id = 2 and i_mat_ovhd_accnt is null) or
5636 (l_cost_elmt_id = 3 and i_res_accnt is null) or
5637 (l_cost_elmt_id = 4 and i_osp_accnt is null) or
5638 (l_cost_elmt_id = 5 and i_ovhd_accnt is null)) then
5639 -- Error occured
5640
5641 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
5642 l_err_code := 'Invalid accounts.';
5643 l_err_msg := FND_MESSAGE.Get;
5644 l_err_num := 999;
5645
5646 raise cost_det_new_insert_error;
5647 end if;
5648
5649
5650 end loop;
5651 end if;
5652
5653 SELECT LAYER_COST
5654 INTO cil_layer_cost
5655 FROM CST_INV_LAYERS
5656 WHERE LAYER_ID = l_layer_id
5657 AND INV_LAYER_ID = i_inv_layer_id;
5658
5659 /* for the case of layer cost equal zero */
5660 /* split cost evenly among cost elements */
5661
5662 if (cl_item_cost = 0) then
5663 SELECT count(COST_ELEMENT_ID)
5664 INTO cost_element_count
5665 FROM CST_INV_LAYER_COST_DETAILS
5666 WHERE LAYER_ID = l_layer_id
5667 AND INV_LAYER_ID = i_inv_layer_id;
5668 end if;
5669
5670 INSERT INTO MTL_CST_TXN_COST_DETAILS (
5671 TRANSACTION_ID,
5672 ORGANIZATION_ID,
5673 INVENTORY_ITEM_ID,
5674 COST_ELEMENT_ID,
5675 LEVEL_TYPE,
5676 TRANSACTION_COST,
5677 NEW_AVERAGE_COST,
5678 PERCENTAGE_CHANGE,
5679 VALUE_CHANGE,
5680 LAST_UPDATE_DATE,
5681 LAST_UPDATED_BY,
5682 CREATION_DATE,
5683 CREATED_BY,
5684 LAST_UPDATE_LOGIN,
5685 REQUEST_ID,
5686 PROGRAM_APPLICATION_ID,
5687 PROGRAM_ID,
5688 PROGRAM_UPDATE_DATE
5689 )
5690 SELECT
5691 i_txn_id,
5692 i_org_id,
5693 i_item_id,
5694 CILCD.COST_ELEMENT_ID,
5695 CILCD.LEVEL_TYPE,
5696 DECODE(CIL.LAYER_COST, 0, i_txn_cost / cost_element_count,
5697 i_txn_cost * CILCD.LAYER_COST / CIL.LAYER_COST),
5698 DECODE(CIL.LAYER_COST, 0, i_new_avg_cost / cost_element_count,
5699 i_new_avg_cost * CILCD.LAYER_COST / CIL.LAYER_COST),
5700 i_per_change,
5701 DECODE(CIL.LAYER_COST, 0, i_val_change / cost_element_count,
5702 i_val_change * CILCD.LAYER_COST / CIL.LAYER_COST),
5703 sysdate,
5704 i_user_id,
5705 sysdate,
5706 i_user_id,
5707 i_login_id,
5708 i_request_id,
5709 i_prog_appl_id,
5710 i_prog_id,
5711 sysdate
5712 FROM CST_INV_LAYERS CIL, CST_INV_LAYER_COST_DETAILS CILCD
5713 WHERE CIL.LAYER_ID = l_layer_id
5714 AND CIL.INV_LAYER_ID = i_inv_layer_id
5715 AND CILCD.LAYER_ID = l_layer_id
5716 AND CILCD.INV_LAYER_ID = i_inv_layer_id;
5717
5718 /* If layer detail does not exist, then insert a new row
5719 * as a this level material.
5720 */
5721 else
5722
5723 if (i_txn_action_id = 24 and i_mat_accnt is null) then
5724 -- Error occured only for layer cost update
5725
5726 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
5727 l_err_code := 'Invalid accounts.';
5728 l_err_msg := FND_MESSAGE.Get;
5729 l_err_num := 999;
5730
5731 raise cost_det_new_insert_error;
5732 end if;
5733
5734
5735 INSERT INTO MTL_CST_TXN_COST_DETAILS (
5736 TRANSACTION_ID,
5737 ORGANIZATION_ID,
5738 INVENTORY_ITEM_ID,
5739 COST_ELEMENT_ID,
5740 LEVEL_TYPE,
5741 TRANSACTION_COST,
5742 NEW_AVERAGE_COST,
5743 PERCENTAGE_CHANGE,
5744 VALUE_CHANGE,
5745 LAST_UPDATE_DATE,
5746 LAST_UPDATED_BY,
5747 CREATION_DATE,
5748 CREATED_BY,
5749 LAST_UPDATE_LOGIN,
5750 REQUEST_ID,
5751 PROGRAM_APPLICATION_ID,
5752 PROGRAM_ID,
5753 PROGRAM_UPDATE_DATE
5754 )
5755 values (
5756 i_txn_id,
5757 i_org_id,
5758 i_item_id,
5759 1, /* Hard coded to This level Material */
5760 1,
5761 i_txn_cost,
5762 i_new_avg_cost,
5763 i_per_change,
5764 i_val_change,
5765 sysdate,
5766 i_user_id,
5767 sysdate,
5768 i_user_id,
5769 i_login_id,
5770 i_request_id,
5771 i_prog_appl_id,
5772 i_prog_id,
5773 sysdate);
5774
5775 end if;
5776
5777 EXCEPTION
5778 when cost_det_new_insert_error then
5779 o_err_num := l_err_num;
5780 o_err_code := l_err_code;
5781 o_err_msg := 'CSTPLENG.LAYER_COST_DET_NEW_INSERT:' || l_err_msg;
5782 when OTHERS then
5783 o_err_num := SQLCODE;
5784 o_err_msg := 'CSTPLENG.LAYER_COST_DET_NEW_INSERT:' || substr(SQLERRM,1,150);
5785
5786 end layer_cost_det_new_insert;
5787
5788 ----------------------------------------------------------------------------
5789 -- PROCEDURE --
5790 -- layer_cost_update_dist --
5791 -- --
5792 -- DESCRIPTION --
5793 -- This procedure is called by CSTPLCIN package for layer cost update --
5794 -- transaction, since distributions need o be done using layer cost --
5795 -- from MCLACD rather than MCLACD --
5796 -- --
5797 -- PURPOSE: --
5798 -- Post distributions into MTA for layer cost update transactions --
5799 -- --
5800 -- PARAMETERS: --
5801 -- all transaction related details --
5802 ----------------------------------------------------------------------------
5803 procedure layer_cost_update_dist(
5804 I_ORG_ID IN NUMBER,
5805 I_TXN_ID IN NUMBER,
5806 I_LAYER_ID IN NUMBER,
5807 I_EXP_ITEM IN NUMBER,
5808 I_USER_ID IN NUMBER,
5809 I_LOGIN_ID IN NUMBER,
5810 I_REQ_ID IN NUMBER,
5811 I_PRG_APPL_ID IN NUMBER,
5812 I_PRG_ID IN NUMBER,
5813 O_Error_Num OUT NOCOPY NUMBER,
5814 O_Error_Code OUT NOCOPY VARCHAR2,
5815 O_Error_Message OUT NOCOPY VARCHAR2
5816 )IS
5817 l_hook NUMBER;
5818 l_item_id NUMBER;
5819 l_cost_grp_id NUMBER;
5820 l_txn_org_id NUMBER;
5821 l_txn_src_id NUMBER;
5822 l_txn_date DATE;
5823 l_p_qty NUMBER;
5824 l_subinv VARCHAR2(10);
5825 l_qty_adj NUMBER;
5826 l_dist_acct NUMBER;
5827 l_mat_acct NUMBER;
5828 l_mat_ovhd_acct NUMBER;
5829 l_res_acct NUMBER;
5830 l_osp_acct NUMBER;
5831 l_ovhd_acct NUMBER;
5832 l_pri_curr VARCHAR2(15);
5833 l_alt_curr VARCHAR2(10);
5834 l_conv_date DATE;
5835 l_conv_rate NUMBER;
5836 l_conv_type VARCHAR2(30);
5837 l_sob_id NUMBER;
5838 l_enc_rev NUMBER;
5839 l_enc_amount NUMBER;
5840 l_enc_acct NUMBER;
5841 l_acct_exist NUMBER;
5842 l_acct NUMBER;
5843 l_cost NUMBER;
5844 l_var NUMBER;
5845 l_ele_exist NUMBER;
5846 l_inv_mat_acct NUMBER;
5847 l_inv_mat_ovhd_acct NUMBER;
5848 l_inv_res_acct NUMBER;
5849 l_inv_osp_acct NUMBER;
5850 l_inv_ovhd_acct NUMBER;
5851 l_avg_cost_var_acct NUMBER;
5852 /*LCM*/
5853 l_onhand_var NUMBER;
5854 l_onhand_var_acct NUMBER;
5855
5856 l_err_num NUMBER;
5857 l_err_code VARCHAR2(240);
5858 l_err_msg VARCHAR2(240);
5859 l_stmt_num NUMBER;
5860 process_error EXCEPTION;
5861 no_acct_error EXCEPTION;
5862 no_txn_det_error EXCEPTION;
5863
5864 BEGIN
5865 -- initialize local variables
5866 l_ele_exist := 0;
5867 l_err_num := 0;
5868 l_err_code := '';
5869 l_err_msg := '';
5870
5871 l_stmt_num := 1;
5872 l_hook := CSTPACHK.cost_dist_hook(i_org_id,
5873 i_txn_id,
5874 i_user_id,
5875 i_login_id,
5876 i_req_id,
5877 i_prg_appl_id,
5878 i_prg_id,
5879 l_err_num,
5880 l_err_code,
5881 l_err_msg);
5882
5883 -- If the user choose to do distribution then we are done!
5884 if (l_hook = 1) then
5885 return;
5886 end if;
5887
5888 -- Populate local variables
5889
5890 l_stmt_num := 2;
5891 select
5892 inventory_item_id, organization_id,
5893 nvl(cost_group_id,1),
5894 transaction_date,
5895 primary_quantity, subinventory_code,
5896 quantity_adjusted,
5897 nvl(transaction_source_id,-1),
5898 nvl(distribution_account_id,-1),
5899 nvl(material_account, -1), nvl(material_overhead_account, -1),
5900 nvl(resource_account, -1), nvl(outside_processing_account, -1),
5901 nvl(overhead_account, -1),
5902 nvl(encumbrance_account, -1), nvl(encumbrance_amount, 0),
5903 currency_code,
5904 nvl(currency_conversion_date,transaction_date),
5905 nvl(currency_conversion_rate,-1) , currency_conversion_type,
5906 nvl(expense_account_id,-1)
5907 into
5908 l_item_id,
5909 l_txn_org_id,
5910 l_cost_grp_id,
5911 l_txn_date,
5912 l_p_qty,
5913 l_subinv,
5914 l_qty_adj,
5915 l_txn_src_id,
5916 l_dist_acct,
5917 l_mat_acct,
5918 l_mat_ovhd_acct,
5919 l_res_acct,
5920 l_osp_acct,
5921 l_ovhd_acct,
5922 l_enc_acct,
5923 l_enc_amount,
5924 l_alt_curr,
5925 l_conv_date,
5926 l_conv_rate,
5927 l_conv_type,
5928 l_onhand_var_acct
5929 from mtl_material_transactions
5930 where transaction_id = i_txn_id;
5931
5932 l_stmt_num := 3;
5933 select decode(encumbrance_reversal_flag,1,1,2,0,0)
5934 into l_enc_rev
5935 from mtl_parameters
5936 where organization_id = i_org_id;
5937
5938 l_stmt_num := 4;
5939
5940 select ledger_id
5941 into l_sob_id
5942 from cst_acct_info_v
5943 where organization_id = i_org_id;
5944
5945 l_stmt_num := 5;
5946 select currency_code
5947 into l_pri_curr
5948 from gl_sets_of_books
5949 where set_of_books_id = l_sob_id;
5950
5951 l_stmt_num := 6;
5952 if (l_alt_curr is not NULL and l_conv_rate = -1) then
5953 if (l_alt_curr <> l_pri_curr) then
5954
5955 if (l_conv_type is NULL) then
5956 FND_PROFILE.get('CURRENCY_CONVERSION_TYPE', l_conv_type);
5957 end if;
5958
5959 l_stmt_num := 7;
5960
5961 l_conv_rate := gl_currency_api.get_rate(l_sob_id,l_alt_curr,l_txn_date,
5962 l_conv_type);
5963 end if;
5964 end if;
5965
5966 l_stmt_num := 8;
5967
5968 BEGIN
5969 IF l_cost_grp_id <> 1 THEN
5970 SELECT
5971 nvl(material_account,-1),
5972 nvl(material_overhead_account,-1),
5973 nvl(resource_account,-1),
5974 nvl(outside_processing_account,-1),
5975 nvl(overhead_account,-1),
5976 nvl(average_cost_var_account,-1)
5977 INTO
5978 l_inv_mat_acct,
5979 l_inv_mat_ovhd_acct,
5980 l_inv_res_acct,
5981 l_inv_osp_acct,
5982 l_inv_ovhd_acct,
5983 l_avg_cost_var_acct
5984 FROM
5985 CST_COST_GROUP_ACCOUNTS
5986 WHERE
5987 ORGANIZATION_ID = i_org_id
5988 AND COST_GROUP_ID = l_cost_grp_id;
5989
5990 ELSE
5991 SELECT
5992 nvl(MATERIAL_ACCOUNT, -1),
5993 nvl(MATERIAL_OVERHEAD_ACCOUNT, -1),
5994 nvl(RESOURCE_ACCOUNT, -1),
5995 nvl(OVERHEAD_ACCOUNT, -1),
5996 nvl(OUTSIDE_PROCESSING_ACCOUNT, -1),
5997 nvl(AVERAGE_COST_VAR_ACCOUNT, -1)
5998 INTO
5999 l_inv_mat_acct,
6000 l_inv_mat_ovhd_acct,
6001 l_inv_res_acct,
6002 l_inv_ovhd_acct,
6003 l_inv_osp_acct,
6004 l_avg_cost_var_acct
6005 FROM
6006 MTL_PARAMETERS
6007 WHERE
6008 ORGANIZATION_ID = i_org_id;
6009 END IF;
6010 EXCEPTION
6011 WHEN NO_DATA_FOUND THEN
6012 raise no_acct_error;
6013 END;
6014
6015 l_stmt_num := 20;
6016
6017 select count(*)
6018 into l_ele_exist
6019 from mtl_cst_actual_cost_details
6020 where transaction_id = i_txn_id
6021 and organization_id = i_org_id;
6022
6023 if (l_ele_exist = 0) then
6024 raise no_txn_det_error;
6025 end if;
6026
6027 -- Layer cost update has been designed along the same lines as average
6028 -- cost update. However, since prior_cost column in MCACD is populated as
6029 -- the current average cost of the item across all inventory layers, the
6030 -- layer cost update distribution should be based on MCLACD rather than MCACD
6031 -- Based on this the accounting rules are :
6032 --
6033 -- Adjustment acct (layer_cost - actual_cost) * Qty - Variance
6034 -- Inventory (actual_cost - layer_cost) * Qty
6035 -- Variance Acct Variance
6036 -- All these value are based on MCLACD.
6037
6038
6039 FOR cost_element IN 1..5 loop
6040 l_cost := NULL;
6041 -- The difference between new cost and prior cost is the impact to
6042 -- inventory. If new cost is higher then it's a debit to inventory
6043 -- else it is a credit to inventory.
6044
6045 l_stmt_num := 30;
6046
6047 select (sum(actual_cost) - sum(layer_cost)),sum(variance_amount),
6048 sum(onhand_variance_amount)
6049 into l_cost,l_var,l_onhand_var
6050 from mtl_cst_layer_act_cost_details
6051 where transaction_id = i_txn_id
6052 and organization_id = i_org_id
6053 and cost_element_id = cost_element;
6054
6055 /*ADDED 'l_cost_grp_id' FOR #BUG8881927*/
6056 if (l_cost is not NULL ) then
6057 -- First post to inventory.
6058 IF (l_cost <> 0) THEN
6059 l_acct := CSTPACHK.get_account_id(i_org_id, i_txn_id,
6060 sign(l_cost), 1,
6061 cost_element, NULL, NULL,
6062 0, NULL, l_err_num, l_err_code,
6063 l_err_msg,l_cost_grp_id);
6064
6065 -- check error
6066 if (l_err_num<>0) then
6067 raise process_error;
6068 end if;
6069
6070 if (l_acct = -1) then
6071
6072 l_stmt_num := 40;
6073
6074 select decode(cost_element, 1, l_inv_mat_acct,
6075 2, l_inv_mat_ovhd_acct,
6076 3, l_inv_res_acct,
6077 4, l_inv_osp_acct,
6078 5, l_inv_ovhd_acct)
6079 into l_acct
6080 from dual;
6081 end if;
6082
6083 CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_qty_adj * l_cost,
6084 sign(l_qty_adj * l_cost) * abs(l_qty_adj)/*modified for bug #4005770*/ /*l_qty_adj*/, l_acct, l_sob_id, 1,
6085 cost_element, NULL,
6086 l_txn_date, l_txn_src_id, 15,
6087 l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
6088 1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
6089 l_err_num, l_err_code, l_err_msg);
6090
6091 -- check error
6092 if (l_err_num<>0) then
6093 raise process_error;
6094 end if;
6095 END IF;
6096 -- Second post to adjustment.
6097 if (l_cost <> 0 OR l_var <> 0 OR l_onhand_var <> 0) then
6098 l_cost := -1 * l_cost;
6099 /*ADDED 'l_cost_grp_id' FOR #BUG8881927*/
6100 l_acct := CSTPACHK.get_account_id(i_org_id, i_txn_id,
6101 sign((l_qty_adj * l_cost) - l_var - l_onhand_var), 2,
6102 cost_element, NULL, NULL,
6103 0, NULL, l_err_num, l_err_code,
6104 l_err_msg,l_cost_grp_id);
6105 -- check error
6106 if (l_err_num<>0) then
6107 raise process_error;
6108 end if;
6109
6110 l_stmt_num := 50;
6111
6112 /* Added the decode for BUG: 1107767. Avg cost update through the interface needs all the accounts
6113 in MMT to be specified, even if only the material cost element is getting affected */
6114
6115 if (l_acct = -1) then
6116 select decode(cost_element, 1, l_mat_acct,
6117 2, decode(l_mat_ovhd_acct,-1, l_mat_acct, l_mat_ovhd_acct),
6118 3, decode(l_res_acct,-1, l_mat_acct, l_res_acct),
6119 4, decode(l_osp_acct,-1, l_mat_acct, l_osp_acct),
6120 5, decode(l_ovhd_acct,-1, l_mat_acct, l_ovhd_acct))
6121 into l_acct
6122 from dual;
6123 end if;
6124
6125 CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, (l_qty_adj * l_cost) - l_var - l_onhand_var,
6126 l_qty_adj, l_acct, l_sob_id, 2,
6127 cost_element, NULL,
6128 l_txn_date, l_txn_src_id, 15,
6129 l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
6130 1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
6131 l_err_num, l_err_code, l_err_msg);
6132
6133 -- check error
6134 if (l_err_num<>0) then
6135 raise process_error;
6136 end if;
6137 end if;
6138 if (l_onhand_var <> 0) then
6139 /*ADDED 'l_cost_grp_id' FOR #BUG8881927*/
6140 l_acct := CSTPACHK.get_account_id(i_org_id, i_txn_id,
6141 sign(l_onhand_var), 20,
6142 cost_element, NULL, NULL,
6143 0, NULL, l_err_num, l_err_code,
6144 l_err_msg,l_cost_grp_id);
6145
6146 -- check error
6147 if(l_err_num<>0) then
6148 raise process_error;
6149 end if;
6150
6151 if (l_acct = -1) then
6152 l_acct := l_onhand_var_acct;
6153 end if;
6154
6155 CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_onhand_var,
6156 l_qty_adj, l_acct, l_sob_id, 20,
6157 cost_element, NULL,
6158 l_txn_date, l_txn_src_id, 15,
6159 l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
6160 1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
6161 l_err_num, l_err_code, l_err_msg);
6162
6163 -- check error
6164 if(l_err_num<>0) then
6165 raise process_error;
6166 end if;
6167
6168 end if;
6169
6170 end if;
6171 end loop;
6172
6173
6174 -- Now Post one consolidated variance entry
6175
6176 l_stmt_num := 60;
6177
6178 select nvl(sum(variance_amount),0)
6179 into l_var
6180 from mtl_cst_actual_cost_details cacd
6181 where transaction_id = i_txn_id
6182 and organization_id = i_org_id;
6183
6184 if (l_var <> 0) then
6185 /*ADDED 'l_cost_grp_id' FOR #BUG8881927*/
6186 l_acct := CSTPACHK.get_account_id(i_org_id, i_txn_id,
6187 sign(l_var), 13,
6188 NULL, NULL, NULL,
6189 0, NULL, l_err_num, l_err_code,
6190 l_err_msg,l_cost_grp_id);
6191
6192 -- check error
6193 if(l_err_num<>0) then
6194 raise process_error;
6195 end if;
6196
6197 if (l_acct = -1) then
6198 l_acct := l_avg_cost_var_acct;
6199 end if;
6200
6201 CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_var,
6202 l_qty_adj, l_acct, l_sob_id, 13,
6203 NULL, NULL,
6204 l_txn_date, l_txn_src_id, 15,
6205 l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
6206 1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
6207 l_err_num, l_err_code, l_err_msg);
6208
6209 -- check error
6210 if(l_err_num<>0) then
6211 raise process_error;
6212 end if;
6213
6214 end if;
6215
6216 UPDATE mtl_cst_actual_cost_details
6217 SET transaction_costed_date = sysdate
6218 WHERE transaction_id = i_txn_id
6219 AND transaction_costed_date IS NULL;
6220
6221 -----For Bug 14768572, fixing the rounding issue in MTA table, start ------------------------------
6222 CSTPACDP.balance_account (i_org_id, i_txn_id, l_err_num, l_err_code, l_err_msg);
6223 if (l_err_num <>0) then
6224 raise process_error;
6225 end if;
6226 -----For Bug 14768572, fixing the rounding issue in MTA table, end ------------------------------
6227
6228 EXCEPTION
6229
6230 when process_error then
6231 rollback;
6232 O_error_num := l_err_num;
6233 O_error_code := l_err_code;
6234 O_error_message := l_err_msg;
6235
6236 when no_acct_error then
6237 rollback;
6238 O_error_num := 9999;
6239 O_error_code := 'CST_NO_CG_ACCTS';
6240 FND_MESSAGE.set_name('BOM', 'CST_NO_CG_ACCTS');
6241 O_error_message := FND_MESSAGE.Get;
6242
6243 when no_txn_det_error then
6244 rollback;
6245 O_error_num := 9999;
6246 O_error_code := 'CST_NO_TXN_DET';
6247 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_DET');
6248 O_error_message := FND_MESSAGE.Get;
6249
6250 when others then
6251 rollback;
6252 O_error_num := SQLCODE;
6253 O_error_message := 'CSTPLENG.layer_cost_update_dist' || to_char(l_stmt_num) ||
6254 substr(SQLERRM,1,180);
6255
6256 END layer_cost_update_dist;
6257
6258 ----------------------------------------------------------------------------
6259 -- PROCEDURE --
6260 -- update_inv_layer_cost --
6261 -- --
6262 -- DESCRIPTION --
6263 -- This procedure is calld by the Define Items form (INVIDITM), to --
6264 -- set costs as zero, when an item is changed from asset to expense --
6265 -- --
6266 -- PURPOSE: --
6267 -- FIFO/LIFO layer cost processing for Oracle Applications Rel 11i.2 --
6268 -- --
6269 -- PARAMETERS: --
6270 -- i_org_id : organization ID --
6271 -- i_item_id : inventory_item_id for the item whose --
6272 -- expense flag is changed --
6273 -- --
6274 -- HISTORY: --
6275 -- 04/20/00 Anitha B Created --
6276 ----------------------------------------------------------------------------
6277
6278 PROCEDURE update_inv_layer_cost (i_org_id IN NUMBER,
6279 i_item_id IN NUMBER,
6280 i_userid IN NUMBER,
6281 i_login_id IN NUMBER)
6282 IS
6283
6284 Begin
6285 update cst_inv_layers
6286 set last_updated_by = i_userid,
6287 last_update_date = sysdate,
6288 last_update_login = i_login_id,
6289 layer_cost = 0,
6290 burden_cost = 0,
6291 unburdened_cost = 0
6292 where organization_id = i_org_id
6293 and inventory_item_id = i_item_id;
6294
6295 delete from cst_inv_layer_cost_details
6296 where inv_layer_id IN (select inv_layer_id
6297 from cst_inv_layers
6298 where organization_id = i_org_id
6299 and inventory_item_id = i_item_id);
6300 EXCEPTION
6301 when NO_DATA_FOUND then null;
6302 End update_inv_layer_cost;
6303
6304
6305 END CSTPLENG;