[Home] [Help]
PACKAGE BODY: APPS.CSTPPWMT
Source
1 PACKAGE BODY CSTPPWMT AS
2 /* $Header: CSTPWMTB.pls 120.9 2010/02/01 23:12:25 vjavli ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSTPPWMT';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 PROCEDURE charge_wip_material(
8 p_pac_period_id IN NUMBER,
9 p_cost_group_id IN NUMBER,
10 p_txn_id IN NUMBER,
11 p_exp_item IN NUMBER DEFAULT NULL,
12 p_exp_flag IN NUMBER DEFAULT NULL,
13 p_legal_entity IN NUMBER,
14 p_cost_type_id IN NUMBER,
15 p_cost_method IN NUMBER,
16 p_pac_rates_id IN NUMBER,
17 p_master_org_id IN NUMBER,
18 p_material_relief_algorithm IN NUMBER,
19 p_uom_control IN NUMBER,
20 p_user_id IN NUMBER,
21 p_login_id IN NUMBER,
22 p_request_id IN NUMBER,
23 p_prog_id IN NUMBER,
24 p_prog_app_id IN NUMBER,
25 p_txn_category IN NUMBER,
26 x_cost_method_hook OUT NOCOPY NUMBER,
27 x_err_num OUT NOCOPY NUMBER,
28 x_err_code OUT NOCOPY VARCHAR2,
29 x_err_msg OUT NOCOPY VARCHAR2)
30 IS
31
32 l_uom_conv_rate NUMBER;
33 l_cost_layer_id NUMBER;
34 l_qty_layer_id NUMBER;
35 l_entity_id NUMBER;
36 l_entity_type NUMBER;
37 l_line_id NUMBER;
38 l_pri_qty NUMBER;
39 l_org_id NUMBER;
40 l_item_id NUMBER;
41 l_op_seq NUMBER;
42 l_txn_action_id NUMBER;
43 l_exp_flag NUMBER;
44 l_exp_item NUMBER;
45 l_subinv VARCHAR2(10);
46 l_stmt_num NUMBER;
47 l_err_num NUMBER;
48 l_err_code VARCHAR2(240);
49 l_err_msg VARCHAR2(2000);
50 l_cost_method_hook NUMBER;
51 cst_fail_method_hook EXCEPTION;
52 cst_no_wip_comp_txn EXCEPTION;
53 cst_process_error EXCEPTION;
54
55 -- Variables for eAM Support in PAC
56 l_zero_cost_flag NUMBER := 0;
57 l_applied_value NUMBER := 0;
58 l_return_status VARCHAR(1);
59 l_msg_return_status VARCHAR2(1);
60 l_msg_count NUMBER := 0;
61 l_msg_data VARCHAR2(8000);
62
63 l_api_name CONSTANT VARCHAR2(30) := 'charge_wip_material';
64 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
65 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
66
67 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
68 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
69 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
70 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
71 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
72 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
73
74
75 BEGIN
76
77 IF (l_pLog) THEN
78 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
79 l_module || '.begin',
80 l_api_name || ' <<< Parameters:
81 p_txn_id = ' || p_txn_id || '
82 p_exp_item = ' || p_exp_item || '
83 p_exp_flag = ' || p_exp_flag || '
84 p_material_relief_algorithm = '|| p_material_relief_algorithm || '
85 p_txn_category = ' || p_txn_category);
86 END IF;
87
88 ----------------------------------------------------------------------
89 -- Initialize Variables
90 ----------------------------------------------------------------------
91
92 l_err_num := 0;
93 l_err_code := '';
94 l_err_msg := '';
95 l_cost_method_hook := -1;
96 l_msg_data := '';
97 l_return_status := FND_API.G_RET_STS_SUCCESS;
98 l_msg_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 ----------------------------------------------------------------------
101 -- Make sure the txn is a WIP Component Txn
102 ----------------------------------------------------------------------
103
104 l_stmt_num := 5;
105
106 SELECT mmt.transaction_source_id entity_id,
107 we.entity_type entity_type,
108 mmt.repetitive_line_id line_id,
109 mmt.primary_quantity pri_qty,
110 mmt.inventory_item_id item_id,
111 mmt.operation_seq_num op_seq,
112 mmt.organization_id,
113 mmt.subinventory_code subinv,
114 mmt.transaction_action_id
115 INTO l_entity_id,
116 l_entity_type,
117 l_line_id,
118 l_pri_qty,
119 l_item_id,
120 l_op_seq,
121 l_org_id,
122 l_subinv,
123 l_txn_action_id
124 FROM mtl_material_transactions mmt,
125 wip_entities we
126 WHERE mmt.transaction_id = p_txn_id
127 AND mmt.transaction_source_type_id = 5
128 AND mmt.transaction_action_id IN (1,27, 33, 34)
129 AND we.wip_entity_id = mmt.transaction_source_id;
130
131 IF SQL%ROWCOUNT = 0 THEN
132 RAISE CST_NO_WIP_COMP_TXN;
133 END IF;
134
135 ----------------------------------------------------------------------
136 -- Check for row in cst_pac_wip_balance
137 ----------------------------------------------------------------------
138
139 l_stmt_num := 10;
140
141 CSTPPWRO.check_pacwip_bal_record (p_pac_period_id => p_pac_period_id,
142 p_cost_group_id => p_cost_group_id,
143 p_cost_type_id => p_cost_type_id,
144 p_org_id => l_org_id,
145 p_entity_id => l_entity_id,
146 p_entity_type => l_entity_type,
147 p_line_id => l_line_id,
148 p_op_seq => l_op_seq,
149 p_user_id => p_user_id,
150 p_request_id => p_request_id,
151 p_prog_app_id => p_prog_app_id,
152 p_prog_id => p_prog_id,
153 p_login_id => p_login_id,
154 x_err_num => l_err_num,
155 x_err_code => l_err_code,
156 x_err_msg => l_err_msg
157 );
158
159 IF (l_err_num <> 0) THEN
160
161 l_err_msg := SUBSTR('Fail_check_bal_rec: ent/line/op'
162 ||TO_CHAR(l_entity_id)
163 ||'/'
164 ||TO_CHAR(l_line_id)
165 ||'/'
166 ||TO_CHAR(l_op_seq)
167 ||':'
168 ||l_err_msg,1,2000);
169
170
171 RAISE CST_PROCESS_ERROR;
172 END IF;
173
174 ----------------------------------------------------------------------
175 -- Check and Create layer for the componenet item, if required
176 ----------------------------------------------------------------------
177
178 l_stmt_num := 15;
179
180 CSTPPCLM.layer_id (
181 i_pac_period_id => p_pac_period_id,
182 i_legal_entity => p_legal_entity,
183 i_item_id => l_item_id,
184 i_cost_group_id => p_cost_group_id,
185 o_cost_layer_id => l_cost_layer_id,
186 o_quantity_layer_id => l_qty_layer_id,
187 o_err_num => l_err_num,
188 o_err_code => l_err_code,
189 o_err_msg => l_err_msg);
190
191 IF (l_err_num <> 0) THEN
192 RAISE CST_PROCESS_ERROR;
193 END IF;
194
195 IF (l_cost_layer_id = 0 AND l_qty_layer_id = 0) THEN
196
197 l_stmt_num := 20;
198
199 CSTPPCLM.create_layer (
200 i_pac_period_id => p_pac_period_id,
201 i_legal_entity => p_legal_entity,
202 i_item_id => l_item_id,
203 i_cost_group_id => p_cost_group_id,
204 i_user_id => p_user_id,
205 i_login_id => p_login_id,
206 i_request_id => p_request_id,
207 i_prog_id => p_prog_id,
208 i_prog_appl_id => p_prog_app_id,
209 o_cost_layer_id => l_cost_layer_id,
210 o_quantity_layer_id => l_qty_layer_id,
211 o_err_num => l_err_num,
212 o_err_code => l_err_code,
213 o_err_msg => l_err_msg);
214
215 IF (l_err_num <> 0) THEN
216 RAISE CST_PROCESS_ERROR;
217 END IF;
218
219 END IF; -- Check Create Layer
220
221 ----------------------------------------------------------------------
222 -- Get the expense flags for this transaction if they are NULL
223 ----------------------------------------------------------------------
224
225 IF (p_exp_item IS NULL OR p_exp_flag IS NULL) THEN
226
227 l_stmt_num := 25;
228
229 CSTPPWAS.check_expense_flags (
230 p_item_id => l_item_id,
231 p_subinv => l_subinv,
232 p_org_id => l_org_id,
233 x_exp_item => l_exp_item,
234 x_exp_flag => l_exp_flag,
235 x_err_num => l_err_num,
236 x_err_code => l_err_code,
237 x_err_msg => l_err_msg);
238
239 IF (l_err_num <> 0) THEN
240
241 l_err_msg := SUBSTR('Item_id: '
242 ||TO_CHAR(l_item_id)
243 ||' '
244 ||l_err_msg,1,2000);
245
246 RAISE CST_PROCESS_ERROR;
247
248 END IF;
249
250 ELSE
251
252 l_exp_item := p_exp_item;
253
254 l_exp_flag := p_exp_flag;
255
256 END IF; -- check for exp flags
257
258
259 ----------------------------------------------------------------------
260 -- The hook is called for a costing method other than PWAC.
261 -- The hook should return -1 if it is not being used.
262 -- If the hook is used the transaction cost is picked from MPACD
263 -- The user will write a customized script to insert costs into
264 -- MPACD for WIP component transactions if hook is to be used.
265 -- If hook is not used then transaction costs are picked from CPIC
266 ----------------------------------------------------------------------
267
268
269 IF (p_cost_method <> 3) THEN
270
271 --------------------------------------------------------------------
272 -- The cost method is not Period weighted average.
273 -- The user should compute the issue costs and update
274 -- WPPB IN columns within their custom logic.
275 --------------------------------------------------------------------
276
277 l_stmt_num := 30;
278
279 l_cost_method_hook := CSTPFCHK.pac_wip_issue_cost_hook(
280 i_pac_period_id => p_pac_period_id,
281 i_org_id => l_org_id,
282 i_cost_group_id => p_cost_group_id,
283 i_cost_type_id => p_cost_type_id,
284 i_cost_method => p_cost_method,
285 i_txn_id => p_txn_id,
286 i_cost_layer_id => l_cost_layer_id,
287 i_qty_layer_id => l_qty_layer_id,
288 i_pac_rates_id => p_pac_rates_id,
289 i_item_id => l_item_id,
290 i_pri_qty => l_pri_qty,
291 i_txn_action_id => l_txn_action_id,
292 i_entity_id => l_entity_id,
293 i_line_id => l_line_id,
294 i_op_seq => l_op_seq,
295 i_exp_flag => l_exp_flag,
296 i_user_id => p_user_id,
297 i_login_id => p_login_id,
298 i_req_id => p_request_id,
299 i_prg_appl_id => p_prog_app_id,
300 i_prg_id => p_prog_id,
301 o_err_num => l_err_num,
302 o_err_code => l_err_code,
303 o_err_msg => l_err_msg);
304
305 IF (l_err_num <> 0) THEN
306
307 RAISE CST_FAIL_METHOD_HOOK;
308
309 END IF;
310
311 ELSE -- Cost method is not PWAC get cost from MPACD
312
313 --------------------------------------------------------------------
314 -- Cost Method is weighted average so get cost from CPIC table
315 --------------------------------------------------------------------
316
317 l_stmt_num := 35;
318
319 -- Check if eAM entity then compute actuals
320 IF l_entity_type in (6,7) THEN /* Also include closed WO for Actuals Bug 5366094 */
321
322 CST_UTILITY_PUB.get_ZeroCostIssue_Flag(
323 p_api_version => 1.0,
324 x_return_status => l_return_status,
325 x_msg_count => l_msg_count,
326 x_msg_data => l_msg_data,
327 p_txn_id => p_txn_id,
328 x_zero_cost_flag => l_zero_cost_flag);
329
330 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
331 l_err_msg := SUBSTR('fail_zero_cost_flag txn_id : '
332 ||TO_CHAR(p_txn_id)
333 ||l_err_msg,1,2000);
334 RAISE CST_PROCESS_ERROR;
335 END IF;
336
337
338 SELECT DECODE(l_zero_cost_flag,
339 0, nvl(cpic.item_cost,0), 0) * (-1 * l_pri_qty )
340 INTO l_applied_value
341 FROM wip_discrete_jobs wdj,
342 cst_pac_item_costs cpic
343 WHERE cpic.pac_period_id = p_pac_period_id
344 AND cpic.cost_group_id = p_cost_group_id
345 AND cpic.inventory_item_id = l_item_id
346 AND wdj.wip_entity_id = l_entity_id;
347
348
349 CST_PacEamCost_GRP.Compute_PAC_JobActuals(
350 p_api_version => 1.0,
351 x_return_status => l_return_status,
352 x_msg_count => l_msg_count,
353 x_msg_data => l_msg_data,
354 p_legal_entity_id => p_legal_entity,
355 p_cost_group_id => p_cost_group_id,
356 p_cost_type_id => p_cost_type_id,
357 p_pac_period_id => p_pac_period_id,
358 p_pac_ct_id => p_pac_rates_id,
359 p_organization_id => l_org_id,
360 p_txn_mode => 1, -- To indicate it is Material Item txn
361 p_txn_id => p_txn_id,
362 p_value => l_applied_value,
363 p_wip_entity_id => l_entity_id,
364 p_op_seq => l_op_seq,
365 p_resource_id => NULL,
366 p_resource_seq_num => NULL,
367 p_user_id => p_user_id,
368 p_request_id => p_request_id,
369 p_prog_app_id => p_prog_app_id,
370 p_prog_id => p_prog_id,
371 p_login_id => p_login_id);
372
373 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
374 l_err_msg := SUBSTR('fail_MAT_PAC_comt_jobAct ent : '
375 ||TO_CHAR(l_entity_id)
376 ||': (' || to_char(l_stmt_num) || '): '
377 ||l_err_msg,1,2000);
378 RAISE CST_PROCESS_ERROR;
379 END IF;
380
381 END IF; -- end eAM check
382
383 charge_wip_pwac_cost(
384 p_pac_period_id => p_pac_period_id,
385 p_cost_group_id => p_cost_group_id,
386 p_pri_qty => l_pri_qty,
387 p_item_id => l_item_id,
388 p_entity_id => l_entity_id,
389 p_line_id => l_line_id,
390 p_op_seq => l_op_seq,
391 p_material_relief_algorithm => p_material_relief_algorithm,
392 p_user_id => p_user_id,
393 p_login_id => p_login_id,
394 p_request_id => p_request_id,
395 p_prog_id => p_prog_id,
396 p_prog_app_id => p_prog_app_id,
397 x_err_num => l_err_num,
398 x_err_code => l_err_code,
399 x_err_msg => l_err_msg,
400 p_zero_cost_flag => l_zero_cost_flag); -- Sending this as rebuild items should respect this
401
402 IF (l_err_num <> 0) THEN
403 l_err_msg := SUBSTR('Item_id: ' || TO_CHAR(l_item_id) || ':' || l_err_msg,1,0040);
404 RAISE CST_PROCESS_ERROR;
405 END IF;
406
407
408 END IF; -- check for hook
409
410 ----------------------------------------------------------------------
411 -- Call Cost Processor
412 ----------------------------------------------------------------------
413
414 l_stmt_num := 40;
415
416 l_uom_conv_rate := 1;
417
418 IF (p_uom_control <> 1) THEN
419
420 l_stmt_num := 45;
421
422 CSTPPINV.get_um_rate (
423 i_txn_org_id => l_org_id,
424 i_master_org_id => p_master_org_id,
425 i_txn_cost_group_id => -1,
426 i_txfr_cost_group_id => -2,
427 i_txn_action_id => l_txn_action_id,
428 i_item_id => l_item_id,
429 i_uom_control => p_uom_control,
430 i_user_id => p_user_id,
431 i_login_id => p_login_id,
432 i_request_id => p_request_id,
433 i_prog_id => p_prog_id,
434 i_prog_appl_id => p_prog_app_id,
435 o_um_rate => l_uom_conv_rate,
436 o_err_num => l_err_num,
437 o_err_code => l_err_code,
438 o_err_msg => l_err_msg);
439
440 IF (l_err_num <> 0) THEN
441
442 l_err_msg := SUBSTR('UOM conv error txn_id: '
443 ||TO_CHAR(p_txn_id)
444 ||':'
445 ||l_err_msg,1,2000);
446 RAISE CST_PROCESS_ERROR;
447
448 END IF;
449
450 END IF; -- check for uom control level
451
452 l_stmt_num := 50;
453
454 CSTPPWAC.cost_processor
455 (i_legal_entity => p_legal_entity,
456 i_pac_period_id => p_pac_period_id,
457 i_org_id => l_org_id,
458 i_cost_group_id => p_cost_group_id,
459 i_txn_cost_group_id => NULL,
460 i_txfr_cost_group_id => NULL,
461 i_cost_type_id => p_cost_type_id,
462 i_cost_method => p_cost_method,
463 i_process_group => 2,
464 i_txn_id => p_txn_id,
465 i_qty_layer_id => l_qty_layer_id,
466 i_cost_layer_id => l_cost_layer_id,
467 i_pac_rates_id => p_pac_rates_id,
468 i_item_id => l_item_id,
469 i_txn_qty => l_pri_qty * l_uom_conv_rate,
470 i_txn_action_id => l_txn_action_id,
471 i_txn_src_type_id => 5,
472 i_fob_point => NULL,
473 i_exp_item => l_exp_item,
474 i_exp_flag => l_exp_flag,
475 i_cost_hook_used => l_cost_method_hook,
476 i_user_id => p_user_id,
477 i_login_id => p_login_id,
478 i_req_id => p_request_id,
479 i_prg_appl_id => p_prog_app_id,
480 i_prg_id => p_prog_id,
481 i_txn_category => p_txn_category,
482 o_err_num => l_err_num,
483 o_err_code => l_err_code,
484 o_err_msg => l_err_msg);
485
486 IF (l_err_num <> 0) THEN
487
488 l_err_msg := SUBSTR('Txn_id: '
489 ||TO_CHAR(p_txn_id)
490 ||':'
491 ||l_err_msg,1,2000);
492
493 RAISE CST_PROCESS_ERROR;
494
495 END IF;
496
497 -- ================================================================
498 -- FP 12.0-12.1.3 Bug 9280484 fix: Update periodic primary quantity
499 -- with converted UOM transaction quantity
500 -- ================================================================
501 UPDATE mtl_material_transactions mmt
502 SET mmt.periodic_primary_quantity = l_pri_qty * l_uom_conv_rate
503 WHERE mmt.transaction_id = p_txn_id;
504
505 x_cost_method_hook := l_cost_method_hook;
506
507 IF (l_pLog) THEN
508 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
509 l_module || '.end',
510 l_api_name || ' >>>');
511 END IF;
512
513 EXCEPTION
514
515 WHEN CST_NO_WIP_COMP_TXN THEN
516 x_err_num := 20007;
517 x_err_code := SUBSTR('CSTPPWMT.charge_wip_material('
518 || to_char(l_stmt_num)
519 || '): '
520 || l_err_msg
521 || '. ',1,240);
522 fnd_message.set_name('BOM', 'CST_NO_WIP_COMP_TXN');
523 x_err_msg := SUBSTR(fnd_message.get,1,2000);
524
525 WHEN CST_FAIL_METHOD_HOOK THEN
526 x_err_num := 20010;
527 x_err_code := SUBSTR('CSTPPWMT.charge_wip_material('
528 || to_char(l_stmt_num)
529 || '): '
530 || l_err_msg
531 || '. ',1,240);
532 fnd_message.set_name('BOM', 'CST_FAIL_METHOD_HOOK');
533 x_err_msg := SUBSTR(fnd_message.get,1,2000);
534
535 WHEN CST_PROCESS_ERROR THEN
536 x_err_num := l_err_num;
537 x_err_code := l_err_code;
538 x_err_msg := l_err_msg;
539
540 WHEN OTHERS THEN
541 IF (l_uLog) THEN
542 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
543 l_module || '.' || l_stmt_num,
544 SQLERRM);
545 END IF;
546 ROLLBACK;
547 x_err_num := SQLCODE;
548 x_err_code := NULL;
549 x_err_msg := SUBSTR('CSTPPWMT.charge_wip_material('
550 || to_char(l_stmt_num)
551 || '): '
552 ||SQLERRM,1,2000);
553
554 END charge_wip_material;
555
556 /*----------------------------------------------------------------------------*
557 | PUBLIC PROCEDURE |
558 | charge_wip_pwac_cost |
559 *----------------------------------------------------------------------------*/
560 PROCEDURE charge_wip_pwac_cost(
561 p_pac_period_id IN NUMBER,
562 p_cost_group_id IN NUMBER,
563 p_pri_qty IN NUMBER,
564 p_item_id IN NUMBER,
565 p_entity_id IN NUMBER,
566 p_line_id IN NUMBER,
567 p_op_seq IN NUMBER,
568 p_material_relief_algorithm IN NUMBER,
569 p_user_id IN NUMBER,
570 p_login_id IN NUMBER,
571 p_request_id IN NUMBER,
572 p_prog_id IN NUMBER,
573 p_prog_app_id IN NUMBER,
574 x_err_num OUT NOCOPY NUMBER,
575 x_err_code OUT NOCOPY VARCHAR2,
576 x_err_msg OUT NOCOPY VARCHAR2,
577 p_zero_cost_flag IN NUMBER) -- Default 0 Variable added for eAM support in PAC
578
579 IS
580
581 l_stmt_num NUMBER;
582 l_err_num NUMBER;
583 l_err_code VARCHAR2(240);
584 l_err_msg VARCHAR2(240);
585
586 l_api_name CONSTANT VARCHAR2(30) := 'charge_wip_pwac_cost';
587 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
588 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
589
590 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
591 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
592 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
593 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
594 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
595 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
596
597 BEGIN
598
599 IF (l_pLog) THEN
600 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
601 l_module || '.begin',
602 l_api_name || ' <<< Parameters:
603 p_pri_qty = ' || p_pri_qty || '
604 p_item_id = ' || p_item_id || '
605 p_entity_id = ' || p_entity_id || '
606 p_line_id = ' || p_line_id ||'
607 p_op_seq = ' || p_op_seq );
608 END IF;
609 ----------------------------------------------------------------------
610 -- Initialize Variables
611 ----------------------------------------------------------------------
612
613 l_err_num := 0;
614 l_err_code := '';
615 l_err_msg := '';
616
617 --------------------------------------------------------------------
618 -- Cost Method is weighted average so get cost from CPIC table
619 -- and update WPPB
620 --------------------------------------------------------------------
621
622 l_stmt_num := 5;
623
624 UPDATE wip_pac_period_balances wppb
625 SET
626 (pl_material_in,
627 pl_material_overhead_in,
628 pl_resource_in,
629 pl_outside_processing_in,
630 pl_overhead_in,
631 last_update_date,
632 last_updated_by,
633 last_update_login,
634 request_id,
635 program_application_id,
636 program_id,
637 program_update_date) =
638 (
639 SELECT -- Checking p_zero_cost_flag for rebuild items as part of eAM support in PAC
640 NVL(wppb.pl_material_in,0) +
641 (DECODE(p_zero_cost_flag,0,NVL(cpic.material_cost,0),0) * (-1 * p_pri_qty)),
642
643 NVL(wppb.pl_material_overhead_in,0) +
644 (DECODE(p_zero_cost_flag,0,NVL(cpic.material_overhead_cost,0),0) * (-1 * p_pri_qty)),
645
646 NVL(wppb.pl_resource_in,0) +
647 (DECODE(p_zero_cost_flag,0,NVL(cpic.resource_cost,0),0) * (-1 * p_pri_qty)),
648
649 NVL(wppb.pl_outside_processing_in,0)+
650 (DECODE(p_zero_cost_flag,0,NVL(cpic.outside_processing_cost,0),0) * (-1 * p_pri_qty)),
651
652 NVL(wppb.pl_overhead_in,0) +
653 (DECODE(p_zero_cost_flag,0,NVL(cpic.overhead_cost,0),0) * (-1 * p_pri_qty)),
654
655 SYSDATE,
656 p_user_id,
657 p_login_id,
658 p_request_id,
659 p_prog_app_id,
660 p_prog_id,
661 SYSDATE
662 FROM cst_pac_item_costs cpic
663 WHERE cpic.pac_period_id = p_pac_period_id
664 AND cpic.cost_group_id = p_cost_group_id
665 AND cpic.inventory_item_id = p_item_id
666 )
667 WHERE wppb.pac_period_id = p_pac_period_id
668 AND wppb.cost_group_id = p_cost_group_id
669 AND wppb.wip_entity_id = p_entity_id
670 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
671 AND wppb.operation_seq_num = p_op_seq
672 AND EXISTS
673 ( SELECT 'X'
674 FROM cst_pac_item_costs cpic2
675 WHERE cpic2.pac_period_id = p_pac_period_id
676 AND cpic2.cost_group_id = p_cost_group_id
677 AND cpic2.inventory_item_id = p_item_id);
678
679 /*Bug 2995978 - Added for updating assembly pull Components IN values*/
680 -- No need to Check p_zero_cost_flag for rebuild items as eAM does not have Assembly pull items.
681
682 l_stmt_num := 10;
683
684 UPDATE wip_pac_period_balances wppb
685 SET
686 (pl_material_in_apull,
687 pl_material_overhead_in_apull,
688 pl_resource_in_apull,
689 pl_outside_processing_in_apull,
690 pl_overhead_in_apull,
691 last_update_date,
692 last_updated_by,
693 last_update_login,
694 request_id,
695 program_application_id,
696 program_id,
697 program_update_date) =
698 (
699 SELECT
700 NVL(wppb.pl_material_in_apull,0) +
701 (NVL(cpic.material_cost,0) * (-1 * p_pri_qty)),
702
703 NVL(wppb.pl_material_overhead_in_apull,0) +
704 (NVL(cpic.material_overhead_cost,0) * (-1 * p_pri_qty)),
705
706 NVL(wppb.pl_resource_in_apull,0) +
707 (NVL(cpic.resource_cost,0) * (-1 * p_pri_qty)),
708
709 NVL(wppb.pl_outside_processing_in_apull,0)+
710 (NVL(cpic.outside_processing_cost,0)* (-1 * p_pri_qty)),
711
712 NVL(wppb.pl_overhead_in_apull,0) +
713 (NVL(cpic.overhead_cost,0) * (-1 * p_pri_qty)),
714
715 SYSDATE,
716 p_user_id,
717 p_login_id,
718 p_request_id,
719 p_prog_app_id,
720 p_prog_id,
721 SYSDATE
722 FROM cst_pac_item_costs cpic
723 WHERE cpic.pac_period_id = p_pac_period_id
724 AND cpic.cost_group_id = p_cost_group_id
725 AND cpic.inventory_item_id = p_item_id
726 )
727 WHERE wppb.pac_period_id = p_pac_period_id
728 AND wppb.cost_group_id = p_cost_group_id
729 AND wppb.wip_entity_id = p_entity_id
730 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
731 AND wppb.operation_seq_num = p_op_seq
732 AND EXISTS
733 ( SELECT 'X'
734 FROM cst_pac_item_costs cpic2
735 WHERE cpic2.pac_period_id = p_pac_period_id
736 AND cpic2.cost_group_id = p_cost_group_id
737 AND cpic2.inventory_item_id = p_item_id)
738 AND EXISTS
739 ( SELECT 'ASSY PULL'
740 FROM wip_requirement_operations wro
741 WHERE wro.wip_entity_id = p_entity_id
742 AND wro.wip_supply_type = 2
743 AND wro.inventory_item_id = p_item_id
744 AND wro.operation_seq_num = p_op_seq);
745
746 /* R12 PAC Enhancement: Populate CPROCD only for Use BOM based Material Algo */
747 IF (p_material_relief_algorithm = 0) THEN
748 -- BOM Based algo
749 l_stmt_num := 40;
750 MERGE INTO CST_PAC_REQ_OPER_COST_DETAILS cprocd
751 USING (SELECT sum(NVL(cpicd.ITEM_COST,0) * -1 * p_pri_qty) cost,
752 (-1 * p_pri_qty) qty,
753 cpicd.cost_element_id cost_element_id
754 FROM CST_PAC_ITEM_COSTS cpic,
755 CST_PAC_ITEM_COST_DETAILS cpicd
756 WHERE cpic.pac_period_id = p_pac_period_id
757 AND cpic.cost_group_id = p_cost_group_id
758 AND cpic.inventory_item_id = p_item_id
759 AND cpic.cost_layer_id = cpicd.cost_layer_id
760 GROUP BY cpicd.cost_element_id ) s
761
762 ON ( cprocd.wip_entity_id = p_entity_id
763 AND nvl(cprocd.line_id,-99) = nvl(p_line_id, -99)
764 AND cprocd.inventory_item_id = p_item_id
765 AND cprocd.operation_seq_num = p_op_seq
766 AND cprocd.pac_period_id = p_pac_period_id
767 AND cprocd.cost_group_id = p_cost_group_id
768 AND cprocd.cost_element_id = s.cost_element_id)
769
770 WHEN MATCHED THEN UPDATE SET cprocd.applied_value = nvl(cprocd.applied_value,0) + nvl(s.cost,0),
771 cprocd.applied_quantity = nvl( cprocd.applied_quantity,0) + nvl(s.qty,0),
772 cprocd.last_update_date = SYSDATE,
773 cprocd.last_updated_by = p_user_id,
774 cprocd.last_update_login = p_login_id,
775 cprocd.request_id = p_request_id,
776 cprocd.program_application_id = p_prog_app_id,
777 cprocd.program_id = p_prog_id,
778 cprocd.program_update_date = SYSDATE
779
780 WHEN NOT MATCHED THEN INSERT ( pac_period_id,
781 cost_group_id,
782 wip_entity_id,
783 line_id,
784 inventory_item_id,
785 cost_element_id,
786 operation_seq_num,
787 applied_value,
788 applied_quantity,
789 relieved_value,
790 relieved_quantity,
791 comp_variance,
792 creation_date,
793 created_by,
794 last_update_date,
795 last_updated_by,
796 last_update_login,
797 request_id,
798 program_application_id,
799 program_id,
800 program_update_date)
801 VALUES (p_pac_period_id,
802 p_cost_group_id,
803 p_entity_id,
804 p_line_id,
805 p_item_id,
806 s.cost_element_id,
807 p_op_seq,
808 s.cost,
809 s.qty,
810 0,
811 0,
812 0,
813 SYSDATE,
814 p_user_id,
815 SYSDATE,
816 p_user_id,
817 p_login_id,
818 p_request_id,
819 p_prog_app_id,
820 p_prog_id,
821 SYSDATE);
822 END IF;
823
824 IF (l_pLog) THEN
825 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
826 l_module || '.end',
827 l_api_name || ' >>>');
828 END IF;
829
830 EXCEPTION
831 WHEN OTHERS THEN
832 IF (l_uLog) THEN
833 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
834 l_module || '.' || l_stmt_num,
835 SQLERRM);
836 END IF;
837 ROLLBACK;
838 x_err_num := SQLCODE;
839 x_err_code := NULL;
840 x_err_msg := SUBSTR('CSTPPWMT.charge_wip_pwac_cost('
841 || to_char(l_stmt_num)
842 || '): '
843 ||SQLERRM,1,240);
844
845 END charge_wip_pwac_cost;
846
847 END cstppwmt;