[Home] [Help]
PACKAGE BODY: APPS.MRP_VALUE_TO_ID
Source
1 PACKAGE BODY MRP_Value_To_Id AS
2 /* $Header: MRPSVIDB.pls 115.1 99/07/16 12:38:14 porting ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MRP_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 BEGIN
19
20 FND_API.g_attr_tbl.DELETE;
21
22 -- START GEN attributes
23
24 -- Generator will append new attributes before end generate comment.
25 I := I + 1;
26 FND_API.g_attr_tbl(I).name := 'Key_Flex';
27 I := I + 1;
28 FND_API.g_attr_tbl(I).name := 'completion_locator';
29 I := I + 1;
30 FND_API.g_attr_tbl(I).name := 'line';
31 I := I + 1;
32 FND_API.g_attr_tbl(I).name := 'organization';
33 I := I + 1;
34 FND_API.g_attr_tbl(I).name := 'primary_item';
35 I := I + 1;
36 FND_API.g_attr_tbl(I).name := 'project';
37 I := I + 1;
38 FND_API.g_attr_tbl(I).name := 'schedule_group';
39 I := I + 1;
40 FND_API.g_attr_tbl(I).name := 'task';
41 I := I + 1;
42 FND_API.g_attr_tbl(I).name := 'wip_entity';
43 -- END GEN attributes
44
45 END Get_Attr_Tbl;
46
47 -- Prototypes for value_to_id functions.
48
49 -- START GEN value_to_id
50
51 -- Key Flex
52
53 FUNCTION Key_Flex
54 ( p_key_flex_code IN VARCHAR2
55 , p_structure_number IN NUMBER
56 , p_appl_short_name IN VARCHAR2
57 , p_segment_array IN FND_FLEX_EXT.SegmentArray
58 )
59 RETURN NUMBER
60 IS
61 l_id NUMBER;
62 l_segment_array FND_FLEX_EXT.SegmentArray;
63 BEGIN
64
65 l_segment_array := p_segment_array;
66
67 -- Convert any missing values to NULL
68
69 FOR I IN 1..l_segment_array.COUNT LOOP
70
71 IF l_segment_array(I) = FND_API.G_MISS_CHAR THEN
72 l_segment_array(I) := NULL;
73 END IF;
74
75 END LOOP;
76
77 -- Call Flex conversion routine
78
79 IF NOT FND_FLEX_EXT.get_combination_id
80 ( application_short_name => p_appl_short_name
81 , key_flex_code => p_key_flex_code
82 , structure_number => p_structure_number
83 , validation_date => NULL
84 , n_segments => l_segment_array.COUNT
85 , segments => l_segment_array
86 , combination_id => l_id
87 )
88 THEN
89
90 -- Error getting combination id.
91 -- Function has already pushed a message on the stack. Add to
92 -- the API message list.
93
94 FND_MSG_PUB.Add;
95 l_id := FND_API.G_MISS_NUM;
96
97 END IF;
98
99 RETURN l_id;
100
101 EXCEPTION
102
103 WHEN OTHERS THEN
104
105 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
106 THEN
107 FND_MSG_PUB.Add_Exc_Msg
108 ( G_PKG_NAME
109 , 'Key_Flex'
110 );
111 END IF;
112
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114
115 END Key_Flex;
116
117 -- Generator will append new prototypes before end generate comment.
118
119
120 -- Completion_Locator
121
122 FUNCTION Completion_Locator
123 ( p_completion_locator IN VARCHAR2
124 ) RETURN NUMBER
125 IS
126 l_id NUMBER;
127 BEGIN
128
129 IF p_completion_locator IS NULL
130 THEN
131 RETURN NULL;
132 END IF;
133
134 -- SELECT XXXX_id
135 -- INTO l_id
136 -- FROM XXXX_table
137 -- WHERE XXXX_val_column = p_completion_locator
138
139 RETURN l_id;
140
141 EXCEPTION
142
143 WHEN NO_DATA_FOUND THEN
144
145 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
146 THEN
147
148 FND_MESSAGE.SET_NAME('MRP','MRP_VALUE_TO_ID_ERROR');
149 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','completion_locator_id');
150 FND_MSG_PUB.Add;
151
152 END IF;
153
154 RETURN FND_API.G_MISS_NUM;
155
156 WHEN OTHERS THEN
157
158 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
159 THEN
160 FND_MSG_PUB.Add_Exc_Msg
161 ( G_PKG_NAME
162 , 'Completion_Locator'
163 );
164 END IF;
165
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167
168 END Completion_Locator;
169
170 -- Line
171
172 FUNCTION Line
173 ( p_line IN VARCHAR2
174 ) RETURN NUMBER
175 IS
176 l_id NUMBER;
177 BEGIN
178
179 IF p_line IS NULL
180 THEN
181 RETURN NULL;
182 END IF;
183
184 -- SELECT XXXX_id
185 -- INTO l_id
186 -- FROM XXXX_table
187 -- WHERE XXXX_val_column = p_line
188
189 RETURN l_id;
190
191 EXCEPTION
192
193 WHEN NO_DATA_FOUND THEN
194
195 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
196 THEN
197
198 FND_MESSAGE.SET_NAME('MRP','MRP_VALUE_TO_ID_ERROR');
199 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','line_id');
200 FND_MSG_PUB.Add;
201
202 END IF;
203
204 RETURN FND_API.G_MISS_NUM;
205
206 WHEN OTHERS THEN
207
208 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
209 THEN
210 FND_MSG_PUB.Add_Exc_Msg
211 ( G_PKG_NAME
212 , 'Line'
213 );
214 END IF;
215
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217
218 END Line;
219
220 -- Organization
221
222 FUNCTION Organization
223 ( p_organization IN VARCHAR2
224 ) RETURN NUMBER
225 IS
226 l_id NUMBER;
227 BEGIN
228
229 IF p_organization IS NULL
230 THEN
231 RETURN NULL;
232 END IF;
233
234 SELECT organization_id
235 INTO l_id
236 FROM org_organization_definitions
237 WHERE organization_code = p_organization;
238
239 RETURN l_id;
240
241 EXCEPTION
242
243 WHEN NO_DATA_FOUND THEN
244
245 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
246 THEN
247
248 FND_MESSAGE.SET_NAME('MRP','MRP_VALUE_TO_ID_ERROR');
249 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','organization_id');
250 FND_MSG_PUB.Add;
251
252 END IF;
253
254 RETURN FND_API.G_MISS_NUM;
255
256 WHEN OTHERS THEN
257
258 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
259 THEN
260 FND_MSG_PUB.Add_Exc_Msg
261 ( G_PKG_NAME
262 , 'Organization'
263 );
264 END IF;
265
266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
267
268 END Organization;
269
270 -- Primary_Item
271
272 FUNCTION Primary_Item
273 ( p_primary_item IN VARCHAR2
274 ) RETURN NUMBER
275 IS
276 l_id NUMBER;
277 BEGIN
278
279 IF p_primary_item IS NULL
280 THEN
281 RETURN NULL;
282 END IF;
283
284 -- SELECT XXXX_id
285 -- INTO l_id
286 -- FROM XXXX_table
287 -- WHERE XXXX_val_column = p_primary_item
288
289 RETURN l_id;
290
291 EXCEPTION
292
293 WHEN NO_DATA_FOUND THEN
294
295 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
296 THEN
297
298 FND_MESSAGE.SET_NAME('MRP','MRP_VALUE_TO_ID_ERROR');
299 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','primary_item_id');
300 FND_MSG_PUB.Add;
301
302 END IF;
303
304 RETURN FND_API.G_MISS_NUM;
305
306 WHEN OTHERS THEN
307
308 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
309 THEN
310 FND_MSG_PUB.Add_Exc_Msg
311 ( G_PKG_NAME
312 , 'Primary_Item'
313 );
314 END IF;
315
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317
318 END Primary_Item;
319
320 -- Project
321
322 FUNCTION Project
323 ( p_project IN VARCHAR2
324 ) RETURN NUMBER
325 IS
326 l_id NUMBER;
327 BEGIN
328
329 IF p_project IS NULL
330 THEN
331 RETURN NULL;
332 END IF;
333
334 SELECT PROJECT_ID
335 INTO l_id
336 FROM MTL_PROJECT_V
337 WHERE PROJECT_NAME = p_project;
338
339 RETURN l_id;
340
341 EXCEPTION
342
343 WHEN NO_DATA_FOUND THEN
344
345 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
346 THEN
347
348 FND_MESSAGE.SET_NAME('MRP','MRP_VALUE_TO_ID_ERROR');
349 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','project_id');
350 FND_MSG_PUB.Add;
351
352 END IF;
353
354 RETURN FND_API.G_MISS_NUM;
355
356 WHEN OTHERS THEN
357
358 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
359 THEN
360 FND_MSG_PUB.Add_Exc_Msg
361 ( G_PKG_NAME
362 , 'Project'
363 );
364 END IF;
365
366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
367
368 END Project;
369
370 -- Schedule_Group
371
372 FUNCTION Schedule_Group
373 ( p_schedule_group IN VARCHAR2
374 ) RETURN NUMBER
375 IS
376 l_id NUMBER;
377 BEGIN
378
379 IF p_schedule_group IS NULL
380 THEN
381 RETURN NULL;
382 END IF;
383
384 -- SELECT XXXX_id
385 -- INTO l_id
386 -- FROM XXXX_table
387 -- WHERE XXXX_val_column = p_schedule_group
388
389 RETURN l_id;
390
391 EXCEPTION
392
393 WHEN NO_DATA_FOUND THEN
394
395 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
396 THEN
397
398 FND_MESSAGE.SET_NAME('MRP','MRP_VALUE_TO_ID_ERROR');
399 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','schedule_group_id');
400 FND_MSG_PUB.Add;
401
402 END IF;
403
404 RETURN FND_API.G_MISS_NUM;
405
406 WHEN OTHERS THEN
407
408 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
409 THEN
410 FND_MSG_PUB.Add_Exc_Msg
411 ( G_PKG_NAME
412 , 'Schedule_Group'
413 );
414 END IF;
415
416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
417
418 END Schedule_Group;
419
420 -- Task
421
422 FUNCTION Task
423 ( p_task IN VARCHAR2
424 ) RETURN NUMBER
425 IS
426 l_id NUMBER;
427 BEGIN
428
429 IF p_task IS NULL
430 THEN
431 RETURN NULL;
432 END IF;
433
434 -- SELECT XXXX_id
435 -- INTO l_id
436 -- FROM XXXX_table
437 -- WHERE XXXX_val_column = p_task
438
439 RETURN l_id;
440
441 EXCEPTION
442
443 WHEN NO_DATA_FOUND THEN
444
445 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
446 THEN
447
448 FND_MESSAGE.SET_NAME('MRP','MRP_VALUE_TO_ID_ERROR');
449 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','task_id');
450 FND_MSG_PUB.Add;
451
452 END IF;
453
454 RETURN FND_API.G_MISS_NUM;
455
456 WHEN OTHERS THEN
457
458 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
459 THEN
460 FND_MSG_PUB.Add_Exc_Msg
461 ( G_PKG_NAME
462 , 'Task'
463 );
464 END IF;
465
466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
467
468 END Task;
469
470 -- Wip_Entity
471
472 FUNCTION Wip_Entity
473 ( p_wip_entity IN VARCHAR2
474 ) RETURN NUMBER
475 IS
476 l_id NUMBER;
477 BEGIN
478
479 IF p_wip_entity IS NULL
480 THEN
481 RETURN NULL;
482 END IF;
483
484 -- SELECT XXXX_id
485 -- INTO l_id
486 -- FROM XXXX_table
487 -- WHERE XXXX_val_column = p_wip_entity
488
489 RETURN l_id;
490
491 EXCEPTION
492
493 WHEN NO_DATA_FOUND THEN
494
495 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
496 THEN
497
498 FND_MESSAGE.SET_NAME('MRP','MRP_VALUE_TO_ID_ERROR');
499 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','wip_entity_id');
500 FND_MSG_PUB.Add;
501
502 END IF;
503
504 RETURN FND_API.G_MISS_NUM;
505
506 WHEN OTHERS THEN
507
508 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
509 THEN
510 FND_MSG_PUB.Add_Exc_Msg
511 ( G_PKG_NAME
512 , 'Wip_Entity'
513 );
514 END IF;
515
516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
517
518 END Wip_Entity;
519 -- END GEN value_to_id
520
521 END MRP_Value_To_Id;