[Home] [Help]
PACKAGE BODY: APPS.CSTPLMWI
Source
1 PACKAGE BODY CSTPLMWI AS
2 /* $Header: CSTLMWIB.pls 120.1 2011/07/26 06:55:02 lchevala ship $ */
3
4
5
6
7 ----------------------------------------------------------------
8 -- wip_layer_create
9 -- This function takes a LayerQtyRecTable containing the INV
10 -- layer IDs and quantities, and creates corresponding WIP
11 -- layers using the given INV layer costs.
12 ----------------------------------------------------------------
13 FUNCTION wip_layer_create (
14 i_wip_entity_id IN NUMBER,
15 i_op_seq_num IN NUMBER,
16 i_inv_item_id IN NUMBER,
17 i_txn_id IN NUMBER,
18 i_layer_qty_table IN LayerQtyRecTable,
19 i_user_id IN NUMBER,
20 i_login_id IN NUMBER,
21 i_request_id IN NUMBER,
22 i_prog_id IN NUMBER,
23 i_prog_appl_id IN NUMBER,
24 o_err_num OUT NOCOPY NUMBER,
25 o_err_msg OUT NOCOPY VARCHAR2
26 ) RETURN NUMBER
27 IS
28
29 l_stmt_num NUMBER(15) := 0;
30
31 l_wip_layer_id NUMBER(15) := 0;
32 i NUMBER(15) := 0;
33
34 invalid_qty_table_exception EXCEPTION;
35 invalid_inv_layer_exception EXCEPTION;
36 no_inv_cost_details_exception EXCEPTION;
37
38 l_org_id NUMBER;
39 l_subinv VARCHAR2(50);
40 l_exp_sub_flag NUMBER;
41 l_exp_item_flag NUMBER;
42 /* EAM Acct Enh Project */
43 l_debug VARCHAR2(80);
44 l_zero_cost_flag NUMBER := -1;
45 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
46 l_msg_count NUMBER := 0;
47 l_msg_data VARCHAR2(8000) := '';
48 l_api_message VARCHAR2(8000);
49
50 BEGIN
51
52 l_stmt_num := 10;
53 IF i_layer_qty_table IS NULL THEN
54 RAISE invalid_qty_table_exception;
55 END IF;
56
57 l_stmt_num := 15;
58 select organization_id,
59 subinventory_code
60 into l_org_id,
61 l_subinv
62 from mtl_material_transactions
63 where transaction_id = i_txn_id;
64
65 select decode(inventory_asset_flag,'Y', 0, 1)
66 into l_exp_item_flag
67 from mtl_system_items
68 where inventory_item_id = i_inv_item_id
69 and organization_id = l_org_id;
70
71 begin
72 select decode(asset_inventory, 1, 0, 1)
73 into l_exp_sub_flag
74 from mtl_secondary_inventories
75 where secondary_inventory_name = l_subinv
76 and organization_id = l_org_id;
77 exception
78 when no_data_found then
79 l_exp_sub_flag := -1;
80 end;
81
82 l_stmt_num := 20;
83 select cst_wip_layers_s.nextval
84 into l_wip_layer_id
85 from dual;
86
87 l_stmt_num := 25;
88 /* EAM Acct Enh Project */
89 CST_Utility_PUB.get_zeroCostIssue_flag (
90 p_api_version => 1.0,
91 x_return_status => l_return_status,
92 x_msg_count => l_msg_count,
93 x_msg_data => l_msg_data,
94 p_txn_id => i_txn_id,
95 x_zero_cost_flag => l_zero_cost_flag
96 );
97
98 if (l_return_status <> fnd_api.g_ret_sts_success) then
99 FND_FILE.put_line(FND_FILE.log, l_msg_data);
100 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
101 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
102 FND_MESSAGE.set_token('TEXT', l_api_message);
103 FND_MSG_pub.add;
104 raise fnd_api.g_exc_unexpected_error;
105 end if;
106
107 if (l_debug = 'Y') then
108 FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
109 end if;
110
111 l_stmt_num := 30;
112 i := i_layer_qty_table.FIRST;
113
114 if (l_exp_item_flag <> 1) and (l_exp_sub_flag <> 1) then
115
116 WHILE i IS NOT NULL LOOP
117
118 IF ( i_layer_qty_table.EXISTS(i) AND
119 i_layer_qty_table(i).layer_id IS NOT NULL ) THEN
120
121 l_stmt_num := 40;
122 insert into cst_wip_layers
123 (
124 wip_layer_id,
125 wip_entity_id,
126 operation_seq_num,
127 inventory_item_id,
128 repetitive_schedule_id,
129 inv_layer_id,
130 inv_layer_date,
131 create_txn_id,
132 applied_matl_qty,
133 relieved_matl_comp_qty,
134 relieved_matl_scrap_qty,
135 relieved_matl_final_comp_qty,
136 temp_relieved_qty,
137 LAST_UPDATE_DATE,
138 LAST_UPDATED_BY,
139 CREATION_DATE,
140 CREATED_BY,
141 LAST_UPDATE_LOGIN,
142 REQUEST_ID,
143 PROGRAM_APPLICATION_ID,
144 PROGRAM_ID,
145 PROGRAM_UPDATE_DATE
146 )
147 select
148 l_wip_layer_id, -- wip_layer_id
149 i_wip_entity_id, -- wip_entity_id
150 i_op_seq_num, -- operation_seq_num
151 CIL.inventory_item_id, -- inventory_item_id
152 null, -- repetitive_schedule_id
153 decode(l_exp_sub_flag, 1, -1, CIL.inv_layer_id),
154 -- inv_layer_id
155 CIL.creation_date, -- inv_layer_date
156 i_txn_id, -- create_txn_id
157 NVL( i_layer_qty_table(i).layer_qty, 0 ),
158 -- applied_matl_qty
159 0, -- relieved_matl_comp_qty
160 0, -- relieved_matl_scrap_qty
161 0, -- relieved_matl_final_comp_qty
162 0, -- temp_relieved_qty
163 sysdate, -- LAST_UPDATE_DATE
164 i_user_id, -- LAST_UPDATED_BY
165 sysdate, -- CREATION_DATE
166 i_user_id, -- CREATED_BY
167 i_login_id, -- LAST_UPDATE_LOGIN
168 i_request_id, -- REQUEST_ID
169 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
170 i_prog_id, -- PROGRAM_ID
171 sysdate -- PROGRAM_UPDATE_DATE
172 from
173 cst_inv_layers CIL
174 where
175 CIL.inv_layer_id = i_layer_qty_table(i).layer_id and
176 CIL.inventory_item_id = i_inv_item_id;
177
178 IF SQL%ROWCOUNT = 0 THEN
179 RAISE invalid_inv_layer_exception;
180 END IF;
181
182
183 l_stmt_num := 50;
184 insert into cst_wip_layer_cost_details
185 (
186 wip_layer_id,
187 inv_layer_id,
188 cost_element_id,
189 level_type,
190 layer_cost,
191 LAST_UPDATE_DATE,
192 LAST_UPDATED_BY,
193 CREATION_DATE,
194 CREATED_BY,
195 LAST_UPDATE_LOGIN,
196 REQUEST_ID,
197 PROGRAM_APPLICATION_ID,
198 PROGRAM_ID,
199 PROGRAM_UPDATE_DATE
200 )
201 select
202 l_wip_layer_id, -- wip_layer_id
203 decode(l_exp_sub_flag, 1, -1, CILCD.inv_layer_id),
204 -- inv_layer_id
205 CILCD.cost_element_id, -- cost_element_id
206 CILCD.level_type, -- level_type
207 sum(decode(l_zero_cost_flag, 1, 0, CILCD.layer_cost)),
208 -- layer_cost
209 sysdate, -- LAST_UPDATE_DATE
210 i_user_id, -- LAST_UPDATED_BY
211 sysdate, -- CREATION_DATE
212 i_user_id, -- CREATED_BY
213 i_login_id, -- LAST_UPDATE_LOGIN
214 i_request_id, -- REQUEST_ID
215 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
216 i_prog_id, -- PROGRAM_ID
217 sysdate -- PROGRAM_UPDATE_DATE
218 from
219 cst_inv_layer_cost_details CILCD
220 where
221 CILCD.inv_layer_id = i_layer_qty_table(i).layer_id
222 group by
223 CILCD.inv_layer_id,
224 CILCD.cost_element_id,
225 CILCD.level_type;
226
227
228 IF SQL%ROWCOUNT = 0 THEN
229 RAISE no_inv_cost_details_exception;
230 END IF;
231
232 END IF;
233
234 i := i_layer_qty_table.NEXT( i );
235
236 END LOOP;
237
238 else
239 /* Expense items/Subinventories */
240 IF ( i_layer_qty_table.EXISTS(i) AND
241 i_layer_qty_table(i).layer_id IS NOT NULL ) THEN
242
243 l_stmt_num := 60;
244 insert into cst_wip_layers
245 (
246 wip_layer_id,
247 wip_entity_id,
248 operation_seq_num,
249 inventory_item_id,
250 repetitive_schedule_id,
251 inv_layer_id,
252 inv_layer_date,
253 create_txn_id,
254 applied_matl_qty,
255 relieved_matl_comp_qty,
256 relieved_matl_scrap_qty,
257 relieved_matl_final_comp_qty,
258 temp_relieved_qty,
259 LAST_UPDATE_DATE,
260 LAST_UPDATED_BY,
261 CREATION_DATE,
262 CREATED_BY,
263 LAST_UPDATE_LOGIN,
264 REQUEST_ID,
265 PROGRAM_APPLICATION_ID,
266 PROGRAM_ID,
267 PROGRAM_UPDATE_DATE
268 )
269 select
270 l_wip_layer_id, -- wip_layer_id
271 i_wip_entity_id, -- wip_entity_id
272 i_op_seq_num, -- operation_seq_num
273 i_inv_item_id, -- inventory_item_id
274 null, -- repetitive_schedule_id
275 -1, -- inv_layer_id
276 sysdate, -- inv_layer_date
277 i_txn_id, -- create_txn_id
278 NVL( i_layer_qty_table(i).layer_qty, 0 ),
279 -- applied_matl_qty
280 0, -- relieved_matl_comp_qty
281 0, -- relieved_matl_scrap_qty
282 0, -- relieved_matl_final_comp_qty
283 0, -- temp_relieved_qty
284 sysdate, -- LAST_UPDATE_DATE
285 i_user_id, -- LAST_UPDATED_BY
286 sysdate, -- CREATION_DATE
287 i_user_id, -- CREATED_BY
288 i_login_id, -- LAST_UPDATE_LOGIN
289 i_request_id, -- REQUEST_ID
290 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
291 i_prog_id, -- PROGRAM_ID
292 sysdate -- PROGRAM_UPDATE_DATE
293 from
294 dual;
295
296 l_stmt_num := 70;
297 insert into cst_wip_layer_cost_details
298 (
299 wip_layer_id,
300 inv_layer_id,
301 cost_element_id,
302 level_type,
303 layer_cost,
304 LAST_UPDATE_DATE,
305 LAST_UPDATED_BY,
306 CREATION_DATE,
307 CREATED_BY,
308 LAST_UPDATE_LOGIN,
309 REQUEST_ID,
310 PROGRAM_APPLICATION_ID,
311 PROGRAM_ID,
312 PROGRAM_UPDATE_DATE
313 )
314 select
315 l_wip_layer_id, -- wip_layer_id
316 -1, -- inv_layer_id
317 1, -- cost_element_id
318 1, -- level_type
319 0, -- layer_cost
320 sysdate, -- LAST_UPDATE_DATE
321 i_user_id, -- LAST_UPDATED_BY
322 sysdate, -- CREATION_DATE
323 i_user_id, -- CREATED_BY
324 i_login_id, -- LAST_UPDATE_LOGIN
325 i_request_id, -- REQUEST_ID
326 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
327 i_prog_id, -- PROGRAM_ID
328 sysdate -- PROGRAM_UPDATE_DATE
329 from
330 dual;
331 end if;
332 end if;
333
334
335
336 RETURN l_wip_layer_id;
337
338
339
340
341
342 EXCEPTION
343 WHEN invalid_qty_table_exception THEN
344 o_err_num := 1001;
345 o_err_msg := 'CSTPLMWI.wip_layer_create():' ||
346 to_char(l_stmt_num) || ':' ||
347 'i_layer_qty_table IS NULL';
348 RETURN 0;
349
350 WHEN invalid_inv_layer_exception THEN
351 o_err_num := 1002;
352 o_err_msg := 'CSTPLMWI.wip_layer_create():' ||
353 to_char(l_stmt_num) || ':' ||
354 'Inventory returned invalid layer ' ||
355 to_char( i_layer_qty_table(i).layer_id );
356 RETURN 0;
357
358 WHEN no_inv_cost_details_exception THEN
359 o_err_num := 1003;
360 o_err_msg := 'CSTPLMWI.wip_layer_create():' ||
361 to_char(l_stmt_num) || ':' ||
362 'Inventory missing layer cost details ' ||
363 i_layer_qty_table(i).layer_id;
364 RETURN 0;
365
366 WHEN OTHERS THEN
367 o_err_num := SQLCODE;
368 o_err_msg := 'CSTPLMWI.wip_layer_create():' ||
369 to_char(l_stmt_num) || ':' ||
370 to_char( i_layer_qty_table(i).layer_id ) || ':' ||
371 substr(SQLERRM,1,150);
372 RETURN 0;
373
374 END wip_layer_create;
375
376
377
378
379 ----------------------------------------------------------------
380 -- wip_layer_consume_sql
381 -- This function returns the dynamic SQL statement for
382 -- consuming the WIP layers using the provided WHERE clause,
383 -- as well as the order mode (FIFO or LIFO).
384 ----------------------------------------------------------------
385 FUNCTION wip_layer_consume_sql (
386 i_where_clause IN VARCHAR2,
387 i_cost_method_id IN NUMBER,
388 i_direction_mode IN NUMBER
389 ) RETURN VARCHAR2
390 IS
391 l_optional_and varchar2(10);
392 l_sql_order_by varchar2(400);
393 BEGIN
394
395 IF i_where_clause IS NULL THEN
396 l_optional_and := to_char( null );
397 ELSE
398 l_optional_and := ' and ';
399 END IF;
400
401 IF ( i_cost_method_id = FIFO and i_direction_mode = NORMAL ) OR
402 ( i_cost_method_id = LIFO and i_direction_mode = REVERSE ) THEN
403 l_sql_order_by :=
404 ' CWL.wip_entity_id asc, ' ||
405 ' CWL.operation_seq_num asc, ' ||
406 ' CWL.inventory_item_id asc, ' ||
407 /*
408 Removed since EAM Acct Enh project has layers with inv_layer_id = -1
409 ' CWL.inv_layer_date asc, ' ||
410 ' CWL.inv_layer_id asc, ' ||
411 */
412 ' CWL.creation_date asc, ' ||
413 ' CWL.wip_layer_id asc ';
414
415 ELSIF ( i_cost_method_id = LIFO and i_direction_mode = NORMAL ) OR
416 ( i_cost_method_id = FIFO and i_direction_mode = REVERSE ) THEN
417 l_sql_order_by :=
418 ' CWL.wip_entity_id desc, ' ||
419 ' CWL.operation_seq_num desc, ' ||
420 ' CWL.inventory_item_id desc, ' ||
421 /*
422 Removed since EAM Acct Enh project has layers with inv_layer_id = -1
423 ' CWL.inv_layer_date desc, ' ||
424 ' CWL.inv_layer_id desc, ' ||
425 */
426 ' CWL.creation_date desc, ' ||
427 ' CWL.wip_layer_id desc ';
428
429 ELSE
430 RETURN to_char( NULL );
431
432 END IF;
433
434
435 RETURN
436 ' select * ' ||
437 ' from cst_wip_layers CWL ' ||
438 ' where ' ||
439 ' CWL.wip_entity_id = :wip_entity_id and ' ||
440 ' CWL.operation_seq_num = :op_seq_num and ' ||
441 ' CWL.inventory_item_id = :inv_item_id ' || l_optional_and ||
442 i_where_clause ||
443 ' order by ' ||
444 l_sql_order_by;
445
446 END wip_layer_consume_sql;
447
448
449
450
451 -----------------------------------------------------------------
452 -- get_last_layer
453 -- This function returns the last (most recent) WIP layer for
454 -- a particular WIP entity/op/item combination.
455 -----------------------------------------------------------------
456 FUNCTION get_last_layer (
457 i_wip_entity_id IN NUMBER,
458 i_op_seq_num IN NUMBER,
459 i_inv_item_id IN NUMBER,
460 o_err_num OUT NOCOPY NUMBER,
461 o_err_msg OUT NOCOPY VARCHAR2
462 ) RETURN cst_wip_layers%ROWTYPE
463 IS
464 l_layer_cursor CSTPLMWI.REF_CURSOR_TYPE;
465 l_layer cst_wip_layers%ROWTYPE;
466 l_sql_stmt VARCHAR2(8000);
467 l_stmt_num NUMBER(15);
468 BEGIN
469
470 l_stmt_num := 10;
471 l_sql_stmt := CSTPLMWI.wip_layer_consume_sql
472 (
473 to_char( NULL ),
474 CSTPLMWI.LIFO,
475 CSTPLMWI.NORMAL
476 );
477
478 l_stmt_num := 20;
479 open l_layer_cursor for l_sql_stmt
480 using i_wip_entity_id, i_op_seq_num, i_inv_item_id;
481
482 l_stmt_num := 30;
483 fetch l_layer_cursor into l_layer;
484
485 l_stmt_num := 40;
486 close l_layer_cursor;
487
488 l_stmt_num := 50;
489 return l_layer;
490
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 o_err_num := SQLCODE;
495 o_err_msg := 'CSTPLMWI.get_last_layer():' ||
496 to_char(l_stmt_num) || ':' ||
497 substr(SQLERRM,1,150);
498
499 END get_last_layer;
500
501
502
503
504
505 ---------------------------------------------------------------
506 -- init_wip_layers
507 -- This function initializes WROCD, CWL, and CWLCD for
508 -- a particular WIP entity/op/Item combination. It will
509 -- create default rows in these tables if they don't exist.
510 ---------------------------------------------------------------
511 PROCEDURE init_wip_layers (
512 i_wip_entity_id IN NUMBER,
513 i_op_seq_num IN NUMBER,
514 i_inv_item_id IN NUMBER,
515 i_org_id IN NUMBER,
516 i_txn_id IN NUMBER,
517 i_layer_id IN NUMBER,
518 i_user_id IN NUMBER,
519 i_login_id IN NUMBER,
520 i_request_id IN NUMBER,
521 i_prog_id IN NUMBER,
522 i_prog_appl_id IN NUMBER,
523 o_err_num OUT NOCOPY NUMBER,
524 o_err_msg OUT NOCOPY VARCHAR2
525 )
526 IS
527 l_stmt_num NUMBER;
528
529 l_inv_layer_id NUMBER;
530 l_layer_qty_table CSTPLMWI.LayerQtyRecTable;
531 l_wip_layer_id NUMBER;
532
533 l_err_code VARCHAR2(2000);
534
535 l_item_layer_id NUMBER;
536 l_cost_group_id NUMBER;
537
538 invalid_inv_layer_exception EXCEPTION;
539
540 l_item_id NUMBER;
541 l_org_id NUMBER;
542 l_exp_item_flag NUMBER;
543
544 BEGIN
545
546 l_stmt_num := 5;
547 select mmt.inventory_item_id,
548 mmt.organization_id
549 into l_item_id,
550 l_org_id
551 from mtl_material_transactions mmt
552 where mmt.transaction_id = i_txn_id;
553
554 select decode(inventory_asset_flag,'Y', 0, 1)
555 into l_exp_item_flag
556 from mtl_system_items
557 where inventory_item_id = l_item_id
558 and organization_id = l_org_id;
559
560 -- clear the temp_relieved_value column
561 l_stmt_num := 10;
562 update wip_req_operation_cost_details
563 set temp_relieved_value = 0
564 where wip_entity_id = i_wip_entity_id and
565 operation_seq_num = i_op_seq_num and
566 inventory_item_id = i_inv_item_id;
567
568 if l_exp_item_flag <> 1 then
569 -- don't insert into WROCD for expense item
570 -- insert into WROCD if not already there
571 l_stmt_num := 20;
572 INSERT INTO WIP_REQ_OPERATION_COST_DETAILS WROCD
573 (
574 WIP_ENTITY_ID,
575 OPERATION_SEQ_NUM,
576 ORGANIZATION_ID,
577 INVENTORY_ITEM_ID,
578 COST_ELEMENT_ID,
579 APPLIED_MATL_VALUE,
580 RELIEVED_MATL_COMPLETION_VALUE,
581 RELIEVED_MATL_SCRAP_VALUE,
582 LAST_UPDATED_BY,
583 LAST_UPDATE_DATE,
584 CREATION_DATE,
585 CREATED_BY,
586 LAST_UPDATE_LOGIN,
587 REQUEST_ID,
588 PROGRAM_APPLICATION_ID,
589 PROGRAM_ID,
590 PROGRAM_UPDATE_DATE
591 )
592 SELECT
593 i_wip_entity_id, -- WIP_ENTITY_ID,
594 i_op_seq_num, -- OPERATION_SEQ_NUM,
595 i_org_id, -- ORGANIZATION_ID,
596 i_inv_item_id, -- INVENTORY_ITEM_ID,
597 CCE.cost_element_id, -- COST_ELEMENT_ID,
598 0, -- APPLIED_MATL_VALUE,
599 0, -- RELIEVED_MATL_COMPLETION_VALUE,
600 0, -- RELIEVED_MATL_SCRAP_VALUE,
601 i_user_id, -- LAST_UPDATED_BY,
602 sysdate, -- LAST_UPDATE_DATE,
603 sysdate, -- CREATION_DATE,
604 i_user_id, -- CREATED_BY,
605 i_login_id, -- LAST_UPDATE_LOGIN,
606 i_request_id, -- REQUEST_ID,
607 i_prog_appl_id, -- PROGRAM_APPLICATION_ID,
608 i_prog_id, -- PROGRAM_ID,
609 sysdate -- PROGRAM_UPDATE_DATE
610 from
611 cst_cost_elements CCE
612 where
613 NOT EXISTS
614 (
615 SELECT 'X'
616 FROM WIP_REQ_OPERATION_COST_DETAILS WROCD2
617 WHERE
618 WROCD2.WIP_ENTITY_ID = i_wip_entity_id AND
619 WROCD2.OPERATION_SEQ_NUM = i_op_seq_num AND
620 WROCD2.INVENTORY_ITEM_ID = i_inv_item_id AND
621 WROCD2.COST_ELEMENT_ID = CCE.cost_element_id
622 ) AND
623 EXISTS
624 (
625 select 'x'
626 from wip_requirement_operations WRO
627 where WRO.wip_entity_id = i_wip_entity_id and
628 WRO.operation_seq_num = i_op_seq_num and
629 WRO.inventory_item_id = i_inv_item_id and
630 WRO.wip_supply_type not in (4, 5, 6)
631 )
632 group by
633 CCE.cost_element_id;
634 end if;
635
636
637 -- check for WIP layers
638 l_stmt_num := 30;
639 update cst_wip_layers CWL
640 set temp_relieved_qty = 0
641 where
642 CWL.wip_entity_id = i_wip_entity_id and
643 CWL.operation_seq_num = i_op_seq_num and
644 CWL.inventory_item_id = i_inv_item_id;
645
646
647 -- if no WIP layer found, create new one using current INV layer cost
648 l_stmt_num := 40;
649 IF SQL%ROWCOUNT <= 0 THEN
650
651 /*
652 Fix for BUG 1359047:
653 The CQL i_layer_id may belong to the assembly whereas
654 i_inv_item_id belongs to the component. Will look up
655 the correct CQL layer_id for the component through the
656 Cost Group ID.
657
658 CQL must have only one row per item/org/CG combination;
659 otherwise this statement will fail (purposefully).
660 */
661
662 if (l_exp_item_flag <> 1) then
663 l_stmt_num := 45;
664 SELECT cost_group_id
665 INTO l_cost_group_id
666 FROM cst_quantity_layers
667 WHERE layer_id = i_layer_id;
668
669 l_stmt_num := 48;
670 SELECT NVL(MIN(CQL.layer_id),-1)
671 INTO l_item_layer_id
672 FROM cst_quantity_layers CQL
673 WHERE CQL.inventory_item_id = i_inv_item_id
674 AND CQL.organization_id = i_org_id
675 AND CQL.cost_group_id = l_cost_group_id;
676
677 IF (l_item_layer_id = -1) THEN
678 l_item_layer_id := CSTPACLM.create_layer(
679 i_org_id => i_org_id,
680 i_item_id => i_inv_item_id,
681 i_cost_group_id => l_cost_group_id,
682 i_user_id => i_user_id,
683 i_request_id => i_request_id,
684 i_prog_id => i_prog_id,
685 i_prog_appl_id => i_prog_appl_id,
686 i_txn_id => i_txn_id,
687 o_err_num => o_err_num,
688 o_err_code => l_err_code,
689 o_err_msg => o_err_msg);
690 END IF;
691
692
693 l_stmt_num := 50;
694 l_inv_layer_id := CSTPLENG.get_current_layer
695 (
696 I_ORG_ID => i_org_id,
697 I_TXN_ID => i_txn_id,
698 I_LAYER_ID => l_item_layer_id,
699 I_ITEM_ID => i_inv_item_id,
700 I_USER_ID => i_user_id,
701 I_LOGIN_ID => i_login_id,
702 I_REQ_ID => i_request_id,
703 I_PRG_APPL_ID => i_prog_appl_id,
704 I_PRG_ID => i_prog_id,
705 I_TXN_SRC_TYPE_ID => 5,
706 I_TXN_SRC_ID => i_wip_entity_id,
707 O_Err_Num => o_err_num,
708 O_Err_Code => l_err_code,
709 O_Err_Msg => o_err_msg
710 );
711 IF o_err_num <> 0 THEN
712 RETURN;
713 END IF;
714 end if; /* l_exp_item_flag <> 1 */
715
716 l_layer_qty_table := CSTPLMWI.LayerQtyRecTable();
717 l_layer_qty_table.EXTEND;
718 if (l_exp_item_flag <> 1) then
719 l_layer_qty_table( l_layer_qty_table.LAST ).layer_id := l_inv_layer_id;
720 else
721 l_layer_qty_table( l_layer_qty_table.LAST ).layer_id := -1;
722 end if;
723 l_layer_qty_table( l_layer_qty_table.LAST ).layer_qty := 0;
724
725
726 l_stmt_num := 60;
727 l_wip_layer_id := CSTPLMWI.wip_layer_create
728 (
729 i_wip_entity_id,
730 i_op_seq_num,
731 i_inv_item_id,
732 i_txn_id,
733 l_layer_qty_table,
734 i_user_id,
735 i_login_id,
736 i_request_id,
737 i_prog_id,
738 i_prog_appl_id,
739 o_err_num,
740 o_err_msg
741 );
742
743 IF (l_wip_layer_id <= 0 and l_exp_item_flag <> 1) THEN
744 RAISE invalid_inv_layer_exception;
745 END IF;
746
747 END IF; -- l_layer_count <= 0
748
749
750
751
752 EXCEPTION
753 WHEN invalid_inv_layer_exception THEN
754 o_err_num := 1005;
755 o_err_msg := 'CSTPLMWI.init_wip_layers():' ||
756 to_char(l_stmt_num) || ':' ||
757 'FIFO Inventory returned invalid current inv layer';
758 RETURN;
759
760 WHEN OTHERS THEN
761 o_err_num := SQLCODE;
762 o_err_msg := 'CSTPLMWI.init_wip_layers():' ||
763 to_char(l_stmt_num) || ':' ||
764 substr(SQLERRM,1,150);
765
766 END init_wip_layers;
767
768
769
770
771 -----------------------------------------------------------------
772 -- reset_temp_columns
773 -- This function resets the temp_relieve_value/qty
774 -- columns in WROCD, WOO, WOR, and CWL.
775 -----------------------------------------------------------------
776 PROCEDURE reset_temp_columns (
777 i_wip_entity_id IN NUMBER,
778 o_err_num OUT NOCOPY NUMBER,
779 o_err_msg OUT NOCOPY VARCHAR2
780 )
781 IS
782 l_stmt_num NUMBER := 0;
783 BEGIN
784
785 l_stmt_num := 10;
786
787 UPDATE WIP_REQ_OPERATION_COST_DETAILS
788 SET temp_relieved_value = 0
789 where WIP_ENTITY_ID = i_wip_entity_id;
790
791 l_stmt_num := 20;
792
793 UPDATE WIP_OPERATION_RESOURCES
794 SET temp_relieved_value = 0
795 where WIP_ENTITY_ID = i_wip_entity_id;
796
797 l_stmt_num := 30;
798
799 UPDATE WIP_OPERATION_OVERHEADS
800 SET temp_relieved_value = 0
801 where WIP_ENTITY_ID = i_wip_entity_id;
802
803 l_stmt_num := 40;
804
805 update cst_wip_layers
806 set temp_relieved_qty = 0
807 where wip_entity_id = i_wip_entity_id;
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 o_err_num := SQLCODE;
812 o_err_msg := 'CSTPLMWI.reset_temp_values():' ||
813 to_char(l_stmt_num) || ':' ||
814 substr(SQLERRM,1,150);
815
816 END reset_temp_columns;
817
818
819
820
821
822 END CSTPLMWI;