[Home] [Help]
PACKAGE BODY: APPS.PAY_1099R_PKG
Source
1 PACKAGE BODY PAY_1099R_PKG as
2 /* $Header: pyus109r.pkb 120.2.12000000.5 2007/09/21 09:12:40 vaprakas noship $*/
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ****************************************************************** */
22 /*
23 Name : pyus109r.pkb
24 Description : This package defines the cursors needed to run
25 1099R Information Return Multi-Threaded
26
27 Change List
28 -----------
29 Date Name Vers Description
30 ---- ----- ----- ------------
31 08-SEP-2000 Fusman 115.0 Created
32 03-OCT-2000 Fusman 115.1 Changed the Range and Action creation
33 cursor for performance.
34 18-JAN-2002 meshah 115.2 Changed the sort cursor.
35 19-JAN-2002 meshah 115.3 dbdrv.
36 20-JAN-2002 ahanda 115.4 Changed sort_action to pass the full
37 date format.
38 11-SEP-2002 jgoswami 115.6 Changed sort cursor , changed for update
39 clause.
40 17-SEP-2002 jgoswami 115.7 Changed action cursor , removed for update
41 clause.
42
43 01-09-20032 asasthan 115.8 Fixes for terminated employee
44 Changed sort_action, removed join with paf
45 so that terminated ees get picked and
46 removed for update of clause.
47 01-09-20032 asasthan 115.9 Nocopy changes made
48 20-JAN-2003 jgoswami 115.10 Changed the action_creation cursor to
49 check for Reduced Subject (A_WAGES) >0 from
50 Gross (A_W2_GROSS_1099R) >0
51 22-JAN-2003 jgoswami 115.11 Commented out the code which locks the Year
52 End Pre-Process when a 1099r Paper
53 assignment action are created.
54 11-SEP-2003 jgoswami 115.12 Changed date format in sort cursor as the
55 EFFECTIVE_DATE value in the legislative parameter
56 is changed form DD-MON-YYYY to YYYY/MM/DD.
57 16-JAN-2003 jgoswami 115.14 Changed the action_creation cursor to
58 check for Gross (A_W2_GROSS_1099R) >0 from
59 Reduced Subject (A_WAGES) >0.Fix bug 3381162
60 14-MAR-2005 sackumar 115.15 4222032 Change in the Range Cursor removing
61 redundant use of bind Variable (:pactid)
62 14-MAR-2006 jgoswami 115.16 Changed the action_creation procedure for
63 performance, split c_action cursor to
64 multiple cursors and added range person
65 functionality. Multiple cursors created are
66 c_actions_with_location,
67 c_actions_with_org, c_actions_with_state,
68 c_actions_with_person,
69 c_actions_with_assign_sql
70 based on the SRS parameters.
71
72 24-MAR-2006 jgoswami 115.17 fix gscc errors
73 01-SEP-2006 saurgupt 115.18 Bug 3913757 : Modified the order by clause in sort_action.
74 21-SEP-2006 jgoswami 115.19 fix sort cursor exceed length issue
75 21-SEP-2006 jgoswami 115.20 fix gscc errors
76 09-NOV-2006 alikhar 115.21 Modified for 1099R PDF. (Bug 5440136)
77 24-NOV-2006 alikhar 115.22 Added tag PAYER_ADDR_CT_ST_ZP for 1099R PDF
78 22-DEC-2006 alikhar 115.23 Added tag PRINT_INSTRUCTION for 1099R PDF (5717266)
79 26-DEC-2006 alikhar 115.24 Fixed GSCC warnings.
80 15-JUN-2007 vaprakas 115.25 5979491 Corrected the difference between paper
81 and pdf report
82 07-SEP-2007 vaprakas 115.26 Modified changes for bug fix 5979491
83 21-SEP-2007 vaprakas 115.27 Modified code to display the DESIG. ROTH CONTRIB
84 */
85
86 /******************************************************************
87 ** private package global declarations
88 ******************************************************************/
89
90 g_package VARCHAR2(50) := 'pay_1099r_pkg.';
91 g_debug boolean := FALSE;
92 g_print_instr VARCHAR2(1) := 'Y';
93
94 ----------------------------------- range_cursor -------------------------------
95 ---
96 --
97 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
98
99 ln_assign_set number;
100 ln_year number;
101 ln_gre_id number;
102 l_procedure_name VARCHAR2(100);
103 --
104 begin
105 l_procedure_name := g_package||'range_cursor';
106 --hr_utility.trace_on(null,'pyus109r');
107 hr_utility.trace('Before the range cursor');
108 hr_utility.trace('Entering :'||l_procedure_name);
109
110 select pay_1099R_pkg.get_parameter('YEAR',ppa.legislative_parameters),
111 pay_1099R_pkg.get_parameter('TAX_ID',ppa.legislative_parameters),
112 pay_1099R_pkg.get_parameter('ASSIGN_SET',ppa.legislative_parameters)
113 into ln_year,
114 ln_gre_id,
115 ln_assign_set
116 from pay_payroll_actions ppa
117 where ppa.payroll_action_id = pactid;
118
119 -- hr_us_w2_rep.initialize_assignment_set(ln_assign_set);
120
121 sqlstr :=
122 'SELECT distinct to_number(paa_arch.serial_number)
123 FROM PAY_ASSIGNMENT_ACTIONS paa_arch,
124 PAY_PAYROLL_ACTIONS ppa_arch
125 WHERE :pactid is not null
126 AND ppa_arch.report_type = ''YREND''
127 AND to_char(ppa_arch.effective_date,''YYYY'')= '''||ln_year||'''
128 AND pay_yrend_reports_pkg.get_parameter(''TRANSFER_GRE'',
129 ppa_arch.legislative_parameters)= '''||ln_gre_id||'''
130 AND ppa_arch.action_status = ''C''
131 AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
132 order by to_number(paa_arch.serial_number) ';
133
134 hr_utility.trace('After the range cursor');
135
136 hr_utility.trace('Leaving :'||l_procedure_name);
137 end range_cursor;
138
139
140
141 ---------------------------- action_creation -----------------------------
142
143 procedure action_creation(pactid in number,
144 stperson in number,
145 endperson in number,
146 chunk in number) is
147 -- jatin
148 -- new cursors start here
149
150
151 lockingactid number;
152 lockedactid number;
153 assignid number;
154 greid number;
155
156 num number;
157 l_effective_end_date DATE;
158 l_effective_date DATE;
159 l_report_type pay_payroll_actions.report_type%type;
160 l_report_category pay_payroll_actions.report_category%type;
161 l_report_qualifier pay_payroll_actions.report_qualifier%type;
162 l_report_format pay_report_format_mappings_f.report_format%type;
163 l_range_person_on BOOLEAN;
164 l_subj_whable ff_user_entities.user_entity_name%TYPE;
165 l_subj_nwhable ff_user_entities.user_entity_name%TYPE;
166 l_tuid_context ff_contexts.context_id%TYPE;
167 l_juri_context ff_contexts.context_id%TYPE;
168
169 l_procedure_name VARCHAR2(100);
170 l_session_date date;
171 l_year number ;
172 l_gre_id pay_assignment_actions.tax_unit_id%type;
173 l_org_id per_assignments_f.organization_id%type;
174 l_loc_id per_assignments_f.location_id%type;
175 l_per_id per_assignments_f.person_id%type;
176 l_ssn per_people_f.national_identifier%type;
177 l_state_code pay_us_states.state_code%type;
178 l_asg_set_id number;
179 l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
180 l_eoy_start_date date;
181 ln_gross_bal number;
182
183
184 cursor c_payroll_param (cp_pactid in number) is
185 select to_number(pay_1099R_pkg.get_parameter('YEAR',ppa1.legislative_parameters)),
186 to_number(pay_1099R_pkg.get_parameter('TAX_ID',ppa1.legislative_parameters)),
187 to_number(pay_1099R_pkg.get_parameter('ORG_ID',ppa1.legislative_parameters)),
188 to_number(pay_1099R_pkg.get_parameter('LOC_ID',ppa1.legislative_parameters)),
189 to_number(pay_1099R_pkg.get_parameter('PER_ID',ppa1.legislative_parameters)),
190 pay_1099R_pkg.get_parameter('SSN',ppa1.legislative_parameters),
191 pay_1099R_pkg.get_parameter('ST_COD',ppa1.legislative_parameters),
192 to_number(pay_1099R_pkg.get_parameter('ASSIGN_SET',ppa1.legislative_parameters)),
193 ppa.effective_date,
194 ppa.payroll_action_id,
195 ppa.start_date,
196 ppa1.effective_date,
197 ppa1.report_type,
198 ppa1.report_qualifier,
199 ppa1.report_category
200 from pay_payroll_actions ppa, /* EOY payroll action id */
201 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
202 where ppa1.payroll_action_id = cp_pactid
203 and ppa.effective_date = ppa1.effective_date
204 and ppa.report_type = 'YREND'
205 and pay_1099R_pkg.get_parameter
206 ('TAX_ID',ppa1.legislative_parameters) =
207 pay_1099R_pkg.get_parameter
208 ('TRANSFER_GRE',ppa.legislative_parameters);
209
210
211 /* cursor c_payroll_param (cp_pactid in number) is
212 select pay_1099R_pkg.get_parameter('YEAR',ppa.legislative_parameters),
213 pay_1099R_pkg.get_parameter('TAX_ID',ppa.legislative_parameters),
214 pay_1099R_pkg.get_parameter('ORG_ID',ppa.legislative_parameters),
215 pay_1099R_pkg.get_parameter('LOC_ID',ppa.legislative_parameters),
216 pay_1099R_pkg.get_parameter('PER_ID',ppa.legislative_parameters),
217 pay_1099R_pkg.get_parameter('SSN',ppa.legislative_parameters),
218 pay_1099R_pkg.get_parameter('ST_COD',ppa.legislative_parameters),
219 pay_1099R_pkg.get_parameter('ASSIGN_SET',ppa.legislative_parameters),
220 effective_date,
221 report_type,
222 report_qualifier,
223 report_category
224 from pay_payroll_actions ppa
225 where ppa.payroll_action_id = cp_pactid;
226 */
227
228 /* when person or ssn selected */
229 CURSOR c_actions_with_person is
230 SELECT paa_arch.assignment_action_id,
231 paa_arch.assignment_id,
232 paa_arch.tax_unit_id,
233 paf.effective_end_date
234 FROM per_assignments_f paf,
235 pay_assignment_actions paa_arch
236 WHERE paa_arch.payroll_action_id = l_eoy_payroll_action_id
237 AND paa_arch.action_status = 'C'
238 AND paf.PERSON_ID = l_per_id
239 AND paa_arch.assignment_id = paf.assignment_id
240 AND paf.effective_start_date = (select max(paf2.effective_start_date)
241 from per_assignments_f paf2
242 where paf2.assignment_id = paf.assignment_id
243 and paf2.effective_start_date <= l_session_date)
244 AND paf.effective_end_date >= l_eoy_start_date
245 AND paf.assignment_type = 'E'
246 AND paf.person_id between stperson and endperson;
247
248 CURSOR c_state_context (p_context_name varchar2) is
249 select context_id from ff_contexts
250 where context_name = p_context_name;
251
252
253
254 CURSOR c_state_ueid (p_user_entity_name varchar2) is
255 select user_entity_id
256 from ff_user_entities
257 where user_entity_name = p_user_entity_name
258 and legislation_code = 'US';
259
260
261
262 TYPE RefCurType is REF CURSOR;
263 c_actions_no_selection RefCurType;
264 c_actions_with_location RefCurType;
265 c_actions_with_org RefCurType;
266 c_actions_with_state RefCurType;
267 c_actions_with_assign_set RefCurType;
268
269 c_actions_no_selection_sql varchar2(10000);
270 c_actions_with_location_sql varchar2(10000);
271 c_actions_with_org_sql varchar2(10000);
272 c_actions_with_state_sql varchar2(10000);
273 c_actions_with_assign_sql varchar2(10000);
274
275 -- new cursors end here
276 -- jatin
277
278
279 begin
280
281 l_procedure_name := g_package||'action_creation';
282
283 --hr_utility.trace_on(null,'pyus109r');
284 hr_utility.trace('Entering :'||l_procedure_name);
285 hr_utility.set_location('action_cursor',1);
286 hr_utility.trace('In the action cursor');
287
288 open c_payroll_param(pactid);
289 fetch c_payroll_param into l_year,
290 l_gre_id,
291 l_org_id,
292 l_loc_id,
293 l_per_id,
294 l_ssn,
295 l_state_code,
296 l_asg_set_id,
297 l_session_date,
298 l_eoy_payroll_action_id,
299 l_eoy_start_date,
300 l_effective_date,
301 l_report_type,
302 l_report_qualifier,
303 l_report_category;
304
305 close c_payroll_param;
306
307 Begin
308 select report_format
309 into l_report_format
310 from pay_report_format_mappings_f
311 where report_type = l_report_type
312 and report_qualifier = l_report_qualifier
313 and report_category = l_report_category
314 and l_effective_date between
315 effective_start_date and effective_end_date;
316 Exception
317 When Others Then
318 l_report_format := Null ;
319 End ;
320
321 hr_utility.set_location(l_procedure_name, 2);
322 l_range_person_on := pay_ac_utility.range_person_on
323 ( p_report_type => l_report_type,
324 p_report_format => l_report_format,
325 p_report_qualifier => l_report_qualifier,
326 p_report_category => l_report_category);
327 /* when no selection is entered */
328 if((l_loc_id is null ) and
329 (l_org_id is null ) and
330 (l_per_id is null ) and
331 (l_ssn is null ) and
332 (l_state_code is null ) and
336 if l_range_person_on = TRUE Then
333 (l_asg_set_id is null )) then
334
335 hr_utility.set_location(l_procedure_name, 5);
337 hr_utility.set_location(l_procedure_name, 10);
338 hr_utility.trace('Range Person ID Functionality is enabled') ;
339 c_actions_no_selection_sql :=
340 'SELECT paa_arch.assignment_action_id,
341 paa_arch.assignment_id,
342 paa_arch.tax_unit_id,
343 paf.effective_end_date
344 FROM per_assignments_f paf,
345 pay_assignment_actions paa_arch,
346 pay_population_ranges ppr
347 WHERE paa_arch.action_status = ''C''
348 AND paa_arch.payroll_action_id +0= ' || l_eoy_payroll_action_id || '
349 AND paa_arch.assignment_id = paf.assignment_id
350 AND paf.effective_start_date =
351 (select max(paf2.effective_start_date)
352 from per_assignments_f paf2
353 where paf2.assignment_id = paf.assignment_id
354 and paf2.effective_start_date <= ''' || l_session_date || ''')
355 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
356 AND paf.assignment_type = ''E''
357 and paf.primary_flag = ''Y''
358 AND ppr.payroll_action_id = ' || pactid || '
359 AND ppr.chunk_number = ' || chunk || '
360 AND paf.person_id = ppr.person_id
361 and paf.person_id = to_number(paa_arch.serial_number)';
362 else
363 hr_utility.set_location(l_procedure_name, 15);
364 c_actions_no_selection_sql :=
365 'SELECT paa_arch.assignment_action_id,
366 paa_arch.assignment_id,
367 paa_arch.tax_unit_id,
368 paf.effective_end_date
369 FROM per_assignments_f paf,
370 pay_assignment_actions paa_arch
371 WHERE paa_arch.action_status = ''C''
372 AND paa_arch.payroll_action_id +0= ' || l_eoy_payroll_action_id || '
373 AND paa_arch.assignment_id = paf.assignment_id
374 AND paf.effective_start_date =
375 (select max(paf2.effective_start_date)
376 from per_assignments_f paf2
377 where paf2.assignment_id = paf.assignment_id
378 and paf2.effective_start_date <= ''' || l_session_date || ''')
379 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
380 AND paf.assignment_type = ''E''
381 and paf.primary_flag = ''Y''
382 AND paf.person_id between ' || stperson || ' and ' || endperson || '
383 and paf.person_id = to_number(paa_arch.serial_number)';
384 end if ;
385
386 hr_utility.set_location(l_procedure_name, 20);
387 OPEN c_actions_no_selection FOR c_actions_no_selection_sql;
388 num := 0;
389
390 loop
391 fetch c_actions_no_selection into lockedactid,assignid,greid,l_effective_end_date;
392 if c_actions_no_selection%found then
393 num := num + 1;
394 hr_utility.trace('In the c_actions_no_selection%found in action cursor');
395 else
396 hr_utility.trace('In the c_actions_no_selection%notfound in action cursor');
397 exit;
398 end if;
399
400 ln_gross_bal := hr_us_w2_rep.get_w2_arch_bal(
401 lockedactid,
402 'A_W2_GROSS_1099R',
403 greid,
404 '00-000-0000',
405 0);
406
407 -- we need to create assignment_actions only if the GROSS
408 -- is greater than ZERO.
409 hr_utility.trace('Before IF Check for GROSS > 0 ');
410 if ln_gross_bal > 0 then
411
412 -- we need to insert one action for each of the
413 -- rows that we return from the cursor (i.e. one
414 -- for each assignment/pre-payment/reversal).
415 hr_utility.set_location(l_procedure_name, 25);
416 hr_utility.trace('Before inserting the action record');
417
418 select pay_assignment_actions_s.nextval
419 into lockingactid
420 from dual;
421
422 -- insert the action record.
423 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
424
425 -- Update serial_numbrt of Pay_assignment_actions with the
426 -- assignment_action_id .
427 update pay_assignment_actions
428 set serial_number = lockedactid
429 where assignment_action_id = lockingactid;
430 end if;
431 end loop;
432 close c_actions_no_selection;
433
434 end if;
435 hr_utility.set_location(l_procedure_name, 30);
436
437
438 /* when location is entered */
439 if l_loc_id is not null then
440 if l_range_person_on = TRUE Then
441 hr_utility.set_location(l_procedure_name, 35);
442 c_actions_with_location_sql :=
443 'SELECT paa_arch.assignment_action_id,
444 paa_arch.assignment_id,
445 paa_arch.tax_unit_id,
446 paf.effective_end_date
447 FROM per_periods_of_service pps,
451 /* disabling the index for performance reason */
448 per_assignments_f paf,
449 pay_assignment_actions paa_arch,
450 pay_population_ranges ppr
452 WHERE paa_arch.payroll_action_id + 0 = ' || l_eoy_payroll_action_id || '
453 AND paa_arch.action_status = ''C''
454 AND paa_arch.assignment_id = paf.assignment_id
455 AND nvl(pps.final_process_date,''' || l_session_date || ''')
456 between paf.effective_start_date
457 and paf.effective_end_date
458 AND paf.location_id = ' || l_loc_id || '
459 AND paf.effective_start_date =
460 (select max(paf2.effective_start_date)
461 from per_assignments_f paf2
462 where paf2.assignment_id = paf.assignment_id
463 and paf2.effective_start_date <= ''' || l_session_date || ''')
464 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
465 AND paf.assignment_type = ''E''
466 AND pps.period_of_service_id = paf.period_of_service_id
467 AND ppr.payroll_action_id = ' || pactid || '
468 AND ppr.chunk_number = ' || chunk || '
469 AND paf.person_id = ppr.person_id' ;
470 else
471 hr_utility.set_location(l_procedure_name, 40);
472 c_actions_with_location_sql :=
473 'SELECT paa_arch.assignment_action_id,
474 paa_arch.assignment_id,
475 paa_arch.tax_unit_id,
476 paf.effective_end_date
477 FROM per_periods_of_service pps,
478 per_assignments_f paf,
479 pay_assignment_actions paa_arch
480 /* disabling the index for performance reason */
481 WHERE paa_arch.payroll_action_id + 0 = ' || l_eoy_payroll_action_id || '
482 AND paa_arch.action_status = ''C''
483 AND paa_arch.assignment_id = paf.assignment_id
484 AND nvl(pps.final_process_date,''' || l_session_date || ''')
485 between paf.effective_start_date
486 and paf.effective_end_date
487 AND paf.location_id = ' || l_loc_id || '
488 AND paf.effective_start_date =
489 (select max(paf2.effective_start_date)
490 from per_assignments_f paf2
491 where paf2.assignment_id = paf.assignment_id
492 and paf2.effective_start_date <= ''' || l_session_date || ''' )
493 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
494 AND paf.assignment_type = ''E''
495 AND pps.period_of_service_id = paf.period_of_service_id
496 AND paf.person_id between ' || stperson || ' and ' || endperson || '';
497 end if ;
498
499 hr_utility.set_location(l_procedure_name, 40);
500 OPEN c_actions_with_location FOR c_actions_with_location_sql;
501 num := 0;
502
503 loop
504 fetch c_actions_with_location into lockedactid,assignid,greid,l_effective_end_date;
505
506 if c_actions_with_location%found then
507 num := num + 1;
508 hr_utility.trace('In the c_actions_with_location%found in action cursor');
509 else
510 hr_utility.trace('In the c_actions_with_location%notfound in action cursor');
511 exit;
512 end if;
513
514 ln_gross_bal := hr_us_w2_rep.get_w2_arch_bal(
515 lockedactid,
516 'A_W2_GROSS_1099R',
517 greid,
518 '00-000-0000',
519 0);
520
521 -- we need to create assignment_actions only if the GROSS
522 -- is greater than ZERO.
526 hr_utility.set_location(l_procedure_name, 45);
523 hr_utility.trace('Before IF Check for GROSS > 0 ');
524 if ln_gross_bal > 0 then
525
527 hr_utility.trace('Before inserting the action record');
528
529 select pay_assignment_actions_s.nextval
530 into lockingactid
531 from dual;
532
533 -- insert the action record.
534 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
535
536 -- Update serial_numbrt of Pay_assignment_actions with the
537 -- assignment_action_id .
538 update pay_assignment_actions
539 set serial_number = lockedactid
540 where assignment_action_id = lockingactid;
541 end if;
542 end loop;
543 close c_actions_with_location;
544
545 end if;
546 hr_utility.set_location(l_procedure_name, 50);
547
548
549 /* when org is entered */
550 if l_org_id is not null then
551
552 if l_range_person_on = TRUE Then
553 hr_utility.set_location(l_procedure_name, 60);
554 hr_utility.trace('Range Person ID Functionality is enabled') ;
555 c_actions_with_org_sql :=
556 'SELECT paa_arch.assignment_action_id,
557 paa_arch.assignment_id,
558 paa_arch.tax_unit_id,
559 paf.effective_end_date
560 FROM per_periods_of_service pps,
561 per_assignments_f paf,
562 pay_assignment_actions paa_arch,
563 pay_population_ranges ppr
564 /* disabling the index for performance reason */
565 WHERE paa_arch.payroll_action_id +0 = ' || l_eoy_payroll_action_id || '
566 AND paa_arch.action_status = ''C''
567 AND nvl(pps.final_process_date,''' || l_session_date || ''')
568 between paf.effective_start_date
569 and paf.effective_end_date
570 AND paf.organization_id = ' || l_org_id || '
571 AND paa_arch.assignment_id = paf.assignment_id
572 AND paf.effective_start_date =
573 (select max(paf2.effective_start_date)
574 from per_assignments_f paf2
575 where paf2.assignment_id = paf.assignment_id
576 and paf2.effective_start_date <= ''' || l_session_date || ''')
577 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
578 AND paf.assignment_type = ''E''
579 AND pps.period_of_service_id = paf.period_of_service_id
580 AND ppr.payroll_action_id = ' || pactid || '
581 AND ppr.chunk_number = ' || chunk || '
582 AND paf.person_id = ppr.person_id';
583 else
584 hr_utility.set_location(l_procedure_name, 70);
585 c_actions_with_org_sql :=
586 'SELECT paa_arch.assignment_action_id,
587 paa_arch.assignment_id,
588 paa_arch.tax_unit_id,
589 paf.effective_end_date
590 FROM per_periods_of_service pps,
591 per_assignments_f paf,
592 pay_assignment_actions paa_arch
593 /* disabling the index for performance reason */
594 WHERE paa_arch.payroll_action_id +0 = ' || l_eoy_payroll_action_id || '
595 AND paa_arch.action_status = ''C''
596 AND nvl(pps.final_process_date,''' || l_session_date || ''')
597 between paf.effective_start_date
598 and paf.effective_end_date
599 AND paf.organization_id = ' || l_org_id || '
600 AND paa_arch.assignment_id = paf.assignment_id
601 AND paf.effective_start_date =
602 (select max(paf2.effective_start_date)
603 from per_assignments_f paf2
604 where paf2.assignment_id = paf.assignment_id
605 and paf2.effective_start_date <= ''' || l_session_date || ''')
606 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
607 AND paf.assignment_type = ''E''
608 AND pps.period_of_service_id = paf.period_of_service_id
609 AND paf.person_id between ' || stperson || ' and ' || endperson ||'';
610 end if ;
611
612 hr_utility.set_location(l_procedure_name, 80);
613 OPEN c_actions_with_org FOR c_actions_with_org_sql;
614 num := 0;
615
616 loop
617 fetch c_actions_with_org into lockedactid,assignid,greid,l_effective_end_date;
618
619 if c_actions_with_org%found then
620 num := num + 1;
621 hr_utility.trace('In the c_actions_with_org%found in action cursor');
622 else
623 hr_utility.trace('In the c_actions_with_org%notfound in action cursor');
624 exit;
625 end if;
626
627
628 ln_gross_bal := hr_us_w2_rep.get_w2_arch_bal(
629 lockedactid,
630 'A_W2_GROSS_1099R',
631 greid,
632 '00-000-0000',
633 0);
634
635 -- we need to create assignment_actions only if the GROSS
639
636 -- is greater than ZERO.
637 hr_utility.trace('Before IF Check for GROSS > 0 ');
638 if ln_gross_bal > 0 then
640 hr_utility.set_location(l_procedure_name, 90);
641 hr_utility.trace('Before inserting the action record');
642
643 select pay_assignment_actions_s.nextval
644 into lockingactid
645 from dual;
646
647 -- insert the action record.
648 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
649
650 -- Update serial_numbrt of Pay_assignment_actions with the
651 -- assignment_action_id .
652 update pay_assignment_actions
653 set serial_number = lockedactid
654 where assignment_action_id = lockingactid;
655 end if;
656 end loop;
657 close c_actions_with_org;
658
659 end if;
660
661 hr_utility.set_location(l_procedure_name, 100);
662
663 /* when person or SSN is entered */
664
665 if (l_ssn is not null and l_per_id is null ) then
666 select person_id into l_per_id
667 from per_people_f ppf
668 where national_identifier = l_ssn
669 and l_effective_date between effective_start_date
670 and effective_end_date;
671 end if;
672
673 if (l_per_id is not null ) then
674 open c_actions_with_person;
675 num := 0;
676 loop
677 hr_utility.set_location('procpyr',2);
678 hr_utility.trace('after the loop in c_actions_with_person');
679 fetch c_actions_with_person into lockedactid,assignid,greid,l_effective_end_date;
680
681 if c_actions_with_person%found then
682 num := num + 1;
683 hr_utility.trace('In the c_actions_with_person%found in action cursor');
684 else
685 hr_utility.trace('In the c_actions_with_person%notfound in action cursor');
686 exit;
687 end if;
688
689 ln_gross_bal := hr_us_w2_rep.get_w2_arch_bal(
690 lockedactid,
691 'A_W2_GROSS_1099R',
692 greid,
693 '00-000-0000',
694 0);
695
696 -- we need to create assignment_actions only if the GROSS
697 -- is greater than ZERO.
698 hr_utility.trace('Before IF Check for GROSS > 0 ');
699 if ln_gross_bal > 0 then
700
701 hr_utility.set_location(l_procedure_name, 110);
702 hr_utility.trace('Before inserting the action record');
703
704 select pay_assignment_actions_s.nextval
705 into lockingactid
706 from dual;
707
708 -- insert the action record.
709 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
710
711 -- Update serial_numbrt of Pay_assignment_actions with the
712 -- assignment_action_id .
713 update pay_assignment_actions
714 set serial_number = lockedactid
715 where assignment_action_id = lockingactid;
716 end if;
717 end loop;
718 close c_actions_with_person;
719
720 end if;
721
722 hr_utility.set_location(l_procedure_name, 120);
723 /* when state is entered */
724 if l_state_code is not null then
725 hr_utility.set_location(l_procedure_name, 130);
726
727 hr_utility.trace('l_state_code = ' || l_state_code);
728 open c_state_context('TAX_UNIT_ID');
729 fetch c_state_context into l_tuid_context;
730 close c_state_context;
731
732 open c_state_context('JURISDICTION_CODE');
733 fetch c_state_context into l_juri_context;
734 close c_state_context;
735
736 if l_range_person_on = TRUE Then
737 hr_utility.set_location(l_procedure_name, 140);
738 hr_utility.trace('Range Person ID Functionality is enabled') ;
739 c_actions_with_state_sql :=
740 'SELECT paa_arch.assignment_action_id,
741 paa_arch.assignment_id,
742 paa_arch.tax_unit_id,
743 paf.effective_end_date
744 FROM per_assignments_f paf,
745 pay_assignment_actions paa_arch,
746 pay_population_ranges ppr
747 WHERE paa_arch.payroll_action_id +0 = ' || l_eoy_payroll_action_id || '
748 AND paa_arch.action_status = ''C''
749 AND paa_arch.assignment_id = paf.assignment_id
750 AND paf.effective_start_date =
751 (select max(paf2.effective_start_date)
752 from per_assignments_f paf2
753 where paf2.assignment_id = paf.assignment_id
754 and paf2.effective_start_date <= ''' || l_session_date || ''')
755 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
756 AND paf.assignment_type = ''E''
757 AND ppr.payroll_action_id = ' || pactid || '
758 AND ppr.chunk_number = ' || chunk || '
759 AND paf.person_id = ppr.person_id ';
760 else
764 paa_arch.assignment_id,
761 hr_utility.set_location(l_procedure_name, 150);
762 c_actions_with_state_sql :=
763 'SELECT paa_arch.assignment_action_id,
765 paa_arch.tax_unit_id,
766 paf.effective_end_date
767 FROM per_assignments_f paf,
768 pay_assignment_actions paa_arch
769 WHERE paa_arch.payroll_action_id +0 = ' || l_eoy_payroll_action_id || '
770 AND paa_arch.action_status = ''C''
771 AND paa_arch.assignment_id = paf.assignment_id
772 AND paf.effective_start_date =
773 (select max(paf2.effective_start_date)
774 from per_assignments_f paf2
775 where paf2.assignment_id = paf.assignment_id
776 and paf2.effective_start_date <= ''' || l_session_date || ''')
777 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
778 AND paf.assignment_type = ''E''
779 AND paf.person_id between ' || stperson || ' and ' || endperson;
780 end if;
781
782 hr_utility.set_location(l_procedure_name, 160);
783
784 open c_state_ueid('A_SIT_SUBJ_WHABLE_PER_JD_GRE_YTD');
785 fetch c_state_ueid into l_subj_whable;
786 close c_state_ueid;
787
788 open c_state_ueid('A_SIT_SUBJ_NWHABLE_PER_JD_GRE_YTD');
789 fetch c_state_ueid into l_subj_nwhable;
790 close c_state_ueid;
791
792 hr_utility.set_location(l_procedure_name, 170);
793 c_actions_with_state_sql := c_actions_with_state_sql ||
794 ' AND exists ( select 1 from dual
795 where 1 =
796 (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
797 from ff_archive_items fai,
798 ff_archive_item_contexts fic1,
799 ff_archive_item_contexts fic2
800 where fai.context1 = paa_arch.assignment_action_id
801 and fai.user_entity_id in (' || l_subj_whable || ',
802 ' || l_subj_nwhable || ')
803 and fai.archive_item_id = fic1.archive_item_id
804 and fic1.context_id = ' || l_tuid_context || '
805 and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
806 and fai.archive_item_id = fic2.archive_item_id
807 and fic2.context_id = ' || l_juri_context || '
808 and substr(ltrim(rtrim(fic2.context)),1,2) = ' || l_state_code || ' ))';
809 --
810 hr_utility.set_location(l_procedure_name, 210);
811
812
813 num := 0;
814 OPEN c_actions_with_state FOR c_actions_with_state_sql;
815 loop
816 fetch c_actions_with_state into lockedactid,assignid,greid,l_effective_end_date;
817
818 if c_actions_with_state%found then
819 num := num + 1;
820 hr_utility.trace('In the c_actions_with_state%found in action cursor');
821 else
822 hr_utility.trace('In the c_actions_with_state%notfound in action cursor');
823 exit;
824 end if;
825
826
827 ln_gross_bal := hr_us_w2_rep.get_w2_arch_bal(
828 lockedactid,
829 'A_W2_GROSS_1099R',
830 greid,
831 '00-000-0000',
832 0);
833
834 -- we need to create assignment_actions only if the GROSS
835 -- is greater than ZERO.
836 hr_utility.trace('Before IF Check for GROSS > 0 ');
837 if ln_gross_bal > 0 then
838
839 hr_utility.set_location(l_procedure_name, 220);
840 hr_utility.trace('Before inserting the action record');
841
842 select pay_assignment_actions_s.nextval
843 into lockingactid
844 from dual;
845
846 -- insert the action record.
847 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
848
849 -- Update serial_numbrt of Pay_assignment_actions with the
850 -- assignment_action_id .
851 update pay_assignment_actions
852 set serial_number = lockedactid
853 where assignment_action_id = lockingactid;
854 end if;
855 end loop;
856 close c_actions_with_state;
857
858 end if;
859 hr_utility.set_location(l_procedure_name, 230);
860
861 /* when assignment set is entered */
862 if l_asg_set_id is not null then
863
864 if l_range_person_on = TRUE Then
865 hr_utility.set_location(l_procedure_name, 240);
866 hr_utility.trace('Range Person ID Functionality is enabled') ;
867 c_actions_with_assign_sql :=
868 'SELECT paa_arch.assignment_action_id,
869 paa_arch.assignment_id,
870 paa_arch.tax_unit_id,
871 paf.effective_end_date
872 FROM per_assignments_f paf,
873 pay_assignment_actions paa_arch,
877 AND paa_arch.assignment_id = paf.assignment_id
874 pay_population_ranges ppr
875 WHERE paa_arch.action_status = ''C''
876 AND paa_arch.payroll_action_id + 0 = ' || l_eoy_payroll_action_id || '
878 AND paf.effective_start_date = (select max(paf2.effective_start_date)
879 from per_assignments_f paf2
880 where paf2.assignment_id = paf.assignment_id
881 and paf2.effective_start_date <= ''' || l_session_date || ''')
882 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
883 AND paf.assignment_type = ''E''
884 AND ppr.payroll_action_id = ' || pactid || '
885 AND ppr.chunk_number = ' || chunk || '
886 AND paf.person_id = ppr.person_id
887 AND exists ( select 1 /* Selected Assignment Set */
888 from hr_assignment_set_amendments hasa
889 where hasa.assignment_set_id = ' || l_asg_set_id || '
890 and hasa.assignment_id = paa_arch.assignment_id
891 and upper(hasa.include_or_exclude) = ''I'')';
892 else
893 hr_utility.set_location(l_procedure_name, 250);
894 c_actions_with_assign_sql :=
895 'SELECT paa_arch.assignment_action_id,
896 paa_arch.assignment_id,
897 paa_arch.tax_unit_id,
898 paf.effective_end_date
899 FROM per_assignments_f paf,
900 pay_assignment_actions paa_arch
901 WHERE paa_arch.action_status = ''C''
902 AND paa_arch.payroll_action_id + 0 = ' || l_eoy_payroll_action_id || '
903 AND paa_arch.assignment_id = paf.assignment_id
904 AND paf.effective_start_date = (select max(paf2.effective_start_date)
905 from per_assignments_f paf2
906 where paf2.assignment_id = paf.assignment_id
907 and paf2.effective_start_date <= ''' || l_session_date || ''')
908 AND paf.effective_end_date >= ''' || l_eoy_start_date || '''
909 AND paf.assignment_type = ''E''
910 AND paf.person_id between ' || stperson || ' and ' || endperson || '
911 AND exists ( select 1 /* Selected Assignment Set */
912 from hr_assignment_set_amendments hasa
913 where hasa.assignment_set_id = ' || l_asg_set_id || '
914 and hasa.assignment_id = paa_arch.assignment_id
915 and upper(hasa.include_or_exclude) = ''I'')';
916 end if ;
917
918 hr_utility.set_location(l_procedure_name, 260);
919 OPEN c_actions_with_assign_set FOR c_actions_with_assign_sql;
920 num := 0;
921
922 loop
923 fetch c_actions_with_assign_set into lockedactid,assignid,greid,l_effective_end_date;
924
925 if c_actions_with_assign_set%found then
926 num := num + 1;
927 hr_utility.trace('In the c_actions_with_assign_set%found in action cursor');
928 else
929 hr_utility.trace('In the c_actions_with_assign_set%notfound in action cursor');
930 exit;
931 end if;
932
933
934 ln_gross_bal := hr_us_w2_rep.get_w2_arch_bal(
935 lockedactid,
936 'A_W2_GROSS_1099R',
937 greid,
938 '00-000-0000',
939 0);
940
941 -- we need to create assignment_actions only if the GROSS
942 -- is greater than ZERO.
943 hr_utility.trace('Before IF Check for GROSS > 0 ');
944 if ln_gross_bal > 0 then
945
946 hr_utility.set_location(l_procedure_name, 270);
947 hr_utility.trace('Before inserting the action record');
948
949 hr_utility.set_location('procpyr',3);
950
951 select pay_assignment_actions_s.nextval
952 into lockingactid
953 from dual;
954
955 -- insert the action record.
956 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
957
958 -- Update serial_numbrt of Pay_assignment_actions with the
959 -- assignment_action_id .
960 update pay_assignment_actions
961 set serial_number = lockedactid
962 where assignment_action_id = lockingactid;
963 end if;
964 end loop;
965 close c_actions_with_assign_set;
966
967 end if;
968
969 hr_utility.set_location(l_procedure_name, 300);
970 /* } 4946225 */
971
972 hr_utility.trace('End of the action cursor');
973 hr_utility.trace('Leaving :'||l_procedure_name);
974
975 end action_creation;
976
977 ---------------------------------- sort_action ------------------------------
978
979 procedure sort_action
980 (
981 payactid in varchar2, /* payroll action id */
982 sqlstr in out nocopy varchar2, /* string holding the sql statement */
983 len out nocopy number /* length of the sql string */
984 ) is
985
989 l_procedure_name VARCHAR2(100);
986 --l_session_date varchar2(11);
987 l_session_date varchar2(11);
988 l_effective_date varchar2(11);
990
991 begin
992 l_procedure_name := g_package||'sort_action';
993 hr_utility.trace('Entering :'||l_procedure_name);
994 hr_utility.trace('Beginning of the sort_action cursor');
995 select to_char(ppa.effective_date, 'DD-MON-YYYY'),
996 to_char(fnd_date.canonical_to_date(pay_1099R_pkg.get_parameter('EFFECTIVE_DATE',
997 ppa.legislative_parameters)),'DD-MON-YYYY')
998 into l_effective_date, l_session_date
999 from pay_payroll_actions ppa
1000 where payroll_action_id = payactid;
1001
1002 if to_date(l_session_date,'DD-MM-YYYY') > to_date(l_effective_date,'DD-MM-YYYY') then
1003 l_effective_date := l_session_date;
1004 end if;
1005
1006 sqlstr :=
1007 'select paa1.rowid
1008 /* we need the row id of the assignment actions
1009 that are created by PYUGEN */
1010 from pay_assignment_actions paa,
1011 pay_assignment_actions paa1, /* PYUGEN assignment action */
1012 pay_payroll_actions ppa1 /* PYUGEN payroll action id */
1013 where ppa1.payroll_action_id = :pactid
1014 and paa1.payroll_action_id = ppa1.payroll_action_id
1015 and paa.assignment_action_id = paa1.serial_number
1016 order by
1017 decode(pay_1099R_pkg.get_parameter(''SORT_1'',ppa1.legislative_parameters),
1018 ''Employee_Name'',
1019 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
1020 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
1021 decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES'' ),
1022 null, null,
1023 substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1)),
1024 ''Social_Security_Number'',
1025 nvl(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_NATIONAL_IDENTIFIER''),''Applied For''),
1026 ''Zip_Code'',
1027 hr_us_w2_rep.get_w2_postal_code(to_number(paa.serial_number),to_date('''||l_effective_date||''',''DD-MM-YYYY'')),
1028 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
1029 hr_us_w2_rep.get_per_item( paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
1030 decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),
1031 null, null,
1032 substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1))),
1033 decode(pay_1099R_pkg.get_parameter(''SORT_2'',ppa1.legislative_parameters),
1034 ''Employee_Name'',
1035 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
1036 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
1037 decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES'' ),
1038 null, null,
1039 substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1)),
1040 ''Social_Security_Number'',
1041 nvl(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
1042 ''Zip_Code'',
1043 hr_us_w2_rep.get_w2_postal_code( to_number(paa.serial_number),
1044 to_date('''||l_effective_date||''',''DD-MM-YYYY'')),
1045 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
1046 hr_us_w2_rep.get_per_item( paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
1047 decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),
1048 null, null,
1049 substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1))),
1050 decode(pay_1099R_pkg.get_parameter(''SORT_3'',ppa1.legislative_parameters),
1051 ''Employee_Name'',
1052 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
1053 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
1054 decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES'' ),
1055 null, null,
1056 substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1)),
1057 ''Social_Security_Number'',
1058 nvl(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
1059 ''Zip_Code'',
1060 hr_us_w2_rep.get_w2_postal_code( to_number(paa.serial_number),
1061 to_date('''||l_effective_date||''',''DD-MM-YYYY'')),
1062 hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
1063 hr_us_w2_rep.get_per_item( paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
1064 decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),
1065 null, null,
1066 substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1)))';
1067
1068 len := length(sqlstr); -- return the length of the string.
1069 hr_utility.trace('length of Sort Cursor '||len);
1070
1071 hr_utility.trace('End of the sort_Action cursor');
1072 hr_utility.trace('Leaving :'||l_procedure_name);
1073 end sort_action;
1074 --
1075 ------------------------------ get_parameter -------------------------------
1076 function get_parameter(name in varchar2,
1077 parameter_list varchar2) return varchar2
1078 is
1079 start_ptr number;
1080 end_ptr number;
1081 token_val pay_payroll_actions.legislative_parameters%type;
1082 par_value pay_payroll_actions.legislative_parameters%type;
1083 begin
1084 --
1085 token_val := name||'=';
1086 --
1090 --
1087 start_ptr := instr(parameter_list, token_val) + length(token_val);
1088 end_ptr := instr(parameter_list, ' ',start_ptr);
1089
1091 /* if there is no spaces use then length of the string */
1092 if end_ptr = 0 then
1093 end_ptr := length(parameter_list)+1;
1094 end if;
1095 --
1096 /* Did we find the token */
1097 if instr(parameter_list, token_val) = 0 then
1098 par_value := NULL;
1099 else
1100 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
1101 end if;
1102 --
1103 return par_value;
1104 --
1105 end get_parameter;
1106
1107 -------------------------- load_xml ----------------------------
1108 PROCEDURE LOAD_XML (
1109 P_NODE_TYPE varchar2,
1110 P_NODE varchar2,
1111 P_DATA varchar2
1112 ) AS
1113
1114 l_proc_name varchar2(100) := 'pay_1099r_pkg.load_xml';
1115 l_data varchar2(500);
1116 BEGIN
1117
1118 hr_utility.trace('Entering : '||l_proc_name);
1119
1120 IF p_node_type = 'CS' THEN
1121 pay_core_files.write_to_magtape_lob('<'||p_node||'>');
1122 ELSIF p_node_type = 'CE' THEN
1123 pay_core_files.write_to_magtape_lob('</'||p_node||'>');
1124 ELSIF p_node_type = 'D' THEN
1125 /* Handle special charaters in data */
1126 l_data := REPLACE (p_data, '&', '&');
1127 l_data := REPLACE (l_data, '>', '>');
1128 l_data := REPLACE (l_data, '<', '<');
1129 l_data := REPLACE (l_data, '''', ''');
1130 l_data := REPLACE (l_data, '"', '"');
1131 pay_core_files.write_to_magtape_lob('<'||p_node||'>'||l_data||'</'||p_node||'>');
1132 END IF;
1133
1134 hr_utility.trace('Leaving : '||l_proc_name);
1135
1136 END LOAD_XML;
1137
1138 ------------------------------ generate_header_xml -------------------------------
1139 PROCEDURE generate_header_xml is
1140
1141 l_proc_name varchar2(50) := 'pay_1099r_pkg.generate_header_xml';
1142
1143 BEGIN
1144 hr_utility.trace('Entering : '||l_proc_name);
1145
1146 load_xml('CS','US_1099R','');
1147 load_xml('CE','US_1099R','');
1148
1149 hr_utility.trace('Leaving : '||l_proc_name);
1150
1151
1152 END generate_header_xml;
1153
1154 ------------------------------ generate_footer_xml -------------------------------
1155
1156 PROCEDURE generate_footer_xml is
1157 l_proc_name varchar2(50) := 'pay_1099r_pkg.generate_footer_xml';
1158
1159 BEGIN
1160 hr_utility.trace('Entering : '||l_proc_name);
1161
1162 load_xml('CS','US_1099R','');
1163 load_xml('CE','US_1099R','');
1164
1165 hr_utility.trace('Leaving : '||l_proc_name);
1166
1167
1168 END generate_footer_xml;
1169
1170 ------------------------------ get_person_address -------------------------------
1171
1172 PROCEDURE get_person_address(p_fed_aaid in number,
1173 p_effective_date in date,
1174 p_year_end_date in date,
1175 p_addr_line1 out nocopy varchar2,
1176 p_addr_line2 out nocopy varchar2,
1177 p_city_state_zip out nocopy varchar2) IS
1178
1179 CURSOR c_person_id IS
1180 SELECT to_number(serial_number)
1181 FROM pay_assignment_actions
1182 WHERE assignment_action_id = p_fed_aaid;
1183
1184 addr pay_us_get_item_data_pkg.person_name_address;
1185
1186 l_employee_address VARCHAR2(300);
1187 l_address_line1 per_addresses.address_line1%TYPE;
1188 l_address_line2 per_addresses.address_line2%TYPE;
1189 l_address_line3 per_addresses.address_line3%TYPE;
1190 l_town_or_city per_addresses.town_or_city%TYPE;
1191 l_province_or_state per_addresses.region_1%TYPE;
1192 l_region_1 per_addresses.region_1%TYPE;
1193 l_region_2 per_addresses.region_2%TYPE;
1194 l_postal_code per_addresses.postal_code%TYPE;
1195 l_country per_addresses.country%TYPE;
1196 l_country_name varchar2(240);
1197 l_person_id per_people_f.person_id%type;
1198 l_validate varchar2(1) := 'Y';
1199
1200 BEGIN
1201
1202 open c_person_id;
1203 fetch c_person_id into l_person_id;
1204 close c_person_id;
1205
1206 -- p_effective_date is the session_date and
1207 -- p_year_end_date will be the last day of the year for which the report is run.
1208 -- we want to fetch the address as of 31-dec if the session date is less than 31-dec of the year.
1209
1210 addr := pay_us_get_item_data_pkg.GET_PERSON_NAME_ADDRESS(
1211 'REPORT',
1212 l_person_id,
1213 NULL,
1214 p_year_end_date,
1215 p_effective_date,
1216 l_validate,
1217 NULL);
1218
1219 l_address_line1 := addr.addr_line_1;
1220 l_address_line2 := addr.addr_line_2;
1221 l_address_line3 := addr.addr_line_3;
1222 l_town_or_city := addr.city;
1223 l_province_or_state := addr.province_state;
1224 l_region_1 := addr.region_1;
1225 l_region_2 := addr.region_2;
1229
1226 l_postal_code := addr.postal_code;
1227 l_country := addr.country;
1228 l_country_name := addr.country_name;
1230 if l_address_line1 is not null then
1231 p_addr_line1 := rpad(substr(l_address_line1,1,30),31,' ');
1232 end if;
1233
1234 if l_address_line2 is not null then
1235 p_addr_line2 := rpad(substr(l_address_line2,1,30),31,' ');
1236
1237 else
1238 /* address_line2 is null then show addres_line3 if address_line3
1239 is not null else address_line2 is blank.
1240 */
1241 if l_address_line3 is not null then
1242 p_addr_line2 := rpad(substr(l_address_line3,1,30),31,' ');
1243 end if;
1244 end if;
1245
1246 if l_town_or_city is not null then
1247 if l_country = 'CA' then
1248 p_city_state_zip := substr(l_town_or_city,1,23)||' ';
1249 else
1250 p_city_state_zip := substr(l_town_or_city,1,29)||' ';
1251 end if;
1252 end if;
1253
1254 if l_province_or_state is not null then
1255 p_city_state_zip := p_city_state_zip||substr(l_province_or_state,1,2)||' ';
1256 end if;
1257
1258 if l_postal_code is not null then
1259 p_city_state_zip := p_city_state_zip||substr(l_postal_code,1,10);
1260 end if;
1261
1262 if l_country = 'CA' then
1263 p_city_state_zip := p_city_state_zip||' '||substr(l_country_name,1,6);
1264 end if;
1265
1266 End get_person_address;
1267
1268 ------------------------------ gen_state_tax_details -------------------------------
1269
1270 PROCEDURE gen_state_tax_details (p_asg_actid in number,
1271 pactid in number) is
1272
1273 CURSOR c_state_tax IS
1274 SELECT tax_unit_id st_tax_unit_id,
1275 assignment_id st_assign_id,
1276 decode(state_abbrev, 'NJ', state_abbrev||nvl(replace(replace(state_ein,'-'),'/'),'NO STATE EIN'), state_abbrev||' '||nvl(state_ein,'NO STATE EIN')) state_ein,
1277 w2_box_17 sit_subject,
1278 w2_box_18 sit_withheld
1279 FROM
1280 pay_us_w2_state_v pws
1281 WHERE state_abbrev NOT IN ( 'AK','FL', 'NH','NV','SD','TN','TX','WA','WY')
1282 AND (w2_box_17 <> 0 OR w2_box_18 <> 0)
1283 AND assignment_action_id = p_asg_actid
1284 AND payroll_action_id = pactid
1285 ORDER BY state_abbrev;
1286
1287 l_count number;
1288
1289 BEGIN
1290
1291 l_count := 0;
1292
1293 For i in c_state_tax loop
1294
1295 if l_count = 0 then
1296 load_xml('D','SIT_WH',i.sit_withheld);
1297 load_xml('D','STATE_EIN',i.state_ein);
1298 load_xml('D','STATE_DIST',i.sit_subject);
1299 else
1300 load_xml('D','SIT_WH1',i.sit_withheld);
1301 load_xml('D','STATE_EIN1',i.state_ein);
1302 load_xml('D','STATE_DIST1',i.sit_subject);
1303 end if;
1304
1305 l_count := l_count + 1;
1306 if l_count >= 2 then
1307 Exit;
1308 end if;
1309
1310 End Loop;
1311
1312 END gen_state_tax_details;
1313
1314 ------------------------------ gen_loc_tax_details -------------------------------
1315
1316 PROCEDURE gen_loc_tax_details (p_asg_actid in number,
1317 pactid in number) is
1318
1319 CURSOR c_locality_tax IS
1320 SELECT locality_name,
1321 assignment_id lit_assign_id,
1322 tax_unit_id lit_tax_unit_id,
1323 w2_box_20 lit_subject,
1324 w2_box_21 lit_withheld
1325 FROM
1326 pay_us_w2_locality_v
1327 WHERE
1328 w2_box_21 <> 0
1329 AND assignment_action_id = p_asg_actid
1330 AND payroll_action_id = pactid;
1331
1332 l_count number;
1333
1334 BEGIN
1335
1336 l_count := 0;
1337
1338 For i in c_locality_tax loop
1339 if l_count = 0 then
1340 load_xml('D','LIT_WH',i.lit_withheld);
1341 load_xml('D','NAME_LOCAL',i.locality_name);
1342 load_xml('D','LOCAL_DIST',i.lit_subject);
1343 else
1344 load_xml('D','LIT_WH1',i.lit_withheld);
1345 load_xml('D','NAME_LOCAL1',i.locality_name);
1346 load_xml('D','LOCAL_DIST1',i.lit_subject);
1347 end if;
1348
1349 l_count := l_count + 1;
1350 if l_count >= 2 then
1351 Exit;
1352 end if;
1353
1354 End Loop;
1355
1356 END gen_loc_tax_details;
1357
1358 ------------------------------ generate_detail_xml -------------------------------
1359 PROCEDURE generate_detail_xml IS
1360
1361 CURSOR csr_get_details (p_asg_actid in number) IS
1362 SELECT
1363 to_number(pay_1099R_pkg.get_parameter('YREND_PACTID',ppa.legislative_parameters)) yrend_pactid,
1364 to_number(paa.serial_number) fed_aaid,
1365 v1099r.year year,
1366 v1099r.gross_1099r gross_1099r,
1367 v1099r.wages_tips_compensation fit_subject,
1368 v1099r.taxable_amt_1099r taxable_amt_1099r,
1369 v1099r.fed_it_withheld fit_withheld,
1370 v1099r.ssn ssn,
1371 v1099r.first_name ||' '||v1099r.middle_name||' ' ||v1099r.pre_name_adjunt ||' '||v1099r.last_name employee_name,
1372 v1099r.federal_ein federal_ein,
1373 v1099r.tax_unit_name tax_unit_name,
1374 rpad(substr(hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR1'),1,30),31,' ')
1375 ||decode( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR2'),null,null,
1379 substr( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'CITY') ,1,29)||', '||
1376 rpad(substr( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR2') ,1,30),31,' '))
1377 ||decode( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR3') ,null,null,
1378 rpad(substr( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR3') ,1,30),31,' ')) tax_unit_address,
1380 hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'STATE') ||' '||
1381 hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ZIP') ct_st_zp,
1382 decode(v1099r.taxable_amount_unknown,'Y','X',null) taxable_amt_unknown,
1383 decode(v1099r.total_distributions,'Y','X',null) total_distributions,
1384 v1099r.employee_distribution_percent ee_dstr_pr,
1385 v1099r.total_distribution_percent tot_dstr_pr,
1386 v1099r.capital_gain,
1387 v1099r.ee_contributions_or_premiums ee_cont_prem,
1388 v1099r.unrealized_net_er_security un_net_er,
1389 v1099r.other_ee_annuity_contract_amt ee_anuity,
1390 v1099r.total_ee_contributions tot_ee_contr,
1391 nvl(hr_us_w2_rep.get_per_item(v1099r.assignment_action_id, 'A_DISTRIBUTION_CODE_FOR_1099R'),'7') ee_distribution_code,
1392 v1099r.defferal_year defferal_year
1393 from
1394 PAY_ASSIGNMENT_ACTIONS PAA, --PYUGEN
1395 PAY_PAYROLL_ACTIONS PPA, --PYUGEN
1396 PAY_US_WAGES_1099r_v v1099r
1397 WHERE
1398 paa.assignment_action_id = p_asg_actid
1399 AND ppa.payroll_action_id = paa.payroll_action_id
1400 AND paa.serial_number = v1099r.assignment_action_id;
1401
1402
1403 CURSOR c_parameters (asg_actid in NUMBER) IS
1404 SELECT ppa.legislative_parameters,
1405 fnd_date.canonical_to_date(pay_1099r_pkg.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters)),
1406 pay_1099r_pkg.get_parameter('PRINT_INSTRUCTION',ppa.legislative_parameters)
1407 FROM pay_payroll_actions ppa,
1408 pay_assignment_actions paa
1409 WHERE paa.assignment_action_id = asg_actid
1410 and ppa.payroll_action_id = paa.payroll_action_id;
1411
1412 l_assignment_action_id number;
1413
1414 l_yrend_pactid number;
1415 l_fed_aaid number;
1416 l_year number;
1417 l_gross_1099r number;
1418 l_fit_subject number;
1419 l_taxable_amt_1099r number;
1420 l_fit_withheld number;
1421 l_ssn per_all_people_f.national_identifier%TYPE;
1422 l_employee_name per_all_people_f.full_name%TYPE;
1423 l_federal_ein varchar2(50);
1424 l_tax_unit_name varchar2(100);
1425 l_tax_unit_address varchar2(100);
1426 l_ct_st_zp varchar2(100);
1427 l_taxable_amt_unknown varchar2(3);
1428 l_total_distributions varchar2(3);
1429 l_ee_dstr_pr number;
1430 l_tot_dstr_pr number;
1431 l_capital_gain number;
1432 l_ee_cont_prem number;
1433 l_un_net_er number;
1434 l_ee_anuity number;
1435 l_tot_ee_contr number;
1436 l_ee_distribution_code varchar2(50);
1437 l_addr_line1 per_addresses.address_line1%TYPE;
1438 l_addr_line2 per_addresses.address_line2%TYPE;
1439 l_city_state_zip varchar2(100);
1440 l_eff_date date;
1441 l_leg_param pay_payroll_actions.legislative_parameters%TYPE;
1442 l_st_tax_unit_id number;
1443 l_st_assign_id number;
1444 l_state_ein varchar2(50);
1445 l_sit_subject number;
1446 l_sit_withheld number;
1447 l_locality_name varchar2(100);
1448 l_lit_assign_id number;
1449 l_lit_tax_unit_id number;
1450 l_lit_subject number;
1451 l_lit_withheld number;
1452 l_defferal_year varchar2(100);
1453
1454 BEGIN
1455
1456 l_assignment_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
1457
1458 open c_parameters(l_assignment_action_id);
1459 fetch c_parameters into
1460 l_leg_param,
1461 l_eff_date,
1462 g_print_instr;
1463 close c_parameters;
1464
1465 IF (g_print_instr IS NULL) OR (g_print_instr = '') THEN
1466 g_print_instr := 'Y';
1467 END IF;
1468
1469 open csr_get_details(l_assignment_action_id);
1470 fetch csr_get_details into
1471 l_yrend_pactid,
1472 l_fed_aaid,
1473 l_year,
1474 l_gross_1099r,
1475 l_fit_subject,
1476 l_taxable_amt_1099r,
1477 l_fit_withheld,
1478 l_ssn,
1479 l_employee_name,
1480 l_federal_ein,
1481 l_tax_unit_name,
1482 l_tax_unit_address,
1483 l_ct_st_zp,
1484 l_taxable_amt_unknown,
1485 l_total_distributions,
1486 l_ee_dstr_pr,
1487 l_tot_dstr_pr,
1488 l_capital_gain,
1489 l_ee_cont_prem,
1490 l_un_net_er,
1491 l_ee_anuity,
1492 l_tot_ee_contr,
1493 l_ee_distribution_code,
1494 l_defferal_year;
1495 close csr_get_details;
1496
1497 -- bug 5979491
1498 if l_taxable_amt_unknown = 'X'
1499 then
1500 l_taxable_amt_1099r := '';
1501 end if;
1502 -- end bug 5979491
1503
1504 /* Get Person Address*/
1505 get_person_address(l_fed_aaid,
1506 l_eff_date,
1507 fnd_date.canonical_to_date(to_char(l_year)||'/12/31'),
1508 l_addr_line1,
1509 l_addr_line2,
1510 l_city_state_zip);
1511
1512 load_xml('CS','G_EMPLOYEE','');
1513 load_xml('D','GROSS_DIST',l_gross_1099r);
1514 load_xml('D','TAX_AMT',l_taxable_amt_1099r);
1515 load_xml('D','TAX_AMT_ND', l_taxable_amt_unknown);
1516 load_xml('D','TOT_DIST', l_total_distributions);
1517 load_xml('D','PAYER_NAME',l_tax_unit_name);
1518 load_xml('D','PAYER_ADDRESS',l_tax_unit_address);
1519 load_xml('D','PAYER_ADDR_CT_ST_ZP',l_ct_st_zp);
1520 load_xml('D','PAYER_FEIN',l_federal_ein);
1521 load_xml('D','EMP_SSN',l_ssn);
1522 load_xml('D','EMP_NAME',l_employee_name);
1523 load_xml('D','EMP_ADDR_LN1',l_addr_line1);
1524 load_xml('D','EMP_ADDR_LN2',l_addr_line2);
1525 load_xml('D','EMP_ADDR_CT_ST_ZP',l_city_state_zip);
1526 load_xml('D','CAP_GAIN',l_capital_gain);
1527 load_xml('D','FIT_WH',l_fit_withheld);
1528 load_xml('D','EMP_CONTR_INS',l_ee_cont_prem);
1529 load_xml('D','NET_APPR_ER_SEC',l_un_net_er);
1530 load_xml('D','DIST_CODE',l_ee_distribution_code);
1531 load_xml('D','IRA_SEP_SIMP',' ');
1532 load_xml('D','OTHER',l_ee_anuity);
1533 load_xml('D','PERCENT',l_ee_dstr_pr);
1534 load_xml('D','PERC_TOTL_DIST',l_tot_dstr_pr);
1535 load_xml('D','TOT_EE_CONTR',l_tot_ee_contr);
1536 load_xml('D','FIRST_YR_ROTH',l_defferal_year);
1537
1538 gen_state_tax_details(l_fed_aaid,l_yrend_pactid);
1539 gen_loc_tax_details(l_fed_aaid,l_yrend_pactid);
1540 load_xml('D','PRINT_INSTRUCTION',g_print_instr);
1541
1542 load_xml('CE','G_EMPLOYEE','');
1543
1544 END generate_detail_xml;
1545
1546 --begin
1547
1548 --hr_utility.trace_on(null, 'pyus109r');
1549
1550 end pay_1099R_pkg;