[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4A_MAG
Source
1 PACKAGE BODY pay_ca_t4a_mag AS
2 /* $Header: pycat4am.pkb 120.9 2011/01/27 11:46:12 sneelapa ship $ */
3
4 -----------------------------------------------------------------------------
5 -- Name : get_report_parameters
6 --
7 -- Purpose
8 -- The procedure gets the 'parameter' for which the report is being
9 -- run i.e., the period, state and business organization.
10 --
11 -- Arguments
12 -- p_pactid Payroll_action_id passed from pyugen process
13 -- p_year_start Start Date of the period for which the report
14 -- has been requested
15 -- p_year_end End date of the period
16 -- p_business_group_id Business group for which the report is being run
17 -- p_report_type Type of report being run T4
18 --
19 -- Notes
20 ----------------------------------------------------------------------------
21
22
23 PROCEDURE get_report_parameters
24 ( p_pactid IN NUMBER,
25 p_year_start IN OUT NOCOPY DATE,
26 p_year_end IN OUT NOCOPY DATE,
27 p_report_type IN OUT NOCOPY VARCHAR2,
28 p_business_group_id IN OUT NOCOPY NUMBER,
29 p_legislative_parameters OUT NOCOPY VARCHAR2
30 ) IS
31 BEGIN
32 hr_utility.set_location
33 ('pay_ca_t4a_mag.get_report_parameters', 10);
34
35 SELECT ppa.start_date,
36 ppa.effective_date,
37 ppa.business_group_id,
38 ppa.report_type,
39 ppa.legislative_parameters
40 INTO p_year_start,
41 p_year_end,
42 p_business_group_id,
43 p_report_type,
44 p_legislative_parameters
45 FROM pay_payroll_actions ppa
46 WHERE payroll_action_id = p_pactid;
47 hr_utility.set_location
48 ('pay_ca_t4a_mag.get_report_parameters', 20);
49
50 END get_report_parameters;
51
52 /* Added to fix performance bug */
53
54 Function get_user_entity_id(p_user_name varchar2) return number is
55
56 cursor cur_user_entity_id is
57 select user_entity_id
58 from ff_database_items
59 where user_name = p_user_name;
60
61 l_user_entity_id ff_database_items.user_entity_id%TYPE;
62
63 begin
64
65 open cur_user_entity_id;
66
67 fetch cur_user_entity_id
68 into l_user_entity_id;
69
70 close cur_user_entity_id;
71 return l_user_entity_id;
72
73 end;
74
75 ----------------------------------------------------------------------------
76 --Name
77 -- range_cursor
78 --Purpose
79 -- This procedure defines a SQL statement
80 -- to fetch all the people to be included in the report. This SQL statement
81 -- is used to define the 'chunks' for multi-threaded operation
82 --Arguments
83 -- p_pactid payroll action id for the report
84 -- p_sqlstr the SQL statement to fetch the people
88 p_sqlstr OUT NOCOPY VARCHAR2
85 ------------------------------------------------------------------------------
86 PROCEDURE range_cursor (
87 p_pactid IN NUMBER,
89 )
90 IS
91 p_year_start DATE;
92 p_year_end DATE;
93 p_business_group_id NUMBER;
94 p_report_type VARCHAR2(30);
95
96 /* added to fix performance bug */
97 l_tax_year_ue_id NUMBER;
98 l_person_id_ue_id NUMBER;
99 l_legislative_parameters VARCHAR2(200);
100 l_tax_year VARCHAR2(10);
101
102 BEGIN
103
104 hr_utility.set_location( 'pay_ca_t4a_mag.range_cursor', 10);
105
106 get_report_parameters(
107 p_pactid,
108 p_year_start,
109 p_year_end,
110 p_report_type,
111 p_business_group_id,
112 l_legislative_parameters
113 );
114
115 l_tax_year_ue_id := get_user_entity_id('CAEOY_TAXATION_YEAR');
116 l_person_id_ue_id := get_user_entity_id('CAEOY_PERSON_ID');
117 l_tax_year := pay_ca_t4a_mag.get_parameter('REPORTING_YEAR',
118 l_legislative_parameters);
119
120 hr_utility.trace('l_tax_year ='||l_tax_year);
121 hr_utility.set_location( 'pay_ca_t4a_mag.range_cursor', 20);
122
123 /* Removed the join from 'Where clause' that checks
124 whether GRE has the 'Fed Magnetic Reporting' information or not */
125
126 /* Changed to fix performance bug */
127 p_sqlstr := 'select distinct to_number(fai1.value)
128 from ff_archive_items fai1,
129 ff_archive_items fai2,
130 pay_assignment_actions paa,
131 pay_payroll_actions ppa,
132 pay_payroll_actions ppa1
133 where ppa1.payroll_action_id = :p_pactid
134 and ppa.report_type = ''T4A''
135 and ppa.report_qualifier = ''CAEOY''
136 and ppa.report_category = ''CAEOY''
137 and ppa.action_type = ''X''
138 and ppa.action_status = ''C''
139 and ppa.business_group_id = ppa1.business_group_id
140 and ppa.effective_date = ppa1.effective_date
141 and paa.payroll_action_id = ppa.payroll_action_id
142 and paa.action_status = ''C''
143 and fai2.user_entity_id = '|| l_tax_year_ue_id ||
144 ' and fai2.context1 = paa.payroll_action_id
145 and fai2.value = '|| l_tax_year ||
146 ' and fai1.context1 = paa.assignment_action_id
147 and fai1.user_entity_id = '||l_person_id_ue_id||
148 ' order by to_number(fai1.value)';
149
150 hr_utility.set_location( 'pay_ca_t4a_mag.range_cursor', 30);
151
152 END range_cursor;
153
154 --
155 -----------------------------------------------------------------------------
156 --Name
157 -- create_assignment_act
158 --Purpose
159 -- Creates assignment actions for the payroll action associated with the
160 -- report
161 --Arguments
162 -- p_pactid payroll action for the report
163 -- p_stperson starting person id for the chunk
164 -- p_endperson last person id for the chunk
165 -- p_chunk size of the chunk
166 --Note
167 -- The procedure processes assignments in 'chunks' to facilitate
168 -- multi-threaded operation. The chunk is defined by the size and the
169 -- starting and ending person id. An interlock is also created against the
170 -- pre-processor assignment action to prevent rolling back of the archiver.
171 ----------------------------------------------------------------------------
172 --
173 PROCEDURE create_assignment_act(
174 p_pactid IN NUMBER,
175 p_stperson IN NUMBER,
176 p_endperson IN NUMBER,
177 p_chunk IN NUMBER )
178 IS
179
180 /* Added variables to fix performance bug */
181 l_legislative_parameters VARCHAR2(200);
182 l_trans_gre VARCHAR2(10);
183 l_validate_gre VARCHAR2(10);
184
185 -- Cursor to retrieve all the assignments for all GRE's
186 -- archived in a reporting year
187 /* Removed the join from 'Where clause' that checks
188 whether GRE has the 'Fed Magnetic Reporting' information or not */
189
190 /* Changed to fix performance bug */
191 CURSOR c_all_asg IS
192 SELECT paf.person_id,
193 paf.assignment_id,
194 Paa.tax_unit_id,
195 paf.effective_end_date,
196 paa.assignment_action_id
197 FROM pay_payroll_actions ppa,
198 pay_assignment_actions paa,
199 per_all_assignments_f paf,
200 pay_payroll_actions ppa1,
201 hr_organization_information hoi1
202 WHERE ppa1.payroll_action_id = p_pactid
203 AND ppa.report_type = 'T4A'
204 and ppa.report_qualifier = 'CAEOY'
205 and ppa.report_category = 'CAEOY'
206 and ppa.action_type = 'X'
207 and ppa.action_status = 'C'
208 AND ppa.business_group_id = ppa1.business_group_id
209 AND ppa.effective_date = ppa1.effective_date
210 AND paa.payroll_action_id = ppa.payroll_action_id
214 and paa.tax_unit_id = hoi1.organization_id
211 AND paa.action_status = 'C'
212 and hoi1.org_information_context= 'Canada Employer Identification'
213 and hoi1.org_information11 = l_trans_gre
215 AND paf.assignment_id = paa.assignment_id
216 AND paf.person_id BETWEEN p_stperson and p_endperson
217 AND paf.effective_start_date <= ppa.effective_date
218 AND paf.effective_end_date >= ppa.start_date
219 AND paf.effective_end_date = (SELECT MAX(paf2.effective_end_date)
220 FROM per_all_assignments_f paf2
221 WHERE paf2.assignment_id = paf.assignment_id
222 AND paf2.effective_start_date <= ppa.effective_date);
223
224
225 --local variables
226
227 l_year_start DATE;
228 l_year_end DATE;
229 l_effective_end_date DATE;
230 l_report_type VARCHAR2(30);
231 l_business_group_id NUMBER;
232 l_person_id NUMBER;
233 l_assignment_id NUMBER;
234 l_assignment_action_id NUMBER;
235 l_value NUMBER;
236 l_tax_unit_id NUMBER;
237 lockingactid NUMBER;
238
239 BEGIN
240
241 -- Get the report parameters. These define the report being run.
242
243 hr_utility.set_location( 'pay_ca_t4a_mag.create_assignement_act',10);
244
245 get_report_parameters(
246 p_pactid,
247 l_year_start,
248 l_year_end,
249 l_report_type,
250 l_business_group_id,
251 l_legislative_parameters
252 );
253 --hr_utility.trace_on(null,'T4MAG');
254 l_trans_gre := pay_ca_t4a_mag.get_parameter('TRANSMITTER_GRE',
255 l_legislative_parameters);
256 hr_utility.trace('l_trans_gre ='||l_trans_gre);
257 l_validate_gre := pay_ca_t4a_mag.validate_gre_data(l_trans_gre, to_char(l_year_end,'YYYY'));
258
259 hr_utility.set_location( 'pay_ca_t4a_mag.create_assignement_act',20);
260
261 if l_validate_gre = '1' then
262 hr_utility.raise_error;
263 end if;
264
265 IF l_report_type = 'MAG_T4A' THEN
266 OPEN c_all_asg;
267 LOOP
268 FETCH c_all_asg INTO l_person_id,
269 l_assignment_id,
270 l_tax_unit_id,
271 l_effective_end_date,
272 l_assignment_action_id;
273 hr_utility.set_location('pay_ca_t4a_mag.create_assignement_act', 30);
274 EXIT WHEN c_all_asg%NOTFOUND;
275
276
277 --Create the assignment action for the record
278
279 hr_utility.trace('Assignment Fetched - ');
280 hr_utility.trace('Assignment Id : '||
281 to_char(l_assignment_id));
282 hr_utility.trace('Person Id : '|| to_char(l_person_id));
283 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
284 hr_utility.trace('Effective End Date : '||
285 to_char(l_effective_end_date));
286
287 hr_utility.set_location(
288 'pay_ca_t4a_mag.create_assignement_act', 40);
289
290 SELECT pay_assignment_actions_s.nextval
291 INTO lockingactid
292 FROM dual;
293 hr_utility.set_location(
294 'pay_ca_t4a_mag.create_assignement_act', 50);
295
296 hr_nonrun_asact.insact(lockingactid, l_assignment_id,
297 p_pactid,p_chunk, l_tax_unit_id);
298
299 hr_utility.set_location(
300 'pay_ca_t4a_mag.create_assignement_act', 60);
301
302 hr_nonrun_asact.insint(lockingactid,
303 l_assignment_action_id);
304
305 hr_utility.set_location(
306 'pay_ca_t4a_mag.create_assignement_act', 70);
307
308 hr_utility.trace('Interlock Created - ');
309 hr_utility.trace('Locking Action : '||
310 to_char(lockingactid));
311 hr_utility.trace('Locked Action : '||
312 to_char(l_assignment_action_id));
313
314 END LOOP;
315 Close c_all_asg;
316 END IF;
317
318 END create_assignment_act;
319
320 function get_parameter(name in varchar2,
321 parameter_list varchar2) return varchar2
322 is
323 start_ptr number;
324 end_ptr number;
325 token_val pay_payroll_actions.legislative_parameters%type;
326 par_value pay_payroll_actions.legislative_parameters%type;
327 begin
328 --
329 token_val := name||'=';
330 --
331 start_ptr := instr(parameter_list, token_val) + length(token_val);
332 end_ptr := instr(parameter_list, ' ',start_ptr);
333 --
334 /* if there is no spaces use then length of the string */
335 if end_ptr = 0 then
336 end_ptr := length(parameter_list)+1;
337 end if;
338 --
339 /* Did we find the token */
343 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
340 if instr(parameter_list, token_val) = 0 then
341 par_value := NULL;
342 else
344 end if;
345 --
346 return par_value;
347 --
348 end get_parameter;
349
350 FUNCTION get_t4a_pp_regno
351 (
352 p_pactid IN NUMBER,
353 p_tax_unit_id IN NUMBER,
354 p_pp_regno1 OUT NOCOPY VARCHAR2,
355 p_pp_regno2 OUT NOCOPY VARCHAR2,
356 p_pp_regno3 OUT NOCOPY VARCHAR2
357 ) RETURN VARCHAR2 IS
358 cursor c_get_reg_no(cp_pact_id number,
359 cp_tax_unit_id number) is
360 select to_number(pai.action_information5) ppreg_amt,
361 pai.action_information4 ppreg_no
362 from pay_action_information pai,pay_payroll_actions ppa
363 where pai.action_context_id = cp_pact_id
364 and pai.tax_unit_id = cp_tax_unit_id
365 and ppa.payroll_action_id = pai.action_context_id
366 and pai.effective_date = ppa.effective_date
367 and pai.action_information_category = 'CAEOY PENSION PLAN INFO'
368 order by 1 desc;
369
370 lv_pp_regno1 varchar2(30) := 'X';
371 ln_pp_regamt1 number(30);
372 lv_pp_regno2 varchar2(30) := 'X';
373 ln_pp_regamt2 number(30);
374 lv_pp_regno3 varchar2(30) := 'X';
375 ln_pp_regamt3 number(30);
376 lv_pp_regno varchar2(30);
377 ln_pp_regamt number(30);
378
379 begin
380
381 open c_get_reg_no(p_pactid,p_tax_unit_id);
382 loop
383 fetch c_get_reg_no into ln_pp_regamt,lv_pp_regno;
384 exit when c_get_reg_no%NOTFOUND;
385
386 if c_get_reg_no%rowcount = 1 then
387 lv_pp_regno1 := lv_pp_regno;
388 ln_pp_regamt1 := ln_pp_regamt;
389 elsif c_get_reg_no%rowcount = 2 then
390 lv_pp_regno2 := lv_pp_regno;
391 ln_pp_regamt2 := ln_pp_regamt;
392 elsif c_get_reg_no%rowcount = 3 then
393 lv_pp_regno3 := lv_pp_regno;
394 ln_pp_regamt3 := ln_pp_regamt;
395 end if;
396
397 if c_get_reg_no%rowcount > 3 then
398 exit;
399 end if;
400
401 end loop;
402 close c_get_reg_no;
403
404 p_pp_regno1 := lv_pp_regno1;
405 p_pp_regno2 := lv_pp_regno2;
406 p_pp_regno3 := lv_pp_regno3;
407
408 return '1';
409
410 end get_t4a_pp_regno;
411
412 FUNCTION get_t4a_footnote_amounts ( p_assignment_action_id in number,p_footnote_code IN VARCHAR2) RETURN varchar2 IS
413 cursor c_get_footnote_amount( cp_assignment_action_id number,cp_footnote_code varchar2) is
414 select pai.action_information5
415 from pay_action_information pai
416 where pai.action_context_id = cp_assignment_action_id
417 and pai.action_information_category = 'CA FOOTNOTES'
418 and pai.action_information4 = cp_footnote_code
419 order by 1 desc;
420
421 lv_footnote_amount varchar2(80);
422
423 begin
424
425 lv_footnote_amount := '0';
426 open c_get_footnote_amount(p_assignment_action_id,p_footnote_code);
427 fetch c_get_footnote_amount into lv_footnote_amount;
428
429 hr_utility.trace('fetch footnote '|| lv_footnote_amount);
430
431 close c_get_footnote_amount;
432
433 return lv_footnote_amount;
434 exception
435 when no_data_found then
436 hr_utility.trace('fetch no footnote ');
437 lv_footnote_amount := '0';
438 return lv_footnote_amount;
439 end get_t4a_footnote_amounts;
440
441 function validate_gre_data ( p_trans IN VARCHAR2,
442 p_year IN VARCHAR2) return varchar2 IS
443
444 cursor c_trans_payid ( c_trans_id VARCHAR2,
445 c_year VARCHAR2) is
446 Select ppa.payroll_action_id,ppa.business_group_id
447 from hr_organization_information hoi,
448 pay_payroll_actions PPA
449 where hoi.organization_id = to_number(c_trans_id)
450 and hoi.org_information_context='Fed Magnetic Reporting'
451 and ppa.report_type = 'T4A' -- T4 Archiver Report Type
452 and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='))
453 and to_char(ppa.effective_date,'YYYY')= c_year
454 and to_char(ppa.effective_date,'DD-MM')= '31-12';
455
456 cursor c_all_gres(p_trans VARCHAR2,
457 p_year VARCHAR2,
458 p_bg_id NUMBER) is
459 Select distinct ppa.payroll_action_id, hoi.organization_id, hou.name
460 From pay_payroll_actions ppa,
461 hr_organization_information hoi,
462 hr_all_organization_units hou
463 where hoi.org_information_context = 'Canada Employer Identification'
464 and hoi.org_information11 = p_trans
465 and hou.business_group_id = p_bg_id
466 and hou.organization_id = hoi.organization_id
467 and ppa.report_type = 'T4A'
468 and ppa.effective_date = to_date('31-12'||p_year,'DD-MM-YYYY')
469 and ppa.business_group_id = p_bg_id
470 and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='));
471
472 cursor c_gre_name (b_org_id VARCHAR2) is
473 select hou.name
474 from hr_all_organization_units hou
475 where hou.organization_id = to_number(b_org_id);
476
477 /* Local variables */
478 l_trans_gre hr_all_organization_units.organization_id%TYPE;
479 l_year VARCHAR2(10);
483 l_tech_name VARCHAR2(240) ;
480 l_gre hr_all_organization_units.organization_id%TYPE;
481 l_bus_grp hr_all_organization_units.business_group_id%TYPE;
482 l_trans_no VARCHAR2(240);
484 l_tech_area VARCHAR2(240) ;
485 l_tech_phno VARCHAR2(240) ;
486 -- l_tech_email variable and validation for the same are added for bug 10247374
487 -- by rgottipa
488 l_tech_email VARCHAR2(240) ;
489 l_lang VARCHAR2(240) ;
490 l_acc_name VARCHAR2(240) ;
491 l_acc_area VARCHAR2(240) ;
492 l_acc_phno VARCHAR2(240) ;
493 l_trans_bus_no VARCHAR2(240);
494 l_bus_no VARCHAR2(240) ;
495 l_trans_payid pay_payroll_actions.payroll_action_id%TYPE;
496 l_gre_payid pay_payroll_actions.payroll_action_id%TYPE;
497 l_gre_actid pay_assignment_actions.assignment_action_id%TYPE;
498 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
499 l_acc_info_flag CHAR(1);
500 l_trans_name VARCHAR2(240);
501 l_gre_name VARCHAR2(240);
502 l_bg_id NUMBER;
503
504 BEGIN
505
506 /* Fetching the Payroll Action Id for Trasnmitter GRE */
507
508 --hr_utility.trace_on(null,'T4MAG');
509 hr_utility.trace('Inside the Validation Code');
510 hr_utility.trace('The Transmitter GRE id passed is '||p_trans);
511 open c_trans_payid(p_trans,p_year);
512 fetch c_trans_payid into l_trans_payid,l_bg_id;
513 IF c_trans_payid%notfound THEN
514 close c_trans_payid;
515 hr_utility.trace('The Transmitter GRE id not found '||p_trans);
516 hr_utility.raise_error;
517 return '1';
518 else
519 close c_trans_payid;
520 END IF;
521
522 hr_utility.trace('Fetched the Payroll Id for transmitter GRE'|| l_trans_payid);
523 hr_utility.trace('The Reporting Year is '||p_year);
524
525 /*Fetching the Trasnmitter Level Data */
526
527 l_trans_no := get_arch_val(l_trans_payid, 'CAEOY_TRANSMITTER_NUMBER');
528 l_tech_name:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_NAME');
529 l_tech_area:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_AREA_CODE');
530 l_tech_phno:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_PHONE');
531 l_lang := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_LANGUAGE');
532
533 l_tech_email := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_EMAIL');
534
535 l_acc_name := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
536 l_acc_area := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
537 l_acc_phno := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
538 l_trans_bus_no := get_arch_val(l_trans_payid, 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
539
540 OPEN c_gre_name(to_number(p_trans));
541 FETCH c_gre_name INTO l_trans_name;
542 CLOSE c_gre_name;
543
544 hr_utility.trace('Transmitter Number'||l_trans_no);
545 hr_utility.trace('Tech Name'||l_tech_name);
546 hr_utility.trace('Tech Email '||l_tech_email);
547 hr_utility.trace('Tech Phno'||l_tech_phno);
548 hr_utility.trace('Tech area'||l_tech_area);
549 hr_utility.trace('Tech Lang'||l_lang);
550
551 /* Checking for the validity of the above values fetched */
552 IF l_trans_no IS NULL
553 OR TRANSLATE(l_trans_no,'M0123456789','M9999999999') <> 'MM999999' THEN
554 hr_utility.trace('Incorrect Transmitter No format');
555 hr_utility.set_message(801,'PAY_74155_INCORRECT_TRANSMT_NO');
556 hr_utility.set_message_token('GRE_NAME',l_trans_name);
557 pay_core_utils.push_message(801,'PAY_74155_INCORRECT_TRANSMT_NO','P');
558 pay_core_utils.push_token('GRE_NAME',l_trans_name);
559 hr_utility.raise_error;
560 return '1';
561 END IF;
562
563 if l_tech_name is null or
564 l_tech_area is null or
565 l_tech_phno is null or
566 l_tech_email is null or
567 l_lang is null then
568 hr_utility.trace('Technical contact details missing');
569 hr_utility.set_message(801,'PAY_74158_INCORRECT_TCHN_INFO');
570 hr_utility.set_message_token('GRE_NAME',l_trans_name);
571 pay_core_utils.push_message(801,'PAY_74158_INCORRECT_TCHN_INFO','P');
572 pay_core_utils.push_token('GRE_NAME',l_trans_name);
573 hr_utility.raise_error;
574 return '1';
575 end if;
576 if l_acc_name is null or
577 l_acc_phno is null or
578 l_acc_area is null then
579 l_acc_info_flag := 'N';
580 else
581 l_acc_info_flag := 'Y';
582 end if;
583 hr_utility.trace('The value of the Flag is '||l_acc_info_flag);
584
585 /* If Transmitter Level Accounting Information is Missing checking for the GRE level information */
586
587 open c_all_gres(p_trans,p_year,l_bg_id);
588 loop
589 fetch c_all_gres into l_gre_payid, l_gre, l_gre_name;
590 hr_utility.trace('The Gre id fetched is '||l_gre);
591 if c_all_gres%notfound then
592 close c_all_gres;
593 exit;
594 end if;
595
596 hr_utility.trace('Before fetching the GREs for this Transmitter '||l_gre||'-'||p_year);
597
598
599 if l_gre <> to_number(p_trans) then
600 hr_utility.trace('Inside the loop'||l_gre_payid);
601
602 hr_utility.trace('Checking GRE level data');
603 hr_utility.trace('The Payroll Action Id for Gre is '|| l_gre_payid);
604 l_bus_no := get_arch_val(l_gre_payid,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
605 --l_tax_unit_id := get_arch_val(l_gre_payid, 'CAEOY_TAX_UNIT_ID');
609
606 l_acc_name := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
607 l_acc_area := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
608 l_acc_phno := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
610 hr_utility.trace('Tax unit Id'||l_tax_unit_id);
611 hr_utility.trace('Acc Name '||l_acc_name);
612 hr_utility.trace('Acc Area '||l_acc_area);
613 hr_utility.trace('Acc Phone '||l_acc_phno);
614 hr_utility.trace('gre namee '||l_gre_name);
615
616
617 if l_bus_no is null
618 or TRANSLATE(l_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' then
619 hr_utility.trace('No Business Number Entereed ');
620 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
621 hr_utility.set_message_token('GRE_NAME',l_gre_name);
622 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
623 pay_core_utils.push_token('GRE_NAME',l_gre_name);
624 hr_utility.raise_error;
625 return '1';
626 end if;
627
628 if (l_acc_name is null or
629 l_acc_area is null or
630 l_acc_phno is null ) and
631 l_acc_info_flag = 'N' then
632 hr_utility.trace('No Accounting Contact info present');
633 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
634 hr_utility.set_message_token('GRE_NAME',l_gre_name);
635 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
636 pay_core_utils.push_token('GRE_NAME',l_gre_name);
637 hr_utility.raise_error;
638 return '1';
639 end if;
640
641 elsif l_gre = to_number(p_trans) then
642
643 if l_trans_bus_no is null
644 or TRANSLATE(l_trans_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' then
645 hr_utility.trace('No Business Number Entereed ');
646 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
647 hr_utility.set_message_token('GRE_NAME',l_trans_name);
648 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
649 pay_core_utils.push_token('GRE_NAME',l_trans_name);
650 hr_utility.raise_error;
651 return '1';
652 end if;
653 if l_acc_info_flag = 'N' then
654 hr_utility.trace('No Accounting Contact info present');
655 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
656 hr_utility.set_message_token('GRE_NAME',l_trans_name);
657 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
658 pay_core_utils.push_token('GRE_NAME',l_trans_name);
659 hr_utility.raise_error;
660 return '1';
661 end if;
662 end if;
663 end loop;
664 return '0';
665 END validate_gre_data;
666
667 function get_dbitem_value(p_asg_act_id in number,
668 p_dbitem_name in varchar2) return varchar2
669 is
670 lv_value varchar2(60);
671
672 cursor c_get_dbitem_value(cp_dbitem varchar2) is
673 select fai.value
674 from ff_database_items fdi,
675 ff_archive_items fai
676 where fai.user_entity_id=fdi.user_entity_id
677 and fai.context1= p_asg_act_id
678 and fdi.user_name = cp_dbitem;
679
680 begin
681
682 open c_get_dbitem_value(p_dbitem_name);
683 fetch c_get_dbitem_value into lv_value;
684
685 /* if c_get_dbitem_value%NOTFOUND then
686 lv_value := '0';
687 end if; */
688
689 close c_get_dbitem_value;
690
691 return lv_value;
692
693 end;
694
695 FUNCTION convert_2_xml(p_data IN varchar2,
696 p_tag IN varchar2,
697 p_datatype IN char default 'T',
698 p_format IN varchar2 default NULL,
699 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
700 return varchar2 is
701
702 l_data varchar2(4000);
703 l_output varchar2(4000);
704 BEGIN
705 if p_null_allowed = 'N'
706 and (TRIM(p_data) is null or (p_datatype in ('N','C') and to_number(p_data) = 0)) then
707 return ' ';
708 end if;
709
710 l_data := trim(p_data);
711 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
712 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
713 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
714 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
715 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
716 --------------------------------------------------------
717 --- P_Datatype: T = Text, N = Number, C=Currency, D=Date
718 --------------------------------------------------------
719 IF p_datatype = 'T' or p_datatype = 'D' then
720 l_output := '<' || trim(p_tag) || '>' || trim(l_data) || '</' || trim(p_tag) || '>
721 ';
722 ELSIF p_datatype = 'N' or p_datatype = 'C' then
723 IF TRIM(p_format) is not null then
724 select to_char(to_number(p_data), p_format)
725 into l_data from dual;
726 ELSIF p_datatype = 'C' then -- Currency should be two decimal places
727 select to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
728 into l_data from dual;
729 END IF;
730 l_output := '<' || trim(p_tag) || '>' || trim(l_data) || '</' || trim(p_tag) || '>
731 ';
732 END IF;
733
734 return l_output;
735 END;
736
740 - Call to this function is made in fast formula T4A_EMPLOYEE_RECORD.
737 /* Function convert_t4a_oth_info_amt
738 - For Bug 6456662
739 - To process the other info amounts for T4A Magnetic Media.
741 - Formatted XML strings for other info amounts are returned through out paramaters
742 - Additionally formatted strings for .a03 file are returned through out paramaters
743 */
744
745 FUNCTION convert_t4a_oth_info_amt(p_assignment_action_id IN Number,
746 p_fail IN char,
747 p_oth_rep1 OUT nocopy varchar2,
748 p_oth_rep2 OUT nocopy varchar2,
749 p_oth_rep3 OUT nocopy varchar2,
750 p_oth_rep1_dummy OUT nocopy varchar2,
751 p_oth_rep2_dummy OUT nocopy varchar2,
752 p_oth_rep3_dummy OUT nocopy varchar2,
753 p_write_f31 OUT nocopy varchar2,
754 p_write_f32 OUT nocopy varchar2,
755 p_transfer_other_info1_str1 OUT nocopy varchar2,
756 p_transfer_other_info1_str2 OUT nocopy varchar2,
757 p_transfer_other_info1_str3 OUT nocopy varchar2,
758 p_transfer_other_info1_str4 OUT nocopy varchar2,
759 p_transfer_other_info1_str5 OUT nocopy varchar2,
760 p_transfer_other_info1_str6 OUT nocopy varchar2,
761 p_transfer_other_info2_str1 OUT nocopy varchar2,
762 p_transfer_other_info2_str2 OUT nocopy varchar2,
763 p_transfer_other_info2_str3 OUT nocopy varchar2,
764 p_transfer_other_info2_str4 OUT nocopy varchar2,
765 p_transfer_other_info2_str5 OUT nocopy varchar2,
766 p_transfer_other_info2_str6 OUT nocopy varchar2,
767 p_transfer_other_info3_str1 OUT nocopy varchar2,
768 p_transfer_other_info3_str2 OUT nocopy varchar2,
769 p_transfer_other_info3_str3 OUT nocopy varchar2,
770 p_transfer_other_info3_str4 OUT nocopy varchar2,
771 p_transfer_other_info3_str5 OUT nocopy varchar2,
772 p_transfer_other_info3_str6 OUT nocopy varchar2,
773 p_transfer_other_info4_str1 OUT nocopy varchar2,
774 p_transfer_other_info4_str2 OUT nocopy varchar2,
775 p_transfer_other_info4_str3 OUT nocopy varchar2,
776 p_transfer_other_info4_str4 OUT nocopy varchar2,
777 p_transfer_other_info4_str5 OUT nocopy varchar2,
778 p_transfer_other_info4_str6 OUT nocopy varchar2,
779 p_transfer_oth1_rep1 OUT nocopy varchar2,
780 p_transfer_oth1_rep2 OUT nocopy varchar2,
781 p_transfer_oth1_rep3 OUT nocopy varchar2,
782 p_transfer_oth2_rep2 OUT nocopy varchar2,
783 p_transfer_oth2_rep3 OUT nocopy varchar2,
784 p_transfer_oth3_rep2 OUT nocopy varchar2,
785 p_transfer_oth3_rep3 OUT nocopy varchar2,
786 p_transfer_oth4_rep3 OUT nocopy varchar2,
787 p_transfer_oth1_rep1_dummy OUT nocopy varchar2,
788 p_transfer_oth1_rep2_dummy OUT nocopy varchar2,
789 p_transfer_oth1_rep3_dummy OUT nocopy varchar2,
790 p_transfer_oth2_rep2_dummy OUT nocopy varchar2,
791 p_transfer_oth2_rep3_dummy OUT nocopy varchar2,
792 p_transfer_oth3_rep2_dummy OUT nocopy varchar2,
793 p_transfer_oth3_rep3_dummy OUT nocopy varchar2,
794 p_transfer_oth4_rep3_dummy OUT nocopy varchar2,
795 p_cnt OUT nocopy Number,
796 p_tot_oth_info_amt OUT nocopy Number)
797 return varchar2 is
798
799 l_other_info varchar2(100);
800 l_cnt Number :=0;
801 l_amt Number :=0;
802
803 l_write_f30 varchar2(400) := ' ';
804 l_write_f31 varchar2(400) := ' ';
805 l_write_f32 varchar2(400) := ' ';
806 l_oth_rep1 varchar2(400);
807 l_oth_rep2 varchar2(400);
808 l_oth_rep3 varchar2(400);
809 l_transfer_other_info1_str1 varchar2(400);
810 l_transfer_other_info1_str2 varchar2(400);
811 l_transfer_other_info1_str3 varchar2(400);
812 l_transfer_other_info1_str4 varchar2(400);
813 l_transfer_other_info1_str5 varchar2(400);
814 l_transfer_other_info1_str6 varchar2(400);
815 l_transfer_other_info2_str1 varchar2(400);
816 l_transfer_other_info2_str2 varchar2(400);
817 l_transfer_other_info2_str3 varchar2(400);
818 l_transfer_other_info2_str4 varchar2(400);
819 l_transfer_other_info2_str5 varchar2(400);
820 l_transfer_other_info2_str6 varchar2(400);
821 l_transfer_other_info3_str1 varchar2(400);
822 l_transfer_other_info3_str2 varchar2(400);
823 l_transfer_other_info3_str3 varchar2(400);
824 l_transfer_other_info3_str4 varchar2(400);
825 l_transfer_other_info3_str5 varchar2(400);
826 l_transfer_other_info3_str6 varchar2(400);
830 l_transfer_other_info4_str4 varchar2(400);
827 l_transfer_other_info4_str1 varchar2(400);
828 l_transfer_other_info4_str2 varchar2(400);
829 l_transfer_other_info4_str3 varchar2(400);
831 l_transfer_other_info4_str5 varchar2(400);
832 l_transfer_other_info4_str6 varchar2(400);
833 l_transfer_oth1_rep1 varchar2(400);
834 l_transfer_oth1_rep2 varchar2(400);
835 l_transfer_oth1_rep3 varchar2(400);
836 l_transfer_oth2_rep2 varchar2(400);
837 l_transfer_oth2_rep3 varchar2(400);
838 l_transfer_oth3_rep2 varchar2(400);
839 l_transfer_oth3_rep3 varchar2(400);
840 l_transfer_oth4_rep3 varchar2(400);
841
842 l_oth_rep1_dummy varchar2(400);
843 l_oth_rep2_dummy varchar2(400);
844 l_oth_rep3_dummy varchar2(400);
845 l_transfer_oth1_rep1_dummy varchar2(400);
846 l_transfer_oth1_rep2_dummy varchar2(400);
847 l_transfer_oth1_rep3_dummy varchar2(400);
848 l_transfer_oth2_rep2_dummy varchar2(400);
849 l_transfer_oth2_rep3_dummy varchar2(400);
850 l_transfer_oth3_rep2_dummy varchar2(400);
851 l_transfer_oth3_rep3_dummy varchar2(400);
852 l_transfer_oth4_rep3_dummy varchar2(400);
853
854 l_tot_oth_info_amt number := 0;
855
856 type string_table is table of varchar2(50) index by binary_integer;
857 t_dbi string_table;
858 t_tag string_table;
859
860 type number_table is table of number(2) index by binary_integer;
861 t_flag number_table;
862
863 BEGIN
864
865 /* DBIs of other info amounts */
866 t_dbi(1) := 'CAEOY_T4A_OTHER_INFO_AMOUNT026_PER_GRE_YTD';
867 t_dbi(2) := 'CAEOY_T4A_OTHER_INFO_AMOUNT027_PER_GRE_YTD';
868 t_dbi(3) := 'CAEOY_T4A_OTHER_INFO_AMOUNT028_PER_GRE_YTD';
869 t_dbi(4) := 'CAEOY_T4A_OTHER_INFO_AMOUNT030_PER_GRE_YTD';
870 t_dbi(5) := 'CAEOY_T4A_OTHER_INFO_AMOUNT032_PER_GRE_YTD';
871 t_dbi(6) := 'CAEOY_T4A_OTHER_INFO_AMOUNT034_PER_GRE_YTD';
872 -- t_dbi(7) := 'CAEOY_T4A_OTHER_INFO_AMOUNT036_PER_GRE_YTD';
873 /* t_dbi(7) := 'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO';
874
875 registration number will be displayed between Payer account
876 number and box 16 as per PM suggestion.
877
878 */
879
880 t_dbi(7) := 'CAEOY_T4A_OTHER_INFO_AMOUNT040_PER_GRE_YTD';
881 t_dbi(8) := 'CAEOY_T4A_OTHER_INFO_AMOUNT042_PER_GRE_YTD';
882 t_dbi(9) := 'CAEOY_T4A_OTHER_INFO_AMOUNT046_PER_GRE_YTD';
883 t_dbi(10) := 'CAEOY_T4A_OTHER_INFO_AMOUNT102_PER_GRE_YTD';
884 t_dbi(11) := 'CAEOY_T4A_OTHER_INFO_AMOUNT104_PER_GRE_YTD';
885 t_dbi(12) := 'CAEOY_T4A_OTHER_INFO_AMOUNT105_PER_GRE_YTD';
886 t_dbi(13) := 'CAEOY_T4A_OTHER_INFO_AMOUNT106_PER_GRE_YTD';
887 t_dbi(14) := 'CAEOY_T4A_OTHER_INFO_AMOUNT107_PER_GRE_YTD';
888 t_dbi(15) := 'CAEOY_T4A_OTHER_INFO_AMOUNT108_PER_GRE_YTD';
889 t_dbi(16) := 'CAEOY_T4A_OTHER_INFO_AMOUNT109_PER_GRE_YTD';
890 t_dbi(17) := 'CAEOY_T4A_OTHER_INFO_AMOUNT110_PER_GRE_YTD';
891 t_dbi(18) := 'CAEOY_T4A_OTHER_INFO_AMOUNT111_PER_GRE_YTD';
892 t_dbi(19) := 'CAEOY_T4A_OTHER_INFO_AMOUNT115_PER_GRE_YTD';
893 t_dbi(20) := 'CAEOY_T4A_OTHER_INFO_AMOUNT116_PER_GRE_YTD';
894 t_dbi(21) := 'CAEOY_T4A_OTHER_INFO_AMOUNT117_PER_GRE_YTD';
895 t_dbi(22) := 'CAEOY_T4A_OTHER_INFO_AMOUNT118_PER_GRE_YTD';
896 t_dbi(23) := 'CAEOY_T4A_OTHER_INFO_AMOUNT119_PER_GRE_YTD';
897 t_dbi(24) := 'CAEOY_T4A_OTHER_INFO_AMOUNT122_PER_GRE_YTD';
898 t_dbi(25) := 'CAEOY_T4A_OTHER_INFO_AMOUNT123_PER_GRE_YTD';
899 t_dbi(26) := 'CAEOY_T4A_OTHER_INFO_AMOUNT124_PER_GRE_YTD';
900 t_dbi(27) := 'CAEOY_T4A_OTHER_INFO_AMOUNT125_PER_GRE_YTD';
901 t_dbi(28) := 'CAEOY_T4A_OTHER_INFO_AMOUNT126_PER_GRE_YTD';
902 t_dbi(29) := 'CAEOY_T4A_OTHER_INFO_AMOUNT127_PER_GRE_YTD';
903 t_dbi(30) := 'CAEOY_T4A_OTHER_INFO_AMOUNT129_PER_GRE_YTD';
904 t_dbi(31) := 'CAEOY_T4A_OTHER_INFO_AMOUNT130_PER_GRE_YTD';
905 t_dbi(32) := 'CAEOY_T4A_OTHER_INFO_AMOUNT131_PER_GRE_YTD';
906 t_dbi(33) := 'CAEOY_T4A_OTHER_INFO_AMOUNT132_PER_GRE_YTD';
907 t_dbi(34) := 'CAEOY_T4A_OTHER_INFO_AMOUNT133_PER_GRE_YTD';
908 t_dbi(35) := 'CAEOY_T4A_OTHER_INFO_AMOUNT134_PER_GRE_YTD';
909 t_dbi(36) := 'CAEOY_T4A_OTHER_INFO_AMOUNT135_PER_GRE_YTD';
910 t_dbi(37) := 'CAEOY_T4A_OTHER_INFO_AMOUNT142_PER_GRE_YTD';
911 t_dbi(38) := 'CAEOY_T4A_OTHER_INFO_AMOUNT143_PER_GRE_YTD';
912 t_dbi(39) := 'CAEOY_T4A_OTHER_INFO_AMOUNT144_PER_GRE_YTD';
913 t_dbi(40) := 'CAEOY_T4A_OTHER_INFO_AMOUNT146_PER_GRE_YTD';
914 t_dbi(41) := 'CAEOY_T4A_OTHER_INFO_AMOUNT148_PER_GRE_YTD';
915 t_dbi(42) := 'CAEOY_T4A_OTHER_INFO_AMOUNT150_PER_GRE_YTD';
916 t_dbi(43) := 'CAEOY_T4A_OTHER_INFO_AMOUNT152_PER_GRE_YTD';
917 t_dbi(44) := 'CAEOY_T4A_OTHER_INFO_AMOUNT154_PER_GRE_YTD';
918 t_dbi(45) := 'CAEOY_T4A_OTHER_INFO_AMOUNT156_PER_GRE_YTD';
919 t_dbi(46) := 'CAEOY_T4A_OTHER_INFO_AMOUNT158_PER_GRE_YTD';
920 t_dbi(47) := 'CAEOY_T4A_OTHER_INFO_AMOUNT180_PER_GRE_YTD';
921 t_dbi(48) := 'CAEOY_T4A_OTHER_INFO_AMOUNT190_PER_GRE_YTD';
922
923 /* XML Tags for corresponding other info amounts*/
924 t_tag(1) := 'elg_rtir_amt';
925 t_tag(2) := 'nelg_rtir_amt';
926 t_tag(3) := 'oth_incamt';
927 t_tag(4) := 'ptrng_aloc_amt';
928 t_tag(5) := 'rpp_past_srvc_amt';
929 t_tag(6) := 'padj_amt';
930 -- t_tag(7) := 'ppln_dpsp_rgst_nbr';
931 t_tag(7) := 'resp_aip_amt';
932 t_tag(8) := 'resp_educt_ast_amt';
933 t_tag(9) := 'chrty_dons_amt';
934 t_tag(10) := 'nr_lsp_trnsf_amt';
935 t_tag(11) := 'rsch_grnt_amt';
936 t_tag(12) := 'brsy_amt';
937 t_tag(13) := 'dth_ben_amt';
938 t_tag(14) := 'wag_ls_incamt';
939 t_tag(15) := 'lsp_rpp_nelg_amt';
940 t_tag(16) := 'nrgst_ppln_amt';
944 t_tag(20) := 'med_trvl_amt';
941 t_tag(17) := 'pr_71_acr_lsp_amt';
942 t_tag(18) := 'inc_avg_annty_amt';
943 t_tag(19) := 'dpsp_ins_pay_amt';
945 t_tag(21) := 'loan_ben_amt';
946 t_tag(22) := 'med_prem_ben_amt';
947 t_tag(23) := 'grp_trm_life_amt';
948 t_tag(24) := 'resp_aip_oth_amt';
949 t_tag(25) := 'ins_rvk_dpsp_amt';
950 t_tag(26) := 'brd_wrk_site_amt';
951 t_tag(27) := 'dsblt_ben_amt';
952 t_tag(28) := 'pr_90_rpp_amt';
953 t_tag(29) := 'vtrn_ben_amt';
954 t_tag(30) := 'tx_dfr_ptrng_dvamt';
955 t_tag(31) := 'atp_inctv_grnt_amt';
956 t_tag(32) := 'rdsp_amt';
957 t_tag(33) := 'wag_ptct_pgm_amt';
958 t_tag(34) := 'var_pens_ben_amt';
959 t_tag(35) := 'tfsa_tax_amt';
960 t_tag(36) := 'rcpnt_pay_prem_phsp_amt';
961 t_tag(37) := 'indn_elg_rtir_amt';
962 t_tag(38) := 'indn_nelg_rtir_amt';
963 t_tag(39) := 'indn_oth_incamt';
964 t_tag(40) := 'indn_xmpt_pens_amt';
965 t_tag(41) := 'indn_xmpt_lsp_amt';
966 t_tag(42) := 'lbr_adj_ben_aprpt_act_amt';
967 t_tag(43) := 'subp_qlf_amt';
968 t_tag(44) := 'csh_awrd_pze_payr_amt';
969 t_tag(45) := 'bkcy_sttl_amt';
970 t_tag(46) := 'lsp_nelg_trnsf_amt';
971 t_tag(47) := 'lsp_dpsp_nelg_amt';
972 t_tag(48) := 'lsp_nrgst_pens_amt';
973
974 /* Total for some of the Other Information Elements
975 to be printed in a01 and .mf files.
976 Flag is set as 1 for the Elements to be included in Total
977 to be displayed and multiplied with l_amt below .
978 */
979
980 t_flag(1) := 1;
981 t_flag(2) := 1;
982 t_flag(3) := 0;
983 t_flag(4) := 0;
984 t_flag(5) := 0;
985 t_flag(6) := 0;
986 t_flag(7) := 0;
987 t_flag(8) := 0;
988 t_flag(9) := 1;
989 t_flag(10) := 1;
990 t_flag(11) := 1;
991 t_flag(12) := 1;
992 t_flag(13) := 1;
993 t_flag(14) := 1;
994 t_flag(15) := 1;
995 t_flag(16) := 1;
996 t_flag(17) := 1;
997 t_flag(18) := 1;
998 t_flag(19) := 1;
999 t_flag(20) := 1;
1000 t_flag(21) := 1;
1001 t_flag(22) := 1;
1002 t_flag(23) := 1;
1003 t_flag(24) := 1;
1004 t_flag(25) := 1;
1005 t_flag(26) := 1;
1006 t_flag(27) := 1;
1007 t_flag(28) := 1;
1008 t_flag(29) := 1;
1009 t_flag(30) := 1;
1010 t_flag(31) := 1;
1011 t_flag(32) := 1;
1012 t_flag(33) := 1;
1013 t_flag(34) := 1;
1014 t_flag(35) := 1;
1015 t_flag(36) := 1;
1016 t_flag(37) := 1;
1017 t_flag(38) := 1;
1018 t_flag(39) := 1;
1019 t_flag(40) := 1;
1020 t_flag(41) := 1;
1021 t_flag(42) := 1;
1022 t_flag(43) := 1;
1023 t_flag(44) := 1;
1024 t_flag(45) := 1;
1025 t_flag(46) := 1;
1026 t_flag(47) := 1;
1027 t_flag(48) := 1;
1028
1029 l_transfer_oth1_rep1 := rpad(lpad('.00,',10), 10*12, lpad('.00,',10));
1030 l_transfer_oth3_rep2 := rpad(lpad('.00,',10), 10*12, lpad('.00,',10));
1031 l_transfer_oth4_rep3 := rpad(lpad('.00,',10), 4*12, lpad('.00,',10));
1032
1033 hr_utility.trace('p_assignment_action_id = '||p_assignment_action_id);
1034 hr_utility.trace('p_fail = '||p_fail);
1035 hr_utility.trace('t_dbi.COUNT = '||t_dbi.COUNT);
1036
1037 for i in 1..t_dbi.COUNT
1038 loop
1039 l_amt := fnd_number.canonical_to_number(get_dbitem_value(p_assignment_action_id,t_dbi(i)));
1040
1041 begin
1042 if t_dbi(i) <> 'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO' then
1043 l_tot_oth_info_amt := l_tot_oth_info_amt + (l_amt * t_flag(i));
1044 end if;
1045 exception
1046 when others then
1047 hr_utility.trace('sqlerrm at multiply = '||sqlerrm);
1048 end;
1049
1050 if (p_fail <> 'Y') and (l_amt >0)
1051 and t_dbi(i) <> 'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO'
1052 then
1053
1054 l_other_info := CONVERT_2_XML(l_amt, t_tag(i), 'C'); -- Bug 7424296
1055
1056 l_cnt := l_cnt+1;
1057
1058 if l_cnt <= 4 then
1059 l_write_f30 := l_write_f30||l_other_info;
1060 elsif l_cnt <=8 then
1061 l_write_f31 := l_write_f31||l_other_info;
1062 elsif l_cnt <=12 then
1063 l_write_f32 := l_write_f32||l_other_info;
1064 elsif l_cnt <= 14 then
1065 l_transfer_other_info1_str1 := l_transfer_other_info1_str1 || l_other_info;
1066 elsif l_cnt <= 16 then
1067 l_transfer_other_info1_str2 := l_transfer_other_info1_str2 || l_other_info;
1068 elsif l_cnt <= 18 then
1069 l_transfer_other_info1_str3 := l_transfer_other_info1_str3 || l_other_info;
1070 elsif l_cnt <= 20 then
1071 l_transfer_other_info1_str4 := l_transfer_other_info1_str4 || l_other_info;
1072 elsif l_cnt <= 22 then
1073 l_transfer_other_info1_str5 := l_transfer_other_info1_str5 || l_other_info;
1074 elsif l_cnt <= 24 then
1075 l_transfer_other_info1_str6 := l_transfer_other_info1_str6 || l_other_info;
1076 elsif l_cnt <= 26 then
1077 l_transfer_other_info2_str1 := l_transfer_other_info2_str1 || l_other_info;
1078 elsif l_cnt <= 28 then
1079 l_transfer_other_info2_str2 := l_transfer_other_info2_str2 || l_other_info;
1080 elsif l_cnt <= 30 then
1081 l_transfer_other_info2_str3 := l_transfer_other_info2_str3 || l_other_info;
1082 elsif l_cnt <= 32 then
1083 l_transfer_other_info2_str4 := l_transfer_other_info2_str4 || l_other_info;
1084 elsif l_cnt <= 34 then
1088 elsif l_cnt <= 38 then
1085 l_transfer_other_info2_str5 := l_transfer_other_info2_str5 || l_other_info;
1086 elsif l_cnt <= 36 then
1087 l_transfer_other_info2_str6 := l_transfer_other_info2_str6 || l_other_info;
1089 l_transfer_other_info3_str1 := l_transfer_other_info3_str1 || l_other_info;
1090 elsif l_cnt <= 40 then
1091 l_transfer_other_info3_str2 := l_transfer_other_info3_str2 || l_other_info;
1092 elsif l_cnt <= 42 then
1093 l_transfer_other_info3_str3 := l_transfer_other_info3_str3 || l_other_info;
1094 elsif l_cnt <= 44 then
1095 l_transfer_other_info3_str4 := l_transfer_other_info3_str4 || l_other_info;
1096 elsif l_cnt <= 46 then
1097 l_transfer_other_info3_str5 := l_transfer_other_info3_str5 || l_other_info;
1098 else
1099 l_transfer_other_info3_str6 := l_transfer_other_info3_str6 || l_other_info;
1100 end if;
1101
1102 end if;
1103
1104 /* Formatting strings for .a03 audit report */
1105 if i <=12 then
1106 if t_dbi(i) = 'CAEOY_T4A_EMPLOYEE_REGISTRATION_NO' then
1107 l_oth_rep1 := l_oth_rep1|| to_char(rpad(l_amt || ' ', length(' Box 36'))) ||',';
1108 else
1109 l_oth_rep1 := l_oth_rep1 || to_char(l_amt, '99999999.99') ||',';
1110 end if;
1111
1112 elsif i <=24 then
1113 if p_fail = 'Y' or l_cnt <= 12 then
1114 l_oth_rep1 := l_oth_rep1 || to_char(l_amt, '99999999.99') ||',';
1115 l_transfer_oth1_rep1 := l_transfer_oth1_rep1 || lpad('.00,',12);
1116 else
1117 l_oth_rep1 := l_oth_rep1 || lpad('.00,',12);
1118 l_transfer_oth1_rep1 := l_transfer_oth1_rep1 || to_char(nvl(l_amt,0), '99999999.99') ||',';
1119 end if;
1120 elsif i <=36 then
1121 if p_fail = 'Y' or l_cnt <= 12 then
1122 l_oth_rep2 := l_oth_rep2 || to_char(l_amt, '99999999.99') ||',';
1123 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
1124 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
1125 elsif l_cnt <= 24 then
1126 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
1127 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || to_char(l_amt, '99999999.99') ||',';
1128 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
1129 else
1130 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
1131 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
1132 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || to_char(l_amt, '99999999.99') ||',';
1133 end if;
1134 elsif i <=48 then
1135 if p_fail = 'Y' or l_cnt <= 12 then
1136 l_oth_rep2 := l_oth_rep2 || to_char(l_amt, '99999999.99') ||',';
1137 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
1138 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
1139 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
1140 elsif l_cnt <= 24 then
1141 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
1142 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || to_char(l_amt, '99999999.99') ||',';
1143 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
1144 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
1145 elsif l_cnt <= 36 then
1146 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
1147 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
1148 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || to_char(l_amt, '99999999.99') ||',';
1149 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
1150 else
1151 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
1152 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
1153 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
1154 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || to_char(l_amt, '99999999.99') ||',';
1155 end if;
1156 end if;
1157 end loop;
1158
1159 p_cnt := l_cnt;
1160 p_tot_oth_info_amt := l_tot_oth_info_amt;
1161
1162 p_oth_rep1 := substr(l_oth_rep1,1,240);
1163 p_oth_rep1_dummy := substr(l_oth_rep1,241,480);
1164
1165 p_oth_rep2 := substr(l_oth_rep2,1,240);
1166 p_oth_rep2_dummy := substr(l_oth_rep2,241,480);
1167
1168 p_oth_rep3 := substr(l_oth_rep3,1,240);
1169 p_oth_rep3_dummy := substr(l_oth_rep3,241,480);
1170
1171 p_write_f31 := l_write_f31;
1172
1173 p_write_f32 := l_write_f32;
1174
1175 p_transfer_other_info1_str1 := l_transfer_other_info1_str1;
1176 p_transfer_other_info1_str2 := l_transfer_other_info1_str2;
1177 p_transfer_other_info1_str3 := l_transfer_other_info1_str3;
1178 p_transfer_other_info1_str4 := l_transfer_other_info1_str4;
1179 p_transfer_other_info1_str5 := l_transfer_other_info1_str5;
1180 p_transfer_other_info1_str6 := l_transfer_other_info1_str6;
1181 p_transfer_other_info2_str1 := l_transfer_other_info2_str1;
1182 p_transfer_other_info2_str2 := l_transfer_other_info2_str2;
1183 p_transfer_other_info2_str3 := l_transfer_other_info2_str3;
1184 p_transfer_other_info2_str4 := l_transfer_other_info2_str4;
1185 p_transfer_other_info2_str5 := l_transfer_other_info2_str5;
1186 p_transfer_other_info2_str6 := l_transfer_other_info2_str6;
1187 p_transfer_other_info3_str1 := l_transfer_other_info3_str1;
1191 p_transfer_other_info3_str5 := l_transfer_other_info3_str5;
1188 p_transfer_other_info3_str2 := l_transfer_other_info3_str2;
1189 p_transfer_other_info3_str3 := l_transfer_other_info3_str3;
1190 p_transfer_other_info3_str4 := l_transfer_other_info3_str4;
1192 p_transfer_other_info3_str6 := l_transfer_other_info3_str6;
1193 p_transfer_other_info4_str1 := l_transfer_other_info4_str1;
1194 p_transfer_other_info4_str2 := l_transfer_other_info4_str2;
1195 p_transfer_other_info4_str3 := l_transfer_other_info4_str3;
1196 p_transfer_other_info4_str4 := l_transfer_other_info4_str4;
1197 p_transfer_other_info4_str5 := l_transfer_other_info4_str5;
1198 p_transfer_other_info4_str6 := l_transfer_other_info4_str6;
1199
1200 p_transfer_oth1_rep1 := substr(l_transfer_oth1_rep1,1,240);
1201 p_transfer_oth1_rep1_dummy := substr(l_transfer_oth1_rep1,241,480);
1202
1203 p_transfer_oth1_rep2 := substr(l_transfer_oth1_rep2,1,240);
1204 p_transfer_oth1_rep2_dummy := substr(l_transfer_oth1_rep2,241,480);
1205
1206 p_transfer_oth1_rep3 := substr(l_transfer_oth1_rep3,1,240);
1207 p_transfer_oth1_rep3_dummy := substr(l_transfer_oth1_rep3,241,480);
1208
1209 p_transfer_oth2_rep2 := substr(l_transfer_oth2_rep2,1,240);
1210 p_transfer_oth2_rep2_dummy := substr(l_transfer_oth2_rep2,241,480);
1211
1212 p_transfer_oth2_rep3 := substr(l_transfer_oth2_rep3,1,240);
1213 p_transfer_oth2_rep3_dummy := substr(l_transfer_oth2_rep3,241,480);
1214
1215 p_transfer_oth3_rep2 := substr(l_transfer_oth3_rep2,1,240);
1216 p_transfer_oth3_rep2_dummy := substr(l_transfer_oth3_rep2,241,480);
1217
1218 p_transfer_oth3_rep3 := substr(l_transfer_oth3_rep3,1,240);
1219 p_transfer_oth3_rep3_dummy := substr(l_transfer_oth3_rep3,241,480);
1220
1221 p_transfer_oth4_rep3 := substr(l_transfer_oth4_rep3,1,240);
1222 p_transfer_oth4_rep3_dummy := substr(l_transfer_oth4_rep3,241,480);
1223
1224 hr_utility.trace('1 p_transfer_oth1_rep1 = '||p_transfer_oth1_rep1);
1225 hr_utility.trace('1 p_transfer_oth1_rep2 = '||p_transfer_oth1_rep2);
1226 hr_utility.trace('1 p_transfer_oth1_rep3 = '||p_transfer_oth1_rep3);
1227 hr_utility.trace('1 p_transfer_oth2_rep2 = '||p_transfer_oth2_rep2);
1228 hr_utility.trace('1 p_transfer_oth2_rep3 = '||p_transfer_oth2_rep3);
1229 hr_utility.trace('1 p_transfer_oth3_rep2 = '||p_transfer_oth3_rep2);
1230 hr_utility.trace('1 p_transfer_oth3_rep3 = '||p_transfer_oth3_rep3);
1231 hr_utility.trace('1 p_transfer_oth1_rep1_dummy = '||p_transfer_oth1_rep1_dummy);
1232 hr_utility.trace('1 p_transfer_oth1_rep2_dummy = '||p_transfer_oth1_rep2_dummy);
1233 hr_utility.trace('1 p_transfer_oth1_rep3_dummy = '||p_transfer_oth1_rep3_dummy);
1234 hr_utility.trace('1 p_transfer_oth2_rep2_dummy = '||p_transfer_oth2_rep2_dummy);
1235 hr_utility.trace('1 p_transfer_oth2_rep3_dummy = '||p_transfer_oth2_rep3_dummy);
1236 hr_utility.trace('1 p_transfer_oth3_rep2_dummy = '||p_transfer_oth3_rep2_dummy);
1237 hr_utility.trace('1 p_transfer_oth3_rep3_dummy = '||p_transfer_oth3_rep3_dummy);
1238
1239 /*
1240 hr_utility.trace('p_cnt = '|| l_cnt);
1241 hr_utility.trace('p_oth_rep1 = '|| l_oth_rep1);
1242 hr_utility.trace('p_oth_rep2 = '|| l_oth_rep2);
1243 hr_utility.trace('p_oth_rep3 = '|| l_oth_rep3);
1244 hr_utility.trace('write_f30 = '|| l_write_f30);
1245 hr_utility.trace('p_write_f31 = '|| l_write_f31);
1246 hr_utility.trace('p_transfer_other_info1_str1 = '|| l_transfer_other_info1_str1);
1247 hr_utility.trace('p_transfer_other_info1_str2 = '|| l_transfer_other_info1_str2);
1248 hr_utility.trace('p_transfer_other_info1_str3 = '|| l_transfer_other_info1_str3);
1249 hr_utility.trace('p_transfer_other_info2_str1 = '|| l_transfer_other_info2_str1);
1250 hr_utility.trace('p_transfer_other_info2_str2 = '|| l_transfer_other_info2_str2);
1251 hr_utility.trace('p_transfer_other_info2_str3 = '|| l_transfer_other_info2_str3);
1252 hr_utility.trace('p_transfer_other_info3_str1 = '|| l_transfer_other_info3_str1);
1253 hr_utility.trace('p_transfer_other_info3_str2 = '|| l_transfer_other_info3_str2);
1254 hr_utility.trace('p_transfer_other_info3_str3 = '|| l_transfer_other_info3_str3);
1255 hr_utility.trace('p_transfer_other_info4_str1 = '|| l_transfer_other_info4_str1);
1256 hr_utility.trace('p_transfer_other_info4_str2 = '|| l_transfer_other_info4_str2);
1257 hr_utility.trace('p_transfer_other_info4_str3 = '|| l_transfer_other_info4_str3);
1258 hr_utility.trace('p_transfer_oth1_rep1 = '|| l_transfer_oth1_rep1);
1259 hr_utility.trace('p_transfer_oth1_rep2 = '|| l_transfer_oth1_rep2);
1260 hr_utility.trace('p_transfer_oth1_rep3 = '|| l_transfer_oth1_rep3);
1261 hr_utility.trace('p_transfer_oth2_rep2 = '|| l_transfer_oth2_rep2);
1262 hr_utility.trace('p_transfer_oth2_rep3 = '|| l_transfer_oth2_rep3);
1263 hr_utility.trace('p_transfer_oth3_rep2 = '|| l_transfer_oth3_rep2);
1264 hr_utility.trace('p_transfer_oth3_rep3 = '|| l_transfer_oth3_rep3);
1265 hr_utility.trace('p_transfer_oth4_rep3 = '|| l_transfer_oth4_rep3);
1266
1267 */
1268
1269 return l_write_f30;
1270
1271 exception
1272 when others then
1273 return null;
1274 END convert_t4a_oth_info_amt;
1275
1276 FUNCTION get_arch_val( p_context_id IN NUMBER,
1277 p_user_name IN VARCHAR2)
1278 RETURN varchar2 IS
1279
1280 cursor cur_archive (b_context_id NUMBER, b_user_name VARCHAR2) is
1281 select fai.value
1282 from ff_archive_items fai,
1283 ff_database_items fdi
1284 where fai.user_entity_id = fdi.user_entity_id
1285 and fai.context1 = b_context_id
1286 and fdi.user_name = b_user_name;
1287
1288 l_return VARCHAR2(240);
1289 BEGIN
1290 open cur_archive(p_context_id,p_user_name);
1291 fetch cur_archive into l_return;
1292 close cur_archive;
1293 RETURN (l_return);
1294 END ;
1295 END pay_ca_t4a_mag;