1 PACKAGE BODY INV_MEANING_SEL as
2 /* $Header: INVRPTMB.pls 120.1.12010000.3 2010/03/01 07:05:36 qyou ship $ */
3
4 FUNCTION C_MFG_LOOKUP(lookup_code_val in number,lookup_type_val in varchar2) RETURN varchar2 IS
5 temp varchar2(80);
6 begin
7 if (lookup_code_val is NOT NULL) then
8 select meaning
9 into temp
10 from mfg_lookups
11 where lookup_code = lookup_code_val
12 and lookup_type = lookup_type_val;
13 return (temp);
14 else
15 return (NULL);
16 end if;
17 exception
18 when NO_DATA_FOUND then
19 return(NULL);
20 when Others then
21 return(SQLCODE);
22 end C_MFG_LOOKUP;
23
24 FUNCTION C_UNIT_MEASURE(uom_code_val in varchar2) RETURN varchar2 IS
25 ret_string varchar2(80);
26 begin
27 if (uom_code_val is NOT NULL) then
28 select unit_of_measure
29 into ret_string
30 from mtl_units_of_measure
31 where uom_code = uom_code_val;
32 return (ret_string);
33 else
34 return (NULL);
35 end if;
36 exception
37 when NO_DATA_FOUND then
38 return(NULL);
39 when Others then
40 return(SQLCODE);
41 end C_UNIT_MEASURE;
42
43 FUNCTION C_PO_UN_NUMB(un_number_val in number) RETURN varchar2 IS
44 ret_string varchar2(25);
45 begin
46 if (un_number_val is NOT NULL) then
47 select un_number
48 into ret_string
49 from po_un_numbers_tl
50 where un_number_id = un_number_val
51 and language = userenv('LANG');
52 return (ret_string);
53 else
54 return(NULL);
55 end if;
56 exception
57 when NO_DATA_FOUND then
58 return(NULL);
59 when Others then
60 return(SQLCODE);
61 end C_PO_UN_NUMB;
62
63 FUNCTION C_PO_HAZARD_CLASS(hazard_val in number) RETURN varchar2 IS
64 ret_string varchar2(40);
65 begin
66 if (hazard_val is NOT NULL) then
67 select HAZARD_CLASS
68 into ret_string
69 from po_hazard_classes_tl
70 where hazard_class_id = hazard_val
71 and language = userenv('LANG');
72 return (ret_string);
73 else
74 return(NULL);
75 end if;
76 exception
77 when NO_DATA_FOUND then
78 return(NULL);
79 when Others then
80 return(SQLCODE);
81 end C_PO_HAZARD_CLASS;
82
83
84 FUNCTION C_PER_PEOPLE(person_id_val in number) RETURN varchar2 IS
85 ret_string varchar2(240);
86 begin
87 if (person_id_val is NOT NULL) then
88 select full_name
89 into ret_string
90 from per_people_f
91 where person_id = person_id_val
92 and trunc(sysdate) >= effective_start_date(+)
96 return(NULL);
93 and trunc(sysdate) <= effective_end_date(+);
94 return (ret_string);
95 else
97 end if;
98 exception
99 when NO_DATA_FOUND then
100 return(NULL);
101 when Others then
102 return(SQLCODE);
103 end C_PER_PEOPLE;
104
105 FUNCTION C_LOOKUPS(Lookup_code_val in varchar2,lookup_type_val in varchar2) RETURN varchar2 IS
106 temp varchar2(80);
107 begin
108 if (lookup_code_val is NOT NULL) then
109 select meaning
110 into temp
111 from fnd_lookups
112 where lookup_code = lookup_code_val
113 and lookup_type = lookup_type_val;
114 return (temp);
115 else
116 return(NULL);
117 end if;
118 exception
119 when NO_DATA_FOUND then
120 return(NULL);
121 when Others then
122 return(SQLCODE);
123 end C_LOOKUPS;
124
125 FUNCTION C_PICK_RULES(pick_id_val in number) RETURN varchar2 IS
126 ret_string varchar2(30);
127 begin
128 if (pick_id_val is NOT NULL) then
129 select picking_rule_name
130 into ret_string
131 from mtl_picking_rules
132 where picking_rule_id = pick_id_val;
133 return (ret_string);
134 else
135 return(NULL);
136 end if;
137 exception
138 when NO_DATA_FOUND then
139 return(NULL);
140 when Others then
141 return(SQLCODE);
142 end C_PICK_RULES;
143
144
145 FUNCTION C_ATP_RULES(atp_id_val in number) RETURN varchar2 IS
146 ret_string varchar2(30);
147 begin
148 if (atp_id_val is NOT NULL) then
149 select rule_name
150 into ret_string
151 from mtl_atp_rules
152 where rule_id = atp_id_val;
153 return (ret_string);
154 else
155 return(NULL);
156 end if;
157 exception
158 when NO_DATA_FOUND then
159 return(NULL);
160 when Others then
161 return(SQLCODE);
162 end C_ATP_RULES;
163
164
165 FUNCTION C_ORG_NAME(org_id_val in number) RETURN VARCHAR2 is
166 ret_string varchar2(240);
167 begin
168 if (org_id_val is NOT NULL) then
169 --Perf Issue : Replaced org_organizations_definitions view.
170 select name into ret_string
171 from hr_organization_units
172 where organization_id = org_id_val;
173 return (ret_string);
174 else
175 return(NULL);
176 end if;
177 exception
178 when NO_DATA_FOUND then
179 return(NULL);
180 when Others then
181 return(SQLCODE);
182 end C_ORG_NAME;
183
184
185 FUNCTION C_RA_RULES(rule_id_val in NUMBER) RETURN varchar2 IS
186 ret_string varchar2(30);
187 begin
188 if (rule_id_val is NOT NULL) then
189 select name
190 into ret_string
191 from ra_rules
192 where rule_id = rule_id_val;
193 return (ret_string);
194 else
195 return(NULL);
196 end if;
197 exception
198 when NO_DATA_FOUND then
199 return(NULL);
200 when Others then
201 return(SQLCODE);
202 end C_RA_RULES;
203
204 FUNCTION C_RA_TERMS(term_id_val in NUMBER) RETURN varchar2 IS
205 ret_string varchar2(15);
206 begin
207 if (term_id_val is NOT NULL) then
208 select name
209 into ret_string
210 from ra_terms
211 where term_id = term_id_val;
212 return (ret_string);
213 else
214 return(NULL);
215 end if;
216 exception
217 when NO_DATA_FOUND then
218 return(NULL);
219 when Others then
220 return(SQLCODE);
221 end C_RA_TERMS;
222
223 FUNCTION C_FND_LOOKUP_VL(lookup_code_val in varchar2,lookup_type_val in varchar2) RETURN varchar2 IS
224 temp varchar2(80);
225 begin
226 if (lookup_code_val is not null) then
227 select meaning
228 into temp
229 from fnd_lookup_values_vl
230 where lookup_code = lookup_code_val
231 and lookup_type = lookup_type_val;
232 return (temp);
233 else
234 return(NULL);
235 end if;
236 exception
237 when NO_DATA_FOUND then
238 return(NULL);
239 when Others then
240 return('Error');
241 end C_FND_LOOKUP_VL;
242
243
244 FUNCTION C_PO_LOOKUP(lookup_code_val in varchar2,lookup_type_val in varchar2) RETURN varchar2 IS
245 temp varchar2(80);
246 begin
247 if (lookup_code_val is NOT NULL) then
248 select displayed_field
249 into temp
250 from po_lookup_codes
251 where lookup_code = lookup_code_val
252 and lookup_type = lookup_type_val;
253 return (temp);
254 else
255 return(NULL);
256 end if;
257 exception
258 when NO_DATA_FOUND then
259 return(NULL);
260 when Others then
261 return('Error');
262 end C_PO_LOOKUP;
263
264 FUNCTION C_FND_LOOKUP(lookup_code_val in varchar2,lookup_type_val in varchar2) RETURN varchar2 IS
265 temp varchar2(80);
266 begin
267 if (lookup_code_val is NOT NULL) then
268 select meaning
269 into temp
270 from fnd_lookup_values
274 and language = userenv('LANG');
271 where lookup_code = lookup_code_val
272 and lookup_type = lookup_type_val
273 and view_application_id = 3
275 return (temp);
276 else
277 return(NULL);
278 end if;
279 exception
280 when NO_DATA_FOUND then
281 return(NULL);
282 when Others then
283 return('Error');
284 end C_FND_LOOKUP;
285
286 FUNCTION C_LOT_LOOKUP(status_id_val in Number) RETURN varchar2 IS
287 temp varchar2(80);
288 begin
289 if (status_id_val is NOT NULL) then
290 select STATUS_CODE
291 into temp
292 from mtl_material_statuses_vl
293 where LOT_CONTROL =1
294 and ENABLED_FLAG = 1
295 and Status_Id = status_id_val;
296 return(temp);
297 else
298 return(NULL);
299 end if;
300 exception
301 when NO_DATA_FOUND then
302 return(NULL);
303 when Others then
304 return('Error');
305 end C_LOT_LOOKUP;
306
307 FUNCTION C_SERIAL_LOOKUP(status_id_val in Number) RETURN varchar2 IS
308 temp varchar2(80);
309 begin
310 if (status_id_val is NOT NULL) then
311 select STATUS_CODE
312 into temp
313 from mtl_material_statuses_vl
314 where SERIAL_CONTROL =1
315 and ENABLED_FLAG = 1
316 and Status_Id = status_id_val;
317 return(temp);
318 else
319 return(NULL);
320 end if;
321 exception
322 when NO_DATA_FOUND then
323 return(NULL);
324 when Others then
325 return('Error');
326 end C_SERIAL_LOOKUP;
327
328 FUNCTION C_UNITMEASURE(uom_code_val in Varchar2) RETURN varchar2 IS
329 temp varchar2(80);
330 begin
331 if (uom_code_val is NOT NULL) then
332 select unit_of_measure
333 into temp
334 from mtl_units_of_measure_vl
335 where uom_code = uom_code_val
336 and language = userenv('LANG');
337 return(temp);
338 else
339 return(NULL);
340 end if;
341 exception
342 when NO_DATA_FOUND then
343 return(NULL);
344 when Others then
345 return('Error');
346 end C_UNITMEASURE;
347
348 FUNCTION C_FNDCOMMON(lookup_code_val in Varchar2 , lookup_type_val in Varchar2) RETURN varchar2 IS
349 temp varchar2(80);
350 begin
351 if (lookup_code_val is NOT NULL) then
352 select meaning
353 into temp
354 from fnd_common_lookups
355 where lookup_code = lookup_code_val
356 and lookup_type = lookup_type_val;
357 return(temp);
358 else
359 return(NULL);
360 end if;
361 exception
362 when NO_DATA_FOUND then
363 return(NULL);
364 when Others then
365 return('Error');
366 end C_FNDCOMMON;
367
368
369 FUNCTION C_QTY_ON_HAND(Item_Id in Number,Org_Id in Number , Sub_Code in Varchar2, Break_Id in Number) RETURN Number IS
370 temp Number ;
371 Begin
372 if (Break_Id = 1) then
373 Select sum(primary_transaction_quantity)
374 into temp
375 from mtl_onhand_quantities_detail
376 where inventory_item_id = Item_Id
377 and organization_id = Org_Id
378 and subinventory_code = Sub_Code;
379 else
380 Select sum(primary_transaction_quantity)
381 into temp
382 from mtl_onhand_quantities_detail
383 where inventory_item_id = Item_Id
384 and organization_id = Org_Id;
385 end if;
386 return (temp);
387 exception
388 when NO_DATA_FOUND then
389 return(0);
390 end C_QTY_ON_HAND;
391
392 FUNCTION C_ITEM_DESCRIPTION(Item_Id in Number, Org_Id in Number) RETURN varchar2 IS
393 temp varchar2(240);
394 begin
395 select description
396 into temp
397 from mtl_system_items_tl
398 where INVENTORY_ITEM_ID = Item_Id
399 and ORGANIZATION_ID = Org_Id
400 and LANGUAGE = userenv('LANG');
401 return(temp);
402 exception
403 when NO_DATA_FOUND then
404 return(NULL);
405 when Others then
406 return('Error');
407 end C_ITEM_DESCRIPTION;
408
409 FUNCTION C_ITEM_REV_DESCRIPTION(Item_Id in Number, Org_Id in Number , Rev_id in NUMBER) RETURN varchar2 IS
410 temp varchar2(240);
411 begin
412 select description
413 into temp
414 from mtl_item_revisions_tl
415 where INVENTORY_ITEM_ID = Item_Id
416 and ORGANIZATION_ID = Org_Id
417 and REVISION_ID = Rev_Id
418 and LANGUAGE = userenv('LANG');
419 return(temp);
420 exception
421 when NO_DATA_FOUND then
422 return(NULL);
423 when Others then
424 return('Error');
425 end C_ITEM_REV_DESCRIPTION;
426
427 --2961986: OE lookup function for default so source type
428 FUNCTION C_OE_LOOKUP(lookup_code_val in varchar2,lookup_type_val in varchar2) RETURN varchar2 IS
429 temp varchar2(80);
430 begin
431 if (lookup_code_val is NOT NULL) then
432 select meaning
433 into temp
434 from oe_lookups
435 where lookup_code = lookup_code_val
436 and lookup_type = lookup_type_val;
437 return (temp);
438 else
439 return (NULL);
440 end if;
441 exception
442 when NO_DATA_FOUND then
443 return(NULL);
444 when Others then
445 return(SQLCODE);
446 end C_OE_LOOKUP;
447
448 FUNCTION C_COVERAGE_SCHEDULE(COVERAGE_SCHEDULE_ID NUMBER) RETURN VARCHAR2 IS
449 l_temp VARCHAR2(240) := NULL;
450 BEGIN
451 IF COVERAGE_SCHEDULE_ID IS NOT NULL THEN
452 SELECT name INTO l_temp
453 FROM OKS_COVERAGE_TEMPLTS_V
454 WHERE ID = COVERAGE_SCHEDULE_ID;
455 END IF;
456 RETURN (l_temp);
457 EXCEPTION
458 WHEN OTHERS THEN
459 return(NULL);
460 END C_COVERAGE_SCHEDULE;
461 --Bug: 1968090
462 FUNCTION C_ITEM_STATUS (status_code_val in varchar2) RETURN varchar2 IS
463 ret_string varchar2(80);
464 BEGIN
465 IF (status_code_val IS NOT NULL) THEN
466 select inventory_item_status_code_tl
467 into ret_string
468 from mtl_item_status
469 where inventory_item_status_code = status_code_val;
470 return (ret_string);
471 ELSE
472 RETURN (NULL);
473 END IF;
474 EXCEPTION
475 when NO_DATA_FOUND then
476 return(NULL);
477 when Others then
478 return(SQLCODE);
479 END C_ITEM_STATUS;
480
481 -- Bug 8762354
482 FUNCTION C_DEFAULT_MATERIAL_STATUS (status_code_val in varchar2) RETURN varchar2
483 IS
484 ret_string varchar2(80);
485 BEGIN
486 IF (STATUS_CODE_VAL IS NOT NULL) THEN
487 select status_code
488 into ret_string
489 from MTL_MATERIAL_STATUSES_TL
490 where status_id = status_code_val AND language =
491 userenv('LANG');
492 return (ret_string);
493 ELSE
494 RETURN (NULL);
495 END IF;
496 EXCEPTION
497 when NO_DATA_FOUND then
498 return(NULL);
499 when OTHERS then
500 return(SQLCODE);
501 END C_DEFAULT_MATERIAL_STATUS;
502
503 end INV_MEANING_SEL;
[Home] [Help]