[Home] [Help]
544: -- NOTE : currently not use
545:
546: PROCEDURE populate_tax_line(
547: p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
548: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
549: p_return_status OUT NOCOPY VARCHAR2,
550: p_error_buffer OUT NOCOPY VARCHAR2)
551: IS
552: BEGIN
620: --
621: -- NOTE : currently not use
622:
623: PROCEDURE populate_orig_columns(
624: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE)
625:
626: IS
627: BEGIN
628:
694: -- CALLED BY
695: -- ZX_TDS_OFFSET_TAX_DETM_PKG
696:
697: PROCEDURE populate_mandatory_columns(
698: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
699: p_return_status OUT NOCOPY VARCHAR2,
700: p_error_buffer OUT NOCOPY VARCHAR2)
701:
702: IS
785: -- CALLED BY
786: -- populate_tax_lines
787:
788: PROCEDURE check_mandatory_columns(
789: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
790: p_return_status OUT NOCOPY VARCHAR2,
791: p_error_buffer OUT NOCOPY VARCHAR2)
792:
793: IS
851: -- CALLED BY
852: -- check_mandatory_columns
853:
854: PROCEDURE check_mandatory_columns_all(
855: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
856: p_return_status OUT NOCOPY VARCHAR2,
857: p_error_buffer OUT NOCOPY VARCHAR2)
858:
859: IS
953: -- check_mandatory_columns
954:
955:
956: PROCEDURE check_non_manual_tax_line(
957: p_tax_line_rec IN OUT NOCOPY ZX_DETAIL_TAX_LINES_GT%ROWTYPE,
958: p_return_status OUT NOCOPY VARCHAR2,
959: p_error_buffer OUT NOCOPY VARCHAR2)
960:
961: IS
1680: END IF;
1681:
1682: p_return_status := FND_API.G_RET_STS_SUCCESS;
1683:
1684: INSERT INTO zx_detail_tax_lines_gt
1685: ( tax_line_id,
1686: internal_organization_id,
1687: application_id,
1688: entity_code,
2303: AND G.line_level_action NOT IN ('SYNCHRONIZE', 'CANCEL', 'NO_CHANGE','DISCARD')
2304: AND L.Cancel_Flag = 'Y'
2305: -- AND (L.tax_provider_id IS NOT NULL OR L.Cancel_Flag = 'Y')
2306: AND NOT EXISTS
2307: (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
2308: 1
2309: FROM zx_detail_tax_lines_gt gt
2310: WHERE gt.application_id = L.application_id
2311: AND gt.entity_code = L.entity_code
2305: -- AND (L.tax_provider_id IS NOT NULL OR L.Cancel_Flag = 'Y')
2306: AND NOT EXISTS
2307: (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
2308: 1
2309: FROM zx_detail_tax_lines_gt gt
2310: WHERE gt.application_id = L.application_id
2311: AND gt.entity_code = L.entity_code
2312: AND gt.event_class_code = L.event_class_code
2313: AND gt.trx_id = L.trx_id
2388:
2389: -- Fetch back canceled tax lines with frozen tax distributions
2390: --
2391:
2392: INSERT INTO zx_detail_tax_lines_gt
2393: ( tax_line_id,
2394: internal_organization_id,
2395: application_id,
2396: entity_code,
3007: AND L.application_id = G.application_id
3008: AND L.associated_child_frozen_flag = 'Y'
3009: AND L.cancel_flag = 'Y'
3010: AND L.tax_apportionment_line_number < 0
3011: AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
3012: 'X'
3013: FROM zx_detail_tax_lines_gt T
3014: WHERE T.trx_id = L.trx_id
3015: AND T.trx_line_id = L.trx_line_id
3009: AND L.cancel_flag = 'Y'
3010: AND L.tax_apportionment_line_number < 0
3011: AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
3012: 'X'
3013: FROM zx_detail_tax_lines_gt T
3014: WHERE T.trx_id = L.trx_id
3015: AND T.trx_line_id = L.trx_line_id
3016: AND T.event_class_code = L.event_class_code
3017: AND T.entity_code = L.entity_code
3024: ELSE
3025:
3026: -- bug 6906427: update tax line id if no grouping criteria change
3027: --
3028: UPDATE zx_detail_tax_lines_gt zlgt
3029: SET (tax_line_id, associated_child_frozen_flag, summary_tax_line_id) =
3030: ( SELECT /*+ INDEX(zl ZX_LINES_U1) */
3031: tax_line_id, associated_child_frozen_flag, summary_tax_line_id
3032: FROM zx_lines zl
3165: 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_frozen_tax_lines',
3166: 'Number of Rows Updated: ' || SQL%ROWCOUNT);
3167: END IF;
3168:
3169: INSERT INTO zx_detail_tax_lines_gt
3170: ( tax_line_id,
3171: internal_organization_id,
3172: application_id,
3173: entity_code,
3492: L.tax_rate_code,
3493: L.tax_rate,
3494: -- bug 6906427
3495: NVL(-((SELECT max(ABS(tax_apportionment_line_number))
3496: FROM zx_detail_tax_lines_gt gt1
3497: WHERE gt1.application_id = L.application_id
3498: AND gt1.entity_code = L.entity_code
3499: AND gt1.event_class_code = L.event_class_code
3500: AND gt1.trx_id = L.trx_id
3800: AND L.associated_child_frozen_flag = 'Y'
3801: AND L.cancel_flag <> 'Y'
3802: -- bug 6906427
3803: AND G.line_level_action = 'UPDATE'
3804: AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
3805: 'X'
3806: FROM zx_detail_tax_lines_gt T
3807: WHERE T.trx_id = L.trx_id
3808: AND T.trx_line_id = L.trx_line_id
3802: -- bug 6906427
3803: AND G.line_level_action = 'UPDATE'
3804: AND NOT EXISTS ( SELECT /*+ INDEX(T ZX_DETAIL_TAX_LINES_GT_U1) */
3805: 'X'
3806: FROM zx_detail_tax_lines_gt T
3807: WHERE T.trx_id = L.trx_id
3808: AND T.trx_line_id = L.trx_line_id
3809: AND T.event_class_code = L.event_class_code
3810: AND T.entity_code = L.entity_code
3879: END IF;
3880:
3881: p_return_status := FND_API.G_RET_STS_SUCCESS;
3882:
3883: INSERT INTO zx_detail_tax_lines_gt
3884: ( tax_line_id,
3885: internal_organization_id,
3886: application_id,
3887: entity_code,
4504: -- L.subscriber_id = G.subscriber_id AND
4505: G.line_level_action IN ('DISCARD', 'UNAPPLY_FROM') AND
4506: L.tax_provider_id IS NULL
4507: AND NOT EXISTS
4508: (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
4509: 1
4510: FROM zx_detail_tax_lines_gt gt
4511: WHERE gt.application_id = L.application_id
4512: AND gt.entity_code = L.entity_code
4506: L.tax_provider_id IS NULL
4507: AND NOT EXISTS
4508: (SELECT /*+ INDEX(gt ZX_DETAIL_TAX_LINES_GT_U1) */
4509: 1
4510: FROM zx_detail_tax_lines_gt gt
4511: WHERE gt.application_id = L.application_id
4512: AND gt.entity_code = L.entity_code
4513: AND gt.event_class_code = L.event_class_code
4514: AND gt.trx_id = L.trx_id
4575: END IF;
4576:
4577: p_return_status := FND_API.G_RET_STS_SUCCESS;
4578:
4579: INSERT INTO zx_detail_tax_lines_gt
4580: ( tax_line_id,
4581: internal_organization_id,
4582: application_id,
4583: entity_code,
5253: l_orig_tax_amt_tax_curr_tbl ORIG_TAX_AMT_TAX_CURR_TBL;
5254:
5255: CURSOR get_tax_tolerance_csr
5256: IS
5257: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5258: tax_line_id,
5259: orig_tax_amt_tax_curr,
5260: tax_amt_tax_curr,
5261: tax_id,
5260: tax_amt_tax_curr,
5261: tax_id,
5262: tax_hold_released_code,
5263: Rounding_Rule_Code
5264: FROM ZX_DETAIL_TAX_LINES_GT
5265: WHERE application_id = p_event_class_rec.application_id
5266: AND entity_code = p_event_class_rec.entity_code
5267: AND event_class_code = p_event_class_rec.event_class_code
5268: AND trx_id = p_event_class_rec.trx_id
5274: -- Start Bugfix: 5617541
5275:
5276: CURSOR get_tax_tol_ctrl_hdr_amt_csr
5277: IS
5278: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5279: tax_line.tax_line_id,
5280: tax_line.orig_tax_amt_tax_curr,
5281: tax_line.tax_amt_tax_curr,
5282: tax_line.tax_id,
5281: tax_line.tax_amt_tax_curr,
5282: tax_line.tax_id,
5283: tax_line.tax_hold_released_code,
5284: tax_line.Rounding_Rule_Code
5285: FROM ZX_DETAIL_TAX_LINES_GT tax_line,
5286: zx_lines_det_factors trx_line
5287: WHERE tax_line.application_id = p_event_class_rec.application_id
5288: AND tax_line.entity_code = p_event_class_rec.entity_code
5289: AND tax_line.event_class_code = p_event_class_rec.event_class_code
5300: AND tax_line.mrc_tax_line_flag = 'N';
5301:
5302: CURSOR get_tax_tol_ctrl_line_amt_csr
5303: IS
5304: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5305: tax_line_id,
5306: orig_tax_amt_tax_curr,
5307: tax_amt_tax_curr,
5308: tax_id,
5307: tax_amt_tax_curr,
5308: tax_id,
5309: tax_hold_released_code,
5310: Rounding_Rule_Code
5311: FROM ZX_DETAIL_TAX_LINES_GT
5312: WHERE application_id = p_event_class_rec.application_id
5313: AND entity_code = p_event_class_rec.entity_code
5314: AND event_class_code = p_event_class_rec.event_class_code
5315: AND trx_id = p_event_class_rec.trx_id
5322:
5323: -- bug 5684123
5324: CURSOR get_tax_tolerance_upd_csr
5325: IS
5326: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5327: tax_line_id,
5328: orig_tax_amt_tax_curr,
5329: tax_amt_tax_curr,
5330: tax_id,
5329: tax_amt_tax_curr,
5330: tax_id,
5331: tax_hold_released_code,
5332: Rounding_Rule_Code
5333: FROM zx_detail_tax_lines_gt gt
5334: WHERE gt.application_id = p_event_class_rec.application_id
5335: AND gt.entity_code = p_event_class_rec.entity_code
5336: AND gt.event_class_code = p_event_class_rec.event_class_code
5337: AND (gt.tax_event_type_code = 'UPDATE' AND
5357: -- bug 6634198
5358: --
5359: CURSOR get_tax_tolerance_import_csr
5360: IS
5361: SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
5362: tax_line_id,
5363: orig_tax_amt_tax_curr,
5364: tax_amt_tax_curr,
5365: tax_id,
5364: tax_amt_tax_curr,
5365: tax_id,
5366: tax_hold_released_code,
5367: Rounding_Rule_Code
5368: FROM zx_detail_tax_lines_gt gt
5369: WHERE gt.application_id = p_event_class_rec.application_id
5370: AND gt.entity_code = p_event_class_rec.entity_code
5371: AND gt.event_class_code = p_event_class_rec.event_class_code
5372: AND (gt.tax_event_type_code = 'CREATE' AND
5562: --
5563: IF (g_level_statement >= g_current_runtime_level ) THEN
5564: FND_LOG.STRING(g_level_statement,
5565: 'ZX.PLSQL.ZX_TDS_TAX_LINES_POPU_PKG.process_tax_tolerance',
5566: 'update ZX_DETAIL_TAX_LINES_GT with tax hold info');
5567: END IF;
5568:
5569: FORALL i IN 1 .. l_count
5570: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5566: 'update ZX_DETAIL_TAX_LINES_GT with tax hold info');
5567: END IF;
5568:
5569: FORALL i IN 1 .. l_count
5570: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5571: ZX_DETAIL_TAX_LINES_GT
5572: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), 0),
5573: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), 0)
5574: WHERE tax_line_id = l_tax_line_id_tbl(i);
5567: END IF;
5568:
5569: FORALL i IN 1 .. l_count
5570: UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
5571: ZX_DETAIL_TAX_LINES_GT
5572: SET tax_hold_code = nvl(l_tax_hold_code_tbl(i), 0),
5573: tax_hold_released_code = nvl(l_tax_hold_released_code_tbl(i), 0)
5574: WHERE tax_line_id = l_tax_line_id_tbl(i);
5575: