1 PACKAGE BODY PER_RO_ASSIGNMENT_VAL AS
2 /* $Header: peroasgn.pkb 120.0.12020000.3 2013/05/16 11:55:14 kbajaj noship $ */
3 g_package VARCHAR2(30) := 'PER_RO_ASSIGNMENT_VAL.';
4 PROCEDURE create_asg_validate
5 ( p_contract_id in number
6 ,p_effective_date IN DATE
7 ,p_person_id IN NUMBER
8 ) IS
9
10
11 --- CURSOR TO VAIDATE TEH CONTRACT_ID , ONE CONTRACT SHOULD BE LINKED WITH ONLY ONE ASSIGNMENT
12 CURSOR csr_assignment_contract is
13 select 'x' from per_all_assignments_f paaf
14 where paaf.person_id = p_person_id
15 and paaf.contract_id = p_contract_id
16 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
17 l_proc VARCHAR2(60);
18 l_exist varchar2(1);
19
20
21 BEGIN
22 l_proc := g_package||'create_asg_validate';
23 --hr_utility.trace_on(null,'Extra1');
24 hr_utility.set_location('Entering : '||l_proc,10);
25 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
26 hr_utility.set_location('Leaving : '||l_proc,10);
27 return;
28 END IF;
29 --
30 hr_utility.set_location('Contract_id=> ' || P_contract_id, 40);
31 if p_contract_id is not null then
32 open csr_assignment_contract;
33 fetch csr_assignment_contract into l_exist;
34 if csr_assignment_contract%found then
35 hr_utility.set_message('800','HR_520045_DUP_CONTRACT_ID');
36 hr_utility.raise_error;
37 end if;
38 close csr_assignment_contract;
39
40 end IF;
41
42 hr_utility.set_location('Leaving : '||l_proc,20);
43 --hr_utility.trace_off;
44 END create_asg_validate;
45
46 -------------------------------------------------------------------------------------------------------
47
48 PROCEDURE update_asg_validate
49 (p_effective_date IN DATE
50 ,p_assignment_id IN NUMBER
51 ,P_contract_id in number
52 ) IS
53
54 CURSOR csr_assignment_contract is
55 select 'x' from per_all_assignments_f paaf
56 where paaf.person_id = (select paaf1.person_id from per_all_assignments_f paaf1 where paaf1.ASSIGNMENT_ID = p_assignment_id
57 and p_effective_date between paaf1.effective_start_date and paaf1.effective_end_date)
58 and paaf.contract_id = p_contract_id
59 AND paaf.ASSIGNMENT_ID <> p_assignment_id
60 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
61
62 l_proc VARCHAR2(60);
63 l_exist varchar2(1);
64 l_contract_start_date per_contracts_f.effective_start_date%type;
65 l_assignment_start_date per_all_assignments_f.effective_start_date%type := p_effective_date;
66 l_contract_signed_date PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION1%type;
67
68 BEGIN
69 l_proc := g_package||'update_asg_validate';
70 --hr_utility.trace_on(null,'Extra');
71 hr_utility.set_location('Entering : '||l_proc,10);
72 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
73 hr_utility.set_location('Leaving : '||l_proc,10);
74 return;
75 END IF;
76 hr_utility.set_location('Contract_id=> ' || P_contract_id, 40);
77 -- VALIDATE THE DUPLICATE CONTRACT id
78 if p_contract_id is not null then
79 open csr_assignment_contract;
80 fetch csr_assignment_contract into l_exist;
81 if csr_assignment_contract%found then
82 hr_utility.set_message('800','HR_520045_DUP_CONTRACT_ID');
83 hr_utility.raise_error;
84 end if;
85 close csr_assignment_contract;
86
87
88 end IF;
89
90 hr_utility.set_location('Leaving : '||l_proc,20);
91 --hr_utility.trace_off;
92 END update_asg_validate;
93
94 END PER_RO_ASSIGNMENT_VAL;
[Home] [Help]