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