1 PACKAGE BODY ZX_TCM_GET_EXCEPT_PKG AS
2 /* $Header: zxcgetexceptb.pls 120.10 2011/04/11 07:10:29 ssanka ship $ */
3 /* ======================================================================*
4 | Global Data Types |
5 * ======================================================================*/
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZX_TCM_GET_EXCEPT_PKG';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'ZX.PLSQL.ZX_TCM_GET_EXCEPT_PKG.';
16
17 G_LINES_PER_FETCH CONSTANT NUMBER:= 1000;
18 G_MAX_LINES_PER_FETCH CONSTANT NUMBER:= 1000000;
19
20
21 PROCEDURE get_tax_exceptions(p_inventory_item_id IN NUMBER,
22 p_inventory_organization_id IN NUMBER,
23 p_product_category IN VARCHAR2,
24 p_fisc_classification_code IN VARCHAR2,
25 p_tax_regime_code IN VARCHAR2,
26 p_tax IN VARCHAR2,
27 p_tax_status_code IN VARCHAR2,
28 p_tax_rate_code IN VARCHAR2,
29 p_trx_date IN DATE,
30 p_tax_jurisdiction_id IN NUMBER,
31 p_multiple_jurisdictions_flag IN VARCHAR2,
32 x_exception_rec OUT NOCOPY exception_rec_type,
33 x_return_status OUT NOCOPY VARCHAR2) IS
34
35 CURSOR item_exceptions(p_inventory_item_id NUMBER,
36 p_inventory_organization_id NUMBER,
37 p_tax_regime_code VARCHAR2,
38 p_tax VARCHAR2,
39 p_tax_status_code VARCHAR2,
40 p_tax_rate_code VARCHAR2,
41 p_tax_jurisdiction_id NUMBER
42 ) IS
43 SELECT tax_exception_id, exception_type_code, rate_modifier
44 FROM zx_sco_exceptions
45 WHERE product_id = p_inventory_item_id
46 -- AND inventory_org_id = p_inventory_organization_id
47 AND classification_type_code IS NULL
48 AND classification_code IS NULL
49 AND tax_regime_code = p_tax_regime_code
50 AND NVL(tax, 'XX') IN (NVL(p_tax, 'XX'), 'XX')
51 AND NVL(tax_status_code, 'XX') IN (NVL(p_tax_status_code, 'XX'), 'XX')
52 AND NVL(tax_rate_code, 'XX') IN (NVL(p_tax_rate_code, 'XX'), 'XX')
53 AND NVL(tax_jurisdiction_id, -99) IN (NVL(p_tax_jurisdiction_id, -99), -99)
54 /* Added for bug 4619907 */
55 AND duplicate_exception = 0
56 AND effective_from <= p_trx_date
57 AND (effective_to >= p_trx_date or effective_to is null);
58
59 CURSOR fisc_exceptions(p_tax_regime_code VARCHAR2,
60 p_tax VARCHAR2,
61 p_tax_status_code VARCHAR2,
62 p_tax_rate_code VARCHAR2,
63 p_tax_jurisdiction_id NUMBER,
64 p_inventory_item_id NUMBER,
65 p_inventory_organization_id NUMBER
66 ) IS
67 SELECT ex.tax_exception_id, ex.exception_type_code, ex.rate_modifier
68 FROM zx_sco_exceptions ex, mtl_categories_b_kfv mc, mtl_category_sets_b mcs,
69 fnd_id_flex_structures_vl fifs, mtl_item_categories mic,
70 zx_fc_types_b fc, zx_fc_types_reg_assoc fcreg
71 WHERE ex.product_id IS NULL
72 AND ex.tax_regime_code = p_tax_regime_code
73 AND (ex.tax = p_tax or ex.tax is null)
74 AND (ex.tax_status_code = p_tax_status_code or ex.tax_status_code is null)
75 AND (ex.tax_rate_code = p_tax_rate_code or ex.tax_rate_code is null)
76 AND (ex.tax_jurisdiction_id = p_tax_jurisdiction_id or ex.tax_jurisdiction_id is null)
77 AND ex.duplicate_exception = 0
78 AND ex.effective_from <= p_trx_date
79 AND (ex.effective_to >= p_trx_date or ex.effective_to is null)
80 AND ex.classification_type_code = fc.classification_type_code
81 AND ex.classification_code = REPLACE(mc.concatenated_segments, fifs.concatenated_segment_delimiter,'')
82 AND mc.structure_id = fifs.id_flex_num
83 AND fifs.application_id = 401
84 AND fifs.id_flex_code = 'MCAT'
85 AND mc.enabled_flag = 'Y'
86 AND fifs.id_flex_num = mcs.structure_id
87 AND mcs.category_set_id = fc.owner_id_num
88 AND fc.classification_type_categ_code = 'PRODUCT_FISCAL_CLASS'
89 AND fc.classification_type_code = fcreg.classification_type_code
90 AND fcreg.tax_regime_code = p_tax_regime_code
91 AND fcreg.use_in_item_exceptions_flag = 'Y'
92 AND fcreg.effective_from <= p_trx_date
93 AND (fcreg.effective_to >= p_trx_date OR fcreg.effective_to IS NULL) --Bug 5383505
94 AND mic.category_set_id = mcs.category_set_id
95 AND mc.category_id = mic.category_id
96 AND mic.inventory_item_id = p_inventory_item_id
97 AND mic.organization_id = p_inventory_organization_id;
98
99 CURSOR fisc_exceptions_no_item(p_tax_regime_code VARCHAR2,
100 p_tax VARCHAR2,
101 p_tax_status_code VARCHAR2,
102 p_tax_rate_code VARCHAR2,
103 p_tax_jurisdiction_id NUMBER,
104 p_fiscal_classification VARCHAR2
105 ) IS
106 SELECT ex.tax_exception_id,
107 ex.exception_type_code,
108 ex.rate_modifier
109 FROM zx_sco_exceptions ex,
110 mtl_categories_b_kfv mc,
111 mtl_category_sets_b mcs,
112 fnd_id_flex_structures_vl fifs,
113 zx_fc_types_b fc,
114 zx_fc_types_reg_assoc fcreg
115 WHERE ex.product_id IS NULL
116 AND ex.tax_regime_code = p_tax_regime_code
117 AND (ex.tax = p_tax or ex.tax is null)
118 AND (ex.tax_status_code = p_tax_status_code or ex.tax_status_code is null)
119 AND (ex.tax_rate_code = p_tax_rate_code or ex.tax_rate_code is null)
120 AND (ex.tax_jurisdiction_id = p_tax_jurisdiction_id or ex.tax_jurisdiction_id is null)
121 AND ex.duplicate_exception = 0
122 AND ex.effective_from <= p_trx_date
123 AND (ex.effective_to >= p_trx_date or ex.effective_to is null)
124 AND ex.classification_type_code = fc.classification_type_code
125 AND ex.classification_code = REPLACE(mc.concatenated_segments, fifs.concatenated_segment_delimiter,'')
126 AND mc.structure_id = fifs.id_flex_num
127 AND fifs.application_id = 401
128 AND fifs.id_flex_code = 'MCAT'
129 AND mc.enabled_flag = 'Y'
130 AND fifs.id_flex_num = mcs.structure_id
131 AND mcs.category_set_id = fc.owner_id_num
132 AND fc.classification_type_categ_code = 'PRODUCT_FISCAL_CLASS'
133 AND fc.classification_type_code = fcreg.classification_type_code
134 AND fcreg.tax_regime_code = p_tax_regime_code
135 AND fcreg.use_in_item_exceptions_flag = 'Y'
136 AND fcreg.effective_from <= p_trx_date
137 AND (fcreg.effective_to >= p_trx_date OR fcreg.effective_to IS NULL)
138 AND ex.classification_code = p_fiscal_classification;
139
140 CURSOR fisc_exceptions_non_inv(p_product_category VARCHAR2,
141 p_tax_regime_code VARCHAR2,
142 p_tax VARCHAR2,
143 p_tax_status_code VARCHAR2,
144 p_tax_rate_code VARCHAR2,
145 p_tax_jurisdiction_id NUMBER
146 ) IS
147 SELECT tax_exception_id, exception_type_code, rate_modifier
148 FROM zx_sco_exceptions ex, zx_fc_types_b fct, zx_fc_types_reg_assoc fcreg, zx_fc_codes_b fcc
149 WHERE ex.tax_regime_code = p_tax_regime_code
153 AND (ex.tax_jurisdiction_id = p_tax_jurisdiction_id or ex.tax_jurisdiction_id is null)
150 AND (ex.tax = p_tax or ex.tax is null)
151 AND (ex.tax_status_code = p_tax_status_code or ex.tax_status_code is null)
152 AND (ex.tax_rate_code = p_tax_rate_code or ex.tax_rate_code is null)
154 AND ex.classification_type_code = fct.classification_type_code
155 AND ex.classification_code = fcc.classification_code
156 AND ex.duplicate_exception = 0
157 AND ex.effective_from <= p_trx_date
158 AND (ex.effective_to >= p_trx_date or ex.effective_to is null)
159 AND fct.classification_type_categ_code = 'PRODUCT_GENERIC_CLASSIFICATION'
160 AND fct.classification_type_code = fcreg.classification_type_code
161 AND fcreg.tax_regime_code = p_tax_regime_code
162 AND fcreg.use_in_item_exceptions_flag = 'Y'
163 AND fct.classification_type_code = fcc.classification_type_code
164 AND p_trx_date BETWEEN fcreg.effective_from AND fcreg.effective_to
165 AND fcc.classification_code = p_product_category;
166
167 l_tax_jurisdiction_id NUMBER;
168
169 l_status VARCHAR2(1);
170 l_db_status VARCHAR2(1);
171 l_api_name CONSTANT VARCHAR2(30):= 'GET_TAX_EXCEPTIONS';
172 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
173
174 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
175 l_tax_jurisdiction_id_tbl NUMBER_TBL_TYPE;
176
177 BEGIN
178 -- Get exception for inventory item;
179 -- If not found
180 -- find the fiscal classification type for the tax regime that is used to define exceptions;
181 -- Check if the inventory item passed belongs to the Fiscal classification type
182 -- find out the fiscal classification code(s).
183 -- check if an exception exists for that fiscal classification type and code(s)
184 -- If found pass it back to TDM
185 -- End if;
186 -- End if;
187 -- End if;
188 ----- Add logic to use zx_jurisdictions_gt table
189 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
190 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
191 END IF;
192 x_return_status := FND_API.G_RET_STS_SUCCESS;
193
194 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
195 l_log_msg := 'Parameters '||
196 ' p_inventory_item_id: '||to_char(p_inventory_item_id)||
197 ' p_inventory_organization_id: '||p_inventory_organization_id||
198 ' p_product_category: '||to_char(p_product_category)||
199 ' p_fiscal_classification: '||to_char(p_fisc_classification_code)||
200 ' p_tax_regime_code: '||to_char(p_tax_regime_code)||
201 ' p_tax : '||to_char(p_tax)||
202 ' p_tax_status_code: '||to_char(p_tax_status_code)||
203 ' p_tax_rate_code : '||to_char(p_tax_rate_code)||
204 ' p_tax_jurisdiction_id: '||to_char(p_tax_jurisdiction_id)||
205 ' p_multiple_jurisdictions_flag: '||to_char(p_multiple_jurisdictions_flag)||' ';
206
207 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name, l_log_msg);
208 END IF;
209
210 IF nvl(p_multiple_jurisdictions_flag,'N') = 'N' THEN
211 l_tax_jurisdiction_id_tbl(1) := p_tax_jurisdiction_id;
212 ELSE
213 SELECT tax_jurisdiction_id
214 BULK COLLECT INTO l_tax_jurisdiction_id_tbl
215 FROM zx_jurisdictions_gt
216 ORDER BY precedence_level;
217 END IF;
218
219 FOR i in l_tax_jurisdiction_id_tbl.first..l_tax_jurisdiction_id_tbl.last LOOP
220 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
221 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME,'Jurisdiction ID: '||
222 Nvl(l_tax_jurisdiction_id_tbl(i),-99));
223 END IF;
224 OPEN item_exceptions(p_inventory_item_id,
225 p_inventory_organization_id,
226 p_tax_regime_code,
227 p_tax,
228 p_tax_status_code,
229 p_tax_rate_code,
230 l_tax_jurisdiction_id_tbl(i));
231 LOOP
232 FETCH item_exceptions INTO x_exception_rec.tax_exception_id, x_exception_rec.exception_type_code,
233 x_exception_rec.exception_rate;
234 EXIT WHEN item_exceptions%NOTFOUND;
235 IF x_exception_rec.tax_exception_id IS NOT NULL THEN
236 EXIT;
237 END IF;
238 END LOOP;
239 CLOSE item_exceptions;
240 --
241 IF x_exception_rec.tax_exception_id IS NULL THEN
242 BEGIN
243 SELECT STATUS, DB_STATUS
244 INTO l_status, l_db_status
245 FROM fnd_product_installations
246 WHERE APPLICATION_ID = '401';
247 EXCEPTION WHEN OTHERS THEN
248 NULL;
249 END;
250
251 IF (nvl(l_status,'N') = 'I' or nvl(l_db_status,'N') = 'I') THEN --Bug 5383505
252 IF p_inventory_item_id IS NOT NULL THEN
253 OPEN fisc_exceptions( p_tax_regime_code,
254 p_tax,
255 p_tax_status_code,
256 p_tax_rate_code,
257 l_tax_jurisdiction_id_tbl(i),
258 p_inventory_item_id,
259 p_inventory_organization_id);
260 LOOP
261 FETCH fisc_exceptions INTO x_exception_rec.tax_exception_id, x_exception_rec.exception_type_code,
262 x_exception_rec.exception_rate;
263 EXIT WHEN fisc_exceptions%NOTFOUND;
264 IF x_exception_rec.tax_exception_id IS NOT NULL THEN
265 EXIT;
266 END IF;
267 END LOOP;
268 CLOSE fisc_exceptions;
269 ELSIF p_fisc_classification_code IS NOT NULL THEN
270 OPEN fisc_exceptions_no_item( p_tax_regime_code,
271 p_tax,
272 p_tax_status_code,
273 p_tax_rate_code,
274 l_tax_jurisdiction_id_tbl(i),
275 p_fisc_classification_code);
276 LOOP
277 FETCH fisc_exceptions_no_item INTO x_exception_rec.tax_exception_id, x_exception_rec.exception_type_code,
278 x_exception_rec.exception_rate;
279 EXIT WHEN fisc_exceptions_no_item%NOTFOUND;
280 IF x_exception_rec.tax_exception_id IS NOT NULL THEN
281 EXIT;
282 END IF;
283 END LOOP;
284 CLOSE fisc_exceptions_no_item;
285 END IF; -- Inventory Item Id is not null
286 ELSE --l inventory is not installed then
287 OPEN fisc_exceptions_non_inv(p_product_category,
288 p_tax_regime_code,
289 p_tax,
290 p_tax_status_code,
291 p_tax_rate_code,
292 l_tax_jurisdiction_id_tbl(i));
293 LOOP
294 FETCH fisc_exceptions_non_inv INTO x_exception_rec.tax_exception_id, x_exception_rec.exception_type_code, x_exception_rec.exception_rate;
295 EXIT WHEN fisc_exceptions_non_inv%NOTFOUND;
296 IF x_exception_rec.tax_exception_id IS NOT NULL THEN
297 EXIT;
298 END IF;
299 END LOOP;
300 CLOSE fisc_exceptions_non_inv;
301
302 IF x_exception_rec.tax_exception_id IS NULL THEN
303 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
304 l_log_msg := 'No exception found ';
305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name, l_log_msg);
306 END IF;
307 END IF;
308 END IF; --l inventory is not installed then
309 END IF; -- Exception ID is null
310 END LOOP; -- Looping over all the jurisdictions.
311 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
312 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()-');
313 END IF;
314 END;
315 END;
[Home] [Help]