[Home] [Help]
PACKAGE BODY: APPS.CSTPPWMT
Source
1 PACKAGE BODY CSTPPWMT AS
2 /* $Header: CSTPWMTB.pls 120.8 2006/07/03 11:18:35 sikhanna noship $ */
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 x_cost_method_hook := l_cost_method_hook;
498
499 IF (l_pLog) THEN
500 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
501 l_module || '.end',
502 l_api_name || ' >>>');
503 END IF;
504
505 EXCEPTION
506
507 WHEN CST_NO_WIP_COMP_TXN THEN
508 x_err_num := 20007;
509 x_err_code := SUBSTR('CSTPPWMT.charge_wip_material('
510 || to_char(l_stmt_num)
511 || '): '
512 || l_err_msg
513 || '. ',1,240);
514 fnd_message.set_name('BOM', 'CST_NO_WIP_COMP_TXN');
515 x_err_msg := SUBSTR(fnd_message.get,1,2000);
516
517 WHEN CST_FAIL_METHOD_HOOK THEN
518 x_err_num := 20010;
519 x_err_code := SUBSTR('CSTPPWMT.charge_wip_material('
520 || to_char(l_stmt_num)
521 || '): '
522 || l_err_msg
523 || '. ',1,240);
524 fnd_message.set_name('BOM', 'CST_FAIL_METHOD_HOOK');
525 x_err_msg := SUBSTR(fnd_message.get,1,2000);
526
527 WHEN CST_PROCESS_ERROR THEN
528 x_err_num := l_err_num;
529 x_err_code := l_err_code;
530 x_err_msg := l_err_msg;
531
532 WHEN OTHERS THEN
533 IF (l_uLog) THEN
534 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
535 l_module || '.' || l_stmt_num,
536 SQLERRM);
537 END IF;
538 ROLLBACK;
539 x_err_num := SQLCODE;
540 x_err_code := NULL;
541 x_err_msg := SUBSTR('CSTPPWMT.charge_wip_material('
542 || to_char(l_stmt_num)
543 || '): '
544 ||SQLERRM,1,2000);
545
546 END charge_wip_material;
547
548 /*----------------------------------------------------------------------------*
549 | PUBLIC PROCEDURE |
550 | charge_wip_pwac_cost |
551 *----------------------------------------------------------------------------*/
552 PROCEDURE charge_wip_pwac_cost(
553 p_pac_period_id IN NUMBER,
554 p_cost_group_id IN NUMBER,
555 p_pri_qty IN NUMBER,
556 p_item_id IN NUMBER,
557 p_entity_id IN NUMBER,
558 p_line_id IN NUMBER,
559 p_op_seq IN NUMBER,
560 p_material_relief_algorithm IN NUMBER,
561 p_user_id IN NUMBER,
562 p_login_id IN NUMBER,
563 p_request_id IN NUMBER,
564 p_prog_id IN NUMBER,
565 p_prog_app_id IN NUMBER,
566 x_err_num OUT NOCOPY NUMBER,
567 x_err_code OUT NOCOPY VARCHAR2,
568 x_err_msg OUT NOCOPY VARCHAR2,
569 p_zero_cost_flag IN NUMBER) -- Default 0 Variable added for eAM support in PAC
570
571 IS
572
573 l_stmt_num NUMBER;
574 l_err_num NUMBER;
575 l_err_code VARCHAR2(240);
576 l_err_msg VARCHAR2(240);
577
578 l_api_name CONSTANT VARCHAR2(30) := 'charge_wip_pwac_cost';
579 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
580 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
581
582 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
583 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
584 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
585 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
586 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
587 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
588
589 BEGIN
590
591 IF (l_pLog) THEN
592 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
593 l_module || '.begin',
594 l_api_name || ' <<< Parameters:
595 p_pri_qty = ' || p_pri_qty || '
596 p_item_id = ' || p_item_id || '
597 p_entity_id = ' || p_entity_id || '
598 p_line_id = ' || p_line_id ||'
599 p_op_seq = ' || p_op_seq );
600 END IF;
601 ----------------------------------------------------------------------
602 -- Initialize Variables
603 ----------------------------------------------------------------------
604
605 l_err_num := 0;
606 l_err_code := '';
607 l_err_msg := '';
608
609 --------------------------------------------------------------------
610 -- Cost Method is weighted average so get cost from CPIC table
611 -- and update WPPB
612 --------------------------------------------------------------------
613
614 l_stmt_num := 5;
615
616 UPDATE wip_pac_period_balances wppb
617 SET
618 (pl_material_in,
619 pl_material_overhead_in,
620 pl_resource_in,
621 pl_outside_processing_in,
622 pl_overhead_in,
623 last_update_date,
624 last_updated_by,
625 last_update_login,
626 request_id,
627 program_application_id,
628 program_id,
629 program_update_date) =
630 (
631 SELECT -- Checking p_zero_cost_flag for rebuild items as part of eAM support in PAC
632 NVL(wppb.pl_material_in,0) +
633 (DECODE(p_zero_cost_flag,0,NVL(cpic.material_cost,0),0) * (-1 * p_pri_qty)),
634
635 NVL(wppb.pl_material_overhead_in,0) +
636 (DECODE(p_zero_cost_flag,0,NVL(cpic.material_overhead_cost,0),0) * (-1 * p_pri_qty)),
637
638 NVL(wppb.pl_resource_in,0) +
639 (DECODE(p_zero_cost_flag,0,NVL(cpic.resource_cost,0),0) * (-1 * p_pri_qty)),
640
641 NVL(wppb.pl_outside_processing_in,0)+
642 (DECODE(p_zero_cost_flag,0,NVL(cpic.outside_processing_cost,0),0) * (-1 * p_pri_qty)),
643
644 NVL(wppb.pl_overhead_in,0) +
645 (DECODE(p_zero_cost_flag,0,NVL(cpic.overhead_cost,0),0) * (-1 * p_pri_qty)),
646
647 SYSDATE,
648 p_user_id,
649 p_login_id,
650 p_request_id,
651 p_prog_app_id,
652 p_prog_id,
653 SYSDATE
654 FROM cst_pac_item_costs cpic
655 WHERE cpic.pac_period_id = p_pac_period_id
656 AND cpic.cost_group_id = p_cost_group_id
657 AND cpic.inventory_item_id = p_item_id
658 )
659 WHERE wppb.pac_period_id = p_pac_period_id
660 AND wppb.cost_group_id = p_cost_group_id
661 AND wppb.wip_entity_id = p_entity_id
662 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
663 AND wppb.operation_seq_num = p_op_seq
664 AND EXISTS
665 ( SELECT 'X'
666 FROM cst_pac_item_costs cpic2
667 WHERE cpic2.pac_period_id = p_pac_period_id
668 AND cpic2.cost_group_id = p_cost_group_id
669 AND cpic2.inventory_item_id = p_item_id);
670
671 /*Bug 2995978 - Added for updating assembly pull Components IN values*/
672 -- No need to Check p_zero_cost_flag for rebuild items as eAM does not have Assembly pull items.
673
674 l_stmt_num := 10;
675
676 UPDATE wip_pac_period_balances wppb
677 SET
678 (pl_material_in_apull,
679 pl_material_overhead_in_apull,
680 pl_resource_in_apull,
681 pl_outside_processing_in_apull,
682 pl_overhead_in_apull,
683 last_update_date,
684 last_updated_by,
685 last_update_login,
686 request_id,
687 program_application_id,
688 program_id,
689 program_update_date) =
690 (
691 SELECT
692 NVL(wppb.pl_material_in_apull,0) +
693 (NVL(cpic.material_cost,0) * (-1 * p_pri_qty)),
694
695 NVL(wppb.pl_material_overhead_in_apull,0) +
696 (NVL(cpic.material_overhead_cost,0) * (-1 * p_pri_qty)),
697
698 NVL(wppb.pl_resource_in_apull,0) +
699 (NVL(cpic.resource_cost,0) * (-1 * p_pri_qty)),
700
701 NVL(wppb.pl_outside_processing_in_apull,0)+
702 (NVL(cpic.outside_processing_cost,0)* (-1 * p_pri_qty)),
703
704 NVL(wppb.pl_overhead_in_apull,0) +
705 (NVL(cpic.overhead_cost,0) * (-1 * p_pri_qty)),
706
707 SYSDATE,
708 p_user_id,
709 p_login_id,
710 p_request_id,
711 p_prog_app_id,
712 p_prog_id,
713 SYSDATE
714 FROM cst_pac_item_costs cpic
715 WHERE cpic.pac_period_id = p_pac_period_id
716 AND cpic.cost_group_id = p_cost_group_id
717 AND cpic.inventory_item_id = p_item_id
718 )
719 WHERE wppb.pac_period_id = p_pac_period_id
720 AND wppb.cost_group_id = p_cost_group_id
721 AND wppb.wip_entity_id = p_entity_id
722 AND NVL(wppb.line_id,-99) = NVL(p_line_id,-99)
723 AND wppb.operation_seq_num = p_op_seq
724 AND EXISTS
725 ( SELECT 'X'
726 FROM cst_pac_item_costs cpic2
727 WHERE cpic2.pac_period_id = p_pac_period_id
728 AND cpic2.cost_group_id = p_cost_group_id
729 AND cpic2.inventory_item_id = p_item_id)
730 AND EXISTS
731 ( SELECT 'ASSY PULL'
732 FROM wip_requirement_operations wro
733 WHERE wro.wip_entity_id = p_entity_id
734 AND wro.wip_supply_type = 2
735 AND wro.inventory_item_id = p_item_id
736 AND wro.operation_seq_num = p_op_seq);
737
738 /* R12 PAC Enhancement: Populate CPROCD only for Use BOM based Material Algo */
739 IF (p_material_relief_algorithm = 0) THEN
740 -- BOM Based algo
741 l_stmt_num := 40;
742 MERGE INTO CST_PAC_REQ_OPER_COST_DETAILS cprocd
743 USING (SELECT sum(NVL(cpicd.ITEM_COST,0) * -1 * p_pri_qty) cost,
744 (-1 * p_pri_qty) qty,
745 cpicd.cost_element_id cost_element_id
746 FROM CST_PAC_ITEM_COSTS cpic,
747 CST_PAC_ITEM_COST_DETAILS cpicd
748 WHERE cpic.pac_period_id = p_pac_period_id
749 AND cpic.cost_group_id = p_cost_group_id
750 AND cpic.inventory_item_id = p_item_id
751 AND cpic.cost_layer_id = cpicd.cost_layer_id
752 GROUP BY cpicd.cost_element_id ) s
753
754 ON ( cprocd.wip_entity_id = p_entity_id
755 AND nvl(cprocd.line_id,-99) = nvl(p_line_id, -99)
756 AND cprocd.inventory_item_id = p_item_id
757 AND cprocd.operation_seq_num = p_op_seq
758 AND cprocd.pac_period_id = p_pac_period_id
759 AND cprocd.cost_group_id = p_cost_group_id
760 AND cprocd.cost_element_id = s.cost_element_id)
761
762 WHEN MATCHED THEN UPDATE SET cprocd.applied_value = nvl(cprocd.applied_value,0) + nvl(s.cost,0),
763 cprocd.applied_quantity = nvl( cprocd.applied_quantity,0) + nvl(s.qty,0),
764 cprocd.last_update_date = SYSDATE,
765 cprocd.last_updated_by = p_user_id,
766 cprocd.last_update_login = p_login_id,
767 cprocd.request_id = p_request_id,
768 cprocd.program_application_id = p_prog_app_id,
769 cprocd.program_id = p_prog_id,
770 cprocd.program_update_date = SYSDATE
771
772 WHEN NOT MATCHED THEN INSERT ( pac_period_id,
773 cost_group_id,
774 wip_entity_id,
775 line_id,
776 inventory_item_id,
777 cost_element_id,
778 operation_seq_num,
779 applied_value,
780 applied_quantity,
781 relieved_value,
782 relieved_quantity,
783 comp_variance,
784 creation_date,
785 created_by,
786 last_update_date,
787 last_updated_by,
788 last_update_login,
789 request_id,
790 program_application_id,
791 program_id,
792 program_update_date)
793 VALUES (p_pac_period_id,
794 p_cost_group_id,
795 p_entity_id,
796 p_line_id,
797 p_item_id,
798 s.cost_element_id,
799 p_op_seq,
800 s.cost,
801 s.qty,
802 0,
803 0,
804 0,
805 SYSDATE,
806 p_user_id,
807 SYSDATE,
808 p_user_id,
809 p_login_id,
810 p_request_id,
811 p_prog_app_id,
812 p_prog_id,
813 SYSDATE);
814 END IF;
815
816 IF (l_pLog) THEN
817 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
818 l_module || '.end',
819 l_api_name || ' >>>');
820 END IF;
821
822 EXCEPTION
823 WHEN OTHERS THEN
824 IF (l_uLog) THEN
825 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
826 l_module || '.' || l_stmt_num,
827 SQLERRM);
828 END IF;
829 ROLLBACK;
830 x_err_num := SQLCODE;
831 x_err_code := NULL;
832 x_err_msg := SUBSTR('CSTPPWMT.charge_wip_pwac_cost('
833 || to_char(l_stmt_num)
834 || '): '
835 ||SQLERRM,1,240);
836
837 END charge_wip_pwac_cost;
838
839 END cstppwmt;