eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ZX_PTP_IMPORT dependencies on ZX_PARTY_TAX_PROFILE

Line 27: G_PROG_APPID zx_party_tax_profile.program_application_id%TYPE;

23:
24: G_USER_ID fnd_user.user_id%TYPE;
25: G_LOGIN_ID fnd_user.last_update_login%TYPE;
26: G_REQUEST_ID NUMBER;
27: G_PROG_APPID zx_party_tax_profile.program_application_id%TYPE;
28: G_PROG_ID zx_party_tax_profile.program_id%TYPE;
29: G_SYSDATE DATE;
30: G_SESSION_LANG VARCHAR2(99);
31:

Line 28: G_PROG_ID zx_party_tax_profile.program_id%TYPE;

24: G_USER_ID fnd_user.user_id%TYPE;
25: G_LOGIN_ID fnd_user.last_update_login%TYPE;
26: G_REQUEST_ID NUMBER;
27: G_PROG_APPID zx_party_tax_profile.program_application_id%TYPE;
28: G_PROG_ID zx_party_tax_profile.program_id%TYPE;
29: G_SYSDATE DATE;
30: G_SESSION_LANG VARCHAR2(99);
31:
32: G_MISS_CHAR VARCHAR2(1);

Line 216: G_INTF_PTP_TABLE_NAME := 'ZX_PARTY_TAX_PROFILE_INT';

212:
213: --
214: -- interface table names
215: --
216: G_INTF_PTP_TABLE_NAME := 'ZX_PARTY_TAX_PROFILE_INT';
217: G_INTF_REG_TABLE_NAME := 'ZX_REGISTRATIONS_INT';
218: G_INTF_EXE_TABLE_NAME := 'ZX_EXEMPTIONS_INT';
219: G_INTF_RCA_TABLE_NAME := 'ZX_REPORT_CODES_ASSOC_INT';
220: G_INTF_CUS_TABLE_NAME := 'RA_CUSTOMERS_INTERFACE_ALL';

Line 444: DELETE zx_party_tax_profile_int

440: IF FND_API.TO_BOOLEAN(p_commit) THEN
441: SAVEPOINT DEL_SUC_REC_SP;
442: END IF;
443:
444: DELETE zx_party_tax_profile_int
445: WHERE request_id = G_REQUEST_ID
446: AND record_status = G_RS_VALID;
447:
448: G_DEBUG_STATEMENT:='Deleted '||SQL%ROWCOUNT||' records from '||G_INTF_PTP_TABLE_NAME;

Line 528: UPDATE zx_party_tax_profile_int ptp_int

524: IF (G_DEBUG_LEVEL_PROCEDURE >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
525: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,G_DEBUG_LOG_HEADER||'ResetRecStatusForChildTables','ResetRecStatusForChildTables(+)');
526: END IF;
527:
528: UPDATE zx_party_tax_profile_int ptp_int
529: SET record_status = NULL
530: WHERE request_id = G_REQUEST_ID
531: AND record_status = G_RS_VALID
532: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1

Line 533: FROM zx_party_tax_profile_int ptp_err

529: SET record_status = NULL
530: WHERE request_id = G_REQUEST_ID
531: AND record_status = G_RS_VALID
532: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
533: FROM zx_party_tax_profile_int ptp_err
534: WHERE request_id = G_REQUEST_ID
535: AND NVL(record_status,G_RS_ERROR) = G_RS_ERROR
536: AND ptp_err.intf_party_reference = ptp_int.intf_party_reference
537: AND ROWNUM = 1

Line 550: FROM zx_party_tax_profile_int ptp_int

546: SET record_status = NULL
547: WHERE request_id = G_REQUEST_ID
548: AND record_status = G_RS_VALID
549: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
550: FROM zx_party_tax_profile_int ptp_int
551: WHERE request_id = G_REQUEST_ID
552: AND NVL(record_status,G_RS_ERROR) = G_RS_ERROR
553: AND ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
554: AND ROWNUM = 1

Line 567: FROM zx_party_tax_profile_int ptp_int

563: SET record_status = NULL
564: WHERE request_id = G_REQUEST_ID
565: AND record_status = G_RS_VALID
566: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
567: FROM zx_party_tax_profile_int ptp_int
568: WHERE request_id = G_REQUEST_ID
569: AND NVL(record_status,G_RS_ERROR) = G_RS_ERROR
570: AND ptp_int.intf_ptp_reference = exe_int.intf_ptp_reference
571: AND ROWNUM = 1

Line 584: FROM zx_party_tax_profile_int ptp_int

580: SET record_status = NULL
581: WHERE request_id = G_REQUEST_ID
582: AND record_status = G_RS_VALID
583: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
584: FROM zx_party_tax_profile_int ptp_int
585: WHERE request_id = G_REQUEST_ID
586: AND NVL(record_status,G_RS_ERROR) = G_RS_ERROR
587: AND ptp_int.intf_ptp_reference = rca_int.intf_ptp_reference
588: AND ROWNUM = 1

Line 673: FROM ZX_PARTY_TAX_PROFILE_INT

669: IF FND_API.TO_BOOLEAN(p_validate_only) THEN
670: BEGIN
671: SELECT 'x'
672: INTO l_temp_char_30
673: FROM ZX_PARTY_TAX_PROFILE_INT
674: WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
675: AND NVL(batch_id,G_MISS_NUM) =
676: NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM))
677: AND ROWNUM = 1;

Line 698: FROM ZX_PARTY_TAX_PROFILE_INT

694: IF FND_API.TO_BOOLEAN(p_insert_only) THEN
695: BEGIN
696: SELECT 'x'
697: INTO l_temp_char_30
698: FROM ZX_PARTY_TAX_PROFILE_INT
699: WHERE request_id = G_REQUEST_ID
700: AND record_status = G_RS_VALID
701: AND ROWNUM = 1;
702: EXCEPTION

Line 970: FROM zx_party_tax_profile_int

966: PROCEDURE ProcessPtyTxProfileRecord IS
967:
968: CURSOR get_country_codes IS
969: SELECT DISTINCT country_code
970: FROM zx_party_tax_profile_int
971: WHERE record_status = G_RS_IN_PROCESS
972: AND request_id = G_REQUEST_ID
973: AND country_code IN ('TW','SK','SI','SE','RU','PT','PL','NL','MT',
974: 'LV','LU','LT','IT','IE','HU','GR','GB','FR',

Line 996: UPDATE zx_party_tax_profile_int

992:
993: -------------------------
994: -- Populate default values
995: -------------------------
996: UPDATE zx_party_tax_profile_int
997: SET request_id = G_REQUEST_ID
998: ,record_status = G_RS_IN_PROCESS
999: ,dml_type = UPPER(dml_type)
1000: ,prog_int_char1 = NULL

Line 1015: UPDATE zx_party_tax_profile_int

1011:
1012: -------------------------
1013: -- Check Mandatory Paramters
1014: -------------------------
1015: UPDATE zx_party_tax_profile_int
1016: SET error_number = G_ES_PTP_MAND_PARAM_MISSING
1017: WHERE request_id = G_REQUEST_ID
1018: AND record_status = G_RS_IN_PROCESS
1019: AND party_id IS NULL

Line 1033: UPDATE zx_party_tax_profile_int orig

1029: -------------------------
1030: -- Duplicate records checking
1031: -------------------------
1032:
1033: UPDATE zx_party_tax_profile_int orig
1034: SET error_number = error_number + G_ES_PTP_DUP_INTF_RECORD,
1035: record_status = G_RS_ERROR
1036: WHERE request_id = G_REQUEST_ID
1037: AND record_status = G_RS_IN_PROCESS

Line 1039: FROM zx_party_tax_profile_int dup

1035: record_status = G_RS_ERROR
1036: WHERE request_id = G_REQUEST_ID
1037: AND record_status = G_RS_IN_PROCESS
1038: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
1039: FROM zx_party_tax_profile_int dup
1040: WHERE dup.rowid <> orig.rowid
1041: AND dup.request_id = G_REQUEST_ID
1042: AND dup.record_status = G_RS_IN_PROCESS
1043: AND NVL(dup.intf_party_reference,G_MISS_CHAR) =

Line 1073: UPDATE zx_party_tax_profile_int ptp_int

1069: ------------------
1070: -- TRN Validations
1071: ------------------
1072:
1073: UPDATE zx_party_tax_profile_int ptp_int
1074: SET PROG_INT_NUM1 = G_PROG_INT_VAL
1075: WHERE request_id = G_REQUEST_ID
1076: AND (country_code IS NULL OR registration_number IS NULL)
1077: AND record_status = G_RS_IN_PROCESS;

Line 1089: UPDATE zx_party_tax_profile_int ptp_int

1085: END LOOP;
1086:
1087: -- Final Updation of the Record Status and error Codes
1088:
1089: UPDATE zx_party_tax_profile_int ptp_int
1090: SET error_number = error_number + G_ES_PTP_REG_NUM_INVALID
1091: WHERE country_code IS NOT NULL
1092: AND country_code IN ('TW','SK','SI','SE','RU','PT','PL','NL','MT',
1093: 'LV','LU','LT','IT','IE','HU','GR','GB','FR',

Line 1115: UPDATE zx_party_tax_profile_int ptp_int

1111: -------------------------
1112: -- NO Other Validations
1113: -------------------------
1114:
1115: UPDATE zx_party_tax_profile_int ptp_int
1116: SET record_status = DECODE(error_number,0,G_RS_VALID,G_RS_ERROR),
1117: prog_int_num1 = 0
1118: WHERE request_id = G_REQUEST_ID
1119: AND record_status = G_RS_IN_PROCESS;

Line 1166: UPDATE zx_party_tax_profile_int

1162: IF (G_DEBUG_LEVEL_PROCEDURE >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
1163: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion','PtpValueToIdConversion(+)');
1164: END IF;
1165:
1166: UPDATE zx_party_tax_profile_int
1167: SET error_number = error_number + G_ES_PTP_TRANS_TYPE_INVALID
1168: WHERE record_status = G_RS_IN_PROCESS
1169: AND request_id = G_REQUEST_ID
1170: AND dml_type <> 'CREATE';

Line 1177: UPDATE zx_party_tax_profile_int

1173: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
1174: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1175: END IF;
1176:
1177: UPDATE zx_party_tax_profile_int
1178: SET error_number = error_number + G_ES_PTP_PARTY_TYPE_INVALID
1179: WHERE record_status = G_RS_IN_PROCESS
1180: AND request_id = G_REQUEST_ID
1181: AND party_type_code NOT IN ('THIRD_PARTY','THIRD_PARTY_SITE');

Line 1190: UPDATE zx_party_tax_profile_int intf

1186: END IF;
1187:
1188: -- value to id conversion for party_id Only for DML TYpe = 'UPDATE'
1189: -- written for future use at the time of creation
1190: UPDATE zx_party_tax_profile_int intf
1191: SET error_number = error_number + G_ES_PTP_PARTY_INVALID
1192: WHERE record_status = G_RS_IN_PROCESS
1193: AND request_id = G_REQUEST_ID
1194: AND party_type_code = 'THIRD_PARTY'

Line 1221: UPDATE zx_party_tax_profile_int intf

1217: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
1218: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1219: END IF;
1220:
1221: UPDATE zx_party_tax_profile_int intf
1222: SET party_id = CASE WHEN party_id IS NULL AND party_name IS NOT NULL
1223: THEN (SELECT party_id
1224: FROM hz_parties
1225: WHERE party_name = intf.party_name)

Line 1241: UPDATE zx_party_tax_profile_int

1237: AND party_type_code = 'THIRD_PARTY'
1238: AND dml_type = 'UPDATE';
1239:
1240: IF G_CUST_INTF_TABLE IN (G_INTF_SUP_TABLE_NAME,G_INTF_SUP_SITE_TABLE_NAME) THEN
1241: UPDATE zx_party_tax_profile_int
1242: SET error_number = error_number + G_ES_PTP_INC_FLG_INVALID
1243: WHERE record_status = G_RS_IN_PROCESS
1244: AND request_id = G_REQUEST_ID
1245: AND inclusive_tax_flag NOT IN ('Y','N',NULL);

Line 1247: UPDATE zx_party_tax_profile_int

1243: WHERE record_status = G_RS_IN_PROCESS
1244: AND request_id = G_REQUEST_ID
1245: AND inclusive_tax_flag NOT IN ('Y','N',NULL);
1246:
1247: UPDATE zx_party_tax_profile_int
1248: SET error_number = error_number + G_ES_PTP_APP_FLG_INVALID
1249: WHERE record_status = G_RS_IN_PROCESS
1250: AND request_id = G_REQUEST_ID
1251: AND process_for_applicability_flag NOT IN ('Y','N',NULL);

Line 1253: UPDATE zx_party_tax_profile_int

1249: WHERE record_status = G_RS_IN_PROCESS
1250: AND request_id = G_REQUEST_ID
1251: AND process_for_applicability_flag NOT IN ('Y','N',NULL);
1252:
1253: UPDATE zx_party_tax_profile_int
1254: SET error_number = error_number + G_ES_PTP_OFF_FLG_RECORD
1255: WHERE record_status = G_RS_IN_PROCESS
1256: AND request_id = G_REQUEST_ID
1257: AND allow_offset_tax_flag NOT IN ('Y','N',NULL);

Line 1262: UPDATE zx_party_tax_profile_int ptp_int

1258:
1259: END IF;
1260:
1261: -- validating the fields country_code and country_name
1262: UPDATE zx_party_tax_profile_int ptp_int
1263: SET error_number = error_number + G_ES_PTP_COUNTRY_INVALID
1264: WHERE (( country_code IS NOT NULL
1265: AND NOT EXISTS
1266: (

Line 1292: UPDATE zx_party_tax_profile_int ptp_int

1288: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1289: END IF;
1290:
1291: -- value to id conversion for registration_type_code
1292: UPDATE zx_party_tax_profile_int ptp_int
1293: SET country_code = (
1294: SELECT territory_code
1295: FROM fnd_territories_vl
1296: WHERE territory_short_name = ptp_int.country_name

Line 1310: UPDATE zx_party_tax_profile_int ptp_int

1306: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1307: END IF;
1308:
1309: -- validating the fields registration_type_code and registration_type_name
1310: UPDATE zx_party_tax_profile_int ptp_int
1311: SET error_number = error_number + G_ES_PTP_REG_TYPE_INVALID
1312: WHERE (( registration_type_code IS NOT NULL
1313: AND NOT EXISTS
1314: (

Line 1347: UPDATE zx_party_tax_profile_int ptp_int

1343: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1344: END IF;
1345:
1346: -- value to id conversion for registration_type_code
1347: UPDATE zx_party_tax_profile_int ptp_int
1348: SET registration_type_code = (
1349: SELECT fndlookup.lookup_code
1350: FROM fnd_lookups fndlookup
1351: WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_TYPE'

Line 1369: UPDATE zx_party_tax_profile_int ptp_int

1365: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1366: END IF;
1367:
1368: -- validating the fields rounding_level_code and rounding_level_name
1369: UPDATE zx_party_tax_profile_int ptp_int
1370: SET error_number = error_number + G_ES_PTP_ROUND_LEVEL_INVALID
1371: WHERE (( rounding_level_code IS NOT NULL
1372: AND NOT EXISTS
1373: (

Line 1406: UPDATE zx_party_tax_profile_int ptp_int

1402: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1403: END IF;
1404:
1405: -- value to id conversion for rounding_level_code
1406: UPDATE zx_party_tax_profile_int ptp_int
1407: SET rounding_level_code =
1408: (
1409: SELECT lookup_code
1410: FROM fnd_lookups

Line 1429: UPDATE zx_party_tax_profile_int ptp_int

1425: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1426: END IF;
1427:
1428: -- validating the fields rounding_rule_code and rounding_rule_name
1429: UPDATE zx_party_tax_profile_int ptp_int
1430: SET error_number = error_number + G_ES_PTP_ROUND_RULE_INVALID
1431: WHERE (( rounding_rule_code IS NOT NULL
1432: AND NOT EXISTS
1433: (

Line 1466: UPDATE zx_party_tax_profile_int ptp_int

1462: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1463: END IF;
1464:
1465: -- value to id conversion for rounding_rule_code
1466: UPDATE zx_party_tax_profile_int ptp_int
1467: SET rounding_rule_code = (
1468: SELECT lookup_code
1469: FROM fnd_lookups
1470: WHERE lookup_type = 'ZX_ROUNDING_RULE'

Line 1488: UPDATE zx_party_tax_profile_int ptp_int

1484: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1485: END IF;
1486:
1487: -- validating the fields tax_classification_code and tax_classification_name
1488: UPDATE zx_party_tax_profile_int ptp_int
1489: SET error_number = error_number + G_ES_PTP_TAX_CLASS_INVALID
1490: WHERE (( tax_classification_code IS NOT NULL
1491: AND NOT EXISTS
1492: (

Line 1538: UPDATE zx_party_tax_profile_int ptp_int

1534: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PtpValueToIdConversion',G_DEBUG_STATEMENT);
1535: END IF;
1536:
1537: -- value to id conversion for rounding_rule_code
1538: UPDATE zx_party_tax_profile_int ptp_int
1539: SET tax_classification_code = (
1540: SELECT lookup_code
1541: FROM zx_input_classifications_v
1542: WHERE enabled_flag = 'Y'

Line 2881: UPDATE zx_party_tax_profile_int ptp_int

2877: SET record_status = DECODE(error_number,0,G_RS_VALID,G_RS_ERROR)
2878: WHERE request_id = G_REQUEST_ID
2879: AND record_status = G_RS_IN_PROCESS;
2880:
2881: UPDATE zx_party_tax_profile_int ptp_int
2882: SET record_status = NULL
2883: WHERE request_id = G_REQUEST_ID
2884: AND record_status = G_RS_VALID
2885: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1

Line 3215: FROM zx_party_tax_profile ptp, hz_parties pty

3211: WHERE (( tax_authority_id IS NOT NULL
3212: AND NOT EXISTS
3213: (
3214: SELECT 1
3215: FROM zx_party_tax_profile ptp, hz_parties pty
3216: WHERE ptp.party_type_code ='TAX_AUTHORITY'
3217: AND ptp.party_id = ptp.party_id
3218: AND pty.party_type = 'ORGANIZATION'
3219: AND ptp.party_tax_profile_id = reg_int.tax_authority_id

Line 3228: FROM zx_party_tax_profile ptp, hz_parties pty

3224: AND tax_authority_name IS NOT NULL
3225: AND NOT EXISTS
3226: (
3227: SELECT 1
3228: FROM zx_party_tax_profile ptp, hz_parties pty
3229: WHERE ptp.party_type_code ='TAX_AUTHORITY'
3230: AND ptp.party_id = PTY.party_id
3231: AND pty.party_type = 'ORGANIZATION'
3232: AND pty.party_name = reg_int.tax_authority_name

Line 3248: FROM zx_party_tax_profile ptp, hz_parties pty

3244: -- value to id conversion for tax_authority_id
3245: UPDATE zx_registrations_int reg_int
3246: SET tax_authority_id = (
3247: SELECT PTP.party_tax_profile_id
3248: FROM zx_party_tax_profile ptp, hz_parties pty
3249: WHERE ptp.party_type_code ='TAX_AUTHORITY'
3250: AND ptp.party_id = PTY.party_id
3251: AND pty.party_type = 'ORGANIZATION'
3252: AND pty.party_name = reg_int.tax_authority_name

Line 3635: FROM zx_party_tax_profile_int ptp_int

3631: -- Store Party Info in prog_int_char1
3632: UPDATE zx_registrations_int reg_int
3633: SET prog_int_char1 =
3634: (SELECT /*+ FIRST_ROWS(1) */ intf_party_reference
3635: FROM zx_party_tax_profile_int ptp_int
3636: WHERE ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
3637: AND ROWNUM = 1 )
3638: WHERE request_id = G_REQUEST_ID
3639: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID;

Line 3667: UPDATE zx_party_tax_profile_int ptp_int

3663: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3664: END IF;
3665:
3666: -- TRN duplicate against other Party/sites
3667: UPDATE zx_party_tax_profile_int ptp_int
3668: SET error_number = error_number + G_ES_PTP_REG_NUM_DUP
3669: WHERE request_id = G_REQUEST_ID
3670: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3671: AND ptp_int.registration_number IS NOT NULL

Line 3674: FROM zx_party_tax_profile_int ptp_dup

3670: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3671: AND ptp_int.registration_number IS NOT NULL
3672: AND EXISTS (
3673: SELECT /*+ FIRST_ROWS(1) */ 1
3674: FROM zx_party_tax_profile_int ptp_dup
3675: WHERE ptp_dup.request_id = ptp_int.request_id
3676: AND ptp_dup.record_status = ptp_int.record_status
3677: AND ptp_dup.registration_number = ptp_int.registration_number
3678: AND ptp_dup.ROWID <> ptp_int.ROWID

Line 3699: UPDATE zx_party_tax_profile_int ptp_int

3695: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3696: END IF;
3697:
3698: -- TRN duplicate for Party Sites Siblings
3699: UPDATE zx_party_tax_profile_int ptp_int
3700: SET error_number = error_number + G_ES_PTP_REG_NUM_DUP
3701: WHERE request_id = G_REQUEST_ID
3702: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3703: AND party_type_code = 'THIRD_PARTY_SITE'

Line 3708: FROM zx_party_tax_profile_int ptp_dup

3704: AND ptp_int.registration_number IS NOT NULL
3705: AND BITAND(ptp_int.error_number,G_ES_PTP_REG_NUM_DUP) = 0
3706: AND EXISTS (
3707: SELECT /*+ FIRST_ROWS(1) */ 1
3708: FROM zx_party_tax_profile_int ptp_dup
3709: WHERE ptp_dup.request_id = ptp_int.request_id
3710: AND ptp_dup.record_status = ptp_int.record_status
3711: and ptp_dup.party_type_code = 'THIRD_PARTY_SITE'
3712: AND ptp_dup.intf_party_reference = ptp_int.intf_party_reference

Line 3726: from zx_party_tax_profile_int ptp_prnt

3722: )
3723: )
3724: AND NOT EXISTS (
3725: select 1
3726: from zx_party_tax_profile_int ptp_prnt
3727: where ptp_prnt.request_id = ptp_dup.request_id
3728: AND ptp_prnt.record_status = ptp_dup.record_status
3729: and ptp_prnt.party_type_code = 'THIRD_PARTY'
3730: AND ptp_prnt.intf_party_reference = ptp_dup.intf_party_reference

Line 3752: UPDATE zx_party_tax_profile_int ptp_int

3748: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
3749: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3750: END IF;
3751:
3752: UPDATE zx_party_tax_profile_int ptp_int
3753: SET error_number = error_number + G_ES_PTP_REG_NUM_DUP
3754: WHERE request_id = G_REQUEST_ID
3755: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3756: AND BITAND(ptp_int.error_number,G_ES_PTP_REG_NUM_DUP) = 0

Line 3759: FROM zx_party_tax_profile prod

3755: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3756: AND BITAND(ptp_int.error_number,G_ES_PTP_REG_NUM_DUP) = 0
3757: AND EXISTS(
3758: SELECT /*+ FIRST_ROWS(1) */ 1
3759: FROM zx_party_tax_profile prod
3760: WHERE prod.rep_registration_number = ptp_int.registration_number
3761: AND ROWNUM = 1
3762: );
3763:

Line 3769: UPDATE zx_party_tax_profile_int ptp_int

3765: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
3766: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3767: END IF;
3768:
3769: UPDATE zx_party_tax_profile_int ptp_int
3770: SET error_number = error_number + G_ES_PTP_REG_NUM_DUP
3771: WHERE request_id = G_REQUEST_ID
3772: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3773: AND BITAND(ptp_int.error_number,G_ES_PTP_REG_NUM_DUP) = 0

Line 3795: FROM zx_party_tax_profile_int ptp_dup

3791: AND prog_int_num1 <> G_PROG_INT_VAL
3792: AND reg_int.registration_number IS NOT NULL
3793: AND EXISTS (
3794: SELECT /*+ FIRST_ROWS(1) */ 1
3795: FROM zx_party_tax_profile_int ptp_dup
3796: WHERE ptp_dup.request_id = reg_int.request_id
3797: AND ptp_dup.record_status = reg_int.record_status
3798: AND ptp_dup.registration_number = reg_int.registration_number
3799: AND ptp_dup.intf_party_reference <> reg_int.prog_int_char1

Line 3824: FROM zx_party_tax_profile_int ptp_int

3820: WHERE request_id = G_REQUEST_ID
3821: AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
3822: AND prog_int_num1 <> G_PROG_INT_VAL
3823: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
3824: FROM zx_party_tax_profile_int ptp_int
3825: WHERE ptp_int.request_id = reg_int.request_id
3826: AND ptp_int.record_status = reg_int.record_status
3827: AND ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
3828: AND ptp_int.intf_party_reference = reg_int.prog_int_char1

Line 3835: FROM zx_party_tax_profile_int ptp_dup

3831: AND reg_int.registration_number IS NOT NULL
3832: AND BITAND(reg_int.error_number,G_ES_REG_REG_NUM_DUP) = 0
3833: AND EXISTS (
3834: SELECT /*+ FIRST_ROWS(1) */ 1
3835: FROM zx_party_tax_profile_int ptp_dup
3836: WHERE ptp_dup.request_id = reg_int.request_id
3837: AND ptp_dup.record_status = reg_int.record_status
3838: and ptp_dup.party_type_code = 'THIRD_PARTY_SITE'
3839: AND ptp_dup.intf_party_reference = reg_int.prog_int_char1

Line 3855: from zx_party_tax_profile_int ptp_prnt

3851: )
3852: )
3853: AND NOT EXISTS (
3854: select /*+ FIRST_ROWS(1) */ 1
3855: from zx_party_tax_profile_int ptp_prnt
3856: where ptp_prnt.request_id = ptp_dup.request_id
3857: AND ptp_prnt.record_status = ptp_dup.record_status
3858: and ptp_prnt.party_type_code = 'THIRD_PARTY'
3859: AND ptp_prnt.intf_party_reference = ptp_dup.intf_party_reference

Line 3890: FROM zx_party_tax_profile prod

3886: AND prog_int_num1 <> G_PROG_INT_VAL
3887: AND BITAND(reg_int.error_number,G_ES_REG_REG_NUM_DUP) = 0
3888: AND EXISTS(
3889: SELECT /*+ FIRST_ROWS(1) */ 1
3890: FROM zx_party_tax_profile prod
3891: WHERE prod.rep_registration_number = reg_int.registration_number
3892: AND ROWNUM = 1
3893: );
3894:

Line 3920: UPDATE zx_party_tax_profile_int ptp_int

3916: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3917: END IF;
3918:
3919:
3920: UPDATE zx_party_tax_profile_int ptp_int
3921: SET record_status = G_RS_ERROR
3922: WHERE request_id = G_REQUEST_ID
3923: AND record_status = G_RS_VALID
3924: AND error_number > 0;

Line 3942: UPDATE zx_party_tax_profile_int ptp_int

3938: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
3939: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'DupTRNValidation',G_DEBUG_STATEMENT);
3940: END IF;
3941:
3942: UPDATE zx_party_tax_profile_int ptp_int
3943: SET record_status = NULL
3944: WHERE request_id = G_REQUEST_ID
3945: AND record_status = G_RS_VALID
3946: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1

Line 3983: UPDATE zx_party_tax_profile_int ptp_int

3979: IF (G_DEBUG_LEVEL_PROCEDURE >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
3980: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,G_DEBUG_LOG_HEADER||'SyncUpRegNumberToPTP','SyncUpRegNumberToPTP(+)');
3981: END IF;
3982:
3983: UPDATE zx_party_tax_profile_int ptp_int
3984: SET registration_number = (
3985: SELECT registration_number
3986: FROM zx_registrations_int reg
3987: WHERE reg.request_id = G_REQUEST_ID

Line 4164: UPDATE zx_party_tax_profile_int ptp_int

4160: SET record_status = DECODE(error_number,0,G_RS_VALID,G_RS_ERROR)
4161: WHERE request_id = G_REQUEST_ID
4162: AND record_status = G_RS_IN_PROCESS;
4163:
4164: UPDATE zx_party_tax_profile_int ptp_int
4165: SET record_status = NULL
4166: WHERE request_id = G_REQUEST_ID
4167: AND record_status = G_RS_VALID
4168: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1

Line 5063: FROM zx_party_tax_profile PTP,

5059: WHERE (( issuing_tax_authority_id IS NOT NULL
5060: AND NOT EXISTS
5061: (
5062: SELECT 1
5063: FROM zx_party_tax_profile PTP,
5064: hz_parties PTY
5065: WHERE PTP.party_type_code ='TAX_AUTHORITY'
5066: AND PTP.party_id = PTY.party_id
5067: AND PTP.party_tax_profile_id = exe_int.issuing_tax_authority_id

Line 5076: FROM zx_party_tax_profile PTP,

5072: AND issuing_tax_authority_name IS NOT NULL
5073: AND NOT EXISTS
5074: (
5075: SELECT PTY.party_name
5076: FROM zx_party_tax_profile PTP,
5077: hz_parties PTY
5078: WHERE PTP.party_type_code ='TAX_AUTHORITY'
5079: AND PTP.party_id = PTY.party_id
5080: AND PTy.party_name = exe_int.issuing_tax_authority_name

Line 5097: FROM zx_party_tax_profile PTP,

5093: UPDATE zx_exemptions_int exe_int
5094: SET issuing_tax_authority_id =
5095: (
5096: SELECT PTP.party_tax_profile_id
5097: FROM zx_party_tax_profile PTP,
5098: hz_parties PTY
5099: WHERE PTP.party_type_code ='TAX_AUTHORITY'
5100: AND PTP.party_id = PTY.party_id
5101: AND PTY.party_name = exe_int.issuing_tax_authority_name

Line 5681: UPDATE zx_party_tax_profile_int ptp_int

5677: WHERE request_id = G_REQUEST_ID
5678: AND record_status = G_RS_IN_PROCESS;
5679:
5680:
5681: UPDATE zx_party_tax_profile_int ptp_int
5682: SET record_status = NULL
5683: WHERE request_id = G_REQUEST_ID
5684: AND record_status = G_RS_VALID
5685: AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1

Line 5764: AND entity_code <> 'ZX_PARTY_TAX_PROFILE';

5760: WHERE record_status = G_RS_IN_PROCESS
5761: AND request_id = G_REQUEST_ID
5762: AND NVL(batch_id,G_MISS_NUM) =
5763: NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM))
5764: AND entity_code <> 'ZX_PARTY_TAX_PROFILE';
5765:
5766: G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records with invalid entity_code found';
5767: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
5768: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'AssocValueToIdConversion',G_DEBUG_STATEMENT);

Line 5797: AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'

5793: WHERE RepType.Reporting_Type_Id = RepUsage.Reporting_Type_Id
5794: AND RepUsage.Enabled_Flag= 'Y'
5795: AND SYSDATE BETWEEN RepType. EFFECTIVE_FROM
5796: AND NVL(RepType. EFFECTIVE_TO,SYSDATE)
5797: AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
5798: AND RepType.Has_Reporting_Codes_Flag = 'Y'
5799: AND NVL(RepType.legal_message_flag, 'N') = 'N'
5800: AND reptype.reporting_type_id = rca_int.reporting_type_id
5801: )

Line 5815: AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'

5811: WHERE RepType.Reporting_Type_Id = RepUsage.Reporting_Type_Id
5812: AND RepUsage.Enabled_Flag= 'Y'
5813: AND SYSDATE BETWEEN RepType. EFFECTIVE_FROM
5814: AND NVL(RepType. EFFECTIVE_TO,SYSDATE)
5815: AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
5816: AND RepType.Has_Reporting_Codes_Flag = 'Y'
5817: AND NVL(RepType.legal_message_flag, 'N') = 'N'
5818: AND reptype.reporting_type_name = rca_int.reporting_type_name
5819: )

Line 5841: AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'

5837: WHERE RepType.Reporting_Type_Id = RepUsage.Reporting_Type_Id
5838: AND RepUsage.Enabled_Flag= 'Y'
5839: AND SYSDATE BETWEEN RepType. EFFECTIVE_FROM
5840: AND NVL(RepType. EFFECTIVE_TO,SYSDATE)
5841: AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
5842: AND RepType.Has_Reporting_Codes_Flag = 'Y'
5843: AND NVL(RepType.legal_message_flag, 'N') = 'N'
5844: AND RepType.Reporting_Type_Name = rca_int.Reporting_Type_Name
5845: )

Line 5965: UPDATE zx_party_tax_profile_int ptp_int

5961: IF (G_DEBUG_LEVEL_PROCEDURE >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
5962: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,G_DEBUG_LOG_HEADER||'PopulateProductionTables','PopulateProductionTables(-)');
5963: END IF;
5964:
5965: UPDATE zx_party_tax_profile_int ptp_int
5966: SET party_tax_profile_id = zx_party_tax_profile_s.nextval
5967: WHERE record_status = G_RS_VALID
5968: AND request_id = G_REQUEST_ID
5969: AND party_tax_profile_id is NULL

Line 5966: SET party_tax_profile_id = zx_party_tax_profile_s.nextval

5962: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,G_DEBUG_LOG_HEADER||'PopulateProductionTables','PopulateProductionTables(-)');
5963: END IF;
5964:
5965: UPDATE zx_party_tax_profile_int ptp_int
5966: SET party_tax_profile_id = zx_party_tax_profile_s.nextval
5967: WHERE record_status = G_RS_VALID
5968: AND request_id = G_REQUEST_ID
5969: AND party_tax_profile_id is NULL
5970: AND dml_type = 'CREATE';

Line 5981: FROM zx_party_tax_profile_int ptp_int

5977: UPDATE zx_registrations_int reg_int
5978: SET party_tax_profile_id =
5979: (
5980: SELECT party_tax_profile_id
5981: FROM zx_party_tax_profile_int ptp_int
5982: WHERE ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
5983: AND ptp_int.record_status = G_RS_VALID
5984: AND ptp_int.request_id = G_REQUEST_ID
5985: )

Line 5998: FROM zx_party_tax_profile_int ptp_int

5994: UPDATE zx_exemptions_int exe_int
5995: SET party_tax_profile_id =
5996: (
5997: SELECT party_tax_profile_id
5998: FROM zx_party_tax_profile_int ptp_int
5999: WHERE ptp_int.intf_ptp_reference = exe_int.intf_ptp_reference
6000: AND ptp_int.record_status = G_RS_VALID
6001: AND ptp_int.request_id = G_REQUEST_ID
6002: )

Line 6015: FROM zx_party_tax_profile_int ptp_int

6011: UPDATE zx_report_codes_assoc_int rca_int
6012: SET (party_tax_profile_id,entity_id) =
6013: (
6014: SELECT party_tax_profile_id,party_tax_profile_id
6015: FROM zx_party_tax_profile_int ptp_int
6016: WHERE ptp_int.intf_ptp_reference = rca_int.intf_ptp_reference
6017: AND ptp_int.record_status = G_RS_VALID
6018: AND ptp_int.request_id = G_REQUEST_ID
6019: )

Line 6028: INSERT INTO zx_party_tax_profile(

6024: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
6025: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PopulateProductionTables',G_DEBUG_STATEMENT);
6026: END IF;
6027:
6028: INSERT INTO zx_party_tax_profile(
6029: PARTY_TYPE_CODE,
6030: SUPPLIER_FLAG,
6031: CUSTOMER_FLAG,
6032: SITE_FLAG,

Line 6155: FROM zx_party_tax_profile_int

6151: REGISTRATION_TYPE_CODE,
6152: COUNTRY_CODE,
6153: NULL, --MERGED_TO_PTP_ID,
6154: NULL --MERGED_STATUS_CODE
6155: FROM zx_party_tax_profile_int
6156: WHERE record_status = G_RS_VALID
6157: AND request_id = G_REQUEST_ID
6158: AND error_number = 0
6159: );

Line 6161: G_DEBUG_STATEMENT:= 'Inserted '||SQL%ROWCOUNT||' records into zx_party_tax_profile ';

6157: AND request_id = G_REQUEST_ID
6158: AND error_number = 0
6159: );
6160:
6161: G_DEBUG_STATEMENT:= 'Inserted '||SQL%ROWCOUNT||' records into zx_party_tax_profile ';
6162: IF (G_DEBUG_LEVEL_STATEMENT >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
6163: FND_LOG.STRING(G_DEBUG_LEVEL_STATEMENT,G_DEBUG_LOG_HEADER||'PopulateProductionTables',G_DEBUG_STATEMENT);
6164: END IF;
6165: log_now (p_log_level => FND_FILE.LOG

Line 6494: FROM zx_party_tax_profile_int

6490: ------------------------------------------------------------------------------
6491: PROCEDURE LogErrorToTable IS
6492: CURSOR c_ptp_errors IS
6493: SELECT *
6494: FROM zx_party_tax_profile_int
6495: WHERE request_id = G_REQUEST_ID
6496: AND record_status = G_RS_ERROR;
6497:
6498: CURSOR c_reg_errors IS

Line 6500: FROM zx_registrations_int reg_int, zx_party_tax_profile_int ptp_int

6496: AND record_status = G_RS_ERROR;
6497:
6498: CURSOR c_reg_errors IS
6499: SELECT ptp_int.party_type_code, ptp_int.party_id, reg_int.*
6500: FROM zx_registrations_int reg_int, zx_party_tax_profile_int ptp_int
6501: WHERE reg_int.request_id = G_REQUEST_ID
6502: AND reg_int.record_status = G_RS_ERROR
6503: AND ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
6504: AND ptp_int.request_id = reg_int.request_id;

Line 6508: FROM zx_exemptions_int exe_int, zx_party_tax_profile_int ptp_int

6504: AND ptp_int.request_id = reg_int.request_id;
6505:
6506: CURSOR c_exe_errors IS
6507: SELECT ptp_int.party_type_code, ptp_int.party_id, exe_int.*
6508: FROM zx_exemptions_int exe_int, zx_party_tax_profile_int ptp_int
6509: WHERE exe_int.request_id = G_REQUEST_ID
6510: AND exe_int.record_status = G_RS_ERROR
6511: AND ptp_int.intf_ptp_reference = exe_int.intf_ptp_reference
6512: AND ptp_int.request_id = exe_int.request_id;

Line 6516: FROM zx_report_codes_assoc_int rca_int, zx_party_tax_profile_int ptp_int

6512: AND ptp_int.request_id = exe_int.request_id;
6513:
6514: CURSOR c_rca_errors IS
6515: SELECT ptp_int.party_type_code, ptp_int.party_id, rca_int.*
6516: FROM zx_report_codes_assoc_int rca_int, zx_party_tax_profile_int ptp_int
6517: WHERE rca_int.request_id = G_REQUEST_ID
6518: AND rca_int.record_status = G_RS_ERROR
6519: AND ptp_int.intf_ptp_reference = rca_int.intf_ptp_reference
6520: AND ptp_int.request_id = rca_int.request_id;

Line 6536: l_temp_char zx_party_tax_profile_int.intf_ptp_reference%TYPE;

6532:
6533: TYPE error_table_type IS TABLE OF ERR_REC_TYPE INDEX BY BINARY_INTEGER;
6534: l_error_table error_table_type;
6535: l_rec_index NUMBER(22);
6536: l_temp_char zx_party_tax_profile_int.intf_ptp_reference%TYPE;
6537: l_start_number NUMBER;
6538:
6539: BEGIN
6540:

Line 6545: UPDATE zx_party_tax_profile_int

6541: IF (G_DEBUG_LEVEL_PROCEDURE >= G_CURRENT_DEBUG_LEVEL_SET ) THEN
6542: FND_LOG.STRING(G_DEBUG_LEVEL_PROCEDURE,G_DEBUG_LOG_HEADER||'LogErrorToTable','LogErrorToTable(+)');
6543: END IF;
6544:
6545: UPDATE zx_party_tax_profile_int
6546: SET intf_ptp_reference = G_REQUEST_ID||'**'||ROWNUM
6547: WHERE request_id = G_REQUEST_ID
6548: AND record_status = G_RS_ERROR
6549: AND intf_ptp_reference IS NULL;

Line 7691: -- INSERT INTO zx_party_tax_profile_int

7687: -- ra_customers_interface_all
7688: --
7689: -- sample dynamic sql
7690: --
7691: -- INSERT INTO zx_party_tax_profile_int
7692: -- (registration_number,country_code,dml_type,intf_party_reference
7693: -- ,intf_party_site_reference,party_type_code,batch_id,request_id)
7694: ---------------------------------------------------
7695: -- p_sql_select_insert_ptp_intf is defined below

Line 7713: -- FROM zx_party_tax_profile_int zx_ptp_int

7709: -- p_sql_from_insert_ptp_intf ends here
7710: ---------------------------------------------------
7711: -- AND NOT EXISTS
7712: -- (SELECT 1
7713: -- FROM zx_party_tax_profile_int zx_ptp_int
7714: -- WHERE zx_ptp_int.record_status IS NULL
7715: -- AND NVL(zx_ptp_int.batch_id,G_MISS_NUM) = NVL(G_BATCH_ID,NVL(zx_ptp_int.batch_id,G_MISS_NUM))
7716: -- AND Nvl(zx_ptp_int.intf_party_reference,G_MISS_CHAR) = Nvl(Nvl(hz_rcia.orig_system_customer_ref,hz_rcia.orig_system_party_ref),G_MISS_CHAR)
7717: -- AND Nvl(zx_ptp_int.intf_party_site_reference,G_MISS_CHAR) = Nvl(hz_rcia.orig_system_address_ref,G_MISS_CHAR)

Line 7723: l_dynamic_sql := 'INSERT INTO zx_party_tax_profile_int '

7719: l_date2 := SYSDATE;
7720: IF p_sql_select_insert_ptp_intf IS NOT NULL
7721: AND p_sql_from_insert_ptp_intf IS NOT NULL THEN
7722: BEGIN
7723: l_dynamic_sql := 'INSERT INTO zx_party_tax_profile_int '
7724: ||' (registration_number ,country_code ,dml_type'
7725: ||' ,intf_party_reference ,intf_party_site_reference ,party_type_code'
7726: ||' ,batch_id)'
7727: ||' '|| p_sql_select_insert_ptp_intf

Line 7731: ||' (SELECT 1 FROM zx_party_tax_profile_int zx_ptp_int'

7727: ||' '|| p_sql_select_insert_ptp_intf
7728: ||' ,:G_BATCH_ID'
7729: ||' '||p_sql_from_insert_ptp_intf
7730: ||' AND NOT EXISTS '
7731: ||' (SELECT 1 FROM zx_party_tax_profile_int zx_ptp_int'
7732: ||' WHERE NVL(zx_ptp_int.record_status,'||G_RS_ERROR||') <> '||G_RS_VALID
7733: ||' AND NVL(zx_ptp_int.batch_id,'||G_MISS_NUM||') '
7734: ||'= '||NVL(G_BATCH_ID,G_MISS_NUM)
7735: ||' AND NVL(zx_ptp_int.intf_party_reference,'''||G_MISS_CHAR||''') '

Line 7813: l_dynamic_sql := 'MERGE INTO zx_party_tax_profile_int ptp_int'

7809: l_date6 := SYSDATE;
7810: IF p_sql_select_valid_party_id IS NOT NULL
7811: AND p_sql_from_valid_party_id IS NOT NULL THEN
7812: BEGIN
7813: l_dynamic_sql := 'MERGE INTO zx_party_tax_profile_int ptp_int'
7814: ||' USING ('||p_sql_select_valid_party_id
7815: ||' '||p_sql_from_valid_party_id ||') hz_int'
7816: ||' ON ('
7817: || ' ptp_int.request_id = '||G_REQUEST_ID

Line 7848: UPDATE zx_party_tax_profile_int zx_ptp_int

7844: END IF;
7845: END IF;
7846: l_date7 := SYSDATE;
7847:
7848: UPDATE zx_party_tax_profile_int zx_ptp_int
7849: SET record_status = NULL
7850: WHERE request_id = G_REQUEST_ID
7851: AND record_status = G_RS_VALID
7852: AND party_id IS NULL;

Line 7928: l_dynamic_sql := 'INSERT INTO ZX_PARTY_TAX_PROFILE'

7924: -- FROM hz_parties hz_insert
7925: -- WHERE request_id = G_REQUEST_ID
7926: BEGIN
7927:
7928: l_dynamic_sql := 'INSERT INTO ZX_PARTY_TAX_PROFILE'
7929: ||'(party_id,party_type_code,country_code'
7930: ||',party_tax_profile_id,creation_date,created_by,last_update_date'
7931: ||',last_updated_by,last_update_login,object_version_number,request_id'
7932: ||',program_application_id,program_id,record_type_code)'

Line 7934: ||' ,zx_party_tax_profile_s.nextval ,:g_date1 '

7930: ||',party_tax_profile_id,creation_date,created_by,last_update_date'
7931: ||',last_updated_by,last_update_login,object_version_number,request_id'
7932: ||',program_application_id,program_id,record_type_code)'
7933: || p_sql_select_insert_ptp_prod
7934: ||' ,zx_party_tax_profile_s.nextval ,:g_date1 '
7935: ||','||G_USER_ID||',:g_date2'
7936: ||','||G_USER_ID||','||G_LOGIN_ID||',1'||','||G_REQUEST_ID
7937: ||','||G_PROG_APPID||','||G_PROG_ID||','''||G_RECORD_TYPE_CODE||''''
7938: ||' '||p_sql_from_insert_ptp_prod||' '

Line 7940: ||' (SELECT 1 FROM zx_party_tax_profile_int zx_ptp_int'

7936: ||','||G_USER_ID||','||G_LOGIN_ID||',1'||','||G_REQUEST_ID
7937: ||','||G_PROG_APPID||','||G_PROG_ID||','''||G_RECORD_TYPE_CODE||''''
7938: ||' '||p_sql_from_insert_ptp_prod||' '
7939: ||' AND NOT EXISTS '
7940: ||' (SELECT 1 FROM zx_party_tax_profile_int zx_ptp_int'
7941: ||' WHERE zx_ptp_int.request_id = '||G_REQUEST_ID
7942: ||' AND zx_ptp_int.party_id = hz_insert.party_id'
7943: ||' AND zx_ptp_int.party_type_code = hz_insert.party_type_code'
7944: ||')';

Line 8074: FROM zx_errors_int err_int, zx_party_tax_profile_int ptp_int

8070: ,G_INTF_REG_TABLE_NAME,cp_msg_reg_table
8071: ,G_INTF_RCA_TABLE_NAME,cp_msg_rca_table
8072: ,' ') AS user_table_name
8073: ,err_int.message_text
8074: FROM zx_errors_int err_int, zx_party_tax_profile_int ptp_int
8075: WHERE err_int.request_id = G_REQUEST_ID
8076: AND err_int.request_id = ptp_int.request_id
8077: AND err_int.intf_ptp_reference = ptp_int.intf_ptp_reference
8078: ORDER BY intf_party_reference, intf_party_site_reference NULLS FIRST,

Line 8141: FROM zx_party_tax_profile_int

8137: l_date1 := SYSDATE;
8138:
8139: SELECT NVL(SUM(DECODE(NVL(record_status,G_RS_ERROR),G_RS_ERROR,1,0)),0), NVL(SUM(1),0)
8140: INTO l_count_exception_records, l_count_success_records
8141: FROM zx_party_tax_profile_int
8142: WHERE request_id = G_REQUEST_ID;
8143: fnd_message.set_name('ZX','ZX_TOT_IMPORTED_RECORDS');
8144: l_tot_records_msg := fnd_message.get();
8145: fnd_message.set_name('ZX','ZX_TOT_EXCEPTION_RECORDS');