[Home] [Help]
PACKAGE BODY: APPS.WSMPGENE
Source
1 PACKAGE BODY WSMPGENE AS
2 /* $Header: WSMGENEB.pls 115.9 2001/08/06 15:36:09 pkm ship $ */
3
4 PROCEDURE one_inv_g(
5 wip_ent_id NUMBER,
6 level_no NUMBER,
7 ent_name IN OUT VARCHAR2,
8 ent_id IN OUT NUMBER,
9 qty IN OUT NUMBER,
10 org_id IN OUT number,
11 x_err_code OUT NUMBER,
12 x_err_msg OUT VARCHAR2) IS
13 /*
14 -- Tuned statement (Ramana Mulpury, 05/14/99)
15 */
16 CURSOR get_wip_info IS
17 SELECT
18 lpad(we.wip_entity_name,length(we.wip_entity_name)+(4*level_no)),
19 we.wip_entity_id,
20 dj.start_quantity,
21 dj.organization_id
22 FROM wip_discrete_jobs dj,
23 wip_entities we
24 WHERE we.wip_entity_id = wip_ent_id
25 AND we.wip_entity_id = dj.wip_entity_id;
26 BEGIN
27 OPEN get_wip_info;
28 FETCH get_wip_info INTO ent_name, ent_id, qty, org_id;
29 CLOSE get_wip_info;
30
31 EXCEPTION WHEN OTHERS THEN
32 X_err_code := SQLCODE;
33 X_err_msg := 'WSMPGENE.ONE_INV_G '|| SUBSTR(SQLERRM,1,60);
34
35 END one_inv_g;
36
37
38 PROCEDURE first_wip_g(wip_ent_id NUMBER,
39 item_number IN OUT VARCHAR2,
40 org_id IN OUT number,
41 x_err_code OUT NUMBER,
42 x_err_msg OUT VARCHAR2) IS
43 /*
44 -- Modified (Ramana)
45 */
46
47 -- commented out by abedajna, 10/11/00
48 --** CURSOR get_ent_info IS
49 --** SELECT /*+ ORDERED */
50 --** msi.segment1,
51 --** dj.organization_id
52 --** FROM wip_discrete_jobs dj,
53 --** mtl_system_items msi
54 --** WHERE dj.wip_entity_id = wip_ent_id
55 --** AND dj.primary_item_id = msi.inventory_item_id
56 --** AND dj.organization_id = msi.organization_id;
57
58
59 -- abedajna modification 10/11/00
60 CURSOR get_ent_info IS
61 SELECT /*+ ORDERED */
62 msi.concatenated_segments,
63 dj.organization_id
64 FROM wip_discrete_jobs dj,
65 mtl_system_items_kfv msi
66 WHERE dj.wip_entity_id = wip_ent_id
67 AND dj.primary_item_id = msi.inventory_item_id
68 AND dj.organization_id = msi.organization_id;
69
70
71
72
73 BEGIN
74 OPEN get_ent_info;
75 FETCH get_ent_info INTO item_number, org_id;
76 CLOSE get_ent_info;
77
78 EXCEPTION WHEN OTHERS THEN
79 X_err_code := SQLCODE;
80 X_err_msg := 'WSMPGENE.FIRST_WIP_G '|| SUBSTR(SQLERRM,1,60);
81
82 END first_wip_g;
83
84
85 PROCEDURE issue_to_wip_n(trans_id NUMBER,
86 wip_ent_id IN OUT NUMBER,
87 x_err_code OUT NUMBER,
88 x_err_msg OUT VARCHAR2) IS
89
90 CURSOR get_wip_id IS
91 SELECT
92 transaction_source_id
93 FROM mtl_material_transactions
94 WHERE transaction_id = trans_id
95 AND transaction_source_type_id = 5;
96 BEGIN
97 OPEN get_wip_id;
98 FETCH get_wip_id INTO wip_ent_id;
99 CLOSE get_wip_id;
100
101 EXCEPTION WHEN OTHERS THEN
102 X_err_code := SQLCODE;
103 X_err_msg := 'WSMPGENE.ISSUE_TO_WIP_N '|| SUBSTR(SQLERRM,1,60);
104
105
106 END issue_to_wip_n;
107
108
109 PROCEDURE fes_txn_n(trans_ref IN NUMBER,
110 lot_name IN VARCHAR2,
111 item_id IN NUMBER,
112 cur_trans_id IN OUT NUMBER,
113 next_trans_id IN OUT NUMBER,
114 next_indicator IN OUT VARCHAR2,
115 next_trans_ref IN OUT NUMBER,
116 tran_name IN OUT VARCHAR2,
117 inv_or_wip IN OUT VARCHAR2,
118 txn_type IN OUT VARCHAR2,
119 txn_type_id OUT NUMBER ,
120 x_err_code OUT NUMBER,
121 x_err_msg OUT VARCHAR2) IS
122
123 mmt_tran_type_id NUMBER;
124
125 CURSOR get_cur_trans_id IS
126 SELECT
127 max(transaction_id)
128 FROM mtl_material_transactions
129 WHERE source_line_id = trans_ref
130 AND transaction_quantity < 0;
131
132 CURSOR test_for_more IS
133 SELECT
134 MIN(transaction_id)
135 FROM mtl_transaction_lot_numbers
136 WHERE inventory_item_id = item_id
137 AND lot_number = ltrim(lot_name)
138 AND transaction_id > cur_trans_id
139 AND transaction_quantity < 0;
140
141 CURSOR next_details IS
142 SELECT /*+ ORDERED */
143 mmt.source_line_id,
144 mmt.transaction_type_id,
145 mmt.source_code
146 FROM mtl_material_transactions mmt
147 WHERE mmt.transaction_id = next_trans_id;
148
149 /* AND mmt.transaction_type_id = mtt.transaction_type_id; */
150
151 BEGIN
152 OPEN get_cur_trans_id;
153 FETCH get_cur_trans_id INTO cur_trans_id;
154 CLOSE get_cur_trans_id;
155
156 OPEN test_for_more;
157 FETCH test_for_more INTO next_trans_id;
158 CLOSE test_for_more;
159
160 IF next_trans_id IS NOT NULL
161 THEN next_indicator := '+';
162 OPEN next_details;
163 FETCH next_details INTO next_trans_ref, mmt_tran_type_id, tran_name;
164 CLOSE next_details;
165
166 IF tran_name not like 'WSM%'
167 THEN txn_type := '';
168 inv_or_wip := 'INV';
169 next_trans_id := '';
170
171 -- Added 'Else' here to reslove Bug #1795523
172 -- The code following else was already there
173
174 ELSE
175 wsm_inv_meaning_t(next_trans_ref,txn_type,txn_type_id,
176 x_err_code, x_err_msg);
177 /* IF txn_type IN ('INVSplit','INVTrans','INVMerge') */
178 IF (txn_type_id IN (1,2,3))
179 THEN next_trans_id := next_trans_ref;
180 inv_or_wip := 'FES_INV';
181 ELSIF txn_type = 'Issue from Stores'
182 THEN inv_or_wip := 'INV';
183 SELECT
184 wip_entity_id
185 INTO next_trans_id
186 FROM wsm_sm_resulting_lots
187 WHERE transaction_id = next_trans_ref;
188
189 /* ELSIF txn_type = 'SubXsfer' */
190 ELSIF txn_type_id = 4
191 THEN inv_or_wip := 'INV';
192 txn_type := '';
193 next_trans_id := '';
194 END IF;
195 END IF;
196 ELSE next_indicator := '';
197 txn_type := '';
198 inv_or_wip := 'FES_INV';
199 END IF;
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 X_err_code := 99999;
203 X_err_msg := 'NULL EXCEPTION ';
204 WHEN OTHERS THEN
205 X_err_code := SQLCODE;
206 X_err_msg := 'WSMPGENE.FES_TXN_N '|| SUBSTR(SQLERRM,1,60);
207
208 END fes_txn_n;
209
210
211 PROCEDURE wip_to_complete_n(wip_ent_id NUMBER,org_id NUMBER,
212 no_trans IN OUT NUMBER,
213 x_err_code OUT NUMBER,
214 x_err_msg OUT VARCHAR2 ) IS
215 CURSOR wip_completion IS
216 SELECT
217 count(*)
218 FROM mtl_transaction_lot_numbers mtln,
219 mtl_material_transactions mt
220 WHERE mt.transaction_source_id = wip_ent_id
221 AND mt.organization_id = org_id
222 AND mt.transaction_action_id = 31
223 AND mt.transaction_source_type_id = 5
224 AND mtln.transaction_id = mt.transaction_id;
225
226 BEGIN
227 OPEN wip_completion;
228 FETCH wip_completion INTO no_trans;
229 CLOSE wip_completion;
230 END wip_to_complete_n;
231
232
233 PROCEDURE inv_n(item_id NUMBER,
234 lot_name VARCHAR2,
235 cur_trans_id NUMBER,
236 no_of_trans IN OUT NUMBER,
237 x_err_code OUT NUMBER,
238 x_err_msg OUT VARCHAR2) IS
239 CURSOR check_for_more IS
240 SELECT
241 count(*)
242 FROM mtl_transaction_lot_numbers
243 WHERE lot_number = lot_name
244 AND inventory_item_id = item_id
245 AND transaction_id > cur_trans_id
246 AND transaction_quantity < 0;
247 BEGIN
248 OPEN check_for_more;
249 FETCH check_for_more INTO no_of_trans;
250 CLOSE check_for_more;
251
252 EXCEPTION WHEN OTHERS THEN
253 X_err_code := SQLCODE;
254 X_err_msg := 'WSMPGENE.INV_N '|| SUBSTR(SQLERRM,1,60);
255
256 END inv_n;
257
258
259 PROCEDURE new_qty_n(trans_id NUMBER,wip_ent_id NUMBER,
260 qty IN OUT NUMBER,
261 x_err_code OUT NUMBER,
262 x_err_msg OUT VARCHAR2 ) IS
263 CURSOR get_qty IS
264 SELECT
265 available_quantity
266 FROM wsm_sm_starting_jobs
267 WHERE wip_entity_id = wip_ent_id
268 AND transaction_id = trans_id;
269 BEGIN
270 OPEN get_qty;
271 FETCH get_qty INTO qty;
272 CLOSE get_qty;
273
274 EXCEPTION WHEN OTHERS THEN
275 X_err_code := SQLCODE;
276 X_err_msg := 'WSMPGENE.NEW_QTY_N '|| SUBSTR(SQLERRM,1,60);
277
278 END new_qty_n;
279
280
281 PROCEDURE completed_qty_n(wip_ent_id NUMBER,
282 qty IN OUT NUMBER,
283 x_err_code OUT NUMBER,
284 x_err_msg OUT VARCHAR2) IS
285 CURSOR get_qty IS
286 SELECT
287 quantity_completed
288 FROM wip_discrete_jobs
289 WHERE wip_entity_id = wip_ent_id;
290 BEGIN
291 OPEN get_qty;
292 FETCH get_qty INTO qty;
293 CLOSE get_qty;
294 EXCEPTION WHEN OTHERS THEN
295 X_err_code := SQLCODE;
296 X_err_msg := 'WSMPGENE.COMPLETED_QTY_N '|| SUBSTR(SQLERRM,1,60);
297
298 END completed_qty_n;
299
300
301 PROCEDURE first_wip_w(wip_ent_id NUMBER,
302 item_number IN OUT VARCHAR2,
303 org_id IN OUT NUMBER,
304 x_err_code OUT NUMBER,
305 x_err_msg OUT VARCHAR2) IS
306
307 -- commented out by abedajna, 10/11/00
308 /* CURSOR get_ent_info IS
309 ** SELECT
310 ** msi.segment1,
311 ** dj.organization_id
312 ** FROM wip_discrete_jobs dj,
313 ** mtl_system_items msi
314 ** WHERE dj.wip_entity_id = wip_ent_id
315 ** AND dj.primary_item_id = msi.inventory_item_id
316 ** AND dj.organization_id = msi.organization_id;
317 */
318
319
320 -- modification by abedajna, 10/11/00
321 CURSOR get_ent_info IS
322 SELECT
323 msi.concatenated_segments,
324 dj.organization_id
325 FROM wip_discrete_jobs dj,
326 mtl_system_items_kfv msi
327 WHERE dj.wip_entity_id = wip_ent_id
328 AND dj.primary_item_id = msi.inventory_item_id
329 AND dj.organization_id = msi.organization_id;
330
331
332
333 BEGIN
334 OPEN get_ent_info;
335 FETCH get_ent_info INTO item_number, org_id;
336 CLOSE get_ent_info;
337 EXCEPTION WHEN OTHERS THEN
338 X_err_code := SQLCODE;
339 X_err_msg := 'WSMPGENE.FIRST_WIP_W '|| SUBSTR(SQLERRM,1,60);
340 END first_wip_w;
341
342
343 PROCEDURE one_inv_w(wip_ent_id NUMBER,
344 level_no NUMBER,
345 ent_name IN OUT VARCHAR2,
346 ent_id IN OUT NUMBER,
347 qty IN OUT NUMBER,
348 item_number IN OUT VARCHAR2,
349 org_id IN OUT NUMBER,
350 x_err_code OUT NUMBER,
351 x_err_msg OUT VARCHAR2) IS
352
353 -- commented out by abedajna, 10/11/00
354 --** CURSOR get_wip_info IS
355 --** SELECT
356 --** lpad(we.wip_entity_name,length(we.wip_entity_name)+(4*level_no)),
357 --** we.wip_entity_id,
358 --** dj.start_quantity,
359 --** msi.segment1,
360 --** dj.organization_id
361 --** FROM wip_entities we,
362 --** wip_discrete_jobs dj,
363 --** mtl_system_items msi
364 --** WHERE we.wip_entity_id = wip_ent_id
365 --** AND we.wip_entity_id = dj.wip_entity_id
366 --** AND dj.primary_item_id = msi.inventory_item_id
367 --** AND dj.organization_id = msi.organization_id;
368
369
370 -- modification by abedajna, 10/11/00
371
372 CURSOR get_wip_info IS
373 SELECT
374 lpad(we.wip_entity_name,length(we.wip_entity_name)+(4*level_no)),
375 we.wip_entity_id,
376 dj.start_quantity,
377 msi.concatenated_segments,
378 dj.organization_id
379 FROM wip_entities we,
380 wip_discrete_jobs dj,
381 mtl_system_items_kfv msi
382 WHERE we.wip_entity_id = wip_ent_id
383 AND we.wip_entity_id = dj.wip_entity_id
384 AND dj.primary_item_id = msi.inventory_item_id
385 AND dj.organization_id = msi.organization_id;
386
387
388
389 BEGIN
390 OPEN get_wip_info;
391 FETCH get_wip_info INTO ent_name, ent_id, qty, item_number, org_id;
392 CLOSE get_wip_info;
393
394 EXCEPTION WHEN OTHERS THEN
395 X_err_code := SQLCODE;
396 X_err_msg := 'WSMPGENE.ONE_INV_W '|| SUBSTR(SQLERRM,1,60);
397 END one_inv_w;
398
399
400 PROCEDURE subxsfer_refs_p(trans_id NUMBER,
401 from_sub IN OUT VARCHAR2,
402 to_sub IN OUT VARCHAR2,
403 x_err_code OUT NUMBER,
404 x_err_msg OUT VARCHAR2) IS
405 CURSOR get_info IS
406 SELECT
407 starting.subinventory_code,
408 result.subinventory_code
409 FROM wsm_sm_starting_lots starting,
410 wsm_sm_resulting_lots result
411 WHERE starting.transaction_id = trans_id
412 AND result.transaction_id = trans_id;
413 BEGIN
414 OPEN get_info;
415 FETCH get_info INTO from_sub, to_sub;
416 CLOSE get_info;
417 EXCEPTION WHEN OTHERS THEN
418 X_err_code := SQLCODE;
419 X_err_msg := 'WSMPGENE.SUBXSFER_REFS_P '|| SUBSTR(SQLERRM,1,60);
420
421
422 END subxsfer_refs_p;
423
424 /********************************************************************************/
425 /* Changed procedure to function */
426
427 FUNCTION complete_from_wip (trans_id NUMBER,
428 x_err_code OUT NUMBER,
429 x_err_msg OUT VARCHAR2) RETURN NUMBER IS
430
431 x_wip_ent_id NUMBER;
432
433 CURSOR get_wip_id IS
434 SELECT
435 transaction_source_id
436 FROM mtl_material_transactions
437 WHERE transaction_id = trans_id
438 AND transaction_source_type_id = 5;
439 BEGIN
440 OPEN get_wip_id;
441 FETCH get_wip_id INTO x_wip_ent_id;
442 CLOSE get_wip_id;
443 RETURN x_wip_ent_id;
444 EXCEPTION WHEN OTHERS THEN
445 X_err_code := SQLCODE;
446 X_err_msg := 'WSMPGENE.COMPLETE_FROM_WIP '|| SUBSTR(SQLERRM,1,60);
447
448 END complete_from_wip;
449
450 /**********************************************************************************/
451
452 PROCEDURE fes_txn_o(trans_ref IN NUMBER,
453 lot_name IN VARCHAR2,
454 item_id IN NUMBER,
455 cur_trans_id IN OUT NUMBER,
456 next_indicator IN OUT VARCHAR2,
457 inv_or_wip IN OUT VARCHAR2,
458 next_trans_id IN OUT NUMBER,
459 txn_type IN OUT VARCHAR2,
460 txn_type_id OUT NUMBER,
461 next_trans_ref IN OUT NUMBER,
462 tran_name IN OUT VARCHAR2,
463 wip_id IN OUT NUMBER,
464 x_err_code OUT NUMBER,
465 x_err_msg OUT VARCHAR2) IS
466
467 tran_type_id NUMBER ;
468
469 CURSOR get_cur_trans_id IS
470 SELECT
471 MIN(mmt.transaction_id)
472 FROM mtl_transaction_lot_numbers mtln,
473 mtl_material_transactions mmt
474 WHERE mtln.lot_number = lot_name
475 AND mtln.transaction_id = mmt.transaction_id
476 AND mmt.source_line_id = trans_ref;
477 CURSOR test_for_more IS
478 SELECT
479 MAX(transaction_id)
480 FROM mtl_transaction_lot_numbers
481 WHERE lot_number = lot_name
482 AND inventory_item_id = item_id
483 AND transaction_id < cur_trans_id
484 AND transaction_quantity > 0;
485 /*
486 -- Tuned statement (Ramana Mulpury, 05/14/99)
487 */
488 CURSOR next_details IS
489 SELECT mmt.source_line_id,
490 mmt.transaction_type_id,
491 mmt.source_code,
492 mmt.transaction_source_id
493 FROM mtl_material_transactions mmt
494 WHERE mmt.transaction_id = next_trans_id ;
495
496 /* AND mmt.transaction_type_id = mtt.transaction_type_id; */
497
498 BEGIN
499 OPEN get_cur_trans_id;
500 FETCH get_cur_trans_id INTO cur_trans_id;
501 CLOSE get_cur_trans_id;
502
503 OPEN test_for_more;
504 FETCH test_for_more INTO next_trans_id;
505 CLOSE test_for_more;
506
507 IF next_trans_id IS NOT NULL
508 THEN next_indicator := '+';
509 OPEN next_details;
510 FETCH next_details INTO next_trans_ref, tran_type_id, tran_name, wip_id;
511 CLOSE next_details;
512
513 IF tran_name like 'WSM%' THEN
514 wsm_inv_meaning_t(next_trans_ref,txn_type,txn_type_id,
515 x_err_code, x_err_msg);
516 /* IF txn_type IN ('INVSplit','INVTrans','INVMerge')*/
517 IF (txn_type_id IN (1,2,3))
518 THEN next_trans_id := next_trans_ref;
519 inv_or_wip := 'FES_INV';
520 ELSIF txn_type_id = 4
521 THEN inv_or_wip := 'INV';
522 txn_type := '';
523 next_trans_id := '';
524 END IF;
525 ELSIF tran_type_id = 44
526 /* THEN txn_type := 'Complete';*/
527 THEN SELECT transaction_type_name INTO txn_type
528 FROM mtl_transaction_types
529 WHERE transaction_type_id = 44 ;
530 inv_or_wip := 'INV';
531 next_trans_id := wip_id;
532 ELSE next_indicator := '+';
533 txn_type := '';
534 inv_or_wip := 'INV';
535 next_trans_id := '';
536 END IF;
537 ELSE next_indicator := '';
538 txn_type := '';
539 inv_or_wip := 'FES_INV';
540 END IF;
541
542 EXCEPTION WHEN OTHERS THEN
543
544 X_err_code := SQLCODE;
545 X_err_msg := 'WSMPGENE.FES_TXN_O '|| SUBSTR(SQLERRM,1,60);
546
547
548 END fes_txn_o;
549
550
551 PROCEDURE issue_from_inv_o(wip_ent_id NUMBER,
552 org_id NUMBER,
553 no_trans IN OUT NUMBER,
554 x_err_code OUT NUMBER,
555 x_err_msg OUT VARCHAR2) IS
556
557 -- commented out by abedajna on 10/13/00 for performance tuning.
558 /*
559 -- Tuned statement (Ramana Mulpury, 05/13/99)
560 */
561 /*
562 ** CURSOR issues IS
563 ** SELECT 1
564 ** FROM sys.dual
565 ** WHERE exists (select 1
566 ** from mtl_transaction_lot_numbers mtln,
567 ** mtl_material_transactions mt
568 ** WHERE mtln.transaction_id = mt.transaction_id
569 ** AND mt.transaction_source_id = wip_ent_id
570 ** AND mt.organization_id = org_id
571 ** AND mt.transaction_action_id = 1
572 ** AND mt.transaction_source_type_id = 5);
573 ** BEGIN
574 ** OPEN issues;
575 ** FETCH issues INTO no_trans;
576 ** CLOSE issues;
577 ** EXCEPTION WHEN OTHERS THEN
578 ** X_err_code := SQLCODE;
579 ** X_err_msg := 'WSMPGENE.ISSUE_FROM_INV_O '|| SUBSTR(SQLERRM,1,60);
580 */
581
582 -- modified by abedajna on 10/13/00 for performance tuning.
583
584 BEGIN
585
586 SELECT 1
587 INTO no_trans
588 FROM mtl_transaction_lot_numbers mtln,
589 mtl_material_transactions mt
590 WHERE mtln.transaction_id = mt.transaction_id
591 AND mt.transaction_source_id = wip_ent_id
592 AND mt.organization_id = org_id
593 AND mt.transaction_action_id = 1
594 AND mt.transaction_source_type_id = 5;
595
596 EXCEPTION
597
598 WHEN TOO_MANY_ROWS THEN
599 no_trans := 1;
600
601 WHEN NO_DATA_FOUND THEN
602 NULL;
603
604 WHEN OTHERS THEN
605 X_err_code := SQLCODE;
606 X_err_msg := 'WSMPGENE.ISSUE_FROM_INV_O '|| SUBSTR(SQLERRM,1,60);
607
608 -- end of modification by abedajna on 10/13/00 for performance tuning.
609
610
611 END issue_from_inv_o;
612
613
614 PROCEDURE wip_o(cur_trans_id IN NUMBER,
615 wip_ent_id IN NUMBER,
616 next_trans_id IN OUT NUMBER,
617 x_err_code OUT NUMBER,
618 x_err_msg OUT VARCHAR2) IS
619 /*
620 -- Tuned statement (Ramana Mulpury, 05/14/99)
621 */
622 CURSOR find_next_id IS
623 SELECT
624 max(rj.transaction_id)
625 FROM
626 --bugfix 1796646, check status with error code '3', instead of with mfg_lookup
627 -- mfg_lookups lk,
628 wsm_split_merge_transactions tx,
629 wsm_sm_resulting_jobs rj
630 WHERE rj.wip_entity_id = wip_ent_id
631 AND rj.transaction_id < nvl(cur_trans_id,
632 rj.transaction_id + 1)
633 AND rj.transaction_id = tx.transaction_id
634 and tx.status <> 3;
635 -- AND tx.status = lk.lookup_code
636 -- AND lk.lookup_type = 'WIP_PROCESS_STATUS'
637 -- AND lk.meaning <> 'Error';
638 -- endfix1796646
639 BEGIN
640 OPEN find_next_id;
641 FETCH find_next_id INTO next_trans_id;
642 CLOSE find_next_id;
643 EXCEPTION WHEN OTHERS THEN
644 X_err_code := SQLCODE;
645 X_err_msg := 'WSMPGENE.FIND_NEXT_ID '|| SUBSTR(SQLERRM,1,60);
646
647
648 END wip_o;
649
650
651 PROCEDURE inv_o(item_id NUMBER,
652 lot_name VARCHAR2,
653 cur_trans_id NUMBER,
654 no_of_trans IN OUT NUMBER,
655 x_err_code OUT NUMBER,
656 x_err_msg OUT VARCHAR2) IS
657 /*
658 -- Tuned statement (Ramana Mulpury, 05/13/99)
659 */
660
661 -- commented out by abedajna on 10/13/00 for performance tuning.
662
663 /* CURSOR check_for_more IS
664 ** SELECT 1
665 ** FROM sys.dual
666 ** WHERE EXISTS (SELECT 1
667 ** FROM mtl_transaction_lot_numbers
668 ** WHERE lot_number = lot_name
669 ** AND inventory_item_id = item_id
670 ** AND transaction_id < cur_trans_id
671 ** AND transaction_quantity > 0);
672 ** BEGIN
673 ** OPEN check_for_more;
674 ** FETCH check_for_more INTO no_of_trans;
675 ** CLOSE check_for_more;
676 ** EXCEPTION WHEN OTHERS THEN
677 ** X_err_code := SQLCODE;
678 ** X_err_msg := 'WSMPGENE.INV_O '|| SUBSTR(SQLERRM,1,60);
679 */
680 -- modified by abedajna on 10/13/00 for performance tuning.
681
682 BEGIN
683
684 SELECT 1
685 INTO no_of_trans
686 FROM mtl_transaction_lot_numbers
687 WHERE lot_number = lot_name
688 AND inventory_item_id = item_id
689 AND transaction_id < nvl(cur_trans_id, transaction_id +1) --bugfix1796646 added nvl.
690 AND transaction_quantity > 0;
691
692
693 EXCEPTION
694
695 WHEN TOO_MANY_ROWS THEN
696 no_of_trans := 1;
697
698 WHEN NO_DATA_FOUND THEN
699 NULL;
700
701 WHEN OTHERS THEN
702 X_err_code := SQLCODE;
703 X_err_msg := 'WSMPGENE.INV_O '|| SUBSTR(SQLERRM,1,60);
704
705 -- end of modification by abedajna on 10/13/00 for performance tuning.
706
707 END inv_o;
708
709
710 PROCEDURE new_qty_o(trans_id NUMBER,
711 wip_ent_id NUMBER,
712 qty IN OUT NUMBER,
713 x_err_code OUT NUMBER,
714 x_err_msg OUT VARCHAR2) IS
715 CURSOR get_qty IS
716 SELECT
717 start_quantity
718 FROM wsm_sm_resulting_jobs
719 WHERE wip_entity_id = wip_ent_id
720 AND transaction_id = trans_id;
721 BEGIN
722 OPEN get_qty;
723 FETCH get_qty INTO qty;
724 CLOSE get_qty;
725 EXCEPTION WHEN OTHERS THEN
726 X_err_code := SQLCODE;
727 X_err_msg := 'WSMPGENE.NEW_QTY_O '|| SUBSTR(SQLERRM,1,60);
728
729 END new_qty_o;
730
731
732 PROCEDURE issued_qty_o(wip_ent_id NUMBER,
733 qty IN OUT NUMBER,
734 x_err_code OUT NUMBER,
735 x_err_msg OUT VARCHAR2) IS
736 CURSOR get_qty IS
737 SELECT
738 start_quantity
739 FROM wip_discrete_jobs
740 WHERE wip_entity_id = wip_ent_id;
741 BEGIN
742 OPEN get_qty;
743 FETCH get_qty INTO qty;
744 CLOSE get_qty;
745 EXCEPTION WHEN OTHERS THEN
746 X_err_code := SQLCODE;
747 X_err_msg := 'WSMPGENE.ISSUED_QTY_O '|| SUBSTR(SQLERRM,1,60);
748
749 END issued_qty_o;
750
751 /************************************************************************/
752 /* This procedure has been modified to get values from mfg_lookups */
753
754 PROCEDURE wsm_inv_meaning_t(txn_id NUMBER,
755 tran_type OUT VARCHAR2,
756 wsm_inv_txn_type_id OUT NUMBER,
757 x_err_code OUT NUMBER,
758 x_err_msg OUT VARCHAR2) IS
759
760
761 CURSOR get_meaning IS
762
763 SELECT ml.meaning,sm.transaction_type_id
764 FROM mfg_lookups ml, wsm_lot_split_merges sm
765 WHERE ml.lookup_type = 'WSM_INV_LOT_TXN_TYPE'
766 AND ml.lookup_code = sm.transaction_type_id
767 AND sm.transaction_id = txn_id;
768
769 BEGIN
770
771 OPEN get_meaning;
772 FETCH get_meaning INTO tran_type ,wsm_inv_txn_type_id;
773 IF get_meaning%NOTFOUND
774 THEN tran_type := '';
775 END IF;
776 CLOSE get_meaning;
777
778 EXCEPTION WHEN OTHERS THEN
779 X_err_code := SQLCODE;
780 X_err_msg := 'WSMPGENE.WSM_INV_MEANING_T '|| SUBSTR(SQLERRM,1,60);
781 END wsm_inv_meaning_t;
782
783 /*************************************************************************/
784
785 PROCEDURE org_transfers_t(from_org_id NUMBER,
786 to_org_id NUMBER,
787 from_org_code IN OUT VARCHAR2,
788 to_org_code IN OUT VARCHAR2,
789 x_err_code OUT NUMBER,
790 x_err_msg OUT VARCHAR2) IS
791 CURSOR get_org_names IS
792 SELECT
793 from_org.organization_code, to_org.organization_code
794 FROM mtl_parameters from_org,
795 mtl_parameters to_org
796 WHERE from_org.organization_id = from_org_id
797 AND to_org.organization_id = to_org_id;
798 BEGIN
799 OPEN get_org_names;
800 FETCH get_org_names INTO from_org_code, to_org_code;
801 CLOSE get_org_names;
802 EXCEPTION WHEN OTHERS THEN
803 X_err_code := SQLCODE;
804 X_err_msg := 'WSMPGENE.ORG_TRANSFERS_T '|| SUBSTR(SQLERRM,1,60);
805
806 END org_transfers_t;
807
808
809 PROCEDURE get_next_type_id_t(id NUMBER,
810 type_id IN OUT NUMBER,
811 x_err_code OUT NUMBER,
812 x_err_msg OUT VARCHAR2) IS
813 CURSOR get_type_id IS
814 SELECT
815 transaction_type_id
816 FROM wsm_split_merge_transactions
817 WHERE transaction_id = id;
818 BEGIN
819 OPEN get_type_id;
820 FETCH get_type_id INTO type_id;
821 CLOSE get_type_id;
822
823 EXCEPTION WHEN OTHERS THEN
824 X_err_code := SQLCODE;
825 X_err_msg := 'WSMPGENE.GET_NEXT_TYPE_ID_T '|| SUBSTR(SQLERRM,1,60);
826 END get_next_type_id_t;
827 end WSMPGENE;