[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_UPGRADE_PKG
Source
1 PACKAGE BODY pay_gb_upgrade_pkg AS
2 /* $Header: pygbgupd.pkb 120.2.12000000.2 2007/02/20 09:35:58 npershad noship $ */
3 -- |-------------------------------------------------------------------|
4 -- |---------------------< upg_disability_status >---------------------|
5 -- |-------------------------------------------------------------------|
6
7 g_package CONSTANT VARCHAR2(30) := 'pay_gb_upgrade_pkg.';
8 l_count number := 1 ;
9
10 procedure qualify_disability_status(p_person_id in number,
11 p_qualifier out nocopy varchar2)
12 is
13 c_proc constant varchar2(61) := g_package || 'qualify_disability_status';
14 l_exists varchar2(1) := 'N';
15
16 cursor csr_exists
17 is
18 select 'Y'
19 from dual
20 where exists
21 (select null
22 from per_all_people_f papf
23 where papf.person_id = p_person_id
24 and papf.registered_disabled_flag is not null)
25 or
26 exists (select null
27 from per_disabilities_f pdf,
28 per_all_people_f papf
29 where pdf.person_id =papf.person_id
30 and pdf.category not in ('Y','N','ND')
31 and papf.person_id = p_person_id);
32 begin
33 hr_utility.set_location('Entering: ' || c_proc, 10);
34 --
35 open csr_exists;
36 fetch csr_exists into l_exists;
37
38 if l_exists = 'Y' then
39 p_qualifier := 'Y';
40 else
41 p_qualifier := 'N';
42 end if;
43
44 close csr_exists;
45 --
46 hr_utility.trace(p_person_id || ' : ' || p_qualifier);
47 hr_utility.set_location('Leaving: ' || c_proc, 100);
48
49 end qualify_disability_status;
50
51 PROCEDURE upg_disability_status(p_person_id in number) is
52
53 --Get the person details having Disability recorded on person form.
54 cursor csr_person_details
55 is
56 select nvl(decode(papf.registered_disabled_flag,'Y','Yes','F','Yes - Fully Disabled','P','Yes - Partially Disabled','N','No',null),' ') old_category,
57 nvl(decode(papf.registered_disabled_flag, 'Y','Yes','F','Yes','P','Yes','N','No',null),' ') new_category,
58 nvl(decode(papf.registered_disabled_flag, 'Y','Y','F','Y','P','Y','N','N',null),' ') category,
59 nvl(papf.national_identifier,' ') ni_no,
60 papf.effective_start_date effective_start_date,
61 papf.effective_end_date effective_end_date,
62 papf.object_version_number,
63 nvl(papf.employee_number,' ') employee_number ,
64 papf.person_id person_id,
65 nvl(papf.full_name,' ') full_name
66 from per_all_people_f papf
67 where papf.person_id = p_person_id
68 and papf.registered_disabled_flag is not null
69 order by papf.person_id,effective_start_date;
70
71 --Fetches the disability records if any.
72 cursor csr_disability_details
73 is
74 select pdf.disability_id,
75 pdf.object_version_number
76 from per_disabilities_f pdf
77 where pdf.person_id = p_person_id
78 order by pdf.effective_start_date desc;
79
80 cursor csr_disability_det
81 is
82 select pdf.disability_id,
83 pdf.object_version_number
84 from per_disabilities_f pdf
85 where pdf.person_id = p_person_id
86 order by pdf.effective_start_date desc;
87
88 --Fetches the disability records recorded with Non-GB specific categories.
89 cursor csr_get_disability(p_person_id in number)
90 is
91 select distinct papf.person_id person_id,
92 nvl(papf.full_name,' ') full_name,
93 pdf.disability_id disability_id,
94 nvl(papf.national_identifier,' ') ni_no,
95 nvl(pdf.category,' ') category,
96 nvl(pdf.quota_fte,'') quota_fte,
97 nvl(pdf.degree,'') degree,
98 pdf.effective_start_date effective_start_date,
99 pdf.effective_end_date effective_end_date,
100 pdf.object_version_number object_version_number
101 from per_disabilities_f pdf,
102 per_all_people_f papf
103 where pdf.person_id =papf.person_id
104 and (pdf.category not in ('Y','N','ND'))
105 and papf.person_id = p_person_id
106 order by papf.person_id;
107
108 l_object_version_number number;
109 l_effective_start_date date;
110 l_effective_end_date date;
111 l_full_name varchar2(240);
112 l_comment_id number;
113 l_name_combination_warning boolean;
114 l_assign_payroll_warning boolean;
115 l_orig_hire_warning boolean;
116 l_disability_id number;
117 v_disability_id number;
118 v_object_version_number number;
119 v_disability_id_new number;
120 v_object_version_number_new number;
121 l_proc VARCHAR2(50) := g_package || 'upg_disability_status';
122 l_category varchar2(30);
123
124 v_dis_det csr_get_disability%rowtype;
125 begin
126
127 l_category :='X';
128 hr_utility.set_location('Entering ' || l_proc,10);
129
130 open csr_disability_det;
131 fetch csr_disability_det into v_disability_id,v_object_version_number;
132 if csr_disability_det%notfound then
133
134 for v_csr_details in csr_person_details
135 loop
136 hr_utility.set_location('Entering ' || l_proc,20);
137 if l_count = 1 then
138 fnd_file.put_line(FND_FILE.OUTPUT,'---------------------------------------------------------------------------------------------------------------------------------------');
139 fnd_file.put_line(FND_FILE.OUTPUT,' List of employees ');
140 fnd_file.put_line(FND_FILE.OUTPUT,'---------------------------------------------------------------------------------------------------------------------------------------');
141 fnd_file.put_line(FND_FILE.OUTPUT,' ');
142 fnd_file.put_line(FND_FILE.OUTPUT, rpad('Person ID',10)||' '||rpad('Full Name',30)||' '||rpad('NI Number',13)
143 ||' '||rpad('Old Category',25)||' '||rpad('New Category',12)||' '||rpad('Effective Start Date',20)||' '||rpad('Effective End Date',20));
144 end if;
145
146 fnd_file.put_line(FND_FILE.OUTPUT, rpad(v_csr_details.person_id,10)
147 ||' '||rpad(v_csr_details.full_name,30)
148 ||' '||rpad(v_csr_details.ni_no,13)
149 ||' '||rpad(v_csr_details.old_category,25)
150 ||' '||rpad(v_csr_details.new_category,12)
151 ||' '||rpad(to_char(v_csr_details.effective_start_date,'DD/MM/YYYY'),20)
152 ||' '||rpad(to_char(v_csr_details.effective_end_date,'DD/MM/YYYY'),20));
153
154 l_count := l_count + 1;
155
156
157 hr_utility.trace('v_csr_details.person_id='||v_csr_details.person_id);
158
159 if l_category <> v_csr_details.category then
160
161 open csr_disability_details;
162 fetch csr_disability_details into v_disability_id_new,v_object_version_number_new;
163 if csr_disability_details%notfound then
164
165 hr_utility.set_location('Entering ' || l_proc,30);
166 insert into per_disabilities_f
167 (disability_id
168 ,effective_start_date
169 ,effective_end_date
170 ,person_id
171 ,category
172 ,status
173 ,degree
174 ,quota_fte
175 ,object_version_number
176 ,created_by
177 ,creation_date
178 ,last_update_date
179 ,last_updated_by
180 ,last_update_login
181 )
182 Values
183 (per_disabilities_s.nextval
184 ,v_csr_details.effective_start_date
185 ,v_csr_details.effective_end_date
186 ,p_person_id
187 ,v_csr_details.category
188 ,'A'
189 ,null
190 ,1.0
191 ,1
192 ,-1
193 ,sysdate
194 ,sysdate
195 ,-1
196 ,-1
197 );
198
199 l_category := v_csr_details.category;
200
201 else
202
203 -- Creating date track updates against Disability records
204 hr_utility.set_location('Entering ' || l_proc,40);
205 insert into per_disabilities_f
206 (disability_id
207 ,effective_start_date
208 ,effective_end_date
209 ,person_id
210 ,category
211 ,status
212 ,degree
213 ,quota_fte
214 ,object_version_number
215 ,created_by
216 ,creation_date
217 ,last_update_date
218 ,last_updated_by
219 ,last_update_login
220 )
221
222 select
223 v_disability_id_new
224 ,v_csr_details.effective_start_date
225 ,v_csr_details.effective_end_date
226 ,p_person_id
227 ,v_csr_details.category
228 ,'A'
229 ,null
230 ,1.0
231 ,v_object_version_number_new
232 ,-1
233 ,sysdate
234 ,sysdate
235 ,-1
236 ,-1
237 from dual
238 where not exists
239 (select null from per_disabilities_f
240 where effective_start_date = v_csr_details.effective_start_date
241 and effective_end_date = v_csr_details.effective_end_date
242 and person_id = p_person_id)
243 ;
244
245 l_category := v_csr_details.category;
246 end if; -- end of insert
247
248 close csr_disability_details;
249
250 else
251
252 open csr_disability_details;
253 fetch csr_disability_details into v_disability_id_new,v_object_version_number_new;
254 if csr_disability_details%found then
255
256 hr_utility.set_location('Entering ' || l_proc,45);
257 update per_disabilities_f
258 set effective_end_date = v_csr_details.effective_end_date
259 where disability_id = v_disability_id_new
260 and category = v_csr_details.category;
261 end if;
262 close csr_disability_details;
263 end if; -- end of category
264
265 hr_utility.set_location('Entering ' || l_proc,50);
266 --Correcting the person record, setting Disability field to null
267
268 update per_all_people_f
269 set registered_disabled_flag = null
270 where person_id = p_person_id
271 and effective_start_date = v_csr_details.effective_start_date;
272 --and object_version_number= v_csr_details.object_version_number
273 --and employee_number = v_csr_details.employee_number;
274
275
276 end loop;
277
278 else
279 for v_dis_det in csr_get_disability(p_person_id)
280 loop
281
282 hr_utility.set_location('Entering ' || l_proc,60);
283 if l_count = 1 then
284 fnd_file.put_line(FND_FILE.OUTPUT,'---------------------------------------------------------------------------------------------------------------------------------------');
285 fnd_file.put_line(FND_FILE.OUTPUT,' List of employees ');
286 fnd_file.put_line(FND_FILE.OUTPUT,'---------------------------------------------------------------------------------------------------------------------------------------');
287 fnd_file.put_line(FND_FILE.OUTPUT,' ');
288 fnd_file.put_line(FND_FILE.OUTPUT, rpad('Person ID',10)||' '||rpad('Full Name',30)||' '||rpad('NI Number',13)
289 ||' '||rpad('Old Category',25)||' '||rpad('New Category',12)||' '||rpad('Effective Start Date',20)||' '||rpad('Effective End Date',20));
290 end if;
291
292 fnd_file.put_line(FND_FILE.OUTPUT, rpad(v_dis_det.person_id,10)
293 ||' '||rpad(v_dis_det.full_name,30)
294 ||' '||rpad(v_dis_det.ni_no,13)
295 ||' '||rpad(v_dis_det.category,25)
296 ||' '||rpad('Yes',12)
297 ||' '||rpad(to_char(v_dis_det.effective_start_date,'DD/MM/YYYY'),20)
298 ||' '||rpad(to_char(v_dis_det.effective_end_date,'DD/MM/YYYY'),20));
299
300
301 --Correcting the disability record, updating category field to 'Yes' against non-GB Categories.
302 hr_utility.set_location('Entering ' || l_proc,70);
303
304 update per_disabilities_f
305 set category='Y'
306 where effective_start_date = v_dis_det.effective_start_date
307 and disability_id = v_dis_det.disability_id ;
308
309 hr_utility.set_location('Entering ' || l_proc,80);
310 l_count := l_count + 1;
311 end loop;
312 end if; -- end of main
313 close csr_disability_det;
314
315 hr_utility.set_location('Leaving ' || l_proc,90);
316 end upg_disability_status;
317
318 end pay_gb_upgrade_pkg;