1 PACKAGE BODY per_pl_update_person AS
2 /* $Header: peplperp.pkb 120.9.12010000.1 2008/07/28 05:21:29 appldev ship $ */
3
4 PROCEDURE update_pl_person (p_person_type_id number
5 ,p_last_name VARCHAR2
6 ,p_first_name VARCHAR2
7 ,p_date_of_birth DATE
8 ,p_marital_status VARCHAR2
9 ,p_nationality VARCHAR2
10 ,p_national_identifier VARCHAR2
11 ,p_per_information1 VARCHAR2
12 ,p_person_id number
13 ,p_effective_date date
14 ,p_per_information2 VARCHAR2
15 ,p_per_information3 VARCHAR2
16 ,p_per_information4 VARCHAR2
17 ,p_per_information5 VARCHAR2
18 ,p_per_information6 VARCHAR2
19 ,p_per_information7 VARCHAR2
20 ,p_per_information8 VARCHAR2) is
21
22 l_contact_type_key per_person_types.seeded_person_type_key%TYPE;
23 l_emp_type_key per_person_types.seeded_person_type_key%TYPE;
24 l_app_type_key per_person_types.seeded_person_type_key%TYPE;
25 l_cwk_type_key per_person_types.seeded_person_type_key%TYPE;
26 l_chk varchar2(1);
27 l_con varchar2(1);
28 l_app varchar2(1);
29 l_cwk varchar2(1);
30 l_proc varchar2(72);
31 per_business_group_id per_all_people_f.business_group_id%TYPE;
32 identifier_chk varchar2(1);
33 assg_payroll_id per_all_assignments_f.payroll_id%TYPE;
34
35 cursor csr_per_value is
36 select papf.business_group_id, papf.nationality, papf.national_identifier, papf.per_information1,
37 papf.per_information2,papf.per_information3,papf.per_information8
38 from per_all_people_f papf
39 where papf.person_id = p_person_id and
40 p_effective_date between papf.effective_start_date and papf.effective_end_date;
41
42 l_csr_per_value csr_per_value%rowtype;
43 cursor csr_person_type(p_person_type_key char) is
44 select 'Y' from per_person_types ppt, per_person_type_usages_f pptu
45 where ppt.person_type_id = pptu.person_type_id and
46 pptu.person_id = p_person_id and
47 ppt.business_group_id = per_business_group_id and
48 ppt.seeded_person_type_key = p_person_type_key and
49 p_effective_date between pptu.effective_start_date and pptu.effective_end_date;
50
51 cursor csr_payroll_id is
52 select payroll_id from per_all_assignments_f paaf where paaf.person_id = p_person_id and
53 p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
54
55
56 BEGIN
57
58 l_emp_type_key := 'EMPLOYEE'; -- This is the System Person Type Key for an Employee
59 l_contact_type_key := 'CONTACT'; -- This is the System Person Type Key for a Contact
60 l_app_type_key := 'APPLICANT'; -- This is the Seeded Person type for an Applicant
61 l_cwk_type_key := 'CWK'; -- This is the Seeded Person type for a Contingent
62 l_chk := NULL;
63 l_con := NULL;
64 l_app := NULL;
65 l_cwk := NULL;
66 per_business_group_id := NULL;
67 l_proc := 'PER_PL_UPDATE_PERSON.UPDATE_PL_PERSON';
68 /* Added for GSI Bug 5472781 */
69 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
70 hr_utility.set_location('Leaving : '||l_proc,10);
71 return;
72 END IF;
73
74 identifier_chk := NULL;
75 assg_payroll_id := NULL;
76
77 open csr_per_value;
78 fetch csr_per_value into l_csr_per_value;
79 close csr_per_value;
80
81 per_business_group_id:=l_csr_per_value.business_group_id;
82
86
83 OPEN csr_person_type(l_emp_type_key); /** Person Type 'Employee' ***/
84 FETCH csr_person_type into l_chk;
85 CLOSE csr_person_type;
87 OPEN csr_person_type(l_contact_type_key); /** Person Type 'Contact' ***/
88 FETCH csr_person_type into l_con;
89 CLOSE csr_person_type;
90
91
92 OPEN csr_person_type(l_app_type_key); /** Person Type 'Applicant ***/
93 FETCH csr_person_type into l_app;
94 CLOSE csr_person_type;
95
96
97 OPEN csr_person_type(l_cwk_type_key); /** Person Type 'Contingent' ***/
98 FETCH csr_person_type into l_cwk;
99 CLOSE csr_person_type;
100
101
102 hr_api.mandatory_arg_error
103 (p_api_name => l_proc,
104 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','LAST'),
105 p_argument_value => p_last_name
106 );
107
108 if l_chk = 'Y' then -- Person Type is an Employee
109
110 hr_api.mandatory_arg_error
111 (p_api_name => l_proc,
112 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','FIRST'),
113 p_argument_value => p_first_name
114 );
115
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc,
118 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','STATUS'),
119 p_argument_value => p_marital_status
120 );
121
122 hr_api.mandatory_arg_error
123 (p_api_name => l_proc,
124 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NAT'),
125 p_argument_value => p_nationality
126 );
127
128 hr_api.mandatory_arg_error
129 (p_api_name => l_proc,
130 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','BIRTH'),
131 p_argument_value => p_date_of_birth
132 );
133 /*legal employer mandatory for employee*/
134 hr_api.mandatory_arg_error
135 (p_api_name => l_proc,
136 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','LEGAL_EMPLOYER'),
137 p_argument_value => p_per_information7
138 );
139 --Citizenship Mandatory for Employee.
140 hr_api.mandatory_arg_error
141 (p_api_name => l_proc,
142 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CITIZENSHIP'),
143 p_argument_value => p_per_information8
144 );
145
146 --National Fund of Health Mandatory for Employee.
147 hr_api.mandatory_arg_error
148 (p_api_name => l_proc,
149 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NATIONAL_FUND_OF_HEALTH'),
150 p_argument_value => p_per_information5
151 );
152 --Modified the condition to check for PESEL and NIP.
153 --NIP and PESEL are mandatory if both Nationality and Citizenship are Polish.
154 --if p_nationality = 'PQH_PL' or (p_nationality = hr_api.g_varchar2 and l_csr_per_value.nationality = 'PQH_PL') then
155
156 if ((p_nationality = 'PQH_PL' or (p_nationality = hr_api.g_varchar2 and l_csr_per_value.nationality = 'PQH_PL'))
157 and
158 (p_per_information8 = 'PL' or (p_per_information8= hr_api.g_varchar2 and l_csr_per_value.per_information8 = 'PL'))
159 )then
160 if p_national_identifier is NULL or (p_national_identifier = hr_api.g_varchar2 and l_csr_per_value.national_identifier is null) then
161 /* Bug fix 4627784 add check fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') in ('ERROR','WARN') */
162 if fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') in ('ERROR','WARN') then
163 hr_api.mandatory_arg_error
164 (p_api_name => l_proc,
165 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PESEL'),
166 p_argument_value => identifier_chk
167 );
168 end if;
169 end if;
170 /* Commented by nprasath for Bug 6272487
171 if p_per_information1 is NULL or (p_per_information1 = hr_api.g_varchar2 and l_csr_per_value.per_information1 is null) then
172 hr_api.mandatory_arg_error
173 (p_api_name => l_proc,
174 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NIP'),
175 p_argument_value => identifier_chk --passing this value as it is bound to fail
176 );
177 end if; */
178 end if;--NIP/PESEL check
179
180 open csr_payroll_id;
181 fetch csr_payroll_id into assg_payroll_id;
182 close csr_payroll_id;
183
184 /* if assg_payroll_id is not NULL and p_per_information1 is NULL then
185 hr_utility.set_message(800,'HR_NIP_REQUIRED_PL');
186 hr_utility.raise_error;
187 end if;
188 Removing this check because according to latest requirement,
189 NIP is needed for an Payroll only if nationality and citizenship are both Polish.
190 If nationality and citizenship are Polish,we need to enter NIP by default.Hence no need for
191 this redundant check.
192
193 */
194
195 /*Phase2 4340576 Oldage pension rights is required to attach payroll to any assignment */
196 if assg_payroll_id is not NULL and p_per_information4 is null then
197 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
198 hr_utility.set_message_token ('TOKEN',hr_general.decode_lookup('PL_FORM_LABELS','OLDAGE_PENSION_RIGHTS')); --default translate false
199 hr_utility.raise_error;
200 end if;
201 /* Tax office is required to attach payroll to any assignment*/
202 if assg_payroll_id is not NULL and p_per_information6 is null then
203 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
204 hr_utility.set_message_token ('TOKEN',hr_general.decode_lookup('PL_FORM_LABELS','TAX_OFFICE')); --default translate false
205 hr_utility.raise_error;
206 end if;
207
208 elsif l_con = 'Y' then -- Person Type is Contact
209
210
211 if ( ( p_per_information2 = 'Y' or (p_per_information2 = hr_api.g_varchar2 and l_csr_per_value.per_information2 = 'Y'))
212 or ( p_per_information3 = 'Y' or (p_per_information3 = hr_api.g_varchar2 and l_csr_per_value.per_information3 = 'Y'))
213 ) then
214
215 -- Bug 4567534 : Replaced hr_api.mandatory_arg_error with an error message
216 if p_first_name is null then
217 hr_utility.set_message(800,'HR_375873_FIRST_NAME_REQD');
218 hr_utility.raise_error;
219 end if;
220
221 --Citizenship Mandatory for contact if either insured by or inheritor is yes.
222 hr_api.mandatory_arg_error
223 (p_api_name => l_proc,
224 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CITIZENSHIP'),
225 p_argument_value => p_per_information8
226 );
227
228 /*********************Validations surrounding Inheritor is Yes******************/
229 /* Commented by nprasath for Bug 6272487
230 if ( (p_per_information3 = 'Y' or (p_per_information3 = hr_api.g_varchar2 and l_csr_per_value.per_information3 = 'Y'))
231 and (p_nationality = 'PQH_PL' or (p_nationality = hr_api.g_varchar2 and l_csr_per_value.nationality= 'PQH_PL'))
232 and (p_per_information8 = 'PL' or (p_per_information8= hr_api.g_varchar2 and l_csr_per_value.per_information8 = 'PL'))
233 ) then
234
235 -- Bug 4567534 : Replaced hr_api.mandatory_arg_error with an error message
236 if p_per_information1 is null then
237 hr_utility.set_message(800,'HR_375874_NIP_REQD');
238 hr_utility.raise_error;
239 end if;
240
241 end if; */
242
243 /**********Validations surrounding Insured by employee is Yes******************/
244 if ( p_per_information2 = 'Y' or (p_per_information2 = hr_api.g_varchar2 and l_csr_per_value.per_information2 = 'Y')) then
245
246 hr_api.mandatory_arg_error
247 (p_api_name => l_proc,
248 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','BIRTH'),
249 p_argument_value => p_date_of_birth
250 );
251
252 if ((p_nationality = 'PQH_PL' or (p_nationality = hr_api.g_varchar2 and l_csr_per_value.nationality = 'PQH_PL'))
253 and (p_per_information8 = 'PL' or (p_per_information8= hr_api.g_varchar2 and l_csr_per_value.per_information8 = 'PL'))
254 )then
255 /* NIP/PESEL enhancement.If Insured by employee is yes and
256 nationality and citizenship are polish,NIP or PESEL needs to be entered.*/
257
258
259 if( ( p_per_information1 is null and p_national_identifier is null )
260 or ((p_per_information1 = hr_api.g_varchar2 and l_csr_per_value.per_information1 is null) --NIP is null
261 and (p_national_identifier = hr_api.g_varchar2 and l_csr_per_value.national_identifier is null) --PESEL is null
262 )
263 )then
264 hr_utility.set_message(800,'HR_375878_NIP_OR_PESEL');
265 hr_utility.raise_error;
266
267 end if; -- End if of Nationality and Citizenship is Polish
268 end if;--p_per_information8='PL' or p_nationality='PQH_PL'
269 end if;--Insured by Employee is yes
270
271 end if; --one of Insured by or Inheritor is yes.
272
273
274 /**End for Person type contact**/
275
276 elsif (l_cwk = 'Y' or l_app = 'Y') then
277
278 -- This is neither a Contact nor an Employee (like Contingent/Applicant)
279
280 hr_api.mandatory_arg_error
281 (p_api_name => l_proc,
282 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','FIRST'),
283 p_argument_value => p_first_name
284 );
285
286
287 end if; -- End if of Person Type in 'Employee/Contact'
288
289
290
291 if p_national_identifier is not null and p_national_identifier <> hr_api.g_varchar2 then
292 hr_pl_utility.per_pl_validate(p_national_identifier);
293 if p_per_information7 is not null then
294 hr_pl_utility.per_pl_check_ni_unique(p_national_identifier,p_person_id,per_business_group_id,p_per_information7);
295 end if;
296 end if;
297
298 if p_per_information1 is not null and p_per_information1 <> hr_api.g_varchar2 then
299 hr_pl_utility.per_pl_nip_validate(p_per_information1,p_person_id,per_business_group_id,p_per_information7,p_nationality,
300 p_per_information8);
301 end if;
302
303
304 END update_pl_person;
305
306 END PER_PL_UPDATE_PERSON;
[Home] [Help]