[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_GET_EXEMPT_PKG
Source
1 PACKAGE BODY ZX_TCM_GET_EXEMPT_PKG AS
2 /* $Header: zxcgetexemptb.pls 120.26.12010000.3 2008/12/06 17:49:19 ssanka ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL NUMBER;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_TCM_GET_EXEMPT_PKG';
12 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
13 procedure period_date_range( p_tax_date in date,
14 p_ledger_id IN NUMBER,
15 start_date out NOCOPY date,
16 end_date out NOCOPY date ) is
17
18 cursor sel_date( p_tax_date in date ) is
19 select p.start_date, p.end_date
20 from gl_period_statuses p, gl_sets_of_books g
21 where p.application_id = 222
22 and p.set_of_books_id = p_ledger_id
23 and trunc(p_tax_date) between p.start_date and p.end_date
24 and g.set_of_books_id = p.set_of_books_id
25 and g.accounted_period_type = p.period_type;
26
27
28 begin
29
30 open sel_date( p_tax_date );
31 fetch sel_date into start_date, end_date;
32 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
33 l_log_msg := 'start_date '||to_char(start_date);
34 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions.period_date_range', l_log_msg);
35 END IF;
36
37
38 if sel_date%notfound
39 then
40 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
41 l_log_msg := 'No Data found';
42 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions.period_date_range', l_log_msg);
43 END IF;
44
45 close sel_date;
46
47 end if;
48 if sel_date%isopen then
49 close sel_date;
50 end if;
51
52 end;
53
54 PROCEDURE get_exemptions(
55 p_ptp_id IN NUMBER,
56 p_cust_account_id IN NUMBER,
57 p_site_use_id IN NUMBER,
58 p_inventory_item_id IN NUMBER,
59 p_tax_date IN DATE,
60 p_exempt_certificate_number IN VARCHAR2,
61 p_reason_code IN VARCHAR2,
62 p_exempt_control_flag IN VARCHAR2,
63 p_tax_regime_code IN VARCHAR2,
64 p_tax IN VARCHAR2,
65 p_tax_status_code IN VARCHAR2,
66 p_tax_rate_code IN VARCHAR2,
67 p_tax_jurisdiction_id IN NUMBER,
68 x_exemption_rec OUT NOCOPY zx_tcm_get_exempt_pkg.exemption_rec_type) IS
69
70 CURSOR exemptions(p_ptp_id NUMBER,
71 p_cust_account_id IN NUMBER,
72 p_site_use_id IN NUMBER,
73 p_inventory_item_id NUMBER,
74 p_tax_date DATE,
75 p_exempt_certificate_number VARCHAR2,
76 p_reason_code VARCHAR2,
77 p_exempt_control_flag VARCHAR2,
78 p_tax_regime_code VARCHAR2,
79 p_tax VARCHAR2,
80 p_tax_status_code VARCHAR2,
81 p_tax_rate_code VARCHAR2,
82 p_tax_jurisdiction_id NUMBER
83 ) IS
84 SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
85 decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
86 decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
87 tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
88 FROM zx_exemptions
89 WHERE party_tax_profile_id = p_ptp_id
90 AND ((p_exempt_control_flag = 'S' and exemption_status_code = 'PRIMARY' )
91 OR ( p_exempt_control_flag = 'E'
92 AND exemption_status_code IN ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' )
93 AND exempt_reason_code = p_reason_code
94 AND ( (rtrim(ltrim(exempt_certificate_number)) = p_exempt_certificate_number)
95 or (exempt_certificate_number IS NULL AND
96 p_exempt_certificate_number IS NULL)) ))
97 AND duplicate_exemption = 0
98 AND tax_regime_code = p_tax_regime_code
99 AND (cust_account_id is null or cust_account_id = p_cust_account_id)
100 AND (site_use_id is null or site_use_id = p_site_use_id)
101 AND (tax is null or tax = p_tax)
102 AND (tax_status_code is null or tax_status_code = p_tax_status_code)
103 AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
104 AND (tax_jurisdiction_id is null or tax_jurisdiction_id = p_tax_jurisdiction_id)
105 AND (product_id is null or product_id = p_inventory_item_id)
106 AND effective_from <= p_tax_date
107 AND (effective_to >= p_tax_date or effective_to is null)
108 order by select_order2,
109 select_order1,
110 tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST, tax_status_code NULLS LAST, tax NULLS LAST;
111
112 l_tax_exmpt_cr_method_code VARCHAR2(30);
113 l_tax_exmpt_source_tax VARCHAR2(30);
114 l_order_by1 NUMBER;
115 l_order_by2 NUMBER;
116 l_source_tax_jurisdiction_id NUMBER;
117 l_tax_rate_code VARCHAR2(50);
118 l_tax_status_code VARCHAR2(30);
119 l_tax VARCHAR2(30);
120 l_tax_jurisdiction_id NUMBER;
121 l_tax_rec ZX_TDS_UTILITIES_PKG.zx_tax_info_cache_rec;
122 l_return_status VARCHAR2(30);
123 l_error_buffer VARCHAR2(240);
124 l_exists VARCHAR2(10);
125 BEGIN
126
127 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
128 l_log_msg := 'Get Exemptions';
129 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
130 END IF;
131 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
132 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions',
133 'p_ptp_id '|| to_char(p_ptp_id) || ' '||
134 'p_cust_account_id '|| to_char(p_cust_account_id) || ' '||
135 'p_site_use_id '|| to_char(p_site_use_id) || ' '||
136 'p_inventory_item_id '|| to_char(p_inventory_item_id)|| ' '||
137 'p_tax_date ' || to_char(p_tax_date)|| ' '||
138 'p_exempt_certificate_number '|| p_exempt_certificate_number|| ' '||
139 'p_reason_code ' || p_reason_code|| ' '||
140 'p_exempt_control_flag '|| p_exempt_control_flag|| ' '||
141 'p_tax_regime_code ' || p_tax_regime_code|| ' '||
142 'p_tax' || p_tax|| ' '||
143 'p_tax_status_code '|| p_tax_status_code|| ' '||
144 'p_tax_rate_code '|| p_tax_rate_code|| ' '||
145 'p_tax_jurisdiction_id '|| to_char(p_tax_jurisdiction_id));
146 END IF;
147
148 OPEN exemptions(p_ptp_id,
149 p_cust_account_id,
150 p_site_use_id,
151 p_inventory_item_id,
152 p_tax_date,
153 p_exempt_certificate_number,
154 p_reason_code,
155 p_exempt_control_flag,
156 p_tax_regime_code,
157 p_tax,
158 p_tax_status_code,
159 p_tax_rate_code,
160 p_tax_jurisdiction_id);
161
162 LOOP
163 FETCH exemptions INTO x_exemption_rec.exemption_id, x_exemption_rec.discount_special_rate,
164 x_exemption_rec.percent_exempt, x_exemption_rec.apply_to_lower_levels_flag,
165 l_order_by1, l_order_by2, l_tax_rate_code, l_tax_jurisdiction_id, l_tax_status_code, l_tax , x_exemption_rec.exempt_reason_code, x_exemption_rec.exempt_certificate_number;
166 EXIT WHEN exemptions%NOTFOUND;
167 IF x_exemption_rec.exemption_id IS NOT NULL THEN
168 IF x_exemption_rec.exempt_reason_code IS NOT NULL THEN
169 BEGIN
170 SELECT meaning
171 INTO x_exemption_rec.exempt_reason
172 from FND_LOOKUPS
173 where lookup_type = 'ZX_EXEMPTION_REASON_CODE'
174 and lookup_code = x_exemption_rec.exempt_reason_code;
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
178 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME,
179 'There is no exempt reason lookup type for this exempt reason.');
180 END IF;
181 RAISE FND_API.G_EXC_ERROR;
182 WHEN OTHERS THEN
183 IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
184 FND_LOG.STRING(G_LEVEL_UNEXPECTED,G_MODULE_NAME,
185 'Failed when trying to get the meaning for Reason Code due to '||SQLERRM);
186 END IF;
187 RAISE FND_API.G_EXC_ERROR;
188 END;
189 END IF;
190 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
191 l_log_msg := 'Tax Exemptions Id '||to_char(x_exemption_rec.exemption_id) || ' Percent Exempt '||to_char(x_exemption_rec.percent_exempt);
192 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
193 END IF;
194 EXIT;
195
196 END IF;
197
198 END LOOP;
199 CLOSE exemptions;
200
201 IF x_exemption_rec.exemption_id IS NULL AND p_tax IS NOT NULL THEN
202
203
204 ZX_TDS_UTILITIES_PKG.get_tax_cache_info (
205 p_tax_regime_code => p_tax_regime_code,
206 p_tax => p_tax,
207 p_tax_determine_date => p_tax_date,
208 x_tax_rec => l_tax_rec,
209 p_return_status => l_return_status,
210 p_error_buffer => l_error_buffer);
211
212 l_tax_exmpt_cr_method_code := l_tax_rec.tax_exmpt_cr_method_code;
213 l_tax_exmpt_source_tax := l_tax_rec.tax_exmpt_source_tax;
214
215 /* Use cache
216 SELECT tax_exmpt_cr_method_code, tax_exmpt_source_tax
217 INTO l_tax_exmpt_cr_method_code, l_tax_exmpt_source_tax
218 FROM zx_sco_taxes
219 WHERE tax_regime_code = p_tax_regime_code
220 AND tax = p_tax;
221 */
222
223
224 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
225 l_log_msg := 'Get Exemptions Source Tax';
226 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
227 END IF;
228
229 IF l_tax_exmpt_cr_method_code = 'USE_EXEMPTIONS' THEN
230 IF l_tax_exmpt_source_tax IS NOT NULL THEN
231 IF p_tax_jurisdiction_id IS NOT NULL THEN
232 SELECT TAX_EXMPT_SRC_JURISDICT_ID
233 INTO l_source_tax_jurisdiction_id
234 FROM zx_jurisdictions_b
235 WHERE tax_jurisdiction_id = p_tax_jurisdiction_id
236 AND effective_from <= p_tax_date
237 AND (effective_to >= p_tax_date or effective_to is null);
238 END IF;
239
240 OPEN exemptions(p_ptp_id,
241 p_cust_account_id,
242 p_site_use_id,
243 p_inventory_item_id,
244 p_tax_date,
245 p_exempt_certificate_number,
246 p_reason_code,
247 p_exempt_control_flag,
248 p_tax_regime_code,
249 l_tax_exmpt_source_tax,
250 p_tax_status_code,
251 p_tax_rate_code,
252 l_source_tax_jurisdiction_id);
253
254 LOOP
255 FETCH exemptions INTO x_exemption_rec.exemption_id, x_exemption_rec.discount_special_rate,
256 x_exemption_rec.percent_exempt, x_exemption_rec.apply_to_lower_levels_flag,
257 l_order_by1, l_order_by2,l_tax_rate_code, l_tax_jurisdiction_id, l_tax_status_code, l_tax, x_exemption_rec.exempt_reason_code, x_exemption_rec.exempt_certificate_number;
258 EXIT WHEN exemptions%NOTFOUND;
259
260 IF x_exemption_rec.exemption_id IS NOT NULL THEN
261 IF x_exemption_rec.apply_to_lower_levels_flag = 'Y' THEN
262 EXIT;
263 ELSE
264 x_exemption_rec.exemption_id := null;
265 x_exemption_rec.discount_special_rate := null;
266 x_exemption_rec.percent_exempt := null;
267 x_exemption_rec.apply_to_lower_levels_flag := null;
268
269 END IF;
270 END IF;
271
272 END LOOP;
273 CLOSE exemptions;
274 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
275 l_log_msg := 'Tax Exemptions Id '||to_char(x_exemption_rec.exemption_id) || ' Percent Exempt '||to_char(x_exemption_rec.percent_exempt);
276 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
277 END IF;
278 END IF;
279 END IF;
280 END IF;
281 END;
282
283 PROCEDURE get_tax_exemptions (p_bill_to_cust_site_use_id IN NUMBER,
284 p_bill_to_cust_acct_id IN NUMBER,
285 p_bill_to_party_site_ptp_id IN NUMBER,
286 p_bill_to_party_ptp_id IN NUMBER,
287 p_sold_to_party_site_ptp_id IN NUMBER,
288 p_sold_to_party_ptp_id IN NUMBER,
289 p_inventory_org_id IN NUMBER,
290 p_inventory_item_id IN NUMBER,
291 p_exempt_certificate_number IN VARCHAR2,
292 p_reason_code IN VARCHAR2,
293 p_exempt_control_flag IN VARCHAR2,
294 p_tax_date IN DATE,
295 p_tax_regime_code IN VARCHAR2,
296 p_tax IN VARCHAR2,
297 p_tax_status_code IN VARCHAR2,
298 p_tax_rate_code IN VARCHAR2,
299 p_tax_jurisdiction_id IN NUMBER,
300 p_multiple_jurisdictions_flag IN VARCHAR2,
301 p_event_class_rec IN zx_api_pub.event_class_rec_type,
302 x_return_status OUT NOCOPY VARCHAR2,
303 x_exemption_rec OUT NOCOPY exemption_rec_type) IS
304 l_tax_jurisdiction_code VARCHAR2(30);
305 l_ledger_id NUMBER;
306 l_start_date DATE;
307 l_end_date DATE;
308 l_exists VARCHAR2(1);
309 i BINARY_INTEGER;
310 n BINARY_INTEGER;
311 l_tax_jurisdiction_id NUMBER;
312 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
313 l_tax_jurisdiction_id_tbl NUMBER_TBL_TYPE;
314 l_precedence_level_tbl NUMBER_TBL_TYPE;
315 -----l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
316 BEGIN
317 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
318 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
319 l_log_msg := 'Get Tax Exemptions ' ||
320 'p_bill_to_cust_site_use_id= '|| p_bill_to_cust_site_use_id ||
321 'p_bill_to_cust_acct_id= '|| p_bill_to_cust_acct_id ||
322 'p_bill_to_party_site_ptp_id= '|| p_bill_to_party_site_ptp_id||
323 'p_bill_to_party_ptp_id= '|| p_bill_to_party_ptp_id ||
324 'p_sold_to_party_site_ptp_id= '|| p_sold_to_party_site_ptp_id||
325 'p_sold_to_party_ptp_id= '|| p_sold_to_party_ptp_id ||
326 'p_inventory_org_id= '|| p_inventory_org_id ||
327 'p_inventory_item_id= '|| p_inventory_item_id ||
328 'p_exempt_certificate_number= '|| p_exempt_certificate_number||
329 'p_reason_code= '|| p_reason_code ||
330 'p_exempt_control_flag= '|| p_exempt_control_flag ||
331 'p_tax_date= '|| p_tax_date ||
332 'p_tax_regime_code= '|| p_tax_regime_code ||
333 'p_tax= '|| p_tax ||
334 'p_tax_status_code= '|| p_tax_status_code ||
335 'p_tax_rate_code= '|| p_tax_rate_code ||
336 'p_tax_jurisdiction_id= '|| p_tax_jurisdiction_id ||
337 'p_multiple_jurisdictions_flag= '||p_multiple_jurisdictions_flag ||
338 'p_event_class_rec.ledger_id= '||p_event_class_rec.ledger_id
339 ;
340 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
341 END IF;
342
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344 IF p_exempt_control_flag = 'E' and p_reason_code is null THEN
345 x_return_status := FND_API.G_RET_STS_ERROR;
346 FND_MESSAGE.SET_NAME('ZX', 'ZX_TCM_NO_EXEMPT_REASON');
347 RAISE FND_API.G_EXC_ERROR;
348
349 END IF;
350
351 IF nvl(p_multiple_jurisdictions_flag,'N') = 'N' THEN
352 l_tax_jurisdiction_id_tbl(1) := p_tax_jurisdiction_id;
353 ELSE
354 SELECT tax_jurisdiction_id, precedence_level
355 BULK COLLECT INTO l_tax_jurisdiction_id_tbl, l_precedence_level_tbl
356 FROM zx_jurisdictions_gt
357 ORDER BY precedence_level;
358 END IF;
359
360 FOR i in l_tax_jurisdiction_id_tbl.first..l_tax_jurisdiction_id_tbl.last LOOP
361 IF nvl(p_event_class_rec.EXMPTN_PTY_BASIS_HIER_1_CODE,'BILL_TO') = 'BILL_TO' THEN
362 -- call ptp_based_exemptions with p_bill_to_party_site_ptp_id,
363 get_exemptions( p_bill_to_party_site_ptp_id,
364 p_bill_to_cust_acct_id,
365 p_bill_to_cust_site_use_id,
366 p_inventory_item_id,
367 p_tax_date,
368 p_exempt_certificate_number,
369 p_reason_code,
370 p_exempt_control_flag,
371 p_tax_regime_code,
372 p_tax,
373 p_tax_status_code,
374 p_tax_rate_code,
375 l_tax_jurisdiction_id_tbl(i),
376 x_exemption_rec);
377 IF x_exemption_rec.exemption_id IS NULL THEN
378 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
379 l_log_msg := 'Calling get_exemptions with p_bill_to_party_ptp_id';
380 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
381 END IF;
382 -- call ptp_based_exemptions with p_bill_to_party_ptp_id,
383 get_exemptions( p_bill_to_party_ptp_id,
384 p_bill_to_cust_acct_id,
385 p_bill_to_cust_site_use_id,
386 p_inventory_item_id,
387 p_tax_date,
388 p_exempt_certificate_number,
389 p_reason_code,
390 p_exempt_control_flag,
391 p_tax_regime_code,
392 p_tax,
393 p_tax_status_code,
394 p_tax_rate_code,
395 l_tax_jurisdiction_id_tbl(i),
396 x_exemption_rec);
397
398 END IF;
399
400 --
401 ELSIF p_event_class_rec.EXMPTN_PTY_BASIS_HIER_1_CODE = 'SOLD_TO' THEN
402 -- call ptp_based_exemptions with p_sold_to_party_site_ptp_id,
403 get_exemptions( p_sold_to_party_site_ptp_id,
404 p_bill_to_cust_acct_id,
405 p_bill_to_cust_site_use_id,
406 p_inventory_item_id,
407 p_tax_date,
408 p_exempt_certificate_number,
409 p_reason_code,
410 p_exempt_control_flag,
411 p_tax_regime_code,
412 p_tax,
413 p_tax_status_code,
414 p_tax_rate_code,
415 l_tax_jurisdiction_id_tbl(i),
416 x_exemption_rec);
417 IF x_exemption_rec.exemption_id IS NULL THEN
418 -- call ptp_based_exemptions with p_sold_to_party_ptp_id,
419 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
420 l_log_msg := 'Calling get_exemptions with p_sold_to_party_ptp_id';
421 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
422 END IF;
423 get_exemptions( p_sold_to_party_ptp_id,
424 p_bill_to_cust_acct_id,
425 p_bill_to_cust_site_use_id,
426 p_inventory_item_id,
427 p_tax_date,
428 p_exempt_certificate_number,
429 p_reason_code,
430 p_exempt_control_flag,
431 p_tax_regime_code,
432 p_tax,
433 p_tax_status_code,
434 p_tax_rate_code,
435 l_tax_jurisdiction_id_tbl(i),
436 x_exemption_rec);
437
438 END IF;
439
440 END IF; -- hier_1_code check
441 --
442 IF x_exemption_rec.exemption_id IS NULL THEN
443 -- Need not add nvl for EXMPTN_PTY_BASIS_HIER_2_CODE
444 -- since get_exemptions has got executed with 'BILL_TO' earlier
445 IF p_event_class_rec.EXMPTN_PTY_BASIS_HIER_2_CODE = 'BILL_TO' THEN
446
447 -- call ptp_based_exemptions with p_bill_to_party_site_ptp_id,
448 get_exemptions( p_bill_to_party_site_ptp_id,
449 p_bill_to_cust_acct_id,
450 p_bill_to_cust_site_use_id,
451 p_inventory_item_id,
452 p_tax_date,
453 p_exempt_certificate_number,
454 p_reason_code,
455 p_exempt_control_flag,
456 p_tax_regime_code,
457 p_tax,
458 p_tax_status_code,
459 p_tax_rate_code,
460 l_tax_jurisdiction_id_tbl(i),
461 x_exemption_rec);
462 IF x_exemption_rec.exemption_id IS NULL THEN
463 -- call ptp_based_exemptions with p_bill_to_party_ptp_id,
464 get_exemptions( p_bill_to_party_ptp_id,
465 p_bill_to_cust_acct_id,
466 p_bill_to_cust_site_use_id,
467 p_inventory_item_id,
468 p_tax_date,
469 p_exempt_certificate_number,
470 p_reason_code,
471 p_exempt_control_flag,
472 p_tax_regime_code,
473 p_tax,
474 p_tax_status_code,
475 p_tax_rate_code,
476 l_tax_jurisdiction_id_tbl(i),
477 x_exemption_rec);
478
479 END IF;
480
481 --
482 ELSIF p_event_class_rec.EXMPTN_PTY_BASIS_HIER_2_CODE = 'SOLD_TO' THEN
483 get_exemptions( p_sold_to_party_site_ptp_id,
484 p_bill_to_cust_acct_id,
485 p_bill_to_cust_site_use_id,
486 p_inventory_item_id,
487 p_tax_date,
488 p_exempt_certificate_number,
489 p_reason_code,
490 p_exempt_control_flag,
491 p_tax_regime_code,
492 p_tax,
493 p_tax_status_code,
494 p_tax_rate_code,
495 l_tax_jurisdiction_id_tbl(i),
496 x_exemption_rec);
497 IF x_exemption_rec.exemption_id IS NULL THEN
498 -- call ptp_based_exemptions with p_sold_to_party_ptp_id,
499 get_exemptions( p_sold_to_party_ptp_id,
500 p_bill_to_cust_acct_id,
501 p_bill_to_cust_site_use_id,
502 p_inventory_item_id,
503 p_tax_date,
504 p_exempt_certificate_number,
505 p_reason_code,
506 p_exempt_control_flag,
507 p_tax_regime_code,
508 p_tax,
509 p_tax_status_code,
510 p_tax_rate_code,
511 l_tax_jurisdiction_id_tbl(i),
512 x_exemption_rec);
513
514 END IF;
515
516 END IF; -- hier_2_code check
517
518 END IF; -- exemption id null check
519 IF x_exemption_rec.exemption_id IS NOT NULL THEN
520 EXIT;
521 END IF;
522
523 END LOOP;
524
525
526 IF x_exemption_rec.exemption_id IS NULL THEN
527 IF p_exempt_control_flag = 'E' THEN
528
529 IF p_event_class_rec.ledger_id is null THEN
530 IF zx_global_structures_pkg.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID.exists(1) THEN
531 SELECT set_of_books_id
532 INTO l_ledger_id
533 FROM ar_system_parameters_all
534 WHERE org_id = zx_global_structures_pkg.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(1);
535 ELSIF p_event_class_rec.internal_organization_id is not null THEN
536 SELECT set_of_books_id
537 INTO l_ledger_id
538 FROM ar_system_parameters_all
539 WHERE org_id = p_event_class_rec.internal_organization_id;
540 END IF;
541
542 END IF;
543 period_date_range(p_tax_date,
544 nvl(p_event_class_rec.ledger_id, l_ledger_id),
545 l_start_date,
546 l_end_date);
547 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
548 l_log_msg := 'l_start_date '||to_char(l_start_date);
549 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
550 END IF;
551
552
553 n := l_tax_jurisdiction_id_tbl.last;
554 l_tax_jurisdiction_id := l_tax_jurisdiction_id_tbl(n);
555
556 Begin
557 select 'Y' into l_exists
558 from
559 zx_exemptions
560 where nvl(tax_rate_code,'X') = nvl(p_tax_rate_code,'X') and
561 effective_from = nvl(l_start_date, trunc(sysdate))
562 and nvl(exempt_certificate_number,'X') = nvl(p_exempt_certificate_number,'X')
563 and exempt_reason_code = p_reason_code
564 and party_tax_profile_id = p_bill_to_party_ptp_id
565 and tax_regime_code = p_tax_regime_code
566 and content_owner_id =
567 nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
568 and nvl(tax_status_code,'X') = nvl(p_tax_status_code,'X')
569 and nvl(tax,'X') = nvl(p_tax,'X')
570 and nvl(tax_jurisdiction_id,-999) = nvl(l_tax_jurisdiction_id,-999)
571 and exemption_status_code = 'UNAPPROVED';
572
573 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
574 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', 'Unapproved exemption exists');
575 END IF;
576
577 exception
578 when no_data_found THEN
579 l_exists := 'N';
580 when others then
581 l_exists := 'N';
582 end;
583
584 IF l_exists = 'N' THEN
585 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
586 l_log_msg := 'Create Unapproved Exemption';
587 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions',
588 l_log_msg);
589 END IF;
590
591 SELECT zx_exemptions_s.nextval
592 INTO x_exemption_rec.exemption_id
593 FROM dual;
594 x_exemption_rec.discount_special_rate := 'DISCOUNT';
595 x_exemption_rec.percent_exempt := 100;
596 x_exemption_rec.apply_to_lower_levels_flag := 'Y';
597 x_exemption_rec.exempt_reason_code := p_reason_code;
598 x_exemption_rec.exempt_certificate_number := p_exempt_certificate_number;
599
600 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
601 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions',
602 'Exemption Id '|| to_char(x_exemption_rec.exemption_id) || ' '||
603 'Tax Regime Code '|| p_TAX_REGIME_CODE || ' '||
604 'Tax '|| p_TAX || ' '||
605 'Tax Status Code ' ||p_TAX_STATUS_CODE || ' ' ||
606 'Tax Rate Code '||p_tax_rate_code || ' ' ||
607 'Content owner id '||to_char(nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)) || ' ' ||
608 'Exemption Certificate Number '|| p_exempt_certificate_number || ' ' ||
609 'Reason Code '|| p_REASON_CODE || ' ' ||
610 'Start Date '|| to_char(nvl(l_start_date, trunc(sysdate))) || ' ' ||
611 'Bill to party ptp id '|| to_char(p_bill_to_party_ptp_id) || ' ' ||
612 'Tax Jurisdiction id '|| to_char(l_tax_jurisdiction_id));
613 END IF;
614
615 INSERT INTO ZX_EXEMPTIONS(
616 TAX_EXEMPTION_ID,
617 EXEMPTION_TYPE_CODE,
618 EXEMPTION_STATUS_CODE,
619 TAX_REGIME_CODE,
620 TAX,
621 TAX_STATUS_CODE,
622 tax_rate_code,
623 CONTENT_OWNER_ID,
624 EXEMPT_CERTIFICATE_NUMBER,
625 EXEMPT_REASON_CODE,
626 EFFECTIVE_FROM,
627 PARTY_TAX_PROFILE_ID,
628 RATE_MODIFIER,
629 APPLY_TO_LOWER_LEVELS_FLAG,
630 TAX_JURISDICTION_ID,
631 cust_account_id,
632 site_use_id,
633 RECORD_TYPE_CODE,
634 CREATED_BY,
635 CREATION_DATE,
636 LAST_UPDATED_BY,
637 LAST_UPDATE_DATE,
638 LAST_UPDATE_LOGIN,
639 REQUEST_ID,
640 PROGRAM_APPLICATION_ID,
641 PROGRAM_ID,
642 --PROGRAM_LOGIN_ID,
643 OBJECT_VERSION_NUMBER,
644 duplicate_exemption)
645 VALUES (
646 x_exemption_rec.exemption_id,
647 'DISCOUNT',
648 'UNAPPROVED',
649 p_TAX_REGIME_CODE,
650 p_TAX,
651 p_TAX_STATUS_CODE,
652 p_tax_rate_code,
653 nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID),
654 p_exempt_certificate_number,
655 p_REASON_CODE,
656 nvl(l_start_date, trunc(sysdate)),
657 p_bill_to_party_ptp_id,
658 100,
659 'Y',
660 l_tax_jurisdiction_id,
661 null,
662 null,
663 'USER_DEFINED',
664 fnd_global.user_id,
665 sysdate,
666 fnd_global.user_id,
667 sysdate,
668 fnd_global.conc_login_id,
669 null, -- request id
670 null, -- PROGRAM_APPLICATION_ID,
671 null, -- PROGRAM_ID,
672 --PROGRAM_LOGIN_ID,
673 1,
674 0);
675 END IF;
676 END IF;
677 END IF;
678
679 END;
680 END;