1 package body PER_PL_ASSIGNMENT as
2 /* $Header: peplasgp.pkb 120.10.12010000.2 2008/09/29 04:58:46 rbabla ship $ */
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 g_package_name :='PER_PL_ASSIGNMENT.';
101 l_proc := g_package_name||'CREATE_PL_SECONDARY_EMP_ASG';
102 /* Added for GSI Bug 5472781 */
103 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
104 hr_utility.set_location('Leaving : '||l_proc,10);
105 return;
106 END IF;
107 hr_utility.set_location(l_proc,10);
108
109
110 hr_api.mandatory_arg_error --Contract Category is mandatory
111 (p_api_name => l_proc,
112 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_CATEGORY'),
113 p_argument_value => p_scl_segment3
114 );
115
116 hr_api.mandatory_arg_error --Contract Type is Mandatory
117 (p_api_name => l_proc,
118 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE'),
119 p_argument_value => p_scl_segment4
120 );
121
122
123 hr_api.mandatory_arg_error --Contract Number is Mandatory
124 (p_api_name => l_proc,
125 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_NUMBER'),
126 p_argument_value => p_scl_segment5
127 );
128
129 hr_api.mandatory_arg_error --Contract Start Date is mandatory
130 (p_api_name => l_proc,
131 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'),
132 p_argument_value => p_scl_segment11
133 );
134
135 hr_api.mandatory_arg_error --Date Contract Signed is mandatory
136 (p_api_name => l_proc,
137 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'),
138 p_argument_value => p_scl_segment14
139 );
140
141 hr_utility.set_location(l_proc,20);
142 ------Conditionally mandatory---------
143 --------for a normal contract,the assignment category is mandatory...
144 -------core store this in employment_category in per_all_assignments_f table..displays it as assignment_category on UI
145 if p_scl_segment3='NORMAL' then
146 hr_api.mandatory_arg_error
147 (p_api_name => l_proc,
148 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_CATEGORY'),
149 p_argument_value => P_EMPLOYMENT_CATEGORY
150 );
151 end if;
152
153
154 if p_scl_segment3='NORMAL' and p_scl_segment4<>'N01' then
155 hr_api.mandatory_arg_error
156 (p_api_name => l_proc,
157 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PLANNED_VALID_TO_DATE'),
158 p_argument_value => P_SCL_SEGMENT12
159 );
160 elsif p_scl_segment12 is not null then
161 hr_utility.set_message(800,'HR_375869_PLANNED_DATE_INVALID');
162 hr_utility.raise_error;
163 end if;
164
165 -----if any of the following 3 has been entered then ..other two become mandatory
166 -----Notice Period Date(p_scl_segment15),Notice Period End Date(p_scl_segment16),Notice Period(p_notice_period)
167 if p_scl_segment16||p_scl_segment15||p_notice_period is not null then
168 hr_api.mandatory_arg_error
169 (p_api_name => l_proc,
170 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_END_DATE'),
171 p_argument_value => p_scl_segment16
172 );
173 hr_api.mandatory_arg_error
174 (p_api_name => l_proc,
175 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'),
176 p_argument_value => p_scl_segment15
177 );
178 hr_api.mandatory_arg_error
179 (p_api_name => l_proc,
180 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD'),
181 p_argument_value => p_notice_period
182 );
183 if ( (p_NOTICE_PERIOD_UOM like '_1' and p_notice_period >1) or
184 (p_NOTICE_PERIOD_UOM like '_' and p_notice_period =1)) then
185 hr_utility.set_message(800,'HR_375856_NOTICE_UNIT_MISMATCH');
186 hr_utility.raise_error;
187 end if;
188 end if;
189 -----if p_notice_period is entered and notice_period_uom is null??
190 -----taken care by core per_asg_bus3.chk_notice_period_uom
191
192
193 --Bug 4504375
194 -- change of contract reason and contract type change date are mandatory
195 -- if any one of them is not null
196 if p_scl_segment6 is not null and p_scl_segment13 is null then
197 hr_utility.set_message(800,'HR_375835_ENTER_OTHER_VALUE');
198 hr_utility.set_message_token(l_token_name=>'DETAIL1',
199 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CHANGE_OF_CONTRACT_REASON')));
200 hr_utility.set_message_token(l_token_name=>'DETAIL2',
201 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE')));
202
203 hr_utility.raise_error;
204 elsif p_scl_segment6 is null and p_scl_segment13 is not null then
205 hr_utility.set_message(800,'HR_375835_ENTER_OTHER_VALUE');
206 hr_utility.set_message_token(l_token_name=>'DETAIL1',
207 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE')));
208
209 hr_utility.set_message_token(l_token_name=>'DETAIL2',
210 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CHANGE_OF_CONTRACT_REASON')));
211 hr_utility.raise_error;
212 end if;
213
214 -----other validations like value set comparison will be taken
215 -----care by core when they validate the flexfields.
216 -----what we need to check are the dates being entered.
217 hr_utility.set_location(l_proc,30);
218 if fnd_date.canonical_to_date(p_scl_segment11) < p_effective_date then
219 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
220 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
221 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_START_DATE'));
222 hr_utility.raise_error;
223
224 elsif fnd_date.canonical_to_date(p_scl_segment12) < fnd_date.canonical_to_date(p_scl_segment11) then
225 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
226 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PLANNED_VALID_TO_DATE'));
227 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
228 hr_utility.raise_error;
229
230 elsif fnd_date.canonical_to_date(p_scl_segment13) < fnd_date.canonical_to_date(p_scl_segment11) then
231 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
232 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE'));
233 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
234 hr_utility.raise_error;
235
236 /* Bug4504375 :Contract type Change Date should not be before Date Contract Signed.*/
237 elsif fnd_date.canonical_to_date(p_scl_segment13) < fnd_date.canonical_to_date(p_scl_segment14) then
238 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
239 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE'));
240 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'));
241 hr_utility.raise_error;
242
243
244 /*
245 This check has been removed because of bug 4504312 DATE CONTRACT SIGNED1 ACCEPTS DATES EVEN AFTER THE CONTRACT START DATE
246 Hence Date Contract Signed must be before or same as Contract Start Date. ie)(p_scl_segment14)<(p_scl_segment11)is the condition
247 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
248 Date contract Signed.
249 elsif fnd_date.canonical_to_date(p_scl_segment14) < fnd_date.canonical_to_date(p_scl_segment11) then
250 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
251 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'));
252 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
253 hr_utility.raise_error;
254 */
255
256 elsif fnd_date.canonical_to_date(p_scl_segment15) < fnd_date.canonical_to_date(p_scl_segment11) then
257 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
258 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'));
259 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
260 hr_utility.raise_error;
261 elsif fnd_date.canonical_to_date(p_scl_segment16) < fnd_date.canonical_to_date(p_scl_segment15) then
262 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
263 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_END_DATE'));
264 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'));
265 hr_utility.raise_error;
266 elsif p_payroll_id is not null then
267
268 l_oldage_pension_rights:=null;
269
270 open csr_oldage_taxoffice_check(p_person_id,p_effective_date);
271 fetch csr_oldage_taxoffice_check into l_oldage_pension_rights,l_tax_office ;
272 close csr_oldage_taxoffice_check;
273
274 /* NIP is neccessary to attach a payroll only for Polish employees(Both Citizenship and nationality)
275 But this is redundant as For Polish Employee these are mandatory
276 if l_nip is null then
277 hr_utility.set_message(800,'HR_NIP_REQUIRED_PL');
278 hr_utility.raise_error;
279 end if; */
280
281 if l_oldage_pension_rights is null then
282 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
283 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
284 hr_utility.raise_error;
285 end if;
286
287 if l_tax_office is null then
288 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
289 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','TAX_OFFICE')); --default translate false
290 hr_utility.raise_error;
291 end if;
292
293 l_one:=0;
294
295 if p_scl_segment3 in ('CIVIL','F_LUMP','LUMP') then
296 /*we cannot have a civil contract with payroll id while creating...
297 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...*/
298 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
299 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_SII_INFO_FLEX'));
300 hr_utility.raise_error;
301 end if;
302
303
304 open csr_check_sii_exists(p_person_id,p_effective_date) ;
305 fetch csr_check_sii_exists into l_one;
306 close csr_check_sii_exists;
307
308 if l_one <> 1 then
309 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
310 hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_SII_INFO_FLEX'));
311 hr_utility.raise_error;
312 end if;
313
314 l_one:=0;
315
316 open csr_check_paye_exists(p_person_id,p_effective_date);
317 fetch csr_check_paye_exists into l_one;
318 close csr_check_paye_exists;
319
320 if l_one<> 1 then
321 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
322 hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_TAX_INFO_FLEX'));
323 hr_utility.raise_error;
324 end if;
325
326 end if; --date checks over
327
328 hr_utility.set_location(l_proc,40);
329 Exception
330 when others then
331 hr_utility.set_location(l_proc,50);
332 hr_utility.raise_error;
333 end create_pl_secondary_emp_asg;
334
335
336 ---------start of update_pl_emp_asg-----------
337
338 procedure update_pl_emp_asg
339 (P_EFFECTIVE_DATE DATE
340 ,P_ASSIGNMENT_ID NUMBER
341 ,P_ASSIGNMENT_STATUS_TYPE_ID NUMBER
342 ,P_SEGMENT3 VARCHAR2
343 ,P_SEGMENT4 VARCHAR2
344 ,P_SEGMENT5 VARCHAR2
345 ,P_SEGMENT6 VARCHAR2
346 ,P_SEGMENT7 VARCHAR2
347 ,P_SEGMENT8 VARCHAR2
348 ,P_SEGMENT9 VARCHAR2
349 ,P_SEGMENT11 VARCHAR2
350 ,P_SEGMENT12 VARCHAR2
351 ,P_SEGMENT13 VARCHAR2
352 ,P_SEGMENT14 VARCHAR2
353 ,P_SEGMENT15 VARCHAR2
354 ,P_SEGMENT16 VARCHAR2
355 ,P_NOTICE_PERIOD NUMBER
356 ,P_NOTICE_PERIOD_UOM VARCHAR2
357 )is
358
359 cursor csr_get_payroll_emp_cat is
360 select payroll_id,employment_category
361 from per_all_assignments_f
362 where assignment_id=P_ASSIGNMENT_ID
363 and P_EFFECTIVE_DATE between effective_start_date and effective_end_date;
364
365 cursor csr_get_contract_details(r_date date) is
366 select kyflx.segment3,kyflx.segment4,kyflx.segment6,fnd_date.canonical_to_date(segment13)
367 from hr_soft_coding_keyflex kyflx , per_all_assignments_f paaf
368 where paaf.assignment_id = P_ASSIGNMENT_ID
369 and paaf.soft_coding_keyflex_id= kyflx.soft_coding_keyflex_id
370 and r_date between effective_start_date and effective_end_date;
371
372 -- Bug 7041296
373 cursor csr_effective_start_date is
374 select min(effective_start_date)
375 from per_all_assignments_f
376 where assignment_id=P_ASSIGNMENT_ID;
377
378 l_employment_category per_all_assignments_f.employment_category%type;
379 l_proc varchar2(35);
380 l_assgt_type varchar2(30);
381 l_prev_assgt_type varchar2(30);
382 l_oldage_pension_rights per_all_people_f.per_information4%TYPE;
383 l_tax_office per_all_people_f.per_information6%TYPE;
384 l_one number(1);
385 l_join_variable number(10);
386 l_payroll_id number(10);
387 l_contract_category hr_soft_coding_keyflex.segment3%type;
388 l_contract_type hr_soft_coding_keyflex.segment4%type;
389 l_contract_change_reason hr_soft_coding_keyflex.segment6%type;
390 l_contract_type_change_date date;
391 l_person_id number(10) ;
392 l_asg_min_start_date date ;
393
394
395 begin
396 --1)Mandatory argument checks
397 --2)Conditionally mandatory arguments ...planned valid to date,notice_period,notice_period_date(scl_segment15),notice_period_end_date(scl_segment16)
398 --3)you cannot correct or update segment3 once enetred
399 --4)in order to update the contract type(segment4),there must be value for change of contract Reason(segment6)
400 -- and Contract type change date must be equal to p_effective_date -1
401 --5)contract number cannot be longer than 30 characters
402 --6)Date checks
403 --7)payroll check ..get it from the table and then do the validation
404 g_package_name :='PER_PL_ASSIGNMENT.';
405 l_proc:=g_package_name||'UPDATE_PL_EMP_ASG';
406 /* Added for GSI Bug 5472781 */
407 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
408 hr_utility.set_location('Leaving : '||l_proc,10);
409 return;
410 END IF;
411 hr_utility.set_location(l_proc,10);
412 l_person_id:= get_person_id(p_assignment_id,p_effective_date);
413 hr_api.mandatory_arg_error --Contract Category is mandatory
414 (p_api_name => l_proc,
415 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_CATEGORY'),
416 p_argument_value => p_segment3
417 );
418
419 hr_api.mandatory_arg_error --Contract Type is Mandatory
420 (p_api_name => l_proc,
421 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE'),
422 p_argument_value => p_segment4
423 );
424
425
426 hr_api.mandatory_arg_error --Contract Number is Mandatory
427 (p_api_name => l_proc,
428 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_NUMBER'),
429 p_argument_value => p_segment5
430 );
431
432 hr_api.mandatory_arg_error --Contract Start Date is mandatory
433 (p_api_name => l_proc,
434 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'),
435 p_argument_value => p_segment11
436 );
437
438 hr_api.mandatory_arg_error --Date Contract Signed is mandatory
439 (p_api_name => l_proc,
440 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'),
441 p_argument_value => p_segment14
442 );
443
444 hr_utility.set_location(l_proc,20);
445
446 ------Conditionally mandatory---------
447 /*
448 1)update_emp_asg_criteria will be called first..this is where the payroll id,employment_category will be set
449 2)since these values are not available here...we have to query for the changes....
450 3) do not use per_asg_shd.g_old_rec.employment_category or payroll_id
451 */
452 if p_segment3='NORMAL' then
453 l_employment_category:=null;
454 open csr_get_payroll_emp_cat;
455 fetch csr_get_payroll_emp_cat into l_payroll_id,l_employment_category;
456 close csr_get_payroll_emp_cat;
457 hr_api.mandatory_arg_error
458 (p_api_name => l_proc,
459 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_CATEGORY'),
460 p_argument_value => l_employment_category
461 );
462 end if;
463
464 if p_segment3='NORMAL' and p_segment4<>'N01' then
465 hr_api.mandatory_arg_error
466 (p_api_name => l_proc,
467 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PLANNED_VALID_TO_DATE'),
468 p_argument_value => P_SEGMENT12
469 );
470 elsif p_segment12<>hr_api.g_varchar2 then --replaced in 115.11
471 --Bug 5386451
472 hr_utility.set_message(800,'HR_375869_PLANNED_DATE_INVALID');
473 hr_utility.raise_error;
474 end if;
475
476 -----if any of the following 3 has been entered then ..other two become mandatory
477 -----Notice Period Date(p_scl_segment15),Notice Period End Date(p_scl_segment16),Notice Period(p_notice_period)
478 if p_segment16||p_segment15||p_notice_period is not null then
479 hr_api.mandatory_arg_error
480 (p_api_name => l_proc,
481 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_END_DATE'),
482 p_argument_value => p_segment16
483 );
484 hr_api.mandatory_arg_error
485 (p_api_name => l_proc,
486 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'),
487 p_argument_value => p_segment15
488 );
489 hr_api.mandatory_arg_error
490 (p_api_name => l_proc,
491 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD'),
492 p_argument_value => p_notice_period
493 );
494 if ( (p_NOTICE_PERIOD_UOM like '_1' and p_notice_period >1) or
495 (p_NOTICE_PERIOD_UOM like '_' and p_notice_period =1)) then
496 hr_utility.set_message(800,'HR_375856_NOTICE_UNIT_MISMATCH');
497 hr_utility.raise_error;
498 end if;
499 end if;
500 -----if p_notice_period is entered and notice_period_uom is null??
501 -----taken care by core per_asg_bus3.chk_notice_period_uom
502
503 --Bug 4504375
504 -- change of contract reason and contract type change date are mandatory
505 -- if any one of them is not null
506 if p_segment6 is not null and p_segment13 is null then
507 hr_utility.set_message(800,'HR_375835_ENTER_OTHER_VALUE');
508 hr_utility.set_message_token(l_token_name=>'DETAIL1',
509 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CHANGE_OF_CONTRACT_REASON')));
510 hr_utility.set_message_token(l_token_name=>'DETAIL2',
511 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE')));
512
513 hr_utility.raise_error;
514 elsif p_segment6 is null and p_segment13 is not null then
515 hr_utility.set_message(800,'HR_375835_ENTER_OTHER_VALUE');
516 hr_utility.set_message_token(l_token_name=>'DETAIL1',
517 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE')));
518
519 hr_utility.set_message_token(l_token_name=>'DETAIL2',
520 l_token_value=>lower(hr_general.decode_lookup('PL_FORM_LABELS','CHANGE_OF_CONTRACT_REASON')));
521 hr_utility.raise_error;
522 end if;
523
524
525
526 --what was the contract category yesterday?
527 open csr_get_contract_details(p_effective_date-1);
528 fetch csr_get_contract_details into l_contract_category,l_contract_type,l_contract_change_reason,l_contract_type_change_date;
529 close csr_get_contract_details;
530 --no need to check for null ...
531
532 --contract category cannot be changed once created
533 if l_contract_category <> p_segment3 then
534 hr_utility.set_message(800,'HR_375868_DONT_CHANGE_CATEGORY');
535 hr_utility.raise_error;
536 end if;
537
538 --contract type change allowed only after reason and change date are provided
539 --p_contract_type cannot be null...no check if user is updating it from null to some contract type
540 if l_contract_type <> p_segment4 then
541 if(l_contract_change_reason is null or (p_effective_date-1)<>nvl(l_contract_type_change_date,p_effective_date)) then
542 hr_utility.set_message(800,'HR_375867_DISALLOW_TYPE_CHANGE');
543 hr_utility.raise_error;
544 end if;
545 end if;
546
547 --contract length to be within 30 characters
548 if length(p_segment5)>30 then
549 hr_utility.set_message(800,'HR_375863_CONTRACT_NUM_LENGTH');
550 hr_utility.set_message_token(l_token_name=>'TYPE',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_NUMBER'));
551 hr_utility.set_message_token(l_token_name=>'LENGTH',l_token_value=>'30');
552 hr_utility.raise_error;
553 end if;
554
555 -----other validations like value set comparison will be taken
556 ----care by core when they validate the flexfields.
557 -----what we need to check are the dates being entered.
558
559 hr_utility.set_location(l_proc,30);
560
561 -- Bug 7041296
562 -- pick minimum start date of assignment
563 -- and compare contract_start_date with minimum(assignment eff_start_date)
564 open csr_effective_start_date;
565 fetch csr_effective_start_date into l_asg_min_start_date;
566 close csr_effective_start_date;
567
568 --hr_utility.trace('Bug 7041296 : p_segment11 :'||p_segment11||'l_asg_min_start_date:'||l_asg_min_start_date);
569
570 if fnd_date.canonical_to_date(p_segment11) < l_asg_min_start_date THEN --Bug 7041296 p_effective_date then
571 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
572 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
573 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_START_DATE'));
574 hr_utility.raise_error;
575
576 elsif fnd_date.canonical_to_date(p_segment12) < fnd_date.canonical_to_date(p_segment11) then
577 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
578 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','PLANNED_VALID_TO_DATE'));
579 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
580 hr_utility.raise_error;
581
582 elsif fnd_date.canonical_to_date(p_segment13) < fnd_date.canonical_to_date(p_segment11) then
583 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
584 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE'));
585 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
586 hr_utility.raise_error;
587
588 /* Bug4504375 :Contract type Change Date should not be before Date Contract Signed.*/
589 elsif fnd_date.canonical_to_date(p_segment13) < fnd_date.canonical_to_date(p_segment14) then
590 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
591 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_TYPE_CHANGE_DATE'));
592 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'));
593 hr_utility.raise_error;
594
595 /*
596 This check has been removed because of bug 4504312 DATE CONTRACT SIGNE D ACCEPTS DATES EVEN AFTER THE CONTRACT START DATE
597 Hence Date Contract Signed must be before or same as Contract Start Date. ie)(p_scl_segment14)<(p_scl_segment11)is the condition
598 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
599 Date contract Signed.
600 elsif fnd_date.canonical_to_date(p_segment14) < fnd_date.canonical_to_date(p_segment11) then
601 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
602 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','DATE_CONTRACT_SIGNED'));
603 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
604 hr_utility.raise_error;
605 */
606
607 elsif fnd_date.canonical_to_date(p_segment15) < fnd_date.canonical_to_date(p_segment11) then
608 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
609 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'));
610 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_START_DATE'));
611 hr_utility.raise_error;
612 elsif fnd_date.canonical_to_date(p_segment16) < fnd_date.canonical_to_date(p_segment15) then
613 hr_utility.set_message(800,'HR_375853_DATE1_AFTER_DATE2');
614 hr_utility.set_message_token(l_token_name=>'DATE1',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_END_DATE'));
615 hr_utility.set_message_token(l_token_name=>'DATE2',l_token_value=> hr_general.decode_lookup('PL_FORM_LABELS','NOTICE_PERIOD_DATE'));
616 hr_utility.raise_error;
617 else
618
619 --if status has been changed ..then create sii/paye record from existing tax record if they already exist
620 open csr_assgt_type(p_assignment_id,p_effective_date-1);
621 fetch csr_assgt_type into l_prev_assgt_type;
622 close csr_assgt_type;
623
624 open csr_assgt_type(p_assignment_id,p_effective_date);
625 fetch csr_assgt_type into l_assgt_type;
626 close csr_assgt_type;
627
628 if (p_segment3='NORMAL' and l_assgt_type='TERM_ASSIGN' and l_prev_assgt_type<>'TERM_ASSIGN') then
629 --check if record already exists with contract category as term_normal?
630
631 declare --declare 1
632 l_sii_already char(1):='0';
633 l_object_version_number pay_pl_sii_details_f.object_version_number%type;
634 l_sii_details_id pay_pl_sii_details_f.sii_details_id%type;
635 l_effective_start_date pay_pl_sii_details_f.effective_start_date%type;
636 l_effective_end_date pay_pl_sii_details_f.effective_end_date%type;
637 l_effective_date_warning boolean;
638 l_person_id number(10);
639 l_business_group_id number(10);
640
641 cursor csr_get_person_bus_id is
642 select person_id,business_group_id
643 from per_all_assignments_f
644 where assignment_id=p_assignment_id
645 and p_effective_date between effective_start_date and effective_end_date;
646
647 cursor csr_sii_already_exists is
648 select '1' from pay_pl_sii_details_f
649 where contract_category='TERM_NORMAL'
650 and p_effective_date between effective_start_date and effective_end_date
651 and per_or_asg_id=p_assignment_id;
652
653 cursor csr_sii_details(r_person_id number) is
654 select emp_social_security_info,old_age_contribution,pension_contribution,sickness_contribution,
655 work_injury_contribution,labor_contribution,health_contribution,unemployment_contribution,
656 old_age_cont_end_reason,pension_cont_end_reason,sickness_cont_end_reason,
657 work_injury_cont_end_reason,labor_fund_cont_end_reason,health_cont_end_reason,unemployment_cont_end_reason
658 from pay_pl_sii_details_f
659 where per_or_asg_id=r_person_id
660 and contract_category='NORMAL'
661 and p_effective_date between effective_start_date and effective_end_date;
662 l_csr_sii_details csr_sii_details%rowtype;
663
664 begin
665
666 open csr_sii_already_exists ;
667 fetch csr_sii_already_exists into l_sii_already;
668 close csr_sii_already_exists;
669 if l_sii_already='1' then
670 null;
671 else
672 open csr_get_person_bus_id ;
673 fetch csr_get_person_bus_id into l_person_id,l_business_group_id;
674 close csr_get_person_bus_id ;
675
676 open csr_sii_details(l_person_id);
677 fetch csr_sii_details into l_csr_sii_details;
678 if csr_sii_details%FOUND then
679 pay_pl_sii_api.create_pl_sii_details
680 (p_validate =>false
681 ,p_effective_date =>p_effective_date
682 ,p_contract_category =>'TERM_NORMAL'
683 ,p_per_or_asg_id =>p_assignment_id
684 ,p_business_group_id =>l_business_group_id
685 ,p_emp_social_security_info =>l_csr_sii_details.emp_social_security_info
686 ,p_old_age_contribution =>l_csr_sii_details.old_age_contribution
687 ,p_pension_contribution =>l_csr_sii_details.pension_contribution
688 ,p_sickness_contribution =>l_csr_sii_details.sickness_contribution
689 ,p_work_injury_contribution =>l_csr_sii_details.work_injury_contribution
690 ,p_labor_contribution =>l_csr_sii_details.labor_contribution
691 ,p_health_contribution =>l_csr_sii_details.health_contribution
692 ,p_unemployment_contribution =>l_csr_sii_details.unemployment_contribution
693 ,p_old_age_cont_end_reason =>l_csr_sii_details.old_age_cont_end_reason
694 ,p_pension_cont_end_reason =>l_csr_sii_details.pension_cont_end_reason
695 ,p_sickness_cont_end_reason =>l_csr_sii_details.sickness_cont_end_reason
696 ,p_work_injury_cont_end_reason =>l_csr_sii_details.work_injury_cont_end_reason
697 ,p_labor_fund_cont_end_reason =>l_csr_sii_details.labor_fund_cont_end_reason
698 ,p_health_cont_end_reason =>l_csr_sii_details.health_cont_end_reason
699 ,p_unemployment_cont_end_reason =>l_csr_sii_details.unemployment_cont_end_reason
700 ,p_sii_details_id =>l_sii_details_id
701 ,p_object_version_number =>l_object_version_number
702 ,p_effective_start_date =>l_effective_start_date
703 ,p_effective_end_date =>l_effective_end_date
704 ,p_effective_date_warning =>l_effective_date_warning);
705 end if;--csr_sii_details found
706 close csr_sii_details;
707 end if; --is sii_alreasdy=1?
708 end ; --of declare1
709
710
711 declare --declare2
712 l_paye_already char(1):='0';
713 l_object_version_number pay_pl_paye_details_f.object_version_number%type;
714 l_paye_details_id pay_pl_paye_details_f.paye_details_id%type;
715 l_effective_start_date pay_pl_paye_details_f.effective_start_date%type;
716 l_effective_end_date pay_pl_paye_details_f.effective_end_date%type;
717 l_effective_date_warning boolean;
718 l_person_id number(10);
719 l_business_group_id number(10);
720
721
722 cursor csr_get_person_bus_id is
723 select person_id,business_group_id
724 from per_all_assignments_f
725 where assignment_id=p_assignment_id
726 and p_effective_date between effective_start_date and effective_end_date;
727
728 cursor csr_paye_already_exists is
729 select '1' from pay_pl_paye_details_f
730 where contract_category='TERM_NORMAL'
731 and p_effective_date between effective_start_date and effective_end_date
732 and per_or_asg_id=p_assignment_id;
733
734 cursor csr_paye_details(r_person_id number) is
735 select tax_reduction,tax_calc_with_spouse_child,income_reduction,income_reduction_amount,rate_of_tax
736 from pay_pl_paye_details_f
737 where per_or_asg_id=r_person_id
738 and contract_category='NORMAL'
739 and p_effective_date between effective_start_date and effective_end_date;
740 l_csr_paye_details csr_paye_details%rowtype;
741
742 begin
743
744 open csr_paye_already_exists;
745 fetch csr_paye_already_exists into l_paye_already;
746 close csr_paye_already_exists;
747
748 if l_paye_already='1' then
749 null;
750 else
751 open csr_get_person_bus_id ;
752 fetch csr_get_person_bus_id into l_person_id,l_business_group_id;
753 close csr_get_person_bus_id ;
754
755 open csr_paye_details(l_person_id);
756 fetch csr_paye_details into l_csr_paye_details;
757 if csr_paye_details%FOUND then
758 pay_pl_paye_api.create_pl_paye_details
759 (p_validate => false
760 ,p_effective_date => p_effective_date
761 ,p_contract_category => 'TERM_NORMAL'
762 ,p_per_or_asg_id => p_assignment_id
763 ,p_business_group_id => l_business_group_id
764 ,p_tax_reduction => l_csr_paye_details.tax_reduction
765 ,p_tax_calc_with_spouse_child => l_csr_paye_details.tax_calc_with_spouse_child
766 ,p_income_reduction => l_csr_paye_details.income_reduction
767 ,p_income_reduction_amount => l_csr_paye_details.income_reduction_amount
768 ,p_rate_of_tax => l_csr_paye_details.rate_of_tax
769 ,p_paye_details_id => l_paye_details_id
770 ,p_object_version_number => l_object_version_number
771 ,p_effective_start_date => l_effective_start_date
772 ,p_effective_end_date => l_effective_end_date
773 ,p_effective_date_warning => l_effective_date_warning
774 );
775 end if;--csr_paye_details found
776 close csr_paye_details;
777 end if; --if l_paye_already=1?
778 end ; --declare2
779
780 end if;--(p_segment3='NORMAL' and l_assgt_type='TERM_ASSIGN' and l_prev_assgt_type<>'TERM_ASSIGN')
781
782 if l_payroll_id is not null then
783 open csr_oldage_taxoffice_check (l_person_id,p_effective_date);
784 fetch csr_oldage_taxoffice_check into l_oldage_pension_rights,l_tax_office;
785 close csr_oldage_taxoffice_check;
786
787 /* NIP is neccessary to attach a payroll only for Polish employees(Both Citizenship and nationality)
788 But this is redundant as For Polish Employee these are mandatory
789 if l_nip is null then
790 hr_utility.set_message(800,'HR_NIP_REQUIRED_PL');
791 hr_utility.raise_error;
792 end if;
793 */
794
795 if l_oldage_pension_rights is null then
796 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
797 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
798 hr_utility.raise_error;
799 end if;
800
801 if l_tax_office is null then
802 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
803 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','TAX_OFFICE')); --default translate false
804 hr_utility.raise_error;
805 end if;
806
807
808 open csr_assgt_type(p_assignment_id,p_effective_date) ;
809 fetch csr_assgt_type into l_assgt_type;
810 close csr_assgt_type ;
811
812 if p_segment3='NORMAL' then --and l_assgt_type in ('ACTIVE_ASSIGN','SUSP_ASSIGN')) then
813 l_join_variable:=get_person_id(p_assignment_id,p_effective_date);
814 else
815 l_join_variable:=p_assignment_id;
816 end if;
817
818 l_one:=0;
819
820 open csr_check_sii_exists(l_join_variable,p_effective_date);
821 fetch csr_check_sii_exists into l_one;
822 close csr_check_sii_exists;
823
824 if l_one <> 1 then
825 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
826 hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_SII_INFO_FLEX'));
827 hr_utility.raise_error;
828 end if;
829
830 l_one:=0;
831 open csr_check_paye_exists(l_join_variable,p_effective_date);
832 fetch csr_check_paye_exists into l_one;
833 close csr_check_paye_exists;
834
835 if l_one <> 1 then
836 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
837 hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_TAX_INFO_FLEX'));
838 hr_utility.raise_error;
839 end if;
840
841 end if;--is payroll id null?
842 end if;--end of date checks
843
844 exception
845 when others then
846 hr_utility.set_location(l_proc,99);
847 hr_utility.raise_error;
848 end ;
849 ----end of update_pl_emp_asg----
850
851 ----Start of Update_pl_emp_asg_criteria----
852 procedure update_pl_emp_asg_criteria
853 (P_EFFECTIVE_DATE in DATE
854 ,P_ASSIGNMENT_ID in NUMBER
855 ,P_PAYROLL_ID in NUMBER
856 ,P_EMPLOYMENT_CATEGORY in VARCHAR2) is
857
858 cursor csr_get_contract_cat is
859 select segment3
860 from hr_soft_coding_keyflex keyflx,per_all_assignments_f paaf
861 where paaf.soft_coding_keyflex_id=keyflx.soft_coding_keyflex_id --Changed the join condition for Bug 7425845
862 and paaf.effective_start_date between effective_start_date and effective_end_date
863 and assignment_id=p_assignment_id;
864 l_contract_category hr_soft_coding_keyflex.segment3%type;
865 l_join_variable number(10);
866 l_assgt_type per_assignment_status_types.per_system_status%type;
867 l_oldage_pension_rights per_all_people_f.per_information4%TYPE;
868 l_tax_office per_all_people_f.per_information6%TYPE;
869 l_one number(1);
870 l_person_id number(10);
871 l_proc varchar2(44);
872
873 Begin
874 g_package_name :='PER_PL_ASSIGNMENT.';
875 l_proc:=g_package_name||'UPDATE_PL_EMP_ASG_CRITERIA';
876 /* Added for GSI Bug 5472781 */
877 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
878 hr_utility.set_location('Leaving : '||l_proc,10);
879 return;
880 END IF;
881 hr_utility.set_location(l_proc,10);
882 l_person_id:=get_person_id(P_ASSIGNMENT_ID,p_effective_date);
883 open csr_get_contract_cat ;
884 fetch csr_get_contract_cat into l_contract_category;
885 close csr_get_contract_cat;
886 if l_contract_category='NORMAL' then
887 hr_utility.set_location(l_proc,20);
888 hr_api.mandatory_arg_error
889 (p_api_name => l_proc,
890 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','ASSIGNMENT_CATEGORY'),
891 p_argument_value => p_employment_category
892 );
893 end if;
894
895
896 --if p_payroll_id is not null then Replace null with hr_api.g_number in 115.10
897 --Bug 5386451
898 if p_payroll_id <> hr_api.g_number then
899 hr_utility.set_location(l_proc,30);
900 open csr_oldage_taxoffice_check(l_person_id,p_effective_date);
901 fetch csr_oldage_taxoffice_check into l_oldage_pension_rights,l_tax_office ;
902 close csr_oldage_taxoffice_check;
903
904 /* NIP is neccessary to attach a payroll only for Polish employees(Both Citizenship and nationality)
905 But this is redundant as For Polish Employee these are mandatory if l_nip is null then
906 hr_utility.set_message(800,'HR_NIP_REQUIRED_PL');
907 hr_utility.raise_error;
908 end if;*/
909
910 if l_oldage_pension_rights is null then
911 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
912 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
913 hr_utility.raise_error;
914 end if;
915
916 if l_tax_office is null then
917 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
918 hr_utility.set_message_token (l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','TAX_OFFICE')); --default translate false
919 hr_utility.raise_error;
920 end if;
921
922
923 open csr_assgt_type(p_assignment_id,p_effective_date);
924 fetch csr_assgt_type into l_assgt_type;
925 close csr_assgt_type;
926
927 --check if there sii and tax record...if not error out
928 open csr_get_contract_cat;
929 fetch csr_get_contract_cat into l_contract_category;
930 close csr_get_contract_cat;
931
932 if l_contract_category is null then
933 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
934 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
935 hr_utility.raise_error;
936 elsif l_contract_category ='NORMAL' then /* and l_assgt_type in ('ACTIVE_ASSIGN' ,'SUSP_ASSIGN') then */
937 l_join_variable:=l_person_id;
938 else
939 l_join_variable:=p_assignment_id;
940 end if;
941
942
943 l_one:=0;
944
945 open csr_check_sii_exists(l_join_variable,p_effective_date);
946 fetch csr_check_sii_exists into l_one;
947 close csr_check_sii_exists;
948
949 if l_one <> 1 then
950 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
951 hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_SII_INFO_FLEX'));
952 hr_utility.raise_error;
953 end if;
954
955 l_one:=0;
956 open csr_check_paye_exists(l_join_variable,p_effective_date);
957 fetch csr_check_paye_exists into l_one;
958 close csr_check_paye_exists;
959
960 if l_one <> 1 then
961 hr_utility.set_message(800,'HR_375855_DONOT_ATTACH_PAYROLL');
962 hr_utility.set_message_token(l_token_name=>'TOKEN',l_token_value=>hr_general.decode_lookup('PL_FORM_LABELS','PL_TAX_INFO_FLEX'));
963 hr_utility.raise_error;
964 end if;
965
966 end if; --payroll id is null?
967 hr_utility.set_location(l_proc,40);
968 Exception
969 when others then
970 hr_utility.set_location(l_proc,50);
971 hr_utility.raise_error;
972 end;
973
974 PROCEDURE CREATE_PL_SECONDARY_EMP_ASG_A
975 (P_ASSIGNMENT_ID in number,
976 P_EFFECTIVE_DATE in date,
977 P_SCL_SEGMENT3 in varchar2) is
978
979 cursor csr_business_group is
980 select business_group_id
981 from per_all_assignments_f
982 where assignment_id = p_assignment_id;
983
984 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
985
986 l_object_version_number pay_pl_paye_details_f.object_version_number%type;
987 l_paye_details_id pay_pl_paye_details_f.paye_details_id%type;
988 l_effective_start_date pay_pl_paye_details_f.effective_start_date%type;
989 l_effective_end_date pay_pl_paye_details_f.effective_end_date%type;
990 l_effective_date_warning boolean;
991 l_proc varchar2(30);
992
993 BEGIN
994 l_proc:='CREATE_PL_SECONDARY_EMP_ASG_A';
995 /* Added for GSI Bug 5472781 */
996 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
997 hr_utility.set_location('Leaving : '||l_proc,10);
998 return;
999 END IF;
1000
1001 if p_scl_segment3 = 'LUMP' then
1002
1003 open csr_business_group;
1004 fetch csr_business_group into l_business_group_id;
1005 close csr_business_group;
1006
1007 pay_pl_paye_api.create_pl_paye_details
1008 (p_effective_date => p_effective_date
1009 ,p_contract_category => p_scl_segment3
1010 ,p_per_or_asg_id => p_assignment_id
1011 ,p_business_group_id => l_business_group_id
1012 ,p_tax_reduction => null
1013 ,p_tax_calc_with_spouse_child => null
1014 ,p_income_reduction => null
1015 ,p_rate_of_tax => null
1016 ,p_paye_details_id => l_paye_details_id
1017 ,p_object_version_number => l_object_version_number
1018 ,p_effective_start_date => l_effective_start_date
1019 ,p_effective_end_date => l_effective_end_date
1020 ,p_effective_date_warning => l_effective_date_warning);
1021
1022 end if;
1023
1024 END CREATE_PL_SECONDARY_EMP_ASG_A;
1025
1026
1027 PROCEDURE UPDATE_PL_EMP_ASG_A
1028 (P_EFFECTIVE_DATE in date,
1029 P_SEGMENT3 in varchar2,
1030 P_ASSIGNMENT_ID in number) is
1031
1032
1033 cursor csr_business_group is
1034 select business_group_id
1035 from per_all_assignments_f
1036 where assignment_id = p_assignment_id;
1037
1038 cursor csr_segment3 is
1039 select soft.segment3
1040 from hr_soft_coding_keyflex soft, per_all_assignments_f paaf
1041 where paaf.soft_coding_keyflex_id = soft.soft_coding_keyflex_id
1042 and paaf.assignment_id = p_assignment_id
1043 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
1044
1045 l_object_version_number pay_pl_paye_details_f.object_version_number%type;
1046 l_paye_details_id pay_pl_paye_details_f.paye_details_id%type;
1047 l_effective_start_date pay_pl_paye_details_f.effective_start_date%type;
1048 l_effective_end_date pay_pl_paye_details_f.effective_end_date%type;
1049 l_effective_date_warning boolean;
1050 l_record_exists varchar2(1);
1051 l_business_group_id pay_pl_paye_details_f.business_group_id%TYPE;
1052 l_segment3 hr_soft_coding_keyflex.segment3%TYPE;
1053 l_proc varchar2(19);
1054 cursor csr_paye_exists is
1055 select '1'
1056 from pay_pl_paye_details_f
1057 where per_or_asg_id = p_assignment_id
1058 and contract_category = l_segment3;
1059
1060 BEGIN
1061 l_proc:='UPDATE_PL_EMP_ASG_A';
1062 /* Added for GSI Bug 5472781 */
1063 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
1064 hr_utility.set_location('Leaving : '||l_proc,10);
1065 return;
1066 END IF;
1067
1068 l_record_exists := '0';
1069 l_segment3 := p_segment3;
1070
1071 if p_segment3 = hr_api.g_varchar2 then
1072 open csr_segment3;
1073 fetch csr_segment3 into l_segment3;
1074 close csr_segment3;
1075 end if;
1076
1077 if l_segment3 = 'LUMP' then
1078
1079 /* Check if a Tax record already exists for this assignment. If not we create one */
1080 open csr_paye_exists;
1081 fetch csr_paye_exists into l_record_exists;
1082 close csr_paye_exists;
1083
1084 if l_record_exists <> '1' then
1085
1086 open csr_business_group;
1087 fetch csr_business_group into l_business_group_id;
1088 close csr_business_group;
1089
1090 pay_pl_paye_api.create_pl_paye_details
1091 (p_effective_date => p_effective_date
1092 ,p_contract_category => l_segment3
1093 ,p_per_or_asg_id => p_assignment_id
1094 ,p_business_group_id => l_business_group_id
1095 ,p_tax_reduction => null
1096 ,p_tax_calc_with_spouse_child => null
1097 ,p_income_reduction => null
1098 ,p_rate_of_tax => null
1099 ,p_paye_details_id => l_paye_details_id
1100 ,p_object_version_number => l_object_version_number
1101 ,p_effective_start_date => l_effective_start_date
1102 ,p_effective_end_date => l_effective_end_date
1103 ,p_effective_date_warning => l_effective_date_warning);
1104
1105 end if;
1106
1107 end if;
1108
1109 END UPDATE_PL_EMP_ASG_A;
1110
1111 end per_pl_assignment;
[Home] [Help]