1 PACKAGE BODY ota_mls_migration AS
2 /* $Header: otmlsmig.pkb 115.1 2003/05/19 07:56:30 jbharath noship $ */
3
4 -- ----------------------------------------------------------------------------
5 -- |---------------------< migrateActivityDefinitionData >--------------------|
6 -- ----------------------------------------------------------------------------
7 procedure migrateActivityDefinitionData(
8 p_process_ctrl IN varchar2,
9 p_start_pkid IN number,
10 p_end_pkid IN number,
11 p_rows_processed OUT nocopy number)
12 is
13
14 cursor csr_installed_languages is
15 select language_code,
16 nls_Language
17 from fnd_languages
18 where installed_flag in ('I', 'B');
19
20 l_userenv_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE := userenv('LANG');
21 l_current_nls_language VARCHAR2(30);
22 l_current_language FND_LANGUAGES.LANGUAGE_CODE%TYPE ;
23 l_rows_processed number := 0;
24
25 begin
26
27 /*
28 ** Clear out any existing data for this range of records
29 **
30 ** Don't delete just yet.
31 **
32 delete from ota_activity_definitions_tl
33 where activity_id between p_start_pkid
34 and p_end_pkid;
35 */
36 /*
37 **
38 ** For each installed language insert a new record into the TL table for
39 ** each record in the range provided that is present in the base table.
40 */
41
42
43 for c_language in csr_installed_languages loop
44
45 /*
46 ** Set language for iteration....
47 */
48 ota_mls_utility.set_session_nls_language(c_language.nls_language);
49 l_current_language := c_language.language_code;
50
51 /*
52 ** Insert the TL rows.
53 */
54
55 Insert into OTA_ACTIVITY_DEFINITIONS_TL
56 (Activity_Id,
57 Language,
58 Name,
59 Description,
60 Source_Lang,
61 Created_By,
62 Creation_Date,
63 Last_Updated_By,
64 Last_Update_Date,
65 Last_Update_Login )
66 Select
67 M.Activity_Id,
68 L_Current_Language,
69 M.Name,
70 M.Description,
71 L_Userenv_language_code,
72 M.Created_By,
73 M.Creation_date,
74 M.Last_Updated_By,
75 M.Last_Update_Date,
76 M.Last_Update_Login
77 From OTA_ACTIVITY_DEFINITIONS M
78 Where M.Activity_id Between P_start_pkid and P_end_pkid
79 And Not Exists (Select '1'
80 From OTA_ACTIVITY_DEFINITIONS_TL T
81 Where T.Activity_Id = M.Activity_Id
82 And T.Language = L_Current_Language ) ;
83
84
85 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
86
87 end loop;
88
89 p_rows_processed := l_rows_processed;
90
91 ota_mls_utility.set_session_language_code( l_userenv_language_code );
92
93 Exception
94 --
95 When Others Then
96 --
97 ota_mls_utility.set_session_language_code( l_userenv_language_code );
98 --
99 raise;
100 end;
101
102 -- ---------------------------------------------------------------------------------
103 -- |------------------------< migrateActivityVersionData >-------------------------|
104 -- ---------------------------------------------------------------------------------
105 procedure migrateActivityVersionData(
106 p_process_ctrl IN varchar2,
107 p_start_pkid IN number,
108 p_end_pkid IN number,
109 p_rows_processed OUT nocopy number)
110 is
111
112 cursor csr_installed_languages is
113 select language_code,
114 nls_Language
115 from fnd_languages
116 where installed_flag in ('I', 'B');
117
118 l_userenv_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE := userenv('LANG');
119 l_current_nls_language VARCHAR2(30);
120 l_current_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
121 l_rows_processed number := 0;
122 l_end_of_time date := hr_general.end_of_time;
123
124 begin
125
126 /*
127 ** Clear out any existing data for this range of records
128 **
129 ** Don't delete just yet.
130 **
131 delete from ota_activity_versions_tl
132 where activity_version_id between p_start_pkid
133 and p_end_pkid;
134 */
135
136 /*
137 **
138 ** For each installed language insert a new record into the TL table for
139 ** each record in the range provided that is present in the base table.
140 */
141 for c_language in csr_installed_languages loop
142
143 /*
144 ** Set language for iteration....
145 */
146 ota_mls_utility.set_session_nls_language(c_language.nls_language);
147 l_current_language := c_language.language_code;
148
149 /*
150 ** Insert the TL rows.
151 */
152 Insert into OTA_ACTIVITY_VERSIONS_TL
153 (Activity_Version_Id,
154 Language,
155 Version_Name,
156 Description,
157 Intended_Audience,
158 Objectives,
159 Source_Lang,
160 Created_By,
161 Creation_Date,
162 Last_Updated_By,
163 Last_Update_Date,
164 Last_Update_Login )
165 Select
166 M.Activity_Version_Id,
167 L_Current_Language,
168 M.Version_Name,
169 M.Description,
170 M.Intended_Audience,
171 M.Objectives,
172 L_Userenv_language_code,
173 M.Created_By,
174 M.Creation_date,
175 M.Last_Updated_By,
176 M.Last_Update_Date,
177 M.Last_Update_Login
178 From OTA_ACTIVITY_VERSIONS M
179 Where M.Activity_version_id Between P_start_pkid and P_end_pkid
180 And Not Exists (Select '1'
181 From OTA_ACTIVITY_VERSIONS_TL T
182 Where T.Activity_version_Id = M.Activity_version_Id
183 And T.Language = L_Current_Language ) ;
184
185 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
186
187 end loop;
188
189 p_rows_processed := l_rows_processed;
190
191 ota_mls_utility.set_session_language_code( l_userenv_language_code );
192
193 Exception
194 --
195 When Others Then
196 --
197 ota_mls_utility.set_session_language_code( l_userenv_language_code );
198 --
199 raise;
200
201 end;
202
203 -- ----------------------------------------------------------------------------
204 -- |---------------------------< migrateEventData >---------------------------|
205 -- ----------------------------------------------------------------------------
206 procedure migrateEventData(
207 p_process_ctrl IN varchar2,
208 p_start_pkid IN number,
209 p_end_pkid IN number,
210 p_rows_processed OUT nocopy number)
211 is
212
213 cursor csr_installed_languages is
214 select language_code,
215 nls_Language
216 from fnd_languages
217 where installed_flag in ('I', 'B');
218
219 l_userenv_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE := userenv('LANG');
220 l_current_nls_language VARCHAR2(30);
221 l_current_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
222 l_rows_processed number := 0;
223 l_end_of_time date := hr_general.end_of_time;
224
225 begin
226
227 /*
228 ** Clear out any existing data for this range of records
229 **
230 ** Don't delete just yet.
231 **
232 delete from ota_events_tl
233 where event_id between p_start_pkid
234 and p_end_pkid;
235 */
236
237 /*
238 **
239 ** For each installed language insert a new record into the TL table for
240 ** each record in the range provided that is present in the base table.
241 */
242 for c_language in csr_installed_languages loop
243
244 /*
245 ** Set language for iteration....
246 */
247 ota_mls_utility.set_session_nls_language(c_language.nls_language);
248 l_current_language := c_language.language_code;
249
250 /*
251 ** Insert the TL rows.
252 */
253 Insert into OTA_EVENTS_TL
254 (Event_Id,
255 Language,
256 Title,
257 Source_Lang,
258 Created_By,
259 Creation_Date,
260 Last_Updated_By,
261 Last_Update_Date,
262 Last_Update_Login )
263 Select
264 M.Event_Id,
265 L_Current_Language,
266 M.Title,
267 L_Userenv_language_code,
268 M.Created_By,
269 M.Creation_date,
270 M.Last_Updated_By,
271 M.Last_Update_Date,
272 M.Last_Update_Login
273 From OTA_EVENTS M
274 Where M.Event_id Between P_start_pkid and P_end_pkid
275 And Not Exists (Select '1'
276 From OTA_EVENTS_TL T
277 Where T.Event_Id = M.Event_Id
278 And T.Language = L_Current_Language ) ;
279
280
281
282 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
283
284 end loop;
285
286 p_rows_processed := l_rows_processed;
287
288 ota_mls_utility.set_session_language_code( l_userenv_language_code );
289
290 Exception
291 --
292 When Others Then
293 --
294 ota_mls_utility.set_session_language_code( l_userenv_language_code );
295 --
296 raise;
297
298 end;
299
300 -- ----------------------------------------------------------------------------
301 -- |------------------< migrateBookingStatusTypeData >------------------------|
302 -- ----------------------------------------------------------------------------
303 procedure migrateBookingStatusTypeData(
304 p_process_ctrl IN varchar2,
305 p_start_pkid IN number,
306 p_end_pkid IN number,
307 p_rows_processed OUT nocopy number)
308 is
309
310 cursor csr_installed_languages is
311 select language_code,
312 nls_Language
313 from fnd_languages
314 where installed_flag in ('I', 'B');
315
316 l_userenv_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE := userenv('LANG');
317 l_current_nls_language VARCHAR2(30);
318 l_current_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
319 l_rows_processed number := 0;
320 l_end_of_time date := hr_general.end_of_time;
321
322 begin
323
324 /*
325 ** Clear out any existing data for this range of records
326 **
327 ** Don't delete just yet.
328 **
329 delete from ota_booking_status_types_tl
330 where booking_status_type_id between p_start_pkid
331 and p_end_pkid;
332 */
333
334 /*
335 **
336 ** For each installed language insert a new record into the TL table for
337 ** each record in the range provided that is present in the base table.
338 */
339 for c_language in csr_installed_languages loop
340
341 /*
342 ** Set language for iteration....
343 */
344 ota_mls_utility.set_session_nls_language(c_language.nls_language);
345 l_current_language := c_language.language_code;
346
347 /*
348 ** Insert the TL rows.
349 */
350 Insert into OTA_BOOKING_STATUS_TYPES_TL
351 (Booking_Status_type_Id,
352 Language,
353 Name,
354 Description,
355 Source_Lang,
356 Created_By,
357 Creation_Date,
358 Last_Updated_By,
359 Last_Update_Date,
360 Last_Update_Login )
361 Select
362 M.Booking_Status_type_Id,
363 L_Current_Language,
364 M.Name,
365 M.Description,
366 L_Userenv_language_code,
367 M.Created_By,
368 M.Creation_date,
369 M.Last_Updated_By,
370 M.Last_Update_Date,
371 M.Last_Update_Login
372 From OTA_BOOKING_STATUS_TYPES M
373 Where M.Booking_status_type_id Between P_start_pkid and P_end_pkid
374 And Not Exists (Select '1'
375 From OTA_BOOKING_STATUS_TYPES_TL T
376 Where T.Booking_status_type_Id = M.Booking_Status_type_Id
377 And T.Language = L_Current_Language ) ;
378
379
380 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
381
382 end loop;
383
384 p_rows_processed := l_rows_processed;
385
386 ota_mls_utility.set_session_language_code( l_userenv_language_code );
387
388 Exception
389 --
390 When Others Then
391 --
392 ota_mls_utility.set_session_language_code( l_userenv_language_code );
393 --
394 raise;
395
396 end;
397
398 -- ----------------------------------------------------------------------------
399 -- |---------------------------< migrateResourceData >------------------------|
400 -- ----------------------------------------------------------------------------
401 procedure migrateResourceData(
402 p_process_ctrl IN varchar2,
403 p_start_pkid IN number,
404 p_end_pkid IN number,
405 p_rows_processed OUT nocopy number)
406 is
407
408 cursor csr_installed_languages is
409 select language_code,
410 nls_Language
411 from fnd_languages
412 where installed_flag in ('I', 'B');
413
414 l_userenv_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE := userenv('LANG');
415 l_current_nls_language VARCHAR2(30);
416 l_current_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
417 l_rows_processed number := 0;
418 l_end_of_time date := hr_general.end_of_time;
419
420 begin
421
422 /*
423 ** Clear out any existing data for this range of records
424 **
425 ** Don't delete just yet.
426 **
427 delete from ota_suppliable_resources_tl
428 where supplied_resource_id between p_start_pkid
429 and p_end_pkid;
430 */
431
432 /*
433 **
434 ** For each installed language insert a new record into the TL table for
435 ** each record in the range provided that is present in the base table.
436 */
437 for c_language in csr_installed_languages loop
438
439 /*
440 ** Set language for iteration....
441 */
442 ota_mls_utility.set_session_nls_language(c_language.nls_language);
443 l_current_language := c_language.language_code;
444
445 /*
446 ** Insert the TL rows.
447 */
448 Insert into OTA_SUPPLIABLE_RESOURCES_TL
449 (Supplied_Resource_Id,
450 Language,
451 Name,
452 Special_Instruction,
453 Source_Lang,
454 Created_By,
455 Creation_Date,
456 Last_Updated_By,
457 Last_Update_Date,
458 Last_Update_Login )
459 Select
460 M.Supplied_Resource_Id,
461 L_Current_Language,
462 nvl(fnd_flex_ext.get_segs('OTA', 'RES',
463 rd.id_flex_num,
464 rd.resource_definition_id), M.Name),
465 M.Special_Instruction,
466 L_Userenv_language_code,
467 M.Created_By,
468 M.Creation_date,
469 M.Last_Updated_By,
470 M.Last_Update_Date,
471 M.Last_Update_Login
472 From OTA_SUPPLIABLE_RESOURCES M, OTA_RESOURCE_DEFINITIONS RD
473 Where M.Resource_definition_id = Rd.Resource_definition_id
474 And M.Supplied_resource_id Between P_start_pkid and P_end_pkid
475 And Not Exists (Select '1'
476 From OTA_SUPPLIABLE_RESOURCES_TL T
477 Where T.Supplied_resource_id = M.Supplied_resource_id
478 And T.Language = L_Current_Language ) ;
479
480 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
481
482 end loop;
483
484 p_rows_processed := l_rows_processed;
485
486 ota_mls_utility.set_session_language_code( l_userenv_language_code );
487
488 Exception
489 --
490 When Others Then
491 --
492 ota_mls_utility.set_session_language_code( l_userenv_language_code );
493 --
494 raise;
495
496 end;
497 --
498 end ota_mls_migration;
[Home] [Help]