1 PACKAGE BODY ZX_TCM_GET_EXCEPT_PKG AS
2 /* $Header: zxcgetexceptb.pls 120.8 2006/09/16 00:15:57 sachandr 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_tax_regime_code IN VARCHAR2,
25 p_tax IN VARCHAR2,
26 p_tax_status_code IN VARCHAR2,
27 p_tax_rate_code IN VARCHAR2,
28 p_trx_date IN DATE,
29 p_tax_jurisdiction_id IN NUMBER,
30 p_multiple_jurisdictions_flag IN VARCHAR2,
31 x_exception_rec OUT NOCOPY exception_rec_type,
32 x_return_status OUT NOCOPY VARCHAR2) IS
33
34 CURSOR item_exceptions(p_inventory_item_id NUMBER,
35 p_inventory_organization_id NUMBER,
36 p_tax_regime_code VARCHAR2,
37 p_tax VARCHAR2,
38 p_tax_status_code VARCHAR2,
39 p_tax_rate_code VARCHAR2,
40 p_tax_jurisdiction_id NUMBER
41 ) IS
42 SELECT tax_exception_id, exception_type_code, rate_modifier
43 FROM zx_sco_exceptions
44 WHERE product_id = p_inventory_item_id
45 -- AND inventory_org_id = p_inventory_organization_id
46 AND classification_type_code IS NULL
47 AND classification_code IS NULL
48 AND tax_regime_code = p_tax_regime_code
49 AND NVL(tax, 'XX') IN (NVL(p_tax, 'XX'), 'XX')
50 AND NVL(tax_status_code, 'XX') IN (NVL(p_tax_status_code, 'XX'), 'XX')
51 AND NVL(tax_rate_code, 'XX') IN (NVL(p_tax_rate_code, 'XX'), 'XX')
52 AND NVL(tax_jurisdiction_id, -99) IN (NVL(p_tax_jurisdiction_id, -99), -99)
53 /* Added for bug 4619907 */
54 AND duplicate_exception = 0
55 AND effective_from <= p_trx_date
56 AND (effective_to >= p_trx_date or effective_to is null);
57
58 CURSOR fisc_exceptions(p_tax_regime_code VARCHAR2,
59 p_tax VARCHAR2,
60 p_tax_status_code VARCHAR2,
61 p_tax_rate_code VARCHAR2,
62 p_tax_jurisdiction_id NUMBER,
63 p_inventory_item_id NUMBER,
64 p_inventory_organization_id NUMBER
65 ) IS
66 SELECT ex.tax_exception_id, ex.exception_type_code, ex.rate_modifier
67 FROM zx_sco_exceptions ex, mtl_categories_b_kfv mc, mtl_category_sets_b mcs,
68 fnd_id_flex_structures_vl fifs, mtl_item_categories mic,
69 zx_fc_types_b fc, zx_fc_types_reg_assoc fcreg
70 WHERE ex.product_id IS NULL
71 AND ex.tax_regime_code = p_tax_regime_code
72 AND (ex.tax = p_tax or ex.tax is null)
73 AND (ex.tax_status_code = p_tax_status_code or ex.tax_status_code is null)
74 AND (ex.tax_rate_code = p_tax_rate_code or ex.tax_rate_code is null)
75 AND (ex.tax_jurisdiction_id = p_tax_jurisdiction_id or ex.tax_jurisdiction_id is null)
76 AND ex.duplicate_exception = 0
77 AND ex.effective_from <= p_trx_date
78 AND (ex.effective_to >= p_trx_date or ex.effective_to is null)
79 AND ex.classification_type_code = fc.classification_type_code
80 AND ex.classification_code = REPLACE(mc.concatenated_segments, fifs.concatenated_segment_delimiter,'')
81 AND mc.structure_id = fifs.id_flex_num
82 AND fifs.application_id = 401
83 AND fifs.id_flex_code = 'MCAT'
84 AND mc.enabled_flag = 'Y'
85 AND fifs.id_flex_num = mcs.structure_id
86 AND mcs.category_set_id = fc.owner_id_num
87 AND fc.classification_type_categ_code = 'PRODUCT_FISCAL_CLASS'
88 AND fc.classification_type_code = fcreg.classification_type_code
89 AND fcreg.tax_regime_code = p_tax_regime_code
90 AND fcreg.use_in_item_exceptions_flag = 'Y'
91 AND fcreg.effective_from <= p_trx_date
92 AND (fcreg.effective_to >= p_trx_date OR fcreg.effective_to IS NULL) --Bug 5383505
93 AND mic.category_set_id = mcs.category_set_id
94 AND mc.category_id = mic.category_id
95 AND mic.inventory_item_id = p_inventory_item_id
96 AND mic.organization_id = p_inventory_organization_id;
97
98 CURSOR fisc_exceptions_non_inv(p_product_category VARCHAR2,
99 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 ) IS
105 SELECT tax_exception_id, exception_type_code, rate_modifier
106 FROM zx_sco_exceptions ex, zx_fc_types_b fct, zx_fc_types_reg_assoc fcreg, zx_fc_codes_b fcc
107 WHERE ex.tax_regime_code = p_tax_regime_code
108 AND (ex.tax = p_tax or ex.tax is null)
109 AND (ex.tax_status_code = p_tax_status_code or ex.tax_status_code is null)
110 AND (ex.tax_rate_code = p_tax_rate_code or ex.tax_rate_code is null)
111 AND (ex.tax_jurisdiction_id = p_tax_jurisdiction_id or ex.tax_jurisdiction_id is null)
112 AND ex.classification_type_code = fct.classification_type_code
113 AND ex.classification_code = fcc.classification_code
114 AND ex.duplicate_exception = 0
115 AND ex.effective_from <= p_trx_date
116 AND (ex.effective_to >= p_trx_date or ex.effective_to is null)
117 AND fct.classification_type_categ_code = 'PRODUCT_GENERIC_CLASSIFICATION'
118 AND fct.classification_type_code = fcreg.classification_type_code
119 AND fcreg.tax_regime_code = p_tax_regime_code
120 AND fcreg.use_in_item_exceptions_flag = 'Y'
121 AND fct.classification_type_code = fcc.classification_type_code
122 AND p_trx_date BETWEEN fcreg.effective_from AND fcreg.effective_to
123 AND fcc.classification_code = p_product_category;
124
125 l_tax_jurisdiction_id NUMBER;
126
127 l_status VARCHAR2(1);
128 l_db_status VARCHAR2(1);
129 l_api_name CONSTANT VARCHAR2(30):= 'GET_TAX_EXCEPTIONS';
130 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
131
132 BEGIN
133 -- Get exception for inventory item;
134 -- If not found
135 -- find the fiscal classification type for the tax regime that is used to define exceptions;
136 -- Check if the inventory item passed belongs to the Fiscal classification type
137 -- find out the fiscal classification code(s).
138 -- check if an exception exists for that fiscal classification type and code(s)
139 -- If found pass it back to TDM
140 -- End if;
141 -- End if;
142 -- End if;
143 ----- Add logic to use zx_jurisdictions_gt table
144 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
145 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()+');
146 END IF;
147
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
151
152 l_log_msg := 'Parameters '||
153 ' p_inventory_item_id: '||to_char(p_inventory_item_id)||
154 ' p_inventory_organization_id: '||p_inventory_organization_id||
155 ' p_product_category: '||to_char(p_product_category)||
156 ' p_tax_regime_code: '||to_char(p_tax_regime_code)||
157 ' p_tax : '||to_char(p_tax)||
158 ' p_tax_status_code: '||to_char(p_tax_status_code)||
159 ' p_tax_rate_code : '||to_char(p_tax_rate_code)||
160 ' p_tax_jurisdiction_id: '||to_char(p_tax_jurisdiction_id)||
161 ' p_multiple_jurisdictions_flag: '||to_char(p_multiple_jurisdictions_flag)||' ';
162
163 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name, l_log_msg);
164 END IF;
165 OPEN item_exceptions(p_inventory_item_id,
166 p_inventory_organization_id,
167 p_tax_regime_code,
168 p_tax,
169 p_tax_status_code,
170 p_tax_rate_code,
171 p_tax_jurisdiction_id);
172 LOOP
173 FETCH item_exceptions INTO x_exception_rec.tax_exception_id, x_exception_rec.exception_type_code,
174 x_exception_rec.exception_rate;
175 EXIT WHEN item_exceptions%NOTFOUND;
176 IF x_exception_rec.tax_exception_id IS NOT NULL THEN
177 EXIT;
178 END IF;
179
180 END LOOP;
181 CLOSE item_exceptions;
182 --
183 IF x_exception_rec.tax_exception_id IS NULL THEN
184 BEGIN
185 SELECT STATUS, DB_STATUS
186 INTO l_status, l_db_status
187 FROM fnd_product_installations
188 WHERE APPLICATION_ID = '401';
189 EXCEPTION WHEN OTHERS THEN
190 NULL;
191
192 END;
193
194
195 IF (nvl(l_status,'N') = 'I' or nvl(l_db_status,'N') = 'I') THEN --Bug 5383505
196 OPEN fisc_exceptions( p_tax_regime_code,
197 p_tax,
198 p_tax_status_code,
199 p_tax_rate_code,
200 p_tax_jurisdiction_id,
201 p_inventory_item_id,
202 p_inventory_organization_id);
203 LOOP
204 FETCH fisc_exceptions INTO x_exception_rec.tax_exception_id, x_exception_rec.exception_type_code,
205 x_exception_rec.exception_rate;
206 EXIT WHEN fisc_exceptions%NOTFOUND;
207 IF x_exception_rec.tax_exception_id IS NOT NULL THEN
208 EXIT;
209 END IF;
210 END LOOP;
211 CLOSE fisc_exceptions;
212
213 IF x_exception_rec.tax_exception_id IS NULL THEN
214 -- Exception not found;
215 null;
216 END IF;
217 ELSE --l inventory is not installed then
218 OPEN fisc_exceptions_non_inv(p_product_category,
219 p_tax_regime_code,
220 p_tax,
221 p_tax_status_code,
222 p_tax_rate_code,
223 p_tax_jurisdiction_id);
224 LOOP
225 FETCH fisc_exceptions_non_inv INTO x_exception_rec.tax_exception_id, x_exception_rec.exception_type_code, x_exception_rec.exception_rate;
226 EXIT WHEN fisc_exceptions_non_inv%NOTFOUND;
227 IF x_exception_rec.tax_exception_id IS NOT NULL THEN
228 EXIT;
229 END IF;
230 END LOOP;
231 CLOSE fisc_exceptions_non_inv;
232
233 IF x_exception_rec.tax_exception_id IS NULL THEN
234 -- Exception not found;
235 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
236
237 l_log_msg := 'No exception found ';
238
239 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_api_name, l_log_msg);
240 END IF;
241 END IF;
242 END IF;
243
244 END IF;
245 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
246 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'()-');
247 END IF;
248 END;
249 END;
[Home] [Help]