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_TRL_PUB_PKG

Source


1 PACKAGE BODY ZX_TRL_PUB_PKG AS
2 /* $Header: zxrwlnrepsrvpubb.pls 120.44.12020000.5 2013/02/19 15:43:22 ssohal ship $ */
3 
4   g_current_runtime_level           NUMBER;
5   g_level_statement       CONSTANT  NUMBER  := FND_LOG.LEVEL_STATEMENT;
6   g_level_procedure       CONSTANT  NUMBER  := FND_LOG.LEVEL_PROCEDURE;
7   g_level_event           CONSTANT  NUMBER  := FND_LOG.LEVEL_EVENT;
8   g_level_unexpected      CONSTANT  NUMBER  := FND_LOG.LEVEL_UNEXPECTED;
9   g_level_error           CONSTANT  NUMBER  := FND_LOG.LEVEL_ERROR;
10 
11   -- Added procedure to run the Frozen tax dist deleted validation --
12   PROCEDURE Check_Frozen_Tax_Dists
13        (x_return_status     OUT NOCOPY VARCHAR2,
14         p_event_class_rec   IN  ZX_API_PUB.EVENT_CLASS_REC_TYPE);
15 
16 /* ===========================================================================*
17  | PROCEDURE Manage_TaxLines: It will Create, update, delete and cancel tax   |
18  |                            lines and summary  tax lines in the tax         |
19  |                            repository                                      |
20  * ===========================================================================*/
21 
22   PROCEDURE Manage_TaxLines
23        (x_return_status      OUT NOCOPY VARCHAR2,
24         p_event_class_rec IN            ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
25 
26     l_error_buffer                 VARCHAR2(100);
27     l_msg_context_info_rec         ZX_API_PUB.CONTEXT_INFO_REC_TYPE;
28 
29   BEGIN
30     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
31 
32     IF (g_level_procedure >= g_current_runtime_level ) THEN
33       FND_LOG.STRING(g_level_procedure,
34                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.BEGIN',
35                      'ZX_TRL_PUB_PKG: Manage_TaxLines (+)');
36     END IF;
37 
38     --  Initialize API return status to success
39     x_return_status := FND_API.G_RET_STS_SUCCESS;
40 
41     --
42     -- bug#4893261- populate message structure
43     --
44     l_msg_context_info_rec.application_id :=
45               p_event_class_rec.application_id;
46     l_msg_context_info_rec.entity_code :=
47               p_event_class_rec.entity_code;
48     l_msg_context_info_rec.event_class_code :=
49               p_event_class_rec.event_class_code;
50     l_msg_context_info_rec.trx_id :=
51               p_event_class_rec.trx_id;
52     l_msg_context_info_rec.trx_line_id := NULL;
53     l_msg_context_info_rec.trx_level_type := NULL;
54     l_msg_context_info_rec.summary_tax_line_number := NULL;
55     l_msg_context_info_rec.tax_line_id := NULL;
56     l_msg_context_info_rec.trx_line_dist_id := NULL;
57 
58 
59     -- p_event_class_rec.TAX_EVENT_TYPE_CODE can be null if there is a transaction with only
60     -- header and no lines (e.g. where all tax lines for the document need to be deleted) or
61     -- when a transaction in the batch has errors and so the transaction was not selected in
62     -- c_lines loop of service types pkg. Hence we should not raise error if the
63     -- p_event_class_rec.TAX_EVENT_TYPE_CODE IS NULL.
64     /*
65     IF p_event_class_rec.TAX_EVENT_TYPE_CODE IS NULL THEN
66       x_return_status := FND_API.G_RET_STS_ERROR;
67       FND_MESSAGE.SET_NAME('ZX','ZX_TRL_NULL_VALUES');
68       ZX_API_PUB.add_msg(l_msg_context_info_rec);
69       RETURN;
70     END IF;
71     */
72 
73     -- bug fix 5417887:
74     -- g_update_event_process_flag being Y means it is either a single update
75     -- document or a update batch process.
76     --
77     -- during the summary tax line creation, still make use of the
78     -- following flags in the p_event_class_rec:
79     -- summarization_flag,  retain_summ_tax_line_id_flag
80     -- the ASSUMPTION here is:
81     -- NO CROSS EVENT_CLASS_CODE DOCUMENTS EXISTS IN THE BATCH!!!
82     -- When situation changes in the future, need to revisit the code and make
83     -- necessary changes.
84 
85     IF ZX_GLOBAL_STRUCTURES_PKG.g_update_event_process_flag = 'Y' THEN
86 
87         /*
88          * no longer needed for UPDATE case
89          *
90          * ZX_TRL_MANAGE_TAX_PKG.Update_Synchronize_Taxlines
91          *                   (x_return_status => x_return_status);
92          *
93          */
94 
95       ZX_TRL_MANAGE_TAX_PKG.Delete_Detail_Lines
96                             (x_return_status   => x_return_status ,
97                              p_event_class_rec => p_event_class_rec);
98 
99       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
100         IF (g_level_error >= g_current_runtime_level ) THEN
101           FND_LOG.STRING(g_level_error,
102                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
103                  'Incorrect return_status after calling ' ||
104                  'ZX_TRL_MANAGE_TAX_PKG.Delete_Detail_Lines()');
105           FND_LOG.STRING(g_level_error,
106                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
107                  'RETURN_STATUS = ' || x_return_status);
108           FND_LOG.STRING(g_level_error,
109                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
110                  'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
111         END IF;
112         RETURN;
113       END IF;
114 
115       -- for summarization_flag <>'Y', only recreate the detail tax lines
116       IF p_event_class_rec.summarization_flag <> 'Y' THEN
117 
118         ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines (
119             p_event_class_rec => p_event_class_rec,
120             x_return_status => x_return_status);
121 
122         IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
123           IF (g_level_error >= g_current_runtime_level ) THEN
124             FND_LOG.STRING(g_level_error,
125                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
126                    'Incorrect return_status after calling ' ||
127                    'ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines()');
128             FND_LOG.STRING(g_level_error,
129                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
130                    'RETURN_STATUS = ' || x_return_status);
131             FND_LOG.STRING(g_level_error,
132                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
133                    'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
134           END IF;
135           RETURN;
136         END IF;
137 
138       ELSE --p_event_class_rec.summarization_flag = 'Y'
139 
140         -- Preserve old summary_tax_line_id in g_detail_tax_lines_gt (for UPDATE
141         -- case) if the same summarization criteria exist in zx_lines_summary
142         --
143         IF p_event_class_rec.retain_summ_tax_line_id_flag = 'Y' THEN
144 
145           ZX_TRL_MANAGE_TAX_PKG.update_exist_summary_line_id (
146                             p_event_class_rec => p_event_class_rec,
147                             x_return_status   => x_return_status);
148 
149           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
150             IF (g_level_error >= g_current_runtime_level ) THEN
151               FND_LOG.STRING(g_level_error,
152                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
153                      'Incorrect return_status after calling ' ||
154                      'ZX_TRL_MANAGE_TAX_PKG.update_exist_summary_line_id()');
155               FND_LOG.STRING(g_level_error,
156                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
157                      'RETURN_STATUS = ' || x_return_status);
158               FND_LOG.STRING(g_level_error,
159                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
160                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
161             END IF;
162             RETURN;
163           END IF;
164         END IF;
165 
166         ZX_TRL_MANAGE_TAX_PKG.Delete_Summary_Lines
167                           (x_return_status   => x_return_status,
168                            p_event_class_rec => p_event_class_rec);
169 
170         IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
171           IF (g_level_error >= g_current_runtime_level ) THEN
172             FND_LOG.STRING(g_level_error,
173                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
174                    'Incorrect return_status after calling ' ||
175                    'ZX_TRL_MANAGE_TAX_PKG.Delete_Summary_Lines()');
176             FND_LOG.STRING(g_level_error,
177                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
178                    'RETURN_STATUS = ' || x_return_status);
179             FND_LOG.STRING(g_level_error,
180                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
181                    'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
182           END IF;
183           RETURN;
184         END IF;
185 
186         -- recreate the summary tax lines and detail tax lines
187 
188         IF p_event_class_rec.retain_summ_tax_line_id_flag = 'N' THEN
189 
190         -- for update tax event, AP will pass in all the trx lines,
191         -- that is, all the tax lines will be in the zx_detail_tax_lines_gt.
192         -- so if not retain summary_tax_line_id, it is treated same as
193         -- created case.
194 
195           ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt (
196               p_event_class_rec => p_event_class_rec,
197               x_return_status   => x_return_status );
198 
199           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
200             IF (g_level_error >= g_current_runtime_level ) THEN
201               FND_LOG.STRING(g_level_error,
202                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
203                      'Incorrect return_status after calling ' ||
204                      'ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt()');
205               FND_LOG.STRING(g_level_error,
206                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
207                      'RETURN_STATUS = ' || x_return_status);
208               FND_LOG.STRING(g_level_error,
209                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
210                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
211             END IF;
212             RETURN;
213           END IF;
214 
215 
216           ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines (
217               p_event_class_rec => p_event_class_rec,
218               x_return_status => x_return_status);
219 
220           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
221             IF (g_level_error >= g_current_runtime_level ) THEN
222               FND_LOG.STRING(g_level_error,
223                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
224                      'Incorrect return_status after calling ' ||
225                      'ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines()');
226               FND_LOG.STRING(g_level_error,
227                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
228                      'RETURN_STATUS = ' || x_return_status);
229               FND_LOG.STRING(g_level_error,
230                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
231                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
232             END IF;
233             RETURN;
234           END IF;
235 
236         ELSE
237         -- For retain_summary_tax_line_id ='Y'
238 
239           ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines (
240               p_event_class_rec => p_event_class_rec,
241               x_return_status => x_return_status);
242 
243           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
244             IF (g_level_error >= g_current_runtime_level ) THEN
245               FND_LOG.STRING(g_level_error,
246                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
247                      'Incorrect return_status after calling ' ||
248                      'ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines()');
249               FND_LOG.STRING(g_level_error,
250                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
251                      'RETURN_STATUS = ' || x_return_status);
252               FND_LOG.STRING(g_level_error,
253                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
254                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
255             END IF;
256             RETURN;
257           END IF;
258 
259           ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_upd_evnt (
260               p_event_class_rec => p_event_class_rec,
261               x_return_status   => x_return_status );
262 
263           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
264             IF (g_level_error >= g_current_runtime_level ) THEN
265               FND_LOG.STRING(g_level_error,
266                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
267                      'Incorrect return_status after calling ' ||
268                      'ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt()');
269               FND_LOG.STRING(g_level_error,
270                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
271                      'RETURN_STATUS = ' || x_return_status);
272               FND_LOG.STRING(g_level_error,
273                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
274                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
275             END IF;
276             RETURN;
277           END IF;
278 
279         END IF; -- p_event_class_rec.retain_summ_tax_line_id_flag = 'N'
280 
281       END IF; -- p_event_class_rec.summarization_flag <> 'Y'
282 
283 
284       ZX_TRL_MANAGE_TAX_PKG.Delete_Loose_Tax_Distributions
285                           (x_return_status   => x_return_status,
286                            p_event_class_rec => p_event_class_rec);
287 
288       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
289         IF (g_level_error >= g_current_runtime_level ) THEN
290           FND_LOG.STRING(g_level_error,
291                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
292                  'Incorrect return_status after calling ' ||
293                  'ZX_TRL_MANAGE_TAX_PKG.Delete_Loose_Tax_Distributions()');
294           FND_LOG.STRING(g_level_error,
295                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
296                  'RETURN_STATUS = ' || x_return_status);
297           FND_LOG.STRING(g_level_error,
298                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
299                  'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
300         END IF;
301         RETURN;
302       END IF;
303 
304     ELSIF (p_event_class_rec.TAX_EVENT_TYPE_CODE = 'CREATE') THEN
305 
306       IF p_event_class_rec.summarization_flag = 'Y' THEN
307 
308         -- for create tax event, summary tax line created based on
309         -- zx_detail_tax_lines_gt, dump detail tax lines from gt to zx_lines
310         -- called after summary lines created for performance consideration.
311         --
312         ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt (
313             p_event_class_rec => p_event_class_rec,
314             x_return_status   => x_return_status );
315 
316         IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
317           IF (g_level_error >= g_current_runtime_level ) THEN
318             FND_LOG.STRING(g_level_error,
319                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
320                    'Incorrect return_status after calling ' ||
321                    'ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt()');
322             FND_LOG.STRING(g_level_error,
323                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
324                    'RETURN_STATUS = ' || x_return_status);
325             FND_LOG.STRING(g_level_error,
326                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
327                    'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
328           END IF;
329           RETURN;
330         END IF;
331 
332       END IF;
333 
334       -- Dump detail tax lines from gt to zx_lines
335       ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines (
336         p_event_class_rec => p_event_class_rec,
337         x_return_status => x_return_status);
338 
339       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
340         IF (g_level_error >= g_current_runtime_level ) THEN
341           FND_LOG.STRING(g_level_error,
342                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
343                  'Incorrect return_status after calling ' ||
344                  'ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines()');
345           FND_LOG.STRING(g_level_error,
346                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
347                  'RETURN_STATUS = ' || x_return_status);
348           FND_LOG.STRING(g_level_error,
349                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
350                  'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
351         END IF;
352         RETURN;
353       END IF;
354 
355     -- bug fix 5417887
356     --ELSIF (p_event_class_rec.tax_event_type_code ='UPDATE' OR
357     --       p_event_class_rec.tax_event_type_code ='OVERRIDE_TAX' ) THEN
358     ELSIF  p_event_class_rec.tax_event_type_code ='OVERRIDE_TAX' THEN
359 
360         /*
361          * no longer needed for UPDATE case
362          *
363          * ZX_TRL_MANAGE_TAX_PKG.Update_Synchronize_Taxlines
364          *                   (x_return_status => x_return_status);
365          *
366          */
367 
368       ZX_TRL_MANAGE_TAX_PKG.Delete_Detail_Lines
369                             (x_return_status   => x_return_status ,
370                              p_event_class_rec => p_event_class_rec);
371 
372       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
373         IF (g_level_error >= g_current_runtime_level ) THEN
374           FND_LOG.STRING(g_level_error,
375                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
376                  'Incorrect return_status after calling ' ||
377                  'ZX_TRL_MANAGE_TAX_PKG.Delete_Detail_Lines()');
378           FND_LOG.STRING(g_level_error,
379                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
380                  'RETURN_STATUS = ' || x_return_status);
381           FND_LOG.STRING(g_level_error,
382                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
383                  'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
384         END IF;
385         RETURN;
386       END IF;
387 
388       -- for summarization_flag <>'Y', only recreate the detail tax lines
389       IF p_event_class_rec.summarization_flag <> 'Y' THEN
390 
391         ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines (
392             p_event_class_rec => p_event_class_rec,
393             x_return_status => x_return_status);
394 
395         IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
396           IF (g_level_error >= g_current_runtime_level ) THEN
397             FND_LOG.STRING(g_level_error,
398                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
399                    'Incorrect return_status after calling ' ||
400                    'ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines()');
401             FND_LOG.STRING(g_level_error,
402                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
403                    'RETURN_STATUS = ' || x_return_status);
404             FND_LOG.STRING(g_level_error,
405                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
406                    'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
407           END IF;
408           RETURN;
409         END IF;
410 
411       ELSE --p_event_class_rec.summarization_flag = 'Y'
412 
413         -- Preserve old summary_tax_line_id in g_detail_tax_lines_gt (for UPDATE
414         -- case) if the same summarization criteria exist in zx_lines_summary
415         --
416         IF p_event_class_rec.retain_summ_tax_line_id_flag = 'Y' THEN
417 
418           ZX_TRL_MANAGE_TAX_PKG.update_exist_summary_line_id (
419                             p_event_class_rec => p_event_class_rec,
420                             x_return_status   => x_return_status);
421 
422           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
423             IF (g_level_error >= g_current_runtime_level ) THEN
424               FND_LOG.STRING(g_level_error,
425                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
426                      'Incorrect return_status after calling ' ||
427                      'ZX_TRL_MANAGE_TAX_PKG.update_exist_summary_line_id()');
428               FND_LOG.STRING(g_level_error,
429                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
430                      'RETURN_STATUS = ' || x_return_status);
431               FND_LOG.STRING(g_level_error,
432                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
433                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
434             END IF;
435             RETURN;
436           END IF;
437         END IF;
438 
439         ZX_TRL_MANAGE_TAX_PKG.Delete_Summary_Lines
440                           (x_return_status   => x_return_status,
441                            p_event_class_rec => p_event_class_rec);
442 
443         IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
444           IF (g_level_error >= g_current_runtime_level ) THEN
445             FND_LOG.STRING(g_level_error,
446                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
447                    'Incorrect return_status after calling ' ||
448                    'ZX_TRL_MANAGE_TAX_PKG.Delete_Summary_Lines()');
449             FND_LOG.STRING(g_level_error,
450                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
451                    'RETURN_STATUS = ' || x_return_status);
452             FND_LOG.STRING(g_level_error,
453                    'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
454                    'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
455           END IF;
456           RETURN;
457         END IF;
458 
459        /* -- commented out for bug fix 5417887
460         -- recreate the summary tax lines and detail tax lines
461 
462         IF p_event_class_rec.tax_event_type_code = 'UPDATE'
463           AND p_event_class_rec.retain_summ_tax_line_id_flag = 'N'
464         THEN
465         -- for update tax event, AP will pass in all the trx lines,
466         -- that is, all the tax lines will be in the zx_detail_tax_lines_gt.
467         -- so if not retain summary_tax_line_id, it is treated same as
468         -- created case.
469 
470           ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt (
471               p_event_class_rec => p_event_class_rec,
472               x_return_status   => x_return_status );
473 
474           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
475             IF (g_level_error >= g_current_runtime_level ) THEN
476               FND_LOG.STRING(g_level_error,
477                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
478                      'Incorrect return_status after calling ' ||
479                      'ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt()');
480               FND_LOG.STRING(g_level_error,
481                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
482                      'RETURN_STATUS = ' || x_return_status);
483               FND_LOG.STRING(g_level_error,
484                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
485                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
486             END IF;
487             RETURN;
488           END IF;
489 
490 
491           ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines (
492               p_event_class_rec => p_event_class_rec,
493               x_return_status => x_return_status);
494 
495           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
496             IF (g_level_error >= g_current_runtime_level ) THEN
497               FND_LOG.STRING(g_level_error,
498                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
499                      'Incorrect return_status after calling ' ||
500                      'ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines()');
501               FND_LOG.STRING(g_level_error,
502                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
503                      'RETURN_STATUS = ' || x_return_status);
504               FND_LOG.STRING(g_level_error,
505                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
506                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
507             END IF;
508             RETURN;
509           END IF;
510 
511         ELSE
512         -- For tax_event_type of UPDATE and retain_summary_tax_line_id ='Y'
513         -- or tax_event_type of OVERRIDE_TAX
514 
515         -- commented out for bug fix 5417887 end */
516 
517           ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines (
518               p_event_class_rec => p_event_class_rec,
519               x_return_status => x_return_status);
520 
521           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
522             IF (g_level_error >= g_current_runtime_level ) THEN
523               FND_LOG.STRING(g_level_error,
524                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
525                      'Incorrect return_status after calling ' ||
526                      'ZX_TRL_MANAGE_TAX_PKG.Create_Detail_Lines()');
527               FND_LOG.STRING(g_level_error,
528                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
529                      'RETURN_STATUS = ' || x_return_status);
530               FND_LOG.STRING(g_level_error,
531                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
532                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
533             END IF;
534             RETURN;
535           END IF;
536 
537           ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_upd_evnt (
538               p_event_class_rec => p_event_class_rec,
539               x_return_status   => x_return_status );
540 
541           IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
542             IF (g_level_error >= g_current_runtime_level ) THEN
543               FND_LOG.STRING(g_level_error,
544                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
545                      'Incorrect return_status after calling ' ||
546                      'ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_crt_evnt()');
547               FND_LOG.STRING(g_level_error,
548                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
549                      'RETURN_STATUS = ' || x_return_status);
550               FND_LOG.STRING(g_level_error,
551                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
552                      'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
553             END IF;
554             RETURN;
555           END IF;
556 
557        -- bug fix 5417887
558        -- END IF; -- p_event_class_rec.tax_event_type_code = 'UPDATE'
559 
560       END IF; -- p_event_class_rec.summarization_flag <> 'Y'
561 
562 
563       ZX_TRL_MANAGE_TAX_PKG.Delete_Loose_Tax_Distributions
564                           (x_return_status   => x_return_status,
565                            p_event_class_rec => p_event_class_rec);
566 
567       IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
568         IF (g_level_error >= g_current_runtime_level ) THEN
569           FND_LOG.STRING(g_level_error,
570                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
571                  'Incorrect return_status after calling ' ||
572                  'ZX_TRL_MANAGE_TAX_PKG.Delete_Loose_Tax_Distributions()');
573           FND_LOG.STRING(g_level_error,
574                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
575                  'RETURN_STATUS = ' || x_return_status);
576           FND_LOG.STRING(g_level_error,
577                  'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
578                  'ZX_TRL_PUB_PKG.Manage_TaxLines(-)');
579         END IF;
580         RETURN;
581       END IF;
582 
583     END IF;      -- tax_event_type_code
584 
585     IF (g_level_procedure >= g_current_runtime_level ) THEN
586 
587       FND_LOG.STRING(g_level_procedure,
588                     'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines.END',
589                     'ZX_TRL_PUB_PKG: Manage_TaxLines (-)'||x_return_status);
590     END IF;
591 
592   EXCEPTION
593     WHEN OTHERS THEN
594       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
595       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
596 
597       IF (g_level_unexpected >= g_current_runtime_level ) THEN
598         FND_LOG.STRING(g_level_unexpected,
599                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxLines',
600                         l_error_buffer);
601       END IF;
602 
603   END Manage_TaxLines;
604 
605 
606 /*============================================================================*
607  | PROCEDURE Document_Level_Changes: It will Delete / Cancel / Purge tax lines |
608  |                                   from the tax repository                   |
609  *============================================================================*/
610 
611   PROCEDURE Document_Level_Changes
612        (x_return_status             OUT NOCOPY VARCHAR2,
613         p_event_class_rec        IN     ZX_API_PUB.EVENT_CLASS_REC_TYPE,
614         p_tax_hold_released_code IN     ZX_API_PUB.VALIDATION_STATUS_TBL_TYPE) IS
615 
616     l_return_status VARCHAR2(1);
617 
618     --TRANSACTION TABLE HAS A NUMBER OF TRANSACTIONS TO BE PROCESSED. BASED ON THE EVENTS ,
619     --APPROPRIATE PROCEDURES WOULD BE CALLED. ZX_LINES TABLE MUST HAVE
620     --TRANSACTION LINES FOR THE GIVEN TRANSACTION FOR FURTHER PROCESSING.
621     l_error_buffer   VARCHAR2(100);
622 
623   BEGIN
624     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
625 
626     IF (g_level_procedure >= g_current_runtime_level ) THEN
627       FND_LOG.STRING(g_level_procedure,
628                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Document_Level_Changes.BEGIN',
629                      'ZX_TRL_PUB_PKG: Document_Level_Changes (+)');
630     END IF;
631 
632     --  Initialize API return status to success
633     x_return_status := FND_API.G_RET_STS_SUCCESS;
634 
635     IF (p_event_class_rec.TAX_EVENT_TYPE_CODE = 'DELETE') THEN
636 
637       ZX_TRL_MANAGE_TAX_PKG.DELETE_TRANSACTION
638                         (x_return_status   => l_return_status    ,
639                          p_event_class_rec => p_event_class_rec);
640 
641     ELSIF (p_event_class_rec.TAX_EVENT_TYPE_CODE = 'PURGE') THEN
642 
643       ZX_TRL_MANAGE_TAX_PKG.PURGE_TRANSACTION
644                         (x_return_status   => l_return_status,
645                          p_event_class_rec => p_event_class_rec);
646 
647     ELSIF (p_event_class_rec.TAX_EVENT_TYPE_CODE = 'CANCEL') THEN
648 
649       ZX_TRL_MANAGE_TAX_PKG.CANCEL_TRANSACTION
650                         (x_return_status   => l_return_status,
651                          p_event_class_rec => p_event_class_rec);
652 
653     -- begin bug fix 3339364
654     ELSIF (p_event_class_rec.TAX_EVENT_TYPE_CODE = 'RELEASE_TAX_HOLD') THEN
655 
656       ZX_TRL_MANAGE_TAX_PKG.RELEASE_DOCUMENT_TAX_HOLD
657                         (x_return_status   => l_return_status,
658                          p_event_class_rec => p_event_class_rec,
659                          p_tax_hold_released_code => p_tax_hold_released_code
660                          );
661 
662     -- end bug fix 3339364
663     END IF;
664 
665     IF l_return_status = FND_API.G_RET_STS_ERROR THEN
666       RAISE FND_API.G_EXC_ERROR;
667     ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
668       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669     END IF;
670 
671     IF (g_level_procedure >= g_current_runtime_level ) THEN
672       FND_LOG.STRING(g_level_procedure,
673                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Document_Level_Changes.END',
674                      'ZX_TRL_PUB_PKG: Document_Level_Changes (-)'||x_return_status);
675     END IF;
676 
677   EXCEPTION
678     WHEN FND_API.G_EXC_ERROR THEN
679       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
680       IF (g_level_unexpected >= g_current_runtime_level ) THEN
681         FND_LOG.STRING(g_level_unexpected,
682                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Document_Level_Changes',
683                        'Return Status = ' || x_return_status);
684       END IF;
685 
686     WHEN OTHERS THEN
687       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
689 
690       IF (g_level_unexpected >= g_current_runtime_level ) THEN
691         FND_LOG.STRING(g_level_unexpected,
692                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Document_Level_Changes',
693                         l_error_buffer);
694       END IF;
695 
696   END Document_Level_Changes;
697 
698 /* ===========================================================================*
699  | PROCEDURE Synchronize_TaxLines : updates transaction information in the tax|
700  |                                  repository                                |
701  * ===========================================================================*/
702 
703   PROCEDURE Synchronize_TaxLines
704        (x_return_status    OUT   NOCOPY VARCHAR2) IS
705 
706     l_return_status  VARCHAR2(1);
707     l_error_buffer   VARCHAR2(100);
708 
709   BEGIN
710     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
711 
712     IF (g_level_procedure >= g_current_runtime_level ) THEN
713       FND_LOG.STRING(g_level_procedure,
714                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Synchronize_TaxLines.BEGIN',
715                      'ZX_TRL_PUB_PKG: Synchronize_TaxLines (+)');
716     END IF;
717 
718     --  Initialize API return status to success
719     x_return_status := FND_API.G_RET_STS_SUCCESS;
720 
721     /****** no longer needed  ***********
722      *ZX_TRL_MANAGE_TAX_PKG.Update_Transaction_Info
723      *                  (x_return_status    =>  l_return_status);
724      *
725      *************************************/
726 
727 
728     IF l_return_status = FND_API.G_RET_STS_ERROR THEN
729       RAISE FND_API.G_EXC_ERROR;
730     ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
731       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732     END IF;
733 
734     IF (g_level_procedure >= g_current_runtime_level ) THEN
735       FND_LOG.STRING(g_level_procedure,
736                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Synchronize_TaxLines.END',
737                      'ZX_TRL_PUB_PKG: Synchronize_TaxLines (-)'||x_return_status);
738     END IF;
739 
740   EXCEPTION
741     WHEN FND_API.G_EXC_ERROR THEN
742       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
743       IF (g_level_unexpected >= g_current_runtime_level ) THEN
744         FND_LOG.STRING(g_level_unexpected,
745                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Synchronize_TaxLines',
746                        'Return Status = ' || x_return_status);
747       END IF;
748 
749     WHEN OTHERS THEN
750       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
751       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
752 
753       IF (g_level_unexpected >= g_current_runtime_level ) THEN
754         FND_LOG.STRING(g_level_unexpected,
755                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Synchronize_TaxLines',
756                         l_error_buffer);
757       END IF;
758 
759   END Synchronize_TaxLines;
760 
761 /* ===========================================================================*
762  | PROCEDURE Mark_Tax_Lines_Delete  : Marks the tax lines as delete.          |
763  * ===========================================================================*/
764 
765   PROCEDURE Mark_Tax_Lines_Delete
766        (x_return_status           OUT NOCOPY VARCHAR2,
767         p_transaction_line_rec IN            ZX_API_PUB.TRANSACTION_LINE_REC_TYPE) IS
768 
769     l_return_status VARCHAR2(1);
770     l_error_buffer  VARCHAR2(100);
771 
772   BEGIN
773     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
774 
775     IF (g_level_procedure >= g_current_runtime_level ) THEN
776       FND_LOG.STRING(g_level_procedure,
777                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete.BEGIN',
778                      'ZX_TRL_PUB_PKG: Mark_Tax_Lines_Delete (+)');
779     END IF;
780 
781     --  Initialize API return status to success
782     x_return_status := FND_API.G_RET_STS_SUCCESS;
783 
784     ZX_TRL_MANAGE_TAX_PKG.Mark_Detail_Tax_Lines_Delete
785                       (x_return_status        => l_return_status ,
786                        p_transaction_line_rec => p_transaction_line_rec);
787 
788 
789     IF l_return_status = FND_API.G_RET_STS_ERROR THEN
790       RAISE FND_API.G_EXC_ERROR;
791     ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
792       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
793     END IF;
794 
795     IF (g_level_procedure >= g_current_runtime_level ) THEN
796       FND_LOG.STRING(g_level_procedure,
797                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete.END',
798                      'ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete (-)'||x_return_status);
799     END IF;
800 
801   EXCEPTION
802     WHEN FND_API.G_EXC_ERROR THEN
803       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804       IF (g_level_unexpected >= g_current_runtime_level ) THEN
805         FND_LOG.STRING(g_level_unexpected,
806                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete',
807                        'Return Status = ' || x_return_status);
808       END IF;
809 
810     WHEN OTHERS THEN
811       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
812       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
813 
814       IF (g_level_unexpected >= g_current_runtime_level ) THEN
815         FND_LOG.STRING(g_level_unexpected,
816                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Mark_Tax_Lines_Delete',
817                         l_error_buffer);
818       END IF;
819 
820   END Mark_Tax_Lines_Delete;
821 
822 /*============================================================================*
823  | PROCEDURE Manage_TaxDistributions: It will create, update, delete tax      |
824  |                                    distributions lines and update tax lines|
825  |                                    and summary tax lines in the            |
826  |                                    tax repository.                         |
827  |============================================================================*/
828 
829   PROCEDURE Manage_TaxDistributions
830        (x_return_status      OUT NOCOPY VARCHAR2,
831         p_event_class_rec IN            ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
832 
833     l_return_status           VARCHAR2(1);
834     l_error_buffer            VARCHAR2(200);
835     l_msg_context_info_rec    ZX_API_PUB.CONTEXT_INFO_REC_TYPE;
836 
837   BEGIN
838     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
839 
840     IF (g_level_procedure >= g_current_runtime_level ) THEN
841       FND_LOG.STRING(g_level_procedure,
842                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxDistributions.BEGIN',
843                      'ZX_TRL_PUB_PKG: Manage_TaxDistributions (+)');
844     END IF;
845 
846     --  Initialize API return status to success
847     x_return_status := FND_API.G_RET_STS_SUCCESS;
848 
849     --
850     -- bug#4893261- populate message structure
851     --
852     l_msg_context_info_rec.application_id :=
853               p_event_class_rec.application_id;
854     l_msg_context_info_rec.entity_code :=
855               p_event_class_rec.entity_code;
856     l_msg_context_info_rec.event_class_code :=
857               p_event_class_rec.event_class_code;
858     l_msg_context_info_rec.trx_id := NULL;
859    --         p_event_class_rec.trx_id;
860     l_msg_context_info_rec.trx_line_id := NULL;
861     l_msg_context_info_rec.trx_level_type := NULL;
862     l_msg_context_info_rec.summary_tax_line_number := NULL;
863     l_msg_context_info_rec.tax_line_id := NULL;
864     l_msg_context_info_rec.trx_line_dist_id := NULL;
865 
866 
867     IF (p_event_class_rec.tax_event_type_code <> 'OVERRIDE_TAX_DISTRIBUTIONS') THEN
868     -- bugfix 5551973
869     -- this part handles the tax event type of DISTRIBUTE and RE-DISTRIBUTE
870     -- If separate logic is required for any other events, it should be handled separetely.
871 
872       -- the call to ZX_TRL_MANAGE_TAX_PKG.Delete_dist_Marked_For_Delete is needed
873       -- only for re-distribute event. make this call conditional later on
874       ZX_TRL_MANAGE_TAX_PKG.Delete_dist_Marked_For_Delete
875                           (x_return_status   => l_return_status ,
876                            p_event_class_rec => p_event_class_rec);
877 
878 
879       IF (l_return_status= FND_API.G_RET_STS_SUCCESS) THEN
880         ZX_TRL_MANAGE_TAX_PKG.Create_Tax_Distributions
881                             (x_return_status => l_return_status);
882 
883       END IF;
884 
885       --IF l_return_status = FND_API.G_RET_STS_ERROR THEN
886       --  FND_MESSAGE.SET_NAME('ZX','ZX_TRL_RECORD_ALREADY_EXISTS');
887       --  ZX_API_PUB.add_msg(l_msg_context_info_rec);
888       --END IF;
889 
890       IF (l_return_status= FND_API.G_RET_STS_SUCCESS) THEN
891         ZX_TRL_MANAGE_TAX_PKG.Update_Taxline_Rec_Nrec_amt
892                             (x_return_status   => l_return_status ,
893                              p_event_class_rec => p_event_class_rec);
894 
895       END IF;
896 
897     ELSIF p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX_DISTRIBUTIONS' THEN
898 
899       -- confirm that  tax_event_type_code = 'OVERRIDE_TAX_DISTRIBUTIONS'
900       -- will not be there in the bulk call to determine recovery. Otherwise these API
901       -- calls need a change for bulk processing of override deistributions
902 
903       ZX_TRL_MANAGE_TAX_PKG.delete_tax_distributions(
904                            x_return_status   => l_return_status,
905                            p_event_class_rec => p_event_class_rec);
906 
907       IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
908         ZX_TRL_MANAGE_TAX_PKG.Create_Tax_Distributions
909                             (x_return_status => l_return_status);
910 
911       END IF;
912 
913       IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
914         ZX_TRL_MANAGE_TAX_PKG.update_taxline_rec_nrec_amt(
915                              x_return_status   => l_return_status,
916                              p_event_class_rec => p_event_class_rec);
917 
918       END IF;
919     END IF;
920 
921     -- Added preventive check to find deleted frozen tax dists --
922     -- Start --
923     IF (l_return_status= FND_API.G_RET_STS_SUCCESS) THEN
924       IF p_event_class_rec.application_id = 200 THEN
925         ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists
926            (x_return_status => l_return_status,
927             p_event_class_rec => p_event_class_rec);
928 
929         IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
930           RAISE FND_API.G_EXC_ERROR;
931         END IF;
932       END IF;
933     END IF;
934     -- End --
935 
936     IF l_return_status = FND_API.G_RET_STS_ERROR THEN
937       RAISE FND_API.G_EXC_ERROR;
938     ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
939       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
940     END IF;
941 
942     IF (g_level_procedure >= g_current_runtime_level ) THEN
943       FND_LOG.STRING(g_level_procedure,
944                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxDistributions.END',
945                      'ZX_TRL_PUB_PKG: Manage_TaxDistributions (-)'||x_return_status);
946     END IF;
947 
948   EXCEPTION
949     WHEN FND_API.G_EXC_ERROR THEN
950       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
951       IF (g_level_unexpected >= g_current_runtime_level ) THEN
952         FND_LOG.STRING(g_level_unexpected,
953                'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxDistributions',
954                'Unexpected Error. Return_Status = '||x_return_status);
955         FND_LOG.STRING(g_level_unexpected,
956                'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxDistributions.END',
957                'ZX_TRL_PUB_PKG: Manage_TaxDistributions (-)');
958       END IF;
959 
960     WHEN OTHERS THEN
961       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
962       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
963 
964       IF (g_level_unexpected >= g_current_runtime_level ) THEN
965         FND_LOG.STRING(g_level_unexpected,
966                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Manage_TaxDistributions',
967                         l_error_buffer);
968       END IF;
969   END Manage_TaxDistributions;
970 
971 /*=====================================================================================*
972  | PROCEDURE Freeze_TaxDistributions: This recording service is used to freeze tax     |
973  |                                    distributions whenever user freezes transaction  |
974  |                                    distribution lines                               |
975  |=====================================================================================*/
976 
977   PROCEDURE Freeze_TaxDistributions
978        (x_return_status      OUT NOCOPY VARCHAR2,
979         p_event_class_rec    IN         ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
980 
981     l_error_buffer  VARCHAR2(100);
982 
983   BEGIN
984     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
985 
986     IF (g_level_procedure >= g_current_runtime_level ) THEN
987       FND_LOG.STRING(g_level_procedure,
988                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Freeze_TaxDistributions.BEGIN',
989                      'ZX_TRL_PUB_PKG: Freeze_TaxDistributions (+)');
990     END IF;
991 
992     ZX_TRL_MANAGE_TAX_PKG.Update_Freeze_Flag
993                       (x_return_status         =>      x_return_status,
994                        p_event_class_rec       =>      p_event_class_rec);
995 
996 
997     IF (g_level_procedure >= g_current_runtime_level ) THEN
998       FND_LOG.STRING(g_level_procedure,
999                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Freeze_TaxDistributions.END',
1000                      'ZX_TRL_PUB_PKG: Freeze_TaxDistributions (-)'||x_return_status);
1001     END IF;
1002 
1003   EXCEPTION
1004     WHEN FND_API.G_EXC_ERROR THEN
1005       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1006       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1007         FND_LOG.STRING(g_level_unexpected,
1008                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Freeze_TaxDistributions',
1009                        'Return Status = ' || x_return_status);
1010       END IF;
1011 
1012     WHEN OTHERS THEN
1013       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1014       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1015 
1016       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1017         FND_LOG.STRING(g_level_unexpected,
1018                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Freeze_TaxDistributions',
1019                         l_error_buffer);
1020       END IF;
1021   END Freeze_TaxDistributions;
1022 
1023 /*============================================================================*
1024  | PROCEDURE Update_Taxlines: This recording service is used to update tax    |
1025  |                            lines (ZX_LINES) with changed status for given  |
1026  |                            transaction line distributions.                 |
1027  |============================================================================*/
1028   PROCEDURE Update_Taxlines
1029        (x_return_status      OUT NOCOPY VARCHAR2,
1030         p_event_class_rec IN            ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
1031 
1032     l_error_buffer  VARCHAR2(100);
1033 
1034   BEGIN
1035     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1036 
1037     IF (g_level_procedure >= g_current_runtime_level ) THEN
1038       FND_LOG.STRING(g_level_procedure,
1039                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Taxlines.BEGIN',
1040                      'ZX_TRL_PUB_PKG: Update_Taxlines (+)');
1041     END IF;
1042 
1043     ZX_TRL_MANAGE_TAX_PKG.Update_Item_Dist_Changed_Flag
1044                       (x_return_status   => x_return_status,
1045                        p_event_class_rec => p_event_class_rec);
1046 
1047     IF (g_level_procedure >= g_current_runtime_level ) THEN
1048       FND_LOG.STRING(g_level_procedure,
1049                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Taxlines.END',
1050                      'ZX_TRL_PUB_PKG: Update_Taxlines (-)'||x_return_status);
1051     END IF;
1052 
1053   EXCEPTION
1054     WHEN FND_API.G_EXC_ERROR THEN
1055       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1056       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1057         FND_LOG.STRING(g_level_unexpected,
1058                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Taxlines',
1059                        'Return Status = ' || x_return_status);
1060       END IF;
1061 
1062     WHEN OTHERS THEN
1063       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1064       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1065 
1066       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1067         FND_LOG.STRING(g_level_unexpected,
1068                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Taxlines',
1069                         l_error_buffer);
1070       END IF;
1071   END Update_Taxlines;
1072 
1073   PROCEDURE Discard_Tax_Only_Lines
1074        (x_return_status      OUT NOCOPY VARCHAR2,
1075         p_event_class_rec IN            ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
1076 
1077     l_error_buffer  VARCHAR2(100);
1078 
1079   BEGIN
1080     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1081 
1082     IF (g_level_procedure >= g_current_runtime_level ) THEN
1083       FND_LOG.STRING(g_level_procedure,
1084                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Discard_Tax_Only_Lines.BEGIN',
1085                      'ZX_TRL_PUB_PKG: Discard_Tax_Only_Lines (+)');
1086     END IF;
1087 
1088     ZX_TRL_MANAGE_TAX_PKG.Discard_Tax_Only_Lines (x_return_status, p_event_class_rec);
1089 
1090     IF (g_level_procedure >= g_current_runtime_level ) THEN
1091       FND_LOG.STRING(g_level_procedure,
1092                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Discard_Tax_Only_Lines.END',
1093                      'ZX_TRL_PUB_PKG: Discard_Tax_Only_Lines (-)'||x_return_status);
1094     END IF;
1095 
1096   EXCEPTION
1097     WHEN FND_API.G_EXC_ERROR THEN
1098       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1099       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1100         FND_LOG.STRING(g_level_unexpected,
1101                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Discard_Tax_Only_Lines',
1102                        'Return Status = ' || x_return_status);
1103       END IF;
1104 
1105     WHEN OTHERS THEN
1106       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1107       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1108 
1109       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1110         FND_LOG.STRING(g_level_unexpected,
1111                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Discard_Tax_Only_Lines',
1112                         l_error_buffer);
1113       END IF;
1114 
1115   END Discard_Tax_Only_Lines;
1116 
1117   PROCEDURE Update_GL_Date
1118        (p_gl_date       IN            DATE,
1119         x_return_status    OUT NOCOPY VARCHAR2) IS
1120 
1121     l_error_buffer  VARCHAR2(100);
1122 
1123   BEGIN
1124     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1125 
1126     IF (g_level_procedure >= g_current_runtime_level ) THEN
1127       FND_LOG.STRING(g_level_procedure,
1128                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_GL_Date.BEGIN',
1129                      'ZX_TRL_PUB_PKG: Update_GL_Date (+)');
1130     END IF;
1131 
1132     ZX_TRL_MANAGE_TAX_PKG.Update_GL_Date (p_gl_date, x_return_status);
1133 
1134 
1135     IF (g_level_procedure >= g_current_runtime_level ) THEN
1136       FND_LOG.STRING(g_level_procedure,
1137                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_GL_Date.END',
1138                      'ZX_TRL_PUB_PKG: Update_GL_Date (-)'||x_return_status);
1139     END IF;
1140 
1141   EXCEPTION
1142     WHEN FND_API.G_EXC_ERROR THEN
1143       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1144       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1145         FND_LOG.STRING(g_level_unexpected,
1146                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_GL_Date',
1147                        'Return Status = ' || x_return_status);
1148       END IF;
1149 
1150     WHEN OTHERS THEN
1151       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1152       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1153 
1154       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1155         FND_LOG.STRING(g_level_unexpected,
1156                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_GL_Date',
1157                         l_error_buffer);
1158       END IF;
1159 
1160   END Update_GL_Date;
1161 
1162   PROCEDURE Update_Exchange_Rate
1163        (p_event_class_rec         IN            ZX_API_PUB.EVENT_CLASS_REC_TYPE,
1164         x_return_status              OUT NOCOPY VARCHAR2) IS
1165 
1166     l_error_buffer  VARCHAR2(100);
1167 
1168   BEGIN
1169     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1170 
1171     IF (g_level_procedure >= g_current_runtime_level ) THEN
1172       FND_LOG.STRING(g_level_procedure,
1173                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Exchange_Rate.BEGIN',
1174                      'ZX_TRL_PUB_PKG: Update_Exchange_Rate (+)');
1175     END IF;
1176 
1177     ZX_TRL_MANAGE_TAX_PKG.Update_Exchange_Rate (p_event_class_rec,
1178                                                   x_return_status);
1179 
1180 
1181     IF (g_level_procedure >= g_current_runtime_level ) THEN
1182       FND_LOG.STRING(g_level_procedure,
1183                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Exchange_Rate.END',
1184                      'ZX_TRL_PUB_PKG: Update_Exchange_Rate (-)'||x_return_status);
1185     END IF;
1186 
1187   EXCEPTION
1188     WHEN FND_API.G_EXC_ERROR THEN
1189       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1190       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1191         FND_LOG.STRING(g_level_unexpected,
1192                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Exchange_Rate',
1193                        'Return Status = ' || x_return_status);
1194       END IF;
1195 
1196     WHEN OTHERS THEN
1197       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1198       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1199 
1200       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1201         FND_LOG.STRING(g_level_unexpected,
1202                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Update_Exchange_Rate',
1203                         l_error_buffer);
1204       END IF;
1205 
1206   END Update_Exchange_Rate;
1207 
1208 ------------------------------------------------------------------------------
1209 --  PUBLIC PROCEDURE
1210 --  delete_tax_lines_and_dists
1211 --
1212 --  DESCRIPTION
1213 --  Delete all the detail tax lines and distributions of the passed-in
1214 --  transaction line from zx_lines and zx_rec_nrec_dist.
1215 ------------------------------------------------------------------------------
1216 
1217 PROCEDURE delete_tax_lines_and_dists
1218 (
1219     p_application_id       IN           NUMBER,
1220     p_entity_code          IN           VARCHAR2,
1221     p_event_class_code     IN           VARCHAR2,
1222     p_trx_id               IN           NUMBER,
1223     p_trx_line_id          IN           NUMBER,
1224     p_trx_level_type       IN           VARCHAR2,
1225     x_return_status        OUT NOCOPY   VARCHAR2
1226 ) IS
1227 
1228  CURSOR c_get_summary_flags IS
1229    SELECT summarization_flag,
1230           retain_summ_tax_line_id_flag
1231      FROM zx_evnt_cls_mappings
1232     WHERE event_class_code = p_event_class_code
1233       AND application_id   = p_application_id
1234       AND entity_code      = p_entity_code;
1235 
1236  l_summarization_flag            VARCHAR2(1);
1237  l_retain_summ_tax_line_id_flag  VARCHAR2(1);
1238 
1239  l_error_buffer VARCHAR2(100);
1240 
1241 BEGIN
1242   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1243 
1244   IF (g_level_procedure >= g_current_runtime_level ) THEN
1245     FND_LOG.STRING(g_level_procedure,
1246                    'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists.BEGIN',
1247                    'ZX_TRL_PUB_PKG: delete_tax_lines_and_dists (+)');
1248   END IF;
1249   x_return_status := FND_API.G_RET_STS_SUCCESS;
1250 
1251   -- delete the distributions
1252   DELETE FROM zx_rec_nrec_dist
1253         WHERE application_id     = p_application_id
1254           AND entity_code        = p_entity_code
1255           AND event_class_code   = p_event_class_code
1256           AND trx_id             = p_trx_id
1257           AND trx_line_id        = p_trx_line_id
1258           AND trx_level_type     = p_trx_level_type;
1259 
1260   OPEN c_get_summary_flags;
1261   FETCH c_get_summary_flags into l_summarization_flag, l_retain_summ_tax_line_id_flag;
1262   CLOSE c_get_summary_flags;
1263 
1264   -- delete the tax detail lines
1265   DELETE FROM zx_lines
1266         WHERE application_id     = p_application_id
1267           AND entity_code        = p_entity_code
1268           AND event_class_code   = p_event_class_code
1269           AND trx_id             = p_trx_id
1270           AND trx_line_id        = p_trx_line_id
1271           AND trx_level_type     = p_trx_level_type;
1272 
1273   IF l_summarization_flag = 'Y' THEN
1274 
1275     -- for current phase, just delete all the summary tax lines
1276     -- for this transaction and recreate
1277 
1278     -- delete the summary tax lines for the transaction
1279     DELETE FROM zx_lines_summary
1280       WHERE application_id     = p_application_id
1281         AND entity_code        = p_entity_code
1282         AND event_class_code   = p_event_class_code
1283         AND trx_id             = p_trx_id;
1284 
1285     ZX_TRL_MANAGE_TAX_PKG.create_summary_lines_del_evnt(
1286       p_application_id    => p_application_id,
1287       p_entity_code       => p_entity_code,
1288       p_event_class_code  => p_event_class_code,
1289       p_trx_id            => p_trx_id,
1290       p_trx_line_id       => p_trx_line_id,
1291       p_trx_level_type    => p_trx_level_type,
1292       p_retain_summ_tax_line_id_flag
1293                           => l_retain_summ_tax_line_id_flag,
1294       x_return_status     => x_return_status
1295     );
1296     IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1297       IF (g_level_error >= g_current_runtime_level ) THEN
1298         FND_LOG.STRING(g_level_error,
1299                'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists',
1300                'MRC Lines: Incorrect return_status after calling ' ||
1301                'ZX_TRL_MANAGE_TAX_PKG.create_summary_from_zx_liness()');
1302         FND_LOG.STRING(g_level_error,
1303                'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists',
1304                'RETURN_STATUS = ' || x_return_status);
1305         FND_LOG.STRING(g_level_error,
1306                'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists.END',
1307                'ZX_TRL_PUB_PKG.delete_tax_lines_and_dists(-)');
1308       END IF;
1309       RETURN;
1310     END IF;
1311 
1312   END IF;
1313 
1314   IF (g_level_procedure >= g_current_runtime_level ) THEN
1315     FND_LOG.STRING(g_level_procedure,
1316                    'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists.END',
1317                    'ZX_TRL_PUB_PKG: delete_tax_lines_and_dists (-)'||x_return_status);
1318   END IF;
1319 
1320   EXCEPTION
1321     WHEN FND_API.G_EXC_ERROR THEN
1322       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1323       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1324         FND_LOG.STRING(g_level_unexpected,
1325                        'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists',
1326                        'Return Status = ' || x_return_status);
1327       END IF;
1328 
1329     WHEN OTHERS THEN
1330       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1331       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1332 
1333       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1334         FND_LOG.STRING(g_level_unexpected,
1335                        'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_lines_and_dists',
1336                         l_error_buffer);
1337       END IF;
1338 
1339 END delete_tax_lines_and_dists;
1340 
1341 ------------------------------------------------------------------------------
1342 --  PUBLIC PROCEDURE
1343 --  delete_tax_dists
1344 --
1345 --  DESCRIPTION
1346 --  Delete all the detail tax distributions of the passed-in transaction
1347 --  line from zx_rec_nrec_dist.
1348 ------------------------------------------------------------------------------
1349 
1350 PROCEDURE delete_tax_dists
1351 (
1352     p_application_id       IN           NUMBER,
1353     p_entity_code          IN           VARCHAR2,
1354     p_event_class_code     IN           VARCHAR2,
1355     p_trx_id               IN           NUMBER,
1356     p_trx_line_id          IN           NUMBER,
1357     p_trx_level_type       IN           VARCHAR2,
1358     x_return_status        OUT NOCOPY   VARCHAR2
1359 ) IS
1360 
1361  l_error_buffer VARCHAR2(100);
1362 
1363 BEGIN
1364   g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1365 
1366   IF (g_level_procedure >= g_current_runtime_level ) THEN
1367     FND_LOG.STRING(g_level_procedure,
1368                    'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_dists.BEGIN',
1369                    'ZX_TRL_PUB_PKG: delete_tax_dists (+)');
1370   END IF;
1371   x_return_status := FND_API.G_RET_STS_SUCCESS;
1372 
1373   DELETE FROM zx_rec_nrec_dist
1374         WHERE application_id     = p_application_id
1375           AND entity_code        = p_entity_code
1376           AND event_class_code   = p_event_class_code
1377           AND trx_id             = p_trx_id
1378           AND trx_line_id        = p_trx_line_id
1379           AND trx_level_type     = p_trx_level_type;
1380 
1381   IF SQL%ROWCOUNT > 0 THEN
1382     UPDATE zx_lines
1383        SET process_for_recovery_flag   = 'Y',  -- DECODE(L.Reporting_Only_Flag, 'N', 'Y', 'N')
1384            rec_tax_amt                 = NULL,
1385            rec_tax_amt_tax_curr        = NULL,
1386            rec_tax_amt_funcl_curr      = NULL,
1387            nrec_tax_amt                = NULL,
1388            nrec_tax_amt_tax_curr       = NULL,
1389            nrec_tax_amt_funcl_curr     = NULL
1390      WHERE application_id     = p_application_id
1391        AND entity_code        = p_entity_code
1392        AND event_class_code   = p_event_class_code
1393        AND trx_id             = p_trx_id
1394        AND trx_line_id        = p_trx_line_id
1395        AND trx_level_type     = p_trx_level_type;
1396   END IF;
1397 
1398   IF (g_level_procedure >= g_current_runtime_level ) THEN
1399     FND_LOG.STRING(g_level_procedure,
1400                    'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_dists.END',
1401                    'ZX_TRL_PUB_PKG: delete_tax_dists (-)'||x_return_status);
1402   END IF;
1403 
1404   EXCEPTION
1405     WHEN FND_API.G_EXC_ERROR THEN
1406       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1407       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1408         FND_LOG.STRING(g_level_unexpected,
1409                        'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_dists',
1410                        'Return Status = ' || x_return_status);
1411       END IF;
1412 
1413     WHEN OTHERS THEN
1414       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1415       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1416 
1417       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1418         FND_LOG.STRING(g_level_unexpected,
1419                        'ZX.PLSQL.ZX_TRL_PUB_PKG.delete_tax_dists',
1420                         l_error_buffer);
1421       END IF;
1422 
1423 END delete_tax_dists;
1424 
1425 
1426 ------------------------------------------------------------------------------
1427 --  PRIVATE PROCEDURE
1428 --  Check_Frozen_Tax_Dists
1429 --
1430 --  DESCRIPTION
1431 --  To run a validation to find if any frozen tax distribution is deleted
1432 --  from ZX_REC_NREC_DIST while tax processing
1433 ------------------------------------------------------------------------------
1434 
1435   PROCEDURE Check_Frozen_Tax_Dists
1436        (x_return_status     OUT NOCOPY VARCHAR2,
1437         p_event_class_rec   IN  ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
1438 
1439     l_return_status           VARCHAR2(1);
1440     l_error_buffer            VARCHAR2(200);
1441     l_msg_context_info_rec    ZX_API_PUB.CONTEXT_INFO_REC_TYPE;
1442     l_prev_trx_id             NUMBER;
1443 
1444     -- Cursors added for Frozen Tax Deleted Check --
1445     -- bug 14198788 driving queries from zx_trx_headers_gt
1446     CURSOR get_deleted_frzn_dist IS
1447     SELECT /*+ leading(headers_gt) use_nl(AID) */
1448            DISTINCT
1449            aid.invoice_id           trx_id,
1450            aid.detail_tax_dist_id   tax_dist_id,
1451            aid.summary_tax_line_id  summary_tax_line_id
1452       FROM ap_invoice_distributions_all aid,
1453            zx_trx_headers_gt headers_gt
1454      WHERE headers_gt.event_class_code = p_event_class_rec.event_class_code
1455        AND headers_gt.entity_code = p_event_class_rec.entity_code
1456        AND headers_gt.application_id = p_event_class_rec.application_id
1457        AND aid.invoice_id = headers_gt.trx_id
1458        AND aid.line_type_lookup_code IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV')
1459        AND (aid.accounting_event_id IS NOT NULL
1460             OR NVL(aid.match_status_flag,'N') IN ('A','T')
1461             OR NVL(aid.posted_flag,'N') = 'Y'
1462             OR NVL(aid.encumbered_flag, 'N') IN ('Y','D','W','X'))
1463        AND NOT EXISTS(SELECT /*+ NO_UNNEST  */ 'Tax Distributions'
1464                         FROM zx_rec_nrec_dist zd
1465                        WHERE zd.rec_nrec_tax_dist_id = aid.detail_tax_dist_id
1466                          AND NVL(zd.self_assessed_flag, 'N') = 'N');
1467 
1468     -- bug 14198788 driving queries from zx_trx_headers_gt
1469     CURSOR get_deleted_frzn_sa_dist IS
1470     SELECT /*+ leading(headers_gt) use_nl(ASAD) */
1471            DISTINCT
1472            asad.invoice_id           trx_id,
1473            asad.detail_tax_dist_id   tax_dist_id,
1474            asad.summary_tax_line_id  summary_tax_line_id
1475       FROM ap_self_assessed_tax_dist_all asad,
1476            zx_trx_headers_gt headers_gt
1477      WHERE headers_gt.event_class_code = p_event_class_rec.event_class_code
1478        AND headers_gt.entity_code = p_event_class_rec.entity_code
1479        AND headers_gt.application_id = p_event_class_rec.application_id
1480        AND asad.invoice_id = headers_gt.trx_id
1481        AND asad.line_type_lookup_code IN ('NONREC_TAX','REC_TAX')
1482        AND (asad.accounting_event_id IS NOT NULL
1483             OR NVL(asad.match_status_flag,'N') IN ('A','T')
1484             OR NVL(asad.posted_flag,'N') = 'Y'
1485             OR NVL(asad.encumbered_flag, 'N') IN ('Y','D','W','X'))
1486        AND NOT EXISTS(SELECT /*+ NO_UNNEST */ 'Tax Distributions'
1487                         FROM zx_rec_nrec_dist zd
1488                        WHERE zd.rec_nrec_tax_dist_id = asad.detail_tax_dist_id
1489                          AND NVL(zd.self_assessed_flag, 'N') = 'Y');
1490 
1491     TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1492     l_trx_id_tbl               num_tbl_type;
1493     l_tax_dist_id_tbl          num_tbl_type;
1494     l_summary_tax_line_id_tbl  num_tbl_type;
1495 
1496   BEGIN
1497     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1498 
1499     IF (g_level_procedure >= g_current_runtime_level ) THEN
1500       FND_LOG.STRING(g_level_procedure,
1501                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists.BEGIN',
1502                      'ZX_TRL_PUB_PKG: Check_Frozen_Tax_Dists (+)');
1503     END IF;
1504 
1505     --  Initialize API return status to success
1506     x_return_status := FND_API.G_RET_STS_SUCCESS;
1507 
1508     l_msg_context_info_rec.application_id := p_event_class_rec.application_id;
1509     l_msg_context_info_rec.entity_code := p_event_class_rec.entity_code;
1510     l_msg_context_info_rec.event_class_code := p_event_class_rec.event_class_code;
1511     l_msg_context_info_rec.trx_id := NULL;
1512     l_msg_context_info_rec.trx_line_id := NULL;
1513     l_msg_context_info_rec.trx_level_type := NULL;
1514     l_msg_context_info_rec.summary_tax_line_number := NULL;
1515     l_msg_context_info_rec.tax_line_id := NULL;
1516     l_msg_context_info_rec.trx_line_dist_id := NULL;
1517 
1518     -- Preventive check to find deleted frozen tax dists --
1519     IF (g_level_procedure >= g_current_runtime_level ) THEN
1520       FND_LOG.STRING(g_level_procedure,
1521            'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1522            'Start : Frozen Tax Distributions deleted Validation : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'));
1523     END IF;
1524 
1525     l_prev_trx_id := -99;
1526     l_trx_id_tbl.DELETE;
1527     l_tax_dist_id_tbl.DELETE;
1528     l_summary_tax_line_id_tbl.DELETE;
1529 
1530     -- Frozen dist deleted check for Normal Tax Distributions
1531     OPEN get_deleted_frzn_dist;
1532     FETCH get_deleted_frzn_dist BULK COLLECT
1533      INTO l_trx_id_tbl,
1534           l_tax_dist_id_tbl,
1535           l_summary_tax_line_id_tbl;
1536     CLOSE get_deleted_frzn_dist;
1537 
1538     IF l_trx_id_tbl.COUNT > 0 THEN
1539       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1540       l_error_buffer := 'System is trying to delete frozen tax distributions. '||
1541                         'Unable to complete Tax Processing.';
1542 
1543       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1544         FND_LOG.STRING(g_level_unexpected,
1545                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1546                        'ERROR: '||l_error_buffer);
1547         FND_LOG.STRING(g_level_unexpected,
1548                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1549                        'Following frozen tax distributions deleted from ZX_REC_NREC_DIST:');
1550       END IF;
1551 
1552       FOR i IN NVL(l_trx_id_tbl.FIRST,0)..NVL(l_trx_id_tbl.LAST,-99) LOOP
1553         IF l_prev_trx_id <> l_trx_id_tbl(i) THEN
1554           l_msg_context_info_rec.trx_id := l_trx_id_tbl(i);
1555 
1556           FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_TEXT');
1557           FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_error_buffer);
1558           ZX_API_PUB.Add_Msg(l_msg_context_info_rec);
1559         END IF;
1560         l_prev_trx_id := l_trx_id_tbl(i);
1561 
1562         IF (g_level_unexpected >= g_current_runtime_level ) THEN
1563            FND_LOG.STRING(g_level_unexpected,
1564                           'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1565                           TO_CHAR(i)||'. Trx_Id: '||TO_CHAR(l_trx_id_tbl(i))||
1566                           ', Rec_Nrec_Tax_Dist_Id: '||TO_CHAR(l_tax_dist_id_tbl(i))||
1567                           ', Summary_Tax_Line_Id: '||TO_CHAR(l_summary_tax_line_id_tbl(i)));
1568         END IF;
1569 
1570       END LOOP;
1571     END IF;
1572 
1573     l_prev_trx_id := -99;
1574     l_trx_id_tbl.DELETE;
1575     l_tax_dist_id_tbl.DELETE;
1576     l_summary_tax_line_id_tbl.DELETE;
1577 
1578     -- Frozen dist deleted check for Self Assessed Tax Distributions
1579     OPEN get_deleted_frzn_sa_dist;
1580     FETCH get_deleted_frzn_sa_dist BULK COLLECT
1581      INTO l_trx_id_tbl,
1582           l_tax_dist_id_tbl,
1583           l_summary_tax_line_id_tbl;
1584     CLOSE get_deleted_frzn_sa_dist;
1585 
1586     IF l_trx_id_tbl.COUNT > 0 THEN
1587       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1588       l_error_buffer := 'System is trying to delete self-assessed frozen tax distributions. '||
1589                         'Unable to complete Tax Processing.';
1590 
1591       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1592         FND_LOG.STRING(g_level_unexpected,
1593                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1594                        'ERROR: '||l_error_buffer);
1595         FND_LOG.STRING(g_level_unexpected,
1596                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1597                        'Following self-assessed frozen tax distributions deleted from ZX_REC_NREC_DIST:');
1598       END IF;
1599 
1600       FOR i IN NVL(l_trx_id_tbl.FIRST,0)..NVL(l_trx_id_tbl.LAST,-99) LOOP
1601         IF l_prev_trx_id <> l_trx_id_tbl(i) THEN
1602           l_msg_context_info_rec.trx_id := l_trx_id_tbl(i);
1603 
1604           FND_MESSAGE.SET_NAME('ZX','ZX_GENERIC_TEXT');
1605           FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_error_buffer);
1606           ZX_API_PUB.Add_Msg(l_msg_context_info_rec);
1607         END IF;
1608         l_prev_trx_id := l_trx_id_tbl(i);
1609 
1610         IF (g_level_unexpected >= g_current_runtime_level ) THEN
1611            FND_LOG.STRING(g_level_unexpected,
1612                           'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1613                           TO_CHAR(i)||'. Trx_Id: '||TO_CHAR(l_trx_id_tbl(i))||
1614                           ', Rec_Nrec_Tax_Dist_Id: '||TO_CHAR(l_tax_dist_id_tbl(i))||
1615                           ', Summary_Tax_Line_Id: '||TO_CHAR(l_summary_tax_line_id_tbl(i)));
1616         END IF;
1617 
1618       END LOOP;
1619     END IF;
1620 
1621     IF (g_level_procedure >= g_current_runtime_level ) THEN
1622       FND_LOG.STRING(g_level_procedure,
1623            'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1624            'End : Frozen Tax Distributions deleted Validation : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'));
1625     END IF;
1626 
1627     IF (g_level_procedure >= g_current_runtime_level ) THEN
1628       FND_LOG.STRING(g_level_procedure,
1629                      'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists.END',
1630                      'ZX_TRL_PUB_PKG: Check_Frozen_Tax_Dists(-)'||x_return_status);
1631     END IF;
1632 
1633   EXCEPTION
1634     WHEN OTHERS THEN
1635       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1636       l_error_buffer  := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1637 
1638       IF (g_level_unexpected >= g_current_runtime_level ) THEN
1639         FND_LOG.STRING(g_level_unexpected,
1640                        'ZX.PLSQL.ZX_TRL_PUB_PKG.Check_Frozen_Tax_Dists',
1641                         l_error_buffer);
1642       END IF;
1643   END Check_Frozen_Tax_Dists;
1644 
1645 --   Package constructor
1646 --
1647 -------------------------------------------------------------------------------
1648 
1649 END ZX_TRL_PUB_PKG;