[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_PAYE_API
Source
1 Package Body pay_ie_paye_api as
2 /* $Header: pyipdapi.pkb 120.4 2007/11/27 07:42:31 rsahai noship $ */
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
25 ) is
26 CURSOR element_csr IS
27 SELECT element_type_id
28 FROM pay_element_types_f
29 WHERE element_name = 'IE P45P3_P46 Information'
30 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
31 AND nvl(legislation_code, 'IE') = 'IE'
32 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
33 --
34 element_rec element_csr%ROWTYPE;
35 --
36 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
37 SELECT input_value_id
38 FROM pay_input_values_f
39 WHERE element_type_id = p_element_type_id
40 AND name = p_name
41 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
42 AND nvl(legislation_code, 'IE') = 'IE'
43 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
44 --
45 input_val_rec1 input_val_csr%ROWTYPE;
46 input_val_rec2 input_val_csr%ROWTYPE;
47 input_val_rec3 input_val_csr%ROWTYPE;
48 input_val_rec4 input_val_csr%ROWTYPE;
49 input_val_rec5 input_val_csr%ROWTYPE;
50 input_val_rec6 input_val_csr%ROWTYPE;
51 input_val_rec7 input_val_csr%ROWTYPE;
52 input_val_rec8 input_val_csr%ROWTYPE;
53 --
54
55 CURSOR link_csr(p_element_type_id IN NUMBER) IS
56 SELECT links.element_link_id
57 FROM pay_element_links_f links, per_all_assignments_f assign
58 WHERE links.element_type_id = p_element_type_id
59 AND links.business_group_id=p_business_group_id
60 AND assign.assignment_id=p_assignment_id
61 AND (( links.payroll_id is not null
62 and links.payroll_id = assign.payroll_id)
63 OR ( links.link_to_all_payrolls_flag='Y'
64 and assign.payroll_id is not null)
65 OR ( links.payroll_id is null
66 and links.link_to_all_payrolls_flag='N')
67 OR links.job_id=assign.job_id
68 OR links.position_id=assign.position_id
69 OR links.people_group_id=assign.people_group_id
70 OR links.organization_id=assign.organization_id
71 OR links.grade_id=assign.grade_id
72 OR links.location_id=assign.location_id
73 OR links.pay_basis_id=assign.pay_basis_id
74 OR links.employment_category=assign.employment_category)
75 AND p_effective_date BETWEEN links.effective_start_date
76 AND links.effective_end_date;
77 --
78 link_rec link_csr%ROWTYPE;
79 --
80 l_element_entry_id NUMBER;
81 l_effective_start_date DATE;
82 l_effective_end_date DATE;
83 l_object_version_number NUMBER;
84 l_create_warning BOOLEAN := FALSE;
85 begin
86 --
87 -- Get Element information
88 --
89 OPEN element_csr;
90 FETCH element_csr INTO element_rec;
91 CLOSE element_csr;
92 --
93 -- Get Input Values
94 --
95 OPEN input_val_csr(element_rec.element_type_id, 'Tax This Employment');
96 FETCH input_val_csr INTO input_val_rec1;
97 CLOSE input_val_csr;
98 --
99 OPEN input_val_csr(element_rec.element_type_id, 'Previous Employment Start Date');
100 FETCH input_val_csr INTO input_val_rec2;
101 CLOSE input_val_csr;
102 --
103 OPEN input_val_csr(element_rec.element_type_id, 'Previous Employment End Date');
104 FETCH input_val_csr INTO input_val_rec3;
105 CLOSE input_val_csr;
106 --
107 OPEN input_val_csr(element_rec.element_type_id, 'Pay This Employment');
108 FETCH input_val_csr INTO input_val_rec4;
109 CLOSE input_val_csr;
110 --
111 OPEN input_val_csr(element_rec.element_type_id, 'PAYE Previous Employer');
112 FETCH input_val_csr INTO input_val_rec5;
113 CLOSE input_val_csr;
114 --
115 OPEN input_val_csr(element_rec.element_type_id, 'P45P3 Or P46');
116 FETCH input_val_csr INTO input_val_rec6;
117 CLOSE input_val_csr;
118 --
119 OPEN input_val_csr(element_rec.element_type_id, 'Already Submitted');
120 FETCH input_val_csr INTO input_val_rec7;
121 CLOSE input_val_csr;
122 --
123 /*OPEN input_val_csr(element_rec.element_type_id, 'P45P3 Or P46 Processed');
124 FETCH input_val_csr INTO input_val_rec8;
125 CLOSE input_val_csr;*/
126 --
127 -- Get element link information
128 --
129 OPEN link_csr(element_rec.element_type_id);
130 FETCH link_csr INTO link_rec;
131 CLOSE link_csr;
132
133 -- Call API To Create element entry.
134 py_element_entry_api.create_element_entry (
135 p_effective_date => p_effective_date,
136 p_business_group_id => p_business_group_id,
137 --p_original_entry_id => p_original_entry_id, -- default
138 p_assignment_id => p_assignment_id,
139 p_element_link_id => link_rec.element_link_id,
140 p_entry_type => 'E',
141 p_creator_type => 'F',
142 p_input_value_id1 => input_val_rec1.input_value_id,
143 p_input_value_id2 => input_val_rec2.input_value_id,
144 p_input_value_id3 => input_val_rec3.input_value_id,
145 p_input_value_id4 => input_val_rec4.input_value_id,
146 p_input_value_id5 => input_val_rec5.input_value_id,
147 p_input_value_id6 => input_val_rec6.input_value_id,
148 p_input_value_id7 => input_val_rec7.input_value_id,
149 --p_input_value_id8 => input_val_rec8.input_value_id,
150 p_entry_value1 => nvl(p_Tax_This_Employment,0),
151 p_entry_value2 => p_Previous_Employment_Start_Dt,
152 p_entry_value3 => p_Previous_Employment_End_Date,
153 p_entry_value4 => nvl(p_Pay_This_Employment,0),
154 p_entry_value5 => p_PAYE_Previous_Employer,
155 p_entry_value6 => nvl(p_P45P3_Or_P46,'N'),
156 p_entry_value7 => nvl(p_Already_Submitted,'N'),
157 --p_entry_value8 => nvl(p_P45P3_Or_P46_Processed,'N'),
158 p_effective_start_date => l_effective_start_date,
159 p_effective_end_date => l_effective_end_date,
160 p_element_entry_id => l_element_entry_id,
161 p_object_version_number => l_object_version_number,
162 p_create_warning => l_create_warning
163 );
164
165 end create_p46;
166 --6015209
167 -- ----------------------------------------------------------------------------
168 -- |--------------------------< update_p46 >------------------------------|
169 -- ----------------------------------------------------------------------------
170 procedure update_p46 ( p_effective_date IN DATE
171 , p_assignment_id IN NUMBER
172 , p_business_group_id IN NUMBER
173 , p_datetrack_update_mode IN VARCHAR2
174 , p_object_version_number IN OUT NOCOPY NUMBER
175 , p_paye_details_id IN NUMBER
176 , p_Tax_This_Employment IN NUMBER
177 , p_Previous_Employment_Start_Dt IN DATE
178 , p_Previous_Employment_End_Date IN DATE
179 , p_Pay_This_Employment IN NUMBER
180 , p_PAYE_Previous_Employer IN VARCHAR2
181 , p_P45P3_Or_P46 IN VARCHAR2
182 , p_Already_Submitted IN VARCHAR2
183 --, p_P45P3_Or_P46_Processed IN VARCHAR2
184 ) is
185 CURSOR element_csr IS
186 SELECT element_type_id
187 FROM pay_element_types_f
188 WHERE element_name = 'IE P45P3_P46 Information'
189 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
190 AND nvl(legislation_code, 'IE') = 'IE'
191 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
192 --
193 element_rec element_csr%ROWTYPE;
194 --
195 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
196 SELECT input_value_id
197 FROM pay_input_values_f
198 WHERE element_type_id = p_element_type_id
199 AND name = p_name
200 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
201 AND nvl(legislation_code, 'IE') = 'IE'
202 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
203 --
204 input_val_rec1 input_val_csr%ROWTYPE;
205 input_val_rec2 input_val_csr%ROWTYPE;
206 input_val_rec3 input_val_csr%ROWTYPE;
207 input_val_rec4 input_val_csr%ROWTYPE;
208 input_val_rec5 input_val_csr%ROWTYPE;
209 input_val_rec6 input_val_csr%ROWTYPE;
210 input_val_rec7 input_val_csr%ROWTYPE;
211 input_val_rec8 input_val_csr%ROWTYPE;
212 --
213 l_tax_yr_start_date date;
214 CURSOR entry_csr IS
215 SELECT pee.element_entry_id, pee.effective_start_date, pee.object_version_number
216 FROM pay_element_entries_f pee,
217 pay_element_types_f pet,
218 pay_element_links_f pel
219 WHERE pee.element_link_id = pel.element_link_id AND
220 pee.assignment_id = p_assignment_id AND
221 --p_effective_date between pee.effective_start_date and pee.effective_end_date AND
222 --pee.effective_start_date >= l_tax_yr_start_date and
223 --pee.effective_end_date <= add_months(l_tax_yr_start_date,12) AND
224 pel.element_type_id = pet.element_type_id AND
225 pel.business_group_id = p_business_group_id AND
226 pet.element_name = 'IE P45P3_P46 Information' AND
227 NVL(pet.business_group_id, p_business_group_id) = p_business_group_id AND
228 pet.legislation_code = 'IE' ;
229
230 rec_entry_csr entry_csr%rowtype;
231 --
232 l_element_entry_id NUMBER;
233 l_effective_start_date DATE;
234 l_effective_end_date DATE;
235 l_update_warning BOOLEAN := FALSE;
236 l_object_version_number NUMBER;
237 --
238 /*
239 CURSOR entry_csr_any IS
240 SELECT pee.element_entry_id, pee.effective_start_date
241 FROM pay_element_entries_f pee,
242 pay_element_types_f pet,
243 pay_element_links_f pel
244 WHERE pee.element_link_id = pel.element_link_id AND
245 pee.assignment_id = p_assignment_id AND
246 --p_effective_date between pee.effective_start_date and pee.effective_end_date AND
247 pel.element_type_id = pet.element_type_id AND
248 pel.business_group_id = p_business_group_id AND
249 pet.element_name = 'IE P45P3_P46 Information' AND
250 NVL(pet.business_group_id, p_business_group_id) = p_business_group_id AND
251 pet.legislation_code = 'IE' ;
252
253 rec_entry_csr_any entry_csr_any%rowtype;
254 */
255 --
256 CURSOR cur_p45p3_eff_start_date IS
257 SELECT min(effective_start_date)
258 FROM pay_ie_paye_details_f
259 WHERE paye_details_id = p_paye_details_id;
260 --AND p_effective_date BETWEEN effective_start_date AND effective_end_date
261 --AND effective_start_date >= l_tax_yr_start_date
262 --AND effective_end_date <= add_months(l_tax_yr_start_date,12);
263 --
264 l_p45p3_eff_start_date pay_ie_paye_details_f.effective_start_date%type;
265 l_datetrack_update_mode VARCHAR2(100);
266
267 begin
268
269 l_tax_yr_start_date := to_date('01/01/' || to_char(p_effective_date,'YYYY'),'DD/MM/YYYY');
270 --
271 -- Get Element information
272 --
273 OPEN element_csr;
274 FETCH element_csr INTO element_rec;
275 CLOSE element_csr;
276 --
277 -- Get Input Values
278 --
279 OPEN input_val_csr(element_rec.element_type_id, 'Tax This Employment');
280 FETCH input_val_csr INTO input_val_rec1;
281 CLOSE input_val_csr;
282 --
283 OPEN input_val_csr(element_rec.element_type_id, 'Previous Employment Start Date');
284 FETCH input_val_csr INTO input_val_rec2;
285 CLOSE input_val_csr;
286 --
287 OPEN input_val_csr(element_rec.element_type_id, 'Previous Employment End Date');
288 FETCH input_val_csr INTO input_val_rec3;
289 CLOSE input_val_csr;
290 --
291 OPEN input_val_csr(element_rec.element_type_id, 'Pay This Employment');
292 FETCH input_val_csr INTO input_val_rec4;
293 CLOSE input_val_csr;
294 --
295 OPEN input_val_csr(element_rec.element_type_id, 'PAYE Previous Employer');
296 FETCH input_val_csr INTO input_val_rec5;
297 CLOSE input_val_csr;
298 --
299 OPEN input_val_csr(element_rec.element_type_id, 'P45P3 Or P46');
300 FETCH input_val_csr INTO input_val_rec6;
301 CLOSE input_val_csr;
302 --
303 OPEN input_val_csr(element_rec.element_type_id, 'Already Submitted');
304 FETCH input_val_csr INTO input_val_rec7;
305 CLOSE input_val_csr;
306 --
307 /*OPEN input_val_csr(element_rec.element_type_id, 'P45P3 Or P46 Processed');
308 FETCH input_val_csr INTO input_val_rec8;
309 CLOSE input_val_csr;*/
310 --
311 open entry_csr;
312 fetch entry_csr into rec_entry_csr;
313 close entry_csr;
314 --
315 OPEN cur_p45p3_eff_start_date;
316 FETCH cur_p45p3_eff_start_date INTO l_p45p3_eff_start_date;
317 CLOSE cur_p45p3_eff_start_date;
318 --
319
320 -- FOR element_entries in entry_csr
321 -- LOOP
322 IF rec_entry_csr.element_entry_id IS NOT NULL THEN
323 -- Call Update element entry API
324 -- Datetrack records are not required. so setting the mode to correction always.
325 l_datetrack_update_mode := 'CORRECTION';
326 l_object_version_number := rec_entry_csr.object_version_number;
327
328 py_element_entry_api.update_element_entry
329 (p_validate => false
330 ,p_datetrack_update_mode => l_datetrack_update_mode --p_datetrack_update_mode
331 ,p_effective_date => l_p45p3_eff_start_date --p_effective_date
332 ,p_business_group_id => p_business_group_id
333 ,p_element_entry_id => rec_entry_csr.element_entry_id
334 ,p_object_version_number => l_object_version_number --p_object_version_number
335 ,p_input_value_id1 => input_val_rec1.input_value_id
336 ,p_input_value_id2 => input_val_rec2.input_value_id
337 ,p_input_value_id3 => input_val_rec3.input_value_id
338 ,p_input_value_id4 => input_val_rec4.input_value_id
339 ,p_input_value_id5 => input_val_rec5.input_value_id
340 ,p_input_value_id6 => input_val_rec6.input_value_id
341 ,p_input_value_id7 => input_val_rec7.input_value_id
342 --,p_input_value_id8 => input_val_rec8.input_value_id
343 ,p_entry_value1 => nvl(p_Tax_This_Employment,0)
344 ,p_entry_value2 => p_Previous_Employment_Start_Dt
345 ,p_entry_value3 => p_Previous_Employment_End_Date
346 ,p_entry_value4 => nvl(p_Pay_This_Employment,0)
347 ,p_entry_value5 => p_PAYE_Previous_Employer
348 ,p_entry_value6 => nvl(p_P45P3_Or_P46,'N')
349 ,p_entry_value7 => nvl(p_Already_Submitted,'N')
350 --,p_entry_value8 => nvl(p_P45P3_Or_P46_Processed,'N')
351 ,p_effective_start_date => l_effective_start_date
352 ,p_effective_end_date => l_effective_end_date
353 ,p_update_warning => l_update_warning
354 );
355 ELSE
356 --OPEN entry_csr_any;
357 --FETCH entry_csr_any INTO rec_entry_csr_any;
358 --IF entry_csr_any%NOTFOUND THEN
359 -- if mode is Correction then create the record with same eff start date as in pay_ie_paye_details_f table
360 -- if mode is updation then create the record with effective date passed.
361 --
362 /*
363 IF p_datetrack_update_mode = 'CORRECTION' THEN
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 ELSIF p_datetrack_update_mode = 'UPDATE' THEN
368 l_p45p3_eff_start_date := p_effective_date;
369 END IF;
370 */
371 --
372 IF ( nvl(p_Tax_This_Employment,0) <> 0 OR
373 nvl(p_Pay_This_Employment,0) <> 0 OR
374 p_Previous_Employment_Start_Dt IS NOT NULL OR
375 p_Previous_Employment_End_Date IS NOT NULL OR
376 p_PAYE_Previous_Employer IS NOT NULL OR
377 p_Already_Submitted IS NOT NULL OR
378 p_P45P3_Or_P46 IS NOT NULL
379 )
380 THEN
381 create_p46 ( l_p45p3_eff_start_date
382 , p_assignment_id
383 , p_business_group_id
384 , p_Tax_This_Employment
385 , p_Previous_Employment_Start_Dt
386 , p_Previous_Employment_End_Date
387 , p_Pay_This_Employment
388 , p_PAYE_Previous_Employer
389 , p_P45P3_Or_P46
390 , p_Already_Submitted
391 --, p_P45P3_Or_P46_Processed
392 );
393 END IF;
394 --END IF;
395 --CLOSE entry_csr_any;
396 END IF;
397 --END LOOP;
398
399 end update_p46;
400 --6015209
401 -- ----------------------------------------------------------------------------
402 -- |--------------------------< delete_p46 >------------------------------|
403 -- ----------------------------------------------------------------------------
404 --6015209
405 procedure delete_p46 ( p_effective_date IN DATE
406 , p_assignment_id IN NUMBER
407 , p_business_group_id IN NUMBER
408 , p_datetrack_delete_mode IN VARCHAR2
409 , p_object_version_number IN OUT NOCOPY NUMBER
410 ) is
411 l_tax_yr_start_date date;
412 -- eff date condition is not used since we are not keeping the datetrack records.
413 CURSOR entry_csr IS
414 SELECT pee.element_entry_id, pee.effective_start_date, pee.object_version_number
415 FROM pay_element_entries_f pee,
416 pay_element_types_f pet,
417 pay_element_links_f pel
418 WHERE pee.element_link_id = pel.element_link_id AND
419 pee.assignment_id = p_assignment_id AND
420 --p_effective_date between pee.effective_start_date and pee.effective_end_date AND
421 pel.element_type_id = pet.element_type_id AND
422 pel.business_group_id = p_business_group_id AND
423 pet.element_name = 'IE P45P3_P46 Information' AND
424 NVL(pet.business_group_id, p_business_group_id) = p_business_group_id AND
425 pet.legislation_code = 'IE' ;
426
427 rec_entry_csr entry_csr%rowtype;
428
429 l_effective_start_date DATE;
430 l_effective_end_date DATE;
431 l_delete_warning BOOLEAN := FALSE;
432
433 BEGIN
434 -- Get ELement Entry Information
435 -- derive the tax year start date givevn the effective data
436 --l_tax_yr_start_date := to_date('01/01/' || to_char(p_effective_date,'YYYY'),'DD/MM/YYYY');
437 open entry_csr;
438 fetch entry_csr into rec_entry_csr;
439 close entry_csr;
440 p_object_version_number := rec_entry_csr.object_version_number;
441
442 --
443 IF rec_entry_csr.element_entry_id IS NOT NULL THEN
444 -- Call Delete element entry API
445 py_element_entry_api.delete_element_entry
446 (p_validate => false
447 ,p_datetrack_delete_mode => p_datetrack_delete_mode
448 ,p_effective_date => rec_entry_csr.effective_start_date
449 ,p_element_entry_id => rec_entry_csr.element_entry_id
450 ,p_object_version_number => p_object_version_number
451 ,p_effective_start_date => l_effective_start_date
452 ,p_effective_end_date => l_effective_end_date
453 ,p_delete_warning => l_delete_warning
454 );
455 END IF;
456 --
457 end delete_p46;
458 --6015209
459 --
460 -- ----------------------------------------------------------------------------
461 -- |--------------------------< create_bal_adj >------------------------------|
462 -- ----------------------------------------------------------------------------
463 --
464 procedure create_bal_adj ( p_effective_date IN DATE
465 , p_assignment_id IN NUMBER
466 , p_business_group_id IN NUMBER
467 , p_tax_deducted_to_date IN NUMBER
468 , p_pay_to_date IN NUMBER
469 , p_disability_benefit IN NUMBER
470 , p_lump_sum_payment IN NUMBER
471 ) is
472 CURSOR element_csr IS
473 SELECT element_type_id
474 FROM pay_element_types_f
475 WHERE element_name = 'IE P45 Information'
476 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
477 AND nvl(legislation_code, 'IE') = 'IE'
478 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
479 --
480 element_rec element_csr%ROWTYPE;
481 --
482 CURSOR input_val_csr(p_element_type_id IN NUMBER, p_name In VARCHAR2) IS
483 SELECT input_value_id
484 FROM pay_input_values_f
485 WHERE element_type_id = p_element_type_id
486 AND name = p_name
487 AND nvl(business_group_id, p_business_group_id) = p_business_group_id
488 AND nvl(legislation_code, 'IE') = 'IE'
489 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
490 --
491 input_val_rec1 input_val_csr%ROWTYPE;
492 input_val_rec2 input_val_csr%ROWTYPE;
493 input_val_rec3 input_val_csr%ROWTYPE;
494 input_val_rec4 input_val_csr%ROWTYPE;
495 --
496 -- BUG 2616218
497 CURSOR link_csr(p_element_type_id IN NUMBER) IS
498 SELECT links.element_link_id
499 FROM pay_element_links_f links, per_all_assignments_f assign
500 WHERE links.element_type_id = p_element_type_id
501 AND links.business_group_id=p_business_group_id
502 AND assign.assignment_id=p_assignment_id
503 AND (( links.payroll_id is not null
504 and links.payroll_id = assign.payroll_id)
505 OR ( links.link_to_all_payrolls_flag='Y'
506 and assign.payroll_id is not null)
507 OR ( links.payroll_id is null
508 and links.link_to_all_payrolls_flag='N')
509 OR links.job_id=assign.job_id
510 OR links.position_id=assign.position_id
511 OR links.people_group_id=assign.people_group_id
512 OR links.organization_id=assign.organization_id
513 OR links.grade_id=assign.grade_id
514 OR links.location_id=assign.location_id
515 OR links.pay_basis_id=assign.pay_basis_id
516 OR links.employment_category=assign.employment_category)
517 AND p_effective_date BETWEEN links.effective_start_date
518 AND links.effective_end_date;
519 --
520 link_rec link_csr%ROWTYPE;
521 --
522 l_element_entry_id NUMBER;
523 l_effective_start_date DATE;
524 l_effectiver_end_date DATE;
525 l_object_version_number NUMBER;
526 l_create_warning BOOLEAN := FALSE;
527 begin
528 --
529 -- Get Element information
530 --
531 OPEN element_csr;
532 FETCH element_csr INTO element_rec;
533 CLOSE element_csr;
534 --
535 -- Get Input Values
536 --
537 OPEN input_val_csr(element_rec.element_type_id, 'Tax Deducted To Date');
538 FETCH input_val_csr INTO input_val_rec1;
539 CLOSE input_val_csr;
540 --
541 OPEN input_val_csr(element_rec.element_type_id, 'Pay To Date');
542 FETCH input_val_csr INTO input_val_rec2;
543 CLOSE input_val_csr;
544 --
545 OPEN input_val_csr(element_rec.element_type_id, 'Lump Sum Payment');
546 FETCH input_val_csr INTO input_val_rec3;
547 CLOSE input_val_csr;
548 --
549 OPEN input_val_csr(element_rec.element_type_id, 'Disability Benefit');
550 FETCH input_val_csr INTO input_val_rec4;
551 CLOSE input_val_csr;
552 --
553 -- Get element link information
554 --
555 OPEN link_csr(element_rec.element_type_id);
556 FETCH link_csr INTO link_rec;
557 CLOSE link_csr;
558 --
559 -- Call API To Create Balance Adjustment
560 --
561 pay_balance_adjustment_api.create_adjustment (
562 p_validate => false,
563 p_effective_date => p_effective_date,
564 p_assignment_id => p_assignment_id,
565 p_consolidation_set_id => NULL,
566 p_element_link_id => link_rec.element_link_id,
567 p_input_value_id1 => input_val_rec1.input_value_id,
568 p_input_value_id2 => input_val_rec2.input_value_id,
569 p_input_value_id3 => input_val_rec3.input_value_id,
570 p_input_value_id4 => input_val_rec4.input_value_id,
571 p_entry_value1 => nvl(p_tax_deducted_to_date,0),
572 p_entry_value2 => nvl(p_pay_to_date,0),
573 p_entry_value3 => nvl(p_lump_sum_payment,0),
574 p_entry_value4 => nvl(p_disability_benefit,0),
575 -- Element entry information.
576 p_element_entry_id => l_element_entry_id,
577 p_effective_start_date => l_effective_start_date,
578 p_effective_end_date => l_effectiver_end_date,
579 p_object_version_number => l_object_version_number,
580 p_create_warning => l_create_warning );
581 end create_bal_adj;
582 --
583 -- ----------------------------------------------------------------------------
584 -- |--------------------------< delete_bal_adj >------------------------------|
585 -- ----------------------------------------------------------------------------
586 --
587 PROCEDURE delete_bal_adj (p_effective_date IN DATE
588 ,p_business_group_id IN NUMBER
589 ,p_assignment_id IN NUMBER ) IS
590 /* commented to fix bug 3013304
591 we now delete all the balances whihc are created within the tax year
592 rather than just deleting the one.
593
594 CURSOR entry_csr IS
595 SELECT pee.element_entry_id
596 FROM pay_element_entries_f pee, pay_element_types_f pet, pay_element_links_f pel
597 WHERE pee.element_link_id = pel.element_link_id
598 AND pee.assignment_id = p_assignment_id
599 AND p_effective_date BETWEEN pee.effective_start_Date AND pee.effective_end_Date
600 AND pel.element_type_id = pet.element_type_id
601 AND pel.business_group_id = p_business_group_id
602 AND p_effective_date BETWEEN pel.effective_start_Date AND pel.effective_end_Date
603 AND pet.element_name = 'IE P45 Information'
604 AND nvl(pet.business_group_id, p_business_group_id) = p_business_group_id
605 AND pet.legislation_code = 'IE'
606 AND p_effective_date BETWEEN pet.effective_start_Date AND pet.effective_end_Date;
607 */
608 --
609 -- entry_rec entry_csr%ROWTYPE;
610 l_tax_yr_start_date date;
611 CURSOR entry_csr IS
612 SELECT pee.element_entry_id, pee.effective_start_date
613 FROM pay_element_entries_f pee,
614 pay_element_types_f pet,
615 pay_element_links_f pel
616 WHERE pee.element_link_id = pel.element_link_id AND
617 pee.assignment_id = p_assignment_id AND
618 pee.effective_start_date >= l_tax_yr_start_date and
619 pee.effective_end_date <= add_months(l_tax_yr_start_date,12) AND
620 pel.element_type_id = pet.element_type_id AND
621 pel.business_group_id = p_business_group_id AND
622 -- pet.element_name = 'IE P45 Information' AND
623 pet.element_name in('IE P45 Information', 'Setup P45 Element') AND
624 NVL(pet.business_group_id, p_business_group_id) = p_business_group_id AND
625 pet.legislation_code = 'IE' ;
626 BEGIN
627 -- Get ELement Entry Information
628 -- derive the tax year start date givevn the effective data
629 l_tax_yr_start_date := to_date('01/01/' || to_char(p_effective_date,'YYYY'),'DD/MM/YYYY');
630 for element_entries in entry_csr loop
631 --
632 IF element_entries.element_entry_id IS NOT NULL THEN
633 -- Call Delete Adjustment API to rollback payroll action for this element entry
634 pay_balance_adjustment_api.delete_adjustment (
635 p_validate => false,
636 p_effective_date => element_entries.effective_start_date,
637 p_element_entry_id => element_entries.element_entry_id );
638 END IF;
639 end loop;
640 END delete_bal_adj;
641 --
642 -- ----------------------------------------------------------------------------
643 -- |------------------------< create_ie_paye_details >------------------------|
644 -- ----------------------------------------------------------------------------
645 --
646 procedure create_ie_paye_details
647 (p_validate in boolean
648 ,p_effective_date in date
649 ,p_assignment_id in number
650 ,p_info_source in varchar2
651 ,p_tax_basis in varchar2
652 ,p_certificate_start_date in date
653 ,p_tax_assess_basis in varchar2
654 ,p_certificate_issue_date in date
655 ,p_certificate_end_date in date
656 ,p_weekly_tax_credit in number
657 ,p_weekly_std_rate_cut_off in number
658 ,p_monthly_tax_credit in number
659 ,p_monthly_std_rate_cut_off in number
660 ,p_tax_deducted_to_date in number
661 ,p_pay_to_date in number
662 ,p_disability_benefit in number
663 ,p_lump_sum_payment in number
664 ,p_paye_details_id out nocopy number
665 ,p_object_version_number out nocopy number
666 ,p_effective_start_date out nocopy date
667 ,p_effective_end_date out nocopy date
668 ,p_Tax_This_Employment in Number
669 ,p_Previous_Employment_Start_Dt in date
670 ,p_Previous_Employment_End_Date in date
671 ,p_Pay_This_Employment in number
672 ,p_PAYE_Previous_Employer in varchar2
673 ,p_P45P3_Or_P46 in varchar2
674 ,p_Already_Submitted in varchar2
675 --,p_P45P3_Or_P46_Processed in varchar2
676 ) is
677 --
678 -- Declare cursors and local variables
679 --
680
681 l_proc varchar2(72) := g_package||'create_ie_paye_details';
682 l_certificate_start_date date;
683 l_certificate_issue_date date;
684 l_certificate_end_date date;
685 l_paye_details_id number;
686 l_object_version_number number;
687 l_effective_start_date date;
688 l_effective_end_Date date;
689 l_comm_period_no number;
690 l_request_id number;
691 l_program_id number;
692 l_prog_appl_id number;
693 l_business_group_id number;
694 --
695 CURSOR business_group_csr IS
696 SELECT business_group_id
697 FROM per_all_assignments_f
698 WHERE assignment_id = p_assignment_id
699 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
700 --
701 --
702 begin
703 hr_utility.set_location('Entering:'|| l_proc, 10);
704 --
705 -- Issue a savepoint
706 --
707 savepoint create_ie_paye_details;
708 --
709 -- Truncate the time portion from all IN date parameters
710 --
711 l_certificate_start_date := trunc(p_certificate_start_date);
712 l_certificate_issue_date := trunc(p_certificate_issue_date);
713 l_certificate_end_date := trunc(p_certificate_end_date);
714 --
715 -- Get business_group_id
716 --
717 OPEN business_group_csr;
718 FETCH business_group_csr INTO l_business_group_id;
719 CLOSE business_group_csr;
720 --
721 -- Call Before Process User Hook
722 --
723 begin
724 pay_ie_paye_bk1.create_ie_paye_details_b
725 (p_effective_date => p_effective_date
726 ,p_business_group_id => l_business_group_id
727 ,p_assignment_id => p_assignment_id
728 ,p_info_source => p_info_source
729 ,p_tax_basis => p_tax_basis
730 ,p_certificate_start_date => p_certificate_start_date
731 ,p_tax_assess_basis => p_tax_assess_basis
732 ,p_certificate_issue_date => p_certificate_issue_date
733 ,p_certificate_end_date => p_certificate_end_date
734 ,p_weekly_tax_credit => p_weekly_tax_credit
735 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
736 ,p_monthly_tax_credit => p_monthly_tax_credit
737 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
738 ,p_tax_deducted_to_date => p_tax_deducted_to_date
739 ,p_pay_to_date => p_pay_to_date
740 ,p_disability_benefit => p_disability_benefit
741 ,p_lump_sum_payment => p_lump_sum_payment
742 ,p_Tax_This_Employment => p_Tax_This_Employment
743 ,p_Previous_Employment_Start_Dt => p_Previous_Employment_Start_Dt
744 ,p_Previous_Employment_End_Date => p_Previous_Employment_End_Date
745 ,p_Pay_This_Employment => p_Pay_This_Employment
746 ,p_PAYE_Previous_Employer => p_PAYE_Previous_Employer
747 ,p_P45P3_Or_P46 => p_P45P3_Or_P46
748 ,p_Already_Submitted => p_Already_Submitted
749 --,p_P45P3_Or_P46_Processed => p_P45P3_Or_P46_Processed
750 );
751 exception
752 when hr_api.cannot_find_prog_unit then
753 hr_api.cannot_find_prog_unit_error
754 (p_module_name => 'create_ie_paye_details'
755 ,p_hook_type => 'BP'
756 );
757 end;
758 --
759 -- Process Logic
760 --
761 -- Set parameter values
762 --
763 l_request_id := fnd_global.conc_request_id;
764 l_prog_appl_id := fnd_global.prog_appl_id;
765 l_program_id := fnd_global.conc_program_id;
766 l_comm_period_no := pay_ipd_bus.get_comm_period_no(p_effective_date => p_effective_date , p_assignment_id => p_assignment_id);
767 --
768 -- Insert record in pay_ie_paye_details_f
769 --
770 pay_ipd_ins.ins
771 ( p_effective_date => p_effective_date
772 ,p_assignment_id => p_assignment_id
773 ,p_info_source => p_info_source
774 ,p_comm_period_no => l_comm_period_no
775 ,p_tax_basis => p_tax_basis
776 ,p_certificate_start_date => p_certificate_start_Date
777 ,p_tax_assess_basis => p_tax_assess_basis
778 ,p_certificate_end_date => p_certificate_end_date
779 ,p_weekly_tax_credit => p_weekly_tax_credit
780 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
781 ,p_monthly_tax_credit => p_monthly_tax_credit
782 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
783 ,p_request_id => l_request_id
784 ,p_program_application_id => l_prog_appl_id
785 ,p_program_id => l_program_id
786 ,p_program_update_date => sysdate
787 ,p_paye_details_id => l_paye_details_id
788 ,p_object_version_number => l_object_version_number
789 ,p_effective_start_date => l_effective_start_date
790 ,p_effective_end_date => l_effective_end_date
791 ,p_certificate_issue_date => p_certificate_issue_Date
792 );
793 --
794 -- Check if adjustments need to be created
795 IF ( nvl(p_tax_deducted_to_date,0) <> 0 OR
796 nvl(p_pay_to_date,0) <> 0 OR
797 nvl(p_disability_benefit,0) <> 0 OR
798 nvl(p_lump_sum_payment,0) <> 0) THEN
799
800 -- delete any bal adj entires which could have
801 -- been crreated by the bal adj screen
802 delete_bal_adj ( p_effective_date => P_EFFECTIVE_DATE
803 ,p_business_group_id => l_business_group_id
804 ,p_assignment_id => p_assignment_id );
805
806
807 -- Create P45 Balance Adjustments
808 create_bal_adj ( p_effective_date => p_effective_date
809 , p_business_group_id => l_business_group_id
810 , p_assignment_id => p_assignment_id
811 , p_tax_deducted_to_date => p_tax_deducted_to_date
812 , p_pay_to_date => p_pay_to_date
813 , p_disability_benefit => p_disability_benefit
814 , p_lump_sum_payment => p_lump_sum_payment );
815 END IF;
816 --
817 --6015209
818 IF ( nvl(p_Tax_This_Employment,0) <> 0 OR
819 nvl(p_Pay_This_Employment,0) <> 0 OR
820 p_Previous_Employment_Start_Dt IS NOT NULL OR
821 p_Previous_Employment_End_Date IS NOT NULL OR
822 p_PAYE_Previous_Employer IS NOT NULL OR
823 p_Already_Submitted IS NOT NULL OR
824 p_P45P3_Or_P46 IS NOT NULL
825 )
826 THEN
827 create_p46 ( p_effective_date
828 , p_assignment_id
829 , l_business_group_id
830 , p_Tax_This_Employment
831 , p_Previous_Employment_Start_Dt
832 , p_Previous_Employment_End_Date
833 , p_Pay_This_Employment
834 , p_PAYE_Previous_Employer
835 , p_P45P3_Or_P46
836 , p_Already_Submitted
837 --, p_P45P3_Or_P46_Processed
838 );
839 END IF;
840 --6015209
841
842 -- Call After Process User Hook
843 --
844 begin
845 pay_ie_paye_bk1.create_ie_paye_details_a
846 (p_effective_date => p_effective_date
847 ,p_business_group_id => l_business_group_id
848 ,p_assignment_id => p_assignment_id
849 ,p_info_source => p_info_source
850 ,p_tax_basis => p_tax_basis
851 ,p_certificate_start_date => p_certificate_start_date
852 ,p_tax_assess_basis => p_tax_assess_basis
853 ,p_certificate_issue_date => p_certificate_issue_date
854 ,p_certificate_end_date => p_certificate_end_date
855 ,p_weekly_tax_credit => p_weekly_tax_credit
856 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
857 ,p_monthly_tax_credit => p_monthly_tax_credit
858 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
859 ,p_tax_deducted_to_date => p_tax_deducted_to_date
860 ,p_pay_to_date => p_pay_to_date
861 ,p_disability_benefit => p_disability_benefit
862 ,p_lump_sum_payment => p_lump_sum_payment
863 ,p_paye_details_id => l_paye_details_id
864 ,p_object_version_number => l_object_version_number
865 ,p_effective_start_date => l_effective_start_date
866 ,p_effective_end_date => l_effective_end_date
867 ,p_Tax_This_Employment => p_Tax_This_Employment
868 ,p_Previous_Employment_Start_Dt => p_Previous_Employment_Start_Dt
869 ,p_Previous_Employment_End_Date => p_Previous_Employment_End_Date
870 ,p_Pay_This_Employment => p_Pay_This_Employment
871 ,p_PAYE_Previous_Employer => p_PAYE_Previous_Employer
872 ,p_P45P3_Or_P46 => p_P45P3_Or_P46
873 ,p_Already_Submitted => p_Already_Submitted
874 --,p_P45P3_Or_P46_Processed => p_P45P3_Or_P46_Processed
875 );
876 exception
877 when hr_api.cannot_find_prog_unit then
878 hr_api.cannot_find_prog_unit_error
879 (p_module_name => 'create_ie_paye_details'
880 ,p_hook_type => 'AP'
881 );
882 end;
883 --
884 -- When in validation only mode raise the Validate_Enabled exception
885 --
886 if p_validate then
887 raise hr_api.validate_enabled;
888 end if;
889 --
890 -- Set all output arguments
891 --
892 p_paye_details_id := l_paye_details_id;
893 p_object_version_number := l_object_version_number;
894 p_effective_start_date := l_effective_start_date;
895 p_effective_end_date := l_effective_end_Date;
896 --
897 hr_utility.set_location(' Leaving:'||l_proc, 70);
898 exception
899 when hr_api.validate_enabled then
900 --
901 -- As the Validate_Enabled exception has been raised
902 -- we must rollback to the savepoint
903 --
904 rollback to create_ie_paye_details;
905 --
906 -- Only set output warning arguments
907 -- (Any key or derived arguments must be set to null
908 -- when validation only mode is being used.)
909 --
910 p_paye_details_id := null;
911 p_object_version_number := null;
912 p_effective_start_date := null;
913 p_effective_end_Date := null;
914 hr_utility.set_location(' Leaving:'||l_proc, 80);
915 when others then
916 --
917 -- A validation or unexpected error has occured
918 --
919 rollback to create_ie_paye_details;
920 p_paye_details_id := null;
921 p_object_version_number := null;
922 p_effective_start_date := null;
923 p_effective_end_Date := null;
924 hr_utility.set_location(' Leaving:'||l_proc, 90);
925 raise;
926 end create_ie_paye_details;
927 --
928 --
929 -- ----------------------------------------------------------------------------
930 -- |------------------------< update_ie_paye_details >------------------------|
931 -- ----------------------------------------------------------------------------
932 --
933 procedure update_ie_paye_details
934 (p_validate in boolean
935 ,p_effective_date in date
936 ,p_datetrack_update_mode in varchar2
937 ,p_paye_details_id in number
938 ,p_info_source in varchar2
939 ,p_tax_basis in varchar2
940 ,p_certificate_start_date in date
941 ,p_tax_assess_basis in varchar2
942 ,p_certificate_issue_date in date default null
943 ,p_certificate_end_date in date default null
944 ,p_weekly_tax_credit in number default null
945 ,p_weekly_std_rate_cut_off in number default null
946 ,p_monthly_tax_credit in number default null
947 ,p_monthly_std_rate_cut_off in number default null
948 ,p_tax_deducted_to_date in number default null
949 ,p_pay_to_date in number default null
950 ,p_disability_benefit in number default null
951 ,p_lump_sum_payment in number default null
952 ,p_object_version_number in out nocopy number
953 ,p_effective_start_date out nocopy date
954 ,p_effective_end_date out nocopy date
955 ,p_Tax_This_Employment in Number
956 ,p_Previous_Employment_Start_Dt in date
957 ,p_Previous_Employment_End_Date in date
958 ,p_Pay_This_Employment in number
959 ,p_PAYE_Previous_Employer in varchar2
960 ,p_P45P3_Or_P46 in varchar2
961 ,p_Already_Submitted in varchar2
962 --,p_P45P3_Or_P46_Processed in varchar2
963 ) IS
964 --
965 -- Declare cursors and local variables
966 --
967
968 l_proc varchar2(72) := g_package||'update_ie_paye_details';
969 l_certificate_start_date date;
970 l_certificate_issue_date date;
971 l_certificate_end_date date;
972 l_object_version_number number := p_object_version_number;
973 l_effective_start_date date;
974 l_effective_end_Date date;
975 l_request_id number;
976 l_program_id number;
977 l_prog_appl_id number;
978 l_p45_effective_date date;
979 l_assignment_id number;
980 l_business_group_id number;
981 --
982 CURSOR asg_csr IS
983 SELECT assignment_id
984 FROM pay_ie_paye_details_f
985 WHERE paye_details_id = p_paye_details_id
986 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
987 --
988 CURSOR business_group_csr IS
989 SELECT business_group_id
990 FROM per_all_assignments_f
991 WHERE assignment_id = l_assignment_id
992 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
993
994 --6015209
995 --l_datetrack_delete_mode VARCHAR2(100) := 'ZAP';
996 l_object_version_p45p3 NUMBER;
997
998 -- Checking if any future record exist for p45 record with in the Year.
999 l_tax_yr_start_date date;
1000 /* CURSOR future_entry_csr IS
1001 SELECT pee.element_entry_id, pee.effective_start_date
1002 FROM pay_element_entries_f pee,
1003 pay_element_types_f pet,
1004 pay_element_links_f pel
1005 WHERE pee.element_link_id = pel.element_link_id AND
1006 pee.assignment_id = l_assignment_id AND
1007 pee.effective_start_date >= l_tax_yr_start_date and
1008 pee.effective_end_date <= add_months(l_tax_yr_start_date,12) AND
1009 (pee.effective_start_date > p_effective_date OR
1010 p_effective_date Between pee.effective_start_date AND pee.effective_end_date) AND
1011 pel.element_type_id = pet.element_type_id AND
1012 pel.business_group_id = l_business_group_id AND
1013 pet.element_name in('IE P45 Information', 'Setup P45 Element') AND
1014 NVL(pet.business_group_id, l_business_group_id) = l_business_group_id AND
1015 pet.legislation_code = 'IE' ; */
1016
1017 CURSOR future_entry_csr IS
1018 select 1
1019 from
1020 pay_payroll_actions ppa,
1021 pay_assignment_actions paa,
1022 pay_run_results prr,
1023 pay_run_result_values prrv,
1024 pay_element_types_f petf,
1025 pay_element_entries_f peef
1026 where ppa.business_group_id = l_business_group_id
1027 and ppa.action_type = 'B'
1028 and ppa.action_status = 'C'
1029 and ppa.payroll_action_id = paa.payroll_action_id
1030 and paa.assignment_id = l_assignment_id
1031 and prr.assignment_action_id = paa.assignment_action_id
1032 and prr.entry_type = 'B'
1033 and prr.run_result_id = prrv.run_result_id
1034 and prr.element_type_id = petf.element_type_id
1035 and NVL(petf.business_group_id, l_business_group_id) = l_business_group_id
1036 and petf.element_name in('IE P45 Information', 'Setup P45 Element')
1037 and petf.legislation_code = 'IE'
1038 and peef.element_type_id = petf.element_type_id
1039 and peef.assignment_id = paa.assignment_id
1040 and peef.effective_start_date >= l_tax_yr_start_date
1041 and peef.effective_end_date <= add_months(l_tax_yr_start_date,12)
1042 and peef.entry_type = 'B'
1043 and peef.element_entry_id = prr.element_entry_id
1044 and ppa.effective_date > p_effective_date;
1045
1046 rec_future_entry_exist future_entry_csr%rowtype;
1047
1048 --6015209
1049 begin
1050 hr_utility.set_location('Entering:'|| l_proc, 10);
1051 l_tax_yr_start_date := to_date('01/01/' || to_char(p_effective_date,'YYYY'),'DD/MM/YYYY'); --6015209
1052 --
1053 -- Issue a savepoint
1054 --
1055 savepoint update_ie_paye_details;
1056 --
1057 -- Truncate the time portion from all IN date parameters
1058 --
1059 l_certificate_start_date := trunc(p_certificate_start_date);
1060 l_certificate_issue_date := trunc(p_certificate_issue_date);
1061 l_certificate_end_date := trunc(p_certificate_end_date);
1062 --
1063 -- Get assignment_id from the cursor
1064 OPEN asg_csr;
1065 FETCH asg_csr INTO l_assignment_id;
1066 CLOSE asg_csr;
1067 --
1068 -- Get Business Group Id
1069 --
1070 OPEN business_group_csr;
1071 FETCH business_group_csr INTO l_business_group_id;
1072 CLOSE business_group_csr;
1073 --
1074 -- Call Before Process User Hook
1075 --
1076 begin
1077 hr_utility.set_location('before pay_ie_paye_bk2.update_ie_paye_details_b', 2001);
1078 pay_ie_paye_bk2.update_ie_paye_details_b
1079 (p_effective_date => p_effective_date
1080 ,p_datetrack_update_mode => p_datetrack_update_mode
1081 ,p_business_group_id => l_business_group_id
1082 ,p_paye_details_id => p_paye_details_id
1083 ,p_info_source => p_info_source
1084 ,p_tax_basis => p_tax_basis
1085 ,p_certificate_start_date => p_certificate_start_date
1086 ,p_tax_assess_basis => p_tax_assess_basis
1087 ,p_certificate_issue_date => p_certificate_issue_date
1088 ,p_certificate_end_date => p_certificate_end_date
1089 ,p_weekly_tax_credit => p_weekly_tax_credit
1090 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1091 ,p_monthly_tax_credit => p_monthly_tax_credit
1092 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1093 ,p_tax_deducted_to_date => p_tax_deducted_to_date
1094 ,p_pay_to_date => p_pay_to_date
1095 ,p_disability_benefit => p_disability_benefit
1096 ,p_lump_sum_payment => p_lump_sum_payment
1097 ,p_object_version_number => l_object_version_number
1098 ,p_Tax_This_Employment => p_Tax_This_Employment
1099 ,p_Previous_Employment_Start_Dt => p_Previous_Employment_Start_Dt
1100 ,p_Previous_Employment_End_Date => p_Previous_Employment_End_Date
1101 ,p_Pay_This_Employment => p_Pay_This_Employment
1102 ,p_PAYE_Previous_Employer => p_PAYE_Previous_Employer
1103 ,p_P45P3_Or_P46 => p_P45P3_Or_P46
1104 ,p_Already_Submitted => p_Already_Submitted
1105 --,p_P45P3_Or_P46_Processed => p_P45P3_Or_P46_Processed
1106 );
1107 hr_utility.set_location('after pay_ie_paye_bk2.update_ie_paye_details_b', 2001);
1108 exception
1109 when hr_api.cannot_find_prog_unit then
1110 hr_api.cannot_find_prog_unit_error
1111 (p_module_name => 'update_ie_paye_details'
1112 ,p_hook_type => 'BP'
1113 );
1114 end;
1115 --
1116 -- Process Logic
1117 --
1118 -- Set parameter values
1119 --
1120 l_request_id := fnd_global.conc_request_id;
1121 l_prog_appl_id := fnd_global.prog_appl_id;
1122 l_program_id := fnd_global.conc_program_id;
1123 --
1124 -- Call row handler procedure to update paye details
1125 --
1126 hr_utility.set_location('before pay_ipd_upd.upd', 2002);
1127 pay_ipd_upd.upd
1128 ( p_effective_date => p_effective_date
1129 ,p_datetrack_mode => p_datetrack_update_mode
1130 ,p_paye_details_id => p_paye_details_id
1131 ,p_object_version_number => l_object_version_number
1132 ,p_info_source => p_info_source
1133 ,p_tax_basis => p_tax_basis
1134 ,p_certificate_start_date => p_certificate_start_Date
1135 ,p_tax_assess_basis => p_tax_assess_basis
1136 ,p_certificate_end_date => p_certificate_end_date
1137 ,p_weekly_tax_credit => p_weekly_tax_credit
1138 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1139 ,p_monthly_tax_credit => p_monthly_tax_credit
1140 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1141 ,p_request_id => l_request_id
1142 ,p_program_application_id => l_prog_appl_id
1143 ,p_program_id => l_program_id
1144 ,p_program_update_date => sysdate
1145 ,p_effective_start_date => l_effective_start_date
1146 ,p_effective_end_date => l_effective_end_date
1147 ,p_certificate_issue_date => p_certificate_issue_Date
1148 );
1149 hr_utility.set_location('after pay_ipd_upd.upd', 2002);
1150 --
1151 -- Get effective date of the P45 balance adjustment
1152 -- If PAYE details record first started before current fiscal year then
1153 -- P45 adjustment effective date is the first day of the fiscal year
1154 -- the below code assumed that the P45 info could be entered only via TAx form
1155 -- which is incorrect., it could be entered by balance adjusment form
1156 -- This resulted in bug 3013304
1157 -- we now deleet all bal adj with the effective date tax year
1158 /*
1159 SELECT min(effective_start_date)
1160 INTO l_p45_effective_date
1161 FROM pay_ie_paye_details_f
1162 WHERE paye_details_id = p_paye_details_id;
1163
1164 --
1165 IF l_p45_effective_date < to_date('01-JAN-'||to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY') THEN
1166 l_p45_effective_date := to_date('01-JAN-'||to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY') ;
1167 END IF;
1168 */
1169 --
1170
1171 OPEN future_entry_csr;
1172 FETCH future_entry_csr INTO rec_future_entry_exist;
1173 IF future_entry_csr%NOTFOUND AND ( nvl(p_tax_deducted_to_date,0) = 0 AND
1174 nvl(p_pay_to_date,0) = 0 AND
1175 nvl(p_disability_benefit,0) = 0 AND
1176 nvl(p_lump_sum_payment,0) = 0
1177 )
1178 THEN
1179 delete_bal_adj ( p_effective_date => P_EFFECTIVE_DATE
1180 ,p_business_group_id => l_business_group_id
1181 ,p_assignment_id => l_assignment_id );
1182 ELSIF ( nvl(p_tax_deducted_to_date,0) <> 0 OR
1183 nvl(p_pay_to_date,0) <> 0 OR
1184 nvl(p_disability_benefit,0) <> 0 OR
1185 nvl(p_lump_sum_payment,0) <> 0) THEN
1186 ---Check if adjustments need to be created
1187 IF ( nvl(p_tax_deducted_to_date,0) <> hr_api.g_number OR
1188 nvl(p_pay_to_date,0) <> hr_api.g_number OR
1189 nvl(p_disability_benefit,0) <> hr_api.g_number OR
1190 nvl(p_lump_sum_payment,0) <> hr_api.g_number) THEN
1191 -- Delete previous adjustments
1192 delete_bal_adj ( p_effective_date => P_EFFECTIVE_DATE
1193 ,p_business_group_id => l_business_group_id
1194 ,p_assignment_id => l_assignment_id );
1195 --
1196 -- Check if new balance adjustment entry needs to be crated
1197 --
1198 IF ( nvl(p_tax_deducted_to_date,0) <> 0 OR
1199 nvl(p_pay_to_date,0) <> 0 OR
1200 nvl(p_disability_benefit,0) <> 0 OR
1201 nvl(p_lump_sum_payment,0) <> 0) THEN
1202 --Create new P45 Balance Adjustments
1203 create_bal_adj ( p_effective_date => P_EFFECTIVE_DATE -- l_p45_effective_date
1204 , p_business_group_id => l_business_group_id
1205 , p_assignment_id => l_assignment_id
1206 , p_tax_deducted_to_date => p_tax_deducted_to_date
1207 , p_pay_to_date => p_pay_to_date
1208 , p_disability_benefit => p_disability_benefit
1209 , p_lump_sum_payment => p_lump_sum_payment );
1210 END IF;
1211 END IF;
1212 END IF;
1213 CLOSE future_entry_csr;
1214 --END IF;
1215
1216 hr_utility.set_location('before update_p46', 2003);
1217
1218 --6015209
1219 IF ( nvl(p_Tax_This_Employment,0) <> 0 OR
1220 nvl(p_Pay_This_Employment,0) <> 0 OR
1221 p_Previous_Employment_Start_Dt IS NOT NULL OR
1222 p_Previous_Employment_End_Date IS NOT NULL OR
1223 p_PAYE_Previous_Employer IS NOT NULL OR
1224 p_Already_Submitted IS NOT NULL OR
1225 p_P45P3_Or_P46 IS NOT NULL
1226 )
1227 THEN
1228 update_p46 ( p_effective_date
1229 , l_assignment_id
1230 , l_business_group_id
1231 , p_datetrack_update_mode
1232 , l_object_version_p45p3
1233 , p_paye_details_id
1234 , p_Tax_This_Employment
1235 , p_Previous_Employment_Start_Dt
1236 , p_Previous_Employment_End_Date
1237 , p_Pay_This_Employment
1238 , p_PAYE_Previous_Employer
1239 , p_P45P3_Or_P46
1240 , p_Already_Submitted
1241 --, p_P45P3_Or_P46_Processed
1242 );
1243 END IF;
1244 --6015209
1245
1246 hr_utility.set_location('after update_p46', 2003);
1247
1248 --
1249 -- Call After Process User Hook
1250 --
1251 begin
1252 hr_utility.set_location('before pay_ie_paye_bk2.update_ie_paye_details_a', 2004);
1253 pay_ie_paye_bk2.update_ie_paye_details_a
1254 (p_effective_date => p_effective_date
1255 ,p_business_group_id => l_business_group_id
1256 ,p_datetrack_update_mode => p_datetrack_update_mode
1257 ,p_paye_details_id => p_paye_details_id
1258 ,p_info_source => p_info_source
1259 ,p_tax_basis => p_tax_basis
1260 ,p_certificate_start_date => p_certificate_start_date
1261 ,p_tax_assess_basis => p_tax_assess_basis
1262 ,p_certificate_issue_date => p_certificate_issue_date
1263 ,p_certificate_end_date => p_certificate_end_date
1264 ,p_weekly_tax_credit => p_weekly_tax_credit
1265 ,p_weekly_std_rate_cut_off => p_weekly_std_rate_cut_off
1266 ,p_monthly_tax_credit => p_monthly_tax_credit
1267 ,p_monthly_std_rate_cut_off => p_monthly_std_rate_cut_off
1268 ,p_tax_deducted_to_date => p_tax_deducted_to_date
1269 ,p_pay_to_date => p_pay_to_date
1270 ,p_disability_benefit => p_disability_benefit
1271 ,p_lump_sum_payment => p_lump_sum_payment
1272 ,p_object_version_number => l_object_version_number
1273 ,p_effective_start_date => l_effective_start_date
1274 ,p_effective_end_date => l_effective_end_date
1275 ,p_Tax_This_Employment => p_Tax_This_Employment
1276 ,p_Previous_Employment_Start_Dt => p_Previous_Employment_Start_Dt
1277 ,p_Previous_Employment_End_Date => p_Previous_Employment_End_Date
1278 ,p_Pay_This_Employment => p_Pay_This_Employment
1279 ,p_PAYE_Previous_Employer => p_PAYE_Previous_Employer
1280 ,p_P45P3_Or_P46 => p_P45P3_Or_P46
1281 ,p_Already_Submitted => p_Already_Submitted
1282 --,p_P45P3_Or_P46_Processed => p_P45P3_Or_P46_Processed
1283 );
1284
1285 hr_utility.set_location('after pay_ie_paye_bk2.update_ie_paye_details_a', 2004);
1286 exception
1287 when hr_api.cannot_find_prog_unit then
1288 hr_api.cannot_find_prog_unit_error
1289 (p_module_name => 'update_ie_paye_details'
1290 ,p_hook_type => 'AP'
1291 );
1292 end;
1293 --
1294 -- When in validation only mode raise the Validate_Enabled exception
1295 --
1296 hr_utility.set_location('before p_validate', 2005);
1297 if p_validate then
1298 raise hr_api.validate_enabled;
1299 end if;
1300 hr_utility.set_location('after p_validate', 2005);
1301 --
1302 -- Set all output arguments
1303 --
1304 p_object_version_number := l_object_version_number;
1305 p_effective_start_date := l_effective_start_date;
1306 p_effective_end_date := l_effective_end_Date;
1307 --
1308 hr_utility.set_location(' Leaving:'||l_proc, 70);
1309 exception
1310 when hr_api.validate_enabled then
1311 --
1312 -- As the Validate_Enabled exception has been raised
1313 -- we must rollback to the savepoint
1314 --
1315 hr_utility.set_location('Inside when hr_api.validate_enabled', 2009);
1316 rollback to update_ie_paye_details;
1317 --
1318 -- Only set output warning arguments
1319 -- (Any key or derived arguments must be set to null
1320 -- when validation only mode is being used.)
1321 --
1322 -- IN OUT parameter should be reset to its IN value
1323 -- therefore no need to reset p_object_version_number
1324 p_object_version_number := l_object_version_number;
1325 p_effective_start_date := null;
1326 p_effective_end_Date := null;
1327 hr_utility.set_location(' Leaving:'||l_proc, 80);
1328 when others then
1329 --
1330 -- A validation or unexpected error has occured
1331 --
1332 hr_utility.set_location('Inside when others', 2010);
1333 rollback to update_ie_paye_details;
1334 p_object_version_number := l_object_version_number;
1335 p_effective_start_date := null;
1336 p_effective_end_Date := null;
1337 hr_utility.set_location(' Leaving:'||l_proc, 90);
1338 raise;
1339 end update_ie_paye_details;
1340
1341 --
1342 -- ----------------------------------------------------------------------------
1343 -- |------------------------< delete_ie_paye_details >------------------------|
1344 -- ----------------------------------------------------------------------------
1345 --
1346 procedure delete_ie_paye_details
1347 (p_validate in boolean
1348 ,p_effective_date in date
1349 ,p_datetrack_delete_mode in varchar2
1350 ,p_paye_details_id in number
1351 ,p_object_version_number in out nocopy number
1352 ,p_effective_start_date out nocopy date
1353 ,p_effective_end_date out nocopy date
1354 ) IS
1355 --
1356 -- Declare cursors and local variables
1357 --
1358
1359 l_proc varchar2(72) := g_package||'delete_ie_paye_details';
1360 l_object_version_number number := p_object_version_number;
1361 l_effective_start_date date;
1362 l_effective_end_Date date;
1363 l_p45_effective_date date;
1364 l_assignment_id number;
1365 l_business_group_id number;
1366 --
1367 CURSOR asg_csr IS
1368 SELECT assignment_id
1369 FROM pay_ie_paye_details_f
1370 WHERE paye_details_id = p_paye_details_id
1371 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1372 --
1373 CURSOR business_group_csr IS
1374 SELECT business_group_id
1375 FROM per_all_assignments_f
1376 WHERE assignment_id = l_assignment_id
1377 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1378 --
1379 l_object_version_p45p3 NUMBER;
1380 --
1381 begin
1382 --hr_utility.trace_on(NULL,'RSS');
1383 hr_utility.set_location('Entering:'|| l_proc, 10);
1384 --
1385 -- Issue a savepoint
1386 --
1387 savepoint delete_ie_paye_details;
1388 --
1389 -- Get assignment_id from the cursor
1390 OPEN asg_csr;
1391 FETCH asg_csr INTO l_assignment_id;
1392 CLOSE asg_csr;
1393 --
1394 -- Get Business_group_id
1395 OPEN business_group_csr;
1396 FETCH business_group_csr INTO l_business_group_id;
1397 CLOSE business_group_csr;
1398 --
1399 --
1400 -- Call Before Process User Hook
1401 --
1402 begin
1403 hr_utility.set_location('before pay_ie_paye_bk3.delete_ie_paye_details_b', 1000);
1404 pay_ie_paye_bk3.delete_ie_paye_details_b
1405 (p_effective_date => p_effective_date
1406 ,p_datetrack_delete_mode => p_datetrack_delete_mode
1407 ,p_business_group_id => l_business_group_id
1408 ,p_paye_details_id => p_paye_details_id
1409 ,p_object_version_number => l_object_version_number
1410 );
1411 hr_utility.set_location('after pay_ie_paye_bk3.delete_ie_paye_details_b', 1000);
1412 exception
1413 when hr_api.cannot_find_prog_unit then
1414 hr_api.cannot_find_prog_unit_error
1415 (p_module_name => 'delete_ie_paye_details'
1416 ,p_hook_type => 'BP'
1417 );
1418 end;
1419 --
1420 --BUG NO:2683086 Moved the call to procedure after call to delete_bal_adj procedure.
1421 /*
1422 -- Process Logic
1423 --
1424 -- Call row handler procedure to update paye details
1425 --
1426 pay_ipd_del.del
1427 ( p_effective_date => p_effective_date
1428 ,p_datetrack_mode => p_datetrack_delete_mode
1429 ,p_paye_details_id => p_paye_details_id
1430 ,p_object_version_number => l_object_version_number
1431 ,p_effective_start_date => l_effective_start_date
1432 ,p_effective_end_date => l_effective_end_date
1433 );
1434 */
1435 --
1436 -- Get effective date of the P45 balance adjustment
1437 -- If PAYE details record first started before current fiscal year then
1438 -- P45 adjustment effective date is the first day of the fiscal year
1439 -- the below code assumed that the P45 info could be entered only via TAx form
1440 -- which is incorrect., it could be entered by balance adjusment form
1441 -- This resulted in bug 3013304
1442 -- we now deleet all bal adj with the effective date tax year
1443 /*
1444 SELECT min(effective_start_date)
1445 INTO l_p45_effective_date
1446 FROM pay_ie_paye_details_f
1447 WHERE paye_details_id = p_paye_details_id;
1448 --
1449 IF l_p45_effective_date < to_date('01-JAN-'||to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY') THEN
1450 l_p45_effective_date := to_date('01-JAN-'||to_char(p_effective_date,'YYYY'), 'DD/MM/YYYY') ;
1451 END IF;
1452 */
1453 --
1454 -- Check if adjustments need to be deleted
1455 IF ( p_datetrack_delete_mode = hr_api.g_zap) THEN
1456 -- Delete previous adjustments
1457 delete_bal_adj ( p_effective_date => p_effective_date -- l_p45_effective_date
1458 ,p_business_group_id => l_business_group_id
1459 ,p_assignment_id => l_assignment_id );
1460
1461 --6015209
1462 hr_utility.set_location('before delete_p46', 1001);
1463
1464 delete_p46 (p_effective_date
1465 , l_assignment_id
1466 , l_business_group_id
1467 , p_datetrack_delete_mode
1468 , l_object_version_p45p3
1469 );
1470 hr_utility.set_location('after delete_p46', 1001);
1471 --6015209
1472 END IF;
1473 --BUG NO:2683086 Moved the call to procedure after call to delete_bal_adj procedure.
1474
1475 -- Process Logic
1476 --
1477 -- Call row handler procedure to update paye details
1478 --
1479 hr_utility.set_location('before pay_ipd_del.del', 1002);
1480 pay_ipd_del.del
1481 ( p_effective_date => p_effective_date
1482 ,p_datetrack_mode => p_datetrack_delete_mode
1483 ,p_paye_details_id => p_paye_details_id
1484 ,p_object_version_number => l_object_version_number
1485 ,p_effective_start_date => l_effective_start_date
1486 ,p_effective_end_date => l_effective_end_date
1487 );
1488 hr_utility.set_location('after pay_ipd_del.del', 1002);
1489 --
1490 --
1491 -- Call After Process User Hook
1492 --
1493 begin
1494 hr_utility.set_location('before pay_ie_paye_bk3.delete_ie_paye_details_a', 1003);
1495 pay_ie_paye_bk3.delete_ie_paye_details_a
1496 (p_effective_date => p_effective_date
1497 ,p_business_group_id => l_business_group_id
1498 ,p_datetrack_delete_mode => p_datetrack_delete_mode
1499 ,p_paye_details_id => p_paye_details_id
1500 ,p_object_version_number => l_object_version_number
1501 ,p_effective_start_date => l_effective_start_date
1502 ,p_effective_end_date => l_effective_end_date
1503 );
1504 hr_utility.set_location('after pay_ie_paye_bk3.delete_ie_paye_details_a', 1003);
1505 exception
1506 when hr_api.cannot_find_prog_unit then
1507 hr_api.cannot_find_prog_unit_error
1508 (p_module_name => 'delete_ie_paye_details'
1509 ,p_hook_type => 'AP'
1510 );
1511 end;
1512 --
1513 -- When in validation only mode raise the Validate_Enabled exception
1514 --
1515 hr_utility.set_location('before p_validate', 1004);
1516 if p_validate then
1517 raise hr_api.validate_enabled;
1518 end if;
1519 hr_utility.set_location('after p_validate', 1004);
1520 --
1521 -- Set all output arguments
1522 --
1523 p_object_version_number := l_object_version_number;
1524 p_effective_start_date := l_effective_start_date;
1525 p_effective_end_date := l_effective_end_Date;
1526 --
1527 hr_utility.set_location(' Leaving:'||l_proc, 70);
1528 --hr_utility.trace_off;
1529 exception
1530 when hr_api.validate_enabled then
1531 --
1532 -- As the Validate_Enabled exception has been raised
1533 -- we must rollback to the savepoint
1534 --
1535 rollback to delete_ie_paye_details;
1536 --
1537 -- Only set output warning arguments
1538 -- (Any key or derived arguments must be set to null
1539 -- when validation only mode is being used.)
1540 --
1541 p_object_version_number := l_object_version_number;
1542 p_effective_start_date := null;
1543 p_effective_end_Date := null;
1544 --
1545 hr_utility.set_location(' Leaving:'||l_proc, 80);
1546 when others then
1547 --
1548 -- A validation or unexpected error has occured
1549 --
1550 hr_utility.set_location('Inside when Others', 1010);
1551 rollback to update_ie_paye_details;
1552 p_object_version_number := l_object_version_number;
1553 p_effective_start_date := null;
1554 p_effective_end_Date := null;
1555 hr_utility.set_location(' Leaving:'||l_proc, 90);
1556 raise;
1557 end delete_ie_paye_details;
1558
1559 end pay_ie_paye_api;