[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_T4_MAG
Source
1 PACKAGE BODY pay_ca_t4_mag AS
2 /* $Header: pycat4mg.pkb 120.15 2011/11/18 13:24:54 rgottipa 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 get_dbitem_value_new(p_asg_act_id in number,
352 p_dbitem_name in varchar2,
353 p_jurisdiction varchar2 default null) return varchar2
354 is
355 lv_value varchar2(60);
356
357 cursor c_get_dbitem_value(cp_dbitem varchar2,
358 cp_jurisdiction varchar2) is
359 SELECT fai1.value
360 FROM FF_ARCHIVE_ITEMS FAI1,
361 ff_database_items fdi1,
362 ff_archive_item_contexts faic,
363 ff_contexts fc
364 WHERE FAI1.USER_ENTITY_ID = fdi1.user_entity_id
365 and fdi1.user_name = cp_dbitem
366 and fai1.archive_item_id = faic.archive_item_id
367 and fc.context_id = faic.context_id
368 and fc.context_name = 'JURISDICTION_CODE'
369 and faic.context = cp_jurisdiction
370 AND FAI1.CONTEXT1 = p_asg_act_id;
371
372 begin
373
374 open c_get_dbitem_value(p_dbitem_name,
375 p_jurisdiction);
376 fetch c_get_dbitem_value into lv_value;
377 if c_get_dbitem_value%NOTFOUND then
378 lv_value := 0;
379 end if;
380 close c_get_dbitem_value;
381
382 return lv_value;
383
384 end;
385
386 FUNCTION convert_2_xml(p_data IN varchar2,
387 p_tag IN varchar2,
388 p_datatype IN char default 'T',
389 p_format IN varchar2 default NULL,
390 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
391 return varchar2 is
392
393 l_data varchar2(4000);
394 l_output varchar2(4000);
395 BEGIN
396 if p_null_allowed = 'N'
397 and (TRIM(p_data) is null or (p_datatype in ('N','C') and to_number(p_data) = 0)) then
398 return ' ';
399 end if;
400
401 l_data := trim(p_data);
402 l_data := REPLACE(l_data, '&' , '&' || 'amp;');
403 l_data := REPLACE(l_data, '<' , '&' || 'lt;');
404 l_data := REPLACE(l_data, '>' , '&' || 'gt;');
405 l_data := REPLACE(l_data, '''' , '&' || 'apos;');
406 l_data := REPLACE(l_data, '"' , '&' || 'quot;');
407 --------------------------------------------------------
408 --- P_Datatype: T = Text, N = Number, C=Currency, D=Date
409 --------------------------------------------------------
410 IF p_datatype = 'T' or p_datatype = 'D' then
411 l_output := '<' || trim(p_tag) || '>' || trim(l_data) || '</' || trim(p_tag) || '>
412 ';
413 ELSIF p_datatype = 'N' or p_datatype = 'C' then
414 IF TRIM(p_format) is not null then
415 select to_char(to_number(p_data), p_format)
416 into l_data from dual;
417
418 ELSIF p_datatype = 'C' then -- Currency should be two decimal places
419 select to_char(to_number(p_data), '99999999999999999999999999999999999990.99')
420 into l_data from dual;
421 END IF;
422 l_output := '<' || trim(p_tag) || '>' || trim(l_data) || '</' || trim(p_tag) || '>';
423 -- new datatype ('X') is added by rgottipa for bug 9984889
424 ELSIF p_datatype = 'X' then
425 l_output := '<' || trim(p_tag) || ' xml:space="preserve" >' || trim(l_data)
426 ||' '||' '||' '||' '||' '
427 ||'</' || trim(p_tag) || '>';
428 --new datatype('Y') is added for bug 3891966
429 ELSIF p_datatype = 'Y' then
430 hr_utility.trace('In convert_2_xml');
431 l_output := '<' || trim(p_tag) || ' xml:space="preserve" >'
432 ||lpad(fnd_number.canonical_to_number(trim(to_char(l_data, '999'))),3,0)
433 ||' '||' '||' '||' '||' '
434 ||' '||' '||' '||' '||' '
435 ||' '||' '||' '||' '||' '
436 ||' '||' '||' '||' '||' '
437 ||' '||' '||' '||' '||' '
438 ||' '||' '||' '||' '||' '
439 ||'</' || trim(p_tag) || '>';
440 ELSIF p_datatype = 'Z' then
441 if l_data like '%._' then
442 l_data := l_data||'0';
443 elsif l_data like '%.__' then
444 l_data := l_data;
445 else l_data := l_data||'.00';
446 end if;
447 l_output := '<' || trim(p_tag) || '>' || trim(l_data) || '</' || trim(p_tag) || '>';
448 END IF;
449
450 return l_output;
451 END;
452
453 FUNCTION get_arch_val( p_context_id IN NUMBER,
454 p_user_name IN VARCHAR2)
455 RETURN varchar2 IS
456
457 cursor cur_archive (b_context_id NUMBER, b_user_name VARCHAR2) is
458 select fai.value
459 from ff_archive_items fai,
460 ff_database_items fdi
461 where fai.user_entity_id = fdi.user_entity_id
462 and fai.context1 = b_context_id
463 and fdi.user_name = b_user_name;
464
465 l_return VARCHAR2(240);
466 BEGIN
467 open cur_archive(p_context_id,p_user_name);
468 fetch cur_archive into l_return;
469 close cur_archive;
470 RETURN (l_return);
471 END ;
472
473 /* Function convert_t4_oth_info_amt
474 - For Bug 6855236
475 - To process the other info amounts for T4 Magnetic Media.
476 - Call to this function is made in fast formula T4_EMPLOYEE.
477 - Formatted XML strings for other info amounts are returned through out paramaters
478 - Additionally formatted strings for .a03 file are returned through out paramaters
479 */
480
481 FUNCTION convert_t4_oth_info_amt(p_assignment_action_id IN Number,
482 p_payroll_action_id IN Number,
483 p_jusrisdiction IN varchar2,
484 p_tax_unit_id IN Number,
485 p_fail IN char,
486 p_oth_rep1 OUT nocopy varchar2,
487 p_oth_rep2 OUT nocopy varchar2,
488 p_oth_rep3 OUT nocopy varchar2,
489 p_write_f31 OUT nocopy varchar2,
490 p_transfer_other_info1_str1 OUT nocopy varchar2,
491 p_transfer_other_info1_str2 OUT nocopy varchar2,
492 p_transfer_other_info1_str3 OUT nocopy varchar2,
493 p_transfer_other_info2_str1 OUT nocopy varchar2,
494 p_transfer_other_info2_str2 OUT nocopy varchar2,
495 p_transfer_other_info2_str3 OUT nocopy varchar2,
496 p_transfer_other_info3_str1 OUT nocopy varchar2,
497 p_transfer_other_info3_str2 OUT nocopy varchar2,
498 p_transfer_other_info3_str3 OUT nocopy varchar2,
499 p_transfer_other_info4_str1 OUT nocopy varchar2,
500 p_transfer_other_info4_str2 OUT nocopy varchar2,
501 p_transfer_other_info4_str3 OUT nocopy varchar2,
502 -- Modification for bug 10097860 starts here.
503 p_transfer_other_info5_str1 OUT nocopy varchar2,
504 p_transfer_other_info5_str2 OUT nocopy varchar2,
505 p_transfer_other_info5_str3 OUT nocopy varchar2,
506 -- Modification for bug 10097860 ends here.
507 p_transfer_oth1_rep1 OUT nocopy varchar2,
508 p_transfer_oth1_rep2 OUT nocopy varchar2,
509 p_transfer_oth1_rep3 OUT nocopy varchar2,
510 p_transfer_oth2_rep2 OUT nocopy varchar2,
511 p_transfer_oth2_rep3 OUT nocopy varchar2,
512 p_transfer_oth3_rep2 OUT nocopy varchar2,
513 p_transfer_oth3_rep3 OUT nocopy varchar2,
514 p_transfer_oth4_rep3 OUT nocopy varchar2,
515 -- Modification for bug 10097860 starts here.
516 p_transfer_oth5_rep4 OUT nocopy varchar2,
517 -- Modification for bug 10097860 ends here.
518 p_cnt OUT nocopy Number)
519 return varchar2 is
520
521 l_other_info varchar2(100);
522 l_cnt Number :=0;
523 l_amt Number :=0;
524
525 l_write_f30 varchar2(400) := ' ';
526 l_write_f31 varchar2(400) := ' ';
527 l_oth_rep1 varchar2(400);
528 l_oth_rep2 varchar2(400);
529 l_oth_rep3 varchar2(400);
530 l_transfer_other_info1_str1 varchar2(400);
531 l_transfer_other_info1_str2 varchar2(400);
532 l_transfer_other_info1_str3 varchar2(400);
533 l_transfer_other_info2_str1 varchar2(400);
534 l_transfer_other_info2_str2 varchar2(400);
535 l_transfer_other_info2_str3 varchar2(400);
536 l_transfer_other_info3_str1 varchar2(400);
537 l_transfer_other_info3_str2 varchar2(400);
538 l_transfer_other_info3_str3 varchar2(400);
539 l_transfer_other_info4_str1 varchar2(400);
540 l_transfer_other_info4_str2 varchar2(400);
541 l_transfer_other_info4_str3 varchar2(400);
542
543 -- Modification for bug 10097860 starts here.
544 l_transfer_other_info5_str1 varchar2(400);
545 l_transfer_other_info5_str2 varchar2(400);
546 l_transfer_other_info5_str3 varchar2(400);
547 -- Modification for bug 10097860 ends here.
548
549 l_transfer_oth1_rep1 varchar2(400);
550 l_transfer_oth1_rep2 varchar2(400);
551 l_transfer_oth1_rep3 varchar2(400);
552 l_transfer_oth2_rep2 varchar2(400);
553 l_transfer_oth2_rep3 varchar2(400);
554 l_transfer_oth3_rep2 varchar2(400);
555 l_transfer_oth3_rep3 varchar2(400);
556 l_transfer_oth4_rep3 varchar2(400);
557 -- l_transfer_oth5_rep4 added for bug 10097860.
558 l_transfer_oth5_rep4 varchar2(400);
559
560 type string_table is table of varchar2(50) index by binary_integer;
561 t_dbi string_table;
562 t_tag string_table;
563
564 BEGIN
565
566 /* DBIs of other info amounts */
567 t_dbi(1) := 'CAEOY_T4_OTHER_INFO_AMOUNT30_PER_JD_GRE_YTD';
568 t_dbi(2) := 'CAEOY_T4_OTHER_INFO_AMOUNT31_PER_JD_GRE_YTD';
569 t_dbi(3) := 'CAEOY_T4_OTHER_INFO_AMOUNT32_PER_JD_GRE_YTD';
570 t_dbi(4) := 'CAEOY_T4_OTHER_INFO_AMOUNT33_PER_JD_GRE_YTD';
571 t_dbi(5) := 'CAEOY_T4_OTHER_INFO_AMOUNT34_PER_JD_GRE_YTD';
572 t_dbi(6) := 'CAEOY_T4_OTHER_INFO_AMOUNT35_PER_JD_GRE_YTD';
573 t_dbi(7) := 'CAEOY_T4_OTHER_INFO_AMOUNT36_PER_JD_GRE_YTD';
574 t_dbi(8) := 'CAEOY_T4_OTHER_INFO_AMOUNT37_PER_JD_GRE_YTD';
575 t_dbi(9) := 'CAEOY_T4_OTHER_INFO_AMOUNT38_PER_JD_GRE_YTD';
576 t_dbi(10) := 'CAEOY_T4_OTHER_INFO_AMOUNT39_PER_JD_GRE_YTD';
577 t_dbi(11) := 'CAEOY_T4_OTHER_INFO_AMOUNT40_PER_JD_GRE_YTD';
578 t_dbi(12) := 'CAEOY_T4_OTHER_INFO_AMOUNT41_PER_JD_GRE_YTD';
579 t_dbi(13) := 'CAEOY_T4_OTHER_INFO_AMOUNT42_PER_JD_GRE_YTD';
580 t_dbi(14) := 'CAEOY_T4_OTHER_INFO_AMOUNT43_PER_JD_GRE_YTD';
581 /* commented for bug 10244185*/
582 -- t_dbi(15) := 'CAEOY_T4_OTHER_INFO_AMOUNT53_PER_JD_GRE_YTD';
583 -- Modification for bug 10097860 starts here.
584 t_dbi(15) := 'CAEOY_T4_OTHER_INFO_AMOUNT66_PER_JD_GRE_YTD';
585 t_dbi(16) := 'CAEOY_T4_OTHER_INFO_AMOUNT67_PER_JD_GRE_YTD';
586 t_dbi(17) := 'CAEOY_T4_OTHER_INFO_AMOUNT68_PER_JD_GRE_YTD';
587 t_dbi(18) := 'CAEOY_T4_OTHER_INFO_AMOUNT69_PER_JD_GRE_YTD';
588 -- Modification for bug 10097860 ends here.
589 t_dbi(19) := 'CAEOY_T4_OTHER_INFO_AMOUNT70_PER_JD_GRE_YTD';
590 t_dbi(20) := 'CAEOY_T4_OTHER_INFO_AMOUNT71_PER_JD_GRE_YTD';
591 t_dbi(21) := 'CAEOY_T4_OTHER_INFO_AMOUNT72_PER_JD_GRE_YTD';
592 t_dbi(22) := 'CAEOY_T4_OTHER_INFO_AMOUNT73_PER_JD_GRE_YTD';
593 t_dbi(23) := 'CAEOY_T4_OTHER_INFO_AMOUNT74_PER_JD_GRE_YTD';
594 t_dbi(24) := 'CAEOY_T4_OTHER_INFO_AMOUNT75_PER_JD_GRE_YTD';
595 t_dbi(25) := 'CAEOY_T4_OTHER_INFO_AMOUNT77_PER_JD_GRE_YTD';
596 t_dbi(26) := 'CAEOY_T4_OTHER_INFO_AMOUNT78_PER_JD_GRE_YTD';
597 t_dbi(27) := 'CAEOY_T4_OTHER_INFO_AMOUNT79_PER_JD_GRE_YTD';
598 t_dbi(28) := 'CAEOY_T4_OTHER_INFO_AMOUNT80_PER_JD_GRE_YTD';
599 t_dbi(29) := 'CAEOY_T4_OTHER_INFO_AMOUNT81_PER_JD_GRE_YTD';
600 t_dbi(30) := 'CAEOY_T4_OTHER_INFO_AMOUNT82_PER_JD_GRE_YTD';
601 t_dbi(31) := 'CAEOY_T4_OTHER_INFO_AMOUNT83_PER_JD_GRE_YTD';
602 t_dbi(32) := 'CAEOY_T4_OTHER_INFO_AMOUNT84_PER_JD_GRE_YTD';
603 t_dbi(33) := 'CAEOY_T4_OTHER_INFO_AMOUNT85_PER_JD_GRE_YTD';
604
605 -- Modification for bug 10097860 starts here.
606 t_dbi(34) := 'CAEOY_T4_OTHER_INFO_AMOUNT86_PER_JD_GRE_YTD';
607 -- Modification for bug 10097860 ends here.
608
609 -- Modification for bug 10244185 starts here.
610 t_dbi(35) := 'CAEOY_T4_OTHER_INFO_AMOUNT87_PER_JD_GRE_YTD';
611 -- Modification for bug 10244185 ends here.
612
613 /* XML Tags for corresponding other info amounts*/
614 t_tag(1) := 'hm_brd_lodg_amt';
615 t_tag(2) := 'spcl_wrk_site_amt';
616 t_tag(3) := 'prscb_zn_trvl_amt';
617 t_tag(4) := 'med_trvl_amt';
618 t_tag(5) := 'prsnl_vhcl_amt';
619 t_tag(6) := 'rsn_per_km_amt';
620 t_tag(7) := 'low_int_loan_amt';
621 t_tag(8) := 'empe_hm_loan_amt';
622 t_tag(9) := 'sob_a00_feb_amt';
623 t_tag(10) := 'sod_d_a00_feb_amt';
624 t_tag(11) := 'oth_tx_ben_amt';
625 t_tag(12) := 'sod_d1_a00_feb_amt';
626 t_tag(13) := 'empt_cmsn_amt';
627 t_tag(14) := 'cfppa_amt';
628 --t_tag(15) := 'dfr_sob_amt';
629
630 -- Modification for bug 10097860 starts here.
631 t_tag(15) := 'elg_rtir_amt';
632 t_tag(16) := 'nelg_rtir_amt';
633 t_tag(17) := 'indn_elg_rtir_amt';
634 t_tag(18) := 'indn_nelg_rtir_amt';
635 -- Modification for bug 10097860 ends here.
636
637 t_tag(19) := 'mun_ofcr_examt';
638 t_tag(20) := 'indn_empe_amt';
639 t_tag(21) := 'oc_incamt';
640 t_tag(22) := 'oc_dy_cnt';
641 t_tag(23) := 'pr_90_cntrbr_amt';
642 t_tag(24) := 'pr_90_ncntrbr_amt';
643 t_tag(25) := 'cmpn_rpay_empr_amt';
644 t_tag(26) := 'fish_gro_ern_amt';
645 t_tag(27) := 'fish_net_ptnr_amt';
646 t_tag(28) := 'fish_shr_prsn_amt';
647 t_tag(29) := 'plcmt_emp_agcy_amt';
648 t_tag(30) := 'drvr_taxis_oth_amt';
649 t_tag(31) := 'brbr_hrdrssr_amt';
650 t_tag(32) := 'pub_trnst_pass_amt';
651 t_tag(33) := 'epaid_hlth_pln_amt';
652
653 -- Modification for bug 10097860 starts here.
654 -- commented by sneelapa, for bug 10349099
655 -- t_tag(35) := 'stok_opt_csh_out_earnt';
656
657 t_tag(34) := 'stok_opt_csh_out_eamt';
658 -- Modification for bug 10097860 ends here.
659
660 t_tag(35) := 'vlntr_firefighter_xmpt_amt'; -- for bug 10244185
661
662 l_transfer_oth1_rep1 := rpad(lpad('.00,',12), 6*12, lpad('.00,',12));
663 --l_transfer_oth2_rep1 := rpad(lpad('.00,',12), 10*12, lpad('.00,',12));
664 --l_transfer_oth2_rep2 := rpad(lpad('.00,',10), 2*10, lpad('.00,',10));
665 --l_transfer_oth3_rep1 := rpad(lpad('.00,',12), 10*12, lpad('.00,',12));
666 l_transfer_oth3_rep2 := rpad(lpad('.00,',12), 6*12, lpad('.00,',12));
667 --l_transfer_oth4_rep1 := rpad(lpad('.00,',12), 10*12, lpad('.00,',12));
668 --l_transfer_oth4_rep2 := rpad(lpad('.00,',12), 10*12, lpad('.00,',12));
669 l_transfer_oth4_rep3 := rpad(lpad('.00,',12), 4*12, lpad('.00,',12));
670 l_transfer_oth5_rep4 := rpad(lpad('.00,',12), 4*12, lpad('.00,',12));
671
672 hr_utility.trace('p_assignment_action_id = '||p_assignment_action_id);
673 hr_utility.trace('p_payroll_action_id = '||p_payroll_action_id);
674 hr_utility.trace('p_fail = '||p_fail);
675
676 for i in 1..t_dbi.COUNT
677 loop
678 l_amt := fnd_number.canonical_to_number(get_dbitem_value_new(p_assignment_action_id,t_dbi(i),p_jusrisdiction));
679 if (p_fail <> 'Y') and (l_amt >0) then
680 if(i=22) then -- changed 19 to 23 by rgottipa for bug 1009760
681 -- changed 23 to 22 for bug 10244185
682 --added lpad and ceil functions for dispalying code 73 in three digit format
683 -- bug 3891966
684 l_other_info := CONVERT_2_XML(lpad(ceil(l_amt),3,0), t_tag(i), 'N'); --For oth. code 73 (<oc_dy_cnt>)
685 else
686 l_other_info := CONVERT_2_XML(l_amt, t_tag(i), 'C'); -- Bug 7424296
687 end if;
688 l_cnt := l_cnt+1;
689
690 hr_utility.trace('l_other_info = '||l_other_info);
691 hr_utility.trace('l_cnt = '||l_cnt);
692
693 if l_cnt <= 3 then
694 l_write_f30 := l_write_f30||l_other_info;
695 elsif l_cnt <=6 then
696 l_write_f31 := l_write_f31||l_other_info;
697 elsif l_cnt <= 8 then
698 l_transfer_other_info1_str1 := l_transfer_other_info1_str1 || l_other_info;
699 elsif l_cnt <= 10 then
700 l_transfer_other_info1_str2 := l_transfer_other_info1_str2 || l_other_info;
701 elsif l_cnt <= 12 then
702 l_transfer_other_info1_str3 := l_transfer_other_info1_str3 || l_other_info;
703 elsif l_cnt <= 14 then
704 l_transfer_other_info2_str1 := l_transfer_other_info2_str1 || l_other_info;
705 elsif l_cnt <= 16 then
706 l_transfer_other_info2_str2 := l_transfer_other_info2_str2 || l_other_info;
707 elsif l_cnt <= 18 then
708 l_transfer_other_info2_str3 := l_transfer_other_info2_str3 || l_other_info;
709 elsif l_cnt <= 20 then
710 l_transfer_other_info3_str1 := l_transfer_other_info3_str1 || l_other_info;
711 elsif l_cnt <= 22 then
712 l_transfer_other_info3_str2 := l_transfer_other_info3_str2 || l_other_info;
713 elsif l_cnt <= 24 then
714 l_transfer_other_info3_str3 := l_transfer_other_info3_str3 || l_other_info;
715 elsif l_cnt <= 26 then
716 l_transfer_other_info4_str1 := l_transfer_other_info4_str1 || l_other_info;
717 elsif l_cnt <= 28 then
718 l_transfer_other_info4_str2 := l_transfer_other_info4_str2 || l_other_info;
719 -- Modification for bug 10097860 starts here.
720 --else
721 elsif l_cnt <= 30 then
722 l_transfer_other_info4_str3 := l_transfer_other_info4_str3 || l_other_info;
723 elsif l_cnt <= 32 then
724 l_transfer_other_info5_str1 := l_transfer_other_info5_str1 || l_other_info;
725 elsif l_cnt <= 34 then
726 l_transfer_other_info5_str2 := l_transfer_other_info5_str2 || l_other_info;
727 else
728 l_transfer_other_info5_str3 := l_transfer_other_info5_str3 || l_other_info;
729 -- Modification for bug 10097860 ends here.
730
731 end if;
732 end if;
733
734 /* Formatting strings for .a03 audit report */
735 if i <=6 then
736 l_oth_rep1 := l_oth_rep1 || to_char(l_amt, '9999999.99') ||',';
737 elsif i <=12 then
738 if p_fail = 'Y' or l_cnt <= 6 then
739 l_oth_rep1 := l_oth_rep1 || to_char(l_amt, '9999999.99') ||',';
740 l_transfer_oth1_rep1 := l_transfer_oth1_rep1 || lpad('.00,',12);
741 else
742 l_oth_rep1 := l_oth_rep1 || lpad('.00,',12);
743 l_transfer_oth1_rep1 := l_transfer_oth1_rep1 || to_char(nvl(l_amt,0), '9999999.99') ||',';
744 end if;
745 elsif i <=18 then
746 if p_fail = 'Y' or l_cnt <= 6 then
747 l_oth_rep2 := l_oth_rep2 || to_char(l_amt, '9999999.99') ||',';
748 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
749 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
750 elsif l_cnt <= 12 then
751 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
752 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || to_char(l_amt, '9999999.99') ||',';
753 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
754 else
755 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
756 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
757 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || to_char(l_amt, '9999999.99') ||',';
758 end if;
759 elsif i <=24 then
760 if p_fail = 'Y' or l_cnt <= 6 then
761 if i =22 then -- changed 19 to 23 by rgottipa for bug 1009760
762 -- changed 23 to 22 for bug 10244185
763 --added lpad and ceil functions for dispalying code 73 in three digit format
764 -- bug 3891966
765 l_oth_rep2 := l_oth_rep2 || --lpad( to_char(l_amt, '999') ||',',12);
766 lpad(lpad(ceil(l_amt),3,0)||',',12);
767 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('0,',12);
768 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('0,',12);
769 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('0,',12);
770 else
771 l_oth_rep2 := l_oth_rep2 || to_char(l_amt, '9999999.99') ||',';
772 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
773 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
774 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
775 end if;
776 elsif l_cnt <= 12 then
777 if i =22 then -- changed 19 to 23 by rgottipa for bug 1009760
778 -- changed 23 to 22 for bug 10244185
779 l_oth_rep2 := l_oth_rep2 || lpad('0,',12);
780 --added lpad and ceil functions for dispalying code 73 in three digit format
781 -- bug 3891966
782 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || --lpad(to_char(l_amt,'999')||',',12);
783 lpad(lpad(ceil(l_amt),3,0)||',',12);
784 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('0,',12);
785 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('0,',12);
786 else
787 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
788 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || to_char(l_amt, '9999999.99') ||',';
789 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
790 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
791 end if;
792 elsif l_cnt <= 18 then
793 if i =22 then -- changed 19 to 23 by rgottipa for bug 1009760
794 -- changed 23 to 22 for bug 10244185
795 l_oth_rep2 := l_oth_rep2 || lpad('0,',12);
796 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('0,',12);
797 --added lpad and ceil functions for dispalying code 73 in three digit format
798 -- bug 3891966
799 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 ||-- lpad(to_char(l_amt, '999') ||',',12);
800 lpad(lpad(ceil(l_amt),3,0)||',',12);
801 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('0,',12);
802 else
803 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
804 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
805 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || to_char(l_amt, '9999999.99') ||',';
806 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || lpad('.00,',12);
807 end if;
808 else
809 if i =22 then -- changed 19 to 23 by rgottipa for bug 1009760
810 -- changed 23 to 22 for bug 10244185
811 l_oth_rep2 := l_oth_rep2 || lpad('0,',12);
812 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('0,',12);
813 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('0,',12);
814 --added lpad and ceil functions for dispalying code 73 in three digit format
815 -- bug 3891966
816 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || --lpad(to_char(l_amt, '999') ||',',12);
817 lpad(lpad(ceil(l_amt),3,0)||',',12);
818 else
819 l_oth_rep2 := l_oth_rep2 || lpad('.00,',12);
820 l_transfer_oth1_rep2 := l_transfer_oth1_rep2 || lpad('.00,',12);
821 l_transfer_oth2_rep2 := l_transfer_oth2_rep2 || lpad('.00,',12);
822 l_transfer_oth3_rep2 := l_transfer_oth3_rep2 || to_char(l_amt, '9999999.99') ||',';
823 end if;
824 end if;
825 elsif i <=30 then
826 if p_fail = 'Y' or l_cnt <= 6 then
827 l_oth_rep3 := l_oth_rep3 || to_char(l_amt, '9999999.99') ||',';
828 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
829 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
830 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
831 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
832 elsif l_cnt <= 12 then
833 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
834 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || to_char(l_amt, '9999999.99') ||',';
835 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
836 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
837 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
838 elsif l_cnt <= 18 then
839 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
840 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
841 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || to_char(l_amt, '9999999.99') ||',';
842 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
843 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
844 elsif l_cnt <= 24 then
845 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
846 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
847 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
848 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || to_char(l_amt, '9999999.99') ||',';
849 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
850 else
851 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
852 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
853 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
854 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
855 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || to_char(l_amt, '9999999.99') ||',';
856 end if;
857 -- Modification for bug 10097860 starts here.
858 elsif i <=36 then
859 if p_fail = 'Y' or l_cnt <= 6 then
860 l_oth_rep3 := l_oth_rep3 || to_char(l_amt, '9999999.99') ||',';
861 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
862 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
863 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
864 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
865 l_transfer_oth5_rep4 := l_transfer_oth5_rep4 || lpad('.00,',12);
866 elsif l_cnt <= 12 then
867 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
868 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || to_char(l_amt, '9999999.99') ||',';
869 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
870 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
871 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
872 l_transfer_oth5_rep4 := l_transfer_oth5_rep4 || lpad('.00,',12);
873 elsif l_cnt <= 18 then
874 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
875 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
876 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || to_char(l_amt, '9999999.99') ||',';
877 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
878 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
879 l_transfer_oth5_rep4 := l_transfer_oth5_rep4 || lpad('.00,',12);
880 elsif l_cnt <= 24 then
881 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
882 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
883 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
884 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || to_char(l_amt, '9999999.99') ||',';
885 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
886 l_transfer_oth5_rep4 := l_transfer_oth5_rep4 || lpad('.00,',12);
887 elsif l_cnt <= 30 then
888 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
889 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
890 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
891 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
892 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || to_char(l_amt, '9999999.99') ||',';
893 l_transfer_oth5_rep4 := l_transfer_oth5_rep4 || lpad('.00,',12);
894 else
895 l_oth_rep3 := l_oth_rep3 || lpad('.00,',12);
896 l_transfer_oth1_rep3 := l_transfer_oth1_rep3 || lpad('.00,',12);
897 l_transfer_oth2_rep3 := l_transfer_oth2_rep3 || lpad('.00,',12);
898 l_transfer_oth3_rep3 := l_transfer_oth3_rep3 || lpad('.00,',12);
899 l_transfer_oth4_rep3 := l_transfer_oth4_rep3 || lpad('.00,',12);
900 l_transfer_oth5_rep4 := l_transfer_oth5_rep4 || to_char(l_amt, '9999999.99') ||',';
901 end if;
902 -- Modification for bug 10097860 ends here.
903 end if;
904 end loop;
905
906 p_cnt := l_cnt;
907 p_oth_rep1 := l_oth_rep1;
908 p_oth_rep2 := l_oth_rep2;
909 p_oth_rep3 := l_oth_rep3;
910 p_write_f31 := l_write_f31;
911 p_transfer_other_info1_str1 := l_transfer_other_info1_str1;
912 p_transfer_other_info1_str2 := l_transfer_other_info1_str2;
913 p_transfer_other_info1_str3 := l_transfer_other_info1_str3;
914 p_transfer_other_info2_str1 := l_transfer_other_info2_str1;
915 p_transfer_other_info2_str2 := l_transfer_other_info2_str2;
916 p_transfer_other_info2_str3 := l_transfer_other_info2_str3;
917 p_transfer_other_info3_str1 := l_transfer_other_info3_str1;
918 p_transfer_other_info3_str2 := l_transfer_other_info3_str2;
919 p_transfer_other_info3_str3 := l_transfer_other_info3_str3;
920 p_transfer_other_info4_str1 := l_transfer_other_info4_str1;
921 p_transfer_other_info4_str2 := l_transfer_other_info4_str2;
922 p_transfer_other_info4_str3 := l_transfer_other_info4_str3;
923 -- Modification for bug 10097860 starts here.
924 p_transfer_other_info5_str1 := l_transfer_other_info5_str1;
925 p_transfer_other_info5_str2 := l_transfer_other_info5_str2;
926 p_transfer_other_info5_str3 := l_transfer_other_info5_str3;
927 -- Modification for bug 10097860 ends here.
928 p_transfer_oth1_rep1 := l_transfer_oth1_rep1;
929 p_transfer_oth1_rep2 := l_transfer_oth1_rep2;
930 p_transfer_oth1_rep3 := l_transfer_oth1_rep3;
931 p_transfer_oth2_rep2 := l_transfer_oth2_rep2;
932 p_transfer_oth2_rep3 := l_transfer_oth2_rep3;
933 p_transfer_oth3_rep2 := l_transfer_oth3_rep2;
934 p_transfer_oth3_rep3 := l_transfer_oth3_rep3;
935 p_transfer_oth4_rep3 := l_transfer_oth4_rep3;
936
937 -- Modification for bug 10097860 starts here.
938 p_transfer_oth5_rep4 := l_transfer_oth5_rep4;
939 -- Modification for bug 10097860 ends here.
940
941 /*
942 hr_utility.trace('p_cnt = '|| l_cnt);
943 hr_utility.trace('p_oth_rep1 = '|| l_oth_rep1);
944 hr_utility.trace('p_oth_rep2 = '|| l_oth_rep2);
945 hr_utility.trace('p_oth_rep3 = '|| l_oth_rep3);
946 hr_utility.trace('write_f30 = '|| l_write_f30);
947 hr_utility.trace('p_write_f31 = '|| l_write_f31);
948 hr_utility.trace('p_transfer_other_info1_str1 = '|| l_transfer_other_info1_str1);
949 hr_utility.trace('p_transfer_other_info1_str2 = '|| l_transfer_other_info1_str2);
950 hr_utility.trace('p_transfer_other_info1_str3 = '|| l_transfer_other_info1_str3);
951 hr_utility.trace('p_transfer_other_info2_str1 = '|| l_transfer_other_info2_str1);
952 hr_utility.trace('p_transfer_other_info2_str2 = '|| l_transfer_other_info2_str2);
953 hr_utility.trace('p_transfer_other_info2_str3 = '|| l_transfer_other_info2_str3);
954 hr_utility.trace('p_transfer_other_info3_str1 = '|| l_transfer_other_info3_str1);
955 hr_utility.trace('p_transfer_other_info3_str2 = '|| l_transfer_other_info3_str2);
956 hr_utility.trace('p_transfer_other_info3_str3 = '|| l_transfer_other_info3_str3);
957 hr_utility.trace('p_transfer_other_info4_str1 = '|| l_transfer_other_info4_str1);
958 hr_utility.trace('p_transfer_other_info4_str2 = '|| l_transfer_other_info4_str2);
959 hr_utility.trace('p_transfer_other_info4_str3 = '|| l_transfer_other_info4_str3);
960 hr_utility.trace('p_transfer_oth1_rep1 = '|| l_transfer_oth1_rep1);
961 hr_utility.trace('p_transfer_oth1_rep2 = '|| l_transfer_oth1_rep2);
962 hr_utility.trace('p_transfer_oth1_rep3 = '|| l_transfer_oth1_rep3);
963 hr_utility.trace('p_transfer_oth2_rep2 = '|| l_transfer_oth2_rep2);
964 hr_utility.trace('p_transfer_oth2_rep3 = '|| l_transfer_oth2_rep3);
965 hr_utility.trace('p_transfer_oth3_rep2 = '|| l_transfer_oth3_rep2);
966 hr_utility.trace('p_transfer_oth3_rep3 = '|| l_transfer_oth3_rep3);
967 hr_utility.trace('p_transfer_oth4_rep3 = '|| l_transfer_oth4_rep3);
968 hr_utility.trace('p_transfer_oth5_rep4 = '|| l_transfer_oth5_rep4);
969 */
970
971 return l_write_f30;
972
973 END;
974
975 FUNCTION validate_gre_data ( p_trans IN VARCHAR2,
976 p_year IN VARCHAR2)
977 RETURN varchar2 IS
978
979 cursor c_trans_payid ( c_trans_id VARCHAR2,
980 c_year VARCHAR2) is
981 Select ppa.payroll_action_id, ppa.business_group_id
982 from hr_organization_information hoi,
983 pay_payroll_actions PPA,
984 pay_ca_legislation_info pcli,
985 pay_ca_legislation_info pcli1
986 where hoi.organization_id = to_number(c_trans_id)
987 and hoi.org_information_context='Fed Magnetic Reporting'
988 and ppa.report_type = 'T4' -- T4 Archiver Report Type
989 and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='))
990 and to_char(ppa.effective_date,'YYYY')= c_year
991 and to_char(ppa.effective_date,'DD-MM')= '31-12'
992 and pcli.information_type = 'MAX_CPP_EARNINGS'
993 and ppa.effective_date between pcli.start_date and pcli.end_date
994 and pcli1.information_type = 'MAX_EI_EARNINGS'
995 and ppa.effective_date between pcli1.start_date and pcli1.end_date;
996
997 cursor c_all_gres(p_trans VARCHAR2,
998 p_year VARCHAR2,
999 p_bg_id NUMBER) is
1000 Select distinct ppa.payroll_action_id, hoi.organization_id, hou.name
1001 From pay_payroll_actions ppa,
1002 hr_organization_information hoi,
1003 hr_all_organization_units hou
1004 where hoi.org_information_context = 'Canada Employer Identification'
1005 and hoi.org_information11 = p_trans
1006 and hou.business_group_id = p_bg_id
1007 and hou.organization_id = hoi.organization_id
1008 and ppa.report_type = 'T4'
1009 and ppa.effective_date = to_date('31-12'||p_year,'DD-MM-YYYY')
1010 and ppa.business_group_id = p_bg_id
1011 and hoi.organization_id = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'TRANSFER_GRE=')+LENGTH('TRANSFER_GRE='));
1012
1013 cursor c_gre_name (b_org_id VARCHAR2) is
1014 select hou.name
1015 from hr_all_organization_units hou
1016 where hou.organization_id = to_number(b_org_id);
1017
1018 /* Local variables */
1019 l_trans_gre hr_all_organization_units.organization_id%TYPE;
1020 l_year VARCHAR2(10);
1021 l_gre hr_all_organization_units.organization_id%TYPE;
1022 l_bus_grp hr_all_organization_units.business_group_id%TYPE;
1023 l_trans_no VARCHAR2(240);
1024 l_tech_name VARCHAR2(240) ;
1025 l_tech_area VARCHAR2(240) ;
1026 l_tech_phno VARCHAR2(240) ;
1027
1028 -- l_tech_email variable and validation for the same are added for bug 10097860
1029 l_tech_email VARCHAR2(240) ;
1030
1031 l_lang VARCHAR2(240) ;
1032 l_acc_name VARCHAR2(240) ;
1033 l_acc_area VARCHAR2(240) ;
1034 l_acc_phno VARCHAR2(240) ;
1035 l_trans_bus_no VARCHAR2(240);
1036 l_trans_name VARCHAR2(240);
1037 l_bus_no VARCHAR2(240) ;
1038 l_bg_id number ;
1039 l_trans_payid pay_payroll_actions.payroll_action_id%TYPE;
1040 l_gre_payid pay_payroll_actions.payroll_action_id%TYPE;
1041 l_gre_actid pay_assignment_actions.assignment_action_id%TYPE;
1042 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
1043 l_acc_info_flag CHAR(1);
1044 l_gre_name VARCHAR2(240);
1045
1046 BEGIN
1047
1048 /* Fetching the Payroll Action Id for Trasnmitter GRE */
1049
1050 --hr_utility.trace_on(null,'T4MAG');
1051 hr_utility.trace('Inside the Validation Code');
1052 hr_utility.trace('The Transmitter GRE id passed is '||p_trans);
1053 open c_trans_payid(p_trans,p_year);
1054 fetch c_trans_payid into l_trans_payid,l_bg_id;
1055 IF c_trans_payid%notfound THEN
1056 close c_trans_payid;
1057 hr_utility.trace('The Transmitter GRE id not found '||p_trans);
1058 hr_utility.raise_error;
1059 return '1';
1060 else
1061 close c_trans_payid;
1062 END IF;
1063
1064 hr_utility.trace('Fetched the Payroll Id for transmitter GRE'|| l_trans_payid);
1065 hr_utility.trace('The Reporting Year is '||p_year);
1066
1067 /*Fetching the Trasnmitter Level Data */
1068
1069 l_trans_no := get_arch_val(l_trans_payid, 'CAEOY_TRANSMITTER_NUMBER');
1070 l_tech_name:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_NAME');
1071 l_tech_area:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_AREA_CODE');
1072 l_tech_phno:= get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_PHONE');
1073 l_lang := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_LANGUAGE');
1074
1075 l_tech_email := get_arch_val(l_trans_payid, 'CAEOY_TECHNICAL_CONTACT_EMAIL');
1076
1077 l_acc_name := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
1078 l_acc_area := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
1079 l_acc_phno := get_arch_val(l_trans_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
1080 l_trans_bus_no := get_arch_val(l_trans_payid, 'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
1081
1082 -- l_trans_name := get_arch_val(l_trans_payid, 'CAEOY_TRANSMITTER_NAME');
1083
1084 OPEN c_gre_name(to_number(p_trans));
1085 FETCH c_gre_name INTO l_trans_name;
1086 CLOSE c_gre_name;
1087
1088 hr_utility.trace('Transmitter Number '||l_trans_no);
1089 hr_utility.trace('Tech Name '||l_tech_name);
1090 hr_utility.trace('Tech Email '||l_tech_email);
1091 hr_utility.trace('Tech Phno '||l_tech_phno);
1092 hr_utility.trace('Tech area '||l_tech_area);
1093 hr_utility.trace('Tech Lang '||l_lang);
1094
1095 /* Checking for the validity of the above values fetched */
1096 hr_utility.trace('Checking the Transmitter No ');
1097 IF l_trans_no IS NULL
1098 OR TRANSLATE(l_trans_no,'M0123456789','M9999999999') <> 'MM999999' THEN
1099 hr_utility.trace('Incorrect Transmitter No format');
1100 hr_utility.set_message(801,'PAY_74155_INCORRECT_TRANSMT_NO');
1101 hr_utility.set_message_token('GRE_NAME',l_trans_name);
1102 pay_core_utils.push_message(801,'PAY_74155_INCORRECT_TRANSMT_NO','P');
1103 pay_core_utils.push_token('GRE_NAME',l_trans_name);
1104 hr_utility.raise_error;
1105 return '1';
1106 END IF;
1107
1108 if l_tech_name is null or
1109 l_tech_area is null or
1110 l_tech_phno is null or
1111 l_tech_email is null or
1112 l_lang is null then
1113 hr_utility.trace('Technical contact details missing');
1114 hr_utility.set_message(801,'PAY_74158_INCORRECT_TCHN_INFO');
1115 hr_utility.set_message_token('GRE_NAME',l_trans_name);
1116 pay_core_utils.push_message(801,'PAY_74158_INCORRECT_TCHN_INFO','P');
1117 pay_core_utils.push_token('GRE_NAME',l_trans_name);
1118 hr_utility.raise_error;
1119 return '1';
1120 end if;
1121
1122 if l_acc_name is null or
1123 l_acc_phno is null or
1124 l_acc_area is null then
1125 l_acc_info_flag := 'N';
1126 else
1127 l_acc_info_flag := 'Y';
1128 end if;
1129 hr_utility.trace('The value of the Flag is '||l_acc_info_flag);
1130 hr_utility.trace('The value of the bgid '||to_char(l_bg_id));
1131
1132
1133 /* Checking for the GRE level information */
1134
1135 open c_all_gres(p_trans,p_year,l_bg_id);
1136 loop
1137 fetch c_all_gres into l_gre_payid, l_gre, l_gre_name;
1138 hr_utility.trace('The Gre id fetched is '||l_gre);
1139 if c_all_gres%notfound then
1140 close c_all_gres;
1141 exit;
1142 end if;
1143
1144 hr_utility.trace('Before fetching the GREs for this Transmitter '||l_gre||'-'||p_year);
1145
1146 if l_gre <> to_number(p_trans) then
1147 hr_utility.trace('Inside the loop'||l_gre_payid);
1148 hr_utility.trace('Checking GRE level data');
1149 hr_utility.trace('The Payroll Action Id for Gre is '|| l_gre_payid);
1150 l_bus_no := get_arch_val(l_gre_payid,'CAEOY_EMPLOYER_IDENTIFICATION_NUMBER');
1151 --l_tax_unit_id := get_arch_val(l_gre_payid, 'CAEOY_TAX_UNIT_ID');
1152 l_acc_name := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_NAME');
1153 l_acc_area := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_AREA_CODE');
1154 l_acc_phno := get_arch_val(l_gre_payid, 'CAEOY_ACCOUNTING_CONTACT_PHONE');
1155
1156 hr_utility.trace('Tax unit Id'||l_tax_unit_id);
1157 hr_utility.trace('Acc Name '||l_acc_name);
1158 hr_utility.trace('Acc Area '||l_acc_area);
1159 hr_utility.trace('Acc Phone '||l_acc_phno);
1160
1161 if l_bus_no is null
1162 or TRANSLATE(l_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' then
1163 hr_utility.trace('No Business Number Entereed ');
1164 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
1165 hr_utility.set_message_token('GRE_NAME',l_gre_name);
1166 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
1167 pay_core_utils.push_token('GRE_NAME',l_gre_name);
1168 hr_utility.raise_error;
1169 return '1';
1170 end if;
1171
1172 if (l_acc_name is null or
1173 l_acc_area is null or
1174 l_acc_phno is null ) and
1175 l_acc_info_flag = 'N' then
1176 hr_utility.trace('No Accounting Contact info present');
1177 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
1178 hr_utility.set_message_token('GRE_NAME',l_gre_name);
1179 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
1180 pay_core_utils.push_token('GRE_NAME',l_gre_name);
1181 hr_utility.raise_error;
1182 return '1';
1183 end if;
1184
1185 elsif l_gre = to_number(p_trans) then
1186
1187 if l_trans_bus_no is null
1188 or TRANSLATE(l_trans_bus_no,'0123456789RP','9999999999RP') <> '999999999RP9999' then
1189 hr_utility.trace('No Business Number Entereed ');
1190 hr_utility.set_message(801,'PAY_74154_INCORRECT_BN');
1191 hr_utility.set_message_token('GRE_NAME',l_trans_name);
1192 pay_core_utils.push_message(801,'PAY_74154_INCORRECT_BN','P');
1193 pay_core_utils.push_token('GRE_NAME',l_trans_name);
1194 hr_utility.raise_error;
1195 return '1';
1196 end if;
1197 if l_acc_info_flag = 'N' then
1198 hr_utility.trace('No Accounting Contact info present');
1199 hr_utility.set_message(801,'PAY_74157_INCORRECT_ACNT_INFO');
1200 hr_utility.set_message_token('GRE_NAME',l_trans_name);
1201 pay_core_utils.push_message(801,'PAY_74157_INCORRECT_ACNT_INFO','P');
1202 pay_core_utils.push_token('GRE_NAME',l_trans_name);
1203 hr_utility.raise_error;
1204 return '1';
1205 end if;
1206 end if;
1207 end loop;
1208 RETURN '0';
1209 END validate_gre_data;
1210 END pay_ca_t4_mag;