1 PACKAGE pay_no_payproc AS
2 /* $Header: pynopproc.pkh 120.0.12000000.1 2007/01/17 23:09:45 appldev noship $ */
3
4 level_cnt NUMBER;
5
6 --
7 -- PROCEDURE range_cursor
8 -- Procedure which stamps the payroll action with the PAYROLL_ID (if
9 -- supplied), then returns a varchar2 defining a SQL Stateent to select
10 -- all the people in the business group.
11 -- The archiver uses this cursor to split the people into chunks for parallel
12 -- processing.
13 --
14 -- to return parameter values from legislative parameters in pay_payroll_actions
15 --
16
17 PROCEDURE range_cursor(
18 p_payroll_action_id IN NUMBER,
19 p_sqlstr OUT NOCOPY VARCHAR2);
20
21 PROCEDURE assignment_action_code(
22 p_payroll_action_id IN NUMBER,
23 p_start_person_id IN NUMBER,
24 p_end_person_id IN NUMBER,
25 p_chunk_number IN NUMBER);
26
27
28 CURSOR CSR_NO_PP_HEADER IS
29 SELECT
30 'CREATION_DATE=P'
31 ,to_char(ppa.effective_date, 'YYYYMMDD')
32 ,'PAYMENT_DATE=P'
33 ,to_char(fnd_date.canonical_to_date(PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'PAYMENT_DATE')), 'YYYYMMDD')
34 ,'LEGAL_EMPLOYER=P'
35 , to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'LEGAL_EMPLOYER'))
36 ,'AH_SEQ_NO=P'
37 ,to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'AH_SEQUENCE_NUMBER'))
38 ,'DIVISION=P'
39 ,PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'DIVISION')
40 ,'PASSWORD=P'
41 ,PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'PASSWORD')
42 ,'NEW_PASSWORD=P'
43 ,PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'NEW_PASSWORD')
44 ,'SEQ_CONTROL=P'
45 ,to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'SEQUENCE_CONTROL'))
46 ,'MAGNETIC_FILE_NAME=P'
47 ,ppa.magnetic_file_name
48 ,'REPORT_FILE_NAME=P'
49 ,ppa.report_file_name
50 ,'DATE_EARNED=C'
51 ,to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
52 ,'ORG_PAY_METHOD_ID=C'
53 ,ppp.org_payment_method_id
54 ,'BUSINESS_GROUP_ID=C'
55 ,ppa.business_group_id
56 ,'PAYROLL_ID=C'
57 ,paf.payroll_id
58 ,'PAYROLL_ACTION_ID=C'
59 ,ppa.payroll_action_id
60
61 FROM pay_payroll_actions ppa,
62 pay_assignment_actions paa,
63 per_all_assignments_f paf,
64 pay_pre_payments ppp
65
66 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
67 and ppp.pre_payment_id = paa.pre_payment_id
68 and ppp.value > 0
69 and paa.payroll_action_id = ppa.payroll_action_id
70 and paa.assignment_id = paf.assignment_id
71 and rownum < 2;
72
73
74 CURSOR CSR_NO_PP_BODY IS
75
76 SELECT 'AMOUNT=P'
77 ,ppp.value
78 ,'EMPLOYEE_NUMBER=P'
79 ,pef.EMPLOYEE_NUMBER
80 ,'FIRST_NAME=P'
81 , substr(pef.first_name,1,35)
82 ,'LAST_NAME=P'
83 ,substr(pef.last_name,1,35)
84 ,'ORG_ACCOUNT_NO=P'
85 ,pea.segment6
86 ,'EXTERNAL_ACCOUNT=P'
87 ,PAY_NO_PAYPROC_UTILITY.get_account_no(ppp.personal_payment_method_id,paf.payroll_id,ppa.effective_date)
88 ,'MASS_OR_INVOICE_F=P'
89 ,PAY_NO_PAYPROC_UTILITY.get_payment_invoice_or_mass(ppp.personal_payment_method_id,paf.payroll_id,ppa.effective_date) mass_or_invoice
90 ,'ASSIGNMENT_ID=C' , paf.assignment_id
91 ,'BUSINESS_GROUP_ID=C' , paf.business_group_id
92 ,'PER_PAY_METHOD_ID=C' , NVL(ppp.personal_payment_method_id,PAY_NO_PAYPROC_UTILITY.get_payment_method_id(paf.payroll_id,ppa.effective_date))
93 ,'ORG_PAY_METHOD_ID=C' , ppp.org_payment_method_id
94 ,'DATE_EARNED=C' , to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
95 ,'PAYROLL_ID=C' , paf.payroll_id
96 ,'PAYROLL_ACTION_ID=C' , ppa.payroll_action_id
97 ,'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id
98
99 FROM pay_assignment_actions paa,
100 per_all_assignments_f paf,
101 pay_payroll_actions ppa,
102 pay_pre_payments ppp,
103 pay_org_payment_methods_f pop,
104 per_all_people_f pef,
105 pay_external_accounts pea,
106 hr_soft_coding_keyflex hsk,
107 pay_payment_types ppt
108
109 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
110 and ppp.pre_payment_id = paa.pre_payment_id
111 and ppp.value > 0
112 and paa.payroll_action_id = ppa.payroll_action_id
113 and pop.org_payment_method_id = ppp.org_payment_method_id
114 and ppt.payment_type_id= pop.payment_type_id
115 and ( ppt.payment_type_name like 'NO Money Order' or ppt.category in ('MT'))
116 and pop.external_account_id = pea.external_account_id
117 and paa.assignment_id = paf.assignment_id
118 and hsk.SOFT_CODING_KEYFLEX_ID = paf.SOFT_CODING_KEYFLEX_ID
119 and hsk.enabled_flag = 'Y'
120 and hsk.segment2 in (
121 select hoi2.org_information1
122 from HR_ORGANIZATION_UNITS o1
123 , HR_ORGANIZATION_INFORMATION hoi1
124 , HR_ORGANIZATION_INFORMATION hoi2
125 WHERE o1.business_group_id = ppa.business_group_id
126 and hoi1.organization_id = o1.organization_id
127 and hoi1.organization_id = to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'LEGAL_EMPLOYER'))
128 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
129 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
130 and hoi1.organization_id = hoi2.organization_id
131 and hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
132 )
133 and paf.person_id = pef.person_id
134 and paf.payroll_id = nvl(ppa.payroll_id,paf.payroll_id )
135 and ppa.effective_date between pop.effective_start_date and pop.effective_end_date
136 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
137 and ppa.effective_date between pef.effective_start_date and pef.effective_end_date
138 ORDER BY mass_or_invoice,paf.organization_id,substr(pef.last_name || ' ' || pef.first_name,1,50);
139 ---------------------------------------------------------------------------------------
140 CURSOR CSR_NO_PP_AUDIT_EMP IS
141
142 SELECT
143 'AMOUNT=P'
144 ,ppp.value
145 ,'EMPLOYEE_NUMBER=P'
146 ,pap.EMPLOYEE_NUMBER
147 ,'FIRST_NAME=P'
148 , substr(pap.first_name,1,35)
149 ,'LAST_NAME=P'
150 ,substr(pap.last_name,1,35)
151 FROM pay_assignment_actions act,
152 per_all_assignments_f asg,
153 pay_payroll_actions pa2,
154 pay_payroll_actions pa1,
155 pay_pre_payments ppp,
156 pay_org_payment_methods_f OPM,
157 per_all_people_f pap,
158 hr_soft_coding_keyflex hsk,
159 pay_payment_types ppt
160
161 WHERE pa1.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
162 AND pa2.payroll_id = NVL(to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'PAYROLL_ID')),pa2.payroll_id)
163 AND pa2.effective_date <= pa1.effective_date
164 AND pa2.action_type IN ('U','P') -- Prepayments or Quickpay Prepayments
165 AND act.payroll_action_id = pa2.payroll_action_id
166 AND act.action_status = 'C'
167 AND asg.assignment_id = act.assignment_id
168 AND pa1.business_group_id = asg.business_group_id
169 AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
170 AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
171 AND pa1.effective_date between opm.effective_start_date and opm.effective_end_date
172 AND pap.person_id = asg.person_id
173 AND ppp.assignment_action_id = act.assignment_action_id
174 AND ppp.org_payment_method_id = opm.org_payment_method_id
175 AND ppt.payment_type_id= opm.payment_type_id
176 AND ( ppt.payment_type_name like 'NO Money Order' or ppt.category in ('MT'))
177 AND (ppt.category not in ('MT') and not exists ( select '1'
178 FROM per_addresses pad
179 WHERE pad.person_id = asg.person_id
180 and pad.PRIMARY_FLAG ='Y')
181 )
182 AND (to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'ASSIGNMENT_SET_ID')) IS NULL
183 OR EXISTS ( SELECT ''
184 FROM hr_assignment_set_amendments hr_asg
185 WHERE hr_asg.assignment_set_id = to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'ASSIGNMENT_SET_ID'))
186 AND hr_asg.assignment_id = asg.assignment_id
187 ))
188 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
189 FROM pay_action_interlocks pai1,
190 pay_assignment_actions act2,
191 pay_payroll_actions appa
192 WHERE pai1.locked_action_id = act.assignment_action_id
193 AND act2.assignment_action_id = pai1.locking_action_id
194 AND act2.payroll_action_id = appa.payroll_action_id
195 AND appa.action_type = 'X'
196 AND appa.report_type = 'NO_PP')
197
198 and hsk.SOFT_CODING_KEYFLEX_ID = asg.SOFT_CODING_KEYFLEX_ID
199 and hsk.enabled_flag = 'Y'
200 and hsk.segment2 in (
201 select hoi2.org_information1
202 from HR_ORGANIZATION_UNITS o1
203 , HR_ORGANIZATION_INFORMATION hoi1
204 , HR_ORGANIZATION_INFORMATION hoi2
205 WHERE o1.business_group_id = pa1.business_group_id
206 and hoi1.organization_id = o1.organization_id
207 and hoi1.organization_id = to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'LEGAL_EMPLOYER'))
208 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
209 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
210 and hoi1.organization_id = hoi2.organization_id
211 and hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
212 )
213 ORDER BY substr(pap.last_name || ' ' || pap.first_name,1,50);
214
215
216 ------------------------------------------------------------------------------------------
217
218 -- Added new select for bug fix 4253729
219 CURSOR CSR_NO_PP_AUDIT_EMP1 IS
220
221 SELECT
222 'AMOUNT=P'
223 ,ppp.value
224 ,'EMPLOYEE_NUMBER=P'
225 ,pap.EMPLOYEE_NUMBER
226 ,'FIRST_NAME=P'
227 , substr(pap.first_name,1,35)
228 ,'LAST_NAME=P'
229 ,substr(pap.last_name,1,35)
230 FROM pay_assignment_actions act,
231 per_all_assignments_f asg,
232 pay_payroll_actions pa2,
233 pay_payroll_actions pa1,
234 pay_pre_payments ppp,
235 pay_org_payment_methods_f OPM,
236 per_all_people_f pap,
237 hr_soft_coding_keyflex hsk,
238 pay_payment_types ppt
239 WHERE pa1.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
240 AND pa2.payroll_id = NVL(to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'PAYROLL_ID')),pa2.payroll_id)
241 AND pa2.effective_date <= pa1.effective_date
242 AND pa2.action_type IN ('U','P') -- Prepayments or Quickpay Prepayments
243 AND act.payroll_action_id = pa2.payroll_action_id
244 AND act.action_status = 'C'
245 AND asg.assignment_id = act.assignment_id
246 AND pa1.business_group_id = asg.business_group_id
247 AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
248 AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
249 AND pa1.effective_date between opm.effective_start_date and opm.effective_end_date
250 AND pap.person_id = asg.person_id
251 AND ppp.assignment_action_id = act.assignment_action_id
252 AND ppp.org_payment_method_id = opm.org_payment_method_id
253 AND ppt.payment_type_id= opm.payment_type_id
254 AND ( ppt.category not in ('MT') and exists ( select '1' FROM
255 pay_org_payment_methods_f opm1
256 ,pay_payment_types ppt1
257 ,pay_pre_payments ppp1
258 WHERE ppp1.assignment_action_id = act.assignment_action_id--2023513440
259 AND ppp1.org_payment_method_id = opm1.org_payment_method_id
260 AND ppt1.payment_type_id= opm1.payment_type_id
261 AND ppt1.category in ('MT')))
262 AND (to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'ASSIGNMENT_SET_ID')) IS NULL
263 OR EXISTS ( SELECT ''
264 FROM hr_assignment_set_amendments hr_asg
265 WHERE hr_asg.assignment_set_id = to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'ASSIGNMENT_SET_ID'))
266 AND hr_asg.assignment_id = asg.assignment_id
267 ))
268 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
269 FROM pay_action_interlocks pai1,
270 pay_assignment_actions act2,
271 pay_payroll_actions appa
272 WHERE pai1.locked_action_id = act.assignment_action_id
273 AND act2.assignment_action_id = pai1.locking_action_id
274 AND act2.payroll_action_id = appa.payroll_action_id
275 AND appa.action_type = 'X'
276 AND appa.report_type = 'NO_PP')
277 and hsk.SOFT_CODING_KEYFLEX_ID = asg.SOFT_CODING_KEYFLEX_ID
278 and hsk.enabled_flag = 'Y'
279 and hsk.segment2 in (
280 select hoi2.org_information1
281 from HR_ORGANIZATION_UNITS o1
282 , HR_ORGANIZATION_INFORMATION hoi1
283 , HR_ORGANIZATION_INFORMATION hoi2
284 WHERE o1.business_group_id = pa1.business_group_id
285 and hoi1.organization_id = o1.organization_id
286 and hoi1.organization_id = to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(pa1.payroll_action_id,'LEGAL_EMPLOYER'))
287 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
288 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
289 and hoi1.organization_id = hoi2.organization_id
290 and hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS' )
291 ORDER BY substr(pap.last_name || ' ' || pap.first_name,1,50);
292
293
294
295
296 ----------------------------------------------------------------------------------------
297
298 CURSOR CSR_NO_PP_AUDIT IS
299
300 SELECT 'SUM_AMOUNT=P'
301 ,sum(ppp.value)
302 ,'ORG_ACCOUNT_NO=P'
303 ,pea.segment6
304
305 FROM pay_assignment_actions paa,
306 per_all_assignments_f paf,
307 pay_payroll_actions ppa,
308 pay_pre_payments ppp,
309 pay_org_payment_methods_f pop,
310 per_all_people_f pef,
311 per_addresses pad,
312 pay_external_accounts pea,
313 hr_soft_coding_keyflex hsk,
314 pay_payment_types ppt
315
319 and paa.payroll_action_id = ppa.payroll_action_id
316 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
317 and ppp.pre_payment_id = paa.pre_payment_id
318 and ppp.value > 0
320 and pop.org_payment_method_id = ppp.org_payment_method_id
321 and ppt.payment_type_id= pop.payment_type_id
322 and ( ppt.payment_type_name like 'NO Money Order' or ppt.category in ('MT'))
323 and pop.external_account_id = pea.external_account_id
324 and paa.assignment_id = paf.assignment_id
325 and hsk.SOFT_CODING_KEYFLEX_ID = paf.SOFT_CODING_KEYFLEX_ID
326 and hsk.segment2 in (
327 select hoi2.org_information1
328 from HR_ORGANIZATION_UNITS o1
329 , HR_ORGANIZATION_INFORMATION hoi1
330 , HR_ORGANIZATION_INFORMATION hoi2
331 WHERE o1.business_group_id = ppa.business_group_id
332 and hoi1.organization_id = o1.organization_id
333 and hoi1.organization_id = to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(ppa.payroll_action_id,'LEGAL_EMPLOYER'))
334 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
335 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
336 and hoi1.organization_id = hoi2.organization_id
337 and hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
338 )
339 and paf.person_id = pef.person_id
340 and pad.person_id (+) = pef.person_id
341 and ( ppt.category in ('MT') or pad.primary_flag = 'Y')
342 and paf.payroll_id = nvl(ppa.payroll_id,paf.payroll_id )
343 and ppa.effective_date between pop.effective_start_date and pop.effective_end_date
344 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
345 and ppa.effective_date between pef.effective_start_date and pef.effective_end_date
346 group by pea.segment6;
347 ----------------------------------------------------------------------------------------
348
349 FUNCTION get_application_header (
350 p_transaction_date in varchar2
351 ,p_sequence_number in number
352 ,p_write_text1 OUT NOCOPY VARCHAR2 ) return varchar2;
353
354 FUNCTION get_betfor00_record (
355 p_Date_Earned IN DATE
356 ,p_payment_method_id IN number
357 ,p_business_group_id IN number
358 ,p_payroll_id IN number
359 ,p_payroll_action_id IN number
360 ,p_production_date IN VARCHAR2
361 ,p_seq_control IN VARCHAR2
362 ,p_write_text1 OUT NOCOPY VARCHAR2
363 ,p_write_text2 OUT NOCOPY VARCHAR2
364 ,p_write_text3 OUT NOCOPY VARCHAR2
365 ,p_write_text4 OUT NOCOPY VARCHAR2
366 ,p_division in varchar2
367 ,p_password in varchar2
368 ,p_new_password in varchar2) return varchar2;
369
370 FUNCTION get_betfor21_mass_record (
371 p_assignment_id IN number
372 ,p_business_group_id IN number
373 ,p_per_pay_method_id IN number
374 ,p_org_pay_method_id IN number
375 ,p_date_earned IN date
376 ,p_payroll_id IN number
377 ,p_payroll_action_id IN number
378 ,p_assignment_action_id IN number
379 ,p_org_account_number IN varchar2
380 ,p_payment_date IN VARCHAR2
381 ,p_seq_control IN VARCHAR2
382 ,p_enterprise_no IN varchar2
383 ,p_write_text1 OUT NOCOPY VARCHAR2
384 ,p_write_text2 OUT NOCOPY VARCHAR2
385 ,p_write_text3 OUT NOCOPY VARCHAR2
386 ,p_write_text4 OUT NOCOPY VARCHAR2 ) return varchar2;
387
388 FUNCTION get_betfor21_invoice_record (
389 p_assignment_id IN number
390 ,p_business_group_id IN number
391 ,p_per_pay_method_id IN number
392 ,p_org_pay_method_id IN number
393 ,p_date_earned IN date
394 ,p_payroll_id IN number
395 ,p_payroll_action_id IN number
396 ,p_assignment_action_id IN number
397 ,p_org_account_number IN varchar2
398 ,p_payment_date IN VARCHAR2
399 ,p_seq_control IN VARCHAR2
400 ,p_enterprise_no IN varchar2
401 ,p_payee_first_name in varchar2
402 ,p_payee_last_name in varchar2
403 ,p_write_text1 OUT NOCOPY VARCHAR2
404 ,p_write_text2 OUT NOCOPY VARCHAR2
405 ,p_write_text3 OUT NOCOPY VARCHAR2
406 ,p_write_text4 OUT NOCOPY VARCHAR2
407 ,p_status OUT NOCOPY VARCHAR2
408 ,p_audit_address OUT NOCOPY VARCHAR2) return varchar2;
409
410 FUNCTION get_betfor23_record (
411 p_assignment_id IN number
412 ,p_business_group_id IN number
413 ,p_per_pay_method_id IN number
414 ,p_org_pay_method_id IN number
415 ,p_date_earned IN date
419 ,p_org_account_number IN varchar2
416 ,p_payroll_id IN number
417 ,p_payroll_action_id IN number
418 ,p_assignment_action_id IN number
420 ,p_amount in varchar2
421 ,p_seq_control IN VARCHAR2
422 ,p_enterprise_no IN varchar2
423 ,p_write_text1 OUT NOCOPY VARCHAR2
424 ,p_write_text2 OUT NOCOPY VARCHAR2
425 ,p_write_text3 OUT NOCOPY VARCHAR2
426 ,p_write_text4 OUT NOCOPY VARCHAR2 ) return varchar2;
427
428 FUNCTION get_betfor22_record (
429 p_assignment_id IN number
430 ,p_business_group_id IN number
431 ,p_per_pay_method_id IN number
432 ,p_org_pay_method_id IN number
433 ,p_date_earned IN date
434 ,p_payroll_id IN number
435 ,p_payroll_action_id IN number
436 ,p_assignment_action_id IN number
437 ,p_org_account_number IN varchar2
438 ,p_last_name in varchar2
439 ,p_first_name in varchar2
440 ,p_amount in varchar2
441 ,p_serial_number in varchar2
442 ,p_seq_control IN VARCHAR2
443 ,p_enterprise_no IN varchar2
444 ,p_write_text1 OUT NOCOPY VARCHAR2
445 ,p_write_text2 OUT NOCOPY VARCHAR2
446 ,p_write_text3 OUT NOCOPY VARCHAR2
447 ,p_write_text4 OUT NOCOPY VARCHAR2 ) return varchar2;
448
449 FUNCTION get_betfor99_record (
450 p_Date_Earned IN DATE
451 ,p_payment_method_id IN number
452 ,p_business_group_id IN number
453 ,p_payroll_id IN number
454 ,p_payroll_action_id IN number
455 ,p_production_date IN VARCHAR2
456 ,p_seq_control IN VARCHAR2
457 ,p_write_text1 OUT NOCOPY VARCHAR2
458 ,p_write_text2 OUT NOCOPY VARCHAR2
459 ,p_write_text3 OUT NOCOPY VARCHAR2
460 ,p_write_text4 OUT NOCOPY VARCHAR2
461 ,p_enterprise_no in varchar2
462 ,p_nos_payments in varchar2
463 ,p_nos_records in varchar2) return varchar2;
464
465 FUNCTION get_audit_record(
466 p_assignment_id IN number
467 ,p_business_group_id IN number
468 ,p_per_pay_method_id IN number
469 ,p_org_pay_method_id IN number
470 ,p_date_earned IN date
471 ,p_payroll_id IN number
472 ,p_payroll_action_id IN number
473 ,p_assignment_action_id IN number
474 ,p_type_of_record in varchar2
475 ,p_last_name in varchar2
476 ,p_first_name in varchar2
477 ,p_amount in varchar2
478 ,p_report2_text1 OUT NOCOPY VARCHAR2
479 ,p_ni_number in varchar2) return varchar2;
480
481 FUNCTION update_seq_values (
482 p_payroll_id IN number
483 ,p_ah_seq IN varchar2
484 ,p_seq_control IN VARCHAR2) return varchar2;
485
486 FUNCTION get_next_value(
487 p_sequence varchar2
488 ,p_type varchar2) return varchar2;
489
490 FUNCTION get_legal_emp_name(
491 p_business_group_id IN number
492 ,p_legal_emp_id IN varchar2
493 ,p_legal_emp_name OUT NOCOPY VARCHAR2 ) return varchar2;
494
495 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
496
497 /* ACknowledgement Reply process */
498
499 PROCEDURE upload(
500 errbuf OUT NOCOPY VARCHAR2,
501 retcode OUT NOCOPY NUMBER,
502 p_file_name IN VARCHAR2,
503 p_effective_date IN VARCHAR2,
504 p_business_group_id IN VARCHAR2
505
506 );
507
508 PROCEDURE read_trans_code
509 ( p_process IN VARCHAR2
510 ,p_line IN VARCHAR2
511 ,p_trans_code OUT NOCOPY VARCHAR2
512 );
513
514 PROCEDURE read_lines
515 ( p_process IN VARCHAR2
516 ,p_file_type IN UTL_FILE.file_type
517 ,p_record OUT NOCOPY VARCHAR2
518 );
519
520 PROCEDURE read_record
521 (p_process IN VARCHAR2
522 ,p_line IN VARCHAR2
523 ,p_trans_code IN VARCHAR2
524 ,p_ah_seq_no OUT NOCOPY VARCHAR2
525 ,p_ah_ret_code OUT NOCOPY VARCHAR2
526 ,p_ref_no OUT NOCOPY VARCHAR2
527 ,p_serial_no OUT NOCOPY VARCHAR2
528 ,p_emp_no OUT NOCOPY VARCHAR2
529 ,p_emp_name OUT NOCOPY VARCHAR2
530 ,p_amount OUT NOCOPY VARCHAR2
531 ,p_ret_code_rem OUT NOCOPY VARCHAR2
532 ,p_emp_name_old IN OUT NOCOPY VARCHAR2
536 ,p_rej_pay_no IN OUT NOCOPY VARCHAR2
533 ,p_ah_seq_no_prev IN OUT NOCOPY VARCHAR2
534 ,p_acc_pay_no IN OUT NOCOPY VARCHAR2
535 ,p_acc_pay_amt IN OUT NOCOPY VARCHAR2
537 ,p_rej_pay_amt IN OUT NOCOPY VARCHAR2
538 );
539
540 -- function to get labels of items from a lookup
541 FUNCTION get_lookup_meaning (p_lookup_type varchar2,p_lookup_code varchar2) RETURN VARCHAR2 ;
542
543
544 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
545 end PAY_NO_PAYPROC;
[Home] [Help]