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