[Home] [Help]
PACKAGE BODY: APPS.CSTPPWAC
Source
1 PACKAGE BODY CSTPPWAC AS
2 /* $Header: CSTPWACB.pls 120.28.12010000.6 2008/11/29 02:36:19 anjha 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)) 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 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, 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 ELSE
3196 /* Value Change transaction - treated like cost owned transactions */
3197 l_stmt_num := 90;
3198
3199 FOR x in
3200 (select actual_cost, cost_element_id, level_type
3201 from mtl_pac_actual_cost_details mpacd
3202 where mpacd.cost_layer_id = i_cost_layer_id
3203 and mpacd.cost_group_id = i_cost_group_id
3204 and mpacd.transaction_id = i_txn_id)
3205 LOOP
3206 DECLARE
3207 l_index NUMBER;
3208 l_count NUMBER;
3209 BEGIN
3210 l_stmt_num := 100;
3211 l_index := -1;
3212 IF (CSTPPINV.l_item_start_index_tbl.EXISTS (i_item_id)) THEN /* item already exists */
3213 FOR l_count IN (CSTPPINV.l_item_start_index_tbl(i_item_id))..(CSTPPINV.l_item_end_index_tbl(i_item_id))
3214 LOOP
3215 IF (CSTPPINV.l_item_id_tbl(l_count) = i_item_id AND
3216 CSTPPINV.l_cost_element_id_tbl(l_count) = x.cost_element_id AND
3217 CSTPPINV.l_level_type_tbl(l_count) = x.level_type) THEN
3218 l_index := l_count;
3219 END IF;
3220 END LOOP;
3221 ELSE /* new item */
3222 CSTPPINV.l_item_start_index_tbl (i_item_id) := CSTPPINV.l_item_id_tbl.COUNT + 1;
3223 CSTPPINV.l_item_end_index_tbl (i_item_id) := CSTPPINV.l_item_id_tbl.COUNT + 1;
3224 END IF;
3225 l_stmt_num := 110;
3226 IF (l_index = -1) THEN /* item-cost_element-level_type combination not found: Insert*/
3227 l_index := CSTPPINV.l_item_id_tbl.COUNT + 1;
3228 CSTPPINV.l_item_id_tbl(l_index) := i_item_id;
3229 CSTPPINV.l_cost_layer_id_tbl(l_index) := i_cost_layer_id;
3230 CSTPPINV.l_qty_layer_id_tbl(l_index) := i_qty_layer_id;
3231 CSTPPINV.l_cost_element_id_tbl(l_index) := x.cost_element_id;
3232 CSTPPINV.l_level_type_tbl(l_index) := x.level_type;
3233 CSTPPINV.l_txn_category_tbl (l_index) := i_txn_category;
3234
3235 CSTPPINV.l_item_balance_tbl(l_index) := x.actual_cost;
3236 CSTPPINV.l_make_balance_tbl(l_index) := 0;
3237 CSTPPINV.l_buy_balance_tbl(l_index) := 0;
3238
3239 CSTPPINV.l_item_end_index_tbl (i_item_id) := l_index;
3240 ELSE /* item-cost_element-level_type combination found: Update*/
3241 CSTPPINV.l_item_balance_tbl(l_index) := x.actual_cost + CSTPPINV.l_item_balance_tbl(l_index);
3242 END IF;
3243 END;
3244 END LOOP;
3245
3246 /* Fix for Bug 1970458
3247 * For a value change periodic update cost transaction,
3248 * update the primary_quantity in mmt to the layer quantity from cpql.
3249 * Prior to this, the quantity at the beginning of the period was being
3250 * used and this caused errors in the distributions.
3251 * The layer qty can be obtained from cst_pac_quantity_layers
3252 */
3253 l_stmt_num := 120;
3254 UPDATE mtl_material_transactions mmt
3255 SET --primary_quantity = l_onhand,
3256 /* Bug 2288994. Update periodic_primary_quantity also */
3257 periodic_primary_quantity = l_onhand
3258 WHERE mmt.value_change IS NOT NULL
3259 AND mmt.transaction_id = i_txn_id;
3260 fnd_file.put_line(fnd_file.log,'Updated MMT with primary_quantity: ' || to_char(l_onhand));
3261
3262 END IF;
3263
3264 /* --- start of auto log --- */
3265 <<out_arg_log>>
3266
3267 IF l_plog THEN
3268 fnd_log.string(
3269 fnd_log.level_procedure,
3270 l_module||'.'||l_stmt_num,
3271 'Exiting CSTPPWAC.periodic_cost_update with '||
3272 'O_Err_Num = '||O_Err_Num||','||
3273 'O_Err_Code = '||O_Err_Code||','||
3274 'O_Err_Msg = '||O_Err_Msg
3275 );
3276 END IF;
3277 /* --- end of auto log --- */
3278 EXCEPTION
3279 WHEN OTHERS THEN
3280 ROLLBACK;
3281 IF l_uLog THEN
3282 fnd_message.set_name('BOM','CST_UNEXPECTED');
3283 fnd_message.set_token('SQLERRM',SQLERRM);
3284 fnd_msg_pub.add;
3285 fnd_log.message(
3286 fnd_log.level_unexpected,
3287 l_module||'.'||l_stmt_num,
3288 FALSE
3289 );
3290 END IF;
3291 o_err_num := SQLCODE;
3292 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3293 /* --- end of auto log --- */
3294 END periodic_cost_update;
3295
3296 /* Commented to remove the dependency on cst_pc_txn_history table
3297 --as part of customer bug 6751847 and fp bug 5999388 performance fixes
3298 -- PROCEDURE
3299 -- insert_txn_history
3300 --
3301 PROCEDURE insert_txn_history (
3302 I_PAC_PERIOD_ID IN NUMBER,
3303 I_COST_GROUP_ID IN NUMBER,
3304 I_TXN_ID IN NUMBER,
3305 I_PROCESS_GROUP IN NUMBER,
3306 I_ITEM_ID IN NUMBER,
3307 I_QTY_LAYER_ID IN NUMBER,
3308 I_TXN_QTY IN NUMBER,
3309 I_USER_ID IN NUMBER,
3310 I_LOGIN_ID IN NUMBER,
3311 I_REQ_ID IN NUMBER,
3312 I_PRG_APPL_ID IN NUMBER,
3313 I_PRG_ID IN NUMBER,
3314 I_TXN_CATEGORY IN NUMBER,
3315 O_Err_Num OUT NOCOPY NUMBER,
3316 O_Err_Code OUT NOCOPY VARCHAR2,
3317 O_Err_Msg OUT NOCOPY VARCHAR2
3318 ) IS
3319 l_stmt_num NUMBER;
3320
3321 -- start of auto log ---
3322 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_txn_history';
3323 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
3324 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
3325 fnd_log.TEST(fnd_log.level_unexpected, l_module);
3326 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
3327 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
3328 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
3329 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
3330 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
3331
3332 -- end of auto log ---
3333 BEGIN
3334 --- start of auto log ---
3335 IF l_plog THEN
3336 fnd_log.string(
3337 fnd_log.level_procedure,
3338 l_module||'.'||l_stmt_num,
3339 'Entering CSTPPWAC.insert_txn_history with '||
3340 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
3341 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
3342 'I_TXN_ID = '||I_TXN_ID||','||
3343 'I_PROCESS_GROUP = '||I_PROCESS_GROUP||','||
3344 'I_ITEM_ID = '||I_ITEM_ID||','||
3345 'I_QTY_LAYER_ID = '||I_QTY_LAYER_ID||','||
3346 'I_TXN_QTY = '||I_TXN_QTY||','||
3347 'I_USER_ID = '||I_USER_ID||','||
3348 'I_LOGIN_ID = '||I_LOGIN_ID||','||
3349 'I_REQ_ID = '||I_REQ_ID||','||
3350 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
3351 'I_PRG_ID = '||I_PRG_ID||','||
3352 'I_TXN_CATEGORY = '||I_TXN_CATEGORY
3353 );
3354 END IF;
3355 --- end of auto log ---
3356
3357 l_stmt_num := 10;
3358 INSERT INTO cst_pc_txn_history (
3359 pac_period_id,
3360 cost_group_id,
3361 transaction_id,
3362 process_seq,
3363 process_group,
3364 inventory_item_id,
3365 txn_master_qty,
3366 prior_costed_master_qty,
3367 txn_category,
3368 last_update_date,
3369 last_updated_by,
3370 creation_date,
3371 created_by,
3372 request_id,
3373 program_application_id,
3374 program_id,
3375 program_update_date,
3376 last_update_login)
3377 (SELECT
3378 i_pac_period_id,
3379 i_cost_group_id,
3380 i_txn_id,
3381 cst_pc_txn_history_s.nextval,
3382 i_process_group,
3383 i_item_id,
3384 i_txn_qty,
3385 layer_quantity,
3386 i_txn_category,
3387 sysdate,
3388 i_user_id,
3389 sysdate,
3390 i_user_id,
3391 i_req_id,
3392 i_prg_appl_id,
3393 i_prg_id,
3394 SYSDATE,
3395 i_login_id
3396 FROM
3397 cst_pac_quantity_layers
3398 WHERE quantity_layer_id = i_qty_layer_id);
3399
3400 --- start of auto log ---
3401 <<out_arg_log>>
3402
3403 IF l_plog THEN
3404 fnd_log.string(
3405 fnd_log.level_procedure,
3406 l_module||'.'||l_stmt_num,
3407 'Exiting CSTPPWAC.insert_txn_history with '||
3408 'O_Err_Num = '||O_Err_Num||','||
3409 'O_Err_Code = '||O_Err_Code||','||
3410 'O_Err_Msg = '||O_Err_Msg
3411 );
3412 END IF;
3413 --- end of auto log ---
3414 EXCEPTION
3415 WHEN OTHERS THEN
3416 ROLLBACK;
3417 IF l_uLog THEN
3418 fnd_message.set_name('BOM','CST_UNEXPECTED');
3419 fnd_message.set_token('SQLERRM',SQLERRM);
3420 fnd_msg_pub.add;
3421 fnd_log.message(
3422 fnd_log.level_unexpected,
3423 l_module||'.'||l_stmt_num,
3424 FALSE
3425 );
3426 END IF;
3427 o_err_num := SQLCODE;
3428 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3429 --- end of auto log ---
3430 END insert_txn_history;
3431 */
3432
3433 /*
3434 -- Commented to remove the dependency on cst_pc_txn_history table
3435 -- as part of customer bug 6751847 and fp bug 5999388 performance fixes
3436 -- PROCEDURE
3437 -- PROCEDURE
3438 -- update_txn_history
3439 --
3440 PROCEDURE update_txn_history (
3441 I_PAC_PERIOD_ID IN NUMBER,
3442 I_COST_GROUP_ID IN NUMBER,
3443 I_TXN_ID IN NUMBER,
3444 I_USER_ID IN NUMBER,
3445 I_LOGIN_ID IN NUMBER,
3446 I_REQ_ID IN NUMBER,
3447 I_PRG_APPL_ID IN NUMBER,
3448 I_PRG_ID IN NUMBER,
3449 O_Err_Num OUT NOCOPY NUMBER,
3450 O_Err_Code OUT NOCOPY VARCHAR2,
3451 O_Err_Msg OUT NOCOPY VARCHAR2
3452 ) IS
3453 l_stmt_num NUMBER;
3454
3455 --- start of auto log ---
3456 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_txn_history';
3457 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
3458 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
3459 fnd_log.TEST(fnd_log.level_unexpected, l_module);
3460 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
3461 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
3462 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
3463 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
3464 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
3465
3466 --- end of auto log ---
3467 BEGIN
3468 --- start of auto log ---
3469 IF l_plog THEN
3470 fnd_log.string(
3471 fnd_log.level_procedure,
3472 l_module||'.'||l_stmt_num,
3473 'Entering CSTPPWAC.update_txn_history with '||
3474 'I_PAC_PERIOD_ID = '||I_PAC_PERIOD_ID||','||
3475 'I_COST_GROUP_ID = '||I_COST_GROUP_ID||','||
3476 'I_TXN_ID = '||I_TXN_ID||','||
3477 'I_USER_ID = '||I_USER_ID||','||
3478 'I_LOGIN_ID = '||I_LOGIN_ID||','||
3479 'I_REQ_ID = '||I_REQ_ID||','||
3480 'I_PRG_APPL_ID = '||I_PRG_APPL_ID||','||
3481 'I_PRG_ID = '||I_PRG_ID
3482 );
3483 END IF;
3484 --- end of auto log ---
3485
3486 l_stmt_num := 10;
3487 UPDATE cst_pc_txn_history
3488 SET( actual_cost,
3489 new_cost,
3490 prior_cost )=
3491 (SELECT
3492 sum(actual_cost),
3493 sum(new_cost),
3494 sum(prior_cost)
3495 FROM
3496 mtl_pac_actual_cost_details
3497 WHERE pac_period_id = i_pac_period_id
3498 and cost_group_id = i_cost_group_id
3499 and transaction_id = i_txn_id)
3500 WHERE pac_period_id = i_pac_period_id
3501 and cost_group_id = i_cost_group_id
3502 and transaction_id = i_txn_id;
3503
3504 --- start of auto log ---
3505 <<out_arg_log>>
3506
3507 IF l_plog THEN
3508 fnd_log.string(
3509 fnd_log.level_procedure,
3510 l_module||'.'||l_stmt_num,
3511 'Exiting CSTPPWAC.update_txn_history with '||
3512 'O_Err_Num = '||O_Err_Num||','||
3513 'O_Err_Code = '||O_Err_Code||','||
3514 'O_Err_Msg = '||O_Err_Msg
3515 );
3516 END IF;
3517 --- end of auto log ---
3518 EXCEPTION
3519 WHEN OTHERS THEN
3520 ROLLBACK;
3521 IF l_uLog THEN
3522 fnd_message.set_name('BOM','CST_UNEXPECTED');
3523 fnd_message.set_token('SQLERRM',SQLERRM);
3524 fnd_msg_pub.add;
3525 fnd_log.message(
3526 fnd_log.level_unexpected,
3527 l_module||'.'||l_stmt_num,
3528 FALSE
3529 );
3530 END IF;
3531 o_err_num := SQLCODE;
3532 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3533 --- end of auto log ---
3534 END update_txn_history;
3535 */
3536
3537 -- PROCEDURE
3538 -- insert_into_cppb
3539 --
3540 PROCEDURE insert_into_cppb(i_pac_period_id IN NUMBER,
3541 i_cost_group_id IN NUMBER,
3542 i_txn_category IN NUMBER,
3543 i_user_id IN NUMBER,
3544 i_login_id IN NUMBER,
3545 i_request_id IN NUMBER,
3546 i_prog_id IN NUMBER,
3547 i_prog_appl_id IN NUMBER,
3548 o_err_num OUT NOCOPY NUMBER,
3549 o_err_code OUT NOCOPY VARCHAR2,
3550 o_err_msg OUT NOCOPY VARCHAR2)
3551 IS
3552 l_stmt_num NUMBER;
3553 l_new_qty_tbl CSTPPINV.t_item_quantity_tbl;
3554 l_new_buy_qty_tbl CSTPPINV.t_item_quantity_tbl;
3555 l_new_make_qty_tbl CSTPPINV.t_item_quantity_tbl;
3556 l_new_issue_qty_tbl CSTPPINV.t_item_quantity_tbl;
3557 l_category_qty_tbl CSTPPINV.t_item_quantity_tbl;
3558
3559 /* --- start of auto log --- */
3560 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_into_cppb';
3561 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
3562 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
3563 fnd_log.TEST(fnd_log.level_unexpected, l_module);
3564 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
3565 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
3566 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
3567 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
3568 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
3569
3570 /* --- end of auto log --- */
3571 BEGIN
3572 /* --- start of auto log --- */
3573 IF l_plog THEN
3574 fnd_log.string(
3575 fnd_log.level_procedure,
3576 l_module||'.'||l_stmt_num,
3577 'Entering CSTPPWAC.insert_into_cppb with '||
3578 'i_cost_group_id = '||i_cost_group_id||','||
3579 'i_txn_category = '||i_txn_category||','||
3580 'i_user_id = '||i_user_id||','||
3581 'i_login_id = '||i_login_id||','||
3582 'i_request_id = '||i_request_id||','||
3583 'i_prog_id = '||i_prog_id||','||
3584 'i_prog_appl_id = '||i_prog_appl_id
3585 );
3586 END IF;
3587 /* --- end of auto log --- */
3588
3589 l_stmt_num := 10;
3590 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
3591 FOR l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3592 LOOP
3593 IF (i_txn_category = 5 OR
3594 i_txn_category = 2.5) THEN
3595 -- Periodic Cost Update value change
3596 SELECT cpic.total_layer_quantity,
3597 cpic.buy_quantity,
3598 cpic.make_quantity,
3599 cpic.issue_quantity,
3600 0 /* category_qty = 0 for cost updates */
3601 INTO l_new_qty_tbl (l_index),
3602 l_new_buy_qty_tbl (l_index),
3603 l_new_make_qty_tbl (l_index),
3604 l_new_issue_qty_tbl (l_index),
3605 l_category_qty_tbl (l_index)
3606 FROM cst_pac_item_costs cpic
3607 WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
3608 ELSE
3609 SELECT cpic.total_layer_quantity + CSTPPINV.l_item_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
3610 cpic.buy_quantity + CSTPPINV.l_buy_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
3611 cpic.make_quantity + CSTPPINV.l_make_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
3612 cpic.issue_quantity + CSTPPINV.l_issue_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index)),
3613 CSTPPINV.l_item_quantity_tbl (CSTPPINV.l_item_id_tbl(l_index))
3614 INTO l_new_qty_tbl (l_index),
3615 l_new_buy_qty_tbl (l_index),
3616 l_new_make_qty_tbl (l_index),
3617 l_new_issue_qty_tbl (l_index),
3618 l_category_qty_tbl (l_index)
3619 FROM cst_pac_item_costs cpic
3620 WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
3621 END IF;
3622 END LOOP;
3623 END IF;
3624
3625 l_stmt_num := 20;
3626 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3627 MERGE INTO CST_PAC_PERIOD_BALANCES cppb
3628 USING (SELECT i_pac_period_id pac_period_id,
3629 i_cost_group_id cost_group_id,
3630 CSTPPINV.l_item_id_tbl(l_index) item_id,
3631 CSTPPINV.l_cost_layer_id_tbl(l_index) cost_layer_id,
3632 CSTPPINV.l_qty_layer_id_tbl(l_index) qty_layer_id,
3633 CSTPPINV.l_cost_element_id_tbl(l_index) cost_element_id,
3634 CSTPPINV.l_level_type_tbl(l_index) level_type,
3635 CSTPPINV.l_txn_category_tbl(l_index) txn_category,
3636 l_category_qty_tbl (l_index) category_quantity,
3637 CSTPPINV.l_item_balance_tbl(l_index) category_balance
3638 FROM dual) temp
3639 ON ( cppb.pac_period_id = temp.pac_period_id
3640 AND cppb.cost_layer_id = temp.cost_layer_id
3641 AND cppb.cost_element_id = temp.cost_element_id
3642 AND cppb.level_type = temp.level_type
3643 AND cppb.txn_category = temp.txn_category)
3644 WHEN NOT MATCHED THEN
3645 INSERT (pac_period_id,
3646 cost_group_id,
3647 inventory_item_id,
3648 cost_layer_id,
3649 quantity_layer_id,
3650 cost_element_id,
3651 level_type,
3652 txn_category,
3653 txn_category_qty,
3654 txn_category_value,
3655 last_update_date,
3656 last_updated_by,
3657 last_update_login,
3658 created_by,
3659 creation_date,
3660 request_id,
3661 program_application_id,
3662 program_id,
3663 program_update_date)
3664 VALUES (temp.pac_period_id,
3665 temp.cost_group_id,
3666 temp.item_id,
3667 temp.cost_layer_id,
3668 temp.qty_layer_id,
3669 temp.cost_element_id,
3670 temp.level_type,
3671 temp.txn_category,
3672 temp.category_quantity,
3673 temp.category_balance,
3674 sysdate,
3675 i_user_id,
3676 i_login_id,
3677 i_user_id,
3678 sysdate,
3679 i_request_id,
3680 i_prog_appl_id,
3681 i_prog_id,
3682 sysdate)
3683 WHEN MATCHED THEN
3684 UPDATE SET
3685 txn_category_qty = txn_category_qty + temp.category_quantity,
3686 txn_category_value = txn_category_value + temp.category_balance,
3687 last_update_date = sysdate,
3688 last_updated_by = i_user_id,
3689 last_update_login = i_login_id,
3690 request_id = i_request_id,
3691 program_application_id = i_prog_appl_id,
3692 program_id = i_prog_id,
3693 program_update_date = sysdate;
3694
3695 l_stmt_num := 30;
3696 -- Update balance of existing rows in CPICD
3697 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3698 UPDATE CST_PAC_ITEM_COST_DETAILS cpicd
3699 SET item_balance = nvl(item_balance, 0) + CSTPPINV.l_item_balance_tbl (l_index),
3700 make_balance = nvl(make_balance, 0) + CSTPPINV.l_make_balance_tbl (l_index),
3701 buy_balance = nvl(buy_balance, 0) + CSTPPINV.l_buy_balance_tbl (l_index),
3702 last_update_date = sysdate,
3703 last_updated_by = i_user_id,
3704 last_update_login = i_login_id,
3705 request_id = i_request_id,
3706 program_application_id = i_prog_appl_id,
3707 program_id = i_prog_id,
3708 program_update_date = sysdate
3709 WHERE cpicd.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index)
3710 AND cpicd.cost_element_id = CSTPPINV.l_cost_element_id_tbl (l_index)
3711 AND cpicd.level_type = CSTPPINV.l_level_type_tbl (l_index);
3712
3713 l_stmt_num := 40;
3714 -- Insert missing cost elements into CPICD
3715 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3716 INSERT INTO CST_PAC_ITEM_COST_DETAILS cpicd
3717 (cost_layer_id,
3718 cost_element_id,
3719 level_type,
3720 last_update_date,
3721 last_updated_by,
3722 creation_date,
3723 created_by,
3724 last_update_login,
3725 request_id,
3726 program_application_id,
3727 program_id,
3728 program_update_date,
3729 item_cost,
3730 item_buy_cost,
3731 item_make_cost,
3732 item_balance,
3733 make_balance,
3734 buy_balance)
3735 (SELECT CSTPPINV.l_cost_layer_id_tbl (l_index),
3736 CSTPPINV.l_cost_element_id_tbl (l_index),
3737 CSTPPINV.l_level_type_tbl (l_index),
3738 sysdate,
3739 i_user_id,
3740 sysdate,
3741 i_user_id,
3742 i_login_id,
3743 i_request_id,
3744 i_prog_appl_id,
3745 i_prog_id,
3746 sysdate,
3747 0,
3748 0,
3749 0,
3750 CSTPPINV.l_item_balance_tbl (l_index),
3751 CSTPPINV.l_make_balance_tbl (l_index),
3752 CSTPPINV.l_buy_balance_tbl (l_index)
3753 FROM dual
3754 WHERE NOT EXISTS (SELECT 1
3755 FROM cst_pac_item_cost_details cpicd1
3756 WHERE cpicd1.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index)
3757 AND cpicd1.cost_element_id = CSTPPINV.l_cost_element_id_tbl (l_index)
3758 AND cpicd1.level_type = CSTPPINV.l_level_type_tbl (l_index)));
3759
3760 l_stmt_num := 50;
3761 /* Changing the query as per Bug5045692. Performance Fix */
3762 /* update quantities and balance in CPIC */
3763 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3764 UPDATE cst_pac_item_costs cpic
3765 SET total_layer_quantity = l_new_qty_tbl (l_index),
3766 issue_quantity = l_new_issue_qty_tbl (l_index),
3767 buy_quantity = l_new_buy_qty_tbl(l_index),
3768 make_quantity = l_new_make_qty_tbl (l_index),
3769 last_update_date = sysdate,
3770 last_updated_by = i_user_id,
3771 request_id = i_request_id,
3772 program_application_id = i_prog_appl_id,
3773 program_id = i_prog_id,
3774 program_update_date = sysdate,
3775 last_update_login = i_login_id
3776 WHERE cpic.cost_layer_id = CSTPPINV.l_cost_layer_id_tbl (l_index);
3777
3778 l_stmt_num := 60;
3779 /* Update CPQL quantity */
3780 FORALL l_index IN CSTPPINV.l_item_id_tbl.FIRST..CSTPPINV.l_item_id_tbl.LAST
3781 UPDATE CST_PAC_QUANTITY_LAYERS cpql
3782 SET (last_updated_by,
3783 last_update_date,
3784 last_update_login,
3785 request_id,
3786 program_application_id,
3787 program_id,
3788 program_update_date,
3789 layer_quantity) =
3790 (SELECT i_user_id,
3791 sysdate,
3792 i_login_id,
3793 i_request_id,
3794 i_prog_appl_id,
3795 i_prog_id,
3796 sysdate,
3797 l_new_qty_tbl (l_index)
3798 FROM sys.dual)
3799 WHERE cpql.quantity_layer_id = CSTPPINV.l_qty_layer_id_tbl (l_index)
3800 AND EXISTS
3801 (SELECT 'there is a layer'
3802 FROM cst_pac_quantity_layers cpql
3803 WHERE cpql.quantity_layer_id = CSTPPINV.l_qty_layer_id_tbl (l_index));
3804
3805 l_stmt_num := 70;
3806 /* Clear All PL/SQL tables */
3807 CSTPPINV.l_item_id_tbl.DELETE;
3808 CSTPPINV.l_cost_layer_id_tbl.DELETE;
3809 CSTPPINV.l_qty_layer_id_tbl.DELETE;
3810
3811 CSTPPINV.l_cost_element_id_tbl.DELETE;
3812 CSTPPINV.l_level_type_tbl.DELETE;
3813 CSTPPINV.l_txn_category_tbl.DELETE;
3814
3815 CSTPPINV.l_item_balance_tbl.DELETE;
3816 CSTPPINV.l_make_balance_tbl.DELETE;
3817 CSTPPINV.l_buy_balance_tbl.DELETE;
3818
3819 CSTPPINV.l_item_quantity_tbl.DELETE;
3820 CSTPPINV.l_make_quantity_tbl.DELETE;
3821 CSTPPINV.l_buy_quantity_tbl.DELETE;
3822 CSTPPINV.l_issue_quantity_tbl.DELETE;
3823
3824 CSTPPINV.l_item_start_index_tbl.DELETE;
3825 CSTPPINV.l_item_end_index_tbl.DELETE;
3826
3827 /* --- start of auto log --- */
3828 <<out_arg_log>>
3829
3830 IF l_plog THEN
3831 fnd_log.string(
3832 fnd_log.level_procedure,
3833 l_module||'.'||l_stmt_num,
3834 'Exiting CSTPPWAC.insert_into_cppb with '||
3835 'o_err_num = '||o_err_num||','||
3836 'o_err_code = '||o_err_code||','||
3837 'o_err_msg = '||o_err_msg
3838 );
3839 END IF;
3840 /* --- end of auto log --- */
3841 EXCEPTION
3842 WHEN OTHERS THEN
3843 ROLLBACK;
3844 IF l_uLog THEN
3845 fnd_message.set_name('BOM','CST_UNEXPECTED');
3846 fnd_message.set_token('SQLERRM',SQLERRM);
3847 fnd_msg_pub.add;
3848 fnd_log.message(
3849 fnd_log.level_unexpected,
3850 l_module||'.'||l_stmt_num,
3851 FALSE
3852 );
3853 END IF;
3854 o_err_num := SQLCODE;
3855 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
3856 /* --- end of auto log --- */
3857 END insert_into_cppb;
3858
3859 -- +========================================================================+
3860 -- PROCEDURE: PRIVATE UTILITY
3861 -- PARAMETERS: calc_item_periodic_cost
3862 -- Descrition: Called from calculate_periodic_cost with inventory_item_id
3863 -- +========================================================================+
3864 PROCEDURE calc_item_periodic_cost (i_pac_period_id IN NUMBER,
3865 i_cost_group_id IN NUMBER,
3866 i_cost_type_id IN NUMBER,
3867 i_low_level_code IN NUMBER,
3868 i_item_id IN NUMBER,
3869 i_user_id IN NUMBER,
3870 i_login_id IN NUMBER,
3871 i_request_id IN NUMBER,
3872 i_prog_id IN NUMBER,
3873 i_prog_appl_id IN NUMBER,
3874 o_err_num OUT NOCOPY NUMBER,
3875 o_err_code OUT NOCOPY VARCHAR2,
3876 o_err_msg OUT NOCOPY VARCHAR2)
3877 IS
3878 l_stmt_num NUMBER;
3879 l_max_txn_category NUMBER;
3880 TYPE t_txn_id_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.transaction_id%TYPE
3881 INDEX BY BINARY_INTEGER;
3882 TYPE t_txn_category_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.txn_category%TYPE
3883 INDEX BY BINARY_INTEGER;
3884 l_last_txn_id_tbl t_txn_id_tbl;
3885 l_txn_category_tbl t_txn_category_tbl;
3886 l_cost_layer_id_tbl CSTPPINV.t_cost_layer_id_tbl;
3887
3888 /* --- start of auto log --- */
3889 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.calc_item_periodic_cost';
3890 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
3891 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
3892 fnd_log.TEST(fnd_log.level_unexpected, l_module);
3893 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
3894 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
3895 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
3896 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
3897 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
3898
3899 /* --- end of auto log --- */
3900 BEGIN
3901 /* --- start of auto log --- */
3902 IF l_plog THEN
3903 fnd_log.string(
3904 fnd_log.level_procedure,
3905 l_module||'.'||l_stmt_num,
3906 'Entering CSTPPWAC.calc_item_periodic_cost with '||
3907 'i_cost_group_id = '||i_cost_group_id||','||
3908 'i_cost_type_id = '||i_cost_type_id||','||
3909 'i_low_level_code = '||i_low_level_code||','||
3910 'i_item_id = '||i_item_id||','||
3911 'i_user_id = '||i_user_id||','||
3912 'i_login_id = '||i_login_id||','||
3913 'i_request_id = '||i_request_id||','||
3914 'i_prog_id = '||i_prog_id||','||
3915 'i_prog_appl_id = '||i_prog_appl_id
3916 );
3917 END IF;
3918 /* --- end of auto log --- */
3919
3920 -- Build temporary tables to hold the last txn id and txn category values for each cost_layer_id
3921 IF (i_low_level_code = -1) THEN
3922 -- items without completion
3923 l_stmt_num := 10;
3924 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
3925 BULK COLLECT
3926 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
3927 FROM mtl_pac_actual_cost_details mpacd
3928 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
3929 FROM mtl_pac_actual_cost_details mpacd1
3930 WHERE mpacd1.txn_category = (SELECT max(txn_category)
3931 FROM cst_pac_period_balances cppb
3932 WHERE cppb.pac_period_id = i_pac_period_id
3933 AND cppb.cost_group_id = i_cost_group_id
3934 AND cppb.cost_layer_id = mpacd.cost_layer_id)
3935 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
3936 AND mpacd1.pac_period_id = i_pac_period_id
3937 AND mpacd1.cost_group_id = i_cost_group_id)
3938 AND mpacd.cost_group_id = i_cost_group_id
3939 AND mpacd.pac_period_id = i_pac_period_id
3940 AND mpacd.inventory_item_id = i_item_id
3941 AND NOT EXISTS (SELECT 1
3942 FROM cst_pac_low_level_codes cpllc
3943 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
3944 AND cpllc.pac_period_id = i_pac_period_id
3945 AND cpllc.cost_group_id = i_cost_group_id);
3946 ELSE
3947 -- items with completion
3948 l_stmt_num := 20;
3949
3950 -- get the maximum transaction category that has been processed for any item having
3951 -- completions till this point in time.
3952
3953 SELECT max(txn_category)
3954 INTO l_max_txn_category
3955 FROM mtl_pac_actual_cost_details mpacd
3956 WHERE mpacd.pac_period_id = i_pac_period_id
3957 AND mpacd.cost_group_id = i_cost_group_id
3958 AND mpacd.inventory_item_id = i_item_id
3959 AND EXISTS (SELECT 1
3960 FROM cst_pac_low_level_codes cpllc
3961 WHERE cpllc.cost_group_id = i_cost_group_id
3962 AND cpllc.pac_period_id = i_pac_period_id
3963 AND cpllc.inventory_item_id = mpacd.inventory_item_id
3964 AND cpllc.low_level_code = i_low_level_code);
3965
3966 IF l_sLog THEN
3967 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module||'.'||l_stmt_num,'l_max_txn_category = '||l_max_txn_category);
3968 END IF;
3969
3970 IF (l_max_txn_category = 7) THEN
3971 -- Rework completions. Pick only items having records with transaction
3972 -- category = 7 to avoid updating variance again wrongly for other cost owned
3973 -- transaction categories
3974
3975 l_stmt_num := 25;
3976 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
3977 BULK COLLECT
3978 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
3979 FROM mtl_pac_actual_cost_details mpacd
3980 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
3981 FROM mtl_pac_actual_cost_details mpacd1
3982 WHERE mpacd1.txn_category = (SELECT max(txn_category)
3983 FROM cst_pac_period_balances cppb
3984 WHERE cppb.pac_period_id = i_pac_period_id
3985 AND cppb.cost_group_id = i_cost_group_id
3986 AND cppb.cost_layer_id = mpacd.cost_layer_id)
3987 AND mpacd1.txn_category = l_max_txn_category
3988 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
3989 AND mpacd1.pac_period_id = i_pac_period_id
3990 AND mpacd1.cost_group_id = i_cost_group_id)
3991 AND mpacd.cost_group_id = i_cost_group_id
3992 AND mpacd.pac_period_id = i_pac_period_id
3993 AND mpacd.inventory_item_id = i_item_id
3994 AND EXISTS (SELECT 1
3995 FROM cst_pac_low_level_codes cpllc
3996 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
3997 AND cpllc.low_level_code = i_low_level_code
3998 AND cpllc.pac_period_id = i_pac_period_id
3999 AND cpllc.cost_group_id = i_cost_group_id);
4000 ELSE
4001 -- Non rework completions
4002
4003 l_stmt_num := 30;
4004 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4005 BULK COLLECT
4006 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4007 FROM mtl_pac_actual_cost_details mpacd
4008 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
4009 FROM mtl_pac_actual_cost_details mpacd1
4010 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4011 FROM cst_pac_period_balances cppb
4012 WHERE cppb.pac_period_id = i_pac_period_id
4013 AND cppb.cost_group_id = i_cost_group_id
4014 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4015 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4016 AND mpacd1.pac_period_id = i_pac_period_id
4017 AND mpacd1.cost_group_id = i_cost_group_id)
4018 AND mpacd.cost_group_id = i_cost_group_id
4019 AND mpacd.pac_period_id = i_pac_period_id
4020 AND mpacd.inventory_item_id = i_item_id
4021 AND EXISTS (SELECT 1
4022 FROM cst_pac_low_level_codes cpllc
4023 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4024 AND cpllc.low_level_code = i_low_level_code
4025 AND cpllc.pac_period_id = i_pac_period_id
4026 AND cpllc.cost_group_id = i_cost_group_id);
4027 END IF;
4028 END IF;
4029
4030
4031 /****************************************************************************
4032 Post variance to the last transaction in the last cost owned txn category
4033 processed for that item. Insert rows into mpacd for missing cost elements
4034 ****************************************************************************/
4035
4036 l_stmt_num := 35;
4037 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
4038 UPDATE mtl_pac_actual_cost_details mpacd
4039 SET variance_amount = (SELECT decode (sign(cpic.total_layer_quantity),
4040 0, cpicd.item_balance,
4041 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
4042 0)
4043 FROM cst_pac_item_costs cpic,
4044 cst_pac_item_cost_details cpicd
4045 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
4046 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4047 AND cpicd.cost_element_id = mpacd.cost_element_id
4048 AND cpicd.level_type = mpacd.level_type),
4049 last_update_date = sysdate,
4050 last_updated_by = i_user_id,
4051 last_update_login = i_login_id,
4052 request_id = i_request_id,
4053 program_application_id = i_prog_appl_id,
4054 program_id = i_prog_id,
4055 program_update_date = sysdate
4056 WHERE transaction_id = l_last_txn_id_tbl (l_index)
4057 AND mpacd.cost_group_id = i_cost_group_id
4058 AND mpacd.pac_period_id = i_pac_period_id
4059 AND mpacd.cost_layer_id = l_cost_layer_id_tbl(l_index)
4060 AND (cost_element_id, level_type) = (SELECT cost_element_id, level_type
4061 FROM cst_pac_item_cost_details cpicd
4062 WHERE cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4063 AND cpicd.cost_element_id = mpacd.cost_element_id
4064 AND cpicd.level_type = mpacd.level_type);
4065
4066 l_stmt_num := 40;
4067 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
4068 INSERT INTO mtl_pac_actual_cost_details mpacd
4069 (COST_GROUP_ID,
4070 TRANSACTION_ID,
4071 PAC_PERIOD_ID,
4072 COST_TYPE_ID,
4073 COST_ELEMENT_ID,
4074 LEVEL_TYPE,
4075 INVENTORY_ITEM_ID,
4076 COST_LAYER_ID,
4077 ACTUAL_COST,
4078 USER_ENTERED,
4079 INSERTION_FLAG,
4080 TRANSACTION_COSTED_DATE,
4081 LAST_UPDATE_DATE,
4082 LAST_UPDATED_BY,
4083 CREATION_DATE,
4084 CREATED_BY,
4085 REQUEST_ID,
4086 PROGRAM_APPLICATION_ID,
4087 PROGRAM_ID,
4088 PROGRAM_UPDATE_DATE,
4089 LAST_UPDATE_LOGIN,
4090 VARIANCE_AMOUNT,
4091 TXN_CATEGORY)
4092 (SELECT i_cost_group_id,
4093 l_last_txn_id_tbl (l_index),
4094 i_pac_period_id,
4095 i_cost_type_id,
4096 cpicd.cost_element_id,
4097 cpicd.level_type,
4098 cpic.inventory_item_id,
4099 cpic.cost_layer_id,
4100 0,
4101 'N',
4102 'N',
4103 sysdate,
4104 SYSDATE,
4105 i_user_id,
4106 SYSDATE,
4107 i_user_id,
4108 i_request_id,
4109 i_prog_appl_id,
4110 i_prog_id,
4111 SYSDATE,
4112 i_login_id,
4113 decode (sign(cpic.total_layer_quantity),
4114 0, cpicd.item_balance,
4115 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
4116 0),
4117 l_txn_category_tbl(l_index)
4118 FROM cst_pac_item_cost_details cpicd,
4119 cst_pac_item_costs cpic
4120 WHERE cpicd.cost_layer_id = cpic.cost_layer_id
4121 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4122 AND NOT EXISTS (SELECT 1
4123 FROM mtl_pac_actual_cost_details mpacd1
4124 WHERE mpacd1.transaction_id = l_last_txn_id_tbl (l_index)
4125 AND mpacd1.cost_layer_id = cpicd.cost_layer_id
4126 AND mpacd1.cost_element_id = cpicd.cost_element_id
4127 AND mpacd1.level_type = cpicd.level_type)
4128 );
4129 l_stmt_num := 50;
4130
4131 IF (i_low_level_code = -1) THEN
4132 -- Items that do not have completion
4133 UPDATE cst_pac_item_cost_details cpicd
4134 SET (last_update_date,
4135 last_updated_by,
4136 last_update_login,
4137 request_id,
4138 program_application_id,
4139 program_id,
4140 program_update_date,
4141 item_cost,
4142 item_buy_cost,
4143 item_make_cost,
4144 item_balance,
4145 buy_balance,
4146 make_balance) =
4147 (SELECT sysdate,
4148 i_user_id,
4149 i_login_id,
4150 i_request_id,
4151 i_prog_appl_id,
4152 i_prog_id,
4153 sysdate,
4154 decode (sign(cpic.total_layer_quantity),
4155 0, cpicd.item_cost,
4156 (-1 * sign(cpicd.item_balance)), 0,
4157 cpicd.item_balance / cpic.total_layer_quantity),
4158 decode (sign(cpic.total_layer_quantity),
4159 0, cpicd.item_buy_cost,
4160 (-1 * sign(cpicd.item_balance)), 0,
4161 decode (cpic.buy_quantity,
4162 0, 0,
4163 cpicd.buy_balance / cpic.buy_quantity)),
4164 decode (sign(cpic.total_layer_quantity),
4165 0, cpicd.item_make_cost,
4166 (-1 * sign(cpicd.item_balance)), 0,
4167 decode (cpic.make_quantity,
4168 0, 0,
4169 cpicd.make_balance / cpic.make_quantity)),
4170 decode (sign (cpic.total_layer_quantity),
4171 0, 0,
4172 (-1 * sign(cpicd.item_balance)), 0,
4173 cpicd.item_balance),
4174 decode (sign (cpic.total_layer_quantity),
4175 0, 0,
4176 (-1 * sign(cpicd.item_balance)), 0,
4177 cpicd.buy_balance),
4178 decode (sign (cpic.total_layer_quantity),
4179 0, 0,
4180 (-1 * sign(cpicd.item_balance)), 0,
4181 cpicd.make_balance)
4182 FROM cst_pac_item_costs cpic
4183 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
4184 WHERE cpicd.cost_layer_id IN ( SELECT cost_layer_id
4185 FROM cst_pac_item_costs
4186 WHERE inventory_item_id = i_item_id
4187 AND cost_group_id = i_cost_group_id
4188 AND pac_period_id = i_pac_period_id)
4189 AND EXISTS (SELECT 1
4190 FROM cst_pac_period_balances cppb
4191 WHERE cppb.pac_period_id = i_pac_period_id
4192 AND cppb.cost_group_id = i_cost_group_id
4193 AND cppb.cost_layer_id = cpicd.cost_layer_id
4194 AND cppb.cost_element_id = cpicd.cost_element_id
4195 AND cppb.level_type = cpicd.level_type
4196 AND cppb.inventory_item_id = i_item_id)
4197 AND NOT EXISTS (SELECT 1
4198 FROM cst_pac_low_level_codes cpllc
4199 WHERE cpllc.pac_period_id = i_pac_period_id
4200 AND cpllc.cost_group_id = i_cost_group_id
4201 AND cpllc.inventory_item_id = i_item_id);
4202
4203 l_stmt_num := 60;
4204 UPDATE cst_pac_item_costs cpic
4205 SET (last_updated_by,
4206 last_update_date,
4207 last_update_login,
4208 request_id,
4209 program_application_id,
4210 program_id,
4211 program_update_date,
4212 pl_material,
4213 pl_material_overhead,
4214 pl_resource,
4215 pl_outside_processing,
4216 pl_overhead,
4217 tl_material,
4218 tl_material_overhead,
4219 tl_resource,
4220 tl_outside_processing,
4221 tl_overhead,
4222 material_cost,
4223 material_overhead_cost,
4224 resource_cost,
4225 outside_processing_cost,
4226 overhead_cost,
4227 pl_item_cost,
4228 tl_item_cost,
4229 item_cost,
4230 item_buy_cost,
4231 item_make_cost,
4232 unburdened_cost,
4233 burden_cost) =
4234 (SELECT i_user_id,
4235 sysdate,
4236 i_login_id,
4237 i_request_id,
4238 i_prog_appl_id,
4239 i_prog_id,
4240 sysdate,
4241 pl_material,
4242 pl_material_overhead,
4243 pl_resource,
4244 pl_outside_processing,
4245 pl_overhead,
4246 tl_material,
4247 tl_material_overhead,
4248 tl_resource,
4249 tl_outside_processing,
4250 tl_overhead,
4251 material_cost,
4252 material_overhead_cost,
4253 resource_cost,
4254 outside_processing_cost,
4255 overhead_cost,
4256 pl_item_cost,
4257 tl_item_cost,
4258 item_cost,
4259 item_buy_cost,
4260 item_make_cost,
4261 unburdened_cost,
4262 burden_cost
4263 FROM cst_pac_item_costs_v v
4264 WHERE v.cost_layer_id = cpic.cost_layer_id)
4265 WHERE cpic.inventory_item_id = i_item_id
4266 AND cpic.cost_group_id = i_cost_group_id
4267 AND cpic.pac_period_id = i_pac_period_id
4268 AND EXISTS (SELECT 1
4269 FROM cst_pac_period_balances cppb
4270 WHERE cppb.pac_period_id = i_pac_period_id
4271 AND cppb.cost_group_id = i_cost_group_id
4272 AND cppb.cost_layer_id = cpic.cost_layer_id)
4273 AND NOT EXISTS (SELECT 1
4274 FROM cst_pac_low_level_codes cpllc
4275 WHERE cpllc.inventory_item_id = cpic.inventory_item_id
4276 AND cpllc.pac_period_id = i_pac_period_id
4277 AND cpllc.cost_group_id = i_cost_group_id)
4278 AND EXISTS
4279 (SELECT 'there is detail cost'
4280 FROM cst_pac_item_cost_details cpicd
4281 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
4282 ELSE
4283 -- low_level_code <> -1; items having completion
4284
4285 l_stmt_num := 70;
4286 UPDATE cst_pac_item_cost_details cpicd
4287 SET (last_update_date,
4288 last_updated_by,
4289 last_update_login,
4290 request_id,
4291 program_application_id,
4292 program_id,
4293 program_update_date,
4294 item_cost,
4295 item_buy_cost,
4296 item_make_cost,
4297 item_balance,
4298 buy_balance,
4299 make_balance) =
4300 (SELECT sysdate,
4301 i_user_id,
4302 i_login_id,
4303 i_request_id,
4304 i_prog_appl_id,
4305 i_prog_id,
4306 sysdate,
4307 decode (sign(cpic.total_layer_quantity),
4308 0, cpicd.item_cost,
4309 (-1 * sign(cpicd.item_balance)), 0,
4310 cpicd.item_balance / cpic.total_layer_quantity),
4311 decode (sign(cpic.total_layer_quantity),
4312 0, cpicd.item_buy_cost,
4313 (-1 * sign(cpicd.item_balance)), 0,
4314 decode (cpic.buy_quantity,
4315 0, 0,
4316 cpicd.buy_balance / cpic.buy_quantity)),
4317 decode (sign(cpic.total_layer_quantity),
4318 0, cpicd.item_make_cost,
4319 (-1 * sign(cpicd.item_balance)), 0,
4320 decode (cpic.make_quantity,
4321 0, 0,
4322 cpicd.make_balance / cpic.make_quantity)),
4323 decode (sign (cpic.total_layer_quantity),
4324 0, 0,
4325 (-1 * sign(cpicd.item_balance)), 0,
4326 cpicd.item_balance),
4327 decode (sign (cpic.total_layer_quantity),
4328 0, 0,
4329 (-1 * sign(cpicd.item_balance)), 0,
4330 cpicd.buy_balance),
4331 decode (sign (cpic.total_layer_quantity),
4332 0, 0,
4333 (-1 * sign(cpicd.item_balance)), 0,
4334 cpicd.make_balance)
4335 FROM cst_pac_item_costs cpic
4336 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
4337 WHERE cpicd.cost_layer_id IN ( SELECT cost_layer_id
4338 FROM cst_pac_item_costs
4339 WHERE inventory_item_id = i_item_id
4340 AND cost_group_id = i_cost_group_id
4341 AND pac_period_id = i_pac_period_id)
4342 AND EXISTS (SELECT 1
4343 FROM cst_pac_period_balances cppb
4344 WHERE cppb.pac_period_id = i_pac_period_id
4345 AND cppb.cost_group_id = i_cost_group_id
4346 AND cppb.cost_layer_id = cpicd.cost_layer_id
4347 AND cppb.cost_element_id = cpicd.cost_element_id
4348 AND cppb.level_type = cpicd.level_type
4349 AND cppb.inventory_item_id = i_item_id)
4350 AND EXISTS (SELECT 1
4351 FROM cst_pac_low_level_codes cpllc
4352 WHERE cpllc.low_level_code = i_low_level_code
4353 AND cpllc.pac_period_id = i_pac_period_id
4354 AND cpllc.cost_group_id = i_cost_group_id
4355 AND cpllc.inventory_item_id = i_item_id);
4356
4357 l_stmt_num := 80;
4358 UPDATE cst_pac_item_costs cpic
4359 SET (last_updated_by,
4360 last_update_date,
4361 last_update_login,
4362 request_id,
4363 program_application_id,
4364 program_id,
4365 program_update_date,
4366 pl_material,
4367 pl_material_overhead,
4368 pl_resource,
4369 pl_outside_processing,
4370 pl_overhead,
4371 tl_material,
4372 tl_material_overhead,
4373 tl_resource,
4374 tl_outside_processing,
4375 tl_overhead,
4376 material_cost,
4377 material_overhead_cost,
4378 resource_cost,
4379 outside_processing_cost,
4380 overhead_cost,
4381 pl_item_cost,
4382 tl_item_cost,
4383 item_cost,
4384 item_buy_cost,
4385 item_make_cost,
4386 unburdened_cost,
4387 burden_cost) =
4388 (SELECT i_user_id,
4389 sysdate,
4390 i_login_id,
4391 i_request_id,
4392 i_prog_appl_id,
4393 i_prog_id,
4394 sysdate,
4395 pl_material,
4396 pl_material_overhead,
4397 pl_resource,
4398 pl_outside_processing,
4399 pl_overhead,
4400 tl_material,
4401 tl_material_overhead,
4402 tl_resource,
4403 tl_outside_processing,
4404 tl_overhead,
4405 material_cost,
4406 material_overhead_cost,
4407 resource_cost,
4408 outside_processing_cost,
4409 overhead_cost,
4410 pl_item_cost,
4411 tl_item_cost,
4412 item_cost,
4413 item_buy_cost,
4414 item_make_cost,
4415 unburdened_cost,
4416 burden_cost
4417 FROM cst_pac_item_costs_v v
4418 WHERE v.cost_layer_id = cpic.cost_layer_id)
4419 WHERE cpic.inventory_item_id = i_item_id
4420 AND cpic.cost_group_id = i_cost_group_id
4421 AND cpic.pac_period_id = i_pac_period_id
4422 AND EXISTS (SELECT 1
4423 FROM cst_pac_period_balances cppb
4424 WHERE cppb.pac_period_id = i_pac_period_id
4425 AND cppb.cost_group_id = i_cost_group_id
4426 AND cppb.cost_layer_id = cpic.cost_layer_id)
4427 AND EXISTS (SELECT 1
4428 FROM cst_pac_low_level_codes cpllc
4429 WHERE cpllc.low_level_code = i_low_level_code
4430 AND cpllc.inventory_item_id = cpic.inventory_item_id
4431 AND cpllc.pac_period_id = i_pac_period_id
4432 AND cpllc.cost_group_id = i_cost_group_id)
4433 AND EXISTS
4434 (SELECT 'there is detail cost'
4435 FROM cst_pac_item_cost_details cpicd
4436 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
4437 END IF;
4438
4439 /* --- start of auto log --- */
4440 <<out_arg_log>>
4441
4442 IF l_plog THEN
4443 fnd_log.string(
4444 fnd_log.level_procedure,
4445 l_module||'.'||l_stmt_num,
4446 'Exiting CSTPPWAC.calc_item_periodic_cost with '||
4447 'o_err_num = '||o_err_num||','||
4448 'o_err_code = '||o_err_code||','||
4449 'o_err_msg = '||o_err_msg
4450 );
4451 END IF;
4452 /* --- end of auto log --- */
4453 EXCEPTION
4454 WHEN OTHERS THEN
4455 ROLLBACK;
4456 IF l_uLog THEN
4457 fnd_message.set_name('BOM','CST_UNEXPECTED');
4458 fnd_message.set_token('SQLERRM',SQLERRM);
4459 fnd_msg_pub.add;
4460 fnd_log.message(
4461 fnd_log.level_unexpected,
4462 l_module||'.'||l_stmt_num,
4463 FALSE
4464 );
4465 END IF;
4466 o_err_num := SQLCODE;
4467 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
4468 /* --- end of auto log --- */
4469 END calc_item_periodic_cost;
4470
4471 -- PROCEDURE
4472 -- calc_periodic_cost
4473 --
4474 PROCEDURE calc_periodic_cost (i_pac_period_id IN NUMBER,
4475 i_cost_group_id IN NUMBER,
4476 i_cost_type_id IN NUMBER,
4477 i_low_level_code IN NUMBER,
4478 i_user_id IN NUMBER,
4479 i_login_id IN NUMBER,
4480 i_request_id IN NUMBER,
4481 i_prog_id IN NUMBER,
4482 i_prog_appl_id IN NUMBER,
4483 o_err_num OUT NOCOPY NUMBER,
4484 o_err_code OUT NOCOPY VARCHAR2,
4485 o_err_msg OUT NOCOPY VARCHAR2)
4486 IS
4487 l_stmt_num NUMBER;
4488 l_max_txn_category NUMBER;
4489 TYPE t_txn_id_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.transaction_id%TYPE
4490 INDEX BY BINARY_INTEGER;
4491 TYPE t_txn_category_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.txn_category%TYPE
4492 INDEX BY BINARY_INTEGER;
4493 l_last_txn_id_tbl t_txn_id_tbl;
4494 l_txn_category_tbl t_txn_category_tbl;
4495 l_cost_layer_id_tbl CSTPPINV.t_cost_layer_id_tbl;
4496
4497 /* --- start of auto log --- */
4498 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.calc_periodic_cost';
4499 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
4500 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
4501 fnd_log.TEST(fnd_log.level_unexpected, l_module);
4502 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
4503 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
4504 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
4505 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
4506 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
4507
4508 /* --- end of auto log --- */
4509 BEGIN
4510 /* --- start of auto log --- */
4511 IF l_plog THEN
4512 fnd_log.string(
4513 fnd_log.level_procedure,
4514 l_module||'.'||l_stmt_num,
4515 'Entering CSTPPWAC.calc_periodic_cost with '||
4516 'i_cost_group_id = '||i_cost_group_id||','||
4517 'i_cost_type_id = '||i_cost_type_id||','||
4518 'i_low_level_code = '||i_low_level_code||','||
4519 'i_user_id = '||i_user_id||','||
4520 'i_login_id = '||i_login_id||','||
4521 'i_request_id = '||i_request_id||','||
4522 'i_prog_id = '||i_prog_id||','||
4523 'i_prog_appl_id = '||i_prog_appl_id
4524 );
4525 END IF;
4526 /* --- end of auto log --- */
4527
4528 -- Build temporary tables to hold the last txn id and txn category values for each cost_layer_id
4529 IF (i_low_level_code = -1) THEN
4530 -- items without completion
4531 l_stmt_num := 10;
4532 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4533 BULK COLLECT
4534 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4535 FROM mtl_pac_actual_cost_details mpacd
4536 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
4537 FROM mtl_pac_actual_cost_details mpacd1
4538 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4539 FROM cst_pac_period_balances cppb
4540 WHERE cppb.pac_period_id = i_pac_period_id
4541 AND cppb.cost_group_id = i_cost_group_id
4542 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4543 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4544 AND mpacd1.pac_period_id = i_pac_period_id
4545 AND mpacd1.cost_group_id = i_cost_group_id)
4546 AND mpacd.cost_group_id = i_cost_group_id
4547 AND mpacd.pac_period_id = i_pac_period_id
4548 AND NOT EXISTS (SELECT 1
4549 FROM cst_pac_low_level_codes cpllc
4550 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4551 AND cpllc.pac_period_id = i_pac_period_id
4552 AND cpllc.cost_group_id = i_cost_group_id);
4553 ELSE
4554 -- items with completion
4555 l_stmt_num := 20;
4556
4557 -- get the maximum transaction category that has been processed for any item having
4558 -- completions till this point in time.
4559
4560 SELECT max(mpacd.txn_category)
4561 INTO l_max_txn_category
4562 FROM mtl_pac_actual_cost_details mpacd
4563 WHERE mpacd.pac_period_id = i_pac_period_id
4564 AND mpacd.cost_group_id = i_cost_group_id
4565 AND EXISTS (SELECT 1
4566 FROM cst_pac_low_level_codes cpllc
4567 WHERE cpllc.cost_group_id = i_cost_group_id
4568 AND cpllc.pac_period_id = i_pac_period_id
4569 AND cpllc.inventory_item_id = mpacd.inventory_item_id
4570 AND cpllc.low_level_code = i_low_level_code);
4571
4572 IF l_sLog THEN
4573 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,l_module||'.'||l_stmt_num,'l_max_txn_category = '||l_max_txn_category);
4574 END IF;
4575
4576 IF (l_max_txn_category = 7) THEN
4577 -- Rework completions. Pick only items having records with transaction
4578 -- category = 7 to avoid updating variance again wrongly for other cost owned
4579 -- transaction categories
4580
4581 l_stmt_num := 25;
4582 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4583 BULK COLLECT
4584 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4585 FROM mtl_pac_actual_cost_details mpacd
4586 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
4587 FROM mtl_pac_actual_cost_details mpacd1
4588 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4589 FROM cst_pac_period_balances cppb
4590 WHERE cppb.pac_period_id = i_pac_period_id
4591 AND cppb.cost_group_id = i_cost_group_id
4592 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4593 AND mpacd1.txn_category = l_max_txn_category
4594 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4595 AND mpacd1.pac_period_id = i_pac_period_id
4596 AND mpacd1.cost_group_id = i_cost_group_id)
4597 AND mpacd.cost_group_id = i_cost_group_id
4598 AND mpacd.pac_period_id = i_pac_period_id
4599 AND EXISTS (SELECT 1
4600 FROM cst_pac_low_level_codes cpllc
4601 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4602 AND cpllc.low_level_code = i_low_level_code
4603 AND cpllc.pac_period_id = i_pac_period_id
4604 AND cpllc.cost_group_id = i_cost_group_id);
4605 ELSE
4606 -- Non rework completions
4607
4608 l_stmt_num := 30;
4609 SELECT distinct cost_layer_id, mpacd.transaction_id, mpacd.txn_category
4610 BULK COLLECT
4611 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl, l_txn_category_tbl
4612 FROM mtl_pac_actual_cost_details mpacd
4613 WHERE mpacd.transaction_id = (SELECT max(transaction_id)
4614 FROM mtl_pac_actual_cost_details mpacd1
4615 WHERE mpacd1.txn_category = (SELECT max(txn_category)
4616 FROM cst_pac_period_balances cppb
4617 WHERE cppb.pac_period_id = i_pac_period_id
4618 AND cppb.cost_group_id = i_cost_group_id
4619 AND cppb.cost_layer_id = mpacd.cost_layer_id)
4620 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
4621 AND mpacd1.pac_period_id = i_pac_period_id
4622 AND mpacd1.cost_group_id = i_cost_group_id)
4623 AND mpacd.cost_group_id = i_cost_group_id
4624 AND mpacd.pac_period_id = i_pac_period_id
4625 AND EXISTS (SELECT 1
4626 FROM cst_pac_low_level_codes cpllc
4627 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
4628 AND cpllc.low_level_code = i_low_level_code
4629 AND cpllc.pac_period_id = i_pac_period_id
4630 AND cpllc.cost_group_id = i_cost_group_id);
4631 END IF;
4632 END IF;
4633
4634
4635 /****************************************************************************
4636 Post variance to the last transaction in the last cost owned txn category
4637 processed for that item. Insert rows into mpacd for missing cost elements
4638 ****************************************************************************/
4639
4640 l_stmt_num := 35;
4641 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
4642 UPDATE mtl_pac_actual_cost_details mpacd
4643 SET variance_amount = (SELECT decode (sign(cpic.total_layer_quantity),
4644 0, cpicd.item_balance,
4645 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
4646 0)
4647 FROM cst_pac_item_costs cpic,
4648 cst_pac_item_cost_details cpicd
4649 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
4650 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4651 AND cpicd.cost_element_id = mpacd.cost_element_id
4652 AND cpicd.level_type = mpacd.level_type),
4653 last_update_date = sysdate,
4654 last_updated_by = i_user_id,
4655 last_update_login = i_login_id,
4656 request_id = i_request_id,
4657 program_application_id = i_prog_appl_id,
4658 program_id = i_prog_id,
4659 program_update_date = sysdate
4660 WHERE transaction_id = l_last_txn_id_tbl (l_index)
4661 AND mpacd.cost_group_id = i_cost_group_id
4662 AND mpacd.pac_period_id = i_pac_period_id
4663 AND mpacd.cost_layer_id = l_cost_layer_id_tbl(l_index)
4664 AND (cost_element_id, level_type) = (SELECT cost_element_id, level_type
4665 FROM cst_pac_item_cost_details cpicd
4666 WHERE cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4667 AND cpicd.cost_element_id = mpacd.cost_element_id
4668 AND cpicd.level_type = mpacd.level_type);
4669
4670 l_stmt_num := 40;
4671 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
4672 INSERT INTO mtl_pac_actual_cost_details mpacd
4673 (COST_GROUP_ID,
4674 TRANSACTION_ID,
4675 PAC_PERIOD_ID,
4676 COST_TYPE_ID,
4677 COST_ELEMENT_ID,
4678 LEVEL_TYPE,
4679 INVENTORY_ITEM_ID,
4680 COST_LAYER_ID,
4681 ACTUAL_COST,
4682 USER_ENTERED,
4683 INSERTION_FLAG,
4684 TRANSACTION_COSTED_DATE,
4685 LAST_UPDATE_DATE,
4686 LAST_UPDATED_BY,
4687 CREATION_DATE,
4688 CREATED_BY,
4689 REQUEST_ID,
4690 PROGRAM_APPLICATION_ID,
4691 PROGRAM_ID,
4692 PROGRAM_UPDATE_DATE,
4693 LAST_UPDATE_LOGIN,
4694 VARIANCE_AMOUNT,
4695 TXN_CATEGORY)
4696 (SELECT i_cost_group_id,
4697 l_last_txn_id_tbl (l_index),
4698 i_pac_period_id,
4699 i_cost_type_id,
4700 cpicd.cost_element_id,
4701 cpicd.level_type,
4702 cpic.inventory_item_id,
4703 cpic.cost_layer_id,
4704 0,
4705 'N',
4706 'N',
4707 sysdate,
4708 SYSDATE,
4709 i_user_id,
4710 SYSDATE,
4711 i_user_id,
4712 i_request_id,
4713 i_prog_appl_id,
4714 i_prog_id,
4715 SYSDATE,
4716 i_login_id,
4717 decode (sign(cpic.total_layer_quantity),
4718 0, cpicd.item_balance,
4719 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
4720 0),
4721 l_txn_category_tbl(l_index)
4722 FROM cst_pac_item_cost_details cpicd,
4723 cst_pac_item_costs cpic
4724 WHERE cpicd.cost_layer_id = cpic.cost_layer_id
4725 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
4726 AND NOT EXISTS (SELECT 1
4727 FROM mtl_pac_actual_cost_details mpacd1
4728 WHERE mpacd1.transaction_id = l_last_txn_id_tbl (l_index)
4729 AND mpacd1.cost_layer_id = cpicd.cost_layer_id
4730 AND mpacd1.cost_element_id = cpicd.cost_element_id
4731 AND mpacd1.level_type = cpicd.level_type)
4732 );
4733 l_stmt_num := 50;
4734
4735 IF (i_low_level_code = -1) THEN
4736 -- Items that do not have completion
4737 UPDATE cst_pac_item_cost_details cpicd
4738 SET (last_update_date,
4739 last_updated_by,
4740 last_update_login,
4741 request_id,
4742 program_application_id,
4743 program_id,
4744 program_update_date,
4745 item_cost,
4746 item_buy_cost,
4747 item_make_cost,
4748 item_balance,
4749 buy_balance,
4750 make_balance) =
4751 (SELECT sysdate,
4752 i_user_id,
4753 i_login_id,
4754 i_request_id,
4755 i_prog_appl_id,
4756 i_prog_id,
4757 sysdate,
4758 decode (sign(cpic.total_layer_quantity),
4759 0, cpicd.item_cost,
4760 (-1 * sign(cpicd.item_balance)), 0,
4761 cpicd.item_balance / cpic.total_layer_quantity),
4762 decode (sign(cpic.total_layer_quantity),
4763 0, cpicd.item_buy_cost,
4764 (-1 * sign(cpicd.item_balance)), 0,
4765 decode (cpic.buy_quantity,
4766 0, 0,
4767 cpicd.buy_balance / cpic.buy_quantity)),
4768 decode (sign(cpic.total_layer_quantity),
4769 0, cpicd.item_make_cost,
4770 (-1 * sign(cpicd.item_balance)), 0,
4771 decode (cpic.make_quantity,
4772 0, 0,
4773 cpicd.make_balance / cpic.make_quantity)),
4774 decode (sign (cpic.total_layer_quantity),
4775 0, 0,
4776 (-1 * sign(cpicd.item_balance)), 0,
4777 cpicd.item_balance),
4778 decode (sign (cpic.total_layer_quantity),
4779 0, 0,
4780 (-1 * sign(cpicd.item_balance)), 0,
4781 cpicd.buy_balance),
4782 decode (sign (cpic.total_layer_quantity),
4783 0, 0,
4784 (-1 * sign(cpicd.item_balance)), 0,
4785 cpicd.make_balance)
4786 FROM cst_pac_item_costs cpic
4787 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
4788 WHERE cpicd.cost_layer_id IN ( SELECT cost_layer_id
4789 FROM cst_pac_item_costs
4790 WHERE pac_period_id = i_pac_period_id
4791 AND cost_group_id = i_cost_group_id)
4792 AND EXISTS (SELECT 1
4793 FROM cst_pac_period_balances cppb
4794 WHERE cppb.pac_period_id = i_pac_period_id
4795 AND cppb.cost_group_id = i_cost_group_id
4796 AND cppb.cost_layer_id = cpicd.cost_layer_id
4797 AND cppb.cost_element_id = cpicd.cost_element_id
4798 AND cppb.level_type = cpicd.level_type)
4799 AND NOT EXISTS (SELECT 1
4800 FROM cst_pac_low_level_codes cpllc,
4801 cst_pac_item_costs cpic1
4802 WHERE cpllc.inventory_item_id = cpic1.inventory_item_id
4803 AND cpic1.cost_layer_id = cpicd.cost_layer_id
4804 AND cpllc.pac_period_id = i_pac_period_id
4805 AND cpllc.cost_group_id = i_cost_group_id);
4806
4807 l_stmt_num := 60;
4808 UPDATE cst_pac_item_costs cpic
4809 SET (last_updated_by,
4810 last_update_date,
4811 last_update_login,
4812 request_id,
4813 program_application_id,
4814 program_id,
4815 program_update_date,
4816 pl_material,
4817 pl_material_overhead,
4818 pl_resource,
4819 pl_outside_processing,
4820 pl_overhead,
4821 tl_material,
4822 tl_material_overhead,
4823 tl_resource,
4824 tl_outside_processing,
4825 tl_overhead,
4826 material_cost,
4827 material_overhead_cost,
4828 resource_cost,
4829 outside_processing_cost,
4830 overhead_cost,
4831 pl_item_cost,
4832 tl_item_cost,
4833 item_cost,
4834 item_buy_cost,
4835 item_make_cost,
4836 unburdened_cost,
4837 burden_cost) =
4838 (SELECT i_user_id,
4839 sysdate,
4840 i_login_id,
4841 i_request_id,
4842 i_prog_appl_id,
4843 i_prog_id,
4844 sysdate,
4845 pl_material,
4846 pl_material_overhead,
4847 pl_resource,
4848 pl_outside_processing,
4849 pl_overhead,
4850 tl_material,
4851 tl_material_overhead,
4852 tl_resource,
4853 tl_outside_processing,
4854 tl_overhead,
4855 material_cost,
4856 material_overhead_cost,
4857 resource_cost,
4858 outside_processing_cost,
4859 overhead_cost,
4860 pl_item_cost,
4861 tl_item_cost,
4862 item_cost,
4863 item_buy_cost,
4864 item_make_cost,
4865 unburdened_cost,
4866 burden_cost
4867 FROM cst_pac_item_costs_v v
4868 WHERE v.cost_layer_id = cpic.cost_layer_id)
4869 WHERE cpic.cost_group_id = i_cost_group_id
4870 AND cpic.pac_period_id = i_pac_period_id
4871 AND EXISTS (SELECT 1
4872 FROM cst_pac_period_balances cppb
4873 WHERE cppb.pac_period_id = i_pac_period_id
4874 AND cppb.cost_group_id = i_cost_group_id
4875 AND cppb.cost_layer_id = cpic.cost_layer_id)
4876 AND NOT EXISTS (SELECT 1
4877 FROM cst_pac_low_level_codes cpllc
4878 WHERE cpllc.inventory_item_id = cpic.inventory_item_id
4879 AND cpllc.pac_period_id = i_pac_period_id
4880 AND cpllc.cost_group_id = i_cost_group_id)
4881 AND EXISTS
4882 (SELECT 'there is detail cost'
4883 FROM cst_pac_item_cost_details cpicd
4884 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
4885 ELSE
4886 -- low_level_code <> -1; items having completion
4887
4888 l_stmt_num := 70;
4889 UPDATE cst_pac_item_cost_details cpicd
4890 SET (last_update_date,
4891 last_updated_by,
4892 last_update_login,
4893 request_id,
4894 program_application_id,
4895 program_id,
4896 program_update_date,
4897 item_cost,
4898 item_buy_cost,
4899 item_make_cost,
4900 item_balance,
4901 buy_balance,
4902 make_balance) =
4903 (SELECT sysdate,
4904 i_user_id,
4905 i_login_id,
4906 i_request_id,
4907 i_prog_appl_id,
4908 i_prog_id,
4909 sysdate,
4910 decode (sign(cpic.total_layer_quantity),
4911 0, cpicd.item_cost,
4912 (-1 * sign(cpicd.item_balance)), 0,
4913 cpicd.item_balance / cpic.total_layer_quantity),
4914 decode (sign(cpic.total_layer_quantity),
4915 0, cpicd.item_buy_cost,
4916 (-1 * sign(cpicd.item_balance)), 0,
4917 decode (cpic.buy_quantity,
4918 0, 0,
4919 cpicd.buy_balance / cpic.buy_quantity)),
4920 decode (sign(cpic.total_layer_quantity),
4921 0, cpicd.item_make_cost,
4922 (-1 * sign(cpicd.item_balance)), 0,
4923 decode (cpic.make_quantity,
4924 0, 0,
4925 cpicd.make_balance / cpic.make_quantity)),
4926 decode (sign (cpic.total_layer_quantity),
4927 0, 0,
4928 (-1 * sign(cpicd.item_balance)), 0,
4929 cpicd.item_balance),
4930 decode (sign (cpic.total_layer_quantity),
4931 0, 0,
4932 (-1 * sign(cpicd.item_balance)), 0,
4933 cpicd.buy_balance),
4934 decode (sign (cpic.total_layer_quantity),
4935 0, 0,
4936 (-1 * sign(cpicd.item_balance)), 0,
4937 cpicd.make_balance)
4938 FROM cst_pac_item_costs cpic
4939 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
4940 WHERE cpicd.cost_layer_id IN ( SELECT cost_layer_id
4941 FROM cst_pac_item_costs
4942 WHERE pac_period_id = i_pac_period_id
4943 AND cost_group_id = i_cost_group_id)
4944 AND EXISTS (SELECT 1
4945 FROM cst_pac_period_balances cppb
4946 WHERE cppb.pac_period_id = i_pac_period_id
4947 AND cppb.cost_group_id = i_cost_group_id
4948 AND cppb.cost_layer_id = cpicd.cost_layer_id
4949 AND cppb.cost_element_id = cpicd.cost_element_id
4950 AND cppb.level_type = cpicd.level_type)
4951 AND EXISTS (SELECT 1
4952 FROM cst_pac_low_level_codes cpllc,
4953 cst_pac_item_costs cpic1
4954 WHERE cpllc.low_level_code = i_low_level_code
4955 AND cpllc.pac_period_id = i_pac_period_id
4956 AND cpllc.cost_group_id = i_cost_group_id
4957 AND cpllc.inventory_item_id = cpic1.inventory_item_id
4958 AND cpic1.cost_layer_id = cpicd.cost_layer_id);
4959
4960 l_stmt_num := 80;
4961 UPDATE cst_pac_item_costs cpic
4962 SET (last_updated_by,
4963 last_update_date,
4964 last_update_login,
4965 request_id,
4966 program_application_id,
4967 program_id,
4968 program_update_date,
4969 pl_material,
4970 pl_material_overhead,
4971 pl_resource,
4972 pl_outside_processing,
4973 pl_overhead,
4974 tl_material,
4975 tl_material_overhead,
4976 tl_resource,
4977 tl_outside_processing,
4978 tl_overhead,
4979 material_cost,
4980 material_overhead_cost,
4981 resource_cost,
4982 outside_processing_cost,
4983 overhead_cost,
4984 pl_item_cost,
4985 tl_item_cost,
4986 item_cost,
4987 item_buy_cost,
4988 item_make_cost,
4989 unburdened_cost,
4990 burden_cost) =
4991 (SELECT i_user_id,
4992 sysdate,
4993 i_login_id,
4994 i_request_id,
4995 i_prog_appl_id,
4996 i_prog_id,
4997 sysdate,
4998 pl_material,
4999 pl_material_overhead,
5000 pl_resource,
5001 pl_outside_processing,
5002 pl_overhead,
5003 tl_material,
5004 tl_material_overhead,
5005 tl_resource,
5006 tl_outside_processing,
5007 tl_overhead,
5008 material_cost,
5009 material_overhead_cost,
5010 resource_cost,
5011 outside_processing_cost,
5012 overhead_cost,
5013 pl_item_cost,
5014 tl_item_cost,
5015 item_cost,
5016 item_buy_cost,
5017 item_make_cost,
5018 unburdened_cost,
5019 burden_cost
5020 FROM cst_pac_item_costs_v v
5021 WHERE v.cost_layer_id = cpic.cost_layer_id)
5022 WHERE cpic.cost_group_id = i_cost_group_id
5023 AND cpic.pac_period_id = i_pac_period_id
5024 AND EXISTS (SELECT 1
5025 FROM cst_pac_period_balances cppb
5026 WHERE cppb.pac_period_id = i_pac_period_id
5027 AND cppb.cost_group_id = i_cost_group_id
5028 AND cppb.cost_layer_id = cpic.cost_layer_id)
5029 AND EXISTS (SELECT 1
5030 FROM cst_pac_low_level_codes cpllc
5031 WHERE cpllc.low_level_code = i_low_level_code
5032 AND cpllc.inventory_item_id = cpic.inventory_item_id
5033 AND cpllc.pac_period_id = i_pac_period_id
5034 AND cpllc.cost_group_id = i_cost_group_id)
5035 AND EXISTS
5036 (SELECT 'there is detail cost'
5037 FROM cst_pac_item_cost_details cpicd
5038 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
5039 END IF;
5040
5041 /* --- start of auto log --- */
5042 <<out_arg_log>>
5043
5044 IF l_plog THEN
5045 fnd_log.string(
5046 fnd_log.level_procedure,
5047 l_module||'.'||l_stmt_num,
5048 'Exiting CSTPPWAC.calc_periodic_cost with '||
5049 'o_err_num = '||o_err_num||','||
5050 'o_err_code = '||o_err_code||','||
5051 'o_err_msg = '||o_err_msg
5052 );
5053 END IF;
5054 /* --- end of auto log --- */
5055 EXCEPTION
5056 WHEN OTHERS THEN
5057 ROLLBACK;
5058 IF l_uLog THEN
5059 fnd_message.set_name('BOM','CST_UNEXPECTED');
5060 fnd_message.set_token('SQLERRM',SQLERRM);
5061 fnd_msg_pub.add;
5062 fnd_log.message(
5063 fnd_log.level_unexpected,
5064 l_module||'.'||l_stmt_num,
5065 FALSE
5066 );
5067 END IF;
5068 o_err_num := SQLCODE;
5069 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
5070 /* --- end of auto log --- */
5071 END calc_periodic_cost;
5072
5073 -- PROCEDURE
5074 -- calculate_periodic_cost
5075 --
5076 PROCEDURE calculate_periodic_cost (i_pac_period_id IN NUMBER,
5077 i_cost_group_id IN NUMBER,
5078 i_cost_type_id IN NUMBER,
5079 i_low_level_code IN NUMBER,
5080 i_item_id IN NUMBER,
5081 i_user_id IN NUMBER,
5082 i_login_id IN NUMBER,
5083 i_request_id IN NUMBER,
5084 i_prog_id IN NUMBER,
5085 i_prog_appl_id IN NUMBER,
5086 o_err_num OUT NOCOPY NUMBER,
5087 o_err_code OUT NOCOPY VARCHAR2,
5088 o_err_msg OUT NOCOPY VARCHAR2)
5089 IS
5090 l_stmt_num NUMBER;
5091 /* --- start of auto log --- */
5092 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.calculate_periodic_cost';
5093 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
5094 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
5095 fnd_log.TEST(fnd_log.level_unexpected, l_module);
5096 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
5097 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
5098 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
5099 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
5100 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
5101
5102 /* --- end of auto log --- */
5103 BEGIN
5104 l_stmt_num := 10;
5105 /* --- start of auto log --- */
5106 IF l_plog THEN
5107 fnd_log.string(
5108 fnd_log.level_procedure,
5109 l_module||'.'||l_stmt_num,
5110 'Entering CSTPPWAC.calculate_periodic_cost with '||
5111 'i_cost_group_id = '||i_cost_group_id||','||
5112 'i_cost_type_id = '||i_cost_type_id||','||
5113 'i_low_level_code = '||i_low_level_code||','||
5114 'i_item_id = '||i_item_id||','||
5115 'i_user_id = '||i_user_id||','||
5116 'i_login_id = '||i_login_id||','||
5117 'i_request_id = '||i_request_id||','||
5118 'i_prog_id = '||i_prog_id||','||
5119 'i_prog_appl_id = '||i_prog_appl_id
5120 );
5121 END IF;
5122 /* --- end of auto log --- */
5123
5124 /* The procedures calc_periodic_cost, calc_item_periodic_cost have similar logic and any change in one
5125 should be synchronized with similar change in the other. "calc_periodic_cost" processes all items and
5126 "calc_item_periodic_cost" processes for i_item_id. For performance, nvl condition is removed so that index on
5127 inventory_item_id is utilised when i_item_id is passed */
5128 IF (i_item_id IS NULL) THEN
5129 l_stmt_num := 20;
5130 calc_periodic_cost(i_pac_period_id => i_pac_period_id,
5131 i_cost_group_id => i_cost_group_id,
5132 i_cost_type_id => i_cost_type_id,
5133 i_low_level_code => i_low_level_code,
5134 i_user_id => i_user_id,
5135 i_login_id => i_login_id,
5136 i_request_id => i_request_id,
5137 i_prog_id => i_prog_id,
5138 i_prog_appl_id => i_prog_appl_id,
5139 o_err_num => o_err_num,
5140 o_err_code => o_err_code,
5141 o_err_msg => o_err_msg);
5142 ELSE
5143 l_stmt_num := 30;
5144 calc_item_periodic_cost(i_pac_period_id => i_pac_period_id,
5145 i_cost_group_id => i_cost_group_id,
5146 i_cost_type_id => i_cost_type_id,
5147 i_low_level_code => i_low_level_code,
5148 i_item_id => i_item_id,
5149 i_user_id => i_user_id,
5150 i_login_id => i_login_id,
5151 i_request_id => i_request_id,
5152 i_prog_id => i_prog_id,
5153 i_prog_appl_id => i_prog_appl_id,
5154 o_err_num => o_err_num,
5155 o_err_code => o_err_code,
5156 o_err_msg => o_err_msg);
5157 END IF;
5158 /* --- start of auto log --- */
5159 <<out_arg_log>>
5160
5161 IF l_plog THEN
5162 fnd_log.string(
5163 fnd_log.level_procedure,
5164 l_module||'.'||l_stmt_num,
5165 'Exiting CSTPPWAC.calculate_periodic_cost with '||
5166 'o_err_num = '||o_err_num||','||
5167 'o_err_code = '||o_err_code||','||
5168 'o_err_msg = '||o_err_msg
5169 );
5170 END IF;
5171 /* --- end of auto log --- */
5172 END calculate_periodic_cost;
5173
5174 -- PROCEDURE
5175 -- update_cppb
5176 --
5177 PROCEDURE update_cppb (i_pac_period_id IN NUMBER,
5178 i_cost_group_id IN NUMBER,
5179 i_txn_category IN NUMBER,
5180 i_low_level_code IN NUMBER,
5181 i_user_id IN NUMBER,
5182 i_login_id IN NUMBER,
5183 i_request_id IN NUMBER,
5184 i_prog_id IN NUMBER,
5185 i_prog_appl_id IN NUMBER,
5186 o_err_num OUT NOCOPY NUMBER,
5187 o_err_code OUT NOCOPY VARCHAR2,
5188 o_err_msg OUT NOCOPY VARCHAR2)
5189 IS
5190 l_stmt_num NUMBER;
5191
5192 /* --- start of auto log --- */
5193 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_cppb';
5194 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
5195 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
5196 fnd_log.TEST(fnd_log.level_unexpected, l_module);
5197 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
5198 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
5199 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
5200 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
5201 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
5202
5203 /* --- end of auto log --- */
5204 BEGIN
5205 /* --- start of auto log --- */
5206 IF l_plog THEN
5207 fnd_log.string(
5208 fnd_log.level_procedure,
5209 l_module||'.'||l_stmt_num,
5210 'Entering CSTPPWAC.update_cppb with '||
5211 'i_cost_group_id = '||i_cost_group_id||','||
5212 'i_txn_category = '||i_txn_category||','||
5213 'i_low_level_code = '||i_low_level_code||','||
5214 'i_user_id = '||i_user_id||','||
5215 'i_login_id = '||i_login_id||','||
5216 'i_request_id = '||i_request_id||','||
5217 'i_prog_id = '||i_prog_id||','||
5218 'i_prog_appl_id = '||i_prog_appl_id
5219 );
5220 END IF;
5221 /* --- end of auto log --- */
5222
5223 -- Update total period quantity, balance, periodic cost and variance into cppb
5224 IF (i_low_level_code = -2) THEN
5225
5226 -- Called after group1_trx cursor, which processes both items with completion and
5227 -- items without completion. So we need to update cppb for all items irrespective
5228 -- of whether they have completions or not.
5229
5230 l_stmt_num := 10;
5231 UPDATE CST_PAC_PERIOD_BALANCES cppb
5232 SET (last_updated_by,
5233 last_update_date,
5234 last_update_login,
5235 request_id,
5236 program_application_id,
5237 program_id,
5238 program_update_date,
5239 period_balance,
5240 period_quantity,
5241 periodic_cost,
5242 variance_amount) =
5243 (SELECT i_user_id,
5244 sysdate,
5245 i_login_id,
5246 i_request_id,
5247 i_prog_appl_id,
5248 i_prog_id,
5249 sysdate,
5250 cpicd.item_balance,
5251 cpic.total_layer_quantity,
5252 cpicd.item_cost,
5253 (SELECT sum (nvl (mpacd.variance_amount, 0))
5254 FROM mtl_pac_actual_cost_details mpacd
5255 WHERE mpacd.txn_category = i_txn_category
5256 AND mpacd.inventory_item_id = cppb.inventory_item_id
5257 AND mpacd.pac_period_id = i_pac_period_id
5258 AND mpacd.cost_group_id = i_cost_group_id
5259 AND mpacd.cost_layer_id = cppb.cost_layer_id
5260 AND mpacd.cost_element_id = cppb.cost_element_id
5261 AND mpacd.level_type = cppb.level_type)
5262 FROM cst_pac_item_cost_details cpicd,
5263 cst_pac_item_costs cpic
5264 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
5265 AND cppb.cost_layer_id = cpicd.cost_layer_id
5266 AND cppb.cost_element_id = cpicd.cost_element_id
5267 AND cppb.level_type = cpicd.level_type)
5268 WHERE cppb.pac_period_id = i_pac_period_id
5269 AND cppb.cost_group_id = i_cost_group_id
5270 AND cppb.txn_category = i_txn_category
5271 AND i_txn_category = (SELECT max (txn_category)
5272 FROM MTL_PAC_ACTUAL_COST_DETAILS
5273 WHERE pac_period_id = i_pac_period_id
5274 AND cost_group_id = i_cost_group_id
5275 AND inventory_item_id = cppb.inventory_item_id)
5276 AND EXISTS (SELECT 1
5277 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
5278 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
5279 AND cppb.cost_element_id = cpicd1.cost_element_id
5280 AND cppb.level_type = cpicd1.level_type);
5281
5282 ELSIF (i_low_level_code = -1) THEN
5283
5284 -- Items without completion
5285
5286 l_stmt_num := 20;
5287 UPDATE CST_PAC_PERIOD_BALANCES cppb
5288 SET (last_updated_by,
5289 last_update_date,
5290 last_update_login,
5291 request_id,
5292 program_application_id,
5293 program_id,
5294 program_update_date,
5295 period_balance,
5296 period_quantity,
5297 periodic_cost,
5298 variance_amount) =
5299 (SELECT i_user_id,
5300 sysdate,
5301 i_login_id,
5302 i_request_id,
5303 i_prog_appl_id,
5304 i_prog_id,
5305 sysdate,
5306 cpicd.item_balance,
5307 cpic.total_layer_quantity,
5308 cpicd.item_cost,
5309 (SELECT sum (nvl (mpacd.variance_amount, 0))
5310 FROM mtl_pac_actual_cost_details mpacd
5311 WHERE mpacd.txn_category = i_txn_category
5312 AND mpacd.inventory_item_id = cppb.inventory_item_id
5313 AND mpacd.pac_period_id = i_pac_period_id
5314 AND mpacd.cost_group_id = i_cost_group_id
5315 AND mpacd.cost_layer_id = cppb.cost_layer_id
5316 AND mpacd.cost_element_id = cppb.cost_element_id
5317 AND mpacd.level_type = cppb.level_type)
5318 FROM cst_pac_item_cost_details cpicd,
5319 cst_pac_item_costs cpic
5320 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
5321 AND cppb.cost_layer_id = cpicd.cost_layer_id
5322 AND cppb.cost_element_id = cpicd.cost_element_id
5323 AND cppb.level_type = cpicd.level_type)
5324 WHERE cppb.pac_period_id = i_pac_period_id
5325 AND cppb.cost_group_id = i_cost_group_id
5326 AND cppb.txn_category = i_txn_category
5327 AND i_txn_category = (SELECT max (txn_category)
5328 FROM MTL_PAC_ACTUAL_COST_DETAILS
5329 WHERE pac_period_id = i_pac_period_id
5330 AND cost_group_id = i_cost_group_id
5331 AND inventory_item_id = cppb.inventory_item_id)
5332 AND EXISTS (SELECT 1
5333 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
5334 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
5335 AND cppb.cost_element_id = cpicd1.cost_element_id
5336 AND cppb.level_type = cpicd1.level_type)
5337 AND NOT EXISTS (SELECT 1
5338 FROM cst_pac_low_level_codes cpllc
5339 WHERE cpllc.inventory_item_id = cppb.inventory_item_id
5340 AND cpllc.pac_period_id = i_pac_period_id
5341 AND cpllc.cost_group_id = i_cost_group_id);
5342 ELSIF (i_low_level_code <> -1) THEN
5343
5344 -- Items with completion
5345
5346 l_stmt_num := 30;
5347 UPDATE CST_PAC_PERIOD_BALANCES cppb
5348 SET (last_updated_by,
5349 last_update_date,
5350 last_update_login,
5351 request_id,
5352 program_application_id,
5353 program_id,
5354 program_update_date,
5355 period_balance,
5356 period_quantity,
5357 periodic_cost,
5358 variance_amount) =
5359 (SELECT i_user_id,
5360 sysdate,
5361 i_login_id,
5362 i_request_id,
5363 i_prog_appl_id,
5364 i_prog_id,
5365 sysdate,
5366 cpicd.item_balance,
5367 cpic.total_layer_quantity,
5368 cpicd.item_cost,
5369 (select sum (nvl (mpacd.variance_amount, 0))
5370 from mtl_pac_actual_cost_details mpacd
5371 where mpacd.txn_category = i_txn_category
5372 and mpacd.inventory_item_id = cppb.inventory_item_id
5373 and mpacd.pac_period_id = i_pac_period_id
5374 and mpacd.cost_group_id = i_cost_group_id
5375 and mpacd.cost_layer_id = cppb.cost_layer_id
5376 and mpacd.cost_element_id = cppb.cost_element_id
5377 and mpacd.level_type = cppb.level_type)
5378 FROM cst_pac_item_cost_details cpicd,
5379 cst_pac_item_costs cpic
5380 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
5381 AND cppb.cost_layer_id = cpicd.cost_layer_id
5382 AND cppb.cost_element_id = cpicd.cost_element_id
5383 AND cppb.level_type = cpicd.level_type)
5384 WHERE cppb.pac_period_id = i_pac_period_id
5385 AND cppb.cost_group_id = i_cost_group_id
5386 AND cppb.txn_category = i_txn_category
5387 AND i_txn_category = (SELECT max (txn_category)
5388 FROM MTL_PAC_ACTUAL_COST_DETAILS
5389 WHERE pac_period_id = i_pac_period_id
5390 AND cost_group_id = i_cost_group_id
5391 AND inventory_item_id = cppb.inventory_item_id)
5392 AND EXISTS (SELECT 1
5393 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
5394 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
5395 AND cppb.cost_element_id = cpicd1.cost_element_id
5396 AND cppb.level_type = cpicd1.level_type)
5397 AND EXISTS (SELECT 1
5398 FROM cst_pac_low_level_codes cpllc
5399 WHERE cpllc.inventory_item_id = cppb.inventory_item_id
5400 AND cpllc.low_level_code = i_low_level_code
5401 AND cpllc.pac_period_id = i_pac_period_id
5402 AND cpllc.cost_group_id = i_cost_group_id);
5403 END IF;
5404
5405 /* --- start of auto log --- */
5406 <<out_arg_log>>
5407
5408 IF l_plog THEN
5409 fnd_log.string(
5410 fnd_log.level_procedure,
5411 l_module||'.'||l_stmt_num,
5412 'Exiting CSTPPWAC.update_cppb with '||
5413 'o_err_num = '||o_err_num||','||
5414 'o_err_code = '||o_err_code||','||
5415 'o_err_msg = '||o_err_msg
5416 );
5417 END IF;
5418 /* --- end of auto log --- */
5419 EXCEPTION
5420 WHEN OTHERS THEN
5421 ROLLBACK;
5422 IF l_uLog THEN
5423 fnd_message.set_name('BOM','CST_UNEXPECTED');
5424 fnd_message.set_token('SQLERRM',SQLERRM);
5425 fnd_msg_pub.add;
5426 fnd_log.message(
5427 fnd_log.level_unexpected,
5428 l_module||'.'||l_stmt_num,
5429 FALSE
5430 );
5431 END IF;
5432 o_err_num := SQLCODE;
5433 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
5434 /* --- end of auto log --- */
5435 END update_cppb;
5436
5437 -- PROCEDURE
5438 -- update_item_cppb, to be used in iPAC during rollup
5439 --
5440
5441 PROCEDURE update_item_cppb (i_pac_period_id IN NUMBER,
5442 i_cost_group_id IN NUMBER,
5443 i_txn_category IN NUMBER,
5444 i_item_id IN NUMBER,
5445 i_user_id IN NUMBER,
5446 i_login_id IN NUMBER,
5447 i_request_id IN NUMBER,
5448 i_prog_id IN NUMBER,
5449 i_prog_appl_id IN NUMBER,
5450 o_err_num OUT NOCOPY NUMBER,
5451 o_err_code OUT NOCOPY VARCHAR2,
5452 o_err_msg OUT NOCOPY VARCHAR2)
5453 IS
5454 l_stmt_num NUMBER;
5455
5456 /* --- start of auto log --- */
5457 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.update_item_cppb';
5458 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
5459 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
5460 fnd_log.TEST(fnd_log.level_unexpected, l_module);
5461 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
5462 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
5463 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
5464 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
5465 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
5466
5467 /* --- end of auto log --- */
5468 BEGIN
5469 /* --- start of auto log --- */
5470 IF l_plog THEN
5471 fnd_log.string(
5472 fnd_log.level_procedure,
5473 l_module||'.'||l_stmt_num,
5474 'Entering CSTPPWAC.update_item_cppb with '||
5475 'i_cost_group_id = '||i_cost_group_id||','||
5476 'i_txn_category = '||i_txn_category||','||
5477 'i_item_id = '||i_item_id||','||
5478 'i_user_id = '||i_user_id||','||
5479 'i_login_id = '||i_login_id||','||
5480 'i_request_id = '||i_request_id||','||
5481 'i_prog_id = '||i_prog_id||','||
5482 'i_prog_appl_id = '||i_prog_appl_id
5483 );
5484 END IF;
5485 /* --- end of auto log --- */
5486
5487 l_stmt_num := 10;
5488 UPDATE CST_PAC_PERIOD_BALANCES cppb
5489 SET (last_updated_by,
5490 last_update_date,
5491 last_update_login,
5492 request_id,
5493 program_application_id,
5494 program_id,
5495 program_update_date,
5496 period_balance,
5497 period_quantity,
5498 periodic_cost,
5499 variance_amount) =
5500 (SELECT i_user_id,
5501 sysdate,
5502 i_login_id,
5503 i_request_id,
5504 i_prog_appl_id,
5505 i_prog_id,
5506 sysdate,
5507 cpicd.item_balance,
5508 cpic.total_layer_quantity,
5509 cpicd.item_cost,
5510 (SELECT sum (nvl (mpacd.variance_amount, 0))
5511 FROM mtl_pac_actual_cost_details mpacd
5512 WHERE mpacd.txn_category = cppb.txn_category
5513 AND mpacd.inventory_item_id = cppb.inventory_item_id
5514 AND mpacd.pac_period_id = cppb.pac_period_id
5515 AND mpacd.cost_group_id = cppb.cost_group_id
5516 AND mpacd.cost_layer_id = cppb.cost_layer_id
5517 AND mpacd.cost_element_id = cppb.cost_element_id
5518 AND mpacd.level_type = cppb.level_type)
5519 FROM cst_pac_item_cost_details cpicd,
5520 cst_pac_item_costs cpic
5521 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
5522 AND cppb.cost_layer_id = cpicd.cost_layer_id
5523 AND cppb.cost_element_id = cpicd.cost_element_id
5524 AND cppb.level_type = cpicd.level_type)
5525 WHERE cppb.pac_period_id = i_pac_period_id
5526 AND cppb.cost_group_id = i_cost_group_id
5527 AND cppb.txn_category = i_txn_category
5528 AND cppb.inventory_item_id = i_item_id
5529 AND i_txn_category = (SELECT max (txn_category)
5530 FROM MTL_PAC_ACTUAL_COST_DETAILS
5531 WHERE pac_period_id = i_pac_period_id
5532 AND cost_group_id = i_cost_group_id
5533 AND inventory_item_id = cppb.inventory_item_id)
5534 AND EXISTS (SELECT 1
5535 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
5536 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
5537 AND cppb.cost_element_id = cpicd1.cost_element_id
5538 AND cppb.level_type = cpicd1.level_type);
5539
5540 /* --- start of auto log --- */
5541 <<out_arg_log>>
5542
5543 IF l_plog THEN
5544 fnd_log.string(
5545 fnd_log.level_procedure,
5546 l_module||'.'||l_stmt_num,
5547 'Exiting CSTPPWAC.update_item_cppb with '||
5548 'o_err_num = '||o_err_num||','||
5549 'o_err_code = '||o_err_code||','||
5550 'o_err_msg = '||o_err_msg
5551 );
5552 END IF;
5553 /* --- end of auto log --- */
5554 EXCEPTION
5555 WHEN OTHERS THEN
5556 ROLLBACK;
5557 IF l_uLog THEN
5558 fnd_message.set_name('BOM','CST_UNEXPECTED');
5559 fnd_message.set_token('SQLERRM',SQLERRM);
5560 fnd_msg_pub.add;
5561 fnd_log.message(
5562 fnd_log.level_unexpected,
5563 l_module||'.'||l_stmt_num,
5564 FALSE
5565 );
5566 END IF;
5567 o_err_num := SQLCODE;
5568 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
5569 /* --- end of auto log --- */
5570 END update_item_cppb;
5571
5572 -- PROCEDURE
5573 -- insert_ending_balance
5574 --
5575 PROCEDURE insert_ending_balance (i_pac_period_id IN NUMBER,
5576 i_cost_group_id IN NUMBER,
5577 i_user_id IN NUMBER,
5578 i_login_id IN NUMBER,
5579 i_request_id IN NUMBER,
5580 i_prog_id IN NUMBER,
5581 i_prog_appl_id IN NUMBER,
5582 o_err_num OUT NOCOPY NUMBER,
5583 o_err_code OUT NOCOPY VARCHAR2,
5584 o_err_msg OUT NOCOPY VARCHAR2)
5585 IS
5586 l_stmt_num NUMBER;
5587
5588 /* --- start of auto log --- */
5589 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CSTPPWAC.insert_ending_balance';
5590 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
5591 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
5592 fnd_log.TEST(fnd_log.level_unexpected, l_module);
5593 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
5594 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
5595 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
5596 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
5597 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
5598
5599 /* --- end of auto log --- */
5600 BEGIN
5601 /* --- start of auto log --- */
5602 IF l_plog THEN
5603 fnd_log.string(
5604 fnd_log.level_procedure,
5605 l_module||'.'||l_stmt_num,
5606 'Entering CSTPPWAC.insert_ending_balance with '||
5607 'i_cost_group_id = '||i_cost_group_id||','||
5608 'i_user_id = '||i_user_id||','||
5609 'i_login_id = '||i_login_id||','||
5610 'i_request_id = '||i_request_id||','||
5611 'i_prog_id = '||i_prog_id||','||
5612 'i_prog_appl_id = '||i_prog_appl_id
5613 );
5614 END IF;
5615 /* --- end of auto log --- */
5616
5617 l_stmt_num := 10;
5618 INSERT INTO cst_pac_period_balances (
5619 pac_period_id,
5620 cost_group_id,
5621 inventory_item_id,
5622 cost_layer_id,
5623 quantity_layer_id,
5624 cost_element_id,
5625 level_type,
5626 txn_category,
5627 txn_category_qty,
5628 txn_category_value,
5629 period_quantity,
5630 periodic_cost,
5631 period_balance,
5632 variance_amount,
5633 last_update_date,
5634 last_updated_by,
5635 last_update_login,
5636 created_by,
5637 creation_date,
5638 request_id,
5639 program_application_id,
5640 program_id,
5641 program_update_date)
5642 (SELECT i_pac_period_id,
5643 i_cost_group_id,
5644 cpic.inventory_item_id,
5645 cpic.cost_layer_id,
5646 cpql.quantity_layer_id,
5647 cpicd.cost_element_id,
5648 cpicd.level_type,
5649 10, -- txn_category
5650 0,
5651 0,
5652 cpic.total_layer_quantity,
5653 cpicd.item_cost,
5654 cpicd.item_balance,
5655 0,
5656 sysdate,
5657 i_user_id,
5658 i_login_id,
5659 i_user_id,
5660 sysdate,
5661 i_request_id,
5662 i_prog_appl_id,
5663 i_prog_id,
5664 sysdate
5665 FROM cst_pac_item_costs cpic,
5666 cst_pac_item_cost_details cpicd,
5667 cst_pac_quantity_layers cpql
5668 WHERE cpic.pac_period_id = i_pac_period_id
5669 AND cpic.cost_group_id = i_cost_group_id
5670 AND cpicd.cost_layer_id = cpic.cost_layer_id
5671 AND cpql.cost_layer_id = cpic.cost_layer_id
5672 -- Insert ending balance records in CPPB only for asset items, i.e. only for items
5673 -- which already have atleast one record in CPPB
5674 AND exists (select 1
5675 from cst_pac_period_balances cppb1
5676 where cppb1.inventory_item_id = cpic.inventory_item_id
5677 and cppb1.cost_group_id = cpic.cost_group_id
5678 and cppb1.pac_period_id = cpic.pac_period_id));
5679
5680 /* --- start of auto log --- */
5681 <<out_arg_log>>
5682
5683 IF l_plog THEN
5684 fnd_log.string(
5685 fnd_log.level_procedure,
5686 l_module||'.'||l_stmt_num,
5687 'Exiting CSTPPWAC.insert_ending_balance with '||
5688 'o_err_num = '||o_err_num||','||
5689 'o_err_code = '||o_err_code||','||
5690 'o_err_msg = '||o_err_msg
5691 );
5692 END IF;
5693 /* --- end of auto log --- */
5694 EXCEPTION
5695 WHEN OTHERS THEN
5696 ROLLBACK;
5697 IF l_uLog THEN
5698 fnd_message.set_name('BOM','CST_UNEXPECTED');
5699 fnd_message.set_token('SQLERRM',SQLERRM);
5700 fnd_msg_pub.add;
5701 fnd_log.message(
5702 fnd_log.level_unexpected,
5703 l_module||'.'||l_stmt_num,
5704 FALSE
5705 );
5706 END IF;
5707 o_err_num := SQLCODE;
5708 o_err_msg := l_module || ' (' || to_char(l_stmt_num) || '): ' || substr(SQLERRM,1,200);
5709 /* --- end of auto log --- */
5710 END insert_ending_balance;
5711
5712 END CSTPPWAC;