eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ZX_TCM_GET_EXEMPT_PKG

Source


1 PACKAGE BODY ZX_TCM_GET_EXEMPT_PKG AS
2 /* $Header: zxcgetexemptb.pls 120.31 2010/05/24 11:38:25 tsen 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                     p_event_class_rec     IN zx_api_pub.event_class_rec_type,
69                     x_exemption_rec       OUT NOCOPY zx_tcm_get_exempt_pkg.exemption_rec_type) IS
70 /*---  Bug 7893788  CURSOR exemptions  splitted into three cursor for proformance improvement. */
71 /*
72   CURSOR exemptions(p_ptp_id              NUMBER,
73                     p_cust_account_id     IN NUMBER,
74                     p_site_use_id         IN NUMBER,
75                     p_inventory_item_id   NUMBER,
76                     p_tax_date            DATE,
77                     p_exempt_certificate_number VARCHAR2,
78                     p_reason_code         VARCHAR2,
79                     p_exempt_control_flag VARCHAR2,
80                     p_tax_regime_code     VARCHAR2,
81                     p_tax                 VARCHAR2,
82                     p_tax_status_code     VARCHAR2,
83                     p_tax_rate_code       VARCHAR2,
84                     p_tax_jurisdiction_id NUMBER
85                      ) IS
86     SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
87            decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
88             decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
89             tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
90     FROM zx_exemptions
91     WHERE party_tax_profile_id = p_ptp_id
92     AND ((p_exempt_control_flag = 'S' and exemption_status_code = 'PRIMARY' )
93              OR ( p_exempt_control_flag = 'E'
94                   AND exemption_status_code IN ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' )
95                   AND exempt_reason_code = p_reason_code
96                   AND ( (rtrim(ltrim(exempt_certificate_number)) = p_exempt_certificate_number)
97                       or (exempt_certificate_number IS NULL AND
98                           p_exempt_certificate_number IS NULL))   ))
99     AND duplicate_exemption = 0
100     AND tax_regime_code = p_tax_regime_code
101     AND (cust_account_id is null or cust_account_id = p_cust_account_id)
102     AND (site_use_id is null or site_use_id = p_site_use_id)
103     AND (tax is null or tax = p_tax)
104     AND (tax_status_code is null or tax_status_code = p_tax_status_code)
105     AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
106     AND (tax_jurisdiction_id is null or tax_jurisdiction_id = p_tax_jurisdiction_id)
107     AND (product_id is null or product_id = p_inventory_item_id)
108     AND effective_from <= p_tax_date
109     AND (effective_to >= p_tax_date or effective_to is null)
110 order by select_order2,
111          select_order1,
112          tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST, tax_status_code NULLS LAST, tax NULLS LAST;
113 
114 
115 */
116 --bug 7893788
117   CURSOR exemptions1(p_ptp_id              NUMBER,
118                     p_cust_account_id     IN NUMBER,
119                     p_site_use_id         IN NUMBER,
120                     p_inventory_item_id   NUMBER,
121                     p_tax_date            DATE,
122                     p_exempt_certificate_number VARCHAR2,
123                     p_reason_code         VARCHAR2,
124                     p_exempt_control_flag VARCHAR2,
125                     p_tax_regime_code     VARCHAR2,
126                     p_tax                 VARCHAR2,
127                     p_tax_status_code     VARCHAR2,
128                     p_tax_rate_code       VARCHAR2,
129                     p_tax_jurisdiction_id NUMBER
130                      ) IS
131     SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
132            decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
133             decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
134             tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
135     FROM zx_exemptions
136      WHERE party_tax_profile_id = p_ptp_id
137      AND  exemption_status_code = 'PRIMARY'
138      AND duplicate_exemption = 0
139      AND tax_regime_code = p_tax_regime_code
140      AND (cust_account_id is null or cust_account_id = p_cust_account_id)
141      AND (site_use_id is null or site_use_id = p_site_use_id)
142      AND (tax is null or tax = p_tax)
143      AND (tax_status_code is null or tax_status_code = p_tax_status_code)
144      AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
145      AND (tax_jurisdiction_id is null or tax_jurisdiction_id = p_tax_jurisdiction_id)
146      AND (product_id is null or product_id = p_inventory_item_id)
147      AND effective_from <= p_tax_date
148      AND (effective_to >= p_tax_date or effective_to is null)
149      AND content_owner_id = nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
150      order by select_order1, tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST, tax_status_code NULLS LAST, tax NULLS LAST;
151 
152   CURSOR exemptions2(p_ptp_id              NUMBER,
153                     p_cust_account_id     IN NUMBER,
154                     p_site_use_id         IN NUMBER,
155                     p_inventory_item_id   NUMBER,
156                     p_tax_date            DATE,
157                     p_exempt_certificate_number VARCHAR2,
158                     p_reason_code         VARCHAR2,
159                     p_exempt_control_flag VARCHAR2,
160                     p_tax_regime_code     VARCHAR2,
161                     p_tax                 VARCHAR2,
162                     p_tax_status_code     VARCHAR2,
163                     p_tax_rate_code       VARCHAR2,
164                     p_tax_jurisdiction_id NUMBER
165                      ) IS
166     SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
167            decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
168             decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
169             tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
170     FROM zx_exemptions
171      WHERE party_tax_profile_id = p_ptp_id
172      AND  exempt_certificate_number IS NULL
173      AND duplicate_exemption = 0
174      AND tax_regime_code = p_tax_regime_code
175      AND (cust_account_id is null or cust_account_id = p_cust_account_id)
176      AND (site_use_id is null or site_use_id = p_site_use_id)
177      AND (tax is null or tax = p_tax)
178      AND (tax_status_code is null or tax_status_code = p_tax_status_code)
179      AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
180      AND (tax_jurisdiction_id is null or tax_jurisdiction_id = p_tax_jurisdiction_id)
181      AND (product_id is null or product_id = p_inventory_item_id)
182      AND effective_from <= p_tax_date
183      AND (effective_to >= p_tax_date or effective_to is null)
184      AND content_owner_id = nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
185  order by select_order2,
186           select_order1,
187           tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST,
188          tax_status_code NULLS LAST, tax NULLS LAST;
189 
190   CURSOR exemptions3(p_ptp_id              NUMBER,
191                     p_cust_account_id     IN NUMBER,
192                     p_site_use_id         IN NUMBER,
193                     p_inventory_item_id   NUMBER,
194                     p_tax_date            DATE,
195                     p_exempt_certificate_number VARCHAR2,
196                     p_reason_code         VARCHAR2,
197                     p_exempt_control_flag VARCHAR2,
198                     p_tax_regime_code     VARCHAR2,
199                     p_tax                 VARCHAR2,
200                     p_tax_status_code     VARCHAR2,
201                     p_tax_rate_code       VARCHAR2,
202                     p_tax_jurisdiction_id NUMBER
203                      ) IS
204     SELECT tax_exemption_id, exemption_type_code, rate_modifier, apply_to_lower_levels_flag,
205            decode(product_id, null, decode(site_use_id, null, decode(cust_account_id, null,4,3),2), 1) select_order1,
206             decode(exemption_status_code,'PRIMARY',1,'MANUAL',2,'UNAPPROVED',3) select_order2,
207             tax_rate_code, tax_jurisdiction_id, tax_status_code, tax, exempt_reason_code, exempt_certificate_number
208     FROM zx_exemptions
209      WHERE party_tax_profile_id = p_ptp_id
210      AND exemption_status_code IN ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' )
211      AND exempt_reason_code = p_reason_code
212      AND rtrim(ltrim(exempt_certificate_number)) = p_exempt_certificate_number
213      AND duplicate_exemption = 0
214      AND tax_regime_code = p_tax_regime_code
215      AND (cust_account_id is null or cust_account_id = p_cust_account_id)
216      AND (site_use_id is null or site_use_id = p_site_use_id)
217      AND (tax is null or tax = p_tax)
218      AND (tax_status_code is null or tax_status_code = p_tax_status_code)
219      AND (tax_rate_code is null or tax_rate_code = p_tax_rate_code)
220      AND (tax_jurisdiction_id is null or tax_jurisdiction_id =  p_tax_jurisdiction_id)
221      AND (product_id is null or product_id = p_inventory_item_id)
222      AND effective_from <= p_tax_date
223      AND (effective_to >= p_tax_date or effective_to is null)
224      AND content_owner_id = nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
225  order by select_order2,
226           select_order1,
227           tax_rate_code NULLS LAST, tax_jurisdiction_id NULLS LAST,
228           tax_status_code NULLS LAST, tax NULLS LAST;
229 
230 
231   l_tax_exmpt_cr_method_code    VARCHAR2(30);
232   l_tax_exmpt_source_tax        VARCHAR2(30);
233   l_order_by1                   NUMBER;
234   l_order_by2                   NUMBER;
235   l_source_tax_jurisdiction_id  NUMBER;
236   l_tax_rate_code               VARCHAR2(50);
237   l_tax_status_code             VARCHAR2(30);
238   l_tax                         VARCHAR2(30);
239   l_tax_jurisdiction_id         NUMBER;
240   l_tax_rec                     ZX_TDS_UTILITIES_PKG.zx_tax_info_cache_rec;
241   l_return_status               VARCHAR2(30);
242   l_error_buffer		VARCHAR2(240);
243   l_exists                      VARCHAR2(10);
244 BEGIN
245 
246   IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
247         l_log_msg := 'Get Exemptions';
248         FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
249     END IF;
250   IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
251        FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions',
252                 'p_ptp_id '|| to_char(p_ptp_id) || ' '||
253                 'p_cust_account_id '||  to_char(p_cust_account_id) || ' '||
254                'p_site_use_id '||  to_char(p_site_use_id)  || ' '||
255                'p_inventory_item_id '||  to_char(p_inventory_item_id)|| ' '||
256                'p_tax_date ' ||  to_char(p_tax_date)|| ' '||
257                'p_exempt_certificate_number '||  p_exempt_certificate_number|| ' '||
258                'p_reason_code ' || p_reason_code|| ' '||
259                'p_exempt_control_flag '||  p_exempt_control_flag|| ' '||
260                'p_tax_regime_code ' || p_tax_regime_code|| ' '||
261                'p_tax' ||  p_tax|| ' '||
262                'p_tax_status_code '||  p_tax_status_code|| ' '||
263                'p_tax_rate_code '||  p_tax_rate_code|| ' '||
264                'p_tax_jurisdiction_id '||  to_char(p_tax_jurisdiction_id));
265      END IF;
266 
267 /*---  Bug 7893788  CURSOR exemptions  splitted into three cursor for proformance improvement. */
268 If p_exempt_control_flag = 'S' then
269   OPEN exemptions1(p_ptp_id,
270                   p_cust_account_id,
271                   p_site_use_id,
272                   p_inventory_item_id,
273                   p_tax_date,
274                   p_exempt_certificate_number,
275                   p_reason_code,
276                   p_exempt_control_flag,
277                   p_tax_regime_code,
278                   p_tax,
279                   p_tax_status_code,
280                   p_tax_rate_code,
281                   p_tax_jurisdiction_id);
282 
283   LOOP
284     FETCH exemptions1 INTO x_exemption_rec.exemption_id, x_exemption_rec.discount_special_rate,
285                           x_exemption_rec.percent_exempt, x_exemption_rec.apply_to_lower_levels_flag,
286                           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;
287     EXIT WHEN exemptions1%NOTFOUND;
288     IF x_exemption_rec.exemption_id IS NOT NULL THEN
289       IF x_exemption_rec.exempt_reason_code IS NOT NULL THEN
290         BEGIN
291            SELECT meaning
292            INTO x_exemption_rec.exempt_reason
293            from FND_LOOKUPS
294            where  lookup_type = 'ZX_EXEMPTION_REASON_CODE'
295            and    lookup_code = x_exemption_rec.exempt_reason_code;
296         EXCEPTION
297            WHEN NO_DATA_FOUND THEN
298              IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
299                 FND_LOG.string(G_LEVEL_UNEXPECTED,G_MODULE_NAME,
300                                   'There is no exempt reason lookup type for this exempt reason.');
301              END IF;
302              RAISE FND_API.G_EXC_ERROR;
303              IF EXEMPTIONS1%ISOPEN THEN
304               CLOSE EXEMPTIONS1;
305              END IF;
306            WHEN OTHERS THEN
307              IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
308                 FND_LOG.string(G_LEVEL_UNEXPECTED,G_MODULE_NAME,
309                                   'Failed when trying to get the meaning for Reason Code due to '||SQLERRM);
310              END IF;
311              RAISE FND_API.G_EXC_ERROR;
312              IF EXEMPTIONS1%ISOPEN THEN
313               CLOSE EXEMPTIONS1;
314              END IF;
315         END;
316       END IF;
317       IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
318         l_log_msg := 'Tax Exemptions Id '||to_char(x_exemption_rec.exemption_id) || ' Percent Exempt '||to_char(x_exemption_rec.percent_exempt);
319         FND_LOG.string(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
320       END IF;
321       EXIT;
322 
323     END IF;
324 
325   END LOOP;
326   CLOSE exemptions1;
327 
328  elsif p_exempt_control_flag = 'E' then
329   if p_exempt_certificate_number IS NULL then
330   OPEN exemptions2(p_ptp_id,
331                   p_cust_account_id,
332                   p_site_use_id,
333                   p_inventory_item_id,
334                   p_tax_date,
335                   p_exempt_certificate_number,
336                   p_reason_code,
337                   p_exempt_control_flag,
338                   p_tax_regime_code,
339                   p_tax,
340                   p_tax_status_code,
341                   p_tax_rate_code,
342                   p_tax_jurisdiction_id);
343 
344   LOOP
345     FETCH exemptions2 INTO x_exemption_rec.exemption_id, x_exemption_rec.discount_special_rate,
346                           x_exemption_rec.percent_exempt, x_exemption_rec.apply_to_lower_levels_flag,
347                           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;
348     EXIT WHEN exemptions2%NOTFOUND;
349     IF x_exemption_rec.exemption_id IS NOT NULL THEN
350       IF x_exemption_rec.exempt_reason_code IS NOT NULL THEN
351         BEGIN
352            SELECT meaning
353            INTO x_exemption_rec.exempt_reason
354            from FND_LOOKUPS
355            where  lookup_type = 'ZX_EXEMPTION_REASON_CODE'
356            and    lookup_code = x_exemption_rec.exempt_reason_code;
357         EXCEPTION
358            WHEN NO_DATA_FOUND THEN
359              IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
360                 FND_LOG.string(G_LEVEL_UNEXPECTED,G_MODULE_NAME,
361                                   'There is no exempt reason lookup type for this exempt reason.');
362              END IF;
363              RAISE FND_API.G_EXC_ERROR;
364              IF EXEMPTIONS2%ISOPEN THEN
365               CLOSE EXEMPTIONS2;
366              END IF;
367            WHEN OTHERS THEN
368              IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
369                 FND_LOG.string(G_LEVEL_UNEXPECTED,G_MODULE_NAME,
370                                   'Failed when trying to get the meaning for Reason Code due to '||SQLERRM);
371              END IF;
372              RAISE FND_API.G_EXC_ERROR;
373              IF EXEMPTIONS2%ISOPEN THEN
374               CLOSE EXEMPTIONS2;
375              END IF;
376         END;
377       END IF;
378       IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
379         l_log_msg := 'Tax Exemptions Id '||to_char(x_exemption_rec.exemption_id) || ' Percent Exempt '||to_char(x_exemption_rec.percent_exempt);
380         FND_LOG.string(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
381       END IF;
382       EXIT;
383 
384     END IF;
385 
386   END LOOP;
387   CLOSE exemptions2;
388 
389   else
390        OPEN exemptions3(p_ptp_id,
391                   p_cust_account_id,
392                   p_site_use_id,
393                   p_inventory_item_id,
394                   p_tax_date,
395                   p_exempt_certificate_number,
396                   p_reason_code,
397                   p_exempt_control_flag,
398                   p_tax_regime_code,
399                   p_tax,
400                   p_tax_status_code,
401                   p_tax_rate_code,
402                   p_tax_jurisdiction_id);
403 
404   LOOP
405     FETCH exemptions3 INTO x_exemption_rec.exemption_id, x_exemption_rec.discount_special_rate,
406                           x_exemption_rec.percent_exempt, x_exemption_rec.apply_to_lower_levels_flag,
407                           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;
408     EXIT WHEN exemptions3%NOTFOUND;
409     IF x_exemption_rec.exemption_id IS NOT NULL THEN
410       IF x_exemption_rec.exempt_reason_code IS NOT NULL THEN
411         BEGIN
412            SELECT meaning
413            INTO x_exemption_rec.exempt_reason
414            from FND_LOOKUPS
415            where  lookup_type = 'ZX_EXEMPTION_REASON_CODE'
416            and    lookup_code = x_exemption_rec.exempt_reason_code;
417         EXCEPTION
418            WHEN NO_DATA_FOUND THEN
419              IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
420                 FND_LOG.string(G_LEVEL_UNEXPECTED,G_MODULE_NAME,
421                                   'There is no exempt reason lookup type for this exempt reason.');
422              END IF;
423              RAISE FND_API.G_EXC_ERROR;
424            WHEN OTHERS THEN
425              IF ( G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL) THEN
426                 FND_LOG.string(G_LEVEL_UNEXPECTED,G_MODULE_NAME,
427                                   'Failed when trying to get the meaning for Reason Code due to '||SQLERRM);
428              END IF;
429              RAISE FND_API.G_EXC_ERROR;
430              IF EXEMPTIONS3%ISOPEN THEN
431               CLOSE EXEMPTIONS3;
432              END IF;
433              IF EXEMPTIONS3%ISOPEN THEN
434               CLOSE EXEMPTIONS3;
435              END IF;
436         END;
437       END IF;
438       IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
439         l_log_msg := 'Tax Exemptions Id '||to_char(x_exemption_rec.exemption_id) || ' Percent Exempt '||to_char(x_exemption_rec.percent_exempt);
440         FND_LOG.string(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
441       END IF;
442       EXIT;
443 
444     END IF;
445 
446   END LOOP;
447   CLOSE exemptions3;
448 
449   end if;
450  end if;
451 
452   IF x_exemption_rec.exemption_id IS NULL AND p_tax IS NOT NULL THEN
453 
454 
455   ZX_TDS_UTILITIES_PKG.get_tax_cache_info (
456   p_tax_regime_code	=>  p_tax_regime_code,
457   p_tax                 =>  p_tax,
458   p_tax_determine_date	=>  p_tax_date,
459   x_tax_rec            	=>  l_tax_rec,
460   p_return_status      	=>  l_return_status,
461   p_error_buffer        =>  l_error_buffer);
462 
463   l_tax_exmpt_cr_method_code  := l_tax_rec.tax_exmpt_cr_method_code;
464   l_tax_exmpt_source_tax      := l_tax_rec.tax_exmpt_source_tax;
465 
466    /* Use cache
467     SELECT tax_exmpt_cr_method_code, tax_exmpt_source_tax
468     INTO   l_tax_exmpt_cr_method_code, l_tax_exmpt_source_tax
469     FROM   zx_sco_taxes
470     WHERE  tax_regime_code = p_tax_regime_code
471     AND    tax = p_tax;
472    */
473 
474 
475     IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
476         l_log_msg := 'Get Exemptions Source Tax';
477         FND_LOG.string(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
478     END IF;
479 
480     IF l_tax_exmpt_cr_method_code = 'USE_EXEMPTIONS' THEN
481       IF l_tax_exmpt_source_tax IS NOT NULL THEN
482         IF p_tax_jurisdiction_id IS NOT NULL THEN
483             SELECT TAX_EXMPT_SRC_JURISDICT_ID
484             INTO l_source_tax_jurisdiction_id
485             FROM zx_jurisdictions_b
486             WHERE tax_jurisdiction_id = p_tax_jurisdiction_id
487             AND effective_from <= p_tax_date
488             AND (effective_to >= p_tax_date or effective_to is null);
489         END IF;
490 
491 
492 If p_exempt_control_flag = 'S' then
493         OPEN exemptions1(p_ptp_id,
494                   p_cust_account_id,
495                   p_site_use_id,
496                   p_inventory_item_id,
497                   p_tax_date,
498                   p_exempt_certificate_number,
499                   p_reason_code,
500                   p_exempt_control_flag,
501                   p_tax_regime_code,
502                   l_tax_exmpt_source_tax,
503                   p_tax_status_code,
504                   p_tax_rate_code,
505                   l_source_tax_jurisdiction_id);
506 
507          LOOP
508            FETCH exemptions1 INTO x_exemption_rec.exemption_id, x_exemption_rec.discount_special_rate,
509                         x_exemption_rec.percent_exempt, x_exemption_rec.apply_to_lower_levels_flag,
510                         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;
511            EXIT WHEN exemptions1%NOTFOUND;
512 
513            IF x_exemption_rec.exemption_id IS NOT NULL THEN
514              IF x_exemption_rec.apply_to_lower_levels_flag = 'Y' THEN
515                EXIT;
516              ELSE
517                x_exemption_rec.exemption_id := null;
518                x_exemption_rec.discount_special_rate := null;
519                x_exemption_rec.percent_exempt := null;
520                x_exemption_rec.apply_to_lower_levels_flag := null;
521 
522              END IF;
523            END IF;
524 
525          END LOOP;
526          CLOSE exemptions1;
527 
528  elsif p_exempt_control_flag = 'E' then
529   if p_exempt_certificate_number IS NULL then
530         OPEN exemptions2(p_ptp_id,
531                   p_cust_account_id,
532                   p_site_use_id,
533                   p_inventory_item_id,
534                   p_tax_date,
535                   p_exempt_certificate_number,
536                   p_reason_code,
537                   p_exempt_control_flag,
538                   p_tax_regime_code,
539                   l_tax_exmpt_source_tax,
540                   p_tax_status_code,
541                   p_tax_rate_code,
542                   l_source_tax_jurisdiction_id);
543 
544          LOOP
545            FETCH exemptions2 INTO x_exemption_rec.exemption_id, x_exemption_rec.discount_special_rate,
546                         x_exemption_rec.percent_exempt, x_exemption_rec.apply_to_lower_levels_flag,
547                         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;
548            EXIT WHEN exemptions2%NOTFOUND;
549 
550            IF x_exemption_rec.exemption_id IS NOT NULL THEN
551              IF x_exemption_rec.apply_to_lower_levels_flag = 'Y' THEN
552                EXIT;
553              ELSE
554                x_exemption_rec.exemption_id := null;
555                x_exemption_rec.discount_special_rate := null;
556                x_exemption_rec.percent_exempt := null;
557                x_exemption_rec.apply_to_lower_levels_flag := null;
558 
559              END IF;
560            END IF;
561 
562          END LOOP;
563          CLOSE exemptions2;
564 
565   else
566         OPEN exemptions3(p_ptp_id,
567                   p_cust_account_id,
568                   p_site_use_id,
569                   p_inventory_item_id,
570                   p_tax_date,
571                   p_exempt_certificate_number,
572                   p_reason_code,
573                   p_exempt_control_flag,
574                   p_tax_regime_code,
575                   l_tax_exmpt_source_tax,
576                   p_tax_status_code,
577                   p_tax_rate_code,
578                   l_source_tax_jurisdiction_id);
579 
580          LOOP
581            FETCH exemptions3 INTO x_exemption_rec.exemption_id, x_exemption_rec.discount_special_rate,
582                         x_exemption_rec.percent_exempt, x_exemption_rec.apply_to_lower_levels_flag,
583                         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;
584            EXIT WHEN exemptions3%NOTFOUND;
585 
586            IF x_exemption_rec.exemption_id IS NOT NULL THEN
587              IF x_exemption_rec.apply_to_lower_levels_flag = 'Y' THEN
588                EXIT;
589              ELSE
590                x_exemption_rec.exemption_id := null;
591                x_exemption_rec.discount_special_rate := null;
592                x_exemption_rec.percent_exempt := null;
593                x_exemption_rec.apply_to_lower_levels_flag := null;
594 
595              END IF;
596            END IF;
597 
598          END LOOP;
599          CLOSE exemptions3;
600 
601   end if;
602  end if;
603 
604          IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
605            l_log_msg := 'Tax Exemptions Id '||to_char(x_exemption_rec.exemption_id) || ' Percent Exempt '||to_char(x_exemption_rec.percent_exempt);
606            FND_LOG.string(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
607          END IF;
608       END IF;
609     END IF;
610   END IF;
611 END;
612 
613 PROCEDURE get_tax_exemptions (p_bill_to_cust_site_use_id      IN NUMBER,
614                              p_bill_to_cust_acct_id          IN NUMBER,
615                              p_bill_to_party_site_ptp_id     IN NUMBER,
616                              p_bill_to_party_ptp_id          IN NUMBER,
617                              p_sold_to_party_site_ptp_id     IN NUMBER,
618                              p_sold_to_party_ptp_id          IN NUMBER,
619                              p_inventory_org_id              IN NUMBER,
620                              p_inventory_item_id             IN NUMBER,
621                              p_exempt_certificate_number     IN VARCHAR2,
622                              p_reason_code                   IN VARCHAR2,
623                              p_exempt_control_flag           IN VARCHAR2,
624                              p_tax_date                      IN DATE,
625                              p_tax_regime_code               IN VARCHAR2,
626                              p_tax                           IN VARCHAR2,
627                              p_tax_status_code               IN VARCHAR2,
628                              p_tax_rate_code                 IN VARCHAR2,
629                              p_tax_jurisdiction_id           IN NUMBER,
630                              p_multiple_jurisdictions_flag   IN VARCHAR2,
631                              p_event_class_rec               IN zx_api_pub.event_class_rec_type,
632                              x_return_status                 OUT NOCOPY VARCHAR2,
633                              x_exemption_rec                 OUT NOCOPY exemption_rec_type) IS
634   l_tax_jurisdiction_code VARCHAR2(30);
635   l_ledger_id   NUMBER;
636   l_start_date  DATE;
637   l_end_date    DATE;
638   l_exists      VARCHAR2(1);
639   i BINARY_INTEGER;
640   n BINARY_INTEGER;
641   l_tax_jurisdiction_id NUMBER;
642   TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
643   l_tax_jurisdiction_id_tbl NUMBER_TBL_TYPE;
644   l_precedence_level_tbl NUMBER_TBL_TYPE;
645   -----l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
646 BEGIN
647 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
648  IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
649    l_log_msg := 'Get Tax Exemptions ' ||
650                 'p_bill_to_cust_site_use_id= '|| p_bill_to_cust_site_use_id ||
651                 'p_bill_to_cust_acct_id= '|| p_bill_to_cust_acct_id ||
652                 'p_bill_to_party_site_ptp_id= '|| p_bill_to_party_site_ptp_id||
653                 'p_bill_to_party_ptp_id= '||  p_bill_to_party_ptp_id ||
654                 'p_sold_to_party_site_ptp_id= '|| p_sold_to_party_site_ptp_id||
655                 'p_sold_to_party_ptp_id= '||  p_sold_to_party_ptp_id ||
656                 'p_inventory_org_id= '||     p_inventory_org_id     ||
657                 'p_inventory_item_id= '||    p_inventory_item_id    ||
658                 'p_exempt_certificate_number= '|| p_exempt_certificate_number||
659                 'p_reason_code= '||          p_reason_code          ||
660                 'p_exempt_control_flag= '||  p_exempt_control_flag  ||
661                 'p_tax_date= '||             p_tax_date             ||
662                 'p_tax_regime_code= '||      p_tax_regime_code      ||
663                 'p_tax= '||                  p_tax                  ||
664                 'p_tax_status_code= '||      p_tax_status_code      ||
665                 'p_tax_rate_code= '||        p_tax_rate_code        ||
666                 'p_tax_jurisdiction_id= '||  p_tax_jurisdiction_id  ||
667                 'p_multiple_jurisdictions_flag= '||p_multiple_jurisdictions_flag ||
668                 'p_event_class_rec.ledger_id= '||p_event_class_rec.ledger_id
669 ;
670         FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
671     END IF;
672 
673     x_return_status := FND_API.G_RET_STS_SUCCESS;
674     IF p_exempt_control_flag = 'E' and p_reason_code is null THEN
675       x_return_status := FND_API.G_RET_STS_ERROR;
676       FND_MESSAGE.SET_NAME('ZX', 'ZX_TCM_NO_EXEMPT_REASON');
677       RAISE FND_API.G_EXC_ERROR;
678 
679     END IF;
680 
681     IF nvl(p_multiple_jurisdictions_flag,'N') = 'N' THEN
682       l_tax_jurisdiction_id_tbl(1) := p_tax_jurisdiction_id;
683     ELSE
684       SELECT tax_jurisdiction_id, precedence_level
685       BULK COLLECT INTO l_tax_jurisdiction_id_tbl, l_precedence_level_tbl
686       FROM zx_jurisdictions_gt
687       ORDER BY precedence_level;
688     END IF;
689 
690     FOR i in l_tax_jurisdiction_id_tbl.first..l_tax_jurisdiction_id_tbl.last LOOP
691       IF nvl(p_event_class_rec.EXMPTN_PTY_BASIS_HIER_1_CODE,'BILL_TO') = 'BILL_TO' THEN
692          -- call ptp_based_exemptions with p_bill_to_party_site_ptp_id,
693          get_exemptions(     p_bill_to_party_site_ptp_id,
694                              p_bill_to_cust_acct_id,
695                              p_bill_to_cust_site_use_id,
696                              p_inventory_item_id,
697                              p_tax_date,
698                              p_exempt_certificate_number,
699                              p_reason_code,
700                              p_exempt_control_flag,
701                              p_tax_regime_code,
702                              p_tax,
703                              p_tax_status_code,
704                              p_tax_rate_code,
705                              l_tax_jurisdiction_id_tbl(i),
706                              p_event_class_rec,
707                              x_exemption_rec);
708          IF x_exemption_rec.exemption_id IS NULL THEN
709 	    IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
710         	l_log_msg := 'Calling get_exemptions with p_bill_to_party_ptp_id';
711         	FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
712     	    END IF;
713            -- call ptp_based_exemptions with p_bill_to_party_ptp_id,
714            get_exemptions(   p_bill_to_party_ptp_id,
715                              p_bill_to_cust_acct_id,
716                              p_bill_to_cust_site_use_id,
717                              p_inventory_item_id,
718                              p_tax_date,
719                              p_exempt_certificate_number,
720                              p_reason_code,
721                              p_exempt_control_flag,
722                              p_tax_regime_code,
723                              p_tax,
724                              p_tax_status_code,
725                              p_tax_rate_code,
726                              l_tax_jurisdiction_id_tbl(i),
727                              p_event_class_rec,
728                              x_exemption_rec);
729 
730          END IF;
731 
732        --
733   ELSIF p_event_class_rec.EXMPTN_PTY_BASIS_HIER_1_CODE = 'SOLD_TO' THEN
734          -- call ptp_based_exemptions with p_sold_to_party_site_ptp_id,
735          get_exemptions(     p_sold_to_party_site_ptp_id,
736                              p_bill_to_cust_acct_id,
737                              p_bill_to_cust_site_use_id,
738                              p_inventory_item_id,
739                              p_tax_date,
740                              p_exempt_certificate_number,
741                              p_reason_code,
742                              p_exempt_control_flag,
743                              p_tax_regime_code,
744                              p_tax,
745                              p_tax_status_code,
746                              p_tax_rate_code,
747                              l_tax_jurisdiction_id_tbl(i),
748                              p_event_class_rec,
749                              x_exemption_rec);
750        IF x_exemption_rec.exemption_id IS NULL THEN
751          -- call ptp_based_exemptions with p_sold_to_party_ptp_id,
752 	IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
753        	      l_log_msg := 'Calling get_exemptions with p_sold_to_party_ptp_id';
754         	FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
755     	END IF;
756          get_exemptions(     p_sold_to_party_ptp_id,
757                              p_bill_to_cust_acct_id,
758                              p_bill_to_cust_site_use_id,
759                              p_inventory_item_id,
760                              p_tax_date,
761                              p_exempt_certificate_number,
762                              p_reason_code,
763                              p_exempt_control_flag,
764                              p_tax_regime_code,
765                              p_tax,
766                              p_tax_status_code,
767                              p_tax_rate_code,
768                              l_tax_jurisdiction_id_tbl(i),
769                              p_event_class_rec,
770                              x_exemption_rec);
771 
772        END IF;
773 
774   END IF; -- hier_1_code check
775   --
776   IF x_exemption_rec.exemption_id IS NULL THEN
777     -- Need not add nvl for EXMPTN_PTY_BASIS_HIER_2_CODE
778     -- since get_exemptions has got executed with 'BILL_TO' earlier
779     IF p_event_class_rec.EXMPTN_PTY_BASIS_HIER_2_CODE = 'BILL_TO' THEN
780 
781         -- call ptp_based_exemptions with p_bill_to_party_site_ptp_id,
782         get_exemptions(      p_bill_to_party_site_ptp_id,
783                              p_bill_to_cust_acct_id,
784                              p_bill_to_cust_site_use_id,
785                              p_inventory_item_id,
786                              p_tax_date,
787                              p_exempt_certificate_number,
788                              p_reason_code,
789                              p_exempt_control_flag,
790                              p_tax_regime_code,
791                              p_tax,
792                              p_tax_status_code,
793                              p_tax_rate_code,
794                              l_tax_jurisdiction_id_tbl(i),
795                              p_event_class_rec,
796                              x_exemption_rec);
797         IF x_exemption_rec.exemption_id IS NULL THEN
798           -- call ptp_based_exemptions with p_bill_to_party_ptp_id,
799           get_exemptions(    p_bill_to_party_ptp_id,
800                              p_bill_to_cust_acct_id,
801                              p_bill_to_cust_site_use_id,
802                              p_inventory_item_id,
803                              p_tax_date,
804                              p_exempt_certificate_number,
805                              p_reason_code,
806                              p_exempt_control_flag,
807                              p_tax_regime_code,
808                              p_tax,
809                              p_tax_status_code,
810                              p_tax_rate_code,
811                              l_tax_jurisdiction_id_tbl(i),
812                              p_event_class_rec,
813                              x_exemption_rec);
814 
815         END IF;
816 
817        --
818     ELSIF p_event_class_rec.EXMPTN_PTY_BASIS_HIER_2_CODE = 'SOLD_TO' THEN
819          get_exemptions(     p_sold_to_party_site_ptp_id,
820                              p_bill_to_cust_acct_id,
821                              p_bill_to_cust_site_use_id,
822                              p_inventory_item_id,
823                              p_tax_date,
824                              p_exempt_certificate_number,
825                              p_reason_code,
826                              p_exempt_control_flag,
827                              p_tax_regime_code,
828                              p_tax,
829                              p_tax_status_code,
830                              p_tax_rate_code,
831                              l_tax_jurisdiction_id_tbl(i),
832                              p_event_class_rec,
833                              x_exemption_rec);
834        IF x_exemption_rec.exemption_id IS NULL THEN
835          -- call ptp_based_exemptions with p_sold_to_party_ptp_id,
836          get_exemptions(     p_sold_to_party_ptp_id,
837                              p_bill_to_cust_acct_id,
838                              p_bill_to_cust_site_use_id,
839                              p_inventory_item_id,
840                              p_tax_date,
841                              p_exempt_certificate_number,
842                              p_reason_code,
843                              p_exempt_control_flag,
844                              p_tax_regime_code,
845                              p_tax,
846                              p_tax_status_code,
847                              p_tax_rate_code,
848                              l_tax_jurisdiction_id_tbl(i),
849                              p_event_class_rec,
850                              x_exemption_rec);
851 
852        END IF;
853 
854   END IF; -- hier_2_code check
855 
856   END IF; -- exemption id null check
857   IF x_exemption_rec.exemption_id IS NOT NULL THEN
858     EXIT;
859   END IF;
860 
861 END LOOP;
862 
863 
864 IF x_exemption_rec.exemption_id IS NULL THEN
865   IF p_exempt_control_flag = 'E' THEN
866 
867     IF p_event_class_rec.ledger_id is null THEN
868      IF zx_global_structures_pkg.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID.exists(1) THEN
869        SELECT set_of_books_id
870        INTO l_ledger_id
871        FROM ar_system_parameters_all
872        WHERE org_id = zx_global_structures_pkg.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(1);
873      ELSIF p_event_class_rec.internal_organization_id is not null THEN
874        SELECT set_of_books_id
875        INTO l_ledger_id
876        FROM ar_system_parameters_all
877        WHERE org_id = p_event_class_rec.internal_organization_id;
878      END IF;
879 
880     END IF;
881     period_date_range(p_tax_date,
882                       nvl(p_event_class_rec.ledger_id, l_ledger_id),
883                       l_start_date,
884                       l_end_date);
885       IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
886         l_log_msg := 'l_start_date '||to_char(l_start_date);
887         FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', l_log_msg);
888       END IF;
889 
890 
891    n := l_tax_jurisdiction_id_tbl.last;
892    l_tax_jurisdiction_id := l_tax_jurisdiction_id_tbl(n);
893 
894     Begin
895      select 'Y' into l_exists
896      from
897      zx_exemptions
898      where nvl(tax_rate_code,'X') = nvl(p_tax_rate_code,'X') and
899      effective_from = nvl(l_start_date, trunc(sysdate))
900      and nvl(exempt_certificate_number,'X') = nvl(p_exempt_certificate_number,'X')
901      and exempt_reason_code = p_reason_code
902      and party_tax_profile_id = p_bill_to_party_ptp_id
903      and tax_regime_code = p_tax_regime_code
904      and content_owner_id =
905        nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)
906      and nvl(tax_status_code,'X') = nvl(p_tax_status_code,'X')
907       and nvl(tax,'X') = nvl(p_tax,'X')
908      and nvl(tax_jurisdiction_id,-999) = nvl(l_tax_jurisdiction_id,-999)
909      and exemption_status_code = 'UNAPPROVED';
910 
911      IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
912        FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions', 'Unapproved exemption exists');
913      END IF;
914 
915  exception
916   when no_data_found THEN
917    l_exists := 'N';
918   when others then
919    l_exists := 'N';
920  end;
921 
922  IF l_exists = 'N' THEN
923    IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
924         l_log_msg := 'Create Unapproved Exemption';
925         FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions',
926 l_log_msg);
927    END IF;
928 
929   SELECT zx_exemptions_s.nextval
930   INTO x_exemption_rec.exemption_id
931   FROM dual;
932   x_exemption_rec.discount_special_rate := 'DISCOUNT';
933   x_exemption_rec.percent_exempt := 100;
934   x_exemption_rec.apply_to_lower_levels_flag := 'Y';
935   x_exemption_rec.exempt_reason_code := p_reason_code;
936    x_exemption_rec.exempt_certificate_number := p_exempt_certificate_number;
937 
938   IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
939        FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || 'Get_tax_exemptions',
940                       'Exemption Id '|| to_char(x_exemption_rec.exemption_id) || ' '||
941                       'Tax Regime Code '|| p_TAX_REGIME_CODE || ' '||
942                       'Tax '|| p_TAX || ' '||
943                       'Tax Status Code ' ||p_TAX_STATUS_CODE || ' ' ||
944                       'Tax Rate Code '||p_tax_rate_code || ' ' ||
945                       'Content owner id '||to_char(nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID)) || ' ' ||
946                       'Exemption Certificate Number '|| p_exempt_certificate_number || ' ' ||
947                       'Reason Code '|| p_REASON_CODE || ' ' ||
948                       'Start Date '|| to_char(nvl(l_start_date, trunc(sysdate))) || ' ' ||
949                       'Bill to party ptp id '|| to_char(p_bill_to_party_ptp_id) || ' ' ||
950                       'Tax Jurisdiction id '|| to_char(l_tax_jurisdiction_id));
951      END IF;
952 
953   INSERT INTO ZX_EXEMPTIONS(
954     TAX_EXEMPTION_ID,
955     EXEMPTION_TYPE_CODE,
956     EXEMPTION_STATUS_CODE,
957     TAX_REGIME_CODE,
958     TAX,
959     TAX_STATUS_CODE,
960     tax_rate_code,
961     CONTENT_OWNER_ID,
962     EXEMPT_CERTIFICATE_NUMBER,
963     EXEMPT_REASON_CODE,
964     EFFECTIVE_FROM,
965     PARTY_TAX_PROFILE_ID,
966     RATE_MODIFIER,
967     APPLY_TO_LOWER_LEVELS_FLAG,
968     TAX_JURISDICTION_ID,
969     cust_account_id,
970     site_use_id,
971     RECORD_TYPE_CODE,
972     CREATED_BY,
973     CREATION_DATE,
974     LAST_UPDATED_BY,
975     LAST_UPDATE_DATE,
976     LAST_UPDATE_LOGIN,
977     REQUEST_ID,
978     PROGRAM_APPLICATION_ID,
979     PROGRAM_ID,
980     --PROGRAM_LOGIN_ID,
981     OBJECT_VERSION_NUMBER,
982     duplicate_exemption)
983   VALUES (
984     x_exemption_rec.exemption_id,
985     'DISCOUNT',
986     'UNAPPROVED',
987     p_TAX_REGIME_CODE,
988     p_TAX,
989     p_TAX_STATUS_CODE,
990     p_tax_rate_code,
991     nvl(p_event_class_rec.first_pty_org_id,ZX_SECURITY.G_FIRST_PARTY_ORG_ID),
992     p_exempt_certificate_number,
993     p_REASON_CODE,
994     nvl(l_start_date, trunc(sysdate)),
995     p_bill_to_party_ptp_id,
996     100,
997     'Y',
998     l_tax_jurisdiction_id,
999     null,
1000     null,
1001     'USER_DEFINED',
1002     fnd_global.user_id,
1003     sysdate,
1004     fnd_global.user_id,
1005     sysdate,
1006     fnd_global.conc_login_id,
1007     null, -- request id
1008     null, -- PROGRAM_APPLICATION_ID,
1009     null, -- PROGRAM_ID,
1010     --PROGRAM_LOGIN_ID,
1011     1,
1012     0);
1013   END IF;
1014   END IF;
1015 END IF;
1016 
1017 END;
1018 END;