[Home] [Help]
406: p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
407: p_return_status OUT NOCOPY VARCHAR2,
408: p_error_buffer OUT NOCOPY VARCHAR2 )
409: IS
410: l_tax_line_rec ZX_DETAIL_TAX_LINES_GT%ROWTYPE;
411: l_offset_tax_line_tbl ZX_TDS_CALC_SERVICES_PUB_PKG.DETAIL_TAX_LINES_TBL_TYPE;
412: i BINARY_INTEGER;
413:
414: --Table to derive the apportionment number in case the transaction line has same taxes
417: l_tax_index VARCHAR2(240);
418:
419: CURSOR get_tax_line_csr
420: IS
421: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
422: tax_line_id,
423: internal_organization_id,
424: application_id,
425: entity_code,
709: proration_code,
710: other_doc_source,
711: ctrl_total_line_tx_amt,
712: tax_rate_type
713: FROM ZX_DETAIL_TAX_LINES_GT gt1
714: WHERE
715: /* -- commented out for bug fix 5417887
716: application_id = p_event_class_rec.application_id
717: AND entity_code = p_event_class_rec.entity_code
728: AND adjusted_doc_trx_id IS NULL
729: AND NOT EXISTS
730: (SELECT 1
731: FROM zx_rates_b rates,
732: zx_detail_tax_lines_gt gt2
733: WHERE gt2.application_id = gt1.application_id
734: AND gt2.entity_code = gt1.entity_code
735: AND gt2.event_class_code = gt1.event_class_code
736: AND gt2.trx_id = gt1.trx_id
1088: l_tax_line_rec.tax_rate_type;
1089:
1090: IF get_tax_line_csr%FOUND THEN
1091: BEGIN --bug6509867
1092: UPDATE zx_detail_tax_lines_gt
1093: SET offset_flag = 'N'
1094: WHERE offset_flag = 'Y'
1095: AND tax_provider_id IS NULL
1096: AND offset_link_to_tax_line_id IS NULL
1213: x_error_buffer OUT NOCOPY VARCHAR2
1214: ) IS
1215:
1216: CURSOR get_tax_lines_csr IS
1217: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
1218: application_id,
1219: event_class_code,
1220: entity_code,
1221: trx_id,
1224: tax_line_id,
1225: tax_regime_code,
1226: tax,
1227: tax_apportionment_line_number
1228: FROM zx_detail_tax_lines_gt
1229: WHERE mrc_tax_line_flag = 'N'
1230: ORDER BY application_id,
1231: event_class_code,
1232: entity_code,
1314:
1315: END LOOP;
1316:
1317: FORALL i in 1 .. l_trx_line_id_tab.COUNT
1318: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
1319: zx_detail_tax_lines_gt
1320: SET tax_line_number = l_tax_line_number_tab(i)
1321: WHERE tax_line_id = l_tax_line_id_tab(i);
1322:
1315: END LOOP;
1316:
1317: FORALL i in 1 .. l_trx_line_id_tab.COUNT
1318: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
1319: zx_detail_tax_lines_gt
1320: SET tax_line_number = l_tax_line_number_tab(i)
1321: WHERE tax_line_id = l_tax_line_id_tab(i);
1322:
1323: IF (g_level_statement >= g_current_runtime_level ) THEN
1323: IF (g_level_statement >= g_current_runtime_level ) THEN
1324: FND_LOG.STRING(g_level_statement,
1325: 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.populate_tax_line_numbers',
1326: 'After update the tax line numbers to the non-mrc tax lines '||
1327: 'in the zx_detail_tax_lines_gt');
1328: END IF;
1329:
1330: --IF p_event_class_rec.enable_mrc_flag = 'Y' THEN
1331: -- FORALL i in 1 .. l_trx_line_id_tab.COUNT
1328: END IF;
1329:
1330: --IF p_event_class_rec.enable_mrc_flag = 'Y' THEN
1331: -- FORALL i in 1 .. l_trx_line_id_tab.COUNT
1332: -- UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
1333: -- zx_detail_tax_lines_gt
1334: -- SET tax_line_number = l_tax_line_number_tab(i)
1335: -- WHERE application_id = l_application_id_tab(i)
1336: -- AND event_class_code = l_event_class_code_tab(i)
1329:
1330: --IF p_event_class_rec.enable_mrc_flag = 'Y' THEN
1331: -- FORALL i in 1 .. l_trx_line_id_tab.COUNT
1332: -- UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
1333: -- zx_detail_tax_lines_gt
1334: -- SET tax_line_number = l_tax_line_number_tab(i)
1335: -- WHERE application_id = l_application_id_tab(i)
1336: -- AND event_class_code = l_event_class_code_tab(i)
1337: -- AND entity_code = l_entity_code_tab(i)
1400: TYPE date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1401: TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1402: TYPE var_30_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1403: TYPE var_1_tab_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
1404: TYPE var_rate_code_tab_type IS TABLE OF zx_detail_tax_lines_gt.tax_rate_code%TYPE
1405: INDEX BY BINARY_INTEGER;
1406:
1407: l_tax_line_id_tab num_tab_type;
1408: l_tax_regime_code_tab var_30_tab_type;
1424:
1425: l_tax_class zx_rates_b.tax_class%TYPE;
1426:
1427: CURSOR get_tax_info_csr IS
1428: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
1429: tax_line_id,
1430: tax_regime_code,
1431: tax_jurisdiction_code,
1432: tax,
1441: tax_id,
1442: tax_jurisdiction_id,
1443: tax_status_id,
1444: tax_rate_id
1445: FROM zx_detail_tax_lines_gt
1446: WHERE ref_doc_application_id IS NOT NULL
1447: AND tax_event_type_code <> 'OVERRIDE_TAX'
1448: AND freeze_until_overridden_flag = 'Y';
1449:
1470: --
1471: IF NVL(p_event_class_rec.enforce_tax_from_ref_doc_flag, 'N') <> 'Y'
1472: THEN
1473: UPDATE
1474: zx_detail_tax_lines_gt LGT
1475: SET LGT.copied_from_other_doc_flag = 'Y',
1476: LGT.other_doc_source = 'REFERENCE'
1477: /* -- comment out the update of the amt until the necessary
1478: -- usage of following column is clearly identified.
1520: l_login_id := fnd_global.login_id;
1521: -- retrieve the non-applicable reference tax lines for current trx
1522: -- for system generated lines, set tax amt and taxable amt to zero;
1523: -- for manually entered tax lines, prorate the tax amt
1524: INSERT INTO zx_detail_tax_lines_gt
1525: ( tax_line_id,
1526: internal_organization_id,
1527: internal_org_location_id,
1528: application_id,
2056: AND L.trx_level_type = G.ref_doc_trx_level_type
2057: AND L.cancel_flag <> 'Y'
2058: AND L.offset_link_to_tax_line_id IS NULL
2059: AND L.mrc_tax_line_flag = 'N'
2060: AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
2061: 'X'
2062: FROM zx_detail_tax_lines_gt T
2063: WHERE T.application_id = G.application_id
2064: AND T.entity_code = G.entity_code
2058: AND L.offset_link_to_tax_line_id IS NULL
2059: AND L.mrc_tax_line_flag = 'N'
2060: AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
2061: 'X'
2062: FROM zx_detail_tax_lines_gt T
2063: WHERE T.application_id = G.application_id
2064: AND T.entity_code = G.entity_code
2065: AND T.event_class_code = G.event_class_code
2066: AND T.trx_id = G.trx_id
2079:
2080: -- retrieve the self assessed and canceled reference tax lines for current trx
2081: -- for system generated lines, set tax amt and taxable amt to zero;
2082: -- for manually entered tax lines, prorate the tax amt
2083: INSERT INTO zx_detail_tax_lines_gt
2084: ( tax_line_id,
2085: internal_organization_id,
2086: internal_org_location_id,
2087: application_id,
2356: L.tax_rate_id,
2357: L.tax_rate_code,
2358: L.tax_rate,
2359: NVL(((SELECT max(ABS(tax_apportionment_line_number))
2360: FROM zx_detail_tax_lines_gt gt1
2361: WHERE gt1.application_id = G.application_id
2362: AND gt1.entity_code = G.entity_code
2363: AND gt1.event_class_code = G.event_class_code
2364: AND gt1.trx_id = G.trx_id
2625: AND L.trx_level_type = G.ref_doc_trx_level_type
2626: AND L.cancel_flag <> 'Y'
2627: AND L.offset_link_to_tax_line_id IS NULL
2628: AND L.mrc_tax_line_flag = 'N'
2629: AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
2630: 'X'
2631: FROM zx_detail_tax_lines_gt T
2632: WHERE T.application_id = G.application_id
2633: AND T.entity_code = G.entity_code
2627: AND L.offset_link_to_tax_line_id IS NULL
2628: AND L.mrc_tax_line_flag = 'N'
2629: AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
2630: 'X'
2631: FROM zx_detail_tax_lines_gt T
2632: WHERE T.application_id = G.application_id
2633: AND T.entity_code = G.entity_code
2634: AND T.event_class_code = G.event_class_code
2635: AND T.trx_id = G.trx_id
2831: END IF; -- bug 7008562
2832: END LOOP;
2833:
2834: FORALL i in 1 .. l_tax_line_id_tab.COUNT
2835: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
2836: zx_detail_tax_lines_gt
2837: SET tax_regime_id = l_tax_regime_id_tab(i),
2838: tax_id = l_tax_id_tab(i),
2839: tax_jurisdiction_id = l_tax_jur_id_tab(i),
2832: END LOOP;
2833:
2834: FORALL i in 1 .. l_tax_line_id_tab.COUNT
2835: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
2836: zx_detail_tax_lines_gt
2837: SET tax_regime_id = l_tax_regime_id_tab(i),
2838: tax_id = l_tax_id_tab(i),
2839: tax_jurisdiction_id = l_tax_jur_id_tab(i),
2840: tax_status_id = l_tax_status_id_tab(i),
2895:
2896: TYPE date_tab_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2897: TYPE num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2898: TYPE var_30_tab_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2899: TYPE var_rate_code_tab_type IS TABLE OF zx_detail_tax_lines_gt.tax_rate_code%TYPE
2900: INDEX BY BINARY_INTEGER;
2901:
2902: l_tax_line_id_tab num_tab_type;
2903: --jur bug
2913: l_tax_status_id_tab num_tab_type;
2914: l_tax_determine_date_tab date_tab_type;
2915:
2916: CURSOR get_tax_info_csr IS
2917: SELECT /*+ ORDERED INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
2918: T.tax_line_id,
2919: T.tax_regime_code,
2920: T.tax_jurisdiction_code,
2921: T.tax,
2921: T.tax,
2922: T.tax_status_code,
2923: T.tax_rate_code,
2924: T.tax_determine_date
2925: FROM zx_detail_tax_lines_gt T,
2926: zx_lines_det_factors G
2927: WHERE
2928: -- commented out for bug fix 5417887
2929: -- G.application_id = p_event_class_rec.application_id
2970: -- Copy the non-applicable manual tax lines from source
2971: -- documnet.
2972: --
2973:
2974: MERGE INTO zx_detail_tax_lines_gt tax_line
2975: USING(
2976: SELECT /*+ index(L ZX_LINES_U1) */ -- Added the hint as part of 6596182
2977: G.internal_organization_id,
2978: G.internal_org_location_id,
4131:
4132: END LOOP;
4133:
4134: FORALL i in 1 .. l_tax_line_id_tab.COUNT
4135: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
4136: zx_detail_tax_lines_gt
4137: SET tax_regime_id = l_tax_regime_id_tab(i),
4138: tax_id = l_tax_id_tab(i),
4139: tax_jurisdiction_id = l_tax_jur_id_tab(i),
4132: END LOOP;
4133:
4134: FORALL i in 1 .. l_tax_line_id_tab.COUNT
4135: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
4136: zx_detail_tax_lines_gt
4137: SET tax_regime_id = l_tax_regime_id_tab(i),
4138: tax_id = l_tax_id_tab(i),
4139: tax_jurisdiction_id = l_tax_jur_id_tab(i),
4140: tax_status_id = l_tax_status_id_tab(i),
4201: ,v.tax_line_id
4202: ,v.remain_line_amt
4203: ,v.orig_tax_amt
4204: ,v.orig_line_amt
4205: FROM zx_detail_tax_lines_gt det
4206: ,(SELECT /*+ INDEX(tax ZX_DETAIL_TAX_LINES_GT_U1) INDEX(cm_tax ZX_LINES_N1) */
4207: tax.tax_amt tax_amt
4208: ,tax.line_amt line_amt
4209: ,org_tax.tax_amt + NVL(SUM(cm_tax.tax_amt), 0) remain_amt
4202: ,v.remain_line_amt
4203: ,v.orig_tax_amt
4204: ,v.orig_line_amt
4205: FROM zx_detail_tax_lines_gt det
4206: ,(SELECT /*+ INDEX(tax ZX_DETAIL_TAX_LINES_GT_U1) INDEX(cm_tax ZX_LINES_N1) */
4207: tax.tax_amt tax_amt
4208: ,tax.line_amt line_amt
4209: ,org_tax.tax_amt + NVL(SUM(cm_tax.tax_amt), 0) remain_amt
4210: ,org_tax.unrounded_tax_amt + NVL(SUM(cm_tax.unrounded_tax_amt), 0) remain_unrounded_amt
4216: ,org_tax.tax_amt orig_tax_amt
4217: ,org_tax.line_amt orig_line_amt
4218: FROM zx_lines org_tax,
4219: zx_lines cm_tax,
4220: zx_detail_tax_lines_gt tax
4221: WHERE
4222: -- commented out for bug fix 5417887
4223: -- tax.application_id = p_event_class_rec.application_id
4224: --AND tax.entity_code = p_event_class_rec.entity_code
4372: END IF;
4373: END LOOP;
4374:
4375: FORALL i IN NVL(l_tax_amt_tbl.FIRST, 0) .. NVL(l_tax_amt_tbl.LAST, -1)
4376: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
4377: zx_detail_tax_lines_gt
4378: SET tax_amt = sign(l_tax_amt_tbl(i))*ABS(l_remain_amt_tbl(i)),
4379: unrounded_tax_amt = sign(l_tax_amt_tbl(i))*ABS(l_remain_unrounded_amt_tbl(i)),
4380: tax_amt_tax_curr = sign(l_tax_amt_tbl(i))*ABS(l_remain_amt_tax_curr_tbl(i)),
4373: END LOOP;
4374:
4375: FORALL i IN NVL(l_tax_amt_tbl.FIRST, 0) .. NVL(l_tax_amt_tbl.LAST, -1)
4376: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
4377: zx_detail_tax_lines_gt
4378: SET tax_amt = sign(l_tax_amt_tbl(i))*ABS(l_remain_amt_tbl(i)),
4379: unrounded_tax_amt = sign(l_tax_amt_tbl(i))*ABS(l_remain_unrounded_amt_tbl(i)),
4380: tax_amt_tax_curr = sign(l_tax_amt_tbl(i))*ABS(l_remain_amt_tax_curr_tbl(i)),
4381: tax_amt_funcl_curr = sign(l_tax_amt_tbl(i))*ABS(l_remain_amt_funcl_curr_tbl(i))
4473:
4474: FORALL l_tax_ln_index IN l_detail_tax_lines_tbl.FIRST ..
4475: l_detail_tax_lines_tbl.LAST
4476:
4477: INSERT INTO zx_detail_tax_lines_gt
4478: VALUES l_detail_tax_lines_tbl(l_tax_ln_index);
4479:
4480:
4481: IF (g_level_procedure >= g_current_runtime_level ) THEN
4529: 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.set_acct_source_tax_rate_id.BEGIN',
4530: 'ZX_TDS_TAX_LINES_DETM_PKG: set_acct_source_tax_rate_id(+)');
4531: END IF;
4532:
4533: UPDATE zx_detail_tax_lines_gt gt1
4534: SET account_source_tax_rate_id =
4535: NVL((SELECT gt2.tax_rate_id
4536: FROM zx_taxes_b ztb,
4537: zx_detail_tax_lines_gt gt2
4533: UPDATE zx_detail_tax_lines_gt gt1
4534: SET account_source_tax_rate_id =
4535: NVL((SELECT gt2.tax_rate_id
4536: FROM zx_taxes_b ztb,
4537: zx_detail_tax_lines_gt gt2
4538: WHERE ztb.tax_id = gt1.tax_id
4539: AND gt2.application_id = gt1.application_id
4540: AND gt2.entity_code = gt1.entity_code
4541: AND gt2.event_class_code = gt1.event_class_code