[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT from_reg.registration_id registration_id_from,
to_reg.registration_id registration_id_to,
CASE WHEN from_reg.effective_from > to_reg.effective_from
THEN to_reg.effective_from
ELSE from_reg.effective_from
END as update_reg_from_date,
CASE WHEN from_reg.effective_to IS NULL OR to_reg.effective_to IS NULL
THEN NULL
WHEN from_reg.effective_to > to_reg.effective_to
THEN from_reg.effective_to
ELSE to_reg.effective_to
END as update_reg_to_date,
CASE WHEN to_reg.LEGAL_LOCATION_ID IS NULL AND from_reg.LEGAL_LOCATION_ID IS NOT NULL
THEN from_reg.LEGAL_LOCATION_ID
END as location_id_to,
CASE WHEN to_reg.REGISTRATION_SOURCE_CODE IS NULL AND from_reg.REGISTRATION_SOURCE_CODE IS NOT NULL
THEN from_reg.REGISTRATION_SOURCE_CODE
END as reg_src_code_to,
CASE WHEN to_reg.REGISTRATION_REASON_CODE IS NULL AND from_reg.REGISTRATION_REASON_CODE IS NOT NULL
THEN from_reg.REGISTRATION_REASON_CODE
END as reg_reason_code_to,
CASE WHEN to_reg.REP_TAX_AUTHORITY_ID IS NULL AND from_reg.REP_TAX_AUTHORITY_ID IS NOT NULL
THEN from_reg.REP_TAX_AUTHORITY_ID
END as rep_tax_auth_id_to,
CASE WHEN to_reg.COLL_TAX_AUTHORITY_ID IS NULL AND from_reg.COLL_TAX_AUTHORITY_ID IS NOT NULL
THEN from_reg.COLL_TAX_AUTHORITY_ID
END as coll_tax_auth_id_to
FROM zx_registrations from_reg,
zx_registrations to_reg
WHERE from_reg.PARTY_TAX_PROFILE_ID IN
(SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_from_party_id
AND party_type_code = 'THIRD_PARTY'
)
AND to_reg.PARTY_TAX_PROFILE_ID IN
(SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_to_party_id
AND party_type_code = 'THIRD_PARTY'
)
AND from_reg.registration_number = to_reg.registration_number
AND from_reg.registration_id <> to_reg.registration_id;
UPDATE zx_registrations
SET merged_to_registration_id = registration_rec.registration_id_to,
effective_to = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE registration_id = registration_rec.registration_id_from;
UPDATE zx_registrations
SET effective_from = registration_rec.update_reg_from_date,
effective_to = registration_rec.update_reg_to_date,
legal_location_id = registration_rec.location_id_to,
registration_source_code = registration_rec.reg_src_code_to,
registration_reason_code = registration_rec.reg_reason_code_to,
rep_tax_authority_id = registration_rec.rep_tax_auth_id_to,
coll_tax_authority_id = registration_rec.coll_tax_auth_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE registration_id = registration_rec.registration_id_to;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT Party_Tax_Profile_id
FROM zx_party_tax_profile prof
WHERE prof.party_id = p_fk_id
AND prof.party_type_code = 'THIRD_PARTY';
SELECT code_assignment_id, class_category, class_code, END_DATE_ACTIVE
FROM hz_code_assignments
WHERE owner_table_name = 'ZX_PARTY_TAX_PROFILE'
AND owner_table_id = p_ptp_id
AND NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE;
SELECT DISTINCT class_category, class_code, END_DATE_ACTIVE
FROM hz_code_assignments
WHERE owner_table_name = 'ZX_PARTY_TAX_PROFILE'
AND owner_table_id = p_ptp_id
AND NVL(END_DATE_ACTIVE,SYSDATE) >= NVL(p_end_date,SYSDATE)
AND class_category = p_class_category
AND class_code = p_class_code
GROUP BY class_category, class_code, END_DATE_ACTIVE;
UPDATE zx_party_tax_profile
set merged_to_ptp_id = l_ptp_id_to,
merged_status_code = 'MERGED',
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE Party_Tax_Profile_id = l_ptp_id_from;
arp_message.set_name('AR','AR_ROWS_UPDATED');
UPDATE hz_code_assignments
set owner_table_id = l_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE code_assignment_id = code_assig.code_assignment_id;
UPDATE zx_registrations
set TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_registrations
set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE REP_TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_registrations
set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_taxes_b
set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE REP_TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_taxes_b
set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_regimes_b
set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE REP_TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_regimes_b
set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_jurisdictions_b
set REP_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE REP_TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_jurisdictions_b
set COLL_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE COLL_TAX_AUTHORITY_ID = p_ptp_id_from;
UPDATE zx_exemptions
set ISSUING_TAX_AUTHORITY_ID = p_ptp_id_to,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE ISSUING_TAX_AUTHORITY_ID = p_ptp_id_from;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT TAX_EXEMPTION_ID
, EXEMPT_CERTIFICATE_NUMBER
, effective_from
, effective_to
, EXEMPTION_TYPE_CODE
, EXEMPTION_STATUS_CODE
, TAX_REGIME_CODE
, TAX_RATE_CODE
, CUST_ACCOUNT_ID
, SITE_USE_ID
, EXEMPT_REASON_CODE
, CONTENT_OWNER_ID
, TAX
, TAX_JURISDICTION_ID
, PRODUCT_ID
, TAX_STATUS_CODE
FROM zx_exemptions exemp
WHERE party_tax_profile_id = p_fk_id;
SELECT TAX_EXEMPTION_ID
FROM zx_exemptions exemp
WHERE party_tax_profile_id = p_to_fk_id
AND exempt_certificate_number = l_certificate_number
AND effective_from = l_effective_from
AND NVL(effective_to,l_effective_to) = l_effective_to
AND exemption_type_code = l_type_code
AND exemption_status_code = l_status_code
AND tax_regime_code = l_tax_regime_code
AND tax_rate_code = l_tax_rate_code
AND cust_account_id = l_cust_account_id
AND site_use_id = l_site_use_id
AND exempt_reason_code = l_exempt_reason_code
AND content_owner_id = l_content_owner_id
AND tax = l_tax
AND tax_jurisdiction_id = l_tax_jurisdiction_id
AND tax_status_code = l_tax_status_code
AND (product_id is null or product_id = l_product_id)
AND duplicate_exemption = 0;
UPDATE zx_exemptions
set --merged_to_exemption_id = l_exemption_id_to,
party_tax_profile_id = p_to_fk_id,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE TAX_EXEMPTION_ID = rec_exe.tax_exemption_id;
arp_message.set_name('AR','AR_ROWS_UPDATED');
SELECT PROCESS_FOR_APPLICABILITY_FLAG
FROM zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = p_ptp_id;
SELECT a.REGISTRATION_TYPE_CODE,
a.REGISTRATION_NUMBER,
a.ROUNDING_RULE_CODE,
NVL(a.SELF_ASSESS_FLAG, 'N') SELF_ASSESS_FLAG,
NVL(a.INCLUSIVE_TAX_FLAG, 'N') INCLUSIVE_TAX_FLAG,
a.TAX_REGIME_CODE,
a.TAX,
b.REP_REGISTRATION_NUMBER
FROM zx_registrations a, zx_party_tax_profile b
WHERE b.party_tax_profile_id = p_ptp_id_1
AND a.party_tax_profile_id = b.party_tax_profile_id
AND SYSDATE BETWEEN a.effective_from AND NVL(a.effective_to, SYSDATE)
MINUS
SELECT a.REGISTRATION_TYPE_CODE,
a.REGISTRATION_NUMBER,
a.ROUNDING_RULE_CODE,
NVL(a.SELF_ASSESS_FLAG, 'N') SELF_ASSESS_FLAG,
NVL(a.INCLUSIVE_TAX_FLAG, 'N') INCLUSIVE_TAX_FLAG,
a.TAX_REGIME_CODE,
a.TAX,
b.REP_REGISTRATION_NUMBER
FROM zx_registrations a, zx_party_tax_profile b
WHERE b.party_tax_profile_id = p_ptp_id_2
AND a.party_tax_profile_id = b.party_tax_profile_id
AND SYSDATE BETWEEN a.effective_from AND NVL(a.effective_to, SYSDATE);
SELECT 1
FROM zx_registrations a, zx_party_tax_profile b
WHERE b.party_tax_profile_id = p_ptp_id
AND a.party_tax_profile_id = b.party_tax_profile_id
AND SYSDATE BETWEEN a.effective_from AND NVL(a.effective_to, SYSDATE);
SELECT Party_Tax_Profile_id, party_type_code
FROM zx_party_tax_profile prof
WHERE prof.party_id = p_fk_id
AND prof.party_type_code = 'THIRD_PARTY';
SELECT from_party_site_id,
to_party_site_id,
party_tax_profile_id,
zx_party_tax_profile_s.nextval,
cust_account_id,
cust_acct_site_id
BULK COLLECT INTO l_tbl_from_party_site_id,
l_tbl_to_party_site_id,
l_tbl_from_ptp_id,
l_tbl_to_ptp_id,
l_tbl_to_acct_id,
l_tbl_to_acct_site_id
FROM (SELECT cas.party_site_id from_party_site_id,
cas2.party_site_id to_party_site_id,
ptp.party_tax_profile_id,
cas2.cust_account_id,
cas2.cust_acct_site_id,
row_number() over (partition by cas.party_site_id,
cas2.party_site_id,
cas2.cust_account_id,
cas2.cust_acct_site_id
order by rm.customer_site_id
) as party_site_num
FROM RA_CUSTOMER_MERGES rm,
HZ_CUST_ACCT_SITES_ALL cas,
HZ_CUST_ACCT_SITES_ALL cas2,
ZX_PARTY_TAX_PROFILE ptp
WHERE rm.request_id = l_request_id
AND rm.duplicate_address_id = cas.cust_acct_site_id
AND rm.customer_address_id = cas2.cust_acct_site_id
AND ptp.party_id = cas.party_site_id
AND ptp.party_type_code = 'THIRD_PARTY_SITE'
)
WHERE party_site_num = 1;
'Inserting party tax profile records');
arp_message.set_line(G_MODULE_NAME||l_procedure_name||'Inserting party tax profile records');
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
a.party_type_code
,a.supplier_flag
,a.customer_flag
,a.site_flag
,a.process_for_applicability_flag
,a.rounding_level_code
,a.rounding_rule_code
,a.withholding_start_date
,a.inclusive_tax_flag
,a.allow_awt_flag
,a.use_le_as_subscriber_flag
,a.legal_establishment_flag
,a.first_party_le_flag
,a.reporting_authority_flag
,a.collecting_authority_flag
,a.provider_type_code
,a.create_awt_dists_type_code
,a.create_awt_invoices_type_code
,a.tax_classification_code
,a.self_assess_flag
,a.allow_offset_tax_flag
,a.effective_from_use_le
,a.record_type_code
,G_USER_ID
,SYSDATE
,G_LOGIN_ID
,SYSDATE
,G_LOGIN_ID
,l_request_id
,l_prog_appl_id
,l_conc_program_id
,a.attribute1
,a.attribute2
,a.attribute3
,a.attribute4
,a.attribute5
,a.attribute6
,a.attribute7
,a.attribute8
,a.attribute9
,a.attribute10
,a.attribute11
,a.attribute12
,a.attribute13
,a.attribute14
,a.attribute15
,a.attribute_category
,G_LOGIN_ID
,l_tbl_to_ptp_id(i)
,l_tbl_to_party_site_id(i)
,a.rep_registration_number
,1
,a.registration_type_code
,a.country_code
,a.merged_to_ptp_id
,a.merged_status_code
FROM zx_party_tax_profile a
WHERE a.party_tax_profile_id = l_tbl_from_ptp_id(i);
'Inserting registration records');
arp_message.set_line(G_MODULE_NAME||l_procedure_name||'Inserting registration records');
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
,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
,G_USER_ID
,SYSDATE
,G_USER_ID
,SYSDATE
,G_LOGIN_ID
,l_request_id
,l_prog_appl_id
,l_conc_program_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
,tax_classification_code
,G_LOGIN_ID
,zx_registrations_s.nextval
,tax_authority_id
,rep_tax_authority_id
,coll_tax_authority_id
,l_tbl_to_ptp_id(i)
,legal_registration_id
,bank_id
,bank_branch_id
,l_tbl_to_acct_id(i)
,l_tbl_to_acct_site_id(i)
,1
,rounding_level_code
,account_type_code
,merged_to_registration_id
FROM zx_registrations main_tbl
WHERE party_tax_profile_id = l_tbl_from_ptp_id(i)
AND NOT EXISTS
(SELECT 1
FROM zx_registrations ref_data
WHERE ref_data.party_tax_profile_id = l_tbl_from_ptp_id(i)
AND NVL(ref_data.tax_regime_code,FND_API.G_MISS_CHAR) = NVL(main_tbl.tax_regime_code,FND_API.G_MISS_CHAR)
AND NVL(ref_data.tax,FND_API.G_MISS_CHAR) = NVL(main_tbl.tax,FND_API.G_MISS_CHAR)
AND NVL(ref_data.tax_jurisdiction_code,FND_API.G_MISS_CHAR) = NVL(main_tbl.tax_jurisdiction_code,FND_API.G_MISS_CHAR)
AND NVL(ref_data.account_id,FND_API.G_MISS_NUM) = NVL(l_tbl_to_acct_id(i),FND_API.G_MISS_NUM)
AND NVL(ref_data.account_site_id,FND_API.G_MISS_NUM) = NVL(l_tbl_to_acct_site_id(i),FND_API.G_MISS_NUM)
AND ref_data.effective_from = main_tbl.effective_from
);
'Inserting exemption records');
arp_message.set_line(G_MODULE_NAME||l_procedure_name||'Inserting exemption records');
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
,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
,G_USER_ID
,SYSDATE
,G_USER_ID
,SYSDATE
,G_LOGIN_ID
,l_request_id
,l_prog_appl_id
,l_conc_program_id
,G_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
,l_tbl_to_ptp_id(i)
,l_tbl_to_acct_id(i)
,site_use_id
,duplicate_exemption
FROM zx_exemptions
WHERE party_tax_profile_id = l_tbl_from_ptp_id(i);
SELECT * FROM zx_registrations
WHERE party_tax_profile_id = p_from_ptp_id;
SELECT zx_registrations_s.nextval
INTO l_registration_id
FROM dual;
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
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
,tax_classification_code
,registration_id
,tax_authority_id
,rep_tax_authority_id
,coll_tax_authority_id
,party_tax_profile_id
,legal_registration_id
,account_id
,account_site_id
,bank_id
,bank_branch_id
,object_version_number
,rounding_level_code
,account_type_code
,merged_to_registration_id
)
SELECT
rec.registration_type_code
,rec.registration_number
,rec.validation_rule
,rec.rounding_rule_code
,rec.tax_jurisdiction_code
,rec.self_assess_flag
,rec.registration_status_code
,rec.registration_source_code
,rec.registration_reason_code
,rec.tax
,rec.tax_regime_code
,rec.inclusive_tax_flag
,rec.has_tax_exemptions_flag
,rec.effective_from
,rec.effective_to
,rec.rep_party_tax_name
,'N'
,rec.bank_account_num
,rec.legal_location_id
,rec.record_type_code
,G_USER_ID
,SYSDATE
,G_USER_ID
,SYSDATE
,G_LOGIN_ID
,rec.attribute1
,rec.attribute2
,rec.attribute3
,rec.attribute4
,rec.attribute5
,rec.attribute6
,rec.attribute7
,rec.attribute8
,rec.attribute9
,rec.attribute10
,rec.attribute11
,rec.attribute12
,rec.attribute13
,rec.attribute14
,rec.attribute15
,rec.attribute_category
,rec.tax_classification_code
,l_registration_id
,rec.tax_authority_id
,rec.rep_tax_authority_id
,rec.coll_tax_authority_id
,p_to_ptp_id
,rec.legal_registration_id
,rec.account_id
,rec.account_site_id
,rec.bank_id
,rec.bank_branch_id
,1
,rec.rounding_level_code
,rec.account_type_code
,TO_NUMBER(NULL)
FROM DUAL
WHERE NOT EXISTS
(SELECT 1
FROM zx_registrations ref_data
WHERE ref_data.party_tax_profile_id = p_to_ptp_id
AND NVL(ref_data.tax_regime_code,FND_API.G_MISS_CHAR) = NVL(rec.tax_regime_code,FND_API.G_MISS_CHAR)
AND NVL(ref_data.tax,FND_API.G_MISS_CHAR) = NVL(rec.tax,FND_API.G_MISS_CHAR)
AND NVL(ref_data.tax_jurisdiction_code,FND_API.G_MISS_CHAR) = NVL(rec.tax_jurisdiction_code,FND_API.G_MISS_CHAR)
AND NVL(ref_data.account_id,FND_API.G_MISS_NUM) = NVL(rec.account_id,FND_API.G_MISS_NUM)
AND NVL(ref_data.account_site_id,FND_API.G_MISS_NUM) = NVL(rec.account_site_id,FND_API.G_MISS_NUM)
AND ref_data.effective_from = rec.effective_from
);
UPDATE zx_registrations
SET merged_to_registration_id = l_registration_id,
effective_to = SYSDATE,
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE registration_id = rec.registration_id
AND party_tax_profile_id = p_from_ptp_id;
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
,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
,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
,G_USER_ID
,SYSDATE
,G_USER_ID
,SYSDATE
,G_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
,p_to_ptp_id
,cust_account_id
,site_use_id
,duplicate_exemption
FROM zx_exemptions
WHERE party_tax_profile_id = p_from_ptp_id;
SELECT lookup_code
FROM fnd_lookups
WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lookup_code = c_party_type
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active,SYSDATE);
SELECT * FROM zx_party_tax_profile
WHERE party_id = c_party_id
AND party_type_code = c_party_type;
SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = c_party_id
AND party_type_code = c_party_type;
l_insert_ptp BOOLEAN;
l_insert_ptp := FALSE;
l_insert_ptp := TRUE;
IF l_insert_ptp THEN
---------------------------------------
-- Create New PTP record for To Site --
---------------------------------------
ZX_PARTY_TAX_PROFILE_PKG.INSERT_ROW
(p_collecting_authority_flag => l_from_ptp_rec.collecting_authority_flag
,p_provider_type_code => l_from_ptp_rec.provider_type_code
,p_create_awt_dists_type_code => l_from_ptp_rec.create_awt_dists_type_code
,p_create_awt_invoices_type_cod => l_from_ptp_rec.create_awt_invoices_type_code
,p_tax_classification_code => l_from_ptp_rec.tax_classification_code
,p_self_assess_flag => l_from_ptp_rec.self_assess_flag
,p_allow_offset_tax_flag => l_from_ptp_rec.allow_offset_tax_flag
,p_rep_registration_number => l_from_ptp_rec.rep_registration_number
,p_effective_from_use_le => l_from_ptp_rec.effective_from_use_le
,p_record_type_code => l_from_ptp_rec.record_type_code
,p_request_id => fnd_global.conc_request_id
,p_attribute1 => l_from_ptp_rec.attribute1
,p_attribute2 => l_from_ptp_rec.attribute2
,p_attribute3 => l_from_ptp_rec.attribute3
,p_attribute4 => l_from_ptp_rec.attribute4
,p_attribute5 => l_from_ptp_rec.attribute5
,p_attribute6 => l_from_ptp_rec.attribute6
,p_attribute7 => l_from_ptp_rec.attribute7
,p_attribute8 => l_from_ptp_rec.attribute8
,p_attribute9 => l_from_ptp_rec.attribute9
,p_attribute10 => l_from_ptp_rec.attribute10
,p_attribute11 => l_from_ptp_rec.attribute11
,p_attribute12 => l_from_ptp_rec.attribute12
,p_attribute13 => l_from_ptp_rec.attribute13
,p_attribute14 => l_from_ptp_rec.attribute14
,p_attribute15 => l_from_ptp_rec.attribute15
,p_attribute_category => l_from_ptp_rec.attribute_category
,p_party_id => p_to_fk_id
,p_program_login_id => fnd_global.conc_login_id
,p_party_type_code => p_to_party_type
,p_supplier_flag => l_from_ptp_rec.supplier_flag
,p_customer_flag => l_from_ptp_rec.customer_flag
,p_site_flag => l_from_ptp_rec.site_flag
,p_process_for_applicability_fl => l_from_ptp_rec.process_for_applicability_flag
,p_rounding_level_code => l_from_ptp_rec.rounding_level_code
,p_rounding_rule_code => l_from_ptp_rec.rounding_rule_code
,p_withholding_start_date => l_from_ptp_rec.withholding_start_date
,p_inclusive_tax_flag => l_from_ptp_rec.inclusive_tax_flag
,p_allow_awt_flag => l_from_ptp_rec.allow_awt_flag
,p_use_le_as_subscriber_flag => l_from_ptp_rec.use_le_as_subscriber_flag
,p_legal_establishment_flag => l_from_ptp_rec.legal_establishment_flag
,p_first_party_le_flag => l_from_ptp_rec.first_party_le_flag
,p_reporting_authority_flag => l_from_ptp_rec.reporting_authority_flag
,x_return_status => x_return_status
,p_registration_type_code => l_from_ptp_rec.registration_type_code
,p_country_code => l_from_ptp_rec.country_code
);
ZX_PARTY_TAX_PROFILE_PKG.UPDATE_ROW (
p_party_tax_profile_id => l_to_ptp_rec.party_tax_profile_id
,p_collecting_authority_flag => l_from_ptp_rec.collecting_authority_flag
,p_provider_type_code => l_from_ptp_rec.provider_type_code
,p_create_awt_dists_type_code => l_from_ptp_rec.create_awt_dists_type_code
,p_create_awt_invoices_type_cod => l_from_ptp_rec.create_awt_invoices_type_code
,p_tax_classification_code => l_from_ptp_rec.tax_classification_code
,p_self_assess_flag => l_from_ptp_rec.self_assess_flag
,p_allow_offset_tax_flag => l_from_ptp_rec.allow_offset_tax_flag
,p_rep_registration_number => l_from_ptp_rec.rep_registration_number
,p_effective_from_use_le => l_from_ptp_rec.effective_from_use_le
,p_record_type_code => l_from_ptp_rec.record_type_code
,p_request_id => fnd_global.conc_request_id
,p_attribute1 => l_from_ptp_rec.attribute1
,p_attribute2 => l_from_ptp_rec.attribute2
,p_attribute3 => l_from_ptp_rec.attribute3
,p_attribute4 => l_from_ptp_rec.attribute4
,p_attribute5 => l_from_ptp_rec.attribute5
,p_attribute6 => l_from_ptp_rec.attribute6
,p_attribute7 => l_from_ptp_rec.attribute7
,p_attribute8 => l_from_ptp_rec.attribute8
,p_attribute9 => l_from_ptp_rec.attribute9
,p_attribute10 => l_from_ptp_rec.attribute10
,p_attribute11 => l_from_ptp_rec.attribute11
,p_attribute12 => l_from_ptp_rec.attribute12
,p_attribute13 => l_from_ptp_rec.attribute13
,p_attribute14 => l_from_ptp_rec.attribute14
,p_attribute15 => l_from_ptp_rec.attribute15
,p_attribute_category => l_from_ptp_rec.attribute_category
,p_party_id => p_to_fk_id
,p_program_login_id => fnd_global.conc_login_id
,p_party_type_code => p_to_party_type
,p_supplier_flag => l_from_ptp_rec.supplier_flag
,p_customer_flag => l_from_ptp_rec.customer_flag
,p_site_flag => l_from_ptp_rec.site_flag
,p_process_for_applicability_fl => l_from_ptp_rec.process_for_applicability_flag
,p_rounding_level_code => l_from_ptp_rec.rounding_level_code
,p_rounding_rule_code => l_from_ptp_rec.rounding_rule_code
,p_withholding_start_date => l_from_ptp_rec.withholding_start_date
,p_inclusive_tax_flag => l_from_ptp_rec.inclusive_tax_flag
,p_allow_awt_flag => l_from_ptp_rec.allow_awt_flag
,p_use_le_as_subscriber_flag => l_from_ptp_rec.use_le_as_subscriber_flag
,p_legal_establishment_flag => l_from_ptp_rec.legal_establishment_flag
,p_first_party_le_flag => l_from_ptp_rec.first_party_le_flag
,p_reporting_authority_flag => l_from_ptp_rec.reporting_authority_flag
,x_return_status => x_return_status
,p_registration_type_code => l_from_ptp_rec.registration_type_code
,p_country_code => l_from_ptp_rec.country_code
);
'Incorrect status retuned by ZX_Party_Tax_Profile_Pkg.Insert_Row()');
UPDATE zx_party_tax_profile
SET merged_to_ptp_id = l_to_ptp_rec.party_tax_profile_id,
merged_status_code = 'MERGED',
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
object_version_number = object_version_number+1
WHERE Party_Tax_Profile_id = l_from_ptp_rec.party_tax_profile_id;