1 PACKAGE BODY pqp_professional_body_function AS
2 -- $Header: pqgbpbfn.pkb 115.4 2003/02/14 19:19:39 tmehra noship $
3
4 g_package VARCHAR2(40) := 'pqp_professional_body_function.';
5 g_eei_info_type VARCHAR2(40) := 'PQP_PROFESSIONAL_BODY_INFO';
6 -----------------------------------------------------------------------------
7 -- GET_ORGANIZATION_INFO
8 -----------------------------------------------------------------------------
9 FUNCTION get_organization_info (p_element_type_id IN NUMBER -- Context
10 ,p_business_group_id IN NUMBER -- Context
11 ,p_organization_id OUT NOCOPY NUMBER
12 ,p_error_message OUT NOCOPY VARCHAR2
13 )
14 RETURN NUMBER
15
16 IS
17
18 l_proc VARCHAR2(80) := g_package||'get_organization_info';
19 l_ret_vlu NUMBER(2) := 0;
20
21 CURSOR csr_get_organization_info
22 IS
23 SELECT TO_NUMBER(eei.eei_information1)
24 FROM pay_element_types_f ele
25 ,pay_element_type_extra_info eei
26 ,fnd_sessions fnd
27 WHERE ele.element_type_id = p_element_type_id
28 AND eei.element_type_id = ele.element_type_id
29 AND (ele.business_group_id = p_business_group_id OR
30 ele.legislation_code IS NOT NULL)
31 AND eei.information_type = g_eei_info_type
32 AND fnd.effective_date BETWEEN ele.effective_start_date
33 AND ele.effective_end_date
34 AND fnd.session_id = USERENV('sessionid');
35
36 l_organization_id NUMBER(15);
37
38 BEGIN
39
40 hr_utility.set_location('Entering: '||l_proc, 10);
41
42 --
43 OPEN csr_get_organization_info;
44 FETCH csr_get_organization_info INTO l_organization_id;
45
46 IF csr_get_organization_info%NOTFOUND THEN
47
48 --
49 -- No row in Extra element information table
50 --
51
52 l_ret_vlu := -1;
53 p_error_message := 'There is no extra information for this element.';
54
55 ELSIF l_organization_id IS NULL THEN
56
57 --
58 -- Organization info not found
59 --
60
61 l_ret_vlu := -1;
62 p_error_message := 'There is no organization information for this element.';
63
64 END IF; -- End if of not found check...
65 CLOSE csr_get_organization_info;
66
67 p_organization_id := l_organization_id;
68
69 --
70 hr_utility.set_location('Leaving: '||l_proc, 20);
71
72 RETURN l_ret_vlu;
73 -- Added by tmehra for nocopy changes Feb'03
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 hr_utility.set_location('Entering excep:'||l_proc, 35);
78 p_error_message := SQLERRM;
79 p_organization_id := NULL;
80 raise;
81
82
83
84 END get_organization_info;
85
86 -----------------------------------------------------------------------------
87 -- GET_PB_MEM_INFO
88 -----------------------------------------------------------------------------
89 FUNCTION get_pb_mem_info (p_assignment_id IN NUMBER -- Context
90 ,p_business_group_id IN NUMBER -- Context
91 ,p_organization_id IN NUMBER
92 ,p_pay_start_dt IN DATE
93 ,p_pay_end_dt IN DATE
94 ,p_professional_body_nm OUT NOCOPY VARCHAR2
95 ,p_membership_category OUT NOCOPY VARCHAR2
96 ,p_error_message OUT NOCOPY VARCHAR2
97 )
98 RETURN NUMBER
99 IS
100
101 l_proc VARCHAR2(80) := g_package || 'get_professional_mem_info';
102 l_ret_vlu NUMBER(2) := 0;
103 l_pay_start_dt DATE := trunc(p_pay_start_dt);
104 l_pay_end_dt DATE := trunc(p_pay_end_dt);
105
106 CURSOR csr_get_person_id
107 IS
108 SELECT paa.person_id
109 FROM per_all_assignments_f paa
110 WHERE paa.assignment_id = p_assignment_id
111 AND nvl(paa.business_group_id, p_business_group_id)
112 = p_business_group_id
113 AND rownum = 1;
114
115 l_person_id NUMBER(15);
116
117 CURSOR csr_get_pb_name
118 IS
119 SELECT hou.name
120 FROM hr_all_organization_units hou
121 ,hr_organization_information hoi
122 WHERE hou.organization_id = p_organization_id
123 AND nvl(hou.business_group_id, p_business_group_id)
124 = p_business_group_id
125 AND hoi.organization_id = hou.organization_id
126 AND hoi.org_information1 = 'PB'
127 AND hoi.org_information_context = 'CLASS';
128
129 l_pb_name VARCHAR2(80);
130
131 CURSOR csr_get_pb_mem_info (c_professional_body_nm IN VARCHAR2)
132 IS
133 SELECT qua.professional_body_name
134 ,qua.membership_category
135 FROM per_qualifications qua
136 WHERE qua.person_id = l_person_id
137 AND qua.professional_body_name = c_professional_body_nm
138 AND nvl(qua.business_group_id, p_business_group_id)
139 = p_business_group_id
140 AND ((trunc(qua.start_date) BETWEEN l_pay_start_dt
141 AND l_pay_end_dt) OR
142 (trunc(qua.end_date) BETWEEN l_pay_start_dt
143 AND l_pay_end_dt) OR
144 (l_pay_start_dt BETWEEN trunc(qua.start_date)
145 AND trunc(qua.end_date)) OR
146 (l_pay_end_dt BETWEEN trunc(qua.start_date)
147 AND trunc(qua.end_date)) OR
148 (qua.end_date IS NULL AND nvl(trunc(qua.start_date), l_pay_start_dt)
149 <= l_pay_end_dt)
150 );
151
152 BEGIN
153
154 hr_utility.set_location ('Entering: '||l_proc, 10);
155 --
156 OPEN csr_get_person_id;
157 FETCH csr_get_person_id INTO l_person_id;
158
159 IF csr_get_person_id%NOTFOUND THEN
160
161 --
162 -- Assignment Not found
163 --
164
165 l_ret_vlu := -1;
166 p_error_message := 'There is no assignment information for this person.';
167 RETURN l_ret_vlu;
168
169 END IF; -- End if of person id check...
170 CLOSE csr_get_person_id;
171
172 hr_utility.set_location (l_proc, 20);
173 --
174
175 OPEN csr_get_pb_name;
176 FETCH csr_get_pb_name INTO l_pb_name;
177
178 IF csr_get_pb_name%NOTFOUND THEN
179
180 --
181 -- Organization not found
182 --
183
184 l_ret_vlu := -1;
185 p_error_message := 'There is no professional body information for this organization.';
186
187 ELSE
188
189 hr_utility.set_location (l_proc, 30);
190 --
191 OPEN csr_get_pb_mem_info (l_pb_name);
192 FETCH csr_get_pb_mem_info INTO p_professional_body_nm
193 ,p_membership_category;
194 IF csr_get_pb_mem_info%NOTFOUND THEN
195
196 --
197 -- Professional Membership Details Missing
198 --
199
200 l_ret_vlu := -2;
201 p_error_message := 'There are no professional body details for this person. ' ||
202 'Check that this is the correct professional body and ' ||
203 'that membership is up-to-date.';
204
205 ELSE
206
207 --
208 -- Check any other row exists for this professional body name
209 --
210
211 hr_utility.set_location (l_proc, 40);
212 --
213 FETCH csr_get_pb_mem_info INTO p_professional_body_nm
214 ,p_membership_category;
215 IF csr_get_pb_mem_info%FOUND THEN
216
217 --
218 -- Multiple professional body row exists for the same person within the same
219 -- payroll period
220 --
221
222 l_ret_vlu := -1;
223 p_error_message := 'A person can only belong to a particular professional body ' ||
224 'once within any given pay period.';
225
226 END IF; -- End if of pb mem multiple row check...
227
228 END IF; -- End if of pb info check...
229 CLOSE csr_get_pb_mem_info;
230
231 END IF; -- End if of organization check...
232 CLOSE csr_get_pb_name;
233
234 --
235 hr_utility.set_location ('Leaving: '||l_proc, 50);
236
237 RETURN l_ret_vlu;
238
239
240 -- Added by tmehra for nocopy changes Feb'03
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 hr_utility.set_location('Entering excep:'||l_proc, 65);
245 p_professional_body_nm := NULL;
246 p_membership_category := NULL;
247 p_error_message := SQLERRM;
248 raise;
249
250
251 END get_pb_mem_info;
252
253 -----------------------------------------------------------------------------
254 -- GET_PB_UDT_INFO
255 -----------------------------------------------------------------------------
256 FUNCTION get_pb_udt_info (p_business_group_id IN NUMBER -- Context
257 ,p_organization_id IN NUMBER
258 ,p_membership_category IN VARCHAR2
259 ,p_user_table_name OUT NOCOPY VARCHAR2
260 ,p_user_row_value OUT NOCOPY VARCHAR2
261 ,p_error_message OUT NOCOPY VARCHAR2
262 )
263 RETURN NUMBER
264 IS
265
266 l_proc VARCHAR2(80) := g_package || ' get_pb_udt_info';
267 l_ret_vlu NUMBER(2) := 0;
268
269 CURSOR csr_get_udt_id
270 IS
271 SELECT TO_NUMBER(hoi.org_information1)
272 FROM hr_all_organization_units hou
273 ,hr_organization_information hoi
274 WHERE hou.organization_id = hoi.organization_id
275 AND hou.organization_id = TO_NUMBER(p_organization_id)
276 AND nvl(hou.business_group_id, p_business_group_id)
277 = p_business_group_id
278 AND hoi.org_information_context = 'PROFESSIONAL_BODY_INFORMATION';
279
280 l_user_table_id NUMBER(15) := 0;
281
282 CURSOR csr_get_udt_row (c_user_table_id IN NUMBER)
283 IS
284 SELECT pur.row_low_range_or_name
285 ,put.user_table_name
286 FROM pay_user_rows_f pur
287 ,pay_user_tables put
288 ,fnd_sessions fnd
289 WHERE put.user_table_id = c_user_table_id
290 AND put.user_table_id = pur.user_table_id
291 AND put.range_or_match = 'M'
292 AND (put.business_group_id = p_business_group_id
293 OR put.legislation_code IS NOT NULL)
294 AND fnd.effective_date BETWEEN pur.effective_start_date
295 AND pur.effective_end_date
296 AND fnd.session_id = USERENV('sessionid');
297
298 CURSOR csr_check_udt_col (c_user_table_id IN NUMBER)
299 IS
300 SELECT NULL
301 FROM pay_user_columns puc
302 ,pay_user_tables put
303 ,pay_user_column_instances_f puci
304 ,fnd_sessions fnd
305 WHERE puc.user_table_id = put.user_table_id
306 AND put.user_table_id = c_user_table_id
307 AND puc.user_column_id = puci.user_column_id
308 AND puc.user_column_name = p_membership_category
309 AND (put.business_group_id = p_business_group_id
310 OR put.legislation_code IS NOT NULL)
311 AND (puci.business_group_id = p_business_group_id
312 OR puci.legislation_code IS NOT NULL)
313 AND puci.value IS NOT NULL
314 AND fnd.effective_date BETWEEN puci.effective_start_date
315 AND puci.effective_end_date
316 AND fnd.session_id = USERENV('sessionid');
317
318 l_dummy VARCHAR2(1);
319
320 BEGIN
321
322 hr_utility.set_location ('Entering: '||l_proc, 10);
323 --
324 OPEN csr_get_udt_id;
325 FETCH csr_get_udt_id INTO l_user_table_id;
326
327 IF csr_get_udt_id%NOTFOUND THEN
328
329 --
330 -- User table Info missing
331 --
332
333 l_ret_vlu := -1;
334 p_error_message := 'There are no subscription rate details for this professional body.';
335
336
337 ELSE
338
339 hr_utility.set_location(l_proc, 20);
340 --
341
342 OPEN csr_get_udt_row (l_user_table_id);
343 FETCH csr_get_udt_row INTO p_user_row_value
344 ,p_user_table_name;
345
346 IF csr_get_udt_row%NOTFOUND THEN
347
348 --
349 -- User Table Row Name not found
350 --
351
352 l_ret_vlu := -1;
353 p_error_message := 'There are no subscription rate details. Check that the details ' ||
354 'exist in the professional membership subscription rates table.';
355
356 ELSE
357
358 hr_utility.set_location(l_proc, 30);
359 --
360
361 FETCH csr_get_udt_row INTO p_user_row_value
362 ,p_user_table_name;
363
364 IF csr_get_udt_row%FOUND THEN
365
366 l_ret_vlu := -1;
367 p_error_message := 'The correct subscription rate could not be identified. Check ' ||
368 'that the professional membership subscription rates table does ' ||
369 'not contain multiple rows.';
370
371 ELSE
372
373 hr_utility.set_location(l_proc, 40);
374 --
375
376 OPEN csr_check_udt_col (l_user_table_id);
377 FETCH csr_check_udt_col INTO l_dummy;
378
379 IF csr_check_udt_col%NOTFOUND THEN
380
381 --
382 -- Column name mismatch or columns not found
383 --
384
385 l_ret_vlu := -1;
386 p_error_message := 'This membership category cannot be identified. Check ' ||
387 'that the membership category is included in the ' ||
388 'subscription rates table and has a value.';
389
390 END IF; -- End if of udt col check...
391 CLOSE csr_check_udt_col;
392
393 END IF; -- End if of udt mult row check...
394
395 END IF; -- End if of udt row check...
396 CLOSE csr_get_udt_row;
397
398 END IF; -- End if of udt info check...
399 CLOSE csr_get_udt_id;
400
401 --
402 hr_utility.set_location ('Leaving: '||l_proc, 50);
403
404 RETURN l_ret_vlu;
405
406 -- Added by tmehra for nocopy changes Feb'03
407
408 EXCEPTION
409 WHEN OTHERS THEN
410 hr_utility.set_location('Entering excep:'||l_proc, 65);
411 p_user_table_name := NULL;
412 p_user_row_value := NULL;
413 p_error_message := SQLERRM;
414 raise;
415
416
417 END get_pb_udt_info;
418
419 -----------------------------------------------------------------------------
420
421 END pqp_professional_body_function;
[Home] [Help]