[Home] [Help]
PACKAGE BODY: APPS.HR_DE_WORK_INCIDENT_REPORT
Source
1 PACKAGE BODY hr_de_work_incident_report AS
2 /* $Header: pedewinr.pkb 115.8 2002/11/26 16:33:37 jahobbs noship $ */
3 --
4 --
5 -- Outputs work incidents.
6 --
7 PROCEDURE run_report
8 (errbuf OUT NOCOPY VARCHAR2
9 ,retcode OUT NOCOPY VARCHAR2
10 ,p_business_group_id IN NUMBER
11 ,p_from_date IN VARCHAR2
12 ,p_to_date IN VARCHAR2) IS
13 --
14 --
15 -- Cursor to return all work incident records between two dates.
16 --
17 CURSOR C_report
18 (p_business_group_id NUMBER
19 ,p_from_date VARCHAR2
20 ,p_to_date VARCHAR2) IS
21 SELECT
22 distinct(wi.incident_id) INCIDENT_ID,
23 wi.incident_date INCIDENT_DATE_ORD,
24 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','PERSON_ID'),40,' ') PER, wi.PERSON_ID,
25 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','LAST_NAME'),40,' ') LAN, wi.LAST_NAME,
26 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','FIRST_NAME'),40,' ') FAN, wi.FIRST_NAME,
27 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','DATE_OF_BIRTH'),40,' ') DOB, fnd_date.date_to_chardate(wi.DATE_OF_BIRTH) date_of_birth,
28 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','DATE_OF_INC'),40,' ') IND, fnd_date.date_to_chardate(wi.INCIDENT_DATE) incident_date,
29 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','INC_REF'),40,' ') REF, wi.INCIDENT_REFERENCE,
30 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','GENDER'),40,' ') GEN, hr_general.decode_lookup('SEX',wi.GENDER) gender,
31 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','SOC_INS_NO'),40,' ') SIN, wi.SOCIAL_INSURANCE_NUMBER,
32 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','MAR_STATUS'),40,' ') MAR, hr_general.decode_lookup('MAR_STATUS', wi.MARITAL_STATUS) marital_status,
33 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','NATIONALITY'),40,' ') NAT, hr_general.decode_lookup('NATIONALITY', wi.NATIONALITY) nationality,
34 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','ASSGT_ORG_NAME'),40,' ') ORG, wi.ASSGT_ORG_NAME ,
35 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','BETRIEBS'),40,' ') BET, wi.BETRIEBSNUMBER ,
36 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','SUP_OFF_NAME'),40,' ') SUP, rtrim(substr(wi.SUP_OFF_NAME,1,240)) SUP_OFF,
37 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','LIAB_PROV_NAME'),40,' ') LIP, rtrim(substr(wi.LIAB_PROV_NAME, 1, 240)) LIAB_PROV,
38 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','MEM_NO'),40,' ') MEM, substr(wi.MEMBERSHIP_NO,1,20) MEM_NO,
39 substr(wi.loc_id_of_liab_prov,1,20) LOC_ID,
40 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','ADD1'),40,' ') AD1, rtrim(substr(wi.ADDRESS_LINE_1, 1, 240)) ADD1,
41 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','ADD2'),40,' ') AD2, rtrim(substr(wi.ADDRESS_LINE_2, 1, 240)) ADD2,
42 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','ADD3'),40,' ') AD3, rtrim(substr(wi.ADDRESS_LINE_3, 1, 240)) ADD3,
43 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','TOWN'),40,' ') TOW, rtrim(substr(wi.TOWN_OR_CITY, 1, 150)) TOWN,
44 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','COUNTRY'),40,' ') CTY, rtrim(substr(wi.COUNTRY, 1, 100)) CTRY,
45 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','POSTAL'),40,' ') POC, substr(wi.POSTAL_CODE, 1, 20) POST,
46 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','BODY_PART'),40,' ') BOD, wi.BODY_PART,
47 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','INJURY_TYPE'),40,' ') INJ, hr_general.decode_lookup('INJURY_TYPE', wi.INJURY_TYPE) injury_type,
48 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','DOC_NAME'),40, ' ') DOC, wi.DOCTOR_NAME,
49 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','INC_TYPE'),40,' ') INT, wi.D_INCIDENT_TYPE,
50 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','HOSP_DETAILS'),40,' ') HOS, wi.HOSPITAL_DETAILS,
51 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','INC_TIME'),40,' ') INM, wi.INCIDENT_TIME,
52 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','INC_LOC'),40,' ') LOC, wi.LOCATION_OF_INCIDENT,
53 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','REM_ACTION'),40,' ') REA, wi.REMEDIAL_HS_ACTION,
54 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','CHILD'),40,' ') NOC, wi.NO_OF_CHILDREN,
55 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','JOB'),40,' ') JOB, wi.JOB_TYPE,
56 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','JOB_START_DT'),40,' ') JST, fnd_date.date_to_chardate(fnd_date.canonical_to_date(wi.JOB_START_DATE)) job_start_date,
57 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','COMP_PART'),40,' ') CMP, wi.PART_OF_COMPANY,
58 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','LOANED_EMP'),40,' ') LND, wi.D_LOANED_EMP,
59 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','UNDERAGE'),40,' ') UND, wi.D_UNDERAGE,
60 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','SUPER_EMP'),40,' ') SEM, wi.SUPERVISING_EMPLOYEE,
61 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','REL_TO_OWNER'),40,' ') REL, wi.D_RELATION,
62 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','DUR_PAY_ENT'),40,' ') DUR, fnd_date.date_to_chardate(fnd_date.canonical_to_date(wi.DUR_OF_PAYMENT_ENT)) dur_of_payment_ent,
63 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','DT_START'),40,' ') DTS, fnd_date.date_to_chardate(fnd_date.canonical_to_date(wi.DATE_OF_STOPPING_WORK)) date_of_stopping_work,
64 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','DT_STOP'),40,' ') DTE, fnd_date.date_to_chardate(fnd_date.canonical_to_date(wi.DATE_OF_RESUMING_WORK)) date_of_resuming_work,
65 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','1_DOC_ADDR'),40,' ') FAD, wi.ADDR_OF_DOC_FIRST_CON,
66 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','ADD_DOC'),40,' ') ADO, wi.ADDR_OF_CURR_DOC,
67 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','WT_START'),40,' ') WTS, wi.START_OF_WORK_TIME,
68 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','WT_END'),40,' ') WTE, wi.END_OF_WORK_TIME,
69 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','MK_MACH'),40,' ') MKM, wi.MAKE_OF_MACHINE,
70 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','TP_MACH'),40,' ') TYM, wi.TYPE_OF_MACHINE,
71 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','DT_MACH'),40,' ') DTM, fnd_date.date_to_chardate(fnd_date.canonical_to_date(wi.DATE_OF_MACHINE_BUILD)) date_of_machine_build,
72 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','TECH_SAFE'),40,' ') TES, wi.TECHNICAL_SAFETY_ADV,
73 rpad(hr_general.decode_lookup('DE_REPORT_LABELS','PERS_SAFE'),40,' ') PES, wi.PERSONAL_SAFETY_ADV
74 FROM hr_de_work_incidents_report wi
75 ,per_people_f per
76 WHERE wi.person_id = per.person_id AND
77 per.business_group_id = p_business_group_id AND
78 wi.incident_date BETWEEN per.effective_start_date
79 AND per.effective_end_date AND
80 wi.incident_date BETWEEN fnd_date.canonical_to_date(p_from_date)
81 AND fnd_date.canonical_to_date(p_to_date)
82 ORDER BY incident_date_ord, wi.person_id;
83 --
84 --
85 -- Local Variables.
86 --
87 l_report varchar2(2000);
88 --
89 BEGIN
90 --
91 INSERT INTO fnd_sessions
92 (session_id
93 ,effective_date)
94 VALUES
95 (userenv('sessionid')
96 ,sysdate);
97 --
98 --
99 --
100 -- Print titles on 1st page
101 --
102 fnd_file.new_line(fnd_file.output, 12);
103 fnd_file.put_line(fnd_file.output, '**********************************************************');
104 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_REPORT_LABELS','WI_REP_TITLE'), 40, ' '));
105 fnd_file.put_line(fnd_file.output, '**********************************************************');
106 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_REPORT_LABELS','DT_FROM'), 28, ' ') || ' : '
107 || fnd_date.date_to_chardate(fnd_date.canonical_to_date(p_from_date)));
108 fnd_file.put_line(fnd_file.output, LPAD(hr_general.decode_lookup('DE_REPORT_LABELS','DT_TO'), 28, ' ') || ' : '
109 || fnd_date.date_to_chardate(fnd_date.canonical_to_date(p_to_date)));
110 fnd_file.new_line(fnd_file.output, 45);
111 --
112 --
113 -- Loop through all work incidents between the two dates.
114 --
115 FOR C_rec IN C_report(p_business_group_id, p_from_date, p_to_date) LOOP
116 fnd_file.put_line(fnd_file.output, '----------------------------------------------------------------------------------');
117 fnd_file.put(fnd_file.output, C_rec.LAN); fnd_file.put(fnd_file.output, ' : ');
118 fnd_file.put_line(fnd_file.output, C_rec.LAST_NAME);
119 fnd_file.put(fnd_file.output, C_rec.FAN); fnd_file.put(fnd_file.output, ' : ');
120 fnd_file.put_line(fnd_file.output, C_rec.FIRST_NAME);
121 fnd_file.put(fnd_file.output, C_rec.REF); fnd_file.put(fnd_file.output, ' : ');
122 fnd_file.put_line(fnd_file.output, C_rec.INCIDENT_REFERENCE);
123 fnd_file.put(fnd_file.output, C_rec.IND); fnd_file.put(fnd_file.output, ' : ');
124 fnd_file.put_line(fnd_file.output, C_rec.INCIDENT_DATE);
125 fnd_file.put(fnd_file.output, C_rec.DOB); fnd_file.put(fnd_file.output, ' : ');
126 fnd_file.put_line(fnd_file.output, C_rec.DATE_OF_BIRTH);
127 fnd_file.put(fnd_file.output, C_rec.GEN); fnd_file.put(fnd_file.output, ' : ');
128 fnd_file.put_line(fnd_file.output, C_rec.GENDER);
129 fnd_file.put(fnd_file.output, C_rec.SIN); fnd_file.put(fnd_file.output, ' : ');
130 fnd_file.put_line(fnd_file.output, C_rec.SOCIAL_INSURANCE_NUMBER);
131 fnd_file.put(fnd_file.output, C_rec.MAR); fnd_file.put(fnd_file.output, ' : ');
132 fnd_file.put_line(fnd_file.output, C_rec.MARITAL_STATUS);
133 fnd_file.put(fnd_file.output, C_rec.NAT); fnd_file.put(fnd_file.output, ' : ');
134 fnd_file.put_line(fnd_file.output, C_rec.NATIONALITY);
135 fnd_file.put(fnd_file.output, C_rec.ORG); fnd_file.put(fnd_file.output, ' : ');
136 fnd_file.put_line(fnd_file.output, C_rec.ASSGT_ORG_NAME);
137 fnd_file.put(fnd_file.output, C_rec.BET); fnd_file.put(fnd_file.output, ' : ');
138 fnd_file.put_line(fnd_file.output, C_rec.BETRIEBSNUMBER);
139 fnd_file.put(fnd_file.output, C_rec.SUP); fnd_file.put(fnd_file.output, ' : ');
140 fnd_file.put_line(fnd_file.output, C_rec.SUP_OFF);
141 fnd_file.put(fnd_file.output, C_rec.LIP); fnd_file.put(fnd_file.output, ' : ');
142 fnd_file.put_line(fnd_file.output, C_rec.LIAB_PROV);
143 fnd_file.put(fnd_file.output, C_rec.MEM); fnd_file.put(fnd_file.output, ' : ');
144 fnd_file.put_line(fnd_file.output, C_rec.MEM_NO);
145 fnd_file.put(fnd_file.output, C_rec.AD1); fnd_file.put(fnd_file.output, ' : ');
146 fnd_file.put_line(fnd_file.output, C_rec.ADD1);
147 fnd_file.put(fnd_file.output, C_rec.AD2); fnd_file.put(fnd_file.output, ' : ');
148 fnd_file.put_line(fnd_file.output, C_rec.ADD2);
149 fnd_file.put(fnd_file.output, C_rec.AD3); fnd_file.put(fnd_file.output, ' : ');
150 fnd_file.put_line(fnd_file.output, C_rec.ADD3);
151 fnd_file.put(fnd_file.output, C_rec.TOW); fnd_file.put(fnd_file.output, ' : ');
152 fnd_file.put_line(fnd_file.output, C_rec.TOWN);
153 fnd_file.put(fnd_file.output, C_rec.CTY); fnd_file.put(fnd_file.output, ' : ');
154 fnd_file.put_line(fnd_file.output, C_rec.CTRY);
155 fnd_file.put(fnd_file.output, C_rec.POC); fnd_file.put(fnd_file.output, ' : ');
156 fnd_file.put_line(fnd_file.output, C_rec.POST);
157 fnd_file.put(fnd_file.output, C_rec.BOD); fnd_file.put(fnd_file.output, ' : ');
158 fnd_file.put_line(fnd_file.output, C_rec.BODY_PART);
159 fnd_file.put(fnd_file.output, C_rec.INJ); fnd_file.put(fnd_file.output, ' : ');
160 fnd_file.put_line(fnd_file.output, C_rec.INJURY_TYPE);
161 fnd_file.put(fnd_file.output, C_rec.DOC); fnd_file.put(fnd_file.output, ' : ');
162 fnd_file.put_line(fnd_file.output, C_rec.DOCTOR_NAME);
163 fnd_file.put(fnd_file.output, C_rec.INT); fnd_file.put(fnd_file.output, ' : ');
164 fnd_file.put_line(fnd_file.output, C_rec.D_INCIDENT_TYPE);
165 fnd_file.put(fnd_file.output, C_rec.HOS); fnd_file.put(fnd_file.output, ' : ');
166 fnd_file.put_line(fnd_file.output, C_rec.HOSPITAL_DETAILS);
167 fnd_file.put(fnd_file.output, C_rec.INM); fnd_file.put(fnd_file.output, ' : ');
168 fnd_file.put_line(fnd_file.output, C_rec.INCIDENT_TIME);
169 fnd_file.put(fnd_file.output, C_rec.LOC); fnd_file.put(fnd_file.output, ' : ');
170 fnd_file.put_line(fnd_file.output, C_rec.LOCATION_OF_INCIDENT);
171 fnd_file.put(fnd_file.output, C_rec.REA); fnd_file.put(fnd_file.output, ' : ');
172 fnd_file.put_line(fnd_file.output, C_rec.REMEDIAL_HS_ACTION);
173 fnd_file.put(fnd_file.output, C_rec.NOC); fnd_file.put(fnd_file.output, ' : ');
174 fnd_file.put_line(fnd_file.output, C_rec.NO_OF_CHILDREN);
175 fnd_file.put(fnd_file.output, C_rec.JOB); fnd_file.put(fnd_file.output, ' : ');
176 fnd_file.put_line(fnd_file.output, C_rec.JOB_TYPE);
177 fnd_file.put(fnd_file.output, C_rec.JST); fnd_file.put(fnd_file.output, ' : ');
178 fnd_file.put_line(fnd_file.output, C_rec.JOB_START_DATE);
179 fnd_file.put(fnd_file.output, C_rec.CMP); fnd_file.put(fnd_file.output, ' : ');
180 fnd_file.put_line(fnd_file.output, C_rec.PART_OF_COMPANY);
181 fnd_file.put(fnd_file.output, C_rec.LND); fnd_file.put(fnd_file.output, ' : ');
182 fnd_file.put_line(fnd_file.output, C_rec.D_LOANED_EMP);
183 fnd_file.put(fnd_file.output, C_rec.UND); fnd_file.put(fnd_file.output, ' : ');
184 fnd_file.put_line(fnd_file.output, C_rec.D_UNDERAGE);
185 fnd_file.put(fnd_file.output, C_rec.SEM); fnd_file.put(fnd_file.output, ' : ');
186 fnd_file.put_line(fnd_file.output, C_rec.SUPERVISING_EMPLOYEE);
187 fnd_file.put(fnd_file.output, C_rec.REL); fnd_file.put(fnd_file.output, ' : ');
188 fnd_file.put_line(fnd_file.output, C_rec.D_RELATION);
189 fnd_file.put(fnd_file.output, C_rec.DUR); fnd_file.put(fnd_file.output, ' : ');
190 fnd_file.put_line(fnd_file.output, C_rec.DUR_OF_PAYMENT_ENT);
191 fnd_file.put(fnd_file.output, C_rec.DTS); fnd_file.put(fnd_file.output, ' : ');
192 fnd_file.put_line(fnd_file.output, C_rec.DATE_OF_STOPPING_WORK);
193 fnd_file.put(fnd_file.output, C_rec.DTE); fnd_file.put(fnd_file.output, ' : ');
194 fnd_file.put_line(fnd_file.output, C_rec.DATE_OF_RESUMING_WORK);
195 fnd_file.put(fnd_file.output, C_rec.FAD); fnd_file.put(fnd_file.output, ' : ');
196 fnd_file.put_line(fnd_file.output, C_rec.ADDR_OF_DOC_FIRST_CON);
197 fnd_file.put(fnd_file.output, C_rec.ADO); fnd_file.put(fnd_file.output, ' : ');
198 fnd_file.put_line(fnd_file.output, C_rec.ADDR_OF_CURR_DOC);
199 fnd_file.put(fnd_file.output, C_rec.WTS); fnd_file.put(fnd_file.output, ' : ');
200 fnd_file.put_line(fnd_file.output, C_rec.START_OF_WORK_TIME);
201 fnd_file.put(fnd_file.output, C_rec.WTE); fnd_file.put(fnd_file.output, ' : ');
202 fnd_file.put_line(fnd_file.output, C_rec.END_OF_WORK_TIME);
203 fnd_file.put(fnd_file.output, C_rec.MKM); fnd_file.put(fnd_file.output, ' : ');
204 fnd_file.put_line(fnd_file.output, C_rec.MAKE_OF_MACHINE);
205 fnd_file.put(fnd_file.output, C_rec.TYM); fnd_file.put(fnd_file.output, ' : ');
206 fnd_file.put_line(fnd_file.output, C_rec.TYPE_OF_MACHINE);
207 fnd_file.put(fnd_file.output, C_rec.DTM); fnd_file.put(fnd_file.output, ' : ');
208 fnd_file.put_line(fnd_file.output, C_rec.DATE_OF_MACHINE_BUILD);
209 fnd_file.put(fnd_file.output, C_rec.TES); fnd_file.put(fnd_file.output, ' : ');
210 fnd_file.put_line(fnd_file.output, C_rec.TECHNICAL_SAFETY_ADV);
211 fnd_file.put(fnd_file.output, C_rec.PES); fnd_file.put(fnd_file.output, ' : ');
212 fnd_file.put_line(fnd_file.output, C_rec.PERSONAL_SAFETY_ADV);
213 fnd_file.put_line(fnd_file.output, '----------------------------------------------------------------------------------');
214 --
215 --
216 -- An A4 page has 62 lines. As 50 lines have been printed, Print 12 blank lines so that
217 -- new record shows on a new page.
218 --
219 fnd_file.new_line(fnd_file.output, 12);
220 END LOOP;
221 END run_report;
222 END hr_de_work_incident_report;