[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_DBI_PKG
Source
1 package body pay_jp_dbi_pkg as
2 /* $Header: pyjpdbi.pkb 120.4 2011/05/17 13:00:44 nchinnam ship $ */
3 --
4 g_elm_name pay_element_types_f.element_name%type;
5 g_elm_name_tl pay_element_types_f_tl.element_name%type;
6 g_glb_name ff_globals_f.global_name%type;
7 g_glb_name_tl ff_globals_f_tl.global_name%type;
8 --
9 procedure translate
10 is
11 l_upper_limit hr_lookups.meaning%type;
12 l_lower_limit hr_lookups.meaning%type;
13 l_max hr_lookups.meaning%type;
14 l_min hr_lookups.meaning%type;
15 --
16 l_user_name ff_database_items.user_name%type;
17 l_tl_user_name ff_database_items_tl.translated_user_name%type;
18 --
19 cursor csr_tab(p_range_or_match in varchar2) is
20 select b.user_table_id,
21 b.user_table_name,
22 b.user_row_title,
23 tl.user_table_name tl_user_table_name,
24 tl.user_row_title tl_user_row_title
25 from pay_user_tables b,
26 pay_user_tables_tl tl
27 where b.range_or_match = p_range_or_match
28 and b.legislation_code = 'JP'
29 and b.business_group_id is null
30 and tl.user_table_id = b.user_table_id
31 and tl.language = 'JA';
32 --
33 cursor csr_row(p_user_table_id in number) is
34 select b.row_low_range_or_name,
35 tl.row_low_range_or_name tl_row_low_range_or_name
36 from pay_user_rows_f b,
37 pay_user_rows_f_tl tl
38 where b.user_table_id = p_user_table_id
39 and b.legislation_code = 'JP'
40 and b.business_group_id is null
41 and tl.user_row_id = b.user_row_id
42 and tl.language = 'JA'
43 group by
44 b.user_row_id,
45 b.row_low_range_or_name,
46 tl.row_low_range_or_name;
47 --
48 cursor csr_col(p_user_table_id in number) is
49 select b.user_column_name,
50 tl.user_column_name tl_user_column_name
51 from pay_user_columns b,
52 pay_user_columns_tl tl
53 where b.user_table_id = p_user_table_id
54 and b.legislation_code = 'JP'
55 and b.business_group_id is null
56 and tl.user_column_id = b.user_column_id
57 and tl.language = 'JA';
58 --
59 function ja_installed return boolean
60 is
61 l_dummy varchar2(1);
62 l_installed boolean;
63 --
64 cursor csr is
65 select 'Y'
66 from fnd_languages
67 where language_code = 'JA'
68 and installed_flag in ('B', 'I');
69 begin
70 open csr;
71 fetch csr into l_dummy;
72 l_installed := csr%found;
73 close csr;
74 --
75 return l_installed;
76 end ja_installed;
77 --
78 function decode_lookup(
79 p_lookup_type in varchar2,
80 p_lookup_code in varchar2) return varchar2
81 is
82 l_meaning hr_lookups.meaning%type;
83 begin
84 -- need to refer to fnd_lookup_values
85 -- because JA japanese dbi suffix should be derived
86 -- when adpatch runs with US lang.
87 select meaning
88 into l_meaning
89 from fnd_lookup_values
90 where lookup_type = p_lookup_type
91 and view_application_id = 3
92 and lookup_code = p_lookup_code
93 and security_group_id = 0
94 and language = 'JA';
95 --
96 return l_meaning;
97 end decode_lookup;
98 --
99 procedure update_tl_row(
100 p_user_name in varchar2,
101 p_tl_user_name in varchar2)
102 is
103 l_user_entity_id number;
104 l_tl_user_name ff_database_items_tl.translated_user_name%type;
105 l_new_tl_user_name ff_database_items_tl.translated_user_name%type;
106 l_got_error boolean;
107 --
108 cursor csr is
109 select u.user_entity_id
110 from ff_database_items d,
111 ff_user_entities u
112 where d.user_name = p_user_name
113 and u.user_entity_id = d.user_entity_id
114 and u.legislation_code = 'JP';
115 begin
116 open csr;
117 fetch csr into l_user_entity_id;
118 if csr%found then
119 select translated_user_name
120 into l_tl_user_name
121 from ff_database_items_tl
122 where user_name = p_user_name
123 and user_entity_id = l_user_entity_id
124 and language = 'JA';
125 --
126 -- Update only when the user_name is different.
127 --
128 l_new_tl_user_name := ff_dbi_utils_pkg.str2dbiname(p_tl_user_name);
129 if l_tl_user_name <> l_new_tl_user_name then
130 --
131 -- Following procedure will be changed to delete compiled info
132 -- if the translated DBI is used in fastformulas,
133 -- and log the message to fix those formulas.
134 --
135 ff_database_items_pkg.update_seeded_tl_rows(
136 x_user_name => p_user_name,
137 x_user_entity_id => l_user_entity_id,
138 x_language => 'JA',
139 x_translated_user_name => l_new_tl_user_name,
140 x_description => null,
141 x_got_error => l_got_error);
142 end if;
143 end if;
144 close csr;
145 end update_tl_row;
146 --
147 function decode_elm(
148 p_element_name in varchar2)
149 return varchar2
150 is
151 --
152 l_elm_tl pay_element_types_f_tl.element_name%type;
153 --
154 cursor csr_elm_tl
155 is
156 select petl.element_name
157 from pay_element_types_f_tl petl,
158 pay_element_types_f pet
159 where pet.element_name = p_element_name
160 and pet.legislation_code = 'JP'
161 and petl.element_type_id = pet.element_type_id
162 and petl.language = 'JA';
163 --
164 begin
165 --
166 if g_elm_name is null
167 or g_elm_name <> p_element_name then
168 --
169 open csr_elm_tl;
170 fetch csr_elm_tl into l_elm_tl;
171 close csr_elm_tl;
172 --
173 g_elm_name := p_element_name;
174 g_elm_name_tl := l_elm_tl;
175 --
176 else
177 --
178 l_elm_tl := g_elm_name_tl;
179 --
180 end if;
181 --
182 return l_elm_tl;
183 --
184 end decode_elm;
185 --
186 function decode_glb(
187 p_global_name in varchar2)
188 return varchar2
189 is
190 --
191 l_glb_tl ff_globals_f_tl.global_name%type;
192 --
193 cursor csr_glb_tl
194 is
195 select fgtl.global_name
196 from ff_globals_f_tl fgtl,
197 ff_globals_f fg
198 where fg.global_name = p_global_name
199 and fg.legislation_code = 'JP'
200 and fgtl.global_id = fg.global_id
201 and fgtl.language = 'JA';
202 --
203 begin
204 --
205 if g_glb_name is null
206 or g_glb_name <> p_global_name then
207 --
208 open csr_glb_tl;
209 fetch csr_glb_tl into l_glb_tl;
210 close csr_glb_tl;
211 --
212 g_glb_name := p_global_name;
213 g_glb_name_tl := l_glb_tl;
214 --
215 else
216 --
217 l_glb_tl := g_glb_name_tl;
218 --
219 end if;
220 --
221 return l_glb_tl;
222 --
223 end decode_glb;
224 --
225 begin
226 --
227 -- Note this only updates FF_DATABASE_ITEMS_TL with JA language.
228 -- It is not necessary to check whether the db charset is JA compliant,
229 -- because JA installation means that the DB is JA charset compliant.
230 --
231 if ja_installed and ff_dbi_utils_pkg.translations_supported('JP') then
232 l_upper_limit := decode_lookup('NAME_TRANSLATIONS', 'UPPER_LIMIT');
233 l_lower_limit := decode_lookup('NAME_TRANSLATIONS', 'LOWER_LIMIT');
234 l_max := decode_lookup('NAME_TRANSLATIONS', 'MAX');
235 l_min := decode_lookup('NAME_TRANSLATIONS', 'MIN');
236 --
237 -- UDT DBIs for route "PAY_JP_UDT_RANGE_ROUTE1".
238 --
239 for l_tab in csr_tab('R') loop
240 l_user_name := ff_dbi_utils_pkg.str2dbiname(
241 l_tab.user_table_name || '_' ||
242 l_tab.user_row_title ||
243 '_UPPER_LIMIT');
244 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
245 l_tab.tl_user_table_name || '_' ||
246 l_tab.tl_user_row_title || '_' ||
247 l_upper_limit);
248 update_tl_row(l_user_name, l_tl_user_name);
249 --
250 l_user_name := ff_dbi_utils_pkg.str2dbiname(
251 l_tab.user_table_name || '_' ||
252 l_tab.user_row_title ||
253 '_LOWER_LIMIT');
254 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
255 l_tab.tl_user_table_name || '_' ||
256 l_tab.tl_user_row_title || '_' ||
257 l_lower_limit);
258 update_tl_row(l_user_name, l_tl_user_name);
259 end loop;
260 --
261 -- UDT DBIs for route "PAY_JP_UDT_RANGE_ROUTE2".
262 --
263 for l_tab in csr_tab('R') loop
264 for l_col in csr_col(l_tab.user_table_id) loop
265 l_user_name := ff_dbi_utils_pkg.str2dbiname(
266 l_tab.user_table_name || '_' ||
267 l_col.user_column_name ||
268 '_MAX');
269 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
270 l_tab.tl_user_table_name || '_' ||
271 l_col.tl_user_column_name || '_' ||
272 l_max);
273 update_tl_row(l_user_name, l_tl_user_name);
274 --
275 l_user_name := ff_dbi_utils_pkg.str2dbiname(
276 l_tab.user_table_name || '_' ||
277 l_col.user_column_name ||
278 '_MIN');
279 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
280 l_tab.tl_user_table_name || '_' ||
281 l_col.tl_user_column_name || '_' ||
282 l_min);
283 update_tl_row(l_user_name, l_tl_user_name);
284 end loop;
285 end loop;
286 --
287 -- UDT DBIs for route "PAY_JP_UDT_MATCH_ROUTE".
288 --
289 for l_tab in csr_tab('M') loop
290 for l_row in csr_row(l_tab.user_table_id) loop
291 for l_col in csr_col(l_tab.user_table_id) loop
292 l_user_name := ff_dbi_utils_pkg.str2dbiname(
293 l_tab.user_table_name || '_' ||
294 l_row.row_low_range_or_name || '_' ||
295 l_col.user_column_name);
296 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
297 l_tab.tl_user_table_name || '_' ||
298 l_row.tl_row_low_range_or_name || '_' ||
299 l_col.tl_user_column_name);
300 update_tl_row(l_user_name, l_tl_user_name);
301 end loop;
302 end loop;
303 end loop;
304 --
305 -- JP Specific DBIs
306 --
307 l_tl_user_name := hr_jp_standard_pkg.get_message('PAY', 'PAY_JP_DBI_PAY_BASE_DAY_MIN_DE', 'JA');
308 --
309 if decode_glb('G_COM_PAY_BASE_DAYS_MIN') is not null then
310 --
311 l_tl_user_name := decode_glb('G_COM_PAY_BASE_DAYS_MIN')||replace(replace(l_tl_user_name,decode_glb('G_COM_PAY_BASE_DAYS_MIN'),''),to_multi_byte('_'),'_');
312 --
313 end if;
314 --
315 if l_tl_user_name is not null then
316 update_tl_row('G_COM_PAY_BASE_DAYS_MIN_DE', l_tl_user_name);
317 end if;
318 --
319 l_tl_user_name := hr_jp_standard_pkg.get_message('PAY', 'PAY_JP_DBI_EI_LOC_BUS_TYPE', 'JA');
320 --
321 if decode_elm('COM_LI_INFO') is not null then
322 --
323 l_tl_user_name := decode_elm('COM_LI_INFO')||replace(replace(l_tl_user_name,decode_elm('COM_LI_INFO'),''),to_multi_byte('_'),'_');
324 --
325 end if;
326 --
327 if l_tl_user_name is not null then
328 update_tl_row('PAY_JP_EI_LOCATION_BUSINESS_TYPE', l_tl_user_name);
329 end if;
330 end if;
331 end translate;
332 --
333 end pay_jp_dbi_pkg;