[Home] [Help]
PACKAGE BODY: APPS.PER_FR_WORK_ACC_UPG_PKG
Source
1 PACKAGE BODY PER_FR_WORK_ACC_UPG_PKG AS
2 /* $Header: pefrwaup.pkb 115.1 2002/07/03 06:46:24 pvaish noship $ */
3
4 CURSOR csr_work_accidents(p_business_group_id number)
5 IS
6 SELECT pei.PERSON_ID,
7 per1.full_name,
8 per1.employee_number,
9 per1.effective_start_date,
10 pei.PEI_INFORMATION1,
11 pei.PEI_INFORMATION2,
12 pei.PEI_INFORMATION3,
13 pei.PEI_INFORMATION4,
14 pei.PEI_INFORMATION5,
15 pei.PEI_INFORMATION6,
16 pei.PEI_INFORMATION7,
17 pei.PEI_INFORMATION8,
18 pei.PEI_INFORMATION9,
19 pei.PERSON_EXTRA_INFO_ID
20 FROM per_people_extra_info pei
21 , per_all_people_f per1
22 WHERE pei.PEI_INFORMATION_CATEGORY = 'FR_WORK_ACCI'
23 AND pei.person_id = per1.person_id
24 AND per1.business_group_id = p_business_group_id
25 AND per1.effective_start_date = (select min(per2.effective_start_date)
26 from per_all_people_f per2
27 where per2.person_id = per1.person_id
28 and per2.business_group_id = p_business_group_id)
29 AND pei.PEI_INFORMATION30 IS NULL
30 order by per1.full_name;
31
32
33 g_package varchar2(30) := 'per_fr_work_acc_upg_pkg';
34
35
36 /********************************************************************************
37 * Procedure that writes out the whole work incidents information to the *
38 * log this allows users to mannual enter this information where it could not be*
39 * created by the process *
40 ********************************************************************************/
41 procedure write_work_accident_to_log(p_work_acci in csr_work_accidents%ROWTYPE)
42 IS
43 BEGIN
44 per_fr_upgrade_data_pkg.write_log(p_work_acci.employee_number);
45 /* added script to print all work incidents information to log*/
46 per_fr_upgrade_data_pkg.write_log(p_work_acci.full_name);
47 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION1);
48 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION2);
49 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION3);
50 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION4);
51 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION5);
52 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION6);
53 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION7);
54 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION8);
55 per_fr_upgrade_data_pkg.write_log(p_work_acci.PEI_INFORMATION9);
56
57 END write_work_accident_to_log;
58
59 /***********************************************************************
60 * function TRANSFER_DATA *
61 * This fucntion must be called from run_upgrade *
62 * RETURN = 0 means upgrade completed OK. *
63 * RETURN = 1 means warnings *
64 * RETURN = 2 means upgrade failed *
65 ***********************************************************************/
66 function transfer_data(p_business_group_id IN NUMBER) RETURN number
67 IS
68
69 l_work_acci csr_work_accidents%ROWTYPE;
70 l_incident_id number;
71 l_incident_date date;
72 l_incident_type varchar2(30);
73 l_org_notified_date date;
74 l_body_part varchar2(30);
75 l_ovn number;
76 l_activity varchar2(30);
77 l_absence_exists_flag varchar2(30);
78 l_proc varchar2(72) := g_package||'.transfer_data';
79 l_run_status number :=0; /* Status of the whole run */
80
81 BEGIN
82
83 hr_utility.set_location('Entered '||l_proc,5);
84
85 OPEN csr_work_accidents(p_business_group_id);
86 FETCH csr_work_accidents INTO l_work_acci;
87
88 WHILE csr_work_accidents%FOUND LOOP
89
90 if l_work_acci.PEI_INFORMATION1 is NULL then
91 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_75000_WA_ACTIVITY_DFLT',
92 p_token1 => 'EMPLOYEE:'|| l_work_acci.full_name);
93 l_activity := 'W';
94 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
95 l_run_status := 1; -- Set status of run to warning.
96 end if;
97 else
98 l_activity :=l_work_acci.PEI_INFORMATION1;
99 end if;
100
101 if l_work_acci.PEI_INFORMATION3 is NULL then
102 l_incident_date :=to_date('01010001','DDMMYYYY');
103
104 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_75001_WA_INCI_DATE_DFLT',
105 p_token1 => 'EMPLOYEE:'|| l_work_acci.full_name);
106
107 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
108 l_run_status := 1; -- Set status of run to warning.
109 end if;
110 else
111 l_incident_date :=fnd_date.canonical_to_date(l_work_acci.PEI_INFORMATION3);
112 end if;
113
114 if l_work_acci.PEI_INFORMATION4 is NOT NULL then
115 l_body_part := l_work_acci.PEI_INFORMATION4||'%';
116 else
117 l_body_part :=NULL;
118 end if;
119
120 if l_work_acci.PEI_INFORMATION7 IS NULL then
121 l_incident_type := 'UNKNOWN';
122 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_75002_WA_INCI_TYPE_DFLT',
123 p_token1 => 'EMPLOYEE:'|| l_work_acci.full_name);
124 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
125 l_run_status := 1; -- Set status of run to warning.
126 end if;
127 else
128 l_incident_type := l_work_acci.PEI_INFORMATION7;
129 end if;
130
131 if l_work_acci.PEI_INFORMATION8 = 'Y' then
132 l_org_notified_date := l_incident_date;
133 else
134 l_org_notified_date := NULL;
135 end if;
136
137 if l_work_acci.PEI_INFORMATION9 = 'Y' then
138 l_absence_exists_flag := 'Y';
139 else
140 l_absence_exists_flag := 'N';
141 end if;
142
143 BEGIN -- Insert newwork inccidents section
144
145 SAVEPOINT start_insert;
146
147 per_work_incident_api.create_work_incident
148 (p_effective_date => l_work_acci.effective_start_date
149 ,p_person_id => l_work_acci.person_id
150 ,p_incident_reference => 'FR' || TO_CHAR(l_work_acci.PERSON_EXTRA_INFO_ID)
151 ,p_at_work_flag => l_activity
152 ,p_hazard_type => l_work_acci.PEI_INFORMATION2
153 ,p_incident_date => l_incident_date
154 ,p_body_part => l_body_part
155 ,p_description => l_work_acci.PEI_INFORMATION5
156 ,p_disease_type => l_work_acci.PEI_INFORMATION6
157 ,p_incident_type => l_incident_type
158 ,p_org_notified_date => l_org_notified_date
159 ,p_absence_exists_flag => l_absence_exists_flag
160 ,p_incident_id => l_incident_id
161 ,p_object_version_number => l_ovn);
162
163 update per_people_extra_info
164 set PEI_INFORMATION30 = to_char(l_incident_id)
165 where person_id = l_work_acci.person_id
166 and PEI_INFORMATION_CATEGORY = 'FR_WORK_ACCI'
167 and PERSON_EXTRA_INFO_ID = l_work_Acci.PERSON_EXTRA_INFO_ID;
168
169 exception when others then
170 rollback to start_insert;
171 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_75003_WA_UPG_FATAL'
172 ,p_token1 => 'TOKEN1:10');
173 write_work_accident_to_log(l_work_acci);
174 per_fr_upgrade_data_pkg.write_log(sqlcode);
175 per_fr_upgrade_data_pkg.write_log(sqlerrm);
176 l_run_status := 2; /* Fatal Error */
177 END; -- end of section inserting new work inccident
178
179 /* Commit every record to ensure conc log corresponds to records in DB */
180 COMMIT;
181
182 FETCH csr_work_accidents INTO l_work_acci;
183 END LOOP;
184 CLOSE csr_work_accidents;
185
186 RETURN l_run_status;
187
188 exception when others then
189 ROLLBACK;
190 CLOSE csr_work_accidents;
191 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_75003_WA_UPG_FATAL'
192 ,p_token1 => 'STEP:50');
193 per_fr_upgrade_data_pkg.write_log(sqlcode);
194 per_fr_upgrade_data_pkg.write_log(sqlerrm);
195 RETURN 2; /* Fatal Error */
196 END transfer_data;
197
198
199 /***********************************************************************
200 * function RUN_UPGRADE *
201 * This fucntion must be called from *
202 * per_fr_upgrade_data_pkg.run_upgrade *
203 * RETURN = 0 for Status Normal *
204 * RETURN = 1 for Status Warning *
205 * RETURN = 2 for Status Error *
206 ***********************************************************************/
207 function run_upgrade(p_business_group_id number) RETURN number
208 IS
209 l_status number :=0;
210 l_error_status number :=0;
211 l_proc varchar2(72) := g_package||'.run_upgrade';
212 begin
213 hr_utility.set_location('Entered ' || l_proc,5);
214 l_status := per_fr_upgrade_data_pkg.check_lookups(p_fr_lookup_type => 'FR_WORK_ACCIDENT_RESULT'
215 ,p_core_lookup_type => 'INCIDENT_TYPE');
216
217 l_error_status := l_status;
218
219 l_status := per_fr_upgrade_data_pkg.check_lookups(p_fr_lookup_type => 'FR_WORK_ACCIDENT_TYPE'
220 ,p_core_lookup_type => 'AT_WORK_FLAG');
221
222 if l_status > 0 then
223 l_error_status := l_status;
224 end if;
225
226 l_status := per_fr_upgrade_data_pkg.check_lookups(p_fr_lookup_type => 'FR_ILLNESS_TYPE'
227 ,p_core_lookup_type => 'DISEASE_TYPE');
228
229 if l_status > 0 then
230 l_error_status := l_status;
231 end if;
232
233 l_status := per_fr_upgrade_data_pkg.check_lookups(p_fr_lookup_type => 'FR_WORK_ACCIDENT_CODE'
234 ,p_core_lookup_type => 'HAZARD_TYPE');
235
236 if l_status > 0 then
237 l_error_status := l_status;
238 end if;
239
240 /* Lookups checked, Check for DF*/
241 l_status := per_fr_upgrade_data_pkg.check_dfs(p_df => 'PER_WORK_INCIDENTS');
242
243 if l_status > 0 then
244 l_error_status := l_status;
245 end if;
246
247 if l_error_status = 0 then
248 /* DFs checked OK, upgrade data */
249 l_status := transfer_data(p_business_group_id => p_business_group_id);
250 RETURN l_status;
251 else
252 /* If DF check fails then fatal error */
253 RETURN 2;
254 end if;
255
256 /* Allow exceptions to be handled by calling unit do not trap here. */
257
258 end run_upgrade;
259
260 END PER_FR_WORK_ACC_UPG_PKG;