[Home] [Help]
PACKAGE BODY: APPS.PAY_ASG_DEBUG_PKG
Source
1 PACKAGE BODY pay_asg_debug_pkg AS
2 /* $Header: pyacdebg.pkb 120.1 2005/10/05 02:39:28 schauhan noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1999 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_asg_debug_pkg
21
22 Description : Package for the Elements Reports. The package
23 generated the output file in the specified user
24 format. The current formats supported are
25 - HTML
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ------ ------- -----------
31 15-MAR-2002 rsirigir 115.2 2254026 GSCC Compliance inclusions
32 15-AUG-2002 ahanda 115.4 Perf. changes to cursor cur_vertex
33 07-JAN-2005 mreid 115.6 4103182 Changed report title
34 19-MAY-2005 schauhan 115.8 4371929 Coorected the spelling for "Assignment number"
35 */
36
37 procedure print_blank_lines(p_no_of_lines number) is
38
39 l_blank_lines varchar2(1000);
40
41 begin
42
43 l_blank_lines := '<TABLE BORDER=0>';
44
45 for i in 1..p_no_of_lines loop
46
47 l_blank_lines := l_blank_lines || '<TR><TD> </TD></TR>';
48
49 end loop;
50
51 l_blank_lines := l_blank_lines || '</TABLE>';
52
53 fnd_file.put_line(fnd_file.output,l_blank_lines);
54
55 end;
56
57 procedure write_data
61
58 (errbuf out nocopy varchar2
59 ,retcode out nocopy number
60 ,p_assignment_id in number) is
62 cursor cur_header2 is
63 select ppf.full_name,
64 ppf.employee_number,
65 paf.assignment_number
66 from per_assignments_f paf,
67 per_people_f ppf
68 where ppf.person_id = paf.person_id and
69 sysdate between ppf.effective_start_date and
70 ppf.effective_end_date and
71 sysdate between paf.effective_start_date and
72 paf.effective_end_date and
73 paf.assignment_id = p_assignment_id;
74
75
76 cursor cur_assignment is select
77 paf.assignment_id,
78 to_char(paf.effective_start_date,'MM/DD/YYYY') effective_start_date,
79 decode(to_char(paf.effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,to_char(paf.effective_end_date,'MM/DD/YYYY')) effective_end_date,
80 paf.location_id location_id,
81 hl.location_code location_code,
82 ppf.payroll_id payroll_id,
83 ppf.payroll_name payroll_name,
84 paf.pay_basis_id pay_basis_id,
85 ppb.name name,
86 ltrim(rtrim(paf.soft_coding_keyflex_id)) sfckid
87 from per_assignments_f paf,
88 per_pay_bases ppb,
89 pay_payrolls_f ppf,
90 hr_locations hl
91 where assignment_id = p_assignment_id
92 and paf.location_id = hl.location_id
93 and paf.pay_basis_id = ppb.pay_basis_id
94 and paf.payroll_id = ppf.payroll_id
95 and sysdate between
96 ppf.effective_start_date and ppf.effective_end_date;
97
98 cursor cur_person is select
99 person_id, full_name,
100 to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
101 decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
102 to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
103 from per_people_f
104 where person_id in (select person_id
105 from per_assignments_f
106 where assignment_id = p_assignment_id);
107
108 cursor cur_address is select
109 ltrim(rtrim(primary_flag)) primary_flag,
110 ltrim(rtrim(region_1)) region_1,
111 ltrim(rtrim(region_2)) region_2,
112 ltrim(rtrim(town_or_city)) town_or_city
113 from per_addresses
114 where person_id in (select person_id
115 from per_assignments_f
116 where assignment_id = p_assignment_id);
117
118 cursor cur_federal is select
119 sui_jurisdiction_code,
120 to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
121 decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
122 to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
123 from pay_us_emp_fed_tax_rules_f
124 where assignment_id = p_assignment_id;
125
126 cursor cur_state is select
127 jurisdiction_code,
128 to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
129 decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
130 to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
131 from pay_us_emp_state_tax_rules_f
132 where assignment_id = p_assignment_id;
133
134 cursor cur_county is select
135 jurisdiction_code,
136 to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
137 decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
138 to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
139 from pay_us_emp_county_tax_rules_f
140 where assignment_id = p_assignment_id;
141
142 cursor cur_city is select
143 jurisdiction_code,
144 to_char(effective_start_date,'MM/DD/YYYY') effective_start_date,
145 decode(to_char(effective_end_date,'MM/DD/YYYY'),'12/31/4712',NULL,
146 to_char(effective_end_date,'MM/DD/YYYY')) effective_end_date
147 from pay_us_emp_city_tax_rules_f
148 where assignment_id = p_assignment_id;
149
150 cursor cur_vertex is
151 select
152 pev.element_entry_id eeid,
153 pev.screen_entry_value sevl,
154 to_char(pev.effective_start_date,'MM/DD/YYYY') effective_start_date,
155 decode(to_char(pev.effective_end_date,'MM/DD/YYYY'),'12/31/4712',
156 NULL, to_char(pev.effective_end_date,'MM/DD/YYYY')) effective_end_date
157 from pay_element_types_f pet,
158 pay_element_links_f pel,
159 pay_element_entries_f pef,
160 pay_element_entry_values_f pev
161 where pet.element_name in ('VERTEX', 'Workers Compensation')
162 and pet.element_type_id = pel.element_type_id
163 and pel.element_link_id = pef.element_link_id
164 and pef.element_entry_id = pev.element_entry_id
165 and pev.screen_entry_value is not null
166 and pef.effective_start_date = pev.effective_start_date
167 and pef.effective_end_date = pev.effective_end_date
168 and pef.assignment_id = p_assignment_id
169 order by pev.element_entry_id,
170 pev.effective_start_date;
171
172 l_heading varchar2(240);
173 l_header2 varchar2(240);
174 l_body varchar2(32000);
175
176
177 l_full_name per_people_f.full_name%TYPE;
178 l_employee_number per_people_f.employee_number%TYPE;
179 l_assignment_number per_assignments_f.assignment_number%TYPE;
180 begin
181
182 --hr_utility.trace_on(1,'pg');
183
184 l_heading := '<HTML><HEAD> <CENTER> <H1> <B> ' ||
185 'Assignment Data Integrity Report: ' ||
186 '</B> </H1> </CENTER> </HEAD> ';
187
188 fnd_file.put_line(fnd_file.output,l_heading);
189
190 open cur_header2;
191 fetch cur_header2
195 close cur_header2;
192 into l_full_name,
193 l_employee_number,
194 l_assignment_number;
196
197 hr_utility.trace('l_full_name = ' || l_full_name);
198 hr_utility.trace('l_employee_number = ' || l_employee_number);
199 hr_utility.trace('l_assignment_number = ' || l_assignment_number);
200
201 l_header2 := '<H2> For Employee: ' || l_employee_number || ' ' || l_full_name || ' Assignment number ' || l_assignment_number || '</H2>';
202
203 fnd_file.put_line(fnd_file.output,l_header2);
204
205 l_body := '<TABLE BORDER=1>';
206
207 l_body := l_body || '<TR> <TD COLSPAN=10 ALIGN="LEFT"> <B> Assignment Details </B> </TD><TR>';
208
209 l_body := l_body || '<TR ALIGN=LEFT>'
210 || '<TD WIDTH=5%>' || 'Assignment ID' || '</TD>'
211 || '<TD WIDTH=5%>'|| 'Effective Start Date' || '</TD>'
212 || '<TD WIDTH=5%>'|| 'Effective End Date' || '</TD>'
213 || '<TD WIDTH=5%>' || 'Location ID' || '</TD>'
214 || '<TD WIDTH=5%>'|| 'Location Name' || '</TD>'
215 || '<TD WIDTH=5%>' || 'Payroll ID' || '</TD>'
216 || '<TD WIDTH=5%>'|| 'Payroll Name' || '</TD>'
217 || '<TD WIDTH=5%>' || 'Pay Basis ID' || '</TD>'
218 || '<TD WIDTH=5%>'|| 'Name' || '</TD>'
219 || '<TD WIDTH=5%>' || 'sfckid'|| '</TD>'
220 || '</TR>';
221
222
223 for i in cur_assignment loop
224
225 l_body := l_body || '<TR ALIGN=LEFT>'
226 || '<TD >' || p_assignment_id || '</TD>'
227 || '<TD >' || i.effective_start_date || '</TD>'
228 || '<TD >' || i.effective_end_date || '</TD>'
229 || '<TD >' || i.location_id || '</TD>'
230 || '<TD >' || i.location_code || '</TD>'
231 || '<TD >' || i.payroll_id || '</TD>'
232 || '<TD >' || i.payroll_name || '</TD>'
233 || '<TD >' || i.pay_basis_id || '</TD>'
234 || '<TD >' || i.name || '</TD>'
235 || '<TD >' || i.sfckid || '</TD>'
236 || '</TR>';
237 end loop;
238
239 l_body := l_body || '</TABLE>';
240
241 fnd_file.put_line(fnd_file.output,l_body);
242
243 print_blank_lines(10);
244
245 l_body := '<TABLE BORDER=1>';
246
247 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> Person Details </B></TD><TR>';
248
249 l_body := l_body || '<TR ALIGN=LEFT>'
250 || '<TD >' || 'Person ID' || '</TD>'
251 || '<TD >' || 'Employee Name' || '</TD>'
252 || '<TD >' || 'Effective Start Date' || '</TD>'
253 || '<TD >' || 'Effective End Date' || '</TD>'
254 || '</TR>';
255
256
257 for i in cur_person loop
258
259 l_body := l_body || '<TR ALIGN=LEFT>'
260 || '<TD >' || i.person_id || '</TD>'
261 || '<TD >' || i.full_name || '</TD>'
262 || '<TD >' || i.effective_start_date || '</TD>'
263 || '<TD >' || i.effective_end_date || '</TD>'
264 || '</TR>';
265 end loop;
266
267 l_body := l_body || '</TABLE>';
268
269 fnd_file.put_line(fnd_file.output,l_body);
270
271 print_blank_lines(10);
272
273 l_body := '<TABLE BORDER=1>';
274
275 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> Address Details </B> </TD><TR>';
276
277 l_body := l_body || '<TR ALIGN=LEFT>'
278 || '<TD >' || 'Primary Address' || '</TD>'
279 || '<TD >' || 'County' || '</TD>'
280 || '<TD >' || 'State' || '</TD>'
281 || '<TD >' || 'City' || '</TD>'
282 || '</TR>';
283
284
285 for i in cur_address loop
286
287 l_body := l_body || '<TR ALIGN=LEFT>'
288 || '<TD >' || i.primary_flag || '</TD>'
289 || '<TD >' || i.region_1 || '</TD>'
290 || '<TD >' || i.region_2 || '</TD>'
291 || '<TD >' || i.town_or_city || '</TD>'
292 || '</TR>';
293 end loop;
294
295 l_body := l_body || '</TABLE>';
296
297 fnd_file.put_line(fnd_file.output,l_body);
298
299 print_blank_lines(10);
300
301 l_body := '<TABLE BORDER=1>';
302
303 l_body := l_body || '<TR> <TD COLSPAN=3 ALIGN="LEFT"> <B> Federal Details </B> </TD><TR>';
304
305 l_body := l_body || '<TR ALIGN=LEFT>'
306 || '<TD >' || 'Jurisdiction' || '</TD>'
307 || '<TD >' || 'Effective Start Date' || '</TD>'
308 || '<TD >' || 'Effective End Date' || '</TD>'
309 || '</TR>';
310
311
312 for i in cur_federal loop
313
314 l_body := l_body || '<TR ALIGN=LEFT>'
315 || '<TD >' || i.sui_jurisdiction_code || '</TD>'
316 || '<TD >' || i.effective_start_date || '</TD>'
317 || '<TD >' || i.effective_end_date || '</TD>'
318 || '</TR>';
319 end loop;
320
321 l_body := l_body || '</TABLE>';
322
323 fnd_file.put_line(fnd_file.output,l_body);
324
325 print_blank_lines(10);
326
327 l_body := '<TABLE BORDER=1>';
328
329 l_body := l_body || '<TR> <TD COLSPAN=3 ALIGN="LEFT"> <B> State Details </B> </TD><TR>';
330
331 l_body := l_body || '<TR ALIGN=LEFT>'
332 || '<TD >' || 'Jurisdiction' || '</TD>'
333 || '<TD >' || 'Effective Start Date' || '</TD>'
337
334 || '<TD >' || 'Effective End Date' || '</TD>'
335 || '</TR>';
336
338 for i in cur_state loop
339
340 l_body := l_body || '<TR ALIGN=LEFT>'
341 || '<TD >' || i.jurisdiction_code || '</TD>'
342 || '<TD >' || i.effective_start_date || '</TD>'
343 || '<TD >' || i.effective_end_date || '</TD>'
344 || '</TR>';
345 end loop;
346
347 l_body := l_body || '</TABLE>';
348
349 fnd_file.put_line(fnd_file.output,l_body);
350
351 print_blank_lines(10);
352
353 l_body := '<TABLE BORDER=1>';
354
355 l_body := l_body || '<TR> <TD COLSPAN=3 ALIGN="LEFT"> <B> County Details </B></TD><TR>';
356
357 l_body := l_body || '<TR ALIGN=LEFT>'
358 || '<TD >' || 'Jurisdiction' || '</TD>'
359 || '<TD >' || 'Effective Start Date' || '</TD>'
360 || '<TD >' || 'Effective End Date' || '</TD>'
361 || '</TR>';
362
363
364 for i in cur_county loop
365
366 l_body := l_body || '<TR ALIGN=LEFT>'
367 || '<TD >' || i.jurisdiction_code || '</TD>'
368 || '<TD >' || i.effective_start_date || '</TD>'
369 || '<TD >' || i.effective_end_date || '</TD>'
370 || '</TR>';
371 end loop;
372
373 l_body := l_body || '</TABLE>';
374
375 fnd_file.put_line(fnd_file.output,l_body);
376
377 print_blank_lines(10);
378
379 l_body := '<TABLE BORDER=1>';
380
381 l_body := l_body || '<TR> <TD COLSPAN=3 ALIGN="LEFT"><B>City Details </B></TD><TR>';
382
383 l_body := l_body || '<TR ALIGN=LEFT>'
384 || '<TD >' || 'Jurisdiction' || '</TD>'
385 || '<TD >' || 'Effective Start Date' || '</TD>'
386 || '<TD >' || 'Effective End Date' || '</TD>'
387 || '</TR>';
388
389
390 for i in cur_city loop
391
392 l_body := l_body || '<TR ALIGN=LEFT>'
393 || '<TD >' || i.jurisdiction_code || '</TD>'
394 || '<TD >' || i.effective_start_date || '</TD>'
395 || '<TD >' || i.effective_end_date || '</TD>'
396 || '</TR>';
397 end loop;
398
399 l_body := l_body || '</TABLE>';
400
401 fnd_file.put_line(fnd_file.output,l_body);
402
403 print_blank_lines(10);
404
405 l_body := '<TABLE BORDER=1>';
406
407 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"><B>VERTEX and Workers Compensation Element Entries.</B></TD><TR>';
408
409 l_body := l_body || '<TR ALIGN=LEFT>'
410 || '<TD>' || 'Element Entry ID' || '</TD>'
411 || '<TD>' || 'Screen Entry Value' || '</TD>'
412 || '<TD>' || 'Effective Start Date' || '</TD>'
413 || '<TD>' || 'Effective End Date' || '</TD>'
414 || '</TR>';
415
416
417 for i in cur_vertex loop
418
419 l_body := l_body || '<TR ALIGN=LEFT>'
420 || '<TD>' || i.eeid || '</TD>'
421 || '<TD>' || i.sevl || '</TD>'
422 || '<TD>' || i.effective_start_date || '</TD>'
423 || '<TD>' || i.effective_end_date || '</TD>'
424 || '</TR>';
425 end loop;
426
427 l_body := l_body || '</TABLE>';
428
429 fnd_file.put_line(fnd_file.output,l_body);
430
431
432 end; -- end of write_data
433
434 end pay_asg_debug_pkg; -- end of package