1 PACKAGE BODY PER_RO_AEI_INFO AS
2 /* $Header: peroaeip.pkb 120.0.12020000.1 2013/05/15 05:09:42 kbajaj noship $ */
3
4
5 g_package varchar2(16):='PER_RO_AEI_INFO.';
6
7 --cursor to get the assignment start date and end date
8 cursor csr_assgt_start_date( P_ASSIGNMENT_ID in number)is
9 select min(effective_start_date),max(effective_end_date) from per_all_assignments_f where ASSIGNMENT_ID=P_ASSIGNMENT_ID;
10
11 --cursor to get the contract start date and end date
12 cursor csr_contract_start_date( P_CONTRACT_ID in number)is
13 select min(effective_start_date) from per_contracts_f pcf where contract_id=P_CONTRACT_ID;
14
15
16 --------------- procedure to validate the date of signing the contract-------------------
17 procedure check_contract_signed_date(P_ASSIGNMENT_ID in NUMBER ,
18 P_AEI_INFORMATION1 in varchar2)
19 as
20 --CURSOR to fetch the contract id a
21 CURSOR csr_get_contract_id(l_assignment_end_date date ) IS
22 select contract_id from per_all_assignments_f paaf
23 where paaf.assignment_id=p_assignment_id
24 and paaf.effective_end_date = l_assignment_end_date;
25
26 l_assignment_start_date date;
27 l_assignment_end_date date;
28 l_contract_start_date date;
29 l_contract_end_date date;
30 l_contract_id per_contracts_f.contract_id%type;
31 l_proc VARCHAR2(50);
32 begin
33 l_proc:=g_package||'check_contract_signed_date';
34
35
36 hr_utility.set_location('Entering '||l_proc,10);
37
38 if p_aei_information1 is not null then
39 open csr_assgt_start_date(P_ASSIGNMENT_ID);
40 fetch csr_assgt_start_date into l_assignment_start_date,l_assignment_end_date;
41 close csr_assgt_start_date;
42 open csr_get_contract_id(l_assignment_end_date);
43 fetch csr_get_contract_id into l_contract_id;
44 if csr_get_contract_id%NOTFOUND then
45 l_contract_id := NULL;
46 end if;
47
48 close csr_get_contract_id;
49
50 hr_utility.set_location('Contract Signed date=> ' || fnd_date.canonical_to_date(p_aei_information1), 20);
51 hr_utility.set_location('Assignment Start Date=> ' || l_assignment_start_date , 30);
52 hr_utility.set_location('Contract_id=> ' || l_contract_id, 40);
53 -- date of signing the contract should be atleast 1 day less than the assignment start date
54 if fnd_date.canonical_to_date(p_aei_information1) >= l_assignment_start_date then
55 hr_utility.set_message('800','HR_520042_INV_CONRACT_SIGND_DT');
56 hr_utility.raise_error;
57 end if;
58
59
60 if (l_contract_id is not null) then
61 open csr_contract_start_date(l_contract_id);
62 fetch csr_contract_start_date into l_contract_start_date;
63 close csr_contract_start_date;
64 hr_utility.set_location('Contract Start Date=> ' || l_contract_start_date, 30);
65
66 --contarct start date should not be less than the contract signing date
67 if fnd_date.canonical_to_date(P_AEI_INFORMATION1) > l_contract_start_date then
68 hr_utility.set_message('800','HR_520043_INV_CONRACT_SIGND_DT');
69 hr_utility.raise_error;
70 end if;
71 end if;
72 end if;
73
74 hr_utility.set_location('Leaving'||g_package||l_proc,50);
75
76
77
78 end check_contract_signed_date;
79
80 procedure CREATE_RO_ASSGT_EXTRA_INFO(
81 P_ASSIGNMENT_ID in NUMBER ,
82 p_information_type in varchar2,
83 p_aei_information_category in varchar2,
84 P_AEI_INFORMATION1 in varchar2
85 ) as
86 cursor csr_contract_details_exist IS
87 select 'x' from per_assignment_extra_info
88 where information_type = p_information_type
89 and aei_information_category = p_aei_information_category
90 and assignment_id=p_assignment_id;
91 l_proc VARCHAR2(60);
92 l_exist varchar2(1);
93
94 Begin
95 --hr_utility.trace_on(null,'Extra');
96 l_proc:=g_package||'CREATE_RO_ASSGT_EXTRA_INFO';
97
98 hr_utility.set_location('Entering '||l_proc,10);
99 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
100 hr_utility.set_location('Leaving : '||l_proc,10);
101 return;
102 END IF;
103
104 -- EIT already exist
105 open csr_contract_details_exist;
106 fetch csr_contract_details_exist into l_exist;
107 if csr_contract_details_exist%found then
108 hr_utility.set_message('800','HR_520044_ASG_EIT_EXIST');
109 hr_utility.raise_error;
110 END IF;
111 CLOSE csr_contract_details_exist;
112
113 hr_utility.set_location('P_ASSIGNMENT_ID=> ' || P_ASSIGNMENT_ID, 30);
114 hr_utility.set_location('P_AEI_INFORMATION1e => ' || P_AEI_INFORMATION1, 30);
115 check_contract_signed_date(P_ASSIGNMENT_ID,
116 P_AEI_INFORMATION1);
117 hr_utility.set_location('Leaving : '||l_proc,20);
118 --hr_utility.trace_off;
119 Exception
120 When others then
121 hr_utility.raise_error;
122 end; --end procedure
123
124
125 procedure UPDATE_RO_ASSGT_EXTRA_INFO(
126
127 P_ASSIGNMENT_EXTRA_INFO_ID in NUMBER,
128 p_aei_information_category in VARCHAR2,
129 P_AEI_INFORMATION1 in varchar2
130 )
131 as
132 cursor csr_get_assignment_id IS
133 select assignment_id from per_assignment_extra_info
134 where aei_information_category = p_aei_information_category
135 and ASSIGNMENT_EXTRA_INFO_ID = P_ASSIGNMENT_EXTRA_INFO_ID;
136 l_assignment_id per_all_assignments_f.assignment_id%type;
137 l_proc varchar2(60);
138
139 begin
140 l_proc:= g_package||'UPDATE_RO_ASSGT_EXTRA_INFO';
141 hr_utility.set_location('Entering : '||l_proc,10);
142 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
143 hr_utility.set_location('Leaving : '||l_proc,10);
144 return;
145 END IF;
146
147 open csr_get_assignment_id;
148 fetch csr_get_assignment_id into l_assignment_id;
149 close csr_get_assignment_id;
150
151 check_contract_signed_date(l_assignment_id,
152 P_AEI_INFORMATION1);
153
154 hr_utility.set_location('Leaving'||g_package||l_proc,40);
155
156 Exception
157 When others then
158 hr_utility.set_location('Leaving : '||Sqlerrm,10);
159 hr_utility.raise_error;
160
161 END UPDATE_RO_ASSGT_EXTRA_INFO; --end procedure update
162
163 END PER_RO_AEI_INFO ; --end package
164
[Home] [Help]