1 PACKAGE BODY PAY_P45_PKG as
2 /* $Header: payrp45.pkb 120.29.12010000.8 2008/09/18 14:26:14 rlingama ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================
8 Name
9 PAY_P45_PKG
10 Purpose
11 Supports the P45 form (PAYWSR45) called from the form PAYGBTAX.
12 This is a UK Specific payroll form/package.
13 Notes
14
15 History
16 07-AUG-94 P.Shergil 40.0 Date Created.
17 29-AUG-94 H.Minton 40.1 Added Function to get the formula id.
18 04-OCT-94 R.Fine 40.2 Renamed package to start PAY_
19 07-OCT-94 A.Snell 40.3 Fixed cursors c_act and c_query to access
20 info date effectively
21 04-NOV-94 A.Snell 40.4 Fix to problem where leaver had restarted
22 and hence had 2 periods of service
23 24-NOV-94 R.Fine 40.5 Suppressed index on business_group_id
24 15-DEC-94 A.Snell 40.6 Fix to Taxable Pay subquery which wasn't
25 correlated to the assignment_id
26 04-JAN-94 A.Snell 40.7 Fix to identify the correct tax details
27 for the payroll the assignment is on
28 using the tax reference on the payroll scl
29 05-MAY-95 M.Roychowdhury 40.8 Changed to use explicit cursors
30 and added error message for missing formula
31 26-JUL-96 C.Barbieri 40.9 Changed dates definition for Y2000.
32 13-NOV-96 T.Inekuku 40.12 Cleared variables to remove previously
33 assigned value.
34 11-DEC-96 C.Barbieri 40.13 Bug: 429163. Changed employer_addr cursor.
35 The parameter ASSIGNMENT_ID is now
36 L_ASSIGNMENT_ID
37 28-AUG-97 A.Mills 110.1 Altered date format for NLS compliance
38 24-SEP-98 A.Parkes 110.2 659488 Changes to get_ff_data: c_ptp now
39 uses last_standard_process_date in
40 preference to session_date, and c_act now
41 searches for PAYE or Taxable Pay balances
42 27-APR-98 115.0 Initial checkin using uppsa from
43 revision 110.1
44 24-SEP-98 115.1 Changes to get_ff_data : c_ptpt now uses
45 last_standard_process_date in preference to session_date, and c_act now searches for
46 PAYE or Taxable Pay Balances
47 08-APR-99 djeng 115.2 Flexi date/multi radix compliance
48 08-APR-99 djeng 115.3 Flex date compliance
49 13-JUL-99 S.Robinson 115.4 Corrected canonical_to_date references
50 08-MAR-2000 J. Moyano 115.5 Function get_student_loan_flag added.
51 14-AUG-2000 A.Parkes 115.6 Added P45 Archiver hooks and globals.
52 Corrected flexdate usage in
53 get_student_loan_flag function (1531071)
54 17-JAN-2001 A.Parkes 115.7 Changed spawn_reports to check for errored
55 asg actions and to cater for Defer
56 Printing parameter.
57 09-FEB-2001 S.Robinson 115.8 Add pop_term_asg_lvl_from_archive and
58 pop_term_pact_lvl_from_archive, called
59 from P45 Form.
60 19-FEB-2001 A.Parkes 115.9 Added get_report_request_error func.
61 26-FEB-2001 A.Parkes 115.10 Altered EDI validation for pay and tax.
62 06-MAR-2001 A.Parkes 115.11 Added missing field EDI validation.
63 28-MAR-2001 A.Parkes 115.12 842703 removed restriction with
64 x_last_process_date in c_act in get_ff_data
65 29-MAR-2001 A.Parkes 115.13 842703 added restriction with new param
66 X_TRANSFER_DATE in c_act in get_ff_data
67 26-APR-2001 S.Robinson 115.14 Amendments to pop_term_asg_from_archive
68 and pop_term_pact_from_archive.
69 15-NOV-2001 K.Thampan 115.15 1926604 Added a condition in where clause
70 of cursor csr_assignments not to pick
71 any assignment with tax code NI
72 02-JAN-2002 S.Robinson 115.18 2149144. Changed EDI validation for
73 forenames from FULL_EDI to EDI_SURNAME.
74 17-FEB-2002 S.Robinson 115.19 2228063. Change to
75 pop_term_asg_from_archive to populate
76 pay and tax values correctly.
77 26-FEB-2002 K.Thampan 115.20 Bug 2233521 - add X_STUDENT_LOAN_FLAG param
78 to procedure pop_term_asg_from_archive and
79 change the format of date_of_leaving_yy to
80 be 4 chars long.
81 01-MAR-2002 S.Robinson 115.21 Change for Positive Offsets, retrieve
82 period number for archiving to use Payroll
83 Actions date earned instead of effective
84 date.
85 01-MAR-2002 S.Robinson 115.22 Change for Positive Offsets. Ensure that
86 assignments are not picked up before
87 the regular payment date of the last
88 period.
89 14-MAR-2002 S.Robinson 115.23 utf8 change on person_address.
90 21-MAR-2002 S.Robinson 115.24 Change floor statements to trunc in
91 pop_term_asg_from_archive so negative
92 values retieved from archive correctly.
93 Bug 2264307.
94 19-JUL-2002 R.Makhija 115.25 Changed archive_code procedure to look
95 at PAYE element run results for
96 statutory details first
97 22-AUG-2002 M.Ahmad 115.26 Concatenated middle_name with 12/26/2007 to
98 display the middle name with the first name
99 in the report.Bug 1690902.
100 11-SEP-2002 G.Butler 115.27 Bug 2264261 - truncate employer name and
101 address retrieved from hr_organization_
102 information in get_employer_address
103 to maximum limits set on Org Developer DF.
104 Set employer name and
105 address to default to null instead of 'B'
106 if archived name and address are null in
107 pop_term_pact_from_archive. Added
108 ORDERED optimiser hint into csr_assignments
109 in arch_act_creation procedure
110 12-Dec-2002 A.Mills 115.28 Added nocopy via utility.
111 31-MAR-2003 ASENGAR 115.29 2702298. Changed EDI validation for
112 Address lines from FULL_EDI to EDI_SURNAME.
113 21-JUL-2003 A.Mills 115.30 Aggregated PAYE changes. Rewrite of
114 arch_act_creation for new design.
115 18-SEP-2003 A.Mills 115.31 3147030. Fixed date issues in get_ff
116 _data and get_data functions.
117 14-OCT-2003 A.Mills 115.32 3096225. Do not return assignment end
118 dates if not terminated (c_query cursor)
119 12-DEC-2003 ASENGAR 115.33 BUG 3221422 Changed Query of CURSOR c_act of procedure
120 get_ff_data for improving performance
121 07-JAN-2004 A.Mills 115.34 3324547. Changed the get_ff_data function,
122 introduced cursors get_latest_id and
123 taxable_or_paye_exists. Also converted
124 asg effective end date to canonical.
125 27-JAN-2004 A.Mills 115.35 3396687. Added Suspended Assignments to check
126 of last assignment before termination.
127 09-FEB-2004 A.Mills 115.36 3433915. Also added suspended assignments to
128 future_active_exists to remedy issue.
129 17-FEB-2004 A.Mills 115.37 Change to payment_made function. Plus use
130 fnd_file calls to place non-p45s in log.
131 3452081.
132 03-MAR-2004 A.Mills 115.38 3473274. For 1st period of new starter, the
133 c_act query raises exception, handle this in
134 code.
135 07-JUN-2004 A.Mills 115.39 Performance enhancements, added range_person_on
136 and allowed range_code to restrict by payroll.
137 01-JUL-2004 K.Thampan 115.40 3681719 - fix cursor csr_transfer
138 14-JUL-2004 A.Mills 115.41 3765485. Fix for positive offset payrolls.
139 31-DEC-2004 K.Thampan 115.42 4055003. Fix procedure p45_existing_actions.
140 14-JAN-2005 K.Thampan 115.43 4120227. Fix function p45_existing_actions.
141 22-FEB-2005 A.Tiwari 115.44 4136320. Cursor C_act changed to query only within the TY
142 And payroll_details are queried for the latest assact
143 24-FEB-2005 K.Thampan 115.45 4169681. Performance fix.
144 26-APR-2005 K.Thampan 115.46 1934837. Archive COUNTRY
145 28-JUL-2005 K.Thampan 115.47 4522272. Change cursor csr_max_run_result to
146 only query within the TY.
147 26-AUG-2005 K.Thampan 115.48 4545963. Amend the archive process to default
148 the tax details with value from PAYE element
149 entries before checking/fetching the latest
150 PAYE run results.
151 08-SEP-2005 K.Thampan 115.49 Amended archive process procedure to use the same
152 procedure as EOY when fetching tax details. Also
153 amend procedure get_ff_data to fetch master
154 assignment_action_id when get_lastest_id return
155 nothing.
156 12-SEP-2005 K.Thampan 115.50 4595939. Change get_tax_details to fetch the
157 latest paye details.
158 16-SEP-2005 K.Thampan 115.51 4553334. Amended cursor agg_latest_action.
159 03-OCT-2005 T.Kumar 115.52 GSCC Corrections : Bug 4646368
160 14-OCT-2005 npershad 115.53 4428406. Removed reference to redundant index
161 PAY_ASSIGNMENT_ACTIONS_N1 used in hints.
162 23-DEC-2005 K.Thampan 115.54 4904738. Amended cursor csr_run_result not
163 to multiply prev pay and prev tax by 100.
164 30-JAN-2006 K.Thampan 115.55 4774622. Fix performance bug.
165 14-MAR-2006 K.Thampan 115.56 5042824. Fix performance bug.
166 23-MAY-2006 K.Thampan 115.57 5202965. Fix performance bug.
167 115.58 Amend the action creation cursor.
168 30-AUG-2006 K.Thampan 115.59 Amend procedure arch_act_creation to
169 only check for a final payment for the last
170 assignment to be ended (aggreated).
171 04-SEP-2006 ajeyam 115.61 New proc/functions created to find whether P45
172 issued (or) not for the given assignment
173 Bug 5144323
174 05-SEP-2006 ajeyam 115.62 Parameters added/changed for new report-
175 show the p45 issued for act asgs 5144323
176 13-OCT-2006 rmakhija 115.63 Bug 5478073, changed get_ff_data to
177 return dummy last assignment action id if
178 it is in pervious tax year and added
179 csr_get_term_period_no to archive
180 and report termination period no in this
181 case.
182 Updated csr_range_format_param to get
183 value for RANGE_PERSON_ID parameter only.
184 Changed c_act cursor in get_ff_data to also
185 fetch payroll actions that are after end of
186 the tax year in which assignemnt has been
187 terminated.
188 Updated get_p45_agg_asg_action_id to add
189 another check to return only those P45
190 action which belongs to an assignment that
191 exists in the aggregation period and is
192 terminated on same PAYE Ref as the given
193 assignment.
194 Updated get_p45_asg_action_id to ignore
195 transfer P45 actions.
196 Updated cursor agg_latest_action in
197 get_ff_data to get aggregated
198 final action from the aggregation period
199 only and also added the check to limit
200 select to aggregated assignments that
201 share same continuous active period of
202 employment.
203 Replaced manual_issue_exists and
204 p45_existing_action functions with a
205 call to return_p45_issued_flag function
206 in arch_act_creation for non-transfer
207 cases.
208 Updated csr_person_agg_asg in arcs_act_creation
209 to fetch only those assignment that share
210 continuous active period of employment and
211 exist within the same aggregation period
212 and replace check to ensure the assignments
213 that had been on the same PAYE Ref at some
214 point in time with a check to ensure the same
215 at the time of termination.
216 Updated arch_act_creation to archive ids
217 of included assignments
218 Updated archive_code to archive final payment
219 action id.
220 Updated archive_code to get balances as at
221 latest action regardless of LSP or final close.
222 06-NOV-2006 rmakhija 115.64 Bug 5144323, Updated get_p45_asg_action_id to
223 ignore transfer P45 actions
224 06-NOV-2006 rmakhija 115.65 Fixed dbdrv line
225 07-NOV-2006 rmakhija 115.66 Updated aggregation period check subquery in
226 csr_person_agg_asg cursor
227 21-NOV-2006 ajeyam 115.67 Code added to check all the transfer payroll
228 actions when we return P45 payroll action.
229 In get_p45_asg_action_id procedure.
230 03-JAN-2007 rmakhija 115.68 Bug 5743581, added to_char to
231 csr_get_final_payment cursor in
232 get_p45_agg_asg_action_id
233 07-Feb-2007 rmakhija 115.69 Bug 5869769, removed sql to get final
234 payment date in get_p45_agg_asg_action_id
235 and updated agg_latest_action cursor to
236 imrove performance.
237 09-Mar-2007 rmakhija 115.69 Bug 5923552, Changed csr_all_assignments
238 and csr_all_assignments_range to use
239 assignment's effective end date instead
240 of actual termination date to ensure
241 tax ref transfers are handled correctly
242 25-Sep-2007 rlingama 115.70 Bug 5671777-2 Update pay_p45_pkg.get_p45_agg_asg_action_id
243 procedure to first look for a P45 for another assignment
244 that included the given assignment using
245 X_P45_INCLUDED_ASSIGNMENT user entity.
246 Bug 5671777-11 validation added in the p45 process to
247 ensure it is run for one tax year at a time
248 16-Nov-2007 parusia 115.71 Archived 2 additional items - X_DATE_OF_BIRTH
249 and X_SEX. Changed range_cursor to throw unhandled
250 exception when TestSubmssion is Yes but TestID
251 is not provided For P45PT1. Bug 6345375.
252 26-Dec-2007 apmishra 115.72 Added the fix for the first name not to appear with a
253 space concated with the middle name Bug:6710229
254 EOY 07-08 P45 PT1: P45PT1 PROCESS ERRORED OUT IF THE MIDDLE NAME IS PROVIDED.
255 4-Jan-2007 parusia 115.73 Archive middle_name separately from first_name.
256 Bug 6710229
257 16-Jan-2007 rlingama 115.75 Modified csr_get_paye_ref to csr_get_p45_another_asg in
258 get_p45_agg_asg_action_id cursor
259 28-Feb-2007 pbalu 115.76 Added to_char in csr_get_p45_another_asg in
260 get_p45_agg_asg_action_id cursor.
261 31-Mar-2008 rlingama 115.77 Bug 6900025 Modified max effective end date of cursor csr_paye_details to
262 final process date in get_tax_details procedure to report correct tax code.
263 01-Apr-2008 rlingama 115.78 Bug 6900025 Added final process date check in get_tax_details procedure
264 03-Apr-2008 rlingama 115.79 Bug 6900025 Modified pact.effective_date condtion in get_tax_details procedure to
265 fetch PAYE details from run results instead of element entries.
266 Reverted the fix did in 115.77 and 78 versions.
267 02-May-2008 rlingama 115.80 Bug 6900025 modified to_date function to add_months in get_tax_details procedure
268 14-May-2008 rlingama 115.81 Bug 7028893.Added function PAYE_RETURN_P45_ISSUED_FLAG.
269 15-Sep-2008 rlingama 115.82 Bug 7410767.Modified the p_eff_date date check in get_tax_detail procedure.
270 ==============================================================================*/
271
272
273 -- Globals
274 g_package CONSTANT VARCHAR2(20):= 'pay_p45_pkg.';
275 g_asg_creation_cache_populated BOOLEAN := FALSE;
276 g_asg_process_cache_populated BOOLEAN := FALSE;
277 g_fnd_rep_request_msg VARCHAR2(2000):=' ';
278 -- SRS Params
279 g_payroll_id pay_payrolls_f.payroll_id%TYPE;
280 g_start_date DATE;
281 g_effective_date DATE;
282 g_end_date DATE;
283 g_business_group_id hr_organization_units.business_group_id%TYPE;
284 g_do_edi_validation BOOLEAN;
285 g_tax_ref VARCHAR2(20);
286 -- User Entity Ids
287 g_address_line1_eid ff_user_entities.user_entity_id%TYPE;
288 g_address_line2_eid ff_user_entities.user_entity_id%TYPE;
289 g_address_line3_eid ff_user_entities.user_entity_id%TYPE;
290 g_assignment_number_eid ff_user_entities.user_entity_id%TYPE;
291 g_county_eid ff_user_entities.user_entity_id%TYPE;
292 g_deceased_flag_eid ff_user_entities.user_entity_id%TYPE;
293 g_first_name_eid ff_user_entities.user_entity_id%TYPE;
294 g_middle_name_eid ff_user_entities.user_entity_id%TYPE; /*Bug 6710229*/
295 g_issue_date_eid ff_user_entities.user_entity_id%TYPE;
296 g_last_name_eid ff_user_entities.user_entity_id%TYPE;
297 g_month_number_eid ff_user_entities.user_entity_id%TYPE;
298 g_ni_number_eid ff_user_entities.user_entity_id%TYPE;
299 g_organization_name_eid ff_user_entities.user_entity_id%TYPE;
300 g_payroll_id_eid ff_user_entities.user_entity_id%TYPE;
301 g_postal_code_eid ff_user_entities.user_entity_id%TYPE;
302 g_prev_tax_paid_eid ff_user_entities.user_entity_id%TYPE;
303 g_prev_taxable_pay_eid ff_user_entities.user_entity_id%TYPE;
304 g_student_loan_flag_eid ff_user_entities.user_entity_id%TYPE;
305 g_aggregated_paye_flag_eid ff_user_entities.user_entity_id%TYPE;
306 g_period_of_service_eid ff_user_entities.user_entity_id%TYPE;
307 g_effective_end_date_eid ff_user_entities.user_entity_id%TYPE;
308 g_tax_code_eid ff_user_entities.user_entity_id%TYPE;
309 g_tax_paid_eid ff_user_entities.user_entity_id%TYPE;
310 g_tax_ref_transfer_eid ff_user_entities.user_entity_id%TYPE;
311 g_taxable_pay_eid ff_user_entities.user_entity_id%TYPE;
312 g_termination_date_eid ff_user_entities.user_entity_id%TYPE;
313 g_title_eid ff_user_entities.user_entity_id%TYPE;
314 g_town_or_city_eid ff_user_entities.user_entity_id%TYPE;
315 g_w1_m1_indicator_eid ff_user_entities.user_entity_id%TYPE;
316 g_week_number_eid ff_user_entities.user_entity_id%TYPE;
317 g_country_eid ff_user_entities.user_entity_id%TYPE;
318 g_p45_final_action ff_user_entities.user_entity_id%TYPE;
319 g_p45_inc_assignment ff_user_entities.user_entity_id%TYPE;
320 -- Added for P45PT1. Bug 6345375
321 g_date_of_birth_eid ff_user_entities.user_entity_id%TYPE;
322 g_sex_eid ff_user_entities.user_entity_id%TYPE;
323 -- Seed data IDs
324 g_paye_details_id pay_element_types_f.element_type_id%TYPE;
325 --
326 FUNCTION get_report_request_error RETURN VARCHAR2 IS
327 BEGIN
328 RETURN g_fnd_rep_request_msg;
329 END get_report_request_error;
330 --
331 --------------------------------------------------------------------------
332 -- FUNCTION override_date
333 -- DESCRIPTION Get the override date if one exists
334 --------------------------------------------------------------------------
335 FUNCTION override_date(p_assignment_id in number) RETURN DATE IS
336 --
337 l_override_date date;
338 cursor csr_override_date (c_assignment_id in number) is
339 select fnd_date.canonical_to_date(aei.aei_information4)
340 from per_assignment_extra_info aei
341 where aei.assignment_id = c_assignment_id
342 and aei.information_type = 'GB_P45';
343 --
344 BEGIN
345 open csr_override_date(p_assignment_id);
346 fetch csr_override_date into l_override_date;
347 if csr_override_date%NOTFOUND then
348 l_override_date := null;
349 end if;
350 close csr_override_date;
351 --
352 RETURN l_override_date;
353 END override_date;
354 -----------------------------------------------------------------------------
355 -- Name --
356 -- get_p45_formula_id --
357 -- Purpose --
358 -- this function finds the formula id for the validation of the PAYE --
359 -- tax_code element entry value.
360 -----------------------------------------------------------------------------
361 --
362 FUNCTION get_p45_formula_id RETURN NUMBER IS
363
364 cursor c_formula is
365 select f.FORMULA_ID
366 from ff_formulas_f f,
367 ff_formula_types t
368 where t.FORMULA_TYPE_ID = f.FORMULA_TYPE_ID
369 and f.FORMULA_NAME = 'P45';
370 --
371 l_formula_id NUMBER;
372 --
373 BEGIN
374 --
375 open c_formula;
376 fetch c_formula into l_formula_id;
377 if c_formula%notfound then
378 --
379 close c_formula;
380 --
381 fnd_message.set_name ('FF', 'FFX03A_FORMULA_NOT_FOUND');
382 fnd_message.set_token ('1','P45');
383 fnd_message.raise_error;
384 --
385 end if;
386 close c_formula;
387 --
388 RETURN l_formula_id;
389 --
390 END get_p45_formula_id;
391 --
392 -----------------------------------------------------------------------------
393 --
394 -- Name --
395 -- get_student_loan_flag
396 -- Purpose --
397 -- this function finds if the employee has a Student Loan effective at --
398 -- the time employment ceases. Returns 'Y' if 'End Date' is not prior --
399 -- or equal to the termination date.
400 -----------------------------------------------------------------------------
401 --
402 FUNCTION get_student_loan_flag (p_assignment_id IN NUMBER,
403 p_termination_date IN DATE,
404 p_session_date IN DATE)
405 RETURN VARCHAR2
406 IS
407 --
408 cursor csr_getdate (x_assignment_id NUMBER,
409 x_termination_date DATE) IS
410 SELECT peev.screen_entry_value
411 FROM pay_element_types_f pet,
412 pay_element_links_f pel,
413 pay_element_entries_f pee,
414 pay_input_values_f piv,
415 pay_element_entry_values_f peev
416 WHERE pee.assignment_id = x_assignment_id
417 AND upper(pet.element_name) = 'STUDENT LOAN'
418 AND upper(piv.name) = 'END DATE'
419 AND pet.business_group_id IS NULL
420 AND pet.legislation_code = 'GB'
421 AND pet.element_type_id = pel.element_type_id
422 AND pel.element_link_id = pee.element_link_id
423 AND pet.element_type_id = piv.element_type_id
424 AND piv.input_value_id = peev.input_value_id
425 AND pee.element_entry_id = peev.element_entry_id
426 AND x_termination_date BETWEEN pet.effective_start_date
427 AND pet.effective_end_date
428 AND x_termination_date BETWEEN pel.effective_start_date
429 AND pel.effective_end_date
430 AND x_termination_date BETWEEN pee.effective_start_date
431 AND pee.effective_end_date
432 AND x_termination_date BETWEEN piv.effective_start_date
433 AND piv.effective_end_date
434 AND x_termination_date BETWEEN peev.effective_start_date
435 AND peev.effective_end_date;
436 --
437 l_end_date pay_element_entry_values_f.screen_entry_value%TYPE;
438 l_flag VARCHAR2(1);
439 l_term_date DATE;
440 l_found BOOLEAN;
441 --
442 BEGIN
443 --
444 --
445 l_flag := 'N';
446 --
447 l_term_date := nvl(p_termination_date,p_session_date);
448 OPEN csr_getdate(p_assignment_id, l_term_date);
449 FETCH csr_getdate INTO l_end_date;
450 l_found := csr_getdate%found;
451 CLOSE csr_getdate;
452 IF l_found THEN
453 IF ((l_end_date IS NULL) OR
454 (fnd_date.canonical_to_date(l_end_date) > l_term_date)) THEN
455 l_flag := 'Y';
456 hr_utility.trace('changing flag to: ' || l_flag);
457 hr_utility.trace('end date is <' || l_end_date || '>');
458 END IF;
459 ELSE
460 hr_utility.trace('no data found...');
461 NULL;
462 END IF;
463 hr_utility.trace('leaving function get_student_loan_flag...');
464 --
465 --
466 RETURN l_flag;
467 --
468 END get_student_loan_flag;
469
470 -----------------------------------------------------------------------------
471
472 procedure person_address(X_PERSON_ID in number,
473 X_SESSION_DATE in date,
474 X_ADDRESS_LINE1 in out nocopy varchar2,
475 X_ADDRESS_LINE2 in out nocopy varchar2,
476 X_ADDRESS_LINE3 in out nocopy varchar2,
477 X_TOWN_OR_CITY in out nocopy varchar2,
478 X_REGION_1 in out nocopy varchar2,
479 X_COUNTRY in out nocopy varchar2,
480 X_POSTAL_CODE in out nocopy varchar2) is
481
482 cursor c_addr is select addr.ADDRESS_LINE1,
483 addr.ADDRESS_LINE2,
484 addr.ADDRESS_LINE3,
485 addr.TOWN_OR_CITY,
486 addr.REGION_1,
487 addr.COUNTRY,
488 addr.POSTAL_CODE
489 from per_addresses addr
490 where addr.PERSON_ID = X_PERSON_ID
491 and addr.PRIMARY_FLAG = 'Y'
492 and X_SESSION_DATE between
493 addr.DATE_FROM and
494 nvl(addr.DATE_TO,fnd_date.canonical_to_date('4712/12/31'));
495
496 cursor get_country(p_code in varchar2) is
497 select ftv.territory_short_name
498 from fnd_territories_vl ftv
499 where ftv.territory_code = p_code;
500
501 l_addr c_addr%rowtype;
502 l_found boolean;
503 l_county per_addresses.region_1%type /*varchar2(30)*/;
504
505 begin
506 /* Clear the variables, as if no future assignment is made to them,
507 they may hold the previous value assigned
508 */
509 X_ADDRESS_LINE1 := '';
510 X_ADDRESS_LINE2 := '';
511 X_ADDRESS_LINE3 := '';
512 X_TOWN_OR_CITY := '';
513 X_COUNTRY := '';
514 X_POSTAL_CODE := '';
515 X_REGION_1 := '';
516 open c_addr;
517 fetch c_addr into l_addr;
518 l_found := c_addr%found;
519 close c_addr;
520 if l_found then
521 X_ADDRESS_LINE1 := l_addr.ADDRESS_LINE1;
522 X_ADDRESS_LINE2 := l_addr.ADDRESS_LINE2;
523 X_ADDRESS_LINE3 := l_addr.ADDRESS_LINE3;
524 X_TOWN_OR_CITY := l_addr.TOWN_OR_CITY;
525 -- X_COUNTRY := l_addr.COUNTRY;
526 X_POSTAL_CODE := l_addr.POSTAL_CODE;
527 l_county := l_addr.REGION_1;
528 open get_country(l_addr.COUNTRY);
529 fetch get_country into X_COUNTRY;
530 close get_country;
531 begin
532 SELECT substr(hr.meaning,1,30)
533 INTO X_REGION_1
534 FROM HR_LOOKUPS hr
535 WHERE hr.LOOKUP_CODE = l_county
536 AND hr.LOOKUP_TYPE = 'GB_COUNTY';
537 EXCEPTION WHEN NO_DATA_FOUND THEN
538 null;
539 END;
540 else
541 X_ADDRESS_LINE1 := '';
542 X_ADDRESS_LINE2 := '';
543 X_ADDRESS_LINE3 := '';
544 X_TOWN_OR_CITY := '';
545 X_COUNTRY := '';
546 X_POSTAL_CODE := '';
547 X_REGION_1 := '';
548 end if;
549 end person_address;
550
551 -- Fetch tax details
552 --
553 procedure get_tax_details(p_assignment_id in number,
554 p_paye_details_id in number,
555 p_paye_id in number,
556 p_eff_date in date,
557 p_tax_code out nocopy varchar2,
558 p_tax_basis out nocopy varchar2,
559 p_prev_pay out nocopy varchar2,
560 p_prev_tax out nocopy varchar2)
561 is
562 l_paye_rr_id number;
563 l_paye_details_rr_id number;
564 l_effective_date date; -- Bug 6900025 to store date earned value
565 l_start_year date; -- Bug 7410767 to store the start of financial year
566
567 -- Bug 6900025 added l_eff_date parameter to check proper financial year for fecting PAYE details.
568 CURSOR csr_max_run_result(l_element_id number,l_eff_date date) IS
569 SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
570 pact PAY_PAYROLL_ACTIONS_PK,
571 r2 PAY_RUN_RESULTS_N50)
572 USE_NL(assact2, pact, r2) */
573 to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
574 r2.run_result_id),17))
575 FROM pay_assignment_actions assact2,
576 pay_payroll_actions pact,
577 pay_run_results r2
578 WHERE assact2.assignment_id = p_assignment_id
579 AND r2.element_type_id+0 = l_element_id
580 AND r2.assignment_action_id = assact2.assignment_action_id
581 AND r2.status IN ('P', 'PA')
582 AND pact.payroll_action_id = assact2.payroll_action_id
583 AND pact.action_type IN ( 'Q','R','B','I')
584 AND assact2.action_status = 'C'
585 AND pact.effective_date between
586 -- Bug 6900025 Modified pact.effective_date condtion to fetch PAYE details from run results instead of element entries.
587 -- to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(p_eff_date,'YYYY'))),'DD-MM-YYYY')
588 -- and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(p_eff_date,'YYYY') + 1)),'DD-MM-YYYY')
589 to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(l_eff_date,'YYYY'))),'DD-MM-YYYY')
590 and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_eff_date,'YYYY') + 1)),'DD-MM-YYYY')
591 AND NOT EXISTS(
592 SELECT '1'
593 FROM pay_action_interlocks pai,
594 pay_assignment_actions assact3,
595 pay_payroll_actions pact3
596 WHERE pai.locked_action_id = assact2.assignment_action_id
597 AND pai.locking_action_id = assact3.assignment_action_id
598 AND pact3.payroll_action_id = assact3.payroll_action_id
599 AND pact3.action_type = 'V'
600 AND assact3.action_status = 'C');
601
602 CURSOR csr_run_result(l_run_result_id number,l_element_type_id number) IS
603 SELECT max(decode(name,'Tax Code',result_value,NULL)) tax_code,
604 max(decode(name,'Tax Basis',result_value,NULL)) tax_basis,
605 to_number(max(decode(name,'Pay Previous',
606 fnd_number.canonical_to_number(result_value),NULL)))
607 pay_previous,
608 to_number(max(decode(name,'Tax Previous',
609 fnd_number.canonical_to_number(result_value),NULL)))
610 tax_previous
611 FROM pay_input_values_f v,
612 pay_run_result_values rrv
613 WHERE rrv.run_result_id = l_run_result_id
614 AND v.input_value_id = rrv.input_value_id
615 AND v.element_type_id = l_element_type_id;
616
617 CURSOR csr_paye_details(p_assignment_id NUMBER) IS
618 SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
619 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
620 max(decode(iv.name,'Pay Previous',screen_entry_value)) pay_previous,
621 max(decode(iv.name,'Tax Previous',screen_entry_value)) tax_previous
622 FROM pay_element_entries_f e,
623 pay_element_entry_values_f v,
624 pay_input_values_f iv,
625 pay_element_links_f link
626 WHERE e.assignment_id = p_assignment_id
627 AND link.element_type_id = g_paye_details_id
628 AND e.element_link_id = link.element_link_id
629 AND e.element_entry_id = v.element_entry_id
630 AND iv.input_value_id = v.input_value_id
631 AND e.effective_end_date BETWEEN link.effective_start_date AND link.effective_end_date
632 AND e.effective_end_date BETWEEN iv.effective_start_date AND iv.effective_end_date
633 AND e.effective_end_date BETWEEN v.effective_start_date AND v.effective_end_date
634 AND e.effective_end_date = (select max(e1.effective_end_date)
635 from pay_element_entries_f e1,
636 pay_element_links_f link1
637 where link1.element_type_id = g_paye_details_id
638 and e1.assignment_id = p_assignment_id
639 and e1.element_link_id = link1.element_link_id);
640 begin
641 hr_utility.set_location('Entering get_tax_details',1);
642 hr_utility.trace('Assignemnt ID : ' || p_assignment_id);
643 hr_utility.trace('PAYE Details ID : ' || p_paye_details_id);
644 hr_utility.trace('PAYE ID : ' || p_paye_id);
645 hr_utility.trace('Effective Date : ' || p_eff_date);
646
647
648 --Bug 6900025 assigning proper date earned to l_effective_date
649 --Bug 7410767 Modified the p_eff_date check
650
651 --if fnd_number.canonical_to_number(to_char(p_eff_date,'DD')) >= 06
652 --and fnd_number.canonical_to_number(to_char(p_eff_date,'MM')) >= 04 then
653 l_start_year := to_date('06/04/'||to_char(p_eff_date,'YYYY'),'DD/MM/YYYY');
654 if to_number(p_eff_date - l_start_year) >= 0 then
655 l_effective_date := p_eff_date;
656 else
657 --l_effective_date := to_date(to_char(p_eff_date,'DD-MM')||to_char(fnd_number.canonical_to_number(to_char(p_eff_date,'YYYY') - 1)),'DD-MM-YYYY');
658 l_effective_date := add_months( p_eff_date,-12);
659 end if;
660
661 hr_utility.trace('l_effective_date value'||l_effective_date);
662 open csr_max_run_result(p_paye_id,l_effective_date);
663 fetch csr_max_run_result into l_paye_rr_id;
664 close csr_max_run_result;
665
666 open csr_max_run_result(p_paye_details_id,l_effective_date);
667 fetch csr_max_run_result into l_paye_details_rr_id;
668 close csr_max_run_result;
669
670 hr_utility.trace('Fetching run result 1');
671 -- 1. First we try to fetch it from the latest PAYE run results
672 open csr_run_result(l_paye_rr_id, p_paye_id);
673 fetch csr_run_result into p_tax_code,
674 p_tax_basis,
675 p_prev_pay,
676 p_prev_tax;
677 close csr_run_result;
678 -- 2. Tax code is not found, fetch from the latest PAYE Details run results
679 if p_tax_code is null then
680 hr_utility.trace('Fetching run result 2');
681 open csr_run_result(l_paye_details_rr_id, p_paye_details_id);
682 fetch csr_run_result into p_tax_code,
683 p_tax_basis,
684 p_prev_pay,
685 p_prev_tax;
686 close csr_run_result;
687
688 -- 3. Still not found, fetch the value from the PAYE
689 if p_tax_code is null then
690 hr_utility.trace('Fetching run result 3');
691 open csr_paye_details(p_assignment_id);
692 fetch csr_paye_details into p_tax_code,
693 p_tax_basis,
694 p_prev_pay,
695 p_prev_tax;
696 close csr_paye_details;
697 end if;
698 end if;
699 hr_utility.set_location('Leaving get_tax_details',999);
700 end;
701
702 procedure get_ff_data(X_SESSION_DATE in date,
703 X_ASSIGNMENT_ID in number,
704 X_ASSIGNMENT_END_DATE in date,
705 X_ASSIGNMENT_ACTION_ID in out nocopy number,
706 X_DATE_EARNED in out nocopy date,
707 X_PAYROLL_ACTION_ID in out nocopy number,
708 X_TRANSFER_DATE in date,
709 X_PERSON_ID in number,
710 X_TAX_REFERENCE in varchar2 default null) is
711 --
712 -- BUG 3221422 Changed Query for improving performance
713 -- 4136320 Querying for actions only in TY of ATD
714 cursor c_act is select act.assignment_action_id,
715 act.payroll_action_id,
716 pact.effective_date ,
717 pact.payroll_id
718 from pay_assignment_actions act,
719 pay_payroll_actions pact
720 where act.assignment_id = X_ASSIGNMENT_ID
721 and act.action_status = 'C'
722 and pact.payroll_action_id = act.payroll_action_id
723 and act.action_sequence = (
724 select /*+ ORDERED use_nl(a,pact2,t,r,v,f)
725 user_index(v, PAY_RUN_RESULT_VALUES_PK) */
726 max(a.action_sequence)
727 from pay_assignment_actions a
728 ,pay_payroll_actions pact2
729 ,pay_balance_types t
730 ,pay_balance_feeds_f f
731 ,pay_run_results r
732 ,pay_run_result_values v
733 where t.balance_name in ('Taxable Pay','PAYE')
734 and t.legislation_code = 'GB'
735 and f.balance_type_id = t.balance_type_id
736 and v.input_value_id = f.input_value_id
737 and v.run_result_id = r.run_result_id
738 and r.assignment_action_id = a.assignment_action_id
739 and a.payroll_action_id = pact2.payroll_action_id
740 and a.assignment_id = X_ASSIGNMENT_ID
741 and a.action_status = 'C'
742 and pact2.effective_date <= X_TRANSFER_DATE
743 and pact2.effective_date >=
744 to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(x_assignment_end_date,'YYYY'))),'DD-MM-YYYY')
745 and pact2.effective_date between f.effective_start_date and f.effective_end_date);
746
747 cursor c_ptp (taxable_update_payroll number, c_date_paid date) is
748 select min(PTP.start_date) start_date
749 from per_time_periods PTP
750 where PTP.payroll_id = taxable_update_payroll
751 and (PTP.REGULAR_PAYMENT_DATE ) >= (/*start of fyear prior to session date*/
752 to_date('06-04-'||
753 to_char(fnd_number.canonical_to_number(to_char(c_date_paid,'YYYY'))
754 + least(sign(c_date_paid - to_date('06-04-'
755 || to_char(c_date_paid,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY'));
756 --
757 cursor agg_latest_action (c_person_id in number,
758 c_effective_end_date in date,
759 c_tax_reference in varchar2,
760 c_agg_active_start in date,
761 c_agg_active_end in date) is
762 select /*+ ORDERED use_nl(flex pay ppa paa a)
763 INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
764 fnd_number.canonical_to_number(substr(max(
765 lpad(paa.action_sequence,15,'0')||
766 paa.assignment_action_id),16)) assignment_action_id
767 from per_all_assignments_f a,
768 pay_assignment_actions paa,
769 pay_payroll_actions ppa,
770 pay_all_payrolls_f pay,
771 hr_soft_coding_keyflex flex
772 where a.person_id = c_person_id
773 and paa.assignment_id = a.assignment_id
774 and ppa.payroll_action_id = paa.payroll_action_id
775 -- and pay.payroll_id = a.payroll_id
776 and pay.payroll_id = ppa.payroll_id
777 and flex.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
778 and flex.segment1 = c_tax_reference
779 and (paa.source_action_id is not null
780 or ppa.action_type in ('I','V','B'))
781 and ppa.effective_date <= c_effective_end_date
782 -- bug 4553334
783 -- and c_effective_end_date between a.effective_start_date and a.effective_end_date
784 -- and c_effective_end_date between pay.effective_start_date and pay.effective_end_date
785 -- 5144323: ensure payroll is on the same paye ref at the time of payroll action
786 AND ppa.effective_date between pay.effective_start_date and pay.effective_end_date
787 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
788 -- 5144323: Ensure the action belongs to an assignment that shares continuous active
789 -- period of employement with the given terminated assignment
790 AND a.effective_start_date = (SELECT /*+ ORDERED use_nl(past a2) */ max(a2.effective_start_date)
791 FROM per_all_assignments_f a2,
792 per_assignment_status_types past
793 WHERE a2.assignment_id = a.assignment_id
794 AND a2.effective_start_date <= c_agg_active_end
795 AND a2.effective_end_date >= c_agg_active_start
796 AND a2.assignment_status_type_id = past.assignment_status_type_id
797 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN'));
798
799 --
800 cursor get_latest_id (c_assignment_id IN NUMBER,
801 c_effective_date IN DATE) is
802 SELECT /*+ USE_NL(paa, ppa) */
803 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
804 paa.assignment_action_id),16))
805 FROM pay_assignment_actions paa,
806 pay_payroll_actions ppa
807 WHERE
808 paa.assignment_id = c_assignment_id
809 AND ppa.payroll_action_id = paa.payroll_action_id
810 AND (paa.source_action_id is not null
811 or ppa.action_type in ('I','V','B'))
812 AND ppa.effective_date <= c_effective_date
813 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
814
815 -- This cursor will fetch master assignment action id
816 -- Added to support upgrade from 11.0 to 11i.
817 cursor get_last_action(c_assignment_id IN NUMBER,
818 c_effective_date IN DATE) is
819 SELECT /*+ USE_NL(paa, ppa) */
820 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
821 paa.assignment_action_id),16))
822 FROM pay_assignment_actions paa,
823 pay_payroll_actions ppa
824 WHERE
825 paa.assignment_id = c_assignment_id
826 AND ppa.payroll_action_id = paa.payroll_action_id
827 AND ppa.effective_date <= c_effective_date
828 AND ppa.action_status = 'C'
829 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
830
831 --
832 cursor taxable_or_paye_exists (c_assignment_action_id in number) is
833 SELECT 'Y' FROM
834 sys.dual target where exists
835 (select 1
836 from PAY_BALANCE_FEEDS_F FEED
837 , PAY_BALANCE_TYPES PBT
838 , PAY_RUN_RESULT_VALUES PRRV
839 , PAY_RUN_RESULTS PRR
840 WHERE PBT.BALANCE_NAME in ('Taxable Pay', 'PAYE')
841 AND PBT.LEGISLATION_CODE = 'GB'
842 AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
843 AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
844 AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
845 AND PRRV.RESULT_VALUE IS NOT NULL
846 AND PRRV.RESULT_VALUE <> '0'
847 AND PRR.ASSIGNMENT_ACTION_ID = c_assignment_action_id);
848 --
849 cursor payroll_details(c_assignment_action_id in number) is
850 select paa.payroll_action_id,
851 ppa.effective_date, ppa.payroll_id
852 from pay_payroll_actions ppa,
853 pay_assignment_actions paa
854 where paa.assignment_action_id = c_assignment_action_id
855 and ppa.payroll_action_id = paa.payroll_action_id;
856 --
857 cursor agg_paye(c_person_id in number,
858 c_effective_date in date) is
859 select decode(p.per_information10,'Y','Y',NULL) agg_paye_flag
860 from per_all_people_f p
861 where p.person_id = c_person_id
862 and c_effective_date between
863 p.effective_start_date and p.effective_end_date;
864 --
865 CURSOR csr_get_term_tax_year_start IS
866 SELECT to_date('06-04-'||
867 to_char(fnd_number.canonical_to_number(to_char(X_ASSIGNMENT_END_DATE,'YYYY'))
868 + least(sign(X_ASSIGNMENT_END_DATE - to_date('06-04-'
869 || to_char(X_ASSIGNMENT_END_DATE,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
870 FROM dual;
871 --
872 CURSOR csr_get_action_tax_year_start (p_asg_action_id NUMBER) IS
873 SELECT to_date('06-04-'||
874 to_char(fnd_number.canonical_to_number(to_char(ptp.regular_payment_date,'YYYY'))
875 + least(sign(ptp.regular_payment_date - to_date('06-04-'
876 || to_char(ptp.regular_payment_date,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
877 FROM per_time_periods ptp, pay_assignment_actions act, pay_payroll_actions pact
878 WHERE act.assignment_action_id = p_asg_action_id
879 AND act.payroll_Action_id = pact.payroll_action_id
880 AND pact.time_period_id = ptp.time_period_id;
881 --
882 -- Cursor to find last date after assignment's termination date until which
883 -- aggregation flag has remained Y - assuming flag is Y at the termination date
884 CURSOR get_aggregation_end IS
885 SELECT nvl((min(effective_start_date)-1), hr_general.end_of_time) agg_end_date
886 FROM per_all_people_f
887 WHERE person_id = X_PERSON_ID
888 AND effective_start_date > X_ASSIGNMENT_END_DATE
889 AND nvl(per_information10, 'N') = 'N';
890 --
891 l_found boolean := FALSE;
892 l_tax_paye_exists varchar2(1);
893 l_taxable_update_action number;
894 l_taxable_update_date date;
895 l_taxable_update_payroll number;
896 l_payroll_year_start date;
897 l_ptp c_ptp%rowtype;
898 l_agg_paye_flag varchar2(1);
899 l_asg_action_id number;
900 l_latest_asg_action_id number;
901 l_effective_date date;
902 l_payroll_id number;
903 l_payroll_action_id number;
904 l_override_date date;
905 l_asg_action_exists boolean;
906 --
907 l_termination_ty_start date;
908 l_latest_action_ty_start date;
909 l_aggregation_end date;
910 l_agg_active_start date;
911 l_agg_active_end date;
912 begin
913 --
914 hr_utility.trace('DATE_EARN ' || x_date_earned);
915 hr_utility.trace('X_PERSON_ID ' || X_PERSON_ID);
916 hr_utility.trace('X_ASSIGNMENT_END_DATE ' || fnd_date.date_to_displaydate(X_ASSIGNMENT_END_DATE));
917 hr_utility.trace('X_TRANSFER_DATE ' || fnd_date.date_to_displaydate(X_TRANSFER_DATE));
918 l_asg_action_exists := FALSE;
919 open agg_paye(X_PERSON_ID, X_ASSIGNMENT_END_DATE);
920 fetch agg_paye into l_agg_paye_flag;
921 close agg_paye;
922 --
923 hr_utility.trace('Agg PAYE in ff data:'||l_agg_paye_flag);
924 hr_utility.trace(to_char(X_TRANSFER_DATE));
925 -- Use the tfr date as includes all processes for the assignment.
926 if nvl(l_agg_paye_flag,'X') = 'Y' then
927 -- 5144323: get_aggregation end date
928 OPEN get_aggregation_end;
929 FETCH get_aggregation_end INTO l_aggregation_end;
930 CLOSE get_aggregation_end;
931 hr_utility.trace('After get_aggregation_end, l_aggregation_end='||fnd_date.date_to_displaydate(l_aggregation_end));
932 l_agg_active_start := pay_gb_eoy_archive.get_agg_active_start(X_ASSIGNMENT_ID, X_TAX_REFERENCE, X_ASSIGNMENT_END_DATE);
933 l_agg_active_end := pay_gb_eoy_archive.get_agg_active_end(X_ASSIGNMENT_ID, X_TAX_REFERENCE, X_ASSIGNMENT_END_DATE);
934 --
935 -- 5144343: Get aggregated action within the aggregation period only
936 open agg_latest_action(X_PERSON_ID, least(X_TRANSFER_DATE, l_aggregation_end),
937 X_TAX_REFERENCE,
938 l_agg_active_start,
939 l_agg_active_end);
940 fetch agg_latest_action into l_asg_action_id;
941 --l_found := agg_latest_action%found;
942 close agg_latest_action;
943 l_found := FALSE;
944 if l_asg_action_id is not null then
945 l_found := TRUE;
946 end if;
947 hr_utility.trace('asg action for agg: '||to_char(l_asg_action_id));
948 open payroll_details(l_asg_action_id);
949 fetch payroll_details into l_payroll_action_id, l_effective_date,
950 l_payroll_id;
951 close payroll_details;
952 hr_utility.trace('asg action: '||to_char(l_asg_action_id));
953 --
954 else
955 -- NOT Aggregated PAYE
956 hr_utility.trace('Not aggregated so get latest action of asg');
957 --
958 open get_latest_id(X_ASSIGNMENT_ID, X_TRANSFER_DATE);
959 fetch get_latest_id into l_asg_action_id;
960 close get_latest_id;
961 --
962 if l_asg_action_id is not null then
963 hr_utility.trace('single asg action found: '||to_char(l_asg_action_id));
964 l_latest_asg_action_id := l_asg_action_id;
965 l_asg_action_exists := TRUE;
966 open taxable_or_paye_exists(l_asg_action_id);
967 fetch taxable_or_paye_exists into l_tax_paye_exists;
968 l_found := taxable_or_paye_exists%found;
969 IF l_found THEN
970 hr_utility.trace(' Non zero results found: TRUE');
971 ELSE
972 hr_utility.trace(' Non zero results found: FALSE');
973 END IF;
974 close taxable_or_paye_exists;
975 else
976 hr_utility.trace('No Master-Child action');
977 open get_last_action(X_ASSIGNMENT_ID, X_TRANSFER_DATE);
978 fetch get_last_action into l_asg_action_id;
979 close get_last_action;
980
981 if l_asg_action_id is not null then
982 l_found := TRUE;
983 end if;
984 end if;
985 --
986 if l_found then
987 -- Above 2 cursors found the last assignment action has
988 -- Taxable Pay or PAYE balances, so obtain payroll action details
989 hr_utility.trace('Asg action has paye or taxable pay');
990 open payroll_details(l_asg_action_id);
991 fetch payroll_details into l_payroll_action_id, l_effective_date,
992 l_payroll_id;
993 close payroll_details;
994
995 else
996 -- Above cursors did not find asg action that has Taxable
997 -- Pay or PAYE balances, so use less efficient cursor
998 -- to search for the last action that has PAYE or Taxable
999 -- Pay. Do this only if there was any asg action for the assignment.
1000 hr_utility.trace('Asg action has NO paye or taxable pay');
1001 IF l_asg_action_exists then
1002 BEGIN
1003 hr_utility.trace('Use c_act');
1004 open c_act;
1005 fetch c_act into l_asg_action_id, l_payroll_action_id,
1006 l_effective_date, l_payroll_id;
1007 l_found := c_act%found;
1008 close c_act;
1009 hr_utility.trace(l_asg_action_id);
1010 hr_utility.trace(l_payroll_action_id);
1011
1012 IF NOT(l_found) THEN -- 4136320: No actions with payments, so query the last run details
1013 open payroll_details( greatest(l_asg_action_id, l_latest_asg_action_id) );
1014 fetch payroll_details into l_payroll_action_id, l_effective_date,
1015 l_payroll_id;
1016 l_found := payroll_details%found;
1017 close payroll_details;
1018 END IF;
1019
1020 hr_utility.trace(l_asg_action_id);
1021 hr_utility.trace(l_payroll_action_id);
1022 hr_utility.trace(l_effective_date);
1023 hr_utility.trace(l_payroll_id);
1024 -- Handle exceptions in c_act, set found = false.
1025 EXCEPTION WHEN OTHERS THEN
1026 hr_utility.trace('c_act raised: '|| sqlerrm(sqlcode));
1027 l_found := FALSE;
1028 END;
1029 ELSE
1030 -- No prior action exists at all, so must set found to false.
1031 hr_utility.trace('No Asg Action found');
1032 l_found := FALSE;
1033 END IF;
1034 end if; -- use c_act cursor if more performant cursors do not find vals
1035 end if; -- Aggregated PAYE
1036 --
1037 -- The above cursors have set the local vars and if so then the l_found
1038 -- has been set in one of the above 3 places. Therefore set the out
1039 -- params as necessary. Otherwise, set the out params to -9999
1040 --
1041 if l_found then
1042 hr_utility.trace('Found');
1043 l_override_date := override_date(x_assignment_id);
1044 l_taxable_update_action := l_asg_action_id;
1045 l_taxable_update_payroll := l_payroll_id;
1046 x_payroll_action_id := l_payroll_action_id;
1047 --
1048 -- Bug 5478073: Get tax year start for termination and latest action
1049 OPEN csr_get_term_tax_year_start;
1050 FETCH csr_get_term_tax_year_start INTO l_termination_ty_start;
1051 CLOSE csr_get_term_tax_year_start;
1052 hr_utility.trace('After csr_get_term_tax_year_start, l_termination_ty_start='||fnd_date.date_to_displaydate(l_termination_ty_start));
1053 --
1054 OPEN csr_get_action_tax_year_start(l_asg_action_id);
1055 FETCH csr_get_action_tax_year_start INTO l_latest_action_ty_start;
1056 CLOSE csr_get_action_tax_year_start;
1057 hr_utility.trace('After csr_get_action_tax_year_start, l_latest_action_ty_start='||fnd_date.date_to_displaydate(l_latest_action_ty_start));
1058 --
1059 -- Bug 2332796. Use the least of date paid and override
1060 -- date to get the time period for EOY expiry check.
1061 --
1062 l_taxable_update_date :=
1063 least(l_effective_date,nvl(l_override_date,hr_general.end_of_time));
1064 open c_ptp(l_taxable_update_payroll,
1065 nvl(l_taxable_update_date, X_ASSIGNMENT_END_DATE));
1066 fetch c_ptp into l_ptp;
1067 l_found := c_ptp%found;
1068 close c_ptp;
1069
1070 if l_found then
1071 l_payroll_year_start := l_ptp.start_date;
1072 if l_payroll_year_start > l_taxable_update_date then
1073 x_assignment_action_id := -9999;
1074 x_date_earned := l_payroll_year_start;
1075 ------------------------------------------------------
1076 -- 5478073: above check is insuffucient hence added --
1077 -- following condition for tax year check --
1078 ------------------------------------------------------
1079 elsif l_latest_action_ty_start < l_termination_ty_start THEN
1080 x_assignment_action_id := -9999; -- to show 0 values on P45
1081 x_payroll_action_id := -9999; -- to show 0 values on P45
1082 x_date_earned := X_SESSION_DATE; -- get tax code/basis at issue date
1083 else
1084 x_assignment_action_id := l_taxable_update_action;
1085 x_date_earned := l_taxable_update_date;
1086 end if;
1087 else
1088 x_assignment_action_id := l_taxable_update_action;
1089 x_date_earned := l_taxable_update_date;
1090 end if;
1091
1092 else
1093 hr_utility.trace('Not found : ' || X_SESSION_DATE);
1094 l_taxable_update_date := NULL;
1095 X_DATE_EARNED := X_SESSION_DATE;
1096 X_ASSIGNMENT_ACTION_ID := -9999;
1097 x_payroll_action_id := -9999;
1098 end if;
1099 --
1100 exception when NO_DATA_FOUND then
1101 -- Set all variables the same as if l_found not true.
1102 l_taxable_update_date := NULL;
1103 X_DATE_EARNED := X_SESSION_DATE;
1104 X_ASSIGNMENT_ACTION_ID := -9999;
1105 x_payroll_action_id := -9999;
1106 end get_ff_data;
1107
1108
1109 procedure get_employer_address(X_ASSIGNMENT_ID in number,
1110 X_ASSIGNMENT_END_DATE in date,
1111 X_EMPLOYER_NAME in out nocopy varchar2,
1112 X_EMPLOYER_ADDRESS in out nocopy varchar2
1113 ) is
1114
1115 cursor employer_addr(L_ASSIGNMENT_ID number, c_assignment_end_date date) is
1116 select oi.ORG_INFORMATION3,
1117 oi.ORG_INFORMATION4,
1118 ass.ASSIGNMENT_ID
1119 from hr_organization_information oi,
1120 pay_payrolls_f roll,
1121 hr_soft_coding_keyflex flex,
1122 per_assignments_f ass,
1123 fnd_sessions sess
1124 where oi.ORG_INFORMATION_CONTEXT = 'Tax Details References'
1125 and roll.business_group_id + 0 = oi.organization_id
1126 /* normally P45 is for leaver so pick up data on the assignment_end_date */
1127 /* for non leavers eg. tax district change use the session date */
1128 and sess.SESSION_ID = userenv('sessionid')
1129 and nvl(c_assignment_end_date, sess.effective_date) between
1130 ass.effective_start_date and ass.effective_end_date
1131 and ass.payroll_id = roll.payroll_id
1132 and nvl(c_assignment_end_date, sess.effective_date) between
1133 roll.effective_start_date and roll.effective_end_date
1134 and ass.assignment_id = L_ASSIGNMENT_ID
1135 and flex.segment1 = oi.org_information1 /* same tax district */
1136 and flex.soft_coding_keyflex_id = roll.soft_coding_keyflex_id;
1137
1138 l_found boolean;
1139 l_employer_addr employer_addr%rowtype;
1140
1141 begin
1142
1143 open employer_addr(X_ASSIGNMENT_ID,X_ASSIGNMENT_END_DATE);
1144 fetch employer_addr into l_employer_addr;
1145 l_found := employer_addr%found;
1146 close employer_addr;
1147
1148 if l_found then
1149 -- truncate employer name and address to max limits allowed on Org Developer DF
1150 X_EMPLOYER_NAME := substr(l_employer_addr.ORG_INFORMATION3,1,36);
1151 X_EMPLOYER_ADDRESS := substr(l_employer_addr.ORG_INFORMATION4,1,60);
1152 else
1153 X_EMPLOYER_NAME := '';
1154 X_EMPLOYER_ADDRESS := '';
1155 end if;
1156
1157
1158 end;
1159
1160 procedure get_data(X_PERSON_ID in number,
1161 X_SESSION_DATE in date,
1162 X_ADDRESS_LINE1 in out nocopy varchar2,
1163 X_ADDRESS_LINE2 in out nocopy varchar2,
1164 X_ADDRESS_LINE3 in out nocopy varchar2,
1165 X_TOWN_OR_CITY in out nocopy varchar2,
1166 X_REGION_1 in out nocopy varchar2,
1167 X_COUNTRY in out nocopy varchar2,
1168 X_POSTAL_CODE in out nocopy varchar2,
1169 X_ASSIGNMENT_ID in number,
1170 X_ASSIGNMENT_ACTION_ID in out nocopy number,
1171 X_ASSIGNMENT_END_DATE in date,
1172 X_DATE_EARNED in out nocopy date,
1173 X_PAYROLL_ACTION_ID in out nocopy number,
1174 X_TRANSFER_DATE in date)
1175 is
1176 CURSOR csr_tax_ref (c_assignment_id in number,
1177 c_effective_end_date in date) is
1178 select scl.segment1
1179 from per_all_assignments_f paf,
1180 pay_all_payrolls_f ppf,
1181 hr_soft_coding_keyflex scl
1182 where paf.assignment_id = c_assignment_id
1183 and paf.payroll_id = ppf.payroll_id
1184 and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
1185 and c_effective_end_date between
1186 paf.effective_start_date and paf.effective_end_date
1187 and c_effective_end_date between
1188 ppf.effective_start_date and ppf.effective_end_date;
1189 --
1190 l_tax_reference varchar2(25);
1191 --
1192 begin
1193 --
1194 open csr_tax_ref(X_ASSIGNMENT_ID,X_ASSIGNMENT_END_DATE);
1195 fetch csr_tax_ref into l_tax_reference;
1196 close csr_tax_ref;
1197 --
1198 hr_utility.trace('Tax Ref: '||l_tax_reference);
1199 --
1200 person_address(X_PERSON_ID,
1201 X_SESSION_DATE,
1202 X_ADDRESS_LINE1,
1203 X_ADDRESS_LINE2,
1204 X_ADDRESS_LINE3,
1205 X_TOWN_OR_CITY,
1206 X_REGION_1,
1207 X_COUNTRY,
1208 X_POSTAL_CODE);
1209
1210
1211 get_ff_data(X_SESSION_DATE,
1212 X_ASSIGNMENT_ID,
1213 X_ASSIGNMENT_END_DATE,
1214 X_ASSIGNMENT_ACTION_ID,
1215 X_DATE_EARNED,
1216 X_PAYROLL_ACTION_ID,
1217 nvl(X_TRANSFER_DATE,hr_general.end_of_time),
1218 X_PERSON_ID,
1219 l_tax_reference);
1220
1221 end get_data;
1222
1223 procedure get_data(X_PERSON_ID in number,
1224 X_SESSION_DATE in date,
1225 X_ADDRESS_LINE1 in out nocopy varchar2,
1226 X_ADDRESS_LINE2 in out nocopy varchar2,
1227 X_ADDRESS_LINE3 in out nocopy varchar2,
1228 X_TOWN_OR_CITY in out nocopy varchar2,
1229 X_REGION_1 in out nocopy varchar2,
1230 X_COUNTRY in out nocopy varchar2,
1231 X_POSTAL_CODE in out nocopy varchar2,
1232 X_ASSIGNMENT_ID in number,
1233 X_ASSIGNMENT_ACTION_ID in out nocopy number,
1234 X_ASSIGNMENT_END_DATE in date,
1235 X_DATE_EARNED in out nocopy date,
1236 X_PAYROLL_ACTION_ID in out nocopy number,
1237 X_EMPLOYER_NAME in out nocopy varchar2,
1238 X_EMPLOYER_ADDRESS in out nocopy varchar2,
1239 X_TRANSFER_DATE in date)
1240 is
1241 CURSOR csr_tax_ref (c_assignment_id in number,
1242 c_effective_end_date in date) is
1243 select scl.segment1
1244 from per_all_assignments_f paf,
1245 pay_all_payrolls_f ppf,
1246 hr_soft_coding_keyflex scl
1247 where paf.assignment_id = c_assignment_id
1248 and paf.payroll_id = ppf.payroll_id
1249 and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
1250 and c_effective_end_date between
1251 paf.effective_start_date and paf.effective_end_date
1252 and c_effective_end_date between
1253 ppf.effective_start_date and ppf.effective_end_date;
1254 --
1255 l_tax_reference varchar2(25);
1256 --
1257 begin
1258 --
1259 open csr_tax_ref(X_ASSIGNMENT_ID,X_ASSIGNMENT_END_DATE);
1260 fetch csr_tax_ref into l_tax_reference;
1261 close csr_tax_ref;
1262 hr_utility.trace('Tax Ref: '||l_tax_reference);
1263 --
1264 person_address(X_PERSON_ID,
1265 X_SESSION_DATE,
1266 X_ADDRESS_LINE1,
1267 X_ADDRESS_LINE2,
1268 X_ADDRESS_LINE3,
1269 X_TOWN_OR_CITY,
1270 X_REGION_1,
1271 X_COUNTRY,
1272 X_POSTAL_CODE);
1273
1274
1275 get_ff_data(X_SESSION_DATE,
1276 X_ASSIGNMENT_ID,
1277 X_ASSIGNMENT_END_DATE,
1278 X_ASSIGNMENT_ACTION_ID,
1279 X_DATE_EARNED,
1280 X_PAYROLL_ACTION_ID,
1281 nvl(X_TRANSFER_DATE,hr_general.end_of_time),
1282 X_PERSON_ID, l_tax_reference);
1283
1284 get_employer_address(X_ASSIGNMENT_ID,
1285 X_ASSIGNMENT_END_DATE,
1286 X_EMPLOYER_NAME,
1287 X_EMPLOYER_ADDRESS);
1288
1289 end get_data;
1290
1291 procedure get_form_query_data(X_ASSIGNMENT_ID in number,
1292 X_LAST_NAME in out nocopy varchar2,
1293 X_TITLE in out nocopy varchar2,
1294 X_FIRST_NAME in out nocopy varchar2,
1295 X_NATIONAL_IDENTIFIER in out nocopy varchar2,
1296 X_PERSON_ID in out nocopy number,
1297 X_ACTUAL_TERMINATION_DATE in out nocopy date,
1298 X_DECEASED_FLAG in out nocopy varchar2,
1299 X_ASSIGNMENT_NUMBER in out nocopy varchar2,
1300 X_PAYROLL_ID in out nocopy number,
1301 X_ORGANIZATION_ID in out nocopy number,
1302 X_ORG_NAME in out nocopy varchar2) is
1303
1304 cursor c_query (p_assignment_id number) is
1305 select p.last_name
1306 , p.title
1307 , p.first_name
1308 , p.middle_names
1309 , p.person_id
1310 , p.national_identifier
1311 , serv.actual_termination_date
1312 , decode(serv.leaving_reason,'D','D',NULL) deceased_flag
1313 , a.assignment_number
1314 , a.payroll_id
1315 , org.organization_id
1316 , org.name org_name
1317 from per_all_people_f p
1318 , per_all_assignments_f a
1319 , per_periods_of_service serv
1320 , hr_all_organization_units org
1321 , fnd_sessions sess
1322 where a.assignment_id = p_assignment_id
1323 and sess.session_id = userenv('sessionid')
1324 and sess.effective_date between
1325 a.effective_start_date and a.effective_end_date
1326 and a.person_id = p.person_id
1327 and sess.effective_date between
1328 p.effective_start_date and p.effective_end_date
1329 and serv.person_id = p.person_id
1330 and serv.date_start = ( select max(s.date_start) from per_periods_of_service s
1331 where s.person_id = p.person_id
1332 and sess.effective_date >= s.date_start )
1333 and a.organization_id = org.organization_id;
1334
1335 --l_query c_query%rowtype;
1336
1337 begin
1338
1339 for l_query_rec in c_query(X_ASSIGNMENT_ID) loop
1340 X_LAST_NAME := l_query_rec.last_name;
1341 X_TITLE := l_query_rec.title;
1342 X_FIRST_NAME := SUBSTR(l_query_rec.first_name || ' ' ||
1343 l_query_rec.middle_names, 1, 150);
1344 X_NATIONAL_IDENTIFIER := l_query_rec.national_identifier;
1345 X_PERSON_ID := l_query_rec.person_id;
1346 X_ACTUAL_TERMINATION_DATE := l_query_rec.actual_termination_date;
1347 X_DECEASED_FLAG := l_query_rec.deceased_flag;
1348 X_ASSIGNMENT_NUMBER := l_query_rec.assignment_number;
1349 X_PAYROLL_ID := l_query_rec.payroll_id;
1350 X_ORGANIZATION_ID := l_query_rec.organization_id;
1351 X_ORG_NAME := l_query_rec.org_name;
1352 end loop;
1353
1354
1355 end;
1356 --
1357 PROCEDURE range_cursor (pactid IN NUMBER,
1358 sqlstr OUT NOCOPY VARCHAR2)
1359 IS
1360 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
1361 l_employers_name_eid ff_user_entities.user_entity_id%TYPE;
1362 l_employers_address_line_eid ff_user_entities.user_entity_id%TYPE;
1363 l_tax_district_name_eid ff_user_entities.user_entity_id%TYPE;
1364 -- vars for returns from the API:
1365 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
1366 l_ovn NUMBER;
1367 l_some_warning BOOLEAN;
1368 l_payroll_id NUMBER;
1369 l_chk_start_date DATE; -- BUG 5671777-11 to store start date
1370 l_chk_end_date DATE; -- BUG 5671777-11 to store end date
1371 p45_one_taxyear_error EXCEPTION; -- raised when P45 process is not fall in the
1372 -- same tax year BUG 5671777-11
1373 --
1374 l_test_indicator varchar2(1);
1375 l_test_id varchar2(8);
1376 l_report_type varchar2(15);
1377 l_report_category varchar2(15);
1378 test_indicator_error EXCEPTION;
1379 --
1380 cursor csr_user_entity(p_entity_name VARCHAR2) IS
1381 SELECT user_entity_id
1382 FROM ff_user_entities
1383 WHERE user_entity_name = p_entity_name
1384 AND legislation_code = 'GB'
1385 AND business_group_id IS NULL;
1386 --
1387 cursor csr_employer_details(p_payroll_action_id NUMBER) IS
1388 -- Select Employer details from Org DDF for specified Tax Ref, only
1389 -- if the action is for a P45 archive.
1390 SELECT
1391 substr(org.org_information3,1,36) employers_name,
1392 substr(org.org_information4,1,60) employers_address_line,
1393 substr(org.org_information2 ,1,40) tax_district_name
1394 FROM
1395 pay_payroll_actions ppa,
1396 hr_organization_information org
1397 WHERE ppa.payroll_action_id = p_payroll_action_id
1398 AND org.org_information_context = 'Tax Details References'
1399 AND NVL(org.org_information10,'UK') = 'UK'
1400 AND org.organization_id = ppa.business_group_id
1401 AND substr(ppa.legislative_parameters,
1402 instr(ppa.legislative_parameters,'TAX_REF=') + 8,
1403 instr(ppa.legislative_parameters||' ',' ',
1404 instr(ppa.legislative_parameters,'TAX_REF=')+8)
1405 - instr(ppa.legislative_parameters,'TAX_REF=') - 8)
1406 = org.org_information1
1407 AND ppa.report_category='P45';
1408 --
1409 -- TEST, TEST_ID, report_type and report_category added in cursor select
1410 -- for P45PT1. Bug 6345375
1411 cursor csr_get_payroll_param is
1412 select pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters) payroll_id,
1413 substr(pay_core_utils.get_parameter('TEST',legislative_parameters),1,1) test_indicator,
1414 trim(substr(pay_core_utils.get_parameter('TEST_ID',legislative_parameters),1,8)) test_id,
1415 report_type,
1416 report_category
1417 from pay_payroll_actions ppa
1418 where ppa.payroll_action_id = pactid;
1419 --
1420 -- Start of BUG 5671777-11
1421 --
1422 -- fetch start date and end date for the P45 process
1423 --
1424 CURSOR csr_get_p45_start_end_date(p_payroll_action_id NUMBER) IS
1425 SELECT
1426 start_date,
1427 fnd_date.canonical_to_date
1428 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1429 'DATE_TO')) end_date
1430 FROM pay_payroll_actions
1431 WHERE payroll_action_id = p_payroll_action_id;
1432
1433 --
1434 -- fetch start date and end date for the P45 EDI process
1435 --
1436
1437 CURSOR csr_get_p45_EDI_start_end_date(p_payroll_action_id NUMBER) IS
1438 SELECT
1439 fnd_date.canonical_to_date
1440 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1441 'DATE_FROM')) start_date,
1442 fnd_date.canonical_to_date
1443 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1444 'END_DATE')) end_date
1445 FROM pay_payroll_actions
1446 WHERE payroll_action_id = p_payroll_action_id;
1447 -- End of BUG 5671777-11
1448 --
1449 rec_employer_details csr_employer_details%ROWTYPE;
1450 --
1451 BEGIN
1452 --
1453 hr_utility.set_location('Entering: '||l_proc,1);
1454 --
1455 -- Get the parameter payroll_id, if this has been used.
1456 --
1457 -- Added for P45PT1. Bug 6345375
1458 open csr_get_payroll_param;
1459 fetch csr_get_payroll_param into l_payroll_id, l_test_indicator,l_test_id,l_report_type,l_report_category;
1460 if csr_get_payroll_param%NOTFOUND then
1461 l_payroll_id := null;
1462 end if;
1463 close csr_get_payroll_param;
1464 --
1465 -- Added for P45PT1. Bug 6345375
1466 -- Log the error in Log_File if Test_Indicator is Yes, but Test_ID is not provided
1467 -- and Raise an unhandled exception to fail the process.
1468 --
1469 IF l_report_type = 'P45PT1' and l_report_category = 'EDI' THEN
1470 IF (l_test_indicator = 'Y' AND l_test_id IS NULL) THEN
1471 fnd_file.put_line (fnd_file.LOG,'Error : Enter the Test ID as the EDI Test Indicator is Yes.');
1472 RAISE test_indicator_error;
1473 END IF;
1474 END IF;
1475 --
1476 hr_utility.trace('Payroll_ID: '||to_char(l_payroll_id));
1477 -- Return Range Cursor
1478 -- Note: There must be one and only one entry of :payroll_action_id in
1479 -- the string, and the statement must be ordered by person_id
1480 --
1481 -- Start of BUG 5671777-11
1482 -- fetch start date and end date
1483 --
1484 BEGIN
1485
1486 OPEN csr_get_p45_start_end_date(pactid);
1487 FETCH csr_get_p45_start_end_date INTO l_chk_start_date,
1488 l_chk_end_date;
1489 CLOSE csr_get_p45_start_end_date;
1490
1491 IF l_chk_end_date IS NULL THEN
1492 OPEN csr_get_p45_EDI_start_end_date(pactid);
1493 FETCH csr_get_p45_EDI_start_end_date INTO l_chk_start_date,
1494 l_chk_end_date;
1495 CLOSE csr_get_p45_EDI_start_end_date;
1496
1497 END IF;
1498
1499
1500 --
1501 -- Check whether P45 Prcess to ensure it is run for one tax year at a time or not
1502 --
1503
1504 IF ((l_chk_start_date BETWEEN to_date('06/04/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')
1505 AND to_date('31/12/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy'))
1506 AND (l_chk_end_date BETWEEN to_date('06/04/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')
1507 AND to_date('05/04/'||to_char(to_number(to_char(l_chk_start_date,'YYYY'))+1),'dd/mm/yyyy')))
1508 OR ((l_chk_start_date BETWEEN to_date('01/01/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')
1509 AND to_date('05/04/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy'))
1510 AND (l_chk_end_date BETWEEN to_date('01/01/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')
1511 AND to_date('05/04/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')))
1512 THEN
1513 hr_utility.set_location('Start Date and End Date are in the same tax year ',6);
1514 ELSE
1515 fnd_file.put_line (fnd_file.LOG, 'The Start Date and the End Date must be within the tax year.');
1516 hr_utility.set_location('The Start Date and the End Date must be within the tax year.',8);
1517 RAISE p45_one_taxyear_error;
1518 END IF;
1519 END;
1520 -- End of BUG 5671777-11
1521
1522 OPEN csr_employer_details(pactid);
1523 FETCH csr_employer_details INTO rec_employer_details;
1524 IF csr_employer_details%FOUND THEN
1525 -- Action is for P45 Archive (not EDI)
1526 OPEN csr_user_entity('X_EMPLOYERS_ADDRESS_LINE');
1527 FETCH csr_user_entity INTO l_employers_address_line_eid;
1528 CLOSE csr_user_entity;
1529 OPEN csr_user_entity('X_EMPLOYERS_NAME');
1530 FETCH csr_user_entity INTO l_employers_name_eid;
1531 CLOSE csr_user_entity;
1532 OPEN csr_user_entity('X_TAX_DISTRICT_NAME');
1533 FETCH csr_user_entity INTO l_tax_district_name_eid;
1534 CLOSE csr_user_entity;
1535 -- Archive the employer details
1536 ff_archive_api.create_archive_item
1537 (p_archive_item_id => l_archive_item_id,
1538 p_user_entity_id => l_employers_address_line_eid,
1539 p_archive_value => rec_employer_details.employers_address_line,
1540 p_archive_type => 'PA',
1541 p_action_id => pactid,
1542 p_legislation_code => 'GB',
1543 p_object_version_number => l_ovn,
1544 p_context_name1 => 'PAYROLL_ID',
1545 p_context1 => '0',
1546 p_some_warning => l_some_warning);
1547 ff_archive_api.create_archive_item
1548 (p_archive_item_id => l_archive_item_id,
1549 p_user_entity_id => l_employers_name_eid,
1550 p_archive_value => rec_employer_details.employers_name,
1551 p_archive_type => 'PA',
1552 p_action_id => pactid,
1553 p_legislation_code => 'GB',
1554 p_object_version_number => l_ovn,
1555 p_context_name1 => 'PAYROLL_ID',
1556 p_context1 => '0',
1557 p_some_warning => l_some_warning);
1558 ff_archive_api.create_archive_item
1559 (p_archive_item_id => l_archive_item_id,
1560 p_user_entity_id => l_tax_district_name_eid,
1561 p_archive_value => rec_employer_details.tax_district_name,
1562 p_archive_type => 'PA',
1563 p_action_id => pactid,
1564 p_legislation_code => 'GB',
1565 p_object_version_number => l_ovn,
1566 p_context_name1 => 'PAYROLL_ID',
1567 p_context1 => '0',
1568 p_some_warning => l_some_warning);
1569 --
1570 END IF;
1571 CLOSE csr_employer_details;
1572 --
1573 hr_utility.set_location(l_proc,10);
1574 --
1575 --
1576 IF l_payroll_id is not null then
1577 -- Payroll ID has been used in param, restrict by this.
1578 hr_utility.set_location(l_proc,20);
1579 sqlstr := 'select distinct paaf.person_id '||
1580 'from pay_payroll_actions ppa, '||
1581 'per_all_assignments_f paaf '||
1582 'where ppa.payroll_action_id = :payroll_action_id '||
1583 'and paaf.business_group_id + 0 = ppa.business_group_id '||
1584 'and paaf.payroll_id = '||to_char(l_payroll_id)||
1585 ' order by paaf.person_id';
1586 --
1587 ELSE
1588 -- Normal range not restricting by payroll_id.
1589 hr_utility.set_location(l_proc,30);
1590 sqlstr := 'select distinct person_id '||
1591 'from per_people_f ppf, '||
1592 'pay_payroll_actions ppa '||
1593 'where ppa.payroll_action_id = :payroll_action_id '||
1594 'and ppa.business_group_id = ppf.business_group_id '||
1595 'order by ppf.person_id';
1596 END IF;
1597 hr_utility.set_location(' Leaving: '||l_proc,100);
1598 EXCEPTION
1599 --
1600 -- Start of BUG 5671777-11
1601 --
1602 WHEN p45_one_taxyear_error THEN
1603 sqlstr := 'select 1 '||
1604 '/* ERROR - The Start Date and the End Date must be within the tax year: '||
1605 sqlerrm(sqlcode)||' */ '||
1606 'from dual where to_char(:payroll_action_id) = dummy';
1607 RAISE;
1608 --
1609 -- End of BUG 5671777-11
1610 --
1611 -- Added for P45PT1. Bug 6345375.
1612 -- Raise Unhandled exception to fail the process.
1613 --
1614 WHEN test_indicator_error THEN
1615 RAISE;
1616 --
1617 --
1618 WHEN OTHERS THEN
1619 -- Return cursor that selects no rows
1620 sqlstr := 'select 1 '||
1621 '/* ERROR - Employer Details Fetch failed with: '||
1622 sqlerrm(sqlcode)||' */ '||
1623 'from dual where to_char(:payroll_action_id) = dummy';
1624 hr_utility.set_location(' Leaving: '||l_proc,110);
1625 END range_cursor;
1626 ---------------------------------------------------------------------------
1627 -- Function: range_person_on.
1628 -- Description: Returns true if the range_person performance enhancement is
1629 -- enabled for the system. Used by arch_act_creation, edi_act_creation.
1630 ---------------------------------------------------------------------------
1631 FUNCTION range_person_on (p_report_format in varchar2) RETURN BOOLEAN IS
1632 --
1633 CURSOR csr_action_parameter is
1634 select parameter_value
1635 from pay_action_parameters
1636 where parameter_name = 'RANGE_PERSON_ID';
1637 --
1638 CURSOR csr_range_format_param (c_report_format in varchar2) is
1639 select par.parameter_value
1640 from pay_report_format_parameters par,
1641 pay_report_format_mappings_f map
1642 where map.report_format_mapping_id = par.report_format_mapping_id
1643 and map.report_type = 'P45'
1644 and map.report_format = c_report_format
1645 and map.report_qualifier = 'GB'
1646 and par.parameter_name = 'RANGE_PERSON_ID';
1647 --
1648 l_return boolean;
1649 l_action_param_val varchar2(30);
1650 l_report_param_val varchar2(30);
1651 --
1652 BEGIN
1653 hr_utility.set_location('range_person_on',10);
1654 --
1655 BEGIN
1656 open csr_action_parameter;
1657 fetch csr_action_parameter into l_action_param_val;
1658 close csr_action_parameter;
1659 --
1660 hr_utility.set_location('range_person_on',20);
1661 open csr_range_format_param(p_report_format);
1662 fetch csr_range_format_param into l_report_param_val;
1663 close csr_range_format_param;
1664 --
1665 hr_utility.set_location('range_person_on',30);
1666 EXCEPTION WHEN NO_DATA_FOUND THEN
1667 l_return := FALSE;
1668 END;
1669 --
1670 hr_utility.set_location('range_person_on',40);
1671 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
1672 l_return := TRUE;
1673 hr_utility.trace('Range Person = True');
1674 ELSE
1675 l_return := FALSE;
1676 END IF;
1677 --
1678 RETURN l_return;
1679 --
1680 END range_person_on;
1681 ---------------------------------------------------------------------------
1682 -- FUNCTION: p45_existing_action
1683 -- DESCRIPTION: boolean function for the existence of a leaver or tfr
1684 -- P45 action (archived). NB Mark for print not used.
1685 --------------------------------------------------------------------------
1686 FUNCTION p45_existing_action(p_assignment_id in number,
1687 p_period_of_service_id in number,
1688 p_mode in varchar2) RETURN BOOLEAN
1689 IS
1690 --
1691 l_p45_action_exists boolean := FALSE;
1692 l_arc_period_of_service_id ff_archive_items.value%TYPE;
1693 l_arc_tax_ref_transfer ff_archive_items.value%TYPE;
1694 --
1695 /*
1696 cursor csr_check_action(c_assignment_id in number) is
1697 select
1698 max(decode(fai.user_entity_id,g_period_of_service_eid,fai.VALUE)) pos
1699 ,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
1700 from ff_archive_items fai, -- of P45 report type
1701 pay_assignment_actions act,
1702 pay_payroll_actions ppa
1703 where ppa.report_type='P45'
1704 and ppa.report_qualifier='GB'
1705 and ppa.report_category ='P45'
1706 and ppa.action_type = 'X'
1707 and ppa.payroll_action_id = act.payroll_action_id
1708 and act.assignment_id = c_assignment_id
1709 and act.assignment_action_id = fai.context1
1710 and fai.user_entity_id in (g_tax_ref_transfer_eid,
1711 g_period_of_service_eid);
1712 */
1713 cursor csr_check_action(c_assignment_id in number) is
1714 select max(decode(fai.user_entity_id,g_period_of_service_eid,fai.VALUE)) pos
1715 ,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
1716 from ff_archive_items fai
1717 where fai.user_entity_id in (g_tax_ref_transfer_eid,g_period_of_service_eid)
1718 and fai.context1 = (select max(act.assignment_action_id)
1719 from pay_payroll_actions ppa,
1720 pay_assignment_actions act
1721 where ppa.report_type='P45'
1722 and ppa.report_qualifier='GB'
1723 and ppa.report_category ='P45'
1724 and ppa.action_type = 'X'
1725 and ppa.payroll_action_id = act.payroll_action_id
1726 and act.assignment_id = c_assignment_id);
1727 --
1728 BEGIN
1729
1730 open csr_check_action(p_assignment_id);
1731 fetch csr_check_action into l_arc_period_of_service_id,
1732 l_arc_tax_ref_transfer;
1733 --
1734 -- Fix bug 4120027
1735 if l_arc_period_of_service_id is null and csr_check_action%FOUND then
1736 if p_mode = 'LEAVER' then
1737 if l_arc_tax_ref_transfer = 'N' then
1738 l_p45_action_exists := TRUE;
1739 else
1740 l_p45_action_exists := FALSE; -- the default
1741 end if;
1742 elsif p_mode = 'TRANSFER' then
1743 if l_arc_tax_ref_transfer = 'Y' then
1744 l_p45_action_exists := TRUE;
1745 else
1746 l_p45_action_exists := FALSE; -- the default
1747 end if;
1748 end if;
1749 else
1750 if p_mode = 'LEAVER' then
1751 -- If the archived period of service matches the live, and
1752 -- the archive action is not a Taxref transfer
1753 --
1754 if l_arc_period_of_service_id = p_period_of_service_id
1755 and l_arc_tax_ref_transfer = 'N' then
1756 l_p45_action_exists := TRUE;
1757 else
1758 l_p45_action_exists := FALSE; -- the default
1759 end if;
1760 elsif p_mode = 'TRANSFER' then
1761 -- If the archived period of service matches the live, and
1762 -- the archive action IS a transfer.
1763 if l_arc_period_of_service_id = p_period_of_service_id
1764 and l_arc_tax_ref_transfer = 'Y' then
1765 l_p45_action_exists := TRUE;
1766 else
1767 l_p45_action_exists := FALSE; -- the default
1768 end if;
1769 end if;
1770 end if;
1771
1772 close csr_check_action;
1773 --
1774 RETURN l_p45_action_exists;
1775
1776 end p45_existing_action;
1777 --------------------------------------------------------------------------
1778 -- FUNCTION future_active_exists
1779 -- Returns TRUE if active or suspended asg exists after the current asg.
1780 --------------------------------------------------------------------------
1781 FUNCTION future_active_exists (p_assignment_id in number,
1782 p_effective_end_date in date) RETURN BOOLEAN
1783 IS
1784 --
1785 l_future_asg boolean := FALSE;
1786 l_number number;
1787 --
1788 cursor csr_future_assignment (c_assignment_id in number,
1789 c_effective_end_date in date) is
1790 select 1 from dual where exists
1791 (select paf.effective_end_date
1792 from per_all_assignments_f paf,
1793 per_assignment_status_types past
1794 where past.assignment_status_type_id = paf.assignment_status_type_id
1795 and paf.assignment_id = c_assignment_id
1796 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
1797 and paf.effective_end_date > c_effective_end_date);
1798 --
1799 BEGIN
1800 --
1801 open csr_future_assignment(p_assignment_id, p_effective_end_date);
1802 fetch csr_future_assignment into l_number;
1803 if csr_future_assignment%FOUND then
1804 l_future_asg := TRUE;
1805 else
1806 l_future_asg := FALSE;
1807 end if;
1808 --
1809 RETURN l_future_asg;
1810 end future_active_exists;
1811 --
1812 --------------------------------------------------------------------------
1813 -- FUNCTION is_transferred
1814 -- DESCRIPTION Check whether an assignment has transferred Tax
1815 -- Districts
1816 --------------------------------------------------------------------------
1817 FUNCTION is_transferred (p_assignment_id in number,
1818 p_effective_end_date in date,
1819 p_tax_ref in varchar2) RETURN BOOLEAN IS
1820 --
1821 l_exists number;
1822 l_transfer_done boolean := FALSE;
1823 --
1824 cursor csr_transfer (c_assignment_id in number,
1825 c_effective_end_date in date,
1826 c_tax_ref in varchar2) is
1827 select 1 from dual where exists
1828 (select scl.segment1
1829 from per_all_assignments_f paf,
1830 pay_all_payrolls_f ppf,
1831 hr_soft_coding_keyflex scl
1832 where paf.assignment_id = c_assignment_id
1833 and paf.payroll_id = ppf.payroll_id
1834 and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
1835 and scl.segment1 <> c_tax_ref
1836 and paf.effective_start_date between
1837 ppf.effective_start_date and ppf.effective_end_date
1838 and paf.effective_end_date > c_effective_end_date);
1839 --
1840 BEGIN
1841 open csr_transfer(p_assignment_id, p_effective_end_date, p_tax_ref);
1842 fetch csr_transfer into l_exists;
1843 if csr_transfer%FOUND then
1844 l_transfer_done := TRUE;
1845 else
1846 l_transfer_done := FALSE;
1847 end if;
1848 --
1849 RETURN l_transfer_done;
1850 --
1851 END is_transferred;
1852 --------------------------------------------------------------------------
1853 -- FUNCTION payment_made
1854 -- DESCRIPTION Check whether final payment has been made for an assignment
1855 -- after the end date so that the p45 can be issued. If not,
1856 -- and a Last Standard Process has been set, check that an asg
1857 -- action exists in the period immediately preceding LSP date.
1858 --------------------------------------------------------------------------
1859 --
1860 FUNCTION payment_made (p_assignment_id in number,
1861 p_effective_end_date in date,
1862 p_period_of_service_id in number) RETURN boolean IS
1863 --
1864 l_payment_made boolean := FALSE;
1865 l_number number;
1866 l_lsp_date date;
1867 --
1868 cursor csr_payment (c_assignment_id in number,
1869 c_effective_end_date in date) is
1870 select 1 from dual where exists
1871 (select pa.effective_date
1872 from pay_payroll_actions pa,
1873 pay_assignment_actions aa
1874 where aa.assignment_id = c_assignment_id
1875 and aa.payroll_action_id = pa.payroll_action_id
1876 and pa.action_type in ('R','Q','V','I','B')
1877 and pa.date_earned >= c_effective_end_date);
1878 --
1879 cursor csr_last_standard_process (c_period_of_service_id in number) is
1880 select serv.last_standard_process_date
1881 from per_periods_of_service serv
1882 where serv.period_of_service_id = c_period_of_service_id;
1883 --
1884 cursor csr_lsp_payment(c_last_process_date in date,
1885 c_effective_end_date in date,
1886 c_assignment_id in number) is
1887 select 1 from dual where exists
1888 (select paa.assignment_action_id
1889 from pay_assignment_actions paa,
1890 pay_payroll_actions ppa,
1891 per_time_periods ptp
1892 where ptp.time_period_id = ppa.time_period_id
1893 and ppa.payroll_action_id = paa.payroll_action_id
1894 and paa.assignment_id = c_assignment_id
1895 and ppa.action_type in ('R','Q','V','I','B')
1896 and ptp.regular_payment_date =
1897 (select max(ptp.regular_payment_date)
1898 from per_all_assignments_f paf,
1899 per_time_periods ptp
1900 where ptp.regular_payment_date <= c_last_process_date
1901 and paf.assignment_id = c_assignment_id
1902 and ptp.payroll_id = paf.payroll_id
1903 and c_effective_end_date between
1904 paf.effective_start_date and paf.effective_end_date));
1905 --
1906 BEGIN
1907 --
1908 -- First check whether there has been any payments made
1909 -- after asgs end date, as there will be in most cases:
1910 --
1911 open csr_payment (p_assignment_id, p_effective_end_date);
1912 fetch csr_payment into l_number;
1913 if csr_payment%FOUND then
1914 l_payment_made := TRUE;
1915 else
1916 l_payment_made := FALSE;
1917 end if;
1918 close csr_payment;
1919 --
1920 IF l_payment_made = FALSE THEN
1921 --
1922 -- User may have set Last Standard Process to before default
1923 -- date or on termination. Check the date
1924 --
1925 open csr_last_standard_process (p_period_of_service_id);
1926 fetch csr_last_standard_process into l_lsp_date;
1927 close csr_last_standard_process;
1928 --
1929 IF l_lsp_date is null then
1930 -- No Last Standard Process Date and no Payment made.
1931 l_payment_made := FALSE;
1932 ELSE
1933 -- LSP Populated, so check assignment actions for assignment
1934 -- during the time period whose RPD immediately precedes the LSP
1935 open csr_lsp_payment(l_lsp_date, p_effective_end_date, p_assignment_id);
1936 fetch csr_lsp_payment into l_number;
1937 if csr_lsp_payment%FOUND then
1938 l_payment_made := TRUE;
1939 else
1940 l_payment_made := FALSE;
1941 end if;
1942 END IF;
1943 END IF;
1944 --
1945 RETURN l_payment_made;
1946 --
1947 end payment_made;
1948 --------------------------------------------------------------------------
1949 -- FUNCTION manual_issue_exists
1950 -- DESCRIPTION Find whether there has been a manual issue date set.
1951 --------------------------------------------------------------------------
1952 FUNCTION manual_issue_exists(p_assignment_id in number) RETURN BOOLEAN IS
1953 --
1954 l_number number;
1955 l_manual_issue_exists BOOLEAN;
1956 --
1957 cursor csr_manual_date (c_assignment_id in number) is
1958 select 1 from dual where exists
1959 (select aei.aei_information3
1960 from per_assignment_extra_info aei
1961 where aei.assignment_id = c_assignment_id
1962 and aei.aei_information3 is not null
1963 and aei.information_type = 'GB_P45');
1964 --
1965 BEGIN
1966 open csr_manual_date(p_assignment_id);
1967 fetch csr_manual_date into l_number;
1968 if csr_manual_date%NOTFOUND then
1969 l_manual_issue_exists := FALSE;
1970 else
1971 l_manual_issue_exists := TRUE;
1972 end if;
1973 close csr_manual_date;
1974 --
1975 RETURN l_manual_issue_exists;
1976 END manual_issue_exists;
1977 --------------------------------------------------------------------------
1978 -- FUNCTION tax_code_ni
1979 -- DESCRIPTION Check that the final tax code of the assignment is not
1980 -- 'NI', as we should not produce p45 for such asgs
1981 --------------------------------------------------------------------------
1982 FUNCTION tax_code_ni(p_assignment_id in number,
1983 p_effective_end_date in date) RETURN BOOLEAN IS
1984 --
1985 l_tax_code_ni boolean;
1986 l_number number;
1987 l_latest_asg_action_id number;
1988 --
1989 cursor csr_latest_action (c_assignment_id in number,
1990 c_effective_end_date in date) is
1991 select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0') ||
1992 paa.assignment_action_id),16))
1993 FROM pay_assignment_actions paa,
1994 pay_payroll_actions ppa
1995 WHERE
1996 paa.assignment_id = c_assignment_id
1997 AND ppa.payroll_action_id = paa.payroll_action_id
1998 AND (paa.source_action_id is not null
1999 or ppa.action_type in ('I','V','B'))
2000 AND ppa.effective_date <= c_effective_end_date
2001 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
2002 --
2003 cursor csr_tax_code_ni (c_assignment_action_id in number,
2004 c_effective_end_date in date) is
2005 select 1 from dual where exists
2006 (select prrv.result_value
2007 from pay_input_values_f inv,
2008 pay_element_types_f type,
2009 pay_run_results prr,
2010 pay_run_result_values prrv
2011 where prrv.input_value_id = inv.input_value_id
2012 and inv.name = 'Tax Code'
2013 and prr.assignment_action_id = c_assignment_action_id
2014 and c_effective_end_date between
2015 inv.effective_start_date and inv.effective_end_date
2016 and c_effective_end_date between
2017 type.effective_start_date and type.effective_end_date
2018 and prrv.result_value = 'NI'
2019 and type.element_name = 'PAYE Details'
2020 and type.element_type_id = prr.element_type_id
2021 and prrv.run_result_id = prr.run_result_id);
2022 --
2023 BEGIN
2024 --
2025 open csr_latest_action(p_assignment_id,p_effective_end_date);
2026 fetch csr_latest_action into l_latest_asg_action_id;
2027 close csr_latest_action;
2028 --
2029 open csr_tax_code_ni(l_latest_asg_action_id,p_effective_end_date);
2030 fetch csr_tax_code_ni into l_number;
2031 if csr_tax_code_ni%FOUND then
2032 l_tax_code_ni := TRUE;
2033 else l_tax_code_ni := FALSE;
2034 end if;
2035 --
2036 RETURN l_tax_code_ni;
2037
2038 END tax_code_ni;
2039 --------------------------------------------------------------------------
2040 -- NEW procedure for dealing with aggregated and non agg assignments
2041 --------------------------------------------------------------------------
2042 PROCEDURE arch_act_creation(pactid IN NUMBER,
2043 stperson IN NUMBER,
2044 endperson IN NUMBER,
2045 chunk IN NUMBER) IS
2046 --
2047 TYPE g_type_asg_rec IS RECORD (
2048 assignment_id number,
2049 assignment_number varchar2(40),
2050 period_of_service_id number,
2051 person_id number,
2052 agg_paye_flag char,
2053 asg_end_date date,
2054 regular_payment_date date
2055 );
2056
2057 l_proc CONSTANT VARCHAR2(35):= g_package||'arch_act_creation';
2058 l_number number;
2059 l_actid pay_assignment_actions.assignment_action_id%TYPE;
2060 l_non_p45_message varchar2(50);
2061 l_transfer_flag varchar2(1);
2062 l_override_date date;
2063 l_archive boolean;
2064 l_check_main_flag boolean;
2065 l_range_person_on varchar2(3);
2066 rec_asg g_type_asg_rec;
2067 --
2068 -- vars for returns from the API:
2069 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
2070 l_ovn NUMBER;
2071 l_some_warning BOOLEAN;
2072 -- 5144323: To store ids of assignments included on the P45
2073 TYPE l_included_asg_tab_type IS TABLE OF NUMBER
2074 INDEX BY binary_integer;
2075 --
2076 l_inc_asg_tab l_included_asg_tab_type;
2077 l_empty_asg_tab l_included_asg_tab_type;
2078 l_inc_asg_index NUMBER;
2079 --
2080 cursor csr_parameter_info(p_payroll_action_id NUMBER) IS
2081 SELECT
2082 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2083 'PAYROLL_ID')) payroll_id,
2084 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2085 'TAX_REF'),1,20) tax_ref,
2086 start_date,
2087 effective_date,
2088 fnd_date.canonical_to_date
2089 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
2090 'DATE_TO')) end_date,
2091 business_group_id
2092 FROM pay_payroll_actions
2093 WHERE payroll_action_id = p_payroll_action_id;
2094 --
2095 cursor csr_user_entity(p_entity_name VARCHAR2) IS
2096 SELECT user_entity_id
2097 FROM ff_user_entities
2098 WHERE user_entity_name = p_entity_name
2099 AND legislation_code = 'GB'
2100 AND business_group_id IS NULL;
2101 --
2102 cursor csr_person_agg_asg (c_person_id in number,
2103 c_tax_ref in varchar2,
2104 c_assignment_id in number,
2105 c_period_of_service_id in number,
2106 c_term_date in date,
2107 c_agg_start_date in date,
2108 c_agg_end_date in date) is
2109 select a.assignment_id,
2110 a.effective_end_date
2111 from per_all_assignments_f a,
2112 pay_all_payrolls_f pay,
2113 hr_soft_coding_keyflex flex,
2114 per_periods_of_service serv
2115 where a.person_id = c_person_id
2116 and flex.segment1 = c_tax_ref
2117 and pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2118 and a.payroll_id = pay.payroll_id
2119 and a.effective_end_date between
2120 pay.effective_start_date and pay.effective_end_date
2121 and serv.period_of_service_id = a.period_of_service_id
2122 and a.assignment_id <> c_assignment_id
2123 and a.period_of_service_id = c_period_of_service_id
2124 -- 5144323: only last active/suspended dt instances of the
2125 -- assignemnts are needed
2126 AND a.effective_end_date = ( SELECT max(effective_end_date)
2127 FROM per_all_assignments_f a1,
2128 per_assignment_status_types past
2129 WHERE a.assignment_id = a1.assignment_id
2130 AND a1.assignment_status_type_id = past.assignment_status_type_id
2131 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN'))
2132 -- 5144323: assignments must exist during the aggregation period
2133 AND EXISTS (SELECT 1 FROM per_all_assignments_f a2
2134 WHERE a.assignment_id = a2.assignment_id
2135 AND a2.effective_start_date <= c_agg_end_date
2136 AND a2.effective_end_date >= c_agg_start_date)
2137 -- 5144323: assignments must share continuous period of
2138 -- employment with the input assignment
2139 AND EXISTS (SELECT 1
2140 FROM per_all_assignments_f a3,
2141 per_assignment_status_types past
2142 WHERE a.assignment_id = a3.assignment_id
2143 AND a3.assignment_status_type_id = past.assignment_status_type_id
2144 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
2145 AND a3.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_term_date)
2146 AND a3.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_term_date));
2147
2148 --
2149 /* Fix performance bug by split cursor into 2 */
2150 cursor csr_all_assignments is
2151 SELECT /*+ ORDERED */
2152 a.assignment_id,
2153 a.assignment_number,
2154 a.period_of_service_id,
2155 p.person_id,
2156 decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
2157 max(a.effective_end_date) asg_end_date,
2158 ptp.regular_payment_date
2159 FROM per_all_people_f p,
2160 per_all_assignments_f a,
2161 per_assignment_status_types past,
2162 pay_all_payrolls_f ppf,
2163 per_time_periods ptp,
2164 per_periods_of_service serv,
2165 hr_soft_coding_keyflex flex
2166 WHERE a.person_id BETWEEN stperson AND endperson
2167 AND a.business_group_id +0 = g_business_group_id
2168 AND (g_payroll_id is null
2169 or
2170 a.payroll_id + 0 = g_payroll_id)
2171 AND a.effective_end_date BETWEEN g_start_date AND g_end_date
2172 AND a.payroll_id = ppf.payroll_id
2173 AND a.period_of_service_id = serv.period_of_service_id
2174 AND a.effective_end_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2175 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2176 AND flex.segment1 = g_tax_ref
2177 AND ppf.payroll_id = ptp.payroll_id
2178 AND a.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
2179 AND a.effective_end_date <= g_end_date -- before run end date
2180
2181 -- AND a.effective_end_date = -- the latest active or susp asg exclude DT update
2182 -- (select max(asg2.effective_end_date)
2183 -- from per_all_assignments_f asg2,
2184 -- per_assignment_status_types past
2185 -- where asg2.assignment_id = a.assignment_id
2186 -- and asg2.assignment_status_type_id =past.assignment_status_type_id
2187 -- and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2188 -- and asg2.effective_end_date <> hr_general.end_of_time)
2189 AND a.assignment_status_type_id =past.assignment_status_type_id
2190 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2191 AND a.effective_end_date <> hr_general.end_of_time
2192 AND a.person_id = p.person_id
2193 AND a.effective_end_date between p.effective_start_date and p.effective_end_date
2194 GROUP BY a.assignment_id, a.assignment_number, a.period_of_service_id,
2195 p.person_id, decode(p.per_information10,'Y','Y',NULL),
2196 ptp.regular_payment_date;
2197 --
2198 -- The 2nd half of union is a copy of csr_all_assignments, but with a join to
2199 -- pay_population_ranges, for performance improvement if the range_person
2200 -- functionality is enabled. Only 1 half will be used as these are made exclusive
2201 -- by the parameter c_range_person_on.
2202 --
2203 -- UNION ALL
2204 --
2205 cursor csr_all_assignments_range is
2206 SELECT /*+ ORDERED*/
2207 a.assignment_id, a.assignment_number,
2208 a.period_of_service_id,
2209 p.person_id,
2210 decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
2211 max(a.effective_end_date) asg_end_date,
2212 ptp.regular_payment_date
2213 FROM pay_population_ranges ppr,
2214 per_all_people_f p,
2215 per_all_assignments_f a,
2216 per_assignment_status_types past,
2217 pay_all_payrolls_f ppf,
2218 per_time_periods ptp,
2219 per_periods_of_service serv,
2220 hr_soft_coding_keyflex flex
2221 WHERE p.person_id = ppr.person_id
2222 AND ppr.chunk_number = chunk
2223 AND ppr.payroll_action_id = pactid
2224 AND a.business_group_id +0 = g_business_group_id
2225 AND a.payroll_id +0 = nvl(g_payroll_id,a.payroll_id)
2226 AND a.effective_end_date
2227 BETWEEN g_start_date AND g_end_date
2228 AND a.payroll_id = ppf.payroll_id
2229 AND a.period_of_service_id = serv.period_of_service_id
2230 AND a.effective_end_date
2231 BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2232 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2233 AND flex.segment1 = g_tax_ref
2234 AND ppf.payroll_id = ptp.payroll_id
2235 AND a.effective_end_date
2236 BETWEEN ptp.start_date AND ptp.end_date
2237 AND a.effective_end_date <= g_end_date -- before run end date
2238
2239 --AND a.effective_end_date = -- the latest active or susp asg exclude DT update
2240 -- (select max(asg2.effective_end_date)
2241 -- from per_all_assignments_f asg2,
2242 -- per_assignment_status_types past
2243 -- where asg2.assignment_id = a.assignment_id
2244 -- and asg2.assignment_status_type_id =
2245 -- past.assignment_status_type_id
2246 -- and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2247 -- and asg2.effective_end_date <> hr_general.end_of_time)
2248 AND a.assignment_status_type_id =past.assignment_status_type_id
2249 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2250 AND a.effective_end_date <> hr_general.end_of_time
2251 AND a.person_id = p.person_id
2252 AND a.effective_end_date between p.effective_start_date and p.effective_end_date
2253 GROUP BY a.assignment_id, a.assignment_number, a.period_of_service_id,
2254 p.person_id, decode(p.per_information10,'Y','Y',NULL),
2255 ptp.regular_payment_date;
2256 -------------------------------------------------------------------------------------
2257 -- Cursor to find last date after assignment's termination date until which
2258 -- aggregation flag has remained Y - assuming flag is Y at the termination date
2259 CURSOR get_agg_end(p_person_id NUMBER, p_term_date DATE) IS
2260 SELECT nvl((min(effective_start_date)-1), hr_general.end_of_time) agg_end_date
2261 FROM per_all_people_f
2262 WHERE person_id = p_person_id
2263 AND effective_start_date > p_term_date
2264 AND nvl(per_information10, 'N') = 'N';
2265 --
2266 CURSOR get_agg_start(p_person_id NUMBER, p_term_date DATE) IS
2267 SELECT nvl((max(effective_end_date)+1), hr_general.start_of_time) agg_start_date
2268 FROM per_all_people_f
2269 WHERE person_id = p_person_id
2270 AND effective_end_date < p_term_date
2271 AND nvl(per_information10, 'N') = 'N';
2272 --
2273 l_agg_start_date DATE;
2274 l_agg_end_date DATE;
2275 BEGIN
2276 hr_utility.set_location('Entering: '||l_proc,1);
2277 --
2278 IF NOT g_asg_creation_cache_populated THEN
2279 OPEN csr_user_entity('X_TAX_REF_TRANSFER');
2280 FETCH csr_user_entity INTO g_tax_ref_transfer_eid;
2281 CLOSE csr_user_entity;
2282 OPEN csr_user_entity('X_TERMINATION_DATE');
2283 FETCH csr_user_entity INTO g_termination_date_eid;
2284 CLOSE csr_user_entity;
2285 OPEN csr_user_entity('X_PERIOD_OF_SERVICE_ID');
2286 FETCH csr_user_entity INTO g_period_of_service_eid;
2287 CLOSE csr_user_entity;
2288 OPEN csr_user_entity('X_P45_INCLUDED_ASSIGNMENT');
2289 FETCH csr_user_entity INTO g_p45_inc_assignment;
2290 CLOSE csr_user_entity;
2291 --
2292 OPEN csr_parameter_info(pactid);
2293 FETCH csr_parameter_info INTO g_payroll_id,
2294 g_tax_ref,
2295 g_start_date,
2296 g_effective_date,
2297 g_end_date,
2298 g_business_group_id;
2299 CLOSE csr_parameter_info;
2300 --
2301 g_asg_creation_cache_populated := true;
2302 END IF;
2303 --
2304 -- Check whether range_person_functionality is used or not.
2305 -- convert boolean to varchar for use in cursor.
2306 --
2307 IF range_person_on('REPORT') then
2308 l_range_person_on := 'YES';
2309 open csr_all_assignments_range;
2310 ELSE
2311 l_range_person_on := 'NO';
2312 open csr_all_assignments;
2313 END IF;
2314 hr_utility.trace('Range person on: '||l_range_person_on);
2315 --
2316 -- Use First half of cursor where range_person not set,
2317 -- use second half for range_person on (performance enhancement)
2318 --
2319 -- FOR rec_asg IN csr_all_assignments(l_range_person_on) LOOP
2320 --
2321 LOOP
2322 if l_range_person_on = 'YES' then
2323 fetch csr_all_assignments_range into rec_asg;
2324 exit when csr_all_assignments_range%notfound;
2325 else
2326 fetch csr_all_assignments into rec_asg;
2327 exit when csr_all_assignments%notfound;
2328 end if;
2329 --
2330 --
2331 l_archive := TRUE;
2332 hr_utility.trace(to_char(rec_asg.assignment_id)||
2333 ' '||rec_asg.agg_paye_flag||' '||
2334 to_char(rec_asg.asg_end_date,'dd-mon-yyyy')||':');
2335 hr_utility.trace('----------------------------');
2336 --
2337 -- initialize the included asg table and index
2338 l_inc_asg_index := 0;
2339 l_inc_asg_tab := l_empty_asg_tab;
2340 --
2341 IF NOT (is_transferred(rec_asg.assignment_id,
2342 rec_asg.asg_end_date, g_tax_ref))
2343 THEN
2344 --
2345 l_transfer_flag := 'N';
2346 -- Is there an assignment with active status existing
2347 -- with this ID at a later date.
2348 IF future_active_exists(rec_asg.assignment_id,
2349 rec_asg.asg_end_date) then
2350 l_non_p45_message := 'Future Active asg exists';
2351 l_archive := FALSE;
2352 -- This asg is to be excluded
2353 ELSE
2354 hr_utility.set_location(l_proc,5);
2355 IF rec_asg.agg_paye_flag = 'Y' then
2356 --
2357 -- Check current asg for existing p45 actions
2358 IF return_p45_issued_flag(rec_asg.assignment_id) = 'Y' then
2359 hr_utility.set_location(l_proc,6);
2360 -- Cannot archive this assignment, set msg.
2361 l_non_p45_message := 'Agg: P45 exists';
2362 l_archive := FALSE;
2363 ELSE
2364 hr_utility.set_location(l_proc,7);
2365 -- 5144323: Get aggregation period start and end
2366 OPEN get_agg_start(rec_asg.person_id, rec_asg.asg_end_date);
2367 FETCH get_agg_start INTO l_agg_start_date;
2368 CLOSE get_agg_start;
2369 --
2370 OPEN get_agg_end(rec_asg.person_id, rec_asg.asg_end_date);
2371 FETCH get_agg_end INTO l_agg_end_date;
2372 CLOSE get_agg_end;
2373 --
2374 hr_utility.trace('l_agg_start_date='||
2375 fnd_date.date_to_displaydate(l_agg_start_date)||
2376 ', l_agg_end_date='||
2377 fnd_date.date_to_displaydate(l_agg_end_date));
2378 --
2379 -- Loop through all OTHER assignments in this aggregation
2380 -- for further checks. Set a flag if rec_asg.assignment_id
2381 -- cannot be archived.
2382 --
2383 FOR rec_all_aggs in csr_person_agg_asg(rec_asg.person_id, g_tax_ref,
2384 rec_asg.assignment_id, rec_asg.period_of_service_id,
2385 rec_asg.asg_end_date, l_agg_start_date, l_agg_end_date) LOOP
2386 -- 5144323: keep list of agg assignments, this info shd be archived
2387 -- when P45 action is created
2388 l_inc_asg_index := l_inc_asg_index + 1;
2389 l_inc_asg_tab(l_inc_asg_index) := rec_all_aggs.assignment_id;
2390 --
2391 hr_utility.set_location(l_proc,10);
2392 IF rec_asg.asg_end_date < rec_all_aggs.effective_end_date THEN
2393 -- Asg exists that is not ended as of the effective end
2394 -- of the current assignment
2395 l_non_p45_message := 'Agg: asg exists not ended: '||
2396 to_char(rec_all_aggs.assignment_id);
2397 -- Exclude main assignment, exit loop (performance)
2398 l_archive := FALSE;
2399 EXIT;
2400 ELSE
2401 hr_utility.set_location(l_proc,20);
2402 IF rec_asg.asg_end_date = rec_all_aggs.effective_end_date
2403 AND rec_asg.assignment_id > rec_all_aggs.assignment_id THEN
2404 -- Other lower Asg ID exists, and ending on the same date.
2405 -- Exclude this assignment exit loop
2406 l_non_p45_message := 'Agg: Lower asg ID, same end date exists';
2407 l_archive := FALSE;
2408 EXIT;
2409 ELSE
2410 hr_utility.set_location(l_proc,30);
2411 l_check_main_flag := true;
2412 --
2413 END IF; -- lower asg id
2414 END IF; -- existing later assignments
2415 END LOOP; -- OTHER agg assignments loop
2416 -- fix for bug 5380921 --
2417 IF l_check_main_flag AND l_archive THEN
2418 IF NOT payment_made(rec_asg.assignment_id, rec_asg.asg_end_date,rec_asg.period_of_service_id) THEN
2419 l_override_date := override_date(rec_asg.assignment_id);
2420 IF l_override_date IS NULL THEN
2421 l_non_p45_message := 'No Final Payment or Override';
2422 l_archive := FALSE;
2423 ELSIF l_override_date > g_effective_date THEN
2424 l_non_p45_message := 'Override date (' || to_char(l_override_date) ||
2425 ') greater than issue date';
2426 l_archive := FALSE;
2427 END IF; -- Override date check
2428 END IF; -- Payment Made check
2429 END IF; -- archive flag check
2430 -- end bug fix for 5380921 --
2431 END IF; -- for current asg leaver check.
2432 ELSE -- Non Aggregated PAYE
2433 hr_utility.set_location(l_proc,35);
2434 IF return_P45_issued_flag(rec_asg.assignment_id) = 'Y' THEN
2435 hr_utility.set_location(l_proc,40);
2436 l_non_p45_message := 'Leaver action exists';
2437 l_archive := FALSE;
2438 ELSE
2439 hr_utility.set_location(l_proc,50);
2440 IF payment_made(rec_asg.assignment_id, rec_asg.asg_end_date,
2441 rec_asg.period_of_service_id) THEN
2442 -- Final Payment made.
2443 hr_utility.set_location(l_proc,60);
2444 l_archive := TRUE;
2445 ELSE
2446 -- Has there been an override that is before the p45
2447 -- run date but after the eff end of the asg.
2448 l_override_date := override_date(rec_asg.assignment_id);
2449 IF l_override_date IS NULL THEN
2450 l_non_p45_message := 'No Final Payment or Override';
2451 l_archive := FALSE;
2452 ELSIF l_override_date <= g_effective_date AND
2453 l_override_date >= rec_asg.asg_end_date THEN
2454 l_archive := TRUE;
2455 hr_utility.set_location(l_proc,65);
2456 ELSE
2457 l_non_p45_message := to_char(rec_asg.asg_end_date)||':'||
2458 to_char(l_override_date)||':'||
2459 to_char(g_effective_date);
2460 l_archive := FALSE;
2461 END IF; -- override date
2462 END IF; -- final payment
2463 END IF; -- P45 check
2464 END IF; -- aggregated paye
2465 END IF; -- future assignment
2466 --
2467 ELSE
2468 ---------------------------------------
2469 -- Tax Reference transfer
2470 ---------------------------------------
2471 hr_utility.set_location(l_proc,70);
2472 l_transfer_flag := 'Y';
2473 if p45_existing_action(p_assignment_id => rec_asg.assignment_id,
2474 p_period_of_service_id => rec_asg.period_of_service_id,
2475 p_mode => 'TRANSFER')
2476 then
2477 l_non_p45_message := 'Transfer action exists';
2478 l_archive := FALSE;
2479 else
2480 IF rec_asg.agg_paye_flag = 'Y' then
2481 hr_utility.set_location(l_proc,80);
2482 for rec_all_aggs in csr_person_agg_asg(rec_asg.person_id, g_tax_ref,
2483 rec_asg.assignment_id, rec_asg.period_of_service_id,
2484 rec_asg.asg_end_date, hr_general.start_of_time,
2485 hr_general.end_of_time) loop
2486 if rec_asg.asg_end_date < rec_all_aggs.effective_end_date then
2487 -- Asg exists that is not ended as of the effective end
2488 -- of the current assignment, and is ACTIVE
2489 l_non_p45_message := 'TFR: Agg: asg exists not ended: '||
2490 to_char(rec_all_aggs.assignment_id);
2491 -- Exclude main assignment, exit loop (performance)
2492 l_archive := FALSE;
2493 exit;
2494 else
2495 hr_utility.set_location(l_proc,90);
2496 if rec_asg.asg_end_date = rec_all_aggs.effective_end_date
2497 and rec_asg.assignment_id > rec_all_aggs.assignment_id
2498 then
2499 -- Other lower Asg ID exists, and ending on the same date.
2500 -- Exclude this assignment exit loop
2501 l_non_p45_message :=
2502 'TFR: Agg: Lower asg ID, same end date exists';
2503 l_archive := FALSE;
2504 exit;
2505 else
2506 -- No reason to exclude this asg.
2507 hr_utility.set_location(l_proc,95);
2508 end if; -- Lower asg id
2509 end if; -- future active for this agg
2510 end loop; -- Aggregated loop
2511 ELSE
2512 -- Not aggregated and no existing action, so archive.
2513 hr_utility.set_location(l_proc,105);
2514 l_archive := TRUE;
2515 END IF; -- Agg asg
2516 end if; -- existing tfr action
2517 END IF; -- TRANSFER
2518 --------------------------------------------
2519 -- Archive the assignment if not excluded
2520 --------------------------------------------
2521 if l_archive = FALSE then
2522 hr_utility.trace('No P45 for '||to_char(rec_asg.assignment_id)||
2523 '. Reason:');
2524 hr_utility.trace(l_non_p45_message);
2525 fnd_file.put_line(fnd_file.log,
2526 rec_asg.assignment_number||': '|| l_non_p45_message);
2527 l_non_p45_message := null;
2528 else
2529 hr_utility.set_location(l_proc,107);
2530 -- Final check for all, has latest asg action got NI Tax code
2531 -- NB Placing this check here at the end for performance reasons.
2532 -- Using regular payment date for final payment date.
2533 if NOT (tax_code_ni(rec_asg.assignment_id,rec_asg.regular_payment_date))
2534 then
2535 hr_utility.trace('ARCHIVING FOR '||to_char(rec_asg.assignment_id));
2536 SELECT pay_assignment_actions_s.nextval
2537 INTO l_actid
2538 FROM dual;
2539 --
2540 hr_utility.set_location(l_proc,110);
2541 hr_nonrun_asact.insact(l_actid,rec_asg.assignment_id,
2542 pactid,chunk,NULL);
2543 --
2544 hr_utility.set_location(l_proc,120);
2545 -- Archive the tax ref transfer flag and the Asg end date.
2546 --
2547 ff_archive_api.create_archive_item
2548 (p_archive_item_id => l_archive_item_id,
2549 p_user_entity_id => g_tax_ref_transfer_eid,
2550 p_archive_value => l_transfer_flag,
2551 p_archive_type => 'AAC',
2552 p_action_id => l_actid,
2553 p_legislation_code => 'GB',
2554 p_object_version_number => l_ovn,
2555 p_some_warning => l_some_warning);
2556 --
2557 ff_archive_api.create_archive_item
2558 (p_archive_item_id => l_archive_item_id,
2559 p_user_entity_id => g_termination_date_eid,
2560 p_archive_value => fnd_date.date_to_canonical(rec_asg.asg_end_date),
2561 p_archive_type => 'AAC',
2562 p_action_id => l_actid,
2563 p_legislation_code => 'GB',
2564 p_object_version_number => l_ovn,
2565 p_some_warning => l_some_warning);
2566 --
2567 FOR l_count IN 1..l_inc_asg_index LOOP
2568 hr_utility.set_location(l_proc,125);
2569 hr_utility.trace('Including asg id='||l_inc_asg_tab(l_count));
2570 ff_archive_api.create_archive_item
2571 (p_archive_item_id => l_archive_item_id,
2572 p_user_entity_id => g_p45_inc_assignment,
2573 p_archive_value => l_inc_asg_tab(l_count),
2574 p_archive_type => 'AAC',
2575 p_action_id => l_actid,
2576 p_legislation_code => 'GB',
2577 p_object_version_number => l_ovn,
2578 p_some_warning => l_some_warning);
2579 END LOOP;
2580 else
2581 hr_utility.set_location(l_proc,130);
2582 hr_utility.trace('No P45 for '||to_char(rec_asg.assignment_id)||
2583 '. Tax Code = NI');
2584 fnd_file.put_line(fnd_file.log,
2585 rec_asg.assignment_number||': Tax Code = NI');
2586 end if; -- NI Tax Code
2587 end if; -- archive FALSE
2588 END LOOP; -- csr_all_assignments
2589 IF l_range_person_on = 'YES' then
2590 if csr_all_assignments_range%isopen then
2591 close csr_all_assignments_range;
2592 end if;
2593 ELSE
2594 if csr_all_assignments%isopen then
2595 close csr_all_assignments;
2596 end if;
2597 END IF;
2598 --
2599 hr_utility.set_location(' Leaving: '||l_proc,999);
2600
2601 END arch_act_creation;
2602
2603 ---------------------------------------------------------------------------
2604 --
2605 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
2606 IS
2607 l_proc CONSTANT VARCHAR2(35):= g_package||'archinit';
2608 l_do_edi_validation VARCHAR2(6);
2609 l_effective_date DATE;
2610 --
2611 cursor csr_user_entity(p_entity_name VARCHAR2) IS
2612 SELECT user_entity_id
2613 FROM ff_user_entities
2614 WHERE user_entity_name = p_entity_name
2615 AND legislation_code = 'GB'
2616 AND business_group_id IS NULL;
2617 --
2618 cursor csr_parameter_info(p_payroll_action_id NUMBER) IS
2619 SELECT
2620 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2621 'PAYROLL_ID')) payroll_id,
2622 decode(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2623 'CHAR_ERROR'),
2624 'Y','TRUE','N','FALSE') check_chars,
2625 effective_date
2626 FROM pay_payroll_actions
2627 WHERE payroll_action_id = p_payroll_action_id;
2628 --
2629 BEGIN
2630 hr_utility.set_location('Entering: '||l_proc,1);
2631 IF NOT g_asg_process_cache_populated THEN
2632 -- does session date need to be set?
2633 -- fnd_sessions used in cursor employer_addr (only when asg terminated
2634 -- with null last std proc date).
2635 -- Get required SRS Parameters
2636 OPEN csr_parameter_info(p_payroll_action_id);
2637 FETCH csr_parameter_info INTO g_payroll_id,
2638 l_do_edi_validation,
2639 l_effective_date;
2640 CLOSE csr_parameter_info;
2641 g_do_edi_validation := hr_general.char_to_bool(l_do_edi_validation);
2642 -- cache User entity Ids
2643 OPEN csr_user_entity('X_ADDRESS_LINE1');
2644 FETCH csr_user_entity INTO g_address_line1_eid;
2645 CLOSE csr_user_entity;
2646 OPEN csr_user_entity('X_ADDRESS_LINE2');
2647 FETCH csr_user_entity INTO g_address_line2_eid;
2648 CLOSE csr_user_entity;
2649 OPEN csr_user_entity('X_ADDRESS_LINE3');
2650 FETCH csr_user_entity INTO g_address_line3_eid;
2651 CLOSE csr_user_entity;
2652 OPEN csr_user_entity('X_ASSIGNMENT_NUMBER');
2653 FETCH csr_user_entity INTO g_assignment_number_eid;
2654 CLOSE csr_user_entity;
2655 OPEN csr_user_entity('X_COUNTY');
2656 FETCH csr_user_entity INTO g_county_eid;
2657 CLOSE csr_user_entity;
2658 OPEN csr_user_entity('X_DECEASED_FLAG');
2659 FETCH csr_user_entity INTO g_deceased_flag_eid;
2660 CLOSE csr_user_entity;
2661 OPEN csr_user_entity('X_FIRST_NAME');
2662 FETCH csr_user_entity INTO g_first_name_eid;
2663 CLOSE csr_user_entity;
2664 OPEN csr_user_entity('X_MIDDLE_NAME'); /*Bug 6710229*/
2665 FETCH csr_user_entity INTO g_middle_name_eid;
2666 CLOSE csr_user_entity;
2667 OPEN csr_user_entity('X_ISSUE_DATE');
2668 FETCH csr_user_entity INTO g_issue_date_eid;
2669 CLOSE csr_user_entity;
2670 OPEN csr_user_entity('X_LAST_NAME');
2671 FETCH csr_user_entity INTO g_last_name_eid;
2672 CLOSE csr_user_entity;
2673 OPEN csr_user_entity('X_MONTH_NUMBER');
2674 FETCH csr_user_entity INTO g_month_number_eid;
2675 CLOSE csr_user_entity;
2676 OPEN csr_user_entity('X_NATIONAL_INSURANCE_NUMBER');
2677 FETCH csr_user_entity INTO g_ni_number_eid;
2678 CLOSE csr_user_entity;
2679 OPEN csr_user_entity('X_ORGANIZATION_NAME');
2680 FETCH csr_user_entity INTO g_organization_name_eid;
2681 CLOSE csr_user_entity;
2682 OPEN csr_user_entity('X_PAYROLL_ID');
2683 FETCH csr_user_entity INTO g_payroll_id_eid;
2684 CLOSE csr_user_entity;
2685 OPEN csr_user_entity('X_POSTAL_CODE');
2686 FETCH csr_user_entity INTO g_postal_code_eid;
2687 CLOSE csr_user_entity;
2688 OPEN csr_user_entity('X_PREVIOUS_TAX_PAID');
2689 FETCH csr_user_entity INTO g_prev_tax_paid_eid;
2690 CLOSE csr_user_entity;
2691 OPEN csr_user_entity('X_PREVIOUS_TAXABLE_PAY');
2692 FETCH csr_user_entity INTO g_prev_taxable_pay_eid;
2693 CLOSE csr_user_entity;
2694 OPEN csr_user_entity('X_STUDENT_LOAN_FLAG');
2695 FETCH csr_user_entity INTO g_student_loan_flag_eid;
2696 CLOSE csr_user_entity;
2697 OPEN csr_user_entity('X_AGGREGATED_PAYE_FLAG');
2698 FETCH csr_user_entity INTO g_aggregated_paye_flag_eid;
2699 CLOSE csr_user_entity;
2700 OPEN csr_user_entity('X_PERIOD_OF_SERVICE_ID');
2701 FETCH csr_user_entity INTO g_period_of_service_eid;
2702 CLOSE csr_user_entity;
2703 OPEN csr_user_entity('X_EFFECTIVE_END_DATE');
2704 FETCH csr_user_entity INTO g_effective_end_date_eid;
2705 CLOSE csr_user_entity;
2706 OPEN csr_user_entity('X_TAX_CODE');
2707 FETCH csr_user_entity INTO g_tax_code_eid;
2708 CLOSE csr_user_entity;
2709 OPEN csr_user_entity('X_TAX_PAID');
2710 FETCH csr_user_entity INTO g_tax_paid_eid;
2711 CLOSE csr_user_entity;
2712 OPEN csr_user_entity('X_TAXABLE_PAY');
2713 FETCH csr_user_entity INTO g_taxable_pay_eid;
2714 CLOSE csr_user_entity;
2715 OPEN csr_user_entity('X_TAX_REF_TRANSFER');
2716 FETCH csr_user_entity INTO g_tax_ref_transfer_eid;
2717 CLOSE csr_user_entity;
2718 OPEN csr_user_entity('X_TERMINATION_DATE');
2719 FETCH csr_user_entity INTO g_termination_date_eid;
2720 CLOSE csr_user_entity;
2721 OPEN csr_user_entity('X_TITLE');
2722 FETCH csr_user_entity INTO g_title_eid;
2723 CLOSE csr_user_entity;
2724 OPEN csr_user_entity('X_DATE_OF_BIRTH');
2725 FETCH csr_user_entity INTO g_date_of_birth_eid;
2726 CLOSE csr_user_entity;
2727 OPEN csr_user_entity('X_SEX');
2728 FETCH csr_user_entity INTO g_sex_eid;
2729 CLOSE csr_user_entity;
2730 OPEN csr_user_entity('X_TOWN_OR_CITY');
2731 FETCH csr_user_entity INTO g_town_or_city_eid;
2732 CLOSE csr_user_entity;
2733 OPEN csr_user_entity('X_W1_M1_INDICATOR');
2734 FETCH csr_user_entity INTO g_w1_m1_indicator_eid;
2735 CLOSE csr_user_entity;
2736 OPEN csr_user_entity('X_WEEK_NUMBER');
2737 FETCH csr_user_entity INTO g_week_number_eid;
2738 CLOSE csr_user_entity;
2739 OPEN csr_user_entity('X_COUNTRY');
2740 FETCH csr_user_entity INTO g_country_eid;
2741 CLOSE csr_user_entity;
2742 OPEN csr_user_entity('X_P45_FINAL_PAYMENT_ACTION');
2743 FETCH csr_user_entity INTO g_p45_final_action;
2744 CLOSE csr_user_entity;
2745
2746 --
2747 -- Get IDs for seed data
2748 SELECT element_type_id
2749 INTO g_paye_details_id
2750 FROM pay_element_types_f
2751 WHERE element_name = 'PAYE Details'
2752 AND l_effective_date BETWEEN effective_start_date
2753 AND effective_end_date;
2754 --
2755 g_asg_process_cache_populated := true;
2756 END IF; -- NOT g_asg_process_cache_populated
2757 hr_utility.set_location(' Leaving: '||l_proc,999);
2758 END archinit;
2759 --
2760 PROCEDURE archive_code(p_assactid IN NUMBER, p_effective_date IN DATE)
2761 IS
2762 --
2763 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
2764 -- vars for returns from the API:
2765 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
2766 l_ovn NUMBER;
2767 l_some_warning BOOLEAN;
2768 --
2769 l_assignment_id per_assignments_f.assignment_id%TYPE;
2770 l_termination_date DATE;
2771 l_tax_ref_transfer VARCHAR2(1);
2772 l_transfer_date DATE:=NULL;
2773 --
2774 l_assignment_number per_assignments_f.assignment_number%TYPE;
2775 l_person_id per_people_f.person_id%TYPE;
2776 l_asg_effective_end_date DATE;
2777 l_deceased_flag VARCHAR2(1);
2778 l_agg_paye_flag VARCHAR2(1);
2779 l_period_of_service_id NUMBER;
2780 l_org_name HR_ALL_ORGANIZATION_units.name%TYPE;
2781 l_last_name per_people_f.last_name%TYPE;
2782 l_first_name per_people_f.first_name%TYPE;
2783 l_middle_name per_people_f.middle_names%TYPE; /*Bug 6710229*/
2784 l_title per_people_f.title%TYPE;
2785 l_date_of_birth per_people_f.date_of_birth%TYPE;
2786 l_sex per_people_f.sex%TYPE;
2787 l_ni_number per_people_f.national_identifier%TYPE;
2788 l_payroll_id per_assignments_f.payroll_id%TYPE;
2789 --
2790 l_address_line1 per_addresses.address_line1%TYPE;
2791 l_address_line2 per_addresses.address_line2%TYPE;
2792 l_address_line3 per_addresses.address_line3%TYPE;
2793 l_town_or_city per_addresses.town_or_city%TYPE;
2794 l_county per_addresses.region_1%TYPE;
2795 l_postal_code per_addresses.postal_code%TYPE;
2796 l_country per_addresses.country%TYPE;
2797 l_last_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
2798 l_date_earned DATE;
2799 l_eff_date DATE;
2800 l_last_pay_action_id pay_assignment_actions.payroll_action_id%TYPE;
2801 --
2802 l_student_loan_flag VARCHAR2(1);
2803 --
2804 l_period_no per_time_periods.period_num%TYPE;
2805 l_tax_reference VARCHAR2(20);
2806 l_tax_code pay_element_entry_values_f.screen_entry_value%TYPE;
2807 l_tax_basis pay_element_entry_values_f.screen_entry_value%TYPE;
2808 l_prev_pay_char pay_element_entry_values_f.screen_entry_value%TYPE;
2809 l_prev_tax_char pay_element_entry_values_f.screen_entry_value%TYPE;
2810 l_tax_code_t pay_element_entry_values_f.screen_entry_value%TYPE;
2811 l_tax_basis_t pay_element_entry_values_f.screen_entry_value%TYPE;
2812 l_prev_pay_char_t pay_element_entry_values_f.screen_entry_value%TYPE;
2813 l_prev_tax_char_t pay_element_entry_values_f.screen_entry_value%TYPE;
2814 --
2815 l_taxable NUMBER;
2816 l_paye NUMBER;
2817 --
2818 l_week_or_month VARCHAR2(1);
2819 --
2820 l_paye_element_id number;
2821 l_tax_code_ipv_id number;
2822 l_tax_basis_ipv_id number;
2823 l_pay_previous_ipv_id number;
2824 l_tax_previous_ipv_id number;
2825 l_max_run_result_id number;
2826 l_lsp_date date;
2827 l_final_process_date date;
2828 --
2829 cursor csr_asg_act_info(p_asgactid NUMBER) IS
2830 SELECT act.assignment_id,
2831 fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str
2832 (act.assignment_action_id,
2833 g_termination_date_eid)) term_date,
2834 pay_gb_eoy_archive.get_arch_str(act.assignment_action_id,
2835 g_tax_ref_transfer_eid) tax_ref_transfer
2836 FROM pay_assignment_actions act
2837 WHERE act.assignment_action_id = p_asgactid;
2838 --
2839 cursor csr_basic_asg_info (p_assid NUMBER, p_term_date DATE) IS
2840 SELECT ass.assignment_number,
2841 ass.person_id,
2842 ass.effective_end_date asg_effective_end_date,
2843 serv.last_standard_process_date,
2844 nvl(serv.final_process_date, hr_general.start_of_time),
2845 ass.period_of_service_id,
2846 decode(serv.leaving_reason,'D','D') deceased_flag,
2847 org.name org_name,
2848 upper(p.last_name) , p.title ,
2849 --SUBSTR(upper(p.first_name || ' ' || p.middle_names),1,150),
2850 --SUBSTR(upper(p.first_name || ',' || p.middle_names),1,150),--replaces space with a "comma" for the P45 EOY changes
2851 upper(p.first_name), upper(p.middle_names), /*Bug 6710229*/
2852 p.national_identifier, ass.payroll_id,
2853 decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
2854 p.date_of_birth, p.sex
2855 FROM
2856 per_all_people_f p,
2857 hr_all_organization_units org,
2858 per_periods_of_service serv,
2859 per_all_assignments_f ass
2860 WHERE ass.assignment_id = p_assid
2861 AND serv.period_of_service_id = ass.period_of_service_id
2862 AND ass.effective_end_date = p_term_date
2863 AND ass.organization_id = org.organization_id
2864 AND ass.person_id = p.person_id
2865 AND ass.effective_end_date BETWEEN p.effective_start_date
2866 AND p.effective_end_date;
2867 --
2868 cursor csr_week_or_month(p_payroll_action_id NUMBER) IS
2869 SELECT decode(target.basic_period_type, 'W', 'W', 'M')
2870 FROM per_time_period_rules target ,
2871 per_time_period_types ptpt ,
2872 pay_payrolls_f roll,
2873 pay_payroll_actions pact
2874 WHERE ptpt.period_type = roll.period_type
2875 AND target.number_per_fiscal_year = ptpt.number_per_fiscal_year
2876 AND roll.payroll_id = pact.payroll_id
2877 AND pact.effective_date BETWEEN roll.effective_start_date
2878 AND roll.effective_end_date
2879 AND pact.payroll_action_id = p_payroll_action_id;
2880 --
2881 -- Bug 5478073: Get period number in which assignment was terminated
2882 CURSOR csr_get_term_period_no(p_term_date DATE, p_payroll_id NUMBER) IS
2883 SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
2884 FROM per_time_periods ptp
2885 WHERE ptp.payroll_id = p_payroll_id
2886 AND p_term_date BETWEEN ptp.start_date AND ptp.end_date;
2887 --
2888 cursor csr_period_number(p_payroll_action_id NUMBER) IS
2889 SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
2890 FROM per_time_periods ptp,
2891 pay_payroll_actions pact
2892 WHERE pact.payroll_action_id = p_payroll_action_id
2893 AND ptp.payroll_id = pact.payroll_id
2894 AND pact.date_earned BETWEEN ptp.start_date AND ptp.end_date;
2895 --
2896 CURSOR csr_paye_element IS
2897 SELECT element_type_id
2898 FROM pay_element_types_f
2899 WHERE element_name = 'PAYE';
2900 --
2901 CURSOR csr_input_value(p_ipv_name IN VARCHAR2) IS
2902 SELECT input_value_id
2903 FROM pay_input_values_f
2904 WHERE element_type_id = l_paye_element_id
2905 AND name = p_ipv_name;
2906 --
2907 CURSOR csr_result_value(p_ipv_id IN NUMBER) IS
2908 SELECT result_value
2909 FROM pay_run_result_values
2910 WHERE run_result_id = l_max_run_result_id
2911 AND input_value_id = p_ipv_id;
2912 --
2913 CURSOR csr_max_run_result IS
2914 SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
2915 pact PAY_PAYROLL_ACTIONS_PK,
2916 r2 PAY_RUN_RESULTS_N50)
2917 USE_NL(assact2, pact, r2) */
2918 to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
2919 r2.run_result_id),17))
2920 FROM pay_assignment_actions assact2,
2921 pay_payroll_actions pact,
2922 pay_run_results r2
2923 WHERE assact2.assignment_id = l_assignment_id
2924 AND r2.element_type_id+0 = l_paye_element_id
2925 AND r2.assignment_action_id = assact2.assignment_action_id
2926 AND r2.status IN ('P', 'PA')
2927 AND pact.payroll_action_id = assact2.payroll_action_id
2928 AND pact.action_type IN ( 'Q','R','B','I')
2929 AND assact2.action_status = 'C'
2930 AND pact.effective_date between
2931 to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(l_date_earned,'YYYY'))),'DD-MM-YYYY')
2932 and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_date_earned,'YYYY') + 1)),'DD-MM-YYYY')
2933 AND NOT EXISTS(
2934 SELECT '1'
2935 FROM pay_action_interlocks pai,
2936 pay_assignment_actions assact3,
2937 pay_payroll_actions pact3
2938 WHERE pai.locked_action_id = assact2.assignment_action_id
2939 AND pai.locking_action_id = assact3.assignment_action_id
2940 AND pact3.payroll_action_id = assact3.payroll_action_id
2941 AND pact3.action_type = 'V'
2942 AND assact3.action_status = 'C');
2943 --
2944 cursor csr_paye_details(p_assignment_id NUMBER,
2945 p_effective_date DATE) IS
2946 SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
2947 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
2948 max(decode(iv.name,'Pay Previous',screen_entry_value))
2949 pay_previous,
2950 max(decode(iv.name,'Tax Previous',screen_entry_value))
2951 tax_previous
2952 FROM pay_element_entries_f e,
2953 pay_element_entry_values_f v,
2954 pay_input_values_f iv,
2955 pay_element_links_f link
2956 WHERE e.assignment_id = p_assignment_id
2957 AND link.element_type_id = g_paye_details_id
2958 AND e.element_link_id = link.element_link_id
2959 AND e.element_entry_id = v.element_entry_id
2960 AND iv.input_value_id = v.input_value_id
2961 AND p_effective_date
2962 BETWEEN link.effective_start_date AND link.effective_end_date
2963 AND p_effective_date
2964 BETWEEN e.effective_start_date AND e.effective_end_date
2965 AND p_effective_date
2966 BETWEEN iv.effective_start_date AND iv.effective_end_date
2967 AND p_effective_date
2968 BETWEEN v.effective_start_date AND v.effective_end_date;
2969 --
2970 PROCEDURE archive_asg_info(p_user_entity_id NUMBER,
2971 p_value VARCHAR2) IS
2972 l_proc CONSTANT VARCHAR2(40):= g_package||'archive_asg_info';
2973 BEGIN
2974 IF p_value IS NOT NULL THEN
2975 hr_utility.set_location(l_proc||' '||p_user_entity_id,10);
2976 ff_archive_api.create_archive_item
2977 (p_archive_item_id => l_archive_item_id,
2978 p_user_entity_id => p_user_entity_id,
2979 p_archive_value => p_value,
2980 p_action_id => p_assactid,
2981 p_legislation_code => 'GB',
2982 p_object_version_number => l_ovn,
2983 p_some_warning => l_some_warning);
2984 END IF;
2985 END archive_asg_info;
2986 --
2987 BEGIN
2988 --hr_utility.trace_on(null,'KT');
2989 hr_utility.set_location('Entering: '||l_proc,1);
2990 hr_utility.trace('Assact ID : ' || p_assactid);
2991 -- Get the AAC level info.
2992 OPEN csr_asg_act_info(p_assactid);
2993 FETCH csr_asg_act_info INTO l_assignment_id,
2994 l_termination_date,
2995 l_tax_ref_transfer;
2996 CLOSE csr_asg_act_info;
2997 --
2998 OPEN csr_basic_asg_info(l_assignment_id,l_termination_date);
2999 FETCH csr_basic_asg_info INTO l_assignment_number,
3000 l_person_id,
3001 l_asg_effective_end_date, l_lsp_date,
3002 l_final_process_date,
3003 l_period_of_service_id,
3004 l_deceased_flag,
3005 l_org_name,
3006 l_last_name,
3007 l_title,
3008 l_first_name,
3009 l_middle_name, /*Bug 6710229*/
3010 l_ni_number,
3011 l_payroll_id,
3012 l_agg_paye_flag,
3013 l_date_of_birth,
3014 l_sex;
3015 CLOSE csr_basic_asg_info;
3016 --
3017 hr_utility.trace('FP Date: ' ||to_char(l_final_process_date));
3018 hr_utility.trace('Term Date: ' || to_char(l_termination_date));
3019 hr_utility.trace('LSP Date: ' || to_char(l_lsp_date));
3020 -- transfer date used in selection of last assignment action
3021 IF l_tax_ref_transfer = 'Y' then
3022 l_transfer_date := l_termination_date;
3023 ELSE
3024 -- 5144323: for transfer cases actions from old PAYE Ref (before transfer date)
3025 -- need to be fetched but assignment/employee termination cases there
3026 -- should be no time limit hence transfer date is set to end of time
3027 l_transfer_date := hr_general.end_of_time;
3028 END IF;
3029 --
3030 hr_utility.trace(l_last_name||' '||to_char(l_asg_effective_end_date)||
3031 to_char(l_assignment_id)||' '||to_char(l_termination_date)||
3032 ' '||l_tax_ref_transfer||' '||to_char(p_effective_date)||
3033 ' '||to_char(l_transfer_date));
3034 hr_utility.trace('--------------------------------------');
3035 IF g_do_edi_validation THEN
3036 IF pay_gb_eoy_magtape.validate_input(upper(l_assignment_number),
3037 'FULL_EDI') > 0 THEN
3038 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3039 hr_utility.set_message_token('ITEM_NAME', 'Assignment Number');
3040 hr_utility.raise_error;
3041 END IF;
3042 IF pay_gb_eoy_magtape.validate_input(l_last_name,
3043 'EDI_SURNAME') > 0 THEN
3044 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3045 hr_utility.set_message_token('ITEM_NAME', 'Surname');
3046 hr_utility.raise_error;
3047 END IF;
3048 IF pay_gb_eoy_magtape.validate_input(l_first_name,
3049 'EDI_SURNAME') > 0 THEN
3050 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3051 hr_utility.set_message_token('ITEM_NAME', 'Forename');
3052 hr_utility.raise_error;
3053 END IF;
3054 IF pay_gb_eoy_magtape.validate_input(l_ni_number,
3055 'FULL_EDI') > 0 THEN
3056 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3057 hr_utility.set_message_token('ITEM_NAME', 'NI Number');
3058 hr_utility.raise_error;
3059 END IF;
3060 END IF;
3061 --
3062 hr_utility.set_location(l_proc,10);
3063 -- Archive info obtained so far
3064 archive_asg_info(g_assignment_number_eid, l_assignment_number);
3065 archive_asg_info(g_deceased_flag_eid, l_deceased_flag);
3066 archive_asg_info(g_aggregated_paye_flag_eid, l_agg_paye_flag);
3067 archive_asg_info(g_period_of_service_eid, l_period_of_service_id);
3068 archive_asg_info(g_effective_end_date_eid, fnd_date.date_to_canonical(l_asg_effective_end_date));
3069 archive_asg_info(g_organization_name_eid, l_org_name);
3070 archive_asg_info(g_last_name_eid, l_last_name);
3071 archive_asg_info(g_title_eid, l_title);
3072 archive_asg_info(g_first_name_eid, l_first_name);
3073 archive_asg_info(g_middle_name_eid, l_middle_name); /*Bug 6710229*/
3074 archive_asg_info(g_ni_number_eid, l_ni_number);
3075 archive_asg_info(g_date_of_birth_eid, fnd_date.date_to_canonical(l_date_of_birth));
3076 archive_asg_info(g_sex_eid, l_sex);
3077 IF g_payroll_id IS NULL THEN
3078 -- archive not restricted by payroll so stamp asg with payroll id.
3079 archive_asg_info(g_payroll_id_eid,
3080 fnd_number.number_to_canonical(l_payroll_id));
3081 END IF;
3082 archive_asg_info(g_issue_date_eid, nvl(fnd_date.date_to_canonical(p_effective_date), fnd_date.date_to_canonical(sysdate)));
3083 --
3084 PAY_P45_PKG.get_data
3085 (X_PERSON_ID => l_person_id,
3086 X_SESSION_DATE => sysdate,
3087 X_ADDRESS_LINE1 => l_address_line1,
3088 X_ADDRESS_LINE2 => l_address_line2,
3089 X_ADDRESS_LINE3 => l_address_line3,
3090 X_TOWN_OR_CITY => l_town_or_city,
3091 X_REGION_1 => l_county,
3092 X_COUNTRY => l_country,
3093 X_POSTAL_CODE => l_postal_code,
3094 X_ASSIGNMENT_ID => l_assignment_id,
3095 X_ASSIGNMENT_ACTION_ID => l_last_asg_action_id,
3096 X_ASSIGNMENT_END_DATE => l_asg_effective_end_date,
3097 X_DATE_EARNED => l_date_earned,
3098 X_PAYROLL_ACTION_ID => l_last_pay_action_id,
3099 X_TRANSFER_DATE => l_transfer_date);
3100 --
3101 hr_utility.trace('Last asg action: '||to_char(l_last_asg_action_id));
3102 hr_utility.trace('Date earned : ' || to_char(l_date_earned));
3103 --
3104 IF g_do_edi_validation THEN
3105 IF pay_gb_eoy_magtape.validate_input(upper(l_address_line1),
3106 'EDI_SURNAME') > 0 THEN
3107 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3108 hr_utility.set_message_token('ITEM_NAME', 'Address Line 1');
3109 hr_utility.raise_error;
3110 END IF;
3111 IF pay_gb_eoy_magtape.validate_input(upper(l_address_line2),
3112 'EDI_SURNAME') > 0 THEN
3113 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3114 hr_utility.set_message_token('ITEM_NAME', 'Address Line 2');
3115 hr_utility.raise_error;
3116 END IF;
3117 IF pay_gb_eoy_magtape.validate_input(upper(l_address_line3),
3118 'EDI_SURNAME') > 0 THEN
3119 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3120 hr_utility.set_message_token('ITEM_NAME', 'Address Line 3');
3121 hr_utility.raise_error;
3122 END IF;
3123 IF pay_gb_eoy_magtape.validate_input(upper(l_town_or_city),
3124 'FULL_EDI') > 0 THEN
3125 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3126 hr_utility.set_message_token('ITEM_NAME', 'Town or City');
3127 hr_utility.raise_error;
3128 END IF;
3129 IF pay_gb_eoy_magtape.validate_input(upper(l_county),
3130 'FULL_EDI') > 0 THEN
3131 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3132 hr_utility.set_message_token('ITEM_NAME', 'County');
3133 hr_utility.raise_error;
3134 END IF;
3135 IF pay_gb_eoy_magtape.validate_input(l_postal_code,
3136 'FULL_EDI') > 0 THEN
3137 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3138 hr_utility.set_message_token('ITEM_NAME', 'Postcode');
3139 hr_utility.raise_error;
3140 END IF;
3141 END IF;
3142 -- Archive info obtained so far
3143 archive_asg_info(g_p45_final_action, l_last_asg_action_id);
3144 archive_asg_info(g_address_line1_eid, l_address_line1);
3145 archive_asg_info(g_address_line2_eid, l_address_line2);
3146 archive_asg_info(g_address_line3_eid, l_address_line3);
3147 archive_asg_info(g_town_or_city_eid, l_town_or_city);
3148 archive_asg_info(g_county_eid, l_county);
3149 archive_asg_info(g_postal_code_eid, l_postal_code);
3150 archive_asg_info(g_country_eid, l_country);
3151 -- get the student loan flag
3152 l_student_loan_flag := PAY_P45_PKG.get_student_loan_flag
3153 (l_assignment_id,
3154 l_termination_date,
3155 sysdate);
3156 -- get_db_and_bal_items
3157 -- Get database items.
3158 --
3159 -- Bug 5478073: If Final Payemnt not found or it is in a previous tax year
3160 -- then report the period no in which employee has been terminated
3161 IF l_last_asg_action_id = -9999 THEN
3162 OPEN csr_get_term_period_no(l_termination_date, l_payroll_id);
3163 FETCH csr_get_term_period_no INTO l_period_no;
3164 CLOSE csr_get_term_period_no;
3165 hr_utility.trace('After csr_get_term_period_no, l_termination_date='||fnd_date.date_to_displaydate(l_termination_date));
3166 hr_utility.trace('l_payroll_id='||l_payroll_id);
3167 hr_utility.trace('l_period_no='||l_period_no);
3168 ELSE
3169 OPEN csr_period_number(l_last_pay_action_id);
3170 FETCH csr_period_number INTO l_period_no;
3171 CLOSE csr_period_number;
3172 hr_utility.trace('After csr_period_number, l_last_pay_action_id='||l_last_pay_action_id);
3173 hr_utility.trace('l_period_no='||l_period_no);
3174 END IF;
3175 --
3176 -- Get element id for PAYE element
3177 OPEN csr_paye_element;
3178 FETCH csr_paye_element INTO l_paye_element_id;
3179 CLOSE csr_paye_element;
3180
3181 -- BEGIN BUG FIX FOR 4595939 --
3182 hr_utility.trace('Date earned : ' || l_date_earned);
3183 hr_utility.trace('Final Process date : ' || l_final_process_date);
3184
3185 if l_date_earned = sysdate then
3186 l_eff_date := l_final_process_date;
3187 else
3188 l_eff_date := l_date_earned;
3189 end if;
3190
3191 get_tax_details(p_assignment_id => l_assignment_id,
3192 p_paye_details_id => g_paye_details_id,
3193 p_paye_id => l_paye_element_id,
3194 p_eff_date => l_eff_date,
3195 p_tax_code => l_tax_code,
3196 p_tax_basis => l_tax_basis,
3197 p_prev_pay => l_prev_pay_char,
3198 p_prev_tax => l_prev_tax_char);
3199 /*
3200 -- Look into run results of PAYE element for tax details
3201 -- Get input_value_id for Tax Code input value
3202 OPEN csr_input_value('Tax Code');
3203 FETCH csr_input_value INTO l_tax_code_ipv_id;
3204 CLOSE csr_input_value;
3205 -- Get input_value_id for Tax Basis input value
3206 OPEN csr_input_value('Tax Basis');
3207 FETCH csr_input_value INTO l_tax_basis_ipv_id;
3208 CLOSE csr_input_value;
3209 -- Get input_value_id for Pay Previous input value
3210 OPEN csr_input_value('Pay Previous');
3211 FETCH csr_input_value INTO l_pay_previous_ipv_id;
3212 CLOSE csr_input_value;
3213 -- Get input_value_id for Tax Previous input value
3214 OPEN csr_input_value('Tax Previous');
3215 FETCH csr_input_value INTO l_tax_previous_ipv_id;
3216 CLOSE csr_input_value;
3217 -- Get tax code from run results of PAYE element
3218 BEGIN
3219 -- fix bug 4545963
3220 -- default the tax details first
3221 OPEN csr_paye_details(l_assignment_id,l_date_earned);
3222 FETCH csr_paye_details INTO l_tax_code,
3223 l_tax_basis,
3224 l_prev_pay_char,
3225 l_prev_tax_char;
3226 CLOSE csr_paye_details;
3227
3228 -- If run result found, overwrite the value.
3229 -- Get max run_result_id for PAYE element
3230 OPEN csr_max_run_result;
3231 FETCH csr_max_run_result INTO l_max_run_result_id;
3232 CLOSE csr_max_run_result;
3233 -- if max run result found then get values from run result values else look at element entries
3234 IF l_max_run_result_id is not null THEN
3235 hr_utility.trace('Max run result found : ' || l_max_run_result_id);
3236 OPEN csr_result_value(l_tax_code_ipv_id);
3237 FETCH csr_result_value INTO l_tax_code_t;
3238 CLOSE csr_result_value;
3239 l_tax_code := nvl(l_tax_code_t, l_tax_code);
3240 --
3241 OPEN csr_result_value(l_tax_basis_ipv_id);
3242 FETCH csr_result_value INTO l_tax_basis_t;
3243 CLOSE csr_result_value;
3244 l_tax_basis := nvl(l_tax_basis_t, l_tax_basis);
3245 --
3246 OPEN csr_result_value(l_pay_previous_ipv_id);
3247 FETCH csr_result_value INTO l_prev_pay_char_t;
3248 CLOSE csr_result_value;
3249 l_prev_pay_char := nvl(l_prev_pay_char_t,l_prev_pay_char);
3250 --
3251 OPEN csr_result_value(l_tax_previous_ipv_id);
3252 FETCH csr_result_value INTO l_prev_tax_char_t;
3253 CLOSE csr_result_value;
3254 l_prev_tax_char := nvl(l_prev_tax_char_t,l_prev_tax_char);
3255 --
3256 END IF;
3257 */
3258 /*
3259 ELSE
3260 hr_utility.trace('Max run resuls not found');
3261 OPEN csr_paye_details(l_assignment_id,l_date_earned);
3262 FETCH csr_paye_details INTO l_tax_code,
3263 l_tax_basis,
3264 l_prev_pay_char,
3265 l_prev_tax_char;
3266 CLOSE csr_paye_details;
3267 END IF;
3268 */
3269 -- END;
3270 hr_utility.trace('tax code: '||l_tax_code||' '||l_tax_basis);
3271 --
3272 -- Get Balance items.
3273 -- Nb. parameter names of the following procedure are inconsistent with
3274 -- the actual values returned.
3275 PAY_P45_PKG2.get_balance_items(
3276 p_assignment_action_id => l_last_asg_action_id,
3277 p_gross_pay => l_taxable,
3278 p_taxable_pay => l_paye,
3279 p_agg_paye_flag => l_agg_paye_flag);
3280 --
3281 IF g_do_edi_validation THEN
3282 IF l_tax_basis = 'N' THEN
3283 IF l_taxable > 999999.99 THEN
3284 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3285 hr_utility.set_message_token('ITEM_NAME', 'Pay in this Employment');
3286 hr_utility.set_message_token('MAX_VALUE', '999999.99');
3287 hr_utility.raise_error;
3288 END IF;
3289 IF l_paye > 999999.99 THEN
3290 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3291 hr_utility.set_message_token('ITEM_NAME', 'Tax in this Employment');
3292 hr_utility.set_message_token('MAX_VALUE', '999999.99');
3293 hr_utility.raise_error;
3294 END IF;
3295 ELSE
3296 IF nvl(l_taxable,0) + nvl(to_number(l_prev_pay_char),0) > 999999.99
3297 THEN
3298 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3299 hr_utility.set_message_token('ITEM_NAME', 'Total pay to date');
3300 hr_utility.set_message_token('MAX_VALUE', '999999.99');
3301 hr_utility.raise_error;
3302 END IF;
3303 IF nvl(l_paye,0) + nvl(to_number(l_prev_tax_char),0) > 999999.99 THEN
3304 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3305 hr_utility.set_message_token('ITEM_NAME', 'Total tax to date');
3306 hr_utility.set_message_token('MAX_VALUE', '999999.99');
3307 hr_utility.raise_error;
3308 END IF;
3309 END IF; -- l_tax_basis = 'N'
3310 IF l_address_line1 IS NULL THEN
3311 hr_utility.set_message(801, 'PAY_GB_MISSING_VALUE');
3312 hr_utility.set_message_token('ITEM_NAME', 'Address');
3313 hr_utility.raise_error;
3314 END IF;
3315 IF l_tax_code IS NULL THEN
3316 hr_utility.set_message(801, 'PAY_GB_MISSING_VALUE');
3317 hr_utility.set_message_token('ITEM_NAME', 'Tax Code');
3318 hr_utility.raise_error;
3319 ELSIF length(ltrim(l_tax_code,'S')) > 6 THEN
3320 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3321 hr_utility.set_message_token('ITEM_NAME', 'Tax Code length');
3322 hr_utility.set_message_token('MAX_VALUE', '6 characters');
3323 hr_utility.raise_error;
3324 END IF;
3325 END IF; -- g_do_edi_validation
3326 -- Check Whether it is Week or Month
3327 OPEN csr_week_or_month(l_last_pay_action_id);
3328 FETCH csr_week_or_month INTO l_week_or_month;
3329 CLOSE csr_week_or_month;
3330 --
3331 -- Archive info obtained so far
3332 archive_asg_info(g_student_loan_flag_eid, l_student_loan_flag);
3333 IF l_week_or_month = 'W' THEN
3334 archive_asg_info(g_week_number_eid, l_period_no);
3335 ELSE
3336 archive_asg_info(g_month_number_eid, l_period_no);
3337 END IF;
3338 archive_asg_info(g_tax_code_eid, l_tax_code);
3339 IF l_tax_basis = 'N' THEN
3340 archive_asg_info(g_w1_m1_indicator_eid, 'X');
3341 END IF;
3342 archive_asg_info(g_prev_taxable_pay_eid, l_prev_pay_char);
3343 archive_asg_info(g_prev_tax_paid_eid, l_prev_tax_char);
3344 archive_asg_info(g_taxable_pay_eid,
3345 fnd_number.number_to_canonical(l_taxable));
3346 archive_asg_info(g_tax_paid_eid,
3347 fnd_number.number_to_canonical(l_paye));
3348 --
3349 hr_utility.set_location(' Leaving: '||l_proc,999);
3350 --hr_utility.trace_off;
3351 END archive_code;
3352 --
3353 PROCEDURE spawn_reports
3354 IS
3355 l_proc CONSTANT VARCHAR2(35):= g_package||'spawn_reports';
3356 --
3357 l_count NUMBER := 0;
3358 l_dummy NUMBER;
3359 l_print_style VARCHAR2(2);
3360 l_report_short_name VARCHAR2(20);
3361 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
3362 l_number_of_copies fnd_concurrent_requests.number_of_copies%TYPE;
3363 l_request_id NUMBER:=-1;
3364 l_formula_id ff_formulas_f.formula_id%TYPE;
3365 --
3366 l_print_together VARCHAR2(80);
3367 l_print_return BOOLEAN;
3368 --
3369 cursor csr_get_formula_id(p_formula_name VARCHAR2) IS
3370 SELECT a.formula_id
3371 FROM ff_formulas_f a,
3372 ff_formula_types t
3373 WHERE a.formula_name = p_formula_name
3374 AND business_group_id IS NULL
3375 AND legislation_code = 'GB'
3376 AND a.formula_type_id = t.formula_type_id
3377 AND t.formula_type_name = 'Oracle Payroll';
3378 --
3379 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3380 SELECT printer,
3381 print_style,
3382 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3383 FROM pay_payroll_actions pact,
3384 fnd_concurrent_requests fcr
3385 WHERE fcr.request_id = pact.request_id
3386 AND pact.payroll_action_id = p_payroll_action_id;
3387 --
3388 cursor get_errored_actions(c_payroll_action_id number) is
3389 select 1 from dual where exists
3390 (select action_status
3391 from pay_assignment_actions
3392 where payroll_action_id = c_payroll_action_id
3393 and action_status = 'E');
3394 --
3395 rec_print_options csr_get_print_options%ROWTYPE;
3396 --
3397 BEGIN
3398 hr_utility.set_location('Entering: '||l_proc,1);
3399 -- get pertinent SRS parameters from the pay_mag_tape tables
3400 BEGIN
3401 LOOP
3402 l_count := l_count + 1;
3403 IF pay_mag_tape.internal_prm_names(l_count) =
3404 'TRANSFER_PAYROLL_ACTION_ID'
3405 THEN
3406 l_payroll_action_id := to_number(pay_mag_tape.internal_prm_values
3407 (l_count));
3408 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'P45'
3409 THEN
3410 l_print_style := pay_mag_tape.internal_prm_values(l_count);
3411 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'PDEF'
3412 THEN
3413 IF pay_mag_tape.internal_prm_values(l_count) = 'Y' THEN
3414 -- Defer printing param set to Y
3415 l_number_of_copies := 0;
3416 ELSE
3417 l_number_of_copies := 1;
3418 END IF;
3419 END IF;
3420 END LOOP;
3421 EXCEPTION
3422 WHEN no_data_found THEN
3423 -- Use this exception to exit loop as no. of plsql tab items
3424 -- is not known beforehand. All values should be assigned.
3425 NULL;
3426 WHEN value_error THEN
3427 NULL;
3428 END;
3429 --
3430 -- Check no assignment actions were errored
3431 open get_errored_actions(l_payroll_action_id);
3432 fetch get_errored_actions into l_dummy;
3433 if get_errored_actions%notfound then
3434 -- No errors, so set up print options and spawn report.
3435 IF l_print_style = 'L'
3436 THEN l_report_short_name := 'PAYGB45L';
3437 ELSE l_report_short_name := 'PAYRPP45';
3438 END IF;
3439 --
3440 hr_utility.set_location(l_proc,10);
3441 -- Get printer options from archive request
3442 OPEN csr_get_print_options(l_payroll_action_id);
3443 FETCH csr_get_print_options INTO rec_print_options;
3444 CLOSE csr_get_print_options;
3445 --
3446 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
3447 --
3448 -- Set printer options
3449 l_print_return := fnd_request.set_print_options
3450 (printer => rec_print_options.printer,
3451 style => rec_print_options.print_style,
3452 copies => l_number_of_copies,
3453 save_output => hr_general.char_to_bool
3454 (rec_print_options.save_output),
3455 print_together => l_print_together);
3456 hr_utility.trace('Print options set call returned: '||
3457 hr_general.bool_to_char(l_print_return));
3458 -- Submit report
3459 l_request_id := fnd_request.submit_request
3460 (application => 'PAY',
3461 program => l_report_short_name,
3462 argument1 => l_payroll_action_id);
3463 --
3464 IF l_request_id = 0 THEN
3465 g_fnd_rep_request_msg := fnd_message.get;
3466 END IF; --
3467 end if; -- get_errored_actions%notfound
3468 --
3469 close get_errored_actions;
3470 -- Set up formula inputs
3471 hr_utility.set_location(l_proc,20);
3472 OPEN csr_get_formula_id('PAY_GB_P45_REPORT_SUBMISSION');
3473 FETCH csr_get_formula_id INTO l_formula_id;
3474 CLOSE csr_get_formula_id;
3475 --
3476 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
3477 pay_mag_tape.internal_prm_values(1) := '4';
3478 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
3479 pay_mag_tape.internal_prm_values(2) := to_char(l_formula_id);
3480 pay_mag_tape.internal_prm_names(3) := 'P45_REQUEST_ID';
3481 pay_mag_tape.internal_prm_values(3) := to_char(l_request_id);
3482 pay_mag_tape.internal_prm_names(4) := 'PAYROLL_ACTION_ID';
3483 pay_mag_tape.internal_prm_values(4) := to_char(l_payroll_action_id);
3484 --
3485 -- Exit procedure, C code will fire formula
3486 hr_utility.set_location(' Leaving: '||l_proc,999);
3487 END spawn_reports;
3488 --
3489 PROCEDURE edi_act_creation(pactid IN NUMBER,
3490 stperson IN NUMBER,
3491 endperson IN NUMBER,
3492 chunk IN NUMBER)
3493 IS
3494 l_proc CONSTANT VARCHAR2(35):= g_package||'edi_act_creation';
3495 l_actid pay_assignment_actions.assignment_action_id%TYPE;
3496 --
3497 cursor csr_user_entity(p_entity_name VARCHAR2) IS
3498 SELECT user_entity_id
3499 FROM ff_user_entities
3500 WHERE user_entity_name = p_entity_name
3501 AND legislation_code = 'GB'
3502 AND business_group_id IS NULL;
3503 --
3504 cursor csr_parameter_info(p_payroll_action_id NUMBER) IS
3505 SELECT
3506 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
3507 'PAYROLL_ID')) payroll_id,
3508 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
3509 'TAX_REF'),1,20) tax_ref,
3510 fnd_date.canonical_to_date
3511 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
3512 'DATE_FROM')) start_date,
3513 effective_date end_date,
3514 business_group_id
3515 FROM pay_payroll_actions
3516 WHERE payroll_action_id = p_payroll_action_id;
3517 --
3518 cursor csr_assignments IS
3519 -- Do not restrict to the last P45 archive action for each employee - get
3520 -- all that are not interlocked
3521 -- Restrict on payroll_id legislative parameter as necessary. If payroll
3522 -- not specified on Archive submission, token will not appear in the
3523 -- legislative parameters, so ensure substr returns null by concatenating
3524 -- the token.
3525 -- If EDI process is restricted by payroll but archive wasn't, need to
3526 -- drill down to asg level and restrict on the payroll_id in the archive.
3527 -- Performance fix bug 5202965
3528 SELECT /*+ ORDERED */
3529 DISTINCT
3530 act.assignment_action_id archive_action,
3531 act.assignment_id
3532 FROM pay_payroll_actions pact,
3533 pay_assignment_actions act,
3534 per_assignments_f paf,
3535 pay_action_interlocks pai,
3536 ff_archive_items fai
3537 WHERE pact.report_type ='P45'
3538 AND pact.report_qualifier ='GB'
3539 AND pact.report_category ='P45'
3540 AND pact.action_status = 'C'
3541 AND pact.action_type = 'X'
3542 AND pact.business_group_id +0 = g_business_group_id
3543 AND pact.effective_date BETWEEN g_start_date AND g_end_date
3544 AND substr(pact.legislative_parameters,
3545 instr(pact.legislative_parameters,'TAX_REF=') + 8,
3546 instr(pact.legislative_parameters||' ',' ',
3547 instr(pact.legislative_parameters,'TAX_REF=')+8)
3548 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
3549 = g_tax_ref
3550 AND (g_payroll_id IS NULL
3551 OR
3552 nvl(substr(pact.legislative_parameters,
3553 instr(pact.legislative_parameters||' PAYROLL_ID='
3554 ,'PAYROLL_ID=') + 11,
3555 instr(pact.legislative_parameters||' ',' ',
3556 instr(pact.legislative_parameters,'PAYROLL_ID=')+11)
3557 - instr(pact.legislative_parameters,'PAYROLL_ID=') - 11),
3558 nvl(to_char(g_payroll_id),'x')) = nvl(to_char(g_payroll_id),'x'))
3559 /* restrict by payroll_id in archive */
3560 AND fai.context1 (+) = act.assignment_action_id
3561 AND fai.user_entity_id (+) = g_payroll_id_eid
3562 AND nvl(fai.VALUE,nvl(to_char(g_payroll_id),'x'))
3563 = nvl(nvl(to_char(g_payroll_id),fai.VALUE),'x')
3564 AND pact.payroll_action_id = act.payroll_action_id
3565 AND paf.assignment_id = act.assignment_id
3566 AND paf.person_id BETWEEN stperson AND endperson
3567 AND paf.business_group_id +0 = g_business_group_id
3568 /* restrict to one row per asg. */
3569 -- Comment out this code as it will be replace by distinct
3570 --AND paf.effective_start_date =
3571 -- (SELECT max(paf2.effective_start_date)
3572 -- FROM per_assignments_f paf2
3573 -- WHERE paf2.assignment_id = paf.assignment_id)
3574 /* commnet out this code and replace by the code below */
3575 --AND NOT EXISTS (SELECT 1
3576 -- FROM pay_action_interlocks pai
3577 -- WHERE pai.locked_action_id = act.assignment_action_id);
3578 AND pai.locked_action_id(+) = act.assignment_action_id
3579 AND decode(pai.locked_action_id,null,1,2) = 1;
3580 --
3581 cursor csr_range_assignments is
3582 --
3583 -- This is a copy of csr_assignments above except with a join to pay_
3584 -- population_ranges for performance enhancement.
3585 --
3586 -- Performance fix
3587 SELECT /*+ ORDERED
3588 USE_NL(act,pai)
3589 INDEX(act PAY_ASSIGNMENT_ACTIONS_N50) */
3590 DISTINCT
3591 act.assignment_action_id archive_action,
3592 act.assignment_id
3593 from pay_payroll_actions pact,
3594 pay_assignment_actions act,
3595 per_assignments_f paf,
3596 pay_action_interlocks pai,
3597 pay_population_ranges ppr,
3598 ff_archive_items fai
3599 WHERE pact.report_type ='P45'
3600 AND pact.report_qualifier ='GB'
3601 AND pact.report_category ='P45'
3602 AND pact.action_status = 'C'
3603 AND pact.action_type = 'X'
3604 AND pact.business_group_id +0 = g_business_group_id
3605 AND pact.effective_date BETWEEN g_start_date AND g_end_date
3606 AND substr(pact.legislative_parameters,
3607 instr(pact.legislative_parameters,'TAX_REF=') + 8,
3608 instr(pact.legislative_parameters||' ',' ',
3609 instr(pact.legislative_parameters,'TAX_REF=')+8)
3610 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
3611 = g_tax_ref
3612 AND (g_payroll_id IS NULL
3613 OR
3614 nvl(substr(pact.legislative_parameters,
3615 instr(pact.legislative_parameters||' PAYROLL_ID='
3616 ,'PAYROLL_ID=') + 11,
3617 instr(pact.legislative_parameters||' ',' ',
3618 instr(pact.legislative_parameters,'PAYROLL_ID=')+11)
3619 - instr(pact.legislative_parameters,'PAYROLL_ID=') - 11),
3620 nvl(to_char(g_payroll_id),'x')) = nvl(to_char(g_payroll_id),'x'))
3621 /* restrict by payroll_id in archive */
3622 AND fai.context1 (+) = act.assignment_action_id
3623 AND fai.user_entity_id (+) = g_payroll_id_eid
3624 AND nvl(fai.VALUE,nvl(to_char(g_payroll_id),'x'))
3625 = nvl(nvl(to_char(g_payroll_id),fai.VALUE),'x')
3626 AND pact.payroll_action_id = act.payroll_action_id
3627 AND paf.assignment_id = act.assignment_id
3628 AND paf.person_id = ppr.person_id
3629 AND ppr.chunk_number = chunk
3630 AND ppr.payroll_action_id = pactid
3631 AND paf.business_group_id +0 = g_business_group_id
3632 /* restrict to one row per asg. */
3633 /* Comment out the code for performance fix */
3634 --AND paf.effective_start_date =
3635 -- (SELECT max(paf2.effective_start_date)
3636 -- FROM per_assignments_f paf2
3637 -- WHERE paf2.assignment_id = paf.assignment_id)
3638 --AND NOT EXISTS (SELECT 1
3639 -- FROM pay_action_interlocks pai
3640 -- WHERE pai.locked_action_id = act.assignment_action_id);
3641 and pai.locked_action_id(+) = act.assignment_action_id
3642 and decode(pai.locked_action_id, null,1,2) = 1;
3643 BEGIN
3644 hr_utility.set_location('Entering: '||l_proc,1);
3645 --
3646 IF NOT g_asg_creation_cache_populated THEN
3647 OPEN csr_user_entity('X_PAYROLL_ID');
3648 FETCH csr_user_entity INTO g_payroll_id_eid;
3649 CLOSE csr_user_entity;
3650 --
3651 OPEN csr_parameter_info(pactid);
3652 FETCH csr_parameter_info INTO g_payroll_id,
3653 g_tax_ref,
3654 g_start_date,
3655 g_end_date,
3656 g_business_group_id;
3657 CLOSE csr_parameter_info;
3658 --
3659 g_asg_creation_cache_populated := true;
3660 END IF;
3661 --
3662 hr_utility.trace('Payroll ID : ' || g_payroll_id);
3663 hr_utility.trace('Tax Ref : ' || g_tax_ref);
3664 hr_utility.trace('Start : ' || g_start_date);
3665 hr_utility.trace('End : ' || g_end_date);
3666 hr_utility.trace('Bus ID : ' || g_business_group_id);
3667 hr_utility.trace('Chunk : ' || chunk);
3668 hr_utility.trace('EID : ' || g_payroll_id_eid);
3669 IF range_person_on('PAY_GB_P45_EDI') then
3670 --
3671 -- Range Person functionality enabled, use new cursor.
3672 --
3673 hr_utility.set_location(l_proc,20);
3674 FOR rec_asg IN csr_range_assignments LOOP
3675 --
3676 hr_utility.set_location(l_proc,25);
3677 SELECT pay_assignment_actions_s.nextval
3678 INTO l_actid
3679 FROM dual;
3680 --
3681 hr_utility.set_location(l_proc,27);
3682 hr_nonrun_asact.insact(l_actid,rec_asg.assignment_id,
3683 pactid,chunk,NULL);
3684 -- Interlock the archive action
3685 hr_utility.set_location(l_proc,29);
3686 hr_nonrun_asact.insint(l_actid, rec_asg.archive_action);
3687 END LOOP;
3688 --
3689 ELSE
3690 --
3691 -- Range Person functionality not enabled, use original cursor
3692 --
3693 hr_utility.set_location(l_proc,30);
3694 FOR rec_asg IN csr_assignments LOOP
3695 --
3696 SELECT pay_assignment_actions_s.nextval
3697 INTO l_actid
3698 FROM dual;
3699 --
3700 hr_nonrun_asact.insact(l_actid,rec_asg.assignment_id,
3701 pactid,chunk,NULL);
3702 -- Interlock the archive action
3703 hr_nonrun_asact.insint(l_actid, rec_asg.archive_action);
3704 END LOOP;
3705 --
3706 END IF; -- range person check.
3707 --
3708 hr_utility.set_location(' Leaving: '||l_proc,999);
3709 END edi_act_creation;
3710
3711 --
3712 -- Populate P45 form with archived information where appropriate
3713 -- archived info exists
3714 --
3715 procedure pop_term_asg_from_archive(X_ASSIGNMENT_ACTION_ID in number,
3716 X_NI1 in out nocopy varchar2,
3717 X_NI2 in out nocopy varchar2,
3718 X_NI3 in out nocopy varchar2,
3719 X_NI4 in out nocopy varchar2,
3720 X_NI5 in out nocopy varchar2,
3721 X_NI6 in out nocopy varchar2,
3722 X_NI7 in out nocopy varchar2,
3723 X_NI8 in out nocopy varchar2,
3724 X_NI9 in out nocopy varchar2,
3725 X_LAST_NAME in out nocopy varchar2,
3726 X_TITLE in out nocopy varchar2,
3727 X_FIRST_NAME in out nocopy varchar2,
3728 X_DATE_OF_LEAVING_DD in out nocopy varchar2,
3729 X_DATE_OF_LEAVING_MM in out nocopy varchar2,
3730 X_DATE_OF_LEAVING_YY in out nocopy varchar2,
3731 X_TAX_CODE_AT_LEAVING in out nocopy varchar2,
3732 X_WK1_OR_MTH1 in out nocopy varchar2,
3733 X_WEEK_NO in out nocopy varchar2,
3734 X_MONTH_NO in out nocopy varchar2,
3735 X_PAY_TD_POUNDS in out nocopy number,
3736 X_PAY_TD_PENCE in out nocopy number,
3737 X_TAX_TD_POUNDS in out nocopy number,
3738 X_TAX_TD_PENCE in out nocopy number,
3739 X_PAY_IN_EMP_POUNDS in out nocopy number,
3740 X_PAY_IN_EMP_PENCE in out nocopy number,
3741 X_TAX_IN_EMP_POUNDS in out nocopy number,
3742 X_TAX_IN_EMP_PENCE in out nocopy number,
3743 X_ASSIGNMENT_NUMBER in out nocopy varchar2,
3744 X_ORG_NAME in out nocopy varchar2,
3745 X_ADDRESS_LINE1 in out nocopy varchar2,
3746 X_ADDRESS_LINE2 in out nocopy varchar2,
3747 X_ADDRESS_LINE3 in out nocopy varchar2,
3748 X_TOWN_OR_CITY in out nocopy varchar2,
3749 X_REGION_1 in out nocopy varchar2,
3750 X_POSTAL_CODE in out nocopy varchar2,
3751 X_DECEASED_FLAG in out nocopy varchar2,
3752 X_ISSUE_DATE in out nocopy varchar2,
3753 X_TAX_REF_TRANSFER in out nocopy varchar2,
3754 X_STUDENT_LOAN_FLAG in out nocopy varchar2,
3755 X_COUNTRY in out nocopy varchar2)
3756 is
3757 cursor cur_get_asg_archive_items is
3758 select nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),1,1),' ') NINO1,
3759 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),2,1),' ') NINO2,
3760 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),3,1),' ') NINO3,
3761 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),4,1),' ') NINO4,
3762 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),5,1),' ') NINO5,
3763 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),6,1),' ') NINO6,
3764 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),7,1),' ') NINO7,
3765 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),8,1),' ') NINO8,
3766 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),9,1),' ') NINO9,
3767 nvl(max(decode(fue.user_entity_name,'X_LAST_NAME',fai.VALUE)),' ') LAST_NAME,
3768 nvl(max(decode(fue.user_entity_name,'X_TITLE',fai.VALUE)),' ') TITLE,
3769 nvl(max(decode(fue.user_entity_name,'X_FIRST_NAME',fai.VALUE)),' ') FIRST_NAME,
3770 nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_TERMINATION_DATE',fai.VALUE))),'DD-MM-YYYY'),1,2),' ') DATE_OF_LEAVING_DD,
3771 nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_TERMINATION_DATE',fai.VALUE))),'DD-MM-YYYY'),4,2),' ') DATE_OF_LEAVING_MM,
3772 nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_TERMINATION_DATE',fai.VALUE))),'DD-MM-YYYY'),7,4),' ') DATE_OF_LEAVING_YY,
3773 nvl(max(decode(fue.user_entity_name,'X_TAX_CODE',fai.VALUE)),' ') TAX_CODE,
3774 nvl(max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR',fai.VALUE)),' ') W1_M1_IND,
3775 nvl(max(decode(fue.user_entity_name,'X_WEEK_NUMBER',fai.VALUE)),' ') WEEK_NO,
3776 nvl(max(decode(fue.user_entity_name,'X_MONTH_NUMBER',fai.VALUE)),' ') MONTH_NO,
3777 trunc(nvl(max(decode(fue.user_entity_name,'X_TAXABLE_PAY',fai.VALUE)),0)) PAY_TD_POUNDS,
3778 mod(nvl((max(decode(fue.user_entity_name,'X_TAXABLE_PAY',fai.VALUE))*100),0),100) PAY_TD_PENCE,
3779 trunc(nvl(max(decode(fue.user_entity_name,'X_TAX_PAID',fai.VALUE)),0)) TAX_TD_POUNDS,
3780 mod(nvl((max(decode(fue.user_entity_name,'X_TAX_PAID',fai.VALUE))*100),0),100) TAX_TD_PENCE,
3781 trunc(nvl(max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',fai.value)),0)) PREV_PAY_IN_POUNDS,
3782 mod(nvl((max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',fai.value))*100),0),100) PREV_PAY_IN_PENCE,
3783 trunc(nvl(max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',fai.value)),0)) PREV_TAX_IN_POUNDS,
3784 mod(nvl((max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',fai.value))*100),0),100) PREV_TAX_IN_PENCE,
3785 upper(nvl(max(decode(fue.user_entity_name,'X_ASSIGNMENT_NUMBER',fai.VALUE)),' ')) ASSIGNMENT_NUMBER,
3786 upper(nvl(max(decode(fue.user_entity_name,'X_ORGANIZATION_NAME',fai.VALUE)),' ')) ORGANIZATION_NAME,
3787 upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE1',fai.VALUE)),' ')) ADDRESS_LINE1,
3788 upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE2',fai.VALUE)),' ')) ADDRESS_LINE2,
3789 upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE3',fai.VALUE)),' ')) ADDRESS_LINE3,
3790 upper(nvl(max(decode(fue.user_entity_name,'X_TOWN_OR_CITY',fai.VALUE)),' ')) TOWN_OR_CITY,
3791 upper(nvl(max(decode(fue.user_entity_name,'X_COUNTY',fai.VALUE)),' ')) COUNTY,
3792 upper(nvl(max(decode(fue.user_entity_name,'X_POSTAL_CODE',fai.VALUE)),' ')) POSTAL_CODE,
3793 upper(nvl(max(decode(fue.user_entity_name,'X_DECEASED_FLAG',fai.VALUE)),' ')) DECEASED_FLAG,
3794 nvl(max(decode(fue.user_entity_name,'X_ISSUE_DATE',fai.VALUE)),' ') ISSUE_DATE,
3795 upper(nvl(max(decode(fue.user_entity_name,'X_TAX_REF_TRANSFER',fai.VALUE)),' ')) TAX_REF_TRANSFER,
3796 upper(nvl(max(decode(fue.user_entity_name,'X_STUDENT_LOAN_FLAG',fai.VALUE)),' ')) STUDENT_LOAN_FLAG,
3797 upper(nvl(max(decode(fue.user_entity_name,'X_COUNTRY',fai.VALUE)),' ')) COUNTRY
3798 from ff_archive_items fai,
3799 ff_user_entities fue
3800 where x_assignment_action_id = fai.context1
3801 and fai.archive_type <>'PA'
3802 and fai.user_entity_id = fue.user_entity_id;
3803 --
3804 l_cur_get_asg_archive_items cur_get_asg_archive_items%ROWTYPE;
3805 l_pay_in_emp number(10,2);
3806 l_tax_in_emp number(10,2);
3807 l_prev_pay number(10,2);
3808 l_prev_tax number(10,2);
3809 l_total_pay_pounds number(7);
3810 l_total_pay_pence number(2);
3811 l_total_tax_pounds number(7);
3812 l_total_tax_pence number(2);
3813 --
3814 BEGIN
3815 OPEN cur_get_asg_archive_items;
3816 FETCH cur_get_asg_archive_items into l_cur_get_asg_archive_items;
3817 IF cur_get_asg_archive_items%NOTFOUND THEN
3818 null;
3819 ELSE
3820 l_pay_in_emp := (l_cur_get_asg_archive_items.pay_td_pounds + (l_cur_get_asg_archive_items.pay_td_pence/100));
3821 hr_utility.trace('l_pay_in_emp : '||to_char(l_pay_in_emp));
3822 l_tax_in_emp := (l_cur_get_asg_archive_items.tax_td_pounds + (l_cur_get_asg_archive_items.tax_td_pence/100));
3823 hr_utility.trace('l_tax_in_emp : '||to_char(l_tax_in_emp));
3824 l_prev_pay := (l_cur_get_asg_archive_items.prev_pay_in_pounds + (l_cur_get_asg_archive_items.prev_pay_in_pence/100));
3825 hr_utility.trace('l_prev_pay : '||to_char(l_prev_pay));
3826 l_prev_tax := (l_cur_get_asg_archive_items.prev_tax_in_pounds + (l_cur_get_asg_archive_items.prev_tax_in_pence/100));
3827 hr_utility.trace('l_prev_tax : '||to_char(l_prev_tax));
3828 l_total_pay_pounds := trunc(l_pay_in_emp + l_prev_pay);
3829 hr_utility.trace('l_total_pay_pounds : '||to_char(l_total_pay_pounds));
3830 l_total_pay_pence := (mod((l_pay_in_emp + l_prev_pay),1)*100);
3831 hr_utility.trace('l_total_pay_pence : '||to_char(l_total_pay_pence));
3832 l_total_tax_pounds := trunc(l_tax_in_emp + l_prev_tax);
3833 hr_utility.trace('l_total_tax_pounds : '||to_char(l_total_tax_pounds));
3834 l_total_tax_pence := (mod((l_tax_in_emp + l_prev_tax),1)*100);
3835 hr_utility.trace('l_total_tax_pence : '||to_char(l_total_tax_pence));
3836 x_ni1 := l_cur_get_asg_archive_items.nino1;
3837 x_ni2 := l_cur_get_asg_archive_items.nino2;
3838 x_ni3 := l_cur_get_asg_archive_items.nino3;
3839 x_ni4 := l_cur_get_asg_archive_items.nino4;
3840 x_ni5 := l_cur_get_asg_archive_items.nino5;
3841 x_ni6 := l_cur_get_asg_archive_items.nino6;
3842 x_ni7 := l_cur_get_asg_archive_items.nino7;
3843 x_ni8 := l_cur_get_asg_archive_items.nino8;
3844 x_ni9 := l_cur_get_asg_archive_items.nino9;
3845 x_last_name := l_cur_get_asg_archive_items.last_name;
3846 x_title := l_cur_get_asg_archive_items.title;
3847 x_first_name := l_cur_get_asg_archive_items.first_name;
3848 x_date_of_leaving_dd := l_cur_get_asg_archive_items.date_of_leaving_dd;
3849 x_date_of_leaving_mm := l_cur_get_asg_archive_items.date_of_leaving_mm;
3850 x_date_of_leaving_yy := l_cur_get_asg_archive_items.date_of_leaving_yy;
3851 x_tax_code_at_leaving := l_cur_get_asg_archive_items.tax_code;
3852 x_wk1_or_mth1 := l_cur_get_asg_archive_items.w1_m1_ind;
3853 x_week_no := l_cur_get_asg_archive_items.week_no;
3854 x_month_no := l_cur_get_asg_archive_items.month_no;
3855 x_pay_td_pounds := l_total_pay_pounds;
3856 x_pay_td_pence := l_total_pay_pence;
3857 x_tax_td_pounds := l_total_tax_pounds;
3858 x_tax_td_pence := l_total_tax_pence;
3859 x_pay_in_emp_pounds := l_cur_get_asg_archive_items.pay_td_pounds;
3860 x_pay_in_emp_pence := l_cur_get_asg_archive_items.pay_td_pence;
3861 x_tax_in_emp_pounds := l_cur_get_asg_archive_items.tax_td_pounds;
3862 x_tax_in_emp_pence := l_cur_get_asg_archive_items.tax_td_pence;
3863 x_assignment_number := l_cur_get_asg_archive_items.assignment_number;
3864 x_org_name := l_cur_get_asg_archive_items.organization_name;
3865 x_address_line1 := l_cur_get_asg_archive_items.address_line1;
3866 x_address_line2 := l_cur_get_asg_archive_items.address_line2;
3867 x_address_line3 := l_cur_get_asg_archive_items.address_line3;
3868 x_town_or_city := l_cur_get_asg_archive_items.town_or_city;
3869 x_region_1 := l_cur_get_asg_archive_items.county;
3870 x_postal_code := l_cur_get_asg_archive_items.postal_code;
3871 x_deceased_flag := l_cur_get_asg_archive_items.deceased_flag;
3872 x_issue_date := l_cur_get_asg_archive_items.issue_date;
3873 x_tax_ref_transfer := l_cur_get_asg_archive_items.tax_ref_transfer;
3874 x_student_loan_flag := l_cur_get_asg_archive_items.student_loan_flag;
3875 x_country := l_cur_get_asg_archive_items.country;
3876 END IF;
3877 end pop_term_asg_from_archive;
3878
3879 Procedure pop_term_pact_from_archive (X_PAYROLL_ACTION_ID in number,
3880 X_EMPLOYER_NAME in out nocopy varchar2,
3881 X_EMPLOYER_ADDRESS in out nocopy varchar2)
3882 is
3883 cursor cur_get_pact_archive_items is
3884 select upper(nvl(max(decode(fue.user_entity_name,'X_EMPLOYERS_NAME',fai.VALUE)),null)) EMPLOYERS_NAME,
3885 upper(nvl(max(decode(fue.user_entity_name,'X_EMPLOYERS_ADDRESS_LINE',fai.VALUE)),null)) EMPLOYERS_ADDRESS
3886 from ff_archive_item_contexts aic,
3887 ff_archive_items fai,
3888 ff_user_entities fue
3889 where X_PAYROLL_ACTION_ID = fai.context1
3890 and fai.user_entity_id = fue.user_entity_id
3891 and fai.archive_item_id = aic.archive_item_id
3892 and aic.context = '0'
3893 and aic.sequence_no = 1;
3894 --
3895 l_cur_get_pact_archive_items cur_get_pact_archive_items%ROWTYPE;
3896 --
3897 BEGIN
3898 OPEN cur_get_pact_archive_items;
3899 FETCH cur_get_pact_archive_items into l_cur_get_pact_archive_items;
3900 IF cur_get_pact_archive_items%NOTFOUND THEN
3901 null;
3902 ELSE
3903 x_employer_name := l_cur_get_pact_archive_items.employers_name;
3904 x_employer_address := l_cur_get_pact_archive_items.employers_address;
3905 END IF;
3906 END pop_term_pact_from_archive;
3907 --------------------------------------------------------------------------
3908 -- PROCEDURE get_p45_asg_action_id
3909 -- DESCRIPTION Get the P45 Assignment Action id, Issue Date
3910 --------------------------------------------------------------------------
3911 PROCEDURE get_p45_asg_action_id(p_assignment_id in number,
3912 p_assignment_action_id out nocopy number,
3913 p_issue_date out nocopy date,
3914 p_action_sequence out nocopy number
3915 ) IS
3916 --
3917 CURSOR csr_get_p45_action(c_assignment_id NUMBER) IS
3918 SELECT act.assignment_action_id, pact.effective_date, act.action_sequence
3919 FROM pay_assignment_actions act, pay_payroll_actions pact
3920 WHERE act.assignment_id = c_assignment_id
3921 AND act.payroll_action_id = pact.payroll_action_id
3922 AND pact.REPORT_QUALIFIER = 'GB'
3923 AND pact.ACTION_TYPE = 'X'
3924 AND act.action_status = 'C'
3925 AND report_type = 'P45'
3926 AND report_category = 'P45';
3927
3928 CURSOR csr_transfer_p45(c_assignment_action_id NUMBER) IS
3929 SELECT fai.VALUE
3930 FROM ff_archive_items fai, ff_user_entities fue
3931 WHERE fai.user_entity_id = fue.user_entity_id
3932 AND fue.user_entity_name = 'X_TAX_REF_TRANSFER'
3933 AND fue.legislation_code = 'GB'
3934 AND fue.business_group_id IS NULL
3935 AND fai.context1 = c_assignment_action_id ;
3936
3937 l_transfer_flag ff_archive_items.value%type;
3938 --
3939 BEGIN
3940 --
3941 open csr_get_p45_action(p_assignment_id);
3942 loop
3943 p_assignment_action_id := null;
3944 p_issue_date := null;
3945 p_action_sequence := null;
3946
3947 fetch csr_get_p45_action into p_assignment_action_id, p_issue_date, p_action_sequence;
3948 exit when csr_get_p45_action%notfound;
3949 --
3950 open csr_transfer_p45(p_assignment_action_id);
3951 fetch csr_transfer_p45 into l_transfer_flag;
3952 if csr_transfer_p45%notfound or nvl(l_transfer_flag,'N') = 'N' then
3953 close csr_transfer_p45;
3954 exit;
3955 end if;
3956 close csr_transfer_p45;
3957 --
3958 end loop;
3959 close csr_get_p45_action;
3960 --
3961
3962 END get_p45_asg_action_id;
3963 --------------------------------------------------------------------------
3964 -- FUNCTION get_p45_eit_manual_issue_dt
3965 -- DESCRIPTION Get the P45 Manual Issue date from Extra Info. table
3966 --------------------------------------------------------------------------
3967 FUNCTION get_p45_eit_manual_issue_dt(p_assignment_id in number) RETURN DATE IS
3968 --
3969 l_manual_issue_date date;
3970 CURSOR csr_get_p45_eit_dtls(c_assignment_id NUMBER) IS
3971 select fnd_date.canonical_to_date(aei_information3)
3972 from per_assignment_extra_info
3973 where assignment_id = c_assignment_id
3974 and information_type = 'GB_P45';
3975 --
3976 BEGIN
3977 open csr_get_p45_eit_dtls(p_assignment_id);
3978 fetch csr_get_p45_eit_dtls into l_manual_issue_date;
3979 if csr_get_p45_eit_dtls%NOTFOUND then
3980 l_manual_issue_date := null;
3981 end if;
3982 close csr_get_p45_eit_dtls;
3983 --
3984 RETURN l_manual_issue_date;
3985 END get_p45_eit_manual_issue_dt;
3986 --------------------------------------------------------------------------
3987 -- PROCEDURE get_p45_agg_asg_action_id
3988 -- DESCRIPTION Get the Aggregated Assignment Id, Assignment Action id,
3989 -- Final Payment Date for which the P45 been issued
3990 --------------------------------------------------------------------------
3991 PROCEDURE get_p45_agg_asg_action_id(p_assignment_id in number,
3992 p_agg_assignment_id out nocopy number,
3993 p_final_payment_date out nocopy date,
3994 p_p45_issue_date out nocopy date,
3995 p_p45_agg_asg_action_id out nocopy number
3996 ) IS
3997 --
3998 Cursor csr_get_all_asg(c_assignment_id NUMBER) IS
3999 select distinct asg1.assignment_id, asg1.person_id
4000 from per_all_assignments_f asg1, per_all_assignments_f asg2
4001 where asg2.assignment_id = c_assignment_id
4002 and asg2.person_id = asg1.person_id
4003 and asg1.assignment_id <> p_assignment_id;
4004
4005 /*
4006 Cursor csr_get_final_payment(c_assignment_id NUMBER, c_asg_action_id NUMBER, c_action_sequence NUMBER) IS
4007 select pact1.payroll_action_id, pact1.effective_date final_payment_date
4008 from FF_ARCHIVE_ITEMS ai,
4009 ff_user_entities ue,
4010 pay_payroll_actions pact1
4011 WHERE ue.user_entity_name in ('X_MONTH_NUMBER', 'X_WEEK_NUMBER') -- for the weekly frequency (and multiples)
4012 AND ue.legislation_code = 'GB'
4013 AND ue.business_group_id IS NULL
4014 and ue.user_entity_id = ai.user_entity_id
4015 and ai.archive_type = 'AAP'
4016 and ai.context1 = c_asg_action_id
4017 and pact1.payroll_action_id =
4018 (
4019 select to_number(substr(max(lpad(to_char(act.action_sequence), 20, '0')||to_char(pact.payroll_action_id)),21)) -- just to be consistent with rest of the code to get highest action based on the action sequence
4020 from pay_assignment_actions act,
4021 pay_payroll_actions pact,
4022 per_time_periods ptp -- moved to subquery to make sure latest payroll action having period num matching the archive is fetched
4023 where pact.payroll_action_id = act.payroll_action_id
4024 and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
4025 and act.assignment_id = c_assignment_id
4026 and pact.action_sequence < c_action_sequence -- assuming you will write another sql to get p_p45_action_sequence, alternatively another join to pay_assignment_actions can get you this value in this sql
4027 -- and act.SOURCE_ACTION_ID is null -- no need to check for source_action_id being null to cover upgrade from R11 cases
4028 and ai.value = to_char(ptp.period_num)
4029 and pact.time_period_id = ptp.time_period_id
4030 );
4031 */
4032
4033 Cursor csr_get_paye_ref(c_assignment_id NUMBER, c_effective_date DATE) IS
4034 SELECT flex.segment1 paye_ref, paaf.period_of_service_id
4035 FROM per_all_assignments_f paaf,
4036 pay_all_payrolls_f papf,
4037 hr_soft_coding_keyflex flex
4038 WHERE paaf.assignment_id = c_assignment_id
4039 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
4040 AND paaf.payroll_id = papf.payroll_id
4041 AND c_effective_date BETWEEN papf.effective_start_date and papf.effective_end_date
4042 AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id;
4043
4044 cursor csr_agg_asg_active_period(c_assignment_id number,
4045 c_agg_assignment_id number,
4046 c_tax_ref in varchar2,
4047 c_effective_date date
4048 ) is
4049 select 1
4050 from per_all_assignments_f a,
4051 per_assignment_status_types past
4052 where a.assignment_status_type_id = past.assignment_status_type_id
4053 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
4054 and a.assignment_id = c_assignment_id
4055 and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_agg_assignment_id, c_tax_ref, c_effective_date)
4056 and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_agg_assignment_id, c_tax_ref, c_effective_date)
4057 ;
4058
4059 cursor csr_aggr_paye_flag (c_person_id number,
4060 c_effective_date date) is
4061 select per_information10
4062 from per_all_people_f
4063 where person_id = c_person_id
4064 and c_effective_date between
4065 effective_start_date and effective_end_date;
4066 --
4067 -- to fetch the last active/susp status date for the given assignment
4068 --
4069 cursor csr_asg_last_active_date(c_assignment_id number) is
4070 select max(effective_end_date)
4071 from per_all_assignments_f a,
4072 per_assignment_status_types past
4073 where a.assignment_id = c_assignment_id
4074 and a.assignment_status_type_id = past.assignment_status_type_id
4075 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
4076
4077 --
4078 -- to fetch the earliest aggregation start date from the final payment date.
4079 --
4080 cursor csr_latest_aggr_start_date(c_person_id number, c_effective_date date) is
4081 select max(effective_end_date) + 1
4082 from per_all_people_f
4083 where person_id = c_person_id
4084 and nvl(per_information10,'N') = 'N'
4085 and effective_end_date < c_effective_date;
4086
4087 --
4088 -- to check whether the given assignment present between
4089 -- the earliest aggregation start date and final payment date
4090 --
4091 cursor csr_asg_present_status(c_assignment_id number, c_start_date date, c_end_date date) is
4092 select 1
4093 from per_all_assignments_f a
4094 where a.assignment_id = c_assignment_id
4095 and a.effective_end_date >= c_start_date
4096 and a.effective_start_date <= c_end_date;
4097
4098 -- Start of BUG 5671777-2
4099 --
4100 -- fetch P45 for another assignment that included the given assignment.
4101 --
4102 cursor csr_get_p45_another_asg(c_assignment_id number,c_assignment_action_id number) is
4103 select 1
4104 from ff_archive_items fai,
4105 ff_user_entities fue
4106 where fai.user_entity_id = fue.user_entity_id
4107 and fue.user_entity_name = 'X_P45_INCLUDED_ASSIGNMENT'
4108 and fai.context1 = c_assignment_action_id
4109 and fai.value = to_char(c_assignment_id);
4110
4111 -- End of BUG 5671777-2
4112
4113 l_latest_aggr_start_date date;
4114 l_asg_last_active_date date;
4115 l_agg_asg_last_active_date date;
4116 --
4117 l_assignment_action_id NUMBER;
4118 l_issue_date DATE;
4119 l_locked_action_id NUMBER;
4120 l_effective_date DATE;
4121 l_agg_paye_flag per_all_people_f.per_information10%type;
4122 l_found BOOLEAN;
4123 l_dummy NUMBER;
4124
4125 l_agg_paye_reference hr_soft_coding_keyflex.segment1%type;
4126 l_paye_reference hr_soft_coding_keyflex.segment1%type;
4127 l_period_of_service_id number;
4128 l_agg_period_of_service_id number;
4129 l_action_sequence number;
4130
4131 l_proc CONSTANT VARCHAR2(100):= g_package||'get_p45_agg_asg_action_id';
4132 BEGIN
4133 hr_utility.set_location('Entering: '||l_proc,1);
4134 p_agg_assignment_id := null;
4135 p_final_payment_date := null;
4136 p_p45_agg_asg_action_id := null;
4137 p_p45_issue_date := null;
4138
4139 hr_utility.trace('g_p45_inc_assignment ' || g_p45_inc_assignment);
4140
4141 hr_utility.set_location(l_proc,10);
4142 -- Start of BUG 5671777-2
4143 --
4144 -- fetch P45 for another assignment that included the given assignment.
4145 --
4146 for r_rec in csr_get_all_asg(p_assignment_id) loop
4147 l_assignment_action_id := null;
4148 l_issue_date := null;
4149 hr_utility.set_location(l_proc,14);
4150 --
4151 -- fetch the p45 issue date and assignment action id
4152 --
4153 get_p45_asg_action_id(p_assignment_id => r_rec.assignment_id,
4154 p_assignment_action_id => l_assignment_action_id,
4155 p_issue_date => l_issue_date,
4156 p_action_sequence => l_action_sequence);
4157
4158 if l_assignment_action_id is not null then
4159 hr_utility.set_location(l_proc,17);
4160
4161 open csr_get_p45_another_asg(p_assignment_id,l_assignment_action_id);
4162 fetch csr_get_p45_another_asg into l_dummy;
4163 l_found := csr_get_p45_another_asg%found;
4164 close csr_get_p45_another_asg;
4165
4166 if l_found then
4167 p_agg_assignment_id := r_rec.assignment_id;
4168 p_final_payment_date := null;
4169 p_p45_agg_asg_action_id := l_assignment_action_id;
4170 p_p45_issue_date := l_issue_date;
4171 EXIT;
4172 end if;
4173 end if;
4174 end loop;
4175
4176 if not l_found then
4177 -- End of Bug 5617777-2
4178 for r_rec in csr_get_all_asg(p_assignment_id) loop
4179 l_assignment_action_id := null;
4180 l_issue_date := null;
4181 hr_utility.set_location(l_proc,20);
4182 --
4183 -- fetch the p45 issue date and assignment action id
4184 --
4185 get_p45_asg_action_id(p_assignment_id => r_rec.assignment_id,
4186 p_assignment_action_id => l_assignment_action_id,
4187 p_issue_date => l_issue_date,
4188 p_action_sequence => l_action_sequence);
4189 --
4190
4191 if l_assignment_action_id is not null then
4192 hr_utility.set_location(l_proc,30);
4193 --
4194 /*
4195 -- get the final payment date/effective_date
4196 --
4197 open csr_get_final_payment(r_rec.assignment_id, l_assignment_action_id, l_action_sequence);
4198 fetch csr_get_final_payment into l_locked_action_id, l_effective_date;
4199 l_found := csr_get_final_payment%found;
4200 close csr_get_final_payment;
4201 --
4202
4203 if l_found then
4204 hr_utility.set_location(l_proc,40);
4205 */
4206 --
4207 -- fetch the last active/susp status of the aggregated assignemnt
4208 --
4209 open csr_asg_last_active_date(r_rec.assignment_id);
4210 fetch csr_asg_last_active_date into l_agg_asg_last_active_date;
4211 close csr_asg_last_active_date;
4212
4213 --
4214 -- getting the PAYE Aggregate flag for the person on last active/susp date of the agg. asg
4215 --
4216 open csr_aggr_paye_flag(r_rec.person_id, l_agg_asg_last_active_date);
4217 fetch csr_aggr_paye_flag into l_agg_paye_flag;
4218 close csr_aggr_paye_flag;
4219 --
4220
4221 if nvl(l_agg_paye_flag, 'X') = 'Y' then
4222 hr_utility.set_location(l_proc,50);
4223
4224 --
4225 -- fetch the Tax reference for the agg. asg. on the last active/susp status date of the asg
4226 --
4227 open csr_get_paye_ref(r_rec.assignment_id, l_agg_asg_last_active_date);
4228 fetch csr_get_paye_ref into l_agg_paye_reference, l_agg_period_of_service_id;
4229 l_found := csr_get_paye_ref%found;
4230 close csr_get_paye_ref;
4231 --
4232
4233 if l_found then
4234 hr_utility.set_location(l_proc,60);
4235 --
4236 -- fetch the last active/susp status of the given assignemnt
4237 --
4238 open csr_asg_last_active_date(p_assignment_id);
4239 fetch csr_asg_last_active_date into l_asg_last_active_date;
4240 close csr_asg_last_active_date;
4241
4242 --
4243 -- fetch the Tax reference, period of service id for the given asg. on
4244 -- the last active/susp status of the assignemnt
4245 --
4246 open csr_get_paye_ref(p_assignment_id, l_asg_last_active_date);
4247 fetch csr_get_paye_ref into l_paye_reference, l_period_of_service_id;
4248 l_found := csr_get_paye_ref%found;
4249 close csr_get_paye_ref;
4250 --
4251
4252 if l_found and l_paye_reference = l_agg_paye_reference and
4253 l_period_of_service_id = l_agg_period_of_service_id then
4254
4255 hr_utility.set_location(l_proc,70);
4256
4257 --
4258 -- check for both assignments share aggregated active period of employment or not
4259 --
4260 open csr_agg_asg_active_period(p_assignment_id, r_rec.assignment_id,
4261 l_agg_paye_reference, l_agg_asg_last_active_date);
4262 fetch csr_agg_asg_active_period into l_dummy;
4263 l_found := csr_agg_asg_active_period%found;
4264 close csr_agg_asg_active_period;
4265 --
4266
4267 if l_found then
4268 hr_utility.set_location(l_proc,80);
4269 --
4270 -- to fetch the latest aggregation start date near to final payment date.
4271 --
4272 open csr_latest_aggr_start_date(r_rec.person_id, l_agg_asg_last_active_date);
4273 fetch csr_latest_aggr_start_date into l_latest_aggr_start_date;
4274 close csr_latest_aggr_start_date;
4275 --
4276
4277 if l_latest_aggr_start_date is not null then
4278 hr_utility.set_location(l_proc,90);
4279 --
4280 -- to check whther the given assignment present between
4281 -- the earliest aggregation start date and final payment date
4282 --
4283 open csr_asg_present_status(p_assignment_id, l_latest_aggr_start_date, l_agg_asg_last_active_date);
4284 fetch csr_asg_present_status into l_dummy;
4285 l_found := csr_asg_present_status%found;
4286 close csr_asg_present_status;
4287 end if;
4288
4289 if l_found then
4290 hr_utility.set_location(l_proc,100);
4291 --
4292 -- returning the final payment date, asg. action id and agg.asg. id
4293 --
4294 p_agg_assignment_id := r_rec.assignment_id;
4295 p_final_payment_date := null;
4296 p_p45_agg_asg_action_id := l_assignment_action_id;
4297 p_p45_issue_date := l_issue_date;
4298 --
4299
4300 exit; -- exiting the loop
4301 end if;
4302 end if;
4303 end if;
4304 end if;
4305 end if;
4306 --end if;
4307 end if;
4308 end loop;
4309 end if; -- l_found
4310 hr_utility.set_location('Leaving: '||l_proc,110);
4311 --
4312 END get_p45_agg_asg_action_id;
4313
4314 -- Bug 7028893.Added function PAYE_RETURN_P45_ISSUED_FLAG.
4315 --------------------------------------------------------------------------
4316 -- FUNCTION paye_return_p45_issued_flag
4317 -- DESCRIPTION return the P45 issued status for the given assignment
4318 --------------------------------------------------------------------------
4319
4320 FUNCTION paye_return_p45_issued_flag(p_assignment_id in number,p_payroll_action_id in number) RETURN VARCHAR2 IS
4321 --
4322
4323 -- Cursor to fetch effective date (date earned + off set)
4324 Cursor csr_get_effective_date(c_payroll_action_id number) is
4325 select effective_date
4326 from pay_payroll_actions
4327 where payroll_action_id = c_payroll_action_id;
4328
4329 l_assignment_action_id number;
4330 l_agg_assignment_id number;
4331 l_issue_date date;
4332 l_final_payment_date date;
4333 l_p45_agg_asg_action_id number;
4334 l_action_sequence number;
4335 l_return_p45_issued_flag VARCHAR2(1);
4336 l_proc CONSTANT VARCHAR2(100):= g_package||'paye_return_p45_issued_flag';
4337 l_effective_date date;
4338 --
4339 BEGIN
4340 hr_utility.set_location('Entering: '||l_proc,10);
4341 --
4342 -- check for the p45 issue date and assignment action id through P45 process
4343 --
4344 get_p45_asg_action_id(p_assignment_id => p_assignment_id,
4345 p_assignment_action_id => l_assignment_action_id,
4346 p_issue_date => l_issue_date,
4347 p_action_sequence => l_action_sequence);
4348 --
4349
4350 hr_utility.set_location(l_proc,20);
4351 if l_assignment_action_id is null then
4352 hr_utility.set_location(l_proc,30);
4353
4354 --
4355 -- check for the P45 manualy issued or not
4356 --
4357 l_issue_date := get_p45_eit_manual_issue_dt(p_assignment_id);
4358 if l_issue_date is null then
4359 hr_utility.set_location(l_proc,40);
4360 --
4361 -- check for the P45 issued for any of the aggregated assignment
4362 --
4363 get_p45_agg_asg_action_id(p_assignment_id => p_assignment_id,
4364 p_agg_assignment_id => l_agg_assignment_id,
4365 p_final_payment_date => l_final_payment_date,
4366 p_p45_issue_date => l_issue_date,
4367 p_p45_agg_asg_action_id => l_p45_agg_asg_action_id);
4368
4369 if l_agg_assignment_id is null then
4370 hr_utility.set_location(l_proc,50);
4371 l_return_p45_issued_flag := 'N';
4372 end if;
4373 end if;
4374 --
4375
4376 end if;
4377 hr_utility.set_location('Leaving: '||l_proc,60);
4378
4379 -- Fetching effective date for payroll action id
4380 open csr_get_effective_date(p_payroll_action_id);
4381 fetch csr_get_effective_date into l_effective_date;
4382 close csr_get_effective_date;
4383
4384 -- Comparing P45 issue date with effective date (date earned + off set)
4385 if l_effective_date >= l_issue_date then
4386 l_return_p45_issued_flag := 'Y';
4387 else
4388 l_return_p45_issued_flag := 'N';
4389 end if;
4390 return l_return_p45_issued_flag;
4391
4392 END paye_return_p45_issued_flag;
4393
4394 --------------------------------------------------------------------------
4395 -- FUNCTION return_p45_issued_flag
4396 -- DESCRIPTION return the P45 issued status for the given assignment
4397 --------------------------------------------------------------------------
4398 FUNCTION return_p45_issued_flag(p_assignment_id in number) RETURN VARCHAR2 IS
4399 --
4400 l_assignment_action_id number;
4401 l_agg_assignment_id number;
4402 l_issue_date date;
4403 l_final_payment_date date;
4404 l_p45_agg_asg_action_id number;
4405 l_action_sequence number;
4406 l_return_p45_issued_flag VARCHAR2(1) := 'Y';
4407 l_proc CONSTANT VARCHAR2(100):= g_package||'return_p45_issued_flag';
4408 --
4409 BEGIN
4410 hr_utility.set_location('Entering: '||l_proc,10);
4411 --
4412 -- check for the p45 issue date and assignment action id through P45 process
4413 --
4414 get_p45_asg_action_id(p_assignment_id => p_assignment_id,
4415 p_assignment_action_id => l_assignment_action_id,
4416 p_issue_date => l_issue_date,
4417 p_action_sequence => l_action_sequence);
4418 --
4419
4420 hr_utility.set_location(l_proc,20);
4421 if l_assignment_action_id is null then
4422 hr_utility.set_location(l_proc,30);
4423
4424 --
4425 -- check for the P45 manualy issued or not
4426 --
4427 if get_p45_eit_manual_issue_dt(p_assignment_id) is null then
4428 hr_utility.set_location(l_proc,40);
4429 --
4430 -- check for the P45 issued for any of the aggregated assignment
4431 --
4432 get_p45_agg_asg_action_id(p_assignment_id => p_assignment_id,
4433 p_agg_assignment_id => l_agg_assignment_id,
4434 p_final_payment_date => l_final_payment_date,
4435 p_p45_issue_date => l_issue_date,
4436 p_p45_agg_asg_action_id => l_p45_agg_asg_action_id);
4437
4438 if l_agg_assignment_id is null then
4439 hr_utility.set_location(l_proc,50);
4440 l_return_p45_issued_flag := 'N';
4441 end if;
4442 end if;
4443 --
4444
4445 end if;
4446 hr_utility.set_location('Leaving: '||l_proc,60);
4447 return l_return_p45_issued_flag;
4448 --
4449 END return_p45_issued_flag;
4450
4451 END pay_p45_pkg;
[Home] [Help]