[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4A_MAG
Source
1 PACKAGE BODY pay_ca_t4a_mag AS
2 /* $Header: pycat4am.pkb 120.1 2005/06/15 10:21:27 ssouresr noship $ */
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
85 ------------------------------------------------------------------------------
86 PROCEDURE range_cursor (
87 p_pactid IN NUMBER,
88 p_sqlstr OUT NOCOPY VARCHAR2
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
211 AND paa.action_status = 'C'
212 and hoi1.org_information_context= 'Canada Employer Identification'
213 and hoi1.org_information11 = l_trans_gre
214 and paa.tax_unit_id = hoi1.organization_id
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 */
340 if instr(parameter_list, token_val) = 0 then
341 par_value := NULL;
342 else
343 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
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);
377 ln_pp_regamt number(30);
374 lv_pp_regno3 varchar2(30) := 'X';
375 ln_pp_regamt3 number(30);
376 lv_pp_regno varchar2(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);
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);
483 l_tech_name VARCHAR2(240) ;
484 l_tech_area VARCHAR2(240) ;
485 l_tech_phno VARCHAR2(240) ;
486 l_lang VARCHAR2(240) ;
487 l_acc_name VARCHAR2(240) ;
488 l_acc_area VARCHAR2(240) ;
489 l_acc_phno VARCHAR2(240) ;
493 l_gre_payid pay_payroll_actions.payroll_action_id%TYPE;
490 l_trans_bus_no VARCHAR2(240);
491 l_bus_no VARCHAR2(240) ;
492 l_trans_payid pay_payroll_actions.payroll_action_id%TYPE;
494 l_gre_actid pay_assignment_actions.assignment_action_id%TYPE;
495 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
496 l_acc_info_flag CHAR(1);
497 l_trans_name VARCHAR2(240);
498 l_gre_name VARCHAR2(240);
499 l_bg_id NUMBER;
500
501 BEGIN
502
503 /* Fetching the Payroll Action Id for Trasnmitter GRE */
504
505 --hr_utility.trace_on(null,'T4MAG');
506 hr_utility.trace('Inside the Validation Code');
507 hr_utility.trace('The Transmitter GRE id passed is '||p_trans);
508 open c_trans_payid(p_trans,p_year);
509 fetch c_trans_payid into l_trans_payid,l_bg_id;
510 IF c_trans_payid%notfound THEN
511 close c_trans_payid;
512 hr_utility.trace('The Transmitter GRE id not found '||p_trans);
513 hr_utility.raise_error;
514 return '1';
515 else
516 close c_trans_payid;
517 END IF;
518
519 hr_utility.trace('Fetched the Payroll Id for transmitter GRE'|| l_trans_payid);
520 hr_utility.trace('The Reporting Year is '||p_year);
521
522 /*Fetching the Trasnmitter Level Data */
523
524 l_trans_no := get_arch_val(l_trans_payid, 'CAEOY_TRANSMITTER_NUMBER');
525 l_tech_name:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_NAME');
526 l_tech_area:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_AREA_CODE');
527 l_tech_phno:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_PHONE');
528 l_lang := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_LANGUAGE');
529 l_acc_name := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
530 l_acc_area := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
531 l_acc_phno := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
532 l_trans_bus_no := get_arch_val(l_trans_payid, 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
533
534 OPEN c_gre_name(to_number(p_trans));
535 FETCH c_gre_name INTO l_trans_name;
536 CLOSE c_gre_name;
537
538 hr_utility.trace('Transmitter Number'||l_trans_no);
539 hr_utility.trace('Tech Name'||l_tech_name);
540 hr_utility.trace('Tech Phno'||l_tech_phno);
541 hr_utility.trace('Tech area'||l_tech_area);
542 hr_utility.trace('Tech Lang'||l_lang);
543
544 /* Checking for the validity of the above values fetched */
545 IF l_trans_no IS NULL
546 OR TRANSLATE(l_trans_no,'M0123456789','M9999999999') <> 'MM999999' THEN
547 hr_utility.trace('Incorrect Transmitter No format');
548 hr_utility.set_message(801,'PAY_74155_INCORRECT_TRANSMT_NO');
549 hr_utility.set_message_token('GRE_NAME',l_trans_name);
550 pay_core_utils.push_message(801,'PAY_74155_INCORRECT_TRANSMT_NO','P');
551 pay_core_utils.push_token('GRE_NAME',l_trans_name);
552 hr_utility.raise_error;
553 return '1';
554 END IF;
555
556 if l_tech_name is null or
557 l_tech_area is null or
558 l_tech_phno is null or
559 l_lang is null then
560 hr_utility.trace('Technical contact details missing');
561 hr_utility.set_message(801,'PAY_74158_INCORRECT_TCHN_INFO');
562 hr_utility.set_message_token('GRE_NAME',l_trans_name);
563 pay_core_utils.push_message(801,'PAY_74158_INCORRECT_TCHN_INFO','P');
564 pay_core_utils.push_token('GRE_NAME',l_trans_name);
565 hr_utility.raise_error;
566 return '1';
567 end if;
568 if l_acc_name is null or
569 l_acc_phno is null or
570 l_acc_area is null then
571 l_acc_info_flag := 'N';
572 else
573 l_acc_info_flag := 'Y';
574 end if;
575 hr_utility.trace('The value of the Flag is '||l_acc_info_flag);
576
577 /* If Transmitter Level Accounting Information is Missing checking for the GRE level information */
578
579 open c_all_gres(p_trans,p_year,l_bg_id);
580 loop
581 fetch c_all_gres into l_gre_payid, l_gre, l_gre_name;
582 hr_utility.trace('The Gre id fetched is '||l_gre);
583 if c_all_gres%notfound then
584 close c_all_gres;
585 exit;
586 end if;
587
588 hr_utility.trace('Before fetching the GREs for this Transmitter '||l_gre||'-'||p_year);
589
590
591 if l_gre <> to_number(p_trans) then
592 hr_utility.trace('Inside the loop'||l_gre_payid);
593
594 hr_utility.trace('Checking GRE level data');
595 hr_utility.trace('The Payroll Action Id for Gre is '|| l_gre_payid);
596 l_bus_no := get_arch_val(l_gre_payid,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
597 --l_tax_unit_id := get_arch_val(l_gre_payid, 'CAEOY_TAX_UNIT_ID');
598 l_acc_name := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
599 l_acc_area := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
600 l_acc_phno := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
601
602 hr_utility.trace('Tax unit Id'||l_tax_unit_id);
603 hr_utility.trace('Acc Name '||l_acc_name);
604 hr_utility.trace('Acc Area '||l_acc_area);
605 hr_utility.trace('Acc Phone '||l_acc_phno);
606 hr_utility.trace('gre namee '||l_gre_name);
607
608
609 if l_bus_no is null
610 or TRANSLATE(l_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' then
611 hr_utility.trace('No Business Number Entereed ');
612 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
613 hr_utility.set_message_token('GRE_NAME',l_gre_name);
614 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
618 end if;
615 pay_core_utils.push_token('GRE_NAME',l_gre_name);
616 hr_utility.raise_error;
617 return '1';
619
620 if (l_acc_name is null or
621 l_acc_area is null or
622 l_acc_phno is null ) and
623 l_acc_info_flag = 'N' then
624 hr_utility.trace('No Accounting Contact info present');
625 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
626 hr_utility.set_message_token('GRE_NAME',l_gre_name);
627 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
628 pay_core_utils.push_token('GRE_NAME',l_gre_name);
629 hr_utility.raise_error;
630 return '1';
631 end if;
632
633 elsif l_gre = to_number(p_trans) then
634
635 if l_trans_bus_no is null
636 or TRANSLATE(l_trans_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' then
637 hr_utility.trace('No Business Number Entereed ');
638 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
639 hr_utility.set_message_token('GRE_NAME',l_trans_name);
640 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
641 pay_core_utils.push_token('GRE_NAME',l_trans_name);
642 hr_utility.raise_error;
643 return '1';
644 end if;
645 if l_acc_info_flag = 'N' then
646 hr_utility.trace('No Accounting Contact info present');
647 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
648 hr_utility.set_message_token('GRE_NAME',l_trans_name);
649 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
650 pay_core_utils.push_token('GRE_NAME',l_trans_name);
651 hr_utility.raise_error;
652 return '1';
653 end if;
654 end if;
655 end loop;
656 return '0';
657 END validate_gre_data;
658
659 FUNCTION get_arch_val( p_context_id IN NUMBER,
660 p_user_name IN VARCHAR2)
661 RETURN varchar2 IS
662
663 cursor cur_archive (b_context_id NUMBER, b_user_name VARCHAR2) is
664 select fai.value
665 from ff_archive_items fai,
666 ff_database_items fdi
667 where fai.user_entity_id = fdi.user_entity_id
668 and fai.context1 = b_context_id
669 and fdi.user_name = b_user_name;
670
671 l_return VARCHAR2(240);
672 BEGIN
673 open cur_archive(p_context_id,p_user_name);
674 fetch cur_archive into l_return;
675 close cur_archive;
676 RETURN (l_return);
677 END ;
678 END pay_ca_t4a_mag;