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