1 PACKAGE PAY_P45_PKG AUTHID CURRENT_USER as
2 /* $Header: payrp45.pkh 120.14.12020000.1 2012/06/28 16:34:01 appldev ship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================
9 Name
10 PAY_P45_PKG
11 Purpose
12 Supports the VIEW P45 form (PAYWSR45) called from the form PAYGBTAX.
13 This is a UK Specific form/package.
14 Notes
15
16 History
17 07-AUG-94 P.Shergil 40.0 Date Created.
18 29-AUG-94 H.Minton 40.1 Added Function to get the formula id
19 04-OCT-94 R.Fine 40.2 Renamed package to start PAY_
20 08-MAR-2000 J. Moyano 115.1 Function get_student_loan_flag added.
21 14-AUG-2000 A.Parkes 115.2 Added P45 Archiver hooks and cursors.
22 08-FEB-2001 S.Robinson 115.3 Procedure pop_term_from_archive added.
23 19-FEB-2001 A.Parkes 115.4 Removed action_status from
24 csr_edi_assignments select.
25 Added get_report_request_error func
26 29-MAR-2001 A.Parkes 115.5 Put space in RECEIVER_ID value.
27 115.6 842703 added X_TRANSFER_DATE param
28 to get_data
29 17-FEB-2002 S.Robinson 115.7 Added dbdrv commands.
30 27-FEB-2002 K.Thampan 115.8 Added X_STUDENT_LOAN_FLAG to procedure
31 pop_term_asg_from_archive
32 12-Dec-2002 A.Mills 115.9 Added nocopy via utility
33 20-Aug-2003 A.Mills 115.9 Agg PAYE changes.
34 13-May-2004 K.Thampan 115.12 Put NONE in the field where NI number
35 is not available on P45 EDI file
36 18-JAN-2005 navesriv 115.13 Displayed Employee PAYE Reference in full
37 by increasing the length of the field
38 26-APR-2005 kthampan 115.14 Add one parameter to the procedure
39 pop_term_asg_from_archive
40 19-JUN-2006 K.Thampan 115.15 Substr title to 4 chars. Bug 5169434
41 04-SEP-2006 ajeyam 115.16 New proc/functions created to find whether
42 P45 issued (or) not for the given
43 assignment. Bug 5144323
44 05-SEP-2006 ajeyam 115.17 Parameters added/changed for new report-
45 show the p45 issued for act asgs 5144323
46 13-NOV-2007 parusia 115.18 Added 2 new cursors csr_tax_district and
47 csr_edi_assignments for P45PT1. Bug 6345375
48 27-DEC-2007 rlingama 115.19 Modified X_COUNTY to X_COUNTRY in
49 csr_movded5_edi_assignments cursor.Bug 6710197
50 4-Jan-2008 parusia 115.20 Selected middle_name also from cursor
51 csr_movded5_edi_assignments. Bug 6710229
52 14-May-2008 rlingama 115.21 Bug 7028893.Added function PAYE_RETURN_P45_ISSUED_FLAG.
53 16-OCT-2008 vijranga 115.22 Added 2 new cursors csr_movded_ver6_tax_district
54 csr_movded_ver6_edi_assignments for P45PT1.
55 Bug 7433580.
56 17-OCT-2008 vijranga 115.23 Renamed cursor csr_movded_ver6_edi_assignments to
57 csr_movded_ver6_edi_assignment. Bug 7433580.
58 22-OCT-2008 vijranga 115.24 Changed FORM_TYPE_MEANING for ver6 in
59 csr_movded_ver6_tax_district.
60 10-DEC-2008 rlingama 115.27 P45 A4 2008-09 Changes.Bug 7261906
61 04-May-2009 jvaradra 115.28 Bug 7601088 Added function PAYE_SYNC_P45_ISSUED_FLAG
62 ============================================================================*/
63 --
64 -- CURSORS
65 CURSOR csr_tax_district IS
66 SELECT 'SENDER_ID=P', upper(nvl(org_information11,' ')),
67 'RECEIVER_ID=P', 'INLAND REVENUE',
68 'TEST_INDICATOR=P', decode(pay_gb_eoy_archive.get_parameter
69 (legislative_parameters,
70 'TEST'),'Y','1',' '),
71 'URGENT_MARKER=P', decode(pay_gb_eoy_archive.get_parameter
72 (legislative_parameters,
73 'URGENT'),'Y','Y',' '),
74 'REQUEST_ID=P', fnd_number.number_to_canonical(pact.request_id),
75 'FORM_TYPE=P', '4',
76 'FORM_TYPE_MEANING=P', 'P45',
77 'TAX_DIST_NO=P', substr(hoi.org_information1,1,3),
78 'TAX_DIST_REF=P',
79 upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10)), /* Bug no 4086012 */
80 'TAX_DISTRICT=P', upper(nvl(substr(hoi.org_information2 ,1,40),' ')),
81 'EMPLOYERS_ADDRESS_LINE=P',
82 upper(nvl(substr(hoi.org_information4,1,60),' ')),
83 'EMPLOYERS_NAME=P', upper(nvl(substr(hoi.org_information3,1,36),' '))
84 FROM pay_payroll_actions pact,
85 hr_organization_information hoi
86 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value
87 ('TRANSFER_PAYROLL_ACTION_ID')
88 AND hoi.org_information_context = 'Tax Details References'
89 AND substr(pact.legislative_parameters,
90 instr(pact.legislative_parameters,'TAX_REF=') + 8,
91 instr(pact.legislative_parameters||' ',' ',
92 instr(pact.legislative_parameters,'TAX_REF=')+8)
93 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
94 = hoi.org_information1
95 AND hoi.organization_id = pact.business_group_id;
96 --
97 CURSOR csr_edi_assignments IS
98 SELECT
99 'ASSIGNMENT_ACTION_ID=C', act.assignment_action_id,
100 'CHARS_ALREADY_TESTED=P', pay_gb_eoy_archive.get_parameter
101 (pact.legislative_parameters,'CHAR_ERROR'),
102 'ADDRESS_LINE1=P',
103 upper(nvl(max(decode(fue.user_entity_name,
104 'X_ADDRESS_LINE1',substr(fai.VALUE,1,35))),' ')),
105 'ADDRESS_LINE2=P',
106 upper(nvl(max(decode(fue.user_entity_name,
107 'X_ADDRESS_LINE2',substr(fai.VALUE,1,35))),' ')),
108 'ADDRESS_LINE3=P',
109 upper(nvl(max(decode(fue.user_entity_name,
110 'X_ADDRESS_LINE3',substr(fai.VALUE,1,35))),' ')),
111 'ASSIGNMENT_NUMBER=P',
112 upper(nvl(max(decode(fue.user_entity_name,
113 'X_ASSIGNMENT_NUMBER',
114 substr(fai.VALUE,1,20))),' ')),
115 'COUNTY=P',
116 upper(nvl(max(decode(fue.user_entity_name,'X_COUNTY',
117 substr(fai.VALUE,1,35))),' ')),
118 'FIRST_NAME=P',
119 nvl(max(decode(fue.user_entity_name,'X_FIRST_NAME',
120 substr(upper(fai.VALUE),1,35))),' '),
121 'LAST_NAME=P',
122 nvl(max(decode(fue.user_entity_name,'X_LAST_NAME',
123 substr(upper(fai.VALUE),1,35))),' '),
124 'NATIONAL_INSURANCE_NUMBER=P',
125 nvl(max(decode(fue.user_entity_name,
126 'X_NATIONAL_INSURANCE_NUMBER',
127 substr(upper(fai.VALUE),1,9))),'NONE'),
128 'POSTAL_CODE=P',
129 nvl(max(decode(fue.user_entity_name,'X_POSTAL_CODE',
130 substr(upper(fai.VALUE),1,9))),' '),
131 'TITLE=P',
132 nvl(max(decode(fue.user_entity_name,'X_TITLE',
133 substr(upper(fai.VALUE),1,4))),' '),
134 'TOWN_OR_CITY=P',
135 upper(nvl(max(decode(fue.user_entity_name,
136 'X_TOWN_OR_CITY',
137 substr(fai.VALUE,1,35))),' '))
138 FROM pay_assignment_actions act_edi,
139 pay_action_interlocks pai,
140 pay_assignment_actions act,
141 pay_payroll_actions pact,
142 ff_archive_items fai,
143 ff_user_entities fue
144 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
145 ('TRANSFER_PAYROLL_ACTION_ID')
146 AND act_edi.assignment_action_id = pai.locking_action_id
147 AND act.assignment_action_id = pai.locked_action_id
148 AND pact.payroll_action_id = act.payroll_action_id
149 AND act.assignment_action_id = fai.context1
150 AND fai.archive_type <> 'PA'
151 AND fai.user_entity_id = fue.user_entity_id
152 GROUP BY act.assignment_action_id, pay_gb_eoy_archive.get_parameter
153 (pact.legislative_parameters,'CHAR_ERROR'),
154 act.action_status
155 ORDER BY 12;
156 --
157 --
158 /* Added for P45PT1 ( Bug 6345375 ) */
159 CURSOR csr_movded5_tax_district IS
160 SELECT 'SENDER_ID=P', upper(nvl(org_information11,' ')),
161 'RECEIVER_ID=P' , 'HMRC',
162 'TEST_INDICATOR=P', decode(pay_gb_eoy_archive.get_parameter
163 (legislative_parameters,
164 'TEST'),'Y','1',' '),
165 'URGENT_MARKER=P' , ' ',
166 'REQUEST_ID=P' , fnd_number.number_to_canonical(pact.request_id),
167 'FORM_TYPE=P' , '4',
168 'FORM_TYPE_MEANING=P', 'P45PT1',
169 'TAX_DIST_NO=P' , substr(hoi.org_information1,1,3),
170 'TAX_DIST_REF=P' , upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10)), /* Bug no 4086012 */
171 'TAX_DISTRICT=P' , upper(nvl(substr(hoi.org_information2 ,1,40),' ')),
172 'EMPLOYERS_ADDRESS_LINE=P', upper(nvl(substr(hoi.org_information4,1,60),' ')),
173 'EMPLOYERS_NAME=P', upper(nvl(substr(hoi.org_information3,1,36),' ')),
174 'TEST_ID=P' , nvl(pay_gb_eoy_archive.get_parameter
175 (legislative_parameters,
176 'TEST_ID'),' ') /*added for P45PT1*/
177 FROM pay_payroll_actions pact,
178 hr_organization_information hoi
179 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value
180 ('TRANSFER_PAYROLL_ACTION_ID')
181 AND hoi.org_information_context = 'Tax Details References'
182 AND substr(pact.legislative_parameters,
183 instr(pact.legislative_parameters,'TAX_REF=') + 8,
184 instr(pact.legislative_parameters||' ',' ',
185 instr(pact.legislative_parameters,'TAX_REF=')+8)
186 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
187 = hoi.org_information1
188 AND hoi.organization_id = pact.business_group_id;
189 --
190 CURSOR csr_movded5_edi_assignments IS
191 SELECT
192 'ASSIGNMENT_ACTION_ID=C', act.assignment_action_id,
193 'CHARS_ALREADY_TESTED=P', pay_gb_eoy_archive.get_parameter
194 (pact.legislative_parameters,'CHAR_ERROR'),
195 'ADDRESS_LINE1=P',
196 upper(nvl(max(decode(fue.user_entity_name,
197 'X_ADDRESS_LINE1',substr(fai.VALUE,1,35))),' ')),
198 'ADDRESS_LINE2=P',
199 upper(nvl(max(decode(fue.user_entity_name,
200 'X_ADDRESS_LINE2',substr(fai.VALUE,1,35))),' ')),
201 'ADDRESS_LINE3=P',
202 upper(nvl(max(decode(fue.user_entity_name,
203 'X_ADDRESS_LINE3',substr(fai.VALUE,1,35))),' ')),
204 'ASSIGNMENT_NUMBER=P',
205 upper(nvl(max(decode(fue.user_entity_name,
206 'X_ASSIGNMENT_NUMBER',
207 substr(fai.VALUE,1,20))),' ')),
208 'COUNTY=P',
209 upper(nvl(max(decode(fue.user_entity_name,'X_COUNTRY',
210 substr(fai.VALUE,1,35))),' ')), /* Country parameter*/
211 'FIRST_NAME=P',
212 nvl(max(decode(fue.user_entity_name,'X_FIRST_NAME',
213 substr(upper(fai.VALUE),1,35))),' '),
214 'MIDDLE_NAME=P', /*Bug 6710229*/
215 nvl(max(decode(fue.user_entity_name,'X_MIDDLE_NAME',
216 substr(upper(fai.VALUE),1,35))),' '),
217 'LAST_NAME=P',
218 nvl(max(decode(fue.user_entity_name,'X_LAST_NAME',
219 substr(upper(fai.VALUE),1,35))),' '),
220 'NATIONAL_INSURANCE_NUMBER=P',
221 nvl(max(decode(fue.user_entity_name,
222 'X_NATIONAL_INSURANCE_NUMBER',
223 substr(upper(fai.VALUE),1,9))),' '),
224 'POSTAL_CODE=P',
225 nvl(max(decode(fue.user_entity_name,'X_POSTAL_CODE',
226 substr(upper(fai.VALUE),1,9))),' '),
227 'TITLE=P',
228 nvl(max(decode(fue.user_entity_name,'X_TITLE',
229 substr(upper(fai.VALUE),1,4))),' '),
230 'TOWN_OR_CITY=P',
231 upper(nvl(max(decode(fue.user_entity_name,
232 'X_TOWN_OR_CITY',
233 substr(fai.VALUE,1,35))),' ')),
234 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pact.effective_date)
235 FROM pay_assignment_actions act_edi,
236 pay_action_interlocks pai,
237 pay_assignment_actions act,
238 pay_payroll_actions pact,
239 ff_archive_items fai,
240 ff_user_entities fue
241 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
242 ('TRANSFER_PAYROLL_ACTION_ID')
243 AND act_edi.assignment_action_id = pai.locking_action_id
244 AND act.assignment_action_id = pai.locked_action_id
245 AND pact.payroll_action_id = act.payroll_action_id
246 AND act.assignment_action_id = fai.context1
247 AND fai.archive_type <> 'PA'
248 AND fai.user_entity_id = fue.user_entity_id
249 GROUP BY act.assignment_action_id, pay_gb_eoy_archive.get_parameter
250 (pact.legislative_parameters,'CHAR_ERROR'),
251 act.action_status, fnd_date.date_to_canonical(pact.effective_date)
252 ORDER BY 12;
253 /* Addition for P45PT1 ( Bug 6345375 ) ends*/
254 --
255 /* Added for P45PT1 Ver6 changes starts */
256 CURSOR csr_movded_ver6_tax_district IS
257 SELECT 'SENDER_ID=P', upper(nvl(org_information11,' ')),
258 'RECEIVER_ID=P' , 'HMRC',
259 'TEST_INDICATOR=P', decode(pay_gb_eoy_archive.get_parameter
260 (legislative_parameters,
261 'TEST'),'Y','1',' '),
262 'URGENT_MARKER=P' , ' ',
263 'REQUEST_ID=P' , fnd_number.number_to_canonical(pact.request_id),
264 'FORM_TYPE=P' , '4',
265 'FORM_TYPE_MEANING=P', 'P45PT1_VER6',
266 'TAX_DIST_NO=P' , substr(hoi.org_information1,1,3),
267 'TAX_DIST_REF=P' , upper(substr(ltrim(substr(hoi.org_information1,4,11),'/') ,1,10)),
268 'TAX_DISTRICT=P' , upper(nvl(substr(hoi.org_information2 ,1,40),' ')),
269 'EMPLOYERS_ADDRESS_LINE=P', upper(nvl(substr(hoi.org_information4,1,60),' ')),
270 'EMPLOYERS_NAME=P', upper(nvl(substr(hoi.org_information3,1,36),' ')),
271 'TEST_ID=P' , nvl(pay_gb_eoy_archive.get_parameter
272 (legislative_parameters,
273 'TEST_ID'),' ') /*added for P45PT1*/
274 FROM pay_payroll_actions pact,
275 hr_organization_information hoi
276 WHERE pact.payroll_action_id = pay_magtape_generic.get_parameter_value
277 ('TRANSFER_PAYROLL_ACTION_ID')
278 AND hoi.org_information_context = 'Tax Details References'
279 AND substr(pact.legislative_parameters,
280 instr(pact.legislative_parameters,'TAX_REF=') + 8,
281 instr(pact.legislative_parameters||' ',' ',
282 instr(pact.legislative_parameters,'TAX_REF=')+8)
283 - instr(pact.legislative_parameters,'TAX_REF=') - 8)
284 = hoi.org_information1
285 AND hoi.organization_id = pact.business_group_id;
286 --
287 CURSOR csr_movded_ver6_edi_assignment IS
288 SELECT
289 'ASSIGNMENT_ACTION_ID=C', act.assignment_action_id,
290 'CHARS_ALREADY_TESTED=P', pay_gb_eoy_archive.get_parameter
291 (pact.legislative_parameters,'CHAR_ERROR'),
292 'ADDRESS_LINE1=P',
293 upper(nvl(max(decode(fue.user_entity_name,
294 'X_ADDRESS_LINE1',substr(fai.VALUE,1,35))),' ')),
295 'ADDRESS_LINE2=P',
296 upper(nvl(max(decode(fue.user_entity_name,
297 'X_ADDRESS_LINE2',substr(fai.VALUE,1,35))),' ')),
298 'ADDRESS_LINE3=P',
299 upper(nvl(max(decode(fue.user_entity_name,
300 'X_ADDRESS_LINE3',substr(fai.VALUE,1,35))),' ')),
301 'ASSIGNMENT_NUMBER=P',
302 upper(nvl(max(decode(fue.user_entity_name,
303 'X_ASSIGNMENT_NUMBER',
304 substr(fai.VALUE,1,20))),' ')),
305 'COUNTY=P',
306 upper(nvl(max(decode(fue.user_entity_name,'X_COUNTRY',
307 substr(fai.VALUE,1,35))),' ')), /* Country parameter*/
308 'FIRST_NAME=P',
309 nvl(max(decode(fue.user_entity_name,'X_FIRST_NAME',
310 substr(upper(fai.VALUE),1,35))),' '),
311 'MIDDLE_NAME=P', /*Bug 6710229*/
312 nvl(max(decode(fue.user_entity_name,'X_MIDDLE_NAME',
313 substr(upper(fai.VALUE),1,35))),' '),
314 'LAST_NAME=P',
315 nvl(max(decode(fue.user_entity_name,'X_LAST_NAME',
316 substr(upper(fai.VALUE),1,35))),' '),
317 'NATIONAL_INSURANCE_NUMBER=P',
318 nvl(max(decode(fue.user_entity_name,
319 'X_NATIONAL_INSURANCE_NUMBER',
320 substr(upper(fai.VALUE),1,9))),' '),
321 'POSTAL_CODE=P',
322 nvl(max(decode(fue.user_entity_name,'X_POSTAL_CODE',
323 substr(upper(fai.VALUE),1,9))),' '),
324 'TITLE=P',
325 nvl(max(decode(fue.user_entity_name,'X_TITLE',
326 substr(upper(fai.VALUE),1,4))),' '),
327 'TOWN_OR_CITY=P',
328 upper(nvl(max(decode(fue.user_entity_name,
329 'X_TOWN_OR_CITY',
330 substr(fai.VALUE,1,35))),' ')),
331 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(pact.effective_date)
332 FROM pay_assignment_actions act_edi,
333 pay_action_interlocks pai,
334 pay_assignment_actions act,
335 pay_payroll_actions pact,
336 ff_archive_items fai,
337 ff_user_entities fue
338 WHERE act_edi.payroll_action_id = pay_magtape_generic.get_parameter_value
339 ('TRANSFER_PAYROLL_ACTION_ID')
340 AND act_edi.assignment_action_id = pai.locking_action_id
341 AND act.assignment_action_id = pai.locked_action_id
342 AND pact.payroll_action_id = act.payroll_action_id
343 AND act.assignment_action_id = fai.context1
344 AND fai.archive_type <> 'PA'
345 AND fai.user_entity_id = fue.user_entity_id
346 GROUP BY act.assignment_action_id, pay_gb_eoy_archive.get_parameter
347 (pact.legislative_parameters,'CHAR_ERROR'),
348 act.action_status, fnd_date.date_to_canonical(pact.effective_date)
349 ORDER BY 12;
350 --
351 --
352 level_cnt number; -- required by the generic magtape procedure.
353 -- FUNCTION get_report_request_error
354 -- Function to retrieve the global g_fnd_rep_request_msg which will be
355 -- populated with the fnd message in the event of the P45 report submission
356 -- failing.
357 FUNCTION get_report_request_error RETURN VARCHAR2;
358 --
359 -- PROCEDURE range_cursor
360 -- Procedure which returns a varchar2 defining a SQL Statement to select
361 -- all the people in the business group.
362 -- The archiver uses this cursor to split the people into chunks for parallel
363 -- processing.
364 -- This procedure is used for both the P45 Archive process and the P45 EDI
365 -- process. When called by the P45 Archive process it also archives the
366 -- tax ref. info.
367 PROCEDURE range_cursor (pactid IN NUMBER,
368 sqlstr out nocopy varchar2);
369 --
370 PROCEDURE arch_act_creation(pactid IN NUMBER,
371 stperson IN NUMBER,
372 endperson IN NUMBER,
373 chunk IN NUMBER);
374 --
375 PROCEDURE archinit(p_payroll_action_id IN NUMBER);
376 --
377 PROCEDURE archive_code(p_assactid IN NUMBER, p_effective_date IN DATE);
378 --
379 -- PROCEDURE spawn_reports
380 -- This is the procedure called after P45 archiving has taken place. It is
381 -- called as the magtape hook, but instead of creating a file, it is used to
382 -- spawn the P45 report. It will call the PAY_GB_P45_REPORT_SUBMISSION
383 -- formula to report the report request id and to stop the magtape process.
384 PROCEDURE spawn_reports;
385 --
386 PROCEDURE edi_act_creation(pactid IN NUMBER,
387 stperson IN NUMBER,
388 endperson IN NUMBER,
389 chunk IN NUMBER);
390 --
391 -----------------------------------------------------------------------------
392 -- Name --
393 -- get_p45_formula_id --
394 -- Purpose --
395 -- this function finds the formula id for the validation of the PAYE --
396 -- tax_code element entry value.
397 -----------------------------------------------------------------------------
398 --
399 FUNCTION get_p45_formula_id RETURN NUMBER;
400 -----------------------------------------------------------------------------
401 --
402 --
403 -----------------------------------------------------------------------------
404 --
405 -- Name --
406 -- get_student_loan_flag --
407 -- Purpose --
408 -- this function finds if the employee has a Student Loan effective at --
409 -- the time employment ceases. Returns 'Y' if 'End Date' is not prior --
410 -- or equal to the termination date. --
411 -----------------------------------------------------------------------------
412 --
413 FUNCTION get_student_loan_flag (p_assignment_id in NUMBER,
414 p_termination_date in DATE,
415 p_session_date in DATE) RETURN VARCHAR2;
416 --
417 --
418 -----------------------------------------------------------------------------
419
420 procedure get_data(X_PERSON_ID in number,
421 X_SESSION_DATE in date,
422 X_ADDRESS_LINE1 in out nocopy varchar2,
423 X_ADDRESS_LINE2 in out nocopy varchar2,
424 X_ADDRESS_LINE3 in out nocopy varchar2,
425 X_TOWN_OR_CITY in out nocopy varchar2,
426 X_REGION_1 in out nocopy varchar2,
427 X_COUNTRY in out nocopy varchar2,
428 X_POSTAL_CODE in out nocopy varchar2,
429 X_ASSIGNMENT_ID in number,
430 X_ASSIGNMENT_ACTION_ID in out nocopy number,
431 X_ASSIGNMENT_END_DATE in date,
432 X_DATE_EARNED in out nocopy date,
433 X_PAYROLL_ACTION_ID in out nocopy number,
434 X_TRANSFER_DATE in date default
435 hr_general.end_of_time);
436
437 procedure get_data(X_PERSON_ID in number,
438 X_SESSION_DATE in date,
439 X_ADDRESS_LINE1 in out nocopy varchar2,
440 X_ADDRESS_LINE2 in out nocopy varchar2,
441 X_ADDRESS_LINE3 in out nocopy varchar2,
442 X_TOWN_OR_CITY in out nocopy varchar2,
443 X_REGION_1 in out nocopy varchar2,
444 X_COUNTRY in out nocopy varchar2,
445 X_POSTAL_CODE in out nocopy varchar2,
446 X_ASSIGNMENT_ID in number,
447 X_ASSIGNMENT_ACTION_ID in out nocopy number,
448 X_ASSIGNMENT_END_DATE in date,
449 X_DATE_EARNED in out nocopy date,
450 X_PAYROLL_ACTION_ID in out nocopy number,
451 X_EMPLOYER_NAME in out nocopy varchar2,
452 X_EMPLOYER_ADDRESS in out nocopy varchar2,
453 X_TRANSFER_DATE in date default
454 hr_general.end_of_time);
455
456 procedure get_form_query_data(X_ASSIGNMENT_ID in number,
457 X_LAST_NAME in out nocopy varchar2,
458 X_TITLE in out nocopy varchar2,
459 X_FIRST_NAME in out nocopy varchar2,
460 X_NATIONAL_IDENTIFIER in out nocopy varchar2,
461 X_PERSON_ID in out nocopy number,
462 X_ACTUAL_TERMINATION_DATE in out nocopy date,
463 X_DECEASED_FLAG in out nocopy varchar2,
464 X_ASSIGNMENT_NUMBER in out nocopy varchar2,
465 X_PAYROLL_ID in out nocopy number,
466 X_ORGANIZATION_ID in out nocopy number,
467 X_ORG_NAME in out nocopy varchar2,
468 X_DATE_OF_BIRTH in out nocopy date, /*P45 A4 2008/09*/
469 X_SEX in out nocopy varchar2); /*P45 A4 2008/09*/
470
471 procedure pop_term_asg_from_archive(X_ASSIGNMENT_ACTION_ID in number,
472 X_NI1 in out nocopy varchar2,
473 X_NI2 in out nocopy varchar2,
474 X_NI3 in out nocopy varchar2,
475 X_NI4 in out nocopy varchar2,
476 X_NI5 in out nocopy varchar2,
477 X_NI6 in out nocopy varchar2,
478 X_NI7 in out nocopy varchar2,
479 X_NI8 in out nocopy varchar2,
480 X_NI9 in out nocopy varchar2,
481 X_LAST_NAME in out nocopy varchar2,
482 X_TITLE in out nocopy varchar2,
483 X_FIRST_NAME in out nocopy varchar2,
484 X_DATE_OF_LEAVING_DD in out nocopy varchar2,
485 X_DATE_OF_LEAVING_MM in out nocopy varchar2,
486 X_DATE_OF_LEAVING_YY in out nocopy varchar2,
487 X_TAX_CODE_AT_LEAVING in out nocopy varchar2,
488 X_WK1_OR_MTH1 in out nocopy varchar2,
489 X_WEEK_NO in out nocopy varchar2,
490 X_MONTH_NO in out nocopy varchar2,
491 X_PAY_TD_POUNDS in out nocopy number,
492 X_PAY_TD_PENCE in out nocopy number,
493 X_TAX_TD_POUNDS in out nocopy number,
494 X_TAX_TD_PENCE in out nocopy number,
495 X_PAY_IN_EMP_POUNDS in out nocopy number,
496 X_PAY_IN_EMP_PENCE in out nocopy number,
497 X_TAX_IN_EMP_POUNDS in out nocopy number,
498 X_TAX_IN_EMP_PENCE in out nocopy number,
499 X_ASSIGNMENT_NUMBER in out nocopy varchar2,
500 X_ORG_NAME in out nocopy varchar2,
501 X_ADDRESS_LINE1 in out nocopy varchar2,
502 X_ADDRESS_LINE2 in out nocopy varchar2,
503 X_ADDRESS_LINE3 in out nocopy varchar2,
504 X_TOWN_OR_CITY in out nocopy varchar2,
505 X_REGION_1 in out nocopy varchar2,
506 X_POSTAL_CODE in out nocopy varchar2,
507 X_DECEASED_FLAG in out nocopy varchar2,
508 X_ISSUE_DATE in out nocopy varchar2,
509 X_TAX_REF_TRANSFER in out nocopy varchar2,
510 X_STUDENT_LOAN_FLAG in out nocopy varchar2,
511 X_COUNTRY in out nocopy varchar2,
512 X_DATE_OF_BIRTH_DD in out nocopy varchar2, /* Start P45 A4 2008/09*/
513 X_DATE_OF_BIRTH_MM in out nocopy varchar2,
514 X_DATE_OF_BIRTH_YY in out nocopy varchar2,
515 X_SEX_M in out nocopy varchar2,
516 X_SEX_F in out nocopy varchar2 /* End P45 A4 2008/09*/);
517
518 Procedure pop_term_pact_from_archive (X_PAYROLL_ACTION_ID in number,
519 X_EMPLOYER_NAME in out nocopy varchar2,
520 X_EMPLOYER_ADDRESS in out nocopy varchar2);
521 --------------------------------------------------------------------------
522 -- name get_p45_asg_action_id
523 -- Purpose
524 -- Get P45 Assignment Action id, Issue Date
525 -- for the given Assignment ID
526 --------------------------------------------------------------------------
527 PROCEDURE get_p45_asg_action_id(p_assignment_id in number,
528 p_assignment_action_id out nocopy number,
529 p_issue_date out nocopy date,
530 p_action_sequence out nocopy number
531 );
532 --------------------------------------------------------------------------
533 -- name get_p45_eit_manual_issue_dt
534 -- purpose
535 -- Get the P45 Manual Issue date from Extra Info. table
536 -- for the given Assignment ID
537 --------------------------------------------------------------------------
538 FUNCTION get_p45_eit_manual_issue_dt(p_assignment_id in number) RETURN DATE;
539
540 --------------------------------------------------------------------------
541 -- name get_p45_agg_asg_action_id
542 -- Get the Aggregated Assignment Id, Assignment Action id,
543 -- Final Payment Date for which the P45 been issued
544 --------------------------------------------------------------------------
545 PROCEDURE get_p45_agg_asg_action_id(p_assignment_id in number,
546 p_agg_assignment_id out nocopy number,
547 p_final_payment_date out nocopy date,
548 p_p45_issue_date out nocopy date,
549 p_p45_agg_asg_action_id out nocopy number
550 );
551
552 --------------------------------------------------------------------------
553 -- name return_p45_issued_flag
554 --
555 -- Purpose --
556 -- this function returns whether the P45 is issued or not for the given --
557 -- assignment (Y-Yes/N-No)
558 -----------------------------------------------------------------------------
559 FUNCTION return_p45_issued_flag(p_assignment_id in number) RETURN VARCHAR2;
560
561 -- Bug 7028893.Added function PAYE_RETURN_P45_ISSUED_FLAG.
562 --------------------------------------------------------------------------
563 -- FUNCTION paye_return_p45_issued_flag
564 -- DESCRIPTION return the P45 issued status for the given assignment (Y-Yes/N-No)
565 --------------------------------------------------------------------------
566 FUNCTION paye_return_p45_issued_flag(p_assignment_id in number,p_payroll_action_id in number) RETURN VARCHAR2;
567
568 -- Bug 7601088.Added function PAYE_SYNC_P45_ISSUED_FLAG.
569 --------------------------------------------------------------------------
570 -- FUNCTION PAYE_SYNC_P45_ISSUED_FLAG
571 -- DESCRIPTION return the P45 issued status for the given assignment (Y-Yes/N-No)
572 --------------------------------------------------------------------------
573 FUNCTION PAYE_SYNC_P45_ISSUED_FLAG(p_assignment_id in number,p_effective_date in date) RETURN VARCHAR2;
574
575 END PAY_P45_PKG;
[Home] [Help]