1 package body PER_PL_ASSIGNMENT as
2 /* $Header: peplasgp.pkb 120.17 2012/01/19 09:22:40 rpahune noship $ */
3
4 g_package_name varchar2(18);
5
6 cursor csr_oldage_taxoffice_check(r_person_id in number,r_date date) is
7 select per_information4,per_information6
8 from per_all_people_f
9 where person_id=r_person_id
10 and r_date between effective_start_date and effective_end_date ;
11
12 cursor csr_check_sii_exists(r_join_variable in number,r_date date) is
13 select '1'
14 from pay_pl_sii_details_f
15 where per_or_asg_id=r_join_variable
16 and r_date between effective_start_date and effective_end_date;
17
18 cursor csr_check_paye_exists(r_join_variable in number,r_date date) is
19 select '1'
20 from pay_pl_paye_details_f
21 where per_or_asg_id=r_join_variable
22 and r_date between effective_start_date and effective_end_date;
23
24 cursor csr_assgt_type(r_assignment_id number,r_date date) is
25 select per_system_status
26 from per_assignment_status_types paat , per_all_assignments_f paaf
27 where paat.assignment_status_type_id=paaf.assignment_status_type_id
28 and assignment_id=r_assignment_id
29 and r_date between effective_start_date and effective_end_date;
30 function get_person_id(l_assignment_id number,l_date date) return number is
31 cursor csr_person_id is
32 select person_id
33 from per_all_assignments_f
34 where assignment_id=l_assignment_id
35 and l_date between effective_start_date and effective_end_date;
36 l_personid number;
37 begin
38 --effective date reduces the number of rows returned by cursor..thats why it has been put up.
39 open csr_person_id;
40 fetch csr_person_id into l_personid;
41 close csr_person_id;
42
43 return l_personid;
44 Exception
45 When others then
46 hr_utility.set_location(g_package_name||'get_person_id',10);
47 hr_utility.raise_error;
48 end get_person_id;
49
50
51 PROCEDURE create_pl_secondary_emp_asg
52 ( p_person_id number
53 ,p_payroll_id number
54 ,p_effective_date date
55 ,p_scl_segment3 varchar2
56 ,p_scl_segment4 varchar2
57 ,p_scl_segment5 varchar2
58 ,p_scl_segment6 varchar2
59 ,p_scl_segment7 varchar2
60 ,p_scl_segment8 varchar2
61 ,p_scl_segment9 varchar2
62 ,p_scl_segment11 varchar2
63 ,p_scl_segment12 varchar2
64 ,p_scl_segment13 varchar2
65 ,p_scl_segment14 varchar2
66 ,p_scl_segment15 varchar2
67 ,p_scl_segment16 varchar2
68 ,p_notice_period number
69 ,P_NOTICE_PERIOD_UOM VARCHAR2
70 ,p_employment_category varchar2
71 ) is
72
73 l_proc varchar2(45);
74 l_oldage_pension_rights per_all_people_f.per_information4%TYPE;
75 l_tax_office per_all_people_f.per_information6%TYPE;
76 l_one number(1);
77 l_join_variable number(10);
78
79 begin
80 /*
81 segment3 Contract Category
82 segment4 Contract Type
83 segment5 Contract Number
84 segment6 Change of contract Reason --no check
85 segment7 Job --no check
86 segment8 Work in special Condition --no check
87 segment9 End of Contract Addl Details --no check
88 segment11 Contract Start Date
89 segment12 Planned Valid To date
90 segment13 Contract Type Change Date
91 segment14 Date Contract Signed
92 segment15 Notice Period Date
93 segment16 Notice Period End Date
94 --1)Mandatory argument checks
95 --2)Conditionally mandatory arguments ...planned valid to date,notice_period,notice_period_date(scl_segment15),notice_period_end_date(scl_segment16),employment_category
96 --3)contract number cannot be longer than 30 characters
97 --4)Date checks
98 --5)payroll check ...check for oldage pension rights and nip.....if civil contract..error out if payroll is not null ...if normal contract then chack for sii and paye details....
99 */
100
101 g_package_name :='PER_PL_ASSIGNMENT.';
102 l_proc := g_package_name||'CREATE_PL_SECONDARY_EMP_ASG';
103 /* Added for GSI Bug 5472781 */
104 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
105 hr_utility.set_location('Leaving : '||l_proc,10);
106 return;
107 END IF;
108 hr_utility.set_location(l_proc,10);
109
110
111 hr_api.mandatory_arg_error --Contract Category is mandatory
112 (p_api_name => l_proc,
113 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_CATEGORY'),
114 p_argument_value => p_scl_segment3
115 );
116
117 hr_api.mandatory_arg_error --Contract Type is Mandatory
118 (p_api_name => l_proc,
119 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE'),
120 p_argument_value => p_scl_segment4
121 );
122
123
124 hr_api.mandatory_arg_error --Contract Number is Mandatory
125 (p_api_name => l_proc,
126 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_NUMBER'),
127 p_argument_value => p_scl_segment5
128 );
129
130 hr_api.mandatory_arg_error --Contract Start Date is mandatory
131 (p_api_name => l_proc,
132 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'),
133 p_argument_value => p_scl_segment11
134 );
135
136 hr_api.mandatory_arg_error --Date Contract Signed is mandatory
137 (p_api_name => l_proc,
138 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'),
139 p_argument_value => p_scl_segment14
140 );
141
142 hr_utility.set_location(l_proc,20);
143 ------Conditionally mandatory---------
144 --------for a normal contract,the assignment category is mandatory...
145 -------core store this in employment_category in per_all_assignments_f table..displays it as assignment_category on UI
146 if p_scl_segment3='NORMAL' then
147 hr_api.mandatory_arg_error
148 (p_api_name => l_proc,
149 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_CATEGORY'),
150 p_argument_value => P_EMPLOYMENT_CATEGORY
151 );
152 end if;
153
154
155 if p_scl_segment3='NORMAL' and p_scl_segment4<>'N01' then
156 hr_api.mandatory_arg_error
157 (p_api_name => l_proc,
158 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PLANNED_VALID_TO_DATE'),
159 p_argument_value => P_SCL_SEGMENT12
160 );
161 elsif p_scl_segment12 is not null then
162 hr_utility.set_message(800,'HR_375869_PLANNED_DATE_INVALID');
163 hr_utility.raise_error;
164 end if;
165
166 -----if any of the following 3 has been entered then ..other two become mandatory
167 -----Notice Period Date(p_scl_segment15),Notice Period End Date(p_scl_segment16),Notice Period(p_notice_period)
168 if p_scl_segment16||p_scl_segment15||p_notice_period is not null then
169 hr_api.mandatory_arg_error
170 (p_api_name => l_proc,
171 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_END_DATE'),
172 p_argument_value => p_scl_segment16
173 );
174 hr_api.mandatory_arg_error
175 (p_api_name => l_proc,
176 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'),
177 p_argument_value => p_scl_segment15
178 );
179 hr_api.mandatory_arg_error
180 (p_api_name => l_proc,
181 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD'),
182 p_argument_value => p_notice_period
183 );
184 if ( (p_NOTICE_PERIOD_UOM like '_1' and p_notice_period >1) or
185 (p_NOTICE_PERIOD_UOM like '_' and p_notice_period =1)) then
186 hr_utility.set_message(800,'HR_375856_NOTICE_UNIT_MISMATCH');
187 hr_utility.raise_error;
188 end if;
189 end if;
190 -----if p_notice_period is entered and notice_period_uom is null??
191 -----taken care by core per_asg_bus3.chk_notice_period_uom
192
193
194 --Bug 4504375
195 -- change of contract reason and contract type change date are mandatory
196 -- if any one of them is not null
197 if p_scl_segment6 is not null and p_scl_segment13 is null then
198 hr_utility.set_message(800,'HR_375835_ENTER_OTHER_VALUE');
199 hr_utility.set_message_token(l_token_name=>'DETAIL1',
200 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CHANGE_OF_CONTRACT_REASON')));
201 hr_utility.set_message_token(l_token_name=>'DETAIL2',
202 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE')));
203
204 hr_utility.raise_error;
205 elsif p_scl_segment6 is null and p_scl_segment13 is not null then
206 hr_utility.set_message(800,'HR_375835_ENTER_OTHER_VALUE');
207 hr_utility.set_message_token(l_token_name=>'DETAIL1',
208 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE')));
209
210 hr_utility.set_message_token(l_token_name=>'DETAIL2',
211 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CHANGE_OF_CONTRACT_REASON')));
212 hr_utility.raise_error;
213 end if;
214
215 -----other validations like value set comparison will be taken
216 -----care by core when they validate the flexfields.
217 -----what we need to check are the dates being entered.
218 hr_utility.set_location(l_proc,30);
219 if fnd_date.canonical_to_date(p_scl_segment11) < p_effective_date then
220 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
221 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
222 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_START_DATE'));
223 hr_utility.raise_error;
224
225 elsif fnd_date.canonical_to_date(p_scl_segment12) < fnd_date.canonical_to_date(p_scl_segment11) then
226 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
227 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PLANNED_VALID_TO_DATE'));
228 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
229 hr_utility.raise_error;
230
231 elsif fnd_date.canonical_to_date(p_scl_segment13) < fnd_date.canonical_to_date(p_scl_segment11) then
232 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
233 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE'));
234 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
235 hr_utility.raise_error;
236
237 /* Bug4504375 :Contract type Change Date should not be before Date Contract Signed.*/
238 elsif fnd_date.canonical_to_date(p_scl_segment13) < fnd_date.canonical_to_date(p_scl_segment14) then
239 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
240 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE'));
241 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'));
242 hr_utility.raise_error;
243
244
245 /*
246 This check has been removed because of bug 4504312 DATE CONTRACT SIGNED1 ACCEPTS DATES EVEN AFTER THE CONTRACT START DATE
247 Hence Date Contract Signed must be before or same as Contract Start Date. ie)(p_scl_segment14)<(p_scl_segment11)is the condition
248 to be held correct.Otherwise a note message is to be thrown.Since note messages cannot be thrown from api's there wont be any check for
249 Date contract Signed.
250 elsif fnd_date.canonical_to_date(p_scl_segment14) < fnd_date.canonical_to_date(p_scl_segment11) then
251 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
252 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'));
253 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
254 hr_utility.raise_error;
255 */
256
257 elsif fnd_date.canonical_to_date(p_scl_segment15) < fnd_date.canonical_to_date(p_scl_segment11) then
258 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
259 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'));
260 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
261 hr_utility.raise_error;
262 elsif fnd_date.canonical_to_date(p_scl_segment16) < fnd_date.canonical_to_date(p_scl_segment15) then
263 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
264 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_END_DATE'));
265 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'));
266 hr_utility.raise_error;
267 elsif p_payroll_id is not null then
268
269 l_oldage_pension_rights:=null;
270
271 open csr_oldage_taxoffice_check(p_person_id,p_effective_date);
272 fetch csr_oldage_taxoffice_check into l_oldage_pension_rights,l_tax_office ;
273 close csr_oldage_taxoffice_check;
274
275 /* NIP is neccessary to attach a payroll only for Polish employees(Both Citizenship and nationality)
276 But this is redundant as For Polish Employee these are mandatory
277 if l_nip is null then
278 hr_utility.set_message(800,'HR_NIP_REQUIRED_PL');
279 hr_utility.raise_error;
280 end if; */
281
282 if l_oldage_pension_rights is null then
283 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
284 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','OLDAGE_PENSION_RIGHTS')); --default translate false
285 hr_utility.raise_error;
286 end if;
287
288 if l_tax_office is null then
289 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
290 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','TAX_OFFICE')); --default translate false
291 hr_utility.raise_error;
292 end if;
293
294 -- Bug 9534572
295 -- As we do not deliver Oracle Payroll functionality specific to Polish legislation,
296 -- hence SII and Tax Calculations do not come into picture for Polish customers.
297 -- Hence removing any dependency on SII/Tax Card before attaching payroll
298 -- to the assignment
299 /*
300 --l_one:=0;
301
302 --if p_scl_segment3 in ('CIVIL','F_LUMP','LUMP') then
303 --we cannot have a civil contract with payroll id while creating...
304 --this is bcoz .user in no way that we can have a tax or sii record ..as they need assignment id to have sii record...
305 -- hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
306 -- hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_SII_INFO_FLEX'));
307 -- hr_utility.raise_error;
308 --end if;
309
310
311 --open csr_check_sii_exists(p_person_id,p_effective_date) ;
312 --fetch csr_check_sii_exists into l_one;
313 --close csr_check_sii_exists;
314
315 --if l_one <> 1 then
316 -- hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
317 -- hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_SII_INFO_FLEX'));
318 -- hr_utility.raise_error;
319 --end if;
320
321 --l_one:=0;
322
323 --open csr_check_paye_exists(p_person_id,p_effective_date);
324 --fetch csr_check_paye_exists into l_one;
325 --close csr_check_paye_exists;
326
327 --if l_one<> 1 then
328 -- hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
329 -- hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_TAX_INFO_FLEX'));
330 -- hr_utility.raise_error;
331 --end if;
332 */
333
334
335 end if; --date checks over
336
337 hr_utility.set_location(l_proc,40);
338 Exception
339 when others then
340 hr_utility.set_location(l_proc,50);
341 hr_utility.raise_error;
342 end create_pl_secondary_emp_asg;
343
344
345 ---------start of update_pl_emp_asg-----------
346
347 procedure update_pl_emp_asg
348 (P_EFFECTIVE_DATE DATE
349 ,P_ASSIGNMENT_ID NUMBER
350 ,P_ASSIGNMENT_STATUS_TYPE_ID NUMBER
351 ,P_SEGMENT3 VARCHAR2
352 ,P_SEGMENT4 VARCHAR2
353 ,P_SEGMENT5 VARCHAR2
354 ,P_SEGMENT6 VARCHAR2
355 ,P_SEGMENT7 VARCHAR2
356 ,P_SEGMENT8 VARCHAR2
357 ,P_SEGMENT9 VARCHAR2
358 ,P_SEGMENT11 VARCHAR2
359 ,P_SEGMENT12 VARCHAR2
360 ,P_SEGMENT13 VARCHAR2
361 ,P_SEGMENT14 VARCHAR2
362 ,P_SEGMENT15 VARCHAR2
363 ,P_SEGMENT16 VARCHAR2
364 ,P_NOTICE_PERIOD NUMBER
365 ,P_NOTICE_PERIOD_UOM VARCHAR2
366 )is
367
368 cursor csr_get_payroll_emp_cat is
369 select payroll_id,employment_category
370 from per_all_assignments_f
371 where assignment_id=P_ASSIGNMENT_ID
372 and P_EFFECTIVE_DATE between effective_start_date and effective_end_date;
373
374 cursor csr_get_contract_details(r_date date) is
375 select kyflx.segment3,kyflx.segment4,kyflx.segment6,fnd_date.canonical_to_date(segment13)
376 from hr_soft_coding_keyflex kyflx , per_all_assignments_f paaf
377 where paaf.assignment_id = P_ASSIGNMENT_ID
378 and paaf.soft_coding_keyflex_id= kyflx.soft_coding_keyflex_id
379 and r_date between effective_start_date and effective_end_date;
380
381 -- Bug 7041296
382 cursor csr_effective_start_date is
383 select min(effective_start_date)
384 from per_all_assignments_f
385 where assignment_id=P_ASSIGNMENT_ID;
386
387 -- Added for Bug 7510498
388 -- Modified for Bug 7554037
389 cursor csr_get_segment_details(r_date date) is
390 select kyflx.segment3,kyflx.segment4,kyflx.segment5,kyflx.segment11,kyflx.segment12,kyflx.segment13,
391 kyflx.segment14,kyflx.segment15,kyflx.segment16
392 from hr_soft_coding_keyflex kyflx , per_all_assignments_f paaf
393 where paaf.assignment_id = P_ASSIGNMENT_ID
394 and paaf.soft_coding_keyflex_id= kyflx.soft_coding_keyflex_id
395 and r_date between effective_start_date and effective_end_date;
396
397 -- Added for Bug 7554037
398 CURSOR csr_get_notice_details is
399 SELECT NOTICE_PERIOD, NOTICE_PERIOD_UOM
400 FROM per_all_assignments_f
401 WHERE assignment_id=P_ASSIGNMENT_ID
402 and P_EFFECTIVE_DATE between effective_start_date and effective_end_date;
403
404 l_employment_category per_all_assignments_f.employment_category%type;
405 l_proc varchar2(35);
406 l_assgt_type varchar2(30);
407 l_prev_assgt_type varchar2(30);
408 l_oldage_pension_rights per_all_people_f.per_information4%TYPE;
409 l_tax_office per_all_people_f.per_information6%TYPE;
410 l_one number(1);
411 l_join_variable number(10);
412 l_payroll_id number(10);
413 l_contract_category hr_soft_coding_keyflex.segment3%type;
414 l_contract_type hr_soft_coding_keyflex.segment4%type;
415 l_contract_change_reason hr_soft_coding_keyflex.segment6%type;
416 l_contract_type_change_date date;
417 l_person_id number(10) ;
418 l_asg_min_start_date date ;
419 -- Added for Bug 7510498
420 l_segment3 hr_soft_coding_keyflex.segment3%type;
421 l_segment4 hr_soft_coding_keyflex.segment4%type;
422 l_segment5 hr_soft_coding_keyflex.segment5%type;
423 l_segment11 hr_soft_coding_keyflex.segment11%type;
424 l_segment12 hr_soft_coding_keyflex.segment12%type;
425 l_segment13 hr_soft_coding_keyflex.segment13%type;
426 l_segment14 hr_soft_coding_keyflex.segment14%type;
427 l_segment15 hr_soft_coding_keyflex.segment15%type;
428 l_segment16 hr_soft_coding_keyflex.segment16%type;
429 l_notice_concat varchar2(180);
430 l_notice_period per_all_assignments_f.notice_period%type;
431 l_notice_uom per_all_assignments_f.notice_period_uom%type;
432 begin
433 --1)Mandatory argument checks
434 --2)Conditionally mandatory arguments ...planned valid to date,notice_period,notice_period_date(scl_segment15),notice_period_end_date(scl_segment16)
435 --3)you cannot correct or update segment3 once enetred
436 --4)in order to update the contract type(segment4),there must be value for change of contract Reason(segment6)
437 -- and Contract type change date must be equal to p_effective_date -1
438 --5)contract number cannot be longer than 30 characters
439 --6)Date checks
440 --7)payroll check ..get it from the table and then do the validation
441 g_package_name :='PER_PL_ASSIGNMENT.';
442 l_proc:=g_package_name||'UPDATE_PL_EMP_ASG';
443 /* Added for GSI Bug 5472781 */
444 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
445 hr_utility.set_location('Leaving : '||l_proc,10);
446 return;
447 END IF;
448 hr_utility.set_location(l_proc,10);
449 l_person_id:= get_person_id(p_assignment_id,p_effective_date);
450
451 --Added for Bug 7510498
452 --Modified for Bug 7554037
453 open csr_get_segment_details(p_effective_date);
454 fetch csr_get_segment_details into
455 l_segment3,l_segment4,l_segment5,l_segment11,l_segment12,l_segment13,l_segment14,l_segment15,l_segment16;
456 close csr_get_segment_details;
457
458 /*If the mandatory arguments are not specified in the call, then it will be defaulted
459 to hr_api.g_varchar2. Added a check that when the mandatory arguments are not specified,
460 check whether these details are already present for the assignment. If not, raise
461 error --Added for Bug 7554037*/
462
463 IF (p_segment3 is null OR p_segment3 <> hr_api.g_varchar2) then
464 l_segment3:=p_segment3;
465 END if;
466 IF (p_segment4 is null OR p_segment4 <> hr_api.g_varchar2) then
467 l_segment4:=p_segment4;
468 END if;
469 IF (p_segment5 is null OR p_segment5 <> hr_api.g_varchar2) then
470 l_segment5:=p_segment5;
471 END if;
472 IF (p_segment11 is null OR p_segment11 <> hr_api.g_varchar2) then
473 l_segment11:=p_segment11;
474 END if;
475 if (p_segment12 is null OR p_segment12 <> hr_api.g_varchar2) then
476 l_segment12:=p_segment12;
477 END if;
478 if (p_segment13 is null OR p_segment13 <> hr_api.g_varchar2) then
479 l_segment13:=p_segment13;
480 END if;
481 IF (p_segment14 is null OR p_segment14 <> hr_api.g_varchar2) then
482 l_segment14:=p_segment14;
483 END if;
484 IF (p_segment15 is null OR p_segment15 <> hr_api.g_varchar2) then
485 l_segment15:=p_segment15;
486 END if;
487 IF (p_segment16 is null OR p_segment16 <> hr_api.g_varchar2) then
488 l_segment16:=p_segment16;
489 END if;
490
491
492 -- Bug#11888146
493 -- This 'if' clause avoids checking for the various
494 -- mandatory parameters during the global deployment
495 -- process for an employee.
496
497
498 if per_per_bus.g_global_transfer_in_process IS NULL
499 OR per_per_bus.g_global_transfer_in_process = FALSE then
500
501 -- Bug#11888146
502
503
504 hr_api.mandatory_arg_error --Contract Category is mandatory
505 (p_api_name => l_proc,
506 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_CATEGORY'),
507 p_argument_value => l_segment3
508 );
509
510 hr_api.mandatory_arg_error --Contract Type is Mandatory
511 (p_api_name => l_proc,
512 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE'),
513 p_argument_value => l_segment4
514 );
515
516 hr_api.mandatory_arg_error --Contract Number is Mandatory
517 (p_api_name => l_proc,
518 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_NUMBER'),
519 p_argument_value => l_segment5
520 );
521
522 hr_api.mandatory_arg_error --Contract Start Date is mandatory
523 (p_api_name => l_proc,
524 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'),
525 p_argument_value => l_segment11
526 );
527
528 hr_api.mandatory_arg_error --Date Contract Signed is mandatory
529 (p_api_name => l_proc,
530 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'),
531 p_argument_value => l_segment14
532 );
533
534
535 hr_utility.set_location(l_proc,20);
536
537 ------Conditionally mandatory---------
538 /*
539 1)update_emp_asg_criteria will be called first..this is where the payroll id,employment_category will be set
540 2)since these values are not available here...we have to query for the changes....
541 3) do not use per_asg_shd.g_old_rec.employment_category or payroll_id
542 */
543 if p_segment3='NORMAL' then
544 l_employment_category:=null;
545 open csr_get_payroll_emp_cat;
546 fetch csr_get_payroll_emp_cat into l_payroll_id,l_employment_category;
547 close csr_get_payroll_emp_cat;
548 hr_api.mandatory_arg_error
549 (p_api_name => l_proc,
550 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_CATEGORY'),
551 p_argument_value => l_employment_category
552 );
553 end if;
554
555 if l_segment3='NORMAL' and l_segment4<>'N01' then
556 hr_api.mandatory_arg_error
557 (p_api_name => l_proc,
558 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PLANNED_VALID_TO_DATE'),
559 p_argument_value => l_SEGMENT12
560 );
561 elsif p_segment12<>hr_api.g_varchar2 then --replaced in 115.11
562 --Bug 5386451
563 hr_utility.set_message(800,'HR_375869_PLANNED_DATE_INVALID');
564 hr_utility.raise_error;
565 end if;
566
567 --Added for Bug 7554037
568 OPEN csr_get_notice_details;
569 FETCH csr_get_notice_details INTO l_notice_period,l_notice_uom;
570 CLOSE csr_get_notice_details;
571
572 SELECT decode(p_segment16,hr_api.g_varchar2,l_segment16,p_segment16)||
573 decode(p_segment15,hr_api.g_varchar2,l_segment15,p_segment15)||
574 decode(p_notice_period,hr_api.g_number,l_notice_period,p_notice_period)
575 INTO l_notice_concat FROM dual;
576
577 IF (p_notice_period is null OR p_notice_period <> hr_api.g_number) then
578 l_notice_period:=p_notice_period;
579 END if;
580 IF (p_NOTICE_PERIOD_UOM is null OR p_NOTICE_PERIOD_UOM <> hr_api.g_varchar2) then
581 l_notice_uom :=p_NOTICE_PERIOD_UOM;
582 END if;
583
584 -----if any of the following 3 has been entered then ..other two become mandatory
585 -----Notice Period Date(p_scl_segment15),Notice Period End Date(p_scl_segment16),Notice Period(p_notice_period)
586 if l_notice_concat is not null THEN --Modified for Bug 7554037
587 hr_api.mandatory_arg_error
588 (p_api_name => l_proc,
589 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_END_DATE'),
590 p_argument_value => l_segment16
591 );
592
593 hr_api.mandatory_arg_error
594 (p_api_name => l_proc,
595 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'),
596 p_argument_value => l_segment15
597 );
598
599 hr_api.mandatory_arg_error
600 (p_api_name => l_proc,
601 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD'),
602 p_argument_value => l_notice_period
603 );
604
605 --If notice_period is passed, p_NOTICE_PERIOD_UOM is mandatory
606 hr_api.mandatory_arg_error
607 (p_api_name => l_proc,
608 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_UNIT'),
609 p_argument_value => l_notice_uom
610 );
611
612 if ( (l_notice_uom like '_1' and l_notice_period >1) or
613 (l_notice_uom like '_' and l_notice_period =1)) then
614 hr_utility.set_message(800,'HR_375856_NOTICE_UNIT_MISMATCH');
615 hr_utility.raise_error;
616 end if;
617 end if;
618
619 end if ; -- Bug#11888146
620
621
622 -----if p_notice_period is entered and notice_period_uom is null??
623 -----taken care by core per_asg_bus3.chk_notice_period_uom
624
625 --Bug 4504375
626 -- change of contract reason and contract type change date are mandatory
627 -- if any one of them is not null
628 if p_segment6 is not null and p_segment13 is null then
629 hr_utility.set_message(800,'HR_375835_ENTER_OTHER_VALUE');
630 hr_utility.set_message_token(l_token_name=>'DETAIL1',
631 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CHANGE_OF_CONTRACT_REASON')));
632 hr_utility.set_message_token(l_token_name=>'DETAIL2',
633 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE')));
634
635 hr_utility.raise_error;
636 elsif p_segment6 is null and p_segment13 is not null then
637 hr_utility.set_message(800,'HR_375835_ENTER_OTHER_VALUE');
638 hr_utility.set_message_token(l_token_name=>'DETAIL1',
639 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE')));
640
641 hr_utility.set_message_token(l_token_name=>'DETAIL2',
642 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CHANGE_OF_CONTRACT_REASON')));
643 hr_utility.raise_error;
644 end if;
645
646
647
648 --what was the contract category yesterday?
649 open csr_get_contract_details(p_effective_date); --Modified for bug 7554037 -No need of checking these details
650 --as of effective_date-1 as if it is the assignment_start_date, no rows will be returned and null value is placed in variables
651 fetch csr_get_contract_details into l_contract_category,l_contract_type,l_contract_change_reason,l_contract_type_change_date;
652 close csr_get_contract_details;
653 --no need to check for null ...
654
655 --contract category cannot be changed once created
656 if (l_contract_category <> p_segment3) AND (p_segment3 <> hr_api.g_varchar2) then --changed for Bug 7510498
657 hr_utility.set_message(800,'HR_375868_DONT_CHANGE_CATEGORY');
658 hr_utility.raise_error;
659 end if;
660
661 --contract type change allowed only after reason and change date are provided
662 --p_contract_type cannot be null...no check if user is updating it from null to some contract type
663 if (l_contract_type <> p_segment4) AND (p_segment4 <> hr_api.g_varchar2) THEN --changed for Bug 7510498
664 if(l_contract_change_reason is null or (p_effective_date-1)<>nvl(l_contract_type_change_date,p_effective_date)) then
665 hr_utility.set_message(800,'HR_375867_DISALLOW_TYPE_CHANGE');
666 hr_utility.raise_error;
667 end if;
668 end if;
669
670
671 --contract length to be within 30 characters
672 if length(p_segment5)>30 AND (p_segment5 <> hr_api.g_varchar2) THEN --changed for Bug 7510498
673 hr_utility.set_message(800,'HR_375863_CONTRACT_NUM_LENGTH');
674 hr_utility.set_message_token(l_token_name=>'TYPE',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_NUMBER'));
675 hr_utility.set_message_token(l_token_name=>'LENGTH',l_token_value=>'30');
676 hr_utility.raise_error;
677 end if;
678
679 -----other validations like value set comparison will be taken
680 ----care by core when they validate the flexfields.
681 -----what we need to check are the dates being entered.
682
683 hr_utility.set_location(l_proc,30);
684
685 -- Bug 7041296
686 -- pick minimum start date of assignment
687 -- and compare contract_start_date with minimum(assignment eff_start_date)
688 open csr_effective_start_date;
689 fetch csr_effective_start_date into l_asg_min_start_date;
690 close csr_effective_start_date;
691
692
693 --hr_utility.trace('Bug 7041296 : p_segment11 :'||p_segment11||'l_asg_min_start_date:'||l_asg_min_start_date);
694 --Changed for Bug 7510498
695 if fnd_date.canonical_to_date(l_segment11) < l_asg_min_start_date THEN --Bug 7041296 p_effective_date then
696 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
697 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
698 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_START_DATE'));
699 hr_utility.raise_error;
700
701 elsif fnd_date.canonical_to_date(l_segment12) < fnd_date.canonical_to_date(l_segment11) then
702 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
703 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','PLANNED_VALID_TO_DATE'));
704 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
705 hr_utility.raise_error;
706
707 ELSIF fnd_date.canonical_to_date(l_segment13) < fnd_date.canonical_to_date(l_segment11) then
708 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
709 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE'));
710 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
711 hr_utility.raise_error;
712
713 /* Bug4504375 :Contract type Change Date should not be before Date Contract Signed.*/
714 elsif fnd_date.canonical_to_date(l_segment13) < fnd_date.canonical_to_date(l_segment14) then
715 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
716 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE'));
717 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'));
718 hr_utility.raise_error;
719
720 /*
721 This check has been removed because of bug 4504312 DATE CONTRACT SIGNE D ACCEPTS DATES EVEN AFTER THE CONTRACT START DATE
722 Hence Date Contract Signed must be before or same as Contract Start Date. ie)(p_scl_segment14)<(p_scl_segment11)is the condition
723 to be held correct.Otherwise a note message is to be thrown.Since note messages cannot be thrown from api's there wont be any check for
724 Date contract Signed.
725 elsif fnd_date.canonical_to_date(p_segment14) < fnd_date.canonical_to_date(p_segment11) then
726 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
727 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'));
728 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
729 hr_utility.raise_error;
730 */
731
732 elsif fnd_date.canonical_to_date(l_segment15) < fnd_date.canonical_to_date(l_segment11) then
733 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
734 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'));
735 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
736 hr_utility.raise_error;
737 elsif fnd_date.canonical_to_date(l_segment16) < fnd_date.canonical_to_date(l_segment15) then
738 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
739 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_END_DATE'));
740 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'));
741 hr_utility.raise_error;
742 else
743
744 --if status has been changed ..then create sii/paye record from existing tax record if they already exist
745 open csr_assgt_type(p_assignment_id,p_effective_date-1);
746 fetch csr_assgt_type into l_prev_assgt_type;
747 close csr_assgt_type;
748
749 open csr_assgt_type(p_assignment_id,p_effective_date);
750 fetch csr_assgt_type into l_assgt_type;
751 close csr_assgt_type;
752
753 if (p_segment3='NORMAL' and l_assgt_type='TERM_ASSIGN' and l_prev_assgt_type<>'TERM_ASSIGN') then
754 --check if record already exists with contract category as term_normal?
755
756 declare --declare 1
757 l_sii_already char(1):='0';
758 l_object_version_number pay_pl_sii_details_f.object_version_number%type;
759 l_sii_details_id pay_pl_sii_details_f.sii_details_id%type;
760 l_effective_start_date pay_pl_sii_details_f.effective_start_date%type;
761 l_effective_end_date pay_pl_sii_details_f.effective_end_date%type;
762 l_effective_date_warning boolean;
763 l_person_id number(10);
764 l_business_group_id number(10);
765
766 cursor csr_get_person_bus_id is
767 select person_id,business_group_id
768 from per_all_assignments_f
769 where assignment_id=p_assignment_id
770 and p_effective_date between effective_start_date and effective_end_date;
771
772 cursor csr_sii_already_exists is
773 select '1' from pay_pl_sii_details_f
774 where contract_category='TERM_NORMAL'
775 and p_effective_date between effective_start_date and effective_end_date
776 and per_or_asg_id=p_assignment_id;
777
778 cursor csr_sii_details(r_person_id number) is
779 select emp_social_security_info,old_age_contribution,pension_contribution,sickness_contribution,
780 work_injury_contribution,labor_contribution,health_contribution,unemployment_contribution,
781 old_age_cont_end_reason,pension_cont_end_reason,sickness_cont_end_reason,
782 work_injury_cont_end_reason,labor_fund_cont_end_reason,health_cont_end_reason,unemployment_cont_end_reason
783 from pay_pl_sii_details_f
784 where per_or_asg_id=r_person_id
785 and contract_category='NORMAL'
786 and p_effective_date between effective_start_date and effective_end_date;
787 l_csr_sii_details csr_sii_details%rowtype;
788
789 begin
790
791 open csr_sii_already_exists ;
792 fetch csr_sii_already_exists into l_sii_already;
793 close csr_sii_already_exists;
794 if l_sii_already='1' then
795 null;
796 else
797 open csr_get_person_bus_id ;
798 fetch csr_get_person_bus_id into l_person_id,l_business_group_id;
799 close csr_get_person_bus_id ;
800
801 open csr_sii_details(l_person_id);
802 fetch csr_sii_details into l_csr_sii_details;
803 if csr_sii_details%FOUND then
804 pay_pl_sii_api.create_pl_sii_details
805 (p_validate =>false
806 ,p_effective_date =>p_effective_date
807 ,p_contract_category =>'TERM_NORMAL'
808 ,p_per_or_asg_id =>p_assignment_id
809 ,p_business_group_id =>l_business_group_id
810 ,p_emp_social_security_info =>l_csr_sii_details.emp_social_security_info
811 ,p_old_age_contribution =>l_csr_sii_details.old_age_contribution
812 ,p_pension_contribution =>l_csr_sii_details.pension_contribution
813 ,p_sickness_contribution =>l_csr_sii_details.sickness_contribution
814 ,p_work_injury_contribution =>l_csr_sii_details.work_injury_contribution
815 ,p_labor_contribution =>l_csr_sii_details.labor_contribution
816 ,p_health_contribution =>l_csr_sii_details.health_contribution
817 ,p_unemployment_contribution =>l_csr_sii_details.unemployment_contribution
818 ,p_old_age_cont_end_reason =>l_csr_sii_details.old_age_cont_end_reason
819 ,p_pension_cont_end_reason =>l_csr_sii_details.pension_cont_end_reason
820 ,p_sickness_cont_end_reason =>l_csr_sii_details.sickness_cont_end_reason
821 ,p_work_injury_cont_end_reason =>l_csr_sii_details.work_injury_cont_end_reason
822 ,p_labor_fund_cont_end_reason =>l_csr_sii_details.labor_fund_cont_end_reason
823 ,p_health_cont_end_reason =>l_csr_sii_details.health_cont_end_reason
824 ,p_unemployment_cont_end_reason =>l_csr_sii_details.unemployment_cont_end_reason
825 ,p_sii_details_id =>l_sii_details_id
826 ,p_object_version_number =>l_object_version_number
827 ,p_effective_start_date =>l_effective_start_date
828 ,p_effective_end_date =>l_effective_end_date
829 ,p_effective_date_warning =>l_effective_date_warning);
830 end if;--csr_sii_details found
831 close csr_sii_details;
832 end if; --is sii_alreasdy=1?
833 end ; --of declare1
834
835
836 declare --declare2
837 l_paye_already char(1):='0';
838 l_object_version_number pay_pl_paye_details_f.object_version_number%type;
839 l_paye_details_id pay_pl_paye_details_f.paye_details_id%type;
840 l_effective_start_date pay_pl_paye_details_f.effective_start_date%type;
841 l_effective_end_date pay_pl_paye_details_f.effective_end_date%type;
842 l_effective_date_warning boolean;
843 l_person_id number(10);
844 l_business_group_id number(10);
845
846
847 cursor csr_get_person_bus_id is
848 select person_id,business_group_id
849 from per_all_assignments_f
850 where assignment_id=p_assignment_id
851 and p_effective_date between effective_start_date and effective_end_date;
852
853 cursor csr_paye_already_exists is
854 select '1' from pay_pl_paye_details_f
855 where contract_category='TERM_NORMAL'
856 and p_effective_date between effective_start_date and effective_end_date
857 and per_or_asg_id=p_assignment_id;
858
859 cursor csr_paye_details(r_person_id number) is
860 select tax_reduction,tax_calc_with_spouse_child,income_reduction,income_reduction_amount,rate_of_tax
861 from pay_pl_paye_details_f
862 where per_or_asg_id=r_person_id
863 and contract_category='NORMAL'
864 and p_effective_date between effective_start_date and effective_end_date;
865 l_csr_paye_details csr_paye_details%rowtype;
866
867 begin
868
869 open csr_paye_already_exists;
870 fetch csr_paye_already_exists into l_paye_already;
871 close csr_paye_already_exists;
872
873 if l_paye_already='1' then
874 null;
875 else
876 open csr_get_person_bus_id ;
877 fetch csr_get_person_bus_id into l_person_id,l_business_group_id;
878 close csr_get_person_bus_id ;
879
880 open csr_paye_details(l_person_id);
881 fetch csr_paye_details into l_csr_paye_details;
882 if csr_paye_details%FOUND then
883 pay_pl_paye_api.create_pl_paye_details
884 (p_validate => false
885 ,p_effective_date => p_effective_date
886 ,p_contract_category => 'TERM_NORMAL'
887 ,p_per_or_asg_id => p_assignment_id
888 ,p_business_group_id => l_business_group_id
889 ,p_tax_reduction => l_csr_paye_details.tax_reduction
890 ,p_tax_calc_with_spouse_child => l_csr_paye_details.tax_calc_with_spouse_child
891 ,p_income_reduction => l_csr_paye_details.income_reduction
892 ,p_income_reduction_amount => l_csr_paye_details.income_reduction_amount
893 ,p_rate_of_tax => l_csr_paye_details.rate_of_tax
894 ,p_paye_details_id => l_paye_details_id
895 ,p_object_version_number => l_object_version_number
896 ,p_effective_start_date => l_effective_start_date
897 ,p_effective_end_date => l_effective_end_date
898 ,p_effective_date_warning => l_effective_date_warning
899 );
900 end if;--csr_paye_details found
901 close csr_paye_details;
902 end if; --if l_paye_already=1?
903 end ; --declare2
904
905 end if;--(p_segment3='NORMAL' and l_assgt_type='TERM_ASSIGN' and l_prev_assgt_type<>'TERM_ASSIGN')
906
907 if l_payroll_id is not null then
908 open csr_oldage_taxoffice_check (l_person_id,p_effective_date);
909 fetch csr_oldage_taxoffice_check into l_oldage_pension_rights,l_tax_office;
910 close csr_oldage_taxoffice_check;
911
912 /* NIP is neccessary to attach a payroll only for Polish employees(Both Citizenship and nationality)
913 But this is redundant as For Polish Employee these are mandatory
914 if l_nip is null then
915 hr_utility.set_message(800,'HR_NIP_REQUIRED_PL');
916 hr_utility.raise_error;
917 end if;
918 */
919
920 if l_oldage_pension_rights is null then
921 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
922 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','OLDAGE_PENSION_RIGHTS')); --default translate false
923 hr_utility.raise_error;
924 end if;
925
926 if l_tax_office is null then
927 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
928 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','TAX_OFFICE')); --default translate false
929 hr_utility.raise_error;
930 end if;
931
932
933 open csr_assgt_type(p_assignment_id,p_effective_date) ;
934 fetch csr_assgt_type into l_assgt_type;
935 close csr_assgt_type ;
936
937
938 -- Bug 9534572
939 -- As we do not deliver Oracle Payroll functionality specific to Polish legislation,
940 -- hence SII and Tax Calculations do not come into picture for Polish customers.
941 -- Hence removing any dependency on SII/Tax Card before attaching payroll
942 -- to the assignment
943 /*
944 --if p_segment3='NORMAL' then --and l_assgt_type in ('ACTIVE_ASSIGN','SUSP_ASSIGN')) then
945 -- l_join_variable:=get_person_id(p_assignment_id,p_effective_date);
946 --else
947 -- l_join_variable:=p_assignment_id;
948 --end if;
949
950 --l_one:=0;
951
952 --open csr_check_sii_exists(l_join_variable,p_effective_date);
953 --fetch csr_check_sii_exists into l_one;
954 --close csr_check_sii_exists;
955
956 --if l_one <> 1 then
957 --hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
958 --hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_SII_INFO_FLEX'));
959 --hr_utility.raise_error;
960 --end if;
961
962 --l_one:=0;
963 --open csr_check_paye_exists(l_join_variable,p_effective_date);
964 --fetch csr_check_paye_exists into l_one;
965 --close csr_check_paye_exists;
966
967 --if l_one <> 1 then
968 --hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
969 --hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_TAX_INFO_FLEX'));
970 --hr_utility.raise_error;
971 --end if;
972 */
973
974 end if;--is payroll id null?
975 end if;--end of date checks
976 hr_utility.set_location('Leaving '||l_proc,30);
977 exception
978 when others then
979 hr_utility.set_location(l_proc,99);
980 hr_utility.raise_error;
981 end ;
982 ----end of update_pl_emp_asg----
983
984 ----Start of Update_pl_emp_asg_criteria----
985 procedure update_pl_emp_asg_criteria
986 (P_EFFECTIVE_DATE in DATE
987 ,P_ASSIGNMENT_ID in NUMBER
988 ,P_PAYROLL_ID in NUMBER
989 ,P_EMPLOYMENT_CATEGORY in VARCHAR2) is
990
991 cursor csr_get_contract_cat is
992 select segment3
993 from hr_soft_coding_keyflex keyflx,per_all_assignments_f paaf
994 where paaf.soft_coding_keyflex_id=keyflx.soft_coding_keyflex_id --Changed the join condition for Bug 7425845
995 and paaf.effective_start_date between effective_start_date and effective_end_date
996 and assignment_id=p_assignment_id;
997
998 --Added for bug 7554037
999 cursor csr_get_payroll_emp_cat is
1000 select employment_category
1001 from per_all_assignments_f
1002 where assignment_id=P_ASSIGNMENT_ID
1003 and P_EFFECTIVE_DATE between effective_start_date and effective_end_date;
1004
1005 l_contract_category hr_soft_coding_keyflex.segment3%type;
1006 l_join_variable number(10);
1007 l_assgt_type per_assignment_status_types.per_system_status%type;
1008 l_oldage_pension_rights per_all_people_f.per_information4%TYPE;
1009 l_tax_office per_all_people_f.per_information6%TYPE;
1010 l_employment_category per_all_assignments_f.employment_category%TYPE;
1011 l_one number(1);
1012 l_person_id number(10);
1013 l_proc varchar2(44);
1014
1015 Begin
1016 g_package_name :='PER_PL_ASSIGNMENT.';
1017 l_proc:=g_package_name||'UPDATE_PL_EMP_ASG_CRITERIA';
1018 /* Added for GSI Bug 5472781 */
1019 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
1020 hr_utility.set_location('Leaving : '||l_proc,10);
1021 return;
1022 END IF;
1023 hr_utility.set_location(l_proc,10);
1024 l_person_id:=get_person_id(P_ASSIGNMENT_ID,p_effective_date);
1025 open csr_get_contract_cat ;
1026 fetch csr_get_contract_cat into l_contract_category;
1027 close csr_get_contract_cat;
1028
1029 OPEN csr_get_payroll_emp_cat;
1030 FETCH csr_get_payroll_emp_cat INTO l_employment_category;
1031 CLOSE csr_get_payroll_emp_cat;
1032
1033 if l_contract_category='NORMAL' then
1034 hr_utility.set_location(l_proc,20);
1035 IF (p_employment_category IS NULL OR p_employment_category <> hr_api.g_varchar2)
1036 then
1037 hr_api.mandatory_arg_error
1038 (p_api_name => l_proc,
1039 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_CATEGORY'),
1040 p_argument_value => p_employment_category
1041 );
1042 else
1043 hr_api.mandatory_arg_error
1044 (p_api_name => l_proc,
1045 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_CATEGORY'),
1046 p_argument_value => l_employment_category
1047 );
1048 END if;
1049 end if;
1050
1051
1052 --if p_payroll_id is not null then Replace null with hr_api.g_number in 115.10
1053 --Bug 5386451
1054 if p_payroll_id <> hr_api.g_number then
1055 hr_utility.set_location(l_proc,30);
1056 open csr_oldage_taxoffice_check(l_person_id,p_effective_date);
1057 fetch csr_oldage_taxoffice_check into l_oldage_pension_rights,l_tax_office ;
1058 close csr_oldage_taxoffice_check;
1059
1060 /* NIP is neccessary to attach a payroll only for Polish employees(Both Citizenship and nationality)
1061 But this is redundant as For Polish Employee these are mandatory if l_nip is null then
1062 hr_utility.set_message(800,'HR_NIP_REQUIRED_PL');
1063 hr_utility.raise_error;
1064 end if;*/
1065
1066 if l_oldage_pension_rights is null then
1067 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
1068 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','OLDAGE_PENSION_RIGHTS')); --default translate false
1069 hr_utility.raise_error;
1070 end if;
1071
1072 if l_tax_office is null then
1073 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
1074 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','TAX_OFFICE')); --default translate false
1075 hr_utility.raise_error;
1076 end if;
1077
1078
1079 -- Bug 9534572
1080 -- As we do not deliver Oracle Payroll functionality specific to Polish legislation,
1081 -- hence SII and Tax Calculations do not come into picture for Polish customers.
1082 -- Hence removing any dependency on SII/Tax Card before attaching payroll
1083 -- to the assignment
1084 /*
1085 -- open csr_assgt_type(p_assignment_id,p_effective_date);
1086 -- fetch csr_assgt_type into l_assgt_type;
1087 -- close csr_assgt_type;
1088
1089 --check if there sii and tax record...if not error out
1090 --open csr_get_contract_cat;
1091 --fetch csr_get_contract_cat into l_contract_category;
1092 --close csr_get_contract_cat;
1093
1094 --if l_contract_category is null then
1095 --hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
1096 --hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_CATEGORY')); --Changed to CONTRACT_CATEGORY for bug 7425845
1097 --hr_utility.raise_error;
1098 --elsif l_contract_category ='NORMAL' then -- and l_assgt_type in ('ACTIVE_ASSIGN' ,'SUSP_ASSIGN') then
1099 --l_join_variable:=l_person_id;
1100 --else
1101 --l_join_variable:=p_assignment_id;
1102 --end if;
1103
1104
1105 --l_one:=0;
1106
1107 --open csr_check_sii_exists(l_join_variable,p_effective_date);
1108 --fetch csr_check_sii_exists into l_one;
1109 --close csr_check_sii_exists;
1110
1111 --if l_one <> 1 then
1112 -- hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
1113 -- hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_SII_INFO_FLEX'));
1114 -- hr_utility.raise_error;
1115 --end if;
1116
1117 --l_one:=0;
1118 --open csr_check_paye_exists(l_join_variable,p_effective_date);
1119 --fetch csr_check_paye_exists into l_one;
1120 --close csr_check_paye_exists;
1121
1122 --if l_one <> 1 then
1123 -- hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
1124 -- hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_TAX_INFO_FLEX'));
1125 -- hr_utility.raise_error;
1126 --end if;
1127 */
1128
1129 end if; --payroll id is null?
1130 hr_utility.set_location(l_proc,40);
1131 Exception
1132 when others then
1133 hr_utility.set_location(l_proc,50);
1134 hr_utility.raise_error;
1135 end;
1136
1137 PROCEDURE CREATE_PL_SECONDARY_EMP_ASG_A
1138 (P_ASSIGNMENT_ID in number,
1139 P_EFFECTIVE_DATE in date,
1140 P_SCL_SEGMENT3 in varchar2) is
1141
1142 cursor csr_business_group is
1143 select business_group_id
1144 from per_all_assignments_f
1145 where assignment_id = p_assignment_id;
1146
1147 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1148
1149 l_object_version_number pay_pl_paye_details_f.object_version_number%type;
1150 l_paye_details_id pay_pl_paye_details_f.paye_details_id%type;
1151 l_effective_start_date pay_pl_paye_details_f.effective_start_date%type;
1152 l_effective_end_date pay_pl_paye_details_f.effective_end_date%type;
1153 l_effective_date_warning boolean;
1154 l_proc varchar2(30);
1155
1156 BEGIN
1157 l_proc:='CREATE_PL_SECONDARY_EMP_ASG_A';
1158 /* Added for GSI Bug 5472781 */
1159 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
1160 hr_utility.set_location('Leaving : '||l_proc,10);
1161 return;
1162 END IF;
1163
1164 if p_scl_segment3 = 'LUMP' then
1165
1166 open csr_business_group;
1167 fetch csr_business_group into l_business_group_id;
1168 close csr_business_group;
1169
1170 pay_pl_paye_api.create_pl_paye_details
1171 (p_effective_date => p_effective_date
1172 ,p_contract_category => p_scl_segment3
1173 ,p_per_or_asg_id => p_assignment_id
1174 ,p_business_group_id => l_business_group_id
1175 ,p_tax_reduction => null
1176 ,p_tax_calc_with_spouse_child => null
1177 ,p_income_reduction => null
1178 ,p_rate_of_tax => null
1179 ,p_paye_details_id => l_paye_details_id
1180 ,p_object_version_number => l_object_version_number
1181 ,p_effective_start_date => l_effective_start_date
1182 ,p_effective_end_date => l_effective_end_date
1183 ,p_effective_date_warning => l_effective_date_warning);
1184
1185 end if;
1186
1187 END CREATE_PL_SECONDARY_EMP_ASG_A;
1188
1189
1190 PROCEDURE UPDATE_PL_EMP_ASG_A
1191 (P_EFFECTIVE_DATE in date,
1192 P_SEGMENT3 in varchar2,
1193 P_ASSIGNMENT_ID in number) is
1194
1195
1196 cursor csr_business_group is
1197 select business_group_id
1198 from per_all_assignments_f
1199 where assignment_id = p_assignment_id;
1200
1201 cursor csr_segment3 is
1202 select soft.segment3
1203 from hr_soft_coding_keyflex soft, per_all_assignments_f paaf
1204 where paaf.soft_coding_keyflex_id = soft.soft_coding_keyflex_id
1205 and paaf.assignment_id = p_assignment_id
1206 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
1207
1208 l_object_version_number pay_pl_paye_details_f.object_version_number%type;
1209 l_paye_details_id pay_pl_paye_details_f.paye_details_id%type;
1210 l_effective_start_date pay_pl_paye_details_f.effective_start_date%type;
1211 l_effective_end_date pay_pl_paye_details_f.effective_end_date%type;
1212 l_effective_date_warning boolean;
1213 l_record_exists varchar2(1);
1214 l_business_group_id pay_pl_paye_details_f.business_group_id%TYPE;
1215 l_segment3 hr_soft_coding_keyflex.segment3%TYPE;
1216 l_proc varchar2(19);
1217 cursor csr_paye_exists is
1218 select '1'
1219 from pay_pl_paye_details_f
1220 where per_or_asg_id = p_assignment_id
1221 and contract_category = l_segment3;
1222
1223 BEGIN
1224 l_proc:='UPDATE_PL_EMP_ASG_A';
1225 /* Added for GSI Bug 5472781 */
1226 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
1227 hr_utility.set_location('Leaving : '||l_proc,10);
1228 return;
1229 END IF;
1230
1231 l_record_exists := '0';
1232 l_segment3 := p_segment3;
1233
1234 if p_segment3 = hr_api.g_varchar2 then
1235 open csr_segment3;
1236 fetch csr_segment3 into l_segment3;
1237 close csr_segment3;
1238 end if;
1239
1240 if l_segment3 = 'LUMP' then
1241
1242 /* Check if a Tax record already exists for this assignment. If not we create one */
1243 open csr_paye_exists;
1244 fetch csr_paye_exists into l_record_exists;
1245 close csr_paye_exists;
1246
1247 if l_record_exists <> '1' then
1248
1249 open csr_business_group;
1250 fetch csr_business_group into l_business_group_id;
1251 close csr_business_group;
1252
1253 pay_pl_paye_api.create_pl_paye_details
1254 (p_effective_date => p_effective_date
1255 ,p_contract_category => l_segment3
1256 ,p_per_or_asg_id => p_assignment_id
1257 ,p_business_group_id => l_business_group_id
1258 ,p_tax_reduction => null
1259 ,p_tax_calc_with_spouse_child => null
1260 ,p_income_reduction => null
1261 ,p_rate_of_tax => null
1262 ,p_paye_details_id => l_paye_details_id
1263 ,p_object_version_number => l_object_version_number
1264 ,p_effective_start_date => l_effective_start_date
1265 ,p_effective_end_date => l_effective_end_date
1266 ,p_effective_date_warning => l_effective_date_warning);
1267
1268 end if;
1269
1270 end if;
1271
1272 END UPDATE_PL_EMP_ASG_A;
1273
1274 end per_pl_assignment;
[Home] [Help]