1 package body pyudet as
2 /* $Header: pyudet.pkb 120.69.12020000.2 2013/01/22 13:16:48 ssarap 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 10-NOV-2006 K.Thampan 115.74 Rewrite package for SOY 07.
20 Truncate the change list,
21 see version 115.73 for
22 previous changes.
23 115.75 Fix GSCC error
24 22-NOV-2006 K.Thampan 115.76 Remove check for NI Director
25 start date.
26 11-JAN-2007 K.Thampan 115.79 5757305 Added more validation for mode
27 3/4.
28 23-FEB-2007 K.Thampan 115.80 Fix tax basis error
29 115.81 Change from DO to D0
30 115.82 Added checked for tax_ref
31 in mode 3,4 for non-aggregated
32 assignment.
33 02-MAR-2007 K.Thampan 115.83 5912261 Apply format mask to tax
34 district number
35 12-MAR-2007 K.Thampan 115.85 5927555 Added table alias
36 22-MAR-2007 K.Thampan 115.86 5948728 Amended report for mode 3/4 to
37 output unprocess record as
38 correction.
39 26-MAR-2007 K.Thampan 115.87 5953974 Amended update_record procedure
40 23-APR-2007 K.Thampan 115.88 5962025 Performance fix.
41 30-NOV-2007 Dinesh C. 115.94 6450573 Change for SOY 08-09.
42 04-MAR-2008 Rajesh L. 115.96 6741064 Modifed cursors csr_mode12,csr_mode34
43 as orderby fullname
44 26-MAR-2008 Rajesh L. 115.97 6864422 Reverted the fix 6741064 as it was reported
45 by one cusotomer.
46 Modified p_m34_rec.full_name to substr
47 of peo.last_name length
48 09-APR-2008 Rajesh.L 115.98 6957644 Modifed cursors csr_mode12,csr_mode34
49 as orderby fullname
50 08-SEP-2008 emunisek 115.99 7373763 Modified procedure process_record to prevent
51 the false entries in the report of SOY changes
52 and to prevent date tracks created for unmodified
53 tax codes
54 3-Oct-2008 apmishra 115.101 7373763 Re arcs in the file so as to enable the dual maintainence
55 The earlier version of the branch file did not contain the fix,
56 hence rearcs in.
57 11-Nov_2008 dchindar 115.102 7532289 Changes has been done in cursor csr_mode12, so that process will
58 now update all eligible Tax Code For a employee having more than one-person
59 record with same NI number and other person details.
60 23-DEC-2008 dwkrishn 115.105 7649174 Modified the cursors/conditions for 3 cases
61 csr_fetch_asg_asgno Pass assignment number , Ni always Null
62 csr_fetch_asg_natid -- pass Ni ,Assignment always Null
63 csr_fetch_asg_other -- Pass Both
64 Both Null Errors
65 11-MAY-2009 jvaradra 115.106 8485686 Variable pqp_gb_ad_ee.g_global_paye_validation is
66 intialized to 'N' before calling hr_entry_api
67 and reset to 'Y' at the end to ensure row handler validations
68 are not fired when p6/p9/SOY process are submitted.
69 21-MAY-2009 rlingama 115.107 8497477 Added Employer's PAYE Reference on the output.
70 Earlier the report was sorted by person full name.
71 Now the report would sort by person name with in the individual tax references.
72 05-OCT-2009 dwkrishn 115.108 8785270 PAYE changes 2009. D0 can be cumulative from 6th APR 2010
73 21-OCT-2009 rlingama 115.109 8976778 Modified the logic to ensure, If TAX1 77 and TAX1 81 record
74 identifiers exist on an incoming P6/P9 file, we should apply
75 the value which will be either 0.00 or a positive value.
76 11-NOV-2009 rlingama 115.110 8510399 Added logic to ensure all the aggregated assignments are updated
77 or ignored.
78 8505085 We would stamp the authority code even P6/P9 file is received
79 with values which are same as before.
80 11-NOV-2009 rlingama 115.111 8510399 Incorporated the code review comments.
81 18-Dec-2009 rlingama 115.112 9215663 Modified the code to ensure that the P6/P9 process changes the tax bais form
82 Non Cumulative to Cumulative after 6th Apr 2010.
83 18-Jan-2009 rlingama 115.113 9253974 Extended the "Future change effective: DATE" validation to check against sepecial
84 authority code if "HR: GB Override SOY Authority" profile value is set to Override is allowed.
85 Modified the code to ensure, for Non aggregated assignments, SOY process updates PAYE details
86 even though other assignments of the person has future changes.
87 31-Mar-2011 pprvenka 115.114 11886669 Included the person_id in the order by clause for the cursor csr_mode12 for the bug :11886669
88 because for the EMP_APL Employees with same full name, records are reported in both
89 processed and error portion.
90 14-Sep-2011 pprvenka 115.115 12830138 Modified the maximum length of the global g_authority to 15.
91 01-Nov-2011 pprvenka 115.116 12649175 Updated the previous pay/tax info, if either of them is present.
92 01-Nov-2011 pprvenka 115.116 12649175 Updated the previous pay/tax info, if either of them is present.
93 22-Jan-2013 ssarap 115.118 16201197 Debug package for processing p6/p9 file considering the payroll id for RTI live employees.
94 ------------------------------------------------------------------------*/
95
96 -----------------------------------------------------
97 -- Constant variable --
98 -----------------------------------------------------
99 err_emp_not_found constant varchar2(255) := 'Emp Data in EDI file does not match application data, or employee is terminated.';
100 err_multiple_found constant varchar2(255) := 'Emp Data in EDI file matches multiple records on the database.';
101 err_data_mismatch constant varchar2(255) := '2 out of 3 values in EDI file required for matching are null.';
102 err_invalid_tax constant varchar2(255) := 'Invalid tax code : TAX_CODE.';
103 err_mode34_ex_emp constant varchar2(255) := 'No update for Ex-Employee, manual update may be required.';
104 err_mode2_ex_emp constant varchar2(255) := 'No bulk uplift for Ex-Employee, manual update may be required.';
105 err_tax_basis constant varchar2(255) := 'Invalid Tax Basis flag : TAX_BASIS.';
106 err_p6_pay_and_tax constant varchar2(255) := 'Previous tax is not zero, therefore previous pay cannot be zero.';
107 err_p45_p6_figures constant varchar2(255) := 'Discrepancy of over 1000 Pounds between P45/P6 figures.';
108 err_future_changes constant varchar2(255) := 'Future change effective : DATE.';
109 err_agg_asg constant varchar2(255) := 'No update due to failure of aggregated assignment/s.'; -- bug 8510399
110 err_multi_fchanges constant varchar2(255) := 'Multiple future changes detected, manual update may be required.';
111 err_no_paye_ele constant varchar2(255) := 'PAYE Details element entries does not exists on DATE.';
112 err_multi_asg constant varchar2(255) := 'Record not updated as assignment number is not supplied and multiple assignments exist, manual update may be required.';
113 err_future_asg constant varchar2(255) := 'Record not updated as future assignment exists under the same tax district, manual update may be required.';
114 warning_msg constant varchar2(255) := 'Tax details updated for assignment with future termination details present.'; /*Added soy 08-09*/
115 err_emp_not_found_payroll constant varchar2(255) := 'Record not updated as payroll id supplied does not exists in the application.';
116 update_mode constant varchar2(20) := 'UPDATE';
117 correct_mode constant varchar2(20) := 'CORRECTION';
118 reject_mode constant varchar2(20) := 'REJECT';
119 ignore_mode constant varchar2(20) := 'IGNORE';
120
121 -----------------------------------------------------
122 -- Record type for holding ASG Info --
123 -----------------------------------------------------
124 TYPE g_typ_paye_record IS RECORD(
125 element_entry_id number,
126 effective_start_date date,
127 effective_end_date date,
128 tax_code_id number,
129 tax_code_sv varchar2(25),
130 tax_basis_id number,
131 tax_basis_sv varchar2(50),
132 pay_previous_id number,
133 pay_previous_sv varchar2(70),
134 tax_previous_id number,
135 tax_previous_sv varchar2(70),
136 authority_id number,
137 authority_sv varchar2(80),
138 refundable_id number,
139 refundable_sv varchar2(50),
140 tax_code_prefix varchar2(20),
141 tax_code_value number,
142 tax_code_suffix varchar2(20),
143 tax_code_amended boolean,
144 tax_basis_amended boolean,
145 p45_val_amended boolean,
146 dt_update_mode varchar2(25),
147 creator_id number);
148
149 TYPE g_typ_per_record IS RECORD(
150 person_id number,
151 full_name varchar2(255),
152 ni_number varchar2(20),
153 aggregate_flag varchar2(2),
154 director_flag varchar2(2),
155 person_type varchar2(30),
156 term_date date,
157 lsp_date date,
158 effective_date date,
159 start_date date,
160 end_date date,
161 tax_ref varchar2(20)); -- Bug#8497477
162
163 TYPE g_tax_code_interface IS RECORD(
164 full_name varchar2(255),
165 national_identifier varchar2(30),
166 assignment_number varchar2(60),
167 payroll_name varchar2(90),
168 effective_date date,
169 date_of_message date,
170 issue_date date,
171 tax_code varchar2(20),
172 tax_basis varchar2(20),
173 previous_pay number,
174 previous_tax number,
175 authority varchar2(20),
176 paye_ref varchar2(30),
177 row_id rowid);
178
179 -- Start of bug#8510399
180 TYPE g_write_paye_record IS RECORD(
181 tax_ref varchar2(200),
182 status varchar2(1),
183 old_paye_rec g_typ_paye_record,
184 paye_rec g_typ_paye_record,
185 per_record g_typ_per_record,
186 assignment_number varchar2(255),
187 dir varchar2(255),
188 effective_date date,
189 assignment_id number,
190 m34_rec g_tax_code_interface,
191 err_msg varchar2(255));
192
193 TYPE g_tb_write_paye_rec IS TABLE OF
194 g_write_paye_record
195 INDEX BY BINARY_INTEGER;
196
197 tb_write_paye_rec g_tb_write_paye_rec;
198 -- end of bug#8510399
199
200 -----------------------------------------------------
201 -- GLOBAL Variables --
202 -----------------------------------------------------
203 g_business_group_id number;
204 g_payroll_id number;
205 g_element_type_id number;
206 g_mode number;
207 g_request_id number;
208 g_p6_request_id number;
209 g_effective_date date;
210 g_authority varchar2(20);
211 g_current_req_id number;
212 g_tax_ref varchar2(20);
213 g_payroll_name varchar2(50);
214 g_update_count number;
215 g_reject_count number;
216 g_E_line_count number;
217 g_P_line_count number;
218 g_T_line_count number;
219 g_uplift_suffix hr_entry.varchar2_table;
220 g_uplift_value hr_entry.number_table;
221 g_validate_only varchar2(1); /*Added soy 08-09*/
222 g_cpe_flag varchar2(1); /*Added soy 08-09*/
223 g_p_print_tax_ref varchar2(20) := '~'; -- Bug#8497477
224 g_e_print_tax_ref varchar2(20) := '~'; -- Bug#8497477
225 g_SOY_override_profile varchar2(255); -- Bug#9253974: variable to store profile value
226
227 ------------------------------------------------------------
228 -- Cursor to fetch element type id for PAYE Details --
229 ------------------------------------------------------------
230 cursor get_element_type_id is
231 select element_type_id
232 from pay_element_types_f
233 where element_name = 'PAYE Details';
234
235 ------------------------------------------------------------
236 -- Cursor to fetch assignment --
237 -- csr_mode12 = Cursor for select emp for mode 1 and 2 --
238 ------------------------------------------------------------
239 cursor csr_mode12 is
240 select /*+ ORDERED
241 INDEX(ppt, PER_PERSON_TYPES_PK) */
242 -- max(peo.person_id) p_id,
243 peo.person_id p_id,
244 peo.full_name,
245 peo.national_identifier,
246 nvl(peo.per_information10,'N') agg_flag,
247 nvl(peo.per_information2, 'N') dir_flag,
248 ppt.system_person_type,
249 pps.actual_termination_date,
250 pps.last_standard_process_date,
251 g_effective_date,
252 peo.effective_start_date,
253 peo.effective_end_date,
254 scl.segment1 -- Bug#8497477
255 from per_all_people_f peo,
256 per_all_assignments_f asg,
257 per_periods_of_service pps,
258 per_person_type_usages_f ptu,
259 per_person_types ppt,
260 pay_all_payrolls_f ppf, -- Bug#8497477
261 hr_soft_coding_keyflex scl -- Bug#8497477
262 where peo.person_id = asg.person_id
263 and peo.business_group_id = g_business_group_id
264 and (g_payroll_id is null or
265 asg.payroll_id = g_payroll_id)
266 and peo.person_id = ptu.person_id
267 and ptu.person_type_id = ppt.person_type_id
268 and pps.period_of_service_id = asg.period_of_service_id
269 and asg.payroll_id is not null
270 -- Start bug#8497477 : added pay_all_payrolls_f and hr_soft_coding_keyflex for tax reference check
271 and ppf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
272 and ppf.payroll_id = asg.payroll_id
273 and g_effective_date between ppf.effective_start_date and ppf.effective_end_date
274 -- End bug#8497477
275 and g_effective_date between peo.effective_start_date and peo.effective_end_date
276 and g_effective_date between asg.effective_start_date and asg.effective_end_date
277 and g_effective_date between ptu.effective_start_date and ptu.effective_end_date
278 and ( (pps.final_process_date is not null and
279 pps.final_process_date > g_effective_date)
280 or
281 (pps.final_process_date is null))
282 -- Bug#8497477: changed group by tax reference followed by existing group by.
283 group by scl.segment1,peo.person_id, peo.full_name, peo.national_identifier, peo.per_information10, peo.per_information2,
284 ppt.system_person_type, pps.actual_termination_date, pps.last_standard_process_date,
285 g_effective_date, peo.effective_start_date, peo.effective_end_date
286 -- Bug 6957644 modified p_id to upper(peo.full_name) to report order by name
287 --Bug 6741064 modified p_id to upper(peo.full_name) to report order by name
288 -- Bug#8497477: changed order by tax reference followed by existing oreder by.
289 order by scl.segment1,upper(peo.full_name),p_id, decode(system_person_type,'EMP',1,'EMP_APL',2,3);
290 -- Bug : 11886669. Included the Person ID in the order by clause.
291 -- Bug 6864422 reverted the fix 6741064 as it was reported by one customer
292 -- order by p_id, decode(system_person_type,'EMP',1,'EMP_APL',2,3);
293
294 ------------------------------------------------------------
295 -- csr_mode34 = Cursor for select data off the tax code --
296 -- interface table --
297 ------------------------------------------------------------
298 cursor csr_mode34 is
299 select tci.employee_name full_name,
300 tci.national_insurance_number national_identifier,
301 tci.works_number assignment_number,
302 tci.employer_reference payroll_name,
303 nvl(tci.effective_date,g_effective_date) effective_date,
304 tci.date_of_message date_of_message,
305 tci.issue_date issue_date,
306 ltrim(rtrim(tci.tax_code)) tax_code_sv,
307 ltrim(rtrim(tci.non_cumulative_flag)) tax_basis_sv,
308 to_char(nvl((tci.tot_pay_prev_emp/100),'')) pay_previous_sv,
309 to_char(nvl((tci.tot_tax_prev_emp/100),'')) tax_previous_sv,
310 upper(nvl(tci.form_type,'')) authority_sv,
311 ltrim(rtrim(to_char(tci.district_number,'000'))) || '/' || ltrim(rtrim(tci.employer_reference)) paye_ref,
312 tci.rowid row_id
313 from pay_gb_tax_code_interface tci
314 where tci.processed_flag is null
315 and ( tci.request_id is null
316 or tci.request_id = g_p6_request_id)
317 -- Bug 6957644 modified 6,2 to date_of_message,upper(full_name) to report order by name
318 --Bug 6741064 modified 6,2 to date_of_message,upper(full_name) to report order by name
319 -- Bug#8497477: changed order by tax reference followed by existing oreder by.
320 order by paye_ref,date_of_message,upper(full_name);
321 -- Bug 6864422 reverted the fix 6741064 as it was reported by one customer
322 -- order by 6,2;
323
324 ------------------------------------------------------------
325 -- Cursor to fetch assignment --
326 -- Cursor for select assignment based on the person_id --
327 -- aggr flag --
328 ------------------------------------------------------------
329 cursor csr_asg_details(p_person_id number,
330 p_aggr_flag varchar2,
331 p_tax_ref varchar2,
332 p_asg_no varchar2,
333 p_date date) is
334 select assignment_id,
335 pay.payroll_id,
336 assignment_number,
337 per_system_status asg_status,
338 sck.segment1 tax_ref
339 from per_all_assignments_f asg,
340 pay_all_payrolls_f pay,
341 per_assignment_status_types pat,
342 hr_soft_coding_keyflex sck
343 where asg.person_id = p_person_id
344 and ( -- no need to fetch payroll, but do check the tax ref
345 (p_aggr_flag = 'Y' and
346 ((g_payroll_id is not null and sck.segment1 = g_tax_ref)
347 or
348 (g_payroll_id is null and p_tax_ref is not null and sck.segment1 = p_tax_ref)
349 or
350 (g_payroll_id is null and p_tax_ref is null)))
351 or -- not aggregate then we have to check the payroll is matched.
352 (p_aggr_flag = 'N' and
353 ((g_payroll_id is null and p_asg_no is null and (p_tax_ref is null or p_tax_ref = sck.segment1) )
354 or
355 (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))
356 or
357 (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) )))
358 )
359 and asg.payroll_id = pay.payroll_id
360 and sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
361 and asg.assignment_status_type_id = pat.assignment_status_type_id
362 and p_date between asg.effective_start_date and asg.effective_end_date
363 and p_date between pay.effective_start_date and pay.effective_end_date
364 -- Bug#8497477: changed order by tax reference followed by existing oreder by.
365 order by sck.segment1,assignment_id, decode(per_system_status,'ACTIVE_ASSIGN',1,'SUSP_ASSIGN',2,'ACTIVE_APL',3,4);
366
367 ----------------------------------------------------------
368 -- This cursor returns those request_ids that are --
369 -- greater than or equal to the request_id entered --
370 -- by the user in the SRS - Resume definition and --
371 -- that are smaller than the current process request_id --
372 ----------------------------------------------------------
373 cursor get_req_cur (c_creator_id in number) IS
374 select 1
375 from dual
376 where exists (select fcr.request_id
377 from fnd_concurrent_requests fcr,
378 fnd_concurrent_programs fcp
379 where fcr.concurrent_program_id = fcp.concurrent_program_id
380 and fcr.request_id = c_creator_id
381 and fcr.request_id < g_current_req_id
382 and fcr.request_id >= g_request_id
383 and ( fcp.concurrent_program_name = 'PYUDET'
384 or fcp.concurrent_program_name = 'PYUDET_R'));
385
386 ---------------------------------------------------------
387 -- This cursor returns tax ref id based on the payroll --
388 ---------------------------------------------------------
389 cursor get_payroll_details IS
390 select sck.segment1, payroll_name
391 from hr_soft_coding_keyflex sck,
392 pay_all_payrolls_f pay
393 where sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
394 and pay.payroll_id = g_payroll_id
395 and g_effective_date between pay.effective_start_date and pay.effective_end_date;
396
397 -----------------------------------------------------------------------------------------------
398 -- This cursor is for Checking active and suspended assignments at the time of process run -- /*Added soy 08-09*/
399 -----------------------------------------------------------------------------------------------
400 CURSOR get_cpe_flag(p_person_id number, p_paye_ref varchar2, p_date date ) IS
401 select assignment_id
402 from per_all_assignments_f asg,
403 pay_all_payrolls_f pay,
404 per_assignment_status_types pat,
405 hr_soft_coding_keyflex sck
406 where asg.person_id = p_person_id
407 and sck.segment1 = p_paye_ref
408 and asg.payroll_id = pay.payroll_id
409 and sck.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
410 and asg.assignment_status_type_id = pat.assignment_status_type_id
411 and p_date between asg.effective_start_date and asg.effective_end_date
412 and p_date between pay.effective_start_date and pay.effective_end_date
413 AND per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') ;
414
415 ---------------------------------------------------------------------
416 -- NAME : set_global --
417 -- Type : Private Procedure --
418 -- DESCRIPTION : --
419 -- Set global variable --
420 ---------------------------------------------------------------------
421 PROCEDURE set_global(p_request_id in number default null,
422 p_mode in number,
423 p_effective_date in date,
424 p_business_group_id in number,
425 p_payroll_id in number,
426 p_authority in varchar2 default null,
427 p_p6_request_id in number default null,
428 p_validate_only in varchar2)
429 IS
430 BEGIN
431 hr_utility.trace('Setting GLOBAL variable');
432 open get_element_type_id;
433 fetch get_element_type_id into g_element_type_id;
434 close get_element_type_id;
435
436 g_business_group_id := p_business_group_id;
437 g_payroll_id := p_payroll_id;
438 g_mode := p_mode;
439 g_request_id := p_request_id;
440 g_p6_request_id := p_p6_request_id;
441 g_effective_date := p_effective_date;
442 g_authority := p_authority;
443 g_current_req_id := nvl(fnd_profile.value('CONC_REQUEST_ID'),-1);
444 g_update_count := 0;
445 g_reject_count := 0;
446 -- Insert the G_EFFECTIVE_DATE into FND_SESSION
447 hr_utility.fnd_insert(g_effective_date);
448
449 IF p_validate_only = 'GB_VALIDATE' then /*Added soy 08-09*/
450 g_validate_only := 'Y';
451 elsif p_validate_only = 'GB_VALIDATE_COMMIT' THEN
452 g_validate_only := 'N';
453 end if;
454
455 if g_payroll_id is not null then
456 open get_payroll_details;
457 fetch get_payroll_details into g_tax_ref, g_payroll_name;
458 close get_payroll_details;
459 end if;
460
461 -- Set up value if running Resume
462 if g_request_id is not null then
463 select decode(max(decode(type,'E',line_no,0)),
464 null, 0,
465 max(decode(type,'E',line_no,0))),
466 decode(max(decode(type,'P',line_no,0)),
467 null, 0,
468 max(decode(type,'P',line_no,0))),
469 decode(max(decode(type,'T',line_no,0)),
470 null, 0,
471 max(decode(type,'T',line_no,0)))
472 into g_E_line_count,
473 g_P_line_count,
474 g_T_line_count
475 from pay_gb_soy_outputs
476 where request_id = g_request_id;
477 end if;
478 hr_utility.trace('Running mode : ' || g_mode);
479 hr_utility.trace('Business group : ' || g_business_group_id);
480 hr_utility.trace('PAYE element id : ' || g_element_type_id);
481 hr_utility.trace('Payroll ID : ' || g_payroll_id);
482 hr_utility.trace('Resume ID : ' || g_request_id);
483 hr_utility.trace('P6 request ID : ' || g_p6_request_id);
484 hr_utility.trace('Effective date : ' || g_effective_date);
485 hr_utility.trace('Authority code : ' || g_authority);
486 hr_utility.trace('PAYE reference : ' || g_tax_ref);
487 EXCEPTION
488 when OTHERS then
489 raise;
490 END set_global;
491
492 ---------------------------------------------------------------------
493 -- NAME : asg_future_termination Added soy 08-09 --
494 -- Type : Private Function --
495 -- DESCRIPTION : --
496 -- This function will return a Y if assignment is future --
497 -- terminated or ended in case of non-aggregated assignment else --
498 -- return N. In case of non-aggregated, if all assignments having --
499 -- same PAYE ref are ended at a future point and assignment future --
500 -- terminated then return Y else return N --
501 ---------------------------------------------------------------------
502 FUNCTION asg_future_termination(p_effective_date in date,
503 p_assignment_id in number,
504 p_person_rec in g_typ_per_record,
505 p_tax_ref in varchar2) return varchar2
506 IS
507 l_future_term_date date :=NULL;
508 l_future_end_date date :=NULL;
509 l_return_flag varchar2(1) :='N';
510 l_future_term_flag varchar2(1) :='N';
511 l_future_end_flag varchar2(1) :='N';
512 l_cpe_end_date date;
513 -------------------------------------------------------------------
514 -- This cursor is for Checking assignment is teminated in future --
515 -------------------------------------------------------------------
516 CURSOR get_future_term_date(p_assignment_id number, p_effective_date date ) IS
517 select effective_start_date
518 from PER_ALL_ASSIGNMENTS_F paaf,
519 per_assignment_status_types past
520 where paaf.effective_start_date >= p_effective_date
521 and paaf.assignment_id = p_assignment_id
522 and paaf.assignment_status_type_id =past.assignment_status_type_id
523 and past.per_system_status IN ('TERM_ASSIGN')
524 and paaf.business_group_id = g_business_group_id;
525 -------------------------------------------------------------------
526 -- This cursor is for Checking assignment is ended in future --
527 -------------------------------------------------------------------
528 CURSOR get_future_end_asg_date(p_assignment_id number, p_effective_date date ) IS
529 select max(effective_end_date)
530 from PER_ALL_ASSIGNMENTS_F paaf
531 where paaf.effective_end_date >= p_effective_date
532 and paaf.assignment_id = p_assignment_id
533 and paaf.business_group_id = g_business_group_id;
534
535 BEGIN
536 hr_utility.trace(' Check assignment is Teminated or Ended in future ');
537 open get_future_term_date(p_assignment_id, p_effective_date);
538 fetch get_future_term_date into l_future_term_date;
539 if get_future_term_date%FOUND then
540 l_future_term_flag := 'Y';
541 end if;
542 close get_future_term_date;
543
544 open get_future_end_asg_date(p_assignment_id, p_effective_date);
545 fetch get_future_end_asg_date into l_future_end_date ;
546 if l_future_end_date <> TO_DATE('31/12/4712', 'DD/MM/RRRR') then
547 l_future_end_flag := 'Y';
548 end if;
549 close get_future_end_asg_date;
550
551 if p_person_rec.aggregate_flag ='N' then
552 --------------------------------------------------
553 -- Check if assignment is terminated in future --
554 --------------------------------------------------
555 if l_future_term_flag = 'Y' then
556 hr_utility.trace(' Assingment Terminated Date is : ' || l_future_term_date);
557 l_return_flag := 'Y';
558 end if;
559
560 -------------------------------------------
561 --Check if assignment is Ended in future --
562 -------------------------------------------
563 if l_future_end_flag = 'Y' then
564 hr_utility.trace(' Assingment End Date is : ' || l_future_end_date );
565 l_return_flag := 'Y';
566 end if;
567 elsif p_person_rec.aggregate_flag ='Y' then
568 ------------------------------------------------------------------------------------
569 -- Check if all the asg having same PAYE terminated or ended in future in future --
570 ------------------------------------------------------------------------------------
571 l_cpe_end_date := pay_gb_eoy_archive.get_agg_active_end(p_assignment_id, p_tax_ref , p_effective_date);
572 if l_cpe_end_date <> TO_DATE('31/12/4712', 'DD/MM/RRRR') and l_cpe_end_date >= p_effective_date and
573 (l_future_term_flag = 'Y' or l_future_end_flag = 'Y')then
574 l_return_flag := 'Y';
575 end if;
576 end if;
577 return l_return_flag;
578 EXCEPTION
579 when others then
580 raise;
581 END asg_future_termination;
582
583 ----------------------------------------------------------------------
584 -- NAME : set_cpe_flag Added soy 08-09 --
585 -- Type : Private Function --
586 -- DESCRIPTION : --
587 -- This function will return N if the terminated assignment passed --
588 -- to this fuction not in CPE with any of the active or suspended --
589 -- assignment having same PAYE refference --
590 ----------------------------------------------------------------------
591 FUNCTION set_cpe_flag(p_person_id IN NUMBER,
592 p_assignment_id IN NUMBER ,
593 p_paye_ref IN VARCHAR2,
594 p_effective_date IN DATE)
595
596 RETURN VARCHAR2 IS
597 BEGIN
598 hr_utility.trace(' Inside set_cpe_flag function for term asg: ' || p_assignment_id);
599 for l_record in get_cpe_flag(p_person_id,
600 p_paye_ref,
601 p_effective_date)
602 loop
603 hr_utility.trace(' Active or suspened asg record');
604 hr_utility.trace(' Assignment ID : ' || l_record.assignment_id);
605 hr_utility.trace(' PAYE Ref : ' || p_paye_ref);
606 if pay_gb_eoy_archive.get_agg_active_end(l_record.assignment_id, p_paye_ref , p_effective_date)=
607 pay_gb_eoy_archive.get_agg_active_end(p_assignment_id, p_paye_ref, p_effective_date)
608 AND pay_gb_eoy_archive.get_agg_active_start(l_record.assignment_id, p_paye_ref, p_effective_date)=
609 pay_gb_eoy_archive.get_agg_active_start(p_assignment_id, p_paye_ref, p_effective_date) then
610
611 hr_utility.trace(' Term Asg is in active CPE ');
612 return 'Y';
613 end if;
614 end loop;
615 hr_utility.trace(' Term Asg is not in active CPE ');
616 RETURN 'N';
617 EXCEPTION
618 when others then
619 raise;
620 END set_cpe_flag;
621 ---------------------------------------------------------------------
622 -- NAME : check_commit --
623 -- Type : Private Procedure --
624 -- DESCRIPTION : --
625 -- Function to return TRUE if we have processed a certain number --
626 -- of records. This is to allow the main process to commit at --
627 -- regular intervals, in order to cut-down on the rollback space --
628 -- required. --
629 ---------------------------------------------------------------------
630 FUNCTION check_commit return BOOLEAN
631 IS
632 l_commit_point number := 10;
633 BEGIN
634 if (mod(g_update_count, l_commit_point) = 0) then
635 return(TRUE);
636 end if;
637 return FALSE;
638 END check_commit;
639
640 ---------------------------------------------------------------------
641 -- NAME : conv_to_table --
642 -- Type : Private Procedure --
643 -- DESCRIPTION : --
644 -- Procedure converts the element entry values from the PAYE --
645 -- record into the PL/SQL table format required for the element --
646 -- entry API --
647 ---------------------------------------------------------------------
648 PROCEDURE conv_to_table(p_paye_rec in g_typ_paye_record,
649 p_num_entry_values in out nocopy number,
650 p_input_value_id_tbl in out nocopy hr_entry.number_table,
651 p_entry_value_tbl in out nocopy hr_entry.varchar2_table)
652 IS
653 l_index number := 0;
654 BEGIN
655 if p_paye_rec.tax_basis_amended then
656 l_index := l_index + 1;
657 p_input_value_id_tbl(l_index) := p_paye_rec.tax_basis_id;
658 p_entry_value_tbl(l_index) := p_paye_rec.tax_basis_sv;
659 end if;
660 if p_paye_rec.p45_val_amended then
661 l_index := l_index + 1;
662 p_input_value_id_tbl(l_index) := p_paye_rec.pay_previous_id;
663 p_entry_value_tbl(l_index) := p_paye_rec.pay_previous_sv;
664
665 l_index := l_index + 1;
666 p_input_value_id_tbl(l_index) := p_paye_rec.tax_previous_id;
667 p_entry_value_tbl(l_index) := p_paye_rec.tax_previous_sv;
668 end if;
669 if p_paye_rec.tax_code_amended then
670 l_index := l_index + 1;
671 p_input_value_id_tbl(l_index) := p_paye_rec.tax_code_id;
672 p_entry_value_tbl(l_index) := ltrim(p_paye_rec.tax_code_sv);
673 end if;
674 if l_index > 0 then
675 l_index := l_index + 1;
676 p_input_value_id_tbl(l_index) := p_paye_rec.authority_id;
677 p_entry_value_tbl(l_index) := p_paye_rec.authority_sv;
678 end if;
679 -- bug 8505085 : We would stamp the authority code even though there is no
680 -- changes in P6/P9 file supplied by HMRC
681 if l_index = 0 and trim(g_authority) in ('P6','P6B','P9') then
682 l_index := l_index + 1;
683 p_input_value_id_tbl(l_index) := p_paye_rec.authority_id;
684 p_entry_value_tbl(l_index) := p_paye_rec.authority_sv;
685 end if;
686 p_num_entry_values := l_index;
687 END conv_to_table;
688
689 ---------------------------------------------------------------------
690 -- NAME : conv_to_table_ni --
691 -- Type : Private Procedure --
692 -- DESCRIPTION : --
693 -- Procedure converts the element entry values from the NI --
694 -- record into the PL/SQL table format required for the element --
695 -- entry API --
696 ---------------------------------------------------------------------
697 PROCEDURE conv_to_table_ni(p_process_type_updated in varchar2 ,
698 p_input_value_id in number ,
699 p_num_entry_values in out nocopy number,
700 p_input_value_id_tbl in out nocopy hr_entry.number_table,
701 p_entry_value_tbl in out nocopy hr_entry.varchar2_table)
702 IS
703 l_index number := 0;
704 BEGIN
705 l_index := l_index + 1;
706 p_input_value_id_tbl(l_index) := p_input_value_id ;
707 p_entry_value_tbl(l_index) := p_process_type_updated;
708 p_num_entry_values := l_index;
709 END conv_to_table_ni;
710
711 ---------------------------------------------------------------------
712 -- NAME : file_output --
713 -- Type : Private Procedure --
714 -- DESCRIPTION : --
715 -- DESCRIPTION --
716 -- Procedure inserts a record into the PAY_GB_SOY_OUTPUT table, --
717 -- which will be used to created the output files required for --
718 -- the PYUDET process --
719 ---------------------------------------------------------------------
720 PROCEDURE file_output(p_type in varchar2,
721 p_line_no in out nocopy number,
722 p_text in varchar2,
723 p_request_id in number default null)
724 IS
725 l_request_id number := nvl(fnd_profile.value('CONC_REQUEST_ID'),-1);
726 BEGIN
727 if p_request_id is not null then
728 l_request_id := p_request_id;
729 end if;
730 --
731 p_line_no := nvl(p_line_no,0) + 1;
732 --
733 insert into pay_gb_soy_outputs(request_id, type, line_no, text)
734 values(l_request_id, p_type, p_line_no, p_text);
735 --
736 END file_output;
737
738 -- Start bug#8497477
739 ---------------------------------------------------------------------
740 -- NAME : write_Tax_Ref --
741 -- Type : Private Procedure --
742 -- DESCRIPTION : --
743 -- Write Tax Reference output --
744 ---------------------------------------------------------------------
745 PROCEDURE write_Tax_Ref (p_tax_ref varchar2,
746 p_msg_type varchar2)
747 IS
748
749 begin
750
751 if p_msg_type = 'P' then
752 if g_p_print_tax_ref <> p_tax_ref then
753 g_p_print_tax_ref := p_tax_ref;
754 file_output('P', g_P_line_count, ' ', g_request_id);
755 file_output('P', g_P_line_count, 'Employer''s PAYE Reference : '|| p_tax_ref, g_request_id);
756 file_output('P', g_P_line_count, ' ', g_request_id);
757 end if;
758 elsif p_msg_type = 'E' then
759 if g_e_print_tax_ref <> p_tax_ref then
760 g_e_print_tax_ref := p_tax_ref;
761 file_output('E', g_E_line_count, ' ', g_request_id);
762 file_output('E', g_E_line_count, 'Employer''s PAYE Reference : '|| p_tax_ref, g_request_id);
763 file_output('E', g_E_line_count, ' ', g_request_id);
764 end if;
765 end if;
766
767 END;
768
769 -- End bug#8497477
770
771 ---------------------------------------------------------------------
772 -- NAME : write_header --
773 -- Type : Private Procedure --
774 -- DESCRIPTION : --
775 -- Write header output --
776 ---------------------------------------------------------------------
777 PROCEDURE write_header
778 IS
779 cursor c_p6 is
780 select count(*)
781 from pay_gb_tax_code_interface
782 where request_id = g_p6_request_id;
783
784 l_process varchar2(110);
785 l_mode varchar2(100);
786 l_run_date varchar2(100);
787 l_p6 varchar2(100);
788 l_eff_date varchar2(100);
789 l_payroll varchar2(100);
790 l_p6_count number;
791 l_validate_mode varchar2(150);
792 BEGIN
793 /*Added soy 08-09*/
794 if g_validate_only = 'N' then
795 l_process := 'Amendments to ''PAYE Details'' - Records Processed';
796 elsif g_validate_only = 'Y' then
797 l_process := 'Amendments to ''PAYE Details'' - Potential Records To Be Processed When Run In Commit Mode';
798 end if;
799 if g_mode = 1 then
800 l_mode := 'Start Of Year';
801 if g_validate_only = 'N' then
802 l_process := 'Amendments to ''PAYE Details'' and ''NI'' - Records Processed';
803 elsif g_validate_only = 'Y' then
804 l_process := 'Amendments to ''PAYE Details'' and ''NI'' - Potential Records To Be Processed When Run In Commit Mode';
805 end if;
806 elsif g_mode = 2 then
807 l_mode := 'Mid Year Tax Code Change';
808 elsif g_mode = 3 then
809 l_mode := 'Tax Code Uplift From Tape';
810 elsif g_mode = 4 then
811 l_mode := 'P6/P6B/P9 Upload Process';
812 open c_p6;
813 fetch c_p6 into l_p6_count;
814 close c_p6;
815 l_p6 := 'Total number of records in EDI file : ' || l_p6_count;
816 end if;
817 --
818 if g_payroll_name is not null then
819 l_payroll := g_payroll_name;
820 else
821 l_payroll := 'All Payrolls';
822 end if;
823 -------------
824 /*Added soy 08-09*/
825 if g_validate_only = 'Y' then
826 l_validate_mode :=rpad('Validate Mode',21) || 'Validate Only - Updates Not Applied To The Database';
827 else
828 l_validate_mode :=rpad('Validate Mode',21) || 'Validate And Commit';
829 end if;
830 --------------
831 l_run_date := rpad('Run Date',21) || rpad(to_char(sysdate,'DD-MON-YYYY'),50) || 'KEY';
832 l_eff_date := rpad('Effective Date',21) || rpad(to_char(g_effective_date,'DD-MON-YYYY'),50) || 'n/c : No Change';
833 l_mode := rpad('Processing Mode',21) || rpad(l_mode,50) || 'n/a : Not Applicable';
834 l_payroll := rpad('Payroll',21) || rpad(l_payroll,50) || 'n/s : Not Supplied';
835 --
836 file_output('P', g_P_line_count, null, g_request_id);
837 file_output('P', g_P_line_count, l_run_date, g_request_id);
838 file_output('P', g_P_line_count, l_eff_date, g_request_id);
839 file_output('P', g_P_line_count, l_mode, g_request_id);
840 file_output('P', g_P_line_count, l_payroll, g_request_id);
841 file_output('P', g_P_line_count, l_validate_mode, g_request_id);
842 file_output('P', g_P_line_count, null, g_request_id);
843 if l_p6 is not null then
844 file_output('P', g_P_line_count, null, g_request_id);
845 file_output('P', g_P_line_count, l_p6, g_request_id);
846 file_output('P', g_P_line_count, null, g_request_id);
847 end if;
848 file_output('P', g_P_line_count, l_process, g_request_id);
849 file_output('P', g_P_line_count, null, g_request_id);
850 if g_validate_only = 'N' then
851 file_output('E', g_E_line_count, replace(l_process,'Processed','Not Processed'), g_request_id);
852 elsif g_validate_only = 'Y' then
853 file_output('E', g_E_line_count, replace(l_process,'Processed','Rejected'), g_request_id);
854 end if;
855 END write_header;
856
857 ---------------------------------------------------------------------
858 -- NAME : write_group_header --
859 -- Type : Private Procedure --
860 -- DESCRIPTION : --
861 -- Procedure to write a group header to the report files if it is --
862 -- required --
863 ---------------------------------------------------------------------
864 PROCEDURE write_group_header
865 IS
866 l_line1 varchar2(255);
867 l_line2 varchar2(255);
868 l_line3 varchar2(255);
869 l_line4 varchar2(255);
870 l_line5 varchar2(255); /*Added soy 08-09*/
871 BEGIN
872 ----------------------------------------
873 -- First write the header for process --
874 ----------------------------------------
875 if g_mode in (1,2) then
876 -- ---------1---------2---------3---------4---------5---------6---------7---------8---------9---------0---------1---------2---------3
877 -- 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
878 l_line1 :=' Authority Tax Previous PAYE NI NI ';
879 l_line2 :='Assignment Old New Code Basis Gross/Tax Update/ Director Update ';
880 l_line3 :='Number Name Code Code Old | New Amended Amended Correction Old New ';
881 l_line4 :='---------- -------------------------------------- ------ ------ --------- ------- --------- ---------- --- --- ------------';
882 elsif g_mode in (3,4) then
883 -- ---------1---------2---------3---------4---------5---------6---------7---------8---------9---------0---------1---------2---------3
884 -- 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
885 l_line1 :='Assignment Record Authority Tax Tax Previous Previous ';
886 l_line2 :='Number Name Type Code Code Basis Pay Tax ';
887 l_line5 :='>>Warning ';
888 l_line3 :='---------- -------------------------------------- ---------- --------- ------ -------------- ----------- -----------';
889 end if;
890 file_output('P', g_P_line_count, l_line1, g_request_id);
891 file_output('P', g_P_line_count, l_line2, g_request_id);
892 if g_mode in (1,2) then
893 file_output('P', g_P_line_count, l_line3, g_request_id);
894 file_output('P', g_P_line_count, l_line4, g_request_id);
895 elsif g_mode in (3,4) then
896 file_output('P', g_P_line_count, l_line5, g_request_id);
897 file_output('P', g_P_line_count, l_line3, g_request_id);
898 end if;
899 /* if l_line4 is not null then
900 file_output('P', g_P_line_count, l_line4, g_request_id);
901 end if;*/
902 --------------------------------------------
903 -- First write the header for not process --
904 --------------------------------------------
905 l_line1 := null;
906 l_line2 := null;
907 l_line3 := null;
908 l_line4 := null;
909 if g_mode in (1,2) then
910 -- ---------1---------2---------3---------4---------5---------6---------7---------8---------9---------0---------1---------2---------3
911 -- 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
912 l_line1 :='Assignment ';
913 l_line2 :='Number Name Reason ';
914 l_line3 :='---------- -------------------------------------- --------------------------------------------------------------------------------';
915 elsif g_mode in (3,4) then
916 -- ---------1---------2---------3---------4---------5---------6---------7---------8---------9---------0---------1---------2---------3
917 -- 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
918 l_line1 :='Assignment NI Tax Tax Previous Previous Effective ';
919 l_line2 :='Number Name Number Code Basis Pay Tax Date ';
920 l_line3 :='>>Reason for Rejection ';
921 l_line4 :='---------- -------------------------------------- ---------- ------ -------------- ----------- ----------- ----------------';
922 end if;
923 file_output('E', g_E_line_count, l_line1, g_request_id);
924 file_output('E', g_E_line_count, l_line2, g_request_id);
925 file_output('E', g_E_line_count, l_line3, g_request_id);
926 if l_line4 is not null then
927 file_output('E', g_E_line_count, l_line4, g_request_id);
928 end if;
929 END write_group_header;
930
931 ---------------------------------------------------------------------
932 -- NAME : write_body --
933 -- Type : Private Procedure --
934 -- DESCRIPTION : --
935 -- This procedure will write lines to the update section in report --
936 ---------------------------------------------------------------------
937 PROCEDURE write_body(p_old_paye_rec in g_typ_paye_record,
938 p_new_paye_rec in g_typ_paye_record,
939 p_person_rec in g_typ_per_record,
940 p_asg_number in varchar2,
941 p_dir in varchar2 default null,
942 p_effective_date in date default null,
943 p_assignment_id in number,
944 p_tax_ref in varchar2) /*Extra parameter Added soy 08-09*/
945 IS
946 l_line varchar2(255);
947 l_before varchar2(255);
948 l_after varchar2(255);
949 l_tax_basis varchar2(20);
950 l_p45_figures varchar2(20);
951 l_prev_pay varchar2(20);
952 l_prev_tax varchar2(20);
953 l_tax_code varchar2(20);
954 l_director varchar2(30);
955 l_authority varchar2(30);
956 l_mode varchar2(30);
957 l_warn_flag varchar2(1);
958 BEGIN
959 l_line := rpad(p_asg_number,10) || ' ' || rpad(p_person_rec.full_name,38) || ' ';
960 l_mode := initcap(lower(p_new_paye_rec.dt_update_mode));
961 ------------
962 -- Mode 1 --
963 ------------
964 if g_mode = 1 then
965 l_tax_basis := 'n/c';
966 l_p45_figures := 'n/c';
967 l_tax_code := rpad('n/c',7) || rpad('n/c',7);
968 if p_new_paye_rec.tax_basis_amended then
969 l_tax_basis := 'YES';
970 end if;
971 if p_new_paye_rec.p45_val_amended then
972 l_p45_figures := 'YES';
973 end if;
974 if p_new_paye_rec.tax_code_amended then
975 l_tax_code := rpad(p_old_paye_rec.tax_code_sv,6) || ' ' || rpad(p_new_paye_rec.tax_code_sv,6) || ' ';
976 end if;
977 l_authority:= rpad(nvl(p_old_paye_rec.authority_sv,' '),4) || '|' || lpad(nvl(p_new_paye_rec.authority_sv,' '),4);
978 l_director := p_dir;
979 if p_dir is null then
980 l_director := 'n/a n/a n/a';
981 end if;
982 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;
983 file_output('P', g_P_line_count,l_line, g_request_id);
984 end if;
985 -------------
986 -- Mode 2 --
987 -------------
988 if g_mode = 2 then
989 l_director := 'n/a n/a n/a';
990 l_tax_code := rpad(p_old_paye_rec.tax_code_sv,6) || ' ' || rpad(p_new_paye_rec.tax_code_sv,6) || ' ';
991 l_authority:= rpad(nvl(p_old_paye_rec.authority_sv,' '),4) || '|' || lpad(nvl(p_new_paye_rec.authority_sv,' '),4);
992 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;
993 file_output('P', g_P_line_count,l_line, g_request_id);
994 end if;
995 -----------------
996 -- Mode 3 or 4 --
997 -----------------
998 if g_mode in (3,4) then
999 l_tax_basis := rpad('n/c',15);
1000 l_tax_code := rpad('n/c',7);
1001 l_prev_pay := lpad('n/c',11);
1002 l_prev_tax := lpad('n/c',11);
1003 l_authority := rpad(nvl(p_old_paye_rec.authority_sv,' '),10);
1004 if p_new_paye_rec.tax_code_amended then
1005 l_tax_code := rpad(p_old_paye_rec.tax_code_sv,7);
1006 end if;
1007 if p_new_paye_rec.tax_basis_amended then
1008 l_tax_basis := rpad(p_old_paye_rec.tax_basis_sv,15);
1009 end if;
1010 if p_new_paye_rec.p45_val_amended then
1011 if to_number(nvl(p_old_paye_rec.pay_previous_sv,'0')) <> to_number(nvl(p_new_paye_rec.pay_previous_sv,'0')) then
1012 l_prev_pay := lpad(to_char(to_number(nvl(p_old_paye_rec.pay_previous_sv,'0')),9999990.99),11);
1013 end if;
1014 if to_number(nvl(p_old_paye_rec.tax_previous_sv,'0')) <> to_number(nvl(p_new_paye_rec.tax_previous_sv,'0')) then
1015 l_prev_tax := lpad(to_char(to_number(nvl(p_old_paye_rec.tax_previous_sv,'0')),9999990.99),11);
1016 end if;
1017 end if;
1018 l_before := l_line || rpad('Before',11) || l_authority || l_tax_code || l_tax_basis || l_prev_pay || ' ' || l_prev_tax;
1019
1020 l_tax_basis := rpad('n/c',15);
1021 l_tax_code := rpad('n/c',7);
1022 l_prev_pay := lpad('n/c',11);
1023 l_prev_tax := lpad('n/c',11);
1024 l_authority := rpad(nvl(p_new_paye_rec.authority_sv,' '),10);
1025 if p_new_paye_rec.tax_code_amended then
1026 l_tax_code := rpad(p_new_paye_rec.tax_code_sv,7);
1027 end if;
1028 if p_new_paye_rec.tax_basis_amended then
1029 l_tax_basis := rpad(p_new_paye_rec.tax_basis_sv,15);
1030 end if;
1031 if p_new_paye_rec.p45_val_amended then
1032 if to_number(nvl(p_old_paye_rec.pay_previous_sv,'0')) <> to_number(nvl(p_new_paye_rec.pay_previous_sv,'0')) then
1033 l_prev_pay := lpad(to_char(to_number(nvl(p_new_paye_rec.pay_previous_sv,'0')),9999990.99),11);
1034 end if;
1035 if to_number(nvl(p_old_paye_rec.tax_previous_sv,'0')) <> to_number(nvl(p_new_paye_rec.tax_previous_sv,'0')) then
1036 l_prev_tax := lpad(to_char(to_number(nvl(p_new_paye_rec.tax_previous_sv,'0')),9999990.99),11);
1037 end if;
1038 end if;
1039 l_after := rpad(' ',50) || rpad(l_mode,11) || l_authority || l_tax_code || l_tax_basis || l_prev_pay || ' ' || l_prev_tax;
1040 file_output('P', g_P_line_count,l_before, g_request_id);
1041 file_output('P', g_P_line_count,l_after, g_request_id);
1042 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*/
1043 if l_warn_flag = 'Y' then
1044 file_output('P', g_P_line_count,'>>' || warning_msg , g_request_id);
1045 end if;
1046 end if;
1047 END write_body;
1048
1049 ---------------------------------------------------------------------
1050 -- NAME : reject_record --
1051 -- Type : Private Procedure --
1052 -- DESCRIPTION : --
1053 -- This procedure will write message to the reject section --
1054 ---------------------------------------------------------------------
1055 PROCEDURE reject_record(p_per_rec g_typ_per_record default null,
1056 p_m34_rec g_tax_code_interface default null,
1057 p_msg varchar2,
1058 p_asg_number varchar2 default null,
1059 p_tax_ref varchar2 default null) -- Bug#8497477 : added tax ref parameter to report tax ref on the output.
1060 IS
1061 l_line varchar2(255);
1062 l_ni varchar2(50);
1063 l_tax_code varchar2(50);
1064 l_tax_basis varchar2(50);
1065 l_p45_figures varchar2(50);
1066 BEGIN
1067 if g_mode in (1,2) then
1068 l_line := rpad(p_asg_number,10) || ' ' || rpad(p_per_rec.full_name,38) || ' ' || p_msg;
1069 -- Start bug#8497477 : Report tax ref if current and previous references are not same.
1070 write_Tax_Ref(p_tax_ref,'E');
1071 -- End bug#8497477
1072 file_output('E', g_E_line_count,l_line, g_request_id);
1073 elsif (g_mode in (3,4)) then
1074 ----------------------------------------
1075 -- First update the taxcode interface --
1076 ----------------------------------------
1077 update pay_gb_tax_code_interface
1078 set processed_flag = 'R'
1079 where rowid = p_m34_rec.row_id;
1080 --
1081 if p_asg_number is not null then
1082 l_line := rpad(p_asg_number,10) || ' ' || rpad(p_per_rec.full_name,38);
1083 else
1084 l_line := rpad(nvl(p_m34_rec.assignment_number,'none'),10) || ' ' || rpad(nvl(p_m34_rec.full_name,' '),38);
1085 end if;
1086 l_ni := rpad(nvl(p_m34_rec.national_identifier,' '),11);
1087 l_tax_code := rpad(nvl(p_m34_rec.tax_code,' '),7);
1088 l_tax_basis := rpad(nvl(p_m34_rec.tax_basis,' '),15);
1089 l_p45_figures:= lpad(to_char(nvl(p_m34_rec.previous_pay,0),9999990.99),11) || ' ' ||
1090 lpad(to_char(nvl(p_m34_rec.previous_tax,0),9999990.99),11) || ' ';
1091 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');
1092 -- Start bug#8497477 : Report tax ref if current and previous references are not same.
1093 write_Tax_Ref(p_tax_ref,'E');
1094 -- End bug#8497477
1095 file_output('E', g_E_line_count,l_line, g_request_id);
1096 file_output('E', g_E_line_count,'>>' || p_msg , g_request_id);
1097 end if;
1098 g_reject_count := g_reject_count + 1;
1099 END reject_record;
1100
1101 ---------------------------------------------------------------------
1102 -- NAME : write_footer --
1103 -- Type : Private Procedure --
1104 -- DESCRIPTION : --
1105 -- Write footer output --
1106 ---------------------------------------------------------------------
1107 PROCEDURE write_footer
1108 IS
1109 l_p6_count number;
1110 cursor p6 is
1111 select count(*)
1112 from pay_gb_tax_code_interface
1113 where request_id = g_p6_request_id
1114 and processed_flag = 'P';
1115 BEGIN
1116 file_output('P', g_P_line_count, null, g_request_id);
1117 file_output('P', g_P_line_count, null, g_request_id);
1118
1119 if (g_p6_request_id is not null) then
1120 open p6;
1121 fetch p6 into l_p6_count;
1122 close p6;
1123 file_output('P', g_P_line_count,'Total EDI Records Processed = '|| to_char(l_p6_count), g_request_id);
1124 end if;
1125 file_output('P', g_P_line_count, 'Records Updated (inc Multi Assignment) = '|| to_char(g_update_count), g_request_id);
1126 --
1127 file_output('E', g_E_line_count, null, g_request_id);
1128 file_output('E', g_E_line_count, null, g_request_id);
1129 if (g_p6_request_id is not null) then
1130 file_output('E', g_E_line_count,'Total EDI Records Not Processed = '|| to_char(g_reject_count), g_request_id);
1131 else
1132 file_output('E', g_E_line_count,'Records Not Processed = '|| to_char(g_reject_count), g_request_id);
1133 end if;
1134 END write_footer;
1135
1136 ---------------------------------------------------------------------
1137 -- NAME : lookup_meaning --
1138 -- Type : Private Procedure --
1139 -- DESCRIPTION : --
1140 -- Function returns the MEANING column from HR_LOOKUPS for the --
1141 -- specified LOOKUP_TYPE and LOOKUP_CODE --
1142 ---------------------------------------------------------------------
1143 FUNCTION lookup_meaning(p_lookup_type in varchar2,
1144 p_lookup_code in varchar2) return VARCHAR2
1145 IS
1146 l_meaning hr_lookups.meaning%type := null;
1147 cursor c_lookup is
1148 select lku.meaning
1149 from hr_lookups lku
1150 where lku.lookup_type = p_lookup_type
1151 and lku.lookup_code = p_lookup_code;
1152 BEGIN
1153 open c_lookup;
1154 fetch c_lookup into l_meaning;
1155 close c_lookup;
1156 return l_meaning;
1157 END lookup_meaning;
1158
1159 ---------------------------------------------------------------------
1160 -- NAME : validate_tax_code --
1161 -- Type : Private Function --
1162 -- DESCRIPTION : --
1163 -- This Function uses the TAX_CODE validation fast formula and --
1164 -- returns an error message if an incorrect tax code is entered. --
1165 ---------------------------------------------------------------------
1166 FUNCTION validate_tax_code(p_tax_code in varchar2,
1167 p_effective_date in date,
1168 p_assignment_id in number) return VARCHAR2
1169 IS
1170 l_formula_id ff_formulas_f.formula_id%type;
1171 l_effective_start_date ff_formulas_f.effective_start_date%type;
1172 l_inputs ff_exec.inputs_t;
1173 l_outputs ff_exec.outputs_t;
1174 l_return_value varchar2(50):= null;
1175 l_formula_mesg varchar2(50):= null;
1176 l_status_value varchar2(2):= null;
1177 BEGIN
1178 -----------------------------
1179 -- Fetch formula details --
1180 -----------------------------
1181 select formula_id,
1182 effective_start_date
1183 into l_formula_id,
1184 l_effective_start_date
1185 from ff_formulas_f
1186 where formula_name='TAX_CODE'
1187 and business_group_id is null
1188 and legislation_code='GB'
1189 and p_effective_date between effective_start_date and effective_end_date;
1190 ----------------------------
1191 -- Initialize the formula --
1192 ----------------------------
1193 ff_exec.init_formula(l_formula_id,l_effective_start_date,l_inputs,l_outputs);
1194 -------------------------
1195 -- Setup formula input --
1196 -------------------------
1197 for l_in_cnt in l_inputs.first..l_inputs.last loop
1198 if l_inputs(l_in_cnt).name = 'ENTRY_VALUE' then
1199 l_inputs(l_in_cnt).value := ltrim(p_tax_code);
1200 end if;
1201 if l_inputs(l_in_cnt).name = 'DATE_EARNED' then
1202 l_inputs(l_in_cnt).value := to_char(p_effective_date,'DD-MON-YYYY');
1203 end if;
1204 if l_inputs(l_in_cnt).name = 'ASSIGNMENT_ID' then
1205 l_inputs(l_in_cnt).value := to_char(p_assignment_id);
1206 end if;
1207 end loop;
1208 -------------------------
1209 -- Execute the formula --
1210 -------------------------
1211 ff_exec.run_formula(l_inputs,l_outputs);
1212 --------------------
1213 -- Reading output --
1214 --------------------
1215 for l_out_cnt in l_outputs.first..l_outputs.last loop
1216 if l_outputs(l_out_cnt).name='FORMULA_MESSAGE' then
1217 l_formula_mesg := l_outputs(l_out_cnt).value;
1218 end if;
1219 if l_outputs(l_out_cnt).name='FORMULA_STATUS' then
1220 l_status_value := l_outputs(l_out_cnt).value;
1221 end if;
1222 end loop;
1223 if l_status_value = 'E' and l_formula_mesg is null then
1224 l_return_value := 'TAX_CODE Formula error';
1225 else
1226 l_return_value := l_formula_mesg;
1227 end if;
1228
1229 return l_return_value;
1230 EXCEPTION
1231 WHEN no_data_found then
1232 return l_return_value;
1233 END validate_tax_code;
1234
1235 ---------------------------------------------------------------------
1236 -- NAME : post_fetch --
1237 -- Type : Private Procedure --
1238 -- DESCRIPTION : --
1239 -- Parse the tax_code details and place the value back into the --
1240 -- record --
1241 ---------------------------------------------------------------------
1242 PROCEDURE post_fetch(p_paye_rec in out nocopy g_typ_paye_record)
1243 IS
1244 l_tax_code varchar2(50);
1245 l_step number;
1246 BEGIN
1247 l_step := 1;
1248 l_tax_code := ltrim(rtrim(p_paye_rec.tax_code_sv));
1249 if (l_tax_code <> 'NT' AND l_tax_code <> 'BR' AND l_tax_code <> 'FT' AND
1250 l_tax_code <> 'D0' AND l_tax_code <> 'NI' AND l_tax_code <> 'T0' AND
1251 l_tax_code <> 'SBR' AND l_tax_code <> 'SNT' AND l_tax_code <> 'SFT' AND
1252 l_tax_code <> 'SNI') then
1253 --
1254 p_paye_rec.tax_code_prefix := pysoytls.tax_prefix(p_paye_rec.tax_code_sv);
1255 p_paye_rec.tax_code_value := pysoytls.tax_value (p_paye_rec.tax_code_sv);
1256 p_paye_rec.tax_code_suffix := pysoytls.tax_suffix(p_paye_rec.tax_code_sv);
1257 l_step := 2;
1258 end if;
1259 --
1260 l_step := 3;
1261 p_paye_rec.tax_basis_sv := lookup_meaning('GB_TAX_BASIS', p_paye_rec.tax_basis_sv);
1262 p_paye_rec.refundable_sv := lookup_meaning('GB_REFUNDABLE', p_paye_rec.refundable_sv);
1263 p_paye_rec.authority_sv := lookup_meaning('GB_AUTHORITY', p_paye_rec.authority_sv);
1264 EXCEPTION
1265 WHEN others then
1266 hr_utility.trace('Error in post_fetch at step ' || l_step);
1267 hr_utility.trace(SQLERRM(SQLCODE));
1268 raise;
1269 END post_fetch;
1270
1271 ---------------------------------------------------------------------
1272 -- NAME : get_uplift_value --
1273 -- Type : Private Function --
1274 -- DESCRIPTION : --
1275 -- This function will return uplift value based on effective_date --
1276 ---------------------------------------------------------------------
1277 FUNCTION get_uplift_value (p_tax_suffix in varchar2) return NUMBER
1278 IS
1279 cursor c_uplift is
1280 select usr.row_low_range_or_name suffix,
1281 fnd_number.canonical_to_number(uci.value) value
1282 from pay_user_tables ust,
1283 pay_user_columns usc,
1284 pay_user_rows_f usr,
1285 pay_user_column_instances_f uci
1286 where ust.user_table_name = 'TAX_CODE_UPLIFT_VALUES'
1287 and ust.user_table_id = usc.user_table_id
1288 and ust.user_table_id = usr.user_table_id
1289 and usc.user_column_name = 'UPLIFT_VALUE'
1290 and usc.user_column_id = uci.user_column_id
1291 and usr.user_row_id = uci.user_row_id
1292 and uci.business_group_id = g_business_group_id
1293 and usr.business_group_id = g_business_group_id
1294 and g_effective_date between usr.effective_start_date and usr.effective_end_date
1295 and g_effective_date = uci.effective_start_date;
1296 l_index number := 0;
1297 BEGIN
1298 if (g_uplift_value(0) = -1) then
1299 ---------------------------------------------
1300 -- Uplift values have not yet been fetched --
1301 ---------------------------------------------
1302 for r_uplift in c_uplift loop
1303 if (r_uplift.value is not null) and (r_uplift.value <> 0) then
1304 l_index := l_index + 1;
1305 g_uplift_suffix(l_index) := r_uplift.suffix;
1306 g_uplift_value(l_index) := r_uplift.value;
1307 end if;
1308 end loop;
1309 g_uplift_value(0) := l_index;
1310 end if;
1311 --------------------------------------------------------
1312 -- Scan the PL/SQL tables to find the required Suffix --
1313 --------------------------------------------------------
1314 for l_index in 1..g_uplift_value(0) loop
1315 if (g_uplift_suffix(l_index) = p_tax_suffix) then
1316 return(g_uplift_value(l_index));
1317 end if;
1318 end loop;
1319 -------------------------------------------------------
1320 -- Suffix was not found in the table, so return NULL --
1321 -------------------------------------------------------
1322 return(null);
1323 END get_uplift_value;
1324
1325 ---------------------------------------------------------------------
1326 -- NAME : check_future_changes --
1327 -- Type : Private Function --
1328 -- DESCRIPTION : --
1329 -- This function will return a date if there is a date tracked --
1330 -- update on or after the given date for PAYE element entries --
1331 ---------------------------------------------------------------------
1332 FUNCTION check_future_changes (p_assignment_id in number,
1333 p_effective_date in date default hr_api.g_date,
1334 p_multi_change out nocopy boolean,
1335 p_auth_code out nocopy varchar2) return DATE -- Bug#9253974 -- added authority code parameter
1336 IS
1337 l_future_date date := null;
1338 BEGIN
1339 hr_utility.trace(' Check future change');
1340
1341 -- Start of bug#9253974
1342 /*select ele.effective_start_date
1343 into l_future_date
1344 from pay_element_entries_f ele
1345 where ele.effective_start_date >= p_effective_date
1346 and ele.assignment_id = p_assignment_id
1347 and ele.element_type_id = g_element_type_id
1348 order by ele.effective_start_date asc;*/
1349
1350 -- Modified the cursor to fetch authority code as well.
1351 select ele.effective_start_date,HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value)
1352 into l_future_date, p_auth_code
1353 from
1354 pay_element_entries_f ele,
1355 pay_element_entry_values_f eev,
1356 pay_input_values_f inv
1357 where inv.element_type_id = g_element_type_id
1358 and inv.name = 'Authority'
1359 and eev.input_value_id = inv.input_value_id
1360 and ele.element_type_id = inv.element_type_id
1361 and eev.element_entry_id = ele.element_entry_id
1362 and ele.assignment_id = p_assignment_id
1363 and ele.effective_start_date >= p_effective_date
1364 and eev.effective_start_date >= p_effective_date
1365 and p_effective_date between inv.effective_start_date and inv.effective_end_date
1366 order by ele.effective_start_date asc;
1367 -- End of bug#9253974
1368 hr_utility.trace(' Future change date : ' || l_future_date);
1369 p_multi_change := FALSE;
1370 return(l_future_date);
1371 EXCEPTION
1372 when too_many_rows then
1373 p_multi_change := TRUE;
1374 return null;
1375 when no_data_found then
1376 p_multi_change := FALSE;
1377 return null;
1378 when others then
1379 raise;
1380 END check_future_changes;
1381
1382 ---------------------------------------------------------------------
1383 -- NAME : process_director --
1384 -- Type : Private Procedure --
1385 -- DESCRIPTION : --
1386 -- This procedure will update director details on NI element --
1387 -- Director Pro Rate to Director --
1388 -- Director Pro Rate Normal to Director Normal --
1389 ---------------------------------------------------------------------
1390 FUNCTION process_directors(p_per_rec in g_typ_per_record,
1391 p_assignment_id in number) return VARCHAR2
1392 IS
1393 l_element_entry_id number;
1394 l_input_value_id number;
1395 l_num_entry_values number;
1396 l_process_type varchar2(3);
1397 l_process_type_new_code varchar2(3);
1398 l_process_type_new varchar2(20);
1399 l_process_type_updated boolean;
1400 l_input_value_id_tbl hr_entry.number_table;
1401 l_entry_value_tbl hr_entry.varchar2_table;
1402
1403 cursor csr_get_ni_process_type is
1404 select element_entry_id,
1405 process_type,
1406 input_value_id5
1407 from PAY_NI_ELEMENT_ENTRIES_V pneev
1408 where pneev.assignment_id = p_assignment_id
1409 and g_effective_date between pneev.effective_start_date and pneev.effective_end_date;
1410 BEGIN
1411 if p_per_rec.director_flag = 'Y' and
1412 p_per_rec.person_type in ('EMP', 'EMP_APL') then
1413
1414 open csr_get_ni_process_type;
1415 fetch csr_get_ni_process_type into l_element_entry_id,
1416 l_process_type,
1417 l_input_value_id ;
1418 close csr_get_ni_process_type;
1419 l_process_type_updated := FALSE;
1420 if l_process_type = 'DP' then
1421 l_process_type_new := 'Director';
1422 l_process_type_new_code := 'DY';
1423 l_process_type_updated := TRUE;
1424 end if;
1425 if l_process_type = 'DR' then
1426 l_process_type_new := 'Director Normal';
1427 l_process_type_new_code := 'DN';
1428 l_process_type_updated := TRUE;
1429 end if;
1430
1431 if l_process_type_updated then
1432 conv_to_table_ni(l_process_type_new,
1433 l_input_value_id,
1434 l_num_entry_values,
1435 l_input_value_id_tbl,
1436 l_entry_value_tbl) ;
1437
1438 if g_validate_only = 'N' then /*Added soy 08-09*/
1439 hr_utility.trace(' In Validate And Commit Mode therefore updating');
1440
1441 -- For bug 8485686
1442 pqp_gb_ad_ee.g_global_paye_validation := 'N';
1443
1444 hr_entry_api.update_element_entry (
1445 p_dt_update_mode => 'UPDATE',
1446 p_session_date => g_effective_date,
1447 p_element_entry_id => l_element_entry_id,
1448 p_num_entry_values => l_num_entry_values,
1449 p_input_value_id_tbl => l_input_value_id_tbl,
1450 p_entry_value_tbl => l_entry_value_tbl );
1451
1452 -- For bug 8485686
1453 pqp_gb_ad_ee.g_global_paye_validation := 'Y';
1454
1455 end if;
1456
1457 return (l_process_type || ' ' || l_process_type_new_code || ' Update');
1458 end if;
1459 end if;
1460
1461 return null;
1462 END process_directors;
1463
1464 ---------------------------------------------------------------------
1465 -- NAME : uplift_taxcode --
1466 -- Type : Private Procedure --
1467 -- DESCRIPTION : --
1468 -- This procedure will uplift tax code for the assignment --
1469 ---------------------------------------------------------------------
1470 PROCEDURE uplift_taxcode(p_asg_typ in varchar2,
1471 p_paye_rec in out nocopy g_typ_paye_record,
1472 p_aggregate_flag varchar2)
1473 IS
1474 l_uplift_value number;
1475 l_new_value number;
1476 BEGIN
1477 -----------------------------------------------------------------------------
1478 -- See if we need to update the tax code by checking the assignment status --
1479 -- If assignment is not active or suspend, then don't do anything --
1480 -- (In case of non aggregated assignments consider terminated assg also --
1481 -- this change is for SOY 08-09) --
1482 -----------------------------------------------------------------------------
1483 if p_aggregate_flag ='Y' then
1484 if (p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN','TERM_ASSIGN') and /*Added terminated for soy 08-09*/
1485 p_paye_rec.tax_code_suffix is not null) then
1486 l_uplift_value := get_uplift_value(p_paye_rec.tax_code_suffix);
1487 if nvl(l_uplift_value,0) <> 0 then
1488 l_new_value := p_paye_rec.tax_code_value + l_uplift_value;
1489 if l_new_value < 0 then
1490 p_paye_rec.tax_code_sv := '0T';
1491 else
1492 p_paye_rec.tax_code_sv := pysoytls.trim(p_paye_rec.tax_code_prefix ||
1493 fnd_number.number_to_canonical(l_new_value) ||
1494 p_paye_rec.tax_code_suffix);
1495 end if;
1496 p_paye_rec.tax_code_amended := TRUE;
1497 end if;
1498 end if;
1499 elsif p_aggregate_flag ='N' then
1500 if (p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN') and
1501 p_paye_rec.tax_code_suffix is not null) then
1502 l_uplift_value := get_uplift_value(p_paye_rec.tax_code_suffix);
1503 if nvl(l_uplift_value,0) <> 0 then
1504 l_new_value := p_paye_rec.tax_code_value + l_uplift_value;
1505 if l_new_value < 0 then
1506 p_paye_rec.tax_code_sv := '0T';
1507 else
1508 p_paye_rec.tax_code_sv := pysoytls.trim(p_paye_rec.tax_code_prefix ||
1509 fnd_number.number_to_canonical(l_new_value) ||
1510 p_paye_rec.tax_code_suffix);
1511 end if;
1512 p_paye_rec.tax_code_amended := TRUE;
1513 end if;
1514 end if;
1515 end if;
1516 END uplift_taxcode;
1517 ---------------------------------------------------------------------
1518 -- NAME : update_p45_taxbasis --
1519 -- Type : Private Procedure --
1520 -- DESCRIPTION : --
1521 -- This procedure will clear down P45 figures n update Tax Basis --
1522 -- from Non Cumulative to Cumulative --
1523 ---------------------------------------------------------------------
1524 PROCEDURE update_p45_taxbasis(p_asg_typ in varchar2,
1525 p_paye_rec in out nocopy g_typ_paye_record,
1526 p_aggregate_flag varchar2)
1527 IS
1528 BEGIN
1529 if p_aggregate_flag ='Y' then
1530 if p_paye_rec.tax_code_sv not in ('D0','SD0') OR g_effective_date >= fnd_date.canonical_to_date('2010/04/06 00:00:00') then --Bug 8785270: Paye 2009-2010 Changes
1531 if p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN','TERM_ASSIGN') and g_cpe_flag ='Y' then
1532 if p_paye_rec.tax_basis_sv = 'Non Cumulative' THEN /*Added terminated for soy 08-09*/
1533 p_paye_rec.tax_basis_sv := 'Cumulative';
1534 p_paye_rec.tax_basis_amended := TRUE;
1535 hr_utility.trace(' Tax Basis update');
1536 end if;
1537 end if;
1538 end if;
1539 elsif p_aggregate_flag ='N' then
1540 if p_paye_rec.tax_code_sv not in ('D0','SD0') OR g_effective_date >= fnd_date.canonical_to_date('2010/04/06 00:00:00') then --Bug 8785270: Paye 2009-2010 Changes
1541 if p_asg_typ in ('ACTIVE_ASSIGN','ACTIVE_APL','SUSP_ASSIGN') then
1542 if p_paye_rec.tax_basis_sv = 'Non Cumulative' then
1543 p_paye_rec.tax_basis_sv := 'Cumulative';
1544 p_paye_rec.tax_basis_amended := TRUE;
1545 hr_utility.trace(' Tax Basis update');
1546 end if;
1547 end if;
1548 end if;
1549 end if;
1550 --
1551 if (to_number(nvl(p_paye_rec.pay_previous_sv,0)) > 0 or
1552 to_number(nvl(p_paye_rec.tax_previous_sv,0)) > 0) then
1553 p_paye_rec.pay_previous_sv := '0';
1554 p_paye_rec.tax_previous_sv := '0';
1555 p_paye_rec.p45_val_amended := TRUE;
1556 hr_utility.trace(' P45 Update');
1557 end if;
1558 END update_p45_taxbasis;
1559
1560 ---------------------------------------------------------------------
1561 -- NAME : check_p45_figures --
1562 -- Type : Private Function --
1563 -- DESCRIPTION : --
1564 -- This function will check the incoming P45 figures from the EDI --
1565 -- and return any error message. This function is for mode 4 --
1566 ---------------------------------------------------------------------
1567 FUNCTION check_p45_figures(p_m34_rec in g_tax_code_interface,
1568 p_paye_rec in out nocopy g_typ_paye_record) return VARCHAR2
1569 IS
1570 ex_p45_figures exception;
1571 l_msg varchar2(255);
1572 l_p6_pay number;
1573 l_p6_tax number;
1574 l_p45_pay number;
1575 l_p45_tax number;
1576 BEGIN
1577 -- Start of bug 8976778
1578 -- If TAX1 77 and TAX1 81 record identifiers exist on an incoming P6/P9 file
1579 -- then only, we should apply the value which will be either 0.00 or a positive value.
1580 /*if p_m34_rec.previous_pay is not null
1581 and p_m34_rec.previous_tax is not null then */
1582 -- bug : 12649175. Updated the previous pay/tax info, if either of them is present.
1583 if p_m34_rec.previous_pay is not null
1584 or p_m34_rec.previous_tax is not null then
1585
1586
1587 -- End of bug 8976778
1588
1589 l_p45_pay := nvl(p_paye_rec.pay_previous_sv,0);
1590 l_p45_tax := nvl(p_paye_rec.tax_previous_sv,0);
1591
1592 l_p6_pay := nvl(p_m34_rec.previous_pay,l_p45_pay); -- bug : 12649175
1593 l_p6_tax := nvl(p_m34_rec.previous_tax,l_p45_tax);
1594
1595 ---------------------------------------
1596 -- If value are the same, do nothing --
1597 ---------------------------------------
1598 if (l_p6_pay = l_p45_pay) and (l_p6_tax = l_p45_tax) then
1599 return null;
1600 end if;
1601 --------------------------------------------------
1602 -- If incoming values are zero then, do nothing --
1603 --------------------------------------------------
1604 -- Start of bug 8976778
1605 -- Commented out the below code as we need to update P45 figures for zero values also
1606 /* if ( l_p6_pay = 0) and (l_p6_tax = 0) then
1607 return null;
1608 end if;*/
1609 -- End of bug 8976778
1610 --------------------------------------------------------
1611 -- If incoming prev pay = 0, but prev tax <> 0; error --
1612 --------------------------------------------------------
1613 if (l_p6_pay = 0) and (l_p6_tax <> 0) then
1614 l_msg := err_p6_pay_and_tax;
1615 raise ex_p45_figures;
1616 end if;
1617 --------------------------------------------------------------------------
1618 -- If the current P45 figures are zero but P6 are not; update the value --
1619 --------------------------------------------------------------------------
1620 if ((l_p45_pay = 0) and (l_p45_tax = 0)) and
1621 ((l_p6_pay <> 0) or (l_p6_tax <> 0)) then
1622 p_paye_rec.tax_previous_sv := l_p6_tax;
1623 p_paye_rec.pay_previous_sv := l_p6_pay;
1624 p_paye_rec.p45_val_amended := TRUE;
1625 return null;
1626 end if;
1627 -------------------------------------------------------------
1628 -- If P45 and P6 are not zero, check the diff between them --
1629 -------------------------------------------------------------
1630 if abs(l_p45_pay - l_p6_pay) >= 1000 or
1631 abs(l_p45_tax - l_p6_tax) >= 1000 then
1632 l_msg := err_p45_p6_figures;
1633 raise ex_p45_figures;
1634 end if;
1635 ---------------------------------------------
1636 -- Getting this far, then assumes no error --
1637 ---------------------------------------------
1638 p_paye_rec.tax_previous_sv := l_p6_tax;
1639 p_paye_rec.pay_previous_sv := l_p6_pay;
1640 p_paye_rec.p45_val_amended := TRUE;
1641 return null;
1642 -- Start of bug 8976778
1643 else
1644 return null;
1645 end if;
1646 -- End of bug 8976778
1647 EXCEPTION
1648 WHEN ex_p45_figures THEN
1649 return l_msg;
1650 END check_p45_figures;
1651
1652
1653
1654 ---------------------------------------------------------------------
1655 -- NAME : conv_to_paye_rec --
1656 -- Type : Private Function --
1657 -- DESCRIPTION : --
1658 -- This function will setup the paye_rec data record and will --
1659 -- return any error message. This function is for mode 3 and 4 --
1660 ---------------------------------------------------------------------
1661 FUNCTION conv_to_paye_rec(p_asg_id in number,
1662 p_m34_rec in g_tax_code_interface,
1663 p_paye_rec in out nocopy g_typ_paye_record) return VARCHAR2
1664 IS
1665 ex_error exception;
1666 l_msg varchar2(255);
1667 BEGIN
1668 hr_utility.trace(' Conv to PAYE Rec (mode 3 | 4)');
1669 hr_utility.trace(' Incoming tax basis: ' || p_m34_rec.tax_basis );
1670 hr_utility.trace(' Incoming tax code : ' || p_m34_rec.tax_code);
1671 hr_utility.trace(' Incoming P45 (P|T): ' || nvl(p_m34_rec.previous_pay,0) || ' | ' || nvl(p_m34_rec.previous_tax,0));
1672 hr_utility.trace(' Current tax basis : ' || p_paye_rec.tax_basis_sv);
1673 hr_utility.trace(' Current tax code : ' || p_paye_rec.tax_code_sv);
1674 hr_utility.trace(' Current P45 (P|T) : ' || nvl(p_paye_rec.pay_previous_sv,0) || ' | ' || nvl(p_paye_rec.tax_previous_sv,0));
1675 -------------------------------------------
1676 -- First we check the incoming tax basis --
1677 -------------------------------------------
1678 if p_m34_rec.tax_basis is null then
1679 if p_paye_rec.tax_basis_sv <> 'Cumulative' then
1680 hr_utility.trace(' Updating tax basis');
1681 p_paye_rec.tax_basis_sv := 'Cumulative';
1682 p_paye_rec.tax_basis_amended := TRUE;
1683 end if;
1684 elsif p_m34_rec.tax_basis = 'Y' then
1685 if p_paye_rec.tax_basis_sv <> 'Non Cumulative' then
1686 hr_utility.trace(' Updating tax basis');
1687 p_paye_rec.tax_basis_sv := 'Non Cumulative';
1688 p_paye_rec.tax_basis_amended := TRUE;
1689 end if;
1690 else
1691 l_msg := replace(err_tax_basis,'TAX_BASIS',p_m34_rec.tax_basis);
1692 raise ex_error;
1693 end if;
1694 -------------------------------------------
1695 -- Now checking the incoming tax code --
1696 -------------------------------------------
1697 if p_m34_rec.tax_code <> p_paye_rec.tax_code_sv then
1698 l_msg := validate_tax_code(p_m34_rec.tax_code, p_m34_rec.effective_date, p_asg_id);
1699 if l_msg is not null then
1700 raise ex_error;
1701 end if;
1702 p_paye_rec.tax_code_sv := p_m34_rec.tax_code;
1703 p_paye_rec.tax_code_amended := TRUE;
1704 hr_utility.trace(' Updating tax code');
1705 end if;
1706 --------------------------------------------
1707 -- If the incoming tax code is D0, --
1708 -- change the tax basis to Non Cumulative --
1709 -- if effective date is less than 6-Apr-10--
1710 --------------------------------------------
1711 --Bug 8785270: Paye 2009-2010 Changes Start
1712 if p_paye_rec.tax_basis_sv <> 'Non Cumulative' AND
1713 -- Bug:9215663 - Modified the code to ensure that the P6/P9 process changes the tax bais form
1714 -- Non Cumulative to Cumulative after 6th Apr 2010.
1715 --(p_paye_rec.tax_code_sv in ('D0','SD0') OR g_effective_date >= fnd_date.canonical_to_date('2010/04/06 00:00:00')) then
1716 (p_paye_rec.tax_code_sv in ('D0','SD0') AND p_m34_rec.effective_date < fnd_date.canonical_to_date('2010/04/06 00:00:00')) then
1717 p_paye_rec.tax_basis_sv := 'Non Cumulative';
1718 p_paye_rec.tax_basis_amended := TRUE;
1719 end if;
1720 --Bug 8785270: Paye 2009-2010 Changes End
1721 -------------------------------------------
1722 -- Now we check the incoming P45 Figures --
1723 -------------------------------------------
1724 if g_mode = 4 then
1725 l_msg := check_p45_figures(p_m34_rec,p_paye_rec);
1726 if l_msg is not null then
1727 raise ex_error;
1728 end if;
1729 end if;
1730 return null;
1731 EXCEPTION
1732 WHEN ex_error THEN
1733 return l_msg;
1734 END conv_to_paye_rec;
1735
1736 ---------------------------------------------------------------------
1737 -- NAME : set_new_paye_record --
1738 -- Type : Private Function --
1739 -- DESCRIPTION : --
1740 -- This function will update the PAYE and return any error msg --
1741 ---------------------------------------------------------------------
1742 FUNCTION set_new_paye_record(p_asg_typ in varchar2,
1743 p_asg_id in number,
1744 p_paye_rec in out nocopy g_typ_paye_record,
1745 p_m34_rec in g_tax_code_interface default null,
1746 p_aggregate_flag in varchar2 default 'N') return VARCHAR2
1747 IS
1748 ex_error exception;
1749 l_msg varchar2(255);
1750 l_multi_change boolean;
1751 l_future date;
1752 l_check_date date;
1753 l_auth_code varchar2(255); -- Bug#9253974: variable to store auth code
1754 BEGIN
1755 hr_utility.trace(' Set New PAYE Record');
1756 l_check_date := g_effective_date;
1757 l_multi_change := FALSE;
1758 ----------------
1759 -- For mode 1 --
1760 ----------------
1761 if g_mode = 1 then
1762 hr_utility.trace(' Updating P45 figures');
1763 update_p45_taxbasis(p_asg_typ,p_paye_rec, p_aggregate_flag);
1764 ----------------------
1765 -- For mode 3 and 4 --
1766 ----------------------
1767 elsif g_mode in (3,4) and g_cpe_flag ='Y' then /*CPE condition Added soy 08-09*/
1768 hr_utility.trace(' Calling conv_to_paye_rec');
1769 l_msg := conv_to_paye_rec(p_asg_id,p_m34_rec,p_paye_rec);
1770 if l_msg is not null then
1771 raise ex_error;
1772 end if;
1773 l_check_date := p_m34_rec.effective_date;
1774 end if;
1775 hr_utility.trace(' Check Date : ' || l_check_date);
1776 ----------------------------------
1777 -- Check for any future changes --
1778 ----------------------------------
1779 -- Bug#9253974: added auth code parameter
1780 l_future := check_future_changes(p_asg_id, l_check_date, l_multi_change,l_auth_code);
1781 if l_multi_change then
1782 hr_utility.trace(' Multiple future changes found');
1783 l_msg := err_multi_fchanges;
1784 raise ex_error;
1785 end if;
1786
1787 if l_future is not null then
1788 hr_utility.trace(' Future change found');
1789 if g_mode = 1 then
1790 -------------------------------------------------------
1791 -- For mode 1, if exists change on the same date and --
1792 -- no change to P45, reject the record --
1793 -------------------------------------------------------
1794 -- Bug#9253974: Don't throw future validation
1795 -- if future date track change on soy process run date
1796 -- and profile value is set to "Override is allowed"
1797 -- and authority code is "Override SOY"'.
1798 if (l_future = g_effective_date)
1799 and (l_auth_code = 'Override SOY')
1800 and (g_SOY_override_profile = 'OVERRIDE_YES') then
1801 NULL;
1802 else
1803 if l_future = l_check_date and
1804 not p_paye_rec.p45_val_amended then
1805 l_msg := replace(err_future_changes,'DATE',to_char(l_future,'DD-MON-YYYY'));
1806 raise ex_error;
1807 elsif l_future > l_check_date then
1808 l_msg := replace(err_future_changes,'DATE',to_char(l_future,'DD-MON-YYYY'));
1809 raise ex_error;
1810 end if;
1811 end if;
1812 -------------------------------------------------
1813 -- There is a change on same day, but have to --
1814 -- clear down P45/TaxBasis, so Correction mode --
1815 -------------------------------------------------
1816 p_paye_rec.dt_update_mode := correct_mode;
1817 elsif g_mode = 2 then
1818 ------------------------------------------------
1819 -- For mode 1 and 2, if exists future changes --
1820 -- reject the record. --
1821 ------------------------------------------------
1822 if l_future >= l_check_date then
1823 l_msg := replace(err_future_changes,'DATE',to_char(l_future,'DD-MON-YYYY'));
1824 raise ex_error;
1825 end if;
1826 elsif g_mode in (3,4) then
1827 ------------------------------------------------
1828 -- For mode 3 and 4, if exists future changes --
1829 -- but the change is on the same day, proceed --
1830 -- otherwise reject --
1831 ------------------------------------------------
1832 if l_future = l_check_date then
1833 p_paye_rec.dt_update_mode := correct_mode;
1834 else
1835 l_msg := replace(err_future_changes,'DATE',to_char(l_future,'DD-MON-YYYY'));
1836 raise ex_error;
1837 end if;
1838 end if;
1839 else
1840 ----------------------------
1841 -- Future change is nulll --
1842 ----------------------------
1843 p_paye_rec.dt_update_mode := update_mode;
1844 end if;
1845
1846 ----------------------
1847 -- For mode 1 and 2 -- /*CPE condition Added soy 08-09*/
1848 ----------------------
1849 -- Bug#9253974: Uplift tax code
1850 -- if future date track change on soy process run date
1851 -- and authority code is set to "Override is allowed"
1852 -- and authority code is "Override SOY"'.
1853 if ( g_mode in (1,2) and (p_paye_rec.dt_update_mode = update_mode and g_cpe_flag ='Y'))
1854 or
1855 ( g_mode = 1
1856 and (l_future = g_effective_date)
1857 and (l_auth_code = 'Override SOY')
1858 and (g_SOY_override_profile = 'OVERRIDE_YES') )then
1859 uplift_taxcode(p_asg_typ, p_paye_rec,p_aggregate_flag);
1860 end if;
1861 -----------------------
1862 -- Set the authority --
1863 -----------------------
1864 hr_utility.trace(' Setting authority');
1865 p_paye_rec.authority_sv := g_authority;
1866 hr_utility.trace(' New PAYE Details element:');
1867 hr_utility.trace(' Tax Code : ' || p_paye_rec.tax_code_sv );
1868 hr_utility.trace(' Tax Basis : ' || p_paye_rec.tax_basis_sv );
1869 hr_utility.trace(' Previous Pay : ' || p_paye_rec.pay_previous_sv );
1870 hr_utility.trace(' Previous Tax : ' || p_paye_rec.tax_previous_sv );
1871 hr_utility.trace(' Authority : ' || p_paye_rec.authority_sv );
1872 return null;
1873 EXCEPTION
1874 WHEN ex_error THEN
1875 return l_msg;
1876 END set_new_paye_record;
1877
1878 ---------------------------------------------------------------------
1879 -- NAME : check_leaver --
1880 -- Type : Private Function --
1881 -- DESCRIPTION : --
1882 -- This function will check for leaver and return the appropriate --
1883 -- message --
1884 ---------------------------------------------------------------------
1885 FUNCTION check_leaver(p_per_rec in g_typ_per_record) return VARCHAR2
1886 IS
1887 ex_exemp exception;
1888 l_msg varchar2(255);
1889 BEGIN
1890 hr_utility.trace(' Check for Leaver');
1891 if p_per_rec.person_type in ('EX_EMP','EX_APL') then
1892 --
1893 hr_utility.trace(' LEAVER = TRUE');
1894 if g_mode in (3,4) then
1895 l_msg := err_mode34_ex_emp;
1896 elsif g_mode = 2 then
1897 l_msg := err_mode2_ex_emp;
1898 end if;
1899 --
1900 raise ex_exemp;
1901 end if;
1902 hr_utility.trace(' Not a leaver');
1903 return null;
1904 EXCEPTION
1905 when ex_exemp then
1906 return l_msg;
1907 END check_leaver;
1908
1909 ---------------------------------------------------------------------
1910 -- NAME : get_paye_record --
1911 -- Type : Private Function --
1912 -- DESCRIPTION : --
1913 -- This function will fetch PAYE element entry --
1914 -- Will return message if error found --
1915 ---------------------------------------------------------------------
1916 FUNCTION get_paye_record(p_assignment_id in number,
1917 p_date in date,
1918 p_paye_rec out nocopy g_typ_paye_record) return VARCHAR2
1919 IS
1920 ex_tax_code exception;
1921 l_msg varchar2(255);
1922 l_paye_rec g_typ_paye_record;
1923
1924 cursor csr_paye_rec is
1925 select ee.element_entry_id ,
1926 ee.effective_start_date ,
1927 ee.effective_end_date ,
1928 min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) tax_code_id ,
1929 min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) tax_code_sv ,
1930 min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) tax_basis_id ,
1931 min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) tax_basis_sv ,
1932 min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) pay_previous_id ,
1933 min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) pay_previous_sv ,
1934 min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) tax_previous_id ,
1935 min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) tax_previous_sv ,
1936 min(decode(inv.name, 'Authority', eev.input_value_id, null)) authority_id ,
1937 min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) authority_sv ,
1938 min(decode(inv.name, 'Refundable', eev.input_value_id, null)) refundable_id ,
1939 min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) refundable_sv,
1940 ee.creator_id
1941 from pay_element_entries_f ee,
1942 pay_element_entry_values_f eev,
1943 pay_input_values_f inv
1944 where ee.assignment_id = p_assignment_id
1945 and ee.element_type_id = g_element_type_id
1946 and ee.element_entry_id = eev.element_entry_id
1947 and eev.input_value_id = inv.input_value_id
1948 and p_date between ee.effective_start_date and ee.effective_end_date
1949 and p_date between eev.effective_start_date and eev.effective_end_date
1950 and p_date between inv.effective_start_date and inv.effective_end_date
1951 group by ee.element_entry_id, ee.effective_start_date, ee.effective_end_date,
1952 ee.creator_id;
1953 BEGIN
1954 open csr_paye_rec;
1955 fetch csr_paye_rec into l_paye_rec.element_entry_id,
1956 l_paye_rec.effective_start_date,
1957 l_paye_rec.effective_end_date,
1958 l_paye_rec.tax_code_id,
1959 l_paye_rec.tax_code_sv,
1960 l_paye_rec.tax_basis_id,
1961 l_paye_rec.tax_basis_sv,
1962 l_paye_rec.pay_previous_id,
1963 l_paye_rec.pay_previous_sv,
1964 l_paye_rec.tax_previous_id,
1965 l_paye_rec.tax_previous_sv,
1966 l_paye_rec.authority_id,
1967 l_paye_rec.authority_sv,
1968 l_paye_rec.refundable_id,
1969 l_paye_rec.refundable_sv,
1970 l_paye_rec.creator_id;
1971 close csr_paye_rec;
1972 l_paye_rec.tax_code_amended := FALSE;
1973 l_paye_rec.tax_basis_amended := FALSE;
1974 l_paye_rec.p45_val_amended := FALSE;
1975 post_fetch(l_paye_rec);
1976 if g_mode in (1,2) then
1977 if l_paye_rec.tax_code_value = 999999 then
1978 l_msg := replace(err_invalid_tax,'TAX_CODE',l_paye_rec.tax_code_sv);
1979 raise ex_tax_code;
1980 end if;
1981 end if;
1982 p_paye_rec := l_paye_rec;
1983 hr_utility.trace(' Element Entry ID : ' || p_paye_rec.element_entry_id );
1984 hr_utility.trace(' Start Date : ' || p_paye_rec.effective_start_date );
1985 hr_utility.trace(' End Date : ' || p_paye_rec.effective_end_date );
1986 hr_utility.trace(' Tax Code : ' || p_paye_rec.tax_code_sv );
1987 hr_utility.trace(' Tax Basis : ' || p_paye_rec.tax_basis_sv );
1988 hr_utility.trace(' Previous Pay : ' || p_paye_rec.pay_previous_sv );
1989 hr_utility.trace(' Previous Tax : ' || p_paye_rec.tax_previous_sv );
1990 hr_utility.trace(' Authority : ' || p_paye_rec.authority_sv );
1991 return null;
1992 EXCEPTION
1993 WHEN ex_tax_code THEN
1994 return l_msg;
1995 END get_paye_record;
1996
1997 ---------------------------------------------------------------------
1998 -- NAME : find_employee --
1999 -- Type : Private Function --
2000 -- DESCRIPTION : --
2001 -- This function will find employee based on incoming data on the --
2002 -- EDI. Function will return 0 if not found, 1 if found and 2 if --
2003 -- more than 1 match is found --
2004 ---------------------------------------------------------------------
2005 FUNCTION find_employee(p_m34_rec IN OUT nocopy g_tax_code_interface,
2006 p_per_rec OUT nocopy g_typ_per_record) return VARCHAR2
2007 IS
2008 ex_error exception;
2009 l_person_rec g_typ_per_record;
2010 l_last_name varchar2(255);
2011 l_counter number;
2012 l_null_field number;
2013 l_count number;
2014 l_msg varchar2(255);
2015 --For Bug 7649174 Start
2016 /*cursor csr_fetch_asg is
2017 select max(peo.person_id) p_id,
2018 peo.last_name,
2019 peo.full_name,
2020 peo.national_identifier,
2021 nvl(peo.per_information10,'N') agg_flag,
2022 'EMP', --ppt.system_person_type,
2023 pps.actual_termination_date,
2024 pps.last_standard_process_date,
2025 p_m34_rec.effective_date
2026 from per_all_people_f peo,
2027 per_all_assignments_f asg,
2028 per_periods_of_service pps
2029 where asg.business_group_id = g_business_group_id
2030 and peo.business_group_id = g_business_group_id
2031 and peo.person_id = asg.person_id
2032 -- Bug 6864422 modified p_m34_rec.full_name to substr of peo.last_name length
2033 --and upper(peo.last_name) like upper(substr(rpad(p_m34_rec.full_name,5,'%'), 1, 5))||'%'
2034 and upper(peo.last_name) like upper(substr(rpad(substr(p_m34_rec.full_name,1,length(peo.last_name)),5,'%'), 1, 5))||'%'
2035 --and upper(substr(rpad(peo.last_name,5,' '),1,5)) = upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5))
2036 and (p_m34_rec.assignment_number is null or
2037 asg.assignment_number = p_m34_rec.assignment_number)
2038 and (p_m34_rec.national_identifier is null or
2039 peo.national_identifier like substr(p_m34_rec.national_identifier,1,8)||'%')
2040 and pps.period_of_service_id = asg.period_of_service_id
2041 and p_m34_rec.effective_date between asg.effective_start_date and asg.effective_end_date
2042 and p_m34_rec.effective_date between peo.effective_start_date and peo.effective_end_date
2043 and (pps.actual_termination_date is null or
2044 p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
2045 and exists (select 1
2046 from per_person_type_usages_f ptu,
2047 per_person_types ppt
2048 where ptu.person_id = peo.person_id
2049 and ptu.person_type_id = ppt.person_type_id
2050 and ppt.system_person_type in ('EMP','EMP_APL')
2051 and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
2052 group by peo.last_name, peo.full_name, peo.national_identifier, peo.per_information10,
2053 'EMP'
2054 --ppt.system_person_type
2055 , pps.actual_termination_date, pps.last_standard_process_date
2056 order by p_id;*/
2057
2058 cursor csr_fetch_asg_asgno is
2059 select max(peo.person_id) p_id,
2060 peo.last_name,
2061 peo.full_name,
2062 peo.national_identifier,
2063 nvl(peo.per_information10,'N') agg_flag,
2064 'EMP', --ppt.system_person_type,
2065 pps.actual_termination_date,
2066 pps.last_standard_process_date,
2067 p_m34_rec.effective_date
2068 from per_all_people_f peo,
2069 per_all_assignments_f asg,
2070 per_periods_of_service pps
2071 where asg.business_group_id = g_business_group_id
2072 and peo.business_group_id = g_business_group_id
2073 and peo.person_id = asg.person_id
2074 -- Bug 6864422 modified p_m34_rec.full_name to substr of peo.last_name length
2075 --and upper(peo.last_name) like upper(substr(rpad(p_m34_rec.full_name,5,'%'), 1, 5))||'%'
2076 and upper(peo.last_name) like upper(substr(rpad(substr(p_m34_rec.full_name,1,length(peo.last_name)),5,'%'), 1, 5))||'%'
2077 --and upper(substr(rpad(peo.last_name,5,' '),1,5)) = upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5))
2078 and asg.assignment_number = p_m34_rec.assignment_number
2079 and p_m34_rec.national_identifier is null
2080 and pps.period_of_service_id = asg.period_of_service_id
2081 and p_m34_rec.effective_date between asg.effective_start_date and asg.effective_end_date
2082 and p_m34_rec.effective_date between peo.effective_start_date and peo.effective_end_date
2083 and (pps.actual_termination_date is null or
2084 p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
2085 and exists (select 1
2086 from per_person_type_usages_f ptu,
2087 per_person_types ppt
2088 where ptu.person_id = peo.person_id
2089 and ptu.person_type_id = ppt.person_type_id
2090 and ppt.system_person_type in ('EMP','EMP_APL')
2091 and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
2092 group by peo.last_name, peo.full_name, peo.national_identifier, peo.per_information10,
2093 'EMP' /*ppt.system_person_type*/, pps.actual_termination_date, pps.last_standard_process_date
2094 order by p_id;
2095
2096 cursor csr_fetch_asg_natid is
2097 select max(peo.person_id) p_id,
2098 peo.last_name,
2099 peo.full_name,
2100 peo.national_identifier,
2101 nvl(peo.per_information10,'N') agg_flag,
2102 'EMP', --ppt.system_person_type,
2103 pps.actual_termination_date,
2104 pps.last_standard_process_date,
2105 p_m34_rec.effective_date
2106 from per_all_people_f peo,
2107 per_all_assignments_f asg,
2108 per_periods_of_service pps
2109 where asg.business_group_id = g_business_group_id
2110 and peo.business_group_id = g_business_group_id
2111 and peo.person_id = asg.person_id
2112 -- Bug 6864422 modified p_m34_rec.full_name to substr of peo.last_name length
2113 --and upper(peo.last_name) like upper(substr(rpad(p_m34_rec.full_name,5,'%'), 1, 5))||'%'
2114 and upper(peo.last_name) like upper(substr(rpad(substr(p_m34_rec.full_name,1,length(peo.last_name)),5,'%'), 1, 5))||'%'
2115 --and upper(substr(rpad(peo.last_name,5,' '),1,5)) = upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5))
2116 and p_m34_rec.assignment_number is null
2117 and peo.national_identifier like substr(p_m34_rec.national_identifier,1,8)||'%'
2118 and pps.period_of_service_id = asg.period_of_service_id
2119 and p_m34_rec.effective_date between asg.effective_start_date and asg.effective_end_date
2120 and p_m34_rec.effective_date between peo.effective_start_date and peo.effective_end_date
2121 and (pps.actual_termination_date is null or
2122 p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
2123 and exists (select 1
2124 from per_person_type_usages_f ptu,
2125 per_person_types ppt
2126 where ptu.person_id = peo.person_id
2127 and ptu.person_type_id = ppt.person_type_id
2128 and ppt.system_person_type in ('EMP','EMP_APL')
2129 and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
2130 group by peo.last_name, peo.full_name, peo.national_identifier, peo.per_information10,
2131 'EMP' /*ppt.system_person_type*/, pps.actual_termination_date, pps.last_standard_process_date
2132 order by p_id;
2133
2134 cursor csr_fetch_asg_other is
2135 select max(peo.person_id) p_id,
2136 peo.last_name,
2137 peo.full_name,
2138 peo.national_identifier,
2139 nvl(peo.per_information10,'N') agg_flag,
2140 'EMP', --ppt.system_person_type,
2141 pps.actual_termination_date,
2142 pps.last_standard_process_date,
2143 p_m34_rec.effective_date
2144 from per_all_people_f peo,
2145 per_all_assignments_f asg,
2146 per_periods_of_service pps
2147 where asg.business_group_id = g_business_group_id
2148 and peo.business_group_id = g_business_group_id
2149 and peo.person_id = asg.person_id
2150 -- Bug 6864422 modified p_m34_rec.full_name to substr of peo.last_name length
2151 --and upper(peo.last_name) like upper(substr(rpad(p_m34_rec.full_name,5,'%'), 1, 5))||'%'
2152 and upper(peo.last_name) like upper(substr(rpad(substr(p_m34_rec.full_name,1,length(peo.last_name)),5,'%'), 1, 5))||'%'
2153 --and upper(substr(rpad(peo.last_name,5,' '),1,5)) = upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5))
2154 and asg.assignment_number = p_m34_rec.assignment_number
2155 and peo.national_identifier like substr(p_m34_rec.national_identifier,1,8)||'%'
2156 and pps.period_of_service_id = asg.period_of_service_id
2157 and p_m34_rec.effective_date between asg.effective_start_date and asg.effective_end_date
2158 and p_m34_rec.effective_date between peo.effective_start_date and peo.effective_end_date
2159 and (pps.actual_termination_date is null or
2160 p_m34_rec.effective_date between pps.date_start and pps.actual_termination_date)
2161 and exists (select 1
2162 from per_person_type_usages_f ptu,
2163 per_person_types ppt
2164 where ptu.person_id = peo.person_id
2165 and ptu.person_type_id = ppt.person_type_id
2166 and ppt.system_person_type in ('EMP','EMP_APL')
2167 and p_m34_rec.effective_date between ptu.effective_start_date and ptu.effective_end_date)
2168 group by peo.last_name, peo.full_name, peo.national_identifier, peo.per_information10,
2169 'EMP' /*ppt.system_person_type*/, pps.actual_termination_date, pps.last_standard_process_date
2170 order by p_id;
2171 --For Bug 7649174 End
2172 cursor csr_count_emp(p_tax_district varchar2,
2173 p_person_id number,
2174 p_date date,
2175 p_asg_no varchar2 default null) is
2176 select count(*)
2177 from per_all_assignments_f asg,
2178 pay_all_payrolls_f pay,
2179 hr_soft_coding_keyflex sck
2180 where asg.person_id = p_person_id
2181 and asg.payroll_id = pay.payroll_id
2182 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2183 and sck.segment1 = p_tax_district
2184 and (p_asg_no is null or
2185 asg.assignment_number = p_asg_no
2186 )
2187 and p_date between asg.effective_start_date and asg.effective_end_date
2188 and p_date between pay.effective_start_date and pay.effective_end_date;
2189
2190 cursor csr_check_future_asg(p_tax_district varchar2,
2191 p_person_id number,
2192 p_date date) is
2193 select count(*)
2194 from per_all_people_f peo,
2195 per_all_assignments_f asg,
2196 pay_all_payrolls_f pay,
2197 hr_soft_coding_keyflex sck
2198 where peo.person_id = p_person_id
2199 and asg.person_id = peo.person_id
2200 and asg.payroll_id = pay.payroll_id
2201 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2202 and sck.segment1 = p_tax_district
2203 and nvl(peo.per_information10,'N') = 'Y'
2204 and asg.effective_start_date > p_date
2205 and p_date between pay.effective_start_date and pay.effective_end_date
2206 and asg.effective_start_date between peo.effective_start_date and peo.effective_end_date
2207 and asg.assignment_id not in (select assignment_id
2208 from per_all_assignments_f asg,
2209 pay_all_payrolls_f pay,
2210 hr_soft_coding_keyflex sck
2211 where asg.person_id = p_person_id
2212 and asg.payroll_id = pay.payroll_id
2213 and pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2214 and sck.segment1 = p_tax_district
2215 and p_date between asg.effective_start_date and asg.effective_end_date
2216 and p_date between pay.effective_start_date and pay.effective_end_date);
2217
2218 r_person_rec csr_fetch_asg_other%rowtype; -- Bug 7649174
2219 BEGIN
2220 l_null_field := 0;
2221 if upper(p_m34_rec.national_identifier) like '%NONE%' then
2222 p_m34_rec.national_identifier := null;
2223 l_null_field := l_null_field + 1;
2224 end if;
2225 if upper(p_m34_rec.assignment_number) like '%NONE%' then
2226 p_m34_rec.assignment_number := null;
2227 l_null_field := l_null_field + 1;
2228 end if;
2229
2230 if l_null_field > 1 then
2231 l_msg := err_data_mismatch;
2232 raise ex_error;
2233 end if;
2234
2235 l_counter := 0;
2236 -- For Bug 7649174 Start
2237 /* for r_person_rec in csr_fetch_asg loop
2238 l_last_name := r_person_rec.last_name;
2239 if upper(substr(rpad(l_last_name,5,' '),1,5)) =
2240 -- Bug 6864422 modified p_m34_rec.full_name to substr of l_last_name length
2241 -- upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5)) then
2242 upper(substr(rpad(substr(p_m34_rec.full_name,1,length(l_last_name)),5,' '), 1, 5)) then
2243 l_counter := l_counter + 1;
2244 l_person_rec.person_id := r_person_rec.p_id;
2245 l_person_rec.full_name := r_person_rec.full_name;
2246 l_person_rec.ni_number := r_person_rec.national_identifier;
2247 l_person_rec.aggregate_flag := r_person_rec.agg_flag;
2248 l_person_rec.person_type := 'EMP';
2249 l_person_rec.term_date := r_person_rec.actual_termination_date;
2250 l_person_rec.lsp_date := r_person_rec.last_standard_process_date;
2251 end if;
2252 end loop; */
2253 if (p_m34_rec.assignment_number is not null and p_m34_rec.national_identifier is null) then
2254 open csr_fetch_asg_asgno;
2255 elsif (p_m34_rec.national_identifier is not null and p_m34_rec.assignment_number is null) then
2256 open csr_fetch_asg_natid;
2257 else
2258 open csr_fetch_asg_other;
2259 end if;
2260 loop
2261 if (p_m34_rec.assignment_number is not null and p_m34_rec.national_identifier is null) then
2262 fetch csr_fetch_asg_asgno into r_person_rec;
2263 exit when csr_fetch_asg_asgno%notfound;
2264 elsif (p_m34_rec.national_identifier is not null and p_m34_rec.assignment_number is null) then
2265 fetch csr_fetch_asg_natid into r_person_rec;
2266 exit when csr_fetch_asg_natid%notfound;
2267 else
2268 fetch csr_fetch_asg_other into r_person_rec;
2269 exit when csr_fetch_asg_other%notfound;
2270 end if;
2271 --For Bug 7649174 End
2272 l_last_name := r_person_rec.last_name;
2273 if upper(substr(rpad(l_last_name,5,' '),1,5)) =
2274 -- Bug 6864422 modified p_m34_rec.full_name to substr of l_last_name length
2275 -- upper(substr(rpad(p_m34_rec.full_name,5,' '), 1, 5)) then
2276 upper(substr(rpad(substr(p_m34_rec.full_name,1,length(l_last_name)),5,' '), 1, 5)) then
2277 l_counter := l_counter + 1;
2278 l_person_rec.person_id := r_person_rec.p_id;
2279 l_person_rec.full_name := r_person_rec.full_name;
2280 l_person_rec.ni_number := r_person_rec.national_identifier;
2281 l_person_rec.aggregate_flag := r_person_rec.agg_flag;
2282 l_person_rec.person_type := 'EMP';
2283 l_person_rec.term_date := r_person_rec.actual_termination_date;
2284 l_person_rec.lsp_date := r_person_rec.last_standard_process_date;
2285 end if;
2286 end loop;
2287 --For Bug 7649174 Start
2288 if (p_m34_rec.assignment_number is not null and p_m34_rec.national_identifier is null) then
2289 close csr_fetch_asg_asgno;
2290 elsif (p_m34_rec.national_identifier is not null and p_m34_rec.assignment_number is null) then
2291 close csr_fetch_asg_natid;
2292 else
2293 close csr_fetch_asg_other;
2294 end if;
2295 --For Bug 7649174 End
2296 if l_counter = 0 then
2297 raise no_data_found;
2298 end if;
2299 if l_counter > 1 then
2300 raise too_many_rows;
2301 end if;
2302 if l_counter = 1 then
2303 p_per_rec.person_id := l_person_rec.person_id;
2304 p_per_rec.full_name := l_person_rec.full_name;
2305 p_per_rec.ni_number := l_person_rec.ni_number;
2306 p_per_rec.aggregate_flag := l_person_rec.aggregate_flag;
2307 p_per_rec.person_type := l_person_rec.person_type;
2308 p_per_rec.term_date := l_person_rec.term_date;
2309 p_per_rec.lsp_date := l_person_rec.lsp_date;
2310 p_per_rec.effective_date := p_m34_rec.effective_date;
2311 end if;
2312
2313 if p_m34_rec.assignment_number is null then
2314 open csr_count_emp(p_m34_rec.paye_ref,
2315 p_per_rec.person_id,
2316 p_m34_rec.effective_date);
2317 fetch csr_count_emp into l_count;
2318 close csr_count_emp;
2319
2320 if l_count = 0 then
2321 l_msg := err_emp_not_found;
2322 raise ex_error;
2323 end if;
2324
2325 if p_per_rec.aggregate_flag = 'N' then
2326 if l_count > 1 then
2327 l_msg := err_multi_asg;
2328 raise ex_error;
2329 end if;
2330 end if;
2331 else -- Even if the assignment number is supplied
2332 -- we need to check that assignment is on the
2333 -- given tax district
2334 open csr_count_emp(p_m34_rec.paye_ref,
2335 p_per_rec.person_id,
2336 p_m34_rec.effective_date,
2337 p_m34_rec.assignment_number);
2338 fetch csr_count_emp into l_count;
2339 close csr_count_emp;
2340 if l_count = 0 then
2341 l_msg := err_emp_not_found;
2342 raise ex_error;
2343 end if;
2344 end if;
2345
2346 if p_per_rec.aggregate_flag = 'Y' then
2347 open csr_check_future_asg(p_m34_rec.paye_ref,
2348 p_per_rec.person_id,
2349 p_m34_rec.effective_date);
2350 fetch csr_check_future_asg into l_count;
2351 close csr_check_future_asg;
2352 if l_count > 0 then
2353 l_msg := err_future_asg;
2354 raise ex_error;
2355 end if;
2356 end if;
2357
2358 return null;
2359 EXCEPTION
2360 when ex_error then
2361 return l_msg;
2362 when too_many_rows then
2363 return err_multiple_found;
2364 when no_data_found then
2365 return err_emp_not_found;
2366 when others then
2367 --For Bug 7649174 Start
2368 if csr_fetch_asg_asgno%isopen then
2369 close csr_fetch_asg_asgno;
2370 end if;
2371 if csr_fetch_asg_natid%isopen then
2372 close csr_fetch_asg_natid;
2373 end if;
2374 if csr_fetch_asg_other%isopen then
2375 close csr_fetch_asg_other;
2376 end if;
2377 --For Bug 7649174 End
2378 raise;
2379 END find_employee;
2380
2381 ---------------------------------------------------------------------
2382 -- NAME : update_record --
2383 -- Type : Private Function --
2384 -- DESCRIPTION : --
2385 -- Function to updates the PAYE Element entries by calling the --
2386 -- Element Entry API. Returns any message if error found --
2387 ---------------------------------------------------------------------
2388 FUNCTION update_record(p_paye_rec IN g_typ_paye_record,
2389 p_per_rec IN g_typ_per_record,
2390 p_m34_rec IN g_tax_code_interface default null) return VARCHAR2
2391 IS
2392 l_issue_date date;
2393 l_message_date date;
2394 l_paye_rec g_typ_paye_record;
2395 l_input_value_id_tbl hr_entry.number_table;
2396 l_entry_value_tbl hr_entry.varchar2_table;
2397 l_row_id rowid;
2398 l_num_entry_values number;
2399
2400 cursor c1 (c_row_id in rowid) is
2401 select rowid from pay_gb_tax_code_interface
2402 where rowid = c_row_id
2403 for update;
2404
2405 BEGIN
2406 l_paye_rec := p_paye_rec;
2407 --
2408 if g_mode in (3,4) then
2409 l_issue_date := p_m34_rec.issue_date;
2410 l_message_date := p_m34_rec.date_of_message;
2411 end if;
2412
2413 ----------------------------
2414 -- Convert entry into tbl --
2415 ----------------------------
2416 conv_to_table(l_paye_rec,l_num_entry_values,
2417 l_input_value_id_tbl,l_entry_value_tbl);
2418 ----------------------------
2419 -- Call API to update --
2420 ----------------------------
2421
2422 if g_validate_only ='N' then /*Added soy 08-09*/
2423 hr_utility.trace(' In Validate And Commit Mode therefore updating.');
2424
2425 -- For bug 8485686
2426 pqp_gb_ad_ee.g_global_paye_validation := 'N';
2427
2428 hr_entry_api.update_element_entry(
2429 p_dt_update_mode => rtrim(p_paye_rec.dt_update_mode),
2430 p_session_date => p_per_rec.effective_date,
2431 p_element_entry_id => p_paye_rec.element_entry_id,
2432 p_num_entry_values => l_num_entry_values,
2433 p_input_value_id_tbl => l_input_value_id_tbl,
2434 p_entry_value_tbl => l_entry_value_tbl,
2435 p_entry_information_category => 'GB_PAYE',
2436 p_entry_information1 => fnd_date.date_to_canonical(l_issue_date),
2437 p_entry_information2 => fnd_date.date_to_canonical(l_message_date));
2438
2439 -- For bug 8485686
2440 pqp_gb_ad_ee.g_global_paye_validation := 'Y';
2441
2442 end if;
2443
2444 ---------------------------------------------------
2445 -- If mode 3 or 4, update the tax code interface --
2446 ---------------------------------------------------
2447 if (g_mode in (3,4)) then
2448 open c1(p_m34_rec.row_id);
2449 fetch c1 into l_row_id;
2450 update pay_gb_tax_code_interface
2451 set processed_flag = 'P'
2452 where current of c1;
2453 close c1;
2454 end if;
2455 ------------------------------
2456 -- Now stamp the creator id --
2457 ------------------------------
2458 if g_validate_only ='N' then /*Added soy 08-09*/
2459 hr_utility.trace(' In Validate And Commit Mode therefore updating.');
2460 update pay_element_entries_f pef
2461 set pef.creator_id = nvl(g_request_id,g_current_req_id)
2462 where pef.element_entry_id = p_paye_rec.element_entry_id
2463 and pef.effective_start_date = p_per_rec.effective_date;
2464 --and pef.effective_start_date = p_paye_rec.effective_start_date
2465 --and pef.effective_end_date = p_paye_rec.effective_end_date;
2466 end if;
2467 ------------------------------------------
2468 -- No error, so update the update count --
2469 ------------------------------------------
2470 g_update_count := g_update_count + 1;
2471 --
2472 return null;
2473 EXCEPTION
2474 WHEN others THEN
2475 return substrb(sqlerrm(sqlcode),1,60);
2476 END update_record;
2477
2478 ---------------------------------------------------------------------
2479 -- NAME : process_record --
2480 -- Type : Private Procedure --
2481 -- DESCRIPTION : --
2482 -- This procedure will process the given assignment based on the --
2483 -- mode --
2484 ---------------------------------------------------------------------
2485 PROCEDURE process_record(p_m12_rec IN g_typ_per_record default null,
2486 p_m34_rec IN g_tax_code_interface default null)
2487 IS
2488 ex_edi_error exception;
2489 ex_resume_mode exception;
2490 ex_asg_error exception;
2491 ex_not_process exception;
2492 l_exists number;
2493 l_process boolean;
2494 l_dir varchar2(255);
2495 l_msg varchar2(255);
2496 l_paye_ref varchar2(30);
2497 l_asg_number varchar2(100);
2498 l_per_record g_typ_per_record;
2499 l_paye_rec g_typ_paye_record;
2500 l_old_paye_rec g_typ_paye_record;
2501 l_m34_rec g_tax_code_interface;
2502 -- Start of bug 8510399
2503 l_count number := 1;
2504 person_err_status boolean := FALSE;
2505 l_rti_tax_ref number;
2506 l_uptake_value varchar2(100) := 'NONE';
2507 l_paye_agg varchar2(100);
2508 l_asg_id number;
2509 l_pay_id_found number;
2510 -- End of bug 8510399
2511
2512 cursor csr_get_asg_num is
2513 select paaf.assignment_number,
2514 paaf.assignment_id,
2515 nvl(papf.per_information10,'N')
2516 from per_assignment_extra_info paei,
2517 per_all_assignments_f paaf,
2518 per_assignment_status_types pat,
2519 per_all_people_f papf
2520 where paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
2521 and paei.AEI_INFORMATION3 = p_m34_rec.assignment_number
2522 and paaf.assignment_id = paei.assignment_id
2523 and paaf.assignment_status_type_id = pat.assignment_status_type_id
2524 and pat.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
2525 and p_m34_rec.effective_date between paaf.effective_start_date and paaf.effective_end_date
2526 and papf.person_id = paaf.person_id
2527 and p_m34_rec.effective_date between papf.effective_start_date and papf.effective_end_date
2528 and ( nvl(papf.per_information10,'N') <> 'Y' OR paaf.PRIMARY_FLAG = 'Y');
2529 cursor csr_asg is
2530 select paaf.assignment_id
2531 from per_all_assignments_f paaf,
2532 pay_payrolls_f pay,
2533 hr_soft_coding_keyflex sck
2534 where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =l_asg_id)
2535 and pay.payroll_id = paaf.payroll_id
2536 and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
2537 and sck.SEGMENT1 = p_m34_rec.paye_ref
2538 and paaf.EFFECTIVE_START_DATE =
2539 ( select max(paaf1.effective_start_date)
2540 from per_all_assignments_f paaf1
2541 where paaf1.assignment_id = paaf.assignment_id
2542 and paaf1.assignment_type = 'E'
2543 and paaf1.effective_start_date <= p_m34_rec.effective_date
2544 )
2545 and p_m34_rec.effective_date between pay.EFFECTIVE_START_DATE and pay.EFFECTIVE_END_DATE;
2546 BEGIN
2547 l_msg := null;
2548 l_paye_ref := null;
2549 l_asg_number := null;
2550 ------------------------------------
2551 -- First setup the record details --
2552 ------------------------------------
2553 if g_mode in (1,2) then
2554 l_per_record := p_m12_rec;
2555 l_paye_ref := p_m12_rec.tax_ref; -- Bug#8497477 : assigned tax ref to fetch assignments which has same tax ref.
2556 elsif g_mode in (3,4) then
2557 l_m34_rec := p_m34_rec;
2558 -- Here based on profile we have to process the employee.
2559 -- Get the profile value
2560 fnd_profile.get('GB RTI Uptake', l_uptake_value);
2561 hr_utility.trace(' l_uptake_value : '||l_uptake_value);
2562
2563 /* When RTI Profile is set to None continue the old process
2564 as employer is not live on RTI.
2565 */
2566 IF l_uptake_value IS NOT NULL and l_uptake_value = 'ALL' THEN
2567 -- treat the works number as payroll id and get the assignment number for the
2568 -- payroll id.
2569 l_m34_rec.assignment_number := null;
2570 hr_utility.trace('In All: '||l_uptake_value);
2571 open csr_get_asg_num;
2572 fetch csr_get_asg_num into l_m34_rec.assignment_number,l_asg_id,l_paye_agg;
2573 close csr_get_asg_num;
2574 hr_utility.trace('Assignment_number : ' || l_m34_rec.assignment_number);
2575
2576 if l_m34_rec.assignment_number is null then
2577 l_msg := err_emp_not_found_payroll;
2578 raise ex_edi_error;
2579 elsif l_paye_agg = 'Y' then
2580 --check all the assignments in this PAYE ref have RTI Payroll id.
2581 for asg_rec in csr_asg
2582 loop
2583 begin
2584 select 1 into l_pay_id_found from per_assignment_extra_info paei where assignment_id = asg_rec.assignment_id
2585 and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION' and paei.AEI_INFORMATION3 is not null;
2586 exception
2587 when no_data_found then
2588 l_msg := 'Payroll id for one of the aggregated assignments not present for supplied Payroll ID:'|| p_m34_rec.assignment_number;
2589 raise ex_edi_error;
2590 end;
2591 end loop;
2592 end if;
2593
2594 elsif l_uptake_value = 'PARTIAL' then
2595 -- treat the works number as payroll id and get the assignment number for the
2596 -- payroll id.
2597
2598 begin--2
2599 --check whether the given paye reference is live on RTI.
2600 SELECT 1
2601 INTO l_rti_tax_ref
2602 FROM pqp_configuration_values
2603 WHERE business_group_id = g_business_group_id
2604 AND pcv_information_category = 'PAY_GB_RTI_FPS_BACS'
2605 AND pcv_information1 = p_m34_rec.paye_ref
2606 AND p_m34_rec.effective_date >= fnd_date.canonical_to_date(PCV_INFORMATION2);
2607
2608 l_m34_rec.assignment_number := null;
2609 open csr_get_asg_num;
2610 fetch csr_get_asg_num into l_m34_rec.assignment_number,l_asg_id,l_paye_agg;
2611 close csr_get_asg_num;
2612 hr_utility.trace('Assignment_number : ' || l_m34_rec.assignment_number);
2613
2614 if l_m34_rec.assignment_number is null then
2615 l_msg := err_emp_not_found_payroll;
2616 raise ex_edi_error;
2617 elsif l_paye_agg = 'Y' then
2618 --check all the assignments in this PAYE ref have RTI Payroll id.
2619 for asg_rec in csr_asg
2620 loop
2621 begin
2622 select 1 into l_pay_id_found from per_assignment_extra_info paei where assignment_id = asg_rec.assignment_id
2623 and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION' and paei.AEI_INFORMATION3 is not null;
2624 exception
2625 when no_data_found then
2626 l_msg := 'Payroll id for one of the aggregated assignments not present for supplied Payroll ID:'|| p_m34_rec.assignment_number;
2627 raise ex_edi_error;
2628 end;
2629 end loop;
2630 end if;
2631 exception --2
2632 when no_data_found then
2633 hr_utility.trace('Profile is partial but RTI not live for paye ref:'|| p_m34_rec.paye_ref);
2634 end;--2
2635 END IF;
2636 l_msg := find_employee(l_m34_rec, l_per_record);
2637 -- Start bug#8497477 : Assigned values before the exception as if exception raises we can't assign the values.
2638 l_paye_ref := l_m34_rec.paye_ref;
2639 l_asg_number := l_m34_rec.assignment_number;
2640 if l_msg is not null then
2641 raise ex_edi_error;
2642 end if;
2643 /*l_paye_ref := l_m34_rec.paye_ref;
2644 l_asg_number := l_m34_rec.assignment_number;*/
2645 -- End bug#8497477
2646 end if;
2647 hr_utility.trace('Start processing record');
2648 hr_utility.trace(' Person ID : ' || l_per_record.person_id);
2649 hr_utility.trace(' NI Number : ' || l_per_record.ni_number);
2650 hr_utility.trace(' Aggregated : ' || l_per_record.aggregate_flag);
2651 hr_utility.trace(' Director : ' || l_per_record.director_flag);
2652 hr_utility.trace(' Person type : ' || l_per_record.person_type);
2653 hr_utility.trace(' Term date : ' || l_per_record.term_date);
2654 hr_utility.trace(' LSP date : ' || l_per_record.lsp_date);
2655 hr_utility.trace(' Start date : ' || l_per_record.start_date);
2656 hr_utility.trace(' End date : ' || l_per_record.end_date);
2657
2658 -----------------------------------------------------------------------
2659 -- Now, loop through each assignment and see if we should process it --
2660 -----------------------------------------------------------------------
2661 -- Start of bug#8510399
2662 -- New save point is created.
2663 -- if any of aggregated assignment is failed, we need to rollback the
2664 -- PAYE uplift changes for all other aggregated assignments.
2665 -- bug#9253974: -- set save point only for PAYE aggregated person.
2666 If l_per_record.aggregate_flag = 'Y' then
2667 SAVEPOINT rollback_per_PAYE;
2668 end if;
2669 -- End of bug#8510399
2670 for asg_record in csr_asg_details(l_per_record.person_id,
2671 l_per_record.aggregate_flag,
2672 l_paye_ref,
2673 l_asg_number,
2674 l_per_record.effective_date)
2675 loop
2676 hr_utility.trace('Start processing record');
2677 hr_utility.trace(' Assignment ID : ' || asg_record.assignment_id);
2678 hr_utility.trace(' Payroll ID : ' || asg_record.payroll_id);
2679 hr_utility.trace(' Assignment No : ' || asg_record.assignment_number);
2680 hr_utility.trace(' Asg Status : ' || asg_record.asg_status);
2681 hr_utility.trace(' PAYE Ref : ' || asg_record.tax_ref);
2682 --------------------------------------------
2683 -- Anonymous block to trap non fatal error --
2684 ---------------------------------------------
2685 begin
2686
2687 l_dir := null;
2688 l_process := true;
2689 g_cpe_flag :='Y'; /*Added soy 08-09*/
2690
2691 ----------------------
2692 -- Check for EX-EMP --
2693 ----------------------
2694 l_msg := check_leaver(l_per_record);
2695 if l_msg is not null then
2696 raise ex_asg_error;
2697 end if;
2698
2699 hr_utility.trace(' Fetch PAYE record');
2700 l_msg := get_paye_record(asg_record.assignment_id,
2701 l_per_record.effective_date,
2702 l_paye_rec);
2703 ------------------------------
2704 -- Check for tax code error --
2705 ------------------------------
2706 if l_msg is not null then
2707 raise ex_asg_error;
2708 end if;
2709 --------------------------
2710 -- See if we found PAYE --
2711 --------------------------
2712 if l_paye_rec.element_entry_id is null then
2713 if g_mode in (3,4) then
2714 l_msg := replace(err_no_paye_ele,'DATE',to_char(l_per_record.effective_date,'DD-MON-YYYY'));
2715 raise ex_asg_error;
2716 else
2717 raise ex_not_process;
2718 end if;
2719 end if;
2720 ---------------------------
2721 -- Check for resume mode --
2722 ---------------------------
2723 open get_req_cur (l_paye_rec.creator_id);
2724 fetch get_req_cur into l_exists;
2725 if get_req_cur%FOUND then
2726 l_process := false;
2727 hr_utility.trace(' Not processing the assignment');
2728 end if;
2729 close get_req_cur;
2730 ------------------------------------------------------------------
2731 -- If part of this assignment already been processed --
2732 -- we can assumed that the whole person already been processed --
2733 -- as the commit is done on a full-person basis --
2734 ------------------------------------------------------------------
2735 if l_process = false then
2736 raise ex_resume_mode;
2737 end if;
2738
2739 --------------------------------------------------------------------------
2740 --Check assignment's CPE In case of aggegated and terminated Assignment --
2741 --If this terminated assignment is in active CPE then we have to process--
2742 --the assignment Added soy 08-09 --
2743 --------------------------------------------------------------------------
2744 if l_per_record.aggregate_flag ='Y' AND asg_record.asg_status ='TERM_ASSIGN' then
2745 g_cpe_flag := set_cpe_flag(l_per_record.person_id,
2746 asg_record.assignment_id,
2747 asg_record.tax_ref,
2748 l_per_record.effective_date );
2749 -- Start of bug 8510399
2750 if g_cpe_flag = 'N' then
2751 raise ex_not_process;
2752 end if;
2753 -- End of bug 8510399
2754 end if;
2755 ------------------------------------
2756 -- Make a copy of the paye record --
2757 ------------------------------------
2758 l_old_paye_rec := l_paye_rec;
2759 -----------------------------
2760 -- Set the new PAYE record --
2761 -----------------------------
2762 -- Start of bug#8510399
2763 l_msg := set_new_paye_record(asg_record.asg_status,
2764 asg_record.assignment_id,
2765 l_paye_rec, l_m34_rec,l_per_record.aggregate_flag);
2766 if l_msg is not null then
2767 if g_payroll_id is not null then
2768 if asg_record.payroll_id <> g_payroll_id
2769 and l_per_record.aggregate_flag ='N'then
2770 raise ex_not_process;
2771 else
2772 raise ex_asg_error;
2773 end if;
2774 else
2775 raise ex_asg_error;
2776 end if;
2777 end if;
2778 -- End of bug#8510399
2779 hr_utility.trace(' Element Entry ID : ' || l_paye_rec.element_entry_id );
2780 hr_utility.trace(' Start Date : ' || l_paye_rec.effective_start_date );
2781 hr_utility.trace(' End Date : ' || l_paye_rec.effective_end_date );
2782 hr_utility.trace(' Tax Code : ' || l_paye_rec.tax_code_sv );
2783 hr_utility.trace(' Tax Basis : ' || l_paye_rec.tax_basis_sv );
2784 hr_utility.trace(' Previous Pay : ' || l_paye_rec.pay_previous_sv );
2785 hr_utility.trace(' Previous Tax : ' || l_paye_rec.tax_previous_sv );
2786 hr_utility.trace(' Authority : ' || l_paye_rec.authority_sv );
2787 hr_utility.trace(' Done setting new PAYE');
2788 -----------------------------------------------
2789 -- If mode 1 then try to process NI director --
2790 -----------------------------------------------
2791 if g_mode = 1 then
2792 hr_utility.trace(' Check if we need to do any update');
2793 l_dir := process_directors(l_per_record,asg_record.assignment_id);
2794 if l_paye_rec.tax_basis_amended or l_paye_rec.p45_val_amended or
2795 l_paye_rec.tax_code_amended or l_dir is not null then
2796 hr_utility.trace(' Calling update_record to update PAYE');
2797 l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
2798 if l_msg is not null then
2799 raise ex_asg_error;
2800 end if;
2801 hr_utility.trace(' Write out body section');
2802 --Start of bug#8510399
2803 -- Start bug#8497477 : Report tax ref if current and previous references are not same.
2804 -- Bug#9253974 : Write to temporary table if PAYE aggregations is set
2805 If l_per_record.aggregate_flag <> 'Y' then
2806 write_Tax_Ref(asg_record.tax_ref,'P');
2807 -- End bug#8497477
2808 write_body(l_old_paye_rec,l_paye_rec,l_per_record, asg_record.assignment_number,l_dir,
2809 p_m34_rec.effective_date,asg_record.assignment_id, asg_record.tax_ref);
2810 else
2811 tb_write_paye_rec(l_count).tax_ref := asg_record.tax_ref;
2812 tb_write_paye_rec(l_count).status := 'P';
2813 tb_write_paye_rec(l_count).old_paye_rec := l_old_paye_rec;
2814 tb_write_paye_rec(l_count).paye_rec := l_paye_rec;
2815 tb_write_paye_rec(l_count).per_record := l_per_record;
2816 tb_write_paye_rec(l_count).assignment_number := asg_record.assignment_number;
2817 tb_write_paye_rec(l_count).dir := l_dir;
2818 tb_write_paye_rec(l_count).effective_date := p_m34_rec.effective_date;
2819 tb_write_paye_rec(l_count).assignment_id := asg_record.assignment_id;
2820 l_count := l_count + 1;
2821 end if;
2822 -- end of bug#8510399
2823 end if;
2824 else
2825 hr_utility.trace(' Check if we need to do any update');
2826 -- if l_paye_rec.tax_basis_amended or l_paye_rec.p45_val_amended or
2827 -- l_paye_rec.tax_code_amended then
2828 hr_utility.trace(' Calling update_record to update PAYE');
2829 --Added for Bug 7373763
2830 if g_mode=2 then
2831 if l_paye_rec.tax_code_amended=TRUE then
2832 l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
2833 if l_msg is not null then
2834 raise ex_asg_error;
2835 end if;
2836 hr_utility.trace(' Write out body section');
2837 --Start of bug#8510399
2838 -- Start bug#8497477 : Report tax ref if current and previous references are not same.
2839 -- Bug#9253974 : Write to temporary table if PAYE aggregations is set
2840 If l_per_record.aggregate_flag <> 'Y' then
2841 write_Tax_Ref(asg_record.tax_ref,'P');
2842 -- End bug#8497477
2843 write_body(l_old_paye_rec,l_paye_rec,l_per_record, asg_record.assignment_number,l_dir,
2844 p_m34_rec.effective_date, asg_record.assignment_id, asg_record.tax_ref);
2845 else
2846 tb_write_paye_rec(l_count).tax_ref := asg_record.tax_ref;
2847 tb_write_paye_rec(l_count).status := 'P';
2848 tb_write_paye_rec(l_count).old_paye_rec := l_old_paye_rec;
2849 tb_write_paye_rec(l_count).paye_rec := l_paye_rec;
2850 tb_write_paye_rec(l_count).per_record := l_per_record;
2851 tb_write_paye_rec(l_count).assignment_number := asg_record.assignment_number;
2852 tb_write_paye_rec(l_count).dir := l_dir;
2853 tb_write_paye_rec(l_count).effective_date := p_m34_rec.effective_date;
2854 tb_write_paye_rec(l_count).assignment_id := asg_record.assignment_id;
2855 l_count := l_count + 1;
2856 end if;
2857 -- end of bug#8510399
2858 end if;
2859 else
2860 l_msg := update_record(l_paye_rec, l_per_record, l_m34_rec);
2861 if l_msg is not null then
2862 raise ex_asg_error;
2863 end if;
2864 hr_utility.trace(' Write out body section');
2865 --Start of bug#8510399
2866 -- Start bug#8497477 : Report tax ref if current and previous references are not same.
2867 -- Bug#9253974 : Write to temporary table if PAYE aggregations is set
2868 If l_per_record.aggregate_flag <> 'Y' then
2869 write_Tax_Ref(asg_record.tax_ref,'P');
2870 -- End bug#8497477
2871 write_body(l_old_paye_rec,l_paye_rec,l_per_record, asg_record.assignment_number,l_dir,
2872 p_m34_rec.effective_date, asg_record.assignment_id, asg_record.tax_ref);
2873 else
2874 tb_write_paye_rec(l_count).tax_ref := asg_record.tax_ref;
2875 tb_write_paye_rec(l_count).status := 'P';
2876 tb_write_paye_rec(l_count).old_paye_rec := l_old_paye_rec;
2877 tb_write_paye_rec(l_count).paye_rec := l_paye_rec;
2878 tb_write_paye_rec(l_count).per_record := l_per_record;
2879 tb_write_paye_rec(l_count).assignment_number := asg_record.assignment_number;
2880 tb_write_paye_rec(l_count).dir := l_dir;
2881 tb_write_paye_rec(l_count).effective_date := p_m34_rec.effective_date;
2882 tb_write_paye_rec(l_count).assignment_id := asg_record.assignment_id;
2883 l_count := l_count + 1;
2884 end if;
2885 -- end of bug#8510399
2886 end if;
2887 --Bug 7373763 ends
2888
2889 -- end if;
2890 end if;
2891 exception
2892 WHEN ex_not_process THEN
2893 null;
2894 WHEN ex_asg_error THEN
2895 -- Bug#8497477 : Passed tax ref to display on output.
2896 --Start of bug#8510399
2897 -- Bug#9253974 : Write to temporary table if PAYE aggregations is set
2898 If l_per_record.aggregate_flag <> 'Y' then
2899 reject_record(l_per_record, l_m34_rec, l_msg, asg_record.assignment_number,asg_record.tax_ref);
2900 else
2901 person_err_status := TRUE;
2902 tb_write_paye_rec(l_count).tax_ref := asg_record.tax_ref;
2903 tb_write_paye_rec(l_count).per_record := l_per_record;
2904 tb_write_paye_rec(l_count).assignment_number := asg_record.assignment_number;
2905 tb_write_paye_rec(l_count).m34_rec := l_m34_rec;
2906 tb_write_paye_rec(l_count).err_msg := l_msg;
2907 l_count := l_count + 1;
2908 end if;
2909 -- end of bug#8510399
2910 end;
2911 ----------------------------
2912 -- End of Anonymous block --
2913 ----------------------------
2914 end loop;
2915 --Start of bug#8510399
2916 -- If any of aggregated assingment is failed, rollback the other aggregated
2917 -- assingment changes.
2918 -- Bug#9253974 : Write to temporary table if PAYE aggregations is set
2919 If l_per_record.aggregate_flag = 'Y' then
2920 if person_err_status = TRUE then
2921 ROLLBACK TO rollback_per_PAYE;
2922 For i in 1..tb_write_paye_rec.count
2923 LOOP
2924 reject_record(tb_write_paye_rec(i).per_record,
2925 tb_write_paye_rec(i).m34_rec,
2926 NVL(tb_write_paye_rec(i).err_msg,err_agg_asg),
2927 tb_write_paye_rec(i).assignment_number,
2928 tb_write_paye_rec(i).tax_ref);
2929 END LOOP;
2930 else
2931 For i in 1..tb_write_paye_rec.count
2932 LOOP
2933 write_Tax_Ref(tb_write_paye_rec(i).tax_ref,
2934 tb_write_paye_rec(i).status);
2935
2936 write_body(tb_write_paye_rec(i).old_paye_rec,
2937 tb_write_paye_rec(i).paye_rec,
2938 tb_write_paye_rec(i).per_record,
2939 tb_write_paye_rec(i).assignment_number,
2940 tb_write_paye_rec(i).dir,
2941 tb_write_paye_rec(i).effective_date,
2942 tb_write_paye_rec(i).assignment_id,
2943 tb_write_paye_rec(i).tax_ref);
2944
2945 END LOOP;
2946 end if;
2947 end if;
2948 -- Deinitialization
2949 tb_write_paye_rec.DELETE;
2950 -- end of bug#8510399
2951
2952 -- Need to do a commit here
2953 if g_validate_only ='N' then /*Added soy 08-09*/
2954 hr_utility.trace(' In Validate And Commit Mode therefore Commiting.');
2955 if check_commit then
2956 commit;
2957 end if;
2958 end if;
2959 EXCEPTION
2960 WHEN ex_edi_error then
2961 -- Bug#8497477 : Passed tax ref to display on output.
2962 reject_record(null, l_m34_rec, l_msg, null,l_paye_ref);
2963 WHEN ex_resume_mode then
2964 null;
2965 END process_record;
2966
2967 ---------------------------------------------------------------------
2968 -- NAME --
2969 -- pyudet.run_process PUBLIC PROCEDURE --
2970 -- --
2971 -- DESCRIPTION --
2972 -- The main procedure called from the SRS screen. The success or --
2973 -- or failure of the process is passed back to the SRS screen in --
2974 -- the retcode paramater --
2975 ---------------------------------------------------------------------
2976 PROCEDURE run_process(errbuf out nocopy varchar2,
2977 retcode out nocopy varchar2,
2978 p_request_id in number default null,
2979 p_mode in number,
2980 p_effective_date in date,
2981 p_business_group_id in number,
2982 p_payroll_id in number,
2983 p_authority in varchar2 default null,
2984 p_p6_request_id in number default null,
2985 p_validate_only in VARCHAR2 DEFAULT 'GB_VALIDATE_COMMIT')
2986 IS
2987 l_m34_rec g_tax_code_interface;
2988 l_m12_rec g_typ_per_record;
2989 l_person_id per_all_people_f.person_id%type;
2990 l_process boolean;
2991 BEGIN
2992 --hr_utility.trace_on(null,'hello');
2993 hr_utility.trace('Start of PYUDET');
2994 --------------------------------
2995 -- Setting up GLOBAL variable --
2996 --------------------------------
2997 set_global(p_request_id,p_mode,p_effective_date,p_business_group_id,
2998 p_payroll_id,p_authority,p_p6_request_id,p_validate_only );
2999
3000 -- Bug#9253974: fetch "HR : GB Override SOY Authority" profile value
3001 fnd_profile.get('GB_OVERRIDE_SOY', g_SOY_override_profile);
3002
3003 --------------------------------
3004 -- Start the process --
3005 --------------------------------
3006 hr_utility.trace('Open main cursor');
3007 if g_mode in (1,2) then
3008 open csr_mode12;
3009 elsif g_mode in (3,4) then
3010 open csr_mode34;
3011 end if;
3012 ------------------
3013 -- Write header --
3014 ------------------
3015 hr_utility.trace('Write report header');
3016 write_header;
3017 hr_utility.trace('Write sub-report header');
3018 write_group_header;
3019
3020 -- Start the loop
3021 loop
3022 hr_utility.trace('In the main loop');
3023 -- Set l_process to true
3024 l_process := true;
3025 --
3026 if g_mode in(1,2) then
3027 fetch csr_mode12 into l_m12_rec;
3028 exit when csr_mode12%notfound;
3029 -- if the current person = the prev person, don't process
3030 if l_person_id = l_m12_rec.person_id then
3031 l_process := false;
3032 end if;
3033 -- copy the person id
3034 l_person_id := l_m12_rec.person_id;
3035 elsif g_mode in (3,4) then
3036 fetch csr_mode34 into l_m34_rec;
3037 exit when csr_mode34%notfound;
3038 end if;
3039 -- Process assignment
3040 if l_process then
3041 process_record(l_m12_rec,l_m34_rec);
3042 end if;
3043 end loop;
3044 ------------------
3045 -- Write footer --
3046 ------------------
3047 hr_utility.trace('Write report footer');
3048 write_footer;
3049
3050 -- Clear out the Tax Code Interface table
3051 if g_mode in (3,4) then
3052 hr_utility.trace('Clearing tax code interface');
3053 delete pay_gb_tax_code_interface
3054 where (request_id is null or request_id = g_p6_request_id)
3055 and processed_flag = 'P'; /*Added soy 08-09*/
3056 commit;
3057 end if;
3058 EXCEPTION
3059 WHEN Others THEN
3060 hr_utility.trace('Error occurs : ' || sqlerrm);
3061 if g_mode in (1,2) then
3062 if csr_mode12%isopen then
3063 close csr_mode12;
3064 end if;
3065 elsif g_mode in (3,4) then
3066 if csr_mode34%isopen then
3067 close csr_mode34;
3068 end if;
3069 end if;
3070 rollback;
3071 errbuf := sqlerrm;
3072 retcode := 2;
3073 raise;
3074 END run_process;
3075
3076 ---------------------------------------------------------------------
3077 -- Instantiation Section --
3078 -- --
3079 -- This section will be executed the first time that the package --
3080 -- is brought into memory for this session --
3081 ---------------------------------------------------------------------
3082 BEGIN
3083 hr_utility.set_location('pyudet',0);
3084 -------------------------------------------------------------------
3085 -- Initialize the count variable for the 'uplift_value' function --
3086 -------------------------------------------------------------------
3087 g_uplift_value(0) := -1;
3088 --
3089 hr_utility.set_location('pyudet',999);
3090 END;
[Home] [Help]