1 PACKAGE BODY PAY_US_ADHOC_UTILS AS
2 /* $Header: pyusdisc.pkb 120.2 2005/06/03 06:50:06 sdhole noship $ */
3 /* ******************************************************************
4 * *
5 * Copyright (C) 1993 Oracle Corporation. *
6 * All rights reserved. *
7 * *
8 * This material has been provided pursuant to an agreement *
9 * containing restrictions on its use. The material is also *
10 * protected by copyright law. No part of this material may *
11 * be copied or distributed, transmitted or transcribed, in *
12 * any form or by any means, electronic, mechanical, magnetic, *
13 * manual, or otherwise, or disclosed to third parties without *
14 * the express written permission of Oracle Corporation, *
15 * 500 Oracle Parkway, Redwood City, CA, 94065. *
16 * *
17 ******************************************************************
18
19 Name : PAY_US_ADHOC_UTILS
20
21 Description : This package is created for the discoverer W2
22 (Year End) Reporting purpose for getting the
23 details about common pay agent, locality name
24 In future we can use the same package by adding
25 more functions for other reporting purpose also.
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ----------- ---------- ------ ------- -----------------------------------
31 09-NOV-2004 sdhole 115.0 Created.
32 09-NOV-2004 sdhole 115.1 added function
33 get_secprofile_bg_id.
34 26-APR-2005 115.3 4226022 Removed the code added in 115.2
35 version and it has been moved
36 to PAY_ADHOC_UTILS_PKG.
37 26-APR-2005 sdhole 115.4 4226022 Added function
38 get_balance_valid_load_date.
39 30-MAY-2005 sdhole 115.5 4400526 Modified get_balance_valid_load_date
40 function.
41 03-JUN-2005 sdhole 115.6 4400526 Code for the function
42 get_balance_valid_load_date moved to
43 PAY_ADHOC_UTILS_PKG. No longer needed
44 in US utils package.
45 ---------------------------------------------------------------------------
46 */
47 function get_locality_name(p_tax_type VARCHAR2,
48 p_state_abbrev VARCHAR2,
49 p_assig_action_id NUMBER,
50 p_locality_name VARCHAR2,
51 p_jurisdiction VARCHAR2) return varchar2 IS
52
53 V_nr_flag VARCHAR2(100);
54 V_nr_jd VARCHAR2(100);
55 v_locality_name varchar2(100);
56
57 BEGIN
58 v_locality_name := p_locality_name;
59
60 if ( p_tax_type = 'CITY SCHOOL' or p_tax_type = 'COUNTY SCHOOL') then
61 if p_state_abbrev = 'OH' then
62 v_locality_name := substr(p_jurisdiction,5,4)||' '
63 ||substr(p_locality_name,1,8);
64 elsif p_state_abbrev = 'KY' then
65 v_locality_name := substr(p_jurisdiction,7,2)||' '
66 ||substr(p_locality_name,1,10);
67 else
68 v_locality_name := substr(p_jurisdiction,4,5)||' '
69 ||substr(p_locality_name,1,7);
70 end if;
71 end if;
72
73 if (p_state_abbrev = 'IN' and p_tax_type = 'COUNTY') then
74 begin
75 select nvl(value,'N') into v_nr_flag
76 from ff_database_items fdi,
77 ff_archive_items fai
78 where user_name = 'A_IN_NR_FLAG'
79 and fdi.user_entity_id = fai.user_entity_id
80 and fai.context1 = p_assig_action_id;
81
82 if v_nr_flag = 'N' then
83 begin
84 select nvl(value,'00-000-0000') into v_nr_jd
85 from ff_database_items fdi,
86 ff_archive_items fai
87 where fdi.user_name = 'A_IN_RES_JD'
88 and fdi.user_entity_id = fai.user_entity_id
89 and context1 = p_assig_action_id;
90
91 if substr(p_jurisdiction,1,2) = '15' then
92 if v_nr_jd <> p_jurisdiction then
93 v_locality_name := 'NR '||substr(p_locality_name,1,10);
94 end if;
95 end if;
96 exception
97 when others then
98 null;
99 end;
100 end if;
101 exception
102 when others then
103 null;
104 end;
105 end if;
106
107 RETURN(v_locality_name);
108 End get_locality_name;
109 --
110 --
111 function get_common_pay_agent_id(p_year varchar2) return number is
112 v_agent_tax_unit_id number;
113 v_error_msg varchar2(2000);
114 begin
115 hr_us_w2_rep.get_agent_tax_unit_id(hr_security.get_sec_profile_bg_id
116 ,p_year
117 ,v_agent_tax_unit_id
118 ,v_error_msg);
119 return(v_agent_tax_unit_id);
120 --
121 exception
122 when others then
123 return(null);
124 end get_common_pay_agent_id;
125 --
126 --
127 function get_commonpay_agent_details(p_year varchar2,
128 p_commonpay_agent_id number,
129 p_type varchar2) return varchar2 is
130 v_name varchar2(1000);
131
132 begin
133 -- Pass the p_type = 'EIN' to get the Employer Identification
134 -- Pass the p_type = 'NAME' to get the 2678 name.
135
136 if p_type = 'EIN' then
137 SELECT FEDERAL_EIN into v_name
138 from pay_us_w2_tax_unit_v
139 where TAX_UNIT_ID = p_commonpay_agent_id
140 and year = p_year;
141
142 elsif p_type = 'NAME' then
143
144 SELECT tax_unit_name into v_name
145 from pay_us_w2_tax_unit_v
146 where TAX_UNIT_ID = p_commonpay_agent_id
147 and year = p_year;
148 end if;
149 return(v_name);
150 exception
151 when others then
152 return(null);
153 end get_commonpay_agent_details;
154 --
155 --
156 FUNCTION get_secprofile_bg_id
157 RETURN per_security_profiles.business_group_id%TYPE IS
158
159 BEGIN
160
161 RETURN hr_security.get_sec_profile_bg_id;
162
163 END get_secprofile_bg_id;
164 --
165 --
166 END PAY_US_ADHOC_UTILS;
[Home] [Help]