1 PACKAGE BODY pay_us_tax_info_pkg AS
2 /* $Header: pyusgjit.pkb 115.8 2003/12/16 09:53:19 tclewis ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1997 Oracle Corporation US Ltd. *
7 * *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation US Ltd. *
17 * *
18 ******************************************************************
19 Name : pay_us_jit (BODY)
20 File : pyusgjit.pkb
21 Description : This package declares functions which are used
22 to return values for US Payroll W2 report.
23
24 Change List
25 -----------
26
27 Version Date Author ER/CR No. Description of Change
28 -------+---------+----------+---------+-------------------------------------------------
29 40.0 07-DEC-97 lwthomps Date Created
30 40.1/
31 110.1 08-DEC-98 rthirlby 735626 Added get_tax_exist.Used in state
32 balance views.
33 110.3 18-FEB-99 rthakur 793794 Added a check for WC2
34 110.5 04-AUG-99 rmonge Made package body adchkdrv compliant.
35 115.6 05-NOV_03 tclewis Added check for State EIC STEIC.
36 ========================================================================================
37 */
38
39 FUNCTION get_sit_exist (p_state_abbrev VARCHAR2,
40 p_date DATE
41 ) return boolean
42 --
43 IS
44 --
45 l_state_exist VARCHAR2(10);
46 --
47 begin
48 --
49 hr_utility.trace('p_state_abbrev ->'||p_state_abbrev);
50 --
51 begin
52 --
53 SELECT
54 sit_exists
55 INTO
56 l_state_exist
57 FROM
58 pay_us_state_tax_info_f pustif,
59 pay_us_states pus
60 WHERE
61 pus.state_code = pustif.state_code
62 AND pus.state_abbrev = p_state_abbrev
63 AND SIT_EXISTS = 'Y'
64 AND p_date between effective_start_date
65 and effective_end_date;
66 --
67 RETURN TRUE;
68 hr_utility.trace('l_state_exist ->'||l_state_exist);
69 --
70 exception when NO_DATA_FOUND then
71 RETURN FALSE;
72 --
73 end;
74 --
75 --
76 end get_sit_exist;
77 --
78 --
79 FUNCTION get_lit_exist (p_tax_type varchar2,
80 p_jurisdiction_code varchar2,
81 p_date date)
82 return boolean
83 --
84 IS
85 --
86 l_lit_exist VARCHAR2(10);
87 --
88 begin
89 --
90 hr_utility.trace('p_tax_type ->'||p_tax_type);
91 hr_utility.trace('p_jurisdiction_code->'||p_jurisdiction_code);
92 hr_utility.trace('p_date ->'||to_char(p_date));
93 --
94 begin
95 --
96 if p_tax_type like 'COUNTY' then
97 --
98 SELECT
99 COUNTY_TAX
100 INTO
101 l_lit_exist
102 FROM
103 pay_us_county_tax_info_f
104 WHERE
105 jurisdiction_code = p_jurisdiction_code
106 AND county_tax = 'Y'
107 AND p_date between effective_start_date
108 and effective_end_date;
109 --
110 elsif p_tax_type like 'CITY' then
111 --
112 SELECT
113 CITY_TAX
114 INTO
115 l_lit_exist
116 FROM
117 pay_us_city_tax_info_f
118 WHERE
119 jurisdiction_code = p_jurisdiction_code
120 AND city_tax = 'Y'
121 AND p_date between effective_start_date
122 and effective_end_date;
123 --
124 end if;
125 --
126 RETURN TRUE;
127 hr_utility.trace('l_lit_exist ->'||l_lit_exist);
128 --
129 exception when NO_DATA_FOUND then
130 RETURN FALSE;
131 --
132 end;
133 --
134 --
135 end get_lit_exist;
136 --
137 --------------------------------------------------------------------
138 -- Function get_tax_exist. This returns 'Y' if the following state
139 -- state taxes exist, SDI, SUI, SIT, WC for a particular state on a
140 -- particular date.
141 --------------------------------------------------------------------
142 FUNCTION get_tax_exist(p_tax_type varchar2,
143 p_jurisdiction_code varchar2,
144 p_ee_or_er varchar2,
145 p_date date)
146 return varchar2
147 IS
148 --
149 l_exists varchar2(2);
150 l_false varchar2(1) := 'N';
151 BEGIN
152 --
153 IF (p_tax_type <> 'WC' OR p_tax_type <> 'WC2') THEN
154 --
155 SELECT 'Y'
156 INTO l_exists
157 FROM pay_us_state_tax_info_f pust
158 WHERE state_code = substr(p_jurisdiction_code,1,2)
159 AND p_date between effective_start_date and effective_end_date
160 AND decode(decode(p_tax_type||'_'||p_ee_or_er
161 , 'SDI_EE', to_char(SDI_EE_WAGE_LIMIT)
162 , 'SDI_ER', to_char(SDI_ER_WAGE_LIMIT)
163 , 'SIT_', SIT_EXISTS
164 , 'SUI_EE', to_char(SUI_EE_WAGE_LIMIT)
165 , 'SUI_ER', to_char(SUI_ER_WAGE_LIMIT)
166 , 'STEIC_EE',STA_INFORMATION17
167 , 'UNKNOWN'), '', 'N', 'Y')
168 = 'Y';
169 --
170 RETURN l_exists;
171 ELSE
172 --
173 SELECT 'Y'
174 INTO l_exists
175 FROM pay_wc_funds pwf,
176 pay_us_states pus
177 WHERE pus.state_code = substr(p_jurisdiction_code,1,2)
178 AND pus.state_abbrev = pwf.state_code;
179 /* How about business_group_id too ? */
180 RETURN l_exists;
181 --
182 END IF;
183 --
184 RETURN l_false;
185 --
186 EXCEPTION
187 --
188 WHEN NO_DATA_FOUND THEN
189 RETURN l_false;
190 --
191 WHEN TOO_MANY_ROWS THEN
192 RETURN l_exists;
193 END get_tax_exist;
194 --
195 end pay_us_tax_info_pkg;
[Home] [Help]