[Home] [Help]
PACKAGE BODY: APPS.ZX_TDS_REVERSE_DOCUMENT_PKG
Source
1 PACKAGE BODY ZX_TDS_REVERSE_DOCUMENT_PKG AS
2 /* $Header: zxdirevdocmtpkgb.pls 120.27.12010000.2 2008/11/18 05:59:25 srajapar ship $ */
3
4 TYPE tax_line_id_tp IS TABLE OF zx_lines.tax_line_id%TYPE INDEX BY BINARY_INTEGER;
5
6 FUNCTION get_tbl_index (
7 l_number_tbl IN tax_line_id_tp,
8 l_number_value IN zx_lines.tax_line_id%TYPE,
9 x_return_status OUT NOCOPY VARCHAR2 ) RETURN NUMBER;
10
11 g_current_runtime_level NUMBER;
12 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
13 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
15 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
16
17 /* ======================================================================*
18 | PROCEDURE reverse_document |
19 | This published service creates an exact mirror image of the tax lines |
20 | of the reversed event class, for the current (reversing) event calss. |
21 | It creates a Detail tax line mirroring the reversing detail tax line |
22 | (Reverse the amount related columns and copy the other details). |
23 * ======================================================================*/
24
25 PROCEDURE reverse_document (
26 p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
27 x_return_status OUT NOCOPY VARCHAR2 )
28 IS
29
30 CURSOR get_sum_tax_line_id_tbl_csr IS
31 SELECT summary_tax_line_id,
32 zx_lines_summary_s.NEXTVAL,
33 reversing_appln_id,
34 reversing_entity_code,
35 reversing_evnt_cls_code,
36 reversing_trx_id,
37 trx_number
38 FROM
39 (SELECT /*+ ORDERED INDEX(hdrgt ZX_REV_TRX_HEADERS_GT_U1)
40 INDEX(lngt ZX_REVERSE_TRX_LINES_GT_U1) */
41 DISTINCT
42 summ.summary_tax_line_id,
43 lngt.reversing_appln_id,
44 lngt.reversing_entity_code,
45 lngt.reversing_evnt_cls_code,
46 lngt.reversing_trx_id,
47 hdrgt.trx_number
48 FROM zx_rev_trx_headers_gt hdrgt,
49 zx_reverse_trx_lines_gt lngt,
50 zx_lines_summary summ
51 WHERE hdrgt.reversing_appln_id = p_event_class_rec.application_id
52 AND hdrgt.reversing_entity_code = p_event_class_rec.entity_code
53 AND hdrgt.reversing_evnt_cls_code = p_event_class_rec.event_class_code
54 AND hdrgt.reversing_trx_id = p_event_class_rec.trx_id
55 AND lngt.reversing_trx_id = hdrgt.reversing_trx_id
56 AND lngt.reversing_appln_id = hdrgt.reversing_appln_id
57 AND lngt.reversing_entity_code = hdrgt.reversing_entity_code
58 AND lngt.reversing_evnt_cls_code = hdrgt.reversing_evnt_cls_code
59 AND summ.trx_id = lngt.reversed_trx_id
60 AND summ.application_id = lngt.reversed_appln_id
61 AND summ.entity_code = lngt.reversed_entity_code
62 AND summ.event_class_code = lngt.reversed_evnt_cls_code
63 AND EXISTS
64 (SELECT /*+ no_unnest */ 1
65 FROM ZX_LINES line
66 WHERE line.summary_tax_line_id = summ.summary_tax_line_id
67 AND line.trx_id = summ.trx_id
68 AND line.application_id = summ.application_id
69 AND line.entity_code = summ.entity_code
70 AND line.event_class_code = summ.event_class_code
71 )
72 );
73
74 CURSOR get_rev_tax_lines_info_csr IS
75 SELECT /*+ ORDERED INDEX(hdrgt ZX_REV_TRX_HEADERS_GT_U1)
76 INDEX(lngt ZX_REVERSE_TRX_LINES_GT_U1) */
77 zl.tax_line_id, -- from zx_lines
78 zx_lines_s.NEXTVAL, -- from Sequence
79 zl.offset_link_to_tax_line_id, -- from zx_lines
80 zl.summary_tax_line_id, -- from zx_lines
81 lngt.reversing_appln_id, -- from line gt
82 lngt.reversing_entity_code, -- from line gt
83 lngt.reversing_evnt_cls_code, -- from line gt
84 lngt.reversing_trx_id, -- from line gt
85 lngt.reversing_trx_line_id, -- from line gt
86 lngt.reversing_trx_level_type, -- from line gt
87 hdrgt.trx_number -- from header gt
88 FROM zx_rev_trx_headers_gt hdrgt,
89 zx_reverse_trx_lines_gt lngt,
90 zx_lines zl
91 WHERE hdrgt.reversing_appln_id = p_event_class_rec.application_id
92 AND hdrgt.reversing_entity_code = p_event_class_rec.entity_code
93 AND hdrgt.reversing_evnt_cls_code = p_event_class_rec.event_class_code
94 AND hdrgt.reversing_trx_id = p_event_class_rec.trx_id
95 AND lngt.reversing_trx_id = hdrgt.reversing_trx_id
96 AND lngt.reversing_appln_id = hdrgt.reversing_appln_id
97 AND lngt.reversing_entity_code = hdrgt.reversing_entity_code
98 AND lngt.reversing_evnt_cls_code = hdrgt.reversing_evnt_cls_code
99 AND zl.trx_id = lngt.reversed_trx_id
100 AND zl.application_id = lngt.reversed_appln_id
101 AND zl.entity_code = lngt.reversed_entity_code
102 AND zl.event_class_code = lngt.reversed_evnt_cls_code
103 AND zl.trx_line_id = lngt.reversed_trx_line_id
104 AND zl.trx_level_type = lngt.reversed_trx_level_type;
105
106
107 TYPE reversing_appln_id_tp IS TABLE OF zx_reverse_trx_lines_gt.reversing_appln_id%TYPE INDEX BY BINARY_INTEGER;
108 TYPE reversing_entity_code_tp IS TABLE OF zx_reverse_trx_lines_gt.reversing_entity_code%TYPE INDEX BY BINARY_INTEGER;
109 TYPE reversing_evnt_cls_code_tp IS TABLE OF zx_reverse_trx_lines_gt.reversing_evnt_cls_code%TYPE INDEX BY BINARY_INTEGER;
110 TYPE reversing_trx_id_tp IS TABLE OF zx_reverse_trx_lines_gt.reversing_trx_id%TYPE INDEX BY BINARY_INTEGER;
111 TYPE reversing_trx_line_id_tp IS TABLE OF zx_reverse_trx_lines_gt.reversing_trx_line_id%TYPE INDEX BY BINARY_INTEGER;
112 TYPE reversing_trx_level_type_tp IS TABLE OF zx_reverse_trx_lines_gt.reversing_trx_level_type%TYPE INDEX BY BINARY_INTEGER;
113 TYPE trx_number_tp IS TABLE OF zx_rev_trx_headers_gt.trx_number%TYPE INDEX BY BINARY_INTEGER;
114
115 l_reversed_tax_line_id_tb tax_line_id_tp;
116 l_reversing_tax_line_id_tb tax_line_id_tp;
117 l_offset_link_to_tx_line_id_tb tax_line_id_tp;
118 l_summary_tax_line_id_tb tax_line_id_tp;
119
120 l_reversing_appln_id_tb reversing_appln_id_tp;
121 l_reversing_entity_code_tb reversing_entity_code_tp;
122 l_reversing_evnt_cls_code_tb reversing_evnt_cls_code_tp;
123 l_reversing_trx_id_tb reversing_trx_id_tp;
124 l_reversing_trx_line_id_tb reversing_trx_line_id_tp;
125 l_reversing_trx_level_type_tb reversing_trx_level_type_tp;
126
127 l_trx_number_tb trx_number_tp;
128
129 l_index NUMBER;
130
131 -- for reversing summary tax lines
132 --
133 l_reversed_sum_tax_line_id_tb tax_line_id_tp;
134 l_reversing_sum_tax_line_id_tb tax_line_id_tp;
135
136 BEGIN
137 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
138
139 IF (g_level_procedure >= g_current_runtime_level ) THEN
140 FND_LOG.STRING(g_level_procedure,
141 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.reverse_document.BEGIN',
142 'ZX_TDS_REVERSE_DOCUMENT_PKG.reverse_document(+)');
143 END IF;
144
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146
147 -- reverse summary tax lines
148 -- 1. get all summary_tax_line_ids that need to be reversed,
149 -- meanwhile, create summary_tax_line_ids for the reversed
150 -- summary tax lines with sequence zx_lines_summary_s
151 -- 2. reverse summary tax lines in zx_lines_summary
152 --
153
154 IF p_event_class_rec.summarization_flag = 'Y' THEN
155
156 OPEN get_sum_tax_line_id_tbl_csr;
157
158 FETCH get_sum_tax_line_id_tbl_csr BULK COLLECT INTO
159 l_reversed_sum_tax_line_id_tb,
160 l_reversing_sum_tax_line_id_tb,
161 l_reversing_appln_id_tb,
162 l_reversing_entity_code_tb,
163 l_reversing_evnt_cls_code_tb,
164 l_reversing_trx_id_tb,
165 l_trx_number_tb;
166
167 CLOSE get_sum_tax_line_id_tbl_csr;
168
169 -- create reversed summary tax lines in zx_lines_summary
170 --
171 FORALL i IN NVL(l_reversed_sum_tax_line_id_tb.FIRST, 0) ..
172 NVL(l_reversed_sum_tax_line_id_tb.LAST, -1)
173 INSERT INTO zx_lines_summary
174 ( summary_tax_line_id,
175 internal_organization_id,
176 application_id,
177 entity_code,
178 event_class_code,
179 trx_id,
180 trx_number,
181 applied_from_application_id,
182 applied_from_event_class_code,
183 applied_from_entity_code,
184 applied_from_trx_id,
185 adjusted_doc_application_id,
186 adjusted_doc_entity_code,
187 adjusted_doc_event_class_code,
188 adjusted_doc_trx_id,
189 summary_tax_line_number,
190 tax_regime_code,
191 tax,
192 tax_status_code,
193 tax_rate_id,
194 tax_rate_code,
195 tax_rate,
196 tax_amt,
197 tax_amt_tax_curr,
198 tax_amt_funcl_curr,
199 tax_jurisdiction_code,
200 total_rec_tax_amt,
201 total_rec_tax_amt_funcl_curr,
202 total_rec_tax_amt_tax_curr,
203 total_nrec_tax_amt,
204 total_nrec_tax_amt_funcl_curr,
205 total_nrec_tax_amt_tax_curr,
206 ledger_id,
207 legal_entity_id,
208 establishment_id,
209 currency_conversion_date,
210 currency_conversion_type,
211 currency_conversion_rate,
212 summarization_template_id,
213 taxable_basis_formula,
214 tax_calculation_formula,
215 Historical_Flag,
216 Cancel_Flag,
217 Delete_Flag,
218 Tax_Amt_Included_Flag,
219 Compounding_Tax_Flag,
220 Self_Assessed_Flag,
221 Overridden_Flag,
222 Reporting_Only_Flag,
223 Associated_Child_Frozen_Flag,
224 Copied_From_Other_Doc_Flag,
225 Manually_Entered_Flag,
226 last_manual_entry,
227 Record_Type_Code,
228 tax_provider_id,
229 Tax_Only_Line_Flag,
230 created_by,
231 creation_date,
232 last_updated_by,
233 last_update_date,
234 last_update_login,
235 attribute_category,
236 attribute1,
237 attribute2,
238 attribute3,
239 attribute4,
240 attribute5,
241 attribute6,
242 attribute7,
243 attribute8,
244 attribute9,
245 attribute10,
246 attribute11,
247 attribute12,
248 attribute13,
249 attribute14,
250 attribute15,
251 applied_from_line_id,
252 applied_to_application_id,
253 applied_to_event_class_code,
254 applied_to_entity_code,
255 applied_to_trx_id,
256 applied_to_line_id,
257 tax_exemption_id,
258 tax_rate_before_exemption,
259 tax_rate_name_before_exemption,
260 exempt_rate_modifier,
261 exempt_certificate_number,
262 exempt_reason,
263 exempt_reason_code,
264 tax_rate_before_exception,
265 tax_rate_name_before_exception,
266 tax_exception_id,
267 exception_rate,
268 mrc_tax_line_flag,
269 content_owner_id,
270 object_version_number
271 )
272 SELECT
273 l_reversing_sum_tax_line_id_tb(i), -- new summary_tax_line_id
274 internal_organization_id,
275 l_reversing_appln_id_tb(i), -- from rev line gt for application_id
276 l_reversing_entity_code_tb(i), -- from rev line gt for entity_code
277 l_reversing_evnt_cls_code_tb(i), -- from rev line gt for event_class_code
278 l_reversing_trx_id_tb(i), -- from rev line gt for trx_id
279 l_trx_number_tb(i), -- from rev header gt for trx_number,
280 applied_from_application_id,
281 applied_from_event_class_code,
282 applied_from_entity_code,
283 applied_from_trx_id,
284 adjusted_doc_application_id,
285 adjusted_doc_entity_code,
286 adjusted_doc_event_class_code,
287 adjusted_doc_trx_id,
288 summary_tax_line_number,
289 tax_regime_code,
290 tax,
291 tax_status_code,
292 tax_rate_id,
293 tax_rate_code,
294 tax_rate,
295 -tax_amt, -- reversed the amount
296 -tax_amt_tax_curr, -- reversed the amount
297 -tax_amt_funcl_curr, -- reversed the amount
298 tax_jurisdiction_code,
299 -total_rec_tax_amt, -- reversed the amount
300 -total_rec_tax_amt_funcl_curr, -- reversed the amount
301 -total_rec_tax_amt_tax_curr, -- reversed the amount
302 -total_nrec_tax_amt, -- reversed the amount
303 -total_nrec_tax_amt_funcl_curr, -- reversed the amount
304 -total_nrec_tax_amt_tax_curr, -- reversed the amount
305 ledger_id,
306 legal_entity_id,
307 establishment_id,
308 currency_conversion_date,
309 currency_conversion_type,
310 currency_conversion_rate,
311 summarization_template_id,
312 taxable_basis_formula,
313 tax_calculation_formula,
314 Historical_Flag,
315 Cancel_Flag,
316 Delete_Flag,
317 Tax_Amt_Included_Flag,
318 Compounding_Tax_Flag,
319 Self_Assessed_Flag,
320 Overridden_Flag,
321 Reporting_Only_Flag,
322 Associated_Child_Frozen_Flag,
323 Copied_From_Other_Doc_Flag,
324 Manually_Entered_Flag,
325 last_manual_entry,
326 Record_Type_Code,
327 tax_provider_id,
328 Tax_Only_Line_Flag,
329 fnd_global.user_id, -- created_by,
330 sysdate, -- creation_date,
331 fnd_global.user_id, -- last_updated_by,
332 sysdate, -- last_update_date,
333 fnd_global.login_id, -- last_update_login,
334 attribute_category,
335 attribute1,
336 attribute2,
337 attribute3,
338 attribute4,
339 attribute5,
340 attribute6,
341 attribute7,
342 attribute8,
343 attribute9,
344 attribute10,
345 attribute11,
346 attribute12,
347 attribute13,
348 attribute14,
349 attribute15,
350 applied_from_line_id,
351 applied_to_application_id,
352 applied_to_event_class_code,
353 applied_to_entity_code,
354 applied_to_trx_id,
355 applied_to_line_id,
356 tax_exemption_id,
357 tax_rate_before_exemption,
358 tax_rate_name_before_exemption,
359 exempt_rate_modifier,
360 exempt_certificate_number,
361 exempt_reason,
362 exempt_reason_code,
363 tax_rate_before_exception,
364 tax_rate_name_before_exception,
365 tax_exception_id,
366 exception_rate,
367 mrc_tax_line_flag,
368 content_owner_id,
369 1
370 FROM zx_lines_summary
371 WHERE summary_tax_line_id = l_reversed_sum_tax_line_id_tb(i);
372
373 -- initialize the following data structures because they will be reused in
374 -- reversing detail tax lines
375 --
376 l_reversing_trx_id_tb.DELETE;
377 l_reversing_appln_id_tb.DELETE;
378 l_reversing_entity_code_tb.DELETE;
379 l_reversing_evnt_cls_code_tb.DELETE;
380 l_reversing_trx_level_type_tb.DELETE;
381 l_trx_number_tb.DELETE;
382
383 END IF; -- of summarization_flag = 'Y'
384
385 -- For detail tax lines in reversed document:
386 -- 1. tax_line_id has new value form sequence zx_lines_s, stored in
387 -- l_reversed_tax_line_id_tb.
388 -- 2. All the amount related columns have values reversed from
389 -- the reversing document.
390 -- 3. Some columns(16) have new values from zx_rev_trx_headers_gt.
391 -- 4. Some columns(11) have new values from zx_reverse_trx_lines_gt.
392 -- 5. WHO columns(5) have new values with current user and sysdate.
393 -- 6. if a tax line has a offset_link_to_tax_line_id, the new value of this
394 -- column will point to the new reversed detail tax line.
395 -- 7. if a tax line has a summary_tax_line_id, the new value of this
396 -- column will point to the new reversed summary tax line.
397 -- 8. All the other columns have values copied from the reversing document.
398 --
399 OPEN get_rev_tax_lines_info_csr;
400
401 FETCH get_rev_tax_lines_info_csr BULK COLLECT INTO
402 l_reversed_tax_line_id_tb,
403 l_reversing_tax_line_id_tb,
404 l_offset_link_to_tx_line_id_tb,
405 l_summary_tax_line_id_tb,
406 l_reversing_appln_id_tb,
407 l_reversing_entity_code_tb,
408 l_reversing_evnt_cls_code_tb,
409 l_reversing_trx_id_tb,
410 l_reversing_trx_line_id_tb,
411 l_reversing_trx_level_type_tb,
412 l_trx_number_tb;
413
414 CLOSE get_rev_tax_lines_info_csr;
415
416 -- Update offset_link_to_tax_line_id of a tax line if it is not NULL:
417 -- 1. get l_index of tax_line_id from l_reversing_tax_line_id_tb with
418 -- l_offset_link_to_tx_line_id_tb(i).
419 -- 2. get the reversed tax line id from l_reversed_tax_line_id_tb with this
420 -- l_index
421 -- 3. update l_offset_link_to_tx_line_id_tb(i) in the reversed tax line with
422 -- l_reversed_tax_line_id_tb(l_index).
423 --
424 FOR i IN NVL(l_offset_link_to_tx_line_id_tb.FIRST, 0) ..
425 NVL(l_offset_link_to_tx_line_id_tb.LAST, -1)
426 LOOP
427 IF l_offset_link_to_tx_line_id_tb(i) IS NOT NULL THEN
428 l_index := get_tbl_index(
429 l_reversed_tax_line_id_tb,
430 l_offset_link_to_tx_line_id_tb(i),
431 x_return_status );
432 IF l_index IS NOT NULL THEN
433 l_offset_link_to_tx_line_id_tb(i) := l_reversing_tax_line_id_tb(l_index);
434 END IF;
435 END IF;
436 END LOOP;
437
438 -- Update summary_tax_line_id a tax line if it is not NULL:
439 -- 1. get l_index of summary_tax_line_id from l_reversing_sum_tax_line_id_tb
440 -- with l_summary_tax_line_id_tb(i).
441 -- 2. get the reversed summary tax line id from l_reversed_sum_tax_line_id_tb
442 -- with this l_index.
443 -- 3. update l_summary_tax_line_id_tb(i) in the reversed tax line with
444 -- l_reversed_sum_tax_line_id_tb(l_index).
445 --
446 IF p_event_class_rec.summarization_flag = 'Y' THEN
447 FOR i IN NVL(l_summary_tax_line_id_tb.FIRST, 0) ..
448 NVL(l_summary_tax_line_id_tb.LAST, -1)
449 LOOP
450 IF l_summary_tax_line_id_tb(i) IS NOT NULL THEN
451 l_index := get_tbl_index(
452 l_reversed_sum_tax_line_id_tb,
453 l_summary_tax_line_id_tb(i),
454 x_return_status );
455 IF l_index IS NOT NULL THEN
456 l_summary_tax_line_id_tb(i) := l_reversing_sum_tax_line_id_tb(l_index);
457 END IF;
458 END IF;
459 END LOOP;
460 END IF;
461
462 -- create reversed detail tax lines in zx_lines
463 --
464 FORALL i IN NVL(l_reversing_tax_line_id_tb.FIRST, 0) ..
465 NVL(l_reversing_tax_line_id_tb.LAST, -1)
466 INSERT INTO zx_lines (
467 tax_line_id,
468 internal_organization_id,
469 application_id,
470 entity_code,
471 event_class_code,
472 event_type_code,
473 trx_id,
474 trx_line_id,
475 trx_level_type,
476 trx_line_number,
477 doc_event_status,
478 tax_event_class_code,
479 tax_event_type_code,
480 tax_line_number,
481 content_owner_id,
482 tax_regime_id,
483 tax_regime_code,
484 tax_id,
485 tax,
486 tax_status_id,
487 tax_status_code,
488 tax_rate_id,
489 tax_rate_code,
490 tax_rate,
491 tax_apportionment_line_number,
492 trx_id_level2,
493 trx_id_level3,
494 trx_id_level4,
495 trx_id_level5,
496 trx_id_level6,
497 trx_user_key_level1,
498 trx_user_key_level2,
499 trx_user_key_level3,
500 trx_user_key_level4,
501 trx_user_key_level5,
502 trx_user_key_level6,
503 mrc_tax_line_flag,
504 ledger_id,
505 establishment_id,
506 legal_entity_id,
507 legal_entity_tax_reg_number,
508 hq_estb_reg_number,
509 hq_estb_party_tax_prof_id,
510 currency_conversion_date,
511 currency_conversion_type,
512 currency_conversion_rate,
513 tax_currency_conversion_date,
514 tax_currency_conversion_type,
515 tax_currency_conversion_rate,
516 trx_currency_code,
517 minimum_accountable_unit,
518 precision,
519 trx_number,
520 trx_date,
521 unit_price,
522 line_amt,
523 trx_line_quantity,
524 tax_base_modifier_rate,
525 ref_doc_application_id,
526 ref_doc_entity_code,
527 ref_doc_event_class_code,
528 ref_doc_trx_id,
529 ref_doc_line_id,
530 ref_doc_line_quantity,
531 other_doc_line_amt,
532 other_doc_line_tax_amt,
533 other_doc_line_taxable_amt,
534 unrounded_taxable_amt,
535 unrounded_tax_amt,
536 related_doc_application_id,
537 related_doc_entity_code,
538 related_doc_event_class_code,
539 related_doc_trx_id,
540 related_doc_number,
541 related_doc_date,
542 applied_from_application_id,
543 applied_from_event_class_code,
544 applied_from_entity_code,
545 applied_from_trx_id,
546 applied_from_line_id,
547 applied_from_trx_number,
548 adjusted_doc_application_id,
549 adjusted_doc_entity_code,
550 adjusted_doc_event_class_code,
551 adjusted_doc_trx_id,
552 adjusted_doc_line_id,
553 adjusted_doc_number,
554 adjusted_doc_date,
555 applied_to_application_id,
556 applied_to_event_class_code,
557 applied_to_entity_code,
558 applied_to_trx_id,
559 applied_to_line_id,
560 applied_to_trx_number,
561 summary_tax_line_id,
562 offset_link_to_tax_line_id,
563 offset_flag,
564 process_for_recovery_flag,
565 tax_jurisdiction_id,
566 tax_jurisdiction_code,
567 place_of_supply,
568 place_of_supply_type_code,
569 place_of_supply_result_id,
570 tax_date_rule_id,
571 tax_date,
572 tax_determine_date,
573 tax_point_date,
574 trx_line_date,
575 tax_type_code,
576 tax_code,
577 tax_registration_id,
578 tax_registration_number,
579 registration_party_type,
580 rounding_level_code,
581 rounding_rule_code,
582 rounding_lvl_party_tax_prof_id,
583 rounding_lvl_party_type,
584 compounding_tax_flag,
585 orig_tax_status_id,
586 orig_tax_status_code,
587 orig_tax_rate_id,
588 orig_tax_rate_code,
589 orig_tax_rate,
590 orig_tax_jurisdiction_id,
591 orig_tax_jurisdiction_code,
592 orig_tax_amt_included_flag,
593 orig_self_assessed_flag,
594 tax_currency_code,
595 tax_amt,
596 tax_amt_tax_curr,
597 tax_amt_funcl_curr,
598 taxable_amt,
599 taxable_amt_tax_curr,
600 taxable_amt_funcl_curr,
601 orig_taxable_amt,
602 orig_taxable_amt_tax_curr,
603 cal_tax_amt,
604 cal_tax_amt_tax_curr,
605 cal_tax_amt_funcl_curr,
606 orig_tax_amt,
607 orig_tax_amt_tax_curr,
608 rec_tax_amt,
609 rec_tax_amt_tax_curr,
610 rec_tax_amt_funcl_curr,
611 nrec_tax_amt,
612 nrec_tax_amt_tax_curr,
613 nrec_tax_amt_funcl_curr,
614 tax_exemption_id,
615 tax_rate_before_exemption,
616 tax_rate_name_before_exemption,
617 exempt_rate_modifier,
618 exempt_certificate_number,
619 exempt_reason,
620 exempt_reason_code,
621 tax_exception_id,
622 tax_rate_before_exception,
623 tax_rate_name_before_exception,
624 exception_rate,
625 tax_apportionment_flag,
626 historical_flag,
627 taxable_basis_formula,
628 tax_calculation_formula,
629 cancel_flag,
630 purge_flag,
631 delete_flag,
632 tax_amt_included_flag,
633 self_assessed_flag,
634 overridden_flag,
635 manually_entered_flag,
636 freeze_until_overridden_flag,
637 copied_from_other_doc_flag,
638 recalc_required_flag,
639 settlement_flag,
640 item_dist_changed_flag,
641 associated_child_frozen_flag,
642 tax_only_line_flag,
643 compounding_dep_tax_flag,
644 last_manual_entry,
645 tax_provider_id,
646 record_type_code,
647 reporting_period_id,
648 legal_message_appl_2,
649 legal_message_status,
650 legal_message_rate,
651 legal_message_basis,
652 legal_message_calc,
653 legal_message_threshold,
654 legal_message_pos,
655 legal_message_trn,
656 legal_message_exmpt,
657 legal_message_excpt,
658 tax_regime_template_id,
659 tax_applicability_result_id,
660 direct_rate_result_id,
661 status_result_id,
662 rate_result_id,
663 basis_result_id,
664 thresh_result_id,
665 calc_result_id,
666 tax_reg_num_det_result_id,
667 eval_exmpt_result_id,
668 eval_excpt_result_id,
669 enforce_from_natural_acct_flag,
670 tax_hold_code,
671 tax_hold_released_code,
672 prd_total_tax_amt,
673 prd_total_tax_amt_tax_curr,
674 prd_total_tax_amt_funcl_curr,
675 internal_org_location_id,
676 attribute_category,
677 attribute1,
678 attribute2,
679 attribute3,
680 attribute4,
681 attribute5,
682 attribute6,
683 attribute7,
684 attribute8,
685 attribute9,
686 attribute10,
687 attribute11,
688 attribute12,
689 attribute13,
690 attribute14,
691 attribute15,
692 global_attribute_category,
693 global_attribute1,
694 global_attribute2,
695 global_attribute3,
696 global_attribute4,
697 global_attribute5,
698 global_attribute6,
699 global_attribute7,
700 global_attribute8,
701 global_attribute9,
702 global_attribute10,
703 global_attribute11,
704 global_attribute12,
705 global_attribute13,
706 global_attribute14,
707 global_attribute15,
708 numeric1,
709 numeric2,
710 numeric3,
711 numeric4,
712 numeric5,
713 numeric6,
714 numeric7,
715 numeric8,
716 numeric9,
717 numeric10,
718 char1,
719 char2,
720 char3,
721 char4,
722 char5,
723 char6,
724 char7,
725 char8,
726 char9,
727 char10,
728 date1,
729 date2,
730 date3,
731 date4,
732 date5,
733 date6,
734 date7,
735 date8,
736 date9,
737 date10,
738 created_by,
739 creation_date,
740 last_updated_by,
741 last_update_date,
742 last_update_login,
743 line_assessable_value,
744 legal_justification_text1,
745 legal_justification_text2,
746 legal_justification_text3,
747 reporting_currency_code,
748 trx_line_index,
749 offset_tax_rate_code,
750 proration_code,
751 other_doc_source,
752 reporting_only_flag,
753 ctrl_total_line_tx_amt,
754 tax_rate_type,
755 interface_entity_code,
756 interface_tax_line_id,
757 taxing_juris_geography_id,
758 adjusted_doc_tax_line_id,
759 object_version_number,
760 reversed_tax_line_id,
761 account_source_tax_rate_id
762 )
763 SELECT
764 l_reversing_tax_line_id_tb(i), -- tax_line_id,
765 zl.internal_organization_id,
766 l_reversing_appln_id_tb(i), -- from line gt for application_id
767 l_reversing_entity_code_tb(i), -- from line gt for entity_code
768 l_reversing_evnt_cls_code_tb(i), -- from line gt for event_class_code
769 zl.event_type_code,
770 l_reversing_trx_id_tb(i), -- from line gt for trx_id,
771 l_reversing_trx_line_id_tb(i), -- from line gt for trx_line_id,
772 l_reversing_trx_level_type_tb(i), -- from line gt for trx_level_type,
773 zl.trx_line_number,
774 zl.doc_event_status,
775 zl.tax_event_class_code,
776 zl.tax_event_type_code,
777 zl.tax_line_number,
778 zl.content_owner_id,
779 zl.tax_regime_id,
780 zl.tax_regime_code,
781 zl.tax_id,
782 zl.tax,
783 zl.tax_status_id,
784 zl.tax_status_code,
785 zl.tax_rate_id,
786 zl.tax_rate_code,
787 zl.tax_rate,
788 zl.tax_apportionment_line_number,
789 zl.trx_id_level2,
790 zl.trx_id_level3,
791 zl.trx_id_level4,
792 zl.trx_id_level5,
793 zl.trx_id_level6,
794 zl.trx_user_key_level1,
795 zl.trx_user_key_level2,
796 zl.trx_user_key_level3,
797 zl.trx_user_key_level4,
798 zl.trx_user_key_level5,
799 zl.trx_user_key_level6,
800 zl.mrc_tax_line_flag,
801 zl.ledger_id,
802 zl.establishment_id,
803 zl.legal_entity_id,
804 zl.legal_entity_tax_reg_number,
805 zl.hq_estb_reg_number,
806 zl.hq_estb_party_tax_prof_id,
807 zl.currency_conversion_date,
808 zl.currency_conversion_type,
809 zl.currency_conversion_rate,
810 zl.tax_currency_conversion_date,
811 zl.tax_currency_conversion_type,
812 zl.tax_currency_conversion_rate,
813 zl.trx_currency_code,
814 zl.minimum_accountable_unit,
815 zl.precision,
816 l_trx_number_tb(i), -- from header gt for trx_number,
817 zl.trx_date,
818 zl.unit_price,
819 -zl.line_amt, -- reversed the amount
820 zl.trx_line_quantity,
821 zl.tax_base_modifier_rate,
822 zl.ref_doc_application_id,
823 zl.ref_doc_entity_code,
824 zl.ref_doc_event_class_code,
825 zl.ref_doc_trx_id,
826 zl.ref_doc_line_id,
827 zl.ref_doc_line_quantity,
828 -zl.other_doc_line_amt, -- reverse the amount
829 -zl.other_doc_line_tax_amt, -- reverse the amount
830 -zl.other_doc_line_taxable_amt, -- reverse the amount
831 -zl.unrounded_taxable_amt, -- reverse the amount
832 -zl.unrounded_tax_amt, -- reverse the amount
833 zl.related_doc_application_id,
834 zl.related_doc_entity_code,
835 zl.related_doc_event_class_code,
836 zl.related_doc_trx_id,
837 zl.related_doc_number,
838 zl.related_doc_date,
839 zl.applied_from_application_id,
840 zl.applied_from_event_class_code,
841 zl.applied_from_entity_code,
842 zl.applied_from_trx_id,
843 zl.applied_from_line_id,
844 zl.applied_from_trx_number,
845 zl.adjusted_doc_application_id,
846 zl.adjusted_doc_entity_code,
847 zl.adjusted_doc_event_class_code,
848 zl.adjusted_doc_trx_id,
849 zl.adjusted_doc_line_id,
850 zl.adjusted_doc_number,
851 zl.adjusted_doc_date,
852 zl.applied_to_application_id,
853 zl.applied_to_event_class_code,
854 zl.applied_to_entity_code,
855 zl.applied_to_trx_id,
856 zl.applied_to_line_id,
857 zl.applied_to_trx_number,
858 l_summary_tax_line_id_tb(i), -- zl.summary_tax_line_id,
859 l_offset_link_to_tx_line_id_tb(i), -- zl.offset_link_to_tax_line_id,
860 zl.offset_flag,
861 zl.process_for_recovery_flag,
862 zl.tax_jurisdiction_id,
863 zl.tax_jurisdiction_code,
864 zl.place_of_supply,
865 zl.place_of_supply_type_code,
866 zl.place_of_supply_result_id,
867 zl.tax_date_rule_id,
868 zl.tax_date,
869 zl.tax_determine_date,
870 zl.tax_point_date,
871 zl.trx_line_date,
872 zl.tax_type_code,
873 zl.tax_code,
874 zl.tax_registration_id,
875 zl.tax_registration_number,
876 zl.registration_party_type,
877 zl.rounding_level_code,
878 zl.rounding_rule_code,
879 zl.rounding_lvl_party_tax_prof_id,
880 zl.rounding_lvl_party_type,
881 zl.compounding_tax_flag,
882 zl.orig_tax_status_id,
883 zl.orig_tax_status_code,
884 zl.orig_tax_rate_id,
885 zl.orig_tax_rate_code,
886 zl.orig_tax_rate,
887 zl.orig_tax_jurisdiction_id,
888 zl.orig_tax_jurisdiction_code,
889 zl.orig_tax_amt_included_flag,
890 zl.orig_self_assessed_flag,
891 zl.tax_currency_code,
892 -zl.tax_amt, -- reverse the amount
893 -zl.tax_amt_tax_curr, -- reverse the amount
894 -zl.tax_amt_funcl_curr, -- reverse the amount
895 -zl.taxable_amt, -- reverse the amount
896 -zl.taxable_amt_tax_curr, -- reverse the amount
897 -zl.taxable_amt_funcl_curr, -- reverse the amount
898 -zl.orig_taxable_amt, -- reverse the amount
899 -zl.orig_taxable_amt_tax_curr, -- reverse the amount
900 -zl.cal_tax_amt, -- reverse the amount
901 -zl.cal_tax_amt_tax_curr, -- reverse the amount
902 -zl.cal_tax_amt_funcl_curr, -- reverse the amount
903 -zl.orig_tax_amt, -- reverse the amount
904 -zl.orig_tax_amt_tax_curr, -- reverse the amount
905 -zl.rec_tax_amt, -- reverse the amount
906 -zl.rec_tax_amt_tax_curr, -- reverse the amount
907 -zl.rec_tax_amt_funcl_curr, -- reverse the amount
908 -zl.nrec_tax_amt, -- reverse the amount
909 -zl.nrec_tax_amt_tax_curr, -- reverse the amount
910 -zl.nrec_tax_amt_funcl_curr, -- reverse the amount
911 zl.tax_exemption_id,
912 zl.tax_rate_before_exemption,
913 zl.tax_rate_name_before_exemption,
914 zl.exempt_rate_modifier,
915 zl.exempt_certificate_number,
916 zl.exempt_reason,
917 zl.exempt_reason_code,
918 zl.tax_exception_id,
919 zl.tax_rate_before_exception,
920 zl.tax_rate_name_before_exception,
921 zl.exception_rate,
922 zl.tax_apportionment_flag,
923 zl.historical_flag,
924 zl.taxable_basis_formula,
925 zl.tax_calculation_formula,
926 zl.cancel_flag,
927 zl.purge_flag,
928 zl.delete_flag,
929 zl.tax_amt_included_flag,
930 zl.self_assessed_flag,
931 zl.overridden_flag,
932 zl.manually_entered_flag,
933 zl.freeze_until_overridden_flag,
934 zl.copied_from_other_doc_flag,
935 zl.recalc_required_flag,
936 zl.settlement_flag,
937 zl.item_dist_changed_flag,
938 zl.associated_child_frozen_flag,
939 zl.tax_only_line_flag,
940 zl.compounding_dep_tax_flag,
941 zl.last_manual_entry,
942 zl.tax_provider_id,
943 zl.record_type_code,
944 zl.reporting_period_id,
945 zl.legal_message_appl_2,
946 zl.legal_message_status,
947 zl.legal_message_rate,
948 zl.legal_message_basis,
949 zl.legal_message_calc,
950 zl.legal_message_threshold,
951 zl.legal_message_pos,
952 zl.legal_message_trn,
953 zl.legal_message_exmpt,
954 zl.legal_message_excpt,
955 zl.tax_regime_template_id,
956 zl.tax_applicability_result_id,
957 zl.direct_rate_result_id,
958 zl.status_result_id,
959 zl.rate_result_id,
960 zl.basis_result_id,
961 zl.thresh_result_id,
962 zl.calc_result_id,
963 zl.tax_reg_num_det_result_id,
964 zl.eval_exmpt_result_id,
965 zl.eval_excpt_result_id,
966 zl.enforce_from_natural_acct_flag,
967 zl.tax_hold_code,
968 zl.tax_hold_released_code,
969 -zl.prd_total_tax_amt, -- reverse the amount
970 -zl.prd_total_tax_amt_tax_curr, -- reverse the amount
971 -zl.prd_total_tax_amt_funcl_curr, -- reverse the amount
972 zl.internal_org_location_id,
973 zl.attribute_category,
974 zl.attribute1,
975 zl.attribute2,
976 zl.attribute3,
977 zl.attribute4,
978 zl.attribute5,
979 zl.attribute6,
980 zl.attribute7,
981 zl.attribute8,
982 zl.attribute9,
983 zl.attribute10,
984 zl.attribute11,
985 zl.attribute12,
986 zl.attribute13,
987 zl.attribute14,
988 zl.attribute15,
989 zl.global_attribute_category,
990 zl.global_attribute1,
991 zl.global_attribute2,
992 zl.global_attribute3,
993 zl.global_attribute4,
994 zl.global_attribute5,
995 zl.global_attribute6,
996 zl.global_attribute7,
997 zl.global_attribute8,
998 zl.global_attribute9,
999 zl.global_attribute10,
1000 zl.global_attribute11,
1001 zl.global_attribute12,
1002 zl.global_attribute13,
1003 zl.global_attribute14,
1004 zl.global_attribute15,
1005 zl.numeric1,
1006 zl.numeric2,
1007 zl.numeric3,
1008 zl.numeric4,
1009 zl.numeric5,
1010 zl.numeric6,
1011 zl.numeric7,
1012 zl.numeric8,
1013 zl.numeric9,
1014 zl.numeric10,
1015 zl.char1,
1016 zl.char2,
1017 zl.char3,
1018 zl.char4,
1019 zl.char5,
1020 zl.char6,
1021 zl.char7,
1022 zl.char8,
1023 zl.char9,
1024 zl.char10,
1025 zl.date1,
1026 zl.date2,
1027 zl.date3,
1028 zl.date4,
1029 zl.date5,
1030 zl.date6,
1031 zl.date7,
1032 zl.date8,
1033 zl.date9,
1034 zl.date10,
1035 fnd_global.user_id, -- created_by
1036 sysdate, -- creation_date
1037 fnd_global.user_id, -- last_updated_by
1038 sysdate, -- last_update_date
1039 fnd_global.login_id, -- last_update_login
1040 zl.line_assessable_value,
1041 zl.legal_justification_text1,
1042 zl.legal_justification_text2,
1043 zl.legal_justification_text3,
1044 zl.reporting_currency_code,
1045 zl.trx_line_index,
1046 zl.offset_tax_rate_code,
1047 zl.proration_code,
1048 zl.other_doc_source,
1049 zl.reporting_only_flag,
1050 zl.ctrl_total_line_tx_amt,
1051 zl.tax_rate_type,
1052 zl.interface_entity_code,
1053 zl.interface_tax_line_id,
1054 zl.taxing_juris_geography_id,
1055 zl.adjusted_doc_tax_line_id,
1056 1,
1057 zl.tax_line_id, -- reversed_tax_line_id
1058 zl.account_source_tax_rate_id
1059 FROM zx_lines zl
1060 WHERE tax_line_id = l_reversed_tax_line_id_tb(i);
1061
1062 IF (g_level_procedure >= g_current_runtime_level ) THEN
1063 FND_LOG.STRING(g_level_procedure,
1064 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.reverse_document',
1065 'RETURN_STATUS = ' || x_return_status);
1066 FND_LOG.STRING(g_level_procedure,
1067 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.reverse_document.END',
1068 'ZX_TDS_REVERSE_DOCUMENT_PKG.reverse_document(-)');
1069 END IF;
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074
1075 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1076 FND_LOG.STRING(g_level_unexpected,
1077 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.reverse_document',
1078 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1079 FND_LOG.STRING(g_level_unexpected,
1080 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.reverse_document',
1081 'reverse_document(-)');
1082 END IF;
1083
1084 END reverse_document;
1085
1086 /* ======================================================================*
1087 | PROCEDURE get_tbl_index |
1088 | This function return the table index if a value l_number_value exists |
1089 | in the plsql data structure l_number_tbl. It returns NULL if |
1090 | l_number_value does not esist in l_number_tbl |
1091 * ======================================================================*/
1092 FUNCTION get_tbl_index (
1093 l_number_tbl IN tax_line_id_tp,
1094 l_number_value IN zx_lines.tax_line_id%TYPE,
1095 x_return_status OUT NOCOPY VARCHAR2
1096 ) RETURN NUMBER IS
1097
1098 l_return_index NUMBER;
1099
1100 BEGIN
1101
1102 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1103
1104 IF (g_level_procedure >= g_current_runtime_level ) THEN
1105 FND_LOG.STRING(g_level_procedure,
1106 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.get_tbl_index.BEGIN',
1107 'ZX_TDS_REVERSE_DOCUMENT_PKG.get_tbl_index(+)');
1108 END IF;
1109
1110 x_return_status := FND_API.G_RET_STS_SUCCESS;
1111
1112 l_return_index := NULL;
1113
1114 FOR i IN NVL(l_number_tbl.FIRST, 0) .. NVL(l_number_tbl.LAST, -1) LOOP
1115 IF l_number_value = l_number_tbl(i) THEN
1116 l_return_index := i;
1117 EXIT;
1118 END IF;
1119 END LOOP;
1120
1121 IF (g_level_procedure >= g_current_runtime_level ) THEN
1122 FND_LOG.STRING(g_level_procedure,
1123 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.get_tbl_index',
1124 'RETURN_STATUS = ' || x_return_status);
1125 FND_LOG.STRING(g_level_procedure,
1126 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.get_tbl_index.END',
1127 'ZX_TDS_REVERSE_DOCUMENT_PKG.get_tbl_index(-)');
1128 END IF;
1129
1130 RETURN l_return_index;
1131
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1135
1136 IF (g_level_unexpected >= g_current_runtime_level ) THEN
1137 FND_LOG.STRING(g_level_unexpected,
1138 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.get_tbl_index',
1139 sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
1140 FND_LOG.STRING(g_level_unexpected,
1141 'ZX.PLSQL.ZX_TDS_REVERSE_DOCUMENT_PKG.get_tbl_index',
1142 'get_tbl_index(-)');
1143 END IF;
1144 END get_tbl_index;
1145
1146 END ZX_TDS_REVERSE_DOCUMENT_PKG;
1147