[Home] [Help]
PACKAGE BODY: APPS.HR_RO_CONTACT_REL_VAL
Source
1 PACKAGE BODY HR_RO_CONTACT_REL_VAL AS
2 /* $Header: perocnrl.pkb 120.0.12020000.3 2013/05/10 08:46:01 kbajaj noship $ */
3 g_package VARCHAR2(30) := 'HR_RO_CONTACT_REL_VAL.';
4
5 PROCEDURE CHECK_RO_CONTACT(p_person_id number
6 ,p_first_name VARCHAR2
7 ,p_national_identifier VARCHAR2
8 ,p_business_group_id NUMBER
9 ,p_sex VARCHAR2
10 ,p_date_of_birth DATE
11 ,p_person_type_id NUMBER
12 ,p_country_of_birth VARCHAR2
13 ,p_region_of_birth VARCHAR2
14 ,p_per_information1 VARCHAR2-- residence
15 ,p_per_information2 VARCHAR2 -- citizenship
16 ) IS
17 l_proc VARCHAR2(72);
18 l_valid_ni NUMBER DEFAULT 10;
19 l_exist varchar2(1);
20 cursor csr_person_type is
21 select 'x'
22 from per_person_types pet
23 where pet.system_person_type = 'OTHER'
24 and pet.business_group_id = p_business_group_id
25 and pet.person_type_id = p_person_type_id
26 and pet.active_flag = 'Y';
27 BEGIN
28 l_proc := g_package||'CHECK_RO_CONTACT';
29 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
30 hr_utility.set_location('Leaving : '||l_proc,10);
31 return;
32 END IF;
33
34 IF nvl(p_person_type_id,hr_api.g_number) <> hr_api.g_number THEN
35 open csr_person_type;
36 fetch csr_person_type
37 into l_exist;
38 if csr_person_type%notfound then
39 close csr_person_type;
40 hr_utility.set_message(801, 'HR_7513_PER_TYPE_INVALID');
41 hr_utility.raise_error;
42 end if;
43 close csr_person_type;
44 end if;
45
46
47 -- first name mandatory
48 hr_api.mandatory_arg_error
49 (p_api_name => l_proc,
50 p_argument => 'First Name',
51 p_argument_value => p_first_name
52 );
53 --Gender Mandatory
54 hr_api.mandatory_arg_error
55 (p_api_name => l_proc,
56 p_argument => 'Gender',
57 p_argument_value => p_sex
58 );
59 -- NPC/FRN mandatory
60 hr_api.mandatory_arg_error
61 (p_api_name => l_proc,
62 p_argument => hr_general.decode_lookup('RO_FORM_LABELS','NATIONAL_IDENTIFIER'),
63 p_argument_value => p_national_identifier
64 );
65
66 IF nvl(p_national_identifier,hr_api.g_varchar2) <> hr_api.g_varchar2 THEN
67 l_valid_ni := 10;
68 l_valid_ni := per_ro_ni_validation.validate_ni (p_person_id
69 ,p_business_group_id
70 ,p_national_identifier
71 ,p_sex
72 ,p_date_of_birth
73 ,p_country_of_birth
74 ,p_region_of_birth
75 ,p_per_information2
76 ,p_per_information1);
77 hr_utility.set_location('Person validation :: '||l_valid_ni,4);
78 IF l_valid_ni <> 0 THEN
79 IF l_valid_ni = 1 THEN
80 hr_utility.set_message(800,'HR_520037_INVALID_NI_DIGITS');
81 elsif l_valid_ni = 2 THEN
82 hr_utility.set_message(800,'HR_520038_INVALID_NI');
83 elsif l_valid_ni = 3 THEN
84 hr_utility.set_message(800,'HR_520039_INVALID_NI_DOB');
85 elsif l_valid_ni = 4 THEN
86 hr_utility.set_message(800,'HR_520040_INVALID_NI_GENDER');
87 elsif l_valid_ni = 5 THEN
88 hr_utility.set_message(800,'HR_520041_INVALID_NI_BIRTH');
89 END IF;
90 hr_utility.raise_error;
91 -- raise warning when 6
92 END IF;
93 END IF;
94 hr_utility.set_location('Leaving : '||l_proc,10);
95 END CHECK_RO_CONTACT;
96
97 /*-------------------------------------------------------------------------
98 ---- CREATE_RO_CONTACT_PERSON start
99 ------------------------------------------------------------------------ */
100
101 PROCEDURE create_ro_contact_person(p_last_name VARCHAR2
102 ,p_first_name VARCHAR2
103 ,p_date_of_birth DATE
104 ,p_national_identifier VARCHAR2
105 ,p_business_group_id NUMBER
106 ,p_sex VARCHAR2
107 ,p_person_type_id NUMBER
108 ,p_country_of_birth VARCHAR2
109 ,p_region_of_birth VARCHAR2
110 ,p_per_information1 VARCHAR2-- residence
111 ,p_per_information2 VARCHAR2 -- citizenship
112 ) IS
113 l_proc VARCHAR2(72);
114 l_person_type_id per_person_types.person_type_id%type := p_person_type_id;
115 l_valid_ni NUMBER DEFAULT 10;
116 BEGIN
117 l_proc := g_package||'CREATE_RO_CONTACT_PERSON';
118 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
119 hr_utility.set_location('Leaving : '||l_proc,10);
120 return;
121 END IF;
122
123 per_per_bus.chk_person_type
124 (p_person_type_id => l_person_type_id
125 ,p_business_group_id => p_business_group_id
126 ,p_expected_sys_type => 'OTHER'
127 );
128
129 check_ro_contact (NULL
130 ,p_first_name
131 ,p_national_identifier
132 ,p_business_group_id
133 ,p_sex
134 ,p_date_of_birth
135 ,p_person_type_id
136 ,p_country_of_birth
137 ,p_region_of_birth
138 ,p_per_information1
139 ,p_per_information2);
140 hr_utility.set_location('Leaving : '||l_proc,10);
141
142 END;
143
144
145 /*-------------------------------------------------------------------------
146 ---- CREATE_RO_CONTACT_PERSON end
147 ------------------------------------------------------------------------ */
148
149 /*-------------------------------------------------------------------------
150 ---- CREATE_RO_CONTACT_REL start
151 ------------------------------------------------------------------------ */
152
153 PROCEDURE create_ro_contact_rel
154 (p_first_name IN varchar2
155 ,p_date_of_birth IN date
156 ,p_national_identifier IN varchar2
157 ,p_business_group_id IN number
158 ,p_sex IN varchar2
159 ,p_date_start IN date
160 ,p_date_end IN date
161 ,p_contact_person_id IN number
162 ,p_person_id IN number
163 ,p_contact_type IN varchar2
164 ,p_person_type_id NUMBER) IS
165
166 cursor cur_crl is select 1 from per_contact_relationships
167 where contact_person_id = p_contact_person_id
168 and person_id = p_person_id
169 and contact_type in ('EC','ES','PR1','PR2','OTH')
170 and P_CONTACT_TYPE in ('EC','ES','PR1','PR2','OTH')
171 and (date_start >= p_date_start
172 OR nvl(date_end,hr_general.end_of_time) <= nvl(p_date_end,hr_general.end_of_time));
173
174 cursor csr_dob_contact is
175 select date_of_birth from per_all_people_f where person_id = P_CONTACT_PERSON_ID;
176
177 /*cursor csr_person_type is
178 select USER_PERSON_TYPE from per_person_types where PERSON_TYPE_ID = P_PERSON_TYPE_ID;*/
179
180 nDummy_val number;
181 l_dob_contact per_all_people_f.date_of_birth%TYPE;
182 l_valid_ni NUMBER DEFAULT 10;
183 --l_user_person_type varchar2(60);--per_person_type.user_person_type%Type;
184 l_proc varchar2(60);
185 BEGIN
186 l_proc:=g_package||'CREATE_RO_CONTACT_REL';
187 hr_utility.set_location('Entering : '||l_proc,10);
188 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
189 hr_utility.set_location('Leaving : '||l_proc,10);
190 return;
191 END IF;
192
193 if P_CONTACT_TYPE NOT IN ('EC','ES','PR1','PR2','OTH') THEN
194 hr_utility.set_message(800,'HR_RO_INV_CONTACT_TYPE');
195 hr_utility.raise_error;
196 End If;
197
198
199
200 open cur_crl;
201 fetch cur_crl into nDummy_val;
202 IF cur_crl%found then
203 Close cur_crl;
204 hr_utility.set_message(800,'HR_CON_REL_OVERLAP_PL');
205 hr_utility.raise_error;
206 End If;
207 Close cur_crl;
208
209 check_ro_contact (NULL
210 ,p_first_name
211 ,p_national_identifier
212 ,p_business_group_id
213 ,p_sex
214 ,p_date_of_birth
215 ,p_person_type_id
216 ,null
217 ,null
218 ,null
219 ,null);
220
221 /* Date of birth is mandatory in case of Employee'Childrem contact */
222 if P_DATE_OF_BIRTH is null and P_CONTACT_TYPE = 'EC' then
223 hr_utility.set_message(800,'HR_CON_DOB_REQD_PL');
224 hr_utility.raise_error;
225 end if;
226
227
228 END CREATE_RO_CONTACT_REL;
229
230 -------------------------------------------------------------------------
231 ---- CREATE_RO_CONTACT_REL end
232 ------------------------------------------------------------------------
233
234 ------------------------------------------------------------------------
235 -- UPDATE_RO_CONTACT_REL start
236 -------------------------------------------------------------------------
237 PROCEDURE update_ro_contact_rel
238 (p_contact_relationship_id IN number
239 ,p_date_start IN date
240 ,p_date_end IN date
241 ,p_contact_type IN varchar2) IS
242
243 cursor cur_crl is select 1 from per_contact_relationships
244 where contact_person_id in
245 (select contact_person_id from per_contact_relationships where contact_relationship_id = p_contact_relationship_id)
246 and person_id in (select person_id from per_contact_relationships where contact_relationship_id = p_contact_relationship_id)
247 and contact_relationship_id <> p_contact_relationship_id
248 and contact_type in ('EC','ES','PR1','PR2','OTH')
249 and P_CONTACT_TYPE in ('EC','ES','PR1','PR2','OTH')
250 and (date_start >= p_date_start
251 OR nvl(date_end,hr_general.end_of_time) <= nvl(p_date_end,hr_general.end_of_time));
252
253 cursor csr_dob_contact is
254 select date_of_birth from per_all_people_f where person_id in (select contact_person_id from
255 per_contact_relationships where contact_relationship_id = P_CONTACT_RELATIONSHIP_ID);
256
257 /* cursor csr_person_type is
258 select USER_PERSON_TYPE from per_person_types where PERSON_TYPE_ID = P_PERSON_TYPE_ID;*/
259
260 l_contact_dob per_all_people_f.date_of_birth%TYPE;
261 nDummy_val number;
262 l_valid_ni NUMBER DEFAULT 10;
263 --l_user_person_type varchar2(60);---per_person_type.user_person_type%Type;
264 l_proc varchar2(60);
265 BEGIN
266 l_proc:=g_package||'UPDATE_RO_CONTACT_REL';
267
268 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'RO') THEN
269 hr_utility.set_location('Leaving : '||l_proc,10);
270 return;
271 END IF;
272
273 if P_CONTACT_TYPE NOT IN ('EC','ES','PR1','PR2','OTH') THEN
274 hr_utility.set_message(800,'HR_RO_INV_CONTACT_TYPE');
275 hr_utility.raise_error;
276 End If;
277
278
279 open cur_crl;
280 fetch cur_crl into nDummy_val;
281 IF cur_crl%found then
282 Close cur_crl;
283 hr_utility.set_message(800,'HR_CON_REL_OVERLAP_PL');
284 hr_utility.raise_error;
285 End If;
286 Close cur_crl;
287
288 /* Date of birth is mandatory in case of Employee'Childrem contact */
289
290 if P_CONTACT_TYPE = 'EC' then
291 open csr_dob_contact;
292 fetch csr_dob_contact into l_contact_dob;
293 close csr_dob_contact;
294
295 if l_contact_dob is null then
296 hr_utility.set_message(800,'HR_CON_DOB_REQD_PL');
297 hr_utility.raise_error;
298 end if;
299
300 end if;
301
302 END UPDATE_RO_CONTACT_REL;
303
304 /*------------------------------------------------------------------------
305 -- UPDATE_RO_CONTACT_REL end
306 ------------------------------------------------------------------------ */
307 END HR_RO_CONTACT_REL_VAL;