[Home] [Help]
116: pg_trx_line_id_tab num_tbl_type;
117: pg_trx_level_type_tab char30_tbl_type;
118:
119: /* ===========================================================================*
120: | PROCEDURE Create_Detail_Lines : Insert tax lines into ZX_LINES table. |
121: * ===========================================================================*/
122:
123: PROCEDURE Create_Detail_Lines (
124: p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
160: l_msg_context_info_rec.summary_tax_line_number := NULL;
161: l_msg_context_info_rec.tax_line_id := NULL;
162: l_msg_context_info_rec.trx_line_dist_id := NULL;
163:
164: INSERT INTO ZX_LINES (TAX_LINE_ID,
165: INTERNAL_ORGANIZATION_ID,
166: APPLICATION_ID,
167: ENTITY_CODE,
168: EVENT_CLASS_CODE,
862:
863: -- Set summary_tax_line id to Null for unfrozen tax lines who share the same
864: -- summary tax_line id as the one who has associated children frozen flag as 'Y'.
865: --
866: -- set SUMMARY_TAX_LINE_ID to NULL in ZX_LINES for
867: -- those tax lines have Associated_Child_Frozen_Flag = 'N'
868: -- belonged to the same summary line
869: --
870:
868: -- belonged to the same summary line
869: --
870:
871: -- Performance Bug 5908610 - Query has been modified
872: /* UPDATE ZX_LINES
873: SET SUMMARY_TAX_LINE_ID = NULL
874: WHERE SUMMARY_TAX_LINE_ID IN
875: (SELECT summary_tax_line_id
876: FROM ZX_LINES
872: /* UPDATE ZX_LINES
873: SET SUMMARY_TAX_LINE_ID = NULL
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))
883: */
884:
885: -- NEW query for the above
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
946: END IF;
947: END Summarization_For_Freeze_Event;
948:
949: /*============================================================================*
950: | PROCEDURE Delete_Detail_TaxLines: Deletes the transaction from ZX_LINES for|
951: | given transaction details |
952: *============================================================================*/
953:
954: PROCEDURE Delete_Detail_Lines
977: -- IF p_event_class_rec.tax_event_type_code = 'UPDATE' THEN
978: IF ZX_GLOBAL_STRUCTURES_PKG.g_update_event_process_flag = 'Y' THEN
979:
980: /* -- rewrote for bug fix 5417887
981: DELETE FROM ZX_LINES
982: WHERE APPLICATION_ID = p_event_class_rec.application_id
983: AND ENTITY_CODE = p_event_class_rec.entity_code
984: AND EVENT_CLASS_CODE = p_event_class_rec.event_class_code
985: AND TRX_ID = p_event_class_rec.trx_id
985: AND TRX_ID = p_event_class_rec.trx_id
986: AND (DELETE_FLAG = 'Y' OR
987: (TRX_LINE_ID, trx_level_type) IN
988: (SELECT TRX_LINE_ID, trx_level_type
989: FROM zx_lines_det_factors
990: WHERE application_id = p_event_class_rec.application_id
991: AND entity_code = p_event_class_rec.entity_code
992: AND event_class_code = p_event_class_rec.event_class_code
993: AND trx_id = p_event_class_rec.trx_id
995: AND line_level_action NOT IN ('SYNCHRONIZE', 'NO_CHANGE')
996: )
997: ); */
998: -- bug fix 5417887
999: DELETE FROM ZX_LINES tax
1000: WHERE EXISTS (SELECT 1
1001: FROM zx_lines_det_factors
1002: WHERE application_id = tax.application_id
1003: AND entity_code = tax.entity_code
997: ); */
998: -- bug fix 5417887
999: DELETE FROM ZX_LINES tax
1000: WHERE EXISTS (SELECT 1
1001: FROM zx_lines_det_factors
1002: WHERE application_id = tax.application_id
1003: AND entity_code = tax.entity_code
1004: AND event_class_code = tax.event_class_code
1005: AND trx_id = tax.trx_id
1007: )
1008: AND (tax.DELETE_FLAG = 'Y' OR
1009: (tax.TRX_LINE_ID, tax.trx_level_type) IN
1010: (SELECT TRX_LINE_ID, trx_level_type
1011: FROM zx_lines_det_factors
1012: WHERE application_id = tax.application_id
1013: AND entity_code = tax.entity_code
1014: AND event_class_code = tax.event_class_code
1015: AND trx_id = tax.trx_id
1021: l_row_count := SQL%ROWCOUNT;
1022:
1023: ELSIF p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX' THEN
1024:
1025: DELETE FROM zx_lines
1026: WHERE application_id = p_event_class_rec.application_id
1027: AND entity_code = p_event_class_rec.entity_code
1028: AND event_class_code = p_event_class_rec.event_class_code
1029: AND trx_id = p_event_class_rec.trx_id
1043:
1044: IF p_event_class_rec.allow_offset_tax_calc_flag ='Y' THEN
1045: -- delete old offset tax lines
1046: --
1047: DELETE FROM zx_lines
1048: WHERE application_id = p_event_class_rec.application_id
1049: AND entity_code = p_event_class_rec.entity_code
1050: AND event_class_code = p_event_class_rec.event_class_code
1051: AND trx_id = p_event_class_rec.trx_id
1067: --
1068: -- Delete old MRC related detail tax lines
1069: --
1070: IF p_event_class_rec.enable_mrc_flag = 'Y' THEN
1071: DELETE FROM zx_lines zl
1072: WHERE application_id = p_event_class_rec.application_id
1073: AND entity_code = p_event_class_rec.entity_code
1074: AND event_class_code = p_event_class_rec.event_class_code
1075: AND trx_id = p_event_class_rec.trx_id
1118: END Delete_Detail_Lines;
1119:
1120: /* ===========================================================================*
1121: | PROCEDURE Delete_Summary_TaxLines: Deletes the transaction from |
1122: | ZX_LINES_SUMMARY for a given transaction details |
1123: * ===========================================================================*/
1124: PROCEDURE Delete_Summary_Lines
1125: (x_return_status OUT NOCOPY VARCHAR2,
1126: p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE) IS
1144: x_return_status := FND_API.G_RET_STS_SUCCESS;
1145:
1146: -- rewrote the following query for bug fix 5417887
1147: /*
1148: DELETE FROM ZX_LINES_SUMMARY
1149: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
1150: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
1151: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1152: AND TRX_ID = p_event_class_rec.TRX_ID;
1151: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1152: AND TRX_ID = p_event_class_rec.TRX_ID;
1153: */
1154:
1155: DELETE FROM zx_lines_summary tax
1156: WHERE EXISTS (SELECT 1
1157: FROM zx_lines_det_factors line
1158: WHERE tax.application_id = line.application_id
1159: AND tax.event_class_code = line.event_class_code
1153: */
1154:
1155: DELETE FROM zx_lines_summary tax
1156: WHERE EXISTS (SELECT 1
1157: FROM zx_lines_det_factors line
1158: WHERE tax.application_id = line.application_id
1159: AND tax.event_class_code = line.event_class_code
1160: AND tax.entity_code = line.entity_code
1161: AND tax.trx_id = line.trx_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
1236: AND TAX_LINE_ID NOT IN
1237: (SELECT ZX_LINES.TAX_LINE_ID
1238: FROM ZX_LINES
1239: WHERE zx_lines.application_id = p_event_class_rec.application_id
1240: AND zx_lines.entity_code = p_event_class_rec.entity_code
1241: AND zx_lines.event_class_code = p_event_class_rec.event_class_code
1234: AND EVENT_CLASS_CODE = p_event_class_rec.event_class_code
1235: AND TRX_ID = p_event_class_rec.trx_id
1236: AND TAX_LINE_ID NOT IN
1237: (SELECT ZX_LINES.TAX_LINE_ID
1238: FROM ZX_LINES
1239: WHERE zx_lines.application_id = p_event_class_rec.application_id
1240: AND zx_lines.entity_code = p_event_class_rec.entity_code
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);
1235: AND TRX_ID = p_event_class_rec.trx_id
1236: AND TAX_LINE_ID NOT IN
1237: (SELECT ZX_LINES.TAX_LINE_ID
1238: FROM ZX_LINES
1239: WHERE zx_lines.application_id = p_event_class_rec.application_id
1240: AND zx_lines.entity_code = p_event_class_rec.entity_code
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: */
1236: AND TAX_LINE_ID NOT IN
1237: (SELECT ZX_LINES.TAX_LINE_ID
1238: FROM ZX_LINES
1239: WHERE zx_lines.application_id = p_event_class_rec.application_id
1240: AND zx_lines.entity_code = p_event_class_rec.entity_code
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:
1237: (SELECT ZX_LINES.TAX_LINE_ID
1238: FROM ZX_LINES
1239: WHERE zx_lines.application_id = p_event_class_rec.application_id
1240: AND zx_lines.entity_code = p_event_class_rec.entity_code
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
1238: FROM ZX_LINES
1239: WHERE zx_lines.application_id = p_event_class_rec.application_id
1240: AND zx_lines.entity_code = p_event_class_rec.entity_code
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 (
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
1250: AND ENTITY_CODE = dist.entity_code
1251: AND EVENT_CLASS_CODE = dist.event_class_code
1252: AND TRX_ID = dist.trx_id
1252: AND TRX_ID = dist.trx_id
1253: AND EVENT_ID = p_event_class_rec.event_id )
1254: AND TAX_LINE_ID NOT IN
1255: (SELECT TAX_LINE_ID
1256: FROM ZX_LINES zx_lines
1257: WHERE zx_lines.application_id = dist.application_id
1258: AND zx_lines.entity_code = dist.entity_code
1259: AND zx_lines.event_class_code = dist.event_class_code
1260: AND zx_lines.trx_id = dist.trx_id);
1253: AND EVENT_ID = p_event_class_rec.event_id )
1254: AND TAX_LINE_ID NOT IN
1255: (SELECT TAX_LINE_ID
1256: FROM ZX_LINES zx_lines
1257: WHERE zx_lines.application_id = dist.application_id
1258: AND zx_lines.entity_code = dist.entity_code
1259: AND zx_lines.event_class_code = dist.event_class_code
1260: AND zx_lines.trx_id = dist.trx_id);
1261:
1254: AND TAX_LINE_ID NOT IN
1255: (SELECT TAX_LINE_ID
1256: FROM ZX_LINES zx_lines
1257: WHERE zx_lines.application_id = dist.application_id
1258: AND zx_lines.entity_code = dist.entity_code
1259: AND zx_lines.event_class_code = dist.event_class_code
1260: AND zx_lines.trx_id = dist.trx_id);
1261:
1262: l_row_count := SQL%ROWCOUNT;
1255: (SELECT TAX_LINE_ID
1256: FROM ZX_LINES zx_lines
1257: WHERE zx_lines.application_id = dist.application_id
1258: AND zx_lines.entity_code = dist.entity_code
1259: AND zx_lines.event_class_code = dist.event_class_code
1260: AND zx_lines.trx_id = dist.trx_id);
1261:
1262: l_row_count := SQL%ROWCOUNT;
1263:
1256: FROM ZX_LINES zx_lines
1257: WHERE zx_lines.application_id = dist.application_id
1258: AND zx_lines.entity_code = dist.entity_code
1259: AND zx_lines.event_class_code = dist.event_class_code
1260: AND zx_lines.trx_id = dist.trx_id);
1261:
1262: l_row_count := SQL%ROWCOUNT;
1263:
1264:
1333: AND NVL(freeze_flag, 'N') = 'N'
1334: AND NVL(reverse_flag, 'N') = 'N'
1335: AND tax_line_id IN
1336: (SELECT tax_line_id
1337: FROM zx_lines
1338: WHERE trx_id = p_event_class_rec.trx_id
1339: AND application_id = p_event_class_rec.application_id
1340: AND entity_code = p_event_class_rec.entity_code
1341: AND event_class_code = p_event_class_rec.event_class_code
1397:
1398: -- This cursor will check if any tax lines has frozen distributions
1399: CURSOR check_frozen_child IS
1400: SELECT TAX_LINE_ID
1401: FROM ZX_LINES
1402: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
1403: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
1404: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1405: AND TRX_ID = p_event_class_rec.TRX_ID
1404: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1405: AND TRX_ID = p_event_class_rec.TRX_ID
1406: AND Associated_Child_Frozen_Flag = 'Y';
1407:
1408: l_tax_line_id ZX_LINES.TAX_LINE_ID%TYPE;
1409:
1410: BEGIN
1411:
1412: g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1454: RETURN;
1455: END IF;
1456: CLOSE check_frozen_child;
1457:
1458: DELETE FROM ZX_LINES
1459: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
1460: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
1461: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1462: AND TRX_ID = p_event_class_rec.TRX_ID;
1463:
1464: IF (g_level_statement >= g_current_runtime_level ) THEN
1465: FND_LOG.STRING(g_level_statement,
1466: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Delete_Transaction',
1467: 'Number of rows deleted from zx_lines = '||l_row_count);
1468: END IF;
1469:
1470: IF p_event_class_rec.summarization_flag = 'Y' THEN
1471: -- Delete the summary lines
1469:
1470: IF p_event_class_rec.summarization_flag = 'Y' THEN
1471: -- Delete the summary lines
1472:
1473: DELETE FROM ZX_LINES_SUMMARY
1474: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
1475: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
1476: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1477: AND TRX_ID = p_event_class_rec.TRX_ID;
1478:
1479: IF (g_level_statement >= g_current_runtime_level ) THEN
1480: FND_LOG.STRING(g_level_statement,
1481: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Delete_Transaction',
1482: 'Number of rows deleted from zx_lines_summary = '||l_row_count);
1483: END IF;
1484:
1485: END IF;
1486:
1607:
1608: -- Initialize API return status to success
1609: x_return_status := FND_API.G_RET_STS_SUCCESS;
1610:
1611: UPDATE ZX_LINES
1612: SET Cancel_Flag = 'Y'
1613: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
1614: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
1615: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1621:
1622: IF (g_level_statement >= g_current_runtime_level ) THEN
1623: FND_LOG.STRING(g_level_statement,
1624: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Cancel_Transaction',
1625: 'Number of rows updated in zx_lines = '||l_row_count);
1626: END IF;
1627:
1628: IF p_event_class_rec.summarization_flag = 'Y' THEN
1629:
1626: END IF;
1627:
1628: IF p_event_class_rec.summarization_flag = 'Y' THEN
1629:
1630: UPDATE ZX_LINES_SUMMARY
1631: SET Cancel_Flag = 'Y'
1632: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
1633: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
1634: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
1638:
1639: IF (g_level_statement >= g_current_runtime_level ) THEN
1640: FND_LOG.STRING(g_level_statement,
1641: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Cancel_Transaction',
1642: 'Number of rows updated in zx_lines_summary = '||l_row_count);
1643: END IF;
1644:
1645: END IF;
1646:
2221: APP_EXCEPTION.Raise_Exception;
2222: RAISE FND_API.G_EXC_ERROR;
2223: END IF;
2224:
2225: UPDATE ZX_LINES
2226: SET Delete_Flag = 'Y'
2227: WHERE APPLICATION_ID = p_transaction_line_rec.APPLICATION_ID
2228: AND ENTITY_CODE = p_transaction_line_rec.ENTITY_CODE
2229: AND EVENT_CLASS_CODE = p_transaction_line_rec.EVENT_CLASS_CODE
2235:
2236: IF (g_level_statement >= g_current_runtime_level ) THEN
2237: FND_LOG.STRING(g_level_statement,
2238: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Mark_Detail_Tax_Lines_Delete',
2239: 'Number of rows updated in zx_lines = '||l_row_count);
2240: END IF;
2241:
2242: IF (g_level_procedure >= g_current_runtime_level ) THEN
2243: FND_LOG.STRING(g_level_procedure,
2676: 'Number of Rows Inserted: '||SQL%ROWCOUNT);
2677: END IF;
2678:
2679: BEGIN
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
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
2829: AND ENTITY_CODE = p_event_class_rec.entity_code
2830: AND EVENT_CLASS_CODE = p_event_class_rec.event_class_code
2831: AND (Process_For_Recovery_Flag = 'Y' OR
2831: AND (Process_For_Recovery_Flag = 'Y' OR
2832: Item_Dist_Changed_Flag = 'Y')
2833: AND EXISTS
2834: (SELECT 1
2835: FROM zx_lines_det_factors
2836: WHERE APPLICATION_ID = L.application_id
2837: AND ENTITY_CODE = L.entity_code
2838: AND EVENT_CLASS_CODE = L.event_class_code
2839: AND TRX_ID = L.trx_id
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
2863: AND tax_line_number <> zd.tax_line_number
2864: )
2865: WHERE NVL(reverse_flag,'N') = 'Y'
2867: AND entity_code = p_event_class_rec.entity_code
2868: AND event_class_code = p_event_class_rec.event_class_code
2869: AND (trx_id, trx_line_id, trx_level_type) IN
2870: (SELECT trx_id, trx_line_id, trx_level_type
2871: FROM zx_lines_det_factors
2872: WHERE application_id = p_event_class_rec.application_id
2873: AND entity_code = p_event_class_rec.entity_code
2874: AND event_class_code = p_event_class_rec.event_class_code
2875: AND event_id = p_event_class_rec.event_id
2875: AND event_id = p_event_class_rec.event_id
2876: )
2877: AND EXISTS
2878: (SELECT tax_line_number
2879: FROM zx_lines
2880: WHERE tax_line_id = zd.tax_line_id
2881: AND tax_line_number <> zd.tax_line_number
2882: );
2883:
2906: )
2907: WHERE reverse_flag = 'Y'
2908: AND tax_line_id IN
2909: (SELECT tax_line_id
2910: FROM zx_lines zl
2911: WHERE application_id = p_event_class_rec.application_id
2912: AND entity_code = p_event_class_rec.entity_code
2913: AND event_class_code = p_event_class_rec.event_class_code
2914: AND (process_for_recovery_flag = 'Y' OR
2914: AND (process_for_recovery_flag = 'Y' OR
2915: item_dist_changed_flag = 'Y')
2916: AND EXISTS
2917: (SELECT 1
2918: FROM zx_lines_det_factors
2919: WHERE application_id = zl.application_id
2920: AND entity_code = zl.entity_code
2921: AND event_class_code = zl.event_class_code
2922: AND trx_id = zl.trx_id
2992: x_return_status := FND_API.G_RET_STS_SUCCESS;
2993:
2994: select trx_id, trx_line_id, trx_level_type
2995: bulk collect into pg_trx_id_tab, pg_trx_line_id_tab ,pg_trx_level_type_tab
2996: from zx_lines_det_factors
2997: where application_id = p_event_class_rec.application_id
2998: and entity_code = p_event_class_rec.entity_code
2999: and event_class_code = p_event_class_rec.event_class_code
3000: and event_id = p_event_class_rec.event_id;
2999: and event_class_code = p_event_class_rec.event_class_code
3000: and event_id = p_event_class_rec.event_id;
3001:
3002: FORALL i in nvl(pg_trx_id_tab.FIRST,0)..nvl(pg_trx_id_tab.LAST,-99)
3003: UPDATE ZX_LINES L
3004: SET (rec_tax_amt,
3005: rec_tax_amt_tax_curr,
3006: rec_tax_amt_funcl_curr,
3007: nrec_tax_amt,
3054: WHERE application_id = p_event_class_rec.application_id
3055: AND entity_code = p_event_class_rec.entity_code
3056: AND event_class_code = p_event_class_rec.event_class_code
3057: /*AND exists
3058: ( select 1 from zx_lines_det_factors
3059: where application_id = l.application_id
3060: and entity_code = l.entity_code
3061: and event_class_code = l.event_class_code
3062: and trx_id = l.trx_id
3070: ;
3071:
3072: IF p_event_class_rec.summarization_flag = 'Y' THEN
3073:
3074: UPDATE ZX_LINES_SUMMARY S
3075: SET (total_rec_tax_amt,
3076: total_nrec_tax_amt,
3077: total_rec_tax_amt_funcl_curr,
3078: total_nrec_tax_amt_funcl_curr,
3085: SUM(rec_tax_amt_funcl_curr) total_rec_tax_amt_funcl_curr,
3086: SUM(nrec_tax_amt_funcl_curr) total_nrec_tax_amt_funcl_curr,
3087: SUM(rec_tax_amt_tax_curr) total_rec_tax_amt_tax_curr,
3088: SUM(nrec_tax_amt_tax_curr) total_nrec_tax_amt_tax_curr
3089: FROM ZX_LINES L
3090: WHERE L.summary_tax_line_id = S.summary_tax_line_id
3091: AND L.application_id = S.application_id
3092: AND L.entity_code = S.entity_code
3093: AND L.event_class_code = S.event_class_code
3096: WHERE application_id = p_event_class_rec.application_id
3097: AND entity_code = p_event_class_rec.entity_code
3098: AND event_class_code = p_event_class_rec.event_class_code
3099: AND exists
3100: ( select 1 from zx_lines_det_factors
3101: where application_id = S.application_id
3102: and entity_code = S.entity_code
3103: and event_class_code = S.event_class_code
3104: and trx_id = S.trx_id
3129: END IF;
3130: END Update_TaxLine_Rec_Nrec_Amt;
3131:
3132: /*============================================================================*
3133: | Update_Freeze_Flag: Freezes distributions and updates ZX_LINES |
3134: | Associated_Child_Frozen_Flag flag to indicate that the associated |
3135: | children are frozen. |
3136: *============================================================================*/
3137:
3181: )
3182: AND application_id = p_event_class_rec.application_id
3183: ;
3184:
3185: UPDATE ZX_LINES ZL
3186: SET Associated_Child_Frozen_Flag ='Y',
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
3200:
3201: --
3202: -- delete all summary tax lines for this document
3203: --
3204: DELETE FROM ZX_LINES_SUMMARY
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
3218: p_event_class_rec,
3219: l_return_status);
3220: ELSE
3221: --
3222: -- create summary lines from zx_lines without
3223: -- retain the current summary_tax_line_id
3224: --
3225: create_summary_lines_upd_evnt(
3226: p_event_class_rec => p_event_class_rec,
3326: END IF;
3327: END Update_Freeze_Flag;
3328:
3329: /*============================================================================*
3330: | Update_Item_Dist_Changed_Flag: This procedure updates tax lines (ZX_LINES) |
3331: | with changed status for given transaction line distributions. |
3332: *============================================================================*/
3333:
3334: PROCEDURE Update_Item_Dist_Changed_Flag
3349:
3350: -- Initialize API return status to success
3351: x_return_status := FND_API.G_RET_STS_SUCCESS;
3352:
3353: UPDATE ZX_LINES ZL
3354: SET Item_Dist_Changed_Flag = 'Y'
3355: WHERE APPLICATION_ID = p_event_class_rec.application_id
3356: AND ENTITY_CODE = p_event_class_rec.entity_code
3357: AND EVENT_CLASS_CODE = p_event_class_rec.event_class_code
3449:
3450: -- Initialize API return status to success
3451: x_return_status := FND_API.G_RET_STS_SUCCESS;
3452:
3453: UPDATE ZX_LINES
3454: SET ORIG_TAXABLE_AMT = NVL(orig_taxable_amt, taxable_amt),
3455: ORIG_TAXABLE_AMT_TAX_CURR = NVL(orig_taxable_amt_tax_curr, taxable_amt_tax_curr),
3456: ORIG_TAX_AMT = NVL(orig_tax_amt, tax_amt),
3457: ORIG_TAX_AMT_TAX_CURR = NVL(orig_tax_amt_tax_curr, tax_amt_tax_curr),
3484:
3485: IF (g_level_statement >= g_current_runtime_level ) THEN
3486: FND_LOG.STRING(g_level_statement,
3487: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Discard_Tax_Only_Lines',
3488: 'Number of rows updated in zx_lines = '||l_row_count);
3489: END IF;
3490:
3491: IF p_event_class_rec.summarization_flag = 'Y' THEN
3492:
3489: END IF;
3490:
3491: IF p_event_class_rec.summarization_flag = 'Y' THEN
3492:
3493: UPDATE zx_lines_summary
3494: SET TAX_AMT = 0,
3495: TAX_AMT_TAX_CURR = 0,
3496: TAX_AMT_FUNCL_CURR = 0,
3497: TOTAL_REC_TAX_AMT = 0,
3510:
3511: IF (g_level_statement >= g_current_runtime_level ) THEN
3512: FND_LOG.STRING(g_level_statement,
3513: 'ZX.PLSQL.ZX_TRL_MANAGE_TAX_PKG.Discard_Tax_Only_Lines',
3514: 'Number of rows updated in zx_lines_summary = '||l_row_count);
3515: END IF;
3516:
3517: END IF;
3518:
4055: x_return_status := FND_API.G_RET_STS_SUCCESS;
4056:
4057: IF p_event_class_rec.tax_recovery_flag = 'Y' THEN
4058:
4059: UPDATE ZX_LINES L
4060: SET (rec_tax_amt_funcl_curr,
4061: nrec_tax_amt_funcl_curr
4062: ) =
4063: (SELECT SUM(decode(Recoverable_Flag,
4084: END IF;
4085:
4086: IF p_event_class_rec.summarization_flag = 'Y' THEN
4087:
4088: UPDATE ZX_LINES_SUMMARY S
4089: SET (tax_amt_funcl_curr,
4090: total_rec_tax_amt_funcl_curr,
4091: total_nrec_tax_amt_funcl_curr
4092: ) =
4092: ) =
4093: (SELECT SUM(L.tax_amt_funcl_curr) tax_amt_funcl_curr,
4094: SUM(rec_tax_amt_funcl_curr) total_rec_tax_amt_funcl_curr ,
4095: SUM(nrec_tax_amt_funcl_curr) total_nrec_tax_amt_funcl_curr
4096: FROM ZX_LINES L
4097: WHERE L.summary_tax_line_id = S.summary_tax_line_id
4098: AND L.application_id = S.application_id
4099: AND L.entity_code = S.entity_code
4100: AND L.event_class_code = S.event_class_code
4132: /* ===========================================================================*
4133: | PROCEDURE update_exist_summary_line_id: |
4134: | Preserve old summary_tax_line_id in g_detail_tax_lines_gt for |
4135: | UPDATE case, if the same summarization criteria exists in |
4136: | zx_lines_summary |
4137: * ===========================================================================*/
4138: PROCEDURE update_exist_summary_line_id(
4139: p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
4140: x_return_status OUT NOCOPY VARCHAR2) IS
4162: --
4163: UPDATE zx_detail_tax_lines_gt zlgt
4164: SET summary_tax_line_id =
4165: ( SELECT summary_tax_line_id
4166: FROM zx_lines_summary summ
4167: -- bug fix 5417887
4168: --WHERE summ.application_id = p_event_class_rec.application_id
4169: -- AND summ.entity_code = p_event_class_rec.entity_code
4170: -- AND summ.event_class_code = p_event_class_rec.event_class_code
4263: END update_exist_summary_line_id;
4264:
4265: /* ===========================================================================*
4266: | PROCEDURE RELEASE_DOCUMENT_TAX_HOLD : public API to release the tax hold at|
4267: | the document level by updating TAX_HOLD_RELEASED_CODE in zx_lines based on |
4268: | the input release tax code table. |
4269: | Bug Fix: 3339364 by lxzhang |
4270: * ===========================================================================*/
4271:
4301: l_tax_hold_release_mask := l_tax_hold_release_mask + l_tax_hold_release_value;
4302: END LOOP;
4303:
4304: -- update the tax_hold_release_code
4305: UPDATE ZX_LINES
4306: SET TAX_HOLD_RELEASED_CODE = BITAND(TAX_HOLD_CODE, l_tax_hold_release_mask )
4307: WHERE TAX_LINE_ID in (
4308: SELECT TAX_LINE_ID
4309: FROM ZX_LINES
4305: UPDATE ZX_LINES
4306: SET TAX_HOLD_RELEASED_CODE = BITAND(TAX_HOLD_CODE, l_tax_hold_release_mask )
4307: WHERE TAX_LINE_ID in (
4308: SELECT TAX_LINE_ID
4309: FROM ZX_LINES
4310: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
4311: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
4312: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
4313: AND TRX_ID = p_event_class_rec.TRX_ID );
4349: RETURN;
4350: END IF;
4351:
4352: -- update the tax_hold_release_code
4353: UPDATE ZX_LINES
4354: SET TAX_HOLD_RELEASED_CODE = BITAND(TAX_HOLD_CODE, l_tax_hold_release_mask )
4355: WHERE TAX_LINE_ID in (
4356: SELECT TAX_LINE_ID
4357: FROM ZX_LINES
4353: UPDATE ZX_LINES
4354: SET TAX_HOLD_RELEASED_CODE = BITAND(TAX_HOLD_CODE, l_tax_hold_release_mask )
4355: WHERE TAX_LINE_ID in (
4356: SELECT TAX_LINE_ID
4357: FROM ZX_LINES
4358: WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
4359: AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
4360: AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
4361: AND TRX_ID = p_event_class_rec.TRX_ID );
4386: -- PRIVATE PROCEDURE
4387: -- create_summary_lines_crt_evnt
4388: --
4389: -- DESCRIPTION
4390: -- Private procedure to create zx_lines_summary from zx_detail_tax_lines_gt
4391: -- for tax_event_type of 'CREATE' or for tax_event_type of 'UPDATE'/'OVERRIDE'
4392: -- with retain_summ_tax_line_id_flag on event_class_rec of 'N'
4393: ------------------------------------------------------------------------------
4394: -- Bug 6456915 - associated_child_frozen_flag has been removed from grouping columns for summary tax lines
4429: entity_code,
4430: event_class_code,
4431: trx_id,
4432: internal_organization_id,
4433: zx_lines_summary_s.NEXTVAL,
4434: count_detail_tax_line,
4435: trx_number,
4436: applied_from_application_id,
4437: applied_from_event_class_code,
4936: WHERE tax_line_id = pg_tax_line_id_tbl(i);
4937:
4938: -- insert the newly created summary tax lines
4939: FORALL i IN 1..pg_summary_tax_line_id_tbl.COUNT
4940: INSERT INTO zx_lines_summary(
4941: summary_tax_line_id,
4942: object_version_number,
4943: created_by,
4944: creation_date,
5118: IF (g_level_statement >= g_current_runtime_level ) THEN
5119:
5120: FND_LOG.STRING(g_level_statement,
5121: 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.create_summary_lines_crt_evnt',
5122: 'Number of Rows Inserted in zx_lines_summary = ' || to_char(SQL%ROWCOUNT));
5123: END IF;
5124:
5125: IF (g_level_procedure >= g_current_runtime_level ) THEN
5126:
5146: -- PRIVATE PROCEDURE
5147: -- create_summary_lines_upd_evnt
5148: --
5149: -- DESCRIPTION
5150: -- Private procedure to create zx_lines_summary from zx_detail_tax_lines_gt
5151: -- for tax_event_type of 'UPDATE' or 'OVERRIDE' with
5152: -- retain_summ_tax_line_id_flag on event_class_rec of 'Y'
5153: ------------------------------------------------------------------------------
5154: -- Bug 6456915 - associated_child_frozen_flag has been removed from grouping columns for summary tax lines
5182: x_return_status := FND_API.G_RET_STS_SUCCESS;
5183:
5184: SELECT tax_line_id, tax_rate_id bulk collect into
5185: tax_line_id_tbl, tax_rate_id_tbl
5186: FROM ZX_LINES tax
5187: WHERE
5188: /* -- bug fix 5417887
5189: application_id = p_event_class_rec.application_id
5190: AND entity_code = p_event_class_rec.entity_code
5192: AND trx_id = p_event_class_rec.trx_id
5193: */
5194: EXISTS (
5195: SELECT 1
5196: FROM zx_lines_det_factors line
5197: WHERE tax.application_id = line.application_id
5198: AND tax.event_class_code = line.event_class_code
5199: AND tax.entity_code = line.entity_code
5200: AND tax.trx_id = line.trx_id
5222: WHEN Others THEN
5223: NULL;
5224: END;
5225: END IF;
5226: update zx_lines set taxable_basis_formula = l_taxable_basis_formula
5227: WHERE tax_line_id = tax_line_id_tbl(i);
5228:
5229:
5230: END LOOP;
5235: event_class_code,
5236: trx_id,
5237: internal_organization_id,
5238: NVL(summary_tax_line_id,
5239: zx_lines_summary_s.NEXTVAL ) summary_tax_line_id,
5240: count_detail_tax_line,
5241: trx_number,
5242: applied_from_application_id,
5243: applied_from_event_class_code,
5461: tax_exception_id,
5462: -- exception_rate,
5463: mrc_tax_line_flag,
5464: tax_only_line_flag
5465: FROM ZX_LINES tax
5466: WHERE
5467: /* -- bug fix 5417887
5468: application_id = p_event_class_rec.application_id
5469: AND entity_code = p_event_class_rec.entity_code
5471: AND trx_id = p_event_class_rec.trx_id
5472: */
5473: EXISTS (
5474: SELECT 1
5475: FROM zx_lines_det_factors line
5476: WHERE tax.application_id = line.application_id
5477: AND tax.event_class_code = line.event_class_code
5478: AND tax.entity_code = line.entity_code
5479: AND tax.trx_id = line.trx_id
5610: tax_only_line_flag );
5611:
5612: -- get the tax line id in the same order of the summary tax line as in the first query.
5613: SELECT tax_line_id BULK COLLECT INTO pg_tax_line_id_tbl
5614: FROM zx_lines tax
5615: -- bug fix 5417887
5616: --WHERE application_id = p_event_class_rec.application_id
5617: --AND entity_code = p_event_class_rec.entity_code
5618: --AND event_class_code = p_event_class_rec.event_class_code
5618: --AND event_class_code = p_event_class_rec.event_class_code
5619: --AND trx_id = p_event_class_rec.trx_id
5620: WHERE EXISTS (
5621: SELECT 1
5622: FROM zx_lines_det_factors line
5623: WHERE tax.application_id = line.application_id
5624: AND tax.event_class_code = line.event_class_code
5625: AND tax.entity_code = line.entity_code
5626: AND tax.trx_id = line.trx_id
5701: entity_code,
5702: event_class_code,
5703: trx_id,
5704: internal_organization_id,
5705: zx_lines_summary_s.NEXTVAL,
5706: count_detail_tax_line,
5707: trx_number,
5708: applied_from_application_id,
5709: applied_from_event_class_code,
5927: tax_exception_id,
5928: -- exception_rate,
5929: mrc_tax_line_flag,
5930: tax_only_line_flag
5931: FROM ZX_LINES tax
5932: -- bug fix 5417887
5933: WHERE EXISTS (
5934: SELECT 1
5935: FROM zx_lines_det_factors line
5931: FROM ZX_LINES tax
5932: -- bug fix 5417887
5933: WHERE EXISTS (
5934: SELECT 1
5935: FROM zx_lines_det_factors line
5936: WHERE tax.application_id = line.application_id
5937: AND tax.event_class_code = line.event_class_code
5938: AND tax.entity_code = line.entity_code
5939: AND tax.trx_id = line.trx_id
6075: tax_only_line_flag );
6076:
6077: -- get the tax line id IN the same order of the summary tax line as IN the first query.
6078: SELECT tax_line_id BULK COLLECT INTO pg_tax_line_id_tbl
6079: FROM zx_lines tax
6080: -- bug fix 5417887
6081: --WHERE application_id = p_event_class_rec.application_id
6082: --AND entity_code = p_event_class_rec.entity_code
6083: --AND event_class_code = p_event_class_rec.event_class_code
6083: --AND event_class_code = p_event_class_rec.event_class_code
6084: --AND trx_id = p_event_class_rec.trx_id
6085: WHERE EXISTS (
6086: SELECT 1
6087: FROM zx_lines_det_factors line
6088: WHERE tax.application_id = line.application_id
6089: AND tax.event_class_code = line.event_class_code
6090: AND tax.entity_code = line.entity_code
6091: AND tax.trx_id = line.trx_id
6205: END LOOP;
6206:
6207: -- update the summary_tax_line_id column in the detail tax line table
6208: FORALL i IN 1.. pg_tax_line_id_tbl.COUNT
6209: UPDATE zx_lines
6210: SET summary_tax_line_id = pg_detail_tax_smry_line_id_tbl(i)
6211: WHERE tax_line_id = pg_tax_line_id_tbl(i);
6212:
6213: -- insert the summary tax lines in the zx_lines_summary table.
6209: UPDATE zx_lines
6210: SET summary_tax_line_id = pg_detail_tax_smry_line_id_tbl(i)
6211: WHERE tax_line_id = pg_tax_line_id_tbl(i);
6212:
6213: -- insert the summary tax lines in the zx_lines_summary table.
6214: FORALL i IN 1..pg_summary_tax_line_id_tbl.COUNT
6215: INSERT INTO zx_lines_summary(
6216: summary_tax_line_id,
6217: object_version_number,
6211: WHERE tax_line_id = pg_tax_line_id_tbl(i);
6212:
6213: -- insert the summary tax lines in the zx_lines_summary table.
6214: FORALL i IN 1..pg_summary_tax_line_id_tbl.COUNT
6215: INSERT INTO zx_lines_summary(
6216: summary_tax_line_id,
6217: object_version_number,
6218: created_by,
6219: creation_date,
6393: IF (g_level_statement >= g_current_runtime_level ) THEN
6394:
6395: FND_LOG.STRING(g_level_statement,
6396: 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.create_summary_lines_upd_evnt',
6397: 'Number of Rows Inserted in zx_lines_summary for update = ' || to_char(SQL%ROWCOUNT));
6398: END IF;
6399:
6400: IF (g_level_procedure >= g_current_runtime_level ) THEN
6401:
6421: -- PUBLIC PROCEDURE
6422: -- create_summary_lines_del_evnt
6423: --
6424: -- DESCRIPTION
6425: -- Public procedure to recreate summary tax lines from zx_lines after detleting
6426: -- the detail tax lines based on the passed-in transaction line information
6427: --
6428: -- NOTE
6429: -- 1. At present, we always regard the retain_summ_tax_line_id_flag as 'Y' due
6428: -- NOTE
6429: -- 1. At present, we always regard the retain_summ_tax_line_id_flag as 'Y' due
6430: -- to coding consideration. If later function requirement identified for not
6431: -- retain the summary tax line id, then need to update the new summary
6432: -- tax line id back to zx_lines.
6433: --
6434: -- 2. This API only used for deleting trx lines event, so that are cases that
6435: -- some summary tax lines for this trx is no need to recreate. The current
6436: -- approach is to delete all th original summary tax lines and recreate.
6470:
6471: -- At present, we always regard the retain_summ_tax_line_id_flag as 'Y' due
6472: -- to coding consideration. If later function requirement identified for not
6473: -- retain the summary tax line id, then need to update the new summary
6474: -- tax line id back to zx_lines.
6475:
6476: SELECT NVL(summary_tax_line_id,
6477: zx_lines_summary_s.NEXTVAL ) summary_tax_line_id,
6478: trx_number,
6473: -- retain the summary tax line id, then need to update the new summary
6474: -- tax line id back to zx_lines.
6475:
6476: SELECT NVL(summary_tax_line_id,
6477: zx_lines_summary_s.NEXTVAL ) summary_tax_line_id,
6478: trx_number,
6479: applied_from_application_id,
6480: applied_from_event_class_code,
6481: applied_from_entity_code,
6686: tax_exception_id,
6687: -- exception_rate,
6688: mrc_tax_line_flag,
6689: tax_only_line_flag
6690: FROM ZX_LINES
6691: WHERE application_id = p_application_id
6692: AND entity_code = p_entity_code
6693: AND event_class_code = p_event_class_code
6694: AND trx_id = p_trx_id
6832: END LOOP;
6833:
6834:
6835: FORALL i IN 1..pg_summary_tax_line_id_tbl.COUNT
6836: INSERT INTO zx_lines_summary
6837: ( summary_tax_line_id,
6838: object_version_number,
6839: created_by,
6840: creation_date,
7003: IF (g_level_statement >= g_current_runtime_level ) THEN
7004:
7005: FND_LOG.STRING(g_level_statement,
7006: 'ZX.PLSQL.ZX_TDS_TAX_LINES_DETM_PKG.create_summary_lines_del_evnt',
7007: 'NON-MRC Lines: Number of Rows Inserted into zx_lines_summary retain summary tax line id: '||to_char(SQL%ROWCOUNT));
7008: END IF;
7009:
7010: IF (g_level_procedure >= g_current_runtime_level ) THEN
7011: