[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_LOAD_PARAMETERS
Source
1 PACKAGE BODY PAY_LOAD_PARAMETERS AS
2 /* $Header: pyprmtld.pkb 120.1.12020000.1 2013/03/22 08:08:01 nvankadh noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 ======================================================================
24
25 Change List
26 ===========
27
28 Version Date Author ER/CR No. Description of Change
29 -------+---------+----------+---------+-------------------------------
30 115.0 03/03/08 ckesanap 6820127 Created the file.
31 ----------------------------------------------------------------------
32 */
33
34 -- ----------------------------------------------------------------------------
35 -- |-----------------------< load_parameter_qualifiers >----------------------|
36 -- ----------------------------------------------------------------------------
37 --
38 -- Description:
39 -- Procedure used to upload seed data for the table PAY_PARAMETER_QUALIFIERS
40 --
41 -- ----------------------------------------------------------------------------
42 procedure load_parameter_qualifiers
43 ( p_qualifier in varchar2
44 ,p_description in VARCHAR2
45 ,p_owner IN varchar2
46 ) AS
47
48 cursor ppq_exits(p_qualifier_name varchar2) is
49 select qualifier
50 from pay_parameter_qualifiers ppq
51 where ppq.qualifier = p_qualifier_name;
52 --
53
54 found boolean;
55 l_qualifier varchar2(30);
56 --
57 begin
58 -- upload code
59 found := FALSE;
60
61 for getrec in ppq_exits(p_qualifier) loop
62 l_qualifier := p_qualifier;
63 found := TRUE;
64 end loop;
65 --
66 hr_utility.trace('After getting qualifier_name : '||l_qualifier);
67
68 if(found = TRUE) then
69
70 UPDATE PAY_PARAMETER_QUALIFIERS SET
71 description = p_description
72 , LAST_UPDATED_BY = decode(p_owner,'SEED',1,0)
73 , LAST_UPDATE_DATE = SYSDATE
74 , LAST_UPDATE_LOGIN = fnd_global.login_id
75 WHERE qualifier = l_qualifier ;
76
77 else
78
79 hr_utility.trace('Inserting new qualifier : '||p_qualifier);
80
81 INSERT INTO PAY_PARAMETER_QUALIFIERS (
82 qualifier
83 , description
84 ,LAST_UPDATED_BY
85 ,LAST_UPDATE_DATE
86 ,LAST_UPDATE_LOGIN
87 ,CREATION_DATE
88 ,CREATED_BY )
89 VALUES ( p_qualifier
90 , p_description
91 , decode(p_owner,'SEED',1,0)
92 , SYSDATE
93 , fnd_global.login_id
94 , SYSDATE
95 , decode(p_owner,'SEED',1,0) );
96
97 end if;
98 --
99 exception
100 when others then
101 hr_utility.trace('An exception was raised : '||dbms_utility.format_error_stack );
102 --
103 end load_parameter_qualifiers;
104
105 --------
106 -- ----------------------------------------------------------------------------
107 -- |-----------------------< load_parameters >----------------------|
108 -- ----------------------------------------------------------------------------
109 --
110 -- Description:
111 -- Procedure used to upload seed data for the table PAY_PARAMETERS
112 --
113 -- ----------------------------------------------------------------------------
114 procedure load_parameters
115 ( p_parameter_name in varchar2
116 ,p_bg_name in varchar2
117 ,p_leg_code in varchar2
118 ,p_qualifier in varchar2
119 ,p_override_allowed in VARCHAR2
120 ,p_owner IN varchar2
121 ) AS
122
123 cursor pp_exits(p_parameter_name varchar2) is
124 select pp.parameter_id
125 from pay_parameters pp,
126 pay_parameter_definitions ppd,
127 per_business_groups pbg
128 where ppd.parameter_name = p_parameter_name
129 AND ppd.parameter_def_id = pp.parameter_def_id
130 AND pp.business_group_id = pbg.business_group_id(+)
131 and nvl(pbg.name,'CORE') = nvl(p_bg_name,'CORE')
132 and nvl(pp.legislation_code,'CORE') = nvl(p_leg_code,'CORE') ;
133
134 cursor get_param_def_id( p_parameter_name in varchar2) is
135 select ppd.parameter_def_id
136 from pay_parameter_definitions ppd
137 where ppd.parameter_name = p_parameter_name;
138 --
139 found boolean;
140 l_parameter_id number;
141 l_parameter_def_id number;
142 l_business_group_id number;
143 --
144 begin
145 -- upload code
146 found := FALSE;
147
148 for getrec in pp_exits(p_parameter_name) loop
149 l_parameter_id := getrec.parameter_id;
150 found := TRUE;
151 end loop;
152 hr_utility.trace('After getrec, l_parameter_id : '||l_parameter_id);
153 --
154 if (p_bg_name is not null) then
155 select business_group_id
156 into l_business_group_id
157 from per_business_groups
158 where name = p_bg_name;
159 end if;
160 --
161 if(found = TRUE) then
162
163 UPDATE PAY_PARAMETERS SET
164 qualifier = p_qualifier
165 , override_allowed = p_override_allowed
166 ,LAST_UPDATED_BY = decode(p_owner,'SEED',1,0)
167 ,LAST_UPDATE_DATE = SYSDATE
168 ,LAST_UPDATE_LOGIN = fnd_global.login_id
169 WHERE parameter_id = l_parameter_id ;
170
171 else
172
173 open get_param_def_id(p_parameter_name);
174 fetch get_param_def_id into l_parameter_def_id;
175 close get_param_def_id;
176
177 hr_utility.trace('Before inserting, l_param_def_id : '||l_parameter_def_id);
178
179 INSERT INTO PAY_PARAMETERS (
180 parameter_id
181 , parameter_def_id
182 , qualifier
183 , override_allowed
184 , business_group_id
185 , legislation_code
186 ,LAST_UPDATED_BY
187 ,LAST_UPDATE_DATE
188 ,LAST_UPDATE_LOGIN
189 ,CREATION_DATE
190 ,CREATED_BY )
191 VALUES (
192 PAY_PARAMETERS_S.nextval
193 , l_parameter_def_id
194 , p_qualifier
195 , p_override_allowed
196 , l_business_group_id
197 , p_leg_code
198 , decode(p_owner,'SEED',1,0)
199 , SYSDATE
200 , fnd_global.login_id
201 , SYSDATE
202 , decode(p_owner,'SEED',1,0) );
203
204 end if;
205 --
206 exception
207 when others then
208 hr_utility.trace('An exception was raised : '||dbms_utility.format_error_stack );
209 --
210 end load_parameterS;
211
212 --------
213 -- ----------------------------------------------------------------------------
214 -- |-----------------------< load_parameter_definitions >----------------------|
215 -- ----------------------------------------------------------------------------
216 --
217 -- Description:
218 -- Procedure used to upload seed data for the table PAY_PARAMETER_DEFINITIONS
219 --
220 -- ----------------------------------------------------------------------------
221 procedure load_parameter_definitions
222 ( p_parameter_name in varchar2
223 ,p_description in VARCHAR2
224 ,p_owner IN varchar2
225 ) AS
226
227 cursor ppd_exists( p_parameter_name varchar2 ) is
228 select ppd.parameter_def_id
229 from pay_parameter_definitions ppd
230 where ppd.parameter_name = p_parameter_name;
231 --
232 found boolean;
233 l_parameter_def_id number;
234 --
235 begin
236 -- upload code
237 found := FALSE;
238
239 for getrec in ppd_exists(p_parameter_name) loop
240 l_parameter_def_id := getrec.parameter_def_id;
241 found := TRUE;
242 end loop;
243
244 hr_utility.trace('After getrec, l_parameter_def_id: '||l_parameter_def_id);
245 --
246 if(found = TRUE) then
247
248 UPDATE PAY_PARAMETER_DEFINITIONS SET
249 description = p_description
250 ,LAST_UPDATED_BY = decode(p_owner,'SEED',1,0)
251 ,LAST_UPDATE_DATE = SYSDATE
252 ,LAST_UPDATE_LOGIN = fnd_global.login_id
253 WHERE parameter_def_id = l_parameter_def_id ;
254
255 ----
256
257 UPDATE PAY_PARAMETER_DEFINITIONS_TL SET
258 name = p_parameter_name
259 , source_language = userenv('LANG')
260 , description = p_description
261 ,LAST_UPDATED_BY = decode(p_owner,'SEED',1,0)
262 ,LAST_UPDATE_DATE = SYSDATE
263 ,LAST_UPDATE_LOGIN = fnd_global.login_id
264 WHERE parameter_def_id = l_parameter_def_id ;
265
266 else
267 hr_utility.trace('Inserting into pay_parameter_definitions... :'|| p_parameter_name);
268
269 INSERT INTO PAY_PARAMETER_DEFINITIONS (
270 parameter_def_id
271 ,parameter_name
272 ,description
273 ,LAST_UPDATED_BY
274 ,LAST_UPDATE_DATE
275 ,LAST_UPDATE_LOGIN
276 ,CREATION_DATE
277 ,CREATED_BY )
278 VALUES (
279 PAY_PARAMETER_DEFINITIONS_S.nextval
280 ,p_parameter_name
281 , p_description
282 , decode(p_owner,'SEED',1,0)
283 , SYSDATE
284 , fnd_global.login_id
285 , SYSDATE
286 , decode(p_owner,'SEED',1,0));
287
288 ---
289
290 INSERT INTO PAY_PARAMETER_DEFINITIONS_TL (
291 parameter_def_id
292 , NAME
293 , LANGUAGE
294 , source_language
295 , description
296 ,LAST_UPDATED_BY
297 ,LAST_UPDATE_DATE
298 ,LAST_UPDATE_LOGIN
299 ,CREATION_DATE
300 ,CREATED_BY )
301 select ppd.parameter_def_id
302 , p_parameter_name
303 , L.LANGUAGE_CODE
304 , userenv('LANG')
305 , p_description
306 , decode(p_owner,'SEED',1,0)
307 , SYSDATE
308 , fnd_global.login_id
309 , SYSDATE
310 , decode(p_owner,'SEED',1,0)
311 from FND_LANGUAGES L,
312 PAY_PARAMETER_DEFINITIONS PPD
313 where L.INSTALLED_FLAG in ('I', 'B')
314 and ppd.parameter_name = p_parameter_name
315 and not exists
316 (select NULL
317 from PAY_PARAMETER_DEFINITIONS_TL T
318 where T.NAME = p_parameter_name
319 and T.LANGUAGE = L.LANGUAGE_CODE);
320
321 end if;
322 --
323 exception
324 when others then
325 hr_utility.trace('An exception was raised : '||dbms_utility.format_error_stack );
326 --
327 end load_parameter_definitions;
328
329 --------
330
331 -- ----------------------------------------------------------------------------
332 -- |--------------------------------< translate_row >--------------------------|
333 -- ----------------------------------------------------------------------------
334 --
335 -- Description:
336 -- Procedure used to translate the seeded parameter definitions for the table
337 -- PAY_PARAMETER_DEFINITIONS_TL
338 --
339 -- ----------------------------------------------------------------------------
340 procedure TRANSLATE_ROW
341 ( p_parameter_name in VARCHAR2
342 ,p_trans_param_name IN varchar2
343 ,p_language in varchar2
344 ,p_source_language in varchar2
345 ,p_description in varchar2
346 ,p_owner IN varchar2
347 ) is
348 begin
349 UPDATE PAY_PARAMETER_DEFINITIONS_TL
350 SET name = nvl(p_trans_param_name,p_parameter_name),
351 last_update_date = SYSDATE,
352 last_updated_by = decode(p_owner,'SEED',1,0),
353 last_update_login = 0,
354 source_language = userenv('LANG')
355 WHERE userenv('LANG') IN (language,source_language)
356 AND parameter_def_id in
357 (select ppdt.parameter_def_id
358 from pay_parameter_definitions_tl ppdt,
359 pay_parameter_definitions ppd
360 where ppd.parameter_def_id = ppdt.parameter_def_id
361 AND ppd.parameter_name = p_parameter_name);
362 if (sql%notfound) then
363 null;
364 end if;
365 end TRANSLATE_ROW;
366
367 end PAY_LOAD_PARAMETERS;