[Home] [Help]
PACKAGE BODY: APPS.PER_PERSON_INFORMATION
Source
1 PACKAGE BODY per_person_information AS
2 /* $Header: peperinf.pkb 115.3 2003/05/01 10:57:39 pkakar 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 : per_person_information
21
22 Description : Package for the Person 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 08-APR-03 pkakar 115.0 Version Created
32 10-Apr-03 pkakar 115.1 updated the cursor csr_assignment,
33 changing the 'order by' claus
34 Also, changed the reference of
35 tables so that they use secure
36 views
37 17-Apr-03 pkakar 115.2 removed csr_payroll and its usage, and
38 updated csr_assignment to include
39 payroll id and payroll name. Also made
40 some other minor changes in the titles.
41 1-May-03 pkakar 115.3 updated csr_persondetails, so that
42 reference to per_person_usages_f
43 has been removed.
44 */
45
46 procedure print_blank_lines(p_no_of_lines number) is
47
48 l_blank_lines varchar2(1000);
49
50 begin
51
52 l_blank_lines := '<TABLE BORDER=0>';
53
54 for i in 1..p_no_of_lines loop
55
56 l_blank_lines := l_blank_lines || '<TR><TD> </TD></TR>';
57
58 end loop;
59
60 l_blank_lines := l_blank_lines || '</TABLE>';
61
62 fnd_file.put_line(fnd_file.output,l_blank_lines);
63
64 end;
65
66 procedure write_data
67 (errbuf out nocopy varchar2
68 ,retcode out nocopy number
69 ,p_person_id in number) is
70
71 cursor csr_instance is
72 select instance_name
73 ,host_name
74 ,version
75 from v$instance;
76
77 cursor csr_language is
78 select upper(value) value
79 from v$parameter
80 where name = 'nls_language';
81
82 cursor csr_release is
83 select release_name
84 from fnd_product_groups;
85
86 cursor csr_application is
87 SELECT v.application_name||'('||v.application_short_name||')' application
88 ,v.application_id id
89 ,l.meaning installstatus
90 ,nvl(substr(i.patch_level,1,12),' ') patchlevel
91 FROM fnd_application_all_view v
92 ,fnd_product_installations i
93 ,fnd_lookups l
94 WHERE v.application_id = i.application_id
95 AND v.application_id in (0, 800, 801, 802, 803, 805, 808, 809, 810, 8301, 8302, 178)
96 AND l.lookup_type = 'FND_PRODUCT_STATUS'
97 AND l.lookup_code = i.Status order by 1;
98
99 cursor csr_assignment is
100 SELECT paf.assignment_number asgno
101 ,paf.assignment_id asgid
102 ,paf.primary_flag prifl
103 ,to_char(paf.effective_start_date,'MM/DD/YYYY') esd
104 ,NVL(to_char(paf.effective_end_date,'MM/DD/YYYY'),'12/31/4712') eed
105 ,SUBSTR(paf.PERIOD_OF_SERVICE_ID,1,9) posid
106 ,paf.location_id loc_id
107 ,past.per_system_status pss
108 ,DECODE(paf.assignment_type
109 ,'A','Applicant'
110 ,'B','Benefits'
111 ,'E','Employee') AsGType
112 ,paf.payroll_id p_id
113 ,papf.payroll_name Pay_Name
114 FROM per_assignments_f paf
115 ,per_assignment_status_types past
116 ,pay_payrolls_f papf
117 WHERE paf.person_id = p_person_id
118 AND past.assignment_status_type_id = paf.assignment_status_type_id
119 AND paf.payroll_id = papf.payroll_id (+)
120 AND paf.effective_start_date between papf.effective_start_date(+) and nvl(papf.effective_end_date,paf.effective_start_date)
121 ORDER BY paf.assignment_id ,paf.effective_start_date,paf.effective_end_date;
122
123 cursor csr_pp_service is
124 SELECT SUBSTR(period_of_service_id,1,11) pos_id
125 ,to_char(date_start,'MM/DD/YYYY') d_s
126 ,NVL(to_char(last_standard_process_date,'MM/DD/YYYY'),'12/31/4712') last
127 ,NVL(to_char(actual_termination_date,'MM/DD/YYYY'), '12/31/4712') actual
128 ,NVL(to_char(final_process_date,'MM/DD/YYYY'), '12/31/4712') final
129 ,leaving_reason l_r
130 FROM per_periods_of_service
131 WHERE person_id = p_person_id
132 ORDER BY date_start;
133
134 cursor csr_persondetails is
135 SELECT SUBSTR(ppf.person_id,1,10) per_id
136 ,SUBSTR(ppf.full_name,1,25) f_n
137 ,to_char(ppf.effective_start_date,'MM/DD/YYYY') e_s_d
138 ,NVL(to_char(ppf.effective_end_date,'MM/DD/YYYY'),'12/31/4712') e_e_d
139 ,substr(ppt.user_person_type,1,15) u_p_t
140 ,substr(ppt.system_person_type,1,7) p_t_id
141 ,ppf.party_id p_id
142 from per_people_f ppf
143 ,per_person_types ppt
144 where ppf.person_id = p_person_id
145 and ppf.person_type_id = ppt.person_type_id
146 order by ppf.effective_start_date
147 ,ppf.effective_end_date;
148
149 cursor csr_address is
150 SELECT address_id add_id
151 ,SUBSTR(primary_flag,1,8) p_f
152 ,SUBSTR(town_or_city, 1,15) city
153 ,SUBSTR(region_1,1,15) r_1
154 ,SUBSTR(region_2, 1,6) r_2
155 ,SUBSTR(postal_code,1,6) zip
156 ,to_char(date_from,'MM/DD/YYYY') d_f
157 ,NVL(to_char(date_to,'MM/DD/YYYY'), '12/31/4712') d_t
158 ,party_id p_id
159 ,nvl(address_type,null) a_d
160 FROM per_addresses
161 WHERE
162 person_id = p_person_id
163 ORDER BY date_from;
164
165 cursor csr_ptu is
166 SELECT SUBSTR(ptu.person_type_usage_id,1,10) ptu_id
167 ,to_char(ptu.effective_start_date,'MM/DD/YYYY') e_s_d
168 ,NVL(to_char(ptu.effective_end_date,'MM/DD/YYYY'),'12/31/4712') e_e_d
169 ,SUBSTR(ppt.user_person_type,1,20) u_p_t
170 ,SUBSTR(ppt.system_person_type,1,7) p_t_id
171 ,ptu.person_type_id p_id
172 FROM per_person_type_usages_f ptu
173 ,per_person_types ppt
174 WHERE ptu.person_id = p_person_id
175 AND ptu.person_type_id = ppt.person_type_id
176 ORDER BY ptu_id, e_s_d;
177
178
179 l_heading varchar2(240);
180 l_header2 varchar2(240);
181 l_body varchar2(20000);
182
183 begin
184
185 l_heading := '<HTML><HEAD> <CENTER> <H1> <B> ' ||
186 'Person Data Integrity Report ' ||
187 '</B> </H1> </CENTER> </HEAD> ';
188
189 fnd_file.put_line(fnd_file.output,l_heading);
190 for i in csr_persondetails loop
191 l_header2 := '<H2> Person: ' || p_person_id || ' , ' || i.f_n;
192 end loop;
193
194 fnd_file.put_line(fnd_file.output,l_header2);
195
196 print_blank_lines(10);
197
198 -- system information
199 l_body := '<TABLE BORDER=1>';
200
201 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> System Information </B></TD><TR>';
202
203 l_body := l_body || '<TR ALIGN=LEFT>'
204 || '<TD ><B>' || 'Machine' || '</TD></B>'
205 || '<TD ><B>' || 'Date Run' || '</TD></B>'
206 || '<TD ><B>' || 'DB Info' || '</TD></B>'
207 || '<TD ><B>' || 'Version' || '</TD></B>'
208 || '<TD ><B>' || 'DB Language' || '</TD></B>'
209 || '<TD ><B>' || 'Apps Version' || '</TD></B>'
210 || '</TR>';
211
212 for i in csr_instance loop
213 for a in csr_language loop
214 for b in csr_release loop
215
216 l_body := l_body || '<TR ALIGN=LEFT>'
217 || '<TD >' || i.host_name || '</TD></B>'
218 || '<TD >' || to_char(sysdate, 'DD-MON-YYYY HH24:Mi:SS') || '</TD>'
219 || '<TD >' || i.instance_name || '</TD>'
220 || '<TD >' || i.version || '</TD>'
221 || '<TD >' || a.value || '</TD>'
222 || '<TD >' || b.release_name || '</TD>'
223 || '</TR>';
224 end loop;
225 end loop;
226 end loop;
227
228 l_body := l_body || '</TABLE>';
229
230 fnd_file.put_line(fnd_file.output,l_body);
231
232 print_blank_lines(10);
233
234 -- application information
235
236 l_body := '<TABLE BORDER=1>';
237
238 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> HRMS Products Install Status </B></TD><TR>';
239
240 l_body := l_body || '<TR ALIGN=LEFT>'
241 || '<TD ><B>' || 'Application' || '</TD></B>'
242 || '<TD ><B>' || 'ID' || '</TD></B>'
243 || '<TD ><B>' || 'Install Status' || '</TD></B>'
244 || '<TD ><B>' || 'Patch Level' || '</TD></B>'
245 || '</TR>';
246
247 for i in csr_application loop
248
249 l_body := l_body || '<TR ALIGN=LEFT>'
250 || '<TD >' || i.application || '</TD>'
251 || '<TD >' || i.id || '</TD>'
252 || '<TD >' || i.installstatus || '</TD>'
253 || '<TD >' || i.patchlevel || '</TD>'
254 || '</TR>';
255 end loop;
256
257 l_body := l_body || '</TABLE>';
258
259 fnd_file.put_line(fnd_file.output,l_body);
260
261 print_blank_lines(10);
262
263 -- assignment information
264 l_body := '<TABLE BORDER=1>';
265
266
267 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> Assignment Details - per_assignments_f </B></TD><TR>';
268
269
270 l_body := l_body || '<TR ALIGN=LEFT>'
271 || '<TD ><B>' || 'Asg No' || '</TD></B>'
272 || '<TD ><B>' || 'Asg Id' || '</TD></B>'
273 || '<TD ><B>' || 'Primary' || '</TD></B>'
274 || '<TD ><B>' || 'Effective Start Date' || '</TD></B>'
275 || '<TD ><B>' || 'Effective End Date' || '</TD></B>'
276 || '<TD ><B>' || 'POS Id' || '</TD></B>'
277 || '<TD ><B>' || 'Loc Id' || '</TD></B>'
278 || '<TD ><B>' || 'Asg Status' || '</TD></B>'
279 || '<TD ><B>' || 'Assignment Type' || '</TD></B>'
280 || '<TD ><B>' || 'Payroll Id' || '</TD></B>'
281 || '<TD ><B>' || 'Payroll Name' || '</TD></B>'
282 || '</TR>';
283
284
285 for i in csr_assignment loop
286
287 l_body := l_body || '<TR ALIGN=LEFT>'
288 || '<TD >' || i.asgno || '</TD>'
289 || '<TD >' || i.asgid || '</TD>'
290 || '<TD >' || i.prifl || '</TD>'
291 || '<TD >' || i.esd || '</TD>'
292 || '<TD >' || i.eed || '</TD>'
293 || '<TD >' || i.posid || '</TD>'
294 || '<TD >' || i.loc_id || '</TD>'
295 || '<TD >' || i.pss || '</TD>'
296 || '<TD >' || i.asgtype || '</TD>'
297 || '<TD >' || i.p_id || '</TD>'
298 || '<TD >' || i.pay_name || '</TD>'
299 || '</TR>';
300 end loop;
301
302 l_body := l_body || '</TABLE>';
303
304 fnd_file.put_line(fnd_file.output,l_body);
305
306 print_blank_lines(10);
307
308 -- Period of service information
309
310
311 l_body := '<TABLE BORDER=1>';
312
313 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> Period of Service Details - per_periods_of_service </B></TD><TR>';
314
315 l_body := l_body || '<TR ALIGN=LEFT>'
316 || '<TD ><B>' || 'POS Id' || '</TD></B>'
317 || '<TD ><B>' || 'Start Date' || '</TD></B>'
318 || '<TD ><B>' || 'Last Standard Process Date' || '</TD></B>'
319 || '<TD ><B>' || 'Actual Termination Date' || '</TD></B>'
320 || '<TD ><B>' || 'Final Process Date' || '</TD></B>'
321 || '<TD ><B>' || 'Leaving Reason' || '</TD></B>'
322 || '</TR>';
323
324
325 for i in csr_pp_service loop
326
327 l_body := l_body || '<TR ALIGN=LEFT>'
328 || '<TD >' || i.pos_id || '</TD>'
329 || '<TD >' || i.d_s || '</TD>'
330 || '<TD >' || i.last || '</TD>'
331 || '<TD >' || i.actual || '</TD>'
332 || '<TD >' || i.final || '</TD>'
333 || '<TD >' || i.l_r || '</TD>'
334 || '</TR>';
335 end loop;
336
337 l_body := l_body || '</TABLE>';
338
339 fnd_file.put_line(fnd_file.output,l_body);
340
341 print_blank_lines(10);
342
343 -- payroll details
344 /*
345 l_body := '<TABLE BORDER=1>';
346
347 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> Payroll Details - pay_payrolls_f </B></TD><TR>';
348
349 l_body := l_body || '<TR ALIGN=LEFT>'
350 || '<TD >' || 'Asg Id' || '</TD>'
351 || '<TD >' || 'Effective Start Date' || '</TD>'
352 || '<TD >' || 'Effective End Date' || '</TD>'
353 || '<TD >' || 'Payroll Name' || '</TD>'
354 || '<TD >' || 'Pay Basis Name' || '</TD>'
355 || '<TD >' || 'Softcoding KeyFlex Field' || '</TD>'
356 || '</TR>';
357
358
359 for i in csr_payroll loop
360
361 l_body := l_body || '<TR ALIGN=LEFT>'
362 || '<TD >' || i.asg_id || '</TD>'
363 || '<TD >' || i.e_s_d || '</TD>'
364 || '<TD >' || i.e_e_d || '</TD>'
365 || '<TD >' || i.pay_name || '</TD>'
366 || '<TD >' || i.basis_name || '</TD>'
367 || '<TD >' || i.s_c_k_ff || '</TD>'
368 || '</TR>';
369 end loop;
370
371 l_body := l_body || '</TABLE>';
372
373 fnd_file.put_line(fnd_file.output,l_body);
374
375 print_blank_lines(10);
376
377 */
378 -- person information
379
380 l_body := '<TABLE BORDER=1>';
381
382 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> Person Details - per_people_f </B></TD><TR>';
383
384 l_body := l_body || '<TR ALIGN=LEFT> '
385 || '<TD ><B>' || 'Full Name' || '</TD></B>'
386 || '<TD ><B>' || 'Effective Start Date' || '</TD></B>'
387 || '<TD ><B>' || 'Effective End Date' || '</TD></B>'
388 || '<TD ><B>' || 'User Per Type' || '</TD></B>'
389 || '<TD ><B>' || 'Type Id' || '</TD></B>'
390 || '<TD ><B>' || 'Party' || '</TD></B>'
391 || '</TR>';
392
393
394 for i in csr_persondetails loop
395
396 l_body := l_body || '<TR ALIGN=LEFT>'
397 || '<TD >' || i.f_n|| '</TD>'
398 || '<TD >' || i.e_s_d || '</TD>'
402 || '<TD >' || i.p_id || '</TD>'
399 || '<TD >' || i.e_e_d || '</TD>'
400 || '<TD >' || i.u_p_t || '</TD>'
401 || '<TD >' || i.p_t_id|| '</TD>'
403 || '</TR>';
404 end loop;
405
406 l_body := l_body || '</TABLE>';
407
408 fnd_file.put_line(fnd_file.output,l_body);
409
410 print_blank_lines(10);
411
412
413 -- person type information
414
415 l_body := '<TABLE BORDER=1>';
416
417 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> Person Type Details - per_person_type_usages_f </B></TD><TR>';
418
419 l_body := l_body || '<TR ALIGN=LEFT>'
420 || '<TD ><B>' || 'PTU Id' || '</TD><B>'
421 || '<TD ><B>' || 'Effective Start Date' || '</TD><B>'
422 || '<TD ><B>' || 'Effective End Date' || '</TD><B>'
423 || '<TD ><B>' || 'User Person Type' || '</TD><B>'
424 || '<TD ><B>' || 'System Person Type' || '</TD><B>'
425 || '<TD ><B>' || 'PType Id' || '</TD><B>'
426 || '</TR>';
427
428
429 for i in csr_ptu loop
430
431 l_body := l_body || '<TR ALIGN=LEFT>'
432 || '<TD >' || i.ptu_id || '</TD>'
433 || '<TD >' || i.e_s_d || '</TD>'
434 || '<TD >' || i.e_e_d || '</TD>'
435 || '<TD >' || i.u_p_t || '</TD>'
436 || '<TD >' || i.p_t_id|| '</TD>'
437 || '<TD >' || i.p_id|| '</TD>'
438 || '</TR>';
439 end loop;
440
441 l_body := l_body || '</TABLE>';
442
443 fnd_file.put_line(fnd_file.output,l_body);
444
445 print_blank_lines(10);
446
447
448 -- Address Details
449
450 l_body := '<TABLE BORDER=1>';
451
452 l_body := l_body || '<TR> <TD COLSPAN=4 ALIGN="LEFT"> <B> Address Details - per_addresses </B></TD><TR>';
453
454 l_body := l_body || '<TR ALIGN=LEFT>'
455 || '<TD ><B>' || 'Address Id' || '</TD></B>'
456 || '<TD ><B>' || 'Primary' || '</TD></B>'
457 || '<TD ><B>' || 'City or Town' || '</TD></B>'
458 || '<TD ><B>' || 'County' || '</TD></B>'
459 || '<TD ><B>' || 'State' || '</TD></B>'
460 || '<TD ><B>' || 'Code' || '</TD></B>'
461 || '<TD ><B>' || 'Date From' || '</TD></B>'
462 || '<TD ><B>' || 'Date To' || '</TD></B>'
463 || '<TD ><B>' || 'Party' || '</TD></B>'
464 || '<TD ><B>' || 'Address Type' || '</TD></B>'
465 || '</TR>';
466
467
468 for i in csr_address loop
469
470 l_body := l_body || '<TR ALIGN=LEFT>'
471 || '<TD >' || i.add_id || '</TD>'
472 || '<TD >' || i.p_f || '</TD>'
473 || '<TD >' || i.city || '</TD>'
474 || '<TD >' || i.r_1 || '</TD>'
475 || '<TD >' || i.r_2 || '</TD>'
476 || '<TD >' || i.zip || '</TD>'
477 || '<TD >' || i.d_f|| '</TD>'
478 || '<TD >' || i.d_t|| '</TD>'
479 || '<TD >' || i.p_id|| '</TD>'
480 || '<TD >' || i.a_d || '</TD>'
481 || '</TR>';
482 end loop;
483
484 l_body := l_body || '</TABLE>';
485
486 fnd_file.put_line(fnd_file.output,l_body);
487
488 print_blank_lines(10);
489
490 end; -- write_data
491
492 end per_person_information;