[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_party_id
AND Party_Type_Code = p_party_type_code;
SELECT ptp.party_tax_profile_id ptp_id
INTO p_ptp_id
FROM xle_tax_associations rel
,zx_party_tax_profile ptp
,xle_etb_profiles etb
WHERE rel.legal_construct_id = etb.establishment_id
AND etb.party_id = ptp.party_id
/* added the below condition for Bug 4878175 */
AND ptp.party_type_code = p_party_type_code
AND rel.entity_id = Biz_Entity
AND rel.legal_parent_id = p_le_id
AND rel.LEGAL_CONSTRUCT = p_construct
AND rel.entity_type = p_type
AND rel.context = p_context
AND rel.effective_from <= sysdate
AND nvl(rel.effective_to,sysdate+1) >= sysdate;
SELECT ptp.party_tax_profile_id ptp_id
INTO p_ptp_id
FROM xle_tax_associations rel
,zx_party_tax_profile ptp
,xle_etb_profiles etb
WHERE rel.legal_construct_id = etb.establishment_id
AND etb.party_id = ptp.party_id
/* added the below condition for Bug 4878175 */
AND ptp.party_type_code = p_party_type_code
AND rel.entity_id = Biz_Entity
AND rel.legal_parent_id = p_le_id
AND rel.LEGAL_CONSTRUCT = p_construct
AND rel.entity_type = p_type
AND rel.context = p_context
AND rel.effective_from <= sysdate
AND nvl(rel.effective_to,sysdate+1) >= sysdate;
SELECT ptp.party_tax_profile_id ptp_id
INTO p_ptp_id
FROM xle_tax_associations rel
,zx_party_tax_profile ptp
,xle_etb_profiles etb
WHERE rel.legal_construct_id = etb.establishment_id
AND etb.party_id = ptp.party_id
/* added the below condition for Bug 4878175 */
AND ptp.party_type_code = p_party_type_code
AND rel.entity_id = p_inventory_loc
AND rel.legal_parent_id = p_le_id
AND rel.LEGAL_CONSTRUCT = p_construct
AND rel.entity_type in (p_type)
AND rel.context = p_context
AND rel.effective_from <= sysdate
AND nvl(rel.effective_to,sysdate+1) >= sysdate;
SELECT ptp.party_tax_profile_id ptp_id
INTO p_ptp_id
FROM xle_tax_associations rel
,zx_party_tax_profile ptp
,xle_etb_profiles etb
WHERE rel.legal_construct_id = etb.establishment_id
AND etb.party_id = ptp.party_id
/* added the below condition for Bug 4878175 */
AND ptp.party_type_code = p_party_type_code
AND rel.entity_id = p_inventory_loc
AND rel.legal_parent_id = p_le_id
AND rel.LEGAL_CONSTRUCT = p_construct
AND rel.entity_type in (p_type)
AND rel.context = p_context
AND rel.effective_from <= sysdate
AND nvl(rel.effective_to,sysdate+1) >= sysdate;
SELECT ptp.party_tax_profile_id
INTO p_ptp_id
FROM zx_party_tax_profile ptp, xle_entity_profiles xle
WHERE xle.legal_entity_id = p_le_id
AND ptp.party_id = xle.party_id
AND ptp.Party_Type_Code = p_party_type_code;
SELECT party_tax_profile_id
FROM zx_party_tax_profile ptp,
xle_etb_profiles xlep
WHERE ptp.party_id = xlep.party_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND xlep.legal_entity_id = p_le_id
AND xlep.main_establishment_flag = 'Y';
SELECT Use_Le_As_Subscriber_Flag, party_tax_profile_id
FROM zx_party_tax_profile
WHERE party_id = p_org_id
AND Party_Type_Code = 'OU';
SELECT ptp.party_tax_profile_id
FROM zx_party_tax_profile ptp,
xle_entity_profiles xle
WHERE xle.legal_entity_id = p_le_id
AND ptp.party_id = xle.party_id
AND ptp.Party_Type_Code = 'FIRST_PARTY';
SELECT location_id
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
SELECT registration_id
FROM zx_registrations
WHERE legal_registration_id = p_le_reg_id;
Objective: Syncronize Tax Registrations after update the Legal Registrations.
Assumption:
Case 1: Legal Registrations number and validation digit have updated.
Update existing tax registrations with end date and create new row.
Case 2: Legal Registrations has updated with end date and created new one.
Update existing tax registrations with end date and create new row.
In Parameters: p_api_version : Required standard IN parameter
p_le_old_reg_id : Prior Legal Registrations ID = Legal Registrations
ID in Tax Registrations.
p_le_old_date : NULL (Case 1)
NOT NULL (Case 2)
p_le_new_erg_id : NULL (Case 1)
NOT NULL (Case 2)
p_le_new_reg_num: New Legal Registration Number
============================================================================= */
Procedure SYNC_TAX_REGISTRATIONS(
p_api_version IN NUMBER,
p_le_old_reg_id IN NUMBER,
p_le_old_end_date IN DATE,
p_le_new_reg_id IN NUMBER,
p_le_new_reg_num IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'SYNC_TAX_REGISTRATIONS';
SELECT
registration_id,
registration_type_code,
registration_number,
validation_rule,
tax_authority_id,
rep_tax_authority_id,
coll_Tax_authority_id,
rounding_rule_code,
tax_jurisdiction_code,
self_assess_flag,
registration_status_code,
registration_source_code,
registration_reason_code,
party_tax_profile_id,
tax,
tax_regime_code,
inclusive_tax_flag,
has_tax_exemptions_flag,
effective_from,
effective_to,
rep_party_tax_name,
legal_registration_id,
default_registration_flag,
bank_id,
bank_branch_id,
bank_account_num,
legal_location_id,
record_type_code,
request_id,
program_application_id,
program_id,
program_login_id,
account_id,
account_site_id,
--site_use_id,
--geo_type_classification_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9 ,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category
FROM zx_registrations
WHERE legal_registration_id = p_le_old_reg_id;
update ZX_REGISTRATIONS set
effective_to = SYSDATE
where registration_id = l_tax_registrations_rec.registration_id;
insert into ZX_REGISTRATIONS (
registration_id,
registration_type_code,
registration_number,
validation_rule,
tax_authority_id,
rep_tax_authority_id,
coll_Tax_authority_id,
rounding_rule_code,
tax_jurisdiction_code,
self_assess_flag,
registration_status_code,
registration_source_code,
registration_reason_code,
party_tax_profile_id,
tax,
tax_regime_code,
inclusive_tax_flag,
has_tax_exemptions_flag,
effective_from,
effective_to,
rep_party_tax_name,
legal_registration_id,
default_registration_flag,
bank_id,
bank_branch_id,
bank_account_num,
legal_location_id,
record_type_code,
request_id,
program_application_id,
program_id,
program_login_id,
account_id,
account_site_id,
--site_use_id,
--geo_type_classification_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9 ,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) values (
Zx_Registrations_S.nextval,
l_tax_registrations_rec.registration_type_code,
p_le_new_reg_num,
l_tax_registrations_rec.validation_rule,
l_tax_registrations_rec.tax_authority_id,
l_tax_registrations_rec.rep_tax_authority_id,
l_tax_registrations_rec.coll_Tax_authority_id,
l_tax_registrations_rec.rounding_rule_code,
l_tax_registrations_rec.tax_jurisdiction_code,
l_tax_registrations_rec.self_assess_flag,
l_tax_registrations_rec.registration_status_code,
l_tax_registrations_rec.registration_source_code,
l_tax_registrations_rec.registration_reason_code,
l_tax_registrations_rec.party_tax_profile_id,
l_tax_registrations_rec.tax,
l_tax_registrations_rec.tax_regime_code,
l_tax_registrations_rec.inclusive_tax_flag,
l_tax_registrations_rec.has_tax_exemptions_flag,
SYSDATE + 1,
NULL,
l_tax_registrations_rec.rep_party_tax_name,
l_tax_registrations_rec.legal_registration_id,
l_tax_registrations_rec.default_registration_flag,
l_tax_registrations_rec.bank_id,
l_tax_registrations_rec.bank_branch_id,
l_tax_registrations_rec.bank_account_num,
l_tax_registrations_rec.legal_location_id,
l_tax_registrations_rec.record_type_code,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
fnd_global.conc_login_id,
l_tax_registrations_rec.account_id,
l_tax_registrations_rec.account_site_id,
--l_tax_registrations_rec.site_use_id,
--l_tax_registrations_rec.geo_type_classification_code,
l_tax_registrations_rec.attribute1,
l_tax_registrations_rec.attribute2,
l_tax_registrations_rec.attribute3,
l_tax_registrations_rec.attribute4,
l_tax_registrations_rec.attribute5,
l_tax_registrations_rec.attribute6,
l_tax_registrations_rec.attribute7,
l_tax_registrations_rec.attribute8,
l_tax_registrations_rec.attribute9 ,
l_tax_registrations_rec.attribute10,
l_tax_registrations_rec.attribute11,
l_tax_registrations_rec.attribute12,
l_tax_registrations_rec.attribute13,
l_tax_registrations_rec.attribute14,
l_tax_registrations_rec.attribute15,
l_tax_registrations_rec.attribute_category,
SYSDATE + 1,
fnd_global.user_id,
SYSDATE + 1,
fnd_global.user_id,
fnd_global.conc_login_id);
update ZX_REGISTRATIONS set
effective_to = p_le_old_end_date
where registration_id = l_tax_registrations_rec.registration_id;
insert into ZX_REGISTRATIONS (
registration_id,
registration_type_code,
registration_number,
validation_rule,
tax_authority_id,
rep_tax_authority_id,
coll_Tax_authority_id,
rounding_rule_code,
tax_jurisdiction_code,
self_assess_flag,
registration_status_code,
registration_source_code,
registration_reason_code,
party_tax_profile_id,
tax,
tax_regime_code,
inclusive_tax_flag,
has_tax_exemptions_flag,
effective_from,
effective_to,
rep_party_tax_name,
legal_registration_id,
default_registration_flag,
bank_id,
bank_branch_id,
bank_account_num,
legal_location_id,
record_type_code,
request_id,
program_application_id,
program_id,
program_login_id,
account_id,
account_site_id,
--site_use_id,
--geo_type_classification_code,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9 ,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) values (
Zx_Registrations_S.nextval,
l_tax_registrations_rec.registration_type_code,
p_le_new_reg_num,
l_tax_registrations_rec.validation_rule,
l_tax_registrations_rec.tax_authority_id,
l_tax_registrations_rec.rep_tax_authority_id,
l_tax_registrations_rec.coll_Tax_authority_id,
l_tax_registrations_rec.rounding_rule_code,
l_tax_registrations_rec.tax_jurisdiction_code,
l_tax_registrations_rec.self_assess_flag,
l_tax_registrations_rec.registration_status_code,
l_tax_registrations_rec.registration_source_code,
l_tax_registrations_rec.registration_reason_code,
l_tax_registrations_rec.party_tax_profile_id,
l_tax_registrations_rec.tax,
l_tax_registrations_rec.tax_regime_code,
l_tax_registrations_rec.inclusive_tax_flag,
l_tax_registrations_rec.has_tax_exemptions_flag,
p_le_old_end_date + 1,
NULL,
l_tax_registrations_rec.rep_party_tax_name,
p_le_new_reg_id,
l_tax_registrations_rec.default_registration_flag,
l_tax_registrations_rec.bank_id,
l_tax_registrations_rec.bank_branch_id,
l_tax_registrations_rec.bank_account_num,
l_tax_registrations_rec.legal_location_id,
l_tax_registrations_rec.record_type_code,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
fnd_global.conc_login_id,
l_tax_registrations_rec.account_id,
l_tax_registrations_rec.account_site_id,
--l_tax_registrations_rec.site_use_id,
--l_tax_registrations_rec.geo_type_classification_code,
l_tax_registrations_rec.attribute1,
l_tax_registrations_rec.attribute2,
l_tax_registrations_rec.attribute3,
l_tax_registrations_rec.attribute4,
l_tax_registrations_rec.attribute5,
l_tax_registrations_rec.attribute6,
l_tax_registrations_rec.attribute7,
l_tax_registrations_rec.attribute8,
l_tax_registrations_rec.attribute9 ,
l_tax_registrations_rec.attribute10,
l_tax_registrations_rec.attribute11,
l_tax_registrations_rec.attribute12,
l_tax_registrations_rec.attribute13,
l_tax_registrations_rec.attribute14,
l_tax_registrations_rec.attribute15,
l_tax_registrations_rec.attribute_category,
p_le_old_end_date + 1,
fnd_global.user_id,
p_le_old_end_date + 1,
fnd_global.user_id,
fnd_global.conc_login_id);
SELECT
ptp.party_tax_profile_id,
ptp.party_id,
ptp.party_type_code,
ptp.supplier_flag,
ptp.customer_flag,
ptp.site_flag,
ptp.process_for_applicability_flag,
ptp.rounding_level_code,
ptp.withholding_start_date,
ptp.allow_awt_flag,
ptp.use_le_as_subscriber_flag,
ptp.legal_establishment_flag,
ptp.first_party_le_flag,
ptp.reporting_authority_flag,
ptp.collecting_authority_flag,
ptp.provider_type_code,
ptp.create_awt_dists_type_code,
ptp.create_awt_invoices_type_code,
ptp.allow_offset_tax_flag,
ptp.effective_from_use_le,
ptp.rep_registration_number,
ptp.rounding_rule_code
FROM zx_party_tax_profile ptp
WHERE ptp.party_id = p_party_id
AND ptp.party_type_code = p_party_type_code;
SELECT
ptp.party_tax_profile_id,
ptp.party_id,
ptp.party_type_code,
ptp.supplier_flag,
ptp.customer_flag,
ptp.site_flag,
ptp.process_for_applicability_flag,
ptp.rounding_level_code,
ptp.withholding_start_date,
ptp.allow_awt_flag,
ptp.use_le_as_subscriber_flag,
ptp.legal_establishment_flag,
ptp.first_party_le_flag,
ptp.reporting_authority_flag,
ptp.collecting_authority_flag,
ptp.provider_type_code,
ptp.create_awt_dists_type_code,
ptp.create_awt_invoices_type_code,
ptp.allow_offset_tax_flag,
ptp.effective_from_use_le,
ptp.rep_registration_number,
ptp.rounding_rule_code
FROM zx_party_tax_profile ptp
WHERE party_tax_profile_id = p_party_tax_profile_id;