[Home] [Help]
PACKAGE BODY: APPS.WIP_PREFERENCES_PKG
Source
1 PACKAGE BODY WIP_PREFERENCES_PKG AS
2 /* $Header: wipprefb.pls 120.3 2006/01/04 17:34 yulin noship $ */
3
4 --
5 -- This function returns attribute_code from wip_preference_values
6 -- for single value preferences based on resp_key, org_id, dept_id.
7 -- For multiple value preferences, it returns "ENTERED" or "INHERIT" based on setup.
8 --
9 function get_preference_value_code
10 (p_preference_id number,
11 p_resp_key varchar2 default null,
12 p_org_id number default null,
13 p_dept_id number default null) return varchar2 is
14
15 l_level_code number := 0;
16 l_val_code varchar2(30);
17 l_return_val varchar2(240);
18 l_pref_type NUMBER;
19 l_multi_val_cnt NUMBER := 0;
20
21 cursor pref_type(cl_pref_id NUMBER) is
22 select preference_type
23 from wip_preference_definitions
24 where preference_id = cl_pref_id;
25
26 cursor single_value (cl_resp_key VARCHAR2,
27 cl_org_id NUMBER,
28 cl_dept_id NUMBER) is
29 select decode( (select count(v.attribute_value_code)
30 from wip_preference_values v,
31 wip_preference_levels l
32 where v.PREFERENCE_ID = p_preference_id
33 and v.LEVEL_ID = l.LEVEL_ID
34 and nvl(l.resp_key,'NULL') = nvl(cl_resp_key, 'NULL')
35 and nvl(l.organization_id, -99) = nvl(cl_org_id, -99)
36 and nvl(l.department_id, -99) = nvl(cl_dept_id, -99)
37 ),
38 0, 'INHERIT',
39 1, (select v.attribute_value_code
40 from wip_preference_values v,
41 wip_preference_levels l
42 where v.PREFERENCE_ID = p_preference_id
43 and v.LEVEL_ID = l.LEVEL_ID
44 and nvl(l.resp_key,'NULL') = nvl(cl_resp_key, 'NULL')
45 and nvl(l.organization_id, -99) = nvl(cl_org_id, -99)
46 and nvl(l.department_id, -99) = nvl(cl_dept_id, -99)),
47 'INHERIT') AS single_value_code
48 from dual;
49 cursor multi_value (cl_resp_key VARCHAR2,
50 cl_org_id NUMBER,
51 cl_dept_id NUMBER) is
52 select count(v.attribute_value_code) as multi_value_count
53 from wip_preference_values v,
54 wip_preference_levels l
55 where v.PREFERENCE_ID = p_preference_id
56 and v.LEVEL_ID = l.LEVEL_ID
57 and nvl(l.resp_key,'NULL') = nvl(cl_resp_key, 'NULL')
58 and nvl(l.organization_id, -99) = nvl(cl_org_id, -99)
59 and nvl(l.department_id, -99) = nvl(cl_dept_id, -99);
60
61 begin
62 if p_dept_id is not null then
63 l_level_code := 3;
64 elsif p_org_id is not null then
65 l_level_code := 2;
66 elsif p_resp_key is not null then
67 l_level_code := 1;
68 else
69 l_level_code := 0;
70 end if;
71
72 for c_pref_type in pref_type(p_preference_id) loop
73 l_pref_type := c_pref_type.preference_type;
74 end loop;
75
76 if (l_pref_type = 1) then --single_level_preference
77 for c_single_value in single_value(p_resp_key, p_org_id, p_dept_id) loop
78 l_return_val := c_single_value.single_value_code;
79 end loop;
80 elsif(l_pref_type = 2) then --multi value preference
81 for c_multi_val in multi_value(p_resp_key, p_org_id, p_dept_id) loop
82 l_multi_val_cnt := c_multi_val.multi_value_count;
83 end loop;
84 if(l_multi_val_cnt > 0) then
85 l_return_val := 'ENTERED';
86 else
87 l_return_val := 'INHERIT';
88 end if;
89 end if;
90
91 if( l_return_val = 'INHERIT' and l_level_code = 0 ) then
92 l_return_val := 'ENTERED';
93 end if;
94
95 return l_return_val;
96 end;
97
98 /*function get_preference_value_code
99 (p_preference_id number,
100 p_resp_key varchar2 default null,
101 p_org_id number default null,
102 p_dept_id number default null) return varchar2 is
103
104 l_level_code number := 0;
105
106 cursor get_attr_code
107 (cl_level_code number) is
108 select decode(wip_preferences_pkg.get_row_count
109 (p_preference_id,
110 cl_level_code,
111 p_resp_key ,
112 p_org_id ,
113 p_dept_id),
114 1, -- single value
115 v.attribute_value_code,
116 0, -- no setup at this level
117 decode(cl_level_code, 0, null, 'INHERIT'),
118 'ENTERED')
119 from wip_preference_values v;
120
121 l_val_code varchar2(30);
122 l_return_val varchar2(240);
123
124 begin
125
126 if p_dept_id is not null then
127 l_level_code := 3;
128 elsif p_org_id is not null then
129 l_level_code := 2;
130 elsif p_resp_key is not null then
131 l_level_code := 1;
132 else
133 l_level_code := 0;
134 end if;
135
136 open get_attr_code(l_level_code);
137 fetch get_attr_code into l_return_val;
138 if get_attr_code%notfound then
139 close get_attr_code;
140 raise no_data_found;
141 end if;
142 close get_attr_code;
143 return l_return_val;
144 end;
145 */
146
147 --
148 -- This function returns attribute value based on attribute_value_code.
149 --
150 function get_preference_value
151 (p_preference_id number,
152 p_resp_key varchar2 default null,
153 p_org_id number default null,
154 p_dept_id number default null) return varchar2 is
155
156 l_return_val varchar2(240);
157 l_value_code varchar2(240);
158
159 cursor lookup_meaning (cl_value_code number) is
160 select ml.meaning
161 from mfg_lookups ml
162 where ml.lookup_code = to_number(cl_value_code) and
163 ml.lookup_type = (select wp.preference_value_lookup_type
164 from wip_preference_definitions wp
165 where wp.preference_id = p_preference_id);
166 begin
167
168 l_value_code := get_preference_value_code (p_preference_id,
169 p_resp_key, p_org_id, p_dept_id);
170 if l_value_code = 'INHERIT' then
171 return fnd_message.get_string('WIP', 'WIP_PREFERENCE_INHERIT');
172 elsif l_value_code = 'ENTERED' then
173 return fnd_message.get_string('WIP', 'WIP_PREFERENCE_ENTERED');
174 else
175 open lookup_meaning (l_value_code);
176 fetch lookup_meaning into l_return_val;
177 if lookup_meaning%NOTFOUND then
178 close lookup_meaning;
179 raise no_data_found;
180 end if;
181 close lookup_meaning;
182 return l_return_val;
183 end if;
184 end;
185
186
187 --
188 -- Return the number of preference setups at specified level
189 --
190 function get_row_count
191 (p_pref_id number,
192 p_level_code number,
193 p_resp_key varchar2,
194 p_org_id number,
195 p_dept_id number) return number is
196
197 cursor default_level_ct is
198 select count(v.preference_value_id)
199 from wip_preference_values v,
200 wip_preference_levels l
201 where l.level_id = v.level_id and
202 l.level_code = 0 and
203 v.preference_id = p_pref_id;
204
205 cursor resp_level_ct is
206 select count(v.preference_value_id)
207 from wip_preference_values v,
208 wip_preference_levels l
209 where l.level_id = v.level_id and
210 l.level_code = 1 and
211 l.resp_key = p_resp_key and
212 v.preference_id = p_pref_id;
213
214 cursor org_level_ct is
215 select count(v.preference_value_id)
216 from wip_preference_values v,
217 wip_preference_levels l
218 where l.level_id = v.level_id and
219 l.level_code = 2 and
220 l.resp_key = p_resp_key and
221 l.organization_id = p_org_id and
222 v.preference_id = p_pref_id;
223
224 cursor dept_level_ct is
225 select count(v.preference_value_id)
226 from wip_preference_values v,
227 wip_preference_levels l
228 where l.level_id = v.level_id and
229 l.level_code = 3 and
230 l.resp_key = p_resp_key and
231 l.organization_id = p_org_id and
232 l.department_id = p_dept_id and
233 v.preference_id = p_pref_id;
234
235 l_count number := 0;
236 invalid_level exception;
237
238 begin
239 if p_level_code = 0 then
240 open default_level_ct;
241 fetch default_level_ct into l_count;
242 if default_level_ct%notfound then
243 close default_level_ct;
244 raise no_data_found;
245 end if;
246 close default_level_ct;
247 elsif p_level_code = 1 then
248 open resp_level_ct;
249 fetch resp_level_ct into l_count;
250 if resp_level_ct%notfound then
251 close resp_level_ct;
252 raise no_data_found;
253 end if;
254 close resp_level_ct;
255 elsif p_level_code = 2 then
256 open org_level_ct;
257 fetch org_level_ct into l_count;
258 if org_level_ct%notfound then
259 close org_level_ct;
260 raise no_data_found;
261 end if;
262 close org_level_ct;
263 elsif p_level_code = 3 then
264 open dept_level_ct;
265 fetch dept_level_ct into l_count;
266 if dept_level_ct%notfound then
267 close dept_level_ct;
268 raise no_data_found;
269 end if;
270 close dept_level_ct;
271 else
272 raise invalid_level;
273 end if;
274 return l_count;
275
276 exception
277 when invalid_level then
278 null;
279 end;
280
281 --
282 -- The function calculates the result preference value
283 --
284 function get_result_value_code
285 (p_preference_id number,
286 p_resp_key varchar2 default null,
287 p_org_id number default null,
288 p_dept_id number default null) return varchar2 is
289
290 l_default_val varchar2(240);
291 l_resp_val varchar2(240);
292 l_org_val varchar2(240);
293 l_dept_val varchar2(240);
294
295 begin
296
297 l_default_val := get_preference_value_code (p_preference_id);
298 l_resp_val := get_preference_value_code (p_preference_id, p_resp_key);
299 l_org_val := get_preference_value_code (p_preference_id, p_resp_key, p_org_id);
300 l_dept_val := get_preference_value_code (p_preference_id, p_resp_key, p_org_id, p_dept_id);
301
302 if l_dept_val = 'INHERIT' then
303 if l_org_val = 'INHERIT' then
304 if l_resp_val = 'INHERIT' then
305 return l_default_val;
306 else
307 return l_resp_val;
308 end if;
309 else
310 return l_org_val;
311 end if;
312 else
313 return l_dept_val;
314 end if;
315 end;
316
317 --
318 -- The function calculates the result preference value
319 --
320 /*function get_result_value
321 (p_preference_id number,
322 p_resp_key varchar2 default null,
323 p_org_id number default null,
324 p_dept_id number default null) return varchar2 is
325
326 l_default_val varchar2(240);
327 l_resp_val varchar2(240);
328 l_org_val varchar2(240);
329 l_dept_val varchar2(240);
330
331 begin
332
333 l_default_val := get_preference_value (p_preference_id);
334 l_resp_val := get_preference_value (p_preference_id, p_resp_key);
335 l_org_val := get_preference_value (p_preference_id, p_resp_key, p_org_id);
336 l_dept_val := get_preference_value (p_preference_id, p_resp_key, p_org_id, p_dept_id);
337
338 if l_dept_val = 'INHERIT' then
339 if l_org_val = 'INHERIT' then
340 if l_resp_val = 'INHERIT' then
341 return l_default_val;
342 else
343 return l_resp_val;
344 end if;
345 else
346 return l_org_val;
347 end if;
348 else
349 return l_dept_val;
350 end if;
351 end;
352 */
353 function get_result_value
354 (p_preference_id number,
355 p_resp_key varchar2 default null,
356 p_org_id number default null,
357 p_dept_id number default null) return varchar2 is
358
359 l_default_val varchar2(240);
360 l_resp_val varchar2(240);
361 l_org_val varchar2(240);
362 l_dept_val varchar2(240);
363
364 l_default_val_code varchar2(240);
365 l_resp_val_code varchar2(240);
366 l_org_val_code varchar2(240);
367 l_dept_val_code varchar2(240);
368
369
370 begin
371
372 l_default_val := get_preference_value (p_preference_id);
373 l_resp_val := get_preference_value (p_preference_id, p_resp_key);
374 l_org_val := get_preference_value (p_preference_id, p_resp_key, p_org_id);
375 l_dept_val := get_preference_value (p_preference_id, p_resp_key, p_org_id, p_dept_id);
376
377 l_default_val_code := get_preference_value_code (p_preference_id);
378 l_resp_val_code := get_preference_value_code (p_preference_id, p_resp_key);
379 l_org_val_code := get_preference_value_code (p_preference_id, p_resp_key, p_org_id);
380 l_dept_val_code := get_preference_value_code (p_preference_id, p_resp_key, p_org_id, p_dept_id);
381
382 if l_dept_val_code = 'INHERIT' then
383 if l_org_val_code = 'INHERIT' then
384 if l_resp_val_code = 'INHERIT' then
385 return l_default_val;
386 else
387 return l_resp_val;
388 end if;
389 else
390 return l_org_val;
391 end if;
392 else
393 return l_dept_val;
394 end if;
395 end;
396
397 --
398 -- The function returns the inherit flag for a preference at specified level
399 --
400 function get_inherit_flag_value
401 (p_level_id number,
402 p_level_code number) return number is
403
404 cursor get_row is
405 select level_code
406 from wip_preference_levels
407 where level_id = p_level_id;
408
409 l_level_code number;
410
411 begin
412 -- if at default value (no parent) or parent levels have no values
413 -- return not inherited
414 if p_level_code = 0 or p_level_id is null then
415 return WIP_CONSTANTS.PREF_NOT_INHERITED;
416 end if;
417
418 open get_row;
419 fetch get_row into l_level_code;
420
421 if l_level_code < p_level_code then
422 close get_row;
423 return WIP_CONSTANTS.PREF_INHERITED;
424 else
425 close get_row;
426 return WIP_CONSTANTS.PREF_NOT_INHERITED;
427 end if;
428 end;
429
430
431 --
432 -- The function calculates the preference level_id based on given resp_key,
433 -- org_id and dept_id
434 --
435 function get_preference_level_id
436 (p_preference_id number,
437 p_resp_key varchar2,
438 p_organization_id number,
439 p_department_id number) return number is
440
441
442 begin
443 -- use a cursor to get the level_id
444 null;
445 end;
446
447 function get_level (p_level_code number) return varchar2 is
448
449 level varchar2(30);
450
451 cursor l is
452 select l.meaning levelCode
453 from mfg_lookups l
454 where l.lookup_type = 'WIP_WS_PREF_LEVELS' and
455 l.lookup_code = p_level_code;
456
457 begin
458 open l;
459 fetch l into level;
460
461 if (l%notfound) then
462 close l;
463 raise no_data_found;
464 end if;
465 close l;
466 return level;
467 end;
468
469 function get_responsibility (p_resp_key varchar2) return varchar2 is
470
471 resp varchar2(240);
472
473 cursor r is
474 select r.responsibility_name responsibility
475 from fnd_responsibility_vl r
476 where r.responsibility_key = p_resp_key;
477
478 begin
479 open r;
480 fetch r into resp;
481
482 if (r%notfound) then
483 close r;
484 raise no_data_found;
485 end if;
486 close r;
487 return resp;
488 end;
489
490 function get_organization (p_org_id number) return varchar2 is
491
492 org varchar(10);
493
494 cursor o is
495 select mp.organization_code organization
496 from mtl_parameters mp
497 where mp.organization_id = p_org_id;
498
499 begin
500 open o;
501 fetch o into org;
502
503 if (o%notfound) then
504 close o;
505 raise no_data_found;
506 end if;
507 close o;
508 return org;
509 end;
510
511 function get_department (p_dept_id number) return varchar2 is
512
513 dept varchar2(240);
514
515 cursor d is
516 select bd.department_code department
517 from bom_departments bd
518 where bd.department_id = p_dept_id;
519 begin
520 open d;
521 fetch d into dept;
522
523 if (d%notfound) then
524 close d;
525 raise no_data_found;
526 end if;
527 close d;
528 return dept;
529 end;
530
531 END WIP_PREFERENCES_PKG;