1 Package Body pqh_ver_bus as
2 /* $Header: pqverrhi.pkb 115.3 2002/12/05 00:30:42 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_ver_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_wrkplc_vldtn_ver_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_wrkplc_vldtn_ver_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , pqh_de_wrkplc_vldtn_vers ver
31 where ver.wrkplc_vldtn_ver_id = p_wrkplc_vldtn_ver_id
32 and pbg.business_group_id = ver.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'wrkplc_vldtn_ver_id'
48 ,p_argument_value => p_wrkplc_vldtn_ver_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 => nvl(p_associated_column1,'WRKPLC_VLDTN_VER_ID')
64 );
65 --
66 else
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 end if;
75 --
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_wrkplc_vldtn_ver_id in number
86 )
87 Return Varchar2 Is
88 --
89 -- Declare cursor
90 --
91 cursor csr_leg_code is
92 select pbg.legislation_code
93 from per_business_groups pbg
94 , pqh_de_wrkplc_vldtn_vers ver
95 where ver.wrkplc_vldtn_ver_id = p_wrkplc_vldtn_ver_id
96 and pbg.business_group_id = ver.business_group_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'wrkplc_vldtn_ver_id'
112 ,p_argument_value => p_wrkplc_vldtn_ver_id
113 );
114 --
115 if ( nvl(pqh_ver_bus.g_wrkplc_vldtn_ver_id, hr_api.g_number)
116 = p_wrkplc_vldtn_ver_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := pqh_ver_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
145 close csr_leg_code;
146 pqh_ver_bus.g_wrkplc_vldtn_ver_id := p_wrkplc_vldtn_ver_id;
147 pqh_ver_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------< chk_non_updateable_args >------------------------|
155 -- ----------------------------------------------------------------------------
156 -- {Start Of Comments}
157 --
158 -- Description:
159 -- This procedure is used to ensure that non updateable attributes have
160 -- not been updated. If an attribute has been updated an error is generated.
161 --
162 -- Pre Conditions:
163 -- g_old_rec has been populated with details of the values currently in
164 -- the database.
165 --
166 -- In Arguments:
167 -- p_rec has been populated with the updated values the user would like the
168 -- record set to.
169 --
170 -- Post Success:
171 -- Processing continues if all the non updateable attributes have not
172 -- changed.
173 --
174 -- Post Failure:
175 -- An application error is raised if any of the non updatable attributes
176 -- have been altered.
177 --
178 -- {End Of Comments}
179 -- ----------------------------------------------------------------------------
180 Procedure chk_non_updateable_args
181 (p_effective_date in date
182 ,p_rec in pqh_ver_shd.g_rec_type
183 ) IS
184 --
185 l_proc varchar2(200) := g_package || 'chk_non_updateable_args';
186 --
187 Begin
188 --
189 -- Only proceed with the validation if a row exists for the current
190 -- record in the HR Schema.
191 --
192 IF NOT pqh_ver_shd.api_updating
193 (p_wrkplc_vldtn_ver_id => p_rec.wrkplc_vldtn_ver_id
194 ,p_object_version_number => p_rec.object_version_number
195 ) THEN
196 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
197 fnd_message.set_token('PROCEDURE ', l_proc);
198 fnd_message.set_token('STEP ', '5');
199 fnd_message.raise_error;
200 END IF;
201 --
202 -- EDIT_HERE: Add checks to ensure non-updateable args have
203 -- not been updated.
204 --
205 If p_rec.Wrkplc_Vldtn_Id <> pqh_ver_shd.g_old_rec.wrkplc_vldtn_id Then
206 hr_utility.set_message(8302, 'PQH_DE_NONUPD_VALDTN_ID');
207 fnd_message.raise_error;
208 End If;
209
210 End chk_non_updateable_args;
211
212 Procedure Ckh_Tariff_Contract
213 (p_effective_date in date
214 ,p_rec in pqh_ver_shd.g_rec_type) is
215 --
216 l_proc Varchar2(200) := g_package||'Ckh_Tariff_Contract';
217 l_Emp_Type Pqh_De_Wrkplc_Vldtns.EMPLOYMENT_TYPE%TYPE;
218 l_Result Varchar2(1);
219
220 Cursor Tariff_Contract is
221 Select '1' from
222 Per_gen_Hierarchy_Nodes a, Per_gen_Hierarchy_Nodes b
223 Where
224 a.Node_Type = 'TARIFF_CONTRACT' and a.Entity_Id = p_rec.Tariff_Contract_Code And
225 b.Node_Type = 'EMP_TYPE' and b.Entity_Id = l_Emp_Type and
226 b.Hierarchy_Node_Id = a.Parent_Hierarchy_Node_Id And
227 a.Hierarchy_Version_Id in (Select Hierarchy_Version_Id from
228 Per_Gen_Hierarchy_versions a, Per_gen_Hierarchy b where
229 Type = 'REMUNERATION_REGULATION' AND
230 a.Hierarchy_Id = b.Hierarchy_Id And
231 p_effective_date between a.Date_From and Nvl(a.Date_To,Trunc(Sysdate)) and
232 b.Business_Group_Id=p_rec.Business_Group_Id
233 ) and
234 b.Hierarchy_Version_id = a.Hierarchy_Version_id;
235
236 Cursor Emp_Type is
237 Select Employment_type from
238 Pqh_De_Wrkplc_Vldtns
239 Where WRKPLC_VLDTN_ID = p_rec.WRKPLC_VLDTN_ID;
240
241 Begin
242
243 Open Emp_Type;
244 Fetch Emp_Type into l_Emp_Type;
245 Close Emp_Type;
246
247 hr_utility.set_location(l_proc, 10);
248 Open Tariff_Contract;
249 Fetch Tariff_Contract into l_Result;
250 If Tariff_Contract%NOTFOUND Then
251 Close Tariff_Contract;
252 hr_utility.set_message(8302,'PQH_DE_TRFCNT_VALDTN_DEF');
253 hr_utility.raise_error;
254 End If;
255 Close Tariff_Contract;
256 Exception
257 when app_exception.application_exception then
258 if hr_multi_message.exception_add
259 (p_associated_column1 => 'PQH_DE_WRKPLC_VLDTN_VERS.TARIFF_CONTRACT_CODE'
260 ) then
261 hr_utility.set_location(' Leaving:'||l_proc,60);
262 raise;
263 end if;
264 hr_utility.set_location(' Leaving:'||l_proc,70);
265 End Ckh_Tariff_Contract;
266
267 Procedure Ckh_Tariff_Group
268 (p_effective_date in date
269 ,p_rec in pqh_ver_shd.g_rec_type) is
270 --
271 l_proc Varchar2(72) := g_package||'Ckh_Tariff_Group';
272 l_Reslt Varchar2(1);
273
274 Cursor Tariff_Group is
275 Select '1' from
276 Per_gen_Hierarchy_Nodes a, Per_gen_Hierarchy_Nodes b
277 Where
278 a.Node_Type = 'TARIFF_GROUP' and a.Entity_Id = p_rec.Tariff_Group_Code And
279 b.Node_Type = 'TARIFF_CONTRACT' and b.Entity_Id = p_rec.Tariff_Contract_Code and
280 b.Hierarchy_Node_Id = a.Parent_Hierarchy_Node_Id And
281 a.Hierarchy_Version_Id in (Select Hierarchy_Version_Id from
282 Per_Gen_Hierarchy_versions a, Per_gen_Hierarchy b where
283 Type = 'REMUNERATION_REGULATION' AND
284 a.Hierarchy_Id = b.Hierarchy_Id And
285 p_effective_date between a.Date_From and Nvl(a.Date_To,Trunc(Sysdate)) and
286 b.Business_Group_Id=p_rec.Business_Group_Id ) and
287 b.Hierarchy_Version_id = a.Hierarchy_Version_id;
288
289 Begin
290 hr_utility.set_location(l_proc, 10);
291 Open Tariff_Group;
292 Fetch Tariff_Group into l_Reslt;
293 If Tariff_Group%NOTFOUND Then
294 Close Tariff_Group;
295 hr_utility.set_message(8302,'PQH_DE_TRFGRP_VALDTN_DEF');
296 hr_utility.raise_error;
297 End If;
298 Close Tariff_Group;
299 Exception
300 when app_exception.application_exception then
301 if hr_multi_message.exception_add
302 (p_associated_column1 => 'PQH_DE_WRKPLC_VLDTN_VERS.TARIFF_GROUP_CODE'
303 ) then
304 hr_utility.set_location(' Leaving:'||l_proc,60);
305 raise;
306 end if;
307 hr_utility.set_location(' Leaving:'||l_proc,70);
308 End Ckh_Tariff_Group;
309
310 Procedure Ckh_Grade
311 (p_effective_date in date
312 ,p_rec in pqh_ver_shd.g_rec_type) is
313 --
314 l_proc Varchar2(72) := g_package||'Ckh_Grade';
315 l_Reslt Varchar2(5);
316
317 Cursor Grade_Dtls(p_Grade_Id In Number) is
318 Select d.Node_Type, d.Entity_Id, e.Node_type, e.Entity_Id,
319 F.Node_type, f.Entity_Id, g.Node_Type, g.Entity_Id
320 from
321 Per_gen_Hierarchy_Nodes a, Per_gen_Hierarchy_Nodes b,
322 Per_gen_Hierarchy_Nodes c, Per_gen_Hierarchy_Nodes d,
323 Per_gen_Hierarchy_Nodes e, Per_gen_Hierarchy_Nodes f,
324 Per_gen_Hierarchy_Nodes g
325 Where
326 a.Node_TYpe = 'PAY_GRADE' and
327 a.Entity_Id = p_GRADE_ID and
328 b.Hierarchy_node_Id = a.Parent_Hierarchy_Node_Id and
329 c.Hierarchy_Node_Id = b.Parent_Hierarchy_Node_Id and
330 d.Hierarchy_Node_Id = c.Parent_Hierarchy_Node_Id and
331 e.Hierarchy_Node_Id = d.Parent_Hierarchy_Node_Id and
332 f.Hierarchy_Node_Id = e.Parent_Hierarchy_Node_Id and
333 g.Hierarchy_Node_Id = f.Parent_Hierarchy_Node_Id and
334 a.Hierarchy_Version_Id in (Select Hierarchy_Version_Id from
335 Per_Gen_Hierarchy_versions a, Per_gen_Hierarchy b where
336 Type = 'REMUNERATION_REGULATION' AND
337 a.Hierarchy_Id = b.Hierarchy_Id And
338 p_Effective_Date between
339 a.Date_From and Nvl(a.Date_To,Trunc(Sysdate))and
340 b.Business_Group_Id=p_rec.Business_Group_Id ) and
341 b.Hierarchy_Version_id = a.Hierarchy_Version_id and
342 c.Hierarchy_Version_id = a.Hierarchy_Version_id and
343 d.Hierarchy_Version_id = a.Hierarchy_Version_id and
344 e.Hierarchy_Version_id = a.Hierarchy_Version_id and
345 f.Hierarchy_Version_id = a.Hierarchy_Version_id and
346 g.Hierarchy_Version_id = a.Hierarchy_Version_id;
347
348 g_Node_type Per_gen_hierarchy_Nodes.Node_Type%TYPE;
349 f_Node_type Per_gen_hierarchy_Nodes.Node_Type%TYPE;
350 e_Node_type Per_gen_hierarchy_Nodes.Node_Type%TYPE;
351 d_Node_type Per_gen_hierarchy_Nodes.Node_Type%TYPE;
352 g_Entity_Id Per_gen_hierarchy_Nodes.Entity_Id%TYPE;
353 f_Entity_Id Per_gen_hierarchy_Nodes.Entity_Id%TYPE;
354 e_Entity_Id Per_gen_hierarchy_Nodes.Entity_Id%TYPE;
355 d_Entity_Id Per_gen_hierarchy_Nodes.Entity_Id%TYPE;
356 l_Grade_id Pqh_De_Wrkplc_Vldtn_Vers.USER_ENTERABLE_GRADE_ID%TYPE := NULL;
357 l_Cnt Number(2) := 0;
358 Begin
359 hr_utility.set_location(l_proc, 10);
360 If p_rec.USER_ENTERABLE_GRADE_ID Is not Null Then
361 l_Grade_Id := p_rec.USER_ENTERABLE_GRADE_ID;
362 Elsif p_rec.DERIVED_GRADE_ID Is Not Null Then
363 l_Grade_Id := p_rec.DERIVED_GRADE_ID;
364 End If;
365
366 If l_Grade_Id is Not Null Then
367 Open Grade_dtls(L_Grade_Id);
368 Loop
369 Fetch Grade_dtls into d_Node_Type, d_Entity_Id, e_Node_type, e_Entity_Id,
370 F_Node_type, f_Entity_Id, g_Node_Type, g_Entity_Id;
371 If g_Node_Type = 'TARIFF_CONTRACT' and g_Entity_Id = p_rec.Tariff_Contract_Code Then
372 l_Cnt := l_Cnt + 1;
373 Close Grade_Dtls;
374 Exit;
375 Elsif f_Node_Type = 'TARIFF_CONTRACT' and f_Entity_Id = p_rec.Tariff_Contract_Code Then
376 l_Cnt := l_Cnt + 1;
377 Close Grade_Dtls;
378 Exit;
379 Elsif e_Node_Type = 'TARIFF_CONTRACT' and e_Entity_Id = p_rec.Tariff_Contract_Code Then
380 l_Cnt := l_Cnt + 1;
381 Close Grade_Dtls;
382 Exit;
383 End If;
384 If Grade_dtls%NOTFOUND Then
385 Close Grade_Dtls;
386 hr_utility.set_message(8302,'PQH_DE_PAYGRD_VALDTN_DEF');
387 hr_utility.raise_error;
388 Exit;
389 End If;
390 End Loop;
391 If l_Cnt = 0 Then
392 Close Grade_Dtls;
393 hr_utility.set_message(8302,'PQH_DE_PAYGRD_VALDTN_DEF');
394 hr_utility.raise_error;
395 End If;
396 End If;
397 Exception
398 when app_exception.application_exception then
399 if hr_multi_message.exception_add
400 (p_associated_column1 => 'PQH_DE_WRKPLC_VLDTN_VERS.DERIVED_GRADE_ID'
401 ) then
402 hr_utility.set_location(' Leaving:'||l_proc,60);
403 raise;
404 end if;
405 hr_utility.set_location(' Leaving:'||l_proc,70);
406 End Ckh_Grade;
407
408 Procedure Ckh_Freeze
409 (p_rec in pqh_ver_shd.g_rec_type,
410 P_Chk In Varchar2 Default 'N') is
411
412 l_proc Varchar2(200) := g_package||'Ckh_Freeze';
413 l_Emp_Type Pqh_De_Wrkplc_Vldtns.EMPLOYMENT_TYPE%TYPE;
414 l_Result Varchar2(1);
415
416 Begin
417 If Nvl(P_Chk,'N') = 'D' and P_rec.Freeze = 'F' Then
418 hr_utility.set_message(8302,'PQH_DE_FRDEL_VALDTN_VER');
419 hr_utility.raise_error;
420 Else
421 If P_rec.Freeze = 'F' and Nvl(p_rec.USER_ENTERABLE_GRADE_ID, P_rec.DERIVED_GRADE_ID) is NULL Then
422 hr_utility.set_message(8302,'PQH_DE_FREZE_VALDTN_VER');
423 hr_utility.raise_error;
424 End If;
425 End If;
426 Exception
427 when app_exception.application_exception then
428 if hr_multi_message.exception_add
429 (p_associated_column1 => 'PQH_DE_WRKPLC_VLDTN_VERS.FREEZE'
430 ) then
431 hr_utility.set_location(' Leaving:'||l_proc,60);
432 raise;
433 end if;
434 hr_utility.set_location(' Leaving:'||l_proc,70);
435 End Ckh_Freeze;
436
437
438 --
439 -- ----------------------------------------------------------------------------
440 -- |---------------------------< insert_validate >----------------------------|
441 -- ----------------------------------------------------------------------------
442 Procedure insert_validate
443 (p_effective_date in date
444 ,p_rec in pqh_ver_shd.g_rec_type
445 ) is
446 --
447 l_proc varchar2(72) := g_package||'insert_validate';
448 --
449 Begin
450 hr_utility.set_location('Entering:'||l_proc, 5);
451 --
452 -- Call all supporting business operations
453 --
454
455
456 hr_api.validate_bus_grp_id
457 (p_business_group_id => p_rec.business_group_id
458 ,p_associated_column1 => pqh_ver_shd.g_tab_nam
459 || '.BUSINESS_GROUP_ID');
460 --
461 -- After validating the set of important attributes,
462 -- if Multiple Message detection is enabled and at least
463 -- one error has been found then abort further validation.
464 --
465 hr_multi_message.end_validation_set;
466
467
468 Ckh_Tariff_Contract(p_effective_date, p_rec);
469
470
471 Ckh_Tariff_Group(p_effective_date,p_rec);
472
473
474 Ckh_Grade(p_effective_date,p_rec);
475
476 Ckh_Freeze(p_rec,'N');
477 --
478 -- Validate Dependent Attributes
479 --
480 --
481 hr_utility.set_location(' Leaving:'||l_proc, 10);
482 End insert_validate;
483 --
484 -- ----------------------------------------------------------------------------
485 -- |---------------------------< update_validate >----------------------------|
486 -- ----------------------------------------------------------------------------
487 Procedure update_validate
488 (p_effective_date in date
489 ,p_rec in pqh_ver_shd.g_rec_type
490 ) is
491 --
492 l_proc varchar2(72) := g_package||'update_validate';
493 --
494 Begin
495 hr_utility.set_location('Entering:'||l_proc, 5);
496 --
497 -- Call all supporting business operations
498 --
499 hr_api.validate_bus_grp_id
500 (p_business_group_id => p_rec.business_group_id
501 ,p_associated_column1 => pqh_ver_shd.g_tab_nam
502 || '.BUSINESS_GROUP_ID');
503 --
504 -- After validating the set of important attributes,
505 -- if Multiple Message detection is enabled and at least
506 -- one error has been found then abort further validation.
507 --
508 hr_multi_message.end_validation_set;
509 --
510 -- Validate Dependent Attributes
511 --
512 chk_non_updateable_args
513 (p_effective_date => p_effective_date
514 ,p_rec => p_rec
515 );
516 --
517 --
518
519 Ckh_Tariff_Contract(p_effective_date, p_rec);
520
521 Ckh_Tariff_Group(p_effective_date,p_rec);
522
523 Ckh_Grade(p_effective_date,p_rec);
524
525 Ckh_Freeze(p_rec,'N');
526
527 hr_utility.set_location(' Leaving:'||l_proc, 10);
528
529 End update_validate;
530 --
531 -- ----------------------------------------------------------------------------
532 -- |---------------------------< delete_validate >----------------------------|
533 -- ----------------------------------------------------------------------------
534 Procedure delete_validate
535 (p_rec in pqh_ver_shd.g_rec_type
536 ) is
537 --
538 l_proc varchar2(72) := g_package||'delete_validate';
539 --
540 Begin
541 hr_utility.set_location('Entering:'||l_proc, 5);
542 --
543 -- Call all supporting business operations
544 --
545 Ckh_Freeze(p_rec,'D');
546 hr_utility.set_location(' Leaving:'||l_proc, 10);
547 End delete_validate;
548 --
549 end pqh_ver_bus;
[Home] [Help]