[Home] [Help]
PACKAGE BODY: APPS.PAY_UPGRADE_LEGISLATIONS_PKG
Source
1 PACKAGE BODY PAY_UPGRADE_LEGISLATIONS_PKG AS
2 /* $Header: pypul01t.pkb 115.0 2003/11/21 03:07 tvankayl noship $ */
3
4 g_package varchar2(33) := ' pay_upgrade_legislations_pkg.';
5
6 PROCEDURE chk_unique ( p_upgrade_definition_id in number
7 , p_legislation_code in varchar2
8 ) is
9 --
10 cursor csr_unique is
11 select null from pay_upgrade_legislations
12 where upgrade_definition_id = p_upgrade_definition_id
13 and legislation_code = p_legislation_code ;
14 --
15 l_proc varchar2(72) := g_package||'chk_unique';
16 l_exists varchar2(1);
17 --
18 BEGIN
19 --
20 hr_utility.set_location('Entering:'|| l_proc, 10);
21
22 open csr_unique;
23 fetch csr_unique into l_exists;
24
25 if csr_unique%found then
26
27 close csr_unique;
28 fnd_message.set_name( 'PAY', 'PAY_33191_UPG_LEG_EXISTS' );
29 fnd_message.raise_error;
30
31 end if;
32 close csr_unique;
33
34 hr_utility.set_location('Leaving:'|| l_proc, 20);
35 --
36 END chk_unique;
37
38 PROCEDURE chk_mandatory_arg( p_argument in varchar2 , p_column in varchar2 )
39 is
40 --
41 l_proc varchar2(72) := g_package||'chk_mandatory_arg';
42 --
43 BEGIN
44 --
45 hr_utility.set_location('Entering:'|| l_proc, 10);
46
47 if p_argument is null then
48 fnd_message.set_name( 'PAY', 'PAY_75178_NO_DATA' );
49 fnd_message.set_token('VALUE1' , l_proc);
50 fnd_message.set_token('VALUE2' , p_column ) ;
51 fnd_message.raise_error;
52 end if;
53
54 hr_utility.set_location(' Leaving:'|| l_proc, 20);
55 --
56 END chk_mandatory_arg;
57
58 PROCEDURE chk_upgrade_definition_id( p_upgrade_definition_id in number )
59 is
60 --
61 l_legislatively_enabled PAY_UPGRADE_DEFINITIONS.LEGISLATIVELY_ENABLED%TYPE;
62 l_proc varchar2(100) := g_package || 'chk_upgrade_definition_id';
63 --
64 cursor csr_upgrade_definition is
65 select legislatively_enabled
66 from pay_upgrade_definitions
67 where upgrade_definition_id = p_upgrade_definition_id ;
68 --
69 BEGIN
70 --
71 hr_utility.set_location('Entering:'|| l_proc, 10);
72
73 open csr_upgrade_definition ;
74 fetch csr_upgrade_definition into l_legislatively_enabled;
75
76 if csr_upgrade_definition%notfound then
77
78 close csr_upgrade_definition;
79 fnd_message.set_name('PAY', 'PAY_33192_INVALID_UPGDEF');
80 fnd_message.raise_error;
81
82 end if;
83 close csr_upgrade_definition;
84
85 if l_legislatively_enabled <> 'Y' then
86
87 fnd_message.set_name('PAY', 'PAY_33193_UPG_CANNOT_ENABLE');
88 fnd_message.raise_error;
89
90 end if;
91
92 hr_utility.set_location('Leaving:'|| l_proc, 20);
93 --
94 END chk_upgrade_definition_id;
95
96 PROCEDURE chk_legislation_code( p_legislation_code in varchar2 )
97 is
98 --
99 cursor csr_legislation_code is
100 select null
101 from fnd_territories
102 where territory_code = p_legislation_code ;
103 --
104 l_exists varchar2(1);
105 l_proc varchar2(100) := g_package || 'chk_legislation_code';
106 --
107 BEGIN
108 --
109 hr_utility.set_location('Entering:'|| l_proc, 10);
110
111 open csr_legislation_code;
112 fetch csr_legislation_code into l_exists ;
113
114 if csr_legislation_code%notfound then
115 close csr_legislation_code;
116 fnd_message.set_name('PAY', 'PAY_33177_LEG_CODE_INVALID');
117 fnd_message.raise_error;
118 end if;
119 close csr_legislation_code;
120
121 hr_utility.set_location(' Leaving:'|| l_proc, 20);
122 --
123 END chk_legislation_code;
124
125 PROCEDURE chk_delete ( p_upgrade_definition_id in number
126 ,p_legislation_code in varchar2 )
127 is
128 --
129 cursor csr_delete is
130 select null from pay_upgrade_status
131 where
132 upgrade_definition_id = p_upgrade_definition_id
133 and (
134 ( legislation_code is not null and legislation_code = p_legislation_code )
135 or ( business_group_id is not null and p_legislation_code = hr_api.return_legislation_code(business_group_id) ) );
136
137 --
138 l_exists varchar2(1);
139 l_proc varchar2(100) := g_package || 'chk_delete';
140 --
141 BEGIN
142 --
143 hr_utility.set_location('Entering:'|| l_proc, 10);
144
145 open csr_delete;
146 fetch csr_delete into l_exists ;
147
148 if csr_delete%found then
149 close csr_delete;
150 fnd_message.set_name('PAY', 'PAY_33188_DELETION_NOT_ALLOWED');
151 fnd_message.raise_error;
152 end if;
153 close csr_delete;
154
155 hr_utility.set_location(' Leaving:'|| l_proc, 20);
156 --
157 END chk_delete;
158
159
160 PROCEDURE Insert_Row (
161 P_UPGRADE_DEFINITION_ID in NUMBER
162 ,P_LEGISLATION_CODE in VARCHAR2
163 ,P_LAST_UPDATE_DATE in DATE
164 ,P_LAST_UPDATED_BY in NUMBER
165 ,P_LAST_UPDATE_LOGIN in NUMBER
166 ,P_CREATED_BY in NUMBER
167 ,P_CREATION_DATE in DATE
168 ) is
169 --
170 l_exists varchar2(1);
171 l_proc varchar2(100) := g_package || 'Insert_Row';
172
173 cursor csr_exists is
174 select null from PAY_UPGRADE_LEGISLATIONS
175 where upgrade_definition_id = p_upgrade_definition_id
176 and legislation_code = p_legislation_code;
177 --
178 BEGIN
179 --
180 hr_utility.set_location('Entering:'|| l_proc, 10);
181
182 -- Check for mandatory arguments.
183
184 chk_mandatory_arg( p_upgrade_definition_id , 'UPGRADE_DEFINITION_ID' );
185
186 chk_mandatory_arg( p_legislation_code, 'LEGISLATION_CODE' );
187
188
189 -- Check for valid Upgrade Definition.
190
191 chk_upgrade_definition_id( p_upgrade_definition_id );
192
193
194 -- Check for valid Legislation code.
195
196 chk_legislation_code( p_legislation_code );
197
198
199 -- Check for Uniqueness
200
201 chk_unique( p_upgrade_definition_id, p_legislation_code );
202
203
204 insert into PAY_UPGRADE_LEGISLATIONS (
205 UPGRADE_DEFINITION_ID,
206 LEGISLATION_CODE,
207 CREATION_DATE,
208 CREATED_BY,
209 LAST_UPDATE_DATE,
210 LAST_UPDATED_BY,
211 LAST_UPDATE_LOGIN
212 ) values (
213 p_upgrade_definition_id,
214 p_legislation_code,
215 p_creation_date,
216 p_created_by,
217 p_last_update_date,
218 p_last_updated_by,
219 p_last_update_login );
220
221 open csr_exists;
222 fetch csr_exists into l_exists;
223
224 if csr_exists%notfound then
225 close csr_exists;
226 raise no_data_found;
227 end if;
228
229 close csr_exists;
230
231 hr_utility.set_location(' Leaving:'|| l_proc, 20);
232 --
233 END Insert_Row;
234
235 PROCEDURE Lock_Row (
236 P_UPGRADE_DEFINITION_ID in NUMBER
237 , P_LEGISLATION_CODE in VARCHAR2
238 ) is
239 --
240 cursor csr_lck is
241 select null
242 from PAY_UPGRADE_LEGISLATIONS
243 where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID
244 and LEGISLATION_CODE = P_LEGISLATION_CODE
245 for update of UPGRADE_DEFINITION_ID nowait;
246 --
247 recinfo csr_lck%rowtype;
248 l_exists varchar2(1);
249 l_proc varchar2(100) := g_package || 'Lock_Row';
250 --
251 BEGIN
252 --
253 hr_utility.set_location('Entering:'|| l_proc, 10);
254
255 -- Lock the table record.
256
257 open csr_lck;
258 fetch csr_lck into recinfo;
259 if (csr_lck%notfound) then
260 close csr_lck;
261 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
262 app_exception.raise_exception;
263 end if;
264 close csr_lck;
265
266
267 hr_utility.set_location(' Leaving:'|| l_proc, 20);
268 return;
269 --
270 END Lock_Row;
271
272 PROCEDURE Delete_Row (
273 P_UPGRADE_DEFINITION_ID in NUMBER
274 ,P_LEGISLATION_CODE in VARCHAR2
275 )is
276 --
277 l_proc varchar2(100) := g_package || 'Delete_Row';
278 --
279 BEGIN
280 --
281 hr_utility.set_location('Entering:'|| l_proc, 10);
282
283 chk_mandatory_arg( p_upgrade_definition_id , 'UPGRADE_DEFINITION_ID' );
284
285 chk_mandatory_arg( p_legislation_code, 'LEGISLATION_CODE' );
286
287 chk_delete ( p_upgrade_definition_id , p_legislation_code );
288
289 delete from PAY_UPGRADE_LEGISLATIONS
290 where UPGRADE_DEFINITION_ID = P_UPGRADE_DEFINITION_ID
291 and LEGISLATION_CODE = P_LEGISLATION_CODE ;
292
293 if (sql%notfound) then
294 raise no_data_found;
295 end if;
296
297 hr_utility.set_location(' Leaving:'|| l_proc, 20);
298 --
299 END Delete_Row;
300
301 PROCEDURE Load_Row (
302 P_SHORT_NAME in VARCHAR2
303 , P_LEGISLATION_CODE in VARCHAR2
304 , P_OWNER in VARCHAR2
305 ) is
306 --
307 l_proc varchar2(100) := g_package || 'Load_Row';
308 l_upgrade_definition_id PAY_UPGRADE_LEGISLATIONS.UPGRADE_DEFINITION_ID%TYPE;
309
310 l_sysdate date := sysdate;
311 l_created_by PAY_UPGRADE_LEGISLATIONS.CREATED_BY%TYPE;
312 l_creation_date PAY_UPGRADE_LEGISLATIONS.CREATION_DATE%TYPE;
313 l_last_updated_by PAY_UPGRADE_LEGISLATIONS.LAST_UPDATED_BY%TYPE;
314 l_last_update_login PAY_UPGRADE_LEGISLATIONS.LAST_UPDATE_LOGIN%TYPE;
315 l_last_update_date PAY_UPGRADE_LEGISLATIONS.LAST_UPDATE_DATE%TYPE;
316
317 cursor csr_existing is
318 select pul.upgrade_definition_id
319 from pay_upgrade_definitions pud, pay_upgrade_legislations pul
320 where upper(pud.short_name) = upper(p_short_name)
321 and pud.upgrade_definition_id = pul.upgrade_definition_id
322 and pul.legislation_code = p_legislation_code;
323
324 cursor csr_upgrade_definition_id is
325 select pud.upgrade_definition_id
326 from pay_upgrade_definitions pud
327 where upper(pud.short_name) = upper(p_short_name);
328
329 --
330 BEGIN
331 --
332 hr_utility.set_location('Entering:'|| l_proc, 10);
333
334 if p_owner = 'SEED' then
335 hr_general2.init_fndload
336 (p_resp_appl_id => 801
337 ,p_user_id => 1
338 );
339 else
340 hr_general2.init_fndload
341 (p_resp_appl_id => 801
342 ,p_user_id => -1
343 );
344 end if;
345
346 -- Set the WHO Columns
347 l_created_by := fnd_global.user_id;
348 l_creation_date := l_sysdate;
349 l_last_update_date := l_sysdate;
350 l_last_updated_by := fnd_global.user_id;
351 l_last_update_login := fnd_global.login_id;
352
353
354 open csr_existing;
355 fetch csr_existing into l_upgrade_definition_id;
356
357 if csr_existing%FOUND then
358
359 close csr_existing;
360 return;
361 else
362
363 close csr_existing;
364
365 -- Get the parent upgrade definition id.
366
367 open csr_upgrade_definition_id;
368 fetch csr_upgrade_definition_id into l_upgrade_definition_id;
369
370 if csr_upgrade_definition_id%notfound then
371 close csr_upgrade_definition_id;
372
373 fnd_message.set_name('PAY', 'PAY_33192_INVALID_UPGDEF');
374 fnd_message.raise_error;
375 end if;
376
377 close csr_upgrade_definition_id;
378
379
380 Insert_Row (
381 P_UPGRADE_DEFINITION_ID => l_upgrade_definition_id
382 ,P_LEGISLATION_CODE => p_legislation_code
383 ,P_LAST_UPDATE_DATE => l_last_update_date
384 ,P_LAST_UPDATED_BY => l_last_updated_by
385 ,P_LAST_UPDATE_LOGIN => l_last_update_login
386 ,P_CREATED_BY => l_created_by
387 ,P_CREATION_DATE => l_creation_date
388 );
389
390 end if;
391
392 hr_utility.set_location('Leaving:'|| l_proc, 20);
393 --
394 END Load_Row;
395
396 END PAY_UPGRADE_LEGISLATIONS_PKG;
397