1 PACKAGE BODY pay_ac_taxability_chk_row as
2 /* $Header: paytaxrulchkrow.pkb 120.0 2005/09/10 03:50 psnellin noship $ */
3
4 /*
5 ******************************************************************
6 * *
7 * Copyright (C) 1993 Oracle Corporation. *
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, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20
21 Package Body Name : pay_ac_taxability_chk_row
22 Package File Name : paytaxrulchkrow.pkb
23 Description : This package declares functions and procedures
24 which supports US and CA taxability rules upload
25 via spread sheet loader.
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ----------- ---------- ------ ------- --------------------------
31 21-JUN-04 fusman 115.0 Created
32 *******************************************************************/
33
34
35 -- Package Variables
36 g_package varchar2(100);
37 TYPE character_data_table IS TABLE OF VARCHAR2(280)
38 INDEX BY BINARY_INTEGER;
39
40 ltt_state_code character_data_table;
41 ltt_state_abbrev character_data_table;
42 ltt_county_code character_data_table;
43 ltt_county_name character_data_table;
44 ltt_city_code character_data_table;
45 ltt_city_name character_data_table;
46
47
48 FUNCTION get_state_name
49 (p_jurisdiction_code IN VARCHAR2)
50 RETURN VARCHAR2
51 IS
52
53 cursor get_state_names
54 is
55 select state_abbrev,state_code
56 from pay_us_states
57 where state_code <= '51';
58
59
60 l_state_abbrev varchar2(2) := '';
61
62 Begin
63 hr_utility.trace('Before ltt_state_abbrev Loop');
64 for i in 1..ltt_state_abbrev.COUNT
65 Loop
66 if substr(p_jurisdiction_code,1,2) = ltt_state_code(i) then
67 hr_utility.trace('state_abbrev = '||ltt_state_abbrev(i));
68 return ltt_state_abbrev(i);
69
70 end if;
71
72 end loop;
73 hr_utility.trace('After ltt_state_abbrev Loop');
74
75 for strec in get_state_names
76 Loop
77 ltt_state_abbrev(strec.state_code) := strec.state_abbrev;
78 ltt_state_code(strec.state_code) := strec.state_code;
79 hr_utility.trace('state_abbrev = '||ltt_state_abbrev(strec.state_code));
80 if substr(p_jurisdiction_code,1,2) = strec.state_code then
81 hr_utility.trace('In strec Loop');
82 l_state_abbrev := strec.state_abbrev;
83 end if;
84
85 end loop;
86
87 return l_state_abbrev;
88 End;
89
90
91
92 FUNCTION get_county_city_name
93 (p_jurisdiction_code IN VARCHAR2,
94 p_county_or_city IN VARCHAR2)
95 RETURN VARCHAR2
96 IS
97
98 cursor get_state_names(cp_jurisdiction_code VARCHAR2)
99 is
100 select state_abbrev
101 from pay_us_states
102 where state_code <= '51';
103
104 cursor get_city_names(cp_state_code VARCHAR2,
105 cp_city_code VARCHAR2)
106 is
107 select city_name
108 from pay_us_city_names
109 where state_code = cp_state_code
110 and city_code = cp_city_code
111 and primary_flag = 'Y';
112
113 cursor get_county_names(cp_state_code VARCHAR2,
114 cp_county_code VARCHAR2)
115 is
116 select county_name
117 from pay_us_counties
118 where state_code = cp_state_code
119 and county_code = cp_county_code;
120
121 l_county_name varchar2(100);
122 l_city_name varchar2(100);
123
124 Begin
125
126 if substr(p_jurisdiction_code,8,4)='0000' then
127
128 open get_county_names(substr(p_jurisdiction_code,1,2),
129 substr(p_jurisdiction_code,4,3));
130 fetch get_county_names into l_county_name;
131 close get_county_names;
132
133 if p_county_or_city = 'COUNTY' then
134
135 return l_county_name;
136
137 else
138
139 return '';
140
141 end if;
142
143
144 elsif substr(p_jurisdiction_code,4,3) = '000' then
145
146 open get_city_names(substr(p_jurisdiction_code,1,2),
147 substr(p_jurisdiction_code,8,4));
148 fetch get_city_names into l_city_name;
149 close get_city_names;
150
151 if p_county_or_city = 'CITY' then
152
153 return l_city_name;
154
155 else
156
157 return '';
158
159 end if;
160
161 end if;
162
163
164
165
166 End;
167
168 /************************************************************
169 ** Function called for US Federal Context is passed
170 ************************************************************/
171 FUNCTION get_taxability_rule_row
172 (p_legislation_code IN VARCHAR2,
173 p_tax_type IN VARCHAR2,
174 p_tax_category IN VARCHAR2,
175 p_classification_id IN NUMBER,
176 p_jurisdiction_code IN VARCHAR2)
177 RETURN VARCHAR2
178
179 IS
180
181 cursor check_taxability_rule_row(cp_legislation_code VARCHAR2,
182 cp_tax_type VARCHAR2,
183 cp_tax_category VARCHAR2,
184 cp_classification_id NUMBER ,
185 cp_jurisdiction_code VARCHAR2)
186 is
187 SELECT nvl(status,'Y')
188 FROM pay_taxability_rules
189 where tax_type = cp_tax_type
190 and tax_category = cp_tax_category
191 and classification_id = cp_classification_id
192 and jurisdiction_code = cp_jurisdiction_code
193 and legislation_code = cp_legislation_code;
194
195 l_status varchar2(10);
196
197 BEGIN
198
199 open check_taxability_rule_row(p_legislation_code,
200 p_tax_type,
201 p_tax_category,
202 p_classification_id,
203 p_jurisdiction_code);
204
205 fetch check_taxability_rule_row into l_status;
206
207 if check_taxability_rule_row%NOTFOUND then
208
209 close check_taxability_rule_row;
210 return null;
211
212 end if;
213
214 If l_status = 'D' then
215
216 return 'N';
217
218 elsif l_status = 'Y' then
219
220 return l_status;
221
222 end if;
223
224 close check_taxability_rule_row;
225
226
227 END;
228
229
230 BEGIN
231 g_package := 'pay_ac_taxability_chk_row.';
232 -- hr_utility.trace_on(null,'tax_api');
233 end pay_ac_taxability_chk_row;
[Home] [Help]