1 PACKAGE BODY HR_RO_CONTRACT_VAL AS
2 /* $Header: perocnrt.pkb 120.0.12020000.5 2013/05/08 07:15:48 abdash noship $ */
3 g_package VARCHAR2(30) := 'HR_RO_CONTRACT_VAL.';
4
5 PROCEDURE CHECK_RO_CONTRACT(p_person_id NUMBER
6 ,p_type VARCHAR2
7 ,p_status VARCHAR2
8 ,p_ctr_information1 VARCHAR2
9 )
10 IS
11
12 cursor csr_contract_legal_susp_reason is
13 select 'x'
14 from hr_lookups
15 where lookup_type = 'RO_LEGAL_SUSPENDED_REASON'
16 and enabled_flag = 'Y'
17 and lookup_code = p_ctr_information1;
18
19 cursor csr_contract_legal_term_reason is
20 select 'x'
21 from hr_lookups
22 where lookup_type = 'RO_LEGAL_TERMINATED_REASON'
23 and enabled_flag = 'Y'
24 and lookup_code = p_ctr_information1;
25
26 cursor csr_contract_react_reason is
27 select 'x'
28 from hr_lookups
32
29 where lookup_type = 'RO_LEGAL_REACTIVATED_REASON'
30 and enabled_flag = 'Y'
31 and lookup_code = p_ctr_information1;
33 cursor csr_get_securityid is
34 select distinct security_group_id
35 from per_all_people_f ppf
36 ,per_business_groups pbg
37 where ppf.person_id = p_person_id
38 and ppf.business_group_id= pbg.business_group_id
39 and SYSDATE between ppf.effective_start_date and ppf.effective_end_date;
40
41 cursor csr_get_status(p_security_id number) is
42 select 'x'
43 from fnd_lookup_values flv
44 where flv.lookup_type='CONTRACT_STATUS'
45 and flv.language = userenv('LANG')
46 and (flv.security_group_id = p_security_id OR flv.tag is null OR flv.tag like '%+RO%')
47 and flv.lookup_code = p_status;
48
49 cursor csr_get_type(p_security_id number) is
50 select 'x'
51 from fnd_lookup_values flv
52 where flv.lookup_type='CONTRACT_TYPE'
53 and flv.language = userenv('LANG')
54 and (flv.security_group_id = p_security_id OR flv.tag is null OR flv.tag like '%+RO%')
55 and flv.lookup_code = p_type;
56
57 l_proc varchar2(60);
58 l_exists varchar2(1);
59 l_security_id number :=0;
60
61 BEGIN
62 l_proc:= g_package||'CHECK_RO_CONTRACT';
63
64 hr_utility.set_location('Entering : '||l_proc,5);
65
66 open csr_get_securityid;
67 fetch csr_get_securityid into l_security_id;
68 close csr_get_securityid;
69
70
71 if p_type is not null then
72 open csr_get_type(l_security_id);
73 fetch csr_get_type into l_exists;
74 if csr_get_type%NOTFOUND then
75 hr_utility.set_message(800,'PO_CONTRACT_TYPE_INVALID');
76 hr_utility.raise_error;
77 end if;
78 close csr_get_type;
79 end if;
80
81 if p_status is not null then
82 open csr_get_status(l_security_id);
83 fetch csr_get_status into l_exists;
84 if csr_get_status%NOTFOUND then
85 hr_utility.set_message(800,'OKC_REP_IMP_INV_STATUS');
86 hr_utility.raise_error;
87 end if;
88 close csr_get_status;
89 end if;
90
91
92 if p_ctr_information1 is not null and p_status is null then
93 hr_utility.set_location('Entering : '||l_proc,10);
94 hr_utility.set_message(800,'HR_520027_CON_STA_REQ');
95 hr_utility.raise_error;
96 end if;
97
98 if p_status = 'A-SUSPENDED' then
99
100 hr_utility.set_location(l_proc,15);
101
102 open csr_contract_legal_susp_reason;
103 fetch csr_contract_legal_susp_reason into l_exists;
104 if csr_contract_legal_susp_reason%NOTFOUND then
105 hr_utility.set_message(800,'HR_520028_CON_INVAL_RES');
106 hr_utility.raise_error;
107 end if;
108 close csr_contract_legal_susp_reason;
109
110 hr_utility.set_location(l_proc,20);
111
112 elsif p_status = 'RO-TERM' then
113
114 hr_utility.set_location(l_proc,25);
115
116 open csr_contract_legal_term_reason;
117 fetch csr_contract_legal_term_reason into l_exists;
118 if csr_contract_legal_term_reason%NOTFOUND then
119 hr_utility.set_message(800,'HR_520028_CON_INVAL_RES');
120 hr_utility.raise_error;
121 end if;
122 close csr_contract_legal_term_reason;
123
124 hr_utility.set_location(l_proc,30);
125
126 elsif p_status = 'RO-REACT' then
127
128 hr_utility.set_location(l_proc,35);
129
130 open csr_contract_react_reason;
131 fetch csr_contract_react_reason into l_exists;
132 if csr_contract_react_reason%NOTFOUND then
133 hr_utility.set_message(800,'HR_520028_CON_INVAL_RES');
134 hr_utility.raise_error;
135 end if;
136 close csr_contract_react_reason;
137
138 hr_utility.set_location(l_proc,40);
139
140 end if;
141
142
143 hr_utility.set_location('Leaving : '||l_proc,50);
144
145 END CHECK_RO_CONTRACT;
146
147
148
149 PROCEDURE CREATE_RO_CONTRACT( p_person_id NUMBER
150 ,p_type VARCHAR2
151 ,p_status VARCHAR2
152 ,p_ctr_information1 VARCHAR2
153 ,p_effective_date DATE
154 )
155 IS
156
157 cursor csr_contract_exists is
158 select 'x'
159 from per_contracts_f
160 where person_id = p_person_id
161 and p_effective_date between effective_start_date and effective_end_date;
162
163 l_exists varchar2(1);
164
165 l_proc varchar2(60);
166
167 BEGIN
168 l_proc:= g_package||'CREATE_RO_CONTRACT';
169
170 hr_utility.set_location('Entering : '||l_proc,5);
171
172 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
173 hr_utility.set_location('Leaving : '||l_proc,10);
174 return;
175 END IF;
176
177 -- No longer required as multiple contracts
178 -- are allowed on same date.
179
180 /*
181 -- Another contract should not exists in the same period
182 -- for the person
183
184 open csr_contract_exists;
185 fetch csr_contract_exists into l_exists;
186 if csr_contract_exists%FOUND then
187 hr_utility.set_message(800,'HR_520026_CON_EXISTS');
188 hr_utility.raise_error;
189 end if;
190 close csr_contract_exists;
191
192 */
193
194 hr_utility.set_location(l_proc,15);
195
196 CHECK_RO_CONTRACT(p_person_id => p_person_id
197 ,p_type => p_type
198 ,p_status => p_status
199 ,p_ctr_information1 => p_ctr_information1
200 );
201
202
203 hr_utility.set_location('Leaving : '||l_proc,20);
204
205 END CREATE_RO_CONTRACT;
206
207 PROCEDURE UPDATE_RO_CONTRACT(p_person_id NUMBER
208 ,p_type VARCHAR2
209 ,p_status VARCHAR2
210 ,p_ctr_information1 VARCHAR2
211 )
212
213 IS
214
215 l_proc varchar2(60);
216
217 BEGIN
218 l_proc:= g_package||'UPDATE_RO_CONTRACT';
219 hr_utility.set_location('Entering : '||l_proc,5);
220 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
221 hr_utility.set_location('Leaving : '||l_proc,10);
222 return;
223 END IF;
224
225 CHECK_RO_CONTRACT(p_person_id => p_person_id
226 ,p_type => p_type
227 ,p_status => p_status
228 ,p_ctr_information1 => p_ctr_information1
229 );
230
231 hr_utility.set_location('Leaving : '||l_proc,20);
232
233 END UPDATE_RO_CONTRACT;
234
235 END HR_RO_CONTRACT_VAL;
[Home] [Help]