1 package body pyudet as
2 /* $Header: pyudet.pkb 120.49.12010000.5 2008/10/03 10:22:54 apmishra ship $ */
3 /*---------------------------------------------------------------------
4 Copyright (c) Oracle Corporation 1995. All rights reserved
5
6 Name : pyudet
7 Description : Start Of Year Process, used to change Tax Basis to
8 Cumulative, clear Previous Tax Paid and Taxable Pay,
9 uplift Tax Codes or read new Tax Codes from tape.
10 Author : Barry Goodsell
11 Date Created : 15-Aug-95
12 Uses : hr_entry_api, hr_utility
13
14 Change List
15 -----------
16 Date Name Vers Bug No Description
17 +-----------+---------------+--------+--------+-----------------------+
18 10-NOV-2006 K.Thampan 115.74 Rewrite package for SOY 07.
19 Truncate the change list,
20 see version 115.73 for
21 previous changes.
22 115.75 Fix GSCC error
23 22-NOV-2006 K.Thampan 115.76 Remove check for NI Director
24 start date.
25 11-JAN-2007 K.Thampan 115.79 5757305 Added more validation for mode
26 3/4.
27 23-FEB-2007 K.Thampan 115.80 Fix tax basis error
28 115.81 Change from DO to D0
29 115.82 Added checked for tax_ref
30 in mode 3,4 for non-aggregated
31 assignment.
32 02-MAR-2007 K.Thampan 115.83 5912261 Apply format mask to tax
33 district number
34 12-MAR-2007 K.Thampan 115.85 5927555 Added table alias
35 22-MAR-2007 K.Thampan 115.86 5948728 Amended report for mode 3/4 to
36 output unprocess record as
37 correction.
38 26-MAR-2007 K.Thampan 115.87 5953974 Amended update_record procedure
39 23-APR-2007 K.Thampan 115.88 5962025 Performance fix.
40 30-NOV-2007 Dinesh C. 115.94 6450573 Change for SOY 08-09.
41 04-MAR-2008 Rajesh L. 115.96 6741064 Modifed cursors csr_mode12,csr_mode34
42 as orderby fullname
43 26-MAR-2008 Rajesh L. 115.97 6864422 Reverted the fix 6741064 as it was reported
44 by one cusotomer.
45 Modified p_m34_rec.full_name to substr
46 of peo.last_name length
47 09-APR-2008 Rajesh.L 115.98 6957644 Modifed cursors csr_mode12,csr_mode34
48 as orderby fullname
49 08-SEP-2008 emunisek 115.99 7373763 Modified procedure process_record to prevent
50 the false entries in the report of SOY changes
51 and to prevent date tracks created for unmodified
52 tax codes
53 3-Oct-2008 apmishra 115.101 7373763 Re arcs in the file so as to enable the dual maintainence
54 The earlier version of the branch file did not contain the fix,
55 hence rearcs in.
56 ------------------------------------------------------------------------*/
57
58 -----------------------------------------------------
59 -- Constant variable --
60 -----------------------------------------------------
61 err_emp_not_found constant varchar2(255) := 'Emp Data in EDI file does not match application data, or employee is terminated.';
62 err_multiple_found constant varchar2(255) := 'Emp Data in EDI file matches multiple records on the database.';
63 err_data_mismatch constant varchar2(255) := '2 out of 3 values in EDI file required for matching are null.';
64 err_invalid_tax constant varchar2(255) := 'Invalid tax code : TAX_CODE.';
65 err_mode34_ex_emp constant varchar2(255) := 'No update for Ex-Employee, manual update may be required.';
66 err_mode2_ex_emp constant varchar2(255) := 'No bulk uplift for Ex-Employee, manual update may be required.';
67 err_tax_basis constant varchar2(255) := 'Invalid Tax Basis flag : TAX_BASIS.';
68 err_p6_pay_and_tax constant varchar2(255) := 'Previous tax is not zero, therefore previous pay cannot be zero.';
69 err_p45_p6_figures constant varchar2(255) := 'Discrepancy of over 1000 Pounds between P45/P6 figures.';
70 err_future_changes constant varchar2(255) := 'Future change effective : DATE.';
71 err_multi_fchanges constant varchar2(255) := 'Multiple future changes detected, manual update may be required.';
72 err_no_paye_ele constant varchar2(255) := 'PAYE Details element entries does not exists on DATE.';
73 err_multi_asg constant varchar2(255) := 'Record not updated as assignment number is not supplied and multiple assignments exist, manual update may be required.';
74 err_future_asg constant varchar2(255) := 'Record not updated as future assignment exists under the same tax district, manual update may be required.';
75 warning_msg constant varchar2(255) := 'Tax details updated for assignment with future termination details present.'; /*Added soy 08-09*/
76 update_mode constant varchar2(20) := 'UPDATE';
77 correct_mode constant varchar2(20) := 'CORRECTION';
78 reject_mode constant varchar2(20) := 'REJECT';
79 ignore_mode constant varchar2(20) := 'IGNORE';
80
81 -----------------------------------------------------
82 -- Record type for holding ASG Info --
83 -----------------------------------------------------
84 TYPE g_typ_paye_record IS RECORD(
85 element_entry_id number,
86 effective_start_date date,
87 effective_end_date date,
88 tax_code_id number,
89 tax_code_sv varchar2(25),
90 tax_basis_id number,
91 tax_basis_sv varchar2(50),
92 pay_previous_id number,
93 pay_previous_sv varchar2(70),
94 tax_previous_id number,
95 tax_previous_sv varchar2(70),
96 authority_id number,
97 authority_sv varchar2(80),
98 refundable_id number,
99 refundable_sv varchar2(50),
100 tax_code_prefix varchar2(20),
101 tax_code_value number,
102 tax_code_suffix varchar2(20),
103 tax_code_amended boolean,
104 tax_basis_amended boolean,
105 p45_val_amended boolean,
106 dt_update_mode varchar2(25),
107 creator_id number);
108
109 TYPE g_typ_per_record IS RECORD(
110 person_id number,
111 full_name varchar2(255),
112 ni_number varchar2(20),
113 aggregate_flag varchar2(2),
114 director_flag varchar2(2),
115 person_type varchar2(30),
116 term_date date,
117 lsp_date date,
118 effective_date date,
119 start_date date,
120 end_date date);
121
122 TYPE g_tax_code_interface IS RECORD(
123 full_name varchar2(255),
124 national_identifier varchar2(30),
125 assignment_number varchar2(60),
126 payroll_name varchar2(90),
127 effective_date date,
128 date_of_message date,
129 issue_date date,
130 tax_code varchar2(20),
131 tax_basis varchar2(20),
132 previous_pay number,
133 previous_tax number,
134 authority varchar2(20),
135 paye_ref varchar2(30),
136 row_id rowid);
137
138 -----------------------------------------------------
139 -- GLOBAL Variables --
140 -----------------------------------------------------
141 g_business_group_id number;
142 g_payroll_id number;
143 g_element_type_id number;
144 g_mode number;
145 g_request_id number;
146 g_p6_request_id number;
147 g_effective_date date;
148 g_authority varchar2(10);
149 g_current_req_id number;
150 g_tax_ref varchar2(20);
151 g_payroll_name varchar2(50);
152 g_update_count number;
153 g_reject_count number;
154 g_E_line_count number;
155 g_P_line_count number;
156 g_T_line_count number;
157 g_uplift_suffix hr_entry.varchar2_table;
158 g_uplift_value hr_entry.number_table;
159 g_validate_only varchar2(1); /*Added soy 08-09*/
160 g_cpe_flag varchar2(1); /*Added soy 08-09*/
161
162 ------------------------------------------------------------
163 -- Cursor to fetch element type id for PAYE Details --
164 ------------------------------------------------------------
165 cursor get_element_type_id is
166 select element_type_id
167 from pay_element_types_f
168 where element_name = 'PAYE Details';
169
170 ------------------------------------------------------------
171 -- Cursor to fetch assignment --
172 -- csr_mode12 = Cursor for select emp for mode 1 and 2 --
173 ------------------------------------------------------------
174 cursor csr_mode12 is
175 select /*+ ORDERED
176 INDEX(ppt, PER_PERSON_TYPES_PK) */
177 max(peo.person_id) p_id,
178 peo.full_name,
179 peo.national_identifier,
180 nvl(peo.per_information10,'N') agg_flag,
181 nvl(peo.per_information2, 'N') dir_flag,
182 ppt.system_person_type,
183 pps.actual_termination_date,
184 pps.last_standard_process_date,
185 g_effective_date,
186 peo.effective_start_date,
187 peo.effective_end_date
188 from per_all_people_f peo,
189 per_all_assignments_f asg,
190 per_periods_of_service pps,
191 per_person_type_usages_f ptu,
192 per_person_types ppt
193 where peo.person_id = asg.person_id
194 and peo.business_group_id = g_business_group_id
195 and (g_payroll_id is null or
196 asg.payroll_id = g_payroll_id)
197 and peo.person_id = ptu.person_id
198 and ptu.person_type_id = ppt.person_type_id
199 and pps.period_of_service_id = asg.period_of_service_id
200 and asg.payroll_id is not null
201 and g_effective_date between peo.effective_start_date and peo.effective_end_date
202 and g_effective_date between asg.effective_start_date and asg.effective_end_date
203 and g_effective_date between ptu.effective_start_date and ptu.effective_end_date
204 and ( (pps.final_process_date is not null and
205 pps.final_process_date > g_effective_date)
206 or
207 (pps.final_process_date is null))
208 group by peo.full_name, peo.national_identifier, peo.per_information10, peo.per_information2,
209 ppt.system_person_type, pps.actual_termination_date, pps.last_standard_process_date,
210 g_effective_date, peo.effective_start_date, peo.effective_end_date
211 -- Bug 6957644 modified p_id to upper(peo.full_name) to report order by name
212 --Bug 6741064 modified p_id to upper(peo.full_name) to report order by name
213 order by upper(peo.full_name), decode(system_person_type,'EMP',1,'EMP_APL',2,3);
214 -- Bug 6864422 reverted the fix 6741064 as it was reported by one customer
215 -- order by p_id, decode(system_person_type,'EMP',1,'EMP_APL',2,3);
216
217 ------------------------------------------------------------
218 -- csr_mode34 = Cursor for select data off the tax code --
219 -- interface table --
220 ------------------------------------------------------------
221 cursor csr_mode34 is
222 select tci.employee_name full_name,
223 tci.national_insurance_number national_identifier,
224 tci.works_number assignment_number,
225 tci.employer_reference payroll_name,
226 nvl(tci.effective_date,g_effective_date) effective_date,
227 tci.date_of_message date_of_message,
228 tci.issue_date issue_date,
229 ltrim(rtrim(tci.tax_code)) tax_code_sv,
230 ltrim(rtrim(tci.non_cumulative_flag)) tax_basis_sv,
231 to_char(nvl((tci.tot_pay_prev_emp/100),'')) pay_previous_sv,
232 to_char(nvl((tci.tot_tax_prev_emp/100),'')) tax_previous_sv,
233 upper(nvl(tci.form_type,'')) authority_sv,
234 ltrim(rtrim(to_char(tci.district_number,'000'))) || '/' || ltrim(rtrim(tci.employer_reference)) paye_ref,
235 tci.rowid row_id
236 from pay_gb_tax_code_interface tci
237 where tci.processed_flag is null
238 and ( tci.request_id is null
239 or tci.request_id = g_p6_request_id)
240 -- Bug 6957644 modified 6,2 to date_of_message,upper(full_name) to report order by name
241 --Bug 6741064 modified 6,2 to date_of_message,upper(full_name) to report order by name
242 order by date_of_message,upper(full_name);
243 -- Bug 6864422 reverted the fix 6741064 as it was reported by one customer
244 -- order by 6,2;
245
246 ------------------------------------------------------------
247 -- Cursor to fetch assignment --
248 -- Cursor for select assignment based on the person_id --
249 -- aggr flag --
250 ------------------------------------------------------------
251 cursor csr_asg_details(p_person_id number,
252 p_aggr_flag varchar2,
253 p_tax_ref varchar2,
254 p_asg_no varchar2,
255 p_date date) is
256 select assignment_id,
257 pay.payroll_id,
258 assignment_number,
259 per_system_status asg_status,
260 sck.segment1 tax_ref
261 from per_all_assignments_f asg,
262 pay_all_payrolls_f pay,
263 per_assignment_status_types pat,
264 hr_soft_coding_keyflex sck
265 where asg.person_id = p_person_id
266 and ( -- no need to fetch payroll, but do check the tax ref
267 (p_aggr_flag = 'Y' and
268 ((g_payroll_id is not null and sck.segment1 = g_tax_ref)
269 or
270 (g_payroll_id is null and p_tax_ref is not null and sck.segment1 = p_tax_ref)
271 or
272 (g_payroll_id is null and p_tax_ref is null)))
273 or -- not aggregate then we have to check the payroll is matched.
274 (p_aggr_flag = 'N' and
275 ((g_payroll_id is null and p_asg_no is null and (p_tax_ref is null or p_tax_ref = sck.segment1) )
276 or
277 (g_payroll_id is null and p_asg_no is not null and asg.assignment_number = p_asg_no and (p_tax_ref is null or p_tax_ref = sck.segment1))
278 or
279 (g_payroll_id is not null and asg.payroll_id = g_payroll_id and (p_tax_ref is null or p_tax_ref = sck.segment1) )))
280 )
281 and asg.payroll_id = pay.payroll_id
282 and sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
283 and asg.assignment_status_type_id = pat.assignment_status_type_id
284 and p_date between asg.effective_start_date and asg.effective_end_date
285 and p_date between pay.effective_start_date and pay.effective_end_date
286 order by assignment_id, decode(per_system_status,'ACTIVE_ASSIGN',1,'SUSP_ASSIGN',2,'ACTIVE_APL',3,4), sck.segment1;
287
288 ----------------------------------------------------------
289 -- This cursor returns those request_ids that are --
290 -- greater than or equal to the request_id entered --
291 -- by the user in the SRS - Resume definition and --
292 -- that are smaller than the current process request_id --
293 ----------------------------------------------------------
294 cursor get_req_cur (c_creator_id in number) IS
295 select 1
296 from dual
297 where exists (select fcr.request_id
298 from fnd_concurrent_requests fcr,
299 fnd_concurrent_programs fcp
300 where fcr.concurrent_program_id = fcp.concurrent_program_id
301 and fcr.request_id = c_creator_id
302 and fcr.request_id < g_current_req_id
303 and fcr.request_id >= g_request_id
304 and ( fcp.concurrent_program_name = 'PYUDET'
305 or fcp.concurrent_program_name = 'PYUDET_R'));
306
307 ---------------------------------------------------------
308 -- This cursor returns tax ref id based on the payroll --
309 ---------------------------------------------------------
310 cursor get_payroll_details IS
311 select sck.segment1, payroll_name
312 from hr_soft_coding_keyflex sck,
313 pay_all_payrolls_f pay
314 where sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
315 and pay.payroll_id = g_payroll_id
316 and g_effective_date between pay.effective_start_date and pay.effective_end_date;
317
318 -----------------------------------------------------------------------------------------------
319 -- This cursor is for Checking active and suspended assignments at the time of process run -- /*Added soy 08-09*/
320 -----------------------------------------------------------------------------------------------
321 CURSOR get_cpe_flag(p_person_id number, p_paye_ref varchar2, p_date date ) IS
322 select assignment_id
323 from per_all_assignments_f asg,
324 pay_all_payrolls_f pay,
325 per_assignment_status_types pat,
326 hr_soft_coding_keyflex sck
327 where asg.person_id = p_person_id
328 and sck.segment1 = p_paye_ref
329 and asg.payroll_id = pay.payroll_id
330 and sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
331 and asg.assignment_status_type_id = pat.assignment_status_type_id
332 and p_date between asg.effective_start_date and asg.effective_end_date
333 and p_date between pay.effective_start_date and pay.effective_end_date
334 AND per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') ;
335
336 ---------------------------------------------------------------------
337 -- NAME : set_global --
338 -- Type : Private Procedure --
339 -- DESCRIPTION : --
340 -- Set global variable --
341 ---------------------------------------------------------------------
342 PROCEDURE set_global(p_request_id in number default null,
343 p_mode in number,
344 p_effective_date in date,
345 p_business_group_id in number,
346 p_payroll_id in number,
347 p_authority in varchar2 default null,
348 p_p6_request_id in number default null,
349 p_validate_only in varchar2)
350 IS
351 BEGIN
352 hr_utility.trace('Setting GLOBAL variable');
353 open get_element_type_id;
354 fetch get_element_type_id into g_element_type_id;
355 close get_element_type_id;
356
357 g_business_group_id := p_business_group_id;
358 g_payroll_id := p_payroll_id;
359 g_mode := p_mode;
360 g_request_id := p_request_id;
361 g_p6_request_id := p_p6_request_id;
362 g_effective_date := p_effective_date;
363 g_authority := p_authority;
364 g_current_req_id := nvl(fnd_profile.value('CONC_REQUEST_ID'),-1);
365 g_update_count := 0;
366 g_reject_count := 0;
367 -- Insert the G_EFFECTIVE_DATE into FND_SESSION
368 hr_utility.fnd_insert(g_effective_date);
369
370 IF p_validate_only = 'GB_VALIDATE' then /*Added soy 08-09*/
371 g_validate_only := 'Y';
372 elsif p_validate_only = 'GB_VALIDATE_COMMIT' THEN
373 g_validate_only := 'N';
374 end if;
375
376 if g_payroll_id is not null then
377 open get_payroll_details;
378 fetch get_payroll_details into g_tax_ref, g_payroll_name;
379 close get_payroll_details;
380 end if;
381
382 -- Set up value if running Resume
383 if g_request_id is not null then
384 select decode(max(decode(type,'E',line_no,0)),
385 null, 0,
386 max(decode(type,'E',line_no,0))),
387 decode(max(decode(type,'P',line_no,0)),
388 null, 0,
389 max(decode(type,'P',line_no,0))),
390 decode(max(decode(type,'T',line_no,0)),
391 null, 0,
392 max(decode(type,'T',line_no,0)))
393 into g_E_line_count,
394 g_P_line_count,
395 g_T_line_count
396 from pay_gb_soy_outputs
397 where request_id = g_request_id;
398 end if;
399 hr_utility.trace('Running mode : ' || g_mode);
400 hr_utility.trace('Business group : ' || g_business_group_id);
401 hr_utility.trace('PAYE element id : ' || g_element_type_id);
402 hr_utility.trace('Payroll ID : ' || g_payroll_id);
403 hr_utility.trace('Resume ID : ' || g_request_id);
404 hr_utility.trace('P6 request ID : ' || g_p6_request_id);
405 hr_utility.trace('Effective date : ' || g_effective_date);
406 hr_utility.trace('Authority code : ' || g_authority);
407 hr_utility.trace('PAYE reference : ' || g_tax_ref);
408 EXCEPTION
409 when OTHERS then
410 raise;
411 END set_global;
412
413 ---------------------------------------------------------------------
414 -- NAME : asg_future_termination Added soy 08-09 --
415 -- Type : Private Function --
416 -- DESCRIPTION : --
417 -- This function will return a Y if assignment is future --
418 -- terminated or ended in case of non-aggregated assignment else --
419 -- return N. In case of non-aggregated, if all assignments having --
420 -- same PAYE ref are ended at a future point and assignment future --
421 -- terminated then return Y else return N --
422 ---------------------------------------------------------------------
423 FUNCTION asg_future_termination(p_effective_date in date,
424 p_assignment_id in number,
425 p_person_rec in g_typ_per_record,
426 p_tax_ref in varchar2) return varchar2
427 IS
428 l_future_term_date date :=NULL;
429 l_future_end_date date :=NULL;
430 l_return_flag varchar2(1) :='N';
431 l_future_term_flag varchar2(1) :='N';
432 l_future_end_flag varchar2(1) :='N';
433 l_cpe_end_date date;
434 -------------------------------------------------------------------
435 -- This cursor is for Checking assignment is teminated in future --
436 -------------------------------------------------------------------
437 CURSOR get_future_term_date(p_assignment_id number, p_effective_date date ) IS
438 select effective_start_date
439 from PER_ALL_ASSIGNMENTS_F paaf,
440 per_assignment_status_types past
441 where paaf.effective_start_date >= p_effective_date
442 and paaf.assignment_id = p_assignment_id
443 and paaf.assignment_status_type_id =past.assignment_status_type_id
444 and past.per_system_status IN ('TERM_ASSIGN')
445 and paaf.business_group_id = g_business_group_id;
446 -------------------------------------------------------------------
447 -- This cursor is for Checking assignment is ended in future --
448 -------------------------------------------------------------------
449 CURSOR get_future_end_asg_date(p_assignment_id number, p_effective_date date ) IS
450 select max(effective_end_date)
451 from PER_ALL_ASSIGNMENTS_F paaf
452 where paaf.effective_end_date >= p_effective_date
453 and paaf.assignment_id = p_assignment_id
454 and paaf.business_group_id = g_business_group_id;
455
456 BEGIN
457 hr_utility.trace(' Check assignment is Teminated or Ended in future ');
458 open get_future_term_date(p_assignment_id, p_effective_date);
459 fetch get_future_term_date into l_future_term_date;
460 if get_future_term_date%FOUND then
461 l_future_term_flag := 'Y';
462 end if;
463 close get_future_term_date;
464
465 open get_future_end_asg_date(p_assignment_id, p_effective_date);
466 fetch get_future_end_asg_date into l_future_end_date ;
467 if l_future_end_date <> TO_DATE('31/12/4712', 'DD/MM/RRRR') then
468 l_future_end_flag := 'Y';
469 end if;
470 close get_future_end_asg_date;
471
472 if p_person_rec.aggregate_flag ='N' then
473 --------------------------------------------------
474 -- Check if assignment is terminated in future --
475 --------------------------------------------------
476 if l_future_term_flag = 'Y' then
477 hr_utility.trace(' Assingment Terminated Date is : ' || l_future_term_date);
478 l_return_flag := 'Y';
479 end if;
480
481 -------------------------------------------
482 --Check if assignment is Ended in future --
483 -------------------------------------------
484 if l_future_end_flag = 'Y' then
485 hr_utility.trace(' Assingment End Date is : ' || l_future_end_date );
486 l_return_flag := 'Y';
487 end if;
488 elsif p_person_rec.aggregate_flag ='Y' then
489 ------------------------------------------------------------------------------------
490 -- Check if all the asg having same PAYE terminated or ended in future in future --
491 ------------------------------------------------------------------------------------
492 l_cpe_end_date := pay_gb_eoy_archive.get_agg_active_end(p_assignment_id, p_tax_ref , p_effective_date);
493 if l_cpe_end_date <> TO_DATE('31/12/4712', 'DD/MM/RRRR') and l_cpe_end_date >= p_effective_date and
494 (l_future_term_flag = 'Y' or l_future_end_flag = 'Y')then
495 l_return_flag := 'Y';
496 end if;
497 end if;
498 return l_return_flag;
499 EXCEPTION
500 when others then
501 raise;
502 END asg_future_termination;
503
504 ----------------------------------------------------------------------
505 -- NAME : set_cpe_flag Added soy 08-09 --
506 -- Type : Private Function --
507 -- DESCRIPTION : --
508 -- This function will return N if the terminated assignment passed --
509 -- to this fuction not in CPE with any of the active or suspended --
510 -- assignment having same PAYE refference --
511 ----------------------------------------------------------------------
512 FUNCTION set_cpe_flag(p_person_id IN NUMBER,
513 p_assignment_id IN NUMBER ,
514 p_paye_ref IN VARCHAR2,
515 p_effective_date IN DATE)
516
517 RETURN VARCHAR2 IS
518 BEGIN
519 hr_utility.trace(' Inside set_cpe_flag function for term asg: ' || p_assignment_id);
520 for l_record in get_cpe_flag(p_person_id,
521 p_paye_ref,
522 p_effective_date)
523 loop
524 hr_utility.trace(' Active or suspened asg record');
525 hr_utility.trace(' Assignment ID : ' || l_record.assignment_id);
526 hr_utility.trace(' PAYE Ref : ' || p_paye_ref);
527 if pay_gb_eoy_archive.get_agg_active_end(l_record.assignment_id, p_paye_ref , p_effective_date)=
528 pay_gb_eoy_archive.get_agg_active_end(p_assignment_id, p_paye_ref, p_effective_date)
529 AND pay_gb_eoy_archive.get_agg_active_start(l_record.assignment_id, p_paye_ref, p_effective_date)=
530 pay_gb_eoy_archive.get_agg_active_start(p_assignment_id, p_paye_ref, p_effective_date) then
531
532 hr_utility.trace(' Term Asg is in active CPE ');
533 return 'Y';
534 end if;
535 end loop;
536 hr_utility.trace(' Term Asg is not in active CPE ');
537 RETURN 'N';
538 EXCEPTION
539 when others then
540 raise;
541 END set_cpe_flag;
542 ---------------------------------------------------------------------
543 -- NAME : check_commit --
544 -- Type : Private Procedure --
545 -- DESCRIPTION : --
546 -- Function to return TRUE if we have processed a certain number --
547 -- of records. This is to allow the main process to commit at --
548 -- regular intervals, in order to cut-down on the rollback space --
549 -- required. --
550 ---------------------------------------------------------------------
551 FUNCTION check_commit return BOOLEAN
552 IS
553 l_commit_point number := 10;
554 BEGIN
555 if (mod(g_update_count, l_commit_point) = 0) then
556 return(TRUE);
557 end if;
558 return FALSE;
559 END check_commit;
560
561 ---------------------------------------------------------------------
562 -- NAME : conv_to_table --
563 -- Type : Private Procedure --
564 -- DESCRIPTION : --
565 -- Procedure converts the element entry values from the PAYE --
566 -- record into the PL/SQL table format required for the element --
567 -- entry API --
568 ---------------------------------------------------------------------
569 PROCEDURE conv_to_table(p_paye_rec in g_typ_paye_record,
570 p_num_entry_values in out nocopy number,
571 p_input_value_id_tbl in out nocopy hr_entry.number_table,
572 p_entry_value_tbl in out nocopy hr_entry.varchar2_table)
573 IS
574 l_index number := 0;
575 BEGIN
576 if p_paye_rec.tax_basis_amended then
577 l_index := l_index + 1;
578 p_input_value_id_tbl(l_index) := p_paye_rec.tax_basis_id;
579 p_entry_value_tbl(l_index) := p_paye_rec.tax_basis_sv;
580 end if;
581 if p_paye_rec.p45_val_amended then
582 l_index := l_index + 1;
583 p_input_value_id_tbl(l_index) := p_paye_rec.pay_previous_id;
584 p_entry_value_tbl(l_index) := p_paye_rec.pay_previous_sv;
585
586 l_index := l_index + 1;
587 p_input_value_id_tbl(l_index) := p_paye_rec.tax_previous_id;
588 p_entry_value_tbl(l_index) := p_paye_rec.tax_previous_sv;
589 end if;
590 if p_paye_rec.tax_code_amended then
591 l_index := l_index + 1;
592 p_input_value_id_tbl(l_index) := p_paye_rec.tax_code_id;
593 p_entry_value_tbl(l_index) := ltrim(p_paye_rec.tax_code_sv);
594 end if;
595 if l_index > 0 then
596 l_index := l_index + 1;
597 p_input_value_id_tbl(l_index) := p_paye_rec.authority_id;
598 p_entry_value_tbl(l_index) := p_paye_rec.authority_sv;
599 end if;
600 p_num_entry_values := l_index;
601 END conv_to_table;
602
603 ---------------------------------------------------------------------
604 -- NAME : conv_to_table_ni --
605 -- Type : Private Procedure --
606 -- DESCRIPTION : --
607 -- Procedure converts the element entry values from the NI --
608 -- record into the PL/SQL table format required for the element --
609 -- entry API --
610 ---------------------------------------------------------------------
611 PROCEDURE conv_to_table_ni(p_process_type_updated in varchar2 ,
612 p_input_value_id in number ,
613 p_num_entry_values in out nocopy number,
614 p_input_value_id_tbl in out nocopy hr_entry.number_table,
615 p_entry_value_tbl in out nocopy hr_entry.varchar2_table)
616 IS
617 l_index number := 0;
618 BEGIN
619 l_index := l_index + 1;
620 p_input_value_id_tbl(l_index) := p_input_value_id ;
621 p_entry_value_tbl(l_index) := p_process_type_updated;
622 p_num_entry_values := l_index;
623 END conv_to_table_ni;
624
625 ---------------------------------------------------------------------
626 -- NAME : file_output --
627 -- Type : Private Procedure --
628 -- DESCRIPTION : --
629 -- DESCRIPTION --
630 -- Procedure inserts a record into the PAY_GB_SOY_OUTPUT table, --
631 -- which will be used to created the output files required for --
632 -- the PYUDET process --
633 ---------------------------------------------------------------------
634 PROCEDURE file_output(p_type in varchar2,
635 p_line_no in out nocopy number,
636 p_text in varchar2,
637 p_request_id in number default null)
638 IS
639 l_request_id number := nvl(fnd_profile.value('CONC_REQUEST_ID'),-1);
640 BEGIN
641 if p_request_id is not null then
642 l_request_id := p_request_id;
643 end if;
644 --
645 p_line_no := nvl(p_line_no,0) + 1;
646 --
647 insert into pay_gb_soy_outputs(request_id, type, line_no, text)
648 values(l_request_id, p_type, p_line_no, p_text);
649 --
650 END file_output;
651
652 ---------------------------------------------------------------------
653 -- NAME : write_header --
654 -- Type : Private Procedure --
655 -- DESCRIPTION : --
656 -- Write header output --
657 ---------------------------------------------------------------------
658 PROCEDURE write_header
659 IS
660 cursor c_p6 is
661 select count(*)
662 from pay_gb_tax_code_interface
663 where request_id = g_p6_request_id;
664
665 l_process varchar2(110);
666 l_mode varchar2(100);
667 l_run_date varchar2(100);
668 l_p6 varchar2(100);
669 l_eff_date varchar2(100);
670 l_payroll varchar2(100);
671 l_p6_count number;
672 l_validate_mode varchar2(150);
673 BEGIN
674 /*Added soy 08-09*/
675 if g_validate_only = 'N' then
676 l_process := 'Amendments to ''PAYE Details'' - Records Processed';
677 elsif g_validate_only = 'Y' then
678 l_process := 'Amendments to ''PAYE Details'' - Potential Records To Be Processed When Run In Commit Mode';
679 end if;
680 if g_mode = 1 then
681 l_mode := 'Start Of Year';
682 if g_validate_only = 'N' then
683 l_process := 'Amendments to ''PAYE Details'' and ''NI'' - Records Processed';
684 elsif g_validate_only = 'Y' then
685 l_process := 'Amendments to ''PAYE Details'' and ''NI'' - Potential Records To Be Processed When Run In Commit Mode';
686 end if;
687 elsif g_mode = 2 then
688 l_mode := 'Mid Year Tax Code Change';
689 elsif g_mode = 3 then
690 l_mode := 'Tax Code Uplift From Tape';
691 elsif g_mode = 4 then
692 l_mode := 'P6/P6B/P9 Upload Process';
693 open c_p6;
694 fetch c_p6 into l_p6_count;
695 close c_p6;
696 l_p6 := 'Total number of records in EDI file : ' || l_p6_count;
697 end if;
698 --
699 if g_payroll_name is not null then
700 l_payroll := g_payroll_name;
701 else
702 l_payroll := 'All Payrolls';
703 end if;
704 -------------
705 /*Added soy 08-09*/
706 if g_validate_only = 'Y' then
707 l_validate_mode :=rpad('Validate Mode',21) || 'Validate Only - Updates Not Applied To The Database';
708 else
709 l_validate_mode :=rpad('Validate Mode',21) || 'Validate And Commit';
710 end if;
711 --------------
712 l_run_date := rpad('Run Date',21) || rpad(to_char(sysdate,'DD-MON-YYYY'),50) || 'KEY';
713 l_eff_date := rpad('Effective Date',21) || rpad(to_char(g_effective_date,'DD-MON-YYYY'),50) || 'n/c : No Change';
714 l_mode := rpad('Processing Mode',21) || rpad(l_mode,50) || 'n/a : Not Applicable';
715 l_payroll := rpad('Payroll',21) || rpad(l_payroll,50) || 'n/s : Not Supplied';
716 --
717 file_output('P', g_P_line_count, null, g_request_id);
718 file_output('P', g_P_line_count, l_run_date, g_request_id);
719 file_output('P', g_P_line_count, l_eff_date, g_request_id);
720 file_output('P', g_P_line_count, l_mode, g_request_id);
721 file_output('P', g_P_line_count, l_payroll, g_request_id);
722 file_output('P', g_P_line_count, l_validate_mode, g_request_id);
723 file_output('P', g_P_line_count, null, g_request_id);
724 if l_p6 is not null then
725 file_output('P', g_P_line_count, null, g_request_id);
726 file_output('P', g_P_line_count, l_p6, g_request_id);
727 file_output('P', g_P_line_count, null, g_request_id);
728 end if;
729 file_output('P', g_P_line_count, l_process, g_request_id);
730 file_output('P', g_P_line_count, null, g_request_id);
731 if g_validate_only = 'N' then
732 file_output('E', g_E_line_count, replace(l_process,'Processed','Not Processed'), g_request_id);
733 elsif g_validate_only = 'Y' then
734 file_output('E', g_E_line_count, replace(l_process,'Processed','Rejected'), g_request_id);
735 end if;
736 END write_header;
737
738 ---------------------------------------------------------------------
739 -- NAME : write_group_header --
740 -- Type : Private Procedure --
741 -- DESCRIPTION : --
742 -- Procedure to write a group header to the report files if it is --
743 -- required --
744 ---------------------------------------------------------------------
745 PROCEDURE write_group_header
746 IS
747 l_line1 varchar2(255);
748 l_line2 varchar2(255);
749 l_line3 varchar2(255);
750 l_line4 varchar2(255);
751 l_line5 varchar2(255); /*Added soy 08-09*/
752 BEGIN
753 ----------------------------------------
754 -- First write the header for process --
755 ----------------------------------------
756 if g_mode in (1,2) then
757 -- ---------1---------2---------3---------4---------5---------6---------7---------8---------9---------0---------1---------2---------3
758 -- 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
759 l_line1 :=' Authority Tax Previous PAYE NI NI ';
760 l_line2 :='Assignment Old New Code Basis Gross/Tax Update/ Director Update ';
761 l_line3 :='Number Name Code Code Old | New Amended Amended Correction Old New ';
762 l_line4 :='---------- -------------------------------------- ------ ------ --------- ------- --------- ---------- --- --- ------------';
763 elsif g_mode in (3,4) then
764 -- ---------1---------2---------3---------4---------5---------6---------7---------8---------9---------0---------1---------2---------3
765 -- 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
766 l_line1 :='Assignment Record Authority Tax Tax Previous Previous ';
767 l_line2 :='Number Name Type Code Code Basis Pay Tax ';
768 l_line5 :='>>Warning ';
769 l_line3 :='---------- -------------------------------------- ---------- --------- ------ -------------- ----------- -----------';
770 end if;
771 file_output('P', g_P_line_count, l_line1, g_request_id);
772 file_output('P', g_P_line_count, l_line2, g_request_id);
773 if g_mode in (1,2) then
774 file_output('P', g_P_line_count, l_line3, g_request_id);
775 file_output('P', g_P_line_count, l_line4, g_request_id);
776 elsif g_mode in (3,4) then
777 file_output('P', g_P_line_count, l_line5, g_request_id);
778 file_output('P', g_P_line_count, l_line3, g_request_id);
779 end if;
780 /* if l_line4 is not null then
781 file_output('P', g_P_line_count, l_line4, g_request_id);
782 end if;*/
783 --------------------------------------------
784 -- First write the header for not process --
785 --------------------------------------------
786 l_line1 := null;
787 l_line2 := null;
788 l_line3 := null;
789 l_line4 := null;
790 if g_mode in (1,2) then
791 -- ---------1---------2---------3---------4---------5---------6---------7---------8---------9---------0---------1---------2---------3
792 -- 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
793 l_line1 :='Assignment ';
794 l_line2 :='Number Name Reason ';
795 l_line3 :='---------- -------------------------------------- --------------------------------------------------------------------------------';
796 elsif g_mode in (3,4) then
797 -- ---------1---------2---------3---------4---------5---------6---------7---------8---------9---------0---------1---------2---------3
798 -- 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
799 l_line1 :='Assignment NI Tax Tax Previous Previous Effective ';
800 l_line2 :='Number Name Number Code Basis Pay Tax Date ';
801 l_line3 :='>>Reason for Rejection ';
802 l_line4 :='---------- -------------------------------------- ---------- ------ -------------- ----------- ----------- ----------------';
803 end if;
804 file_output('E', g_E_line_count, l_line1, g_request_id);
805 file_output('E', g_E_line_count, l_line2, g_request_id);
806 file_output('E', g_E_line_count, l_line3, g_request_id);
807 if l_line4 is not null then
808 file_output('E', g_E_line_count, l_line4, g_request_id);
809 end if;
810 END write_group_header;
811
812 ---------------------------------------------------------------------
813 -- NAME : write_body --
814 -- Type : Private Procedure --
815 -- DESCRIPTION : --
816 -- This procedure will write lines to the update section in report --
817 ---------------------------------------------------------------------
818 PROCEDURE write_body(p_old_paye_rec in g_typ_paye_record,
819 p_new_paye_rec in g_typ_paye_record,
820 p_person_rec in g_typ_per_record,
821 p_asg_number in varchar2,
822 p_dir in varchar2 default null,
823 p_effective_date in date default null,
824 p_assignment_id in number,
825 p_tax_ref in varchar2) /*Extra parameter Added soy 08-09*/
826 IS
827 l_line varchar2(255);
828 l_before varchar2(255);
829 l_after varchar2(255);
830 l_tax_basis varchar2(20);
831 l_p45_figures varchar2(20);
832 l_prev_pay varchar2(20);
833 l_prev_tax varchar2(20);
834 l_tax_code varchar2(20);
835 l_director varchar2(30);
836 l_authority varchar2(30);
837 l_mode varchar2(30);
838 l_warn_flag varchar2(1);
839 BEGIN
840 l_line := rpad(p_asg_number,10) || ' ' || rpad(p_person_rec.full_name,38) || ' ';
841 l_mode := initcap(lower(p_new_paye_rec.dt_update_mode));
842 ------------
843 -- Mode 1 --
844 ------------
845 if g_mode = 1 then
846 l_tax_basis := 'n/c';
847 l_p45_figures := 'n/c';
848 l_tax_code := rpad('n/c',7) || rpad('n/c',7);
849 if p_new_paye_rec.tax_basis_amended then
850 l_tax_basis := 'YES';
851 end if;
852 if p_new_paye_rec.p45_val_amended then
853 l_p45_figures := 'YES';
854 end if;
855 if p_new_paye_rec.tax_code_amended then
856 l_tax_code := rpad(p_old_paye_rec.tax_code_sv,6) || ' ' || rpad(p_new_paye_rec.tax_code_sv,6) || ' ';
857 end if;
858 l_authority:= rpad(nvl(p_old_paye_rec.authority_sv,' '),4) || '|' || lpad(nvl(p_new_paye_rec.authority_sv,' '),4);
859 l_director := p_dir;
860 if p_dir is null then
861 l_director := 'n/a n/a n/a';
862 end if;
863 l_line := l_line || l_tax_code || rpad(l_authority,10) || rpad(l_tax_basis,8) || rpad(l_p45_figures,10) || rpad(l_mode, 11) || l_director;
864 file_output('P', g_P_line_count,l_line, g_request_id);
865 end if;
866 -------------
867 -- Mode 2 --
868 -------------
869 if g_mode = 2 then
870 l_director := 'n/a n/a n/a';
871 l_tax_code := rpad(p_old_paye_rec.tax_code_sv,6) || ' ' || rpad(p_new_paye_rec.tax_code_sv,6) || ' ';
872 l_authority:= rpad(nvl(p_old_paye_rec.authority_sv,' '),4) || '|' || lpad(nvl(p_new_paye_rec.authority_sv,' '),4);
873 l_line := l_line || l_tax_code || rpad(l_authority,10) || rpad('n/a',8) || rpad('n/a',10) || rpad(l_mode, 11) || l_director;
874 file_output('P', g_P_line_count,l_line, g_request_id);
875 end if;
876 -----------------
877 -- Mode 3 or 4 --
878 -----------------
879 if g_mode in (3,4) then
880 l_tax_basis := rpad('n/c',15);
881 l_tax_code := rpad('n/c',7);
882 l_prev_pay := lpad('n/c',11);
883 l_prev_tax := lpad('n/c',11);
884 l_authority := rpad(nvl(p_old_paye_rec.authority_sv,' '),10);
885 if p_new_paye_rec.tax_code_amended then
886 l_tax_code := rpad(p_old_paye_rec.tax_code_sv,7);
887 end if;
888 if p_new_paye_rec.tax_basis_amended then
889 l_tax_basis := rpad(p_old_paye_rec.tax_basis_sv,15);
890 end if;
891 if p_new_paye_rec.p45_val_amended then
892 if to_number(nvl(p_old_paye_rec.pay_previous_sv,'0')) <> to_number(nvl(p_new_paye_rec.pay_previous_sv,'0')) then
893 l_prev_pay := lpad(to_char(to_number(nvl(p_old_paye_rec.pay_previous_sv,'0')),9999990.99),11);
894 end if;
895 if to_number(nvl(p_old_paye_rec.tax_previous_sv,'0')) <> to_number(nvl(p_new_paye_rec.tax_previous_sv,'0')) then
896 l_prev_tax := lpad(to_char(to_number(nvl(p_old_paye_rec.tax_previous_sv,'0')),9999990.99),11);
897 end if;
898 end if;
899 l_before := l_line || rpad('Before',11) || l_authority || l_tax_code || l_tax_basis || l_prev_pay || ' ' || l_prev_tax;
900
901 l_tax_basis := rpad('n/c',15);
902 l_tax_code := rpad('n/c',7);
903 l_prev_pay := lpad('n/c',11);
904 l_prev_tax := lpad('n/c',11);
905 l_authority := rpad(nvl(p_new_paye_rec.authority_sv,' '),10);
906 if p_new_paye_rec.tax_code_amended then
907 l_tax_code := rpad(p_new_paye_rec.tax_code_sv,7);
908 end if;
909 if p_new_paye_rec.tax_basis_amended then
910 l_tax_basis := rpad(p_new_paye_rec.tax_basis_sv,15);
911 end if;
912 if p_new_paye_rec.p45_val_amended then
913 if to_number(nvl(p_old_paye_rec.pay_previous_sv,'0')) <> to_number(nvl(p_new_paye_rec.pay_previous_sv,'0')) then
914 l_prev_pay := lpad(to_char(to_number(nvl(p_new_paye_rec.pay_previous_sv,'0')),9999990.99),11);
915 end if;
916 if to_number(nvl(p_old_paye_rec.tax_previous_sv,'0')) <> to_number(nvl(p_new_paye_rec.tax_previous_sv,'0')) then
917 l_prev_tax := lpad(to_char(to_number(nvl(p_new_paye_rec.tax_previous_sv,'0')),9999990.99),11);
918 end if;
919 end if;
920 l_after := rpad(' ',50) || rpad(l_mode,11) || l_authority || l_tax_code || l_tax_basis || l_prev_pay || ' ' || l_prev_tax;
921 file_output('P', g_P_line_count,l_before, g_request_id);
922 file_output('P', g_P_line_count,l_after, g_request_id);
923 l_warn_flag := asg_future_termination(nvl(p_effective_date,g_effective_date), p_assignment_id, p_person_rec, p_tax_ref) ; /*Added soy 08-09*/
924 if l_warn_flag = 'Y' then
925 file_output('P', g_P_line_count,'>>' || warning_msg , g_request_id);
926 end if;
927 end if;
928 END write_body;
929
930 ---------------------------------------------------------------------
931 -- NAME : reject_record --
932 -- Type : Private Procedure --
933 -- DESCRIPTION : --
934 -- This procedure will write message to the reject section --
935 ---------------------------------------------------------------------
936 PROCEDURE reject_record(p_per_rec g_typ_per_record default null,
937 p_m34_rec g_tax_code_interface default null,
938 p_msg varchar2,
939 p_asg_number varchar2 default null)
940 IS
941 l_line varchar2(255);
942 l_ni varchar2(50);
943 l_tax_code varchar2(50);
944 l_tax_basis varchar2(50);
945 l_p45_figures varchar2(50);
946 BEGIN
947 if g_mode in (1,2) then
948 l_line := rpad(p_asg_number,10) || ' ' || rpad(p_per_rec.full_name,38) || ' ' || p_msg;
949 file_output('E', g_E_line_count,l_line, g_request_id);
950 elsif (g_mode in (3,4)) then
951 ----------------------------------------
952 -- First update the taxcode interface --
953 ----------------------------------------
954 update pay_gb_tax_code_interface
955 set processed_flag = 'R'
956 where rowid = p_m34_rec.row_id;
957 --
958 if p_asg_number is not null then
959 l_line := rpad(p_asg_number,10) || ' ' || rpad(p_per_rec.full_name,38);
960 else
961 l_line := rpad(nvl(p_m34_rec.assignment_number,'none'),10) || ' ' || rpad(nvl(p_m34_rec.full_name,' '),38);
962 end if;
963 l_ni := rpad(nvl(p_m34_rec.national_identifier,' '),11);
964 l_tax_code := rpad(nvl(p_m34_rec.tax_code,' '),7);
965 l_tax_basis := rpad(nvl(p_m34_rec.tax_basis,' '),15);
966 l_p45_figures:= lpad(to_char(nvl(p_m34_rec.previous_pay,0),9999990.99),11) || ' ' ||
967 lpad(to_char(nvl(p_m34_rec.previous_tax,0),9999990.99),11) || ' ';
968 l_line := l_line || ' ' || l_ni || l_tax_code || l_tax_basis || l_p45_figures || to_char(p_m34_rec.effective_date,'DD-MON-YYYY');
969 file_output('E', g_E_line_count,l_line, g_request_id);
970 file_output('E', g_E_line_count,'>>' || p_msg , g_request_id);
971 end if;
972 g_reject_count := g_reject_count + 1;
973 END reject_record;
974
975 ---------------------------------------------------------------------
976 -- NAME : write_footer --
977 -- Type : Private Procedure --
978 -- DESCRIPTION : --
979 -- Write footer output --
980 ---------------------------------------------------------------------
981 PROCEDURE write_footer
982 IS
983 l_p6_count number;
984 cursor p6 is
985 select count(*)
986 from pay_gb_tax_code_interface
987 where request_id = g_p6_request_id
988 and processed_flag = 'P';
989 BEGIN
990 file_output('P', g_P_line_count, null, g_request_id);
991 file_output('P', g_P_line_count, null, g_request_id);
992
993 if (g_p6_request_id is not null) then
994 open p6;
995 fetch p6 into l_p6_count;
996 close p6;
997 file_output('P', g_P_line_count,'Total EDI Records Processed = '|| to_char(l_p6_count), g_request_id);
998 end if;
999 file_output('P', g_P_line_count, 'Records Updated (inc Multi Assignment) = '|| to_char(g_update_count), g_request_id);
1000 --
1001 file_output('E', g_E_line_count, null, g_request_id);
1002 file_output('E', g_E_line_count, null, g_request_id);
1003 if (g_p6_request_id is not null) then
1004 file_output('E', g_E_line_count,'Total EDI Records Not Processed = '|| to_char(g_reject_count), g_request_id);
1005 else
1006 file_output('E', g_E_line_count,'Records Not Processed = '|| to_char(g_reject_count), g_request_id);
1007 end if;
1008 END write_footer;
1009
1010 ---------------------------------------------------------------------
1011 -- NAME : lookup_meaning --
1012 -- Type : Private Procedure --
1013 -- DESCRIPTION : --
1014 -- Function returns the MEANING column from HR_LOOKUPS for the --
1015 -- specified LOOKUP_TYPE and LOOKUP_CODE --
1016 ---------------------------------------------------------------------
1017 FUNCTION lookup_meaning(p_lookup_type in varchar2,
1018 p_lookup_code in varchar2) return VARCHAR2
1019 IS
1020 l_meaning hr_lookups.meaning%type := null;
1021 cursor c_lookup is
1022 select lku.meaning
1023 from hr_lookups lku
1024 where lku.lookup_type = p_lookup_type
1025 and lku.lookup_code = p_lookup_code;
1026 BEGIN
1027 open c_lookup;
1028 fetch c_lookup into l_meaning;
1029 close c_lookup;
1030 return l_meaning;
1031 END lookup_meaning;
1032
1033 ---------------------------------------------------------------------
1034 -- NAME : validate_tax_code --
1035 -- Type : Private Function --
1036 -- DESCRIPTION : --
1037 -- This Function uses the TAX_CODE validation fast formula and --
1038 -- returns an error message if an incorrect tax code is entered. --
1039 ---------------------------------------------------------------------
1040 FUNCTION validate_tax_code(p_tax_code in varchar2,
1041 p_effective_date in date,
1042 p_assignment_id in number) return VARCHAR2
1043 IS
1044 l_formula_id ff_formulas_f.formula_id%type;
1045 l_effective_start_date ff_formulas_f.effective_start_date%type;
1046 l_inputs ff_exec.inputs_t;
1047 l_outputs ff_exec.outputs_t;
1048 l_return_value varchar2(50):= null;
1049 l_formula_mesg varchar2(50):= null;
1050 l_status_value varchar2(2):= null;
1051 BEGIN
1052 -----------------------------
1053 -- Fetch formula details --
1054 -----------------------------
1055 select formula_id,
1056 effective_start_date
1057 into l_formula_id,
1058 l_effective_start_date
1059 from ff_formulas_f
1060 where formula_name='TAX_CODE'
1061 and business_group_id is null
1062 and legislation_code='GB'
1063 and p_effective_date between effective_start_date and effective_end_date;
1064 ----------------------------
1065 -- Initialize the formula --
1066 ----------------------------
1067 ff_exec.init_formula(l_formula_id,l_effective_start_date,l_inputs,l_outputs);
1068 -------------------------
1069 -- Setup formula input --
1070 -------------------------
1071 for l_in_cnt in l_inputs.first..l_inputs.last loop
1072 if l_inputs(l_in_cnt).name = 'ENTRY_VALUE' then
1073 l_inputs(l_in_cnt).value := ltrim(p_tax_code);
1074 end if;
1075 if l_inputs(l_in_cnt).name = 'DATE_EARNED' then
1076 l_inputs(l_in_cnt).value := to_char(p_effective_date,'DD-MON-YYYY');
1077 end if;
1078 if l_inputs(l_in_cnt).name = 'ASSIGNMENT_ID' then
1079 l_inputs(l_in_cnt).value := to_char(p_assignment_id);
1080 end if;
1081 end loop;
1082 -------------------------
1083 -- Execute the formula --
1084 -------------------------
1085 ff_exec.run_formula(l_inputs,l_outputs);
1086 --------------------
1087 -- Reading output --
1088 --------------------
1089 for l_out_cnt in l_outputs.first..l_outputs.last loop
1090 if l_outputs(l_out_cnt).name='FORMULA_MESSAGE' then
1091 l_formula_mesg := l_outputs(l_out_cnt).value;
1092 end if;
1093 if l_outputs(l_out_cnt).name='FORMULA_STATUS' then
1094 l_status_value := l_outputs(l_out_cnt).value;
1095 end if;
1096 end loop;
1097 if l_status_value = 'E' and l_formula_mesg is null then
1098 l_return_value := 'TAX_CODE Formula error';
1099 else
1100 l_return_value := l_formula_mesg;
1101 end if;
1102
1103 return l_return_value;
1104 EXCEPTION
1105 WHEN no_data_found then
1106 return l_return_value;
1107 END validate_tax_code;
1108
1109 ---------------------------------------------------------------------
1110 -- NAME : post_fetch --
1111 -- Type : Private Procedure --
1112 -- DESCRIPTION : --
1113 -- Parse the tax_code details and place the value back into the --
1114 -- record --
1115 ---------------------------------------------------------------------
1116 PROCEDURE post_fetch(p_paye_rec in out nocopy g_typ_paye_record)
1117 IS
1118 l_tax_code varchar2(50);
1119 l_step number;
1120 BEGIN
1121 l_step := 1;
1122 l_tax_code := ltrim(rtrim(p_paye_rec.tax_code_sv));
1123 if (l_tax_code <> 'NT' AND l_tax_code <> 'BR' AND l_tax_code <> 'FT' AND
1124 l_tax_code <> 'D0' AND l_tax_code <> 'NI' AND l_tax_code <> 'T0' AND
1125 l_tax_code <> 'SBR' AND l_tax_code <> 'SNT' AND l_tax_code <> 'SFT' AND
1126 l_tax_code <> 'SNI') then
1127 --
1128 p_paye_rec.tax_code_prefix := pysoytls.tax_prefix(p_paye_rec.tax_code_sv);
1129 p_paye_rec.tax_code_value := pysoytls.tax_value (p_paye_rec.tax_code_sv);
1130 p_paye_rec.tax_code_suffix := pysoytls.tax_suffix(p_paye_rec.tax_code_sv);
1131 l_step := 2;
1132 end if;
1133 --
1134 l_step := 3;
1135 p_paye_rec.tax_basis_sv := lookup_meaning('GB_TAX_BASIS', p_paye_rec.tax_basis_sv);
1136 p_paye_rec.refundable_sv := lookup_meaning('GB_REFUNDABLE', p_paye_rec.refundable_sv);
1137 p_paye_rec.authority_sv := lookup_meaning('GB_AUTHORITY', p_paye_rec.authority_sv);
1138 EXCEPTION
1139 WHEN others then
1140 hr_utility.trace('Error in post_fetch at step ' || l_step);
1141 hr_utility.trace(SQLERRM(SQLCODE));
1142 raise;
1143 END post_fetch;
1144
1145 ---------------------------------------------------------------------
1146 -- NAME : get_uplift_value --
1147 -- Type : Private Function --
1148 -- DESCRIPTION : --
1149 -- This function will return uplift value based on effective_date --
1150 ---------------------------------------------------------------------
1151 FUNCTION get_uplift_value (p_tax_suffix in varchar2) return NUMBER
1152 IS
1153 cursor c_uplift is
1154 select usr.row_low_range_or_name suffix,
1155 fnd_number.canonical_to_number(uci.value) value
1156 from pay_user_tables ust,
1157 pay_user_columns usc,
1158 pay_user_rows_f usr,
1159 pay_user_column_instances_f uci
1160 where ust.user_table_name = 'TAX_CODE_UPLIFT_VALUES'
1161 and ust.user_table_id = usc.user_table_id
1162 and ust.user_table_id = usr.user_table_id
1163 and usc.user_column_name = 'UPLIFT_VALUE'
1164 and usc.user_column_id = uci.user_column_id
1165 and usr.user_row_id = uci.user_row_id
1166 and uci.business_group_id = g_business_group_id
1167 and usr.business_group_id = g_business_group_id
1168 and g_effective_date between usr.effective_start_date and usr.effective_end_date
1169 and g_effective_date = uci.effective_start_date;
1170 l_index number := 0;
1171 BEGIN
1172 if (g_uplift_value(0) = -1) then
1173 ---------------------------------------------
1174 -- Uplift values have not yet been fetched --
1175 ---------------------------------------------
1176 for r_uplift in c_uplift loop
1177 if (r_uplift.value is not null) and (r_uplift.value <> 0) then
1178 l_index := l_index + 1;
1179 g_uplift_suffix(l_index) := r_uplift.suffix;
1180 g_uplift_value(l_index) := r_uplift.value;
1181 end if;
1182 end loop;
1183 g_uplift_value(0) := l_index;
1184 end if;
1185 --------------------------------------------------------
1186 -- Scan the PL/SQL tables to find the required Suffix --
1187 --------------------------------------------------------
1188 for l_index in 1..g_uplift_value(0) loop
1189 if (g_uplift_suffix(l_index) = p_tax_suffix) then
1190 return(g_uplift_value(l_index));
1191 end if;
1192 end loop;
1193 -------------------------------------------------------
1194 -- Suffix was not found in the table, so return NULL --
1195 -------------------------------------------------------
1196 return(null);
1197 END get_uplift_value;
1198
1199 ---------------------------------------------------------------------
1200 -- NAME : check_future_changes --
1201 -- Type : Private Function --
1202 -- DESCRIPTION : --
1203 -- This function will return a date if there is a date tracked --
1204 -- update on or after the given date for PAYE element entries --
1205 ---------------------------------------------------------------------
1206 FUNCTION check_future_changes (p_assignment_id in number,
1207 p_effective_date in date default hr_api.g_date,
1208 p_multi_change out nocopy boolean) return DATE
1209 IS
1210 l_future_date date := null;
1211 BEGIN
1212 hr_utility.trace(' Check future change');
1213 select ele.effective_start_date
1214 into l_future_date
1215 from pay_element_entries_f ele
1216 where ele.effective_start_date >= p_effective_date
1217 and ele.assignment_id = p_assignment_id
1218 and ele.element_type_id = g_element_type_id
1219 order by ele.effective_start_date asc;
1220
1221 hr_utility.trace(' Future change date : ' || l_future_date);
1222 p_multi_change := FALSE;
1223 return(l_future_date);
1224 EXCEPTION
1225 when too_many_rows then
1226 p_multi_change := TRUE;
1227 return null;
1228 when no_data_found then
1229 p_multi_change := FALSE;
1230 return null;
1231 when others then
1232 raise;
1233 END check_future_changes;
1234
1235 ---------------------------------------------------------------------
1236 -- NAME : process_director --
1237 -- Type : Private Procedure --
1238 -- DESCRIPTION : --
1239 -- This procedure will update director details on NI element --
1240 -- Director Pro Rate to Director --
1241 -- Director Pro Rate Normal to Director Normal --
1242 ---------------------------------------------------------------------
1243 FUNCTION process_directors(p_per_rec in g_typ_per_record,
1244 p_assignment_id in number) return VARCHAR2
1245 IS
1246 l_element_entry_id number;
1247 l_input_value_id number;
1248 l_num_entry_values number;
1249 l_process_type varchar2(3);
1250 l_process_type_new_code varchar2(3);
1251 l_process_type_new varchar2(20);
1252 l_process_type_updated boolean;
1253 l_input_value_id_tbl hr_entry.number_table;
1254 l_entry_value_tbl hr_entry.varchar2_table;
1255
1256 cursor csr_get_ni_process_type is
1257 select element_entry_id,
1258 process_type,
1259 input_value_id5
1260 from PAY_NI_ELEMENT_ENTRIES_V pneev
1261 where pneev.assignment_id = p_assignment_id
1262 and g_effective_date between pneev.effective_start_date and pneev.effective_end_date;
1263 BEGIN
1264 if p_per_rec.director_flag = 'Y' and
1265 p_per_rec.person_type in ('EMP', 'EMP_APL') then
1266
1267 open csr_get_ni_process_type;
1268 fetch csr_get_ni_process_type into l_element_entry_id,
1269 l_process_type,
1270 l_input_value_id ;
1271 close csr_get_ni_process_type;
1272 l_process_type_updated := FALSE;
1273 if l_process_type = 'DP' then
1274 l_process_type_new := 'Director';
1275 l_process_type_new_code := 'DY';
1276 l_process_type_updated := TRUE;
1277 end if;
1278 if l_process_type = 'DR' then
1279 l_process_type_new := 'Director Normal';
1280 l_process_type_new_code := 'DN';
1281 l_process_type_updated := TRUE;
1282 end if;
1283
1284 if l_process_type_updated then
1285 conv_to_table_ni(l_process_type_new,
1286 l_input_value_id,
1287 l_num_entry_values,
1288 l_input_value_id_tbl,
1289 l_entry_value_tbl) ;
1290
1291 if g_validate_only = 'N' then /*Added soy 08-09*/
1292 hr_utility.trace(' In Validate And Commit Mode therefore updating');
1293 hr_entry_api.update_element_entry (
1294 p_dt_update_mode => 'UPDATE',
1295 p_session_date => g_effective_date,
1296 p_element_entry_id => l_element_entry_id,
1297 p_num_entry_values => l_num_entry_values,
1298 p_input_value_id_tbl => l_input_value_id_tbl,
1299 p_entry_value_tbl => l_entry_value_tbl );
1300 end if;
1301
1302 return (l_process_type || ' ' || l_process_type_new_code || ' Update');
1303 end if;
1304 end if;
1305
1306 return null;
1307 END process_directors;
1308
1309 ---------------------------------------------------------------------
1310 -- NAME : uplift_taxcode --
1311 -- Type : Private Procedure --
1312 -- DESCRIPTION : --
1313 -- This procedure will uplift tax code for the assignment --
1314 ---------------------------------------------------------------------
1315 PROCEDURE uplift_taxcode(p_asg_typ in varchar2,
1316 p_paye_rec in out nocopy g_typ_paye_record,
1317 p_aggregate_flag varchar2)
1318 IS
1319 l_uplift_value number;
1320 l_new_value number;
1321 BEGIN
1322 -----------------------------------------------------------------------------
1323 -- See if we need to update the tax code by checking the assignment status --
1324 -- If assignment is not active or suspend, then don't do anything --
1325 -- (In case of non aggregated assignments consider terminated assg also --
1326 -- this change is for SOY 08-09) --
1327 -----------------------------------------------------------------------------
1328 if p_aggregate_flag ='Y' then
1329 if (p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN','TERM_ASSIGN') and /*Added terminated for soy 08-09*/
1330 p_paye_rec.tax_code_suffix is not null) then
1331 l_uplift_value := get_uplift_value(p_paye_rec.tax_code_suffix);
1332 if nvl(l_uplift_value,0) <> 0 then
1333 l_new_value := p_paye_rec.tax_code_value + l_uplift_value;
1334 if l_new_value < 0 then
1335 p_paye_rec.tax_code_sv := '0T';
1336 else
1337 p_paye_rec.tax_code_sv := pysoytls.trim(p_paye_rec.tax_code_prefix ||
1338 fnd_number.number_to_canonical(l_new_value) ||
1339 p_paye_rec.tax_code_suffix);
1340 end if;
1341 p_paye_rec.tax_code_amended := TRUE;
1342 end if;
1343 end if;
1344 elsif p_aggregate_flag ='N' then
1345 if (p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN') and
1346 p_paye_rec.tax_code_suffix is not null) then
1347 l_uplift_value := get_uplift_value(p_paye_rec.tax_code_suffix);
1348 if nvl(l_uplift_value,0) <> 0 then
1349 l_new_value := p_paye_rec.tax_code_value + l_uplift_value;
1350 if l_new_value < 0 then
1351 p_paye_rec.tax_code_sv := '0T';
1352 else
1353 p_paye_rec.tax_code_sv := pysoytls.trim(p_paye_rec.tax_code_prefix ||
1354 fnd_number.number_to_canonical(l_new_value) ||
1355 p_paye_rec.tax_code_suffix);
1356 end if;
1357 p_paye_rec.tax_code_amended := TRUE;
1358 end if;
1359 end if;
1360 end if;
1361 END uplift_taxcode;
1362 ---------------------------------------------------------------------
1363 -- NAME : update_p45_taxbasis --
1364 -- Type : Private Procedure --
1365 -- DESCRIPTION : --
1366 -- This procedure will clear down P45 figures n update Tax Basis --
1367 -- from Non Cumulative to Cumulative --
1368 ---------------------------------------------------------------------
1369 PROCEDURE update_p45_taxbasis(p_asg_typ in varchar2,
1370 p_paye_rec in out nocopy g_typ_paye_record,
1371 p_aggregate_flag varchar2)
1372 IS
1373 BEGIN
1374 if p_aggregate_flag ='Y' then
1375 if p_paye_rec.tax_code_sv not in ('D0','SD0') then /*Added terminated for soy 08-09*/
1376 if p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN','TERM_ASSIGN') and g_cpe_flag ='Y' then
1377 if p_paye_rec.tax_basis_sv = 'Non Cumulative' then
1378 p_paye_rec.tax_basis_sv := 'Cumulative';
1379 p_paye_rec.tax_basis_amended := TRUE;
1380 hr_utility.trace(' Tax Basis update');
1381 end if;
1382 end if;
1383 end if;
1384 elsif p_aggregate_flag ='N' then
1385 if p_paye_rec.tax_code_sv not in ('D0','SD0') then
1386 if p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN') then
1387 if p_paye_rec.tax_basis_sv = 'Non Cumulative' then
1388 p_paye_rec.tax_basis_sv := 'Cumulative';
1389 p_paye_rec.tax_basis_amended := TRUE;
1390 hr_utility.trace(' Tax Basis update');
1391 end if;
1392 end if;
1393 end if;
1394 end if;
1395 --
1396 if (to_number(nvl(p_paye_rec.pay_previous_sv,0)) > 0 or
1397 to_number(nvl(p_paye_rec.tax_previous_sv,0)) > 0) then
1398 p_paye_rec.pay_previous_sv := '0';
1399 p_paye_rec.tax_previous_sv := '0';
1400 p_paye_rec.p45_val_amended := TRUE;
1401 hr_utility.trace(' P45 Update');
1402 end if;
1403 END update_p45_taxbasis;
1404
1405 ---------------------------------------------------------------------
1406 -- NAME : check_p45_figures --
1407 -- Type : Private Function --
1408 -- DESCRIPTION : --
1409 -- This function will check the incoming P45 figures from the EDI --
1410 -- and return any error message. This function is for mode 4 --
1411 ---------------------------------------------------------------------
1412 FUNCTION check_p45_figures(p_m34_rec in g_tax_code_interface,
1413 p_paye_rec in out nocopy g_typ_paye_record) return VARCHAR2
1414 IS
1415 ex_p45_figures exception;
1416 l_msg varchar2(255);
1417 l_p6_pay number;
1418 l_p6_tax number;
1419 l_p45_pay number;
1420 l_p45_tax number;
1421 BEGIN
1422 l_p6_pay := nvl(p_m34_rec.previous_pay,0);
1423 l_p6_tax := nvl(p_m34_rec.previous_tax,0);
1424 l_p45_pay := nvl(p_paye_rec.pay_previous_sv,0);
1425 l_p45_tax := nvl(p_paye_rec.tax_previous_sv,0);
1426 ---------------------------------------
1427 -- If value are the same, do nothing --
1428 ---------------------------------------
1429 if (l_p6_pay = l_p45_pay) and (l_p6_tax = l_p45_tax) then
1430 return null;
1431 end if;
1432 --------------------------------------------------
1433 -- If incoming values are zero then, do nothing --
1434 --------------------------------------------------
1435 if ( l_p6_pay = 0) and (l_p6_tax = 0) then
1436 return null;
1437 end if;
1438 --------------------------------------------------------
1439 -- If incoming prev pay = 0, but prev tax <> 0; error --
1440 --------------------------------------------------------
1441 if (l_p6_pay = 0) and (l_p6_tax <> 0) then
1442 l_msg := err_p6_pay_and_tax;
1443 raise ex_p45_figures;
1444 end if;
1445 --------------------------------------------------------------------------
1446 -- If the current P45 figures are zero but P6 are not; update the value --
1447 --------------------------------------------------------------------------
1448 if ((l_p45_pay = 0) and (l_p45_tax = 0)) and
1449 ((l_p6_pay <> 0) or (l_p6_tax <> 0)) then
1450 p_paye_rec.tax_previous_sv := l_p6_tax;
1451 p_paye_rec.pay_previous_sv := l_p6_pay;
1452 p_paye_rec.p45_val_amended := TRUE;
1453 return null;
1454 end if;
1455 -------------------------------------------------------------
1456 -- If P45 and P6 are not zero, check the diff between them --
1457 -------------------------------------------------------------
1458 if abs(l_p45_pay - l_p6_pay) >= 1000 or
1459 abs(l_p45_tax - l_p6_tax) >= 1000 then
1460 l_msg := err_p45_p6_figures;
1461 raise ex_p45_figures;
1462 end if;
1463 ---------------------------------------------
1464 -- Getting this far, then assumes no error --
1465 ---------------------------------------------
1466 p_paye_rec.tax_previous_sv := l_p6_tax;
1467 p_paye_rec.pay_previous_sv := l_p6_pay;
1468 p_paye_rec.p45_val_amended := TRUE;
1469 return null;
1470 EXCEPTION
1471 WHEN ex_p45_figures THEN
1472 return l_msg;
1473 END check_p45_figures;
1474
1475
1476
1477 ---------------------------------------------------------------------
1478 -- NAME : conv_to_paye_rec --
1479 -- Type : Private Function --
1480 -- DESCRIPTION : --
1481 -- This function will setup the paye_rec data record and will --
1482 -- return any error message. This function is for mode 3 and 4 --
1483 ---------------------------------------------------------------------
1484 FUNCTION conv_to_paye_rec(p_asg_id in number,
1485 p_m34_rec in g_tax_code_interface,
1486 p_paye_rec in out nocopy g_typ_paye_record) return VARCHAR2
1487 IS
1488 ex_error exception;
1489 l_msg varchar2(255);
1490 BEGIN
1491 hr_utility.trace(' Conv to PAYE Rec (mode 3 | 4)');
1492 hr_utility.trace(' Incoming tax basis: ' || p_m34_rec.tax_basis );
1493 hr_utility.trace(' Incoming tax code : ' || p_m34_rec.tax_code);
1494 hr_utility.trace(' Incoming P45 (P|T): ' || nvl(p_m34_rec.previous_pay,0) || ' | ' || nvl(p_m34_rec.previous_tax,0));
1495 hr_utility.trace(' Current tax basis : ' || p_paye_rec.tax_basis_sv);
1496 hr_utility.trace(' Current tax code : ' || p_paye_rec.tax_code_sv);
1497 hr_utility.trace(' Current P45 (P|T) : ' || nvl(p_paye_rec.pay_previous_sv,0) || ' | ' || nvl(p_paye_rec.tax_previous_sv,0));
1498 -------------------------------------------
1499 -- First we check the incoming tax basis --
1500 -------------------------------------------
1501 if p_m34_rec.tax_basis is null then
1502 if p_paye_rec.tax_basis_sv <> 'Cumulative' then
1503 hr_utility.trace(' Updating tax basis');
1504 p_paye_rec.tax_basis_sv := 'Cumulative';
1505 p_paye_rec.tax_basis_amended := TRUE;
1506 end if;
1507 elsif p_m34_rec.tax_basis = 'Y' then
1508 if p_paye_rec.tax_basis_sv <> 'Non Cumulative' then
1509 hr_utility.trace(' Updating tax basis');
1510 p_paye_rec.tax_basis_sv := 'Non Cumulative';
1511 p_paye_rec.tax_basis_amended := TRUE;
1512 end if;
1513 else
1514 l_msg := replace(err_tax_basis,'TAX_BASIS',p_m34_rec.tax_basis);
1515 raise ex_error;
1516 end if;
1517 -------------------------------------------
1518 -- Now checking the incoming tax code --
1519 -------------------------------------------
1520 if p_m34_rec.tax_code <> p_paye_rec.tax_code_sv then
1521 l_msg := validate_tax_code(p_m34_rec.tax_code, p_m34_rec.effective_date, p_asg_id);
1522 if l_msg is not null then
1523 raise ex_error;
1524 end if;
1525 p_paye_rec.tax_code_sv := p_m34_rec.tax_code;
1526 p_paye_rec.tax_code_amended := TRUE;
1527 hr_utility.trace(' Updating tax code');
1528 end if;
1529 --------------------------------------------
1530 -- If the incoming tax code is D0, --
1531 -- change the tax basis to Non Cumulative --
1532 --------------------------------------------
1533 if p_paye_rec.tax_basis_sv <> 'Non Cumulative' and
1534 p_paye_rec.tax_code_sv in ('D0','SD0') then
1535 p_paye_rec.tax_basis_sv := 'Non Cumulative';
1536 p_paye_rec.tax_basis_amended := TRUE;
1537 end if;
1538 -------------------------------------------
1539 -- Now we check the incoming P45 Figures --
1540 -------------------------------------------
1541 if g_mode = 4 then
1542 l_msg := check_p45_figures(p_m34_rec,p_paye_rec);
1543 if l_msg is not null then
1544 raise ex_error;
1545 end if;
1546 end if;
1547 return null;
1548 EXCEPTION
1549 WHEN ex_error THEN
1550 return l_msg;
1551 END conv_to_paye_rec;
1552
1553 ---------------------------------------------------------------------
1554 -- NAME : set_new_paye_record --
1555 -- Type : Private Function --
1556 -- DESCRIPTION : --
1557 -- This function will update the PAYE and return any error msg --
1558 ---------------------------------------------------------------------
1559 FUNCTION set_new_paye_record(p_asg_typ in varchar2,
1560 p_asg_id in number,
1561 p_paye_rec in out nocopy g_typ_paye_record,
1562 p_m34_rec in g_tax_code_interface default null,
1563 p_aggregate_flag in varchar2 default 'N') return VARCHAR2
1564 IS
1565 ex_error exception;
1566 l_msg varchar2(255);
1567 l_multi_change boolean;
1568 l_future date;
1569 l_check_date date;
1570 BEGIN
1571 hr_utility.trace(' Set New PAYE Record');
1572 l_check_date := g_effective_date;
1573 l_multi_change := FALSE;
1574 ----------------
1575 -- For mode 1 --
1576 ----------------
1577 if g_mode = 1 then
1578 hr_utility.trace(' Updating P45 figures');
1579 update_p45_taxbasis(p_asg_typ,p_paye_rec, p_aggregate_flag);
1580 ----------------------
1581 -- For mode 3 and 4 --
1582 ----------------------
1583 elsif g_mode in (3,4) and g_cpe_flag ='Y' then /*CPE condition Added soy 08-09*/
1584 hr_utility.trace(' Calling conv_to_paye_rec');
1585 l_msg := conv_to_paye_rec(p_asg_id,p_m34_rec,p_paye_rec);
1586 if l_msg is not null then
1587 raise ex_error;
1588 end if;
1589 l_check_date := p_m34_rec.effective_date;
1590 end if;
1591 hr_utility.trace(' Check Date : ' || l_check_date);
1592 ----------------------------------
1593 -- Check for any future changes --
1594 ----------------------------------
1595 l_future := check_future_changes(p_asg_id, l_check_date, l_multi_change);
1596 if l_multi_change then
1597 hr_utility.trace(' Multiple future changes found');
1598 l_msg := err_multi_fchanges;
1599 raise ex_error;
1600 end if;
1601
1602 if l_future is not null then
1603 hr_utility.trace(' Future change found');
1604 if g_mode = 1 then
1605 -------------------------------------------------------
1606 -- For mode 1, if exists change on the same date and --
1607 -- no change to P45, reject the record --
1608 -------------------------------------------------------
1609 if l_future = l_check_date and
1610 not p_paye_rec.p45_val_amended then
1611 l_msg := replace(err_future_changes,'DATE',to_char(l_future,'DD-MON-YYYY'));
1612 raise ex_error;
1613 elsif l_future > l_check_date then
1614 l_msg := replace(err_future_changes,'DATE',to_char(l_future,'DD-MON-YYYY'));
1615 raise ex_error;
1616 end if;
1617 -------------------------------------------------
1618 -- There is a change on same day, but have to --
1619 -- clear down P45/TaxBasis, so Correction mode --
1620 -------------------------------------------------
1621 p_paye_rec.dt_update_mode := correct_mode;
1622 elsif g_mode = 2 then
1623 ------------------------------------------------
1624 -- For mode 1 and 2, if exists future changes --
1625 -- reject the record. --
1626 ------------------------------------------------
1627 if l_future >= l_check_date then
1628 l_msg := replace(err_future_changes,'DATE',to_char(l_future,'DD-MON-YYYY'));
1629 raise ex_error;
1630 end if;
1631 elsif g_mode in (3,4) then
1632 ------------------------------------------------
1633 -- For mode 3 and 4, if exists future changes --
1634 -- but the change is on the same day, proceed --
1635 -- otherwise reject --
1636 ------------------------------------------------
1637 if l_future = l_check_date then
1638 p_paye_rec.dt_update_mode := correct_mode;
1639 else
1640 l_msg := replace(err_future_changes,'DATE',to_char(l_future,'DD-MON-YYYY'));
1641 raise ex_error;
1642 end if;
1643 end if;
1644 else
1645 ----------------------------
1646 -- Future change is nulll --
1647 ----------------------------
1648 p_paye_rec.dt_update_mode := update_mode;
1649 end if;
1650
1651 ----------------------
1652 -- For mode 1 and 2 -- /*CPE condition Added soy 08-09*/
1653 ----------------------
1654 if g_mode in (1,2) and p_paye_rec.dt_update_mode = update_mode and g_cpe_flag ='Y'then
1655 uplift_taxcode(p_asg_typ, p_paye_rec,p_aggregate_flag);
1656 end if;
1657 -----------------------
1658 -- Set the authority --
1659 -----------------------
1660 hr_utility.trace(' Setting authority');
1661 p_paye_rec.authority_sv := g_authority;
1662 hr_utility.trace(' New PAYE Details element:');
1663 hr_utility.trace(' Tax Code : ' || p_paye_rec.tax_code_sv );
1664 hr_utility.trace(' Tax Basis : ' || p_paye_rec.tax_basis_sv );
1665 hr_utility.trace(' Previous Pay : ' || p_paye_rec.pay_previous_sv );
1666 hr_utility.trace(' Previous Tax : ' || p_paye_rec.tax_previous_sv );
1667 hr_utility.trace(' Authority : ' || p_paye_rec.authority_sv );
1668 return null;
1669 EXCEPTION
1670 WHEN ex_error THEN
1671 return l_msg;
1672 END set_new_paye_record;
1673
1674 ---------------------------------------------------------------------
1675 -- NAME : check_leaver --
1676 -- Type : Private Function --
1677 -- DESCRIPTION : --
1678 -- This function will check for leaver and return the appropriate --
1679 -- message --
1680 ---------------------------------------------------------------------
1681 FUNCTION check_leaver(p_per_rec in g_typ_per_record) return VARCHAR2
1682 IS
1683 ex_exemp exception;
1684 l_msg varchar2(255);
1685 BEGIN
1686 hr_utility.trace(' Check for Leaver');
1687 if p_per_rec.person_type in ('EX_EMP','EX_APL') then
1688 --
1689 hr_utility.trace(' LEAVER = TRUE');
1690 if g_mode in (3,4) then
1691 l_msg := err_mode34_ex_emp;
1692 elsif g_mode = 2 then
1693 l_msg := err_mode2_ex_emp;
1694 end if;
1695 --
1696 raise ex_exemp;
1697 end if;
1698 hr_utility.trace(' Not a leaver');
1699 return null;
1700 EXCEPTION
1701 when ex_exemp then
1702 return l_msg;
1703 END check_leaver;
1704
1705 ---------------------------------------------------------------------
1706 -- NAME : get_paye_record --
1707 -- Type : Private Function --
1708 -- DESCRIPTION : --
1709 -- This function will fetch PAYE element entry --
1710 -- Will return message if error found --
1711 ---------------------------------------------------------------------
1712 FUNCTION get_paye_record(p_assignment_id in number,
1713 p_date in date,
1714 p_paye_rec out nocopy g_typ_paye_record) return VARCHAR2
1715 IS
1716 ex_tax_code exception;
1717 l_msg varchar2(255);
1718 l_paye_rec g_typ_paye_record;
1719
1720 cursor csr_paye_rec is
1721 select ee.element_entry_id ,
1722 ee.effective_start_date ,
1723 ee.effective_end_date ,
1724 min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) tax_code_id ,
1725 min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) tax_code_sv ,
1726 min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) tax_basis_id ,
1727 min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) tax_basis_sv ,
1728 min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) pay_previous_id ,
1729 min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) pay_previous_sv ,
1730 min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) tax_previous_id ,
1731 min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) tax_previous_sv ,
1732 min(decode(inv.name, 'Authority', eev.input_value_id, null)) authority_id ,
1733 min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) authority_sv ,
1734 min(decode(inv.name, 'Refundable', eev.input_value_id, null)) refundable_id ,
1735 min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) refundable_sv,
1736 ee.creator_id
1737 from pay_element_entries_f ee,
1738 pay_element_entry_values_f eev,
1739 pay_input_values_f inv
1740 where ee.assignment_id = p_assignment_id
1741 and ee.element_type_id = g_element_type_id
1742 and ee.element_entry_id = eev.element_entry_id
1743 and eev.input_value_id = inv.input_value_id
1744 and p_date between ee.effective_start_date and ee.effective_end_date
1745 and p_date between eev.effective_start_date and eev.effective_end_date
1746 and p_date between inv.effective_start_date and inv.effective_end_date
1747 group by ee.element_entry_id, ee.effective_start_date, ee.effective_end_date,
1748 ee.creator_id;
1749 BEGIN
1750 open csr_paye_rec;
1751 fetch csr_paye_rec into l_paye_rec.element_entry_id,
1752 l_paye_rec.effective_start_date,
1753 l_paye_rec.effective_end_date,
1754 l_paye_rec.tax_code_id,
1755 l_paye_rec.tax_code_sv,
1756 l_paye_rec.tax_basis_id,
1757 l_paye_rec.tax_basis_sv,
1758 l_paye_rec.pay_previous_id,
1759 l_paye_rec.pay_previous_sv,
1760 l_paye_rec.tax_previous_id,
1761 l_paye_rec.tax_previous_sv,
1762 l_paye_rec.authority_id,
1763 l_paye_rec.authority_sv,
1764 l_paye_rec.refundable_id,
1765 l_paye_rec.refundable_sv,
1766 l_paye_rec.creator_id;
1767 close csr_paye_rec;
1768 l_paye_rec.tax_code_amended := FALSE;
1769 l_paye_rec.tax_basis_amended := FALSE;
1770 l_paye_rec.p45_val_amended := FALSE;
1771 post_fetch(l_paye_rec);
1772 if g_mode in (1,2) then
1773 if l_paye_rec.tax_code_value = 999999 then
1774 l_msg := replace(err_invalid_tax,'TAX_CODE',l_paye_rec.tax_code_sv);
1775 raise ex_tax_code;
1776 end if;
1777 end if;
1778 p_paye_rec := l_paye_rec;
1779 hr_utility.trace(' Element Entry ID : ' || p_paye_rec.element_entry_id );
1780 hr_utility.trace(' Start Date : ' || p_paye_rec.effective_start_date );
1781 hr_utility.trace(' End Date : ' || p_paye_rec.effective_end_date );
1782 hr_utility.trace(' Tax Code : ' || p_paye_rec.tax_code_sv );
1783 hr_utility.trace(' Tax Basis : ' || p_paye_rec.tax_basis_sv );
1784 hr_utility.trace(' Previous Pay : ' || p_paye_rec.pay_previous_sv );
1785 hr_utility.trace(' Previous Tax : ' || p_paye_rec.tax_previous_sv );
1786 hr_utility.trace(' Authority : ' || p_paye_rec.authority_sv );
1787 return null;
1788 EXCEPTION
1789 WHEN ex_tax_code THEN
1790 return l_msg;
1791 END get_paye_record;
1792
1793 ---------------------------------------------------------------------
1794 -- NAME : find_employee --
1795 -- Type : Private Function --
1796 -- DESCRIPTION : --
1797 -- This function will find employee based on incoming data on the --
1798 -- EDI. Function will return 0 if not found, 1 if found and 2 if --
1799 -- more than 1 match is found --
1800 ---------------------------------------------------------------------
1801 FUNCTION find_employee(p_m34_rec IN OUT nocopy g_tax_code_interface,
1802 p_per_rec OUT nocopy g_typ_per_record) return VARCHAR2
1803 IS
1804 ex_error exception;
1805 l_person_rec g_typ_per_record;
1806 l_last_name varchar2(255);
1807 l_counter number;
1808 l_null_field number;
1809 l_count number;
1810 l_msg varchar2(255);
1811
1812 cursor csr_fetch_asg is
1813 select max(peo.person_id) p_id,
1814 peo.last_name,
1815 peo.full_name,
1816 peo.national_identifier,
1817 nvl(peo.per_information10,'N') agg_flag,
1818 'EMP', --ppt.system_person_type,
1819 pps.actual_termination_date,
1820 pps.last_standard_process_date,
1821 p_m34_rec.effective_date
1822 from per_all_people_f peo,
1823 per_all_assignments_f asg,
1824 per_periods_of_service pps
1825 where asg.business_group_id = g_business_group_id
1826 and peo.business_group_id = g_business_group_id
1827 and peo.person_id = asg.person_id
1828 -- Bug 6864422 modified p_m34_rec.full_name to substr of peo.last_name length
1829 --and upper(peo.last_name) like upper(substr(rpad(p_m34_rec.full_name,5,'%'), 1, 5))||'%'
1830 and upper(peo.last_name) like upper(substr(rpad(substr(p_m34_rec.full_name,1,length(peo.last_name)),5,'%'), 1, 5))||'%'
1831 --and upper(substr(rpad(peo.last_name,5,' '),1,5)) = upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5))
1832 and (p_m34_rec.assignment_number is null or
1833 asg.assignment_number = p_m34_rec.assignment_number)
1834 and (p_m34_rec.national_identifier is null or
1835 peo.national_identifier like substr(p_m34_rec.national_identifier,1,8)||'%')
1836 and pps.period_of_service_id = asg.period_of_service_id
1837 and p_m34_rec.effective_date between asg.effective_start_date and asg.effective_end_date
1838 and p_m34_rec.effective_date between peo.effective_start_date and peo.effective_end_date
1839 and (pps.actual_termination_date is null or
1840 p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
1841 and exists (select 1
1842 from per_person_type_usages_f ptu,
1843 per_person_types ppt
1844 where ptu.person_id = peo.person_id
1845 and ptu.person_type_id = ppt.person_type_id
1846 and ppt.system_person_type in ('EMP','EMP_APL')
1847 and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
1848 group by peo.last_name, peo.full_name, peo.national_identifier, peo.per_information10,
1849 'EMP' /*ppt.system_person_type*/, pps.actual_termination_date, pps.last_standard_process_date
1850 order by p_id;
1851
1852 cursor csr_count_emp(p_tax_district varchar2,
1853 p_person_id number,
1854 p_date date,
1855 p_asg_no varchar2 default null) is
1856 select count(*)
1857 from per_all_assignments_f asg,
1858 pay_all_payrolls_f pay,
1859 hr_soft_coding_keyflex sck
1860 where asg.person_id = p_person_id
1861 and asg.payroll_id = pay.payroll_id
1862 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1863 and sck.segment1 = p_tax_district
1864 and (p_asg_no is null or
1865 asg.assignment_number = p_asg_no
1866 )
1867 and p_date between asg.effective_start_date and asg.effective_end_date
1868 and p_date between pay.effective_start_date and pay.effective_end_date;
1869
1870 cursor csr_check_future_asg(p_tax_district varchar2,
1871 p_person_id number,
1872 p_date date) is
1873 select count(*)
1874 from per_all_people_f peo,
1875 per_all_assignments_f asg,
1876 pay_all_payrolls_f pay,
1877 hr_soft_coding_keyflex sck
1878 where peo.person_id = p_person_id
1879 and asg.person_id = peo.person_id
1880 and asg.payroll_id = pay.payroll_id
1881 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1882 and sck.segment1 = p_tax_district
1883 and nvl(peo.per_information10,'N') = 'Y'
1884 and asg.effective_start_date > p_date
1885 and p_date between pay.effective_start_date and pay.effective_end_date
1886 and asg.effective_start_date between peo.effective_start_date and peo.effective_end_date
1887 and asg.assignment_id not in (select assignment_id
1888 from per_all_assignments_f asg,
1889 pay_all_payrolls_f pay,
1890 hr_soft_coding_keyflex sck
1891 where asg.person_id = p_person_id
1892 and asg.payroll_id = pay.payroll_id
1893 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1894 and sck.segment1 = p_tax_district
1895 and p_date between asg.effective_start_date and asg.effective_end_date
1896 and p_date between pay.effective_start_date and pay.effective_end_date);
1897
1898 BEGIN
1899 l_null_field := 0;
1900 if upper(p_m34_rec.national_identifier) like '%NONE%' then
1901 p_m34_rec.national_identifier := null;
1902 l_null_field := l_null_field + 1;
1903 end if;
1904 if upper(p_m34_rec.assignment_number) like '%NONE%' then
1905 p_m34_rec.assignment_number := null;
1906 l_null_field := l_null_field + 1;
1907 end if;
1908
1909 if l_null_field > 1 then
1910 l_msg := err_data_mismatch;
1911 raise ex_error;
1912 end if;
1913
1914 l_counter := 0;
1915 for r_person_rec in csr_fetch_asg loop
1916 l_last_name := r_person_rec.last_name;
1917 if upper(substr(rpad(l_last_name,5,' '),1,5)) =
1918 -- Bug 6864422 modified p_m34_rec.full_name to substr of l_last_name length
1919 -- upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5)) then
1920 upper(substr(rpad(substr(p_m34_rec.full_name,1,length(l_last_name)),5,' '), 1, 5)) then
1921 l_counter := l_counter + 1;
1922 l_person_rec.person_id := r_person_rec.p_id;
1923 l_person_rec.full_name := r_person_rec.full_name;
1924 l_person_rec.ni_number := r_person_rec.national_identifier;
1925 l_person_rec.aggregate_flag := r_person_rec.agg_flag;
1926 l_person_rec.person_type := 'EMP';
1927 l_person_rec.term_date := r_person_rec.actual_termination_date;
1928 l_person_rec.lsp_date := r_person_rec.last_standard_process_date;
1929 end if;
1930 end loop;
1931 if l_counter = 0 then
1932 raise no_data_found;
1933 end if;
1934 if l_counter > 1 then
1935 raise too_many_rows;
1936 end if;
1937 if l_counter = 1 then
1938 p_per_rec.person_id := l_person_rec.person_id;
1939 p_per_rec.full_name := l_person_rec.full_name;
1940 p_per_rec.ni_number := l_person_rec.ni_number;
1941 p_per_rec.aggregate_flag := l_person_rec.aggregate_flag;
1942 p_per_rec.person_type := l_person_rec.person_type;
1943 p_per_rec.term_date := l_person_rec.term_date;
1944 p_per_rec.lsp_date := l_person_rec.lsp_date;
1945 p_per_rec.effective_date := p_m34_rec.effective_date;
1946 end if;
1947
1948 if p_m34_rec.assignment_number is null then
1949 open csr_count_emp(p_m34_rec.paye_ref,
1950 p_per_rec.person_id,
1951 p_m34_rec.effective_date);
1952 fetch csr_count_emp into l_count;
1953 close csr_count_emp;
1954
1955 if l_count = 0 then
1956 l_msg := err_emp_not_found;
1957 raise ex_error;
1958 end if;
1959
1960 if p_per_rec.aggregate_flag = 'N' then
1961 if l_count > 1 then
1962 l_msg := err_multi_asg;
1963 raise ex_error;
1964 end if;
1965 end if;
1966 else -- Even if the assignment number is supplied
1967 -- we need to check that assignment is on the
1968 -- given tax district
1969 open csr_count_emp(p_m34_rec.paye_ref,
1970 p_per_rec.person_id,
1971 p_m34_rec.effective_date,
1972 p_m34_rec.assignment_number);
1973 fetch csr_count_emp into l_count;
1974 close csr_count_emp;
1975 if l_count = 0 then
1976 l_msg := err_emp_not_found;
1977 raise ex_error;
1978 end if;
1979 end if;
1980
1981 if p_per_rec.aggregate_flag = 'Y' then
1982 open csr_check_future_asg(p_m34_rec.paye_ref,
1983 p_per_rec.person_id,
1984 p_m34_rec.effective_date);
1985 fetch csr_check_future_asg into l_count;
1986 close csr_check_future_asg;
1987 if l_count > 0 then
1988 l_msg := err_future_asg;
1989 raise ex_error;
1990 end if;
1991 end if;
1992
1993 return null;
1994 EXCEPTION
1995 when ex_error then
1996 return l_msg;
1997 when too_many_rows then
1998 return err_multiple_found;
1999 when no_data_found then
2000 return err_emp_not_found;
2001 when others then
2002 raise;
2003 END find_employee;
2004
2005 ---------------------------------------------------------------------
2006 -- NAME : update_record --
2007 -- Type : Private Function --
2008 -- DESCRIPTION : --
2009 -- Function to updates the PAYE Element entries by calling the --
2010 -- Element Entry API. Returns any message if error found --
2011 ---------------------------------------------------------------------
2012 FUNCTION update_record(p_paye_rec IN g_typ_paye_record,
2013 p_per_rec IN g_typ_per_record,
2014 p_m34_rec IN g_tax_code_interface default null) return VARCHAR2
2015 IS
2016 l_issue_date date;
2017 l_message_date date;
2018 l_paye_rec g_typ_paye_record;
2019 l_input_value_id_tbl hr_entry.number_table;
2020 l_entry_value_tbl hr_entry.varchar2_table;
2021 l_row_id rowid;
2022 l_num_entry_values number;
2023
2024 cursor c1 (c_row_id in rowid) is
2025 select rowid from pay_gb_tax_code_interface
2026 where rowid = c_row_id
2027 for update;
2028
2029 BEGIN
2030 l_paye_rec := p_paye_rec;
2031 --
2032 if g_mode in (3,4) then
2033 l_issue_date := p_m34_rec.issue_date;
2034 l_message_date := p_m34_rec.date_of_message;
2035 end if;
2036
2037 ----------------------------
2038 -- Convert entry into tbl --
2039 ----------------------------
2040 conv_to_table(l_paye_rec,l_num_entry_values,
2041 l_input_value_id_tbl,l_entry_value_tbl);
2042 ----------------------------
2043 -- Call API to update --
2044 ----------------------------
2045
2046 if g_validate_only ='N' then /*Added soy 08-09*/
2047 hr_utility.trace(' In Validate And Commit Mode therefore updating.');
2048 hr_entry_api.update_element_entry(
2049 p_dt_update_mode => rtrim(p_paye_rec.dt_update_mode),
2050 p_session_date => p_per_rec.effective_date,
2051 p_element_entry_id => p_paye_rec.element_entry_id,
2052 p_num_entry_values => l_num_entry_values,
2053 p_input_value_id_tbl => l_input_value_id_tbl,
2054 p_entry_value_tbl => l_entry_value_tbl,
2055 p_entry_information_category => 'GB_PAYE',
2056 p_entry_information1 => fnd_date.date_to_canonical(l_issue_date),
2057 p_entry_information2 => fnd_date.date_to_canonical(l_message_date));
2058 end if;
2059
2060 ---------------------------------------------------
2061 -- If mode 3 or 4, update the tax code interface --
2062 ---------------------------------------------------
2063 if (g_mode in (3,4)) then
2064 open c1(p_m34_rec.row_id);
2065 fetch c1 into l_row_id;
2066 update pay_gb_tax_code_interface
2067 set processed_flag = 'P'
2068 where current of c1;
2069 close c1;
2070 end if;
2071 ------------------------------
2072 -- Now stamp the creator id --
2073 ------------------------------
2074 if g_validate_only ='N' then /*Added soy 08-09*/
2075 hr_utility.trace(' In Validate And Commit Mode therefore updating.');
2076 update pay_element_entries_f pef
2077 set pef.creator_id = nvl(g_request_id,g_current_req_id)
2078 where pef.element_entry_id = p_paye_rec.element_entry_id
2079 and pef.effective_start_date = p_per_rec.effective_date;
2080 --and pef.effective_start_date = p_paye_rec.effective_start_date
2081 --and pef.effective_end_date = p_paye_rec.effective_end_date;
2082 end if;
2083 ------------------------------------------
2084 -- No error, so update the update count --
2085 ------------------------------------------
2086 g_update_count := g_update_count + 1;
2087 --
2088 return null;
2089 EXCEPTION
2090 WHEN others THEN
2091 return substrb(sqlerrm(sqlcode),1,60);
2092 END update_record;
2093
2094 ---------------------------------------------------------------------
2095 -- NAME : process_record --
2096 -- Type : Private Procedure --
2097 -- DESCRIPTION : --
2098 -- This procedure will process the given assignment based on the --
2099 -- mode --
2100 ---------------------------------------------------------------------
2101 PROCEDURE process_record(p_m12_rec IN g_typ_per_record default null,
2102 p_m34_rec IN g_tax_code_interface default null)
2103 IS
2104 ex_edi_error exception;
2105 ex_resume_mode exception;
2106 ex_asg_error exception;
2107 ex_not_process exception;
2108 l_exists number;
2109 l_process boolean;
2110 l_dir varchar2(255);
2111 l_msg varchar2(255);
2112 l_paye_ref varchar2(30);
2113 l_asg_number varchar2(100);
2114 l_per_record g_typ_per_record;
2115 l_paye_rec g_typ_paye_record;
2116 l_old_paye_rec g_typ_paye_record;
2117 l_m34_rec g_tax_code_interface;
2118 BEGIN
2119 l_msg := null;
2120 l_paye_ref := null;
2121 l_asg_number := null;
2122 ------------------------------------
2123 -- First setup the record details --
2124 ------------------------------------
2125 if g_mode in (1,2) then
2126 l_per_record := p_m12_rec;
2127 elsif g_mode in (3,4) then
2128 l_m34_rec := p_m34_rec;
2129 l_msg := find_employee(l_m34_rec, l_per_record);
2130 if l_msg is not null then
2131 raise ex_edi_error;
2132 end if;
2133 l_paye_ref := l_m34_rec.paye_ref;
2134 l_asg_number := l_m34_rec.assignment_number;
2135 end if;
2136 hr_utility.trace('Start processing record');
2137 hr_utility.trace(' Person ID : ' || l_per_record.person_id);
2138 hr_utility.trace(' NI Number : ' || l_per_record.ni_number);
2139 hr_utility.trace(' Aggregated : ' || l_per_record.aggregate_flag);
2140 hr_utility.trace(' Director : ' || l_per_record.director_flag);
2141 hr_utility.trace(' Person type : ' || l_per_record.person_type);
2142 hr_utility.trace(' Term date : ' || l_per_record.term_date);
2143 hr_utility.trace(' LSP date : ' || l_per_record.lsp_date);
2144 hr_utility.trace(' Start date : ' || l_per_record.start_date);
2145 hr_utility.trace(' End date : ' || l_per_record.end_date);
2146
2147 -----------------------------------------------------------------------
2148 -- Now, loop through each assignment and see if we should process it --
2149 -----------------------------------------------------------------------
2150 for asg_record in csr_asg_details(l_per_record.person_id,
2151 l_per_record.aggregate_flag,
2152 l_paye_ref,
2153 l_asg_number,
2154 l_per_record.effective_date)
2155 loop
2156 hr_utility.trace('Start processing record');
2157 hr_utility.trace(' Assignment ID : ' || asg_record.assignment_id);
2158 hr_utility.trace(' Payroll ID : ' || asg_record.payroll_id);
2159 hr_utility.trace(' Assignment No : ' || asg_record.assignment_number);
2160 hr_utility.trace(' Asg Status : ' || asg_record.asg_status);
2161 hr_utility.trace(' PAYE Ref : ' || asg_record.tax_ref);
2162 --------------------------------------------
2163 -- Anonymous block to trap non fatal error --
2164 ---------------------------------------------
2165 begin
2166
2167 l_dir := null;
2168 l_process := true;
2169 g_cpe_flag :='Y'; /*Added soy 08-09*/
2170
2171 ----------------------
2172 -- Check for EX-EMP --
2173 ----------------------
2174 l_msg := check_leaver(l_per_record);
2175 if l_msg is not null then
2176 raise ex_asg_error;
2177 end if;
2178
2179 hr_utility.trace(' Fetch PAYE record');
2180 l_msg := get_paye_record(asg_record.assignment_id,
2181 l_per_record.effective_date,
2182 l_paye_rec);
2183 ------------------------------
2184 -- Check for tax code error --
2185 ------------------------------
2186 if l_msg is not null then
2187 raise ex_asg_error;
2188 end if;
2189 --------------------------
2190 -- See if we found PAYE --
2191 --------------------------
2192 if l_paye_rec.element_entry_id is null then
2193 if g_mode in (3,4) then
2194 l_msg := replace(err_no_paye_ele,'DATE',to_char(l_per_record.effective_date,'DD-MON-YYYY'));
2195 raise ex_asg_error;
2196 else
2197 raise ex_not_process;
2198 end if;
2199 end if;
2200 ---------------------------
2201 -- Check for resume mode --
2202 ---------------------------
2203 open get_req_cur (l_paye_rec.creator_id);
2204 fetch get_req_cur into l_exists;
2205 if get_req_cur%FOUND then
2206 l_process := false;
2207 hr_utility.trace(' Not processing the assignment');
2208 end if;
2209 close get_req_cur;
2210 ------------------------------------------------------------------
2211 -- If part of this assignment already been processed --
2212 -- we can assumed that the whole person already been processed --
2213 -- as the commit is done on a full-person basis --
2214 ------------------------------------------------------------------
2215 if l_process = false then
2216 raise ex_resume_mode;
2217 end if;
2218
2219 --------------------------------------------------------------------------
2220 --Check assignment's CPE In case of aggegated and terminated Assignment --
2221 --If this terminated assignment is in active CPE then we have to process--
2222 --the assignment Added soy 08-09 --
2223 --------------------------------------------------------------------------
2224 if l_per_record.aggregate_flag ='Y' AND asg_record.asg_status ='TERM_ASSIGN' then
2225 g_cpe_flag := set_cpe_flag(l_per_record.person_id,
2226 asg_record.assignment_id,
2227 asg_record.tax_ref,
2228 l_per_record.effective_date );
2229
2230 end if;
2231 ------------------------------------
2232 -- Make a copy of the paye record --
2233 ------------------------------------
2234 l_old_paye_rec := l_paye_rec;
2235 -----------------------------
2236 -- Set the new PAYE record --
2237 -----------------------------
2238 l_msg := set_new_paye_record(asg_record.asg_status,
2239 asg_record.assignment_id,
2240 l_paye_rec, l_m34_rec,l_per_record.aggregate_flag);
2241 if l_msg is not null then
2242 if g_payroll_id is not null then
2243 if asg_record.payroll_id <> g_payroll_id then
2244 raise ex_not_process;
2245 else
2246 raise ex_asg_error;
2247 end if;
2248 else
2249 raise ex_asg_error;
2250 end if;
2251 end if;
2252 hr_utility.trace(' Element Entry ID : ' || l_paye_rec.element_entry_id );
2253 hr_utility.trace(' Start Date : ' || l_paye_rec.effective_start_date );
2254 hr_utility.trace(' End Date : ' || l_paye_rec.effective_end_date );
2255 hr_utility.trace(' Tax Code : ' || l_paye_rec.tax_code_sv );
2256 hr_utility.trace(' Tax Basis : ' || l_paye_rec.tax_basis_sv );
2257 hr_utility.trace(' Previous Pay : ' || l_paye_rec.pay_previous_sv );
2258 hr_utility.trace(' Previous Tax : ' || l_paye_rec.tax_previous_sv );
2259 hr_utility.trace(' Authority : ' || l_paye_rec.authority_sv );
2260 hr_utility.trace(' Done setting new PAYE');
2261 -----------------------------------------------
2262 -- If mode 1 then try to process NI director --
2263 -----------------------------------------------
2264 if g_mode = 1 then
2265 hr_utility.trace(' Check if we need to do any update');
2266 l_dir := process_directors(l_per_record,asg_record.assignment_id);
2267 if l_paye_rec.tax_basis_amended or l_paye_rec.p45_val_amended or
2268 l_paye_rec.tax_code_amended or l_dir is not null then
2269 hr_utility.trace(' Calling update_record to update PAYE');
2270 l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
2271 if l_msg is not null then
2272 raise ex_asg_error;
2273 end if;
2274 hr_utility.trace(' Write out body section');
2275 write_body(l_old_paye_rec,l_paye_rec,l_per_record, asg_record.assignment_number,l_dir,
2276 p_m34_rec.effective_date,asg_record.assignment_id, asg_record.tax_ref);
2277 end if;
2278 else
2279 hr_utility.trace(' Check if we need to do any update');
2280 -- if l_paye_rec.tax_basis_amended or l_paye_rec.p45_val_amended or
2281 -- l_paye_rec.tax_code_amended then
2282 hr_utility.trace(' Calling update_record to update PAYE');
2283 --Added for Bug 7373763
2284 if g_mode=2 then
2285 if l_paye_rec.tax_code_amended=TRUE then
2286 l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
2287 if l_msg is not null then
2288 raise ex_asg_error;
2289 end if;
2290 hr_utility.trace(' Write out body section');
2291 write_body(l_old_paye_rec,l_paye_rec,l_per_record, asg_record.assignment_number,l_dir,
2292 p_m34_rec.effective_date, asg_record.assignment_id, asg_record.tax_ref);
2293 end if;
2294 else
2295 l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
2296 if l_msg is not null then
2297 raise ex_asg_error;
2298 end if;
2299 hr_utility.trace(' Write out body section');
2300 write_body(l_old_paye_rec,l_paye_rec,l_per_record, asg_record.assignment_number,l_dir,
2301 p_m34_rec.effective_date, asg_record.assignment_id, asg_record.tax_ref);
2302 end if;
2303 --Bug 7373763 ends
2304
2305 -- end if;
2306 end if;
2307 exception
2308 WHEN ex_not_process THEN
2309 null;
2310 WHEN ex_asg_error THEN
2311 reject_record(l_per_record, l_m34_rec, l_msg, asg_record.assignment_number);
2312 end;
2313 ----------------------------
2314 -- End of Anonymous block --
2315 ----------------------------
2316 end loop;
2317 -- Need to do a commit here
2318 if g_validate_only ='N' then /*Added soy 08-09*/
2319 hr_utility.trace(' In Validate And Commit Mode therefore Commiting.');
2320 if check_commit then
2321 commit;
2322 end if;
2323 end if;
2324 EXCEPTION
2325 WHEN ex_edi_error then
2326 reject_record(null, l_m34_rec, l_msg, null);
2327 WHEN ex_resume_mode then
2328 null;
2329 END process_record;
2330
2331 ---------------------------------------------------------------------
2332 -- NAME --
2333 -- pyudet.run_process PUBLIC PROCEDURE --
2334 -- --
2335 -- DESCRIPTION --
2336 -- The main procedure called from the SRS screen. The success or --
2337 -- or failure of the process is passed back to the SRS screen in --
2338 -- the retcode paramater --
2339 ---------------------------------------------------------------------
2340 PROCEDURE run_process(errbuf out nocopy varchar2,
2341 retcode out nocopy varchar2,
2342 p_request_id in number default null,
2343 p_mode in number,
2344 p_effective_date in date,
2345 p_business_group_id in number,
2346 p_payroll_id in number,
2347 p_authority in varchar2 default null,
2348 p_p6_request_id in number default null,
2349 p_validate_only in VARCHAR2 DEFAULT 'GB_VALIDATE_COMMIT')
2350 IS
2351 l_m34_rec g_tax_code_interface;
2352 l_m12_rec g_typ_per_record;
2353 l_person_id per_all_people_f.person_id%type;
2354 l_process boolean;
2355 BEGIN
2356 hr_utility.trace('Start of PYUDET');
2357 --------------------------------
2358 -- Setting up GLOBAL variable --
2359 --------------------------------
2360 set_global(p_request_id,p_mode,p_effective_date,p_business_group_id,
2361 p_payroll_id,p_authority,p_p6_request_id,p_validate_only );
2362
2363 --------------------------------
2364 -- Start the process --
2365 --------------------------------
2366 hr_utility.trace('Open main cursor');
2367 if g_mode in (1,2) then
2368 open csr_mode12;
2369 elsif g_mode in (3,4) then
2370 open csr_mode34;
2371 end if;
2372 ------------------
2373 -- Write header --
2374 ------------------
2375 hr_utility.trace('Write report header');
2376 write_header;
2377 hr_utility.trace('Write sub-report header');
2378 write_group_header;
2379
2380 -- Start the loop
2381 loop
2382 hr_utility.trace('In the main loop');
2383 -- Set l_process to true
2384 l_process := true;
2385 --
2386 if g_mode in(1,2) then
2387 fetch csr_mode12 into l_m12_rec;
2388 exit when csr_mode12%notfound;
2389 -- if the current person = the prev person, don't process
2390 if l_person_id = l_m12_rec.person_id then
2391 l_process := false;
2392 end if;
2393 -- copy the person id
2394 l_person_id := l_m12_rec.person_id;
2395 elsif g_mode in (3,4) then
2396 fetch csr_mode34 into l_m34_rec;
2397 exit when csr_mode34%notfound;
2398 end if;
2399 -- Process assignment
2400 if l_process then
2401 process_record(l_m12_rec,l_m34_rec);
2402 end if;
2403 end loop;
2404 ------------------
2405 -- Write footer --
2406 ------------------
2407 hr_utility.trace('Write report footer');
2408 write_footer;
2409
2410 -- Clear out the Tax Code Interface table
2411 if g_mode in (3,4) then
2412 hr_utility.trace('Clearing tax code interface');
2413 delete pay_gb_tax_code_interface
2414 where (request_id is null or request_id = g_p6_request_id)
2415 and processed_flag = 'P'; /*Added soy 08-09*/
2416 commit;
2417 end if;
2418 EXCEPTION
2419 WHEN Others THEN
2420 hr_utility.trace('Error occurs : ' || sqlerrm);
2421 if g_mode in (1,2) then
2422 if csr_mode12%isopen then
2423 close csr_mode12;
2424 end if;
2425 elsif g_mode in (3,4) then
2426 if csr_mode34%isopen then
2427 close csr_mode34;
2428 end if;
2429 end if;
2430 rollback;
2431 errbuf := sqlerrm;
2432 retcode := 2;
2433 raise;
2434 END run_process;
2435
2436 ---------------------------------------------------------------------
2437 -- Instantiation Section --
2438 -- --
2439 -- This section will be executed the first time that the package --
2440 -- is brought into memory for this session --
2441 ---------------------------------------------------------------------
2442 BEGIN
2443 hr_utility.set_location('pyudet',0);
2444 -------------------------------------------------------------------
2445 -- Initialize the count variable for the 'uplift_value' function --
2446 -------------------------------------------------------------------
2447 g_uplift_value(0) := -1;
2448 --
2449 hr_utility.set_location('pyudet',999);
2450 END;
[Home] [Help]