[Home] [Help]
PACKAGE BODY: APPS.CSTPACWP
Source
1 PACKAGE BODY CSTPACWP AS
2 /* $Header: CSTPACPB.pls 120.1 2005/10/03 17:36:08 visrivas noship $ */
3
4 PROCEDURE cost_wip_trx(
5 l_trx_id IN NUMBER,
6 l_comm_iss_flag IN NUMBER,
7 l_cost_type_id IN NUMBER,
8 l_cost_method IN NUMBER,
9 l_rates_cost_type_id IN NUMBER,
10 l_cost_grp_id IN NUMBER,
11 l_txfr_cost_grp_id IN NUMBER,
12 l_exp_flag IN NUMBER,
13 l_exp_item_flag IN NUMBER,
14 l_flow_schedule IN NUMBER,
15 l_user_id IN NUMBER,
16 l_login_id IN NUMBER,
17 l_request_id IN NUMBER,
18 l_prog_id IN NUMBER,
19 l_prog_app_id IN NUMBER,
20 err_num OUT NOCOPY NUMBER,
21 err_code OUT NOCOPY VARCHAR2,
22 err_msg OUT NOCOPY VARCHAR2)
23
24 is
25
26 l_layer_id NUMBER;
27 l_cost_group_id NUMBER;
28 l_inv_item_id NUMBER;
29 l_org_id NUMBER;
30 l_txn_date DATE;
31 l_period_id NUMBER;
32 l_action_id NUMBER;
33 l_src_type_id NUMBER;
34 l_txn_qty NUMBER;
35 l_wip_entity_id NUMBER;
36 l_op_seq_num NUMBER;
37 l_final_comp_flag VARCHAR2(1);
38 l_movhd_cost_type_id NUMBER;
39 stmt_num NUMBER;
40 l_return NUMBER;
41 l_row_count NUMBER;
42 l_mtl_txn_exists NUMBER;
43 l_entity_type NUMBER;
44 l_err_num NUMBER;
45 l_err_code VARCHAR2(240);
46 l_err_msg VARCHAR2(240);
47 proc_fail EXCEPTION;
48
49 l_debug VARCHAR2(80);
50 l_msg_return_status VARCHAR2(1);
51
52 l_trx_info CST_XLA_PVT.t_xla_inv_trx_info;
53 l_return_status varchar2(1);
54 l_msg_count number := 0;
55 l_msg_data varchar2(8000);
56 BEGIN
57
58 --
59 -- initialize l_movhd_cost_type_id. Bug 609001
60 --
61 l_movhd_cost_type_id := l_rates_cost_type_id;
62
63 err_num:=0;
64 l_err_num := 0;
65 l_debug := fnd_profile.value('MRP_DEBUG');
66
67 stmt_num := 10;
68
69 if (l_debug = 'Y') then
70 FND_FILE.PUT_LINE(FND_FILE.log, 'CSTPACPB.cost_wip_trx <<<');
71 end if;
72
73 select
74 inventory_item_id,
75 organization_id,
76 transaction_date,
77 transaction_action_id,
78 transaction_source_type_id,
79 primary_quantity,
80 transaction_source_id,
81 operation_seq_num,
82 nvl(final_completion_flag,'N'),
83 acct_period_id
84 into
85 l_inv_item_id,
86 l_org_id,
87 l_txn_date,
88 l_action_id,
89 l_src_type_id,
90 l_txn_qty,
91 l_wip_entity_id,
92 l_op_seq_num,
93 l_final_comp_flag,
94 l_period_id
95 from mtl_material_transactions
96 where
97 transaction_id=l_trx_id;
98
99
100 -- Check to see if the item has a row in cst_quantity_layers.
101
102 -- For a regular transaction the layer_id in MCACD corresponds to
103 -- the cost_group_id in MMT. For a CITW txn however, the WIP issue
104 -- is being done from the txfr_cost_group in MMT and so we should
105 -- fetch the layer_id corresponding to this.
106
107 IF (l_comm_iss_flag <>1) THEN
108
109 l_layer_id:=CSTPACLM.layer_id(l_org_id,l_inv_item_id,
110 l_cost_grp_id,
111 l_err_num,l_err_code,l_err_msg);
112
113 ELSE
114
115 l_layer_id:=CSTPACLM.layer_id(l_org_id,l_inv_item_id,
116 l_txfr_cost_grp_id,
117 l_err_num,l_err_code,l_err_msg);
118
119 END IF;
120
121 IF (l_err_num<>0) THEN
122 raise proc_fail;
123 END IF;
124
125
126 -- If row exists proceed, else create row.
127
128 IF l_layer_id = 0 THEN
129 l_layer_id:=
130 CSTPACLM.create_layer(i_org_id => l_org_id,
131 i_item_id => l_inv_item_id,
132 i_cost_group_id => l_cost_grp_id,
133 i_user_id => l_user_id,
134 i_request_id => l_request_id,
135 i_prog_id => l_prog_id,
136 i_prog_appl_id => l_prog_app_id,
137 i_txn_id => l_trx_id,
138 o_err_num => l_err_num,
139 o_err_code => l_err_code,
140 o_err_msg => l_err_msg);
141 END IF;
142
143
144 IF (l_err_num<>0) THEN
145 raise proc_fail;
146 END IF;
147
148
149
150 -- Prior to doing any further processing, check for a CFM txn if a row
151 -- exists in WPB for that schedule. If no row exists then this fn will
152 -- create a row automatically.
153
154
155 If (l_flow_schedule = 1) THEN
156
157 l_return:= CSTPCFMS.wip_cfm_cbr(i_org_id => l_org_id,
158 i_user_id => l_user_id,
159 i_login_id => l_user_id,
160 i_acct_period_id => l_period_id,
161 i_wip_entity_id => l_wip_entity_id,
162 err_buf => l_err_msg);
163
164 If (l_return <> 0) THEN
165 if (l_debug = 'Y') then
166 fnd_file.put_line(fnd_file.log, 'proc_fail-cstpcfms'||SQLERRM);
167 end if;
168 raise proc_fail;
169 END IF;
170
171 END IF;
172
173
174 -- Based on transaction_action_id call appropriate function for processing.
175 -- Need to also account for the 2 new wip txns.
176
177
178 /********************************************************
179 * IF Component Issue txn OR Component Return txn OR *
180 * Negative Component Issue txn OR Negative Component *
181 * Return txn, then ..... *
182 ********************************************************/
183
184 /********************************************************
185 * Also, do so if it is not flow schedule - tchan
186 ********************************************************/
187
188
189 IF (l_flow_schedule <> 1 AND
190 (l_action_id = 1 OR
191 l_action_id = 27 OR
192 l_action_id = 33 OR
193 l_action_id = 34)
194 ) then
195
196 CSTPACIR.issue(i_trx_id => l_trx_id,
197 i_layer_id => l_layer_id,
198 i_inv_item_id => l_inv_item_id,
199 i_org_id => l_org_id,
200 i_wip_entity_id => l_wip_entity_id,
201 i_txn_qty => l_txn_qty,
202 i_op_seq_num => l_op_seq_num,
203 i_user_id => l_user_id,
204 i_login_id => l_login_id,
205 i_request_id => l_request_id,
206 i_prog_id => l_prog_id,
207 i_prog_appl_id => l_prog_app_id,
208 err_num => l_err_num,
209 err_code => l_err_code,
210 err_msg => l_err_msg);
211
212
213
214 ELSIF (l_action_id = 31 AND l_flow_schedule <> 1) THEN
215 CSTPACWC.complete(i_trx_id => l_trx_id,
216 i_txn_qty => l_txn_qty,
217 i_txn_date => l_txn_date,
218 i_acct_period_id => l_period_id,
219 i_wip_entity_id => l_wip_entity_id,
220 i_org_id => l_org_id,
221 i_inv_item_id => l_inv_item_id,
222 i_cost_type_id => 2,
223 i_res_cost_type_id => l_rates_cost_type_id,
224 i_final_comp_flag => l_final_comp_flag,
225 i_layer_id => l_layer_id,
226 i_movhd_cost_type_id => l_movhd_cost_type_id,
227 i_cost_group_id => l_cost_grp_id,
228 i_user_id => l_user_id,
229 i_login_id => l_login_id,
230 i_request_id => l_request_id,
231 i_prog_id => l_prog_id,
232 i_prog_appl_id => l_prog_app_id,
233 err_num => l_err_num,
234 err_code => l_err_code,
235 err_msg => l_err_msg);
236
237 --
238 -- for cfm scrap call cstpcfms.wip_cfm_complete also
239 --
240 ELSIF ((l_action_id = 31 OR (l_action_id=30 AND l_txn_qty>0)) AND l_flow_schedule = 1) THEN
241 CSTPCFMS.wip_cfm_complete(i_trx_id => l_trx_id,
242 i_org_id => l_org_id,
243 i_inv_item_id => l_inv_item_id,
244 i_txn_qty => l_txn_qty,
245 i_wip_entity_id => l_wip_entity_id,
246 i_txn_src_type_id => 5,
247 i_flow_schedule => l_flow_schedule,
248 i_txn_action_id => l_action_id,
249 i_user_id => l_user_id,
250 i_login_id => l_login_id,
251 i_request_id => l_request_id,
252 i_prog_id => l_prog_id,
253 i_prog_appl_id => l_prog_app_id,
254 err_num => l_err_num,
255 err_code => l_err_code,
256 err_msg => l_err_msg);
257
258 ELSIF (l_action_id = 32 AND l_flow_schedule <> 1) THEN
259 CSTPACWC.assembly_return(i_trx_id => l_trx_id,
260 i_txn_qty => l_txn_qty,
261 i_wip_entity_id => l_wip_entity_id,
262 i_org_id => l_org_id,
263 i_inv_item_id => l_inv_item_id,
264 i_cost_type_id => 2,
265 i_layer_id => l_layer_id,
266 i_movhd_cost_type_id => l_movhd_cost_type_id,
267 i_res_cost_type_id => l_rates_cost_type_id,
268 i_user_id => l_user_id,
269 i_login_id => l_login_id,
270 i_request_id => l_request_id,
271 i_prog_id => l_prog_id,
272 i_prog_appl_id => l_prog_app_id,
273 err_num => l_err_num,
274 err_code => l_err_code,
275 err_msg => l_err_msg);
276
277 --
278 -- for cfm scrap return call cstpcfms.wip_cfm_assy_return also
279 --
280 ELSIF ((l_action_id = 32 OR (l_action_id=30 AND l_txn_qty<0)) AND l_flow_schedule = 1) THEN
281
282 CSTPCFMS.wip_cfm_assy_return(i_trx_id => l_trx_id,
283 i_org_id => l_org_id,
284 i_inv_item_id => l_inv_item_id,
285 i_txn_qty => l_txn_qty,
286 i_wip_entity_id => l_wip_entity_id,
287 i_txn_src_type_id => 5,
288 i_flow_schedule => l_flow_schedule,
289 i_txn_action_id => l_action_id,
290 i_user_id => l_user_id,
291 i_login_id => l_login_id,
292 i_request_id => l_request_id,
293 i_prog_id => l_prog_id,
294 i_prog_appl_id => l_prog_app_id,
295 err_num => l_err_num,
296 err_code => l_err_code,
297 err_msg => l_err_msg);
298
299 /********************************************************
300 * Also, do scrap if it is not flow schedule - tchan
301 ********************************************************/
302
303 ELSIF (l_action_id=30 AND l_txn_qty>0 AND l_flow_schedule <> 1) THEN
304 CSTPACWS.scrap(i_trx_id => l_trx_id,
305 i_txn_qty => l_txn_qty,
306 i_wip_entity_id => l_wip_entity_id,
307 i_org_id => l_org_id,
308 i_inv_item_id => l_inv_item_id,
309 i_cost_group_id => l_cost_grp_id,
310 i_op_seq_num => l_op_seq_num,
311 i_cost_type_id => 2,
312 i_res_cost_type_id => l_rates_cost_type_id,
313 err_num => l_err_num,
314 err_code => l_err_code,
315 err_msg => l_err_msg);
316
317 /********************************************************
318 * Also, do scrap if it is not flow schedule - tchan
319 ********************************************************/
320
321 ELSIF (l_action_id=30 AND l_txn_qty<0 AND l_flow_schedule <> 1) THEN
322 CSTPACWS.scrap_return(i_trx_id => l_trx_id,
323 i_txn_qty => l_txn_qty,
324 i_wip_entity_id => l_wip_entity_id,
325 i_org_id => l_org_id,
326 i_inv_item_id => l_inv_item_id,
327 i_op_seq_num => l_op_seq_num,
328 i_cost_type_id => 2,
329 err_num => l_err_num,
330 err_code => l_err_code,
331 err_msg => l_err_msg);
332
333 END IF;
334
335 -- Check if processing is succesful, if not, pass control back to calling
336 -- module.
337
338 IF (l_err_num<>0) THEN
339 raise proc_fail;
340 END IF;
341
342
343 -- All the logic below should be exectued only if the transaction
344 -- is not a CITW txn. If it is then the cost proc and distbn proc
345 -- is done within CSTPACIn, the Inv library.
346
347 IF (l_comm_iss_flag <> 1) THEN
348
349
350 /*----------------------------------------------------
351 | The cost processor operates under the assumption that
352 | if there are no rows in the transaction cost table,
353 | then the transaction occured at the current average
354 | cost. For component issue/return transactions we
355 | therefore deliberately refrain from inserting a
356 | cost row. For completions, assembly returns and
357 | scrap transactions however, in the respective
358 | packages, we do not insert row if the cost is zero.
359 | To prevent such transactions from being processed at
360 | current average cost, we need to insert a dummy
361 | TL materil row into the cost table with zero cost.
362 |------------------------------------------------------*/
363
364
365
366 stmt_num := 25;
367
368 select count(*)
369 into l_mtl_txn_exists
370 from
371 mtl_cst_txn_cost_details
372 where
373 transaction_id = l_trx_id;
374
375 IF(l_mtl_txn_exists=0
376 AND (l_action_id=30 OR l_action_id=31 OR l_action_id=32)) THEN
377
378
379 stmt_num:=27;
380
381 INSERT INTO mtl_cst_txn_cost_details
382 (
383 TRANSACTION_ID,
384 ORGANIZATION_ID,
385 INVENTORY_ITEM_ID,
386 COST_ELEMENT_ID,
387 LEVEL_TYPE,
388 TRANSACTION_COST,
389 NEW_AVERAGE_COST,
390 PERCENTAGE_CHANGE,
391 VALUE_CHANGE,
392 LAST_UPDATE_DATE,
393 LAST_UPDATED_BY,
394 CREATION_DATE,
395 CREATED_BY,
396 LAST_UPDATE_LOGIN,
397 REQUEST_ID,
398 PROGRAM_APPLICATION_ID,
399 PROGRAM_ID,
400 PROGRAM_UPDATE_DATE)
401 VALUES
402 (l_trx_id,
403 l_org_id,
404 l_inv_item_id,
405 1,
406 1,
407 0,
408 NULL,
409 NULL,
410 NULL,
411 SYSDATE,
412 l_user_id,
413 SYSDATE,
414 l_user_id,
415 l_login_id,
416 l_request_id,
417 l_prog_app_id,
418 l_prog_id,
419 SYSDATE);
420
421
422 END IF;
423
424
425
426 -- For all txns call the cost processor, then the distribution
427 -- processor. Note that for scrap there is no avg cost recomputed though.
428
429
430 CSTPAVCP.cost_processor(i_org_id => l_org_id,
431 i_txn_id => l_trx_id,
432 i_layer_id => l_layer_id,
433 i_cost_type => l_cost_type_id,
434 i_cost_method => l_cost_method,
435 i_mat_ct_id => l_movhd_cost_type_id,
436 i_avg_rates_id => l_rates_cost_type_id,
437 i_item_id => l_inv_item_id,
438 i_txn_qty => l_txn_qty,
439 i_txn_action_id => l_action_id,
440 i_txn_src_type => 5,
441 i_txn_org_id => l_org_id,
442 i_txfr_org_id => NULL,
443 i_cost_grp_id => l_cost_grp_id,
444 i_txfr_cost_grp => l_txfr_cost_grp_id,
445 i_txfr_layer_id => NULL,
446 i_fob_point => NULL,
447 i_exp_item => l_exp_item_flag,
448 i_exp_flag => l_exp_flag,
449 i_citw_flag => 0,
450 i_flow_schedule => l_flow_schedule,
451 i_user_id => l_user_id,
452 i_login_id => l_login_id,
453 i_req_id => l_request_id,
454 i_prg_appl_id => l_prog_app_id,
455 i_prg_id => l_prog_id,
456 i_tprice_option => 0,
457 i_txf_price => 0,
458 o_err_num => l_err_num,
459 o_err_code => l_err_code,
460 o_err_msg => l_err_msg);
461
462
463 /****************************************************
464 *Call distribution processor - if the avg cost proc**
465 *succeeds **
466 *****************************************************/
467
468 IF (l_err_num<>0) THEN
469 raise proc_fail;
470 END IF;
471
472 CSTPACDP.cost_txn(i_org_id => l_org_id,
473 i_txn_id => l_trx_id,
474 i_layer_id => l_layer_id,
475 i_fob_point => NULL,
476 i_exp_item => l_exp_item_flag,
477 I_COMM_ISS_FLAG => L_COMM_ISS_FLAG,
478 i_flow_schedule => l_flow_schedule,
479 I_USER_ID => l_user_id,
480 i_login_id => l_login_id,
481 i_req_id => l_request_id,
482 i_prg_appl_id => l_prog_app_id,
483 i_prg_id => l_prog_id,
484 i_tprice_option => 0,
485 i_txf_price => 0,
486 o_error_num => l_err_num,
487 o_error_code => l_err_code,
488 o_error_message => l_err_msg);
489
490
491
492
493 IF (l_err_num<>0) THEN
494 raise proc_fail;
495 END IF;
496
497
498 /* Create SLA Event */
499 l_trx_info.TRANSACTION_ID := l_trx_id;
500 l_trx_info.TXN_ACTION_ID := l_action_id;
501 l_trx_info.TXN_ORGANIZATION_ID := l_org_id;
502 l_trx_info.TXN_SRC_TYPE_ID := l_src_type_id;
503 l_trx_info.TRANSACTION_DATE := l_txn_date;
504
505 CST_XLA_PVT.Create_INVXLAEvent (
506 p_api_version => 1.0,
507 p_init_msg_list => FND_API.G_FALSE,
508 p_commit => FND_API.G_FALSE,
509 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
510 x_return_status => l_return_status,
511 x_msg_count => l_msg_count,
512 x_msg_data => l_msg_data,
513 p_trx_info => l_trx_info
514 );
515 IF l_return_status <> 'S' THEN
516 l_err_num := -1;
517 l_err_code := 'Error raising SLA Event for transaction: '||to_char(l_trx_id);
518 RAISE proc_fail;
519 END IF;
520
521
522
523 END IF; -- CITW condition check IF statement ends here.
524
525 /*---------------------------------------------------------
526 | If processing was succesful in the prior step, we need
527 | to update wip_period_balances.
528 |----------------------------------------------------------*/
529
530 stmt_num := 30;
531
532 select count(*)
533 into l_row_count
534 from
535 mtl_cst_actual_cost_details
536 where
537 transaction_id = l_trx_id;
538
539
540 IF (l_row_count <> 0) THEN
541
542
543 IF (l_action_id = 1 OR l_action_id = 27 OR
544 l_action_id = 33 OR l_action_id = 34) THEN
545 if l_debug = 'Y' then
546 fnd_file.put_line(fnd_file.log,'Calling CSTPACWB.cost_in');
547 end if;
548 CSTPACWB.cost_in(i_trx_id => l_trx_id,
549 i_layer_id => l_layer_id,
550 i_comm_iss_flag => l_comm_iss_flag,
551 I_COST_TXN_ACTION_ID => l_action_id,
552 i_txn_qty => l_txn_qty,
553 i_period_id => l_period_id,
554 i_wip_entity_id => l_wip_entity_id,
555 i_org_id => l_org_id,
556 i_user_id => l_user_id,
557 i_request_id => l_request_id,
558 err_num => l_err_num,
559 err_code => l_err_code,
560 err_msg => l_err_msg);
561 if l_debug = 'Y' then
562 fnd_file.put_line(fnd_file.log,'l_err_num: '|| to_char(l_err_num));
563 end if;
564 /* Bug 3062249 - Call EAM API only if transaction is not CITW
565 This is because, distributions have not yet been created for
566 CITW txns. For these, EAM API will be called in CSTPACIN */
567
568 if(l_comm_iss_flag <> 1) then
569 if (l_err_num = 0) then
570 /* Check if EAM job */
571 select entity_type
572 into l_entity_type
573 from wip_entities
574 where wip_entity_id = l_wip_entity_id;
575
576 /* Update Asset cost for eam jobs */
577 if (l_entity_type in (6,7)) then
578 if l_debug = 'Y' then
579 FND_FILE.PUT_LINE(FND_FILE.log, 'calling eamCost_pub.process_matCost ');
580 end if;
581 CST_eamCost_PUB.process_matCost (
582 p_api_version => 1.0,
583 x_return_status => l_return_status,
584 x_msg_count => l_msg_count,
585 x_msg_data => l_msg_data,
586 p_txn_id => l_trx_id,
587 p_user_id => l_user_id,
588 p_request_id => l_request_id,
589 p_prog_id => l_prog_id,
590 p_prog_app_id => l_prog_app_id,
591 p_login_id => l_login_id
592 );
593 if (l_return_status <> fnd_api.g_ret_sts_success) then
594 CST_UTILITY_PUB.writelogmessages
595 ( p_api_version => 1.0,
596 p_msg_count => l_msg_count,
597 p_msg_data => l_msg_data,
598 x_return_status => l_msg_return_status );
599 l_err_num := l_msg_count;
600 else
601 l_err_num := 0;
602 end if;
603 end if;
604 end if;
605 end if; --CITW check for EAM API
606
607 ELSIF((l_action_id = 31 OR l_action_id = 32 OR l_action_id = 30) AND l_exp_item_flag <> 1)
608 THEN
609 CSTPACWB.cost_out(i_trx_id => l_trx_id,
610 i_txn_qty => l_txn_qty,
611 i_period_id => l_period_id,
612 i_wip_entity_id => l_wip_entity_id,
613 i_org_id => l_org_id,
614 i_user_id => l_user_id,
615 i_request_id => l_request_id,
616 err_num => l_err_num,
617 err_code => l_err_code,
618 err_msg => l_err_msg);
619
620 END IF;
621
622
623
624 -- Raise exception if there is an error in updating WPB.
625
626 IF (l_err_num<>0) THEN
627 if (l_debug = 'Y') then
628 fnd_file.put_line(fnd_file.log, 'raising proc_fail after call to cost_in/cost_out');
629 end if;
630 raise proc_fail;
631 END IF;
632
633 END IF;
634
635
636
637
638 EXCEPTION
639
640 WHEN proc_fail THEN
641 FND_FILE.PUT_LINE(FND_FILE.log, 'in exception loop proc_fail raised');
642 err_num := l_err_num;
643 err_code := l_err_code;
644 err_msg := l_err_msg;
645 ROLLBACK;
646
647 WHEN OTHERS THEN
648 ROLLBACK;
649 err_num := SQLCODE;
650 err_msg := 'CSTPACWP:' || to_char(stmt_num) || substr(SQLERRM,1,150);
651
652 END cost_wip_trx;
653
654 END CSTPACWP;