1 PACKAGE BODY PER_FR_MEDICAL_EXAMS_UPG_PKG AS
2 /* $Header: pefrmeup.pkb 115.1 2002/07/03 06:47:56 pvaish noship $ */
3
4 CURSOR csr_medic_exam(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.PERSON_EXTRA_INFO_ID
19 FROM per_people_extra_info pei
20 , per_all_people_f per1
21 WHERE pei.PEI_INFORMATION_CATEGORY = 'FR_MEDIC_EXAM'
22 AND pei.person_id = per1.person_id
23 AND per1.business_group_id = p_business_group_id
24 AND per1.effective_start_date = (select min(per2.effective_start_date)
25 from per_all_people_f per2
26 where per2.person_id = per1.person_id
27 and per2.business_group_id = p_business_group_id)
28 AND pei.PEI_INFORMATION30 IS NULL
29 order by per1.full_name;
30
31
32 g_package varchar2(30) := 'per_fr_medical_exams_upg_pkg';
33
34
35 /********************************************************************************
36 * Procedure that writes out the whole medical examination information to the *
37 * log this allows users to mannual enter this information where it could not be*
38 * created by the process *
39 ********************************************************************************/
40 procedure write_medical_exam_to_log(p_medical_exam in csr_medic_exam%ROWTYPE)
41 IS
42 BEGIN
43 per_fr_upgrade_data_pkg.write_log(p_medical_exam.employee_number);
44 /* added script to print all medical assessment information to log*/
45 per_fr_upgrade_data_pkg.write_log(p_medical_exam.full_name);
46 per_fr_upgrade_data_pkg.write_log(p_medical_exam.PEI_INFORMATION1);
47 per_fr_upgrade_data_pkg.write_log(p_medical_exam.PEI_INFORMATION2);
48 per_fr_upgrade_data_pkg.write_log(p_medical_exam.PEI_INFORMATION3);
49 per_fr_upgrade_data_pkg.write_log(p_medical_exam.PEI_INFORMATION4);
50 per_fr_upgrade_data_pkg.write_log(p_medical_exam.PEI_INFORMATION5);
51 per_fr_upgrade_data_pkg.write_log(p_medical_exam.PEI_INFORMATION6);
52 per_fr_upgrade_data_pkg.write_log(p_medical_exam.PEI_INFORMATION7);
53 per_fr_upgrade_data_pkg.write_log(p_medical_exam.PEI_INFORMATION8);
54
55 END write_medical_exam_to_log;
56
57 /***********************************************************************
58 * function TRANSFER_DATA *
59 * This fucntion must be called from run_upgrade *
60 * Return = 0 means upgrade completed OK. *
61 * Return = 1 means warnings *
62 * Return = 2 means upgrade failed *
63 ***********************************************************************/
64 function transfer_data(p_business_group_id IN NUMBER) return number
65 IS
66
67 l_medical_exam csr_medic_exam%ROWTYPE;
68 l_consultation_date date;
69 l_next_consultation_date date;
70 l_consultation_type varchar2(30);
71 l_medical_assessment_id number;
72 l_ovn number;
73 l_proc varchar2(72) := g_package||'.transfer_data';
74 l_run_status number :=0; /* Status of the whole run */
75
76 BEGIN
77
78 hr_utility.set_location('Entered '||l_proc,5);
79
80 OPEN csr_medic_exam(p_business_group_id);
81 FETCH csr_medic_exam INTO l_medical_exam;
82
83 WHILE csr_medic_exam%FOUND LOOP
84
85 if l_medical_exam.PEI_INFORMATION1 is NULL then
86 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74996_MED_DATE_DFLT',
87 p_token1 => 'EMPLOYEE:'|| l_medical_exam.employee_number);
88 l_consultation_date :=to_date('01010001','DDMMYYYY');
89 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
90 l_run_status := 1; -- Set status of run to warning.
91 end if;
92 else
93 l_consultation_date :=fnd_date.canonical_to_date(l_medical_exam.PEI_INFORMATION1);
94 end if;
95
96
97 l_next_consultation_date := fnd_date.canonical_to_date(l_medical_exam.PEI_INFORMATION8);
98
99 if l_next_consultation_date <= l_consultation_date then
100 -- Next consultation date must be after consultation date. Other set to NULL.
101 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74997_MED_NDATE_DFLT',
102 p_token1 => 'EMPLOYEE:'|| l_medical_exam.employee_number);
103 l_next_consultation_date := NULL;
104 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
105 l_run_status := 1; -- Set status of run to warning.
106 end if;
107 END IF;
108
109 if l_medical_exam.PEI_INFORMATION2 IS NULL then
110 l_consultation_type := 'UNKNOWN';
111 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74998_MED_TYPE_DFLT',
112 p_token1 => 'EMPLOYEE:'|| l_medical_exam.employee_number);
113 if l_run_status = 0 THEN /* only change status if not 1 or 2 already */
114 l_run_status := 1; -- Set status of run to warning.
115 end if;
116 else
117 l_consultation_type := l_medical_exam.PEI_INFORMATION2;
118 end if;
119
120 BEGIN -- Insert new medical examinations section
121
122 SAVEPOINT start_insert;
123
124 per_medical_assessment_api.create_medical_assessment
125 (p_effective_date => l_medical_exam.effective_start_date
126 ,p_person_id => l_medical_exam.person_id
127 ,p_consultation_date => l_consultation_date
128 ,p_consultation_type => l_consultation_type
129 ,p_examiner_name => l_medical_exam.PEI_INFORMATION7
130 ,p_consultation_result => l_medical_exam.PEI_INFORMATION5
131 ,p_next_consultation_date => l_next_consultation_date
132 ,p_description => l_medical_exam.PEI_INFORMATION6
133 ,p_mea_information_category => 'FR'
134 ,p_mea_information1 => l_medical_exam.PEI_INFORMATION3
135 ,p_mea_information2 => l_medical_exam.PEI_INFORMATION4
136 ,p_medical_assessment_id => l_medical_assessment_id
137 ,p_object_version_number => l_ovn);
138
139 update per_people_extra_info
140 set PEI_INFORMATION30 = to_char(l_medical_assessment_id)
141 where person_id = l_medical_exam.person_id
142 and PEI_INFORMATION_CATEGORY = 'FR_MEDIC_EXAM'
143 and PERSON_EXTRA_INFO_ID = l_medical_exam.PERSON_EXTRA_INFO_ID;
144
145 exception when others then
146 rollback to start_insert;
147 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74999_MED_UPG_FATAL'
148 ,p_token1 => 'TOKEN1:10');
149 write_medical_exam_to_log(l_medical_exam);
150 per_fr_upgrade_data_pkg.write_log(sqlcode);
151 per_fr_upgrade_data_pkg.write_log(sqlerrm);
152 l_run_status := 2; /* Fatal Error */
153 END; -- end of section inserting new medical exam
154
155
156 /* Commit every record to ensure conc log corresponds to records in DB */
157 commit;
158
159 FETCH csr_medic_exam INTO l_medical_exam;
160 END LOOP;
161 CLOSE csr_medic_exam;
162
163 return l_run_status;
164
165 exception when others then
166 rollback;
167 CLOSE csr_medic_exam;
168 per_fr_upgrade_data_pkg.write_log_message(p_message_name => 'PER_74999_MED_UPG_FATAL'
169 ,p_token1 => 'TOKEN1:50');
170 per_fr_upgrade_data_pkg.write_log(sqlcode);
171 per_fr_upgrade_data_pkg.write_log(sqlerrm);
172 return 2; /* Fatal Error */
173 END transfer_data;
174
175
176 /***********************************************************************
177 * function RUN_UPGRADE *
178 * This fucntion must be called from *
179 * per_fr_upgrade_data_pkg.run_upgrade *
180 * return = 0 for Status Normal *
181 * return = 1 for Status Warning *
182 * return = 2 for Status Error *
183 ***********************************************************************/
184 function run_upgrade(p_business_group_id number) return number
185 IS
186 l_status number :=0;
187 l_error_status number :=0;
188 begin
189 l_status := per_fr_upgrade_data_pkg.check_lookups(p_fr_lookup_type => 'FR_MEDICAL_EXAMINATION_TYPE'
190 ,p_core_lookup_type => 'CONSULTATION_TYPE');
191
192 l_error_status := l_status;
193
194 l_status := per_fr_upgrade_data_pkg.check_lookups(p_fr_lookup_type => 'FR_MEDICAL_RESULT'
195 ,p_core_lookup_type => 'CONSULTATION_RESULT');
196
197 if l_status > 0 then
198 l_error_status := l_status;
199 end if;
200
201 /* Lookups checked, Check for DF*/
202 l_status := per_fr_upgrade_data_pkg.check_dfs(p_df => 'PER_MEDICAL_ASSESSMENTS');
203
204 if l_status > 0 then
205 l_error_status := l_status;
206 end if;
207
208 if l_error_status = 0 then
209 /* Lookups and DFs checked OK, upgrade data */
210 l_status := transfer_data(p_business_group_id => p_business_group_id);
211 RETURN l_status;
212 else
213 /* If DF check fails then fatal error */
214 RETURN 2;
215 end if;
216
217 /* Allow exceptions to be handled by calling unit do not trap here. */
218
219 end run_upgrade;
220
221
222 END PER_FR_MEDICAL_EXAMS_UPG_PKG;
[Home] [Help]