[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_PAYPROC
Source
1 PACKAGE BODY pay_no_payproc AS
2 /* $Header: pynopproc.pkb 120.1.12000000.2 2007/04/05 19:38:18 sugarg noship $ */
3
4 -- Globals
5 l_package CONSTANT VARCHAR2(20):= 'PAY_NO_PAYPROC.';
6
7 /* name of the process , this name is used to do any custom validation, it defaults to NO_ACK */
8
9 g_process CONSTANT VARCHAR2 (10) := 'NO_ACK' ;
10 c_data_exchange_dir CONSTANT VARCHAR2 (30) := 'PER_DATA_EXCHANGE_DIR';
11
12 /* Exception Variables */
13 e_wrong_csr_routine EXCEPTION;
14 e_err_in_csr EXCEPTION;
15 e_invalid_value EXCEPTION;
16 e_record_too_long EXCEPTION;
17
18
19 PRAGMA exception_init (e_invalid_value, -1858);
20
21 /* Global constants */
22 c_warning CONSTANT NUMBER := 1;
23 c_error CONSTANT NUMBER := 2;
24 c_end_of_time CONSTANT DATE := to_date('12/31/4712','MM/DD/YYYY');
25
26
27 --------------------------------------------------------------------------------+
28 -- Range cursor returns the ids of the assignments to be archived
29 --------------------------------------------------------------------------------+
30 PROCEDURE range_cursor(
31 p_payroll_action_id IN NUMBER,
32 p_sqlstr OUT NOCOPY VARCHAR2)
33 IS
34 l_proc_name VARCHAR2(100) ;
35 BEGIN
36 l_proc_name := l_package || 'range_code';
37 hr_utility.set_location(l_proc_name, 10);
38 p_sqlstr := 'SELECT DISTINCT person_id
39 FROM per_all_people_f ppf,
40 pay_payroll_actions ppa
41 WHERE ppa.payroll_action_id = :payroll_action_id
42 AND ppa.business_group_id = ppf.business_group_id
43 ORDER BY ppf.person_id';
44 hr_utility.set_location(l_proc_name, 20);
45 END range_cursor;
46 --
47
48 ----------------------------------------------------------------------------------------------
49
50 --------------------------------------------------------------------------------+
51 -- Creates assignment action id for all the valid person id's in
52 -- the range selected by the Range code.
53 --------------------------------------------------------------------------------+
54 PROCEDURE assignment_action_code(
55 p_payroll_action_id IN NUMBER,
56 p_start_person_id IN NUMBER,
57 p_end_person_id IN NUMBER,
58 p_chunk_number IN NUMBER)
59 IS
60 l_proc_name VARCHAR2(100) ;
61
62
63 -- Bug 5943355 Fix : Changing action_type in pay_payroll_actions from 'Magnetic report' to 'Magnetic transfer'.
64 -- old : appa.action_type = 'X' (Magnetic report)
65 -- new : appa.action_type = 'M' (Magnetic transfer)
66
67 CURSOR csr_asg(p_payroll_action_id NUMBER,
68 p_start_person_id NUMBER,
69 p_end_person_id NUMBER,
70 p_payroll_id NUMBER,
71 p_consolidation_id NUMBER,
72 p_assignment_set_id NUMBER,
73 p_person_id NUMBER) IS
74 SELECT act.assignment_action_id,
75 act.assignment_id,
76 ppp.pre_payment_id
77 FROM pay_assignment_actions act,
78 per_all_assignments_f asg,
79 pay_payroll_actions pa2,
80 pay_payroll_actions pa1,
81 pay_pre_payments ppp,
82 pay_org_payment_methods_f OPM,
83 per_all_people_f pap,
84 hr_soft_coding_keyflex hsk,
85 pay_payment_types ppt
86
87 WHERE pa1.payroll_action_id = p_payroll_action_id
88 AND pa2.payroll_id = NVL(p_payroll_id,pa2.payroll_id)
89 AND pa2.effective_date <= pa1.effective_date
90 AND pa2.action_type IN ('U','P') -- Prepayments or Quickpay Prepayments
91 AND act.payroll_action_id = pa2.payroll_action_id
92 AND act.action_status = 'C'
93 AND asg.assignment_id = act.assignment_id
94 AND pa1.business_group_id = asg.business_group_id
95 AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
96 AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
97 AND pa1.effective_date between opm.effective_start_date and opm.effective_end_date
98 AND pap.person_id = asg.person_id
99 AND pap.person_id between p_start_person_id and p_end_person_id
100 AND ppp.assignment_action_id = act.assignment_action_id
101 AND ppp.org_payment_method_id = opm.org_payment_method_id
102 AND ppt.payment_type_id= opm.payment_type_id
103 AND ( ppt.payment_type_name like 'NO Money Order' or ppt.category in ('MT'))
104 AND pap.person_id = NVL(p_person_id,pap.person_id)
105 AND (ppt.category in ('MT') or exists ( select '1'
106 FROM per_addresses pad
107 WHERE pad.person_id = asg.person_id
108 and pad.PRIMARY_FLAG ='Y')
109 )
110 AND (p_assignment_set_id IS NULL
111 OR EXISTS ( SELECT ''
112 FROM hr_assignment_set_amendments hr_asg
113 WHERE hr_asg.assignment_set_id = p_assignment_set_id
114 AND hr_asg.assignment_id = asg.assignment_id
115 ))
116 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
117 FROM pay_action_interlocks pai1,
118 pay_assignment_actions act2,
119 pay_payroll_actions appa
120 WHERE pai1.locked_action_id = act.assignment_action_id
121 AND act2.assignment_action_id = pai1.locking_action_id
122 AND act2.payroll_action_id = appa.payroll_action_id
123 -- AND appa.action_type = 'X'
124 AND appa.action_type = 'M'
125 AND appa.report_type = 'NO_PP')
126 and hsk.SOFT_CODING_KEYFLEX_ID = asg.SOFT_CODING_KEYFLEX_ID
127 and hsk.enabled_flag = 'Y'
128 and hsk.segment2 in (
129 select hoi2.org_information1
130 from HR_ORGANIZATION_UNITS o1
131 , HR_ORGANIZATION_INFORMATION hoi1
132 , HR_ORGANIZATION_INFORMATION hoi2
133 WHERE o1.business_group_id = pa1.business_group_id
134 and hoi1.organization_id = o1.organization_id
135 and hoi1.organization_id = to_number(PAY_NO_PAYPROC_UTILITY.get_parameter
136 (pa1.payroll_action_id,'LEGAL_EMPLOYER'))
137 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
138 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
139 and hoi1.organization_id = hoi2.organization_id
140 and hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
141 ) ;
142
143
144 l_payroll_id VARCHAR2(15):=NULL;
145 l_consolidation_set VARCHAR2(15):=NULL;
146 l_locking_action_id VARCHAR2(15):=NULL;
147 l_assignment_set_id VARCHAR2(15):=NULL;
148 l_person_id VARCHAR2(15):=NULL;
149
150 BEGIN
151
152 l_proc_name := l_package || 'assignment_action_code';
153 hr_utility.set_location(l_proc_name, 10);
154
155 l_payroll_id := to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(p_payroll_action_id,'PAYROLL_ID'));
156 l_consolidation_set := to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(p_payroll_action_id,'CONSOLIDATION_SET_ID'));
157 l_assignment_set_id :=to_number(PAY_NO_PAYPROC_UTILITY.get_parameter(p_payroll_action_id,'ASSIGNMENT_SET_ID'));
158
159 hr_utility.set_location(l_proc_name, 20);
160
161 FOR rec_asg IN csr_asg(p_payroll_action_id
162 ,p_start_person_id
163 ,p_end_person_id
164 ,l_payroll_id
165 ,l_consolidation_set
166 ,l_assignment_set_id
167 ,l_person_id) LOOP
168
169 SELECT pay_assignment_actions_s.nextval
170 INTO l_locking_action_id
171 FROM dual;
172
173 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
174 assignid => rec_asg.assignment_id,
175 pactid => p_payroll_action_id,
176 chunk => p_chunk_number,
177 greid => NULL,
178 prepayid => rec_asg.pre_payment_id,
179 status => 'U');
180
181 --
182 -- insert the lock on the run action.
183 --
184
185 hr_nonrun_asact.insint(l_locking_action_id
186 , rec_asg.assignment_action_id);
187 --
188
189 END LOOP;
190 hr_utility.set_location(l_proc_name, 40);
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 hr_utility.set_location('Error in assignment action code ',100);
195 RAISE;
196 END assignment_action_code;
197
198 -----------------------------------------------------------------------------------------------------------------
199
200 FUNCTION get_application_header (
201 p_transaction_date in varchar2
202 ,p_sequence_number in number
203 ,p_write_text1 OUT NOCOPY VARCHAR2 ) return varchar2 IS
204
205
206 l_text varchar2(50);
207
208 begin
209
210 l_text :=get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','AH')||'2'||'00'||get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','TBII')|| to_char(to_date(p_transaction_date,'YYYYMMDD'),'MMDD') || lpad(p_sequence_number,6,'0')||
211 rpad(' ',8,' ')||rpad(' ',11,' ')||'04';
212
213 p_write_text1 := l_text;
214
215 RETURN '1';
216 end get_application_header;
217
218 ----------------------------------------------------------------------------
219
220 FUNCTION get_betfor00_record (
221 p_Date_Earned IN DATE
222 ,p_payment_method_id IN number
223 ,p_business_group_id IN number
224 ,p_payroll_id IN number
225 ,p_payroll_action_id IN number
226 ,p_production_date IN VARCHAR2
227 ,p_seq_control IN VARCHAR2
228 ,p_write_text1 OUT NOCOPY VARCHAR2
229 ,p_write_text2 OUT NOCOPY VARCHAR2
230 ,p_write_text3 OUT NOCOPY VARCHAR2
231 ,p_write_text4 OUT NOCOPY VARCHAR2
232 ,p_division in varchar2
233 ,p_password in varchar2
234 ,p_new_password in varchar2) return varchar2 IS
235
236 cursor c_get_enterprise_number is
237 select pop.pmeth_information1
238 from pay_org_payment_methods_f pop,
239 pay_payment_types ppt,
240 pay_org_pay_method_usages_f ppu,
241 pay_payroll_actions ppa
242
243 where ppt.payment_type_id=pop.payment_type_id
244 and ppt.category in ('MT')
245 and ppu.payroll_id=p_payroll_id
246 and pop.org_payment_method_id=ppu.org_payment_method_id
247 and ppa.effective_date between pop.effective_start_date and pop.effective_end_date
248 and ppa.payroll_action_id=p_payroll_action_id
249 and rownum<2;
250
251
252 l_division varchar2(20) := NULL;
253 l_password varchar2(20) := NULL;
254 l_new_password varchar(20) := NULL;
255
256 l_enterprise_number number(11);
257
258 l_text1 varchar2(100);
259 l_text2 varchar2(100);
260 l_text3 varchar2(100);
261 l_text4 varchar2(100);
262
263 l_return_val varchar2(15);
264
265 begin
266
267
268 l_division := UPPER(p_division);
269 l_password := UPPER(p_password);
270 l_new_password := UPPER(p_new_password);
271
272
273 open c_get_enterprise_number;
274 fetch c_get_enterprise_number into l_enterprise_number;
275 close c_get_enterprise_number;
276
277 if l_division is NULL then
278 l_division := ' ';
279 end if;
280
281 if l_password is NULL then
282 l_password := ' ';
283 end if;
284
285 if l_new_password is NULL then
286 l_new_password := ' ';
287 end if;
288
289 l_text1 := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','BF00')||lpad(to_char(l_enterprise_number),11,'0')||rpad(UPPER(l_division),11,' ')||
290 lpad(p_seq_control,4,'0')||rpad(' ',6,' ');
291
292 l_text2 := to_char(to_date(p_production_date,'YYYYMMDD'),'MMDD')||
293 rpad(UPPER(l_password),10,' ')||get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','VER')||rpad(UPPER(l_new_password),10,' ')||rpad(' ',11,' ')||' '||
294 rpad('0',6,'0')||rpad('0',20,'0')||rpad(' ',1,' ')||rpad(' ',7,' ');
295
296 l_text3 := rpad(' ',80,' ');
297
298
299 l_text4:= rpad(' ',56,' ')||lpad(p_payroll_action_id,15,'0')||rpad(' ',9,' ');
300
301 p_write_text1 := l_text1;
302 p_write_text2 := l_text2;
303 p_write_text3 := l_text3;
304 p_write_text4 := l_text4;
305
306 l_return_val := lpad(to_char(l_enterprise_number),11,'0');
307 RETURN l_return_val;
308
309 end get_betfor00_record;
310
311 ---------------------------------------------------------------------------------------------------
312
313 FUNCTION get_betfor99_record (
314 p_Date_Earned IN DATE
315 ,p_payment_method_id IN number
316 ,p_business_group_id IN number
317 ,p_payroll_id IN number
318 ,p_payroll_action_id IN number
319 ,p_production_date IN VARCHAR2
320 ,p_seq_control IN VARCHAR2
321 ,p_write_text1 OUT NOCOPY VARCHAR2
322 ,p_write_text2 OUT NOCOPY VARCHAR2
323 ,p_write_text3 OUT NOCOPY VARCHAR2
324 ,p_write_text4 OUT NOCOPY VARCHAR2
325 ,p_enterprise_no in varchar2
326 ,p_nos_payments in varchar2
327 ,p_nos_records in varchar2) return varchar2 IS
328
329
330
331 l_text1 varchar2(100);
332 l_text2 varchar2(100);
333 l_text3 varchar2(100);
334 l_text4 varchar2(100);
335
336 begin
337
338
339 update pay_payroll_actions set action_type='M' where payroll_action_id=p_payroll_action_id;
340
341
342 l_text1 := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','BF99')||lpad(to_char(p_enterprise_no),11,'0')||rpad(' ',11,' ')||
343 lpad(p_seq_control,4,'0')||rpad(' ',6,' ');
344
345 -- Modified for bug fix 4253690
346 l_text2 := to_char(to_date(p_production_date,'YYYYMMDD'),'MMDD')||lpad(p_nos_payments,4,'0')
347 ||lpad('0',15,'0')||lpad(p_nos_records,5,'0')||rpad(' ',52,' ');
348
349 l_text3 := rpad(' ',80,' ');
350
351
352 l_text4:= rpad(' ',31,' ')||rpad(' ',4,' ')||rpad(' ',1,' ')||rpad(' ',1,' ')||rpad(' ',1,' ')
353 ||rpad(' ',18,' ')||lpad(get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','VERSW'),16,' ')||rpad(' ',8,' ');
354
355 p_write_text1 := l_text1;
356 p_write_text2 := l_text2;
357 p_write_text3 := l_text3;
358 p_write_text4 := l_text4;
359
360 RETURN '1';
361 end get_betfor99_record;
362
363 ------------------------------------------------------------------------------------------------
364 FUNCTION get_betfor21_mass_record (
365 p_assignment_id IN number
366 ,p_business_group_id IN number
367 ,p_per_pay_method_id IN number
368 ,p_org_pay_method_id IN number
372 ,p_assignment_action_id IN number
369 ,p_date_earned IN date
370 ,p_payroll_id IN number
371 ,p_payroll_action_id IN number
373 ,p_org_account_number IN varchar2
374 ,p_payment_date IN VARCHAR2
375 ,p_seq_control IN VARCHAR2
376 ,p_enterprise_no IN varchar2
377 ,p_write_text1 OUT NOCOPY VARCHAR2
378 ,p_write_text2 OUT NOCOPY VARCHAR2
379 ,p_write_text3 OUT NOCOPY VARCHAR2
380 ,p_write_text4 OUT NOCOPY VARCHAR2 ) return varchar2 IS
381
382
383
384 l_enterprise_number number(11);
385
386 l_text1 varchar2(100);
387 l_text2 varchar2(100);
388 l_text3 varchar2(100);
389 l_text4 varchar2(100);
390
391 begin
392
393
394 l_text1 := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','BF21')||lpad(to_char(p_enterprise_no),11,'0')||lpad(to_char(p_org_account_number),11,'0')||
395 lpad(p_seq_control,4,'0')||rpad(' ',6,' ');
396
397 l_text2 := to_char(to_date(p_payment_date,'YYYYMMDD'),'YYMMDD')||rpad(' ',30,' ')||rpad(' ',1,' ')
398 ||rpad('0',11,'0')||rpad(' ',30,' ')||rpad(' ',2,' ');
399
400 l_text3 := rpad(' ',28,' ')||rpad(' ',30,' ')||rpad('0',4,'0')||rpad(' ',18,' ');
401
402 -- Modified last two fields for bug fix 4253690
403 l_text4 := rpad(' ',8,' ')||rpad('0',15,'0')||'604'||'L'||rpad(' ',1,' ')||rpad('0',15,'0')
404 ||rpad(' ',5,' ')||rpad('0',6,'0')||rpad('0',6,'0')||rpad(' ',1,' ')||rpad(' ',9,' ')||lpad('0',10,'0');
405
406 p_write_text1 := l_text1;
407 p_write_text2 := l_text2;
408 p_write_text3 := l_text3;
409 p_write_text4 := l_text4;
410
411 RETURN '1';
412 end get_betfor21_mass_record;
413
414 ----------------------------------------------------------------------------
415 FUNCTION get_next_value(p_sequence varchar2,p_type varchar2) return varchar2
416 is
417
418 l_next_value varchar2(50);
419 l_number number(10);
420
421 begin
422
423 l_number := to_number(p_sequence);
424
425 if l_number = 9999 and p_type = 'SERIAL_NUM' then
426 hr_utility.set_message (801, 'PAY_376830_NO_SERIAL_NUM_OVER');
427 hr_utility.raise_error;
428 return '-1';
429 end if;
430
431 if l_number = 9999 and p_type = 'SEQ_CONTROL' then
432 l_number := -1;
433 end if;
434
435 if l_number = 999999 and p_type = 'SEQ_NO' then
436 l_number := 0;
437 end if;
438
439 l_number := l_number+1;
440 l_next_value := to_char(l_number);
441
442 RETURN l_next_value;
443 end get_next_value;
444
445 ---------------------------------------------------------------------------
446 FUNCTION get_betfor22_record (
447 p_assignment_id IN number
448 ,p_business_group_id IN number
449 ,p_per_pay_method_id IN number
450 ,p_org_pay_method_id IN number
451 ,p_date_earned IN date
452 ,p_payroll_id IN number
453 ,p_payroll_action_id IN number
454 ,p_assignment_action_id IN number
455 ,p_org_account_number IN varchar2
456 ,p_last_name in varchar2
457 ,p_first_name in varchar2
458 ,p_amount in varchar2
459 ,p_serial_number in varchar2
460 ,p_seq_control IN VARCHAR2
461 ,p_enterprise_no IN varchar2
462 ,p_write_text1 OUT NOCOPY VARCHAR2
463 ,p_write_text2 OUT NOCOPY VARCHAR2
464 ,p_write_text3 OUT NOCOPY VARCHAR2
465 ,p_write_text4 OUT NOCOPY VARCHAR2 ) return varchar2 is
466
467
468
469 l_enterprise_number number(11);
470
471 l_text1 varchar2(100);
472 l_text2 varchar2(100);
473 l_text3 varchar2(100);
474 l_text4 varchar2(100);
475
476
477 l_per_account_num PAY_EXTERNAL_ACCOUNTS.SEGMENT6%TYPE;
478
479 l_payee_name varchar2(100);
480 l_amount number(15);
481
482 cursor get_per_org_account_no is
483 select pea.segment6
484 from pay_external_accounts pea,
485 pay_personal_payment_methods_f ppp
486 where
487 ppp.personal_payment_method_id=p_per_pay_method_id
488 and ppp.external_account_id=pea.external_account_id;
489
490 begin
491
492 open get_per_org_account_no;
493 fetch get_per_org_account_no into l_per_account_num;
494 close get_per_org_account_no;
495
496 l_payee_name := UPPER(substr(p_last_name || ' ' || p_first_name,1,30));
497 l_amount := p_amount*100;
498
499
500 l_text1 := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','BF22')||lpad(to_char(p_enterprise_no),11,'0')||p_org_account_number||
501 lpad(p_seq_control,4,'0')||rpad(' ',6,' ');
502
503 l_text2 := lpad(to_char(l_per_account_num),11,'0')||rpad(l_payee_name,30,' ')||lpad(l_amount,15,'0')
504 ||rpad(' ',1,' ')||rpad(' ',23,' ');
505
506 l_text3 := rpad(' ',80,' ');
507
508 -- Modified for bug fix 4253690
512 p_write_text1 := l_text1;
509 l_text4 := rpad(' ',42,' ')||lpad(p_assignment_action_id,10,'0')||lpad('0',4,'0')||rpad(' ',1,' ')||rpad(' ',23,' ');
510
511
513 p_write_text2 := l_text2;
514 p_write_text3 := l_text3;
515 p_write_text4 := l_text4;
516
517 RETURN '1';
518 end get_betfor22_record;
519
520 ---------------------------------------------------------------------------
521
522 FUNCTION get_betfor21_invoice_record (
523 p_assignment_id IN number
524 ,p_business_group_id IN number
525 ,p_per_pay_method_id IN number
526 ,p_org_pay_method_id IN number
527 ,p_date_earned IN date
528 ,p_payroll_id IN number
529 ,p_payroll_action_id IN number
530 ,p_assignment_action_id IN number
531 ,p_org_account_number IN varchar2
532 ,p_payment_date IN VARCHAR2
533 ,p_seq_control IN VARCHAR2
534 ,p_enterprise_no IN varchar2
535 ,p_payee_first_name in varchar2
536 ,p_payee_last_name in varchar2
537 ,p_write_text1 OUT NOCOPY VARCHAR2
538 ,p_write_text2 OUT NOCOPY VARCHAR2
539 ,p_write_text3 OUT NOCOPY VARCHAR2
540 ,p_write_text4 OUT NOCOPY VARCHAR2
541 ,p_status OUT NOCOPY VARCHAR2
542 ,p_audit_address OUT NOCOPY VARCHAR2) return varchar2 is
543
544 l_enterprise_number number(11);
545
546 l_text1 varchar2(100);
547 l_text2 varchar2(100);
548 l_text3 varchar2(100);
549 l_text4 varchar2(100);
550
551 l_audit_text varchar2(500);
552
553 l_payee_name varchar2(100);
554
555
556 l_address1 per_addresses.ADDRESS_LINE1%TYPE;
557 l_address2 per_addresses.ADDRESS_LINE2%TYPE;
558 l_post_code per_addresses.POSTAL_CODE%TYPE;
559 --Modified for bug fix 4253729
560 l_city VARCHAR2(80); --per_addresses.TOWN_OR_CITY%TYPE;
561
562 --Modified cursor for bug fix 4253729
563 CURSOR csr_get_address(p_assignment_id number)
564 is
565 select ADDRESS_LINE1,ADDRESS_LINE2,POSTAL_CODE --,TOWN_OR_CITY
566 FROM per_addresses pad
567 ,per_all_assignments_f paf
568 ,per_all_people_f pef
569
570 WHERE paf.assignment_id=p_assignment_id
571 and paf.person_id = pef.person_id
572 and pad.person_id= pef.person_id
573 and pad.PRIMARY_FLAG ='Y';
574
575 begin
576
577 p_status := 'OK';
578 open csr_get_address(p_assignment_id);
579 --Modified for bug fix 4253729
580 fetch csr_get_address into l_address1,l_address2,l_post_code ; --,l_city;
581 --Added for bug fix 4253729
582 l_city := substr(get_lookup_meaning ('NO_POSTAL_CODE',l_post_code),6);
583
584 IF csr_get_address%NOTFOUND THEN
585 p_status := 'WARNING';
586
587 p_write_text1 := ' ';
588 p_write_text2 := ' ';
589 p_write_text3 := ' ';
590 p_write_text4 := ' ';
591 p_audit_address := '*';
592
593 RETURN '1';
594 END IF;
595 close csr_get_address;
596
597
598 l_payee_name := UPPER(substr(p_payee_last_name || ' ' || p_payee_first_name,1,30));
599
600 if l_address1=NULL then
601 l_address1 := ' '||' ';
602 end if;
603
604 if l_address2=NULL then
605 l_address2 := ' '||' ';
606 end if;
607
608
609
610
611 l_text1 := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','BF21')||lpad(to_char(p_enterprise_no),11,'0')||p_org_account_number||
612 lpad(p_seq_control,4,'0')||rpad(' ',6,' ');
613
614 l_text2 := to_char(to_date(p_payment_date,'YYYYMMDD'),'YYMMDD')||lpad(to_char(p_assignment_action_id),30,'0')
615 ||rpad(' ',1,' ')||lpad('19',11,'0')||rpad(l_payee_name,30,' ')||
616 rpad(UPPER(substr(rpad(NVL(l_address1,' '),30,' '),1,2)),2,' ');
617
618 l_text3 := rpad(UPPER(SUBSTR(rpad(NVL(l_address1,' '),30,' '),3,28)),28,' ')||rpad(UPPER(NVL(l_address2,' ')),30,' ')||
619 lpad(l_post_code,4,'0')||rpad(UPPER(SUBSTR(rpad(NVL(l_city,' '),26,' '),1,18)),18,' ');
620
621 -- Modified last two fields for bug fix 4253690
622 l_text4 := rpad(UPPER(SUBSTR(rpad(NVL(l_city,' '),26,' '),19,8)),8,' ')||rpad('0',15,'0')||'604'||'L'||rpad(' ',1,' ')
623 ||rpad('0',15,'0')||rpad(' ',5,' ')||rpad('0',6,'0')||rpad('0',6,'0')||rpad(' ',1,' ')
624 ||rpad(' ',9,' ')||lpad('0',10,'0');
625
626
627
628 l_audit_text := l_address1|| ',' ||l_address2|| ',' ||l_post_code||','||l_city;
629
630 p_write_text1 := l_text1;
631 p_write_text2 := l_text2;
632 p_write_text3 := l_text3;
633 p_write_text4 := l_text4;
634
635 p_audit_address := l_audit_text;
636
637 RETURN '1';
638 end get_betfor21_invoice_record;
639
640 -----------------------------------------------------------------------------------------------------------
641
642 FUNCTION get_betfor23_record (
646 ,p_org_pay_method_id IN number
643 p_assignment_id IN number
644 ,p_business_group_id IN number
645 ,p_per_pay_method_id IN number
647 ,p_date_earned IN date
648 ,p_payroll_id IN number
649 ,p_payroll_action_id IN number
650 ,p_assignment_action_id IN number
651 ,p_org_account_number IN varchar2
652 ,p_amount in varchar2
653 ,p_seq_control IN VARCHAR2
654 ,p_enterprise_no IN varchar2
655 ,p_write_text1 OUT NOCOPY VARCHAR2
656 ,p_write_text2 OUT NOCOPY VARCHAR2
657 ,p_write_text3 OUT NOCOPY VARCHAR2
658 ,p_write_text4 OUT NOCOPY VARCHAR2 ) return varchar2 is
659
660 l_text1 varchar2(100);
661 l_text2 varchar2(100);
662 l_text3 varchar2(100);
663 l_text4 varchar2(100);
664
665 l_amount number(15);
666
667 begin
668
669 l_amount := p_amount*100;
670
671
672 l_text1 := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','BF23')||lpad(to_char(p_enterprise_no),11,'0')||p_org_account_number||
673 lpad(p_seq_control,4,'0')||rpad(' ',6,' ');
674
675 l_text2 := rpad(' ',40,' ')||rpad(' ',40,' ');
676
677 l_text3 := rpad(' ',40,' ')||rpad(' ',27,' ')||lpad(SUBSTR(lpad(to_char(p_assignment_action_id),30,'0'),1,13),13,'0');
678
679
680 l_text4 := lpad(SUBSTR(lpad(to_char(p_assignment_action_id),30,'0'),14,17),17,'0')||lpad(l_amount,15,'0')||
681 'D'||rpad(' ',20,' ')||rpad(' ',3,' ')||rpad(' ',1,' ')||rpad(' ',15,' ')||rpad('0',8,'0');
682
683 p_write_text1 := l_text1;
684 p_write_text2 := l_text2;
685 p_write_text3 := l_text3;
686 p_write_text4 := l_text4;
687
688 RETURN '1';
689 end get_betfor23_record;
690
691 --------------------------------------------------------------------------------
692
693 FUNCTION get_audit_record (
694 p_assignment_id IN number
695 ,p_business_group_id IN number
696 ,p_per_pay_method_id IN number
697 ,p_org_pay_method_id IN number
698 ,p_date_earned IN date
699 ,p_payroll_id IN number
700 ,p_payroll_action_id IN number
701 ,p_assignment_action_id IN number
702 ,p_type_of_record in varchar2
703 ,p_last_name in varchar2
704 ,p_first_name in varchar2
705 ,p_amount in varchar2
706 ,p_report2_text1 OUT NOCOPY VARCHAR2
707 ,p_ni_number in varchar2) return varchar2 is
708
709
710 l_text1 varchar2(300);
711
712
713 l_per_account_num PAY_EXTERNAL_ACCOUNTS.SEGMENT6%TYPE;
714
715 l_per_account_num_t varchar2(17);
716 l_text_account_no varchar2(30);
717
718 l_payee_name varchar2(100);
719 l_amount varchar2(25);
720
721 cursor get_per_org_account_no is
722 select pea.segment6
723 from pay_external_accounts pea,
724 pay_personal_payment_methods_f ppp
725 where
726 ppp.personal_payment_method_id=p_per_pay_method_id
727 and ppp.external_account_id=pea.external_account_id;
728
729
730 begin
731
732 IF p_type_of_record = 'BETFOR22' then
733
734 open get_per_org_account_no;
735 fetch get_per_org_account_no into l_per_account_num;
736 close get_per_org_account_no;
737
738 l_payee_name := UPPER(substr(p_last_name || ' ' || p_first_name,1,50));
739
740 -- Bug 5943490 Fix : Correct Number Format
741 -- l_amount := p_amount||'.00';
742 l_amount := trim(to_char(trunc(p_amount,2),'999G999G999D99')) ;
743
744 l_per_account_num_t := lpad(to_char(l_per_account_num),11,'0');
745
746 l_text_account_no := SUBSTR(l_per_account_num_t,1,4)||'.'||SUBSTR(l_per_account_num_t,5,2)
747 ||'.'||SUBSTR(l_per_account_num_t,7,5);
748
749 l_text1 := rpad(p_ni_number,30,' ')||rpad(l_payee_name,50,' ')||rpad(to_char(l_text_account_no),25,' ')||
750 rpad(' ',5,' ')||rpad(l_amount,30,' ')||rpad(' ',5,' ');
751
752 p_report2_text1 := l_text1;
753
754 elsif p_type_of_record = 'BETFOR23' then
755
756 l_payee_name := UPPER(substr(p_last_name || ' ' || p_first_name,1,30));
757
758 -- Bug 5943490 Fix : Correct Number Format
759 -- l_amount := p_amount||'.00';
760 l_amount := trim(to_char(trunc(p_amount,2),'999G999G999D99')) ;
761
762 l_text1 := rpad(p_ni_number,30,' ')||rpad(l_payee_name,50,' ')||rpad('-',30,' ')||
763 rpad(l_amount,25,' ')||rpad(' ',5,' ');
764
765 p_report2_text1 := l_text1;
766
767
768 end if;
769
770 RETURN '1';
771 end get_audit_record;
772
773 -----------------------------------------------------------------------------
774
775 FUNCTION get_legal_emp_name(p_business_group_id IN number,p_legal_emp_id IN varchar2
776 ,p_legal_emp_name OUT NOCOPY VARCHAR2)
777 return varchar2 is
778
782 WHERE o.business_group_id = p_business_group_id
779 cursor c_get_legal_emp_name is
780 select o.name
781 from HR_ORGANIZATION_UNITS o
783 and o.organization_id = to_number(p_legal_emp_id);
784
785
786 l_legal_emp_name hr_organization_units.NAME%TYPE ;
787
788 begin
789
790
791 open c_get_legal_emp_name;
792 fetch c_get_legal_emp_name into l_legal_emp_name;
793 close c_get_legal_emp_name;
794
795 p_legal_emp_name := l_legal_emp_name;
796 return l_legal_emp_name;
797
798
799 end get_legal_emp_name;
800 -------------------------------------------------------------------------------
801
802 FUNCTION update_seq_values (
803 p_payroll_id IN number
804 ,p_ah_seq IN varchar2
805 ,p_seq_control IN VARCHAR2) return varchar2 IS
806
807 l_org_pay_method_id NUMBER;
808 l_ah_seq NUMBER ;
809 l_seq_control NUMBER ;
810
811
812
813 BEGIN
814
815 l_ah_seq := to_number(p_ah_seq);
816 l_seq_control := to_number(p_seq_control);
817
818 select pop.ORG_PAYMENT_METHOD_ID
819 into l_org_pay_method_id
820 from pay_org_payment_methods_f pop,
821 pay_payment_types ppt,
822 pay_org_pay_method_usages_f ppu
823
824 where ppt.payment_type_id=pop.payment_type_id
825 and ppt.category in ('MT')
826 and ppu.payroll_id=p_payroll_id
827 and pop.org_payment_method_id=ppu.org_payment_method_id
828 and rownum<2;
829
830
831
832 if l_seq_control = 9999 then
833 l_seq_control := 0;
834 else
835 l_seq_control := l_seq_control + 1;
836 end if;
837
838
839 if l_ah_seq = 999999 then
840 l_ah_seq := 1;
841 else
842 l_ah_seq := l_ah_seq + 1;
843 end if;
844
845 update pay_org_payment_methods_f
846 set pmeth_information3=l_ah_seq , pmeth_information4=l_seq_control
847 where ORG_PAYMENT_METHOD_ID=l_org_pay_method_id;
848
849 RETURN '1';
850
851 end update_seq_values;
852 -------------------------------------------------------------------------------
853 /* Acknowledgement Reply Functions */
854
855 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
856 PROCEDURE upload(
857 errbuf OUT NOCOPY VARCHAR2,
858 retcode OUT NOCOPY NUMBER,
859 p_file_name IN VARCHAR2,
860 p_effective_date IN VARCHAR2,
861 p_business_group_id IN VARCHAR2
862 )
863 IS
864
865 /* Constants */
866 c_read_file CONSTANT VARCHAR2 (1) := 'r';
867 c_max_linesize CONSTANT NUMBER := 4000;
868
869 /* Procedure name */
870 l_proc CONSTANT VARCHAR2 (72) := l_package||'.read' ;
871
872 /* File Handling variables */
873 l_file_type UTL_FILE.file_type;
874 l_filename VARCHAR2 (240);
875 l_location VARCHAR2 (4000);
876 l_record_read VARCHAR2 (1000) ;
877 l_record_write VARCHAR2 (1000) ;
878 l_num_of_records NUMBER ;
879 l_column_heading1 VARCHAR2 (1000) ;
880 l_column_heading2 VARCHAR2 (1000) ;
881 l_column_underline VARCHAR2 (1000) ;
882
883 l_heading_emp VARCHAR2 (100) ;
884 l_heading_num VARCHAR2 (100) ;
885 l_heading_amt VARCHAR2 (100) ;
886 l_heading_return VARCHAR2 (100) ;
887 l_heading_code VARCHAR2 (100) ;
888 l_heading_seq VARCHAR2 (100) ;
889 l_heading_ah VARCHAR2 (100) ;
890 l_heading_ref VARCHAR2 (100) ;
891 l_heading_serial VARCHAR2 (100) ;
892 l_heading_name VARCHAR2 (100) ;
893 l_heading_remark VARCHAR2 (100) ;
894
895 /* Variables to Read from File */
896 l_trans_code varchar2(8);
897 l_val_seq_no varchar2(20);
898 l_val_return_code varchar2(20);
899 l_val_ref_no varchar2(20);
900 l_val_remark varchar2(200);
901 l_val_serial_no varchar2(4);
902 l_ah_trans_date varchar2(20);
903 l_ah_proc_id varchar2(20);
904 l_enterprise_no varchar2(20);
905 l_division varchar2(20);
906 l_production_date varchar2(20);
907 l_no_of_payments varchar2(20);
908 l_tot_amt_batch varchar2(20);
909 l_no_of_records varchar2(20);
910 l_emp_no varchar2(20);
911 l_emp_name varchar2(50);
912 l_own_ref varchar2(30);
913 l_asg_act_id NUMBER;
914 l_emp_name_old varchar2(50) ;
915 l_amount varchar2(20);
916 l_val_seq_no_prev varchar2(20);
917
918 /* local Variables for calculation */
919 l_acc_pay_no NUMBER:=0;
923
920 l_acc_pay_amt NUMBER:=0;
921 l_rej_pay_no NUMBER:=0;
922 l_rej_pay_amt NUMBER:=0;
924
925 /* Exceptions */
926 e_fatal_error EXCEPTION;
927 BETFOR00_NOT_FOUND EXCEPTION;
928
929 BEGIN
930
931
932 l_record_read := NULL;
933 l_record_write := NULL;
934 l_num_of_records := 0;
935 l_column_heading1 := NULL;
936 l_column_heading2 := NULL;
937 l_column_underline := NULL;
938
939 l_emp_name_old := ' ' ;
940 l_heading_emp := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','EMP');
941 l_heading_num := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','NUM');
942 l_heading_amt := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','AMT');
943 l_heading_return := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','RET');
944 l_heading_code := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','CODE');
945 l_heading_seq := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','SEQ');
946 l_heading_ah := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','AH');
947 l_heading_ref := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','REF');
948 l_heading_serial := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','SER');
949 l_heading_name := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','NAME');
950 l_heading_remark := get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','REM');
951
952 l_filename := p_file_name;
953 fnd_profile.get (c_data_exchange_dir, l_location);
954
955 /* error : I/O directory not defined */
956 IF l_location IS NULL THEN
957 RAISE e_fatal_error;
958 END IF;
959
960 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
961
962 /* getting file header information
963 - open the file
964 - read the first record
965 - check fro BETFOR00
966 - if not present , raise error else print details */
967 BEGIN
968
969 l_file_type := UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
970 /* read the entire record consisting of the 4 lines of data from file */
971 read_lines ( p_process => g_process
972 ,p_file_type => l_file_type
973 ,p_record => l_record_read );
974 /* read the transaction code from the record */
975 read_trans_code
976 ( p_process => g_process
977 ,p_line => l_record_read
978 ,p_trans_code => l_trans_code
979 );
980 /* check for BETFOR00 */
981
982 fnd_file.put_line (fnd_file.LOG,'');
983 fnd_file.put_line (fnd_file.LOG,'');
984 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','NAL'));
985 fnd_file.put_line (fnd_file.LOG,'====================================================');
986 fnd_file.put_line (fnd_file.LOG,'');
987
988 IF l_trans_code <> 'BETFOR00' THEN
989
990 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','RNV00'));
991 RAISE BETFOR00_NOT_FOUND;
992 ELSE
993 /* get batch details */
994 l_ah_trans_date := SUBSTR(l_record_read,10,4);
995 l_ah_proc_id := SUBSTR(l_record_read,6,4);
996 l_enterprise_no := SUBSTR(l_record_read,49,11);
997 l_division := SUBSTR(l_record_read,60,11);
998 l_production_date := SUBSTR(l_record_read,81,4);
999
1000 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','ARFN') || ' : ' || l_filename);
1001 fnd_file.put_line (fnd_file.LOG,'');
1002 fnd_file.put_line (fnd_file.LOG,l_heading_ah || ' - ' || get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','TD') ||' : ' || l_ah_trans_date);
1003 fnd_file.put_line (fnd_file.LOG,'');
1004 fnd_file.put_line (fnd_file.LOG,l_heading_ah || ' - ' || get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','PI') ||' : ' || l_ah_proc_id);
1005 fnd_file.put_line (fnd_file.LOG,'');
1006 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','EN') ||' : ' || l_enterprise_no);
1007 fnd_file.put_line (fnd_file.LOG,'');
1008 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','DIV') ||' : ' || l_division);
1009 fnd_file.put_line (fnd_file.LOG,'');
1010 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','PD') ||' : ' || l_production_date);
1011 fnd_file.put_line (fnd_file.LOG,'');
1012 END IF;
1013 UTL_FILE.fclose (l_file_type);
1014 EXCEPTION
1015 WHEN BETFOR00_NOT_FOUND
1016 THEN
1017 fnd_file.put_line (fnd_file.LOG,'');
1018 fnd_file.put_line (fnd_file.LOG,'');
1019 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','EAL'));
1020 fnd_file.put_line (fnd_file.LOG,'=========================================');
1021 fnd_file.put_line (fnd_file.LOG,'');
1022 fnd_file.put_line (fnd_file.LOG,'');
1023
1024
1025 END;
1026
1027 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1031 l_file_type := UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
1028 /* regular redaing of the file */
1029
1030 /* Open flat file */
1032
1033
1034 /* Loop over the file, reading in each line. GET_LINE will
1035 raise NO_DATA_FOUND when it is done, so we use that as the
1036 exit condition for the loop
1037 */
1038
1039 l_column_heading1 := rpad(l_heading_ah || ' ' || l_heading_seq,16,' ')
1040 || rpad(l_heading_ah || ' ' || l_heading_return,13,' ')
1041 || rpad(l_heading_ref,14,' ')
1042 || rpad(l_heading_serial,10,' ')
1043 || rpad(l_heading_emp,31,' ')
1044 || rpad(l_heading_emp,31,' ')
1045 || rpad(l_heading_amt,17,' ')
1046 || rpad(l_heading_return || ' ' || l_heading_code,15,' ') ;
1047
1048 l_column_heading2 := rpad(l_heading_num,16,' ')
1049 || rpad(l_heading_code,13,' ')
1050 || rpad(l_heading_num,14,' ')
1051 || rpad(l_heading_num,10,' ')
1052 || rpad(l_heading_num,31,' ')
1053 || rpad(l_heading_name,31,' ')
1054 || rpad(' ',17,' ')
1055 || rpad(l_heading_remark,15,' ') ;
1056
1057
1058 l_column_underline := lpad(' ',16 ,'=')
1059 || lpad(' ',13 ,'=')
1060 || lpad(' ',14 ,'=')
1061 || lpad(' ',10 ,'=')
1062 || lpad(' ',31 ,'=')
1063 || lpad(' ',31 ,'=')
1064 || lpad(' ',17 ,'=')
1065 || lpad(' ',15 ,'=');
1066
1067 fnd_file.put_line (fnd_file.LOG,'');
1068 fnd_file.put_line (fnd_file.LOG,l_column_heading1 );
1069 fnd_file.put_line (fnd_file.LOG,l_column_heading2 );
1070 fnd_file.put_line (fnd_file.LOG,l_column_underline );
1071 fnd_file.put_line (fnd_file.LOG,'');
1072
1073 <<read_lines_in_file>>
1074 LOOP
1075 BEGIN
1076
1077 /* read the entire record consisting of the 4 lines of data from file */
1078 read_lines ( p_process => g_process
1079 ,p_file_type => l_file_type
1080 ,p_record => l_record_read );
1081
1082 /* read the transaction code from the record */
1083 read_trans_code
1084 ( p_process => g_process
1085 ,p_line => l_record_read
1086 ,p_trans_code => l_trans_code
1087 );
1088
1089 /* increment the no. of records */
1090 l_num_of_records := l_num_of_records + 1;
1091
1092
1093 EXCEPTION
1094 WHEN VALUE_ERROR -- Input line too large for buffer specified in UTL_FILE.fopen
1095 THEN
1096 IF UTL_FILE.is_open (l_file_type)
1097 THEN
1098 UTL_FILE.fclose (l_file_type);
1099 END IF;
1100 retcode := c_error;
1101 EXIT;
1102
1103 WHEN NO_DATA_FOUND
1104 THEN
1105 IF l_trans_code <> 'BETFOR99' THEN
1106 fnd_file.put_line (fnd_file.LOG,'');
1107 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','RNV99'));
1108 ELSE
1109
1110 fnd_file.put_line (fnd_file.LOG,'');
1111 fnd_file.put_line (fnd_file.LOG,'');
1112
1113 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','NAP') ||' : ' || l_acc_pay_no);
1114 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','TAA') ||' : ' || l_acc_pay_amt);
1115 fnd_file.put_line (fnd_file.LOG,'');
1116 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','NRP') ||' : ' || l_rej_pay_no);
1117 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','TRA') ||' : ' || l_rej_pay_amt);
1118 fnd_file.put_line (fnd_file.LOG,'');
1119 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','NP') ||' : ' || l_no_of_payments);
1120 fnd_file.put_line (fnd_file.LOG,'');
1121 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','TAB') ||' : ' || l_tot_amt_batch);
1122 fnd_file.put_line (fnd_file.LOG,'');
1123 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','NR') ||' : ' || l_no_of_records);
1124 fnd_file.put_line (fnd_file.LOG,'');
1125
1126 END IF;
1127 fnd_file.put_line (fnd_file.LOG,'');
1128 fnd_file.put_line (fnd_file.LOG,'');
1129 fnd_file.put_line (fnd_file.LOG,get_lookup_meaning ('NO_PAYMENT_PROCESS_LABELS','EAL'));
1130 fnd_file.put_line (fnd_file.LOG,'=======================================================');
1131 fnd_file.put_line (fnd_file.LOG,'');
1132 fnd_file.put_line (fnd_file.LOG,'');
1133 EXIT;
1134
1135 END;
1136
1137 BEGIN
1138
1139 read_record
1140 (p_process => g_process
1141 ,p_line => l_record_read
1142 ,p_trans_code => l_trans_code
1143 ,p_ah_seq_no => l_val_seq_no
1144 ,p_ah_ret_code => l_val_return_code
1145 ,p_ref_no => l_val_ref_no
1149 ,p_amount => l_amount
1146 ,p_serial_no => l_val_serial_no
1147 ,p_emp_no => l_emp_no
1148 ,p_emp_name => l_emp_name
1150 ,p_ret_code_rem => l_val_remark
1151 ,p_emp_name_old => l_emp_name_old
1152 ,p_ah_seq_no_prev => l_val_seq_no_prev
1153 ,p_acc_pay_no => l_acc_pay_no
1154 ,p_acc_pay_amt => l_acc_pay_amt
1155 ,p_rej_pay_no => l_rej_pay_no
1156 ,p_rej_pay_amt => l_rej_pay_amt
1157 );
1158
1159 /* Get specific fields from BETFORXX */
1160 IF l_trans_code = 'BETFOR22' THEN
1161 l_val_seq_no_prev := l_val_seq_no;
1162
1163 ELSIF l_trans_code = 'BETFOR23' THEN
1164 l_emp_name := l_emp_name_old;
1165 l_val_seq_no_prev := l_val_seq_no;
1166
1167 ELSIF l_trans_code = 'BETFOR99' THEN
1168 l_no_of_payments := SUBSTR(l_record_read,85,4);
1169 l_tot_amt_batch := SUBSTR(l_record_read,89,15);
1170 l_no_of_records := SUBSTR(l_record_read,104,5);
1171
1172 END IF;
1173
1174
1175 l_record_write:= rpad(l_val_seq_no ,16 , ' ') ||
1176 rpad(l_val_return_code ,13, ' ') ||
1177 rpad(l_val_ref_no ,14 , ' ') ||
1178 rpad(l_val_serial_no ,10 , ' ') ||
1179 rpad(l_emp_no ,31,' ') ||
1180 rpad(l_emp_name ,31,' ') ||
1181 rpad(l_amount ,17,' ') ||
1182 l_val_remark;
1183
1184 fnd_file.put_line (fnd_file.LOG,l_record_write);
1185
1186 EXCEPTION
1187 WHEN e_record_too_long --Record is too long
1188 THEN -- Set retcode to 1, indicating a WARNING to the ConcMgr
1189 retcode := c_warning;
1190
1191 END;
1192 END LOOP read_lines_in_file;
1193
1194
1195 UTL_FILE.fclose (l_file_type);
1196
1197
1198 -- Most of these exceptions are not translated as they should not happen normally
1199 -- If they do happen, something is seriously wrong and SysAdmin interference will be necessary.
1200
1201 EXCEPTION
1202 WHEN e_fatal_error
1203 -- No directory specified
1204 THEN
1205 -- Close the file in case of error
1206 IF UTL_FILE.is_open (l_file_type)
1207 THEN
1208 UTL_FILE.fclose (l_file_type);
1209 END IF;
1210
1211
1212 -- Set retcode to 2, indicating an ERROR to the ConcMgr
1213 retcode := c_error;
1214
1215 -- Set the application error
1216 hr_utility.set_message (801, 'PAY_376826_NO_DATA_EXC_DIR_MIS');
1217
1218 -- Return the message to the ConcMgr (This msg will appear in the log file)
1219
1220 errbuf := hr_utility.get_message;
1221
1222 WHEN UTL_FILE.invalid_operation
1223 -- File could not be opened as requested, perhaps because of operating system permissions
1224 -- Also raised when attempting a write operation on a file opened for read, or a read operation
1225 -- on a file opened for write.
1226
1227 THEN
1228 IF UTL_FILE.is_open (l_file_type)
1229 THEN
1230 UTL_FILE.fclose (l_file_type);
1231 END IF;
1232
1233 retcode := c_error;
1234 errbuf := 'Reading File ('||l_location ||' -> '
1235 || l_filename
1236 || ') - Invalid Operation.';
1237 WHEN UTL_FILE.internal_error
1238 -- Unspecified internal error
1239 THEN
1240 IF UTL_FILE.is_open (l_file_type)
1241 THEN
1242 UTL_FILE.fclose (l_file_type);
1243 END IF;
1244
1245 retcode := c_error;
1246 errbuf := 'Reading File ('
1247 || l_location
1248 || ' -> '
1249 || l_filename
1250 || ') - Internal Error.';
1251
1252 WHEN UTL_FILE.invalid_mode
1253 -- Invalid string specified for file mode
1254 THEN
1255 IF UTL_FILE.is_open (l_file_type)
1256 THEN
1257 UTL_FILE.fclose (l_file_type);
1258 END IF;
1259
1260 retcode := c_error;
1261 errbuf := 'Reading File ('
1262 || l_location
1263 || ' -> '
1264 || l_filename
1265 || ') - Invalid Mode.';
1266
1267 WHEN UTL_FILE.invalid_path
1268 -- Directory or filename is invalid or not accessible
1269 THEN
1270 IF UTL_FILE.is_open (l_file_type)
1271 THEN
1272 UTL_FILE.fclose (l_file_type);
1273 END IF;
1274
1275 retcode := c_error;
1276 errbuf := 'Reading File ('
1277 || l_location
1278 || ' -> '
1279 || l_filename
1280 || ') - Invalid Path or Filename.';
1281
1282 WHEN UTL_FILE.invalid_filehandle
1283 -- File type does not specify an open file
1284 THEN
1285 IF UTL_FILE.is_open (l_file_type)
1286 THEN
1287 UTL_FILE.fclose (l_file_type);
1288 END IF;
1289
1290 retcode := c_error;
1291 errbuf := 'Reading File ('
1292 || l_location
1293 || ' -> '
1294 || l_filename
1295 || ') - Invalid File Type.';
1296 WHEN UTL_FILE.read_error
1297
1298 -- Operating system error occurred during a read operation
1299 THEN
1300 IF UTL_FILE.is_open (l_file_type)
1301 THEN
1302 UTL_FILE.fclose (l_file_type);
1303 END IF;
1304
1305 retcode := c_error;
1306 errbuf := 'Reading File ('
1307 || l_location
1308 || ' -> '
1309 || l_filename
1310 || ') - Read Error.';
1311 END upload;
1312
1313 -----------------------------------------------------------------------------------------------------------------------------------------------------------
1314
1315 PROCEDURE read_lines
1316 ( p_process IN VARCHAR2
1317 ,p_file_type IN UTL_FILE.file_type
1318 ,p_record OUT NOCOPY VARCHAR2
1319 )
1320 is
1321
1325
1322 l_line_read VARCHAR2 (4000) := NULL;
1323
1324 BEGIN
1326 IF (p_process = 'NO_ACK') THEN
1327 /* Read 4 lines of data from the acknowledgement reply file and combine it in 1 record */
1328
1329 UTL_FILE.get_line (p_file_type, l_line_read);
1330 p_record:=l_line_read;
1331
1332 UTL_FILE.get_line (p_file_type, l_line_read);
1333 p_record:=p_record || l_line_read;
1334
1335 UTL_FILE.get_line (p_file_type, l_line_read);
1336 p_record:=p_record || l_line_read;
1337
1338 UTL_FILE.get_line (p_file_type, l_line_read);
1339 p_record:=p_record || l_line_read;
1340
1341
1342 END IF;
1343 END read_lines;
1344
1345 -----------------------------------------------------------------------------------------------------------------------------------------------------------
1346
1347 PROCEDURE read_trans_code
1348 ( p_process IN VARCHAR2
1349 ,p_line IN VARCHAR2
1350 ,p_trans_code OUT NOCOPY VARCHAR2
1351 )
1352 is
1353
1354 --Variables to store the extra values read from the flat file
1355 l_record_length NUMBER :=4000;
1356
1357 BEGIN
1358
1359 IF (p_process = 'NO_ACK') THEN
1360
1361 --Set record length
1362 l_record_length := 321;
1363
1364 p_trans_code := substr( p_line ,41,8);
1365
1366 END IF;
1367
1368
1369 -- Error in record if it is too long according to given format
1370 IF (length(p_line)> l_record_length) THEN
1371
1372 RAISE e_record_too_long;
1373 END IF;
1374
1375 END read_trans_code;
1376
1377 -----------------------------------------------------------------------------------------------------------------------------------------------------------
1378 PROCEDURE read_record
1379 (p_process IN VARCHAR2
1380 ,p_line IN VARCHAR2
1381 ,p_trans_code IN VARCHAR2
1382 ,p_ah_seq_no OUT NOCOPY VARCHAR2
1383 ,p_ah_ret_code OUT NOCOPY VARCHAR2
1384 ,p_ref_no OUT NOCOPY VARCHAR2
1385 ,p_serial_no OUT NOCOPY VARCHAR2
1386 ,p_emp_no OUT NOCOPY VARCHAR2
1387 ,p_emp_name OUT NOCOPY VARCHAR2
1388 ,p_amount OUT NOCOPY VARCHAR2
1389 ,p_ret_code_rem OUT NOCOPY VARCHAR2
1390 ,p_emp_name_old IN OUT NOCOPY VARCHAR2
1391 ,p_ah_seq_no_prev IN OUT NOCOPY VARCHAR2
1392 ,p_acc_pay_no IN OUT NOCOPY VARCHAR2
1393 ,p_acc_pay_amt IN OUT NOCOPY VARCHAR2
1394 ,p_rej_pay_no IN OUT NOCOPY VARCHAR2
1395 ,p_rej_pay_amt IN OUT NOCOPY VARCHAR2
1396 )
1397 IS
1398
1399
1400 --Variables to store the extra values read from the flat file
1401 l_record_length NUMBER :=4000;
1402
1403 -- Procedure name
1404 l_proc CONSTANT VARCHAR2 (72) := l_package|| '.read_record';
1405
1406 --local variables
1407 l_own_ref varchar2(50);
1408 l_asg_act_id pay_assignment_actions.assignment_action_id%TYPE ;
1409
1410
1411 /* cursor to get the emp no from the assignment_action_id */
1412 CURSOR csr_emp_no(v_asg_act_id pay_assignment_actions.assignment_action_id%TYPE)
1413 IS
1414 SELECT ppf.employee_number
1415 FROM per_all_people_f ppf,
1416 per_all_assignments_f paf,
1417 pay_assignment_actions paa
1418 WHERE ppf.person_id=paf.person_id
1419 AND paf.assignment_id=paa.assignment_id
1420 AND paa.assignment_action_id=v_asg_act_id;
1421
1422 BEGIN
1423
1424 IF (p_process = 'NO_ACK') THEN
1425
1426 --Set record length
1427 l_record_length := 321;
1428
1429 p_ah_seq_no := substr( p_line ,14,6);
1430 p_ah_ret_code := substr( p_line ,4,2);
1431 p_ret_code_rem := pay_no_payproc.get_lookup_meaning ('NO_RETURN_CODES',p_ah_ret_code);
1432
1433
1434 IF p_trans_code = 'BETFOR00' THEN
1435
1436 p_ref_no := '----';
1437 p_serial_no := '----';
1438 p_emp_no := '----';
1439 p_emp_name := '----';
1440 p_amount := '----';
1441
1442 ELSIF p_trans_code = 'BETFOR21' THEN
1443
1444 p_ref_no := substr( p_line ,75,6);
1445 p_serial_no := '----';
1446 p_emp_no := '----';
1447 p_emp_name := '----';
1448 p_amount := '----';
1449 p_emp_name_old:=SUBSTR(p_line,129,30);
1450 p_ah_seq_no_prev := p_ah_seq_no;
1451
1452 ELSIF p_trans_code = 'BETFOR22' THEN
1453
1454 p_ref_no := substr( p_line ,75,6);
1455 p_serial_no := substr( p_line ,293,4);
1456 p_emp_name := substr( p_line ,92,30);
1457 /* p_amount := ltrim(SUBSTR(p_line,122,13),'0') || '.' || SUBSTR(p_line,134,2); */
1458
1459 /* Bug Fix 4410230 */
1460 p_amount := ltrim(SUBSTR(p_line,122,13),'0') || '.' || SUBSTR(p_line,135,2);
1461
1462 l_own_ref := SUBSTR(p_line,283,10);
1463 l_asg_act_id := to_number(ltrim(l_own_ref,'0'));
1464
1465 OPEN csr_emp_no (l_asg_act_id);
1466 FETCH csr_emp_no INTO p_emp_no;
1467 CLOSE csr_emp_no;
1468
1469 IF p_emp_no IS NULL THEN
1470 p_emp_no := ' ';
1471 END IF;
1472
1473 IF (trim(p_ah_ret_code)='01') THEN
1474 p_acc_pay_no := p_acc_pay_no + 1;
1475 p_acc_pay_amt := p_acc_pay_amt + to_number(p_amount);
1476 ELSIF (p_ah_seq_no <> p_ah_seq_no_prev) THEN
1477 p_rej_pay_no := p_rej_pay_no + 1;
1478 p_rej_pay_amt := p_rej_pay_amt + to_number(p_amount);
1479 END IF;
1480
1481
1482 ELSIF p_trans_code = 'BETFOR23' THEN
1483
1484 p_ref_no := substr( p_line ,75,6);
1485 p_serial_no := substr( p_line ,294,3);
1486 /* p_amount := ltrim(SUBSTR(p_line,258,13),'0') || '.' || SUBSTR(p_line,270,2) ; */
1487
1488 /* Bug Fix 4410230 */
1489 p_amount := ltrim(SUBSTR(p_line,258,13),'0') || '.' || SUBSTR(p_line,271,2) ;
1490
1491 p_emp_name:= p_emp_name_old;
1492
1493 l_own_ref := SUBSTR(p_line,228,30);
1494 l_asg_act_id := to_number(ltrim(l_own_ref,'0'));
1495
1496 OPEN csr_emp_no (l_asg_act_id);
1497 FETCH csr_emp_no INTO p_emp_no;
1498 CLOSE csr_emp_no;
1499
1500 IF p_emp_no IS NULL THEN
1501 p_emp_no := ' ';
1502 END IF;
1503
1504 IF trim(p_ah_ret_code)='01' THEN
1505 p_acc_pay_no := p_acc_pay_no + 1;
1506 p_acc_pay_amt := p_acc_pay_amt + to_number(p_amount);
1507 ELSIF (p_ah_seq_no <> p_ah_seq_no_prev) THEN
1508 p_rej_pay_no := p_rej_pay_no + 1;
1509 p_rej_pay_amt := p_rej_pay_amt + to_number(p_amount);
1510 END IF;
1511
1512 ELSIF p_trans_code = 'BETFOR99' THEN
1513
1514 p_ref_no := '----';
1515 p_serial_no := '----';
1516 p_emp_no := '----';
1517 p_emp_name := '----';
1518 p_amount := '----';
1519
1520 END IF;
1521
1522 END IF;
1523
1524 -- Error in record if it is too long according to given format
1525 IF (length(p_line)> l_record_length) THEN
1526 RAISE e_record_too_long;
1527 END IF;
1528
1529 END read_record;
1530
1531
1532 -----------------------------------------------------------------------------------------------------------------------------------------
1533 -- function to get labels of items from a lookup
1534 FUNCTION get_lookup_meaning (p_lookup_type varchar2,p_lookup_code varchar2) RETURN VARCHAR2 IS
1535 CURSOR csr_lookup IS
1536 select meaning
1537 from hr_lookups
1538 where lookup_type = p_lookup_type
1539 and lookup_code = p_lookup_code;
1540 l_meaning hr_lookups.meaning%type;
1541 BEGIN
1542 OPEN csr_lookup;
1543 FETCH csr_lookup INTO l_Meaning;
1544 CLOSE csr_lookup;
1545 RETURN l_meaning;
1546 END get_lookup_meaning;
1547
1548
1549 ---------------------------------------------------------------------------------------------------------------
1550
1551
1552 ------------------------------------------------------------------------
1553 end PAY_NO_PAYPROC;