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);
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);
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);
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);
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);
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);
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);
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:
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;
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:
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: /***********************************************************************
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.
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.
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;
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);
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:
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 */
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 */
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:
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:
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: ***********************************************************************/
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:
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;
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;
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;
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;