[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4_MAG
Source
1 PACKAGE BODY pay_ca_t4_mag AS
2 /* $Header: pycat4mg.pkb 120.2.12010000.4 2008/09/21 09:45:07 sapalani 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.trace_on('Y','T4MAG'); */
33 hr_utility.set_location
34 ('pay_ca_t4_mag.get_report_parameters', 10);
35
36 SELECT ppa.start_date,
37 ppa.effective_date,
38 ppa.business_group_id,
39 ppa.report_type,
40 ppa.legislative_parameters
41 INTO p_year_start,
42 p_year_end,
43 p_business_group_id,
44 p_report_type,
45 p_legislative_parameters
46 FROM pay_payroll_actions ppa
47 WHERE payroll_action_id = p_pactid;
48
49 hr_utility.set_location
50 ('pay_ca_t4_mag.get_report_parameters', 20);
51
52 END get_report_parameters;
53
54
55 /*
56 Name
57 range_cursor
58 Purpose
59 This procedure defines a SQL statement
60 to fetch all the people to be included in the report. This SQL statement
61 is used to define the 'chunks' for multi-threaded operation
62 Arguments
63 p_pactid payroll action id for the report
64 p_sqlstr the SQL statement to fetch the people
65 */
66
67 PROCEDURE range_cursor (
68 p_pactid IN NUMBER,
69 p_sqlstr OUT NOCOPY VARCHAR2
70 )
71 IS
72 p_year_start DATE;
73 p_year_end DATE;
74 p_business_group_id NUMBER;
75 p_report_type VARCHAR2(30);
76 l_legislative_parameters VARCHAR2(200);
77
78 BEGIN
79
80 hr_utility.set_location( 'pay_ca_t4_mag.range_cursor', 10);
81
82 get_report_parameters(
83 p_pactid,
84 p_year_start,
85 p_year_end,
86 p_report_type,
87 p_business_group_id,
88 l_legislative_parameters
89 );
90
91 hr_utility.set_location( 'pay_ca_t4_mag.range_cursor', 20);
92
93 p_sqlstr := 'select distinct to_number(fai1.value)
94 from ff_archive_items fai1,
95 ff_database_items fdi1,
96 ff_archive_items fai2,
97 ff_database_items fdi2,
98 pay_assignment_actions paa,
99 pay_payroll_actions ppa,
100 pay_payroll_actions ppa1
101 where ppa1.payroll_action_id = :payroll_action_id
102 and ppa.business_group_id+0 = ppa1.business_group_id
103 and ppa.effective_date = ppa1.effective_date
104 and ppa.report_type = ''T4''
105 and ppa.payroll_action_id = paa.payroll_action_id
106 and fdi2.user_name = ''CAEOY_TAXATION_YEAR''
107 and fai2.user_entity_id = fdi2.user_entity_id
108 and fai2.value = pay_ca_t4_mag.get_parameter(''REPORTING_YEAR'',ppa1.legislative_parameters)
109 and paa.payroll_action_id= fai2.context1
110 and paa.action_status = ''C''
111 and paa.assignment_action_id = fai1.context1
112 and fai1.user_entity_id = fdi1.user_entity_id
113 and fdi1.user_name = ''CAEOY_PERSON_ID''
114 order by to_number(fai1.value)' ;
115
116 hr_utility.set_location( 'pay_ca_t4_mag.range_cursor',
117 30);
118
119 END range_cursor;
120
121
122 /*
123 Name
124 create_assignment_act
125 Purpose
126 Creates assignment actions for the payroll action associated with the
127 report
128 Arguments
129 p_pactid payroll action for the report
130 p_stperson starting person id for the chunk
131 p_endperson last person id for the chunk
132 p_chunk size of the chunk
133 Note
134 The procedure processes assignments in 'chunks' to facilitate
135 multi-threaded operation. The chunk is defined by the size and the
136 starting and ending person id. An interlock is also created against the
137 pre-processor assignment action to prevent rolling back of the archiver.
138 */
139
140 PROCEDURE create_assignment_act(
141 p_pactid IN NUMBER,
142 p_stperson IN NUMBER,
143 p_endperson IN NUMBER,
144 p_chunk IN NUMBER )
145 IS
146
147 /* Cursor to retrieve all the assignments for all GRE's
148 archived in a reporting year */
149
150 CURSOR c_all_asg IS
151 SELECT paf.person_id,
152 paf.assignment_id,
153 Paa.tax_unit_id,
154 paf.effective_end_date,
155 paa.assignment_action_id
156 FROM pay_payroll_actions ppa,
157 pay_assignment_actions paa,
158 per_all_assignments_f paf,
159 pay_payroll_actions ppa1
160 WHERE ppa1.payroll_action_id = p_pactid
161 AND ppa.report_type = 'T4'
162 AND ppa.business_group_id+0 = ppa1.business_group_id
163 AND ppa.effective_date = ppa1.effective_date
164 AND paa.payroll_action_id = ppa.payroll_action_id
165 AND paa.action_status = 'C'
166 AND exists ( /* Query to select all GRE 's under a transmitter GRE */
167 select 'X'
168 from
169 hr_organization_information hoi1,
170 hr_organization_information hoi
171 where hoi.organization_id = paa.tax_unit_id
172 and hoi.org_information_context = 'Canada Employer Identification'
173 and to_number(hoi.org_information11) = pay_ca_t4_mag.get_parameter('TRANSMITTER_GRE',ppa1.legislative_parameters)
174 and hoi1.org_information_context = 'Fed Magnetic Reporting'
175 and hoi.org_information5 = 'T4/RL1'
176 and hoi1.organization_id = to_number(hoi.org_information11)
177 )
178 AND paf.assignment_id = paa.assignment_id
179 AND paf.person_id BETWEEN p_stperson AND p_endperson
180 AND paf.assignment_type = 'E'
181 AND paf.effective_start_date <= ppa.effective_date
182 AND paf.effective_end_date >= ppa.start_date
183 AND paf.effective_end_date = (SELECT MAX(paf2.effective_end_date)
184 FROM per_all_assignments_f paf2
185 WHERE paf2.assignment_id = paf.assignment_id
186 AND paf2.effective_start_date <= ppa.effective_date )
187 ORDER BY paf.person_id;
188
189
190 /* local variables */
191
192 l_year_start DATE;
193 l_year_end DATE;
194 l_effective_end_date DATE;
195 l_report_type VARCHAR2(30);
196 l_business_group_id NUMBER;
197 l_person_id NUMBER;
198 l_assignment_id NUMBER;
199 l_assignment_action_id NUMBER;
200 l_value NUMBER;
201 l_tax_unit_id NUMBER;
202 lockingactid NUMBER;
203
204 l_trans_gre VARCHAR2(10);
205 l_validate_gre VARCHAR2(10);
206 l_legislative_parameters VARCHAR2(200);
207
208 BEGIN
209
210 /* Get the report parameters. These define the report being run.*/
211
212 hr_utility.set_location( 'pay_ca_t4_mag.create_assignement_act',10);
213
214 get_report_parameters(
215 p_pactid,
216 l_year_start,
217 l_year_end,
218 l_report_type,
219 l_business_group_id,
220 l_legislative_parameters
221 );
222
223 /* Open the appropriate cursor */
224
225 l_trans_gre := pay_ca_t4a_mag.get_parameter('TRANSMITTER_GRE',
226 l_legislative_parameters);
227 hr_utility.trace('l_trans_gre ='||l_trans_gre);
228 l_validate_gre := pay_ca_t4_mag.validate_gre_data(l_trans_gre, to_char(l_year_end,'YYYY'));
229 hr_utility.set_location( 'pay_ca_t4_mag.create_assignement_act',20);
230
231 IF l_report_type = 'PYT4MAG' THEN
232 OPEN c_all_asg;
233 LOOP
234 FETCH c_all_asg INTO l_person_id,
235 l_assignment_id,
236 l_tax_unit_id,
237 l_effective_end_date,
238 l_assignment_action_id;
239
240 hr_utility.set_location(
241 'pay_ca_t4_mag.create_assignement_act', 30);
242
243 EXIT WHEN c_all_asg%NOTFOUND;
244
245
246 /* Create the assignment action for the record */
247
248 hr_utility.trace('Assignment Fetched - ');
249 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
250 hr_utility.trace('Person Id : '|| to_char(l_person_id));
251 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
252 hr_utility.trace('Effective End Date : '||
253 to_char(l_effective_end_date));
254
255 hr_utility.set_location(
256 'pay_ca_t4_mag.create_assignement_act', 40);
257
258 SELECT pay_assignment_actions_s.nextval
259 INTO lockingactid
260 FROM dual;
261
262 hr_utility.set_location(
263 'pay_ca_t4_mag.create_assignement_act', 50);
264
265 hr_nonrun_asact.insact(lockingactid, l_assignment_id, p_pactid,p_chunk, l_tax_unit_id);
266
267 hr_utility.set_location(
268 'pay_ca_t4_mag.create_assignement_act', 60);
269
270 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
271
272 hr_utility.set_location(
273 'pay_ca_t4_mag.create_assignement_act', 70);
274
275 hr_utility.trace('Interlock Created - ');
276 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
277 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
278
279 END LOOP;
280 Close c_all_asg;
281 END IF;
282
283 END create_assignment_act;
284
285 function get_parameter(name in varchar2,
286 parameter_list varchar2) return varchar2
287 is
288 start_ptr number;
289 end_ptr number;
290 token_val pay_payroll_actions.legislative_parameters%type;
291 par_value pay_payroll_actions.legislative_parameters%type;
292 begin
293
294 token_val := name||'=';
295
296 start_ptr := instr(parameter_list, token_val) + length(token_val);
297 end_ptr := instr(parameter_list, ' ',start_ptr);
298
299 /* if there is no spaces use then length of the string */
300 if end_ptr = 0 then
301 end_ptr := length(parameter_list)+1;
302 end if;
303
304 /* Did we find the token */
305 if instr(parameter_list, token_val) = 0 then
306 par_value := NULL;
307 else
308 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
309 end if;
310
311 return par_value;
312
313 end get_parameter;
314
315
316 function get_dbitem_value(p_asg_act_id in number,
317 p_dbitem_name in varchar2,
318 p_jurisdiction varchar2 default null) return varchar2
319 is
320 lv_value varchar2(60);
321
322 cursor c_get_dbitem_value(cp_dbitem varchar2,
323 cp_jurisdiction varchar2) is
324 SELECT fai1.value
325 FROM FF_ARCHIVE_ITEMS FAI1,
326 ff_database_items fdi1,
327 ff_archive_item_contexts faic,
328 ff_contexts fc
329 WHERE FAI1.USER_ENTITY_ID = fdi1.user_entity_id
330 and fdi1.user_name = cp_dbitem
331 and fai1.archive_item_id = faic.archive_item_id
332 and fc.context_id = faic.context_id
333 and fc.context_name = 'JURISDICTION_CODE'
334 and faic.context = cp_jurisdiction
335 AND FAI1.CONTEXT1 = p_asg_act_id;
336
337 begin
338
339 open c_get_dbitem_value(p_dbitem_name,
340 p_jurisdiction);
341 fetch c_get_dbitem_value into lv_value;
342 if c_get_dbitem_value%NOTFOUND then
343 lv_value := 'ZZZ';
344 end if;
345 close c_get_dbitem_value;
346
347 return lv_value;
348
349 end;
350
351 FUNCTION convert_2_xml(p_data IN varchar2,
352 p_tag IN varchar2,
353 p_datatype IN char default 'T',
354 p_format IN varchar2 default NULL,
355 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
356 return varchar2 is
357
358 l_data varchar2(4000);
359 l_output varchar2(4000);
360 BEGIN
361 if p_null_allowed = 'N'
362 and (TRIM(p_data) is null or (p_datatype in ('N','C') and to_number(p_data) = 0)) then
363 return ' ';
364 end if;
365
366 l_data := trim(p_data);
367 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
368 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
369 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
370 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
371 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
372 --------------------------------------------------------
373 --- P_Datatype: T = Text, N = Number, C=Currency, D=Date
374 --------------------------------------------------------
375 IF p_datatype = 'T' or p_datatype = 'D' then
376 l_output := '<' || trim(p_tag) || '>' || trim(l_data) || '</' || trim(p_tag) || '>
377 ';
378 ELSIF p_datatype = 'N' or p_datatype = 'C' then
379 IF TRIM(p_format) is not null then
380 select to_char(to_number(p_data), p_format)
381 into l_data from dual;
385 END IF;
382 ELSIF p_datatype = 'C' then -- Currency should be two decimal places
383 select to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
384 into l_data from dual;
386 l_output := '<' || trim(p_tag) || '>' || trim(l_data) || '</' || trim(p_tag) || '>
387 ';
388 END IF;
389
390 return l_output;
391 END;
392
393 FUNCTION get_arch_val( p_context_id IN NUMBER,
394 p_user_name IN VARCHAR2)
395 RETURN varchar2 IS
396
397 cursor cur_archive (b_context_id NUMBER, b_user_name VARCHAR2) is
398 select fai.value
399 from ff_archive_items fai,
400 ff_database_items fdi
401 where fai.user_entity_id = fdi.user_entity_id
402 and fai.context1 = b_context_id
403 and fdi.user_name = b_user_name;
404
405 l_return VARCHAR2(240);
406 BEGIN
407 open cur_archive(p_context_id,p_user_name);
408 fetch cur_archive into l_return;
409 close cur_archive;
410 RETURN (l_return);
411 END ;
412
413 /* Function convert_t4_oth_info_amt
414 - For Bug 6855236
415 - To process the other info amounts for T4 Magnetic Media.
416 - Call to this function is made in fast formula T4_EMPLOYEE.
417 - Formatted XML strings for other info amounts are returned through out paramaters
418 - Additionally formatted strings for .a03 file are returned through out paramaters
419 */
420
421 FUNCTION convert_t4_oth_info_amt(p_assignment_action_id IN Number,
422 p_payroll_action_id IN Number,
423 p_fail IN char,
424 p_oth_rep1 OUT nocopy varchar2,
425 p_oth_rep2 OUT nocopy varchar2,
426 p_oth_rep3 OUT nocopy varchar2,
427 p_write_f31 OUT nocopy varchar2,
428 p_transfer_other_info1_str1 OUT nocopy varchar2,
429 p_transfer_other_info1_str2 OUT nocopy varchar2,
430 p_transfer_other_info1_str3 OUT nocopy varchar2,
431 p_transfer_other_info2_str1 OUT nocopy varchar2,
432 p_transfer_other_info2_str2 OUT nocopy varchar2,
433 p_transfer_other_info2_str3 OUT nocopy varchar2,
434 p_transfer_other_info3_str1 OUT nocopy varchar2,
435 p_transfer_other_info3_str2 OUT nocopy varchar2,
436 p_transfer_other_info3_str3 OUT nocopy varchar2,
437 p_transfer_other_info4_str1 OUT nocopy varchar2,
438 p_transfer_other_info4_str2 OUT nocopy varchar2,
439 p_transfer_other_info4_str3 OUT nocopy varchar2,
440 p_transfer_oth1_rep1 OUT nocopy varchar2,
441 p_transfer_oth1_rep2 OUT nocopy varchar2,
442 p_transfer_oth1_rep3 OUT nocopy varchar2,
443 p_transfer_oth2_rep2 OUT nocopy varchar2,
444 p_transfer_oth2_rep3 OUT nocopy varchar2,
445 p_transfer_oth3_rep2 OUT nocopy varchar2,
446 p_transfer_oth3_rep3 OUT nocopy varchar2,
447 p_transfer_oth4_rep3 OUT nocopy varchar2,
448 p_cnt OUT nocopy Number)
449 return varchar2 is
450
451 l_other_info varchar2(100);
452 l_cnt Number :=0;
453 l_amt Number :=0;
454
455 l_write_f30 varchar2(400) := ' ';
456 l_write_f31 varchar2(400) := ' ';
457 l_oth_rep1 varchar2(400);
458 l_oth_rep2 varchar2(400);
459 l_oth_rep3 varchar2(400);
460 l_transfer_other_info1_str1 varchar2(400);
461 l_transfer_other_info1_str2 varchar2(400);
462 l_transfer_other_info1_str3 varchar2(400);
463 l_transfer_other_info2_str1 varchar2(400);
464 l_transfer_other_info2_str2 varchar2(400);
465 l_transfer_other_info2_str3 varchar2(400);
466 l_transfer_other_info3_str1 varchar2(400);
467 l_transfer_other_info3_str2 varchar2(400);
468 l_transfer_other_info3_str3 varchar2(400);
469 l_transfer_other_info4_str1 varchar2(400);
470 l_transfer_other_info4_str2 varchar2(400);
471 l_transfer_other_info4_str3 varchar2(400);
472 l_transfer_oth1_rep1 varchar2(400);
473 l_transfer_oth1_rep2 varchar2(400);
474 l_transfer_oth1_rep3 varchar2(400);
475 l_transfer_oth2_rep2 varchar2(400);
476 l_transfer_oth2_rep3 varchar2(400);
477 l_transfer_oth3_rep2 varchar2(400);
478 l_transfer_oth3_rep3 varchar2(400);
479 l_transfer_oth4_rep3 varchar2(400);
480
481 type string_table is table of varchar2(50) index by binary_integer;
482 t_dbi string_table;
483 t_tag string_table;
484
485 BEGIN
486
487 /* DBIs of other info amounts */
488 t_dbi(1) := 'CAEOY_T4_OTHER_INFO_AMOUNT30_PER_JD_GRE_YTD';
489 t_dbi(2) := 'CAEOY_T4_OTHER_INFO_AMOUNT31_PER_JD_GRE_YTD';
490 t_dbi(3) := 'CAEOY_T4_OTHER_INFO_AMOUNT32_PER_JD_GRE_YTD';
494 t_dbi(7) := 'CAEOY_T4_OTHER_INFO_AMOUNT36_PER_JD_GRE_YTD';
491 t_dbi(4) := 'CAEOY_T4_OTHER_INFO_AMOUNT33_PER_JD_GRE_YTD';
492 t_dbi(5) := 'CAEOY_T4_OTHER_INFO_AMOUNT34_PER_JD_GRE_YTD';
493 t_dbi(6) := 'CAEOY_T4_OTHER_INFO_AMOUNT35_PER_JD_GRE_YTD';
495 t_dbi(8) := 'CAEOY_T4_OTHER_INFO_AMOUNT37_PER_JD_GRE_YTD';
496 t_dbi(9) := 'CAEOY_T4_OTHER_INFO_AMOUNT38_PER_JD_GRE_YTD';
497 t_dbi(10) := 'CAEOY_T4_OTHER_INFO_AMOUNT39_PER_JD_GRE_YTD';
498 t_dbi(11) := 'CAEOY_T4_OTHER_INFO_AMOUNT40_PER_JD_GRE_YTD';
499 t_dbi(12) := 'CAEOY_T4_OTHER_INFO_AMOUNT41_PER_JD_GRE_YTD';
500 t_dbi(13) := 'CAEOY_T4_OTHER_INFO_AMOUNT42_PER_JD_GRE_YTD';
501 t_dbi(14) := 'CAEOY_T4_OTHER_INFO_AMOUNT43_PER_JD_GRE_YTD';
502 t_dbi(15) := 'CAEOY_T4_OTHER_INFO_AMOUNT53_PER_JD_GRE_YTD';
503 t_dbi(16) := 'CAEOY_T4_OTHER_INFO_AMOUNT70_PER_JD_GRE_YTD';
504 t_dbi(17) := 'CAEOY_T4_OTHER_INFO_AMOUNT71_PER_JD_GRE_YTD';
505 t_dbi(18) := 'CAEOY_T4_OTHER_INFO_AMOUNT72_PER_JD_GRE_YTD';
506 t_dbi(19) := 'CAEOY_T4_OTHER_INFO_AMOUNT73_PER_JD_GRE_YTD';
507 t_dbi(20) := 'CAEOY_T4_OTHER_INFO_AMOUNT74_PER_JD_GRE_YTD';
508 t_dbi(21) := 'CAEOY_T4_OTHER_INFO_AMOUNT75_PER_JD_GRE_YTD';
509 t_dbi(22) := 'CAEOY_T4_OTHER_INFO_AMOUNT77_PER_JD_GRE_YTD';
510 t_dbi(23) := 'CAEOY_T4_OTHER_INFO_AMOUNT78_PER_JD_GRE_YTD';
511 t_dbi(24) := 'CAEOY_T4_OTHER_INFO_AMOUNT79_PER_JD_GRE_YTD';
512 t_dbi(25) := 'CAEOY_T4_OTHER_INFO_AMOUNT80_PER_JD_GRE_YTD';
513 t_dbi(26) := 'CAEOY_T4_OTHER_INFO_AMOUNT81_PER_JD_GRE_YTD';
514 t_dbi(27) := 'CAEOY_T4_OTHER_INFO_AMOUNT82_PER_JD_GRE_YTD';
515 t_dbi(28) := 'CAEOY_T4_OTHER_INFO_AMOUNT83_PER_JD_GRE_YTD';
516 t_dbi(29) := 'CAEOY_T4_OTHER_INFO_AMOUNT84_PER_JD_GRE_YTD';
517 t_dbi(30) := 'CAEOY_T4_OTHER_INFO_AMOUNT85_PER_JD_GRE_YTD';
518
519 /* XML Tags for corresponding other info amounts*/
520 t_tag(1) := 'hm_brd_lodg_amt';
521 t_tag(2) := 'spcl_wrk_site_amt';
522 t_tag(3) := 'prscb_zn_trvl_amt';
523 t_tag(4) := 'med_trvl_amt';
524 t_tag(5) := 'prsnl_vhcl_amt';
525 t_tag(6) := 'rsn_per_km_amt';
526 t_tag(7) := 'low_int_loan_amt';
527 t_tag(8) := 'empe_hm_loan_amt';
528 t_tag(9) := 'sob_a00_feb_amt';
529 t_tag(10) := 'sod_d_a00_feb_amt';
530 t_tag(11) := 'oth_tx_ben_amt';
531 t_tag(12) := 'sod_d1_a00_feb_amt';
532 t_tag(13) := 'empt_cmsn_amt';
533 t_tag(14) := 'cfppa_amt';
534 t_tag(15) := 'dfr_sob_amt';
535 t_tag(16) := 'mun_ofcr_examt';
536 t_tag(17) := 'indn_empe_amt';
537 t_tag(18) := 'oc_incamt';
538 t_tag(19) := 'oc_dy_cnt';
539 t_tag(20) := 'pr_90_cntrbr_amt';
540 t_tag(21) := 'pr_90_ncntrbr_amt';
541 t_tag(22) := 'cmpn_rpay_empr_amt';
542 t_tag(23) := 'fish_gro_ern_amt';
543 t_tag(24) := 'fish_net_ptnr_amt';
544 t_tag(25) := 'fish_shr_prsn_amt';
545 t_tag(26) := 'plcmt_emp_agcy_amt';
546 t_tag(27) := 'drvr_taxis_oth_amt';
547 t_tag(28) := 'brbr_hrdrssr_amt';
548 t_tag(29) := 'pub_trnst_pass_amt';
549 t_tag(30) := 'epaid_hlth_pln_amt';
550
551 l_transfer_oth1_rep1 := rpad(lpad('.00,',12), 6*12, lpad('.00,',12));
552 --l_transfer_oth2_rep1 := rpad(lpad('.00,',12), 10*12, lpad('.00,',12));
553 --l_transfer_oth2_rep2 := rpad(lpad('.00,',10), 2*10, lpad('.00,',10));
554 --l_transfer_oth3_rep1 := rpad(lpad('.00,',12), 10*12, lpad('.00,',12));
555 l_transfer_oth3_rep2 := rpad(lpad('.00,',12), 6*12, lpad('.00,',12));
556 --l_transfer_oth4_rep1 := rpad(lpad('.00,',12), 10*12, lpad('.00,',12));
557 --l_transfer_oth4_rep2 := rpad(lpad('.00,',12), 10*12, lpad('.00,',12));
558 l_transfer_oth4_rep3 := rpad(lpad('.00,',12), 4*12, lpad('.00,',12));
559
560 hr_utility.trace('p_assignment_action_id = '||p_assignment_action_id);
561 hr_utility.trace('p_payroll_action_id = '||p_payroll_action_id);
562 hr_utility.trace('p_fail = '||p_fail);
563
564 for i in 1..t_dbi.COUNT
565 loop
566 l_amt := fnd_number.canonical_to_number(get_arch_val(p_assignment_action_id,t_dbi(i)));
567 if (p_fail <> 'Y') and (l_amt >0) then
568 if(i=19) then
569 l_other_info := CONVERT_2_XML(l_amt, t_tag(i), 'N'); --For oth. code 73 (<oc_dy_cnt>)
570 else
571 l_other_info := CONVERT_2_XML(l_amt, t_tag(i), 'C'); -- Bug 7424296
572 end if;
573 l_cnt := l_cnt+1;
574
575 hr_utility.trace('l_other_info = '||l_other_info);
576 hr_utility.trace('l_cnt = '||l_cnt);
577
578 if l_cnt <= 3 then
579 l_write_f30 := l_write_f30||l_other_info;
580 elsif l_cnt <=6 then
581 l_write_f31 := l_write_f31||l_other_info;
582 elsif l_cnt <= 8 then
583 l_transfer_other_info1_str1 := l_transfer_other_info1_str1 || l_other_info;
584 elsif l_cnt <= 10 then
585 l_transfer_other_info1_str2 := l_transfer_other_info1_str2 || l_other_info;
586 elsif l_cnt <= 12 then
587 l_transfer_other_info1_str3 := l_transfer_other_info1_str3 || l_other_info;
588 elsif l_cnt <= 14 then
589 l_transfer_other_info2_str1 := l_transfer_other_info2_str1 || l_other_info;
590 elsif l_cnt <= 16 then
591 l_transfer_other_info2_str2 := l_transfer_other_info2_str2 || l_other_info;
592 elsif l_cnt <= 18 then
593 l_transfer_other_info2_str3 := l_transfer_other_info2_str3 || l_other_info;
594 elsif l_cnt <= 20 then
595 l_transfer_other_info3_str1 := l_transfer_other_info3_str1 || l_other_info;
596 elsif l_cnt <= 22 then
597 l_transfer_other_info3_str2 := l_transfer_other_info3_str2 || l_other_info;
598 elsif l_cnt <= 24 then
599 l_transfer_other_info3_str3 := l_transfer_other_info3_str3 || l_other_info;
600 elsif l_cnt <= 26 then
601 l_transfer_other_info4_str1 := l_transfer_other_info4_str1 || l_other_info;
602 elsif l_cnt <= 28 then
603 l_transfer_other_info4_str2 := l_transfer_other_info4_str2 || l_other_info;
604 else
605 l_transfer_other_info4_str3 := l_transfer_other_info4_str3 || l_other_info;
606 end if;
607 end if;
608
609 /* Formatting strings for .a03 audit report */
610 if i <=6 then
611 l_oth_rep1 := l_oth_rep1 || to_char(l_amt, '9999999.99') ||',';
612 elsif i <=12 then
613 if p_fail = 'Y' or l_cnt <= 6 then
614 l_oth_rep1 := l_oth_rep1 || to_char(l_amt, '9999999.99') ||',';
615 l_transfer_oth1_rep1 := l_transfer_oth1_rep1 || lpad('.00,',12);
616 else
617 l_oth_rep1 := l_oth_rep1 || lpad('.00,',12);
618 l_transfer_oth1_rep1 := l_transfer_oth1_rep1 || to_char(nvl(l_amt,0), '9999999.99') ||',';
619 end if;
620 elsif i <=18 then
621 if p_fail = 'Y' or l_cnt <= 6 then
622 l_oth_rep2 := l_oth_rep2 || to_char(l_amt, '9999999.99') ||',';
623 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
624 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
625 elsif l_cnt <= 12 then
626 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
627 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || to_char(l_amt, '9999999.99') ||',';
628 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
629 else
630 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
631 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
632 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || to_char(l_amt, '9999999.99') ||',';
633 end if;
634 elsif i <=24 then
635 if p_fail = 'Y' or l_cnt <= 6 then
636 if i =19 then
637 l_oth_rep2 := l_oth_rep2 || lpad( to_char(l_amt, '999') ||',',12);
638 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('0,',12);
639 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('0,',12);
640 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('0,',12);
641 else
645 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
642 l_oth_rep2 := l_oth_rep2 || to_char(l_amt, '9999999.99') ||',';
643 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
644 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
646 end if;
647 elsif l_cnt <= 12 then
648 if i =19 then
649 l_oth_rep2 := l_oth_rep2 || lpad('0,',12);
650 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad(to_char(l_amt,'999')||',',12);
651 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('0,',12);
652 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('0,',12);
653 else
654 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
655 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || to_char(l_amt, '9999999.99') ||',';
656 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
657 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
658 end if;
659 elsif l_cnt <= 18 then
660 if i =19 then
661 l_oth_rep2 := l_oth_rep2 || lpad('0,',12);
662 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('0,',12);
663 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad(to_char(l_amt, '999') ||',',12);
664 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('0,',12);
665 else
666 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
667 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
668 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || to_char(l_amt, '9999999.99') ||',';
669 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
670 end if;
671 else
672 if i =19 then
673 l_oth_rep2 := l_oth_rep2 || lpad('0,',12);
674 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('0,',12);
675 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('0,',12);
676 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad(to_char(l_amt, '999') ||',',12);
677 else
678 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
679 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
680 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
681 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || to_char(l_amt, '9999999.99') ||',';
682 end if;
683 end if;
684 elsif i <=30 then
685 if p_fail = 'Y' or l_cnt <= 6 then
686 l_oth_rep3 := l_oth_rep3 || to_char(l_amt, '9999999.99') ||',';
687 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
688 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
689 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
690 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
691 elsif l_cnt <= 12 then
692 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
693 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || to_char(l_amt, '9999999.99') ||',';
694 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
695 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
696 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
697 elsif l_cnt <= 18 then
698 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
699 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
700 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || to_char(l_amt, '9999999.99') ||',';
701 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
702 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
703 elsif l_cnt <= 24 then
704 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
705 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
706 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
707 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || to_char(l_amt, '9999999.99') ||',';
708 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
709 else
710 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
711 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
712 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
713 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
714 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || to_char(l_amt, '9999999.99') ||',';
715 end if;
716 end if;
717 end loop;
718
719 p_cnt := l_cnt;
720 p_oth_rep1 := l_oth_rep1;
721 p_oth_rep2 := l_oth_rep2;
722 p_oth_rep3 := l_oth_rep3;
723 p_write_f31 := l_write_f31;
724 p_transfer_other_info1_str1 := l_transfer_other_info1_str1;
725 p_transfer_other_info1_str2 := l_transfer_other_info1_str2;
726 p_transfer_other_info1_str3 := l_transfer_other_info1_str3;
727 p_transfer_other_info2_str1 := l_transfer_other_info2_str1;
728 p_transfer_other_info2_str2 := l_transfer_other_info2_str2;
729 p_transfer_other_info2_str3 := l_transfer_other_info2_str3;
730 p_transfer_other_info3_str1 := l_transfer_other_info3_str1;
731 p_transfer_other_info3_str2 := l_transfer_other_info3_str2;
732 p_transfer_other_info3_str3 := l_transfer_other_info3_str3;
736 p_transfer_oth1_rep1 := l_transfer_oth1_rep1;
733 p_transfer_other_info4_str1 := l_transfer_other_info4_str1;
734 p_transfer_other_info4_str2 := l_transfer_other_info4_str2;
735 p_transfer_other_info4_str3 := l_transfer_other_info4_str3;
737 p_transfer_oth1_rep2 := l_transfer_oth1_rep2;
738 p_transfer_oth1_rep3 := l_transfer_oth1_rep3;
739 p_transfer_oth2_rep2 := l_transfer_oth2_rep2;
740 p_transfer_oth2_rep3 := l_transfer_oth2_rep3;
741 p_transfer_oth3_rep2 := l_transfer_oth3_rep2;
742 p_transfer_oth3_rep3 := l_transfer_oth3_rep3;
743 p_transfer_oth4_rep3 := l_transfer_oth4_rep3;
744
745 /*
746 hr_utility.trace('p_cnt = '|| l_cnt);
747 hr_utility.trace('p_oth_rep1 = '|| l_oth_rep1);
748 hr_utility.trace('p_oth_rep2 = '|| l_oth_rep2);
749 hr_utility.trace('p_oth_rep3 = '|| l_oth_rep3);
750 hr_utility.trace('write_f30 = '|| l_write_f30);
751 hr_utility.trace('p_write_f31 = '|| l_write_f31);
752 hr_utility.trace('p_transfer_other_info1_str1 = '|| l_transfer_other_info1_str1);
753 hr_utility.trace('p_transfer_other_info1_str2 = '|| l_transfer_other_info1_str2);
754 hr_utility.trace('p_transfer_other_info1_str3 = '|| l_transfer_other_info1_str3);
755 hr_utility.trace('p_transfer_other_info2_str1 = '|| l_transfer_other_info2_str1);
756 hr_utility.trace('p_transfer_other_info2_str2 = '|| l_transfer_other_info2_str2);
757 hr_utility.trace('p_transfer_other_info2_str3 = '|| l_transfer_other_info2_str3);
758 hr_utility.trace('p_transfer_other_info3_str1 = '|| l_transfer_other_info3_str1);
759 hr_utility.trace('p_transfer_other_info3_str2 = '|| l_transfer_other_info3_str2);
760 hr_utility.trace('p_transfer_other_info3_str3 = '|| l_transfer_other_info3_str3);
761 hr_utility.trace('p_transfer_other_info4_str1 = '|| l_transfer_other_info4_str1);
762 hr_utility.trace('p_transfer_other_info4_str2 = '|| l_transfer_other_info4_str2);
763 hr_utility.trace('p_transfer_other_info4_str3 = '|| l_transfer_other_info4_str3);
764 hr_utility.trace('p_transfer_oth1_rep1 = '|| l_transfer_oth1_rep1);
765 hr_utility.trace('p_transfer_oth1_rep2 = '|| l_transfer_oth1_rep2);
766 hr_utility.trace('p_transfer_oth1_rep3 = '|| l_transfer_oth1_rep3);
767 hr_utility.trace('p_transfer_oth2_rep2 = '|| l_transfer_oth2_rep2);
768 hr_utility.trace('p_transfer_oth2_rep3 = '|| l_transfer_oth2_rep3);
769 hr_utility.trace('p_transfer_oth3_rep2 = '|| l_transfer_oth3_rep2);
770 hr_utility.trace('p_transfer_oth3_rep3 = '|| l_transfer_oth3_rep3);
771 hr_utility.trace('p_transfer_oth4_rep3 = '|| l_transfer_oth4_rep3);
772
773 */
774
775 return l_write_f30;
776
777 END;
778
779 FUNCTION validate_gre_data ( p_trans IN VARCHAR2,
780 p_year IN VARCHAR2)
781 RETURN varchar2 IS
782
783 cursor c_trans_payid ( c_trans_id VARCHAR2,
784 c_year VARCHAR2) is
785 Select ppa.payroll_action_id, ppa.business_group_id
786 from hr_organization_information hoi,
787 pay_payroll_actions PPA,
788 pay_ca_legislation_info pcli,
789 pay_ca_legislation_info pcli1
790 where hoi.organization_id = to_number(c_trans_id)
791 and hoi.org_information_context='Fed Magnetic Reporting'
792 and ppa.report_type = 'T4' -- T4 Archiver Report Type
793 and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='))
794 and to_char(ppa.effective_date,'YYYY')= c_year
795 and to_char(ppa.effective_date,'DD-MM')= '31-12'
796 and pcli.information_type = 'MAX_CPP_EARNINGS'
797 and ppa.effective_date between pcli.start_date and pcli.end_date
798 and pcli1.information_type = 'MAX_EI_EARNINGS'
799 and ppa.effective_date between pcli1.start_date and pcli1.end_date;
800
801 cursor c_all_gres(p_trans VARCHAR2,
802 p_year VARCHAR2,
803 p_bg_id NUMBER) is
804 Select distinct ppa.payroll_action_id, hoi.organization_id, hou.name
805 From pay_payroll_actions ppa,
806 hr_organization_information hoi,
807 hr_all_organization_units hou
808 where hoi.org_information_context = 'Canada Employer Identification'
809 and hoi.org_information11 = p_trans
810 and hou.business_group_id = p_bg_id
811 and hou.organization_id = hoi.organization_id
812 and ppa.report_type = 'T4'
813 and ppa.effective_date = to_date('31-12'||p_year,'DD-MM-YYYY')
814 and ppa.business_group_id = p_bg_id
815 and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='));
816
817 cursor c_gre_name (b_org_id VARCHAR2) is
818 select hou.name
819 from hr_all_organization_units hou
820 where hou.organization_id = to_number(b_org_id);
821
822 /* Local variables */
823 l_trans_gre hr_all_organization_units.organization_id%TYPE;
824 l_year VARCHAR2(10);
825 l_gre hr_all_organization_units.organization_id%TYPE;
826 l_bus_grp hr_all_organization_units.business_group_id%TYPE;
827 l_trans_no VARCHAR2(240);
828 l_tech_name VARCHAR2(240) ;
829 l_tech_area VARCHAR2(240) ;
830 l_tech_phno VARCHAR2(240) ;
831 l_lang VARCHAR2(240) ;
832 l_acc_name VARCHAR2(240) ;
833 l_acc_area VARCHAR2(240) ;
834 l_acc_phno VARCHAR2(240) ;
835 l_trans_bus_no VARCHAR2(240);
836 l_trans_name VARCHAR2(240);
837 l_bus_no VARCHAR2(240) ;
838 l_bg_id number ;
839 l_trans_payid pay_payroll_actions.payroll_action_id%TYPE;
840 l_gre_payid pay_payroll_actions.payroll_action_id%TYPE;
841 l_gre_actid pay_assignment_actions.assignment_action_id%TYPE;
842 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
846 BEGIN
843 l_acc_info_flag CHAR(1);
844 l_gre_name VARCHAR2(240);
845
847
848 /* Fetching the Payroll Action Id for Trasnmitter GRE */
849
850 --hr_utility.trace_on(null,'T4MAG');
851 hr_utility.trace('Inside the Validation Code');
852 hr_utility.trace('The Transmitter GRE id passed is '||p_trans);
853 open c_trans_payid(p_trans,p_year);
854 fetch c_trans_payid into l_trans_payid,l_bg_id;
855 IF c_trans_payid%notfound THEN
856 close c_trans_payid;
857 hr_utility.trace('The Transmitter GRE id not found '||p_trans);
858 hr_utility.raise_error;
859 return '1';
860 else
861 close c_trans_payid;
862 END IF;
863
864 hr_utility.trace('Fetched the Payroll Id for transmitter GRE'|| l_trans_payid);
865 hr_utility.trace('The Reporting Year is '||p_year);
866
867 /*Fetching the Trasnmitter Level Data */
868
869 l_trans_no := get_arch_val(l_trans_payid, 'CAEOY_TRANSMITTER_NUMBER');
870 l_tech_name:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_NAME');
871 l_tech_area:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_AREA_CODE');
872 l_tech_phno:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_PHONE');
873 l_lang := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_LANGUAGE');
874 l_acc_name := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
875 l_acc_area := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
876 l_acc_phno := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
877 l_trans_bus_no := get_arch_val(l_trans_payid, 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
878 -- l_trans_name := get_arch_val(l_trans_payid, 'CAEOY_TRANSMITTER_NAME');
879 OPEN c_gre_name(to_number(p_trans));
880 FETCH c_gre_name INTO l_trans_name;
881 CLOSE c_gre_name;
882
883 hr_utility.trace('Transmitter Number'||l_trans_no);
884 hr_utility.trace('Tech Name'||l_tech_name);
885 hr_utility.trace('Tech Phno'||l_tech_phno);
886 hr_utility.trace('Tech area'||l_tech_area);
887 hr_utility.trace('Tech Lang'||l_lang);
888
889 /* Checking for the validity of the above values fetched */
890 hr_utility.trace('Checking the Transmitter No ');
891 IF l_trans_no IS NULL
892 OR TRANSLATE(l_trans_no,'M0123456789','M9999999999') <> 'MM999999' THEN
893 hr_utility.trace('Incorrect Transmitter No format');
894 hr_utility.set_message(801,'PAY_74155_INCORRECT_TRANSMT_NO');
895 hr_utility.set_message_token('GRE_NAME',l_trans_name);
896 pay_core_utils.push_message(801,'PAY_74155_INCORRECT_TRANSMT_NO','P');
897 pay_core_utils.push_token('GRE_NAME',l_trans_name);
898 hr_utility.raise_error;
899 return '1';
900 END IF;
901
902 if l_tech_name is null or
903 l_tech_area is null or
904 l_tech_phno is null or
905 l_lang is null then
906 hr_utility.trace('Technical contact details missing');
907 hr_utility.set_message(801,'PAY_74158_INCORRECT_TCHN_INFO');
908 hr_utility.set_message_token('GRE_NAME',l_trans_name);
909 pay_core_utils.push_message(801,'PAY_74158_INCORRECT_TCHN_INFO','P');
910 pay_core_utils.push_token('GRE_NAME',l_trans_name);
911 hr_utility.raise_error;
912 return '1';
913 end if;
914
915 if l_acc_name is null or
916 l_acc_phno is null or
917 l_acc_area is null then
918 l_acc_info_flag := 'N';
919 else
920 l_acc_info_flag := 'Y';
921 end if;
922 hr_utility.trace('The value of the Flag is '||l_acc_info_flag);
923 hr_utility.trace('The value of the bgid '||to_char(l_bg_id));
924
925
926 /* Checking for the GRE level information */
927
928 open c_all_gres(p_trans,p_year,l_bg_id);
929 loop
930 fetch c_all_gres into l_gre_payid, l_gre, l_gre_name;
931 hr_utility.trace('The Gre id fetched is '||l_gre);
932 if c_all_gres%notfound then
933 close c_all_gres;
934 exit;
935 end if;
936
937 hr_utility.trace('Before fetching the GREs for this Transmitter '||l_gre||'-'||p_year);
938
939 if l_gre <> to_number(p_trans) then
940 hr_utility.trace('Inside the loop'||l_gre_payid);
941 hr_utility.trace('Checking GRE level data');
942 hr_utility.trace('The Payroll Action Id for Gre is '|| l_gre_payid);
943 l_bus_no := get_arch_val(l_gre_payid,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
944 --l_tax_unit_id := get_arch_val(l_gre_payid, 'CAEOY_TAX_UNIT_ID');
945 l_acc_name := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
946 l_acc_area := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
947 l_acc_phno := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
948
949 hr_utility.trace('Tax unit Id'||l_tax_unit_id);
950 hr_utility.trace('Acc Name '||l_acc_name);
951 hr_utility.trace('Acc Area '||l_acc_area);
952 hr_utility.trace('Acc Phone '||l_acc_phno);
953
954 if l_bus_no is null
955 or TRANSLATE(l_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' then
956 hr_utility.trace('No Business Number Entereed ');
957 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
958 hr_utility.set_message_token('GRE_NAME',l_gre_name);
959 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
960 pay_core_utils.push_token('GRE_NAME',l_gre_name);
961 hr_utility.raise_error;
962 return '1';
963 end if;
964
965 if (l_acc_name is null or
966 l_acc_area is null or
967 l_acc_phno is null ) and
968 l_acc_info_flag = 'N' then
969 hr_utility.trace('No Accounting Contact info present');
970 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
971 hr_utility.set_message_token('GRE_NAME',l_gre_name);
972 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
973 pay_core_utils.push_token('GRE_NAME',l_gre_name);
974 hr_utility.raise_error;
975 return '1';
976 end if;
977
978 elsif l_gre = to_number(p_trans) then
979
980 if l_trans_bus_no is null
981 or TRANSLATE(l_trans_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' then
982 hr_utility.trace('No Business Number Entereed ');
983 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
984 hr_utility.set_message_token('GRE_NAME',l_trans_name);
985 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
986 pay_core_utils.push_token('GRE_NAME',l_trans_name);
987 hr_utility.raise_error;
988 return '1';
989 end if;
990 if l_acc_info_flag = 'N' then
991 hr_utility.trace('No Accounting Contact info present');
992 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
993 hr_utility.set_message_token('GRE_NAME',l_trans_name);
994 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
995 pay_core_utils.push_token('GRE_NAME',l_trans_name);
996 hr_utility.raise_error;
997 return '1';
998 end if;
999 end if;
1000 end loop;
1001 RETURN '0';
1002 END validate_gre_data;
1003 END pay_ca_t4_mag;