1 PACKAGE BODY PAY_P45_PKG as
2 /* $Header: payrp45.pkb 120.70.12020000.5 2012/12/10 09:54:45 kvinayku 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 19-Nov-2008 vijranga 115.88 Incorporated the new parameter EDI_VER to modify the already checked in code for
271 EDI Validations at the Archival level itself. Added one missed 'Tax code missing' validation
272 and changed the parameters for DOB validation call.
273 21-Nov-2008 dwkrishn 115.89 Performance fix for Cursor csr_range_assignments.Used Not Exists in place of Outer Join
274 26-Nov-2008 vijranga 115.90 Incorporated review comments.
275 27-Nov-2008 vijranga 115.91 Bug #7433580. Incorporated INL team review comments for newly added error messages.
276 21-OCT-2008 rlingama 115.92 P45 A4 2008-09 Changes.Bug 7261906
277 07-Jan-2009 namgoyal 115.93 Bug 7281023: modified cursor csr_get_term_period_no to report
278 correct period number if P45 balance is zero.
279 08-Jan-2009 namgoyal 115.94 Bug 7281023: Added '=' operator in cursor csr_get_term_period_no.
280 15-Feb-2009 dwkrishn 115.96 Bug 8254291: Added Procedure populate_run_msg to insert error messages
281 in pay_message_lines
282 19-Feb-2009 dwkrishn 115.97 Bug 8254291: Made the process continue even if the process errors
283 out noting each error EDI error messages, finally error the process.
284 26-Feb-2009 rlingama 115.98 Bug 8275145 : P45 A4 Laser 4 part changes 2008-09
285 12-Mar-2009 rlingama 115.99 Bug 8275145 : P45 A4 Continuous report changes 2008-09
286 03-Apr-2009 dwkrishn 115.100 Bug 8282187 : Few P45 EDI validations commented as it was they
287 are not to be performed during archive
288 04-May-2009 jvaradra 115.101 Bug 7601088 Added function PAYE_SYNC_P45_ISSUED_FLAG
289 14-May-2009 dwkrishn 115.102 Bug 8464343 fetch week month type with payroll id if last action is -9999
290 08-Jun-2009 dwkrishn 115.103 Bug 8366684 modified agg_latest_action for performance.Removed use_nl
291 to enable optimizer to choose hash join if needed.
292 22-06-2009 dwkrishn 115.104 Bug:8566920 Added hints to cursor csr_range_assignments.Issue occured when
293 DB upgraded from 9i to 10gR2
294 30-8-2009 dwkrishn 115.105 Bug:8537504 assignment_number validation handled in edi_movded6_asg
295 16-11-2009 jvaradra 115.106 Bug:9071978 End of time needs to be considered when FPD is NULL
296 03-11-2009 rlingama 115.107 Bug 9170440 Changed l_printer_style variable declaration.
297 08-02-2010 rlingama 115.108 Bug 9347169 Modified the code to ensure, fecth address based on the assignment
298 end date if address not exists on sysdate.
299 23-01-2010 dwkrishn 115.109 update the payroll_id in the pay_payroll_actions table.
300 04-03-2010 rlingama 115.110 Bug:8370481 Modifed the Total pay/tax to date and Pay/Tax in this Employment exceeds
301 999999.99 to 999999999.99.
302 25-03-2010 dwkrishn 115.111 Bug 9292092 Modified agg_latest_action. Introduced an inline view instead of a
303 corelated subquery
304 08-04-2010 pbalu 115.112 Bug:9036584 : Check for Tax code 'NI' is changed to handle payroll runs after
305 termination also
306 10-05-2010 pbalu 115.113 Bug 9473697. P45 issued flag is checked even when P45 process errors.
307 29-06-2010 pbalu 115.114 Bug 9794457. Non aggregated assignment's last payroll action id is not fetched correctly.
308 03-Jan-2010 npannamp 115.115 Bug 9910054: Modified get_ff_data to skip actions with 0 PAYE for aggregated assignments.
309 31-may-2011 achandwa 115.116 Bug 10077936: Modified the expression for calculating pay to date value
310 17-Jan-2012 krreddy 115.117 Bug 13054902 Modified get_ff_data to fetch last asg_act_id only if it has a non-zero value.
311 05-Jun-2012 achandwa 115.118 Bug 13428461 Modified the cursor csr_transfer to avoid issuing P45 to the employee
312 whose payroll is not changed but PAYE change happens at payroll level
313 09-Aug-2012 kvinayku 115.119 Bug 14206931 Modified the cursor csr_all_assignments and csr_all_assignments_range not to
314 pick up the assignments which are after the effective date
315 10-Dec-2012 kvinayku 115.120 Bug 15972584 Modified the cursor csr_all_assignments and csr_all_assignments_range to pick
316 all the assignmentsin a period.
317 ==============================================================================*/
318
319
320 -- Globals
321 g_package CONSTANT VARCHAR2(20):= 'pay_p45_pkg.';
322 g_asg_creation_cache_populated BOOLEAN := FALSE;
323 g_asg_process_cache_populated BOOLEAN := FALSE;
324 g_fnd_rep_request_msg VARCHAR2(2000):=' ';
325 -- SRS Params
326 g_payroll_id pay_payrolls_f.payroll_id%TYPE;
327 g_start_date DATE;
328 g_effective_date DATE;
329 g_end_date DATE;
330 g_business_group_id hr_organization_units.business_group_id%TYPE;
331 g_do_edi_validation BOOLEAN;
332 g_tax_ref VARCHAR2(20);
333 g_edi_ver VARCHAR2(10);
334 -- User Entity Ids
335 g_address_line1_eid ff_user_entities.user_entity_id%TYPE;
336 g_address_line2_eid ff_user_entities.user_entity_id%TYPE;
337 g_address_line3_eid ff_user_entities.user_entity_id%TYPE;
338 g_assignment_number_eid ff_user_entities.user_entity_id%TYPE;
339 g_county_eid ff_user_entities.user_entity_id%TYPE;
340 g_deceased_flag_eid ff_user_entities.user_entity_id%TYPE;
341 g_first_name_eid ff_user_entities.user_entity_id%TYPE;
342 g_middle_name_eid ff_user_entities.user_entity_id%TYPE; /*Bug 6710229*/
343 g_issue_date_eid ff_user_entities.user_entity_id%TYPE;
344 g_last_name_eid ff_user_entities.user_entity_id%TYPE;
345 g_month_number_eid ff_user_entities.user_entity_id%TYPE;
346 g_ni_number_eid ff_user_entities.user_entity_id%TYPE;
347 g_organization_name_eid ff_user_entities.user_entity_id%TYPE;
348 g_payroll_id_eid ff_user_entities.user_entity_id%TYPE;
349 g_postal_code_eid ff_user_entities.user_entity_id%TYPE;
350 g_prev_tax_paid_eid ff_user_entities.user_entity_id%TYPE;
351 g_prev_taxable_pay_eid ff_user_entities.user_entity_id%TYPE;
352 g_student_loan_flag_eid ff_user_entities.user_entity_id%TYPE;
353 g_aggregated_paye_flag_eid ff_user_entities.user_entity_id%TYPE;
354 g_period_of_service_eid ff_user_entities.user_entity_id%TYPE;
355 g_effective_end_date_eid ff_user_entities.user_entity_id%TYPE;
356 g_tax_code_eid ff_user_entities.user_entity_id%TYPE;
357 g_tax_paid_eid ff_user_entities.user_entity_id%TYPE;
358 g_tax_ref_transfer_eid ff_user_entities.user_entity_id%TYPE;
359 g_taxable_pay_eid ff_user_entities.user_entity_id%TYPE;
360 g_termination_date_eid ff_user_entities.user_entity_id%TYPE;
361 g_title_eid ff_user_entities.user_entity_id%TYPE;
362 g_town_or_city_eid ff_user_entities.user_entity_id%TYPE;
363 g_w1_m1_indicator_eid ff_user_entities.user_entity_id%TYPE;
364 g_week_number_eid ff_user_entities.user_entity_id%TYPE;
365 g_country_eid ff_user_entities.user_entity_id%TYPE;
366 g_p45_final_action ff_user_entities.user_entity_id%TYPE;
367 g_p45_inc_assignment ff_user_entities.user_entity_id%TYPE;
368 -- Added for P45PT1. Bug 6345375
369 g_date_of_birth_eid ff_user_entities.user_entity_id%TYPE;
370 g_sex_eid ff_user_entities.user_entity_id%TYPE;
371 -- Seed data IDs
372 g_paye_details_id pay_element_types_f.element_type_id%TYPE;
373 --
374 FUNCTION get_report_request_error RETURN VARCHAR2 IS
375 BEGIN
376 RETURN g_fnd_rep_request_msg;
377 END get_report_request_error;
378 --
379 --------------------------------------------------------------------------
380 -- FUNCTION override_date
381 -- DESCRIPTION Get the override date if one exists
382 --------------------------------------------------------------------------
383 FUNCTION override_date(p_assignment_id in number) RETURN DATE IS
384 --
385 l_override_date date;
386 cursor csr_override_date (c_assignment_id in number) is
387 select fnd_date.canonical_to_date(aei.aei_information4)
388 from per_assignment_extra_info aei
389 where aei.assignment_id = c_assignment_id
390 and aei.information_type = 'GB_P45';
391 --
392 BEGIN
393 open csr_override_date(p_assignment_id);
394 fetch csr_override_date into l_override_date;
395 if csr_override_date%NOTFOUND then
396 l_override_date := null;
397 end if;
398 close csr_override_date;
399 --
400 RETURN l_override_date;
401 END override_date;
402 -----------------------------------------------------------------------------
403 -- Name --
404 -- get_p45_formula_id --
405 -- Purpose --
406 -- this function finds the formula id for the validation of the PAYE --
407 -- tax_code element entry value.
408 -----------------------------------------------------------------------------
409 --
410 FUNCTION get_p45_formula_id RETURN NUMBER IS
411
412 cursor c_formula is
413 select f.FORMULA_ID
414 from ff_formulas_f f,
415 ff_formula_types t
416 where t.FORMULA_TYPE_ID = f.FORMULA_TYPE_ID
417 and f.FORMULA_NAME = 'P45';
418 --
419 l_formula_id NUMBER;
420 --
421 BEGIN
422 --
423 open c_formula;
424 fetch c_formula into l_formula_id;
425 if c_formula%notfound then
426 --
427 close c_formula;
428 --
429 fnd_message.set_name ('FF', 'FFX03A_FORMULA_NOT_FOUND');
430 fnd_message.set_token ('1','P45');
431 fnd_message.raise_error;
432 --
433 end if;
434 close c_formula;
435 --
436 RETURN l_formula_id;
437 --
438 END get_p45_formula_id;
439 --
440 -----------------------------------------------------------------------------
441 --
442 -- Name --
443 -- get_student_loan_flag
444 -- Purpose --
445 -- this function finds if the employee has a Student Loan effective at --
446 -- the time employment ceases. Returns 'Y' if 'End Date' is not prior --
447 -- or equal to the termination date.
448 -----------------------------------------------------------------------------
449 --
450 FUNCTION get_student_loan_flag (p_assignment_id IN NUMBER,
451 p_termination_date IN DATE,
452 p_session_date IN DATE)
453 RETURN VARCHAR2
454 IS
455 --
456 cursor csr_getdate (x_assignment_id NUMBER,
457 x_termination_date DATE) IS
458 SELECT peev.screen_entry_value
459 FROM pay_element_types_f pet,
460 pay_element_links_f pel,
461 pay_element_entries_f pee,
462 pay_input_values_f piv,
463 pay_element_entry_values_f peev
464 WHERE pee.assignment_id = x_assignment_id
465 AND upper(pet.element_name) = 'STUDENT LOAN'
466 AND upper(piv.name) = 'END DATE'
467 AND pet.business_group_id IS NULL
468 AND pet.legislation_code = 'GB'
469 AND pet.element_type_id = pel.element_type_id
470 AND pel.element_link_id = pee.element_link_id
471 AND pet.element_type_id = piv.element_type_id
472 AND piv.input_value_id = peev.input_value_id
473 AND pee.element_entry_id = peev.element_entry_id
474 AND x_termination_date BETWEEN pet.effective_start_date
475 AND pet.effective_end_date
476 AND x_termination_date BETWEEN pel.effective_start_date
477 AND pel.effective_end_date
478 AND x_termination_date BETWEEN pee.effective_start_date
479 AND pee.effective_end_date
480 AND x_termination_date BETWEEN piv.effective_start_date
481 AND piv.effective_end_date
482 AND x_termination_date BETWEEN peev.effective_start_date
483 AND peev.effective_end_date;
484 --
485 l_end_date pay_element_entry_values_f.screen_entry_value%TYPE;
486 l_flag VARCHAR2(1);
487 l_term_date DATE;
488 l_found BOOLEAN;
489 --
490 BEGIN
491 --
492 --
493 l_flag := 'N';
494 --
495 l_term_date := nvl(p_termination_date,p_session_date);
496 OPEN csr_getdate(p_assignment_id, l_term_date);
497 FETCH csr_getdate INTO l_end_date;
498 l_found := csr_getdate%found;
499 CLOSE csr_getdate;
500 IF l_found THEN
501 IF ((l_end_date IS NULL) OR
502 (fnd_date.canonical_to_date(l_end_date) > l_term_date)) THEN
503 l_flag := 'Y';
504 hr_utility.trace('changing flag to: ' || l_flag);
505 hr_utility.trace('end date is <' || l_end_date || '>');
506 END IF;
507 ELSE
508 hr_utility.trace('no data found...');
509 NULL;
510 END IF;
511 hr_utility.trace('leaving function get_student_loan_flag...');
512 --
513 --
514 RETURN l_flag;
515 --
516 END get_student_loan_flag;
517
518 procedure person_address(X_PERSON_ID in number,
519 X_SESSION_DATE in date,
520 X_ADDRESS_LINE1 in out nocopy varchar2,
521 X_ADDRESS_LINE2 in out nocopy varchar2,
522 X_ADDRESS_LINE3 in out nocopy varchar2,
523 X_TOWN_OR_CITY in out nocopy varchar2,
524 X_REGION_1 in out nocopy varchar2,
525 X_COUNTRY in out nocopy varchar2,
526 X_POSTAL_CODE in out nocopy varchar2,
527 X_ASSIGNMENT_END_DATE in date) is -- added for bug 9347169
528
529 -- Bug 9347169 : Modified the cursor to fetch address based on the effective date.
530 cursor c_addr (p_effective_date in date) is select addr.ADDRESS_LINE1,
531 addr.ADDRESS_LINE2,
532 addr.ADDRESS_LINE3,
533 addr.TOWN_OR_CITY,
534 addr.REGION_1,
535 addr.COUNTRY,
536 addr.POSTAL_CODE
537 from per_addresses addr
538 where addr.PERSON_ID = X_PERSON_ID
539 and addr.PRIMARY_FLAG = 'Y'
540 and p_effective_date between
541 addr.DATE_FROM and
542 nvl(addr.DATE_TO,fnd_date.canonical_to_date('4712/12/31'));
543
544 cursor get_country(p_code in varchar2) is
545 select ftv.territory_short_name
546 from fnd_territories_vl ftv
547 where ftv.territory_code = p_code;
548
549 l_addr c_addr%rowtype;
550 l_found boolean;
551 l_county per_addresses.region_1%type /*varchar2(30)*/;
552
553 begin
554 /* Clear the variables, as if no future assignment is made to them,
555 they may hold the previous value assigned
556 */
557 X_ADDRESS_LINE1 := '';
558 X_ADDRESS_LINE2 := '';
559 X_ADDRESS_LINE3 := '';
560 X_TOWN_OR_CITY := '';
561 X_COUNTRY := '';
562 X_POSTAL_CODE := '';
563 X_REGION_1 := '';
564 open c_addr (X_SESSION_DATE);
565 fetch c_addr into l_addr;
566 l_found := c_addr%found;
567 close c_addr;
568 -- Start bug 9347169
569 -- If address not exists on sysdate, fecth address based on assignment end date.
570 if not l_found then
571 open c_addr(X_ASSIGNMENT_END_DATE);
572 fetch c_addr into l_addr;
573 l_found := c_addr%found;
574 close c_addr;
575 end if;
576 -- End bug 9347169
577 if l_found then
578 X_ADDRESS_LINE1 := l_addr.ADDRESS_LINE1;
579 X_ADDRESS_LINE2 := l_addr.ADDRESS_LINE2;
580 X_ADDRESS_LINE3 := l_addr.ADDRESS_LINE3;
581 X_TOWN_OR_CITY := l_addr.TOWN_OR_CITY;
582 -- X_COUNTRY := l_addr.COUNTRY;
583 X_POSTAL_CODE := l_addr.POSTAL_CODE;
584 l_county := l_addr.REGION_1;
585 open get_country(l_addr.COUNTRY);
586 fetch get_country into X_COUNTRY;
587 close get_country;
588 begin
589 SELECT substr(hr.meaning,1,30)
590 INTO X_REGION_1
591 FROM HR_LOOKUPS hr
592 WHERE hr.LOOKUP_CODE = l_county
593 AND hr.LOOKUP_TYPE = 'GB_COUNTY';
594 EXCEPTION WHEN NO_DATA_FOUND THEN
595 null;
596 END;
597 else
598 X_ADDRESS_LINE1 := '';
599 X_ADDRESS_LINE2 := '';
600 X_ADDRESS_LINE3 := '';
601 X_TOWN_OR_CITY := '';
602 X_COUNTRY := '';
603 X_POSTAL_CODE := '';
604 X_REGION_1 := '';
605 end if;
606 end person_address;
607
608 -- Fetch tax details
609 --
610 procedure get_tax_details(p_assignment_id in number,
611 p_paye_details_id in number,
612 p_paye_id in number,
613 p_eff_date in date,
614 p_tax_code out nocopy varchar2,
615 p_tax_basis out nocopy varchar2,
616 p_prev_pay out nocopy varchar2,
617 p_prev_tax out nocopy varchar2)
618 is
619 l_paye_rr_id number;
620 l_paye_details_rr_id number;
621 l_effective_date date; -- Bug 6900025 to store date earned value
622 l_start_year date; -- Bug 7410767 to store the start of financial year
623
624 -- Bug 6900025 added l_eff_date parameter to check proper financial year for fecting PAYE details.
625 CURSOR csr_max_run_result(l_element_id number,l_eff_date date) IS
626 SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
627 pact PAY_PAYROLL_ACTIONS_PK,
628 r2 PAY_RUN_RESULTS_N50)
629 USE_NL(assact2, pact, r2) */
630 to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
631 r2.run_result_id),17))
632 FROM pay_assignment_actions assact2,
633 pay_payroll_actions pact,
634 pay_run_results r2
635 WHERE assact2.assignment_id = p_assignment_id
636 AND r2.element_type_id+0 = l_element_id
637 AND r2.assignment_action_id = assact2.assignment_action_id
638 AND r2.status IN ('P', 'PA')
639 AND pact.payroll_action_id = assact2.payroll_action_id
640 AND pact.action_type IN ( 'Q','R','B','I')
641 AND assact2.action_status = 'C'
642 AND pact.effective_date between
643 -- Bug 6900025 Modified pact.effective_date condtion to fetch PAYE details from run results instead of element entries.
644 -- to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(p_eff_date,'YYYY'))),'DD-MM-YYYY')
645 -- and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(p_eff_date,'YYYY') + 1)),'DD-MM-YYYY')
646 to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(l_eff_date,'YYYY'))),'DD-MM-YYYY')
647 and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_eff_date,'YYYY') + 1)),'DD-MM-YYYY')
648 AND NOT EXISTS(
649 SELECT '1'
650 FROM pay_action_interlocks pai,
651 pay_assignment_actions assact3,
652 pay_payroll_actions pact3
653 WHERE pai.locked_action_id = assact2.assignment_action_id
654 AND pai.locking_action_id = assact3.assignment_action_id
655 AND pact3.payroll_action_id = assact3.payroll_action_id
656 AND pact3.action_type = 'V'
657 AND assact3.action_status = 'C');
658
659 CURSOR csr_run_result(l_run_result_id number,l_element_type_id number) IS
660 SELECT max(decode(name,'Tax Code',result_value,NULL)) tax_code,
661 max(decode(name,'Tax Basis',result_value,NULL)) tax_basis,
662 to_number(max(decode(name,'Pay Previous',
663 fnd_number.canonical_to_number(result_value),NULL)))
664 pay_previous,
665 to_number(max(decode(name,'Tax Previous',
666 fnd_number.canonical_to_number(result_value),NULL)))
667 tax_previous
668 FROM pay_input_values_f v,
669 pay_run_result_values rrv
670 WHERE rrv.run_result_id = l_run_result_id
671 AND v.input_value_id = rrv.input_value_id
672 AND v.element_type_id = l_element_type_id;
673
674 CURSOR csr_paye_details(p_assignment_id NUMBER) IS
675 SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
676 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
677 max(decode(iv.name,'Pay Previous',screen_entry_value)) pay_previous,
678 max(decode(iv.name,'Tax Previous',screen_entry_value)) tax_previous
679 FROM pay_element_entries_f e,
680 pay_element_entry_values_f v,
681 pay_input_values_f iv,
682 pay_element_links_f link
683 WHERE e.assignment_id = p_assignment_id
684 AND link.element_type_id = g_paye_details_id
685 AND e.element_link_id = link.element_link_id
686 AND e.element_entry_id = v.element_entry_id
687 AND iv.input_value_id = v.input_value_id
688 AND e.effective_end_date BETWEEN link.effective_start_date AND link.effective_end_date
689 AND e.effective_end_date BETWEEN iv.effective_start_date AND iv.effective_end_date
690 AND e.effective_end_date BETWEEN v.effective_start_date AND v.effective_end_date
691 AND e.effective_end_date = (select max(e1.effective_end_date)
692 from pay_element_entries_f e1,
693 pay_element_links_f link1
694 where link1.element_type_id = g_paye_details_id
695 and e1.assignment_id = p_assignment_id
696 and e1.element_link_id = link1.element_link_id);
697 begin
698 hr_utility.set_location('Entering get_tax_details',1);
699 hr_utility.trace('Assignemnt ID : ' || p_assignment_id);
700 hr_utility.trace('PAYE Details ID : ' || p_paye_details_id);
701 hr_utility.trace('PAYE ID : ' || p_paye_id);
702 hr_utility.trace('Effective Date : ' || p_eff_date);
703
704
705 --Bug 6900025 assigning proper date earned to l_effective_date
706 --Bug 7410767 Modified the p_eff_date check
707
708 --if fnd_number.canonical_to_number(to_char(p_eff_date,'DD')) >= 06
709 --and fnd_number.canonical_to_number(to_char(p_eff_date,'MM')) >= 04 then
710 l_start_year := to_date('06/04/'||to_char(p_eff_date,'YYYY'),'DD/MM/YYYY');
711 if to_number(p_eff_date - l_start_year) >= 0 then
712 l_effective_date := p_eff_date;
713 else
714 --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');
715 l_effective_date := add_months( p_eff_date,-12);
716 end if;
717
718 hr_utility.trace('l_effective_date value'||l_effective_date);
719 open csr_max_run_result(p_paye_id,l_effective_date);
720 fetch csr_max_run_result into l_paye_rr_id;
721 close csr_max_run_result;
722
723 open csr_max_run_result(p_paye_details_id,l_effective_date);
724 fetch csr_max_run_result into l_paye_details_rr_id;
725 close csr_max_run_result;
726
727 hr_utility.trace('Fetching run result 1');
728 -- 1. First we try to fetch it from the latest PAYE run results
729 open csr_run_result(l_paye_rr_id, p_paye_id);
730 fetch csr_run_result into p_tax_code,
731 p_tax_basis,
732 p_prev_pay,
733 p_prev_tax;
734 close csr_run_result;
735 -- 2. Tax code is not found, fetch from the latest PAYE Details run results
736 if p_tax_code is null then
737 hr_utility.trace('Fetching run result 2');
738 open csr_run_result(l_paye_details_rr_id, p_paye_details_id);
739 fetch csr_run_result into p_tax_code,
740 p_tax_basis,
741 p_prev_pay,
742 p_prev_tax;
743 close csr_run_result;
744
745 -- 3. Still not found, fetch the value from the PAYE
746 if p_tax_code is null then
747 hr_utility.trace('Fetching run result 3');
748 open csr_paye_details(p_assignment_id);
749 fetch csr_paye_details into p_tax_code,
750 p_tax_basis,
751 p_prev_pay,
752 p_prev_tax;
753 close csr_paye_details;
754 end if;
755 end if;
756 hr_utility.set_location('Leaving get_tax_details',999);
757 end;
758
759 procedure get_ff_data(X_SESSION_DATE in date,
760 X_ASSIGNMENT_ID in number,
761 X_ASSIGNMENT_END_DATE in date,
762 X_ASSIGNMENT_ACTION_ID in out nocopy number,
763 X_DATE_EARNED in out nocopy date,
764 X_PAYROLL_ACTION_ID in out nocopy number,
765 X_TRANSFER_DATE in date,
766 X_PERSON_ID in number,
767 X_TAX_REFERENCE in varchar2 default null) is
768 --
769 -- BUG 3221422 Changed Query for improving performance
770 -- 4136320 Querying for actions only in TY of ATD
771 --9794457 to fetch payroll actions after the tax year start corresponding to
772 --termination date.
773 --cursor c_act is select act.assignment_action_id,
774 cursor c_act(l_tax_year_start date)
775 is select act.assignment_action_id,
776 act.payroll_action_id,
777 pact.effective_date ,
778 pact.payroll_id
779 from pay_assignment_actions act,
780 pay_payroll_actions pact
781 where act.assignment_id = X_ASSIGNMENT_ID
782 and act.action_status = 'C'
783 and pact.payroll_action_id = act.payroll_action_id
784 and act.action_sequence = (
785 select /*+ ORDERED use_nl(a,pact2,t,r,v,f)
786 user_index(v, PAY_RUN_RESULT_VALUES_PK) */
787 max(a.action_sequence)
788 from pay_assignment_actions a
789 ,pay_payroll_actions pact2
790 ,pay_balance_types t
791 ,pay_balance_feeds_f f
792 ,pay_run_results r
793 ,pay_run_result_values v
794 where t.balance_name in ('Taxable Pay','PAYE')
795 and t.legislation_code = 'GB'
796 and f.balance_type_id = t.balance_type_id
797 and v.input_value_id = f.input_value_id
798 and v.run_result_id = r.run_result_id
799 and r.assignment_action_id = a.assignment_action_id
800 and a.payroll_action_id = pact2.payroll_action_id
801 and a.assignment_id = X_ASSIGNMENT_ID
802 and a.action_status = 'C'
803 and pact2.effective_date <= X_TRANSFER_DATE
804 and pact2.effective_date >= l_tax_year_start
805 -- and pact2.effective_date >=
806 -- to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(x_assignment_end_date,'YYYY'))),'DD-MM-YYYY')
807 and pact2.effective_date between f.effective_start_date and f.effective_end_date
808 and v.result_value is not null --Added for bug 13054902
809 and v.result_value <> '0'); --Added for bug 13054902
810
811 cursor c_ptp (taxable_update_payroll number, c_date_paid date) is
812 select min(PTP.start_date) start_date
813 from per_time_periods PTP
814 where PTP.payroll_id = taxable_update_payroll
815 and (PTP.REGULAR_PAYMENT_DATE ) >= (/*start of fyear prior to session date*/
816 to_date('06-04-'||
817 to_char(fnd_number.canonical_to_number(to_char(c_date_paid,'YYYY'))
818 + least(sign(c_date_paid - to_date('06-04-'
819 || to_char(c_date_paid,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY'));
820 --
821 -- Bug 9910054: agg_latest_action modified to take all the asg actions in desc order, instead of Max action.
822 cursor agg_latest_action (c_person_id in number, -- query rewritten for bug 9292092
823 c_effective_end_date in date,
824 c_tax_reference in varchar2,
825 c_agg_active_start in date,
826 c_agg_active_end in date) is
827 select /*+ ORDERED index(a PER_ASSIGNMENTS_F_N12) use_nl(paa ppa pay flex) */
828 -- fnd_number.canonical_to_number(substr(max(
829 fnd_number.canonical_to_number(substr((
830 lpad(paa.action_sequence,15,'0')||
831 paa.assignment_action_id),16)) assignment_action_id
832 from per_all_assignments_f a,
833 pay_assignment_actions paa,
834 pay_payroll_actions ppa,
835 pay_all_payrolls_f pay,
836 hr_soft_coding_keyflex flex
837 ,(SELECT a2.assignment_id, max(a2.effective_start_date) max_effective_start_date -- Bug 9292092
838 FROM per_all_assignments_f a2,
839 per_assignment_status_types past
840 WHERE /* a2.assignment_id = a.assignment_id
841 AND */ a2.effective_start_date <= c_agg_active_end
842 AND a2.effective_end_date >= c_agg_active_start
843 AND a2.assignment_status_type_id = past.assignment_status_type_id
844 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') group by a2.assignment_id) b
845 where a.person_id = c_person_id
846 and b.assignment_id = a.assignment_id
847 and a.effective_start_date = b.max_effective_start_date
848 and paa.assignment_id = a.assignment_id
849 and ppa.payroll_action_id = paa.payroll_action_id
850 -- and pay.payroll_id = a.payroll_id
851 and pay.payroll_id = ppa.payroll_id
852 and flex.soft_coding_keyflex_id = pay.soft_coding_keyflex_id
853 and flex.segment1 = c_tax_reference
854 and (paa.source_action_id is not null
855 or ppa.action_type in ('I','V','B'))
856 and ppa.effective_date <= c_effective_end_date
857 -- bug 4553334
858 -- and c_effective_end_date between a.effective_start_date and a.effective_end_date
859 -- and c_effective_end_date between pay.effective_start_date and pay.effective_end_date
860 -- 5144323: ensure payroll is on the same paye ref at the time of payroll action
861 AND ppa.effective_date between pay.effective_start_date and pay.effective_end_date
862 -- bug 9910054
863 -- and ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
864 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
865 order by (lpad(paa.action_sequence,15,'0')|| paa.assignment_action_id) desc;
866 -- 5144323: Ensure the action belongs to an assignment that shares continuous active
867 -- period of employement with the given terminated assignment
868 -- AND a.effective_start_date = (SELECT /*+ ORDERED */ max(a2.effective_start_date)
869 -- FROM per_all_assignments_f a2,
870 -- per_assignment_status_types past
871 -- WHERE a2.assignment_id = a.assignment_id
872 -- AND a2.effective_start_date <= to_date(c_agg_active_end, 'MM/DD/YYYY HH24:MI:SS')
873 -- AND a2.effective_end_date >= to_date(c_agg_active_start, 'MM/DD/YYYY HH24:MI:SS')
874 -- AND a2.assignment_status_type_id = past.assignment_status_type_id
875 -- AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN'))
876
877 --
878 cursor get_latest_id (c_assignment_id IN NUMBER,
879 c_effective_date IN DATE) is
880 SELECT /*+ USE_NL(paa, ppa) */
881 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
882 paa.assignment_action_id),16))
883 FROM pay_assignment_actions paa,
884 pay_payroll_actions ppa
885 WHERE
886 paa.assignment_id = c_assignment_id
887 AND ppa.payroll_action_id = paa.payroll_action_id
888 AND (paa.source_action_id is not null
889 or ppa.action_type in ('I','V','B'))
890 AND ppa.effective_date <= c_effective_date
891 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
892
893 -- This cursor will fetch master assignment action id
894 -- Added to support upgrade from 11.0 to 11i.
895 cursor get_last_action(c_assignment_id IN NUMBER,
896 c_effective_date IN DATE) is
897 SELECT /*+ USE_NL(paa, ppa) */
898 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
899 paa.assignment_action_id),16))
900 FROM pay_assignment_actions paa,
901 pay_payroll_actions ppa
902 WHERE
903 paa.assignment_id = c_assignment_id
904 AND ppa.payroll_action_id = paa.payroll_action_id
905 AND ppa.effective_date <= c_effective_date
906 AND ppa.action_status = 'C'
907 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
908
909 --
910 cursor taxable_or_paye_exists (c_assignment_action_id in number) is
911 SELECT 'Y' FROM
912 sys.dual target where exists
913 (select 1
914 from PAY_BALANCE_FEEDS_F FEED
915 , PAY_BALANCE_TYPES PBT
916 , PAY_RUN_RESULT_VALUES PRRV
917 , PAY_RUN_RESULTS PRR
918 WHERE PBT.BALANCE_NAME in ('Taxable Pay', 'PAYE')
919 AND PBT.LEGISLATION_CODE = 'GB'
920 AND PBT.BALANCE_TYPE_ID = FEED.BALANCE_TYPE_ID
921 AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
922 AND FEED.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
923 AND PRRV.RESULT_VALUE IS NOT NULL
924 AND PRRV.RESULT_VALUE <> '0'
925 AND PRR.ASSIGNMENT_ACTION_ID = c_assignment_action_id);
926 --
927 cursor payroll_details(c_assignment_action_id in number) is
928 select paa.payroll_action_id,
929 ppa.effective_date, ppa.payroll_id
930 from pay_payroll_actions ppa,
931 pay_assignment_actions paa
932 where paa.assignment_action_id = c_assignment_action_id
933 and ppa.payroll_action_id = paa.payroll_action_id;
934 --
935 cursor agg_paye(c_person_id in number,
936 c_effective_date in date) is
937 select decode(p.per_information10,'Y','Y',NULL) agg_paye_flag
938 from per_all_people_f p
939 where p.person_id = c_person_id
940 and c_effective_date between
941 p.effective_start_date and p.effective_end_date;
942 --
943 CURSOR csr_get_term_tax_year_start IS
944 SELECT to_date('06-04-'||
945 to_char(fnd_number.canonical_to_number(to_char(X_ASSIGNMENT_END_DATE,'YYYY'))
946 + least(sign(X_ASSIGNMENT_END_DATE - to_date('06-04-'
947 || to_char(X_ASSIGNMENT_END_DATE,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
948 FROM dual;
949 --
950 CURSOR csr_get_action_tax_year_start (p_asg_action_id NUMBER) IS
951 SELECT to_date('06-04-'||
952 to_char(fnd_number.canonical_to_number(to_char(ptp.regular_payment_date,'YYYY'))
953 + least(sign(ptp.regular_payment_date - to_date('06-04-'
954 || to_char(ptp.regular_payment_date,'YYYY'),'DD-MM-YYYY')),0)),'DD-MM-YYYY')
955 FROM per_time_periods ptp, pay_assignment_actions act, pay_payroll_actions pact
956 WHERE act.assignment_action_id = p_asg_action_id
957 AND act.payroll_Action_id = pact.payroll_action_id
958 AND pact.time_period_id = ptp.time_period_id;
959 --
960 -- Cursor to find last date after assignment's termination date until which
961 -- aggregation flag has remained Y - assuming flag is Y at the termination date
962 CURSOR get_aggregation_end IS
963 SELECT nvl((min(effective_start_date)-1), hr_general.end_of_time) agg_end_date
964 FROM per_all_people_f
965 WHERE person_id = X_PERSON_ID
966 AND effective_start_date > X_ASSIGNMENT_END_DATE
967 AND nvl(per_information10, 'N') = 'N';
968 --
969 l_found boolean := FALSE;
970 l_tax_paye_exists varchar2(1);
971 l_taxable_update_action number;
972 l_taxable_update_date date;
973 l_taxable_update_payroll number;
974 l_payroll_year_start date;
975 l_ptp c_ptp%rowtype;
976 l_agg_paye_flag varchar2(1);
977 l_asg_action_id number;
978 l_latest_asg_action_id number;
979 l_effective_date date;
980 l_payroll_id number;
981 l_payroll_action_id number;
982 l_override_date date;
983 l_asg_action_exists boolean;
984 --
985 l_termination_ty_start date;
986 l_latest_action_ty_start date;
987 l_aggregation_end date;
988 l_agg_active_start date;
989 l_agg_active_end date;
990 begin
991 --
992 hr_utility.trace('DATE_EARN ' || x_date_earned);
993 hr_utility.trace('X_PERSON_ID ' || X_PERSON_ID);
994 hr_utility.trace('X_ASSIGNMENT_END_DATE ' || fnd_date.date_to_displaydate(X_ASSIGNMENT_END_DATE));
995 hr_utility.trace('X_TRANSFER_DATE ' || fnd_date.date_to_displaydate(X_TRANSFER_DATE));
996 l_asg_action_exists := FALSE;
997 open agg_paye(X_PERSON_ID, X_ASSIGNMENT_END_DATE);
998 fetch agg_paye into l_agg_paye_flag;
999 close agg_paye;
1000 --
1001 --9794457 Begin
1002 OPEN csr_get_term_tax_year_start;
1003 FETCH csr_get_term_tax_year_start INTO l_termination_ty_start;
1004 CLOSE csr_get_term_tax_year_start;
1005 hr_utility.trace('After csr_get_term_tax_year_start, l_termination_ty_start='||fnd_date.date_to_displaydate(l_termination_ty_start));
1006 --9794457 End
1007
1008 hr_utility.trace('Agg PAYE in ff data:'||l_agg_paye_flag);
1009 hr_utility.trace(to_char(X_TRANSFER_DATE));
1010 -- Use the tfr date as includes all processes for the assignment.
1011 if nvl(l_agg_paye_flag,'X') = 'Y' then
1012 -- 5144323: get_aggregation end date
1013 OPEN get_aggregation_end;
1014 FETCH get_aggregation_end INTO l_aggregation_end;
1015 CLOSE get_aggregation_end;
1016 hr_utility.trace('After get_aggregation_end, l_aggregation_end='||fnd_date.date_to_displaydate(l_aggregation_end));
1017 l_agg_active_start := pay_gb_eoy_archive.get_agg_active_start(X_ASSIGNMENT_ID, X_TAX_REFERENCE, X_ASSIGNMENT_END_DATE);
1018 l_agg_active_end := pay_gb_eoy_archive.get_agg_active_end(X_ASSIGNMENT_ID, X_TAX_REFERENCE, X_ASSIGNMENT_END_DATE);
1019 --
1020 -- bug 9910054, consider asg actions with non zero taxable pay or PAYE.
1021 -- 5144343: Get aggregated action within the aggregation period only
1022 /* open agg_latest_action(X_PERSON_ID, least(X_TRANSFER_DATE, l_aggregation_end),
1023 X_TAX_REFERENCE,
1024 l_agg_active_start,
1025 l_agg_active_end);
1026 fetch agg_latest_action into l_asg_action_id;
1027 --l_found := agg_latest_action%found;
1028 close agg_latest_action;
1029 */
1030 for r_asg_action in agg_latest_action(X_PERSON_ID, least(X_TRANSFER_DATE, l_aggregation_end),
1031 X_TAX_REFERENCE,
1032 l_agg_active_start,
1033 l_agg_active_end) loop
1034 open taxable_or_paye_exists(r_asg_action.assignment_action_id);
1035 fetch taxable_or_paye_exists into l_tax_paye_exists;
1036 l_found := taxable_or_paye_exists%found;
1037 IF l_found THEN
1038 l_asg_action_id := r_asg_action.assignment_action_id;
1039 hr_utility.trace('Aggregated Assignments Non zero results found: TRUE');
1040 exit;
1041 ELSE
1042 hr_utility.trace('Aggregated Assignments Non zero results found: FALSE');
1043 END IF;
1044 close taxable_or_paye_exists;
1045 end loop;
1046 -- bug 9910054 end
1047 l_found := FALSE;
1048 if l_asg_action_id is not null then
1049 l_found := TRUE;
1050 end if;
1051 hr_utility.trace('asg action for agg: '||to_char(l_asg_action_id));
1052 open payroll_details(l_asg_action_id);
1053 fetch payroll_details into l_payroll_action_id, l_effective_date,
1054 l_payroll_id;
1055 close payroll_details;
1056 hr_utility.trace('asg action: '||to_char(l_asg_action_id));
1057 --
1058 else
1059 -- NOT Aggregated PAYE
1060 hr_utility.trace('Not aggregated so get latest action of asg');
1061 --
1062 open get_latest_id(X_ASSIGNMENT_ID, X_TRANSFER_DATE);
1063 fetch get_latest_id into l_asg_action_id;
1064 close get_latest_id;
1065 --
1066 if l_asg_action_id is not null then
1067 hr_utility.trace('single asg action found: '||to_char(l_asg_action_id));
1068 l_latest_asg_action_id := l_asg_action_id;
1069 l_asg_action_exists := TRUE;
1070 open taxable_or_paye_exists(l_asg_action_id);
1071 fetch taxable_or_paye_exists into l_tax_paye_exists;
1072 l_found := taxable_or_paye_exists%found;
1073 IF l_found THEN
1074 hr_utility.trace(' Non zero results found: TRUE');
1075 ELSE
1076 hr_utility.trace(' Non zero results found: FALSE');
1077 END IF;
1078 close taxable_or_paye_exists;
1079 else
1080 hr_utility.trace('No Master-Child action');
1081 open get_last_action(X_ASSIGNMENT_ID, X_TRANSFER_DATE);
1082 fetch get_last_action into l_asg_action_id;
1083 close get_last_action;
1084
1085 if l_asg_action_id is not null then
1086 l_found := TRUE;
1087 end if;
1088 end if;
1089 --
1090 if l_found then
1091 -- Above 2 cursors found the last assignment action has
1092 -- Taxable Pay or PAYE balances, so obtain payroll action details
1093 hr_utility.trace('Asg action has paye or taxable pay');
1094 open payroll_details(l_asg_action_id);
1095 fetch payroll_details into l_payroll_action_id, l_effective_date,
1096 l_payroll_id;
1097 close payroll_details;
1098
1099 else
1100 -- Above cursors did not find asg action that has Taxable
1101 -- Pay or PAYE balances, so use less efficient cursor
1102 -- to search for the last action that has PAYE or Taxable
1103 -- Pay. Do this only if there was any asg action for the assignment.
1104 hr_utility.trace('Asg action has NO paye or taxable pay');
1105 IF l_asg_action_exists then
1106 BEGIN
1107 hr_utility.trace('Use c_act');
1108 open c_act(l_termination_ty_start); --9794457
1109 fetch c_act into l_asg_action_id, l_payroll_action_id,
1110 l_effective_date, l_payroll_id;
1111 l_found := c_act%found;
1112 close c_act;
1113 hr_utility.trace(l_asg_action_id);
1114 hr_utility.trace(l_payroll_action_id);
1115
1116 IF NOT(l_found) THEN -- 4136320: No actions with payments, so query the last run details
1117 open payroll_details( greatest(l_asg_action_id, l_latest_asg_action_id) );
1118 fetch payroll_details into l_payroll_action_id, l_effective_date,
1119 l_payroll_id;
1120 l_found := payroll_details%found;
1121 close payroll_details;
1122 END IF;
1123
1124 hr_utility.trace(l_asg_action_id);
1125 hr_utility.trace(l_payroll_action_id);
1126 hr_utility.trace(l_effective_date);
1127 hr_utility.trace(l_payroll_id);
1128 -- Handle exceptions in c_act, set found = false.
1129 EXCEPTION WHEN OTHERS THEN
1130 hr_utility.trace('c_act raised: '|| sqlerrm(sqlcode));
1131 l_found := FALSE;
1132 END;
1133 ELSE
1134 -- No prior action exists at all, so must set found to false.
1135 hr_utility.trace('No Asg Action found');
1136 l_found := FALSE;
1137 END IF;
1138 end if; -- use c_act cursor if more performant cursors do not find vals
1139 end if; -- Aggregated PAYE
1140 --
1141 -- The above cursors have set the local vars and if so then the l_found
1142 -- has been set in one of the above 3 places. Therefore set the out
1143 -- params as necessary. Otherwise, set the out params to -9999
1144 --
1145 if l_found then
1146 hr_utility.trace('Found');
1147 l_override_date := override_date(x_assignment_id);
1148 l_taxable_update_action := l_asg_action_id;
1149 l_taxable_update_payroll := l_payroll_id;
1150 x_payroll_action_id := l_payroll_action_id;
1151 --
1152 -- Bug 5478073: Get tax year start for termination and latest action
1153 -- Bug 9794457 begin
1154 /* Moved this above so that can be used in c_act
1155 OPEN csr_get_term_tax_year_start;
1156 FETCH csr_get_term_tax_year_start INTO l_termination_ty_start;
1157 CLOSE csr_get_term_tax_year_start;
1158 hr_utility.trace('After csr_get_term_tax_year_start, l_termination_ty_start='||fnd_date.date_to_displaydate(l_termination_ty_start));
1159 */
1160 --
1161 OPEN csr_get_action_tax_year_start(l_asg_action_id);
1162 FETCH csr_get_action_tax_year_start INTO l_latest_action_ty_start;
1163 CLOSE csr_get_action_tax_year_start;
1164 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));
1165 --
1166 -- Bug 2332796. Use the least of date paid and override
1167 -- date to get the time period for EOY expiry check.
1168 --
1169 l_taxable_update_date :=
1170 least(l_effective_date,nvl(l_override_date,hr_general.end_of_time));
1171 open c_ptp(l_taxable_update_payroll,
1172 nvl(l_taxable_update_date, X_ASSIGNMENT_END_DATE));
1173 fetch c_ptp into l_ptp;
1174 l_found := c_ptp%found;
1175 close c_ptp;
1176
1177 if l_found then
1178 l_payroll_year_start := l_ptp.start_date;
1179 if l_payroll_year_start > l_taxable_update_date then
1180 x_assignment_action_id := -9999;
1181 x_date_earned := l_payroll_year_start;
1182 ------------------------------------------------------
1183 -- 5478073: above check is insuffucient hence added --
1184 -- following condition for tax year check --
1185 ------------------------------------------------------
1186 elsif l_latest_action_ty_start < l_termination_ty_start THEN
1187 x_assignment_action_id := -9999; -- to show 0 values on P45
1188 x_payroll_action_id := -9999; -- to show 0 values on P45
1189 x_date_earned := X_SESSION_DATE; -- get tax code/basis at issue date
1190 else
1191 x_assignment_action_id := l_taxable_update_action;
1192 x_date_earned := l_taxable_update_date;
1193 end if;
1194 else
1195 x_assignment_action_id := l_taxable_update_action;
1196 x_date_earned := l_taxable_update_date;
1197 end if;
1198
1199 else
1200 hr_utility.trace('Not found : ' || X_SESSION_DATE);
1201 l_taxable_update_date := NULL;
1202 X_DATE_EARNED := X_SESSION_DATE;
1203 X_ASSIGNMENT_ACTION_ID := -9999;
1204 x_payroll_action_id := -9999;
1205 end if;
1206 --
1207 exception when NO_DATA_FOUND then
1208 -- Set all variables the same as if l_found not true.
1209 l_taxable_update_date := NULL;
1210 X_DATE_EARNED := X_SESSION_DATE;
1211 X_ASSIGNMENT_ACTION_ID := -9999;
1212 x_payroll_action_id := -9999;
1213 end get_ff_data;
1214
1215
1216 procedure get_employer_address(X_ASSIGNMENT_ID in number,
1217 X_ASSIGNMENT_END_DATE in date,
1218 X_EMPLOYER_NAME in out nocopy varchar2,
1219 X_EMPLOYER_ADDRESS in out nocopy varchar2
1220 ) is
1221
1222 cursor employer_addr(L_ASSIGNMENT_ID number, c_assignment_end_date date) is
1223 select oi.ORG_INFORMATION3,
1224 oi.ORG_INFORMATION4,
1225 ass.ASSIGNMENT_ID
1226 from hr_organization_information oi,
1227 pay_payrolls_f roll,
1228 hr_soft_coding_keyflex flex,
1229 per_assignments_f ass,
1230 fnd_sessions sess
1231 where oi.ORG_INFORMATION_CONTEXT = 'Tax Details References'
1232 and roll.business_group_id + 0 = oi.organization_id
1233 /* normally P45 is for leaver so pick up data on the assignment_end_date */
1234 /* for non leavers eg. tax district change use the session date */
1235 and sess.SESSION_ID = userenv('sessionid')
1236 and nvl(c_assignment_end_date, sess.effective_date) between
1237 ass.effective_start_date and ass.effective_end_date
1238 and ass.payroll_id = roll.payroll_id
1239 and nvl(c_assignment_end_date, sess.effective_date) between
1240 roll.effective_start_date and roll.effective_end_date
1241 and ass.assignment_id = L_ASSIGNMENT_ID
1242 and flex.segment1 = oi.org_information1 /* same tax district */
1243 and flex.soft_coding_keyflex_id = roll.soft_coding_keyflex_id;
1244
1245 l_found boolean;
1246 l_employer_addr employer_addr%rowtype;
1247
1248 begin
1249
1250 open employer_addr(X_ASSIGNMENT_ID,X_ASSIGNMENT_END_DATE);
1251 fetch employer_addr into l_employer_addr;
1252 l_found := employer_addr%found;
1253 close employer_addr;
1254
1255 if l_found then
1256 -- truncate employer name and address to max limits allowed on Org Developer DF
1257 X_EMPLOYER_NAME := substr(l_employer_addr.ORG_INFORMATION3,1,36);
1258 X_EMPLOYER_ADDRESS := substr(l_employer_addr.ORG_INFORMATION4,1,60);
1259 else
1260 X_EMPLOYER_NAME := '';
1261 X_EMPLOYER_ADDRESS := '';
1262 end if;
1263
1264
1265 end;
1266
1267 procedure get_data(X_PERSON_ID in number,
1268 X_SESSION_DATE in date,
1269 X_ADDRESS_LINE1 in out nocopy varchar2,
1270 X_ADDRESS_LINE2 in out nocopy varchar2,
1271 X_ADDRESS_LINE3 in out nocopy varchar2,
1272 X_TOWN_OR_CITY in out nocopy varchar2,
1273 X_REGION_1 in out nocopy varchar2,
1274 X_COUNTRY in out nocopy varchar2,
1275 X_POSTAL_CODE in out nocopy varchar2,
1276 X_ASSIGNMENT_ID in number,
1277 X_ASSIGNMENT_ACTION_ID in out nocopy number,
1278 X_ASSIGNMENT_END_DATE in date,
1279 X_DATE_EARNED in out nocopy date,
1280 X_PAYROLL_ACTION_ID in out nocopy number,
1281 X_TRANSFER_DATE in date)
1282 is
1283 CURSOR csr_tax_ref (c_assignment_id in number,
1284 c_effective_end_date in date) is
1285 select scl.segment1
1286 from per_all_assignments_f paf,
1287 pay_all_payrolls_f ppf,
1288 hr_soft_coding_keyflex scl
1289 where paf.assignment_id = c_assignment_id
1290 and paf.payroll_id = ppf.payroll_id
1291 and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
1292 and c_effective_end_date between
1293 paf.effective_start_date and paf.effective_end_date
1294 and c_effective_end_date between
1295 ppf.effective_start_date and ppf.effective_end_date;
1296 --
1297 l_tax_reference varchar2(25);
1298 --
1299 begin
1300 --
1301 open csr_tax_ref(X_ASSIGNMENT_ID,X_ASSIGNMENT_END_DATE);
1302 fetch csr_tax_ref into l_tax_reference;
1303 close csr_tax_ref;
1304 --
1305 hr_utility.trace('Tax Ref: '||l_tax_reference);
1306 --
1307 person_address(X_PERSON_ID,
1308 X_SESSION_DATE,
1309 X_ADDRESS_LINE1,
1310 X_ADDRESS_LINE2,
1311 X_ADDRESS_LINE3,
1312 X_TOWN_OR_CITY,
1313 X_REGION_1,
1314 X_COUNTRY,
1315 X_POSTAL_CODE,
1316 X_ASSIGNMENT_END_DATE); -- added for bug9347169
1317
1318
1319 get_ff_data(X_SESSION_DATE,
1320 X_ASSIGNMENT_ID,
1321 X_ASSIGNMENT_END_DATE,
1322 X_ASSIGNMENT_ACTION_ID,
1323 X_DATE_EARNED,
1324 X_PAYROLL_ACTION_ID,
1325 nvl(X_TRANSFER_DATE,hr_general.end_of_time),
1326 X_PERSON_ID,
1327 l_tax_reference);
1328
1329 end get_data;
1330
1331 procedure get_data(X_PERSON_ID in number,
1332 X_SESSION_DATE in date,
1333 X_ADDRESS_LINE1 in out nocopy varchar2,
1334 X_ADDRESS_LINE2 in out nocopy varchar2,
1335 X_ADDRESS_LINE3 in out nocopy varchar2,
1336 X_TOWN_OR_CITY in out nocopy varchar2,
1337 X_REGION_1 in out nocopy varchar2,
1338 X_COUNTRY in out nocopy varchar2,
1339 X_POSTAL_CODE in out nocopy varchar2,
1340 X_ASSIGNMENT_ID in number,
1341 X_ASSIGNMENT_ACTION_ID in out nocopy number,
1342 X_ASSIGNMENT_END_DATE in date,
1343 X_DATE_EARNED in out nocopy date,
1344 X_PAYROLL_ACTION_ID in out nocopy number,
1345 X_EMPLOYER_NAME in out nocopy varchar2,
1346 X_EMPLOYER_ADDRESS in out nocopy varchar2,
1347 X_TRANSFER_DATE in date)
1348 is
1349 CURSOR csr_tax_ref (c_assignment_id in number,
1350 c_effective_end_date in date) is
1351 select scl.segment1
1352 from per_all_assignments_f paf,
1353 pay_all_payrolls_f ppf,
1354 hr_soft_coding_keyflex scl
1355 where paf.assignment_id = c_assignment_id
1356 and paf.payroll_id = ppf.payroll_id
1357 and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
1358 and c_effective_end_date between
1359 paf.effective_start_date and paf.effective_end_date
1360 and c_effective_end_date between
1361 ppf.effective_start_date and ppf.effective_end_date;
1362 --
1363 l_tax_reference varchar2(25);
1364 --
1365 begin
1366 --
1367 open csr_tax_ref(X_ASSIGNMENT_ID,X_ASSIGNMENT_END_DATE);
1368 fetch csr_tax_ref into l_tax_reference;
1369 close csr_tax_ref;
1370 hr_utility.trace('Tax Ref: '||l_tax_reference);
1371 --
1372 person_address(X_PERSON_ID,
1373 X_SESSION_DATE,
1374 X_ADDRESS_LINE1,
1375 X_ADDRESS_LINE2,
1376 X_ADDRESS_LINE3,
1377 X_TOWN_OR_CITY,
1378 X_REGION_1,
1379 X_COUNTRY,
1380 X_POSTAL_CODE,
1381 X_ASSIGNMENT_END_DATE); -- added for bug9347169
1382
1383
1384 get_ff_data(X_SESSION_DATE,
1385 X_ASSIGNMENT_ID,
1386 X_ASSIGNMENT_END_DATE,
1387 X_ASSIGNMENT_ACTION_ID,
1388 X_DATE_EARNED,
1389 X_PAYROLL_ACTION_ID,
1390 nvl(X_TRANSFER_DATE,hr_general.end_of_time),
1391 X_PERSON_ID, l_tax_reference);
1392
1393 get_employer_address(X_ASSIGNMENT_ID,
1394 X_ASSIGNMENT_END_DATE,
1395 X_EMPLOYER_NAME,
1396 X_EMPLOYER_ADDRESS);
1397
1398 end get_data;
1399
1400 procedure get_form_query_data(X_ASSIGNMENT_ID in number,
1401 X_LAST_NAME in out nocopy varchar2,
1402 X_TITLE in out nocopy varchar2,
1403 X_FIRST_NAME in out nocopy varchar2,
1404 X_NATIONAL_IDENTIFIER in out nocopy varchar2,
1405 X_PERSON_ID in out nocopy number,
1406 X_ACTUAL_TERMINATION_DATE in out nocopy date,
1407 X_DECEASED_FLAG in out nocopy varchar2,
1408 X_ASSIGNMENT_NUMBER in out nocopy varchar2,
1409 X_PAYROLL_ID in out nocopy number,
1410 X_ORGANIZATION_ID in out nocopy number,
1411 X_ORG_NAME in out nocopy varchar2,
1412 X_DATE_OF_BIRTH in out nocopy date, /* P45 A4 2008/09 */
1413 X_SEX in out nocopy varchar2) is /* P45 A4 2008/09 */
1414
1415
1416 cursor c_query (p_assignment_id number) is
1417 select p.last_name
1418 , p.title
1419 , p.first_name
1420 , p.middle_names
1421 , p.person_id
1422 , p.national_identifier
1423 , serv.actual_termination_date
1424 , decode(serv.leaving_reason,'D','D',NULL) deceased_flag
1425 , a.assignment_number
1426 , a.payroll_id
1427 , org.organization_id
1428 , org.name org_name
1429 , p.date_of_birth /* P45 A4 2008/09 */
1430 , p.sex /* P45 A4 2008/09 */
1431 from per_all_people_f p
1432 , per_all_assignments_f a
1433 , per_periods_of_service serv
1434 , hr_all_organization_units org
1435 , fnd_sessions sess
1436 where a.assignment_id = p_assignment_id
1437 and sess.session_id = userenv('sessionid')
1438 and sess.effective_date between
1439 a.effective_start_date and a.effective_end_date
1440 and a.person_id = p.person_id
1441 and sess.effective_date between
1442 p.effective_start_date and p.effective_end_date
1443 and serv.person_id = p.person_id
1444 and serv.date_start = ( select max(s.date_start) from per_periods_of_service s
1445 where s.person_id = p.person_id
1446 and sess.effective_date >= s.date_start )
1447 and a.organization_id = org.organization_id;
1448
1449 --l_query c_query%rowtype;
1450
1451 begin
1452
1453 for l_query_rec in c_query(X_ASSIGNMENT_ID) loop
1454 X_LAST_NAME := l_query_rec.last_name;
1455 X_TITLE := l_query_rec.title;
1456 X_FIRST_NAME := SUBSTR(l_query_rec.first_name || ' ' ||
1457 l_query_rec.middle_names, 1, 150);
1458 X_NATIONAL_IDENTIFIER := l_query_rec.national_identifier;
1459 X_PERSON_ID := l_query_rec.person_id;
1460 X_ACTUAL_TERMINATION_DATE := l_query_rec.actual_termination_date;
1461 X_DECEASED_FLAG := l_query_rec.deceased_flag;
1462 X_ASSIGNMENT_NUMBER := l_query_rec.assignment_number;
1463 X_PAYROLL_ID := l_query_rec.payroll_id;
1464 X_ORGANIZATION_ID := l_query_rec.organization_id;
1465 X_ORG_NAME := l_query_rec.org_name;
1466 X_DATE_OF_BIRTH := l_query_rec.date_of_birth; /* P45 A4 2008/09 */
1467 X_SEX := l_query_rec.sex; /* P45 A4 2008/09 */
1468
1469 end loop;
1470
1471
1472 end;
1473 --
1474 PROCEDURE range_cursor (pactid IN NUMBER,
1475 sqlstr OUT NOCOPY VARCHAR2)
1476 IS
1477 l_proc CONSTANT VARCHAR2(35):= g_package||'range_cursor';
1478 l_employers_name_eid ff_user_entities.user_entity_id%TYPE;
1479 l_employers_address_line_eid ff_user_entities.user_entity_id%TYPE;
1480 l_tax_district_name_eid ff_user_entities.user_entity_id%TYPE;
1481 -- vars for returns from the API:
1482 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
1483 l_ovn NUMBER;
1484 l_some_warning BOOLEAN;
1485 l_payroll_id NUMBER;
1486 l_chk_start_date DATE; -- BUG 5671777-11 to store start date
1487 l_chk_end_date DATE; -- BUG 5671777-11 to store end date
1488 p45_one_taxyear_error EXCEPTION; -- raised when P45 process is not fall in the
1489 -- same tax year BUG 5671777-11
1490 --
1491 l_test_indicator varchar2(1);
1492 l_test_id varchar2(8);
1493 l_report_type varchar2(15);
1494 l_report_category varchar2(15);
1495 test_indicator_error EXCEPTION;
1496 --
1497 cursor csr_user_entity(p_entity_name VARCHAR2) IS
1498 SELECT user_entity_id
1499 FROM ff_user_entities
1500 WHERE user_entity_name = p_entity_name
1501 AND legislation_code = 'GB'
1502 AND business_group_id IS NULL;
1503 --
1504 cursor csr_employer_details(p_payroll_action_id NUMBER) IS
1505 -- Select Employer details from Org DDF for specified Tax Ref, only
1506 -- if the action is for a P45 archive.
1507 SELECT
1508 substr(org.org_information3,1,36) employers_name,
1509 substr(org.org_information4,1,60) employers_address_line,
1510 substr(org.org_information2 ,1,40) tax_district_name
1511 FROM
1512 pay_payroll_actions ppa,
1513 hr_organization_information org
1514 WHERE ppa.payroll_action_id = p_payroll_action_id
1515 AND org.org_information_context = 'Tax Details References'
1516 AND NVL(org.org_information10,'UK') = 'UK'
1517 AND org.organization_id = ppa.business_group_id
1518 AND substr(ppa.legislative_parameters,
1519 instr(ppa.legislative_parameters,'TAX_REF=') + 8,
1520 instr(ppa.legislative_parameters||' ',' ',
1521 instr(ppa.legislative_parameters,'TAX_REF=')+8)
1522 - instr(ppa.legislative_parameters,'TAX_REF=') - 8)
1523 = org.org_information1
1524 AND ppa.report_category='P45';
1525 --
1526 -- TEST, TEST_ID, report_type and report_category added in cursor select
1527 -- for P45PT1. Bug 6345375
1528 cursor csr_get_payroll_param is
1529 select pay_core_utils.get_parameter('PAYROLL_ID',legislative_parameters) payroll_id,
1530 substr(pay_core_utils.get_parameter('TEST',legislative_parameters),1,1) test_indicator,
1531 trim(substr(pay_core_utils.get_parameter('TEST_ID',legislative_parameters),1,8)) test_id,
1532 report_type,
1533 report_category
1534 from pay_payroll_actions ppa
1535 where ppa.payroll_action_id = pactid;
1536 --
1537 -- Start of BUG 5671777-11
1538 --
1539 -- fetch start date and end date for the P45 process
1540 --
1541 CURSOR csr_get_p45_start_end_date(p_payroll_action_id NUMBER) IS
1542 SELECT
1543 start_date,
1544 fnd_date.canonical_to_date
1545 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1546 'DATE_TO')) end_date
1547 FROM pay_payroll_actions
1548 WHERE payroll_action_id = p_payroll_action_id;
1549
1550 --
1551 -- fetch start date and end date for the P45 EDI process
1552 --
1553
1554 CURSOR csr_get_p45_EDI_start_end_date(p_payroll_action_id NUMBER) IS
1555 SELECT
1556 fnd_date.canonical_to_date
1557 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1558 'DATE_FROM')) start_date,
1559 fnd_date.canonical_to_date
1560 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
1561 'END_DATE')) end_date
1562 FROM pay_payroll_actions
1563 WHERE payroll_action_id = p_payroll_action_id;
1564 -- End of BUG 5671777-11
1565 --
1566 rec_employer_details csr_employer_details%ROWTYPE;
1567 --
1568 BEGIN
1569 --
1570 hr_utility.set_location('Entering: '||l_proc,1);
1571 --
1572 -- Get the parameter payroll_id, if this has been used.
1573 --
1574 -- Added for P45PT1. Bug 6345375
1575 open csr_get_payroll_param;
1576 fetch csr_get_payroll_param into l_payroll_id, l_test_indicator,l_test_id,l_report_type,l_report_category;
1577 if csr_get_payroll_param%NOTFOUND then
1578 l_payroll_id := null;
1579 end if;
1580 close csr_get_payroll_param;
1581 --
1582 -- Added for P45PT1. Bug 6345375
1583 -- Log the error in Log_File if Test_Indicator is Yes, but Test_ID is not provided
1584 -- and Raise an unhandled exception to fail the process.
1585 --
1586 IF l_report_type = 'P45PT1' and l_report_category = 'EDI' THEN
1587 IF (l_test_indicator = 'Y' AND l_test_id IS NULL) THEN
1588 fnd_file.put_line (fnd_file.LOG,'Error : Enter the Test ID as the EDI Test Indicator is Yes.');
1589 RAISE test_indicator_error;
1590 END IF;
1591 END IF;
1592 --
1593 hr_utility.trace('Payroll_ID: '||to_char(l_payroll_id));
1594 -- Return Range Cursor
1595 -- Note: There must be one and only one entry of :payroll_action_id in
1596 -- the string, and the statement must be ordered by person_id
1597 --
1598 -- Start of BUG 5671777-11
1599 -- fetch start date and end date
1600 --
1601 BEGIN
1602
1603 OPEN csr_get_p45_start_end_date(pactid);
1604 FETCH csr_get_p45_start_end_date INTO l_chk_start_date,
1605 l_chk_end_date;
1606 CLOSE csr_get_p45_start_end_date;
1607
1608 IF l_chk_end_date IS NULL THEN
1609 OPEN csr_get_p45_EDI_start_end_date(pactid);
1610 FETCH csr_get_p45_EDI_start_end_date INTO l_chk_start_date,
1611 l_chk_end_date;
1612 CLOSE csr_get_p45_EDI_start_end_date;
1613
1614 END IF;
1615
1616
1617 --
1618 -- Check whether P45 Prcess to ensure it is run for one tax year at a time or not
1619 --
1620
1621 IF ((l_chk_start_date BETWEEN to_date('06/04/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')
1622 AND to_date('31/12/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy'))
1623 AND (l_chk_end_date BETWEEN to_date('06/04/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')
1624 AND to_date('05/04/'||to_char(to_number(to_char(l_chk_start_date,'YYYY'))+1),'dd/mm/yyyy')))
1625 OR ((l_chk_start_date BETWEEN to_date('01/01/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')
1626 AND to_date('05/04/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy'))
1627 AND (l_chk_end_date BETWEEN to_date('01/01/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')
1628 AND to_date('05/04/'||to_char(l_chk_start_date,'YYYY'),'dd/mm/yyyy')))
1629 THEN
1630 hr_utility.set_location('Start Date and End Date are in the same tax year ',6);
1631 ELSE
1632 fnd_file.put_line (fnd_file.LOG, 'The Start Date and the End Date must be within the tax year.');
1633 hr_utility.set_location('The Start Date and the End Date must be within the tax year.',8);
1634 RAISE p45_one_taxyear_error;
1635 END IF;
1636 END;
1637 -- End of BUG 5671777-11
1638
1639 OPEN csr_employer_details(pactid);
1640 FETCH csr_employer_details INTO rec_employer_details;
1641 IF csr_employer_details%FOUND THEN
1642 -- Action is for P45 Archive (not EDI)
1643 OPEN csr_user_entity('X_EMPLOYERS_ADDRESS_LINE');
1644 FETCH csr_user_entity INTO l_employers_address_line_eid;
1645 CLOSE csr_user_entity;
1646 OPEN csr_user_entity('X_EMPLOYERS_NAME');
1647 FETCH csr_user_entity INTO l_employers_name_eid;
1648 CLOSE csr_user_entity;
1649 OPEN csr_user_entity('X_TAX_DISTRICT_NAME');
1650 FETCH csr_user_entity INTO l_tax_district_name_eid;
1651 CLOSE csr_user_entity;
1652 -- Archive the employer details
1653 ff_archive_api.create_archive_item
1654 (p_archive_item_id => l_archive_item_id,
1655 p_user_entity_id => l_employers_address_line_eid,
1656 p_archive_value => rec_employer_details.employers_address_line,
1657 p_archive_type => 'PA',
1658 p_action_id => pactid,
1659 p_legislation_code => 'GB',
1660 p_object_version_number => l_ovn,
1661 p_context_name1 => 'PAYROLL_ID',
1662 p_context1 => '0',
1663 p_some_warning => l_some_warning);
1664 ff_archive_api.create_archive_item
1665 (p_archive_item_id => l_archive_item_id,
1666 p_user_entity_id => l_employers_name_eid,
1667 p_archive_value => rec_employer_details.employers_name,
1668 p_archive_type => 'PA',
1669 p_action_id => pactid,
1670 p_legislation_code => 'GB',
1671 p_object_version_number => l_ovn,
1672 p_context_name1 => 'PAYROLL_ID',
1673 p_context1 => '0',
1674 p_some_warning => l_some_warning);
1675 ff_archive_api.create_archive_item
1676 (p_archive_item_id => l_archive_item_id,
1677 p_user_entity_id => l_tax_district_name_eid,
1678 p_archive_value => rec_employer_details.tax_district_name,
1679 p_archive_type => 'PA',
1680 p_action_id => pactid,
1681 p_legislation_code => 'GB',
1682 p_object_version_number => l_ovn,
1683 p_context_name1 => 'PAYROLL_ID',
1684 p_context1 => '0',
1685 p_some_warning => l_some_warning);
1686 --
1687 END IF;
1688 CLOSE csr_employer_details;
1689 --
1690 hr_utility.set_location(l_proc,10);
1691 --
1692 --
1693 IF l_payroll_id is not null then
1694 -- Payroll ID has been used in param, restrict by this.
1695 hr_utility.set_location(l_proc,20);
1696 sqlstr := 'select distinct paaf.person_id '||
1697 'from pay_payroll_actions ppa, '||
1698 'per_all_assignments_f paaf '||
1699 'where ppa.payroll_action_id = :payroll_action_id '||
1700 'and paaf.business_group_id + 0 = ppa.business_group_id '||
1701 'and paaf.payroll_id = '||to_char(l_payroll_id)||
1702 ' order by paaf.person_id';
1703 --
1704 ELSE
1705 -- Normal range not restricting by payroll_id.
1706 hr_utility.set_location(l_proc,30);
1707 sqlstr := 'select distinct person_id '||
1708 'from per_people_f ppf, '||
1709 'pay_payroll_actions ppa '||
1710 'where ppa.payroll_action_id = :payroll_action_id '||
1711 'and ppa.business_group_id = ppf.business_group_id '||
1712 'order by ppf.person_id';
1713 END IF;
1714 hr_utility.set_location(' Leaving: '||l_proc,100);
1715 EXCEPTION
1716 --
1717 -- Start of BUG 5671777-11
1718 --
1719 WHEN p45_one_taxyear_error THEN
1720 sqlstr := 'select 1 '||
1721 '/* ERROR - The Start Date and the End Date must be within the tax year: '||
1722 sqlerrm(sqlcode)||' */ '||
1723 'from dual where to_char(:payroll_action_id) = dummy';
1724 RAISE;
1725 --
1726 -- End of BUG 5671777-11
1727 --
1728 -- Added for P45PT1. Bug 6345375.
1729 -- Raise Unhandled exception to fail the process.
1730 --
1731 WHEN test_indicator_error THEN
1732 RAISE;
1733 --
1734 --
1735 WHEN OTHERS THEN
1736 -- Return cursor that selects no rows
1737 sqlstr := 'select 1 '||
1738 '/* ERROR - Employer Details Fetch failed with: '||
1739 sqlerrm(sqlcode)||' */ '||
1740 'from dual where to_char(:payroll_action_id) = dummy';
1741 hr_utility.set_location(' Leaving: '||l_proc,110);
1742 END range_cursor;
1743 ---------------------------------------------------------------------------
1744 -- Function: range_person_on.
1745 -- Description: Returns true if the range_person performance enhancement is
1746 -- enabled for the system. Used by arch_act_creation, edi_act_creation.
1747 ---------------------------------------------------------------------------
1748 FUNCTION range_person_on (p_report_format in varchar2) RETURN BOOLEAN IS
1749 --
1750 CURSOR csr_action_parameter is
1751 select parameter_value
1752 from pay_action_parameters
1753 where parameter_name = 'RANGE_PERSON_ID';
1754 --
1755 CURSOR csr_range_format_param (c_report_format in varchar2) is
1756 select par.parameter_value
1757 from pay_report_format_parameters par,
1758 pay_report_format_mappings_f map
1759 where map.report_format_mapping_id = par.report_format_mapping_id
1760 and map.report_type = 'P45'
1761 and map.report_format = c_report_format
1762 and map.report_qualifier = 'GB'
1763 and par.parameter_name = 'RANGE_PERSON_ID';
1764 --
1765 l_return boolean;
1766 l_action_param_val varchar2(30);
1767 l_report_param_val varchar2(30);
1768 --
1769 BEGIN
1770 hr_utility.set_location('range_person_on',10);
1771 --
1772 BEGIN
1773 open csr_action_parameter;
1774 fetch csr_action_parameter into l_action_param_val;
1775 close csr_action_parameter;
1776 --
1777 hr_utility.set_location('range_person_on',20);
1778 open csr_range_format_param(p_report_format);
1779 fetch csr_range_format_param into l_report_param_val;
1780 close csr_range_format_param;
1781 --
1782 hr_utility.set_location('range_person_on',30);
1783 EXCEPTION WHEN NO_DATA_FOUND THEN
1784 l_return := FALSE;
1785 END;
1786 --
1787 hr_utility.set_location('range_person_on',40);
1788 IF l_action_param_val = 'Y' AND l_report_param_val = 'Y' THEN
1789 l_return := TRUE;
1790 hr_utility.trace('Range Person = True');
1791 ELSE
1792 l_return := FALSE;
1793 END IF;
1794 --
1795 RETURN l_return;
1796 --
1797 END range_person_on;
1798 ---------------------------------------------------------------------------
1799 -- FUNCTION: p45_existing_action
1800 -- DESCRIPTION: boolean function for the existence of a leaver or tfr
1801 -- P45 action (archived). NB Mark for print not used.
1802 --------------------------------------------------------------------------
1803 FUNCTION p45_existing_action(p_assignment_id in number,
1804 p_period_of_service_id in number,
1805 p_mode in varchar2) RETURN BOOLEAN
1806 IS
1807 --
1808 l_p45_action_exists boolean := FALSE;
1809 l_arc_period_of_service_id ff_archive_items.value%TYPE;
1810 l_arc_tax_ref_transfer ff_archive_items.value%TYPE;
1811 --
1812 /*
1813 cursor csr_check_action(c_assignment_id in number) is
1814 select
1815 max(decode(fai.user_entity_id,g_period_of_service_eid,fai.VALUE)) pos
1816 ,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
1817 from ff_archive_items fai, -- of P45 report type
1818 pay_assignment_actions act,
1819 pay_payroll_actions ppa
1820 where ppa.report_type='P45'
1821 and ppa.report_qualifier='GB'
1822 and ppa.report_category ='P45'
1823 and ppa.action_type = 'X'
1824 and ppa.payroll_action_id = act.payroll_action_id
1825 and act.assignment_id = c_assignment_id
1826 and act.assignment_action_id = fai.context1
1827 and fai.user_entity_id in (g_tax_ref_transfer_eid,
1828 g_period_of_service_eid);
1829 */
1830 cursor csr_check_action(c_assignment_id in number) is
1831 select max(decode(fai.user_entity_id,g_period_of_service_eid,fai.VALUE)) pos
1832 ,max(decode(fai.user_entity_id,g_tax_ref_transfer_eid,fai.VALUE)) tfr
1833 from ff_archive_items fai
1834 where fai.user_entity_id in (g_tax_ref_transfer_eid,g_period_of_service_eid)
1835 and fai.context1 = (select max(act.assignment_action_id)
1836 from pay_payroll_actions ppa,
1837 pay_assignment_actions act
1838 where ppa.report_type='P45'
1839 and ppa.report_qualifier='GB'
1840 and ppa.report_category ='P45'
1841 and ppa.action_type = 'X'
1842 and ppa.payroll_action_id = act.payroll_action_id
1843 and act.assignment_id = c_assignment_id);
1844 --
1845 BEGIN
1846
1847 open csr_check_action(p_assignment_id);
1848 fetch csr_check_action into l_arc_period_of_service_id,
1849 l_arc_tax_ref_transfer;
1850 --
1851 -- Fix bug 4120027
1852 if l_arc_period_of_service_id is null and csr_check_action%FOUND then
1853 if p_mode = 'LEAVER' then
1854 if l_arc_tax_ref_transfer = 'N' then
1855 l_p45_action_exists := TRUE;
1856 else
1857 l_p45_action_exists := FALSE; -- the default
1858 end if;
1859 elsif p_mode = 'TRANSFER' then
1860 if l_arc_tax_ref_transfer = 'Y' then
1861 l_p45_action_exists := TRUE;
1862 else
1863 l_p45_action_exists := FALSE; -- the default
1864 end if;
1865 end if;
1866 else
1867 if p_mode = 'LEAVER' then
1868 -- If the archived period of service matches the live, and
1869 -- the archive action is not a Taxref transfer
1870 --
1871 if l_arc_period_of_service_id = p_period_of_service_id
1872 and l_arc_tax_ref_transfer = 'N' then
1873 l_p45_action_exists := TRUE;
1874 else
1875 l_p45_action_exists := FALSE; -- the default
1876 end if;
1877 elsif p_mode = 'TRANSFER' then
1878 -- If the archived period of service matches the live, and
1879 -- the archive action IS a transfer.
1880 if l_arc_period_of_service_id = p_period_of_service_id
1881 and l_arc_tax_ref_transfer = 'Y' then
1882 l_p45_action_exists := TRUE;
1883 else
1884 l_p45_action_exists := FALSE; -- the default
1885 end if;
1886 end if;
1887 end if;
1888
1889 close csr_check_action;
1890 --
1891 RETURN l_p45_action_exists;
1892
1893 end p45_existing_action;
1894 --------------------------------------------------------------------------
1895 -- FUNCTION future_active_exists
1896 -- Returns TRUE if active or suspended asg exists after the current asg.
1897 --------------------------------------------------------------------------
1898 FUNCTION future_active_exists (p_assignment_id in number,
1899 p_effective_end_date in date) RETURN BOOLEAN
1900 IS
1901 --
1902 l_future_asg boolean := FALSE;
1903 l_number number;
1904 --
1905 cursor csr_future_assignment (c_assignment_id in number,
1906 c_effective_end_date in date) is
1907 select 1 from dual where exists
1908 (select paf.effective_end_date
1909 from per_all_assignments_f paf,
1910 per_assignment_status_types past
1911 where past.assignment_status_type_id = paf.assignment_status_type_id
1912 and paf.assignment_id = c_assignment_id
1913 and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
1914 and paf.effective_end_date > c_effective_end_date);
1915 --
1916 BEGIN
1917 --
1918 open csr_future_assignment(p_assignment_id, p_effective_end_date);
1919 fetch csr_future_assignment into l_number;
1920 if csr_future_assignment%FOUND then
1921 l_future_asg := TRUE;
1922 else
1923 l_future_asg := FALSE;
1924 end if;
1925 --
1926 RETURN l_future_asg;
1927 end future_active_exists;
1928 --
1929 --------------------------------------------------------------------------
1930 -- FUNCTION is_transferred
1931 -- DESCRIPTION Check whether an assignment has transferred Tax
1932 -- Districts
1933 --------------------------------------------------------------------------
1934 FUNCTION is_transferred (p_assignment_id in number,
1935 p_effective_end_date in date,
1936 p_tax_ref in varchar2,
1937 /*Bug 13428461: Added payroll_id as parameter to be used in the cursor*/
1938 p_payroll_id in number) RETURN BOOLEAN IS
1939 --
1940 l_exists number;
1941 l_transfer_done boolean := FALSE;
1942 --
1943 cursor csr_transfer (c_assignment_id in number,
1944 c_effective_end_date in date,
1945 c_tax_ref in varchar2) is
1946 select 1 from dual where exists
1947 (select scl.segment1
1948 from per_all_assignments_f paf,
1949 pay_all_payrolls_f ppf,
1950 hr_soft_coding_keyflex scl
1951 where paf.assignment_id = c_assignment_id
1952 and paf.payroll_id = ppf.payroll_id
1953 and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
1954 and scl.segment1 <> c_tax_ref
1955 --Bug:13428461:
1956 and ppf.payroll_id <> p_payroll_id
1957 --End 13428461:
1958 and paf.effective_start_date between
1959 ppf.effective_start_date and ppf.effective_end_date
1960 and paf.effective_end_date > c_effective_end_date);
1961 --
1962 BEGIN
1963 open csr_transfer(p_assignment_id, p_effective_end_date, p_tax_ref);
1964 fetch csr_transfer into l_exists;
1965 if csr_transfer%FOUND then
1966 l_transfer_done := TRUE;
1967 else
1968 l_transfer_done := FALSE;
1969 end if;
1970 --
1971 RETURN l_transfer_done;
1972 --
1973 END is_transferred;
1974 --------------------------------------------------------------------------
1975 -- FUNCTION payment_made
1976 -- DESCRIPTION Check whether final payment has been made for an assignment
1977 -- after the end date so that the p45 can be issued. If not,
1978 -- and a Last Standard Process has been set, check that an asg
1979 -- action exists in the period immediately preceding LSP date.
1980 --------------------------------------------------------------------------
1981 --
1982 FUNCTION payment_made (p_assignment_id in number,
1983 p_effective_end_date in date,
1984 p_period_of_service_id in number) RETURN boolean IS
1985 --
1986 l_payment_made boolean := FALSE;
1987 l_number number;
1988 l_lsp_date date;
1989 --
1990 cursor csr_payment (c_assignment_id in number,
1991 c_effective_end_date in date) is
1992 select 1 from dual where exists
1993 (select pa.effective_date
1994 from pay_payroll_actions pa,
1995 pay_assignment_actions aa
1996 where aa.assignment_id = c_assignment_id
1997 and aa.payroll_action_id = pa.payroll_action_id
1998 and pa.action_type in ('R','Q','V','I','B')
1999 and pa.date_earned >= c_effective_end_date);
2000 --
2001 cursor csr_last_standard_process (c_period_of_service_id in number) is
2002 select serv.last_standard_process_date
2003 from per_periods_of_service serv
2004 where serv.period_of_service_id = c_period_of_service_id;
2005 --
2006 cursor csr_lsp_payment(c_last_process_date in date,
2007 c_effective_end_date in date,
2008 c_assignment_id in number) is
2009 select 1 from dual where exists
2010 (select paa.assignment_action_id
2011 from pay_assignment_actions paa,
2012 pay_payroll_actions ppa,
2013 per_time_periods ptp
2014 where ptp.time_period_id = ppa.time_period_id
2015 and ppa.payroll_action_id = paa.payroll_action_id
2016 and paa.assignment_id = c_assignment_id
2017 and ppa.action_type in ('R','Q','V','I','B')
2018 and ptp.regular_payment_date =
2019 (select max(ptp.regular_payment_date)
2020 from per_all_assignments_f paf,
2021 per_time_periods ptp
2022 where ptp.regular_payment_date <= c_last_process_date
2023 and paf.assignment_id = c_assignment_id
2024 and ptp.payroll_id = paf.payroll_id
2025 and c_effective_end_date between
2026 paf.effective_start_date and paf.effective_end_date));
2027 --
2028 BEGIN
2029 --
2030 -- First check whether there has been any payments made
2031 -- after asgs end date, as there will be in most cases:
2032 --
2033 open csr_payment (p_assignment_id, p_effective_end_date);
2034 fetch csr_payment into l_number;
2035 if csr_payment%FOUND then
2036 l_payment_made := TRUE;
2037 else
2038 l_payment_made := FALSE;
2039 end if;
2040 close csr_payment;
2041 --
2042 IF l_payment_made = FALSE THEN
2043 --
2044 -- User may have set Last Standard Process to before default
2045 -- date or on termination. Check the date
2046 --
2047 open csr_last_standard_process (p_period_of_service_id);
2048 fetch csr_last_standard_process into l_lsp_date;
2049 close csr_last_standard_process;
2050 --
2051 IF l_lsp_date is null then
2052 -- No Last Standard Process Date and no Payment made.
2053 l_payment_made := FALSE;
2054 ELSE
2055 -- LSP Populated, so check assignment actions for assignment
2056 -- during the time period whose RPD immediately precedes the LSP
2057 open csr_lsp_payment(l_lsp_date, p_effective_end_date, p_assignment_id);
2058 fetch csr_lsp_payment into l_number;
2059 if csr_lsp_payment%FOUND then
2060 l_payment_made := TRUE;
2061 else
2062 l_payment_made := FALSE;
2063 end if;
2064 END IF;
2065 END IF;
2066 --
2067 RETURN l_payment_made;
2068 --
2069 end payment_made;
2070 --------------------------------------------------------------------------
2071 -- FUNCTION manual_issue_exists
2072 -- DESCRIPTION Find whether there has been a manual issue date set.
2073 --------------------------------------------------------------------------
2074 FUNCTION manual_issue_exists(p_assignment_id in number) RETURN BOOLEAN IS
2075 --
2076 l_number number;
2077 l_manual_issue_exists BOOLEAN;
2078 --
2079 cursor csr_manual_date (c_assignment_id in number) is
2080 select 1 from dual where exists
2081 (select aei.aei_information3
2082 from per_assignment_extra_info aei
2083 where aei.assignment_id = c_assignment_id
2084 and aei.aei_information3 is not null
2085 and aei.information_type = 'GB_P45');
2086 --
2087 BEGIN
2088 open csr_manual_date(p_assignment_id);
2089 fetch csr_manual_date into l_number;
2090 if csr_manual_date%NOTFOUND then
2091 l_manual_issue_exists := FALSE;
2092 else
2093 l_manual_issue_exists := TRUE;
2094 end if;
2095 close csr_manual_date;
2096 --
2097 RETURN l_manual_issue_exists;
2098 END manual_issue_exists;
2099 --------------------------------------------------------------------------
2100 -- FUNCTION tax_code_ni
2101 -- DESCRIPTION Check that the final tax code of the assignment is not
2102 -- 'NI', as we should not produce p45 for such asgs
2103 --------------------------------------------------------------------------
2104 FUNCTION tax_code_ni(p_assignment_id in number,
2105 p_effective_end_date in date) RETURN BOOLEAN IS
2106 --
2107 l_tax_code_ni boolean;
2108 l_number number;
2109 l_latest_asg_action_id number;
2110 --
2111 cursor csr_latest_action (c_assignment_id in number) is
2112 -- c_effective_end_date in date) is --Bug 9036584
2113 select fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0') ||
2114 paa.assignment_action_id),16))
2115 FROM pay_assignment_actions paa,
2116 pay_payroll_actions ppa
2117 WHERE
2118 paa.assignment_id = c_assignment_id
2119 AND ppa.payroll_action_id = paa.payroll_action_id
2120 AND (paa.source_action_id is not null
2121 or ppa.action_type in ('I','V','B'))
2122 -- AND ppa.effective_date <= c_effective_end_date --Bug 9036584
2123 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
2124 --
2125 cursor csr_tax_code_ni (c_assignment_action_id in number) is
2126 -- c_effective_end_date in date) is --Bug 9036584
2127 select 1 from dual where exists
2128 (select prrv.result_value
2129 from pay_input_values_f inv,
2130 pay_element_types_f type,
2131 pay_run_results prr,
2132 pay_run_result_values prrv
2133 where prrv.input_value_id = inv.input_value_id
2134 and inv.name = 'Tax Code'
2135 and prr.assignment_action_id = c_assignment_action_id
2136 /* and c_effective_end_date between
2137 inv.effective_start_date and inv.effective_end_date
2138 and c_effective_end_date between
2139 type.effective_start_date and type.effective_end_date */--Bug 9036584
2140 and prrv.result_value = 'NI'
2141 and type.element_name = 'PAYE Details'
2142 and type.element_type_id = prr.element_type_id
2143 and prrv.run_result_id = prr.run_result_id);
2144 --
2145 BEGIN
2146 /* 9036584 - P45 should check for tax code of NI as of the last process
2147 which is being included in the P45, rather than before the actual termination date.
2148 Hence removing the date(termination date) parameter for the below two cursors */
2149 --
2150 -- open csr_latest_action(p_assignment_id,p_effective_end_date); --Bug 9036584
2151 open csr_latest_action(p_assignment_id);
2152 fetch csr_latest_action into l_latest_asg_action_id;
2153 close csr_latest_action;
2154 --
2155 -- open csr_tax_code_ni(l_latest_asg_action_id,p_effective_end_date); --Bug 9036584
2156 open csr_tax_code_ni(l_latest_asg_action_id);
2157 fetch csr_tax_code_ni into l_number;
2158 if csr_tax_code_ni%FOUND then
2159 l_tax_code_ni := TRUE;
2160 else l_tax_code_ni := FALSE;
2161 end if;
2162 --
2163 RETURN l_tax_code_ni;
2164
2165 END tax_code_ni;
2166 --------------------------------------------------------------------------
2167 -- NEW procedure for dealing with aggregated and non agg assignments
2168 --------------------------------------------------------------------------
2169 PROCEDURE arch_act_creation(pactid IN NUMBER,
2170 stperson IN NUMBER,
2171 endperson IN NUMBER,
2172 chunk IN NUMBER) IS
2173 --
2174 TYPE g_type_asg_rec IS RECORD (
2175 assignment_id number,
2176 assignment_number varchar2(40),
2177 period_of_service_id number,
2178 person_id number,
2179 agg_paye_flag char,
2180 asg_end_date date,
2181 regular_payment_date date
2182 );
2183
2184 l_proc CONSTANT VARCHAR2(35):= g_package||'arch_act_creation';
2185 l_number number;
2186 l_actid pay_assignment_actions.assignment_action_id%TYPE;
2187 l_non_p45_message varchar2(50);
2188 l_transfer_flag varchar2(1);
2189 l_override_date date;
2190 l_archive boolean;
2191 l_check_main_flag boolean;
2192 l_range_person_on varchar2(3);
2193 rec_asg g_type_asg_rec;
2194 --
2195 -- vars for returns from the API:
2196 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
2197 l_ovn NUMBER;
2198 l_some_warning BOOLEAN;
2199 -- 5144323: To store ids of assignments included on the P45
2200 TYPE l_included_asg_tab_type IS TABLE OF NUMBER
2201 INDEX BY binary_integer;
2202 --
2203 l_inc_asg_tab l_included_asg_tab_type;
2204 l_empty_asg_tab l_included_asg_tab_type;
2205 l_inc_asg_index NUMBER;
2206 --
2207 cursor csr_parameter_info(p_payroll_action_id NUMBER) IS
2208 SELECT
2209 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2210 'PAYROLL_ID')) payroll_id,
2211 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2212 'TAX_REF'),1,20) tax_ref,
2213 start_date,
2214 effective_date,
2215 fnd_date.canonical_to_date
2216 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
2217 'DATE_TO')) end_date,
2218 business_group_id
2219 FROM pay_payroll_actions
2220 WHERE payroll_action_id = p_payroll_action_id;
2221 --
2222 cursor csr_user_entity(p_entity_name VARCHAR2) IS
2223 SELECT user_entity_id
2224 FROM ff_user_entities
2225 WHERE user_entity_name = p_entity_name
2226 AND legislation_code = 'GB'
2227 AND business_group_id IS NULL;
2228 --
2229 cursor csr_person_agg_asg (c_person_id in number,
2230 c_tax_ref in varchar2,
2231 c_assignment_id in number,
2232 c_period_of_service_id in number,
2233 c_term_date in date,
2234 c_agg_start_date in date,
2235 c_agg_end_date in date) is
2236 select a.assignment_id,
2237 a.effective_end_date
2238 from per_all_assignments_f a,
2239 pay_all_payrolls_f pay,
2240 hr_soft_coding_keyflex flex,
2241 per_periods_of_service serv
2242 where a.person_id = c_person_id
2243 and flex.segment1 = c_tax_ref
2244 and pay.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2245 and a.payroll_id = pay.payroll_id
2246 and a.effective_end_date between
2247 pay.effective_start_date and pay.effective_end_date
2248 and serv.period_of_service_id = a.period_of_service_id
2249 and a.assignment_id <> c_assignment_id
2250 and a.period_of_service_id = c_period_of_service_id
2251 -- 5144323: only last active/suspended dt instances of the
2252 -- assignemnts are needed
2253 AND a.effective_end_date = ( SELECT max(effective_end_date)
2254 FROM per_all_assignments_f a1,
2255 per_assignment_status_types past
2256 WHERE a.assignment_id = a1.assignment_id
2257 AND a1.assignment_status_type_id = past.assignment_status_type_id
2258 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN'))
2259 -- 5144323: assignments must exist during the aggregation period
2260 AND EXISTS (SELECT 1 FROM per_all_assignments_f a2
2261 WHERE a.assignment_id = a2.assignment_id
2262 AND a2.effective_start_date <= c_agg_end_date
2263 AND a2.effective_end_date >= c_agg_start_date)
2264 -- 5144323: assignments must share continuous period of
2265 -- employment with the input assignment
2266 AND EXISTS (SELECT 1
2267 FROM per_all_assignments_f a3,
2268 per_assignment_status_types past
2269 WHERE a.assignment_id = a3.assignment_id
2270 AND a3.assignment_status_type_id = past.assignment_status_type_id
2271 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
2272 AND a3.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_ref, c_term_date)
2273 AND a3.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_ref, c_term_date));
2274
2275 --
2276 /* Fix performance bug by split cursor into 2 */
2277 cursor csr_all_assignments is
2278 SELECT /*+ ORDERED */
2279 a.assignment_id,
2280 a.assignment_number,
2281 a.period_of_service_id,
2282 p.person_id,
2283 decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
2284 max(a.effective_end_date) asg_end_date,
2285 ptp.regular_payment_date
2286 FROM per_all_people_f p,
2287 per_all_assignments_f a,
2288 per_assignment_status_types past,
2289 pay_all_payrolls_f ppf,
2290 per_time_periods ptp,
2291 per_periods_of_service serv,
2292 hr_soft_coding_keyflex flex
2293 WHERE a.person_id BETWEEN stperson AND endperson
2294 AND a.business_group_id +0 = g_business_group_id
2295 AND (g_payroll_id is null
2296 or
2297 a.payroll_id + 0 = g_payroll_id)
2298 AND a.effective_end_date BETWEEN g_start_date AND g_end_date
2299 AND a.payroll_id = ppf.payroll_id
2300 AND a.period_of_service_id = serv.period_of_service_id
2301 AND a.effective_end_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2302 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2303 AND flex.segment1 = g_tax_ref
2304 AND ppf.payroll_id = ptp.payroll_id
2305 AND a.effective_end_date BETWEEN ptp.start_date AND ptp.end_date
2306 AND a.effective_end_date <= g_end_date -- before run end date
2307 AND g_effective_date >= a.effective_start_date -- added by kvinayku for bug 14206931,15971584
2308
2309 -- AND a.effective_end_date = -- the latest active or susp asg exclude DT update
2310 -- (select max(asg2.effective_end_date)
2311 -- from per_all_assignments_f asg2,
2312 -- per_assignment_status_types past
2313 -- where asg2.assignment_id = a.assignment_id
2314 -- and asg2.assignment_status_type_id =past.assignment_status_type_id
2315 -- and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2316 -- and asg2.effective_end_date <> hr_general.end_of_time)
2317 AND a.assignment_status_type_id =past.assignment_status_type_id
2318 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2319 AND a.effective_end_date <> hr_general.end_of_time
2320 AND a.person_id = p.person_id
2321 AND a.effective_end_date between p.effective_start_date and p.effective_end_date
2322 GROUP BY a.assignment_id, a.assignment_number, a.period_of_service_id,
2323 p.person_id, decode(p.per_information10,'Y','Y',NULL),
2324 ptp.regular_payment_date
2325 --BUG:13428461
2326 order by ptp.regular_payment_date desc, a.assignment_id, a.assignment_number, a.period_of_service_id,
2327 p.person_id, decode(p.per_information10,'Y','Y',NULL);
2328 --
2329 -- The 2nd half of union is a copy of csr_all_assignments, but with a join to
2330 -- pay_population_ranges, for performance improvement if the range_person
2331 -- functionality is enabled. Only 1 half will be used as these are made exclusive
2332 -- by the parameter c_range_person_on.
2333 --
2334 -- UNION ALL
2335 --
2336 cursor csr_all_assignments_range is
2337 SELECT /*+ ORDERED*/
2338 a.assignment_id, a.assignment_number,
2339 a.period_of_service_id,
2340 p.person_id,
2341 decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
2342 max(a.effective_end_date) asg_end_date,
2343 ptp.regular_payment_date
2344 FROM pay_population_ranges ppr,
2345 per_all_people_f p,
2346 per_all_assignments_f a,
2347 per_assignment_status_types past,
2348 pay_all_payrolls_f ppf,
2349 per_time_periods ptp,
2350 per_periods_of_service serv,
2351 hr_soft_coding_keyflex flex
2352 WHERE p.person_id = ppr.person_id
2353 AND ppr.chunk_number = chunk
2354 AND ppr.payroll_action_id = pactid
2355 AND a.business_group_id +0 = g_business_group_id
2356 AND a.payroll_id +0 = nvl(g_payroll_id,a.payroll_id)
2357 AND a.effective_end_date
2358 BETWEEN g_start_date AND g_end_date
2359 AND a.payroll_id = ppf.payroll_id
2360 AND a.period_of_service_id = serv.period_of_service_id
2361 AND a.effective_end_date
2362 BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2363 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2364 AND flex.segment1 = g_tax_ref
2365 AND ppf.payroll_id = ptp.payroll_id
2366 AND a.effective_end_date
2367 BETWEEN ptp.start_date AND ptp.end_date
2368 AND a.effective_end_date <= g_end_date -- before run end date
2369 AND g_effective_date >= a.effective_start_date -- added by kvinayku for bug 14206931,15971584
2370
2371 --AND a.effective_end_date = -- the latest active or susp asg exclude DT update
2372 -- (select max(asg2.effective_end_date)
2373 -- from per_all_assignments_f asg2,
2374 -- per_assignment_status_types past
2375 -- where asg2.assignment_id = a.assignment_id
2376 -- and asg2.assignment_status_type_id =
2377 -- past.assignment_status_type_id
2378 -- and past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2379 -- and asg2.effective_end_date <> hr_general.end_of_time)
2380 AND a.assignment_status_type_id =past.assignment_status_type_id
2381 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2382 AND a.effective_end_date <> hr_general.end_of_time
2383 AND a.person_id = p.person_id
2384 AND a.effective_end_date between p.effective_start_date and p.effective_end_date
2385 GROUP BY a.assignment_id, a.assignment_number, a.period_of_service_id,
2386 p.person_id, decode(p.per_information10,'Y','Y',NULL),
2387 ptp.regular_payment_date
2388 --BUG 13428461:
2389 ORDER BY ptp.regular_payment_date desc, a.assignment_id, a.assignment_number, a.period_of_service_id,
2390 p.person_id, decode(p.per_information10,'Y','Y',NULL);
2391 --END -BUG:13428461
2392 -------------------------------------------------------------------------------------
2393 -- Cursor to find last date after assignment's termination date until which
2394 -- aggregation flag has remained Y - assuming flag is Y at the termination date
2395 CURSOR get_agg_end(p_person_id NUMBER, p_term_date DATE) IS
2396 SELECT nvl((min(effective_start_date)-1), hr_general.end_of_time) agg_end_date
2397 FROM per_all_people_f
2398 WHERE person_id = p_person_id
2399 AND effective_start_date > p_term_date
2400 AND nvl(per_information10, 'N') = 'N';
2401 --
2402 CURSOR get_agg_start(p_person_id NUMBER, p_term_date DATE) IS
2403 SELECT nvl((max(effective_end_date)+1), hr_general.start_of_time) agg_start_date
2404 FROM per_all_people_f
2405 WHERE person_id = p_person_id
2406 AND effective_end_date < p_term_date
2407 AND nvl(per_information10, 'N') = 'N';
2408 --
2409 l_agg_start_date DATE;
2410 l_agg_end_date DATE;
2411 BEGIN
2412 hr_utility.set_location('Entering: '||l_proc,1);
2413 --
2414 IF NOT g_asg_creation_cache_populated THEN
2415 OPEN csr_user_entity('X_TAX_REF_TRANSFER');
2416 FETCH csr_user_entity INTO g_tax_ref_transfer_eid;
2417 CLOSE csr_user_entity;
2418 OPEN csr_user_entity('X_TERMINATION_DATE');
2419 FETCH csr_user_entity INTO g_termination_date_eid;
2420 CLOSE csr_user_entity;
2421 OPEN csr_user_entity('X_PERIOD_OF_SERVICE_ID');
2422 FETCH csr_user_entity INTO g_period_of_service_eid;
2423 CLOSE csr_user_entity;
2424 OPEN csr_user_entity('X_P45_INCLUDED_ASSIGNMENT');
2425 FETCH csr_user_entity INTO g_p45_inc_assignment;
2426 CLOSE csr_user_entity;
2427 --
2428 OPEN csr_parameter_info(pactid);
2429 FETCH csr_parameter_info INTO g_payroll_id,
2430 g_tax_ref,
2431 g_start_date,
2432 g_effective_date,
2433 g_end_date,
2434 g_business_group_id;
2435 CLOSE csr_parameter_info;
2436 --
2437 g_asg_creation_cache_populated := true;
2438 END IF;
2439 --
2440 -- Check whether range_person_functionality is used or not.
2441 -- convert boolean to varchar for use in cursor.
2442 --
2443 IF range_person_on('REPORT') then
2444 l_range_person_on := 'YES';
2445 open csr_all_assignments_range;
2446 ELSE
2447 l_range_person_on := 'NO';
2448 open csr_all_assignments;
2449 END IF;
2450 hr_utility.trace('Range person on: '||l_range_person_on);
2451 --
2452 -- Use First half of cursor where range_person not set,
2453 -- use second half for range_person on (performance enhancement)
2454 --
2455 -- FOR rec_asg IN csr_all_assignments(l_range_person_on) LOOP
2456 --
2457 LOOP
2458 if l_range_person_on = 'YES' then
2459 fetch csr_all_assignments_range into rec_asg;
2460 exit when csr_all_assignments_range%notfound;
2461 else
2462 fetch csr_all_assignments into rec_asg;
2463 exit when csr_all_assignments%notfound;
2464 end if;
2465 --
2466 --
2467 l_archive := TRUE;
2468 hr_utility.trace(to_char(rec_asg.assignment_id)||
2469 ' '||rec_asg.agg_paye_flag||' '||
2470 to_char(rec_asg.asg_end_date,'dd-mon-yyyy')||':');
2471 hr_utility.trace('----------------------------');
2472 --
2473 -- initialize the included asg table and index
2474 l_inc_asg_index := 0;
2475 l_inc_asg_tab := l_empty_asg_tab;
2476 --
2477 IF NOT (is_transferred(rec_asg.assignment_id,
2478 rec_asg.asg_end_date, g_tax_ref, g_payroll_id))
2479 THEN
2480 --
2481 l_transfer_flag := 'N';
2482 -- Is there an assignment with active status existing
2483 -- with this ID at a later date.
2484 IF future_active_exists(rec_asg.assignment_id,
2485 rec_asg.asg_end_date) then
2486 l_non_p45_message := 'Future Active asg exists';
2487 l_archive := FALSE;
2488 -- This asg is to be excluded
2489 ELSE
2490 hr_utility.set_location(l_proc,5);
2491 IF rec_asg.agg_paye_flag = 'Y' then
2492 --
2493 -- Check current asg for existing p45 actions
2494 IF return_p45_issued_flag(rec_asg.assignment_id) = 'Y' then
2495 hr_utility.set_location(l_proc,6);
2496 -- Cannot archive this assignment, set msg.
2497 l_non_p45_message := 'Agg: P45 exists';
2498 l_archive := FALSE;
2499 ELSE
2500 hr_utility.set_location(l_proc,7);
2501 -- 5144323: Get aggregation period start and end
2502 OPEN get_agg_start(rec_asg.person_id, rec_asg.asg_end_date);
2503 FETCH get_agg_start INTO l_agg_start_date;
2504 CLOSE get_agg_start;
2505 --
2506 OPEN get_agg_end(rec_asg.person_id, rec_asg.asg_end_date);
2507 FETCH get_agg_end INTO l_agg_end_date;
2508 CLOSE get_agg_end;
2509 --
2510 hr_utility.trace('l_agg_start_date='||
2511 fnd_date.date_to_displaydate(l_agg_start_date)||
2512 ', l_agg_end_date='||
2513 fnd_date.date_to_displaydate(l_agg_end_date));
2514 --
2515 -- Loop through all OTHER assignments in this aggregation
2516 -- for further checks. Set a flag if rec_asg.assignment_id
2517 -- cannot be archived.
2518 --
2519 FOR rec_all_aggs in csr_person_agg_asg(rec_asg.person_id, g_tax_ref,
2520 rec_asg.assignment_id, rec_asg.period_of_service_id,
2521 rec_asg.asg_end_date, l_agg_start_date, l_agg_end_date) LOOP
2522 -- 5144323: keep list of agg assignments, this info shd be archived
2523 -- when P45 action is created
2524 l_inc_asg_index := l_inc_asg_index + 1;
2525 l_inc_asg_tab(l_inc_asg_index) := rec_all_aggs.assignment_id;
2526 --
2527 hr_utility.set_location(l_proc,10);
2528 IF rec_asg.asg_end_date < rec_all_aggs.effective_end_date THEN
2529 -- Asg exists that is not ended as of the effective end
2530 -- of the current assignment
2531 l_non_p45_message := 'Agg: asg exists not ended: '||
2532 to_char(rec_all_aggs.assignment_id);
2533 -- Exclude main assignment, exit loop (performance)
2534 l_archive := FALSE;
2535 EXIT;
2536 ELSE
2537 hr_utility.set_location(l_proc,20);
2538 IF rec_asg.asg_end_date = rec_all_aggs.effective_end_date
2539 AND rec_asg.assignment_id > rec_all_aggs.assignment_id THEN
2540 -- Other lower Asg ID exists, and ending on the same date.
2541 -- Exclude this assignment exit loop
2542 l_non_p45_message := 'Agg: Lower asg ID, same end date exists';
2543 l_archive := FALSE;
2544 EXIT;
2545 ELSE
2546 hr_utility.set_location(l_proc,30);
2547 l_check_main_flag := true;
2548 --
2549 END IF; -- lower asg id
2550 END IF; -- existing later assignments
2551 END LOOP; -- OTHER agg assignments loop
2552 -- fix for bug 5380921 --
2553 IF l_check_main_flag AND l_archive THEN
2554 IF NOT payment_made(rec_asg.assignment_id, rec_asg.asg_end_date,rec_asg.period_of_service_id) THEN
2555 l_override_date := override_date(rec_asg.assignment_id);
2556 IF l_override_date IS NULL THEN
2557 l_non_p45_message := 'No Final Payment or Override';
2558 l_archive := FALSE;
2559 ELSIF l_override_date > g_effective_date THEN
2560 l_non_p45_message := 'Override date (' || to_char(l_override_date) ||
2561 ') greater than issue date';
2562 l_archive := FALSE;
2563 END IF; -- Override date check
2564 END IF; -- Payment Made check
2565 END IF; -- archive flag check
2566 -- end bug fix for 5380921 --
2567 END IF; -- for current asg leaver check.
2568 ELSE -- Non Aggregated PAYE
2569 hr_utility.set_location(l_proc,35);
2570 IF return_P45_issued_flag(rec_asg.assignment_id) = 'Y' THEN
2571 hr_utility.set_location(l_proc,40);
2572 l_non_p45_message := 'Leaver action exists';
2573 l_archive := FALSE;
2574 ELSE
2575 hr_utility.set_location(l_proc,50);
2576 IF payment_made(rec_asg.assignment_id, rec_asg.asg_end_date,
2577 rec_asg.period_of_service_id) THEN
2578 -- Final Payment made.
2579 hr_utility.set_location(l_proc,60);
2580 l_archive := TRUE;
2581 ELSE
2582 -- Has there been an override that is before the p45
2583 -- run date but after the eff end of the asg.
2584 l_override_date := override_date(rec_asg.assignment_id);
2585 IF l_override_date IS NULL THEN
2586 l_non_p45_message := 'No Final Payment or Override';
2587 l_archive := FALSE;
2588 ELSIF l_override_date <= g_effective_date AND
2589 l_override_date >= rec_asg.asg_end_date THEN
2590 l_archive := TRUE;
2591 hr_utility.set_location(l_proc,65);
2592 ELSE
2593 l_non_p45_message := to_char(rec_asg.asg_end_date)||':'||
2594 to_char(l_override_date)||':'||
2595 to_char(g_effective_date);
2596 l_archive := FALSE;
2597 END IF; -- override date
2598 END IF; -- final payment
2599 END IF; -- P45 check
2600 END IF; -- aggregated paye
2601 END IF; -- future assignment
2602 --
2603 ELSE
2604 ---------------------------------------
2605 -- Tax Reference transfer
2606 ---------------------------------------
2607 hr_utility.set_location(l_proc,70);
2608 l_transfer_flag := 'Y';
2609 if p45_existing_action(p_assignment_id => rec_asg.assignment_id,
2610 p_period_of_service_id => rec_asg.period_of_service_id,
2611 p_mode => 'TRANSFER')
2612 then
2613 l_non_p45_message := 'Transfer action exists';
2614 l_archive := FALSE;
2615 else
2616 IF rec_asg.agg_paye_flag = 'Y' then
2617 hr_utility.set_location(l_proc,80);
2618 for rec_all_aggs in csr_person_agg_asg(rec_asg.person_id, g_tax_ref,
2619 rec_asg.assignment_id, rec_asg.period_of_service_id,
2620 rec_asg.asg_end_date, hr_general.start_of_time,
2621 hr_general.end_of_time) loop
2622 if rec_asg.asg_end_date < rec_all_aggs.effective_end_date then
2623 -- Asg exists that is not ended as of the effective end
2624 -- of the current assignment, and is ACTIVE
2625 l_non_p45_message := 'TFR: Agg: asg exists not ended: '||
2626 to_char(rec_all_aggs.assignment_id);
2627 -- Exclude main assignment, exit loop (performance)
2628 l_archive := FALSE;
2629 exit;
2630 else
2631 hr_utility.set_location(l_proc,90);
2632 if rec_asg.asg_end_date = rec_all_aggs.effective_end_date
2633 and rec_asg.assignment_id > rec_all_aggs.assignment_id
2634 then
2635 -- Other lower Asg ID exists, and ending on the same date.
2636 -- Exclude this assignment exit loop
2637 l_non_p45_message :=
2638 'TFR: Agg: Lower asg ID, same end date exists';
2639 l_archive := FALSE;
2640 exit;
2641 else
2642 -- No reason to exclude this asg.
2643 hr_utility.set_location(l_proc,95);
2644 end if; -- Lower asg id
2645 end if; -- future active for this agg
2646 end loop; -- Aggregated loop
2647 ELSE
2648 -- Not aggregated and no existing action, so archive.
2649 hr_utility.set_location(l_proc,105);
2650 l_archive := TRUE;
2651 END IF; -- Agg asg
2652 end if; -- existing tfr action
2653 END IF; -- TRANSFER
2654 --------------------------------------------
2655 -- Archive the assignment if not excluded
2656 --------------------------------------------
2657 if l_archive = FALSE then
2658 hr_utility.trace('No P45 for '||to_char(rec_asg.assignment_id)||
2659 '. Reason:');
2660 hr_utility.trace(l_non_p45_message);
2661 fnd_file.put_line(fnd_file.log,
2662 rec_asg.assignment_number||': '|| l_non_p45_message);
2663 l_non_p45_message := null;
2664 else
2665 hr_utility.set_location(l_proc,107);
2666 -- Final check for all, has latest asg action got NI Tax code
2667 -- NB Placing this check here at the end for performance reasons.
2668 -- Using regular payment date for final payment date.
2669 if NOT (tax_code_ni(rec_asg.assignment_id,rec_asg.regular_payment_date))
2670 then
2671 hr_utility.trace('ARCHIVING FOR '||to_char(rec_asg.assignment_id));
2672 SELECT pay_assignment_actions_s.nextval
2673 INTO l_actid
2674 FROM dual;
2675 --
2676 hr_utility.set_location(l_proc,110);
2677 hr_nonrun_asact.insact(l_actid,rec_asg.assignment_id,
2678 pactid,chunk,NULL);
2679 --
2680 hr_utility.set_location(l_proc,120);
2681 -- Archive the tax ref transfer flag and the Asg end date.
2682 --
2683 ff_archive_api.create_archive_item
2684 (p_archive_item_id => l_archive_item_id,
2685 p_user_entity_id => g_tax_ref_transfer_eid,
2686 p_archive_value => l_transfer_flag,
2687 p_archive_type => 'AAC',
2688 p_action_id => l_actid,
2689 p_legislation_code => 'GB',
2690 p_object_version_number => l_ovn,
2691 p_some_warning => l_some_warning);
2692 --
2693 ff_archive_api.create_archive_item
2694 (p_archive_item_id => l_archive_item_id,
2695 p_user_entity_id => g_termination_date_eid,
2696 p_archive_value => fnd_date.date_to_canonical(rec_asg.asg_end_date),
2697 p_archive_type => 'AAC',
2698 p_action_id => l_actid,
2699 p_legislation_code => 'GB',
2700 p_object_version_number => l_ovn,
2701 p_some_warning => l_some_warning);
2702 --
2703 FOR l_count IN 1..l_inc_asg_index LOOP
2704 hr_utility.set_location(l_proc,125);
2705 hr_utility.trace('Including asg id='||l_inc_asg_tab(l_count));
2706 ff_archive_api.create_archive_item
2707 (p_archive_item_id => l_archive_item_id,
2708 p_user_entity_id => g_p45_inc_assignment,
2709 p_archive_value => l_inc_asg_tab(l_count),
2710 p_archive_type => 'AAC',
2711 p_action_id => l_actid,
2712 p_legislation_code => 'GB',
2713 p_object_version_number => l_ovn,
2714 p_some_warning => l_some_warning);
2715 END LOOP;
2716 else
2717 hr_utility.set_location(l_proc,130);
2718 hr_utility.trace('No P45 for '||to_char(rec_asg.assignment_id)||
2719 '. Tax Code = NI');
2720 fnd_file.put_line(fnd_file.log,
2721 rec_asg.assignment_number||': Tax Code = NI');
2722 end if; -- NI Tax Code
2723 end if; -- archive FALSE
2724 END LOOP; -- csr_all_assignments
2725 IF l_range_person_on = 'YES' then
2726 if csr_all_assignments_range%isopen then
2727 close csr_all_assignments_range;
2728 end if;
2729 ELSE
2730 if csr_all_assignments%isopen then
2731 close csr_all_assignments;
2732 end if;
2733 END IF;
2734 --
2735 hr_utility.set_location(' Leaving: '||l_proc,999);
2736
2737 END arch_act_creation;
2738 ---------------------------------------------------------------------------
2739 PROCEDURE populate_run_msg(
2740 p45_assignment_action_id IN NUMBER
2741 ,p_message_text IN varchar2
2742 )
2743 IS
2744 PRAGMA AUTONOMOUS_TRANSACTION;
2745 BEGIN
2746 hr_utility.set_location(' Entering: populate_run_msg',111);
2747
2748 INSERT INTO pay_message_lines(line_sequence,
2749 payroll_id,
2750 message_level,
2751 source_id,
2752 source_type,
2753 line_text)
2754 VALUES(
2755 pay_message_lines_s.nextval
2756 ,null
2757 ,'F'
2758 ,p45_assignment_action_id
2759 ,'A'
2760 ,substr(p_message_text,1,240)
2761 );
2762
2763 hr_utility.set_location(' Leaving: populate_run_msg',999);
2764 COMMIT;
2765 EXCEPTION
2766 WHEN OTHERS THEN
2767 hr_utility.trace('Error occured in populate_run_msg');
2768 RAISE;
2769 END populate_run_msg;
2770 ---------------------------------------------------------------------------
2771 PROCEDURE EDI_MOVDED6_ASG ( address_line1 in varchar2,
2772 address_line2 in varchar2,
2773 address_line3 in varchar2,
2774 assignment_number in varchar2,
2775 county in varchar2,
2776 first_name in varchar2,
2777 middle_name in varchar2,
2778 last_name in varchar2,
2779 national_insurance_number in varchar2,
2780 postal_code in varchar2,
2781 title in varchar2,
2782 town_or_city in varchar2,
2783 effective_date in varchar2,
2784 p_assactid in number,
2785 edi_validation_fail out nocopy varchar2) IS
2786
2787 l_addline1 per_addresses.address_line1%TYPE := address_line1;
2788 l_addline2 per_addresses.address_line2%TYPE:= address_line2;
2789 l_addline3 per_addresses.address_line3%TYPE:=address_line3 ;
2790 l_assignment_number per_assignments_f.assignment_number%TYPE:=assignment_number ;
2791 l_county per_addresses.region_1%type:= county;
2792 l_first_name per_people_f.first_name%TYPE:= first_name;
2793 l_middle_name per_people_f.middle_names%TYPE:= middle_name;
2794 l_last_name per_people_f.last_name%TYPE:= last_name;
2795 l_ni_number per_people_f.national_identifier%TYPE:= national_insurance_number;
2796 l_postal_code per_addresses.postal_code%TYPE:= postal_code;
2797 l_title per_people_f.title%TYPE:= title ;
2798 l_addline4 per_addresses.town_or_city%TYPE := town_or_city; --
2799 l_effective_date date := effective_date;
2800
2801 BEGIN
2802
2803 if l_addline3 IS NULL then
2804 l_addline3 := l_addline4;
2805 l_addline4 := NULL;
2806 end if;
2807 if l_addline2 IS NULL then
2808 l_addline2 := l_addline3;
2809 l_addline3 := l_addline4;
2810 l_addline4 := NULL;
2811 end if;
2812 if LENGTH(TRIM(l_addline4)) > 0 then
2813 l_addline4 := l_addline4;
2814 else
2815 l_addline4 := NULL;
2816 end if;
2817
2818 if LENGTH(TRIM(l_addline3)) > 0 then
2819 l_addline3 := l_addline3;
2820 else
2821 l_addline3 := l_addline4;
2822 l_addline4 := NULL;
2823 end if;
2824 if LENGTH(TRIM(l_addline2)) > 0 then
2825 l_addline2 := l_addline2;
2826 else
2827 l_addline2 := l_addline3;
2828 l_addline3 := l_addline4;
2829 l_addline4 := NULL;
2830 end if;
2831
2832 IF l_ni_number IS NOT NULL and hr_gb_utility.ni_validate(l_ni_number,l_effective_date) <> 0 THEN
2833 populate_run_msg(p_assactid,'The National Insurance Number of the assignment has invalid character(s)');
2834 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The National Insurance Number of the assignment has invalid character(s)');
2835 edi_validation_fail := 'Y';
2836 END IF;
2837
2838 IF l_addline1 IS NULL THEN
2839 populate_run_msg(p_assactid,' The Address Line 1 of the assignment is missing.');
2840 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Address Line 1 of the assignment is missing.');
2841 edi_validation_fail := 'Y';
2842 ELSIF pay_gb_eoy_magtape.validate_input(l_addline1, 'P14_FULL_EDI') > 0 THEN
2843 populate_run_msg(p_assactid,'The Address Line 1 of the assignment has invalid character(s).');
2844 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Address Line 1 of the assignment has invalid character(s).');
2845 edi_validation_fail := 'Y';
2846 END IF;
2847
2848 IF l_addline2 IS NULL THEN
2849 populate_run_msg(p_assactid,'The Address Line 2 of the assignment is missing.');
2850 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Address Line 2 of the assignment is missing.');
2851 edi_validation_fail := 'Y';
2852 ELSIF pay_gb_eoy_magtape.validate_input(l_addline2, 'P14_FULL_EDI') > 0 THEN
2853 populate_run_msg(p_assactid,'The Address Line 2 of the assignment has invalid character(s).');
2854 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Address Line 2 of the assignment has invalid character(s).');
2855 edi_validation_fail := 'Y';
2856 END IF;
2857
2858 IF l_addline3 IS NOT NULL AND pay_gb_eoy_magtape.validate_input(l_addline3, 'P14_FULL_EDI') > 0 THEN
2859 populate_run_msg(p_assactid,'The Address Line 3 of the assignment has invalid character(s).');
2860 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Address Line 3 of the assignment has invalid character(s).');
2861 edi_validation_fail := 'Y';
2862 END IF;
2863
2864 IF town_or_city IS NOT NULL AND pay_gb_eoy_magtape.validate_input(town_or_city, 'P14_FULL_EDI') > 0 THEN
2865 populate_run_msg(p_assactid,'The Town or City of the assignment has invalid character(s).');
2866 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Town or City of the assignment has invalid character(s).');
2867 edi_validation_fail := 'Y';
2868 END IF;
2869
2870 IF assignment_number IS NOT NULL AND pay_gb_eoy_magtape.validate_input(assignment_number, 'P14_FULL_EDI') > 0 THEN
2871 populate_run_msg(p_assactid,'Assignment number has invalid character(s).');
2872 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : Assignment number has invalid character(s).');
2873 edi_validation_fail := 'Y';
2874 END IF;
2875
2876 IF l_county IS NOT NULL AND pay_gb_eoy_magtape.validate_input(l_county, 'P14_FULL_EDI') > 0 THEN
2877 populate_run_msg(p_assactid,'The County of the assignment has invalid character(s).');
2878 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The County of the assignment has invalid character(s).');
2879 edi_validation_fail := 'Y';
2880 END IF;
2881
2882 IF l_last_name IS NULL THEN
2883 populate_run_msg(p_assactid,'The Last Name of the assignment is missing.');
2884 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Last Name of the assignment is missing');
2885 edi_validation_fail := 'Y';
2886 ELSIF pay_gb_eoy_magtape.validate_input(l_last_name, 'P45_46_LAST_NAME') > 0 THEN
2887 populate_run_msg(p_assactid,'The Last Name of the assignment has invalid character(s).');
2888 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Last Name of the assignment has invalid character(s).');
2889 edi_validation_fail := 'Y';
2890 END IF;
2891
2892 IF l_first_name IS NULL THEN
2893 populate_run_msg(p_assactid,' The First Name of the assignment is missing');
2894 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The First Name of the assignment is missing');
2895 edi_validation_fail := 'Y';
2896 ELSIF pay_gb_eoy_magtape.validate_input(l_first_name, 'P45_46_FIRST_NAME') > 0 THEN
2897 populate_run_msg(p_assactid,'The First Name of the assignment has invalid character(s).');
2898 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The First Name of the assignment has invalid character(s).');
2899 edi_validation_fail := 'Y';
2900 END IF;
2901
2902 IF l_middle_name IS NOT NULL and pay_gb_eoy_magtape.validate_input(l_middle_name, 'P45_46_FIRST_NAME') > 0 THEN
2903 populate_run_msg(p_assactid,'The Middle Name of the assignment has invalid character(s).');
2904 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Middle Name of the assignment has invalid character(s).');
2905 edi_validation_fail := 'Y';
2906 END IF;
2907
2908 IF l_title IS NOT NULL and pay_gb_eoy_magtape.validate_input(l_title, 'P45_46_TITLE') > 0 THEN
2909 populate_run_msg(p_assactid,'The Title of the assignment has invalid character(s).');
2910 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Title of the assignment has invalid character(s).');
2911 edi_validation_fail := 'Y';
2912 END IF;
2913
2914 IF l_postal_code IS NOT NULL and pay_gb_eoy_magtape.validate_input(l_postal_code, 'P45_46_POSTCODE') > 0 THEN
2915 populate_run_msg(p_assactid,'The Postal Code of the assignment has invalid character(s).');
2916 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Postal Code of the assignment has invalid character(s).');
2917 edi_validation_fail := 'Y';
2918 END IF;
2919
2920
2921 END EDI_MOVDED6_ASG;
2922
2923 ---------------------------------------------------------------------------
2924 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
2925 IS
2926 l_proc CONSTANT VARCHAR2(35):= g_package||'archinit';
2927 l_do_edi_validation VARCHAR2(6);
2928 l_effective_date DATE;
2929 l_edi_ver g_edi_ver%type;
2930 -- Bug 8815269
2931 l_ppa_payroll_id pay_payroll_actions.payroll_id%TYPE;
2932 l_payroll_id pay_payroll_actions.payroll_id%TYPE;
2933 -- Bug 8815269
2934
2935 --
2936 cursor csr_user_entity(p_entity_name VARCHAR2) IS
2937 SELECT user_entity_id
2938 FROM ff_user_entities
2939 WHERE user_entity_name = p_entity_name
2940 AND legislation_code = 'GB'
2941 AND business_group_id IS NULL;
2942 --
2943 cursor csr_parameter_info(p_payroll_action_id NUMBER) IS
2944 SELECT
2945 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2946 'PAYROLL_ID')) payroll_id,
2947 decode(pay_gb_eoy_archive.get_parameter(legislative_parameters,
2948 'CHAR_ERROR'),
2949 'Y','TRUE','N','FALSE') check_chars,
2950 effective_date
2951 FROM pay_payroll_actions
2952 WHERE payroll_action_id = p_payroll_action_id;
2953 --
2954 cursor csr_version_info(p_payroll_action_id NUMBER) IS
2955 SELECT pay_gb_eoy_archive.get_parameter(legislative_parameters,'EDI_VER') edi_ver
2956 FROM pay_payroll_actions
2957 WHERE payroll_action_id = p_payroll_action_id;
2958
2959 BEGIN
2960 hr_utility.set_location('Entering: '||l_proc,1);
2961 IF NOT g_asg_process_cache_populated THEN
2962 -- does session date need to be set?
2963 -- fnd_sessions used in cursor employer_addr (only when asg terminated
2964 -- with null last std proc date).
2965 -- Get required SRS Parameters
2966 OPEN csr_parameter_info(p_payroll_action_id);
2967 FETCH csr_parameter_info INTO g_payroll_id,
2968 l_do_edi_validation,
2969 l_effective_date;
2970 CLOSE csr_parameter_info;
2971 g_do_edi_validation := hr_general.char_to_bool(l_do_edi_validation);
2972 OPEN csr_version_info(p_payroll_action_id);
2973 FETCH csr_version_info INTO l_edi_ver;
2974 CLOSE csr_version_info;
2975 g_edi_ver := l_edi_ver;
2976 -- cache User entity Ids
2977 OPEN csr_user_entity('X_ADDRESS_LINE1');
2978 FETCH csr_user_entity INTO g_address_line1_eid;
2979 CLOSE csr_user_entity;
2980 OPEN csr_user_entity('X_ADDRESS_LINE2');
2981 FETCH csr_user_entity INTO g_address_line2_eid;
2982 CLOSE csr_user_entity;
2983 OPEN csr_user_entity('X_ADDRESS_LINE3');
2984 FETCH csr_user_entity INTO g_address_line3_eid;
2985 CLOSE csr_user_entity;
2986 OPEN csr_user_entity('X_ASSIGNMENT_NUMBER');
2987 FETCH csr_user_entity INTO g_assignment_number_eid;
2988 CLOSE csr_user_entity;
2989 OPEN csr_user_entity('X_COUNTY');
2990 FETCH csr_user_entity INTO g_county_eid;
2991 CLOSE csr_user_entity;
2992 OPEN csr_user_entity('X_DECEASED_FLAG');
2993 FETCH csr_user_entity INTO g_deceased_flag_eid;
2994 CLOSE csr_user_entity;
2995 OPEN csr_user_entity('X_FIRST_NAME');
2996 FETCH csr_user_entity INTO g_first_name_eid;
2997 CLOSE csr_user_entity;
2998 OPEN csr_user_entity('X_MIDDLE_NAME'); /*Bug 6710229*/
2999 FETCH csr_user_entity INTO g_middle_name_eid;
3000 CLOSE csr_user_entity;
3001 OPEN csr_user_entity('X_ISSUE_DATE');
3002 FETCH csr_user_entity INTO g_issue_date_eid;
3003 CLOSE csr_user_entity;
3004 OPEN csr_user_entity('X_LAST_NAME');
3005 FETCH csr_user_entity INTO g_last_name_eid;
3006 CLOSE csr_user_entity;
3007 OPEN csr_user_entity('X_MONTH_NUMBER');
3008 FETCH csr_user_entity INTO g_month_number_eid;
3009 CLOSE csr_user_entity;
3010 OPEN csr_user_entity('X_NATIONAL_INSURANCE_NUMBER');
3011 FETCH csr_user_entity INTO g_ni_number_eid;
3012 CLOSE csr_user_entity;
3013 OPEN csr_user_entity('X_ORGANIZATION_NAME');
3014 FETCH csr_user_entity INTO g_organization_name_eid;
3015 CLOSE csr_user_entity;
3016 OPEN csr_user_entity('X_PAYROLL_ID');
3017 FETCH csr_user_entity INTO g_payroll_id_eid;
3018 CLOSE csr_user_entity;
3019 OPEN csr_user_entity('X_POSTAL_CODE');
3020 FETCH csr_user_entity INTO g_postal_code_eid;
3021 CLOSE csr_user_entity;
3022 OPEN csr_user_entity('X_PREVIOUS_TAX_PAID');
3023 FETCH csr_user_entity INTO g_prev_tax_paid_eid;
3024 CLOSE csr_user_entity;
3025 OPEN csr_user_entity('X_PREVIOUS_TAXABLE_PAY');
3026 FETCH csr_user_entity INTO g_prev_taxable_pay_eid;
3027 CLOSE csr_user_entity;
3028 OPEN csr_user_entity('X_STUDENT_LOAN_FLAG');
3029 FETCH csr_user_entity INTO g_student_loan_flag_eid;
3030 CLOSE csr_user_entity;
3031 OPEN csr_user_entity('X_AGGREGATED_PAYE_FLAG');
3032 FETCH csr_user_entity INTO g_aggregated_paye_flag_eid;
3033 CLOSE csr_user_entity;
3034 OPEN csr_user_entity('X_PERIOD_OF_SERVICE_ID');
3035 FETCH csr_user_entity INTO g_period_of_service_eid;
3036 CLOSE csr_user_entity;
3037 OPEN csr_user_entity('X_EFFECTIVE_END_DATE');
3038 FETCH csr_user_entity INTO g_effective_end_date_eid;
3039 CLOSE csr_user_entity;
3040 OPEN csr_user_entity('X_TAX_CODE');
3041 FETCH csr_user_entity INTO g_tax_code_eid;
3042 CLOSE csr_user_entity;
3043 OPEN csr_user_entity('X_TAX_PAID');
3044 FETCH csr_user_entity INTO g_tax_paid_eid;
3045 CLOSE csr_user_entity;
3046 OPEN csr_user_entity('X_TAXABLE_PAY');
3047 FETCH csr_user_entity INTO g_taxable_pay_eid;
3048 CLOSE csr_user_entity;
3049 OPEN csr_user_entity('X_TAX_REF_TRANSFER');
3050 FETCH csr_user_entity INTO g_tax_ref_transfer_eid;
3051 CLOSE csr_user_entity;
3052 OPEN csr_user_entity('X_TERMINATION_DATE');
3053 FETCH csr_user_entity INTO g_termination_date_eid;
3054 CLOSE csr_user_entity;
3055 OPEN csr_user_entity('X_TITLE');
3056 FETCH csr_user_entity INTO g_title_eid;
3057 CLOSE csr_user_entity;
3058 OPEN csr_user_entity('X_DATE_OF_BIRTH');
3059 FETCH csr_user_entity INTO g_date_of_birth_eid;
3060 CLOSE csr_user_entity;
3061 OPEN csr_user_entity('X_SEX');
3062 FETCH csr_user_entity INTO g_sex_eid;
3063 CLOSE csr_user_entity;
3064 OPEN csr_user_entity('X_TOWN_OR_CITY');
3065 FETCH csr_user_entity INTO g_town_or_city_eid;
3066 CLOSE csr_user_entity;
3067 OPEN csr_user_entity('X_W1_M1_INDICATOR');
3068 FETCH csr_user_entity INTO g_w1_m1_indicator_eid;
3069 CLOSE csr_user_entity;
3070 OPEN csr_user_entity('X_WEEK_NUMBER');
3071 FETCH csr_user_entity INTO g_week_number_eid;
3072 CLOSE csr_user_entity;
3073 OPEN csr_user_entity('X_COUNTRY');
3074 FETCH csr_user_entity INTO g_country_eid;
3075 CLOSE csr_user_entity;
3076 OPEN csr_user_entity('X_P45_FINAL_PAYMENT_ACTION');
3077 FETCH csr_user_entity INTO g_p45_final_action;
3078 CLOSE csr_user_entity;
3079
3080 --
3081 -- Get IDs for seed data
3082 SELECT element_type_id
3083 INTO g_paye_details_id
3084 FROM pay_element_types_f
3085 WHERE element_name = 'PAYE Details'
3086 AND l_effective_date BETWEEN effective_start_date
3087 AND effective_end_date;
3088 --
3089 g_asg_process_cache_populated := true;
3090 END IF; -- NOT g_asg_process_cache_populated
3091
3092
3093 --------------------------------------------------------------------------
3094 -- Code to update the payroll_id in the pay_payroll_actions table. -- Bug 8815269
3095 --------------------------------------------------------------------------
3096
3097 SELECT payroll_id,pay_gb_eoy_archive.get_parameter(legislative_parameters,'PAYROLL_ID')
3098 INTO l_ppa_payroll_id,l_payroll_id
3099 FROM pay_payroll_actions
3100 WHERE payroll_action_id = p_payroll_action_id ;
3101
3102
3103 -- Update the Payroll Action with the Payroll ID
3104
3105 IF l_ppa_payroll_id IS NULL and l_payroll_id is not null THEN
3106 UPDATE pay_payroll_actions
3107 SET payroll_id = l_payroll_id
3108 WHERE payroll_action_id = p_payroll_action_id;
3109 END IF;
3110
3111 -- end Bug 8815269
3112
3113 hr_utility.set_location(' Leaving: '||l_proc,999);
3114 END archinit;
3115 --
3116 PROCEDURE archive_code(p_assactid IN NUMBER, p_effective_date IN DATE)
3117 IS
3118 --
3119 l_proc CONSTANT VARCHAR2(35):= g_package||'archive_code';
3120 -- vars for returns from the API:
3121 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
3122 l_ovn NUMBER;
3123 l_some_warning BOOLEAN;
3124 --
3125 l_assignment_id per_assignments_f.assignment_id%TYPE;
3126 l_termination_date DATE;
3127 l_tax_ref_transfer VARCHAR2(1);
3128 l_transfer_date DATE:=NULL;
3129 --
3130 l_assignment_number per_assignments_f.assignment_number%TYPE;
3131 l_person_id per_people_f.person_id%TYPE;
3132 l_asg_effective_end_date DATE;
3133 l_deceased_flag VARCHAR2(1);
3134 l_agg_paye_flag VARCHAR2(1);
3135 l_period_of_service_id NUMBER;
3136 l_org_name HR_ALL_ORGANIZATION_units.name%TYPE;
3137 l_last_name per_people_f.last_name%TYPE;
3138 l_first_name per_people_f.first_name%TYPE;
3139 l_middle_name per_people_f.middle_names%TYPE; /*Bug 6710229*/
3140 l_title per_people_f.title%TYPE;
3141 l_date_of_birth per_people_f.date_of_birth%TYPE;
3142 l_sex per_people_f.sex%TYPE;
3143 l_ni_number per_people_f.national_identifier%TYPE;
3144 l_payroll_id per_assignments_f.payroll_id%TYPE;
3145 --
3146 l_address_line1 per_addresses.address_line1%TYPE;
3147 l_address_line2 per_addresses.address_line2%TYPE;
3148 l_address_line3 per_addresses.address_line3%TYPE;
3149 l_town_or_city per_addresses.town_or_city%TYPE;
3150 l_county per_addresses.region_1%TYPE;
3151 l_postal_code per_addresses.postal_code%TYPE;
3152 l_country per_addresses.country%TYPE;
3153 l_last_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
3154 l_date_earned DATE;
3155 l_eff_date DATE;
3156 l_last_pay_action_id pay_assignment_actions.payroll_action_id%TYPE;
3157 --
3158 l_student_loan_flag VARCHAR2(1);
3159 --
3160 l_period_no per_time_periods.period_num%TYPE;
3161 l_tax_reference VARCHAR2(20);
3162 l_tax_code pay_element_entry_values_f.screen_entry_value%TYPE;
3163 l_tax_basis pay_element_entry_values_f.screen_entry_value%TYPE;
3164 l_prev_pay_char pay_element_entry_values_f.screen_entry_value%TYPE;
3165 l_prev_tax_char pay_element_entry_values_f.screen_entry_value%TYPE;
3166 l_tax_code_t pay_element_entry_values_f.screen_entry_value%TYPE;
3167 l_tax_basis_t pay_element_entry_values_f.screen_entry_value%TYPE;
3168 l_prev_pay_char_t pay_element_entry_values_f.screen_entry_value%TYPE;
3169 l_prev_tax_char_t pay_element_entry_values_f.screen_entry_value%TYPE;
3170 --
3171 l_taxable NUMBER;
3172 l_paye NUMBER;
3173 --
3174 l_week_or_month VARCHAR2(1);
3175 --
3176 l_paye_element_id number;
3177 l_tax_code_ipv_id number;
3178 l_tax_basis_ipv_id number;
3179 l_pay_previous_ipv_id number;
3180 l_tax_previous_ipv_id number;
3181 l_max_run_result_id number;
3182 l_lsp_date date;
3183 l_final_process_date date;
3184 ---
3185 l_pay_in_this_emp varchar2(100);
3186 l_tax_in_this_emp varchar2(100);
3187 less_than_zero_flag varchar2(100);
3188 l_total_pay_to_date varchar2(100);
3189 l_total_tax_to_date varchar2(100);
3190 l_edi_validation_fail varchar2(1);
3191 l_edi_movded6_asg_flag varchar2(1);
3192 ---
3193
3194 --
3195 cursor csr_asg_act_info(p_asgactid NUMBER) IS
3196 SELECT act.assignment_id,
3197 fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str
3198 (act.assignment_action_id,
3199 g_termination_date_eid)) term_date,
3200 pay_gb_eoy_archive.get_arch_str(act.assignment_action_id,
3201 g_tax_ref_transfer_eid) tax_ref_transfer
3202 FROM pay_assignment_actions act
3203 WHERE act.assignment_action_id = p_asgactid;
3204 --
3205 cursor csr_basic_asg_info (p_assid NUMBER, p_term_date DATE) IS
3206 SELECT ass.assignment_number,
3207 ass.person_id,
3208 ass.effective_end_date asg_effective_end_date,
3209 serv.last_standard_process_date,
3210 nvl(serv.final_process_date, hr_general.end_of_time), -- For bug 9071978
3211 ass.period_of_service_id,
3212 decode(serv.leaving_reason,'D','D') deceased_flag,
3213 org.name org_name,
3214 upper(p.last_name) , p.title ,
3215 --SUBSTR(upper(p.first_name || ' ' || p.middle_names),1,150),
3216 --SUBSTR(upper(p.first_name || ',' || p.middle_names),1,150),--replaces space with a "comma" for the P45 EOY changes
3217 upper(p.first_name), upper(p.middle_names), /*Bug 6710229*/
3218 p.national_identifier, ass.payroll_id,
3219 decode(p.per_information10,'Y','Y',NULL) agg_paye_flag,
3220 p.date_of_birth, p.sex
3221 FROM
3222 per_all_people_f p,
3223 hr_all_organization_units org,
3224 per_periods_of_service serv,
3225 per_all_assignments_f ass
3226 WHERE ass.assignment_id = p_assid
3227 AND serv.period_of_service_id = ass.period_of_service_id
3228 AND ass.effective_end_date = p_term_date
3229 AND ass.organization_id = org.organization_id
3230 AND ass.person_id = p.person_id
3231 AND ass.effective_end_date BETWEEN p.effective_start_date
3232 AND p.effective_end_date;
3233 --
3234 cursor csr_week_or_month(p_payroll_action_id NUMBER) IS
3235 SELECT decode(target.basic_period_type, 'W', 'W', 'M')
3236 FROM per_time_period_rules target ,
3237 per_time_period_types ptpt ,
3238 pay_payrolls_f roll,
3239 pay_payroll_actions pact
3240 WHERE ptpt.period_type = roll.period_type
3241 AND target.number_per_fiscal_year = ptpt.number_per_fiscal_year
3242 AND roll.payroll_id = pact.payroll_id
3243 AND pact.effective_date BETWEEN roll.effective_start_date
3244 AND roll.effective_end_date
3245 AND pact.payroll_action_id = p_payroll_action_id;
3246 --
3247 -- Bug 8464343 : fetch week month type by payroll_id
3248 cursor week_month_by_paroll_id (p_payroll_id NUMBER , p_effective_date date) IS
3249 SELECT decode(target.basic_period_type, 'W', 'W', 'M')
3250 FROM per_time_period_rules target ,
3251 per_time_period_types ptpt ,
3252 pay_payrolls_f roll
3253 WHERE ptpt.period_type = roll.period_type
3254 AND target.number_per_fiscal_year = ptpt.number_per_fiscal_year
3255 AND roll.payroll_id = p_payroll_id
3256 AND p_effective_date BETWEEN roll.effective_start_date
3257 AND roll.effective_end_date ;
3258 -- Bug 5478073: Get period number in which assignment was terminated
3259 CURSOR csr_get_term_period_no(p_term_date DATE, p_payroll_id NUMBER) IS
3260 --Bug 7281023: Changed cursor logic
3261 /*SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
3262 FROM per_time_periods ptp
3263 WHERE ptp.payroll_id = p_payroll_id
3264 AND p_term_date BETWEEN ptp.start_date AND ptp.end_date;
3265 --*/
3266 --will fetch the period num of the first row ordered by regular_payment_date in ascending order
3267 SELECT distinct first_value(period_num) Over(order by regular_payment_date)
3268 FROM per_time_periods
3269 Where payroll_id = p_payroll_id
3270 AND regular_payment_date >= p_term_date;
3271
3272 --
3273 cursor csr_period_number(p_payroll_action_id NUMBER) IS
3274 SELECT nvl(max(ptp.period_num),0) -- Max and nvl are added to return 0 if period not found
3275 FROM per_time_periods ptp,
3276 pay_payroll_actions pact
3277 WHERE pact.payroll_action_id = p_payroll_action_id
3278 AND ptp.payroll_id = pact.payroll_id
3279 AND pact.date_earned BETWEEN ptp.start_date AND ptp.end_date;
3280 --
3281 CURSOR csr_paye_element IS
3282 SELECT element_type_id
3283 FROM pay_element_types_f
3284 WHERE element_name = 'PAYE';
3285 --
3286 CURSOR csr_input_value(p_ipv_name IN VARCHAR2) IS
3287 SELECT input_value_id
3288 FROM pay_input_values_f
3289 WHERE element_type_id = l_paye_element_id
3290 AND name = p_ipv_name;
3291 --
3292 CURSOR csr_result_value(p_ipv_id IN NUMBER) IS
3293 SELECT result_value
3294 FROM pay_run_result_values
3295 WHERE run_result_id = l_max_run_result_id
3296 AND input_value_id = p_ipv_id;
3297 --
3298 CURSOR csr_max_run_result IS
3299 SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
3300 pact PAY_PAYROLL_ACTIONS_PK,
3301 r2 PAY_RUN_RESULTS_N50)
3302 USE_NL(assact2, pact, r2) */
3303 to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
3304 r2.run_result_id),17))
3305 FROM pay_assignment_actions assact2,
3306 pay_payroll_actions pact,
3307 pay_run_results r2
3308 WHERE assact2.assignment_id = l_assignment_id
3309 AND r2.element_type_id+0 = l_paye_element_id
3310 AND r2.assignment_action_id = assact2.assignment_action_id
3311 AND r2.status IN ('P', 'PA')
3312 AND pact.payroll_action_id = assact2.payroll_action_id
3313 AND pact.action_type IN ( 'Q','R','B','I')
3314 AND assact2.action_status = 'C'
3315 AND pact.effective_date between
3316 to_date('06-04-'||to_char(fnd_number.canonical_to_number(to_char(l_date_earned,'YYYY'))),'DD-MM-YYYY')
3317 and to_date('05-04-'||to_char(fnd_number.canonical_to_number(to_char(l_date_earned,'YYYY') + 1)),'DD-MM-YYYY')
3318 AND NOT EXISTS(
3319 SELECT '1'
3320 FROM pay_action_interlocks pai,
3321 pay_assignment_actions assact3,
3322 pay_payroll_actions pact3
3323 WHERE pai.locked_action_id = assact2.assignment_action_id
3324 AND pai.locking_action_id = assact3.assignment_action_id
3325 AND pact3.payroll_action_id = assact3.payroll_action_id
3326 AND pact3.action_type = 'V'
3327 AND assact3.action_status = 'C');
3328 --
3329 cursor csr_paye_details(p_assignment_id NUMBER,
3330 p_effective_date DATE) IS
3331 SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
3332 max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
3333 max(decode(iv.name,'Pay Previous',screen_entry_value))
3334 pay_previous,
3335 max(decode(iv.name,'Tax Previous',screen_entry_value))
3336 tax_previous
3337 FROM pay_element_entries_f e,
3338 pay_element_entry_values_f v,
3339 pay_input_values_f iv,
3340 pay_element_links_f link
3341 WHERE e.assignment_id = p_assignment_id
3342 AND link.element_type_id = g_paye_details_id
3343 AND e.element_link_id = link.element_link_id
3344 AND e.element_entry_id = v.element_entry_id
3345 AND iv.input_value_id = v.input_value_id
3346 AND p_effective_date
3347 BETWEEN link.effective_start_date AND link.effective_end_date
3348 AND p_effective_date
3349 BETWEEN e.effective_start_date AND e.effective_end_date
3350 AND p_effective_date
3351 BETWEEN iv.effective_start_date AND iv.effective_end_date
3352 AND p_effective_date
3353 BETWEEN v.effective_start_date AND v.effective_end_date;
3354 --
3355 PROCEDURE archive_asg_info(p_user_entity_id NUMBER,
3356 p_value VARCHAR2) IS
3357 l_proc CONSTANT VARCHAR2(40):= g_package||'archive_asg_info';
3358 BEGIN
3359 IF p_value IS NOT NULL THEN
3360 hr_utility.set_location(l_proc||' '||p_user_entity_id,10);
3361 ff_archive_api.create_archive_item
3362 (p_archive_item_id => l_archive_item_id,
3363 p_user_entity_id => p_user_entity_id,
3364 p_archive_value => p_value,
3365 p_action_id => p_assactid,
3366 p_legislation_code => 'GB',
3367 p_object_version_number => l_ovn,
3368 p_some_warning => l_some_warning);
3369 END IF;
3370 END archive_asg_info;
3371 --
3372 BEGIN
3373 --hr_utility.trace_on(null,'KT');
3374 hr_utility.set_location('Entering: '||l_proc,1);
3375 hr_utility.trace('Assact ID : ' || p_assactid);
3376 -- Get the AAC level info.
3377 OPEN csr_asg_act_info(p_assactid);
3378 FETCH csr_asg_act_info INTO l_assignment_id,
3379 l_termination_date,
3380 l_tax_ref_transfer;
3381 CLOSE csr_asg_act_info;
3382 --
3383 OPEN csr_basic_asg_info(l_assignment_id,l_termination_date);
3384 FETCH csr_basic_asg_info INTO l_assignment_number,
3385 l_person_id,
3386 l_asg_effective_end_date, l_lsp_date,
3387 l_final_process_date,
3388 l_period_of_service_id,
3389 l_deceased_flag,
3390 l_org_name,
3391 l_last_name,
3392 l_title,
3393 l_first_name,
3394 l_middle_name, /*Bug 6710229*/
3395 l_ni_number,
3396 l_payroll_id,
3397 l_agg_paye_flag,
3398 l_date_of_birth,
3399 l_sex;
3400 CLOSE csr_basic_asg_info;
3401 --
3402 hr_utility.trace('FP Date: ' ||to_char(l_final_process_date));
3403 hr_utility.trace('Term Date: ' || to_char(l_termination_date));
3404 hr_utility.trace('LSP Date: ' || to_char(l_lsp_date));
3405 -- transfer date used in selection of last assignment action
3406 IF l_tax_ref_transfer = 'Y' then
3407 l_transfer_date := l_termination_date;
3408 ELSE
3409 -- 5144323: for transfer cases actions from old PAYE Ref (before transfer date)
3410 -- need to be fetched but assignment/employee termination cases there
3411 -- should be no time limit hence transfer date is set to end of time
3412 l_transfer_date := hr_general.end_of_time;
3413 END IF;
3414 --
3415 hr_utility.trace(l_last_name||' '||to_char(l_asg_effective_end_date)||
3416 to_char(l_assignment_id)||' '||to_char(l_termination_date)||
3417 ' '||l_tax_ref_transfer||' '||to_char(p_effective_date)||
3418 ' '||to_char(l_transfer_date));
3419 hr_utility.trace('--------------------------------------');
3420 IF g_do_edi_validation or g_edi_ver = 'V6' THEN
3421 --Already handled in EDI_MOVDED6_ASG procedure. we dont want to error the process
3422 --immediately once we hit an error bug 8254291
3423 /* IF pay_gb_eoy_magtape.validate_input(upper(l_assignment_number),
3424 'FULL_EDI') > 0 THEN
3425 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3426 hr_utility.set_message_token('ITEM_NAME', 'Assignment Number');
3427 hr_utility.raise_error;
3428 END IF;
3429
3430 IF pay_gb_eoy_magtape.validate_input(l_last_name,
3431 'EDI_SURNAME') > 0 THEN
3432 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3433 hr_utility.set_message_token('ITEM_NAME', 'Surname');
3434 hr_utility.raise_error;
3435 END IF;
3436
3437 IF pay_gb_eoy_magtape.validate_input(l_first_name,
3438 'EDI_SURNAME') > 0 THEN
3439 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3440 hr_utility.set_message_token('ITEM_NAME', 'Forename');
3441 hr_utility.raise_error;
3442 END IF;
3443
3444 IF pay_gb_eoy_magtape.validate_input(l_ni_number,
3445 'FULL_EDI') > 0 THEN
3446 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3447 hr_utility.set_message_token('ITEM_NAME', 'NI Number');
3448 hr_utility.raise_error;
3449 END IF;*/
3450
3451 IF g_edi_ver = 'V6' and l_date_of_birth IS NULL THEN -- V6 Validation
3452 populate_run_msg(p_assactid,'Date Of Birth for this assignment is missing.');
3453 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : Date Of Birth for this assignment is missing');
3454 l_edi_validation_fail := 'Y';
3455 ELSIF l_date_of_birth IS NOT NULL and PAY_GB_MOVDED_EDI.date_validate(p_assactid,'DOB',l_date_of_birth) = 0 THEN
3456 populate_run_msg(p_assactid,'The Date of Birth for this assignment must be the current date or an earlier date.');
3457 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Date of Birth for this assignment must be the current date or an earlier date.');
3458 l_edi_validation_fail := 'Y';
3459 END IF;
3460
3461 IF g_edi_ver = 'V6' and l_sex IS NULL THEN -- V6 Validation
3462 populate_run_msg(p_assactid,'The Gender for this assignment is missing.');
3463 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Gender for this assignment is missing');
3464 l_edi_validation_fail := 'Y';
3465 END IF;
3466
3467 IF l_sex not in ('M','F') THEN
3468 populate_run_msg(p_assactid,'The Gender of this assignment has invalid character(s)');
3469 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Gender of this assignment has invalid character(s)');
3470 l_edi_validation_fail := 'Y';
3471 END IF;
3472
3473 IF l_termination_date IS NULL THEN
3474 populate_run_msg(p_assactid,'The Date of Leaving of the assignment is missing.');
3475 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Date of Leaving of the assignment is missing');
3476 l_edi_validation_fail := 'Y';
3477 ELSIF PAY_GB_MOVDED_EDI.date_validate(p_assactid,'LEFT_DATE_V6',l_termination_date) = 0 THEN
3478 populate_run_msg(p_assactid,'The Date of Leaving is invalid.');
3479 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Date of Leaving is invalid.');
3480 l_edi_validation_fail := 'Y';
3481 END IF;
3482
3483
3484 END IF;
3485 --
3486 hr_utility.set_location(l_proc,10);
3487 -- Archive info obtained so far
3488 archive_asg_info(g_assignment_number_eid, l_assignment_number);
3489 archive_asg_info(g_deceased_flag_eid, l_deceased_flag);
3490 archive_asg_info(g_aggregated_paye_flag_eid, l_agg_paye_flag);
3491 archive_asg_info(g_period_of_service_eid, l_period_of_service_id);
3492 archive_asg_info(g_effective_end_date_eid, fnd_date.date_to_canonical(l_asg_effective_end_date));
3493 archive_asg_info(g_organization_name_eid, l_org_name);
3494 archive_asg_info(g_last_name_eid, l_last_name);
3495 archive_asg_info(g_title_eid, l_title);
3496 archive_asg_info(g_first_name_eid, l_first_name);
3497 archive_asg_info(g_middle_name_eid, l_middle_name); /*Bug 6710229*/
3498 archive_asg_info(g_ni_number_eid, l_ni_number);
3499 archive_asg_info(g_date_of_birth_eid, fnd_date.date_to_canonical(l_date_of_birth));
3500 archive_asg_info(g_sex_eid, l_sex);
3501 IF g_payroll_id IS NULL THEN
3502 -- archive not restricted by payroll so stamp asg with payroll id.
3503 archive_asg_info(g_payroll_id_eid,
3504 fnd_number.number_to_canonical(l_payroll_id));
3505 END IF;
3506 archive_asg_info(g_issue_date_eid, nvl(fnd_date.date_to_canonical(p_effective_date), fnd_date.date_to_canonical(sysdate)));
3507 --
3508 PAY_P45_PKG.get_data
3509 (X_PERSON_ID => l_person_id,
3510 X_SESSION_DATE => sysdate,
3511 X_ADDRESS_LINE1 => l_address_line1,
3512 X_ADDRESS_LINE2 => l_address_line2,
3513 X_ADDRESS_LINE3 => l_address_line3,
3514 X_TOWN_OR_CITY => l_town_or_city,
3515 X_REGION_1 => l_county,
3516 X_COUNTRY => l_country,
3517 X_POSTAL_CODE => l_postal_code,
3518 X_ASSIGNMENT_ID => l_assignment_id,
3519 X_ASSIGNMENT_ACTION_ID => l_last_asg_action_id,
3520 X_ASSIGNMENT_END_DATE => l_asg_effective_end_date,
3521 X_DATE_EARNED => l_date_earned,
3522 X_PAYROLL_ACTION_ID => l_last_pay_action_id,
3523 X_TRANSFER_DATE => l_transfer_date);
3524 --
3525 hr_utility.trace('Last asg action: '||to_char(l_last_asg_action_id));
3526 hr_utility.trace('Date earned : ' || to_char(l_date_earned));
3527 --
3528 IF g_do_edi_validation or g_edi_ver = 'V6' THEN
3529
3530 EDI_MOVDED6_ASG(address_line1 => l_address_line1,
3531 address_line2 => l_address_line2,
3532 address_line3 => l_address_line3,
3533 assignment_number => l_assignment_number,
3534 county => l_county,
3535 first_name => l_first_name,
3536 middle_name => l_middle_name,
3537 last_name => l_last_name,
3538 national_insurance_number => l_ni_number,
3539 postal_code => l_postal_code,
3540 title => l_title,
3541 town_or_city => l_town_or_city,
3542 effective_date => p_effective_date,
3543 p_assactid => p_assactid,
3544 edi_validation_fail => l_edi_movded6_asg_flag);
3545
3546 IF l_edi_movded6_asg_flag = 'Y' THEN
3547 l_edi_validation_fail := 'Y';
3548 END IF;
3549 /* All the validations are already done in EDI_MOVDED6_ASG so commenting out
3550 IF pay_gb_eoy_magtape.validate_input(upper(l_address_line1),
3551 'EDI_SURNAME') > 0 THEN
3552 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3553 hr_utility.set_message_token('ITEM_NAME', 'Address Line 1');
3554 hr_utility.raise_error;
3555 END IF;
3556 IF pay_gb_eoy_magtape.validate_input(upper(l_address_line2),
3557 'EDI_SURNAME') > 0 THEN
3558 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3559 hr_utility.set_message_token('ITEM_NAME', 'Address Line 2');
3560 hr_utility.raise_error;
3561 END IF;
3562 IF pay_gb_eoy_magtape.validate_input(upper(l_address_line3),
3563 'EDI_SURNAME') > 0 THEN
3564 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3565 hr_utility.set_message_token('ITEM_NAME', 'Address Line 3');
3566 hr_utility.raise_error;
3567 END IF;
3568 IF pay_gb_eoy_magtape.validate_input(upper(l_town_or_city),
3569 'FULL_EDI') > 0 THEN
3570 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3571 hr_utility.set_message_token('ITEM_NAME', 'Town or City');
3572 hr_utility.raise_error;
3573 END IF;
3574 IF pay_gb_eoy_magtape.validate_input(upper(l_county),
3575 'FULL_EDI') > 0 THEN
3576 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3577 hr_utility.set_message_token('ITEM_NAME', 'County');
3578 hr_utility.raise_error;
3579 END IF;
3580 IF pay_gb_eoy_magtape.validate_input(l_postal_code,
3581 'FULL_EDI') > 0 THEN
3582 hr_utility.set_message(801, 'PAY_78037_EDI_ILLEGAL_CHARS');
3583 hr_utility.set_message_token('ITEM_NAME', 'Postcode');
3584 hr_utility.raise_error;
3585 END IF;*/
3586 END IF;
3587 -- Archive info obtained so far
3588 archive_asg_info(g_p45_final_action, l_last_asg_action_id);
3589 archive_asg_info(g_address_line1_eid, l_address_line1);
3590 archive_asg_info(g_address_line2_eid, l_address_line2);
3591 archive_asg_info(g_address_line3_eid, l_address_line3);
3592 archive_asg_info(g_town_or_city_eid, l_town_or_city);
3593 archive_asg_info(g_county_eid, l_county);
3594 archive_asg_info(g_postal_code_eid, l_postal_code);
3595 archive_asg_info(g_country_eid, l_country);
3596 -- get the student loan flag
3597 l_student_loan_flag := PAY_P45_PKG.get_student_loan_flag
3598 (l_assignment_id,
3599 l_termination_date,
3600 sysdate);
3601 -- get_db_and_bal_items
3602 -- Get database items.
3603 --
3604 -- Bug 5478073: If Final Payemnt not found or it is in a previous tax year
3605 -- then report the period no in which employee has been terminated
3606 IF l_last_asg_action_id = -9999 THEN
3607 OPEN csr_get_term_period_no(l_termination_date, l_payroll_id);
3608 FETCH csr_get_term_period_no INTO l_period_no;
3609 CLOSE csr_get_term_period_no;
3610
3611 --Bug 7281023: Added to return 0 if period not found
3612 IF l_period_no IS NULL
3613 THEN
3614 l_period_no :=0;
3615 END IF;
3616
3617 hr_utility.trace('After csr_get_term_period_no, l_termination_date='||fnd_date.date_to_displaydate(l_termination_date));
3618 hr_utility.trace('l_payroll_id='||l_payroll_id);
3619 hr_utility.trace('l_period_no='||l_period_no);
3620 ELSE
3621 OPEN csr_period_number(l_last_pay_action_id);
3622 FETCH csr_period_number INTO l_period_no;
3623 CLOSE csr_period_number;
3624 hr_utility.trace('After csr_period_number, l_last_pay_action_id='||l_last_pay_action_id);
3625 hr_utility.trace('l_period_no='||l_period_no);
3626 END IF;
3627 --
3628 -- Get element id for PAYE element
3629 OPEN csr_paye_element;
3630 FETCH csr_paye_element INTO l_paye_element_id;
3631 CLOSE csr_paye_element;
3632
3633 -- BEGIN BUG FIX FOR 4595939 --
3634 hr_utility.trace('Date earned : ' || l_date_earned);
3635 hr_utility.trace('Final Process date : ' || l_final_process_date);
3636
3637 if l_date_earned = sysdate then
3638 l_eff_date := l_final_process_date;
3639 else
3640 l_eff_date := l_date_earned;
3641 end if;
3642
3643 get_tax_details(p_assignment_id => l_assignment_id,
3644 p_paye_details_id => g_paye_details_id,
3645 p_paye_id => l_paye_element_id,
3646 p_eff_date => l_eff_date,
3647 p_tax_code => l_tax_code,
3648 p_tax_basis => l_tax_basis,
3649 p_prev_pay => l_prev_pay_char,
3650 p_prev_tax => l_prev_tax_char);
3651 /*
3652 -- Look into run results of PAYE element for tax details
3653 -- Get input_value_id for Tax Code input value
3654 OPEN csr_input_value('Tax Code');
3655 FETCH csr_input_value INTO l_tax_code_ipv_id;
3656 CLOSE csr_input_value;
3657 -- Get input_value_id for Tax Basis input value
3658 OPEN csr_input_value('Tax Basis');
3659 FETCH csr_input_value INTO l_tax_basis_ipv_id;
3660 CLOSE csr_input_value;
3661 -- Get input_value_id for Pay Previous input value
3662 OPEN csr_input_value('Pay Previous');
3663 FETCH csr_input_value INTO l_pay_previous_ipv_id;
3664 CLOSE csr_input_value;
3665 -- Get input_value_id for Tax Previous input value
3666 OPEN csr_input_value('Tax Previous');
3667 FETCH csr_input_value INTO l_tax_previous_ipv_id;
3668 CLOSE csr_input_value;
3669 -- Get tax code from run results of PAYE element
3670 BEGIN
3671 -- fix bug 4545963
3672 -- default the tax details first
3673 OPEN csr_paye_details(l_assignment_id,l_date_earned);
3674 FETCH csr_paye_details INTO l_tax_code,
3675 l_tax_basis,
3676 l_prev_pay_char,
3677 l_prev_tax_char;
3678 CLOSE csr_paye_details;
3679
3680 -- If run result found, overwrite the value.
3681 -- Get max run_result_id for PAYE element
3682 OPEN csr_max_run_result;
3683 FETCH csr_max_run_result INTO l_max_run_result_id;
3684 CLOSE csr_max_run_result;
3685 -- if max run result found then get values from run result values else look at element entries
3686 IF l_max_run_result_id is not null THEN
3687 hr_utility.trace('Max run result found : ' || l_max_run_result_id);
3688 OPEN csr_result_value(l_tax_code_ipv_id);
3689 FETCH csr_result_value INTO l_tax_code_t;
3690 CLOSE csr_result_value;
3691 l_tax_code := nvl(l_tax_code_t, l_tax_code);
3692 --
3693 OPEN csr_result_value(l_tax_basis_ipv_id);
3694 FETCH csr_result_value INTO l_tax_basis_t;
3695 CLOSE csr_result_value;
3696 l_tax_basis := nvl(l_tax_basis_t, l_tax_basis);
3697 --
3698 OPEN csr_result_value(l_pay_previous_ipv_id);
3699 FETCH csr_result_value INTO l_prev_pay_char_t;
3700 CLOSE csr_result_value;
3701 l_prev_pay_char := nvl(l_prev_pay_char_t,l_prev_pay_char);
3702 --
3703 OPEN csr_result_value(l_tax_previous_ipv_id);
3704 FETCH csr_result_value INTO l_prev_tax_char_t;
3705 CLOSE csr_result_value;
3706 l_prev_tax_char := nvl(l_prev_tax_char_t,l_prev_tax_char);
3707 --
3708 END IF;
3709 */
3710 /*
3711 ELSE
3712 hr_utility.trace('Max run resuls not found');
3713 OPEN csr_paye_details(l_assignment_id,l_date_earned);
3714 FETCH csr_paye_details INTO l_tax_code,
3715 l_tax_basis,
3716 l_prev_pay_char,
3717 l_prev_tax_char;
3718 CLOSE csr_paye_details;
3719 END IF;
3720 */
3721 -- END;
3722 hr_utility.trace('tax code: '||l_tax_code||' '||l_tax_basis);
3723 --
3724 -- Get Balance items.
3725 -- Nb. parameter names of the following procedure are inconsistent with
3726 -- the actual values returned.
3727 PAY_P45_PKG2.get_balance_items(
3728 p_assignment_action_id => l_last_asg_action_id,
3729 p_gross_pay => l_taxable,
3730 p_taxable_pay => l_paye,
3731 p_agg_paye_flag => l_agg_paye_flag);
3732 --
3733 -- Shifted Here from below to use it in side this validation
3734 IF l_tax_basis <> 'N' THEN
3735 IF l_last_pay_action_id = -9999 then
3736 OPEN week_month_by_paroll_id(l_payroll_id,p_effective_date);
3737 FETCH week_month_by_paroll_id INTO l_week_or_month;
3738 CLOSE week_month_by_paroll_id;
3739 ELSE
3740 OPEN csr_week_or_month(l_last_pay_action_id);
3741 FETCH csr_week_or_month INTO l_week_or_month;
3742 CLOSE csr_week_or_month;
3743 END IF ;
3744 END IF;
3745
3746 IF g_do_edi_validation or g_edi_ver = 'V6' THEN
3747 IF l_tax_basis = 'N' THEN
3748 /*Bug:8370481 Modifed the Total pay/tax to date and Pay/Tax in this Employment values from
3749 999999.99 to 999999999.99. */
3750 IF l_taxable > 999999999.99 THEN -- Bug:8370481 changed 999999.99 to 999999999.99
3751 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3752 hr_utility.set_message_token('ITEM_NAME', 'Pay in this Employment');
3753 hr_utility.set_message_token('MAX_VALUE', '999999999.99'); -- Bug:8370481 changed 999999.99 to 999999999.99
3754 hr_utility.raise_error;
3755 END IF;
3756 IF l_paye > 999999999.99 THEN -- Bug:8370481 changed 999999.99 to 999999999.99
3757 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3758 hr_utility.set_message_token('ITEM_NAME', 'Tax in this Employment');
3759 hr_utility.set_message_token('MAX_VALUE', '999999999.99'); -- Bug:8370481 changed 999999.99 to 999999999.99
3760 hr_utility.raise_error;
3761 END IF;
3762 ELSE
3763 IF nvl(l_taxable,0) + nvl(to_number(l_prev_pay_char),0) > 999999999.99 -- Bug:8370481 changed 999999.99 to 999999999.99
3764 THEN
3765 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3766 hr_utility.set_message_token('ITEM_NAME', 'Total pay to date');
3767 hr_utility.set_message_token('MAX_VALUE', '999999999.99'); -- Bug:8370481 changed 999999.99 to 999999999.99
3768 hr_utility.raise_error;
3769 END IF;
3770 IF nvl(l_paye,0) + nvl(to_number(l_prev_tax_char),0) > 999999999.99 THEN -- Bug:8370481 changed 999999.99 to 999999999.99
3771 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3772 hr_utility.set_message_token('ITEM_NAME', 'Total tax to date');
3773 hr_utility.set_message_token('MAX_VALUE', '999999999.99'); -- Bug:8370481 changed 999999.99 to 999999999.99
3774 hr_utility.raise_error;
3775 END IF;
3776 END IF; -- l_tax_basis = 'N'
3777 IF l_address_line1 IS NULL THEN
3778 hr_utility.set_message(801, 'PAY_GB_MISSING_VALUE');
3779 hr_utility.set_message_token('ITEM_NAME', 'Address');
3780 hr_utility.raise_error;
3781 END IF;
3782 IF l_tax_code IS NULL THEN
3783 hr_utility.set_message(801, 'PAY_GB_MISSING_VALUE');
3784 hr_utility.set_message_token('ITEM_NAME', 'Tax Code');
3785 hr_utility.raise_error;
3786 ELSIF length(ltrim(l_tax_code,'S')) > 6 THEN
3787 hr_utility.set_message(801, 'PAY_78034_VALUE_EXCEEDS_MAX');
3788 hr_utility.set_message_token('ITEM_NAME', 'Tax Code length');
3789 hr_utility.set_message_token('MAX_VALUE', '6 characters');
3790 hr_utility.raise_error;
3791 END IF;
3792
3793 IF l_tax_basis = 'N' AND l_tax_code IS NULL THEN
3794 populate_run_msg(p_assactid,'The Tax Code at Leaving of the assignment must be defined if the Week1/Month1 indicator is defined.');
3795 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Tax Code at Leaving of the assignment must be defined if the Week1/Month1 indicator is defined.');
3796 l_edi_validation_fail := 'Y';
3797 END IF;
3798
3799 IF l_tax_basis = 'N' AND l_week_or_month IS NOT NULL AND l_tax_code IS NOT NULL THEN
3800 populate_run_msg(p_assactid,'The Week/Month type of the assignment must not be defined if both the Tax Code at Leaving Date and the Week1/Month1 indicator are defined.');
3801 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Week/Month type of the assignment must not be defined if both the Tax Code at Leaving Date and the Week1/Month1 indicator are defined.');
3802 l_edi_validation_fail := 'Y';
3803 END IF;
3804
3805 IF l_tax_basis <> 'N' AND l_week_or_month IS NULL AND l_tax_code IS NOT NULL THEN
3806 populate_run_msg(p_assactid,'The Week/Month type of the assignment must be defined if both the Tax Code at Leaving Date and the Week1/Month1 indicator are defined.');
3807 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Week/Month type of the assignment must be defined if both the Tax Code at Leaving Date and the Week1/Month1 indicator are defined.');
3808 l_edi_validation_fail := 'Y';
3809 END IF;
3810
3811 IF l_tax_basis <> 'N' AND l_period_no IS NOT NULL AND l_week_or_month IS NULL THEN
3812 populate_run_msg(p_assactid,'The Week/Month type of the assignment must be defined if the Week/Month Number is defined.');
3813 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Week/Month type of the assignment must be defined if the Week/Month Number is defined.');
3814 l_edi_validation_fail := 'Y';
3815 END IF;
3816
3817 IF nvl(l_taxable,0) <> 0 AND nvl(l_paye,0) <> 0 THEN
3818 IF ((l_tax_basis ='N') OR (l_tax_basis <> 'N' AND trunc(nvl(to_number(l_prev_pay_char),0) * 100) <> 0 AND trunc(nvl(to_number(l_prev_tax_char),0) * 100) <> 0)) THEN
3819 l_pay_in_this_emp := to_char(trunc(l_taxable * 100));
3820 END IF;
3821 END IF;
3822
3823 IF nvl(l_taxable,0) <> 0 AND nvl(l_paye,0) <> 0 THEN
3824 IF ((l_tax_basis ='N') OR (l_tax_basis <> 'N' AND trunc(nvl(to_number(l_prev_pay_char),0) * 100) <> 0 AND trunc(nvl(to_number(l_prev_tax_char),0) * 100) <> 0)) THEN
3825 IF l_paye >= 0 then
3826 l_tax_in_this_emp := to_char(trunc(l_paye * 100));
3827 ELSE
3828 l_tax_in_this_emp := 0;
3829 less_than_zero_flag := 'Y';
3830 END IF;
3831 END IF;
3832 END IF;
3833
3834 -- Bug 10077936: Modified the expressions of l_total_pay_to_date and l_total_tax_to_date
3835 IF ( nvl(l_taxable,0) <> 0 OR nvl(to_number(l_prev_pay_char),0) <> 0 ) AND
3836 ( nvl(l_paye,0) <> 0 OR nvl(to_number(l_prev_tax_char),0) <> 0) THEN
3837 IF l_tax_basis <> 'N' THEN
3838 l_total_pay_to_date := to_char(trunc(nvl(l_taxable,0)*100) + trunc(nvl(to_number(l_prev_pay_char),0) * 100 ));
3839 END IF;
3840 END IF;
3841
3842 IF ( nvl(l_taxable,0) <> 0 OR nvl(to_number(l_prev_pay_char),0) <> 0 ) AND
3843 ( nvl(l_paye,0) <> 0 OR nvl(to_number(l_prev_tax_char),0) <> 0 ) THEN
3844 IF l_tax_basis <> 'N' THEN
3845 l_total_tax_to_date := to_char(trunc(nvl(l_paye,0)* 100) + trunc(nvl(to_number(l_prev_tax_char),0) * 100));
3846 END IF;
3847 END IF;
3848
3849
3850
3851 /*IF l_tax_basis ='N' AND l_pay_in_this_emp IS NULL THEN
3852 populate_run_msg(p_assactid,'The Total Pay in this Employment of the assignment must be defined if the Week1/Month1 indicator is defined.');
3853 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Pay in this Employment of the assignment must be defined if the Week1/Month1 indicator is defined.');
3854 l_edi_validation_fail := 'Y';
3855 END IF;
3856
3857 IF l_tax_basis ='N' AND l_tax_in_this_emp IS NULL THEN
3858 populate_run_msg(p_assactid,'The Tax Deducted in this Employment of the assignment must be defined if the Week1/Month1 indicator is defined.');
3859 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Tax Deducted in this Employment of the assignment must be defined if the Week1/Month1 indicator is defined.');
3860 l_edi_validation_fail := 'Y';
3861 END IF;*/
3862 --
3863 IF l_pay_in_this_emp IS NOT NULL THEN
3864 /*IF l_tax_in_this_emp IS NULL THEN
3865 populate_run_msg(p_assactid,'The Total Pay in this Employment of the assignment must be defined if the Tax Deducted in this employment is defined.');
3866 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' :The Total Pay in this Employment of the assignment must be defined if the Tax Deducted in this employment is defined.');
3867 l_edi_validation_fail := 'Y';
3868 END IF;*/ -- Tax can be Zero even if pay is not Zero
3869
3870 IF pay_gb_eoy_magtape.validate_input(l_pay_in_this_emp,'NUMBER_1') > 0 THEN
3871 populate_run_msg(p_assactid,'The Total Pay in this Employment of the assignment has invalid character(s).');
3872 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Pay in this Employment of the assignment has invalid character(s).');
3873 l_edi_validation_fail := 'Y';
3874 END IF;
3875
3876 IF to_number(l_pay_in_this_emp) < 0 THEN
3877 populate_run_msg(p_assactid,'The Total Pay in this Employment of the assignment is Less Than 0');
3878 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Pay in this Employment of the assignment is Less Than 0');
3879 l_edi_validation_fail := 'Y';
3880 END IF;
3881
3882 IF l_tax_in_this_emp IS NOT NULL THEN
3883 IF to_number(l_pay_in_this_emp) < to_number(l_tax_in_this_emp) THEN
3884 populate_run_msg(p_assactid,'The Tax Deducted in this employment of the assignment must be Less Than or equal to the Total Pay in this employment.');
3885 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Tax Deducted in this employment of the assignment must be Less Than or equal to the Total Pay in this employment.');
3886 l_edi_validation_fail := 'Y';
3887 END IF;
3888 END IF;
3889 END IF;
3890 --
3891 IF l_tax_in_this_emp IS NOT NULL THEN
3892 IF l_pay_in_this_emp IS NULL THEN
3893 populate_run_msg(p_assactid,'The Tax Deducted in this Employment of the assignment must be defined if the Total Pay in this employment is defined.');
3894 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Tax Deducted in this Employment of the assignment must be defined if the Total Pay in this employment is defined.');
3895 l_edi_validation_fail := 'Y';
3896 END IF;
3897
3898 IF pay_gb_eoy_magtape.validate_input(l_tax_in_this_emp,'NUMBER_1') > 0 THEN
3899 populate_run_msg(p_assactid,'The Tax Deducted in this Employment of the assignment has invalid character(s).');
3900 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Tax Deducted in this Employment of the assignment has invalid character(s).');
3901 l_edi_validation_fail := 'Y';
3902 ELSIF less_than_zero_flag = 'Y' THEN
3903 populate_run_msg(p_assactid,'The Tax Deducted in this Employment of the assignment is Less Than 0; printing the amount as Zero.');
3904 fnd_file.put_line (fnd_file.LOG,'The Tax Deducted in this Employment of the assignment is Less Than 0; printing the amount as Zero.');
3905 -- Not raising an error as this is a warning
3906 END IF;
3907 END IF;
3908
3909 IF l_total_pay_to_date IS NOT NULL THEN
3910 /*IF l_tax_basis = 'N' THEN
3911 populate_run_msg(p_assactid,'The Total Pay to Date of the assignment must not be defined if the Week1/Month1 indicator is defined.');
3912 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Pay to Date of the assignment must not be defined if the Week1/Month1 indicator is defined.');
3913 l_edi_validation_fail := 'Y';
3914 END IF;*/-- Commenting the code as Total Pay to Date can be Zero and it is legitame to have no pay
3915 -- This case will be handled at the EDi level
3916
3917 IF pay_gb_eoy_magtape.validate_input(l_total_pay_to_date,'NUMBER_1') > 0 THEN
3918 populate_run_msg(p_assactid,'The Total Pay to Date of the assignment has invalid character(s).');
3919 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Pay to Date of the assignment has invalid character(s).');
3920 l_edi_validation_fail := 'Y';
3921 END IF;
3922
3923 --Bug 10077936:Removed the Else if and made it If statement
3924
3925 IF to_number(l_total_pay_to_date) < 0 THEN
3926 populate_run_msg(p_assactid,'The Total Pay to Date of the assignment is Less Than 0.');
3927 fnd_file.put_line (fnd_file.LOG,'The Total Pay to Date of the assignment is Less Than 0.');
3928 l_edi_validation_fail := 'Y';
3929 END IF;
3930
3931 IF l_total_tax_to_date IS NOT NULL THEN
3932 IF (to_number(l_total_pay_to_date) < to_number(l_total_tax_to_date)) THEN
3933 populate_run_msg(p_assactid,'The Total Tax to Date of the assignment must be Less Than or equal to the Total Pay to Date.');
3934 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Tax to Date of the assignment must be Less Than or equal to the Total Pay to Date.');
3935 l_edi_validation_fail := 'Y';
3936 END IF;
3937 END IF;
3938 END IF;
3939
3940 IF l_total_pay_to_date IS NULL THEN
3941 IF l_total_tax_to_date IS NOT NULL THEN
3942 populate_run_msg(p_assactid,'The Total Pay to Date of the assignment must be defined if the Total Tax to Date is defined.');
3943 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Pay to Date of the assignment must be defined if the Total Tax to Date is defined.');
3944 l_edi_validation_fail := 'Y';
3945 END IF;
3946
3947 /* IF l_tax_basis <> 'N' THEN
3948 populate_run_msg(p_assactid,'The Total Pay to Date of the assignment must be defined if the Week1/Month1 indicator is not defined.');
3949 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Pay to Date of the assignment must be defined if the Week1/Month1 indicator is not defined.');
3950 l_edi_validation_fail := 'Y';
3951 END IF;*/-- Commenting the code as Total Pay to Date can be Zero and it is legitame to have no pay
3952 -- This case will be handled at the EDi level
3953 END IF;
3954
3955 IF l_total_tax_to_date IS NOT NULL THEN
3956 /*IF l_tax_basis = 'N' THEN
3957 populate_run_msg(p_assactid,'The Total Tax to Date of the assignment must not be defined if the Week1/Month1 indicator is defined.');
3958 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Tax to Date of the assignment must not be defined if the Week1/Month1 indicator is defined.');
3959 l_edi_validation_fail := 'Y';
3960 ELSIF */
3961 IF pay_gb_eoy_magtape.validate_input(l_total_tax_to_date,'NUMBER_1') > 0 THEN
3962 populate_run_msg(p_assactid,'The Total Tax to Date of the assignment has invalid character(s).');
3963 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Tax to Date of the assignment has invalid character(s).');
3964 l_edi_validation_fail := 'Y';
3965 END IF;
3966
3967 IF to_number(l_total_tax_to_date) < 0 THEN
3968 populate_run_msg(p_assactid,'The Total Tax to Date of the assignment is Less Than 0.');
3969 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Tax to Date of the assignment is Less Than 0.');
3970 l_edi_validation_fail := 'Y';
3971 END IF;
3972 END IF;
3973
3974 /*IF l_total_tax_to_date IS NULL THEN
3975 IF l_total_pay_to_date IS NOT NULL THEN
3976 populate_run_msg(p_assactid,'The Total Tax to Date of the assignment must be defined if the Total Pay to Date is defined.');
3977 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' :The Total Tax to Date of the assignment must be defined if the Total Pay to Date is defined.');
3978 l_edi_validation_fail := 'Y'; -- Total Tax can be Zero even though the employee has total pay
3979 ELSIF l_tax_basis <> 'N' THEN
3980 populate_run_msg(p_assactid,'The Total Tax to Date of the assignment must be defined if the Week1/Month1 indicator is not defined.');
3981 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Total Tax to Date of the assignment must be defined if the Week1/Month1 indicator is not defined.');
3982 l_edi_validation_fail := 'Y';
3983 END IF;
3984 END IF; */ -- Total Tax zan be Zero if tax basis is not N
3985
3986 IF l_tax_code IS NULL THEN
3987 populate_run_msg(p_assactid,'The Tax Code at Leaving Date is missing.');
3988 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Tax Code at Leaving Date is missing.');
3989 l_edi_validation_fail := 'Y';
3990 ELSIF pay_gb_eoy_magtape.validate_tax_code_yrfil(p_assactid,l_tax_code,p_effective_date) <> ' ' THEN
3991 populate_run_msg(p_assactid,'The Tax Code at Leaving Date is invalid.');
3992 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Tax Code at Leaving Date is invalid.');
3993 l_edi_validation_fail := 'Y';
3994 END IF;
3995
3996 IF l_week_or_month IS NOT NULL THEN
3997 IF l_week_or_month = 'W' THEN
3998 IF l_period_no < 1 OR l_period_no = 55 OR l_period_no > 56 THEN
3999 populate_run_msg(p_assactid,'The Week/Month Number of the assignment must be between 1 to 54 or 56 as the Week/Month Type is Weekly.');
4000 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Week/Month Number of the assignment must be between 1 to 54 or 56 as the Week/Month Type is Weekly.');
4001 l_edi_validation_fail := 'Y';
4002 END IF;
4003 ELSIF l_week_or_month = 'M' THEN
4004 IF l_period_no < 1 OR l_period_no > 12 THEN
4005 populate_run_msg(p_assactid,'The Week/Month Number of the assignment must be between 1 to 12 as the Week/Month Type is Monthly.');
4006 fnd_file.put_line (fnd_file.LOG,l_assignment_number||' : The Week/Month Number of the assignment must be between 1 to 12 as the Week/Month Type is Monthly.');
4007 l_edi_validation_fail := 'Y';
4008 END IF;
4009 END IF;
4010 END IF;
4011
4012 IF l_edi_validation_fail = 'Y' THEN
4013 raise_application_error (-20001, 'The Process Failed as there were Errors for this assignment' );
4014 END IF;
4015
4016 END IF; -- g_do_edi_validation
4017 -- Check Whether it is Week or Month
4018 /*OPEN csr_week_or_month(l_last_pay_action_id);
4019 FETCH csr_week_or_month INTO l_week_or_month;
4020 CLOSE csr_week_or_month;*/ -- Shifted above g_do_edi_validation
4021 --
4022 -- Archive info obtained so far
4023 archive_asg_info(g_student_loan_flag_eid, l_student_loan_flag);
4024 IF l_week_or_month = 'W' THEN
4025 archive_asg_info(g_week_number_eid, l_period_no);
4026 ELSE
4027 archive_asg_info(g_month_number_eid, l_period_no);
4028 END IF;
4029
4030 archive_asg_info(g_tax_code_eid, l_tax_code);
4031 IF l_tax_basis = 'N' THEN
4032 archive_asg_info(g_w1_m1_indicator_eid, 'X');
4033 END IF;
4034 archive_asg_info(g_prev_taxable_pay_eid, l_prev_pay_char);
4035 archive_asg_info(g_prev_tax_paid_eid, l_prev_tax_char);
4036 archive_asg_info(g_taxable_pay_eid,
4037 fnd_number.number_to_canonical(l_taxable));
4038 archive_asg_info(g_tax_paid_eid,
4039 fnd_number.number_to_canonical(l_paye));
4040 --
4041 hr_utility.set_location(' Leaving: '||l_proc,999);
4042 --hr_utility.trace_off;
4043 END archive_code;
4044 --
4045 PROCEDURE spawn_reports
4046 IS
4047 l_proc CONSTANT VARCHAR2(35):= g_package||'spawn_reports';
4048 --
4049 l_count NUMBER := 0;
4050 l_dummy NUMBER;
4051 l_print_style VARCHAR2(2);
4052 l_report_short_name VARCHAR2(20);
4053 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
4054 l_number_of_copies fnd_concurrent_requests.number_of_copies%TYPE;
4055 l_request_id NUMBER:=-1;
4056 l_formula_id ff_formulas_f.formula_id%TYPE;
4057 --
4058 l_print_together VARCHAR2(80);
4059 l_print_return BOOLEAN;
4060 l_stationary VARCHAR2(2); /*P45 A4 2008-09 changes */
4061 l_defer_print VARCHAR2(1);
4062 xml_layout BOOLEAN ; /*P45 A4 2008-09 changes */
4063 l_printer_style fnd_concurrent_requests.print_style%TYPE; --Bug 9170440
4064
4065 --
4066 cursor csr_get_formula_id(p_formula_name VARCHAR2) IS
4067 SELECT a.formula_id
4068 FROM ff_formulas_f a,
4069 ff_formula_types t
4070 WHERE a.formula_name = p_formula_name
4071 AND business_group_id IS NULL
4072 AND legislation_code = 'GB'
4073 AND a.formula_type_id = t.formula_type_id
4074 AND t.formula_type_name = 'Oracle Payroll';
4075 --
4076 cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
4077 SELECT printer,
4078 print_style,
4079 decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
4080 FROM pay_payroll_actions pact,
4081 fnd_concurrent_requests fcr
4082 WHERE fcr.request_id = pact.request_id
4083 AND pact.payroll_action_id = p_payroll_action_id;
4084 --
4085 cursor get_errored_actions(c_payroll_action_id number) is
4086 select 1 from dual where exists
4087 (select action_status
4088 from pay_assignment_actions
4089 where payroll_action_id = c_payroll_action_id
4090 and action_status = 'E');
4091 --
4092 /*P45 A4 2008-09 changes */
4093 /*Cursor to fetch stationary type and differ printing option*/
4094 cursor csr_get_stationary_defer(c_payroll_action_id NUMBER) IS
4095 select
4096 pay_gb_eoy_archive.get_parameter(legislative_parameters,
4097 'P45') stationary_type,
4098 pay_gb_eoy_archive.get_parameter(legislative_parameters,
4099 'PDEF') defer_print
4100 from pay_payroll_actions
4101 where payroll_action_id = c_payroll_action_id;
4102 /*P45 A4 2008-09 changes */
4103 --
4104 rec_print_options csr_get_print_options%ROWTYPE;
4105 --
4106 BEGIN
4107 hr_utility.set_location('Entering: '||l_proc,1);
4108 -- get pertinent SRS parameters from the pay_mag_tape tables
4109 BEGIN
4110 LOOP
4111 l_count := l_count + 1;
4112 IF pay_mag_tape.internal_prm_names(l_count) =
4113 'TRANSFER_PAYROLL_ACTION_ID'
4114 THEN
4115 l_payroll_action_id := to_number(pay_mag_tape.internal_prm_values
4116 (l_count));
4117 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'P45'
4118 THEN
4119 l_print_style := pay_mag_tape.internal_prm_values(l_count);
4120 ELSIF pay_mag_tape.internal_prm_names(l_count) = 'PDEF'
4121 THEN
4122 IF pay_mag_tape.internal_prm_values(l_count) = 'Y' THEN
4123 -- Defer printing param set to Y
4124 l_number_of_copies := 0;
4125 ELSE
4126 l_number_of_copies := 1;
4127 END IF;
4128 END IF;
4129 END LOOP;
4130 EXCEPTION
4131 WHEN no_data_found THEN
4132 -- Use this exception to exit loop as no. of plsql tab items
4133 -- is not known beforehand. All values should be assigned.
4134 NULL;
4135 WHEN value_error THEN
4136 NULL;
4137 END;
4138 --
4139 -- Check no assignment actions were errored
4140 open get_errored_actions(l_payroll_action_id);
4141 fetch get_errored_actions into l_dummy;
4142 if get_errored_actions%notfound then
4143 -- No errors, so set up print options and spawn report.
4144 IF l_print_style = 'L'
4145 THEN l_report_short_name := 'PAYGB45L';
4146 ELSE l_report_short_name := 'PAYRPP45';
4147 END IF;
4148 --
4149 hr_utility.set_location(l_proc,10);
4150 -- Get printer options from archive request
4151 OPEN csr_get_print_options(l_payroll_action_id);
4152 FETCH csr_get_print_options INTO rec_print_options;
4153 CLOSE csr_get_print_options;
4154 --
4155 l_print_together := nvl(fnd_profile.value('CONC_PRINT_TOGETHER'), 'N');
4156 --
4157
4158 -- Get the stationary and defer printing options
4159 OPEN csr_get_stationary_defer(l_payroll_action_id);
4160 FETCH csr_get_stationary_defer INTO l_stationary,l_defer_print;
4161 CLOSE csr_get_stationary_defer;
4162
4163 -- Start of bug 8275145
4164 IF upper(l_stationary) = 'C3' or upper(l_stationary) = 'C4' then
4165 l_printer_style := 'P45C';
4166 else
4167 l_printer_style := rec_print_options.print_style;
4168 end if;
4169 -- Start of bug 8275145
4170
4171 -- Set printer options
4172 l_print_return := fnd_request.set_print_options
4173 (printer => rec_print_options.printer,
4174 style => l_printer_style,
4175 copies => l_number_of_copies,
4176 save_output => hr_general.char_to_bool
4177 (rec_print_options.save_output),
4178 print_together => l_print_together);
4179 hr_utility.trace('Print options set call returned: '||
4180 hr_general.bool_to_char(l_print_return));
4181
4182 -- Submit report
4183 /*P45 A4 2008-09 changes */
4184 /*l_request_id := fnd_request.submit_request
4185 (application => 'PAY',
4186 program => l_report_short_name,
4187 argument1 => l_payroll_action_id);*/
4188
4189 -- Submit report
4190 IF upper(l_defer_print) = 'N' then
4191 IF upper(l_stationary) = 'C' then
4192 l_request_id := fnd_request.submit_request
4193 (application => 'PAY',
4194 program => 'PAYRPP45',
4195 argument1 => l_payroll_action_id);
4196 ELSIF upper(l_stationary) = 'L' then
4197 l_request_id := fnd_request.submit_request
4198 (application => 'PAY',
4199 program => 'PAYGB45L',
4200 argument1 => l_payroll_action_id);
4201 -- Start of bug 8275145
4202 ELSIF upper(l_stationary) = 'C3' then
4203 l_request_id := fnd_request.submit_request
4204 (application => 'PAY',
4205 program => 'PAYRPP45',
4206 argument1 => l_payroll_action_id,
4207 argument2 => null,
4208 argument3 => 'Continuous (A4 Sheet 3-Part)');
4209 ELSIF upper(l_stationary) = 'C4' then
4210 l_request_id := fnd_request.submit_request
4211 (application => 'PAY',
4212 program => 'PAYRPP45',
4213 argument1 => l_payroll_action_id,
4214 argument2 => null,
4215 argument3 => 'Continuous (A4 Sheet 4-Part)');
4216 -- end of bug 8275145
4217 ELSIF upper(l_stationary) = 'P' then
4218 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBA4P45','en','US','PDF');
4219 IF xml_layout = true then
4220 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'PAY',
4221 program => 'PYGBA4P45',
4222 argument1 => l_payroll_action_id);
4223 END IF;
4224 ELSIF upper(l_stationary) = 'A' then
4225 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBA4P45LS','en','US','PDF');
4226 IF xml_layout = true then
4227 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'PAY',
4228 program => 'PYGBA4P45',
4229 argument1 => l_payroll_action_id);
4230 END IF;
4231 -- Start bug 8275145
4232 ELSIF upper(l_stationary) = 'A4' then
4233 xml_layout := FND_REQUEST.ADD_LAYOUT('PAY','PYGBA4P45LS4','en','US','PDF');
4234 IF xml_layout = true then
4235 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'PAY',
4236 program => 'PYGBA4P45',
4237 argument1 => l_payroll_action_id);
4238 END IF;
4239 -- ENd bug 8275145
4240 END IF;
4241 ELSIF upper(l_defer_print) = 'Y' then
4242 l_request_id := -2;
4243 END IF;
4244 /*P45 A4 2008-09 changes */
4245 --
4246 IF l_request_id = 0 THEN
4247 g_fnd_rep_request_msg := fnd_message.get;
4248 END IF; --
4249 end if; -- get_errored_actions%notfound
4250 --
4251 close get_errored_actions;
4252 -- Set up formula inputs
4253 hr_utility.set_location(l_proc,20);
4254 OPEN csr_get_formula_id('PAY_GB_P45_REPORT_SUBMISSION');
4255 FETCH csr_get_formula_id INTO l_formula_id;
4256 CLOSE csr_get_formula_id;
4257 --
4258 pay_mag_tape.internal_prm_names(1) := 'NO_OF_PARAMETERS';
4259 pay_mag_tape.internal_prm_values(1) := '4';
4260 pay_mag_tape.internal_prm_names(2) := 'NEW_FORMULA_ID';
4261 pay_mag_tape.internal_prm_values(2) := to_char(l_formula_id);
4262 pay_mag_tape.internal_prm_names(3) := 'P45_REQUEST_ID';
4263 pay_mag_tape.internal_prm_values(3) := to_char(l_request_id);
4264 pay_mag_tape.internal_prm_names(4) := 'PAYROLL_ACTION_ID';
4265 pay_mag_tape.internal_prm_values(4) := to_char(l_payroll_action_id);
4266 --
4267 -- Exit procedure, C code will fire formula
4268 hr_utility.set_location(' Leaving: '||l_proc,999);
4269 END spawn_reports;
4270 --
4271 PROCEDURE edi_act_creation(pactid IN NUMBER,
4272 stperson IN NUMBER,
4273 endperson IN NUMBER,
4274 chunk IN NUMBER)
4275 IS
4276 l_proc CONSTANT VARCHAR2(35):= g_package||'edi_act_creation';
4277 l_actid pay_assignment_actions.assignment_action_id%TYPE;
4278 --
4279 cursor csr_user_entity(p_entity_name VARCHAR2) IS
4280 SELECT user_entity_id
4281 FROM ff_user_entities
4282 WHERE user_entity_name = p_entity_name
4283 AND legislation_code = 'GB'
4284 AND business_group_id IS NULL;
4285 --
4286 cursor csr_parameter_info(p_payroll_action_id NUMBER) IS
4287 SELECT
4288 to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters,
4289 'PAYROLL_ID')) payroll_id,
4290 substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
4291 'TAX_REF'),1,20) tax_ref,
4292 fnd_date.canonical_to_date
4293 (pay_gb_eoy_archive.get_parameter(legislative_parameters,
4294 'DATE_FROM')) start_date,
4295 effective_date end_date,
4296 business_group_id
4297 FROM pay_payroll_actions
4298 WHERE payroll_action_id = p_payroll_action_id;
4299 --
4300 cursor csr_assignments IS
4301 -- Do not restrict to the last P45 archive action for each employee - get
4302 -- all that are not interlocked
4303 -- Restrict on payroll_id legislative parameter as necessary. If payroll
4304 -- not specified on Archive submission, token will not appear in the
4305 -- legislative parameters, so ensure substr returns null by concatenating
4306 -- the token.
4307 -- If EDI process is restricted by payroll but archive wasn't, need to
4308 -- drill down to asg level and restrict on the payroll_id in the archive.
4309 -- Performance fix bug 5202965
4310 SELECT /*+ ORDERED */
4311 DISTINCT
4312 act.assignment_action_id archive_action,
4313 act.assignment_id
4314 FROM pay_payroll_actions pact,
4315 pay_assignment_actions act,
4316 per_assignments_f paf,
4317 pay_action_interlocks pai,
4318 ff_archive_items fai
4319 WHERE pact.report_type ='P45'
4320 AND pact.report_qualifier ='GB'
4321 AND pact.report_category ='P45'
4322 AND pact.action_status = 'C'
4323 AND pact.action_type = 'X'
4324 AND pact.business_group_id +0 = g_business_group_id
4325 AND pact.effective_date BETWEEN g_start_date AND g_end_date
4326 AND substr(pact.legislative_parameters,
4327 instr(pact.legislative_parameters,'TAX_REF=') + 8,
4328 instr(pact.legislative_parameters||' ',' ',
4329 instr(pact.legislative_parameters,'TAX_REF=')+8)
4330 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
4331 = g_tax_ref
4332 AND (g_payroll_id IS NULL
4333 OR
4334 nvl(substr(pact.legislative_parameters,
4335 instr(pact.legislative_parameters||' PAYROLL_ID='
4336 ,'PAYROLL_ID=') + 11,
4337 instr(pact.legislative_parameters||' ',' ',
4338 instr(pact.legislative_parameters,'PAYROLL_ID=')+11)
4339 - instr(pact.legislative_parameters,'PAYROLL_ID=') - 11),
4340 nvl(to_char(g_payroll_id),'x')) = nvl(to_char(g_payroll_id),'x'))
4341 /* restrict by payroll_id in archive */
4342 AND fai.context1 (+) = act.assignment_action_id
4343 AND fai.user_entity_id (+) = g_payroll_id_eid
4344 AND nvl(fai.VALUE,nvl(to_char(g_payroll_id),'x'))
4345 = nvl(nvl(to_char(g_payroll_id),fai.VALUE),'x')
4346 AND pact.payroll_action_id = act.payroll_action_id
4347 AND paf.assignment_id = act.assignment_id
4348 AND paf.person_id BETWEEN stperson AND endperson
4349 AND paf.business_group_id +0 = g_business_group_id
4350 /* restrict to one row per asg. */
4351 -- Comment out this code as it will be replace by distinct
4352 --AND paf.effective_start_date =
4353 -- (SELECT max(paf2.effective_start_date)
4354 -- FROM per_assignments_f paf2
4355 -- WHERE paf2.assignment_id = paf.assignment_id)
4356 /* commnet out this code and replace by the code below */
4357 --AND NOT EXISTS (SELECT 1
4358 -- FROM pay_action_interlocks pai
4359 -- WHERE pai.locked_action_id = act.assignment_action_id);
4360 AND pai.locked_action_id(+) = act.assignment_action_id
4361 AND decode(pai.locked_action_id,null,1,2) = 1;
4362 --
4363 cursor csr_range_assignments is
4364 --
4365 -- This is a copy of csr_assignments above except with a join to pay_
4366 -- population_ranges for performance enhancement.
4367 --
4368 -- Used Not Exists instead of using Outer Join : Bug :7442831
4369 SELECT /*+ordered
4370 index(pact PAY_PAYROLL_ACTIONS_N52)
4371 index(ppr PAY_POPULATION_RANGES_N4)
4372 index(act PAY_ASSIGNMENT_ACTIONS_N51)
4373 index(fai FF_ARCHIVE_ITEMS_N50)*/
4374 DISTINCT
4375 act.assignment_action_id archive_action,
4376 act.assignment_id
4377 from pay_payroll_actions pact,
4378 pay_population_ranges ppr,
4379 per_assignments_f paf,
4380 pay_assignment_actions act,
4381 ff_archive_items fai
4382 WHERE pact.report_type ='P45'
4383 AND pact.report_qualifier ='GB'
4384 AND pact.report_category ='P45'
4385 AND pact.action_status = 'C'
4386 AND pact.action_type = 'X'
4387 AND pact.business_group_id +0 = g_business_group_id
4388 AND pact.effective_date BETWEEN g_start_date AND g_end_date
4389 AND substr(pact.legislative_parameters,
4390 instr(pact.legislative_parameters,'TAX_REF=') + 8,
4391 instr(pact.legislative_parameters||' ',' ',
4392 instr(pact.legislative_parameters,'TAX_REF=')+8)
4393 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
4394 = g_tax_ref
4395 AND (g_payroll_id IS NULL
4396 OR
4397 nvl(substr(pact.legislative_parameters,
4398 instr(pact.legislative_parameters||' PAYROLL_ID='
4399 ,'PAYROLL_ID=') + 11,
4400 instr(pact.legislative_parameters||' ',' ',
4401 instr(pact.legislative_parameters,'PAYROLL_ID=')+11)
4402 - instr(pact.legislative_parameters,'PAYROLL_ID=') - 11),
4403 nvl(to_char(g_payroll_id),'x')) = nvl(to_char(g_payroll_id),'x'))
4404 /* restrict by payroll_id in archive */
4405 AND fai.context1 (+) = act.assignment_action_id
4406 AND fai.user_entity_id (+) = g_payroll_id_eid
4407 AND nvl(fai.VALUE,nvl(to_char(g_payroll_id),'x'))
4408 = nvl(nvl(to_char(g_payroll_id),fai.VALUE),'x')
4409 AND pact.payroll_action_id = act.payroll_action_id
4410 AND paf.assignment_id = act.assignment_id
4411 AND paf.person_id = ppr.person_id
4412 AND ppr.chunk_number = chunk
4413 AND ppr.payroll_action_id = pactid
4414 AND paf.business_group_id +0 = g_business_group_id
4415 /* restrict to one row per asg. */
4416 /* Comment out the code for performance fix */
4417 --AND paf.effective_start_date =
4418 -- (SELECT max(paf2.effective_start_date)
4419 -- FROM per_assignments_f paf2
4420 -- WHERE paf2.assignment_id = paf.assignment_id)
4421 AND NOT EXISTS (SELECT 1
4422 FROM pay_action_interlocks pai
4423 WHERE pai.locked_action_id = act.assignment_action_id);
4424
4425 BEGIN
4426 hr_utility.set_location('Entering: '||l_proc,1);
4427 --
4428 IF NOT g_asg_creation_cache_populated THEN
4429 OPEN csr_user_entity('X_PAYROLL_ID');
4430 FETCH csr_user_entity INTO g_payroll_id_eid;
4431 CLOSE csr_user_entity;
4432 --
4433 OPEN csr_parameter_info(pactid);
4434 FETCH csr_parameter_info INTO g_payroll_id,
4435 g_tax_ref,
4436 g_start_date,
4437 g_end_date,
4438 g_business_group_id;
4439 CLOSE csr_parameter_info;
4440 --
4441 g_asg_creation_cache_populated := true;
4442 END IF;
4443 --
4444 hr_utility.trace('Payroll ID : ' || g_payroll_id);
4445 hr_utility.trace('Tax Ref : ' || g_tax_ref);
4446 hr_utility.trace('Start : ' || g_start_date);
4447 hr_utility.trace('End : ' || g_end_date);
4448 hr_utility.trace('Bus ID : ' || g_business_group_id);
4449 hr_utility.trace('Chunk : ' || chunk);
4450 hr_utility.trace('EID : ' || g_payroll_id_eid);
4451 IF range_person_on('PAY_GB_P45_EDI') then
4452 --
4453 -- Range Person functionality enabled, use new cursor.
4454 --
4455 hr_utility.set_location(l_proc,20);
4456 FOR rec_asg IN csr_range_assignments LOOP
4457 --
4458 hr_utility.set_location(l_proc,25);
4459 SELECT pay_assignment_actions_s.nextval
4460 INTO l_actid
4461 FROM dual;
4462 --
4463 hr_utility.set_location(l_proc,27);
4464 hr_nonrun_asact.insact(l_actid,rec_asg.assignment_id,
4465 pactid,chunk,NULL);
4466 -- Interlock the archive action
4467 hr_utility.set_location(l_proc,29);
4468 hr_nonrun_asact.insint(l_actid, rec_asg.archive_action);
4469 END LOOP;
4470 --
4471 ELSE
4472 --
4473 -- Range Person functionality not enabled, use original cursor
4474 --
4475 hr_utility.set_location(l_proc,30);
4476 FOR rec_asg IN csr_assignments LOOP
4477 --
4478 SELECT pay_assignment_actions_s.nextval
4479 INTO l_actid
4480 FROM dual;
4481 --
4482 hr_nonrun_asact.insact(l_actid,rec_asg.assignment_id,
4483 pactid,chunk,NULL);
4484 -- Interlock the archive action
4485 hr_nonrun_asact.insint(l_actid, rec_asg.archive_action);
4486 END LOOP;
4487 --
4488 END IF; -- range person check.
4489 --
4490 hr_utility.set_location(' Leaving: '||l_proc,999);
4491 END edi_act_creation;
4492
4493 --
4494 -- Populate P45 form with archived information where appropriate
4495 -- archived info exists
4496 --
4497 procedure pop_term_asg_from_archive(X_ASSIGNMENT_ACTION_ID in number,
4498 X_NI1 in out nocopy varchar2,
4499 X_NI2 in out nocopy varchar2,
4500 X_NI3 in out nocopy varchar2,
4501 X_NI4 in out nocopy varchar2,
4502 X_NI5 in out nocopy varchar2,
4503 X_NI6 in out nocopy varchar2,
4504 X_NI7 in out nocopy varchar2,
4505 X_NI8 in out nocopy varchar2,
4506 X_NI9 in out nocopy varchar2,
4507 X_LAST_NAME in out nocopy varchar2,
4508 X_TITLE in out nocopy varchar2,
4509 X_FIRST_NAME in out nocopy varchar2,
4510 X_DATE_OF_LEAVING_DD in out nocopy varchar2,
4511 X_DATE_OF_LEAVING_MM in out nocopy varchar2,
4512 X_DATE_OF_LEAVING_YY in out nocopy varchar2,
4513 X_TAX_CODE_AT_LEAVING in out nocopy varchar2,
4514 X_WK1_OR_MTH1 in out nocopy varchar2,
4515 X_WEEK_NO in out nocopy varchar2,
4516 X_MONTH_NO in out nocopy varchar2,
4517 X_PAY_TD_POUNDS in out nocopy number,
4518 X_PAY_TD_PENCE in out nocopy number,
4519 X_TAX_TD_POUNDS in out nocopy number,
4520 X_TAX_TD_PENCE in out nocopy number,
4521 X_PAY_IN_EMP_POUNDS in out nocopy number,
4522 X_PAY_IN_EMP_PENCE in out nocopy number,
4523 X_TAX_IN_EMP_POUNDS in out nocopy number,
4524 X_TAX_IN_EMP_PENCE in out nocopy number,
4525 X_ASSIGNMENT_NUMBER in out nocopy varchar2,
4526 X_ORG_NAME in out nocopy varchar2,
4527 X_ADDRESS_LINE1 in out nocopy varchar2,
4528 X_ADDRESS_LINE2 in out nocopy varchar2,
4529 X_ADDRESS_LINE3 in out nocopy varchar2,
4530 X_TOWN_OR_CITY in out nocopy varchar2,
4531 X_REGION_1 in out nocopy varchar2,
4532 X_POSTAL_CODE in out nocopy varchar2,
4533 X_DECEASED_FLAG in out nocopy varchar2,
4534 X_ISSUE_DATE in out nocopy varchar2,
4535 X_TAX_REF_TRANSFER in out nocopy varchar2,
4536 X_STUDENT_LOAN_FLAG in out nocopy varchar2,
4537 X_COUNTRY in out nocopy varchar2,
4538 X_DATE_OF_BIRTH_DD in out nocopy varchar2, /*Start P45 A4 2008/09*/
4539 X_DATE_OF_BIRTH_MM in out nocopy varchar2,
4540 X_DATE_OF_BIRTH_YY in out nocopy varchar2,
4541 X_SEX_M in out nocopy varchar2,
4542 X_SEX_F in out nocopy varchar2) /*End P45 A4 2008/09*/
4543 is
4544 cursor cur_get_asg_archive_items is
4545 select nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),1,1),' ') NINO1,
4546 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),2,1),' ') NINO2,
4547 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),3,1),' ') NINO3,
4548 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),4,1),' ') NINO4,
4549 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),5,1),' ') NINO5,
4550 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),6,1),' ') NINO6,
4551 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),7,1),' ') NINO7,
4552 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),8,1),' ') NINO8,
4553 nvl(substr(max(decode(fue.user_entity_name,'X_NATIONAL_INSURANCE_NUMBER',fai.VALUE)),9,1),' ') NINO9,
4554 nvl(max(decode(fue.user_entity_name,'X_LAST_NAME',fai.VALUE)),' ') LAST_NAME,
4555 nvl(max(decode(fue.user_entity_name,'X_TITLE',fai.VALUE)),' ') TITLE,
4556 nvl(max(decode(fue.user_entity_name,'X_FIRST_NAME',fai.VALUE)),' ') FIRST_NAME,
4557 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,
4558 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,
4559 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,
4560 nvl(max(decode(fue.user_entity_name,'X_TAX_CODE',fai.VALUE)),' ') TAX_CODE,
4561 nvl(max(decode(fue.user_entity_name,'X_W1_M1_INDICATOR',fai.VALUE)),' ') W1_M1_IND,
4562 nvl(max(decode(fue.user_entity_name,'X_WEEK_NUMBER',fai.VALUE)),' ') WEEK_NO,
4563 nvl(max(decode(fue.user_entity_name,'X_MONTH_NUMBER',fai.VALUE)),' ') MONTH_NO,
4564 trunc(nvl(max(decode(fue.user_entity_name,'X_TAXABLE_PAY',fai.VALUE)),0)) PAY_TD_POUNDS,
4565 mod(nvl((max(decode(fue.user_entity_name,'X_TAXABLE_PAY',fai.VALUE))*100),0),100) PAY_TD_PENCE,
4566 trunc(nvl(max(decode(fue.user_entity_name,'X_TAX_PAID',fai.VALUE)),0)) TAX_TD_POUNDS,
4567 mod(nvl((max(decode(fue.user_entity_name,'X_TAX_PAID',fai.VALUE))*100),0),100) TAX_TD_PENCE,
4568 trunc(nvl(max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',fai.value)),0)) PREV_PAY_IN_POUNDS,
4569 mod(nvl((max(decode(fue.user_entity_name,'X_PREVIOUS_TAXABLE_PAY',fai.value))*100),0),100) PREV_PAY_IN_PENCE,
4570 trunc(nvl(max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',fai.value)),0)) PREV_TAX_IN_POUNDS,
4571 mod(nvl((max(decode(fue.user_entity_name,'X_PREVIOUS_TAX_PAID',fai.value))*100),0),100) PREV_TAX_IN_PENCE,
4572 upper(nvl(max(decode(fue.user_entity_name,'X_ASSIGNMENT_NUMBER',fai.VALUE)),' ')) ASSIGNMENT_NUMBER,
4573 upper(nvl(max(decode(fue.user_entity_name,'X_ORGANIZATION_NAME',fai.VALUE)),' ')) ORGANIZATION_NAME,
4574 upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE1',fai.VALUE)),' ')) ADDRESS_LINE1,
4575 upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE2',fai.VALUE)),' ')) ADDRESS_LINE2,
4576 upper(nvl(max(decode(fue.user_entity_name,'X_ADDRESS_LINE3',fai.VALUE)),' ')) ADDRESS_LINE3,
4577 upper(nvl(max(decode(fue.user_entity_name,'X_TOWN_OR_CITY',fai.VALUE)),' ')) TOWN_OR_CITY,
4578 upper(nvl(max(decode(fue.user_entity_name,'X_COUNTY',fai.VALUE)),' ')) COUNTY,
4579 upper(nvl(max(decode(fue.user_entity_name,'X_POSTAL_CODE',fai.VALUE)),' ')) POSTAL_CODE,
4580 upper(nvl(max(decode(fue.user_entity_name,'X_DECEASED_FLAG',fai.VALUE)),' ')) DECEASED_FLAG,
4581 nvl(max(decode(fue.user_entity_name,'X_ISSUE_DATE',fai.VALUE)),' ') ISSUE_DATE,
4582 upper(nvl(max(decode(fue.user_entity_name,'X_TAX_REF_TRANSFER',fai.VALUE)),' ')) TAX_REF_TRANSFER,
4583 upper(nvl(max(decode(fue.user_entity_name,'X_STUDENT_LOAN_FLAG',fai.VALUE)),' ')) STUDENT_LOAN_FLAG,
4584 upper(nvl(max(decode(fue.user_entity_name,'X_COUNTRY',fai.VALUE)),' ')) COUNTRY,
4585 /*P45 A4 2008/09*/
4586 nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),1,2),' ') DATE_OF_BIRTH_DD,
4587 nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),4,2),' ') DATE_OF_BIRTH_MM,
4588 nvl(substr(to_char(fnd_date.canonical_to_date(max(decode(fue.user_entity_name,'X_DATE_OF_BIRTH',fai.VALUE))),'DD-MM-YYYY'),7,4),' ') DATE_OF_BIRTH_YY,
4589 nvl(max (decode(fue.user_entity_name,'X_SEX', substr(fai.value,1,1))),' ') SEX
4590 /*P45 A4 2008/09*/
4591 from ff_archive_items fai,
4592 ff_user_entities fue
4593 where x_assignment_action_id = fai.context1
4594 and fai.archive_type <>'PA'
4595 and fai.user_entity_id = fue.user_entity_id;
4596 --
4597 l_cur_get_asg_archive_items cur_get_asg_archive_items%ROWTYPE;
4598 -- Bug 8370481 : remvoed the number precision
4599 l_pay_in_emp number;
4600 l_tax_in_emp number;
4601 l_prev_pay number;
4602 l_prev_tax number;
4603 l_total_pay_pounds number;
4604 l_total_pay_pence number;
4605 l_total_tax_pounds number;
4606 l_total_tax_pence number;
4607 --
4608 BEGIN
4609 OPEN cur_get_asg_archive_items;
4610 FETCH cur_get_asg_archive_items into l_cur_get_asg_archive_items;
4611 IF cur_get_asg_archive_items%NOTFOUND THEN
4612 null;
4613 ELSE
4614 l_pay_in_emp := (l_cur_get_asg_archive_items.pay_td_pounds + (l_cur_get_asg_archive_items.pay_td_pence/100));
4615 hr_utility.trace('l_pay_in_emp : '||to_char(l_pay_in_emp));
4616 l_tax_in_emp := (l_cur_get_asg_archive_items.tax_td_pounds + (l_cur_get_asg_archive_items.tax_td_pence/100));
4617 hr_utility.trace('l_tax_in_emp : '||to_char(l_tax_in_emp));
4618 l_prev_pay := (l_cur_get_asg_archive_items.prev_pay_in_pounds + (l_cur_get_asg_archive_items.prev_pay_in_pence/100));
4619 hr_utility.trace('l_prev_pay : '||to_char(l_prev_pay));
4620 l_prev_tax := (l_cur_get_asg_archive_items.prev_tax_in_pounds + (l_cur_get_asg_archive_items.prev_tax_in_pence/100));
4621 hr_utility.trace('l_prev_tax : '||to_char(l_prev_tax));
4622 l_total_pay_pounds := trunc(l_pay_in_emp + l_prev_pay);
4623 hr_utility.trace('l_total_pay_pounds : '||to_char(l_total_pay_pounds));
4624 l_total_pay_pence := (mod((l_pay_in_emp + l_prev_pay),1)*100);
4625 hr_utility.trace('l_total_pay_pence : '||to_char(l_total_pay_pence));
4626 l_total_tax_pounds := trunc(l_tax_in_emp + l_prev_tax);
4627 hr_utility.trace('l_total_tax_pounds : '||to_char(l_total_tax_pounds));
4628 l_total_tax_pence := (mod((l_tax_in_emp + l_prev_tax),1)*100);
4629 hr_utility.trace('l_total_tax_pence : '||to_char(l_total_tax_pence));
4630 x_ni1 := l_cur_get_asg_archive_items.nino1;
4631 x_ni2 := l_cur_get_asg_archive_items.nino2;
4632 x_ni3 := l_cur_get_asg_archive_items.nino3;
4633 x_ni4 := l_cur_get_asg_archive_items.nino4;
4634 x_ni5 := l_cur_get_asg_archive_items.nino5;
4635 x_ni6 := l_cur_get_asg_archive_items.nino6;
4636 x_ni7 := l_cur_get_asg_archive_items.nino7;
4637 x_ni8 := l_cur_get_asg_archive_items.nino8;
4638 x_ni9 := l_cur_get_asg_archive_items.nino9;
4639 x_last_name := l_cur_get_asg_archive_items.last_name;
4640 x_title := l_cur_get_asg_archive_items.title;
4641 x_first_name := l_cur_get_asg_archive_items.first_name;
4642 x_date_of_leaving_dd := l_cur_get_asg_archive_items.date_of_leaving_dd;
4643 x_date_of_leaving_mm := l_cur_get_asg_archive_items.date_of_leaving_mm;
4644 x_date_of_leaving_yy := l_cur_get_asg_archive_items.date_of_leaving_yy;
4645 x_tax_code_at_leaving := l_cur_get_asg_archive_items.tax_code;
4646 x_wk1_or_mth1 := l_cur_get_asg_archive_items.w1_m1_ind;
4647 x_week_no := l_cur_get_asg_archive_items.week_no;
4648 x_month_no := l_cur_get_asg_archive_items.month_no;
4649 x_pay_td_pounds := l_total_pay_pounds;
4650 x_pay_td_pence := l_total_pay_pence;
4651 x_tax_td_pounds := l_total_tax_pounds;
4652 x_tax_td_pence := l_total_tax_pence;
4653 x_pay_in_emp_pounds := l_cur_get_asg_archive_items.pay_td_pounds;
4654 x_pay_in_emp_pence := l_cur_get_asg_archive_items.pay_td_pence;
4655 x_tax_in_emp_pounds := l_cur_get_asg_archive_items.tax_td_pounds;
4656 x_tax_in_emp_pence := l_cur_get_asg_archive_items.tax_td_pence;
4657 x_assignment_number := l_cur_get_asg_archive_items.assignment_number;
4658 x_org_name := l_cur_get_asg_archive_items.organization_name;
4659 x_address_line1 := l_cur_get_asg_archive_items.address_line1;
4660 x_address_line2 := l_cur_get_asg_archive_items.address_line2;
4661 x_address_line3 := l_cur_get_asg_archive_items.address_line3;
4662 x_town_or_city := l_cur_get_asg_archive_items.town_or_city;
4663 x_region_1 := l_cur_get_asg_archive_items.county;
4664 x_postal_code := l_cur_get_asg_archive_items.postal_code;
4665 x_deceased_flag := l_cur_get_asg_archive_items.deceased_flag;
4666 x_issue_date := l_cur_get_asg_archive_items.issue_date;
4667 x_tax_ref_transfer := l_cur_get_asg_archive_items.tax_ref_transfer;
4668 x_student_loan_flag := l_cur_get_asg_archive_items.student_loan_flag;
4669 x_country := l_cur_get_asg_archive_items.country;
4670 /* Start P45 A4 2008/09*/
4671 x_date_of_birth_dd := l_cur_get_asg_archive_items.date_of_birth_dd;
4672 x_date_of_birth_mm := l_cur_get_asg_archive_items.date_of_birth_mm;
4673 x_date_of_birth_yy := l_cur_get_asg_archive_items.date_of_birth_yy;
4674 IF UPPER(l_cur_get_asg_archive_items.sex) = 'M' THEN
4675 x_sex_m := 'X';
4676 x_sex_f := ' ';
4677 ELSIF UPPER(l_cur_get_asg_archive_items.sex) = 'F' THEN
4678 x_sex_f:= 'X';
4679 x_sex_m:= ' ';
4680 END IF;
4681 /*End P45 A4 2008/09*/
4682 END IF;
4683 end pop_term_asg_from_archive;
4684
4685 Procedure pop_term_pact_from_archive (X_PAYROLL_ACTION_ID in number,
4686 X_EMPLOYER_NAME in out nocopy varchar2,
4687 X_EMPLOYER_ADDRESS in out nocopy varchar2)
4688 is
4689 cursor cur_get_pact_archive_items is
4690 select upper(nvl(max(decode(fue.user_entity_name,'X_EMPLOYERS_NAME',fai.VALUE)),null)) EMPLOYERS_NAME,
4691 upper(nvl(max(decode(fue.user_entity_name,'X_EMPLOYERS_ADDRESS_LINE',fai.VALUE)),null)) EMPLOYERS_ADDRESS
4692 from ff_archive_item_contexts aic,
4693 ff_archive_items fai,
4694 ff_user_entities fue
4695 where X_PAYROLL_ACTION_ID = fai.context1
4696 and fai.user_entity_id = fue.user_entity_id
4697 and fai.archive_item_id = aic.archive_item_id
4698 and aic.context = '0'
4699 and aic.sequence_no = 1;
4700 --
4701 l_cur_get_pact_archive_items cur_get_pact_archive_items%ROWTYPE;
4702 --
4703 BEGIN
4704 OPEN cur_get_pact_archive_items;
4705 FETCH cur_get_pact_archive_items into l_cur_get_pact_archive_items;
4706 IF cur_get_pact_archive_items%NOTFOUND THEN
4707 null;
4708 ELSE
4709 x_employer_name := l_cur_get_pact_archive_items.employers_name;
4710 x_employer_address := l_cur_get_pact_archive_items.employers_address;
4711 END IF;
4712 END pop_term_pact_from_archive;
4713 --------------------------------------------------------------------------
4714 -- PROCEDURE get_p45_asg_action_id
4715 -- DESCRIPTION Get the P45 Assignment Action id, Issue Date
4716 --------------------------------------------------------------------------
4717 PROCEDURE get_p45_asg_action_id(p_assignment_id in number,
4718 p_assignment_action_id out nocopy number,
4719 p_issue_date out nocopy date,
4720 p_action_sequence out nocopy number
4721 ) IS
4722 --
4723 CURSOR csr_get_p45_action(c_assignment_id NUMBER) IS
4724 SELECT act.assignment_action_id, pact.effective_date, act.action_sequence
4725 FROM pay_assignment_actions act, pay_payroll_actions pact
4726 WHERE act.assignment_id = c_assignment_id
4727 AND act.payroll_action_id = pact.payroll_action_id
4728 AND pact.REPORT_QUALIFIER = 'GB'
4729 AND pact.ACTION_TYPE = 'X'
4730 AND act.action_status = 'C'
4731 AND pact.action_status = 'C' --9473697 When Payoll action is in error status, neither P45 pdf report nor EDI can be produced.
4732 AND report_type = 'P45'
4733 AND report_category = 'P45';
4734
4735 CURSOR csr_transfer_p45(c_assignment_action_id NUMBER) IS
4736 SELECT fai.VALUE
4737 FROM ff_archive_items fai, ff_user_entities fue
4738 WHERE fai.user_entity_id = fue.user_entity_id
4739 AND fue.user_entity_name = 'X_TAX_REF_TRANSFER'
4740 AND fue.legislation_code = 'GB'
4741 AND fue.business_group_id IS NULL
4742 AND fai.context1 = c_assignment_action_id ;
4743
4744 l_transfer_flag ff_archive_items.value%type;
4745 --
4746 BEGIN
4747 --
4748 open csr_get_p45_action(p_assignment_id);
4749 loop
4750 p_assignment_action_id := null;
4751 p_issue_date := null;
4752 p_action_sequence := null;
4753
4754 fetch csr_get_p45_action into p_assignment_action_id, p_issue_date, p_action_sequence;
4755 exit when csr_get_p45_action%notfound;
4756 --
4757 open csr_transfer_p45(p_assignment_action_id);
4758 fetch csr_transfer_p45 into l_transfer_flag;
4759 if csr_transfer_p45%notfound or nvl(l_transfer_flag,'N') = 'N' then
4760 close csr_transfer_p45;
4761 exit;
4762 end if;
4763 close csr_transfer_p45;
4764 --
4765 end loop;
4766 close csr_get_p45_action;
4767 --
4768
4769 END get_p45_asg_action_id;
4770 --------------------------------------------------------------------------
4771 -- FUNCTION get_p45_eit_manual_issue_dt
4772 -- DESCRIPTION Get the P45 Manual Issue date from Extra Info. table
4773 --------------------------------------------------------------------------
4774 FUNCTION get_p45_eit_manual_issue_dt(p_assignment_id in number) RETURN DATE IS
4775 --
4776 l_manual_issue_date date;
4777 CURSOR csr_get_p45_eit_dtls(c_assignment_id NUMBER) IS
4778 select fnd_date.canonical_to_date(aei_information3)
4779 from per_assignment_extra_info
4780 where assignment_id = c_assignment_id
4781 and information_type = 'GB_P45';
4782 --
4783 BEGIN
4784 open csr_get_p45_eit_dtls(p_assignment_id);
4785 fetch csr_get_p45_eit_dtls into l_manual_issue_date;
4786 if csr_get_p45_eit_dtls%NOTFOUND then
4787 l_manual_issue_date := null;
4788 end if;
4789 close csr_get_p45_eit_dtls;
4790 --
4791 RETURN l_manual_issue_date;
4792 END get_p45_eit_manual_issue_dt;
4793 --------------------------------------------------------------------------
4794 -- PROCEDURE get_p45_agg_asg_action_id
4795 -- DESCRIPTION Get the Aggregated Assignment Id, Assignment Action id,
4796 -- Final Payment Date for which the P45 been issued
4797 --------------------------------------------------------------------------
4798 PROCEDURE get_p45_agg_asg_action_id(p_assignment_id in number,
4799 p_agg_assignment_id out nocopy number,
4800 p_final_payment_date out nocopy date,
4801 p_p45_issue_date out nocopy date,
4802 p_p45_agg_asg_action_id out nocopy number
4803 ) IS
4804 --
4805 Cursor csr_get_all_asg(c_assignment_id NUMBER) IS
4806 select distinct asg1.assignment_id, asg1.person_id
4807 from per_all_assignments_f asg1, per_all_assignments_f asg2
4808 where asg2.assignment_id = c_assignment_id
4809 and asg2.person_id = asg1.person_id
4810 and asg1.assignment_id <> p_assignment_id;
4811
4812 /*
4813 Cursor csr_get_final_payment(c_assignment_id NUMBER, c_asg_action_id NUMBER, c_action_sequence NUMBER) IS
4814 select pact1.payroll_action_id, pact1.effective_date final_payment_date
4815 from FF_ARCHIVE_ITEMS ai,
4816 ff_user_entities ue,
4817 pay_payroll_actions pact1
4818 WHERE ue.user_entity_name in ('X_MONTH_NUMBER', 'X_WEEK_NUMBER') -- for the weekly frequency (and multiples)
4819 AND ue.legislation_code = 'GB'
4820 AND ue.business_group_id IS NULL
4821 and ue.user_entity_id = ai.user_entity_id
4822 and ai.archive_type = 'AAP'
4823 and ai.context1 = c_asg_action_id
4824 and pact1.payroll_action_id =
4825 (
4826 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
4827 from pay_assignment_actions act,
4828 pay_payroll_actions pact,
4829 per_time_periods ptp -- moved to subquery to make sure latest payroll action having period num matching the archive is fetched
4830 where pact.payroll_action_id = act.payroll_action_id
4831 and pact.action_type in ('Q', 'R', 'B', 'I', 'V')
4832 and act.assignment_id = c_assignment_id
4833 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
4834 -- and act.SOURCE_ACTION_ID is null -- no need to check for source_action_id being null to cover upgrade from R11 cases
4835 and ai.value = to_char(ptp.period_num)
4836 and pact.time_period_id = ptp.time_period_id
4837 );
4838 */
4839
4840 Cursor csr_get_paye_ref(c_assignment_id NUMBER, c_effective_date DATE) IS
4841 SELECT flex.segment1 paye_ref, paaf.period_of_service_id
4842 FROM per_all_assignments_f paaf,
4843 pay_all_payrolls_f papf,
4844 hr_soft_coding_keyflex flex
4845 WHERE paaf.assignment_id = c_assignment_id
4846 AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
4847 AND paaf.payroll_id = papf.payroll_id
4848 AND c_effective_date BETWEEN papf.effective_start_date and papf.effective_end_date
4849 AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id;
4850
4851 cursor csr_agg_asg_active_period(c_assignment_id number,
4852 c_agg_assignment_id number,
4853 c_tax_ref in varchar2,
4854 c_effective_date date
4855 ) is
4856 select 1
4857 from per_all_assignments_f a,
4858 per_assignment_status_types past
4859 where a.assignment_status_type_id = past.assignment_status_type_id
4860 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
4861 and a.assignment_id = c_assignment_id
4862 and a.effective_start_date <= pay_gb_eoy_archive.get_agg_active_end(c_agg_assignment_id, c_tax_ref, c_effective_date)
4863 and a.effective_end_date >= pay_gb_eoy_archive.get_agg_active_start(c_agg_assignment_id, c_tax_ref, c_effective_date)
4864 ;
4865
4866 cursor csr_aggr_paye_flag (c_person_id number,
4867 c_effective_date date) is
4868 select per_information10
4869 from per_all_people_f
4870 where person_id = c_person_id
4871 and c_effective_date between
4872 effective_start_date and effective_end_date;
4873 --
4874 -- to fetch the last active/susp status date for the given assignment
4875 --
4876 cursor csr_asg_last_active_date(c_assignment_id number) is
4877 select max(effective_end_date)
4878 from per_all_assignments_f a,
4879 per_assignment_status_types past
4880 where a.assignment_id = c_assignment_id
4881 and a.assignment_status_type_id = past.assignment_status_type_id
4882 and past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
4883
4884 --
4885 -- to fetch the earliest aggregation start date from the final payment date.
4886 --
4887 cursor csr_latest_aggr_start_date(c_person_id number, c_effective_date date) is
4888 select max(effective_end_date) + 1
4889 from per_all_people_f
4890 where person_id = c_person_id
4891 and nvl(per_information10,'N') = 'N'
4892 and effective_end_date < c_effective_date;
4893
4894 --
4895 -- to check whether the given assignment present between
4896 -- the earliest aggregation start date and final payment date
4897 --
4898 cursor csr_asg_present_status(c_assignment_id number, c_start_date date, c_end_date date) is
4899 select 1
4900 from per_all_assignments_f a
4901 where a.assignment_id = c_assignment_id
4902 and a.effective_end_date >= c_start_date
4903 and a.effective_start_date <= c_end_date;
4904
4905 -- Start of BUG 5671777-2
4906 --
4907 -- fetch P45 for another assignment that included the given assignment.
4908 --
4909 cursor csr_get_p45_another_asg(c_assignment_id number,c_assignment_action_id number) is
4910 select 1
4911 from ff_archive_items fai,
4912 ff_user_entities fue
4913 where fai.user_entity_id = fue.user_entity_id
4914 and fue.user_entity_name = 'X_P45_INCLUDED_ASSIGNMENT'
4915 and fai.context1 = c_assignment_action_id
4916 and fai.value = to_char(c_assignment_id);
4917
4918 -- End of BUG 5671777-2
4919
4920 l_latest_aggr_start_date date;
4921 l_asg_last_active_date date;
4922 l_agg_asg_last_active_date date;
4923 --
4924 l_assignment_action_id NUMBER;
4925 l_issue_date DATE;
4926 l_locked_action_id NUMBER;
4927 l_effective_date DATE;
4928 l_agg_paye_flag per_all_people_f.per_information10%type;
4929 l_found BOOLEAN;
4930 l_dummy NUMBER;
4931
4932 l_agg_paye_reference hr_soft_coding_keyflex.segment1%type;
4933 l_paye_reference hr_soft_coding_keyflex.segment1%type;
4934 l_period_of_service_id number;
4935 l_agg_period_of_service_id number;
4936 l_action_sequence number;
4937
4938 l_proc CONSTANT VARCHAR2(100):= g_package||'get_p45_agg_asg_action_id';
4939 BEGIN
4940 hr_utility.set_location('Entering: '||l_proc,1);
4941 p_agg_assignment_id := null;
4942 p_final_payment_date := null;
4943 p_p45_agg_asg_action_id := null;
4944 p_p45_issue_date := null;
4945
4946 hr_utility.trace('g_p45_inc_assignment ' || g_p45_inc_assignment);
4947
4948 hr_utility.set_location(l_proc,10);
4949 -- Start of BUG 5671777-2
4950 --
4951 -- fetch P45 for another assignment that included the given assignment.
4952 --
4953 for r_rec in csr_get_all_asg(p_assignment_id) loop
4954 l_assignment_action_id := null;
4955 l_issue_date := null;
4956 hr_utility.set_location(l_proc,14);
4957 --
4958 -- fetch the p45 issue date and assignment action id
4959 --
4960 get_p45_asg_action_id(p_assignment_id => r_rec.assignment_id,
4961 p_assignment_action_id => l_assignment_action_id,
4962 p_issue_date => l_issue_date,
4963 p_action_sequence => l_action_sequence);
4964
4965 if l_assignment_action_id is not null then
4966 hr_utility.set_location(l_proc,17);
4967
4968 open csr_get_p45_another_asg(p_assignment_id,l_assignment_action_id);
4969 fetch csr_get_p45_another_asg into l_dummy;
4970 l_found := csr_get_p45_another_asg%found;
4971 close csr_get_p45_another_asg;
4972
4973 if l_found then
4974 p_agg_assignment_id := r_rec.assignment_id;
4975 p_final_payment_date := null;
4976 p_p45_agg_asg_action_id := l_assignment_action_id;
4977 p_p45_issue_date := l_issue_date;
4978 EXIT;
4979 end if;
4980 end if;
4981 end loop;
4982
4983 if not l_found then
4984 -- End of Bug 5617777-2
4985 for r_rec in csr_get_all_asg(p_assignment_id) loop
4986 l_assignment_action_id := null;
4987 l_issue_date := null;
4988 hr_utility.set_location(l_proc,20);
4989 --
4990 -- fetch the p45 issue date and assignment action id
4991 --
4992 get_p45_asg_action_id(p_assignment_id => r_rec.assignment_id,
4993 p_assignment_action_id => l_assignment_action_id,
4994 p_issue_date => l_issue_date,
4995 p_action_sequence => l_action_sequence);
4996 --
4997
4998 if l_assignment_action_id is not null then
4999 hr_utility.set_location(l_proc,30);
5000 --
5001 /*
5002 -- get the final payment date/effective_date
5003 --
5004 open csr_get_final_payment(r_rec.assignment_id, l_assignment_action_id, l_action_sequence);
5005 fetch csr_get_final_payment into l_locked_action_id, l_effective_date;
5006 l_found := csr_get_final_payment%found;
5007 close csr_get_final_payment;
5008 --
5009
5010 if l_found then
5011 hr_utility.set_location(l_proc,40);
5012 */
5013 --
5014 -- fetch the last active/susp status of the aggregated assignemnt
5015 --
5016 open csr_asg_last_active_date(r_rec.assignment_id);
5017 fetch csr_asg_last_active_date into l_agg_asg_last_active_date;
5018 close csr_asg_last_active_date;
5019
5020 --
5021 -- getting the PAYE Aggregate flag for the person on last active/susp date of the agg. asg
5022 --
5023 open csr_aggr_paye_flag(r_rec.person_id, l_agg_asg_last_active_date);
5024 fetch csr_aggr_paye_flag into l_agg_paye_flag;
5025 close csr_aggr_paye_flag;
5026 --
5027
5028 if nvl(l_agg_paye_flag, 'X') = 'Y' then
5029 hr_utility.set_location(l_proc,50);
5030
5031 --
5032 -- fetch the Tax reference for the agg. asg. on the last active/susp status date of the asg
5033 --
5034 open csr_get_paye_ref(r_rec.assignment_id, l_agg_asg_last_active_date);
5035 fetch csr_get_paye_ref into l_agg_paye_reference, l_agg_period_of_service_id;
5036 l_found := csr_get_paye_ref%found;
5037 close csr_get_paye_ref;
5038 --
5039
5040 if l_found then
5041 hr_utility.set_location(l_proc,60);
5042 --
5043 -- fetch the last active/susp status of the given assignemnt
5044 --
5045 open csr_asg_last_active_date(p_assignment_id);
5046 fetch csr_asg_last_active_date into l_asg_last_active_date;
5047 close csr_asg_last_active_date;
5048
5049 --
5050 -- fetch the Tax reference, period of service id for the given asg. on
5051 -- the last active/susp status of the assignemnt
5052 --
5053 open csr_get_paye_ref(p_assignment_id, l_asg_last_active_date);
5054 fetch csr_get_paye_ref into l_paye_reference, l_period_of_service_id;
5055 l_found := csr_get_paye_ref%found;
5056 close csr_get_paye_ref;
5057 --
5058
5059 if l_found and l_paye_reference = l_agg_paye_reference and
5060 l_period_of_service_id = l_agg_period_of_service_id then
5061
5062 hr_utility.set_location(l_proc,70);
5063
5064 --
5065 -- check for both assignments share aggregated active period of employment or not
5066 --
5067 open csr_agg_asg_active_period(p_assignment_id, r_rec.assignment_id,
5068 l_agg_paye_reference, l_agg_asg_last_active_date);
5069 fetch csr_agg_asg_active_period into l_dummy;
5070 l_found := csr_agg_asg_active_period%found;
5071 close csr_agg_asg_active_period;
5072 --
5073
5074 if l_found then
5075 hr_utility.set_location(l_proc,80);
5076 --
5077 -- to fetch the latest aggregation start date near to final payment date.
5078 --
5079 open csr_latest_aggr_start_date(r_rec.person_id, l_agg_asg_last_active_date);
5080 fetch csr_latest_aggr_start_date into l_latest_aggr_start_date;
5081 close csr_latest_aggr_start_date;
5082 --
5083
5084 if l_latest_aggr_start_date is not null then
5085 hr_utility.set_location(l_proc,90);
5086 --
5087 -- to check whther the given assignment present between
5088 -- the earliest aggregation start date and final payment date
5089 --
5090 open csr_asg_present_status(p_assignment_id, l_latest_aggr_start_date, l_agg_asg_last_active_date);
5091 fetch csr_asg_present_status into l_dummy;
5092 l_found := csr_asg_present_status%found;
5093 close csr_asg_present_status;
5094 end if;
5095
5096 if l_found then
5097 hr_utility.set_location(l_proc,100);
5098 --
5099 -- returning the final payment date, asg. action id and agg.asg. id
5100 --
5101 p_agg_assignment_id := r_rec.assignment_id;
5102 p_final_payment_date := null;
5103 p_p45_agg_asg_action_id := l_assignment_action_id;
5104 p_p45_issue_date := l_issue_date;
5105 --
5106
5107 exit; -- exiting the loop
5108 end if;
5109 end if;
5110 end if;
5111 end if;
5112 end if;
5113 --end if;
5114 end if;
5115 end loop;
5116 end if; -- l_found
5117 hr_utility.set_location('Leaving: '||l_proc,110);
5118 --
5119 END get_p45_agg_asg_action_id;
5120
5121 -- Bug 7028893.Added function PAYE_RETURN_P45_ISSUED_FLAG.
5122 --------------------------------------------------------------------------
5123 -- FUNCTION paye_return_p45_issued_flag
5124 -- DESCRIPTION return the P45 issued status for the given assignment
5125 --------------------------------------------------------------------------
5126
5127 FUNCTION paye_return_p45_issued_flag(p_assignment_id in number,p_payroll_action_id in number) RETURN VARCHAR2 IS
5128 --
5129
5130 -- Cursor to fetch effective date (date earned + off set)
5131 Cursor csr_get_effective_date(c_payroll_action_id number) is
5132 select effective_date
5133 from pay_payroll_actions
5134 where payroll_action_id = c_payroll_action_id;
5135
5136 l_assignment_action_id number;
5137 l_agg_assignment_id number;
5138 l_issue_date date;
5139 l_final_payment_date date;
5140 l_p45_agg_asg_action_id number;
5141 l_action_sequence number;
5142 l_return_p45_issued_flag VARCHAR2(1);
5143 l_proc CONSTANT VARCHAR2(100):= g_package||'paye_return_p45_issued_flag';
5144 l_effective_date date;
5145 --
5146 BEGIN
5147 hr_utility.set_location('Entering: '||l_proc,10);
5148 --
5149 -- check for the p45 issue date and assignment action id through P45 process
5150 --
5151 get_p45_asg_action_id(p_assignment_id => p_assignment_id,
5152 p_assignment_action_id => l_assignment_action_id,
5153 p_issue_date => l_issue_date,
5154 p_action_sequence => l_action_sequence);
5155 --
5156
5157 hr_utility.set_location(l_proc,20);
5158 if l_assignment_action_id is null then
5159 hr_utility.set_location(l_proc,30);
5160
5161 --
5162 -- check for the P45 manualy issued or not
5163 --
5164 l_issue_date := get_p45_eit_manual_issue_dt(p_assignment_id);
5165 if l_issue_date is null then
5166 hr_utility.set_location(l_proc,40);
5167 --
5168 -- check for the P45 issued for any of the aggregated assignment
5169 --
5170 get_p45_agg_asg_action_id(p_assignment_id => p_assignment_id,
5171 p_agg_assignment_id => l_agg_assignment_id,
5172 p_final_payment_date => l_final_payment_date,
5173 p_p45_issue_date => l_issue_date,
5174 p_p45_agg_asg_action_id => l_p45_agg_asg_action_id);
5175
5176 if l_agg_assignment_id is null then
5177 hr_utility.set_location(l_proc,50);
5178 l_return_p45_issued_flag := 'N';
5179 end if;
5180 end if;
5181 --
5182
5183 end if;
5184 hr_utility.set_location('Leaving: '||l_proc,60);
5185
5186 -- Fetching effective date for payroll action id
5187 open csr_get_effective_date(p_payroll_action_id);
5188 fetch csr_get_effective_date into l_effective_date;
5189 close csr_get_effective_date;
5190
5191 -- Comparing P45 issue date with effective date (date earned + off set)
5192 if l_effective_date >= l_issue_date then
5193 l_return_p45_issued_flag := 'Y';
5194 else
5195 l_return_p45_issued_flag := 'N';
5196 end if;
5197 return l_return_p45_issued_flag;
5198
5199 END paye_return_p45_issued_flag;
5200
5201 -- Bug 7601088.Added function PAYE_RETURN_P45_ISSUED_FLAG.
5202 --------------------------------------------------------------------------
5203 -- FUNCTION PAYE_SYNC_P45_ISSUED_FLAG
5204 -- DESCRIPTION return the P45 issued status for the given assignment
5205 --------------------------------------------------------------------------
5206
5207 FUNCTION PAYE_SYNC_P45_ISSUED_FLAG(p_assignment_id in number,p_effective_date in date) RETURN VARCHAR2 IS
5208 --
5209
5210 -- Cursor to fetch effective date (date earned + off set)
5211 /* Cursor csr_get_effective_date(c_payroll_action_id number) is
5212 select effective_date
5213 from pay_payroll_actions
5214 where payroll_action_id = c_payroll_action_id; */
5215
5216 l_assignment_action_id number;
5217 l_agg_assignment_id number;
5218 l_issue_date date;
5219 l_final_payment_date date;
5220 l_p45_agg_asg_action_id number;
5221 l_action_sequence number;
5222 l_return_p45_issued_flag VARCHAR2(1);
5223 l_proc CONSTANT VARCHAR2(100):= g_package||'PAYE_SYNC_P45_ISSUED_FLAG';
5224 l_effective_date date;
5225 --
5226 BEGIN
5227 hr_utility.set_location('Entering: '||l_proc,10);
5228 --
5229 -- check for the p45 issue date and assignment action id through P45 process
5230 --
5231 get_p45_asg_action_id(p_assignment_id => p_assignment_id,
5232 p_assignment_action_id => l_assignment_action_id,
5233 p_issue_date => l_issue_date,
5234 p_action_sequence => l_action_sequence);
5235 --
5236
5237 hr_utility.set_location(l_proc,20);
5238 if l_assignment_action_id is null then
5239 hr_utility.set_location(l_proc,30);
5240
5241 --
5242 -- check for the P45 manualy issued or not
5243 --
5244 l_issue_date := get_p45_eit_manual_issue_dt(p_assignment_id);
5245 if l_issue_date is null then
5246 hr_utility.set_location(l_proc,40);
5247 --
5248 -- check for the P45 issued for any of the aggregated assignment
5249 --
5250 get_p45_agg_asg_action_id(p_assignment_id => p_assignment_id,
5251 p_agg_assignment_id => l_agg_assignment_id,
5252 p_final_payment_date => l_final_payment_date,
5253 p_p45_issue_date => l_issue_date,
5254 p_p45_agg_asg_action_id => l_p45_agg_asg_action_id);
5255
5256 if l_agg_assignment_id is null then
5257 hr_utility.set_location(l_proc,50);
5258 l_return_p45_issued_flag := 'N';
5259 end if;
5260 end if;
5261 --
5262
5263 end if;
5264 hr_utility.set_location('Leaving: '||l_proc,60);
5265
5266 -- Fetching effective date for payroll action id
5267 /* open csr_get_effective_date(p_payroll_action_id);
5268 fetch csr_get_effective_date into l_effective_date;
5269 close csr_get_effective_date; */
5270
5271 -- Comparing P45 issue date with effective date (date earned + off set)
5272 if p_effective_date >= l_issue_date then
5273 l_return_p45_issued_flag := 'Y';
5274 else
5275 l_return_p45_issued_flag := 'N';
5276 end if;
5277 return l_return_p45_issued_flag;
5278
5279 END PAYE_SYNC_P45_ISSUED_FLAG;
5280
5281 --------------------------------------------------------------------------
5282 -- FUNCTION return_p45_issued_flag
5283 -- DESCRIPTION return the P45 issued status for the given assignment
5284 --------------------------------------------------------------------------
5285 FUNCTION return_p45_issued_flag(p_assignment_id in number) RETURN VARCHAR2 IS
5286 --
5287 l_assignment_action_id number;
5288 l_agg_assignment_id number;
5289 l_issue_date date;
5290 l_final_payment_date date;
5291 l_p45_agg_asg_action_id number;
5292 l_action_sequence number;
5293 l_return_p45_issued_flag VARCHAR2(1) := 'Y';
5294 l_proc CONSTANT VARCHAR2(100):= g_package||'return_p45_issued_flag';
5295 --
5296 BEGIN
5297 hr_utility.set_location('Entering: '||l_proc,10);
5298 --
5299 -- check for the p45 issue date and assignment action id through P45 process
5300 --
5301 get_p45_asg_action_id(p_assignment_id => p_assignment_id,
5302 p_assignment_action_id => l_assignment_action_id,
5303 p_issue_date => l_issue_date,
5304 p_action_sequence => l_action_sequence);
5305 --
5306
5307 hr_utility.set_location(l_proc,20);
5308 if l_assignment_action_id is null then
5309 hr_utility.set_location(l_proc,30);
5310
5311 --
5312 -- check for the P45 manualy issued or not
5313 --
5314 if get_p45_eit_manual_issue_dt(p_assignment_id) is null then
5315 hr_utility.set_location(l_proc,40);
5316 --
5317 -- check for the P45 issued for any of the aggregated assignment
5318 --
5319 get_p45_agg_asg_action_id(p_assignment_id => p_assignment_id,
5320 p_agg_assignment_id => l_agg_assignment_id,
5321 p_final_payment_date => l_final_payment_date,
5322 p_p45_issue_date => l_issue_date,
5323 p_p45_agg_asg_action_id => l_p45_agg_asg_action_id);
5324
5325 if l_agg_assignment_id is null then
5326 hr_utility.set_location(l_proc,50);
5327 l_return_p45_issued_flag := 'N';
5328 end if;
5329 end if;
5330 --
5331
5332 end if;
5333 hr_utility.set_location('Leaving: '||l_proc,60);
5334 return l_return_p45_issued_flag;
5335 --
5336 END return_p45_issued_flag;
5337
5338 END pay_p45_pkg;
[Home] [Help]