[Home] [Help]
549: -- NOTE : currently not use
550:
551: PROCEDURE populate_tax_line(
552: p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
553: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
554: p_return_status OUT NOCOPY VARCHAR2,
555: p_error_buffer OUT NOCOPY VARCHAR2)
556: IS
557: BEGIN
625: --
626: -- NOTE : currently not use
627:
628: PROCEDURE populate_orig_columns(
629: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE)
630:
631: IS
632: BEGIN
633:
699: -- CALLED BY
700: -- ZX_TDS_OFFSET_TAX_DETM_PKG
701:
702: PROCEDURE populate_mandatory_columns(
703: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
704: p_return_status OUT NOCOPY VARCHAR2,
705: p_error_buffer OUT NOCOPY VARCHAR2)
706:
707: IS
790: -- CALLED BY
791: -- populate_tax_lines
792:
793: PROCEDURE check_mandatory_columns(
794: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
795: p_return_status OUT NOCOPY VARCHAR2,
796: p_error_buffer OUT NOCOPY VARCHAR2)
797:
798: IS
856: -- CALLED BY
857: -- check_mandatory_columns
858:
859: PROCEDURE check_mandatory_columns_all(
860: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
861: p_return_status OUT NOCOPY VARCHAR2,
862: p_error_buffer OUT NOCOPY VARCHAR2)
863:
864: IS
958: -- check_mandatory_columns
959:
960:
961: PROCEDURE check_non_manual_tax_line(
962: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
963: p_return_status OUT NOCOPY VARCHAR2,
964: p_error_buffer OUT NOCOPY VARCHAR2)
965:
966: IS
1741:
1742: p_return_status := FND_API.G_RET_STS_SUCCESS;
1743:
1744: -- bug 8470599 forcing the driving table in select clause from zx_lines_det_factors
1745: INSERT INTO zx_detail_tax_lines_gt
1746: ( tax_line_id,
1747: internal_organization_id,
1748: application_id,
1749: entity_code,
2364: AND G.line_level_action NOT IN ('SYNCHRONIZE', 'CANCEL', 'NO_CHANGE','DISCARD')
2365: AND L.Cancel_Flag = 'Y'
2366: -- AND (L.tax_provider_id IS NOT NULL OR L.Cancel_Flag = 'Y')
2367: AND NOT EXISTS
2368: (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
2369: 1
2370: FROM zx_detail_tax_lines_gt gt
2371: WHERE gt.application_id = L.application_id
2372: AND gt.entity_code = L.entity_code
2366: -- AND (L.tax_provider_id IS NOT NULL OR L.Cancel_Flag = 'Y')
2367: AND NOT EXISTS
2368: (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
2369: 1
2370: FROM zx_detail_tax_lines_gt gt
2371: WHERE gt.application_id = L.application_id
2372: AND gt.entity_code = L.entity_code
2373: AND gt.event_class_code = L.event_class_code
2374: AND gt.trx_id = L.trx_id
2450: -- Fetch back canceled tax lines with frozen tax distributions
2451: --
2452:
2453: -- bug 8470599 forcing the driving table in select clause from zx_lines_det_factors
2454: INSERT INTO zx_detail_tax_lines_gt
2455: ( tax_line_id,
2456: internal_organization_id,
2457: application_id,
2458: entity_code,
3070: AND L.associated_child_frozen_flag = 'Y'
3071: AND L.cancel_flag = 'Y'
3072: AND L.tax_apportionment_line_number < 0
3073: AND NOT EXISTS ( SELECT 1
3074: FROM zx_detail_tax_lines_gt T
3075: WHERE T.tax_line_id = L.tax_line_id
3076: -- bug 8470599 these columns are redundant and are doing index range scan on ZX_DETAIL_TAX_LINES_GT_U1
3077: -- AND T.trx_id = L.trx_id
3078: -- AND T.trx_line_id = L.trx_line_id
3072: AND L.tax_apportionment_line_number < 0
3073: AND NOT EXISTS ( SELECT 1
3074: FROM zx_detail_tax_lines_gt T
3075: WHERE T.tax_line_id = L.tax_line_id
3076: -- bug 8470599 these columns are redundant and are doing index range scan on ZX_DETAIL_TAX_LINES_GT_U1
3077: -- AND T.trx_id = L.trx_id
3078: -- AND T.trx_line_id = L.trx_line_id
3079: -- AND T.event_class_code = L.event_class_code
3080: -- AND T.entity_code = L.entity_code
3088: -- bug 6906427: update tax line id if no grouping criteria change
3089: --
3090: --bug8517610
3091:
3092: UPDATE zx_detail_tax_lines_gt zlgt
3093: SET (tax_line_id, associated_child_frozen_flag, summary_tax_line_id,offset_link_to_tax_line_id) =
3094: ( SELECT /*+ INDEX(zl ZX_LINES_U1) */
3095: tax_line_id, associated_child_frozen_flag, summary_tax_line_id,offset_link_to_tax_line_id
3096: FROM zx_lines zl
3231: 'Number of Rows Updated: ' || SQL%ROWCOUNT);
3232: END IF;
3233:
3234: -- bug 8470599 forcing the driving table in select clause from zx_lines_det_factors
3235: INSERT INTO zx_detail_tax_lines_gt
3236: ( tax_line_id,
3237: internal_organization_id,
3238: application_id,
3239: entity_code,
3558: L.tax_rate_code,
3559: L.tax_rate,
3560: -- bug 6906427
3561: NVL(-((SELECT max(ABS(tax_apportionment_line_number))
3562: FROM zx_detail_tax_lines_gt gt1
3563: WHERE gt1.application_id = L.application_id
3564: AND gt1.entity_code = L.entity_code
3565: AND gt1.event_class_code = L.event_class_code
3566: AND gt1.trx_id = L.trx_id
3868: AND L.cancel_flag <> 'Y'
3869: -- bug 6906427
3870: AND G.line_level_action = 'UPDATE'
3871: AND NOT EXISTS ( SELECT 1
3872: FROM zx_detail_tax_lines_gt T
3873: WHERE T.tax_line_id = L.tax_line_id
3874: -- bug 8470599 these columns are redundant and are doing index range scan on ZX_DETAIL_TAX_LINES_GT_U1
3875: -- AND T.trx_id = L.trx_id
3876: -- AND T.trx_line_id = L.trx_line_id
3870: AND G.line_level_action = 'UPDATE'
3871: AND NOT EXISTS ( SELECT 1
3872: FROM zx_detail_tax_lines_gt T
3873: WHERE T.tax_line_id = L.tax_line_id
3874: -- bug 8470599 these columns are redundant and are doing index range scan on ZX_DETAIL_TAX_LINES_GT_U1
3875: -- AND T.trx_id = L.trx_id
3876: -- AND T.trx_line_id = L.trx_line_id
3877: -- AND T.event_class_code = L.event_class_code
3878: -- AND T.entity_code = L.entity_code
3947:
3948: p_return_status := FND_API.G_RET_STS_SUCCESS;
3949:
3950: -- bug 8470599 forcing the driving table in select clause from zx_lines_det_factors
3951: INSERT INTO zx_detail_tax_lines_gt
3952: ( tax_line_id,
3953: internal_organization_id,
3954: application_id,
3955: entity_code,
4573: -- L.subscriber_id = G.subscriber_id AND
4574: G.line_level_action IN ('DISCARD', 'UNAPPLY_FROM') AND
4575: L.tax_provider_id IS NULL
4576: AND NOT EXISTS
4577: (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
4578: 1
4579: FROM zx_detail_tax_lines_gt gt
4580: WHERE gt.application_id = L.application_id
4581: AND gt.entity_code = L.entity_code
4575: L.tax_provider_id IS NULL
4576: AND NOT EXISTS
4577: (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
4578: 1
4579: FROM zx_detail_tax_lines_gt gt
4580: WHERE gt.application_id = L.application_id
4581: AND gt.entity_code = L.entity_code
4582: AND gt.event_class_code = L.event_class_code
4583: AND gt.trx_id = L.trx_id
4648:
4649: p_return_status := FND_API.G_RET_STS_SUCCESS;
4650:
4651: -- bug 8470599 forcing the driving table in select clause from zx_lines_det_factors
4652: INSERT INTO zx_detail_tax_lines_gt
4653: ( tax_line_id,
4654: internal_organization_id,
4655: application_id,
4656: entity_code,
5327: l_orig_tax_amt_tax_curr_tbl ORIG_TAX_AMT_TAX_CURR_TBL;
5328:
5329: CURSOR get_tax_tolerance_csr
5330: IS
5331: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5332: tax_line_id,
5333: orig_tax_amt_tax_curr,
5334: tax_amt_tax_curr,
5335: tax_id,
5334: tax_amt_tax_curr,
5335: tax_id,
5336: tax_hold_released_code,
5337: Rounding_Rule_Code
5338: FROM ZX_DETAIL_TAX_LINES_GT
5339: WHERE application_id = p_event_class_rec.application_id
5340: AND entity_code = p_event_class_rec.entity_code
5341: AND event_class_code = p_event_class_rec.event_class_code
5342: AND trx_id = p_event_class_rec.trx_id
5347:
5348: -- Start Bugfix: 5617541
5349: CURSOR get_tax_tol_ctrl_hdr_amt_csr
5350: IS
5351: SELECT /*+ INDEX(tax_line ZX_DETAIL_TAX_LINES_GT_U1) */
5352: tax_line.tax_line_id,
5353: tax_line.orig_tax_amt_tax_curr,
5354: tax_line.tax_amt_tax_curr,
5355: tax_line.tax_id,
5354: tax_line.tax_amt_tax_curr,
5355: tax_line.tax_id,
5356: tax_line.tax_hold_released_code,
5357: tax_line.Rounding_Rule_Code
5358: FROM ZX_DETAIL_TAX_LINES_GT tax_line,
5359: zx_lines_det_factors trx_line
5360: WHERE tax_line.application_id = p_event_class_rec.application_id
5361: AND tax_line.entity_code = p_event_class_rec.entity_code
5362: AND tax_line.event_class_code = p_event_class_rec.event_class_code
5373: AND tax_line.mrc_tax_line_flag = 'N';
5374:
5375: CURSOR get_tax_tol_ctrl_line_amt_csr
5376: IS
5377: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5378: tax_line_id,
5379: orig_tax_amt_tax_curr,
5380: tax_amt_tax_curr,
5381: tax_id,
5380: tax_amt_tax_curr,
5381: tax_id,
5382: tax_hold_released_code,
5383: Rounding_Rule_Code
5384: FROM ZX_DETAIL_TAX_LINES_GT
5385: WHERE application_id = p_event_class_rec.application_id
5386: AND entity_code = p_event_class_rec.entity_code
5387: AND event_class_code = p_event_class_rec.event_class_code
5388: AND trx_id = p_event_class_rec.trx_id
5394:
5395: -- bug 5684123
5396: CURSOR get_tax_tolerance_upd_csr
5397: IS
5398: SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
5399: tax_line_id,
5400: orig_tax_amt_tax_curr,
5401: tax_amt_tax_curr,
5402: tax_id,
5401: tax_amt_tax_curr,
5402: tax_id,
5403: tax_hold_released_code,
5404: Rounding_Rule_Code
5405: FROM zx_detail_tax_lines_gt gt
5406: WHERE gt.application_id = p_event_class_rec.application_id
5407: AND gt.entity_code = p_event_class_rec.entity_code
5408: AND gt.event_class_code = p_event_class_rec.event_class_code
5409: AND (gt.tax_event_type_code = 'UPDATE' AND
5429: -- bug 6634198
5430: --
5431: CURSOR get_tax_tolerance_import_csr
5432: IS
5433: SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
5434: tax_line_id,
5435: orig_tax_amt_tax_curr,
5436: tax_amt_tax_curr,
5437: tax_id,
5436: tax_amt_tax_curr,
5437: tax_id,
5438: tax_hold_released_code,
5439: Rounding_Rule_Code
5440: FROM zx_detail_tax_lines_gt gt
5441: WHERE gt.application_id = p_event_class_rec.application_id
5442: AND gt.entity_code = p_event_class_rec.entity_code
5443: AND gt.event_class_code = p_event_class_rec.event_class_code
5444: AND (gt.tax_event_type_code = 'CREATE' AND
5585: --
5586: IF (g_level_statement >= g_current_runtime_level) THEN
5587: FND_LOG.STRING(g_level_statement,
5588: 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5589: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - OVERRIDE_TAX');
5590: END IF;
5591:
5592: FORALL i IN 1..l_count
5593: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5589: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - OVERRIDE_TAX');
5590: END IF;
5591:
5592: FORALL i IN 1..l_count
5593: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5594: ZX_DETAIL_TAX_LINES_GT
5595: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i),tax_hold_code),
5596: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
5597: WHERE tax_line_id = l_tax_line_id_tbl(i);
5590: END IF;
5591:
5592: FORALL i IN 1..l_count
5593: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5594: ZX_DETAIL_TAX_LINES_GT
5595: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i),tax_hold_code),
5596: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
5597: WHERE tax_line_id = l_tax_line_id_tbl(i);
5598:
5714: --
5715: IF (g_level_statement >= g_current_runtime_level ) THEN
5716: FND_LOG.STRING(g_level_statement,
5717: 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5718: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - Control Line Amount');
5719: END IF;
5720:
5721: FORALL i IN 1..l_count
5722: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5718: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - Control Line Amount');
5719: END IF;
5720:
5721: FORALL i IN 1..l_count
5722: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5723: ZX_DETAIL_TAX_LINES_GT
5724: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
5725: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
5726: WHERE tax_line_id = l_tax_line_id_tbl(i);
5719: END IF;
5720:
5721: FORALL i IN 1..l_count
5722: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5723: ZX_DETAIL_TAX_LINES_GT
5724: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
5725: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
5726: WHERE tax_line_id = l_tax_line_id_tbl(i);
5727:
5843: --
5844: IF (g_level_statement >= g_current_runtime_level ) THEN
5845: FND_LOG.STRING(g_level_statement,
5846: 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5847: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - Control Header Amount');
5848: END IF;
5849:
5850: FORALL i IN 1..l_count
5851: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5847: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - Control Header Amount');
5848: END IF;
5849:
5850: FORALL i IN 1..l_count
5851: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5852: ZX_DETAIL_TAX_LINES_GT
5853: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
5854: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
5855: WHERE tax_line_id = l_tax_line_id_tbl(i);
5848: END IF;
5849:
5850: FORALL i IN 1..l_count
5851: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5852: ZX_DETAIL_TAX_LINES_GT
5853: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
5854: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
5855: WHERE tax_line_id = l_tax_line_id_tbl(i);
5856:
5972: --
5973: IF (g_level_statement >= g_current_runtime_level ) THEN
5974: FND_LOG.STRING(g_level_statement,
5975: 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5976: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - UPDATE event');
5977: END IF;
5978:
5979: FORALL i IN 1..l_count
5980: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5976: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - UPDATE event');
5977: END IF;
5978:
5979: FORALL i IN 1..l_count
5980: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5981: ZX_DETAIL_TAX_LINES_GT
5982: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
5983: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
5984: WHERE tax_line_id = l_tax_line_id_tbl(i);
5977: END IF;
5978:
5979: FORALL i IN 1..l_count
5980: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5981: ZX_DETAIL_TAX_LINES_GT
5982: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
5983: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
5984: WHERE tax_line_id = l_tax_line_id_tbl(i);
5985:
6101: --
6102: IF (g_level_statement >= g_current_runtime_level) THEN
6103: FND_LOG.STRING(g_level_statement,
6104: 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
6105: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - CREATE event');
6106: END IF;
6107:
6108: FORALL i IN 1..l_count
6109: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
6105: 'Update ZX_DETAIL_TAX_LINES_GT with Tax Hold Info - CREATE event');
6106: END IF;
6107:
6108: FORALL i IN 1..l_count
6109: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
6110: ZX_DETAIL_TAX_LINES_GT
6111: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
6112: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
6113: WHERE tax_line_id = l_tax_line_id_tbl(i);
6106: END IF;
6107:
6108: FORALL i IN 1..l_count
6109: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
6110: ZX_DETAIL_TAX_LINES_GT
6111: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), tax_hold_code),
6112: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), tax_hold_released_code)
6113: WHERE tax_line_id = l_tax_line_id_tbl(i);
6114: