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