[Home] [Help]
PACKAGE BODY: APPS.CSTPLENG
Source
1 PACKAGE BODY CSTPLENG AS
2 /* $Header: CSTLENGB.pls 120.16.12010000.4 2008/11/19 05:02:09 anjha 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
2318 BEGIN
2319 l_stmt_num := 0;
2320 l_err_num := 0;
2321 l_err_code := '';
2322 l_err_msg := '';
2323 l_layers_exist := 0;
2324 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
2325
2326 l_stmt_num := 5;
2327 if (l_debug = 'Y') then
2328 FND_FILE.PUT_LINE(FND_FILE.LOG,'Consuming inventory layers from CG layer : ' || to_char(i_layer_id));
2329 end if;
2330 select count(*)
2331 into l_layers_exist
2332 from cst_inv_layers
2333 where layer_id = i_layer_id;
2334
2335 if (l_layers_exist = 0) then
2336 if (l_debug = 'Y') then
2337 FND_FILE.PUT_LINE(FND_FILE.LOG,'Creating negative layer ...');
2338 end if;
2339
2340 /* Bug 2325297
2341 Create Layers is called with i_interorg_rec parameter as NULL
2342 This is due to the fact that layers have to be created if layers
2343 do not exist in the LIFO/FIFO Organization.
2344 But if the the transaction is a sending transaction, then we
2345 do not earn material overhead (which is also taken care of in create_layers()
2346 The value NULL for i_interorg_rec ensures that layers are created
2347 in the sending organization but it does not earn MOH
2348 */
2349 create_layers(i_org_id,
2350 i_txn_id,
2351 i_layer_id,
2352 i_item_id,
2353 i_txn_qty,
2354 i_cost_method,
2355 i_txn_src_type,
2356 i_txn_action_id,
2357 i_cost_hook,
2358 NULL, -- i_interorg_rec: Create Layers always if it a sending txn
2359 i_cost_type,
2360 i_mat_ct_id,
2361 i_avg_rates_id,
2362 i_exp_flag,
2363 i_user_id,
2364 i_login_id,
2365 i_req_id,
2366 i_prg_appl_id,
2367 i_prg_id,
2368 l_err_num,
2369 l_err_code,
2370 l_err_msg);
2371 return;
2372 end if;
2373
2374 /* If expense item, then insert into MCACD using current costs. No inventory
2375 layer consumed or created */
2376 l_stmt_num := 6;
2377 select decode(inventory_asset_flag,'Y',0,1)
2378 into l_exp_item
2379 from mtl_system_items
2380 where inventory_item_id = i_item_id
2381 and organization_id = i_org_id;
2382
2383 l_stmt_num := 7;
2384 if (l_exp_item = 1) then
2385 if (l_debug = 'Y') then
2386 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for expense item...');
2387 end if;
2388 select count(*) into l_count
2389 from cst_layer_cost_details
2390 where layer_id = i_layer_id;
2391
2392 if (l_count > 0) then
2393 insert into mtl_cst_actual_cost_details (
2394 transaction_id,
2395 organization_id,
2396 layer_id,
2397 cost_element_id,
2398 level_type,
2399 transaction_action_id,
2400 last_update_date,
2401 last_updated_by,
2402 creation_date,
2403 created_by,
2404 last_update_login,
2405 request_id,
2406 program_application_id,
2407 program_id,
2408 program_update_date,
2409 inventory_item_id,
2410 actual_cost,
2411 prior_cost,
2412 new_cost,
2413 insertion_flag,
2414 variance_amount,
2415 user_entered)
2416 select i_txn_id,
2417 i_org_id,
2418 i_layer_id,
2419 clcd.cost_element_id,
2420 clcd.level_type,
2421 i_txn_action_id,
2422 sysdate,
2423 i_user_id,
2424 sysdate,
2425 i_user_id,
2426 i_login_id,
2427 i_req_id,
2428 i_prg_appl_id,
2429 i_prg_id,
2430 sysdate,
2431 i_item_id,
2432 clcd.item_cost,
2433 clcd.item_cost,
2434 clcd.item_cost,
2435 'N',
2436 0,
2437 'N'
2438 from cst_layer_cost_details clcd
2439 where layer_id = i_layer_id;
2440 else
2441 insert into mtl_cst_actual_cost_details (
2442 transaction_id,
2443 organization_id,
2444 layer_id,
2445 cost_element_id,
2446 level_type,
2447 transaction_action_id,
2448 last_update_date,
2449 last_updated_by,
2450 creation_date,
2451 created_by,
2452 last_update_login,
2453 request_id,
2454 program_application_id,
2455 program_id,
2456 program_update_date,
2457 inventory_item_id,
2458 actual_cost,
2459 prior_cost,
2460 new_cost,
2461 insertion_flag,
2462 variance_amount,
2463 user_entered)
2464 select i_txn_id,
2465 i_org_id,
2466 i_layer_id,
2467 1,
2468 1,
2469 i_txn_action_id,
2470 sysdate,
2471 i_user_id,
2472 sysdate,
2473 i_user_id,
2474 i_login_id,
2475 i_req_id,
2476 i_prg_appl_id,
2477 i_prg_id,
2478 sysdate,
2479 i_item_id,
2480 0,
2481 0,
2482 0,
2483 'Y',
2484 0,
2485 'N'
2486 from dual;
2487 end if;
2488 return;
2489 end if;
2490
2491 l_stmt_num := 10;
2492 l_layer_hook := CSTPACHK.layer_hook(
2493 i_org_id,
2494 i_txn_id,
2495 i_layer_id,
2496 i_cost_method,
2497 i_user_id,
2498 i_login_id,
2499 i_req_id,
2500 i_prg_appl_id,
2501 i_prg_id,
2502 l_err_num,
2503 l_err_code,
2504 l_err_msg);
2505 IF l_err_num <> 0 THEN
2506 IF l_debug = 'Y' THEN
2507 l_stmt_num := 15;
2508 fnd_file.put_line(
2509 fnd_file.log,
2510 'CSTPACHK.layer_hook errors out with '||
2511 'l_err_num = '||l_err_num||','||
2512 'l_err_code = '||l_err_code||','||
2513 'l_err_msg = '||l_err_msg
2514 );
2515 END IF;
2516 RAISE process_error;
2517 END IF;
2518 l_stmt_num := 20;
2519 if ((l_layer_hook > 0) OR ((i_txn_action_id = 1) and (i_txn_src_type = 1))
2520 OR ((i_txn_action_id = 29) and (i_txn_qty < 0))
2521 OR ((i_txn_action_id = 32) and (i_txn_src_type = 5))) then
2522
2523 l_expsub := 0;
2524
2525 select transaction_source_id
2526 into l_src_id
2527 from mtl_material_transactions
2528 where transaction_id = i_txn_id;
2529
2530
2531 select subinventory_code
2532 into l_subinv
2533 from mtl_material_transactions
2534 where transaction_id = i_txn_id;
2535
2536 select decode(asset_inventory, 1, 0, 1)
2537 into l_expsub
2538 from mtl_secondary_inventories
2539 where organization_id = i_org_id
2540 and secondary_inventory_name = l_subinv;
2541
2542 if (l_debug = 'Y') then
2543 FND_FILE.PUT_LINE(FND_FILE.LOG,'Layer specific consumption...');
2544 FND_FILE.PUT_LINE(FND_FILE.LOG,'Layer hook : ' || to_char(l_layer_hook));
2545 FND_FILE.PUT_LINE(FND_FILE.LOG,'Source ID : ' || to_char(l_src_id));
2546 end if;
2547 if (l_expsub = 1) then
2548 IF (l_debug = 'Y') THEN
2549 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for the Item Coming from Exp Sub Inv ...');
2550 END IF;
2551
2552 l_stmt_num := 21;
2553 SELECT COUNT(*)
2554 INTO l_count
2555 FROM mtl_cst_txn_cost_details
2556 WHERE transaction_id = i_txn_id
2557 AND organization_id = i_org_id;
2558
2559 IF (l_count > 0) THEN
2560 l_stmt_num := 22;
2561 INSERT
2562 INTO mtl_cst_actual_cost_details (
2563 transaction_id,
2564 organization_id,
2565 layer_id,
2566 cost_element_id,
2567 level_type,
2568 transaction_action_id,
2569 last_update_date,
2570 last_updated_by,
2571 creation_date,
2572 created_by,
2573 last_update_login,
2574 request_id,
2575 program_application_id,
2576 program_id,
2577 program_update_date,
2578 inventory_item_id,
2579 actual_cost,
2580 prior_cost,
2581 new_cost,
2582 insertion_flag,
2583 variance_amount,
2584 user_entered)
2585 SELECT i_txn_id,
2586 i_org_id,
2587 i_layer_id,
2588 ctcd.cost_element_id,
2589 ctcd.level_type,
2590 i_txn_action_id,
2591 sysdate,
2592 i_user_id,
2593 sysdate,
2594 i_user_id,
2595 i_login_id,
2596 i_req_id,
2597 i_prg_appl_id,
2598 i_prg_id,
2599 sysdate,
2600 ctcd.inventory_item_id,
2601 ctcd.transaction_cost,
2602 0,
2603 0,
2604 'Y',
2605 0,
2606 'N'
2607 FROM mtl_cst_txn_cost_details ctcd
2608 WHERE ctcd.transaction_id = i_txn_id
2609 AND ctcd.organization_id = i_org_id ;
2610
2611 else
2612 l_stmt_num := 23;
2613 INSERT
2614 INTO mtl_cst_actual_cost_details (
2615 transaction_id,
2616 organization_id,
2617 layer_id,
2618 cost_element_id,
2619 level_type,
2620 transaction_action_id,
2621 last_update_date,
2622 last_updated_by,
2623 creation_date,
2624 created_by,
2625 last_update_login,
2626 request_id,
2627 program_application_id,
2628 program_id,
2629 program_update_date,
2630 inventory_item_id,
2631 actual_cost,
2632 prior_cost,
2633 new_cost,
2634 insertion_flag,
2635 variance_amount,
2636 user_entered)
2637 SELECT i_txn_id,
2638 i_org_id,
2639 i_layer_id,
2640 1,
2641 1,
2642 i_txn_action_id,
2643 sysdate,
2644 i_user_id,
2645 sysdate,
2646 i_user_id,
2647 i_login_id,
2648 i_req_id,
2649 i_prg_appl_id,
2650 i_prg_id,
2651 sysdate,
2652 i_item_id,
2653 0,
2654 0,
2655 0,
2656 'Y',
2657 0,
2658 'N'
2659 FROM dual;
2660 end if; /* l_count > 0 */
2661 return;
2662 else
2663 get_layers_consumed(
2664 i_txn_qty => i_txn_qty,
2665 i_cost_method => i_cost_method,
2666 i_layer_id => i_layer_id,
2667 consume_mode => 'SPECIFIC',
2668 i_layer_hook => l_layer_hook,
2669 i_src_id => l_src_id,
2670 i_txn_id => i_txn_id,
2671 l_inv_layer_table => l_inv_layer_table,
2672 o_err_num => l_err_num,
2673 o_err_code => l_err_code,
2674 o_err_msg => l_err_msg
2675 );
2676 end if; /* l_expsub =1 */
2677 else
2678 l_expsub := 0;
2679
2680 if (i_txn_action_id in (1, 27, 33, 34)) then
2681 l_stmt_num := 25;
2682 select subinventory_code
2683 into l_subinv
2684 from mtl_material_transactions
2685 where transaction_id = i_txn_id;
2686
2687 select decode(asset_inventory, 1, 0, 1)
2688 into l_expsub
2689 from mtl_secondary_inventories
2690 where organization_id = i_org_id
2691 and secondary_inventory_name = l_subinv;
2692 end if;
2693
2694 if (l_expsub = 1) then
2695 /* For WIP issue/return transactions: cost needed for consumption from
2696 expense subinv for asset item should come from only the next layer that
2697 would be consumed if it were from asset subinv */
2698 l_stmt_num := 27;
2699 if (l_debug = 'Y') then
2700 FND_FILE.PUT_LINE(FND_FILE.LOG,'Consumption from exp subinv');
2701 end if;
2702
2703 get_layers_consumed(
2704 i_txn_id => i_txn_id,
2705 i_txn_qty => i_txn_qty,
2706 i_cost_method => i_cost_method,
2707 i_layer_id => i_layer_id,
2708 consume_mode => 'EXPSUB',
2709 l_inv_layer_table => l_inv_layer_table,
2710 o_err_num => l_err_num,
2711 o_err_code => l_err_code,
2712 o_err_msg => l_err_msg
2713 );
2714 else
2715 l_stmt_num := 30;
2716 if (l_debug = 'Y') then
2717 fnd_file.put_line(fnd_file.log, 'Regular consumption ');
2718 end if;
2719
2720 get_layers_consumed(
2721 i_txn_id => i_txn_id,
2722 i_txn_qty => i_txn_qty,
2723 i_cost_method => i_cost_method,
2724 i_layer_id => i_layer_id,
2725 consume_mode => 'NORMAL',
2726 l_inv_layer_table => l_inv_layer_table,
2727 o_err_num => l_err_num,
2728 o_err_code => l_err_code,
2729 o_err_msg => l_err_msg
2730 );
2731 end if; /* l_expsub = 1 */
2732 end if;
2733 if (l_err_num <> 0) then
2734 raise process_error;
2735 end if;
2736
2737 l_stmt_num := 40;
2738 if (i_cost_hook = 1) then
2739 l_actual_cost_table := 'MCACD';
2740 else
2741 select count(*)
2742 into l_txn_cost_exists
2743 from mtl_cst_txn_cost_details
2744 where transaction_id = i_txn_id
2745 and organization_id = i_org_id;
2746
2747 if (l_txn_cost_exists > 0) then
2748 l_actual_cost_table := 'MCTCD';
2749 else
2750 l_actual_cost_table := 'CILCD';
2751 end if;
2752 end if;
2753
2754 IF l_inv_layer_table.COUNT >0 THEN
2755 For i IN l_inv_layer_table.FIRST..l_inv_layer_table.LAST
2756 LOOP
2757 l_stmt_num := 50;
2758 insert_mclacd(i_txn_id,
2759 i_org_id,
2760 i_item_id,
2761 i_layer_id,
2762 l_inv_layer_table(i).inv_layer_id,
2763 -1*l_inv_layer_table(i).layer_quantity,
2764 i_txn_action_id,
2765 i_user_id,
2766 i_login_id,
2767 i_req_id,
2768 i_prg_id,
2769 i_prg_appl_id,
2770 l_actual_cost_table,
2771 'CILCD',
2772 l_inv_layer_table(i).inv_layer_id,
2773 'CONSUME',
2774 l_err_num,
2775 l_err_code,
2776 l_err_msg);
2777
2778 /* 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 */
2779 if (l_err_num <> 0) then
2780 if (l_err_num = 999) then
2781 l_err_num := 0;
2782 else
2783 raise process_error;
2784 end if;
2785 else
2786
2787 if ((i_exp_flag <> 1)
2788 AND
2789 ((i_txn_action_id = 27 and i_txn_src_type = 1) /* PO Receipt */
2790 OR
2791 (i_txn_action_id = 1 and i_txn_src_type = 1) /* RTV */
2792 OR
2793 (i_txn_action_id = 29 and i_txn_src_type = 1) /* Delivery Adj */
2794 OR
2795 (i_txn_action_id = 31 and i_txn_src_type = 5) /*WIP completion*/
2796 OR
2797 (i_txn_action_id = 6) /* Change of ownership */
2798
2799 OR
2800 (i_txn_action_id = 32 and i_txn_src_type = 5) /* Assembly completion*/
2801 OR
2802 (i_interorg_rec = 1) /*Interorg receipt*/
2803 )
2804 ) then
2805 l_stmt_num :=60;
2806 if (l_debug = 'Y') then
2807 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling apply_layer_material_ovhd...');
2808 end if;
2809 apply_layer_material_ovhd(
2810 i_org_id,
2811 i_txn_id,
2812 i_layer_id,
2813 l_inv_layer_table(i).inv_layer_id,
2814 l_inv_layer_table(i).layer_quantity,
2815 i_cost_type,
2816 i_mat_ct_id,
2817 i_avg_rates_id,
2818 i_item_id,
2819 i_txn_qty,
2820 i_txn_action_id,
2821 1,
2822 i_user_id,
2823 i_login_id,
2824 i_req_id,
2825 i_prg_appl_id,
2826 i_prg_id,
2827 i_interorg_rec, --bug 2280515
2828 l_err_num,
2829 l_err_code,
2830 l_err_msg
2831 );
2832
2833 if (l_err_num <> 0) then
2834 raise process_error;
2835 end if;
2836 end if;
2837 end if; /* l_err_num = 999 */
2838 l_stmt_num := 70;
2839 if ((nvl(i_interorg_rec,-1) <> 3) and (i_exp_flag <> 1)) then
2840 update cst_inv_layers
2841 set layer_quantity = nvl(layer_quantity,0)-l_inv_layer_table(i).layer_quantity
2842 where inv_layer_id = l_inv_layer_table(i).inv_layer_id;
2843 if (l_debug = 'Y') then
2844 FND_FILE.PUT_LINE(FND_FILE.LOG,'CIL.layer_qty changed by ' || to_char(l_inv_layer_table(i).layer_quantity));
2845 end if;
2846 end if;
2847 END LOOP;
2848 END IF; /* IF l_inv_layer_table.COUNT >0 THEN */
2849 EXCEPTION
2850 when process_error then
2851 o_err_num := l_err_num;
2852 o_err_code := l_err_code;
2853 o_err_msg := l_err_msg;
2854 when others then
2855 rollback;
2856 o_err_num := SQLCODE;
2857 o_err_msg := 'CSTPLENG.consume_layers (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
2858
2859 END consume_layers;
2860
2861 ----------------------------------------------------------------------------
2862 -- PROCEDURE --
2863 -- get_layers_consumed --
2864 -- --
2865 -- DESCRIPTION --
2866 -- This procedure decides which inventory layers need to be consumed --
2867 -- for the specified issue transaction. The output of this procedure is --
2868 -- a table of inventory layer ids and the quantity that should be --
2869 -- consumed from each layer. --
2870 -- --
2871 -- PARAMETERS: --
2872 -- i_txn_qty : quantity that needs to be consumed in primary --
2873 -- UOM --
2874 -- i_cost_method : cost method of the organization (5 for FIFO, 6 --
2875 -- for LIFO) --
2876 -- i_layer_id : cost group layer id --
2877 -- consume_mode : consumption mode (EXPSUB for Issues of asset --
2878 -- items from expense subinventories, SPECIFIC for --
2879 -- layer_hook, return to receiving, correction, --
2880 -- assembly return, NORMAL for all others) --
2881 -- i_layer_hook : specific custom layer that should be consumed --
2882 -- i_src_id : source id (PO Receipt for return to receiving, --
2883 -- corrections, Job for assembly return, NULL for --
2884 -- all others) --
2885 -- i_txn_id : issue transaction id --
2886 -- l_inv_layer_table : inventory layers that should be consumed --
2887 ----------------------------------------------------------------------------
2888 PROCEDURE get_layers_consumed (
2889 i_txn_qty IN NUMBER,
2890 i_cost_method IN NUMBER,
2891 i_layer_id IN NUMBER,
2892 consume_mode IN VARCHAR2,
2893 i_layer_hook IN NUMBER,
2894 i_src_id IN NUMBER,
2895 i_txn_id IN NUMBER,
2896 l_inv_layer_table IN OUT NOCOPY cst_layer_tbl_type,
2897 o_err_num OUT NOCOPY NUMBER,
2898 o_err_code OUT NOCOPY VARCHAR2,
2899 o_err_msg OUT NOCOPY VARCHAR2
2900 )
2901 IS
2902 l_stmt_num NUMBER;
2903 l_debug VARCHAR2(80);
2904 l_required_qty NUMBER;
2905 l_custom_layer NUMBER;
2906 l_source_id NUMBER;
2907 l_inv_layer_id NUMBER;
2908 l_pos_layer_exist NUMBER;
2909 l_layers_hook NUMBER;
2910 l_rtr NUMBER;
2911 l_rtr_txn_id NUMBER;
2912 l_custom_layers CSTPACHK.inv_layer_tbl;
2913 l_layers_list VARCHAR2(240);
2914 sql_stmt VARCHAR2(360);
2915 l_inv_layer_rec cst_layer_rec_type;
2916 inv_layer_cursor LayerCurType;
2917 l_err_num NUMBER;
2918 l_err_code VARCHAR2(240);
2919 l_err_msg VARCHAR2(240);
2920 process_error EXCEPTION;
2921 BEGIN
2922 l_stmt_num := 0;
2923 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
2924 l_debug := 'Y';
2925 l_required_qty := ABS(i_txn_qty);
2926 l_custom_layer := NVL(i_layer_hook,-1);
2927 l_source_id := NVL(i_src_id,-1);
2928 l_rtr_txn_id := 0;
2929 l_custom_layers := CSTPACHK.inv_layer_tbl();
2930 l_err_num := 0;
2931 l_err_code := '';
2932 l_err_msg := '';
2933 IF l_debug = 'Y' THEN
2934 fnd_file.put_line(
2935 fnd_file.log,
2936 'Entering get_layers_consumed for transaction '||i_txn_id||
2937 ' and a required quantity of '||l_required_qty||
2938 ' with a consumption mode of '||consume_mode
2939 );
2940 END IF;
2941 l_stmt_num := 5;
2942
2943 /* For issues of asset items from expense subinventories, we don't consume any layers
2944 Instead, we just need to get a reference cost from the earliest / latest layer */
2945
2946 IF consume_mode = 'EXPSUB' THEN
2947 IF l_debug = 'Y' THEN
2948 fnd_file.put_line(fnd_file.log,'EXPSUB consumption');
2949 END IF;
2950 IF i_cost_method = 5 THEN
2951 l_stmt_num := 10;
2952 SELECT MIN(inv_layer_id)
2953 INTO l_inv_layer_id
2954 FROM cst_inv_layers
2955 WHERE layer_id = i_layer_id
2956 AND layer_quantity > 0;
2957 ELSE
2958 l_stmt_num := 15;
2959 SELECT MAX(inv_layer_id)
2960 INTO l_inv_layer_id
2961 FROM cst_inv_layers
2962 WHERE layer_id = i_layer_id
2963 AND layer_quantity > 0;
2964 END IF;
2965 /* If no positive layers exist, pick the latest layer */
2966 IF l_inv_layer_id IS NULL THEN
2967 l_stmt_num := 20;
2968 SELECT MAX(inv_layer_id)
2969 INTO l_inv_layer_id
2970 FROM cst_inv_layers
2971 WHERE layer_id = i_layer_id;
2972 END IF;
2973 l_inv_layer_rec.inv_layer_id := l_inv_layer_id;
2974 l_inv_layer_rec.layer_quantity := l_required_qty;
2975 l_stmt_num := 25;
2976 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
2977 l_required_qty := 0;
2978 RETURN;
2979 END IF;
2980
2981 /* For issues from asset subinventories, consume the layers in the following order
2982 1. Positive quantity in the layer specified by the layer hook
2983 2. Drive the layer specified by the layer hook negative only if there are no
2984 other positive layers
2985 3. Positive quantity from the layers specified by the layers hook in the order
2986 that they are specified
2987 4. Positive quantity from the layer that was created for the delivery that this
2988 return / correction correspond to
2989 5. Positive quantity from the layers that was created for the deliveries for
2990 the same PO or completions from the same job in FIFO/LIFO manner
2991 6. Drive the earliest / latest layer that was created for the deliveries for the
2992 same PO or completions from the same job negative only if there are
2993 no other positive layers
2994 7. Positive quantity from all layers in FIFO/LIFO manner
2995 8. Drive the overall earliest / latest layer negative
2996
2997 1 and 2 are applicable only when layer hook is used.
2998 3 is applicable only when layers hook is used.
2999 4 is applicable only for returns to receiving / corrections.
3000 5 and 6 are applicable only for returns to receiving, corrections and assembly
3001 returns. */
3002
3003 /* 1. Positive quantity in the layer specified by the layer hook */
3004 IF (l_custom_layer > 0) AND (l_required_qty > 0) THEN
3005 IF l_debug = 'Y' THEN
3006 fnd_file.put_line(fnd_file.log,'Trying custom layer '||l_custom_layer);
3007 END IF;
3008 l_stmt_num := 30;
3009 SELECT inv_layer_id, layer_quantity
3010 INTO l_inv_layer_rec.inv_layer_id,l_inv_layer_rec.layer_quantity
3011 FROM cst_inv_layers
3012 WHERE inv_layer_id = l_custom_layer -- inventory layer id exists
3013 AND layer_id = i_layer_id; -- correct organization, item, cost group
3014 IF l_inv_layer_rec.layer_quantity > 0 THEN
3015 IF l_required_qty < l_inv_layer_rec.layer_quantity THEN
3016 l_inv_layer_rec.layer_quantity := l_required_qty;
3017 END IF;
3018 IF l_debug = 'Y' THEN
3019 fnd_file.put_line(
3020 fnd_file.log,
3021 'Using custom layer '||l_custom_layer||' for '||l_inv_layer_rec.layer_quantity
3022 );
3023 END IF;
3024 l_required_qty := l_required_qty - l_inv_layer_rec.layer_quantity;
3025 l_stmt_num := 35;
3026 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3027 END IF;
3028 END IF;
3029 /* End of 1 */
3030
3031 /* 2. Drive the layer specified by the layer hook negative only if there are no
3032 other positive layers */
3033 IF (l_custom_layer > 0) AND (l_required_qty > 0) THEN
3034 IF l_debug = 'Y' THEN
3035 fnd_file.put_line(fnd_file.log,'Driving custom layer '||l_custom_layer||' negative?');
3036 END IF;
3037 l_stmt_num := 40;
3038 SELECT count(*)
3039 INTO l_pos_layer_exist
3040 FROM cst_inv_layers
3041 WHERE layer_id = i_layer_id
3042 AND inv_layer_id <> l_custom_layer
3043 AND layer_quantity > 0;
3044 IF l_pos_layer_exist = 0 THEN
3045 IF l_debug = 'Y' THEN
3046 fnd_file.put_line(fnd_file.log,'Driving custom layer '||l_custom_layer||' negative');
3047 END IF;
3048 l_inv_layer_rec.inv_layer_id := l_custom_layer;
3049 l_inv_layer_rec.layer_quantity := l_required_qty;
3050 l_required_qty := 0;
3051 l_stmt_num := 45;
3052 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3053 END IF;
3054 END IF;
3055 /* End of 2 */
3056
3057 /* 3. Positive quantity from the layers specified in the layers hook in the order that they are specified */
3058 IF l_required_qty > 0 THEN
3059 IF l_debug = 'Y' THEN
3060 fnd_file.put_line(fnd_file.log,'Trying custom layers');
3061 END IF;
3062 l_stmt_num := 50;
3063 CSTPACHK.layers_hook (
3064 i_txn_id => i_txn_id,
3065 i_required_qty => l_required_qty,
3066 i_cost_method => i_cost_method,
3067 o_custom_layers => l_custom_layers,
3068 o_err_num => l_err_num,
3069 o_err_code => l_err_code,
3070 o_err_msg => l_err_msg
3071 );
3072 IF l_err_num <> 0 THEN
3073 fnd_file.put_line(fnd_file.log,'Error in calling CSTPACHK.layers_hook');
3074 RAISE process_error;
3075 END IF;
3076 l_layers_hook := 0;
3077 l_layers_list := '(-1';
3078 IF l_debug = 'Y' THEN
3079 fnd_file.put_line(fnd_file.log,'There are '||l_custom_layers.COUNT||' custom layers');
3080 END IF;
3081 IF l_custom_layers.COUNT > 0 THEN
3082 FOR i IN l_custom_layers.FIRST..l_custom_layers.LAST LOOP
3083 EXIT WHEN l_required_qty = 0;
3084 IF l_debug = 'Y' THEN
3085 fnd_file.put_line(
3086 fnd_file.log,
3087 'Trying to consume '||l_custom_layers(i).layer_quantity||
3088 ' from custom layer '||l_custom_layers(i).inv_layer_id
3089 );
3090 END IF;
3091 l_stmt_num := 55;
3092 BEGIN
3093 SELECT inv_layer_id, l_custom_layers(i).layer_quantity
3094 INTO l_inv_layer_rec.inv_layer_id, l_inv_layer_rec.layer_quantity
3095 FROM cst_inv_layers
3096 WHERE inv_layer_id = l_custom_layers(i).inv_layer_id -- valid inventory layer id
3097 AND layer_id = i_layer_id -- valid org, item, cost group
3098 AND layer_quantity >=
3099 l_custom_layers(i).layer_quantity -- enough quantity
3100 AND l_custom_layers(i).layer_quantity > 0; -- positive quanttiy
3101 EXCEPTION
3102 WHEN NO_DATA_FOUND THEN
3103 l_err_num := -1;
3104 l_err_msg := 'Custom layer '||l_custom_layers(i).inv_layer_id||
3105 ' and quantity '||l_custom_layers(i).layer_quantity||
3106 ' is not valid';
3107 fnd_file.put_line(
3108 fnd_file.log, l_err_msg
3109 );
3110 RAISE process_error;
3111 END;
3112 -- ignore the layer if it has been specified by the layer hook to avoid double counting.
3113 IF l_inv_layer_rec.inv_layer_id <> l_custom_layer THEN
3114 IF l_inv_layer_rec.layer_quantity > l_required_qty THEN
3115 l_inv_layer_rec.layer_quantity := l_required_qty;
3116 END IF;
3117 l_required_qty := l_required_qty - l_inv_layer_rec.layer_quantity;
3118 l_stmt_num := 60;
3119 IF l_debug = 'Y' THEN
3120 fnd_file.put_line(
3121 fnd_file.log,
3122 'Using custom layer '||l_custom_layers(i).inv_layer_id||
3123 ' for '||l_inv_layer_rec.layer_quantity
3124 );
3125 END IF;
3126 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3127 l_layers_hook := 1;
3128 l_layers_list := l_layers_list || ',' || l_inv_layer_rec.inv_layer_id;
3129 END IF;
3130 END LOOP;
3131 END IF;
3132 l_layers_list := l_layers_list || ')';
3133 END IF;
3134 /* End of 3 */
3135
3136 /* 4. Positive quantity from the layer that was created for the delivery that this
3137 return / correction corresponds to */
3138 IF (consume_mode = 'SPECIFIC') AND (i_src_id IS NOT NULL) AND (l_required_qty > 0) then
3139 IF l_debug = 'Y' THEN
3140 fnd_file.put_line(fnd_file.log,'Trying original delivery');
3141 END IF;
3142 -- check if the current transaction is a return to receiving or correction
3143 l_stmt_num := 65;
3144 SELECT COUNT(*)
3145 INTO l_rtr
3146 FROM mtl_material_transactions
3147 WHERE transaction_id = i_txn_id
3148 AND transaction_action_id in (1, 29)
3149 AND transaction_source_type_id = 1;
3150 -- if it is, try to first consume the inv layer created by the receipt
3151 -- that this return is performed against
3152 IF l_rtr = 1 THEN
3153 l_stmt_num := 70;
3154 BEGIN
3155 SELECT mmt_del.transaction_id
3156 INTO l_rtr_txn_id
3157 FROM mtl_material_transactions mmt_del,
3158 mtl_material_transactions mmt_rtr,
3159 rcv_transactions rt_rtr
3160 WHERE mmt_del.rcv_transaction_id = rt_rtr.parent_transaction_id
3161 AND rt_rtr.transaction_id = mmt_rtr.rcv_transaction_id
3162 AND mmt_rtr.transaction_id = i_txn_id;
3163 EXCEPTION
3164 WHEN OTHERS THEN
3165 IF (l_debug = 'Y') THEN
3166 FND_FILE.PUT_LINE(
3167 FND_FILE.LOG,
3168 'No delivery is found for transaction ' || i_txn_id
3169 );
3170 END IF;
3171 END;
3172 l_stmt_num := 75;
3173 sql_stmt := 'SELECT inv_layer_id, layer_quantity'
3174 ||' FROM cst_inv_layers'
3175 ||' WHERE create_transaction_id = :i'
3176 ||' AND layer_quantity > 0'
3177 ||' AND inv_layer_id <> :j';
3178 IF l_layers_hook > 0 THEN
3179 l_stmt_num := 80;
3180 sql_stmt := sql_stmt || ' AND inv_layer_id NOT IN '|| l_layers_list;
3181 END IF;
3182 IF l_debug = 'Y' THEN
3183 fnd_file.put_line(
3184 fnd_file.log,
3185 'Using SQL '||sql_stmt||' with '||l_rtr_txn_id||','||l_custom_layer
3186 );
3187 END IF;
3188 OPEN inv_layer_cursor FOR sql_stmt USING l_rtr_txn_id, l_custom_layer;
3189 l_stmt_num := 85;
3190 populate_layer_table(
3191 l_inv_layer_table => l_inv_layer_table,
3192 inv_layer_cursor => inv_layer_cursor,
3193 i_qty_required => l_required_qty,
3194 o_err_num => l_err_num,
3195 o_err_code => l_err_code,
3196 o_err_msg => l_err_msg
3197 );
3198 CLOSE inv_layer_cursor;
3199 END IF; -- l_rtr = 1
3200 END IF;
3201 /* End of 4 */
3202
3203 /* 5. Positive quantity from the layers that was created for the deliveries for
3204 the same PO or completions from the same job in FIFO/LIFO manner */
3205 IF (consume_mode = 'SPECIFIC') AND (i_src_id IS NOT NULL) AND (l_required_qty > 0) THEN
3206 IF l_debug = 'Y' THEN
3207 fnd_file.put_line(fnd_file.log,'Trying other layers with the same source');
3208 END IF;
3209 l_stmt_num := 90;
3210 sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers'
3211 ||' WHERE layer_id = :i AND transaction_source_id = :j AND layer_quantity > 0 '
3212 ||' AND create_transaction_id <> :k AND inv_layer_id <> :l';
3213 IF l_layers_hook > 0 THEN
3214 l_stmt_num := 95;
3215 sql_stmt := sql_stmt || ' AND inv_layer_id NOT IN ' || l_layers_list;
3216 END IF;
3217 IF i_cost_method = 6 THEN
3218 l_stmt_num := 100;
3219 sql_stmt := sql_stmt || ' ORDER BY creation_date DESC, inv_layer_id DESC';
3220 ELSE
3221 l_stmt_num := 105;
3222 sql_stmt := sql_stmt || ' ORDER BY creation_date, inv_layer_id';
3223 END IF;
3224 IF l_debug = 'Y' THEN
3225 fnd_file.put_line(
3226 fnd_file.log,
3227 'Using SQL '||sql_stmt||' with '||i_layer_id||','||l_source_id||
3228 ','||l_rtr_txn_id||','||l_custom_layer
3229 );
3230 END IF;
3231 OPEN inv_layer_cursor FOR sql_stmt USING i_layer_id,l_source_id,l_rtr_txn_id,l_custom_layer;
3232 l_stmt_num := 110;
3233 populate_layer_table(
3234 l_inv_layer_table => l_inv_layer_table,
3235 inv_layer_cursor => inv_layer_cursor,
3236 i_qty_required => l_required_qty,
3237 o_err_num => l_err_num,
3238 o_err_code => l_err_code,
3239 o_err_msg => l_err_msg
3240 );
3241 CLOSE inv_layer_cursor;
3242 END IF;
3243 /* End of 5 */
3244
3245 /* 6. Drive the earliest / latest layer that was created for the deliveries for the
3246 same PO or completions from the same job negative only if there are
3247 no other positive layers */
3248 IF (consume_mode = 'SPECIFIC') AND (i_src_id IS NOT NULL) AND (l_required_qty > 0) THEN
3249 IF l_debug = 'Y' THEN
3250 fnd_file.put_line(
3251 fnd_file.log,
3252 'Driving earliest/latest layer with the same source negative?'
3253 );
3254 END IF;
3255 l_stmt_num := 115;
3256 sql_stmt := 'SELECT inv_layer_id, layer_quantity FROM cst_inv_layers'
3257 ||' WHERE layer_id = :i AND inv_layer_id <> :j'
3258 ||' AND NVL(transaction_source_id,-2) <> :k'
3259 ||' AND layer_quantity > 0';
3260 IF l_layers_hook > 0 THEN
3261 l_stmt_num := 120;
3262 sql_stmt := sql_stmt || ' AND inv_layer_id NOT IN ' || l_layers_list;
3263 END IF;
3264 IF l_debug = 'Y' THEN
3265 fnd_file.put_line(
3266 fnd_file.log,
3267 'Using SQL '||sql_stmt||' with '||i_layer_id||','||l_custom_layer||
3268 ','||l_source_id
3269 );
3270 END IF;
3271 OPEN inv_layer_cursor FOR sql_stmt USING i_layer_id,l_custom_layer,l_source_id;
3272 FETCH inv_layer_cursor INTO l_inv_layer_rec.inv_layer_id, l_inv_layer_rec.layer_quantity;
3273 IF inv_layer_cursor%NOTFOUND THEN
3274 IF i_cost_method = 5 THEN
3275 l_stmt_num := 125;
3276 SELECT MAX(inv_layer_id)
3277 INTO l_inv_layer_rec.inv_layer_id
3278 FROM cst_inv_layers
3279 WHERE layer_id = i_layer_id
3280 AND transaction_source_id = l_source_id;
3281 ELSE
3282 l_stmt_num := 130;
3283 SELECT MIN(inv_layer_id)
3284 INTO l_inv_layer_rec.inv_layer_id
3285 FROM cst_inv_layers
3286 WHERE layer_id = i_layer_id
3287 AND transaction_source_id = l_source_id;
3288 END IF;
3289 IF l_inv_layer_rec.inv_layer_id IS NOT NULL THEN
3290 IF l_debug = 'Y' THEN
3291 fnd_file.put_line(
3292 fnd_file.log,
3293 'Driving earliest/latest layer with the same source negative'
3294 );
3295 END IF;
3296 l_inv_layer_rec.layer_quantity := l_required_qty;
3297 l_stmt_num := 135;
3298 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3299 l_required_qty := 0;
3300 END IF;
3301 END IF;
3302 CLOSE inv_layer_cursor;
3303 END IF;
3304 /* End of 6 */
3305
3306 /* 7. Positive quantity from all layers in FIFO/LIFO manner */
3307 IF l_required_qty > 0 THEN
3308 IF l_debug = 'Y' THEN
3309 fnd_file.put_line(fnd_file.log,'General consumption');
3310 END IF;
3311 l_stmt_num := 140;
3312 sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i'
3313 ||' AND inv_layer_id <> :j AND NVL(transaction_source_id,-2) <> :k'
3314 ||' AND layer_quantity > 0';
3315 l_stmt_num := 145;
3316 IF l_layers_hook > 0 THEN
3317 sql_stmt := sql_stmt || ' AND inv_layer_id NOT IN '|| l_layers_list;
3318 END IF;
3319 IF i_cost_method = 6 THEN
3320 l_stmt_num := 150;
3321 sql_stmt := sql_stmt || ' ORDER BY creation_date DESC, inv_layer_id DESC';
3322 ELSE
3323 l_stmt_num := 155;
3324 sql_stmt := sql_stmt || ' ORDER BY creation_date, inv_layer_id';
3325 END IF;
3326 IF l_debug = 'Y' THEN
3327 fnd_file.put_line(
3328 fnd_file.log,
3329 'Using SQL '||sql_stmt||' with '||i_layer_id||','||l_custom_layer||
3330 ','||l_source_id
3331 );
3332 END IF;
3333 OPEN inv_layer_cursor FOR sql_stmt USING i_layer_id,l_custom_layer,l_source_id;
3334 l_stmt_num := 160;
3335 populate_layer_table(
3336 l_inv_layer_table => l_inv_layer_table,
3337 inv_layer_cursor => inv_layer_cursor,
3338 i_qty_required => l_required_qty,
3339 o_err_num => l_err_num,
3340 o_err_code => l_err_code,
3341 o_err_msg => l_err_msg
3342 );
3343 CLOSE inv_layer_cursor;
3344 END IF;
3345 /* End of 7 */
3346
3347 /* 8. Drive the overall earliest / latest layer negative */
3348 IF l_required_qty > 0 THEN
3349 IF l_debug = 'Y' THEN
3350 fnd_file.put_line(fnd_file.log,'Driving earliest/latest layer negative');
3351 END IF;
3352 IF l_debug = 'Y' THEN
3353 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_neg_qty ' || to_char(l_required_qty));
3354 END IF;
3355 l_stmt_num := 165;
3356 sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i';
3357 IF i_cost_method = 5 THEN
3358 sql_stmt := sql_stmt || ' ORDER BY creation_date DESC,inv_layer_id DESC';
3359 ELSE
3360 sql_stmt := sql_stmt || ' ORDER BY creation_date,inv_layer_id';
3361 END IF;
3362 IF l_debug = 'Y' THEN
3363 fnd_file.put_line(fnd_file.log,'Using SQL '||sql_stmt||' with '||i_layer_id);
3364 END IF;
3365 OPEN inv_layer_cursor FOR sql_stmt USING i_layer_id;
3366 FETCH inv_layer_cursor into l_inv_layer_rec.inv_layer_id,l_inv_layer_rec.layer_quantity;
3367 l_inv_layer_rec.layer_quantity := l_required_qty;
3368 l_stmt_num := 170;
3369 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3370 CLOSE inv_layer_cursor;
3371 END IF;
3372 /* End of 8 */
3373
3374 EXCEPTION
3375 when process_error then
3376 o_err_num := l_err_num;
3377 o_err_code := l_err_code;
3378 o_err_msg := l_err_msg;
3379 when others then
3380 rollback;
3381 FND_FILE.PUT_LINE(FND_FILE.LOG,SQLCODE ||' ' ||to_char(l_stmt_num)||' '||substr(SQLERRM,1,200));
3382 o_err_num := SQLCODE;
3383 o_err_msg := 'CSTPLENG.get_layers_consumed (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3384 END get_layers_consumed;
3385
3386 ----------------------------------------------------------------------------
3387 -- PROCEDURE --
3388 -- populate_layer_table --
3389 -- --
3390 -- DESCRIPTION --
3391 -- * This procedure loops through the records fetched by the layer --
3392 -- cursor (IN parameter), and populates the PL/SQL table with the --
3393 -- inventory layer ID and the quantity that needs to be consumed --
3394 -- from that layer --
3395 -- * If no layers are fetched, the procedure issues a return --
3396 -- --
3397 -- PURPOSE: --
3398 -- a single function to loop through records fetched by the inventory --
3399 -- layer cursor and populate the PL/SQL table with the inv layer ID --
3400 -- and the quantity to be consuemd from that layer --
3401 -- --
3402 -- PARAMETERS: --
3403 -- l_inv_layer_table : PL/SQL parameter that is populated --
3404 -- inv_layer_cursor : cursor that is used to fetch inventory layers --
3405 -- i_qty_required : total quantity left to be consumed --
3406 ----------------------------------------------------------------------------
3407 PROCEDURE populate_layer_table(l_inv_layer_table IN OUT NOCOPY cst_layer_tbl_type,
3408 inv_layer_cursor IN LayerCurType,
3409 i_qty_required IN OUT NOCOPY NUMBER,
3410 o_err_num OUT NOCOPY NUMBER,
3411 o_err_code OUT NOCOPY VARCHAR2,
3412 o_err_msg OUT NOCOPY VARCHAR2)
3413 IS
3414 l_inv_layer_rec cst_layer_rec_type;
3415 l_stmt_num NUMBER;
3416 l_err_num NUMBER;
3417 l_err_code VARCHAR2(240);
3418 l_err_msg VARCHAR2(240);
3419 process_error EXCEPTION;
3420 BEGIN
3421 l_stmt_num := 0;
3422 l_err_num := 0;
3423 l_err_code := '';
3424 l_err_msg := '';
3425
3426 while (i_qty_required > 0) LOOP
3427 l_stmt_num := 20;
3428 FETCH inv_layer_cursor into l_inv_layer_rec.inv_layer_id,
3429 l_inv_layer_rec.layer_quantity;
3430 EXIT WHEN inv_layer_cursor%NOTFOUND;
3431
3432 if (i_qty_required < l_inv_layer_rec.layer_quantity) then
3433 l_stmt_num := 30;
3434 l_inv_layer_rec.layer_quantity := i_qty_required;
3435 end if;
3436
3437 l_stmt_num := 40;
3438 i_qty_required := i_qty_required - l_inv_layer_rec.layer_quantity;
3439
3440 l_stmt_num := 50;
3441 insert_record(l_inv_layer_rec,l_inv_layer_table,l_err_num,l_err_code,l_err_msg);
3442
3443 END LOOP;
3444
3445 EXCEPTION
3446 when process_error then
3447 o_err_num := l_err_num;
3448 o_err_code := l_err_code;
3449 o_err_msg := l_err_msg;
3450 when others then
3451 rollback;
3452 o_err_num := SQLCODE;
3453 o_err_msg := 'CSTPLENG.populate_layer_table (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3454 END populate_layer_table;
3455
3456 ----------------------------------------------------------------------------
3457 -- PROCEDURE --
3458 -- insert_record --
3459 -- --
3460 -- DESCRIPTION --
3461 -- This procedure extends the PL/SQL table and inserts a new record --
3462 -- --
3463 -- PURPOSE: --
3464 -- This procedure extends the PL/SQL table and inserts a new record --
3465 -- --
3466 -- PARAMETERS: --
3467 -- l_inv_layer_rec : record to be inserted --
3468 -- l_inv_layer_table : PL/SQL table into which record is inserted --
3469 ----------------------------------------------------------------------------
3470 PROCEDURE insert_record(l_inv_layer_rec IN cst_layer_rec_type,
3471 l_inv_layer_table IN OUT NOCOPY cst_layer_tbl_type,
3472 o_err_num OUT NOCOPY NUMBER,
3473 o_err_code OUT NOCOPY VARCHAR2,
3474 o_err_msg OUT NOCOPY VARCHAR2) IS
3475 l_stmt_num NUMBER;
3476 l_err_num NUMBER;
3477 l_err_code VARCHAR2(240);
3478 l_err_msg VARCHAR2(240);
3479 l_next_record NUMBER;
3480 process_error EXCEPTION;
3481
3482 BEGIN
3483 l_stmt_num := 0;
3484 l_err_num := 0;
3485 l_err_code := '';
3486 l_err_msg := '';
3487
3488 l_stmt_num := 10;
3489 l_next_record := nvl(l_inv_layer_table.LAST,0);
3490
3491 l_stmt_num := 20;
3492 l_inv_layer_table.extend;
3493
3494 l_stmt_num := 30;
3495 l_next_record := nvl(l_inv_layer_table.LAST,0);
3496
3497 l_stmt_num := 40;
3498 l_inv_layer_table(l_next_record).inv_layer_id := l_inv_layer_rec.inv_layer_id;
3499 l_inv_layer_table(l_next_record).layer_quantity := l_inv_layer_rec.layer_quantity;
3500 EXCEPTION
3501 when process_error then
3502 o_err_num := l_err_num;
3503 o_err_code := l_err_code;
3504 o_err_msg := l_err_msg;
3505 when others then
3506 rollback;
3507 o_err_num := SQLCODE;
3508 o_err_msg := 'CSTPLENG.insert_record (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3509 END insert_record;
3510
3511
3512 ---------------------------------------------------------------------------
3513 -- PROCEDURE apply_layer_material_ovh
3514 -- created by Dieu-Thuong Le 21-Apr-2000
3515
3516 -- This procedure will compute material overheads based on the rates
3517 -- defined in the rate cost type specified for the current costing org.
3518 -- The computed movh will be applied to this level material overhead of
3519 -- the specified inventory layer.
3520 -- This procedure will insert MACS rows for each applicable material
3521 -- overhead and insert or update MCLACD.
3522 -- Inserting MCLACD: populate both layer_cost and actual_cost with the
3523 -- total costs of all MACS rows created for tranx.
3524 -- Updating MCLACD: add MACS costs to actual_cost, layer_cost will
3525 -- not be touched.
3526 -- Assumption: when this procedure is called, MCLACD material row should
3527 -- already be inserted for the transaction.
3528 ---------------------------------------------------------------------------
3529 procedure apply_layer_material_ovhd(
3530 I_ORG_ID IN NUMBER,
3531 I_TXN_ID IN NUMBER,
3532 I_LAYER_ID IN NUMBER,
3533 I_INV_LAYER_ID IN NUMBER,
3534 I_LAYER_QTY IN NUMBER,
3535 I_COST_TYPE IN NUMBER,
3536 I_MAT_CT_ID IN NUMBER,
3537 I_AVG_RATES_ID IN NUMBER,
3538 I_ITEM_ID IN NUMBER,
3539 I_TXN_QTY IN NUMBER,
3540 I_TXN_ACTION_ID IN NUMBER,
3541 I_LEVEL IN NUMBER,
3542 I_USER_ID IN NUMBER,
3543 I_LOGIN_ID IN NUMBER,
3544 I_REQ_ID IN NUMBER,
3545 I_PRG_APPL_ID IN NUMBER,
3546 I_PRG_ID IN NUMBER,
3547 I_INTERORG_REC IN NUMBER, --bug 2280515:anjgupta
3548 O_Err_Num OUT NOCOPY NUMBER,
3549 O_Err_Code OUT NOCOPY VARCHAR2,
3550 O_Err_Msg OUT NOCOPY VARCHAR2
3551 ) IS
3552 l_mat_ovhds NUMBER;
3553 l_item_cost NUMBER;
3554 l_res_id NUMBER;
3555 l_err_num NUMBER;
3556 l_err_code VARCHAR2(240);
3557 l_err_msg VARCHAR2(240);
3558 l_stmt_num NUMBER;
3559 overhead_error EXCEPTION;
3560 avg_rates_no_ovhd EXCEPTION;
3561 l_mclacd_ovhd NUMBER;
3562 l_ovhd_cost NUMBER;
3563 l_macs_ovhd NUMBER;
3564 l_elemental_visible VARCHAR2(1);
3565 l_from_org NUMBER;
3566 l_to_org NUMBER;
3567 l_txn_org_id NUMBER;
3568 l_txfr_org_id NUMBER;
3569 l_txn_qty NUMBER;
3570 l_txn_type_id NUMBER;
3571 l_debug VARCHAR2(80);
3572
3573 /* moh variables */
3574 l_earn_moh NUMBER;
3575 l_return_status VARCHAR2(1);
3576 l_msg_count NUMBER;
3577 l_msg_data VARCHAR2(240);
3578 moh_rules_error EXCEPTION;
3579 l_default_MOH_subelement NUMBER;-------------------Bug 3959770
3580
3581
3582 BEGIN
3583 -- initialize local variables
3584 l_err_num := 0;
3585 l_err_code := '';
3586 l_err_msg := '';
3587 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
3588 l_earn_moh := 1;
3589 l_return_status := fnd_api.g_ret_sts_success;
3590 l_msg_count := 0;
3591
3592 /* BUG 3959770*/
3593 /* Get the Default MOH sub element of the organization*/
3594
3595 select DEFAULT_MATL_OVHD_COST_ID
3596 into l_default_MOH_subelement
3597 from mtl_parameters
3598 where organization_id= I_ORG_ID;
3599
3600
3601 -- Find out if there are any material overhead rows for the layer
3602 -- which have actual cost value.
3603
3604 if(l_debug = 'Y') then
3605 fnd_file.put_line(fnd_file.log, 'In apply_layer_material_ovhd!!!!');
3606 end if;
3607
3608 l_stmt_num := 10;
3609 /* Changes for MOH Absorption Rules */
3610
3611 cst_mohRules_pub.apply_moh(
3612 1.0,
3613 p_organization_id => i_org_id,
3614 p_earn_moh =>l_earn_moh,
3615 p_txn_id => i_txn_id,
3616 p_item_id => i_item_id,
3617 x_return_status => l_return_status,
3618 x_msg_count => l_msg_count,
3619 x_msg_data => l_msg_data);
3620
3621 IF l_return_status <> FND_API.g_ret_sts_success THEN
3622
3623 CST_UTILITY_PUB.writelogmessages
3624 ( p_api_version => 1.0,
3625 p_msg_count => l_msg_count,
3626 p_msg_data => l_msg_data,
3627 x_return_status => l_return_status);
3628 RAISE moh_rules_error;
3629 END IF;
3630
3631 IF(l_earn_moh = 0) THEN
3632 fnd_file.put_line(fnd_file.log, '--Material Overhead Absorption Overidden--'); ELSE
3633
3634 l_stmt_num := 11;
3635
3636 select count(*)
3637 into l_mat_ovhds
3638 from mtl_cst_layer_act_cost_details
3639 where transaction_id = i_txn_id
3640 and organization_id = i_org_id
3641 and layer_id = i_layer_id -- cost group layer
3642 and inv_layer_id = i_inv_layer_id -- inventory layer
3643 and cost_element_id = 2
3644 and level_type = decode(i_level,1,1,level_type)
3645 and actual_cost > 0;
3646
3647 l_stmt_num := 15;
3648
3649 select organization_id, transfer_organization_id, primary_quantity
3650 into l_txn_org_id, l_txfr_org_id, l_txn_qty
3651 from mtl_material_transactions
3652 where transaction_id = i_txn_id;
3653
3654 -- Determine the from and to org for this transaction.
3655 if (i_txn_action_id = 21) then -- intransit shipment
3656 l_from_org := l_txn_org_id;
3657 l_to_org := l_txfr_org_id;
3658 elsif (i_txn_action_id = 12) then -- intransit receipt
3659 l_from_org := l_txfr_org_id;
3660 l_to_org := l_txn_org_id;
3661 elsif (i_txn_action_id =3 and l_txn_qty <0) then -- direct org transfer
3662 l_from_org := l_txn_org_id;
3663 l_to_org := l_txfr_org_id;
3664 else
3665 l_from_org := l_txfr_org_id;
3666 l_to_org := l_txn_org_id;
3667 end if;
3668
3669
3670 l_stmt_num := 20;
3671 -- do elemental visibility check for interorg transfer
3672 if (i_txn_action_id in (12,21,3)) then
3673 select NVL(elemental_visibility_enabled,'N')
3674 into l_elemental_visible
3675 from mtl_interorg_parameters
3676 where from_organization_id = l_from_org
3677 and to_organization_id = l_to_org;
3678 end if;
3679
3680 -- Until we can support landed cost, i.e. freight, duty, etc... for PO receipt,
3681 -- we can assume that there should be no actual cost in MCLACD overhead rows
3682 -- at this time, UNLESS it is an interorg transaction.
3683
3684 if not ((i_txn_action_id in (12,21,3)) and (l_elemental_visible = 'Y')) then
3685 if (l_mat_ovhds > 0) then
3686 raise overhead_error;
3687 end if;
3688 end if;
3689
3690 -- Since RTV or Assembly Return transactions can conceivably have multiple MCLACD
3691 -- movh rows, we should check if MACS rows have already been inserted for the
3692 -- transaction. If so, there is no need to insert MACS again,
3693 -- we just have to insert or update MCLACD later.
3694
3695 l_stmt_num := 25;
3696
3697 select count(*)
3698 into l_macs_ovhd
3699 from mtl_actual_cost_subelement
3700 where transaction_id = i_txn_id
3701 and organization_id = i_org_id
3702 and layer_id = i_layer_id
3703 and cost_element_id = 2
3704 and level_type = decode (i_level, 1,1,level_type);
3705
3706 if l_macs_ovhd <= 0 then /* inserting MACS */
3707 if (i_mat_ct_id <> i_cost_type) then -- this is the common scenario since the
3708 -- seeded cost type for FIFO/LIFO should
3709 -- not be the rate cost type
3710
3711 l_stmt_num := 30;
3712
3713 -- Compute item cost of layer. This will be used to calculate
3714 -- the material overhead of 'total value' basis (basis_type = 5)
3715
3716 select nvl(sum(actual_cost),0)
3717 into l_item_cost
3718 from mtl_cst_layer_act_cost_details
3719 where transaction_id = i_txn_id
3720 and organization_id = i_org_id
3721 and layer_id = i_layer_id
3722 and inv_layer_id = i_inv_layer_id;
3723
3724 -- Ensure that resource_id is NOT NULL in CICD
3725 l_stmt_num := 32;
3726 select count(*)
3727 into l_res_id
3728 from cst_item_cost_details cicd
3729 where inventory_item_id = i_item_id
3730 and organization_id = i_org_id
3731 and cost_type_id = i_mat_ct_Id
3732 and basis_type in (1,2,5,6)
3733 and cost_element_id = 2
3734 and resource_id IS NULL;
3735
3736 if (l_res_id > 0) then /*Changed this if block and inserted the update statement
3737 instead of raising the exception due to bugg 3959770*/
3738
3739 if (l_default_MOH_subelement is NOT NULL) then
3740 update CST_ITEM_COST_DETAILS
3741 set resource_id = l_default_MOH_subelement
3742 where inventory_item_id = i_item_id
3743 and organization_id = i_org_id
3744 and cost_type_id = i_mat_ct_Id
3745 and basis_type in (1,2,5,6)
3746 and cost_element_id = 2
3747 and resource_id IS NULL;
3748 else
3749 raise avg_rates_no_ovhd;
3750 end if;
3751 end if;
3752
3753
3754
3755 l_stmt_num := 35;
3756
3757 if(l_debug = 'Y') then
3758 fnd_file.put_line(fnd_file.log, 'Inserting into MACS');
3759 end if;
3760
3761 Insert into mtl_actual_cost_subelement(
3762 transaction_id,
3763 organization_id,
3764 layer_id,
3765 cost_element_id,
3766 level_type,
3767 resource_id,
3768 last_update_date,
3769 last_updated_by,
3770 creation_date,
3771 created_by,
3772 last_update_login,
3773 request_id,
3774 program_application_id,
3775 program_id,
3776 program_update_date,
3777 actual_cost,
3778 user_entered)
3779 select i_txn_id,
3780 i_org_id,
3781 i_layer_id,
3782 cicd.cost_element_id,
3783 cicd.level_type,
3784 cicd.resource_id,
3785 sysdate,
3786 i_user_id,
3787 sysdate,
3788 i_user_id,
3789 i_login_id,
3790 i_req_id,
3791 i_prg_appl_id,
3792 i_prg_id,
3793 sysdate,
3794 decode(cicd.basis_type, 1, cicd.usage_rate_or_amount,
3795 2, cicd.usage_rate_or_amount/abs(i_txn_qty),
3796 5, cicd.usage_rate_or_amount * l_item_cost,
3797 6, cicd.usage_rate_or_amount * cicd.basis_factor,0),
3798 'N'
3799 from cst_item_cost_details cicd
3800 where inventory_item_id = i_item_id
3801 and organization_id = i_org_id
3802 and cost_type_id = i_mat_ct_Id
3803 and basis_type in (1,2,5,6)
3804 and cost_element_id = 2
3805 and level_type = decode(i_level, 1,1,level_type);
3806
3807 else /* material overhead cost type is average cost type */
3808 -- In this case we will charge the material overhead in the average
3809 -- cost type using the first material overhead in the average rates
3810 -- cost type. This function will error out if material overhead
3811 -- exists in average cost type and none is defined in the average rates
3812 -- cost type.
3813
3814 l_stmt_num := 40;
3815
3816 select count(*)
3817 into l_mat_ovhds
3818 from cst_layer_cost_details
3819 where layer_id = i_layer_id
3820 and cost_element_id = 2
3821 and level_type = 1;
3822
3823 if (l_mat_ovhds >0 ) then /* material overhead exists in the seeded
3824 cost type */
3825 l_stmt_num := 45;
3826 select count(*)
3827 into l_res_id
3828 from cst_item_cost_details
3829 where cost_type_id = i_avg_rates_id
3830 and inventory_item_id = i_item_id
3831 and organization_id = i_org_id;
3832
3833 if (l_res_id > 0) then
3834 l_stmt_num := 50;
3835 select resource_id
3836 into l_res_id
3837 from cst_item_cost_details
3838 where cost_type_id = i_avg_rates_id
3839 and inventory_item_id = i_item_id
3840 and organization_id = i_org_id
3841 and cost_element_id = 2
3842 and rownum = 1;
3843 end if;
3844 /* Changed this check and included the elsif block which inserts the resource
3845 id instead of throwing the exception Bug 3959770*/
3846
3847 if (l_res_id = 0) then
3848 raise avg_rates_no_ovhd;
3849 elsif (l_res_id is NULL) then
3850 if (l_default_MOH_subelement IS NOT NULL) then
3851 l_res_id := l_default_MOH_subelement;
3852
3853 update cst_item_cost_details
3854 set resource_id = l_default_MOH_subelement
3855 where cost_type_id = i_avg_rates_id
3856 and inventory_item_id = i_item_id
3857 and organization_id = i_org_id
3858 and cost_element_id = 2
3859 and resource_id IS NULL
3860 and rownum =1;
3861 else
3862 raise avg_rates_no_ovhd;
3863 end if;
3864 end if;
3865
3866
3867 l_stmt_num := 55;
3868 if(l_debug = 'Y') then
3869 fnd_file.put_line(fnd_file.log, 'Inserting into MACS');
3870 end if;
3871
3872 Insert into mtl_actual_cost_subelement(
3873 transaction_id,
3874 organization_id,
3875 layer_id,
3876 cost_element_id,
3877 level_type,
3878 resource_id,
3879 last_update_date,
3880 last_updated_by,
3881 creation_date,
3882 created_by,
3883 last_update_login,
3884 request_id,
3885 program_application_id,
3886 program_id,
3887 program_update_date,
3888 actual_cost,
3889 user_entered)
3890 select i_txn_id,
3891 i_org_id,
3892 i_layer_id,
3893 clcd.cost_element_id,
3894 clcd.level_type,
3895 l_res_id,
3896 sysdate,
3897 i_user_id,
3898 sysdate,
3899 i_user_id,
3900 i_login_id,
3901 i_req_id,
3902 i_prg_appl_id,
3903 i_prg_id,
3904 sysdate,
3905 clcd.item_cost,
3906 'N'
3907 from cst_layer_cost_details clcd
3908 where layer_id = i_layer_id
3909 and cost_element_id = 2
3910 and level_type = 1;
3911 end if;
3912 end if;
3913 end if; /* end of inserting MACS */
3914
3915 -- check again for existence of MACS. This time load count into l_mat_ovhds.
3916
3917 l_stmt_num := 60;
3918
3919 select count(*)
3920 into l_mat_ovhds
3921 from mtl_actual_cost_subelement
3922 where transaction_id = i_txn_id
3923 and organization_id = i_org_id
3924 and layer_id = i_layer_id
3925 and cost_element_id = 2
3926 and level_type = decode(i_level, 1,1,level_type);
3927
3928 if l_debug = 'Y' then
3929 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.l_mat_ovhds = '
3930 || to_char(l_mat_ovhds)
3931 || ' , stmt '
3932 || to_char(l_stmt_num));
3933 end if;
3934
3935 l_stmt_num := 65;
3936
3937 -- check if there is data in MCLACD (material overhead) for this layer.
3938 select count(*)
3939 into l_mclacd_ovhd
3940 from mtl_cst_layer_act_cost_details mclacd
3941 where transaction_id = i_txn_id
3942 and organization_id = i_org_id
3943 and layer_id = i_layer_id
3944 and inv_layer_id = i_inv_layer_id
3945 and cost_element_id = 2
3946 and level_type = decode(i_level,1,1,level_type);
3947
3948 if l_debug = 'Y' then
3949 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.mclacd_ovhd = '
3950 || to_char(l_mclacd_ovhd)
3951 || ' , stmt '
3952 || to_char(l_stmt_num));
3953 end if;
3954
3955 -- MACS exists :add or modify MCLACD
3956 -- No data in MACS, then we do not need to do anything.
3957
3958 if (l_mat_ovhds > 0) then /* MACS exists */
3959 -- If there is data in MCLACD then do an update, adding the
3960 -- sum of MACS.actual cost to the existing cost in mclacd.
3961 -- Otherwise, insert a row in MCLACD.
3962
3963 l_stmt_num := 70;
3964 select sum(actual_cost)
3965 into l_ovhd_cost
3966 from mtl_actual_cost_subelement
3967 where transaction_id = i_txn_id
3968 and organization_id = i_org_id
3969 and layer_id = i_layer_id
3970 and cost_element_id = 2;
3971
3972 if l_debug = 'Y' then
3973 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.l_ovhd_cost = '
3974 || to_char(l_ovhd_cost)
3975 || ' ,stmt '
3976 || to_char(l_stmt_num));
3977 end if;
3978
3979
3980 if (l_mclacd_ovhd > 0) then /* mclacd exists */
3981 l_stmt_num := 72;
3982 select transaction_type_id
3983 into l_txn_type_id
3984 from mtl_material_transactions
3985 where transaction_id = i_txn_id;
3986
3987 /* Bug 2280515 :anjgupta
3988 The variance_amount is zero in the case of interorg receipt transactions.
3989 Updating in a seperate if-else loop to prevent use of decode statements.
3990 */
3991 if(i_interorg_rec = 1) then
3992
3993 l_stmt_num := 75;
3994
3995 update mtl_cst_layer_act_cost_details mclacd
3996 set mclacd.actual_cost = nvl(mclacd.actual_cost, 0) + l_ovhd_cost,
3997 mclacd.layer_cost = nvl(mclacd.layer_cost,0) + l_ovhd_cost,
3998 mclacd.variance_amount = 0,
3999 mclacd.payback_variance_amount = 0
4000 where mclacd.transaction_id = i_txn_id
4001 and mclacd.organization_id = i_org_id
4002 and mclacd.layer_id = i_layer_id
4003 and mclacd.inv_layer_id = i_inv_layer_id
4004 and mclacd.level_type = 1
4005 and mclacd.cost_element_id = 2;
4006
4007 else
4008
4009 l_stmt_num := 76;
4010 update mtl_cst_layer_act_cost_details mclacd
4011 set mclacd.actual_cost = nvl(mclacd.actual_cost, 0) + l_ovhd_cost,
4012 mclacd.variance_amount = decode(l_txn_type_id,68,0,
4013 (nvl(mclacd.actual_cost,0) + l_ovhd_cost
4014 - nvl(mclacd.layer_cost,0)) * layer_quantity ),
4015 mclacd.payback_variance_amount = decode(l_txn_type_id,68,
4016 ((nvl(mclacd.actual_cost,0) + l_ovhd_cost
4017 - nvl(mclacd.layer_cost,0)) * layer_quantity),0)
4018
4019 where mclacd.transaction_id = i_txn_id
4020 and mclacd.organization_id = i_org_id
4021 and mclacd.layer_id = i_layer_id
4022 and mclacd.inv_layer_id = i_inv_layer_id
4023 and mclacd.level_type = 1
4024 and mclacd.cost_element_id = 2;
4025 end if;
4026
4027 if l_debug = 'Y' then
4028 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.updating mclacd...l_ovhd_cost = '
4029 || to_char(l_ovhd_cost)
4030 || ' , stmt '
4031 || to_char(l_stmt_num));
4032 end if;
4033
4034
4035 else /* mclacd does not exist */
4036 l_stmt_num := 80;
4037 insert into mtl_cst_layer_act_cost_details(
4038 transaction_id,
4039 organization_id,
4040 inventory_item_id,
4041 cost_element_id,
4042 level_type,
4043 layer_id,
4044 inv_layer_id,
4045 layer_quantity,
4046 layer_cost,
4047 actual_cost,
4048 variance_amount,
4049 user_entered,
4050 payback_variance_amount,
4051 last_update_date,
4052 last_updated_by,
4053 creation_date,
4054 created_by,
4055 last_update_login,
4056 request_id,
4057 program_application_id,
4058 program_id,
4059 program_update_date)
4060 values (
4061 i_txn_id,
4062 i_org_id,
4063 i_item_id,
4064 2,
4065 1,
4066 i_layer_id,
4067 i_inv_layer_id,
4068 decode(sign(i_txn_qty),-1,-1*i_layer_qty,i_layer_qty),
4069 decode(sign(i_txn_qty),-1,0,l_ovhd_cost), /* layer_cost */
4070 l_ovhd_cost, /* actual_cost */
4071 decode(sign(i_txn_qty),-1,(-1*l_ovhd_cost*i_layer_qty),0), /* variance_amount */
4072 'N', /* user_entered */
4073 0, /* payback_variance_amount */
4074 sysdate,
4075 i_user_id,
4076 sysdate,
4077 i_user_id,
4078 i_login_id,
4079 i_req_id,
4080 i_prg_appl_id,
4081 i_prg_id,
4082 sysdate);
4083
4084 if l_debug = 'Y' then
4085 FND_FILE.PUT_LINE(FND_FILE.LOG, 'movh.insertign mclacd...l_ovhd_cost = '
4086 || to_char(l_ovhd_cost)
4087 || ',txn_lyr = '
4088 || to_char(i_layer_qty)
4089 || ' , stmt '
4090 || to_char(l_stmt_num));
4091 end if;
4092
4093 end if; /* mclacd does not exist */
4094 end if; /* macs exists */
4095 END IF;
4096
4097 EXCEPTION
4098 when avg_rates_no_ovhd then
4099 rollback;
4100 o_err_num := 9999;
4101 o_err_code := 'CST_NO_MAT_OVHDS';
4102 FND_MESSAGE.set_name('BOM', 'CST_NO_MAT_OVHDS');
4103 o_err_msg := FND_MESSAGE.Get;
4104 when overhead_error then
4105 rollback;
4106 o_err_num := 9999;
4107 o_err_code := 'CST_MAT_OVERHEAD';
4108 FND_MESSAGE.set_name('BOM', 'CST_MAT_OVERHEAD');
4109 o_err_msg := FND_MESSAGE.Get;
4110 when moh_rules_error then
4111 rollback;
4112 o_err_num := 9999;
4113 o_err_code := 'CST_RULES_ERROR';
4114 FND_MESSAGE.set_name('BOM', 'CST_RULES_ERROR');
4115 o_err_msg := FND_MESSAGE.Get;
4116 when others then
4117 rollback;
4118 o_err_num := SQLCODE;
4119 o_err_msg := 'CSTPLENG.apply_layer_material_ovhd (' || to_char(l_stmt_num) ||
4120 '): '
4121 || substr(SQLERRM, 1,200);
4122
4123 END apply_layer_material_ovhd;
4124
4125 /*********************************************************************************
4126 ** PROCEDURE **
4127 ** calc_layer_average_cost **
4128 ** **
4129 ** DESCRIPTION **
4130 ** It main function is to perform the following for the specified transaction: **
4131 ** . insert into MCACD with MCLACD's summarized costs **
4132 ** . update CLCD with CILCD's summarized costs **
4133 ** . update CQL's costs from CLCD **
4134 ** . update CICD's costs from CLCD **
4135 ** . update CIC's costs from CICD **
4136 ** This procedure assumes that all MCLACD rows have already been inserted by **
4137 ** calling program. **
4138 ** Set I_NO_UPDATE_MMT = 1 if the calling program does not want mmt to be **
4139 ** update; otherwise, set it to 0 **
4140 ** Set I_NO_UPDATE_QTY = 1 if clcd, cql, cic and cicd should not be updated; **
4141 ** otherwise, set it to 0 **
4142 ** **
4143 ** HISTORY **
4144 ** 4/24/00 Dieu-Thuong Le Creation **
4145 ** 9/05/00 Dieu-Thuong Le Fix bug 1393484: payback variance should **
4146 ** be stored in MCACD by qty unit because **
4147 ** the distribution proc. CSTPACDP.inventory_ **
4148 ** accounts will calc payback variance to be **
4149 ** posted (-1*i_pqty*l_payback_var) **
4150 *********************************************************************************/
4151
4152 procedure calc_layer_average_cost(
4153 I_ORG_ID IN NUMBER,
4154 I_TXN_ID IN NUMBER,
4155 I_LAYER_ID IN NUMBER,
4156 I_COST_TYPE IN NUMBER,
4157 I_ITEM_ID IN NUMBER,
4158 I_TXN_QTY IN NUMBER,
4159 I_TXN_ACTION_ID IN NUMBER,
4160 I_COST_HOOK IN NUMBER,
4161 I_NO_UPDATE_MMT IN NUMBER,
4162 I_NO_UPDATE_QTY IN NUMBER,
4163 I_USER_ID IN NUMBER,
4164 I_LOGIN_ID IN NUMBER,
4165 I_REQ_ID IN NUMBER,
4166 I_PRG_APPL_ID IN NUMBER,
4167 I_PRG_ID IN NUMBER,
4168 O_Err_Num OUT NOCOPY NUMBER,
4169 O_Err_Code OUT NOCOPY VARCHAR2,
4170 O_Err_Msg OUT NOCOPY VARCHAR2
4171 ) IS
4172 l_txfr_txn_id NUMBER;
4173 l_total_layer_qty NUMBER;
4174 l_level_type NUMBER;
4175 l_txn_type_id NUMBER;
4176 l_proj_enabled NUMBER;
4177 l_mandatory_update NUMBER;
4178 l_count NUMBER;
4179 l_err_num NUMBER;
4180 l_err_code VARCHAR2(240);
4181 l_err_msg VARCHAR2(240);
4182 l_stmt_num NUMBER;
4183 process_error EXCEPTION;
4184
4185 BEGIN
4186 -- initialize local variables
4187 l_err_num := 0;
4188 l_err_code := '';
4189 l_err_msg := '';
4190
4191 /********************************************************************
4192 ** For each cost element/level type, one row of MCACD is inserted, **
4193 ** aggregating inventory layer(s) cost. The actual cost populated **
4194 ** in MCACD is the weighted average cost of all inventory layers **
4195 ** associated to the transaction. The variance amount is the sum **
4196 ** of those layers' amounts. **
4197 ** **
4198 ** Note: Unlike the Average Costing processor which uses the **
4199 ** insertion flag to signal clcd insert, the layer cost processor **
4200 ** uses CILCD for CLCD insert and not MCACD. Therefore, insertion **
4201 ** flag will always be set to 'N'. **
4202 ** **
4203 ********************************************************************/
4204
4205 -- get transaction type. It will be needed to identify payback transaction
4206 -- and calculate payback variance.
4207
4208 l_stmt_num := 5;
4209 select transaction_type_id
4210 into l_txn_type_id
4211 from mtl_material_transactions
4212 where transaction_id = i_txn_id;
4213
4214 l_stmt_num := 6;
4215 select count(*)
4216 into l_count
4217 from mtl_cst_layer_act_cost_details
4218 where transaction_id = i_txn_id
4219 and organization_id = i_org_id;
4220
4221 if (l_count = 0) then
4222 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No records in MCLACD');
4223 end if;
4224
4225 /* Insert MCACD (by summing up MCLACD) only if it's not a scrap txn.
4226 Beware: there will be time where MCACD exists, such as when cost hook is used.
4227 In such case, update MCACD with variance amounts.
4228 */
4229
4230 if (i_txn_action_id <> 30) then
4231
4232 l_stmt_num := 10;
4233 update mtl_cst_actual_cost_details mcacd
4234 set (prior_cost,
4235 new_cost,
4236 variance_amount,
4237 payback_variance_amount,
4238 onhand_variance_amount) =
4239 (select
4240 0, -- prior cost
4241 NULL, -- new cost
4242 NVL(sum(mclacd.variance_amount),0),
4243 NVL(sum(mclacd.payback_variance_amount)/abs(i_txn_qty),0), -- bugfix 1393484
4244 NVL(sum(mclacd.onhand_variance_amount),0)
4245 from mtl_cst_layer_act_cost_details mclacd
4246 where mclacd.transaction_id = i_txn_id
4247 and mclacd.organization_id = i_org_id
4248 and mclacd.layer_id = i_layer_id
4249 and mclacd.cost_element_id = mcacd.cost_element_id
4250 and mclacd.level_type = mcacd.level_type
4251 group by mclacd.cost_element_id, mclacd.level_type)
4252 where mcacd.transaction_id = i_txn_id
4253 and mcacd.organization_id = i_org_id
4254 and mcacd.layer_id = i_layer_id
4255 and mcacd.transaction_action_id = i_txn_action_id;
4256
4257 l_stmt_num := 12;
4258 insert into mtl_cst_actual_cost_details (
4259 transaction_id,
4260 organization_id,
4261 layer_id,
4262 cost_element_id,
4263 level_type,
4264 transaction_action_id,
4265 last_update_date,
4266 last_updated_by,
4267 creation_date,
4268 created_by,
4269 last_update_login,
4270 request_id,
4271 program_application_id,
4272 program_id,
4273 program_update_date,
4274 inventory_item_id,
4275 actual_cost,
4276 prior_cost,
4277 new_cost,
4278 insertion_flag,
4279 variance_amount,
4280 user_entered,
4281 payback_variance_amount,
4282 onhand_variance_amount)
4283 select i_txn_id,
4284 i_org_id,
4285 i_layer_id,
4286 mclacd.cost_element_id,
4287 mclacd.level_type,
4288 i_txn_action_id,
4289 sysdate,
4290 i_user_id,
4291 sysdate,
4292 i_user_id,
4293 i_login_id,
4294 i_req_id,
4295 i_prg_appl_id,
4296 i_prg_id,
4297 sysdate,
4298 i_item_id,
4299 decode(
4300 i_txn_qty,
4301 0,
4302 NVL((sum(mclacd.actual_cost)),0), -- modified for bug#3835412 -- NVL(abs(sum(mclacd.actual_cost)),0),
4303 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)),
4304 0, -- prior cost
4305 NULL, -- new cost
4306 'N', -- insertion flag
4307 NVL(sum(mclacd.variance_amount),0),
4308 'N',
4309 NVL(sum(mclacd.payback_variance_amount)/abs(i_txn_qty),0), -- bugfix 1393484
4310 NVL(sum(mclacd.onhand_variance_amount),0)
4311 from mtl_cst_layer_act_cost_details mclacd
4312 where mclacd.transaction_id = i_txn_id
4313 and mclacd.organization_id = i_org_id
4314 and mclacd.layer_id = i_layer_id
4315 and not exists
4316 (select 'MCACD does not exist'
4317 from mtl_cst_actual_cost_details mcacd
4318 where mcacd.transaction_id = i_txn_id
4319 and mcacd.organization_id = i_org_id
4320 and mcacd.layer_id = i_layer_id
4321 and mcacd.cost_element_id = mclacd.cost_element_id
4322 and mcacd.level_type = mclacd.level_type)
4323 group by mclacd.cost_element_id, mclacd.level_type;
4324
4325 end if; -- end checking for scrap transaction
4326
4327 -- Update MCACD.prior_cost with the corresponding cost in CLCD before CLCD cost
4328 -- is updated.
4329
4330 l_stmt_num := 15;
4331
4332 update mtl_cst_actual_cost_details mcacd
4333 set prior_cost =
4334 (select clcd.item_cost
4335 from cst_layer_cost_details clcd
4336 where clcd.layer_id = i_layer_id
4337 and clcd.cost_element_id = mcacd.cost_element_id
4338 and clcd.level_type = mcacd.level_type)
4339 where mcacd.transaction_id = i_txn_id
4340 and mcacd.organization_id = i_org_id
4341 and mcacd.layer_id = i_layer_id
4342 and mcacd.transaction_action_id = i_txn_action_id
4343 and exists
4344 (select 'there is details in clcd'
4345 from cst_layer_cost_details clcd
4346 where clcd.layer_id = i_layer_id
4347 and clcd.cost_element_id = mcacd.cost_element_id
4348 and clcd.level_type = mcacd.level_type);
4349
4350 -- Insert missing cost elements into mcacd (bug 2987309)
4351 l_stmt_num := 17;
4352 INSERT
4353 INTO mtl_cst_actual_cost_details (
4354 transaction_id,
4355 organization_id,
4356 layer_id,
4357 cost_element_id,
4358 level_type,
4359 transaction_action_id,
4360 last_update_date,
4361 last_updated_by,
4362 creation_date,
4363 created_by,
4364 last_update_login,
4365 request_id,
4366 program_application_id,
4367 program_id,
4368 program_update_date,
4369 inventory_item_id,
4370 actual_cost,
4371 prior_cost,
4372 new_cost,
4373 insertion_flag,
4374 variance_amount,
4375 user_entered
4376 )
4377 SELECT i_txn_id,
4378 i_org_id,
4379 i_layer_id,
4380 CLCD.cost_element_id,
4381 CLCD.level_type,
4382 i_txn_action_id,
4383 sysdate,
4384 i_user_id,
4385 sysdate,
4386 i_user_id,
4387 i_login_id,
4388 i_req_id,
4389 i_prg_appl_id,
4390 i_prg_id,
4391 sysdate,
4392 i_item_id,
4393 0,
4394 CLCD.item_cost,
4395 NULL,
4396 'N',
4397 0,
4398 'N'
4399 FROM cst_layer_cost_details CLCD
4400 WHERE layer_id = i_layer_id
4401 AND NOT EXISTS(
4402 SELECT 'this detail is not in MCACD already'
4403 FROM mtl_cst_actual_cost_details MCACD
4404 WHERE MCACD.transaction_id = i_txn_id
4405 AND MCACD.organization_id = i_org_id
4406 AND MCACD.layer_id = i_layer_id
4407 AND MCACD.cost_element_id = CLCD.cost_element_id
4408 AND MCACD.level_type = CLCD.level_type);
4409
4410 /*******************************************************************
4411 ** Update cst_layer_cost_details if i_no_update_qty is not set. **
4412 ** Since CQL quantity before this transaction is still needed by **
4413 ** CSTPAVCP.update_mmt, CQL quantity and cost information will be **
4414 ** updated later. **
4415 ********************************************************************/
4416 -- get the total layer quantity from cil
4417 select sum(cil.layer_quantity)
4418 into l_total_layer_qty
4419 from cst_inv_layers cil
4420 where cil.layer_id = i_layer_id;
4421
4422 /* Update clcd only if i_no_update_qty flag is not set and the total layer quantity is not zero */
4423
4424 if (i_no_update_qty = 0) and
4425 (l_total_layer_qty <> 0) then
4426 l_stmt_num := 20;
4427 -- get the total layer quantity from cil
4428 select sum(cil.layer_quantity)
4429 into l_total_layer_qty
4430 from cst_inv_layers cil
4431 where cil.layer_id = i_layer_id;
4432
4433 l_stmt_num := 25;
4434
4435 delete from cst_layer_cost_details
4436 where layer_id = i_layer_id;
4437
4438 l_stmt_num := 30;
4439
4440 insert into cst_layer_cost_details(
4441 layer_id,
4442 cost_element_id,
4443 level_type,
4444 last_update_date,
4445 last_updated_by,
4446 creation_date,
4447 created_by,
4448 last_update_login,
4449 request_id,
4450 program_application_id,
4451 program_id,
4452 program_update_date,
4453 item_cost)
4454 select cilcd.layer_id,
4455 cilcd.cost_element_id,
4456 cilcd.level_type,
4457 sysdate,
4458 i_user_id,
4459 sysdate,
4460 i_user_id,
4461 i_login_id,
4462 i_req_id,
4463 i_prg_appl_id,
4464 i_prg_id,
4465 sysdate,
4466 (sum((cilcd.layer_cost*cil.layer_quantity)/l_total_layer_qty)) -- modified for bug#3835412
4467 from cst_inv_layer_cost_details cilcd,
4468 cst_inv_layers cil
4469 where cil.layer_id = i_layer_id
4470 and cil.organization_id = i_org_id
4471 and cil.inventory_item_id = i_item_id
4472 and cil.inv_layer_id = cilcd.inv_layer_id
4473 group by cilcd.layer_id,cost_element_id, level_type;
4474 end if; -- end updating cost info
4475
4476 /********************************************************************
4477 ** Update MCACD with new cost **
4478 ********************************************************************/
4479 l_stmt_num := 35;
4480
4481 update mtl_cst_actual_cost_details mcacd
4482 set new_cost =
4483 (select clcd.item_cost
4484 from cst_layer_cost_details clcd
4485 where clcd.layer_id = i_layer_id
4486 and clcd.cost_element_id = mcacd.cost_element_id
4487 and clcd.level_type = mcacd.level_type)
4488 where mcacd.organization_id = i_org_id
4489 and mcacd.transaction_id = i_txn_id
4490 and mcacd.layer_id = i_layer_id
4491 and mcacd.transaction_action_id = i_txn_action_id;
4492
4493 /********************************************************************
4494 ** Update Mtl_Material_Transactions **
4495 ** Need to update prior_costed_quantity now. **
4496 ********************************************************************/
4497 l_stmt_num := 40;
4498 if (i_no_update_mmt = 0) then
4499 -- subinventory transfer for receipt side, we need to pass
4500 -- txfr_txn_id to update proper transaction in MMT.
4501 if (i_txn_action_id = 2 and i_txn_qty > 0) then
4502 select transfer_transaction_id
4503 into l_txfr_txn_id
4504 from mtl_material_transactions
4505 where transaction_id = i_txn_id;
4506 else
4507 l_txfr_txn_id := -1;
4508 end if;
4509
4510 CSTPAVCP.update_mmt(
4511 i_org_id,
4512 i_txn_id,
4513 l_txfr_txn_id,
4514 i_layer_id,
4515 0,
4516 i_user_id,
4517 i_login_id,
4518 i_req_id,
4519 i_prg_appl_id,
4520 i_prg_id,
4521 l_err_num,
4522 l_err_code,
4523 l_err_msg);
4524 if (l_err_num <> 0) then
4525 raise process_error;
4526 end if;
4527 end if; -- end updating mmt
4528
4529 /********************************************************************
4530 ** Update layer quantity and layer costs information **
4531 ** (cst_quantity_layers) **
4532 ********************************************************************/
4533 if i_no_update_qty = 0 then
4534 l_stmt_num := 45;
4535 update cst_quantity_layers cql
4536 set (last_updated_by,
4537 last_update_date,
4538 last_update_login,
4539 request_id,
4540 program_application_id,
4541 program_id,
4542 program_update_date,
4543 layer_quantity,
4544 update_transaction_id,
4545 pl_material,
4546 pl_material_overhead,
4547 pl_resource,
4548 pl_outside_processing,
4549 pl_overhead,
4550 tl_material,
4551 tl_material_overhead,
4552 tl_resource,
4553 tl_outside_processing,
4554 tl_overhead,
4555 material_cost,
4556 material_overhead_cost,
4557 resource_cost,
4558 outside_processing_cost,
4559 overhead_cost,
4560 pl_item_cost,
4561 tl_item_cost,
4562 item_cost,
4563 unburdened_cost,
4564 burden_cost) =
4565 (select
4566 i_user_id,
4567 sysdate,
4568 i_login_id,
4569 i_req_id,
4570 i_prg_appl_id,
4571 i_prg_id,
4572 sysdate,
4573 l_total_layer_qty,
4574 i_txn_id,
4575 pl_material,
4576 pl_material_overhead,
4577 pl_resource,
4578 pl_outside_processing,
4579 pl_overhead,
4580 tl_material,
4581 tl_material_overhead,
4582 tl_resource,
4583 tl_outside_processing,
4584 tl_overhead,
4585 material_cost,
4586 material_overhead_cost,
4587 resource_cost,
4588 outside_processing_cost,
4589 overhead_cost,
4590 pl_item_cost,
4591 tl_item_cost,
4592 item_cost,
4593 unburdened_cost,
4594 burden_cost
4595 from cst_quantity_layers_v v
4596 where v.layer_id = i_layer_id)
4597 where cql.layer_id = i_layer_id
4598 and exists
4599 (select 'there is detail cost'
4600 from cst_layer_cost_details clcd
4601 where clcd.layer_id = i_layer_id);
4602
4603 /********************************************************************
4604 ** Update Item Cost and Item Cost Details **
4605 ********************************************************************/
4606
4607 -- Determine the value of mandatory_update_flag.
4608 -- If project is not enabled, set the l_mandatory_update flag.
4609 -- This flag is passed to update_item_cost() routine. In that
4610 -- routine, if this flag is set to 1, the item_cost will be
4611 -- copied from clcd to cicd evenif the quantity <= 0.
4612 -- Otherwise, it will return immediately if the quantity <= 0.
4613 -- For quantity > 0, this flag is ignored, and the weighted avg
4614 -- of cost in clcd (accross different cost group) will be put
4615 -- into cicd.
4616
4617 l_stmt_num := 50;
4618
4619 -- Bug 2401323 - propagation bug for bugfix 2306923
4620 -- Bug 2306923: l_mandatory_update should be zero even if project is not enabled
4621 -- This change was made to function calls (CSTPAVCP.update_item_cost) for
4622 -- average costing as part of bug 1756613.
4623 -- In 11i.2, the cost group model has been enhanced, so that
4624 -- multiple cost groups can exist in a non project manufacturing organization,
4625 -- depending on the set of accounts. Hence updating item cost and item cost details
4626 -- is made to behave exactly like an organization with project
4627 -- references enabled. The cost will be updated in CLCD, but not in CICD.
4628
4629 /* select nvl(project_reference_enabled,0)
4630 into l_proj_enabled
4631 from mtl_parameters
4632 where organization_id = i_org_id;
4633
4634 if (l_proj_enabled = 2) then
4635 l_mandatory_update := 1;
4636 else
4637 l_mandatory_update := 0;
4638 end if;
4639 */
4640
4641 l_mandatory_update := 0;
4642
4643 CSTPAVCP.update_item_cost(
4644 i_org_id,
4645 i_txn_id,
4646 i_layer_id,
4647 i_cost_type,
4648 i_item_id,
4649 l_mandatory_update,
4650 i_user_id,
4651 i_login_id,
4652 i_req_id,
4653 i_prg_appl_id,
4654 i_prg_id,
4655 l_err_num,
4656 l_err_code,
4657 l_err_msg);
4658 if (l_err_num <> 0) then
4659 raise process_error;
4660 end if;
4661 end if; -- end updating quantity and cost info
4662
4663 EXCEPTION
4664 when process_error then
4665 o_err_num := l_err_num;
4666 o_err_code := l_err_code;
4667 o_err_msg := l_err_msg;
4668 when others then
4669 rollback;
4670 o_err_num := SQLCODE;
4671 o_err_msg := 'CSTPLENG.calc_layer_average_cost (' || to_char(l_stmt_num) ||
4672 '): '
4673 || substr(SQLERRM, 1,200);
4674
4675 END calc_layer_average_cost;
4676
4677 /************************************************************************
4678 ** PROCEDURE **
4679 ** layer_cost_update **
4680 ** **
4681 ** DESCRIPTION **
4682 ** This function is called to update inventory layer cost. **
4683 ** It will determine the new elemental costs of the layer based **
4684 ** on user-enter values and compute the adjustment amounts to **
4685 ** inventory valuation. **
4686 ** MTL_CST_LAYER_ACT_COST_DETAILS will be populated and the other **
4687 ** cost tables (CILCD, CIL, CLCD, CQL, CICD, CIC) will be updated **
4688 ** accordingly with the new cost information. **
4689 ** This function is duplicated from CSTPAVCP.average_cost_update. **
4690 ** **
4691 ** HISTORY **
4692 ** 12-MAY-2000 Dieu-Thuong Le Creation **
4693 ** **
4694 ************************************************************************/
4695
4696 procedure layer_cost_update(
4697 I_ORG_ID IN NUMBER,
4698 I_TXN_ID IN NUMBER,
4699 I_LAYER_ID IN NUMBER,
4700 I_COST_TYPE IN NUMBER,
4701 I_ITEM_ID IN NUMBER,
4702 I_TXN_QTY IN NUMBER,
4703 I_TXN_ACT_ID IN NUMBER,
4704 I_USER_ID IN NUMBER,
4705 I_LOGIN_ID IN NUMBER,
4706 I_REQ_ID IN NUMBER,
4707 I_PRG_APPL_ID IN NUMBER,
4708 I_PRG_ID IN NUMBER,
4709 O_Err_Num OUT NOCOPY NUMBER,
4710 O_Err_Code OUT NOCOPY VARCHAR2,
4711 O_Err_Msg OUT NOCOPY VARCHAR2
4712 ) IS
4713
4714 l_neg_cost NUMBER;
4715 l_proj_enabled NUMBER;
4716 l_mandatory_update NUMBER;
4717 l_inv_layer_id NUMBER;
4718 l_layer_qty NUMBER;
4719 l_mctcd_exist NUMBER;
4720 l_stmt_num NUMBER;
4721 l_err_num NUMBER;
4722 l_err_code VARCHAR2(240);
4723 l_err_msg VARCHAR2(240);
4724 process_error EXCEPTION;
4725 neg_cost_error EXCEPTION;
4726 no_mctcd_error EXCEPTION;
4727
4728 BEGIN
4729 -- Initialize variables.
4730 l_neg_cost := 0;
4731 l_layer_qty := 0;
4732 l_mctcd_exist := 0;
4733 l_err_num := 0;
4734 l_err_code := '';
4735 l_err_msg := '';
4736 o_err_num := 0;
4737 o_err_code := '';
4738 o_err_msg := '';
4739
4740 l_stmt_num := 5;
4741
4742 -- Get the inv_layer_id whose cost is being changed
4743 select transaction_source_id
4744 into l_inv_layer_id
4745 from mtl_material_transactions
4746 where transaction_id = I_TXN_ID;
4747
4748 -- check for existence of mctcd
4749 l_stmt_num := 7;
4750 select count(*)
4751 into l_mctcd_exist
4752 from mtl_cst_txn_cost_details ctcd
4753 where ctcd.transaction_id = i_txn_id;
4754
4755 /* if l_mctcd_exist = 0 then
4756 raise no_mctcd_error;
4757 end if;
4758 */
4759 if l_mctcd_exist = 0 then
4760 FND_FILE.PUT_LINE(FND_FILE.LOG,'No mctcd rows');
4761 end if;
4762
4763 l_stmt_num := 10;
4764
4765 /*********************************************************
4766 ** Insert records into mtl_cst_layer_act_cost_details. **
4767 *********************************************************/
4768
4769 insert into mtl_cst_layer_act_cost_details (
4770 transaction_id,
4771 organization_id,
4772 layer_id,
4773 inv_layer_id,
4774 layer_quantity,
4775 cost_element_id,
4776 level_type,
4777 last_update_date,
4778 last_updated_by,
4779 creation_date,
4780 created_by,
4781 last_update_login,
4782 request_id,
4783 program_application_id,
4784 program_id,
4785 program_update_date,
4786 inventory_item_id,
4787 actual_cost,
4788 layer_cost,
4789 variance_amount,
4790 user_entered,
4791 onhand_variance_amount)
4792
4793 select
4794 i_txn_id,
4795 i_org_id,
4796 i_layer_id,
4797 l_inv_layer_id,
4798 cil.layer_quantity,
4799 ctcd.cost_element_id,
4800 ctcd.level_type,
4801 sysdate,
4802 i_user_id,
4803 sysdate,
4804 i_user_id,
4805 i_login_id,
4806 i_req_id,
4807 i_prg_appl_id,
4808 i_prg_id,
4809 sysdate,
4810 ctcd.inventory_item_id,
4811 decode(ctcd.new_average_cost,NULL, -- actual cost
4812 decode(ctcd.percentage_change,NULL,
4813 /* value change formula */
4814 decode(sign(cil.layer_quantity),1,
4815 decode(sign(i_txn_qty),1,
4816 decode( sign(cil.layer_quantity-i_txn_qty),-1,
4817 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + (ctcd.value_change/i_txn_qty*cil.layer_quantity)),-1,
4818 0,
4819 (nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
4820 (ctcd.value_change/i_txn_qty*cil.layer_quantity))/nvl(cil.layer_quantity,-1)),
4821 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
4822 0,
4823 (nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
4824 ctcd.value_change)/nvl(cil.layer_quantity,-1))
4825 ),
4826 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
4827 0,
4828 (nvl(cilcd.layer_cost,0)*nvl(cil.layer_quantity,0) +
4829 ctcd.value_change)/nvl(cil.layer_quantity,-1))),
4830 nvl(cilcd.layer_cost,0)),
4831 /* percentage change formula */
4832 nvl(cilcd.layer_cost,0)*(1+ctcd.percentage_change/100)),
4833 /* new average cost formula */
4834 ctcd.new_average_cost),
4835 nvl(cilcd.layer_cost,0), -- layer cost
4836 decode(ctcd.value_change,NULL,
4837 0,
4838 decode(sign(cil.layer_quantity),1,
4839 decode(sign(i_txn_qty),1,
4840 decode(sign(cil.layer_quantity-i_txn_qty),-1,
4841 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + (ctcd.value_change/i_txn_qty*cil.layer_quantity)),-1,
4842 (ctcd.value_change/i_txn_qty*cil.layer_quantity) + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
4843 0),
4844 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
4845 ctcd.value_change + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
4846 0)),
4847 decode(sign(nvl(cilcd.layer_cost,0) * cil.layer_quantity + ctcd.value_change),-1,
4848 ctcd.value_change + nvl(cilcd.layer_cost,0) * cil.layer_quantity,
4849 0)),
4850 ctcd.value_change)),
4851 'N', -- user entered
4852 /*LCM*/
4853 decode(ctcd.value_change,NULL,
4854 0,
4855 decode(sign(i_txn_qty),1,
4856 decode(sign(cil.layer_quantity),1,
4857 decode(sign(cil.layer_quantity-i_txn_qty),-1,
4858 ctcd.value_change*(1-cil.layer_quantity/i_txn_qty),
4859 0
4860 ),
4861 0
4862 ),
4863 0
4864 )
4865 )
4866 FROM mtl_cst_txn_cost_details ctcd,
4867 cst_inv_layers cil,
4868 cst_inv_layer_cost_details cilcd
4869 WHERE ctcd.transaction_id = i_txn_id
4870 AND ctcd.organization_id = i_org_id
4871 AND cil.layer_id = i_layer_id
4872 AND cil.inv_layer_id = l_inv_layer_id
4873 AND cil.inventory_item_id = ctcd.inventory_item_id
4874 AND cil.organization_id = ctcd.organization_id
4875 AND cilcd.inv_layer_id (+) = l_inv_layer_id
4876 AND cilcd.cost_element_id (+) = ctcd.cost_element_id
4877 AND cilcd.level_type (+) = ctcd.level_type;
4878
4879 -- Verify there are no negative costs!
4880 l_stmt_num := 20;
4881
4882 /* select count(*)
4883 into l_neg_cost
4884 from mtl_cst_layer_act_cost_details
4885 where transaction_id = i_txn_id
4886 and organization_id = i_org_id
4887 and layer_id = i_layer_id
4888 and inv_layer_id = l_inv_layer_id
4889 and actual_cost < 0;
4890
4891 if (l_neg_cost > 0) then
4892 raise neg_cost_error;
4893 end if; */ --removed for bug #4005770
4894
4895 /************************************************************************
4896 ** Delete from cst_inv_layer_cost_details and insert the new rows **
4897 ** from mtl_cst_actual_cost_details. **
4898 ***********************************************************************/
4899 l_stmt_num := 30;
4900
4901 Delete from cst_inv_layer_cost_details
4902 where layer_id = i_layer_id
4903 and inv_layer_id = l_inv_layer_id;
4904
4905 l_stmt_num := 40;
4906 Insert into cst_inv_layer_cost_details (
4907 layer_id,
4908 inv_layer_id,
4909 cost_element_id,
4910 level_type,
4911 layer_cost,
4912 last_update_date,
4913 last_updated_by,
4914 creation_date,
4915 created_by,
4916 last_update_login,
4917 request_id,
4918 program_application_id,
4919 program_id,
4920 program_update_date)
4921 select i_layer_id,
4922 l_inv_layer_id,
4923 mclacd.cost_element_id,
4924 mclacd.level_type,
4925 mclacd.actual_cost,
4926 sysdate,
4927 i_user_id,
4928 sysdate,
4929 i_user_id,
4930 i_login_id,
4931 i_req_id,
4932 i_prg_appl_id,
4933 i_prg_id,
4934 sysdate
4935 from mtl_cst_layer_act_cost_details mclacd
4936 where mclacd.transaction_id = i_txn_id
4937 and mclacd.organization_id = i_org_id
4938 and mclacd.layer_id = i_layer_id
4939 and mclacd.inv_layer_id = l_inv_layer_id;
4940
4941 /********************************************************************
4942 ** Update cst_inv_layers **
4943 ********************************************************************/
4944 l_stmt_num := 50;
4945
4946 update cst_inv_layers cil
4947 set (last_updated_by,
4948 last_update_date,
4949 last_update_login,
4950 request_id,
4951 program_application_id,
4952 program_id,
4953 program_update_date,
4954 layer_cost)=
4955 (select
4956 i_user_id,
4957 sysdate,
4958 i_login_id,
4959 i_req_id,
4960 i_prg_appl_id,
4961 i_prg_id,
4962 sysdate,
4963 nvl(sum(layer_cost),0)
4964 from cst_inv_layer_cost_details cilcd
4965 where cilcd.layer_id = i_layer_id
4966 and cilcd.inv_layer_id = l_inv_layer_id)
4967 where cil.layer_id = i_layer_id
4968 and cil.inv_layer_id = l_inv_layer_id
4969 and exists
4970 (select 'there is detail cost'
4971 from cst_inv_layer_cost_details cilcd
4972 where cilcd.layer_id = i_layer_id
4973 and cilcd.inv_layer_id = l_inv_layer_id);
4974
4975
4976 /*******************************************************
4977 ** Update mcacd, clcd, cql, cic, cicd and mmt **
4978 *******************************************************/
4979
4980 l_stmt_num := 60;
4981
4982 -- Get transaction quantity
4983 select cil.layer_quantity
4984 into l_layer_qty
4985 from cst_inv_layers cil
4986 where cil.layer_id = i_layer_id
4987 and cil.inv_layer_id = l_inv_layer_id;
4988
4989 FND_FILE.PUT_LINE(FND_FILE.LOG, 'layer qty = ' || to_char(l_layer_qty));
4990 l_stmt_num := 70;
4991
4992 CSTPLENG.calc_layer_average_cost(
4993 i_org_id,
4994 i_txn_id,
4995 i_layer_id,
4996 i_cost_type,
4997 i_item_id,
4998 l_layer_qty,
4999 i_txn_act_id,
5000 0, -- no cost hook
5001 0, -- i_no_update_mmt
5002 0, -- i_no_update_qty
5003 i_user_id,
5004 i_login_id,
5005 i_req_id,
5006 i_prg_appl_id,
5007 i_prg_id,
5008 l_err_num,
5009 l_err_code,
5010 l_err_msg);
5011
5012 if (l_err_num <> 0) then
5013 raise process_error;
5014 end if;
5015
5016 /* Update MMT.quantity_adjusted with update transaction quantity. */
5017
5018 update mtl_material_transactions mmt
5019 set last_update_date = sysdate,
5020 last_updated_by = i_user_id,
5021 last_update_login = i_login_id,
5022 program_application_id = i_prg_appl_id,
5023 program_id = i_prg_id,
5024 program_update_date = sysdate,
5025 quantity_adjusted = l_layer_qty
5026 where mmt.transaction_id = i_txn_id;
5027
5028 EXCEPTION
5029 when neg_cost_error then
5030 rollback;
5031 o_err_num := 9999;
5032 o_err_code := 'CST_NEG_ITEM_COST';
5033 FND_MESSAGE.set_name('BOM', 'CST_NEG_ITEM_COST');
5034 o_err_msg := FND_MESSAGE.Get;
5035
5036 /* when no_mctcd_error then
5037 rollback;
5038 o_err_num := 9999;
5039 o_err_code := 'CST_NO_MCTCD';
5040 FND_MESSAGE.set_name('BOM', 'CST_NO_MCTCD');
5041 o_err_msg := FND_MESSAGE.Get;
5042 */
5043 when process_error then
5044 o_err_num := l_err_num;
5045 o_err_code := l_err_code;
5046 o_err_msg := l_err_msg;
5047
5048 when others then
5049 rollback;
5050 o_err_num := SQLCODE;
5051 o_err_msg := 'CSTPLENG.layer_cost_update (' || to_char(l_stmt_num) ||
5052 '): '
5053 || substr(SQLERRM, 1,200);
5054 END layer_cost_update;
5055
5056 ----------------------------------------------------------------------------
5057 -- FUNCTION
5058 -- get_current_layer
5059 -- This function is called to return the inv layer id whose cost needs to be
5060 -- used if a issue is done. It is called from WIP to create layers when there
5061 -- are no layers at all in WIP. In that case WIP needs to know which layer cost-- has to be used.
5062 --
5063 -- INPUT PARAMETERS
5064 -- I_ORG_ID
5065 -- I_TXN_ID
5066 -- I_LAYER_ID
5067 -- I_ITEM_ID
5068 -- I_TXN_ACT_ID
5069 --
5070 -- RETURN VALUES
5071 -- integer 1 Successful
5072 -- 0 Error
5073 -----------------------------------------------------------------------------
5074 function get_current_layer(
5075 I_ORG_ID IN NUMBER,
5076 I_TXN_ID IN NUMBER,
5077 I_LAYER_ID IN NUMBER,
5078 I_ITEM_ID IN NUMBER,
5079 I_USER_ID IN NUMBER,
5080 I_LOGIN_ID IN NUMBER,
5081 I_REQ_ID IN NUMBER,
5082 I_PRG_APPL_ID IN NUMBER,
5083 I_PRG_ID IN NUMBER,
5084 I_TXN_SRC_TYPE_ID IN NUMBER,
5085 I_TXN_SRC_ID IN NUMBER,
5086 O_Err_Num OUT NOCOPY NUMBER,
5087 O_Err_Code OUT NOCOPY VARCHAR2,
5088 O_Err_Msg OUT NOCOPY VARCHAR2
5089 )
5090 return integer IS
5091 l_inv_layer_id NUMBER;
5092 l_cost_method NUMBER;
5093
5094 BEGIN
5095 o_err_num := 0;
5096 o_err_code := '';
5097 o_err_msg := '';
5098 l_inv_layer_id := 0;
5099
5100 /* Get the cost method for the org */
5101 select primary_cost_method
5102 into l_cost_method
5103 from mtl_parameters
5104 where organization_id = I_ORG_ID;
5105
5106 if (l_cost_method = 5) then
5107 /* Try to return the first positive layer */
5108 select nvl(min(inv_layer_id),0)
5109 into l_inv_layer_id
5110 from cst_inv_layers
5111 where layer_id = i_layer_id
5112 and layer_quantity > 0;
5113 /* If there is no positive layer, return the last layer */
5114 if l_inv_layer_id = 0 then
5115 select nvl(max(inv_layer_id),0)
5116 into l_inv_layer_id
5117 from cst_inv_layers
5118 where layer_id = i_layer_id;
5119 end if;
5120 elsif (l_cost_method = 6) then
5121 /* Try to return the last positive layer */
5122 select nvl(max(inv_layer_id), 0)
5123 into l_inv_layer_id
5124 from cst_inv_layers
5125 where layer_id = i_layer_id
5126 and layer_quantity > 0;
5127 /* If there is no positive layer, return the first layer */
5128 if l_inv_layer_id = 0 then
5129 select nvl(min(inv_layer_id),0)
5130 into l_inv_layer_id
5131 from cst_inv_layers
5132 where layer_id = i_layer_id;
5133 end if;
5134 end if;
5135
5136 if (l_inv_layer_id = 0) then
5137 /* No inv layers exist: Hence create one with 0 qty,cost */
5138
5139 select cst_inv_layers_s.nextval
5140 into l_inv_layer_id
5141 from dual;
5142
5143 insert into cst_inv_layers (
5144 create_transaction_id,
5145 layer_id,
5146 inv_layer_id,
5147 organization_id,
5148 inventory_item_id,
5149 creation_quantity,
5150 layer_quantity,
5151 layer_cost,
5152 transaction_source_type_id,
5153 transaction_source_id,
5154 last_update_date,
5155 last_updated_by,
5156 creation_date,
5157 created_by,
5158 last_update_login,
5159 request_id,
5160 program_application_id,
5161 program_id,
5162 program_update_date)
5163 select
5164 i_txn_id,
5165 i_layer_id,
5166 l_inv_layer_id,
5167 i_org_id,
5168 i_item_id,
5169 0,
5170 0,
5171 0,
5172 i_txn_src_type_id,
5173 i_txn_src_id,
5174 sysdate,
5175 i_user_id,
5176 sysdate,
5177 i_user_id,
5178 i_login_id,
5179 i_req_id,
5180 i_prg_appl_id,
5181 i_prg_id,
5182 sysdate
5183 from dual;
5184
5185 insert into cst_inv_layer_cost_details (
5186 layer_id,
5187 inv_layer_id,
5188 cost_element_id,
5189 level_type,
5190 layer_cost,
5191 last_update_date,
5192 last_updated_by,
5193 creation_date,
5194 created_by,
5195 last_update_login,
5196 request_id,
5197 program_application_id,
5198 program_id,
5199 program_update_date)
5200 select
5201 i_layer_id,
5202 l_inv_layer_id,
5203 1,
5204 1,
5205 0,
5206 sysdate,
5207 i_user_id,
5208 sysdate,
5209 i_user_id,
5210 i_login_id,
5211 i_req_id,
5212 i_prg_appl_id,
5213 i_prg_id,
5214 sysdate
5215 from dual;
5216
5217
5218 end if; /* if no layer exists */
5219
5220
5221
5222 return l_inv_layer_id;
5223
5224 EXCEPTION
5225
5226 when others then
5227 o_err_num := SQLCODE;
5228 o_err_msg := 'CSTPLENG.get_current_layer:' || substrb(SQLERRM,1,150);
5229 return 0;
5230
5231 END get_current_layer;
5232
5233 ----------------------------------------------------------------------------
5234 -- layer_cost_det_move
5235 -- This procedure inserts into MCTCD for Layer Cost Update through
5236 -- open interface
5237 ----------------------------------------------------------------------------
5238 procedure layer_cost_det_move (
5239 i_txn_id in number,
5240 i_txn_interface_id in number,
5241 i_txn_action_id in number,
5242 i_org_id in number,
5243 i_item_id in number,
5244 i_cost_group_id in number,
5245 i_inv_layer_id in number,
5246 i_txn_cost in number,
5247 i_new_avg_cost in number,
5248 i_per_change in number,
5249 i_val_change in number,
5250 i_mat_accnt in number,
5251 i_mat_ovhd_accnt in number,
5252 i_res_accnt in number,
5253 i_osp_accnt in number,
5254 i_ovhd_accnt in number,
5255 i_user_id in number,
5256 i_login_id in number,
5257 i_request_id in number,
5258 i_prog_appl_id in number,
5259 i_prog_id in number,
5260 o_err_num out NOCOPY number,
5261 o_err_code out NOCOPY varchar2,
5262 o_err_msg out NOCOPY varchar2
5263 )
5264 is
5265 l_err_num number;
5266 l_err_code varchar2(240);
5267 l_err_msg varchar2(240);
5268 l_num_detail number;
5269 l_layer_id number;
5270 cost_det_move_error EXCEPTION;
5271 cost_no_layer_error EXCEPTION;
5272 begin
5273 /*
5274 ** initialize local variables
5275 */
5276 l_err_num := 0;
5277 l_err_code := '';
5278 l_err_msg := '';
5279
5280 o_err_num := 0;
5281 o_err_code := '';
5282 o_err_msg := '';
5283
5284 SELECT count(*)
5285 INTO l_num_detail
5286 FROM MTL_TXN_COST_DET_INTERFACE
5287 WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
5288
5289 /* l_num_detail = 0 : No corresponding rows in MTL_TXN_COST_DET_INTERFACE
5290 * OR i_txn_interface_id is null.
5291 * In this case, call cstpacit.cost_det_new_insert.
5292 */
5293
5294 if (l_num_detail = 0) then
5295 cstpleng.layer_cost_det_new_insert(i_txn_id, i_txn_action_id, i_org_id,
5296 i_item_id, i_cost_group_id, i_inv_layer_id, i_txn_cost,
5297 i_new_avg_cost, i_per_change, i_val_change,
5298 i_mat_accnt, i_mat_ovhd_accnt, i_res_accnt,
5299 i_osp_accnt, i_ovhd_accnt,
5300 i_user_id, i_login_id, i_request_id,
5301 i_prog_appl_id, i_prog_id,
5302 l_err_num, l_err_code, l_err_msg);
5303 if (l_err_num <> 0) then
5304 raise cost_det_move_error;
5305 end if;
5306
5307 else
5308
5309 l_layer_id := cstpaclm.layer_det_exist(i_org_id, i_item_id, i_cost_group_id,
5310 l_err_num, l_err_code, l_err_msg);
5311
5312 if (l_err_num <> 0) then
5313 raise cost_no_layer_error;
5314 end if;
5315
5316 if (l_layer_id <> 0) then
5317
5318 INSERT INTO MTL_CST_TXN_COST_DETAILS (
5319 TRANSACTION_ID,
5320 ORGANIZATION_ID,
5321 INVENTORY_ITEM_ID,
5322 COST_ELEMENT_ID,
5323 LEVEL_TYPE,
5324 TRANSACTION_COST,
5325 NEW_AVERAGE_COST,
5326 PERCENTAGE_CHANGE,
5327 VALUE_CHANGE,
5328 LAST_UPDATE_DATE,
5329 LAST_UPDATED_BY,
5330 CREATION_DATE,
5331 CREATED_BY,
5332 LAST_UPDATE_LOGIN,
5333 REQUEST_ID,
5334 PROGRAM_APPLICATION_ID,
5335 PROGRAM_ID,
5336 PROGRAM_UPDATE_DATE
5337 )
5338 SELECT
5339 i_txn_id,
5340 i_org_id,
5341 i_item_id,
5342 COST_ELEMENT_ID,
5343 LEVEL_TYPE,
5344 LAYER_COST,
5345 LAYER_COST,
5346 NULL,
5347 NULL,
5348 sysdate,
5349 i_user_id,
5350 sysdate,
5351 i_user_id,
5352 i_login_id,
5353 i_request_id,
5354 i_prog_appl_id,
5355 i_prog_id,
5356 sysdate
5357 FROM CST_INV_LAYER_COST_DETAILS CILCD
5358 WHERE CILCD.LAYER_ID = l_layer_id
5359 AND CILCD.INV_LAYER_ID = i_inv_layer_id;
5360
5361 UPDATE MTL_CST_TXN_COST_DETAILS mctcd
5362 set (VALUE_CHANGE,
5363 PERCENTAGE_CHANGE,
5364 NEW_AVERAGE_COST)
5365 =
5366 (select
5367 mtcdi.VALUE_CHANGE,
5368 mtcdi.PERCENTAGE_CHANGE,
5369 mtcdi.NEW_AVERAGE_COST
5370 from MTL_TXN_COST_DET_INTERFACE mtcdi
5371 where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
5372 and mctcd.transaction_id = i_txn_id
5373 and mtcdi.level_type = mctcd.level_type
5374 and mtcdi.cost_element_id = mctcd.cost_element_id
5375 )
5376 where
5377 mctcd.transaction_id = i_txn_id
5378 and exists (select 1
5379 from MTL_TXN_COST_DET_INTERFACE mtcdi
5380 where mtcdi.TRANSACTION_INTERFACE_ID = i_txn_interface_id
5381 and mtcdi.level_type = mctcd.level_type
5382 and mtcdi.cost_element_id = mctcd.cost_element_id);
5383
5384 else
5385
5386 /* No layer exists , hence use THIS level MATERIAL row */
5387
5388 INSERT INTO MTL_CST_TXN_COST_DETAILS (
5389 TRANSACTION_ID,
5390 ORGANIZATION_ID,
5391 INVENTORY_ITEM_ID,
5392 COST_ELEMENT_ID,
5393 LEVEL_TYPE,
5394 TRANSACTION_COST,
5395 NEW_AVERAGE_COST,
5396 PERCENTAGE_CHANGE,
5397 VALUE_CHANGE,
5398 LAST_UPDATE_DATE,
5399 LAST_UPDATED_BY,
5400 CREATION_DATE,
5401 CREATED_BY,
5402 LAST_UPDATE_LOGIN,
5403 REQUEST_ID,
5404 PROGRAM_APPLICATION_ID,
5405 PROGRAM_ID,
5406 PROGRAM_UPDATE_DATE
5407 )
5408 values (
5409 i_txn_id,
5410 i_org_id,
5411 i_item_id,
5412 1, /* Hard coded to This level Material */
5413 1,
5414 i_txn_cost,
5415 i_new_avg_cost,
5416 i_per_change,
5417 i_val_change,
5418 sysdate,
5419 i_user_id,
5420 sysdate,
5421 i_user_id,
5422 i_login_id,
5423 i_request_id,
5424 i_prog_appl_id,
5425 i_prog_id,
5426 sysdate);
5427
5428
5429 end if; /* if layer exists */
5430
5431 end if; /* if l_num_detail = 0 */
5432
5433 EXCEPTION
5434 when cost_det_move_error then
5435 o_err_num := l_err_num;
5436 o_err_code := l_err_code;
5437 o_err_msg := 'CSTPLENG.LAYER_COST_DET_MOVE:' || l_err_msg;
5438 when cost_no_layer_error then
5439 o_err_num := l_err_num;
5440 o_err_code := l_err_code;
5441 o_err_msg := 'CSTPLENG.LAYER_COST_DET_MOVE: No layer exists' || l_err_msg;
5442 when OTHERS then
5443 o_err_num := SQLCODE;
5444 o_err_msg := 'CSTPLENG.LAYER_COST_DET_MOVE:' || substr(SQLERRM,1,150);
5445
5446 end layer_cost_det_move;
5447
5448 --------------------------------------------------------------------------
5449 -- PROCEDURE layer_cost_det_new_insert
5450 -- procedure used by layer cost update through open interface
5451 --------------------------------------------------------------------------
5452 procedure layer_cost_det_new_insert (
5453 i_txn_id in number,
5454 i_txn_action_id in number,
5455 i_org_id in number,
5456 i_item_id in number,
5457 i_cost_group_id in number,
5458 i_inv_layer_id in number,
5459 i_txn_cost in number,
5460 i_new_avg_cost in number,
5461 i_per_change in number,
5462 i_val_change in number,
5463 i_mat_accnt in number,
5464 i_mat_ovhd_accnt in number,
5465 i_res_accnt in number,
5466 i_osp_accnt in number,
5467 i_ovhd_accnt in number,
5468 i_user_id in number,
5469 i_login_id in number,
5470 i_request_id in number,
5471 i_prog_appl_id in number,
5472 i_prog_id in number,
5473 o_err_num out NOCOPY number,
5474 o_err_code out NOCOPY varchar2,
5475 o_err_msg out NOCOPY varchar2
5476 )
5477 is
5478 l_err_num number;
5479 l_err_code varchar2(240);
5480 l_err_msg varchar2(240);
5481
5482 cl_item_cost number;
5483 cost_element_count number;
5484
5485 l_cost_elmt_id number;
5486 l_layer_id number;
5487 cil_layer_cost number;
5488 cost_det_new_insert_error EXCEPTION;
5489
5490
5491 cursor cost_elmt_ids is
5492 SELECT CILCD.COST_ELEMENT_ID
5493 FROM CST_INV_LAYERS CIL,
5494 CST_INV_LAYER_COST_DETAILS CILCD
5495 WHERE CIL.LAYER_ID = l_layer_id
5496 AND CIL.INV_LAYER_ID = i_inv_layer_id
5497 AND CILCD.LAYER_ID = l_layer_id
5498 AND CILCD.INV_LAYER_ID = i_inv_layer_id;
5499
5500
5501 begin
5502 /*
5503 ** initialize local variables
5504 */
5505 l_err_num := 0;
5506 l_err_code := '';
5507 l_err_msg := '';
5508
5509 l_layer_id := cstpaclm.layer_det_exist(i_org_id, i_item_id, i_cost_group_id,
5510 l_err_num, l_err_code, l_err_msg);
5511
5512 if (l_err_num <> 0) then
5513 raise cost_det_new_insert_error;
5514 end if;
5515
5516 /* If layer detail exist, then calculate proportional costs and
5517 * insert each elements into MTL_CST_TXN_COST_DETAILS.
5518 */
5519
5520 if (l_layer_id <> 0) then
5521
5522 if (i_txn_action_id = 24) then
5523 -- checking the existence of accounts for layer cost update case
5524 open cost_elmt_ids;
5525
5526 loop
5527 fetch cost_elmt_ids into l_cost_elmt_id;
5528 exit when cost_elmt_ids%NOTFOUND;
5529
5530 if ((l_cost_elmt_id = 1 and i_mat_accnt is null) or
5531 (l_cost_elmt_id = 2 and i_mat_ovhd_accnt is null) or
5532 (l_cost_elmt_id = 3 and i_res_accnt is null) or
5533 (l_cost_elmt_id = 4 and i_osp_accnt is null) or
5534 (l_cost_elmt_id = 5 and i_ovhd_accnt is null)) then
5535 -- Error occured
5536
5537 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
5538 l_err_code := 'Invalid accounts.';
5539 l_err_msg := FND_MESSAGE.Get;
5540 l_err_num := 999;
5541
5542 raise cost_det_new_insert_error;
5543 end if;
5544
5545
5546 end loop;
5547 end if;
5548
5549 SELECT LAYER_COST
5550 INTO cil_layer_cost
5551 FROM CST_INV_LAYERS
5552 WHERE LAYER_ID = l_layer_id
5553 AND INV_LAYER_ID = i_inv_layer_id;
5554
5555 /* for the case of layer cost equal zero */
5556 /* split cost evenly among cost elements */
5557
5558 if (cl_item_cost = 0) then
5559 SELECT count(COST_ELEMENT_ID)
5560 INTO cost_element_count
5561 FROM CST_INV_LAYER_COST_DETAILS
5562 WHERE LAYER_ID = l_layer_id
5563 AND INV_LAYER_ID = i_inv_layer_id;
5564 end if;
5565
5566 INSERT INTO MTL_CST_TXN_COST_DETAILS (
5567 TRANSACTION_ID,
5568 ORGANIZATION_ID,
5569 INVENTORY_ITEM_ID,
5570 COST_ELEMENT_ID,
5571 LEVEL_TYPE,
5572 TRANSACTION_COST,
5573 NEW_AVERAGE_COST,
5574 PERCENTAGE_CHANGE,
5575 VALUE_CHANGE,
5576 LAST_UPDATE_DATE,
5577 LAST_UPDATED_BY,
5578 CREATION_DATE,
5579 CREATED_BY,
5580 LAST_UPDATE_LOGIN,
5581 REQUEST_ID,
5582 PROGRAM_APPLICATION_ID,
5583 PROGRAM_ID,
5584 PROGRAM_UPDATE_DATE
5585 )
5586 SELECT
5587 i_txn_id,
5588 i_org_id,
5589 i_item_id,
5590 CILCD.COST_ELEMENT_ID,
5591 CILCD.LEVEL_TYPE,
5592 DECODE(CIL.LAYER_COST, 0, i_txn_cost / cost_element_count,
5593 i_txn_cost * CILCD.LAYER_COST / CIL.LAYER_COST),
5594 DECODE(CIL.LAYER_COST, 0, i_new_avg_cost / cost_element_count,
5595 i_new_avg_cost * CILCD.LAYER_COST / CIL.LAYER_COST),
5596 i_per_change,
5597 DECODE(CIL.LAYER_COST, 0, i_val_change / cost_element_count,
5598 i_val_change * CILCD.LAYER_COST / CIL.LAYER_COST),
5599 sysdate,
5600 i_user_id,
5601 sysdate,
5602 i_user_id,
5603 i_login_id,
5604 i_request_id,
5605 i_prog_appl_id,
5606 i_prog_id,
5607 sysdate
5608 FROM CST_INV_LAYERS CIL, CST_INV_LAYER_COST_DETAILS CILCD
5609 WHERE CIL.LAYER_ID = l_layer_id
5610 AND CIL.INV_LAYER_ID = i_inv_layer_id
5611 AND CILCD.LAYER_ID = l_layer_id
5612 AND CILCD.INV_LAYER_ID = i_inv_layer_id;
5613
5614 /* If layer detail does not exist, then insert a new row
5615 * as a this level material.
5616 */
5617 else
5618
5619 if (i_txn_action_id = 24 and i_mat_accnt is null) then
5620 -- Error occured only for layer cost update
5621
5622 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
5623 l_err_code := 'Invalid accounts.';
5624 l_err_msg := FND_MESSAGE.Get;
5625 l_err_num := 999;
5626
5627 raise cost_det_new_insert_error;
5628 end if;
5629
5630
5631 INSERT INTO MTL_CST_TXN_COST_DETAILS (
5632 TRANSACTION_ID,
5633 ORGANIZATION_ID,
5634 INVENTORY_ITEM_ID,
5635 COST_ELEMENT_ID,
5636 LEVEL_TYPE,
5637 TRANSACTION_COST,
5638 NEW_AVERAGE_COST,
5639 PERCENTAGE_CHANGE,
5640 VALUE_CHANGE,
5641 LAST_UPDATE_DATE,
5642 LAST_UPDATED_BY,
5643 CREATION_DATE,
5644 CREATED_BY,
5645 LAST_UPDATE_LOGIN,
5646 REQUEST_ID,
5647 PROGRAM_APPLICATION_ID,
5648 PROGRAM_ID,
5649 PROGRAM_UPDATE_DATE
5650 )
5651 values (
5652 i_txn_id,
5653 i_org_id,
5654 i_item_id,
5655 1, /* Hard coded to This level Material */
5656 1,
5657 i_txn_cost,
5658 i_new_avg_cost,
5659 i_per_change,
5660 i_val_change,
5661 sysdate,
5662 i_user_id,
5663 sysdate,
5664 i_user_id,
5665 i_login_id,
5666 i_request_id,
5667 i_prog_appl_id,
5668 i_prog_id,
5669 sysdate);
5670
5671 end if;
5672
5673 EXCEPTION
5674 when cost_det_new_insert_error then
5675 o_err_num := l_err_num;
5676 o_err_code := l_err_code;
5677 o_err_msg := 'CSTPLENG.LAYER_COST_DET_NEW_INSERT:' || l_err_msg;
5678 when OTHERS then
5679 o_err_num := SQLCODE;
5680 o_err_msg := 'CSTPLENG.LAYER_COST_DET_NEW_INSERT:' || substr(SQLERRM,1,150);
5681
5682 end layer_cost_det_new_insert;
5683
5684 ----------------------------------------------------------------------------
5685 -- PROCEDURE --
5686 -- layer_cost_update_dist --
5687 -- --
5688 -- DESCRIPTION --
5689 -- This procedure is called by CSTPLCIN package for layer cost update --
5690 -- transaction, since distributions need o be done using layer cost --
5691 -- from MCLACD rather than MCLACD --
5692 -- --
5693 -- PURPOSE: --
5694 -- Post distributions into MTA for layer cost update transactions --
5695 -- --
5696 -- PARAMETERS: --
5697 -- all transaction related details --
5698 ----------------------------------------------------------------------------
5699 procedure layer_cost_update_dist(
5700 I_ORG_ID IN NUMBER,
5701 I_TXN_ID IN NUMBER,
5702 I_LAYER_ID IN NUMBER,
5703 I_EXP_ITEM IN NUMBER,
5704 I_USER_ID IN NUMBER,
5705 I_LOGIN_ID IN NUMBER,
5706 I_REQ_ID IN NUMBER,
5707 I_PRG_APPL_ID IN NUMBER,
5708 I_PRG_ID IN NUMBER,
5709 O_Error_Num OUT NOCOPY NUMBER,
5710 O_Error_Code OUT NOCOPY VARCHAR2,
5711 O_Error_Message OUT NOCOPY VARCHAR2
5712 )IS
5713 l_hook NUMBER;
5714 l_item_id NUMBER;
5715 l_cost_grp_id NUMBER;
5716 l_txn_org_id NUMBER;
5717 l_txn_src_id NUMBER;
5718 l_txn_date DATE;
5719 l_p_qty NUMBER;
5720 l_subinv VARCHAR2(10);
5721 l_qty_adj NUMBER;
5722 l_dist_acct NUMBER;
5723 l_mat_acct NUMBER;
5724 l_mat_ovhd_acct NUMBER;
5725 l_res_acct NUMBER;
5726 l_osp_acct NUMBER;
5727 l_ovhd_acct NUMBER;
5728 l_pri_curr VARCHAR2(15);
5729 l_alt_curr VARCHAR2(10);
5730 l_conv_date DATE;
5731 l_conv_rate NUMBER;
5732 l_conv_type VARCHAR2(30);
5733 l_sob_id NUMBER;
5734 l_enc_rev NUMBER;
5735 l_enc_amount NUMBER;
5736 l_enc_acct NUMBER;
5737 l_acct_exist NUMBER;
5738 l_acct NUMBER;
5739 l_cost NUMBER;
5740 l_var NUMBER;
5741 l_ele_exist NUMBER;
5742 l_inv_mat_acct NUMBER;
5743 l_inv_mat_ovhd_acct NUMBER;
5744 l_inv_res_acct NUMBER;
5745 l_inv_osp_acct NUMBER;
5746 l_inv_ovhd_acct NUMBER;
5747 l_avg_cost_var_acct NUMBER;
5748 /*LCM*/
5749 l_onhand_var NUMBER;
5750 l_onhand_var_acct NUMBER;
5751
5752 l_err_num NUMBER;
5753 l_err_code VARCHAR2(240);
5754 l_err_msg VARCHAR2(240);
5755 l_stmt_num NUMBER;
5756 process_error EXCEPTION;
5757 no_acct_error EXCEPTION;
5758 no_txn_det_error EXCEPTION;
5759
5760 BEGIN
5761 -- initialize local variables
5762 l_ele_exist := 0;
5763 l_err_num := 0;
5764 l_err_code := '';
5765 l_err_msg := '';
5766
5767 l_stmt_num := 1;
5768 l_hook := CSTPACHK.cost_dist_hook(i_org_id,
5769 i_txn_id,
5770 i_user_id,
5771 i_login_id,
5772 i_req_id,
5773 i_prg_appl_id,
5774 i_prg_id,
5775 l_err_num,
5776 l_err_code,
5777 l_err_msg);
5778
5779 -- If the user choose to do distribution then we are done!
5780 if (l_hook = 1) then
5781 return;
5782 end if;
5783
5784 -- Populate local variables
5785
5786 l_stmt_num := 2;
5787 select
5788 inventory_item_id, organization_id,
5789 nvl(cost_group_id,1),
5790 transaction_date,
5791 primary_quantity, subinventory_code,
5792 quantity_adjusted,
5793 nvl(transaction_source_id,-1),
5794 nvl(distribution_account_id,-1),
5795 nvl(material_account, -1), nvl(material_overhead_account, -1),
5796 nvl(resource_account, -1), nvl(outside_processing_account, -1),
5797 nvl(overhead_account, -1),
5798 nvl(encumbrance_account, -1), nvl(encumbrance_amount, 0),
5799 currency_code,
5800 nvl(currency_conversion_date,transaction_date),
5801 nvl(currency_conversion_rate,-1) , currency_conversion_type,
5802 nvl(expense_account_id,-1)
5803 into
5804 l_item_id,
5805 l_txn_org_id,
5806 l_cost_grp_id,
5807 l_txn_date,
5808 l_p_qty,
5809 l_subinv,
5810 l_qty_adj,
5811 l_txn_src_id,
5812 l_dist_acct,
5813 l_mat_acct,
5814 l_mat_ovhd_acct,
5815 l_res_acct,
5816 l_osp_acct,
5817 l_ovhd_acct,
5818 l_enc_acct,
5819 l_enc_amount,
5820 l_alt_curr,
5821 l_conv_date,
5822 l_conv_rate,
5823 l_conv_type,
5824 l_onhand_var_acct
5825 from mtl_material_transactions
5826 where transaction_id = i_txn_id;
5827
5828 l_stmt_num := 3;
5829 select decode(encumbrance_reversal_flag,1,1,2,0,0)
5830 into l_enc_rev
5831 from mtl_parameters
5832 where organization_id = i_org_id;
5833
5834 l_stmt_num := 4;
5835
5836 select ledger_id
5837 into l_sob_id
5838 from cst_acct_info_v
5839 where organization_id = i_org_id;
5840
5841 l_stmt_num := 5;
5842 select currency_code
5843 into l_pri_curr
5844 from gl_sets_of_books
5845 where set_of_books_id = l_sob_id;
5846
5847 l_stmt_num := 6;
5848 if (l_alt_curr is not NULL and l_conv_rate = -1) then
5849 if (l_alt_curr <> l_pri_curr) then
5850
5851 if (l_conv_type is NULL) then
5852 FND_PROFILE.get('CURRENCY_CONVERSION_TYPE', l_conv_type);
5853 end if;
5854
5855 l_stmt_num := 7;
5856
5857 l_conv_rate := gl_currency_api.get_rate(l_sob_id,l_alt_curr,l_txn_date,
5858 l_conv_type);
5859 end if;
5860 end if;
5861
5862 l_stmt_num := 8;
5863
5864 BEGIN
5865 IF l_cost_grp_id <> 1 THEN
5866 SELECT
5867 nvl(material_account,-1),
5868 nvl(material_overhead_account,-1),
5869 nvl(resource_account,-1),
5870 nvl(outside_processing_account,-1),
5871 nvl(overhead_account,-1),
5872 nvl(average_cost_var_account,-1)
5873 INTO
5874 l_inv_mat_acct,
5875 l_inv_mat_ovhd_acct,
5876 l_inv_res_acct,
5877 l_inv_osp_acct,
5878 l_inv_ovhd_acct,
5879 l_avg_cost_var_acct
5880 FROM
5881 CST_COST_GROUP_ACCOUNTS
5882 WHERE
5883 ORGANIZATION_ID = i_org_id
5884 AND COST_GROUP_ID = l_cost_grp_id;
5885
5886 ELSE
5887 SELECT
5888 nvl(MATERIAL_ACCOUNT, -1),
5889 nvl(MATERIAL_OVERHEAD_ACCOUNT, -1),
5890 nvl(RESOURCE_ACCOUNT, -1),
5891 nvl(OVERHEAD_ACCOUNT, -1),
5892 nvl(OUTSIDE_PROCESSING_ACCOUNT, -1),
5893 nvl(AVERAGE_COST_VAR_ACCOUNT, -1)
5894 INTO
5895 l_inv_mat_acct,
5896 l_inv_mat_ovhd_acct,
5897 l_inv_res_acct,
5898 l_inv_ovhd_acct,
5899 l_inv_osp_acct,
5900 l_avg_cost_var_acct
5901 FROM
5902 MTL_PARAMETERS
5903 WHERE
5904 ORGANIZATION_ID = i_org_id;
5905 END IF;
5906 EXCEPTION
5907 WHEN NO_DATA_FOUND THEN
5908 raise no_acct_error;
5909 END;
5910
5911 l_stmt_num := 20;
5912
5913 select count(*)
5914 into l_ele_exist
5915 from mtl_cst_actual_cost_details
5916 where transaction_id = i_txn_id
5917 and organization_id = i_org_id;
5918
5919 if (l_ele_exist = 0) then
5920 raise no_txn_det_error;
5921 end if;
5922
5923 -- Layer cost update has been designed along the same lines as average
5924 -- cost update. However, since prior_cost column in MCACD is populated as
5925 -- the current average cost of the item across all inventory layers, the
5926 -- layer cost update distribution should be based on MCLACD rather than MCACD
5927 -- Based on this the accounting rules are :
5928 --
5929 -- Adjustment acct (layer_cost - actual_cost) * Qty - Variance
5930 -- Inventory (actual_cost - layer_cost) * Qty
5931 -- Variance Acct Variance
5932 -- All these value are based on MCLACD.
5933
5934
5935 FOR cost_element IN 1..5 loop
5936 l_cost := NULL;
5937 -- The difference between new cost and prior cost is the impact to
5938 -- inventory. If new cost is higher then it's a debit to inventory
5939 -- else it is a credit to inventory.
5940
5941 l_stmt_num := 30;
5942
5943 select (sum(actual_cost) - sum(layer_cost)),sum(variance_amount),
5944 sum(onhand_variance_amount)
5945 into l_cost,l_var,l_onhand_var
5946 from mtl_cst_layer_act_cost_details
5947 where transaction_id = i_txn_id
5948 and organization_id = i_org_id
5949 and cost_element_id = cost_element;
5950
5951
5952 if (l_cost is not NULL ) then
5953 -- First post to inventory.
5954 IF (l_cost <> 0) THEN
5955 l_acct := CSTPACHK.get_account_id(i_org_id, i_txn_id,
5956 sign(l_cost), 1,
5957 cost_element, NULL, NULL,
5958 0, NULL, l_err_num, l_err_code,
5959 l_err_msg);
5960
5961 -- check error
5962 if (l_err_num<>0) then
5963 raise process_error;
5964 end if;
5965
5966 if (l_acct = -1) then
5967
5968 l_stmt_num := 40;
5969
5970 select decode(cost_element, 1, l_inv_mat_acct,
5971 2, l_inv_mat_ovhd_acct,
5972 3, l_inv_res_acct,
5973 4, l_inv_osp_acct,
5974 5, l_inv_ovhd_acct)
5975 into l_acct
5976 from dual;
5977 end if;
5978
5979 CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_qty_adj * l_cost,
5980 sign(l_qty_adj * l_cost) * abs(l_qty_adj)/*modified for bug #4005770*/ /*l_qty_adj*/, l_acct, l_sob_id, 1,
5981 cost_element, NULL,
5982 l_txn_date, l_txn_src_id, 15,
5983 l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
5984 1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
5985 l_err_num, l_err_code, l_err_msg);
5986
5987 -- check error
5988 if (l_err_num<>0) then
5989 raise process_error;
5990 end if;
5991 END IF;
5992 -- Second post to adjustment.
5993 if (l_cost <> 0 OR l_var <> 0 OR l_onhand_var <> 0) then
5994 l_cost := -1 * l_cost;
5995 l_acct := CSTPACHK.get_account_id(i_org_id, i_txn_id,
5996 sign((l_qty_adj * l_cost) - l_var - l_onhand_var), 2,
5997 cost_element, NULL, NULL,
5998 0, NULL, l_err_num, l_err_code,
5999 l_err_msg);
6000 -- check error
6001 if (l_err_num<>0) then
6002 raise process_error;
6003 end if;
6004
6005 l_stmt_num := 50;
6006
6007 /* Added the decode for BUG: 1107767. Avg cost update through the interface needs all the accounts
6008 in MMT to be specified, even if only the material cost element is getting affected */
6009
6010 if (l_acct = -1) then
6011 select decode(cost_element, 1, l_mat_acct,
6012 2, decode(l_mat_ovhd_acct,-1, l_mat_acct, l_mat_ovhd_acct),
6013 3, decode(l_res_acct,-1, l_mat_acct, l_res_acct),
6014 4, decode(l_osp_acct,-1, l_mat_acct, l_osp_acct),
6015 5, decode(l_ovhd_acct,-1, l_mat_acct, l_ovhd_acct))
6016 into l_acct
6017 from dual;
6018 end if;
6019
6020 CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, (l_qty_adj * l_cost) - l_var - l_onhand_var,
6021 l_qty_adj, l_acct, l_sob_id, 2,
6022 cost_element, NULL,
6023 l_txn_date, l_txn_src_id, 15,
6024 l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
6025 1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
6026 l_err_num, l_err_code, l_err_msg);
6027
6028 -- check error
6029 if (l_err_num<>0) then
6030 raise process_error;
6031 end if;
6032 end if;
6033 if (l_onhand_var <> 0) then
6034
6035 l_acct := CSTPACHK.get_account_id(i_org_id, i_txn_id,
6036 sign(l_onhand_var), 20,
6037 cost_element, NULL, NULL,
6038 0, NULL, l_err_num, l_err_code,
6039 l_err_msg);
6040
6041 -- check error
6042 if(l_err_num<>0) then
6043 raise process_error;
6044 end if;
6045
6046 if (l_acct = -1) then
6047 l_acct := l_onhand_var_acct;
6048 end if;
6049
6050 CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_onhand_var,
6051 l_qty_adj, l_acct, l_sob_id, 20,
6052 cost_element, NULL,
6053 l_txn_date, l_txn_src_id, 15,
6054 l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
6055 1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
6056 l_err_num, l_err_code, l_err_msg);
6057
6058 -- check error
6059 if(l_err_num<>0) then
6060 raise process_error;
6061 end if;
6062
6063 end if;
6064
6065 end if;
6066 end loop;
6067
6068
6069 -- Now Post one consolidated variance entry
6070
6071 l_stmt_num := 60;
6072
6073 select nvl(sum(variance_amount),0)
6074 into l_var
6075 from mtl_cst_actual_cost_details cacd
6076 where transaction_id = i_txn_id
6077 and organization_id = i_org_id;
6078
6079 if (l_var <> 0) then
6080
6081 l_acct := CSTPACHK.get_account_id(i_org_id, i_txn_id,
6082 sign(l_var), 13,
6083 NULL, NULL, NULL,
6084 0, NULL, l_err_num, l_err_code,
6085 l_err_msg);
6086
6087 -- check error
6088 if(l_err_num<>0) then
6089 raise process_error;
6090 end if;
6091
6092 if (l_acct = -1) then
6093 l_acct := l_avg_cost_var_acct;
6094 end if;
6095
6096 CSTPACDP.insert_account(i_org_id, i_txn_id, l_item_id, l_var,
6097 l_qty_adj, l_acct, l_sob_id, 13,
6098 NULL, NULL,
6099 l_txn_date, l_txn_src_id, 15,
6100 l_pri_curr, l_alt_curr, l_conv_date, l_conv_rate, l_conv_type,
6101 1,i_user_id, i_login_id, i_req_id, i_prg_appl_id,i_prg_id,
6102 l_err_num, l_err_code, l_err_msg);
6103
6104 -- check error
6105 if(l_err_num<>0) then
6106 raise process_error;
6107 end if;
6108
6109 end if;
6110
6111 UPDATE mtl_cst_actual_cost_details
6112 SET transaction_costed_date = sysdate
6113 WHERE transaction_id = i_txn_id
6114 AND transaction_costed_date IS NULL;
6115
6116 EXCEPTION
6117
6118 when process_error then
6119 rollback;
6120 O_error_num := l_err_num;
6121 O_error_code := l_err_code;
6122 O_error_message := l_err_msg;
6123
6124 when no_acct_error then
6125 rollback;
6126 O_error_num := 9999;
6127 O_error_code := 'CST_NO_CG_ACCTS';
6128 FND_MESSAGE.set_name('BOM', 'CST_NO_CG_ACCTS');
6129 O_error_message := FND_MESSAGE.Get;
6130
6131 when no_txn_det_error then
6132 rollback;
6133 O_error_num := 9999;
6134 O_error_code := 'CST_NO_TXN_DET';
6135 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_DET');
6136 O_error_message := FND_MESSAGE.Get;
6137
6138 when others then
6139 rollback;
6140 O_error_num := SQLCODE;
6141 O_error_message := 'CSTPLENG.layer_cost_update_dist' || to_char(l_stmt_num) ||
6142 substr(SQLERRM,1,180);
6143
6144 END layer_cost_update_dist;
6145
6146 ----------------------------------------------------------------------------
6147 -- PROCEDURE --
6148 -- update_inv_layer_cost --
6149 -- --
6150 -- DESCRIPTION --
6151 -- This procedure is calld by the Define Items form (INVIDITM), to --
6152 -- set costs as zero, when an item is changed from asset to expense --
6153 -- --
6154 -- PURPOSE: --
6155 -- FIFO/LIFO layer cost processing for Oracle Applications Rel 11i.2 --
6156 -- --
6157 -- PARAMETERS: --
6158 -- i_org_id : organization ID --
6159 -- i_item_id : inventory_item_id for the item whose --
6160 -- expense flag is changed --
6161 -- --
6162 -- HISTORY: --
6163 -- 04/20/00 Anitha B Created --
6164 ----------------------------------------------------------------------------
6165
6166 PROCEDURE update_inv_layer_cost (i_org_id IN NUMBER,
6167 i_item_id IN NUMBER,
6168 i_userid IN NUMBER,
6169 i_login_id IN NUMBER)
6170 IS
6171
6172 Begin
6173 update cst_inv_layers
6174 set last_updated_by = i_userid,
6175 last_update_date = sysdate,
6176 last_update_login = i_login_id,
6177 layer_cost = 0,
6178 burden_cost = 0,
6179 unburdened_cost = 0
6180 where organization_id = i_org_id
6181 and inventory_item_id = i_item_id;
6182
6183 delete from cst_inv_layer_cost_details
6184 where inv_layer_id IN (select inv_layer_id
6185 from cst_inv_layers
6186 where organization_id = i_org_id
6187 and inventory_item_id = i_item_id);
6188 EXCEPTION
6189 when NO_DATA_FOUND then null;
6190 End update_inv_layer_cost;
6191
6192
6193 END CSTPLENG;