1 PACKAGE BODY PER_FR_UPGRADE_DATA_PKG AS
2 /* $Header: perfrupd.pkb 115.5 2002/11/25 16:35:34 sfmorris noship $ */
3
4 g_package varchar2(30) := 'per_fr_upgrade_data_pkg';
5
6
7 ------------------------------------------------------------------------
8 -- PROCEDURE WRITE_LOG
9 -- This PROCEDURE writes a text string to the concurrent manager log
10 ------------------------------------------------------------------------
11 procedure write_log(p_message in varchar2)
12 is
13 l_proc varchar2(72) := g_package||'.write_log';
14 --
15 begin
16 --
17 hr_utility.set_location('Entered '||l_proc,5);
18
19 fnd_file.put_line(FND_FILE.LOG,substrb(p_message,1,1024));
20
21 hr_utility.set_location('leaving '||l_proc,100);
22
23 exception when others then
24 null;
25 end write_log;
26
27 ------------------------------------------------------------------------
28 -- PROCEDURE WRITE_LOG_MESSAGE
29 -- This PROCEDURE is used to obtain a fnd message and write it out to
30 -- the concurrent manager log file.
31 -- The token parameters must be of the form 'TOKEN_NAME:TOKEN_VALUE' i.e.
32 -- If you want to set the value of a token called EMPLOYEE to 'Bill Smith'
33 -- the token parameter would be 'EMPLOYEE:Bill Smith'
34 ------------------------------------------------------------------------
35 procedure write_log_message(p_message_name in varchar2
36 ,p_token1 in varchar2
37 ,p_token2 in varchar2
38 ,p_token3 in varchar2)
39 is
40 l_message varchar2(2000);
41 l_token_name varchar2(20);
42 l_token_value varchar2(80);
43 l_colon_position number;
44 l_proc varchar2(72) := g_package||'.write_log_message';
45 --
46 begin
47 --
48 hr_utility.set_location('Entered '||l_proc,5);
49 hr_utility.set_location('. Message Name: '||p_message_name,40);
50
51 fnd_message.set_name('PER', p_message_name);
52
53 if p_token1 is not null then
54 /* Obtain token 1 name and value */
55 l_colon_position := instr(p_token1,':');
56 l_token_name := substr(p_token1,1,l_colon_position-1);
57 l_token_value := substr(p_token1,l_colon_position+1,length(p_token1));
58 fnd_message.set_token(l_token_name, l_token_value);
59 hr_utility.set_location('. Token1: '||l_token_name||'. Value: '||l_token_value,50);
60 end if;
61
62 if p_token2 is not null then
63 /* Obtain token 2 name and value */
64 l_colon_position := instr(p_token2,':');
65 l_token_name := substr(p_token2,1,l_colon_position-1);
66 l_token_value := substr(p_token2,l_colon_position+1,length(p_token2));
67 fnd_message.set_token(l_token_name, l_token_value);
68 hr_utility.set_location('. Token2: '||l_token_name||'. Value: '||l_token_value,60);
69 end if;
70
71 if p_token3 is not null then
72 /* Obtain token 3 name and value */
73 l_colon_position := instr(p_token3,':');
74 l_token_name := substr(p_token3,1,l_colon_position-1);
75 l_token_value := substr(p_token3,l_colon_position+1,length(p_token3));
76 fnd_message.set_token(l_token_name, l_token_value);
77 hr_utility.set_location('. Token3: '||l_token_name||'. Value: '||l_token_value,70);
78 end if;
79
80 l_message := fnd_message.get;
81
82 fnd_file.put_line(FND_FILE.LOG,substrb(l_message,1,1024));
83
84 hr_utility.set_location('leaving '||l_proc,100);
85
86 exception when others then
87 null;
88 hr_utility.set_location('error occured in:'||l_proc,200);
89 hr_utility.set_location(sqlcode,210);
90 end write_log_message;
91
92
93 /*************************************************************
94 * Function GET_TRANSLATION *
95 * This function gets the meaning for values that need to be *
96 * tranlated before they are written to the log *
97 * Using the lookup NAME_TRANSLATIONS *
98 *************************************************************/
99 function get_translation(p_lookup_code in varchar2) return varchar2
100 IS
101 l_proc varchar2(72) := g_package||'.get_translation';
102 l_meaning hr_lookups.meaning%type := hr_general.decode_lookup(p_lookup_type => 'NAME_TRANSLATIONS'
103 ,p_lookup_code => p_lookup_code);
104 --
105 begin
106 --
107 hr_utility.set_location('Entered '||l_proc,5);
108
109 return l_meaning;
110
111 END get_translation;
112
113
114 /*******************************************************************
115 * CHECK_LOOKUPS *
116 * This function takes a french lookup type and a core lookup type *
117 * It checks that any lookup codes in the french lookup exist in *
118 * the core lookup. It takes into account active dates and *
119 * enabled_flag *
120 * Return = 0 means OK *
121 * Return = 1 means an error occured. *
122 *******************************************************************/
123 function check_lookups(p_fr_lookup_type in varchar2,
124 p_core_lookup_type in varchar2) return number
125 IS
126 --
127 /* Find which lookups are in the french lookup but not the core lookup
128 Note: fnd_lookup_values is used for the source table as we need to check
129 lookups for any dates. hr_lookups only gets lookup at session date
130 The destination table is hr_leg_lookups. This is necessary as some
131 APIs validate to this view and we need to ensure that the TAG column
132 is taken into account. This view obtains at session date, this is
133 acceptable as the API will expect the lookups to be available on
134 session date. */
135 cursor csr_missing_lookup
136 IS
137 select lup1.lookup_code
138 ,lup1.meaning
139 from fnd_lookup_values lup1
140 where lup1.lookup_type = p_fr_lookup_type
141 and NOT exists (select lup2.lookup_code
142 from hr_leg_lookups lup2
143 where lup2.lookup_type = p_core_lookup_type
144 and lup2.lookup_code = lup1.lookup_code
145 and lup2.enabled_flag = lup1.enabled_flag
146 and nvl(lup2.start_date_active,to_date('01010001','DDMMYYYY')) <= nvl(lup1.start_date_active,to_date('01010001','DDMMYYYY'))
147 and nvl(lup2.end_date_active,to_date('31124712','DDMMYYYY')) >= nvl(lup1.end_date_active,to_date('01010001','DDMMYYYY'))
148 );
149 --
150 l_counter number :=0;
151 l_proc varchar2(72) := g_package||'.check_lookups';
152 --
153 begin
154 --
155 hr_utility.set_location('Entered '||l_proc,5);
156
157 write_log_message(p_message_name => 'PER_74988_STRT_CHK_LKP'
158 ,p_token1 => 'FR_LKP_TYPE:'||p_fr_lookup_type
159 ,p_token2 => 'CORE_LKP_TYPE:'||p_core_lookup_type);
160
161 for l_lookup_rec in csr_missing_lookup LOOP
162 --
163 l_counter:= l_counter +1;
164 write_log_message(p_message_name => 'PER_74989_MISS_LKP'
165 , p_token1 => 'LOOKUP_CODE:'||l_lookup_rec.lookup_code);
166 --
167 END LOOP;
168
169 if l_counter =0 then -- counter =0 means all lookups found
170 write_log_message(p_message_name => 'PER_74990_END_CHK_LKP'
171 ,p_token1 => 'STATUS:'||get_translation('COMPLETE'));
172
173 else -- Otherwise some lookups were missing so error
174 write_log_message(p_message_name => 'PER_74990_END_CHK_LKP'
175 ,p_token1 => 'STATUS:'||get_translation('ERROR'));
176 end if;
177
178 hr_utility.set_location('leaving '||l_proc,100);
179 return l_counter;
180
181 end check_lookups;
182
183 /*******************************************************************
184 * CHECK_DFS *
185 * This function takes a DF and checks whether the Segment values *
186 * validate against the Value Set associated. *
187 * Return = 0 means OK *
188 * Return = 1 means an error occured. *
189 *******************************************************************/
190 function check_dfs(p_df in varchar2) return number
191 IS
192 --
193 /* Find which DF has required segments.*/
194 cursor csr_required_df_seg(p_df varchar2, p_application_id number, p_table_id number)
195 IS
196 SELECT g.descriptive_flex_context_code,
197 g.end_user_column_name
198 FROM fnd_descr_flex_column_usages g, fnd_columns c
199 WHERE g.application_id = p_application_id
200 AND g.required_flag = 'Y'
201 AND g.descriptive_flexfield_name = p_df
202 AND g.enabled_flag = 'Y'
203 AND c.application_id = p_application_id
204 AND c.table_id = p_table_id
205 AND c.column_name = g.application_column_name
206 ORDER BY g.descriptive_flex_context_code;
207
208 cursor csr_user_df
209 IS
210 SELECT t.table_id,
211 df.application_id,
212 df.context_required_flag,
213 df.table_application_id,
214 df.concatenated_segment_delimiter,
215 df.description,
216 df.form_context_prompt
217 FROM fnd_tables t, fnd_descriptive_flexs_vl df, fnd_application a
218 WHERE a.application_short_name = 'PER'
219 AND df.application_id = a.application_id
220 AND df.descriptive_flexfield_name = p_df
221 AND t.application_id = df.table_application_id
222 AND t.table_name = df.application_table_name;
223 --
224 l_counter number :=0;
225 l_proc varchar2(72) := g_package||'.check_dfs';
226 l_table_id number := 0;
227 l_df_title varchar2(240) := p_df;
228 l_df_context_field varchar2(45);
229 l_application_id number := 0;
230 l_table_application_id number := 0;
231 l_segment_delimiter varchar2(1) := '.';
232 l_context_required_flag varchar2(1):= 'N';
233 --
234 begin
235 --
236 hr_utility.set_location('Entered '||l_proc,5);
237
238 Open csr_user_df;
239 Fetch csr_user_df Into l_table_id, l_application_id, l_context_required_flag, l_table_application_id,l_segment_delimiter,l_df_title,l_df_context_field;
240
241 IF csr_user_df%NOTFOUND THEN
242 CLOSE csr_user_df;
243 l_counter := 0;
244 return l_counter;
245 END IF;
246
247 Close csr_user_df;
248
249 write_log_message(p_message_name => 'PER_75005_STRT_CHK_DF'
250 ,p_token1 => 'DF:'||l_df_title);
251
252 IF (l_context_required_flag = 'Y') THEN
253 write_log_message(p_message_name => 'PER_75006_REQ_CTX'
254 , p_token1 => 'DF_CONTEXT_FIELD:'||l_df_context_field);
255 END IF;
256
257 for l_df_rec in csr_required_df_seg(p_df, l_application_id, l_table_id) LOOP
258 --
259 l_counter:= l_counter +1;
260 write_log_message(p_message_name => 'PER_75007_REQ_SEG'
261 , p_token1 => 'DF_CONTEXT_CODE:'||l_df_rec.descriptive_flex_context_code
262 , p_token2 => 'DF_SEGMENT:'||l_df_rec.end_user_column_name);
263 --
264 END LOOP;
265
266 if l_counter =0 then -- counter =0 means no required Segment found.
267 write_log_message(p_message_name => 'PER_75008_END_CHK_DF'
268 ,p_token1 => 'STATUS:'||get_translation('COMPLETE'));
269
270 else -- Otherwise some required segments found so error
271 write_log_message(p_message_name => 'PER_75008_END_CHK_DF'
272 ,p_token1 => 'STATUS:'||get_translation('ERROR'));
273 end if;
274
275 hr_utility.set_location('leaving '||l_proc,100);
276 return l_counter;
277
278 end check_dfs;
279
280 /*****************************************************************************
281 * This procedure is called from concurrent manager, and runs the correct *
282 * upgrade depending on which upgrade the user selected *
283 * retcode = 0 for Status Normal *
284 * retcode = 1 for Status Warning *
285 * retcode = 2 for Status Error *
286 *****************************************************************************/
287 Procedure run_upgrade(errbuf OUT NOCOPY VARCHAR2
288 ,retcode OUT NOCOPY NUMBER
289 ,p_business_group_id IN NUMBER
290 ,p_upgrade_type IN VARCHAR2)
291 IS
292 l_status number :=0; /* Zero means upgrade OK. 1 means error */
293 l_proc varchar2(72) := g_package||'.run_upgrade';
294 --
295 l_upg_type_meaning hr_lookups.meaning%type := hr_general.decode_lookup(p_lookup_type => 'FR_DATA_UPGRADE_TYPES'
296 ,p_lookup_code => p_upgrade_type);
297 --
298 begin
299
300 --
301 hr_utility.set_location('Entered '||l_proc,5);
302 --
303 write_log_message(p_message_name => 'PER_74987_DAT_UPG_PROC'
304 ,p_token1 => 'STATUS:'||get_translation('STARTING')
305 ,p_token2 => 'UPG_TYPE:'||l_upg_type_meaning);
306 --
307 if p_upgrade_type = 'DISABILITIES' then
308 l_status := PER_FR_DISABILITY_UPG_PKG.run_upgrade(p_business_group_id => p_business_group_id);
309 elsif p_upgrade_type = 'MEDICAL_EXAMS' then
310 l_status := PER_FR_MEDICAL_EXAMS_UPG_PKG.run_upgrade(p_business_group_id => p_business_group_id);
311 elsif p_upgrade_type = 'WORK_ACCIDENTS' then
312 l_status := PER_FR_WORK_ACC_UPG_PKG.run_upgrade(p_business_group_id => p_business_group_id);
313 elsif p_upgrade_type = 'TERMINATION' then
314 l_status := PER_FR_TERMINATION_UPG_PKG.run_upgrade(p_business_group_id => p_business_group_id);
315 else
316 l_status := 2;
317 -- This error should never happen. therefore no translation required.
318 write_log('Invalid Upgrade Type:'||p_upgrade_type);
319 end if;
320 --
321
322 /* Zero means OK, 1 means warning, 2 means Error - Write out correct message to log */
323 if l_status = 0 then
324 write_log_message(p_message_name => 'PER_74987_DAT_UPG_PROC'
325 ,p_token1 => 'STATUS:'||get_translation('COMPLETE')
326 ,p_token2 => 'UPG_TYPE:'||l_upg_type_meaning);
327 elsif l_status = 1 then /* Complete with Warnings */
328 write_log_message(p_message_name => 'PER_74987_DAT_UPG_PROC'
329 ,p_token1 => 'STATUS:'||get_translation('COMP_WARN')
330 ,p_token2 => 'UPG_TYPE:'||l_upg_type_meaning);
331 else /*Status 2 - Error */
332 write_log_message(p_message_name => 'PER_74987_DAT_UPG_PROC'
333 ,p_token1 => 'STATUS:'||get_translation('ERROR')
334 ,p_token2 => 'UPG_TYPE:'||l_upg_type_meaning);
335 end if;
336
337
338 retcode := l_status;
339 hr_utility.set_location('leaving '||l_proc,100);
340
341 exception when others then
342 write_log_message(p_message_name => 'PER_74991_DAT_UPG_FATAL'
343 ,p_token1 => 'STEP:10');
344 write_log(sqlcode);
345 write_log(sqlerrm);
346 retcode := 2; /* Fatal Error */
347
348 END run_upgrade;
349
350
351 END PER_FR_UPGRADE_DATA_PKG;
[Home] [Help]