[Home] [Help]
874: WHERE SUMMARY_TAX_LINE_ID IN
875: (SELECT summary_tax_line_id
876: FROM ZX_LINES
877: WHERE TAX_LINE_ID IN (SELECT ZD.TAX_LINE_ID
878: FROM ZX_REC_NREC_DIST ZD,
879: ZX_TAX_DIST_ID_GT ZGT
880: WHERE ZD.REC_NREC_TAX_DIST_ID = ZGT.TAX_DIST_ID))
881: AND Associated_Child_Frozen_Flag = 'N';
882:
886:
887: UPDATE ZX_LINES
888: SET SUMMARY_TAX_LINE_ID = NULL
889: WHERE TAX_LINE_ID IN (SELECT ZD.TAX_LINE_ID
890: FROM ZX_REC_NREC_DIST ZD,
891: ZX_TAX_DIST_ID_GT ZGT
892: WHERE ZD.REC_NREC_TAX_DIST_ID = ZGT.TAX_DIST_ID)
893: AND ASSOCIATED_CHILD_FROZEN_FLAG = 'N';
894: --
1200: END Delete_Summary_Lines;
1201:
1202: /* ===========================================================================*
1203: | PROCEDURE Delete_Loose_Tax_Distributions: Deletes tax distributions from |
1204: | ZX_REC_NREC_DIST for given tax line |
1205: * ===========================================================================*/
1206:
1207: PROCEDURE Delete_Loose_Tax_Distributions
1208: (x_return_status OUT NOCOPY VARCHAR2,
1227: x_return_status := FND_API.G_RET_STS_SUCCESS;
1228:
1229: -- rewrote the following query for bug fix 5417887
1230: /*
1231: DELETE FROM ZX_REC_NREC_DIST
1232: WHERE APPLICATION_ID = p_event_class_rec.application_id
1233: AND ENTITY_CODE = p_event_class_rec.entity_code
1234: AND EVENT_CLASS_CODE = p_event_class_rec.event_class_code
1235: AND TRX_ID = p_event_class_rec.trx_id
1241: AND zx_lines.event_class_code = p_event_class_rec.event_class_code
1242: AND zx_lines.trx_id = p_event_class_rec.trx_id);
1243: */
1244:
1245: DELETE FROM ZX_REC_NREC_DIST dist
1246: WHERE EXISTS (
1247: SELECT 1
1248: FROM zx_lines_det_factors
1249: WHERE APPLICATION_ID = dist.application_id
1298: END Delete_Loose_Tax_Distributions;
1299:
1300: /* ===========================================================================*
1301: | PROCEDURE Delete_Tax_Distributions: Deletes old tax distributions from |
1302: | ZX_REC_NREC_DIST when new tax distributions are created in |
1303: | ZX_REC_NREC_DIST_GT |
1304: * ===========================================================================*/
1305:
1306: PROCEDURE Delete_Tax_Distributions(
1299:
1300: /* ===========================================================================*
1301: | PROCEDURE Delete_Tax_Distributions: Deletes old tax distributions from |
1302: | ZX_REC_NREC_DIST when new tax distributions are created in |
1303: | ZX_REC_NREC_DIST_GT |
1304: * ===========================================================================*/
1305:
1306: PROCEDURE Delete_Tax_Distributions(
1307: x_return_status OUT NOCOPY VARCHAR2,
1324:
1325: -- Initialize API return status to success
1326: x_return_status := FND_API.G_RET_STS_SUCCESS;
1327:
1328: DELETE FROM zx_rec_nrec_dist
1329: WHERE application_id = p_event_class_rec.application_id
1330: AND entity_code = p_event_class_rec.entity_code
1331: AND event_class_code = p_event_class_rec.event_class_code
1332: AND trx_id = p_event_class_rec.trx_id
1483: END IF;
1484:
1485: END IF;
1486:
1487: DELETE FROM ZX_REC_NREC_DIST
1488: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
1489: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
1490: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1491: AND TRX_ID = p_event_class_rec.TRX_ID;
1492:
1493: IF (g_level_statement >= g_current_runtime_level ) THEN
1494: FND_LOG.STRING(g_level_statement,
1495: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Delete_Transaction',
1496: 'Number of rows deleted from zx_rec_nrec_dist = '||l_row_count);
1497: END IF;
1498:
1499: IF (g_level_procedure >= g_current_runtime_level ) THEN
1500: FND_LOG.STRING(g_level_procedure,
1574: CURSOR get_max_tax_dist_num_csr(
1575: c_tax_line_id NUMBER,
1576: c_trx_line_dist_id NUMBER) IS
1577: SELECT max(rec_nrec_tax_dist_number)
1578: FROM zx_rec_nrec_dist
1579: WHERE tax_line_id = c_tax_line_id
1580: AND trx_line_dist_id = c_trx_line_dist_id;
1581:
1582: CURSOR get_frozen_tax_dists_csr IS
1585: trx_line_dist_id,
1586: rec_nrec_tax_dist_number,
1587: internal_organization_id,
1588: gl_date
1589: FROM zx_rec_nrec_dist
1590: WHERE freeze_flag = 'Y'
1591: AND NVL(reverse_flag, 'N') = 'N'
1592: AND application_id = p_event_class_rec.application_id
1593: AND entity_code = p_event_class_rec.entity_code
1694:
1695: FORALL i IN NVL(l_rec_nrec_tax_dist_id_tbl.FIRST, 0) ..
1696: NVL(l_rec_nrec_tax_dist_id_tbl.LAST, -1)
1697:
1698: INSERT INTO ZX_REC_NREC_DIST(
1699: REC_NREC_TAX_DIST_ID,
1700: APPLICATION_ID,
1701: ENTITY_CODE,
1702: EVENT_CLASS_CODE,
1864: INTERNAL_ORGANIZATION_ID,
1865: DEF_REC_SETTLEMENT_OPTION_CODE,
1866: TAX_JURISDICTION_ID,
1867: ACCOUNT_SOURCE_TAX_RATE_ID)
1868: SELECT ZX_REC_NREC_DIST_S.NEXTVAL,
1869: APPLICATION_ID,
1870: ENTITY_CODE,
1871: EVENT_CLASS_CODE,
1872: EVENT_TYPE_CODE,
2033: INTERNAL_ORGANIZATION_ID,
2034: DEF_REC_SETTLEMENT_OPTION_CODE,
2035: TAX_JURISDICTION_ID,
2036: ACCOUNT_SOURCE_TAX_RATE_ID
2037: FROM ZX_REC_NREC_DIST
2038: WHERE rec_nrec_tax_dist_id = l_rec_nrec_tax_dist_id_tbl(i);
2039:
2040: l_row_count := SQL%ROWCOUNT;
2041:
2041:
2042: IF (g_level_statement >= g_current_runtime_level ) THEN
2043: FND_LOG.STRING(g_level_statement,
2044: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Cancel_Transaction',
2045: 'Number of rows updated in zx_rec_nrec_dist = '||l_row_count);
2046: END IF;
2047:
2048: FORALL i IN NVL(l_rec_nrec_tax_dist_id_tbl.FIRST, 0) ..
2049: NVL(l_rec_nrec_tax_dist_id_tbl.LAST, -1)
2046: END IF;
2047:
2048: FORALL i IN NVL(l_rec_nrec_tax_dist_id_tbl.FIRST, 0) ..
2049: NVL(l_rec_nrec_tax_dist_id_tbl.LAST, -1)
2050: UPDATE zx_rec_nrec_dist
2051: SET reverse_flag = 'Y'
2052: WHERE rec_nrec_tax_dist_id = l_rec_nrec_tax_dist_id_tbl(i);
2053:
2054: l_row_count := SQL%ROWCOUNT;
2055:
2056: IF (g_level_statement >= g_current_runtime_level ) THEN
2057: FND_LOG.STRING(g_level_statement,
2058: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Cancel_Transaction',
2059: 'Number of rows updated in zx_rec_nrec_dist = '||l_row_count);
2060: END IF;
2061:
2062: END IF; -- _rec_nrec_tax_dist_id_tbl.COUNT > 0
2063:
2188: l_error_buffer VARCHAR2(100);
2189:
2190: CURSOR check_dist IS
2191: SELECT count(*) frozen_rec
2192: FROM ZX_REC_NREC_DIST
2193: WHERE application_id = p_transaction_line_rec.application_id
2194: AND entity_code = p_transaction_line_rec.entity_code
2195: AND event_class_code = p_transaction_line_rec.event_class_code
2196: AND trx_id = p_transaction_line_rec.trx_id
2308: END Mark_Detail_Tax_Lines_Delete;
2309:
2310: /*============================================================================*
2311: | PROCEDURE Create_Tax_Distributions: Inserts distribution lines into |
2312: | ZX_REC_NREC_DIST table. |
2313: *============================================================================*/
2314:
2315: PROCEDURE Create_Tax_Distributions
2316: (x_return_status OUT NOCOPY VARCHAR2) IS
2329:
2330: -- Initialize API return status to success
2331: x_return_status := FND_API.G_RET_STS_SUCCESS;
2332:
2333: INSERT INTO ZX_REC_NREC_DIST (REC_NREC_TAX_DIST_ID,
2334: APPLICATION_ID,
2335: ENTITY_CODE,
2336: EVENT_CLASS_CODE,
2337: EVENT_TYPE_CODE,
2667: INTERNAL_ORGANIZATION_ID,
2668: DEF_REC_SETTLEMENT_OPTION_CODE,
2669: TAX_JURISDICTION_ID,
2670: ACCOUNT_SOURCE_TAX_RATE_ID
2671: FROM ZX_REC_NREC_DIST_GT;
2672:
2673: IF (g_level_statement >= g_current_runtime_level ) THEN
2674: FND_LOG.STRING(g_level_statement,
2675: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Create_Tax_Distributions',
2680: UPDATE zx_lines
2681: SET associated_child_frozen_flag = 'Y'
2682: WHERE tax_line_id IN
2683: (SELECT tax_line_id
2684: FROM zx_rec_nrec_dist_gt
2685: WHERE tax_only_line_flag = 'Y'
2686: );
2687: EXCEPTION
2688: WHEN OTHERS THEN
2735: END Create_Tax_Distributions;
2736:
2737: /*============================================================================*
2738: | Delete_Dist_Marked_For_Delete: Deletes all the tax distributions from |
2739: | ZX_REC_NREC_DIST that are associated with tax lines whose |
2740: | Process_For_Recovery_Flag is 'Y' or Item_Dist_Changed_Flag is 'Y'. |
2741: *============================================================================*/
2742:
2743: PROCEDURE Delete_Dist_Marked_For_Delete
2798: ELSE
2799: IF (g_level_procedure >= g_current_runtime_level ) THEN
2800: FND_LOG.STRING(g_level_procedure,
2801: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Delete_Dist_Marked_For_Delete',
2802: 'Delete from ZX_REC_NREC_DIST (+)');
2803:
2804: FND_LOG.STRING(g_level_procedure,
2805: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Delete_Dist_Marked_For_Delete',
2806: 'Application Id: ' ||p_event_class_rec.application_id);
2820: */
2821: END IF;
2822:
2823:
2824: DELETE FROM ZX_REC_NREC_DIST
2825: WHERE NVL(Reverse_Flag,'N') = 'N'
2826: AND TAX_LINE_ID IN (SELECT TAX_LINE_ID
2827: FROM ZX_LINES L
2828: WHERE APPLICATION_ID = p_event_class_rec.application_id
2854: -- bug6890360: synchronize tax_line_number for reversed tax dustributions,
2855: -- which are not fetched back for processing
2856: --
2857: BEGIN
2858: UPDATE zx_rec_nrec_dist zd
2859: SET tax_line_number =
2860: (SELECT tax_line_number
2861: FROM zx_lines
2862: WHERE tax_line_id = zd.tax_line_id
2895: END;
2896:
2897:
2898: BEGIN
2899: UPDATE zx_rec_nrec_dist zd
2900: SET summary_tax_line_id =
2901: (SELECT summary_tax_line_id
2902: FROM zx_rec_nrec_dist_gt gt
2903: WHERE gt.tax_line_id = zd.tax_line_id
2898: BEGIN
2899: UPDATE zx_rec_nrec_dist zd
2900: SET summary_tax_line_id =
2901: (SELECT summary_tax_line_id
2902: FROM zx_rec_nrec_dist_gt gt
2903: WHERE gt.tax_line_id = zd.tax_line_id
2904: AND gt.trx_line_dist_id = zd.trx_line_dist_id
2905: AND ROWNUM = 1
2906: )
2926: )
2927: )
2928: AND EXISTS
2929: (SELECT summary_tax_line_id
2930: FROM zx_rec_nrec_dist_gt gt
2931: WHERE gt.tax_line_id = zd.tax_line_id
2932: AND gt.trx_line_dist_id = zd.trx_line_dist_id
2933: );
2934:
3041: )
3042: ) nrec_tax_amt_funcl_curr,
3043: 'N' Process_For_Recovery_Flag,
3044: 'N' Item_Dist_Changed_Flag
3045: FROM ZX_REC_NREC_DIST D
3046: WHERE D.tax_line_id = L.tax_line_id
3047: AND D.application_id = L.application_id
3048: AND D.entity_code = L.entity_code
3049: AND D.event_class_code = L.event_class_code
3156:
3157: -- Initialize API return status to success
3158: x_return_status := FND_API.G_RET_STS_SUCCESS;
3159:
3160: /*UPDATE ZX_REC_NREC_DIST
3161: SET Freeze_Flag = 'Y',
3162: event_type_code = p_event_class_rec.event_type_code,
3163: tax_event_type_code = p_event_class_rec.tax_event_type_code
3164: WHERE REC_NREC_TAX_DIST_ID IN (SELECT TAX_DIST_ID
3164: WHERE REC_NREC_TAX_DIST_ID IN (SELECT TAX_DIST_ID
3165: FROM ZX_TAX_DIST_ID_GT);
3166:
3167: */
3168: -- Bug 6335649, In cases where Recovery is 100% Recoverable, in ZX_REC_NREC_DIST we have both REC and NONREC(0 amount)lines.
3169: -- When we call ZX_TRD_INTERNAL_SERVICES_PVT.calc_tax_dist for the second time computation,
3170: -- freeze_flag is checked for both the REC and NONREC lines, popualting the p_rec_nrec_tbl for them.
3171: -- But for the non rec lines, freeze flag is NULL. Below code, updates the freeze flags of the REC/NONREC lines with 0 rec_nrec_rate
3172:
3169: -- When we call ZX_TRD_INTERNAL_SERVICES_PVT.calc_tax_dist for the second time computation,
3170: -- freeze_flag is checked for both the REC and NONREC lines, popualting the p_rec_nrec_tbl for them.
3171: -- But for the non rec lines, freeze flag is NULL. Below code, updates the freeze flags of the REC/NONREC lines with 0 rec_nrec_rate
3172:
3173: UPDATE ZX_REC_NREC_DIST
3174: SET freeze_flag = 'Y',
3175: event_type_code = p_event_class_rec.event_type_code,
3176: tax_event_type_code = p_event_class_rec.tax_event_type_code
3177: WHERE tax_line_id IN (SELECT tax_line_id
3174: SET freeze_flag = 'Y',
3175: event_type_code = p_event_class_rec.event_type_code,
3176: tax_event_type_code = p_event_class_rec.tax_event_type_code
3177: WHERE tax_line_id IN (SELECT tax_line_id
3178: FROM zx_rec_nrec_dist
3179: WHERE rec_nrec_tax_dist_id IN
3180: (SELECT TAX_DIST_ID FROM ZX_TAX_DIST_ID_GT)
3181: )
3182: AND application_id = p_event_class_rec.application_id
3187: event_type_code = p_event_class_rec.event_type_code,
3188: tax_event_type_code = p_event_class_rec.tax_event_type_code,
3189: doc_event_status = p_event_class_rec.doc_status_code
3190: WHERE TAX_LINE_ID IN (SELECT ZD.TAX_LINE_ID
3191: FROM ZX_REC_NREC_DIST ZD ,
3192: ZX_TAX_DIST_ID_GT ZGT
3193: WHERE ZD.REC_NREC_TAX_DIST_ID = ZGT.TAX_DIST_ID);
3194:
3195: -- Bug 6456915: Associated_child_frozen_flag has been removed from grouping columns for summary tax lines
3205: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
3206: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
3207: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
3208: AND TRX_ID IN(SELECT ZD.trx_id
3209: FROM ZX_REC_NREC_DIST ZD ,
3210: ZX_TAX_DIST_ID_GT ZGT
3211: WHERE ZD.REC_NREC_TAX_DIST_ID = ZGT.TAX_DIST_ID);
3212:
3213: IF p_event_class_rec.retain_summ_tax_line_id_flag = 'Y' THEN
3415: CURSOR get_max_tax_dist_num_csr(
3416: c_tax_line_id NUMBER,
3417: c_trx_line_dist_id NUMBER) IS
3418: SELECT max(rec_nrec_tax_dist_number)
3419: FROM zx_rec_nrec_dist
3420: WHERE tax_line_id = c_tax_line_id
3421: AND trx_line_dist_id = c_trx_line_dist_id;
3422:
3423: CURSOR get_tax_dists_csr IS
3426: trx_line_dist_id,
3427: rec_nrec_tax_dist_number,
3428: internal_organization_id,
3429: gl_date
3430: FROM zx_rec_nrec_dist zd
3431: WHERE zd.trx_id = p_event_class_rec.trx_id
3432: AND zd.application_id = p_event_class_rec.application_id
3433: AND zd.entity_code = p_event_class_rec.entity_code
3434: AND zd.event_class_code = p_event_class_rec.event_class_code
3568: END LOOP;
3569:
3570: FORALL i IN l_rec_nrec_tax_dist_id_tbl.FIRST .. l_rec_nrec_tax_dist_id_tbl.LAST
3571:
3572: INSERT INTO ZX_REC_NREC_DIST(
3573: REC_NREC_TAX_DIST_ID,
3574: APPLICATION_ID,
3575: ENTITY_CODE,
3576: EVENT_CLASS_CODE,
3738: INTERNAL_ORGANIZATION_ID,
3739: DEF_REC_SETTLEMENT_OPTION_CODE,
3740: TAX_JURISDICTION_ID,
3741: ACCOUNT_SOURCE_TAX_RATE_ID)
3742: SELECT ZX_REC_NREC_DIST_S.NEXTVAL, -- REC_NREC_TAX_DIST_ID,
3743: ZD.APPLICATION_ID,
3744: ZD.ENTITY_CODE,
3745: ZD.EVENT_CLASS_CODE,
3746: ZD.EVENT_TYPE_CODE,
3907: ZD.INTERNAL_ORGANIZATION_ID,
3908: ZD.DEF_REC_SETTLEMENT_OPTION_CODE,
3909: ZD.TAX_JURISDICTION_ID,
3910: ZD.ACCOUNT_SOURCE_TAX_RATE_ID
3911: FROM ZX_REC_NREC_DIST ZD
3912: WHERE rec_nrec_tax_dist_id = l_rec_nrec_tax_dist_id_tbl(i);
3913:
3914: l_row_count := SQL%ROWCOUNT;
3915:
3915:
3916: IF (g_level_statement >= g_current_runtime_level ) THEN
3917: FND_LOG.STRING(g_level_statement,
3918: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Cancel_Transaction',
3919: 'Number of rows updated in zx_rec_nrec_dist = '||l_row_count);
3920: END IF;
3921:
3922: FORALL i IN NVL(l_rec_nrec_tax_dist_id_tbl.FIRST, 0) ..
3923: NVL(l_rec_nrec_tax_dist_id_tbl.LAST, -1)
3920: END IF;
3921:
3922: FORALL i IN NVL(l_rec_nrec_tax_dist_id_tbl.FIRST, 0) ..
3923: NVL(l_rec_nrec_tax_dist_id_tbl.LAST, -1)
3924: UPDATE zx_rec_nrec_dist
3925: SET reverse_flag = 'Y'
3926: WHERE rec_nrec_tax_dist_id = l_rec_nrec_tax_dist_id_tbl(i);
3927:
3928: l_row_count := SQL%ROWCOUNT;
3929:
3930: IF (g_level_statement >= g_current_runtime_level ) THEN
3931: FND_LOG.STRING(g_level_statement,
3932: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Cancel_Transaction',
3933: 'Number of rows updated in zx_rec_nrec_dist = '||l_row_count);
3934: END IF;
3935:
3936: END IF; -- _rec_nrec_tax_dist_id_tbl.COUNT > 0
3937:
3938: /*
3939: * IF (g_level_procedure >= g_current_runtime_level ) THEN
3940: * FND_LOG.STRING(g_level_procedure,
3941: * 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Discard_Tax_Only_Lines',
3942: * 'Update ZX_REC_NREC_DIST set reverse flag to Y (+)');
3943: * END IF;
3944: *
3945: * UPDATE ZX_REC_NREC_DIST
3946: * SET Reverse_Flag = 'Y'
3941: * 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Discard_Tax_Only_Lines',
3942: * 'Update ZX_REC_NREC_DIST set reverse flag to Y (+)');
3943: * END IF;
3944: *
3945: * UPDATE ZX_REC_NREC_DIST
3946: * SET Reverse_Flag = 'Y'
3947: * WHERE APPLICATION_ID = p_event_class_rec.application_id
3948: * AND ENTITY_CODE = p_event_class_rec.entity_code
3949: * AND EVENT_CLASS_CODE = p_event_class_rec.event_class_code
3953: *
3954: * IF (g_level_procedure >= g_current_runtime_level ) THEN
3955: * FND_LOG.STRING(g_level_procedure,
3956: * 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Discard_Tax_Only_Lines',
3957: * 'Update ZX_REC_NREC_DIST set reverse flag to Y (-)');
3958: * END IF;
3959: */
3960:
3961: IF (g_level_procedure >= g_current_runtime_level ) THEN
4001:
4002: -- Initialize API return status to success
4003: x_return_status := FND_API.G_RET_STS_SUCCESS;
4004:
4005: UPDATE zx_rec_nrec_dist
4006: SET gl_date = p_gl_date
4007: WHERE rec_nrec_tax_dist_id IN
4008: (SELECT tax_dist_id FROM zx_tax_dist_id_gt);
4009:
4067: SUM(decode(Recoverable_Flag,
4068: 'N',
4069: rec_nrec_tax_amt_funcl_curr)
4070: ) nrec_tax_amt_funcl_curr
4071: FROM ZX_REC_NREC_DIST D
4072: WHERE L.tax_line_id = D.tax_line_id
4073: AND L.application_id = D.application_id
4074: AND L.entity_code = D.entity_code
4075: AND L.event_class_code = D.event_class_code