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)
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)
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)
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:
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
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
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: