[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_REP
Source
1 PACKAGE BODY pay_gb_rep AS
2 /* $Header: paygbrep.pkb 120.2.12000000.3 2007/02/07 22:22:27 rmakhija noship $ */
3
4 g_package CONSTANT VARCHAR2(30) := 'PAY_GB_REP';
5
6 PROCEDURE ni_arrears_report(
7 errbuf out NOCOPY varchar2
8 ,retcode out NOCOPY varchar2
9 ,p_business_group_id in varchar2
10 ,p_effective_date in varchar2
11 ,p_payroll_id in varchar2
12 ,p_def_bal_id in varchar2) IS
13
14 cursor header_details is
15 select distinct
16 pbg.name,
17 fnd_date.canonical_to_date(p_effective_date),
18 decode(p_payroll_id,null, ' ',papf.payroll_id, papf.payroll_name)
19 from per_business_groups pbg,
20 pay_all_payrolls_f papf
21 where pbg.business_group_id = p_business_group_id
22 and pbg.business_group_id = papf.business_group_id
23 and papf.payroll_id = nvl(to_number(p_payroll_id),papf.payroll_id)
24 and fnd_date.canonical_to_date(p_effective_date) between
25 papf.effective_start_date and papf.effective_end_date;
26
27 cursor body_details is
28 select /*+ ORDERED
29 use_index(ppf, PER_PEOPLE_F_FK1)
30 use_index(ppt, PER_PERSON_TYPES_N1) */
31 paf.assignment_number,
32 ppf.full_name,
33 paf.assignment_id
34 -- hr_gbbal.calc_all_balances(fnd_date.canonical_to_date(p_effective_date), paf.assignment_id, p_def_bal_id ) ni_arrears
35 from per_all_people_f ppf,
36 per_person_types ppt,
37 per_all_assignments_f paf,
38 pay_all_payrolls_f papf
39 where ppf.business_group_id = p_business_group_id
40 and papf.business_group_id = p_business_group_id
41 and ppt.business_group_id = p_business_group_id
42 --and papf.payroll_id = nvl(p_payroll_id,papf.payroll_id)
43 and (p_payroll_id is null
44 or
45 papf.payroll_id = p_payroll_id)
46 and papf.payroll_id = paf.payroll_id
47 and ppt.system_person_type = 'EMP'
48 and fnd_date.canonical_to_date(p_effective_date) between ppf.effective_start_date and ppf.effective_end_date
49 and fnd_date.canonical_to_date(p_effective_date) between papf.effective_start_date and papf.effective_end_date
50 and ppf.effective_end_date between paf.effective_start_date and paf.effective_end_date
51 and ppt.person_type_id = ppf.person_type_id
52 and paf.person_id = ppf.person_id
53 -- and hr_gbbal.calc_all_balances(fnd_date.canonical_to_date(p_effective_date), paf.assignment_id, p_def_bal_id) > 0
54 order by assignment_number;
55
56 l_total_arrears number;
57 l_ni_arrears number;
58 l_date varchar2(20);
59 l_bg_name varchar2(50);
60 l_payroll_name varchar2(50);
61
62 BEGIN
63 OPEN header_details;
64 FETCH header_details into l_bg_name, l_date, l_payroll_name;
65 CLOSE header_details;
66
67 fnd_file.put_line(fnd_file.output,lpad('NI Arrears Report',42));
68 fnd_file.put_line(fnd_file.output,' ');
69 fnd_file.put_line(fnd_file.output,'Business Group : ' || l_bg_name);
70 fnd_file.put_line(fnd_file.output,'Effective Date : ' || l_date);
71 fnd_file.put_line(fnd_file.output,'Payroll : ' || l_payroll_name);
72 fnd_file.put_line(fnd_file.output,' ');
73
74 fnd_file.put_line(fnd_file.output,rpad('Assignment',15,' ') || ' ' || rpad('Full Name',35,' ') || ' ' || rpad('NI Arrears YTD',15,' '));
75 fnd_file.put_line(fnd_file.output,rpad('-',15,'-') || ' ' || rpad('-',35,'-') || ' ' || rpad('-',15,'-'));
76 l_total_arrears := 0;
77
78 for l_body in body_details loop
79 l_ni_arrears := nvl(hr_gbbal.calc_all_balances(fnd_date.canonical_to_date(p_effective_date),
80 l_body.assignment_id,
81 p_def_bal_id),0);
82 if (l_ni_arrears > 0) then
83 fnd_file.put_line(fnd_file.output,rpad(l_body.assignment_number,15,' ') || ' ' || rpad(l_body.full_name,35,' ') ||
84 ' ' || lpad(to_char(l_ni_arrears,'999,999,999.90'),15,' '));
85 l_total_arrears := l_total_arrears + l_ni_arrears;
86 end if;
87 end loop;
88
89 fnd_file.put_line(fnd_file.output,lpad(lpad('-',15,'-'),67,' '));
90 fnd_file.put_line(fnd_file.output,'Report Total' || lpad(to_char(l_total_arrears,'999,999,999.90'),55,' '));
91
92 END ni_arrears_report;
93
94 PROCEDURE p45_issued_active_asg_report(
95 errbuf out NOCOPY varchar2
96 ,retcode out NOCOPY varchar2
97 ,p_business_group_id in varchar2
98 ,p_effective_date in varchar2
99 ,p_payroll_id in varchar2) IS
100
101 cursor csr_header_details(c_business_group_id NUMBER,
102 c_payroll_id NUMBER,
103 c_effective_date VARCHAR2) is
104 select pbg.name,
105 fnd_date.canonical_to_date(c_effective_date),
106 decode(c_payroll_id,null, ' ',papf.payroll_id, papf.payroll_name)
107 from per_business_groups pbg,
108 pay_all_payrolls_f papf
109 where pbg.business_group_id = c_business_group_id
110 and pbg.business_group_id = papf.business_group_id
111 and papf.payroll_id = nvl(to_number(c_payroll_id),papf.payroll_id)
112 and fnd_date.canonical_to_date(c_effective_date) between
113 papf.effective_start_date and papf.effective_end_date;
114
115 cursor csr_payroll_details(c_business_group_id NUMBER,
116 c_payroll_id NUMBER,
117 c_effective_date DATE) is
118 select distinct
119 papf.payroll_id,
120 papf.payroll_name
121 from per_business_groups pbg,
122 pay_all_payrolls_f papf
123 where pbg.business_group_id = c_business_group_id
124 and pbg.business_group_id = papf.business_group_id
125 and papf.payroll_id = nvl(to_number(c_payroll_id),papf.payroll_id)
126 and c_effective_date between
127 papf.effective_start_date and papf.effective_end_date
128 order by papf.payroll_name;
129
130 cursor csr_body_details(c_business_group_id NUMBER,
131 c_payroll_id NUMBER,
132 c_effective_date DATE) is
133 select paf.assignment_number,
134 ppf.full_name,
135 paf.assignment_id
136 from per_all_people_f ppf,
137 per_all_assignments_f paf,
138 per_assignment_status_types past
139 where paf.assignment_status_type_id = past.assignment_status_type_id
140 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
141 and ppf.business_group_id = c_business_group_id
142 and paf.payroll_id = c_payroll_id
143 and c_effective_date between ppf.effective_start_date and ppf.effective_end_date
144 and c_effective_date between paf.effective_start_date and paf.effective_end_date
145 and paf.person_id = ppf.person_id
146 order by lpad(substr(assignment_number,1,decode(instr(assignment_number,'-'),0,25,instr(assignment_number,'-')-1)),25,'0') ||
147 lpad(nvl(substr(assignment_number,decode(instr(assignment_number,'-'),0,null,instr(assignment_number,'-')+1)),'0'),10,'0');
148
149 cursor csr_asg_dtls(c_assignment_id number) is
150 select assignment_number
151 from per_all_assignments_f
152 where assignment_id = c_assignment_id;
153
154 cursor csr_locked_act_dtls(c_asg_action_id NUMBER) IS
155 select locking_action_id, pact.effective_date
156 from pay_action_interlocks, pay_assignment_actions act, pay_payroll_actions pact
157 where locked_action_id = c_asg_action_id
158 and locking_action_id = act.assignment_action_id
159 and act.payroll_action_id = pact.payroll_action_id;
160
161 l_date varchar2(20);
162 l_effective_date date;
163 l_bg_name varchar2(50);
164 l_payroll_name varchar2(50);
165 l_issue_date date;
166
167 l_assignment_action_id number;
168 l_agg_assignment_id number;
169 l_agg_assignment_number per_all_assignments_f.assignment_number%type;
170 l_final_payment_date date;
171 l_p45_issue_date date;
172 l_p45_agg_asg_action_id number;
173 l_action_sequence number;
174
175 l_locking_action_id number;
176 l_lock_effective_date date;
177 l_count number;
178 l_total_count number;
179 BEGIN
180 --
181 -- conveting the paramter value to date, effective date
182 --
183 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
184
185 OPEN csr_header_details(p_business_group_id, p_payroll_id, p_effective_date);
186 FETCH csr_header_details into l_bg_name, l_date, l_payroll_name;
187 CLOSE csr_header_details;
188
189 fnd_file.put_line(fnd_file.output,lpad('P45 Issued for Active Assignments Report',80) || lpad(' ', 37, ' ') || sysdate);
190 fnd_file.put_line(fnd_file.output,' ');
191 fnd_file.put_line(fnd_file.output,'Business Group : ' || l_bg_name);
192 fnd_file.put_line(fnd_file.output,'Effective Date : ' || l_date);
193 fnd_file.put_line(fnd_file.output,'Payroll : ' || l_payroll_name);
194 fnd_file.put_line(fnd_file.output,' ');
195
196 fnd_file.put_line(fnd_file.output,'P45 Manually Issued for Assignments active or suspended as at '|| l_effective_date || ' :');
197 fnd_file.put_line(fnd_file.output,' ');
198
199 l_total_count := 0;
200 for p_rec in csr_payroll_details(p_business_group_id, p_payroll_id, l_effective_date) loop
201 l_count := 0;
202 for b_rec in csr_body_details(p_business_group_id, p_rec.payroll_id, l_effective_date) loop
203 l_issue_date := pay_p45_pkg.get_p45_eit_manual_issue_dt(b_rec.assignment_id);
204 if l_issue_date is not null then
205 l_total_count := l_total_count + 1;
206 if l_count = 0 then
207 fnd_file.put_line(fnd_file.output,' ');
208 fnd_file.put_line(fnd_file.output,'Payroll Name : ' || p_rec.payroll_name);
209 fnd_file.put_line(fnd_file.output,' ');
210
211 fnd_file.put_line(fnd_file.output,rpad('Assignment',15,' ') || ' ' || rpad('Full Name',35,' ') || ' ' || rpad('Manual Issue Date',20,' '));
212 fnd_file.put_line(fnd_file.output,rpad('-',15,'-') || ' ' || rpad('-',35,'-') || ' ' || rpad('-',20,'-'));
213 l_count := 1;
214 end if;
215 fnd_file.put_line(fnd_file.output,rpad(b_rec.assignment_number,15,' ') || ' ' ||
216 rpad(b_rec.full_name,35,' ') ||' ' ||
217 rpad(l_issue_date,20,' '));
218 end if;
219 end loop;
220 end loop;
221
222 fnd_file.put_line(fnd_file.output,' ');
223 fnd_file.put_line(fnd_file.output,' ');
224 fnd_file.put_line(fnd_file.output,'Total Number of active or suspended assignments issued P45 manually: ' || l_total_count);
225
226
227 --
228 -- P45 Issued Assignments and P45 issued for
229 -- Aggregated Assignments
230 --
231 fnd_file.put_line(fnd_file.output,' ');
232 fnd_file.put_line(fnd_file.output,' ');
233 fnd_file.put_line(fnd_file.output,'P45 Issued for Assignments/Aggregated Assignments active or suspended as at ' || l_effective_date || ' :');
234 fnd_file.put_line(fnd_file.output,' ');
235 l_total_count := 0;
236 for p_rec in csr_payroll_details(p_business_group_id, p_payroll_id, l_effective_date) loop
237 l_count := 0;
238 for b_rec in csr_body_details(p_business_group_id, p_rec.payroll_id, l_effective_date) loop
239 pay_p45_pkg.get_p45_asg_action_id(p_assignment_id => b_rec.assignment_id,
240 p_assignment_action_id => l_assignment_action_id,
241 p_issue_date => l_issue_date,
242 p_action_sequence => l_action_sequence);
243
244 if l_assignment_action_id is not null then
245 l_total_count := l_total_count + 1;
246 if l_count = 0 then
247 fnd_file.put_line(fnd_file.output,' ');
248 fnd_file.put_line(fnd_file.output,'Payroll Name : ' || p_rec.payroll_name);
249 fnd_file.put_line(fnd_file.output,' ');
250 fnd_file.put_line(fnd_file.output,rpad('Assignment',15,' ') || ' ' ||
251 rpad('Full Name',35,' ') || ' ' ||
252 rpad('Agg Assignment',15,' ') || ' ' ||
253 rpad('Issue Date',10,' ') || ' ' ||
254 lpad('Asg Action ID', 15,' ') || ' ' ||
255 lpad('Locking Action ID',17,' ') || ' ' ||
256 rpad('Action Eff.Date',15,' '));
257 fnd_file.put_line(fnd_file.output,rpad('-',15,'-') || ' ' ||
258 rpad('-',35,'-') || ' ' ||
259 rpad('-',15,'-') || ' ' ||
260 rpad('-',10,'-') || ' ' ||
261 lpad('-',15,'-') || ' ' ||
262 lpad('-',17,'-') || ' ' ||
263 rpad('-',15,'-'));
264 l_count := 1;
265 end if;
266
267 --
268 -- check for any payroll action, locked the P45 asg action. if found then show the
269 -- locking acions id and effective date of that payroll action
270 --
271 open csr_locked_act_dtls(l_assignment_action_id);
272 fetch csr_locked_act_dtls into l_locking_action_id, l_lock_effective_date;
273 if csr_locked_act_dtls%notfound then
274 fnd_file.put_line(fnd_file.output,rpad(b_rec.assignment_number,15,' ') || ' ' ||
275 rpad(b_rec.full_name,35,' ') || ' ' ||
276 rpad(' ',15,' ') || ' ' ||
277 rpad(l_issue_date,10,' ') || ' ' ||
278 lpad(l_assignment_action_id, 15,' ') || ' '||
279 lpad(' ',17,' ') || ' ' ||
280 rpad(' ',15,' '));
281 else
282 fnd_file.put_line(fnd_file.output,rpad(b_rec.assignment_number,15,' ') || ' ' ||
283 rpad(b_rec.full_name,35,' ') || ' ' ||
284 rpad(' ',15,' ') || ' ' ||
285 rpad(l_issue_date,10,' ') || ' ' ||
286 lpad(l_assignment_action_id, 15,' ') || ' '||
287 lpad(l_locking_action_id,17,' ') || ' ' ||
288 rpad(l_lock_effective_date,15,' '));
289 loop
290 fetch csr_locked_act_dtls into l_locking_action_id, l_lock_effective_date;
291 if csr_locked_act_dtls%notfound then
292 exit;
293 end if;
294 fnd_file.put_line(fnd_file.output,rpad(' ',15,' ') || ' ' ||
295 rpad(' ',35,' ') || ' ' ||
296 rpad(' ',15,' ') || ' ' ||
297 rpad(' ',10,' ') || ' ' ||
298 lpad(' ',15,' ') || ' ' ||
299 lpad(l_locking_action_id,17,' ') || ' ' ||
300 rpad(l_lock_effective_date,15,' '));
304
301 end loop;
302 end if;
303 close csr_locked_act_dtls;
305 end if;
306
307 --
308 -- check for the P45 issued for any of the aggregated assignment associated with this assignment
309 --
310 pay_p45_pkg.get_p45_agg_asg_action_id(p_assignment_id => b_rec.assignment_id,
311 p_agg_assignment_id => l_agg_assignment_id,
312 p_final_payment_date => l_final_payment_date,
313 p_p45_issue_date => l_p45_issue_date,
314 p_p45_agg_asg_action_id => l_p45_agg_asg_action_id);
315 if l_agg_assignment_id is not null then
316 l_total_count := l_total_count + 1;
317 if l_count = 0 then
318 fnd_file.put_line(fnd_file.output,' ');
319 fnd_file.put_line(fnd_file.output,'Payroll Name : ' || p_rec.payroll_name);
320 fnd_file.put_line(fnd_file.output,' ');
321 fnd_file.put_line(fnd_file.output,rpad('Assignment',15,' ') || ' ' ||
322 rpad('Full Name',35,' ') || ' ' ||
323 rpad('Agg Assignment',15,' ') || ' ' ||
324 rpad('Issue Date',10,' ') || ' ' ||
325 lpad('Asg Action ID', 15,' ') || ' ' ||
326 lpad('Locking Action ID',17,' ') || ' ' ||
327 rpad('Action Eff.Date',15,' '));
328 fnd_file.put_line(fnd_file.output,rpad('-',15,'-') || ' ' ||
329 rpad('-',35,'-') || ' ' ||
330 rpad('-',15,'-') || ' ' ||
331 rpad('-',10,'-') || ' ' ||
332 lpad('-',15,'-') || ' ' ||
333 lpad('-',17,'-') || ' ' ||
334 rpad('-',15,'-'));
335 l_count := 1;
336 end if;
337 --fnd_file.put_line(fnd_file.output,' l_final_payment_date '|| l_final_payment_date);
338 --fnd_file.put_line(fnd_file.output,' l_agg_assignment_id ' || l_agg_assignment_id );
339 --
340 -- fetching the Assignment number for the agg.assignment id
341 --
342 open csr_asg_dtls(l_agg_assignment_id);
343 fetch csr_asg_dtls into l_agg_assignment_number;
344 close csr_asg_dtls;
345
346 --
347 -- check for any payroll action, locked the P45 asg action. if found then show the
348 -- locking acions id and effective date of that payroll action
349 --
350 open csr_locked_act_dtls(l_p45_agg_asg_action_id);
351 fetch csr_locked_act_dtls into l_locking_action_id, l_lock_effective_date;
352 if csr_locked_act_dtls%notfound then
353 fnd_file.put_line(fnd_file.output,rpad(b_rec.assignment_number,15,' ') || ' ' ||
354 rpad(b_rec.full_name,35,' ') || ' ' ||
355 rpad(l_agg_assignment_number,15,' ') || ' ' ||
356 rpad(l_p45_issue_date,10,' ') || ' ' ||
357 lpad(l_p45_agg_asg_action_id, 15,' ') || ' ' ||
358 lpad(' ',17,' ') || ' ' ||
359 rpad(' ',15,' '));
360 else
361 fnd_file.put_line(fnd_file.output,rpad(b_rec.assignment_number,15,' ') || ' ' ||
362 rpad(b_rec.full_name,35,' ') || ' ' ||
363 rpad(l_agg_assignment_number,15,' ') || ' ' ||
364 rpad(l_p45_issue_date,10,' ') || ' ' ||
365 lpad(l_p45_agg_asg_action_id, 15,' ') || ' ' ||
366 lpad(l_locking_action_id,17,' ') || ' ' ||
367 rpad(l_lock_effective_date,15,' '));
368 loop
369 fetch csr_locked_act_dtls into l_locking_action_id, l_lock_effective_date;
370 if csr_locked_act_dtls%notfound then
371 exit;
372 end if;
373 fnd_file.put_line(fnd_file.output,rpad(' ',15,' ') || ' ' ||
374 rpad(' ',35,' ') || ' ' ||
375 rpad(' ',15,' ') || ' ' ||
376 rpad(' ',10,' ') || ' ' ||
377 lpad(' ',15,' ') || ' ' ||
378 lpad(l_locking_action_id,17,' ') || ' ' ||
379 rpad(l_lock_effective_date,15,' '));
380 end loop;
381 end if;
382 close csr_locked_act_dtls;
383 end if;
384
385 end loop;
386 end loop;
387
388 fnd_file.put_line(fnd_file.output,' ');
389 fnd_file.put_line(fnd_file.output,' ');
390 fnd_file.put_line(fnd_file.output,'Total number of active or suspended assignments/aggregated assignments issued P45: ' || l_total_count);
391
392 END p45_issued_active_asg_report;
393
394 END;