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