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