[Home] [Help]
PACKAGE BODY: APPS.CSTPLCIR
Source
1 PACKAGE BODY CSTPLCIR AS
2 /* $Header: CSTLCIRB.pls 120.1.12020000.2 2012/07/11 13:04:53 vkatakam ship $ */
3
4
5
6 PROCEDURE component_issue (
7 i_cost_method_id IN NUMBER,
8 i_txn_id IN NUMBER,
9 i_layer_id IN NUMBER,
10 i_inv_item_id IN NUMBER,
11 i_org_id IN NUMBER,
12 i_wip_entity_id IN NUMBER,
13 i_txn_qty IN NUMBER,
14 i_op_seq_num IN NUMBER,
15 i_cost_type_id IN NUMBER,
16 i_exp_flag IN NUMBER,
17 i_user_id IN NUMBER,
18 i_login_id IN NUMBER,
19 i_request_id IN NUMBER,
20 i_prog_id IN NUMBER,
21 i_prog_appl_id IN NUMBER,
22 o_err_num OUT NOCOPY NUMBER,
23 o_err_msg OUT NOCOPY VARCHAR2
24 )
25 IS
26
27 l_stmt_num NUMBER := 0;
28
29 CURSOR layer_qty_cursor IS
30 select
31 MCLACD.inv_layer_id inv_layer_id,
32 min( MCLACD.layer_quantity ) layer_quantity
33 from
34 mtl_cst_layer_act_cost_details MCLACD
35 where
36 MCLACD.transaction_id = i_txn_id
37 group by
38 MCLACD.inv_layer_id;
39
40
41 l_layer_qty_table CSTPLMWI.LayerQtyRecTable;
42
43 l_wip_layer_id NUMBER;
44
45 l_err_code VARCHAR2(2000);
46 l_cost_hook_used NUMBER(15);
47 l_total_txn_qty NUMBER;
48
49 bad_inv_consume_exception EXCEPTION;
50
51 l_item_exp_flag NUMBER;
52 l_exp_flag NUMBER;
53
54 BEGIN
55
56 /* i_txn_qty < 0 for regular component issues */
57
58 l_stmt_num := 5;
59
60 select decode(inventory_asset_flag,'Y', 0, 1)
61 into l_item_exp_flag
62 from mtl_system_items
63 where inventory_item_id = i_inv_item_id
64 and organization_id = i_org_id;
65
66 l_stmt_num := 8;
67
68 select decode( l_item_exp_flag, 1, 1,
69 decode(asset_inventory, 1, 0, 1) )
70 into l_exp_flag
71 from mtl_secondary_inventories msi,
72 mtl_material_transactions mmt
73 where msi.secondary_inventory_name = mmt.subinventory_code
74 and msi.organization_id = i_org_id
75 and mmt.transaction_id = i_txn_id
76 and mmt.organization_id = i_org_id;
77
78
79 l_stmt_num := 10;
80
81 -- This is a weird place to call the Actual Cost Hook.
82 -- But, INV's consume_create_layers() functions expects
83 -- it to be called first, so need to call it here.
84
85 l_cost_hook_used := CSTPACHK.actual_cost_hook
86 (
87 I_ORG_ID => i_org_id,
88 I_TXN_ID => i_txn_id,
89 I_LAYER_ID => i_layer_id,
90 I_COST_TYPE => i_cost_type_id,
91 I_COST_METHOD => i_cost_method_id,
92 I_USER_ID => i_user_id,
93 I_LOGIN_ID => i_login_id,
94 I_REQ_ID => i_request_id,
95 I_PRG_APPL_ID => i_prog_appl_id,
96 I_PRG_ID => i_prog_id,
97 O_Err_Num => o_err_num,
98 O_Err_Code => l_err_code,
99 O_Err_Msg => o_err_msg
100 );
101
102
103 IF o_err_num <> 0 THEN
104 RETURN;
105 END IF;
106
107
108 l_stmt_num := 20;
109
110 -- Consume INV layers
111 CSTPLENG.consume_create_layers
112 (
113 i_org_id => i_org_id,
114 i_txn_id => i_txn_id,
115 i_layer_id => i_layer_id,
116 i_cost_hook => l_cost_hook_used,
117 i_item_id => i_inv_item_id,
118 i_txn_qty => i_txn_qty,
119 i_cost_method => i_cost_method_id,
120 i_txn_src_type => 5,
121 i_txn_action_id => 1,
122 i_interorg_rec => null,
123 i_cost_type => i_cost_type_id,
124 i_mat_ct_id => null,
125 i_avg_rates_id => null,
126 i_exp_flag => i_exp_flag,
127 i_user_id => i_user_id,
128 i_login_id => i_login_id,
129 i_req_id => i_request_id,
130 i_prg_appl_id => i_prog_appl_id,
131 i_prg_id => i_prog_id,
132 o_err_num => o_err_num,
133 o_err_code => l_err_code,
134 o_err_msg => o_err_msg
135 );
136
137 IF o_err_num <> 0 THEN
138 RETURN;
139 END if;
140
141
142 IF(l_exp_flag = 1) THEN
143 /* Expense - Expense Item or Issue from Expense Subinventory */
144
145 l_stmt_num := 25;
146 l_layer_qty_table := CSTPLMWI.LayerQtyRecTable();
147 l_layer_qty_table.EXTEND;
148 l_layer_qty_table( l_layer_qty_table.FIRST ).layer_id := -1;
149 l_layer_qty_table( l_layer_qty_table.FIRST ).layer_qty := -1 * i_txn_qty;
150
151 ELSE
152 /* Asset Item */
153 -- populate the layer_qty_table from MCLACD
154
155 l_stmt_num := 30;
156 l_total_txn_qty := 0;
157 l_layer_qty_table := CSTPLMWI.LayerQtyRecTable();
158 FOR l_layer_qty_rec in layer_qty_cursor LOOP
159 l_layer_qty_table.EXTEND;
160
161 l_layer_qty_table( l_layer_qty_table.LAST ).layer_id :=
162 l_layer_qty_rec.inv_layer_id;
163
164 -- this needs sign reversal because INV is now storing
165 -- consumed quantities as negative
166 l_layer_qty_table( l_layer_qty_table.LAST ).layer_qty :=
167 -l_layer_qty_rec.layer_quantity;
168
169 l_total_txn_qty := l_total_txn_qty + l_layer_qty_rec.layer_quantity;
170 END LOOP;
171
172 IF l_total_txn_qty <> i_txn_qty THEN
173 RAISE bad_inv_consume_exception;
174 END IF;
175
176 end if; /* l_exp_flag */
177
178
179 l_stmt_num := 40;
180
181 l_wip_layer_id := CSTPLMWI.wip_layer_create
182 (
183 i_wip_entity_id,
184 i_op_seq_num,
185 i_inv_item_id,
186 i_txn_id,
187 l_layer_qty_table,
188 i_user_id,
189 i_login_id,
190 i_request_id,
191 i_prog_id,
192 i_prog_appl_id,
193 o_err_num,
194 o_err_msg
195 );
196
197 IF o_err_num <> 0 THEN
198 RETURN;
199 END if;
200
201 if (l_exp_flag <> 1) then
202
203 -- insert into WROCD if not already there
204 l_stmt_num := 50;
205
206 CSTPLMWI.init_wip_layers
207 (
208 i_wip_entity_id,
209 i_op_seq_num,
210 i_inv_item_id,
211 i_org_id,
212 i_txn_id,
213 i_layer_id,
214 i_user_id,
215 i_login_id,
216 i_request_id,
217 i_prog_id,
218 i_prog_appl_id,
219 o_err_num,
220 o_err_msg
221 );
222 IF o_err_num <> 0 THEN
223 RETURN;
224 END IF;
225
226
227 -- update WROCD.applied_matl_value
228 l_stmt_num := 60;
229
230 update wip_req_operation_cost_details WROCD
231 set applied_matl_value
232 =
233 (
234 select
235 nvl(WROCD.applied_matl_value, 0) + -- add nvl for bug13523172
236 sum( CWL.applied_matl_qty * CWLCD.layer_cost )
237 from
238 cst_wip_layers CWL,
239 cst_wip_layer_cost_details CWLCD
240 where
241 CWL.wip_layer_id = l_wip_layer_id and
242 CWLCD.wip_layer_id = CWL.wip_layer_id and
243 CWLCD.inv_layer_id = CWL.inv_layer_id and
244 CWLCD.cost_element_id = WROCD.cost_element_id and
245 CWLCD.level_type in (1, 2)
246 )
247 where
248 WROCD.wip_entity_id = i_wip_entity_id and
249 WROCD.operation_seq_num = i_op_seq_num and
250 WROCD.inventory_item_id = i_inv_item_id;
251
252 end if;
253
254
255 EXCEPTION
256 WHEN bad_inv_consume_exception THEN
257 o_err_num := 1007;
258 o_err_msg := 'CSTPLCIR.component_issue():' ||
259 to_char(l_stmt_num) || ':' ||
260 'Inventory total txn qty was ' || l_total_txn_qty || '; ' ||
261 ' expected ' || i_txn_qty;
262
263 WHEN OTHERS THEN
264 o_err_num := SQLCODE;
265 o_err_msg := 'CSTPLCIR.component_issue():' ||
266 to_char(l_stmt_num) || ':' ||
267 substr(SQLERRM,1,150);
268
269 END component_issue;
270
271
272
273
274
275
276 PROCEDURE component_return (
277 i_cost_method_id IN NUMBER,
278 i_txn_id IN NUMBER,
279 i_layer_id IN NUMBER,
280 i_inv_item_id IN NUMBER,
281 i_org_id IN NUMBER,
282 i_wip_entity_id IN NUMBER,
283 i_txn_qty IN NUMBER,
284 i_op_seq_num IN NUMBER,
285 i_user_id IN NUMBER,
286 i_login_id IN NUMBER,
287 i_request_id IN NUMBER,
288 i_prog_id IN NUMBER,
289 i_prog_appl_id IN NUMBER,
290 o_err_num OUT NOCOPY NUMBER,
291 o_err_msg OUT NOCOPY VARCHAR2
292 )
293 IS
294
295 l_stmt_num NUMBER := 0;
296
297 l_sql_stmt VARCHAR2(8000);
298 l_layer_cursor CSTPLMWI.REF_CURSOR_TYPE;
299 l_layer cst_wip_layers%ROWTYPE;
300
301 l_txn_qty_remaining NUMBER;
302 l_consumed_qty NUMBER;
303
304 /* EAM Acct Enh Project */
305 l_debug VARCHAR2(80);
306 l_zero_cost_flag NUMBER := -1;
307 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
308 l_msg_count NUMBER := 0;
309 l_msg_data VARCHAR2(8000) := '';
310 l_api_message VARCHAR2(8000);
311
312 BEGIN
313
314 l_stmt_num := 20;
315 CSTPLMWI.init_wip_layers
316 (
317 i_wip_entity_id,
318 i_op_seq_num,
319 i_inv_item_id,
320 i_org_id,
321 i_txn_id,
322 i_layer_id,
323 i_user_id,
324 i_login_id,
325 i_request_id,
326 i_prog_id,
327 i_prog_appl_id,
328 o_err_num,
329 o_err_msg
330 );
331 IF o_err_num <> 0 THEN
332 RETURN;
333 END IF;
334
335
336
337 -- component returns consume WIP layer(s) in reverse
338 l_stmt_num := 30;
339 l_sql_stmt := CSTPLMWI.wip_layer_consume_sql
340 (
341 ' ( CWL.applied_matl_qty - ' ||
342 ' CWL.relieved_matl_comp_qty - ' ||
343 ' CWL.relieved_matl_scrap_qty - ' ||
344 ' CWL.relieved_matl_final_comp_qty ) > 0 ',
345 i_cost_method_id,
346 CSTPLMWI.REVERSE
347 );
348
349 l_stmt_num := 40;
350 open l_layer_cursor for l_sql_stmt
351 using i_wip_entity_id, i_op_seq_num, i_inv_item_id;
352
353 l_txn_qty_remaining := nvl( i_txn_qty, 0 );
354
355 l_stmt_num := 50;
356 LOOP
357 exit when l_txn_qty_remaining = 0;
358
359 l_stmt_num := 60;
360 fetch l_layer_cursor into l_layer;
361
362 l_stmt_num := 70;
363 IF l_layer_cursor%NOTFOUND THEN
364
365 l_layer := CSTPLMWI.get_last_layer
366 (
367 i_wip_entity_id,
368 i_op_seq_num,
369 i_inv_item_id,
370 o_err_num,
371 o_err_msg
372 );
373 IF o_err_num <> 0 THEN
374 RETURN;
375 END IF;
376
377 l_consumed_qty := l_txn_qty_remaining;
378
379 ELSE
380 l_consumed_qty := least( ( l_layer.applied_matl_qty -
381 l_layer.relieved_matl_comp_qty -
382 l_layer.relieved_matl_scrap_qty -
383 l_layer.relieved_matl_final_comp_qty ),
384 l_txn_qty_remaining );
385 END IF;
386
387
388 l_stmt_num := 80;
389 update cst_wip_layers CWL
390 set
391 applied_matl_qty = applied_matl_qty - l_consumed_qty,
392 temp_relieved_qty = temp_relieved_qty + l_consumed_qty
393 where
394 wip_layer_id = l_layer.wip_layer_id and
395 inv_layer_id = l_layer.inv_layer_id;
396
397 l_txn_qty_remaining := l_txn_qty_remaining - l_consumed_qty;
398
399 END LOOP;
400
401 l_stmt_num := 90;
402 close l_layer_cursor;
403
404 l_stmt_num := 95;
405 /* EAM Acct Enh Project */
406 CST_Utility_PUB.get_zeroCostIssue_flag (
407 p_api_version => 1.0,
408 x_return_status => l_return_status,
409 x_msg_count => l_msg_count,
410 x_msg_data => l_msg_data,
411 p_txn_id => i_txn_id,
412 x_zero_cost_flag => l_zero_cost_flag
413 );
414
415 if (l_return_status <> fnd_api.g_ret_sts_success) then
416 FND_FILE.put_line(FND_FILE.log, l_msg_data);
417 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
418 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
419 FND_MESSAGE.set_token('TEXT', l_api_message);
420 FND_MSG_pub.add;
421 raise fnd_api.g_exc_unexpected_error;
422 end if;
423
424 if (l_debug = 'Y') then
425 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
426 end if;
427
428 -- update WROCD
429 l_stmt_num := 100;
430 update wip_req_operation_cost_details WROCD
431 set
432 (
433 WROCD.applied_matl_value,
434 WROCD.temp_relieved_value
435 )
436 =
437 (
438 select
439 NVL( WROCD.applied_matl_value, 0 ) -
440 sum( CWL.temp_relieved_qty *
441 decode( l_zero_cost_flag, 1, 0, CWLCD.layer_cost ) ),
442 sum( CWL.temp_relieved_qty *
443 decode( l_zero_cost_flag, 1, 0, CWLCD.layer_cost ) )
444 from
445 cst_wip_layers CWL,
446 cst_wip_layer_cost_details CWLCD
447 where
448 CWL.wip_entity_id = WROCD.wip_entity_id and
449 CWL.operation_seq_num = WROCD.operation_seq_num and
450 CWL.inventory_item_id = WROCD.inventory_item_id and
451 CWL.temp_relieved_qty <> 0 and
452 CWLCD.wip_layer_id = CWL.wip_layer_id and
453 CWLCD.inv_layer_id = CWL.inv_layer_id and
454 CWLCD.cost_element_id = WROCD.cost_element_id and
455 CWLCD.level_type in (1, 2)
456 )
457 where
458 WROCD.wip_entity_id = i_wip_entity_id and
459 WROCD.operation_seq_num = i_op_seq_num and
460 WROCD.inventory_item_id = i_inv_item_id;
461
462
463
464
465 l_stmt_num := 110;
466 INSERT INTO mtl_cst_txn_cost_details
467 (
468 TRANSACTION_ID,
469 ORGANIZATION_ID,
470 INVENTORY_ITEM_ID,
471 COST_ELEMENT_ID,
472 LEVEL_TYPE,
473 TRANSACTION_COST,
474 LAST_UPDATE_DATE,
475 LAST_UPDATED_BY,
476 CREATION_DATE,
477 CREATED_BY,
478 LAST_UPDATE_LOGIN,
479 REQUEST_ID,
480 PROGRAM_APPLICATION_ID,
481 PROGRAM_ID,
482 PROGRAM_UPDATE_DATE
483 )
484 SELECT
485 i_txn_id, -- TRANSACTION_ID,
486 i_org_id, -- ORGANIZATION_ID,
487 i_inv_item_id, -- INVENTORY_ITEM_ID,
488 CWLCD.cost_element_id, -- COST_ELEMENT_ID,
489 CWLCD.level_type, -- LEVEL_TYPE,
490 sum( decode( l_zero_cost_flag, 1, 0, CWLCD.layer_cost ) *
491 CWL.temp_relieved_qty ) / i_txn_qty,
492 -- TRANSACTION_COST,
493 sysdate, -- LAST_UPDATE_DATE,
494 i_user_id, -- LAST_UPDATED_BY,
495 sysdate, -- CREATION_DATE,
496 i_user_id, -- CREATED_BY,
497 i_login_id, -- LAST_UPDATE_LOGIN,
498 i_request_id, -- REQUEST_ID,
499 i_prog_appl_id, -- PROGRAM_APPLICATION_ID,
500 i_prog_id, -- PROGRAM_ID,
501 sysdate -- PROGRAM_UPDATE_DATE
502 from
503 cst_wip_layers CWL,
504 cst_wip_layer_cost_details CWLCD
505 where
506 CWL.wip_entity_id = i_wip_entity_id and
507 CWL.operation_seq_num = i_op_seq_num and
508 CWL.inventory_item_id = i_inv_item_id and
509 CWL.temp_relieved_qty <> 0 and
510 CWLCD.wip_layer_id = CWL.wip_layer_id and
514 CWLCD.cost_element_id,
511 CWLCD.inv_layer_id = CWL.inv_layer_id and
512 CWLCD.level_type in (1,2)
513 group by
515 CWLCD.level_type;
516
517
518
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 o_err_num := SQLCODE;
523 o_err_msg := 'CSTPLCIR.component_return():' ||
524 to_char(l_stmt_num) || ':' ||
525 substr(SQLERRM,1,150);
526
527 END component_return;
528
529
530
531
532
533 END CSTPLCIR;