[Home] [Help]
PACKAGE: APPS.PAY_GB_MOVDED_EDI
Source
1 PACKAGE PAY_GB_MOVDED_EDI as
2 /* $Header: pygbmedi.pkh 120.16.12010000.5 2008/08/06 07:18:23 ubhat 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 ============================================================================*/
105 --
106 --
107 --
108 -- Function to fetch country name for the given country code
109 --
110 function get_territory_short_name(prm_name varchar2)
111 return varchar2;
112
113 cursor csr_p45_3_header is
114 select 'SENDER_ID=P', nvl(hoi.org_information11,' '),
115 'RECEIVER_ID=P', 'INLAND REVENUE',
116 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
117 'URGENT_MARKER=P', decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
118 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
119 'FORM_TYPE=P', '5',
120 'FORM_TYPE_MEANING=P', 'P45_3',
121 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
122 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
123 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
124 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
125 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' ')
126 from pay_payroll_actions pact,
127 hr_organization_information hoi
128 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
129 and pact.business_group_id = hoi.organization_id
130 and hoi.org_information_context = 'Tax Details References'
131 and nvl(hoi.org_information10,'UK') = 'UK'
132 and substr(pact.legislative_parameters, instr(pact.legislative_parameters, 'TAX_REF=') + 8,
133 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
134 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
135 --
136 --
137 cursor csr_p45_3_assignments is
138 select 'ASSIGNMENT_ACTION_ID=C',paa.assignment_action_id,
139 'CHARS_ALREADY_TESTED=P','N',
140 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
141 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
142 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
143 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
144 'SEX=P', nvl(peo.action_information17,' '),
145 'PREV_TAX_REFERENCE=P', nvl(upper(substr(ltrim(substr(p453.action_information2,4,11),'/'),1,10)),' '),
146 'PREV_TAX_DISTRICT=P', nvl(substr(p453.action_information2,1,3),' '),
147 'DATE_OF_BIRTH=P', nvl(peo.action_information15,' '),
148 'HIRE_DATE=P', nvl(peo.action_information16,' '),
149 'DATE_LEFT_PREV_EMP=P', nvl(p453.action_information3,' '),
150 'PREV_TAX_CODE=P', nvl(p453.action_information4,' '),
151 'PREV_TAX_BASIS=P', nvl(p453.action_information5,' '),
152 'PREV_LAST_PAY_TYPE=P', nvl(p453.action_information6,' '),
153 'PREV_LAST_PAY_PERIOD=P',nvl(p453.action_information7,' '),
154 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
155 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
156 'PAY_PREVIOUS=P', nvl(p453.action_information8,'0'),
157 'TAX_PREVIOUS=P', nvl(p453.action_information9,'0'),
158 'JOB_TITLE=P', peo.action_information18,
159 'COUNTY=P', nvl(upper(addr.action_information9),' '),
160 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
161 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
162 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,'NONE'),
163 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
164 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
165 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
166 'STUDENT_LOAN_FLAG_START_DATE=P', nvl(p453.action_information10,'X'),
167 'STUDENT_LOAN_FLAG_END_DATE=P', nvl(p453.action_information11,'4712/12/31 00:00:00'),
168 'STUDENT_LOAN_FLAG_EFFECTIVE_END_DATE=P', nvl(p453.action_information12,'4712/12/31 00:00:00') ,
169 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date)
170 from pay_payroll_actions pay,
171 pay_assignment_actions paa,
172 pay_action_information addr,
173 pay_action_information peo,
174 pay_action_information p453
175 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
176 and pay.payroll_action_id = paa.payroll_action_id
177 and paa.assignment_action_id = peo.action_context_id
178 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
179 and peo.action_context_type = 'AAP'
180 and paa.assignment_action_id = addr.action_context_id
181 and addr.action_information_category = 'ADDRESS DETAILS'
182 and addr.action_context_type = 'AAP'
183 and paa.assignment_action_id = p453.action_context_id
184 and p453.action_information_category = 'GB P45(3) EDI'
185 and p453.action_context_type = 'AAP';
186 --
187 --
188 cursor csr_p46_header is
189 select 'SENDER_ID=P', nvl(hoi.org_information11,' '),
190 'RECEIVER_ID=P', 'INLAND REVENUE',
191 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
192 'URGENT_MARKER=P', decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
193 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
194 'FORM_TYPE=P', '7',
195 'FORM_TYPE_MEANING=P', 'P46',
196 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
197 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
198 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
199 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
200 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' ')
201 from pay_payroll_actions pact,
202 hr_organization_information hoi
203 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
204 and pact.business_group_id = hoi.organization_id
205 and hoi.org_information_context = 'Tax Details References'
206 and nvl(hoi.org_information10,'UK') = 'UK'
207 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
208 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
209 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
210
211 cursor csr_p46_assignments is
212 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
213 'CHARS_ALREADY_TESTED=P', 'N',
214 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
215 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
216 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
217 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
218 'ASSIGNMENT_ID=P', paa.assignment_id,
219 'P46_STATEMENT=P', nvl(p46.action_information2,'N'),
220 'P46_STATEMENT_STUDENT_LOAN=P', nvl(p46.action_information3,'N'),
221 'DATE_OF_BIRTH=P', peo.action_information15,
222 'HIRE_DATE=P', peo.action_information16,
223 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
224 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
225 'COUNTY=P', nvl(upper(addr.action_information9),' '),
226 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
227 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
228 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,'NONE'),
229 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
230 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
231 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
232 'SEX=P', nvl(peo.action_information17,' '),
233 'JOB_TITLE=P', nvl(peo.action_information18,' ')
234 from pay_payroll_actions pay,
235 pay_assignment_actions paa,
236 pay_action_information addr,
237 pay_action_information peo,
238 pay_action_information p46
239 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
240 and pay.payroll_action_id = paa.payroll_action_id
241 and paa.assignment_action_id = peo.action_context_id
242 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
243 and peo.action_context_type = 'AAP'
244 and paa.assignment_action_id = addr.action_context_id
245 and addr.action_information_category = 'ADDRESS DETAILS'
246 and addr.action_context_type = 'AAP'
247 and paa.assignment_action_id = p46.action_context_id
248 and p46.action_information_category = 'GB P46 EDI'
249 and p46.action_context_type = 'AAP';
250 --
251 --
252 cursor csr_p46_pennot_header is
253 select 'SENDER_ID=P', hoi.org_information11,
254 'RECEIVER_ID=P', 'INLAND REVENUE',
255 'TEST_INDICATOR=P',decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
256 'URGENT_MARKER=P', decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
257 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
258 'FORM_TYPE=P', '6',
259 'FORM_TYPE_MEANING=P', 'P46_PENNOT',
260 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
261 'TAX_DIST_REF=P', upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),
262 'TAX_DISTRICT=P', upper(hoi.org_information2),
263 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
264 'EMPLOYERS_NAME=P',nvl(upper(hoi.org_information3),' ')
265 from pay_payroll_actions pact,
266 hr_organization_information hoi
267 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
268 and pact.business_group_id = hoi.organization_id
269 and hoi.org_information_context = 'Tax Details References'
270 and nvl(hoi.org_information10,'UK') = 'UK'
271 and substr(pact.legislative_parameters, instr(pact.legislative_parameters,'TAX_REF=') + 8,
272 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
273 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
274 --
275 --
276 cursor csr_p46_pennot_assignments is
277 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
281 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
278 'CHARS_ALREADY_TESTED=P', 'N',
279 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
280 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
282 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
283 'SEX=P', nvl(peo.action_information17,' '),
284 'ANNUAL_PENSION=P', nvl(p46p.action_information2,'X'),
285 'DATE_PENSION_STARTED=P', nvl(p46p.action_information3,'0001/01/01 00:00:00'),
286 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
287 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
288 'JOB_TITLE=P', nvl(peo.action_information18,' '),
289 'COUNTY=P', nvl(upper(addr.action_information9),' '),
290 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
291 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
292 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,'NONE'),
293 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
294 'TITLE=P', nvl(peo.action_information14,' '),
295 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' ')
296 from pay_payroll_actions pay,
297 pay_assignment_actions paa,
298 pay_action_information addr,
299 pay_action_information peo,
300 pay_action_information p46p
301 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
302 and pay.payroll_action_id = paa.payroll_action_id
303 and paa.assignment_action_id = peo.action_context_id
304 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
305 and peo.action_context_type = 'AAP'
306 and paa.assignment_action_id = addr.action_context_id
307 and addr.action_information_category = 'ADDRESS DETAILS'
308 and addr.action_context_type = 'AAP'
309 and paa.assignment_action_id = p46p.action_context_id
310 and p46p.action_information_category = 'GB P46 Pension EDI'
311 and p46p.action_context_type = 'AAP';
312 --
313 --
314 -- Bug 5398360
315 cursor csr_wnu3_header is
316 select 'SENDER_ID=P', nvl(hoi.org_information11,' '),
317 'RECEIVER_ID=P', 'INLAND REVENUE',
318 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
319 'URGENT_MARKER=P', ' ', -- as a space is required in this place
320 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
321 'FORM_TYPE=P', '18',
322 'FORM_TYPE_MEANING=P', 'WNU',
323 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
324 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
325 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
326 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
327 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' ')
328 from pay_payroll_actions pact,
329 hr_organization_information hoi
330 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
331 and pact.business_group_id = hoi.organization_id
332 and hoi.org_information_context = 'Tax Details References'
333 and nvl(hoi.org_information10,'UK') = 'UK'
334 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
335 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
336 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
337 --
338 --
339 cursor csr_wnu3_assignments is
340 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
341 'ADDRESS_LINE1=P', ' ', -- wnu3 does not require address
342 'ADDRESS_LINE2=P', ' ',
343 'ADDRESS_LINE3=P', ' ',
344 'COUNTY=P', ' ',
345 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
346 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
347 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,'NONE'),
348 'POSTAL_CODE=P', ' ',
349 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
350 'TOWN_OR_CITY=P', ' ',
351 'DATE_OF_BIRTH=P', peo.action_information15,
352 'HIRE_DATE=P', peo.action_information16,
353 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
354 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
355 'CHARS_ALREADY_TESTED=P', 'N',
356 'ASSIGNMENT_ID=P', paa.assignment_id,
357 'ASSIGNMENT_NUMBER=P', nvl(upper(peo.action_information11), ' '), -- for MOVDED_ASG
358 'NEW_ASSIGNMENT_NUMBER=P',nvl(upper(peo.action_information11), ' ') , -- for PAY_GB_EDI_WNU_3
359 'OLD_ASSIGNMENT_NUMBER=P',nvl(upper(wnu3.action_information2), ' ') -- for PAY_GB_EDI_WNU_3
360 from pay_assignment_actions paa,
361 pay_action_information peo,
362 pay_action_information wnu3
363 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
364 and paa.assignment_action_id = peo.action_context_id
365 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
366 and peo.action_context_type = 'AAP'
367 and paa.assignment_action_id = wnu3.action_context_id(+)
368 and wnu3.action_information_category(+) = 'GB WNU EDI'
369 and wnu3.action_context_type(+)= 'AAP';
370
371 --
372 /* changes for P45PT_3 start*/
376 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
373 cursor csr_p45pt_3_header is
374 select 'SENDER_ID=P', nvl(hoi.org_information11,' '),
375 'RECEIVER_ID=P', 'HMRC', /* changed for P45PT_3*/
377 'URGENT_MARKER=P', ' ',
378 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
379 'FORM_TYPE=P', '5',
380 'FORM_TYPE_MEANING=P', 'P45PT3', /* changed for P45PT_3*/
381 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
382 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
383 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
384 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
385 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' '),
386 'TEST_ID=P', nvl(pay_magtape_generic.get_parameter_value('TEST_ID'),' ') /*added for P45PT_3*/
387 from pay_payroll_actions pact,
388 hr_organization_information hoi
389 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
390 and pact.business_group_id = hoi.organization_id
391 and hoi.org_information_context = 'Tax Details References'
392 and nvl(hoi.org_information10,'UK') = 'UK'
393 and substr(pact.legislative_parameters, instr(pact.legislative_parameters, 'TAX_REF=') + 8,
394 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
395 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
396 --
397 --
398 cursor csr_p45pt_3_assignments is
399 select 'ASSIGNMENT_ACTION_ID=C',paa.assignment_action_id,
400 'CHARS_ALREADY_TESTED=P','N',
401 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
402 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
403 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
404 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
405 'SEX=P', nvl(peo.action_information17,' '),
406 'PREV_TAX_REFERENCE=P', nvl(upper(substr(ltrim(substr(p453.action_information2,4,11),'/'),1,10)),' '),
407 'PREV_TAX_DISTRICT=P', nvl(substr(p453.action_information2,1,3),' '),
408 'DATE_OF_BIRTH=P', peo.action_information15,
409 'HIRE_DATE=P', peo.action_information16,
410 'DATE_LEFT_PREV_EMP=P', nvl(p453.action_information3,'0001/01/01 00:00:00'),
411 'PREV_TAX_CODE=P', nvl(p453.action_information4,' '),
412 'PREV_TAX_BASIS=P', nvl(p453.action_information5,' '),
413 'PREV_LAST_PAY_TYPE=P', nvl(p453.action_information6,' '),
414 'PREV_LAST_PAY_PERIOD=P',nvl(p453.action_information7,' '),
415 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
416 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
417 -- Bug 6994632 We need to report values form Newstarter tab instead of PAYE tab values
418 /*'PAY_PREVIOUS=P', nvl(p453.action_information8,' '),*/
419 -- Bug 7038073 These fields are numeric hence assigning 0.00 if the value is NULL
420 /*'PAY_PREVIOUS=P', nvl(decode(p453.action_information16,0,NULL,p453.action_information16),nvl(p453.action_information8,' ')),
421 'TAX_PREVIOUS=P', nvl(p453.action_information9,' '),*/
422 'PAY_PREVIOUS=P', nvl(decode(p453.action_information16,0,NULL,p453.action_information16),nvl(p453.action_information8,'0')),
423 'TAX_PREVIOUS=P', nvl(p453.action_information9,'0'),
424 'JOB_TITLE=P', peo.action_information18,
425 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
426 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
427 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
428 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
429 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,' '),
430 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
431 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
432 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
433 'STUDENT_LOAN_FLAG_START_DATE=P', nvl(p453.action_information10,'X'),
434 'STUDENT_LOAN_FLAG_END_DATE=P', nvl(p453.action_information11,'4712/12/31 00:00:00'),
435 'STUDENT_LOAN_FLAG_EFFECTIVE_END_DATE=P', nvl(p453.action_information12,'4712/12/31 00:00:00') ,
436 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date),
437 --Bug 7157720 report PREVIOUS_TAX_PAID_NOTIFIED if >=0
438 -- Bug 6994632 assigning NULL if it is zero
439 'PREVIOUS_TAX_PAID_NOTIFIED=P', nvl(p453.action_information13,' '),
440 --'PREVIOUS_TAX_PAID_NOTIFIED=P', nvl(decode(p453.action_information13,0,' ',p453.action_information13),' '),
441 'NOT_PAID_BETWEEN_START_AND5APR=P', decode(p453.action_information14,'Y','Y','N',' ',' '),
442 'CONTINUE_SL_DEDUCTIONS=P', decode(p453.action_information15,'Y','Y','N',' ',' ')
443 from pay_payroll_actions pay,
444 pay_assignment_actions paa,
445 pay_action_information addr,
446 pay_action_information peo,
447 pay_action_information p453
448 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
449 and pay.payroll_action_id = paa.payroll_action_id
450 and paa.assignment_action_id = peo.action_context_id
451 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
452 and peo.action_context_type = 'AAP'
456 and paa.assignment_action_id = p453.action_context_id
453 and paa.assignment_action_id = addr.action_context_id
454 and addr.action_information_category = 'ADDRESS DETAILS'
455 and addr.action_context_type = 'AAP'
457 and p453.action_information_category = 'GB P45(3) EDI'
458 and p453.action_context_type = 'AAP';
459 /* changes for P45PT_3 end*/
460
461
462 /***** Year end changes for P46 PENNOT ********/
463 /******* start *******/
464 cursor csr_p46_5_pennot_header is
465 select 'SENDER_ID=P', hoi.org_information11,
466 'RECEIVER_ID=P', 'HMRC',
467 'TEST_INDICATOR=P',decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
468 'TEST_ID=P', pay_magtape_generic.get_parameter_value('TEST_ID'),
469 'URGENT_MARKER=P', ' ',
470 'TRANSMISSION_DATE=P', to_char(sysdate,'CCYYMMDD'),
471 'TRANSMISSION_TIME=P', to_char(sysdate,'HHMMSS'),
472 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
473 'FORM_TYPE=P', '6',
474 'FORM_TYPE_MEANING=P', 'P46_5_PENNOT',
475 'UNIQUE_REF=P', '1234',
476 'SENDER_SUBAD=P',' ',
477 'RECIPIENT_SUBAD=P',' ',
478 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
479 'TAX_DIST_REF=P', upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),
480 'TAX_DISTRICT=P', upper(hoi.org_information2),
481 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
482 'EMPLOYERS_NAME=P',nvl(upper(hoi.org_information3),' ')
483 from pay_payroll_actions pact,
484 hr_organization_information hoi
485 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
486 and pact.business_group_id = hoi.organization_id
487 and hoi.org_information_context = 'Tax Details References'
488 and nvl(hoi.org_information10,'UK') = 'UK'
489 and substr(pact.legislative_parameters, instr(pact.legislative_parameters,'TAX_REF=') + 8,
490 instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters, 'TAX_REF=')+8) -
491 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
492 --
493 --
494 cursor csr_p46_5_pennot_assignments is
495 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
496 'CHARS_ALREADY_TESTED=P', 'N',
497 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
498 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '), -- Bug 7038073 Added nvl
499 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '), -- Bug 7038073 Added nvl
500 'ADDRESS_LINE4=P', ' ',
501 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
502 'SEX=P', nvl(peo.action_information17,' '),
503 'ANNUAL_PENSION=P', p46p.action_information2,
504 'DATE_PENSION_STARTED=P', nvl(p46p.action_information3,'0001/01/01 00:00:00'),
505 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
506 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
507 'JOB_TITLE=P', nvl(peo.action_information18,' '),
508 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
509 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
510 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
511 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
512 'NATIONAL_INSURANCE_NUMBER=P', peo.action_information12,
513 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
514 'TITLE=P', nvl(peo.action_information14,' '),
515 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
516 'MID_NAME=P', upper(substr(peo.action_information7,1,35)),
517 'RECENT_BEREAVED=P', p46p.action_information10,
518 'PREV_EMP_REF=P', upper(substr(ltrim(substr(p46p.action_information4,4,11),'/'),1,10)),
519 'PREV_HMRC_NO=P', substr(p46p.action_information4,1,3),
520 -- Bug 7038073 These fields are numeric hence assigning 0.00 if the value is NULL
521 /*'TOTAL_PAY=P', nvl(p46p.action_information11,' '),
522 'TOTAL_TAX=P', nvl(p46p.action_information12,' '),*/
523 'TOTAL_PAY=P', nvl(p46p.action_information11,'0'),
524 'TOTAL_TAX=P', nvl(p46p.action_information12,'0'),
525 'DATE_LEFT_PREV_EMP=P', nvl(p46p.action_information5,'0001/01/01 00:00:00'),
526 'DATE_OF_BIRTH=P', nvl(peo.action_information15,'0001/01/01 00:00:00'),
527 'TAX_CODE_LEAVING=P', nvl(p46p.action_information6,' '),
528 'TAX_BASIS_PREV=P', nvl(p46p.action_information7,' '),
529 'PREV_PAY_TYPE=P', nvl(p46p.action_information8,' '),
530 'PREV_PAY_PERIOD=P', nvl(p46p.action_information9,' '),
531 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date)
532 from pay_payroll_actions pay,
533 pay_assignment_actions paa,
534 pay_action_information addr,
535 pay_action_information peo,
536 pay_action_information p46p
537 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
538 and pay.payroll_action_id = paa.payroll_action_id
539 and paa.assignment_action_id = peo.action_context_id
540 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
541 and peo.action_context_type = 'AAP'
542 and paa.assignment_action_id = addr.action_context_id
543 and addr.action_information_category = 'ADDRESS DETAILS'
544 and addr.action_context_type = 'AAP'
545 and paa.assignment_action_id = p46p.action_context_id
546 and p46p.action_information_category = 'GB P46 PENNOT EDI'
547 and p46p.action_context_type = 'AAP';
548
549 ---
550 cursor csr_p46_5_header is
551 select 'SENDER_ID=P', nvl(UPPER(hoi.org_information11),' '),
552 'RECEIVER_ID=P', 'HMRC',
553 'TEST_INDICATOR=P', decode(pay_magtape_generic.get_parameter_value('TEST'),'N',' ','Y','1'),
554 'URGENT_MARKER=P', ' ',--decode(pay_magtape_generic.get_parameter_value('URGENT'),'N',' ','Y','1'),
555 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
556 'FORM_TYPE=P', '7',
557 'FORM_TYPE_MEANING=P', 'P46_5',
558 'TAX_DIST_NO=P', nvl(substr(hoi.org_information1,1,3),' '),
559 'TAX_DIST_REF=P', nvl(upper(substr(ltrim(substr(hoi.org_information1,4,11),'/'),1,10)),' '),
560 'TAX_DISTRICT=P', nvl(upper(hoi.org_information2),' '),
561 'EMPLOYERS_ADDRESS_LINE=P', nvl(upper(hoi.org_information4),' '),
562 'EMPLOYERS_NAME=P', nvl(upper(hoi.org_information3),' '),
563 'TEST_ID=P', pay_magtape_generic.get_parameter_value('TEST_ID')
564 from pay_payroll_actions pact,
565 hr_organization_information hoi
566 where pact.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
567 and pact.business_group_id = hoi.organization_id
568 and hoi.org_information_context = 'Tax Details References'
569 and nvl(hoi.org_information10,'UK') = 'UK'
570 and substr(pact.legislative_parameters,instr(pact.legislative_parameters,'TAX_REF=') + 8,
571 instr(pact.legislative_parameters||' ',' ', instr(pact.legislative_parameters,'TAX_REF=')+8) -
572 instr(pact.legislative_parameters, 'TAX_REF=') - 8) = hoi.org_information1;
573
574 cursor csr_p46_5_assignments is
575 select 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
576 'CHARS_ALREADY_TESTED=P', 'N',
577 'ADDRESS_LINE1=P', nvl(upper(substr(addr.action_information5,1,35)),' '),
578 'ADDRESS_LINE2=P', nvl(upper(substr(addr.action_information6,1,35)),' '),
579 'ADDRESS_LINE3=P', nvl(upper(substr(addr.action_information7,1,35)),' '),
580 'ASSIGNMENT_NUMBER=P', nvl(peo.action_information11,' '),
581 'ASSIGNMENT_ID=P', paa.assignment_id,
582 'P46_STATEMENT=P', nvl(p46.action_information2,' '),
583 'P46_STATEMENT_STUDENT_LOAN=P', nvl(p46.action_information3,' '),
584 'DEFAULT_P46=P', nvl(p46.action_information4,' '),
585 'DATE_OF_BIRTH=P', nvl(peo.action_information15,' '),
586 'HIRE_DATE=P', nvl(peo.action_information16,' '),
587 'TAX_CODE_IN_USE=P', nvl(peo.action_information21,' '),
588 'TAX_BASIS_IN_USE=P', nvl(peo.action_information22,' '),
589 'COUNTY=P', nvl(upper(pay_gb_movded_edi.get_territory_short_name(addr.action_information13)),' '), /* Country parameter*/
590 'FIRST_NAME=P', nvl(upper(substr(peo.action_information6,1,35)),' '),
591 'MIDDLE_NAME=P', nvl(upper(substr(peo.action_information7,1,35)),' '), /*Bug 6710229*/
592 'LAST_NAME=P', nvl(upper(substr(peo.action_information8,1,35)),' '),
593 'NATIONAL_INSURANCE_NUMBER=P', nvl(peo.action_information12,' '),
594 'POSTAL_CODE=P', nvl(addr.action_information12,' '),
595 'TITLE=P', nvl(substr(peo.action_information14,1,4),' '),
596 'TOWN_OR_CITY=P', nvl(upper(addr.action_information8),' '),
597 'SEX=P', nvl(peo.action_information17,' '),
598 'JOB_TITLE=P', nvl(peo.action_information18,' '),
599 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pay.effective_date)
600 from pay_payroll_actions pay,
601 pay_assignment_actions paa,
602 pay_action_information addr,
603 pay_action_information peo,
604 pay_action_information p46
605 where pay.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
606 and pay.payroll_action_id = paa.payroll_action_id
607 and paa.assignment_action_id = peo.action_context_id
608 and peo.action_information_category = 'GB EMPLOYEE DETAILS'
609 and peo.action_context_type = 'AAP'
610 and paa.assignment_action_id = addr.action_context_id
611 and addr.action_information_category = 'ADDRESS DETAILS'
612 and addr.action_context_type = 'AAP'
613 and paa.assignment_action_id = p46.action_context_id
614 and p46.action_information_category = 'GB P46_5 EDI'
615 and p46.action_context_type = 'AAP';
616
617 /***** END *****/
618 --
619 level_cnt number;
620 --
621 --
622 PROCEDURE archinit ( p_payroll_action_id IN NUMBER);
623 --
624 PROCEDURE range_cursor (pactid IN NUMBER,
625 sqlstr OUT NOCOPY VARCHAR2);
626 --
627 PROCEDURE p45_3_action_creation(pactid IN NUMBER,
628 stperson IN NUMBER,
629 endperson IN NUMBER,
630 chunk IN NUMBER);
631 --
632 /* changes for P45PT_3 start */
633 PROCEDURE p45pt_3_action_creation(pactid IN NUMBER,
634 stperson IN NUMBER,
635 endperson IN NUMBER,
636 chunk IN NUMBER);
637 /* changes for P45PT_3 end */
638 --
639 PROCEDURE p46_action_creation(pactid IN NUMBER,
640 stperson IN NUMBER,
641 endperson IN NUMBER,
642 chunk IN NUMBER);
643 --
644 PROCEDURE p46_5_action_creation (pactid in number,
645 stperson in number,
646 endperson in number,
647 chunk in number);
648 --
649 PROCEDURE p46_pennot_action_creation(pactid IN NUMBER,
650 stperson IN NUMBER,
651 endperson IN NUMBER,
652 chunk IN NUMBER);
653
654 /**UK EOY P46 PENNOT --- Corresponds to CP PENNOT EDI Process **/
655 PROCEDURE p46_5_pennot_action_creation(pactid IN NUMBER,
656 stperson IN NUMBER,
657 endperson IN NUMBER,
658 chunk IN NUMBER);
659
660 --
661 PROCEDURE archive_code (p_assactid IN NUMBER,
662 p_effective_date IN DATE);
663 --
664 PROCEDURE deinitialization_code(pactid IN NUMBER);
665 --
666 FUNCTION date_validate (c_assignment_action_id NUMBER,
667 p_mode VARCHAR2,
668 p_validate_date DATE)
669 RETURN NUMBER;
670
671 END PAY_GB_MOVDED_EDI;