[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_IE_PAYE_API
Source
1 Package Body pay_ie_paye_api as
2 /* $Header: pyipdapi.pkb 120.7.12020000.3 2013/01/29 05:00:31 rsahai ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_ie_paye_api.';
7
8 -- 6015209
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< create_p46 >------------------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 procedure create_p46 ( p_effective_date IN DATE
14 , p_assignment_id IN NUMBER
15 , p_business_group_id IN NUMBER
16 , p_Tax_This_Employment IN NUMBER
17 , p_Previous_Employment_Start_Dt IN DATE
18 , p_Previous_Employment_End_Date IN DATE
19 , p_Pay_This_Employment IN NUMBER
20 , p_PAYE_Previous_Employer IN VARCHAR2
21 , p_P45P3_Or_P46 IN VARCHAR2
22 , p_Already_Submitted IN VARCHAR2
23 --, p_P45P3_Or_P46_Processed IN VARCHAR2
24 --13359423
25 ,p_usc_pay_this_employment in number
26 ,p_usc_tax_this_employment in Number
27 --13359423
28
29 ) is
30 CURSOR element_csr IS
31 SELECT element_type_id
32 FROM pay_element_types_f
33 WHERE element_name = 'IE P45P3_P46 Information'
34 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
35 AND nvl(legislation_code, 'IE') = 'IE'
36 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
37 --
38 element_rec element_csr%ROWTYPE;
39 --
40 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
41 SELECT input_value_id
42 FROM pay_input_values_f
43 WHERE element_type_id = p_element_type_id
44 AND name = p_name
45 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
46 AND nvl(legislation_code, 'IE') = 'IE'
47 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
48 --
49 input_val_rec1 input_val_csr%ROWTYPE;
50 input_val_rec2 input_val_csr%ROWTYPE;
51 input_val_rec3 input_val_csr%ROWTYPE;
52 input_val_rec4 input_val_csr%ROWTYPE;
53 input_val_rec5 input_val_csr%ROWTYPE;
54 input_val_rec6 input_val_csr%ROWTYPE;
55 input_val_rec7 input_val_csr%ROWTYPE;
56 input_val_rec8 input_val_csr%ROWTYPE;
57 input_val_rec9 input_val_csr%ROWTYPE; --13359423
58 --
59
60 CURSOR link_csr(p_element_type_id IN NUMBER) IS
61 SELECT links.element_link_id
62 FROM pay_element_links_f links, per_all_assignments_f assign
63 WHERE links.element_type_id = p_element_type_id
64 AND links.business_group_id=p_business_group_id
65 AND assign.assignment_id=p_assignment_id
66 AND (( links.payroll_id is not null
67 and links.payroll_id = assign.payroll_id)
68 OR ( links.link_to_all_payrolls_flag='Y'
69 and assign.payroll_id is not null)
70 OR ( links.payroll_id is null
71 and links.link_to_all_payrolls_flag='N')
72 OR links.job_id=assign.job_id
73 OR links.position_id=assign.position_id
74 OR links.people_group_id=assign.people_group_id
75 OR links.organization_id=assign.organization_id
76 OR links.grade_id=assign.grade_id
77 OR links.location_id=assign.location_id
78 OR links.pay_basis_id=assign.pay_basis_id
79 OR links.employment_category=assign.employment_category)
80 AND p_effective_date BETWEEN links.effective_start_date
81 AND links.effective_end_date
82 AND p_effective_date BETWEEN assign.effective_start_date
83 AND assign.effective_end_date; --16079457
84 --
85 link_rec link_csr%ROWTYPE;
86 --
87 l_element_entry_id NUMBER;
88 l_effective_start_date DATE;
89 l_effective_end_date DATE;
90 l_object_version_number NUMBER;
91 l_create_warning BOOLEAN := FALSE;
92 begin
93 --
94 -- Get Element information
95 --
96 OPEN element_csr;
97 FETCH element_csr INTO element_rec;
98 CLOSE element_csr;
99 --
100 -- Get Input Values
101 --
102 OPEN input_val_csr(element_rec.element_type_id, 'Tax This Employment');
103 FETCH input_val_csr INTO input_val_rec1;
104 CLOSE input_val_csr;
105 --
106 OPEN input_val_csr(element_rec.element_type_id, 'Previous Employment Start Date');
107 FETCH input_val_csr INTO input_val_rec2;
108 CLOSE input_val_csr;
109 --
110 OPEN input_val_csr(element_rec.element_type_id, 'Previous Employment End Date');
111 FETCH input_val_csr INTO input_val_rec3;
112 CLOSE input_val_csr;
113 --
114 OPEN input_val_csr(element_rec.element_type_id, 'Pay This Employment');
115 FETCH input_val_csr INTO input_val_rec4;
116 CLOSE input_val_csr;
117 --
118 OPEN input_val_csr(element_rec.element_type_id, 'PAYE Previous Employer');
119 FETCH input_val_csr INTO input_val_rec5;
120 CLOSE input_val_csr;
121 --
122 OPEN input_val_csr(element_rec.element_type_id, 'P45P3 Or P46');
123 FETCH input_val_csr INTO input_val_rec6;
124 CLOSE input_val_csr;
125 --
126 OPEN input_val_csr(element_rec.element_type_id, 'Already Submitted');
127 FETCH input_val_csr INTO input_val_rec7;
128 CLOSE input_val_csr;
129 --
130 --13359423
131 --
132 --
133 OPEN input_val_csr(element_rec.element_type_id, 'USC Pay This Employment');
134 FETCH input_val_csr INTO input_val_rec8;
135 CLOSE input_val_csr;
136 --
137 --
138 OPEN input_val_csr(element_rec.element_type_id, 'USC Tax This Employment');
139 FETCH input_val_csr INTO input_val_rec9;
140 CLOSE input_val_csr;
141 --13359423
142
143 /*OPEN input_val_csr(element_rec.element_type_id, 'P45P3 Or P46 Processed');
144 FETCH input_val_csr INTO input_val_rec8;
145 CLOSE input_val_csr;*/
146 --
147 -- Get element link information
148 --
149 OPEN link_csr(element_rec.element_type_id);
150 FETCH link_csr INTO link_rec;
151 CLOSE link_csr;
152
153 -- Call API To Create element entry.
154 py_element_entry_api.create_element_entry (
155 p_effective_date => p_effective_date,
156 p_business_group_id => p_business_group_id,
157 --p_original_entry_id => p_original_entry_id, -- default
158 p_assignment_id => p_assignment_id,
159 p_element_link_id => link_rec.element_link_id,
160 p_entry_type => 'E',
161 p_creator_type => 'F',
162 p_input_value_id1 => input_val_rec1.input_value_id,
163 p_input_value_id2 => input_val_rec2.input_value_id,
164 p_input_value_id3 => input_val_rec3.input_value_id,
165 p_input_value_id4 => input_val_rec4.input_value_id,
166 p_input_value_id5 => input_val_rec5.input_value_id,
167 p_input_value_id6 => input_val_rec6.input_value_id,
168 p_input_value_id7 => input_val_rec7.input_value_id,
169 --p_input_value_id8 => input_val_rec8.input_value_id,
170 --13359423
171 p_input_value_id8 => input_val_rec8.input_value_id,
172 p_input_value_id9 => input_val_rec9.input_value_id,
173 --13359423
174 p_entry_value1 => nvl(p_Tax_This_Employment,0),
175 p_entry_value2 => p_Previous_Employment_Start_Dt,
176 p_entry_value3 => p_Previous_Employment_End_Date,
177 p_entry_value4 => nvl(p_Pay_This_Employment,0),
178 p_entry_value5 => p_PAYE_Previous_Employer,
179 p_entry_value6 => nvl(p_P45P3_Or_P46,'N'),
180 p_entry_value7 => nvl(p_Already_Submitted,'N'),
181 --p_entry_value8 => nvl(p_P45P3_Or_P46_Processed,'N'),
182 --13359423
183 p_entry_value8 => nvl(p_usc_pay_this_employment,0),
184 p_entry_value9 => nvl(p_usc_tax_this_employment,0),
185 --13359423
186 p_effective_start_date => l_effective_start_date,
187 p_effective_end_date => l_effective_end_date,
188 p_element_entry_id => l_element_entry_id,
189 p_object_version_number => l_object_version_number,
190 p_create_warning => l_create_warning
191 );
192
193 end create_p46;
194 --6015209
195 -- ----------------------------------------------------------------------------
196 -- |--------------------------< update_p46 >------------------------------|
197 -- ----------------------------------------------------------------------------
198 procedure update_p46 ( p_effective_date IN DATE
199 , p_assignment_id IN NUMBER
200 , p_business_group_id IN NUMBER
201 , p_datetrack_update_mode IN VARCHAR2
202 , p_object_version_number IN OUT NOCOPY NUMBER
203 , p_paye_details_id IN NUMBER
204 , p_Tax_This_Employment IN NUMBER
205 , p_Previous_Employment_Start_Dt IN DATE
206 , p_Previous_Employment_End_Date IN DATE
207 , p_Pay_This_Employment IN NUMBER
208 , p_PAYE_Previous_Employer IN VARCHAR2
209 , p_P45P3_Or_P46 IN VARCHAR2
210 , p_Already_Submitted IN VARCHAR2
211 --, p_P45P3_Or_P46_Processed IN VARCHAR2
212 --13359423
213 ,p_usc_pay_this_employment in number
214 ,p_usc_tax_this_employment in Number
215 --13359423
216 ) is
217 CURSOR element_csr IS
218 SELECT element_type_id
219 FROM pay_element_types_f
220 WHERE element_name = 'IE P45P3_P46 Information'
221 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
222 AND nvl(legislation_code, 'IE') = 'IE'
223 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
224 --
225 element_rec element_csr%ROWTYPE;
226 --
227 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
228 SELECT input_value_id
229 FROM pay_input_values_f
230 WHERE element_type_id = p_element_type_id
231 AND name = p_name
232 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
233 AND nvl(legislation_code, 'IE') = 'IE'
234 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
235 --
236 input_val_rec1 input_val_csr%ROWTYPE;
237 input_val_rec2 input_val_csr%ROWTYPE;
238 input_val_rec3 input_val_csr%ROWTYPE;
239 input_val_rec4 input_val_csr%ROWTYPE;
240 input_val_rec5 input_val_csr%ROWTYPE;
241 input_val_rec6 input_val_csr%ROWTYPE;
242 input_val_rec7 input_val_csr%ROWTYPE;
243 input_val_rec8 input_val_csr%ROWTYPE;
244 input_val_rec9 input_val_csr%ROWTYPE; --13359423
245 --
246 l_tax_yr_start_date date;
247 CURSOR entry_csr IS
248 SELECT pee.element_entry_id, pee.effective_start_date, pee.object_version_number
249 FROM pay_element_entries_f pee,
250 pay_element_types_f pet,
251 pay_element_links_f pel
252 WHERE pee.element_link_id = pel.element_link_id AND
253 pee.assignment_id = p_assignment_id AND
254 --p_effective_date between pee.effective_start_date and pee.effective_end_date AND
255 --pee.effective_start_date >= l_tax_yr_start_date and
256 --pee.effective_end_date <= add_months(l_tax_yr_start_date,12) AND
257 pel.element_type_id = pet.element_type_id AND
258 pel.business_group_id = p_business_group_id AND
259 pet.element_name = 'IE P45P3_P46 Information' AND
260 NVL(pet.business_group_id, p_business_group_id) = p_business_group_id AND
261 pet.legislation_code = 'IE' ;
262
263 --rec_entry_csr entry_csr%rowtype; --11788742
264 --
265 l_element_entry_id NUMBER;
266 l_effective_start_date DATE;
267 l_effective_end_date DATE;
268 l_update_warning BOOLEAN := FALSE;
269 l_object_version_number NUMBER;
270 --
271 /*
272 CURSOR entry_csr_any IS
273 SELECT pee.element_entry_id, pee.effective_start_date
274 FROM pay_element_entries_f pee,
275 pay_element_types_f pet,
276 pay_element_links_f pel
277 WHERE pee.element_link_id = pel.element_link_id AND
278 pee.assignment_id = p_assignment_id AND
279 --p_effective_date between pee.effective_start_date and pee.effective_end_date AND
280 pel.element_type_id = pet.element_type_id AND
281 pel.business_group_id = p_business_group_id AND
282 pet.element_name = 'IE P45P3_P46 Information' AND
283 NVL(pet.business_group_id, p_business_group_id) = p_business_group_id AND
284 pet.legislation_code = 'IE' ;
285
286 rec_entry_csr_any entry_csr_any%rowtype;
287 */
288 --
289 CURSOR cur_p45p3_eff_start_date IS
290 SELECT min(effective_start_date)
291 FROM pay_ie_paye_details_f
292 WHERE paye_details_id = p_paye_details_id;
293 --AND p_effective_date BETWEEN effective_start_date AND effective_end_date
294 --AND effective_start_date >= l_tax_yr_start_date
295 --AND effective_end_date <= add_months(l_tax_yr_start_date,12);
296 --
297 l_p45p3_eff_start_date pay_ie_paye_details_f.effective_start_date%type;
298 l_datetrack_update_mode VARCHAR2(100);
299
300 begin
301
302 l_tax_yr_start_date := to_date('01/01/' || to_char(p_effective_date,'YYYY'),'DD/MM/YYYY');
303 --
304 -- Get Element information
305 --
306 OPEN element_csr;
307 FETCH element_csr INTO element_rec;
308 CLOSE element_csr;
309 --
310 -- Get Input Values
311 --
312 OPEN input_val_csr(element_rec.element_type_id, 'Tax This Employment');
313 FETCH input_val_csr INTO input_val_rec1;
314 CLOSE input_val_csr;
315 --
316 OPEN input_val_csr(element_rec.element_type_id, 'Previous Employment Start Date');
317 FETCH input_val_csr INTO input_val_rec2;
318 CLOSE input_val_csr;
319 --
320 OPEN input_val_csr(element_rec.element_type_id, 'Previous Employment End Date');
321 FETCH input_val_csr INTO input_val_rec3;
322 CLOSE input_val_csr;
323 --
324 OPEN input_val_csr(element_rec.element_type_id, 'Pay This Employment');
325 FETCH input_val_csr INTO input_val_rec4;
326 CLOSE input_val_csr;
327 --
328 OPEN input_val_csr(element_rec.element_type_id, 'PAYE Previous Employer');
329 FETCH input_val_csr INTO input_val_rec5;
330 CLOSE input_val_csr;
331 --
332 OPEN input_val_csr(element_rec.element_type_id, 'P45P3 Or P46');
333 FETCH input_val_csr INTO input_val_rec6;
334 CLOSE input_val_csr;
335 --
336 OPEN input_val_csr(element_rec.element_type_id, 'Already Submitted');
337 FETCH input_val_csr INTO input_val_rec7;
338 CLOSE input_val_csr;
339 --
340 --13359423
341 --
342 OPEN input_val_csr(element_rec.element_type_id, 'USC Pay This Employment');
343 FETCH input_val_csr INTO input_val_rec8;
344 CLOSE input_val_csr;
345 --
346 --
347 OPEN input_val_csr(element_rec.element_type_id, 'USC Tax This Employment');
348 FETCH input_val_csr INTO input_val_rec9;
349 CLOSE input_val_csr;
350 --
351 --13359423
352 /*OPEN input_val_csr(element_rec.element_type_id, 'P45P3 Or P46 Processed');
353 FETCH input_val_csr INTO input_val_rec8;
354 CLOSE input_val_csr;*/
355 --
356 --11788742
357 /*
358 open entry_csr;
359 fetch entry_csr into rec_entry_csr;
360 close entry_csr;
361 */
362 --11788742
363 --
364 OPEN cur_p45p3_eff_start_date;
365 FETCH cur_p45p3_eff_start_date INTO l_p45p3_eff_start_date;
366 CLOSE cur_p45p3_eff_start_date;
367 --
368
369 --FOR element_entries in entry_csr --11788742
370 FOR rec_entry_csr in entry_csr --11788742
371 LOOP --11788742
372 IF rec_entry_csr.element_entry_id IS NOT NULL THEN
373 -- Call Update element entry API
374 -- Datetrack records are not required. so setting the mode to correction always.
375 l_datetrack_update_mode := 'CORRECTION';
376 l_object_version_number := rec_entry_csr.object_version_number;
377
378 py_element_entry_api.update_element_entry
379 (p_validate => false
380 ,p_datetrack_update_mode => l_datetrack_update_mode --p_datetrack_update_mode
381 ,p_effective_date => rec_entry_csr.effective_start_date --l_p45p3_eff_start_date --p_effective_date --11788742
382 ,p_business_group_id => p_business_group_id
383 ,p_element_entry_id => rec_entry_csr.element_entry_id
384 ,p_object_version_number => l_object_version_number --p_object_version_number
385 ,p_input_value_id1 => input_val_rec1.input_value_id
386 ,p_input_value_id2 => input_val_rec2.input_value_id
387 ,p_input_value_id3 => input_val_rec3.input_value_id
388 ,p_input_value_id4 => input_val_rec4.input_value_id
389 ,p_input_value_id5 => input_val_rec5.input_value_id
390 ,p_input_value_id6 => input_val_rec6.input_value_id
391 ,p_input_value_id7 => input_val_rec7.input_value_id
392 --,p_input_value_id8 => input_val_rec8.input_value_id
393 --13359423
394 ,p_input_value_id8 => input_val_rec8.input_value_id
395 ,p_input_value_id9 => input_val_rec9.input_value_id
396 --13359423
397 ,p_entry_value1 => nvl(p_Tax_This_Employment,0)
398 ,p_entry_value2 => p_Previous_Employment_Start_Dt
399 ,p_entry_value3 => p_Previous_Employment_End_Date
400 ,p_entry_value4 => nvl(p_Pay_This_Employment,0)
401 ,p_entry_value5 => p_PAYE_Previous_Employer
402 ,p_entry_value6 => nvl(p_P45P3_Or_P46,'N')
403 ,p_entry_value7 => nvl(p_Already_Submitted,'N')
404 --,p_entry_value8 => nvl(p_P45P3_Or_P46_Processed,'N')
405 --13359423
406 , p_entry_value8 => nvl(p_usc_pay_this_employment,0)
407 , p_entry_value9 => nvl(p_usc_tax_this_employment,0)
408 --13359423
409 ,p_effective_start_date => l_effective_start_date
410 ,p_effective_end_date => l_effective_end_date
411 ,p_update_warning => l_update_warning
412 );
413 ELSE
414 --OPEN entry_csr_any;
415 --FETCH entry_csr_any INTO rec_entry_csr_any;
416 --IF entry_csr_any%NOTFOUND THEN
417 -- if mode is Correction then create the record with same eff start date as in pay_ie_paye_details_f table
418 -- if mode is updation then create the record with effective date passed.
419 --
420 /*
421 IF p_datetrack_update_mode = 'CORRECTION' THEN
422 OPEN cur_p45p3_eff_start_date;
423 FETCH cur_p45p3_eff_start_date INTO l_p45p3_eff_start_date;
424 CLOSE cur_p45p3_eff_start_date;
425 ELSIF p_datetrack_update_mode = 'UPDATE' THEN
426 l_p45p3_eff_start_date := p_effective_date;
427 END IF;
428 */
429 --
430 IF ( nvl(p_Tax_This_Employment,0) <> 0 OR
431 nvl(p_Pay_This_Employment,0) <> 0 OR
432 p_Previous_Employment_Start_Dt IS NOT NULL OR
433 p_Previous_Employment_End_Date IS NOT NULL OR
434 p_PAYE_Previous_Employer IS NOT NULL OR
435 p_Already_Submitted IS NOT NULL OR
436 p_P45P3_Or_P46 IS NOT NULL
437 --13359423
438 OR nvl(p_usc_pay_this_employment,0) <> 0 OR
439 nvl(p_usc_tax_this_employment,0) <> 0
440 --13359423
441 )
442 THEN
443 create_p46 ( l_p45p3_eff_start_date
444 , p_assignment_id
445 , p_business_group_id
446 , p_Tax_This_Employment
447 , p_Previous_Employment_Start_Dt
448 , p_Previous_Employment_End_Date
449 , p_Pay_This_Employment
450 , p_PAYE_Previous_Employer
451 , p_P45P3_Or_P46
452 , p_Already_Submitted
453 --, p_P45P3_Or_P46_Processed
454 --13359423
455 ,p_usc_pay_this_employment
456 ,p_usc_tax_this_employment
457 --13359423
458 );
459 END IF;
460 --END IF;
461 --CLOSE entry_csr_any;
462 END IF;
463 END LOOP; --11788742
464
465 end update_p46;
466 --6015209
467 -- ----------------------------------------------------------------------------
468 -- |--------------------------< delete_p46 >------------------------------|
469 -- ----------------------------------------------------------------------------
470 --6015209
471 procedure delete_p46 ( p_effective_date IN DATE
472 , p_assignment_id IN NUMBER
473 , p_business_group_id IN NUMBER
474 , p_datetrack_delete_mode IN VARCHAR2
475 , p_object_version_number IN OUT NOCOPY NUMBER
476 ) is
477 l_tax_yr_start_date date;
478 -- eff date condition is not used since we are not keeping the datetrack records.
479 CURSOR entry_csr IS
480 SELECT pee.element_entry_id, pee.effective_start_date, pee.object_version_number
481 FROM pay_element_entries_f pee,
482 pay_element_types_f pet,
483 pay_element_links_f pel
484 WHERE pee.element_link_id = pel.element_link_id AND
485 pee.assignment_id = p_assignment_id AND
486 --p_effective_date between pee.effective_start_date and pee.effective_end_date AND
487 pel.element_type_id = pet.element_type_id AND
488 pel.business_group_id = p_business_group_id AND
489 pet.element_name = 'IE P45P3_P46 Information' AND
490 NVL(pet.business_group_id, p_business_group_id) = p_business_group_id AND
491 pet.legislation_code = 'IE' ;
492
493 rec_entry_csr entry_csr%rowtype;
494
495 l_effective_start_date DATE;
496 l_effective_end_date DATE;
497 l_delete_warning BOOLEAN := FALSE;
498
499 BEGIN
500 -- Get ELement Entry Information
501 -- derive the tax year start date givevn the effective data
502 --l_tax_yr_start_date := to_date('01/01/' || to_char(p_effective_date,'YYYY'),'DD/MM/YYYY');
503 open entry_csr;
504 fetch entry_csr into rec_entry_csr;
505 close entry_csr;
506 p_object_version_number := rec_entry_csr.object_version_number;
507
508 --
509 IF rec_entry_csr.element_entry_id IS NOT NULL THEN
510 -- Call Delete element entry API
511 py_element_entry_api.delete_element_entry
512 (p_validate => false
513 ,p_datetrack_delete_mode => p_datetrack_delete_mode
514 ,p_effective_date => rec_entry_csr.effective_start_date
515 ,p_element_entry_id => rec_entry_csr.element_entry_id
516 ,p_object_version_number => p_object_version_number
517 ,p_effective_start_date => l_effective_start_date
518 ,p_effective_end_date => l_effective_end_date
519 ,p_delete_warning => l_delete_warning
520 );
521 END IF;
522 --
523 end delete_p46;
524 --6015209
525 --
526 -- ----------------------------------------------------------------------------
527 -- |--------------------------< create_bal_adj >------------------------------|
528 -- ----------------------------------------------------------------------------
529 --
530 procedure create_bal_adj ( p_effective_date IN DATE
531 , p_assignment_id IN NUMBER
532 , p_business_group_id IN NUMBER
533 , p_tax_deducted_to_date IN NUMBER
534 , p_pay_to_date IN NUMBER
535 , p_disability_benefit IN NUMBER
536 , p_lump_sum_payment IN NUMBER
537 --13359423
538 , p_total_usc_pay_todate IN NUMBER
539 , p_total_usc_tax_todate IN NUMBER
540 --13359423
541 ) is
542 CURSOR element_csr IS
543 SELECT element_type_id
544 FROM pay_element_types_f
545 WHERE element_name = 'IE P45 Information'
546 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
547 AND nvl(legislation_code, 'IE') = 'IE'
548 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
549 --
550 element_rec element_csr%ROWTYPE;
551 --
552 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
553 SELECT input_value_id
554 FROM pay_input_values_f
555 WHERE element_type_id = p_element_type_id
556 AND name = p_name
557 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
558 AND nvl(legislation_code, 'IE') = 'IE'
559 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
560 --
561 input_val_rec1 input_val_csr%ROWTYPE;
562 input_val_rec2 input_val_csr%ROWTYPE;
563 input_val_rec3 input_val_csr%ROWTYPE;
564 input_val_rec4 input_val_csr%ROWTYPE;
565 --13359423
566 input_val_rec5 input_val_csr%ROWTYPE;
567 input_val_rec6 input_val_csr%ROWTYPE;
568 --13359423
569 --
570 -- BUG 2616218
571 CURSOR link_csr(p_element_type_id IN NUMBER) IS
572 SELECT links.element_link_id
573 FROM pay_element_links_f links, per_all_assignments_f assign
574 WHERE links.element_type_id = p_element_type_id
575 AND links.business_group_id=p_business_group_id
576 AND assign.assignment_id=p_assignment_id
577 AND (( links.payroll_id is not null
578 and links.payroll_id = assign.payroll_id)
579 OR ( links.link_to_all_payrolls_flag='Y'
580 and assign.payroll_id is not null)
581 OR ( links.payroll_id is null
582 and links.link_to_all_payrolls_flag='N')
583 OR links.job_id=assign.job_id
584 OR links.position_id=assign.position_id
585 OR links.people_group_id=assign.people_group_id
586 OR links.organization_id=assign.organization_id
587 OR links.grade_id=assign.grade_id
588 OR links.location_id=assign.location_id
589 OR links.pay_basis_id=assign.pay_basis_id
590 OR links.employment_category=assign.employment_category)
591 AND p_effective_date BETWEEN links.effective_start_date
592 AND links.effective_end_date
593 AND p_effective_date BETWEEN assign.effective_start_date
594 AND assign.effective_end_date; --16079457
595 --
596 link_rec link_csr%ROWTYPE;
597 --
598 l_element_entry_id NUMBER;
599 l_effective_start_date DATE;
600 l_effectiver_end_date DATE;
601 l_object_version_number NUMBER;
602 l_create_warning BOOLEAN := FALSE;
603 begin
604 --
605 -- Get Element information
606 --
607 OPEN element_csr;
608 FETCH element_csr INTO element_rec;
609 CLOSE element_csr;
610 --
611 -- Get Input Values
612 --
613 OPEN input_val_csr(element_rec.element_type_id, 'Tax Deducted To Date');
614 FETCH input_val_csr INTO input_val_rec1;
615 CLOSE input_val_csr;
616 --
617 OPEN input_val_csr(element_rec.element_type_id, 'Pay To Date');
618 FETCH input_val_csr INTO input_val_rec2;
619 CLOSE input_val_csr;
620 --
621 OPEN input_val_csr(element_rec.element_type_id, 'Lump Sum Payment');
622 FETCH input_val_csr INTO input_val_rec3;
623 CLOSE input_val_csr;
624 --
625 OPEN input_val_csr(element_rec.element_type_id, 'Disability Benefit');
626 FETCH input_val_csr INTO input_val_rec4;
627 CLOSE input_val_csr;
628 --
629 --13359423
630 --
631 OPEN input_val_csr(element_rec.element_type_id, 'USC Pay To Date');
632 FETCH input_val_csr INTO input_val_rec5;
633 CLOSE input_val_csr;
634 --
635 --
636 OPEN input_val_csr(element_rec.element_type_id, 'USC Tax To Date');
637 FETCH input_val_csr INTO input_val_rec6;
638 CLOSE input_val_csr;
639 --
640 --13359423
641 -- Get element link information
642 --
643 OPEN link_csr(element_rec.element_type_id);
644 FETCH link_csr INTO link_rec;
645 CLOSE link_csr;
646 --
647 -- Call API To Create Balance Adjustment
648 --
649 pay_balance_adjustment_api.create_adjustment (
650 p_validate => false,
651 p_effective_date => p_effective_date,
652 p_assignment_id => p_assignment_id,
653 p_consolidation_set_id => NULL,
654 p_element_link_id => link_rec.element_link_id,
655 p_input_value_id1 => input_val_rec1.input_value_id,
656 p_input_value_id2 => input_val_rec2.input_value_id,
657 p_input_value_id3 => input_val_rec3.input_value_id,
658 p_input_value_id4 => input_val_rec4.input_value_id,
659 --13359423
660 p_input_value_id5 => input_val_rec5.input_value_id,
661 p_input_value_id6 => input_val_rec6.input_value_id,
662 --13359423
663 p_entry_value1 => nvl(p_tax_deducted_to_date,0),
664 p_entry_value2 => nvl(p_pay_to_date,0),
665 p_entry_value3 => nvl(p_lump_sum_payment,0),
666 p_entry_value4 => nvl(p_disability_benefit,0),
667 --13359423
668 p_entry_value5 => nvl(p_total_usc_pay_todate,0),
669 p_entry_value6 => nvl(p_total_usc_tax_todate,0),
670 --13359423
671 -- Element entry information.
672 p_element_entry_id => l_element_entry_id,
673 p_effective_start_date => l_effective_start_date,
674 p_effective_end_date => l_effectiver_end_date,
675 p_object_version_number => l_object_version_number,
676 p_create_warning => l_create_warning );
677 end create_bal_adj;
678 --
679 -- ----------------------------------------------------------------------------
680 -- |--------------------------< delete_bal_adj >------------------------------|
681 -- ----------------------------------------------------------------------------
682 --
683 PROCEDURE delete_bal_adj (p_effective_date IN DATE
684 ,p_business_group_id IN NUMBER
685 ,p_assignment_id IN NUMBER ) IS
686 /* commented to fix bug 3013304
687 we now delete all the balances whihc are created within the tax year
688 rather than just deleting the one.
689
690 CURSOR entry_csr IS
691 SELECT pee.element_entry_id
692 FROM pay_element_entries_f pee, pay_element_types_f pet, pay_element_links_f pel
693 WHERE pee.element_link_id = pel.element_link_id
694 AND pee.assignment_id = p_assignment_id
695 AND p_effective_date BETWEEN pee.effective_start_Date AND pee.effective_end_Date
696 AND pel.element_type_id = pet.element_type_id
697 AND pel.business_group_id = p_business_group_id
698 AND p_effective_date BETWEEN pel.effective_start_Date AND pel.effective_end_Date
699 AND pet.element_name = 'IE P45 Information'
700 AND nvl(pet.business_group_id, p_business_group_id) = p_business_group_id
701 AND pet.legislation_code = 'IE'
702 AND p_effective_date BETWEEN pet.effective_start_Date AND pet.effective_end_Date;
703 */
704 --
705 -- entry_rec entry_csr%ROWTYPE;
706 l_tax_yr_start_date date;
707 CURSOR entry_csr IS
708 SELECT pee.element_entry_id, pee.effective_start_date
709 FROM pay_element_entries_f pee,
710 pay_element_types_f pet,
711 pay_element_links_f pel
712 WHERE pee.element_link_id = pel.element_link_id AND
713 pee.assignment_id = p_assignment_id AND
714 pee.effective_start_date >= l_tax_yr_start_date and
715 pee.effective_end_date <= add_months(l_tax_yr_start_date,12) AND
716 pel.element_type_id = pet.element_type_id AND
717 pel.business_group_id = p_business_group_id AND
718 -- pet.element_name = 'IE P45 Information' AND
719 pet.element_name in('IE P45 Information', 'Setup P45 Element') AND
720 NVL(pet.business_group_id, p_business_group_id) = p_business_group_id AND
721 pet.legislation_code = 'IE' ;
722 BEGIN
723 -- Get ELement Entry Information
724 -- derive the tax year start date givevn the effective data
725 l_tax_yr_start_date := to_date('01/01/' || to_char(p_effective_date,'YYYY'),'DD/MM/YYYY');
726 for element_entries in entry_csr loop
727 --
728 IF element_entries.element_entry_id IS NOT NULL THEN
729 -- Call Delete Adjustment API to rollback payroll action for this element entry
730 pay_balance_adjustment_api.delete_adjustment (
731 p_validate => false,
732 p_effective_date => element_entries.effective_start_date,
733 p_element_entry_id => element_entries.element_entry_id );
734 END IF;
735 end loop;
736 END delete_bal_adj;
737 --
738 -- ----------------------------------------------------------------------------
739 -- |------------------------< create_ie_paye_details >------------------------|
740 -- ----------------------------------------------------------------------------
741 --
742 procedure create_ie_paye_details
743 (p_validate in boolean
744 ,p_effective_date in date
745 ,p_assignment_id in number
746 ,p_info_source in varchar2
747 ,p_tax_basis in varchar2
748 ,p_certificate_start_date in date
749 ,p_tax_assess_basis in varchar2
750 ,p_certificate_issue_date in date
751 ,p_certificate_end_date in date
752 ,p_weekly_tax_credit in number
753 ,p_weekly_std_rate_cut_off in number
754 ,p_monthly_tax_credit in number
755 ,p_monthly_std_rate_cut_off in number
756 ,p_tax_deducted_to_date in number
757 ,p_pay_to_date in number
758 ,p_disability_benefit in number
759 ,p_lump_sum_payment in number
760 ,p_paye_details_id out nocopy number
761 ,p_object_version_number out nocopy number
762 ,p_effective_start_date out nocopy date
763 ,p_effective_end_date out nocopy date
764 ,p_Tax_This_Employment in Number
765 ,p_Previous_Employment_Start_Dt in date
766 ,p_Previous_Employment_End_Date in date
767 ,p_Pay_This_Employment in number
768 ,p_PAYE_Previous_Employer in varchar2
769 ,p_P45P3_Or_P46 in varchar2
770 ,p_Already_Submitted in varchar2
771 --,p_P45P3_Or_P46_Processed in varchar2
772 --13359423
773 ,p_yrly_tax_cred in NUMBER DEFAULT NULL
774 ,p_yrly_tax_rate_1 in number default null
775 ,p_yrly_tax_rate_2 in number default null
776 ,p_mthly_tax_rate_2 in number default null
777 ,p_wkly_tax_rate_2 in number default null
778 ,p_tax_rate_3 in number default null
779 ,p_yrly_tax_rate_3 in number default null
780 ,p_mthly_tax_rate_3 in number default null
781 ,p_wkly_tax_rate_3 in number default null
782 ,p_tax_rate_4 in number default null
783 ,p_yrly_tax_rate_4 in number default null
784 ,p_mthly_tax_rate_4 in number default null
785 ,p_wkly_tax_rate_4 in number default null
786 ,p_tax_rate_5 in number default null
787 ,p_in_exempt_usc in varchar2 default null
788 ,p_total_usc_pay_todate in number default null
789 ,p_total_usc_tax_todate in number default null
790 ,p_usc_rate_1 in number default null
791 ,p_usc_yrly_cutoff_1 in number default null
792 ,p_usc_mthly_cutoff_1 in number default null
793 ,p_usc_wkly_cutoff_1 in number default null
794 ,p_usc_rate_2 in number default null
795 ,p_usc_yrly_cutoff_2 in number default null
796 ,p_usc_mthly_cutoff_2 in number default null
797 ,p_usc_wkly_cutoff_2 in number default null
798 ,p_usc_rate_3 in number default null
799 ,p_usc_yrly_cutoff_3 in number default null
800 ,p_usc_mthly_cutoff_3 in number default null
801 ,p_usc_wkly_cutoff_3 in number default null
802 ,p_usc_rate_4 in number default null
803 ,p_usc_yrly_cutoff_4 in number default null
804 ,p_usc_mthly_cutoff_4 in number default null
805 ,p_usc_wkly_cutoff_4 in number default null
806 ,p_usc_rate_5 in number default null
807 ,p_usc_tax_basis in varchar2 default null
808 ,p_usc_info_source in varchar2 default null
809 ,p_usc_pay_this_employment in number default null
810 ,p_usc_tax_this_employment in number default null
811 --13359423
812 ) is
813 --
814 -- Declare cursors and local variables
815 --
816
817 l_proc varchar2(72) := g_package||'create_ie_paye_details';
818 l_certificate_start_date date;
819 l_certificate_issue_date date;
820 l_certificate_end_date date;
821 l_paye_details_id number;
822 l_object_version_number number;
823 l_effective_start_date date;
824 l_effective_end_Date date;
825 l_comm_period_no number;
826 l_request_id number;
827 l_program_id number;
828 l_prog_appl_id number;
829 l_business_group_id number;
830 --
831 CURSOR business_group_csr IS
832 SELECT business_group_id
833 FROM per_all_assignments_f
834 WHERE assignment_id = p_assignment_id
835 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
836 --
837 --
838 begin
839 hr_utility.set_location('Entering:'|| l_proc, 10);
840 --
841 -- Issue a savepoint
842 --
843 savepoint create_ie_paye_details;
844 --
845 -- Truncate the time portion from all IN date parameters
846 --
847 l_certificate_start_date := trunc(p_certificate_start_date);
848 l_certificate_issue_date := trunc(p_certificate_issue_date);
849 l_certificate_end_date := trunc(p_certificate_end_date);
850 --
851 -- Get business_group_id
852 --
853 OPEN business_group_csr;
854 FETCH business_group_csr INTO l_business_group_id;
855 CLOSE business_group_csr;
856 --
857 -- Call Before Process User Hook
858 --
859 begin
860 pay_ie_paye_bk1.create_ie_paye_details_b
861 (p_effective_date => p_effective_date
862 ,p_business_group_id => l_business_group_id
863 ,p_assignment_id => p_assignment_id
864 ,p_info_source => p_info_source
865 ,p_tax_basis => p_tax_basis
866 ,p_certificate_start_date => p_certificate_start_date
867 ,p_tax_assess_basis => p_tax_assess_basis
868 ,p_certificate_issue_date => p_certificate_issue_date
869 ,p_certificate_end_date => p_certificate_end_date
870 ,p_weekly_tax_credit => p_weekly_tax_credit
871 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
872 ,p_monthly_tax_credit => p_monthly_tax_credit
873 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
874 ,p_tax_deducted_to_date => p_tax_deducted_to_date
875 ,p_pay_to_date => p_pay_to_date
876 ,p_disability_benefit => p_disability_benefit
877 ,p_lump_sum_payment => p_lump_sum_payment
878 ,p_Tax_This_Employment => p_Tax_This_Employment
879 ,p_Previous_Employment_Start_Dt => p_Previous_Employment_Start_Dt
880 ,p_Previous_Employment_End_Date => p_Previous_Employment_End_Date
881 ,p_Pay_This_Employment => p_Pay_This_Employment
882 ,p_PAYE_Previous_Employer => p_PAYE_Previous_Employer
883 ,p_P45P3_Or_P46 => p_P45P3_Or_P46
884 ,p_Already_Submitted => p_Already_Submitted
885 --,p_P45P3_Or_P46_Processed => p_P45P3_Or_P46_Processed
886 --13359423
887 ,p_yrly_tax_cred => p_yrly_tax_cred
888 ,p_yrly_tax_rate_1 => p_yrly_tax_rate_1
889 ,p_yrly_tax_rate_2 => p_yrly_tax_rate_2
890 ,p_mthly_tax_rate_2 => p_mthly_tax_rate_2
891 ,p_wkly_tax_rate_2 => p_wkly_tax_rate_2
892 ,p_tax_rate_3 => p_tax_rate_3
893 ,p_yrly_tax_rate_3 => p_yrly_tax_rate_3
894 ,p_mthly_tax_rate_3 => p_mthly_tax_rate_3
895 ,p_wkly_tax_rate_3 => p_wkly_tax_rate_3
896 ,p_tax_rate_4 => p_tax_rate_4
897 ,p_yrly_tax_rate_4 => p_yrly_tax_rate_4
898 ,p_mthly_tax_rate_4 => p_mthly_tax_rate_4
899 ,p_wkly_tax_rate_4 => p_wkly_tax_rate_4
900 ,p_tax_rate_5 => p_tax_rate_5
901 ,p_in_exempt_usc => p_in_exempt_usc
902 ,p_total_usc_pay_todate => p_total_usc_pay_todate
903 ,p_total_usc_tax_todate => p_total_usc_tax_todate
904 ,p_usc_rate_1 => p_usc_rate_1
905 ,p_usc_yrly_cutoff_1 => p_usc_yrly_cutoff_1
906 ,p_usc_mthly_cutoff_1 => p_usc_mthly_cutoff_1
907 ,p_usc_wkly_cutoff_1 => p_usc_wkly_cutoff_1
908 ,p_usc_rate_2 => p_usc_rate_2
909 ,p_usc_yrly_cutoff_2 => p_usc_yrly_cutoff_2
910 ,p_usc_mthly_cutoff_2 => p_usc_mthly_cutoff_2
911 ,p_usc_wkly_cutoff_2 => p_usc_wkly_cutoff_2
912 ,p_usc_rate_3 => p_usc_rate_3
913 ,p_usc_yrly_cutoff_3 => p_usc_yrly_cutoff_3
914 ,p_usc_mthly_cutoff_3 => p_usc_mthly_cutoff_3
915 ,p_usc_wkly_cutoff_3 => p_usc_wkly_cutoff_3
916 ,p_usc_rate_4 => p_usc_rate_4
917 ,p_usc_yrly_cutoff_4 => p_usc_yrly_cutoff_4
918 ,p_usc_mthly_cutoff_4 => p_usc_mthly_cutoff_4
919 ,p_usc_wkly_cutoff_4 => p_usc_wkly_cutoff_4
920 ,p_usc_rate_5 => p_usc_rate_5
921 ,p_usc_tax_basis => p_usc_tax_basis
922 ,p_usc_info_source => p_usc_info_source
923 ,p_usc_pay_this_employment => p_usc_pay_this_employment
924 ,p_usc_tax_this_employment => p_usc_tax_this_employment
925 --13359423
926 );
927 exception
928 when hr_api.cannot_find_prog_unit then
929 hr_api.cannot_find_prog_unit_error
930 (p_module_name => 'create_ie_paye_details'
931 ,p_hook_type => 'BP'
932 );
933 end;
934 --
935 -- Process Logic
936 --
937 -- Set parameter values
938 --
939 l_request_id := fnd_global.conc_request_id;
940 l_prog_appl_id := fnd_global.prog_appl_id;
941 l_program_id := fnd_global.conc_program_id;
942 l_comm_period_no := pay_ipd_bus.get_comm_period_no(p_effective_date => p_effective_date , p_assignment_id => p_assignment_id);
943 --
944 -- Insert record in pay_ie_paye_details_f
945 --
946 pay_ipd_ins.ins
947 ( p_effective_date => p_effective_date
948 ,p_assignment_id => p_assignment_id
949 ,p_info_source => p_info_source
950 ,p_comm_period_no => l_comm_period_no
951 ,p_tax_basis => p_tax_basis
952 ,p_certificate_start_date => p_certificate_start_Date
953 ,p_tax_assess_basis => p_tax_assess_basis
954 ,p_certificate_end_date => p_certificate_end_date
955 ,p_weekly_tax_credit => p_weekly_tax_credit
956 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
957 ,p_monthly_tax_credit => p_monthly_tax_credit
958 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
959 ,p_request_id => l_request_id
960 ,p_program_application_id => l_prog_appl_id
961 ,p_program_id => l_program_id
962 ,p_program_update_date => sysdate
963 ,p_paye_details_id => l_paye_details_id
964 ,p_object_version_number => l_object_version_number
965 ,p_effective_start_date => l_effective_start_date
966 ,p_effective_end_date => l_effective_end_date
967 ,p_certificate_issue_date => p_certificate_issue_Date
968 --13359423
969 ,p_yrly_tax_cred => p_yrly_tax_cred
970 ,p_yrly_tax_rate_1 => p_yrly_tax_rate_1
971 ,p_yrly_tax_rate_2 => p_yrly_tax_rate_2
972 ,p_mthly_tax_rate_2 => p_mthly_tax_rate_2
973 ,p_wkly_tax_rate_2 => p_wkly_tax_rate_2
974 ,p_tax_rate_3 => p_tax_rate_3
975 ,p_yrly_tax_rate_3 => p_yrly_tax_rate_3
976 ,p_mthly_tax_rate_3 => p_mthly_tax_rate_3
977 ,p_wkly_tax_rate_3 => p_wkly_tax_rate_3
978 ,p_tax_rate_4 => p_tax_rate_4
979 ,p_yrly_tax_rate_4 => p_yrly_tax_rate_4
980 ,p_mthly_tax_rate_4 => p_mthly_tax_rate_4
981 ,p_wkly_tax_rate_4 => p_wkly_tax_rate_4
982 ,p_tax_rate_5 => p_tax_rate_5
983 ,p_in_exempt_usc => p_in_exempt_usc
984 ,p_total_usc_pay_todate => p_total_usc_pay_todate
985 ,p_total_usc_tax_todate => p_total_usc_tax_todate
986 ,p_usc_rate_1 => p_usc_rate_1
987 ,p_usc_yrly_cutoff_1 => p_usc_yrly_cutoff_1
988 ,p_usc_mthly_cutoff_1 => p_usc_mthly_cutoff_1
989 ,p_usc_wkly_cutoff_1 => p_usc_wkly_cutoff_1
990 ,p_usc_rate_2 => p_usc_rate_2
991 ,p_usc_yrly_cutoff_2 => p_usc_yrly_cutoff_2
992 ,p_usc_mthly_cutoff_2 => p_usc_mthly_cutoff_2
993 ,p_usc_wkly_cutoff_2 => p_usc_wkly_cutoff_2
994 ,p_usc_rate_3 => p_usc_rate_3
995 ,p_usc_yrly_cutoff_3 => p_usc_yrly_cutoff_3
996 ,p_usc_mthly_cutoff_3 => p_usc_mthly_cutoff_3
997 ,p_usc_wkly_cutoff_3 => p_usc_wkly_cutoff_3
998 ,p_usc_rate_4 => p_usc_rate_4
999 ,p_usc_yrly_cutoff_4 => p_usc_yrly_cutoff_4
1000 ,p_usc_mthly_cutoff_4 => p_usc_mthly_cutoff_4
1001 ,p_usc_wkly_cutoff_4 => p_usc_wkly_cutoff_4
1002 ,p_usc_rate_5 => p_usc_rate_5
1003 ,p_usc_tax_basis => p_usc_tax_basis
1004 ,p_usc_info_source => p_usc_info_source
1005 --13359423
1006 );
1007 --
1008 -- Check if adjustments need to be created
1009 IF ( nvl(p_tax_deducted_to_date,0) <> 0 OR
1010 nvl(p_pay_to_date,0) <> 0 OR
1011 nvl(p_disability_benefit,0) <> 0 OR
1012 nvl(p_lump_sum_payment,0) <> 0
1013 --13359423
1014 OR nvl(p_total_usc_pay_todate,0) <> 0
1015 OR nvl (p_total_usc_tax_todate,0) <> 0
1016 --13359423
1017 ) THEN
1018
1019 -- delete any bal adj entires which could have
1020 -- been crreated by the bal adj screen
1021 delete_bal_adj ( p_effective_date => P_EFFECTIVE_DATE
1022 ,p_business_group_id => l_business_group_id
1023 ,p_assignment_id => p_assignment_id );
1024
1025
1026 -- Create P45 Balance Adjustments
1027 create_bal_adj ( p_effective_date => p_effective_date
1028 , p_business_group_id => l_business_group_id
1029 , p_assignment_id => p_assignment_id
1030 , p_tax_deducted_to_date => p_tax_deducted_to_date
1031 , p_pay_to_date => p_pay_to_date
1032 , p_disability_benefit => p_disability_benefit
1033 , p_lump_sum_payment => p_lump_sum_payment
1034 --13359423
1035 , p_total_usc_pay_todate => p_total_usc_pay_todate
1036 , p_total_usc_tax_todate => p_total_usc_tax_todate
1037 --13359423
1038 );
1039 END IF;
1040 --
1041 --6015209
1042 IF ( nvl(p_Tax_This_Employment,0) <> 0 OR
1043 nvl(p_Pay_This_Employment,0) <> 0 OR
1044 p_Previous_Employment_Start_Dt IS NOT NULL OR
1045 p_Previous_Employment_End_Date IS NOT NULL OR
1046 p_PAYE_Previous_Employer IS NOT NULL OR
1047 p_Already_Submitted IS NOT NULL OR
1048 p_P45P3_Or_P46 IS NOT NULL
1049 --13359423
1050 OR nvl(p_usc_pay_this_employment,0) <> 0
1051 OR nvl(p_usc_tax_this_employment,0) <> 0
1052 --13359423
1053 )
1054 THEN
1055 create_p46 ( p_effective_date
1056 , p_assignment_id
1057 , l_business_group_id
1058 , p_Tax_This_Employment
1059 , p_Previous_Employment_Start_Dt
1060 , p_Previous_Employment_End_Date
1061 , p_Pay_This_Employment
1062 , p_PAYE_Previous_Employer
1063 , p_P45P3_Or_P46
1064 , p_Already_Submitted
1065 --, p_P45P3_Or_P46_Processed
1066 --13359423
1067 ,p_usc_pay_this_employment
1068 ,p_usc_tax_this_employment
1069 --13359423
1070 );
1071 END IF;
1072 --6015209
1073
1074 -- Call After Process User Hook
1075 --
1076 begin
1077 pay_ie_paye_bk1.create_ie_paye_details_a
1078 (p_effective_date => p_effective_date
1079 ,p_business_group_id => l_business_group_id
1080 ,p_assignment_id => p_assignment_id
1081 ,p_info_source => p_info_source
1082 ,p_tax_basis => p_tax_basis
1083 ,p_certificate_start_date => p_certificate_start_date
1084 ,p_tax_assess_basis => p_tax_assess_basis
1085 ,p_certificate_issue_date => p_certificate_issue_date
1086 ,p_certificate_end_date => p_certificate_end_date
1087 ,p_weekly_tax_credit => p_weekly_tax_credit
1088 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1089 ,p_monthly_tax_credit => p_monthly_tax_credit
1090 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1091 ,p_tax_deducted_to_date => p_tax_deducted_to_date
1092 ,p_pay_to_date => p_pay_to_date
1093 ,p_disability_benefit => p_disability_benefit
1094 ,p_lump_sum_payment => p_lump_sum_payment
1095 ,p_paye_details_id => l_paye_details_id
1096 ,p_object_version_number => l_object_version_number
1097 ,p_effective_start_date => l_effective_start_date
1098 ,p_effective_end_date => l_effective_end_date
1099 ,p_Tax_This_Employment => p_Tax_This_Employment
1100 ,p_Previous_Employment_Start_Dt => p_Previous_Employment_Start_Dt
1101 ,p_Previous_Employment_End_Date => p_Previous_Employment_End_Date
1102 ,p_Pay_This_Employment => p_Pay_This_Employment
1103 ,p_PAYE_Previous_Employer => p_PAYE_Previous_Employer
1104 ,p_P45P3_Or_P46 => p_P45P3_Or_P46
1105 ,p_Already_Submitted => p_Already_Submitted
1106 --,p_P45P3_Or_P46_Processed => p_P45P3_Or_P46_Processed
1107 --13359423
1108 ,p_yrly_tax_cred => p_yrly_tax_cred
1109 ,p_yrly_tax_rate_1 => p_yrly_tax_rate_1
1110 ,p_yrly_tax_rate_2 => p_yrly_tax_rate_2
1111 ,p_mthly_tax_rate_2 => p_mthly_tax_rate_2
1112 ,p_wkly_tax_rate_2 => p_wkly_tax_rate_2
1113 ,p_tax_rate_3 => p_tax_rate_3
1114 ,p_yrly_tax_rate_3 => p_yrly_tax_rate_3
1115 ,p_mthly_tax_rate_3 => p_mthly_tax_rate_3
1116 ,p_wkly_tax_rate_3 => p_wkly_tax_rate_3
1117 ,p_tax_rate_4 => p_tax_rate_4
1118 ,p_yrly_tax_rate_4 => p_yrly_tax_rate_4
1119 ,p_mthly_tax_rate_4 => p_mthly_tax_rate_4
1120 ,p_wkly_tax_rate_4 => p_wkly_tax_rate_4
1121 ,p_tax_rate_5 => p_tax_rate_5
1122 ,p_in_exempt_usc => p_in_exempt_usc
1123 ,p_total_usc_pay_todate => p_total_usc_pay_todate
1124 ,p_total_usc_tax_todate => p_total_usc_tax_todate
1125 ,p_usc_rate_1 => p_usc_rate_1
1126 ,p_usc_yrly_cutoff_1 => p_usc_yrly_cutoff_1
1127 ,p_usc_mthly_cutoff_1 => p_usc_mthly_cutoff_1
1128 ,p_usc_wkly_cutoff_1 => p_usc_wkly_cutoff_1
1129 ,p_usc_rate_2 => p_usc_rate_2
1130 ,p_usc_yrly_cutoff_2 => p_usc_yrly_cutoff_2
1131 ,p_usc_mthly_cutoff_2 => p_usc_mthly_cutoff_2
1132 ,p_usc_wkly_cutoff_2 => p_usc_wkly_cutoff_2
1133 ,p_usc_rate_3 => p_usc_rate_3
1134 ,p_usc_yrly_cutoff_3 => p_usc_yrly_cutoff_3
1135 ,p_usc_mthly_cutoff_3 => p_usc_mthly_cutoff_3
1136 ,p_usc_wkly_cutoff_3 => p_usc_wkly_cutoff_3
1137 ,p_usc_rate_4 => p_usc_rate_4
1138 ,p_usc_yrly_cutoff_4 => p_usc_yrly_cutoff_4
1139 ,p_usc_mthly_cutoff_4 => p_usc_mthly_cutoff_4
1140 ,p_usc_wkly_cutoff_4 => p_usc_wkly_cutoff_4
1141 ,p_usc_rate_5 => p_usc_rate_5
1142 ,p_usc_tax_basis => p_usc_tax_basis
1143 ,p_usc_info_source => p_usc_info_source
1144 ,p_usc_pay_this_employment => p_usc_pay_this_employment
1145 ,p_usc_tax_this_employment => p_usc_tax_this_employment
1146 --13359423
1147 );
1148 exception
1149 when hr_api.cannot_find_prog_unit then
1150 hr_api.cannot_find_prog_unit_error
1151 (p_module_name => 'create_ie_paye_details'
1152 ,p_hook_type => 'AP'
1153 );
1154 end;
1155 --
1156 -- When in validation only mode raise the Validate_Enabled exception
1157 --
1158 if p_validate then
1159 raise hr_api.validate_enabled;
1160 end if;
1161 --
1162 -- Set all output arguments
1163 --
1164 p_paye_details_id := l_paye_details_id;
1165 p_object_version_number := l_object_version_number;
1166 p_effective_start_date := l_effective_start_date;
1167 p_effective_end_date := l_effective_end_Date;
1168 --
1169 hr_utility.set_location(' Leaving:'||l_proc, 70);
1170 exception
1171 when hr_api.validate_enabled then
1172 --
1173 -- As the Validate_Enabled exception has been raised
1174 -- we must rollback to the savepoint
1175 --
1176 rollback to create_ie_paye_details;
1177 --
1178 -- Only set output warning arguments
1179 -- (Any key or derived arguments must be set to null
1180 -- when validation only mode is being used.)
1181 --
1182 p_paye_details_id := null;
1183 p_object_version_number := null;
1184 p_effective_start_date := null;
1185 p_effective_end_Date := null;
1186 hr_utility.set_location(' Leaving:'||l_proc, 80);
1187 when others then
1188 --
1189 -- A validation or unexpected error has occured
1190 --
1191 rollback to create_ie_paye_details;
1192 p_paye_details_id := null;
1193 p_object_version_number := null;
1194 p_effective_start_date := null;
1195 p_effective_end_Date := null;
1196 hr_utility.set_location(' Leaving:'||l_proc, 90);
1197 raise;
1198 end create_ie_paye_details;
1199 --
1200 --
1201 -- ----------------------------------------------------------------------------
1202 -- |------------------------< update_ie_paye_details >------------------------|
1203 -- ----------------------------------------------------------------------------
1204 --
1205 procedure update_ie_paye_details
1206 (p_validate in boolean
1207 ,p_effective_date in date
1208 ,p_datetrack_update_mode in varchar2
1209 ,p_paye_details_id in number
1210 ,p_info_source in varchar2
1211 ,p_tax_basis in varchar2
1212 ,p_certificate_start_date in date
1213 ,p_tax_assess_basis in varchar2
1214 ,p_certificate_issue_date in date default null
1215 ,p_certificate_end_date in date default null
1216 ,p_weekly_tax_credit in number default null
1217 ,p_weekly_std_rate_cut_off in number default null
1218 ,p_monthly_tax_credit in number default null
1219 ,p_monthly_std_rate_cut_off in number default null
1220 ,p_tax_deducted_to_date in number default null
1221 ,p_pay_to_date in number default null
1222 ,p_disability_benefit in number default null
1223 ,p_lump_sum_payment in number default null
1224 ,p_object_version_number in out nocopy number
1225 ,p_effective_start_date out nocopy date
1226 ,p_effective_end_date out nocopy date
1227 ,p_Tax_This_Employment in Number
1228 ,p_Previous_Employment_Start_Dt in date
1229 ,p_Previous_Employment_End_Date in date
1230 ,p_Pay_This_Employment in number
1231 ,p_PAYE_Previous_Employer in varchar2
1232 ,p_P45P3_Or_P46 in varchar2
1233 ,p_Already_Submitted in varchar2
1234 --,p_P45P3_Or_P46_Processed in varchar2
1235 --13359423
1236 ,p_yrly_tax_cred in NUMBER DEFAULT NULL
1237 ,p_yrly_tax_rate_1 in number default null
1238 ,p_yrly_tax_rate_2 in number default null
1239 ,p_mthly_tax_rate_2 in number default null
1240 ,p_wkly_tax_rate_2 in number default null
1241 ,p_tax_rate_3 in number default null
1242 ,p_yrly_tax_rate_3 in number default null
1243 ,p_mthly_tax_rate_3 in number default null
1244 ,p_wkly_tax_rate_3 in number default null
1245 ,p_tax_rate_4 in number default null
1246 ,p_yrly_tax_rate_4 in number default null
1247 ,p_mthly_tax_rate_4 in number default null
1248 ,p_wkly_tax_rate_4 in number default null
1249 ,p_tax_rate_5 in number default null
1250 ,p_in_exempt_usc in varchar2 default null
1251 ,p_total_usc_pay_todate in number default null
1252 ,p_total_usc_tax_todate in number default null
1253 ,p_usc_rate_1 in number default null
1254 ,p_usc_yrly_cutoff_1 in number default null
1255 ,p_usc_mthly_cutoff_1 in number default null
1256 ,p_usc_wkly_cutoff_1 in number default null
1257 ,p_usc_rate_2 in number default null
1258 ,p_usc_yrly_cutoff_2 in number default null
1259 ,p_usc_mthly_cutoff_2 in number default null
1260 ,p_usc_wkly_cutoff_2 in number default null
1261 ,p_usc_rate_3 in number default null
1262 ,p_usc_yrly_cutoff_3 in number default null
1263 ,p_usc_mthly_cutoff_3 in number default null
1264 ,p_usc_wkly_cutoff_3 in number default null
1265 ,p_usc_rate_4 in number default null
1266 ,p_usc_yrly_cutoff_4 in number default null
1267 ,p_usc_mthly_cutoff_4 in number default null
1268 ,p_usc_wkly_cutoff_4 in number default null
1269 ,p_usc_rate_5 in number default null
1270 ,p_usc_tax_basis in varchar2 default null
1271 ,p_usc_info_source in varchar2 default null
1272 ,p_usc_pay_this_employment in number default null
1273 ,p_usc_tax_this_employment in Number default null
1274 --13359423
1275 ) IS
1276 --
1277 -- Declare cursors and local variables
1278 --
1279
1280 l_proc varchar2(72) := g_package||'update_ie_paye_details';
1281 l_certificate_start_date date;
1282 l_certificate_issue_date date;
1283 l_certificate_end_date date;
1284 l_object_version_number number := p_object_version_number;
1285 l_effective_start_date date;
1286 l_effective_end_Date date;
1287 l_request_id number;
1288 l_program_id number;
1289 l_prog_appl_id number;
1290 l_p45_effective_date date;
1291 l_assignment_id number;
1292 l_business_group_id number;
1293 --
1294 CURSOR asg_csr IS
1295 SELECT assignment_id
1296 FROM pay_ie_paye_details_f
1297 WHERE paye_details_id = p_paye_details_id
1298 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1299 --
1300 CURSOR business_group_csr IS
1301 SELECT business_group_id
1302 FROM per_all_assignments_f
1303 WHERE assignment_id = l_assignment_id
1304 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1305
1306 --6015209
1307 --l_datetrack_delete_mode VARCHAR2(100) := 'ZAP';
1308 l_object_version_p45p3 NUMBER;
1309
1310 -- Checking if any future record exist for p45 record with in the Year.
1311 l_tax_yr_start_date date;
1312 /* CURSOR future_entry_csr IS
1313 SELECT pee.element_entry_id, pee.effective_start_date
1314 FROM pay_element_entries_f pee,
1315 pay_element_types_f pet,
1316 pay_element_links_f pel
1317 WHERE pee.element_link_id = pel.element_link_id AND
1318 pee.assignment_id = l_assignment_id AND
1319 pee.effective_start_date >= l_tax_yr_start_date and
1320 pee.effective_end_date <= add_months(l_tax_yr_start_date,12) AND
1321 (pee.effective_start_date > p_effective_date OR
1322 p_effective_date Between pee.effective_start_date AND pee.effective_end_date) AND
1323 pel.element_type_id = pet.element_type_id AND
1324 pel.business_group_id = l_business_group_id AND
1325 pet.element_name in('IE P45 Information', 'Setup P45 Element') AND
1326 NVL(pet.business_group_id, l_business_group_id) = l_business_group_id AND
1327 pet.legislation_code = 'IE' ; */
1328
1329 CURSOR future_entry_csr IS
1330 select 1
1331 from
1332 pay_payroll_actions ppa,
1333 pay_assignment_actions paa,
1334 pay_run_results prr,
1335 pay_run_result_values prrv,
1336 pay_element_types_f petf,
1337 pay_element_entries_f peef
1338 where ppa.business_group_id = l_business_group_id
1339 and ppa.action_type = 'B'
1340 and ppa.action_status = 'C'
1341 and ppa.payroll_action_id = paa.payroll_action_id
1342 and paa.assignment_id = l_assignment_id
1343 and prr.assignment_action_id = paa.assignment_action_id
1344 and prr.entry_type = 'B'
1345 and prr.run_result_id = prrv.run_result_id
1346 and prr.element_type_id = petf.element_type_id
1347 and NVL(petf.business_group_id, l_business_group_id) = l_business_group_id
1348 and petf.element_name in('IE P45 Information', 'Setup P45 Element')
1349 and petf.legislation_code = 'IE'
1350 and peef.element_type_id = petf.element_type_id
1351 and peef.assignment_id = paa.assignment_id
1352 and peef.effective_start_date >= l_tax_yr_start_date
1353 and peef.effective_end_date <= add_months(l_tax_yr_start_date,12)
1354 and peef.entry_type = 'B'
1355 and peef.element_entry_id = prr.element_entry_id
1356 and ppa.effective_date > p_effective_date;
1357
1358 rec_future_entry_exist future_entry_csr%rowtype;
1359
1360 --6015209
1361 begin
1362 hr_utility.set_location('Entering:'|| l_proc, 10);
1363 l_tax_yr_start_date := to_date('01/01/' || to_char(p_effective_date,'YYYY'),'DD/MM/YYYY'); --6015209
1364 --
1365 -- Issue a savepoint
1366 --
1367 savepoint update_ie_paye_details;
1368 --
1369 -- Truncate the time portion from all IN date parameters
1370 --
1371 l_certificate_start_date := trunc(p_certificate_start_date);
1372 l_certificate_issue_date := trunc(p_certificate_issue_date);
1373 l_certificate_end_date := trunc(p_certificate_end_date);
1374 --
1375 -- Get assignment_id from the cursor
1376 OPEN asg_csr;
1377 FETCH asg_csr INTO l_assignment_id;
1378 CLOSE asg_csr;
1379 --
1380 -- Get Business Group Id
1381 --
1382 OPEN business_group_csr;
1383 FETCH business_group_csr INTO l_business_group_id;
1384 CLOSE business_group_csr;
1385 --
1386 -- Call Before Process User Hook
1387 --
1388 begin
1389 hr_utility.set_location('before pay_ie_paye_bk2.update_ie_paye_details_b', 2001);
1390 pay_ie_paye_bk2.update_ie_paye_details_b
1391 (p_effective_date => p_effective_date
1392 ,p_datetrack_update_mode => p_datetrack_update_mode
1393 ,p_business_group_id => l_business_group_id
1394 ,p_paye_details_id => p_paye_details_id
1395 ,p_info_source => p_info_source
1396 ,p_tax_basis => p_tax_basis
1397 ,p_certificate_start_date => p_certificate_start_date
1398 ,p_tax_assess_basis => p_tax_assess_basis
1399 ,p_certificate_issue_date => p_certificate_issue_date
1400 ,p_certificate_end_date => p_certificate_end_date
1401 ,p_weekly_tax_credit => p_weekly_tax_credit
1402 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1403 ,p_monthly_tax_credit => p_monthly_tax_credit
1404 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1405 ,p_tax_deducted_to_date => p_tax_deducted_to_date
1406 ,p_pay_to_date => p_pay_to_date
1407 ,p_disability_benefit => p_disability_benefit
1408 ,p_lump_sum_payment => p_lump_sum_payment
1409 ,p_object_version_number => l_object_version_number
1410 ,p_Tax_This_Employment => p_Tax_This_Employment
1411 ,p_Previous_Employment_Start_Dt => p_Previous_Employment_Start_Dt
1412 ,p_Previous_Employment_End_Date => p_Previous_Employment_End_Date
1413 ,p_Pay_This_Employment => p_Pay_This_Employment
1414 ,p_PAYE_Previous_Employer => p_PAYE_Previous_Employer
1415 ,p_P45P3_Or_P46 => p_P45P3_Or_P46
1416 ,p_Already_Submitted => p_Already_Submitted
1417 --,p_P45P3_Or_P46_Processed => p_P45P3_Or_P46_Processed
1418 --13359423
1419 ,p_yrly_tax_cred => p_yrly_tax_cred
1420 ,p_yrly_tax_rate_1 => p_yrly_tax_rate_1
1421 ,p_yrly_tax_rate_2 => p_yrly_tax_rate_2
1422 ,p_mthly_tax_rate_2 => p_mthly_tax_rate_2
1423 ,p_wkly_tax_rate_2 => p_wkly_tax_rate_2
1424 ,p_tax_rate_3 => p_tax_rate_3
1425 ,p_yrly_tax_rate_3 => p_yrly_tax_rate_3
1426 ,p_mthly_tax_rate_3 => p_mthly_tax_rate_3
1427 ,p_wkly_tax_rate_3 => p_wkly_tax_rate_3
1428 ,p_tax_rate_4 => p_tax_rate_4
1429 ,p_yrly_tax_rate_4 => p_yrly_tax_rate_4
1430 ,p_mthly_tax_rate_4 => p_mthly_tax_rate_4
1431 ,p_wkly_tax_rate_4 => p_wkly_tax_rate_4
1432 ,p_tax_rate_5 => p_tax_rate_5
1433 ,p_in_exempt_usc => p_in_exempt_usc
1434 ,p_total_usc_pay_todate => p_total_usc_pay_todate
1435 ,p_total_usc_tax_todate => p_total_usc_tax_todate
1436 ,p_usc_rate_1 => p_usc_rate_1
1437 ,p_usc_yrly_cutoff_1 => p_usc_yrly_cutoff_1
1438 ,p_usc_mthly_cutoff_1 => p_usc_mthly_cutoff_1
1439 ,p_usc_wkly_cutoff_1 => p_usc_wkly_cutoff_1
1440 ,p_usc_rate_2 => p_usc_rate_2
1441 ,p_usc_yrly_cutoff_2 => p_usc_yrly_cutoff_2
1442 ,p_usc_mthly_cutoff_2 => p_usc_mthly_cutoff_2
1443 ,p_usc_wkly_cutoff_2 => p_usc_wkly_cutoff_2
1444 ,p_usc_rate_3 => p_usc_rate_3
1445 ,p_usc_yrly_cutoff_3 => p_usc_yrly_cutoff_3
1446 ,p_usc_mthly_cutoff_3 => p_usc_mthly_cutoff_3
1447 ,p_usc_wkly_cutoff_3 => p_usc_wkly_cutoff_3
1448 ,p_usc_rate_4 => p_usc_rate_4
1449 ,p_usc_yrly_cutoff_4 => p_usc_yrly_cutoff_4
1450 ,p_usc_mthly_cutoff_4 => p_usc_mthly_cutoff_4
1451 ,p_usc_wkly_cutoff_4 => p_usc_wkly_cutoff_4
1452 ,p_usc_rate_5 => p_usc_rate_5
1453 ,p_usc_tax_basis => p_usc_tax_basis
1454 ,p_usc_info_source => p_usc_info_source
1455 ,p_usc_pay_this_employment => p_usc_pay_this_employment
1456 ,p_usc_tax_this_employment => p_usc_tax_this_employment
1457 --13359423
1458 );
1459 hr_utility.set_location('after pay_ie_paye_bk2.update_ie_paye_details_b', 2001);
1460 exception
1461 when hr_api.cannot_find_prog_unit then
1462 hr_api.cannot_find_prog_unit_error
1463 (p_module_name => 'update_ie_paye_details'
1464 ,p_hook_type => 'BP'
1465 );
1466 end;
1467 --
1468 -- Process Logic
1469 --
1470 -- Set parameter values
1471 --
1472 l_request_id := fnd_global.conc_request_id;
1473 l_prog_appl_id := fnd_global.prog_appl_id;
1474 l_program_id := fnd_global.conc_program_id;
1475 --
1476 -- Call row handler procedure to update paye details
1477 --
1478 hr_utility.set_location('before pay_ipd_upd.upd', 2002);
1479 pay_ipd_upd.upd
1480 ( p_effective_date => p_effective_date
1481 ,p_datetrack_mode => p_datetrack_update_mode
1482 ,p_paye_details_id => p_paye_details_id
1483 ,p_object_version_number => l_object_version_number
1484 ,p_info_source => p_info_source
1485 ,p_tax_basis => p_tax_basis
1486 ,p_certificate_start_date => p_certificate_start_Date
1487 ,p_tax_assess_basis => p_tax_assess_basis
1488 ,p_certificate_end_date => p_certificate_end_date
1489 ,p_weekly_tax_credit => p_weekly_tax_credit
1490 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1491 ,p_monthly_tax_credit => p_monthly_tax_credit
1492 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1493 ,p_request_id => l_request_id
1494 ,p_program_application_id => l_prog_appl_id
1495 ,p_program_id => l_program_id
1496 ,p_program_update_date => sysdate
1497 ,p_effective_start_date => l_effective_start_date
1498 ,p_effective_end_date => l_effective_end_date
1499 ,p_certificate_issue_date => p_certificate_issue_Date
1500 --13359423
1501 ,p_yrly_tax_cred => p_yrly_tax_cred
1502 ,p_yrly_tax_rate_1 => p_yrly_tax_rate_1
1503 ,p_yrly_tax_rate_2 => p_yrly_tax_rate_2
1504 ,p_mthly_tax_rate_2 => p_mthly_tax_rate_2
1505 ,p_wkly_tax_rate_2 => p_wkly_tax_rate_2
1506 ,p_tax_rate_3 => p_tax_rate_3
1507 ,p_yrly_tax_rate_3 => p_yrly_tax_rate_3
1508 ,p_mthly_tax_rate_3 => p_mthly_tax_rate_3
1509 ,p_wkly_tax_rate_3 => p_wkly_tax_rate_3
1510 ,p_tax_rate_4 => p_tax_rate_4
1511 ,p_yrly_tax_rate_4 => p_yrly_tax_rate_4
1512 ,p_mthly_tax_rate_4 => p_mthly_tax_rate_4
1513 ,p_wkly_tax_rate_4 => p_wkly_tax_rate_4
1514 ,p_tax_rate_5 => p_tax_rate_5
1515 ,p_in_exempt_usc => p_in_exempt_usc
1516 ,p_total_usc_pay_todate => p_total_usc_pay_todate
1517 ,p_total_usc_tax_todate => p_total_usc_tax_todate
1518 ,p_usc_rate_1 => p_usc_rate_1
1519 ,p_usc_yrly_cutoff_1 => p_usc_yrly_cutoff_1
1520 ,p_usc_mthly_cutoff_1 => p_usc_mthly_cutoff_1
1521 ,p_usc_wkly_cutoff_1 => p_usc_wkly_cutoff_1
1522 ,p_usc_rate_2 => p_usc_rate_2
1523 ,p_usc_yrly_cutoff_2 => p_usc_yrly_cutoff_2
1524 ,p_usc_mthly_cutoff_2 => p_usc_mthly_cutoff_2
1525 ,p_usc_wkly_cutoff_2 => p_usc_wkly_cutoff_2
1526 ,p_usc_rate_3 => p_usc_rate_3
1527 ,p_usc_yrly_cutoff_3 => p_usc_yrly_cutoff_3
1528 ,p_usc_mthly_cutoff_3 => p_usc_mthly_cutoff_3
1529 ,p_usc_wkly_cutoff_3 => p_usc_wkly_cutoff_3
1530 ,p_usc_rate_4 => p_usc_rate_4
1531 ,p_usc_yrly_cutoff_4 => p_usc_yrly_cutoff_4
1532 ,p_usc_mthly_cutoff_4 => p_usc_mthly_cutoff_4
1533 ,p_usc_wkly_cutoff_4 => p_usc_wkly_cutoff_4
1534 ,p_usc_rate_5 => p_usc_rate_5
1535 ,p_usc_tax_basis => p_usc_tax_basis
1536 ,p_usc_info_source => p_usc_info_source
1537 --13359423
1538 );
1539 hr_utility.set_location('after pay_ipd_upd.upd', 2002);
1540 --
1541 -- Get effective date of the P45 balance adjustment
1542 -- If PAYE details record first started before current fiscal year then
1543 -- P45 adjustment effective date is the first day of the fiscal year
1544 -- the below code assumed that the P45 info could be entered only via TAx form
1545 -- which is incorrect., it could be entered by balance adjusment form
1546 -- This resulted in bug 3013304
1547 -- we now deleet all bal adj with the effective date tax year
1548 /*
1549 SELECT min(effective_start_date)
1550 INTO l_p45_effective_date
1551 FROM pay_ie_paye_details_f
1552 WHERE paye_details_id = p_paye_details_id;
1553
1554 --
1555 IF l_p45_effective_date < to_date('01-JAN-'||to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY') THEN
1556 l_p45_effective_date := to_date('01-JAN-'||to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY') ;
1557 END IF;
1558 */
1559 --
1560
1561 OPEN future_entry_csr;
1562 FETCH future_entry_csr INTO rec_future_entry_exist;
1563 IF future_entry_csr%NOTFOUND AND ( nvl(p_tax_deducted_to_date,0) = 0 AND
1564 nvl(p_pay_to_date,0) = 0 AND
1565 nvl(p_disability_benefit,0) = 0 AND
1566 nvl(p_lump_sum_payment,0) = 0
1567 --13359423
1568 AND nvl(p_total_usc_pay_todate,0) = 0
1569 AND nvl(p_total_usc_tax_todate,0) = 0
1570 --13359423
1571 )
1572 THEN
1573 delete_bal_adj ( p_effective_date => P_EFFECTIVE_DATE
1574 ,p_business_group_id => l_business_group_id
1575 ,p_assignment_id => l_assignment_id );
1576 ELSIF ( nvl(p_tax_deducted_to_date,0) <> 0 OR
1577 nvl(p_pay_to_date,0) <> 0 OR
1578 nvl(p_disability_benefit,0) <> 0 OR
1579 nvl(p_lump_sum_payment,0) <> 0
1580 --13359423
1581 OR nvl(p_total_usc_pay_todate,0) <> 0
1582 OR nvl(p_total_usc_tax_todate,0) <> 0
1583 --13359423
1584 ) THEN
1585 ---Check if adjustments need to be created
1586 IF ( nvl(p_tax_deducted_to_date,0) <> hr_api.g_number OR
1587 nvl(p_pay_to_date,0) <> hr_api.g_number OR
1588 nvl(p_disability_benefit,0) <> hr_api.g_number OR
1589 nvl(p_lump_sum_payment,0) <> hr_api.g_number
1590 --13359423
1591 OR nvl(p_total_usc_pay_todate,0) <> hr_api.g_number
1592 OR nvl(p_total_usc_tax_todate,0) <> hr_api.g_number
1593 --13359423
1594 ) THEN
1595 -- Delete previous adjustments
1596 delete_bal_adj ( p_effective_date => P_EFFECTIVE_DATE
1597 ,p_business_group_id => l_business_group_id
1598 ,p_assignment_id => l_assignment_id );
1599 --
1600 -- Check if new balance adjustment entry needs to be crated
1601 --
1602 IF ( nvl(p_tax_deducted_to_date,0) <> 0 OR
1603 nvl(p_pay_to_date,0) <> 0 OR
1604 nvl(p_disability_benefit,0) <> 0 OR
1605 nvl(p_lump_sum_payment,0) <> 0
1606 --13359423
1607 OR nvl(p_total_usc_pay_todate,0) <> 0
1608 OR nvl(p_total_usc_tax_todate,0) <> 0
1609 --13359423
1610 ) THEN
1611 --Create new P45 Balance Adjustments
1612 create_bal_adj ( p_effective_date => P_EFFECTIVE_DATE -- l_p45_effective_date
1613 , p_business_group_id => l_business_group_id
1614 , p_assignment_id => l_assignment_id
1615 , p_tax_deducted_to_date => p_tax_deducted_to_date
1616 , p_pay_to_date => p_pay_to_date
1617 , p_disability_benefit => p_disability_benefit
1618 , p_lump_sum_payment => p_lump_sum_payment
1619 --13359423
1620 , p_total_usc_pay_todate => p_total_usc_pay_todate
1621 , p_total_usc_tax_todate => p_total_usc_tax_todate
1622 --13359423
1623 );
1624 END IF;
1625 END IF;
1626 END IF;
1627 CLOSE future_entry_csr;
1628 --END IF;
1629
1630 hr_utility.set_location('before update_p46', 2003);
1631
1632 --6015209
1633 IF ( nvl(p_Tax_This_Employment,0) <> 0 OR
1634 nvl(p_Pay_This_Employment,0) <> 0 OR
1635 p_Previous_Employment_Start_Dt IS NOT NULL OR
1636 p_Previous_Employment_End_Date IS NOT NULL OR
1637 p_PAYE_Previous_Employer IS NOT NULL OR
1638 p_Already_Submitted IS NOT NULL OR
1639 p_P45P3_Or_P46 IS NOT NULL
1640 --13359423
1641 OR nvl(p_usc_pay_this_employment,0) <> 0
1642 OR nvl(p_usc_tax_this_employment,0) <> 0
1643 --13359423
1644
1645 )
1646 THEN
1647 update_p46 ( p_effective_date
1648 , l_assignment_id
1649 , l_business_group_id
1650 , p_datetrack_update_mode
1651 , l_object_version_p45p3
1652 , p_paye_details_id
1653 , p_Tax_This_Employment
1654 , p_Previous_Employment_Start_Dt
1655 , p_Previous_Employment_End_Date
1656 , p_Pay_This_Employment
1657 , p_PAYE_Previous_Employer
1658 , p_P45P3_Or_P46
1659 , p_Already_Submitted
1660 --, p_P45P3_Or_P46_Processed
1661 --13359423
1662 ,p_usc_pay_this_employment
1663 ,p_usc_tax_this_employment
1664 --13359423
1665 );
1666 END IF;
1667 --6015209
1668
1669 hr_utility.set_location('after update_p46', 2003);
1670
1671 --
1672 -- Call After Process User Hook
1673 --
1674 begin
1675 hr_utility.set_location('before pay_ie_paye_bk2.update_ie_paye_details_a', 2004);
1676 pay_ie_paye_bk2.update_ie_paye_details_a
1677 (p_effective_date => p_effective_date
1678 ,p_business_group_id => l_business_group_id
1679 ,p_datetrack_update_mode => p_datetrack_update_mode
1680 ,p_paye_details_id => p_paye_details_id
1681 ,p_info_source => p_info_source
1682 ,p_tax_basis => p_tax_basis
1683 ,p_certificate_start_date => p_certificate_start_date
1684 ,p_tax_assess_basis => p_tax_assess_basis
1685 ,p_certificate_issue_date => p_certificate_issue_date
1686 ,p_certificate_end_date => p_certificate_end_date
1687 ,p_weekly_tax_credit => p_weekly_tax_credit
1688 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1689 ,p_monthly_tax_credit => p_monthly_tax_credit
1690 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1691 ,p_tax_deducted_to_date => p_tax_deducted_to_date
1692 ,p_pay_to_date => p_pay_to_date
1693 ,p_disability_benefit => p_disability_benefit
1694 ,p_lump_sum_payment => p_lump_sum_payment
1695 ,p_object_version_number => l_object_version_number
1696 ,p_effective_start_date => l_effective_start_date
1697 ,p_effective_end_date => l_effective_end_date
1698 ,p_Tax_This_Employment => p_Tax_This_Employment
1699 ,p_Previous_Employment_Start_Dt => p_Previous_Employment_Start_Dt
1700 ,p_Previous_Employment_End_Date => p_Previous_Employment_End_Date
1701 ,p_Pay_This_Employment => p_Pay_This_Employment
1702 ,p_PAYE_Previous_Employer => p_PAYE_Previous_Employer
1703 ,p_P45P3_Or_P46 => p_P45P3_Or_P46
1704 ,p_Already_Submitted => p_Already_Submitted
1705 --,p_P45P3_Or_P46_Processed => p_P45P3_Or_P46_Processed
1706 --13359423
1707 ,p_yrly_tax_cred => p_yrly_tax_cred
1708 ,p_yrly_tax_rate_1 => p_yrly_tax_rate_1
1709 ,p_yrly_tax_rate_2 => p_yrly_tax_rate_2
1710 ,p_mthly_tax_rate_2 => p_mthly_tax_rate_2
1711 ,p_wkly_tax_rate_2 => p_wkly_tax_rate_2
1712 ,p_tax_rate_3 => p_tax_rate_3
1713 ,p_yrly_tax_rate_3 => p_yrly_tax_rate_3
1714 ,p_mthly_tax_rate_3 => p_mthly_tax_rate_3
1715 ,p_wkly_tax_rate_3 => p_wkly_tax_rate_3
1716 ,p_tax_rate_4 => p_tax_rate_4
1717 ,p_yrly_tax_rate_4 => p_yrly_tax_rate_4
1718 ,p_mthly_tax_rate_4 => p_mthly_tax_rate_4
1719 ,p_wkly_tax_rate_4 => p_wkly_tax_rate_4
1720 ,p_tax_rate_5 => p_tax_rate_5
1721 ,p_in_exempt_usc => p_in_exempt_usc
1722 ,p_total_usc_pay_todate => p_total_usc_pay_todate
1723 ,p_total_usc_tax_todate => p_total_usc_tax_todate
1724 ,p_usc_rate_1 => p_usc_rate_1
1725 ,p_usc_yrly_cutoff_1 => p_usc_yrly_cutoff_1
1726 ,p_usc_mthly_cutoff_1 => p_usc_mthly_cutoff_1
1727 ,p_usc_wkly_cutoff_1 => p_usc_wkly_cutoff_1
1728 ,p_usc_rate_2 => p_usc_rate_2
1729 ,p_usc_yrly_cutoff_2 => p_usc_yrly_cutoff_2
1730 ,p_usc_mthly_cutoff_2 => p_usc_mthly_cutoff_2
1731 ,p_usc_wkly_cutoff_2 => p_usc_wkly_cutoff_2
1732 ,p_usc_rate_3 => p_usc_rate_3
1733 ,p_usc_yrly_cutoff_3 => p_usc_yrly_cutoff_3
1734 ,p_usc_mthly_cutoff_3 => p_usc_mthly_cutoff_3
1735 ,p_usc_wkly_cutoff_3 => p_usc_wkly_cutoff_3
1736 ,p_usc_rate_4 => p_usc_rate_4
1737 ,p_usc_yrly_cutoff_4 => p_usc_yrly_cutoff_4
1738 ,p_usc_mthly_cutoff_4 => p_usc_mthly_cutoff_4
1739 ,p_usc_wkly_cutoff_4 => p_usc_wkly_cutoff_4
1740 ,p_usc_rate_5 => p_usc_rate_5
1741 ,p_usc_tax_basis => p_usc_tax_basis
1742 ,p_usc_info_source => p_usc_info_source
1743 ,p_usc_pay_this_employment => p_usc_pay_this_employment
1744 ,p_usc_tax_this_employment => p_usc_tax_this_employment
1745 --13359423
1746 );
1747
1748 hr_utility.set_location('after pay_ie_paye_bk2.update_ie_paye_details_a', 2004);
1749 exception
1750 when hr_api.cannot_find_prog_unit then
1751 hr_api.cannot_find_prog_unit_error
1752 (p_module_name => 'update_ie_paye_details'
1753 ,p_hook_type => 'AP'
1754 );
1755 end;
1756 --
1757 -- When in validation only mode raise the Validate_Enabled exception
1758 --
1759 hr_utility.set_location('before p_validate', 2005);
1760 if p_validate then
1761 raise hr_api.validate_enabled;
1762 end if;
1763 hr_utility.set_location('after p_validate', 2005);
1764 --
1765 -- Set all output arguments
1766 --
1767 p_object_version_number := l_object_version_number;
1768 p_effective_start_date := l_effective_start_date;
1769 p_effective_end_date := l_effective_end_Date;
1770 --
1771 hr_utility.set_location(' Leaving:'||l_proc, 70);
1772 exception
1773 when hr_api.validate_enabled then
1774 --
1775 -- As the Validate_Enabled exception has been raised
1776 -- we must rollback to the savepoint
1777 --
1778 hr_utility.set_location('Inside when hr_api.validate_enabled', 2009);
1779 rollback to update_ie_paye_details;
1780 --
1781 -- Only set output warning arguments
1782 -- (Any key or derived arguments must be set to null
1783 -- when validation only mode is being used.)
1784 --
1785 -- IN OUT parameter should be reset to its IN value
1786 -- therefore no need to reset p_object_version_number
1787 p_object_version_number := l_object_version_number;
1788 p_effective_start_date := null;
1789 p_effective_end_Date := null;
1790 hr_utility.set_location(' Leaving:'||l_proc, 80);
1791 when others then
1792 --
1793 -- A validation or unexpected error has occured
1794 --
1795 hr_utility.set_location('Inside when others', 2010);
1796 rollback to update_ie_paye_details;
1797 p_object_version_number := l_object_version_number;
1798 p_effective_start_date := null;
1799 p_effective_end_Date := null;
1800 hr_utility.set_location(' Leaving:'||l_proc, 90);
1801 raise;
1802 end update_ie_paye_details;
1803
1804 --
1805 -- ----------------------------------------------------------------------------
1806 -- |------------------------< delete_ie_paye_details >------------------------|
1807 -- ----------------------------------------------------------------------------
1808 --
1809 procedure delete_ie_paye_details
1810 (p_validate in boolean
1811 ,p_effective_date in date
1812 ,p_datetrack_delete_mode in varchar2
1813 ,p_paye_details_id in number
1814 ,p_object_version_number in out nocopy number
1815 ,p_effective_start_date out nocopy date
1816 ,p_effective_end_date out nocopy date
1817 ) IS
1818 --
1819 -- Declare cursors and local variables
1820 --
1821
1822 l_proc varchar2(72) := g_package||'delete_ie_paye_details';
1823 l_object_version_number number := p_object_version_number;
1824 l_effective_start_date date;
1825 l_effective_end_Date date;
1826 l_p45_effective_date date;
1827 l_assignment_id number;
1828 l_business_group_id number;
1829 --
1830 CURSOR asg_csr IS
1831 SELECT assignment_id
1832 FROM pay_ie_paye_details_f
1833 WHERE paye_details_id = p_paye_details_id
1834 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1835 --
1836 CURSOR business_group_csr IS
1837 SELECT business_group_id
1838 FROM per_all_assignments_f
1839 WHERE assignment_id = l_assignment_id
1840 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1841 --
1842 l_object_version_p45p3 NUMBER;
1843 --
1844 begin
1845 --hr_utility.trace_on(NULL,'RSS');
1846 hr_utility.set_location('Entering:'|| l_proc, 10);
1847 --
1848 -- Issue a savepoint
1849 --
1850 savepoint delete_ie_paye_details;
1851 --
1852 -- Get assignment_id from the cursor
1853 OPEN asg_csr;
1854 FETCH asg_csr INTO l_assignment_id;
1855 CLOSE asg_csr;
1856 --
1857 -- Get Business_group_id
1858 OPEN business_group_csr;
1859 FETCH business_group_csr INTO l_business_group_id;
1860 CLOSE business_group_csr;
1861 --
1862 --
1863 -- Call Before Process User Hook
1864 --
1865 begin
1866 hr_utility.set_location('before pay_ie_paye_bk3.delete_ie_paye_details_b', 1000);
1867 pay_ie_paye_bk3.delete_ie_paye_details_b
1868 (p_effective_date => p_effective_date
1869 ,p_datetrack_delete_mode => p_datetrack_delete_mode
1870 ,p_business_group_id => l_business_group_id
1871 ,p_paye_details_id => p_paye_details_id
1872 ,p_object_version_number => l_object_version_number
1873 );
1874 hr_utility.set_location('after pay_ie_paye_bk3.delete_ie_paye_details_b', 1000);
1875 exception
1876 when hr_api.cannot_find_prog_unit then
1877 hr_api.cannot_find_prog_unit_error
1878 (p_module_name => 'delete_ie_paye_details'
1879 ,p_hook_type => 'BP'
1880 );
1881 end;
1882 --
1883 --BUG NO:2683086 Moved the call to procedure after call to delete_bal_adj procedure.
1884 /*
1885 -- Process Logic
1886 --
1887 -- Call row handler procedure to update paye details
1888 --
1889 pay_ipd_del.del
1890 ( p_effective_date => p_effective_date
1891 ,p_datetrack_mode => p_datetrack_delete_mode
1892 ,p_paye_details_id => p_paye_details_id
1893 ,p_object_version_number => l_object_version_number
1894 ,p_effective_start_date => l_effective_start_date
1895 ,p_effective_end_date => l_effective_end_date
1896 );
1897 */
1898 --
1899 -- Get effective date of the P45 balance adjustment
1900 -- If PAYE details record first started before current fiscal year then
1901 -- P45 adjustment effective date is the first day of the fiscal year
1902 -- the below code assumed that the P45 info could be entered only via TAx form
1903 -- which is incorrect., it could be entered by balance adjusment form
1904 -- This resulted in bug 3013304
1905 -- we now deleet all bal adj with the effective date tax year
1906 /*
1907 SELECT min(effective_start_date)
1908 INTO l_p45_effective_date
1909 FROM pay_ie_paye_details_f
1910 WHERE paye_details_id = p_paye_details_id;
1911 --
1912 IF l_p45_effective_date < to_date('01-JAN-'||to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY') THEN
1913 l_p45_effective_date := to_date('01-JAN-'||to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY') ;
1914 END IF;
1915 */
1916 --
1917 -- Check if adjustments need to be deleted
1918 IF ( p_datetrack_delete_mode = hr_api.g_zap) THEN
1919 -- Delete previous adjustments
1920 delete_bal_adj ( p_effective_date => p_effective_date -- l_p45_effective_date
1921 ,p_business_group_id => l_business_group_id
1922 ,p_assignment_id => l_assignment_id );
1923
1924 --6015209
1925 hr_utility.set_location('before delete_p46', 1001);
1926
1927 delete_p46 (p_effective_date
1928 , l_assignment_id
1929 , l_business_group_id
1930 , p_datetrack_delete_mode
1931 , l_object_version_p45p3
1932 );
1933 hr_utility.set_location('after delete_p46', 1001);
1934 --6015209
1935 END IF;
1936 --BUG NO:2683086 Moved the call to procedure after call to delete_bal_adj procedure.
1937
1938 -- Process Logic
1939 --
1940 -- Call row handler procedure to update paye details
1941 --
1942 hr_utility.set_location('before pay_ipd_del.del', 1002);
1943 pay_ipd_del.del
1944 ( p_effective_date => p_effective_date
1945 ,p_datetrack_mode => p_datetrack_delete_mode
1946 ,p_paye_details_id => p_paye_details_id
1947 ,p_object_version_number => l_object_version_number
1948 ,p_effective_start_date => l_effective_start_date
1949 ,p_effective_end_date => l_effective_end_date
1950 );
1951 hr_utility.set_location('after pay_ipd_del.del', 1002);
1952 --
1953 --
1954 -- Call After Process User Hook
1955 --
1956 begin
1957 hr_utility.set_location('before pay_ie_paye_bk3.delete_ie_paye_details_a', 1003);
1958 pay_ie_paye_bk3.delete_ie_paye_details_a
1959 (p_effective_date => p_effective_date
1960 ,p_business_group_id => l_business_group_id
1961 ,p_datetrack_delete_mode => p_datetrack_delete_mode
1962 ,p_paye_details_id => p_paye_details_id
1963 ,p_object_version_number => l_object_version_number
1964 ,p_effective_start_date => l_effective_start_date
1965 ,p_effective_end_date => l_effective_end_date
1966 );
1967 hr_utility.set_location('after pay_ie_paye_bk3.delete_ie_paye_details_a', 1003);
1968 exception
1969 when hr_api.cannot_find_prog_unit then
1970 hr_api.cannot_find_prog_unit_error
1971 (p_module_name => 'delete_ie_paye_details'
1972 ,p_hook_type => 'AP'
1973 );
1974 end;
1975 --
1976 -- When in validation only mode raise the Validate_Enabled exception
1977 --
1978 hr_utility.set_location('before p_validate', 1004);
1979 if p_validate then
1980 raise hr_api.validate_enabled;
1981 end if;
1982 hr_utility.set_location('after p_validate', 1004);
1983 --
1984 -- Set all output arguments
1985 --
1986 p_object_version_number := l_object_version_number;
1987 p_effective_start_date := l_effective_start_date;
1988 p_effective_end_date := l_effective_end_Date;
1989 --
1990 hr_utility.set_location(' Leaving:'||l_proc, 70);
1991 --hr_utility.trace_off;
1992 exception
1993 when hr_api.validate_enabled then
1994 --
1995 -- As the Validate_Enabled exception has been raised
1996 -- we must rollback to the savepoint
1997 --
1998 rollback to delete_ie_paye_details;
1999 --
2000 -- Only set output warning arguments
2001 -- (Any key or derived arguments must be set to null
2002 -- when validation only mode is being used.)
2003 --
2004 p_object_version_number := l_object_version_number;
2005 p_effective_start_date := null;
2006 p_effective_end_Date := null;
2007 --
2008 hr_utility.set_location(' Leaving:'||l_proc, 80);
2009 when others then
2010 --
2011 -- A validation or unexpected error has occured
2012 --
2013 hr_utility.set_location('Inside when Others', 1010);
2014 rollback to update_ie_paye_details;
2015 p_object_version_number := l_object_version_number;
2016 p_effective_start_date := null;
2017 p_effective_end_Date := null;
2018 hr_utility.set_location(' Leaving:'||l_proc, 90);
2019 raise;
2020 end delete_ie_paye_details;
2021
2022 end pay_ie_paye_api;