[Home] [Help]
PACKAGE BODY: APPS.CSTPPWAC
Source
1 PACKAGE BODY CSTPPWAC AS
2 /* $Header: CSTPWACB.pls 120.42 2011/12/18 00:42:04 fayang ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSTPPWAC';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 -- PROCEDURE
8 -- cost_processor Costs inventory transactions
9 --
10 procedure cost_processor(
11 I_LEGAL_ENTITY IN NUMBER,
12 I_PAC_PERIOD_ID IN NUMBER,
13 I_ORG_ID IN NUMBER,
14 I_COST_GROUP_ID IN NUMBER,
15 I_TXN_COST_GROUP_ID IN NUMBER,
16 I_TXFR_COST_GROUP_ID IN NUMBER,
17 I_COST_TYPE_ID IN NUMBER,
18 I_COST_METHOD IN NUMBER,
19 I_PROCESS_GROUP IN NUMBER,
20 I_TXN_ID IN NUMBER,
21 I_QTY_LAYER_ID IN NUMBER,
22 I_COST_LAYER_ID IN NUMBER,
23 I_PAC_RATES_ID IN NUMBER,
24 I_ITEM_ID IN NUMBER,
25 I_TXN_QTY IN NUMBER,
26 I_TXN_ACTION_ID IN NUMBER,
27 I_TXN_SRC_TYPE_ID IN NUMBER,
28 I_FOB_POINT IN NUMBER,
29 I_EXP_ITEM IN NUMBER,
30 I_EXP_FLAG IN NUMBER,
31 I_COST_HOOK_USED IN NUMBER,
32 I_USER_ID IN NUMBER,
33 I_LOGIN_ID IN NUMBER,
34 I_REQ_ID IN NUMBER,
35 I_PRG_APPL_ID IN NUMBER,
36 I_PRG_ID IN NUMBER,
37 I_TXN_CATEGORY IN NUMBER,
38 O_Err_Num OUT NOCOPY NUMBER,
39 O_Err_Code OUT NOCOPY VARCHAR2,
40 O_Err_Msg OUT NOCOPY VARCHAR2
41 ) IS
42 l_new_cost NUMBER;
43 l_no_update_qty NUMBER;
44 l_hook NUMBER;
45 l_interorg_rec NUMBER;
46 l_across_cgs NUMBER;
47 l_issue_qty NUMBER;
48 l_buy_qty NUMBER;
49 l_make_qty NUMBER;
50 l_err_num NUMBER;
51 l_err_code VARCHAR2(240);
52 l_err_msg VARCHAR2(240);
53 l_stmt_num NUMBER;
54
55 /* --- start of auto log --- */
56 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.cost_processor';
57 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
58 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
59 fnd_log.TEST(fnd_log.level_unexpected, l_module);
60 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
61 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
62 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
63 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
64 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
65
66 /* --- end of auto log --- */
67 BEGIN
68 /* --- start of auto log --- */
69 IF l_plog THEN
70 fnd_log.string(
71 fnd_log.level_procedure,
72 l_module||'.'||l_stmt_num,
73 'Entering CSTPPWAC.cost_processor with '||
74 'I_LEGAL_ENTITY = '||I_LEGAL_ENTITY||','||
75 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
76 'I_ORG_ID = '||I_ORG_ID||','||
77 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
78 'I_TXN_COST_GROUP_ID = '||I_TXN_COST_GROUP_ID||','||
79 'I_TXFR_COST_GROUP_ID = '||I_TXFR_COST_GROUP_ID||','||
80 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
81 'I_COST_METHOD = '||I_COST_METHOD||','||
82 'I_PROCESS_GROUP = '||I_PROCESS_GROUP||','||
83 'I_TXN_ID = '||I_TXN_ID||','||
84 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
85 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
86 'I_PAC_RATES_ID = '||I_PAC_RATES_ID||','||
87 'I_ITEM_ID = '||I_ITEM_ID||','||
88 'I_TXN_QTY = '||I_TXN_QTY||','||
89 'I_TXN_ACTION_ID = '||I_TXN_ACTION_ID||','||
90 'I_TXN_SRC_TYPE_ID = '||I_TXN_SRC_TYPE_ID||','||
91 'I_FOB_POINT = '||I_FOB_POINT||','||
92 'I_EXP_ITEM = '||I_EXP_ITEM||','||
93 'I_EXP_FLAG = '||I_EXP_FLAG||','||
94 'I_COST_HOOK_USED = '||I_COST_HOOK_USED||','||
95 'I_USER_ID = '||I_USER_ID||','||
96 'I_LOGIN_ID = '||I_LOGIN_ID||','||
97 'I_REQ_ID = '||I_REQ_ID||','||
98 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
99 'I_PRG_ID = '||I_PRG_ID||','||
100 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
101 );
102 END IF;
103 /* --- end of auto log --- */
104
105 -- initialize local variables
106 l_err_num := 0;
107 l_err_code := '';
108 l_err_msg := '';
109 l_new_cost := 0;
110 l_no_update_qty := 0;
111 l_hook := i_cost_hook_used;
112 l_interorg_rec := 0;
113 l_across_cgs := 0;
114 l_issue_qty := 0;
115 l_buy_qty := 0;
116 l_make_qty := 0;
117
118 /*
119 insert_txn_history(i_pac_period_id, i_cost_group_id, i_txn_id,
120 i_process_group, i_item_id, i_qty_layer_id,
121 i_txn_qty, i_user_id, i_login_id, i_req_id,
122 i_prg_appl_id, i_prg_id, i_txn_category,
123 l_err_num, l_err_code, l_err_msg);
124 if (l_err_num <> 0) then
125 -- Error occured
126 raise fnd_api.g_exc_unexpected_error;
127 end if;
128 */
129
130 -- No need to process cost update
131 if (i_txn_action_id = 24) then
132 if i_cost_method = 3 then
133 -- PWAC cost method
134 CSTPPWAC.periodic_cost_update(
135 i_pac_period_id,
136 i_cost_group_id,
137 i_cost_type_id,
138 i_txn_id,
139 i_cost_layer_id,
140 i_qty_layer_id,
141 i_item_id,
142 i_user_id,
143 i_login_id,
144 i_req_id,
145 i_prg_appl_id,
146 i_prg_id,
147 i_txn_category,
148 i_txn_qty,/*LCM*/
149 l_err_num,
150 l_err_code,
151 l_err_msg);
152 else
153 -- Incremental LIFO cost method
154 CSTPFCHK.periodic_cost_update_hook(
155 i_pac_period_id,
156 i_cost_group_id,
157 i_cost_type_id,
158 i_txn_id,
159 i_cost_layer_id,
160 i_qty_layer_id,
161 i_item_id,
162 i_user_id,
163 i_login_id,
164 i_req_id,
165 i_prg_appl_id,
166 i_prg_id,
167 i_txn_category,
168 i_txn_qty,/*LCM*/
169 l_err_num,
170 l_err_code,
171 l_err_msg);
172 end if;
173
174 if (l_err_num <> 0) then
175 -- Error occured
176 raise fnd_api.g_exc_unexpected_error;
177 end if;
178
179 /*
180 -- Updating txn history table
181 update_txn_history(i_pac_period_id, i_cost_group_id, i_txn_id,
182 i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
183 l_err_num, l_err_code, l_err_msg);
184 if (l_err_num <> 0) then
185 -- Error occured
186 raise fnd_api.g_exc_unexpected_error;
187 end if;
188 */
189
190 GOTO out_arg_log;
191
192 end if;
193
194 -- InterOrg transfer crossing Cost Groups
195 if (i_txn_cost_group_id <> i_txfr_cost_group_id) then
196 l_across_cgs := 1;
197 else
198 l_across_cgs := 0;
199 end if;
200
201
202 -- 1) The Direct Interorg Receipt
203 -- 2) The Intransit Shipment fob ship and processed by receiving CG
204 -- 3) The Intransit Rceipt fob receipt and processed by receiving CG
205 if ((i_txn_action_id = 3 and i_txn_qty > 0) or
206 (i_txn_action_id = 21 and i_fob_point = 1 and i_cost_group_id = i_txfr_cost_group_id) or
207 (i_txn_action_id = 12 and i_fob_point = 2 and i_cost_group_id = i_txn_cost_group_id) or
208 (i_txn_action_id = 15 and i_fob_point = 1)) -- INVCONV sikhanna, no 22 as cost-derived
209 then
210 l_interorg_rec := 1;
211 else
212 l_interorg_rec := 0;
213 end if;
214
215 -- Call the Actual Cost Hook for following transactions
216 -- 1) asset item
217 -- 2) Cost Owned transactions
218 -- 3) None WIP related transactions.
219 -- Actual Cost Hook for WIP transactions is called in
220 -- WIP transaction processor.
221 if (i_exp_item = 0 and i_process_group = 1 and i_txn_src_type_id <> 5) then
222 l_hook := CSTPPCHK.actual_cost_hook(
223 i_pac_period_id,
224 i_cost_group_id,
225 i_cost_type_id,
226 i_cost_method,
227 i_txn_id,
228 i_cost_layer_id,
229 i_qty_layer_id,
230 i_pac_rates_id,
231 i_user_id,
232 i_login_id,
233 i_req_id,
234 i_prg_appl_id,
235 i_prg_id,
236 l_err_num,
237 l_err_code,
238 l_err_msg);
239
240 if (l_err_num <> 0) then
241 -- Error occured
242 raise fnd_api.g_exc_unexpected_error;
243 end if;
244 end if;
245
246 if (l_hook = -1) then
247 -- If hook is not used then proceed to compute actual cost.
248
249 -- PAC Weighted Average costing method
250 if (i_cost_method = 3) then
251 l_new_cost := CSTPPWAC.compute_pwac_cost(
252 i_pac_period_id,
253 i_org_id,
254 i_cost_group_id,
255 i_cost_type_id,
256 i_txn_id,
257 i_cost_layer_id,
258 i_pac_rates_id,
259 i_item_id,
260 i_txn_qty,
261 i_txn_action_id,
262 i_txn_src_type_id,
263 l_interorg_rec,
264 l_across_cgs,
265 i_exp_flag,
266 i_user_id,
267 i_login_id,
268 i_req_id,
269 i_prg_appl_id,
270 i_prg_id,
271 i_txn_category,
272 l_err_num,
273 l_err_code,
274 l_err_msg);
275 -- All other Fiscal costing method
276 else
277 l_new_cost := CSTPFCHK.compute_pac_cost_hook(
278 i_pac_period_id,
279 i_org_id,
280 i_cost_group_id,
281 i_cost_type_id,
282 i_txn_id,
283 i_cost_layer_id,
284 i_pac_rates_id,
285 i_item_id,
286 i_txn_qty,
287 i_txn_action_id,
288 i_txn_src_type_id,
289 l_interorg_rec,
290 l_across_cgs,
291 i_exp_flag,
292 i_user_id,
293 i_login_id,
294 i_req_id,
295 i_prg_appl_id,
296 i_prg_id,
297 i_txn_category,
298 l_err_num,
299 l_err_code,
300 l_err_msg);
301 end if;
302
303 if (l_err_num <> 0) then
304 -- Error occured
305 raise fnd_api.g_exc_unexpected_error;
306 end if;
307
308 else
309 -- user populated actual cost.
310 l_new_cost := 1;
311 end if;
312
313 -- No need to proceed for expense items and WIP Scrap
314 if (i_exp_item = 1 or i_txn_action_id = 30) then
315 /*
316 -- Updating txn history table
317 update_txn_history(i_pac_period_id, i_cost_group_id, i_txn_id,
318 i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
319 l_err_num, l_err_code, l_err_msg);
320 if (l_err_num <> 0) then
321 -- Error occured
322 raise fnd_api.g_exc_unexpected_error;
323 end if;
324 */
325
326 GOTO out_arg_log;
327
328 -- 1) Sub Transfer
329 -- 2) VMI Planning Transfer
330 -- 3) InterOrg Transfer(within same CG or no ownership changes)
331 -- 4) bug 6942050 fix: internal order shipment staging transfer
332 -- 5) cost group transfer
333 elsif ((i_txn_action_id = 2) or
334 (i_txn_action_id = 5) or
335 (i_txn_action_id = 28) or
336 (i_txn_action_id = 55) or
337 (i_txn_action_id in (3,21,12) and l_across_cgs = 0) or
338 (i_txn_action_id = 21 and i_fob_point = 2) or
339 (i_txn_action_id = 12 and i_fob_point = 1))
340 then
341 CSTPPWAC.sub_transfer(
342 i_pac_period_id,
343 i_org_id,
344 i_cost_group_id,
345 i_cost_type_id,
346 i_txn_id,
347 i_cost_layer_id,
348 i_qty_layer_id,
349 i_pac_rates_id,
350 i_item_id,
351 i_txn_qty,
352 i_txn_action_id,
353 i_txn_src_type_id,
354 i_exp_flag,
355 l_no_update_qty,
356 i_cost_method,
357 i_user_id,
358 i_login_id,
359 i_req_id,
360 i_prg_appl_id,
361 i_prg_id,
362 i_txn_category,
363 l_err_num,
364 l_err_code,
365 l_err_msg);
366
367 if (l_err_num <> 0) then
368 -- Error occured
369 raise fnd_api.g_exc_unexpected_error;
370 end if;
371
372 -- InterOrg Transfer across cost groups(ownership changes)
373 -- INVCONV sikhanna adding 15 and 22 (as these will be across CG's only)
374 elsif (i_txn_action_id in (3,21,12,15,22) and l_across_cgs = 1) then
375
376 CSTPPWAC.interorg( i_pac_period_id,
377 i_org_id,
378 i_cost_group_id,
379 i_txfr_cost_group_id,
380 i_cost_type_id,
381 i_cost_method,
382 i_txn_id,
383 i_cost_layer_id,
384 i_qty_layer_id,
385 i_item_id,
386 i_txn_qty,
387 l_issue_qty,
388 l_buy_qty,
389 l_make_qty,
390 i_txn_action_id,
391 i_txn_src_type_id,
392 i_exp_flag,
393 l_interorg_rec,
394 i_user_id,
395 i_login_id,
396 i_req_id,
397 i_prg_appl_id,
398 i_prg_id,
399 i_txn_category,
400 l_err_num,
401 l_err_code,
402 l_err_msg);
403
404 if (l_err_num <> 0) then
405 -- Error occured
406 raise fnd_api.g_exc_unexpected_error;
407 end if;
408
409 -- The Group 1 and 1' transactions
410 elsif (i_process_group = 1) then
411
412 if (i_txn_action_id in (31,32)) then
413 l_make_qty := i_txn_qty;
414 else
415 l_buy_qty := i_txn_qty;
416 end if;
417
418 -- bug 2541342 : add if clause so we do not reaverage cost for
419 -- group 1 txns of into expense subinventories.
420 -- issue/buy/make qtys still updated to match
421 -- qty adjustments when doing subtransfer out of
422 -- expense subinventories for asset items.
423
424 if (i_exp_flag = 1) then
425
426 UPDATE cst_pac_item_costs cpic
427 SET last_update_date = sysdate,
428 last_updated_by = i_user_id,
429 last_update_login = i_login_id,
430 request_id = i_req_id,
431 program_application_id = i_prg_appl_id,
432 program_id = i_prg_id,
433 program_update_date = sysdate,
434 issue_quantity = issue_quantity + i_txn_qty,
435 buy_quantity = buy_quantity + l_buy_qty,
436 make_quantity = make_quantity + l_make_qty
437 WHERE cpic.cost_layer_id = i_cost_layer_id;
438 else
439 -- end of bug 2541342 addition; original code follows
440
441 CSTPPWAC.cost_owned_txns(
442 i_pac_period_id,
443 i_cost_group_id,
444 i_cost_type_id,
445 i_txn_id,
446 i_cost_layer_id,
447 i_qty_layer_id,
448 i_item_id,
449 i_txn_qty,
450 l_issue_qty,
451 l_buy_qty,
452 l_make_qty,
453 i_txn_action_id,
454 i_cost_method,
455 i_user_id,
456 i_login_id,
457 i_req_id,
458 i_prg_appl_id,
459 i_prg_id,
460 i_txn_category,
461 l_err_num,
462 l_err_code,
463 l_err_msg);
464
465 if (l_err_num <> 0) then
466 -- Error occured
467 raise fnd_api.g_exc_unexpected_error;
468 end if;
469 end if; -- end of if clause for bug 2541342
470
471
472 -- The Group 2 transactions
473 elsif (i_process_group = 2) then
474
475 -- Cost Derived transactions has impact only on issue quantity
476 l_issue_qty := i_txn_qty * -1;
477
478 CSTPPWAC.cost_derived_txns(
479 i_pac_period_id,
480 i_cost_group_id,
481 i_cost_type_id,
482 i_txn_id,
483 i_cost_layer_id,
484 i_qty_layer_id,
485 i_item_id,
486 i_txn_qty,
487 l_issue_qty,
488 l_buy_qty,
489 l_make_qty,
490 i_txn_action_id,
491 i_txn_src_type_id,
492 i_exp_flag,
493 l_no_update_qty,
494 i_cost_method,
495 i_user_id,
496 i_login_id,
497 i_req_id,
498 i_prg_appl_id,
499 i_prg_id,
500 i_txn_category,
501 l_err_num,
502 l_err_code,
503 l_err_msg);
504
505 if (l_err_num <> 0) then
506 -- Error occured
507 raise fnd_api.g_exc_unexpected_error;
508 end if;
509
510 end if;
511
512 /*
513 -- Updating txn history table
514 update_txn_history(i_pac_period_id, i_cost_group_id, i_txn_id,
515 i_user_id, i_login_id, i_req_id, i_prg_appl_id, i_prg_id,
516 l_err_num, l_err_code, l_err_msg);
517 if (l_err_num <> 0) then
518 -- Error occured
519 raise fnd_api.g_exc_unexpected_error;
520 end if;
521 */
522
523 /* --- start of auto log --- */
524 <<out_arg_log>>
525
526 IF l_plog THEN
527 fnd_log.string(
528 fnd_log.level_procedure,
529 l_module||'.'||l_stmt_num,
530 'Exiting CSTPPWAC.cost_processor with '||
531 'O_Err_Num = '||O_Err_Num||','||
532 'O_Err_Code = '||O_Err_Code||','||
533 'O_Err_Msg = '||O_Err_Msg
534 );
535 END IF;
536 /* --- end of auto log --- */
537 EXCEPTION
538 /* --- start of auto log --- */
539 WHEN fnd_api.g_exc_unexpected_error THEN
540 IF l_exceptionlog THEN
541 fnd_msg_pub.add_exc_msg(
542 p_pkg_name => 'CSTPPWAC',
543 p_procedure_name => 'cost_processor',
544 p_error_text => 'An exception has occurred.'
545 );
546 fnd_log.string(
547 fnd_log.level_exception,
548 l_module||'.'||l_stmt_num,
549 'An exception has occurred.'
550 );
551 END IF;
552 o_err_num := l_err_num;
553 o_err_code := l_err_code;
554 o_err_msg := l_err_msg;
555 WHEN OTHERS THEN
556 ROLLBACK;
557 IF l_uLog THEN
558 fnd_message.set_name('BOM','CST_UNEXPECTED');
559 fnd_message.set_token('SQLERRM',SQLERRM);
560 fnd_msg_pub.add;
561 fnd_log.message(
562 fnd_log.level_unexpected,
563 l_module||'.'||l_stmt_num,
564 FALSE
565 );
566 END IF;
567 o_err_num := SQLCODE;
568 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
569 /* --- end of auto log --- */
570 END cost_processor;
571
572 -- PROCEDURE
573 -- cost_owned_txns
574 --
575 procedure cost_owned_txns(
576 I_PAC_PERIOD_ID IN NUMBER,
577 I_COST_GROUP_ID IN NUMBER,
578 I_COST_TYPE_ID IN NUMBER,
579 I_TXN_ID IN NUMBER,
580 I_COST_LAYER_ID IN NUMBER,
581 I_QTY_LAYER_ID IN NUMBER,
582 I_ITEM_ID IN NUMBER,
583 I_TXN_QTY IN NUMBER,
584 I_ISSUE_QTY IN NUMBER,
585 I_BUY_QTY IN NUMBER,
586 I_MAKE_QTY IN NUMBER,
587 I_TXN_ACTION_ID IN NUMBER,
588 I_COST_METHOD IN NUMBER,
589 I_USER_ID IN NUMBER,
590 I_LOGIN_ID IN NUMBER,
591 I_REQ_ID IN NUMBER,
592 I_PRG_APPL_ID IN NUMBER,
593 I_PRG_ID IN NUMBER,
594 I_TXN_CATEGORY IN NUMBER,
595 O_Err_Num OUT NOCOPY NUMBER,
596 O_Err_Code OUT NOCOPY VARCHAR2,
597 O_Err_Msg OUT NOCOPY VARCHAR2
598 ) IS
599 l_txn_cost_exist NUMBER;
600 l_txn_cost NUMBER;
601 l_err_num NUMBER;
602 l_err_code VARCHAR2(240);
603 l_err_msg VARCHAR2(240);
604 l_stmt_num NUMBER;
605
606 /* --- start of auto log --- */
607 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.cost_owned_txns';
608 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
609 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
610 fnd_log.TEST(fnd_log.level_unexpected, l_module);
611 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
612 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
613 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
614 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
615 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
616
617 /* --- end of auto log --- */
618 BEGIN
619 /* --- start of auto log --- */
620 IF l_plog THEN
621 fnd_log.string(
622 fnd_log.level_procedure,
623 l_module||'.'||l_stmt_num,
624 'Entering CSTPPWAC.cost_owned_txns with '||
625 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
626 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
627 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
628 'I_TXN_ID = '||I_TXN_ID||','||
629 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
630 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
631 'I_ITEM_ID = '||I_ITEM_ID||','||
632 'I_TXN_QTY = '||I_TXN_QTY||','||
633 'I_ISSUE_QTY = '||I_ISSUE_QTY||','||
634 'I_BUY_QTY = '||I_BUY_QTY||','||
635 'I_MAKE_QTY = '||I_MAKE_QTY||','||
636 'I_TXN_ACTION_ID = '||I_TXN_ACTION_ID||','||
637 'I_COST_METHOD = '||I_COST_METHOD||','||
638 'I_USER_ID = '||I_USER_ID||','||
639 'I_LOGIN_ID = '||I_LOGIN_ID||','||
640 'I_REQ_ID = '||I_REQ_ID||','||
641 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
642 'I_PRG_ID = '||I_PRG_ID||','||
643 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
644 );
645 END IF;
646 /* --- end of auto log --- */
647
648 -- initialize local variables
649 l_err_num := 0;
650 l_err_code := '';
651 l_err_msg := '';
652
653
654 -- PAC Weighted Average costing method
655 if (i_cost_method = 3) then
656 CSTPPWAC.calc_pwac_cost(
657 i_pac_period_id,
658 i_cost_group_id,
659 i_cost_type_id,
660 i_txn_id,
661 i_cost_layer_id,
662 i_qty_layer_id,
663 i_item_id,
664 i_txn_qty,
665 i_issue_qty,
666 i_buy_qty,
667 i_make_qty,
668 i_user_id,
669 i_login_id,
670 i_req_id,
671 i_prg_appl_id,
672 i_prg_id,
673 i_txn_category,
674 l_err_num,
675 l_err_code,
676 l_err_msg);
677
678 -- All other Fiscal costing method
679 else
680 CSTPFCHK.calc_pac_cost_hook(
681 i_pac_period_id,
682 i_cost_group_id,
683 i_cost_type_id,
684 i_txn_id,
685 i_cost_layer_id,
686 i_qty_layer_id,
687 i_item_id,
688 i_txn_qty,
689 i_issue_qty,
690 i_buy_qty,
691 i_make_qty,
692 i_user_id,
693 i_login_id,
694 i_req_id,
695 i_prg_appl_id,
696 i_prg_id,
697 i_txn_category,
698 l_err_num,
699 l_err_code,
700 l_err_msg);
701 end if;
702
703 if (l_err_num <> 0) then
704 -- Error occured
705 raise fnd_api.g_exc_unexpected_error;
706 end if;
707
708 /* --- start of auto log --- */
709 <<out_arg_log>>
710
711 IF l_plog THEN
712 fnd_log.string(
713 fnd_log.level_procedure,
714 l_module||'.'||l_stmt_num,
715 'Exiting CSTPPWAC.cost_owned_txns with '||
716 'O_Err_Num = '||O_Err_Num||','||
717 'O_Err_Code = '||O_Err_Code||','||
718 'O_Err_Msg = '||O_Err_Msg
719 );
720 END IF;
721 /* --- end of auto log --- */
722 EXCEPTION
723 /* --- start of auto log --- */
724 WHEN fnd_api.g_exc_unexpected_error THEN
725 IF l_exceptionlog THEN
726 fnd_msg_pub.add_exc_msg(
727 p_pkg_name => 'CSTPPWAC',
728 p_procedure_name => 'cost_owned_txns',
729 p_error_text => 'An exception has occurred.'
730 );
731 fnd_log.string(
732 fnd_log.level_exception,
733 l_module||'.'||l_stmt_num,
734 'An exception has occurred.'
735 );
736 END IF;
737 o_err_num := l_err_num;
738 o_err_code := l_err_code;
739 o_err_msg := l_err_msg;
740 WHEN OTHERS THEN
741 ROLLBACK;
742 IF l_uLog THEN
743 fnd_message.set_name('BOM','CST_UNEXPECTED');
744 fnd_message.set_token('SQLERRM',SQLERRM);
745 fnd_msg_pub.add;
746 fnd_log.message(
747 fnd_log.level_unexpected,
748 l_module||'.'||l_stmt_num,
749 FALSE
750 );
751 END IF;
752 o_err_num := SQLCODE;
753 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
754 /* --- end of auto log --- */
755 END cost_owned_txns;
756
757 -- PROCEDURE
758 -- cost_derived_txns
759 --
760 procedure cost_derived_txns(
761 I_PAC_PERIOD_ID IN NUMBER,
762 I_COST_GROUP_ID IN NUMBER,
763 I_COST_TYPE_ID IN NUMBER,
764 I_TXN_ID IN NUMBER,
765 I_COST_LAYER_ID IN NUMBER,
766 I_QTY_LAYER_ID IN NUMBER,
767 I_ITEM_ID IN NUMBER,
768 I_TXN_QTY IN NUMBER,
769 I_ISSUE_QTY IN NUMBER,
770 I_BUY_QTY IN NUMBER,
771 I_MAKE_QTY IN NUMBER,
772 I_TXN_ACTION_ID IN NUMBER,
773 I_TXN_SRC_TYPE_ID IN NUMBER,
774 I_EXP_FLAG IN NUMBER,
775 I_NO_UPDATE_QTY IN NUMBER,
776 I_COST_METHOD IN NUMBER,
777 I_USER_ID IN NUMBER,
778 I_LOGIN_ID IN NUMBER,
779 I_REQ_ID IN NUMBER,
780 I_PRG_APPL_ID IN NUMBER,
781 I_PRG_ID IN NUMBER,
782 I_TXN_CATEGORY IN NUMBER,
783 O_Err_Num OUT NOCOPY NUMBER,
784 O_Err_Code OUT NOCOPY VARCHAR2,
785 O_Err_Msg OUT NOCOPY VARCHAR2
786 ) IS
787 l_err_num NUMBER;
788 l_err_code VARCHAR2(240);
789 l_err_msg VARCHAR2(240);
790 l_stmt_num NUMBER;
791
792 /* --- start of auto log --- */
793 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.cost_derived_txns';
794 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
795 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
796 fnd_log.TEST(fnd_log.level_unexpected, l_module);
797 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
798 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
799 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
800 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
801 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
802
803 /* --- end of auto log --- */
804 BEGIN
805 /* --- start of auto log --- */
806 IF l_plog THEN
807 fnd_log.string(
808 fnd_log.level_procedure,
809 l_module||'.'||l_stmt_num,
810 'Entering CSTPPWAC.cost_derived_txns with '||
811 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
812 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
813 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
814 'I_TXN_ID = '||I_TXN_ID||','||
815 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
816 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
817 'I_ITEM_ID = '||I_ITEM_ID||','||
818 'I_TXN_QTY = '||I_TXN_QTY||','||
819 'I_ISSUE_QTY = '||I_ISSUE_QTY||','||
820 'I_BUY_QTY = '||I_BUY_QTY||','||
821 'I_MAKE_QTY = '||I_MAKE_QTY||','||
822 'I_TXN_ACTION_ID = '||I_TXN_ACTION_ID||','||
823 'I_TXN_SRC_TYPE_ID = '||I_TXN_SRC_TYPE_ID||','||
824 'I_EXP_FLAG = '||I_EXP_FLAG||','||
825 'I_NO_UPDATE_QTY = '||I_NO_UPDATE_QTY||','||
826 'I_COST_METHOD = '||I_COST_METHOD||','||
827 'I_USER_ID = '||I_USER_ID||','||
828 'I_LOGIN_ID = '||I_LOGIN_ID||','||
829 'I_REQ_ID = '||I_REQ_ID||','||
830 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
831 'I_PRG_ID = '||I_PRG_ID||','||
832 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
833 );
834 END IF;
835 /* --- end of auto log --- */
836
837 -- initialize local variables
838 l_err_num := 0;
839 l_err_code := '';
840 l_err_msg := '';
841
842
843 -- PAC Weighted Average costing method
844 if (i_cost_method = 3) then
845 CSTPPWAC.current_pwac_cost(
846 i_cost_layer_id,
847 i_qty_layer_id,
848 i_txn_qty,
849 i_issue_qty,
850 i_buy_qty,
851 i_make_qty,
852 i_txn_action_id,
853 i_exp_flag,
854 i_no_update_qty,
855 i_user_id,
856 i_login_id,
857 i_req_id,
858 i_prg_appl_id,
859 i_prg_id,
860 i_txn_category,
861 i_txn_id,
862 i_item_id,
863 l_err_num,
864 l_err_code,
865 l_err_msg);
866 else
867 -- All other Fiscal costing method
868 CSTPFCHK.current_pac_cost_hook(
869 i_cost_layer_id,
870 i_qty_layer_id,
871 i_txn_qty,
872 i_issue_qty,
873 i_buy_qty,
874 i_make_qty,
875 i_txn_action_id,
876 i_exp_flag,
877 i_no_update_qty,
878 i_user_id,
879 i_login_id,
880 i_req_id,
881 i_prg_appl_id,
882 i_prg_id,
883 l_err_num,
884 l_err_code,
885 l_err_msg);
886 end if;
887
888 if (l_err_num <> 0) then
889 -- Error occured
890 raise fnd_api.g_exc_unexpected_error;
891 end if;
892
893 /* --- start of auto log --- */
894 <<out_arg_log>>
895
896 IF l_plog THEN
897 fnd_log.string(
898 fnd_log.level_procedure,
899 l_module||'.'||l_stmt_num,
900 'Exiting CSTPPWAC.cost_derived_txns with '||
901 'O_Err_Num = '||O_Err_Num||','||
902 'O_Err_Code = '||O_Err_Code||','||
903 'O_Err_Msg = '||O_Err_Msg
904 );
905 END IF;
906 /* --- end of auto log --- */
907 EXCEPTION
908 /* --- start of auto log --- */
909 WHEN fnd_api.g_exc_unexpected_error THEN
910 IF l_exceptionlog THEN
911 fnd_msg_pub.add_exc_msg(
912 p_pkg_name => 'CSTPPWAC',
913 p_procedure_name => 'cost_derived_txns',
914 p_error_text => 'An exception has occurred.'
915 );
916 fnd_log.string(
917 fnd_log.level_exception,
918 l_module||'.'||l_stmt_num,
919 'An exception has occurred.'
920 );
921 END IF;
922 o_err_num := l_err_num;
923 o_err_code := l_err_code;
924 o_err_msg := l_err_msg;
925 WHEN OTHERS THEN
926 ROLLBACK;
927 IF l_uLog THEN
928 fnd_message.set_name('BOM','CST_UNEXPECTED');
929 fnd_message.set_token('SQLERRM',SQLERRM);
930 fnd_msg_pub.add;
931 fnd_log.message(
932 fnd_log.level_unexpected,
933 l_module||'.'||l_stmt_num,
934 FALSE
935 );
936 END IF;
937 o_err_num := SQLCODE;
938 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
939 /* --- end of auto log --- */
940 END cost_derived_txns;
941
942 -- PROCEDURE
943 -- sub_transfer
944 --
945 procedure sub_transfer(
946 I_PAC_PERIOD_ID IN NUMBER,
947 I_ORG_ID IN NUMBER,
948 I_COST_GROUP_ID IN NUMBER,
949 I_COST_TYPE_ID IN NUMBER,
950 I_TXN_ID IN NUMBER,
951 I_COST_LAYER_ID IN NUMBER,
952 I_QTY_LAYER_ID IN NUMBER,
953 I_PAC_RATES_ID IN NUMBER,
954 I_ITEM_ID IN NUMBER,
955 I_TXN_QTY IN NUMBER,
956 I_TXN_ACTION_ID IN NUMBER,
957 I_TXN_SRC_TYPE_ID IN NUMBER,
958 I_EXP_FLAG IN NUMBER,
959 I_NO_UPDATE_QTY IN NUMBER,
960 I_COST_METHOD IN NUMBER,
961 I_USER_ID IN NUMBER,
962 I_LOGIN_ID IN NUMBER,
963 I_REQ_ID IN NUMBER,
964 I_PRG_APPL_ID IN NUMBER,
965 I_PRG_ID IN NUMBER,
966 I_TXN_CATEGORY IN NUMBER,
967 O_Err_Num OUT NOCOPY NUMBER,
968 O_Err_Code OUT NOCOPY VARCHAR2,
969 O_Err_Msg OUT NOCOPY VARCHAR2
970 ) IS
971 l_exp1 NUMBER;
972 l_exp2 NUMBER;
973 l_from_exp NUMBER;
974 l_to_exp NUMBER;
975 l_txn_qty NUMBER;
976 l_issue_qty NUMBER;
977 l_buy_qty NUMBER;
978 l_make_qty NUMBER;
979 l_err_num NUMBER;
980 l_err_code VARCHAR2(240);
981 l_err_msg VARCHAR2(240);
982 l_stmt_num NUMBER;
983
984 /* --- start of auto log --- */
985 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.sub_transfer';
986 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
987 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
988 fnd_log.TEST(fnd_log.level_unexpected, l_module);
989 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
990 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
991 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
992 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
993 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
994
995 /* --- end of auto log --- */
996 BEGIN
997 /* --- start of auto log --- */
998 IF l_plog THEN
999 fnd_log.string(
1000 fnd_log.level_procedure,
1001 l_module||'.'||l_stmt_num,
1002 'Entering CSTPPWAC.sub_transfer with '||
1003 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
1004 'I_ORG_ID = '||I_ORG_ID||','||
1005 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
1006 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
1007 'I_TXN_ID = '||I_TXN_ID||','||
1008 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
1009 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
1010 'I_PAC_RATES_ID = '||I_PAC_RATES_ID||','||
1011 'I_ITEM_ID = '||I_ITEM_ID||','||
1012 'I_TXN_QTY = '||I_TXN_QTY||','||
1013 'I_TXN_ACTION_ID = '||I_TXN_ACTION_ID||','||
1014 'I_TXN_SRC_TYPE_ID = '||I_TXN_SRC_TYPE_ID||','||
1015 'I_EXP_FLAG = '||I_EXP_FLAG||','||
1016 'I_NO_UPDATE_QTY = '||I_NO_UPDATE_QTY||','||
1017 'I_COST_METHOD = '||I_COST_METHOD||','||
1018 'I_USER_ID = '||I_USER_ID||','||
1019 'I_LOGIN_ID = '||I_LOGIN_ID||','||
1020 'I_REQ_ID = '||I_REQ_ID||','||
1021 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
1022 'I_PRG_ID = '||I_PRG_ID||','||
1023 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
1024 );
1025 END IF;
1026 /* --- end of auto log --- */
1027
1028 -- initialize local variables
1029 l_err_num := 0;
1030 l_err_code := '';
1031 l_err_msg := '';
1032 l_issue_qty := 0;
1033 l_buy_qty := 0;
1034 l_make_qty := 0;
1035
1036
1037 l_stmt_num := 10;
1038 select decode(asset_inventory,1,0,1)
1039 into l_exp1
1040 from mtl_secondary_inventories msi,
1041 mtl_material_transactions mmt
1042 where msi.secondary_inventory_name = mmt.subinventory_code
1043 and msi.organization_id = i_org_id
1044 and mmt.transaction_id = i_txn_id
1045 and mmt.organization_id = i_org_id;
1046
1047
1048 -- Intransit is always Asset
1049 if (i_txn_action_id in (21,12)) then
1050 l_exp2 := 0;
1051 else
1052 l_stmt_num := 20;
1053 select decode(asset_inventory,1,0,1)
1054 into l_exp2
1055 from mtl_secondary_inventories msi,
1056 mtl_material_transactions mmt
1057 where msi.secondary_inventory_name = mmt.transfer_subinventory
1058 and msi.organization_id = mmt.transfer_organization_id
1059 and mmt.transaction_id = i_txn_id
1060 and mmt.organization_id = i_org_id;
1061 end if;
1062
1063 /* Changes for VMI. Adding Planning Transfer transaction */
1064 if (i_txn_action_id in (2,3,5,21,28)) then
1065 l_from_exp := l_exp1;
1066 l_to_exp := l_exp2;
1067 else
1068 l_from_exp := l_exp2;
1069 l_to_exp := l_exp1;
1070 end if;
1071
1072
1073 -- no changes necessary for asset->asset or exp->exp
1074 -- 1) asset->asset : no changes
1075 -- 2) exp->exp : no changes
1076 if (l_from_exp = l_to_exp) then
1077 GOTO out_arg_log;
1078
1079 -- update issue quantity only for exp->asset or asset->exp
1080 -- 3) exp->asset : increase qty
1081 -- 4) asset->exp : decrease qty
1082 elsif ((l_from_exp = 1 and l_to_exp = 0) -- exp->asset
1083 or (l_from_exp = 0 and l_to_exp = 1 and i_txn_action_id = 12) -- asset->expense intransit receipt
1084 -- added for bug #2531002
1085 ) then
1086 l_txn_qty := i_txn_qty * -1; -- increase qty for exp->asset,
1087 -- decrease qty for asset->exp intransit receipt
1088 l_issue_qty := i_txn_qty;
1089
1090 elsif (l_from_exp = 0 and l_to_exp = 1) then -- asset->exp
1091 l_txn_qty := i_txn_qty; -- decrease qty
1092 l_issue_qty := i_txn_qty * -1;
1093
1094 end if;
1095
1096
1097 l_stmt_num := 30;
1098 CSTPPWAC.cost_derived_txns(
1099 i_pac_period_id,
1100 i_cost_group_id,
1101 i_cost_type_id,
1102 i_txn_id,
1103 i_cost_layer_id,
1104 i_qty_layer_id,
1105 i_item_id,
1106 l_txn_qty,
1107 l_issue_qty,
1108 l_buy_qty,
1109 l_make_qty,
1110 i_txn_action_id,
1111 i_txn_src_type_id,
1112 0, -- disable i_exp_flag
1113 i_no_update_qty,
1114 i_cost_method,
1115 i_user_id,
1116 i_login_id,
1117 i_req_id,
1118 i_prg_appl_id,
1119 i_prg_id,
1120 i_txn_category,
1121 l_err_num,
1122 l_err_code,
1123 l_err_msg);
1124
1125 if (l_err_num <> 0) then
1126 -- Error occured
1127 raise fnd_api.g_exc_unexpected_error;
1128 end if;
1129
1130 /* --- start of auto log --- */
1131 <<out_arg_log>>
1132
1133 IF l_plog THEN
1134 fnd_log.string(
1135 fnd_log.level_procedure,
1136 l_module||'.'||l_stmt_num,
1137 'Exiting CSTPPWAC.sub_transfer with '||
1138 'O_Err_Num = '||O_Err_Num||','||
1139 'O_Err_Code = '||O_Err_Code||','||
1140 'O_Err_Msg = '||O_Err_Msg
1141 );
1142 END IF;
1143 /* --- end of auto log --- */
1144 EXCEPTION
1145 /* --- start of auto log --- */
1146 WHEN fnd_api.g_exc_unexpected_error THEN
1147 IF l_exceptionlog THEN
1148 fnd_msg_pub.add_exc_msg(
1149 p_pkg_name => 'CSTPPWAC',
1150 p_procedure_name => 'sub_transfer',
1151 p_error_text => 'An exception has occurred.'
1152 );
1153 fnd_log.string(
1154 fnd_log.level_exception,
1155 l_module||'.'||l_stmt_num,
1156 'An exception has occurred.'
1157 );
1158 END IF;
1159 o_err_num := l_err_num;
1160 o_err_code := l_err_code;
1161 o_err_msg := l_err_msg;
1162 WHEN OTHERS THEN
1163 ROLLBACK;
1164 IF l_uLog THEN
1165 fnd_message.set_name('BOM','CST_UNEXPECTED');
1166 fnd_message.set_token('SQLERRM',SQLERRM);
1167 fnd_msg_pub.add;
1168 fnd_log.message(
1169 fnd_log.level_unexpected,
1170 l_module||'.'||l_stmt_num,
1171 FALSE
1172 );
1173 END IF;
1174 o_err_num := SQLCODE;
1175 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
1176 /* --- end of auto log --- */
1177 END sub_transfer;
1178
1179 -- PROCEDURE
1180 -- interorg
1181 --
1182 procedure interorg(
1183 I_PAC_PERIOD_ID IN NUMBER,
1184 I_ORG_ID IN NUMBER,
1185 I_COST_GROUP_ID IN NUMBER,
1186 I_TXFR_COST_GROUP_ID IN NUMBER,
1187 I_COST_TYPE_ID IN NUMBER,
1188 I_COST_METHOD IN NUMBER,
1189 I_TXN_ID IN NUMBER,
1190 I_COST_LAYER_ID IN NUMBER,
1191 I_QTY_LAYER_ID IN NUMBER,
1192 I_ITEM_ID IN NUMBER,
1193 I_TXN_QTY IN NUMBER,
1194 I_ISSUE_QTY IN NUMBER,
1195 I_BUY_QTY IN NUMBER,
1196 I_MAKE_QTY IN NUMBER,
1197 I_TXN_ACTION_ID IN NUMBER,
1198 I_TXN_SRC_TYPE_ID IN NUMBER,
1199 I_EXP_FLAG IN NUMBER,
1200 I_INTERORG_REC IN NUMBER,
1201 I_USER_ID IN NUMBER,
1202 I_LOGIN_ID IN NUMBER,
1203 I_REQ_ID IN NUMBER,
1204 I_PRG_APPL_ID IN NUMBER,
1205 I_PRG_ID IN NUMBER,
1206 I_TXN_CATEGORY IN NUMBER,
1207 O_Err_Num OUT NOCOPY NUMBER,
1208 O_Err_Code OUT NOCOPY VARCHAR2,
1209 O_Err_Msg OUT NOCOPY VARCHAR2
1210 ) IS
1211 l_txn_qty NUMBER;
1212 l_issue_qty NUMBER;
1213 l_buy_qty NUMBER;
1214 l_make_qty NUMBER;
1215 l_no_update_qty NUMBER;
1216 l_exp1 NUMBER;
1217 l_err_num NUMBER;
1218 l_err_code VARCHAR2(240);
1219 l_err_msg VARCHAR2(240);
1220 l_stmt_num NUMBER;
1221
1222 /* --- start of auto log --- */
1223 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.interorg';
1224 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
1225 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
1226 fnd_log.TEST(fnd_log.level_unexpected, l_module);
1227 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
1228 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
1229 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
1230 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
1231 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1232
1233 /* --- end of auto log --- */
1234 BEGIN
1235 /* --- start of auto log --- */
1236 IF l_plog THEN
1237 fnd_log.string(
1238 fnd_log.level_procedure,
1239 l_module||'.'||l_stmt_num,
1240 'Entering CSTPPWAC.interorg with '||
1241 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
1242 'I_ORG_ID = '||I_ORG_ID||','||
1243 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
1244 'I_TXFR_COST_GROUP_ID = '||I_TXFR_COST_GROUP_ID||','||
1245 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
1246 'I_COST_METHOD = '||I_COST_METHOD||','||
1247 'I_TXN_ID = '||I_TXN_ID||','||
1248 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
1249 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
1250 'I_ITEM_ID = '||I_ITEM_ID||','||
1251 'I_TXN_QTY = '||I_TXN_QTY||','||
1252 'I_ISSUE_QTY = '||I_ISSUE_QTY||','||
1253 'I_BUY_QTY = '||I_BUY_QTY||','||
1254 'I_MAKE_QTY = '||I_MAKE_QTY||','||
1255 'I_TXN_ACTION_ID = '||I_TXN_ACTION_ID||','||
1256 'I_TXN_SRC_TYPE_ID = '||I_TXN_SRC_TYPE_ID||','||
1257 'I_EXP_FLAG = '||I_EXP_FLAG||','||
1258 'I_INTERORG_REC = '||I_INTERORG_REC||','||
1259 'I_USER_ID = '||I_USER_ID||','||
1260 'I_LOGIN_ID = '||I_LOGIN_ID||','||
1261 'I_REQ_ID = '||I_REQ_ID||','||
1262 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
1263 'I_PRG_ID = '||I_PRG_ID||','||
1264 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
1265 );
1266 END IF;
1267 /* --- end of auto log --- */
1268
1269 -- initialize local variables
1270 l_err_num := 0;
1271 l_err_code := '';
1272 l_err_msg := '';
1273 l_issue_qty := 0;
1274 l_buy_qty := 0;
1275 l_make_qty := 0;
1276 l_no_update_qty := 0;
1277
1278
1279 -- 1) The Intransit Shipment fob ship and processed by receiving CG
1280 -- 2) The Intransit Receipt fob receipt and processed by shipping CG
1281 -- 3) The logical transactions created for OPM - discrete transfers
1282 -- For above cases, it's in/out of Intransit, thus, always asset.
1283 if ( (i_cost_group_id = i_txfr_cost_group_id) OR (I_TXN_ACTION_ID in (15,22)) ) then
1284 l_exp1 := 0;
1285
1286 else
1287 select decode(asset_inventory,1,0,1)
1288 into l_exp1
1289 from mtl_secondary_inventories msi,
1290 mtl_material_transactions mmt
1291 where msi.secondary_inventory_name = mmt.subinventory_code
1292 and msi.organization_id = i_org_id
1293 and mmt.transaction_id = i_txn_id
1294 and mmt.organization_id = i_org_id;
1295
1296 end if;
1297
1298
1299 -- No Quantity or Cost changes for items in/out of expense sub
1300 if (l_exp1 = 1) then
1301 GOTO out_arg_log;
1302
1303 -- 1) The Direct Interorg Receipt
1304 -- 2) The Intransit Shipment fob ship and processed by receiving CG
1305 -- 3) The Intransit Rceipt fob receipt and processed by receiving CG
1306 elsif (i_interorg_rec = 1) then
1307
1308 -- reverse the sign of quantity, since shipment is processed by
1309 -- receiving costgroup
1310 if (i_txn_action_id = 21) then -- no need to reverse sign of txn act 15
1311 l_buy_qty := i_txn_qty * -1;
1312 l_txn_qty := i_txn_qty * -1;
1313 else
1314 l_buy_qty := i_txn_qty;
1315 l_txn_qty := i_txn_qty;
1316 end if;
1317
1318 CSTPPWAC.cost_owned_txns(
1319 i_pac_period_id,
1320 i_cost_group_id,
1321 i_cost_type_id,
1322 i_txn_id,
1323 i_cost_layer_id,
1324 i_qty_layer_id,
1325 i_item_id,
1326 l_txn_qty,
1327 l_issue_qty,
1328 l_buy_qty,
1329 l_make_qty,
1330 i_txn_action_id,
1331 i_cost_method,
1332 i_user_id,
1333 i_login_id,
1334 i_req_id,
1335 i_prg_appl_id,
1336 i_prg_id,
1337 i_txn_category,
1338 l_err_num,
1339 l_err_code,
1340 l_err_msg);
1341
1342 if (l_err_num <> 0) then
1343 -- Error occured
1344 raise fnd_api.g_exc_unexpected_error;
1345 end if;
1346
1347
1348 -- 1) The Direct Interorg Shipment
1349 -- 2) The Intransit Shipment fob ship and processed by sending CG
1350 -- 3) The Intransit Rceipt fob receipt and processed by sending CG
1351 else
1352
1353 -- reverse the sign of quantity, since receipt is processed by
1354 -- shipping costgroup
1355 if (i_txn_action_id in (12,22)) then -- INVCONV sikhanna
1356 l_issue_qty := i_txn_qty;
1357 l_txn_qty := i_txn_qty * -1;
1358 else
1359 l_issue_qty := i_txn_qty * -1;
1360 l_txn_qty := i_txn_qty;
1361 end if;
1362
1363 CSTPPWAC.cost_derived_txns(
1364 i_pac_period_id,
1365 i_cost_group_id,
1366 i_cost_type_id,
1367 i_txn_id,
1368 i_cost_layer_id,
1369 i_qty_layer_id,
1370 i_item_id,
1371 l_txn_qty,
1372 l_issue_qty,
1373 l_buy_qty,
1374 l_make_qty,
1375 i_txn_action_id,
1376 i_txn_src_type_id,
1377 l_exp1, --i_exp_flag,
1378 l_no_update_qty,
1379 i_cost_method,
1380 i_user_id,
1381 i_login_id,
1382 i_req_id,
1383 i_prg_appl_id,
1384 i_prg_id,
1385 i_txn_category,
1386 l_err_num,
1387 l_err_code,
1388 l_err_msg);
1389
1390 if (l_err_num <> 0) then
1391 -- Error occured
1392 raise fnd_api.g_exc_unexpected_error;
1393 end if;
1394
1395
1396 end if;
1397
1398 /* --- start of auto log --- */
1399 <<out_arg_log>>
1400
1401 IF l_plog THEN
1402 fnd_log.string(
1403 fnd_log.level_procedure,
1404 l_module||'.'||l_stmt_num,
1405 'Exiting CSTPPWAC.interorg with '||
1406 'O_Err_Num = '||O_Err_Num||','||
1407 'O_Err_Code = '||O_Err_Code||','||
1408 'O_Err_Msg = '||O_Err_Msg
1409 );
1410 END IF;
1411 /* --- end of auto log --- */
1412 EXCEPTION
1413 /* --- start of auto log --- */
1414 WHEN fnd_api.g_exc_unexpected_error THEN
1415 IF l_exceptionlog THEN
1416 fnd_msg_pub.add_exc_msg(
1417 p_pkg_name => 'CSTPPWAC',
1418 p_procedure_name => 'interorg',
1419 p_error_text => 'An exception has occurred.'
1420 );
1421 fnd_log.string(
1422 fnd_log.level_exception,
1423 l_module||'.'||l_stmt_num,
1424 'An exception has occurred.'
1425 );
1426 END IF;
1427 o_err_num := l_err_num;
1428 o_err_code := l_err_code;
1429 o_err_msg := l_err_msg;
1430 WHEN OTHERS THEN
1431 ROLLBACK;
1432 IF l_uLog THEN
1433 fnd_message.set_name('BOM','CST_UNEXPECTED');
1434 fnd_message.set_token('SQLERRM',SQLERRM);
1435 fnd_msg_pub.add;
1436 fnd_log.message(
1437 fnd_log.level_unexpected,
1438 l_module||'.'||l_stmt_num,
1439 FALSE
1440 );
1441 END IF;
1442 o_err_num := SQLCODE;
1443 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
1444 /* --- end of auto log --- */
1445 END interorg;
1446
1447 -- FUNCTION
1448 -- compute_pwac_cost
1449 --
1450 function compute_pwac_cost(
1451 I_PAC_PERIOD_ID IN NUMBER,
1452 I_ORG_ID IN NUMBER,
1453 I_COST_GROUP_ID IN NUMBER,
1454 I_COST_TYPE_ID IN NUMBER,
1455 I_TXN_ID IN NUMBER,
1456 I_COST_LAYER_ID IN NUMBER,
1457 I_PAC_RATES_ID IN NUMBER,
1458 I_ITEM_ID IN NUMBER,
1459 I_TXN_QTY IN NUMBER,
1460 I_TXN_ACTION_ID IN NUMBER,
1461 I_TXN_SRC_TYPE_ID IN NUMBER,
1462 I_INTERORG_REC IN NUMBER,
1463 I_ACROSS_CGS IN NUMBER,
1464 I_EXP_FLAG IN NUMBER,
1465 I_USER_ID IN NUMBER,
1466 I_LOGIN_ID IN NUMBER,
1467 I_REQ_ID IN NUMBER,
1468 I_PRG_APPL_ID IN NUMBER,
1469 I_PRG_ID IN NUMBER,
1470 I_TXN_CATEGORY IN NUMBER,
1471 O_Err_Num OUT NOCOPY NUMBER,
1472 O_Err_Code OUT NOCOPY VARCHAR2,
1473 O_Err_Msg OUT NOCOPY VARCHAR2
1474 )
1475 return integer IS
1476 l_ret_val NUMBER;
1477 l_level NUMBER;
1478 l_txn_cost_exist NUMBER;
1479 l_cost_details NUMBER;
1480 l_err_num NUMBER;
1481 l_err_code VARCHAR2(240);
1482 l_err_msg VARCHAR2(240);
1483 l_stmt_num NUMBER;
1484 l_earn_moh NUMBER;
1485 l_moh_org_id NUMBER;
1486 l_fob_point NUMBER;
1487 l_txfr_org_id NUMBER;
1488
1489 -- Variables defined for eAM Support in PAC
1490 l_eam_job NUMBER;
1491 l_zero_cost_flag NUMBER;
1492 l_return_status VARCHAR(1) := FND_API.G_RET_STS_SUCCESS;
1493 l_msg_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1494 l_msg_count NUMBER;
1495 l_msg_data VARCHAR2(8000) := '';
1496 l_api_message VARCHAR2(1000) := '';
1497
1498 /* --- start of auto log --- */
1499 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.compute_pwac_cost';
1500 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
1501 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
1502 fnd_log.TEST(fnd_log.level_unexpected, l_module);
1503 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
1504 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
1505 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
1506 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
1507 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1508
1509 /* --- end of auto log --- */
1510 BEGIN
1511 /* --- start of auto log --- */
1512 IF l_plog THEN
1513 fnd_log.string(
1514 fnd_log.level_procedure,
1515 l_module||'.'||l_stmt_num,
1516 'Entering CSTPPWAC.compute_pwac_cost with '||
1517 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
1518 'I_ORG_ID = '||I_ORG_ID||','||
1519 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
1520 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
1521 'I_TXN_ID = '||I_TXN_ID||','||
1522 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
1523 'I_PAC_RATES_ID = '||I_PAC_RATES_ID||','||
1524 'I_ITEM_ID = '||I_ITEM_ID||','||
1525 'I_TXN_QTY = '||I_TXN_QTY||','||
1526 'I_TXN_ACTION_ID = '||I_TXN_ACTION_ID||','||
1527 'I_TXN_SRC_TYPE_ID = '||I_TXN_SRC_TYPE_ID||','||
1528 'I_INTERORG_REC = '||I_INTERORG_REC||','||
1529 'I_ACROSS_CGS = '||I_ACROSS_CGS||','||
1530 'I_EXP_FLAG = '||I_EXP_FLAG||','||
1531 'I_USER_ID = '||I_USER_ID||','||
1532 'I_LOGIN_ID = '||I_LOGIN_ID||','||
1533 'I_REQ_ID = '||I_REQ_ID||','||
1534 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
1535 'I_PRG_ID = '||I_PRG_ID||','||
1536 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
1537 );
1538 END IF;
1539 /* --- end of auto log --- */
1540
1541 -- initialize local variables
1542 l_err_num := 0;
1543 l_err_code := '';
1544 l_err_msg := '';
1545 l_txn_cost_exist := 0;
1546 l_cost_details := 0;
1547 l_moh_org_id := i_org_id;
1548
1549 l_stmt_num := 10;
1550
1551 select count(*)
1552 into l_txn_cost_exist
1553 from mtl_pac_txn_cost_details
1554 where transaction_id = i_txn_id
1555 and cost_group_id = i_cost_group_id
1556 and pac_period_id = i_pac_period_id;
1557
1558 if (l_txn_cost_exist > 0) then
1559
1560 l_ret_val := 1;
1561 l_stmt_num := 20;
1562
1563 INSERT INTO mtl_pac_actual_cost_details (
1564 transaction_id,
1565 pac_period_id,
1566 cost_type_id,
1567 cost_group_id,
1568 cost_layer_id,
1569 cost_element_id,
1570 level_type,
1571 last_update_date,
1572 last_updated_by,
1573 creation_date,
1574 created_by,
1575 last_update_login,
1576 request_id,
1577 program_application_id,
1578 program_id,
1579 program_update_date,
1580 inventory_item_id,
1581 actual_cost,
1582 wip_variance, -- New Column added for BOM based WIP reqmnt
1583 insertion_flag,
1584 user_entered,
1585 transaction_costed_date,
1586 txn_category)
1587 SELECT
1588 i_txn_id,
1589 i_pac_period_id,
1590 i_cost_type_id,
1591 i_cost_group_id,
1592 i_cost_layer_id,
1593 mptcd.cost_element_id,
1594 mptcd.level_type,
1595 sysdate,
1596 i_user_id,
1597 sysdate,
1598 i_user_id,
1599 i_login_id,
1600 i_req_id,
1601 i_prg_appl_id,
1602 i_prg_id,
1603 sysdate,
1604 mptcd.inventory_item_id,
1605 mptcd.transaction_cost,
1606 mptcd.wip_variance,
1607 'Y',
1608 'N',
1609 SYSDATE,
1610 i_txn_category
1611 FROM mtl_pac_txn_cost_details mptcd
1612 WHERE transaction_id = i_txn_id
1613 AND pac_period_id = i_pac_period_id
1614 AND cost_group_id = i_cost_group_id;
1615
1616 else
1617 l_ret_val := 0;
1618
1619 /********************************************************************
1620 ** Create detail rows in MTL_PAC_ACTUAL_COST_DETAILS based on **
1621 ** records in CST_PAC_ITEM_COST_DETAILS. Since we are using **
1622 ** current average the actual cost, prior cost and new cost are **
1623 ** all the same. **
1624 ** If detail rows do not exist in CST_PAC_ITEM_COST_DETAILS, **
1625 ** we will insert a TL material 0 cost layer. **
1626 ********************************************************************/
1627
1628 l_stmt_num := 30;
1629
1630 select count(*)
1631 into l_cost_details
1632 from cst_pac_item_cost_details
1633 where cost_layer_id = i_cost_layer_id;
1634
1635
1636
1637 if (l_cost_details > 0) then
1638
1639 l_eam_job := 0;
1640
1641 IF (I_TXN_SRC_TYPE_ID = 5) THEN -- checking for eAM jobs
1642
1643 SELECT decode(WE.entity_type,6,1,7,1,0)
1644 INTO l_eam_job
1645 FROM mtl_material_transactions MMT, WIP_ENTITIES WE
1646 WHERE MMT.transaction_id = i_txn_id
1647 AND MMT.transaction_source_id = WE.wip_entity_id;
1648
1649 END IF;
1650
1651 IF (l_eam_job = 1) THEN
1652
1653 l_stmt_num := 35;
1654
1655 /* Check the zero cost flag for rebuildables */
1656 CST_Utility_PUB.get_zeroCostIssue_flag (
1657 p_api_version => 1.0,
1658 x_return_status => l_return_status,
1659 x_msg_count => l_msg_count,
1660 x_msg_data => l_msg_data,
1661 p_txn_id => i_txn_id,
1662 x_zero_cost_flag => l_zero_cost_flag
1663 );
1664
1665 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1666 l_err_num := -4321; -- Giving a number which is non-zero
1667 l_err_code := l_msg_count;
1668 l_err_msg := 'get_zeroCostIssue_flag returned unexpected error';
1669 RAISE fnd_api.g_exc_unexpected_error;
1670 END IF;
1671
1672 END IF;
1673
1674
1675 l_stmt_num := 40;
1676
1677
1678 INSERT INTO mtl_pac_actual_cost_details (
1679 transaction_id,
1680 pac_period_id,
1681 cost_type_id,
1682 cost_group_id,
1683 cost_layer_id,
1684 cost_element_id,
1685 level_type,
1686 last_update_date,
1687 last_updated_by,
1688 creation_date,
1689 created_by,
1690 last_update_login,
1691 request_id,
1692 program_application_id,
1693 program_id,
1694 program_update_date,
1695 inventory_item_id,
1696 actual_cost,
1697 insertion_flag,
1698 user_entered,
1699 transaction_costed_date,
1700 txn_category)
1701 SELECT
1702 i_txn_id,
1703 i_pac_period_id,
1704 i_cost_type_id,
1705 i_cost_group_id,
1706 i_cost_layer_id,
1707 cpicd.cost_element_id,
1708 cpicd.level_type,
1709 sysdate,
1710 i_user_id,
1711 sysdate,
1712 i_user_id,
1713 i_login_id,
1714 i_req_id,
1715 i_prg_appl_id,
1716 i_prg_id,
1717 sysdate,
1718 i_item_id,
1719 decode(l_zero_cost_flag, 1, 0, cpicd.item_cost), /* changed for eAM support in PAC. Added decode to handle rebuilds */
1720 'N',
1721 'N',
1722 SYSDATE,
1723 i_txn_category
1724 FROM cst_pac_item_cost_details cpicd
1725 WHERE cpicd.cost_layer_id = i_cost_layer_id;
1726
1727 else
1728 l_stmt_num := 50;
1729
1730 INSERT INTO mtl_pac_actual_cost_details (
1731 transaction_id,
1732 pac_period_id,
1733 cost_type_id,
1734 cost_group_id,
1735 cost_layer_id,
1736 cost_element_id,
1737 level_type,
1738 last_update_date,
1739 last_updated_by,
1740 creation_date,
1741 created_by,
1742 last_update_login,
1743 request_id,
1744 program_application_id,
1745 program_id,
1746 program_update_date,
1747 inventory_item_id,
1748 actual_cost,
1749 insertion_flag,
1750 user_entered,
1751 transaction_costed_date,
1752 txn_category)
1753 VALUES(
1754 i_txn_id,
1755 i_pac_period_id,
1756 i_cost_type_id,
1757 i_cost_group_id,
1758 i_cost_layer_id,
1759 1,
1760 1,
1761 sysdate,
1762 i_user_id,
1763 sysdate,
1764 i_user_id,
1765 i_login_id,
1766 i_req_id,
1767 i_prg_appl_id,
1768 i_prg_id,
1769 sysdate,
1770 i_item_id,
1771 0,
1772 'N',
1773 'N',
1774 SYSDATE,
1775 i_txn_category);
1776 end if;
1777
1778 end if;
1779
1780 -- Apply material overhead to certain txns which are asset item and
1781 -- asset subinventory
1782 if ((i_exp_flag <> 1) AND
1783 ((i_txn_action_id = 27 and i_txn_src_type_id = 1) or -- PO receipt
1784 (i_txn_action_id = 1 and i_txn_src_type_id = 1) or -- RTV
1785 (i_txn_action_id = 29 and i_txn_src_type_id = 1) or -- Delivery Adj
1786 (i_txn_action_id = 31 and i_txn_src_type_id = 5) or -- WIP completions
1787 (i_txn_action_id = 32 and i_txn_src_type_id = 5) or -- Assembly return
1788 (i_across_cgs = 1 and i_interorg_rec = 1) or -- Across CGs and Ownership changes
1789 (i_txn_action_id = 6 and i_txn_src_type_id in (1, 13))) -- Transfer to regular/Consigned
1790 ) then
1791
1792 -- Intransit Shipment transaction for FOB Shipment has to absorb MOH from
1793 -- transfer_organization_id in MMT instead of organization_id
1794 if (i_interorg_rec = 1 and i_txn_action_id = 21) then
1795
1796 l_stmt_num := 60;
1797 select nvl(mmt.fob_point, mip.fob_point), mmt.transfer_organization_id
1798 into l_fob_point, l_txfr_org_id
1799 from mtl_interorg_parameters mip, mtl_material_transactions mmt
1800 where mip.from_organization_id = i_org_id
1801 and mip.to_organization_id = mmt.transfer_organization_id
1802 and mmt.transaction_id = i_txn_id;
1803
1804 if (l_fob_point = 1) then
1805 l_moh_org_id := l_txfr_org_id;
1806 end if;
1807
1808 fnd_file.put_line (fnd_file.log, 'moh org: ' || l_moh_org_id);
1809 end if;
1810
1811 l_stmt_num := 70;
1812 CST_MOHRULES_PUB.apply_moh ( p_api_version => 1.0,
1813 p_organization_id => l_moh_org_id,
1814 p_earn_moh => l_earn_moh,
1815 p_txn_id => i_txn_id,
1816 p_item_id => i_item_id,
1817 x_return_status => l_return_status,
1818 x_msg_count => l_msg_count,
1819 x_msg_data => l_err_msg
1820 );
1821 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1822 -- Error occured
1823 RAISE fnd_api.g_exc_unexpected_error;
1824 END IF;
1825
1826 IF (l_earn_moh = 1) THEN
1827
1828 l_level := 1;
1829
1830 l_stmt_num := 80;
1831 CSTPPWAC.apply_material_ovhd(
1832 i_pac_period_id,
1833 l_moh_org_id,
1834 i_cost_group_id,
1835 i_cost_type_id,
1836 i_txn_id,
1837 i_cost_layer_id,
1838 i_pac_rates_id,
1839 i_item_id,
1840 i_txn_qty,
1841 l_level,
1842 i_user_id,
1843 i_login_id,
1844 i_req_id,
1845 i_prg_appl_id,
1846 i_prg_id,
1847 i_txn_category,
1848 l_err_num,
1849 l_err_code,
1850 l_err_msg);
1851
1852 if (l_err_num <> 0) then
1853 -- Error occured
1854 raise fnd_api.g_exc_unexpected_error;
1855 end if;
1856 l_ret_val := 1;
1857
1858 end if;
1859 end if;
1860
1861 /* --- start of auto log --- */
1862 <<out_arg_log>>
1863
1864 IF l_plog THEN
1865 fnd_log.string(
1866 fnd_log.level_procedure,
1867 l_module||'.'||l_stmt_num,
1868 'Exiting CSTPPWAC.compute_pwac_cost with '||
1869 'O_Err_Num = '||O_Err_Num||','||
1870 'O_Err_Code = '||O_Err_Code||','||
1871 'O_Err_Msg = '||O_Err_Msg
1872 );
1873 END IF;
1874 /* --- end of auto log --- */
1875
1876 return l_ret_val;
1877
1878 EXCEPTION
1879 /* --- start of auto log --- */
1880 WHEN fnd_api.g_exc_unexpected_error THEN
1881 IF l_exceptionlog THEN
1882 fnd_msg_pub.add_exc_msg(
1883 p_pkg_name => 'CSTPPWAC',
1884 p_procedure_name => 'compute_pwac_cost',
1885 p_error_text => 'An exception has occurred.'
1886 );
1887 fnd_log.string(
1888 fnd_log.level_exception,
1889 l_module||'.'||l_stmt_num,
1890 'An exception has occurred.'
1891 );
1892 END IF;
1893 o_err_num := l_err_num;
1894 o_err_code := l_err_code;
1895 o_err_msg := l_err_msg;
1896 return l_ret_val;
1897 WHEN OTHERS THEN
1898 ROLLBACK;
1899 IF l_uLog THEN
1900 fnd_message.set_name('BOM','CST_UNEXPECTED');
1901 fnd_message.set_token('SQLERRM',SQLERRM);
1902 fnd_msg_pub.add;
1903 fnd_log.message(
1904 fnd_log.level_unexpected,
1905 l_module||'.'||l_stmt_num,
1906 FALSE
1907 );
1908 END IF;
1909 o_err_num := SQLCODE;
1910 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
1911 return l_ret_val;
1912 /* --- end of auto log --- */
1913 END compute_pwac_cost;
1914
1915 -- PROCEDURE
1916 -- apply_material_ovhd Applying this level material overhead based
1917 -- on the pre-defined rates in the material
1918 --
1919 procedure apply_material_ovhd(
1920 I_PAC_PERIOD_ID IN NUMBER,
1921 I_ORG_ID IN NUMBER,
1922 I_COST_GROUP_ID IN NUMBER,
1923 I_COST_TYPE_ID IN NUMBER,
1924 I_TXN_ID IN NUMBER,
1925 I_COST_LAYER_ID IN NUMBER,
1926 I_PAC_RATES_ID IN NUMBER,
1927 I_ITEM_ID IN NUMBER,
1928 I_TXN_QTY IN NUMBER,
1929 I_LEVEL IN NUMBER,
1930 I_USER_ID IN NUMBER,
1931 I_LOGIN_ID IN NUMBER,
1932 I_REQ_ID IN NUMBER,
1933 I_PRG_APPL_ID IN NUMBER,
1934 I_PRG_ID IN NUMBER,
1935 I_TXN_CATEGORY IN NUMBER,
1936 O_Err_Num OUT NOCOPY NUMBER,
1937 O_Err_Code OUT NOCOPY VARCHAR2,
1938 O_Err_Msg OUT NOCOPY VARCHAR2
1939 ) IS
1940 l_mpacd_mat_ovhds NUMBER;
1941 l_mpcs_mat_ovhds NUMBER;
1942 l_item_cost NUMBER;
1943 l_res_id NUMBER;
1944
1945 l_err_num NUMBER;
1946 l_err_code VARCHAR2(240);
1947 l_err_msg VARCHAR2(240);
1948 l_stmt_num NUMBER;
1949
1950 /* --- start of auto log --- */
1951 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.apply_material_ovhd';
1952 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
1953 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
1954 fnd_log.TEST(fnd_log.level_unexpected, l_module);
1955 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
1956 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
1957 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
1958 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
1959 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
1960
1961 /* --- end of auto log --- */
1962 BEGIN
1963 /* --- start of auto log --- */
1964 IF l_plog THEN
1965 fnd_log.string(
1966 fnd_log.level_procedure,
1967 l_module||'.'||l_stmt_num,
1968 'Entering CSTPPWAC.apply_material_ovhd with '||
1969 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
1970 'I_ORG_ID = '||I_ORG_ID||','||
1971 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
1972 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
1973 'I_TXN_ID = '||I_TXN_ID||','||
1974 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
1975 'I_PAC_RATES_ID = '||I_PAC_RATES_ID||','||
1976 'I_ITEM_ID = '||I_ITEM_ID||','||
1977 'I_TXN_QTY = '||I_TXN_QTY||','||
1978 'I_LEVEL = '||I_LEVEL||','||
1979 'I_USER_ID = '||I_USER_ID||','||
1980 'I_LOGIN_ID = '||I_LOGIN_ID||','||
1981 'I_REQ_ID = '||I_REQ_ID||','||
1982 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
1983 'I_PRG_ID = '||I_PRG_ID || ',' ||
1984 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
1985 );
1986 END IF;
1987 /* --- end of auto log --- */
1988
1989 -- initialize local variables
1990 l_err_num := 0;
1991 l_err_code := '';
1992 l_err_msg := '';
1993
1994 l_stmt_num := 10;
1995
1996 select count(*)
1997 into l_mpacd_mat_ovhds
1998 from mtl_pac_actual_cost_details mpacd
1999 where transaction_id = i_txn_id
2000 and cost_layer_id = i_cost_layer_id
2001 and cost_element_id = 2
2002 and level_type = decode(i_level, 1,1,level_type);
2003
2004 l_stmt_num := 20;
2005
2006 select nvl(sum(actual_cost),0)
2007 into l_item_cost
2008 from mtl_pac_actual_cost_details mpacd
2009 where transaction_id = i_txn_id
2010 and cost_layer_id = i_cost_layer_id;
2011
2012 l_stmt_num := 30;
2013
2014 INSERT INTO mtl_pac_cost_subelements(
2015 transaction_id,
2016 pac_period_id,
2017 cost_type_id,
2018 cost_group_id,
2019 cost_element_id,
2020 level_type,
2021 resource_id,
2022 last_update_date,
2023 last_updated_by,
2024 creation_date,
2025 created_by,
2026 last_update_login,
2027 request_id,
2028 program_application_id,
2029 program_id,
2030 program_update_date,
2031 actual_cost,
2032 user_entered)
2033 SELECT i_txn_id,
2034 i_pac_period_id,
2035 i_cost_type_id,
2036 i_cost_group_id,
2037 cicd.cost_element_id,
2038 cicd.level_type,
2039 cicd.resource_id,
2040 sysdate,
2041 i_user_id,
2042 sysdate,
2043 i_user_id,
2044 i_login_id,
2045 i_req_id,
2046 i_prg_appl_id,
2047 i_prg_id,
2048 sysdate,
2049 decode(cicd.basis_type, 1, cicd.usage_rate_or_amount,
2050 2, cicd.usage_rate_or_amount/abs(i_txn_qty),
2051 5, cicd.usage_rate_or_amount * l_item_cost,
2052 6, cicd.usage_rate_or_amount * cicd.basis_factor,0),
2053 'N'
2054 FROM cst_item_cost_details cicd
2055 WHERE inventory_item_id = i_item_id
2056 AND organization_id = i_org_id
2057 AND cost_type_id = i_pac_rates_id
2058 AND basis_type in (1,2,5,6)
2059 AND cost_element_id = 2
2060 AND level_type = decode(i_level, 1,1,level_type);
2061
2062 l_stmt_num := 40;
2063
2064 select count(*)
2065 into l_mpcs_mat_ovhds
2066 from mtl_pac_cost_subelements
2067 where transaction_id = i_txn_id
2068 and pac_period_id = i_pac_period_id
2069 and cost_group_id = i_cost_group_id
2070 and cost_element_id = 2
2071 and level_type = decode(i_level, 1,1,level_type);
2072
2073 l_stmt_num := 50;
2074
2075 if (l_mpcs_mat_ovhds > 0) then
2076
2077 -- Material Overhead element cost already exists in MPACD,
2078 -- thus, add all additional material overhead.
2079 if (l_mpacd_mat_ovhds > 0) then
2080
2081 l_stmt_num := 60;
2082
2083 UPDATE mtl_pac_actual_cost_details mpacd
2084 SET (last_update_date,
2085 last_updated_by,
2086 creation_date,
2087 created_by,
2088 last_update_login,
2089 request_id,
2090 program_application_id,
2091 program_id,
2092 program_update_date,
2093 actual_cost,
2094 transaction_costed_date) =
2095 (SELECT sysdate,
2096 i_user_id,
2097 sysdate,
2098 i_user_id,
2099 i_login_id,
2100 i_req_id,
2101 i_prg_appl_id,
2102 i_prg_id,
2103 sysdate,
2104 sum(mpcs.actual_cost) + mpacd.actual_cost,
2105 sysdate
2106 FROM mtl_pac_cost_subelements mpcs
2107 WHERE mpcs.transaction_id = i_txn_id
2108 AND mpcs.pac_period_id = i_pac_period_id
2109 AND mpcs.cost_group_id = i_cost_group_id
2110 AND mpcs.cost_element_id = 2)
2111 WHERE mpacd.transaction_id = i_txn_id
2112 AND mpacd.cost_group_id = i_cost_group_id
2113 AND mpacd.cost_layer_id = i_cost_layer_id
2114 AND mpacd.cost_element_id = 2
2115 AND mpacd.level_type = 1;
2116
2117 else
2118
2119 l_stmt_num := 70;
2120
2121 INSERT INTO mtl_pac_actual_cost_details(
2122 transaction_id,
2123 pac_period_id,
2124 cost_type_id,
2125 cost_group_id,
2126 cost_layer_id,
2127 cost_element_id,
2128 level_type,
2129 last_update_date,
2130 last_updated_by,
2131 creation_date,
2132 created_by,
2133 last_update_login,
2134 request_id,
2135 program_application_id,
2136 program_id,
2137 program_update_date,
2138 inventory_item_id,
2139 actual_cost,
2140 insertion_flag,
2141 user_entered,
2142 transaction_costed_date,
2143 txn_category)
2144 SELECT
2145 i_txn_id,
2146 i_pac_period_id,
2147 i_cost_type_id,
2148 i_cost_group_id,
2149 i_cost_layer_id,
2150 2,
2151 1,
2152 sysdate,
2153 i_user_id,
2154 sysdate,
2155 i_user_id,
2156 i_login_id,
2157 i_req_id,
2158 i_prg_appl_id,
2159 i_prg_id,
2160 sysdate,
2161 i_item_id,
2162 sum(actual_cost),
2163 'Y',
2164 'N',
2165 SYSDATE,
2166 i_txn_category
2167 FROM mtl_pac_cost_subelements
2168 WHERE transaction_id = i_txn_id
2169 AND pac_period_id = i_pac_period_id
2170 AND cost_group_id = i_cost_group_id
2171 AND cost_element_id = 2;
2172
2173 end if;
2174 end if;
2175
2176
2177
2178 /* --- start of auto log --- */
2179 <<out_arg_log>>
2180
2181 IF l_plog THEN
2182 fnd_log.string(
2183 fnd_log.level_procedure,
2184 l_module||'.'||l_stmt_num,
2185 'Exiting CSTPPWAC.apply_material_ovhd with '||
2186 'O_Err_Num = '||O_Err_Num||','||
2187 'O_Err_Code = '||O_Err_Code||','||
2188 'O_Err_Msg = '||O_Err_Msg
2189 );
2190 END IF;
2191 /* --- end of auto log --- */
2192 EXCEPTION
2193 WHEN OTHERS THEN
2194 ROLLBACK;
2195 IF l_uLog THEN
2196 fnd_message.set_name('BOM','CST_UNEXPECTED');
2197 fnd_message.set_token('SQLERRM',SQLERRM);
2198 fnd_msg_pub.add;
2199 fnd_log.message(
2200 fnd_log.level_unexpected,
2201 l_module||'.'||l_stmt_num,
2202 FALSE
2203 );
2204 END IF;
2205 o_err_num := SQLCODE;
2206 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
2207 /* --- end of auto log --- */
2208 END apply_material_ovhd;
2209
2210 -- PROCEDURE
2211 -- current_pwac_cost
2212 --
2213 procedure current_pwac_cost(
2214 I_COST_LAYER_ID IN NUMBER,
2215 I_QTY_LAYER_ID IN NUMBER,
2216 I_TXN_QTY IN NUMBER,
2217 I_ISSUE_QTY IN NUMBER,
2218 I_BUY_QTY IN NUMBER,
2219 I_MAKE_QTY IN NUMBER,
2220 I_TXN_ACTION_ID IN NUMBER,
2221 I_EXP_FLAG IN NUMBER,
2222 I_NO_UPDATE_QTY IN NUMBER,
2223 I_USER_ID IN NUMBER,
2224 I_LOGIN_ID IN NUMBER,
2225 I_REQ_ID IN NUMBER,
2226 I_PRG_APPL_ID IN NUMBER,
2227 I_PRG_ID IN NUMBER,
2228 I_TXN_CATEGORY IN NUMBER,
2229 I_TXN_ID IN NUMBER,
2230 I_ITEM_ID IN NUMBER,
2231 O_Err_Num OUT NOCOPY NUMBER,
2232 O_Err_Code OUT NOCOPY VARCHAR2,
2233 O_Err_Msg OUT NOCOPY VARCHAR2
2234 ) IS
2235 l_err_num NUMBER;
2236 l_err_code VARCHAR2(240);
2237 l_err_msg VARCHAR2(240);
2238 l_stmt_num NUMBER;
2239
2240 /* --- start of auto log --- */
2241 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.current_pwac_cost';
2242 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
2243 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
2244 fnd_log.TEST(fnd_log.level_unexpected, l_module);
2245 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
2246 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
2247 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
2248 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
2249 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
2250
2251 /* --- end of auto log --- */
2252 BEGIN
2253 /* --- start of auto log --- */
2254 IF l_plog THEN
2255 fnd_log.string(
2256 fnd_log.level_procedure,
2257 l_module||'.'||l_stmt_num,
2258 'Entering CSTPPWAC.current_pwac_cost with '||
2259 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
2260 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
2261 'I_TXN_QTY = '||I_TXN_QTY||','||
2262 'I_ISSUE_QTY = '||I_ISSUE_QTY||','||
2263 'I_BUY_QTY = '||I_BUY_QTY||','||
2264 'I_MAKE_QTY = '||I_MAKE_QTY||','||
2265 'I_TXN_ACTION_ID = '||I_TXN_ACTION_ID||','||
2266 'I_EXP_FLAG = '||I_EXP_FLAG||','||
2267 'I_NO_UPDATE_QTY = '||I_NO_UPDATE_QTY||','||
2268 'I_USER_ID = '||I_USER_ID||','||
2269 'I_LOGIN_ID = '||I_LOGIN_ID||','||
2270 'I_REQ_ID = '||I_REQ_ID||','||
2271 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
2272 'I_PRG_ID = '||I_PRG_ID||','||
2273 'I_TXN_CATEGORY = '||I_TXN_CATEGORY||','||
2274 'I_TXN_ID = '||I_TXN_ID||','||
2275 'I_ITEM_ID = '||I_ITEM_ID
2276 );
2277 END IF;
2278 /* --- end of auto log --- */
2279
2280 -- initialize local variables
2281 l_err_num := 0;
2282 l_err_code := '';
2283 l_err_msg := '';
2284
2285
2286 if ((i_txn_action_id = 30) or (i_no_update_qty = 1) or (i_exp_flag = 1)) then
2287 GOTO out_arg_log;
2288 else
2289
2290
2291 -- Insert quantity and balance details into PL/SQL table for each
2292 -- item-cost_element_id-level_type combination in mpacd.
2293
2294 l_stmt_num := 10;
2295 FOR x IN
2296 (SELECT actual_cost, cost_element_id, level_type
2297 FROM mtl_pac_actual_cost_details mpacd
2298 WHERE mpacd.cost_layer_id = i_cost_layer_id
2299 AND mpacd.transaction_id = i_txn_id)
2300 LOOP
2301 DECLARE
2302 l_index NUMBER;
2303 l_count NUMBER;
2304 BEGIN
2305 l_index := -1;
2306
2307 -- Check if the item-cost_element_id-level_type combination exists
2308 IF (CSTPPINV.l_item_start_index_tbl.EXISTS (i_item_id)) THEN
2309 FOR l_count IN (CSTPPINV.l_item_start_index_tbl(i_item_id))..(CSTPPINV.l_item_end_index_tbl(i_item_id))
2310 LOOP
2311 IF (CSTPPINV.l_item_id_tbl(l_count) = i_item_id AND
2312 CSTPPINV.l_cost_element_id_tbl(l_count) = x.cost_element_id AND
2313 CSTPPINV.l_level_type_tbl(l_count) = x.level_type) THEN
2314 l_index := l_count;
2315 END IF;
2316 END LOOP;
2317 ELSE
2318 CSTPPINV.l_item_start_index_tbl (i_item_id) := CSTPPINV.l_item_id_tbl.COUNT + 1;
2319 CSTPPINV.l_item_end_index_tbl (i_item_id) := CSTPPINV.l_item_id_tbl.COUNT + 1;
2320 END IF;
2321 IF (l_index = -1) THEN
2322 -- Combination not found: Insert intp PL/SQL table
2323 l_index := CSTPPINV.l_item_id_tbl.COUNT + 1;
2324 CSTPPINV.l_item_id_tbl(l_index) := i_item_id;
2325 CSTPPINV.l_cost_layer_id_tbl(l_index) := i_cost_layer_id;
2326 CSTPPINV.l_qty_layer_id_tbl(l_index) := i_qty_layer_id;
2327 CSTPPINV.l_cost_element_id_tbl(l_index) := x.cost_element_id;
2328 CSTPPINV.l_level_type_tbl(l_index) := x.level_type;
2329 CSTPPINV.l_txn_category_tbl (l_index) := i_txn_category;
2330
2331 CSTPPINV.l_item_balance_tbl(l_index) := x.actual_cost * i_txn_qty;
2332 CSTPPINV.l_make_balance_tbl(l_index) := x.actual_cost * i_make_qty;
2333 CSTPPINV.l_buy_balance_tbl(l_index) := x.actual_cost * i_buy_qty;
2334
2335 CSTPPINV.l_item_end_index_tbl (i_item_id) := l_index;
2336 ELSE
2337 -- Combination found: Update balance in PL/SQL table.
2338 CSTPPINV.l_item_balance_tbl(l_index) := (x.actual_cost * i_txn_qty) + CSTPPINV.l_item_balance_tbl(l_index);
2339 CSTPPINV.l_make_balance_tbl(l_index) := (x.actual_cost * i_make_qty) + CSTPPINV.l_make_balance_tbl(l_index);
2340 CSTPPINV.l_buy_balance_tbl(l_index) := (x.actual_cost * i_buy_qty) + CSTPPINV.l_buy_balance_tbl(l_index);
2341 END IF;
2342 END;
2343 END LOOP;
2344
2345 -- Insert/Update quantities in PL/SQL tables
2346 IF CSTPPINV.l_item_quantity_tbl.EXISTS (i_item_id) THEN
2347 CSTPPINV.l_item_quantity_tbl(i_item_id) := i_txn_qty + CSTPPINV.l_item_quantity_tbl(i_item_id);
2348 ELSE
2349 CSTPPINV.l_item_quantity_tbl(i_item_id):= i_txn_qty;
2350 END IF;
2351
2352 IF CSTPPINV.l_make_quantity_tbl.EXISTS (i_item_id) THEN
2353 CSTPPINV.l_make_quantity_tbl(i_item_id) := i_make_qty + CSTPPINV.l_make_quantity_tbl(i_item_id);
2354 ELSE
2355 CSTPPINV.l_make_quantity_tbl(i_item_id):= i_make_qty;
2356 END IF;
2357
2358 IF CSTPPINV.l_issue_quantity_tbl.EXISTS (i_item_id) THEN
2359 CSTPPINV.l_issue_quantity_tbl (i_item_id):= i_issue_qty + CSTPPINV.l_issue_quantity_tbl(i_item_id);
2360 ELSE
2361 CSTPPINV.l_issue_quantity_tbl(i_item_id):= i_issue_qty;
2362 END IF;
2363
2364 IF CSTPPINV.l_buy_quantity_tbl.EXISTS (i_item_id) THEN
2365 CSTPPINV.l_buy_quantity_tbl (i_item_id):= i_buy_qty + CSTPPINV.l_buy_quantity_tbl(i_item_id);
2366 ELSE
2367 CSTPPINV.l_buy_quantity_tbl(i_item_id):= i_buy_qty;
2368 END IF;
2369 end if;
2370
2371 /* --- start of auto log --- */
2372 <<out_arg_log>>
2373
2374 IF l_plog THEN
2375 fnd_log.string(
2376 fnd_log.level_procedure,
2377 l_module||'.'||l_stmt_num,
2378 'Exiting CSTPPWAC.current_pwac_cost with '||
2379 'O_Err_Num = '||O_Err_Num||','||
2380 'O_Err_Code = '||O_Err_Code||','||
2381 'O_Err_Msg = '||O_Err_Msg
2382 );
2383 END IF;
2384 /* --- end of auto log --- */
2385 EXCEPTION
2386 /* --- start of auto log --- */
2387 WHEN fnd_api.g_exc_unexpected_error THEN
2388 IF l_exceptionlog THEN
2389 fnd_msg_pub.add_exc_msg(
2390 p_pkg_name => 'CSTPPWAC',
2391 p_procedure_name => 'current_pwac_cost',
2392 p_error_text => 'An exception has occurred.'
2393 );
2394 fnd_log.string(
2395 fnd_log.level_exception,
2396 l_module||'.'||l_stmt_num,
2397 'An exception has occurred.'
2398 );
2399 END IF;
2400 o_err_num := l_err_num;
2401 o_err_code := l_err_code;
2402 o_err_msg := l_err_msg;
2403 WHEN OTHERS THEN
2404 ROLLBACK;
2405 IF l_uLog THEN
2406 fnd_message.set_name('BOM','CST_UNEXPECTED');
2407 fnd_message.set_token('SQLERRM',SQLERRM);
2408 fnd_msg_pub.add;
2409 fnd_log.message(
2410 fnd_log.level_unexpected,
2411 l_module||'.'||l_stmt_num,
2412 FALSE
2413 );
2414 END IF;
2415 o_err_num := SQLCODE;
2416 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
2417 /* --- end of auto log --- */
2418 END current_pwac_cost;
2419
2420 -- PROCEDURE
2421 -- calc_pwac_cost
2422 --
2423 procedure calc_pwac_cost(
2424 I_PAC_PERIOD_ID IN NUMBER,
2425 I_COST_GROUP_ID IN NUMBER,
2426 I_COST_TYPE_ID IN NUMBER,
2427 I_TXN_ID IN NUMBER,
2428 I_COST_LAYER_ID IN NUMBER,
2429 I_QTY_LAYER_ID IN NUMBER,
2430 I_ITEM_ID IN NUMBER,
2431 I_TXN_QTY IN NUMBER,
2432 I_ISSUE_QTY IN NUMBER,
2433 I_BUY_QTY IN NUMBER,
2434 I_MAKE_QTY IN NUMBER,
2435 I_USER_ID IN NUMBER,
2436 I_LOGIN_ID IN NUMBER,
2437 I_REQ_ID IN NUMBER,
2438 I_PRG_APPL_ID IN NUMBER,
2439 I_PRG_ID IN NUMBER,
2440 I_TXN_CATEGORY IN NUMBER,
2441 O_Err_Num OUT NOCOPY NUMBER,
2442 O_Err_Code OUT NOCOPY VARCHAR2,
2443 O_Err_Msg OUT NOCOPY VARCHAR2
2444 ) IS
2445 l_cur_onhand NUMBER;
2446 l_cur_buy_qty NUMBER;
2447 l_cur_make_qty NUMBER;
2448 l_new_onhand NUMBER;
2449 l_new_buy_qty NUMBER;
2450 l_new_make_qty NUMBER;
2451
2452 l_err_num NUMBER;
2453 l_err_code VARCHAR2(240);
2454 l_err_msg VARCHAR2(240);
2455 l_stmt_num NUMBER;
2456
2457 /* --- start of auto log --- */
2458 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.calc_pwac_cost';
2459 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
2460 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
2461 fnd_log.TEST(fnd_log.level_unexpected, l_module);
2462 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
2463 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
2464 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
2465 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
2466 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
2467
2468 /* --- end of auto log --- */
2469 BEGIN
2470 /* --- start of auto log --- */
2471 IF l_plog THEN
2472 fnd_log.string(
2473 fnd_log.level_procedure,
2474 l_module||'.'||l_stmt_num,
2475 'Entering CSTPPWAC.calc_pwac_cost with '||
2476 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
2477 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
2478 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
2479 'I_TXN_ID = '||I_TXN_ID||','||
2480 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
2481 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
2482 'I_ITEM_ID = '||I_ITEM_ID||','||
2483 'I_TXN_QTY = '||I_TXN_QTY||','||
2484 'I_ISSUE_QTY = '||I_ISSUE_QTY||','||
2485 'I_BUY_QTY = '||I_BUY_QTY||','||
2486 'I_MAKE_QTY = '||I_MAKE_QTY||','||
2487 'I_USER_ID = '||I_USER_ID||','||
2488 'I_LOGIN_ID = '||I_LOGIN_ID||','||
2489 'I_REQ_ID = '||I_REQ_ID||','||
2490 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
2491 'I_PRG_ID = '||I_PRG_ID||','||
2492 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
2493 );
2494 END IF;
2495 /* --- end of auto log --- */
2496
2497 -- initialize local variables
2498 l_err_num := 0;
2499 l_err_code := '';
2500 l_err_msg := '';
2501 l_cur_onhand := 0;
2502 l_cur_buy_qty := 0;
2503 l_cur_make_qty := 0;
2504 l_new_onhand := 0;
2505 l_new_buy_qty := 0;
2506 l_new_make_qty := 0;
2507
2508
2509 /********************************************************************
2510 ** Update mtl_pac_actual_cost_details and update the prior cost **
2511 ** to the current average for the elements that exists and insert **
2512 ** in to mtl_pac_actual_cost_details the current average cost for **
2513 ** the elements that do not exist. **
2514 ********************************************************************/
2515
2516 l_stmt_num := 10;
2517
2518 INSERT INTO mtl_pac_actual_cost_details (
2519 transaction_id,
2520 pac_period_id,
2521 cost_type_id,
2522 cost_group_id,
2523 cost_layer_id,
2524 cost_element_id,
2525 level_type,
2526 last_update_date,
2527 last_updated_by,
2528 creation_date,
2529 created_by,
2530 last_update_login,
2531 request_id,
2532 program_application_id,
2533 program_id,
2534 program_update_date,
2535 inventory_item_id,
2536 actual_cost,
2537 insertion_flag,
2538 user_entered,
2539 transaction_costed_date,
2540 txn_category)
2541 SELECT i_txn_id,
2542 i_pac_period_id,
2543 i_cost_type_id,
2544 i_cost_group_id,
2545 i_cost_layer_id,
2546 cpicd.cost_element_id,
2547 cpicd.level_type,
2548 sysdate,
2549 i_user_id,
2550 sysdate,
2551 i_user_id,
2552 i_login_id,
2553 i_req_id,
2554 i_prg_appl_id,
2555 i_prg_id,
2556 sysdate,
2557 i_item_id,
2558 0,
2559 'N',
2560 'N',
2561 sysdate,
2562 i_txn_category
2563 FROM cst_pac_item_cost_details cpicd
2564 WHERE cost_layer_id = i_cost_layer_id
2565 AND NOT EXISTS
2566 (SELECT 'this detail is not in mpacd already'
2567 FROM mtl_pac_actual_cost_details mpacd
2568 WHERE mpacd.transaction_id = i_txn_id
2569 AND mpacd.cost_group_id = i_cost_group_id
2570 AND mpacd.cost_layer_id = i_cost_layer_id
2571 AND mpacd.cost_element_id = cpicd.cost_element_id
2572 AND mpacd.level_type = cpicd.level_type);
2573
2574 l_stmt_num := 20;
2575 FOR x IN
2576 (SELECT actual_cost, cost_element_id, level_type
2577 FROM mtl_pac_actual_cost_details mpacd
2578 WHERE mpacd.cost_layer_id = i_cost_layer_id
2579 AND mpacd.transaction_id = i_txn_id)
2580 LOOP
2581 DECLARE
2582 l_index NUMBER;
2583 l_count NUMBER;
2584 BEGIN
2585 l_index := -1;
2586 IF (CSTPPINV.l_item_start_index_tbl.EXISTS (i_item_id)) THEN /* item already exists */
2587 FOR l_count IN (CSTPPINV.l_item_start_index_tbl(i_item_id))..(CSTPPINV.l_item_end_index_tbl(i_item_id))
2588 LOOP
2589 IF (CSTPPINV.l_item_id_tbl(l_count) = i_item_id AND
2590 CSTPPINV.l_cost_element_id_tbl(l_count) = x.cost_element_id AND
2591 CSTPPINV.l_level_type_tbl(l_count) = x.level_type) THEN
2592 l_index := l_count;
2593 END IF;
2594 END LOOP;
2595 ELSE /* new item */
2596 CSTPPINV.l_item_start_index_tbl (i_item_id) := CSTPPINV.l_item_id_tbl.COUNT + 1;
2597 CSTPPINV.l_item_end_index_tbl (i_item_id) := CSTPPINV.l_item_id_tbl.COUNT + 1;
2598 END IF;
2599 IF (l_index = -1) THEN
2600
2601 /*********************************************************************
2602 Item-cost_element-level_type combination not found: Insert into
2603 PL/SQL table. Quantity is maintained for each item, whereas all
2604 other tables are maintained for each item-cost_element-level_type
2605 **********************************************************************/
2606
2607 l_index := CSTPPINV.l_item_id_tbl.COUNT + 1;
2608 CSTPPINV.l_item_id_tbl(l_index) := i_item_id;
2609 CSTPPINV.l_cost_layer_id_tbl(l_index) := i_cost_layer_id;
2610 CSTPPINV.l_qty_layer_id_tbl(l_index) := i_qty_layer_id;
2611 CSTPPINV.l_cost_element_id_tbl(l_index) := x.cost_element_id;
2612 CSTPPINV.l_level_type_tbl(l_index) := x.level_type;
2613 CSTPPINV.l_txn_category_tbl (l_index) := i_txn_category;
2614
2615 CSTPPINV.l_item_balance_tbl(l_index) := x.actual_cost * i_txn_qty;
2616 CSTPPINV.l_make_balance_tbl(l_index) := x.actual_cost * i_make_qty;
2617 CSTPPINV.l_buy_balance_tbl(l_index) := x.actual_cost * i_buy_qty;
2618
2619 CSTPPINV.l_item_end_index_tbl (i_item_id) := l_index;
2620 ELSE
2621
2622 /*************************************************************************
2623 Update/Insert total quantity, make quantity, buy quantity for each item
2624 **************************************************************************/
2625
2626 CSTPPINV.l_item_balance_tbl(l_index) := (x.actual_cost * i_txn_qty) + CSTPPINV.l_item_balance_tbl(l_index);
2627 CSTPPINV.l_make_balance_tbl(l_index) := (x.actual_cost * i_make_qty) + CSTPPINV.l_make_balance_tbl(l_index);
2628 CSTPPINV.l_buy_balance_tbl(l_index) := (x.actual_cost * i_buy_qty) + CSTPPINV.l_buy_balance_tbl(l_index);
2629 END IF;
2630 END;
2631 END LOOP;
2632
2633 IF CSTPPINV.l_item_quantity_tbl.EXISTS (i_item_id) THEN
2634 CSTPPINV.l_item_quantity_tbl(i_item_id) := i_txn_qty + CSTPPINV.l_item_quantity_tbl(i_item_id);
2635 ELSE
2636 CSTPPINV.l_item_quantity_tbl(i_item_id):= i_txn_qty;
2637 END IF;
2638
2639 IF CSTPPINV.l_make_quantity_tbl.EXISTS (i_item_id) THEN
2640 CSTPPINV.l_make_quantity_tbl(i_item_id) := i_make_qty + CSTPPINV.l_make_quantity_tbl(i_item_id);
2641 ELSE
2642 CSTPPINV.l_make_quantity_tbl(i_item_id):= i_make_qty;
2643 END IF;
2644
2645 IF CSTPPINV.l_issue_quantity_tbl.EXISTS (i_item_id) THEN
2646 CSTPPINV.l_issue_quantity_tbl (i_item_id):= i_issue_qty + CSTPPINV.l_issue_quantity_tbl(i_item_id);
2647 ELSE
2648 CSTPPINV.l_issue_quantity_tbl(i_item_id):= i_issue_qty;
2649 END IF;
2650
2651 IF CSTPPINV.l_buy_quantity_tbl.EXISTS (i_item_id) THEN
2652 CSTPPINV.l_buy_quantity_tbl (i_item_id):= i_buy_qty + CSTPPINV.l_buy_quantity_tbl(i_item_id);
2653 ELSE
2654 CSTPPINV.l_buy_quantity_tbl(i_item_id):= i_buy_qty;
2655 END IF;
2656
2657 /* --- start of auto log --- */
2658 <<out_arg_log>>
2659
2660 IF l_plog THEN
2661 fnd_log.string(
2662 fnd_log.level_procedure,
2663 l_module||'.'||l_stmt_num,
2664 'Exiting CSTPPWAC.calc_pwac_cost with '||
2665 'O_Err_Num = '||O_Err_Num||','||
2666 'O_Err_Code = '||O_Err_Code||','||
2667 'O_Err_Msg = '||O_Err_Msg
2668 );
2669 END IF;
2670 /* --- end of auto log --- */
2671 EXCEPTION
2672 /* --- start of auto log --- */
2673 WHEN fnd_api.g_exc_unexpected_error THEN
2674 IF l_exceptionlog THEN
2675 fnd_msg_pub.add_exc_msg(
2676 p_pkg_name => 'CSTPPWAC',
2677 p_procedure_name => 'calc_pwac_cost',
2678 p_error_text => 'An exception has occurred.'
2679 );
2680 fnd_log.string(
2681 fnd_log.level_exception,
2682 l_module||'.'||l_stmt_num,
2683 'An exception has occurred.'
2684 );
2685 END IF;
2686 o_err_num := l_err_num;
2687 o_err_code := l_err_code;
2688 o_err_msg := l_err_msg;
2689 WHEN OTHERS THEN
2690 ROLLBACK;
2691 IF l_uLog THEN
2692 fnd_message.set_name('BOM','CST_UNEXPECTED');
2693 fnd_message.set_token('SQLERRM',SQLERRM);
2694 fnd_msg_pub.add;
2695 fnd_log.message(
2696 fnd_log.level_unexpected,
2697 l_module||'.'||l_stmt_num,
2698 FALSE
2699 );
2700 END IF;
2701 o_err_num := SQLCODE;
2702 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
2703 /* --- end of auto log --- */
2704 END calc_pwac_cost;
2705
2706 -- PROCEDURE
2707 -- periodic_cost_update
2708 --
2709 PROCEDURE periodic_cost_update (
2710 I_PAC_PERIOD_ID IN NUMBER,
2711 I_COST_GROUP_ID IN NUMBER,
2712 I_COST_TYPE_ID IN NUMBER,
2713 I_TXN_ID IN NUMBER,
2714 I_COST_LAYER_ID IN NUMBER,
2715 I_QTY_LAYER_ID IN NUMBER,
2716 I_ITEM_ID IN NUMBER,
2717 I_USER_ID IN NUMBER,
2718 I_LOGIN_ID IN NUMBER,
2719 I_REQ_ID IN NUMBER,
2720 I_PRG_APPL_ID IN NUMBER,
2721 I_PRG_ID IN NUMBER,
2722 I_TXN_CATEGORY IN NUMBER,
2723 I_TXN_QTY IN NUMBER,
2724 O_Err_Num OUT NOCOPY NUMBER,
2725 O_Err_Code OUT NOCOPY VARCHAR2,
2726 O_Err_Msg OUT NOCOPY VARCHAR2)
2727 IS
2728 l_value_change_flag NUMBER;
2729 l_stmt_num NUMBER;
2730 l_onhand NUMBER;
2731 l_make_qty NUMBER;
2732 l_buy_qty NUMBER;
2733
2734 /* --- start of auto log --- */
2735 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.periodic_cost_update';
2736 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
2737 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
2738 fnd_log.TEST(fnd_log.level_unexpected, l_module);
2739 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
2740 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
2741 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
2742 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
2743 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
2744
2745 /* --- end of auto log --- */
2746 BEGIN
2747 /* --- start of auto log --- */
2748 IF l_plog THEN
2749 fnd_log.string(
2750 fnd_log.level_procedure,
2751 l_module||'.'||l_stmt_num,
2752 'Entering CSTPPWAC.periodic_cost_update with '||
2753 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
2754 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
2755 'I_COST_TYPE_ID = '||I_COST_TYPE_ID||','||
2756 'I_TXN_ID = '||I_TXN_ID||','||
2757 'I_COST_LAYER_ID = '||I_COST_LAYER_ID||','||
2758 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
2759 'I_ITEM_ID = '||I_ITEM_ID||','||
2760 'I_USER_ID = '||I_USER_ID||','||
2761 'I_LOGIN_ID = '||I_LOGIN_ID||','||
2762 'I_REQ_ID = '||I_REQ_ID||','||
2763 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
2764 'I_PRG_ID = '||I_PRG_ID||','||
2765 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
2766 );
2767 END IF;
2768 /* --- end of auto log --- */
2769
2770 /********************************************************************
2771 ** Insert into mpacd, all the elemental cost : **
2772 ** - exists in cpicd, but not exists in mptcd **
2773 ** It will use the current cost in cpicd as the new cost **
2774 ********************************************************************/
2775 l_stmt_num := 10;
2776
2777 INSERT INTO mtl_pac_actual_cost_details (
2778 transaction_id,
2779 pac_period_id,
2780 cost_type_id,
2781 cost_group_id,
2782 cost_layer_id,
2783 cost_element_id,
2784 level_type,
2785 last_update_date,
2786 last_updated_by,
2787 creation_date,
2788 created_by,
2789 last_update_login,
2790 request_id,
2791 program_application_id,
2792 program_id,
2793 program_update_date,
2794 inventory_item_id,
2795 actual_cost,
2796 prior_cost,
2797 prior_buy_cost,
2798 prior_make_cost,
2799 new_cost,
2800 new_buy_cost,
2801 new_make_cost,
2802 variance_amount,
2803 insertion_flag,
2804 user_entered,
2805 transaction_costed_date,
2806 txn_category)
2807 SELECT
2808 i_txn_id,
2809 i_pac_period_id,
2810 i_cost_type_id,
2811 i_cost_group_id,
2812 i_cost_layer_id,
2813 cpicd.cost_element_id,
2814 cpicd.level_type,
2815 sysdate,
2816 i_user_id,
2817 sysdate,
2818 i_user_id,
2819 i_login_id,
2820 i_req_id,
2821 i_prg_appl_id,
2822 i_prg_id,
2823 sysdate,
2824 i_item_id,
2825 decode (i_txn_category, 5, 0, 8.5, 0,2.5,0, nvl(cpicd.item_cost,0)), -- insert 0 for PCU value change
2826 nvl(cpicd.item_cost,0),
2827 nvl(cpicd.item_buy_cost,0),
2828 nvl(cpicd.item_make_cost,0),
2829 nvl(cpicd.item_cost,0),
2830 nvl(cpicd.item_buy_cost,0),
2831 nvl(cpicd.item_make_cost,0),
2832 0, -- variance
2833 'Y',
2834 'N',
2835 sysdate,
2836 i_txn_category
2837 FROM cst_pac_item_cost_details cpicd
2838 WHERE cpicd.cost_layer_id = i_cost_layer_id
2839 AND not exists (
2840 SELECT 'not exists in mptcd'
2841 FROM mtl_pac_txn_cost_details mptcd
2842 WHERE mptcd.transaction_id = i_txn_id
2843 AND mptcd.pac_period_id = i_pac_period_id
2844 AND mptcd.cost_group_id = i_cost_group_id
2845 AND mptcd.cost_element_id = cpicd.cost_element_id
2846 AND mptcd.level_type = cpicd.level_type);
2847
2848 /********************************************************************
2849 ** Insert into mpacd, all the elemental cost : **
2850 ** - exists in mptcd and cpicd **
2851 ** - exists in mptcd but not exists in cpicd **
2852 ** New cost will be calculated based on current cost (if exists) **
2853 ** and cost change in mptcd. **
2854 ********************************************************************/
2855 l_stmt_num := 20;
2856 INSERT INTO mtl_pac_actual_cost_details (
2857 transaction_id,
2858 pac_period_id,
2859 cost_type_id,
2860 cost_group_id,
2861 cost_layer_id,
2862 cost_element_id,
2863 level_type,
2864 last_update_date,
2865 last_updated_by,
2866 creation_date,
2867 created_by,
2868 last_update_login,
2869 request_id,
2870 program_application_id,
2871 program_id,
2872 program_update_date,
2873 inventory_item_id,
2874 actual_cost,
2875 prior_cost,
2876 prior_buy_cost,
2877 prior_make_cost,
2878 new_cost,
2879 new_buy_cost,
2880 new_make_cost,
2881 variance_amount,
2882 insertion_flag,
2883 user_entered,
2884 transaction_costed_date,
2885 txn_category,
2886 onhand_variance_amount)
2887 SELECT
2888 i_txn_id,
2889 i_pac_period_id,
2890 i_cost_type_id,
2891 i_cost_group_id,
2892 i_cost_layer_id,
2893 mptcd.cost_element_id,
2894 mptcd.level_type,
2895 sysdate,
2896 i_user_id,
2897 sysdate,
2898 i_user_id,
2899 i_login_id,
2900 i_req_id,
2901 i_prg_appl_id,
2902 i_prg_id,
2903 sysdate,
2904 mptcd.inventory_item_id,
2905 decode(mptcd.new_periodic_cost,NULL,
2906 decode(mptcd.percentage_change,NULL,
2907 /* value change formula */
2908 decode(sign(cpql.layer_quantity),1,
2909 decode(sign(i_txn_qty),1,
2910 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
2911 (mptcd.value_change/i_txn_qty*cpql.layer_quantity),
2912 nvl(mptcd.value_change,0)
2913 ),
2914 nvl(mptcd.value_change,0)),
2915 nvl(mptcd.value_change,0)),
2916 /* percentage change formula */
2917 nvl(cpicd.item_cost,0)*(1+mptcd.percentage_change/100)),
2918 /* new average cost formula */
2919 mptcd.new_periodic_cost),
2920 decode (mptcd.value_change, NULL, nvl(cpicd.item_cost,0), NULL),
2921 decode (mptcd.value_change, NULL, nvl(cpicd.item_buy_cost,0), NULL),
2922 decode (mptcd.value_change, NULL, nvl(cpicd.item_make_cost,0), NULL),
2923 decode(mptcd.new_periodic_cost,NULL,
2924 decode(mptcd.percentage_change,NULL,
2925 /* value change formula */
2926 NULL, /* do not populate new_cost for value_change */
2927 /* percentage change formula */
2928 nvl(cpicd.item_cost,0)*(1+mptcd.percentage_change/100)),
2929 /* new average cost formula */
2930 mptcd.new_periodic_cost),
2931 decode (mptcd.value_change, NULL, nvl(cpicd.item_buy_cost,0), NULL),
2932 decode (mptcd.value_change, NULL, nvl(cpicd.item_make_cost,0), NULL),
2933 NULL, /* variance */
2934 'Y',
2935 'N',
2936 sysdate,
2937 i_txn_category,
2938 decode(mptcd.value_change,NULL,
2939 0,
2940 decode(sign(i_txn_qty),1,
2941 decode(sign(cpql.layer_quantity),1,
2942 decode(sign(cpql.layer_quantity-i_txn_qty),-1,
2943 mptcd.value_change*(1-cpql.layer_quantity/i_txn_qty),
2944 0
2945 ),
2946 0
2947 ),
2948 0
2949 )
2950 )
2951 FROM mtl_pac_txn_cost_details mptcd,
2952 cst_pac_item_cost_details cpicd,
2953 cst_pac_quantity_layers cpql
2954 WHERE mptcd.transaction_id = i_txn_id
2955 AND mptcd.pac_period_id = i_pac_period_id
2956 AND mptcd.cost_group_id = i_cost_group_id
2957 AND cpql.cost_layer_id = i_cost_layer_id
2958 AND cpql.quantity_layer_id = i_qty_layer_id
2959 AND cpicd.cost_layer_id (+) = i_cost_layer_id
2960 AND cpicd.cost_element_id (+) = mptcd.cost_element_id
2961 AND cpicd.level_type (+) = mptcd.level_type;
2962
2963 /****************************************************************************
2964 If the transaction is not a value change cost update, set the value_change
2965 flag to 1, otherwise set it to 0
2966 *****************************************************************************/
2967
2968 l_stmt_num := 30;
2969 SELECT DECODE(MAX(value_change),NULL, 1, 0)
2970 INTO l_value_change_flag
2971 FROM mtl_pac_txn_cost_details mptcd
2972 WHERE mptcd.transaction_id = i_txn_id
2973 AND mptcd.pac_period_id = i_pac_period_id
2974 AND mptcd.cost_group_id = i_cost_group_id;
2975
2976 l_stmt_num := 40;
2977 SELECT nvl(total_layer_quantity,0),
2978 nvl(make_quantity,0),
2979 nvl(buy_quantity,0)
2980 INTO l_onhand,
2981 l_make_qty,
2982 l_buy_qty
2983 FROM cst_pac_item_costs
2984 WHERE cost_layer_id = i_cost_layer_id;
2985
2986 IF (l_value_change_flag <> 0)
2987 THEN
2988 -- New Cost or percent change cost update
2989 l_stmt_num := 50;
2990 DELETE FROM cst_pac_item_cost_details
2991 WHERE cost_layer_id = i_cost_layer_id;
2992
2993 l_stmt_num := 60;
2994 INSERT INTO cst_pac_item_cost_details(
2995 cost_layer_id,
2996 cost_element_id,
2997 level_type,
2998 last_update_date,
2999 last_updated_by,
3000 creation_date,
3001 created_by,
3002 last_update_login,
3003 request_id,
3004 program_application_id,
3005 program_id,
3006 program_update_date,
3007 item_cost,
3008 item_buy_cost,
3009 item_make_cost,
3010 item_balance,
3011 buy_balance,
3012 make_balance)
3013 SELECT i_cost_layer_id,
3014 mpacd.cost_element_id,
3015 mpacd.level_type,
3016 sysdate,
3017 i_user_id,
3018 sysdate,
3019 i_user_id,
3020 i_login_id,
3021 i_req_id,
3022 i_prg_appl_id,
3023 i_prg_id,
3024 sysdate,
3025 mpacd.new_cost,
3026 mpacd.new_buy_cost,
3027 mpacd.new_make_cost,
3028 mpacd.new_cost * l_onhand,
3029 mpacd.new_buy_cost * l_buy_qty,
3030 mpacd.new_make_cost * l_make_qty
3031 FROM mtl_pac_actual_cost_details mpacd
3032 WHERE mpacd.transaction_id = i_txn_id
3033 AND mpacd.cost_group_id = i_cost_group_id
3034 AND mpacd.cost_layer_id = i_cost_layer_id;
3035
3036 l_stmt_num := 70;
3037 UPDATE cst_pac_item_costs cpic
3038 SET (last_updated_by,
3039 last_update_date,
3040 last_update_login,
3041 request_id,
3042 program_application_id,
3043 program_id,
3044 program_update_date,
3045 pl_material,
3046 pl_material_overhead,
3047 pl_resource,
3048 pl_outside_processing,
3049 pl_overhead,
3050 tl_material,
3051 tl_material_overhead,
3052 tl_resource,
3053 tl_outside_processing,
3054 tl_overhead,
3055 material_cost,
3056 material_overhead_cost,
3057 resource_cost,
3058 outside_processing_cost,
3059 overhead_cost,
3060 pl_item_cost,
3061 tl_item_cost,
3062 item_cost,
3063 begin_item_cost,
3064 item_buy_cost,
3065 item_make_cost,
3066 unburdened_cost,
3067 burden_cost) =
3068 (SELECT
3069 i_user_id,
3070 sysdate,
3071 i_login_id,
3072 i_req_id,
3073 i_prg_appl_id,
3074 i_prg_id,
3075 sysdate,
3076 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)), -- PL_MATERIAL
3077 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)), -- PL_MATERIAL_OVERHEAD
3078 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)), -- PL_RESOURCE
3079 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,ITEM_COST,0),0)), -- PL_OUTSIDE_PROCESSING
3080 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)), -- PL_OVERHEAD
3081 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,0),0)), -- TL_MATERIAL
3082 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,ITEM_COST,0),0)), -- TL_MATERIAL_OVERHEAD
3083 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,ITEM_COST,0),0)), -- TL_RESOURCE
3084 SUM(DECODE(LEVEL_TYPE ,1,DECODE(COST_ELEMENT_ID ,4,ITEM_COST,0),0)), -- TL_OUTSIDE_PROCESSING
3085 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,ITEM_COST,0),0)), -- TL_OVERHEAD
3086 SUM(DECODE(COST_ELEMENT_ID,1,ITEM_COST,0)), -- MATERIAL_COST
3087 SUM(DECODE(COST_ELEMENT_ID,2,ITEM_COST,0)), -- MATERIAL_OVERHEAD_COST
3088 SUM(DECODE(COST_ELEMENT_ID,3,ITEM_COST,0)), -- RESOURCE_COST
3089 SUM(DECODE(COST_ELEMENT_ID,4,ITEM_COST,0)), -- OUTSIDE_PROCESSING_COST
3090 SUM(DECODE(COST_ELEMENT_ID,5,ITEM_COST,0)), -- OVERHEAD_COST
3091 SUM(DECODE(LEVEL_TYPE,2,ITEM_COST,0)), -- PL_ITEM_COST
3092 SUM(DECODE(LEVEL_TYPE,1,ITEM_COST,0)), -- TL_ITEM_COST
3093 SUM(ITEM_COST), -- ITEM_COST
3094 DECODE(l_value_change_flag, 1, SUM(ITEM_COST), cpic.begin_item_cost),
3095 SUM(ITEM_BUY_COST), -- ITEM_BUY_COST
3096 SUM(ITEM_MAKE_COST), -- ITEM_MAKE_COST
3097 SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,2,ITEM_COST,0),ITEM_COST)),-- UNBURDENED_COST
3098 SUM(DECODE(COST_ELEMENT_ID,2,DECODE(LEVEL_TYPE,1,ITEM_COST,0),0)) -- BURDEN_COST
3099 FROM CST_PAC_ITEM_COST_DETAILS
3100 WHERE COST_LAYER_ID = i_cost_layer_id
3101 GROUP BY COST_LAYER_ID)
3102 WHERE cpic.cost_layer_id = i_cost_layer_id
3103 AND EXISTS
3104 (SELECT 'there is detail cost'
3105 FROM cst_pac_item_cost_details cpicd
3106 WHERE cpicd.cost_layer_id = i_cost_layer_id);
3107
3108 l_stmt_num := 80;
3109 MERGE INTO CST_PAC_PERIOD_BALANCES cppb
3110 USING (SELECT i_pac_period_id pac_period_id,
3111 i_cost_group_id cost_group_id,
3112 i_item_id item_id,
3113 i_cost_layer_id cost_layer_id,
3114 i_qty_layer_id qty_layer_id,
3115 mpacd.cost_element_id cost_element_id,
3116 mpacd.level_type level_type,
3117 2 txn_category, -- txn category = 2 for PCU new cost and % change
3118 0 category_quantity, -- quantity = 0 for cost update transactions
3119 (l_onhand * (mpacd.actual_cost - mpacd.prior_cost)) category_balance,
3120 (l_onhand * mpacd.actual_cost) period_balance,
3121 l_onhand period_quantity,
3122 mpacd.actual_cost
3123 FROM mtl_pac_actual_cost_details mpacd
3124 WHERE mpacd.cost_layer_id = i_cost_layer_id
3125 AND mpacd.pac_period_id = i_pac_period_id
3126 AND mpacd.cost_group_id = i_cost_group_id
3127 AND mpacd.transaction_id = i_txn_id) mpacd
3128 ON ( cppb.pac_period_id = mpacd.pac_period_id
3129 AND cppb.cost_group_id = mpacd.cost_group_id
3130 AND cppb.cost_layer_id = mpacd.cost_layer_id
3131 AND cppb.cost_element_id = mpacd.cost_element_id
3132 AND cppb.level_type = mpacd.level_type
3133 AND cppb.txn_category = mpacd.txn_category)
3134 WHEN NOT MATCHED THEN
3135 INSERT (PAC_PERIOD_ID,
3136 COST_GROUP_ID,
3137 INVENTORY_ITEM_ID,
3138 COST_LAYER_ID,
3139 QUANTITY_LAYER_ID,
3140 COST_ELEMENT_ID,
3141 LEVEL_TYPE,
3142 TXN_CATEGORY,
3143 TXN_CATEGORY_QTY,
3144 TXN_CATEGORY_VALUE,
3145 PERIOD_BALANCE,
3146 PERIOD_QUANTITY,
3147 PERIODIC_COST,
3148 VARIANCE_AMOUNT,
3149 LAST_UPDATE_DATE,
3150 LAST_UPDATED_BY,
3151 LAST_UPDATE_LOGIN,
3152 CREATED_BY,
3153 CREATION_DATE,
3154 REQUEST_ID,
3155 PROGRAM_APPLICATION_ID,
3156 PROGRAM_ID,
3157 PROGRAM_UPDATE_DATE)
3158 VALUES (mpacd.pac_period_id,
3159 mpacd.cost_group_id,
3160 mpacd.item_id,
3161 mpacd.cost_layer_id,
3162 mpacd.qty_layer_id,
3163 mpacd.cost_element_id,
3164 mpacd.level_type,
3165 mpacd.txn_category,
3166 mpacd.category_quantity,
3167 mpacd.category_balance,
3168 mpacd.period_balance,
3169 mpacd.period_quantity,
3170 mpacd.actual_cost,
3171 0,
3172 sysdate,
3173 i_user_id,
3174 i_login_id,
3175 i_user_id,
3176 sysdate,
3177 i_req_id,
3178 i_prg_appl_id,
3179 i_prg_id,
3180 sysdate)
3181 WHEN MATCHED THEN
3182 UPDATE SET
3183 txn_category_qty = mpacd.category_quantity,
3184 txn_category_value = txn_category_value + mpacd.category_balance,
3185 period_quantity = mpacd.period_quantity,
3186 period_balance = mpacd.period_balance,
3187 periodic_cost = mpacd.actual_cost,
3188 last_update_date = sysdate,
3189 last_updated_by = i_user_id,
3190 last_update_login = i_login_id,
3191 request_id = i_req_id,
3192 program_application_id = i_prg_appl_id,
3193 program_id = i_prg_id,
3194 program_update_date = sysdate;
3195 l_stmt_num := 85;
3196 UPDATE mtl_material_transactions mmt
3197 SET periodic_primary_quantity = l_onhand
3198 WHERE mmt.transaction_id = i_txn_id;
3199 fnd_file.put_line(fnd_file.log,'Updated MMT with primary_quantity: ' || to_char(l_onhand));
3200 ELSE
3201 /* Value Change transaction - treated like cost owned transactions */
3202 l_stmt_num := 90;
3203
3204 FOR x in
3205 (select actual_cost, cost_element_id, level_type
3206 from mtl_pac_actual_cost_details mpacd
3207 where mpacd.cost_layer_id = i_cost_layer_id
3208 and mpacd.cost_group_id = i_cost_group_id
3209 and mpacd.transaction_id = i_txn_id)
3210 LOOP
3211 DECLARE
3212 l_index NUMBER;
3213 l_count NUMBER;
3214 BEGIN
3215 l_stmt_num := 100;
3216 l_index := -1;
3217 IF (CSTPPINV.l_item_start_index_tbl.EXISTS (i_item_id)) THEN /* item already exists */
3218 FOR l_count IN (CSTPPINV.l_item_start_index_tbl(i_item_id))..(CSTPPINV.l_item_end_index_tbl(i_item_id))
3219 LOOP
3220 IF (CSTPPINV.l_item_id_tbl(l_count) = i_item_id AND
3221 CSTPPINV.l_cost_element_id_tbl(l_count) = x.cost_element_id AND
3222 CSTPPINV.l_level_type_tbl(l_count) = x.level_type) THEN
3223 l_index := l_count;
3224 END IF;
3225 END LOOP;
3226 ELSE /* new item */
3227 CSTPPINV.l_item_start_index_tbl (i_item_id) := CSTPPINV.l_item_id_tbl.COUNT + 1;
3228 CSTPPINV.l_item_end_index_tbl (i_item_id) := CSTPPINV.l_item_id_tbl.COUNT + 1;
3229 END IF;
3230 l_stmt_num := 110;
3231 IF (l_index = -1) THEN /* item-cost_element-level_type combination not found: Insert*/
3232 l_index := CSTPPINV.l_item_id_tbl.COUNT + 1;
3233 CSTPPINV.l_item_id_tbl(l_index) := i_item_id;
3234 CSTPPINV.l_cost_layer_id_tbl(l_index) := i_cost_layer_id;
3235 CSTPPINV.l_qty_layer_id_tbl(l_index) := i_qty_layer_id;
3236 CSTPPINV.l_cost_element_id_tbl(l_index) := x.cost_element_id;
3237 CSTPPINV.l_level_type_tbl(l_index) := x.level_type;
3238 CSTPPINV.l_txn_category_tbl (l_index) := i_txn_category;
3239
3240 CSTPPINV.l_item_balance_tbl(l_index) := x.actual_cost;
3241 CSTPPINV.l_make_balance_tbl(l_index) := 0;
3242 CSTPPINV.l_buy_balance_tbl(l_index) := 0;
3243
3244 CSTPPINV.l_item_end_index_tbl (i_item_id) := l_index;
3245 ELSE /* item-cost_element-level_type combination found: Update*/
3246 CSTPPINV.l_item_balance_tbl(l_index) := x.actual_cost + CSTPPINV.l_item_balance_tbl(l_index);
3247 END IF;
3248 END;
3249 END LOOP;
3250
3251 /* Fix for Bug 1970458
3252 * For a value change periodic update cost transaction,
3253 * update the primary_quantity in mmt to the layer quantity from cpql.
3254 * Prior to this, the quantity at the beginning of the period was being
3255 * used and this caused errors in the distributions.
3256 * The layer qty can be obtained from cst_pac_quantity_layers
3257 */
3258 l_stmt_num := 120;
3259 UPDATE mtl_material_transactions mmt
3260 SET --primary_quantity = l_onhand,
3261 /* Bug 2288994. Update periodic_primary_quantity also */
3262 periodic_primary_quantity = l_onhand
3263 WHERE mmt.value_change IS NOT NULL
3264 AND mmt.transaction_id = i_txn_id;
3265 fnd_file.put_line(fnd_file.log,'Updated MMT with primary_quantity: ' || to_char(l_onhand));
3266
3267 END IF;
3268
3269 /* --- start of auto log --- */
3270 <<out_arg_log>>
3271
3272 IF l_plog THEN
3273 fnd_log.string(
3274 fnd_log.level_procedure,
3275 l_module||'.'||l_stmt_num,
3276 'Exiting CSTPPWAC.periodic_cost_update with '||
3277 'O_Err_Num = '||O_Err_Num||','||
3278 'O_Err_Code = '||O_Err_Code||','||
3279 'O_Err_Msg = '||O_Err_Msg
3280 );
3281 END IF;
3282 /* --- end of auto log --- */
3283 EXCEPTION
3284 WHEN OTHERS THEN
3285 ROLLBACK;
3286 IF l_uLog THEN
3287 fnd_message.set_name('BOM','CST_UNEXPECTED');
3288 fnd_message.set_token('SQLERRM',SQLERRM);
3289 fnd_msg_pub.add;
3290 fnd_log.message(
3291 fnd_log.level_unexpected,
3292 l_module||'.'||l_stmt_num,
3293 FALSE
3294 );
3295 END IF;
3296 o_err_num := SQLCODE;
3297 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3298 /* --- end of auto log --- */
3299 END periodic_cost_update;
3300
3301 /* Commented to remove the dependency on cst_pc_txn_history table
3302 --as part of customer bug 6751847 and fp bug 5999388 performance fixes
3303 -- PROCEDURE
3304 -- insert_txn_history
3305 --
3306 PROCEDURE insert_txn_history (
3307 I_PAC_PERIOD_ID IN NUMBER,
3308 I_COST_GROUP_ID IN NUMBER,
3309 I_TXN_ID IN NUMBER,
3310 I_PROCESS_GROUP IN NUMBER,
3311 I_ITEM_ID IN NUMBER,
3312 I_QTY_LAYER_ID IN NUMBER,
3313 I_TXN_QTY IN NUMBER,
3314 I_USER_ID IN NUMBER,
3315 I_LOGIN_ID IN NUMBER,
3316 I_REQ_ID IN NUMBER,
3317 I_PRG_APPL_ID IN NUMBER,
3318 I_PRG_ID IN NUMBER,
3319 I_TXN_CATEGORY IN NUMBER,
3320 O_Err_Num OUT NOCOPY NUMBER,
3321 O_Err_Code OUT NOCOPY VARCHAR2,
3322 O_Err_Msg OUT NOCOPY VARCHAR2
3323 ) IS
3324 l_stmt_num NUMBER;
3325
3326 -- start of auto log ---
3327 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_txn_history';
3328 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
3329 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
3330 fnd_log.TEST(fnd_log.level_unexpected, l_module);
3331 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
3332 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
3333 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
3334 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
3335 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
3336
3337 -- end of auto log ---
3338 BEGIN
3339 --- start of auto log ---
3340 IF l_plog THEN
3341 fnd_log.string(
3342 fnd_log.level_procedure,
3343 l_module||'.'||l_stmt_num,
3344 'Entering CSTPPWAC.insert_txn_history with '||
3345 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
3346 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
3347 'I_TXN_ID = '||I_TXN_ID||','||
3348 'I_PROCESS_GROUP = '||I_PROCESS_GROUP||','||
3349 'I_ITEM_ID = '||I_ITEM_ID||','||
3350 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
3351 'I_TXN_QTY = '||I_TXN_QTY||','||
3352 'I_USER_ID = '||I_USER_ID||','||
3353 'I_LOGIN_ID = '||I_LOGIN_ID||','||
3354 'I_REQ_ID = '||I_REQ_ID||','||
3355 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
3356 'I_PRG_ID = '||I_PRG_ID||','||
3357 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
3358 );
3359 END IF;
3360 --- end of auto log ---
3361
3362 l_stmt_num := 10;
3363 INSERT INTO cst_pc_txn_history (
3364 pac_period_id,
3365 cost_group_id,
3366 transaction_id,
3367 process_seq,
3368 process_group,
3369 inventory_item_id,
3370 txn_master_qty,
3371 prior_costed_master_qty,
3372 txn_category,
3373 last_update_date,
3374 last_updated_by,
3375 creation_date,
3376 created_by,
3377 request_id,
3378 program_application_id,
3379 program_id,
3380 program_update_date,
3381 last_update_login)
3382 (SELECT
3383 i_pac_period_id,
3384 i_cost_group_id,
3385 i_txn_id,
3386 cst_pc_txn_history_s.nextval,
3387 i_process_group,
3388 i_item_id,
3389 i_txn_qty,
3390 layer_quantity,
3391 i_txn_category,
3392 sysdate,
3393 i_user_id,
3394 sysdate,
3395 i_user_id,
3396 i_req_id,
3397 i_prg_appl_id,
3398 i_prg_id,
3399 SYSDATE,
3400 i_login_id
3401 FROM
3402 cst_pac_quantity_layers
3403 WHERE quantity_layer_id = i_qty_layer_id);
3404
3405 --- start of auto log ---
3406 <<out_arg_log>>
3407
3408 IF l_plog THEN
3409 fnd_log.string(
3410 fnd_log.level_procedure,
3411 l_module||'.'||l_stmt_num,
3412 'Exiting CSTPPWAC.insert_txn_history with '||
3413 'O_Err_Num = '||O_Err_Num||','||
3414 'O_Err_Code = '||O_Err_Code||','||
3415 'O_Err_Msg = '||O_Err_Msg
3416 );
3417 END IF;
3418 --- end of auto log ---
3419 EXCEPTION
3420 WHEN OTHERS THEN
3421 ROLLBACK;
3422 IF l_uLog THEN
3423 fnd_message.set_name('BOM','CST_UNEXPECTED');
3424 fnd_message.set_token('SQLERRM',SQLERRM);
3425 fnd_msg_pub.add;
3426 fnd_log.message(
3427 fnd_log.level_unexpected,
3428 l_module||'.'||l_stmt_num,
3429 FALSE
3430 );
3431 END IF;
3432 o_err_num := SQLCODE;
3433 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3434 --- end of auto log ---
3435 END insert_txn_history;
3436 */
3437
3438 /*
3439 -- Commented to remove the dependency on cst_pc_txn_history table
3440 -- as part of customer bug 6751847 and fp bug 5999388 performance fixes
3441 -- PROCEDURE
3442 -- PROCEDURE
3443 -- update_txn_history
3444 --
3445 PROCEDURE update_txn_history (
3446 I_PAC_PERIOD_ID IN NUMBER,
3447 I_COST_GROUP_ID IN NUMBER,
3448 I_TXN_ID IN NUMBER,
3449 I_USER_ID IN NUMBER,
3450 I_LOGIN_ID IN NUMBER,
3451 I_REQ_ID IN NUMBER,
3452 I_PRG_APPL_ID IN NUMBER,
3453 I_PRG_ID IN NUMBER,
3454 O_Err_Num OUT NOCOPY NUMBER,
3455 O_Err_Code OUT NOCOPY VARCHAR2,
3456 O_Err_Msg OUT NOCOPY VARCHAR2
3457 ) IS
3458 l_stmt_num NUMBER;
3459
3460 --- start of auto log ---
3461 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_txn_history';
3462 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
3463 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
3464 fnd_log.TEST(fnd_log.level_unexpected, l_module);
3465 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
3466 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
3467 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
3468 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
3469 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
3470
3471 --- end of auto log ---
3472 BEGIN
3473 --- start of auto log ---
3474 IF l_plog THEN
3475 fnd_log.string(
3476 fnd_log.level_procedure,
3477 l_module||'.'||l_stmt_num,
3478 'Entering CSTPPWAC.update_txn_history with '||
3479 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
3480 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
3481 'I_TXN_ID = '||I_TXN_ID||','||
3482 'I_USER_ID = '||I_USER_ID||','||
3483 'I_LOGIN_ID = '||I_LOGIN_ID||','||
3484 'I_REQ_ID = '||I_REQ_ID||','||
3485 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
3486 'I_PRG_ID = '||I_PRG_ID
3487 );
3488 END IF;
3489 --- end of auto log ---
3490
3491 l_stmt_num := 10;
3492 UPDATE cst_pc_txn_history
3493 SET( actual_cost,
3494 new_cost,
3495 prior_cost )=
3496 (SELECT
3497 sum(actual_cost),
3498 sum(new_cost),
3499 sum(prior_cost)
3500 FROM
3501 mtl_pac_actual_cost_details
3502 WHERE pac_period_id = i_pac_period_id
3503 and cost_group_id = i_cost_group_id
3504 and transaction_id = i_txn_id)
3505 WHERE pac_period_id = i_pac_period_id
3506 and cost_group_id = i_cost_group_id
3507 and transaction_id = i_txn_id;
3508
3509 --- start of auto log ---
3510 <<out_arg_log>>
3511
3512 IF l_plog THEN
3513 fnd_log.string(
3514 fnd_log.level_procedure,
3515 l_module||'.'||l_stmt_num,
3516 'Exiting CSTPPWAC.update_txn_history with '||
3517 'O_Err_Num = '||O_Err_Num||','||
3518 'O_Err_Code = '||O_Err_Code||','||
3519 'O_Err_Msg = '||O_Err_Msg
3520 );
3521 END IF;
3522 --- end of auto log ---
3523 EXCEPTION
3524 WHEN OTHERS THEN
3525 ROLLBACK;
3526 IF l_uLog THEN
3527 fnd_message.set_name('BOM','CST_UNEXPECTED');
3528 fnd_message.set_token('SQLERRM',SQLERRM);
3529 fnd_msg_pub.add;
3530 fnd_log.message(
3531 fnd_log.level_unexpected,
3532 l_module||'.'||l_stmt_num,
3533 FALSE
3534 );
3535 END IF;
3536 o_err_num := SQLCODE;
3537 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3538 --- end of auto log ---
3539 END update_txn_history;
3540 */
3541
3542 -- PROCEDURE
3543 -- insert_into_cppb
3544 --
3545 PROCEDURE insert_into_cppb(i_pac_period_id IN NUMBER,
3546 i_cost_group_id IN NUMBER,
3547 i_txn_category IN NUMBER,
3548 i_user_id IN NUMBER,
3549 i_login_id IN NUMBER,
3550 i_request_id IN NUMBER,
3551 i_prog_id IN NUMBER,
3552 i_prog_appl_id IN NUMBER,
3553 o_err_num OUT NOCOPY NUMBER,
3554 o_err_code OUT NOCOPY VARCHAR2,
3555 o_err_msg OUT NOCOPY VARCHAR2)
3556 IS
3557 l_stmt_num NUMBER;
3558 l_new_qty_tbl CSTPPINV.t_item_quantity_tbl;
3559 l_new_buy_qty_tbl CSTPPINV.t_item_quantity_tbl;
3560 l_new_make_qty_tbl CSTPPINV.t_item_quantity_tbl;
3561 l_new_issue_qty_tbl CSTPPINV.t_item_quantity_tbl;
3562 l_category_qty_tbl CSTPPINV.t_item_quantity_tbl;
3563
3564 /* --- start of auto log --- */
3565 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_into_cppb';
3566 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
3567 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
3568 fnd_log.TEST(fnd_log.level_unexpected, l_module);
3569 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
3570 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
3571 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
3572 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
3573 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
3574
3575 /* --- end of auto log --- */
3576 BEGIN
3577 /* --- start of auto log --- */
3578 IF l_plog THEN
3579 fnd_log.string(
3580 fnd_log.level_procedure,
3581 l_module||'.'||l_stmt_num,
3582 'Entering CSTPPWAC.insert_into_cppb with '||
3583 'i_cost_group_id = '||i_cost_group_id||','||
3584 'i_txn_category = '||i_txn_category||','||
3585 'i_user_id = '||i_user_id||','||
3586 'i_login_id = '||i_login_id||','||
3587 'i_request_id = '||i_request_id||','||
3588 'i_prog_id = '||i_prog_id||','||
3589 'i_prog_appl_id = '||i_prog_appl_id
3590 );
3591 END IF;
3592 /* --- end of auto log --- */
3593
3594 l_stmt_num := 10;
3595 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
3596 FOR l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3597 LOOP
3598 IF (i_txn_category = 5 OR i_txn_category = 8.5 OR
3599 i_txn_category = 2.5) THEN
3600 -- Periodic Cost Update value change
3601 SELECT cpic.total_layer_quantity,
3602 cpic.buy_quantity,
3603 cpic.make_quantity,
3604 cpic.issue_quantity,
3605 0 /* category_qty = 0 for cost updates */
3606 INTO l_new_qty_tbl (l_index),
3607 l_new_buy_qty_tbl (l_index),
3608 l_new_make_qty_tbl (l_index),
3609 l_new_issue_qty_tbl (l_index),
3610 l_category_qty_tbl (l_index)
3611 FROM cst_pac_item_costs cpic
3612 WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
3613 ELSE
3614 SELECT cpic.total_layer_quantity + CSTPPINV.l_item_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
3615 cpic.buy_quantity + CSTPPINV.l_buy_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
3616 cpic.make_quantity + CSTPPINV.l_make_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
3617 cpic.issue_quantity + CSTPPINV.l_issue_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
3618 CSTPPINV.l_item_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index))
3619 INTO l_new_qty_tbl (l_index),
3620 l_new_buy_qty_tbl (l_index),
3621 l_new_make_qty_tbl (l_index),
3622 l_new_issue_qty_tbl (l_index),
3623 l_category_qty_tbl (l_index)
3624 FROM cst_pac_item_costs cpic
3625 WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
3626 END IF;
3627 END LOOP;
3628 END IF;
3629
3630 l_stmt_num := 20;
3631 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3632 MERGE INTO CST_PAC_PERIOD_BALANCES cppb
3633 USING (SELECT i_pac_period_id pac_period_id,
3634 i_cost_group_id cost_group_id,
3635 CSTPPINV.l_item_id_tbl(l_index) item_id,
3636 CSTPPINV.l_cost_layer_id_tbl(l_index) cost_layer_id,
3637 CSTPPINV.l_qty_layer_id_tbl(l_index) qty_layer_id,
3638 CSTPPINV.l_cost_element_id_tbl(l_index) cost_element_id,
3639 CSTPPINV.l_level_type_tbl(l_index) level_type,
3640 CSTPPINV.l_txn_category_tbl(l_index) txn_category,
3641 l_category_qty_tbl (l_index) category_quantity,
3642 CSTPPINV.l_item_balance_tbl(l_index) category_balance
3643 FROM dual) temp
3644 ON ( cppb.pac_period_id = temp.pac_period_id
3645 AND cppb.cost_layer_id = temp.cost_layer_id
3646 AND cppb.cost_element_id = temp.cost_element_id
3647 AND cppb.level_type = temp.level_type
3648 AND cppb.txn_category = temp.txn_category)
3649 WHEN NOT MATCHED THEN
3650 INSERT (pac_period_id,
3651 cost_group_id,
3652 inventory_item_id,
3653 cost_layer_id,
3654 quantity_layer_id,
3655 cost_element_id,
3656 level_type,
3657 txn_category,
3658 txn_category_qty,
3659 txn_category_value,
3660 last_update_date,
3661 last_updated_by,
3662 last_update_login,
3663 created_by,
3664 creation_date,
3665 request_id,
3666 program_application_id,
3667 program_id,
3668 program_update_date)
3669 VALUES (temp.pac_period_id,
3670 temp.cost_group_id,
3671 temp.item_id,
3672 temp.cost_layer_id,
3673 temp.qty_layer_id,
3674 temp.cost_element_id,
3675 temp.level_type,
3676 temp.txn_category,
3677 temp.category_quantity,
3678 temp.category_balance,
3679 sysdate,
3680 i_user_id,
3681 i_login_id,
3682 i_user_id,
3683 sysdate,
3684 i_request_id,
3685 i_prog_appl_id,
3686 i_prog_id,
3687 sysdate)
3688 WHEN MATCHED THEN
3689 UPDATE SET
3690 txn_category_qty = txn_category_qty + temp.category_quantity,
3691 txn_category_value = txn_category_value + temp.category_balance,
3692 last_update_date = sysdate,
3693 last_updated_by = i_user_id,
3694 last_update_login = i_login_id,
3695 request_id = i_request_id,
3696 program_application_id = i_prog_appl_id,
3697 program_id = i_prog_id,
3698 program_update_date = sysdate;
3699
3700 l_stmt_num := 30;
3701 -- Update balance of existing rows in CPICD
3702 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3703 UPDATE CST_PAC_ITEM_COST_DETAILS cpicd
3704 SET item_balance = nvl(item_balance, 0) + CSTPPINV.l_item_balance_tbl (l_index),
3705 /* 11834257: The total item cost and make cost needs to be updated for non-rework
3706 completions as calculate_periodic_cost is not called for this category */
3707 item_cost = decode(i_txn_category, 4,
3708 decode(sign(Nvl(l_new_qty_tbl (l_index),0)),
3709 0, cpicd.item_cost,
3710 (-1 * sign(Nvl(item_balance, 0) + Nvl(CSTPPINV.l_item_balance_tbl (l_index),0))), 0,
3711 (Nvl(item_balance, 0) + Nvl(CSTPPINV.l_item_balance_tbl (l_index),0)) / l_new_qty_tbl (l_index)),
3712 item_cost),
3713 item_make_cost = decode(i_txn_category, 4,
3714 decode(sign(Nvl(l_new_make_qty_tbl (l_index),0)),
3715 0, cpicd.item_make_cost,
3716 (-1 * sign(Nvl(make_balance, 0) + Nvl(CSTPPINV.l_make_balance_tbl (l_index),0))), 0,
3717 (Nvl(make_balance, 0) + Nvl(CSTPPINV.l_make_balance_tbl (l_index),0)) / l_new_make_qty_tbl (l_index)),
3718 item_make_cost),
3719 make_balance = nvl(make_balance, 0) + CSTPPINV.l_make_balance_tbl (l_index),
3720 buy_balance = nvl(buy_balance, 0) + CSTPPINV.l_buy_balance_tbl (l_index),
3721 last_update_date = sysdate,
3722 last_updated_by = i_user_id,
3723 last_update_login = i_login_id,
3724 request_id = i_request_id,
3725 program_application_id = i_prog_appl_id,
3726 program_id = i_prog_id,
3727 program_update_date = sysdate
3728 WHERE cpicd.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index)
3729 AND cpicd.cost_element_id = CSTPPINV.l_cost_element_id_tbl (l_index)
3730 AND cpicd.level_type = CSTPPINV.l_level_type_tbl (l_index);
3731
3732 l_stmt_num := 40;
3733 -- Insert missing cost elements into CPICD
3734 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3735 INSERT INTO CST_PAC_ITEM_COST_DETAILS cpicd
3736 (cost_layer_id,
3737 cost_element_id,
3738 level_type,
3739 last_update_date,
3740 last_updated_by,
3741 creation_date,
3742 created_by,
3743 last_update_login,
3744 request_id,
3745 program_application_id,
3746 program_id,
3747 program_update_date,
3748 item_cost,
3749 item_buy_cost,
3750 item_make_cost,
3751 item_balance,
3752 make_balance,
3753 buy_balance)
3754 (SELECT CSTPPINV.l_cost_layer_id_tbl (l_index),
3755 CSTPPINV.l_cost_element_id_tbl (l_index),
3756 CSTPPINV.l_level_type_tbl (l_index),
3757 sysdate,
3758 i_user_id,
3759 sysdate,
3760 i_user_id,
3761 i_login_id,
3762 i_request_id,
3763 i_prog_appl_id,
3764 i_prog_id,
3765 sysdate,
3766 /* 11834257: The total item cost and make cost needs to be updated for non-rework
3767 completions as calculate_periodic_cost is not called for this category */
3768 decode(i_txn_category, 4,
3769 decode(sign(Nvl(l_new_qty_tbl (l_index),0)),
3770 0, 0,
3771 (-1 * sign(Nvl(CSTPPINV.l_item_balance_tbl (l_index),0))), 0,
3772 Nvl(CSTPPINV.l_item_balance_tbl (l_index),0) / l_new_qty_tbl (l_index)),
3773 0),
3774 decode(i_txn_category, 4,
3775 decode(sign(Nvl(l_new_make_qty_tbl (l_index),0)),
3776 0, 0,
3777 (-1 * sign(Nvl(CSTPPINV.l_make_balance_tbl (l_index),0))), 0,
3778 (Nvl(CSTPPINV.l_make_balance_tbl (l_index),0)) / l_new_make_qty_tbl (l_index)),
3779 0),
3780 0,
3781 CSTPPINV.l_item_balance_tbl (l_index),
3782 CSTPPINV.l_make_balance_tbl (l_index),
3783 CSTPPINV.l_buy_balance_tbl (l_index)
3784 FROM dual
3785 WHERE NOT EXISTS (SELECT 1
3786 FROM cst_pac_item_cost_details cpicd1
3787 WHERE cpicd1.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index)
3788 AND cpicd1.cost_element_id = CSTPPINV.l_cost_element_id_tbl (l_index)
3789 AND cpicd1.level_type = CSTPPINV.l_level_type_tbl (l_index)));
3790
3791 l_stmt_num := 50;
3792 /* Changing the query as per Bug5045692. Performance Fix */
3793 /* update quantities and balance in CPIC */
3794 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3795 UPDATE cst_pac_item_costs cpic
3796 SET total_layer_quantity = l_new_qty_tbl (l_index),
3797 issue_quantity = l_new_issue_qty_tbl (l_index),
3798 buy_quantity = l_new_buy_qty_tbl(l_index),
3799 make_quantity = l_new_make_qty_tbl (l_index),
3800 last_update_date = sysdate,
3801 last_updated_by = i_user_id,
3802 request_id = i_request_id,
3803 program_application_id = i_prog_appl_id,
3804 program_id = i_prog_id,
3805 program_update_date = sysdate,
3806 last_update_login = i_login_id
3807 WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
3808
3809 l_stmt_num := 60;
3810 /* Update CPQL quantity */
3811 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3812 UPDATE CST_PAC_QUANTITY_LAYERS cpql
3813 SET (last_updated_by,
3814 last_update_date,
3815 last_update_login,
3816 request_id,
3817 program_application_id,
3818 program_id,
3819 program_update_date,
3820 layer_quantity) =
3821 (SELECT i_user_id,
3822 sysdate,
3823 i_login_id,
3824 i_request_id,
3825 i_prog_appl_id,
3826 i_prog_id,
3827 sysdate,
3828 l_new_qty_tbl (l_index)
3829 FROM sys.dual)
3830 WHERE cpql.quantity_layer_id = CSTPPINV.l_qty_layer_id_tbl (l_index)
3831 AND EXISTS
3832 (SELECT 'there is a layer'
3833 FROM cst_pac_quantity_layers cpql
3834 WHERE cpql.quantity_layer_id = CSTPPINV.l_qty_layer_id_tbl (l_index));
3835
3836 l_stmt_num := 70;
3837 /* Clear All PL/SQL tables */
3838 CSTPPINV.l_item_id_tbl.DELETE;
3839 CSTPPINV.l_cost_layer_id_tbl.DELETE;
3840 CSTPPINV.l_qty_layer_id_tbl.DELETE;
3841
3842 CSTPPINV.l_cost_element_id_tbl.DELETE;
3843 CSTPPINV.l_level_type_tbl.DELETE;
3844 CSTPPINV.l_txn_category_tbl.DELETE;
3845
3846 CSTPPINV.l_item_balance_tbl.DELETE;
3847 CSTPPINV.l_make_balance_tbl.DELETE;
3848 CSTPPINV.l_buy_balance_tbl.DELETE;
3849
3850 CSTPPINV.l_item_quantity_tbl.DELETE;
3851 CSTPPINV.l_make_quantity_tbl.DELETE;
3852 CSTPPINV.l_buy_quantity_tbl.DELETE;
3853 CSTPPINV.l_issue_quantity_tbl.DELETE;
3854
3855 CSTPPINV.l_item_start_index_tbl.DELETE;
3856 CSTPPINV.l_item_end_index_tbl.DELETE;
3857
3858 /* --- start of auto log --- */
3859 <<out_arg_log>>
3860
3861 IF l_plog THEN
3862 fnd_log.string(
3863 fnd_log.level_procedure,
3864 l_module||'.'||l_stmt_num,
3865 'Exiting CSTPPWAC.insert_into_cppb with '||
3866 'o_err_num = '||o_err_num||','||
3867 'o_err_code = '||o_err_code||','||
3868 'o_err_msg = '||o_err_msg
3869 );
3870 END IF;
3871 /* --- end of auto log --- */
3872 EXCEPTION
3873 WHEN OTHERS THEN
3874 ROLLBACK;
3875 IF l_uLog THEN
3876 fnd_message.set_name('BOM','CST_UNEXPECTED');
3877 fnd_message.set_token('SQLERRM',SQLERRM);
3878 fnd_msg_pub.add;
3879 fnd_log.message(
3880 fnd_log.level_unexpected,
3881 l_module||'.'||l_stmt_num,
3882 FALSE
3883 );
3884 END IF;
3885 o_err_num := SQLCODE;
3886 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3887 /* --- end of auto log --- */
3888 END insert_into_cppb;
3889
3890 -- +========================================================================+
3891 -- PROCEDURE: PRIVATE UTILITY
3892 -- PARAMETERS: calc_item_periodic_cost
3893 -- Descrition: Called from calculate_periodic_cost with inventory_item_id
3894 -- +========================================================================+
3895 PROCEDURE calc_item_periodic_cost (i_pac_period_id IN NUMBER,
3896 i_cost_group_id IN NUMBER,
3897 i_cost_type_id IN NUMBER,
3898 i_low_level_code IN NUMBER,
3899 i_item_id IN NUMBER,
3900 i_user_id IN NUMBER,
3901 i_login_id IN NUMBER,
3902 i_request_id IN NUMBER,
3903 i_prog_id IN NUMBER,
3904 i_prog_appl_id IN NUMBER,
3905 o_err_num OUT NOCOPY NUMBER,
3906 o_err_code OUT NOCOPY VARCHAR2,
3907 o_err_msg OUT NOCOPY VARCHAR2)
3908 IS
3909 l_stmt_num NUMBER;
3910 l_max_txn_category NUMBER;
3911 TYPE t_txn_id_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.transaction_id%TYPE
3912 INDEX BY BINARY_INTEGER;
3913 TYPE t_txn_category_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.txn_category%TYPE
3914 INDEX BY BINARY_INTEGER;
3915 l_last_txn_id_tbl t_txn_id_tbl;
3916 l_txn_category_tbl t_txn_category_tbl;
3917 l_cost_layer_id_tbl CSTPPINV.t_cost_layer_id_tbl;
3918
3919 /* --- start of auto log --- */
3920 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.calc_item_periodic_cost';
3921 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
3922 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
3923 fnd_log.TEST(fnd_log.level_unexpected, l_module);
3924 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
3925 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
3926 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
3927 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
3928 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
3929
3930 /* --- end of auto log --- */
3931 BEGIN
3932 /* --- start of auto log --- */
3933 IF l_plog THEN
3934 fnd_log.string(
3935 fnd_log.level_procedure,
3936 l_module||'.'||l_stmt_num,
3937 'Entering CSTPPWAC.calc_item_periodic_cost with '||
3938 'i_cost_group_id = '||i_cost_group_id||','||
3939 'i_cost_type_id = '||i_cost_type_id||','||
3940 'i_low_level_code = '||i_low_level_code||','||
3941 'i_item_id = '||i_item_id||','||
3942 'i_user_id = '||i_user_id||','||
3943 'i_login_id = '||i_login_id||','||
3944 'i_request_id = '||i_request_id||','||
3945 'i_prog_id = '||i_prog_id||','||
3946 'i_prog_appl_id = '||i_prog_appl_id
3947 );
3948 END IF;
3949 /* --- end of auto log --- */
3950
3951 -- Build temporary tables to hold the last txn id and txn category values for each cost_layer_id
3952 IF (i_low_level_code = -1) THEN
3953 -- items without completion
3954 l_stmt_num := 10;
3955 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
3956 BULK COLLECT
3957 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
3958 FROM mtl_pac_actual_cost_details mpacd
3959 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
3960 FROM mtl_pac_actual_cost_details mpacd1
3961 WHERE mpacd1.txn_category = (SELECT max(txn_category)
3962 FROM cst_pac_period_balances cppb
3963 WHERE cppb.pac_period_id = i_pac_period_id
3964 AND cppb.cost_group_id = i_cost_group_id
3965 AND cppb.cost_layer_id = mpacd.cost_layer_id)
3966 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
3967 AND mpacd1.pac_period_id = i_pac_period_id
3968 AND mpacd1.cost_group_id = i_cost_group_id)
3969 AND mpacd.cost_group_id = i_cost_group_id
3970 AND mpacd.pac_period_id = i_pac_period_id
3971 AND mpacd.inventory_item_id = i_item_id
3972 AND NOT EXISTS (SELECT 1
3973 FROM cst_pac_low_level_codes cpllc
3974 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
3975 AND cpllc.pac_period_id = i_pac_period_id
3976 AND cpllc.cost_group_id = i_cost_group_id);
3977 ELSE
3978 -- items with completion
3979 l_stmt_num := 20;
3980
3981 -- get the maximum transaction category that has been processed for any item having
3982 -- completions till this point in time.
3983
3984 SELECT max(txn_category)
3985 INTO l_max_txn_category
3986 FROM mtl_pac_actual_cost_details mpacd
3987 WHERE mpacd.pac_period_id = i_pac_period_id
3988 AND mpacd.cost_group_id = i_cost_group_id
3989 AND mpacd.inventory_item_id = i_item_id
3990 AND EXISTS (SELECT 1
3991 FROM cst_pac_low_level_codes cpllc
3992 WHERE cpllc.cost_group_id = i_cost_group_id
3993 AND cpllc.pac_period_id = i_pac_period_id
3994 AND cpllc.inventory_item_id = mpacd.inventory_item_id
3995 AND cpllc.low_level_code = i_low_level_code);
3996
3997 IF l_sLog THEN
3998 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module||'.'||l_stmt_num,'l_max_txn_category = '||l_max_txn_category);
3999 END IF;
4000
4001 IF (l_max_txn_category = 7) THEN
4002 -- Rework completions. Pick only items having records with transaction
4003 -- category = 7 to avoid updating variance again wrongly for other cost owned
4004 -- transaction categories
4005
4006 l_stmt_num := 25;
4007 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4008 BULK COLLECT
4009 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4010 FROM mtl_pac_actual_cost_details mpacd
4011 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
4012 FROM mtl_pac_actual_cost_details mpacd1
4013 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4014 FROM cst_pac_period_balances cppb
4015 WHERE cppb.pac_period_id = i_pac_period_id
4016 AND cppb.cost_group_id = i_cost_group_id
4017 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4018 AND mpacd1.txn_category = l_max_txn_category
4019 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4020 AND mpacd1.pac_period_id = i_pac_period_id
4021 AND mpacd1.cost_group_id = i_cost_group_id)
4022 AND mpacd.cost_group_id = i_cost_group_id
4023 AND mpacd.pac_period_id = i_pac_period_id
4024 AND mpacd.inventory_item_id = i_item_id
4025 AND EXISTS (SELECT 1
4026 FROM cst_pac_low_level_codes cpllc
4027 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4028 AND cpllc.low_level_code = i_low_level_code
4029 AND cpllc.pac_period_id = i_pac_period_id
4030 AND cpllc.cost_group_id = i_cost_group_id);
4031 ELSE
4032 -- Non rework completions
4033
4034 l_stmt_num := 30;
4035 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4036 BULK COLLECT
4037 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4038 FROM mtl_pac_actual_cost_details mpacd
4039 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
4040 FROM mtl_pac_actual_cost_details mpacd1
4041 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4042 FROM cst_pac_period_balances cppb
4043 WHERE cppb.pac_period_id = i_pac_period_id
4044 AND cppb.cost_group_id = i_cost_group_id
4045 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4046 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4047 AND mpacd1.pac_period_id = i_pac_period_id
4048 AND mpacd1.cost_group_id = i_cost_group_id)
4049 AND mpacd.cost_group_id = i_cost_group_id
4050 AND mpacd.pac_period_id = i_pac_period_id
4051 AND mpacd.inventory_item_id = i_item_id
4052 AND EXISTS (SELECT 1
4053 FROM cst_pac_low_level_codes cpllc
4054 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4055 AND cpllc.low_level_code = i_low_level_code
4056 AND cpllc.pac_period_id = i_pac_period_id
4057 AND cpllc.cost_group_id = i_cost_group_id);
4058 END IF;
4059 END IF;
4060
4061
4062 /****************************************************************************
4063 Post variance to the last transaction in the last cost owned txn category
4064 processed for that item. Insert rows into mpacd for missing cost elements
4065 ****************************************************************************/
4066
4067 l_stmt_num := 35;
4068 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
4069 UPDATE mtl_pac_actual_cost_details mpacd
4070 SET variance_amount = (SELECT decode (sign(cpic.total_layer_quantity),
4071 0, cpicd.item_balance,
4072 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
4073 0)
4074 FROM cst_pac_item_costs cpic,
4075 cst_pac_item_cost_details cpicd
4076 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
4077 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4078 AND cpicd.cost_element_id = mpacd.cost_element_id
4079 AND cpicd.level_type = mpacd.level_type),
4080 last_update_date = sysdate,
4081 last_updated_by = i_user_id,
4082 last_update_login = i_login_id,
4083 request_id = i_request_id,
4084 program_application_id = i_prog_appl_id,
4085 program_id = i_prog_id,
4086 program_update_date = sysdate
4087 WHERE transaction_id = l_last_txn_id_tbl (l_index)
4088 AND mpacd.cost_group_id = i_cost_group_id
4089 AND mpacd.pac_period_id = i_pac_period_id
4090 AND mpacd.cost_layer_id = l_cost_layer_id_tbl(l_index)
4091 AND (cost_element_id, level_type) = (SELECT cost_element_id, level_type
4092 FROM cst_pac_item_cost_details cpicd
4093 WHERE cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4094 AND cpicd.cost_element_id = mpacd.cost_element_id
4095 AND cpicd.level_type = mpacd.level_type);
4096
4097 l_stmt_num := 40;
4098 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
4099 INSERT INTO mtl_pac_actual_cost_details mpacd
4100 (COST_GROUP_ID,
4101 TRANSACTION_ID,
4102 PAC_PERIOD_ID,
4103 COST_TYPE_ID,
4104 COST_ELEMENT_ID,
4105 LEVEL_TYPE,
4106 INVENTORY_ITEM_ID,
4107 COST_LAYER_ID,
4108 ACTUAL_COST,
4109 USER_ENTERED,
4110 INSERTION_FLAG,
4111 TRANSACTION_COSTED_DATE,
4112 LAST_UPDATE_DATE,
4113 LAST_UPDATED_BY,
4114 CREATION_DATE,
4115 CREATED_BY,
4116 REQUEST_ID,
4117 PROGRAM_APPLICATION_ID,
4118 PROGRAM_ID,
4119 PROGRAM_UPDATE_DATE,
4120 LAST_UPDATE_LOGIN,
4121 VARIANCE_AMOUNT,
4122 TXN_CATEGORY)
4123 (SELECT i_cost_group_id,
4124 l_last_txn_id_tbl (l_index),
4125 i_pac_period_id,
4126 i_cost_type_id,
4127 cpicd.cost_element_id,
4128 cpicd.level_type,
4129 cpic.inventory_item_id,
4130 cpic.cost_layer_id,
4131 0,
4132 'N',
4133 'N',
4134 sysdate,
4135 SYSDATE,
4136 i_user_id,
4137 SYSDATE,
4138 i_user_id,
4139 i_request_id,
4140 i_prog_appl_id,
4141 i_prog_id,
4142 SYSDATE,
4143 i_login_id,
4144 decode (sign(cpic.total_layer_quantity),
4145 0, cpicd.item_balance,
4146 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
4147 0),
4148 l_txn_category_tbl(l_index)
4149 FROM cst_pac_item_cost_details cpicd,
4150 cst_pac_item_costs cpic
4151 WHERE cpicd.cost_layer_id = cpic.cost_layer_id
4152 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4153 AND NOT EXISTS (SELECT 1
4154 FROM mtl_pac_actual_cost_details mpacd1
4155 WHERE mpacd1.transaction_id = l_last_txn_id_tbl (l_index)
4156 AND mpacd1.cost_layer_id = cpicd.cost_layer_id
4157 AND mpacd1.cost_element_id = cpicd.cost_element_id
4158 AND mpacd1.level_type = cpicd.level_type)
4159 );
4160 l_stmt_num := 50;
4161
4162 IF (i_low_level_code = -1) THEN
4163 -- Items that do not have completion
4164 UPDATE cst_pac_item_cost_details cpicd
4165 SET (last_update_date,
4166 last_updated_by,
4167 last_update_login,
4168 request_id,
4169 program_application_id,
4170 program_id,
4171 program_update_date,
4172 item_cost,
4173 item_buy_cost,
4174 item_make_cost,
4175 item_balance,
4176 buy_balance,
4177 make_balance) =
4178 (SELECT sysdate,
4179 i_user_id,
4180 i_login_id,
4181 i_request_id,
4182 i_prog_appl_id,
4183 i_prog_id,
4184 sysdate,
4185 decode (sign(cpic.total_layer_quantity),
4186 0, cpicd.item_cost,
4187 (-1 * sign(cpicd.item_balance)), 0,
4188 cpicd.item_balance / cpic.total_layer_quantity),
4189 decode (sign(cpic.total_layer_quantity),
4190 0, cpicd.item_buy_cost,
4191 (-1 * sign(cpicd.item_balance)), 0,
4192 decode (cpic.buy_quantity,
4193 0, 0,
4194 cpicd.buy_balance / cpic.buy_quantity)),
4195 decode (sign(cpic.total_layer_quantity),
4196 0, cpicd.item_make_cost,
4197 (-1 * sign(cpicd.item_balance)), 0,
4198 decode (cpic.make_quantity,
4199 0, 0,
4200 cpicd.make_balance / cpic.make_quantity)),
4201 decode (sign (cpic.total_layer_quantity),
4202 0, 0,
4203 (-1 * sign(cpicd.item_balance)), 0,
4204 cpicd.item_balance),
4205 decode (sign (cpic.total_layer_quantity),
4206 0, 0,
4207 (-1 * sign(cpicd.item_balance)), 0,
4208 cpicd.buy_balance),
4209 decode (sign (cpic.total_layer_quantity),
4210 0, 0,
4211 (-1 * sign(cpicd.item_balance)), 0,
4212 cpicd.make_balance)
4213 FROM cst_pac_item_costs cpic
4214 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
4215 WHERE cpicd.cost_layer_id IN ( SELECT cost_layer_id
4216 FROM cst_pac_item_costs
4217 WHERE inventory_item_id = i_item_id
4218 AND cost_group_id = i_cost_group_id
4219 AND pac_period_id = i_pac_period_id)
4220 AND EXISTS (SELECT 1
4221 FROM cst_pac_period_balances cppb
4222 WHERE cppb.pac_period_id = i_pac_period_id
4223 AND cppb.cost_group_id = i_cost_group_id
4224 AND cppb.cost_layer_id = cpicd.cost_layer_id
4225 AND cppb.cost_element_id = cpicd.cost_element_id
4226 AND cppb.level_type = cpicd.level_type
4227 AND cppb.inventory_item_id = i_item_id)
4228 AND NOT EXISTS (SELECT 1
4229 FROM cst_pac_low_level_codes cpllc
4230 WHERE cpllc.pac_period_id = i_pac_period_id
4231 AND cpllc.cost_group_id = i_cost_group_id
4232 AND cpllc.inventory_item_id = i_item_id);
4233
4234 l_stmt_num := 60;
4235 UPDATE cst_pac_item_costs cpic
4236 SET (last_updated_by,
4237 last_update_date,
4238 last_update_login,
4239 request_id,
4240 program_application_id,
4241 program_id,
4242 program_update_date,
4243 pl_material,
4244 pl_material_overhead,
4245 pl_resource,
4246 pl_outside_processing,
4247 pl_overhead,
4248 tl_material,
4249 tl_material_overhead,
4250 tl_resource,
4251 tl_outside_processing,
4252 tl_overhead,
4253 material_cost,
4254 material_overhead_cost,
4255 resource_cost,
4256 outside_processing_cost,
4257 overhead_cost,
4258 pl_item_cost,
4259 tl_item_cost,
4260 item_cost,
4261 item_buy_cost,
4262 item_make_cost,
4263 unburdened_cost,
4264 burden_cost) =
4265 (SELECT i_user_id,
4266 sysdate,
4267 i_login_id,
4268 i_request_id,
4269 i_prog_appl_id,
4270 i_prog_id,
4271 sysdate,
4272 pl_material,
4273 pl_material_overhead,
4274 pl_resource,
4275 pl_outside_processing,
4276 pl_overhead,
4277 tl_material,
4278 tl_material_overhead,
4279 tl_resource,
4280 tl_outside_processing,
4281 tl_overhead,
4282 material_cost,
4283 material_overhead_cost,
4284 resource_cost,
4285 outside_processing_cost,
4286 overhead_cost,
4287 pl_item_cost,
4288 tl_item_cost,
4289 item_cost,
4290 item_buy_cost,
4291 item_make_cost,
4292 unburdened_cost,
4293 burden_cost
4294 FROM cst_pac_item_costs_v v
4295 WHERE v.cost_layer_id = cpic.cost_layer_id)
4296 WHERE cpic.inventory_item_id = i_item_id
4297 AND cpic.cost_group_id = i_cost_group_id
4298 AND cpic.pac_period_id = i_pac_period_id
4299 AND EXISTS (SELECT 1
4300 FROM cst_pac_period_balances cppb
4301 WHERE cppb.pac_period_id = i_pac_period_id
4302 AND cppb.cost_group_id = i_cost_group_id
4303 AND cppb.cost_layer_id = cpic.cost_layer_id)
4304 AND NOT EXISTS (SELECT 1
4305 FROM cst_pac_low_level_codes cpllc
4306 WHERE cpllc.inventory_item_id = cpic.inventory_item_id
4307 AND cpllc.pac_period_id = i_pac_period_id
4308 AND cpllc.cost_group_id = i_cost_group_id)
4309 AND EXISTS
4310 (SELECT 'there is detail cost'
4311 FROM cst_pac_item_cost_details cpicd
4312 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
4313 ELSE
4314 -- low_level_code <> -1; items having completion
4315
4316 l_stmt_num := 70;
4317 UPDATE cst_pac_item_cost_details cpicd
4318 SET (last_update_date,
4319 last_updated_by,
4320 last_update_login,
4321 request_id,
4322 program_application_id,
4323 program_id,
4324 program_update_date,
4325 item_cost,
4326 item_buy_cost,
4327 item_make_cost,
4328 item_balance,
4329 buy_balance,
4330 make_balance) =
4331 (SELECT sysdate,
4332 i_user_id,
4333 i_login_id,
4334 i_request_id,
4335 i_prog_appl_id,
4336 i_prog_id,
4337 sysdate,
4338 decode (sign(cpic.total_layer_quantity),
4339 0, cpicd.item_cost,
4340 (-1 * sign(cpicd.item_balance)), 0,
4341 cpicd.item_balance / cpic.total_layer_quantity),
4342 decode (sign(cpic.total_layer_quantity),
4343 0, cpicd.item_buy_cost,
4344 (-1 * sign(cpicd.item_balance)), 0,
4345 decode (cpic.buy_quantity,
4346 0, 0,
4347 cpicd.buy_balance / cpic.buy_quantity)),
4348 decode (sign(cpic.total_layer_quantity),
4349 0, cpicd.item_make_cost,
4350 (-1 * sign(cpicd.item_balance)), 0,
4351 decode (cpic.make_quantity,
4352 0, 0,
4353 cpicd.make_balance / cpic.make_quantity)),
4354 decode (sign (cpic.total_layer_quantity),
4355 0, 0,
4356 (-1 * sign(cpicd.item_balance)), 0,
4357 cpicd.item_balance),
4358 decode (sign (cpic.total_layer_quantity),
4359 0, 0,
4360 (-1 * sign(cpicd.item_balance)), 0,
4361 cpicd.buy_balance),
4362 decode (sign (cpic.total_layer_quantity),
4363 0, 0,
4364 (-1 * sign(cpicd.item_balance)), 0,
4365 cpicd.make_balance)
4366 FROM cst_pac_item_costs cpic
4367 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
4368 WHERE cpicd.cost_layer_id IN ( SELECT cost_layer_id
4369 FROM cst_pac_item_costs
4370 WHERE inventory_item_id = i_item_id
4371 AND cost_group_id = i_cost_group_id
4372 AND pac_period_id = i_pac_period_id)
4373 AND EXISTS (SELECT 1
4374 FROM cst_pac_period_balances cppb
4375 WHERE cppb.pac_period_id = i_pac_period_id
4376 AND cppb.cost_group_id = i_cost_group_id
4377 AND cppb.cost_layer_id = cpicd.cost_layer_id
4378 AND cppb.cost_element_id = cpicd.cost_element_id
4379 AND cppb.level_type = cpicd.level_type
4380 AND cppb.inventory_item_id = i_item_id)
4381 AND EXISTS (SELECT 1
4382 FROM cst_pac_low_level_codes cpllc
4383 WHERE cpllc.low_level_code = i_low_level_code
4384 AND cpllc.pac_period_id = i_pac_period_id
4385 AND cpllc.cost_group_id = i_cost_group_id
4386 AND cpllc.inventory_item_id = i_item_id);
4387
4388 l_stmt_num := 80;
4389 UPDATE cst_pac_item_costs cpic
4390 SET (last_updated_by,
4391 last_update_date,
4392 last_update_login,
4393 request_id,
4394 program_application_id,
4395 program_id,
4396 program_update_date,
4397 pl_material,
4398 pl_material_overhead,
4399 pl_resource,
4400 pl_outside_processing,
4401 pl_overhead,
4402 tl_material,
4403 tl_material_overhead,
4404 tl_resource,
4405 tl_outside_processing,
4406 tl_overhead,
4407 material_cost,
4408 material_overhead_cost,
4409 resource_cost,
4410 outside_processing_cost,
4411 overhead_cost,
4412 pl_item_cost,
4413 tl_item_cost,
4414 item_cost,
4415 item_buy_cost,
4416 item_make_cost,
4417 unburdened_cost,
4418 burden_cost) =
4419 (SELECT i_user_id,
4420 sysdate,
4421 i_login_id,
4422 i_request_id,
4423 i_prog_appl_id,
4424 i_prog_id,
4425 sysdate,
4426 pl_material,
4427 pl_material_overhead,
4428 pl_resource,
4429 pl_outside_processing,
4430 pl_overhead,
4431 tl_material,
4432 tl_material_overhead,
4433 tl_resource,
4434 tl_outside_processing,
4435 tl_overhead,
4436 material_cost,
4437 material_overhead_cost,
4438 resource_cost,
4439 outside_processing_cost,
4440 overhead_cost,
4441 pl_item_cost,
4442 tl_item_cost,
4443 item_cost,
4444 item_buy_cost,
4445 item_make_cost,
4446 unburdened_cost,
4447 burden_cost
4448 FROM cst_pac_item_costs_v v
4449 WHERE v.cost_layer_id = cpic.cost_layer_id)
4450 WHERE cpic.inventory_item_id = i_item_id
4451 AND cpic.cost_group_id = i_cost_group_id
4452 AND cpic.pac_period_id = i_pac_period_id
4453 AND EXISTS (SELECT 1
4454 FROM cst_pac_period_balances cppb
4455 WHERE cppb.pac_period_id = i_pac_period_id
4456 AND cppb.cost_group_id = i_cost_group_id
4457 AND cppb.cost_layer_id = cpic.cost_layer_id)
4458 AND EXISTS (SELECT 1
4459 FROM cst_pac_low_level_codes cpllc
4460 WHERE cpllc.low_level_code = i_low_level_code
4461 AND cpllc.inventory_item_id = cpic.inventory_item_id
4462 AND cpllc.pac_period_id = i_pac_period_id
4463 AND cpllc.cost_group_id = i_cost_group_id)
4464 AND EXISTS
4465 (SELECT 'there is detail cost'
4466 FROM cst_pac_item_cost_details cpicd
4467 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
4468 END IF;
4469
4470 /* --- start of auto log --- */
4471 <<out_arg_log>>
4472
4473 IF l_plog THEN
4474 fnd_log.string(
4475 fnd_log.level_procedure,
4476 l_module||'.'||l_stmt_num,
4477 'Exiting CSTPPWAC.calc_item_periodic_cost with '||
4478 'o_err_num = '||o_err_num||','||
4479 'o_err_code = '||o_err_code||','||
4480 'o_err_msg = '||o_err_msg
4481 );
4482 END IF;
4483 /* --- end of auto log --- */
4484 EXCEPTION
4485 WHEN OTHERS THEN
4486 ROLLBACK;
4487 IF l_uLog THEN
4488 fnd_message.set_name('BOM','CST_UNEXPECTED');
4489 fnd_message.set_token('SQLERRM',SQLERRM);
4490 fnd_msg_pub.add;
4491 fnd_log.message(
4492 fnd_log.level_unexpected,
4493 l_module||'.'||l_stmt_num,
4494 FALSE
4495 );
4496 END IF;
4497 o_err_num := SQLCODE;
4498 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
4499 /* --- end of auto log --- */
4500 END calc_item_periodic_cost;
4501
4502 -- PROCEDURE
4503 -- calc_periodic_cost
4504 --
4505 PROCEDURE calc_periodic_cost (i_pac_period_id IN NUMBER,
4506 i_cost_group_id IN NUMBER,
4507 i_cost_type_id IN NUMBER,
4508 i_low_level_code IN NUMBER,
4509 i_user_id IN NUMBER,
4510 i_login_id IN NUMBER,
4511 i_request_id IN NUMBER,
4512 i_prog_id IN NUMBER,
4513 i_prog_appl_id IN NUMBER,
4514 o_err_num OUT NOCOPY NUMBER,
4515 o_err_code OUT NOCOPY VARCHAR2,
4516 o_err_msg OUT NOCOPY VARCHAR2)
4517 IS
4518 l_stmt_num NUMBER;
4519 l_max_txn_category NUMBER;
4520 TYPE t_txn_id_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.transaction_id%TYPE
4521 INDEX BY BINARY_INTEGER;
4522 TYPE t_txn_category_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.txn_category%TYPE
4523 INDEX BY BINARY_INTEGER;
4524 l_last_txn_id_tbl t_txn_id_tbl;
4525 l_txn_category_tbl t_txn_category_tbl;
4526 l_cost_layer_id_tbl CSTPPINV.t_cost_layer_id_tbl;
4527
4528 /* --- start of auto log --- */
4529 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.calc_periodic_cost';
4530 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
4531 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
4532 fnd_log.TEST(fnd_log.level_unexpected, l_module);
4533 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
4534 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
4535 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
4536 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
4537 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
4538
4539 /* --- end of auto log --- */
4540 BEGIN
4541 /* --- start of auto log --- */
4542 IF l_plog THEN
4543 fnd_log.string(
4544 fnd_log.level_procedure,
4545 l_module||'.'||l_stmt_num,
4546 'Entering CSTPPWAC.calc_periodic_cost with '||
4547 'i_cost_group_id = '||i_cost_group_id||','||
4548 'i_cost_type_id = '||i_cost_type_id||','||
4549 'i_low_level_code = '||i_low_level_code||','||
4550 'i_user_id = '||i_user_id||','||
4551 'i_login_id = '||i_login_id||','||
4552 'i_request_id = '||i_request_id||','||
4553 'i_prog_id = '||i_prog_id||','||
4554 'i_prog_appl_id = '||i_prog_appl_id
4555 );
4556 END IF;
4557 /* --- end of auto log --- */
4558
4559 -- Build temporary tables to hold the last txn id and txn category values for each cost_layer_id
4560 IF (i_low_level_code = -1) THEN
4561 -- items without completion
4562 l_stmt_num := 10;
4563 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4564 BULK COLLECT
4565 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4566 FROM mtl_pac_actual_cost_details mpacd
4567 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
4568 FROM mtl_pac_actual_cost_details mpacd1
4569 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4570 FROM cst_pac_period_balances cppb
4571 WHERE cppb.pac_period_id = i_pac_period_id
4572 AND cppb.cost_group_id = i_cost_group_id
4573 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4574 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4575 AND mpacd1.pac_period_id = i_pac_period_id
4576 AND mpacd1.cost_group_id = i_cost_group_id)
4577 AND mpacd.cost_group_id = i_cost_group_id
4578 AND mpacd.pac_period_id = i_pac_period_id
4579 AND NOT EXISTS (SELECT 1
4580 FROM cst_pac_low_level_codes cpllc
4581 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4582 AND cpllc.pac_period_id = i_pac_period_id
4583 AND cpllc.cost_group_id = i_cost_group_id);
4584 ELSE
4585 -- items with completion
4586 l_stmt_num := 20;
4587
4588 -- get the maximum transaction category that has been processed for any item having
4589 -- completions till this point in time.
4590
4591 SELECT max(mpacd.txn_category)
4592 INTO l_max_txn_category
4593 FROM mtl_pac_actual_cost_details mpacd
4594 WHERE mpacd.pac_period_id = i_pac_period_id
4595 AND mpacd.cost_group_id = i_cost_group_id
4596 AND EXISTS (SELECT 1
4597 FROM cst_pac_low_level_codes cpllc
4598 WHERE cpllc.cost_group_id = i_cost_group_id
4599 AND cpllc.pac_period_id = i_pac_period_id
4600 AND cpllc.inventory_item_id = mpacd.inventory_item_id
4601 AND cpllc.low_level_code = i_low_level_code);
4602
4603 IF l_sLog THEN
4604 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module||'.'||l_stmt_num,'l_max_txn_category = '||l_max_txn_category);
4605 END IF;
4606
4607 IF (l_max_txn_category = 7) THEN
4608 -- Rework completions. Pick only items having records with transaction
4609 -- category = 7 to avoid updating variance again wrongly for other cost owned
4610 -- transaction categories
4611
4612 l_stmt_num := 25;
4613 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4614 BULK COLLECT
4615 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4616 FROM mtl_pac_actual_cost_details mpacd
4617 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
4618 FROM mtl_pac_actual_cost_details mpacd1
4619 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4620 FROM cst_pac_period_balances cppb
4621 WHERE cppb.pac_period_id = i_pac_period_id
4622 AND cppb.cost_group_id = i_cost_group_id
4623 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4624 AND mpacd1.txn_category = l_max_txn_category
4625 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4626 AND mpacd1.pac_period_id = i_pac_period_id
4627 AND mpacd1.cost_group_id = i_cost_group_id)
4628 AND mpacd.cost_group_id = i_cost_group_id
4629 AND mpacd.pac_period_id = i_pac_period_id
4630 AND EXISTS (SELECT 1
4631 FROM cst_pac_low_level_codes cpllc
4632 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4633 AND cpllc.low_level_code = i_low_level_code
4634 AND cpllc.pac_period_id = i_pac_period_id
4635 AND cpllc.cost_group_id = i_cost_group_id);
4636 ELSE
4637 -- Non rework completions
4638
4639 l_stmt_num := 30;
4640 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4641 BULK COLLECT
4642 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4643 FROM mtl_pac_actual_cost_details mpacd
4644 WHERE mpacd.transaction_id = (SELECT max(transaction_id)
4645 FROM mtl_pac_actual_cost_details mpacd1
4646 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4647 FROM cst_pac_period_balances cppb
4648 WHERE cppb.pac_period_id = i_pac_period_id
4649 AND cppb.cost_group_id = i_cost_group_id
4650 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4651 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4652 AND mpacd1.pac_period_id = i_pac_period_id
4653 AND mpacd1.cost_group_id = i_cost_group_id)
4654 AND mpacd.cost_group_id = i_cost_group_id
4655 AND mpacd.pac_period_id = i_pac_period_id
4656 AND EXISTS (SELECT 1
4657 FROM cst_pac_low_level_codes cpllc
4658 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4659 AND cpllc.low_level_code = i_low_level_code
4660 AND cpllc.pac_period_id = i_pac_period_id
4661 AND cpllc.cost_group_id = i_cost_group_id);
4662 END IF;
4663 END IF;
4664
4665
4666 /****************************************************************************
4667 Post variance to the last transaction in the last cost owned txn category
4668 processed for that item. Insert rows into mpacd for missing cost elements
4669 ****************************************************************************/
4670
4671 l_stmt_num := 35;
4672 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
4673 UPDATE mtl_pac_actual_cost_details mpacd
4674 SET variance_amount = (SELECT decode (sign(cpic.total_layer_quantity),
4675 0, cpicd.item_balance,
4676 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
4677 0)
4678 FROM cst_pac_item_costs cpic,
4679 cst_pac_item_cost_details cpicd
4680 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
4681 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4682 AND cpicd.cost_element_id = mpacd.cost_element_id
4683 AND cpicd.level_type = mpacd.level_type),
4684 last_update_date = sysdate,
4685 last_updated_by = i_user_id,
4686 last_update_login = i_login_id,
4687 request_id = i_request_id,
4688 program_application_id = i_prog_appl_id,
4689 program_id = i_prog_id,
4690 program_update_date = sysdate
4691 WHERE transaction_id = l_last_txn_id_tbl (l_index)
4692 AND mpacd.cost_group_id = i_cost_group_id
4693 AND mpacd.pac_period_id = i_pac_period_id
4694 AND mpacd.cost_layer_id = l_cost_layer_id_tbl(l_index)
4695 AND (cost_element_id, level_type) = (SELECT cost_element_id, level_type
4696 FROM cst_pac_item_cost_details cpicd
4697 WHERE cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4698 AND cpicd.cost_element_id = mpacd.cost_element_id
4699 AND cpicd.level_type = mpacd.level_type);
4700
4701 l_stmt_num := 40;
4702 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
4703 INSERT INTO mtl_pac_actual_cost_details mpacd
4704 (COST_GROUP_ID,
4705 TRANSACTION_ID,
4706 PAC_PERIOD_ID,
4707 COST_TYPE_ID,
4708 COST_ELEMENT_ID,
4709 LEVEL_TYPE,
4710 INVENTORY_ITEM_ID,
4711 COST_LAYER_ID,
4712 ACTUAL_COST,
4713 USER_ENTERED,
4714 INSERTION_FLAG,
4715 TRANSACTION_COSTED_DATE,
4716 LAST_UPDATE_DATE,
4717 LAST_UPDATED_BY,
4718 CREATION_DATE,
4719 CREATED_BY,
4720 REQUEST_ID,
4721 PROGRAM_APPLICATION_ID,
4722 PROGRAM_ID,
4723 PROGRAM_UPDATE_DATE,
4724 LAST_UPDATE_LOGIN,
4725 VARIANCE_AMOUNT,
4726 TXN_CATEGORY)
4727 (SELECT i_cost_group_id,
4728 l_last_txn_id_tbl (l_index),
4729 i_pac_period_id,
4730 i_cost_type_id,
4731 cpicd.cost_element_id,
4732 cpicd.level_type,
4733 cpic.inventory_item_id,
4734 cpic.cost_layer_id,
4735 0,
4736 'N',
4737 'N',
4738 sysdate,
4739 SYSDATE,
4740 i_user_id,
4741 SYSDATE,
4742 i_user_id,
4743 i_request_id,
4744 i_prog_appl_id,
4745 i_prog_id,
4746 SYSDATE,
4747 i_login_id,
4748 decode (sign(cpic.total_layer_quantity),
4749 0, cpicd.item_balance,
4750 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
4751 0),
4752 l_txn_category_tbl(l_index)
4753 FROM cst_pac_item_cost_details cpicd,
4754 cst_pac_item_costs cpic
4755 WHERE cpicd.cost_layer_id = cpic.cost_layer_id
4756 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4757 AND NOT EXISTS (SELECT 1
4758 FROM mtl_pac_actual_cost_details mpacd1
4759 WHERE mpacd1.transaction_id = l_last_txn_id_tbl (l_index)
4760 AND mpacd1.cost_layer_id = cpicd.cost_layer_id
4761 AND mpacd1.cost_element_id = cpicd.cost_element_id
4762 AND mpacd1.level_type = cpicd.level_type)
4763 );
4764 l_stmt_num := 50;
4765
4766 IF (i_low_level_code = -1) THEN
4767 -- Items that do not have completion
4768 UPDATE cst_pac_item_cost_details cpicd
4769 SET (last_update_date,
4770 last_updated_by,
4771 last_update_login,
4772 request_id,
4773 program_application_id,
4774 program_id,
4775 program_update_date,
4776 item_cost,
4777 item_buy_cost,
4778 item_make_cost,
4779 item_balance,
4780 buy_balance,
4781 make_balance) =
4782 (SELECT sysdate,
4783 i_user_id,
4784 i_login_id,
4785 i_request_id,
4786 i_prog_appl_id,
4787 i_prog_id,
4788 sysdate,
4789 decode (sign(cpic.total_layer_quantity),
4790 0, cpicd.item_cost,
4791 (-1 * sign(cpicd.item_balance)), 0,
4792 cpicd.item_balance / cpic.total_layer_quantity),
4793 decode (sign(cpic.total_layer_quantity),
4794 0, cpicd.item_buy_cost,
4795 (-1 * sign(cpicd.item_balance)), 0,
4796 decode (cpic.buy_quantity,
4797 0, 0,
4798 cpicd.buy_balance / cpic.buy_quantity)),
4799 decode (sign(cpic.total_layer_quantity),
4800 0, cpicd.item_make_cost,
4801 (-1 * sign(cpicd.item_balance)), 0,
4802 decode (cpic.make_quantity,
4803 0, 0,
4804 cpicd.make_balance / cpic.make_quantity)),
4805 decode (sign (cpic.total_layer_quantity),
4806 0, 0,
4807 (-1 * sign(cpicd.item_balance)), 0,
4808 cpicd.item_balance),
4809 decode (sign (cpic.total_layer_quantity),
4810 0, 0,
4811 (-1 * sign(cpicd.item_balance)), 0,
4812 cpicd.buy_balance),
4813 decode (sign (cpic.total_layer_quantity),
4814 0, 0,
4815 (-1 * sign(cpicd.item_balance)), 0,
4816 cpicd.make_balance)
4817 FROM cst_pac_item_costs cpic
4818 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
4819 WHERE cpicd.cost_layer_id IN ( SELECT cost_layer_id
4820 FROM cst_pac_item_costs
4821 WHERE pac_period_id = i_pac_period_id
4822 AND cost_group_id = i_cost_group_id)
4823 AND EXISTS (SELECT 1
4824 FROM cst_pac_period_balances cppb
4825 WHERE cppb.pac_period_id = i_pac_period_id
4826 AND cppb.cost_group_id = i_cost_group_id
4827 AND cppb.cost_layer_id = cpicd.cost_layer_id
4828 AND cppb.cost_element_id = cpicd.cost_element_id
4829 AND cppb.level_type = cpicd.level_type)
4830 AND NOT EXISTS (SELECT 1
4831 FROM cst_pac_low_level_codes cpllc,
4832 cst_pac_item_costs cpic1
4833 WHERE cpllc.inventory_item_id = cpic1.inventory_item_id
4834 AND cpic1.cost_layer_id = cpicd.cost_layer_id
4835 AND cpllc.pac_period_id = i_pac_period_id
4836 AND cpllc.cost_group_id = i_cost_group_id);
4837
4838 l_stmt_num := 60;
4839 UPDATE cst_pac_item_costs cpic
4840 SET (last_updated_by,
4841 last_update_date,
4842 last_update_login,
4843 request_id,
4844 program_application_id,
4845 program_id,
4846 program_update_date,
4847 pl_material,
4848 pl_material_overhead,
4849 pl_resource,
4850 pl_outside_processing,
4851 pl_overhead,
4852 tl_material,
4853 tl_material_overhead,
4854 tl_resource,
4855 tl_outside_processing,
4856 tl_overhead,
4857 material_cost,
4858 material_overhead_cost,
4859 resource_cost,
4860 outside_processing_cost,
4861 overhead_cost,
4862 pl_item_cost,
4863 tl_item_cost,
4864 item_cost,
4865 item_buy_cost,
4866 item_make_cost,
4867 unburdened_cost,
4868 burden_cost) =
4869 (SELECT i_user_id,
4870 sysdate,
4871 i_login_id,
4872 i_request_id,
4873 i_prog_appl_id,
4874 i_prog_id,
4875 sysdate,
4876 pl_material,
4877 pl_material_overhead,
4878 pl_resource,
4879 pl_outside_processing,
4880 pl_overhead,
4881 tl_material,
4882 tl_material_overhead,
4883 tl_resource,
4884 tl_outside_processing,
4885 tl_overhead,
4886 material_cost,
4887 material_overhead_cost,
4888 resource_cost,
4889 outside_processing_cost,
4890 overhead_cost,
4891 pl_item_cost,
4892 tl_item_cost,
4893 item_cost,
4894 item_buy_cost,
4895 item_make_cost,
4896 unburdened_cost,
4897 burden_cost
4898 FROM cst_pac_item_costs_v v
4899 WHERE v.cost_layer_id = cpic.cost_layer_id)
4900 WHERE cpic.cost_group_id = i_cost_group_id
4901 AND cpic.pac_period_id = i_pac_period_id
4902 AND EXISTS (SELECT 1
4903 FROM cst_pac_period_balances cppb
4904 WHERE cppb.pac_period_id = i_pac_period_id
4905 AND cppb.cost_group_id = i_cost_group_id
4906 AND cppb.cost_layer_id = cpic.cost_layer_id)
4907 AND NOT EXISTS (SELECT 1
4908 FROM cst_pac_low_level_codes cpllc
4909 WHERE cpllc.inventory_item_id = cpic.inventory_item_id
4910 AND cpllc.pac_period_id = i_pac_period_id
4911 AND cpllc.cost_group_id = i_cost_group_id)
4912 AND EXISTS
4913 (SELECT 'there is detail cost'
4914 FROM cst_pac_item_cost_details cpicd
4915 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
4916 ELSE
4917 -- low_level_code <> -1; items having completion
4918
4919 l_stmt_num := 70;
4920 UPDATE cst_pac_item_cost_details cpicd
4921 SET (last_update_date,
4922 last_updated_by,
4923 last_update_login,
4924 request_id,
4925 program_application_id,
4926 program_id,
4927 program_update_date,
4928 item_cost,
4929 item_buy_cost,
4930 item_make_cost,
4931 item_balance,
4932 buy_balance,
4933 make_balance) =
4934 (SELECT sysdate,
4935 i_user_id,
4936 i_login_id,
4937 i_request_id,
4938 i_prog_appl_id,
4939 i_prog_id,
4940 sysdate,
4941 decode (sign(cpic.total_layer_quantity),
4942 0, cpicd.item_cost,
4943 (-1 * sign(cpicd.item_balance)), 0,
4944 cpicd.item_balance / cpic.total_layer_quantity),
4945 decode (sign(cpic.total_layer_quantity),
4946 0, cpicd.item_buy_cost,
4947 (-1 * sign(cpicd.item_balance)), 0,
4948 decode (cpic.buy_quantity,
4949 0, 0,
4950 cpicd.buy_balance / cpic.buy_quantity)),
4951 decode (sign(cpic.total_layer_quantity),
4952 0, cpicd.item_make_cost,
4953 (-1 * sign(cpicd.item_balance)), 0,
4954 decode (cpic.make_quantity,
4955 0, 0,
4956 cpicd.make_balance / cpic.make_quantity)),
4957 decode (sign (cpic.total_layer_quantity),
4958 0, 0,
4959 (-1 * sign(cpicd.item_balance)), 0,
4960 cpicd.item_balance),
4961 decode (sign (cpic.total_layer_quantity),
4962 0, 0,
4963 (-1 * sign(cpicd.item_balance)), 0,
4964 cpicd.buy_balance),
4965 decode (sign (cpic.total_layer_quantity),
4966 0, 0,
4967 (-1 * sign(cpicd.item_balance)), 0,
4968 cpicd.make_balance)
4969 FROM cst_pac_item_costs cpic
4970 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
4971 WHERE cpicd.cost_layer_id IN ( SELECT cost_layer_id
4972 FROM cst_pac_item_costs
4973 WHERE pac_period_id = i_pac_period_id
4974 AND cost_group_id = i_cost_group_id)
4975 AND EXISTS (SELECT 1
4976 FROM cst_pac_period_balances cppb
4977 WHERE cppb.pac_period_id = i_pac_period_id
4978 AND cppb.cost_group_id = i_cost_group_id
4979 AND cppb.cost_layer_id = cpicd.cost_layer_id
4980 AND cppb.cost_element_id = cpicd.cost_element_id
4981 AND cppb.level_type = cpicd.level_type)
4982 AND EXISTS (SELECT 1
4983 FROM cst_pac_low_level_codes cpllc,
4984 cst_pac_item_costs cpic1
4985 WHERE cpllc.low_level_code = i_low_level_code
4986 AND cpllc.pac_period_id = i_pac_period_id
4987 AND cpllc.cost_group_id = i_cost_group_id
4988 AND cpllc.inventory_item_id = cpic1.inventory_item_id
4989 AND cpic1.cost_layer_id = cpicd.cost_layer_id);
4990
4991 l_stmt_num := 80;
4992 UPDATE cst_pac_item_costs cpic
4993 SET (last_updated_by,
4994 last_update_date,
4995 last_update_login,
4996 request_id,
4997 program_application_id,
4998 program_id,
4999 program_update_date,
5000 pl_material,
5001 pl_material_overhead,
5002 pl_resource,
5003 pl_outside_processing,
5004 pl_overhead,
5005 tl_material,
5006 tl_material_overhead,
5007 tl_resource,
5008 tl_outside_processing,
5009 tl_overhead,
5010 material_cost,
5011 material_overhead_cost,
5012 resource_cost,
5013 outside_processing_cost,
5014 overhead_cost,
5015 pl_item_cost,
5016 tl_item_cost,
5017 item_cost,
5018 item_buy_cost,
5019 item_make_cost,
5020 unburdened_cost,
5021 burden_cost) =
5022 (SELECT i_user_id,
5023 sysdate,
5024 i_login_id,
5025 i_request_id,
5026 i_prog_appl_id,
5027 i_prog_id,
5028 sysdate,
5029 pl_material,
5030 pl_material_overhead,
5031 pl_resource,
5032 pl_outside_processing,
5033 pl_overhead,
5034 tl_material,
5035 tl_material_overhead,
5036 tl_resource,
5037 tl_outside_processing,
5038 tl_overhead,
5039 material_cost,
5040 material_overhead_cost,
5041 resource_cost,
5042 outside_processing_cost,
5043 overhead_cost,
5044 pl_item_cost,
5045 tl_item_cost,
5046 item_cost,
5047 item_buy_cost,
5048 item_make_cost,
5049 unburdened_cost,
5050 burden_cost
5051 FROM cst_pac_item_costs_v v
5052 WHERE v.cost_layer_id = cpic.cost_layer_id)
5053 WHERE cpic.cost_group_id = i_cost_group_id
5054 AND cpic.pac_period_id = i_pac_period_id
5055 AND EXISTS (SELECT 1
5056 FROM cst_pac_period_balances cppb
5057 WHERE cppb.pac_period_id = i_pac_period_id
5058 AND cppb.cost_group_id = i_cost_group_id
5059 AND cppb.cost_layer_id = cpic.cost_layer_id)
5060 AND EXISTS (SELECT 1
5061 FROM cst_pac_low_level_codes cpllc
5062 WHERE cpllc.low_level_code = i_low_level_code
5063 AND cpllc.inventory_item_id = cpic.inventory_item_id
5064 AND cpllc.pac_period_id = i_pac_period_id
5065 AND cpllc.cost_group_id = i_cost_group_id)
5066 AND EXISTS
5067 (SELECT 'there is detail cost'
5068 FROM cst_pac_item_cost_details cpicd
5069 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
5070 END IF;
5071
5072 /* --- start of auto log --- */
5073 <<out_arg_log>>
5074
5075 IF l_plog THEN
5076 fnd_log.string(
5077 fnd_log.level_procedure,
5078 l_module||'.'||l_stmt_num,
5079 'Exiting CSTPPWAC.calc_periodic_cost with '||
5080 'o_err_num = '||o_err_num||','||
5081 'o_err_code = '||o_err_code||','||
5082 'o_err_msg = '||o_err_msg
5083 );
5084 END IF;
5085 /* --- end of auto log --- */
5086 EXCEPTION
5087 WHEN OTHERS THEN
5088 ROLLBACK;
5089 IF l_uLog THEN
5090 fnd_message.set_name('BOM','CST_UNEXPECTED');
5091 fnd_message.set_token('SQLERRM',SQLERRM);
5092 fnd_msg_pub.add;
5093 fnd_log.message(
5094 fnd_log.level_unexpected,
5095 l_module||'.'||l_stmt_num,
5096 FALSE
5097 );
5098 END IF;
5099 o_err_num := SQLCODE;
5100 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
5101 /* --- end of auto log --- */
5102 END calc_periodic_cost;
5103
5104 -- PROCEDURE
5105 -- calculate_periodic_cost
5106 --
5107 PROCEDURE calculate_periodic_cost (i_pac_period_id IN NUMBER,
5108 i_cost_group_id IN NUMBER,
5109 i_cost_type_id IN NUMBER,
5110 i_low_level_code IN NUMBER,
5111 i_item_id IN NUMBER,
5112 i_user_id IN NUMBER,
5113 i_login_id IN NUMBER,
5114 i_request_id IN NUMBER,
5115 i_prog_id IN NUMBER,
5116 i_prog_appl_id IN NUMBER,
5117 o_err_num OUT NOCOPY NUMBER,
5118 o_err_code OUT NOCOPY VARCHAR2,
5119 o_err_msg OUT NOCOPY VARCHAR2)
5120 IS
5121 l_stmt_num NUMBER;
5122 /* --- start of auto log --- */
5123 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.calculate_periodic_cost';
5124 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
5125 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
5126 fnd_log.TEST(fnd_log.level_unexpected, l_module);
5127 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
5128 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
5129 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
5130 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
5131 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
5132
5133 /* --- end of auto log --- */
5134 BEGIN
5135 l_stmt_num := 10;
5136 /* --- start of auto log --- */
5137 IF l_plog THEN
5138 fnd_log.string(
5139 fnd_log.level_procedure,
5140 l_module||'.'||l_stmt_num,
5141 'Entering CSTPPWAC.calculate_periodic_cost with '||
5142 'i_cost_group_id = '||i_cost_group_id||','||
5143 'i_cost_type_id = '||i_cost_type_id||','||
5144 'i_low_level_code = '||i_low_level_code||','||
5145 'i_item_id = '||i_item_id||','||
5146 'i_user_id = '||i_user_id||','||
5147 'i_login_id = '||i_login_id||','||
5148 'i_request_id = '||i_request_id||','||
5149 'i_prog_id = '||i_prog_id||','||
5150 'i_prog_appl_id = '||i_prog_appl_id
5151 );
5152 END IF;
5153 /* --- end of auto log --- */
5154
5155 /* The procedures calc_periodic_cost, calc_item_periodic_cost have similar logic and any change in one
5156 should be synchronized with similar change in the other. "calc_periodic_cost" processes all items and
5157 "calc_item_periodic_cost" processes for i_item_id. For performance, nvl condition is removed so that index on
5158 inventory_item_id is utilised when i_item_id is passed */
5159 IF (i_item_id IS NULL) THEN
5160 l_stmt_num := 20;
5161 calc_periodic_cost(i_pac_period_id => i_pac_period_id,
5162 i_cost_group_id => i_cost_group_id,
5163 i_cost_type_id => i_cost_type_id,
5164 i_low_level_code => i_low_level_code,
5165 i_user_id => i_user_id,
5166 i_login_id => i_login_id,
5167 i_request_id => i_request_id,
5168 i_prog_id => i_prog_id,
5169 i_prog_appl_id => i_prog_appl_id,
5170 o_err_num => o_err_num,
5171 o_err_code => o_err_code,
5172 o_err_msg => o_err_msg);
5173 ELSE
5174 l_stmt_num := 30;
5175 calc_item_periodic_cost(i_pac_period_id => i_pac_period_id,
5176 i_cost_group_id => i_cost_group_id,
5177 i_cost_type_id => i_cost_type_id,
5178 i_low_level_code => i_low_level_code,
5179 i_item_id => i_item_id,
5180 i_user_id => i_user_id,
5181 i_login_id => i_login_id,
5182 i_request_id => i_request_id,
5183 i_prog_id => i_prog_id,
5184 i_prog_appl_id => i_prog_appl_id,
5185 o_err_num => o_err_num,
5186 o_err_code => o_err_code,
5187 o_err_msg => o_err_msg);
5188 END IF;
5189 /* --- start of auto log --- */
5190 <<out_arg_log>>
5191
5192 IF l_plog THEN
5193 fnd_log.string(
5194 fnd_log.level_procedure,
5195 l_module||'.'||l_stmt_num,
5196 'Exiting CSTPPWAC.calculate_periodic_cost with '||
5197 'o_err_num = '||o_err_num||','||
5198 'o_err_code = '||o_err_code||','||
5199 'o_err_msg = '||o_err_msg
5200 );
5201 END IF;
5202 /* --- end of auto log --- */
5203 END calculate_periodic_cost;
5204
5205 -- PROCEDURE
5206 -- update_cppb
5207 --
5208 PROCEDURE update_cppb (i_pac_period_id IN NUMBER,
5209 i_cost_group_id IN NUMBER,
5210 i_txn_category IN NUMBER,
5211 i_low_level_code IN NUMBER,
5212 i_user_id IN NUMBER,
5213 i_login_id IN NUMBER,
5214 i_request_id IN NUMBER,
5215 i_prog_id IN NUMBER,
5216 i_prog_appl_id IN NUMBER,
5217 o_err_num OUT NOCOPY NUMBER,
5218 o_err_code OUT NOCOPY VARCHAR2,
5219 o_err_msg OUT NOCOPY VARCHAR2)
5220 IS
5221 l_stmt_num NUMBER;
5222
5223 /* --- start of auto log --- */
5224 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_cppb';
5225 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
5226 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
5227 fnd_log.TEST(fnd_log.level_unexpected, l_module);
5228 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
5229 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
5230 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
5231 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
5232 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
5233
5234 /* --- end of auto log --- */
5235 BEGIN
5236 /* --- start of auto log --- */
5237 IF l_plog THEN
5238 fnd_log.string(
5239 fnd_log.level_procedure,
5240 l_module||'.'||l_stmt_num,
5241 'Entering CSTPPWAC.update_cppb with '||
5242 'i_cost_group_id = '||i_cost_group_id||','||
5243 'i_txn_category = '||i_txn_category||','||
5244 'i_low_level_code = '||i_low_level_code||','||
5245 'i_user_id = '||i_user_id||','||
5246 'i_login_id = '||i_login_id||','||
5247 'i_request_id = '||i_request_id||','||
5248 'i_prog_id = '||i_prog_id||','||
5249 'i_prog_appl_id = '||i_prog_appl_id
5250 );
5251 END IF;
5252 /* --- end of auto log --- */
5253
5254 -- Update total period quantity, balance, periodic cost and variance into cppb
5255 IF (i_low_level_code = -2) THEN
5256
5257 -- Called after group1_trx cursor, which processes both items with completion and
5258 -- items without completion. So we need to update cppb for all items irrespective
5259 -- of whether they have completions or not.
5260
5261 l_stmt_num := 10;
5262 UPDATE CST_PAC_PERIOD_BALANCES cppb
5263 SET (last_updated_by,
5264 last_update_date,
5265 last_update_login,
5266 request_id,
5267 program_application_id,
5268 program_id,
5269 program_update_date,
5270 period_balance,
5271 period_quantity,
5272 periodic_cost,
5273 variance_amount) =
5274 (SELECT i_user_id,
5275 sysdate,
5276 i_login_id,
5277 i_request_id,
5278 i_prog_appl_id,
5279 i_prog_id,
5280 sysdate,
5281 cpicd.item_balance,
5282 cpic.total_layer_quantity,
5283 cpicd.item_cost,
5284 (SELECT /*+ INDEX(MPACD MTL_PAC_ACTUAL_COST_DETAILS_N1)*/
5285 sum (nvl (mpacd.variance_amount, 0))
5286 FROM mtl_pac_actual_cost_details mpacd
5287 WHERE mpacd.txn_category = i_txn_category
5288 AND mpacd.inventory_item_id = cppb.inventory_item_id
5289 AND mpacd.pac_period_id = i_pac_period_id
5290 AND mpacd.cost_group_id = i_cost_group_id
5291 AND mpacd.cost_layer_id = cppb.cost_layer_id
5292 AND mpacd.cost_element_id = cppb.cost_element_id
5293 AND mpacd.level_type = cppb.level_type)
5294 FROM cst_pac_item_cost_details cpicd,
5295 cst_pac_item_costs cpic
5296 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
5297 AND cppb.cost_layer_id = cpicd.cost_layer_id
5298 AND cppb.cost_element_id = cpicd.cost_element_id
5299 AND cppb.level_type = cpicd.level_type)
5300 WHERE cppb.pac_period_id = i_pac_period_id
5301 AND cppb.cost_group_id = i_cost_group_id
5302 AND cppb.txn_category = i_txn_category
5303 AND i_txn_category = (SELECT /*+ INDEX(MPACD1 MTL_PAC_ACTUAL_COST_DETAILS_N1)*/
5304 max (txn_category)
5305 FROM MTL_PAC_ACTUAL_COST_DETAILS MPACD1
5306 WHERE MPACD1.pac_period_id = i_pac_period_id
5307 AND MPACD1.cost_group_id = i_cost_group_id
5308 AND MPACD1.inventory_item_id = cppb.inventory_item_id)
5309 AND EXISTS (SELECT /*+ INDEX(CPICD1 CST_PAC_ITEM_COST_DETAILS_U1)*/
5310 1
5311 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
5312 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
5313 AND cppb.cost_element_id = cpicd1.cost_element_id
5314 AND cppb.level_type = cpicd1.level_type);
5315
5316 ELSIF (i_low_level_code = -1) THEN
5317
5318 -- Items without completion
5319
5320 l_stmt_num := 20;
5321 UPDATE CST_PAC_PERIOD_BALANCES cppb
5322 SET (last_updated_by,
5323 last_update_date,
5324 last_update_login,
5325 request_id,
5326 program_application_id,
5327 program_id,
5328 program_update_date,
5329 period_balance,
5330 period_quantity,
5331 periodic_cost,
5332 variance_amount) =
5333 (SELECT i_user_id,
5334 sysdate,
5335 i_login_id,
5336 i_request_id,
5337 i_prog_appl_id,
5338 i_prog_id,
5339 sysdate,
5340 cpicd.item_balance,
5341 cpic.total_layer_quantity,
5342 cpicd.item_cost,
5343 (SELECT /*+ INDEX(MPACD MTL_PAC_ACTUAL_COST_DETAILS_N1)*/
5344 sum (nvl (mpacd.variance_amount, 0))
5345 FROM mtl_pac_actual_cost_details mpacd
5346 WHERE mpacd.txn_category = i_txn_category
5347 AND mpacd.inventory_item_id = cppb.inventory_item_id
5348 AND mpacd.pac_period_id = i_pac_period_id
5349 AND mpacd.cost_group_id = i_cost_group_id
5350 AND mpacd.cost_layer_id = cppb.cost_layer_id
5351 AND mpacd.cost_element_id = cppb.cost_element_id
5352 AND mpacd.level_type = cppb.level_type)
5353 FROM cst_pac_item_cost_details cpicd,
5354 cst_pac_item_costs cpic
5355 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
5356 AND cppb.cost_layer_id = cpicd.cost_layer_id
5357 AND cppb.cost_element_id = cpicd.cost_element_id
5358 AND cppb.level_type = cpicd.level_type)
5359 WHERE cppb.pac_period_id = i_pac_period_id
5360 AND cppb.cost_group_id = i_cost_group_id
5361 AND cppb.txn_category = i_txn_category
5362 AND i_txn_category = (SELECT /*+ INDEX(MPACD1 MTL_PAC_ACTUAL_COST_DETAILS_N1)*/
5363 max (txn_category)
5364 FROM MTL_PAC_ACTUAL_COST_DETAILS MPACD1
5365 WHERE MPACD1.pac_period_id = i_pac_period_id
5366 AND MPACD1.cost_group_id = i_cost_group_id
5367 AND MPACD1.inventory_item_id = cppb.inventory_item_id)
5368 AND EXISTS (SELECT /*+ INDEX(CPICD1 CST_PAC_ITEM_COST_DETAILS_U1)*/
5369 1
5370 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
5371 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
5372 AND cppb.cost_element_id = cpicd1.cost_element_id
5373 AND cppb.level_type = cpicd1.level_type)
5374 AND NOT EXISTS (SELECT 1
5375 FROM cst_pac_low_level_codes cpllc
5376 WHERE cpllc.inventory_item_id = cppb.inventory_item_id
5377 AND cpllc.pac_period_id = i_pac_period_id
5378 AND cpllc.cost_group_id = i_cost_group_id);
5379 ELSIF (i_low_level_code <> -1) THEN
5380
5381 -- Items with completion
5382
5383 l_stmt_num := 30;
5384 UPDATE CST_PAC_PERIOD_BALANCES cppb
5385 SET (last_updated_by,
5386 last_update_date,
5387 last_update_login,
5388 request_id,
5389 program_application_id,
5390 program_id,
5391 program_update_date,
5392 period_balance,
5393 period_quantity,
5394 periodic_cost,
5395 variance_amount) =
5396 (SELECT /*+ INDEX(CPICD CST_PAC_ITEM_COST_DETAILS_U1)*/
5397 i_user_id,
5398 sysdate,
5399 i_login_id,
5400 i_request_id,
5401 i_prog_appl_id,
5402 i_prog_id,
5403 sysdate,
5404 cpicd.item_balance,
5405 cpic.total_layer_quantity,
5406 cpicd.item_cost,
5407 (select /*+ INDEX(MPACD MTL_PAC_ACTUAL_COST_DETAILS_N1)*/
5408 sum (nvl (mpacd.variance_amount, 0))
5409 from mtl_pac_actual_cost_details mpacd
5410 where mpacd.txn_category = i_txn_category
5411 and mpacd.inventory_item_id = cppb.inventory_item_id
5412 and mpacd.pac_period_id = i_pac_period_id
5413 and mpacd.cost_group_id = i_cost_group_id
5414 and mpacd.cost_layer_id = cppb.cost_layer_id
5415 and mpacd.cost_element_id = cppb.cost_element_id
5416 and mpacd.level_type = cppb.level_type)
5417 FROM cst_pac_item_cost_details cpicd,
5418 cst_pac_item_costs cpic
5419 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
5420 AND cppb.cost_layer_id = cpicd.cost_layer_id
5421 AND cppb.cost_element_id = cpicd.cost_element_id
5422 AND cppb.level_type = cpicd.level_type)
5423 WHERE cppb.pac_period_id = i_pac_period_id
5424 AND cppb.cost_group_id = i_cost_group_id
5425 AND cppb.txn_category = i_txn_category
5426 AND i_txn_category = (SELECT /*+ INDEX(MPACD1 MTL_PAC_ACTUAL_COST_DETAILS_N1)*/
5427 max (txn_category)
5428 FROM MTL_PAC_ACTUAL_COST_DETAILS MPACD1
5429 WHERE MPACD1.pac_period_id = i_pac_period_id
5430 AND MPACD1.cost_group_id = i_cost_group_id
5431 AND MPACD1.inventory_item_id = cppb.inventory_item_id)
5432 AND EXISTS (SELECT /*+ INDEX(CPICD1 CST_PAC_ITEM_COST_DETAILS_U1)*/
5433 1
5434 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
5435 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
5436 AND cppb.cost_element_id = cpicd1.cost_element_id
5437 AND cppb.level_type = cpicd1.level_type)
5438 AND EXISTS (SELECT 1
5439 FROM cst_pac_low_level_codes cpllc
5440 WHERE cpllc.inventory_item_id = cppb.inventory_item_id
5441 AND cpllc.low_level_code = i_low_level_code
5442 AND cpllc.pac_period_id = i_pac_period_id
5443 AND cpllc.cost_group_id = i_cost_group_id);
5444 END IF;
5445
5446 /* --- start of auto log --- */
5447 <<out_arg_log>>
5448
5449 IF l_plog THEN
5450 fnd_log.string(
5451 fnd_log.level_procedure,
5452 l_module||'.'||l_stmt_num,
5453 'Exiting CSTPPWAC.update_cppb with '||
5454 'o_err_num = '||o_err_num||','||
5455 'o_err_code = '||o_err_code||','||
5456 'o_err_msg = '||o_err_msg
5457 );
5458 END IF;
5459 /* --- end of auto log --- */
5460 EXCEPTION
5461 WHEN OTHERS THEN
5462 ROLLBACK;
5463 IF l_uLog THEN
5464 fnd_message.set_name('BOM','CST_UNEXPECTED');
5465 fnd_message.set_token('SQLERRM',SQLERRM);
5466 fnd_msg_pub.add;
5467 fnd_log.message(
5468 fnd_log.level_unexpected,
5469 l_module||'.'||l_stmt_num,
5470 FALSE
5471 );
5472 END IF;
5473 o_err_num := SQLCODE;
5474 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
5475 /* --- end of auto log --- */
5476 END update_cppb;
5477
5478 -- PROCEDURE
5479 -- update_item_cppb, to be used in iPAC during rollup
5480 --
5481
5482 PROCEDURE update_item_cppb (i_pac_period_id IN NUMBER,
5483 i_cost_group_id IN NUMBER,
5484 i_txn_category IN NUMBER,
5485 i_item_id IN NUMBER,
5486 i_user_id IN NUMBER,
5487 i_login_id IN NUMBER,
5488 i_request_id IN NUMBER,
5489 i_prog_id IN NUMBER,
5490 i_prog_appl_id IN NUMBER,
5491 o_err_num OUT NOCOPY NUMBER,
5492 o_err_code OUT NOCOPY VARCHAR2,
5493 o_err_msg OUT NOCOPY VARCHAR2)
5494 IS
5495 l_stmt_num NUMBER;
5496
5497 /* --- start of auto log --- */
5498 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_item_cppb';
5499 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
5500 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
5501 fnd_log.TEST(fnd_log.level_unexpected, l_module);
5502 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
5503 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
5504 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
5505 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
5506 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
5507
5508 /* --- end of auto log --- */
5509 BEGIN
5510 /* --- start of auto log --- */
5511 IF l_plog THEN
5512 fnd_log.string(
5513 fnd_log.level_procedure,
5514 l_module||'.'||l_stmt_num,
5515 'Entering CSTPPWAC.update_item_cppb with '||
5516 'i_cost_group_id = '||i_cost_group_id||','||
5517 'i_txn_category = '||i_txn_category||','||
5518 'i_item_id = '||i_item_id||','||
5519 'i_user_id = '||i_user_id||','||
5520 'i_login_id = '||i_login_id||','||
5521 'i_request_id = '||i_request_id||','||
5522 'i_prog_id = '||i_prog_id||','||
5523 'i_prog_appl_id = '||i_prog_appl_id
5524 );
5525 END IF;
5526 /* --- end of auto log --- */
5527
5528 l_stmt_num := 10;
5529 UPDATE CST_PAC_PERIOD_BALANCES cppb
5530 SET (last_updated_by,
5531 last_update_date,
5532 last_update_login,
5533 request_id,
5534 program_application_id,
5535 program_id,
5536 program_update_date,
5537 period_balance,
5538 period_quantity,
5539 periodic_cost,
5540 variance_amount) =
5541 (SELECT i_user_id,
5542 sysdate,
5543 i_login_id,
5544 i_request_id,
5545 i_prog_appl_id,
5546 i_prog_id,
5547 sysdate,
5548 cpicd.item_balance,
5549 cpic.total_layer_quantity,
5550 cpicd.item_cost,
5551 (SELECT sum (nvl (mpacd.variance_amount, 0))
5552 FROM mtl_pac_actual_cost_details mpacd
5553 WHERE mpacd.txn_category = cppb.txn_category
5554 AND mpacd.inventory_item_id = cppb.inventory_item_id
5555 AND mpacd.pac_period_id = cppb.pac_period_id
5556 AND mpacd.cost_group_id = cppb.cost_group_id
5557 AND mpacd.cost_layer_id = cppb.cost_layer_id
5558 AND mpacd.cost_element_id = cppb.cost_element_id
5559 AND mpacd.level_type = cppb.level_type)
5560 FROM cst_pac_item_cost_details cpicd,
5561 cst_pac_item_costs cpic
5562 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
5563 AND cppb.cost_layer_id = cpicd.cost_layer_id
5564 AND cppb.cost_element_id = cpicd.cost_element_id
5565 AND cppb.level_type = cpicd.level_type)
5566 WHERE cppb.pac_period_id = i_pac_period_id
5567 AND cppb.cost_group_id = i_cost_group_id
5568 AND cppb.txn_category = i_txn_category
5569 AND cppb.inventory_item_id = i_item_id
5570 AND i_txn_category = (SELECT max (txn_category)
5571 FROM MTL_PAC_ACTUAL_COST_DETAILS
5572 WHERE pac_period_id = i_pac_period_id
5573 AND cost_group_id = i_cost_group_id
5574 AND inventory_item_id = cppb.inventory_item_id)
5575 AND EXISTS (SELECT 1
5576 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
5577 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
5578 AND cppb.cost_element_id = cpicd1.cost_element_id
5579 AND cppb.level_type = cpicd1.level_type);
5580
5581 /* --- start of auto log --- */
5582 <<out_arg_log>>
5583
5584 IF l_plog THEN
5585 fnd_log.string(
5586 fnd_log.level_procedure,
5587 l_module||'.'||l_stmt_num,
5588 'Exiting CSTPPWAC.update_item_cppb with '||
5589 'o_err_num = '||o_err_num||','||
5590 'o_err_code = '||o_err_code||','||
5591 'o_err_msg = '||o_err_msg
5592 );
5593 END IF;
5594 /* --- end of auto log --- */
5595 EXCEPTION
5596 WHEN OTHERS THEN
5597 ROLLBACK;
5598 IF l_uLog THEN
5599 fnd_message.set_name('BOM','CST_UNEXPECTED');
5600 fnd_message.set_token('SQLERRM',SQLERRM);
5601 fnd_msg_pub.add;
5602 fnd_log.message(
5603 fnd_log.level_unexpected,
5604 l_module||'.'||l_stmt_num,
5605 FALSE
5606 );
5607 END IF;
5608 o_err_num := SQLCODE;
5609 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
5610 /* --- end of auto log --- */
5611 END update_item_cppb;
5612
5613 -- PROCEDURE
5614 -- insert_ending_balance
5615 --
5616 PROCEDURE insert_ending_balance (i_pac_period_id IN NUMBER,
5617 i_cost_group_id IN NUMBER,
5618 i_user_id IN NUMBER,
5619 i_login_id IN NUMBER,
5620 i_request_id IN NUMBER,
5621 i_prog_id IN NUMBER,
5622 i_prog_appl_id IN NUMBER,
5623 o_err_num OUT NOCOPY NUMBER,
5624 o_err_code OUT NOCOPY VARCHAR2,
5625 o_err_msg OUT NOCOPY VARCHAR2)
5626 IS
5627 l_stmt_num NUMBER;
5628
5629 /* --- start of auto log --- */
5630 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_ending_balance';
5631 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
5632 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
5633 fnd_log.TEST(fnd_log.level_unexpected, l_module);
5634 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
5635 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
5636 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
5637 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
5638 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
5639
5640 /* --- end of auto log --- */
5641 BEGIN
5642 /* --- start of auto log --- */
5643 IF l_plog THEN
5644 fnd_log.string(
5645 fnd_log.level_procedure,
5646 l_module||'.'||l_stmt_num,
5647 'Entering CSTPPWAC.insert_ending_balance with '||
5648 'i_cost_group_id = '||i_cost_group_id||','||
5649 'i_user_id = '||i_user_id||','||
5650 'i_login_id = '||i_login_id||','||
5651 'i_request_id = '||i_request_id||','||
5652 'i_prog_id = '||i_prog_id||','||
5653 'i_prog_appl_id = '||i_prog_appl_id
5654 );
5655 END IF;
5656 /* --- end of auto log --- */
5657
5658 l_stmt_num := 10;
5659 INSERT INTO cst_pac_period_balances (
5660 pac_period_id,
5661 cost_group_id,
5662 inventory_item_id,
5663 cost_layer_id,
5664 quantity_layer_id,
5665 cost_element_id,
5666 level_type,
5667 txn_category,
5668 txn_category_qty,
5669 txn_category_value,
5670 period_quantity,
5671 periodic_cost,
5672 period_balance,
5673 variance_amount,
5674 last_update_date,
5675 last_updated_by,
5676 last_update_login,
5677 created_by,
5678 creation_date,
5679 request_id,
5680 program_application_id,
5681 program_id,
5682 program_update_date)
5683 (SELECT i_pac_period_id,
5684 i_cost_group_id,
5685 cpic.inventory_item_id,
5686 cpic.cost_layer_id,
5687 cpql.quantity_layer_id,
5688 cpicd.cost_element_id,
5689 cpicd.level_type,
5690 10, -- txn_category
5691 0,
5692 0,
5693 cpic.total_layer_quantity,
5694 cpicd.item_cost,
5695 cpicd.item_balance,
5696 0,
5697 sysdate,
5698 i_user_id,
5699 i_login_id,
5700 i_user_id,
5701 sysdate,
5702 i_request_id,
5703 i_prog_appl_id,
5704 i_prog_id,
5705 sysdate
5706 FROM cst_pac_item_costs cpic,
5707 cst_pac_item_cost_details cpicd,
5708 cst_pac_quantity_layers cpql
5709 WHERE cpic.pac_period_id = i_pac_period_id
5710 AND cpic.cost_group_id = i_cost_group_id
5711 AND cpicd.cost_layer_id = cpic.cost_layer_id
5712 AND cpql.cost_layer_id = cpic.cost_layer_id
5713 -- Insert ending balance records in CPPB only for asset items, i.e. only for items
5714 -- which already have atleast one record in CPPB
5715 AND exists (select 1
5716 from cst_pac_period_balances cppb1
5717 where cppb1.inventory_item_id = cpic.inventory_item_id
5718 and cppb1.cost_group_id = cpic.cost_group_id
5719 and cppb1.pac_period_id = cpic.pac_period_id));
5720
5721 /* --- start of auto log --- */
5722 <<out_arg_log>>
5723
5724 IF l_plog THEN
5725 fnd_log.string(
5726 fnd_log.level_procedure,
5727 l_module||'.'||l_stmt_num,
5728 'Exiting CSTPPWAC.insert_ending_balance with '||
5729 'o_err_num = '||o_err_num||','||
5730 'o_err_code = '||o_err_code||','||
5731 'o_err_msg = '||o_err_msg
5732 );
5733 END IF;
5734 /* --- end of auto log --- */
5735 EXCEPTION
5736 WHEN OTHERS THEN
5737 ROLLBACK;
5738 IF l_uLog THEN
5739 fnd_message.set_name('BOM','CST_UNEXPECTED');
5740 fnd_message.set_token('SQLERRM',SQLERRM);
5741 fnd_msg_pub.add;
5742 fnd_log.message(
5743 fnd_log.level_unexpected,
5744 l_module||'.'||l_stmt_num,
5745 FALSE
5746 );
5747 END IF;
5748 o_err_num := SQLCODE;
5749 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
5750 /* --- end of auto log --- */
5751 END insert_ending_balance;
5752
5753 END CSTPPWAC;