[Home] [Help]
PACKAGE BODY: APPS.PAY_US_1099R_UDFS
Source
1 package body pay_us_1099R_udfs AS
2 /* $Header: py99udfs.pkb 120.2 2010/09/17 09:00:49 nkjaladi ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1996 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : pay_us_1099R_udfs
11 Filename : py99udfs.pkb
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 3/16/97 HEKIM 40.0 Created.
17 23/05/97 MFENDER 40.2 Removed show_errors call.
18 10/14/98 AHANDA 40.3 Changed function : state_1099R_specs
19 11/19/98 AHANDA 40.4/ Changed function : state_1099R_specs
20 110.2 to put data in 3 fields - Special Entry,
21 SIT and LIT
22 20/04/99 scgrant 115.1 Multi-radix changes.
23 13/11/02 djoshi 115.7 Added the New Function get_1099R_values
24 13/11/02 djoshi 115.7 Changed to add all the values for K rec
25 02/12/02 djoshi 119.10 Added changes to have value assignmed
26 12/20/02 djoshi 115.11 Added new function ny_local
27 10/30/03 jgoswami 115.12 Added function GET_1099R_ITEM_DATA,
28 format_pub1220_address
29 11/06/03 jgoswami 115.13 Added Distribution code to get_1099R_values
30 added procedure format_1099r_wv_address
31 13-NOV-2003 jgoswami 115.14 3241256 Added GET_1099R_TRANSMITTER_VALUE
32 17-NOV-2003 sodhingr 115.15 removed the to_char from hr_utility.trace
33 09-DEC-2003 jgoswami 115.16 3308537 modified format_1099r_wv_address package
34 13-DEC-2003 jgoswami 115.17 3317434 modified GET_1099R_TRANSMITTER_VALU E to
35 return '0' for other states and federal
36 except 'CT'.
37 16-DEC-2003 jgoswami 115.18 3323062 Modified the lpad to rpad for State Abbrev
38 for WV address function.
39 17-Sep-2010 nkjaladi 115.20 10084549 Modified the function GET_1099R_ITEM_DATA
40 to return person_id in out parameter
41 sp_out_10 if the format type is 'PUB1220'
42 irrespective of any value of report
43 qualifier
44 */
45
46 -------------------------------------------------------------------------
47 --Name: init_global_1099R_tables
48 --Purpose: Initializes pay_us_1099R_udfs global tables for K-record totals
49 -------------------------------------------------------------------------
50 FUNCTION init_global_1099R_tables(p_dummy in VARCHAR2) RETURN VARCHAR2 IS
51 l_size number:= 55;
52 BEGIN
53 FOR l_count IN 1..l_size LOOP
54 pay_us_1099R_udfs.gt_combined_filer_state_payees(l_count) := 0;
55 pay_us_1099R_udfs.gt_CFS_control_total_1(l_count) := 0;
56 pay_us_1099R_udfs.gt_CFS_control_total_2(l_count) := 0;
57 pay_us_1099R_udfs.gt_CFS_control_total_3(l_count) := 0;
58 pay_us_1099R_udfs.gt_CFS_control_total_4(l_count) := 0;
59 pay_us_1099R_udfs.gt_CFS_control_total_5(l_count) := 0;
60 pay_us_1099R_udfs.gt_CFS_control_total_6(l_count) := 0;
61 pay_us_1099R_udfs.gt_CFS_control_total_8(l_count) := 0;
62 pay_us_1099R_udfs.gt_CFS_control_total_9(l_count) := 0;
63 pay_us_1099R_udfs.gt_CFS_SIT_total(l_count) := 0;
64 pay_us_1099R_udfs.gt_CFS_LIT_total(l_count) := 0;
65 END LOOP;
66 return 1;
67 END init_global_1099R_tables;
68
69 -------------------------------------------------------------------------
70 --Name: state_1099R_specs
71 --Purpose: updates global tables , and returns formula output
72 -- as specified by state
73 -------------------------------------------------------------------------
74 FUNCTION state_1099R_specs( p_state in VARCHAR2,
75 p_amount_1 in NUMBER,
76 p_amount_2 in NUMBER,
77 p_amount_3 in NUMBER,
78 p_amount_4 in NUMBER,
79 p_amount_5 in NUMBER,
80 p_amount_6 in NUMBER,
81 p_amount_8 in NUMBER,
82 p_amount_9 in NUMBER,
83 p_SIT in NUMBER,
84 p_LIT in NUMBER,
85 p_SEIN in VARCHAR2,
86 p_state_taxable in NUMBER ) RETURN VARCHAR2 IS
87 --
88 l_index number;
89 l_f34 VARCHAR2(84) :=lpad(' ',84);
90 l_SIT VARCHAR2(20) ;
91 l_LIT VARCHAR2(20) ;
92 l_SEIN VARCHAR2(20) ;
93 hyphen_position number;
94 --
95 BEGIN
96 l_SIT := to_char(p_SIT);
97 l_LIT := to_char(p_LIT);
98
99 hr_utility.trace('SIT : ' || l_sit);
100 hr_utility.trace('LIT : ' || l_lit);
101
102 --
103 ----------------------------------------------------------------
104 --increment table
105 ----------------------------------------------------------------
106 l_index := fnd_number.canonical_to_number(pay_us_1099R_udfs.get_1099R_state_code(p_state));
107 pay_us_1099R_udfs.gt_combined_filer_state_payees(l_index) :=
108 pay_us_1099R_udfs.gt_combined_filer_state_payees(l_index) + 1;
109 --
110 pay_us_1099R_udfs.gt_CFS_control_total_1(l_index) :=
111 pay_us_1099R_udfs.gt_CFS_control_total_1(l_index) + p_amount_1;
112 --
113
114 pay_us_1099R_udfs.gt_CFS_control_total_2(l_index) :=
115 pay_us_1099R_udfs.gt_CFS_control_total_2 (l_index)+ p_amount_2;
116 --
117 pay_us_1099R_udfs.gt_CFS_control_total_3(l_index) :=
118 pay_us_1099R_udfs.gt_CFS_control_total_3 (l_index)+ p_amount_3;
119 --
120 pay_us_1099R_udfs.gt_CFS_control_total_4(l_index) :=
121 pay_us_1099R_udfs.gt_CFS_control_total_4 (l_index)+ p_amount_4;
122 --
123 pay_us_1099R_udfs.gt_CFS_control_total_5(l_index) :=
124 pay_us_1099R_udfs.gt_CFS_control_total_5 (l_index)+ p_amount_5;
125 --
126 pay_us_1099R_udfs.gt_CFS_control_total_6(l_index) :=
127 pay_us_1099R_udfs.gt_CFS_control_total_6 (l_index)+ p_amount_6;
128 --
129 pay_us_1099R_udfs.gt_CFS_control_total_8(l_index) :=
130 pay_us_1099R_udfs.gt_CFS_control_total_8 (l_index)+ p_amount_8;
131 --
132 pay_us_1099R_udfs.gt_CFS_control_total_9(l_index) :=
133 pay_us_1099R_udfs.gt_CFS_control_total_9 (l_index)+ p_amount_9;
134 --
135 --
136 pay_us_1099R_udfs.gt_CFS_SIT_total(l_index) :=
137 pay_us_1099R_udfs.gt_CFS_SIT_total (l_index)+ p_SIT;
138 --
139 pay_us_1099R_udfs.gt_CFS_LIT_total(l_index) :=
140 pay_us_1099R_udfs.gt_CFS_LIT_total(l_index) + p_LIT;
141
142 --------------------------------------------------------------------------
143 --specify field
144 --------------------------------------------------------------------------
145 -- Pos:663 Len: 60 Desc: Special data entries
146 -- Pos:723 Len: 12 Desc: State Income Tax Withheld
147 -- Pos:735 Len: 12 Desc: Local Income Tax Withheld
148 --
149 --CA,MN,WI,AR ------------------------------------------------------------
150 IF p_state IN ('MN','WI','CA','AR') THEN
151 l_f34 := lpad(' ',60) ||
152 lpad(substr(l_SIT,1,12),12,'0') ||
153 lpad('0', 12, '0');
154
155 --MAINE------------------------------------------------------------------
156 ELSIF p_state = 'ME' THEN
157 /* Special data entries = 9 bytes State Taxable Income */
158 l_f34 := lpad(lpad(substr(to_char(p_state_taxable),1,9), 9, '0'),60) ||
159 lpad(substr(l_SIT,1,12),12,'0') ||
160 lpad('0', 12, '0');
161
162 --IDAHO------------------------------------------------------------------
163 ELSIF p_state = 'ID' THEN
164 /* Special data entries =
165 10 bytes Idaho Withholding Account number: strip number before hyphen*/
166
167 hyphen_position := instr( p_SEIN,'-');
168 if (hyphen_position <> 0) then
169 l_SEIN := lpad(replace(substr(p_SEIN, 1,
170 hyphen_position - 1),' '), 10,'0');
171 else
172 l_SEIN := lpad(substr(p_SEIN,1,10),10,'0');
173 end if;
174 l_f34 := lpad(l_SEIN, 60) ||
175 lpad(substr(l_SIT,1,12),12,'0') ||
176 lpad('0',12,'0');
177
178 -------------------------------------------------------------------------
179 --Following Federal specs exactly: AZ,IN,KS,MS,MO,ND,MT,IA,NJ,SC
180 ELSE
181 l_f34 := lpad(' ',60) ||
182 lpad(substr(l_SIT,1,12),12,'0') ||
183 lpad(substr(l_LIT,1,12),12,'0') ;
184 END IF;
185 --
186 hr_utility.trace('Value of l34 is ' || l_f34);
187 return l_f34;
188 --
189 END state_1099R_specs;
190 --
191 -------------------------------------------------------------------------
192 --Name: get_1099R_state_payee_count
193 --Purpose: returns the number of payees processed currently for that state
194 -------------------------------------------------------------------------
195 FUNCTION get_1099R_state_payee_count(p_state in VARCHAR2)
196 RETURN NUMBER IS
197 l_index number;
198 BEGIN
199 l_index := pay_us_1099R_udfs.get_1099R_state_code(p_state);
200 IF l_index IS NULL THEN
201 return 0;
202 ELSE return pay_us_1099R_udfs.gt_combined_filer_state_payees(l_index);
203 END IF;
204 END get_1099R_state_payee_count;
205 --
206 -------------------------------------------------------------------------
207 --Name: get_1099R_state_total
208 --Purpose: returns total amounts from global tables according to p_type
209 -------------------------------------------------------------------------
210 FUNCTION get_1099R_state_total(p_state in VARCHAR2,
211 p_type in VARCHAR2 ) RETURN VARCHAR2 IS
212
213 l_index number; -- index into global tables
214 BEGIN
215 l_index := pay_us_1099R_udfs.get_1099R_state_code(p_state);
216 IF l_index IS NULL THEN
217 return 0;
218 END IF;
219 IF p_type = 'amount_1' THEN
220 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_control_total_1(l_index));
221 ELSIF p_type = 'amount_2' THEN
222 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_control_total_2(l_index));
223 ELSIF p_type = 'amount_3' THEN
224 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_control_total_3(l_index));
225 ELSIF p_type = 'amount_4' THEN
226 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_control_total_4(l_index));
227 ELSIF p_type = 'amount_5' THEN
228 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_control_total_5(l_index));
229 ELSIF p_type = 'amount_6' THEN
230 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_control_total_6(l_index));
231 ELSIF p_type = 'amount_8' THEN
232 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_control_total_8(l_index));
233 ELSIF p_type = 'amount_9' THEN
234 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_control_total_9(l_index));
235 ELSIF p_type = 'SIT' THEN
236 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_SIT_total(l_index));
237 ELSIF p_type = 'LIT' THEN
238 return fnd_number.number_to_canonical(pay_us_1099R_udfs.gt_CFS_LIT_total(l_index));
239 END IF;
240
241 END get_1099R_state_total;
242 --
243 -------------------------------------------------------------------------
244 --Name: get_1099R_name_control
245 --Purpose: Returns first four alphanumeric characters of p_name
246 -------------------------------------------------------------------------
247 FUNCTION get_1099R_name_control (p_name in VARCHAR2)
248 RETURN VARCHAR2 IS
249 l_ascii varchar2(80);
250 l_name_code varchar2(10) := NULL;
251 l_index NUMBER := 0;
252 l_num NUMBER := 0;
253 BEGIN
254
255 WHILE (length(p_name)>l_index and l_num< 4) LOOP
256 l_index := l_index + 1;
257 l_ascii := ASCII(substr(p_name,l_index,1));
258 IF (l_ascii >= ASCII('A') and l_ascii <= ASCII('Z')) or
259 (l_ascii >= ASCII('0') and l_ascii <= ASCII('9')) or
260 (l_ascii = ASCII('-')) or (l_ascii = ASCII('&')) THEN
261 l_name_code := l_name_code || substr(p_name,l_index,1);
262 l_num := l_num + 1;
263 end if;
264 end loop;
265 return l_name_code;
266
267 END get_1099R_name_control;
268 --
269 -------------------------------------------------------------------------
270 --Name: get_1099R_NE_SEIN
271 --Purpose: Returns Nebraska SEIN, Right justified, zero filled
272 -- without any blanks, hypens, alpha characters,all 9's or 0's
273 -------------------------------------------------------------------------
274 FUNCTION get_1099R_NE_SEIN (p_SEIN in VARCHAR2)
275 RETURN VARCHAR2 IS
276 l_ascii varchar2(80);
277 l_NE_SEIN varchar2(80) := NULL;
278 l_result varchar2(7) := NULL;
279 l_index NUMBER := 0;
280 l_num NUMBER := 0;
281 l_nine NUMBER:=0;
282 l_char CHAR;
283
284 BEGIN
285 l_NE_SEIN := upper(p_SEIN);
286 WHILE (length(l_NE_SEIN)>l_index and l_num< 7) LOOP
287 l_index := l_index + 1;
288 l_char := substr(l_NE_SEIN,l_index,1);
289 l_ascii := ASCII(l_char);
290
291 --
292 IF (l_ascii >= ASCII('A') and l_ascii <= ASCII('Z')) or
293 (l_ascii = ASCII('-')) or (l_ascii = ASCII(' ')) THEN
294 hr_utility.trace ('Removing ' || l_char ||
295 'from NE-SEIN.');
296 ELSE
297 l_result := l_result || l_char;
298 l_num := l_num + 1;
299 if l_char = '9' then
300 l_nine := l_nine + 1;
301 end if;
302 --
303 END IF;
304 end loop;
305
306 --right justify and zero fill
307 --
308 if l_nine = l_num then --all nines
309 l_result := '0';
310 end if;
311 --
312 l_result := rpad(l_result, 7,'0');
313 return l_result;
314 --
315 END get_1099R_NE_SEIN;
316 -------------------------------------------------------------------------
317 --Name: combined_filer_1099R_state
318 --Purpose: returns 'Y' if p_state participates in combined filing,
319 -- otherwise 'N'
320 -------------------------------------------------------------------------
321 FUNCTION combined_filer_1099R_state (p_state in VARCHAR2) RETURN VARCHAR2 IS
322 l_flag VARCHAR(2);
323 BEGIN
324 SELECT 'Y' into l_flag from hr_lookups
325 where lookup_type = '1099R_US_COMBINED_FILER_STATES'
326 and lookup_code = p_state;
327 return l_flag;
328
329 EXCEPTION
330 WHEN NO_DATA_FOUND THEN
331 return 'N';
332 --
333 END combined_filer_1099R_state;
334 --
335 -------------------------------------------------------------------------
336 --Name: get_1099R_state_code
337 --Purpose: Returns the corresponding combined filer code for p_state
338 -------------------------------------------------------------------------
339 FUNCTION get_1099R_state_code (p_state in VARCHAR2) RETURN VARCHAR2 IS
340 l_code VARCHAR(80):=NULL;
341 --
342 BEGIN
343 SELECT MEANING into l_code from hr_lookups
344 where lookup_type = '1099R_US_COMBINED_FILER_STATES'
345 and lookup_code = p_state;
346 return l_code;
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 return l_code;
351 --
352 END get_1099R_state_code;
353
354
355 /* Function Name : Get_Territory_Values
356 Purpose : Purpose of this function is to fetch the balances as well
357 as the data related to territory. */
358
359 FUNCTION GET_1099R_VALUE(
360 p_assignment_action_id number, -- context
361 p_tax_unit_id number,-- context
362 sp_out_1 OUT nocopy varchar2,
363 sp_out_2 OUT nocopy varchar2,
364 sp_out_3 OUT nocopy varchar2,
365 sp_out_4 OUT nocopy varchar2,
366 sp_out_5 OUT nocopy varchar2,
367 sp_out_6 OUT nocopy varchar2,
368 sp_out_7 OUT nocopy varchar2,
369 sp_out_8 OUT nocopy varchar2,
370 sp_out_9 OUT nocopy varchar2,
371 sp_out_10 OUT nocopy varchar2)
372 RETURN VARCHAR2 IS
373
374 l_entity_id ff_database_items.user_entity_id%type;
375 l_archived_value ff_archive_items.value%type;
376 l_message varchar2(1000);
377 l_main_return varchar2(100);
378
379 TYPE dbi_columns IS RECORD(
380 p_user_name ff_database_items.user_name%type,
381 p_archived_value ff_archive_items.value%type);
382
383 dbi_rec dbi_columns;
384
385 TYPE dbi_infm IS TABLE OF dbi_rec%TYPE
386 INDEX BY BINARY_INTEGER;
387
388 dbi_table dbi_infm;
389
390 CURSOR get_user_entity_id
391 (c_user_name ff_database_items.user_name%type) IS
392 SELECT fdi.user_entity_id
393 FROM ff_database_items fdi,
394 ff_user_entities fue
395 WHERE fue.legislation_code = 'US'
396 AND fue.user_entity_id = fdi.user_entity_id
397 AND fdi.user_name = c_user_name;
398
399 CURSOR get_archived_values(
400 c_user_entity_id ff_database_items.user_entity_id%type,
401 c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
402 c_tax_unit_id hr_organization_units.organization_id%type)
403 IS
404 SELECT target.value
405 FROM ff_archive_item_contexts con2,
406 ff_contexts fc2,
407 ff_archive_items target
408 WHERE target.user_entity_id = c_user_entity_id
409 AND target.context1 = to_char(c_assignment_action_id)
410 /* context assignment action id */
411 AND fc2.context_name = 'TAX_UNIT_ID'
412 and con2.archive_item_id = target.archive_item_id
413 and con2.context_id = fc2.context_id
414 and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id);
415 /*context of tax_unit_id */
416
417 CURSOR get_archived_values_assignment(
418 c_user_entity_id ff_database_items.user_entity_id%type,
419 c_assignment_action_id pay_assignment_actions.assignment_action_id%type
420 )
421 IS
422 SELECT target.value
423 FROM
424 ff_archive_items target
425 WHERE target.user_entity_id = c_user_entity_id
426 AND target.context1 = to_char(c_assignment_action_id);
427 /* context assignment action id */
428
429
430 BEGIN
431 -- hr_utility.trace_on(NULL,'oracle');
432 /* call to funciton to get the value of 1099R Balanaces */
433 hr_utility.trace('Calling for 1099R balnaces for following value');
434
435 hr_utility.trace('Assignment_action_id = '||to_char(p_assignment_action_id));
436 hr_utility.trace('Tax_unit_id = '||to_char(p_tax_unit_id));
437
438 dbi_table(1).p_user_name := 'A_CAPITAL_GAIN_PER_GRE_YTD';
439 dbi_table(2).p_user_name := 'A_OTHER_EE_ANNUITY_CONTRACT_AMT_PER_GRE_YTD';
440 dbi_table(3).p_user_name := 'A_TOTAL_EE_CONTRIBUTIONS_PER_GRE_YTD';
441 dbi_table(4).p_user_name := 'A_UNREALIZED_NET_ER_SEC_APPREC_PER_GRE_YTD';
442 dbi_table(5).p_user_name := 'A_EE_CONTRIBUTIONS_OR_PREMIUMS_PER_GRE_YTD';
443
444 dbi_table(6).p_user_name := 'A_TAXABLE_AMOUNT_UNKNOWN';
445 dbi_table(7).p_user_name := 'A_TOTAL_DISTRIBUTIONS';
446 dbi_table(8).p_user_name := 'A_EMPLOYEE_DISTRIBUTION_PERCENT';
447 dbi_table(9).p_user_name := 'A_TOTAL_DISTRIBUTION_PERCENT';
448 dbi_table(10).p_user_name := 'A_DISTRIBUTION_CODE_FOR_1099R';
449
450 hr_utility.trace('Getting the user_entity id');
451
452 FOR i in dbi_table.first .. dbi_table.last loop
453
454 OPEN get_user_entity_id(dbi_table(i).p_user_name);
455 FETCH get_user_entity_id INTO l_entity_id;
456
457 IF get_user_entity_id%NOTFOUND THEN
458
459 l_message:='Error: User_Entity_Id not found for user name '
460 ||dbi_table(i).p_user_name;
461
462 dbi_table(i).p_archived_value:='0';
463
464 ELSE
465
466 hr_utility.trace('get_user_entity_id = '||to_char(l_entity_id));
467 hr_utility.trace('p_assignment_action_id = '||to_char(p_assignment_action_id));
468 hr_utility.trace('p_tax_unit_id = '||to_char(p_tax_unit_id));
469
470 IF substr(dbi_table(i).p_user_name,-11) = 'PER_GRE_YTD' THEN
471
472 OPEN get_archived_values(l_entity_id,
473 p_assignment_action_id,
474 p_tax_unit_id);
475 FETCH get_archived_values INTO l_archived_value;
476
477 IF get_archived_values%NOTFOUND THEN
478 dbi_table(i).p_archived_value:='0';
479 hr_utility.trace('Archived_values not found for user name ' ||dbi_table(i).p_user_name);
480 ELSIF get_archived_values%FOUND THEN
481 dbi_table(i).p_archived_value := l_archived_value;
482 hr_utility.trace('Archived_values found for user name ' ||dbi_table(i).p_user_name);
483 hr_utility.trace('Archived_value before neg check= '||l_archived_value);
484
485 END IF;
486 CLOSE get_archived_values;
487 ELSE
488 /* To get value of non Per gre YTD */
489
490 OPEN get_archived_values_assignment(l_entity_id,
491 p_assignment_action_id);
492 FETCH get_archived_values_assignment INTO l_archived_value;
493
494 IF get_archived_values_assignment%NOTFOUND THEN
495 dbi_table(i).p_archived_value:='0';
496 hr_utility.trace('Archived_values not found for user name ' ||dbi_table(i).p_user_name);
497 ELSE
498 dbi_table(i).p_archived_value := l_archived_value;
499 hr_utility.trace('Archived_values found for user name ' ||dbi_table(i).p_user_name);
500 hr_utility.trace('Archived_value before neg check= '||l_archived_value);
501 END IF; /* get_archive_value_assignment not_found */
502 CLOSE get_archived_values_assignment;
503 END IF; /* PER_GRE_YTD */
504 END IF; /* USER_ENTITY_FOUND */
505
506 CLOSE get_user_entity_id;
507
508 end loop;
509
510 sp_out_1 :=nvl(dbi_table(1).p_archived_value,'0');
511 sp_out_2 :=nvl(dbi_table(2).p_archived_value,'0');
512 sp_out_3 :=nvl(dbi_table(3).p_archived_value,'0');
513 sp_out_4 :=nvl(dbi_table(4).p_archived_value,'0');
514 sp_out_5 :=nvl(dbi_table(5).p_archived_value,'0');
515
516 IF dbi_table(6).p_archived_value = 'Y'
517 THEN
518 sp_out_6 :='1';
519 ELSE
520 sp_out_6 :=' ';
521 END IF;
522 IF dbi_table(7).p_archived_value = 'Y'
523 THEN
524 sp_out_7 :='1';
525 ELSE
526 sp_out_7 :=' ';
527 END IF;
528
529 IF dbi_table(8).p_archived_value = '100' THEN
530 sp_out_8 := ' ';
531 ELSIF nvl(dbi_table(8).p_archived_value,'-0') = '-0' THEN
532 sp_out_8 := ' ';
533 ELSE
534 sp_out_8 := lpad(dbi_table(8).p_archived_value,2,'0');
535 END IF;
536 sp_out_9 := nvl(dbi_table(9).p_archived_value,'0');
537 sp_out_10 := nvl(dbi_table(10).p_archived_value,'7');
538 --sp_out_10:= ' ';
539 hr_utility.trace('sp_out_10 = '||sp_out_10);
540
541 l_main_return := ' ';
542 return l_main_return;
543
544 END Get_1099R_value;
545
546 FUNCTION GET_1099R_NY_VALUE(
547 p_assignment_action_id number, -- context
548 p_tax_unit_id number,-- context
549 p_state in varchar2)
550 RETURN VARCHAR2 IS
551 CURSOR c_sum_of_city_withheld(c_assignment_action_id number,c_tax_unit_id number) IS
552 select nvl(sum(target.value),0)
553 from
554 ff_archive_item_contexts con3,
555 ff_archive_item_contexts con2,
556 ff_contexts fc3,
557 ff_contexts fc2,
558 ff_archive_items target,
559 ff_database_items fdi
560 where fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
561 and target.user_entity_id = fdi.user_entity_id
562 and target.context1 = to_char(c_assignment_action_id)
563 /* context assignment action id */
564 and fc2.context_name = 'TAX_UNIT_ID'
565 and con2.archive_item_id = target.archive_item_id
566 and con2.context_id = fc2.context_id
567 and ltrim(rtrim(con2.context)) = to_char(c_tax_unit_id)
568 /* 2nd context of tax_unit_id */
569 and fc3.context_name = 'JURISDICTION_CODE'
570 and con3.archive_item_id = target.archive_item_id
571 and con3.context_id = fc3.context_id
572 and ltrim(rtrim(con3.context)) in
573 ( '33-005-2010', '33-047-2010',
574 '33-061-2010' ,'33-081-2010' ,
575 '33-085-2010', '33-119-3230');
576 l_city_withheld number;
577
578 BEGIN
579
580 IF p_state = 'NY' THEN
581 open c_sum_of_city_withheld(p_assignment_action_id,p_tax_unit_id);
582 fetch c_sum_of_city_withheld into l_city_withheld;
583 close c_sum_of_city_withheld;
584
585 return to_char(l_city_withheld);
586
587 ELSE
588 return '0';
589
590 END IF; /* IF p_state = 'NY' */
591
592 END get_1099R_ny_value;
593
594 --
595 -- Function to Get Payee Latest Address
596 --
597 /*
598 Parameters :
599 p_effective_date -
600 This parameter indicates the year for the function.
601 p_item_name - 'EE_ADDRESS'
602 identifies Employee Address required for
603 Employee record.
604 p_report_type - This parameter will have the type of the report.
605 eg: '1099R'
606 p_format - This parameter will have the format to be printed
607 on 1099R. eg:'PUB1220','MMREF'
608 ( Will be used when we move the formatting from formula to function)
609 p_record_name - This parameter will have the particular
610 record name. eg: B for PUB1220
611 p_validate - This parameter will check whether it wants to
612 validate the error condition or override the
613 checking.
614 'N'- Override
615 'Y'- Check
616 p_exclude_from_output -
617 This parameter gives the information on
618 whether the record has to be printed or not.
619 'Y'- Do not print.
620 'N'- Print.
621 p_input_2 - Application Session Date this would be used to
622 fetch the address
623 sp_out_1 - This out parameter returns Employee Location Address
624 sp_out_2 - This out parameter returns Employee Deliver Address
625 sp_out_3 - This out parameter returns Employee City
626 sp_out_4 - This out parameter returns State
627 sp_out_5 - This out parameter returns Zip Code
628 sp_out_6 - This out parameter returns Zip Code Extension
629 sp_out_7 - This out parameter returns Foreign State/Province
630 sp_out_8 - This out parameter returns Foreign Postal Code
631 sp_out_9 - This out parameter returns Foreign Country Code
632 sp_out_10 - This parameter is returns Employee Number
633 or person_id if p_format is 'PUB1220'
634 */
635
636 FUNCTION GET_1099R_ITEM_DATA(
637 p_assignment_id IN number,
638 p_date_earned IN date,
639 p_tax_unit_id IN number,
640 p_effective_date IN varchar2,
641 p_item_name IN varchar2,
642 p_report_type IN varchar2,
643 p_format IN varchar2,
644 p_report_qualifier IN varchar2,
645 p_record_name IN varchar2,
646 p_input_1 IN varchar2,
647 p_input_2 IN varchar2,
648 p_input_3 IN varchar2,
649 p_input_4 IN varchar2,
650 p_input_5 IN varchar2,
651 p_validate IN varchar2,
652 p_exclude_from_output OUT nocopy varchar2,
653 sp_out_1 OUT nocopy varchar2,
654 sp_out_2 OUT nocopy varchar2,
655 sp_out_3 OUT nocopy varchar2,
656 sp_out_4 OUT nocopy varchar2,
657 sp_out_5 OUT nocopy varchar2,
658 sp_out_6 OUT nocopy varchar2,
659 sp_out_7 OUT nocopy varchar2,
660 sp_out_8 OUT nocopy varchar2,
661 sp_out_9 OUT nocopy varchar2,
662 sp_out_10 OUT nocopy varchar2
663 ) RETURN VARCHAR2 IS
664
665 -- Local Variable Declaration
666 --
667
668 c_item_name varchar2(40);
669 c_tax_unit_id hr_all_organization_units.organization_id%TYPE;
670 l_organization_name hr_organization_units.name%TYPE;
671 l_person_id number(10);
672 l_locality_company_id varchar2(50);
673 lr_employee_addr pay_us_get_item_data_pkg.person_name_address;
674 l_country varchar2(40);
675 l_effective_date date;
676 l_input_2 varchar2(200);
677
678 cursor get_person_id (c_assignment_id number , c_effective_date date ) /* 8219772 */
679 is
680 select distinct paa.person_id
681 from per_all_assignments_f paa
682 where paa.assignment_id = c_assignment_id
683 and c_effective_date between paa.effective_start_date and paa.effective_end_date ;
684
685 BEGIN
686 hr_utility.trace('In function GET_1099R_ITEM_DATA');
687 c_item_name:='EE_ADDRESS';
688 l_input_2 := ltrim(rtrim(p_input_2));
689 if l_input_2 is not null then
690 l_effective_date := fnd_date.canonical_TO_DATE(l_input_2);
691 else
692 l_effective_date := p_effective_date;
693 end if;
694
695 /* 8219772 */
696 open get_person_id( p_assignment_id ,p_effective_date );
697 fetch get_person_id into l_person_id ;
698 close get_person_id ;
699
700 hr_utility.trace('In function GET_MMREF_EMPLOYEE_ADDRESS');
701 lr_employee_addr :=
702 pay_us_get_item_data_pkg.GET_PERSON_NAME_ADDRESS(
703 p_report_type,
704 l_person_id,
705 p_assignment_id,
706 l_effective_date,
707 p_date_earned,
708 p_validate,
709 p_record_name);
710 hr_utility.trace('Employee '||lr_employee_addr.full_name ||' Info found ');
711 hr_utility.trace('Formatting Employee Address for '||p_report_type
712 ||' Reporting ');
713
714 /*
715 l_country := lr_employee_addr.country;
716
717 sp_out_1 := lr_employee_addr.addr_line_1
718 ||' '||lr_employee_addr.addr_line_2
719 ||' '||lr_employee_addr.addr_line_3;
720 sp_out_2 := sp_out_1;
721
722 if l_country = 'US' then
723
724 sp_out_3 := lr_employee_addr.city;
725 sp_out_4 := lr_employee_addr.province_state;
726 sp_out_5 := lr_employee_addr.postal_code;
727 sp_out_6 := '';
728 sp_out_7 := '';
729 sp_out_8 := '';
730 sp_out_9 := '';
731 sp_out_10 := '';
732
733 else
734
735 sp_out_3 := substr(lr_employee_addr.city,1,35);
736 sp_out_4 := '';
737 sp_out_5 := '';
738 sp_out_6 := '';
739 sp_out_7 := lr_employee_addr.province_state;
740 sp_out_8 := lr_employee_addr.postal_code;
741 --sp_out_9 := lr_employee_addr.country;
742 sp_out_9 := lr_employee_addr.country_name;
743 sp_out_10 := '';
744 end if;
745 */
746
747 IF p_format = 'PUB1220' then
748 --
749 -- Format Employee Address for 1099R (PUB1220 format)
750
751 pay_us_1099R_udfs.format_pub1220_address(
752 lr_employee_addr.full_name,
753 l_locality_company_id,
754 lr_employee_addr.employee_number,
755 lr_employee_addr.addr_line_1,
756 lr_employee_addr.addr_line_2,
757 lr_employee_addr.addr_line_3,
758 lr_employee_addr.city,
759 lr_employee_addr.province_state,
760 lr_employee_addr.postal_code,
761 lr_employee_addr.country,
762 lr_employee_addr.country_name,
763 lr_employee_addr.region_1,
764 lr_employee_addr.region_2,
765 lr_employee_addr.valid_address,
766 p_item_name,
767 p_report_type,
768 p_record_name,
769 p_validate,
770 p_input_1,
771 p_exclude_from_output,
772 sp_out_1,
773 sp_out_2,
774 sp_out_3,
775 sp_out_4,
776 sp_out_5,
777 sp_out_6,
778 sp_out_7,
779 sp_out_8,
780 sp_out_9,
781 sp_out_10
782 );
783 ELSIF p_format = '1099R_WV' then
784 -- Format Employee Address for 1099R (PUB1220 format)
785
786 pay_us_1099R_udfs.format_1099r_wv_address(
787 lr_employee_addr.full_name,
788 l_locality_company_id,
789 lr_employee_addr.employee_number,
790 lr_employee_addr.addr_line_1,
791 lr_employee_addr.addr_line_2,
792 lr_employee_addr.addr_line_3,
793 lr_employee_addr.city,
794 lr_employee_addr.province_state,
795 lr_employee_addr.postal_code,
796 lr_employee_addr.country,
797 lr_employee_addr.country_name,
798 lr_employee_addr.region_1,
799 lr_employee_addr.region_2,
800 lr_employee_addr.valid_address,
801 p_item_name,
802 p_report_type,
803 p_record_name,
804 p_validate,
805 p_input_1,
806 p_exclude_from_output,
807 sp_out_1,
808 sp_out_2,
809 sp_out_3,
810 sp_out_4,
811 sp_out_5,
812 sp_out_6,
813 sp_out_7,
814 sp_out_8,
815 sp_out_9,
816 sp_out_10
817 );
818
819 END IF;
820 /* 10084549 removed the condition p_report_qualifier = 'FED'
821 so that person_id is returned*/
822 if p_format = 'PUB1220' /* 8219772 */
823 then
824 sp_out_10 := to_char(l_person_id);
825 end if ;
826
827 RETURN sp_out_1;
828 END GET_1099R_ITEM_DATA;
829
830
831 --
832 -- Procedure to Format Employee Address
833 -- This procedure is being called from function GET_1099R_ITEM_DATA
834 --
835 PROCEDURE format_pub1220_address(
836 p_name IN varchar2,
837 p_locality_company_id IN varchar2,
838 p_emp_number IN varchar2,
839 p_address_line_1 IN varchar2,
840 p_address_line_2 IN varchar2,
841 p_address_line_3 IN varchar2,
842 p_town_or_city IN varchar2,
843 p_state IN varchar2,
844 p_postal_code IN varchar2,
845 p_country IN varchar2,
846 p_country_name IN varchar2,
847 p_region_1 IN varchar2,
848 p_region_2 IN varchar2,
849 p_valid_address IN varchar2,
850 p_item_name IN varchar2,
851 p_report_type IN varchar2,
852 p_record_name IN varchar2,
853 p_validate IN varchar2,
854 p_local_code IN varchar2,
855 p_exclude_from_output OUT nocopy varchar2,
856 sp_out_1 IN OUT nocopy varchar2,
857 sp_out_2 IN OUT nocopy varchar2,
858 sp_out_3 IN OUT nocopy varchar2,
859 sp_out_4 IN OUT nocopy varchar2,
860 sp_out_5 IN OUT nocopy varchar2,
861 sp_out_6 IN OUT nocopy varchar2,
862 sp_out_7 IN OUT nocopy varchar2,
863 sp_out_8 IN OUT nocopy varchar2,
864 sp_out_9 IN OUT nocopy varchar2,
865 sp_out_10 IN OUT nocopy varchar2 ) IS
866 --
867 TYPE message_columns IS RECORD(
868 p_mesg_description varchar2(100),
869 p_mesg_value varchar2(100),
870 p_output_value varchar2(100));
871 message_parameter_rec message_columns;
872 TYPE message_parameter_record IS TABLE OF message_parameter_rec%TYPE
873 INDEX BY BINARY_INTEGER;
874 message_record message_parameter_record;
875
876 l_level varchar2(1);
877 l_mesg_name varchar2(50);
878 l_name_or_number varchar2(50);
879 l_err boolean := FALSE;
880 l_hyphen_position number(10);
881 c_item_name varchar2(100);
882 l_name varchar2(100);
883 l_location_addr varchar2(100);
884 l_delivery_addr varchar2(100);
885 l_State varchar2(100);
886 l_city varchar2(100);
887
888 BEGIN
889 c_item_name := p_item_name;
890 l_name := rpad(upper(substr(nvl(p_name,lpad(' ',80)),1,80)),80);
891 l_location_addr := nvl(rpad(replace(replace(upper(substr(ltrim
892 (p_address_line_2 ||' '||p_address_line_3), 1, 40))
893 ,',','_'),''''),40) ,lpad(' ',40));
894 l_delivery_addr := nvl(rpad(replace(replace(upper(substr(ltrim(
895 p_address_line_1||' '||p_address_line_2 ||' '||
896 p_address_line_3),1,40)),',','_'),''''),40),lpad(' ',40));
897 l_State := upper(rpad(substr(p_state,1,2),2));
898 l_city := nvl(upper(rpad(substr(p_town_or_city, 1, 40), 40)),
899 lpad(' ',40));
900 -- Format for Valid Address
901 IF p_valid_address = 'Y' THEN
902 --{
903 hr_utility.trace('Valid Address found ');
904 hr_utility.trace('Location address '||l_location_addr);
905 hr_utility.trace('Delivery address '||l_delivery_addr);
906 hr_utility.trace('town_or_city '||l_city);
907 hr_utility.trace('postal_code '||p_postal_code);
908 hr_utility.trace('State '||l_state);
909 hr_utility.trace('p_country '||p_country);
910
911 IF c_item_name = 'EE_ADDRESS' THEN
912 l_level := 'A';
913 l_mesg_name := 'PAY_INVALID_EE_FORMAT';
914 l_name_or_number := p_emp_number;
915 ELSIF c_item_name = 'ER_ADDRESS' THEN
916 l_level := 'P';
917 l_mesg_name := 'PAY_INVALID_ER_FORMAT';
918 l_name_or_number := substr(p_name,1,50);
919 END IF;
920
921 message_record(1).p_mesg_description:='Invalid address.Address Line1 is null';
922 message_record(2).p_mesg_description:='Invalid address.City is null';
923 message_record(3).p_mesg_description:='Invalid address.State is null';
924 message_record(4).p_mesg_description:='Invalid address.Zip is null';
925 message_record(1).p_mesg_value:= l_delivery_addr;
926 message_record(2).p_mesg_value:= l_city;
927 message_record(3).p_mesg_value:= l_state;
928 message_record(4).p_mesg_value:= p_postal_code;
929
930 FOR i in 1..4 LOOP
931 IF message_record(i).p_mesg_value IS NULL THEN
932 pay_core_utils.push_message(801,l_mesg_name,l_level);
933 pay_core_utils.push_token('record_name', p_record_name);
934 pay_core_utils.push_token('name_or_number', l_name_or_number);
935 pay_core_utils.push_token('description',
936 message_record(i).p_mesg_description);
937 l_err:=TRUE;
938 END IF;
939 END LOOP;
940
941 sp_out_1 := l_location_addr;
942 sp_out_2 := l_delivery_addr;
943 sp_out_3 := l_city;
944
945 IF (p_country = 'US' OR p_country IS NULL )THEN
946 --sp_out_9:= lpad(' ',2);
947 sp_out_9:= lpad(substr(p_country,1,2),2);
948 IF p_region_2 IS NOT NULL THEN
949 sp_out_4 := l_state; --State abbreviation
950 sp_out_7 := lpad(' ',2); --foreign state/province
951 ELSE --The region is null.
952 sp_out_4 := lpad(' ',2);
953 sp_out_7 := lpad(' ',2);
954 END IF;
955 ELSE -- country is not US
956 sp_out_4 := lpad(' ',2);
957 -- Bug:2133985 foreign state/province
958 sp_out_7 := upper(rpad(substr(nvl(p_region_1,' '),1,2),2));
959 sp_out_9:= upper(rpad(substr(p_country_name,1,6),6));
960 END IF;
961
962 -- See if the zip code has a zip code extension ie. contains a hyphen
963
964 IF p_postal_code IS NOT NULL THEN
965 --{
966 l_hyphen_position := instr(p_postal_code, '-');
967
968 -- sp_out_5: zip code Len: 5
969 --sp_out_6: zip code extension Len: 4
970 --sp_out_8: foreign postal_code Len: 9
971
972 IF ( (p_country = 'US') OR ( p_country IS NULL ) ) THEN
973 IF l_hyphen_position = 0 THEN
974 sp_out_5:= upper(rpad(substr(p_postal_code,1,5),5));
975 sp_out_6 := lpad(' ', 4);
976 ELSE
977 sp_out_5:= upper(rpad(substr(substr
978 (p_postal_code,1,l_hyphen_position-1),1,5),5));
979 sp_out_6 := upper(rpad(substr(
980 p_postal_code,l_hyphen_position+1,4),4));
981 END IF;
982 sp_out_8:= lpad(' ',9);
983 ELSE -- ( (l_country = 'US') OR ( l_country IS NULL ) ) --
984 sp_out_5:= lpad(' ',5); --zip
985 sp_out_6:= lpad(' ', 4); --extension
986 sp_out_8:= upper(rpad(substr(p_postal_code,1,9),9)); --foreign zip
987 END IF;
988 --}
989 ELSE -- l_postal_code IS NULL.--
990 --{
991 sp_out_5:= lpad(' ',5); --zip
992 sp_out_8:= lpad(' ',9); -- foreign zip
993 sp_out_6:= lpad(' ', 4); --extension
994 hr_utility.trace('Zip or Postal Code is null');
995 --}
996 END IF;
997 IF (p_item_name = 'ER_ADDRESS') THEN
998 sp_out_10:= p_name;
999 hr_utility.trace('Organization Name = '||p_name);
1000 ELSIF p_item_name = 'EE_ADDRESS' THEN
1001 sp_out_10:= pay_us_reporting_utils_pkg.Character_check(p_emp_number);
1002 END IF;
1003 --}
1004 --
1005 -- when address is Invalid
1006 --
1007 ELSE
1008 --{
1009 IF p_item_name IN ('EE_ADDRESS',
1010 'ER_ADDRESS'
1011 ) THEN
1012 sp_out_1:=lpad(' ',40);
1013 sp_out_2:=lpad(' ',40);
1014 sp_out_3:=lpad(' ',40);
1015 sp_out_4:=lpad(' ',2);
1016 sp_out_5:=lpad(' ',5);
1017 sp_out_6:=lpad(' ',9);
1018 sp_out_7:=lpad(' ',2);
1019 sp_out_8:=lpad(' ',9);
1020 sp_out_9:=lpad(' ',2);
1021 sp_out_10:=lpad(' ',80);
1022 END IF;
1023 IF ( (p_item_name = 'ER_ADDRESS')OR
1024 (p_item_name = 'EE_ADDRESS')
1025 ) THEN
1026 l_err :=TRUE;
1027 END IF;
1028 --}
1029 END IF; --p_valid_address
1030 hr_utility.trace('location address '||sp_out_1);
1031 hr_utility.trace('delivery address '||sp_out_2);
1032 hr_utility.trace('City '||sp_out_3);
1033 hr_utility.trace('State '||sp_out_4);
1034 hr_utility.trace('Zip '||sp_out_5);
1035 hr_utility.trace('Zip Code Extension '||sp_out_6);
1036 hr_utility.trace('Foreign State/Province '||sp_out_7);
1037 hr_utility.trace('Foreign Zip '||sp_out_8);
1038 hr_utility.trace('Country '||sp_out_9);
1039 IF (p_item_name = 'ER_ADDRESS') THEN
1040 hr_utility.trace('Organization Name '||sp_out_10);
1041 ELSE
1042 hr_utility.trace('Employee Number '||sp_out_10);
1043 END IF;
1044 --
1045 -- Check to include or exclude record on the basis of validity of address
1046 --
1047 IF p_validate = 'Y' THEN
1048 IF l_err THEN
1049 p_exclude_from_output := 'Y';
1050 hr_utility.trace('p_validate is Y .error '||p_exclude_from_output);
1051 END IF;
1052 END IF;
1053 IF p_exclude_from_output IS NULL THEN
1054 p_exclude_from_output := 'N';
1055 END IF;
1056 END format_pub1220_address; --End of Procedure Validate_address
1057
1058 --
1059 -- Procedure to Format Employee Address
1060 -- This procedure is being called from function GET_1099R_ITEM_DATA
1061 --
1062 PROCEDURE format_1099r_wv_address(
1063 p_name IN varchar2,
1064 p_locality_company_id IN varchar2,
1065 p_emp_number IN varchar2,
1066 p_address_line_1 IN varchar2,
1067 p_address_line_2 IN varchar2,
1068 p_address_line_3 IN varchar2,
1069 p_town_or_city IN varchar2,
1070 p_state IN varchar2,
1071 p_postal_code IN varchar2,
1072 p_country IN varchar2,
1073 p_country_name IN varchar2,
1074 p_region_1 IN varchar2,
1075 p_region_2 IN varchar2,
1076 p_valid_address IN varchar2,
1077 p_item_name IN varchar2,
1078 p_report_type IN varchar2,
1079 p_record_name IN varchar2,
1080 p_validate IN varchar2,
1081 p_local_code IN varchar2,
1082 p_exclude_from_output OUT nocopy varchar2,
1083 sp_out_1 IN OUT nocopy varchar2,
1084 sp_out_2 IN OUT nocopy varchar2,
1085 sp_out_3 IN OUT nocopy varchar2,
1086 sp_out_4 IN OUT nocopy varchar2,
1087 sp_out_5 IN OUT nocopy varchar2,
1088 sp_out_6 IN OUT nocopy varchar2,
1089 sp_out_7 IN OUT nocopy varchar2,
1090 sp_out_8 IN OUT nocopy varchar2,
1091 sp_out_9 IN OUT nocopy varchar2,
1092 sp_out_10 IN OUT nocopy varchar2 ) IS
1093 --
1094 TYPE message_columns IS RECORD(
1095 p_mesg_description varchar2(100),
1096 p_mesg_value varchar2(100),
1097 p_output_value varchar2(100));
1098 message_parameter_rec message_columns;
1099 TYPE message_parameter_record IS TABLE OF message_parameter_rec%TYPE
1100 INDEX BY BINARY_INTEGER;
1101 message_record message_parameter_record;
1102
1103 l_level varchar2(1);
1104 l_mesg_name varchar2(50);
1105 l_name_or_number varchar2(50);
1106 l_err boolean := FALSE;
1107 l_hyphen_position number(10);
1108 c_item_name varchar2(100);
1109 l_name varchar2(100);
1110 l_location_addr varchar2(100);
1111 l_delivery_addr varchar2(100);
1112 l_State varchar2(100);
1113 l_city varchar2(100);
1114
1115 BEGIN
1116 c_item_name := p_item_name;
1117 l_name := rpad(upper(substr(nvl(p_name,lpad(' ',80)),1,80)),80);
1118 l_location_addr := nvl(rpad(replace(replace(upper(substr(ltrim
1119 (p_address_line_2 ||' '||p_address_line_3), 1, 40))
1120 ,',','_'),''''),40) ,lpad(' ',40));
1121 l_delivery_addr := nvl(rpad(replace(replace(upper(substr(ltrim(
1122 p_address_line_1||' '||p_address_line_2 ||' '||
1123 p_address_line_3),1,40)),',','_'),''''),40),lpad(' ',40));
1124 l_State := upper(rpad(substr(p_state,1,2),2));
1125 l_city := nvl(upper(rpad(substr(p_town_or_city, 1, 25), 25)),
1126 lpad(' ',25));
1127 -- Format for Valid Address
1128 IF p_valid_address = 'Y' THEN
1129 --{
1130 hr_utility.trace('Valid Address found ');
1131 hr_utility.trace('Location address '||l_location_addr);
1132 hr_utility.trace('Delivery address '||l_delivery_addr);
1133 hr_utility.trace('town_or_city '||l_city);
1134 hr_utility.trace('postal_code '||p_postal_code);
1135 hr_utility.trace('State '||l_state);
1136 hr_utility.trace('p_country '||p_country);
1137
1138 IF c_item_name = 'EE_ADDRESS' THEN
1139 l_level := 'A';
1140 l_mesg_name := 'PAY_INVALID_EE_FORMAT';
1141 l_name_or_number := p_emp_number;
1142 ELSIF c_item_name = 'ER_ADDRESS' THEN
1143 l_level := 'P';
1144 l_mesg_name := 'PAY_INVALID_ER_FORMAT';
1145 l_name_or_number := substr(p_name,1,50);
1146 END IF;
1147
1148 message_record(1).p_mesg_description:='Invalid address.Address Line1 is null';
1149 message_record(2).p_mesg_description:='Invalid address.City is null';
1150 message_record(3).p_mesg_description:='Invalid address.State is null';
1151 message_record(4).p_mesg_description:='Invalid address.Zip is null';
1152 message_record(1).p_mesg_value:= l_delivery_addr;
1153 message_record(2).p_mesg_value:= l_city;
1154 message_record(3).p_mesg_value:= l_state;
1155 message_record(4).p_mesg_value:= p_postal_code;
1156
1157 FOR i in 1..4 LOOP
1158 IF message_record(i).p_mesg_value IS NULL THEN
1159 pay_core_utils.push_message(801,l_mesg_name,l_level);
1160 pay_core_utils.push_token('record_name', p_record_name);
1161 pay_core_utils.push_token('name_or_number', l_name_or_number);
1162 pay_core_utils.push_token('description',
1163 message_record(i).p_mesg_description);
1164 l_err:=TRUE;
1165 END IF;
1166 END LOOP;
1167
1168 sp_out_1 := l_location_addr;
1169 sp_out_2 := l_delivery_addr;
1170
1171 IF (p_country = 'US' OR p_country IS NULL )THEN
1172 sp_out_3 := l_city;
1173 sp_out_9:= lpad(substr(p_country,1,2),2);
1174 IF p_region_2 IS NOT NULL THEN
1175 --sp_out_4 := l_state; --State abbreviation
1176 sp_out_4 := rpad(substr(l_state,1,2),10); --State abbreviation
1177 sp_out_7 := lpad(' ',2); --foreign state/province
1178 ELSE --The region is null.
1179 sp_out_4 := lpad(' ',10);
1180 sp_out_7 := lpad(' ',2);
1181 END IF;
1182 ELSE -- country is not US
1183 sp_out_3 := upper(rpad(substr(l_city,1,15),15));
1184 sp_out_4 := lpad(' ',10);
1185 -- Bug:2133985 foreign state/province
1186 sp_out_7 := upper(rpad(substr(nvl(p_region_1,' '),1,2),2));
1187 sp_out_9:= upper(rpad(substr(p_country_name,1,6),6));
1188 END IF;
1189
1190 -- See if the zip code has a zip code extension ie. contains a hyphen
1191
1192 IF p_postal_code IS NOT NULL THEN
1193 --{
1194 l_hyphen_position := instr(p_postal_code, '-');
1195
1196 -- sp_out_5: zip code Len: 5
1197 --sp_out_6: zip code extension Len: 5
1198 --sp_out_8: foreign postal_code Len: 5
1199
1200 IF ( (p_country = 'US') OR ( p_country IS NULL ) ) THEN
1201 IF l_hyphen_position = 0 THEN
1202 sp_out_5:= upper(rpad(substr(p_postal_code,1,5),5));
1203 sp_out_6 := lpad(' ', 5);
1204 ELSE
1205 sp_out_5:= upper(rpad(substr(substr
1206 (p_postal_code,1,l_hyphen_position-1),1,5),5));
1207 sp_out_6 := upper(rpad(substr(
1208 p_postal_code,l_hyphen_position+1,4),5));
1209 END IF;
1210 sp_out_8:= lpad(' ',5);
1211 ELSE -- ( (l_country = 'US') OR ( l_country IS NULL ) ) --
1212 sp_out_5:= lpad(' ',5); --zip
1213 sp_out_6:= lpad(' ', 5); --extension
1214 sp_out_8:= upper(rpad(substr(p_postal_code,1,5),5)); --foreign zip
1215 END IF;
1216 --}
1217 ELSE -- l_postal_code IS NULL.--
1218 --{
1219 sp_out_5:= lpad(' ',5); --zip
1220 sp_out_8:= lpad(' ',5); -- foreign zip
1221 sp_out_6:= lpad(' ', 5); --extension
1222 hr_utility.trace('Zip or Postal Code is null');
1223 --}
1224 END IF;
1225 IF (p_item_name = 'ER_ADDRESS') THEN
1226 sp_out_10:= p_name;
1227 hr_utility.trace('Organization Name = '||p_name);
1228 ELSIF p_item_name = 'EE_ADDRESS' THEN
1229 sp_out_10:= pay_us_reporting_utils_pkg.Character_check(p_emp_number);
1230 END IF;
1231 --}
1232 --
1233 -- when address is Invalid
1234 --
1235 ELSE
1236 --{
1237 IF p_item_name IN ('EE_ADDRESS',
1238 'ER_ADDRESS'
1239 ) THEN
1240 sp_out_1:=lpad(' ',40);
1241 sp_out_2:=lpad(' ',40);
1242 sp_out_3:=lpad(' ',15);
1243 sp_out_4:=lpad(' ',10);
1244 sp_out_5:=lpad(' ',5);
1245 sp_out_6:=lpad(' ',4);
1246 sp_out_7:=lpad(' ',2);
1247 sp_out_8:=lpad(' ',5);
1248 sp_out_9:=lpad(' ',2);
1249 sp_out_10:=lpad(' ',80);
1250 END IF;
1251 IF ( (p_item_name = 'ER_ADDRESS')OR
1252 (p_item_name = 'EE_ADDRESS')
1253 ) THEN
1254 l_err :=TRUE;
1255 END IF;
1256 --}
1257 END IF; --p_valid_address
1258 hr_utility.trace('location address '||sp_out_1);
1259 hr_utility.trace('delivery address '||sp_out_2);
1260 hr_utility.trace('City '||sp_out_3);
1261 hr_utility.trace('State '||sp_out_4);
1262 hr_utility.trace('Zip '||sp_out_5);
1263 hr_utility.trace('Zip Code Extension '||sp_out_6);
1264 hr_utility.trace('Foreign State/Province '||sp_out_7);
1265 hr_utility.trace('Foreign Zip '||sp_out_8);
1266 hr_utility.trace('Country '||sp_out_9);
1267
1268
1269 hr_utility.trace('location address '||replace(sp_out_1,' ','*'));
1270 hr_utility.trace('delivery address '||replace(sp_out_2,' ','*'));
1271 hr_utility.trace('City '||replace(sp_out_3,' ','*'));
1272 hr_utility.trace('State '||replace(sp_out_4,' ','*'));
1273 hr_utility.trace('Zip '||replace(sp_out_5,' ','*'));
1274 hr_utility.trace('Zip Code Extension '||replace(sp_out_6,' ','*'));
1275 hr_utility.trace('Foreign State/Province '||replace(sp_out_7,' ','*'));
1276 hr_utility.trace('Foreign Zip '||replace(sp_out_8,' ','*'));
1277 hr_utility.trace('Country '||replace(sp_out_9,' ','*'));
1278
1279
1280 IF (p_item_name = 'ER_ADDRESS') THEN
1281 hr_utility.trace('Organization Name '||sp_out_10);
1282 ELSE
1283 hr_utility.trace('Employee Number '||sp_out_10);
1284 END IF;
1285 --
1286 -- Check to include or exclude record on the basis of validity of address
1287 --
1288 IF p_validate = 'Y' THEN
1289 IF l_err THEN
1290 p_exclude_from_output := 'Y';
1291 hr_utility.trace('p_validate is Y .error '||p_exclude_from_output);
1292 END IF;
1293 END IF;
1294 IF p_exclude_from_output IS NULL THEN
1295 p_exclude_from_output := 'N';
1296 END IF;
1297 END format_1099r_wv_address; --End of Procedure Validate_address
1298
1299
1300 --
1301 FUNCTION Get_1099R_Transmitter_Value(
1302 p_payroll_action_id in varchar2,
1303 p_state in varchar2,
1304 sp_out_1 IN OUT nocopy varchar2,
1305 sp_out_2 IN OUT nocopy varchar2,
1306 sp_out_3 IN OUT nocopy varchar2,
1307 sp_out_4 IN OUT nocopy varchar2,
1308 sp_out_5 IN OUT nocopy varchar2,
1309 sp_out_6 IN OUT nocopy varchar2,
1310 sp_out_7 IN OUT nocopy varchar2,
1311 sp_out_8 IN OUT nocopy varchar2,
1312 sp_out_9 IN OUT nocopy varchar2,
1313 sp_out_10 IN OUT nocopy varchar2)
1314 RETURN VARCHAR2 IS
1315
1316 l_entity_id ff_database_items.user_entity_id%type;
1317 l_archived_value ff_archive_items.value%type;
1318 l_message varchar2(1000);
1319 l_main_return varchar2(100);
1320 l_payee_count number;
1321 lv_payee_count varchar2(30);
1322
1323 CURSOR get_payee_count
1324 (pact_id varchar2) IS
1325 SELECT count(paa.assignment_Action_id)
1326 FROM pay_assignment_actions paa
1327 WHERE paa.payroll_action_id = to_number(pact_id);
1328
1329 BEGIN
1330 -- hr_utility.trace_on(NULL,'oracle');
1331 /* call to funciton to get the value of 1099R Transmitter */
1332 hr_utility.trace('Payroll_action_id = '||p_payroll_action_id);
1333 hr_utility.trace('p_state = '||p_state);
1334
1335 OPEN get_payee_count(p_payroll_action_id);
1336 FETCH get_payee_count INTO l_payee_count;
1337
1338 IF get_payee_count%NOTFOUND THEN
1339
1340 l_message:='Error: No Payee found for Transmitter';
1341
1342 l_payee_count := 0;
1343
1344 hr_utility.trace('Payee Count = '||to_char(l_payee_count));
1345 ELSE
1346
1347 hr_utility.trace('Payroll_action_id = '||p_payroll_action_id);
1348 hr_utility.trace('Payee Count = '||to_char(l_payee_count));
1349
1350 END IF; /* get_payee_count */
1351
1352 CLOSE get_payee_count;
1353
1354 lv_payee_count := to_char(l_payee_count);
1355
1356 if p_state = 'CT' then
1357 sp_out_1 :=lpad(substr(nvl(lv_payee_count,'0'),1,8),8,'0');
1358 else
1359 lv_payee_count := '0';
1360 sp_out_1 :=lpad(substr(lv_payee_count,1,8),8,'0');
1361 end if;
1362
1363 sp_out_2 :=' ';
1364 sp_out_3 :=' ';
1365 sp_out_4 :=' ';
1366 sp_out_5 :=' ';
1367 sp_out_6 :=' ';
1368 sp_out_7 :=' ';
1369 sp_out_8 :=' ';
1370 sp_out_9 :=' ';
1371 sp_out_10 :=' ';
1372
1373 hr_utility.trace('sp_out_1 = '||sp_out_1);
1374
1375 l_main_return := ' ';
1376 return l_main_return;
1377
1378 END Get_1099R_Transmitter_Value;
1379 --
1380 -------------------------------------------------------------------------
1381 END pay_us_1099R_udfs;
1382