[Home] [Help]
PACKAGE BODY: APPS.CSTPLVCP
Source
1 PACKAGE BODY CSTPLVCP AS
2 /* $Header: CSTLVCPB.pls 120.15 2007/11/30 21:37:41 ipineda ship $ */
3
4 -- PROCEDURE
5 -- cost_processor Costs inventory transactions for FIFO/LIFO
6 --
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSTPLVCP';
9 G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('MRP_DEBUG'),'N');
10
11 l_pd_txfr_ind NUMBER := 0; -- OPM INVCONV sschinch
12
13 procedure cost_processor(
14 I_ORG_ID IN NUMBER,
15 I_TXN_ID IN NUMBER,
16 I_LAYER_ID IN NUMBER,
17 I_COST_TYPE IN NUMBER,
18 I_COST_METHOD IN NUMBER,
19 I_MAT_CT_ID IN NUMBER,
20 I_AVG_RATES_ID IN NUMBER,
21 I_ITEM_ID IN NUMBER,
22 I_TXN_QTY IN NUMBER,
23 I_TXN_ACTION_ID IN NUMBER,
24 I_TXN_SRC_TYPE IN NUMBER,
25 I_TXN_ORG_ID IN NUMBER,
26 I_TXFR_ORG_ID IN NUMBER,
27 I_COST_GRP_ID IN NUMBER,
28 I_TXFR_COST_GRP IN NUMBER,
29 I_TXFR_LAYER_ID IN NUMBER,
30 I_FOB_POINT IN NUMBER,
31 I_EXP_ITEM IN NUMBER,
32 I_EXP_FLAG IN NUMBER,
33 I_CITW_FLAG IN NUMBER,
34 I_FLOW_SCHEDULE IN NUMBER,
35 I_USER_ID IN NUMBER,
36 I_LOGIN_ID IN NUMBER,
37 I_REQ_ID IN NUMBER,
38 I_PRG_APPL_ID IN NUMBER,
39 I_PRG_ID IN NUMBER,
40 I_TPRICE_OPTION IN NUMBER,
41 I_TXF_PRICE IN NUMBER,
42 O_Err_Num OUT NOCOPY NUMBER,
43 O_Err_Code OUT NOCOPY VARCHAR2,
44 O_Err_Msg OUT NOCOPY VARCHAR2
45 ) IS
46 l_txn_qty NUMBER;
47 l_cost_hook NUMBER;
48 l_new_cost NUMBER;
49 l_exp_flag NUMBER;
50 l_interorg_rec NUMBER;
51 l_no_update_mmt NUMBER;
52 l_layer_chg NUMBER;
53 l_txn_action_id NUMBER;
54 l_layer_id NUMBER;
55 l_org_id NUMBER;
56 l_err_num NUMBER;
57 l_err_code VARCHAR2(240);
58 l_err_msg VARCHAR2(240);
59 process_error EXCEPTION;
60 l_stmt_num NUMBER;
61 l_so_line_id NUMBER;
62
63 /* Borrow Payback */
64 l_txn_type_id NUMBER;
65
66 -- Added for bug 3679625
67 l_to_std_exp NUMBER;
68 l_std_org NUMBER;
69 l_to_method NUMBER;
70
71 -- l_pd_txfr_ind NUMBER := 0; -- OPM INVCONV sschinch
72
73
74 BEGIN
75 -- initialize local variables
76 l_err_num := 0;
77 l_txn_qty := i_txn_qty; /* mmt.primary quantity */
78 l_org_id := i_org_id;
79 l_no_update_mmt := 0;
80 l_interorg_rec := 0;
81 l_exp_flag := i_exp_flag; /* Expense item or expense subinventory*/
82 l_cost_hook := 0;
83 l_new_cost := 0;
84 l_txn_action_id := 0;
85
86 -- Added for bug 3679625
87 l_to_std_exp := 0;
88
89 -- The l_exp_flag determines if this is an expense item or the transaction
90 -- involves an expense subinventory.
91
92 /* OPM INVCONV sschinch Check if this transaction is a process discrete transfer */
93 SELECT MOD(SUM(DECODE(mp.process_enabled_flag, 'Y', 1, 2)), 2)
94 INTO l_pd_txfr_ind
95 FROM mtl_parameters mp, mtl_material_transactions mmt
96 WHERE mmt.transaction_id = i_txn_id
97 AND (mmt.organization_id = mp.organization_id
98 OR mmt.transfer_organization_id = mp.organization_id);
99
100 if ((l_pd_txfr_ind = 1) AND (i_txn_action_id in (3, 15, 12, 21, 22))) then
101
102 --
103 -- OPM INVCONV umoogala/sschinch Process-Discrete Transfers Enh.:
104 -- Processing for
105 -- 1. Logical Intransit Receipt
106 -- This is a new transaction type introduced for p-d xfers enh. only.
107 -- 2. Physical Intransit Receipt and
108 -- 3. Direct Xfer receipt.
109 --
110 CSTPLVCP.cost_logical_itr_receipt(
111 i_org_id,
112 i_txn_id,
113 i_cost_method,
114 i_layer_id,
115 i_cost_type,
116 i_item_id,
117 i_txn_action_id,
118 i_txn_src_type,
119 i_txn_org_id,
120 i_txfr_org_id,
121 i_cost_grp_id,
122 i_txfr_cost_grp,
123 i_fob_point,
124 i_mat_ct_id,
125 i_avg_rates_id,
126 i_user_id,
127 i_login_id,
128 i_req_id,
129 i_prg_appl_id,
130 i_prg_id,
131 i_tprice_option,
132 i_txf_price,
133 l_txn_qty,
134 l_interorg_rec,
135 l_no_update_mmt,
136 l_exp_flag,
137 l_err_num,
138 l_err_code,
139 l_err_msg);
140
141 IF g_debug = 'Y' THEN
142 FND_FILE.PUT_LINE(FND_FILE.LOG,'cost_logical_itr_receipt(): interorg_rec: ' || to_char(l_interorg_rec));
143 END IF;
144
145 if (l_err_num <> 0) then
146 raise process_error;
147 end if;
148
149 /* If this is an interorg transfer transaction, call interorg procedure
150 to figure out transfer cost and transaction cost.
151 INTERORG TRANSFER TXN
152 */
153
154 elsif (i_txn_action_id in (3,12,21)) then
155 /* Called for all orgs. If sending org, then populate MCTCD
156 for receiving org populate MCACD for std org, if applicable,
157 else no processing */
158
159 l_stmt_num := 10;
160
161 CSTPLVCP.interorg ( i_org_id,
162 i_txn_id,
163 i_cost_method,
164 i_layer_id,
165 i_cost_type,
166 i_item_id,
167 i_txn_action_id,
168 i_txn_src_type,
169 i_txn_org_id,
170 i_txfr_org_id,
171 i_cost_grp_id,
172 i_txfr_cost_grp,
173 i_fob_point,
174 i_mat_ct_id,
175 i_avg_rates_id,
176 i_user_id,
177 i_login_id,
178 i_req_id,
179 i_prg_appl_id,
180 i_prg_id,
181 i_tprice_option,
182 i_txf_price,
183 l_txn_qty,
184 l_interorg_rec,
185 l_no_update_mmt,
186 l_exp_flag,
187 l_err_num,
188 l_err_code,
189 l_err_msg);
190 IF g_debug = 'Y' THEN
191 FND_FILE.PUT_LINE(FND_FILE.LOG,'interorg(): interorg_rec: ' || to_char(l_interorg_rec));
192 END IF;
193 if (l_err_num <> 0) then
194 raise process_error;
195 end if;
196 /* Some transactions do not need to be cost processed and only need cost
197 distribution!
198 1) The intransit shipment from standard to average with fob receipt
199 2) The intransit receipt to standard from average with fob shipment
200 3) The direct interorg shipment from standard to average/FIFO/LIFO
201 4) The direct interorg receipt from average/FIFO/LIFO to standard. */
202
203 l_stmt_num := 20;
204
205 if ((i_txn_action_id = 21 and i_fob_point = 2 and i_txfr_org_id = i_org_id)
206 OR
207 (i_txn_action_id = 12 and i_fob_point = 1 and i_txfr_org_id = i_org_id)
208 OR
209 (i_txn_action_id = 3 and i_txfr_org_id = i_org_id and i_txn_qty < 0)
210 OR
211 (i_txn_action_id = 3 and i_txfr_org_id = i_org_id and i_txn_qty > 0)) then
212 return;
213 end if;
214 /* END INTERORG TRANSFER TXN */
215
216 elsif (i_citw_flag = 1) then
217 /* Common Issue to WIP is processed separately. There is no cost hook
218 available for this transaction. Check for layer hook at the time of consume_layers()
219
220 Treat it as a subinventory transfer */
221 l_txn_action_id := 2;
222
223 /* Call WIP processor for the component issue */
224 l_stmt_num := 30;
225
226 CSTPLVCP.common_issue_to_wip(
227 i_org_id,
228 i_txn_id,
229 i_layer_id,
230 i_cost_type,
231 i_item_id,
232 l_txn_qty,
233 i_txn_action_id,
234 i_txn_src_type,
235 l_new_cost,
236 0,
237 i_txfr_layer_id,
238 i_cost_method,
239 i_avg_rates_id,
240 i_mat_ct_id,
241 i_cost_grp_id,
242 i_txfr_cost_grp,
243 l_exp_flag,
244 i_exp_item,
245 i_citw_flag,
246 i_flow_schedule,
247 i_user_id,
248 i_login_id,
249 i_req_id,
250 i_prg_appl_id,
251 i_prg_id,
252 l_err_num,
253 l_err_code,
254 l_err_msg);
255 if (l_err_num <> 0) then
256 raise process_error;
257 end if;
258
259 return;
260
261 elsif (i_txn_action_id = 24) then /*Removed condition i_txn_src_type = 15 for Bug 6030287*/
262 /* Layer Cost Update is processed separately. There is no hook
263 available for this transaction. In contrast with average cost
264 update this function inserts distributions into MTA, and not
265 through the distribution processor
266 */
267
268 l_stmt_num := 40;
269
270 CSTPLENG.layer_cost_update(
271 i_org_id,
272 i_txn_id,
273 i_layer_id,
274 i_cost_type,
275 i_item_id,
276 i_txn_qty,
277 i_txn_action_id,
278 i_user_id,
279 i_login_id,
280 i_req_id,
281 i_prg_appl_id,
282 i_prg_id,
283 l_err_num,
284 l_err_code,
285 l_err_msg);
286 if (l_err_num <> 0) then
287 raise process_error;
288 end if;
289
290 /* no more processing */
291 return;
292
293 elsif (i_exp_item = 0) then
294 /* Call the Actual Cost Hook for asset items */
295
296 l_stmt_num := 50;
297
298 l_cost_hook := CSTPACHK.actual_cost_hook ( i_org_id,
299 i_txn_id,
300 i_layer_id,
301 i_cost_type,
302 i_cost_method,
303 i_user_id,
304 i_login_id,
305 i_req_id,
306 i_prg_appl_id,
307 i_prg_id,
308 l_err_num,
309 l_err_code,
310 l_err_msg);
311 if (l_err_num <> 0) then
312 raise process_error;
313 end if;
314 end if;
315
316 if (l_cost_hook = 0) then
317 /* BORROW PAYBACK */
318 /* If hook is not used and it is a payback transaction,
319 we need to populate MCTCD with the borrowed cost. */
320
321 /* Changes for VMI. Adding planning transfer transaction */
322
323 if i_txn_action_id IN (2,5,28,55) then
324 l_stmt_num := 60;
325
326 select transaction_type_id
327 into l_txn_type_id
328 from mtl_material_transactions
329 where transaction_id = i_txn_id;
330
331 if (l_txn_type_id = 68) and (i_layer_id <> i_txfr_layer_id) then
332 /* if payback txn and txn involved different projects
333 then populate MCTCD with the borrowed cost */
334
335 l_stmt_num := 70;
336
337 CSTPLVCP.borrow_cost( i_org_id,
338 i_txn_id,
339 i_user_id,
340 i_login_id,
341 i_req_id,
342 i_prg_appl_id,
343 i_prg_id,
344 i_item_id,
345 0, -- hook is not used
346 i_txfr_layer_id,
347 l_err_num,
348 l_err_code,
349 l_err_msg);
350 if (l_err_num <> 0) then
351 raise process_error;
352 end if;
353
354 end if; -- l_txn_type_id = 68, if it is payback transaction
355 end if; -- i_txn_action_id IN (2,28,55), if it is sub/staging transfer
356
357 /* when we process transfer org's txn(i.e. intransit txfr),
358 we need to use txfr_layer_id instead. */
359 if (i_org_id <> i_txn_org_id) then
360 l_layer_id := i_txfr_layer_id;
361 else
362 l_layer_id := i_layer_id;
363 end if;
364 end if;
365
366 /*
367 The following section will populate MCTCD for RMA Receipts. If the RMA Receipt
368 references a Sales Order, the average cost of the Sales Order Issues will be used.
369 If the RMA Receipt does not reference a Sales Order and there exist positive
370 layers, the cost of the earliest positive layer will be used in FIFO. If no
371 positive layer exists or if the cost method is LIFO, MCTCD will not be populated
372 and the RMA Receipt will be processed at the latest layer cost (e.g. as a cost
373 derived transaction)
374 */
375
376 -- Check if the transaction is an RMA receipt
377 IF i_txn_action_id = 27 AND i_txn_src_type = 12 THEN
378 -- Check if Sales Order is referenced
379 l_stmt_num := 72;
380 SELECT MIN(OOLA.reference_line_id)
381 INTO l_so_line_id
382 FROM mtl_material_transactions MMT,
383 oe_order_lines_all OOLA
384 WHERE MMT.transaction_id = i_txn_id
385 AND OOLA.line_id = MMT.trx_source_line_id;
386
387 IF l_so_line_id IS NOT NULL THEN
388 -- A Sales Order is referenced, use the average cost of the Sales Order Issues
389 l_stmt_num := 74;
390 INSERT
391 INTO mtl_cst_txn_cost_details (
392 transaction_id,
393 organization_id,
394 inventory_item_id,
395 cost_element_id,
396 level_type,
397 transaction_cost,
398 last_update_date,
399 last_updated_by,
400 creation_date,
401 created_by,
402 last_update_login,
403 request_id,
404 program_application_id,
405 program_id,
406 program_update_date
407 )
408 SELECT i_txn_id,
409 i_org_id,
410 i_item_id,
411 MCACD.cost_element_id,
412 MCACD.level_type,
413 SUM(MMT.primary_quantity*MCACD.actual_cost)/SUM(MMT.primary_quantity),
414 SYSDATE,
415 i_user_id,
416 SYSDATE,
417 i_user_id,
418 i_login_id,
419 i_req_id,
420 i_prg_appl_id,
421 i_prg_id,
422 SYSDATE
423 FROM oe_order_lines_all OOLA, /*BUG 5768680 Changes introduced to improve performance*/
424 oe_order_headers_all OOHA, /* of the layer cost worker*/
425 mtl_sales_orders MSO,
426 mtl_material_transactions MMT,
427 mtl_cst_actual_cost_details MCACD
428 WHERE OOLA.line_id = l_so_line_id
429 AND OOHA.header_id = OOLA.header_id
430 AND MSO.segment1 = TO_CHAR(OOHA.order_number) -- extraneous MSOs are possible
431 AND MMT.transaction_source_id = MSO.sales_order_id
432 AND MMT.trx_source_line_id = l_so_line_id -- filter MMTs corresponding to extraneous MSOs
433 AND MMT.transaction_action_id IN (1,7)
434 AND MMT.transaction_source_type_id = 2
435 AND MMT.organization_id = i_org_id
436 AND MMT.inventory_item_id = i_item_id
437 AND MCACD.transaction_id = MMT.transaction_id
438 GROUP
439 BY MCACD.cost_element_id,
440 MCACD.level_type;
441 ELSIF i_cost_method = 5 THEN
442 -- No Sales Order is referenced and the cost method is FIFO, use the cost of
443 -- earliest positive layer if one exists
444 l_stmt_num := 76;
445 INSERT
446 INTO mtl_cst_txn_cost_details (
447 transaction_id,
448 organization_id,
449 inventory_item_id,
450 cost_element_id,
451 level_type,
452 transaction_cost,
453 last_update_date,
454 last_updated_by,
455 creation_date,
456 created_by,
457 last_update_login,
458 request_id,
459 program_application_id,
460 program_id,
461 program_update_date
462 )
463 SELECT i_txn_id,
464 i_org_id,
465 i_item_id,
466 CILCD.cost_element_id,
467 CILCD.level_type,
468 CILCD.layer_cost,
469 SYSDATE,
470 i_user_id,
471 SYSDATE,
472 i_user_id,
473 i_login_id,
474 i_req_id,
475 i_prg_appl_id,
476 i_prg_id,
477 SYSDATE
478 FROM cst_inv_layer_cost_details CILCD
479 WHERE CILCD.inv_layer_id = (
480 SELECT MIN(inv_layer_id)
481 FROM cst_inv_layers
482 WHERE layer_id = l_layer_id
483 AND layer_quantity > 0
484 AND creation_date = (
485 SELECT MIN(creation_date)
486 FROM cst_inv_layers
487 WHERE layer_id = l_layer_id
488 AND layer_quantity > 0
489 )
490 );
491 END IF; -- Check if Sales Order is referenced
492 END IF; -- Check if transaction is an RMA receipt
493
494 /* Call compute_layer_actual_cost for all transactions,
495 so that MCACD and MCLACD can be updated, contrary to average
496 costing where the function is called only if the cost hook
497 does not exist */
498
499 /* Changes for VMI. Adding planning transfer transaction */
500 if (i_txn_action_id NOT IN (2,5,3,12,21,28,55)) then
501 l_stmt_num := 80;
502
503 l_new_cost := CSTPLENG.compute_layer_actual_cost(
504 i_org_id,
505 i_cost_method,
506 i_txn_id,
507 i_layer_id,
508 l_cost_hook,
509 i_cost_type,
510 i_mat_ct_id,
511 i_avg_rates_id,
512 i_item_id,
513 i_txn_qty,
514 i_txn_action_id,
515 i_txn_src_type,
516 null,
517 i_exp_flag,
518 i_user_id,
519 i_login_id,
520 i_req_id,
521 i_prg_appl_id,
522 i_prg_id,
523 l_err_num,
524 l_err_code,
525 l_err_msg);
526
527
528 if (l_err_num <> 0) then
529 raise process_error;
530 end if;
531 end if;
532
533 /* If this transaction is a subinventory transfer then call the
534 sub_transfer special function. We treat interorg intransit
535 shipment for FOB receipt and interorg intransit for FOB shipment
536 as sub_transfer transactions. */
537 /* Changes for VMI. Adding planning transfer transaction */
538 if ((i_txn_action_id IN (2,5,28,55)) or
539 (i_txn_action_id = 21 and i_org_id = i_txn_org_id and i_fob_point = 2) or
540 (i_txn_action_id = 12 and i_org_id = i_txn_org_id and i_fob_point = 1)) then
541 l_stmt_num := 90;
542 CSTPLVCP.sub_transfer(
543 i_org_id,
544 i_txn_id,
545 i_layer_id,
546 i_cost_type,
547 i_item_id,
548 l_txn_qty,
549 i_txn_action_id,
550 i_txn_src_type,
551 l_new_cost,
552 l_cost_hook,
553 i_cost_method,
554 i_txfr_layer_id,
555 i_citw_flag,
556 i_flow_schedule,
557 i_mat_ct_id,
558 i_avg_rates_id,
559 i_user_id,
560 i_login_id,
561 i_req_id,
562 i_prg_appl_id,
563 i_prg_id,
564 l_err_num,
565 l_err_code,
566 l_err_msg);
567
568 if (l_err_num <> 0) then
569 raise process_error;
570 end if;
571 /* Update the layer costs, CQL, CLCD and item costs for
572 processed transactions */
573 elsif (i_exp_item <> 1) then
574 /* when we process transfer org's txn(i.e. intransit txfr),
575 we need to use txfr_layer_id instead. */
576 if (i_org_id <> i_txn_org_id) then
577 l_layer_id := i_txfr_layer_id;
578 l_txn_qty := -1 * l_txn_qty;
579 l_org_id := i_txn_org_id;
580 else
581 l_layer_id := i_layer_id;
582 l_org_id := i_org_id;
583 end if;
584
585 l_stmt_num := 100;
586
587 /* begin fix for bug 3679625 */
588 if (i_txn_action_id = 3 and i_org_id <> i_txn_org_id) then
589 -- for the receiving transaction of a direct interorg transfer,
590 -- if the receiving org is standard and item or sub is expense in the std org,
591 -- do not call calc_layer_average_cost
592 select primary_cost_method
593 into l_to_method
594 from mtl_parameters
595 where organization_id = i_txn_org_id;
596
597 if (l_to_method = 1) then
598 l_std_org := i_txn_org_id;
599 l_stmt_num := 102;
600 select decode(inventory_asset_flag, 'Y', 0, 1)
601 into l_to_std_exp
602 from mtl_system_items
603 where inventory_item_id = i_item_id
604 and organization_id = l_std_org;
605
606 l_stmt_num := 103;
607 select decode(l_to_std_exp,1,1,decode(asset_inventory,1,0,1))
608 into l_to_std_exp
609 from mtl_secondary_inventories msi,
610 mtl_material_transactions mmt
611 where mmt.transaction_id = i_txn_id
612 and mmt.organization_id = l_std_org
613 and msi.organization_id = l_std_org
614 and msi.secondary_inventory_name = mmt.subinventory_code;
615 end if;
616 end if;
617
618 if (i_org_id = i_txn_org_id or i_txn_action_id <> 3 or l_to_std_exp <> 1) then
619 l_stmt_num := 104;
620 CSTPLENG.calc_layer_average_cost (i_org_id,
621 i_txn_id,
622 l_layer_id,
623 i_cost_type,
624 i_item_id,
625 l_txn_qty,
626 i_txn_action_id,
627 l_cost_hook,
628 l_no_update_mmt,
629 0,
630 i_user_id,
631 i_login_id,
632 i_req_id,
633 i_prg_appl_id,
634 i_prg_id,
635 l_err_num,
636 l_err_code,
637 l_err_msg);
638 if (l_err_num <> 0) then
639 raise process_error;
640 end if;
641 end if;
642 end if;
643
644 -- For Internal Order Issue transactons to Expense destinations,
645 -- call Cost_LogicalSOReceipt API to cost the Receipt transaction.
646 l_stmt_num := 110;
647
648 select transaction_type_id
649 into l_txn_type_id
650 from mtl_material_transactions
651 where transaction_id = i_txn_id;
652
653 l_stmt_num := 120;
654 IF ( I_TXN_ACTION_ID = 1 AND L_TXN_TYPE_ID = 34 AND I_TXN_SRC_TYPE = 8) THEN
655 CSTPAVCP.Cost_LogicalSOReceipt (
656 p_parent_txn_id => i_txn_id,
657 p_user_id => i_user_id,
658 p_request_id => i_req_id,
659 p_prog_id => i_prg_id,
660 p_prog_app_id => i_prg_appl_id,
661 p_login_id => i_login_id,
662 x_err_num => l_err_num,
663 x_err_code => l_err_code,
664 x_err_msg => l_err_msg
665 );
666 IF l_err_num <> 0 THEN
667 RAISE PROCESS_ERROR;
668 END IF;
669 END IF;
670
671 EXCEPTION
672 when process_error then
673 o_err_num := l_err_num;
674 o_err_code := l_err_code;
675 o_err_msg := l_err_msg;
676 when OTHERS then
677 rollback;
678 o_err_num := SQLCODE;
679 o_err_msg := 'CSTPLVCP.Cost_Processor (' || to_char(l_stmt_num) || '): '
680 || substr(SQLERRM,1,200);
681 END cost_processor;
682
683 -- PROCEDURE
684 -- common_issue_to_wip
685 -- Cost process the common issue to wip transaction.
686
687 procedure common_issue_to_wip(
688 I_ORG_ID IN NUMBER,
689 I_TXN_ID IN NUMBER,
690 I_LAYER_ID IN NUMBER,
691 I_COST_TYPE IN NUMBER,
692 I_ITEM_ID IN NUMBER,
693 I_TXN_QTY IN NUMBER,
694 I_TXN_ACTION_ID IN NUMBER,
695 I_TXN_SRC_TYPE IN NUMBER,
696 I_NEW_COST IN NUMBER,
697 I_COST_HOOK IN NUMBER,
698 I_TXFR_LAYER_ID IN NUMBER,
699 I_COST_METHOD IN NUMBER,
700 I_AVG_RATES_ID IN NUMBER,
701 I_MAT_CT_ID IN NUMBER,
702 I_COST_GRP_ID IN NUMBER,
703 I_TXFR_COST_GRP IN NUMBER,
704 I_EXP_FLAG IN NUMBER,
705 I_EXP_ITEM IN NUMBER,
706 I_CITW_FLAG IN NUMBER,
707 I_FLOW_SCHEDULE IN NUMBER,
708 I_USER_ID IN NUMBER,
709 I_LOGIN_ID IN NUMBER,
710 I_REQ_ID IN NUMBER,
711 I_PRG_APPL_ID IN NUMBER,
712 I_PRG_ID IN NUMBER,
713 O_Err_Num OUT NOCOPY NUMBER,
714 O_Err_Code OUT NOCOPY VARCHAR2,
715 O_Err_Msg OUT NOCOPY VARCHAR2
716 ) IS
717 l_txn_qty NUMBER;
718 l_new_cost NUMBER;
719 l_exp_flag NUMBER;
720 l_err_num NUMBER;
721 l_err_code VARCHAR2(240);
722 l_err_msg VARCHAR2(240);
723 l_stmt_num NUMBER;
724 process_error EXCEPTION;
725 BEGIN
726 -- initialize local variables
727 l_err_num := 0;
728 l_err_code := '';
729 l_err_msg := '';
730
731 l_txn_qty := i_txn_qty;
732
733 l_stmt_num := 10;
734 -- item cost history, update the transfer_prior_costed_quantity
735 -- is necessary because we need both the from and the to information
736 -- in item cost history
737 UPDATE mtl_material_transactions mmt
738 SET TRANSFER_PRIOR_COSTED_QUANTITY =
739 (SELECT
740 layer_quantity
741 FROM cst_quantity_layers cql
742 WHERE cql.layer_id = i_txfr_layer_id)
743 WHERE mmt.transaction_id = i_txn_id
744 AND EXISTS (
745 SELECT 'X'
746 FROM cst_quantity_layers cql
747 WHERE cql.layer_id = i_txfr_layer_id);
748
749 -- item cost history
750 l_stmt_num := 20;
751
752 -- We break down common issue to WIP transaction into two parts.
753 -- 1. common to project sub
754 -- treat it as a subinventory transfer
755 -- 2. project sub to project job issue
756 -- insert a separate row in MCACD and call wip cost processor
757 -- and distribution processor
758
759 CSTPLVCP.sub_transfer(
760 i_org_id,
761 i_txn_id,
762 i_layer_id,
763 i_cost_type,
764 i_item_id,
765 l_txn_qty,
766 i_txn_action_id,
767 i_txn_src_type,
768 l_new_cost,
769 NULL,
770 i_cost_method,
771 i_txfr_layer_id,
772 i_citw_flag,
773 i_flow_schedule,
774 i_mat_ct_id,
775 i_avg_rates_id,
776 i_user_id,
777 i_login_id,
778 i_req_id,
779 i_prg_appl_id,
780 i_prg_id,
781 l_err_num,
782 l_err_code,
783 l_err_msg);
784
785 if (l_err_num <> 0) then
786 raise process_error;
787 end if;
788
789 l_stmt_num := 30;
790
791 -- figure out project sub asset type, it depends on project job type and
792 -- from subinventory asset type.
793 if (i_flow_schedule = 1) then
794 l_stmt_num := 35;
795 select decode(wac.class_type, 1, 0,
796 3, 0,
797 4, decode(i_exp_flag, 1, 1, 0))
798 into l_exp_flag
799 from mtl_material_transactions mmt,
800 wip_flow_schedules wfs,
801 wip_accounting_classes wac
802 where mmt.transaction_id = i_txn_id
803 and mmt.organization_id = i_org_id
804 and wfs.organization_id = i_org_id
805 and wfs.wip_entity_id = mmt.transaction_source_id
806 and wac.organization_id = i_org_id
807 and wac.class_code = wfs.class_code;
808 else
809 l_stmt_num := 37;
810 select decode(wac.class_type, 1, 0,
811 3, 0,
812 4, decode(i_exp_flag, 1, 1, 0))
813 into l_exp_flag
814 from mtl_material_transactions mmt,
815 wip_discrete_jobs wdj,
816 wip_accounting_classes wac
817 where mmt.transaction_id = i_txn_id
818 and mmt.organization_id = i_org_id
819 and wdj.organization_id = i_org_id
820 and wdj.wip_entity_id = mmt.transaction_source_id
821 and wac.organization_id = i_org_id
822 and wac.class_code = wdj.class_code;
823 end if;
824
825 l_stmt_num := 40;
826
827 CSTPLCWP.cost_wip_trx(i_txn_id,
828 i_citw_flag,
829 i_cost_type,
830 i_cost_method,
831 i_avg_rates_id,
832 i_cost_grp_id,
833 i_txfr_cost_grp,
834 l_exp_flag,
835 i_exp_item,
836 i_flow_schedule,
837 i_user_id,
838 i_login_id,
839 i_req_id,
840 i_prg_id,
841 i_prg_appl_id,
842 l_err_num,
843 l_err_code,
844 l_err_msg);
845
846
847 if (l_err_num <> 0) then
848 raise process_error;
849 end if;
850
851 EXCEPTION
852 when process_error then
853 o_err_num := l_err_num;
854 o_err_code := l_err_code;
855 o_err_msg := l_err_msg;
856 when others then
857 rollback;
858 o_err_num := SQLCODE;
859 o_err_msg := 'CSTPLVCP.common_issue_to_wip (' || to_char(l_stmt_num) ||
860 '): '
861 || substr(SQLERRM, 1,200);
862 END common_issue_to_wip;
863
864 -- PROCEDURE
865 -- Interorg
866 -- This procedure will compute the transfer cost of an intransit
867 -- interorg transaction. It will also compute the transaction cost
868 -- of this transfer.
869 procedure interorg(
870 I_ORG_ID IN NUMBER,
871 I_TXN_ID IN NUMBER,
872 I_COST_METHOD IN NUMBER,
873 I_LAYER_ID IN NUMBER,
874 I_COST_TYPE IN NUMBER,
875 I_ITEM_ID IN NUMBER,
876 I_TXN_ACTION_ID IN NUMBER,
877 I_TXN_SRC_TYPE IN NUMBER,
878 I_TXN_ORG_ID IN NUMBER,
879 I_TXFR_ORG_ID IN NUMBER,
880 I_COST_GRP_ID IN NUMBER,
881 I_TXFR_COST_GRP IN NUMBER,
882 I_FOB_POINT IN NUMBER,
883 I_MAT_CT_ID IN NUMBER,
884 I_AVG_RATES_ID IN NUMBER,
885 I_USER_ID IN NUMBER,
886 I_LOGIN_ID IN NUMBER,
887 I_REQ_ID IN NUMBER,
888 I_PRG_APPL_ID IN NUMBER,
889 I_PRG_ID IN NUMBER,
890 I_TPRICE_OPTION IN NUMBER,
891 I_TXF_PRICE IN NUMBER,
892 O_TXN_QTY IN OUT NOCOPY NUMBER,
893 O_INTERORG_REC IN OUT NOCOPY NUMBER,
894 O_NO_UPDATE_MMT IN OUT NOCOPY NUMBER,
895 O_EXP_FLAG IN OUT NOCOPY NUMBER,
896 O_Err_Num OUT NOCOPY NUMBER,
897 O_Err_Code OUT NOCOPY VARCHAR2,
898 O_Err_Msg OUT NOCOPY VARCHAR2
899 ) IS
900 l_err_num NUMBER;
901 l_err_code VARCHAR2(240);
902 l_err_msg VARCHAR2(240);
903 l_stmt_num NUMBER;
904 process_error EXCEPTION;
905 l_txn_update_id NUMBER;
906 l_compute_txn_cost NUMBER;
907 l_from_org NUMBER;
908 l_to_org NUMBER;
909 l_from_cost_grp NUMBER;
910 l_to_cost_grp NUMBER;
911 l_cost_type_id NUMBER;
912 l_snd_txn_cost NUMBER;
913 l_rcv_txn_cost NUMBER;
914 l_txn_cost NUMBER;
915 l_txfr_cost NUMBER;
916 l_trans_cost NUMBER;
917 l_std_from_org NUMBER;
918 l_std_to_org NUMBER;
919 l_std_org NUMBER;
920 l_std_cost_org NUMBER; /* bugfix 3048258 */
921 l_std_exp NUMBER;
922 l_update_std NUMBER;
923 l_snd_sob_id NUMBER;
924 l_snd_curr VARCHAR2(10);
925 l_rcv_sob_id NUMBER;
926 l_rcv_curr VARCHAR2(10);
927 l_curr_type VARCHAR2(30);
928 l_conv_rate NUMBER;
929 l_conv_date DATE;
930 l_snd_uom VARCHAR2(3);
931 l_rcv_uom VARCHAR2(3);
932 l_snd_qty NUMBER;
933 l_count NUMBER;
934 -- item cost history stuff
935 l_which_org NUMBER;
936 l_which_cst_grp NUMBER;
937 -- item cost history stuff
938 -- elemental visibility
939 l_movh NUMBER;
940 l_movh_cost NUMBER;
941 l_rec_movh_cost NUMBER;
942 l_mctcd_ovhd NUMBER;
943 l_from_layer_id NUMBER;
944 l_elemental_visible varchar2(1);
945 -- elemental visibility
946 l_um_rate NUMBER;
947 l_new_cost NUMBER;
948 -- FIFO/LIFO
949 l_from_method NUMBER;
950 l_to_method NUMBER;
951 l_from_layer NUMBER;
952 l_to_layer NUMBER;
953 l_mclacd_exists NUMBER;
954 l_create_layers NUMBER;
955 l_debug VARCHAR2(80);
956 /* moh variables */
957 l_return_status VARCHAR2(1);
958 l_msg_count NUMBER;
959 l_msg_data VARCHAR2(240);
960 l_earn_moh NUMBER;
961 moh_rules_error EXCEPTION;
962
963 -- Added for bug 2827548
964 l_xfer_conv_rate NUMBER;
965 l_new_txn_cost NUMBER;
966
967 -- Added for bug 3679625
968 l_txfr_txn_id NUMBER;
969 l_txfr_std_exp NUMBER;
970 -- Added for bug 3761538
971 l_to_std_exp NUMBER;
972 l_interorg_elem_exp_flag NUMBER;
973
974 BEGIN
975 -- initialize local variables
976 l_err_num := 0;
977 l_err_code := '';
978 l_err_msg := '';
979 l_update_std := 0;
980 l_snd_qty := o_txn_qty;
981 l_std_exp := 0;
982 l_from_layer := 0;
983 l_to_layer := 0;
984 l_create_layers := 1;
985 l_debug := FND_PROFILE.value('MRP_DEBUG');
986
987 l_earn_moh := 1;
988 l_return_status := fnd_api.g_ret_sts_success;
989 l_msg_count := 0;
990
991 l_txfr_std_exp := 0;
992 l_to_std_exp := 0;
993
994 -- Figure the from and to org for this transaction.
995 if (i_txn_action_id = 21) then
996 l_from_org := i_txn_org_id;
997 l_to_org := i_txfr_org_id;
998 l_from_cost_grp := i_cost_grp_id;
999 l_to_cost_grp := i_txfr_cost_grp;
1000 elsif (i_txn_action_id = 12) then
1001 l_from_org := i_txfr_org_id;
1002 l_to_org := i_txn_org_id;
1003 l_from_cost_grp := i_txfr_cost_grp;
1004 l_to_cost_grp := i_cost_grp_id;
1005 elsif (i_txn_action_id =3 and o_txn_qty <0) then
1006 l_from_org := i_txn_org_id;
1007 l_to_org := i_txfr_org_id;
1008 l_from_cost_grp := i_cost_grp_id;
1009 l_to_cost_grp := i_txfr_cost_grp;
1010 else
1011 l_from_org := i_txfr_org_id;
1012 l_to_org := i_txn_org_id;
1013 l_from_cost_grp := i_txfr_cost_grp;
1014 l_to_cost_grp := i_cost_grp_id;
1015 end if;
1016
1017 l_stmt_num := 2;
1018 select primary_cost_method
1019 into l_from_method
1020 from mtl_parameters
1021 where organization_id = l_from_org;
1022
1023 select primary_cost_method
1024 into l_to_method
1025 from mtl_parameters
1026 where organization_id = l_to_org;
1027
1028 l_stmt_num := 3;
1029 if ((l_from_method NOT IN (5,6)) and (l_to_method NOT IN (5,6))) then
1030 raise process_error;
1031 end if;
1032
1033 l_stmt_num := 6;
1034 select NVL(elemental_visibility_enabled,'N')
1035 into l_elemental_visible
1036 from mtl_interorg_parameters
1037 where from_organization_id = l_from_org
1038 and to_organization_id = l_to_org;
1039
1040 l_stmt_num := 10;
1041
1042 /* Bug 2926258 - default l_std_org to -1 to support org_id=0 */
1043 if (l_from_method = 1) then
1044 l_std_org := l_from_org;
1045 l_std_from_org := 1;
1046 l_std_to_org := 0;
1047 elsif (l_to_method = 1) then
1048 l_std_org := l_to_org;
1049 l_std_from_org := 0;
1050 l_std_to_org := 1;
1051 else
1052 l_std_org := -1;
1053 l_std_from_org := 0;
1054 l_std_to_org := 0;
1055 end if;
1056
1057
1058 FND_FILE.PUT_LINE(FND_FILE.LOG,'Standard costing org : ' || to_char(l_std_org));
1059
1060 if(l_debug = 'Y') then
1061 fnd_file.put_line(fnd_file.log, 'In interorg(..)');
1062 fnd_file.put_line(fnd_file.log, 'l_std_org' || l_std_org);
1063 fnd_file.put_line(fnd_file.log, 'l_std_from_org' || l_std_from_org);
1064 fnd_file.put_line(fnd_file.log, 'l_std_to_org' || l_std_to_org);
1065 fnd_file.put_line(fnd_file.log, 'i_fob_point' || i_fob_point);
1066 end if;
1067
1068 /* bug 3048258: For std costing, item cost from cost organization should be used */
1069 l_stmt_num := 15;
1070 IF ( l_std_from_org = 1 OR l_std_to_org = 1 ) THEN
1071 select cost_organization_id
1072 into l_std_cost_org
1073 from mtl_parameters
1074 where organization_id = l_std_org;
1075 END IF;
1076
1077 if (l_std_org = i_txn_org_id) then
1078 l_stmt_num := 20;
1079 select decode(inventory_asset_flag, 'Y', 0, 1)
1080 into l_std_exp
1081 from mtl_system_items
1082 where inventory_item_id = i_item_id
1083 and organization_id = l_std_org;
1084
1085 l_stmt_num := 30;
1086 select decode(l_std_exp,1,1,decode(asset_inventory,1,0,1))
1087 into l_std_exp
1088 from mtl_secondary_inventories msi
1089 ,mtl_material_transactions mmt
1090 where mmt.transaction_id = i_txn_id
1091 and mmt.organization_id = l_std_org
1092 and msi.organization_id = l_std_org
1093 and msi.secondary_inventory_name = mmt.subinventory_code;
1094 end if;
1095
1096 get_snd_rcv_uom(i_item_id, l_from_org, l_to_org, l_snd_uom, l_rcv_uom,
1097 l_err_num, l_err_code, l_err_msg);
1098 if (l_err_num <> 0) then
1099 raise process_error;
1100 end if;
1101
1102 -- If the transaction organization id is not the organization id of this
1103 -- cost worker then we have to make sure this transaction record in mmt
1104 -- does not get updated. Most likely this is an intrasit interorg transaction
1105 -- and we are processing either the shipping or the receiving side. For the
1106 -- same reason we cannot rely on the expense flag since it is based on
1107 -- the current record's subinventory code.
1108 --
1109 if ((i_org_id <> i_txn_org_id) and (i_txn_action_id in (12,21))) then
1110 o_no_update_mmt := 1;
1111 if (l_from_org = i_org_id) then
1112 o_txn_qty := inv_convert.inv_um_convert
1113 (i_item_id, NULL, o_txn_qty,
1114 l_rcv_uom, l_snd_uom, NULL, NULL);
1115 l_snd_qty := o_txn_qty;
1116 else
1117 o_txn_qty := inv_convert.inv_um_convert
1118 (i_item_id, NULL, o_txn_qty,
1119 l_snd_uom, l_rcv_uom, NULL, NULL);
1120 end if;
1121
1122 l_stmt_num := 40;
1123
1124 select decode(inventory_asset_flag, 'Y',0,1)
1125 into o_exp_flag
1126 from mtl_system_items
1127 where inventory_item_id = i_item_id
1128 and organization_id = i_org_id;
1129 end if;
1130
1131 -- The following are considered interorg receipt transactions.
1132 -- These are transactions where ownership changes and the current org
1133 -- is the receiving org.
1134 if ((i_txn_action_id = 3 and o_txn_qty > 0 and i_txn_org_id = i_org_id) OR
1135 (i_txn_action_id = 21 and i_txfr_org_id = i_org_id and i_fob_point = 1)
1136 OR (i_txn_action_id = 12 and i_txn_org_id = i_org_id and i_fob_point = 2))
1137 then
1138 o_interorg_rec := 1;
1139 else
1140 o_interorg_rec :=0;
1141 end if;
1142
1143 -- item cost history stuff
1144
1145 if( ( (i_txn_action_id = 21) and (i_fob_point = 1) and (l_std_to_org = 0) ) OR
1146 ( (i_txn_action_id = 12) and (i_fob_point = 1) and (l_std_to_org = 0) ) OR
1147 ( (i_txn_action_id = 21) and (i_fob_point = 2) and (l_std_from_org = 0) ) OR
1148 ( (i_txn_action_id = 12) and (i_fob_point = 2) and (l_std_to_org = 0) ) ) then
1149 -- intransit ship, fob ship, receiving org is avg org.
1150 if ( (i_txn_action_id = 21) and (i_fob_point = 1) and (l_std_to_org = 0) ) then
1151 l_which_org := l_to_org;
1152 l_which_cst_grp := i_txfr_cost_grp;
1153 -- intransit receipt, fob ship, receiving org is avg org.
1154 elsif ( (i_txn_action_id = 12) and (i_fob_point = 1) and (l_std_to_org = 0) ) then
1155 l_which_org := l_to_org;
1156 l_which_cst_grp := i_cost_grp_id;
1157 -- intransit ship, fob receipt, sending org is avg org.
1158 -- bug 729138
1159 elsif ( (i_txn_action_id = 21) and (i_fob_point = 2) and (l_std_from_org = 0) ) then
1160 l_which_org := l_from_org;
1161 l_which_cst_grp := i_txfr_cost_grp;
1162 -- intransit receipt, fob receipt, receiving org is avg org.
1163 elsif ( (i_txn_action_id = 12) and (i_fob_point = 2) and (l_std_to_org = 0) ) then
1164 l_which_org := l_from_org;
1165 l_which_cst_grp := i_txfr_cost_grp;
1166 end if;
1167
1168 if i_org_id = l_which_org then -- this takes care the case R/R,
1169 -- cost worker will process the same mmt
1170 -- transaction twice
1171 l_stmt_num := 50;
1172 UPDATE mtl_material_transactions mmt
1173 SET TRANSFER_PRIOR_COSTED_QUANTITY =
1174 (SELECT
1175 layer_quantity
1176 FROM cst_quantity_layers cql
1177 WHERE cql.organization_id = l_which_org
1178 AND cql.inventory_item_id = i_item_id
1179 AND cql.cost_group_id = l_which_cst_grp)
1180 WHERE mmt.transaction_id = i_txn_id
1181 AND EXISTS (
1182 SELECT 'X'
1183 FROM cst_quantity_layers cql
1184 WHERE cql.organization_id = l_which_org
1185 AND cql.inventory_item_id = i_item_id
1186 AND cql.cost_group_id = l_which_cst_grp);
1187
1188 IF SQL%ROWCOUNT = 0 THEN
1189 update mtl_material_transactions mmt
1190 set TRANSFER_PRIOR_COSTED_QUANTITY = 0
1191 where mmt.transaction_id = i_txn_id;
1192 END IF;
1193 end if;
1194 end if;
1195
1196 ---- end for item cost history
1197
1198 -- bug 2827548 - took following IF condition out of the one that follows it because we
1199 -- need to update txn_cost of receit txn before returning if shipment is already costed
1200 if (i_txn_action_id = 12 and i_org_id = i_txn_org_id and i_fob_point = 1) then
1201 UPDATE mtl_material_transactions mmt
1202 SET mmt.transaction_cost = (select (mmt1.transaction_cost * mmt1.currency_conversion_rate)
1203 from mtl_material_transactions mmt1
1204 where mmt1.transaction_id = mmt.transfer_transaction_id
1205 and mmt1.costed_flag is null)
1206 WHERE mmt.transaction_id = i_txn_id
1207 AND nvl(mmt.transaction_cost,0) = 0;
1208 return;
1209 end if;
1210
1211 -- If this is an intransit shipment with FOB point receipt or intransit
1212 -- receipt with FOB point shipment or if this is an interorg receipt
1213 -- transaction from another average cost org, or if this is a direct
1214 -- interorg receipt transaction, then we are all done!!!
1215 if (i_txn_action_id = 21 and i_org_id = i_txn_org_id and i_fob_point = 2) then
1216
1217 return;
1218 /* Consume or create layers as necessary */
1219 -- elsif (o_exp_flag <> 1) then
1220 else
1221 if ((i_txn_action_id = 3 and o_txn_qty > 0)
1222 OR (i_txn_action_id = 12 and i_org_id = i_txn_org_id and i_fob_point = 2)
1223 OR (i_txn_action_id = 21 and i_org_id = i_txfr_org_id and i_fob_point = 1)) then
1224 if (l_to_method <> 1) then
1225 select nvl(layer_id,0) into l_to_layer from cst_quantity_layers
1226 where organization_id = l_to_org and inventory_item_id = i_item_id
1227 and cost_group_id = l_to_cost_grp;
1228 end if;
1229
1230
1231 FND_FILE.PUT_LINE(FND_FILE.LOG,'----------l_to_method---------');
1232 FND_FILE.PUT_LINE(FND_FILE.LOG,'=' || l_to_method);
1233
1234 FND_FILE.PUT_LINE(FND_FILE.LOG,'Interorg transfer receiving org: (create layers) ...');
1235 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(i_txn_org_id) || ':' || to_char(l_to_method));
1236 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(i_txn_id) || ':' || to_char(l_to_layer));
1237
1238 /* Bug #2352604, 2362306.
1239 Call create_layers when :
1240 Direct Interorg transfers
1241 Intransit shipment, FOB shipment from non-std to LIFO
1242 Intransit receipt, FOB receipt from non-std to LIFO
1243 */
1244 if ((l_to_method IN (5,6)) and (l_from_method <> 1 or i_txn_action_id = 3)) then
1245 CSTPLENG.create_layers(
1246 i_org_id,
1247 i_txn_id,
1248 l_to_layer,
1249 i_item_id,
1250 abs(o_txn_qty),
1251 i_cost_method,
1252 i_txn_src_type,
1253 i_txn_action_id,
1254 0,
1255 o_interorg_rec, --bug 2280515 (anjgupta)
1256 i_cost_type,
1257 i_mat_ct_id,
1258 i_avg_rates_id,
1259 o_exp_flag,
1260 i_user_id,
1261 i_login_id,
1262 i_req_id,
1263 i_prg_appl_id,
1264 i_prg_id,
1265 l_err_num,
1266 l_err_code,
1267 l_err_msg);
1268 end if;
1269 elsif ((i_txn_action_id = 3 and o_txn_qty < 0)
1270 OR (i_txn_action_id = 12 and i_org_id = i_txfr_org_id and i_fob_point = 2)
1271 OR (i_txn_action_id = 21 and i_org_id = i_txn_org_id and i_fob_point = 1)) then
1272 if (l_from_method <> 1) then
1273 select nvl(layer_id,0)
1274 into l_from_layer
1275 from cst_quantity_layers
1276 where organization_id = l_from_org
1277 and inventory_item_id = i_item_id
1278 and cost_group_id = l_from_cost_grp;
1279 end if;
1280
1281 FND_FILE.PUT_LINE(FND_FILE.LOG,'Interorg transfer send org: (consume layers) ...');
1282 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(i_txn_org_id) || ':' || to_char(l_from_method));
1283 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(i_txn_id) || ':' || to_char(l_from_layer));
1284 if (l_from_method IN (5,6)) then
1285 CSTPLENG.consume_layers(
1286 i_org_id,
1287 i_txn_id,
1288 l_from_layer,
1289 i_item_id,
1290 -1*abs(o_txn_qty),
1291 i_cost_method,
1292 i_txn_src_type,
1293 i_txn_action_id,
1294 0,
1295 o_interorg_rec, --bug 2280515
1296 i_cost_type,
1297 i_mat_ct_id,
1298 i_avg_rates_id,
1299 o_exp_flag,
1300 i_user_id,
1301 i_login_id,
1302 i_req_id,
1303 i_prg_appl_id,
1304 i_prg_id,
1305 l_err_num,
1306 l_err_code,
1307 l_err_msg);
1308 end if;
1309 end if;
1310
1311
1312 if (l_err_num <> 0) then
1313 raise process_error;
1314 end if;
1315 end if;
1316
1317
1318 if (o_interorg_rec = 1 and (i_txn_action_id = 3 or l_std_from_org <> 1))
1319 then
1320 return;
1321 end if;
1322
1323 /***********************************************************************
1324 ** In the following conditions we will be doing distribution for the **
1325 ** standard org, so need populate mtl_cst_actual_cost_details with **
1326 ** the standard costs. **
1327 ** 1. intransit interorg and one of the orgs is standard. **
1328 ** 2. direct interorg and the txn_org_id is standard. **
1329 ***********************************************************************/
1330 if ((i_txn_action_id = 3 and l_std_org = i_txn_org_id) OR
1331 (i_txn_action_id in (12,21) and
1332 (l_std_from_org = 1 or l_std_to_org = 1))) then
1333
1334 /* for bug 3761538 */
1335 if (i_txn_action_id in (12,21) and i_fob_point = 1 and l_std_to_org = 1) then
1336 -- for the receiving transaction of a intransit fob shipment interorg transfer,
1337 -- if the receiving org is standard and item is expense in the std org, set l_to_std_exp
1338 -- = 1 to later insert into mcacd from mcacd.
1339 l_stmt_num := 102;
1340 select decode(inventory_asset_flag, 'Y', 0, 1)
1341 into l_to_std_exp
1342 from mtl_system_items
1343 where inventory_item_id = i_item_id
1344 and organization_id = l_std_org;
1345 end if;
1346 if (l_to_std_exp = 1) then
1347 fnd_file.put_line(fnd_file.log, 'item is expense in receiving std org');
1348 elsif
1349 /* end for bug 3671538 */
1350 ((l_std_exp <> 1) or (l_std_from_org = 1) or
1351 (l_std_to_org = 1 and i_txn_action_id = 12 and i_fob_point = 1))
1352 then
1353
1354 /* Use standard costs only for non-expense or not interorg shipements*/
1355 /* Need to use sending org cost for expense interorg receipts */
1356 l_stmt_num := 60;
1357
1358 l_count := 0;
1359
1360 select count(*)
1361 into l_count
1362 from cst_item_cost_details
1363 where /* organization_id = l_std_org : bugfix 3048258 */
1364 organization_id = l_std_cost_org
1365 and cost_type_id = 1
1366 and inventory_item_id = i_item_id;
1367
1368 l_stmt_num := 70;
1369 /* If no rows exist in cicd (item hasn't been costed), insert into */
1370 /* mcacd using 0 value of this level material */
1371 if (l_count > 0) then
1372 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for std org using cost from CICD');
1373 insert into mtl_cst_actual_cost_details (
1374 transaction_id,
1375 organization_id,
1376 layer_id,
1377 cost_element_id,
1378 level_type,
1379 transaction_action_id,
1380 last_update_date,
1381 last_updated_by,
1382 creation_date,
1383 created_by,
1384 last_update_login,
1385 request_id,
1386 program_application_id,
1387 program_id,
1388 program_update_date,
1389 inventory_item_id,
1390 actual_cost,
1391 prior_cost,
1392 new_cost,
1393 insertion_flag,
1394 variance_amount,
1395 user_entered)
1396 select i_txn_id,
1397 l_std_org,
1398 -1,
1399 cicd.cost_element_id,
1400 cicd.level_type,
1401 i_txn_action_id,
1402 sysdate,
1403 i_user_id,
1404 sysdate,
1405 i_user_id,
1406 i_login_id,
1407 i_req_id,
1408 i_prg_appl_id,
1409 i_prg_id,
1410 sysdate,
1411 i_item_id,
1412 nvl(sum(cicd.item_cost),0),
1413 NULL,
1414 NULL,
1415 'N',
1416 0,
1417 'N'
1418 from cst_item_cost_details cicd
1419 where /* organization_id = l_std_org : bugfix 3048258 */
1420 organization_id = l_std_cost_org
1421 and cost_type_id = 1
1422 and inventory_item_id = i_item_id
1423 group by cost_element_id, level_type;
1424 else
1425 FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert into MCACD for std org using 0 cost');
1426 insert into mtl_cst_actual_cost_details (
1427 transaction_id,
1428 organization_id,
1429 layer_id,
1430 cost_element_id,
1431 level_type,
1432 transaction_action_id,
1433 last_update_date,
1434 last_updated_by,
1435 creation_date,
1436 created_by,
1437 last_update_login,
1438 request_id,
1439 program_application_id,
1440 program_id,
1441 program_update_date,
1442 inventory_item_id,
1443 actual_cost,
1444 prior_cost,
1445 new_cost,
1446 insertion_flag,
1447 variance_amount,
1448 user_entered)
1449 values ( i_txn_id,
1450 l_std_org,
1451 -1,
1452 1,
1453 1,
1454 i_txn_action_id,
1455 sysdate,
1456 i_user_id,
1457 sysdate,
1458 i_user_id,
1459 i_login_id,
1460 i_req_id,
1461 i_prg_appl_id,
1462 i_prg_id,
1463 sysdate,
1464 i_item_id,
1465 0,
1466 NULL,
1467 NULL,
1468 'N',
1469 0,
1470 'N');
1471 end if;
1472
1473 -- Need to apply material overheads if standard org is receiving
1474 if (l_std_to_org =1) then
1475 l_stmt_num := 80;
1476 /* Changes for MOH Absorption */
1477 cst_mohRules_pub.apply_moh(
1478 1.0,
1479 p_organization_id => l_std_org,
1480 p_earn_moh =>l_earn_moh,
1481 p_txn_id => i_txn_id,
1482 p_item_id => i_item_id,
1483 x_return_status => l_return_status,
1484 x_msg_count => l_msg_count,
1485 x_msg_data => l_msg_data);
1486
1487 IF l_return_status <> FND_API.g_ret_sts_success THEN
1488
1489 CST_UTILITY_PUB.writelogmessages
1490 ( p_api_version => 1.0,
1491 p_msg_count => l_msg_count,
1492 p_msg_data => l_msg_data,
1493 x_return_status => l_return_status);
1494 RAISE moh_rules_error;
1495 END IF;
1496
1497 if (l_earn_moh = 0 ) then
1498 FND_FILE.put_line(fnd_file.log, '--Material Overhead Absorption Overridden--');
1499 else
1500
1501 Insert into mtl_actual_cost_subelement(
1502 layer_id,
1503 transaction_id,
1504 organization_id,
1505 cost_element_id,
1506 level_type,
1507 resource_id,
1508 last_update_date,
1509 last_updated_by,
1510 creation_date,
1511 created_by,
1512 last_update_login,
1513 request_id,
1514 program_application_id,
1515 program_id,
1516 program_update_date,
1517 actual_cost,
1518 user_entered)
1519 select -1, i_txn_id,
1520 l_std_org,
1521 cicd.cost_element_id,
1522 cicd.level_type,
1523 cicd.resource_id,
1524 sysdate,
1525 i_user_id,
1526 sysdate,
1527 i_user_id,
1528 i_login_id,
1529 i_req_id,
1530 i_prg_appl_id,
1531 i_prg_id,
1532 sysdate,
1533 cicd.item_cost,
1534 'N'
1535 from cst_item_cost_details cicd
1536 where inventory_item_id = i_item_id
1537 and /* organization_id = l_std_org : bugfix 3048258 */
1538 organization_id = l_std_cost_org
1539 and cost_type_id = 1
1540 and cost_element_id = 2
1541 and level_type = 1;
1542 /* Bug 2277950 - Earn only THIS level Material Overhead */
1543 end if;
1544 END IF;
1545
1546 if (i_txn_org_id = l_std_org) then
1547 -- update actual cost column of mmt.
1548 l_stmt_num := 90;
1549
1550 update mtl_material_transactions mmt
1551 set (last_update_date,
1552 last_updated_by,
1553 last_update_login,
1554 request_id,
1555 program_application_id,
1556 program_id,
1557 program_update_date,
1558 actual_cost) =
1559 (select sysdate,
1560 i_user_id,
1561 i_login_id,
1562 i_req_id,
1563 i_prg_appl_id,
1564 i_prg_id,
1565 sysdate,
1566 nvl(sum(actual_cost),0)
1567 from mtl_cst_actual_cost_details cacd
1568 where cacd.transaction_id = i_txn_id
1569 and cacd.organization_id = l_std_org
1570 and cacd.layer_id = -1)
1571 where mmt.transaction_id = i_txn_id;
1572 end if;
1573 else
1574 l_update_std := 1;
1575 end if;
1576 end if;
1577
1578 -- If this is a direct interorg transfer then we need to update the
1579 -- transaction cost and transaction cost details using the transaction_id
1580 -- in the transfer_transaction_id.
1581
1582 if (i_txn_action_id = 3) then
1583 l_stmt_num := 100;
1584
1585 select transfer_transaction_id
1586 into l_txn_update_id
1587 from mtl_material_transactions
1588 where transaction_id = i_txn_id;
1589 else
1590 l_txn_update_id := i_txn_id;
1591 end if;
1592
1593 -- If we are shipping from a standard cost org to an average cost org, the
1594 -- transaction cost must be computed at the time of the average cost worker
1595 -- for the receiving organization. This is an exception to the general case
1596 -- where the shipping organization always figures out the transaction cost
1597 -- and populate the details rows for the receiving org.
1598 if ((i_txn_action_id = 21 and i_fob_point = 1 and l_to_org = i_org_id)
1599 OR
1600 (i_txn_action_id = 12 and i_fob_point = 2 and l_to_org = i_org_id)
1601 OR
1602 (i_txn_action_id = 3 and o_txn_qty < 0 and l_std_org = i_txn_org_id)) then
1603 l_compute_txn_cost := l_std_from_org;
1604 l_cost_type_id := 1;
1605 elsif ((i_txn_action_id = 21 and i_fob_point = 1 and l_from_org = i_org_id)
1606 OR
1607 (i_txn_action_id = 12 and i_fob_point = 2 and l_from_org = i_org_id)
1608 OR
1609 (i_txn_action_id = 3 and o_txn_qty < 0 and l_from_org = i_org_id))
1610 then
1611 l_compute_txn_cost := 2;
1612 l_cost_type_id := i_cost_type;
1613 else
1614 l_compute_txn_cost := 0;
1615 end if;
1616
1617 -- compute transfer cost and compute transaction cost.
1618 if (l_compute_txn_cost > 0) then
1619 -- Figure out the transaction cost from the sending org.
1620 if (l_compute_txn_cost = 1) then
1621 l_stmt_num := 110;
1622 /* Exception block inserted for bug 1399079, (non costed items) */
1623 BEGIN
1624 select item_cost, -1
1625 into l_snd_txn_cost, l_from_layer_id
1626 from cst_item_costs
1627 where cost_type_id = l_cost_type_id
1628 and inventory_item_id = i_item_id
1629 /* and organization_id = l_from_org; : bugfix 3048258 */
1630 and organization_id = l_std_cost_org;
1631 EXCEPTION
1632 when no_data_found then
1633 l_snd_txn_cost := 0;
1634 l_from_layer_id := -1;
1635 END;
1636
1637 else
1638 l_stmt_num := 115;
1639 select count(*) into l_mclacd_exists
1640 from mtl_cst_layer_act_cost_details
1641 where transaction_id = i_txn_id
1642 and organization_id = i_org_id
1643 and layer_id = l_from_layer;
1644
1645 l_stmt_num :=120;
1646 if ((o_exp_flag = 1) and (l_mclacd_exists = 0)) then
1647 select item_cost, layer_id
1648 into l_snd_txn_cost, l_from_layer_id
1649 from cst_quantity_layers
1650 where organization_id = l_from_org
1651 and inventory_item_id = i_item_id
1652 and cost_group_id = l_from_cost_grp;
1653
1654 else
1655 l_stmt_num := 130;
1656
1657 select NVL(abs(sum(mclacd.actual_cost * mclacd.layer_quantity) / abs(o_txn_qty)),0)
1658 into l_snd_txn_cost
1659 from mtl_cst_layer_act_cost_details mclacd
1660 where transaction_id = i_txn_id
1661 and organization_id = i_org_id
1662 and layer_id = l_from_layer;
1663
1664 l_from_layer_id := i_layer_id;
1665 end if;
1666 end if;
1667
1668 -- Get the conversion_rate.
1669 -- receiving_currency = sending_currency * conversion_rate
1670 l_stmt_num := 140;
1671 get_snd_rcv_rate(i_txn_id, l_from_org, l_to_org,
1672 l_snd_sob_id, l_snd_curr, l_rcv_sob_id, l_rcv_curr,
1673 l_curr_type,
1674 l_conv_rate, l_conv_date, l_err_num, l_err_code,
1675 l_err_msg);
1676 if (l_err_num <> 0) then
1677 raise process_error;
1678 end if;
1679
1680 -- Need to get UOM conversion
1681 -- l_snd_uom = l_um_rate * l_rcv_uom
1682 -- 1 Dozen = 12 * 1 each
1683 l_um_rate := inv_convert.inv_um_convert(i_item_id, NULL, 1,
1684 l_snd_uom, l_rcv_uom, NULL, NULL);
1685
1686 -- Added for bug 2827548
1687 if (i_txn_action_id = 12 and i_fob_point = 2) then -- receiving txn for FOB receipt
1688 l_xfer_conv_rate := l_conv_rate;
1689 else
1690 l_xfer_conv_rate := 1;
1691 end if;
1692
1693 -- the transfer cost is always in shipping UOM and currency
1694 -- For FOB receipt, need to convert the primary_quantity (in receiving UOM)
1695 -- to sending primary quantity.
1696 l_stmt_num := 150;
1697 Update mtl_material_transactions
1698 Set transfer_cost =
1699 (select decode(nvl(transfer_percentage, -999),-999, transfer_cost,
1700 (transfer_percentage * l_snd_txn_cost *
1701 decode(i_txn_action_id, 12, abs(primary_quantity)/l_um_rate,
1702 abs(primary_quantity)))*l_xfer_conv_rate/100) -- bug 2827548-added l_xfer_conv_rate
1703 from mtl_material_transactions
1704 where transaction_id = i_txn_id)
1705 where transaction_id = i_txn_id
1706 or (transaction_id = decode(i_txn_action_id,3,l_txn_update_id,-1));
1707
1708 -- Get transfer cost and transportation cost from mmt which is in sending currency.
1709 l_stmt_num := 160;
1710
1711 select nvl(transfer_cost,0), nvl(transportation_cost,0),
1712 decode(i_txn_action_id,12,(primary_quantity / l_um_rate),primary_quantity)
1713 into l_txfr_cost, l_trans_cost, l_snd_qty
1714 from mtl_material_transactions
1715 where transaction_id = i_txn_id;
1716
1717 /* TPRICE: If the transfer pricing option is yes, set transfer credit to be zero */
1718 if (i_tprice_option <> 0) then
1719 l_txfr_cost := 0;
1720 end if;
1721
1722 -- change for bug 2827548
1723 if (i_txn_action_id = 12 and i_fob_point = 2) then
1724 l_rcv_txn_cost := ( ((l_snd_txn_cost * abs(l_snd_qty)) * l_conv_rate / l_um_rate) +
1725 l_txfr_cost + l_trans_cost) / abs(l_snd_qty);
1726 l_new_txn_cost := l_rcv_txn_cost;
1727 elsif ((i_txn_action_id = 12 and i_fob_point = 1) or (i_txn_action_id = 3 and o_txn_qty > 0)) then
1728 l_rcv_txn_cost := ( ((l_snd_txn_cost * abs(l_snd_qty)) + l_txfr_cost + l_trans_cost) *
1729 l_conv_rate / l_um_rate) / abs(l_snd_qty);
1730 l_new_txn_cost := l_rcv_txn_cost;
1731 else
1732 l_new_txn_cost := (l_snd_txn_cost * abs(l_snd_qty) + l_txfr_cost +
1733 l_trans_cost) / abs(l_snd_qty);
1734 l_rcv_txn_cost := l_new_txn_cost * l_conv_rate / l_um_rate;
1735 end if;
1736 if (i_txn_action_id<>12 or i_fob_point<>2) then
1737 l_trans_cost := l_trans_cost * l_conv_rate;
1738 l_txfr_cost := l_txfr_cost * l_conv_rate;
1739 end if;
1740
1741 /* TPRICE: If the transfter pricing option is to treat the price as the incoming cost,
1742 insert price into MCTCD */
1743 if (i_tprice_option = 2) then
1744 l_rcv_txn_cost := i_txf_price;
1745 l_elemental_visible := 'N';
1746 end if;
1747
1748 if (l_elemental_visible = 'Y') then
1749 if ((o_exp_flag = 1) and (l_mclacd_exists = 0)) then
1750 l_interorg_elem_exp_flag := 1;
1751 else
1752 l_interorg_elem_exp_flag := 0;
1753 end if;
1754
1755 interorg_elemental_detail(i_org_id,i_txn_id,l_compute_txn_cost,
1756 l_cost_type_id, l_from_layer_id, i_item_id, l_interorg_elem_exp_flag,
1757 l_txn_update_id,l_from_org, l_to_org,
1758 l_snd_qty,l_txfr_cost,l_trans_cost,l_conv_rate,l_um_rate,
1759 i_user_id,i_login_id,i_req_id,i_prg_appl_id,i_prg_id,
1760 l_err_num,l_err_code,l_err_msg);
1761 if (l_err_num <> 0) then
1762 raise process_error;
1763 end if;
1764 else
1765 insert into mtl_cst_txn_cost_details (
1766 transaction_id,
1767 organization_id,
1768 cost_element_id,
1769 level_type,
1770 last_update_date,
1771 last_updated_by,
1772 creation_date,
1773 created_by,
1774 last_update_login,
1775 request_id,
1776 program_application_id,
1777 program_id,
1778 program_update_date,
1779 inventory_item_id,
1780 transaction_cost,
1781 new_average_cost,
1782 percentage_change,
1783 value_change)
1784 values (l_txn_update_id,
1785 l_to_org,
1786 1,
1787 1,
1788 sysdate,
1789 i_user_id,
1790 sysdate,
1791 i_user_id,
1792 i_login_id,
1793 i_req_id,
1794 i_prg_appl_id,
1795 i_prg_id,
1796 sysdate,
1797 i_item_id,
1798 l_rcv_txn_cost,
1799 0,
1800 0,
1801 0);
1802 END IF;
1803
1804 /* If sending org is a standard costign org, then create layers in the receiving org, only
1805 after MCTCD is populated */
1806 if ((l_to_method IN (5,6)) and (l_from_method = 1)) then
1807 /* Bug #2352604, 2362306.
1808 Call create_layers when :
1809 No Direct Interorg transfers! This is called for receiving transaction.
1810 Intransit shipment, FOB shipment from std to LIFO
1811 Intransit receipt, FOB receipt from std to LIFO
1812 */
1813
1814 if(o_interorg_rec=1 and i_txn_action_id <> 3) then
1815 FND_FILE.PUT_LINE(FND_FILE.LOG,'Creating layers when sending org is std costing org');
1816
1817 -- Fix for bug 1872444
1818 -- Populate l_to_layer before calling CSTPLENG.create_layers
1819 -- for direct interorg transactions
1820 -- Also, the second argument was changed from i_txn_id to
1821 -- l_txn_update_id
1822
1823 if (i_txn_action_id = 3) then
1824 select nvl(layer_id,0) into l_to_layer from cst_quantity_layers
1825 where organization_id = l_to_org and inventory_item_id = i_item_id
1826 and cost_group_id = l_to_cost_grp;
1827 end if;
1828
1829 if(l_debug = 'Y') then
1830 fnd_file.put_line(fnd_file.log, 'Calling createlayers for the std org' || i_org_id || ': interorg rec : '|| o_interorg_rec || ':txn_update_id :'|| l_txn_update_id );
1831 end if;
1832
1833
1834 CSTPLENG.create_layers(
1835 i_org_id,
1836 l_txn_update_id,
1837 l_to_layer,
1838 i_item_id,
1839 abs(o_txn_qty),
1840 i_cost_method,
1841 i_txn_src_type,
1842 i_txn_action_id,
1843 0,
1844 o_interorg_rec, --2280515 (anjgupta)
1845 i_cost_type,
1846 i_mat_ct_id,
1847 i_avg_rates_id,
1848 o_exp_flag,
1849 i_user_id,
1850 i_login_id,
1851 i_req_id,
1852 i_prg_appl_id,
1853 i_prg_id,
1854 l_err_num,
1855 l_err_code,
1856 l_err_msg);
1857 end if; -- Bug #2352604
1858 end if;
1859
1860 -- Update the transaction cost column if appropriate.
1861 /* Begin changes and additions for bug 2827548 */
1862 Update mtl_material_transactions
1863 Set transaction_cost = l_new_txn_cost
1864 where transaction_id = i_txn_id;
1865
1866 if (i_txn_action_id = 3) then
1867 Update mtl_material_transactions
1868 Set transaction_cost = l_rcv_txn_cost
1869 where transaction_id = l_txn_update_id;
1870 end if;
1871
1872 -- Update the transaction_cost column for receipt txn w/ fob shipment
1873 -- in the receiving org's currency
1874 if (i_txn_action_id = 21 and i_fob_point = 1) then
1875 update mtl_material_transactions mmt
1876 set mmt.transaction_cost = l_rcv_txn_cost
1877 where mmt.transfer_transaction_id = i_txn_id
1878 and mmt.transaction_action_id = 12;
1879 -- Update the transaction_cost column for shipment txn w/ fob receipt
1880 -- in the sending org's currency
1881 elsif (i_txn_action_id = 12 and i_fob_point = 2) then
1882 update mtl_material_transactions mmt
1883 set mmt.transaction_cost = l_snd_txn_cost
1884 where mmt.transaction_id =
1885 (select mmt1.transfer_transaction_id
1886 from mtl_material_transactions mmt1
1887 where mmt1.transaction_id = i_txn_id)
1888 and mmt.transaction_action_id = 21
1889 and nvl(mmt.transaction_cost,0) = 0;
1890 end if;
1891
1892 /* End changes for bug 2827548 */
1893
1894 if (l_update_std = 1) then
1895 /* the receiving org is standard exp. */
1896 l_stmt_num := 210;
1897 -- if the receiving org is std exp, copy the txn info
1898 -- into MCACD from MCTCD.
1899
1900 insert into mtl_cst_actual_cost_details (
1901 transaction_id,
1902 organization_id,
1903 layer_id,
1904 cost_element_id,
1905 level_type,
1906 transaction_action_id,
1907 last_update_date,
1908 last_updated_by,
1909 creation_date,
1910 created_by,
1911 last_update_login,
1912 request_id,
1913 program_application_id,
1914 program_id,
1915 program_update_date,
1916 inventory_item_id,
1917 actual_cost,
1918 prior_cost,
1919 new_cost,
1920 insertion_flag,
1921 variance_amount,
1922 user_entered)
1923 select
1924 i_txn_id,
1925 l_std_org,
1926 -1,
1927 decode(l_elemental_visible,'Y',ctcd.cost_element_id,1),
1928 decode(l_elemental_visible,'Y',ctcd.level_type,1),
1929 i_txn_action_id,
1930 sysdate,
1931 i_user_id,
1932 sysdate,
1933 i_user_id,
1934 i_login_id,
1935 i_req_id,
1936 i_prg_appl_id,
1937 i_prg_id,
1938 sysdate,
1939 ctcd.inventory_item_id,
1940 decode(l_elemental_visible,'Y',ctcd.transaction_cost,l_rcv_txn_cost),
1941 NULL,
1942 NULL,
1943 'N',
1944 0,
1945 'N'
1946 FROM mtl_cst_txn_cost_details ctcd
1947 WHERE ctcd.transaction_id = l_txn_update_id
1948 AND ctcd.organization_id = l_std_org
1949 /* AND ctcd.transaction_cost >= 0 */; -- modified for bug #3835412
1950
1951 if (l_std_org = i_txn_org_id or i_txn_action_id = 3) then
1952 l_stmt_num := 220;
1953
1954 update mtl_material_transactions mmt
1955 set (last_update_date,
1956 last_updated_by,
1957 last_update_login,
1958 request_id,
1959 program_application_id,
1960 program_id,
1961 program_update_date,
1962 actual_cost) =
1963 (select sysdate,
1964 i_user_id,
1965 i_login_id,
1966 i_req_id,
1967 i_prg_appl_id,
1968 i_prg_id,
1969 sysdate,
1970 nvl(sum(actual_cost),0)
1971 from mtl_cst_actual_cost_details cacd
1972 where cacd.transaction_id = l_txn_update_id
1973 and cacd.organization_id = l_std_org
1974 and cacd.layer_id = -1)
1975 where mmt.transaction_id = l_txn_update_id;
1976 end if;
1977 /* begin: fix for bug 3679625 for DIRECT TRANSFERS - on the sending transaction
1978 * of a direct transfer where receiving org is standard costing org and item/sub
1979 * is expense in receiving org, we need to insert into MCACD from MCTCD and update MMT.
1980 */
1981 elsif (i_txn_action_id = 3 and l_std_org = i_txfr_org_id) then
1982
1983 l_stmt_num := 230;
1984 select decode(inventory_asset_flag, 'Y', 0, 1)
1985 into l_txfr_std_exp
1986 from mtl_system_items
1987 where inventory_item_id = i_item_id
1988 and organization_id = l_std_org;
1989
1990 l_stmt_num := 240;
1991 select transfer_transaction_id
1992 into l_txfr_txn_id
1993 from mtl_material_transactions mmt
1994 where mmt.transaction_id = i_txn_id;
1995
1996 l_stmt_num := 250;
1997 select decode(l_txfr_std_exp,1,1,decode(asset_inventory,1,0,1))
1998 into l_txfr_std_exp
1999 from mtl_secondary_inventories msi
2000 ,mtl_material_transactions mmt
2001 where mmt.transaction_id = l_txfr_txn_id
2002 and mmt.organization_id = l_std_org
2003 and msi.organization_id = l_std_org
2004 and msi.secondary_inventory_name = mmt.subinventory_code;
2005
2006 if (l_txfr_std_exp = 1) then
2007 l_stmt_num := 260;
2008 FND_FILE.PUT_LINE(FND_FILE.LOG, to_char(l_stmt_num) || ' insert into MCACD from MCTCD with l_txfr_std_exp = 1');
2009 insert into mtl_cst_actual_cost_details (
2010 transaction_id,
2011 organization_id,
2012 layer_id,
2013 cost_element_id,
2014 level_type,
2015 transaction_action_id,
2016 last_update_date,
2017 last_updated_by,
2018 creation_date,
2019 created_by,
2020 last_update_login,
2021 request_id,
2022 program_application_id,
2023 program_id,
2024 program_update_date,
2025 inventory_item_id,
2026 actual_cost,
2027 prior_cost,
2028 new_cost,
2029 insertion_flag,
2030 variance_amount,
2031 user_entered)
2032 select
2033 l_txfr_txn_id,
2034 l_std_org,
2035 -1,
2036 decode(l_elemental_visible,'Y',ctcd.cost_element_id,1),
2037 decode(l_elemental_visible,'Y',ctcd.level_type,1),
2038 i_txn_action_id,
2039 sysdate,
2040 i_user_id,
2041 sysdate,
2042 i_user_id,
2043 i_login_id,
2044 i_req_id,
2045 i_prg_appl_id,
2046 i_prg_id,
2047 sysdate,
2048 ctcd.inventory_item_id,
2049 decode(l_elemental_visible,'Y',ctcd.transaction_cost,l_rcv_txn_cost),
2050 NULL,
2051 NULL,
2052 'N',
2053 0,
2054 'N'
2055 FROM mtl_cst_txn_cost_details ctcd
2056 WHERE ctcd.transaction_id = l_txn_update_id
2057 AND ctcd.organization_id = l_std_org
2058 /* AND ctcd.transaction_cost >= 0 */; -- modified for bug #3835412
2059
2060 FND_FILE.PUT_LINE(FND_FILE.LOG, to_char(l_stmt_num) || ' update MMT from MCACD with l_txfr_std_exp = 1');
2061 update mtl_material_transactions mmt
2062 set (last_update_date,
2063 last_updated_by,
2064 last_update_login,
2065 request_id,
2066 program_application_id,
2067 program_id,
2068 program_update_date,
2069 actual_cost) =
2070 (select sysdate,
2071 i_user_id,
2072 i_login_id,
2073 i_req_id,
2074 i_prg_appl_id,
2075 i_prg_id,
2076 sysdate,
2077 nvl(sum(actual_cost),0)
2078 from mtl_cst_actual_cost_details cacd
2079 where cacd.transaction_id = l_txn_update_id
2080 and cacd.organization_id = l_std_org
2081 and cacd.layer_id = -1)
2082 where mmt.transaction_id = l_txn_update_id;
2083 end if;
2084 /* end bug 3679625 */
2085 end if;
2086 end if;
2087
2088 /* begin bug 3761538
2089 * for intransit interorg transfers where receiving org is standard costing org
2090 * and item is expense in receiving org, insert into MCACD from MCTCD on both the
2091 * sending and receiving transactions and update MMT on the receiving transaction.
2092 */
2093 if (l_to_std_exp = 1) then
2094
2095 if (i_txn_org_id = l_std_org) then
2096 select transfer_transaction_id
2097 into l_txfr_txn_id
2098 from mtl_material_transactions mmt
2099 where mmt.transaction_id = i_txn_id;
2100 end if;
2101
2102 l_stmt_num := 270;
2103 FND_FILE.PUT_LINE(FND_FILE.LOG, to_char(l_stmt_num) || ' insert into MCACD from MCTCD with l_to_std_exp = 1');
2104 insert into mtl_cst_actual_cost_details (
2105 transaction_id,
2106 organization_id,
2107 layer_id,
2108 cost_element_id,
2109 level_type,
2110 transaction_action_id,
2111 last_update_date,
2112 last_updated_by,
2113 creation_date,
2114 created_by,
2115 last_update_login,
2116 request_id,
2117 program_application_id,
2118 program_id,
2119 program_update_date,
2120 inventory_item_id,
2121 actual_cost,
2122 prior_cost,
2123 new_cost,
2124 insertion_flag,
2125 variance_amount,
2126 user_entered)
2127 select
2128 i_txn_id,
2129 l_std_org,
2130 -1,
2131 ctcd.cost_element_id,
2132 ctcd.level_type,
2133 i_txn_action_id,
2134 sysdate,
2135 i_user_id,
2136 sysdate,
2137 i_user_id,
2138 i_login_id,
2139 i_req_id,
2140 i_prg_appl_id,
2141 i_prg_id,
2142 sysdate,
2143 ctcd.inventory_item_id,
2144 ctcd.transaction_cost,
2145 NULL,
2146 NULL,
2147 'N',
2148 0,
2149 'N'
2150 FROM mtl_cst_txn_cost_details ctcd
2151 WHERE ctcd.transaction_id = decode(i_txn_org_id, l_std_org, l_txfr_txn_id, l_txn_update_id) -- sending txn id
2152 AND ctcd.organization_id = l_std_org
2153 /* AND ctcd.transaction_cost >= 0 */; -- modified for bug #3835412
2154
2155 -- update mmt if this is the receiving transaction id
2156 if (i_txn_org_id = l_std_org) then
2157 FND_FILE.PUT_LINE(FND_FILE.LOG, to_char(l_stmt_num) || ' update MMT from MCACD with l_to_std_exp = 1');
2158 update mtl_material_transactions mmt
2159 set (last_update_date,
2160 last_updated_by,
2161 last_update_login,
2162 request_id,
2163 program_application_id,
2164 program_id,
2165 program_update_date,
2166 actual_cost) =
2167 (select sysdate,
2168 i_user_id,
2169 i_login_id,
2170 i_req_id,
2171 i_prg_appl_id,
2172 i_prg_id,
2173 sysdate,
2174 nvl(sum(actual_cost),0)
2175 from mtl_cst_actual_cost_details cacd
2176 where cacd.transaction_id = l_txn_update_id
2177 and cacd.organization_id = l_std_org
2178 and cacd.layer_id = -1)
2179 where mmt.transaction_id = l_txn_update_id;
2180 end if;
2181 end if;
2182 /* end bug 3761538 */
2183
2184 EXCEPTION
2185 when process_error then
2186 o_err_num := l_err_num;
2187 o_err_code := l_err_code;
2188 o_err_msg := l_err_msg;
2189 when moh_rules_error then
2190 o_err_num := 9999;
2191 o_err_code := 'CST_RULES_ERROR';
2192 FND_MESSAGE.set_name('BOM', 'CST_RULES_ERROR');
2193 o_err_msg := FND_MESSAGE.Get;
2194 when others then
2195 rollback;
2196 o_err_num := SQLCODE;
2197 o_err_msg := 'CSTPLVCP.interorg (' || to_char(l_stmt_num) ||
2198 '): '
2199 || substr(SQLERRM, 1,200);
2200
2201 END interorg;
2202
2203 PROCEDURE get_snd_rcv_rate(
2204 I_TXN_ID IN NUMBER,
2205 I_FROM_ORG IN NUMBER,
2206 I_TO_ORG IN NUMBER,
2207 O_SND_SOB_ID OUT NOCOPY NUMBER,
2208 O_SND_CURR OUT NOCOPY VARCHAR2,
2209 O_RCV_SOB_ID OUT NOCOPY NUMBER,
2210 O_RCV_CURR OUT NOCOPY VARCHAR2,
2211 O_CURR_TYPE OUT NOCOPY VARCHAR2,
2212 O_CONV_RATE OUT NOCOPY NUMBER,
2213 O_CONV_DATE OUT NOCOPY DATE,
2214 O_Err_Num OUT NOCOPY NUMBER,
2215 O_Err_Code OUT NOCOPY VARCHAR2,
2216 O_Err_Msg OUT NOCOPY VARCHAR2
2217 )IS
2218 l_snd_sob_id NUMBER;
2219 l_snd_curr VARCHAR2(10);
2220 l_rcv_sob_id NUMBER;
2221 l_rcv_curr VARCHAR2(10);
2222 l_curr_type VARCHAR2(30);
2223 l_conv_rate NUMBER;
2224 l_conv_date DATE;
2225 l_txn_date DATE;
2226 l_err_num NUMBER;
2227 l_err_code VARCHAR2(240);
2228 l_err_msg VARCHAR2(240);
2229 l_stmt_num NUMBER;
2230
2231 BEGIN
2232 -- initialize local variables
2233 l_err_num := 0;
2234 l_err_code := '';
2235 l_err_msg := '';
2236
2237 l_stmt_num := 10;
2238
2239 select ledger_id
2240 into l_snd_sob_id
2241 /*from org_organization_definitions */
2242 from cst_acct_info_v
2243 where organization_id = i_from_org;
2244
2245 l_stmt_num := 20;
2246
2247 select currency_code
2248 into l_snd_curr
2249 from gl_sets_of_books
2250 where set_of_books_id = l_snd_sob_id;
2251
2252 l_stmt_num := 30;
2253
2254 select ledger_id
2255 into l_rcv_sob_id
2256 /*from org_organization_definitions*/
2257 from cst_acct_info_v
2258 where organization_id = i_to_org;
2259
2260 l_stmt_num := 40;
2261
2262 select currency_code
2263 into l_rcv_curr
2264 from gl_sets_of_books
2265 where set_of_books_id = l_rcv_sob_id;
2266
2267 l_stmt_num := 50;
2268
2269 select currency_conversion_type, TRUNC(transaction_date)
2270 into l_curr_type, l_txn_date
2271 from mtl_material_transactions
2272 where transaction_id = i_txn_id;
2273
2274 if (l_curr_type is NULL) then
2275 FND_PROFILE.get('CURRENCY_CONVERSION_TYPE', l_curr_type);
2276 end if;
2277
2278 if (l_rcv_curr <> l_snd_curr) then
2279 l_stmt_num := 60;
2280
2281 /* --- replacing gl table hit by gl currency api
2282
2283 select conversion_rate, conversion_date
2284 into l_conv_rate, l_conv_date
2285 from gl_daily_conversion_rates
2286 where set_of_books_id = l_rcv_sob_id
2287 and from_currency_code = l_snd_curr
2288 and conversion_type = l_curr_type
2289 and conversion_date =
2290 (select max(conversion_date)
2291 from gl_daily_conversion_rates
2292 where set_of_books_id = l_rcv_sob_id
2293 and from_currency_code = l_snd_curr
2294 and conversion_type = l_curr_type
2295 and conversion_date <= l_txn_date);
2296 -------------------------------------------------------------*/
2297
2298 l_conv_rate := gl_currency_api.get_rate(l_rcv_sob_id,l_snd_curr,l_txn_date,
2299 l_curr_type);
2300 else
2301 l_conv_rate := 1;
2302 end if;
2303
2304 o_snd_sob_id := l_snd_sob_id;
2305 o_snd_curr := l_snd_curr;
2306 o_rcv_sob_id := l_rcv_sob_id;
2307 o_rcv_curr := l_rcv_curr;
2308 o_curr_type := l_curr_type;
2309 o_conv_rate := l_conv_rate;
2310 o_conv_date := l_conv_date;
2311
2312 EXCEPTION
2313
2314 when gl_currency_api.NO_RATE then
2315 rollback;
2316 O_err_num := 9999;
2317 O_err_code := 'CST_NO_GL_RATE';
2318 FND_MESSAGE.set_name('BOM', 'CST_NO_GL_RATE');
2319 O_err_msg := FND_MESSAGE.Get;
2320
2321 when others then
2322 rollback;
2323 o_err_num := SQLCODE;
2324 o_err_msg := 'CSTPLVCP.get_snd_rcv_rate (' || to_char(l_stmt_num) ||
2325 '): '
2326 || substr(SQLERRM, 1,200);
2327
2328 END get_snd_rcv_rate;
2329
2330 PROCEDURE get_snd_rcv_uom(
2331 I_ITEM_ID IN NUMBER,
2332 I_FROM_ORG IN NUMBER,
2333 I_TO_ORG IN NUMBER,
2334 O_SND_UOM OUT NOCOPY VARCHAR2,
2335 O_RCV_UOM OUT NOCOPY VARCHAR2,
2336 O_Err_Num OUT NOCOPY NUMBER,
2337 O_Err_Code OUT NOCOPY VARCHAR2,
2338 O_Err_Msg OUT NOCOPY VARCHAR2
2339 )IS
2340 l_err_num NUMBER;
2341 l_err_code VARCHAR2(240);
2342 l_err_msg VARCHAR2(240);
2343 l_stmt_num NUMBER;
2344
2345 BEGIN
2346 -- initialize local variables
2347 l_err_num := 0;
2348 l_err_code := '';
2349 l_err_msg := '';
2350
2351 l_stmt_num := 10;
2352
2353 select primary_uom_code
2354 into o_snd_uom
2355 from mtl_system_items
2356 where organization_id = i_from_org
2357 and inventory_item_id = i_item_id;
2358
2359 l_stmt_num := 20;
2360
2361 select primary_uom_code
2362 into o_rcv_uom
2363 from mtl_system_items
2364 where organization_id = i_to_org
2365 and inventory_item_id = i_item_id;
2366
2367 EXCEPTION
2368 when others then
2369 rollback;
2370 o_err_num := SQLCODE;
2371 o_err_msg := 'CSTPLVCP.get_snd_rcv_uom (' || to_char(l_stmt_num) ||
2372 '): '
2373 || substr(SQLERRM, 1,200);
2374
2375 END get_snd_rcv_uom;
2376
2377 FUNCTION standard_cost_org(
2378 I_ORG_ID IN NUMBER
2379 ) RETURN INTEGER IS
2380 l_ret_val NUMBER;
2381 BEGIN
2382 select decode(primary_cost_method,1,1,0)
2383 into l_ret_val
2384 from mtl_parameters
2385 where organization_id = i_org_id;
2386
2387 return l_ret_val;
2388 END standard_cost_org;
2389
2390 PROCEDURE interorg_elemental_detail(
2391 i_org_id IN NUMBER,
2392 i_txn_id IN NUMBER,
2393 i_compute_txn_cost IN NUMBER,
2394 i_cost_type_id IN NUMBER,
2395 i_from_layer_id IN NUMBER,
2396 i_item_id IN NUMBER,
2397 i_exp_flag IN NUMBER,
2398 i_txn_update_id IN NUMBER,
2399 i_from_org IN NUMBER,
2400 i_to_org IN NUMBER,
2401 i_snd_qty IN NUMBER,
2402 i_txfr_cost IN NUMBER,
2403 i_trans_cost IN NUMBER,
2404 i_conv_rate IN NUMBER,
2405 i_um_rate IN NUMBER,
2406 i_user_id IN NUMBER,
2407 i_login_id IN NUMBER,
2408 i_req_id IN NUMBER,
2409 i_prg_appl_id IN NUMBER,
2410 i_prg_id IN NUMBER,
2411 o_err_num OUT NOCOPY NUMBER,
2412 o_err_code OUT NOCOPY VARCHAR2,
2413 o_err_msg OUT NOCOPY VARCHAR2)
2414 IS
2415 l_err_num NUMBER;
2416 l_err_code VARCHAR2(240);
2417 l_err_msg VARCHAR2(240);
2418 l_stmt_num NUMBER;
2419 process_error EXCEPTION;
2420
2421 l_movh_cnt NUMBER;
2422 l_rcv_movh NUMBER;
2423 l_rcv_qty NUMBER;
2424 l_trp_trf NUMBER;
2425 l_from_cost_org NUMBER; /* bugfix 3048258 */
2426 BEGIN
2427
2428 -- Insert detail elemental cost into mctcd.
2429 -- Based on the from_org :
2430 -- * If from_org is a standard org (l_compute_txn_cost=1),
2431 -- insert detail cost from cicd
2432 -- * If from_org is an avg org (l_compute_txn_cost=2),
2433 -- insert detail cost from clcd
2434 -- Need to convert the cost into the receiving org cost in receiving org
2435 -- currenct and UOM.
2436 -- Suppose that : * This level material in from org is 12 USD with UOM of DZ.
2437 -- * Receiving org is in SGD and UOM of EA
2438 -- * i_conv_rate = 2 (from USD to SGD),
2439 -- * i_um_rate = 12 (from DZ to EA).
2440 -- * In the mctcd of receiving org, we insert :
2441 -- this level material cost as : 12 * 2 / 12 = 2 SGD/EA
2442 if (i_compute_txn_cost = 1) then
2443 l_stmt_num := 10;
2444
2445 /* Added for bugfix 3048258 */
2446 select cost_organization_id
2447 into l_from_cost_org
2448 from mtl_parameters
2449 where organization_id = i_from_org;
2450
2451 insert into mtl_cst_txn_cost_details (
2452 transaction_id,
2453 organization_id,
2454 cost_element_id,
2455 level_type,
2456 last_update_date,
2457 last_updated_by,
2458 creation_date,
2459 created_by,
2460 last_update_login,
2461 request_id,
2462 program_application_id,
2463 program_id,
2464 program_update_date,
2465 inventory_item_id,
2466 transaction_cost,
2467 new_average_cost,
2468 percentage_change,
2469 value_change)
2470 select
2471 i_txn_update_id,
2472 i_to_org,
2473 cost_element_id,
2474 level_type,
2475 sysdate,
2476 i_user_id,
2477 sysdate,
2478 i_user_id,
2479 i_login_id,
2480 i_req_id,
2481 i_prg_appl_id,
2482 i_prg_id,
2483 sysdate,
2484 i_item_id,
2485 sum(item_cost)*i_conv_rate/i_um_rate,
2486 0,
2487 0,
2488 0
2489 from cst_item_cost_details cicd
2490 where cicd.cost_type_id = i_cost_type_id
2491 and cicd.inventory_item_id = i_item_id
2492 /* and cicd.organization_id = i_from_org : bugfix 3048258 */
2493 and cicd.organization_id = l_from_cost_org
2494 group by cicd.cost_element_id,cicd.level_type;
2495
2496 elsif (i_exp_flag = 1) then
2497 l_stmt_num := 15;
2498
2499 insert into mtl_cst_txn_cost_details (
2500 transaction_id,
2501 organization_id,
2502 cost_element_id,
2503 level_type,
2504 last_update_date,
2505 last_updated_by,
2506 creation_date,
2507 created_by,
2508 last_update_login,
2509 request_id,
2510 program_application_id,
2511 program_id,
2512 program_update_date,
2513 inventory_item_id,
2514 transaction_cost,
2515 new_average_cost,
2516 percentage_change,
2517 value_change)
2518 select
2519 i_txn_update_id,
2520 i_to_org,
2521 cost_element_id,
2522 level_type,
2523 sysdate,
2524 i_user_id,
2525 sysdate,
2526 i_user_id,
2527 i_login_id,
2528 i_req_id,
2529 i_prg_appl_id,
2530 i_prg_id,
2531 sysdate,
2532 i_item_id,
2533 clcd.item_cost*i_conv_rate/i_um_rate,
2534 0,
2535 0,
2536 0
2537 from cst_layer_cost_details clcd
2538 where clcd.layer_id = i_from_layer_id;
2539
2540 else
2541
2542 l_stmt_num := 20;
2543 insert into mtl_cst_txn_cost_details (
2544 transaction_id,
2545 organization_id,
2546 cost_element_id,
2547 level_type,
2548 last_update_date,
2549 last_updated_by,
2550 creation_date,
2551 created_by,
2552 last_update_login,
2553 request_id,
2554 program_application_id,
2555 program_id,
2556 program_update_date,
2557 inventory_item_id,
2558 transaction_cost,
2559 new_average_cost,
2560 percentage_change,
2561 value_change)
2562 select
2563 i_txn_update_id,
2564 i_to_org,
2565 cost_element_id,
2566 level_type,
2567 sysdate,
2568 i_user_id,
2569 sysdate,
2570 i_user_id,
2571 i_login_id,
2572 i_req_id,
2573 i_prg_appl_id,
2574 i_prg_id,
2575 sysdate,
2576 i_item_id,
2577 NVL((sum(mclacd.actual_cost * abs(mclacd.layer_quantity)) / abs(i_snd_qty)),0)*i_conv_rate/i_um_rate, -- modified for bug #3835412
2578 0,
2579 0,
2580 0
2581 from mtl_cst_layer_act_cost_details mclacd
2582 where organization_id = i_org_id
2583 and transaction_id = i_txn_id
2584 group by cost_element_id,level_type;
2585
2586 end if;
2587
2588 l_stmt_num := 30;
2589 -- Find out if there is already exist this level material overhead in mctcd.
2590 select count(*)
2591 into l_movh_cnt
2592 from mtl_cst_txn_cost_details mctcd
2593 where mctcd.transaction_id = i_txn_update_id
2594 and mctcd.organization_id = i_to_org
2595 and mctcd.inventory_item_id = i_item_id
2596 and mctcd.level_type = 1
2597 and mctcd.cost_element_id = 2;
2598
2599 if (l_movh_cnt > 0) then
2600 l_stmt_num := 40;
2601 select NVL(mctcd.transaction_cost,0)
2602 into l_rcv_movh
2603 from mtl_cst_txn_cost_details mctcd
2604 where mctcd.transaction_id = i_txn_update_id
2605 and mctcd.organization_id = i_to_org
2606 and mctcd.inventory_item_id = i_item_id
2607 and mctcd.level_type = 1
2608 and mctcd.cost_element_id = 2;
2609 else
2610 l_rcv_movh := 0;
2611 end if;
2612
2613 -- Convert the i_snd_qty in the receiving org UOM
2614 l_rcv_qty := abs(i_snd_qty) * i_um_rate;
2615
2616 -- The transportation and transfer cost is a total cost in sending currency.
2617 -- Thus we need to convert it to recv currency.
2618 /* change for bug 2827548 - moved currency conversion outside of this function */
2619 l_trp_trf := (i_txfr_cost+i_trans_cost);
2620
2621 -- Add in the trp and trf cost as the this level material overhead
2622 l_rcv_movh := (l_rcv_movh*l_rcv_qty + l_trp_trf)/l_rcv_qty;
2623
2624 -- The new material overhead (l_rcv_movh) includes :
2625 -- * This level material overhead of the sending org item cost,
2626 -- * Transportation and transfer cost
2627 -- The new material overhead cost has been converted into recv org currency
2628 -- and UOM.
2629 -- If there already exist movh in mctcd, then update mctcd with new movhd
2630 -- value. Otherwise insert the new movhd into mctcd
2631 if (l_movh_cnt > 0) then
2632 l_stmt_num := 50;
2633 update mtl_cst_txn_cost_details mctcd
2634 set mctcd.transaction_cost = l_rcv_movh
2635 where mctcd.transaction_id = i_txn_update_id
2636 and mctcd.organization_id = i_to_org
2637 and mctcd.inventory_item_id = i_item_id
2638 and mctcd.level_type = 1
2639 and mctcd.cost_element_id = 2;
2640 elsif (l_rcv_movh > 0) then
2641 l_stmt_num := 60;
2642 insert into mtl_cst_txn_cost_details (
2643 transaction_id,
2644 organization_id,
2645 cost_element_id,
2646 level_type,
2647 last_update_date,
2648 last_updated_by,
2649 creation_date,
2650 created_by,
2651 last_update_login,
2652 request_id,
2653 program_application_id,
2654 program_id,
2655 program_update_date,
2656 inventory_item_id,
2657 transaction_cost,
2658 new_average_cost,
2659 percentage_change,
2660 value_change)
2661 values (
2662 i_txn_update_id,
2663 i_to_org,
2664 2,
2665 1,
2666 sysdate,
2667 i_user_id,
2668 sysdate,
2669 i_user_id,
2670 i_login_id,
2671 i_req_id,
2672 i_prg_appl_id,
2673 i_prg_id,
2674 sysdate,
2675 i_item_id,
2676 l_rcv_movh,
2677 0,
2678 0,
2679 0);
2680 end if;
2681
2682 EXCEPTION
2683 when process_error then
2684 o_err_num := l_err_num;
2685 o_err_code := l_err_code;
2686 o_err_msg := l_err_msg;
2687 when others then
2688 rollback;
2689 o_err_num := SQLCODE;
2690 o_err_msg := 'CSTPLVCP.interorg_elemental_detail (' || to_char(l_stmt_num)
2691 || '): ' || substr(SQLERRM, 1,200);
2692
2693
2694 END interorg_elemental_detail;
2695
2696
2697 /*========================================================================
2698 -- PROCEDURE
2699 -- borrow_cost
2700 --
2701 -- DESCRIPTION
2702 -- This procedure is duplicated from CSTPAVCP.borrow_cost procedure and
2703 -- and revised for FIFO/LIFO costing
2704 -- This procedure will identify the cost of all borrow transactions
2705 -- related to the specified payback transactions, compute the average cost
2706 -- and store it in MCTCD.
2707 -- If layer actual cost hook is used, it will error out
2708 -- since user-entered actual cost is not allowed for payback transaction.
2709
2710 -- HISTORY
2711 -- 04/26/00 Dieu-Thuong Le Creation
2712
2713 =========================================================================*/
2714
2715 PROCEDURE borrow_cost(
2716 I_ORG_ID IN NUMBER,
2717 I_TXN_ID IN NUMBER,
2718 I_USER_ID IN NUMBER,
2719 I_LOGIN_ID IN NUMBER,
2720 I_REQ_ID IN NUMBER,
2721 I_PRG_APPL_ID IN NUMBER,
2722 I_PRG_ID IN NUMBER,
2723 I_ITEM_ID IN NUMBER,
2724 I_HOOK IN NUMBER,
2725 I_TO_LAYER IN NUMBER,
2726 O_Err_Num OUT NOCOPY NUMBER,
2727 O_Err_Code OUT NOCOPY VARCHAR2,
2728 O_Err_Msg OUT NOCOPY VARCHAR2
2729 )IS
2730
2731 -- this cursor is to find out all the related
2732 -- borrow transactions for a specific payback
2733 -- transaction
2734 cursor c_payback_txn(c_cur_txn_id number) is
2735 select pbp.borrow_transaction_id,
2736 pbp.payback_quantity
2737 from pjm_borrow_paybacks pbp
2738 where pbp.payback_transaction_id = c_cur_txn_id;
2739
2740 -- this cursor is to find out all the mcacd data
2741 -- for a specific transaction_id
2742 cursor c_mcacd_data (c_transaction_id number)is
2743 select mcacd.transaction_id,
2744 mcacd.cost_element_id,
2745 mcacd.level_type,
2746 mcacd.inventory_item_id,
2747 mcacd.actual_cost,
2748 mcacd.prior_cost,
2749 mcacd.new_cost,
2750 mcacd.layer_id
2751 from mtl_cst_actual_cost_details mcacd
2752 where mcacd.transaction_id = c_transaction_id
2753 and mcacd.layer_id = i_to_layer;
2754
2755 type t_cst_element is table of number
2756 index by binary_integer;
2757
2758 l_temp_element_cost t_cst_element;
2759 l_level_type NUMBER;
2760 l_total_bp_qty NUMBER;
2761 l_err_num NUMBER;
2762 l_err_code VARCHAR2(240);
2763 l_err_msg VARCHAR2(240);
2764 l_stmt_num NUMBER;
2765 cst_no_hook_allowed EXCEPTION;
2766
2767 BEGIN
2768 -- initialize local variables
2769 l_err_num := 0;
2770 l_err_code := '';
2771 l_err_msg := '';
2772
2773 l_stmt_num := 10;
2774
2775 -- initialize array with 0
2776
2777 for l_index_counter in 1..10 loop
2778 l_temp_element_cost(l_index_counter):=0;
2779 end loop;
2780
2781 -- check for actual cost hook used.
2782 l_stmt_num := 15;
2783
2784 if i_hook = 1 then
2785 raise cst_no_hook_allowed;
2786 end if;
2787
2788 -- loop through all the payaback txn to find the borrowing cost
2789 -- from MCACD and sum it up.
2790 l_stmt_num := 20;
2791
2792 l_total_bp_qty := 0;
2793
2794 for c_payback_rec in c_payback_txn(i_txn_id) loop
2795 for c_mcacd_rec in c_mcacd_data(c_payback_rec.borrow_transaction_id) LOOP
2796 if c_mcacd_rec.level_type =1 then
2797 l_temp_element_cost(c_mcacd_rec.cost_element_id):=
2798 l_temp_element_cost(c_mcacd_rec.cost_element_id) +
2799 c_mcacd_rec.actual_cost * abs(c_payback_rec.payback_quantity);
2800 elsif c_mcacd_rec.level_type = 2 then
2801 l_temp_element_cost(c_mcacd_rec.cost_element_id + 5):=
2802 l_temp_element_cost(c_mcacd_rec.cost_element_id + 5) +
2803 c_mcacd_rec.actual_cost * abs(c_payback_rec.payback_quantity);
2804 end if;
2805 end loop; -- end looping c_mcacd_rec
2806 l_total_bp_qty := l_total_bp_qty + abs(c_payback_rec.payback_quantity);
2807 end loop; -- end looping c_payback_rec
2808
2809 -- do a division here to find out the average cost
2810 for l_index_counter in 1..10 loop
2811 l_temp_element_cost(l_index_counter):= l_temp_element_cost(l_index_counter)
2812 / l_total_bp_qty;
2813 end loop;
2814
2815 -- populate MCTCD from here
2816 for l_index_counter in 1..10 loop
2817 if l_index_counter < 6 then
2818 l_level_type := 1;
2819 else
2820 l_level_type := 2;
2821 end if;
2822
2823 -- populate mctcd
2824 if (i_hook = 0) then -- if no hook is used then populate mctcd
2825 if l_temp_element_cost(l_index_counter) <> 0 then
2826 l_stmt_num := 25;
2827 insert into mtl_cst_txn_cost_details(
2828 TRANSACTION_ID,
2829 ORGANIZATION_ID,
2830 COST_ELEMENT_ID,
2831 LEVEL_TYPE,
2832 LAST_UPDATE_DATE,
2833 LAST_UPDATED_BY,
2834 CREATION_DATE,
2835 CREATED_BY,
2836 LAST_UPDATE_LOGIN,
2837 REQUEST_ID,
2838 PROGRAM_APPLICATION_ID,
2839 PROGRAM_ID,
2840 PROGRAM_UPDATE_DATE,
2841 INVENTORY_ITEM_ID,
2842 TRANSACTION_COST)
2843 values(
2844 i_txn_id,
2845 i_org_id,
2846 decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5)),
2847 l_level_type,
2848 sysdate,
2849 i_user_id,
2850 sysdate,
2851 i_user_id,
2852 i_login_id,
2853 i_req_id,
2854 i_prg_appl_id,
2855 i_prg_id,
2856 sysdate,
2857 i_item_id,
2858 l_temp_element_cost(l_index_counter));
2859 end if; -- end for checking existence of elemental cost
2860 end if; -- end for checking for cost hook
2861 end loop; -- end for looping 10 elements
2862
2863 EXCEPTION
2864 when cst_no_hook_allowed then
2865 rollback;
2866 o_err_num := 24020;
2867 o_err_code := substr('CSTPLVCP.borrow_cost('
2868 ||to_char(l_stmt_num)
2869 || '): '
2870 || l_err_msg
2871 || '.',1,240);
2872 fnd_message.set_name('BOM', 'CST_NO_HOOK_ALLOWED');
2873 o_err_msg := fnd_message.get;
2874 o_err_msg := substr(o_err_msg,1,240);
2875 when others then
2876 rollback;
2877 o_err_num := SQLCODE;
2878 o_err_msg := 'CSTPLVCP.borrow_cost (' || to_char(l_stmt_num) ||
2879 '): '
2880 || substr(SQLERRM, 1,200);
2881
2882 END borrow_cost;
2883
2884 /*=========================================================================
2885 -- PROCEDURE
2886 -- sub_transfer
2887 --
2888 -- DESCRIPTION
2889 -- This procedure costs the subinventory transfer for both the transfer
2890 -- subinventory and the destination subinventory.
2891 --
2892 -- HISTORY
2893 -- 4/26/00 Dieu-Thuong Le Creation
2894 -- 9/05/00 Dieu-Thuong Le Bugfix 1393484: For payback trxn,
2895 -- MCLACD.actual_cost for return('from')
2896 -- sub should be the same as layer_cost.
2897
2898 ==========================================================================*/
2899
2900 procedure sub_transfer(
2901 I_ORG_ID IN NUMBER,
2902 I_TXN_ID IN NUMBER,
2903 I_LAYER_ID IN NUMBER,
2904 I_COST_TYPE IN NUMBER,
2905 I_ITEM_ID IN NUMBER,
2906 I_TXN_QTY IN NUMBER,
2907 I_TXN_ACTION_ID IN NUMBER,
2908 I_TXN_SRC_TYPE IN NUMBER,
2909 I_NEW_COST IN NUMBER,
2910 I_HOOK IN NUMBER,
2911 I_COST_METHOD IN NUMBER,
2912 I_TXFR_LAYER_ID IN NUMBER,
2913 I_CITW_FLAG IN NUMBER,
2914 I_FLOW_SCHEDULE IN NUMBER,
2915 I_MAT_CT_ID IN NUMBER,
2916 I_AVG_RATES_ID IN NUMBER,
2917 I_USER_ID IN NUMBER,
2918 I_LOGIN_ID IN NUMBER,
2919 I_REQ_ID IN NUMBER,
2920 I_PRG_APPL_ID IN NUMBER,
2921 I_PRG_ID IN NUMBER,
2922 O_Err_Num OUT NOCOPY NUMBER,
2923 O_Err_Code OUT NOCOPY VARCHAR2,
2924 O_Err_Msg OUT NOCOPY VARCHAR2
2925 ) IS
2926 l_layer_chg NUMBER;
2927 l_exp_item NUMBER;
2928 l_exp1 NUMBER;
2929 l_exp2 NUMBER;
2930 l_from_layer NUMBER;
2931 l_to_layer NUMBER;
2932 l_from_exp NUMBER;
2933 l_to_exp NUMBER;
2934 l_from_qty NUMBER;
2935 l_to_qty NUMBER;
2936 l_ret_val NUMBER;
2937 l_no_update_qty NUMBER;
2938 l_new_cost NUMBER;
2939 l_txf_txn_id NUMBER;
2940 l_txn_type_id NUMBER;
2941 l_cur_cost NUMBER;
2942 l_interorg_rec NUMBER;
2943 l_txn_cost_exist NUMBER;
2944 l_inv_layer_id NUMBER;
2945 l_err_num NUMBER;
2946 l_err_code VARCHAR2(240);
2947 l_err_msg VARCHAR2(240);
2948 l_debug VARCHAR2(80);
2949 l_stmt_num NUMBER;
2950 l_exp_flag NUMBER;
2951 l_src_id NUMBER;/* For bug 4523417*/
2952 l_src_number VARCHAR2(240);/*For Bug4523417*/
2953 process_error EXCEPTION;
2954 cst_no_hook_allowed EXCEPTION;
2955
2956 BEGIN
2957 -- initialize local variables
2958 l_err_num := 0;
2959 l_err_code := '';
2960 l_err_msg := '';
2961 l_new_cost := i_new_cost;
2962 l_interorg_rec := '';
2963 l_exp_flag := 0;
2964 l_txn_cost_exist := 0;
2965 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
2966
2967 /********************************************************************
2968 ** Figure out layer_change flag **
2969 ** A layer change occurs when we transfer material between **
2970 ** two different layers within the same org. **
2971 ********************************************************************/
2972 l_stmt_num := 10;
2973 select transaction_type_id
2974 into l_txn_type_id
2975 from mtl_material_transactions
2976 where transaction_id = i_txn_id;
2977
2978 if ((i_txfr_layer_id is not NULL) and (i_txfr_layer_id <> i_layer_id)) then
2979 l_layer_chg := 1;
2980 else
2981 l_layer_chg := 0;
2982 end if;
2983
2984 -- check if item is an expense item.
2985 l_stmt_num := 20;
2986
2987 select decode(inventory_asset_flag, 'Y',0,1)
2988 into l_exp_item
2989 from mtl_system_items
2990 where inventory_item_id = i_item_id
2991 and organization_id = i_org_id;
2992
2993 -- check if subinventory is an expense sub.
2994 l_stmt_num := 30;
2995
2996 select decode(asset_inventory,1,0,1)
2997 into l_exp1
2998 from mtl_secondary_inventories msi,
2999 mtl_material_transactions mmt
3000 where msi.secondary_inventory_name = mmt.subinventory_code
3001 and msi.organization_id = i_org_id
3002 and mmt.transaction_id = i_txn_id
3003 and mmt.organization_id = i_org_id;
3004
3005 l_stmt_num := 40;
3006
3007 -- the nature of project sub is the same as project job, except
3008 -- asset common to expense project job issue case.
3009 --
3010 -- Common Proj Sub Proj Job
3011 -- asset asset asset
3012 -- asset asset * exp * <= special case
3013 -- exp asset asset
3014 -- exp exp exp
3015
3016 -- we divide into three cases to figure out the type of txfr sub.
3017 -- 1. common issue to wip
3018 -- 2. normal subinventory txfr
3019 -- 3. others
3020
3021 l_stmt_num := 50;
3022
3023 if (i_citw_flag = 1) then
3024 if(i_flow_schedule = 1) then
3025 -- cfm then use wip_flow_schedules
3026 -- class_type 1 and 3 : asset job, 4 : exp job
3027 select decode(wac.class_type, 1, 0,
3028 3, 0,
3029 4, decode(l_exp1, 1, 1, 0))
3030 into l_exp2
3031 from mtl_material_transactions mmt,
3032 wip_flow_schedules wfs,
3033 wip_accounting_classes wac
3034 where mmt.transaction_id = i_txn_id
3035 and mmt.organization_id = i_org_id
3036 and wfs.organization_id = i_org_id
3037 and wfs.wip_entity_id = mmt.transaction_source_id
3038 and wac.organization_id = i_org_id
3039 and wac.class_code = wfs.class_code;
3040 else
3041 -- non cfm then use wip_discrete_jobs
3042 -- class_type 1 and 3 : asset job, 4 : exp job
3043 select decode(wac.class_type, 1, 0,
3044 3, 0,
3045 4, decode(l_exp1, 1, 1, 0))
3046 into l_exp2
3047 from mtl_material_transactions mmt,
3048 wip_discrete_jobs wdj,
3049 wip_accounting_classes wac
3050 where mmt.transaction_id = i_txn_id
3051 and mmt.organization_id = i_org_id
3052 and wdj.organization_id = i_org_id
3053 and wdj.wip_entity_id = mmt.transaction_source_id
3054 and wac.organization_id = i_org_id
3055 and wac.class_code = wdj.class_code;
3056 end if;
3057 /* Changes for VMI. Adding planning transfer transaction */
3058 elsif (i_txn_action_id IN (2,5,28)) then
3059 select decode(asset_inventory,1,0,1)
3060 into l_exp2
3061 from mtl_secondary_inventories msi,
3062 mtl_material_transactions mmt
3063 where msi.secondary_inventory_name = mmt.transfer_subinventory
3064 and msi.organization_id = i_org_id
3065 and mmt.transaction_id = i_txn_id
3066 and mmt.organization_id = i_org_id;
3067 elsif (i_txn_action_id = 55) then
3068 l_exp2 := l_exp1;
3069 else
3070 l_exp2 := 0;
3071 end if;
3072
3073 /* Changes for VMI. Adding planning transfer transaction */
3074 if (i_txn_action_id in (2,5,28,55,21)) then
3075 l_from_layer := i_layer_id;
3076 l_to_layer := i_txfr_layer_id;
3077 l_from_exp := l_exp1;
3078 l_to_exp := l_exp2;
3079 l_from_qty := i_txn_qty;
3080 l_to_qty := -1 * i_txn_qty;
3081 else
3082 l_from_layer := i_txfr_layer_id;
3083 l_to_layer := i_layer_id;
3084 l_from_exp := l_exp2;
3085 l_to_exp := l_exp1;
3086 l_from_qty :=-1 * i_txn_qty;
3087 l_to_qty := i_txn_qty;
3088 end if;
3089
3090 -- Just in case i_txfr_layer_id is NULL, always set from and to layer
3091 -- to layer_id for same layer transfers.
3092 if (l_layer_chg = 0) then
3093 l_from_layer := i_layer_id;
3094 l_to_layer := i_layer_id;
3095 end if;
3096
3097 -- Do not allow actual cost hook if it's a payback transaction or if
3098 -- it is an asset to asset transfer for the same layer.
3099 -- Error out.
3100
3101 l_stmt_num := 60;
3102 if (i_hook = 1) and
3103 ((l_layer_chg = 0 and l_from_exp = 0 and l_to_exp = 0)
3104 or l_txn_type_id = 68) then
3105 raise cst_no_hook_allowed;
3106 end if;
3107
3108 l_stmt_num := 65;
3109 -- Check for existing mctcd. For example, if it's a payback transaction
3110 -- mctcd will be created before sub_transfer is called.
3111
3112 select count(*)
3113 into l_txn_cost_exist
3114 from mtl_cst_txn_cost_details
3115 where transaction_id = i_txn_id
3116 and organization_id = i_org_id;
3117
3118 l_stmt_num := 70;
3119 -- get latest layer id regardless of the remaining layer quantity
3120 -- or if the cost method is FIFO or LIFO
3121
3122 select nvl(max(inv_layer_id), 0)
3123 into l_inv_layer_id
3124 from cst_inv_layers
3125 where layer_id = l_from_layer;
3126
3127 if l_debug = 'Y' then
3128 FND_FILE.PUT_LINE(FND_FILE.LOG,'Transaction: ' || to_char(i_txn_id)
3129 ||',layer change: '|| to_char(l_layer_chg));
3130 FND_FILE.PUT_LINE(FND_FILE.LOG,'From layer: '|| to_char(l_from_layer)
3131 || 'From qty: '|| to_char(l_from_qty));
3132 FND_FILE.PUT_LINE(FND_FILE.LOG, 'To layer: '|| to_char(l_to_layer)
3133 || 'To qty: '|| to_char(l_to_qty));
3134 end if;
3135
3136 /************************************************************
3137 ** Compute actual cost for the from subinventory **
3138 ************************************************************/
3139
3140 /* Set l_exp_flag for the from subinventory. Set flag to 1 if it's
3141 an expense item or if the 'from' sub is an expense sub. */
3142
3143 if l_exp_item = 1 or
3144 l_from_exp = 1 then
3145 l_exp_flag := 1;
3146 end if;
3147
3148 -- If from subinventory is an expense sub, cost at latest layer cost
3149 -- and do not consume inventory layer. Exception: Exp to asset will be
3150 -- handled when we process to subinventory.
3151 -- Asset to asset subtransfer in the same layer should be costed using
3152 -- the FIFO/LIFO consumption logic, but will not actually consume
3153 -- inventory layer.
3154
3155 if (l_from_exp = 1) then
3156 if (l_layer_chg = 1 or
3157 (l_layer_chg = 0 and l_to_exp = 1)) then
3158 if i_hook = 0 then -- insert MCACD only if there is no cost hook
3159
3160 l_stmt_num := 80;
3161
3162 if l_inv_layer_id = 0 then -- no layer cost
3163 insert into mtl_cst_actual_cost_details(
3164 transaction_id,
3165 organization_id,
3166 layer_id,
3167 cost_element_id,
3168 level_type,
3169 transaction_action_id,
3170 last_update_date,
3171 last_updated_by,
3172 creation_date,
3173 created_by,
3174 last_update_login,
3175 request_id,
3176 program_application_id,
3177 program_id,
3178 program_update_date,
3179 inventory_item_id,
3180 actual_cost,
3181 prior_cost,
3182 new_cost,
3183 insertion_flag,
3184 variance_amount,
3185 user_entered)
3186 values(
3187 i_txn_id,
3188 i_org_id,
3189 l_from_layer,
3190 1,
3191 1,
3192 i_txn_action_id,
3193 sysdate,
3194 i_user_id,
3195 sysdate,
3196 i_user_id,
3197 i_login_id,
3198 i_req_id,
3199 i_prg_appl_id,
3200 i_prg_id,
3201 sysdate,
3202 i_item_id,
3203 0,
3204 0,
3205 0,
3206 'N',
3207 0,
3208 'N');
3209 else -- has layer cost
3210 insert into mtl_cst_actual_cost_details(
3211 transaction_id,
3212 organization_id,
3213 layer_id,
3214 cost_element_id,
3215 level_type,
3216 transaction_action_id,
3217 last_update_date,
3218 last_updated_by,
3219 creation_date,
3220 created_by,
3221 last_update_login,
3222 request_id,
3223 program_application_id,
3224 program_id,
3225 program_update_date,
3226 inventory_item_id,
3227 actual_cost,
3228 prior_cost,
3229 new_cost,
3230 insertion_flag,
3231 variance_amount,
3232 user_entered)
3233 select
3234 i_txn_id,
3235 i_org_id,
3236 l_from_layer,
3237 cilcd.cost_element_id,
3238 cilcd.level_type,
3239 i_txn_action_id,
3240 sysdate,
3241 i_user_id,
3242 sysdate,
3243 i_user_id,
3244 i_login_id,
3245 i_req_id,
3246 i_prg_appl_id,
3247 i_prg_id,
3248 sysdate,
3249 i_item_id,
3250 cilcd.layer_cost,
3251 0,
3252 NULL,
3253 'N',
3254 0,
3255 'N'
3256 from cst_inv_layer_cost_details cilcd
3257 where layer_id = l_from_layer
3258 and inv_layer_id = l_inv_layer_id;
3259 end if; --- i checking layer cost and inserting MCACD
3260
3261 if l_debug = 'Y' then
3262 FND_FILE.PUT_LINE(FND_FILE.LOG,'MCACD inserted for trxn '
3263 || to_char(i_txn_id)
3264 || ':' || to_char(l_stmt_num));
3265 end if;
3266
3267 -- update MMT with cost information
3268 l_stmt_num := 85;
3269
3270 CSTPAVCP.update_mmt(
3271 i_org_id,
3272 i_txn_id,
3273 -1, -- i_txfr_txn_id
3274 i_layer_id,
3275 0, -- i_cost_update
3276 i_user_id,
3277 i_login_id,
3278 i_req_id,
3279 i_prg_appl_id,
3280 i_prg_id,
3281 l_err_num,
3282 l_err_code,
3283 l_err_msg);
3284 if (l_err_num <> 0) then
3285 raise process_error;
3286 end if;
3287 end if; -- end having cost hook
3288 end if; -- layer_chg of 1
3289 else -- from asset sub
3290 if (l_to_exp = 0 and l_layer_chg = 0) then
3291 l_interorg_rec := 3; -- compute layer cost and insert mclacd only
3292 -- otherwise, compute cost and consume layers
3293 /* Added the following if condition to create an inventory layer
3294 for bug 4523417
3295 */
3296
3297 if(l_inv_layer_id = 0) then
3298 l_stmt_num := 86;
3299 SELECT cst_inv_layers_s.nextval
3300 INTO l_inv_layer_id
3301 FROM dual;
3302 l_stmt_num :=87;
3303 SELECT transaction_source_id
3304 INTO l_src_id
3305 FROM mtl_material_transactions
3306 WHERE transaction_id = i_txn_id;
3307 l_stmt_num :=88;
3308 l_src_number := CSTPLENG.GET_SOURCE_NUMBER(i_txn_id,i_txn_src_type,l_src_id);
3309 l_stmt_num :=89;
3310 INSERT
3311 INTO cst_inv_layers (
3312 layer_id,
3313 inv_layer_id,
3314 organization_id,
3315 inventory_item_id,
3316 creation_quantity,
3317 layer_quantity,
3318 layer_cost,
3319 create_transaction_id,
3320 transaction_source_id,
3321 transaction_action_id,
3322 transaction_source_type_id,
3323 transaction_source,
3324 unburdened_cost,
3325 burden_cost,
3326 last_update_date,
3327 last_updated_by,
3328 creation_date,
3329 created_by,
3330 last_update_login,
3331 request_id,
3332 program_application_id,
3333 program_id,
3334 program_update_date)
3335 VALUES (l_from_layer,
3336 l_inv_layer_id,
3337 i_org_id,
3338 i_item_id,
3339 0,
3340 0,
3341 0,
3342 i_txn_id,
3343 l_src_id,
3344 i_txn_action_id,
3345 i_txn_src_type,
3346 l_src_number,
3347 0,
3348 0,
3349 sysdate,
3350 i_user_id,
3351 sysdate,
3352 i_user_id,
3353 i_login_id,
3354 i_req_id,
3355 i_prg_appl_id,
3356 i_prg_id,
3357 sysdate);
3358
3359 IF (l_debug = 'Y') THEN
3360 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inventory layer created');
3361 END IF;
3362
3363 /* Delete cost details for the inventory layer from CILCD.
3364 No rows should be present. Just a safety check
3365 */
3366
3367 DELETE
3368 FROM cst_inv_layer_cost_details
3369 WHERE inv_layer_id = l_inv_layer_id;
3370
3371
3372 INSERT
3373 INTO cst_inv_layer_cost_details (
3374 layer_id,
3375 inv_layer_id,
3376 level_type,
3377 cost_element_id,
3378 layer_cost,
3379 last_update_date,
3380 last_updated_by,
3381 creation_date,
3382 created_by,
3383 last_update_login,
3384 request_id,
3385 program_application_id,
3386 program_id,
3387 program_update_date)
3388 VALUES( l_from_layer,
3389 l_inv_layer_id,
3390 1,
3391 1,
3392 0,
3393 sysdate,
3394 i_user_id,
3395 sysdate,
3396 i_user_id,
3397 i_login_id,
3398 i_req_id,
3399 i_prg_appl_id,
3400 i_prg_id,
3401 sysdate );
3402 end if;
3403 end if;
3404
3405 l_stmt_num := 90;
3406
3407 l_new_cost := CSTPLENG.compute_layer_actual_cost(
3408 i_org_id,
3409 i_cost_method,
3410 i_txn_id,
3411 l_from_layer, --i_layer_id,
3412 i_hook,
3413 i_cost_type,
3414 i_mat_ct_id,
3415 i_avg_rates_id,
3416 i_item_id,
3417 l_from_qty, -- i_txn_qty
3418 i_txn_action_id,
3419 i_txn_src_type,
3420 l_interorg_rec,
3421 l_exp_flag, -- i_exp_flag
3422 i_user_id,
3423 i_login_id,
3424 i_req_id,
3425 i_prg_appl_id,
3426 i_prg_id,
3427 l_err_num,
3428 l_err_code,
3429 l_err_msg);
3430 if (l_err_num <> 0) then
3431 raise process_error;
3432 end if;
3433
3434 if l_debug = 'Y' then
3435 FND_FILE.PUT_LINE(FND_FILE.LOG,'Compute_layer_actual_cost completed for txn '
3436 || to_char(i_txn_id)
3437 || ':' || to_char(l_stmt_num));
3438 end if;
3439 /* Borrow Payback Enhancement Bug#2325290 */
3440 if (l_txn_type_id = 68) and (l_layer_chg = 0) then
3441
3442 if l_debug = 'Y' then
3443 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling payback_variance() ');
3444 end if;
3445
3446 payback_variance(
3447 i_org_id,
3448 i_txn_id,
3449 i_txn_qty,
3450 i_user_id,
3451 i_login_id,
3452 i_req_id,
3453 i_prg_appl_id,
3454 i_prg_id,
3455 i_item_id,
3456 i_hook,
3457 l_from_layer,
3458 l_err_num,
3459 l_err_code,
3460 l_err_msg);
3461
3462 end if;
3463
3464
3465
3466 /* Bug Fix 1393484 */
3467 -- Payback transaction:
3468 -- mclacd of from sub is populated with the borrow cost by compute_layer_actual_cost
3469 -- due to the existence of mctcd with borrow cost.
3470 -- However, we want to store the current inv layer cost instead of borrow cost
3471 -- for the from sub.
3472
3473 l_stmt_num := 92;
3474
3475 if (l_txn_type_id = 68) and (i_hook <> 1) and (l_layer_chg = 1) then
3476 update mtl_cst_layer_act_cost_details mclacd
3477 set actual_cost = layer_cost
3478 where mclacd.transaction_id = i_txn_id
3479 and mclacd.organization_id = i_org_id
3480 and mclacd.layer_id = l_from_layer;
3481 end if;
3482 /* End bug fix 1393484 */
3483
3484 -- update layer average cost
3485 if l_interorg_rec = 3 then
3486 l_no_update_qty := 1;
3487 else
3488 l_no_update_qty := 0;
3489 end if;
3490
3491 l_stmt_num := 95;
3492 CSTPLENG.calc_layer_average_cost(
3493 i_org_id,
3494 i_txn_id,
3495 l_from_layer,
3496 i_cost_type,
3497 i_item_id,
3498 l_from_qty, -- i_txn_qty
3499 i_txn_action_id,
3500 i_hook,
3501 0, -- i_no_update_mmt
3502 l_no_update_qty,
3503 i_user_id,
3504 i_login_id,
3505 i_req_id,
3506 i_prg_appl_id,
3507 i_prg_id,
3508 l_err_num,
3509 l_err_code,
3510 l_err_msg);
3511 if (l_err_num <> 0) then
3512 raise process_error;
3513 end if;
3514
3515 if l_debug = 'Y' then
3516 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calc_layer_average_cost completed for txn '
3517 || to_char(i_txn_id)
3518 || ':' || to_char(l_stmt_num));
3519 end if;
3520 end if; -- end from asset sub
3521
3522 /************************************************************
3523 ** Compute inventory layers for the to subinventory **
3524 ************************************************************/
3525
3526 /* If it's an expense item, there is no cost impact on 'to' sub.
3527 Three scenarios where we need to process cost of asset item for 'to' sub:
3528 1. Layer change and to expense sub: insert MCACD using 'from' layer's MCACD.
3529 No layer consumption or layer cost impact.
3530 2. Layer change and to asset sub: create MCTCD using 'from' layer's MCACD
3531 then call API's to create new layer and to insert MCACD for 'to' layer
3532 3. No layer change and to asset sub: create MCTCD using latest layer cost
3533 of 'from' layer then call API's to create new layer and to insert
3534 MCACD for 'to' layer.
3535 */
3536
3537 /*-------------------------------------------------------------
3538 Scenario 1: Layer change and transfer to an expense sub
3539 ------------------------------------------------------------*/
3540 /* Set l_exp_flag for the 'to' subinventory */
3541 if l_exp_item = 1 or
3542 l_to_exp = 1 then
3543 l_exp_flag := 1;
3544 else
3545 l_exp_flag := 0;
3546 end if;
3547
3548 l_stmt_num := 100;
3549
3550 if (l_exp_item = 0 and l_layer_chg = 1 and l_to_exp = 1 and i_hook = 0) then
3551 insert into mtl_cst_actual_cost_details(
3552 transaction_id,
3553 organization_id,
3554 layer_id,
3555 cost_element_id,
3556 level_type,
3557 transaction_action_id,
3558 last_update_date,
3559 last_updated_by,
3560 creation_date,
3561 created_by,
3562 last_update_login,
3563 request_id,
3564 program_application_id,
3565 program_id,
3566 program_update_date,
3567 inventory_item_id,
3568 actual_cost,
3569 prior_cost,
3570 new_cost,
3571 insertion_flag,
3572 variance_amount,
3573 user_entered)
3574 select
3575 i_txn_id,
3576 i_org_id,
3577 l_to_layer,
3578 mcacd.cost_element_id,
3579 mcacd.level_type,
3580 i_txn_action_id,
3581 sysdate,
3582 i_user_id,
3583 sysdate,
3584 i_user_id,
3585 i_login_id,
3586 i_req_id,
3587 i_prg_appl_id,
3588 i_prg_id,
3589 sysdate,
3590 i_item_id,
3591 mcacd.actual_cost,
3592 mcacd.actual_cost,
3593 mcacd.actual_cost,
3594 'N',
3595 0,
3596 'N'
3597 from mtl_cst_actual_cost_details mcacd
3598 where transaction_id = i_txn_id
3599 and organization_id = i_org_id
3600 and layer_id = l_from_layer;
3601
3602 if l_debug = 'Y' then
3603 FND_FILE.PUT_LINE(FND_FILE.LOG,'MCTCD inserted for txn '
3604 || to_char(i_txn_id)
3605 || ',to layer '|| to_char(l_from_layer)
3606 || ',stmt ' || to_char(l_stmt_num));
3607 end if;
3608 end if; -- end of scenario 1
3609
3610 /*------------------------------------------------------------
3611 Scenario 2: Layr change and transfer to an asset sub
3612 ------------------------------------------------------------*/
3613 l_stmt_num := 110;
3614
3615 -- If cost hook is on, no need to populate MCTCD because
3616 -- compute_layer_actual_cost will look for user-populated MCACD.
3617
3618 if (l_exp_item = 0 and l_layer_chg = 1 and l_to_exp = 0) then
3619 if i_hook = 0 then -- no cost hook
3620 select count(*) -- check for existing mctcd
3621 into l_txn_cost_exist
3622 from mtl_cst_txn_cost_details
3623 where transaction_id = i_txn_id
3624 and organization_id = i_org_id;
3625 if l_txn_cost_exist = 0 then -- populate mctcd if it does not exist
3626 insert into mtl_cst_txn_cost_details(
3627 transaction_id,
3628 organization_id,
3629 cost_element_id,
3630 level_type,
3631 last_update_date,
3632 last_updated_by,
3633 creation_date,
3634 created_by,
3635 last_update_login,
3636 request_id,
3637 program_application_id,
3638 program_id,
3639 program_update_date,
3640 inventory_item_id,
3641 transaction_cost)
3642 select
3643 i_txn_id,
3644 i_org_id,
3645 mcacd.cost_element_id,
3646 mcacd.level_type,
3647 sysdate,
3648 i_user_id,
3649 sysdate,
3650 i_user_id,
3651 i_login_id,
3652 i_req_id,
3653 i_prg_appl_id,
3654 i_prg_id,
3655 sysdate,
3656 i_item_id,
3657 mcacd.actual_cost
3658 from mtl_cst_actual_cost_details mcacd
3659 where transaction_id = i_txn_id
3660 and organization_id = i_org_id
3661 and layer_id = l_from_layer;
3662
3663 if l_debug = 'Y' then
3664 FND_FILE.PUT_LINE(FND_FILE.LOG,'MCTCD inserted for txn '
3665 || to_char(i_txn_id)
3666 || ',to layer '|| to_char(l_from_layer)
3667 || ',stmt ' || to_char(l_stmt_num));
3668 end if;
3669
3670 end if; -- end checking for mctcd
3671 end if; -- end checking for cost hook
3672
3673 -- create inventory layer
3674
3675 l_stmt_num := 120;
3676
3677 CSTPLENG.create_layers(
3678 i_org_id,
3679 i_txn_id,
3680 l_to_layer,
3681 i_item_id,
3682 l_to_qty,
3683 i_cost_method,
3684 i_txn_src_type,
3685 i_txn_action_id,
3686 i_hook,
3687 NULL, -- i_interorg_rec
3688 i_cost_type,
3689 i_mat_ct_id,
3690 i_avg_rates_id,
3691 l_exp_flag, -- i_exp_flag
3692 i_user_id,
3693 i_login_id,
3694 i_req_id,
3695 i_prg_appl_id,
3696 i_prg_id,
3697 l_err_num,
3698 l_err_code,
3699 l_err_msg);
3700 if (l_err_num <> 0) then
3701 raise process_error;
3702 end if;
3703
3704 if l_debug = 'Y' then
3705 FND_FILE.PUT_LINE(FND_FILE.LOG,'created_layer called for txn '
3706 || to_char(i_txn_id)
3707 || ',to layer '|| to_char(l_to_layer)
3708 || ',stmt ' || to_char(l_stmt_num));
3709 end if;
3710
3711 l_stmt_num := 125;
3712
3713 CSTPLENG.calc_layer_average_cost(
3714 i_org_id,
3715 i_txn_id,
3716 l_to_layer,
3717 i_cost_type,
3718 i_item_id,
3719 l_to_qty, -- i_txn_qty
3720 i_txn_action_id,
3721 i_hook,
3722 0, -- i_no_update_mmt
3723 0, -- i_no_update_qty
3724 i_user_id,
3725 i_login_id,
3726 i_req_id,
3727 i_prg_appl_id,
3728 i_prg_id,
3729 l_err_num,
3730 l_err_code,
3731 l_err_msg);
3732 if (l_err_num <> 0) then
3733 raise process_error;
3734 end if;
3735
3736 if l_debug = 'Y' then
3737 FND_FILE.PUT_LINE(FND_FILE.LOG,'calc_layer_avg_cost called for txn '
3738 || to_char(i_txn_id)
3739 || ',to layer '|| to_char(l_to_layer)
3740 || ',stmt ' || to_char(l_stmt_num));
3741 end if;
3742
3743 end if; -- end scenario 2
3744
3745 /*----------------------------------------------------------------
3746 Scenario 3: No layer change and transfer of asset item from
3747 expense sub to asset sub.
3748 ----------------------------------------------------------------*/
3749
3750 l_stmt_num := 130;
3751
3752 if (l_exp_item = 0 and l_layer_chg = 0 and l_from_exp = 1 and l_to_exp = 0) then
3753 if i_hook = 0 and l_txn_cost_exist = 0 then -- no cost hook and no mctcd
3754 if l_inv_layer_id = 0 then -- no layer cost
3755 insert into mtl_cst_txn_cost_details(
3756 transaction_id,
3757 organization_id,
3758 cost_element_id,
3759 level_type,
3760 last_update_date,
3761 last_updated_by,
3762 creation_date,
3763 created_by,
3764 last_update_login,
3765 request_id,
3766 program_application_id,
3767 program_id,
3768 program_update_date,
3769 inventory_item_id,
3770 transaction_cost)
3771 values(
3772 i_txn_id,
3773 i_org_id,
3774 1, -- material cost element
3775 1, -- this level
3776 sysdate,
3777 i_user_id,
3778 sysdate,
3779 i_user_id,
3780 i_login_id,
3781 i_req_id,
3782 i_prg_appl_id,
3783 i_prg_id,
3784 sysdate,
3785 i_item_id,
3786 0);
3787 else -- has layer cost
3788 insert into mtl_cst_txn_cost_details(
3789 transaction_id,
3790 organization_id,
3791 cost_element_id,
3792 level_type,
3793 last_update_date,
3794 last_updated_by,
3795 creation_date,
3796 created_by,
3797 last_update_login,
3798 request_id,
3799 program_application_id,
3800 program_id,
3801 program_update_date,
3802 inventory_item_id,
3803 transaction_cost)
3804 select
3805 i_txn_id,
3806 i_org_id,
3807 cilcd.cost_element_id,
3808 cilcd.level_type,
3809 sysdate,
3810 i_user_id,
3811 sysdate,
3812 i_user_id,
3813 i_login_id,
3814 i_req_id,
3815 i_prg_appl_id,
3816 i_prg_id,
3817 sysdate,
3818 i_item_id,
3819 cilcd.layer_cost
3820 from cst_inv_layer_cost_details cilcd
3821 where layer_id = l_from_layer
3822 and inv_layer_id = l_inv_layer_id;
3823 end if; -- end checking for layer cost
3824
3825 if l_debug = 'Y' then
3826 FND_FILE.PUT_LINE(FND_FILE.LOG,'MCTCD inserted for txn '
3827 || to_char(i_txn_id)
3828 || ',to layer '|| to_char(l_to_layer)
3829 || ',stmt ' || to_char(l_stmt_num));
3830 end if;
3831
3832 end if; -- end for no cost hook and no mctcd
3833
3834 -- create inventory layer
3835
3836 l_stmt_num := 140;
3837
3838 CSTPLENG.create_layers(
3839 i_org_id,
3840 i_txn_id,
3841 l_to_layer,
3842 i_item_id,
3843 l_to_qty,
3844 i_cost_method,
3845 i_txn_src_type,
3846 i_txn_action_id,
3847 i_hook,
3848 NULL, -- i_interorg_rec
3849 i_cost_type,
3850 i_mat_ct_id,
3851 i_avg_rates_id,
3852 l_exp_flag, -- i_exp_flag
3853 i_user_id,
3854 i_login_id,
3855 i_req_id,
3856 i_prg_appl_id,
3857 i_prg_id,
3858 l_err_num,
3859 l_err_code,
3860 l_err_msg);
3861 if (l_err_num <> 0) then
3862 raise process_error;
3863 end if;
3864
3865 if l_debug = 'Y' then
3866 FND_FILE.PUT_LINE(FND_FILE.LOG,'created_layer called for txn '
3867 || to_char(i_txn_id)
3868 || ',to layer '|| to_char(l_to_layer)
3869 || ',stmt ' || to_char(l_stmt_num));
3870 end if;
3871
3872 l_stmt_num := 150;
3873
3874 CSTPLENG.calc_layer_average_cost(
3875 i_org_id,
3876 i_txn_id,
3877 l_to_layer,
3878 i_cost_type,
3879 i_item_id,
3880 l_to_qty, -- i_txn_qty
3881 i_txn_action_id,
3882 i_hook,
3883 0, -- i_no_update_mmt
3884 0, -- i_no_update_qty
3885 i_user_id,
3886 i_login_id,
3887 i_req_id,
3888 i_prg_appl_id,
3889 i_prg_id,
3890 l_err_num,
3891 l_err_code,
3892 l_err_msg);
3893 if (l_err_num <> 0) then
3894 raise process_error;
3895 end if;
3896
3897 if l_debug = 'Y' then
3898 FND_FILE.PUT_LINE(FND_FILE.LOG,'calc_layer_avg_cost called for txn '
3899 || to_char(i_txn_id)
3900 || ',to layer '|| to_char(l_to_layer)
3901 || ',stmt ' || to_char(l_stmt_num));
3902 end if;
3903
3904 end if; -- end scenario 3
3905
3906 -- Update the recv side mmt for subinventory transfer
3907 -- It updates actual_cost, new_cost, prior_cost and variance
3908 -- of the receiving with the shipment side values
3909 /* Bug 3613854
3910 * regardless of whether those values are null in the receiving side,
3911 * and from mcacd instead of mmt to account for transfers within
3912 * and across cost groups.
3913 */
3914 l_stmt_num := 160;
3915
3916 /* Changes for VMI. Adding planning transfer transaction */
3917 if (i_txn_action_id IN (2,5,28,55)) then
3918
3919 select transfer_transaction_id
3920 into l_txf_txn_id
3921 from mtl_material_transactions
3922 where transaction_id = i_txn_id;
3923
3924 l_stmt_num := 170;
3925
3926 update mtl_material_transactions mmt1
3927 set (last_update_date,
3928 last_updated_by,
3929 last_update_login,
3930 request_id,
3931 program_application_id,
3932 program_id,
3933 program_update_date,
3934 actual_cost,
3935 prior_cost,
3936 new_cost,
3937 variance_amount) =
3938 (select sysdate,
3939 i_user_id,
3940 i_login_id,
3941 i_req_id,
3942 i_prg_appl_id,
3943 i_prg_id,
3944 sysdate,
3945 SUM(NVL(mcacd.actual_cost, 0)),
3946 SUM(NVL(mcacd.prior_cost, 0)),
3947 SUM(NVL(mcacd.new_cost, 0)),
3948 SUM(NVL(mcacd.variance_amount, 0))
3949 from mtl_cst_actual_cost_details mcacd
3950 where mcacd.transaction_id = i_txn_id
3951 and mcacd.layer_id = l_to_layer)
3952 where mmt1.transaction_id = l_txf_txn_id
3953 and mmt1.primary_quantity > 0;
3954 end if;
3955
3956 EXCEPTION
3957 when cst_no_hook_allowed then
3958 rollback;
3959 o_err_num := 24030;
3960 o_err_code := substr('CSTPLVCP.sub_transfer('
3961 ||to_char(l_stmt_num)
3962 || '): '
3963 || l_err_msg
3964 || '.',1,240);
3965 fnd_message.set_name('BOM', 'CST_NO_HOOK_ALLOWED');
3966 o_err_msg := fnd_message.get;
3967 o_err_msg := substr(o_err_msg,1,240);
3968
3969 when process_error then
3970 o_err_num := l_err_num;
3971 o_err_code := l_err_code;
3972 o_err_msg := l_err_msg;
3973
3974 when others then
3975 rollback;
3976 o_err_num := SQLCODE;
3977 o_err_msg := 'CSTPLVCP.sub_transfer (' || to_char(l_stmt_num)
3978 || '): '
3979 || substr(SQLERRM, 1,240);
3980 END sub_transfer;
3981
3982 /* Bug 2665290 */
3983 /*========================================================================
3984 -- PROCEDURE
3985 -- payback_variance
3986 --
3987 -- DESCRIPTION
3988 -- This procedure will be called for all Payback transactions across the
3989 -- same cost group.
3990 -- This procedure will identify the cost of all borrow transactions
3991 -- related to the specified payback transactions, compute the average cost
3992 -- calculate the variance and update payback_variance_amount column of MCLACD.
3993 --
3994 -- If layer actual cost hook is used, it will error out
3995 -- since user-entered actual cost is not allowed for payback transaction.
3996
3997 -- HISTORY
3998 -- 09/15/03 Anju Gupta Design
3999
4000 =========================================================================*/
4001
4002 PROCEDURE payback_variance(
4003 I_ORG_ID IN NUMBER,
4004 I_TXN_ID IN NUMBER,
4005 I_TXN_QTY IN NUMBER,
4006 I_USER_ID IN NUMBER,
4007 I_LOGIN_ID IN NUMBER,
4008 I_REQ_ID IN NUMBER,
4009 I_PRG_APPL_ID IN NUMBER,
4010 I_PRG_ID IN NUMBER,
4011 I_ITEM_ID IN NUMBER,
4012 I_HOOK IN NUMBER,
4013 I_FROM_LAYER IN NUMBER,
4014 O_Err_Num OUT NOCOPY NUMBER,
4015 O_Err_Code OUT NOCOPY VARCHAR2,
4016 O_Err_Msg OUT NOCOPY VARCHAR2
4017 )IS
4018
4019 -- this cursor is to find out all the related
4020 -- borrow transactions for a specific payback
4021 -- transaction
4022 cursor c_payback_txn(c_cur_txn_id number) is
4023 select pbp.borrow_transaction_id,
4024 pbp.payback_quantity
4025 from pjm_borrow_paybacks pbp
4026 where pbp.payback_transaction_id = c_cur_txn_id;
4027
4028 -- this cursor is to find out all the mcacd data
4029 -- for a specific transaction_id
4030 cursor c_mclacd_data (c_transaction_id number)is
4031 select mclacd.transaction_id,
4032 mclacd.cost_element_id,
4033 mclacd.level_type,
4034 mclacd.inventory_item_id,
4035 mclacd.actual_cost,
4036 mclacd.layer_id,
4037 mclacd.layer_quantity
4038 from mtl_cst_layer_act_cost_details mclacd
4039 where mclacd.transaction_id = c_transaction_id
4040 and mclacd.layer_id = i_from_layer;
4041
4042 type t_cst_element is table of number
4043 index by binary_integer;
4044
4045 l_temp_borrow_cost t_cst_element;
4046 l_temp_payback_cost t_cst_element;
4047 l_level_type NUMBER;
4048 l_total_borrow_qty NUMBER;
4049 l_count NUMBER;
4050 l_loan_quantity NUMBER;
4051 l_variance NUMBER;
4052 mclacd_variance NUMBER;
4053 l_err_num NUMBER;
4054 l_err_code VARCHAR2(240);
4055 l_err_msg VARCHAR2(240);
4056 l_stmt_num NUMBER;
4057 cst_no_hook_allowed EXCEPTION;
4058
4059 BEGIN
4060 -- initialize local variables
4061 l_err_num := 0;
4062 l_err_code := '';
4063 l_err_msg := '';
4064
4065 l_stmt_num := 10;
4066
4067 -- initialize array with 0
4068
4069 for l_index_counter in 1..10 loop
4070 l_temp_borrow_cost(l_index_counter):=0;
4071 l_temp_payback_cost(l_index_counter):=0;
4072 end loop;
4073
4074 -- check for actual cost hook used.
4075 l_stmt_num := 15;
4076
4077 if i_hook = 1 then
4078 raise cst_no_hook_allowed;
4079 end if;
4080
4081 -- loop through all the payaback txn to find the borrowing cost
4082 -- from MCACD and sum it up.
4083 l_stmt_num := 20;
4084
4085 l_total_borrow_qty := 0;
4086
4087 for c_payback_rec in c_payback_txn(i_txn_id) loop
4088 for c_mclacd_rec in c_mclacd_data(c_payback_rec.borrow_transaction_id) LOOP
4089 if c_mclacd_rec.level_type =1 then
4090 l_temp_borrow_cost(c_mclacd_rec.cost_element_id):=
4091 l_temp_borrow_cost(c_mclacd_rec.cost_element_id) +
4092 c_mclacd_rec.actual_cost * abs(c_mclacd_rec.layer_quantity);
4093 elsif c_mclacd_rec.level_type = 2 then
4094 l_temp_borrow_cost(c_mclacd_rec.cost_element_id + 5):=
4095 l_temp_borrow_cost(c_mclacd_rec.cost_element_id + 5) +
4096 c_mclacd_rec.actual_cost * abs(c_mclacd_rec.layer_quantity);
4097 end if;
4098 end loop; -- end looping c_mclacd_rec
4099 select loan_quantity
4100 into l_loan_quantity
4101 from pjm_borrow_transactions
4102 where borrow_transaction_id = c_payback_rec.borrow_transaction_id;
4103
4104 l_total_borrow_qty := l_total_borrow_qty + abs(l_loan_quantity);
4105 end loop; -- end looping c_payback_rec
4106
4107 fnd_file.put_line(fnd_file.log, 'borrow quantity' || l_total_borrow_qty);
4108
4109 l_stmt_num := 30;
4110 /* Figure out the payback cost */
4111 for c_mclacd_rec in c_mclacd_data(i_txn_id) LOOP
4112 if c_mclacd_rec.level_type =1 then
4113 l_temp_payback_cost(c_mclacd_rec.cost_element_id):=
4114 l_temp_payback_cost(c_mclacd_rec.cost_element_id) +
4115 c_mclacd_rec.actual_cost * abs(c_mclacd_rec.layer_quantity);
4116 elsif c_mclacd_rec.level_type = 2 then
4117 l_temp_payback_cost(c_mclacd_rec.cost_element_id + 5):=
4118 l_temp_payback_cost(c_mclacd_rec.cost_element_id + 5) +
4119 c_mclacd_rec.actual_cost * abs(c_mclacd_rec.layer_quantity);
4120 end if;
4121 end loop; -- end looping c_mcacd_rec
4122
4123 l_stmt_num := 40;
4124
4125 -- do a division here to find out the borrow and payback unit cost
4126 for l_index_counter in 1..10 loop
4127 l_temp_payback_cost(l_index_counter):= l_temp_payback_cost(l_index_counter)
4128 / abs(i_txn_qty);
4129 l_temp_borrow_cost(l_index_counter):= l_temp_borrow_cost(l_index_counter)
4130 / l_total_borrow_qty;
4131 end loop;
4132
4133 l_stmt_num := 50;
4134
4135 for l_index_counter in 1..10 loop
4136 if l_index_counter < 6 then
4137 l_level_type := 1;
4138 else
4139 l_level_type := 2;
4140 end if;
4141
4142 select count(*)
4143 into l_count
4144 from mtl_cst_layer_act_cost_details mcacd
4145 where mcacd.level_type = l_level_type
4146 and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
4147 and mcacd.transaction_id = i_txn_id
4148 and mcacd.layer_id = i_from_layer;
4149
4150 l_stmt_num := 60;
4151
4152 if (l_count) <> 0 then
4153
4154 --payback varaince
4155 l_variance := l_temp_payback_cost(l_index_counter) - l_temp_borrow_cost(l_index_counter);
4156
4157 --variance to be updated in mclacd
4158 mclacd_variance := l_variance * abs(i_txn_qty) / l_count;
4159
4160 update mtl_cst_layer_act_cost_details mcacd
4161 set mcacd.payback_variance_amount = mclacd_variance
4162 where mcacd.transaction_id = i_txn_id
4163 and mcacd.cost_element_id = decode(mod(l_index_counter,5),0,5,mod(l_index_counter,5))
4164 and mcacd.level_type = l_level_type
4165 and mcacd.layer_id = i_from_layer;
4166 end if;
4167 end loop;
4168
4169 EXCEPTION
4170 when cst_no_hook_allowed then
4171 rollback;
4172 o_err_num := 24020;
4173 o_err_code := substr('CSTPLVCP.payback_variance('
4174 ||to_char(l_stmt_num)
4175 || '): '
4176 || l_err_msg
4177 || '.',1,240);
4178 fnd_message.set_name('BOM', 'CST_NO_HOOK_ALLOWED');
4179 o_err_msg := fnd_message.get;
4180 o_err_msg := substr(o_err_msg,1,240);
4181 when others then
4182 rollback;
4183 o_err_num := SQLCODE;
4184 o_err_msg := 'CSTPLVCP.payback_variance (' || to_char(l_stmt_num) ||
4185 '): '
4186 || substr(SQLERRM, 1,200);
4187
4188 END payback_variance;
4189
4190 /* ===========================================================
4191 OPM INVCONV umoogala Process-Discrete trasnfers Enh
4192 This procedure computes cost for a logical receipt in
4193 receiving organizations for a process discrete transfer
4194 ===========================================================*/
4195
4196 PROCEDURE Cost_Logical_itr_receipt(
4197 I_ORG_ID IN NUMBER,
4198 I_TXN_ID IN NUMBER,
4199 I_COST_METHOD IN NUMBER,
4200 I_LAYER_ID IN NUMBER,
4201 I_COST_TYPE IN NUMBER,
4202 I_ITEM_ID IN NUMBER,
4203 I_TXN_ACTION_ID IN NUMBER,
4204 I_TXN_SRC_TYPE IN NUMBER,
4205 I_TXN_ORG_ID IN NUMBER,
4206 I_TXFR_ORG_ID IN NUMBER,
4207 I_COST_GRP_ID IN NUMBER,
4208 I_TXFR_COST_GRP IN NUMBER,
4209 I_FOB_POINT IN NUMBER,
4210 I_MAT_CT_ID IN NUMBER,
4211 I_AVG_RATES_ID IN NUMBER,
4212 I_USER_ID IN NUMBER,
4213 I_LOGIN_ID IN NUMBER,
4214 I_REQ_ID IN NUMBER,
4215 I_PRG_APPL_ID IN NUMBER,
4216 I_PRG_ID IN NUMBER,
4217 I_TPRICE_OPTION IN NUMBER,
4218 I_TXF_PRICE IN NUMBER,
4219 O_TXN_QTY IN OUT NOCOPY NUMBER,
4220 O_INTERORG_REC IN OUT NOCOPY NUMBER,
4221 O_NO_UPDATE_MMT IN OUT NOCOPY NUMBER,
4222 O_EXP_FLAG IN OUT NOCOPY NUMBER,
4223 O_Err_Num OUT NOCOPY NUMBER,
4224 O_Err_Code OUT NOCOPY VARCHAR2,
4225 O_Err_Msg OUT NOCOPY VARCHAR2
4226 ) IS
4227
4228 l_err_num NUMBER;
4229 l_err_code VARCHAR2(240);
4230 l_err_msg VARCHAR2(240);
4231 l_stmt_num NUMBER;
4232 process_error EXCEPTION;
4233 l_txn_update_id NUMBER;
4234 l_compute_txn_cost NUMBER;
4235 l_from_org NUMBER;
4236 l_to_org NUMBER;
4237 l_from_cost_grp NUMBER;
4238 l_to_cost_grp NUMBER;
4239 l_cost_type_id NUMBER;
4240 l_snd_txn_cost NUMBER;
4241 l_rcv_txn_cost NUMBER;
4242 l_new_txn_cost NUMBER;
4243 l_txn_cost NUMBER;
4244 l_txfr_cost NUMBER;
4245 l_trans_cost NUMBER;
4246 l_std_from_org NUMBER;
4247 l_std_to_org NUMBER;
4248 l_std_org NUMBER;
4249 l_std_cost_org NUMBER;
4250 l_std_exp NUMBER;
4251 l_update_std NUMBER;
4252 l_snd_sob_id NUMBER;
4253 l_snd_curr VARCHAR2(10);
4254 l_rcv_sob_id NUMBER;
4255 l_rcv_curr VARCHAR2(10);
4256 l_curr_type VARCHAR2(30);
4257 l_conv_rate NUMBER;
4258 l_conv_date DATE;
4259 l_snd_uom VARCHAR2(3);
4260 l_rcv_uom VARCHAR2(3);
4261 l_snd_qty NUMBER;
4262 l_count NUMBER;
4263 -- item cost history stuff
4264 l_transfer_layer_id NUMBER;
4265 l_transfer_layer_qty NUMBER;
4266 l_which_org NUMBER;
4267 l_which_cst_grp NUMBER;
4268 -- item cost history stuff
4269 -- elemental visibility
4270 l_movh NUMBER;
4271 l_movh_cost NUMBER;
4272 l_rec_movh_cost NUMBER;
4273 l_mctcd_ovhd NUMBER;
4274 l_from_layed NUMBER;
4275 l_elemental_visible varchar2(1);
4276 -- elemental visibility
4277 l_um_rate NUMBER;
4278 l_return_status VARCHAR2(1);
4279 l_msg_count NUMBER;
4280 l_msg_data VARCHAR2(240);
4281 l_earn_moh NUMBER;
4282 moh_rules_error EXCEPTION;
4283
4284 l_to_std_exp NUMBER;
4285 l_txfr_std_exp NUMBER;
4286
4287 l_from_method NUMBER;
4288 l_to_method NUMBER;
4289 l_from_layer NUMBER;
4290 l_to_layer NUMBER;
4291
4292 l_procedure_name VARCHAR2(60);
4293
4294 BEGIN
4295 -- initialize local variables
4296 l_procedure_name := 'Cost_Logical_itr_receipt';
4297 l_err_num := 0;
4298 l_err_code := '';
4299 l_err_msg := '';
4300 l_update_std := 0;
4301 l_snd_qty := o_txn_qty;
4302 l_std_exp := 0;
4303
4304 IF g_debug = 'Y' THEN
4305 fnd_file.put_line(fnd_file.log, l_procedure_name || ' <<<');
4306 END IF;
4307
4308 l_earn_moh := 1;
4309 l_return_status := fnd_api.g_ret_sts_success;
4310 l_msg_count := 0;
4311
4312 l_txfr_std_exp := 0;
4313 l_to_std_exp := 0;
4314 l_elemental_visible := 'N';
4315
4316
4317 -- Figure the from and to org for this transaction.
4318 l_stmt_num := 1;
4319 if (i_txn_action_id IN (21,22)) then
4320 l_from_org := i_txn_org_id;
4321 l_to_org := i_txfr_org_id;
4322 l_from_cost_grp := i_cost_grp_id;
4323 l_to_cost_grp := i_txfr_cost_grp;
4324 elsif (i_txn_action_id = 12) then
4325 l_from_org := i_txfr_org_id;
4326 l_to_org := i_txn_org_id;
4327 l_from_cost_grp := i_txfr_cost_grp;
4328 l_to_cost_grp := i_cost_grp_id;
4329 elsif (i_txn_action_id =3 and o_txn_qty <0) then
4330 l_from_org := i_txn_org_id;
4331 l_to_org := i_txfr_org_id;
4332 l_from_cost_grp := i_cost_grp_id;
4333 l_to_cost_grp := i_txfr_cost_grp;
4334 else
4335 l_from_org := i_txfr_org_id;
4336 l_to_org := i_txn_org_id;
4337 l_from_cost_grp := i_txfr_cost_grp;
4338 l_to_cost_grp := i_cost_grp_id;
4339 end if;
4340
4341 l_stmt_num := 2;
4342 select primary_cost_method
4343 into l_from_method
4344 from mtl_parameters
4345 where organization_id = l_from_org;
4346
4347 l_stmt_num := 3;
4348 select primary_cost_method
4349 into l_to_method
4350 from mtl_parameters
4351 where organization_id = l_to_org;
4352
4353 --
4354 -- Bug 5702988: This flag was set to 1 in cmllcw.lpc when receiving
4355 -- sub is expense sub. For fob shipment, we have to treat subinv as
4356 -- asset sub. Just go by item flag only.
4357 -- This flag is being passed to create_layers procedure and
4358 -- incorrect accounting (MOH not earned) was being done.
4359 --
4360 -- We have to overwrite this flag here as it was done in
4361 -- interorg procedure for discrete/discrete orgs.
4362 -- Comments from discrete code:
4363 -- If the transaction organization id is not the organization id of this
4364 -- cost worker then we have to make sure this transaction record in mmt
4365 -- does not get updated. Most likely this is an intrasit interorg transaction
4366 -- and we are processing either the shipping or the receiving side. For the
4367 -- same reason we cannot rely on the expense flag since it is based on
4368 -- the current record's subinventory code.
4369 --
4370 if (i_txn_action_id = 22 or i_txn_action_id = 15)
4371 then
4372 select decode(inventory_asset_flag, 'Y',0,1)
4373 into o_exp_flag
4374 from mtl_system_items
4375 where inventory_item_id = i_item_id
4376 and organization_id = i_org_id;
4377 end if;
4378
4379 -- item cost history stuff
4380 --
4381 -- For p-d xfers, in Avg Cost processor Org will always be Average Costin Org.
4382 -- Shipment to Std Orgs are being processed by Std Cost processor.
4383 --
4384 if( ( (i_txn_action_id = 21) and (i_fob_point = 1) and (l_std_to_org = 0) ) OR
4385 ( (i_txn_action_id = 12) and (i_fob_point = 1) and (l_std_to_org = 0) ) OR
4386 ( (i_txn_action_id = 21) and (i_fob_point = 2) and (l_std_from_org = 0) ) OR
4387 ( (i_txn_action_id = 12) and (i_fob_point = 2) and (l_std_to_org = 0) ) ) then
4388
4389 l_which_org := l_to_org;
4390 l_which_cst_grp := i_cost_grp_id;
4391
4392 if i_org_id = l_which_org then
4393 l_stmt_num := 10;
4394 UPDATE mtl_material_transactions mmt
4395 SET TRANSFER_PRIOR_COSTED_QUANTITY =
4396 (SELECT
4397 layer_quantity
4398 FROM cst_quantity_layers cql
4399 WHERE cql.organization_id = l_which_org
4400 AND cql.inventory_item_id = i_item_id
4401 AND cql.cost_group_id = l_which_cst_grp)
4402 WHERE mmt.transaction_id = i_txn_id
4403 AND EXISTS (
4404 SELECT 'X'
4405 FROM cst_quantity_layers cql
4406 WHERE cql.organization_id = l_which_org
4407 AND cql.inventory_item_id = i_item_id
4408 AND cql.cost_group_id = l_which_cst_grp);
4409
4410 IF SQL%ROWCOUNT = 0 THEN
4411 l_stmt_num := 20;
4412 update mtl_material_transactions mmt
4413 set TRANSFER_PRIOR_COSTED_QUANTITY = 0
4414 where mmt.transaction_id = i_txn_id;
4415 END IF;
4416 end if;
4417 end if;
4418 -- End of item cost history
4419
4420 --
4421 -- Got rid of big chunck of code for standard costing org.
4422 -- For p-d xfers, in Avg Cost processor Org will always be Average Costin Org.
4423 -- Shipment to Std Orgs are being processed by Std Cost processor.
4424 --
4425
4426 l_stmt_num := 30;
4427 SELECT nvl(transportation_cost,0)
4428 INTO l_trans_cost
4429 FROM mtl_material_transactions
4430 WHERE transaction_id = i_txn_id;
4431
4432 --
4433 -- No need for any UOM or currency conversion as it is already done
4434 -- while creating MMT row for this logical transactions.
4435 -- For details refer to: INV_LOGICAL_TRANSACTIONS_PUB.create_opm_disc_logical_trx
4436 --
4437
4438 -- For process to discrete transfers all qtys and costs are properly converted to the
4439 -- owning orgs units.
4440 -- So, i_txf_price and l_trans_cost are in base currency and txn_qty is in primary qty
4441 --
4442
4443 /* INVCONV Bug#5461814 ANTHIYAG 17-Aug-2006 Start */
4444 ---l_rcv_txn_cost := ((i_txf_price * abs(o_txn_qty)) + l_trans_cost) / abs(o_txn_qty);
4445 if ((i_txn_action_id = 12 and i_fob_point = 2) or (i_txn_action_id = 3 and o_txn_qty > 0)) then
4446 l_rcv_txn_cost := ((i_txf_price * abs(o_txn_qty))) / abs(o_txn_qty);
4447 else
4448 l_rcv_txn_cost := ((i_txf_price * abs(o_txn_qty)) + l_trans_cost) / abs(o_txn_qty);
4449 end if;
4450 /* INVCONV Bug#5461814 ANTHIYAG 17-Aug-2006 End */
4451
4452 -- The following are considered interorg receipt transactions.
4453 -- These are transactions where ownership changes and the current org
4454 -- is the receiving org.
4455 if ((i_txn_action_id = 3 and o_txn_qty > 0) OR
4456 (i_txn_action_id = 15) OR
4457 (i_txn_action_id = 12 and i_fob_point = 2))
4458 then
4459 o_interorg_rec := 1;
4460 else
4461 o_interorg_rec := 0;
4462 end if;
4463
4464 IF g_debug = 'Y' THEN
4465 fnd_file.put_line(fnd_file.log, 'TxnOrg: ' || i_txn_org_id || ' Item: ' || i_item_id);
4466 fnd_file.put_line(fnd_file.log, 'fromOrg: ' || l_from_org || ' toOrg: ' || l_to_org);
4467 fnd_file.put_line(fnd_file.log, 'fromCG: ' || l_from_cost_grp || ' toCG: ' || l_to_cost_grp);
4468 fnd_file.put_line(fnd_file.log, 'Transaction Action: ' || i_txn_action_id);
4469 fnd_file.put_line(fnd_file.log, 'Transfer price options: ' || i_tprice_option ||
4470 ' Transfer Price: ' || i_txf_price ||
4471 ' Trp Cost: ' || l_trans_cost || ' Qty: ' || o_txn_qty);
4472 fnd_file.put_line(fnd_file.log, 'trx: ' || i_txn_id || ' trxCost: ' || l_rcv_txn_cost);
4473 fnd_file.put_line(fnd_file.log, 'o_interorg_rec: ' || o_interorg_rec);
4474 END IF;
4475
4476
4477 if ((i_txn_action_id in (21,22)) OR
4478 (i_txn_action_id = 3 and o_txn_qty < 0))
4479 then
4480
4481 l_stmt_num := 40;
4482
4483 if (l_from_method <> 1) then
4484 select item_cost, layer_id
4485 into l_snd_txn_cost, l_from_layer
4486 from cst_quantity_layers
4487 where organization_id = l_from_org
4488 and inventory_item_id = i_item_id
4489 and cost_group_id = l_from_cost_grp;
4490 end if;
4491
4492 IF g_debug = 'Y' THEN
4493 fnd_file.put_line(fnd_file.log, 'Updating trx: ' || i_txn_id || ' with trxCost: ' || l_rcv_txn_cost);
4494 END IF;
4495
4496 l_stmt_num := 50;
4497 Update mtl_material_transactions
4498 Set transaction_cost = l_rcv_txn_cost
4499 where transaction_id = i_txn_id;
4500
4501
4502 IF g_debug = 'Y' THEN
4503 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Interorg transfer send org: (consume layers) ...');
4504 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(i_txn_org_id) || ':' || to_char(l_from_method));
4505 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(i_txn_id) || ':' || to_char(l_from_layer));
4506 END IF;
4507
4508 --
4509 -- Bug 5222084: consume_layers layer should be called only for the following txns.
4510 --
4511 if ((i_txn_action_id = 3 and o_txn_qty < 0)
4512 OR (i_txn_action_id = 22)
4513 OR (i_txn_action_id = 21 and i_org_id = i_txn_org_id and i_fob_point = 1)) then
4514
4515 if (l_from_method IN (5,6)) then
4516 l_stmt_num := 50;
4517 CSTPLENG.consume_layers(
4518 i_org_id,
4519 i_txn_id,
4520 l_from_layer,
4521 i_item_id,
4522 -1*abs(o_txn_qty),
4523 i_cost_method,
4524 i_txn_src_type,
4525 i_txn_action_id,
4526 0,
4527 o_interorg_rec,
4528 i_cost_type,
4529 i_mat_ct_id,
4530 i_avg_rates_id,
4531 o_exp_flag,
4532 i_user_id,
4533 i_login_id,
4534 i_req_id,
4535 i_prg_appl_id,
4536 i_prg_id,
4537 l_err_num,
4538 l_err_code,
4539 l_err_msg);
4540 end if;
4541
4542 end if;
4543
4544 elsif (i_txn_action_id = 12 AND i_fob_point = 1)
4545 then
4546
4547 IF g_debug = 'Y' THEN
4548 fnd_file.put_line(fnd_file.log, 'Updating trx: ' || i_txn_id || ' with trxCost: ' || l_rcv_txn_cost);
4549 END IF;
4550
4551 l_stmt_num := 70;
4552 Update mtl_material_transactions
4553 Set transaction_cost = l_rcv_txn_cost
4554 where transaction_id = i_txn_id;
4555
4556 elsif ((i_txn_action_id = 15) OR
4557 (i_txn_action_id = 3 and o_txn_qty > 0) OR
4558 (i_txn_action_id = 12 AND i_fob_point = 2))
4559 then
4560
4561 IF g_debug = 'Y' THEN
4562 fnd_file.put_line(fnd_file.log, 'inserting to MCTCD for txn: ' || i_txn_id || '. trxCost: ' || l_rcv_txn_cost);
4563 END IF;
4564
4565 l_stmt_num := 80;
4566 insert into mtl_cst_txn_cost_details (
4567 transaction_id,
4568 organization_id,
4569 cost_element_id,
4570 level_type,
4571 last_update_date,
4572 last_updated_by,
4573 creation_date,
4574 created_by,
4575 last_update_login,
4576 request_id,
4577 program_application_id,
4578 program_id,
4579 program_update_date,
4580 inventory_item_id,
4581 transaction_cost,
4582 new_average_cost,
4583 percentage_change,
4584 value_change)
4585 values (i_txn_id,
4586 l_to_org,
4587 1,
4588 1,
4589 sysdate,
4590 i_user_id,
4591 sysdate,
4592 i_user_id,
4593 i_login_id,
4594 i_req_id,
4595 i_prg_appl_id,
4596 i_prg_id,
4597 sysdate,
4598 i_item_id,
4599 l_rcv_txn_cost,
4600 0,
4601 0,
4602 0);
4603
4604 l_stmt_num := 90;
4605 -- Update the transaction cost column if appropriate.
4606 Update mtl_material_transactions
4607 Set transaction_cost = l_rcv_txn_cost
4608 where transaction_id = i_txn_id;
4609
4610 if (l_to_method <> 1) then
4611 select nvl(layer_id,0)
4612 into l_to_layer
4613 from cst_quantity_layers
4614 where organization_id = l_to_org
4615 and inventory_item_id = i_item_id
4616 and cost_group_id = l_to_cost_grp;
4617 end if;
4618
4619
4620 IF g_debug = 'Y' THEN
4621 FND_FILE.PUT_LINE(FND_FILE.LOG, l_procedure_name || ': Interorg transfer receiving org: (create layers) ...');
4622 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(i_txn_org_id) || ':' || to_char(l_to_method));
4623 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(i_txn_id) || ':' || to_char(l_to_layer));
4624 END IF;
4625
4626 /* Call create_layers when :
4627 Direct Interorg transfers
4628 Intransit shipment, FOB shipment from non-std to LIFO
4629 Intransit receipt, FOB receipt from non-std to LIFO
4630 */
4631 --
4632 -- Bug 5391121: we need to create layers
4633 -- if (l_to_method IN (5,6) or (i_txn_action_id = 3))
4634 -- then
4635 CSTPLENG.create_layers(
4636 i_org_id,
4637 i_txn_id,
4638 l_to_layer,
4639 i_item_id,
4640 abs(o_txn_qty),
4641 i_cost_method,
4642 i_txn_src_type,
4643 i_txn_action_id,
4644 0,
4645 o_interorg_rec,
4646 i_cost_type,
4647 i_mat_ct_id,
4648 i_avg_rates_id,
4649 o_exp_flag,
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 ;
4659 -- end if;
4660
4661 END IF;
4662
4663 IF g_debug = 'Y' THEN
4664 fnd_file.put_line(fnd_file.log, l_procedure_name || ' >>>');
4665 END IF;
4666
4667 EXCEPTION
4668 when others then
4669 rollback;
4670 o_err_num := SQLCODE;
4671 o_err_msg := 'CSTPAVCP.Logical_itr_receipt(' || to_char(l_stmt_num) ||
4672 '): ' || substr(SQLERRM, 1,200);
4673
4674 END Cost_Logical_itr_receipt;
4675
4676 END CSTPLVCP;