[Home] [Help]
PACKAGE BODY: APPS.ZX_TDS_TAX_LINES_DETM_PKG
Source
1 package body ZX_TDS_TAX_LINES_DETM_PKG as
2 /* $Header: zxditaxlndetpkgb.pls 120.114.12020000.3 2012/07/30 08:34:28 smuthusa noship $ */
3
4 -- private procedures
5 PROCEDURE create_offset_tax_lines(
6 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
7 p_return_status OUT NOCOPY VARCHAR2,
8 p_error_buffer OUT NOCOPY VARCHAR2 );
9
10 PROCEDURE populate_tax_line_numbers(
11 -- p_event_class_rec => p_event_class_rec,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_error_buffer OUT NOCOPY VARCHAR2
14 );
15
16 PROCEDURE process_reference_tax_lines(
17 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
18 x_return_status OUT NOCOPY VARCHAR2,
19 x_error_buffer OUT NOCOPY VARCHAR2
20 );
21
22 PROCEDURE process_copy_and_create (
23 p_event_class_rec IN zx_api_pub.event_class_rec_type,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_error_buffer OUT NOCOPY VARCHAR2);
26
27 PROCEDURE adjust_overapplication(
28 -- p_event_class_rec => p_event_class_rec,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_error_buffer OUT NOCOPY VARCHAR2);
31
32 PROCEDURE process_unchanged_trx_lines(
33 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_error_buffer OUT NOCOPY VARCHAR2);
36
37 PROCEDURE set_acct_source_tax_rate_id(
38 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_error_buffer OUT NOCOPY VARCHAR2);
41
42 g_current_runtime_level NUMBER;
43 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
44 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
45 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
46
47 c_lines_per_fetch CONSTANT NUMBER := 1000;
48 NUMBER_DUMMY CONSTANT NUMBER(15):= -999999999999999;
49 -----------------------------------------------------------------------
50 -- PUBLIC PROCEDURE
51 -- determine_tax_lines
52 --
53 -- DESCRIPTION
54 -- This procedure is the tail end wrap service for tax rounding, tax
55 -- columns population and offset tax lines determination
56
57 PROCEDURE determine_tax_lines(
58 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
59 p_return_status OUT NOCOPY VARCHAR2,
60 p_error_buffer OUT NOCOPY VARCHAR2
61 )
62 IS
63
64 BEGIN
65
66 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
67
68 IF (g_level_procedure >= g_current_runtime_level ) THEN
69 FND_LOG.STRING(g_level_procedure,
70 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.determine_tax_lines.BEGIN',
71 'ZX_TDS_TAX_LINES_DETM_PKG: determine_tax_lines(+)');
72 END IF;
73
74 --
75 -- init error buffer and return status
76 --
77 p_return_status := FND_API.G_RET_STS_SUCCESS;
78 p_error_buffer := NULL;
79
80 -- Bug 3971006: copy manual tax line from source document for trx lines
81 -- with line_level_action = 'COPY_AND_CREATE'
82 --
83 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_process_copy_and_create_flg = 'Y'
84 THEN
85
86 process_copy_and_create(
87 p_event_class_rec => p_event_class_rec,
88 x_return_status => p_return_status,
89 x_error_buffer => p_error_buffer);
90
91 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
92 IF g_level_unexpected >= g_current_runtime_level THEN
93 FND_LOG.STRING(g_level_unexpected,
94 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
95 'Incorrect return_status after calling process_copy_and_create()');
96 FND_LOG.STRING(g_level_unexpected,
97 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
98 'RETURN_STATUS = ' || p_return_status);
99 FND_LOG.STRING(g_level_unexpected,
100 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination.END',
101 'ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination(-)');
102 END IF;
103 RETURN;
104 END IF;
105 END IF;
106
107 -- add call to local procedure process_reference_tax_lines()
108 -- to process ref doc tax line for current trx.
109 --
110 IF ZX_TDS_CALC_SERVICES_PUB_PKG.g_reference_doc_exist_flg = 'Y' THEN
111 process_reference_tax_lines(
112 p_event_class_rec => p_event_class_rec,
113 x_return_status => p_return_status,
114 x_error_buffer => p_error_buffer);
115
116 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
117 IF g_level_unexpected >= g_current_runtime_level THEN
118 FND_LOG.STRING(g_level_unexpected,
119 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
120 'Incorrect return_status after calling '||
121 'process_reference_tax_lines()');
122 FND_LOG.STRING(g_level_unexpected,
123 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
124 'RETURN_STATUS = ' || p_return_status);
125 FND_LOG.STRING(g_level_unexpected,
126 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination.END',
127 'ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination(-)');
128 END IF;
129 RETURN;
130 END IF;
131 END IF;
132
133 -- Bug 4352593: comment out the call to MRC processing procedure
134 --
135 -- IF p_event_class_rec.enable_mrc_flag = 'Y' THEN
136 -- -- Create detail tax lines in reporting currencies
137 -- --
138 -- ZX_TDS_MRC_PROCESSING_PKG.create_mrc_det_tax_lines(
139 -- p_event_class_rec => p_event_class_rec,
140 -- x_return_status => p_return_status);
141 --
142 -- IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
143 -- IF g_level_unexpected >= g_current_runtime_level THEN
144 -- FND_LOG.STRING(g_level_unexpected,
145 -- 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
146 -- 'Incorrect return_status after calling '||
147 -- 'ZX_TDS_MRC_PROCESSING_PKG.create_mrc_det_tax_lines()');
148 -- FND_LOG.STRING(g_level_unexpected,
149 -- 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
150 -- 'RETURN_STATUS = ' || p_return_status);
151 -- FND_LOG.STRING(g_level_unexpected,
152 -- 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination.END',
153 -- 'ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination(-)');
154 -- END IF;
155 -- RETURN;
156 -- END IF;
157 -- END IF;
158
159 --
160 -- perform_rounding for the whole document
161 --
162 ZX_TDS_TAX_ROUNDING_PKG.perform_rounding(
163 p_event_class_rec,
164 p_return_status,
165 p_error_buffer);
166
167 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
168 RETURN;
169 END IF;
170
171 --
172 -- Check and adjust overapplication for CM
173 -- overapplication for adjustment taken care by AR before AR call eTax
174 --
175 -- Bug#7721270- we will not do over application for O2C
176 -- let AR handles the over application
177 --
178 -- Bug#8502340 - we will handle overapplication for O2C
179 -- also will ensure only completed documents are considered
180
181 IF ZX_GLOBAL_STRUCTURES_PKG.g_credit_memo_exists_flg = 'Y' THEN
182 -- bug fix 5417887, change populate tax_line_number to bulk process
183
184 adjust_overapplication(
185 -- p_event_class_rec => p_event_class_rec,
186 x_return_status => p_return_status,
187 x_error_buffer => p_error_buffer);
188
189 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
190 IF g_level_unexpected >= g_current_runtime_level THEN
191 FND_LOG.STRING(g_level_unexpected,
192 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
193 'Incorrect return_status after calling adjust_overapplication()');
194 FND_LOG.STRING(g_level_unexpected,
195 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
196 'RETURN_STATUS = ' || p_return_status);
197 FND_LOG.STRING(g_level_unexpected,
198 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination.END',
199 'ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination(-)');
200 END IF;
201 RETURN;
202 END IF;
203 END IF;
204
205 --
206 -- process tax tolerance for the whole document
207 --
208 IF p_event_class_rec.tax_event_type_code IN ('OVERRIDE_TAX' ,'UPDATE', 'CREATE') -- Bug 5684123
209 THEN
210 ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance(
211 p_event_class_rec,
212 p_return_status,
213 p_error_buffer);
214
215 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
216 RETURN;
217 END IF;
218 END IF;
219
220 -- Check the value of p_event_class_rec.allow_offset_tax_calc_flag to
221 -- determine if it is necessary to create offset tax lines
222 --
223 IF p_event_class_rec.allow_offset_tax_calc_flag ='Y' THEN
224 --
225 -- now create offset tax lines
226 --
227 create_offset_tax_lines(
228 p_event_class_rec,
229 p_return_status,
230 p_error_buffer);
231
232 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
233 RETURN;
234 END IF;
235 END IF;
236
237 -- set account_source_tax_rate_id
238 --
239 set_acct_source_tax_rate_id(
240 p_event_class_rec,
241 p_return_status,
242 p_error_buffer);
243
244 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
245 RETURN;
246 END IF;
247
248 --
249 -- process cancel, provider generated and frozen
250 -- tax lines in zx_lines for update event,
251 -- insert these tax lines into detail tax lines
252 -- global temp table and mark them as cancel
253 --
254
255 --IF (p_event_class_rec.tax_event_type_code = 'UPDATE') THEN
256 -- bug 3770874: call process_cancel_trx_lines conditionally
257
258 --Bug 8736358
259 IF (p_event_class_rec.tax_event_type_code = 'UPDATE' or NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_ln_action_update_exist_flg, 'N') = 'Y')
260 THEN
261
262 IF NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_ln_action_cancel_exist_flg, 'N') = 'Y'
263 THEN
264
265 -- process cancelled trx lines (line_level_action='CANCEL')
266
267 ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_trx_lines(
268 p_event_class_rec,
269 p_return_status,
270 p_error_buffer);
271
272 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
273 RETURN;
274 END IF;
275 END IF;
276
277 ZX_TDS_TAX_LINES_POPU_PKG.process_cancel_tax_lines(
278 p_event_class_rec,
279 p_return_status,
280 p_error_buffer);
281
282 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
283 RETURN;
284 END IF;
285
286 ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines(
287 p_event_class_rec,
288 p_return_status,
289 p_error_buffer);
290
291 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
292 RETURN;
293 END IF;
294
295 -- bug 3770874: call process_discard_tax_lines conditionally
296 --
297 IF NVL(ZX_TDS_CALC_SERVICES_PUB_PKG.g_ln_action_discard_exist_flg,'N') = 'Y'
298 THEN
299 --
300 -- process discard tax lines
301 --
302 ZX_TDS_TAX_LINES_POPU_PKG.process_discard_tax_lines(
303 p_event_class_rec,
304 p_return_status,
305 p_error_buffer);
306
307 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
308 IF g_level_unexpected >= g_current_runtime_level THEN
309 FND_LOG.STRING(g_level_unexpected,
310 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
311 'Incorrect return_status after calling '||
312 'ZX_TDS_TAX_LINES_POPU_PKG.process_discard_tax_lines()');
313 FND_LOG.STRING(g_level_unexpected,
314 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
315 'RETURN_STATUS = ' || p_return_status);
316 FND_LOG.STRING(g_level_unexpected,
317 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination.END',
318 'ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination(-)');
319 END IF;
320 RETURN;
321 END IF;
322
323 END IF;
324 --
325 -- call TRL api here to handle 'SYNCHRONIZE' case
326 --
327 END IF; -- p_event_class_rec.tax_event_type_code = 'UPDATE'
328
329 -- bug fix 3391299, call populate tax_line_number() to populate the tax_line_number
330 -- for all the tax_lines generated per trx_lines in current trx
331
332 -- bug fix 5417887, change populate tax_line_number to bulk process
333
334 IF NVL(p_event_class_rec.tax_event_type_code, 'X') <> 'OVERRIDE_TAX' THEN
335 populate_tax_line_numbers(
336 -- p_event_class_rec => p_event_class_rec,
337 x_return_status => p_return_status,
338 x_error_buffer => p_error_buffer
339 );
340 END IF;
341 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
342 IF g_level_unexpected >= g_current_runtime_level THEN
343 FND_LOG.STRING(g_level_unexpected,
344 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
345 'Incorrect return_status after calling '||
346 'populate_tax_line_numbers()');
347 FND_LOG.STRING(g_level_unexpected,
348 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination',
349 'RETURN_STATUS = ' || p_return_status);
350 FND_LOG.STRING(g_level_unexpected,
351 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination.END',
352 'ZX_TDS_TAX_LINES_DETM_PKG.tax_line_determination(-)');
353 END IF;
354 RETURN;
355 END IF;
356
357 -- for updte case of the quote calls, bring back the tax lines of the
358 -- untouched trx lines whose item distributions are changed.
359
360 -- bug fix 5417887
361 --IF p_event_class_rec.tax_event_type_code = 'UPDATE'
362 IF ZX_GLOBAL_STRUCTURES_PKG.g_update_event_process_flag = 'Y'
363 AND p_event_class_rec.QUOTE_FLAG ='Y'
364 AND ZX_TDS_CALC_SERVICES_PUB_PKG.g_ln_action_nochange_exist_flg = 'Y'
365 THEN
366
367 process_unchanged_trx_lines(
368 p_event_class_rec,
369 p_return_status,
370 p_error_buffer);
371
372 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
373 RETURN;
374 END IF;
375
376 END IF;
377
378 IF (g_level_procedure >= g_current_runtime_level ) THEN
379
380 FND_LOG.STRING(g_level_procedure,
381 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.determine_tax_lines.END',
382 'ZX_TDS_TAX_LINES_DETM_PKG: determine_tax_lines(-)'||p_return_status);
383 END IF;
384
385 EXCEPTION
386 WHEN OTHERS THEN
387 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
388 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
389 IF (g_level_unexpected >= g_current_runtime_level ) THEN
390 FND_LOG.STRING(g_level_unexpected,
391 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.determine_tax_lines',
392 p_error_buffer);
393 END IF;
394
395 END determine_tax_lines;
396
397 -----------------------------------------------------------------------
398 --
399 -- PRIVATE PROCEDURE
400 -- create_offset_tax_lines
401 --
402 -- DESCRIPTION
403 -- This procedure creates offset tax lines after rounding has been done
404 --
405 PROCEDURE create_offset_tax_lines(
406 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
407 p_return_status OUT NOCOPY VARCHAR2,
408 p_error_buffer OUT NOCOPY VARCHAR2 )
409 IS
410 l_tax_line_rec ZX_DETAIL_TAX_LINES_GT%ROWTYPE;
411 l_offset_tax_line_tbl ZX_TDS_CALC_SERVICES_PUB_PKG.DETAIL_TAX_LINES_TBL_TYPE;
412 i BINARY_INTEGER;
413
414 --Table to derive the apportionment number in case the transaction line has same taxes
415 TYPE tax_tbl_type is table of VARCHAR2(30) index by VARCHAR2(240);
416 tax_tbl tax_tbl_type;
417 l_tax_index VARCHAR2(240);
418
419 CURSOR get_tax_line_csr
420 IS
421 SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
422 tax_line_id,
423 internal_organization_id,
424 application_id,
425 entity_code,
426 event_class_code,
427 event_type_code,
428 trx_id,
429 trx_line_id,
430 trx_level_type,
431 trx_line_number,
432 doc_event_status,
433 tax_event_class_code,
434 tax_event_type_code,
435 tax_line_number,
436 content_owner_id,
437 tax_regime_id,
438 tax_regime_code,
439 tax_id,
440 tax,
441 tax_status_id,
442 tax_status_code,
443 tax_rate_id,
444 tax_rate_code,
445 tax_rate,
446 tax_apportionment_line_number,
447 trx_id_level2,
448 trx_id_level3,
449 trx_id_level4,
450 trx_id_level5,
451 trx_id_level6,
452 trx_user_key_level1,
453 trx_user_key_level2,
454 trx_user_key_level3,
455 trx_user_key_level4,
456 trx_user_key_level5,
457 trx_user_key_level6,
458 mrc_tax_line_flag,
459 ledger_id,
460 establishment_id,
461 legal_entity_id,
462 legal_entity_tax_reg_number,
463 hq_estb_reg_number,
464 hq_estb_party_tax_prof_id,
465 currency_conversion_date,
466 currency_conversion_type,
467 currency_conversion_rate,
468 tax_currency_conversion_date,
469 tax_currency_conversion_type,
470 tax_currency_conversion_rate,
471 trx_currency_code,
472 minimum_accountable_unit,
473 precision,
474 trx_number,
475 trx_date,
476 -- trx_sic_code,
477 -- fob_point,
478 unit_price,
479 line_amt,
480 trx_line_quantity,
481 tax_base_modifier_rate,
482 ref_doc_application_id,
483 ref_doc_entity_code,
484 ref_doc_event_class_code,
485 ref_doc_trx_id,
486 ref_doc_line_id,
487 ref_doc_line_quantity,
488 other_doc_line_amt,
489 other_doc_line_tax_amt,
490 other_doc_line_taxable_amt,
491 unrounded_taxable_amt,
492 unrounded_tax_amt,
493 related_doc_application_id,
494 related_doc_entity_code,
495 related_doc_event_class_code,
496 related_doc_trx_id,
497 related_doc_number,
498 related_doc_date,
499 applied_from_application_id,
500 applied_from_event_class_code,
501 applied_from_entity_code,
502 applied_from_trx_id,
503 applied_from_line_id,
504 applied_from_trx_number,
505 adjusted_doc_application_id,
506 adjusted_doc_entity_code,
507 adjusted_doc_event_class_code,
508 adjusted_doc_trx_id,
509 adjusted_doc_line_id,
510 adjusted_doc_trx_level_type, -- bug 6776312
511 adjusted_doc_number,
512 adjusted_doc_date,
513 applied_to_application_id,
514 applied_to_event_class_code,
515 applied_to_entity_code,
516 applied_to_trx_id,
517 applied_to_line_id,
518 applied_to_trx_number,
519 summary_tax_line_id,
520 offset_link_to_tax_line_id,
521 offset_flag,
522 process_for_recovery_flag,
523 tax_jurisdiction_id,
524 tax_jurisdiction_code,
525 place_of_supply,
526 place_of_supply_type_code,
527 place_of_supply_result_id,
528 tax_date_rule_id,
529 tax_date,
530 tax_determine_date,
531 tax_point_date,
532 trx_line_date,
533 tax_type_code,
534 tax_code,
535 tax_registration_id,
536 tax_registration_number,
537 registration_party_type,
538 rounding_level_code,
539 rounding_rule_code,
540 rounding_lvl_party_tax_prof_id,
541 rounding_lvl_party_type,
542 compounding_tax_flag,
543 orig_tax_status_id,
544 orig_tax_status_code,
545 orig_tax_rate_id,
546 orig_tax_rate_code,
547 orig_tax_rate,
548 orig_tax_jurisdiction_id,
549 orig_tax_jurisdiction_code,
550 orig_tax_amt_included_flag,
551 orig_self_assessed_flag,
552 tax_currency_code,
553 tax_amt,
554 tax_amt_tax_curr,
555 tax_amt_funcl_curr,
556 taxable_amt,
557 taxable_amt_tax_curr,
558 taxable_amt_funcl_curr,
559 orig_taxable_amt,
560 orig_taxable_amt_tax_curr,
561 cal_tax_amt,
562 cal_tax_amt_tax_curr,
563 cal_tax_amt_funcl_curr,
564 orig_tax_amt,
565 orig_tax_amt_tax_curr,
566 rec_tax_amt,
567 rec_tax_amt_tax_curr,
568 rec_tax_amt_funcl_curr,
569 nrec_tax_amt,
570 nrec_tax_amt_tax_curr,
571 nrec_tax_amt_funcl_curr,
572 tax_exemption_id,
573 tax_rate_before_exemption,
574 tax_rate_name_before_exemption,
575 exempt_rate_modifier,
576 exempt_certificate_number,
577 exempt_reason,
578 exempt_reason_code,
579 tax_exception_id,
580 tax_rate_before_exception,
581 tax_rate_name_before_exception,
582 exception_rate,
583 tax_apportionment_flag,
584 historical_flag,
585 taxable_basis_formula,
586 tax_calculation_formula,
587 cancel_flag,
588 purge_flag,
589 delete_flag,
590 tax_amt_included_flag,
591 self_assessed_flag,
592 overridden_flag,
593 manually_entered_flag,
594 reporting_only_flag,
595 freeze_until_overridden_flag,
596 copied_from_other_doc_flag,
597 recalc_required_flag,
598 settlement_flag,
599 item_dist_changed_flag,
600 associated_child_frozen_flag,
601 tax_only_line_flag,
602 compounding_dep_tax_flag,
603 last_manual_entry,
604 tax_provider_id,
605 record_type_code,
606 reporting_period_id,
607 legal_message_appl_2,
608 legal_message_status,
609 legal_message_rate,
610 legal_message_basis,
611 legal_message_calc,
612 legal_message_threshold,
613 legal_message_pos,
614 legal_message_trn,
615 legal_message_exmpt,
616 legal_message_excpt,
617 tax_regime_template_id,
618 tax_applicability_result_id,
619 direct_rate_result_id,
620 status_result_id,
621 rate_result_id,
622 basis_result_id,
623 thresh_result_id,
624 calc_result_id,
625 tax_reg_num_det_result_id,
626 eval_exmpt_result_id,
627 eval_excpt_result_id,
628 enforce_from_natural_acct_flag,
629 tax_hold_code,
630 tax_hold_released_code,
631 prd_total_tax_amt,
632 prd_total_tax_amt_tax_curr,
633 prd_total_tax_amt_funcl_curr,
634 internal_org_location_id,
635 attribute_category,
636 attribute1,
637 attribute2,
638 attribute3,
639 attribute4,
640 attribute5,
641 attribute6,
642 attribute7,
643 attribute8,
644 attribute9,
645 attribute10,
646 attribute11,
647 attribute12,
648 attribute13,
649 attribute14,
650 attribute15,
651 global_attribute_category,
652 global_attribute1,
653 global_attribute2,
654 global_attribute3,
655 global_attribute4,
656 global_attribute5,
657 global_attribute6,
658 global_attribute7,
659 global_attribute8,
660 global_attribute9,
661 global_attribute10,
662 global_attribute11,
663 global_attribute12,
664 global_attribute13,
665 global_attribute14,
666 global_attribute15,
667 numeric1,
668 numeric2,
669 numeric3,
670 numeric4,
671 numeric5,
672 numeric6,
673 numeric7,
674 numeric8,
675 numeric9,
676 numeric10,
677 char1,
678 char2,
679 char3,
680 char4,
681 char5,
682 char6,
683 char7,
684 char8,
685 char9,
686 char10,
687 date1,
688 date2,
689 date3,
690 date4,
691 date5,
692 date6,
693 date7,
694 date8,
695 date9,
696 date10,
697 created_by,
698 creation_date,
699 last_updated_by,
700 last_update_date,
701 last_update_login,
702 legal_justification_text1,
703 legal_justification_text2,
704 legal_justification_text3,
705 reporting_currency_code,
706 line_assessable_value,
707 trx_line_index,
708 offset_tax_rate_code,
709 proration_code,
710 other_doc_source,
711 ctrl_total_line_tx_amt,
712 tax_rate_type
713 FROM ZX_DETAIL_TAX_LINES_GT gt1
714 WHERE
715 /* -- commented out for bug fix 5417887
716 application_id = p_event_class_rec.application_id
717 AND entity_code = p_event_class_rec.entity_code
718 AND event_class_code = p_event_class_rec.event_class_code
719 AND trx_id = p_event_class_rec.trx_id
720 AND */
721 -- Offset_Flag = 'Y' -- 6634198
722 offset_tax_rate_code IS NOT NULL
723 AND tax_provider_id IS NULL
724 AND offset_link_to_tax_line_id IS NULL
725 AND NVL(tax_amt_included_flag, 'N') = 'N'
726 AND NVL(self_assessed_flag, 'N') ='N'
727 AND applied_from_trx_id IS NULL
728 AND adjusted_doc_trx_id IS NULL
729 AND NOT EXISTS
730 (SELECT 1
731 FROM zx_rates_b rates,
732 zx_detail_tax_lines_gt gt2
733 WHERE gt2.application_id = gt1.application_id
734 AND gt2.entity_code = gt1.entity_code
735 AND gt2.event_class_code = gt1.event_class_code
736 AND gt2.trx_id = gt1.trx_id
737 AND gt2.trx_line_id = gt1.trx_line_id
738 AND gt2.trx_level_type = gt1.trx_level_type
739 AND gt2.tax_regime_code = gt1.tax_regime_code
740 AND rates.tax_rate_id = gt1.tax_rate_id
741 AND gt2.tax = rates.offset_tax
742 )
743 AND NOT EXISTS
744 (SELECT /*+ INDEX(zl ZX_LINES_U1) */
745 1
746 FROM zx_rates_b rates,
747 zx_lines zl
748 WHERE zl.application_id = gt1.application_id
749 AND zl.entity_code = gt1.entity_code
750 AND zl.event_class_code = gt1.event_class_code
751 AND zl.trx_id = gt1.trx_id
752 AND zl.trx_line_id = gt1.trx_line_id
753 AND zl.trx_level_type = gt1.trx_level_type
754 AND zl.tax_regime_code = gt1.tax_regime_code
755 AND rates.tax_rate_id = gt1.tax_rate_id
756 AND zl.tax = rates.offset_tax
757 AND zl.cancel_flag = 'Y'
758 AND zl.tax_apportionment_line_number > 0
759 )
760
761 ORDER BY trx_id, trx_line_id, Tax_line_id;
762
763 CURSOR get_tax_line_number_csr IS
764 SELECT NVL(MAX(tax_line_number), 0) + 1
765 FROM zx_lines
766 WHERE application_id = l_tax_line_rec.application_id
767 AND event_class_code = l_tax_line_rec.event_class_code
768 AND entity_code = l_tax_line_rec.entity_code
769 AND trx_id = l_tax_line_rec.trx_id
770 AND trx_line_id = l_tax_line_rec.trx_line_id
771 AND trx_level_type = l_tax_line_rec.trx_level_type;
772
773 l_previous_trx_id NUMBER;
774 l_previous_trx_line_id NUMBER;
775 l_tax_line_number NUMBER;
776
777 BEGIN
778
779 IF (g_level_statement >= g_current_runtime_level ) THEN
780 FND_LOG.STRING(g_level_statement,
781 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.create_offset_tax_lines.BEGIN',
782 'ZX_TDS_TAX_LINES_DETM_PKG: create_offset_tax_lines(+)');
783 END IF;
784
785 p_return_status := FND_API.G_RET_STS_SUCCESS;
786
787 --
788 -- init index to offset tax lines structure
789 --
790 i := 0;
791
792 --
793 -- process detail tax lines from global temp table line by line
794 --
795 OPEN get_tax_line_csr;
796 LOOP
797 FETCH get_tax_line_csr INTO
798 l_tax_line_rec.tax_line_id,
799 l_tax_line_rec.internal_organization_id,
800 l_tax_line_rec.application_id,
801 l_tax_line_rec.entity_code,
802 l_tax_line_rec.event_class_code,
803 l_tax_line_rec.event_type_code,
804 l_tax_line_rec.trx_id,
805 l_tax_line_rec.trx_line_id,
806 l_tax_line_rec.trx_level_type,
807 l_tax_line_rec.trx_line_number,
808 l_tax_line_rec.doc_event_status,
809 l_tax_line_rec.tax_event_class_code,
810 l_tax_line_rec.tax_event_type_code,
811 l_tax_line_rec.tax_line_number,
812 l_tax_line_rec.content_owner_id,
813 l_tax_line_rec.tax_regime_id,
814 l_tax_line_rec.tax_regime_code,
815 l_tax_line_rec.tax_id,
816 l_tax_line_rec.tax,
817 l_tax_line_rec.tax_status_id,
818 l_tax_line_rec.tax_status_code,
819 l_tax_line_rec.tax_rate_id,
820 l_tax_line_rec.tax_rate_code,
821 l_tax_line_rec.tax_rate,
822 l_tax_line_rec.tax_apportionment_line_number,
823 l_tax_line_rec.trx_id_level2,
824 l_tax_line_rec.trx_id_level3,
825 l_tax_line_rec.trx_id_level4,
826 l_tax_line_rec.trx_id_level5,
827 l_tax_line_rec.trx_id_level6,
828 l_tax_line_rec.trx_user_key_level1,
829 l_tax_line_rec.trx_user_key_level2,
830 l_tax_line_rec.trx_user_key_level3,
831 l_tax_line_rec.trx_user_key_level4,
832 l_tax_line_rec.trx_user_key_level5,
833 l_tax_line_rec.trx_user_key_level6,
834 l_tax_line_rec.mrc_tax_line_flag,
835 l_tax_line_rec.ledger_id,
836 l_tax_line_rec.establishment_id,
837 l_tax_line_rec.legal_entity_id,
838 l_tax_line_rec.legal_entity_tax_reg_number,
839 l_tax_line_rec.hq_estb_reg_number,
840 l_tax_line_rec.hq_estb_party_tax_prof_id,
841 l_tax_line_rec.currency_conversion_date,
842 l_tax_line_rec.currency_conversion_type,
843 l_tax_line_rec.currency_conversion_rate,
844 l_tax_line_rec.tax_currency_conversion_date,
845 l_tax_line_rec.tax_currency_conversion_type,
846 l_tax_line_rec.tax_currency_conversion_rate,
847 l_tax_line_rec.trx_currency_code,
848 l_tax_line_rec.minimum_accountable_unit,
849 l_tax_line_rec.precision,
850 l_tax_line_rec.trx_number,
851 l_tax_line_rec.trx_date,
852 -- l_tax_line_rec.trx_sic_code,
853 -- l_tax_line_rec.fob_point,
854 l_tax_line_rec.unit_price,
855 l_tax_line_rec.line_amt,
856 l_tax_line_rec.trx_line_quantity,
857 l_tax_line_rec.tax_base_modifier_rate,
858 l_tax_line_rec.ref_doc_application_id,
859 l_tax_line_rec.ref_doc_entity_code,
860 l_tax_line_rec.ref_doc_event_class_code,
861 l_tax_line_rec.ref_doc_trx_id,
862 l_tax_line_rec.ref_doc_line_id,
863 l_tax_line_rec.ref_doc_line_quantity,
864 l_tax_line_rec.other_doc_line_amt,
865 l_tax_line_rec.other_doc_line_tax_amt,
866 l_tax_line_rec.other_doc_line_taxable_amt,
867 l_tax_line_rec.unrounded_taxable_amt,
868 l_tax_line_rec.unrounded_tax_amt,
869 l_tax_line_rec.related_doc_application_id,
870 l_tax_line_rec.related_doc_entity_code,
871 l_tax_line_rec.related_doc_event_class_code,
872 l_tax_line_rec.related_doc_trx_id,
873 l_tax_line_rec.related_doc_number,
874 l_tax_line_rec.related_doc_date,
875 l_tax_line_rec.applied_from_application_id,
876 l_tax_line_rec.applied_from_event_class_code,
877 l_tax_line_rec.applied_from_entity_code,
878 l_tax_line_rec.applied_from_trx_id,
879 l_tax_line_rec.applied_from_line_id,
880 l_tax_line_rec.applied_from_trx_number,
881 l_tax_line_rec.adjusted_doc_application_id,
882 l_tax_line_rec.adjusted_doc_entity_code,
883 l_tax_line_rec.adjusted_doc_event_class_code,
884 l_tax_line_rec.adjusted_doc_trx_id,
885 l_tax_line_rec.adjusted_doc_line_id,
886 l_tax_line_rec.adjusted_doc_trx_level_type, -- bug 6776312
887 l_tax_line_rec.adjusted_doc_number,
888 l_tax_line_rec.adjusted_doc_date,
889 l_tax_line_rec.applied_to_application_id,
890 l_tax_line_rec.applied_to_event_class_code,
891 l_tax_line_rec.applied_to_entity_code,
892 l_tax_line_rec.applied_to_trx_id,
893 l_tax_line_rec.applied_to_line_id,
894 l_tax_line_rec.applied_to_trx_number,
895 l_tax_line_rec.summary_tax_line_id,
896 l_tax_line_rec.offset_link_to_tax_line_id,
897 l_tax_line_rec.offset_flag,
898 l_tax_line_rec.process_for_recovery_flag,
899 l_tax_line_rec.tax_jurisdiction_id,
900 l_tax_line_rec.tax_jurisdiction_code,
901 l_tax_line_rec.place_of_supply,
902 l_tax_line_rec.place_of_supply_type_code,
903 l_tax_line_rec.place_of_supply_result_id,
904 l_tax_line_rec.tax_date_rule_id,
905 l_tax_line_rec.tax_date,
906 l_tax_line_rec.tax_determine_date,
907 l_tax_line_rec.tax_point_date,
908 l_tax_line_rec.trx_line_date,
909 l_tax_line_rec.tax_type_code,
910 l_tax_line_rec.tax_code,
911 l_tax_line_rec.tax_registration_id,
912 l_tax_line_rec.tax_registration_number,
913 l_tax_line_rec.registration_party_type,
914 l_tax_line_rec.rounding_level_code,
915 l_tax_line_rec.rounding_rule_code,
916 l_tax_line_rec.rounding_lvl_party_tax_prof_id,
917 l_tax_line_rec.rounding_lvl_party_type,
918 l_tax_line_rec.compounding_tax_flag,
919 l_tax_line_rec.orig_tax_status_id,
920 l_tax_line_rec.orig_tax_status_code,
921 l_tax_line_rec.orig_tax_rate_id,
922 l_tax_line_rec.orig_tax_rate_code,
923 l_tax_line_rec.orig_tax_rate,
924 l_tax_line_rec.orig_tax_jurisdiction_id,
925 l_tax_line_rec.orig_tax_jurisdiction_code,
926 l_tax_line_rec.orig_tax_amt_included_flag,
927 l_tax_line_rec.orig_self_assessed_flag,
928 l_tax_line_rec.tax_currency_code,
929 l_tax_line_rec.tax_amt,
930 l_tax_line_rec.tax_amt_tax_curr,
931 l_tax_line_rec.tax_amt_funcl_curr,
932 l_tax_line_rec.taxable_amt,
933 l_tax_line_rec.taxable_amt_tax_curr,
934 l_tax_line_rec.taxable_amt_funcl_curr,
935 l_tax_line_rec.orig_taxable_amt,
936 l_tax_line_rec.orig_taxable_amt_tax_curr,
937 l_tax_line_rec.cal_tax_amt,
938 l_tax_line_rec.cal_tax_amt_tax_curr,
939 l_tax_line_rec.cal_tax_amt_funcl_curr,
940 l_tax_line_rec.orig_tax_amt,
941 l_tax_line_rec.orig_tax_amt_tax_curr,
942 l_tax_line_rec.rec_tax_amt,
943 l_tax_line_rec.rec_tax_amt_tax_curr,
944 l_tax_line_rec.rec_tax_amt_funcl_curr,
945 l_tax_line_rec.nrec_tax_amt,
946 l_tax_line_rec.nrec_tax_amt_tax_curr,
947 l_tax_line_rec.nrec_tax_amt_funcl_curr,
948 l_tax_line_rec.tax_exemption_id,
949 l_tax_line_rec.tax_rate_before_exemption,
950 l_tax_line_rec.tax_rate_name_before_exemption,
951 l_tax_line_rec.exempt_rate_modifier,
952 l_tax_line_rec.exempt_certificate_number,
953 l_tax_line_rec.exempt_reason,
954 l_tax_line_rec.exempt_reason_code,
955 l_tax_line_rec.tax_exception_id,
956 l_tax_line_rec.tax_rate_before_exception,
957 l_tax_line_rec.tax_rate_name_before_exception,
958 l_tax_line_rec.exception_rate,
959 l_tax_line_rec.tax_apportionment_flag,
960 l_tax_line_rec.historical_flag,
961 l_tax_line_rec.taxable_basis_formula,
962 l_tax_line_rec.tax_calculation_formula,
963 l_tax_line_rec.cancel_flag,
964 l_tax_line_rec.purge_flag,
965 l_tax_line_rec.delete_flag,
966 l_tax_line_rec.tax_amt_included_flag,
967 l_tax_line_rec.self_assessed_flag,
968 l_tax_line_rec.overridden_flag,
969 l_tax_line_rec.manually_entered_flag,
970 l_tax_line_rec.reporting_only_flag,
971 l_tax_line_rec.freeze_until_overridden_flag,
972 l_tax_line_rec.copied_from_other_doc_flag,
973 l_tax_line_rec.recalc_required_flag,
974 l_tax_line_rec.settlement_flag,
975 l_tax_line_rec.item_dist_changed_flag,
976 l_tax_line_rec.associated_child_frozen_flag,
977 l_tax_line_rec.tax_only_line_flag,
978 l_tax_line_rec.compounding_dep_tax_flag,
979 l_tax_line_rec.last_manual_entry,
980 l_tax_line_rec.tax_provider_id,
981 l_tax_line_rec.record_type_code,
982 l_tax_line_rec.reporting_period_id,
983 l_tax_line_rec.legal_message_appl_2,
984 l_tax_line_rec.legal_message_status,
985 l_tax_line_rec.legal_message_rate,
986 l_tax_line_rec.legal_message_basis,
987 l_tax_line_rec.legal_message_calc,
988 l_tax_line_rec.legal_message_threshold,
989 l_tax_line_rec.legal_message_pos,
990 l_tax_line_rec.legal_message_trn,
991 l_tax_line_rec.legal_message_exmpt,
992 l_tax_line_rec.legal_message_excpt,
993 l_tax_line_rec.tax_regime_template_id,
994 l_tax_line_rec.tax_applicability_result_id,
995 l_tax_line_rec.direct_rate_result_id,
996 l_tax_line_rec.status_result_id,
997 l_tax_line_rec.rate_result_id,
998 l_tax_line_rec.basis_result_id,
999 l_tax_line_rec.thresh_result_id,
1000 l_tax_line_rec.calc_result_id,
1001 l_tax_line_rec.tax_reg_num_det_result_id,
1002 l_tax_line_rec.eval_exmpt_result_id,
1003 l_tax_line_rec.eval_excpt_result_id,
1004 l_tax_line_rec.enforce_from_natural_acct_flag,
1005 l_tax_line_rec.tax_hold_code,
1006 l_tax_line_rec.tax_hold_released_code,
1007 l_tax_line_rec.prd_total_tax_amt,
1008 l_tax_line_rec.prd_total_tax_amt_tax_curr,
1009 l_tax_line_rec.prd_total_tax_amt_funcl_curr,
1010 l_tax_line_rec.internal_org_location_id,
1011 l_tax_line_rec.attribute_category,
1012 l_tax_line_rec.attribute1,
1013 l_tax_line_rec.attribute2,
1014 l_tax_line_rec.attribute3,
1015 l_tax_line_rec.attribute4,
1016 l_tax_line_rec.attribute5,
1017 l_tax_line_rec.attribute6,
1018 l_tax_line_rec.attribute7,
1019 l_tax_line_rec.attribute8,
1020 l_tax_line_rec.attribute9,
1021 l_tax_line_rec.attribute10,
1022 l_tax_line_rec.attribute11,
1023 l_tax_line_rec.attribute12,
1024 l_tax_line_rec.attribute13,
1025 l_tax_line_rec.attribute14,
1026 l_tax_line_rec.attribute15,
1027 l_tax_line_rec.global_attribute_category,
1028 l_tax_line_rec.global_attribute1,
1029 l_tax_line_rec.global_attribute2,
1030 l_tax_line_rec.global_attribute3,
1031 l_tax_line_rec.global_attribute4,
1032 l_tax_line_rec.global_attribute5,
1033 l_tax_line_rec.global_attribute6,
1034 l_tax_line_rec.global_attribute7,
1035 l_tax_line_rec.global_attribute8,
1036 l_tax_line_rec.global_attribute9,
1037 l_tax_line_rec.global_attribute10,
1038 l_tax_line_rec.global_attribute11,
1039 l_tax_line_rec.global_attribute12,
1040 l_tax_line_rec.global_attribute13,
1041 l_tax_line_rec.global_attribute14,
1042 l_tax_line_rec.global_attribute15,
1043 l_tax_line_rec.numeric1,
1044 l_tax_line_rec.numeric2,
1045 l_tax_line_rec.numeric3,
1046 l_tax_line_rec.numeric4,
1047 l_tax_line_rec.numeric5,
1048 l_tax_line_rec.numeric6,
1049 l_tax_line_rec.numeric7,
1050 l_tax_line_rec.numeric8,
1051 l_tax_line_rec.numeric9,
1052 l_tax_line_rec.numeric10,
1053 l_tax_line_rec.char1,
1054 l_tax_line_rec.char2,
1055 l_tax_line_rec.char3,
1056 l_tax_line_rec.char4,
1057 l_tax_line_rec.char5,
1058 l_tax_line_rec.char6,
1059 l_tax_line_rec.char7,
1060 l_tax_line_rec.char8,
1061 l_tax_line_rec.char9,
1062 l_tax_line_rec.char10,
1063 l_tax_line_rec.date1,
1064 l_tax_line_rec.date2,
1065 l_tax_line_rec.date3,
1066 l_tax_line_rec.date4,
1067 l_tax_line_rec.date5,
1068 l_tax_line_rec.date6,
1069 l_tax_line_rec.date7,
1070 l_tax_line_rec.date8,
1071 l_tax_line_rec.date9,
1072 l_tax_line_rec.date10,
1073 l_tax_line_rec.created_by,
1074 l_tax_line_rec.creation_date,
1075 l_tax_line_rec.last_updated_by,
1076 l_tax_line_rec.last_update_date,
1077 l_tax_line_rec.last_update_login,
1078 l_tax_line_rec.legal_justification_text1,
1079 l_tax_line_rec.legal_justification_text2,
1080 l_tax_line_rec.legal_justification_text3,
1081 l_tax_line_rec.reporting_currency_code,
1082 l_tax_line_rec.line_assessable_value,
1083 l_tax_line_rec.trx_line_index,
1084 l_tax_line_rec.offset_tax_rate_code,
1085 l_tax_line_rec.proration_code,
1086 l_tax_line_rec.other_doc_source,
1087 l_tax_line_rec.ctrl_total_line_tx_amt,
1088 l_tax_line_rec.tax_rate_type;
1089
1090 IF get_tax_line_csr%FOUND THEN
1091 BEGIN --bug6509867
1092 UPDATE zx_detail_tax_lines_gt
1093 SET offset_flag = 'N'
1094 WHERE offset_flag = 'Y'
1095 AND tax_provider_id IS NULL
1096 AND offset_link_to_tax_line_id IS NULL
1097 AND tax_line_id = l_tax_line_rec.tax_line_id;
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 NULL;
1101 END;
1102
1103 IF NVL(l_tax_line_rec.tax_event_type_code, 'A') = 'OVERRIDE_TAX'
1104 THEN
1105
1106 IF nvl(l_previous_trx_id,-99) <> l_tax_line_rec.trx_id or
1107 nvl(l_previous_trx_line_id,-999) <> l_tax_line_rec.trx_line_id
1108 THEN
1109
1110 l_previous_trx_id := l_tax_line_rec.trx_id;
1111 l_previous_trx_line_id := l_tax_line_rec.trx_line_id;
1112
1113 OPEN get_tax_line_number_csr;
1114 FETCH get_tax_line_number_csr INTO l_tax_line_number;
1115 CLOSE get_tax_line_number_csr;
1116
1117 ELSE
1118
1119 l_tax_line_number := l_tax_line_number + 1;
1120
1121 END IF;
1122
1123 l_tax_line_rec.tax_line_number := l_tax_line_number;
1124
1125 END IF;
1126
1127 ZX_TDS_OFFSET_TAX_DETM_PKG.process_offset_tax(
1128 l_tax_line_rec,
1129 p_event_class_rec,
1130 p_return_status,
1131 p_error_buffer);
1132
1133 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1134 EXIT;
1135 ELSE
1136 i := i + 1;
1137 l_offset_tax_line_tbl(i) := l_tax_line_rec;
1138 END IF;
1139
1140 l_tax_index := l_offset_tax_line_tbl(i).tax_regime_code ||'$'||
1141 l_offset_tax_line_tbl(i).tax ||'$'||
1142 to_char(l_offset_tax_line_tbl(i).trx_id) ||'$'||
1143 to_char(l_offset_tax_line_tbl(i).trx_line_id);
1144
1145 IF (tax_tbl.EXISTS(l_tax_index)) THEN
1146 l_offset_tax_line_tbl(i).tax_apportionment_line_number := tax_tbl(l_tax_index)+1;
1147 ELSE
1148 l_offset_tax_line_tbl(i).tax_apportionment_line_number := 1;
1149 END IF;
1150 tax_tbl(l_tax_index) := l_offset_tax_line_tbl(i).tax_apportionment_line_number;
1151
1152 ELSE
1153 --
1154 -- no more record to process
1155 --
1156 CLOSE get_tax_line_csr;
1157 EXIT;
1158 END IF; -- end of get_tax_line_csr%FOUND
1159 END LOOP;
1160
1161 IF p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1162 CLOSE get_tax_line_csr;
1163 RETURN;
1164 END IF;
1165
1166 --
1167 -- insert offset tax lines to gt from pl/sql structure,
1168 --
1169 IF i > 0 THEN
1170 ZX_TDS_CALC_SERVICES_PUB_PKG.dump_detail_tax_lines_into_gt(
1171 l_offset_tax_line_tbl,
1172 p_return_status);
1173 END IF;
1174
1175 IF (g_level_procedure >= g_current_runtime_level ) THEN
1176
1177 FND_LOG.STRING(g_level_procedure,
1178 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.create_offset_tax_lines.END',
1179 'ZX_TDS_TAX_LINES_DETM_PKG: create_offset_tax_lines(-)'||p_return_status);
1180 END IF;
1181
1182 EXCEPTION
1183 WHEN OTHERS THEN
1184 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1185 p_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1186 IF get_tax_line_csr%ISOPEN THEN
1187 CLOSE get_tax_line_csr;
1188 END IF;
1189 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1190 FND_LOG.STRING(g_level_unexpected,
1191 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.create_offset_tax_lines',
1192 p_error_buffer);
1193 END IF;
1194
1195 END create_offset_tax_lines;
1196
1197 -----------------------------------------------------------------------
1198 --
1199 -- PRIVATE PROCEDURE
1200 -- populate_tax_line_numbers
1201 --
1202 -- DESCRIPTION
1203 -- populate tax_line_number for all the tax_lines generated
1204 -- per trx_lines in current trx
1205 --
1206 -- HISTORY
1207 -- Ling Zhang 26-Jul-04 Created for bug fix 3391299
1208 -- Ling Zhang 17-Aug-06 Rewritten for bug fix 5417887
1209
1210 PROCEDURE populate_tax_line_numbers(
1211 -- p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
1212 x_return_status OUT NOCOPY VARCHAR2,
1213 x_error_buffer OUT NOCOPY VARCHAR2
1214 ) IS
1215
1216 CURSOR get_tax_lines_csr IS
1217 SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
1218 application_id,
1219 event_class_code,
1220 entity_code,
1221 trx_id,
1222 trx_line_id,
1223 trx_level_type,
1224 tax_line_id,
1225 tax_regime_code,
1226 tax,
1227 tax_apportionment_line_number
1228 FROM zx_detail_tax_lines_gt
1229 WHERE mrc_tax_line_flag = 'N'
1230 ORDER BY application_id,
1231 event_class_code,
1232 entity_code,
1233 trx_id,
1234 trx_line_id,
1235 trx_level_type,
1236 tax_line_id asc NULLs LAST;
1237
1238 l_old_application_id NUMBER;
1239 l_old_event_class_code VARCHAR2(30);
1240 l_old_entity_code VARCHAR2(30);
1241 l_old_trx_id NUMBER;
1242 l_old_trx_line_id NUMBER;
1243 l_old_trx_level_type VARCHAR2(30);
1244 l_tax_line_number NUMBER;
1245
1246 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1247 TYPE var_30_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1248
1249 l_application_id_tab num_tab_type;
1250 l_event_class_code_tab var_30_tab_type;
1251 l_entity_code_tab var_30_tab_type;
1252 l_trx_id_tab num_tab_type;
1253 l_trx_line_id_tab num_tab_type;
1254 l_trx_level_type_tab var_30_tab_type;
1255 l_tax_line_id_tab num_tab_type;
1256 l_tax_regime_code_tab var_30_tab_type;
1257 l_tax_tab var_30_tab_type;
1258 l_tax_apprtnmt_line_num_tab num_tab_type;
1259 l_tax_line_number_tab num_tab_type;
1260 BEGIN
1261
1262 IF (g_level_statement >= g_current_runtime_level ) THEN
1263 FND_LOG.STRING(g_level_statement,
1264 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.populate_tax_line_numbers.BEGIN',
1265 'ZX_TDS_TAX_LINES_DETM_PKG: populate_tax_line_numbers(+)');
1266 END IF;
1267
1268 x_return_status := FND_API.G_RET_STS_SUCCESS;
1269
1270 l_old_application_id := -1;
1271 l_old_event_class_code := '@@@###$$$***';
1272 l_old_entity_code := '@@@###$$$***';
1273 l_old_trx_id := -1;
1274 l_old_trx_line_id := -1;
1275 l_old_trx_level_type := '@@@###$$$***';
1276
1277 OPEN get_tax_lines_csr;
1278 LOOP
1279 FETCH get_tax_lines_csr BULK COLLECT INTO
1280 l_application_id_tab,
1281 l_event_class_code_tab,
1282 l_entity_code_tab,
1283 l_trx_id_tab,
1284 l_trx_line_id_tab,
1285 l_trx_level_type_tab,
1286 l_tax_line_id_tab,
1287 l_tax_regime_code_tab,
1288 l_tax_tab,
1289 l_tax_apprtnmt_line_num_tab
1290 LIMIT c_lines_per_fetch;
1291
1292 FOR i in 1 .. l_trx_line_id_tab.COUNT LOOP
1293
1294 IF l_old_application_id = l_application_id_tab(i) AND
1295 l_old_event_class_code = l_event_class_code_tab(i) AND
1296 l_old_entity_code = l_entity_code_tab(i) AND
1297 l_old_trx_id = l_trx_id_tab(i) AND
1298 l_old_trx_line_id = l_trx_line_id_tab(i) AND
1299 l_old_trx_level_type = l_trx_level_type_tab(i)
1300 THEN
1301 l_tax_line_number := l_tax_line_number + 1;
1302 ELSE
1303 -- when trx_line changes, reset the values
1304 l_old_application_id := l_application_id_tab(i);
1305 l_old_event_class_code := l_event_class_code_tab(i);
1306 l_old_entity_code := l_entity_code_tab(i);
1307 l_old_trx_id := l_trx_id_tab(i);
1308 l_old_trx_line_id := l_trx_line_id_tab(i);
1309 l_old_trx_level_type := l_trx_level_type_tab(i);
1310 l_tax_line_number := 1;
1311 END IF;
1312
1313 l_tax_line_number_tab(i) := l_tax_line_number;
1314
1315 END LOOP;
1316
1317 FORALL i in 1 .. l_trx_line_id_tab.COUNT
1318 UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
1319 zx_detail_tax_lines_gt
1320 SET tax_line_number = l_tax_line_number_tab(i)
1321 WHERE tax_line_id = l_tax_line_id_tab(i);
1322
1323 IF (g_level_statement >= g_current_runtime_level ) THEN
1324 FND_LOG.STRING(g_level_statement,
1325 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.populate_tax_line_numbers',
1326 'After update the tax line numbers to the non-mrc tax lines '||
1327 'in the zx_detail_tax_lines_gt');
1328 END IF;
1329
1330 --IF p_event_class_rec.enable_mrc_flag = 'Y' THEN
1331 -- FORALL i in 1 .. l_trx_line_id_tab.COUNT
1332 -- UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
1333 -- zx_detail_tax_lines_gt
1334 -- SET tax_line_number = l_tax_line_number_tab(i)
1335 -- WHERE application_id = l_application_id_tab(i)
1336 -- AND event_class_code = l_event_class_code_tab(i)
1337 -- AND entity_code = l_entity_code_tab(i)
1338 -- AND trx_id = l_trx_id_tab(i)
1339 -- AND trx_line_id = l_trx_line_id_tab(i)
1340 -- AND trx_level_type = l_trx_level_type_tab(i)
1341 -- AND mrc_tax_line_flag = 'Y'
1342 -- AND tax_regime_code = l_tax_regime_code_tab(i)
1343 -- AND tax = l_tax_tab(i)
1344 -- AND tax_apportionment_line_number
1345 -- = l_tax_apprtnmt_line_num_tab(i);
1346 --
1347 --END IF;
1348
1349 EXIT WHEN get_tax_lines_csr%NOTFOUND;
1350 END LOOP;
1351 CLOSE get_tax_lines_csr;
1352
1353 IF (g_level_procedure >= g_current_runtime_level ) THEN
1354 FND_LOG.STRING(g_level_procedure,
1355 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.populate_tax_line_numbers.END',
1356 'ZX_TDS_TAX_LINES_DETM_PKG: populate_tax_line_numbers(-)');
1357 END IF;
1358
1359 EXCEPTION
1360 WHEN OTHERS THEN
1361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1362 x_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
1363 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1364 FND_LOG.STRING(g_level_unexpected,
1365 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.populate_tax_line_numbers',
1366 x_error_buffer);
1367 END IF;
1368
1369 END populate_tax_line_numbers;
1370
1371 -----------------------------------------------------------------------
1372 -- PUBLIC PROCEDURE
1373 -- process_reference_tax_lines
1374 --
1375 -- DESCRIPTION
1376 -- This procedure brings all reference tax lines which found not
1377 -- applicable into detail tax lines global temp table and reset
1378 -- the unrounded amounts.
1379 --
1380 -- CALLED BY
1381 -- ZX_TDS_TAX_LINES_DETM_PKG
1382 --
1383 -- HISTORY
1384 -- Ling Zhang Aug-06-2004 Created for bug fix 3391186
1385 --
1386
1387 PROCEDURE process_reference_tax_lines(
1388 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
1389 x_return_status OUT NOCOPY VARCHAR2,
1390 x_error_buffer OUT NOCOPY VARCHAR2)
1391 IS
1392 l_user_id NUMBER;
1393 l_login_id NUMBER;
1394 l_tax_regime_rec zx_global_structures_pkg.tax_regime_rec_type;
1395 l_tax_rec ZX_TDS_UTILITIES_PKG.zx_tax_info_cache_rec;
1396 l_tax_status_rec ZX_TDS_UTILITIES_PKG.zx_status_info_rec;
1397 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
1398 l_tax_jurisdiction_rec ZX_TDS_UTILITIES_PKG.zx_jur_info_cache_rec_type;
1399
1400 TYPE date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1401 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1402 TYPE var_30_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1403 TYPE var_1_tab_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
1404 TYPE var_rate_code_tab_type IS TABLE OF zx_detail_tax_lines_gt.tax_rate_code%TYPE
1405 INDEX BY BINARY_INTEGER;
1406
1407 l_tax_line_id_tab num_tab_type;
1408 l_tax_regime_code_tab var_30_tab_type;
1409 l_tax_jurisdiction_code_tab var_30_tab_type;
1410 l_tax_rate_code_tab var_rate_code_tab_type;
1411 l_tax_tab var_30_tab_type;
1412 l_tax_status_code_tab var_30_tab_type;
1413 l_tax_regime_id_tab num_tab_type;
1414 l_tax_rate_id_tab num_tab_type;
1415 l_tax_id_tab num_tab_type;
1416 l_tax_jur_id_tab num_tab_type;
1417 l_tax_status_id_tab num_tab_type;
1418 l_tax_determine_date_tab date_tab_type;
1419
1420 l_other_doc_source_tab var_30_tab_type;
1421 l_unrounded_tax_amt_tab num_tab_type;
1422 l_unrounded_taxable_amt_tab num_tab_type;
1423 l_manually_entered_flag_tab var_1_tab_type;
1424
1425 l_tax_class zx_rates_b.tax_class%TYPE;
1426
1427 CURSOR get_tax_info_csr IS
1428 SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
1429 tax_line_id,
1430 tax_regime_code,
1431 tax_jurisdiction_code,
1432 tax,
1433 tax_status_code,
1434 tax_rate_code,
1435 tax_determine_date,
1436 other_doc_source,
1437 unrounded_tax_amt,
1438 unrounded_taxable_amt,
1439 manually_entered_flag,
1440 tax_regime_id,
1441 tax_id,
1442 tax_jurisdiction_id,
1443 tax_status_id,
1444 tax_rate_id
1445 FROM zx_detail_tax_lines_gt
1446 WHERE ref_doc_application_id IS NOT NULL
1447 AND tax_event_type_code <> 'OVERRIDE_TAX'
1448 AND freeze_until_overridden_flag = 'Y';
1449
1450 BEGIN
1451
1452 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1453
1454 IF (g_level_procedure >= g_current_runtime_level ) THEN
1455 FND_LOG.STRING(g_level_procedure,
1456 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines.BEGIN',
1457 'ZX_TDS_TAX_LINES_DETM_PKG: process_reference_tax_lines(+)');
1458 END IF;
1459
1460 x_return_status := FND_API.G_RET_STS_SUCCESS;
1461
1462 -- Bug#5417753- determine tax_class value
1463 IF p_event_class_rec.prod_family_grp_code = 'O2C' THEN
1464 l_tax_class := 'OUTPUT';
1465 ELSIF p_event_class_rec.prod_family_grp_code = 'P2P' THEN
1466 l_tax_class := 'INPUT';
1467 END IF;
1468
1469 -- update the flags and other_doc_source for the applicable reference tax lines
1470 --
1471 IF NVL(p_event_class_rec.enforce_tax_from_ref_doc_flag, 'N') <> 'Y'
1472 THEN
1473 UPDATE
1474 zx_detail_tax_lines_gt LGT
1475 SET LGT.copied_from_other_doc_flag = 'Y',
1476 LGT.other_doc_source = 'REFERENCE'
1477 /* -- comment out the update of the amt until the necessary
1478 -- usage of following column is clearly identified.
1479 (LGT.other_doc_line_amt, LGT.other_doc_line_tax_amt, LGT.other_doc_line_taxable_amt ) =
1480 (SELECT L.line_amt,
1481 L.tax_amt,
1482 L.taxable_amt
1483 FROM zx_lines L
1484 WHERE L.application_id = LGT.ref_doc_application_id
1485 AND L.event_class_code = LGT.ref_doc_event_class_code
1486 AND L.entity_code = LGT.ref_doc_entity_code
1487 AND L.trx_id = LGT.ref_doc_trx_id
1488 AND L.trx_line_id = LGT.ref_doc_line_id
1489 AND L.trx_level_type = LGT.ref_doc_trx_level_type
1490 AND L.tax_regime_code = LGT.tax_regime_code
1491 AND L.tax = LGT.tax
1492 And L.tax_apportionment_line_number = LGT.tax_apportionment_line_number
1493 AND L.cancel_flag <> 'Y'
1494 AND L.mrc_tax_line_flag = 'N' ) */
1495 WHERE ref_doc_application_id IS NOT NULL
1496 AND tax_event_type_code <> 'OVERRIDE_TAX'
1497 AND NVL(historical_flag, 'N') <> 'Y'
1498 AND EXISTS (SELECT /*+ INDEX(L ZX_LINES_U1 ) */ 'X'
1499 FROM zx_lines L
1500 WHERE L.application_id = LGT.ref_doc_application_id
1501 AND L.event_class_code = LGT.ref_doc_event_class_code
1502 AND L.entity_code = LGT.ref_doc_entity_code
1503 AND L.trx_id = LGT.ref_doc_trx_id
1504 AND L.trx_line_id = LGT.ref_doc_line_id
1505 AND L.trx_level_type = LGT.ref_doc_trx_level_type
1506 AND L.tax_regime_code = LGT.tax_regime_code
1507 AND L.tax = LGT.tax
1508 AND NVL(L.tax_apportionment_line_number, -999999) = NVL(LGT.tax_apportionment_line_number, -999999)
1509 AND L.cancel_flag <> 'Y'
1510 AND L.mrc_tax_line_flag = 'N');
1511
1512 IF (g_level_statement >= g_current_runtime_level ) THEN
1513 FND_LOG.STRING(g_level_statement,
1514 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
1515 'Number of Rows Updated: ' || SQL%ROWCOUNT);
1516 END IF;
1517 END IF;
1518
1519 l_user_id := fnd_global.user_id;
1520 l_login_id := fnd_global.login_id;
1521 -- retrieve the non-applicable reference tax lines for current trx
1522 -- for system generated lines, set tax amt and taxable amt to zero;
1523 -- for manually entered tax lines, prorate the tax amt
1524 INSERT INTO zx_detail_tax_lines_gt
1525 ( tax_line_id,
1526 internal_organization_id,
1527 internal_org_location_id,
1528 application_id,
1529 entity_code,
1530 event_class_code,
1531 event_type_code,
1532 trx_id,
1533 trx_line_id,
1534 trx_level_type,
1535 trx_line_number,
1536 doc_event_status,
1537 tax_event_class_code,
1538 tax_event_type_code,
1539 tax_line_number,
1540 content_owner_id,
1541 tax_regime_id,
1542 tax_regime_code,
1543 tax_id,
1544 tax,
1545 tax_status_id,
1546 tax_status_code,
1547 tax_rate_id,
1548 tax_rate_code,
1549 tax_rate,
1550 tax_apportionment_line_number,
1551 trx_id_level2,
1552 trx_id_level3,
1553 trx_id_level4,
1554 trx_id_level5,
1555 --trx_id_level6,
1556 mrc_tax_line_flag,
1557 ledger_id,
1558 establishment_id,
1559 legal_entity_id,
1560 legal_entity_tax_reg_number,
1561 hq_estb_reg_number,
1562 hq_estb_party_tax_prof_id,
1563 currency_conversion_date,
1564 currency_conversion_type,
1565 currency_conversion_rate,
1566 trx_currency_code,
1567 minimum_accountable_unit,
1568 precision,
1569 trx_number,
1570 trx_date,
1571 unit_price,
1572 line_amt,
1573 trx_line_quantity,
1574 ref_doc_application_id,
1575 ref_doc_entity_code,
1576 ref_doc_event_class_code,
1577 ref_doc_trx_id,
1578 ref_doc_line_id,
1579 ref_doc_line_quantity,
1580 other_doc_line_amt,
1581 other_doc_line_tax_amt,
1582 other_doc_line_taxable_amt,
1583 unrounded_taxable_amt,
1584 unrounded_tax_amt,
1585 process_for_recovery_flag,
1586 tax_jurisdiction_id,
1587 tax_jurisdiction_code,
1588 place_of_supply,
1589 place_of_supply_type_code,
1590 place_of_supply_result_id,
1591 offset_flag,
1592 tax_date,
1593 tax_determine_date,
1594 tax_point_date,
1595 trx_line_date,
1596 tax_type_code,
1597 tax_code,
1598 tax_registration_number,
1599 registration_party_type,
1600 rounding_level_code,
1601 rounding_rule_code,
1602 rounding_lvl_party_tax_prof_id,
1603 rounding_lvl_party_type,
1604 historical_flag,
1605 tax_amt_included_flag,
1606 self_assessed_flag,
1607 overridden_flag,
1608 freeze_until_overridden_flag,
1609 copied_from_other_doc_flag,
1610 manually_entered_flag,
1611 taxable_basis_formula,
1612 tax_calculation_formula,
1613 recalc_required_flag,
1614 settlement_flag,
1615 item_dist_changed_flag,
1616 associated_child_frozen_flag,
1617 tax_only_line_flag,
1618 compounding_dep_tax_flag,
1619 last_manual_entry,
1620 tax_provider_id,
1621 tax_applicability_result_id,
1622 direct_rate_result_id,
1623 sync_with_prvdr_flag,
1624 other_doc_source,
1625 reporting_only_flag,
1626 line_assessable_value,
1627 tax_reg_num_det_result_id,
1628 record_type_code,
1629 tax_currency_code,
1630 -- numeric1,
1631 -- numeric2,
1632 -- numeric3,
1633 -- numeric4,
1634 -- numeric5,
1635 -- numeric6,
1636 -- numeric7,
1637 -- numeric8,
1638 -- numeric9,
1639 -- numeric10,
1640 -- char1,
1641 -- char2,
1642 -- char3,
1643 -- char4,
1644 -- char5,
1645 -- char6,
1646 -- char7,
1647 -- char8,
1648 -- char9,
1649 -- char10,
1650 -- date1,
1651 -- date2,
1652 -- date3,
1653 -- date4,
1654 -- date5,
1655 -- date6,
1656 -- date7,
1657 -- date8,
1658 -- date9,
1659 -- date10,
1660 related_doc_application_id,
1661 related_doc_entity_code,
1662 related_doc_event_class_code,
1663 related_doc_trx_id,
1664 related_doc_number,
1665 related_doc_date,
1666 applied_from_application_id,
1667 applied_from_event_class_code,
1668 applied_from_entity_code,
1669 applied_from_trx_id,
1670 applied_from_line_id,
1671 applied_from_trx_number,
1672 adjusted_doc_application_id,
1673 adjusted_doc_entity_code,
1674 adjusted_doc_event_class_code,
1675 adjusted_doc_trx_id,
1676 adjusted_doc_line_id,
1677 --Bug14251332
1678 adjusted_doc_trx_level_type,
1679 adjusted_doc_number,
1680 adjusted_doc_date,
1681 applied_to_application_id,
1682 applied_to_event_class_code,
1683 applied_to_entity_code,
1684 applied_to_trx_id,
1685 applied_to_line_id,
1686 applied_to_trx_number,
1687 exempt_certificate_number,
1688 -- summary_tax_line_id,
1689 offset_link_to_tax_line_id,
1690 tax_currency_conversion_date,
1691 tax_currency_conversion_type,
1692 tax_currency_conversion_rate,
1693 tax_base_modifier_rate,
1694 tax_date_rule_id,
1695 tax_registration_id,
1696 compounding_tax_flag,
1697 -- tax_amt,
1698 -- tax_amt_tax_curr,
1699 -- tax_amt_funcl_curr,
1700 -- taxable_amt,
1701 -- taxable_amt_tax_curr,
1702 -- taxable_amt_funcl_curr,
1703 -- cal_tax_amt,
1704 -- cal_tax_amt_tax_curr,
1705 -- cal_tax_amt_funcl_curr,
1706 -- rec_tax_amt,
1707 -- rec_tax_amt_tax_curr,
1708 -- rec_tax_amt_funcl_curr,
1709 -- nrec_tax_amt,
1710 -- nrec_tax_amt_tax_curr,
1711 -- nrec_tax_amt_funcl_curr,
1712 tax_exemption_id,
1713 tax_rate_before_exemption,
1714 tax_rate_name_before_exemption,
1715 exempt_rate_modifier,
1716 exempt_reason,
1717 exempt_reason_code,
1718 tax_exception_id,
1719 tax_rate_before_exception,
1720 tax_rate_name_before_exception,
1721 exception_rate,
1722 tax_apportionment_flag,
1723 cancel_flag,
1724 purge_flag,
1725 delete_flag,
1726 enforce_from_natural_acct_flag,
1727 reporting_period_id,
1728 legal_message_appl_2,
1729 legal_message_status,
1730 legal_message_rate,
1731 legal_message_basis,
1732 legal_message_calc,
1733 legal_message_threshold,
1734 legal_message_pos,
1735 legal_message_trn,
1736 legal_message_exmpt,
1737 legal_message_excpt,
1738 tax_regime_template_id,
1739 status_result_id,
1740 rate_result_id,
1741 basis_result_id,
1742 thresh_result_id,
1743 calc_result_id,
1744 eval_exmpt_result_id,
1745 eval_excpt_result_id,
1746 -- tax_hold_code,
1747 -- tax_hold_released_code,
1748 -- prd_total_tax_amt,
1749 -- prd_total_tax_amt_tax_curr,
1750 -- prd_total_tax_amt_funcl_curr,
1751 tax_rate_type,
1752 legal_justification_text1,
1753 legal_justification_text2,
1754 legal_justification_text3,
1755 reporting_currency_code,
1756 -- trx_line_index,
1757 -- offset_tax_rate_code,
1758 -- proration_code,
1759 ctrl_total_line_tx_amt,
1760 created_by,
1761 creation_date,
1762 last_updated_by,
1763 last_update_date,
1764 last_update_login,
1765 interface_entity_code,
1766 interface_tax_line_id,
1767 taxing_juris_geography_id,
1768 adjusted_doc_tax_line_id,
1769 --Start of Bug 7383041
1770 legal_reporting_status,
1771 --End of Bug 7383041
1772 object_version_number
1773 )
1774 (SELECT
1775 zx_lines_s.NEXTVAL, -- tax_line_id,
1776 G.internal_organization_id,
1777 G.internal_org_location_id,
1778 G.application_id,
1779 G.entity_code,
1780 G.event_class_code,
1781 G.event_type_code,
1782 G.trx_id,
1783 G.trx_line_id,
1784 G.trx_level_type,
1785 G.trx_line_number,
1786 G.doc_event_status,
1787 G.tax_event_class_code,
1788 G.tax_event_type_code,
1789 NUMBER_DUMMY, -- L.tax_line_number,
1790 G.first_pty_org_id, -- content_owner_id,
1791 L.tax_regime_id,
1792 L.tax_regime_code,
1793 L.tax_id,
1794 L.tax,
1795 L.tax_status_id,
1796 L.tax_status_code,
1797 L.tax_rate_id,
1798 L.tax_rate_code,
1799 L.tax_rate,
1800 L.tax_apportionment_line_number,
1801 G.trx_id_level2,
1802 G.trx_id_level3,
1803 G.trx_id_level4,
1804 G.trx_id_level5,
1805 --G.trx_id_level6,
1806 L.mrc_tax_line_flag,
1807 G.ledger_id,
1808 G.establishment_id,
1809 G.legal_entity_id,
1810 L.legal_entity_tax_reg_number,
1811 L.hq_estb_reg_number,
1812 G.hq_estb_party_tax_prof_id,
1813 G.currency_conversion_date,
1814 G.currency_conversion_type,
1815 G.currency_conversion_rate,
1816 G.trx_currency_code,
1817 G.minimum_accountable_unit,
1818 G.precision,
1819 G.trx_number,
1820 G.trx_date,
1821 G.unit_price,
1822 G.line_amt, -- line_amt
1823 G.trx_line_quantity,
1824 G.ref_doc_application_id,
1825 G.ref_doc_entity_code,
1826 G.ref_doc_event_class_code,
1827 G.ref_doc_trx_id,
1828 G.ref_doc_line_id,
1829 G.ref_doc_line_quantity,
1830 L.line_amt, -- other_doc_line_amt
1831 L.tax_amt, -- other_doc_line_tax_amt
1832 L.taxable_amt, -- other_doc_line_taxable_amt
1833 DECODE(l.manually_entered_flag,
1834 'N', 0,
1835 DECODE(L.line_amt,
1836 NULL, L.unrounded_taxable_amt,
1837 0, L.unrounded_taxable_amt,
1838 L.unrounded_taxable_amt * ( G.line_amt / L.line_amt )) ), -- unrounded_taxable_amt,
1839 DECODE(l.manually_entered_flag,
1840 'N', 0,
1841 DECODE(L.line_amt,
1842 NULL, L.unrounded_tax_amt,
1843 0, L.unrounded_tax_amt,
1844 L.unrounded_tax_amt * ( G.line_amt / L.line_amt )) ), -- unrounded_tax_amt,
1845 DECODE(L.Reporting_Only_Flag, 'N', 'Y', 'N'),
1846 L.tax_jurisdiction_id,
1847 L.tax_jurisdiction_code,
1848 L.place_of_supply,
1849 L.place_of_supply_type_code,
1850 L.place_of_supply_result_id,
1851 L.offset_flag,
1852 NVL(G.related_doc_date, NVL(G.provnl_tax_determination_date,
1853 NVL(G.adjusted_doc_date, NVL(G.trx_line_date, G.trx_date)))), --tax_date,
1854 NVL(G.related_doc_date, NVL(G.provnl_tax_determination_date,
1855 NVL(G.adjusted_doc_date, NVL(G.trx_line_date, G.trx_date)))), --tax_determine_date,
1856 NVL(G.related_doc_date, NVL(G.provnl_tax_determination_date,
1857 NVL(G.adjusted_doc_date, NVL(G.trx_line_date, G.trx_date)))), --tax_point_date,
1858 G.trx_line_date,
1859 L.tax_type_code,
1860 L.tax_code,
1861 L.tax_registration_number,
1862 L.registration_party_type,
1863 L.rounding_level_code,
1864 L.rounding_rule_code,
1865 L.rounding_lvl_party_tax_prof_id,
1866 L.rounding_lvl_party_type,
1867 G.historical_flag,
1868 L.tax_amt_included_flag,
1869 L.self_assessed_flag,
1870 'Y', -- L.overridden_flag,
1871 'Y', -- L.freeze_until_overridden_flag,
1872 'Y', -- L.copied_from_other_doc_flag,
1873 'Y', -- L.manually_entered_flag,
1874 'PRORATED_TB', -- L.taxable_basis_formula,
1875 L.tax_calculation_formula,
1876 L.recalc_required_flag,
1877 L.settlement_flag,
1878 L.item_dist_changed_flag,
1879 L.associated_child_frozen_flag,
1880 L.tax_only_line_flag,
1881 L.compounding_dep_tax_flag,
1882 'TAX_AMOUNT', -- L.last_manual_entry,
1883 L.tax_provider_id,
1884 L.tax_applicability_result_id,
1885 L.direct_rate_result_id,
1886 DECODE(L.tax_provider_id, NULL, L.sync_with_prvdr_flag, 'Y'), -- sync_with_prvdr_flag
1887 'REFERENCE', -- L.other_doc_source
1888 L.reporting_only_flag,
1889 G.assessable_value, -- line_assessable_value,
1890 L.tax_reg_num_det_result_id,
1891 --Start of Bug 7384041
1892 --L.record_type_code,
1893 DECODE(G.historical_flag,'Y','MIGRATED','ETAX_CREATED'), -- record_type_code
1894 --End of Bug 7384041
1895 L.tax_currency_code,
1896 -- G.numeric1,
1897 -- G.numeric2,
1898 -- G.numeric3,
1899 -- G.numeric4,
1900 -- G.numeric5,
1901 -- G.numeric6,
1902 -- G.numeric7,
1903 -- G.numeric8,
1904 -- G.numeric9,
1905 -- G.numeric10,
1906 -- G.char1,
1907 -- G.char2,
1908 -- G.char3,
1909 -- G.char4,
1910 -- G.char5,
1911 -- G.char6,
1912 -- G.char7,
1913 -- G.char8,
1914 -- G.char9,
1915 -- G.char10,
1916 -- G.date1,
1917 -- G.date2,
1918 -- G.date3,
1919 -- G.date4,
1920 -- G.date5,
1921 -- G.date6,
1922 -- G.date7,
1923 -- G.date8,
1924 -- G.date9,
1925 -- G.date10,
1926 G.related_doc_application_id,
1927 G.related_doc_entity_code,
1928 G.related_doc_event_class_code,
1929 G.related_doc_trx_id,
1930 G.related_doc_number,
1931 G.related_doc_date,
1932 G.applied_from_application_id,
1933 G.applied_from_event_class_code,
1934 G.applied_from_entity_code,
1935 G.applied_from_trx_id,
1936 G.applied_from_line_id,
1937 L.applied_from_trx_number,
1938 G.adjusted_doc_application_id,
1939 G.adjusted_doc_entity_code,
1940 G.adjusted_doc_event_class_code,
1941 G.adjusted_doc_trx_id,
1942 G.adjusted_doc_line_id,
1943 --Bug14251332
1944 G.adjusted_doc_trx_level_type,
1945 G.adjusted_doc_number,
1946 G.adjusted_doc_date,
1947 G.applied_to_application_id,
1948 G.applied_to_event_class_code,
1949 G.applied_to_entity_code,
1950 G.applied_to_trx_id,
1951 G.applied_to_trx_line_id,
1952 G.applied_to_trx_number,
1953 G.exempt_certificate_number,
1954 -- NULL, -- L.summary_tax_line_id,
1955 NULL, -- L.offset_link_to_tax_line_id,
1956 L.tax_currency_conversion_date,
1957 L.tax_currency_conversion_type,
1958 L.tax_currency_conversion_rate,
1959 L.tax_base_modifier_rate,
1960 L.tax_date_rule_id,
1961 L.tax_registration_id,
1962 L.compounding_tax_flag,
1963 -- NULL, -- L.tax_amt,
1964 -- NULL, -- L.tax_amt_tax_curr,
1965 -- NULL, -- L.tax_amt_funcl_curr,
1966 -- NULL, -- L.taxable_amt,
1967 -- NULL, -- L.taxable_amt_tax_curr,
1968 -- NULL, -- L.taxable_amt_funcl_curr,
1969 -- NULL, -- L.cal_tax_amt,
1970 -- NULL, -- L.cal_tax_amt_tax_curr,
1971 -- NULL, -- L.cal_tax_amt_funcl_curr,
1972 -- NULL, -- L.rec_tax_amt,
1973 -- NULL, -- L.rec_tax_amt_tax_curr,
1974 -- NULL, -- L.rec_tax_amt_funcl_curr,
1975 -- NULL, -- L.nrec_tax_amt,
1976 -- NULL, -- L.nrec_tax_amt_tax_curr,
1977 -- NULL, -- L.nrec_tax_amt_funcl_curr,
1978 L.tax_exemption_id,
1979 L.tax_rate_before_exemption,
1980 L.tax_rate_name_before_exemption,
1981 L.exempt_rate_modifier,
1982 L.exempt_reason,
1983 L.exempt_reason_code,
1984 L.tax_exception_id,
1985 L.tax_rate_before_exception,
1986 L.tax_rate_name_before_exception,
1987 L.exception_rate,
1988 L.tax_apportionment_flag,
1989 L.cancel_flag,
1990 L.purge_flag,
1991 L.delete_flag,
1992 L.enforce_from_natural_acct_flag,
1993 L.reporting_period_id,
1994 L.legal_message_appl_2,
1995 L.legal_message_status,
1996 L.legal_message_rate,
1997 L.legal_message_basis,
1998 L.legal_message_calc,
1999 L.legal_message_threshold,
2000 L.legal_message_pos,
2001 L.legal_message_trn,
2002 L.legal_message_exmpt,
2003 L.legal_message_excpt,
2004 L.tax_regime_template_id,
2005 L.status_result_id,
2006 L.rate_result_id,
2007 L.basis_result_id,
2008 L.thresh_result_id,
2009 L.calc_result_id,
2010 L.eval_exmpt_result_id,
2011 L.eval_excpt_result_id,
2012 -- NULL, --L.tax_hold_code,
2013 -- NULL, --L.tax_hold_released_code,
2014 -- NULL, -- L.prd_total_tax_amt,
2015 -- NULL, -- L.prd_total_tax_amt_tax_curr,
2016 -- NULL, -- L.prd_total_tax_amt_funcl_curr,
2017 L.tax_rate_type,
2018 L.legal_justification_text1,
2019 L.legal_justification_text2,
2020 L.legal_justification_text3,
2021 L.reporting_currency_code,
2022 -- NULL, -- L.trx_line_index
2023 -- NULL, -- L.offset_tax_rate_code
2024 -- NULL, -- L.proration_code
2025 G.ctrl_total_line_tx_amt,
2026 l_user_id, -- created_by,
2027 sysdate, -- creation_date,
2028 l_user_id, -- last_updated_by
2029 sysdate, -- last_update_date,
2030 l_login_id,
2031 L.interface_entity_code,
2032 L.interface_tax_line_id,
2033 L.taxing_juris_geography_id,
2034 L.adjusted_doc_tax_line_id,
2035 --Start of Bug 7383041
2036 (SELECT legal_reporting_status_def_val
2037 FROM zx_taxes_b
2038 WHERE tax_id = L.tax_id) legal_reporting_status,
2039 --End of Bug 7383041
2040 1
2041 FROM zx_lines L,
2042 zx_lines_det_factors G
2043 WHERE G.event_id = p_event_class_rec.event_id
2044 AND G.ref_doc_application_id IS NOT NULL
2045 AND (G.tax_event_type_code NOT IN ('OVERRIDE_TAX', 'UPDATE')
2046 OR (G.tax_event_type_code ='UPDATE' AND
2047 G.line_level_action NOT IN ('NO_CHANGE', 'CANCEL', 'DISCARD')
2048 )
2049 )
2050 -- AND NVL(G.historical_flag,'N') <> 'Y'
2051 AND L.application_id = G.ref_doc_application_id
2052 AND L.event_class_code = G.ref_doc_event_class_code
2053 AND L.entity_code = G.ref_doc_entity_code
2054 AND L.trx_id = G.ref_doc_trx_id
2055 AND L.trx_line_id = G.ref_doc_line_id
2056 AND L.trx_level_type = G.ref_doc_trx_level_type
2057 AND L.cancel_flag <> 'Y'
2058 AND L.offset_link_to_tax_line_id IS NULL
2059 AND L.mrc_tax_line_flag = 'N'
2060 AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
2061 'X'
2062 FROM zx_detail_tax_lines_gt T
2063 WHERE T.application_id = G.application_id
2064 AND T.entity_code = G.entity_code
2065 AND T.event_class_code = G.event_class_code
2066 AND T.trx_id = G.trx_id
2067 AND T.trx_line_id = G.trx_line_id
2068 AND T.trx_level_type = G.trx_level_type
2069 AND T.tax = L.tax
2070 AND T.tax_regime_code = L.tax_regime_code
2071 )
2072 );
2073
2074 IF (g_level_statement >= g_current_runtime_level ) THEN
2075 FND_LOG.STRING(g_level_statement,
2076 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2077 '1 : Number of Rows Inserted: ' || SQL%ROWCOUNT);
2078 END IF;
2079
2080 -- retrieve the self assessed and canceled reference tax lines for current trx
2081 -- for system generated lines, set tax amt and taxable amt to zero;
2082 -- for manually entered tax lines, prorate the tax amt
2083 INSERT INTO zx_detail_tax_lines_gt
2084 ( tax_line_id,
2085 internal_organization_id,
2086 internal_org_location_id,
2087 application_id,
2088 entity_code,
2089 event_class_code,
2090 event_type_code,
2091 trx_id,
2092 trx_line_id,
2093 trx_level_type,
2094 trx_line_number,
2095 doc_event_status,
2096 tax_event_class_code,
2097 tax_event_type_code,
2098 tax_line_number,
2099 content_owner_id,
2100 tax_regime_id,
2101 tax_regime_code,
2102 tax_id,
2103 tax,
2104 tax_status_id,
2105 tax_status_code,
2106 tax_rate_id,
2107 tax_rate_code,
2108 tax_rate,
2109 tax_apportionment_line_number,
2110 trx_id_level2,
2111 trx_id_level3,
2112 trx_id_level4,
2113 trx_id_level5,
2114 --trx_id_level6,
2115 mrc_tax_line_flag,
2116 ledger_id,
2117 establishment_id,
2118 legal_entity_id,
2119 legal_entity_tax_reg_number,
2120 hq_estb_reg_number,
2121 hq_estb_party_tax_prof_id,
2122 currency_conversion_date,
2123 currency_conversion_type,
2124 currency_conversion_rate,
2125 trx_currency_code,
2126 minimum_accountable_unit,
2127 precision,
2128 trx_number,
2129 trx_date,
2130 unit_price,
2131 line_amt,
2132 trx_line_quantity,
2133 ref_doc_application_id,
2134 ref_doc_entity_code,
2135 ref_doc_event_class_code,
2136 ref_doc_trx_id,
2137 ref_doc_line_id,
2138 ref_doc_line_quantity,
2139 other_doc_line_amt,
2140 other_doc_line_tax_amt,
2141 other_doc_line_taxable_amt,
2142 unrounded_taxable_amt,
2143 unrounded_tax_amt,
2144 process_for_recovery_flag,
2145 tax_jurisdiction_id,
2146 tax_jurisdiction_code,
2147 place_of_supply,
2148 place_of_supply_type_code,
2149 place_of_supply_result_id,
2150 offset_flag,
2151 tax_date,
2152 tax_determine_date,
2153 tax_point_date,
2154 trx_line_date,
2155 tax_type_code,
2156 tax_code,
2157 tax_registration_number,
2158 registration_party_type,
2159 rounding_level_code,
2160 rounding_rule_code,
2161 rounding_lvl_party_tax_prof_id,
2162 rounding_lvl_party_type,
2163 historical_flag,
2164 tax_amt_included_flag,
2165 self_assessed_flag,
2166 overridden_flag,
2167 freeze_until_overridden_flag,
2168 copied_from_other_doc_flag,
2169 manually_entered_flag,
2170 taxable_basis_formula,
2171 tax_calculation_formula,
2172 recalc_required_flag,
2173 settlement_flag,
2174 item_dist_changed_flag,
2175 associated_child_frozen_flag,
2176 tax_only_line_flag,
2177 compounding_dep_tax_flag,
2178 last_manual_entry,
2179 tax_provider_id,
2180 tax_applicability_result_id,
2181 direct_rate_result_id,
2182 sync_with_prvdr_flag,
2183 other_doc_source,
2184 reporting_only_flag,
2185 line_assessable_value,
2186 tax_reg_num_det_result_id,
2187 record_type_code,
2188 tax_currency_code,
2189 -- numeric1,
2190 -- numeric2,
2191 -- numeric3,
2192 -- numeric4,
2193 -- numeric5,
2194 -- numeric6,
2195 -- numeric7,
2196 -- numeric8,
2197 -- numeric9,
2198 -- numeric10,
2199 -- char1,
2200 -- char2,
2201 -- char3,
2202 -- char4,
2203 -- char5,
2204 -- char6,
2205 -- char7,
2206 -- char8,
2207 -- char9,
2208 -- char10,
2209 -- date1,
2210 -- date2,
2211 -- date3,
2212 -- date4,
2213 -- date5,
2214 -- date6,
2215 -- date7,
2216 -- date8,
2217 -- date9,
2218 -- date10,
2219 related_doc_application_id,
2220 related_doc_entity_code,
2221 related_doc_event_class_code,
2222 related_doc_trx_id,
2223 related_doc_number,
2224 related_doc_date,
2225 applied_from_application_id,
2226 applied_from_event_class_code,
2227 applied_from_entity_code,
2228 applied_from_trx_id,
2229 applied_from_line_id,
2230 applied_from_trx_number,
2231 adjusted_doc_application_id,
2232 adjusted_doc_entity_code,
2233 adjusted_doc_event_class_code,
2234 adjusted_doc_trx_id,
2235 adjusted_doc_line_id,
2236 --Bug14251332
2237 adjusted_doc_trx_level_type,
2238 adjusted_doc_number,
2239 adjusted_doc_date,
2240 applied_to_application_id,
2241 applied_to_event_class_code,
2242 applied_to_entity_code,
2243 applied_to_trx_id,
2244 applied_to_line_id,
2245 applied_to_trx_number,
2246 exempt_certificate_number,
2247 -- summary_tax_line_id,
2248 offset_link_to_tax_line_id,
2249 tax_currency_conversion_date,
2250 tax_currency_conversion_type,
2251 tax_currency_conversion_rate,
2252 tax_base_modifier_rate,
2253 tax_date_rule_id,
2254 tax_registration_id,
2255 compounding_tax_flag,
2256 -- tax_amt,
2257 -- tax_amt_tax_curr,
2258 -- tax_amt_funcl_curr,
2259 -- taxable_amt,
2260 -- taxable_amt_tax_curr,
2261 -- taxable_amt_funcl_curr,
2262 -- cal_tax_amt,
2263 -- cal_tax_amt_tax_curr,
2264 -- cal_tax_amt_funcl_curr,
2265 -- rec_tax_amt,
2266 -- rec_tax_amt_tax_curr,
2267 -- rec_tax_amt_funcl_curr,
2268 -- nrec_tax_amt,
2269 -- nrec_tax_amt_tax_curr,
2270 -- nrec_tax_amt_funcl_curr,
2271 tax_exemption_id,
2272 tax_rate_before_exemption,
2273 tax_rate_name_before_exemption,
2274 exempt_rate_modifier,
2275 exempt_reason,
2276 exempt_reason_code,
2277 tax_exception_id,
2278 tax_rate_before_exception,
2279 tax_rate_name_before_exception,
2280 exception_rate,
2281 tax_apportionment_flag,
2282 cancel_flag,
2283 purge_flag,
2284 delete_flag,
2285 enforce_from_natural_acct_flag,
2286 reporting_period_id,
2287 legal_message_appl_2,
2288 legal_message_status,
2289 legal_message_rate,
2290 legal_message_basis,
2291 legal_message_calc,
2292 legal_message_threshold,
2293 legal_message_pos,
2294 legal_message_trn,
2295 legal_message_exmpt,
2296 legal_message_excpt,
2297 tax_regime_template_id,
2298 status_result_id,
2299 rate_result_id,
2300 basis_result_id,
2301 thresh_result_id,
2302 calc_result_id,
2303 eval_exmpt_result_id,
2304 eval_excpt_result_id,
2305 -- tax_hold_code,
2306 -- tax_hold_released_code,
2307 -- prd_total_tax_amt,
2308 -- prd_total_tax_amt_tax_curr,
2309 -- prd_total_tax_amt_funcl_curr,
2310 tax_rate_type,
2311 legal_justification_text1,
2312 legal_justification_text2,
2313 legal_justification_text3,
2314 reporting_currency_code,
2315 -- trx_line_index,
2316 -- offset_tax_rate_code,
2317 -- proration_code,
2318 ctrl_total_line_tx_amt,
2319 created_by,
2320 creation_date,
2321 last_updated_by,
2322 last_update_date,
2323 last_update_login,
2324 interface_entity_code,
2325 interface_tax_line_id,
2326 taxing_juris_geography_id,
2327 adjusted_doc_tax_line_id,
2328 --Start of Bug 7383041
2329 legal_reporting_status,
2330 --End of Bug 7383041
2331 object_version_number
2332 )
2333 (SELECT
2334 zx_lines_s.NEXTVAL, -- tax_line_id,
2335 G.internal_organization_id,
2336 G.internal_org_location_id,
2337 G.application_id,
2338 G.entity_code,
2339 G.event_class_code,
2340 G.event_type_code,
2341 G.trx_id,
2342 G.trx_line_id,
2343 G.trx_level_type,
2344 G.trx_line_number,
2345 G.doc_event_status,
2346 G.tax_event_class_code,
2347 G.tax_event_type_code,
2348 NUMBER_DUMMY, -- L.tax_line_number,
2349 G.first_pty_org_id, -- content_owner_id,
2350 L.tax_regime_id,
2351 L.tax_regime_code,
2352 L.tax_id,
2353 L.tax,
2354 L.tax_status_id,
2355 L.tax_status_code,
2356 L.tax_rate_id,
2357 L.tax_rate_code,
2358 L.tax_rate,
2359 NVL(((SELECT max(ABS(tax_apportionment_line_number))
2360 FROM zx_detail_tax_lines_gt gt1
2361 WHERE gt1.application_id = G.application_id
2362 AND gt1.entity_code = G.entity_code
2363 AND gt1.event_class_code = G.event_class_code
2364 AND gt1.trx_id = G.trx_id
2365 AND gt1.trx_line_id = G.trx_line_id
2366 AND gt1.trx_level_type = G.trx_level_type
2367 AND gt1.tax_regime_code = L.tax_regime_code
2368 AND gt1.tax = L.tax
2369 ) + L.tax_apportionment_line_number
2370 ), L.tax_apportionment_line_number), -- tax_apportionment_line_number
2371 G.trx_id_level2,
2372 G.trx_id_level3,
2373 G.trx_id_level4,
2374 G.trx_id_level5,
2375 --G.trx_id_level6,
2376 L.mrc_tax_line_flag,
2377 G.ledger_id,
2378 G.establishment_id,
2379 G.legal_entity_id,
2380 L.legal_entity_tax_reg_number,
2381 L.hq_estb_reg_number,
2382 G.hq_estb_party_tax_prof_id,
2383 G.currency_conversion_date,
2384 G.currency_conversion_type,
2385 G.currency_conversion_rate,
2386 G.trx_currency_code,
2387 G.minimum_accountable_unit,
2388 G.precision,
2389 G.trx_number,
2390 G.trx_date,
2391 G.unit_price,
2392 G.line_amt, -- line_amt
2393 G.trx_line_quantity,
2394 G.ref_doc_application_id,
2395 G.ref_doc_entity_code,
2396 G.ref_doc_event_class_code,
2397 G.ref_doc_trx_id,
2398 G.ref_doc_line_id,
2399 G.ref_doc_line_quantity,
2400 L.line_amt, -- other_doc_line_amt
2401 L.tax_amt, -- other_doc_line_tax_amt
2402 L.taxable_amt, -- other_doc_line_taxable_amt
2403 DECODE(l.manually_entered_flag,
2404 'N', 0,
2405 DECODE(L.line_amt,
2406 NULL, L.unrounded_taxable_amt,
2407 0, L.unrounded_taxable_amt,
2408 L.unrounded_taxable_amt * ( G.line_amt / L.line_amt )) ), -- unrounded_taxable_amt,
2409 DECODE(l.manually_entered_flag,
2410 'N', 0,
2411 DECODE(L.line_amt,
2412 NULL, L.unrounded_tax_amt,
2413 0, L.unrounded_tax_amt,
2414 L.unrounded_tax_amt * ( G.line_amt / L.line_amt )) ), -- unrounded_tax_amt,
2415 DECODE(L.Reporting_Only_Flag, 'N', 'Y', 'N'),
2416 L.tax_jurisdiction_id,
2417 L.tax_jurisdiction_code,
2418 L.place_of_supply,
2419 L.place_of_supply_type_code,
2420 L.place_of_supply_result_id,
2421 L.offset_flag,
2422 NVL(G.related_doc_date, NVL(G.provnl_tax_determination_date,
2423 NVL(G.adjusted_doc_date, NVL(G.trx_line_date, G.trx_date)))), --tax_date,
2424 NVL(G.related_doc_date, NVL(G.provnl_tax_determination_date,
2425 NVL(G.adjusted_doc_date, NVL(G.trx_line_date, G.trx_date)))), --tax_determine_date,
2426 NVL(G.related_doc_date, NVL(G.provnl_tax_determination_date,
2427 NVL(G.adjusted_doc_date, NVL(G.trx_line_date, G.trx_date)))), --tax_point_date,
2428 G.trx_line_date,
2429 L.tax_type_code,
2430 L.tax_code,
2431 L.tax_registration_number,
2432 L.registration_party_type,
2433 L.rounding_level_code,
2434 L.rounding_rule_code,
2435 L.rounding_lvl_party_tax_prof_id,
2436 L.rounding_lvl_party_type,
2437 G.historical_flag,
2438 L.tax_amt_included_flag,
2439 L.self_assessed_flag,
2440 'Y', -- L.overridden_flag,
2441 'Y', -- L.freeze_until_overridden_flag,
2442 'Y', -- L.copied_from_other_doc_flag,
2443 'N', -- L.manually_entered_flag,
2444 'STANDARD_TB', -- L.taxable_basis_formula,
2445 L.tax_calculation_formula,
2446 L.recalc_required_flag,
2447 L.settlement_flag,
2448 L.item_dist_changed_flag,
2449 L.associated_child_frozen_flag,
2450 L.tax_only_line_flag,
2451 L.compounding_dep_tax_flag,
2452 'TAX_AMOUNT', -- L.last_manual_entry,
2453 L.tax_provider_id,
2454 L.tax_applicability_result_id,
2455 L.direct_rate_result_id,
2456 DECODE(L.tax_provider_id, NULL, L.sync_with_prvdr_flag, 'Y'), -- sync_with_prvdr_flag
2457 'REFERENCE', -- L.other_doc_source
2458 L.reporting_only_flag,
2459 G.assessable_value, -- line_assessable_value,
2460 L.tax_reg_num_det_result_id,
2461 --Start of Bug 7384041
2462 DECODE(G.historical_flag,'Y','MIGRATED','ETAX_CREATED'), -- record_type_code
2463 --End of Bug 7384041
2464 L.tax_currency_code,
2465 -- G.numeric1,
2466 -- G.numeric2,
2467 -- G.numeric3,
2468 -- G.numeric4,
2469 -- G.numeric5,
2470 -- G.numeric6,
2471 -- G.numeric7,
2472 -- G.numeric8,
2473 -- G.numeric9,
2474 -- G.numeric10,
2475 -- G.char1,
2476 -- G.char2,
2477 -- G.char3,
2478 -- G.char4,
2479 -- G.char5,
2480 -- G.char6,
2481 -- G.char7,
2482 -- G.char8,
2483 -- G.char9,
2484 -- G.char10,
2485 -- G.date1,
2486 -- G.date2,
2487 -- G.date3,
2488 -- G.date4,
2489 -- G.date5,
2490 -- G.date6,
2491 -- G.date7,
2492 -- G.date8,
2493 -- G.date9,
2494 -- G.date10,
2495 G.related_doc_application_id,
2496 G.related_doc_entity_code,
2497 G.related_doc_event_class_code,
2498 G.related_doc_trx_id,
2499 G.related_doc_number,
2500 G.related_doc_date,
2501 G.applied_from_application_id,
2502 G.applied_from_event_class_code,
2503 G.applied_from_entity_code,
2504 G.applied_from_trx_id,
2505 G.applied_from_line_id,
2506 L.applied_from_trx_number,
2507 G.adjusted_doc_application_id,
2508 G.adjusted_doc_entity_code,
2509 G.adjusted_doc_event_class_code,
2510 G.adjusted_doc_trx_id,
2511 G.adjusted_doc_line_id,
2512 --Bug14251332
2513 G.adjusted_doc_trx_level_type,
2514 G.adjusted_doc_number,
2515 G.adjusted_doc_date,
2516 G.applied_to_application_id,
2517 G.applied_to_event_class_code,
2518 G.applied_to_entity_code,
2519 G.applied_to_trx_id,
2520 G.applied_to_trx_line_id,
2521 G.applied_to_trx_number,
2522 G.exempt_certificate_number,
2523 -- NULL, -- L.summary_tax_line_id,
2524 NULL, -- L.offset_link_to_tax_line_id,
2525 L.tax_currency_conversion_date,
2526 L.tax_currency_conversion_type,
2527 L.tax_currency_conversion_rate,
2528 L.tax_base_modifier_rate,
2529 L.tax_date_rule_id,
2530 L.tax_registration_id,
2531 L.compounding_tax_flag,
2532 -- NULL, -- L.tax_amt,
2533 -- NULL, -- L.tax_amt_tax_curr,
2534 -- NULL, -- L.tax_amt_funcl_curr,
2535 -- NULL, -- L.taxable_amt,
2536 -- NULL, -- L.taxable_amt_tax_curr,
2537 -- NULL, -- L.taxable_amt_funcl_curr,
2538 -- NULL, -- L.cal_tax_amt,
2539 -- NULL, -- L.cal_tax_amt_tax_curr,
2540 -- NULL, -- L.cal_tax_amt_funcl_curr,
2541 -- NULL, -- L.rec_tax_amt,
2542 -- NULL, -- L.rec_tax_amt_tax_curr,
2543 -- NULL, -- L.rec_tax_amt_funcl_curr,
2544 -- NULL, -- L.nrec_tax_amt,
2545 -- NULL, -- L.nrec_tax_amt_tax_curr,
2546 -- NULL, -- L.nrec_tax_amt_funcl_curr,
2547 L.tax_exemption_id,
2548 L.tax_rate_before_exemption,
2549 L.tax_rate_name_before_exemption,
2550 L.exempt_rate_modifier,
2551 L.exempt_reason,
2552 L.exempt_reason_code,
2553 L.tax_exception_id,
2554 L.tax_rate_before_exception,
2555 L.tax_rate_name_before_exception,
2556 L.exception_rate,
2557 L.tax_apportionment_flag,
2558 L.cancel_flag,
2559 L.purge_flag,
2560 L.delete_flag,
2561 L.enforce_from_natural_acct_flag,
2562 L.reporting_period_id,
2563 L.legal_message_appl_2,
2564 L.legal_message_status,
2565 L.legal_message_rate,
2566 L.legal_message_basis,
2567 L.legal_message_calc,
2568 L.legal_message_threshold,
2569 L.legal_message_pos,
2570 L.legal_message_trn,
2571 L.legal_message_exmpt,
2572 L.legal_message_excpt,
2573 L.tax_regime_template_id,
2574 L.status_result_id,
2575 L.rate_result_id,
2576 L.basis_result_id,
2577 L.thresh_result_id,
2578 L.calc_result_id,
2579 L.eval_exmpt_result_id,
2580 L.eval_excpt_result_id,
2581 -- NULL, --L.tax_hold_code,
2582 -- NULL, --L.tax_hold_released_code,
2583 -- NULL, -- L.prd_total_tax_amt,
2584 -- NULL, -- L.prd_total_tax_amt_tax_curr,
2585 -- NULL, -- L.prd_total_tax_amt_funcl_curr,
2586 L.tax_rate_type,
2587 L.legal_justification_text1,
2588 L.legal_justification_text2,
2589 L.legal_justification_text3,
2590 L.reporting_currency_code,
2591 -- NULL, -- L.trx_line_index
2592 -- NULL, -- L.offset_tax_rate_code
2593 -- NULL, -- L.proration_code
2594 G.ctrl_total_line_tx_amt,
2595 l_user_id, -- created_by,
2596 sysdate, -- creation_date,
2597 l_user_id, -- last_updated_by
2598 sysdate, -- last_update_date,
2599 l_login_id,
2600 L.interface_entity_code,
2601 L.interface_tax_line_id,
2602 L.taxing_juris_geography_id,
2603 L.adjusted_doc_tax_line_id,
2604 --Start of Bug 7383041
2605 (SELECT legal_reporting_status_def_val
2606 FROM zx_taxes_b
2607 WHERE tax_id = L.tax_id) legal_reporting_status,
2608 --End of Bug 7383041
2609 1
2610 FROM zx_lines L,
2611 zx_lines_det_factors G
2612 WHERE G.event_id = p_event_class_rec.event_id
2613 AND G.ref_doc_application_id IS NOT NULL
2614 AND (G.tax_event_type_code NOT IN ('OVERRIDE_TAX', 'UPDATE')
2615 OR (G.tax_event_type_code ='UPDATE' AND
2616 G.line_level_action NOT IN ('NO_CHANGE', 'CANCEL', 'DISCARD')
2617 )
2618 )
2619 -- AND NVL(G.historical_flag,'N') <> 'Y'
2620 AND L.application_id = G.ref_doc_application_id
2621 AND L.event_class_code = G.ref_doc_event_class_code
2622 AND L.entity_code = G.ref_doc_entity_code
2623 AND L.trx_id = G.ref_doc_trx_id
2624 AND L.trx_line_id = G.ref_doc_line_id
2625 AND L.trx_level_type = G.ref_doc_trx_level_type
2626 AND L.cancel_flag <> 'Y'
2627 AND L.offset_link_to_tax_line_id IS NULL
2628 AND L.mrc_tax_line_flag = 'N'
2629 AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
2630 'X'
2631 FROM zx_detail_tax_lines_gt T
2632 WHERE T.application_id = G.application_id
2633 AND T.entity_code = G.entity_code
2634 AND T.event_class_code = G.event_class_code
2635 AND T.trx_id = G.trx_id
2636 AND T.trx_line_id = G.trx_line_id
2637 AND T.trx_level_type = G.trx_level_type
2638 AND T.tax = L.tax
2639 AND T.tax_regime_code = L.tax_regime_code
2640 AND NVL(T.self_assessed_flag, 'N') = 'N'
2641 AND NVL(T.cancel_flag, 'N') = 'N'
2642 )
2643 );
2644
2645 IF (g_level_statement >= g_current_runtime_level ) THEN
2646 FND_LOG.STRING(g_level_statement,
2647 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2648 '2 : Number of Rows Inserted: ' || SQL%ROWCOUNT);
2649 END IF;
2650
2651 OPEN get_tax_info_csr;
2652 LOOP
2653
2654 FETCH get_tax_info_csr BULK COLLECT INTO
2655 l_tax_line_id_tab,
2656 l_tax_regime_code_tab,
2657 l_tax_jurisdiction_code_tab,
2658 l_tax_tab,
2659 l_tax_status_code_tab,
2660 l_tax_rate_code_tab,
2661 l_tax_determine_date_tab,
2662 l_other_doc_source_tab,
2663 l_unrounded_tax_amt_tab,
2664 l_unrounded_taxable_amt_tab,
2665 l_manually_entered_flag_tab,
2666 l_tax_regime_id_tab,
2667 l_tax_id_tab,
2668 l_tax_jur_id_tab,
2669 l_tax_status_id_tab,
2670 l_tax_rate_id_tab
2671 LIMIT c_lines_per_fetch;
2672
2673 FOR i in 1 .. l_tax_line_id_tab.count LOOP
2674
2675 -- bug 7008562: Per Harsh and Desh, do not do validation for PO taxes
2676 -- that are not applicable in current AP invoice
2677 --
2678 IF l_other_doc_source_tab(i)='REFERENCE' AND l_unrounded_tax_amt_tab(i)=0 AND
2679 l_unrounded_taxable_amt_tab(i)=0 AND l_manually_entered_flag_tab(i)='Y'
2680 THEN
2681
2682 NULL;
2683
2684 ELSE
2685 -- validate and populate tax_regime_id
2686 --
2687 ZX_TDS_UTILITIES_PKG.get_regime_cache_info(
2688 l_tax_regime_code_tab(i),
2689 l_tax_determine_date_tab(i),
2690 l_tax_regime_rec,
2691 x_return_status,
2692 x_error_buffer);
2693
2694 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2695 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2696 FND_LOG.STRING(g_level_unexpected,
2697 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2698 'Incorrect return_status after calling ' ||
2699 'ZX_TDS_UTILITIES_PKG.get_regime_cache_info');
2700 FND_LOG.STRING(g_level_unexpected,
2701 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2702 'RETURN_STATUS = ' || x_return_status);
2703 FND_LOG.STRING(g_level_unexpected,
2704 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines.END',
2705 'ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines(-)');
2706 END IF;
2707 RETURN;
2708 END IF;
2709
2710 l_tax_regime_id_tab(i) := l_tax_regime_rec.tax_regime_id;
2711
2712 -- validate and populate tax_id
2713 --
2714 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
2715 l_tax_regime_code_tab(i),
2716 l_tax_tab(i),
2717 l_tax_determine_date_tab(i),
2718 l_tax_rec,
2719 x_return_status,
2720 x_error_buffer);
2721
2722 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2723 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2724 FND_LOG.STRING(g_level_unexpected,
2725 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2726 'Incorrect return_status after calling ' ||
2727 'ZX_TDS_UTILITIES_PKG.get_tax_cache_info');
2728 FND_LOG.STRING(g_level_unexpected,
2729 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2730 'RETURN_STATUS = ' || x_return_status);
2731 FND_LOG.STRING(g_level_unexpected,
2732 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines.END',
2733 'ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines(-)');
2734 END IF;
2735 RETURN;
2736 END IF;
2737
2738 l_tax_id_tab(i) := l_tax_rec.tax_id;
2739
2740 IF l_tax_jurisdiction_code_tab(i) IS NOT NULL THEN
2741 ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info(
2742 l_tax_regime_code_tab(i),
2743 l_tax_tab(i),
2744 l_tax_jurisdiction_code_tab(i),
2745 l_tax_determine_date_tab(i),
2746 l_tax_jurisdiction_rec,
2747 x_return_status,
2748 x_error_buffer);
2749
2750 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2751 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2752 FND_LOG.STRING(g_level_unexpected,
2753 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2754 'Incorrect return_status after calling ' ||
2755 'ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info');
2756 FND_LOG.STRING(g_level_unexpected,
2757 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2758 'RETURN_STATUS = ' || x_return_status);
2759 FND_LOG.STRING(g_level_unexpected,
2760 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines.END',
2761 'ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines(-)');
2762 END IF;
2763 RETURN;
2764 END IF;
2765
2766 l_tax_jur_id_tab(i) := l_tax_jurisdiction_rec.tax_jurisdiction_id;
2767 ELSE
2768
2769 l_tax_jur_id_tab(i) := NULL;
2770 END IF;
2771
2772 ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info(
2773 l_tax_tab(i),
2774 l_tax_regime_code_tab(i),
2775 l_tax_status_code_tab(i),
2776 l_tax_determine_date_tab(i),
2777 l_tax_status_rec,
2778 x_return_status,
2779 x_error_buffer);
2780
2781 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2782 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2783 FND_LOG.STRING(g_level_unexpected,
2784 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2785 'Incorrect return_status after calling ' ||
2786 'ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info');
2787 FND_LOG.STRING(g_level_unexpected,
2788 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2789 'RETURN_STATUS = ' || x_return_status);
2790 FND_LOG.STRING(g_level_unexpected,
2791 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines.END',
2792 'ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines(-)');
2793 END IF;
2794 RETURN;
2795 END IF;
2796
2797 l_tax_status_id_tab(i) := l_tax_status_rec.tax_status_id;
2798
2799 -- validate and populate tax_rate_id
2800 --
2801 ZX_TDS_UTILITIES_PKG.get_tax_rate_info(
2802 l_tax_regime_code_tab(i),
2803 l_tax_tab(i),
2804 l_tax_jurisdiction_code_tab(i),
2805 l_tax_status_code_tab(i),
2806 l_tax_rate_code_tab(i),
2807 l_tax_determine_date_tab(i),
2808 l_tax_class,
2809 l_tax_rate_rec,
2810 x_return_status,
2811 x_error_buffer);
2812
2813 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2814 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2815 FND_LOG.STRING(g_level_unexpected,
2816 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2817 'Incorrect return_status after calling ' ||
2818 'ZX_TDS_UTILITIES_PKG.get_tax_rate_info');
2819 FND_LOG.STRING(g_level_unexpected,
2820 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2821 'RETURN_STATUS = ' || x_return_status);
2822 FND_LOG.STRING(g_level_unexpected,
2823 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines.END',
2824 'ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines(-)');
2825 END IF;
2826 RETURN;
2827 END IF;
2828
2829 l_tax_rate_id_tab(i) := l_tax_rate_rec.tax_rate_id;
2830
2831 END IF; -- bug 7008562
2832 END LOOP;
2833
2834 FORALL i in 1 .. l_tax_line_id_tab.COUNT
2835 UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
2836 zx_detail_tax_lines_gt
2837 SET tax_regime_id = l_tax_regime_id_tab(i),
2838 tax_id = l_tax_id_tab(i),
2839 tax_jurisdiction_id = l_tax_jur_id_tab(i),
2840 tax_status_id = l_tax_status_id_tab(i),
2841 tax_rate_id = l_tax_rate_id_tab(i)
2842 WHERE tax_line_id = l_tax_line_id_tab(i);
2843
2844 EXIT WHEN get_tax_info_csr%NOTFOUND;
2845 END LOOP;
2846 CLOSE get_tax_info_csr;
2847
2848 IF (g_level_procedure >= g_current_runtime_level ) THEN
2849
2850 FND_LOG.STRING(g_level_procedure,
2851 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines.END',
2852 'ZX_TDS_TAX_LINES_DETM_PKG: process_reference_tax_lines(-)'||x_return_status);
2853 END IF;
2854
2855 EXCEPTION
2856 WHEN OTHERS THEN
2857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2858 x_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
2859 IF (g_level_unexpected >= g_current_runtime_level ) THEN
2860 FND_LOG.STRING(g_level_unexpected,
2861 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_reference_tax_lines',
2862 x_error_buffer);
2863 END IF;
2864
2865 END process_reference_tax_lines;
2866
2867 -----------------------------------------------------------------------
2868 -- PRIVATE PROCEDURE
2869 -- process_copy_and_create
2870 --
2871 -- DESCRIPTION
2872 -- This procedure copies all manual tax lines that do not exist
2873 -- in the system-generated tax lines for the trx lines
2874 -- with line_level_action = 'COPY_ANY_CREATE'
2875 --
2876 -- HISTORY
2877 -- Hongjun Liu Dec-18-2004 Created for Bug Fix 3971006
2878 -- Ling Zhang Aug-08-2005 Bug Fix 4542315
2879 --
2880
2881 PROCEDURE process_copy_and_create(
2882 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
2883 x_return_status OUT NOCOPY VARCHAR2,
2884 x_error_buffer OUT NOCOPY VARCHAR2) IS
2885
2886 l_user_id NUMBER;
2887 l_login_id NUMBER;
2888 l_tax_regime_rec zx_global_structures_pkg.tax_regime_rec_type;
2889 l_tax_rec ZX_TDS_UTILITIES_PKG.zx_tax_info_cache_rec;
2890 l_tax_status_rec ZX_TDS_UTILITIES_PKG.zx_status_info_rec;
2891 l_tax_rate_rec ZX_TDS_UTILITIES_PKG.zx_rate_info_rec_type;
2892 l_tax_jurisdiction_rec ZX_TDS_UTILITIES_PKG.zx_jur_info_cache_rec_type;
2893
2894 l_tax_class ZX_RATES_B.tax_class%TYPE;
2895
2896 TYPE date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2897 TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2898 TYPE var_30_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2899 TYPE var_rate_code_tab_type IS TABLE OF zx_detail_tax_lines_gt.tax_rate_code%TYPE
2900 INDEX BY BINARY_INTEGER;
2901
2902 l_tax_line_id_tab num_tab_type;
2903 --jur bug
2904 l_tax_jur_id_tab num_tab_type;
2905 l_tax_regime_code_tab var_30_tab_type;
2906 l_tax_rate_code_tab var_rate_code_tab_type;
2907 l_tax_tab var_30_tab_type;
2908 l_tax_status_code_tab var_30_tab_type;
2909 l_tax_jurisdiction_code_tab var_30_tab_type;
2910 l_tax_regime_id_tab num_tab_type;
2911 l_tax_rate_id_tab num_tab_type;
2912 l_tax_id_tab num_tab_type;
2913 l_tax_status_id_tab num_tab_type;
2914 l_tax_determine_date_tab date_tab_type;
2915
2916 CURSOR get_tax_info_csr IS
2917 SELECT /*+ ORDERED INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
2918 T.tax_line_id,
2919 T.tax_regime_code,
2920 T.tax_jurisdiction_code,
2921 T.tax,
2922 T.tax_status_code,
2923 T.tax_rate_code,
2924 T.tax_determine_date
2925 FROM zx_detail_tax_lines_gt T,
2926 zx_lines_det_factors G
2927 WHERE
2928 -- commented out for bug fix 5417887
2929 -- G.application_id = p_event_class_rec.application_id
2930 --AND G.event_class_code = p_event_class_rec.event_class_code
2931 --AND G.entity_code = p_event_class_rec.entity_code
2932 --AND G.trx_id = p_event_class_rec.trx_id
2933 --AND G.event_id = p_event_class_rec.event_id
2934 --AND
2935 G.line_level_action = 'COPY_AND_CREATE'
2936 AND T.application_id = G.application_id
2937 AND T.event_class_code = G.event_class_code
2938 AND T.entity_code = G.entity_code
2939 AND T.trx_id = G.trx_id
2940 AND T.trx_line_id = G.trx_line_id
2941 AND T.trx_level_type = G.trx_level_type
2942 AND T.manually_entered_flag = 'Y'
2943 AND T.tax_provider_id IS NULL;
2944
2945 BEGIN
2946
2947 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2948
2949 IF (g_level_procedure >= g_current_runtime_level ) THEN
2950 FND_LOG.STRING(g_level_procedure,
2951 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create.BEGIN',
2952 'ZX_TDS_TAX_LINES_DETM_PKG: process_copy_and_create(+)');
2953 END IF;
2954
2955 x_return_status := FND_API.G_RET_STS_SUCCESS;
2956
2957
2958 l_user_id := fnd_global.user_id;
2959 l_login_id := fnd_global.login_id;
2960
2961 -- Bug#5417753- determine tax_class value
2962 -- bug 5417887 - assume all trx in the batch carry the same product family group code
2963
2964 IF p_event_class_rec.prod_family_grp_code = 'O2C' THEN
2965 l_tax_class := 'OUTPUT';
2966 ELSIF p_event_class_rec.prod_family_grp_code = 'P2P' THEN
2967 l_tax_class := 'INPUT';
2968 END IF;
2969
2970 -- Copy the non-applicable manual tax lines from source
2971 -- documnet.
2972 --
2973
2974 MERGE INTO zx_detail_tax_lines_gt tax_line
2975 USING(
2976 SELECT /*+ index(L ZX_LINES_U1) */ -- Added the hint as part of 6596182
2977 G.internal_organization_id,
2978 G.internal_org_location_id,
2979 G.application_id,
2980 G.entity_code,
2981 G.event_class_code,
2982 G.event_type_code,
2983 G.trx_id,
2984 G.trx_line_id,
2985 G.trx_level_type,
2986 G.trx_line_number,
2987 G.doc_event_status,
2988 G.tax_event_class_code,
2989 G.tax_event_type_code,
2990 G.first_pty_org_id content_owner_id,
2991 --L.tax_regime_id,
2992 L.tax_regime_code,
2993 --L.tax_id,
2994 L.tax,
2995 --L.tax_status_id,
2996 L.tax_status_code,
2997 --L.tax_rate_id,
2998 L.tax_rate_code,
2999 L.tax_rate,
3000 L.tax_apportionment_line_number,
3001 G.trx_id_level2,
3002 G.trx_id_level3,
3003 G.trx_id_level4,
3004 G.trx_id_level5,
3005 -- G.trx_id_level6,
3006 L.mrc_tax_line_flag,
3007 G.ledger_id,
3008 G.establishment_id,
3009 G.legal_entity_id,
3010 L.legal_entity_tax_reg_number,
3011 L.hq_estb_reg_number,
3012 G.hq_estb_party_tax_prof_id,
3013 G.currency_conversion_date,
3014 G.currency_conversion_type,
3015 G.currency_conversion_rate,
3016 G.trx_currency_code,
3017 G.minimum_accountable_unit,
3018 G.precision,
3019 G.trx_number,
3020 G.trx_date,
3021 G.unit_price,
3022 G.line_amt line_amt,
3023 G.trx_line_quantity,
3024 G.ref_doc_application_id,
3025 G.ref_doc_entity_code,
3026 G.ref_doc_event_class_code,
3027 G.ref_doc_trx_id,
3028 G.ref_doc_line_id,
3029 G.ref_doc_line_quantity,
3030 L.line_amt other_doc_line_amt,
3031 L.tax_amt other_doc_line_tax_amt,
3032 L.taxable_amt other_doc_line_taxable_amt,
3033 L.unrounded_taxable_amt,
3034 L.unrounded_tax_amt,
3035 DECODE(L.reporting_only_flag,
3036 'N', 'Y', 'N') process_for_recovery_flag,
3037 L.tax_jurisdiction_id,
3038 L.tax_jurisdiction_code,
3039 L.place_of_supply,
3040 L.place_of_supply_type_code,
3041 L.place_of_supply_result_id,
3042 L.offset_flag,
3043 NVL(G.related_doc_date,
3044 NVL(G.provnl_tax_determination_date,
3045 NVL(G.adjusted_doc_date,
3046 NVL(G.trx_line_date, G.trx_date)))) tax_date,
3047 NVL(G.related_doc_date,
3048 NVL(G.provnl_tax_determination_date,
3049 NVL(G.adjusted_doc_date,
3050 NVL(G.trx_line_date, G.trx_date)))) tax_determine_date,
3051 NVL(G.related_doc_date,
3052 NVL(G.provnl_tax_determination_date,
3053 NVL(G.adjusted_doc_date,
3054 NVL(G.trx_line_date, G.trx_date)))) tax_point_date,
3055 G.trx_line_date,
3056 L.tax_type_code,
3057 L.tax_code,
3058 L.tax_registration_number,
3059 L.registration_party_type,
3060 L.rounding_level_code,
3061 L.rounding_rule_code,
3062 L.rounding_lvl_party_tax_prof_id,
3063 L.rounding_lvl_party_type,
3064 G.historical_flag,
3065 L.tax_amt_included_flag,
3066 L.self_assessed_flag,
3067 L.overridden_flag,
3068 L.manually_entered_flag,
3069 L.recalc_required_flag,
3070 L.settlement_flag,
3071 L.item_dist_changed_flag,
3072 L.associated_child_frozen_flag,
3073 L.tax_only_line_flag,
3074 L.compounding_dep_tax_flag,
3075 L.tax_provider_id,
3076 L.tax_applicability_result_id,
3077 L.direct_rate_result_id,
3078 DECODE(L.tax_provider_id,
3079 NULL, L.sync_with_prvdr_flag, 'Y') sync_with_prvdr_flag,
3080 L.reporting_only_flag,
3081 G.assessable_value line_assessable_value,
3082 L.tax_reg_num_det_result_id,
3083 L.record_type_code,
3084 L.tax_currency_code,
3085 G.numeric1,
3086 G.numeric2,
3087 G.numeric3,
3088 G.numeric4,
3089 G.numeric5,
3090 G.numeric6,
3091 G.numeric7,
3092 G.numeric8,
3093 G.numeric9,
3094 G.numeric10,
3095 G.char1,
3096 G.char2,
3097 G.char3,
3098 G.char4,
3099 G.char5,
3100 G.char6,
3101 G.char7,
3102 G.char8,
3103 G.char9,
3104 G.char10,
3105 G.date1,
3106 G.date2,
3107 G.date3,
3108 G.date4,
3109 G.date5,
3110 G.date6,
3111 G.date7,
3112 G.date8,
3113 G.date9,
3114 G.date10,
3115 G.related_doc_application_id,
3116 G.related_doc_entity_code,
3117 G.related_doc_event_class_code,
3118 G.related_doc_trx_id,
3119 G.related_doc_number,
3120 G.related_doc_date,
3121 G.applied_from_application_id,
3122 G.applied_from_event_class_code,
3123 G.applied_from_entity_code,
3124 G.applied_from_trx_id,
3125 G.applied_from_line_id,
3126 L.applied_from_trx_number,
3127 G.adjusted_doc_application_id,
3128 G.adjusted_doc_entity_code,
3129 G.adjusted_doc_event_class_code,
3130 G.adjusted_doc_trx_id,
3131 G.adjusted_doc_line_id,
3132 G.adjusted_doc_number,
3133 G.adjusted_doc_date,
3134 G.applied_to_application_id,
3135 G.applied_to_event_class_code,
3136 G.applied_to_entity_code,
3137 G.applied_to_trx_id,
3138 G.applied_to_trx_line_id,
3139 G.applied_to_trx_number,
3140 G.exempt_certificate_number,
3141 L.tax_currency_conversion_date,
3142 L.tax_currency_conversion_type,
3143 L.tax_currency_conversion_rate,
3144 L.tax_base_modifier_rate,
3145 L.tax_date_rule_id,
3146 L.tax_registration_id,
3147 L.compounding_tax_flag,
3148 L.tax_exemption_id,
3149 L.tax_rate_before_exemption,
3150 L.tax_rate_name_before_exemption,
3151 L.exempt_rate_modifier,
3152 L.exempt_reason,
3153 L.exempt_reason_code,
3154 L.tax_exception_id,
3155 L.tax_rate_before_exception,
3156 L.tax_rate_name_before_exception,
3157 L.exception_rate,
3158 L.taxable_basis_formula,
3159 L.tax_calculation_formula,
3160 L.tax_apportionment_flag,
3161 L.cancel_flag,
3162 L.purge_flag,
3163 L.delete_flag,
3164 L.enforce_from_natural_acct_flag,
3165 L.reporting_period_id,
3166 L.legal_message_appl_2,
3167 L.legal_message_status,
3168 L.legal_message_rate,
3169 L.legal_message_basis,
3170 L.legal_message_calc,
3171 L.legal_message_threshold,
3172 L.legal_message_pos,
3173 L.legal_message_trn,
3174 L.legal_message_exmpt,
3175 L.legal_message_excpt,
3176 L.tax_regime_template_id,
3177 L.status_result_id,
3178 L.rate_result_id,
3179 L.basis_result_id,
3180 L.thresh_result_id,
3181 L.calc_result_id,
3182 L.eval_exmpt_result_id,
3183 L.eval_excpt_result_id,
3184 L.tax_rate_type,
3185 L.legal_justification_text1,
3186 L.legal_justification_text2,
3187 L.legal_justification_text3,
3188 L.reporting_currency_code,
3189 G.ctrl_total_line_tx_amt,
3190 L.interface_entity_code,
3191 L.interface_tax_line_id,
3192 L.taxing_juris_geography_id,
3193 L.adjusted_doc_tax_line_id,
3194 L.cal_tax_amt,
3195 L.cal_tax_amt_tax_curr,
3196 L.cal_tax_amt_funcl_curr
3197 FROM zx_lines L,
3198 zx_lines_det_factors G
3199 WHERE
3200 -- commented out for bug fix 5417887
3201 -- G.application_id = p_event_class_rec.application_id
3202 --AND G.event_class_code = p_event_class_rec.event_class_code
3203 --AND G.entity_code = p_event_class_rec.entity_code
3204 --AND G.trx_id = p_event_class_rec.trx_id
3205
3206 G.event_id = p_event_class_rec.event_id
3207 AND G.line_level_action = 'COPY_AND_CREATE'
3208 AND L.application_id = G.source_application_id
3209 AND L.event_class_code = G.source_event_class_code
3210 AND L.entity_code = G.source_entity_code
3211 AND L.trx_id = G.source_trx_id
3212 AND L.trx_line_id = G.source_line_id
3213 AND L.trx_level_type = G.source_trx_level_type
3214 AND L.cancel_flag <> 'Y'
3215 AND L.offset_link_to_tax_line_id IS NULL
3216 AND L.mrc_tax_line_flag = 'N'
3217 AND ( L.manually_entered_flag = 'Y'
3218 OR L.last_manual_entry is NOT NULL )
3219 AND L.tax_provider_id IS NULL
3220 ) temp
3221 ON( tax_line.application_id = temp.application_id
3222 AND tax_line.entity_code = temp.entity_code
3223 AND tax_line.event_class_code = temp.event_class_code
3224 AND tax_line.trx_id = temp.trx_id
3225 AND tax_line.trx_line_id = temp.trx_line_id
3226 AND tax_line.tax_regime_code = temp.tax_regime_code
3227 AND tax_line.tax = temp.tax
3228 AND NVL(tax_line.tax_apportionment_line_number, 1) =
3229 NVL(temp.tax_apportionment_line_number, 1) )
3230 WHEN MATCHED THEN
3231 UPDATE SET
3232 -- tax_line_id = zx_lines_s.NEXTVAL,
3233 internal_organization_id = temp.internal_organization_id,
3234 internal_org_location_id = temp.internal_org_location_id,
3235 -- application_id = temp.application_id,
3236 -- entity_code = temp.entity_code,
3237 -- event_class_code = temp.event_class_code,
3238 event_type_code = temp.event_type_code,
3239 -- trx_id = temp.trx_id,
3240 -- trx_line_id = temp.trx_line_id,
3241 trx_level_type = temp.trx_level_type,
3242 trx_line_number = temp.trx_line_number,
3243 doc_event_status = temp.doc_event_status,
3244 tax_event_class_code = temp.tax_event_class_code,
3245 tax_event_type_code = temp.tax_event_type_code,
3246 tax_line_number = NUMBER_DUMMY,
3247 content_owner_id = temp.content_owner_id,
3248 --tax_regime_id = --temp.tax_regime_id,
3249 -- tax_regime_code = temp.tax_regime_code,
3250 --tax_id = --temp.tax_id,
3251 -- tax = temp.tax,
3252 --tax_status_id = --temp.tax_status_id,
3253 tax_status_code = temp.tax_status_code,
3254 --tax_rate_id = --temp.tax_rate_id,
3255 tax_rate_code = temp.tax_rate_code,
3256 tax_rate = temp.tax_rate,
3257 -- tax_apportionment_line_number = temp.tax_apportionment_line_number,
3258 trx_id_level2 = temp.trx_id_level2,
3259 trx_id_level3 = temp.trx_id_level3,
3260 trx_id_level4 = temp.trx_id_level4,
3261 trx_id_level5 = temp.trx_id_level5,
3262 --trx_id_level6 = -- temp.trx_id_level6,
3263 mrc_tax_line_flag = temp.mrc_tax_line_flag,
3264 ledger_id = temp.ledger_id,
3265 establishment_id = temp.establishment_id,
3266 legal_entity_id = temp.legal_entity_id,
3267 legal_entity_tax_reg_number = temp.legal_entity_tax_reg_number,
3268 hq_estb_reg_number = temp.hq_estb_reg_number,
3269 hq_estb_party_tax_prof_id = temp.hq_estb_party_tax_prof_id,
3270 currency_conversion_date = temp.currency_conversion_date,
3271 currency_conversion_type = temp.currency_conversion_type,
3272 currency_conversion_rate = temp.currency_conversion_rate,
3273 trx_currency_code = temp.trx_currency_code,
3274 minimum_accountable_unit = temp.minimum_accountable_unit,
3275 precision = temp.precision,
3276 trx_number = temp.trx_number,
3277 trx_date = temp.trx_date,
3278 unit_price = temp.unit_price,
3279 line_amt = temp.line_amt,
3280 trx_line_quantity = temp.trx_line_quantity,
3281 ref_doc_application_id = temp.ref_doc_application_id,
3282 ref_doc_entity_code = temp.ref_doc_entity_code,
3283 ref_doc_event_class_code = temp.ref_doc_event_class_code,
3284 ref_doc_trx_id = temp.ref_doc_trx_id,
3285 ref_doc_line_id = temp.ref_doc_line_id,
3286 ref_doc_line_quantity = temp.ref_doc_line_quantity,
3287 other_doc_line_amt = temp.other_doc_line_amt,
3288 other_doc_line_tax_amt = temp.other_doc_line_tax_amt,
3289 other_doc_line_taxable_amt = temp.other_doc_line_taxable_amt,
3290 unrounded_taxable_amt = temp.unrounded_taxable_amt,
3291 unrounded_tax_amt = temp.unrounded_tax_amt,
3292 process_for_recovery_flag = temp.process_for_recovery_flag,
3293 tax_jurisdiction_id = temp.tax_jurisdiction_id,
3294 tax_jurisdiction_code = temp.tax_jurisdiction_code,
3295 place_of_supply = temp.place_of_supply,
3296 place_of_supply_type_code = temp.place_of_supply_type_code,
3297 place_of_supply_result_id = temp.place_of_supply_result_id,
3298 offset_flag = temp.offset_flag,
3299 tax_date = temp.tax_date,
3300 tax_determine_date = temp.tax_determine_date,
3301 tax_point_date = temp.tax_point_date,
3302 trx_line_date = temp.trx_line_date,
3303 tax_type_code = temp.tax_type_code,
3304 tax_code = temp.tax_code,
3305 tax_registration_number = temp.tax_registration_number,
3306 registration_party_type = temp.registration_party_type,
3307 rounding_level_code = temp.rounding_level_code,
3308 rounding_rule_code = temp.rounding_rule_code,
3309 rounding_lvl_party_tax_prof_id = temp.rounding_lvl_party_tax_prof_id,
3310 rounding_lvl_party_type = temp.rounding_lvl_party_type,
3311 historical_flag = temp.historical_flag,
3312 tax_amt_included_flag = temp.tax_amt_included_flag,
3313 self_assessed_flag = temp.self_assessed_flag,
3314 overridden_flag = temp.overridden_flag,
3315 manually_entered_flag = temp.manually_entered_flag,
3316 freeze_until_overridden_flag = 'Y', -- L.freeze_until_overridden_flag,
3317 copied_from_other_doc_flag = 'Y', -- L.copied_from_other_doc_flag,
3318 recalc_required_flag = temp.recalc_required_flag,
3319 settlement_flag = temp.settlement_flag,
3320 item_dist_changed_flag = temp.item_dist_changed_flag,
3321 associated_child_frozen_flag = temp.associated_child_frozen_flag,
3322 tax_only_line_flag = temp.tax_only_line_flag,
3323 compounding_dep_tax_flag = temp.compounding_dep_tax_flag,
3324 last_manual_entry = 'TAX_AMOUNT', -- L.last_manual_entry,
3325 tax_provider_id = temp.tax_provider_id,
3326 tax_applicability_result_id = temp.tax_applicability_result_id,
3327 direct_rate_result_id = temp.direct_rate_result_id,
3328 sync_with_prvdr_flag = temp.sync_with_prvdr_flag,
3329 other_doc_source = 'SOURCE', -- L.other_doc_source
3330 reporting_only_flag = temp.reporting_only_flag,
3331 line_assessable_value = temp.line_assessable_value,
3332 tax_reg_num_det_result_id = temp.tax_reg_num_det_result_id,
3333 record_type_code = temp.record_type_code,
3334 tax_currency_code = temp.tax_currency_code,
3335 numeric1 = temp.numeric1,
3336 numeric2 = temp.numeric2,
3337 numeric3 = temp.numeric3,
3338 numeric4 = temp.numeric4,
3339 numeric5 = temp.numeric5,
3340 numeric6 = temp.numeric6,
3341 numeric7 = temp.numeric7,
3342 numeric8 = temp.numeric8,
3343 numeric9 = temp.numeric9,
3344 numeric10 = temp.numeric10,
3345 char1 = temp.char1,
3346 char2 = temp.char2,
3347 char3 = temp.char3,
3348 char4 = temp.char4,
3349 char5 = temp.char5,
3350 char6 = temp.char6,
3351 char7 = temp.char7,
3352 char8 = temp.char8,
3353 char9 = temp.char9,
3354 char10 = temp.char10,
3355 date1 = temp.date1,
3356 date2 = temp.date2,
3357 date3 = temp.date3,
3358 date4 = temp.date4,
3359 date5 = temp.date5,
3360 date6 = temp.date6,
3361 date7 = temp.date7,
3362 date8 = temp.date8,
3363 date9 = temp.date9,
3364 date10 = temp.date10,
3365 related_doc_application_id = temp.related_doc_application_id,
3366 related_doc_entity_code = temp.related_doc_entity_code,
3367 related_doc_event_class_code = temp.related_doc_event_class_code,
3368 related_doc_trx_id = temp.related_doc_trx_id,
3369 related_doc_number = temp.related_doc_number,
3370 related_doc_date = temp.related_doc_date,
3371 applied_from_application_id = temp.applied_from_application_id,
3372 applied_from_event_class_code = temp.applied_from_event_class_code,
3373 applied_from_entity_code = temp.applied_from_entity_code,
3374 applied_from_trx_id = temp.applied_from_trx_id,
3375 applied_from_line_id = temp.applied_from_line_id,
3376 applied_from_trx_number = temp.applied_from_trx_number,
3377 adjusted_doc_application_id = temp.adjusted_doc_application_id,
3378 adjusted_doc_entity_code = temp.adjusted_doc_entity_code,
3379 adjusted_doc_event_class_code = temp.adjusted_doc_event_class_code,
3380 adjusted_doc_trx_id = temp.adjusted_doc_trx_id,
3381 adjusted_doc_line_id = temp.adjusted_doc_line_id,
3382 adjusted_doc_number = temp.adjusted_doc_number,
3383 adjusted_doc_date = temp.adjusted_doc_date,
3384 applied_to_application_id = temp.applied_to_application_id,
3385 applied_to_event_class_code = temp.applied_to_event_class_code,
3386 applied_to_entity_code = temp.applied_to_entity_code,
3387 applied_to_trx_id = temp.applied_to_trx_id,
3388 applied_to_line_id = temp.applied_to_trx_line_id,
3389 applied_to_trx_number = temp.applied_to_trx_number,
3390 exempt_certificate_number = temp.exempt_certificate_number,
3391 summary_tax_line_id = NULL, -- L.summary_tax_line_id,
3392 offset_link_to_tax_line_id = NULL, -- L.offset_link_to_tax_line_id,
3393 tax_currency_conversion_date = temp.tax_currency_conversion_date,
3394 tax_currency_conversion_type = temp.tax_currency_conversion_type,
3395 tax_currency_conversion_rate = temp.tax_currency_conversion_rate,
3396 tax_base_modifier_rate = temp.tax_base_modifier_rate,
3397 tax_date_rule_id = temp.tax_date_rule_id,
3398 tax_registration_id = temp.tax_registration_id,
3399 compounding_tax_flag = temp.compounding_tax_flag,
3400 tax_amt = NULL, -- L.tax_amt,
3401 tax_amt_tax_curr = NULL, -- L.tax_amt_tax_curr,
3402 tax_amt_funcl_curr = NULL, -- L.tax_amt_funcl_curr,
3403 taxable_amt = NULL, -- L.taxable_amt,
3404 taxable_amt_tax_curr = NULL, -- L.taxable_amt_tax_curr,
3405 taxable_amt_funcl_curr = NULL, -- L.taxable_amt_funcl_curr,
3406 cal_tax_amt = temp.cal_tax_amt,
3407 cal_tax_amt_tax_curr = NULL, -- L.cal_tax_amt_tax_curr,
3408 cal_tax_amt_funcl_curr = NULL, -- L.cal_tax_amt_funcl_curr,
3409 rec_tax_amt = NULL, -- L.rec_tax_amt,
3410 rec_tax_amt_tax_curr = NULL, -- L.rec_tax_amt_tax_curr,
3411 rec_tax_amt_funcl_curr = NULL, -- L.rec_tax_amt_funcl_curr,
3412 nrec_tax_amt = NULL, -- L.nrec_tax_amt,
3413 nrec_tax_amt_tax_curr = NULL, -- L.nrec_tax_amt_tax_curr,
3414 nrec_tax_amt_funcl_curr = NULL, -- L.nrec_tax_amt_funcl_curr,
3415 tax_exemption_id = temp.tax_exemption_id,
3416 tax_rate_before_exemption = temp.tax_rate_before_exemption,
3417 tax_rate_name_before_exemption = temp.tax_rate_name_before_exemption,
3418 exempt_rate_modifier = temp.exempt_rate_modifier,
3419 exempt_reason = temp.exempt_reason,
3420 exempt_reason_code = temp.exempt_reason_code,
3421 tax_exception_id = temp.tax_exception_id,
3422 tax_rate_before_exception = temp.tax_rate_before_exception,
3423 tax_rate_name_before_exception = temp.tax_rate_name_before_exception,
3424 exception_rate = temp.exception_rate,
3425 taxable_basis_formula = temp.taxable_basis_formula,
3426 tax_calculation_formula = temp.tax_calculation_formula,
3427 tax_apportionment_flag = temp.tax_apportionment_flag,
3428 cancel_flag = temp.cancel_flag,
3429 purge_flag = temp.purge_flag,
3430 delete_flag = temp.delete_flag,
3431 enforce_from_natural_acct_flag = temp.enforce_from_natural_acct_flag,
3432 reporting_period_id = temp.reporting_period_id,
3433 legal_message_appl_2 = temp.legal_message_appl_2,
3434 legal_message_status = temp.legal_message_status,
3435 legal_message_rate = temp.legal_message_rate,
3436 legal_message_basis = temp.legal_message_basis,
3437 legal_message_calc = temp.legal_message_calc,
3438 legal_message_threshold = temp.legal_message_threshold,
3439 legal_message_pos = temp.legal_message_pos,
3440 legal_message_trn = temp.legal_message_trn,
3441 legal_message_exmpt = temp.legal_message_exmpt,
3442 legal_message_excpt = temp.legal_message_excpt,
3443 tax_regime_template_id = temp.tax_regime_template_id,
3444 status_result_id = temp.status_result_id,
3445 rate_result_id = temp.rate_result_id,
3446 basis_result_id = temp.basis_result_id,
3447 thresh_result_id = temp.thresh_result_id,
3448 calc_result_id = temp.calc_result_id,
3449 eval_exmpt_result_id = temp.eval_exmpt_result_id,
3450 eval_excpt_result_id = temp.eval_excpt_result_id,
3451 tax_hold_code = NULL, -- L.tax_hold_code,
3452 tax_hold_released_code = NULL, -- L.tax_hold_released_code,
3453 prd_total_tax_amt = NULL, -- L.prd_total_tax_amt,
3454 prd_total_tax_amt_tax_curr = NULL, -- L.prd_total_tax_amt_tax_curr,
3455 prd_total_tax_amt_funcl_curr = NULL, -- L.prd_total_tax_amt_funcl_curr,
3456 tax_rate_type = temp.tax_rate_type,
3457 legal_justification_text1 = temp.legal_justification_text1,
3458 legal_justification_text2 = temp.legal_justification_text2,
3459 legal_justification_text3 = temp.legal_justification_text3,
3460 reporting_currency_code = temp.reporting_currency_code,
3461 trx_line_index = NULL, -- L.trx_line_index
3462 offset_tax_rate_code = NULL, -- L.offset_tax_rate_code
3463 proration_code = NULL, -- L.proration_code
3464 ctrl_total_line_tx_amt = temp.ctrl_total_line_tx_amt,
3465 created_by = l_user_id, -- created_by,
3466 creation_date = sysdate, -- creation_date,
3467 last_updated_by = l_user_id, -- last_updated_by
3468 last_update_date = sysdate, -- last_update_date,
3469 last_update_login = l_login_id,
3470 interface_entity_code = temp.interface_entity_code,
3471 interface_tax_line_id = temp.interface_tax_line_id,
3472 taxing_juris_geography_id = temp.taxing_juris_geography_id,
3473 adjusted_doc_tax_line_id = temp.adjusted_doc_tax_line_id,
3474 object_version_number = 1
3475
3476 WHEN NOT MATCHED THEN
3477 INSERT ( tax_line_id,
3478 internal_organization_id,
3479 internal_org_location_id,
3480 application_id,
3481 entity_code,
3482 event_class_code,
3483 event_type_code,
3484 trx_id,
3485 trx_line_id,
3486 trx_level_type,
3487 trx_line_number,
3488 doc_event_status,
3489 tax_event_class_code,
3490 tax_event_type_code,
3491 tax_line_number,
3492 content_owner_id,
3493 --tax_regime_id,
3494 tax_regime_code,
3495 --tax_id,
3496 tax,
3497 --tax_status_id,
3498 tax_status_code,
3499 --tax_rate_id,
3500 tax_rate_code,
3501 tax_rate,
3502 tax_apportionment_line_number,
3503 trx_id_level2,
3504 trx_id_level3,
3505 trx_id_level4,
3506 trx_id_level5,
3507 --trx_id_level6,
3508 mrc_tax_line_flag,
3509 ledger_id,
3510 establishment_id,
3511 legal_entity_id,
3512 legal_entity_tax_reg_number,
3513 hq_estb_reg_number,
3514 hq_estb_party_tax_prof_id,
3515 currency_conversion_date,
3516 currency_conversion_type,
3517 currency_conversion_rate,
3518 trx_currency_code,
3519 minimum_accountable_unit,
3520 precision,
3521 trx_number,
3522 trx_date,
3523 unit_price,
3524 line_amt,
3525 trx_line_quantity,
3526 ref_doc_application_id,
3527 ref_doc_entity_code,
3528 ref_doc_event_class_code,
3529 ref_doc_trx_id,
3530 ref_doc_line_id,
3531 ref_doc_line_quantity,
3532 other_doc_line_amt,
3533 other_doc_line_tax_amt,
3534 other_doc_line_taxable_amt,
3535 unrounded_taxable_amt,
3536 unrounded_tax_amt,
3537 process_for_recovery_flag,
3538 tax_jurisdiction_id,
3539 tax_jurisdiction_code,
3540 place_of_supply,
3541 place_of_supply_type_code,
3542 place_of_supply_result_id,
3543 offset_flag,
3544 tax_date,
3545 tax_determine_date,
3546 tax_point_date,
3547 trx_line_date,
3548 tax_type_code,
3549 tax_code,
3550 tax_registration_number,
3551 registration_party_type,
3552 rounding_level_code,
3553 rounding_rule_code,
3554 rounding_lvl_party_tax_prof_id,
3555 rounding_lvl_party_type,
3556 historical_flag,
3557 tax_amt_included_flag,
3558 self_assessed_flag,
3559 overridden_flag,
3560 manually_entered_flag,
3561 freeze_until_overridden_flag,
3562 copied_from_other_doc_flag,
3563 recalc_required_flag,
3564 settlement_flag,
3565 item_dist_changed_flag,
3566 associated_child_frozen_flag,
3567 tax_only_line_flag,
3568 compounding_dep_tax_flag,
3569 last_manual_entry,
3570 tax_provider_id,
3571 tax_applicability_result_id,
3572 direct_rate_result_id,
3573 sync_with_prvdr_flag,
3574 other_doc_source,
3575 reporting_only_flag,
3576 line_assessable_value,
3577 tax_reg_num_det_result_id,
3578 record_type_code,
3579 tax_currency_code,
3580 numeric1,
3581 numeric2,
3582 numeric3,
3583 numeric4,
3584 numeric5,
3585 numeric6,
3586 numeric7,
3587 numeric8,
3588 numeric9,
3589 numeric10,
3590 char1,
3591 char2,
3592 char3,
3593 char4,
3594 char5,
3595 char6,
3596 char7,
3597 char8,
3598 char9,
3599 char10,
3600 date1,
3601 date2,
3602 date3,
3603 date4,
3604 date5,
3605 date6,
3606 date7,
3607 date8,
3608 date9,
3609 date10,
3610 related_doc_application_id,
3611 related_doc_entity_code,
3612 related_doc_event_class_code,
3613 related_doc_trx_id,
3614 related_doc_number,
3615 related_doc_date,
3616 applied_from_application_id,
3617 applied_from_event_class_code,
3618 applied_from_entity_code,
3619 applied_from_trx_id,
3620 applied_from_line_id,
3621 applied_from_trx_number,
3622 adjusted_doc_application_id,
3623 adjusted_doc_entity_code,
3624 adjusted_doc_event_class_code,
3625 adjusted_doc_trx_id,
3626 adjusted_doc_line_id,
3627 adjusted_doc_number,
3628 adjusted_doc_date,
3629 applied_to_application_id,
3630 applied_to_event_class_code,
3631 applied_to_entity_code,
3632 applied_to_trx_id,
3633 applied_to_line_id,
3634 applied_to_trx_number,
3635 exempt_certificate_number,
3636 summary_tax_line_id,
3637 offset_link_to_tax_line_id,
3638 tax_currency_conversion_date,
3639 tax_currency_conversion_type,
3640 tax_currency_conversion_rate,
3641 tax_base_modifier_rate,
3642 tax_date_rule_id,
3643 tax_registration_id,
3644 compounding_tax_flag,
3645 tax_amt,
3646 tax_amt_tax_curr,
3647 tax_amt_funcl_curr,
3648 taxable_amt,
3649 taxable_amt_tax_curr,
3650 taxable_amt_funcl_curr,
3651 cal_tax_amt,
3652 cal_tax_amt_tax_curr,
3653 cal_tax_amt_funcl_curr,
3654 rec_tax_amt,
3655 rec_tax_amt_tax_curr,
3656 rec_tax_amt_funcl_curr,
3657 nrec_tax_amt,
3658 nrec_tax_amt_tax_curr,
3659 nrec_tax_amt_funcl_curr,
3660 tax_exemption_id,
3661 tax_rate_before_exemption,
3662 tax_rate_name_before_exemption,
3663 exempt_rate_modifier,
3664 exempt_reason,
3665 exempt_reason_code,
3666 tax_exception_id,
3667 tax_rate_before_exception,
3668 tax_rate_name_before_exception,
3669 exception_rate,
3670 taxable_basis_formula,
3671 tax_calculation_formula,
3672 tax_apportionment_flag,
3673 cancel_flag,
3674 purge_flag,
3675 delete_flag,
3676 enforce_from_natural_acct_flag,
3677 reporting_period_id,
3678 legal_message_appl_2,
3679 legal_message_status,
3680 legal_message_rate,
3681 legal_message_basis,
3682 legal_message_calc,
3683 legal_message_threshold,
3684 legal_message_pos,
3685 legal_message_trn,
3686 legal_message_exmpt,
3687 legal_message_excpt,
3688 tax_regime_template_id,
3689 status_result_id,
3690 rate_result_id,
3691 basis_result_id,
3692 thresh_result_id,
3693 calc_result_id,
3694 eval_exmpt_result_id,
3695 eval_excpt_result_id,
3696 tax_hold_code,
3697 tax_hold_released_code,
3698 prd_total_tax_amt,
3699 prd_total_tax_amt_tax_curr,
3700 prd_total_tax_amt_funcl_curr,
3701 tax_rate_type,
3702 legal_justification_text1,
3703 legal_justification_text2,
3704 legal_justification_text3,
3705 reporting_currency_code,
3706 trx_line_index,
3707 offset_tax_rate_code,
3708 proration_code,
3709 ctrl_total_line_tx_amt,
3710 created_by,
3711 creation_date,
3712 last_updated_by,
3713 last_update_date,
3714 last_update_login,
3715 interface_entity_code,
3716 interface_tax_line_id,
3717 taxing_juris_geography_id,
3718 adjusted_doc_tax_line_id,
3719 object_version_number,
3720 legal_reporting_status
3721 )
3722 VALUES(
3723 zx_lines_s.NEXTVAL, -- tax_line_id,
3724 temp.internal_organization_id,
3725 temp.internal_org_location_id,
3726 temp.application_id,
3727 temp.entity_code,
3728 temp.event_class_code,
3729 temp.event_type_code,
3730 temp.trx_id,
3731 temp.trx_line_id,
3732 temp.trx_level_type,
3733 temp.trx_line_number,
3734 temp.doc_event_status,
3735 temp.tax_event_class_code,
3736 temp.tax_event_type_code,
3737 NUMBER_DUMMY, -- L.tax_line_number,
3738 temp.content_owner_id,
3739 --temp.tax_regime_id,
3740 temp.tax_regime_code,
3741 --temp.tax_id,
3742 temp.tax,
3743 --temp.tax_status_id,
3744 temp.tax_status_code,
3745 --temp.tax_rate_id,
3746 temp.tax_rate_code,
3747 temp.tax_rate,
3748 temp.tax_apportionment_line_number,
3749 temp.trx_id_level2,
3750 temp.trx_id_level3,
3751 temp.trx_id_level4,
3752 temp.trx_id_level5,
3753 -- temp.trx_id_level6,
3754 temp.mrc_tax_line_flag,
3755 temp.ledger_id,
3756 temp.establishment_id,
3757 temp.legal_entity_id,
3758 temp.legal_entity_tax_reg_number,
3759 temp.hq_estb_reg_number,
3760 temp.hq_estb_party_tax_prof_id,
3761 temp.currency_conversion_date,
3762 temp.currency_conversion_type,
3763 temp.currency_conversion_rate,
3764 temp.trx_currency_code,
3765 temp.minimum_accountable_unit,
3766 temp.precision,
3767 temp.trx_number,
3768 temp.trx_date,
3769 temp.unit_price,
3770 temp.line_amt,
3771 temp.trx_line_quantity,
3772 temp.ref_doc_application_id,
3773 temp.ref_doc_entity_code,
3774 temp.ref_doc_event_class_code,
3775 temp.ref_doc_trx_id,
3776 temp.ref_doc_line_id,
3777 temp.ref_doc_line_quantity,
3778 temp.other_doc_line_amt,
3779 temp.other_doc_line_tax_amt,
3780 temp.other_doc_line_taxable_amt,
3781 temp.unrounded_taxable_amt,
3782 temp.unrounded_tax_amt,
3783 temp.process_for_recovery_flag,
3784 temp.tax_jurisdiction_id,
3785 temp.tax_jurisdiction_code,
3786 temp.place_of_supply,
3787 temp.place_of_supply_type_code,
3788 temp.place_of_supply_result_id,
3789 temp.offset_flag,
3790 temp.tax_date,
3791 temp.tax_determine_date,
3792 temp.tax_point_date,
3793 temp.trx_line_date,
3794 temp.tax_type_code,
3795 temp.tax_code,
3796 temp.tax_registration_number,
3797 temp.registration_party_type,
3798 temp.rounding_level_code,
3799 temp.rounding_rule_code,
3800 temp.rounding_lvl_party_tax_prof_id,
3801 temp.rounding_lvl_party_type,
3802 temp.historical_flag,
3803 temp.tax_amt_included_flag,
3804 temp.self_assessed_flag,
3805 temp.overridden_flag,
3806 temp.manually_entered_flag,
3807 'Y', -- L.freeze_until_overridden_flag,
3808 'Y', -- L.copied_from_other_doc_flag,
3809 temp.recalc_required_flag,
3810 temp.settlement_flag,
3811 temp.item_dist_changed_flag,
3812 temp.associated_child_frozen_flag,
3813 temp.tax_only_line_flag,
3814 temp.compounding_dep_tax_flag,
3815 'TAX_AMOUNT', -- L.last_manual_entry,
3816 temp.tax_provider_id,
3817 temp.tax_applicability_result_id,
3818 temp.direct_rate_result_id,
3819 temp.sync_with_prvdr_flag,
3820 'SOURCE', -- L.other_doc_source
3821 temp.reporting_only_flag,
3822 temp.line_assessable_value,
3823 temp.tax_reg_num_det_result_id,
3824 temp.record_type_code,
3825 temp.tax_currency_code,
3826 temp.numeric1,
3827 temp.numeric2,
3828 temp.numeric3,
3829 temp.numeric4,
3830 temp.numeric5,
3831 temp.numeric6,
3832 temp.numeric7,
3833 temp.numeric8,
3834 temp.numeric9,
3835 temp.numeric10,
3836 temp.char1,
3837 temp.char2,
3838 temp.char3,
3839 temp.char4,
3840 temp.char5,
3841 temp.char6,
3842 temp.char7,
3843 temp.char8,
3844 temp.char9,
3845 temp.char10,
3846 temp.date1,
3847 temp.date2,
3848 temp.date3,
3849 temp.date4,
3850 temp.date5,
3851 temp.date6,
3852 temp.date7,
3853 temp.date8,
3854 temp.date9,
3855 temp.date10,
3856 temp.related_doc_application_id,
3857 temp.related_doc_entity_code,
3858 temp.related_doc_event_class_code,
3859 temp.related_doc_trx_id,
3860 temp.related_doc_number,
3861 temp.related_doc_date,
3862 temp.applied_from_application_id,
3863 temp.applied_from_event_class_code,
3864 temp.applied_from_entity_code,
3865 temp.applied_from_trx_id,
3866 temp.applied_from_line_id,
3867 temp.applied_from_trx_number,
3868 temp.adjusted_doc_application_id,
3869 temp.adjusted_doc_entity_code,
3870 temp.adjusted_doc_event_class_code,
3871 temp.adjusted_doc_trx_id,
3872 temp.adjusted_doc_line_id,
3873 temp.adjusted_doc_number,
3874 temp.adjusted_doc_date,
3875 temp.applied_to_application_id,
3876 temp.applied_to_event_class_code,
3877 temp.applied_to_entity_code,
3878 temp.applied_to_trx_id,
3879 temp.applied_to_trx_line_id,
3880 temp.applied_to_trx_number,
3881 temp.exempt_certificate_number,
3882 NULL, -- L.summary_tax_line_id,
3883 NULL, -- L.offset_link_to_tax_line_id,
3884 temp.tax_currency_conversion_date,
3885 temp.tax_currency_conversion_type,
3886 temp.tax_currency_conversion_rate,
3887 temp.tax_base_modifier_rate,
3888 temp.tax_date_rule_id,
3889 temp.tax_registration_id,
3890 temp.compounding_tax_flag,
3891 NULL, -- L.tax_amt,
3892 NULL, -- L.tax_amt_tax_curr,
3893 NULL, -- L.tax_amt_funcl_curr,
3894 NULL, -- L.taxable_amt,
3895 NULL, -- L.taxable_amt_tax_curr,
3896 NULL, -- L.taxable_amt_funcl_curr,
3897 temp.cal_tax_amt,
3898 NULL, -- L.cal_tax_amt_tax_curr,
3899 NULL, -- L.cal_tax_amt_funcl_curr,
3900 NULL, -- L.rec_tax_amt,
3901 NULL, -- L.rec_tax_amt_tax_curr,
3902 NULL, -- L.rec_tax_amt_funcl_curr,
3903 NULL, -- L.nrec_tax_amt,
3904 NULL, -- L.nrec_tax_amt_tax_curr,
3905 NULL, -- L.nrec_tax_amt_funcl_curr,
3906 temp.tax_exemption_id,
3907 temp.tax_rate_before_exemption,
3908 temp.tax_rate_name_before_exemption,
3909 temp.exempt_rate_modifier,
3910 temp.exempt_reason,
3911 temp.exempt_reason_code,
3912 temp.tax_exception_id,
3913 temp.tax_rate_before_exception,
3914 temp.tax_rate_name_before_exception,
3915 temp.exception_rate,
3916 temp.taxable_basis_formula,
3917 temp.tax_calculation_formula,
3918 temp.tax_apportionment_flag,
3919 temp.cancel_flag,
3920 temp.purge_flag,
3921 temp.delete_flag,
3922 temp.enforce_from_natural_acct_flag,
3923 temp.reporting_period_id,
3924 temp.legal_message_appl_2,
3925 temp.legal_message_status,
3926 temp.legal_message_rate,
3927 temp.legal_message_basis,
3928 temp.legal_message_calc,
3929 temp.legal_message_threshold,
3930 temp.legal_message_pos,
3931 temp.legal_message_trn,
3932 temp.legal_message_exmpt,
3933 temp.legal_message_excpt,
3934 temp.tax_regime_template_id,
3935 temp.status_result_id,
3936 temp.rate_result_id,
3937 temp.basis_result_id,
3938 temp.thresh_result_id,
3939 temp.calc_result_id,
3940 temp.eval_exmpt_result_id,
3941 temp.eval_excpt_result_id,
3942 NULL, -- L.tax_hold_code,
3943 NULL, -- L.tax_hold_released_code,
3944 NULL, -- L.prd_total_tax_amt,
3945 NULL, -- L.prd_total_tax_amt_tax_curr,
3946 NULL, -- L.prd_total_tax_amt_funcl_curr,
3947 temp.tax_rate_type,
3948 temp.legal_justification_text1,
3949 temp.legal_justification_text2,
3950 temp.legal_justification_text3,
3951 temp.reporting_currency_code,
3952 NULL, -- L.trx_line_index
3953 NULL, -- L.offset_tax_rate_code
3954 NULL, -- L.proration_code
3955 temp.ctrl_total_line_tx_amt,
3956 l_user_id, -- created_by,
3957 sysdate, -- creation_date,
3958 l_user_id, -- last_updated_by
3959 sysdate, -- last_update_date,
3960 l_login_id,
3961 temp.interface_entity_code,
3962 temp.interface_tax_line_id,
3963 temp.taxing_juris_geography_id,
3964 temp.adjusted_doc_tax_line_id,
3965 1,
3966 '000000000000000');
3967
3968 OPEN get_tax_info_csr;
3969 LOOP
3970
3971 FETCH get_tax_info_csr BULK COLLECT INTO
3972 l_tax_line_id_tab,
3973 l_tax_regime_code_tab,
3974 l_tax_jurisdiction_code_tab,
3975 l_tax_tab,
3976 l_tax_status_code_tab,
3977 l_tax_rate_code_tab,
3978 l_tax_determine_date_tab
3979 LIMIT c_lines_per_fetch;
3980
3981 FOR i in 1 .. l_tax_line_id_tab.count LOOP
3982
3983 -- validate and populate tax_regime_id
3984 --
3985 ZX_TDS_UTILITIES_PKG.get_regime_cache_info(
3986 l_tax_regime_code_tab(i),
3987 l_tax_determine_date_tab(i),
3988 l_tax_regime_rec,
3989 x_return_status,
3990 x_error_buffer);
3991
3992 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3993 IF (g_level_unexpected >= g_current_runtime_level ) THEN
3994 FND_LOG.STRING(g_level_unexpected,
3995 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
3996 'Incorrect return_status after calling ' ||
3997 'ZX_TDS_UTILITIES_PKG.get_regime_cache_info');
3998 FND_LOG.STRING(g_level_unexpected,
3999 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4000 'RETURN_STATUS = ' || x_return_status);
4001 FND_LOG.STRING(g_level_unexpected,
4002 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create.END',
4003 'ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create(-)');
4004 END IF;
4005 RETURN;
4006 END IF;
4007
4008 l_tax_regime_id_tab(i) := l_tax_regime_rec.tax_regime_id;
4009
4010 -- validate and populate tax_id
4011 --
4012 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
4013 l_tax_regime_code_tab(i),
4014 l_tax_tab(i),
4015 l_tax_determine_date_tab(i),
4016 l_tax_rec,
4017 x_return_status,
4018 x_error_buffer);
4019
4020 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4021 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4022 FND_LOG.STRING(g_level_unexpected,
4023 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4024 'Incorrect return_status after calling ' ||
4025 'ZX_TDS_UTILITIES_PKG.get_tax_cache_info');
4026 FND_LOG.STRING(g_level_unexpected,
4027 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4028 'RETURN_STATUS = ' || x_return_status);
4029 FND_LOG.STRING(g_level_unexpected,
4030 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create.END',
4031 'ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create(-)');
4032 END IF;
4033 RETURN;
4034 END IF;
4035
4036 l_tax_id_tab(i) := l_tax_rec.tax_id;
4037
4038 IF l_tax_jurisdiction_code_tab(i) IS NOT NULL THEN
4039 ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info(
4040 l_tax_regime_code_tab(i),
4041 l_tax_tab(i),
4042 l_tax_jurisdiction_code_tab(i),
4043 l_tax_determine_date_tab(i),
4044 l_tax_jurisdiction_rec,
4045 x_return_status,
4046 x_error_buffer);
4047
4048 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4049 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4050 FND_LOG.STRING(g_level_unexpected,
4051 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4052 'Incorrect return_status after calling ' ||
4053 'ZX_TDS_UTILITIES_PKG.get_jurisdiction_cache_info');
4054 FND_LOG.STRING(g_level_unexpected,
4055 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4056 'RETURN_STATUS = ' || x_return_status);
4057 FND_LOG.STRING(g_level_unexpected,
4058 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create.END',
4059 'ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create(-)');
4060 END IF;
4061 RETURN;
4062 END IF;
4063
4064 l_tax_jur_id_tab(i) := l_tax_jurisdiction_rec.tax_jurisdiction_id;
4065
4066 ELSE
4067
4068 l_tax_jur_id_tab(i) := NULL;
4069 END IF;
4070
4071 -- validate and populate tax_status_id
4072 --
4073 ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info(
4074 l_tax_tab(i),
4075 l_tax_regime_code_tab(i),
4076 l_tax_status_code_tab(i),
4077 l_tax_determine_date_tab(i),
4078 l_tax_status_rec,
4079 x_return_status,
4080 x_error_buffer);
4081
4082 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4083 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4084 FND_LOG.STRING(g_level_unexpected,
4085 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4086 'Incorrect return_status after calling ' ||
4087 'ZX_TDS_UTILITIES_PKG.get_tax_status_cache_info');
4088 FND_LOG.STRING(g_level_unexpected,
4089 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4090 'RETURN_STATUS = ' || x_return_status);
4091 FND_LOG.STRING(g_level_unexpected,
4092 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create.END',
4093 'ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create(-)');
4094 END IF;
4095 RETURN;
4096 END IF;
4097
4098 l_tax_status_id_tab(i) := l_tax_status_rec.tax_status_id;
4099
4100 -- validate and populate tax_rate_id
4101 --
4102 ZX_TDS_UTILITIES_PKG.get_tax_rate_info(
4103 l_tax_regime_code_tab(i),
4104 l_tax_tab(i),
4105 l_tax_jurisdiction_code_tab(i),
4106 l_tax_status_code_tab(i),
4107 l_tax_rate_code_tab(i),
4108 l_tax_determine_date_tab(i),
4109 l_tax_class,
4110 l_tax_rate_rec,
4111 x_return_status,
4112 x_error_buffer);
4113
4114 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4115 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4116 FND_LOG.STRING(g_level_unexpected,
4117 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4118 'Incorrect return_status after calling ' ||
4119 'ZX_TDS_UTILITIES_PKG.get_tax_rate_info');
4120 FND_LOG.STRING(g_level_unexpected,
4121 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4122 'RETURN_STATUS = ' || x_return_status);
4123 FND_LOG.STRING(g_level_unexpected,
4124 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create.END',
4125 'ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create(-)');
4126 END IF;
4127 RETURN;
4128 END IF;
4129
4130 l_tax_rate_id_tab(i) := l_tax_rate_rec.tax_rate_id;
4131
4132 END LOOP;
4133
4134 FORALL i in 1 .. l_tax_line_id_tab.COUNT
4135 UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
4136 zx_detail_tax_lines_gt
4137 SET tax_regime_id = l_tax_regime_id_tab(i),
4138 tax_id = l_tax_id_tab(i),
4139 tax_jurisdiction_id = l_tax_jur_id_tab(i),
4140 tax_status_id = l_tax_status_id_tab(i),
4141 tax_rate_id = l_tax_rate_id_tab(i)
4142 WHERE tax_line_id = l_tax_line_id_tab(i);
4143
4144 EXIT WHEN get_tax_info_csr%NOTFOUND;
4145 END LOOP;
4146 CLOSE get_tax_info_csr;
4147
4148 IF (g_level_procedure >= g_current_runtime_level ) THEN
4149 FND_LOG.STRING(g_level_procedure,
4150 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4151 'p_return_status = ' || x_return_status);
4152 FND_LOG.STRING(g_level_procedure,
4153 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4154 'p_error_buffer = ' || x_error_buffer);
4155 FND_LOG.STRING(g_level_procedure,
4156 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create.END',
4157 'ZX_TDS_TAX_LINES_DETM_PKG: process_copy_and_create(-)');
4158 END IF;
4159
4160 EXCEPTION
4161 WHEN OTHERS THEN
4162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4163 x_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4164 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4165 FND_LOG.STRING(g_level_unexpected,
4166 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_copy_and_create',
4167 x_error_buffer);
4168 END IF;
4169
4170 END process_copy_and_create;
4171
4172 -----------------------------------------------------------------------
4173 -- PRIVATE PROCEDURE
4174 -- adjust_overapplication
4175 --
4176 -- DESCRIPTION
4177 --
4178 -- HISTORY
4179 -- Ling Zhang Feb-23-2005 Created for Bug Fix 4151574
4180 --
4181
4182 PROCEDURE adjust_overapplication(
4183 -- p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
4184 x_return_status OUT NOCOPY VARCHAR2,
4185 x_error_buffer OUT NOCOPY VARCHAR2)
4186 IS
4187 -- bug 14102214 using forceful hint ZX_LINES_N1 on cm_tax table
4188 CURSOR get_tax_amts IS
4189 SELECT det.unrounded_tax_amt
4190 ,det.tax_amt_tax_curr
4191 ,det.tax_amt_funcl_curr
4192 ,det.manually_entered_flag
4193 ,det.copied_from_other_doc_flag
4194 ,v.tax_amt
4195 ,v.line_amt
4196 ,v.remain_amt
4197 ,v.remain_unrounded_amt
4198 ,v.remain_amt_tax_curr
4199 ,v.remain_amt_funcl_curr
4200 ,v.reporting_currency_code
4201 ,v.tax_line_id
4202 ,v.remain_line_amt
4203 ,v.orig_tax_amt
4204 ,v.orig_line_amt
4205 FROM zx_detail_tax_lines_gt det
4206 ,(SELECT /*+ INDEX(tax ZX_DETAIL_TAX_LINES_GT_U1) INDEX(cm_tax ZX_LINES_N1) */
4207 tax.tax_amt tax_amt
4208 ,tax.line_amt line_amt
4209 ,org_tax.tax_amt + NVL(SUM(cm_tax.tax_amt), 0) remain_amt
4210 ,org_tax.unrounded_tax_amt + NVL(SUM(cm_tax.unrounded_tax_amt), 0) remain_unrounded_amt
4211 ,org_tax.tax_amt_tax_curr + NVL(SUM(cm_tax.tax_amt_tax_curr), 0) remain_amt_tax_curr
4212 ,org_tax.tax_amt_funcl_curr + NVL(SUM(cm_tax.tax_amt_funcl_curr), 0) remain_amt_funcl_curr
4213 ,org_tax.reporting_currency_code
4214 ,tax.tax_line_id
4215 ,org_tax.line_amt + NVL(SUM(cm_tax.line_amt), 0) remain_line_amt
4216 ,org_tax.tax_amt orig_tax_amt
4217 ,org_tax.line_amt orig_line_amt
4218 FROM zx_lines org_tax,
4219 zx_lines cm_tax,
4220 zx_detail_tax_lines_gt tax
4221 WHERE
4222 -- commented out for bug fix 5417887
4223 -- tax.application_id = p_event_class_rec.application_id
4224 --AND tax.entity_code = p_event_class_rec.entity_code
4225 --AND tax.event_class_code = p_event_class_rec.event_class_code
4226 --AND tax.trx_id = p_event_class_rec.trx_id
4227
4228 tax.event_class_code = 'CREDIT_MEMO' -- added for bug fix 5417887
4229 AND org_tax.tax_line_id = tax.adjusted_doc_tax_line_id
4230 AND cm_tax.adjusted_doc_tax_line_id(+) = tax.adjusted_doc_tax_line_id
4231 AND cm_tax.adjusted_doc_trx_id(+) = tax.adjusted_doc_trx_id
4232 AND cm_tax.adjusted_doc_line_id(+) = tax.adjusted_doc_line_id
4233 AND cm_tax.application_id(+) = tax.application_id
4234 AND cm_tax.trx_id(+) <> tax.trx_id
4235 -- filter out the tax lines for the current credit memo
4236 -- can't just use the tax_line_id equal condition, since for update
4237 -- case, the credt memo tax lines will be deleted and recreated,
4238 -- hence the tax_line_id will change.
4239 /* AND NOT (tax.application_id = cm_tax.application_id
4240 AND tax.entity_code = cm_tax.entity_code
4241 AND tax.event_class_code = cm_tax.event_class_code
4242 AND tax.trx_id = cm_tax.trx_id
4243 --AND tax.trx_level_type = cm_tax.trx_level_type
4244 --AND tax.trx_line_id = cm_tax.trx_line_id
4245 --AND tax.tax_regime_code = cm_tax.tax_regime_code
4246 --AND tax.tax = cm_tax.tax
4247 --AND tax.tax_apportionment_line_number = cm.tax_apportionment_line_number
4248 )
4249 */
4250 AND org_tax.Cancel_Flag <> 'Y'
4251 AND cm_tax.Cancel_Flag(+) <> 'Y'
4252 /* AND ( ( cm_tax.mrc_tax_line_flag = 'N'
4253 AND org_tax.mrc_tax_line_flag = 'N'
4254 AND tax.mrc_tax_line_flag = 'N')
4255 OR ( org_tax.reporting_currency_code = tax.reporting_currency_code
4256 AND tax.reporting_currency_code = cm_tax.reporting_currency_code))
4257 */
4258 GROUP BY org_tax.reporting_currency_code
4259 ,tax.tax_line_id
4260 ,tax.tax_amt
4261 ,tax.line_amt
4262 ,tax.unrounded_tax_amt
4263 ,org_tax.tax_amt
4264 ,org_tax.line_amt
4265 ,org_tax.unrounded_tax_amt
4266 ,org_tax.tax_amt_tax_curr
4267 ,org_tax.tax_amt_funcl_curr) v
4268 WHERE det.tax_line_id = v.tax_line_id;
4269
4270
4271 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4272 TYPE var_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
4273 TYPE var1_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
4274
4275 l_unrounded_tax_amt_tbl num_tbl_type;
4276 l_tax_amt_tax_curr_tbl num_tbl_type;
4277 l_tax_amt_funcl_curr_tbl num_tbl_type;
4278 l_manually_entered_flag_tbl var1_tbl_type;
4279 l_cpd_frm_other_doc_flg_tbl var1_tbl_type;
4280
4281 l_tax_amt_tbl num_tbl_type;
4282 l_remain_amt_tbl num_tbl_type;
4283 l_remain_unrounded_amt_tbl num_tbl_type;
4284 l_remain_amt_tax_curr_tbl num_tbl_type;
4285 l_remain_amt_funcl_curr_tbl num_tbl_type;
4286 l_rpt_currency_code_tbl var_tbl_type;
4287 l_tax_line_id_tbl num_tbl_type;
4288 l_line_amt_tbl num_tbl_type;
4289 l_remain_line_amt_tbl num_tbl_type;
4290
4291 l_sign_tax_amt NUMBER; -- mark the sign of the cm tax amt
4292
4293 -- bug 6919608
4294 l_orig_tax_amt_tbl num_tbl_type;
4295 l_orig_line_amt_tbl num_tbl_type;
4296
4297 BEGIN
4298 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4299
4300 IF (g_level_procedure >= g_current_runtime_level ) THEN
4301 FND_LOG.STRING(g_level_procedure,
4302 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.adjust_overapplication.BEGIN',
4303 'ZX_TDS_TAX_LINES_DETM_PKG: adjust_overapplication(+)');
4304 END IF;
4305
4306 x_return_status := FND_API.G_RET_STS_SUCCESS;
4307
4308 -- if it is a historical trx, do nothing
4309 --IF p_event_class_rec.CTRL_TOTAL_HDR_TX_AMT IS NOT NULL
4310 -- OR p_event_class_rec.CTRL_TOTAL_LINE_TX_AMT_FLG = 'Y'
4311 --THEN
4312 -- IF (g_level_statement >= g_current_runtime_level ) THEN
4313 --
4314 -- FND_LOG.STRING(g_level_statement,
4315 -- 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.adjust_overapplication.END',
4316 -- 'ZX_TDS_TAX_LINES_DETM_PKG: adjust_overapplication(-)'||' do not check for historical trx');
4317 -- END IF;
4318 -- RETURN;
4319 --END IF;
4320
4321 OPEN get_tax_amts;
4322 FETCH get_tax_amts BULK COLLECT INTO
4323 l_unrounded_tax_amt_tbl
4324 ,l_tax_amt_tax_curr_tbl
4325 ,l_tax_amt_funcl_curr_tbl
4326 ,l_manually_entered_flag_tbl
4327 ,l_cpd_frm_other_doc_flg_tbl
4328 ,l_tax_amt_tbl
4329 ,l_line_amt_tbl
4330 ,l_remain_amt_tbl
4331 ,l_remain_unrounded_amt_tbl
4332 ,l_remain_amt_tax_curr_tbl
4333 ,l_remain_amt_funcl_curr_tbl
4334 ,l_rpt_currency_code_tbl
4335 ,l_tax_line_id_tbl
4336 ,l_remain_line_amt_tbl
4337 ,l_orig_tax_amt_tbl
4338 ,l_orig_line_amt_tbl;
4339 CLOSE get_tax_amts;
4340
4341 -- bug 6919608
4342 --
4343 FOR i IN NVL(l_tax_amt_tbl.FIRST, 0) .. NVL(l_tax_amt_tbl.LAST, -1) LOOP
4344 IF l_orig_tax_amt_tbl(i) > 0 THEN
4345 IF l_remain_amt_tbl(i) < 0 THEN
4346 l_remain_amt_tbl(i) := 0;
4347 END IF;
4348 ELSE
4349 IF l_remain_amt_tbl(i) > 0 THEN
4350 l_remain_amt_tbl(i) := 0;
4351 END IF;
4352 END IF;
4353
4354 IF l_orig_line_amt_tbl(i) > 0 THEN
4355 IF l_remain_line_amt_tbl(i) < 0 THEN
4356 l_remain_line_amt_tbl(i) := 0;
4357 l_remain_amt_tbl(i) := 0;
4358 END IF;
4359 ELSE
4360 IF l_remain_line_amt_tbl(i) > 0 THEN
4361 l_remain_line_amt_tbl(i) := 0;
4362 l_remain_amt_tbl(i) := 0;
4363 END IF;
4364 END IF;
4365 IF l_manually_entered_flag_tbl(i) = 'Y' AND l_cpd_frm_other_doc_flg_tbl(i) = 'N' THEN
4366 IF ABS(l_remain_amt_tbl(i)) > ABS(l_tax_amt_tbl(i)) THEN
4367 l_remain_amt_tbl(i) := l_tax_amt_tbl(i);
4368 l_remain_unrounded_amt_tbl(i) := l_unrounded_tax_amt_tbl(i);
4369 l_remain_amt_tax_curr_tbl(i) := l_tax_amt_tax_curr_tbl(i);
4370 l_remain_amt_funcl_curr_tbl(i) := l_tax_amt_funcl_curr_tbl(i);
4371 END IF;
4372 END IF;
4373 END LOOP;
4374
4375 FORALL i IN NVL(l_tax_amt_tbl.FIRST, 0) .. NVL(l_tax_amt_tbl.LAST, -1)
4376 UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
4377 zx_detail_tax_lines_gt
4378 SET tax_amt = sign(l_tax_amt_tbl(i))*ABS(l_remain_amt_tbl(i)),
4379 unrounded_tax_amt = sign(l_tax_amt_tbl(i))*ABS(l_remain_unrounded_amt_tbl(i)),
4380 tax_amt_tax_curr = sign(l_tax_amt_tbl(i))*ABS(l_remain_amt_tax_curr_tbl(i)),
4381 tax_amt_funcl_curr = sign(l_tax_amt_tbl(i))*ABS(l_remain_amt_funcl_curr_tbl(i))
4382 WHERE (ABS(l_line_amt_tbl(i)) > ABS(l_remain_line_amt_tbl(i)) OR
4383 ABS(l_tax_amt_tbl(i)) > ABS(l_remain_amt_tbl(i))
4384 )
4385 AND tax_line_id = l_tax_line_id_tbl(i)
4386 AND NVL(reporting_currency_code, -99) = NVL(l_rpt_currency_code_tbl(i), -99);
4387
4388 IF (g_level_procedure >= g_current_runtime_level ) THEN
4389 FND_LOG.STRING(g_level_procedure,
4390 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.adjust_overapplication.END',
4391 'ZX_TDS_TAX_LINES_DETM_PKG: adjust_overapplication(-)');
4392 END IF;
4393
4394 EXCEPTION
4395 WHEN OTHERS THEN
4396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4397 x_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4398
4399 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4400 FND_LOG.STRING(g_level_unexpected,
4401 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.adjust_overapplication',
4402 x_error_buffer);
4403 END IF;
4404
4405 END adjust_overapplication;
4406
4407 -----------------------------------------------------------------------
4408 -- PRIVATE PROCEDURE
4409 -- process_unchanged_trx_lines
4410 --
4411 -- DESCRIPTION
4412 --
4413 -- HISTORY
4414 -- Ling Zhang Apr-20-2005 Created for Bug Fix 4313177
4415 --
4416
4417 PROCEDURE process_unchanged_trx_lines(
4418 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
4419 x_return_status OUT NOCOPY VARCHAR2,
4420 x_error_buffer OUT NOCOPY VARCHAR2)
4421 IS
4422
4423 l_detail_tax_lines_tbl ZX_TDS_CALC_SERVICES_PUB_PKG.detail_tax_lines_tbl_type;
4424 l_tax_index NUMBER;
4425
4426 -- this procedure is added for PO eBTax uptake. PO calls eBtax only in GT mode,
4427 -- so use zx_transaction_lines_gt in stead of zx_lines_det_factors to retrieve
4428 -- the tax lines for unchanged the trx lines.
4429
4430 CURSOR c_tx_ln_for_unchanged_trx_ln IS
4431 SELECT L.*
4432 FROM zx_lines L,
4433 zx_transaction_lines_gt G
4434 WHERE /* -- commented out for bug fix 5417887
4435 G.application_id = p_event_class_rec.application_id
4436 AND G.event_class_code = p_event_class_rec.event_class_code
4437 AND G.entity_code = p_event_class_rec.entity_code
4438 AND G.trx_id = p_event_class_rec.trx_id
4439 AND */
4440 G.line_level_action = 'NO_CHANGE'
4441 AND L.application_id = G.application_id
4442 AND L.event_class_code = G.event_class_code
4443 AND L.entity_code = G.entity_code
4444 AND L.trx_id = G.trx_id
4445 AND L.trx_line_id = G.trx_line_id
4446 AND L.trx_level_type = G.trx_level_type
4447 AND L.cancel_flag <> 'Y';
4448
4449 BEGIN
4450
4451 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4452
4453 IF (g_level_procedure >= g_current_runtime_level ) THEN
4454 FND_LOG.STRING(g_level_procedure,
4455 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_unchanged_trx_lines.BEGIN',
4456 'ZX_TDS_TAX_LINES_DETM_PKG: process_unchanged_trx_lines(+)');
4457 END IF;
4458
4459 l_tax_index := 1;
4460 FOR tax_rec IN c_tx_ln_for_unchanged_trx_ln LOOP
4461
4462 l_detail_tax_lines_tbl(l_tax_index) := tax_rec;
4463
4464 IF tax_rec.reporting_only_flag = 'N' THEN
4465 l_detail_tax_lines_tbl(l_tax_index).process_for_recovery_flag := 'Y';
4466 ELSE
4467 l_detail_tax_lines_tbl(l_tax_index).process_for_recovery_flag := 'N';
4468 END IF;
4469
4470 l_tax_index := l_tax_index + 1;
4471
4472 END LOOP;
4473
4474 FORALL l_tax_ln_index IN l_detail_tax_lines_tbl.FIRST ..
4475 l_detail_tax_lines_tbl.LAST
4476
4477 INSERT INTO zx_detail_tax_lines_gt
4478 VALUES l_detail_tax_lines_tbl(l_tax_ln_index);
4479
4480
4481 IF (g_level_procedure >= g_current_runtime_level ) THEN
4482 FND_LOG.STRING(g_level_procedure,
4483 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_unchanged_trx_lines.BEGIN',
4484 'ZX_TDS_TAX_LINES_DETM_PKG: process_unchanged_trx_lines(-)');
4485 END IF;
4486
4487 EXCEPTION
4488 WHEN OTHERS THEN
4489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4490 x_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4491
4492 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4493 FND_LOG.STRING(g_level_unexpected,
4494 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.process_unchanged_trx_lines',
4495 x_error_buffer);
4496 END IF;
4497
4498 END process_unchanged_trx_lines;
4499
4500 -----------------------------------------------------------------------
4501 -- PRIVATE PROCEDURE
4502 -- set_acct_source_tax_rate_id
4503 --
4504 -- DESCRIPTION
4505 --
4506 -- HISTORY
4507 -- Hongjun Liu Sept-07-2006 Created for Bug Fix 5508356
4508 --
4509 -- DESCRIPTION
4510 -- This procedure is added for bug fix 5508356 for the setting of
4511 -- account_source_tax_rate_id, which should be set based on the
4512 -- tax_rate_id of from the TAX_ACCOUNT_SOURCE_TAX, which belongs
4513 -- to the same transaction line
4514 --
4515
4516 PROCEDURE set_acct_source_tax_rate_id(
4517 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
4518 x_return_status OUT NOCOPY VARCHAR2,
4519 x_error_buffer OUT NOCOPY VARCHAR2) IS
4520
4521 l_row_count NUMBER;
4522
4523 BEGIN
4524
4525 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4526
4527 IF (g_level_procedure >= g_current_runtime_level ) THEN
4528 FND_LOG.STRING(g_level_procedure,
4529 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.set_acct_source_tax_rate_id.BEGIN',
4530 'ZX_TDS_TAX_LINES_DETM_PKG: set_acct_source_tax_rate_id(+)');
4531 END IF;
4532
4533 UPDATE zx_detail_tax_lines_gt gt1
4534 SET account_source_tax_rate_id =
4535 NVL((SELECT gt2.tax_rate_id
4536 FROM zx_taxes_b ztb,
4537 zx_detail_tax_lines_gt gt2
4538 WHERE ztb.tax_id = gt1.tax_id
4539 AND gt2.application_id = gt1.application_id
4540 AND gt2.entity_code = gt1.entity_code
4541 AND gt2.event_class_code = gt1.event_class_code
4542 AND gt2.trx_id = gt1.trx_id
4543 AND gt2.trx_line_id = gt1.trx_line_id
4544 AND gt2.trx_level_type = gt1.trx_level_type
4545 AND gt2.tax_regime_code = ztb.tax_regime_code
4546 AND gt2.tax = ztb.tax_account_source_tax
4547 AND rownum=1),account_source_tax_rate_id);
4548 -- WHERE adjusted_doc_application_id IS NULL;
4549
4550 l_row_count := SQL%ROWCOUNT;
4551
4552 IF (g_level_statement >= g_current_runtime_level ) THEN
4553 FND_LOG.STRING(g_level_statement,
4554 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.set_acct_source_tax_rate_id',
4555 ' number of rows inserted = ' || l_row_count);
4556 END IF;
4557
4558 IF (g_level_procedure >= g_current_runtime_level ) THEN
4559 FND_LOG.STRING(g_level_procedure,
4560 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.set_acct_source_tax_rate_id.END',
4561 'ZX_TDS_TAX_LINES_DETM_PKG: set_acct_source_tax_rate_id(-)');
4562 END IF;
4563
4564 EXCEPTION
4565 WHEN OTHERS THEN
4566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4567 x_error_buffer := sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80);
4568
4569 IF (g_level_unexpected >= g_current_runtime_level ) THEN
4570 FND_LOG.STRING(g_level_unexpected,
4571 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.set_acct_source_tax_rate_id',
4572 x_error_buffer);
4573 END IF;
4574
4575 END set_acct_source_tax_rate_id;
4576
4577 END ZX_TDS_TAX_LINES_DETM_PKG;