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