[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_P45_ARCHIVE
Source
1 PACKAGE BODY pay_ie_p45_archive AS
2 /* $Header: pyiep45.pkb 120.34.12010000.1 2008/07/27 22:50:34 appldev ship $ */
3 /*
4 **
5 ** Copyright (C) 1999 Oracle Corporation
6 ** All Rights Reserved
7 **
8 ** IE P45 Archive Package
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** -----------+--------+---------+------------------------------
15 ** 05 APR 2002 abhaduri N/A Created
16 ** 10 JUN 2002 viviswan 2268282 XML Report generator
17 ** procedure added.
18 ** 14 JUN 2002 Kavenkat Modified the address information fields
19 ** 09-JUL-2002 Kavenkat 2448728 Passed on the prepayment assignment action id
20 ** 2450336 to pay_ie_p45_archive.process_balance to archive
21 ** the balance 'Total Pay'.
22 ** 10-JUL-2002 viviswan 2452564 Fixed EMEA BALANCE DEFINITION context getting
23 ** 2450279 archived for each chunk created by the PYUGEN
24 ** process.
25 ** 12-JUL-2002 viviswan Modified logic for setting Non-Cumm Flag
26 ** depending on Calculation Options
27 ** 17-JUL-2002 viviswan 2466382 open cursor csr_check_def_exists_info closed
28 ** 18-JUL-2002 viviswan 2466773 Total Pay to be claculated as IE Taxabale Pay_YTD
29 ** value and not Total Pay_PAYMENT.
30 ** Correctd Date format. Added additional joins
31 ** for XML Report Query.
32 ** 19-JUL-2002 viviswan 2468773 Moved the EMEA BALANCE DEFINITION archive code
33 ** from archinit to range_cursor to avoid the same
34 ** context getting archived multiple times.
35 ** 08-AUG-2002 viviswan 2452531 Modified the logic for archiving EMEA PAYROLL INFO
36 ** 2499841 context for XML Reporting
37 ** Modified to archive the RUN values in case of
38 ** Supplementary Run
39 ** 09-AUG-2002 gbutler 11.5.8 Performance fix - added ORDERED hint
40 ** to csr_prepaid_assignments in action_creation
41 ** to resolve merge cartesian join in NOT EXISTS
42 ** subquery
43 ** 29-OCT-2002 smrobins 2567139 Mutliplied perion_num by pay_periods per
44 ** period, where pay_period_per_period
45 ** attributed to associated payroll is
46 ** greater than 1. Also added mid period
47 ** functionality to derive mid period
48 ** leaver.
49 ** 05-NOV-2002 smrobins Changes period_num cursor to identify
50 ** assignment uniquely by effective start and
51 ** effective end dates
52 ** 05-DEC-2002 viviswan 2643489 Performance changes and nocopy changes.
53 ** 20-DEC-2002 smrobins Changes to deriving weeks at class A
54 ** following changes to prsi balance
55 ** structure
56 ** 15-MAY-2003 nsugavan 2943335 Changed PROCEDURE setup_standard_balance_table
57 ** to archive balance, IE Taxable Social Benefit
58 ** instead of balance, IE Benefit Amount
59 ** Also, commented the cursor cur_cal_option
60 ** and used IE Taxable Social Benefit balance value
61 ** instead to check presence of benefit amount.
62 ** 12-AUG-2003 npershad 3079945 Changed the cursor csr_iea_weeks
63 ** to derive correct insurable weeks at class A
64 ** 12-SEP-2003 npershad 3079945 Commented cusor crs_iea_weeks and added
65 ** call to pay_balance_pkg.get_value to derive
66 ** correct insurable weeks at class A in cursor
67 ** Cur_Act_Contexts
68 ** 07-APR-2004 ssekhar 3436737 Added code to support K and M Employee and
69 ** Employer figures when a severance payment
70 ** exists
71 ** 12-APR-2004 npershad 3567562 Modified the cursors csr_get_org_tax_address, csr_payroll_ifo
72 ** to restrict the details fetched based on the PAYE Reference.
73 ** Added a new procedure get_paye_reference to get the PAYE reference
74 ** attributed to payrolls in a consolidation set.
75 ** 07-JUN-2004 ssekhar 3669639 Changed the code so that l_prsi_cat is now
76 ** previous classes concatenated with K or M
77 ** 28-JUN-2004 ssekhar 3669639 Changed the space between the classes so that
78 ** the display is uniform
79 ** 24-AUG-2004 alikhar 3817846 Changed the cursor cur_child_pay_action to get the
80 ** 115.26 correct maximum assignment action id
81 ** 05-OCT-2004 aashokan 115.27 Fixed Case issue with cursor cur_defined_balance
82 ** 06-NOV-2004 aashokan 115.28 Bug 3986018 - Added nvl in get_bal_arch_value function
83 ** Bug 3991416 - Added period,frequency and date
84 ** earned check to fetch single record
85 ** 06-NOV-2004 npershad 115.29 Bug 3986250 - Modified the cursor Cur_Act_Contexts
86 ** to report correct total insurable weeks for class or subclass 'A'.
87 ** 12-NOV-2004 Kthampan 115.30 Bug 4001524 - Pass g_archive_end_date to the cursor
88 ** cur_child_pay_action instead of the effective_date (session date)
89 ** to report correct total insurable weeks for class or subclass 'A'.
90 ** 17-NOV-2004 alogue 115.31 Bug 4011305 - Added hints to csr_prepaid_assignments in
91 ** action_creation to force use of optimum plan.
92 ** 20-NOV-2004 aashokan 115.32 Bug 4016508 - Tax credit and cut off to be 0 if tax basis is
93 ** emergency or emergency no pps.
94 ** 22-NOV-2004 KThampan 115.33 Bug 4001524 - Modified cursor csr_prepaid_assignments to
95 ** use archive start date and archive end date
96 ** 09-DEC-2004 aashokan 115.34 Bug 4050372 - Added new cursor to fetch tax basis on termination
97 ** date
98 ** 10-jan-2005 npershad 115.35 Bug 4108423 - Modified the cursor Cur_Act_Contexts
99 ** to report correct total insurable weeks for class or subclass 'A'.
100 ** 21-feb-2005 aashokan 115.36 Bug 4193738 - Modified cursor csr_prepaid_assignments to fetch only those
101 ** records for which pre payment is run between a given period.
102 ** 28-feb-2005 aashokan 115.37 Bug 4208273 - Modified subquery of cursor csr_prepaid_assignments
103 ** 28-feb-2005 aashokan 115.38 Added act2.action_status='C' in subquery of cursor csr_prepaid_assignments
104 ** 28-APR-2005 rrajaman 115.39 Removed pay_element_types_f join from cursor cur_non_cum_tax
105 ** for performance bug 4315023
106 ** 24-MAY-2005 sgajula 115.40 Changed to refer new Information type IE_EMPLOYER_INFO to accomodate changes
107 ** for Employer Migration
108 ** 16-JUN-2005 alikhar 115.41 Bug 4437249: Changed the cursor csr_get_arc_bal_value to get the balance
109 ** value for balance name with length more than 30 chars.
110 ** 30-JUN-2005 sgajula 115.42 Initialized g_archive_end_date in archinit to support Retry Option.
111 ** 06-JUL-2005 sgajula 115.43 Moved 'PA' archive code from range_cursor to archive_deinit,handled
112 ** case of zero pay but non-zero PAYE,avoid data corruption by locking,
113 ** and proper archiving of EMEA PAYROLL INFO
114 ** 22-JUL-2005 sgajula 115.44 Called setup_standard_balance_table in archive_deinit(4508661)
115 ** 26-SEP-2005 rrajaman 115.45 Added IE_EXEMPTION tax_basis condition to cursor cur_non_cum_tax(4619038).
116 ** 29-SEP-2005 sgajula 115.46 Changed the deceased attribute tags(4641660)
117 ** 24-OCT-2005 sgajula 115.47 Modified for 2006 Changes.
118 ** 26-OCT-2005 sgajula 115.48 Changed NOTFOUND condition for cur_child_pay_action
119 ** 07-Nov-2005 vikgupta 115.49 Modified the formversion and xml file version to 3 and 3.0(4721955)
120 ** 08-Nov-2005 vikgupta 115.50 revet the xml file version from 3.0 to 1.0
121 ** 08-Nov-2005 vikgupta 115.51 Warning message to be raised if PPSN and address are missing. Also
122 ** made noncumulative attribute values to true/false instead of Y/N.
123 ** 09-Nov-2005 vikgupta 115.52 change the attribute firstname to firstnames.
124 ** 09-Nov-2005 vikgupta 115.53 restrict the total prsi classes to be shown is 4. bug (4724788)
125 ** 23-Dec-2005 sgajula 115.54 Fixed Period Number issues with Offset Payrolls (4906850)
126 ** 06-Jan-2006 sgajula 115.55 Enabled the Start Date Parameter.
127 ** 02-Feb-2006 rbhardwa 115.56 Changed supplementary P45 to report PRSI Class A.(5015438)
128 ** 06-Feb-2006 rbhardwa 115.57 Changed supplementary P45 to report insurable weeks for PRSI Class A correctly.(5015438)
129 ** 14-Feb-2006 rbhardwa 115.58 Changed supplementary P45 to not report Class A if class A insurable weeks are zero.
130 ** (5015438).
131 ** 14-Feb-2006 sgajula 115.59 Changed get_arc_bal_value to improve the performance. Also changed to support view
132 ** changes to improve the performance(5005788)
133 ** 15-Feb-2006 sgajula 115.60 changed cur_p45_paye_prsi_details. fetched employer_prsi(5005788)
134 ** 21-Feb-2006 sgajula 115.61 removed csr_all_payroll_info.Used csr_payroll_info to archive
135 ** EMEA BALANCE DEFINITION(4771780)
136 ** 3-Mar-2006 sgajula 115.62 Changed to support user assignment status (5073577)
137 ** 8-Mar-2006 rbhardwa 115.63 Added payroll parameter to the p45 report generator process.(5059862)
138 ** 31-Mar-2006 sgajula 115.64 Changed to fetch PAYE Details from Run Results(5128377)
139 ** 31-Jul-2006 vikgupta 115.65 Bug 5401393 - added abs for thistax in generate_xml.
140 ** Bug 5386432 - made modifications to ensure that if there are no
141 ** results, main P45 should be generated but if there are no run-results
142 ** previous P45 exists then no supplement P45 should be generated.
143 ** Bug 5383808 - If P45 is issued for employees which are hired in the same
144 ** tax year than the commencement date should be the latest hire date.
145 ** Similary thispay and thistax should be the period for which he was rehired.
146 ** and not for the entire tax year.
147 ** 07-Aug-2006 vikgupta 115.66 In process_balance procedure, while calculation l_p45_last_bal_value
148 ** pass source_id of previous P45 archive instead of previous p45 action
149 ** for eg case hire in jan, run payroll, terminate, rehire in feb run payroll
150 ** now run P45 for jan and then for feb. In this case total pay and tax will be zero for feb.
151 ** 08-Sep-2006 vikgupta 115.67 1. 5519933 - noncumulative attribute does not depend upon
152 ** social benefit balance.
153 ** 2. 5510536 - Tax credit and cutoff should show either monthly or
154 ** weekly figures. Added cursor to display weekly figures for bi-weekly payroll
155 ** 3. 5510536 - Insurable weeks were summing up the previous tax year
156 ** insurable weeks also, modified the cursor Cur_Act_Contexts in
157 ** process_balance to have date join.
158 ** 4. 5510536 - PRSI figures displayed on P45 XML should be this employment
159 ** figures. So made changes in process_balance procedure.
160 ** 22-Sep-2006 rbhardwa 115.70 Bug 5528450. Chaged tax credit and cutoff to Period Weekly Tax Credit and
161 ** Period Weekly Standard Rate Cutoff to archive correct values.
162 ** 28-Sep-2006 sgajula 115.71 Bug 5519933. changed to report correct value against non cumulative flag
163 ** If employee is rehired in same tax year.
164 ** 09-oct-2006 vikgupta 115.72 5597735 - Performance fix (the same was also raised in bug 5233518)
165 ** 5591812 - to display PRSI classes only if its insurable weeks are not zero.
166 ** 5600150 - Changed to report deceased flag correctly.
167 ** 15-Jan-2007 sgajula 115.73 5758951 - If rehired on a new payroll, running P45 for old payroll does not
168 ** pick the employee.
169 ** 04-Jun-2007 vikgupta 115.74 6144761 - Modified action creation code. As case was failing when the
170 ** the employee was terminated in one tax year but has FPD in next
171 ** tax year and has payroll and prepayments in that tax year.
172 ** 07-nov-2007 rrajaman 115.75 6615117 - The p45 process is generating xml files with incorrect data.
173 ** For rehire having no child actions is showing -ve P45 details.
174 --------------------------------------------------------------------------------------------------------
175 */
176
177 TYPE balance_rec IS RECORD (
178 balance_type_id NUMBER,
179 balance_dimension_id NUMBER,
180 defined_balance_id NUMBER,
181 balance_narrative VARCHAR2(30),
182 balance_name VARCHAR2(60),
183 database_item_suffix VARCHAR2(30),
184 legislation_code VARCHAR2(20));
185
186 TYPE element_rec IS RECORD (
187 element_type_id NUMBER,
188 input_value_id NUMBER,
189 formula_id NUMBER,
190 element_narrative VARCHAR2(30));
191
192
193 TYPE balance_table IS TABLE OF balance_rec INDEX BY BINARY_INTEGER;
194 TYPE element_table IS TABLE OF element_rec INDEX BY BINARY_INTEGER;
195
196 g_statutory_balance_table balance_table;
197
198 g_balance_archive_index NUMBER := 0;
199 g_element_archive_index NUMBER := 0;
200 g_max_element_index NUMBER := 0;
201 g_max_user_balance_index NUMBER := 0;
202 g_max_statutory_balance_index NUMBER := 0;
203
204 g_paye_details_element_id NUMBER;
205
206 g_tax_basis_id NUMBER;
207 g_prsi_cat_id NUMBER;
208 g_prsi_subcat_id NUMBER;
209 g_ins_weeks_id NUMBER;
210
211 -- Global variables used to fetch Input value ids of Tax Credit and Cutoff 5128377
212 g_month_tax_rate NUMBER;
213 g_week_tax_rate NUMBER;
214 g_month_std_cutoff NUMBER;
215 g_week_std_cutoff NUMBER;
216 g_period_week_tax_rate NUMBER; /* 5528450 */
217 g_period_week_std_cutoff NUMBER; /* 5528450 */
218
219
220
221 g_package CONSTANT VARCHAR2(30) := 'pay_ie_p45_archive.';
222
223 g_archive_pact NUMBER;
224 g_archive_effective_date DATE;
225 g_archive_start_date DATE;
226 g_archive_end_date DATE;
227
228 g_paye_ref NUMBER;
229
230 -------
231 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
232 p_token_name IN VARCHAR2,
233 p_token_value OUT nocopy VARCHAR2) IS
234
235 CURSOR csr_parameter_info(p_pact_id NUMBER,
236 p_token CHAR) IS
237 SELECT SUBSTR(legislative_parameters,
238 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
239 INSTR(legislative_parameters,' ',
240 INSTR(legislative_parameters,p_token))
241 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
242 business_group_id
243 FROM pay_payroll_actions
244 WHERE payroll_action_id = p_pact_id;
245
246 l_business_group_id VARCHAR2(20);
247 l_token_value VARCHAR2(50);
248
249 l_proc VARCHAR2(50) := g_package || 'get_parameters';
250
251 BEGIN
252
253 hr_utility.set_location('Entering ' || l_proc,10);
254
255 hr_utility.set_location('Step ' || l_proc,20);
256 hr_utility.set_location('p_token_name = ' || p_token_name,20);
257
258 OPEN csr_parameter_info(p_payroll_action_id,
259 p_token_name);
260
261 FETCH csr_parameter_info INTO l_token_value,
262 l_business_group_id;
263
264 CLOSE csr_parameter_info;
265
266 IF p_token_name = 'BG_ID'
267
268 THEN
269
270 p_token_value := l_business_group_id;
271
272 ELSE
273
274 p_token_value := l_token_value;
275
276 END IF;
277
278 hr_utility.set_location('l_token_value = ' || l_token_value,20);
279 hr_utility.set_location('Leaving ' || l_proc,30);
280
281 END get_parameters;
282
283 --------------------------------
284 FUNCTION get_lookup_meaning(
285 p_lookup_type in varchar2
286 ,p_lookup_code in varchar2 ) RETURN varchar2 IS
287
288 CURSOR csr_get_lookup IS
289 SELECT meaning
290 FROM hr_lookups
291 WHERE lookup_type=p_lookup_type
292 AND lookup_code=p_lookup_code;
293
294 p_lookup_meaning hr_lookups.meaning%TYPE;
295
296 BEGIN
297 p_lookup_meaning := NULL;
298 OPEN csr_get_lookup;
299 FETCH csr_get_lookup INTO p_lookup_meaning;
300 CLOSE csr_get_lookup;
301
302 RETURN(p_lookup_meaning);
303
304 END get_lookup_meaning;
305
306 --------------------------------
307 PROCEDURE setup_balance_definitions(p_pactid IN NUMBER,
308 p_payroll_pact IN NUMBER,
309 p_effective_date IN DATE) IS
310
311 l_action_info_id NUMBER(15);
312 l_ovn NUMBER(15);
313 l_index NUMBER;
314
315 l_proc VARCHAR2(50) := g_package || 'setup_balance_definitions';
316
317 BEGIN
318
319
320 hr_utility.set_location('Entering ' || l_proc,10);
321 hr_utility.set_location('Step ' || l_proc,20);
322
323 FOR l_index IN 1 ..g_max_statutory_balance_index
324 LOOP
325
326 hr_utility.set_location('p_pactid = '||p_pactid,20);
327 hr_utility.set_location('p_payroll_pact = '||p_payroll_pact,20);
328 hr_utility.set_location('p_effective_date = '||p_effective_date,20);
329 hr_utility.set_location('defined_balance_id = '||g_statutory_balance_table(l_index).defined_balance_id,20);
330 hr_utility.set_location('balance_name = '||g_statutory_balance_table(l_index).balance_name,20);
331
332 pay_action_information_api.create_action_information (
333 p_action_information_id => l_action_info_id
334 , p_action_context_id => p_pactid
335 , p_action_context_type => 'PA'
336 , p_object_version_number => l_ovn
337 , p_effective_date => p_effective_date
338 , p_source_id => NULL
339 , p_source_text => NULL
340 , p_action_information_category => 'EMEA BALANCE DEFINITION'
341 , p_action_information1 => p_payroll_pact
342 , p_action_information2 => g_statutory_balance_table(l_index).defined_balance_id
343 , p_action_information3 => NULL
344 , p_action_information4 => g_statutory_balance_table(l_index).balance_name);
345
346 END LOOP;
347
348 hr_utility.set_location('Leaving ' || l_proc,30);
349
350 END setup_balance_definitions;
351 ------------------------
352 /* This Procedure populates PL/SQL Table with balance names and defined_balance_id which are used to
353 archive Balance Names and their Values
354 Balances With Index 1-19 are Total YTD Balances and
355 Balances with Index 20 -33 are Balances used for Supplementary P45
356 */
357 PROCEDURE setup_standard_balance_table
358 IS
359 TYPE balance_name_rec IS RECORD (
360 balance_name VARCHAR2(60));
361 TYPE balance_id_rec IS RECORD (
362 defined_balance_id NUMBER);
363 TYPE balance_name_tab IS TABLE OF balance_name_rec INDEX BY BINARY_INTEGER;
364 TYPE balance_id_tab IS TABLE OF balance_id_rec INDEX BY BINARY_INTEGER;
365 l_statutory_balance balance_name_tab;
366 l_statutory_bal_id balance_id_tab;
367 CURSOR csr_balance_dimension(p_balance IN CHAR,
368 p_dimension IN CHAR) IS
369 SELECT pdb.defined_balance_id
370 FROM pay_balance_types pbt,
371 pay_balance_dimensions pbd,
372 pay_defined_balances pdb
373 WHERE pdb.balance_type_id = pbt.balance_type_id
374 AND pdb.balance_dimension_id = pbd.balance_dimension_id
375 AND pbt.balance_name = p_balance
376 AND pbd.database_item_suffix = p_dimension
377 AND pdb.legislation_code = 'IE';
378 l_archive_index NUMBER := 1;
379 -- Balances used for YTD
380 l_dimension VARCHAR2(20) := '_PER_PAYE_REF_YTD'; -- 'PER_PAYE_REF_YTD'
381 l_dimension_1 VARCHAR2(30) := '_PER_PAYE_REF_PRSI_YTD';
382 -- Balances used for Supp P45
383 l_dimension_2 VARCHAR2(30) := '_ASG_PAYE_REF_PRSI_RUN'; -- Bug 5015438
384 l_dimension_pay VARCHAR2(16) := '_PAYMENTS';
385 l_dimension_run VARCHAR2(16) := '_ASG_RUN';
386 l_dimension_ptd VARCHAR2(16) := '_ASG_PTD';
387 l_found VARCHAR2(1) := 'N';
388 l_max_stat_balance NUMBER := 19;
389 l_pactid NUMBER;
390 l_payroll_pact NUMBER;
391 l_proc VARCHAR2(100) := g_package || 'setup_standard_balance_table';
392 BEGIN
393 hr_utility.set_location('Entering ' || l_proc,10);
394 hr_utility.set_location('Step ' || l_proc,20);
395 l_statutory_balance(1).balance_name := 'IE Taxable Pay';
396 l_statutory_balance(2).balance_name := 'IE Net Tax';
397 l_statutory_balance(3).balance_name := 'IE PRSI Employer';
398 l_statutory_balance(4).balance_name := 'IE PRSI Employee';
399 l_statutory_balance(5).balance_name := 'IE Lump Sum';
400 l_statutory_balance(6).balance_name := 'IE PRSI Insurable Weeks';
401 l_statutory_balance(15).balance_name := 'IE Reduced Tax Credit';
402 l_statutory_balance(16).balance_name := 'IE Reduced Std Rate Cut Off';
403 l_statutory_balance(17).balance_name := 'IE Taxable Social Benefit';
404 l_statutory_balance(18).balance_name := 'IE P45 Pay';
405 l_statutory_balance(19).balance_name := 'IE P45 Tax Deducted';
406 l_statutory_balance(14).balance_name := 'IE PRSI_ClassA Insurable Weeks'; -- Bug 5015438
407 -- Added new balances which needs to be archived when a severance payment exists
408 l_statutory_balance(7).balance_name := 'IE PRSI K Employee Lump Sum';
409 l_statutory_balance(8).balance_name := 'IE PRSI M Employee Lump Sum';
410 l_statutory_balance(9).balance_name := 'IE PRSI K Employer Lump Sum';
411 l_statutory_balance(10).balance_name := 'IE PRSI M Employer Lump Sum';
412 l_statutory_balance(11).balance_name := 'IE PRSI K Term Insurable Weeks';
413 l_statutory_balance(12).balance_name := 'IE PRSI M Term Insurable Weeks';
414 l_statutory_balance(13).balance_name := 'IE Term Health Levy';
415 hr_utility.set_location('Step = ' || l_proc,30);
416 FOR l_index IN 1 .. l_max_stat_balance
417 LOOP
418 hr_utility.set_location('l_index = ' || l_index,30);
419 hr_utility.set_location('balance_name = ' || l_statutory_balance(l_index).balance_name,30);
420 hr_utility.set_location('l_dimension = ' || l_dimension,30);
421 IF (l_index < 15) THEN -- Stores RUN balance_defined information -- Bug 5015438
422 IF l_statutory_balance(l_index).balance_name in ('IE PRSI Insurable Weeks','IE PRSI K Term Insurable Weeks','IE PRSI M Term Insurable Weeks','IE PRSI_ClassA Insurable Weeks') THEN
423
424 IF l_index <> 14 THEN --Bug 5015438
425 /* If the Balance is IE PRSI Insurable Weeks or IE PRSI K Term Insurable Weeks or IE PRSI M Term Insurable Weeks then attach the dimension ASG_PTD for Supp P45 Balances*/
426 OPEN csr_balance_dimension(l_statutory_balance(l_index).balance_name,l_dimension_ptd);
427 l_statutory_balance(l_max_stat_balance + l_index).balance_name :=l_statutory_balance(l_index).balance_name || 'ASG_PTD';
428 ELSE
429 /* If the Balance is IE PRSI_ClassA Insurable Weeks attach the dimension _ASG_PAYE_REF_PRSI_RUN to it for Supp P45 balance*/
430 OPEN csr_balance_dimension('IE PRSI Insurable Weeks',l_dimension_2);
431 l_statutory_balance(l_max_stat_balance + l_index).balance_name :=l_statutory_balance(l_index).balance_name || 'ASG_PTD';
432 END IF;
433
434 ELSE
435 /* In other cases attach the dimension ASG_RUN for Supp P45 Balance */
436 OPEN csr_balance_dimension(l_statutory_balance(l_index).balance_name,l_dimension_run);
437 l_statutory_balance(l_max_stat_balance + l_index).balance_name :=l_statutory_balance(l_index).balance_name || 'ASG_RUN';
438 END IF;
439
440 FETCH csr_balance_dimension INTO l_statutory_bal_id(l_max_stat_balance + l_index).defined_balance_id;
441 IF csr_balance_dimension%NOTFOUND THEN
442 l_statutory_bal_id(l_max_stat_balance + l_index).defined_balance_id := 0;
443 END IF;
444 CLOSE csr_balance_dimension;
445 g_statutory_balance_table(l_max_stat_balance + l_index).defined_balance_id := l_statutory_bal_id(l_max_stat_balance + l_index).defined_balance_id;
446 g_statutory_balance_table(l_max_stat_balance + l_index).balance_name := l_statutory_balance(l_max_stat_balance + l_index).balance_name;
447
448 IF l_index <> 14 THEN -- Bug 5015438
449 g_statutory_balance_table(l_max_stat_balance + l_index).database_item_suffix := l_dimension_run;
450 ELSE
451 g_statutory_balance_table(l_max_stat_balance + l_index).database_item_suffix := l_dimension_2;
452 END IF;
453
454 l_archive_index := l_archive_index + 1;
455 END IF;
456 -- Stores ASG_YTD balance_defined information
457 IF l_index <> 14 THEN -- Bug 5015438
458 OPEN csr_balance_dimension(l_statutory_balance(l_index).balance_name,l_dimension);
459 ELSE
460 OPEN csr_balance_dimension('IE PRSI Insurable Weeks',l_dimension_1);
461 END IF;
462
463 FETCH csr_balance_dimension INTO l_statutory_bal_id(l_index).defined_balance_id;
464 IF csr_balance_dimension%NOTFOUND THEN
465 l_statutory_bal_id(l_index).defined_balance_id := 0;
466 END IF;
467 CLOSE csr_balance_dimension;
468 g_statutory_balance_table(l_index).defined_balance_id := l_statutory_bal_id(l_index).defined_balance_id;
469 g_statutory_balance_table(l_index).balance_name := l_statutory_balance(l_index).balance_name;
470 IF l_index <> 14 THEN -- Bug 5015438
471 g_statutory_balance_table(l_index).database_item_suffix := l_dimension;
472 -- l_archive_index := l_archive_index + 1;
473 ELSE
474 g_statutory_balance_table(l_index).database_item_suffix := l_dimension_1;
475
476 END IF;
477 l_archive_index := l_archive_index + 1;
478 END LOOP;
479 ---
480 l_archive_index := l_archive_index - 1;
481 -- hr_utility.set_location('retrieving PER_PAYE_REF_PRSI_YTD bal_id for Insurable weeks',40); -- Bug 5015438
482 -- OPEN csr_balance_dimension('IE PRSI Insurable Weeks',l_dimension_1);
483 -- FETCH csr_balance_dimension INTO l_statutory_bal_id(l_archive_index).defined_balance_id;
484 -- CLOSE csr_balance_dimension;
485 -- g_statutory_balance_table(l_archive_index).defined_balance_id := l_statutory_bal_id(l_archive_index).defined_balance_id;
486 -- g_statutory_balance_table(l_archive_index).balance_name := 'IE PRSI_ClassA Insurable Weeks';
487 -- g_statutory_balance_table(l_archive_index).database_item_suffix := l_dimension_1;
488 -- hr_utility.set_location('Step = ' || l_proc,40);
489 -- hr_utility.set_location('l_max_stat_balance = ' || l_max_stat_balance,40);
490 g_max_statutory_balance_index := l_archive_index;
491 -- hr_utility.set_location('Step ' || l_proc,50);
492 -- hr_utility.set_location('l_archive_index = ' || l_archive_index,50);
493 -- hr_utility.set_location('Leaving ' || l_proc,60);
494 END setup_standard_balance_table;
495 ---------------------------------------
496
497 PROCEDURE archinit (p_payroll_action_id IN NUMBER)
498 IS
499
500 CURSOR csr_archive_effective_date(pactid NUMBER) IS
501 SELECT effective_date
502 FROM pay_payroll_actions
503 WHERE payroll_action_id = pactid;
504
505 CURSOR csr_input_value_id(p_element_name CHAR,
506 p_value_name CHAR) IS
507 SELECT pet.element_type_id,
508 piv.input_value_id
509 FROM pay_input_values_f piv,
510 pay_element_types_f pet
511 WHERE piv.element_type_id = pet.element_type_id
512 AND pet.legislation_code = 'IE'
513 AND pet.element_name = p_element_name
514 AND piv.name = p_value_name;
515
516 l_proc VARCHAR2(50) := g_package || 'archinit';
517 l_assignment_set_id NUMBER;
518 l_bg_id NUMBER;
519 l_canonical_end_date DATE;
520 l_canonical_start_date DATE;
521 l_consolidation_set NUMBER;
522 l_end_date VARCHAR2(30);
523 l_payroll_id NUMBER;
524 l_start_date VARCHAR2(30);
525 l_dummy VARCHAR2(2);
526 l_error varchar2(1) ;
527 BEGIN
528 --hr_utility.trace_on(null,'IEP45');
529 hr_utility.set_location('Entering ' || l_proc,10);
530
531 g_archive_pact := p_payroll_action_id;
532
533 OPEN csr_archive_effective_date(p_payroll_action_id);
534 FETCH csr_archive_effective_date
535 INTO g_archive_effective_date;
536 CLOSE csr_archive_effective_date;
537
538 pay_ie_p45_archive.get_parameters (
539 p_payroll_action_id => p_payroll_action_id
540 , p_token_name => 'EMPLOYER'
541 , p_token_value => g_paye_ref);
542
543 pay_ie_p45_archive.get_parameters (
544 p_payroll_action_id => p_payroll_action_id
545 , p_token_name => 'END_DATE'
546 , p_token_value => l_end_date);
547
548 pay_ie_p45_archive.get_parameters (
549 p_payroll_action_id => p_payroll_action_id
550 , p_token_name => 'START_DATE'
551 , p_token_value => l_start_date);
552
553 pay_ie_p45_archive.get_parameters (
554 p_payroll_action_id => p_payroll_action_id
555 , p_token_name => 'BG_ID'
556 , p_token_value => l_bg_id);
557
558 hr_utility.set_location('Step ' || l_proc,20);
559 hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
560 hr_utility.set_location('l_end_date = ' || l_end_date,20);
561
562 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
563 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
564
565 -- Initialized g_archive_end_date to support Retry Option
566 g_archive_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
567 g_archive_start_date := l_canonical_start_date;
568
569 hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
570
571
572 -- retrieve ids for tax elements
573 hr_utility.set_location('stage 1',22);
574
575 OPEN csr_input_value_id('IE PAYE details','Tax Basis');
576 FETCH csr_input_value_id INTO g_paye_details_element_id,
577 g_tax_basis_id;
578 CLOSE csr_input_value_id;
579
580 OPEN csr_input_value_id('IE PRSI Detail','Contribution Class');
581 FETCH csr_input_value_id INTO g_paye_details_element_id,
582 g_prsi_cat_id;
583 CLOSE csr_input_value_id;
584
585 OPEN csr_input_value_id('IE PRSI Detail','Subclass');
586 FETCH csr_input_value_id INTO g_paye_details_element_id,
587 g_prsi_subcat_id;
588 CLOSE csr_input_value_id;
589
590 OPEN csr_input_value_id('IE PRSI Detail','Insurable Weeks');
591 FETCH csr_input_value_id INTO g_paye_details_element_id,
592 g_ins_weeks_id;
593 CLOSE csr_input_value_id;
594
595 -- Fetch the Input value ID of Monthly Tax Credit 5128377
596 OPEN csr_input_value_id('IE PAYE details','Monthly Tax Credit');
597 FETCH csr_input_value_id INTO g_paye_details_element_id,
598 g_month_tax_rate;
599 CLOSE csr_input_value_id;
600
601 -- Fetch the Input value ID of Weekly Tax Credit 5128377
602 OPEN csr_input_value_id('IE PAYE details','Weekly Tax Credit');
603 FETCH csr_input_value_id INTO g_paye_details_element_id,
604 g_week_tax_rate;
605 CLOSE csr_input_value_id;
606
607 -- Changed to Period Weekly Tax Credit for Bug 5528450.
608 OPEN csr_input_value_id('IE PAYE details','Period Weekly Tax Credit');
609 FETCH csr_input_value_id INTO g_paye_details_element_id,
610 g_period_week_tax_rate;
611 CLOSE csr_input_value_id;
612
613 -- Fetch the Input value ID of Monthly Standard Rate Cutoff 5128377
614 OPEN csr_input_value_id('IE PAYE details','Monthly Standard Rate Cutoff');
615 FETCH csr_input_value_id INTO g_paye_details_element_id,
616 g_month_std_cutoff;
617 CLOSE csr_input_value_id;
618
619 -- Fetch the Input value ID of Weekly Standard Rate Cutoff 5128377
620 OPEN csr_input_value_id('IE PAYE details','Weekly Standard Rate Cutoff');
621 FETCH csr_input_value_id INTO g_paye_details_element_id,
622 g_week_std_cutoff;
623 CLOSE csr_input_value_id;
624
625 -- Changed to Period Weekly Standard Rate Cutoff for Bug 5528450.
626 OPEN csr_input_value_id('IE PAYE details','Period Weekly Standard Rate Cutoff');
627 FETCH csr_input_value_id INTO g_paye_details_element_id,
628 g_period_week_std_cutoff;
629 CLOSE csr_input_value_id;
630
631 hr_utility.set_location('stage 2',23);
632 pay_ie_p45_archive.setup_standard_balance_table;
633
634 hr_utility.set_location('stage 3',24);
635
636 -- hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
637 -- hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
638 -- hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
639 -- hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
640 hr_utility.set_location('Leaving ' || l_proc,20);
641 END archinit;
642 --------------------------------------------------------------------
643
644 PROCEDURE archive_employee_details (
645 p_assactid IN NUMBER
646 , p_assignment_id IN NUMBER
647 , p_curr_pymt_ass_act_id IN NUMBER
648 , p_payroll_child_actid IN NUMBER
649 , p_date_earned IN DATE
650 , p_curr_pymt_eff_date IN DATE
651 , p_time_period_id IN NUMBER
652 , p_record_count IN NUMBER
653 , p_supp_flag IN VARCHAR2
654 , p_person_id IN NUMBER
655 , p_termination_date IN DATE
656 , p_last_act_seq IN NUMBER
657 , p_last_p45_act IN NUMBER
658 -- added effective_date for bug 5591812
659 , p_effective_date IN DATE) IS
660
661 l_action_info_id NUMBER;
662 l_ovn NUMBER;
663 --
664 l_tax_basis VARCHAR2(20);
665 l_tax_basis_det VARCHAR2(20);
666 l_arch_run_count NUMBER;
667 l_prsi_cat VARCHAR2(50) :='';
668 l_prsi_cur_cat VARCHAR2(1);
669 l_prsi_subcat VARCHAR2(10);
670 l_ins_weeks VARCHAR2(10);
671 l_monthly_tax_credit NUMBER;
672 l_weekly_tax_credit NUMBER;
673 l_period_weekly_tax_credit NUMBER; /* 5528450 */
674 l_monthly_std_rate_cutoff NUMBER;
675 l_weekly_std_rate_cutoff NUMBER;
676 l_period_weekly_std_cutoff NUMBER; /* 5528450 */
677 l_tax_credit NUMBER;
678 l_std_rate_cut_off NUMBER;
679 l_period_type VARCHAR2(20);
680 l_date_of_birth DATE;
681 l_first_name per_all_people_f.first_name%TYPE;
682 l_last_name per_all_people_f.last_name%TYPE;
683 l_bg_id NUMBER;
684 l_commencement_date VARCHAR2(30);
685
686 -- Commented as PAYE Details are now fetched from Run Results (5128377)
687 /*
688 cursor cur_paye_dtl is
689 select nvl(monthly_tax_credit,0)
690 ,nvl(weekly_tax_credit,0)
691 ,nvl(monthly_std_rate_cut_off,0)
692 ,nvl(weekly_std_rate_cut_off,0)
693 from pay_ie_paye_details_f pipd
694 where assignment_id = p_assignment_id
695 and p_date_earned between
696 effective_start_date and effective_end_date
697 and info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS')
698 and tax_basis not in ('IE_EMERGENCY','IE_EMERGENCY_NO_PPS'); --Bug No. 4016508
699 */
700
701 /*Bug No. 4016508*/
702 /* cursor cur_credit_cutoff_emer(p_global_name varchar2) is
703 select fgf.global_value
704 from ff_globals_f fgf
705 where fgf.global_name = p_global_name
706 and fgf.legislation_code ='IE'
707 and p_date_earned between
708 fgf.effective_start_date and fgf.effective_end_date;*/
709
710 cursor cur_period_type is
711 select ptp.period_type
712 from per_time_periods ptp
713 where time_period_id = p_time_period_id;
714
715 cursor cur_sep_name_dob(p_bg_id NUMBER) is
716 select papf.date_of_birth,papf.first_name, papf.last_name
717 from per_all_people_f papf,
718 per_all_assignments_f pasf
719 where pasf.assignment_id = p_assignment_id
720 and p_date_earned between
721 pasf.effective_start_date and pasf.effective_end_date
722 and pasf.business_group_id = p_bg_id
723 and papf.person_id = pasf.person_id
724 and p_date_earned between
725 papf.effective_start_date and papf.effective_end_date
726 and papf.business_group_id = pasf.business_group_id;
727
728 CURSOR cur_payroll_assg_action is
729 select paa.assignment_action_id pay_assg_act_id
730 from pay_assignment_actions paa,
731 pay_payroll_actions ppa
732 where paa.assignment_id in (select assignment_id
733 from per_all_assignments_f
734 where person_id = p_person_id
735 )
736 and paa.tax_unit_id = g_paye_ref
737 and paa.payroll_action_id = ppa.payroll_action_id
738 and ppa.action_type in ('R','Q')
739 and paa.action_sequence > p_last_act_seq
740 and to_char(ppa.effective_date,'YYYY') = to_char(p_date_earned, 'YYYY')
741 and paa.action_status = 'C'
742 and paa.source_action_id is not null
743 --Bug 4724788
744 order by paa.assignment_action_id;
745 -- Modified this cursor, parameterised dimension_name for bug 5591812
746 cursor balance_id (bal_name varchar2, p_dimension_name varchar2) is
747 SELECT pdb.defined_balance_id
748 FROM
749 pay_balance_dimensions pbd
750 ,pay_balance_types pbt
751 ,pay_defined_balances pdb
752 WHERE
753 pbd.dimension_name = p_dimension_name
754 AND pbd.business_group_id is null
755 AND pbd.legislation_code='IE'
756 AND pbt.balance_name = bal_name
757 AND pbt.business_group_id is null
758 AND pbt.legislation_code='IE'
759 AND pdb.balance_type_id = pbt.balance_type_id
760 AND pdb.balance_dimension_id= pbd.balance_dimension_id
761 AND pdb.business_group_id is null
762 AND pdb.legislation_code='IE';
763
764 CURSOR payroll_asg_action is
765 select max(paa.assignment_action_id)
766 from pay_assignment_actions paa,
767 pay_payroll_actions ppa
768 where paa.assignment_id in (select assignment_id
769 from per_all_assignments_f
770 where person_id = p_person_id
771 )
772 and paa.tax_unit_id = g_paye_ref
773 and paa.payroll_action_id = ppa.payroll_action_id
774 and ppa.action_type in ('R','Q')
775 and to_char(ppa.effective_date,'YYYY') = to_char(p_date_earned, 'YYYY')
776 and paa.action_status = 'C'
777 and paa.source_action_id is not null;
778
779 -- Fetch Commencement date when no previous p45 produced.
780 CURSOR comm_date_first IS
781 select act_inf.action_information11
782 from pay_action_information act_inf
783 where act_inf.action_context_id = p_assactid
784 and act_inf.action_information_category = 'EMPLOYEE DETAILS'
785 and act_inf.action_context_type = 'AAP';
786
787 -- Fetch Commencement date when p45 is produced previously.
788 CURSOR comm_date_last_p45 IS
789 select act_inf.action_information30
790 from pay_action_information act_inf
791 where act_inf.action_context_id = p_last_p45_act
792 and act_inf.action_information_category = 'IE EMPLOYEE DETAILS'
793 and act_inf.action_context_type = 'AAP';
794
795 -- Bug 5386432
796 -- CURSOR to fetch tax credit and std cutoff from paye table for
797 -- employees having 0 earnings.
798 CURSOR csr_get_paye_details is
799 select tax_basis
800 ,weekly_tax_credit
801 ,weekly_std_rate_cut_off
802 ,monthly_tax_credit
803 ,monthly_std_rate_cut_off
804 from pay_ie_paye_details_f
805 where assignment_id = p_assignment_id
806 and p_termination_date between effective_start_date and effective_end_date;
807
808 -- Added by vikas cursor to number_per_fiscal_year.
809 -- Since tax credit and cutoff figures are fetched from run-results, for
810 -- bi-weekly payroll cutoff and credit and are shown as twice of weekly figures.
811 -- bug 5510536
812 cursor csr_number_per_year (l_period_type per_time_periods.period_type%type) is
813 select number_per_fiscal_year
814 from per_time_period_types tpt
815 where period_type = l_period_type;
816 --
817 l_number_per_year per_time_period_types.number_per_fiscal_year%type;
818
819 --end vikas
820
821 -- To display futher PRSI Classes only, if its insurable weeks are non-zero.
822 -- bug 5591812
823 l_prev_sequence number;
824 l_current_sequence number;
825 CURSOR c_context_id
826 IS
827 SELECT context_id
828 FROM ff_contexts
829 WHERE context_name = 'SOURCE_TEXT';
830 l_context_id ff_contexts.context_id%TYPE;
831 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
832 v_class varchar2(30);
833
834
835 CURSOR Cur_Act_Contexts(l_defined_bal_id number,p_context_value varchar2) IS
836 SELECT sum(PAY_BALANCE_PKG.GET_VALUE(l_defined_bal_id, -- changes made
837 pac.ASSIGNMENT_ACTION_ID,
838 g_paye_ref,
839 null,
840 pac.CONTEXT_ID,
841 pac.CONTEXT_VALUE,
842 null,
843 null))
844 FROM pay_action_contexts pac,
845 pay_assignment_actions pas,
846 pay_payroll_actions ppa
847 WHERE substr(pac.Context_Value,1,4) = p_context_value
848 AND pac.assignment_id in (select papf.assignment_id
849 from per_all_assignments_f papf
850 where papf.person_id = p_person_id
851 )
852 AND pas.tax_unit_id = g_paye_ref
853 AND pas.assignment_action_id = pac.assignment_action_id
854 AND ppa.payroll_action_id = pas.payroll_action_id
855 AND ppa.effective_date between to_date('01-01-' || to_char(p_effective_date,'YYYY'),'DD-MM-YYYY') --Bug fix 4108423
856 AND g_archive_end_date
857 and pas.action_sequence > l_prev_sequence
858 and pas.action_sequence <= l_current_sequence;
859
860 CURSOR cur_get_prev_run_seq is
861 select paa.action_sequence
862 from pay_assignment_actions paa,
863 pay_payroll_actions ppa,
864 pay_action_interlocks pai,
865 pay_assignment_actions paa1
866 where paa1.source_action_id = p_last_p45_act
867 and pai.locking_action_id = paa1.assignment_action_id
868 and pai.locked_action_id = paa.assignment_action_id
869 and paa.assignment_id in (select papf.assignment_id
870 from per_all_assignments_f papf
871 where papf.person_id = p_person_id
872 )
873 and paa.tax_unit_id = g_paye_ref
874 and paa.payroll_action_id = ppa.payroll_action_id
875 and ppa.action_type in ('R','Q','I','B','V');
876
877 CURSOR cur_get_curr_run_seq is
878 select action_sequence
879 from pay_assignment_actions ppa
880 where assignment_action_id = p_payroll_child_actid;
881
882 l_class_weeks number;
883 -- end for bug 5591812
884
885
886
887 -- Variables to store K, M and Total Insurable Weeks
888 k_defined_balance_id pay_defined_balances.DEFINED_BALANCE_ID%type := NULL;
889 m_defined_balance_id pay_defined_balances.DEFINED_BALANCE_ID%type := NULL;
890 pay_act_id number := NULL;
891
892 l_proc VARCHAR2(60) := g_package || 'archive_employee_details';
893 l_prsi_count NUMBER(1) := 0; --bug 4724788
894 --
895 BEGIN
896 hr_utility.set_location('Entering ' || l_proc,10);
897 -- call generic procedure to retrieve and archive all data for
898 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE NET PAY DISTRIBUTION
899 hr_utility.set_location('Calling pay_emp_action_arch',20);
900
901 pay_emp_action_arch.get_personal_information (
902 p_payroll_action_id => g_archive_pact -- archive payroll_action_id
903 , p_assactid => p_assactid -- archive assignment_action_id
904 , p_assignment_id => p_assignment_id -- current assignment_id
905 , p_curr_pymt_ass_act_id => p_curr_pymt_ass_act_id -- prepayment assignment_action_id
906 , p_curr_eff_date => p_curr_pymt_eff_date -- prepayment effective_date(specially reqd
907 -- for archives later than the
908 -- last process date after termination date)
909 , p_date_earned => p_date_earned -- payroll date_earned
910 , p_curr_pymt_eff_date => p_curr_pymt_eff_date -- prepayment effective_date
911 , p_tax_unit_id => g_paye_ref -- only required for US
912 , p_time_period_id => p_time_period_id -- payroll time_period_id
913 , p_ppp_source_action_id => NULL);
914
915 hr_utility.set_location('Returned from pay_emp_action_arch',30);
916
917 -- get the business group id
918 pay_ie_p45_archive.get_parameters (
919 p_payroll_action_id => g_archive_pact
920 , p_token_name => 'BG_ID'
921 , p_token_value => l_bg_id);
922
923 hr_utility.set_location('p_run_assignment_action_id ='||p_payroll_child_actid,40);
924
925 -- get tax basis
926 hr_utility.set_location('g_tax_basis_id ='||g_tax_basis_id,40);
927 -- Bug 5386432, since for terminated which does not have any element attached with FPD
928 -- will have no child assignment actions. Call this only if child actions exists.
929 IF p_payroll_child_actid IS NOT NULL THEN
930 l_tax_basis := pay_ie_archive_detail_pkg.get_tax_details (
931 p_run_assignment_action_id => p_payroll_child_actid
932 ,p_input_value_id => g_tax_basis_id
933 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
934 END IF;
935 hr_utility.set_location('l_tax_basis = ' || l_tax_basis,40);
936
937 -- get prsi classes
938 hr_utility.set_location('g_prsi_cat_id ='||g_prsi_cat_id,40);
939 -- check for supplementary run
940 /*
941 OPEN cur_supp_run;
942 FETCH cur_supp_run INTO l_arch_run_count;
943 CLOSE cur_supp_run;
944 hr_utility.set_location('l_arch_run_count ='||l_arch_run_count,40);
945 -- if it is a supplementary run, archive only for the current run
946 IF l_arch_run_count>1
947 */
948 /*
949 IF p_supp_flag = 'Y'
950 THEN
951 l_prsi_cat := pay_ie_archive_detail_pkg.get_tax_details (
952 p_run_assignment_action_id => p_payroll_child_actid
953 ,p_input_value_id => g_prsi_cat_id
954 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
955 -- otherise archive for all payroll runs
956 ELSE
957 */
958 -- Added to check to dispaly further PRSI classes only if insurable
959 -- weeks are not zero.
960 /* OPEN c_context_id;
961 FETCH c_context_id INTO l_context_id;
962 CLOSE c_context_id;
963
964 if p_supp_flag <> 'Y' then*/
965 -- bug 5591812
966 open balance_id('IE PRSI Insurable Weeks','_ASG_PAYE_REF_PRSI_RUN');
967 FETCH balance_id into l_defined_balance_id ;
968 CLOSE balance_id;
969
970 OPEN cur_get_prev_run_seq;
971 FETCH cur_get_prev_run_seq into l_prev_sequence;
972 CLOSE cur_get_prev_run_seq;
973
974 IF l_prev_sequence IS NULL THEN
975 l_prev_sequence := 0;
976 END IF;
977
978 open cur_get_curr_run_seq;
979 fetch cur_get_curr_run_seq into l_current_sequence;
980 CLOSE cur_get_curr_run_seq;
981
982 if l_current_sequence is null then
983 l_current_sequence := 0;
984 end if;
985 hr_utility.set_location('l_current_sequence..'||l_current_sequence,101);
986 hr_utility.set_location('l_prev_sequence..'||l_prev_sequence,101);
987
988
989 FOR assg_act_rec IN cur_payroll_assg_action
990 LOOP
991 l_prsi_cur_cat := pay_ie_archive_detail_pkg.get_tax_details (
992 p_run_assignment_action_id => assg_act_rec.pay_assg_act_id
993 ,p_input_value_id => g_prsi_cat_id
994 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
995 hr_utility.set_location('l_prsi_cur_cat = ' || l_prsi_cur_cat,40);
996
997 IF l_prsi_cur_cat IS NOT NULL AND l_prsi_cur_cat <>'A' AND (nvl(instr(l_prsi_cat,l_prsi_cur_cat),0) = 0)
998 THEN
999
1000 IF l_prsi_cat <> ' ' THEN
1001 -- Bug 3669639 Added a space after the comma so that the display is now uniform with spaces
1002 hr_utility.set_location('In if l_prsi_cur_cat = ' || l_prsi_cur_cat,41);
1003 open Cur_Act_Contexts(l_defined_balance_id,'IE_'||l_prsi_cur_cat);
1004 fetch Cur_Act_Contexts into l_class_weeks;
1005 CLOSE Cur_Act_Contexts;
1006 hr_utility.set_location('in if l_class_weeks ..'||l_class_weeks,101);
1007 if nvl(l_class_weeks,0) > 0 then
1008 l_prsi_cat := l_prsi_cat||', '|| l_prsi_cur_cat;
1009 l_prsi_count := l_prsi_count + 1; --Bug 4724788
1010 end if;
1011 hr_utility.set_location('l_prsi_count = ' || l_prsi_count,420);
1012 --Bug 4724788
1013 if l_prsi_count = 2 then
1014 exit;
1015 end if;
1016 ELSE
1017 v_class := 'IE_'||l_prsi_cur_cat;
1018 hr_utility.set_location('before in else l_class_weeks ..'||l_class_weeks,101);
1019 hr_utility.set_location('before in else l_prsi_cur_cat ..'||l_prsi_cur_cat,101);
1020 hr_utility.set_location('v_class ..'||v_class,101);
1021 open Cur_Act_Contexts(l_defined_balance_id,v_class);
1022 fetch Cur_Act_Contexts into l_class_weeks;
1023 CLOSE Cur_Act_Contexts;
1024 hr_utility.set_location('in else l_class_weeks ..'||l_class_weeks,101);
1025
1026 if nvl(l_class_weeks,0) > 0 then
1027
1028 l_prsi_cat :=l_prsi_cur_cat;
1029 hr_utility.set_location('In if else v_class = ' || v_class,42);
1030 end if;
1031 --exit;
1032 END IF;
1033 END IF;
1034
1035 END LOOP;
1036 -- end bug 5591812
1037 -- END IF;
1038 hr_utility.set_location('g_prsi_subcat_id ='||g_prsi_subcat_id,40);
1039 -- Bug 5386432, since for terminated which does not have any element attached with FPD
1040 -- will have no child assignment actions. Call this only if child actions exists.
1041 IF p_payroll_child_actid IS NOT NULL THEN
1042 l_prsi_subcat := pay_ie_archive_detail_pkg.get_tax_details (
1043 p_run_assignment_action_id => p_payroll_child_actid
1044 ,p_input_value_id => g_prsi_subcat_id
1045 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
1046 l_ins_weeks := pay_ie_archive_detail_pkg.get_tax_details (
1047 p_run_assignment_action_id => p_payroll_child_actid
1048 ,p_input_value_id => g_ins_weeks_id
1049 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
1050 END IF;
1051 hr_utility.set_location('l_prsi_subcat = ' || l_prsi_subcat,40);
1052 hr_utility.set_location('g_ins_weeks_id ='||g_ins_weeks_id,40);
1053 hr_utility.set_location('l_ins_weeks = ' || l_ins_weeks,40);
1054
1055 -- get tax credit and std rate cut off
1056 OPEN cur_period_type;
1057 FETCH cur_period_type INTO l_period_type;
1058 CLOSE cur_period_type;
1059
1060 -- Commented as PAYE Details are now fetched from Run Results (5128377)
1061 /*
1062 OPEN cur_paye_dtl;
1063 FETCH cur_paye_dtl INTO l_monthly_tax_credit ,
1064 l_weekly_tax_credit,
1065 l_monthly_std_rate_cutoff,
1066 l_weekly_std_rate_cutoff;
1067 IF cur_paye_dtl%NOTFOUND
1068 THEN */
1069 /*Bug No. 4016508*/
1070 /*OPEN cur_credit_cutoff_emer('IE_WEEKLY_TAX_CREDIT');
1071 FETCH cur_credit_cutoff_emer INTO l_weekly_tax_credit;
1072 CLOSE cur_credit_cutoff_emer;
1073 OPEN cur_credit_cutoff_emer('IE_MONTHLY_TAX_CREDIT');
1074 FETCH cur_credit_cutoff_emer INTO l_monthly_tax_credit;
1075 CLOSE cur_credit_cutoff_emer;*/
1076 /* l_weekly_tax_credit:=0;
1077 l_monthly_tax_credit:=0;
1078 l_monthly_std_rate_cutoff:=0;
1079 l_weekly_std_rate_cutoff:=0;
1080 END IF;
1081 CLOSE cur_paye_dtl;
1082 */
1083
1084 -- Tax Credit and Cutoff are now fetched from Run Results 5128377
1085 -- Bug 5386432, since for terminated which does not have any element attached with FPD
1086 -- will have no child assignment actions. Call this only if child actions exists.
1087 IF p_payroll_child_actid IS NOT NULL THEN
1088 l_weekly_tax_credit := pay_ie_archive_detail_pkg.get_tax_details (
1089 p_run_assignment_action_id => p_payroll_child_actid
1090 ,p_input_value_id => g_week_tax_rate
1091 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
1092
1093 /* 5528450 */
1094 l_period_weekly_tax_credit := pay_ie_archive_detail_pkg.get_tax_details (
1095 p_run_assignment_action_id => p_payroll_child_actid
1096 ,p_input_value_id => g_period_week_tax_rate
1097 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
1098
1099 IF l_period_weekly_tax_credit IS NOT NULL THEN
1100 l_weekly_tax_credit := l_period_weekly_tax_credit;
1101 END IF;
1102
1103 l_monthly_tax_credit := pay_ie_archive_detail_pkg.get_tax_details (
1104 p_run_assignment_action_id => p_payroll_child_actid
1105 ,p_input_value_id => g_month_tax_rate
1106 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
1107
1108 l_monthly_std_rate_cutoff := pay_ie_archive_detail_pkg.get_tax_details (
1109 p_run_assignment_action_id => p_payroll_child_actid
1110 ,p_input_value_id => g_month_std_cutoff
1111 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
1112
1113 l_weekly_std_rate_cutoff := pay_ie_archive_detail_pkg.get_tax_details (
1114 p_run_assignment_action_id => p_payroll_child_actid
1115 ,p_input_value_id => g_week_std_cutoff
1116 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
1117
1118 /* 5528450 */
1119 l_period_weekly_std_cutoff := pay_ie_archive_detail_pkg.get_tax_details (
1120 p_run_assignment_action_id => p_payroll_child_actid
1121 ,p_input_value_id => g_period_week_std_cutoff
1122 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
1123
1124 IF l_period_weekly_std_cutoff IS NOT NULL THEN
1125 l_weekly_std_rate_cutoff := l_period_weekly_std_cutoff;
1126 END IF;
1127 END IF;
1128
1129 -- Bug 5386432, if no child assignment actions fetch values from
1130 -- PAYE table.
1131 IF p_payroll_child_actid IS NULL THEN
1132 open csr_get_paye_details;
1133 FETCH csr_get_paye_details into l_tax_basis,l_weekly_tax_credit,l_weekly_std_rate_cutoff,l_monthly_tax_credit,l_monthly_std_rate_cutoff;
1134 CLOSE csr_get_paye_details;
1135 END IF;
1136 ---
1137 -- Bug 5510536
1138 IF (l_period_type IN ('Bi-Week','Week','Lunar Month'))
1139 THEN
1140 open csr_number_per_year(l_period_type);
1141 FETCH csr_number_per_year into l_number_per_year;
1142 CLOSE csr_number_per_year;
1143 l_tax_credit :=l_weekly_tax_credit*l_number_per_year/52;
1144 l_std_rate_cut_off := l_weekly_std_rate_cutoff*l_number_per_year/52;
1145 ELSIF (l_period_type IN ('Bi-Month','Calendar Month','Quarter'))
1146 THEN
1147 open csr_number_per_year(l_period_type);
1148 FETCH csr_number_per_year into l_number_per_year;
1149 CLOSE csr_number_per_year;
1150 l_tax_credit :=l_monthly_tax_credit* l_number_per_year/12;
1151 l_std_rate_cut_off := l_monthly_std_rate_cutoff* l_number_per_year/12;
1152 END IF;
1153 -- end 5510536.
1154 hr_utility.set_location('l_tax_credit = ' || l_tax_credit,40);
1155 hr_utility.set_location('l_std_rate_cut_off = ' || l_std_rate_cut_off,40);
1156 IF l_tax_basis = 'C'
1157 THEN
1158 l_tax_basis_det := 'Cumulative';
1159 ELSIF l_tax_basis = 'N'
1160 THEN
1161 l_tax_basis_det := 'Non Cumulative';
1162 ELSE
1163 l_tax_basis_det := l_tax_basis;
1164 END IF;
1165
1166 --get the date of birth and separate name
1167 hr_utility.set_location('V_assignment_id = ' || p_assignment_id,40);
1168 hr_utility.set_location('V_date_earned = ' || p_date_earned,40);
1169
1170 OPEN cur_sep_name_dob(l_bg_id);
1171 FETCH cur_sep_name_dob INTO l_date_of_birth,l_first_name,l_last_name;
1172 CLOSE cur_sep_name_dob;
1173
1174 -- Fetching K, M and Total Insurable Weeks which needs to be stored
1175 -- with class name in l_prsi_cat
1176 open balance_id('IE PRSI K Term Insurable Weeks','_PER_PAYE_REF_YTD');
1177 fetch balance_id into k_defined_balance_id;
1178 close balance_id;
1179
1180 open balance_id('IE PRSI M Term Insurable Weeks','_PER_PAYE_REF_YTD');
1181 fetch balance_id into m_defined_balance_id;
1182 close balance_id;
1183
1184 open payroll_asg_action;
1185 fetch payroll_asg_action into pay_act_id;
1186 close payroll_asg_action;
1187
1188 -- Bug 3669639 : Changed the code so that l_prsi_cat is now
1189 -- previous classes concatenated with K or M
1190 -- Bug 5386432, since for terminated which does not have any element attached with FPD
1191 -- will have no child assignment actions. Call this only if child actions exists.
1192 IF pay_act_id IS NOT NULL THEN
1193 if pay_balance_pkg.get_value(k_defined_balance_id,pay_act_id,g_paye_ref,null,null,null,null,null) > 0 and
1194 l_prsi_cat is not NULL and l_prsi_count <> 2 then --Bug 4724788
1195 l_prsi_cat := l_prsi_cat ||', K';
1196 elsif pay_balance_pkg.get_value(k_defined_balance_id,pay_act_id,g_paye_ref,null,null,null,null,null) > 0 and
1197 l_prsi_cat is null then
1198 l_prsi_cat := 'K';
1199 end if;
1200
1201 if pay_balance_pkg.get_value(m_defined_balance_id,pay_act_id,g_paye_ref,null,null,null,null,null) > 0 and
1202 l_prsi_cat is not NULL and l_prsi_count <> 2 then --Bug 4724788
1203 l_prsi_cat := l_prsi_cat ||', M';
1204 elsif pay_balance_pkg.get_value(m_defined_balance_id,pay_act_id,g_paye_ref,null,null,null,null,null) > 0 and
1205 l_prsi_cat is null then
1206 l_prsi_cat := 'M';
1207 end if;
1208 END IF;
1209
1210 -- bug 5383808, Fetch value of latest hire date.
1211 --if p_last_p45_act IS NULL THEN
1212 OPEN comm_date_first;
1213 FETCH comm_date_first INTO l_commencement_date;
1214 CLOSE comm_date_first;
1215 /*else
1216 OPEN comm_date_last_p45;
1217 FETCH comm_date_last_p45 INTO l_commencement_date;
1218 CLOSE comm_date_last_p45;
1219 end if;*/
1220 -- end bug 5383808
1221 hr_utility.set_location('Archiving IE EMPLOYEE DETAILS',50);
1222 pay_action_information_api.create_action_information (
1223 p_action_information_id => l_action_info_id
1224 , p_action_context_id => p_assactid
1225 , p_action_context_type => 'AAP'
1226 , p_object_version_number => l_ovn
1227 , p_assignment_id => p_assignment_id
1228 , p_effective_date => g_archive_effective_date
1229 , p_source_id => NULL
1230 , p_source_text => NULL
1231 , p_action_information_category => 'IE EMPLOYEE DETAILS'
1232 , p_action_information1 => NULL
1233 , p_action_information2 => NULL
1234 , p_action_information3 => NULL
1235 , p_action_information21 => l_tax_basis_det
1236 , p_action_information22 => l_prsi_cat
1237 , p_action_information23 => l_prsi_subcat
1238 , p_action_information24 => l_ins_weeks
1239 , p_action_information25 => to_char(l_date_of_birth,'DD-MON-YYYY')
1240 , p_action_information26 => l_tax_credit
1241 , p_action_information27 => l_std_rate_cut_off
1242 , p_action_information28 => l_first_name
1243 , p_action_information29 => l_last_name
1244 , p_action_information30 => l_commencement_date);
1245 END archive_employee_details;
1246
1247 -----------------------------
1248
1249 PROCEDURE process_balance (p_action_context_id IN NUMBER,
1250 p_assignment_id IN NUMBER,
1251 p_person_id IN NUMBER,
1252 p_source_id IN NUMBER,
1253 p_effective_date IN DATE,
1254 p_balance IN VARCHAR2,
1255 p_dimension IN VARCHAR2,
1256 p_defined_bal_id IN NUMBER,
1257 p_record_count IN NUMBER,
1258 p_termination_date IN DATE,
1259 p_supp_flag IN VARCHAR2,
1260 p_last_p45_action IN NUMBER,
1261 p_last_p45_pact IN NUMBER, -- Bug 5005788
1262 p_prev_src_id IN NUMBER) -- p45 action locked by current P45 action.
1263 IS
1264 --
1265 -- Cursor for retrieving balance type id of defined balance
1266 --
1267 CURSOR csr_bal_type IS
1268 select balance_type_id
1269 from pay_defined_balances
1270 where defined_balance_id = p_defined_bal_id;
1271 -- Cursor for retrieving the summed run results for
1272 -- PRSI insurable weeks where Contribution Class
1273 -- starts with IE_A
1274 -- First part of the select retrives summed up values for Payroll runs,
1275 -- Second part of the select retrives summed up values for Uploaded Balance
1276 /* CURSOR csr_iea_weeks (p_balance_type_id in number) IS
1277 select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0) weeks
1278 from pay_run_result_values TARGET
1279 , pay_balance_feeds_f FEED
1280 , pay_run_results RR
1281 , pay_assignment_actions ASSACT
1282 , pay_assignment_actions BAL_ASSACT
1283 , pay_payroll_actions PACT
1284 , pay_payroll_actions BACT
1285 , per_time_periods PPTP
1286 , per_time_periods BPTP
1287 , pay_run_results PROCESS_RR
1288 , pay_run_result_values PROCESS
1289 , pay_input_values_f PROCESS_IV
1290 , pay_action_contexts ACX_PROCESS_ID
1291 , ff_contexts CON_PROCESS_ID
1292 where BAL_ASSACT.assignment_action_id = p_source_id
1293 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
1294 and FEED.balance_type_id +0 = p_balance_type_id
1295 and FEED.input_value_id = TARGET.input_value_id
1296 and nvl(TARGET.result_value,'0') <> '0'
1297 and TARGET.run_result_id = RR.run_result_id
1298 and RR.assignment_action_id = ASSACT.assignment_action_id
1299 and ASSACT.payroll_action_id = PACT.payroll_action_id
1300 and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date
1301 and RR.status in ('P','PA')
1302 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
1303 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
1304 and BPTP.payroll_id = BACT.payroll_id
1305 and BACT.date_earned between BPTP.start_date and BPTP.end_date
1306 and PPTP.payroll_id = PACT.payroll_id
1307 and PACT.date_earned between PPTP.start_date and PPTP.end_date
1308 and ASSACT.assignment_action_id = ACX_PROCESS_ID.assignment_action_id
1309 and ACX_PROCESS_ID.context_id = CON_PROCESS_ID.context_id
1310 and CON_PROCESS_ID.context_name = 'SOURCE_TEXT'
1311 and PROCESS.result_value = ACX_PROCESS_ID.context_value
1312 and PROCESS.run_result_id = PROCESS_RR.run_result_id
1313 and PROCESS_RR.assignment_action_id = ASSACT.assignment_action_id
1314 and PROCESS_RR.status in ('P','PA')
1315 and PROCESS.input_value_id = PROCESS_IV.input_value_id
1316 and PROCESS_IV.name = 'Contribution_Class'
1317 and PACT.effective_date between PROCESS_IV.effective_start_date and PROCESS_IV.effective_end_date
1318 and PACT.effective_date > to_date(to_char(PACT.effective_date, 'YYYY')||'01/01','YYYY/MM/DD')
1319 and ACX_PROCESS_ID.context_value like 'IE_A%'
1320 and PPTP.regular_payment_date >= trunc(BPTP.regular_payment_date,'Y')
1321 and RR.entry_type <>'B' -- Bug 3079945 start
1322 union all
1323 select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0) weeks
1324 from pay_run_result_values TARGET
1325 , pay_run_results RR
1326 , pay_assignment_actions ASSACT
1327 , pay_balance_feeds_f FEED
1328 where ASSACT.assignment_action_id in (select min(assignment_action_id) from
1329 pay_assignment_actions where assignment_id = p_assignment_id)
1330 and FEED.balance_type_id +0 = p_balance_type_id
1331 and FEED.input_value_id = TARGET.input_value_id
1332 and nvl(TARGET.result_value,'0') <> '0'
1333 and TARGET.run_result_id = RR.run_result_id
1334 and RR.assignment_action_id = ASSACT.assignment_action_id
1335 and RR.status in ('P','PA')
1336 and RR.entry_type = 'B';*/
1337 --v_csr_iea_weeks csr_iea_weeks%ROWTYPE;
1338 -- Bug 3079945 End
1339 -- Cursor for retrieving the context and source id for
1340 -- payroll runs having prsi contribution class as A
1341 -- in the same tax year for which the archive is run
1342 /*CURSOR Cur_Act_Contexts IS
1343 SELECT pac.Context_ID,pac.Context_Value,pac.Assignment_action_id
1344 FROM pay_action_contexts pac,pay_assignment_actions pas,
1345 pay_payroll_actions ppa,pay_payroll_actions appa
1346 WHERE pac.Context_Value = 'IE_A'
1347 AND pac.assignment_id = p_assignment_id
1348 AND pas.assignment_action_id = pac.assignment_action_id
1349 AND ppa.payroll_action_id = pas.payroll_action_id
1350 And appa.payroll_action_id = g_archive_pact
1351 AND to_char(appa.date_earned,'YYYY') = to_char(ppa.date_earned,'YYYY')
1352 AND pac.assignment_action_id = (SELECT MAX(assignment_action_id)
1353 FROM pay_action_contexts
1354 WHERE Context_Value = 'IE_A'
1355 AND assignment_id = p_assignment_id);
1356 v_Cur_Act_Contexts Cur_Act_Contexts%ROWTYPE;*/
1357 --Bug 3079945 Start
1358 -- Cursor for retrieving the summed values for
1359 -- PRSI insurable weeks where Contribution Class
1360 -- starts with IE_A%
1361 l_prev_sequence number;
1362 l_current_sequence number;
1363
1364 -- for bug 5383808, to get the action_sequence of run locked by
1365 -- the previous P45 archive.
1366 CURSOR cur_get_prev_run_seq is
1367 select paa.action_sequence
1368 from pay_assignment_actions paa,
1369 pay_payroll_actions ppa,
1370 pay_action_interlocks pai,
1371 pay_assignment_actions paa1
1372 where paa1.source_action_id = p_last_p45_action
1373 and pai.locking_action_id = paa1.assignment_action_id
1374 and pai.locked_action_id = paa.assignment_action_id
1375 and paa.assignment_id in (select papf.assignment_id
1376 from per_all_assignments_f papf
1377 where papf.person_id = p_person_id
1378 )
1379 and paa.tax_unit_id = g_paye_ref
1380 and paa.payroll_action_id = ppa.payroll_action_id
1381 and ppa.action_type in ('R','Q','I','B','V');
1382
1383 -- for bug 5383808, get the action_sequence of run locked by
1384 -- current p45 archive.
1385 CURSOR cur_get_curr_run_seq is
1386 select action_sequence
1387 from pay_assignment_actions ppa
1388 where assignment_action_id = p_source_id;
1389
1390 --bug 5383808. IF previous P45 exists fetch the sum of PRSI insurable
1391 -- weeks for class between the current run action sequence locked by P45
1392 -- and run action locked by previous P45.
1393 CURSOR Cur_Act_Contexts(l_defined_bal_id number) IS
1394 SELECT sum(PAY_BALANCE_PKG.GET_VALUE(l_defined_bal_id, -- changes made
1395 pac.ASSIGNMENT_ACTION_ID,
1396 g_paye_ref,
1397 null,
1398 pac.CONTEXT_ID,
1399 pac.CONTEXT_VALUE,
1400 null,
1401 null))
1402 FROM pay_action_contexts pac,
1403 pay_assignment_actions pas,
1404 pay_payroll_actions ppa
1405 WHERE pac.Context_Value like 'IE_A%'
1406 AND pac.assignment_id in (select papf.assignment_id
1407 from per_all_assignments_f papf
1408 where papf.person_id = p_person_id
1409 )
1410 AND pas.tax_unit_id = g_paye_ref
1411 AND pas.assignment_action_id = pac.assignment_action_id
1412 AND ppa.payroll_action_id = pas.payroll_action_id
1413 /*AND ppa.date_earned between to_date('01-01-' || to_char(g_archive_start_date ,'YYYY'),'DD-MM-YYYY') --Bug Fix 3986250*/
1414 AND ppa.effective_date between to_date('01-01-' || to_char(p_effective_date,'YYYY'),'DD-MM-YYYY') --Bug fix 4108423
1415 AND g_archive_end_date
1416 and pas.action_sequence > l_prev_sequence
1417 and pas.action_sequence <= l_current_sequence;
1418 /* group by pac.context_id,pac.context_value;*/
1419
1420 -- cursor to get defined balance id
1421 -- bug 5383808
1422 cursor csr_defined_bal_id(p_balance_name varchar2) is
1423 select defined_balance_id
1424 from pay_balance_types pbt,
1425 pay_balance_dimensions pbd,
1426 pay_defined_balances pdb
1427 where pbt.balance_name = p_balance_name
1428 and pbt.balance_type_id = pdb.balance_type_id
1429 and pbd.database_item_suffix = '_ASG_PAYE_REF_PRSI_RUN'
1430 and pbd.balance_dimension_id = pdb.balance_dimension_id
1431 and pbt.legislation_code = 'IE'
1432 and pbd.legislation_code = 'IE';
1433
1434 -- get balance from EMEA balances
1435 cursor get_prev_ins_bal is
1436 SELECT to_number(pai.action_information4) balance_value
1437 FROM pay_action_information pai
1438 WHERE pai.action_context_id = p_last_p45_action
1439 AND pai.action_information_category = 'EMEA BALANCES'
1440 AND pai.action_information1 = p_defined_bal_id;
1441
1442
1443 v_Cur_Act_Contexts Cur_Act_Contexts%ROWTYPE;
1444 --Bug 3079945 End
1445 l_action_info_id NUMBER;
1446 l_balance_value NUMBER:=0;
1447 l_balance_value_classA NUMBER;
1448 l_ovn NUMBER;
1449 l_record_count VARCHAR2(10);
1450 l_proc VARCHAR2(50) := g_package || 'process_balance';
1451 l_balance_type_id NUMBER;
1452 l_balance_value1 NUMBER:=0;
1453 l_balance_value2 NUMBER :=0;
1454 --bug 5383808
1455 l_p45_last_bal_value NUMBER :=0;
1456 l_defined_id NUMBER;
1457 l_pre_ins_bal number;
1458 --6615117
1459 l_source_null_flag NUMBER:= 0;
1460
1461 BEGIN
1462 -- hr_utility.trace_on(null,'P45');
1463 hr_utility.set_location('Entering ' || l_proc,10);
1464 hr_utility.set_location('Step ' || l_proc,20);
1465 hr_utility.set_location('p_source_id = ' || p_source_id,20);
1466 hr_utility.set_location('p_balance = ' || p_balance,20);
1467 hr_utility.set_location('p_dimension = ' || p_dimension,20);
1468 hr_utility.set_location('p_defined_bal_id = ' || p_defined_bal_id,20);
1469
1470 IF p_balance = 'IE PRSI_ClassA Insurable Weeks'
1471 THEN
1472 --bug 5383808
1473 OPEN cur_get_prev_run_seq;
1474 FETCH cur_get_prev_run_seq into l_prev_sequence;
1475 CLOSE cur_get_prev_run_seq;
1476
1477 IF l_prev_sequence IS NULL THEN
1478 l_prev_sequence := 0;
1479 END IF;
1480
1481 open cur_get_curr_run_seq;
1482 fetch cur_get_curr_run_seq into l_current_sequence;
1483 CLOSE cur_get_curr_run_seq;
1484
1485 if l_current_sequence is null then
1486 l_current_sequence := 0;
1487 end if;
1488
1489 /*OPEN csr_bal_type;
1490 FETCH csr_bal_type into l_balance_type_id;
1491 CLOSE csr_bal_type;*/
1492
1493
1494 open csr_defined_bal_id('IE PRSI Insurable Weeks');
1495 FETCH csr_defined_bal_id into l_defined_id;
1496 CLOSE csr_defined_bal_id;
1497
1498 hr_utility.set_location ('l_prev_sequence..'||l_prev_sequence,200);
1499 hr_utility.set_location ('l_current_sequence..'||l_current_sequence,200);
1500 hr_utility.set_location ('l_defined_id..'||l_defined_id,200);
1501 --Commented code for bug fix 3079945
1502 /*OPEN csr_iea_weeks(l_balance_type_id);
1503 FETCH csr_iea_weeks into l_balance_value;
1504 CLOSE csr_iea_weeks; */
1505 /*OPEN Cur_Act_Contexts;
1506 FETCH Cur_Act_Contexts INTO v_Cur_Act_Contexts;
1507 IF Cur_Act_Contexts%FOUND
1508 THEN
1509 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1510 v_Cur_Act_Contexts.ASSIGNMENT_ACTION_ID,
1511 null,
1512 null,
1513 v_Cur_Act_Contexts.CONTEXT_ID,
1514 v_Cur_Act_Contexts.CONTEXT_VALUE,
1515 null,
1516 null);
1517 END IF;
1518 CLOSE Cur_Act_Contexts;*/
1519
1520 --Bug 3079945 start
1521 OPEN Cur_Act_Contexts(l_defined_id);
1522 FETCH Cur_Act_Contexts into l_balance_value;
1523 CLOSE Cur_Act_Contexts;
1524 --Bug 3079945 End
1525 /*IF nvl(p_supp_flag,'N') = 'N' AND (p_last_p45_action IS NOT NULL) then
1526 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1527 p_last_p45_action,
1528 g_paye_ref,
1529 null,
1530 null,
1531 null,
1532 null,
1533 null);
1534 l_balance_value := l_balance_value - l_p45_last_bal_value;
1535 END IF;*/
1536 l_balance_value := nvl(l_balance_value,0);
1537 hr_utility.set_location('IE PRSI_ClassA Insurable Weeks..'||l_balance_value,1000);
1538 IF p_supp_flag = 'Y' AND p_last_p45_action IS NOT NULL THEN
1539 open get_prev_ins_bal;
1540 FETCH get_prev_ins_bal into l_pre_ins_bal;
1541 CLOSE get_prev_ins_bal;
1542 l_balance_value := l_balance_value + l_pre_ins_bal;
1543 END IF;
1544 hr_utility.set_location('After IE PRSI_ClassA Insurable Weeks..'||l_pre_ins_bal,1000.1);
1545 ELSE
1546 /* l_balance_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1547 p_source_id );
1548 */
1549 --bug 5383808, call this only p_source_id is not null.
1550 if p_source_id is not null then
1551 l_balance_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1552 p_source_id,
1553 g_paye_ref,
1554 null,
1555 null,
1556 null,
1557 null,
1558 null);
1559 --6615117
1560 Else
1561 l_source_null_flag := 1;
1562 end if;
1563
1564 hr_utility.set_location('sg Supp Flag ='||p_supp_flag,36);
1565 hr_utility.set_location('sg Last P45 Action ='||p_last_p45_action,37);
1566 --bug 5383808
1567 IF (p_balance like 'IE Taxable Pay') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1568 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1569 p_prev_src_id,
1570 g_paye_ref,
1571 null,
1572 null,
1573 null,
1574 null,
1575 null);
1576
1577 hr_utility.set_location('before IE Taxable Pay '||l_balance_value,204);
1578 hr_utility.set_location('before IE Taxable Pay '||l_p45_last_bal_value,205);
1579 hr_utility.set_location('p_last_p45_action '||p_last_p45_action,206);
1580 hr_utility.set_location('p_defined_bal_id '||p_defined_bal_id,207);
1581 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1582 --6615117
1583 IF l_source_null_flag = 1 THEN
1584 l_balance_value:= l_balance_value;
1585 ELSE
1586 l_balance_value := l_balance_value - l_p45_last_bal_value;
1587 END IF;
1588
1589 END IF;
1590
1591
1592 IF (p_balance like 'IE P45 Pay') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1593 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1594 p_prev_src_id,
1595 g_paye_ref,
1596 null,
1597 null,
1598 null,
1599 null,
1600 null);
1601
1602 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1603 --6615117
1604 IF l_source_null_flag = 1 THEN
1605 l_balance_value:= l_balance_value;
1606 ELSE
1607 l_balance_value := l_balance_value - l_p45_last_bal_value;
1608 END IF;
1609 END IF;
1610
1611 IF (p_balance like 'IE P45 Tax Deducted') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1612 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1613 p_prev_src_id,
1614 g_paye_ref,
1615 null,
1616 null,
1617 null,
1618 null,
1619 null);
1620
1621 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1622 --6615117
1623 IF l_source_null_flag = 1 THEN
1624 l_balance_value:= l_balance_value;
1625 ELSE
1626 l_balance_value := l_balance_value - l_p45_last_bal_value;
1627 END IF;
1628 END IF;
1629
1630 IF (p_balance like 'IE Net Tax') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1631 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1632 p_prev_src_id,
1633 g_paye_ref,
1634 null,
1635 null,
1636 null,
1637 null,
1638 null);
1639
1640 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1641 --6615117
1642 IF l_source_null_flag = 1 THEN
1643 l_balance_value:= l_balance_value;
1644 ELSE
1645 l_balance_value := l_balance_value - l_p45_last_bal_value;
1646 END IF;
1647 END IF;
1648 IF (p_balance like 'IE PRSI Insurable Weeks') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1649 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1650 p_prev_src_id,
1651 g_paye_ref,
1652 null,
1653 null,
1654 null,
1655 null,
1656 null);
1657
1658 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1659 --6615117
1660 IF l_source_null_flag = 1 THEN
1661 l_balance_value:= l_balance_value;
1662 ELSE
1663 l_balance_value := l_balance_value - l_p45_last_bal_value;
1664 END IF;
1665 END IF;
1666
1667 -- changes made for PRSI bug 5510536. To show this employment figures only.
1668 IF (p_balance like 'IE PRSI Employer') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1669 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1670 p_prev_src_id,
1671 g_paye_ref,
1672 null,
1673 null,
1674 null,
1675 null,
1676 null);
1677
1678 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1679 --6615117
1680 IF l_source_null_flag = 1 THEN
1681 l_balance_value:= l_balance_value;
1682 ELSE
1683 l_balance_value := l_balance_value - l_p45_last_bal_value;
1684 END IF;
1685 END IF;
1686
1687 IF (p_balance like 'IE PRSI K Employer Lump Sum') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1688 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1689 p_prev_src_id,
1690 g_paye_ref,
1691 null,
1692 null,
1693 null,
1694 null,
1695 null);
1696
1697 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1698 --6615117
1699 IF l_source_null_flag = 1 THEN
1700 l_balance_value:= l_balance_value;
1701 ELSE
1702 l_balance_value := l_balance_value - l_p45_last_bal_value;
1703 END IF;
1704 END IF;
1705
1706 IF (p_balance like 'IE PRSI M Employer Lump Sum') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1707 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1708 p_prev_src_id,
1709 g_paye_ref,
1710 null,
1711 null,
1712 null,
1713 null,
1714 null);
1715
1716 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1717 --6615117
1718 IF l_source_null_flag = 1 THEN
1719 l_balance_value:= l_balance_value;
1720 ELSE
1721 l_balance_value := l_balance_value - l_p45_last_bal_value;
1722 END IF;
1723 END IF;
1724
1725 IF (p_balance like 'IE PRSI Employee') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1726 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1727 p_prev_src_id,
1728 g_paye_ref,
1729 null,
1730 null,
1731 null,
1732 null,
1733 null);
1734
1735 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1736 --6615117
1737 IF l_source_null_flag = 1 THEN
1738 l_balance_value:= l_balance_value;
1739 ELSE
1740 l_balance_value := l_balance_value - l_p45_last_bal_value;
1741 END IF;
1742 END IF;
1743
1744 IF (p_balance like 'IE PRSI K Employee Lump Sum') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1745 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1746 p_prev_src_id,
1747 g_paye_ref,
1748 null,
1749 null,
1750 null,
1751 null,
1752 null);
1753
1754 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1755 --6615117
1756 IF l_source_null_flag = 1 THEN
1757 l_balance_value:= l_balance_value;
1758 ELSE
1759 l_balance_value := l_balance_value - l_p45_last_bal_value;
1760 END IF;
1761 END IF;
1762
1763 IF (p_balance like 'IE PRSI M Employee Lump Sum') AND (nvl(p_supp_flag,'N') = 'N') AND (p_last_p45_action IS NOT NULL) THEN
1764 l_p45_last_bal_value := PAY_BALANCE_PKG.GET_VALUE(p_defined_bal_id,
1765 p_prev_src_id,
1766 g_paye_ref,
1767 null,
1768 null,
1769 null,
1770 null,
1771 null);
1772
1773 --l_balance_value := l_balance_value - l_p45_last_bal_value;
1774 --6615117
1775 IF l_source_null_flag = 1 THEN
1776 l_balance_value:= l_balance_value;
1777 ELSE
1778 l_balance_value := l_balance_value - l_p45_last_bal_value;
1779 END IF;
1780 END IF;
1781
1782 END IF;
1783
1784 -- end changes made for PRSI
1785
1786 --end bug 5383808
1787 hr_utility.set_location('l_balance_value = ' || l_balance_value,20);
1788
1789 IF p_record_count = 0
1790 THEN
1791 l_record_count := NULL;
1792 ELSE
1793 l_record_count := p_record_count + 1;
1794 END IF;
1795
1796 IF l_balance_value <> 0
1797 THEN
1798 hr_utility.set_location('Archiving EMEA BALANCES',20);
1799 pay_action_information_api.create_action_information (
1800 p_action_information_id => l_action_info_id
1801 , p_action_context_id => p_action_context_id
1802 , p_action_context_type => 'AAP'
1803 , p_object_version_number => l_ovn
1804 , p_assignment_id => p_assignment_id
1805 , p_effective_date => p_effective_date
1806 , p_source_id => p_source_id
1807 , p_source_text => NULL
1808 , p_action_information_category => 'EMEA BALANCES'
1809 , p_action_information1 => p_defined_bal_id
1810 , p_action_information2 => NULL
1811 , p_action_information3 => NULL
1812 , p_action_information4 => l_balance_value
1813 , p_action_information5 => l_record_count);
1814 END IF;
1815
1816 hr_utility.set_location('Leaving ' || l_proc,30);
1817 EXCEPTION
1818 WHEN NO_DATA_FOUND
1819 THEN
1820 NULL;
1821 END process_balance;
1822 ---------------------
1823 PROCEDURE process_supp_balance (p_action_context_id IN NUMBER,
1824 p_assignment_id IN NUMBER,
1825 p_person_id IN NUMBER,
1826 p_source_id IN NUMBER,
1827 p_effective_date IN DATE,
1828 p_balance IN VARCHAR2,
1829 p_dimension IN VARCHAR2,
1830 p_defined_bal_id IN NUMBER,
1831 p_record_count IN NUMBER,
1832 p_termination_date IN DATE,
1833 p_supp_flag IN VARCHAR2,
1834 p_last_p45_action IN NUMBER,
1835 p_last_p45_pact IN NUMBER, -- Bug 5005788
1836 p_ytd_balance IN VARCHAR2,
1837 p_ytd_def_bal_id IN NUMBER)
1838 IS
1839 --
1840 -- Cursor for retrieving balance type id of defined balance
1841 --
1842 CURSOR csr_bal_type IS
1843 select balance_type_id
1844 from pay_defined_balances
1845 where defined_balance_id = p_defined_bal_id;
1846
1847 CURSOR csr_get_curr_val(p_action_context_id NUMBER,p_def_bal_id NUMBER) IS
1848 SELECT to_number(pai.action_information4) balance_value
1849 FROM pay_action_information pai
1850 WHERE pai.action_context_id = p_action_context_id
1851 AND pai.action_information_category = 'EMEA BALANCES'
1852 AND pai.action_information1 = p_def_bal_id;
1853
1854 -- cursor to fetch source_id from the last p45 action
1855 CURSOR get_last_source_id is
1856 select source_id from
1857 pay_action_information pai,
1858 pay_assignment_actions paa
1859 where paa.assignment_action_id = p_last_p45_action
1860 and paa.assignment_action_id = pai.action_context_id
1861 and pai.action_information_category='EMEA BALANCES';
1862
1863
1864 --Bug 3079945 End
1865 l_action_info_id NUMBER;
1866 l_balance_value NUMBER:=0;
1867 l_balance_value_classA NUMBER;
1868 l_ovn NUMBER;
1869 l_record_count VARCHAR2(10);
1870 l_proc VARCHAR2(50) := g_package || 'process_supp_balance';
1871 l_balance_type_id NUMBER;
1872 l_balance_value1 NUMBER:=0;
1873 l_prev_source_id number;
1874 BEGIN
1875 hr_utility.set_location('Entering ' || l_proc,10);
1876 hr_utility.set_location('Step ' || l_proc,20);
1877 hr_utility.set_location('p_source_id = ' || p_source_id,20);
1878 hr_utility.set_location('p_balance = ' || p_balance,20);
1879 hr_utility.set_location('p_dimension = ' || p_dimension,20);
1880 hr_utility.set_location('p_defined_bal_id = ' || p_defined_bal_id,20);
1881
1882 hr_utility.set_location('p_last_p45_action = ' || p_last_p45_action,20);
1883 hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
1884 hr_utility.set_location('p_last_p45_pact = ' || p_last_p45_pact,20);
1885 hr_utility.set_location('p_ytd_balance = ' || p_ytd_balance,20);
1886 hr_utility.set_location('p_ytd_def_bal_id = ' || p_ytd_def_bal_id,20);
1887
1888 OPEN csr_get_curr_val(p_action_context_id,p_ytd_def_bal_id);
1889 FETCH csr_get_curr_val INTO l_balance_value;
1890 CLOSE csr_get_curr_val;
1891 hr_utility.set_location('l_balance_value = ' || l_balance_value,20);
1892
1893 -- l_balance_value := get_arc_bal_value(p_action_context_id,p_ytd_balance);
1894
1895 IF p_last_p45_action IS NOT NULL THEN
1896 -- commented by vikas
1897 if p_balance = 'IE PRSI_ClassA Insurable WeeksASG_PTD' then
1898 l_balance_value1 := get_arc_bal_value(p_last_p45_action,p_last_p45_pact,p_ytd_balance); -- Bug 5005788
1899 ELSE
1900 OPEN get_last_source_id;
1901 FETCH get_last_source_id into l_prev_source_id;
1902 CLOSE get_last_source_id;
1903 l_balance_value1 := PAY_BALANCE_PKG.GET_VALUE(p_ytd_def_bal_id,
1904 l_prev_source_id,
1905 g_paye_ref,
1906 null,
1907 null,
1908 null,
1909 null,
1910 null);
1911
1912 END IF;
1913 END IF;
1914 hr_utility.set_location('l_balance_value1 = ' || l_balance_value1,20);
1915
1916 l_balance_value := l_balance_value - l_balance_value1;
1917
1918 IF p_record_count = 0
1919 THEN
1920 l_record_count := NULL;
1921 ELSE
1922 l_record_count := p_record_count + 1;
1923 END IF;
1924 IF l_balance_value <> 0
1925 THEN
1926 hr_utility.set_location('Archiving EMEA BALANCES',20);
1927 pay_action_information_api.create_action_information (
1928 p_action_information_id => l_action_info_id
1929 , p_action_context_id => p_action_context_id
1930 , p_action_context_type => 'AAP'
1931 , p_object_version_number => l_ovn
1932 , p_assignment_id => p_assignment_id
1933 , p_effective_date => p_effective_date
1934 , p_source_id => p_source_id
1935 , p_source_text => NULL
1936 , p_action_information_category => 'EMEA BALANCES'
1937 , p_action_information1 => p_defined_bal_id
1938 , p_action_information2 => NULL
1939 , p_action_information3 => NULL
1940 , p_action_information4 => l_balance_value
1941 , p_action_information5 => l_record_count);
1942 END IF;
1943 hr_utility.set_location('Leaving ' || l_proc,30);
1944 EXCEPTION
1945 WHEN NO_DATA_FOUND
1946 THEN
1947 NULL;
1948 END process_supp_balance;
1949
1950 --------------------------------------------------------------------------------
1951 -- To get the termination date and supplement flag
1952 -- for bug 5383808
1953 --------------------------------------------------------------------------------
1954 PROCEDURE get_termination_date (p_action_context_id IN NUMBER,
1955 p_assignment_id IN NUMBER,
1956 p_person_id IN NUMBER,
1957 p_date_earned IN DATE,
1958 p_termination_date OUT NOCOPY DATE,
1959 p_supp_pymt_date OUT NOCOPY DATE,
1960 p_supp_flag OUT NOCOPY VARCHAR2,
1961 p_deceased_flag OUT NOCOPY VARCHAR2
1962 ) is
1963
1964 CURSOR cur_service_leave IS
1965 select decode(ppos.leaving_reason, 'D','Y','N'),
1966 ppos.actual_termination_date
1967 from per_periods_of_service ppos
1968 where ppos.person_id = p_person_id
1969 and ppos.period_of_service_id = (select max(paf.period_of_service_id)
1970 from per_all_assignments_f paf,
1971 pay_assignment_actions paa,
1972 pay_action_interlocks pai
1973 where pai.locking_action_id = p_action_context_id
1974 and pai.locked_action_id = paa.assignment_action_id
1975 and paa.action_status = 'C'
1976 and paa.assignment_id = paf.assignment_id
1977 );
1978
1979 CURSOR cur_max_end_date IS
1980 SELECT max(paaf.effective_end_date)
1981 FROM per_all_assignments_f paaf,
1982 pay_all_payrolls_f papf,
1983 hr_soft_coding_keyflex scl
1984 WHERE paaf.person_id = p_person_id
1985 AND paaf.payroll_id = papf.payroll_id
1986 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
1987 AND scl.segment4 = to_char(g_paye_ref)
1988 AND paaf.assignment_status_type_id in
1989 (SELECT ast.assignment_status_type_id
1990 FROM per_assignment_status_types ast
1991 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
1992 )
1993 AND paaf.effective_end_date between g_archive_start_date and g_archive_end_date;
1994
1995 /* changed the cursor to handle case where 2 user defined assignment status exist mapping to
1996 same per_system_status (5073577) */
1997 CURSOR cur_get_asg_end_date IS
1998 SELECT max(effective_end_date)
1999 FROM per_all_assignments_f paaf
2000 WHERE paaf.assignment_id = p_assignment_id
2001 AND paaf.assignment_status_type_id in
2002 (SELECT ast.assignment_status_type_id
2003 FROM per_assignment_status_types ast
2004 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2005 );
2006
2007
2008
2009 cursor cur_supp_run is
2010 select act_inf.action_information3
2011 from pay_assignment_actions paa_run,
2012 pay_action_interlocks pai,
2013 pay_assignment_actions paa,
2014 pay_payroll_actions ppa,
2015 pay_action_information act_inf
2016 where ppa.payroll_action_id = paa.payroll_action_id
2017 and ppa.report_type = 'P45'
2018 and ppa.report_qualifier = 'IE'
2019 and ppa.action_type = 'X'
2020 and paa.assignment_action_id = act_inf.action_context_id
2021 and act_inf.action_information_category = 'IE P45 INFORMATION'
2022 and act_inf.action_context_type = 'AAP'
2023 and ppa.payroll_action_id <> g_archive_pact
2024 and paa.assignment_action_id = pai.locking_action_id
2025 and paa.source_action_id is NULL
2026 and pai.locked_action_id = paa_run.assignment_action_id
2027 and paa_run.assignment_id = p_assignment_id
2028 and paa_run.action_status = 'C'
2029 and paa.action_status = 'C';
2030
2031
2032 l_proc CONSTANT VARCHAR2(50):= g_package||'get_termination_date';
2033 l_deceased_flg VARCHAR2(1);
2034 l_termination_date DATE;
2035 l_start_date DATE;
2036 l_end_date DATE;
2037 l_asg_end_date DATE;
2038 l_last_end_date DATE;
2039
2040 BEGIN
2041 hr_utility.set_location('Entering ' || l_proc,20);
2042 hr_utility.set_location('Step ' || l_proc,20);
2043 hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
2044 hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
2045 hr_utility.set_location('p_person_id = ' || p_person_id,20);
2046 hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
2047 hr_utility.set_location('p_termination_date = ' || p_termination_date,20);
2048
2049
2050
2051 -- get deceased flag, date of leaving
2052 OPEN cur_service_leave;
2053 FETCH cur_service_leave INTO l_deceased_flg,l_termination_date;
2054 CLOSE cur_service_leave;
2055
2056 -- Copied to out variable (5600150)
2057 p_deceased_flag := l_deceased_flg;
2058
2059 l_asg_end_date := l_termination_date;
2060 hr_utility.set_location('l_termination_date = ' || l_termination_date,21);
2061
2062 /* If employee is not terminated using end employment check for asg end date */
2063 IF l_termination_date IS NULL THEN
2064 /* Get End Date of Employement with Employer */
2065 OPEN cur_max_end_date;
2066 FETCH cur_max_end_date INTO l_termination_date;
2067 CLOSE cur_max_end_date;
2068 /* Get End Date of Assignment */
2069 OPEN cur_get_asg_end_date;
2070 FETCH cur_get_asg_end_date INTO l_asg_end_date;
2071 CLOSE cur_get_asg_end_date;
2072 END IF;
2073 hr_utility.set_location('l_termination_date = ' || l_termination_date,22);
2074 p_termination_date := l_termination_date;
2075 OPEN cur_supp_run;
2076 FETCH cur_supp_run INTO l_last_end_date;
2077 hr_utility.set_location('l_last_end_date = '|| l_last_end_date,20);
2078 IF l_last_end_date IS NOT NULL THEN
2079 --IF l_report_type_count >= 1 THEN
2080 p_supp_pymt_date := p_date_earned;
2081 p_supp_flag:= 'Y';
2082 p_termination_date := l_last_end_date;
2083 ELSE
2084 p_supp_flag:= 'N';
2085 p_supp_pymt_date :=null;
2086 END IF;
2087 END get_termination_date;
2088
2089 ------------------------------------------------------------
2090 -- for bug 5383808, made the p_supp_flag,p_termination_date and
2091 -- p_supp_pymt_date as in parameters.
2092 -- added p_deceased_flag as out variable 5600150
2093 PROCEDURE archive_p45_info(p_action_context_id IN NUMBER,
2094 p_assignment_id IN NUMBER,
2095 p_payroll_id IN NUMBER,
2096 p_date_earned IN DATE,
2097 p_child_run_ass_act_id IN NUMBER,
2098 p_supp_flag IN VARCHAR2, -- 5383808
2099 p_person_id IN NUMBER,
2100 p_termination_date in DATE, -- 5383808
2101 p_child_pay_action IN NUMBER,
2102 p_supp_pymt_date IN DATE,
2103 p_deceased_flag IN VARCHAR2
2104 ) -- 5383808
2105 IS
2106 l_action_info_id NUMBER(15);
2107 l_proc CONSTANT VARCHAR2(50):= g_package||'archive_p45_info';
2108 l_ovn NUMBER;
2109 l_deceased_flg VARCHAR2(1);
2110 l_termination_date DATE;
2111 l_period_num NUMBER;
2112 l_calculation_option VARCHAR2(15);
2113 l_non_cum_tax VARCHAR2(20);
2114 l_noncum_ben_operated VARCHAR2(5);
2115 l_emer_tax_operated VARCHAR2(1);
2116 l_defined_balance_id NUMBER;
2117 l_emer_num NUMBER;
2118 l_emer_basis_flg VARCHAR2(1);
2119 l_supp_flg VARCHAR2(1);
2120 l_report_type_count NUMBER;
2121 l_supp_pymt_date DATE;
2122 l_number_per_fiscal_year NUMBER;
2123 l_periods_per_period NUMBER;
2124 l_start_date DATE;
2125 l_end_date DATE;
2126 l_p45_period_num NUMBER;
2127 -- Bug 2943335
2128 l_balance_name varchar2(80);
2129 l_soc_ben_defined_bal_id NUMBER;
2130 l_disability_ben_amount NUMBER;
2131 l_soc_ben_amount NUMBER;
2132 l_asg_end_date DATE;
2133 l_last_end_date DATE;
2134
2135 -- variable used to fetch Tax Basis 5128377
2136 l_tax_basis VARCHAR2(20);
2137 --
2138 CURSOR cur_service_leave IS
2139 select decode(ppos.leaving_reason, 'D','Y','N'),
2140 ppos.actual_termination_date
2141 from per_periods_of_service ppos
2142 where ppos.person_id = p_person_id
2143 and ppos.period_of_service_id = (select max(paf.period_of_service_id)
2144 from per_all_assignments_f paf,
2145 pay_assignment_actions paa,
2146 pay_action_interlocks pai
2147 where pai.locking_action_id = p_action_context_id
2148 and pai.locked_action_id = paa.assignment_action_id
2149 and paa.action_status = 'C'
2150 and paa.assignment_id = paf.assignment_id
2151 );
2152 /*
2153 SELECT decode(ppos.leaving_reason, 'D','Y','N'),
2154 ppos.actual_termination_date
2155 FROM per_periods_of_service ppos,
2156 per_all_assignments_f paf
2157 WHERE paf.assignment_id = p_assignment_id
2158 AND ppos.period_of_service_id = paf.period_of_service_id;
2159 */
2160 /* changed the cursor to handle case where 2 user defined assignment status exist mapping to
2161 same per_system_status (5073577) */
2162 CURSOR cur_max_end_date IS
2163 SELECT max(paaf.effective_end_date)
2164 FROM per_all_assignments_f paaf,
2165 pay_all_payrolls_f papf,
2166 hr_soft_coding_keyflex scl
2167 WHERE paaf.person_id = p_person_id
2168 AND paaf.payroll_id = papf.payroll_id
2169 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
2170 AND scl.segment4 = to_char(g_paye_ref)
2171 AND paaf.assignment_status_type_id in
2172 (SELECT ast.assignment_status_type_id
2173 FROM per_assignment_status_types ast
2174 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2175 )
2176 AND paaf.effective_end_date between g_archive_start_date and g_archive_end_date;
2177
2178 /* changed the cursor to handle case where 2 user defined assignment status exist mapping to
2179 same per_system_status (5073577) */
2180 CURSOR cur_get_asg_end_date IS
2181 SELECT max(effective_end_date)
2182 FROM per_all_assignments_f paaf
2183 WHERE paaf.assignment_id = p_assignment_id
2184 AND paaf.assignment_status_type_id in
2185 (SELECT ast.assignment_status_type_id
2186 FROM per_assignment_status_types ast
2187 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2188 );
2189
2190 -- Bug 2943335 used balance name as a parameter
2191 CURSOR cur_defined_balance(l_balance_name IN varchar2 ) IS
2192 SELECT pdb.defined_balance_id
2193 FROM pay_balance_types pbt,
2194 pay_balance_dimensions pbd,
2195 pay_defined_balances pdb
2196 WHERE pdb.balance_type_id = pbt.balance_type_id
2197 AND pdb.balance_dimension_id = pbd.balance_dimension_id
2198 AND UPPER(pbt.balance_name) = UPPER(l_balance_name)
2199 AND pbd.database_item_suffix = '_PER_PAYE_REF_YTD';
2200 -- Bug
2201 cursor cur_period_num is
2202 select ptp.period_num,
2203 ptpt.number_per_fiscal_year,
2204 ptpr.periods_per_period,
2205 ptp.start_date,
2206 ptp.end_date
2207 from per_all_assignments_f paf,
2208 per_time_periods ptp,
2209 per_time_period_types ptpt,
2210 per_time_period_rules ptpr
2211 where paf.assignment_id = p_assignment_id
2212 and p_date_earned between paf.effective_start_date
2213 and paf.effective_end_date
2214 and paf.payroll_id = ptp.payroll_id
2215 and p_date_earned between ptp.start_date and ptp.end_date
2216 and ptp.period_type = ptpt.period_type
2217 and ptpt.number_per_fiscal_year = ptpr.number_per_fiscal_year;
2218
2219 --Bug:2450336
2220 -- Bug 2943335 commented unwanted cursor
2221 /* cursor cur_cal_option is
2222 select calculation_option
2223 from pay_ie_social_benefits_f psb
2224 where psb.assignment_id = p_assignment_id; */
2225 -- Bug 4315023 Removed Pay_element_types_f join for performance
2226 cursor cur_non_cum_tax is
2227 select result_value
2228 from pay_run_result_values prr,
2229 pay_run_results pr,
2230 pay_input_values_f piv,
2231 pay_assignment_actions pas,
2232 pay_payroll_actions ppa
2233 where pas.assignment_id in (select assignment_id
2234 from per_all_assignments_f
2235 where person_id = p_person_id)
2236 and pas.tax_unit_id = g_paye_ref
2237 and pas.payroll_action_id = ppa.payroll_action_id
2238 and to_char(ppa.effective_date,'YYYY') = to_char(p_date_earned,'YYYY')
2239 and pr.assignment_action_id = pas.assignment_action_id
2240 and pr.run_result_id = prr.run_result_id
2241 and prr.input_value_id = piv.input_value_id
2242 and pr.element_type_id = piv.element_type_id
2243 and piv.input_value_id = g_tax_basis_id
2244 and piv.business_group_id IS NULL
2245 and piv.legislation_code = 'IE'
2246 and result_value not in ('IE_CUMULATIVE', 'C','IE_EXEMPTION');
2247 /*
2248 cursor cur_supp_run is
2249 select count(*)
2250 from pay_action_information pai,
2251 pay_assignment_Actions paa
2252 where paa.assignment_action_id = pai.action_context_id
2253 and pai.action_context_type = 'AAP'
2254 and pai.action_information_category = 'IE P45 INFORMATION'
2255 and paa.tax_unit_id = g_paye_ref
2256 and paa.assignment_id in ( select assignment_id
2257 from per_all_assignments_f
2258 where person_id = p_person_id
2259 )
2260 and to_date(pai.action_information3) = l_termination_date;
2261 -- and fnd_date.canonical_to_date(pai.action_information3) = l_termination_date;
2262 */
2263
2264 /* cursor cur_supp_run is
2265 select act_inf.action_information3
2266 from pay_assignment_actions paa_run,
2267 pay_action_interlocks pai,
2268 pay_assignment_actions paa,
2269 pay_payroll_actions ppa,
2270 pay_action_information act_inf
2271 where ppa.payroll_action_id = paa.payroll_action_id
2272 and ppa.report_type = 'P45'
2273 and ppa.report_qualifier = 'IE'
2274 and ppa.action_type = 'X'
2275 and paa.assignment_action_id = act_inf.action_context_id
2276 and act_inf.action_information_category = 'IE P45 INFORMATION'
2277 and act_inf.action_context_type = 'AAP'
2278 and ppa.payroll_action_id <> g_archive_pact
2279 and paa.assignment_action_id = pai.locking_action_id
2280 and paa.source_action_id is NULL
2281 and pai.locked_action_id = paa_run.assignment_action_id
2282 and paa_run.assignment_id = p_assignment_id
2283 and paa_run.action_status = 'C'
2284 and paa.action_status = 'C'; */
2285
2286 -- Commented as Tax Basis is now fetched from Run Results (5128377)
2287 /*Bug 4050372 */
2288 /*
2289 cursor cur_tax_basis(l_termination_date date)
2290 is
2291 select 'N'
2292 from pay_ie_paye_details_f
2293 where assignment_id=p_assignment_id
2294 and l_termination_date between effective_start_date and effective_end_date
2295 and tax_basis not in ('IE_EMERGENCY','IE_EMERGENCY_NO_PPS');
2296 */
2297 BEGIN
2298 --
2299 hr_utility.set_location('Entering ' || l_proc,20);
2300 hr_utility.set_location('Step ' || l_proc,20);
2301 hr_utility.set_location('p_action_context_id = ' || p_action_context_id,20);
2302 hr_utility.set_location('p_payroll_id = ' || p_payroll_id,20);
2303 hr_utility.set_location('p_assignment_id = ' || p_assignment_id,20);
2304 hr_utility.set_location('p_person_id = ' || p_person_id,20);
2305 hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
2306 hr_utility.set_location('p_date_earned = ' || p_date_earned,20);
2307 hr_utility.set_location('p_child_run_ass_act_id = ' || p_child_run_ass_act_id,20);
2308 -- get deceased flag, date of leaving
2309 /* OPEN cur_service_leave;
2310 FETCH cur_service_leave INTO l_deceased_flg,l_termination_date;
2311 CLOSE cur_service_leave;
2312
2313 l_asg_end_date := l_termination_date;
2314
2315 -- If employee is not terminated using end employment check for asg end date
2316 IF l_termination_date IS NULL THEN
2317 -- Get End Date of Employement with Employer
2318 OPEN cur_max_end_date;
2319 FETCH cur_max_end_date INTO l_termination_date;
2320 CLOSE cur_max_end_date;
2321 -- Get End Date of Assignment
2322 OPEN cur_get_asg_end_date;
2323 FETCH cur_get_asg_end_date INTO l_asg_end_date;
2324 CLOSE cur_get_asg_end_date;
2325 END IF;
2326 p_termination_date := l_termination_date;*/
2327 -- check whether this is a supplementary run
2328 -- and get the payment date of supplementary run
2329 /*OPEN cur_supp_run;
2330 FETCH cur_supp_run INTO l_last_end_date;
2331 hr_utility.set_location('l_last_end_date = '|| l_last_end_date,20);
2332 IF l_last_end_date IS NOT NULL THEN
2333 --IF l_report_type_count >= 1 THEN
2334 l_supp_flg :='Y';
2335 l_supp_pymt_date := p_date_earned;
2336 p_supp_flag:= 'Y';
2337 p_termination_date := l_last_end_date;
2338 l_termination_date := l_last_end_date;
2339 ELSE
2340 l_supp_flg :='N';
2341 p_supp_flag:= 'N';
2342 l_supp_pymt_date :=null;
2343 END IF;*/
2344
2345 l_supp_flg := p_supp_flag;
2346 l_supp_pymt_date := p_supp_pymt_date;
2347 l_termination_date := p_termination_date;
2348 hr_utility.set_location('supplementary flag = '||l_supp_flg,20);
2349 hr_utility.set_location('supplementary date = '||l_supp_pymt_date,20);
2350 --
2351 --
2352 -- get pay_period_number
2353 OPEN cur_period_num;
2354 FETCH cur_period_num INTO l_period_num, l_number_per_fiscal_year, l_periods_per_period, l_start_date, l_end_date;
2355 CLOSE cur_period_num;
2356 hr_utility.set_location('period number = '||l_period_num,20);
2357 hr_utility.set_location('number per fiscal year : '||l_number_per_fiscal_year,20);
2358 hr_utility.set_location('periods per period : '||l_periods_per_period,20);
2359 hr_utility.set_location('start date : '||l_start_date,20);
2360 hr_utility.set_location('end date : '||l_end_date,20);
2361 --
2362 If l_periods_per_period = 1 then
2363 l_p45_period_num := l_period_num;
2364 Elsif l_asg_end_date between l_start_date and l_end_date then
2365 If l_number_per_fiscal_year in (13,26,52) then
2366 l_p45_period_num := (((l_period_num - 1) * l_periods_per_period) + (ceil(((l_asg_end_date) - (l_start_date))/7)));
2367 Else
2368 l_p45_period_num := (((l_period_num - 1) * l_periods_per_period) + (ceil(months_between((l_asg_end_date),(l_start_date)))));
2369 End If;
2370 Else
2371 l_p45_period_num := (l_period_num * l_periods_per_period);
2372 End If;
2373 --
2374 -- get emergency_tax_operated_flg
2375 -- Bug 2943335 passed 'IE EMERGENCY PERIOD' as a parameter to cursor which was earlier hardcoded
2376 /* OPEN cur_defined_balance('IE EMERGENCY PERIOD');
2377 FETCH cur_defined_balance INTO l_defined_balance_id;
2378 CLOSE cur_defined_balance;
2379 hr_utility.set_location('defined balance = '||l_defined_balance_id,20);
2380 l_emer_num := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
2381 ,p_assignment_action_id => p_child_run_ass_act_id);
2382 IF l_emer_num >0 THEN
2383 l_emer_basis_flg :='Y';
2384 ELSE
2385 l_emer_basis_flg :='N';
2386 END IF;*/
2387 /*Bug 4050372*/
2388 l_emer_basis_flg := 'Y';
2389
2390 -- Emergency Flag is now fetched from Run Results 5128377
2391 -- for bug 5386432, call only if p_child_run_ass_act_id is not null
2392 IF p_child_run_ass_act_id IS NOT NULL THEN
2393 l_tax_basis := pay_ie_archive_detail_pkg.get_tax_details (
2394 p_run_assignment_action_id => p_child_run_ass_act_id
2395 ,p_input_value_id => g_tax_basis_id
2396 ,p_date_earned => to_char(p_date_earned, 'yyyy/mm/dd'));
2397 END IF;
2398
2399 /*
2400 OPEN cur_tax_basis(l_asg_end_date);
2401 FETCH cur_tax_basis INTO l_emer_basis_flg;
2402 CLOSE cur_tax_basis;
2403 */
2404 IF l_tax_basis NOT IN('IE_EMERGENCY','IE_EMERGENCY_NO_PPS') THEN
2405 l_emer_basis_flg := 'N';
2406 END IF;
2407
2408 hr_utility.set_location('emergency basis = '||l_emer_basis_flg,20);
2409 --
2410 -- get non_cumulative_operated for benefits
2411 -- Bug 2943335 Commented out code below
2412 /* OPEN cur_cal_option;
2413 FETCH cur_cal_option INTO l_calculation_option;
2414 hr_utility.set_location('l_calculation_option'||l_calculation_option,20);
2415 --Bug:2450336
2416 IF l_calculation_option IN ('IE_OPTION3','IE_OPTION4') THEN
2417 l_noncum_ben_operated := 'Y';
2418 ELSIF l_calculation_option IN ('IE_OPTION1','IE_OPTION2') THEN */
2419 -- Bug 2943335
2420 --
2421 -- get Social benefits amount paid
2422 -- From 1-jan-2006 noncumulative value does not depend upon social benefit
2423 -- amount. Bug 5519933. Removed the check.
2424
2425 /*OPEN cur_defined_balance('IE Taxable Social Benefit');
2426 FETCH cur_defined_balance INTO l_soc_ben_defined_bal_id;
2427 CLOSE cur_defined_balance;
2428 hr_utility.set_location('defined balance id = '||l_soc_ben_defined_bal_id,25);
2429
2430 -- bug 5386432, call only if p_child_run_ass_act_id is not null
2431 IF p_child_run_ass_act_id IS NOT NULL THEN
2432 l_soc_ben_amount := pay_balance_pkg.get_value(l_soc_ben_defined_bal_id,
2433 p_child_run_ass_act_id
2434 ,g_paye_ref
2435 ,null
2436 ,null
2437 ,null
2438 ,null
2439 ,null
2440 );
2441 end if;
2442 hr_utility.set_location('benefit amount = '||l_soc_ben_amount,25);
2443 -- Check this to identify if employee was ever paid social benefit amount
2444 -- Replacing fix for Bug:2450336
2445 IF ( l_soc_ben_amount <> 0) THEN */
2446 -- end of changes for Bug 2943335
2447 -- commented for Bug 5519933.
2448 -- If Employee is terminated and rehired in same tax year tax details of previous termination are
2449 -- reported in this termination against non cumulative flag
2450 /*
2451 OPEN cur_non_cum_tax;
2452 FETCH cur_non_cum_tax INTO l_non_cum_tax;
2453 hr_utility.set_location('l_non_cum_tax'||l_non_cum_tax,20);
2454
2455 IF cur_non_cum_tax%FOUND THEN
2456 l_noncum_ben_operated := 'true';
2457 ELSE
2458 l_noncum_ben_operated := 'false';
2459 END IF;
2460 CLOSE cur_non_cum_tax;
2461 */
2462 l_noncum_ben_operated := 'false';
2463 IF l_tax_basis NOT IN('IE_CUMULATIVE', 'C','IE_EXEMPTION') THEN
2464 l_noncum_ben_operated := 'true';
2465 END IF;
2466
2467 /*ELSE
2468 l_noncum_ben_operated := 'false';
2469 END IF;*/
2470
2471 -- CLOSE cur_cal_option;
2472 hr_utility.set_location('non cum basis operated = '||l_noncum_ben_operated,20);
2473 --
2474 -- archive the details
2475 pay_action_information_api.create_action_information (
2476 p_action_information_id => l_action_info_id
2477 , p_action_context_id => p_action_context_id
2478 , p_action_context_type => 'AAP'
2479 , p_object_version_number => l_ovn
2480 , p_effective_date => g_archive_effective_date
2481 , p_source_id => p_child_pay_action
2482 , p_source_text => NULL
2483 , p_action_information_category => 'IE P45 INFORMATION'
2484 , p_action_information1 => p_deceased_flag
2485 , p_action_information2 => l_supp_flg
2486 , p_action_information3 => l_termination_date
2487 , p_action_information4 => l_emer_basis_flg
2488 , p_action_information5 => l_p45_period_num
2489 , p_action_information6 => l_noncum_ben_operated
2490 , p_action_information7 => l_supp_pymt_date
2491 , p_action_information8 => p_person_id);
2492 --
2493 hr_utility.set_location('Leaving '||l_proc,20);
2494 END archive_p45_info;
2495 ---------------------------------------------------------------------------------
2496 --Moved the archiving Payroll Action Level Info archivng part from range_cursor 4468864
2497 PROCEDURE archive_deinit(p_payroll_action_id IN NUMBER) IS
2498 l_proc CONSTANT VARCHAR2(50):= g_package||'archive_deinit';
2499 -- vars for constructing the sqlstr
2500 l_range_cursor VARCHAR2(4000) := NULL;
2501 l_parameter_match VARCHAR2(500) := NULL;
2502 l_ovn NUMBER(15);
2503 l_request_id NUMBER;
2504 l_action_info_id NUMBER(15);
2505 l_business_group_id NUMBER;
2506 g_tax_dis_ref varchar2(10);
2507 CURSOR csr_check_archived(p_pact_id NUMBER) IS
2508 SELECT 1
2509 FROM DUAL
2510 WHERE EXISTS (SELECT NULL
2511 FROM pay_action_information pai
2512 WHERE pai.action_context_id = p_pact_id
2513 AND pai.action_context_type = 'PA'
2514 AND rownum = 1
2515 );
2516 CURSOR csr_input_value_id(p_element_name CHAR,
2517 p_value_name CHAR) IS
2518 SELECT pet.element_type_id,
2519 piv.input_value_id
2520 FROM pay_input_values_f piv,
2521 pay_element_types_f pet
2522 WHERE piv.element_type_id = pet.element_type_id
2523 AND pet.legislation_code = 'IE'
2524 AND pet.element_name = p_element_name
2525 AND piv.name = p_value_name;
2526 -- Archive all the prepayments information locked by the P45 4468864
2527 CURSOR csr_payroll_info(p_pact_id NUMBER,
2528 -- p_payroll_id NUMBER,
2529 -- p_consolidation_id NUMBER,
2530 p_start_date DATE,
2531 p_end_date DATE,
2532 -- g_tax_dis_ref VARCHAR2,
2533 g_paye_ref NUMBER) IS
2534 SELECT pact.payroll_action_id payroll_action_id,
2535 pact.effective_date effective_date,
2536 pact.date_earned date_earned,
2537 pact.payroll_id payroll_id,
2538 org.org_information1 tax_details_ref_no,
2539 org.org_information2 employer_paye_ref_no,
2540 hrl.address_line_1 employer_tax_addr1,
2541 hrl.address_line_2 employer_tax_addr2,
2542 hrl.address_line_3 employer_tax_addr3,
2543 hrl.telephone_number_1 employer_tax_ref_phone
2544 --
2545 FROM pay_all_payrolls_f ppf,
2546 pay_payroll_actions pact,
2547 hr_organization_information org,
2548 hr_soft_coding_keyflex flex,
2549 hr_organization_units hou,
2550 hr_locations_all hrl
2551 WHERE org.org_information_context = 'IE_EMPLOYER_INFO' -- for migration changes 4369280
2552 AND ppf.business_group_id = hou.business_group_id
2553 AND org.organization_id = hou.organization_id
2554 AND hou.location_id = hrl.location_id(+)
2555 /*
2556 org.org_information_context = 'IE_ORG_INFORMATION'
2557 AND ppf.business_group_id = org.organization_id
2558 */
2559 AND pact.payroll_id = ppf.payroll_id
2560 AND pact.effective_date BETWEEN
2561 ppf.effective_start_date AND ppf.effective_end_date
2562 -- AND pact.payroll_id = NVL(p_payroll_id,pact.payroll_id)
2563 -- AND ppf.consolidation_set_id = p_consolidation_id
2564 AND pact.effective_date BETWEEN
2565 p_start_date AND nvl(p_end_date,to_date('31-12-4712','dd-mm-rrrr'))
2566 AND (pact.action_type = 'P' OR
2567 pact.action_type = 'U')
2568 AND pact.action_status = 'C'
2569 --Added for bug fix 3567562, to filter payroll information based on PAYE reference
2570 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
2571 AND org.organization_id = flex.segment4
2572 /*
2573 AND org.org_information1 = flex.segment1
2574 AND org.org_information2 = flex.segment3
2575 */
2576 -- AND org.org_information_id = g_tax_dis_ref
2577 -- AND org.org_information2 = g_paye_ref
2578 AND org.organization_id = g_paye_ref
2579 AND exists (SELECT NULL
2580 FROM pay_assignment_actions paa,
2581 pay_action_interlocks pai,
2582 pay_assignment_actions paa_arc
2583 WHERE pai.locked_action_id = paa.assignment_action_id
2584 AND pai.locking_action_id = paa_arc.assignment_action_id
2585 AND paa_arc.payroll_action_id = p_pact_id
2586 AND paa.payroll_action_id = pact.payroll_action_id
2587 );
2588 l_check_payroll_info VARCHAR2(1):='N';
2589 -- Cursor csr_get_org_tax_address
2590 CURSOR csr_get_org_tax_address(-- c_consolidation_set PAY_CONSOLIDATION_SETS.CONSOLIDATION_SET_ID%type
2591 -- ,g_tax_dis_ref varchar2,
2592 g_paye_ref number
2593 ) IS
2594 SELECT
2595 hrl.address_line_1 employer_tax_addr1,
2596 hrl.address_line_2 employer_tax_addr2,
2597 hrl.address_line_3 employer_tax_addr3,
2598 org_info.org_information4 employer_tax_contact,
2599 hrl.telephone_number_1 employer_tax_ref_phone,
2600 org_all.name employer_tax_rep_name,
2601 org_all.business_group_id business_group_id
2602 --
2603 FROM hr_all_organization_units org_all
2604 ,hr_organization_information org_info
2605 -- ,pay_consolidation_sets pcs
2606 ,hr_locations_all hrl
2607 WHERE /*pcs.consolidation_set_id = c_consolidation_set
2608 AND org_all.organization_id = pcs.business_group_id
2609 AND org_info.organization_id = org_all.organization_id
2610 AND org_info.org_information_context = 'IE_ORG_INFORMATION'
2611 AND org_all.business_group_id = pcs.business_group_id
2612 AND*/ org_info.organization_id = org_all.organization_id
2613 AND org_info.org_information_context = 'IE_EMPLOYER_INFO' --for migration changes 4369280
2614 AND org_all.location_id = hrl.location_id (+)
2615 --Added new condition for bug fix 3567562 to filter record based on PAYE reference and Tax District Reference
2616 -- AND org_info.org_information1 = g_tax_dis_ref
2617 -- AND org_info.org_information2 = g_paye_ref ;
2618 AND org_info.organization_id = g_paye_ref ;
2619 ---- Cursor csr_check_archive
2620 CURSOR csr_check_archive( cp_payroll_action_id number
2621 ,cp_payroll_id number
2622 ,cp_effective_date date) IS
2623 SELECT DISTINCT paf.organization_id
2624 FROM per_all_assignments_f paf
2625 WHERE paf.payroll_id = cp_payroll_id
2626 AND cp_effective_date between paf.effective_start_date
2627 AND paf.effective_end_date
2628 AND NOT EXISTS (
2629 SELECT NULL
2630 FROM pay_action_information pai
2631 WHERE pai.action_context_id = cp_payroll_action_id
2632 AND pai.action_context_type = 'PA'
2633 AND pai.action_information_category = 'ADDRESS DETAILS'
2634 AND pai.action_information1 = paf.organization_id
2635 AND pai.action_information14 = 'Employer Address');
2636 -- Archive against only those prepayments which are locked by P45 4468864
2637 -- Commented to improve the performance 4771780
2638 /*
2639 CURSOR csr_all_payroll_info(p_pact_id NUMBER) IS
2640 SELECT pact.payroll_action_id payroll_action_id,
2641 pact.effective_date effective_date
2642 FROM pay_assignment_actions paa,
2643 pay_action_interlocks pai,
2644 pay_assignment_actions paa_arc,
2645 pay_payroll_actions pact
2646 WHERE pai.locked_action_id = paa.assignment_action_id
2647 AND pai.locking_action_id = paa_arc.assignment_action_id
2648 AND paa_arc.payroll_action_id = p_pact_id
2649 AND paa.payroll_action_id = pact.payroll_action_id
2650 AND (pact.action_type = 'P' OR
2651 pact.action_type = 'U')
2652 AND pact.action_status = 'C';
2653 */
2654 l_dummy NUMBER;
2655 l_assignment_set_id NUMBER;
2656 l_bg_id NUMBER;
2657 l_canonical_end_date DATE;
2658 l_canonical_start_date DATE;
2659 l_consolidation_set NUMBER;
2660 l_end_date VARCHAR2(30);
2661 l_legislation_code VARCHAR2(30) := 'IE';
2662 l_payroll_id NUMBER;
2663 l_start_date VARCHAR2(30);
2664 l_tax_period_no VARCHAR2(30);
2665 l_curr_payroll_id NUMBER;
2666 l_error varchar2(1) ;
2667 l_archived NUMBER;
2668 BEGIN
2669 hr_utility.set_location('Entering ' || l_proc,10);
2670 l_archived := 0;
2671 -- Check whether assignment action is retried 4468864
2672 OPEN csr_check_archived(p_payroll_action_id);
2673 FETCH csr_check_archived INTO l_archived;
2674 CLOSE csr_check_archived;
2675 IF l_archived = 0 THEN
2676 pay_ie_p45_archive.get_parameters (
2677 p_payroll_action_id => p_payroll_action_id
2678 , p_token_name => 'EMPLOYER'
2679 , p_token_value => g_paye_ref);
2680 /*
2681 pay_ie_p45_archive.get_parameters (
2682 p_payroll_action_id => p_payroll_action_id
2683 , p_token_name => 'CONSOLIDATION'
2684 , p_token_value => l_consolidation_set);
2685 pay_ie_p45_archive.get_parameters (
2686 p_payroll_action_id => p_payroll_action_id
2687 , p_token_name => 'ASSIGNMENT_SET'
2688 , p_token_value => l_assignment_set_id);
2689 pay_ie_p45_archive.get_parameters (
2690 p_payroll_action_id => p_payroll_action_id
2691 , p_token_name => 'START_DATE'
2692 , p_token_value => l_start_date);
2693 */
2694 pay_ie_p45_archive.get_parameters (
2695 p_payroll_action_id => p_payroll_action_id
2696 , p_token_name => 'END_DATE'
2697 , p_token_value => l_end_date);
2698
2699 pay_ie_p45_archive.get_parameters (
2700 p_payroll_action_id => p_payroll_action_id
2701 , p_token_name => 'START_DATE'
2702 , p_token_value => l_start_date);
2703
2704 pay_ie_p45_archive.get_parameters (
2705 p_payroll_action_id => p_payroll_action_id
2706 , p_token_name => 'BG_ID'
2707 , p_token_value => l_bg_id);
2708 hr_utility.set_location('Step ' || l_proc,20);
2709 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
2710 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
2711 --get_paye_reference (l_consolidation_set,g_paye_ref,l_bg_id,l_canonical_start_date,l_canonical_end_date,l_error);
2712 /*if l_error ='Y' then
2713 NULL;
2714 else
2715 */
2716 FOR tax_info_rec IN csr_get_org_tax_address (g_paye_ref) LOOP
2717 --
2718 pay_action_information_api.create_action_information (
2719 p_action_information_id => l_action_info_id
2720 , p_action_context_id => p_payroll_action_id
2721 , p_action_context_type => 'PA'
2722 , p_object_version_number => l_ovn
2723 , p_action_information_category => 'ADDRESS DETAILS'
2724 , p_action_information1 => tax_info_rec.business_group_id
2725 , p_action_information5 => tax_info_rec.employer_tax_addr1
2726 , p_action_information6 => tax_info_rec.employer_tax_addr2
2727 , p_action_information7 => tax_info_rec.employer_tax_addr3
2728 , p_action_information14 => 'IE Employer Tax Address'
2729 , p_action_information26 => tax_info_rec.employer_tax_contact
2730 , p_action_information27 => tax_info_rec.employer_tax_ref_phone
2731 , p_action_information28 => tax_info_rec.employer_tax_rep_name);
2732 --
2733 END LOOP;
2734 l_curr_payroll_id:= -1;
2735 FOR rec_payroll_info in csr_payroll_info(p_payroll_action_id,
2736 -- l_payroll_id,
2737 -- l_consolidation_set,
2738 l_canonical_start_date,
2739 l_canonical_end_date,
2740 -- g_tax_dis_ref,
2741 g_paye_ref)
2742 LOOP
2743 -- Cursor csr_check_archive called
2744 OPEN csr_check_archive( p_payroll_action_id
2745 ,rec_payroll_info.payroll_id
2746 ,l_canonical_end_date);
2747 FETCH csr_check_archive INTO l_dummy;
2748 IF csr_check_archive%FOUND THEN
2749 pay_emp_action_arch.arch_pay_action_level_data (
2750 p_payroll_action_id => p_payroll_action_id
2751 , p_payroll_id => rec_payroll_info.payroll_id
2752 , p_effective_date => l_canonical_end_date);
2753 END IF;
2754 CLOSE csr_check_archive;
2755 hr_utility.set_location('rec_payroll_info.payroll_action_id = ' || rec_payroll_info.payroll_action_id,30);
2756 hr_utility.set_location('rec_payroll_info.tax_details_ref = ' || rec_payroll_info.tax_details_ref_no,30);
2757 hr_utility.set_location('rec_payroll_info.employers_paye_ref_no = ' || rec_payroll_info.employer_paye_ref_no,30);
2758 hr_utility.set_location('Archiving EMEA PAYROLL INFO',30);
2759 pay_action_information_api.create_action_information (
2760 p_action_information_id => l_action_info_id
2761 , p_action_context_id => p_payroll_action_id
2762 , p_action_context_type => 'PA'
2763 , p_object_version_number => l_ovn
2764 , p_effective_date => rec_payroll_info.effective_date
2765 , p_source_id => NULL
2766 , p_source_text => NULL
2767 , p_action_information_category => 'EMEA PAYROLL INFO'
2768 , p_action_information1 => rec_payroll_info.payroll_action_id
2769 , p_action_information2 => rec_payroll_info.payroll_id
2770 , p_action_information3 => l_consolidation_set
2771 , p_action_information4 => rec_payroll_info.tax_details_ref_no
2772 , p_action_information5 => rec_payroll_info.employer_tax_ref_phone
2773 , p_action_information6 => rec_payroll_info.employer_paye_ref_no
2774 , p_action_information8 => rec_payroll_info.employer_tax_addr1
2775 , p_action_information9 => rec_payroll_info.employer_tax_addr2
2776 , p_action_information10 => rec_payroll_info.employer_tax_addr3);
2777 /* Coomented to improve the performance 4771780 since the cursor csr_all_payroll_info has high cost.
2778 as the same cursor csr_payroll_info can be used to get the required details */
2779 /*
2780 END LOOP;
2781 -- setup statutory balances pl/sql table
2782 pay_ie_p45_archive.setup_standard_balance_table;
2783 FOR rec_payroll_info in csr_all_payroll_info(p_payroll_action_id)
2784 LOOP
2785 */
2786 hr_utility.trace('Entered payroll info');
2787 pay_balance_pkg.set_context('PAYROLL_ACTION_ID'
2788 , rec_payroll_info.payroll_action_id);
2789 pay_ie_p45_archive.setup_balance_definitions(p_payroll_action_id,
2790 rec_payroll_info.payroll_action_id,
2791 rec_payroll_info.effective_date);
2792 END LOOP;
2793 --end if;
2794 END IF;
2795 Exception
2796 when others then
2797 hr_utility.set_location('Leaving via exception section ' || l_proc,40);
2798 END archive_deinit;
2799
2800 ---------------------------------------------------------------------------------
2801
2802 PROCEDURE range_cursor (pactid IN NUMBER,
2803 sqlstr OUT nocopy VARCHAR2)
2804 -- public procedure which archives the payroll information, then returns a
2805 -- varchar2 defining a SQL statement to select all the people that may be
2806 -- eligible for payslip reports.
2807 -- The archiver uses this cursor to split the people into chunks for parallel
2808 -- processing.
2809 IS
2810 --
2811 l_proc CONSTANT VARCHAR2(50):= g_package||'range_cursor';
2812 -- vars for constructing the sqlstr
2813 l_range_cursor VARCHAR2(4000) := NULL;
2814 l_parameter_match VARCHAR2(500) := NULL;
2815 l_ovn NUMBER(15);
2816 l_request_id NUMBER;
2817 l_action_info_id NUMBER(15);
2818 l_business_group_id NUMBER;
2819 g_tax_dis_ref varchar2(10);
2820 --
2821 l_check_payroll_info VARCHAR2(1):='N';
2822 ---- Cursor csr_check_archive
2823 l_dummy NUMBER;
2824 l_assignment_set_id NUMBER;
2825 l_bg_id NUMBER;
2826 l_canonical_end_date DATE;
2827 l_canonical_start_date DATE;
2828 l_consolidation_set NUMBER;
2829 l_end_date VARCHAR2(30);
2830 l_legislation_code VARCHAR2(30) := 'IE';
2831 l_payroll_id NUMBER;
2832 l_start_date VARCHAR2(30);
2833 l_tax_period_no VARCHAR2(30);
2834 l_curr_payroll_id NUMBER;
2835 l_error varchar2(1) ;
2836 l_employer NUMBER;
2837
2838 BEGIN
2839 hr_utility.set_location('Entering ' || l_proc,10);
2840 /*
2841 pay_ie_p45_archive.get_parameters (
2842 p_payroll_action_id => pactid
2843 , p_token_name => 'PAYROLL'
2844 , p_token_value => l_payroll_id);
2845 pay_ie_p45_archive.get_parameters (
2846 p_payroll_action_id => pactid
2847 , p_token_name => 'CONSOLIDATION'
2848 , p_token_value => l_consolidation_set);
2849 pay_ie_p45_archive.get_parameters (
2850 p_payroll_action_id => pactid
2851 , p_token_name => 'ASSIGNMENT_SET'
2852 , p_token_value => l_assignment_set_id);
2853 pay_ie_p45_archive.get_parameters (
2854 p_payroll_action_id => pactid
2855 , p_token_name => 'START_DATE'
2856 , p_token_value => l_start_date);
2857 */
2858 pay_ie_p45_archive.get_parameters (
2859 p_payroll_action_id => pactid
2860 , p_token_name => 'END_DATE'
2861 , p_token_value => l_end_date);
2862
2863 pay_ie_p45_archive.get_parameters (
2864 p_payroll_action_id => pactid
2865 , p_token_name => 'BG_ID'
2866 , p_token_value => l_bg_id);
2867
2868 pay_ie_p45_archive.get_parameters (
2869 p_payroll_action_id => pactid
2870 , p_token_name => 'EMPLOYER'
2871 , p_token_value => l_employer);
2872
2873 pay_ie_p45_archive.get_parameters (
2874 p_payroll_action_id => pactid
2875 , p_token_name => 'START_DATE'
2876 , p_token_value => l_start_date);
2877
2878 hr_utility.set_location('Step ' || l_proc,20);
2879 --hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
2880 --hr_utility.set_location('l_start_date = ' || l_start_date,20);
2881 --hr_utility.set_location('l_end_date = ' || l_end_date,20);
2882 --hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
2883 --hr_utility.set_location('l_start_date = ' || l_start_date,20);
2884 -- l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
2885 -- l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
2886 --archive EMEA PAYROLL INFO for each prepayment run identified
2887 --hr_utility.set_location('l_payroll_id = ' || l_payroll_id,20);
2888 --hr_utility.set_location('l_consolidation_set = ' || l_consolidation_set,20);
2889 -- hr_utility.set_location('l_canonical_start_date = ' || l_canonical_start_date,20);
2890 -- hr_utility.set_location('l_canonical_end_date = ' || l_canonical_end_date,20);
2891 --Call made to procedure get_paye_referene to get the PAYE reference attributed at payroll level,added for bug fix 3567562.
2892 --get_paye_reference (l_consolidation_set,g_paye_ref,l_bg_id,l_canonical_start_date,l_canonical_end_date,l_error);
2893 --Added for bug fix 3567562
2894 /*
2895 if l_error ='Y' then
2896 sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
2897 else
2898 */
2899 sqlstr := 'SELECT DISTINCT person_id
2900 FROM per_people_f ppf,
2901 pay_payroll_actions ppa
2902 WHERE ppa.payroll_action_id = :payroll_action_id
2903 AND ppa.business_group_id +0= ppf.business_group_id
2904 ORDER BY ppf.person_id';
2905 hr_utility.set_location('Leaving ' || l_proc,40);
2906 --end if;
2907 Exception
2908 when others then
2909 hr_utility.set_location('Leaving via exception section ' || l_proc,40);
2910 sqlstr:='select 1 from dual where to_char(:payroll_action_id) = dummy';
2911 END range_cursor;
2912
2913 -------------------------------------------------
2914 PROCEDURE action_creation (pactid in number,
2915 stperson in number,
2916 endperson in number,
2917 chunk in number) is
2918 --
2919 CURSOR csr_prepaid_assignments(p_pact_id NUMBER,
2920 stperson NUMBER,
2921 endperson NUMBER,
2922 p_paye_ref NUMBER,
2923 l_payroll_id NUMBER -- 5059862 p45 payroll parameter change
2924 ) IS
2925 SELECT as1.person_id person_id,
2926 act.assignment_id assignment_id,
2927 act.assignment_action_id run_action_id,
2928 act1.assignment_action_id prepaid_action_id,
2929 as1.assignment_number works_number,
2930 as1.period_of_service_id period_of_service_id
2931 FROM --per_periods_of_service ppos,
2932 per_all_assignments_f as1,
2933 pay_assignment_actions act,
2934 pay_payroll_actions appa,
2935 pay_action_interlocks pai,
2936 pay_assignment_actions act1,
2937 pay_payroll_actions appa2
2938 WHERE /*appa.consolidation_set_id = p_consolidation_id*/
2939 act.tax_unit_id = p_paye_ref
2940 AND appa.effective_date BETWEEN g_archive_start_date AND g_archive_end_date
2941 AND as1.person_id BETWEEN stperson AND endperson
2942 /* Assignment End Date should be between archive start date and end date */
2943 AND as1.effective_end_date between g_archive_start_date AND g_archive_end_date
2944 AND (as1.effective_end_date = (select max(effective_end_date)
2945 from per_all_assignments_f paf1
2946 where paf1.assignment_id = as1.assignment_id
2947 /* changed the cursor to handle case where 2 user defined assignment status exist mapping to
2948 same per_system_status (5073577) */
2949 and paf1.assignment_status_type_id in
2950 (SELECT ast.assignment_status_type_id
2951 FROM per_assignment_status_types ast
2952 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2953 )
2954 )
2955 AND as1.effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
2956 )
2957 AND (as1.payroll_id in (select b.payroll_id -- 5059862
2958 from per_assignments_f a,per_assignments_f b
2959 where a.payroll_id = l_payroll_id
2960 and a.person_id = b.person_id
2961 and a.period_of_Service_id = b.period_of_Service_id
2962 and a.period_of_Service_id = as1.period_of_Service_id -- 5758951
2963 and a.person_id = as1.person_id
2964 and a.effective_start_date <= g_archive_end_date
2965 -- and a.effective_end_date >= trunc(g_archive_end_date,'Y') -- bug 6144761
2966 -- 5758951
2967 and a.effective_end_date = (select max(effective_end_date)
2968 from per_all_assignments_f paf1
2969 where paf1.assignment_id = a.assignment_id
2970 and paf1.assignment_status_type_id in
2971 (SELECT ast.assignment_status_type_id
2972 FROM per_assignment_status_types ast
2973 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
2974 )
2975 )
2976 )
2977 OR l_payroll_id is null)
2978
2979 --
2980 AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
2981 AND act.payroll_action_id = appa.payroll_action_id
2982 AND act.source_action_id IS NULL
2983 AND as1.assignment_id = act.assignment_id
2984 AND act.action_status = 'C'
2985 AND act.assignment_action_id = pai.locked_action_id
2986 AND act1.assignment_action_id = pai.locking_action_id
2987 AND act1.action_status = 'C'
2988 AND act1.payroll_action_id = appa2.payroll_action_id
2989 AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
2990 AND appa2.payroll_action_id = (SELECT /*+ USE_NL(ACT2 APPA4)*/
2991 max(appa4.payroll_action_id)
2992 FROM /*pay_pre_payments ppp, --Bug 4193738 --Bug 4468864*/
2993 pay_assignment_actions act2,
2994 pay_payroll_actions appa4
2995 WHERE /*ppp.assignment_action_id=act2.assignment_action_id
2996 AND*/ act2.assignment_id = act.assignment_id
2997 AND act2.action_status = 'C'
2998 AND appa4.payroll_action_id = act2.payroll_action_id
2999 AND appa4.action_type in ('P','U')
3000 AND appa4.effective_date BETWEEN g_archive_start_date AND g_archive_end_date)
3001 -- bug 5597735, change the not exists clause.
3002 -- refer bug 5233518 for more details.
3003 AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
3004 from pay_assignment_actions act3,
3005 pay_payroll_actions appa3,
3006 pay_action_interlocks pai, --bug 4208273
3007 pay_assignment_actions act2, --bug 4208273
3008 pay_payroll_actions appa4 --bug 4208273
3009 where pai.locked_action_id= act3.assignment_action_id
3010 and pai.locking_action_id=act2.assignment_action_id
3011 and act3.action_sequence >= act1.action_sequence --bug 4193738
3012 and act3.assignment_id in (select distinct paaf.assignment_id
3013 from per_all_assignments_f paaf
3014 where paaf.person_id = as1.person_id
3015 )
3016 and act3.tax_unit_id = act1.tax_unit_id
3017 and act3.action_status = 'C'
3018 and act2.action_status = 'C'
3019 and act3.payroll_action_id=appa4.payroll_action_id
3020 and appa4.action_type in ('P','U')
3021 and act2.payroll_action_id = appa3.payroll_action_id
3022 and appa3.action_type = 'X'
3023 and appa3.report_type = 'P45')
3024 /* check person does not hold employment with the employer between start of year and archive end date */
3025 AND NOT EXISTS (
3026 SELECT MIN(paf.effective_start_date),MAX(paf.effective_end_date)
3027 FROM per_all_assignments_f paf,
3028 pay_all_payrolls_f papf,
3029 hr_soft_coding_keyflex scl
3030 WHERE paf.person_id = as1.person_id
3031 AND paf.payroll_id = papf.payroll_id
3032 /* changed the cursor to handle case where 2 user defined assignment status exist mapping to
3033 same per_system_status (5073577) */
3034 AND paf.assignment_status_type_id in
3035 (SELECT ast.assignment_status_type_id
3036 FROM per_assignment_status_types ast
3037 WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
3038 )
3039 AND g_archive_end_date between papf.effective_start_date and papf.effective_end_date
3040 AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
3041 AND scl.segment4 = to_char(p_paye_ref)
3042 group by paf.assignment_id
3043 having min(paf.effective_start_date) <= g_archive_end_date
3044 and max(paf.effective_end_date) > g_archive_end_date
3045 )
3046 ORDER BY as1.person_id,as1.assignment_number,act.assignment_id
3047 FOR UPDATE OF as1.assignment_id;
3048
3049 l_actid NUMBER;
3050 l_canonical_end_date DATE;
3051 l_canonical_start_date DATE;
3052 l_consolidation_set VARCHAR2(30);
3053 l_end_date VARCHAR2(20);
3054 l_payroll_id NUMBER;
3055 l_prepay_action_id NUMBER;
3056 l_start_date VARCHAR2(20);
3057 l_person_id NUMBER;
3058 l_error varchar2(1) ;
3059 l_period_of_service_id NUMBER;
3060 l_bg_id NUMBER;
3061 --
3062 l_proc VARCHAR2(50) := g_package||'action_creation';
3063 BEGIN
3064 --hr_utility.trace_on(null,'P45');
3065 hr_utility.set_location('Entering ' || l_proc,10);
3066 pay_ie_p45_archive.get_parameters (
3067 p_payroll_action_id => pactid
3068 , p_token_name => 'EMPLOYER'
3069 , p_token_value => g_paye_ref);
3070
3071 pay_ie_p45_archive.get_parameters (
3072 p_payroll_action_id => pactid
3073 , p_token_name => 'END_DATE'
3074 , p_token_value => l_end_date);
3075
3076 pay_ie_p45_archive.get_parameters (
3077 p_payroll_action_id => pactid
3078 , p_token_name => 'BG_ID'
3079 , p_token_value => l_bg_id);
3080
3081 pay_ie_p45_archive.get_parameters (
3082 p_payroll_action_id => pactid
3083 , p_token_name => 'START_DATE'
3084 , p_token_value => l_start_date);
3085
3086 pay_ie_p45_archive.get_parameters ( -- 5059862
3087 p_payroll_action_id => pactid
3088 , p_token_name => 'PAYROLL'
3089 , p_token_value => l_payroll_id);
3090
3091 hr_utility.set_location('Step ' || l_proc,20);
3092 hr_utility.set_location('g_paye_ref = ' || g_paye_ref,20);
3093 hr_utility.set_location('l_end_date = ' || l_end_date,20);
3094
3095 l_canonical_start_date := TO_DATE(l_start_date,'yyyy/mm/dd');
3096 l_canonical_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
3097 g_archive_start_date := l_canonical_start_date;
3098 g_archive_end_date := TO_DATE(l_end_date,'yyyy/mm/dd');
3099 -- l_payroll_id := TO_NUM(l_payroll_id);
3100
3101 l_prepay_action_id := 0;
3102 l_person_id := 0;
3103 l_period_of_service_id := 0;
3104
3105 hr_utility.set_location('l_start_date = ' || l_canonical_start_date,20);
3106
3107
3108 --get_paye_reference (l_consolidation_set,g_paye_ref,l_bg_id,l_canonical_start_date,l_canonical_end_date,l_error);
3109 hr_utility.set_location('Before csr_prepaid_assignments',21);
3110
3111 FOR csr_rec IN csr_prepaid_assignments(pactid,
3112 stperson,
3113 endperson,
3114 g_paye_ref,
3115 l_payroll_id)
3116 LOOP
3117
3118 IF (l_person_id <> csr_rec.person_id) THEN
3119
3120 hr_utility.set_location('Different Person '|| csr_rec.person_id ,22);
3121
3122 SELECT pay_assignment_actions_s.NEXTVAL
3123 INTO l_actid
3124 FROM dual;
3125
3126 -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
3127 hr_utility.set_location('ASSIGNMENT ID : ' || csr_rec.assignment_id,23);
3128 hr_utility.trace('ASSIGNMENT ID : ' || csr_rec.assignment_id);
3129
3130 hr_nonrun_asact.insact(l_actid,csr_rec.assignment_id,pactid,chunk,g_paye_ref);
3131 -- CREATE THE ARCHIVE TO PAYROLL MASTER ASSIGNMENT ACTION INTERLOCK AND
3132 -- THE ARCHIVE TO PREPAYMENT ASSIGNMENT ACTION INTERLOCK
3133 -- hr_utility.set_location('creating lock1 ' || l_actid || ' to ' || csr_rec.run_action_id,20);
3134 -- hr_utility.set_location('creating lock2 ' || l_actid || ' to ' || csr_rec.prepaid_action_id,20);
3135 END IF;
3136 hr_utility.set_location('l_prepay_action_id : ' || l_prepay_action_id,100);
3137 hr_utility.set_location('csr_rec.prepaid_action_id : ' || csr_rec.prepaid_action_id,101);
3138 hr_utility.set_location('l_actid : ' || l_actid,102);
3139
3140 IF l_prepay_action_id <> csr_rec.prepaid_action_id THEN
3141 hr_utility.set_location('locked id : ' || csr_rec.prepaid_action_id,23);
3142 hr_nonrun_asact.insint(l_actid,csr_rec.prepaid_action_id);
3143 END IF;
3144
3145 hr_nonrun_asact.insint(l_actid,csr_rec.run_action_id);
3146
3147 l_prepay_action_id := csr_rec.prepaid_action_id;
3148 l_person_id := csr_rec.person_id;
3149 l_period_of_service_id := csr_rec.period_of_service_id;
3150
3151 END LOOP;
3152
3153 hr_utility.set_location('Leaving ' || l_proc,20);
3154 END action_creation;
3155
3156 ----------------------------
3157 PROCEDURE archive_code (p_assactid in number,
3158 p_effective_date in date) IS
3159 CURSOR csr_assignment_actions(p_locking_action_id NUMBER) IS
3160 SELECT pre.locked_action_id pre_assignment_action_id,
3161 pay.locked_action_id master_assignment_action_id,
3162 assact.assignment_id assignment_id,
3163 assact.payroll_action_id pay_payroll_action_id,
3164 paa.effective_date effective_date,
3165 ppaa.effective_date pre_effective_date,
3166 paa.date_earned date_earned,
3167 ptp.time_period_id time_period_id
3168 FROM pay_action_interlocks pre,
3169 pay_action_interlocks pay,
3170 pay_payroll_actions paa,
3171 pay_payroll_actions ppaa,
3172 pay_assignment_actions assact,
3173 pay_assignment_actions passact,
3174 per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
3175 WHERE pre.locked_action_id = pay.locking_action_id
3176 AND pre.locking_action_id = p_locking_action_id
3177 AND pre.locked_action_id = passact.assignment_action_id
3178 AND passact.payroll_action_id = ppaa.payroll_action_id
3179 AND ppaa.action_type IN ('P','U')
3180 AND pay.locked_action_id = assact.assignment_action_id
3181 AND assact.payroll_action_id = paa.payroll_action_id
3182 AND assact.source_action_id IS NULL
3183 AND ptp.payroll_id = paa.payroll_id
3184 AND paa.date_earned between ptp.start_date and ptp.end_date
3185 --
3186 ORDER BY pay.locked_action_id DESC;
3187
3188 -- cursor to retrieve payroll run assignment_action_ids
3189 -- Bug Fix 3817846
3190 -- Changed the cursor cur_child_pay_action
3191 /*CURSOR cur_child_pay_action(p_assignment_id NUMBER,
3192 p_date_earned DATE)is
3193 SELECT max(paa.assignment_action_id)
3194 FROM pay_assignment_actions paa,
3195 pay_payroll_actions ppa
3196 where paa.assignment_id = p_assignment_id
3197 AND paa.payroll_action_id = ppa.payroll_action_id
3198 AND ppa.date_earned =p_date_earned
3199 AND ppa.action_type in ('R','Q')
3200 AND paa.action_status = 'C'
3201 AND paa.source_action_id is not null;*/
3202 /*New Cursor to fetch latest child action */
3203 CURSOR cur_child_pay_action (p_person_id IN NUMBER,
3204 p_effective_date IN DATE,
3205 p_lat_act_seq IN NUMBER) is
3206 SELECT /*+ USE_NL(paa, ppa) */
3207 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3208 paa.assignment_action_id),16))
3209 FROM pay_assignment_actions paa,
3210 pay_payroll_actions ppa
3211 WHERE paa.payroll_action_id = ppa.payroll_action_id
3212 AND paa.assignment_id in (select assignment_id
3213 from per_all_assignments_f
3214 where person_id = p_person_id
3215 )
3216 AND paa.tax_unit_id = g_paye_ref
3217 AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
3218 AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
3219 AND paa.action_sequence > p_lat_act_seq
3220 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
3221 AND paa.action_status = 'C';
3222
3223 -- cursor to find assignment action locked by latest P45 child action
3224 CURSOR cur_get_latest_p45(p_pact_id NUMBER,
3225 p_person_id NUMBER
3226 ) IS
3227 SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
3228 FROM pay_payroll_actions ppa_p45,
3229 pay_assignment_actions p45_src,
3230 pay_action_information pai_p45,
3231 pay_assignment_actions paa_src
3232 WHERE ppa_p45.action_type = 'X'
3233 AND ppa_p45.report_type = 'P45'
3234 AND ppa_p45.report_qualifier = 'IE'
3235 AND ppa_p45.payroll_action_id <> p_pact_id
3236 AND ppa_p45.payroll_action_id = p45_src.payroll_action_id
3237 AND p45_src.assignment_action_id = pai_p45.action_context_id
3238 AND pai_p45.action_context_type = 'AAP'
3239 AND pai_p45.action_information_category = 'IE P45 INFORMATION'
3240 AND pai_p45.source_id = paa_src.assignment_action_id
3241 AND p45_src.action_status = 'C'
3242 AND paa_src.tax_unit_id = g_paye_ref
3243 AND p45_src.tax_unit_id = g_paye_ref
3244 AND pai_p45.action_information8 = to_char(p_person_id);
3245
3246 -- Cursor to fetch action context id of P45 for previous period of service.
3247 CURSOR cur_get_last_p45(p_person_id NUMBER,p_termination_date DATE,p_pact NUMBER) IS
3248 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3249 paa.assignment_action_id),16))
3250 FROM pay_payroll_actions ppa,
3251 pay_assignment_actions paa,
3252 pay_action_information pai
3253 WHERE paa.assignment_action_id = pai.action_context_id
3254 AND pai.action_information_category = 'IE P45 INFORMATION'
3255 AND pai.action_context_type = 'AAP'
3256 AND paa.tax_unit_id = g_paye_ref
3257 AND pai.action_information3 between trunc(p_termination_date,'Y') and p_termination_date
3258 AND ppa.payroll_action_id = paa.payroll_action_id
3259 AND ppa.report_type = 'P45'
3260 AND ppa.report_category = 'ARCHIVE'
3261 AND ppa.report_qualifier = 'IE'
3262 AND ppa.effective_date between trunc(g_archive_end_date,'Y') and g_archive_end_date
3263 AND paa.payroll_action_id <> p_pact
3264 AND paa.action_status = 'C'
3265 AND pai.action_information8 = to_char(p_person_id);
3266
3267 -- cursor to fetch Payroll action of Last P45 to pass to get_arc_bal_value 5005788
3268 CURSOR cur_get_p45_pact(p_p45_aact pay_assignment_actions.assignment_action_id%TYPE) IS
3269 SELECT paa.payroll_action_id
3270 FROM pay_assignment_actions paa
3271 WHERE paa.assignment_action_id = p_p45_aact;
3272
3273 -- cursor to retrieve payroll id
3274 CURSOR cur_assgn_payroll(p_assignment_id NUMBER,
3275 p_date_earned DATE) IS
3276 SELECT payroll_id,person_id,period_of_service_id
3277 FROM per_all_assignments_f
3278 WHERE assignment_id = p_assignment_id
3279 AND p_date_earned
3280 BETWEEN effective_start_date AND effective_end_date;
3281
3282 l_actid NUMBER;
3283 l_action_context_id NUMBER;
3284 l_action_info_id NUMBER(15);
3285 l_assignment_action_id NUMBER;
3286 l_business_group_id NUMBER;
3287 l_chunk_number NUMBER;
3288 l_assignment_id NUMBER;
3289 l_date_earned DATE;
3290 l_ovn NUMBER;
3291 l_person_id NUMBER;
3292 l_pos_id NUMBER;
3293 l_record_count NUMBER;
3294 l_salary VARCHAR2(10);
3295 l_sequence NUMBER;
3296 l_child_pay_action NUMBER;
3297 l_payroll_id NUMBER;
3298 l_supp_flag VARCHAR2(1):='N';
3299 l_deceased_flag VARCHAR2(1):='N';
3300 l_proc VARCHAR2(50) := g_package || 'archive_code';
3301 l_lat_act_seq NUMBER;
3302 l_termination_date DATE;
3303 l_last_p45_action NUMBER;
3304 l_max_stat_balance NUMBER := 19;
3305 l_concat_sequence VARCHAR2(40);
3306 l_prev_src_id NUMBER;
3307 l_last_p45_pact NUMBER;
3308 -- 5386432
3309 l_supp_pymt_date DATE;
3310
3311
3312
3313 BEGIN
3314 -- hr_utility.trace_on(null,'P45');
3315 l_lat_act_seq := NULL;
3316 hr_utility.set_location('Entering'|| l_proc,10);
3317 hr_utility.set_location('Step '|| l_proc,20);
3318 hr_utility.set_location('p_assactid = ' || p_assactid,20);
3319
3320 -- retrieve the chunk number for the current assignment action
3321 SELECT paa.chunk_number,paa.assignment_id
3322 INTO l_chunk_number,l_assignment_id
3323 FROM pay_assignment_actions paa
3324 WHERE paa.assignment_action_id = p_assactid;
3325
3326 l_action_context_id := p_assactid;
3327 l_record_count := 0;
3328
3329 FOR csr_rec IN csr_assignment_actions(p_assactid)
3330 LOOP
3331 hr_utility.set_location('csr_rec.master_assignment_action_id = ' || csr_rec.master_assignment_action_id,20);
3332 hr_utility.set_location('csr_rec.pre_assignment_action_id = ' || csr_rec.pre_assignment_action_id,20);
3333 hr_utility.set_location('csr_rec.assignment_id = ' || csr_rec.assignment_id,20);
3334 hr_utility.set_location('csr_rec.date_earned = ' ||to_char( csr_rec.date_earned,'dd-mon-yyyy'),20);
3335 hr_utility.set_location('csr_rec.pre_effective_date = ' ||to_char( csr_rec.pre_effective_date,'dd-mon-yyyy'),20);
3336 hr_utility.set_location('csr_rec.time_period_id = ' || csr_rec.time_period_id,20);
3337
3338 OPEN cur_assgn_payroll(csr_rec.assignment_id,csr_rec.date_earned);
3339 FETCH cur_assgn_payroll INTO l_payroll_id,l_person_id,l_pos_id;
3340 CLOSE cur_assgn_payroll;
3341
3342 --Fetch the action sequence of latest payroll run child action locked by latest P45
3343 --For the assignment 4468864
3344 OPEN cur_get_latest_p45(g_archive_pact,l_person_id);
3345 FETCH cur_get_latest_p45 INTO l_concat_sequence;
3346
3347 IF cur_get_latest_p45%NOTFOUND THEN
3348 hr_utility.set_location('Action Sequence notfound = ' || l_lat_act_seq,21);
3349 l_lat_act_seq := 0;
3350 l_prev_src_id := 0;
3351 END IF;
3352
3353 l_lat_act_seq := nvl(substr(l_concat_sequence,1,15),0);
3354 l_prev_src_id := nvl(substr(l_concat_sequence,16),0);
3355
3356 hr_utility.set_location('Action Sequence = ' || l_lat_act_seq,21);
3357 CLOSE cur_get_latest_p45;
3358
3359 hr_utility.set_location('Action Sequence = ' || l_lat_act_seq,21);
3360
3361 -- Bug Fix 3817846
3362 -- OPEN cur_child_pay_action(csr_rec.assignment_id, csr_rec.date_earned);
3363 -- Bug Fix 4001524
3364 -- OPEN cur_child_pay_action(csr_rec.assignment_id, csr_rec.effective_date);
3365 l_child_pay_action := NULL;
3366 OPEN cur_child_pay_action(l_person_id,g_archive_end_date,l_lat_act_seq);
3367 FETCH cur_child_pay_action INTO l_child_pay_action;
3368
3369 hr_utility.set_location('Child Action ='||l_child_pay_action,24);
3370
3371 -------------- Moved here for bug 5386432 ----
3372 get_termination_date(p_action_context_id => p_assactid,
3373 p_assignment_id => csr_rec.assignment_id,
3374 p_person_id => l_person_id,
3375 p_date_earned => csr_rec.date_earned,
3376 p_termination_date => l_termination_date,
3377 p_supp_pymt_date => l_supp_pymt_date,
3378 p_supp_flag => l_supp_flag,
3379 p_deceased_flag => l_deceased_flag
3380 );
3381 OPEN cur_get_last_p45(l_person_id,l_termination_date,g_archive_pact);
3382 FETCH cur_get_last_p45 into l_last_p45_action;
3383 CLOSE cur_get_last_p45;
3384
3385 -- Fetch the Payroll action of Last P45 5005788
3386 OPEN cur_get_p45_pact(l_last_p45_action);
3387 FETCH cur_get_p45_pact INTO l_last_p45_pact;
3388 CLOSE cur_get_p45_pact;
3389 hr_utility.set_location(' l_termination_date = '||l_termination_date,30);
3390 hr_utility.set_location(' l_supp_pymt_date = '||l_supp_pymt_date,30);
3391 hr_utility.set_location(' l_supp_flag = '||l_supp_flag,30);
3392
3393 ------------------
3394 IF ((l_child_pay_action IS NULL) and l_supp_flag = 'Y' ) THEN
3395 NULL;
3396 ELSE
3397 IF (l_record_count = 0 AND csr_rec.assignment_id = l_assignment_id)
3398 THEN
3399 -- Create child P45 action to lock the child payroll process child action
3400 -- To avoid data corruption 4468864
3401 SELECT pay_assignment_actions_s.NEXTVAL
3402 INTO l_actid
3403 FROM dual;
3404
3405 hr_nonrun_asact.insact(
3406 lockingactid => l_actid
3407 , assignid => l_assignment_id
3408 , pactid => g_archive_pact
3409 , chunk => l_chunk_number
3410 , greid => g_paye_ref
3411 , prepayid => NULL
3412 , status => 'C'
3413 , source_act => p_assactid);
3414
3415 hr_utility.set_location('creating lock4 ' || l_actid || ' to ' || l_child_pay_action,30);
3416 -- bug 5386432, checks l_child_pay_action is not null, since for zero
3417 -- earnigns there will not child actions, so cant lock any
3418 IF l_child_pay_action IS NOT NULL THEN
3419 hr_nonrun_asact.insint(
3420 lockingactid => l_actid
3421 , lockedactid => l_child_pay_action);
3422 END IF;
3423
3424 pay_ie_p45_archive.archive_p45_info(
3425 p_action_context_id => p_assactid,
3426 p_assignment_id => csr_rec.assignment_id, -- assignment_id
3427 p_payroll_id => l_payroll_id,
3428 p_date_earned => csr_rec.date_earned,
3429 p_child_run_ass_act_id => l_child_pay_action,
3430 p_supp_flag => l_supp_flag,
3431 p_person_id => l_person_id,
3432 p_termination_date => l_termination_date,
3433 p_child_pay_action => l_child_pay_action, -- child payroll assignment action id
3434 p_supp_pymt_date => l_supp_pymt_date,
3435 p_deceased_flag => l_deceased_flag);
3436
3437 -- Moved this above as we will now have to archive for Main P45 even if
3438 -- it has no run-results. bug 5386432
3439 /*open cur_get_last_p45(l_person_id,l_termination_date,g_archive_pact);
3440 fetch cur_get_last_p45 into l_last_p45_action;
3441 close cur_get_last_p45;
3442
3443 -- Fetch the Payroll action of Last P45 5005788
3444 OPEN cur_get_p45_pact(l_last_p45_action);
3445 FETCH cur_get_p45_pact INTO l_last_p45_pact;
3446 CLOSE cur_get_p45_pact; */
3447
3448 hr_utility.set_location('sg Person Id ='||l_person_id,32);
3449 hr_utility.set_location('sg Termination Date ='||l_termination_date,33);
3450 hr_utility.set_location('sg Payroll action ='||g_archive_pact,34);
3451 hr_utility.set_location('sg P45 action ='||l_last_p45_action,35);
3452
3453 IF l_last_p45_action IS NOT NULL THEN
3454 hr_nonrun_asact.insint(
3455 lockingactid => l_actid
3456 , lockedactid => l_last_p45_action);
3457 END IF;
3458
3459 pay_ie_p45_archive.archive_employee_details(
3460 p_assactid => l_action_context_id -- P45 master action
3461 , p_assignment_id => l_assignment_id
3462 , p_curr_pymt_ass_act_id => csr_rec.pre_assignment_action_id -- prepayment assignment_action_id
3463 , p_date_earned => csr_rec.date_earned -- payroll date_earned
3464 , p_payroll_child_actid => l_child_pay_action -- payroll assignment action id
3465 , p_curr_pymt_eff_date => csr_rec.pre_effective_date -- prepayment effective_date
3466 , p_time_period_id => csr_rec.time_period_id -- payroll time_period_id
3467 , p_record_count => l_record_count
3468 , p_supp_flag => l_supp_flag
3469 , p_person_id => l_person_id
3470 , p_termination_date => l_termination_date
3471 , p_last_act_seq => l_lat_act_seq
3472 , p_last_p45_act => l_last_p45_action
3473 ,p_effective_date => csr_rec.effective_date);
3474
3475 -- Statutory Balances are archived for all Separate Payment assignment actions
3476 -- and the last (i.e. highest action_sequence) Process Separately assignment action
3477 -- (EMEA BALANCES)
3478 hr_utility.set_location('Archive User Balances - Complete',60);
3479 -- archive statutory balances
3480 hr_utility.set_location('Archive Statutory Balances - Starting',70);
3481 hr_utility.set_location('g_max_statutory_balance_index = '|| g_max_statutory_balance_index,70);
3482 FOR l_index IN 1 .. g_max_statutory_balance_index
3483 LOOP
3484 hr_utility.set_location('l_index = ' || l_index,70);
3485 hr_utility.set_location('balance_name ='||g_statutory_balance_table(l_index).balance_name,70);
3486 hr_utility.set_location('database_item_suffix ='||g_statutory_balance_table(l_index).database_item_suffix,70);
3487 /*
3488 --Bug:2448728.Passing the prepayment assignment action id to p_source_id as the _PAYMENTS
3489 --balances are fed only during pre Payments.
3490 If g_statutory_balance_table(l_index).balance_name = 'Total Pay' Then
3491 pay_ie_p45_archive.process_balance (
3492 p_action_context_id => l_action_context_id
3493 , p_assignment_id => csr_rec.assignment_id
3494 , p_source_id => csr_rec.pre_assignment_action_id
3495 , p_effective_date => csr_rec.effective_date
3496 , p_balance => g_statutory_balance_table(l_index).balance_name
3497 , p_dimension => g_statutory_balance_table(l_index).database_item_suffix
3498 , p_defined_bal_id => g_statutory_balance_table(l_index).defined_balance_id
3499 , p_record_count => l_record_count);
3500 Else
3501 */
3502 -- IF ( l_supp_flag = 'Y' OR l_index < 19 OR l_index > 31 ) THEN
3503
3504 IF (l_index < 20) THEN -- Bug 5015438
3505 pay_ie_p45_archive.process_balance (
3506 p_action_context_id => l_action_context_id
3507 , p_assignment_id => csr_rec.assignment_id
3508 , p_person_id => l_person_id
3509 , p_source_id => l_child_pay_action
3510 , p_effective_date => csr_rec.effective_date
3511 , p_balance => g_statutory_balance_table(l_index).balance_name
3512 , p_dimension => g_statutory_balance_table(l_index).database_item_suffix
3513 , p_defined_bal_id => g_statutory_balance_table(l_index).defined_balance_id
3514 , p_record_count => l_record_count
3515 , p_termination_date => l_termination_date
3516 , p_supp_flag => l_supp_flag
3517 , p_last_p45_action => l_last_p45_action
3518 , p_last_p45_pact => l_last_p45_pact -- Bug 5005788
3519 , p_prev_src_id => l_prev_src_id);
3520 ELSE
3521 IF (l_supp_flag = 'Y') THEN
3522 pay_ie_p45_archive.process_supp_balance (
3523 p_action_context_id => l_action_context_id
3524 , p_assignment_id => csr_rec.assignment_id
3525 , p_person_id => l_person_id
3526 , p_source_id => l_child_pay_action
3527 , p_effective_date => csr_rec.effective_date
3528 , p_balance => g_statutory_balance_table(l_index).balance_name
3529 , p_dimension => g_statutory_balance_table(l_index).database_item_suffix
3530 , p_defined_bal_id => g_statutory_balance_table(l_index).defined_balance_id
3531 , p_record_count => l_record_count
3532 , p_termination_date => l_termination_date
3533 , p_supp_flag => l_supp_flag
3534 , p_last_p45_action => l_last_p45_action
3535 , p_last_p45_pact => l_last_p45_pact -- Bug 5005788
3536 , p_ytd_balance => g_statutory_balance_table(l_index - l_max_stat_balance).balance_name
3537 , p_ytd_def_bal_id => g_statutory_balance_table(l_index - l_max_stat_balance).defined_balance_id);
3538 END IF;
3539
3540 END IF;
3541 -- End If;
3542 END LOOP;
3543 hr_utility.set_location('Archive Statutory Balances - Complete',70);
3544 l_record_count := l_record_count + 1;
3545 END IF;
3546 END IF;
3547 CLOSE cur_child_pay_action;
3548 l_date_earned := csr_rec.date_earned;
3549 END LOOP;
3550 hr_utility.set_location('Leaving '|| l_proc,80);
3551 END archive_code;
3552
3553 --------------------------------------------------------------------------------
3554 -- Bug 2643489: Function to return balnce values archived by P45 Archive Process
3555 --------------------------------------------------------------------------------
3556
3557 -- Added the parameter p_payroll_action_id to improve the performance,
3558 FUNCTION get_arc_bal_value(
3559 p_assignment_action_id in number
3560 ,p_payroll_action_id in number
3561 ,p_balance_name in varchar2 ) return number
3562 AS
3563
3564 /* Split the cursor to 2 cursors to improve the performace.new parameter is added to reduce the number of
3565 tables involved to 2 from 5 (5005788) */
3566 CURSOR csr_get_def_bal(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
3567 ,p_balance_name pay_balance_types.balance_name%TYPE
3568 ) IS
3569 SELECT pai1.action_information2
3570 FROM pay_action_information pai1
3571 WHERE pai1.action_context_type = 'PA'
3572 AND pai1.action_information_category = 'EMEA BALANCE DEFINITION'
3573 AND substr(pai1.action_information4, 1,50) = p_balance_name
3574 AND pai1.action_context_id = p_payroll_action_id;
3575
3576 CURSOR csr_get_arc_bal_value(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
3577 ,p_def_bal_id pay_action_information.action_information1%TYPE) IS
3578 SELECT to_number(pai2.action_information4) balance_value
3579 FROM pay_action_information pai2
3580 WHERE pai2.action_context_id = p_assignment_action_id
3581 AND pai2.action_information_category = 'EMEA BALANCES'
3582 AND pai2.action_context_type = 'AAP'
3583 AND pai2.action_information1 = p_def_bal_id;
3584
3585 l_bal_value number:=null;
3586 l_def_bal_id pay_action_information.action_information1%TYPE := NULL;
3587
3588 BEGIN
3589
3590 --hr_utility.trace_on(null,'P45XML');
3591 IF p_assignment_action_id IS NOT NULL AND p_balance_name IS NOT NULL THEN
3592 OPEN csr_get_def_bal(p_payroll_action_id,p_balance_name);
3593 FETCH csr_get_def_bal INTO l_def_bal_id;
3594 CLOSE csr_get_def_bal;
3595 hr_utility.set_location('p_payroll_action_id '||p_payroll_action_id,400);
3596 hr_utility.set_location('p_balance_name '||p_balance_name,400);
3597 hr_utility.set_location('l_def_bal_id '||l_def_bal_id,400);
3598
3599 IF l_def_bal_id IS NOT NULL THEN
3600 OPEN csr_get_arc_bal_value(p_assignment_action_id,l_def_bal_id);
3601 FETCH csr_get_arc_bal_value into l_bal_value;
3602 CLOSE csr_get_arc_bal_value;
3603 END IF;
3604
3605 END IF;
3606 /*Bug 3986018*/
3607 return(nvl(l_bal_value,0));
3608
3609 END get_arc_bal_value;
3610
3611 ---------------------------------------------------------------------
3612 -- Procedure generate_xml - Generates P45 XML Output File
3613 -- viviswan 29-may-2002 created
3614 ---------------------------------------------------------------------
3615 PROCEDURE generate_xml(
3616 errbuf out nocopy varchar2
3617 ,retcode out nocopy varchar2
3618 ,p_p45_archive_process in number
3619 ,p_assignment_id in number) IS
3620 -- Commented for Bug 2643489 Performance
3621 /*SELECT paa.assignment_id assignment_id
3622 ,paa.assignment_action_id
3623 ,pai_iep45.action_information2 supp_flag
3624 ,emp_details.pps_no ppsn
3625 ,emp_details.last_name surname
3626 ,emp_details.first_name firstname
3627 ,emp_details.works_no works
3628 ,emp_details.deceased deceased
3629 ,to_char(emp_details.date_of_birth,'dd/mm/rrrr') dob
3630 ,emp_address.address1 address1
3631 ,emp_address.address2 address2
3632 ,emp_address.address3 address3
3633 ,to_char(emp_details.date_of_commencement,'dd/mm/rrrr') start1
3634 ,to_char(emp_details.date_of_leaving,'dd/mm/rrrr') end1
3635 ,decode(ptp.period_type,'Lunar Month','W',decode(instr(ptp.period_type,'Week'),0,'M','W')) freq
3636 ,to_number(substr(pai_iep45.action_information5, 1,30)) period
3637 ,(round(to_number(substr(pai_ieed.action_information26, 1,30)),2)*100) taxcredit
3638 ,(round(to_number(substr(pai_ieed.action_information27, 1,30)),2)*100) cutoff
3639 ,pai_iep45.action_information4 emergency_tax
3640 ,substr(pai_ieed.action_information22, 1,30) prsi_class
3641 ,(round(to_number(emp_paye.total_tax),2)*100) totaltax
3642 ,(round(to_number(emp_paye.total_pay),2)*100) totalpay
3643 ,(round(to_number(emp_paye.this_tax),2)*100) thistax
3644 ,(round(to_number(emp_paye.this_pay),2)*100) thispay
3645 ,(round(to_number(emp_paye.lump_sum),2)*100) lumpsum
3646 ,(round(to_number(emp_prsi.total_prsi),2)*100) totalprsi
3647 ,(round(to_number(emp_prsi.total_employee_prsi),2)*100) employeeprsi
3648 ,emp_prsi.insurable_weeks totalweeks
3649 ,emp_prsi.class_a_insurable_weeks totalaweeks
3650 ,(round(to_number(emp_soc.disability_benefit),2)*100) benefit
3651 ,(round(to_number(emp_soc.red_tax_credit),2)*100) taxcreditreduction
3652 ,(round(to_number(emp_soc.red_std_cut_off),2)*100) cutoffreduction
3653 ,emp_soc.non_cummulative_basis noncumulative
3654 ,pai_epif.action_information6 employer_number
3655 ,(round(to_number(emp_supp.total_tax),2)*100) supp_totaltax
3656 ,(round(to_number(emp_supp.total_pay),2)*100) supp_totalpay
3657 ,(round(to_number(emp_supp.lump_sum),2)*100) supp_lumpsum
3658 ,(round(to_number(emp_supp.total_prsi),2)*100) supp_totalprsi
3659 ,(round(to_number(emp_supp.total_employee_prsi),2)*100) supp_employeeprsi
3660 ,emp_supp.insurable_weeks supp_totalweeks
3661 FROM pay_action_information pai_ed
3662 ,pay_action_information pai_iep45
3663 ,pay_action_information pai_ieed
3664 ,pay_action_information pai_epif
3665 ,pay_assignment_actions paa
3666 ,pay_action_interlocks pai_arc
3667 ,pay_assignment_actions paa_payroll
3668 ,per_time_periods ptp
3669 ,pay_ie_p45_address_details emp_address
3670 ,pay_ie_p45_employee_details emp_details
3671 ,pay_ie_p45_soc_ben_details emp_soc
3672 ,pay_ie_p45_prsi_details emp_prsi
3673 ,pay_ie_p45_paye_details emp_paye
3674 ,pay_ie_p45_supp_details emp_supp
3675 WHERE paa.payroll_action_id = c_p45_arch_id
3676 AND paa.assignment_action_id = pai_arc.locking_action_id
3677 AND paa_payroll.assignment_action_id = pai_arc.locked_action_id
3678 AND paa.assignment_action_id = pai_iep45.action_context_id
3679 AND pai_iep45.action_context_type ='AAP'
3680 AND pai_iep45.action_information_category = 'IE P45 INFORMATION'
3681 AND paa.assignment_action_id = pai_ed.action_context_id
3682 AND pai_ed.action_context_type ='AAP'
3683 AND pai_ed.action_information_category = 'EMPLOYEE DETAILS'
3684 AND ptp.time_period_id = pai_ed.action_information16
3685 AND paa.assignment_action_id = pai_ieed.action_context_ID
3686 AND pai_ieed.action_context_type ='AAP'
3687 AND pai_ieed.action_information_category = 'IE EMPLOYEE DETAILS'
3688 AND paa.payroll_action_id = pai_epif.action_context_ID (+)
3689 AND pai_epif.action_context_type (+) ='PA'
3690 AND pai_epif.action_information_category (+)= 'EMEA PAYROLL INFO'
3691 AND pai_epif.action_information1 = paa_payroll.payroll_action_id
3692 AND emp_address.assignment_action_id = paa.assignment_action_id
3693 AND emp_details.assignment_action_id = paa.assignment_action_id
3694 AND emp_soc.assignment_action_id = paa.assignment_action_id
3695 AND emp_prsi.assignment_action_id = paa.assignment_action_id
3696 AND emp_paye.assignment_action_id = paa.assignment_action_id
3697 AND emp_supp.assignment_id (+) = paa.assignment_id
3698 AND paa.assignment_id = NVL(p_assignment_id,paa.assignment_id)
3699 ORDER BY pai_iep45.action_information2;
3700 */
3701 -- The above cursor has been modified as specified below.
3702 CURSOR cur_p45_details(
3703 c_p45_arch_id pay_payroll_actions.payroll_action_id%TYPE) IS
3704 SELECT paa.assignment_id assignment_id
3705 ,paa.assignment_action_id
3706 ,pai_iep45.action_information2 supp_flag
3707 ,decode(ptp.period_type,'Lunar Month','W',decode(instr(ptp.period_type,'Week'),0,'M','W')) freq
3708 ,to_number(substr(pai_iep45.action_information5, 1,30)) period
3709 ,pai_iep45.action_information4 emergency_tax,
3710 to_date(substr(pai_iep45.action_information7, 1,30),'DD/MM/RRRR') date_paid --Bug 3991416
3711 FROM pay_action_information pai_ed
3712 ,pay_action_information pai_iep45
3713 ,pay_assignment_actions paa
3714 ,per_time_periods ptp
3715 WHERE paa.payroll_action_id = c_p45_arch_id
3716 AND paa.assignment_action_id = pai_iep45.action_context_id
3717 AND pai_iep45.action_context_type ='AAP'
3718 AND pai_iep45.action_information_category = 'IE P45 INFORMATION'
3719 AND ptp.time_period_id = pai_ed.action_information16
3720 AND paa.assignment_action_id = pai_ed.action_context_ID
3721 AND pai_ed.action_context_type ='AAP'
3722 AND pai_ed.action_information_category = 'EMPLOYEE DETAILS'
3723 AND paa.assignment_id = NVL(p_assignment_id,paa.assignment_id)
3724 ORDER BY pai_iep45.action_information2;
3725 --
3726 CURSOR cur_p45_employer_no (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3727 SELECT pai_epif.action_information6 employer_number
3728 FROM pay_assignment_actions paa
3729 ,pay_action_interlocks pai_arc
3730 ,pay_assignment_actions paa_payroll
3731 ,pay_action_information pai_epif
3732 WHERE paa.assignment_action_id = p_assignment_action_id
3733 AND paa.assignment_action_id = pai_arc.locking_action_id
3734 AND paa_payroll.assignment_action_id = pai_arc.locked_action_id
3735 AND paa.payroll_action_id = pai_epif.action_context_ID
3736 AND pai_epif.action_context_type ='PA'
3737 AND pai_epif.action_information_category = 'EMEA PAYROLL INFO'
3738 AND pai_epif.action_information1 = paa_payroll.payroll_action_id;
3739 --
3740 CURSOR cur_p45_ie_emp_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3741 SELECT (round(to_number(substr(nvl(pai_ieed.action_information26,'0'), 1,30)),2)*100) taxcredit
3742 ,(round(to_number(substr(nvl(pai_ieed.action_information27,'0'), 1,30)),2)*100) cutoff
3743 ,substr(pai_ieed.action_information22, 1,30) prsi_class
3744 FROM pay_action_information pai_ieed
3745 WHERE pai_ieed.action_context_ID = p_assignment_action_id
3746 AND pai_ieed.action_context_type = 'AAP'
3747 AND pai_ieed.action_information_category = 'IE EMPLOYEE DETAILS';
3748 cur_p45_ie_emp_details_rec cur_p45_ie_emp_details%ROWTYPE;
3749 --
3750 --6615117
3751 CURSOR cur_p45_emp_soc_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3752 SELECT (round(to_number(nvl(emp_soc.disability_benefit,'0')),2)*100) benefit
3753 ,(round(to_number(nvl(emp_soc.red_tax_credit,'0')),2)*100) taxcreditreduction
3754 ,(round(to_number(nvl(emp_soc.red_std_cut_off,'0')),2)*100) cutoffreduction
3755 ,emp_soc.non_cummulative_basis noncumulative
3756 FROM pay_ie_p45_soc_ben_details emp_soc
3757 WHERE emp_soc.assignment_action_id = p_assignment_action_id;
3758 cur_p45_emp_soc_details_rec cur_p45_emp_soc_details%ROWTYPE;
3759 --
3760 --6615117
3761 CURSOR cur_p45_emp_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3762 SELECT emp_details.pps_no ppsn
3763 ,emp_details.last_name surname
3764 ,emp_details.first_name firstname
3765 ,emp_details.works_no works
3766 ,emp_details.deceased deceased
3767 ,to_char(emp_details.date_of_birth,'dd/mm/rrrr') dob
3768 ,to_char(emp_details.date_of_commencement,'dd/mm/rrrr') start1
3769 ,to_char(emp_details.date_of_leaving,'dd/mm/rrrr') end1
3770 FROM pay_ie_p45_employee_details emp_details
3771 WHERE emp_details.assignment_action_id = p_assignment_action_id;
3772 cur_p45_emp_details_rec cur_p45_emp_details%ROWTYPE;
3773 --
3774 CURSOR cur_p45_emp_address (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3775 SELECT emp_address.address1 address1
3776 ,emp_address.address2 address2
3777 ,emp_address.address3 address3
3778 FROM pay_ie_p45_address_details emp_address
3779 WHERE emp_address.assignment_action_id = p_assignment_action_id;
3780 cur_p45_emp_address_rec cur_p45_emp_address%ROWTYPE;
3781 --
3782 CURSOR cur_p45_paye_prsi_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3783 SELECT (round(to_number(emp_paye.total_tax),2)*100) totaltax
3784 ,(round(to_number(emp_paye.total_pay),2)*100) totalpay
3785 ,(round(to_number(emp_paye.this_tax),2)*100) thistax
3786 ,(round(to_number(emp_paye.this_pay),2)*100) thispay
3787 ,(round(to_number(emp_paye.lump_sum),2)*100) lumpsum
3788 ,(round(to_number(emp_prsi.total_employer_prsi),2)*100) employerprsi -- Bug 5005788
3789 ,(round(to_number(emp_prsi.total_employee_prsi),2)*100) employeeprsi
3790 ,emp_prsi.insurable_weeks totalweeks
3791 ,emp_prsi.class_a_insurable_weeks totalaweeks
3792 FROM pay_ie_p45_prsi_details emp_prsi
3793 ,pay_ie_p45_paye_details emp_paye
3794 WHERE emp_prsi.assignment_action_id = p_assignment_action_id
3795 AND emp_paye.assignment_action_id = p_assignment_action_id;
3796 cur_p45_paye_prsi_rec cur_p45_paye_prsi_details%ROWTYPE;
3797 --
3798 --Bug 3991416 Added period,frequency and date earned check to fetch single record
3799 CURSOR cur_p45_supp_details (p_assignment_id per_all_assignments_f.assignment_id%TYPE,
3800 p_date_earned date,
3801 p_period NUMBER,
3802 p_freq VARCHAR2) IS
3803 SELECT (round(to_number(emp_supp.total_tax),2)*100) supp_totaltax
3804 ,(round(to_number(emp_supp.total_pay),2)*100) supp_totalpay
3805 ,(round(to_number(emp_supp.lump_sum),2)*100) supp_lumpsum
3806 ,(round(to_number(emp_supp.total_employer_prsi),2)*100) supp_totalprsi -- Bug 5005788
3807 ,(round(to_number(emp_supp.total_employee_prsi),2)*100) supp_employeeprsi
3808 ,emp_supp.insurable_weeks supp_totalweeks
3809 ,emp_supp.supp_insurable_classA_weeks supp_classA_weeks -- Bug 5015438
3810 FROM pay_ie_p45_supp_details emp_supp
3811 WHERE emp_supp.assignment_id = p_assignment_id
3812 AND emp_supp.date_paid =p_date_earned
3813 AND emp_supp.pay_period =p_period
3814 AND emp_supp.period_frequency = decode(p_freq,'M','Monthly','Weekly');
3815 --
3816 CURSOR cur_p30_start_date(
3817 c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
3818 SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
3819 FROM pay_assignment_actions paa_p30,
3820 pay_action_interlocks pai_p30,
3821 pay_assignment_actions paa_arc,
3822 pay_payroll_actions ppa_arc
3823 WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
3824 AND paa_p30.assignment_action_id = pai_p30.locking_action_id
3825 AND paa_arc.assignment_action_id = pai_p30.locked_action_id
3826 AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
3827 --
3828 CURSOR cur_employer_address(
3829 c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE) IS
3830 SELECT substr(pai.action_information5,1,30) employer_tax_addr1
3831 ,substr(pai.action_information6,1,30) employer_tax_addr2
3832 ,substr(pai.action_information7,1,30) employer_tax_addr3
3833 ,substr(pai.action_information26,1,30) employer_tax_contact
3834 ,substr(pai.action_information27,1,12) employer_tax_ref_phone
3835 ,substr(pai.action_information28,1,30) employer_tax_rep_name
3836 FROM pay_action_information pai
3837 WHERE pai.action_context_id = c_payroll_action_id
3838 AND pai.action_context_type = 'PA'
3839 AND pai.action_information_category = 'ADDRESS DETAILS'
3840 AND pai.action_information14 = 'IE Employer Tax Address';
3841 --
3842 l_root_start_tag varchar2(200);
3843 l_root_end_tag varchar2(50);
3844 --
3845 l_employer_start_tag varchar2(20);
3846 l_employer_end_tag varchar2(20);
3847 --
3848 l_p45_start_tag varchar2(20);
3849 l_p45_end_tag varchar2(20);
3850 --
3851 --
3852 l_employer_paye_number varchar2(80);
3853 l_employer_number varchar2(10);
3854 l_employer_name varchar2(30);
3855 l_employer_add1 varchar2(30);
3856 l_employer_add2 varchar2(30);
3857 l_employer_add3 varchar2(30);
3858 l_employer_contact varchar2(20);
3859 l_employer_phone varchar2(12);
3860 --
3861 l_supp_totaltax number;
3862 l_supp_totalpay number;
3863 l_supp_lumpsum number;
3864 l_supp_totalprsi number;
3865 l_supp_employeeprsi number;
3866 l_supp_totalweeks varchar2(10);
3867 l_supp_classA_weeks varchar2(10); -- Bug 5015438
3868 --
3869 l_employment_unit varchar2(3);
3870 once_per_run varchar2(1);
3871 vfrom number;
3872 vfound number;
3873 vto number;
3874 v_prsi_class varchar2(10);
3875 ppsn_flag number(1) :=1;
3876 warn_status number(1) := 0;
3877 l_conc_status BOOLEAN;
3878 l_total_prsi NUMBER;
3879 BEGIN
3880 once_per_run := 'N' ;
3881 l_employment_unit := '000';
3882 l_root_start_tag :='<P45File currency="E" formversion="3" language="E" printer="0">';
3883 l_root_end_tag :='</P45File>';
3884 FOR p45_rec IN cur_p45_details(p_p45_archive_process) LOOP
3885 OPEN cur_p45_paye_prsi_details(p45_rec.assignment_action_id);
3886 FETCH cur_p45_paye_prsi_details into cur_p45_paye_prsi_rec;
3887 CLOSE cur_p45_paye_prsi_details;
3888 --
3889 OPEN cur_p45_emp_address(p45_rec.assignment_action_id);
3890 FETCH cur_p45_emp_address into cur_p45_emp_address_rec;
3891 CLOSE cur_p45_emp_address;
3892 --
3893 OPEN cur_p45_emp_soc_details(p45_rec.assignment_action_id);
3894 FETCH cur_p45_emp_soc_details into cur_p45_emp_soc_details_rec;
3895 CLOSE cur_p45_emp_soc_details;
3896 --
3897 OPEN cur_p45_emp_details(p45_rec.assignment_action_id);
3898 FETCH cur_p45_emp_details into cur_p45_emp_details_rec;
3899 CLOSE cur_p45_emp_details;
3900 --
3901 OPEN cur_p45_ie_emp_details(p45_rec.assignment_action_id);
3902 FETCH cur_p45_ie_emp_details into cur_p45_ie_emp_details_rec;
3903 CLOSE cur_p45_ie_emp_details;
3904 --
3905 OPEN cur_p45_employer_no(p45_rec.assignment_action_id);
3906 FETCH cur_p45_employer_no into l_employer_number;
3907 CLOSE cur_p45_employer_no;
3908
3909 l_total_prsi := 0; -- Bug 5005788
3910
3911 IF p45_rec.supp_flag = 'Y' THEN
3912 -- Get Supp Details
3913 /*Bug 3991416*/
3914 OPEN cur_p45_supp_details(p45_rec.assignment_id,p45_rec.date_paid,p45_rec.period,p45_rec.freq);
3915 FETCH cur_p45_supp_details INTO l_supp_totaltax
3916 ,l_supp_totalpay
3917 ,l_supp_lumpsum
3918 ,l_supp_totalprsi
3919 ,l_supp_employeeprsi
3920 ,l_supp_totalweeks
3921 ,l_supp_classA_weeks ; -- Bug 5015438
3922 l_supp_totalprsi := NVL(l_supp_totalprsi,0) + NVL(l_supp_employeeprsi,0); -- Bug 5005788
3923 CLOSE cur_p45_supp_details;
3924 END IF;
3925 -- Report the assignment if the Pay and Tax values exist.
3926 IF (((p45_rec.supp_flag = 'N') /*AND (NVL(cur_p45_paye_prsi_rec.totalpay,0) <> 0 OR
3927 NVL(cur_p45_paye_prsi_rec.totaltax,0) <> 0 OR
3928 NVL(cur_p45_paye_prsi_rec.thispay,0) <> 0 OR
3929 NVL(cur_p45_paye_prsi_rec.thistax,0) <> 0 OR
3930 NVL(cur_p45_paye_prsi_rec.lumpsum,0) <> 0 OR
3931 NVL(cur_p45_paye_prsi_rec.employerprsi,0) <> 0 OR -- Bug 5005788
3932 NVL(cur_p45_paye_prsi_rec.employeeprsi,0) <> 0 OR
3933 NVL(cur_p45_paye_prsi_rec.totalweeks,0) <> 0
3934 )*/
3935 ) OR
3936 ((p45_rec.supp_flag = 'Y') AND (NVL(l_supp_totalpay,0) <> 0 OR
3937 NVL(l_supp_totaltax,0) <> 0 OR
3938 NVL(l_supp_totalprsi,0) <> 0 OR
3939 NVL(l_supp_employeeprsi,0) <> 0 OR
3940 NVL(l_supp_lumpsum,0) <> 0 OR
3941 NVL(l_supp_totalweeks,0) <> 0 OR
3942 NVL(l_supp_classA_weeks,0) <> 0 -- Bug 5015438
3943 )
3944 )
3945 ) THEN
3946 IF once_per_run = 'N' THEN
3947 -- Start of xml doc
3948 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8"?>');
3949 -- P45File root ELEMENT
3950 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_start_tag);
3951 -- Get Employer Address
3952 OPEN cur_employer_address(p_p45_archive_process);
3953 FETCH cur_employer_address INTO l_employer_add1
3954 ,l_employer_add2
3955 ,l_employer_add3
3956 ,l_employer_contact
3957 ,l_employer_phone
3958 ,l_employer_name;
3959 CLOSE cur_employer_address;
3960 -- Employer ELEMENT
3961 FND_FILE.PUT(FND_FILE.OUTPUT,' <Employer ');
3962 FND_FILE.PUT(FND_FILE.OUTPUT,'number="' || l_employer_number ||'" ');
3963 FND_FILE.PUT(FND_FILE.OUTPUT,'name="' || l_employer_name ||'" ');
3964 IF l_employer_add1 IS NOT NULL THEN
3965 FND_FILE.PUT(FND_FILE.OUTPUT,'address1="' || l_employer_add1 ||'" ');
3966 END IF;
3967 IF l_employer_add2 IS NOT NULL THEN
3968 FND_FILE.PUT(FND_FILE.OUTPUT,'address2="' || l_employer_add2 ||'" ');
3969 END IF;
3970 IF l_employer_add3 IS NOT NULL THEN
3971 FND_FILE.PUT(FND_FILE.OUTPUT,'address3="' || l_employer_add3 ||'" ');
3972 END IF;
3973 FND_FILE.PUT(FND_FILE.OUTPUT,'contact="' || l_employer_contact ||'" ');
3974 FND_FILE.PUT(FND_FILE.OUTPUT,'phone="' || l_employer_phone ||'" ');
3975 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
3976 -- End of Employer
3977 once_per_run := 'Y';
3978 END IF;
3979 IF p45_rec.supp_flag = 'N' THEN
3980 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <P45>');
3981 ELSE
3982 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <P45Supp>');
3983 END IF;
3984 -- Employee
3985 FND_FILE.PUT(FND_FILE.OUTPUT,' <Employee ');
3986 IF cur_p45_emp_details_rec.ppsn IS NOT NULL THEN -- Optional
3987 FND_FILE.PUT(FND_FILE.OUTPUT,'ppsn="' || cur_p45_emp_details_rec.ppsn ||'" ');
3988 ppsn_flag := 1;
3989 ELSE
3990 ppsn_flag := 0;
3991 END IF;
3992
3993 -- required
3994 FND_FILE.PUT(FND_FILE.OUTPUT,'surname="' || cur_p45_emp_details_rec.surname ||'" ');
3995 FND_FILE.PUT(FND_FILE.OUTPUT,'firstnames="' || cur_p45_emp_details_rec.firstname ||'" ');
3996 IF cur_p45_emp_details_rec.works IS NOT NULL THEN -- Optional
3997 FND_FILE.PUT(FND_FILE.OUTPUT,'works="' || cur_p45_emp_details_rec.works ||'" ');
3998 END IF;
3999
4000 IF cur_p45_emp_details_rec.dob IS NOT NULL THEN -- Optional
4001 FND_FILE.PUT(FND_FILE.OUTPUT,'dob="' || cur_p45_emp_details_rec.dob ||'" ');
4002 END IF;
4003
4004 IF cur_p45_emp_address_rec.address1 IS NOT NULL THEN -- Optional
4005 FND_FILE.PUT(FND_FILE.OUTPUT,'address1="' || cur_p45_emp_address_rec.address1 ||'" ');
4006 ELSIF cur_p45_emp_address_rec.address1 IS NULL and ppsn_flag = 0 THEN
4007 -- Enter the employee details in the log
4008 warn_status := 1;
4009 Fnd_file.put_line(FND_FILE.LOG,'Employee '|| cur_p45_emp_details_rec.works||' : PPSN and Address Line 1 missing for employee' );
4010 END IF;
4011
4012 IF cur_p45_emp_address_rec.address2 IS NOT NULL THEN -- Optional
4013 FND_FILE.PUT(FND_FILE.OUTPUT,'address2="' || cur_p45_emp_address_rec.address2 ||'" ');
4014 ELSIF cur_p45_emp_address_rec.address2 IS NULL and ppsn_flag = 0 THEN
4015 -- Enter the employee details in the log
4016 warn_status := 1;
4017 Fnd_file.put_line(FND_FILE.LOG,'Employee '|| cur_p45_emp_details_rec.works||' : PPSN and Address Line 2 missing for employee');
4018 END IF;
4019
4020 IF cur_p45_emp_address_rec.address3 IS NOT NULL THEN -- Optional
4021 FND_FILE.PUT(FND_FILE.OUTPUT,'address3="' || cur_p45_emp_address_rec.address3 ||'" ');
4022 END IF;
4023 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
4024 -- Employment
4025 FND_FILE.PUT(FND_FILE.OUTPUT,' <Employment ');
4026 IF cur_p45_emp_details_rec.start1 IS NOT NULL THEN -- Optional
4027 FND_FILE.PUT(FND_FILE.OUTPUT,'start="' || cur_p45_emp_details_rec.start1 ||'" ');
4028 END IF;
4029 -- required
4030 FND_FILE.PUT(FND_FILE.OUTPUT,'end="' || cur_p45_emp_details_rec.end1 ||'" ');
4031 FND_FILE.PUT(FND_FILE.OUTPUT,'unit="' || l_employment_unit ||'" ');
4032 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
4033 -- Pay
4034 FND_FILE.PUT(FND_FILE.OUTPUT,' <Pay ');
4035 -- required
4036 FND_FILE.PUT(FND_FILE.OUTPUT,'freq="' || p45_rec.freq ||'" ');
4037 FND_FILE.PUT(FND_FILE.OUTPUT,'period="' || p45_rec.period ||'" ');
4038 FND_FILE.PUT(FND_FILE.OUTPUT,'taxcredit="' || cur_p45_ie_emp_details_rec.taxcredit ||'" ');
4039 FND_FILE.PUT(FND_FILE.OUTPUT,'cutoff="' || cur_p45_ie_emp_details_rec.cutoff ||'" ');
4040
4041 IF p45_rec.emergency_tax = 'Y' THEN
4042 FND_FILE.PUT(FND_FILE.OUTPUT,'emergency="' || 'true' ||'" ');
4043 ELSE
4044 FND_FILE.PUT(FND_FILE.OUTPUT,'emergency="' || 'false' ||'" ');
4045 END IF;
4046 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
4047
4048 IF p45_rec.supp_flag = 'N' THEN
4049 -- Tax Details for Normal P45 Run
4050 FND_FILE.PUT(FND_FILE.OUTPUT,' <TaxDetails ');
4051 IF cur_p45_paye_prsi_rec.totalpay IS NOT NULL THEN -- Optional
4052 FND_FILE.PUT(FND_FILE.OUTPUT,'totalpay="' || cur_p45_paye_prsi_rec.totalpay ||'" ');
4053 END IF;
4054 IF cur_p45_paye_prsi_rec.totaltax IS NOT NULL THEN -- Optional
4055 FND_FILE.PUT(FND_FILE.OUTPUT,'totaltax="' || cur_p45_paye_prsi_rec.totaltax ||'" ');
4056 END IF;
4057 IF cur_p45_paye_prsi_rec.thispay IS NOT NULL THEN -- Optional
4058 FND_FILE.PUT(FND_FILE.OUTPUT,'thispay="' || cur_p45_paye_prsi_rec.thispay ||'" ');
4059 END IF;
4060 IF cur_p45_paye_prsi_rec.thistax IS NOT NULL THEN -- Optional
4061 -- for bug 5401393, negative tax should not be displayed with - sign.
4062 FND_FILE.PUT(FND_FILE.OUTPUT,'thistax="' || abs(cur_p45_paye_prsi_rec.thistax) ||'" ');
4063 IF cur_p45_paye_prsi_rec.thistax < 0 THEN
4064 FND_FILE.PUT(FND_FILE.OUTPUT,'thistaxrefunded="true" ');
4065 ELSE
4066 FND_FILE.PUT(FND_FILE.OUTPUT,'thistaxrefunded="false" ');
4067 END IF;
4068 END IF;
4069
4070 IF cur_p45_paye_prsi_rec.lumpsum IS NOT NULL THEN -- Optional
4071 FND_FILE.PUT(FND_FILE.OUTPUT,'lumpsum="' || cur_p45_paye_prsi_rec.lumpsum ||'" ');
4072 END IF;
4073 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
4074 -- PRSI
4075 FND_FILE.PUT(FND_FILE.OUTPUT,' <PRSI ');
4076 -- Bug 5005788
4077 l_total_prsi := NVL(cur_p45_paye_prsi_rec.employerprsi,0) + NVL(cur_p45_paye_prsi_rec.employeeprsi,0);
4078 FND_FILE.PUT(FND_FILE.OUTPUT,'total="' || NVL(l_total_prsi,0) ||'" ');
4079 IF cur_p45_paye_prsi_rec.employeeprsi IS NOT NULL THEN -- Optional
4080 FND_FILE.PUT(FND_FILE.OUTPUT,'employee="' || NVL(cur_p45_paye_prsi_rec.employeeprsi,0) ||'" ');
4081 END IF;
4082 FND_FILE.PUT(FND_FILE.OUTPUT,'weeks="' || NVL(cur_p45_paye_prsi_rec.totalweeks,0) ||'" ');
4083 ELSE
4084 -- Tax Details for P45 Supp Run
4085 FND_FILE.PUT(FND_FILE.OUTPUT,' <TaxDetails ');
4086 IF l_supp_totalpay IS NOT NULL THEN -- Optional
4087 FND_FILE.PUT(FND_FILE.OUTPUT,'totalpay="' || l_supp_totalpay ||'" ');
4088 END IF;
4089 IF l_supp_totaltax IS NOT NULL THEN -- Optional
4090 FND_FILE.PUT(FND_FILE.OUTPUT,'totaltax="' || l_supp_totaltax ||'" ');
4091 END IF;
4092 IF l_supp_lumpsum IS NOT NULL THEN -- Optional
4093 FND_FILE.PUT(FND_FILE.OUTPUT,'lumpsum="' || l_supp_lumpsum ||'" ');
4094 END IF;
4095 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
4096 -- PRSI details for P45 Supp Run
4097 FND_FILE.PUT(FND_FILE.OUTPUT,' <PRSI ');
4098 FND_FILE.PUT(FND_FILE.OUTPUT,'total="' || NVL(l_supp_totalprsi,0) ||'" ');
4099 IF l_supp_employeeprsi IS NOT NULL THEN -- Optional
4100 FND_FILE.PUT(FND_FILE.OUTPUT,'employee="' || NVL(l_supp_employeeprsi,0) ||'" ');
4101 END IF;
4102 FND_FILE.PUT(FND_FILE.OUTPUT,'weeks="' || NVL(l_supp_totalweeks,0) ||'" ');
4103 END IF;
4104 -- PRSIClass for main P45
4105 IF cur_p45_ie_emp_details_rec.prsi_class IS NOT NULL OR
4106 ( NVL(cur_p45_paye_prsi_rec.totalaweeks,0) <> 0 and p45_rec.supp_flag <> 'Y' ) OR
4107 ( NVL(l_supp_classA_weeks,0) <> 0 and p45_rec.supp_flag <> 'N' ) THEN
4108 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'>');
4109 IF (NVL(cur_p45_paye_prsi_rec.totalaweeks,0) <> 0) and p45_rec.supp_flag <> 'Y' THEN -- Bug 5015438
4110 FND_FILE.PUT(FND_FILE.OUTPUT,' <PRSIClass ');
4111 FND_FILE.PUT(FND_FILE.OUTPUT,'class="' || 'A' ||'" ');
4112 FND_FILE.PUT(FND_FILE.OUTPUT,'weeks="' || cur_p45_paye_prsi_rec.totalaweeks ||'" ');
4113 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'/>');
4114 END IF;
4115 IF (NVL(l_supp_classA_weeks,0) <> 0 ) and p45_rec.supp_flag <> 'N' THEN
4116 FND_FILE.PUT(FND_FILE.OUTPUT,' <PRSIClass ');
4117 FND_FILE.PUT(FND_FILE.OUTPUT,'class="' || 'A' ||'" ');
4118 FND_FILE.PUT(FND_FILE.OUTPUT,'weeks="' || l_supp_classA_weeks ||'" ');
4119 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'/>');
4120 END IF;
4121 vfrom:=1;
4122 vto:=length(cur_p45_ie_emp_details_rec.prsi_class);
4123 LOOP
4124 vfound:= instr(cur_p45_ie_emp_details_rec.prsi_class,',',vfrom,1);
4125 IF (vfound > 0 ) THEN
4126 vto:=vfound-vfrom;
4127 v_prsi_class:= substr(cur_p45_ie_emp_details_rec.prsi_class,vfrom,vto);
4128 FND_FILE.PUT(FND_FILE.OUTPUT,' <PRSIClass ');
4129 FND_FILE.PUT(FND_FILE.OUTPUT,'class="' || v_prsi_class ||'" ');
4130 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'/>');
4131 vfrom:=vfound+1;
4132 ELSE
4133 v_prsi_class:= substr(cur_p45_ie_emp_details_rec.prsi_class,vfrom);
4134 IF v_prsi_class IS NOT NULL THEN
4135 FND_FILE.PUT(FND_FILE.OUTPUT,' <PRSIClass ');
4136 FND_FILE.PUT(FND_FILE.OUTPUT,'class="' || v_prsi_class ||'" ');
4137 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'/>');
4138 END IF;
4139 EXIT;
4140 END IF;
4141 END LOOP;
4142 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </PRSI>');
4143 ELSE
4144 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'/>');
4145 END IF;
4146 IF p45_rec.supp_flag = 'N' THEN
4147 -- Disability
4148 FND_FILE.PUT(FND_FILE.OUTPUT,' <Disability ');
4149 FND_FILE.PUT(FND_FILE.OUTPUT,'benefit="' || nvl(cur_p45_emp_soc_details_rec.benefit,0) ||'" ');
4150 IF cur_p45_emp_soc_details_rec.taxcreditreduction IS NOT NULL THEN -- Optional
4151 FND_FILE.PUT(FND_FILE.OUTPUT,'taxcreditreduction="' || cur_p45_emp_soc_details_rec.taxcreditreduction ||'" ');
4152 END IF;
4153 IF cur_p45_emp_soc_details_rec.cutoffreduction IS NOT NULL THEN -- Optional
4154 FND_FILE.PUT(FND_FILE.OUTPUT,'cutoffreduction="' || cur_p45_emp_soc_details_rec.cutoffreduction ||'" ');
4155 END IF;
4156 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
4157 -- Deceased
4158
4159 FND_FILE.PUT(FND_FILE.OUTPUT,' <Basis ');
4160 IF cur_p45_emp_soc_details_rec.noncumulative IS NOT NULL THEN -- Optional
4161 FND_FILE.PUT(FND_FILE.OUTPUT,'noncumulative="' || cur_p45_emp_soc_details_rec.noncumulative ||'" ');
4162 END IF;
4163 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
4164
4165 IF cur_p45_emp_details_rec.deceased = 'Y' THEN
4166 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' <Deceased/> ');
4167 END IF;
4168 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </P45> ');
4169 ELSE
4170 -- FND_FILE.PUT(FND_FILE.OUTPUT,' <Disability ');
4171 -- FND_FILE.PUT(FND_FILE.OUTPUT,'benefit="' || p45_rec.benefit ||'" ');
4172 -- FND_FILE.PUT(FND_FILE.OUTPUT,'taxcreditreduction="' || p45_rec.taxcreditreduction ||'" ');
4173 -- FND_FILE.PUT(FND_FILE.OUTPUT,'cutoffreduction="' || p45_rec.cutoffreduction ||'" ');
4174 -- FND_FILE.PUT(FND_FILE.OUTPUT,'noncumulative="' || p45_rec.noncumulative ||'" ');
4175 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' />');
4176 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' </P45Supp> ');
4177 END IF;
4178 END IF;
4179 END LOOP;
4180 IF once_per_run = 'Y' THEN
4181 -- End of ROOT P45File ELEMENT
4182 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_root_end_tag);
4183 END IF;
4184 IF warn_status =1 then
4185 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS
4186 (
4187 status => 'WARNING',
4188 message => 'PPSN and Address missing. Please check the log file for more details.'
4189 );
4190
4191 END IF;
4192
4193 END generate_xml;
4194
4195 END pay_ie_p45_archive;