[Home] [Help]
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
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,
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
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
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
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,
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,
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:
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
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
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)
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)
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;
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,
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:
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.
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,
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
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
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),
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),
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
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,
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
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,
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),
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),
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
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
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)),
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))
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
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
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