[Home] [Help]
331: NULL;
332: ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.tax_line_id := NULL;
333: ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_dist_id := NULL;
334:
335: SELECT ZX_REC_NREC_DIST_S.nextval
336: INTO ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id
337: FROM dual;
338:
339: -- For a update event in quote calls(only PO has such case now),
1012: RETURN;
1013: END IF;
1014:
1015: FORALL ctr IN NVL(l_rec_nrec_dist_tbl.FIRST,0) .. NVL(l_rec_nrec_dist_tbl.LAST,-1)
1016: INSERT INTO zx_rec_nrec_dist_gt VALUES l_rec_nrec_dist_tbl(ctr);
1017:
1018: -- when is tax_variance_calc_flag is 'Y', calculate variance factors for all
1019: -- the tax distributions in global table zx_rec_nrec_dist_gt
1020:
1015: FORALL ctr IN NVL(l_rec_nrec_dist_tbl.FIRST,0) .. NVL(l_rec_nrec_dist_tbl.LAST,-1)
1016: INSERT INTO zx_rec_nrec_dist_gt VALUES l_rec_nrec_dist_tbl(ctr);
1017:
1018: -- when is tax_variance_calc_flag is 'Y', calculate variance factors for all
1019: -- the tax distributions in global table zx_rec_nrec_dist_gt
1020:
1021: IF l_exist_frozen_tax_dist_flag = 'Y' THEN
1022:
1023: delete_unnecessary_tax_dists(p_event_class_rec => p_event_class_rec,
1102: AND mrc_tax_line_flag = 'N';
1103:
1104: CURSOR fetch_tax_distributions_csr(l_tax_line_id NUMBER) IS
1105: SELECT *
1106: FROM zx_rec_nrec_dist
1107: WHERE trx_id = p_event_class_rec.trx_id
1108: AND application_id = p_event_class_rec.application_id
1109: AND entity_code = p_event_class_rec.entity_code
1110: AND event_class_code = p_event_class_rec.event_class_code
1303:
1304: END IF;
1305:
1306: FORALL ctr IN NVL(l_rec_nrec_dist_tbl.FIRST,0) .. NVL(l_rec_nrec_dist_tbl.LAST,-1)
1307: INSERT INTO zx_rec_nrec_dist_gt VALUES l_rec_nrec_dist_tbl(ctr);
1308:
1309: -- bug fix 3313938: add tax_variance_calc_flag check.
1310: IF g_variance_calc_flag = 'Y' THEN
1311: -- when is tax_variance_calc_flag is 'Y', calculate variance factors for
1308:
1309: -- bug fix 3313938: add tax_variance_calc_flag check.
1310: IF g_variance_calc_flag = 'Y' THEN
1311: -- when is tax_variance_calc_flag is 'Y', calculate variance factors for
1312: -- all the tax distributions in global table zx_rec_nrec_dist_gt
1313:
1314: ZX_TRD_INTERNAL_SERVICES_PVT.calc_variance_factors(
1315: x_return_status,
1316: p_error_buffer
1379: l_index number;
1380:
1381: CURSOR get_rec_nrec_dist_cur is
1382: SELECT *
1383: FROM zx_rec_nrec_dist
1384: WHERE rec_nrec_tax_dist_id IN (SELECT tax_dist_id FROM zx_tax_dist_id_gt);
1385:
1386: BEGIN
1387:
1403: EXIT when get_rec_nrec_dist_cur%NOTFOUND;
1404:
1405: -- get g_tax_dist_id
1406: --
1407: SELECT ZX_REC_NREC_DIST_S.nextval
1408: INTO ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id
1409: FROM dual;
1410:
1411: p_rec_nrec_dist_tbl(l_index).Reverse_Flag:= 'Y';
1483: l_index:= l_index + 1;
1484:
1485: -- get g_tax_dist_id
1486: --
1487: SELECT ZX_REC_NREC_DIST_S.nextval
1488: INTO ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id
1489: FROM dual;
1490:
1491: -- ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id:= ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id + 1;
1932:
1933: PROCEDURE REVERSE_DISTRIBUTIONS(
1934: x_return_status OUT NOCOPY VARCHAR2) IS
1935:
1936: TYPE num_tbl_type IS TABLE OF zx_rec_nrec_dist.rec_nrec_tax_dist_id%TYPE
1937: INDEX BY BINARY_INTEGER;
1938: --Bug 9651174
1939: TYPE num2_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_line_id%TYPE
1940: INDEX BY BINARY_INTEGER;
1935:
1936: TYPE num_tbl_type IS TABLE OF zx_rec_nrec_dist.rec_nrec_tax_dist_id%TYPE
1937: INDEX BY BINARY_INTEGER;
1938: --Bug 9651174
1939: TYPE num2_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_line_id%TYPE
1940: INDEX BY BINARY_INTEGER;
1941:
1942: TYPE char30_tbl_type IS TABLE OF zx_rec_nrec_dist.entity_code%TYPE
1943: INDEX BY BINARY_INTEGER;
1938: --Bug 9651174
1939: TYPE num2_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_line_id%TYPE
1940: INDEX BY BINARY_INTEGER;
1941:
1942: TYPE char30_tbl_type IS TABLE OF zx_rec_nrec_dist.entity_code%TYPE
1943: INDEX BY BINARY_INTEGER;
1944: TYPE char150_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_number%TYPE
1945: INDEX BY BINARY_INTEGER;
1946: TYPE date_tbl_type IS TABLE OF zx_rec_nrec_dist.gl_date%TYPE
1940: INDEX BY BINARY_INTEGER;
1941:
1942: TYPE char30_tbl_type IS TABLE OF zx_rec_nrec_dist.entity_code%TYPE
1943: INDEX BY BINARY_INTEGER;
1944: TYPE char150_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_number%TYPE
1945: INDEX BY BINARY_INTEGER;
1946: TYPE date_tbl_type IS TABLE OF zx_rec_nrec_dist.gl_date%TYPE
1947: INDEX BY BINARY_INTEGER;
1948:
1942: TYPE char30_tbl_type IS TABLE OF zx_rec_nrec_dist.entity_code%TYPE
1943: INDEX BY BINARY_INTEGER;
1944: TYPE char150_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_number%TYPE
1945: INDEX BY BINARY_INTEGER;
1946: TYPE date_tbl_type IS TABLE OF zx_rec_nrec_dist.gl_date%TYPE
1947: INDEX BY BINARY_INTEGER;
1948:
1949: l_rvrsed_tax_dist_id_tbl num_tbl_type;
1950: l_rvrsng_appln_id_tbl num_tbl_type;
1978: x_return_status:= FND_API.G_RET_STS_SUCCESS;
1979:
1980: -- Bug 9088833. This is used to populate reverse_flag and reversed_tax_dist_id
1981: SELECT count(*) INTO l_dist_count
1982: FROM zx_rec_nrec_dist
1983: WHERE (application_id,
1984: entity_code,
1985: event_class_code,
1986: trx_id) IN (SELECT DISTINCT reversing_appln_id,
2021: l_summary_tax_line_id_tbl,
2022: l_rvrsng_trx_number_tbl,
2023: l_org_id_tbl,
2024: l_gl_date_tbl
2025: FROM zx_rec_nrec_dist zd, zx_reverse_dist_gt gt,
2026: --Bug 9883546
2027: zx_reverse_trx_lines_gt lines_gt, zx_lines zl
2028: WHERE zd.application_id = gt.reversed_appln_id
2029: AND zd.entity_code = gt.reversed_entity_code
2060: )
2061: )
2062: --Bug 9189035
2063: AND NOT EXISTS (SELECT 1
2064: FROM zx_rec_nrec_dist zxd
2065: WHERE zxd.application_id = zd.application_id
2066: AND zxd.entity_code = zd.entity_code
2067: AND zxd.event_class_code = zd.event_class_code
2068: AND zxd.trx_id = zd.trx_id
2103: l_summary_tax_line_id_tbl,
2104: l_rvrsng_trx_number_tbl,
2105: l_org_id_tbl,
2106: l_gl_date_tbl
2107: FROM zx_rec_nrec_dist zd, zx_reverse_dist_gt gt, zx_lines zl
2108: WHERE zd.application_id = gt.reversed_appln_id
2109: AND zd.entity_code = gt.reversed_entity_code
2110: AND zd.event_class_code = gt.reversed_evnt_cls_code
2111: AND zd.trx_id = gt.reversed_trx_id
2132: )
2133: )
2134: --Bug 9189035
2135: AND NOT EXISTS (SELECT 1
2136: FROM zx_rec_nrec_dist zxd
2137: WHERE zxd.application_id = zd.application_id
2138: AND zxd.entity_code = zd.entity_code
2139: AND zxd.event_class_code = zd.event_class_code
2140: AND zxd.trx_id = zd.trx_id
2147: --Bug 13434422
2148: -- Update the REVERSE_FLAG of the original dist line to 'Y'
2149: FORALL i IN NVL(l_rvrsed_tax_dist_id_tbl.FIRST, 0)..
2150: NVL(l_rvrsed_tax_dist_id_tbl.LAST, -1)
2151: UPDATE ZX_REC_NREC_DIST
2152: SET REVERSE_FLAG = 'Y'
2153: WHERE REC_NREC_TAX_DIST_ID = l_rvrsed_tax_dist_id_tbl(i);
2154: END IF;
2155:
2168: -- Insert the reversing tax distributions
2169:
2170: FORALL i IN NVL(l_rvrsed_tax_dist_id_tbl.FIRST, 0)..
2171: NVL(l_rvrsed_tax_dist_id_tbl.LAST, -1)
2172: INSERT INTO ZX_REC_NREC_DIST(
2173: REC_NREC_TAX_DIST_ID,
2174: APPLICATION_ID,
2175: ENTITY_CODE,
2176: EVENT_CLASS_CODE,
2343: TAX_JURISDICTION_ID,
2344: ACCOUNT_SOURCE_TAX_RATE_ID
2345: )
2346: SELECT
2347: ZX_REC_NREC_DIST_S.NEXTVAL,
2348: l_rvrsng_appln_id_tbl(i), -- GT.REVERSING_APPLN_ID,
2349: l_rvrsng_entity_code_tbl(i), -- GT.REVERSING_ENTITY_CODE,
2350: l_rvrsng_evnt_cls_code_tbl(i), -- GT.REVERSING_EVNT_CLS_CODE,
2351: ZD.EVENT_TYPE_CODE,
2517: ZD.INTERNAL_ORGANIZATION_ID,
2518: ZD.DEF_REC_SETTLEMENT_OPTION_CODE,
2519: ZD.TAX_JURISDICTION_ID,
2520: ZD.ACCOUNT_SOURCE_TAX_RATE_ID
2521: FROM zx_rec_nrec_dist zd
2522: WHERE zd.rec_nrec_tax_dist_id = l_rvrsed_tax_dist_id_tbl(i);
2523:
2524: --Bug 13434422
2525: -- Moved the below update into IF ELSE, only if l_dist_count <> 0
2525: -- Moved the below update into IF ELSE, only if l_dist_count <> 0
2526: -- Update the REVERSE_FLAG of the original dist line to 'Y'
2527: --FORALL i IN NVL(l_rvrsed_tax_dist_id_tbl.FIRST, 0)..
2528: -- NVL(l_rvrsed_tax_dist_id_tbl.LAST, -1)
2529: -- UPDATE ZX_REC_NREC_DIST
2530: -- SET REVERSE_FLAG = 'Y'
2531: -- WHERE REC_NREC_TAX_DIST_ID = l_rvrsed_tax_dist_id_tbl(i);
2532:
2533: IF (g_level_procedure >= g_current_runtime_level ) THEN
4045: /* ======================================================================*
4046: | PRIVATE PROCEDURE fetch_tax_distributions |
4047: | |
4048: | This procedure is used to fetch all the tax distributions from |
4049: | ZX_REC_NREC_DIST table to the PL/SQL table p_rec_nrec_dist_tbl |
4050: | |
4051: * ======================================================================*/
4052:
4053: PROCEDURE fetch_tax_distributions(
4059: p_rec_nrec_dist_end_index OUT NOCOPY NUMBER,
4060: x_return_status OUT NOCOPY VARCHAR2) IS
4061:
4062: CURSOR fetch_tax_distributions_csr IS
4063: SELECT * FROM zx_rec_nrec_dist
4064: WHERE trx_id =
4065: p_event_class_rec.trx_id
4066: AND application_id =
4067: p_event_class_rec.application_id
4419: | PRIVATE PROCEDURE insert_global_table |
4420: | |
4421: | DESCRIPTION |
4422: | This procedure is used to insert rec/non-rec tax distributions from the |
4423: | PL/SQL table to the global tempoarary table zx_rec_nrec_dist_gt when there |
4424: | are more than 1000 records in the PL/SQL table |
4425: | |
4426: * ============================================================================*/
4427:
4465: END IF;
4466:
4467:
4468: FORALL ctr IN NVL(p_rec_nrec_dist_tbl.FIRST,0) .. NVL(p_rec_nrec_dist_tbl.LAST,-1)
4469: INSERT INTO zx_rec_nrec_dist_gt VALUES p_rec_nrec_dist_tbl(ctr);
4470:
4471: -- reinitialize the PL/SQL table.
4472: p_rec_nrec_dist_tbl.delete;
4473: p_rec_nrec_dist_end_index := 0;
4534: IF p_rec_nrec_dist_tbl(i).rec_nrec_tax_dist_id IS NULL THEN
4535:
4536: -- get g_tax_dist_id
4537: --
4538: SELECT ZX_REC_NREC_DIST_S.nextval
4539: INTO ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id
4540: FROM dual;
4541:
4542: p_rec_nrec_dist_tbl(i).rec_nrec_tax_dist_id :=
4692: NVL(p_ledger_id, ledger_id) ledger_id,
4693: trx_currency_code,
4694: tax_rate,
4695: 0
4696: FROM zx_rec_nrec_dist
4697: WHERE application_id = p_event_class_rec.application_id
4698: AND entity_code = p_event_class_rec.entity_code
4699: AND event_class_code = p_event_class_rec.event_class_code
4700: AND trx_id = p_event_class_rec.trx_id
4714: WHERE sob.set_of_books_id = p_ledger_id
4715: AND cur.currency_code = sob.currency_code;
4716:
4717: TYPE rec_nrec_tax_dist_id_tbl_type IS TABLE OF
4718: zx_rec_nrec_dist.rec_nrec_tax_dist_id%TYPE INDEX BY BINARY_INTEGER;
4719:
4720: TYPE tax_line_id_tbl_type IS TABLE OF zx_rec_nrec_dist.tax_line_id%TYPE
4721: INDEX BY BINARY_INTEGER;
4722:
4716:
4717: TYPE rec_nrec_tax_dist_id_tbl_type IS TABLE OF
4718: zx_rec_nrec_dist.rec_nrec_tax_dist_id%TYPE INDEX BY BINARY_INTEGER;
4719:
4720: TYPE tax_line_id_tbl_type IS TABLE OF zx_rec_nrec_dist.tax_line_id%TYPE
4721: INDEX BY BINARY_INTEGER;
4722:
4723: TYPE recoverable_flg_tbl_type IS TABLE OF
4724: zx_rec_nrec_dist.Recoverable_Flag%TYPE INDEX BY BINARY_INTEGER;
4720: TYPE tax_line_id_tbl_type IS TABLE OF zx_rec_nrec_dist.tax_line_id%TYPE
4721: INDEX BY BINARY_INTEGER;
4722:
4723: TYPE recoverable_flg_tbl_type IS TABLE OF
4724: zx_rec_nrec_dist.Recoverable_Flag%TYPE INDEX BY BINARY_INTEGER;
4725:
4726: TYPE rec_nrec_tax_amt_tbl_type IS TABLE OF
4727: zx_rec_nrec_dist.rec_nrec_tax_amt%TYPE INDEX BY BINARY_INTEGER;
4728:
4723: TYPE recoverable_flg_tbl_type IS TABLE OF
4724: zx_rec_nrec_dist.Recoverable_Flag%TYPE INDEX BY BINARY_INTEGER;
4725:
4726: TYPE rec_nrec_tax_amt_tbl_type IS TABLE OF
4727: zx_rec_nrec_dist.rec_nrec_tax_amt%TYPE INDEX BY BINARY_INTEGER;
4728:
4729: TYPE taxable_amt_tbl_type IS TABLE OF zx_rec_nrec_dist.taxable_amt%TYPE
4730: INDEX BY BINARY_INTEGER;
4731:
4725:
4726: TYPE rec_nrec_tax_amt_tbl_type IS TABLE OF
4727: zx_rec_nrec_dist.rec_nrec_tax_amt%TYPE INDEX BY BINARY_INTEGER;
4728:
4729: TYPE taxable_amt_tbl_type IS TABLE OF zx_rec_nrec_dist.taxable_amt%TYPE
4730: INDEX BY BINARY_INTEGER;
4731:
4732: TYPE tax_amt_funcl_curr_tbl_type IS TABLE OF
4733: zx_rec_nrec_dist.rec_nrec_tax_amt_funcl_curr%TYPE INDEX BY BINARY_INTEGER;
4729: TYPE taxable_amt_tbl_type IS TABLE OF zx_rec_nrec_dist.taxable_amt%TYPE
4730: INDEX BY BINARY_INTEGER;
4731:
4732: TYPE tax_amt_funcl_curr_tbl_type IS TABLE OF
4733: zx_rec_nrec_dist.rec_nrec_tax_amt_funcl_curr%TYPE INDEX BY BINARY_INTEGER;
4734:
4735: TYPE txable_amt_funcl_curr_tbl_type IS TABLE OF
4736: zx_rec_nrec_dist.taxable_amt_funcl_curr%TYPE INDEX BY BINARY_INTEGER;
4737:
4732: TYPE tax_amt_funcl_curr_tbl_type IS TABLE OF
4733: zx_rec_nrec_dist.rec_nrec_tax_amt_funcl_curr%TYPE INDEX BY BINARY_INTEGER;
4734:
4735: TYPE txable_amt_funcl_curr_tbl_type IS TABLE OF
4736: zx_rec_nrec_dist.taxable_amt_funcl_curr%TYPE INDEX BY BINARY_INTEGER;
4737:
4738: TYPE unrounded_tax_amt_tbl_type IS TABLE OF
4739: zx_rec_nrec_dist.unrounded_rec_nrec_tax_amt%TYPE INDEX BY BINARY_INTEGER;
4740:
4735: TYPE txable_amt_funcl_curr_tbl_type IS TABLE OF
4736: zx_rec_nrec_dist.taxable_amt_funcl_curr%TYPE INDEX BY BINARY_INTEGER;
4737:
4738: TYPE unrounded_tax_amt_tbl_type IS TABLE OF
4739: zx_rec_nrec_dist.unrounded_rec_nrec_tax_amt%TYPE INDEX BY BINARY_INTEGER;
4740:
4741: TYPE unrounded_taxable_amt_tbl_type IS TABLE OF
4742: zx_rec_nrec_dist.unrounded_taxable_amt%TYPE INDEX BY BINARY_INTEGER;
4743:
4738: TYPE unrounded_tax_amt_tbl_type IS TABLE OF
4739: zx_rec_nrec_dist.unrounded_rec_nrec_tax_amt%TYPE INDEX BY BINARY_INTEGER;
4740:
4741: TYPE unrounded_taxable_amt_tbl_type IS TABLE OF
4742: zx_rec_nrec_dist.unrounded_taxable_amt%TYPE INDEX BY BINARY_INTEGER;
4743:
4744: TYPE ledger_id_tbl_type IS TABLE OF zx_rec_nrec_dist.ledger_id%TYPE
4745: INDEX BY BINARY_INTEGER;
4746:
4740:
4741: TYPE unrounded_taxable_amt_tbl_type IS TABLE OF
4742: zx_rec_nrec_dist.unrounded_taxable_amt%TYPE INDEX BY BINARY_INTEGER;
4743:
4744: TYPE ledger_id_tbl_type IS TABLE OF zx_rec_nrec_dist.ledger_id%TYPE
4745: INDEX BY BINARY_INTEGER;
4746:
4747: TYPE trx_currency_code_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_currency_code%TYPE
4748: INDEX BY BINARY_INTEGER;
4743:
4744: TYPE ledger_id_tbl_type IS TABLE OF zx_rec_nrec_dist.ledger_id%TYPE
4745: INDEX BY BINARY_INTEGER;
4746:
4747: TYPE trx_currency_code_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_currency_code%TYPE
4748: INDEX BY BINARY_INTEGER;
4749:
4750: TYPE rec_nrec_rate_tbl_type IS TABLE OF zx_rec_nrec_dist.rec_nrec_rate%TYPE
4751: INDEX BY BINARY_INTEGER;
4746:
4747: TYPE trx_currency_code_tbl_type IS TABLE OF zx_rec_nrec_dist.trx_currency_code%TYPE
4748: INDEX BY BINARY_INTEGER;
4749:
4750: TYPE rec_nrec_rate_tbl_type IS TABLE OF zx_rec_nrec_dist.rec_nrec_rate%TYPE
4751: INDEX BY BINARY_INTEGER;
4752: TYPE func_curr_rnd_adjust_tbl_type IS TABLE OF
4753: zx_rec_nrec_dist.func_curr_rounding_adjustment%TYPE INDEX BY BINARY_INTEGER;
4754:
4749:
4750: TYPE rec_nrec_rate_tbl_type IS TABLE OF zx_rec_nrec_dist.rec_nrec_rate%TYPE
4751: INDEX BY BINARY_INTEGER;
4752: TYPE func_curr_rnd_adjust_tbl_type IS TABLE OF
4753: zx_rec_nrec_dist.func_curr_rounding_adjustment%TYPE INDEX BY BINARY_INTEGER;
4754:
4755: l_rec_nrec_tax_dist_id_tbl rec_nrec_tax_dist_id_tbl_type;
4756: l_tax_line_id_tbl tax_line_id_tbl_type;
4757: l_recoverable_flg_tbl recoverable_flg_tbl_type;
4794: l_nrec_tax_rounding_diff NUMBER;
4795:
4796: l_tax_line_rounding_diff NUMBER;
4797:
4798: l_current_tax_line_id zx_rec_nrec_dist.tax_line_id%TYPE;
4799: l_rec_begin_index NUMBER;
4800: l_nrec_begin_index NUMBER;
4801:
4802: l_minimum_accountable_unit NUMBER;
4837:
4838: IF (g_level_statement >= g_current_runtime_level ) THEN
4839: FND_LOG.STRING(g_level_statement,
4840: 'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate',
4841: 'No tax distributions are fetched from ZX_REC_NREC_DIST.');
4842: END IF;
4843: RETURN;
4844: ELSE
4845:
5522: END LOOP;
5523:
5524:
5525:
5526: -- Update Table ZX_REC_NREC_DIST
5527: --
5528: FORALL i IN NVL(l_rec_nrec_tax_dist_id_tbl.FIRST,0) .. NVL(l_rec_nrec_tax_dist_id_tbl.LAST,-1)
5529:
5530: UPDATE zx_rec_nrec_dist
5526: -- Update Table ZX_REC_NREC_DIST
5527: --
5528: FORALL i IN NVL(l_rec_nrec_tax_dist_id_tbl.FIRST,0) .. NVL(l_rec_nrec_tax_dist_id_tbl.LAST,-1)
5529:
5530: UPDATE zx_rec_nrec_dist
5531: SET currency_conversion_rate = p_currency_conversion_rate,
5532: currency_conversion_type = p_currency_conversion_type,
5533: currency_conversion_date = p_currency_conversion_date,
5534: rec_nrec_tax_amt_funcl_curr = l_tax_amt_funcl_curr_tbl(i),
5738: p_pa_item_info_tbl IN OUT NOCOPY ZX_API_PUB.pa_item_info_tbl_type,
5739: x_return_status OUT NOCOPY VARCHAR2) IS
5740:
5741: CURSOR get_tax_dists_csr(
5742: c_application_id zx_rec_nrec_dist.application_id%TYPE,
5743: c_entity_code zx_rec_nrec_dist.entity_code%TYPE,
5744: c_event_class_code zx_rec_nrec_dist.entity_code%TYPE,
5745: c_trx_id zx_rec_nrec_dist.trx_id%TYPE,
5746: c_trx_line_id zx_rec_nrec_dist.trx_line_id%TYPE,
5739: x_return_status OUT NOCOPY VARCHAR2) IS
5740:
5741: CURSOR get_tax_dists_csr(
5742: c_application_id zx_rec_nrec_dist.application_id%TYPE,
5743: c_entity_code zx_rec_nrec_dist.entity_code%TYPE,
5744: c_event_class_code zx_rec_nrec_dist.entity_code%TYPE,
5745: c_trx_id zx_rec_nrec_dist.trx_id%TYPE,
5746: c_trx_line_id zx_rec_nrec_dist.trx_line_id%TYPE,
5747: c_trx_level_type zx_rec_nrec_dist.trx_level_type%TYPE,
5740:
5741: CURSOR get_tax_dists_csr(
5742: c_application_id zx_rec_nrec_dist.application_id%TYPE,
5743: c_entity_code zx_rec_nrec_dist.entity_code%TYPE,
5744: c_event_class_code zx_rec_nrec_dist.entity_code%TYPE,
5745: c_trx_id zx_rec_nrec_dist.trx_id%TYPE,
5746: c_trx_line_id zx_rec_nrec_dist.trx_line_id%TYPE,
5747: c_trx_level_type zx_rec_nrec_dist.trx_level_type%TYPE,
5748: c_item_expense_dist_id zx_rec_nrec_dist.trx_line_dist_id %TYPE) IS
5741: CURSOR get_tax_dists_csr(
5742: c_application_id zx_rec_nrec_dist.application_id%TYPE,
5743: c_entity_code zx_rec_nrec_dist.entity_code%TYPE,
5744: c_event_class_code zx_rec_nrec_dist.entity_code%TYPE,
5745: c_trx_id zx_rec_nrec_dist.trx_id%TYPE,
5746: c_trx_line_id zx_rec_nrec_dist.trx_line_id%TYPE,
5747: c_trx_level_type zx_rec_nrec_dist.trx_level_type%TYPE,
5748: c_item_expense_dist_id zx_rec_nrec_dist.trx_line_dist_id %TYPE) IS
5749: SELECT *
5742: c_application_id zx_rec_nrec_dist.application_id%TYPE,
5743: c_entity_code zx_rec_nrec_dist.entity_code%TYPE,
5744: c_event_class_code zx_rec_nrec_dist.entity_code%TYPE,
5745: c_trx_id zx_rec_nrec_dist.trx_id%TYPE,
5746: c_trx_line_id zx_rec_nrec_dist.trx_line_id%TYPE,
5747: c_trx_level_type zx_rec_nrec_dist.trx_level_type%TYPE,
5748: c_item_expense_dist_id zx_rec_nrec_dist.trx_line_dist_id %TYPE) IS
5749: SELECT *
5750: FROM zx_rec_nrec_dist
5743: c_entity_code zx_rec_nrec_dist.entity_code%TYPE,
5744: c_event_class_code zx_rec_nrec_dist.entity_code%TYPE,
5745: c_trx_id zx_rec_nrec_dist.trx_id%TYPE,
5746: c_trx_line_id zx_rec_nrec_dist.trx_line_id%TYPE,
5747: c_trx_level_type zx_rec_nrec_dist.trx_level_type%TYPE,
5748: c_item_expense_dist_id zx_rec_nrec_dist.trx_line_dist_id %TYPE) IS
5749: SELECT *
5750: FROM zx_rec_nrec_dist
5751: WHERE trx_line_dist_id = c_item_expense_dist_id
5744: c_event_class_code zx_rec_nrec_dist.entity_code%TYPE,
5745: c_trx_id zx_rec_nrec_dist.trx_id%TYPE,
5746: c_trx_line_id zx_rec_nrec_dist.trx_line_id%TYPE,
5747: c_trx_level_type zx_rec_nrec_dist.trx_level_type%TYPE,
5748: c_item_expense_dist_id zx_rec_nrec_dist.trx_line_dist_id %TYPE) IS
5749: SELECT *
5750: FROM zx_rec_nrec_dist
5751: WHERE trx_line_dist_id = c_item_expense_dist_id
5752: AND application_id = c_application_id
5746: c_trx_line_id zx_rec_nrec_dist.trx_line_id%TYPE,
5747: c_trx_level_type zx_rec_nrec_dist.trx_level_type%TYPE,
5748: c_item_expense_dist_id zx_rec_nrec_dist.trx_line_dist_id %TYPE) IS
5749: SELECT *
5750: FROM zx_rec_nrec_dist
5751: WHERE trx_line_dist_id = c_item_expense_dist_id
5752: AND application_id = c_application_id
5753: AND entity_code = c_entity_code
5754: AND event_class_code = c_event_class_code
5762: FROM zx_rates_b rates
5763: WHERE rates.tax_rate_id = c_tax_rate_id;
5764:
5765: CURSOR check_current_rule_csr(
5766: c_rec_rate_result_id zx_rec_nrec_dist.rec_rate_result_id%TYPE) IS
5767: SELECT rules.service_type_code,
5768: rules.priority,
5769: factor_dtls.determining_factor_code
5770: FROM zx_process_results results,
6092: | PUBLIC PROCEDURE delete_unnecessary_tax_dists |
6093: | |
6094: | DESCRIPTION: |
6095: | This procedure is called if associate_child_frozen_flag on detail tax |
6096: | lines is 'Y' after tax dists are inserted into zx_rec_nrec_dist_gt. |
6097: | If there is no change on tax_line and item distribution, the reversed |
6098: | tax_distributions and the new created tax distributions, which are |
6099: | created for the frozen taxdistributions, will be deleted. |
6100: | |
6186: -- are not created for Historical Invoices on re-validation.
6187:
6188: SELECT REC_NREC_TAX_DIST_ID, REVERSED_TAX_DIST_ID
6189: BULK COLLECT INTO l_rec_nrec_tax_Dist_id_tbl1, l_reversed_tax_dist_id_tbl
6190: FROM ZX_REC_NREC_DIST_GT gt
6191: WHERE reversed_tax_dist_id IS NOT NULL
6192: AND EXISTS
6193: (SELECT /*+ first_rows (1) index (gt1, ZX_REC_NREC_DIST_GT_N1) */ 1
6194: FROM ZX_REC_NREC_DIST_GT gt1
6189: BULK COLLECT INTO l_rec_nrec_tax_Dist_id_tbl1, l_reversed_tax_dist_id_tbl
6190: FROM ZX_REC_NREC_DIST_GT gt
6191: WHERE reversed_tax_dist_id IS NOT NULL
6192: AND EXISTS
6193: (SELECT /*+ first_rows (1) index (gt1, ZX_REC_NREC_DIST_GT_N1) */ 1
6194: FROM ZX_REC_NREC_DIST_GT gt1
6195: WHERE gt1.tax_line_id = gt.tax_line_id
6196: AND gt1.trx_line_dist_id = gt.trx_line_dist_id
6197: AND gt1.tax_status_id = gt.tax_status_id
6190: FROM ZX_REC_NREC_DIST_GT gt
6191: WHERE reversed_tax_dist_id IS NOT NULL
6192: AND EXISTS
6193: (SELECT /*+ first_rows (1) index (gt1, ZX_REC_NREC_DIST_GT_N1) */ 1
6194: FROM ZX_REC_NREC_DIST_GT gt1
6195: WHERE gt1.tax_line_id = gt.tax_line_id
6196: AND gt1.trx_line_dist_id = gt.trx_line_dist_id
6197: AND gt1.tax_status_id = gt.tax_status_id
6198: AND gt1.tax_rate_id = gt.tax_rate_id
6245: IF l_rec_nrec_tax_Dist_id_tbl1.count > 0 THEN
6246:
6247: SELECT rec_nrec_tax_dist_id
6248: BULK COLLECT INTO l_rec_nrec_tax_Dist_id_tbl2
6249: FROM ZX_REC_NREC_DIST_GT gt
6250: WHERE gt.freeze_flag = 'N'
6251: AND gt.reverse_flag = 'N'
6252: AND EXISTS
6253: (SELECT /*+ first_rows (1) index (gt1, ZX_REC_NREC_DIST_GT_N1) */ 1
6249: FROM ZX_REC_NREC_DIST_GT gt
6250: WHERE gt.freeze_flag = 'N'
6251: AND gt.reverse_flag = 'N'
6252: AND EXISTS
6253: (SELECT /*+ first_rows (1) index (gt1, ZX_REC_NREC_DIST_GT_N1) */ 1
6254: FROM ZX_REC_NREC_DIST_GT gt1
6255: WHERE gt1.tax_line_id = gt.tax_line_id
6256: AND gt1.trx_line_dist_id = gt.trx_line_dist_id
6257: AND gt1.tax_status_id = gt.tax_status_id
6250: WHERE gt.freeze_flag = 'N'
6251: AND gt.reverse_flag = 'N'
6252: AND EXISTS
6253: (SELECT /*+ first_rows (1) index (gt1, ZX_REC_NREC_DIST_GT_N1) */ 1
6254: FROM ZX_REC_NREC_DIST_GT gt1
6255: WHERE gt1.tax_line_id = gt.tax_line_id
6256: AND gt1.trx_line_dist_id = gt.trx_line_dist_id
6257: AND gt1.tax_status_id = gt.tax_status_id
6258: AND gt1.tax_rate_id = gt.tax_rate_id
6308:
6309: IF l_rec_nrec_tax_Dist_id_tbl1.COUNT > 0 AND l_rec_nrec_tax_Dist_id_tbl2.COUNT > 0 THEN
6310:
6311: FORALL j in l_rec_nrec_tax_Dist_id_tbl1.FIRST .. l_rec_nrec_tax_Dist_id_tbl1.LAST
6312: DELETE FROM zx_rec_nrec_dist_gt
6313: WHERE rec_nrec_tax_dist_id IN l_rec_nrec_tax_Dist_id_tbl1(j);
6314:
6315: FORALL j in l_rec_nrec_tax_Dist_id_tbl2.FIRST .. l_rec_nrec_tax_Dist_id_tbl2.LAST
6316: DELETE FROM zx_rec_nrec_dist_gt
6312: DELETE FROM zx_rec_nrec_dist_gt
6313: WHERE rec_nrec_tax_dist_id IN l_rec_nrec_tax_Dist_id_tbl1(j);
6314:
6315: FORALL j in l_rec_nrec_tax_Dist_id_tbl2.FIRST .. l_rec_nrec_tax_Dist_id_tbl2.LAST
6316: DELETE FROM zx_rec_nrec_dist_gt
6317: WHERE rec_nrec_tax_dist_id IN l_rec_nrec_tax_Dist_id_tbl2(j);
6318:
6319: END IF;
6320:
6321: IF l_reversed_tax_dist_id_tbl.COUNT > 0 AND l_rec_nrec_tax_Dist_id_tbl1.count > 0
6322: AND l_rec_nrec_tax_Dist_id_tbl2.COUNT > 0 THEN
6323:
6324: FORALL i IN l_reversed_tax_dist_id_tbl.FIRST .. l_reversed_tax_dist_id_tbl.LAST
6325: UPDATE zx_rec_nrec_dist_gt gt
6326: SET reverse_flag = 'N'
6327: WHERE rec_nrec_tax_dist_id = l_reversed_tax_dist_id_tbl(i);
6328:
6329: END IF;
6368:
6369: x_return_status:= FND_API.G_RET_STS_SUCCESS;
6370:
6371: FORALL i IN NVL(p_tax_dist_id_tbl.FIRST, 0) .. NVL(p_tax_dist_id_tbl.LAST, -1)
6372: UPDATE ZX_Rec_Nrec_Dist
6373: SET posting_flag = 'A'
6374: WHERE rec_nrec_tax_dist_id = p_tax_dist_id_tbl(i);
6375:
6376: IF (g_level_procedure >= g_current_runtime_level ) THEN