[Home] [Help]
PACKAGE BODY: APPS.HXC_RESOURCE_PERSONAL_PREF_PKG
Source
1 package body hxc_resource_personal_pref_pkg as
2 /* $Header: hxcrppref.pkb 115.8 2004/01/28 02:50:05 avramach noship $ */
3
4 g_package varchar2(33) := ' hxc_resource_personal_pref_pkg.';
5
6 procedure manage_personal_pref
7 (p_resource_id in number
8 ,p_login_id in number
9 ,p_preference_id in number
10 ,p_preference_code in varchar2
11 ,p_business_group_id in number
12 ,p_legislation_code in varchar2
13 ,p_pref_attribute_category in varchar2
14 ,p_pref_attribute1 in varchar2
15 ,p_pref_attribute2 in varchar2
16 ,p_pref_attribute3 in varchar2
17 ,p_pref_attribute4 in varchar2
18 ,p_pref_attribute5 in varchar2
19 ,p_pref_attribute6 in varchar2
20 ,p_pref_attribute7 in varchar2
21 ,p_pref_attribute8 in varchar2
22 ,p_pref_attribute9 in varchar2
23 ,p_pref_attribute10 in varchar2
24 ,p_pref_attribute11 in varchar2
25 ,p_pref_attribute12 in varchar2
26 ,p_pref_attribute13 in varchar2
27 ,p_pref_attribute14 in varchar2
28 ,p_pref_attribute15 in varchar2
29 ,p_pref_attribute16 in varchar2
30 ,p_pref_attribute17 in varchar2
31 ,p_pref_attribute18 in varchar2
32 ,p_pref_attribute19 in varchar2
33 ,p_pref_attribute20 in varchar2
34 ,p_pref_attribute21 in varchar2
35 ,p_pref_attribute22 in varchar2
36 ,p_pref_attribute23 in varchar2
37 ,p_pref_attribute24 in varchar2
38 ,p_pref_attribute25 in varchar2
39 ,p_pref_attribute26 in varchar2
40 ,p_pref_attribute27 in varchar2
41 ,p_pref_attribute28 in varchar2
42 ,p_pref_attribute29 in varchar2
43 ,p_pref_attribute30 in varchar2
44 ) is
45
46 --l_pref_hierarchy_id number;
47 l_object_version_number number;
48 l_top_hcy_id number;
49 l_pref_hcy_id number;
50 l_pref_ovn number;
51 l_resource_rule_id number;
52 l_dummy varchar2(1);
53 l_top_ovn number;
54
55 cursor c_resource_rule(l_pref_hierarchy_id in number) is
56 select '1'
57 from hxc_resource_rules hrr
58 where hrr.eligibility_criteria_id = to_char(p_login_id)
59 and hrr.eligibility_criteria_type = 'LOGIN'
60 and hrr.resource_type = 'PERSON'
61 and hrr.pref_hierarchy_id = l_pref_hierarchy_id;
62
63 -- Bug 3380737
64 cursor c_top_hierarchy is
65 select pref_hierarchy_id, max(object_version_number)
66 from hxc_pref_hierarchies
67 where name = to_char(p_login_id)
68 and parent_pref_hierarchy_id is null
69 and type ='USER'
70 group by pref_hierarchy_id;
71
72 cursor c_pref_hierarchy (l_top_hierarchy_id in number) is
73 select pref_hierarchy_id, max(object_version_number)
74 from hxc_pref_hierarchies hph
75 where hph.name = p_preference_code
76 and hph.parent_pref_hierarchy_id = l_top_hierarchy_id
77 group by pref_hierarchy_id;
78
79
80 begin
81
82 -- Check if the top hierarchy exists
83 open c_top_hierarchy;
84 fetch c_top_hierarchy into l_top_hcy_id,l_top_ovn;
85 if c_top_hierarchy%notfound then
86
87 -- Create the top hierarchy with the name equals to login_id
88 l_top_ovn := null;
89 hxc_pref_hierarchies_api.create_pref_hierarchies
90 (p_type => 'USER'
91 ,p_name => to_char(p_login_id)
92 ,p_business_group_id => p_business_group_id
93 ,p_legislation_code => p_legislation_code
94 ,p_parent_pref_hierarchy_id => null
95 ,p_edit_allowed => 'Y'
96 ,p_displayed => 'Y'
97 ,p_pref_definition_id => null
98 ,p_attribute_category => null
99 ,p_attribute1 => null
100 ,p_attribute2 => null
101 ,p_attribute3 => null
102 ,p_attribute4 => null
103 ,p_attribute5 => null
104 ,p_attribute6 => null
105 ,p_attribute7 => null
106 ,p_attribute8 => null
107 ,p_attribute9 => null
108 ,p_attribute10 => null
109 ,p_attribute11 => null
110 ,p_attribute12 => null
111 ,p_attribute13 => null
112 ,p_attribute14 => null
113 ,p_attribute15 => null
114 ,p_attribute16 => null
115 ,p_attribute17 => null
116 ,p_attribute18 => null
117 ,p_attribute19 => null
118 ,p_attribute20 => null
119 ,p_attribute21 => null
120 ,p_attribute22 => null
121 ,p_attribute23 => null
122 ,p_attribute24 => null
123 ,p_attribute25 => null
124 ,p_attribute26 => null
125 ,p_attribute27 => null
126 ,p_attribute28 => null
127 ,p_attribute29 => null
128 ,p_attribute30 => null
129 ,p_orig_pref_hierarchy_id => null
130 ,p_orig_parent_hierarchy_id => null
131 ,p_pref_hierarchy_id => l_top_hcy_id
132 ,p_object_version_number => l_top_ovn);
133
134 end if;
135 close c_top_hierarchy;
136
137 -- Check if the preference hierarchy exist
138 open c_pref_hierarchy(l_top_hcy_id);
139 fetch c_pref_hierarchy into l_pref_hcy_id,l_pref_ovn;
140 if c_pref_hierarchy%notfound then
141
142 -- Create the pef hierarchy with the name equals to preference_code
143 l_object_version_number := null;
144 hxc_pref_hierarchies_api.create_pref_hierarchies
145 (p_type => 'USER'
146 ,p_name => p_preference_code
147 ,p_business_group_id => p_business_group_id
148 ,p_legislation_code => p_legislation_code
149 ,p_parent_pref_hierarchy_id => l_top_hcy_id
150 ,p_edit_allowed => 'Y'
151 ,p_displayed => 'Y'
152 ,p_pref_definition_id => p_preference_id
153 ,p_attribute_category => p_pref_attribute_category
154 ,p_attribute1 => p_pref_attribute1
155 ,p_attribute2 => p_pref_attribute2
156 ,p_attribute3 => p_pref_attribute3
157 ,p_attribute4 => p_pref_attribute4
158 ,p_attribute5 => p_pref_attribute5
159 ,p_attribute6 => p_pref_attribute6
160 ,p_attribute7 => p_pref_attribute7
161 ,p_attribute8 => p_pref_attribute8
162 ,p_attribute9 => p_pref_attribute9
163 ,p_attribute10 => p_pref_attribute10
164 ,p_attribute11 => p_pref_attribute11
165 ,p_attribute12 => p_pref_attribute12
166 ,p_attribute13 => p_pref_attribute13
167 ,p_attribute14 => p_pref_attribute14
168 ,p_attribute15 => p_pref_attribute15
169 ,p_attribute16 => p_pref_attribute16
170 ,p_attribute17 => p_pref_attribute17
171 ,p_attribute18 => p_pref_attribute18
172 ,p_attribute19 => p_pref_attribute19
173 ,p_attribute20 => p_pref_attribute20
174 ,p_attribute21 => p_pref_attribute21
175 ,p_attribute22 => p_pref_attribute22
176 ,p_attribute23 => p_pref_attribute23
177 ,p_attribute24 => p_pref_attribute24
178 ,p_attribute25 => p_pref_attribute25
179 ,p_attribute26 => p_pref_attribute26
180 ,p_attribute27 => p_pref_attribute27
181 ,p_attribute28 => p_pref_attribute28
182 ,p_attribute29 => p_pref_attribute29
183 ,p_attribute30 => p_pref_attribute30
184 ,p_orig_pref_hierarchy_id => null
185 ,p_orig_parent_hierarchy_id => null
186 ,p_pref_hierarchy_id => l_pref_hcy_id
187 ,p_object_version_number => l_object_version_number);
188 else
189 -- Update the preference hierarchy
190 l_object_version_number := l_pref_ovn;
191 hxc_pref_hierarchies_api.update_pref_hierarchies
192 (p_effective_date => sysdate
193 ,p_type => 'USER'
194 ,p_name => p_preference_code
195 ,p_business_group_id => p_business_group_id
196 ,p_legislation_code => p_legislation_code
197 ,p_parent_pref_hierarchy_id => l_top_hcy_id
198 ,p_edit_allowed => 'Y'
199 ,p_displayed => 'Y'
200 ,p_pref_definition_id => p_preference_id
201 ,p_attribute_category => p_pref_attribute_category
202 ,p_attribute1 => p_pref_attribute1
203 ,p_attribute2 => p_pref_attribute2
204 ,p_attribute3 => p_pref_attribute3
205 ,p_attribute4 => p_pref_attribute4
206 ,p_attribute5 => p_pref_attribute5
207 ,p_attribute6 => p_pref_attribute6
208 ,p_attribute7 => p_pref_attribute7
209 ,p_attribute8 => p_pref_attribute8
210 ,p_attribute9 => p_pref_attribute9
211 ,p_attribute10 => p_pref_attribute10
212 ,p_attribute11 => p_pref_attribute11
213 ,p_attribute12 => p_pref_attribute12
214 ,p_attribute13 => p_pref_attribute13
215 ,p_attribute14 => p_pref_attribute14
216 ,p_attribute15 => p_pref_attribute15
217 ,p_attribute16 => p_pref_attribute16
218 ,p_attribute17 => p_pref_attribute17
219 ,p_attribute18 => p_pref_attribute18
220 ,p_attribute19 => p_pref_attribute19
221 ,p_attribute20 => p_pref_attribute20
222 ,p_attribute21 => p_pref_attribute21
223 ,p_attribute22 => p_pref_attribute22
224 ,p_attribute23 => p_pref_attribute23
225 ,p_attribute24 => p_pref_attribute24
226 ,p_attribute25 => p_pref_attribute25
227 ,p_attribute26 => p_pref_attribute26
228 ,p_attribute27 => p_pref_attribute27
229 ,p_attribute28 => p_pref_attribute28
230 ,p_attribute29 => p_pref_attribute29
231 ,p_attribute30 => p_pref_attribute30
232 ,p_orig_pref_hierarchy_id => null
233 ,p_orig_parent_hierarchy_id => null
234 ,p_pref_hierarchy_id => l_pref_hcy_id
235 ,p_object_version_number => l_object_version_number);
236
237 end if;
238 close c_pref_hierarchy;
239
240 -- Bug 3380737
241 -- Update the Top_most_Parent to reflect that the child preference has been created/modified.
242 hxc_pref_hierarchies_api.update_pref_hierarchies
243 (p_effective_date => sysdate
244 ,p_type => 'USER'
245 ,p_name => to_char(p_login_id)
246 ,p_business_group_id => p_business_group_id
247 ,p_legislation_code => p_legislation_code
248 ,p_parent_pref_hierarchy_id => null
249 ,p_edit_allowed => 'Y'
250 ,p_displayed => 'Y'
251 ,p_pref_definition_id => null
252 ,p_attribute_category => null
253 ,p_attribute1 => null
254 ,p_attribute2 => null
255 ,p_attribute3 => null
256 ,p_attribute4 => null
257 ,p_attribute5 => null
258 ,p_attribute6 => null
259 ,p_attribute7 => null
260 ,p_attribute8 => null
261 ,p_attribute9 => null
262 ,p_attribute10 => null
263 ,p_attribute11 => null
264 ,p_attribute12 => null
265 ,p_attribute13 => null
266 ,p_attribute14 => null
267 ,p_attribute15 => null
268 ,p_attribute16 => null
269 ,p_attribute17 => null
270 ,p_attribute18 => null
271 ,p_attribute19 => null
272 ,p_attribute20 => null
273 ,p_attribute21 => null
274 ,p_attribute22 => null
275 ,p_attribute23 => null
276 ,p_attribute24 => null
277 ,p_attribute25 => null
278 ,p_attribute26 => null
279 ,p_attribute27 => null
280 ,p_attribute28 => null
281 ,p_attribute29 => null
282 ,p_attribute30 => null
283 ,p_orig_pref_hierarchy_id => null
284 ,p_orig_parent_hierarchy_id => null
285 ,p_pref_hierarchy_id => l_top_hcy_id
286 ,p_object_version_number => l_top_ovn);
287
288 -- Check if the Rule exist
289 open c_resource_rule(l_top_hcy_id);
290 fetch c_resource_rule into l_dummy;
291 if c_resource_rule%notfound then
292
293 l_object_version_number := null;
294 -- Create the rule with the name equals to resource id
295 hxc_resource_rules_api.create_resource_rules
296 (p_name => to_char(p_login_id),
297 p_business_group_id => p_business_group_id,
298 p_legislation_code => p_legislation_code,
299 p_eligibility_criteria_type => 'LOGIN',
300 p_eligibility_criteria_id => to_char(p_login_id),
301 p_pref_hierarchy_id => l_top_hcy_id,
302 p_rule_evaluation_order => 0,
303 p_resource_type => 'PERSON',
304 p_start_date => sysdate,
305 p_end_date => null,
306 p_resource_rule_id => l_resource_rule_id,
307 p_object_version_number => l_object_version_number);
308
309 end if;
310 close c_resource_rule;
311
312 end manage_personal_pref;
313
314 end hxc_resource_personal_pref_pkg;