[Home] [Help]
PACKAGE BODY: APPS.ZX_PARAMETERS_PKG
Source
1 package body ZX_PARAMETERS_PKG as
2 /* $Header: zxiparameterb.pls 120.6 2005/10/05 22:11:03 vsidhart ship $ */
3
4 g_current_runtime_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
6 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
7 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
8 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
9
10 PROCEDURE insert_row (
11 x_tax_parameter_code IN VARCHAR2,
12 x_tax_parameter_type IN VARCHAR2,
13 x_format_type IN VARCHAR2,
14 x_max_size IN NUMBER,
15 x_seeded_flag IN VARCHAR2,
16 x_enabled_flag IN VARCHAR2,
17 x_generate_get_flag IN VARCHAR2,
18 x_allow_override IN VARCHAR2,
19 x_tax_parameter_name IN VARCHAR2,
20 x_creation_date IN DATE,
21 x_created_by IN NUMBER,
22 x_last_update_date IN DATE,
23 x_last_updated_by IN NUMBER,
24 x_last_update_login IN NUMBER
25 ) IS
26 CURSOR c IS SELECT rowid FROM zx_parameters_b
27 WHERE tax_parameter_code = x_tax_parameter_code;
28 prminfo c%ROWTYPE;
29 BEGIN
30 IF (g_level_procedure >= g_current_runtime_level ) THEN
31 FND_LOG.STRING(g_level_procedure,
32 'ZX_PARAMETERS_PKG',
33 'Insert_Row (+)');
34 END IF;
35 IF (g_level_procedure >= g_current_runtime_level ) THEN
36 FND_LOG.STRING(g_level_procedure,
37 'ZX_PARAMETERS_PKG.Insert_Row',
38 'Insert into ZX_PARAMETERS_B (+)');
39 END IF;
40
41 INSERT INTO zx_parameters_b (
42 tax_parameter_code,
43 tax_parameter_type_code,
44 format_type_code,
45 max_size,
46 seeded_flag,
47 enabled_flag,
48 generate_get_flag,
49 allow_override_flag,
50 creation_date,
51 created_by,
52 last_update_date,
53 last_updated_by,
54 last_update_login
55 ) VALUES (
56 x_tax_parameter_code,
57 x_tax_parameter_type,
58 x_format_type,
59 x_max_size,
60 x_seeded_flag,
61 x_enabled_flag,
62 x_generate_get_flag,
63 x_allow_override,
64 x_creation_date,
65 x_created_by,
66 x_last_update_date,
67 x_last_updated_by,
68 x_last_update_login
69 );
70
71 INSERT INTO zx_parameters_tl (
72 tax_parameter_code,
73 tax_parameter_name,
74 creation_date,
75 created_by,
76 last_update_date,
77 last_updated_by,
78 last_update_login,
79 language,
80 source_lang
81 )
82 SELECT
83 x_tax_parameter_code,
84 x_tax_parameter_name,
85 x_creation_date,
86 x_created_by,
87 x_last_update_date,
88 x_last_updated_by,
89 x_last_update_login,
90 l.language_code,
91 userenv('LANG')
92 FROM fnd_languages l
93 WHERE l.installed_flag IN ('I', 'B')
94 AND NOT EXISTS
95 (SELECT NULL
96 FROM zx_parameters_tl t
97 WHERE t.tax_parameter_code = x_tax_parameter_code
98 AND t.language = l.language_code);
99
100 OPEN c;
101 FETCH c INTO prminfo;
102 IF (c%notfound) THEN
103 CLOSE c;
104 RAISE NO_DATA_FOUND;
105 END IF;
106 CLOSE c;
107
108 IF (g_level_procedure >= g_current_runtime_level ) THEN
109 FND_LOG.STRING(g_level_procedure,
110 'ZX_PARAMETERS_PKG.Insert_Row',
111 'Insert into ZX_PARAMETERS_B (-)');
112 END IF;
113 END INSERT_ROW;
114
115
116
117 PROCEDURE translate_row
118 ( x_tax_parameter_code IN VARCHAR2,
119 x_owner IN VARCHAR2,
120 x_tax_parameter_name IN VARCHAR2,
121 x_last_update_date IN VARCHAR2,
122 x_custom_mode IN VARCHAR2 ) IS
123
124 f_luby number;
125 f_ludate date;
126 db_luby number;
127 db_ludate date;
128
129 BEGIN
130 IF (g_level_procedure >= g_current_runtime_level ) THEN
131 FND_LOG.STRING(g_level_procedure,
132 'ZX_PARAMETERS_PKG',
133 'Translate_Row (+)');
134 END IF;
135
136 --Translate owner to file_last_updated_by
137 f_luby:= fnd_load_util.owner_id(x_owner);
138 --Translate char last_update_date to date
139 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE,'DD/MM/YYYY'),sysdate);
140
141 BEGIN
142 SELECT decode (p.seeded_flag,'Y',1,0), t.last_update_date
143 INTO db_luby, db_ludate
144 FROM zx_parameters_tl t, zx_parameters_b p
145 WHERE t.tax_parameter_code = p.tax_parameter_code
146 AND t.tax_parameter_code = X_TAX_PARAMETER_CODE
147 AND t.language = userenv('LANG');
148
149 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
150 db_ludate, x_custom_mode)) THEN
151 UPDATE zx_parameters_tl SET
152 tax_parameter_name = x_tax_parameter_name,
153 last_update_date = f_ludate,
154 last_updated_by = f_luby,
155 last_update_login = 0,
156 source_lang = userenv ('LANG')
157 WHERE userenv('LANG') IN (language,source_lang)
158 AND tax_parameter_code = x_tax_parameter_code;
159 END IF;
160
161 EXCEPTION
162 WHEN NO_DATA_FOUND THEN
163 --Do not insert missing translation, skip this row
164 NULL;
165 END;
166 IF (g_level_procedure >= g_current_runtime_level ) THEN
167 FND_LOG.STRING(g_level_procedure,
168 'ZX_PARAMETERS_PKG',
169 'Translate_Row (-)');
170 END IF;
171 END translate_row;
172
173
174 PROCEDURE load_row
175 ( x_tax_parameter_code IN VARCHAR2,
176 x_owner IN VARCHAR2,
177 x_tax_parameter_type IN VARCHAR2,
178 x_format_type IN VARCHAR2,
179 x_max_size IN VARCHAR2,
180 x_enabled_flag IN VARCHAR2,
181 x_generate_get_flag IN VARCHAR2,
182 x_tax_parameter_name IN VARCHAR2,
183 x_allow_override IN VARCHAR2,
184 x_last_update_date IN VARCHAR2,
185 x_custom_mode IN VARCHAR2 ) IS
186
187 f_luby number;
188 f_ludate date;
189 db_luby number;
190 db_ludate date;
191 l_max_size number;
192
193 BEGIN
194 IF (g_level_procedure >= g_current_runtime_level ) THEN
195 FND_LOG.STRING(g_level_procedure,
196 'ZX_PARAMETERS_PKG',
197 'Load_Row (+)');
198 END IF;
199 --Translate owner to file_last_updated_by
200 f_luby:= fnd_load_util.owner_id(x_owner);
201 --Translate char last_update_date to date
202 f_ludate := nvl(to_date(x_last_update_date,'DD/MM/YYYY'),sysdate);
203
204 --Analyze and set up the correct value for the nullable parameters
205 IF (x_max_size = fnd_user_pkg.null_char) then
206 l_max_size := fnd_user_pkg.null_number;
207 ELSE
208 l_max_size := to_number(x_max_size);
209 END IF;
210
211 BEGIN
212 SELECT decode (seeded_flag,'Y',1,0), last_update_date
213 INTO db_luby, db_ludate
214 FROM zx_parameters_b
215 WHERE tax_parameter_code = x_tax_parameter_code;
216
217 -- Record should be updated only if :
218 -- a. file owner is CUSTOM and db owner is SEED
219 -- b. owners are the same, and file_date > db_date
220 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
221 db_ludate, x_custom_mode)) THEN
222 UPDATE zx_parameters_b
223 SET tax_parameter_type_code = x_tax_parameter_type,
224 format_type_code = x_format_type,
225 allow_override_flag = x_allow_override,
226 enabled_flag = x_enabled_flag,
227 generate_get_flag = x_generate_get_flag,
228 max_size = l_max_size,
229 last_update_date = f_ludate,
230 last_updated_by = f_luby,
231 last_update_login = 0
232 WHERE tax_parameter_code = x_tax_parameter_code;
233 END IF;
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 insert_row (
237 x_tax_parameter_code => x_tax_parameter_code,
238 x_tax_parameter_type => x_tax_parameter_type,
239 x_format_type => x_format_type,
240 x_max_size => l_max_size,
241 x_seeded_flag => 'Y',
242 x_enabled_flag => x_enabled_flag,
243 x_generate_get_flag => x_generate_get_flag,
244 x_allow_override => x_allow_override,
245 x_tax_parameter_name => x_tax_parameter_name,
246 x_last_update_date => f_ludate,
247 x_creation_date => f_ludate,
248 x_created_by => f_luby,
249 x_last_updated_by => f_luby,
250 x_last_update_login => 0 );
251 END;
252 IF (g_level_procedure >= g_current_runtime_level ) THEN
253 FND_LOG.STRING(g_level_procedure,
254 'ZX_PARAMETERS_PKG',
255 'Load_Row (+)');
256 END IF;
257 END load_row;
258
259 procedure ADD_LANGUAGE
260 is
261 begin
262 delete from ZX_PARAMETERS_TL T
263 where not exists
264 (select NULL
265 from ZX_PARAMETERS_B B
266 where B.TAX_PARAMETER_CODE = T.TAX_PARAMETER_CODE
267 );
268
269 update ZX_PARAMETERS_TL T set (
270 TAX_PARAMETER_NAME
271 ) = (select
272 B.TAX_PARAMETER_NAME
273 from ZX_PARAMETERS_TL B
274 where B.TAX_PARAMETER_CODE = T.TAX_PARAMETER_CODE
275 and B.LANGUAGE = T.SOURCE_LANG)
276 where (
277 T.TAX_PARAMETER_CODE,
278 T.LANGUAGE
279 ) in (select
280 SUBT.TAX_PARAMETER_CODE,
281 SUBT.LANGUAGE
282 from ZX_PARAMETERS_TL SUBB, ZX_PARAMETERS_TL SUBT
283 where SUBB.TAX_PARAMETER_CODE = SUBT.TAX_PARAMETER_CODE
284 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
285 and (SUBB.TAX_PARAMETER_NAME <> SUBT.TAX_PARAMETER_NAME
286 ));
287
288 insert into ZX_PARAMETERS_TL (
289 TAX_PARAMETER_CODE,
290 TAX_PARAMETER_NAME,
291 CREATION_DATE,
292 CREATED_BY,
293 LAST_UPDATE_DATE,
294 LAST_UPDATED_BY,
295 LAST_UPDATE_LOGIN,
296 LANGUAGE,
297 SOURCE_LANG
298 ) select /*+ ORDERED */
299 B.TAX_PARAMETER_CODE,
300 B.TAX_PARAMETER_NAME,
301 B.CREATION_DATE,
302 B.CREATED_BY,
303 B.LAST_UPDATE_DATE,
304 B.LAST_UPDATED_BY,
305 B.LAST_UPDATE_LOGIN,
306 L.LANGUAGE_CODE,
307 B.SOURCE_LANG
308 from ZX_PARAMETERS_TL B, FND_LANGUAGES L
309 where L.INSTALLED_FLAG in ('I', 'B')
310 and B.LANGUAGE = userenv('LANG')
311 and not exists
312 (select NULL
313 from ZX_PARAMETERS_TL T
314 where T.TAX_PARAMETER_CODE = B.TAX_PARAMETER_CODE
315 and T.LANGUAGE = L.LANGUAGE_CODE);
316 end ADD_LANGUAGE;
317 end ZX_PARAMETERS_PKG;