[Home] [Help]
PACKAGE BODY: APPS.ZX_TDS_OFFSET_TAX_DETM_PKG
Source
1 package body ZX_TDS_OFFSET_TAX_DETM_PKG as
2 /* $Header: zxdioffsettxpkgb.pls 120.47.12020000.2 2012/07/23 12:53:02 srajapar noship $ */
3
4 PROCEDURE get_offset_info(
5 p_tax_rate_code IN ZX_RATES_B.TAX_RATE_CODE%TYPE,
6 p_tax IN ZX_TAXES_B.TAX%TYPE,
7 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
8 p_tax_status_code IN ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
9 p_tax_determine_date IN ZX_DETAIL_TAX_LINES_GT.TAX_DETERMINE_DATE%TYPE,
10 p_tax_jurisdiction_code IN ZX_JURISDICTIONS_B.tax_jurisdiction_code%TYPE,
11 p_tax_class IN ZX_RATES_B.TAX_CLASS%TYPE,
12 p_offset_tax_rate_id OUT NOCOPY ZX_RATES_B.TAX_RATE_ID%TYPE,
13 p_tax_rate OUT NOCOPY ZX_LINES.TAX_RATE%TYPE,
14 p_return_status OUT NOCOPY VARCHAR2,
15 p_error_buffer OUT NOCOPY VARCHAR2);
16
17 PROCEDURE set_null_columns(
18 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE);
19
20 PROCEDURE set_flg_columns(
21 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE);
22
23 PROCEDURE set_amt_columns(
24 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
25 p_parent_tax_rate IN VARCHAR2,
26 p_initial_tax_rate IN NUMBER,
27 p_return_status OUT NOCOPY VARCHAR2,
28 p_error_buffer OUT NOCOPY VARCHAR2);
29
30
31 PROCEDURE get_tax_status_id(
32 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
33 p_tax IN ZX_TAXES_B.TAX%TYPE,
34 p_tax_status_code IN ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
35 p_tax_determine_date IN ZX_DETAIL_TAX_LINES_GT.TAX_DETERMINE_DATE%TYPE,
36 p_tax_status_id OUT NOCOPY ZX_STATUS_B.TAX_STATUS_ID%TYPE,
37 p_return_status OUT NOCOPY VARCHAR2,
38 p_error_buffer OUT NOCOPY VARCHAR2);
39
40 PROCEDURE get_tax_id(
41 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
42 p_tax IN ZX_TAXES_B.TAX%TYPE,
43 p_tax_determine_date IN ZX_DETAIL_TAX_LINES_GT.TAX_DETERMINE_DATE%TYPE,
44 p_tax_id OUT NOCOPY ZX_TAXES_B.TAX_ID%TYPE,
45 p_return_status OUT NOCOPY VARCHAR2,
46 p_error_buffer OUT NOCOPY VARCHAR2);
47
48 PROCEDURE get_old_offset_tax_line_id(
49 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
50 p_offset_tax_line_rec IN ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
51 p_old_offset_tax_line_id OUT NOCOPY ZX_LINES.TAX_LINE_ID%TYPE,
52 p_return_status OUT NOCOPY VARCHAR2,
53 p_error_buffer OUT NOCOPY VARCHAR2);
54
55 PROCEDURE create_offset_tax_line(
56 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
57 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
58 p_offset_tax_rate_code IN ZX_RATES_B.TAX_RATE_CODE%TYPE,
59 p_offset_tax_rate_id IN ZX_RATES_B.TAX_RATE_ID%TYPE,
60 p_tax_rate IN ZX_LINES.TAX_RATE%TYPE,
61 p_initial_tax_rate IN ZX_LINES.TAX_RATE%TYPE,
62 p_offset_tax IN ZX_TAXES_B.TAX%TYPE,
63 p_offset_status_code IN ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
64 p_return_status OUT NOCOPY VARCHAR2,
65 p_error_buffer OUT NOCOPY VARCHAR2);
66
67 g_current_runtime_level NUMBER;
68 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
69 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
70 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
71 g_level_error CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
72
73 ----------------------------------------------------------------------
74 -- PUBLIC PROCEDURE
75 -- PROCESS_OFFSET_TAX
76 --
77 -- DESCRIPTION
78 --
79 -- This procedure is the entry point to offset tax determination process
80
81 PROCEDURE PROCESS_OFFSET_TAX(
82 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
83 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
84 p_return_status OUT NOCOPY VARCHAR2,
85 p_error_buffer OUT NOCOPY VARCHAR2)
86
87 IS
88 l_tax_rate_id ZX_RATES_B.TAX_RATE_ID%TYPE;
89 l_offset_tax_rate_id ZX_RATES_B.TAX_RATE_ID%TYPE;
90 l_offset_tax_rate ZX_LINES.TAX_RATE%TYPE;
91 l_initial_tax_rate ZX_LINES.TAX_RATE%TYPE;
92 l_offset_tax ZX_TAXES_B.TAX%TYPE;
93 l_offset_tax_regime_code ZX_REGIMES_B.TAX_REGIME_CODE%TYPE;
94 l_offset_tax_status_code ZX_STATUS_B.TAX_STATUS_CODE%TYPE;
95 l_offset_tax_rate_code ZX_RATES_B.OFFSET_TAX_RATE_CODE%TYPE;
96 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
97 l_tax_class ZX_RATES_B.TAX_CLASS%TYPE;
98
99 BEGIN
100 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
101
102 IF (g_level_procedure >= g_current_runtime_level) THEN
103 FND_LOG.STRING(g_level_procedure,
104 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.PROCESS_OFFSET_TAX.BEGIN',
105 'ZX_TDS_OFFSET_TAX_DETM_PKG: PROCESS_OFFSET_TAX(+)');
106 END IF;
107
108 p_return_status := FND_API.G_RET_STS_SUCCESS;
109 p_error_buffer := NULL;
110
111 -- Bug#5417753- determine tax_class value
112 IF p_event_class_rec.prod_family_grp_code = 'O2C' THEN
113 l_tax_class := 'OUTPUT';
114 ELSIF p_event_class_rec.prod_family_grp_code = 'P2P' THEN
115 l_tax_class := 'INPUT';
116 END IF;
117
118 l_tax_rate_id := p_offset_tax_line_rec.tax_rate_id;
119 l_offset_tax_regime_code := p_offset_tax_line_rec.tax_regime_code;
120
121
122 IF ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl.EXISTS(l_tax_rate_id) THEN
123 l_offset_tax_rate_code :=
124 ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl(l_tax_rate_id).offset_tax_rate_code;
125 l_offset_tax_status_code :=
126 ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl(l_tax_rate_id).offset_status_code;
127 l_offset_tax :=
128 ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl(l_tax_rate_id).offset_tax;
129
130 l_initial_tax_rate :=
131 ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl(l_tax_rate_id).percentage_rate;
132 ELSE
133
134 ZX_TDS_UTILITIES_PKG.get_tax_rate_info(
135 p_tax_rate_id => l_tax_rate_id,
136 p_tax_rate_rec => l_tax_rate_rec,
137 p_return_status => p_return_status,
138 p_error_buffer => p_error_buffer
139 );
140 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
141 IF (g_level_error >= g_current_runtime_level) THEN
142 FND_LOG.STRING(g_level_error,
143 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.PROCESS_OFFSET_TAX',
144 'Incorrect status returned from ZX_TDS_UTILITIES_PKG.get_tax_rate_info'||
145 'p_return_status = ' || p_return_status);
146 FND_LOG.STRING(g_level_error,
147 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.PROCESS_OFFSET_TAX',
148 'p_error_buffer = ' || p_error_buffer);
149 END IF;
150 RETURN;
151 END IF;
152
153 ZX_TDS_UTILITIES_PKG.g_tax_rate_info_tbl(l_tax_rate_id) := l_tax_rate_rec;
154
155 l_offset_tax_rate_code := l_tax_rate_rec.offset_tax_rate_code;
156 l_offset_tax_status_code := l_tax_rate_rec.offset_status_code;
157 l_offset_tax := l_tax_rate_rec.offset_tax;
158 l_initial_tax_rate := l_tax_rate_rec.percentage_rate;
159
160 END IF;
161
162 get_offset_info(l_offset_tax_rate_code,
163 l_offset_tax,
164 l_offset_tax_regime_code,
165 l_offset_tax_status_code,
166 p_offset_tax_line_rec.tax_determine_date,
167 p_offset_tax_line_rec.tax_jurisdiction_code,
168 l_tax_class,
169 l_offset_tax_rate_id,
170 l_offset_tax_rate,
171 p_return_status,
172 p_error_buffer);
173
174 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
175 --
176 -- bug#4893261- need to seed ZX_OFFSET_RATE_NOT_FOUND
177 --
178 IF p_return_status = FND_API.G_RET_STS_ERROR THEN
179 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.application_id :=
180 p_offset_tax_line_rec.application_id;
181 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.entity_code :=
182 p_offset_tax_line_rec.entity_code;
183 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.event_class_code :=
184 p_offset_tax_line_rec.event_class_code;
185 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_id :=
186 p_offset_tax_line_rec.trx_id;
187 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
188 p_offset_tax_line_rec.trx_line_id;
189 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
190 p_offset_tax_line_rec.trx_level_type;
191 FND_MESSAGE.SET_NAME('ZX','ZX_OFFSET_RATE_NOT_FOUND');
192 FND_MESSAGE.SET_TOKEN('TAX_REGIME', l_offset_tax_regime_code);
193 FND_MESSAGE.SET_TOKEN('TAX',l_offset_tax);
194 FND_MESSAGE.SET_TOKEN('TAX_STATUS',l_offset_tax_status_code);
195 FND_MESSAGE.SET_TOKEN('TAX_RATE_CODE', l_offset_tax_rate_code);
196 ZX_API_PUB.add_msg(
197 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
198
199 END IF;
200 RETURN;
201 END IF;
202
203 IF (g_level_procedure >= g_current_runtime_level) THEN
204 FND_LOG.STRING(g_level_procedure,
205 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.PROCESS_OFFSET_TAX.END',
206 'initial tax rate '||to_char(l_initial_tax_rate));
207 END IF;
208 create_offset_tax_line(p_offset_tax_line_rec,
209 p_event_class_rec,
210 l_offset_tax_rate_code,
211 l_offset_tax_rate_id,
212 l_offset_tax_rate,
213 l_initial_tax_rate,
214 l_offset_tax,
215 l_offset_tax_status_code,
216 p_return_status,
217 p_error_buffer);
218 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
219 RETURN;
220 END IF;
221
222 IF (g_level_procedure >= g_current_runtime_level) THEN
223 FND_LOG.STRING(g_level_procedure,
224 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.PROCESS_OFFSET_TAX.END',
225 'ZX_TDS_OFFSET_TAX_DETM_PKG: PROCESS_OFFSET_TAX(-)'||p_return_status);
226 END IF;
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
232
233 IF (g_level_unexpected >= g_current_runtime_level) THEN
234 FND_LOG.STRING(g_level_unexpected,
238
235 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.PROCESS_OFFSET_TAX',
236 p_error_buffer);
237 END IF;
239 END PROCESS_OFFSET_TAX;
240
241 -----------------------------------------------------------------------
242 -- PRIVATE PROCEDURE
243 -- get_offset_info
244 --
245 -- DESCRIPTION
246 -- This procedure gets offset tax info from ZX_RATES_B
247 --
248 PROCEDURE get_offset_info(
249 p_tax_rate_code IN ZX_RATES_B.TAX_RATE_CODE%TYPE,
250 p_tax IN ZX_TAXES_B.TAX%TYPE,
251 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
252 p_tax_status_code IN ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
253 p_tax_determine_date IN ZX_DETAIL_TAX_LINES_GT.TAX_DETERMINE_DATE%TYPE,
254 p_tax_jurisdiction_code IN ZX_JURISDICTIONS_B.tax_jurisdiction_code%TYPE,
255 p_tax_class IN ZX_RATES_B.TAX_CLASS%TYPE,
256 p_offset_tax_rate_id OUT NOCOPY ZX_RATES_B.TAX_RATE_ID%TYPE,
257 p_tax_rate OUT NOCOPY ZX_LINES.TAX_RATE%TYPE,
258 p_return_status OUT NOCOPY VARCHAR2,
259 p_error_buffer OUT NOCOPY VARCHAR2)
260
261 IS
262 --l_Rate_Type_Code ZX_RATES_B.Rate_Type_Code%TYPE;
263 --l_percentage_rate ZX_RATES_B.PERCENTAGE_RATE%TYPE;
264 --l_quantity_rate ZX_RATES_B.QUANTITY_RATE%TYPE;
265 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
266
267 /* Bug#5417753- use cache structure
268 CURSOR get_offset_info_csr
269 (c_tax_rate_code ZX_RATES_B.TAX_RATE_CODE%TYPE,
270 c_tax ZX_TAXES_B.TAX%TYPE,
271 c_tax_regime_code ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
272 c_tax_status_code ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
273 c_tax_determine_date ZX_DETAIL_TAX_LINES_GT.TAX_DETERMINE_DATE%TYPE)
274 IS
275
276 SELECT tax_rate_id,
277 Rate_Type_Code,
278 percentage_rate,
279 quantity_rate
280 FROM ZX_SCO_RATES_B_V
281 WHERE tax_rate_code = c_tax_rate_code AND
282 tax = c_tax AND
283 tax_regime_code = c_tax_regime_code AND
284 tax_status_code = c_tax_status_code AND
285 active_flag = 'Y' AND
286 c_tax_determine_date >= effective_from AND
287 (c_tax_determine_date <= effective_to OR
288 effective_to IS NULL)
289 ORDER BY subscription_level_code;
290 */
291
292 BEGIN
293 IF (g_level_statement >= g_current_runtime_level) THEN
294 FND_LOG.STRING(g_level_statement,
295 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_offset_info.BEGIN',
296 'ZX_TDS_OFFSET_TAX_DETM_PKG: get_offset_info(+)'||
297 'p_tax_rate_code = ' || p_tax_rate_code||
298 'p_tax = ' || p_tax||
299 'p_tax_regime_code = ' || p_tax_regime_code||
300 'p_tax_jurisdiction_code = ' || p_tax_jurisdiction_code ||
301 'p_tax_class = ' || p_tax_class ||
302 'p_tax_status_code = ' || p_tax_status_code);
303 END IF;
304
305 p_return_status := FND_API.G_RET_STS_SUCCESS;
306
307 /* Bug#5417753- use cache structure
308 OPEN get_offset_info_csr(p_tax_rate_code,
309 p_tax,
310 p_tax_regime_code,
311 p_tax_status_code,
312 p_tax_determine_date);
313 FETCH get_offset_info_csr INTO
314 p_offset_tax_rate_id,
315 l_Rate_Type_Code,
316 l_percentage_rate,
317 l_quantity_rate;
318 IF get_offset_info_csr%NOTFOUND THEN
319 p_return_status := FND_API.G_RET_STS_ERROR;
320 p_error_buffer := 'No data found for the specified tax rate ';
321 END IF;
322 CLOSE get_offset_info_csr;
323 */
324
325 ZX_TDS_UTILITIES_PKG.get_tax_rate_info(
326 p_tax_regime_code,
327 p_tax,
328 p_tax_jurisdiction_code,
329 p_tax_status_code,
330 p_tax_rate_code,
331 p_tax_determine_date,
332 p_tax_class,
333 l_tax_rate_rec,
334 p_return_status,
335 p_error_buffer);
336
337 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
338 p_offset_tax_rate_id := l_tax_rate_rec.tax_rate_id;
339
340 IF l_tax_rate_rec.Rate_Type_Code = 'PERCENTAGE' THEN
341 p_tax_rate := l_tax_rate_rec.percentage_rate;
342 ELSE
343 p_tax_rate := l_tax_rate_rec.quantity_rate;
344 END IF;
345 END IF;
346
347 IF (g_level_statement >= g_current_runtime_level) THEN
348 FND_LOG.STRING(g_level_statement,
349 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_offset_info.END',
350 'ZX_TDS_OFFSET_TAX_DETM_PKG: get_offset_info(-)'||
351 'p_tax_rate = ' || to_char(p_tax_rate));
352 END IF;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
358
359 IF (FND_LOG.LEVEL_UNEXPECTED >= g_current_runtime_level) THEN
360 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
361 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_offset_info',
362 'p_error_buffer = ' || p_error_buffer);
363 END IF;
364 END get_offset_info;
365
366 -----------------------------------------------------------------------
367 -- PRIVATE PROCEDURE
368 -- set_null_columns
369 --
370 -- DESCRIPTION
371 -- This procedure sets the values of columns to NULL
372
373 PROCEDURE set_null_columns(
374 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE)
375 IS
376 BEGIN
377 IF (g_level_statement >= g_current_runtime_level) THEN
378 FND_LOG.STRING(g_level_statement,
379 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_null_columns.BEGIN',
380 'ZX_TDS_OFFSET_TAX_DETM_PKG: set_null_columns(+)');
381 END IF;
382
383 p_offset_tax_line_rec.orig_tax_status_id := NULL;
384 p_offset_tax_line_rec.orig_tax_status_code := NULL;
385 p_offset_tax_line_rec.orig_tax_rate_id := NULL;
386 p_offset_tax_line_rec.orig_tax_rate_code := NULL;
387 p_offset_tax_line_rec.orig_tax_rate := NULL;
388 p_offset_tax_line_rec.orig_taxable_amt := NULL;
389 p_offset_tax_line_rec.orig_taxable_amt_tax_curr := NULL;
390 p_offset_tax_line_rec.orig_tax_amt := NULL;
391 p_offset_tax_line_rec.orig_tax_amt_tax_curr := NULL;
392 -- p_offset_tax_line_rec.offset_tax_line_number := NULL;
393 p_offset_tax_line_rec.created_by := NULL;
394 p_offset_tax_line_rec.creation_date := NULL;
395 p_offset_tax_line_rec.tax_line_id := NULL;
396 p_offset_tax_line_rec.last_manual_entry := NULL;
397 p_offset_tax_line_rec.tax_provider_id := NULL;
398 p_offset_tax_line_rec.tax_applicability_result_id := NULL;
399 p_offset_tax_line_rec.status_result_id := NULL;
400 p_offset_tax_line_rec.rate_result_id := NULL;
401 p_offset_tax_line_rec.basis_result_id := NULL;
402 p_offset_tax_line_rec.thresh_result_id := NULL;
403 p_offset_tax_line_rec.calc_result_id := NULL;
404 p_offset_tax_line_rec.direct_rate_result_id := NULL;
405 p_offset_tax_line_rec.tax_apportionment_line_number := NULL;
406 p_offset_tax_line_rec.summary_tax_line_id := NULL;
407 p_offset_tax_line_rec.tax_hold_code := NULL;
408 p_offset_tax_line_rec.tax_hold_released_code := NULL;
409 p_offset_tax_line_rec.legal_message_appl_2 := NULL;
410 p_offset_tax_line_rec.legal_message_status := NULL;
411 p_offset_tax_line_rec.legal_message_rate := NULL;
412 p_offset_tax_line_rec.legal_message_basis := NULL;
413 p_offset_tax_line_rec.legal_message_calc := NULL;
414 p_offset_tax_line_rec.legal_message_pos := NULL;
415 p_offset_tax_line_rec.legal_message_trn := NULL;
416
417 IF (g_level_statement >= g_current_runtime_level) THEN
418 FND_LOG.STRING(g_level_statement,
419 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_null_columns.END',
420 'ZX_TDS_OFFSET_TAX_DETM_PKG: set_null_columns(-)');
421 END IF;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 IF (g_level_unexpected >= g_current_runtime_level) THEN
426 FND_LOG.STRING(g_level_unexpected,
427 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_null_columns',
428 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
429 END IF;
430
431 END set_null_columns;
432
433 ----------------------------------------------------------------------
434 -- PRIVATE PROCEDURE
435 -- set_flg_columns
436 --
437 -- DESCRIPTION
438 -- This procedure initializes the value of flag columns
439
440 PROCEDURE set_flg_columns(
441 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE)
442 IS
443 BEGIN
444 IF (g_level_statement >= g_current_runtime_level) THEN
445 FND_LOG.STRING(g_level_statement,
446 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_flg_columns.BEGIN',
447 'ZX_TDS_OFFSET_TAX_DETM_PKG: set_flg_columns(+)');
448 END IF;
449
450 p_offset_tax_line_rec.Offset_Flag := 'Y'; --Bug6509867
451 p_offset_tax_line_rec.Compounding_Tax_Flag := 'N';
452 p_offset_tax_line_rec.Tax_Apportionment_Flag := 'N';
453 p_offset_tax_line_rec.Overridden_Flag := 'N';
454 p_offset_tax_line_rec.Manually_Entered_Flag := 'N';
455 p_offset_tax_line_rec.Reporting_Only_Flag := 'N';
456 p_offset_tax_line_rec.Freeze_Until_Overridden_Flag := 'N';
457 p_offset_tax_line_rec.Copied_From_Other_Doc_Flag := 'N';
458 p_offset_tax_line_rec.Recalc_Required_Flag := 'N';
459 p_offset_tax_line_rec.Settlement_Flag := 'N';
460 p_offset_tax_line_rec.Associated_Child_Frozen_Flag := 'N';
461 p_offset_tax_line_rec.Enforce_From_Natural_Acct_Flag := 'N';
462 p_offset_tax_line_rec.Historical_Flag := 'N';
463
464 -- should get from the parent line
465 -- p_offset_tax_line_rec.Process_For_Recovery_Flag := 'Y';
466
467 IF (g_level_statement >= g_current_runtime_level) THEN
468 FND_LOG.STRING(g_level_statement,
469 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_flg_columns.END',
470 'ZX_TDS_OFFSET_TAX_DETM_PKG: set_flg_columns(-)');
471 END IF;
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 IF (g_level_unexpected >= g_current_runtime_level) THEN
476 FND_LOG.STRING(g_level_unexpected,
477 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_flg_columns',
478 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
479 END IF;
480
481 END set_flg_columns;
482 ----------------------------------------------------------------------
483 -- PRIVATE PROCEDURE
484 -- set_amt_columns
485 --
486 -- DESCRIPTION
487 -- This procedure populates tax amount related columns
488
489 PROCEDURE set_amt_columns(
490 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
491 p_parent_tax_rate IN VARCHAR2,
492 p_initial_tax_rate IN NUMBER,
493 p_return_status OUT NOCOPY VARCHAR2,
494 p_error_buffer OUT NOCOPY VARCHAR2
495 )
496 IS
497 l_tax_id ZX_TAXES_B.TAX_ID%TYPE;
498 l_tax_min_acct_unit ZX_TAXES_B.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
499 l_tax_precision ZX_TAXES_B.TAX_PRECISION%TYPE;
500 BEGIN
501 IF (g_level_statement >= g_current_runtime_level) THEN
502 FND_LOG.STRING(g_level_statement,
503 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_amt_columns.BEGIN',
504 'ZX_TDS_OFFSET_TAX_DETM_PKG: set_amt_columns(+)');
505 END IF;
506
507 p_return_status := FND_API.G_RET_STS_SUCCESS;
508
509 --
510 -- taxable amounts would be same as the original tax line
511 -- do not need to change unrounded_taxable_amt,
512 -- taxable_amt, taxable_amt_tax_curr, taxable_amt_funcl_curr
513 --
514
515 --
516 -- prorated total amounts would be opposite of original tax line
517 --
518 p_offset_tax_line_rec.prd_total_tax_amt := - p_offset_tax_line_rec.prd_total_tax_amt;
519 p_offset_tax_line_rec.prd_total_tax_amt_tax_curr := - p_offset_tax_line_rec.prd_total_tax_amt_tax_curr;
520 p_offset_tax_line_rec.prd_total_tax_amt_funcl_curr := - p_offset_tax_line_rec.prd_total_tax_amt_funcl_curr;
521
522 --
523 -- check to see if need to recalculate tax related amounts
524 -- if offset tax rate is different
525 --
526
527 IF (p_offset_tax_line_rec.tax_rate + p_parent_tax_rate = 0 ) THEN
528 --
529 -- set all offset amounts to negative, no need to recalculate
530 --
531 p_offset_tax_line_rec.unrounded_tax_amt := - p_offset_tax_line_rec.unrounded_tax_amt;
532 p_offset_tax_line_rec.tax_amt := - p_offset_tax_line_rec.tax_amt;
533 p_offset_tax_line_rec.tax_amt_tax_curr := - p_offset_tax_line_rec.tax_amt_tax_curr;
534 p_offset_tax_line_rec.cal_tax_amt := p_offset_tax_line_rec.tax_amt;
535 p_offset_tax_line_rec.cal_tax_amt_tax_curr := p_offset_tax_line_rec.tax_amt_tax_curr;
536 p_offset_tax_line_rec.tax_amt_funcl_curr := - p_offset_tax_line_rec.tax_amt_funcl_curr;
537 p_offset_tax_line_rec.cal_tax_amt_funcl_curr := - p_offset_tax_line_rec.cal_tax_amt_funcl_curr;
538
539 RETURN;
540 END IF;
541
542 IF (g_level_statement >= g_current_runtime_level) THEN
543 FND_LOG.STRING(g_level_statement,
544 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_amt_columns.END',
545 'initia tax rate'||to_number(p_initial_tax_rate));
546 END IF;
547
548 IF (g_level_statement >= g_current_runtime_level) THEN
549 FND_LOG.STRING(g_level_statement,
550 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_amt_columns.END',
551 'offset tax rate'||to_number(p_offset_tax_line_rec.tax_rate));
552 END IF;
553 IF (p_offset_tax_line_rec.tax_rate + p_initial_tax_rate = 0 ) THEN
554 --
555 -- set all offset amounts to negative, no need to recalculate
556 --
557 p_offset_tax_line_rec.unrounded_tax_amt := - p_offset_tax_line_rec.unrounded_tax_amt;
558 p_offset_tax_line_rec.tax_amt := - p_offset_tax_line_rec.tax_amt;
559 p_offset_tax_line_rec.tax_amt_tax_curr := - p_offset_tax_line_rec.tax_amt_tax_curr;
560 p_offset_tax_line_rec.cal_tax_amt := p_offset_tax_line_rec.tax_amt;
561 p_offset_tax_line_rec.cal_tax_amt_tax_curr := p_offset_tax_line_rec.tax_amt_tax_curr;
562 p_offset_tax_line_rec.tax_amt_funcl_curr := - p_offset_tax_line_rec.tax_amt_funcl_curr;
563 p_offset_tax_line_rec.cal_tax_amt_funcl_curr := - p_offset_tax_line_rec.cal_tax_amt_funcl_curr;
564 p_offset_tax_line_rec.tax_rate := - p_parent_tax_rate;
565
566 RETURN;
567 END IF;
568 --
569 -- offset tax has a different rate, need to recalculate amounts
570 --
571
572 p_offset_tax_line_rec.unrounded_tax_amt :=
573 p_offset_tax_line_rec.unrounded_taxable_amt * p_offset_tax_line_rec.tax_rate/100;
574
575 --
576 -- tax amount
577 --
578 p_offset_tax_line_rec.tax_amt := p_offset_tax_line_rec.unrounded_tax_amt;
579
580 p_offset_tax_line_rec.tax_amt :=
581 ZX_TDS_TAX_ROUNDING_PKG.round_tax(
582 p_offset_tax_line_rec.tax_amt,
583 p_offset_tax_line_rec.Rounding_Rule_Code,
584 p_offset_tax_line_rec.minimum_accountable_unit,
585 p_offset_tax_line_rec.precision,
586 p_return_status,
587 p_error_buffer);
588
589
590 IF p_offset_tax_line_rec.mrc_tax_line_flag = 'N' THEN
591 --
592 -- tax amount tax currency
593 --
594 p_offset_tax_line_rec.tax_amt_tax_curr :=
595 p_offset_tax_line_rec.unrounded_tax_amt * p_offset_tax_line_rec.tax_currency_conversion_rate;
596
597 --
598 -- now round the tax amount tax currency
599 --
600
601 l_tax_id := p_offset_tax_line_rec.tax_id;
602 l_tax_precision := ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).tax_precision;
603 l_tax_min_acct_unit := ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(l_tax_id).minimum_accountable_unit;
604
605 p_offset_tax_line_rec.tax_amt_tax_curr :=
606 ZX_TDS_TAX_ROUNDING_PKG.round_tax(
607 p_offset_tax_line_rec.tax_amt_tax_curr,
608 p_offset_tax_line_rec.Rounding_Rule_Code,
609 l_tax_min_acct_unit,
610 l_tax_precision,
611 p_return_status,
612 p_error_buffer);
613
614 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
615 RETURN;
616 END IF;
617
618 --
619 -- functional currency
620 --
621
622 IF p_offset_tax_line_rec.currency_conversion_rate IS NOT NULL THEN
623 p_offset_tax_line_rec.tax_amt_funcl_curr :=
624 p_offset_tax_line_rec.unrounded_tax_amt * p_offset_tax_line_rec.currency_conversion_rate;
625
626 p_offset_tax_line_rec.tax_amt_funcl_curr :=
627 ZX_TDS_TAX_ROUNDING_PKG.round_tax_funcl_curr(
628 p_offset_tax_line_rec.tax_amt_funcl_curr,
629 p_offset_tax_line_rec.ledger_id,
630 p_return_status,
631 p_error_buffer);
632
633 END IF;
634 END IF;
635
636 --
637 -- calculated tax amounts
638 --
639 p_offset_tax_line_rec.cal_tax_amt := p_offset_tax_line_rec.tax_amt;
640 p_offset_tax_line_rec.cal_tax_amt_tax_curr := p_offset_tax_line_rec.tax_amt_tax_curr;
641 p_offset_tax_line_rec.cal_tax_amt_funcl_curr := p_offset_tax_line_rec.tax_amt_funcl_curr;
642
643 IF (g_level_statement >= g_current_runtime_level) THEN
644 FND_LOG.STRING(g_level_statement,
645 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_amt_columns.END',
646 'ZX_TDS_OFFSET_TAX_DETM_PKG: set_amt_columns(-)'||p_return_status);
647 END IF;
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
653
654 IF (g_level_unexpected >= g_current_runtime_level) THEN
655 FND_LOG.STRING(g_level_unexpected,
656 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.set_amt_columns',
657 p_error_buffer);
658 END IF;
659
660 END set_amt_columns;
661 -----------------------------------------------------------------------
662 -- PRIVATE PROCEDURE
663 -- get_tax_status_id
664 --
665 -- DESCRIPTION
666 -- This procedure gets tax status id from global cache structure based on
667 -- tax regime code, tax and tax status code
668 --
669 PROCEDURE get_tax_status_id(
670 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
671 p_tax IN ZX_TAXES_B.TAX%TYPE,
672 p_tax_status_code IN ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
673 p_tax_determine_date IN ZX_DETAIL_TAX_LINES_GT.TAX_DETERMINE_DATE%TYPE,
674 p_tax_status_id OUT NOCOPY ZX_STATUS_B.TAX_STATUS_ID%TYPE,
675 p_return_status OUT NOCOPY VARCHAR2,
676 p_error_buffer OUT NOCOPY VARCHAR2)
677
678 IS
679 l_status_rec ZX_TDS_UTILITIES_PKG.ZX_STATUS_INFO_REC;
680
681 BEGIN
682 IF (g_level_statement >= g_current_runtime_level) THEN
683 FND_LOG.STRING(g_level_statement,
684 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_tax_status_id.BEGIN',
685 'ZX_TDS_OFFSET_TAX_DETM_PKG: get_tax_status_id(+)');
686 END IF;
687 p_return_status := FND_API.G_RET_STS_SUCCESS;
688
689 ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info(
690 p_tax,
691 p_tax_regime_code,
692 p_tax_status_code,
693 p_tax_determine_date,
694 l_status_rec,
695 p_return_status,
696 p_error_buffer);
697
698 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
699 p_tax_status_id := l_status_rec.tax_status_id;
700 END IF;
701
702 IF (g_level_statement >= g_current_runtime_level) THEN
703 FND_LOG.STRING(g_level_statement,
704 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_tax_status_id.END',
705 'ZX_TDS_OFFSET_TAX_DETM_PKG: get_tax_status_id(-)');
706 END IF;
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
712
713 IF (g_level_unexpected >= g_current_runtime_level) THEN
714 FND_LOG.STRING(g_level_unexpected,
715 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_tax_status_id',
716 p_error_buffer);
717 END IF;
718
719 END get_tax_status_id;
720 -----------------------------------------------------------------------
721 -- PRIVATE PROCEDURE
722 -- get_tax_id
723 --
724 -- DESCRIPTION
725 -- This procedure gets tax id from zx_taxes based on
726 -- tax regime code and tax
727 --
728 PROCEDURE get_tax_id(
729 p_tax_regime_code IN ZX_REGIMES_B.TAX_REGIME_CODE%TYPE,
730 p_tax IN ZX_TAXES_B.TAX%TYPE,
731 p_tax_determine_date IN ZX_DETAIL_TAX_LINES_GT.TAX_DETERMINE_DATE%TYPE,
732 p_tax_id OUT NOCOPY ZX_TAXES_B.TAX_ID%TYPE,
733 p_return_status OUT NOCOPY VARCHAR2,
734 p_error_buffer OUT NOCOPY VARCHAR2)
735
736 IS
737 l_tax_rec ZX_TDS_UTILITIES_PKG.ZX_TAX_INFO_CACHE_REC;
738 BEGIN
739 IF (g_level_statement >= g_current_runtime_level) THEN
740 FND_LOG.STRING(g_level_statement,
741 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_tax_id.BEGIN',
742 'ZX_TDS_OFFSET_TAX_DETM_PKG: get_tax_id(+)');
743 END IF;
744
745 p_return_status := FND_API.G_RET_STS_SUCCESS;
746
747 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
748 p_tax_regime_code,
749 p_tax,
750 p_tax_determine_date,
751 l_tax_rec,
752 p_return_status,
753 p_error_buffer);
754
755 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
756 p_tax_id := l_tax_rec.tax_id;
757 END IF;
758
759 IF (g_level_statement >= g_current_runtime_level) THEN
760 FND_LOG.STRING(g_level_statement,
761 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_tax_id.END',
762 'ZX_TDS_OFFSET_TAX_DETM_PKG: get_tax_id(-)'||
763 'p_tax_id = ' || to_char(p_tax_id)||
764 'p_return_status = ' || p_return_status);
765 END IF;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
770 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
771
772 IF (g_level_unexpected >= g_current_runtime_level) THEN
773 FND_LOG.STRING(g_level_unexpected,
774 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_tax_id',
775 p_error_buffer);
776 END IF;
777
778 END get_tax_id;
779
780 ----------------------------------------------------------------------
781 -- PRIVATE PROCEDURE
782 -- get_old_offset_tax_line_id
783 --
784 -- DESCRIPTION
785 --
786 -- The procedure gets the tax line id of an offset tax line
787 -- from the repository based on the value of the tax line id
788 -- it links to
789
790 PROCEDURE get_old_offset_tax_line_id(
791 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
792 p_offset_tax_line_rec IN ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
793 p_old_offset_tax_line_id OUT NOCOPY ZX_LINES.TAX_LINE_ID%TYPE,
794 p_return_status OUT NOCOPY VARCHAR2,
795 p_error_buffer OUT NOCOPY VARCHAR2)
796 IS
797
798
799 CURSOR get_old_offset_tax_line_id_csr
800 -- (c_offset_link_to_tax_line_id ZX_LINES.OFFSET_LINK_TO_TAX_LINE_ID%TYPE)
801 IS
802 SELECT tax_line_id
803 FROM ZX_LINES
804 WHERE APPLICATION_ID = p_offset_tax_line_rec.APPLICATION_ID
805 AND ENTITY_CODE = p_offset_tax_line_rec.ENTITY_CODE
806 AND EVENT_CLASS_CODE = p_offset_tax_line_rec.EVENT_CLASS_CODE
807 AND trx_id = p_offset_tax_line_rec.trx_id
808 AND trx_line_id = p_offset_tax_line_rec.trx_line_id
809 AND trx_level_type = p_offset_tax_line_rec.trx_level_type
810 AND tax_regime_code = p_offset_tax_line_rec.tax_regime_code
811 AND offset_link_to_tax_line_id = p_offset_tax_line_rec.offset_link_to_tax_line_id;
812
813 BEGIN
814 IF (g_level_statement >= g_current_runtime_level) THEN
815 FND_LOG.STRING(g_level_statement,
816 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_old_offset_tax_line_id.BEGIN',
817 'ZX_TDS_OFFSET_TAX_DETM_PKG: get_old_offset_tax_line_id(+)'||
818 'p_offset_link_to_tax_line_id = ' ||
819 to_char(p_offset_tax_line_rec.offset_link_to_tax_line_id));
820 END IF;
821
822 p_return_status := FND_API.G_RET_STS_SUCCESS;
823
824 OPEN get_old_offset_tax_line_id_csr;
825 FETCH get_old_offset_tax_line_id_csr INTO p_old_offset_tax_line_id;
826 IF get_old_offset_tax_line_id_csr%NOTFOUND THEN
827 --
828 -- in this case, it is the first time this tax line has offset
829 -- tax associated with it, no offset tax line has been previously
830 -- created for this line
831 --
832 p_old_offset_tax_line_id := NULL;
833 END IF;
834 CLOSE get_old_offset_tax_line_id_csr;
835
836
837 IF (g_level_statement >= g_current_runtime_level) THEN
838 FND_LOG.STRING(g_level_statement,
839 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_old_offset_tax_line_id.END',
840 'ZX_TDS_OFFSET_TAX_DETM_PKG: get_old_offset_tax_line_id(-)'||
841 'p_old_offset_tax_line_id = ' ||
842 to_char(p_old_offset_tax_line_id)||
843 'p_return_status = ' || p_return_status);
844 END IF;
845
846 EXCEPTION
847 WHEN OTHERS THEN
848 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
849 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
850
851 IF (g_level_unexpected >= g_current_runtime_level) THEN
852 FND_LOG.STRING(g_level_unexpected,
853 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.get_old_offset_tax_line_id',
854 p_error_buffer);
855 END IF;
856
857 END get_old_offset_tax_line_id;
858
859 ----------------------------------------------------------------------
860 -- PRIVATE PROCEDURE
861 -- create_offset_tax_line
862 --
863 -- DESCRIPTION
864 --
865 -- The procedure is used to create an offset tax line for a main tax line
866 -- which has offset_flag = 'Y'
867
868 PROCEDURE create_offset_tax_line(
869 p_offset_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
870 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
871 p_offset_tax_rate_code IN ZX_RATES_B.TAX_RATE_CODE%TYPE,
872 p_offset_tax_rate_id IN ZX_RATES_B.TAX_RATE_ID%TYPE,
873 p_tax_rate IN ZX_LINES.TAX_RATE%TYPE,
874 p_initial_tax_rate IN ZX_LINES.TAX_RATE%TYPE,
875 p_offset_tax IN ZX_TAXES_B.TAX%TYPE,
876 p_offset_status_code IN ZX_STATUS_B.TAX_STATUS_CODE%TYPE,
877 p_return_status OUT NOCOPY VARCHAR2,
878 p_error_buffer OUT NOCOPY VARCHAR2)
879 IS
880
881 CURSOR get_tax_line_number_csr IS
882 SELECT NVL(MAX(tax_line_number), 0) + 1
883 FROM zx_lines
884 WHERE application_id = p_offset_tax_line_rec.application_id
885 AND event_class_code = p_offset_tax_line_rec.event_class_code
886 AND entity_code = p_offset_tax_line_rec.entity_code
887 AND trx_id = p_offset_tax_line_rec.trx_id
888 AND trx_line_id = p_offset_tax_line_rec.trx_line_id
889 AND trx_level_type = p_offset_tax_line_rec.trx_level_type;
890
891 l_parent_tax_rate ZX_LINES.TAX_RATE%TYPE;
892 l_old_offset_tax_line_id ZX_LINES.TAX_LINE_ID%TYPE;
893
894 BEGIN
895 IF (g_level_statement >= g_current_runtime_level) THEN
896 FND_LOG.STRING(g_level_statement,
897 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.create_offset_tax_line.BEGIN',
898 'ZX_TDS_OFFSET_TAX_DETM_PKG: create_offset_tax_line(+)');
899 END IF;
900
901 p_return_status := FND_API.G_RET_STS_SUCCESS;
902
903 --
904 -- set link to current non offset tax line
905 --
906 p_offset_tax_line_rec.offset_link_to_tax_line_id :=
907 p_offset_tax_line_rec.tax_line_id;
908 -- p_offset_tax_line_rec.tax_line_number :=
909 -- p_offset_tax_line_rec.offset_tax_line_number;
910
911 --
912 -- null out columns
913 --
914 set_null_columns(p_offset_tax_line_rec);
915
916 --
917 -- set flags
918 --
919 set_flg_columns(p_offset_tax_line_rec);
920
921 -- Added code to handle taxable_basis_formula mismatch --
922 --
923 -- populate taxable_basis_formula column
924 --
925 IF p_offset_tax_line_rec.manually_entered_flag = 'N' AND
926 (p_offset_tax_line_rec.taxable_basis_formula IS NULL OR
927 p_offset_tax_line_rec.taxable_basis_formula = 'PRORATED_TB')
928 THEN
929 p_offset_tax_line_rec.taxable_basis_formula := 'STANDARD_TB';
930 END IF;
931
932 --
933 -- populate tax_line_id and who columns
934 --
935 ZX_TDS_TAX_LINES_POPU_PKG.populate_mandatory_columns(
936 p_offset_tax_line_rec,
937 p_return_status,
938 p_error_buffer);
939 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
940 RETURN;
941 END IF;
942
943 --
944 -- keep the tax rate of the tax line which associated with this
945 -- offset tax line, it will be used to determine whether all tax amounts
946 -- related need to be recalculated if offset tax rate is different
947 --
948 l_parent_tax_rate := p_offset_tax_line_rec.tax_rate;
949
950 --
951 -- populate tax related info
952 --
953 p_offset_tax_line_rec.tax_rate_code := p_offset_tax_rate_code;
954 p_offset_tax_line_rec.tax_rate_id := p_offset_tax_rate_id;
955 p_offset_tax_line_rec.tax_rate := p_tax_rate;
956 p_offset_tax_line_rec.offset_tax_rate_code := NULL;
957
958 p_offset_tax_line_rec.tax := p_offset_tax;
959 get_tax_id(p_offset_tax_line_rec.tax_regime_code,
960 p_offset_tax,
961 p_offset_tax_line_rec.tax_determine_date,
962 p_offset_tax_line_rec.tax_id,
963 p_return_status,
964 p_error_buffer);
965 IF p_return_status <>FND_API.G_RET_STS_SUCCESS THEN
966 RETURN;
967 END IF;
968
969 p_offset_tax_line_rec.tax_status_code := p_offset_status_code;
970 get_tax_status_id(p_offset_tax_line_rec.tax_regime_code,
971 p_offset_tax,
972 p_offset_status_code,
973 p_offset_tax_line_rec.tax_determine_date,
974 p_offset_tax_line_rec.tax_status_id,
975 p_return_status,
976 p_error_buffer);
977 IF p_return_status <>FND_API.G_RET_STS_SUCCESS THEN
978 RETURN;
979 END IF;
980
981 p_offset_tax_line_rec.tax_type_code :=
982 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(p_offset_tax_line_rec.tax_id).tax_type_code;
983
984 --
985 -- populate amount columns
986 --
987 set_amt_columns(p_offset_tax_line_rec,
988 l_parent_tax_rate,
989 p_initial_tax_rate,
990 p_return_status,
991 p_error_buffer);
992
993 IF p_return_status <>FND_API.G_RET_STS_SUCCESS THEN
994 RETURN;
995 END IF;
996
997 -- bug 5580990: populate legal_reporting_status
998 IF p_event_class_rec.tax_reporting_flag = 'Y' THEN
999 p_offset_tax_line_rec.legal_reporting_status :=
1000 ZX_TDS_UTILITIES_PKG.g_tax_rec_tbl(
1001 p_offset_tax_line_rec.tax_id).legal_reporting_status_def_val;
1002 END IF;
1003
1004 --
1005 -- if tax line is updated or overridden, need to preserve the
1006 -- existing offset tax line id for this tax line since the offset
1007 -- tax line may have been accounted, update the new created offset
1008 -- tax line id with the existing offset tax line id in the
1009 -- repository for this tax line
1010 --
1011
1012 -- populate tax_line_number for OVERRIDE_TAX
1013 --
1014 IF NVL(p_offset_tax_line_rec.tax_event_type_code, 'A') = 'OVERRIDE_TAX' THEN
1015 OPEN get_tax_line_number_csr;
1016 FETCH get_tax_line_number_csr INTO p_offset_tax_line_rec.tax_line_number;
1017 CLOSE get_tax_line_number_csr;
1018 END IF;
1019
1020 -- Bug Fix 5417887
1021 -- IF (p_event_class_rec.tax_event_type_code = 'UPDATE' OR
1022 IF (p_offset_tax_line_rec.tax_event_type_code = 'UPDATE' OR
1023 p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX') THEN
1024 get_old_offset_tax_line_id(
1025 p_event_class_rec,
1026 p_offset_tax_line_rec,
1027 l_old_offset_tax_line_id,
1028 p_return_status,
1029 p_error_buffer);
1030
1031 IF p_return_status <>FND_API.G_RET_STS_SUCCESS THEN
1032 RETURN;
1033 END IF;
1034
1035 IF l_old_offset_tax_line_id IS NOT NULL THEN
1036 --
1037 -- overwrite the newly generated tax line id with
1038 -- the existing one in the repository
1039 --
1040 p_offset_tax_line_rec.tax_line_id := l_old_offset_tax_line_id;
1041 END IF;
1042 END IF;
1043
1044 IF (g_level_statement >= g_current_runtime_level) THEN
1045 FND_LOG.STRING(g_level_statement,
1046 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.create_offset_tax_line.END',
1047 'ZX_TDS_OFFSET_TAX_DETM_PKG: create_offset_tax_line(-)'||p_return_status);
1048 END IF;
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1054
1055 IF (g_level_unexpected >= g_current_runtime_level) THEN
1056 FND_LOG.STRING(g_level_unexpected,
1057 'ZX.PLSQL.ZX_TDS_OFFSET_TAX_DETM_PKG.create_offset_tax_line',
1058 p_error_buffer);
1059 END IF;
1060
1061 END create_offset_tax_line;
1062
1063 END ZX_TDS_OFFSET_TAX_DETM_PKG;