[Home] [Help]
PACKAGE BODY: APPS.INV_VALUE_TO_ID
Source
1 PACKAGE BODY INV_Value_To_Id AS
2 /* $Header: INVSVIDB.pls 115.5 2004/05/27 05:52:01 cjandhya ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_Value_To_Id';
7
8 -- Procedure Get_Attr_Tbl.
9 --
10 -- Used by generator to avoid overriding or duplicating existing
11 -- conversion functions.
12 --
13 -- DO NOT REMOVE
14
15 PROCEDURE Get_Attr_Tbl
16 IS
17 I NUMBER:=0;
18 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
19 BEGIN
20
21 FND_API.g_attr_tbl.DELETE;
22
23 -- START GEN attributes
24
25 -- Generator will append new attributes before end generate comment.
26 I := I + 1;
27 FND_API.g_attr_tbl(I).name := 'Key_Flex';
28 I := I + 1;
29 FND_API.g_attr_tbl(I).name := 'from_subinventory';
30 I := I + 1;
31 FND_API.g_attr_tbl(I).name := 'header';
32 I := I + 1;
33 FND_API.g_attr_tbl(I).name := 'organization';
34 I := I + 1;
35 FND_API.g_attr_tbl(I).name := 'to_account';
36 I := I + 1;
37 FND_API.g_attr_tbl(I).name := 'to_subinventory';
38 I := I + 1;
39 FND_API.g_attr_tbl(I).name := 'transaction_type';
40 I := I + 1;
41 FND_API.g_attr_tbl(I).name := 'from_locator';
42 I := I + 1;
43 FND_API.g_attr_tbl(I).name := 'inventory_item';
44 I := I + 1;
45 FND_API.g_attr_tbl(I).name := 'line';
46 I := I + 1;
47 FND_API.g_attr_tbl(I).name := 'project';
48 I := I + 1;
49 FND_API.g_attr_tbl(I).name := 'reason';
50 I := I + 1;
51 FND_API.g_attr_tbl(I).name := 'reference';
52 I := I + 1;
53 FND_API.g_attr_tbl(I).name := 'reference_type';
54 I := I + 1;
55 FND_API.g_attr_tbl(I).name := 'task';
56 I := I + 1;
57 FND_API.g_attr_tbl(I).name := 'to_locator';
58 I := I + 1;
59 FND_API.g_attr_tbl(I).name := 'transaction_header';
60 I := I + 1;
61 FND_API.g_attr_tbl(I).name := 'uom';
62 I := I + 1;
63 FND_API.g_attr_tbl(I).name := 'uom';
64 -- END GEN attributes
65
66 END Get_Attr_Tbl;
67
68 -- Prototypes for value_to_id functions.
69
70 -- START GEN value_to_id
71
72 -- Key Flex
73
74 FUNCTION Key_Flex
75 ( p_key_flex_code IN VARCHAR2
76 , p_structure_number IN NUMBER
77 , p_appl_short_name IN VARCHAR2
78 , p_segment_array IN FND_FLEX_EXT.SegmentArray
79 )
80 RETURN NUMBER
81 IS
82 l_id NUMBER;
83 l_segment_array FND_FLEX_EXT.SegmentArray;
84 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
85 BEGIN
86
87 l_segment_array := p_segment_array;
88
89 -- Convert any missing values to NULL
90
91 FOR I IN 1..l_segment_array.COUNT LOOP
92
93 IF l_segment_array(I) = FND_API.G_MISS_CHAR THEN
94 l_segment_array(I) := NULL;
95 END IF;
96
97 END LOOP;
98
99 -- Call Flex conversion routine
100
101 IF NOT FND_FLEX_EXT.get_combination_id
102 ( application_short_name => p_appl_short_name
103 , key_flex_code => p_key_flex_code
104 , structure_number => p_structure_number
105 , validation_date => NULL
106 , n_segments => l_segment_array.COUNT
107 , segments => l_segment_array
108 , combination_id => l_id
109 )
110 THEN
111
112 -- Error getting combination id.
113 -- Function has already pushed a message on the stack. Add to
114 -- the API message list.
115
116 FND_MSG_PUB.Add;
117 l_id := FND_API.G_MISS_NUM;
118
119 END IF;
120
121 RETURN l_id;
122
123 EXCEPTION
124
125 WHEN OTHERS THEN
126
127 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
128 THEN
129 FND_MSG_PUB.Add_Exc_Msg
130 ( G_PKG_NAME
131 , 'Key_Flex'
132 );
133 END IF;
134
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136
137 END Key_Flex;
138
139 -- Generator will append new prototypes before end generate comment.
140
141
142 -- From_Subinventory
143
144 FUNCTION From_Subinventory
145 ( p_organization_id IN NUMBER,
146 p_from_subinventory IN VARCHAR2
147 ) RETURN VARCHAR2
148 IS
149 l_code VARCHAR2(10);
150 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
151 BEGIN
152
153 l_code := p_from_subinventory;
154 RETURN l_code;
155
156 END From_Subinventory;
157
158 -- Header
159
160 FUNCTION Header
161 ( p_header IN VARCHAR2
162 ) RETURN NUMBER
163 IS
164 l_id NUMBER;
165 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
166 BEGIN
167
168 IF p_header IS NULL
169 THEN
170 RETURN NULL;
171 END IF;
172
173 -- SELECT XXXX_id
174 -- INTO l_id
175 -- FROM XXXX_table
176 -- WHERE XXXX_val_column = p_header
177
178 RETURN l_id;
179
180 EXCEPTION
181
182 WHEN NO_DATA_FOUND THEN
183
184 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
185 THEN
186
187 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
188 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','header_id');
189 FND_MSG_PUB.Add;
190
191 END IF;
192
193 RETURN FND_API.G_MISS_NUM;
194
195 WHEN OTHERS THEN
196
197 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
198 THEN
199 FND_MSG_PUB.Add_Exc_Msg
200 ( G_PKG_NAME
201 , 'Header'
202 );
203 END IF;
204
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206
207 END Header;
208
209 -- Organization
210
211 FUNCTION Organization
212 ( p_organization IN VARCHAR2
213 ) RETURN NUMBER
214 IS
215 l_id NUMBER;
216 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
217 BEGIN
218
219 IF p_organization IS NULL
220 OR p_organization = FND_API.G_MISS_CHAR
221 THEN
222 RETURN NULL;
223 END IF;
224
225 SELECT ORGANIZATION_ID
226 INTO l_id
227 FROM ORG_ORGANIZATION_DEFINITIONS
228 WHERE ORGANIZATION_CODE = p_organization;
229
230 RETURN l_id;
231
232 EXCEPTION
233
234 WHEN NO_DATA_FOUND THEN
235
236 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
237 THEN
238
239 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
240 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','organization_id');
241 FND_MSG_PUB.Add;
242
243 END IF;
244
245 RETURN FND_API.G_MISS_NUM;
246
247 WHEN OTHERS THEN
248
249 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
250 THEN
251 FND_MSG_PUB.Add_Exc_Msg
252 ( G_PKG_NAME
253 , 'Organization'
254 );
255 END IF;
256
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258
259 END Organization;
260
261 FUNCTION To_Organization
262 ( p_to_organization IN VARCHAR2
263 ) RETURN NUMBER
264 IS
265 l_id NUMBER;
266 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
267 BEGIN
268
269 IF p_to_organization IS NULL
270 OR p_to_organization = FND_API.G_MISS_CHAR
271 THEN
272 RETURN NULL;
273 END IF;
274
275 SELECT ORGANIZATION_ID
276 INTO l_id
277 FROM ORG_ORGANIZATION_DEFINITIONS
278 WHERE ORGANIZATION_CODE = p_to_organization;
279
280 RETURN l_id;
281
282 EXCEPTION
283
284 WHEN NO_DATA_FOUND THEN
285
286 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
287 THEN
288
289 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
290 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_organization_id');
291 FND_MSG_PUB.Add;
292
293 END IF;
294
295 RETURN FND_API.G_MISS_NUM;
296
297 WHEN OTHERS THEN
298
299 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
300 THEN
301 FND_MSG_PUB.Add_Exc_Msg
302 ( G_PKG_NAME
303 , 'TO_Organization'
304 );
305 END IF;
306
307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
308
309 END To_Organization;
310
311 --To_Account
312 --Bug 3632199 fix. Removed the query to gl_code_combinations_kfv
313 --Now using fnd apis to get the ccid
314 FUNCTION To_Account
315 ( p_organization_id IN NUMBER,
316 p_to_account IN VARCHAR2
317 ) RETURN NUMBER
318 IS
319 l_id NUMBER := NULL;
320 l_chart NUMBER := NULL;
321 BEGIN
322 IF p_to_account IS NULL
323 OR p_to_account = FND_API.G_MISS_CHAR
324 THEN
325 RETURN NULL;
326 END IF;
327
328 SELECT gsb.chart_of_accounts_id INTO l_chart
329 FROM GL_SETS_OF_BOOKS gsb
330 WHERE gsb.set_of_books_id=
331 (SELECT to_number(hoi.org_information1)
332 FROM HR_ORGANIZATION_INFORMATION hoi
333 WHERE hoi.organization_id = p_organization_id
334 AND hoi.org_information_context = 'Accounting Information'
335 AND ROWNUM < 2);
336
337 l_id := fnd_flex_ext.get_ccid
338 (application_short_name =>'SQLGL',
339 key_flex_code =>'GL#',
340 structure_number =>l_chart,
341 validation_date =>to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'),
342 concatenated_segments =>p_to_account);
343
344 IF l_id=0 then
345 RAISE fnd_api.g_exc_error;
346 END IF;
347
348 RETURN l_id;
349
350 EXCEPTION
351 WHEN NO_DATA_FOUND THEN
352 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
353 THEN
354 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
355 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_account_id'|| p_to_account);
356 FND_MSG_PUB.Add;
357 END IF;
358 RETURN FND_API.G_MISS_NUM;
359 WHEN fnd_api.g_exc_error THEN
360 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
361 THEN
362 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
363 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_account_id'|| p_to_account);
364 FND_MSG_PUB.Add;
365 END IF;
366 RETURN FND_API.G_MISS_NUM;
367 WHEN OTHERS THEN
368 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
369 THEN
370 FND_MSG_PUB.Add_Exc_Msg
371 ( G_PKG_NAME
372 , 'To_Account'
373 );
374 END IF;
375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
376 END To_Account;
377
378 -- To_Subinventory
379
380 FUNCTION To_Subinventory
381 ( p_organization_id IN NUMBER,
382 p_to_subinventory IN VARCHAR2
383 ) RETURN VARCHAR2
384 IS
385 l_code VARCHAR2(10);
386 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
387 BEGIN
388
389 l_code := p_to_subinventory;
390 RETURN l_code;
391
392 END To_Subinventory;
393
394 -- Transaction_Type
395
396 FUNCTION Transaction_Type
397 ( p_transaction_type IN VARCHAR2
398 ) RETURN NUMBER
399 IS
400 l_id NUMBER;
401 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
402 BEGIN
403
404 IF p_transaction_type IS NULL
405 THEN
406 RETURN NULL;
407 END IF;
408
409 -- SELECT XXXX_id
410 -- INTO l_id
411 -- FROM XXXX_table
412 -- WHERE XXXX_val_column = p_transaction_type
413
414 RETURN l_id;
415
416 EXCEPTION
417
418 WHEN NO_DATA_FOUND THEN
419
420 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
421 THEN
422
423 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
424 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','transaction_type_id');
425 FND_MSG_PUB.Add;
426
427 END IF;
428
429 RETURN FND_API.G_MISS_NUM;
430
431 WHEN OTHERS THEN
432
433 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
434 THEN
435 FND_MSG_PUB.Add_Exc_Msg
436 ( G_PKG_NAME
437 , 'Transaction_Type'
438 );
439 END IF;
440
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442
443 END Transaction_Type;
444
445 -- Move_Order_Type
446
447 FUNCTION Move_Order_Type
448 ( p_move_order_type IN VARCHAR2
449 ) RETURN NUMBER
450 IS
451 l_id NUMBER;
452 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
453 BEGIN
454
455 IF p_move_order_type IS NULL
456 THEN
457 RETURN NULL;
458 END IF;
459
460 -- SELECT XXXX_id
461 -- INTO l_id
462 -- FROM XXXX_table
463 -- WHERE XXXX_val_column = p_transaction_type
464
465 RETURN l_id;
466
467 EXCEPTION
468
469 WHEN NO_DATA_FOUND THEN
470
471 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
472 THEN
473
474 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
475 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','move_order_type');
476 FND_MSG_PUB.Add;
477
478 END IF;
479
480 RETURN FND_API.G_MISS_NUM;
481
482 WHEN OTHERS THEN
483
484 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
485 THEN
486 FND_MSG_PUB.Add_Exc_Msg
487 ( G_PKG_NAME
488 , 'Move_Order_Type'
489 );
490 END IF;
491
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493
494 END Move_Order_Type;
495
496 -- From_Locator
497
498 FUNCTION From_Locator
499 ( p_organization_id IN NUMBER,
500 p_from_locator IN VARCHAR2
501 ) RETURN NUMBER
502 IS
503 l_id NUMBER;
504 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
505 BEGIN
506
510 RETURN NULL;
507 IF p_from_locator IS NULL
508 OR p_from_locator = FND_API.G_MISS_CHAR
509 THEN
511 END IF;
512
513 SELECT INVENTORY_LOCATION_ID
514 INTO l_id
515 FROM MTL_ITEM_LOCATIONS_KFV
516 WHERE ORGANIZATION_ID = p_organization_id
517 AND CONCATENATED_SEGMENTS = p_from_locator;
518
519 RETURN l_id;
520
521 EXCEPTION
522
523 WHEN NO_DATA_FOUND THEN
524
525 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
526 THEN
527
528 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
529 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','from_locator_id');
530 FND_MSG_PUB.Add;
531
532 END IF;
533
534 RETURN FND_API.G_MISS_NUM;
535
536 WHEN OTHERS THEN
537
538 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
539 THEN
540 FND_MSG_PUB.Add_Exc_Msg
541 ( G_PKG_NAME
542 , 'From_Locator'
543 );
544 END IF;
545
546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
547
548 END From_Locator;
549
550 -- Inventory_Item
551
552 FUNCTION Inventory_Item
553 ( p_organization_id IN NUMBER,
554 p_inventory_item IN VARCHAR2
555 ) RETURN NUMBER
556 IS
557 l_id NUMBER;
558 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
559 BEGIN
560
561 IF p_inventory_item IS NULL
562 OR p_inventory_item = FND_API.G_MISS_CHAR
563 THEN
564 RETURN NULL;
565 END IF;
566 /* inv_debug.message('TRO: svid item: '||p_inventory_item|| ' ' || to_char(p_organization_id)); */
567 SELECT INVENTORY_ITEM_ID
568 INTO l_id
569 FROM MTL_SYSTEM_ITEMS_KFV
570 WHERE CONCATENATED_SEGMENTS = p_inventory_item
571 AND ORGANIZATION_ID = p_organization_id;
572 RETURN l_id;
573
574 EXCEPTION
575
576 WHEN NO_DATA_FOUND THEN
577
578 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
579 THEN
580
581 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
582 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','inventory_item_id');
583 FND_MSG_PUB.Add;
584
585 END IF;
586
587 RETURN FND_API.G_MISS_NUM;
588
589 WHEN OTHERS THEN
590
591 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
592 THEN
593 FND_MSG_PUB.Add_Exc_Msg
594 ( G_PKG_NAME
595 , 'Inventory_Item'
596 );
597 END IF;
598
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600
601 END Inventory_Item;
602
603 -- Line
604
605 FUNCTION Line
606 ( p_line IN VARCHAR2
607 ) RETURN NUMBER
608 IS
609 l_id NUMBER;
610 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
611 BEGIN
612
613 IF p_line IS NULL
614 THEN
615 RETURN NULL;
616 END IF;
617
618 -- SELECT XXXX_id
619 -- INTO l_id
620 -- FROM XXXX_table
621 -- WHERE XXXX_val_column = p_line
622
623 RETURN l_id;
624
625 EXCEPTION
626
627 WHEN NO_DATA_FOUND THEN
628
629 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
630 THEN
631
632 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
633 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','line_id');
634 FND_MSG_PUB.Add;
635
636 END IF;
637
638 RETURN FND_API.G_MISS_NUM;
639
640 WHEN OTHERS THEN
641
642 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
643 THEN
644 FND_MSG_PUB.Add_Exc_Msg
645 ( G_PKG_NAME
646 , 'Line'
647 );
648 END IF;
649
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
651
652 END Line;
653
654 -- Project
655
656 FUNCTION Project
657 ( p_project IN VARCHAR2
658 ) RETURN NUMBER
659 IS
660 l_id NUMBER;
661 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
662 BEGIN
663
664 IF p_project IS NULL
665 THEN
666 RETURN NULL;
667 END IF;
668
669 -- SELECT XXXX_id
670 -- INTO l_id
671 -- FROM XXXX_table
672 -- WHERE XXXX_val_column = p_project
673
674 RETURN l_id;
675
676 EXCEPTION
677
678 WHEN NO_DATA_FOUND THEN
679
680 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
681 THEN
682
686
683 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
684 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','project_id');
685 FND_MSG_PUB.Add;
687 END IF;
688
689 RETURN FND_API.G_MISS_NUM;
690
691 WHEN OTHERS THEN
692
693 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
694 THEN
695 FND_MSG_PUB.Add_Exc_Msg
696 ( G_PKG_NAME
697 , 'Project'
698 );
699 END IF;
700
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702
703 END Project;
704
705 -- Reason
706
707 FUNCTION Reason
708 ( p_reason IN VARCHAR2
709 ) RETURN NUMBER
710 IS
711 l_id NUMBER;
712 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
713 BEGIN
714
715 IF p_reason IS NULL
716 THEN
717 RETURN NULL;
718 END IF;
719
720 -- SELECT XXXX_id
721 -- INTO l_id
722 -- FROM XXXX_table
723 -- WHERE XXXX_val_column = p_reason
724
725 RETURN l_id;
726
727 EXCEPTION
728
729 WHEN NO_DATA_FOUND THEN
730
731 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
732 THEN
733
734 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
735 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reason_id');
736 FND_MSG_PUB.Add;
737
738 END IF;
739
740 RETURN FND_API.G_MISS_NUM;
741
742 WHEN OTHERS THEN
743
744 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
745 THEN
746 FND_MSG_PUB.Add_Exc_Msg
747 ( G_PKG_NAME
748 , 'Reason'
749 );
750 END IF;
751
752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753
754 END Reason;
755
756 -- Reference
757
758 FUNCTION Reference
759 ( p_reference IN VARCHAR2
760 ) RETURN NUMBER
761 IS
762 l_id NUMBER;
763 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
764 BEGIN
765
766 IF p_reference IS NULL
767 THEN
768 RETURN NULL;
769 END IF;
770
771 -- SELECT XXXX_id
772 -- INTO l_id
773 -- FROM XXXX_table
774 -- WHERE XXXX_val_column = p_reference
775
776 RETURN l_id;
777
778 EXCEPTION
779
780 WHEN NO_DATA_FOUND THEN
781
782 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
783 THEN
784
785 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
786 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reference_id');
787 FND_MSG_PUB.Add;
788
789 END IF;
790
791 RETURN FND_API.G_MISS_NUM;
792
793 WHEN OTHERS THEN
794
795 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
796 THEN
797 FND_MSG_PUB.Add_Exc_Msg
798 ( G_PKG_NAME
799 , 'Reference'
800 );
801 END IF;
802
803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
804
805 END Reference;
806
807 -- Reference_Type
808
809 FUNCTION Reference_Type
810 ( p_reference_type IN VARCHAR2
811 ) RETURN NUMBER
812 IS
813 l_id NUMBER;
814 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
815 BEGIN
816
817 IF p_reference_type IS NULL
818 THEN
819 RETURN NULL;
820 END IF;
821
822 -- SELECT XXXX_id
823 -- INTO l_id
824 -- FROM XXXX_table
825 -- WHERE XXXX_val_column = p_reference_type
826
827 RETURN l_id;
828
829 EXCEPTION
830
831 WHEN NO_DATA_FOUND THEN
832
833 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
834 THEN
835
836 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
837 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reference_type_code');
838 FND_MSG_PUB.Add;
839
840 END IF;
841
842 RETURN FND_API.G_MISS_NUM;
843
844 WHEN OTHERS THEN
845
846 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
847 THEN
848 FND_MSG_PUB.Add_Exc_Msg
849 ( G_PKG_NAME
850 , 'Reference_Type'
851 );
852 END IF;
853
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855
856 END Reference_Type;
857
858 -- Task
859
860 FUNCTION Task
861 ( p_task IN VARCHAR2
862 ) RETURN NUMBER
863 IS
867
864 l_id NUMBER;
865 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
866 BEGIN
868 IF p_task IS NULL
869 THEN
870 RETURN NULL;
871 END IF;
872
873 -- SELECT XXXX_id
874 -- INTO l_id
875 -- FROM XXXX_table
876 -- WHERE XXXX_val_column = p_task
877
878 RETURN l_id;
879
880 EXCEPTION
881
882 WHEN NO_DATA_FOUND THEN
883
884 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
885 THEN
886
887 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
888 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','task_id');
889 FND_MSG_PUB.Add;
890
891 END IF;
892
893 RETURN FND_API.G_MISS_NUM;
894
895 WHEN OTHERS THEN
896
897 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
898 THEN
899 FND_MSG_PUB.Add_Exc_Msg
900 ( G_PKG_NAME
901 , 'Task'
902 );
903 END IF;
904
905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
906
907 END Task;
908
909 -- To_Locator
910
911 FUNCTION To_Locator
912 ( p_organization_id IN NUMBER,
913 p_to_locator IN VARCHAR2
914 ) RETURN NUMBER
915 IS
916 l_id NUMBER;
917 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
918 BEGIN
919
920 IF p_to_locator IS NULL
921 OR p_to_locator = FND_API.G_MISS_CHAR
922 THEN
923 RETURN NULL;
924 END IF;
925
926 SELECT INVENTORY_LOCATION_ID
927 INTO l_id
928 FROM MTL_ITEM_LOCATIONS_KFV
929 WHERE ORGANIZATION_ID = p_organization_id
930 AND CONCATENATED_SEGMENTS = p_to_locator;
931 RETURN l_id;
932
933 EXCEPTION
934
935 WHEN NO_DATA_FOUND THEN
936
937 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
938 THEN
939
940 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
941 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_locator_id');
942 FND_MSG_PUB.Add;
943
944 END IF;
945
946 RETURN FND_API.G_MISS_NUM;
947
948 WHEN OTHERS THEN
949
950 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
951 THEN
952 FND_MSG_PUB.Add_Exc_Msg
953 ( G_PKG_NAME
954 , 'To_Locator'
955 );
956 END IF;
957
958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
959
960 END To_Locator;
961
962 -- Transaction_Header
963
964 FUNCTION Transaction_Header
965 ( p_transaction_header IN VARCHAR2
966 ) RETURN NUMBER
967 IS
968 l_id NUMBER;
969 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
970 BEGIN
971
972 IF p_transaction_header IS NULL
973 THEN
974 RETURN NULL;
975 END IF;
976
977 -- SELECT XXXX_id
978 -- INTO l_id
979 -- FROM XXXX_table
980 -- WHERE XXXX_val_column = p_transaction_header
981
982 RETURN l_id;
983
984 EXCEPTION
985
986 WHEN NO_DATA_FOUND THEN
987
988 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
989 THEN
990
991 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
992 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','transaction_header_id');
993 FND_MSG_PUB.Add;
994
995 END IF;
996
997 RETURN FND_API.G_MISS_NUM;
998
999 WHEN OTHERS THEN
1000
1001 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1002 THEN
1003 FND_MSG_PUB.Add_Exc_Msg
1004 ( G_PKG_NAME
1005 , 'Transaction_Header'
1006 );
1007 END IF;
1008
1009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1010
1011 END Transaction_Header;
1012
1013 -- Uom
1014
1015 FUNCTION Uom
1016 ( p_uom IN VARCHAR2
1017 ) RETURN VARCHAR2
1018 IS
1019 l_code VARCHAR2(3);
1020 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1021 BEGIN
1022
1023 IF p_uom IS NULL
1024 THEN
1025 RETURN NULL;
1026 END IF;
1027
1028 -- SELECT XXXX_id
1029 -- INTO l_code
1030 -- FROM XXXX_table
1031 -- WHERE XXXX_val_column = p_uom
1032
1033 RETURN l_code;
1034
1035 EXCEPTION
1036
1037 WHEN NO_DATA_FOUND THEN
1038
1039 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1040 THEN
1041
1042 FND_MESSAGE.SET_NAME('INV','INV_VALUE_TO_ID_ERROR');
1043 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','uom_code');
1044 FND_MSG_PUB.Add;
1045
1046 END IF;
1047
1048 RETURN FND_API.G_MISS_CHAR;
1049
1050 WHEN OTHERS THEN
1051
1052 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1053 THEN
1054 FND_MSG_PUB.Add_Exc_Msg
1055 ( G_PKG_NAME
1056 , 'Uom'
1057 );
1058 END IF;
1059
1060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1061
1062 END Uom;
1063
1064 -- END GEN value_to_id
1065
1066 END INV_Value_To_Id;