eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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>'
399                 || '<TD >' || i.e_e_d || '</TD>'
400                 || '<TD >' || i.u_p_t || '</TD>'
401                 || '<TD >' || i.p_t_id|| '</TD>'
402                 || '<TD >' || i.p_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;