[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LOGIN_ID fnd_user.last_update_login%TYPE;
PROCEDURE DELETE_SUCCESS_RECORDS
(p_request_id IN NUMBER
,p_commit IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
G_PROC_NAME := 'DELETE_SUCCESS_RECORDS';
DELETE zx_party_tax_profile_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID;
G_DEBUG_STATEMENT:='Deleted '||SQL%ROWCOUNT||' records from '||G_INTF_PTP_TABLE_NAME;
DELETE zx_registrations_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID;
G_DEBUG_STATEMENT:='Deleted '||SQL%ROWCOUNT||' records from '||G_INTF_REG_TABLE_NAME;
DELETE zx_exemptions_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID;
G_DEBUG_STATEMENT:='Deleted '||SQL%ROWCOUNT||' records from '||G_INTF_EXE_TABLE_NAME;
DELETE zx_report_codes_assoc_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID;
G_DEBUG_STATEMENT:='Deleted '||SQL%ROWCOUNT||' records from '||G_INTF_RCA_TABLE_NAME;
END DELETE_SUCCESS_RECORDS;
UPDATE zx_party_tax_profile_int ptp_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_err
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_ERROR
AND ptp_err.intf_party_reference = ptp_int.intf_party_reference
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:='Updated Record Status for '||SQL%ROWCOUNT||' records from '||G_INTF_PTP_TABLE_NAME;
UPDATE zx_registrations_int reg_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_int
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_ERROR
AND ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:='Updated Record Status for '||SQL%ROWCOUNT||' records from '||G_INTF_REG_TABLE_NAME;
UPDATE zx_exemptions_int exe_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_int
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_ERROR
AND ptp_int.intf_ptp_reference = exe_int.intf_ptp_reference
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:='Updated Record Status for '||SQL%ROWCOUNT||' records from '||G_INTF_EXE_TABLE_NAME;
UPDATE zx_report_codes_assoc_int rca_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_int
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_ERROR
AND ptp_int.intf_ptp_reference = rca_int.intf_ptp_reference
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:='Updated Record Status for '||SQL%ROWCOUNT||' records from '||G_INTF_RCA_TABLE_NAME;
,p_insert_only IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
) IS
---------------------------------------------------------------------------
-- Start of comments
-- API name : Import_Ptp
-- Type : Protected (only for ZX Use not for customer call)
-- Pre-reqs : None
-- FUNCTION : To bulkload the Party related Tax information from the
-- Interface tables into Production tables after validations
--
-- Parameters:
-- IN :
--
--
-- OUT : x_return_status VARCHAR2
-- status of the API
-- 'S' if the program is success
-- 'W' if the program terminated successfully with atleast an
-- error reported onto error table
-- 'E' if the program has an exception
-- 'U' if the program has an unhandled exception
-- x_msg_data VARCHAR2
-- contains data only if return status is 'U' or 'E'
--
---------------------------------------------------------------------------
l_temp_char_30 VARCHAR2(30);
SELECT 'x'
INTO l_temp_char_30
FROM ZX_PARTY_TAX_PROFILE_INT
WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM))
AND ROWNUM = 1;
IF FND_API.TO_BOOLEAN(p_insert_only) THEN
BEGIN
SELECT 'x'
INTO l_temp_char_30
FROM ZX_PARTY_TAX_PROFILE_INT
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND ROWNUM = 1;
G_DEBUG_STATEMENT:='Insert Mode : There are records to Process in PTP';
SELECT owner
INTO l_temp_char_30
FROM sys.all_objects
WHERE object_name = G_INTF_PTP_TABLE_NAME
AND object_type = 'TABLE';
SELECT G_INTF_REG_TABLE_NAME
INTO l_temp_char_30
FROM ZX_REGISTRATIONS_INT
WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM))
AND ROWNUM = 1;
SELECT G_INTF_EXE_TABLE_NAME
INTO l_temp_char_30
FROM ZX_EXEMPTIONS_INT
WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM))
AND ROWNUM = 1;
SELECT G_INTF_RCA_TABLE_NAME
INTO l_temp_char_30
FROM ZX_REPORT_CODES_ASSOC_INT
WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM))
AND ROWNUM = 1;
IF FND_API.to_boolean(p_insert_only) THEN
PopulateProductionTables();
SELECT DISTINCT country_code
FROM zx_party_tax_profile_int
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND country_code IN ('TW','SK','SI','SE','RU','PT','PL','NL','MT',
'LV','LU','LT','IT','IE','HU','GR','GB','FR',
'FI','ES','EE','DK','DE','CY','CO','CL','CH',
'AR','AT','BE','BR')
AND BITAND(error_number,G_ES_PTP_COUNTRY_INVALID) = 0;
UPDATE zx_party_tax_profile_int
SET request_id = G_REQUEST_ID
,record_status = G_RS_IN_PROCESS
,dml_type = UPPER(dml_type)
,prog_int_char1 = NULL
,prog_int_num1 = 0
,error_number = 0
WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM));
UPDATE zx_party_tax_profile_int
SET error_number = G_ES_PTP_MAND_PARAM_MISSING
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND party_id IS NULL
AND party_name IS NULL
AND intf_party_reference IS NULL
AND intf_party_site_reference IS NULL;
UPDATE zx_party_tax_profile_int orig
SET error_number = error_number + G_ES_PTP_DUP_INTF_RECORD,
record_status = G_RS_ERROR
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int dup
WHERE dup.rowid <> orig.rowid
AND dup.request_id = G_REQUEST_ID
AND dup.record_status = G_RS_IN_PROCESS
AND NVL(dup.intf_party_reference,G_MISS_CHAR) =
NVL(orig.intf_party_reference,G_MISS_CHAR)
AND NVL(dup.intf_party_site_reference,G_MISS_CHAR) =
NVL(orig.intf_party_site_reference,G_MISS_CHAR)
AND dup.party_type_code = orig.party_type_code
AND ROWNUM = 1
);
UPDATE zx_party_tax_profile_int ptp_int
SET PROG_INT_NUM1 = G_PROG_INT_VAL
WHERE request_id = G_REQUEST_ID
AND (country_code IS NULL OR registration_number IS NULL)
AND record_status = G_RS_IN_PROCESS;
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_REG_NUM_INVALID
WHERE country_code IS NOT NULL
AND country_code IN ('TW','SK','SI','SE','RU','PT','PL','NL','MT',
'LV','LU','LT','IT','IE','HU','GR','GB','FR',
'FI','ES','EE','DK','DE','CY','CO','CL','CH',
'AR','AT','BE','BR')
AND registration_number IS NOT NULL
AND prog_int_num1 <> G_PROG_INT_VAL
AND request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS;
UPDATE zx_party_tax_profile_int ptp_int
SET record_status = DECODE(error_number,0,G_RS_VALID,G_RS_ERROR),
prog_int_num1 = 0
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS;
UPDATE zx_party_tax_profile_int
SET error_number = error_number + G_ES_PTP_TRANS_TYPE_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND dml_type <> 'CREATE';
UPDATE zx_party_tax_profile_int
SET error_number = error_number + G_ES_PTP_PARTY_TYPE_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND party_type_code NOT IN ('THIRD_PARTY','THIRD_PARTY_SITE');
UPDATE zx_party_tax_profile_int intf
SET error_number = error_number + G_ES_PTP_PARTY_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND party_type_code = 'THIRD_PARTY'
AND dml_type = 'UPDATE'
AND ( ( party_id IS NOT NULL
AND NOT EXISTS
(SELECT 1 FROM hz_parties WHERE party_id = intf.party_id)
)
OR
( party_id IS NULL
AND party_name IS NOT NULL
AND NOT EXISTS
(SELECT 1 FROM hz_parties WHERE party_name = intf.party_name)
)
OR
( party_id IS NULL
AND party_name IS NULL
AND intf_party_reference IS NOT NULL
AND NOT EXISTS
(SELECT 1 FROM hz_parties
WHERE orig_system_reference = intf.intf_party_reference)
)
);
UPDATE zx_party_tax_profile_int intf
SET party_id = CASE WHEN party_id IS NULL AND party_name IS NOT NULL
THEN (SELECT party_id
FROM hz_parties
WHERE party_name = intf.party_name)
WHEN party_id is NULL
AND party_name IS NULL
AND intf_party_reference IS NOT NULL
THEN (SELECT 1
FROM hz_parties
WHERE orig_system_reference =
intf.intf_party_reference)
END
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_PTP_PARTY_INVALID) = 0
AND party_type_code = 'THIRD_PARTY'
AND dml_type = 'UPDATE';
UPDATE zx_party_tax_profile_int
SET error_number = error_number + G_ES_PTP_INC_FLG_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND inclusive_tax_flag NOT IN ('Y','N',NULL);
UPDATE zx_party_tax_profile_int
SET error_number = error_number + G_ES_PTP_APP_FLG_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND process_for_applicability_flag NOT IN ('Y','N',NULL);
UPDATE zx_party_tax_profile_int
SET error_number = error_number + G_ES_PTP_OFF_FLG_RECORD
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND allow_offset_tax_flag NOT IN ('Y','N',NULL);
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_COUNTRY_INVALID
WHERE (( country_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_territories
WHERE territory_code = ptp_int.country_code
)
)
OR
( country_code IS NULL
AND country_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_territories_vl
WHERE territory_short_name = ptp_int.country_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_party_tax_profile_int ptp_int
SET country_code = (
SELECT territory_code
FROM fnd_territories_vl
WHERE territory_short_name = ptp_int.country_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_PTP_COUNTRY_INVALID) = 0
AND country_code IS NULL
AND country_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with country_code';
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_REG_TYPE_INVALID
WHERE (( registration_type_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.lookup_code = ptp_int.registration_type_code
)
)
OR
( registration_type_code IS NULL
AND registration_type_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = ptp_int.registration_type_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_party_tax_profile_int ptp_int
SET registration_type_code = (
SELECT fndlookup.lookup_code
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = ptp_int.registration_type_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_PTP_REG_TYPE_INVALID) = 0
AND registration_type_code IS NULL
AND ptp_int.registration_type_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with registration_type_code';
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_ROUND_LEVEL_INVALID
WHERE (( rounding_level_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_LEVEL'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND lookup_code = ptp_int.rounding_level_code
)
)
OR
( rounding_level_code IS NULL
AND rounding_level_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_LEVEL'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND meaning = ptp_int.rounding_level_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_party_tax_profile_int ptp_int
SET rounding_level_code =
(
SELECT lookup_code
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_LEVEL'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND meaning = ptp_int.rounding_level_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_PTP_ROUND_LEVEL_INVALID) = 0
AND rounding_level_code IS NULL
AND rounding_level_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with rounding_level_code';
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_ROUND_RULE_INVALID
WHERE (( rounding_rule_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_RULE'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND lookup_code = ptp_int.rounding_rule_code
)
)
OR
( rounding_rule_code IS NULL
AND rounding_rule_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_RULE'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND meaning = ptp_int.rounding_rule_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_party_tax_profile_int ptp_int
SET rounding_rule_code = (
SELECT lookup_code
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_RULE'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND meaning = ptp_int.rounding_rule_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_PTP_ROUND_RULE_INVALID) = 0
AND rounding_rule_code IS NULL
AND rounding_rule_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with rounding_rule_code';
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_TAX_CLASS_INVALID
WHERE (( tax_classification_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_input_classifications_v
WHERE enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE)
AND lookup_code = ptp_int.tax_classification_code
UNION ALL
SELECT 1
FROM zx_output_classifications_v
WHERE enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE)
AND lookup_code = ptp_int.tax_classification_code
)
)
OR
( tax_classification_code IS NULL
AND tax_classification_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_input_classifications_v
WHERE enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE)
AND meaning = ptp_int.tax_classification_name
UNION ALL
SELECT 1
FROM zx_output_classifications_v
WHERE enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE)
AND meaning = ptp_int.tax_classification_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_party_tax_profile_int ptp_int
SET tax_classification_code = (
SELECT lookup_code
FROM zx_input_classifications_v
WHERE enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE)
AND meaning = ptp_int.tax_classification_name
UNION
SELECT lookup_code
FROM zx_output_classifications_v
WHERE enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE)
AND meaning = ptp_int.tax_classification_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_PTP_TAX_CLASS_INVALID) = 0
AND tax_classification_code IS NULL
AND tax_classification_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with Tax Classification code';
' UPDATE '||p_table_name||
' SET PROG_INT_NUM1 = :G_PROG_INT_VAL
WHERE request_id = :G_REQUEST_ID
AND record_status = :G_RS_IN_PROCESS
AND registration_number IS NOT NULL
AND '||p_column_name||' = :P_COUNTRY_CODE
AND BITAND(error_number,:G_ES_PTP_COUNTRY_INVALID) = 0
AND prog_int_num1 <> :G_PROG_INT_VAL ';
(SELECT 1
FROM DUAL
CONNECT BY LEVEL<8
HAVING (Mod(SUM(SUBSTR(registration_number,LEVEL+2,1)*(9-LEVEL)),97)-97 + SUBSTR(registration_number,-2,2) = 0) OR
(Mod(SUM(SUBSTR(registration_number,LEVEL+2,1)*(9-LEVEL)),97)-97 + 55 + SUBSTR(registration_number,-2,2) = 0) OR
(Mod(SUM(SUBSTR(registration_number,LEVEL+2,1)*(9-LEVEL)),97)-97 + 55 -97 + SUBSTR(registration_number,-2,2) = 0))
ELSE :G_MISS_NUM END
) #' ;
' UPDATE '||p_table_name||
' SET PROG_INT_NUM1 = '||
q'# (MOD((nvl(TO_NUMBER(SUBSTR(registration_number,-2,1)),0) *3 +
nvl(TO_NUMBER(SUBSTR(registration_number,-3,1)),0) *7 +
nvl(TO_NUMBER(SUBSTR(registration_number,-4,1)),0) *13 +
nvl(TO_NUMBER(SUBSTR(registration_number,-5,1)),0) *17 +
nvl(TO_NUMBER(SUBSTR(registration_number,-6,1)),0) *19 +
nvl(TO_NUMBER(SUBSTR(registration_number,-7,1)),0) *23 +
nvl(TO_NUMBER(SUBSTR(registration_number,-8,1)),0) *29 +
nvl(TO_NUMBER(SUBSTR(registration_number,-9,1)),0) *37 +
nvl(TO_NUMBER(SUBSTR(registration_number,-10,1)),0) *41 +
nvl(TO_NUMBER(SUBSTR(registration_number,-11,1)),0) *43 +
nvl(TO_NUMBER(SUBSTR(registration_number,-12,1)),0) *47 +
nvl(TO_NUMBER(SUBSTR(registration_number,-13,1)),0) *53 +
nvl(TO_NUMBER(SUBSTR(registration_number,-14,1)),0) *59 +
nvl(TO_NUMBER(SUBSTR(registration_number,-15,1)),0) *67 +
nvl(TO_NUMBER(SUBSTR(registration_number,-16,1)),0) *71),11))#'||
' WHERE request_id = :G_REQUEST_ID
AND record_status = :G_RS_IN_PROCESS
AND registration_number IS NOT NULL
AND '||p_column_name||' = :P_COUNTRY_CODE
AND BITAND(error_number,:G_ES_PTP_COUNTRY_INVALID) = 0
AND prog_int_num1 <> :G_PROG_INT_VAL'||
q'# AND REGEXP_LIKE(registration_number,'^[[:digit:]]{0,16}$') #';
' UPDATE '||p_table_name||
' SET PROG_INT_NUM1 = :G_PROG_INT_VAL
WHERE request_id = :G_REQUEST_ID
AND record_status = :G_RS_IN_PROCESS
AND registration_number IS NOT NULL
AND '||p_column_name||' = :P_COUNTRY_CODE
AND BITAND(error_number,:G_ES_PTP_COUNTRY_INVALID) = 0
AND DECODE(PROG_INT_NUM1,1,PROG_INT_NUM1,0,PROG_INT_NUM1,11-PROG_INT_NUM1)
= nvl((SUBSTR(registration_number,-1,1)),0)
AND prog_int_num1 <> :G_PROG_INT_VAL'||
q'# AND REGEXP_LIKE(registration_number,'^[[:digit:]]{0,16}$') #';
(SELECT
DECODE(ceil(((REGEXP_SUBSTR(num,'[0-9]',1,1))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,2))*4+
(REGEXP_SUBSTR(num,'[0-9]',1,3))*3+
(REGEXP_SUBSTR(num,'[0-9]',1,4))*2+
(REGEXP_SUBSTR(num,'[0-9]',1,5))*7+
(REGEXP_SUBSTR(num,'[0-9]',1,6))*6+
(REGEXP_SUBSTR(num,'[0-9]',1,7))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,8))*4
)/11) * 11 -
((REGEXP_SUBSTR(num,'[0-9]',1,1))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,2))*4+
(REGEXP_SUBSTR(num,'[0-9]',1,3))*3+
(REGEXP_SUBSTR(num,'[0-9]',1,4))*2+
(REGEXP_SUBSTR(num,'[0-9]',1,5))*7+
(REGEXP_SUBSTR(num,'[0-9]',1,6))*6+
(REGEXP_SUBSTR(num,'[0-9]',1,7))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,8))*4),
10,'INVALID',
(REGEXP_SUBSTR(num,'[0-9]',1,9)),'VALID',
'INVALID') FINAL_RESULT
FROM (SELECT
REGEXP_SUBSTR(REPLACE(REGEXP_SUBSTR(registration_number,'^(CHE-[0-9]{3}.[0-9]{3}.[0-9]{3}) (MWST|TVA|IVA)$'),'.',''),'[0-9]+') NUM
FROM DUAL
)
WHERE REGEXP_SUBSTR(registration_number,'^(CHE-[0-9]{3}.[0-9]{3}.[0-9]{3}) (MWST|TVA|IVA)$')=registration_number
)
)
#' ;
l_dynamic_sql := ' UPDATE '||p_table_name||
' SET PROG_INT_NUM1 = '||
q'# DECODE((11 - mod(
(to_number(substr(registration_number,9,1)) * 2 +
to_number(substr(registration_number,8,1)) * 3 +
to_number(substr(registration_number,7,1)) * 4 +
to_number(substr(registration_number,6,1)) * 5 +
to_number(substr(registration_number,5,1)) * 6 +
to_number(substr(registration_number,4,1)) * 7 +
to_number(substr(registration_number,3,1)) * 8 +
to_number(substr(registration_number,2,1)) * 9 +
to_number(substr(registration_number,1,1)) * 10),11)),10,0,10,0,
(11 - mod(
(to_number(substr(registration_number,9,1)) * 2 +
to_number(substr(registration_number,8,1)) * 3 +
to_number(substr(registration_number,7,1)) * 4 +
to_number(substr(registration_number,6,1)) * 5 +
to_number(substr(registration_number,5,1)) * 6 +
to_number(substr(registration_number,4,1)) * 7 +
to_number(substr(registration_number,3,1)) * 8 +
to_number(substr(registration_number,2,1)) * 9 +
to_number(substr(registration_number,1,1)) * 10),11))) #'
||' WHERE request_id = :G_REQUEST_ID'
||' AND record_status = :G_RS_IN_PROCESS'
||' AND registration_number IS NOT NULL'
||' AND '||p_column_name||' = :P_COUNTRY_CODE'
||' AND BITAND(error_number,:G_ES_PTP_COUNTRY_INVALID) = 0'
||' AND prog_int_num1 <> :G_PROG_INT_VAL' ||
q'# AND NVL(REGISTRATION_TYPE_CODE,'CNPJ') = 'CPF'
AND REGEXP_LIKE(REGISTRATION_NUMBER,'^(0000)*[[:digit:]]{11}$') #' ;
l_dynamic_sql := ' UPDATE '||p_table_name
||' SET PROG_INT_NUM1 = :G_PROG_INT_VAL '
||' WHERE request_id = :G_REQUEST_ID'
||' AND record_status = :G_RS_IN_PROCESS'
||' AND registration_number IS NOT NULL'
||' AND '||p_column_name||' = :P_COUNTRY_CODE'
||' AND BITAND(error_number,:G_ES_PTP_COUNTRY_INVALID) = 0'
||' AND prog_int_num1 <> :G_PROG_INT_VAL' ||
q'# AND NVL(REGISTRATION_TYPE_CODE,'CNPJ') = 'CPF'
AND REGEXP_LIKE(REGISTRATION_NUMBER,'^(0000)*[[:digit:]]{11}$')
AND substr(to_char(PROG_INT_NUM1),1,1) = = substr(REGISTRATION_NUMBER,-2,1)
AND substr(to_char(Decode((11 - mod((PROG_INT_NUM1 * 2 +
to_number(substr(registration_number,9,1)) * 3 +
to_number(substr(registration_number,8,1)) * 4 +
to_number(substr(registration_number,7,1)) * 5 +
to_number(substr(registration_number,6,1)) * 6 +
to_number(substr(registration_number,5,1)) * 7 +
to_number(substr(registration_number,4,1)) * 8 +
to_number(substr(registration_number,3,1)) * 9 +
to_number(substr(registration_number,2,1)) * 10 +
to_number(substr(registration_number,1,1)) * 11),11)),11,0,10,0,
(11 - mod((PROG_INT_NUM1 * 2 +
to_number(substr(registration_number,9,1)) * 3 +
to_number(substr(registration_number,8,1)) * 4 +
to_number(substr(registration_number,7,1)) * 5 +
to_number(substr(registration_number,6,1)) * 6 +
to_number(substr(registration_number,5,1)) * 7 +
to_number(substr(registration_number,4,1)) * 8 +
to_number(substr(registration_number,3,1)) * 9 +
to_number(substr(registration_number,2,1)) * 10 +
to_number(substr(registration_number,1,1)) * 11),11)))),1,1)
= substr(registration_number,-1,1) #' ;
l_dynamic_sql := ' UPDATE '||p_table_name
||' SET PROG_INT_NUM1 = '||
q'# DECODE((11 - mod(
(to_number(substr(registration_number,-3,1)) * 2 +
to_number(substr(registration_number,-4,1)) * 3 +
to_number(substr(registration_number,-5,1)) * 4 +
to_number(substr(registration_number,-6,1)) * 5 +
to_number(substr(registration_number,-7,1)) * 6 +
to_number(substr(registration_number,-8,1)) * 7 +
to_number(substr(registration_number,-9,1)) * 8 +
to_number(substr(registration_number,-10,1)) * 9 +
to_number(substr(registration_number,-11,1)) * 2 +
to_number(substr(registration_number,-12,1)) * 3 +
to_number(substr(registration_number,-13,1)) * 4 +
to_number(substr(registration_number,-14,1))* 5),11)),10,0,10,0,
(11 - mod(
(to_number(substr(registration_number,-3,1)) * 2 +
to_number(substr(registration_number,-4,1)) * 3 +
to_number(substr(registration_number,-5,1)) * 4 +
to_number(substr(registration_number,-6,1)) * 5 +
to_number(substr(registration_number,-7,1)) * 6 +
to_number(substr(registration_number,-8,1)) * 7 +
to_number(substr(registration_number,-9,1)) * 8 +
to_number(substr(registration_number,-10,1)) * 9 +
to_number(substr(registration_number,-11,1)) * 2 +
to_number(substr(registration_number,-12,1)) * 3 +
to_number(substr(registration_number,-13,1)) * 4 +
to_number(substr(registration_number,-14,1))* 5),11))) #'
||' WHERE request_id = :G_REQUEST_ID'
||' AND record_status = :G_RS_IN_PROCESS'
||' AND registration_number IS NOT NULL'
||' AND '||p_column_name||' = :P_COUNTRY_CODE'
||' AND BITAND(error_number,:G_ES_PTP_COUNTRY_INVALID) = 0'
||' AND prog_int_num1 <> :G_PROG_INT_VAL' ||
q'# AND NVL(REGISTRATION_TYPE_CODE,'CNPJ') = 'CNPJ'
AND REGEXP_LIKE(registration_number,'^[[:digit:]]?[[:digit:]]{14}$') #' ;
l_dynamic_sql := ' UPDATE '||p_table_name
||' SET PROG_INT_NUM1 = :G_PROG_INT_VAL '
||' WHERE request_id = :G_REQUEST_ID'
||' AND record_status = :G_RS_IN_PROCESS'
||' AND registration_number IS NOT NULL'
||' AND '||p_column_name||' = :P_COUNTRY_CODE'
||' AND BITAND(error_number,:G_ES_PTP_COUNTRY_INVALID) = 0'
||' AND prog_int_num1 <> :G_PROG_INT_VAL' ||
q'# AND NVL(REGISTRATION_TYPE_CODE,'CNPJ') = 'CNPJ'
AND REGEXP_LIKE(registration_number,'^[[:digit:]]?[[:digit:]]{14}$')
AND substr(to_char(PROG_INT_NUM1),1,1) = = substr(registration_number,-2,1)
AND substr(to_char(Decode((11 - mod(((l_control_digit_1 * 2) +
to_number(substr(registration_number,-3,1)) * 3 +
to_number(substr(registration_number,-4,1)) * 4 +
to_number(substr(registration_number,-5,1)) * 5 +
to_number(substr(registration_number,-6,1)) * 6 +
to_number(substr(registration_number,-7,1)) * 7 +
to_number(substr(registration_number,-8,1)) * 8 +
to_number(substr(registration_number,-9,1)) * 9 +
to_number(substr(registration_number,-10,1)) * 2 +
to_number(substr(registration_number,-11,1)) * 3 +
to_number(substr(registration_number,-12,1)) * 4 +
to_number(substr(registration_number,-13,1)) * 5 +
to_number(substr(registration_number,-14,1)) * 6),11)),11,0,10,0,
(11 - mod(((l_control_digit_1 * 2) +
to_number(substr(registration_number,-3,1)) * 3 +
to_number(substr(registration_number,-4,1)) * 4 +
to_number(substr(registration_number,-5,1)) * 5 +
to_number(substr(registration_number,-6,1)) * 6 +
to_number(substr(registration_number,-7,1)) * 7 +
to_number(substr(registration_number,-8,1)) * 8 +
to_number(substr(registration_number,-9,1)) * 9 +
to_number(substr(registration_number,-10,1)) * 2 +
to_number(substr(registration_number,-11,1)) * 3 +
to_number(substr(registration_number,-12,1)) * 4 +
to_number(substr(registration_number,-13,1)) * 5 +
to_number(substr(registration_number,-14,1)) * 6),11)))),1,1)
= substr(registration_number,-1,1) #' ;
SELECT DISTINCT prog_int_char1
FROM zx_registrations_int
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID) = 0
AND prog_int_char1 IS NOT NULL
AND prog_int_char1 IN ('TW','SK','SI','SE','RU','PT','PL','NL','MT',
'LV','LU','LT','IT','IE','HU','GR','GB','FR',
'FI','ES','EE','DK','DE','CY','CO','CL','CH',
'AR','AT','BE','BR');
UPDATE zx_registrations_int
SET request_id = G_REQUEST_ID
,record_status = G_RS_IN_PROCESS
,dml_type = UPPER(dml_type)
,prog_int_char1 = NULL
,prog_int_num1 = 0
,error_number = 0
WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM));
UPDATE zx_registrations_int
SET error_number = G_ES_REG_MAND_PARAM_MISSING
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND NVL(tax_regime_code,tax_regime_name) IS NULL;
UPDATE zx_registrations_int orig
SET error_number = error_number + G_ES_REG_DUP_INTF_RECORD,
record_status = G_RS_ERROR
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND error_number <> G_ES_REG_MAND_PARAM_MISSING
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations_int dup
WHERE dup.rowid <> orig.rowid
AND dup.request_id = G_REQUEST_ID
AND dup.record_status = G_RS_IN_PROCESS
AND NVL(dup.party_tax_profile_id,G_MISS_NUM)
= NVL(orig.party_tax_profile_id,G_MISS_NUM)
AND NVL(dup.intf_ptp_reference,G_MISS_CHAR)
= NVL(orig.intf_ptp_reference,G_MISS_CHAR)
AND NVL(dup.tax_regime_code,G_MISS_CHAR) = NVL(orig.tax_regime_code,G_MISS_CHAR)
AND NVL(dup.tax_regime_name,G_MISS_CHAR) = NVL(orig.tax_regime_name,G_MISS_CHAR)
AND NVL(dup.tax_code,G_MISS_CHAR) = NVL(orig.tax_code,G_MISS_CHAR)
AND NVL(dup.tax_name,G_MISS_CHAR) = NVL(orig.tax_name,G_MISS_CHAR)
AND NVL(dup.tax_jurisdiction_code,G_MISS_CHAR) =
NVL(orig.tax_jurisdiction_code,G_MISS_CHAR)
AND NVL(dup.tax_jurisdiction_name,G_MISS_CHAR) =
NVL(orig.tax_jurisdiction_name,G_MISS_CHAR)
AND dup.effective_from = orig.effective_from
AND ROWNUM = 1
);
UPDATE zx_registrations_int orig
SET error_number = error_number + G_ES_REG_DATE_OVERLAP,
record_status = G_RS_ERROR
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND BITAND(error_number,G_ES_REG_DUP_INTF_RECORD) = 0
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations_int dup
WHERE dup.rowid <> orig.rowid
AND dup.request_id = G_REQUEST_ID
AND dup.record_status = G_RS_IN_PROCESS
AND dup.intf_ptp_reference = orig.intf_ptp_reference
AND dup.tax_regime_code = orig.tax_regime_code
AND NVL(dup.tax_code,G_MISS_CHAR) = NVL(orig.tax_code,G_MISS_CHAR)
AND NVL(dup.tax_jurisdiction_code,G_MISS_CHAR) =
NVL(orig.tax_jurisdiction_code,G_MISS_CHAR)
AND BITAND(dup.error_number,G_ES_REG_TAX_REG_INVALID+
G_ES_REG_TAX_PARAM_INVALID+
G_ES_REG_TAX_JUR_INVALID+
G_ES_REG_DUP_INTF_RECORD) = 0
AND ( dup.effective_to IS NULL
OR orig.effective_from <= dup.effective_to
)
AND ( orig.effective_to IS NULL
OR orig.effective_to >= dup.effective_from
)
AND ROWNUM = 1
);
UPDATE ZX_REGISTRATIONS_INT REG
SET PROG_INT_CHAR1 = (SELECT COUNTRY_CODE
FROM ZX_REGIMES_B TR
WHERE TR.TAX_REGIME_CODE = REG.TAX_REGIME_CODE)
WHERE request_id = G_REQUEST_ID
AND PROG_INT_CHAR1 IS NULL
AND TAX_REGIME_CODE IS NOT NULL
AND record_status = G_RS_IN_PROCESS
AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID) = 0;
UPDATE zx_registrations_int reg_int
SET PROG_INT_NUM1 = G_PROG_INT_VAL
WHERE request_id = G_REQUEST_ID
AND prog_int_char1 IS NULL
OR registration_number IS NULL
AND record_status = G_RS_IN_PROCESS;
UPDATE zx_registrations_int reg
SET error_number = error_number + G_ES_REG_REG_NUM_INVALID
WHERE prog_int_char1 IS NOT NULL
AND prog_int_char1 IN ('TW','SK','SI','SE','RU','PT','PL','NL','MT',
'LV','LU','LT','IT','IE','HU','GR','GB','FR',
'FI','ES','EE','DK','DE','CY','CO','CL','CH',
'AR','AT','BE','BR')
AND registration_number IS NOT NULL
AND prog_int_num1 <> G_PROG_INT_VAL
AND request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS;
UPDATE zx_registrations_int orig
SET error_number = error_number + G_ES_REG_MULT_DEF_FLAG
WHERE orig.record_status = G_RS_IN_PROCESS
AND orig.request_id = G_REQUEST_ID
AND BITAND(orig.error_number,G_ES_REG_DEFREG_FLG_INVALID) = 0
AND NVL(orig.default_registration_flag,'N') = 'Y'
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations_int dup
WHERE dup.rowid <> orig.rowid
AND dup.request_id = G_REQUEST_ID
AND dup.record_status = G_RS_IN_PROCESS
AND NVL(dup.default_registration_flag,'N') = 'Y'
AND BITAND(dup.error_number,G_ES_REG_DEFREG_FLG_INVALID) = 0
AND dup.intf_ptp_reference = orig.intf_ptp_reference
AND ROWNUM = 1
);
UPDATE zx_registrations_int
SET error_number = error_number + G_ES_REG_TAX_AUT_REQ
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_AUT_INVALID) = 0
AND NVL(registration_status_code,G_MISS_CHAR) = 'REGISTERED'
AND NVL(registration_source_code,G_MISS_CHAR) = 'EXPLICIT'
AND tax_authority_id IS NULL;
UPDATE zx_registrations_int reg_int
SET record_status = DECODE(error_number,0,G_RS_VALID,G_RS_ERROR)
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS;
UPDATE zx_party_tax_profile_int ptp_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations_int reg_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_ERROR
AND reg_int.intf_ptp_reference = ptp_int.intf_ptp_reference);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' PTP records updated with NULL record_status';
UPDATE zx_registrations_int
SET error_number = error_number + G_ES_REG_TRANS_TYPE_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND dml_type <> 'CREATE';
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid dml_type';
UPDATE zx_registrations_int
SET error_number = error_number + G_ES_REG_DATE_RANGE_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND effective_to IS NOT NULL
AND effective_to < effective_from;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid date ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_REG_TYPE_INVALID
WHERE (( REGISTRATION_TYPE_CODE IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.lookup_code = reg_int.REGISTRATION_TYPE_CODE
)
)
OR
( REGISTRATION_TYPE_CODE IS NULL
AND REGISTRATION_TYPE_NAME IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = reg_int.REGISTRATION_TYPE_NAME
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid registration_type ';
UPDATE zx_registrations_int reg_int
SET registration_type_code = (
SELECT fndlookup.lookup_code
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = reg_int.REGISTRATION_TYPE_NAME
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_REG_TYPE_INVALID) = 0
AND registration_type_code IS NULL
AND reg_int.REGISTRATION_TYPE_NAME IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid registration_type_code ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_REG_STAT_INVALID
WHERE (( registration_status_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_STATUS'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.lookup_code = reg_int.registration_status_code
)
)
OR
( registration_status_code IS NULL
AND registration_status_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_STATUS'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = reg_int.registration_status_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid registration_status ';
UPDATE zx_registrations_int reg_int
SET registration_status_code = (
SELECT fndlookup.lookup_code
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_STATUS'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = reg_int.registration_status_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_REG_STAT_INVALID) = 0
AND registration_status_code IS NULL
AND reg_int.registration_status_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid registration_status_code ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_REG_SRC_INVALID
WHERE (( registration_source_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_SOURCE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.lookup_code = reg_int.registration_source_code
)
)
OR
( registration_source_code IS NULL
AND registration_source_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_SOURCE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = reg_int.registration_source_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid registration_source ';
UPDATE zx_registrations_int reg_int
SET registration_source_code = (
SELECT fndlookup.lookup_code
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_SOURCE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = reg_int.registration_source_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_REG_SRC_INVALID) = 0
AND registration_source_code IS NULL
AND registration_source_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid registration_source_code ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_REG_RSN_INVALID
WHERE (( registration_reason_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_REASON'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.lookup_code = reg_int.registration_reason_code
)
)
OR
( registration_reason_code IS NULL
AND registration_reason_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_REASON'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = reg_int.registration_reason_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid registration_reason ';
UPDATE zx_registrations_int reg_int
SET registration_reason_code = (
SELECT fndlookup.lookup_code
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_REGISTRATIONS_REASON'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = reg_int.registration_reason_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_REG_RSN_INVALID) = 0
AND registration_reason_code IS NULL
AND reg_int.registration_reason_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid registration_reason_code ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_TAX_AUT_INVALID
WHERE (( tax_authority_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_party_tax_profile ptp, hz_parties pty
WHERE ptp.party_type_code ='TAX_AUTHORITY'
AND ptp.party_id = ptp.party_id
AND pty.party_type = 'ORGANIZATION'
AND ptp.party_tax_profile_id = reg_int.tax_authority_id
)
)
OR
( tax_authority_id IS NULL
AND tax_authority_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_party_tax_profile ptp, hz_parties pty
WHERE ptp.party_type_code ='TAX_AUTHORITY'
AND ptp.party_id = PTY.party_id
AND pty.party_type = 'ORGANIZATION'
AND pty.party_name = reg_int.tax_authority_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid tax authority ';
UPDATE zx_registrations_int reg_int
SET tax_authority_id = (
SELECT PTP.party_tax_profile_id
FROM zx_party_tax_profile ptp, hz_parties pty
WHERE ptp.party_type_code ='TAX_AUTHORITY'
AND ptp.party_id = PTY.party_id
AND pty.party_type = 'ORGANIZATION'
AND pty.party_name = reg_int.tax_authority_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_AUT_INVALID) = 0
AND tax_authority_id IS NULL
AND tax_authority_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid tax_authority_id ';
UPDATE zx_registrations_int
SET error_number = error_number + G_ES_REG_INC_FLG_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND inclusive_tax_flag NOT IN ('Y','N',NULL);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid inclusive_tax_flag ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_ROUND_RULE_INVALID
WHERE (( rounding_rule_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_RULE'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND lookup_code = reg_int.rounding_rule_code
)
)
OR
( rounding_rule_code IS NULL
AND rounding_rule_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_RULE'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND meaning = reg_int.rounding_rule_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid rounding_rule ';
UPDATE zx_registrations_int reg_int
SET rounding_rule_code = (
SELECT lookup_code
FROM fnd_lookups
WHERE lookup_type = 'ZX_ROUNDING_RULE'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE
AND NVL(enabled_flag, 'N') = 'Y'
AND meaning = reg_int.rounding_rule_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_ROUND_RULE_INVALID) = 0
AND rounding_rule_code IS NULL
AND rounding_rule_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid rounding_rule_code ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_LGL_LOC_INVALID
WHERE (( legal_location_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM hr_locations loc
WHERE legal_address_flag = 'Y'
AND SYSDATE < nvl(inactive_date, SYSDATE + 1)
AND loc.location_id = reg_int.legal_location_id
)
)
OR
( legal_location_id IS NULL
AND (legal_location_code IS NOT NULL OR
address_line_1 IS NOT NULL OR
region_1 IS NOT NULL OR
town_or_city IS NOT NULL
)
AND NOT EXISTS
(
SELECT 1
FROM hr_locations
WHERE legal_address_flag = 'Y'
AND SYSDATE < nvl(inactive_date, SYSDATE + 1)
AND legal_location_code = reg_int.legal_location_code
AND NVL(address_line_1,G_MISS_CHAR) = NVL(reg_int.address_line_1,G_MISS_CHAR)
AND NVL(region_1,G_MISS_CHAR) = NVL(reg_int.region_1,G_MISS_CHAR)
AND NVL(town_or_city,G_MISS_CHAR) = NVL(reg_int.town_or_city,G_MISS_CHAR)
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid legal_location_id ';
UPDATE zx_registrations_int reg_int
SET legal_location_id = (
SELECT legal_location_id
FROM hr_locations
WHERE legal_address_flag = 'Y'
AND SYSDATE < nvl(inactive_date, SYSDATE + 1)
AND legal_location_code = reg_int.legal_location_code
AND NVL(address_line_1,G_MISS_CHAR) =
NVL(reg_int.Address_Line_1,G_MISS_CHAR)
AND NVL(region_1,G_MISS_CHAR) =
NVL(reg_int.Region_1,G_MISS_CHAR)
AND NVL(town_or_city,G_MISS_CHAR) =
NVL(reg_int.Town_OR_City,G_MISS_CHAR)
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_LGL_LOC_INVALID) = 0
AND legal_location_id IS NULL
AND ( legal_location_code IS NOT NULL OR
address_line_1 IS NOT NULL OR
region_1 IS NOT NULL OR
town_or_city IS NOT NULL
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid legal_location_id ';
UPDATE zx_registrations_int
SET error_number = error_number + G_ES_REG_DEFREG_FLG_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND default_registration_flag NOT IN ('Y','N');
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid default_registration_flag ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_TAX_REG_INVALID
WHERE (( tax_regime_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_regimes_v
WHERE has_sub_regime_flag <> 'Y'
AND tax_regime_code = reg_int.tax_regime_code
)
)
OR
( tax_regime_code IS NULL
AND tax_regime_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_regimes_v
WHERE has_sub_regime_flag <> 'Y'
AND tax_regime_name = reg_int.tax_regime_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid tax_regime ';
UPDATE zx_registrations_int reg_int
SET tax_regime_code = (
SELECT tax_regime_name
FROM zx_regimes_v
WHERE has_sub_regime_flag <> 'Y'
AND tax_regime_name = reg_int.tax_regime_name
AND rownum=1
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID) = 0
AND tax_regime_code IS NULL
AND tax_regime_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid tax_regime_code ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_TAX_PARAM_INVALID
WHERE (( tax_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_mco_lv_taxes_v
WHERE tax_regime_code = reg_int.tax_regime_code
AND tax = reg_int.tax_code
)
)
OR
( tax_code IS NULL
AND tax_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_mco_lv_taxes_v
WHERE tax_regime_code = reg_int.tax_regime_code
AND tax_full_name = reg_int.tax_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID) = 0;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid tax ';
UPDATE zx_registrations_int reg_int
SET tax_code = (
SELECT tax
FROM zx_mco_lv_taxes_v
WHERE tax_regime_code = reg_int.tax_regime_code
AND tax_full_name = reg_int.tax_name
AND rownum=1
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID+
G_ES_REG_TAX_PARAM_INVALID) = 0
AND tax_code IS NULL
AND tax_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid tax_code ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_TAX_JUR_INVALID
WHERE ( tax_jurisdiction_code IS NOT NULL
OR tax_jurisdiction_name IS NOT NULL
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID+
G_ES_REG_TAX_PARAM_INVALID) = 0
AND tax_code IS NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid tax_jurisdiction ';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_TAX_JUR_INVALID
WHERE (( tax_jurisdiction_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_jurisdictions_b
WHERE tax_regime_code = reg_int.tax_regime_code
AND tax = reg_int.tax_code
AND tax_jurisdiction_code = reg_int.tax_jurisdiction_code
AND NVL(allow_tax_registrations_flag,'N') = 'Y'
)
)
OR
( tax_jurisdiction_code IS NULL
AND tax_jurisdiction_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_jurisdictions_vl
WHERE tax_regime_code = reg_int.tax_regime_code
AND tax = reg_int.tax_code
AND tax_jurisdiction_name = reg_int.tax_jurisdiction_name
AND NVL(allow_tax_registrations_flag,'N') = 'Y'
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_REG_INVALID+
G_ES_REG_TAX_PARAM_INVALID+
G_ES_REG_TAX_JUR_INVALID) = 0
AND tax_code IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with invalid tax_jurisdiction ';
UPDATE zx_registrations_int reg_int
SET tax_jurisdiction_code = (
SELECT tax_jurisdiction_code
FROM zx_jurisdictions_vl
WHERE tax_regime_code = reg_int.tax_regime_code
AND tax = reg_int.tax_code
AND tax_jurisdiction_name = reg_int.tax_jurisdiction_name
AND NVL(allow_tax_registrations_flag,'N') = 'Y'
AND rownum=1
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_REG_TAX_JUR_INVALID+
G_ES_REG_TAX_REG_INVALID+
G_ES_REG_TAX_PARAM_INVALID) = 0
AND tax_jurisdiction_code IS NULL
AND tax_jurisdiction_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' REG records updated with valid tax_jurisdiction_code ';
UPDATE zx_registrations_int reg_int
SET prog_int_char1 =
(SELECT /*+ FIRST_ROWS(1) */ intf_party_reference
FROM zx_party_tax_profile_int ptp_int
WHERE ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
AND ROWNUM = 1 )
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with Party Info in prog_int_char1';
UPDATE zx_registrations_int reg_int
SET prog_int_num1 = G_PROG_INT_VAL
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND BITAND(reg_int.error_number,G_ES_REG_TAX_PARAM_INVALID) = 0
and reg_int.tax_code IS NOT NULL
AND EXISTS (
select 1
from zx_taxes_b tax
where tax.tax = reg_int.tax_code
and tax.tax_regime_code = reg_int.tax_regime_code
and nvl(allow_dup_regn_num_flag,'N') = 'Y'
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with allow_dup_regn_num_flag ';
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_REG_NUM_DUP
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND ptp_int.registration_number IS NOT NULL
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_dup
WHERE ptp_dup.request_id = ptp_int.request_id
AND ptp_dup.record_status = ptp_int.record_status
AND ptp_dup.registration_number = ptp_int.registration_number
AND ptp_dup.ROWID <> ptp_int.ROWID
AND ptp_dup.intf_party_reference <> ptp_int.intf_party_reference
AND ROWNUM = 1
UNION ALL
select /*+ FIRST_ROWS(1) */ 2
from zx_registrations_int reg_dup
where reg_dup.request_id = ptp_int.request_id
AND reg_dup.record_status = ptp_int.record_status
AND reg_dup.registration_number = ptp_int.registration_number
AND reg_dup.intf_ptp_reference <> ptp_int.intf_ptp_reference
AND BITAND(reg_dup.error_number,G_ES_REG_TAX_PARAM_INVALID) = 0
AND reg_dup.prog_int_char1 <> ptp_int.intf_party_reference
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with TRN duplicate against other Party/sites ';
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_REG_NUM_DUP
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND party_type_code = 'THIRD_PARTY_SITE'
AND ptp_int.registration_number IS NOT NULL
AND BITAND(ptp_int.error_number,G_ES_PTP_REG_NUM_DUP) = 0
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_dup
WHERE ptp_dup.request_id = ptp_int.request_id
AND ptp_dup.record_status = ptp_int.record_status
and ptp_dup.party_type_code = 'THIRD_PARTY_SITE'
AND ptp_dup.intf_party_reference = ptp_int.intf_party_reference
AND ptp_dup.ROWID <> ptp_int.ROWID
AND (ptp_dup.registration_number = ptp_int.registration_number
OR EXISTS (
select /*+ FIRST_ROWS(1) */ 2
from zx_registrations_int reg_dup
where reg_dup.request_id = ptp_dup.request_id
AND reg_dup.record_status = ptp_dup.record_status
and reg_dup.registration_number =ptp_int.registration_number
and reg_dup.intf_ptp_reference =ptp_dup.intf_ptp_reference
)
)
AND NOT EXISTS (
select 1
from zx_party_tax_profile_int ptp_prnt
where ptp_prnt.request_id = ptp_dup.request_id
AND ptp_prnt.record_status = ptp_dup.record_status
and ptp_prnt.party_type_code = 'THIRD_PARTY'
AND ptp_prnt.intf_party_reference = ptp_dup.intf_party_reference
AND (
ptp_prnt.registration_number=ptp_dup.registration_number
OR EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations_int reg_prnt
WHERE reg_prnt.request_id = ptp_prnt.request_id
AND reg_prnt.record_status = ptp_prnt.record_status
AND reg_prnt.intf_ptp_reference =ptp_prnt.intf_ptp_reference
AND reg_prnt.registration_number =
ptp_dup.registration_number
)
)
)
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with TRN duplicate for Party Sites Siblings ';
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_REG_NUM_DUP
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND BITAND(ptp_int.error_number,G_ES_PTP_REG_NUM_DUP) = 0
AND EXISTS(
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile prod
WHERE prod.rep_registration_number = ptp_int.registration_number
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with TRN duplicate against Production';
UPDATE zx_party_tax_profile_int ptp_int
SET error_number = error_number + G_ES_PTP_REG_NUM_DUP
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND BITAND(ptp_int.error_number,G_ES_PTP_REG_NUM_DUP) = 0
AND EXISTS(
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations prod
WHERE prod.registration_number = ptp_int.registration_number
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with TRN duplicate against REG Production';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_REG_NUM_DUP
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND prog_int_num1 <> G_PROG_INT_VAL
AND reg_int.registration_number IS NOT NULL
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_dup
WHERE ptp_dup.request_id = reg_int.request_id
AND ptp_dup.record_status = reg_int.record_status
AND ptp_dup.registration_number = reg_int.registration_number
AND ptp_dup.intf_party_reference <> reg_int.prog_int_char1
AND ROWNUM = 1
UNION ALL
select /*+ FIRST_ROWS(1) */ 2
from zx_registrations_int reg_dup
where reg_dup.request_id = reg_int.request_id
AND reg_dup.record_status = reg_int.record_status
and reg_dup.registration_number = reg_int.registration_number
AND reg_dup.ROWID <> reg_int.ROWID
and reg_dup.prog_int_char1 <> reg_int.prog_int_char1
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with TRN duplicate for Third Party and Third Party Site against Diff Party/Sites';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_REG_NUM_DUP
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND prog_int_num1 <> G_PROG_INT_VAL
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_int
WHERE ptp_int.request_id = reg_int.request_id
AND ptp_int.record_status = reg_int.record_status
AND ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
AND ptp_int.intf_party_reference = reg_int.prog_int_char1
AND ptp_int.party_type_code = 'THIRD_PARTY_SITE'
AND ROWNUM = 1)
AND reg_int.registration_number IS NOT NULL
AND BITAND(reg_int.error_number,G_ES_REG_REG_NUM_DUP) = 0
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile_int ptp_dup
WHERE ptp_dup.request_id = reg_int.request_id
AND ptp_dup.record_status = reg_int.record_status
and ptp_dup.party_type_code = 'THIRD_PARTY_SITE'
AND ptp_dup.intf_party_reference = reg_int.prog_int_char1
AND ptp_dup.intf_ptp_reference <> reg_int.intf_ptp_reference
AND (ptp_dup.registration_number = reg_int.registration_number
OR EXISTS (
select /*+ FIRST_ROWS(1) */ 2
from zx_registrations_int reg_dup
where reg_dup.request_id = ptp_dup.request_id
AND reg_dup.record_status = ptp_dup.record_status
and reg_dup.registration_number =reg_int.registration_number
and reg_dup.intf_ptp_reference =ptp_dup.intf_ptp_reference
AND reg_dup.prog_int_char1 =reg_int.prog_int_char1
AND BITAND(reg_dup.error_number,G_ES_REG_TAX_PARAM_INVALID) = 0
)
)
AND NOT EXISTS (
select /*+ FIRST_ROWS(1) */ 1
from zx_party_tax_profile_int ptp_prnt
where ptp_prnt.request_id = ptp_dup.request_id
AND ptp_prnt.record_status = ptp_dup.record_status
and ptp_prnt.party_type_code = 'THIRD_PARTY'
AND ptp_prnt.intf_party_reference = ptp_dup.intf_party_reference
AND (
ptp_prnt.registration_number=ptp_dup.registration_number
OR EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations_int reg_prnt
WHERE reg_prnt.request_id = ptp_prnt.request_id
AND reg_prnt.record_status = ptp_prnt.record_status
AND reg_prnt.intf_ptp_reference =ptp_prnt.intf_ptp_reference
AND reg_prnt.registration_number =
ptp_dup.registration_number
)
)
)
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with TRN duplicate for Third Party Sites Siblings';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_REG_NUM_DUP
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND prog_int_num1 <> G_PROG_INT_VAL
AND BITAND(reg_int.error_number,G_ES_REG_REG_NUM_DUP) = 0
AND EXISTS(
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_party_tax_profile prod
WHERE prod.rep_registration_number = reg_int.registration_number
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with TRN duplicate against Prod Tables';
UPDATE zx_registrations_int reg_int
SET error_number = error_number + G_ES_REG_REG_NUM_DUP
WHERE request_id = G_REQUEST_ID
AND NVL(record_status,G_RS_ERROR) = G_RS_VALID
AND prog_int_num1 <> G_PROG_INT_VAL
AND BITAND(reg_int.error_number,G_ES_REG_REG_NUM_DUP) = 0
AND EXISTS(
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations prod
WHERE prod.registration_number = reg_int.registration_number
AND ROWNUM = 1
);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with TRN duplicate against Prod Tables';
UPDATE zx_party_tax_profile_int ptp_int
SET record_status = G_RS_ERROR
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND error_number > 0;
G_DEBUG_STATEMENT:='Updated Record Status for '||SQL%ROWCOUNT||' records from '||G_INTF_PTP_TABLE_NAME;
UPDATE zx_registrations_int reg_int
SET record_status = G_RS_ERROR
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND error_number > 0;
G_DEBUG_STATEMENT:='Updated Record Status for '||SQL%ROWCOUNT||' records from '||G_INTF_REG_TABLE_NAME;
UPDATE zx_party_tax_profile_int ptp_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_registrations_int reg_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_ERROR
AND reg_int.intf_ptp_reference = ptp_int.intf_ptp_reference);
UPDATE zx_party_tax_profile_int ptp_int
SET registration_number = (
SELECT registration_number
FROM zx_registrations_int reg
WHERE reg.request_id = G_REQUEST_ID
AND NVL(reg.record_status,G_RS_ERROR) = G_RS_VALID
AND reg.intf_ptp_reference = nvl(ptp_int.intf_ptp_reference,G_MISS_CHAR)
AND NVL(reg.default_registration_flag,'N') = 'Y'
AND BITAND(error_number,G_ES_REG_DEFREG_FLG_INVALID+G_ES_REG_MULT_DEF_FLAG) = 0
)
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND EXISTS (
SELECT 1
FROM zx_registrations_int reg
WHERE reg.request_id = G_REQUEST_ID
AND NVL(reg.record_status,G_RS_ERROR) = G_RS_VALID
AND reg.intf_ptp_reference = nvl(ptp_int.intf_ptp_reference,G_MISS_CHAR)
AND NVL(reg.default_registration_flag,'N') = 'Y'
AND BITAND(error_number,G_ES_REG_DEFREG_FLG_INVALID+G_ES_REG_MULT_DEF_FLAG) = 0
);
UPDATE zx_exemptions_int
SET request_id = G_REQUEST_ID
,record_status = G_RS_IN_PROCESS
,dml_type = UPPER(dml_type)
,prog_int_char1 = NULL
,prog_int_num1 = 0
,error_number = 0
WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM));
UPDATE zx_exemptions_int exe_int
SET error_number = G_ES_EXE_MAND_PARAM_MISSING
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND (NVL(tax_regime_code,tax_regime_name) IS NULL OR
(content_owner_id IS NULL AND content_owner_name IS NULL) OR
rate_modifier IS NULL);
UPDATE zx_exemptions_int orig
SET error_number = error_number + G_ES_EXE_DATE_OVERLAP
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_exemptions_int dup
WHERE dup.rowid <> orig.rowid
AND dup.request_id = G_REQUEST_ID
AND dup.record_status = G_RS_IN_PROCESS
AND dup.intf_ptp_reference = orig.intf_ptp_reference
AND dup.tax_regime_code = orig.tax_regime_code
AND NVL(dup.tax_code,G_MISS_CHAR) = NVL(orig.tax_code,G_MISS_CHAR)
AND NVL(dup.tax_status_code,G_MISS_CHAR) = NVL(orig.tax_status_code,G_MISS_CHAR)
AND NVL(dup.tax_rate_code,G_MISS_CHAR) = NVL(orig.tax_rate_code,G_MISS_CHAR)
AND NVL(dup.tax_jurisdiction_id,G_MISS_NUM) = NVL(orig.tax_jurisdiction_id,G_MISS_NUM)
AND NVL(dup.inventory_org_id,G_MISS_NUM) = NVL(orig.inventory_org_id,G_MISS_NUM)
AND NVL(dup.inventory_item_id,G_MISS_NUM) = NVL(orig.inventory_item_id,G_MISS_NUM)
AND (
( dup.exemption_status_code = 'PRIMARY' AND orig.exemption_status_code = 'PRIMARY')
OR
( NVL(dup.exempt_reason_code,G_MISS_CHAR) = NVL(orig.exempt_reason_code,G_MISS_CHAR)
AND NVL(dup.exempt_certificate_number,G_MISS_CHAR) = NVL(orig.exempt_certificate_number,G_MISS_CHAR)
)
)
AND ( dup.effective_to IS NULL
OR orig.effective_from <= dup.effective_to
)
AND ( orig.effective_to IS NULL
OR orig.effective_to >= dup.effective_from
)
);
UPDATE zx_exemptions_int exe_int
SET record_status = DECODE(error_number,0,G_RS_VALID,G_RS_ERROR)
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS;
UPDATE zx_party_tax_profile_int ptp_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_exemptions_int exe_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_ERROR
AND exe_int.intf_ptp_reference = ptp_int.intf_ptp_reference);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with record_status NULL';
UPDATE zx_exemptions_int
SET error_number = error_number + G_ES_EXE_TRANS_TYPE_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND dml_type <> 'CREATE';
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_EXMPT_RSN_INVALID
WHERE (( exempt_reason_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_REASON_CODE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.lookup_code = exe_int.exempt_reason_code
)
)
OR
( exempt_reason_code IS NULL
AND exempt_reason_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_REASON_CODE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = exe_int.exempt_reason_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_exemptions_int exe_int
SET exempt_reason_code = (
SELECT fndlookup.lookup_code
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_REASON_CODE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = exe_int.exempt_reason_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_EXMPT_RSN_INVALID) = 0
AND exempt_reason_code IS NULL
AND exempt_reason_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_EXMPT_STS_INVALID
WHERE (( exemption_status_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_STATUS'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.lookup_code = exe_int.exemption_status_code
)
)
OR
( exemption_status_code IS NULL
AND exemption_status_name IS NOT NULL
AND NOT EXISTS
( SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_STATUS'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = exe_int.exemption_status_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_exemptions_int exe_int
SET exemption_status_code = (
SELECT fndlookup.lookup_code
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_STATUS'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = exe_int.exemption_status_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_EXMPT_STS_INVALID) = 0
AND exemption_status_code IS NULL
AND exemption_status_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_TAX_REG_INVALID
WHERE (
( tax_regime_code IS NOT NULL
AND NOT EXISTS
(
SELECT tax_regime_code
FROM zx_regimes_v
WHERE has_sub_regime_flag <> 'Y'
AND tax_regime_code = exe_int.tax_regime_code
)
)
OR
( tax_regime_code IS NULL
AND tax_regime_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_regimes_v
WHERE has_sub_regime_flag <> 'Y'
AND tax_regime_name = exe_int.tax_regime_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_exemptions_int exe_int
SET tax_regime_code = (
SELECT tax_regime_code
FROM zx_regimes_v
WHERE has_sub_regime_flag <> 'Y'
AND tax_regime_name = exe_int.tax_regime_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID) = 0
AND tax_regime_code IS NULL
AND tax_regime_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_CNT_OWN_INVALID
WHERE (( content_owner_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_first_party_orgs_moac_v fpo,
fnd_lookups LKP,
zx_regimes_usages REG
WHERE fpo.party_tax_profile_id <> -99
AND lkp.lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lkp.lookup_code = fpo.party_type_code
AND SYSDATE BETWEEN lkp.start_date_active
AND NVL(LKP.end_date_active,SYSDATE)
AND lkp.enabled_flag = 'Y'
AND reg.first_pty_org_id = fpo.party_tax_profile_id
AND reg.tax_regime_code = exe_int.tax_regime_code
AND fpo.party_tax_profile_id = exe_int.content_owner_id
)
)
OR
( content_owner_id IS NULL
AND content_owner_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_first_party_orgs_moac_v fpo,
fnd_lookups lkp,
zx_regimes_usages reg
WHERE fpo.party_tax_profile_id <> -99
AND lkp.lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lkp.lookup_code = fpo.party_type_code
AND SYSDATE BETWEEN LKP.start_date_active
AND NVL(LKP.end_date_active,SYSDATE)
AND lkp.enabled_flag = 'Y'
AND reg.first_pty_org_id = fpo.party_tax_profile_id
AND reg.tax_regime_code = exe_int.tax_regime_code
AND fpo.party_name = exe_int.content_owner_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID) = 0;
UPDATE zx_exemptions_int exe_int
SET content_owner_id = (
SELECT fpo.party_tax_profile_id
FROM zx_first_party_orgs_moac_v fpo,
fnd_lookups lkp,
zx_regimes_usages reg
WHERE FPO.party_tax_profile_id <> -99
AND lkp.lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lkp.lookup_code = fpo.party_type_code
AND SYSDATE BETWEEN LKP.start_date_active
AND NVL(LKP.end_date_active,SYSDATE)
AND lkp.enabled_flag = 'Y'
AND reg.first_pty_org_id = fpo.party_tax_profile_id
AND reg.tax_regime_code = exe_int.tax_regime_code
AND fpo.party_name = exe_int.content_owner_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_CNT_OWN_INVALID) = 0
AND content_owner_id IS NULL
AND content_owner_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_TAX_PARAM_INVALID
WHERE (( tax_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_taxes_b ztvl,
zx_subscription_details sd
WHERE ztvl.tax_regime_code = sd.tax_regime_code
AND ztvl.content_owner_id = sd.parent_first_pty_org_id
AND (sd.view_options_code in ('NONE', 'VFC')
or
(sd.view_options_code = 'VFR'
AND not exists ( SELECT 1
FROM zx_taxes_b b
WHERE b.tax_regime_code = ztvl.tax_regime_code
AND b.tax = ztvl.tax
AND b.content_owner_id = sd.first_pty_org_id
)
)
)
AND ztvl.allow_exemptions_flag = 'Y'
AND ztvl.live_for_applicability_flag = 'Y'
AND ztvl.tax_regime_code = exe_int.tax_regime_code
AND sd.first_pty_org_id = exe_int.content_owner_id
AND ztvl.tax = exe_int.tax_code
)
)
OR
( tax_code IS NULL
AND tax_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_taxes_vl ztvl,
zx_subscription_details sd
WHERE ztvl.tax_regime_code = sd.tax_regime_code
AND ztvl.content_owner_id = sd.parent_first_pty_org_id
AND (sd.view_options_code in ('NONE', 'VFC')
or
(sd.view_options_code = 'VFR'
AND not exists ( SELECT 1
FROM zx_taxes_b b
WHERE b.tax_regime_code = ztvl.tax_regime_code
AND b.tax = ztvl.tax
AND b.content_owner_id = sd.first_pty_org_id
)
)
)
AND ztvl.allow_exemptions_flag = 'Y'
AND ztvl.live_for_applicability_flag = 'Y'
AND ztvl.tax_regime_code = exe_int.tax_regime_code
AND sd.first_pty_org_id = exe_int.content_owner_id
AND ztvl.tax_full_name = exe_int.tax_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_CNT_OWN_INVALID) = 0;
UPDATE zx_exemptions_int exe_int
SET tax_code = (
SELECT ztvl.tax
FROM zx_taxes_vl ztvl,
zx_subscription_details sd
WHERE ztvl.tax_regime_code = sd.tax_regime_code
AND ztvl.content_owner_id = sd.parent_first_pty_org_id
AND (sd.view_options_code in ('NONE', 'VFC')
or
(sd.view_options_code = 'VFR'
AND not exists ( SELECT 1
FROM zx_taxes_b b
WHERE b.tax_regime_code = ztvl.tax_regime_code
AND b.tax = ztvl.tax
AND b.content_owner_id = sd.first_pty_org_id
)
)
)
AND ztvl.allow_exemptions_flag = 'Y'
AND ztvl.live_for_applicability_flag = 'Y'
AND ztvl.tax_regime_code = exe_int.tax_regime_code
AND sd.first_pty_org_id = exe_int.content_owner_id
AND ztvl.tax_full_name = exe_int.tax_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_CNT_OWN_INVALID+
G_ES_EXE_TAX_PARAM_INVALID) = 0
AND tax_code IS NULL
AND tax_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_TAX_STS_INVALID
WHERE (( tax_status_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_status_b zsvl,
zx_subscription_details zsd
WHERE zsvl.allow_exemptions_flag = 'Y'
AND zsd.tax_regime_code = zsvl.tax_regime_code
AND zsd.parent_first_pty_org_id = zsvl.content_owner_id
AND (NVL(zsd.view_options_code,'NONE') IN ('NONE', 'VFC') OR
(NVL(zsd.view_options_code,'VFR') = 'VFR'
AND NOT EXISTS
(SELECT 1
FROM zx_status_b b
WHERE b.tax_regime_code = zsvl.tax_regime_code
AND b.tax = zsvl.tax
AND b.tax_status_code = zsvl.tax_status_code
AND b.content_owner_id = zsd.first_pty_org_id
)
)
)
AND zsvl.tax_regime_code = exe_int.tax_regime_code
AND zsd.first_pty_org_id = exe_int.content_owner_id
AND zsvl.tax = exe_int.tax_code
AND zsvl.tax_status_code = exe_int.tax_status_code
)
)
OR
( tax_status_code IS NULL
AND tax_status_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_status_vl zsvl,
zx_subscription_details zsd
WHERE zsvl.allow_exemptions_flag = 'Y'
AND zsd.tax_regime_code = zsvl.tax_regime_code
AND zsd.parent_first_pty_org_id = zsvl.content_owner_id
AND (NVL(zsd.view_options_code,'NONE') IN ('NONE', 'VFC') OR
(NVL(zsd.view_options_code,'VFR') = 'VFR'
AND NOT EXISTS
(SELECT 1
FROM zx_status_b b
WHERE b.tax_regime_code = zsvl.tax_regime_code
AND b.tax = zsvl.tax
AND b.tax_status_code = zsvl.tax_status_code
AND b.content_owner_id = zsd.first_pty_org_id
)
)
)
AND zsvl.tax_regime_code = exe_int.tax_regime_code
AND zsd.first_pty_org_id = exe_int.content_owner_id
AND zsvl.tax = exe_int.tax_code
AND zsvl.tax_status_name = exe_int.tax_status_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_CNT_OWN_INVALID+
G_ES_EXE_TAX_PARAM_INVALID) = 0;
UPDATE zx_exemptions_int exe_int
SET tax_status_code = (
SELECT zsvl.TAX_STATUS_CODE
FROM zx_status_vl zsvl,
zx_subscription_details zsd
WHERE zsvl.allow_exemptions_flag = 'y'
AND zsd.tax_regime_code = zsvl.tax_regime_code
AND zsd.parent_first_pty_org_id = zsvl.content_owner_id
AND (NVL(zsd.view_options_code,'NONE') IN ('NONE', 'VFC') OR
(NVL(zsd.view_options_code,'VFR') = 'VFR'
AND NOT EXISTS
(SELECT 1
FROM zx_status_b b
WHERE b.tax_regime_code = zsvl.tax_regime_code
AND b.tax = zsvl.tax
AND b.tax_status_code = zsvl.tax_status_code
AND b.content_owner_id = zsd.first_pty_org_id
)
)
)
AND zsvl.tax_regime_code = exe_int.tax_regime_code
AND zsd.first_pty_org_id = exe_int.content_owner_id
AND zsvl.tax = exe_int.tax_code
AND zsvl.TAX_STATUS_NAME = exe_int.tax_status_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_CNT_OWN_INVALID+
G_ES_EXE_TAX_PARAM_INVALID+
G_ES_EXE_TAX_STS_INVALID) = 0
AND tax_status_code IS NULL
AND tax_status_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_TAX_JUR_INVALID
WHERE ( tax_jurisdiction_id IS NOT NULL
OR tax_jurisdiction_name IS NOT NULL
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND tax_code IS NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_TAX_JUR_INVALID
WHERE (( tax_jurisdiction_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM ZX_JURISDICTIONS_b
WHERE TAX_REGIME_CODE = exe_int.tax_regime_code
AND tax = exe_int.tax_code
AND tax_jurisdiction_id = exe_int.tax_jurisdiction_id
)
)
OR
( tax_jurisdiction_id IS NULL
AND tax_jurisdiction_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_jurisdictions_vl
WHERE tax_regime_code = exe_int.tax_regime_code
AND tax = exe_int.tax_code
AND tax_jurisdiction_name = exe_int.tax_jurisdiction_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_JUR_INVALID+
G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_TAX_PARAM_INVALID) = 0
AND tax_code IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET tax_jurisdiction_id = (
SELECT tax_jurisdiction_id
FROM ZX_JURISDICTIONS_VL
WHERE TAX_REGIME_CODE = exe_int.tax_regime_code
AND tax = exe_int.tax_code
AND tax_jurisdiction_name = exe_int.tax_jurisdiction_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_JUR_INVALID+
G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_TAX_PARAM_INVALID) = 0
AND tax_jurisdiction_id IS NULL
AND tax_jurisdiction_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_TAX_RATE_INVALID
WHERE (( tax_rate_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_rates_b zrvl, zx_subscription_details sd
WHERE zrvl.tax_regime_code = sd.tax_regime_code
AND zrvl.content_owner_id = sd.parent_first_pty_org_id
AND (sd.view_options_code in ('NONE', 'VFC')
or
(sd.view_options_code = 'VFR'
AND not exists ( SELECT 1
FROM zx_rates_b b
WHERE b.tax_regime_code = zrvl.tax_regime_code
AND b.tax = zrvl.tax
AND b.tax_status_code = zrvl.tax_status_code
AND b.tax_rate_code = zrvl.tax_rate_code
AND b.content_owner_id = sd.first_pty_org_id
)
)
)
AND zrvl.tax_regime_code = exe_int.tax_regime_code
AND sd.first_pty_org_id = exe_int.content_owner_id
AND zrvl.tax = exe_int.tax_code
AND zrvl.tax_status_code = exe_int.tax_status_code
AND zrvl.tax_rate_code = exe_int.tax_rate_code
)
)
OR
( tax_rate_code IS NULL
AND tax_rate_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_rates_vl zrvl, zx_subscription_details sd
WHERE zrvl.tax_regime_code = sd.tax_regime_code
AND zrvl.content_owner_id = sd.parent_first_pty_org_id
AND (sd.view_options_code in ('NONE', 'VFC')
or
(sd.view_options_code = 'VFR'
AND not exists ( SELECT 1
FROM zx_rates_b b
WHERE b.tax_regime_code = zrvl.tax_regime_code
AND b.tax = zrvl.tax
AND b.tax_status_code = zrvl.tax_status_code
AND b.tax_rate_code = zrvl.tax_rate_code
AND b.content_owner_id = sd.first_pty_org_id
)
)
)
AND zrvl.tax_regime_code = exe_int.tax_regime_code
AND sd.first_pty_org_id = exe_int.content_owner_id
AND zrvl.tax = exe_int.tax_code
AND zrvl.tax_status_code = exe_int.tax_status_code
AND zrvl.tax_rate_name = exe_int.tax_rate_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_TAX_PARAM_INVALID+
G_ES_EXE_CNT_OWN_INVALID+
G_ES_EXE_TAX_STS_INVALID) = 0;
UPDATE zx_exemptions_int exe_int
SET TAX_RATE_CODE = (
SELECT zrvl.tax_rate_code
FROM zx_rates_vl zrvl, zx_subscription_details sd
WHERE zrvl.tax_regime_code = sd.tax_regime_code
AND zrvl.content_owner_id = sd.parent_first_pty_org_id
AND (sd.view_options_code in ('NONE', 'VFC')
or
(sd.view_options_code = 'VFR'
AND not exists ( SELECT 1
FROM zx_rates_b b
WHERE b.tax_regime_code = zrvl.tax_regime_code
AND b.tax = zrvl.tax
AND b.tax_status_code = zrvl.tax_status_code
AND b.tax_rate_code = zrvl.tax_rate_code
AND b.content_owner_id = sd.first_pty_org_id
)
)
)
AND zrvl.tax_regime_code = exe_int.tax_regime_code
AND sd.first_pty_org_id = exe_int.content_owner_id
AND zrvl.tax = exe_int.tax_code
AND zrvl.tax_status_code = exe_int.tax_status_code
AND zrvl.tax_rate_name = exe_int.tax_rate_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_REG_INVALID+
G_ES_EXE_TAX_PARAM_INVALID+
G_ES_EXE_CNT_OWN_INVALID+
G_ES_EXE_TAX_STS_INVALID+
G_ES_EXE_TAX_RATE_INVALID) = 0
AND TAX_RATE_CODE IS NULL
AND tax_rate_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_LWR_LVL_FLG_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND apply_to_lower_levels_flag NOT IN ('Y','N');
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_DATE_RANGE_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND effective_to IS NOT NULL
AND effective_to < effective_from;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_INV_ORG_INVALID
WHERE (( inventory_org_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM org_organization_definitions ood,
hr_all_organization_units org,
hr_organization_information ori
WHERE ood.organization_id = org.organization_id
AND org.organization_id = ori.organization_id
AND ori.org_information_context = 'CLASS'
AND ori.org_information1 = 'INV'
AND ori.org_information2 = 'Y'
AND EXISTS (SELECT 1
FROM mtl_parameters
WHERE master_organization_id = ood.organization_id)
AND ood.organization_id = exe_int.inventory_org_id
)
)
OR
( inventory_org_id IS NULL
AND inventory_org_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM org_organization_definitions ood,
hr_all_organization_units org,
hr_organization_information ori
WHERE ood.organization_id = org.organization_id
AND org.organization_id = ORI.organization_id
AND ori.org_information_context = 'CLASS'
AND ori.org_information1 = 'INV'
AND ori.org_information2 = 'Y'
AND EXISTS (SELECT 1
FROM mtl_parameters
WHERE master_organization_id = OOD.ORGANIZATION_ID
)
AND ood.organization_name = exe_int.inventory_org_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_exemptions_int exe_int
SET Inventory_Org_id =
(
SELECT OOD.ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS OOD,
HR_ALL_ORGANIZATION_UNITS ORG,
HR_ORGANIZATION_INFORMATION ORI
WHERE OOD.ORGANIZATION_ID = ORG.organization_id
AND ORG.organization_id = ORI.organization_id
AND ORI.org_information_context = 'CLASS'
AND ORI.org_information1 = 'INV'
AND ORI.org_information2 = 'Y'
AND EXISTS (SELECT 1
FROM mtl_parameters
WHERE master_organization_id = OOD.ORGANIZATION_ID)
AND OOD.ORGANIZATION_NAME = exe_int.Inventory_Org_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_INV_ORG_INVALID) = 0
AND Inventory_Org_id IS NULL
AND Inventory_Org_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_INV_ITM_INVALID
WHERE (( inventory_item_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM MTL_SYSTEM_ITEMS_B
WHERE organization_id = exe_int.inventory_org_id
AND inventory_item_id = exe_int.inventory_item_id
)
)
OR
( inventory_item_id IS NULL
AND item_number IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE organization_id = exe_int.inventory_org_id
AND concatenated_segments = exe_int.item_number
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_INV_ORG_INVALID) = 0;
UPDATE zx_exemptions_int exe_int
SET inventory_item_id =
(
SELECT inventory_item_id
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE organization_id = exe_int.inventory_org_id
AND concatenated_segments = exe_int.item_number
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_INV_ORG_INVALID+
G_ES_EXE_INV_ITM_INVALID) = 0
AND inventory_item_id IS NULL
AND item_number IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_TAX_AUT_INVALID
WHERE (( issuing_tax_authority_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM zx_party_tax_profile PTP,
hz_parties PTY
WHERE PTP.party_type_code ='TAX_AUTHORITY'
AND PTP.party_id = PTY.party_id
AND PTP.party_tax_profile_id = exe_int.issuing_tax_authority_id
)
)
OR
( issuing_tax_authority_id IS NULL
AND issuing_tax_authority_name IS NOT NULL
AND NOT EXISTS
(
SELECT PTY.party_name
FROM zx_party_tax_profile PTP,
hz_parties PTY
WHERE PTP.party_type_code ='TAX_AUTHORITY'
AND PTP.party_id = PTY.party_id
AND PTy.party_name = exe_int.issuing_tax_authority_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_exemptions_int exe_int
SET issuing_tax_authority_id =
(
SELECT PTP.party_tax_profile_id
FROM zx_party_tax_profile PTP,
hz_parties PTY
WHERE PTP.party_type_code ='TAX_AUTHORITY'
AND PTP.party_id = PTY.party_id
AND PTY.party_name = exe_int.issuing_tax_authority_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_TAX_AUT_INVALID) = 0
AND issuing_tax_authority_id IS NULL
AND issuing_tax_authority_name IS NOT NULL;
UPDATE zx_exemptions_int exe_int
SET error_number = error_number + G_ES_EXE_EXMPT_TYP_INVALID
WHERE (( exemption_type_code IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.lookup_code = exe_int.exemption_type_code
)
)
OR
( exemption_type_code IS NULL
AND exemption_type_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = exe_int.exemption_type_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_exemptions_int exe_int
SET exemption_type_code = (
SELECT fndlookup.lookup_code
FROM fnd_lookups fndlookup
WHERE fndlookup.lookup_type = 'ZX_EXEMPTION_TYPE'
AND NVL(fndlookup.start_date_active, SYSDATE) <= SYSDATE
AND NVL(fndlookup.end_date_active, SYSDATE) >= SYSDATE
AND NVL(fndlookup.enabled_flag, 'N') = 'Y'
AND fndlookup.meaning = exe_int.exemption_type_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_EXE_EXMPT_TYP_INVALID) = 0
AND exemption_type_code IS NULL
AND exemption_type_name IS NOT NULL;
SELECT *
FROM zx_exemptions_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
ORDER BY attribute_category desc
FOR UPDATE;
UPDATE zx_exemptions_int
SET error_number = error_number + G_ES_EXE_DFF_INVALID,
prog_int_char1 = fnd_flex_descval.error_message
WHERE CURRENT OF c_exempt_records;
UPDATE zx_exemptions_int intf
SET attribute1 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute1,NULL)
,attribute2 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute2,NULL)
,attribute3 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute3,NULL)
,attribute4 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute4,NULL)
,attribute5 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute5,NULL)
,attribute6 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute6,NULL)
,attribute7 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute7,NULL)
,attribute8 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute8,NULL)
,attribute9 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute9,NULL)
,attribute10 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute10,NULL)
,attribute11 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute11,NULL)
,attribute12 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute12,NULL)
,attribute13 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute13,NULL)
,attribute14 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute14,NULL)
,attribute15 = DECODE(G_DFF_USAGE_RECORD.attribute1,G_TRUE,intf.attribute15,NULL)
WHERE CURRENT OF c_exempt_records;
UPDATE zx_exemptions_int
SET error_number = error_number + G_ES_EXE_DFF_INVALID,
prog_int_char1 = 'Attributes have been populated but no Context has been defined.'
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND (attribute1 IS NOT NULL
OR
attribute2 IS NOT NULL
OR
attribute3 IS NOT NULL
OR
attribute4 IS NOT NULL
OR
attribute5 IS NOT NULL
OR
attribute6 IS NOT NULL
OR
attribute7 IS NOT NULL
OR
attribute8 IS NOT NULL
OR
attribute9 IS NOT NULL
OR
attribute10 IS NOT NULL
OR
attribute11 IS NOT NULL
OR
attribute12 IS NOT NULL
OR
attribute13 IS NOT NULL
OR
attribute14 IS NOT NULL
OR
attribute15 IS NOT NULL
);
UPDATE zx_report_codes_assoc_int
SET request_id = G_REQUEST_ID
,record_status = G_RS_IN_PROCESS
,dml_type = UPPER(dml_type)
,prog_int_char1 = NULL
,prog_int_num1 = 0
,error_number = 0
WHERE NVL(record_status,G_RS_ERROR) <> G_RS_VALID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM));
UPDATE zx_report_codes_assoc_int
SET error_number = G_ES_RCA_MAND_PARAM_MISSING
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND reporting_type_id IS NULL
AND reporting_type_name IS NULL;
UPDATE zx_report_codes_assoc_int orig
SET error_number = error_number + G_ES_RCA_DUP_INTF_RECORD,
record_status = G_RS_ERROR
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS
AND error_number <> G_ES_RCA_MAND_PARAM_MISSING
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_report_codes_assoc_int dup
WHERE dup.rowid <> orig.rowid
AND dup.request_id = G_REQUEST_ID
AND dup.record_status = G_RS_IN_PROCESS
AND NVL(dup.entity_id,G_MISS_NUM) = NVL(orig.entity_id,G_MISS_NUM)
AND dup.intf_ptp_reference = orig.intf_ptp_reference
AND nvl(dup.reporting_type_id,G_MISS_NUM) = nvl(orig.reporting_type_id,G_MISS_NUM)
AND nvl(dup.reporting_type_name,G_MISS_CHAR) = nvl(orig.reporting_type_name,G_MISS_CHAR)
AND dup.effective_from = orig.effective_from
);
UPDATE zx_report_codes_assoc_int orig
SET error_number = error_number + G_ES_RCA_DATE_OVERLAP
WHERE orig.record_status = G_RS_IN_PROCESS
AND orig.request_id = G_REQUEST_ID
AND EXISTS (
SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_report_codes_assoc_int dup
WHERE dup.rowid <> orig.rowid
AND dup.request_id = G_REQUEST_ID
AND dup.record_status = G_RS_IN_PROCESS
AND BITAND(dup.error_number,G_ES_RCA_REP_TYPE_INVALID) = 0
AND dup.reporting_type_id = orig.reporting_type_id
AND dup.intf_ptp_reference = orig.intf_ptp_reference
AND ( dup.effective_to IS NULL
OR orig.effective_from <= dup.effective_to
)
AND ( orig.effective_to IS NULL
OR orig.effective_to >= dup.effective_from
)
);
UPDATE zx_report_codes_assoc_int rca_int
SET record_status = DECODE(error_number,0,G_RS_VALID,G_RS_ERROR)
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_IN_PROCESS;
UPDATE zx_party_tax_profile_int ptp_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_report_codes_assoc_int rca_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_ERROR
AND rca_int.intf_ptp_reference = ptp_int.intf_ptp_reference);
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' ptp records with record_status updated to NULL';
UPDATE zx_report_codes_assoc_int
SET error_number = error_number + G_ES_RCA_TRANS_TYPE_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND dml_type <> 'CREATE';
UPDATE zx_report_codes_assoc_int
SET error_number = error_number + G_ES_RCA_ENTY_CD_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND NVL(batch_id,G_MISS_NUM) =
NVL(G_BATCH_ID,NVL(batch_id,G_MISS_NUM))
AND entity_code <> 'ZX_PARTY_TAX_PROFILE';
UPDATE zx_report_codes_assoc_int
SET error_number = error_number + G_ES_RCA_DATE_RANGE_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND effective_to IS NOT NULL
AND effective_to < effective_from;
UPDATE zx_report_codes_assoc_int rca_int
SET error_number = error_number + G_ES_RCA_REP_TYPE_INVALID
WHERE (( reporting_type_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM Zx_Reporting_Types_b RepType,
Zx_Report_Types_Usages RepUsage
WHERE RepType.Reporting_Type_Id = RepUsage.Reporting_Type_Id
AND RepUsage.Enabled_Flag= 'Y'
AND SYSDATE BETWEEN RepType. EFFECTIVE_FROM
AND NVL(RepType. EFFECTIVE_TO,SYSDATE)
AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
AND RepType.Has_Reporting_Codes_Flag = 'Y'
AND NVL(RepType.legal_message_flag, 'N') = 'N'
AND reptype.reporting_type_id = rca_int.reporting_type_id
)
)
OR
( reporting_type_id IS NULL
AND reporting_type_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM Zx_Reporting_Types_VL RepType,
Zx_Report_Types_Usages RepUsage
WHERE RepType.Reporting_Type_Id = RepUsage.Reporting_Type_Id
AND RepUsage.Enabled_Flag= 'Y'
AND SYSDATE BETWEEN RepType. EFFECTIVE_FROM
AND NVL(RepType. EFFECTIVE_TO,SYSDATE)
AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
AND RepType.Has_Reporting_Codes_Flag = 'Y'
AND NVL(RepType.legal_message_flag, 'N') = 'N'
AND reptype.reporting_type_name = rca_int.reporting_type_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID;
UPDATE zx_report_codes_assoc_int rca_int
SET reporting_type_id =
(
SELECT RepType.Reporting_Type_Id
FROM Zx_Reporting_Types_VL RepType,
Zx_Report_Types_Usages RepUsage
WHERE RepType.Reporting_Type_Id = RepUsage.Reporting_Type_Id
AND RepUsage.Enabled_Flag= 'Y'
AND SYSDATE BETWEEN RepType. EFFECTIVE_FROM
AND NVL(RepType. EFFECTIVE_TO,SYSDATE)
AND RepUsage.ENTITY_CODE = 'ZX_PARTY_TAX_PROFILE'
AND RepType.Has_Reporting_Codes_Flag = 'Y'
AND NVL(RepType.legal_message_flag, 'N') = 'N'
AND RepType.Reporting_Type_Name = rca_int.Reporting_Type_Name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_RCA_REP_TYPE_INVALID) = 0
AND reporting_type_id IS NULL
AND reporting_type_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with valid reporting_type_id';
UPDATE zx_report_codes_assoc_int rca_int
SET error_number = error_number + G_ES_RCA_REP_CODE_INVALID
WHERE (( reporting_code_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM ZX_REPORTING_CODES_b RepCode
WHERE RepCode.Reporting_Type_Id = rca_int.Reporting_Type_Id
AND SYSDATE BETWEEN RepCode.EFFECTIVE_FROM
AND NVL(RepCode.EFFECTIVE_TO,SYSDATE)
AND reporting_code_id = rca_int.reporting_code_id
)
)
OR
( reporting_code_id IS NULL
AND reporting_code_name IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM ZX_REPORTING_CODES_VL RepCode
WHERE RepCode.Reporting_Type_Id = rca_int.Reporting_Type_Id
AND SYSDATE BETWEEN RepCode.EFFECTIVE_FROM
AND NVL(RepCode.EFFECTIVE_TO,SYSDATE)
AND reporting_code_name = rca_int.reporting_code_name
)
)
)
AND record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_RCA_REP_TYPE_INVALID) = 0;
UPDATE zx_report_codes_assoc_int rca_int
SET reporting_code_id =
(
SELECT Reporting_Code_Id
FROM ZX_REPORTING_CODES_VL RepCode
WHERE RepCode.Reporting_Type_Id = rca_int.Reporting_Type_Id
AND SYSDATE BETWEEN RepCode.EFFECTIVE_FROM
AND NVL(RepCode.EFFECTIVE_TO,SYSDATE)
AND RepCode.Reporting_Code_Name = rca_int.reporting_code_name
)
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_RCA_REP_TYPE_INVALID+
G_ES_RCA_REP_CODE_INVALID) = 0
AND reporting_code_id IS NULL
AND reporting_code_name IS NOT NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' records updated with valid reporting_code_id';
UPDATE zx_report_codes_assoc_int rca_int
SET error_number = error_number + G_ES_RCA_REP_CODE_VAL_INVALID
WHERE record_status = G_RS_IN_PROCESS
AND request_id = G_REQUEST_ID
AND BITAND(error_number,G_ES_RCA_REP_TYPE_INVALID+
G_ES_RCA_REP_CODE_INVALID) = 0
AND NOT EXISTS (
SELECT 1
FROM ZX_REPORTING_CODES_B RepCode
WHERE RepCode.Reporting_Type_Id = rca_int.Reporting_Type_Id
AND RepCode.Reporting_Code_Id = rca_int.Reporting_Code_Id
AND NVL(reporting_code_char_value, G_MISS_CHAR) =
NVL(rca_int.reporting_code_char_value, G_MISS_CHAR)
AND NVL(reporting_code_date_value, FND_API.G_MISS_DATE) =
NVL(rca_int.reporting_code_date_value, FND_API.G_MISS_DATE)
AND NVL(reporting_code_num_value, G_MISS_NUM) =
NVL(rca_int.reporting_code_num_value, G_MISS_NUM)
AND SYSDATE BETWEEN RepCode. EFFECTIVE_FROM
AND NVL(RepCode. EFFECTIVE_TO,SYSDATE)
);
UPDATE zx_party_tax_profile_int ptp_int
SET party_tax_profile_id = zx_party_tax_profile_s.nextval
WHERE record_status = G_RS_VALID
AND request_id = G_REQUEST_ID
AND party_tax_profile_id is NULL
AND dml_type = 'CREATE';
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' ptp records updated with valid party_tax_profile_id';
UPDATE zx_registrations_int reg_int
SET party_tax_profile_id =
(
SELECT party_tax_profile_id
FROM zx_party_tax_profile_int ptp_int
WHERE ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
AND ptp_int.record_status = G_RS_VALID
AND ptp_int.request_id = G_REQUEST_ID
)
WHERE record_status = G_RS_VALID
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' reg records updated with valid party_tax_profile_id';
UPDATE zx_exemptions_int exe_int
SET party_tax_profile_id =
(
SELECT party_tax_profile_id
FROM zx_party_tax_profile_int ptp_int
WHERE ptp_int.intf_ptp_reference = exe_int.intf_ptp_reference
AND ptp_int.record_status = G_RS_VALID
AND ptp_int.request_id = G_REQUEST_ID
)
WHERE record_status = G_RS_VALID
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' exe records updated with valid party_tax_profile_id';
UPDATE zx_report_codes_assoc_int rca_int
SET (party_tax_profile_id,entity_id) =
(
SELECT party_tax_profile_id,party_tax_profile_id
FROM zx_party_tax_profile_int ptp_int
WHERE ptp_int.intf_ptp_reference = rca_int.intf_ptp_reference
AND ptp_int.record_status = G_RS_VALID
AND ptp_int.request_id = G_REQUEST_ID
)
WHERE record_status = G_RS_VALID
AND request_id = G_REQUEST_ID;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' rca records updated with valid party_tax_profile_id';
INSERT INTO zx_party_tax_profile(
PARTY_TYPE_CODE,
SUPPLIER_FLAG,
CUSTOMER_FLAG,
SITE_FLAG,
PROCESS_FOR_APPLICABILITY_FLAG,
ROUNDING_LEVEL_CODE,
ROUNDING_RULE_CODE,
WITHHOLDING_START_DATE,
INCLUSIVE_TAX_FLAG,
ALLOW_AWT_FLAG,
USE_LE_AS_SUBSCRIBER_FLAG,
LEGAL_ESTABLISHMENT_FLAG,
FIRST_PARTY_LE_FLAG,
REPORTING_AUTHORITY_FLAG,
COLLECTING_AUTHORITY_FLAG,
PROVIDER_TYPE_CODE,
CREATE_AWT_DISTS_TYPE_CODE,
CREATE_AWT_INVOICES_TYPE_CODE,
TAX_CLASSIFICATION_CODE,
SELF_ASSESS_FLAG,
ALLOW_OFFSET_TAX_FLAG,
EFFECTIVE_FROM_USE_LE,
RECORD_TYPE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
PROGRAM_LOGIN_ID,
PARTY_TAX_PROFILE_ID,
PARTY_ID,
REP_REGISTRATION_NUMBER,
OBJECT_VERSION_NUMBER,
REGISTRATION_TYPE_CODE,
COUNTRY_CODE,
MERGED_TO_PTP_ID,
MERGED_STATUS_CODE
)
(
SELECT PARTY_TYPE_CODE,
DECODE(G_CUST_INTF_TABLE, G_INTF_CUS_TABLE_NAME,'N',
G_INTF_SUP_TABLE_NAME,'Y',
G_INTF_SUP_SITE_TABLE_NAME,'Y',
NULL), --SUPPLIER_FLAG,
DECODE(G_CUST_INTF_TABLE, G_INTF_CUS_TABLE_NAME,'Y',
G_INTF_SUP_TABLE_NAME,'N',
G_INTF_SUP_SITE_TABLE_NAME,'N',
NULL), --CUSTOMER_FLAG,
DECODE(PARTY_TYPE_CODE,'THIRD_PARTY_SITE','Y','N'), --SITE_FLAG,
DECODE(G_CUST_INTF_TABLE, G_INTF_SUP_TABLE_NAME,process_for_applicability_flag,
G_INTF_SUP_SITE_TABLE_NAME,process_for_applicability_flag,
NULL), --PROCESS_FOR_APPLICABILITY_FLAG,
rounding_level_code,
rounding_rule_code,
NULL, --WITHHOLDING_START_DATE,
DECODE(G_CUST_INTF_TABLE, G_INTF_SUP_TABLE_NAME,inclusive_tax_flag,
G_INTF_SUP_SITE_TABLE_NAME,inclusive_tax_flag,
NULL), --INCLUSIVE_TAX_FLAG,
NULL, --ALLOW_AWT_FLAG,
NULL, --USE_LE_AS_SUBSCRIBER_FLAG,
NULL, --LEGAL_ESTABLISHMENT_FLAG,
NULL, --FIRST_PARTY_LE_FLAG,
NULL, --REPORTING_AUTHORITY_FLAG,
NULL, --COLLECTING_AUTHORITY_FLAG,
NULL, --PROVIDER_TYPE_CODE
NULL, --CREATE_AWT_DISTS_TYPE_CODE,
NULL, --CREATE_AWT_INVOICES_TYPE_CODE,
TAX_CLASSIFICATION_CODE,
NULL, --SELF_ASSESS_FLAG,
DECODE(G_CUST_INTF_TABLE, G_INTF_SUP_TABLE_NAME,allow_offset_tax_flag,
G_INTF_SUP_SITE_TABLE_NAME,allow_offset_tax_flag,
NULL), --ALLOW_OFFSET_TAX_FLAG,
NULL, --EFFECTIVE_FROM_USE_LE,
G_RECORD_TYPE_CODE, --RECORD_TYPE_CODE,
G_USER_ID, --CREATED_BY
G_SYSDATE, --CREATION_DATE,
G_USER_ID, --LAST_UPDATED_BY
G_SYSDATE, --LAST_UPDATE_DATE
G_LOGIN_ID, --LAST_UPDATE_LOGIN
G_REQUEST_ID, --REQUEST_ID,
G_PROG_APPID, --PROGRAM_APPLICATION_ID
G_PROG_ID, --PROGRAM_ID
NULL, --ATTRIBUTE1
NULL, --ATTRIBUTE2
NULL, --ATTRIBUTE3
NULL, --ATTRIBUTE4
NULL, --ATTRIBUTE5
NULL, --ATTRIBUTE6
NULL, --ATTRIBUTE7
NULL, --ATTRIBUTE8
NULL, --ATTRIBUTE9
NULL, --ATTRIBUTE10
NULL, --ATTRIBUTE11
NULL, --ATTRIBUTE12
NULL, --ATTRIBUTE13
NULL, --ATTRIBUTE14
NULL, --ATTRIBUTE15
NULL, --ATTRIBUTE_CATEGORY
G_LOGIN_ID, --PROGRAM_LOGIN_ID
PARTY_TAX_PROFILE_ID,
PARTY_ID,
registration_number,
1, --OBJECT_VERSION_NUMBER,
REGISTRATION_TYPE_CODE,
COUNTRY_CODE,
NULL, --MERGED_TO_PTP_ID,
NULL --MERGED_STATUS_CODE
FROM zx_party_tax_profile_int
WHERE record_status = G_RS_VALID
AND request_id = G_REQUEST_ID
AND error_number = 0
);
G_DEBUG_STATEMENT:= 'Inserted '||SQL%ROWCOUNT||' records into zx_party_tax_profile ';
INSERT INTO zx_registrations (
REGISTRATION_TYPE_CODE,
REGISTRATION_NUMBER,
VALIDATION_RULE,
ROUNDING_RULE_CODE,
TAX_JURISDICTION_CODE,
SELF_ASSESS_FLAG,
REGISTRATION_STATUS_CODE,
REGISTRATION_SOURCE_CODE,
REGISTRATION_REASON_CODE,
TAX,
TAX_REGIME_CODE,
INCLUSIVE_TAX_FLAG,
HAS_TAX_EXEMPTIONS_FLAG,
EFFECTIVE_FROM,
EFFECTIVE_TO,
REP_PARTY_TAX_NAME,
DEFAULT_REGISTRATION_FLAG,
BANK_ACCOUNT_NUM,
LEGAL_LOCATION_ID,
RECORD_TYPE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
TAX_CLASSIFICATION_CODE,
PROGRAM_LOGIN_ID,
REGISTRATION_ID,
TAX_AUTHORITY_ID,
REP_TAX_AUTHORITY_ID,
COLL_TAX_AUTHORITY_ID,
PARTY_TAX_PROFILE_ID,
LEGAL_REGISTRATION_ID,
BANK_ID,
BANK_BRANCH_ID,
ACCOUNT_ID,
ACCOUNT_SITE_ID,
OBJECT_VERSION_NUMBER,
ROUNDING_LEVEL_CODE,
ACCOUNT_TYPE_CODE,
MERGED_TO_REGISTRATION_ID
)
(SELECT REGISTRATION_TYPE_CODE,
REGISTRATION_NUMBER,
NULL, --VALIDATION_RULE
ROUNDING_RULE_CODE,
TAX_JURISDICTION_CODE,
NULL, --SELF_ASSESS_FLAG
REGISTRATION_STATUS_CODE,
REGISTRATION_SOURCE_CODE,
REGISTRATION_REASON_CODE,
TAX_CODE,
TAX_REGIME_CODE,
INCLUSIVE_TAX_FLAG,
NULL, --HAS_TAX_EXEMPTIONS_FLAG
EFFECTIVE_FROM,
EFFECTIVE_TO,
NULL, --REP_PARTY_TAX_NAME
DEFAULT_REGISTRATION_FLAG,
NULL, --BANK_ACCOUNT_NUM
LEGAL_LOCATION_ID,
G_RECORD_TYPE_CODE, --RECORD_TYPE_CODE
G_USER_ID, --CREATED_BY
G_SYSDATE, --CREATION_DATE
G_USER_ID, --LAST_UPDATED_BY
G_SYSDATE, --LAST_UPDATE_DATE
G_LOGIN_ID, --LAST_UPDATE_LOGIN
G_REQUEST_ID, --REQUEST_ID
G_PROG_APPID, --PROGRAM_APPLICATION_ID
G_PROG_ID, --PROGRAM_ID
NULL, --ATTRIBUTE1
NULL, --ATTRIBUTE2
NULL, --ATTRIBUTE3
NULL, --ATTRIBUTE4
NULL, --ATTRIBUTE5
NULL, --ATTRIBUTE6
NULL, --ATTRIBUTE7
NULL, --ATTRIBUTE8
NULL, --ATTRIBUTE9
NULL, --ATTRIBUTE10
NULL, --ATTRIBUTE11
NULL, --ATTRIBUTE12
NULL, --ATTRIBUTE13
NULL, --ATTRIBUTE14
NULL, --ATTRIBUTE15
NULL, --ATTRIBUTE_CATEGORY
NULL, --TAX_CLASSIFICATION_CODE
G_LOGIN_ID, --PROGRAM_LOGIN_ID
ZX_REGISTRATIONS_S.nextval, --REGISTRATION_ID
TAX_AUTHORITY_ID,
NULL, --REP_TAX_AUTHORITY_ID
NULL, --COLL_TAX_AUTHORITY_ID
PARTY_TAX_PROFILE_ID,
LEGAL_REGISTRATION_ID,
NULL, --BANK_ID
NULL, --BANK_BRANCH_ID
NULL, --ACCOUNT_ID
NULL, --ACCOUNT_SITE_ID
1, --OBJECT_VERSION_NUMBER
NULL, --ROUNDING_LEVEL_CODE
NULL, --ACCOUNT_TYPE_CODE
NULL --MERGED_TO_REGISTRATION_ID
FROM zx_registrations_int
WHERE record_status = G_RS_VALID
AND request_id = G_REQUEST_ID
AND error_number = 0);
G_DEBUG_STATEMENT:= 'Inserted '||SQL%ROWCOUNT||' records into zx_registrations ';
INSERT INTO zx_exemptions(
TAX_EXEMPTION_ID,
EXEMPTION_TYPE_CODE,
EXEMPTION_STATUS_CODE,
TAX_REGIME_CODE,
TAX_STATUS_CODE,
TAX,
TAX_RATE_CODE,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE,
ISSUING_TAX_AUTHORITY_ID,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CONTENT_OWNER_ID,
PRODUCT_ID,
INVENTORY_ORG_ID,
RATE_MODIFIER,
TAX_JURISDICTION_ID,
DET_FACTOR_TEMPL_CODE,
RECORD_TYPE_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
APPLY_TO_LOWER_LEVELS_FLAG,
OBJECT_VERSION_NUMBER,
PARTY_TAX_PROFILE_ID,
CUST_ACCOUNT_ID,
SITE_USE_ID,
DUPLICATE_EXEMPTION
)
(
SELECT zx_exemptions_s.nextval, --TAX_EXEMPTION_ID,
EXEMPTION_TYPE_CODE,
EXEMPTION_STATUS_CODE,
TAX_REGIME_CODE,
TAX_STATUS_CODE,
TAX_CODE,
TAX_RATE_CODE,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE,
ISSUING_TAX_AUTHORITY_ID,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CONTENT_OWNER_ID,
INVENTORY_ITEM_ID, --PRODUCT_ID,
INVENTORY_ORG_ID,
RATE_MODIFIER,
TAX_JURISDICTION_ID,
NULL, --DET_FACTOR_TEMPL_CODE,
G_RECORD_TYPE_CODE, --RECORD_TYPE_CODE,
G_USER_ID, --CREATED_BY
G_SYSDATE, --CREATION_DATE,
G_USER_ID, --LAST_UPDATED_BY
G_SYSDATE, --LAST_UPDATE_DATE
G_LOGIN_ID, --LAST_UPDATE_LOGIN
G_REQUEST_ID, --REQUEST_ID,
G_PROG_APPID, --PROGRAM_APPLICATION_ID,
G_PROG_ID, --PROGRAM_ID,
NULL, --PROGRAM_LOGIN_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
APPLY_TO_LOWER_LEVELS_FLAG,
1, --OBJECT_VERSION_NUMBER,
PARTY_TAX_PROFILE_ID,
NULL, --CUST_ACCOUNT_ID,
NULL, --SITE_USE_ID,
0 --DUPLICATE_EXEMPTION
FROM zx_exemptions_int
WHERE record_status = G_RS_VALID
AND request_id = G_REQUEST_ID
AND error_number = 0);
G_DEBUG_STATEMENT:= 'Inserted '||SQL%ROWCOUNT||' records into zx_exemptions ';
INSERT INTO zx_report_codes_assoc(
REPORTING_CODE_ASSOC_ID,
ENTITY_CODE,
ENTITY_ID,
EXCEPTION_CODE,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REPORTING_CODE_CHAR_VALUE,
REPORTING_CODE_DATE_VALUE,
REPORTING_CODE_NUM_VALUE,
REPORTING_TYPE_ID,
REPORTING_CODE_ID,
OBJECT_VERSION_NUMBER
)
(
SELECT ZX_REPORT_CODES_ASSOC_S.nextval, --REPORTING_CODE_ASSOC_ID,
ENTITY_CODE,
ENTITY_ID,
NULL, --EXCEPTION_CODE,
EFFECTIVE_FROM,
EFFECTIVE_TO,
G_USER_ID, --CREATED_BY
G_SYSDATE, --CREATION_DATE,
G_USER_ID, --LAST_UPDATED_BY
G_SYSDATE, --LAST_UPDATE_DATE
G_LOGIN_ID, --LAST_UPDATE_LOGIN
REPORTING_CODE_CHAR_VALUE,
REPORTING_CODE_DATE_VALUE,
REPORTING_CODE_NUM_VALUE,
REPORTING_TYPE_ID,
REPORTING_CODE_ID,
1 --OBJECT_VERSION_NUMBER
FROM zx_report_codes_assoc_int
WHERE record_status = G_RS_VALID
AND request_id = G_REQUEST_ID
AND error_number = 0
);
G_DEBUG_STATEMENT:= 'Inserted '||SQL%ROWCOUNT||' records into zx_report_codes_assoc ';
SELECT *
FROM zx_party_tax_profile_int
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_ERROR;
SELECT ptp_int.party_type_code, ptp_int.party_id, reg_int.*
FROM zx_registrations_int reg_int, zx_party_tax_profile_int ptp_int
WHERE reg_int.request_id = G_REQUEST_ID
AND reg_int.record_status = G_RS_ERROR
AND ptp_int.intf_ptp_reference = reg_int.intf_ptp_reference
AND ptp_int.request_id = reg_int.request_id;
SELECT ptp_int.party_type_code, ptp_int.party_id, exe_int.*
FROM zx_exemptions_int exe_int, zx_party_tax_profile_int ptp_int
WHERE exe_int.request_id = G_REQUEST_ID
AND exe_int.record_status = G_RS_ERROR
AND ptp_int.intf_ptp_reference = exe_int.intf_ptp_reference
AND ptp_int.request_id = exe_int.request_id;
SELECT ptp_int.party_type_code, ptp_int.party_id, rca_int.*
FROM zx_report_codes_assoc_int rca_int, zx_party_tax_profile_int ptp_int
WHERE rca_int.request_id = G_REQUEST_ID
AND rca_int.record_status = G_RS_ERROR
AND ptp_int.intf_ptp_reference = rca_int.intf_ptp_reference
AND ptp_int.request_id = rca_int.request_id;
UPDATE zx_party_tax_profile_int
SET intf_ptp_reference = G_REQUEST_ID||'**'||ROWNUM
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_ERROR
AND intf_ptp_reference IS NULL;
INSERT INTO zx_errors_int
(request_id
,error_table_name
,intf_ptp_reference
,party_type_code
,message_text
,prog_int_record_ref
,party_id
)
VALUES
(G_REQUEST_ID
,G_INTF_PTP_TABLE_NAME
,l_error_table(loop_index).intf_ptp_reference
,l_error_table(loop_index).party_type_code
,l_error_table(loop_index).message_text
,NULL
,l_error_table(loop_index).party_id
);
l_error_table.DELETE();
l_error_table.DELETE();
INSERT INTO zx_errors_int
(request_id
,error_table_name
,intf_ptp_reference
,party_type_code
,message_text
,prog_int_record_ref
,party_id
)
VALUES
(G_REQUEST_ID
,G_INTF_REG_TABLE_NAME
,l_error_table(loop_index).intf_ptp_reference
,l_error_table(loop_index).party_type_code
,l_error_table(loop_index).message_text
,l_error_table(loop_index).prog_int_record_ref
,l_error_table(loop_index).party_id
);
l_error_table.DELETE();
l_error_table.DELETE();
INSERT INTO zx_errors_int
(request_id
,error_table_name
,intf_ptp_reference
,party_type_code
,message_text
,prog_int_record_ref
,party_id
)
VALUES
(G_REQUEST_ID
,G_INTF_EXE_TABLE_NAME
,l_error_table(loop_index).intf_ptp_reference
,l_error_table(loop_index).party_type_code
,l_error_table(loop_index).message_text
,l_error_table(loop_index).prog_int_record_ref
,l_error_table(loop_index).party_id
);
l_error_table.DELETE();
l_error_table.DELETE();
INSERT INTO zx_errors_int
(request_id
,error_table_name
,intf_ptp_reference
,party_type_code
,message_text
,prog_int_record_ref
,party_id
)
VALUES
(G_REQUEST_ID
,G_INTF_RCA_TABLE_NAME
,l_error_table(loop_index).intf_ptp_reference
,l_error_table(loop_index).party_type_code
,l_error_table(loop_index).message_text
,l_error_table(loop_index).prog_int_record_ref
,l_error_table(loop_index).party_id
);
l_error_table.DELETE();
l_error_table.DELETE();
,p_sql_select_insert_ptp_intf IN VARCHAR2
,p_sql_from_insert_ptp_intf IN VARCHAR2
,p_insert_only IN VARCHAR2
,p_sql_select_valid_party_id IN VARCHAR2
,p_sql_from_valid_party_id IN VARCHAR2
,p_sql_select_insert_ptp_prod IN VARCHAR2
,p_sql_from_insert_ptp_prod IN VARCHAR2
,p_commit IN VARCHAR2
,p_batch_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_dynamic_sql VARCHAR2(4000);
( p_sql_select_insert_ptp_intf IS NULL
AND p_sql_from_insert_ptp_intf IS NOT NULL
)
OR
( p_sql_select_insert_ptp_intf IS NOT NULL
AND p_sql_from_insert_ptp_intf IS NULL
)
)
)
OR NVL(p_insert_only,G_MISS_CHAR)
NOT IN (G_TRUE, G_FALSE)
OR (p_insert_only IS NOT NULL AND
(
( p_sql_select_valid_party_id IS NULL
AND p_sql_from_valid_party_id IS NOT NULL
)
OR
( p_sql_select_valid_party_id IS NOT NULL
AND p_sql_from_valid_party_id IS NULL
)
)
OR
(
( p_sql_select_insert_ptp_prod IS NULL
AND p_sql_from_insert_ptp_prod IS NOT NULL
)
OR
( p_sql_select_insert_ptp_prod IS NOT NULL
AND p_sql_from_insert_ptp_prod IS NULL
)
)
)
OR NVL(p_commit,G_MISS_CHAR)
NOT IN (G_TRUE, G_FALSE)
THEN
x_return_status := G_CONC_RET_STS_ERROR;
IF p_sql_select_insert_ptp_intf IS NOT NULL
AND p_sql_from_insert_ptp_intf IS NOT NULL THEN
BEGIN
l_dynamic_sql := 'INSERT INTO zx_party_tax_profile_int '
||' (registration_number ,country_code ,dml_type'
||' ,intf_party_reference ,intf_party_site_reference ,party_type_code'
||' ,batch_id)'
||' '|| p_sql_select_insert_ptp_intf
||' ,:G_BATCH_ID'
||' '||p_sql_from_insert_ptp_intf
||' AND NOT EXISTS '
||' (SELECT 1 FROM zx_party_tax_profile_int zx_ptp_int'
||' WHERE NVL(zx_ptp_int.record_status,'||G_RS_ERROR||') <> '||G_RS_VALID
||' AND NVL(zx_ptp_int.batch_id,'||G_MISS_NUM||') '
||'= '||NVL(G_BATCH_ID,G_MISS_NUM)
||' AND NVL(zx_ptp_int.intf_party_reference,'''||G_MISS_CHAR||''') '
||' = NVL(NVL(hz_rcia.orig_system_party_ref'
||',hz_rcia.orig_system_customer_ref),'''||G_MISS_CHAR||''')'
||' AND NVL(zx_ptp_int.intf_party_site_reference,'''||G_MISS_CHAR||''')'
||' = NVL(hz_rcia.orig_system_address_ref,'''||G_MISS_CHAR||''')'
||')';
,p_insert_only => G_FALSE
,x_return_status => x_return_status
,x_msg_data => x_msg_data
);
SELECT G_CONC_RET_STS_WARNING
INTO x_return_status
FROM DUAL
WHERE EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_errors_int
WHERE request_id = G_REQUEST_ID
AND ROWNUM=1);
IF FND_API.TO_BOOLEAN(p_insert_only) THEN
-- sample code for p_sql_select_valid_party_id
-- SELECT party_id, 'THIRD_PARTY' as party_type_code, orig_system_reference as intf_party_reference, NULL as intf_party_site_reference
-- sample code for p_sql_from_valid_party_id
-- FROM hz_parties
-- WHERE request_id = G_REQUEST_ID
-- AND .. ..
l_date6 := SYSDATE;
IF p_sql_select_valid_party_id IS NOT NULL
AND p_sql_from_valid_party_id IS NOT NULL THEN
BEGIN
l_dynamic_sql := 'MERGE INTO zx_party_tax_profile_int ptp_int'
||' USING ('||p_sql_select_valid_party_id
||' '||p_sql_from_valid_party_id ||') hz_int'
||' ON ('
|| ' ptp_int.request_id = '||G_REQUEST_ID
|| ' AND ptp_int.record_status = '||G_RS_VALID
|| ' AND ptp_int.party_type_code = hz_int.party_type_code'
|| ' AND ptp_int.intf_party_reference = hz_int.intf_party_reference'
|| ' AND nvl(ptp_int.intf_party_site_reference,'''||G_MISS_CHAR||''') '
|| ' = nvl(hz_int.intf_party_site_reference,'''||G_MISS_CHAR||''') '
|| ' )'
|| ' WHEN MATCHED THEN UPDATE'
|| ' SET party_id = hz_int.party_id';
UPDATE zx_party_tax_profile_int zx_ptp_int
SET record_status = NULL
WHERE request_id = G_REQUEST_ID
AND record_status = G_RS_VALID
AND party_id IS NULL;
G_DEBUG_STATEMENT:= SQL%ROWCOUNT||' PTP records updated with record_status = NULL';
,p_insert_only => p_insert_only
,x_return_status => x_return_status
,x_msg_data => x_msg_data
);
l_dynamic_sql := 'INSERT INTO ZX_PARTY_TAX_PROFILE'
||'(party_id,party_type_code,country_code'
||',party_tax_profile_id,creation_date,created_by,last_update_date'
||',last_updated_by,last_update_login,object_version_number,request_id'
||',program_application_id,program_id,record_type_code)'
|| p_sql_select_insert_ptp_prod
||' ,zx_party_tax_profile_s.nextval ,:g_date1 '
||','||G_USER_ID||',:g_date2'
||','||G_USER_ID||','||G_LOGIN_ID||',1'||','||G_REQUEST_ID
||','||G_PROG_APPID||','||G_PROG_ID||','''||G_RECORD_TYPE_CODE||''''
||' '||p_sql_from_insert_ptp_prod||' '
||' AND NOT EXISTS '
||' (SELECT 1 FROM zx_party_tax_profile_int zx_ptp_int'
||' WHERE zx_ptp_int.request_id = '||G_REQUEST_ID
||' AND zx_ptp_int.party_id = hz_insert.party_id'
||' AND zx_ptp_int.party_type_code = hz_insert.party_type_code'
||')';
G_DEBUG_STATEMENT:= 'Completed Insert Mode for IMPORT_WRAPPER';
END IF; -- end p_insert_only
IF FND_API.TO_BOOLEAN(p_insert_only) THEN
log_now (p_log_level => FND_FILE.LOG
,p_message => '| Time Taken to Populate Party Id '||(l_date7-l_date6)*86400||' Secs'
);
,p_message => '| Total Time Taken for Insert Mode '||(l_date12-l_date5)*86400||' Secs'
);
SELECT ptp_int.intf_party_reference, ptp_int.intf_party_site_reference
,DECODE(err_int.error_table_name
,G_INTF_PTP_TABLE_NAME,cp_msg_ptp_table
,G_INTF_EXE_TABLE_NAME,cp_msg_exe_table
,G_INTF_REG_TABLE_NAME,cp_msg_reg_table
,G_INTF_RCA_TABLE_NAME,cp_msg_rca_table
,' ') AS user_table_name
,err_int.message_text
FROM zx_errors_int err_int, zx_party_tax_profile_int ptp_int
WHERE err_int.request_id = G_REQUEST_ID
AND err_int.request_id = ptp_int.request_id
AND err_int.intf_ptp_reference = ptp_int.intf_ptp_reference
ORDER BY intf_party_reference, intf_party_site_reference NULLS FIRST,
DECODE(error_table_name,G_INTF_PTP_TABLE_NAME,1
,G_INTF_EXE_TABLE_NAME,2
,G_INTF_REG_TABLE_NAME,3
,G_INTF_RCA_TABLE_NAME,4,5),
prog_int_record_ref NULLS FIRST;
SELECT NVL(SUM(DECODE(NVL(record_status,G_RS_ERROR),G_RS_ERROR,1,0)),0), NVL(SUM(1),0)
INTO l_count_exception_records, l_count_success_records
FROM zx_party_tax_profile_int
WHERE request_id = G_REQUEST_ID;
SELECT NVL(SUM(DECODE(record_status,G_RS_ERROR,1,0)),0), NVL(SUM(1),0)
INTO l_count_exception_records, l_count_success_records
FROM zx_registrations_int
WHERE request_id = G_REQUEST_ID;
SELECT NVL(SUM(DECODE(record_status,G_RS_ERROR,1,0)),0), NVL(SUM(1),0)
INTO l_count_exception_records, l_count_success_records
FROM zx_exemptions_int
WHERE request_id = G_REQUEST_ID;
SELECT NVL(SUM(DECODE(record_status,G_RS_ERROR,1,0)),0), NVL(SUM(1),0)
INTO l_count_exception_records, l_count_success_records
FROM zx_report_codes_assoc_int
WHERE request_id = G_REQUEST_ID;
SELECT 1
INTO l_num1
FROM DUAL
WHERE EXISTS (SELECT /*+ FIRST_ROWS(1) */ 1
FROM zx_errors_int
WHERE request_id = G_REQUEST_ID
AND ROWNUM=1);