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