[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_DBI_PKG
Source
1 package body pay_jp_dbi_pkg as
2 /* $Header: pyjpdbi.pkb 120.2 2006/12/06 06:34:31 keyazawa noship $ */
3 --
4 procedure translate
5 is
6 l_upper_limit hr_lookups.meaning%type;
7 l_lower_limit hr_lookups.meaning%type;
8 l_max hr_lookups.meaning%type;
9 l_min hr_lookups.meaning%type;
10 --
11 l_user_name ff_database_items.user_name%type;
12 l_tl_user_name ff_database_items_tl.translated_user_name%type;
13 --
14 cursor csr_tab(p_range_or_match in varchar2) is
15 select b.user_table_id,
16 b.user_table_name,
17 b.user_row_title,
18 tl.user_table_name tl_user_table_name,
19 tl.user_row_title tl_user_row_title
20 from pay_user_tables b,
21 pay_user_tables_tl tl
22 where b.range_or_match = p_range_or_match
23 and b.legislation_code = 'JP'
24 and b.business_group_id is null
25 and tl.user_table_id = b.user_table_id
26 and tl.language = 'JA';
27 --
28 cursor csr_row(p_user_table_id in number) is
29 select b.row_low_range_or_name,
30 tl.row_low_range_or_name tl_row_low_range_or_name
31 from pay_user_rows_f b,
32 pay_user_rows_f_tl tl
33 where b.user_table_id = p_user_table_id
34 and b.legislation_code = 'JP'
35 and b.business_group_id is null
36 and tl.user_row_id = b.user_row_id
37 and tl.language = 'JA'
38 group by
39 b.user_row_id,
40 b.row_low_range_or_name,
41 tl.row_low_range_or_name;
42 --
43 cursor csr_col(p_user_table_id in number) is
44 select b.user_column_name,
45 tl.user_column_name tl_user_column_name
46 from pay_user_columns b,
47 pay_user_columns_tl tl
48 where b.user_table_id = p_user_table_id
49 and b.legislation_code = 'JP'
50 and b.business_group_id is null
51 and tl.user_column_id = b.user_column_id
52 and tl.language = 'JA';
53 --
54 function ja_installed return boolean
55 is
56 l_dummy varchar2(1);
57 l_installed boolean;
58 --
59 cursor csr is
60 select 'Y'
61 from fnd_languages
62 where language_code = 'JA'
63 and installed_flag in ('B', 'I');
64 begin
65 open csr;
66 fetch csr into l_dummy;
67 l_installed := csr%found;
68 close csr;
69 --
70 return l_installed;
71 end ja_installed;
72 --
73 function decode_lookup(
74 p_lookup_type in varchar2,
75 p_lookup_code in varchar2) return varchar2
76 is
77 l_meaning hr_lookups.meaning%type;
78 begin
79 -- need to refer to fnd_lookup_values
80 -- because JA japanese dbi suffix should be derived
81 -- when adpatch runs with US lang.
82 select meaning
83 into l_meaning
84 from fnd_lookup_values
85 where lookup_type = p_lookup_type
86 and view_application_id = 3
87 and lookup_code = p_lookup_code
88 and security_group_id = 0
89 and language = 'JA';
90 --
91 return l_meaning;
92 end decode_lookup;
93 --
94 procedure update_tl_row(
95 p_user_name in varchar2,
96 p_tl_user_name in varchar2)
97 is
98 l_user_entity_id number;
99 l_tl_user_name ff_database_items_tl.translated_user_name%type;
100 l_new_tl_user_name ff_database_items_tl.translated_user_name%type;
101 l_got_error boolean;
102 --
103 cursor csr is
104 select u.user_entity_id
105 from ff_database_items d,
106 ff_user_entities u
107 where d.user_name = p_user_name
108 and u.user_entity_id = d.user_entity_id
109 and u.legislation_code = 'JP';
110 begin
111 open csr;
112 fetch csr into l_user_entity_id;
113 if csr%found then
114 select translated_user_name
115 into l_tl_user_name
116 from ff_database_items_tl
117 where user_name = p_user_name
118 and user_entity_id = l_user_entity_id
119 and language = 'JA';
120 --
121 -- Update only when the user_name is different.
122 --
123 l_new_tl_user_name := ff_dbi_utils_pkg.str2dbiname(p_tl_user_name);
124 if l_tl_user_name <> l_new_tl_user_name then
125 --
126 -- Following procedure will be changed to delete compiled info
127 -- if the translated DBI is used in fastformulas,
128 -- and log the message to fix those formulas.
129 --
130 ff_database_items_pkg.update_seeded_tl_rows(
131 x_user_name => p_user_name,
132 x_user_entity_id => l_user_entity_id,
133 x_language => 'JA',
134 x_translated_user_name => l_new_tl_user_name,
135 x_description => null,
136 x_got_error => l_got_error);
137 end if;
138 end if;
139 close csr;
140 end update_tl_row;
141 begin
142 --
143 -- Note this only updates FF_DATABASE_ITEMS_TL with JA language.
144 -- It is not necessary to check whether the db charset is JA compliant,
145 -- because JA installation means that the DB is JA charset compliant.
146 --
147 if ja_installed and ff_dbi_utils_pkg.translations_supported('JP') then
148 l_upper_limit := decode_lookup('NAME_TRANSLATIONS', 'UPPER_LIMIT');
149 l_lower_limit := decode_lookup('NAME_TRANSLATIONS', 'LOWER_LIMIT');
150 l_max := decode_lookup('NAME_TRANSLATIONS', 'MAX');
151 l_min := decode_lookup('NAME_TRANSLATIONS', 'MIN');
152 --
153 -- UDT DBIs for route "PAY_JP_UDT_RANGE_ROUTE1".
154 --
155 for l_tab in csr_tab('R') loop
156 l_user_name := ff_dbi_utils_pkg.str2dbiname(
157 l_tab.user_table_name || '_' ||
158 l_tab.user_row_title ||
159 '_UPPER_LIMIT');
160 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
161 l_tab.tl_user_table_name || '_' ||
162 l_tab.tl_user_row_title || '_' ||
163 l_upper_limit);
164 update_tl_row(l_user_name, l_tl_user_name);
165 --
166 l_user_name := ff_dbi_utils_pkg.str2dbiname(
167 l_tab.user_table_name || '_' ||
168 l_tab.user_row_title ||
169 '_LOWER_LIMIT');
170 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
171 l_tab.tl_user_table_name || '_' ||
172 l_tab.tl_user_row_title || '_' ||
173 l_lower_limit);
174 update_tl_row(l_user_name, l_tl_user_name);
175 end loop;
176 --
177 -- UDT DBIs for route "PAY_JP_UDT_RANGE_ROUTE2".
178 --
179 for l_tab in csr_tab('R') loop
180 for l_col in csr_col(l_tab.user_table_id) loop
181 l_user_name := ff_dbi_utils_pkg.str2dbiname(
182 l_tab.user_table_name || '_' ||
183 l_col.user_column_name ||
184 '_MAX');
185 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
186 l_tab.tl_user_table_name || '_' ||
187 l_col.tl_user_column_name || '_' ||
188 l_max);
189 update_tl_row(l_user_name, l_tl_user_name);
190 --
191 l_user_name := ff_dbi_utils_pkg.str2dbiname(
192 l_tab.user_table_name || '_' ||
193 l_col.user_column_name ||
194 '_MIN');
195 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
196 l_tab.tl_user_table_name || '_' ||
197 l_col.tl_user_column_name || '_' ||
198 l_min);
199 update_tl_row(l_user_name, l_tl_user_name);
200 end loop;
201 end loop;
202 --
203 -- UDT DBIs for route "PAY_JP_UDT_MATCH_ROUTE".
204 --
205 for l_tab in csr_tab('M') loop
206 for l_row in csr_row(l_tab.user_table_id) loop
207 for l_col in csr_col(l_tab.user_table_id) loop
208 l_user_name := ff_dbi_utils_pkg.str2dbiname(
209 l_tab.user_table_name || '_' ||
210 l_row.row_low_range_or_name || '_' ||
211 l_col.user_column_name);
212 l_tl_user_name := ff_dbi_utils_pkg.str2dbiname(
213 l_tab.tl_user_table_name || '_' ||
214 l_row.tl_row_low_range_or_name || '_' ||
215 l_col.tl_user_column_name);
216 update_tl_row(l_user_name, l_tl_user_name);
217 end loop;
218 end loop;
219 end loop;
220 --
221 -- JP Specific DBIs
222 --
223 l_tl_user_name := hr_jp_standard_pkg.get_message('PAY', 'PAY_JP_DBI_PAY_BASE_DAY_MIN_DE', 'JA');
224 if l_tl_user_name is not null then
225 update_tl_row('G_COM_PAY_BASE_DAYS_MIN_DE', l_tl_user_name);
226 end if;
227 --
228 l_tl_user_name := hr_jp_standard_pkg.get_message('PAY', 'PAY_JP_DBI_EI_LOC_BUS_TYPE', 'JA');
229 if l_tl_user_name is not null then
230 update_tl_row('PAY_JP_EI_LOCATION_BUSINESS_TYPE', l_tl_user_name);
231 end if;
232 end if;
233 end translate;
234 --
235 end pay_jp_dbi_pkg;