[Home] [Help]
PACKAGE: APPS.PAY_GB_MOVDED_EDI
Source
1 PACKAGE PAY_GB_MOVDED_EDI AUTHID CURRENT_USER as
2 /* $Header: pygbmedi.pkh 120.34.12020000.1 2012/06/28 17:54:16 appldev ship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================
9 Name
10 PAY_GB_MOVDED_EDI
11 Purpose
12 Package to contol archiver process in the creation of assignment actions
13 and then the creation of EDI message files using the magtape process for
14 EDI Message Types : P45(3), P46, P46PENNOT
15 Notes
16
17 History
18 10-OCT-2000 S.Robinson 115.0 Date created.
19 18-JUN-2001 S.Robinson 115.1 Passing Char_Errors as 'N'
20 19-JUN-2001 S.Robinson 115.2 Enforce Character validation for
21 P45(3), P46 and P46 Pen processes.
22 26-JUN-2001 S.Robinson 115.3 Ensure numeric values nvl is passed
23 as 0 - Bug 1851781.
24 20-JUL-2001 S.Robinson 115.4 Removed carriage return from Test
25 submission parameter in P45_3
26 header.
27 05-MAR-2002 G.Butler 115.5 Altered cursors for UTF8 project.
28 Added dbdrv lines
29 10-JUN-2002 R.Makhija 115.6 Changed cursors to get tax details
30 from PAYE Details element as
31 effective of last Run
32 08-JUL-2002 R.Makhija 115.7 Changed cursors to look for PAYE
33 element run results for tax
34 details first
35 06-NOV-2002 BTHAMMIN 115.9 Bug 2657976
36 Cursors csr_p45_3_assignments
37 and csr_p46_assignments are
38 changed. job.name is changet to
39 display only the selected segment
40 in Organization Developer DF.
41 09-DEC-2002 BTHAMMIN 115.10 Check for enabled and displayed
42 segments.
43 23-DEC-2002 NSUGAVAN 115.11 To be R8.0 compliant, commented out
44 function get_job_segment as it has been
45 moved to a different file(pygbjseg.pkh)
46 Replaced calls to this function in
47 cursors.
48 03-jan-2003 NSUGAVAN 115.12 Modified cursor calls w.r.t change in
49 get_job_segment Function change
50 08-Jul-2003 AMILLS 115.13 Bug 3038685. Performance tune of
51 assignments cursors.
52 16-DEC-2003 ASENGAR 115.16 BUG 3221422. Removed merge(cartesian) joins
53 for assignment cursors.
54 28-APR-2004 ASENGAR 115.17 BUG 3550468 Changed cursor csr_p46_assignments
55 using nvl for the case when job is null.
56 13-MAY-2004 KTHAMPAN 115.18 BUG 3609354. Use nvl(xx,'NONE') for NI field
57 in cursor csr_p45_3_assignments,
58 csr_p46_assignments and csr_p46_pennot_assignments
59 04-OCT-2004 AMILLS 115.19 BUG 3850012. Added Ordered Index and Use NL
60 Hint to csr_p46_assignments.
61 05-OCT-2004 AMILLS 115.20 3850012. Also hinted csr_p45_3_assignments,
62 csr_p46_pennot_assignments.
63 13-OCT-2004 ALIKHAR 115.21 Bug 3891351. Changed the cursors
64 csr_p45_3_assignments, csr_p46_assignments,
65 csr_p46_pennot_assignments to join
66 assignment table with period of service
67 table based on period_of_service_id column.
68 06-JAN-2005 TUKUMAR 115.22 Bugs 4086317,4086331,4086142 : Changed the length
69 of display of Employer's PAYE Reference
70 13-FEB-2006 TUKUMAR 115.23 Bug 5006451 : P46 Students loan Enhancement.
71 Modified cursor csr_p46_assignments
72 16-JUN-2006 KTHAMPAN 115.24 Code change for EDI Rollback.
73 19-JUN-2006 KTHAMPAN 115.25 Bug 5169434. Substr title to 4 chars for
74 P46 and P45(3).
75 27-JUL-2006 TUKUMAR 115.26 Inlcuded WNU 3.0 cursors: 5398360
76 19-DEC-2006 KTHAMPAN 115.27 Fix bug 5719330
77 18-JAN-2007 JVARADRA 115.29 Fix bug 5766232, changed the TAX_REF value to uppercase
78 30-Oct-2007 ABHGANGU 115.31 6345375 Added cursors
79 csr_p45pt_3_header
80 ,csr_p45pt_3_assignments
81 ,csr_p46_5_pennot_header
82 ,csr_p46_5_pennot_assignments
83 19-Nov-2007 ABHGANGU 115.32 6345375 Added cursors csr_p46_5_header
84 csr_p46_5_assignments
85 19-Nov-2007 PARUSIA 115.33 6345375 Changed cursor csr_p45pt_3_assignments
86 to fetch continue_student_loan_deductions
87 for P45PT3
88 28-Nov-2007 PARUSIA 115.34 6345375 Hardcoded value of URGENT_MARKER in
89 csr_p45pt_3_header as ' '
90 30-Nov-2007 PARUSIA 115.35 6345375 Set default value for NATIONAL_INSURANCE_NUMBER
91 in csr_p45pt_3_assignments as ' '
92 27-DEC-2007 rlingama 115.37 6710197 Modified action_information9 to action_information13
93 in csr_p45pt_3_assignments,csr_p46_5_pennot_assignments,
94 csr_p46_5_assignments cursors.
95 4-Jan-2007 PARUSIA 115.38 6710229 Selected middle_name also from
96 csr_p45pt_3_assignments,
97 csr_p46_5_pennot_assignments,
98 csr_p46_5_assignments.
99 30-Apr-2008 rlingama 115.39 6994632 P45(3) minor enhancements for UK EOY Changes APR08
100 29-May-2008 rlingama 115.40 7038073 The PAY_PREVIOUS and TAX_PREVIOUS fields are numeric
101 hence assigning 0 if the value is NULL
102 06-Jun-2008 rlingama 115.41 7038073 Added NVL for pennot address line 2 and 3.
103 05-Jul-2008 rlingama 115.42 7157720 Report PREVIOUS_TAX_PAID_NOTIFIED if >=0 in P45PT3
104 17-Oct-2008 dwkrishn 115.43 7433580 Added Cursors for 2009 legislative changes.
105 20-Oct-2008 dwkrishn 115.44 7433580 Incorporated Review Comments.
106 30-Oct-2008 dwkrishn 115.45 7433580 Added P45PT3 Changes.
107 03-Nov-2008 dwkrishn 115.46 7433580 Added Pennot Changes.
108 03-Nov-2008 dwkrishn 115.47 7433580 Changed Pennot csr_p46_ver6_pennot_assg cursor
109 10-Nov-2008 namgoyal 115.48 7540858 Added first_name validation to truncate the name
110 after space in cursors for P45PT3, P46 and P46 Pennot
111 EDI's version 5 and 6. Also commented the new procedures
112 introduced for In Year filling for 2008-2009.
113 10-Nov-2008 namgoyal 115.49 Uncommented the new procedures
114 introduced for In Year filling for 2008-2009.
115 11-NOV-2008 dwkrishn 115.50 7433580 Added Function edi_errors_log,And other edi_errors Types
116 to incorporate Success Failure sections in O/P File
117 20-Nov-2008 namgoyal 115.51 7540858 Removed the changes done in version 48/49
118 27-Jan-2009 dwkrishn 115.52 7830717 Total Pay,Total Tax was defaulted to 0 causing issues in
119 Null comparison in formula.Added decode to make user entered 0
120 also to be space.
121 05-Feb-2009 krreddy 115.53 8216080 Added 2 cursors and a procedure P46EXP_VER6_ACTION_CREATION
122 to implement P46Expat Notification
123 15-Jul-2009 dwkrishn 115.54 8640608 Added hints to cursor csr_p45pt_3_ver6_assignments,csr_p46_ver6_assignments .
124 09-Sep-2009 dwkrishn 115.55 8329474 Sender EDI is made case insensitive for P45PT3 and P46 Pennot.
125 22-Jan-2010 namgoyal 115.56 9255173,9255183 Updated for P46 V6 and P46 Expat eText reports
126 ============================================================================*/
127 --
128 --
129 --
130 -- Function to fetch country name for the given country code
131 --
132 function get_territory_short_name(prm_name varchar2)
133 return varchar2;
134
135 cursor csr_p45_3_header is
136 select 'SENDER_ID=P', nvl(hoi.org_information11,' '),
137 'RECEIVER_ID=P', 'INLAND REVENUE',
138 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
139 'URGENT_MARKER=P', decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
140 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
141 'FORM_TYPE=P', '5',
142 'FORM_TYPE_MEANING=P', 'P45_3',
143 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
144 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
145 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
146 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
147 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' ')
148 from pay_payroll_actions pact,
149 hr_organization_information hoi
150 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
151 and pact.business_group_id = hoi.organization_id
152 and hoi.org_information_context = 'Tax Details References'
153 and nvl(hoi.org_information10,'UK') = 'UK'
154 and substr(pact.legislative_parameters, instr(pact.legislative_parameters, 'TAX_REF=') + 8,
155 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
156 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
157 --
158 --
159 cursor csr_p45_3_assignments is
160 select 'ASSIGNMENT_ACTION_ID=C',paa.assignment_action_id,
161 'CHARS_ALREADY_TESTED=P','N',
162 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
163 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
164 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
165 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
166 'SEX=P', nvl(peo.action_information17,' '),
167 'PREV_TAX_REFERENCE=P', nvl(upper(substr(ltrim(substr(p453.action_information2,4,11),'/'),1,10)),' '),
168 'PREV_TAX_DISTRICT=P', nvl(substr(p453.action_information2,1,3),' '),
169 'DATE_OF_BIRTH=P', nvl(peo.action_information15,' '),
170 'HIRE_DATE=P', nvl(peo.action_information16,' '),
171 'DATE_LEFT_PREV_EMP=P', nvl(p453.action_information3,' '),
172 'PREV_TAX_CODE=P', nvl(p453.action_information4,' '),
173 'PREV_TAX_BASIS=P', nvl(p453.action_information5,' '),
174 'PREV_LAST_PAY_TYPE=P', nvl(p453.action_information6,' '),
175 'PREV_LAST_PAY_PERIOD=P',nvl(p453.action_information7,' '),
176 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
177 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
178 'PAY_PREVIOUS=P', nvl(p453.action_information8,'0'),
179 'TAX_PREVIOUS=P', nvl(p453.action_information9,'0'),
180 'JOB_TITLE=P', peo.action_information18,
181 'COUNTY=P', nvl(upper(addr.action_information9),' '),
182 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
183 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
184 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,'NONE'),
185 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
186 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
187 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
188 'STUDENT_LOAN_FLAG_START_DATE=P', nvl(p453.action_information10,'X'),
189 'STUDENT_LOAN_FLAG_END_DATE=P', nvl(p453.action_information11,'4712/12/31 00:00:00'),
190 'STUDENT_LOAN_FLAG_EFFECTIVE_END_DATE=P', nvl(p453.action_information12,'4712/12/31 00:00:00') ,
191 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date)
192 from pay_payroll_actions pay,
193 pay_assignment_actions paa,
194 pay_action_information addr,
195 pay_action_information peo,
196 pay_action_information p453
197 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
198 and pay.payroll_action_id = paa.payroll_action_id
199 and paa.assignment_action_id = peo.action_context_id
200 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
201 and peo.action_context_type = 'AAP'
202 and paa.assignment_action_id = addr.action_context_id
203 and addr.action_information_category = 'ADDRESS DETAILS'
204 and addr.action_context_type = 'AAP'
205 and paa.assignment_action_id = p453.action_context_id
206 and p453.action_information_category = 'GB P45(3) EDI'
207 and p453.action_context_type = 'AAP';
208 --
209 --
210 cursor csr_p46_header is
211 select 'SENDER_ID=P', nvl(hoi.org_information11,' '),
212 'RECEIVER_ID=P', 'INLAND REVENUE',
213 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
214 'URGENT_MARKER=P', decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
215 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
216 'FORM_TYPE=P', '7',
217 'FORM_TYPE_MEANING=P', 'P46',
218 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
219 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
220 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
221 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
222 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' ')
223 from pay_payroll_actions pact,
224 hr_organization_information hoi
225 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
226 and pact.business_group_id = hoi.organization_id
227 and hoi.org_information_context = 'Tax Details References'
228 and nvl(hoi.org_information10,'UK') = 'UK'
229 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
230 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
231 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
232
233 cursor csr_p46_assignments is
234 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
235 'CHARS_ALREADY_TESTED=P', 'N',
236 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
237 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
241 'P46_STATEMENT=P', nvl(p46.action_information2,'N'),
238 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
239 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
240 'ASSIGNMENT_ID=P', paa.assignment_id,
242 'P46_STATEMENT_STUDENT_LOAN=P', nvl(p46.action_information3,'N'),
243 'DATE_OF_BIRTH=P', peo.action_information15,
244 'HIRE_DATE=P', peo.action_information16,
245 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
246 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
247 'COUNTY=P', nvl(upper(addr.action_information9),' '),
248 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
249 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
250 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,'NONE'),
251 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
252 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
253 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
254 'SEX=P', nvl(peo.action_information17,' '),
255 'JOB_TITLE=P', nvl(peo.action_information18,' ')
256 from pay_payroll_actions pay,
257 pay_assignment_actions paa,
258 pay_action_information addr,
259 pay_action_information peo,
260 pay_action_information p46
261 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
262 and pay.payroll_action_id = paa.payroll_action_id
263 and paa.assignment_action_id = peo.action_context_id
264 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
265 and peo.action_context_type = 'AAP'
266 and paa.assignment_action_id = addr.action_context_id
267 and addr.action_information_category = 'ADDRESS DETAILS'
268 and addr.action_context_type = 'AAP'
269 and paa.assignment_action_id = p46.action_context_id
270 and p46.action_information_category = 'GB P46 EDI'
271 and p46.action_context_type = 'AAP';
272 --
273 --
274 cursor csr_p46_pennot_header is
275 select 'SENDER_ID=P', hoi.org_information11,
276 'RECEIVER_ID=P', 'INLAND REVENUE',
277 'TEST_INDICATOR=P',decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
278 'URGENT_MARKER=P', decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
279 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
280 'FORM_TYPE=P', '6',
281 'FORM_TYPE_MEANING=P', 'P46_PENNOT',
282 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
283 'TAX_DIST_REF=P', upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),
284 'TAX_DISTRICT=P', upper(hoi.org_information2),
285 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
286 'EMPLOYERS_NAME=P',nvl(upper(hoi.org_information3),' ')
287 from pay_payroll_actions pact,
288 hr_organization_information hoi
289 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
290 and pact.business_group_id = hoi.organization_id
291 and hoi.org_information_context = 'Tax Details References'
292 and nvl(hoi.org_information10,'UK') = 'UK'
293 and substr(pact.legislative_parameters, instr(pact.legislative_parameters,'TAX_REF=') + 8,
294 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
295 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
296 --
297
298 --
299 cursor csr_p46_pennot_assignments is
300 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
301 'CHARS_ALREADY_TESTED=P', 'N',
302 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
303 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
304 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
305 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
306 'SEX=P', nvl(peo.action_information17,' '),
307 'ANNUAL_PENSION=P', nvl(p46p.action_information2,'X'),
308 'DATE_PENSION_STARTED=P', nvl(p46p.action_information3,'0001/01/01 00:00:00'),
309 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
310 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
311 'JOB_TITLE=P', nvl(peo.action_information18,' '),
312 'COUNTY=P', nvl(upper(addr.action_information9),' '),
313 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
314 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
315 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,'NONE'),
316 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
317 'TITLE=P', nvl(peo.action_information14,' '),
318 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' ')
319 from pay_payroll_actions pay,
320 pay_assignment_actions paa,
321 pay_action_information addr,
322 pay_action_information peo,
323 pay_action_information p46p
324 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
325 and pay.payroll_action_id = paa.payroll_action_id
326 and paa.assignment_action_id = peo.action_context_id
327 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
328 and peo.action_context_type = 'AAP'
329 and paa.assignment_action_id = addr.action_context_id
330 and addr.action_information_category = 'ADDRESS DETAILS'
331 and addr.action_context_type = 'AAP'
332 and paa.assignment_action_id = p46p.action_context_id
333 and p46p.action_information_category = 'GB P46 Pension EDI'
334 and p46p.action_context_type = 'AAP';
335 --
336 --
337 -- Bug 5398360
341 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
338 cursor csr_wnu3_header is
339 select 'SENDER_ID=P', nvl(hoi.org_information11,' '),
340 'RECEIVER_ID=P', 'INLAND REVENUE',
342 'URGENT_MARKER=P', ' ', -- as a space is required in this place
343 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
344 'FORM_TYPE=P', '18',
345 'FORM_TYPE_MEANING=P', 'WNU',
346 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
347 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
348 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
349 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
350 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' ')
351 from pay_payroll_actions pact,
352 hr_organization_information hoi
353 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
354 and pact.business_group_id = hoi.organization_id
355 and hoi.org_information_context = 'Tax Details References'
356 and nvl(hoi.org_information10,'UK') = 'UK'
357 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
358 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
359 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
360 --
361 --
362 cursor csr_wnu3_assignments is
363 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
364 'ADDRESS_LINE1=P', ' ', -- wnu3 does not require address
365 'ADDRESS_LINE2=P', ' ',
366 'ADDRESS_LINE3=P', ' ',
367 'COUNTY=P', ' ',
368 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
369 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
370 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,'NONE'),
371 'POSTAL_CODE=P', ' ',
372 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
373 'TOWN_OR_CITY=P', ' ',
374 'DATE_OF_BIRTH=P', peo.action_information15,
375 'HIRE_DATE=P', peo.action_information16,
376 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
377 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
378 'CHARS_ALREADY_TESTED=P', 'N',
379 'ASSIGNMENT_ID=P', paa.assignment_id,
380 'ASSIGNMENT_NUMBER=P', nvl(upper(peo.action_information11), ' '), -- for MOVDED_ASG
381 'NEW_ASSIGNMENT_NUMBER=P',nvl(upper(peo.action_information11), ' ') , -- for PAY_GB_EDI_WNU_3
382 'OLD_ASSIGNMENT_NUMBER=P',nvl(upper(wnu3.action_information2), ' ') -- for PAY_GB_EDI_WNU_3
383 from pay_assignment_actions paa,
384 pay_action_information peo,
385 pay_action_information wnu3
386 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
387 and paa.assignment_action_id = peo.action_context_id
388 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
389 and peo.action_context_type = 'AAP'
390 and paa.assignment_action_id = wnu3.action_context_id(+)
391 and wnu3.action_information_category(+) = 'GB WNU EDI'
392 and wnu3.action_context_type(+)= 'AAP';
393
394 --
395 /* changes for P45PT_3 start*/
396 cursor csr_p45pt_3_header is
397 select 'SENDER_ID=P', nvl(hoi.org_information11,' '),
398 'RECEIVER_ID=P', 'HMRC', /* changed for P45PT_3*/
399 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
400 'URGENT_MARKER=P', ' ',
401 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
402 'FORM_TYPE=P', '5',
403 'FORM_TYPE_MEANING=P', 'P45PT3', /* changed for P45PT_3*/
404 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
405 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
406 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
407 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
408 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' '),
409 'TEST_ID=P', nvl(pay_magtape_generic.get_parameter_value('TEST_ID'),' ') /*added for P45PT_3*/
410 from pay_payroll_actions pact,
411 hr_organization_information hoi
412 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
413 and pact.business_group_id = hoi.organization_id
414 and hoi.org_information_context = 'Tax Details References'
415 and nvl(hoi.org_information10,'UK') = 'UK'
416 and substr(pact.legislative_parameters, instr(pact.legislative_parameters, 'TAX_REF=') + 8,
417 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
418 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
419 --
420 --
421 cursor csr_p45pt_3_assignments is
422 select 'ASSIGNMENT_ACTION_ID=C',paa.assignment_action_id,
423 'CHARS_ALREADY_TESTED=P','N',
424 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
425 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
426 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
427 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
428 'SEX=P', nvl(peo.action_information17,' '),
429 'PREV_TAX_REFERENCE=P', nvl(upper(substr(ltrim(substr(p453.action_information2,4,11),'/'),1,10)),' '),
430 'PREV_TAX_DISTRICT=P', nvl(substr(p453.action_information2,1,3),' '),
431 'DATE_OF_BIRTH=P', peo.action_information15,
432 'HIRE_DATE=P', peo.action_information16,
433 'DATE_LEFT_PREV_EMP=P', nvl(p453.action_information3,'0001/01/01 00:00:00'),
437 'PREV_LAST_PAY_PERIOD=P',nvl(p453.action_information7,' '),
434 'PREV_TAX_CODE=P', nvl(p453.action_information4,' '),
435 'PREV_TAX_BASIS=P', nvl(p453.action_information5,' '),
436 'PREV_LAST_PAY_TYPE=P', nvl(p453.action_information6,' '),
438 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
439 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
440 -- Bug 6994632 We need to report values form Newstarter tab instead of PAYE tab values
441 /*'PAY_PREVIOUS=P', nvl(p453.action_information8,' '),*/
442 -- Bug 7038073 These fields are numeric hence assigning 0.00 if the value is NULL
443 /*'PAY_PREVIOUS=P', nvl(decode(p453.action_information16,0,NULL,p453.action_information16),nvl(p453.action_information8,' ')),
444 'TAX_PREVIOUS=P', nvl(p453.action_information9,' '),*/
445 'PAY_PREVIOUS=P', nvl(decode(p453.action_information16,0,NULL,p453.action_information16),nvl(p453.action_information8,'0')),
446 'TAX_PREVIOUS=P', nvl(p453.action_information9,'0'),
447 'JOB_TITLE=P', peo.action_information18,
448 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
449 --For bug 7540858 : P45PT3 version5
450 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
451 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
452 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
453 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,' '),
454 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
455 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
456 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
457 'STUDENT_LOAN_FLAG_START_DATE=P', nvl(p453.action_information10,'X'),
458 'STUDENT_LOAN_FLAG_END_DATE=P', nvl(p453.action_information11,'4712/12/31 00:00:00'),
459 'STUDENT_LOAN_FLAG_EFFECTIVE_END_DATE=P', nvl(p453.action_information12,'4712/12/31 00:00:00') ,
460 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date),
461 --Bug 7157720 report PREVIOUS_TAX_PAID_NOTIFIED if >=0
462 -- Bug 6994632 assigning NULL if it is zero
463 'PREVIOUS_TAX_PAID_NOTIFIED=P', nvl(p453.action_information13,' '),
464 --'PREVIOUS_TAX_PAID_NOTIFIED=P', nvl(decode(p453.action_information13,0,' ',p453.action_information13),' '),
465 'NOT_PAID_BETWEEN_START_AND5APR=P', decode(p453.action_information14,'Y','Y','N',' ',' '),
466 'CONTINUE_SL_DEDUCTIONS=P', decode(p453.action_information15,'Y','Y','N',' ',' ')
467 from pay_payroll_actions pay,
468 pay_assignment_actions paa,
469 pay_action_information addr,
470 pay_action_information peo,
471 pay_action_information p453
472 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
473 and pay.payroll_action_id = paa.payroll_action_id
474 and paa.assignment_action_id = peo.action_context_id
475 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
476 and peo.action_context_type = 'AAP'
477 and paa.assignment_action_id = addr.action_context_id
478 and addr.action_information_category = 'ADDRESS DETAILS'
479 and addr.action_context_type = 'AAP'
480 and paa.assignment_action_id = p453.action_context_id
481 and p453.action_information_category = 'GB P45(3) EDI'
482 and p453.action_context_type = 'AAP';
483 /* changes for P45PT_3 end*/
484
485 /*Changes for P45PT_3 Version 6*/
486
487 cursor csr_p45pt_3_ver6_header is
488 select 'SENDER_ID=P', nvl(UPPER(hoi.org_information11),' '), -- Bug 8329474
489 'RECEIVER_ID=P', 'HMRC', /* changed for P45PT_3*/
490 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
491 'URGENT_MARKER=P', ' ',
492 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
493 'FORM_TYPE=P', '5',
494 'FORM_TYPE_MEANING=P', 'P45PT_3_VER6', /* changed for P45PT_3*/
495 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
496 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
497 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
498 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
499 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' '),
500 'TEST_ID=P', nvl(pay_magtape_generic.get_parameter_value('TEST_ID'),' ') /*added for P45PT_3*/
501 from pay_payroll_actions pact,
502 hr_organization_information hoi
503 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
504 and pact.business_group_id = hoi.organization_id
505 and hoi.org_information_context = 'Tax Details References'
506 and nvl(hoi.org_information10,'UK') = 'UK'
507 and substr(pact.legislative_parameters, instr(pact.legislative_parameters, 'TAX_REF=') + 8,
508 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
509 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
510 ---
511 cursor csr_p45pt_3_ver6_assignments is
512 select /*+ ordered*/ 'ASSIGNMENT_ACTION_ID=C',paa.assignment_action_id,
513 'CHARS_ALREADY_TESTED=P','N',
514 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
515 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
516 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
517 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
518 'SEX=P', nvl(peo.action_information17,' '),
519 'PREV_TAX_REFERENCE=P', nvl(upper(substr(ltrim(substr(p453.action_information2,4,11),'/'),1,10)),' '),
520 'PREV_TAX_DISTRICT=P', nvl(substr(p453.action_information2,1,3),' '),
524 'PREV_TAX_CODE=P', nvl(p453.action_information4,' '),
521 'DATE_OF_BIRTH=P', nvl(peo.action_information15,' '),
522 'HIRE_DATE=P', peo.action_information16,
523 'DATE_LEFT_PREV_EMP=P', nvl(p453.action_information3,'0001/01/01 00:00:00'),
525 'PREV_TAX_BASIS=P', nvl(p453.action_information5,' '),
526 'PREV_LAST_PAY_TYPE=P', nvl(p453.action_information6,' '),
527 'PREV_LAST_PAY_PERIOD=P',nvl(p453.action_information7,' '),
528 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
529 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
530 -- Bug 6994632 We need to report values form Newstarter tab instead of PAYE tab values
531 /*'PAY_PREVIOUS=P', nvl(p453.action_information8,' '),*/
532 -- Bug 7038073 These fields are numeric hence assigning 0.00 if the value is NULL
533 /*'PAY_PREVIOUS=P', nvl(decode(p453.action_information16,0,NULL,p453.action_information16),nvl(p453.action_information8,' ')),
534 'TAX_PREVIOUS=P', nvl(p453.action_information9,' '),*/
535 'PAY_PREVIOUS=P', nvl(decode(p453.action_information16,0,NULL,p453.action_information16),nvl(p453.action_information8,'0')),
536 'TAX_PREVIOUS=P', nvl(p453.action_information9,'0'),
537 'JOB_TITLE=P', peo.action_information18,
538 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
539 --For bug 7540858 : P45PT3 version 6
540 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
541 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
542 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
543 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,' '),
544 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
545 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
546 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
547 'STUDENT_LOAN_FLAG_START_DATE=P', nvl(p453.action_information10,'X'),
548 'STUDENT_LOAN_FLAG_END_DATE=P', nvl(p453.action_information11,'4712/12/31 00:00:00'),
549 'STUDENT_LOAN_FLAG_EFFECTIVE_END_DATE=P', nvl(p453.action_information12,'4712/12/31 00:00:00') ,
550 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date),
551 --Bug 7157720 report PREVIOUS_TAX_PAID_NOTIFIED if >=0
552 -- Bug 6994632 assigning NULL if it is zero
553 'PREVIOUS_TAX_PAID_NOTIFIED=P', nvl(p453.action_information13,' '),
554 --'PREVIOUS_TAX_PAID_NOTIFIED=P', nvl(decode(p453.action_information13,0,' ',p453.action_information13),' '),
555 'NOT_PAID_BETWEEN_START_AND5APR=P', decode(p453.action_information14,'Y','Y','N',' ',' '),
556 'CONTINUE_SL_DEDUCTIONS=P', decode(p453.action_information15,'Y','Y','N',' ',' ')
557 from pay_payroll_actions pay,
558 pay_assignment_actions paa,
559 pay_action_information addr,
560 pay_action_information peo,
561 pay_action_information p453
562 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
563 and pay.payroll_action_id = paa.payroll_action_id
564 and paa.assignment_action_id = peo.action_context_id
565 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
566 and peo.action_context_type = 'AAP'
567 and paa.assignment_action_id = addr.action_context_id
568 and addr.action_information_category = 'ADDRESS DETAILS'
569 and addr.action_context_type = 'AAP'
570 and paa.assignment_action_id = p453.action_context_id
571 and p453.action_information_category = 'GB P45(3) EDI'
572 and p453.action_context_type = 'AAP';
573 ----
574 /*Changes for P45PT_3 Version 6*/
575
576 /***** Year end changes for P46 PENNOT ********/
577 /******* start *******/
578 cursor csr_p46_5_pennot_header is
579 select 'SENDER_ID=P', hoi.org_information11,
580 'RECEIVER_ID=P', 'HMRC',
581 'TEST_INDICATOR=P',decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
582 'TEST_ID=P', pay_magtape_generic.get_parameter_value('TEST_ID'),
583 'URGENT_MARKER=P', ' ',
584 'TRANSMISSION_DATE=P', to_char(sysdate,'CCYYMMDD'),
585 'TRANSMISSION_TIME=P', to_char(sysdate,'HHMMSS'),
586 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
587 'FORM_TYPE=P', '6',
588 'FORM_TYPE_MEANING=P', 'P46_5_PENNOT',
589 'UNIQUE_REF=P', '1234',
590 'SENDER_SUBAD=P',' ',
591 'RECIPIENT_SUBAD=P',' ',
592 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
593 'TAX_DIST_REF=P', upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),
594 'TAX_DISTRICT=P', upper(hoi.org_information2),
595 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
596 'EMPLOYERS_NAME=P',nvl(upper(hoi.org_information3),' ')
597 from pay_payroll_actions pact,
598 hr_organization_information hoi
599 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
600 and pact.business_group_id = hoi.organization_id
601 and hoi.org_information_context = 'Tax Details References'
602 and nvl(hoi.org_information10,'UK') = 'UK'
603 and substr(pact.legislative_parameters, instr(pact.legislative_parameters,'TAX_REF=') + 8,
604 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
605 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
606 --
607 --
608 cursor csr_p46_5_pennot_assignments is
609 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
610 'CHARS_ALREADY_TESTED=P', 'N',
611 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
612 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '), -- Bug 7038073 Added nvl
616 'SEX=P', nvl(peo.action_information17,' '),
613 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '), -- Bug 7038073 Added nvl
614 'ADDRESS_LINE4=P', ' ',
615 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
617 'ANNUAL_PENSION=P', p46p.action_information2,
618 'DATE_PENSION_STARTED=P', nvl(p46p.action_information3,'0001/01/01 00:00:00'),
619 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
620 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
621 'JOB_TITLE=P', nvl(peo.action_information18,' '),
622 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
623 --For bug 7540858 : P46 Pennot version 5
624 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
625 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
626 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
627 'NATIONAL_INSURANCE_NUMBER=P', peo.action_information12,
628 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
629 'TITLE=P', nvl(peo.action_information14,' '),
630 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
631 'MID_NAME=P', upper(substr(peo.action_information7,1,35)),
632 'RECENT_BEREAVED=P', p46p.action_information10,
633 'PREV_EMP_REF=P', upper(substr(ltrim(substr(p46p.action_information4,4,11),'/'),1,10)),
634 'PREV_HMRC_NO=P', substr(p46p.action_information4,1,3),
635 -- Bug 7038073 These fields are numeric hence assigning 0.00 if the value is NULL
636 /*'TOTAL_PAY=P', nvl(p46p.action_information11,' '),
637 'TOTAL_TAX=P', nvl(p46p.action_information12,' '),*/
638 'TOTAL_PAY=P', nvl(p46p.action_information11,'0'),
639 'TOTAL_TAX=P', nvl(p46p.action_information12,'0'),
640 'DATE_LEFT_PREV_EMP=P', nvl(p46p.action_information5,'0001/01/01 00:00:00'),
641 'DATE_OF_BIRTH=P', nvl(peo.action_information15,'0001/01/01 00:00:00'),
642 'TAX_CODE_LEAVING=P', nvl(p46p.action_information6,' '),
643 'TAX_BASIS_PREV=P', nvl(p46p.action_information7,' '),
644 'PREV_PAY_TYPE=P', nvl(p46p.action_information8,' '),
645 'PREV_PAY_PERIOD=P', nvl(p46p.action_information9,' '),
646 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date)
647 from pay_payroll_actions pay,
648 pay_assignment_actions paa,
649 pay_action_information addr,
650 pay_action_information peo,
651 pay_action_information p46p
652 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
653 and pay.payroll_action_id = paa.payroll_action_id
654 and paa.assignment_action_id = peo.action_context_id
655 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
656 and peo.action_context_type = 'AAP'
657 and paa.assignment_action_id = addr.action_context_id
658 and addr.action_information_category = 'ADDRESS DETAILS'
659 and addr.action_context_type = 'AAP'
660 and paa.assignment_action_id = p46p.action_context_id
661 and p46p.action_information_category = 'GB P46 PENNOT EDI'
662 and p46p.action_context_type = 'AAP';
663
664
665 /***** Year end changes for P46 PENNOT Version 6 Start********/
666 cursor csr_p46_ver6_pennot_header is
667 select 'SENDER_ID=P', UPPER(hoi.org_information11),
668 'RECEIVER_ID=P', 'HMRC',
669 'TEST_INDICATOR=P',decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
670 'TEST_ID=P', pay_magtape_generic.get_parameter_value('TEST_ID'),
671 'URGENT_MARKER=P', ' ',
672 'TRANSMISSION_DATE=P', to_char(sysdate,'CCYYMMDD'),
673 'TRANSMISSION_TIME=P', to_char(sysdate,'HHMMSS'),
674 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
675 'FORM_TYPE=P', '6',
676 'FORM_TYPE_MEANING=P', 'P46_VER6_PENNOT',
677 'UNIQUE_REF=P', '1234',
678 'SENDER_SUBAD=P',' ',
679 'RECIPIENT_SUBAD=P',' ',
680 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
681 'TAX_DIST_REF=P', upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),
682 'TAX_DISTRICT=P', upper(hoi.org_information2),
683 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
684 'EMPLOYERS_NAME=P',nvl(upper(hoi.org_information3),' ')
685 from pay_payroll_actions pact,
686 hr_organization_information hoi
687 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
688 and pact.business_group_id = hoi.organization_id
689 and hoi.org_information_context = 'Tax Details References'
690 and nvl(hoi.org_information10,'UK') = 'UK'
691 and substr(pact.legislative_parameters, instr(pact.legislative_parameters,'TAX_REF=') + 8,
692 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
693 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
694 --
695 --
696 cursor csr_p46_ver6_pennot_assg is
697 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
698 'CHARS_ALREADY_TESTED=P', 'N',
699 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
700 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '), -- Bug 7038073 Added nvl
701 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '), -- Bug 7038073 Added nvl
702 'ADDRESS_LINE4=P', ' ',
703 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
704 'SEX=P', nvl(peo.action_information17,' '),
705 'ANNUAL_PENSION=P', p46p.action_information2,
706 'DATE_PENSION_STARTED=P', nvl(p46p.action_information3,'0001/01/01 00:00:00'),
710 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
707 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
708 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
709 'JOB_TITLE=P', nvl(peo.action_information18,' '),
711 --For bug 7540858 : P46 Pennot version 6
712 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
713 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
714 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
715 'NATIONAL_INSURANCE_NUMBER=P', peo.action_information12,
716 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
717 'TITLE=P', nvl(peo.action_information14,' '),
718 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
719 'MID_NAME=P', upper(substr(peo.action_information7,1,35)),
720 'RECENT_BEREAVED=P', p46p.action_information10,
721 'PREV_EMP_REF=P', nvl(upper(substr(ltrim(substr(p46p.action_information4,4,11),'/'),1,10)),' '),
722 'PREV_HMRC_NO=P', nvl(substr(p46p.action_information4,1,3),' '),
723 -- Bug 7038073 These fields are numeric hence assigning 0.00 if the value is NULL
724 /*'TOTAL_PAY=P', nvl(p46p.action_information11,' '),
725 'TOTAL_TAX=P', nvl(p46p.action_information12,' '),*/
726 /*'TOTAL_PAY=P', nvl(p46p.action_information11,'0'),
727 'TOTAL_TAX=P', nvl(p46p.action_information12,'0'),*/
728 'TOTAL_PAY=P', nvl(decode(p46p.action_information11,'0',null,p46p.action_information11),' '), --7830717
729 'TOTAL_TAX=P', nvl(decode(p46p.action_information12,'0',null,p46p.action_information12),' '), --7830717
730 'DATE_LEFT_PREV_EMP=P', nvl(p46p.action_information5,'0001/01/01 00:00:00'),
731 'DATE_OF_BIRTH=P', nvl(peo.action_information15,'0001/01/01 00:00:00'),
732 'TAX_CODE_LEAVING=P', nvl(p46p.action_information6,' '),
733 'TAX_BASIS_PREV=P', nvl(p46p.action_information7,' '),
734 'PREV_PAY_TYPE=P', nvl(p46p.action_information8,' '),
735 'PREV_PAY_PERIOD=P', nvl(p46p.action_information9,' '),
736 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date)
737 from pay_payroll_actions pay,
738 pay_assignment_actions paa,
739 pay_action_information addr,
740 pay_action_information peo,
741 pay_action_information p46p
742 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
743 and pay.payroll_action_id = paa.payroll_action_id
744 and paa.assignment_action_id = peo.action_context_id
745 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
746 and peo.action_context_type = 'AAP'
747 and paa.assignment_action_id = addr.action_context_id
748 and addr.action_information_category = 'ADDRESS DETAILS'
749 and addr.action_context_type = 'AAP'
750 and paa.assignment_action_id = p46p.action_context_id
751 and p46p.action_information_category = 'GB P46 PENNOT EDI'
752 and p46p.action_context_type = 'AAP';
753
754 /***** Year end changes for P46 PENNOT Version 6 End********/
755 ---
756 cursor csr_p46_5_header is
757 select 'SENDER_ID=P', nvl(UPPER(hoi.org_information11),' '),
758 'RECEIVER_ID=P', 'HMRC',
759 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
760 'URGENT_MARKER=P', ' ',--decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
761 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
762 'FORM_TYPE=P', '7',
763 'FORM_TYPE_MEANING=P', 'P46_5',
764 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
765 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
766 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
767 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
768 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' '),
769 'TEST_ID=P', pay_magtape_generic.get_parameter_value('TEST_ID')
770 from pay_payroll_actions pact,
771 hr_organization_information hoi
772 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
773 and pact.business_group_id = hoi.organization_id
774 and hoi.org_information_context = 'Tax Details References'
775 and nvl(hoi.org_information10,'UK') = 'UK'
776 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
777 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
778 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
779
780 cursor csr_p46_5_assignments is
781 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
782 'CHARS_ALREADY_TESTED=P', 'N',
783 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
784 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
785 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
786 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
787 'ASSIGNMENT_ID=P', paa.assignment_id,
788 'P46_STATEMENT=P', nvl(p46.action_information2,' '),
789 'P46_STATEMENT_STUDENT_LOAN=P', nvl(p46.action_information3,' '),
790 'DEFAULT_P46=P', nvl(p46.action_information4,' '),
791 'DATE_OF_BIRTH=P', nvl(peo.action_information15,' '),
792 'HIRE_DATE=P', nvl(peo.action_information16,' '),
793 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
794 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
795 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
799 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
796 --For bug 7540858 : P46 version 5
797 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
798 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
800 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,' '),
801 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
802 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
803 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
804 'SEX=P', nvl(peo.action_information17,' '),
805 'JOB_TITLE=P', nvl(peo.action_information18,' '),
806 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date)
807 from pay_payroll_actions pay,
808 pay_assignment_actions paa,
809 pay_action_information addr,
810 pay_action_information peo,
811 pay_action_information p46
812 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
813 and pay.payroll_action_id = paa.payroll_action_id
814 and paa.assignment_action_id = peo.action_context_id
815 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
816 and peo.action_context_type = 'AAP'
817 and paa.assignment_action_id = addr.action_context_id
818 and addr.action_information_category = 'ADDRESS DETAILS'
819 and addr.action_context_type = 'AAP'
820 and paa.assignment_action_id = p46.action_context_id
821 and p46.action_information_category = 'GB P46_5 EDI'
822 and p46.action_context_type = 'AAP';
823
824 /***** END *****/
825
826 /*P46 Version6 EOY Changes Start*/
827 cursor csr_p46_ver6_header is
828 select 'SENDER_ID=P', nvl(UPPER(hoi.org_information11),' '),
829 'RECEIVER_ID=P', 'HMRC',
830 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
831 'URGENT_MARKER=P', ' ',--decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
832 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
833 'FORM_TYPE=P', '7',
834 'FORM_TYPE_MEANING=P', 'P46_VER6',
835 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
836 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
837 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
838 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
839 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' '),
840 'TEST_ID=P', pay_magtape_generic.get_parameter_value('TEST_ID')
841 from pay_payroll_actions pact,
842 hr_organization_information hoi
843 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
844 and pact.business_group_id = hoi.organization_id
845 and hoi.org_information_context = 'Tax Details References'
846 and nvl(hoi.org_information10,'UK') = 'UK'
847 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
848 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
849 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
850
851 cursor csr_p46_ver6_assignments is
852 select /*+ ordered */ 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
853 'CHARS_ALREADY_TESTED=P', 'N',
854 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
855 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
856 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
857 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
858 'ASSIGNMENT_ID=P', paa.assignment_id,
859 'P46_STATEMENT=P', nvl(p46.action_information2,' '),
860 'P46_STATEMENT_STUDENT_LOAN=P', nvl(p46.action_information3,' '),
861 'DEFAULT_P46=P', nvl(p46.action_information4,' '),
862 'DATE_OF_BIRTH=P', nvl(peo.action_information15,' '),
863 'HIRE_DATE=P', nvl(peo.action_information16,' '),
864 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
865 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
866 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
867 --For bug 7540858 : P46 version 6
868 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
869 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
870 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
871 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,' '),
872 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
873 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
874 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
875 'SEX=P', nvl(peo.action_information17,' '),
876 'JOB_TITLE=P', nvl(peo.action_information18,' '),
877 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date)
878 from pay_payroll_actions pay,
879 pay_assignment_actions paa,
880 pay_action_information addr,
881 pay_action_information peo,
882 pay_action_information p46
883 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
884 and pay.payroll_action_id = paa.payroll_action_id
885 and paa.assignment_action_id = peo.action_context_id
886 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
887 and peo.action_context_type = 'AAP'
888 and paa.assignment_action_id = addr.action_context_id
889 and addr.action_information_category = 'ADDRESS DETAILS'
890 and addr.action_context_type = 'AAP'
894
891 and paa.assignment_action_id = p46.action_context_id
892 and p46.action_information_category = 'GB P46_5 EDI'
893 and p46.action_context_type = 'AAP';
895 /*P46 Version6 EOY Changes End*/
896
897
898 /*Changes for P46EXP_Ver6 starts*/
899 cursor csr_p46exp_ver6_header is
900 select 'SENDER_ID=P', nvl(UPPER(hoi.org_information11),' '),
901 'RECEIVER_ID=P', 'HMRC',
902 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
903 'URGENT_MARKER=P', ' ',
904 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
905 'FORM_TYPE=P', '23',
906 'FORM_TYPE_MEANING=P', 'P46EXP_VER6',
907 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
908 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
909 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
910 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
911 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' '),
912 'TEST_ID=P', pay_magtape_generic.get_parameter_value('TEST_ID')
913 from pay_payroll_actions pact,
914 hr_organization_information hoi
915 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
916 and pact.business_group_id = hoi.organization_id
917 and hoi.org_information_context = 'Tax Details References'
918 and nvl(hoi.org_information10,'UK') = 'UK'
919 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
920 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
921 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
922
923 cursor csr_p46exp_ver6_assignments is
924 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
925 'CHARS_ALREADY_TESTED=P', 'N',
926 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
927 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
928 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
929 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
930 'ASSIGNMENT_ID=P', paa.assignment_id,
931 'P46_EXPAT_STATEMENT=P', nvl(p46.action_information2,' '),
932 'P46_EXPAT_STUDENT_LOAN=P', nvl(p46.action_information3,' '),
933 'DATE_OF_BIRTH=P', nvl(peo.action_information15,' '),
934 'HIRE_DATE=P', nvl(peo.action_information16,' '),
935 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
936 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
937 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
938 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
939 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
940 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
941 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,' '),
942 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
943 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
944 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
945 'SEX=P', nvl(peo.action_information17,' '),
946 'JOB_TITLE=P', nvl(peo.action_information18,' '),
947 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date),
948 'P46_EXPAT_EEA_CITIZEN=P', nvl(p46.action_information4,' '),
949 'P46_EXPAT_START_EMPL_DATE=P', nvl(p46.action_information5,' '),
950 'P46_EXPAT_EMP6_SCHEME=P', nvl(p46.action_information6,' ')
951
952 from pay_payroll_actions pay,
953 pay_assignment_actions paa,
954 pay_action_information addr,
955 pay_action_information peo,
956 pay_action_information p46
957 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
958 and pay.payroll_action_id = paa.payroll_action_id
959 and paa.assignment_action_id = peo.action_context_id
960 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
961 and peo.action_context_type = 'AAP'
962 and paa.assignment_action_id = addr.action_context_id
963 and addr.action_information_category = 'ADDRESS DETAILS'
964 and addr.action_context_type = 'AAP'
965 and paa.assignment_action_id = p46.action_context_id
966 and p46.action_information_category = 'GB P46EXP EDI'
967 and p46.action_context_type = 'AAP';
968
969
970 /*Changes for P46EXP_Ver6 End*/
971
972 --
973 level_cnt number;
974 --
975 --
976 PROCEDURE archinit ( p_payroll_action_id IN NUMBER);
977 --
978 PROCEDURE range_cursor (pactid IN NUMBER,
979 sqlstr OUT NOCOPY VARCHAR2);
980 --
981 PROCEDURE p45_3_action_creation(pactid IN NUMBER,
982 stperson IN NUMBER,
983 endperson IN NUMBER,
984 chunk IN NUMBER);
985 --
986 /* changes for P45PT_3 start */
987 PROCEDURE p45pt_3_action_creation(pactid IN NUMBER,
988 stperson IN NUMBER,
989 endperson IN NUMBER,
990 chunk IN NUMBER);
991 /* changes for P45PT_3 end */
992
993 /*Changes for P45PT_3 Version 6 Start*/
994 PROCEDURE p45pt_3_ver6_action_creation(pactid IN NUMBER,
995 stperson IN NUMBER,
996 endperson IN NUMBER,
997 chunk IN NUMBER);
998 /*Changes for P45PT_3 Version 6 End*/
999 --
1000 PROCEDURE p46_action_creation(pactid IN NUMBER,
1004 --
1001 stperson IN NUMBER,
1002 endperson IN NUMBER,
1003 chunk IN NUMBER);
1005 PROCEDURE p46_5_action_creation (pactid in number,
1006 stperson in number,
1007 endperson in number,
1008 chunk in number);
1009
1010 --
1011 /*P46 Version6 EOY Changes Start*/
1012 PROCEDURE p46_ver6_action_creation (pactid in number,
1013 stperson in number,
1014 endperson in number,
1015 chunk in number);
1016 /*P46 Version6 EOY Changes End*/
1017
1018 /*Changes for P46EXP_Ver6 starts*/
1019 PROCEDURE P46EXP_VER6_ACTION_CREATION (pactid in number,
1020 stperson in number,
1021 endperson in number,
1022 chunk in number);
1023 /*Changes for P46EXP_Ver6 End*/
1024
1025 PROCEDURE p46_pennot_action_creation(pactid IN NUMBER,
1026 stperson IN NUMBER,
1027 endperson IN NUMBER,
1028 chunk IN NUMBER);
1029
1030 /**UK EOY P46 PENNOT --- Corresponds to CP PENNOT EDI Process **/
1031 PROCEDURE p46_5_pennot_action_creation(pactid IN NUMBER,
1032 stperson IN NUMBER,
1033 endperson IN NUMBER,
1034 chunk IN NUMBER);
1035
1036 --
1037 /**UK EOY 08-09 P46 PENNOT --- Corresponds to CP PENNOT EDI Process **/
1038 PROCEDURE P46_VER6_PENNOT_ACT_CREATION(pactid IN NUMBER,
1039 stperson IN NUMBER,
1040 endperson IN NUMBER,
1041 chunk IN NUMBER);
1042 --
1043 PROCEDURE archive_code (p_assactid IN NUMBER,
1044 p_effective_date IN DATE);
1045 --
1046 PROCEDURE deinitialization_code(pactid IN NUMBER);
1047 --
1048 FUNCTION date_validate (c_assignment_action_id NUMBER,
1049 p_mode VARCHAR2,
1050 p_validate_date DATE)
1051 RETURN NUMBER;
1052
1053 function edi_errors_log(assignment_number IN varchar2,
1054 ni_number IN varchar2,
1055 first_name IN varchar2,
1056 last_name IN varchar2,
1057 middle_name IN varchar2,
1058 title IN varchar2,
1059 status IN varchar2)
1060 RETURN NUMBER;
1061
1062 --For bug 9255173:
1063 /*P46 Version6 eText Changes Start*/
1064 PROCEDURE p46_ver6et_action_creation (pactid in number,
1065 stperson in number,
1066 endperson in number,
1067 chunk in number);
1068
1069 FUNCTION tax_dist_etext_vals(p_tst_indi in varchar2,
1070 p_tst_id in varchar2,
1071 p_tax_ref in varchar2,
1072 p_employer_name in varchar2)
1073 Return BOOLEAN;
1074 /*P46 Version6 eText Changes End*/
1075
1076 --For bug 9255183:
1077 /*P46Expat eText Changes Start*/
1078 PROCEDURE P46EXP_VER6ET_ACTION_CREATION (pactid in number,
1079 stperson in number,
1080 endperson in number,
1081 chunk in number);
1082
1083 g_archive_type varchar2(20);
1084 /*P46Expat eText Changes End*/
1085
1086 TYPE edi_errors IS RECORD
1087 (assignment_number varchar2(100),
1088 ni_number varchar2(30),
1089 first_name varchar2(100),
1090 last_name varchar2(100),
1091 middle_name varchar2(100),
1092 title varchar2(100),
1093 status varchar2(100));
1094
1095 TYPE edi_errors_table IS TABLE OF
1096 edi_errors INDEX BY BINARY_INTEGER;
1097
1098 g_edi_errors_table edi_errors_table;
1099
1100 END PAY_GB_MOVDED_EDI;