[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tax_provider_id,
tax_regime_code
INTO x_migrated_tax_provider_id,
x_migrated_tax_regime_code
FROM ZX_LINES
WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index)
AND rownum = 1;
SELECT tax_provider_id,
tax_regime_code
INTO x_migrated_tax_provider_id,
x_migrated_tax_regime_code
FROM ZX_LINES
WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_trx_line_index)
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(p_trx_line_index)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_trx_line_index)
AND rownum = 1;
SELECT tax_regime_id
, effective_from
, effective_to
, country_code
INTO x_migrated_tax_regime_id
, x_migrated_effective_from -- Bug 5557565
, x_migrated_effective_to -- Bug 5557565
, x_migrated_country_code
FROM ZX_REGIMES_B
WHERE tax_regime_code = x_migrated_tax_regime_code;
SELECT *
INTO l_lines_det_fact_rec
FROM zx_lines_det_factors
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(p_ptnr_tax_line_ind)
AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(p_ptnr_tax_line_ind);
SELECT historical_flag INTO l_historical_flag
FROM ZX_LINES_DET_FACTORS
WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind);
SELECT tax_line_id
INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind)
FROM zx_lines
WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind)
AND tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind)
AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind);
SELECT tax_line_id
INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(p_ptnr_tax_line_ind)
FROM zx_lines
WHERE application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(p_ptnr_tax_line_ind)
AND entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(p_ptnr_tax_line_ind)
AND event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(p_ptnr_tax_line_ind)
AND trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(p_ptnr_tax_line_ind)
AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(p_ptnr_tax_line_ind)
AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(p_ptnr_tax_line_ind)
AND tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind);
SELECT tax_regime_id
INTO l_tax_regime_id
FROM zx_regimes_b
WHERE tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind);
SELECT tax_jurisdiction_id
INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction_id(p_ptnr_tax_line_ind)
FROM ZX_JURISDICTIONS_B
WHERE tax_jurisdiction_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction(p_ptnr_tax_line_ind);
SELECT TAX_ACCOUNT_SOURCE_TAX
INTO l_tax_account_source_tax
FROM ZX_SCO_TAXES_B_V
WHERE tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(p_ptnr_tax_line_ind) AND
tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(p_ptnr_tax_line_ind) AND
( ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_date(p_ptnr_tax_line_ind) >= effective_from AND
( ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_date(p_ptnr_tax_line_ind) <= effective_to OR effective_to IS NULL));
SELECT exempt_certificate_number
FROM ZX_EXEMPTIONS
WHERE content_owner_id = p_event_class_rec.first_pty_org_id
AND product_id = ??
AND inventory_org_id = ??
AND exemption_status_code = ??
AND exempt_certificate_number = ZX_PTNR_SRVC_INTGRTN_PKG.g_exemption_tbl(exemption_index).EXEMPT_CERTIFICATE_NUMBER
AND exempt_reason_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_exemption_tbl(exemption_index).EXEMPT_REASON_CODE
AND duplicate_exemption = ??
AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_exemption_tbl(exemption_index).TAX
AND tax_status_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl(p_tax_line_index).TAX_STATUS_CODE
AND tax_jurisdiction_id = ??
AND tax_rate_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl(p_tax_line_index).TAX_STATUS_CODE
AND effective_from = G_EFFECTIVE_DATE
AND tax_regime_code = ??
AND party_tax_profile_id = ??
ZX_TCM_GET_EXEMPT_PKG.get_tax_exemptions(
p_bill_to_cust_site_use_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_cust_acct_site_use_id(p_structure_index),
p_bill_to_cust_acct_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_third_pty_acct_id(p_structure_index),
p_bill_to_party_site_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_site_tax_prof_id(p_structure_index),
p_bill_to_party_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.bill_to_party_tax_prof_id(p_structure_index),
p_sold_to_party_site_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trading_hq_site_tax_prof_id(p_structure_index),
p_sold_to_party_ptp_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.trading_hq_party_tax_prof_id(p_structure_index),
p_inventory_org_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_org_id(p_structure_index),
p_inventory_item_id => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.product_id(p_structure_index),
p_exempt_certificate_number => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_certificate_number(p_structure_index),
p_reason_code => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exempt_reason_code(p_structure_index),
p_exempt_control_flag => ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.exemption_control_flag(p_structure_index),
p_tax_date => l_tax_date,
p_tax_regime_code => l_tax_regime_code,
p_tax => l_tax,
p_tax_status_code => l_tax_status_code,
p_tax_rate_code => l_tax_rate_code,
p_tax_jurisdiction_id => l_tax_jurisdiction_id,
p_multiple_jurisdictions_flag => l_multiple_jurisdictions_flag,
p_event_class_rec => p_event_class_rec,
x_return_status => p_return_status,
x_exemption_rec => l_exemption_rec);
IF p_event = 'INSERT' THEN
FOR i IN nvl(ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id.FIRST,0) .. nvl(ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id.LAST,-1)
LOOP
IF ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id(i) = p_event_class_rec.application_id
AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.entity_code(i) = p_event_class_rec.entity_code
AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.event_class_code(i) = p_event_class_rec.event_class_code
AND ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_id(i) = p_event_class_rec.trx_id THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,
' Application Id = ' || p_event_class_rec.application_id ||
' Entity code = ' || p_event_class_rec.entity_code ||
' Event Class code = ' || p_event_class_rec.event_class_code ||
' Trx Id = ' || p_event_class_rec.trx_id ||
' Trx Line Id = ' || ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_line_id(i) ||
' Tax Regime code = ' || ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_code(i));
SELECT 1
INTO l_dummy
FROM zx_trx_line_app_regimes
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_line_id(i)
AND trx_level_type =ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_level_type(i)
AND tax_regime_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_code(i);
INSERT INTO ZX_TRX_LINE_APP_REGIMES(APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID,
TRX_LINE_ID,
TRX_LEVEL_TYPE,
TAX_REGIME_ID,
TAX_REGIME_CODE,
TAX_PROVIDER_ID,
ALLOW_TAX_CALCULATION_FLAG,
PSEUDO_TAX_ONLY_LINE_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.application_id(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.entity_code(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.event_class_code(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_id(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_line_id(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.trx_level_type(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_id(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_regime_code(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.tax_provider_id(i),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_app_regime_tbl.allow_tax_calculation_flag(i),
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
) ;
ELSIF p_event = 'DELETE' THEN
DELETE from ZX_TRX_LINE_APP_REGIMES
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND tax_provider_id = p_provider_id
AND tax_regime_code = p_tax_regime_code;
UPDATE zx_trx_line_app_regimes
SET update_flag ='Y'
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id = p_trx_line_id
AND trx_level_type = p_trx_level_type;
UPDATE zx_trx_line_app_regimes
SET update_flag = null
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND update_flag = 'Y';
SELECT ztlar.tax_regime_id,
ztlar.tax_regime_code,
ztlar.tax_provider_id,
regimes.effective_from,
regimes.effective_to,
regimes.country_code
FROM ZX_TRX_LINE_APP_REGIMES ztlar
, zx_regimes_b regimes
WHERE ztlar.application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(l_index)
AND ztlar.entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(l_index)
AND ztlar.event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(l_index)
AND ztlar.trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(l_index)
AND ztlar.trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(l_index)
AND ztlar.trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(l_index)
AND ztlar.tax_regime_id = regimes.tax_regime_id;
SELECT ztlar.tax_regime_id,
ztlar.tax_regime_code,
ztlar.tax_provider_id,
regimes.effective_from,
regimes.effective_to,
regimes.country_code
FROM ZX_TRX_LINE_APP_REGIMES ztlar
, zx_regimes_b regimes
WHERE ztlar.application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(l_index)
AND ztlar.entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(l_index)
AND ztlar.event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(l_index)
AND ztlar.trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(l_index)
AND ztlar.trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(l_index)
AND ztlar.trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(l_index)
AND ztlar.tax_regime_id = regimes.tax_regime_id;
SELECT nvl(partner_migrated_flag, 'N') -- Bug 5007293
INTO l_ptnr_migrated_flag
FROM ZX_LINES_DET_FACTORS
WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_application_id(p_trx_line_index)
AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_entity_code(p_trx_line_index)
AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_event_class_code(p_trx_line_index)
AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_id(p_trx_line_index)
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_line_id(p_trx_line_index)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.applied_from_trx_level_type(p_trx_line_index);
SELECT nvl(partner_migrated_flag, 'N') -- Bug 5007293
INTO l_ptnr_migrated_flag
FROM ZX_LINES_DET_FACTORS
WHERE application_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_application_id(p_trx_line_index)
AND entity_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_entity_code(p_trx_line_index)
AND event_class_code = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_event_class_code(p_trx_line_index)
AND trx_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_id(p_trx_line_index)
AND trx_line_id = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_line_id(p_trx_line_index)
AND trx_level_type = ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.adjusted_doc_trx_level_type(p_trx_line_index);
SELECT srvc.srvc_provider_id
INTO x_provider_id
FROM ZX_SRVC_SUBSCRIPTIONS srvc,
ZX_REGIMES_USAGES reg
WHERE reg.tax_regime_code = p_tax_regime_code
AND srvc.regime_usage_id = reg.regime_usage_id
AND srvc.enabled_flag = 'Y'
AND srvc.prod_family_grp_code = ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW
AND ZX_SECURITY.G_EFFECTIVE_DATE between
(srvc.effective_from) AND nvl( srvc.effective_to,ZX_SECURITY.G_EFFECTIVE_DATE)
AND reg.first_pty_org_id = ZX_SECURITY.G_FIRST_PARTY_ORG_ID
AND NOT EXISTS (SELECT 1
FROM ZX_SRVC_SBSCRPTN_EXCLS excl
WHERE excl.application_id = p_application_id
AND excl.entity_code = p_entity_code
AND excl.event_class_code = p_event_class_code
AND excl.srvc_subscription_id = srvc.srvc_subscription_id
);
SELECT party_name
INTO l_provider_name
FROM HZ_PARTIES pty,
ZX_PARTY_TAX_PROFILE ptp
WHERE pty.party_id = ptp.party_id
AND ptp.party_tax_profile_id = x_provider_id;
SELECT srvc.srvc_provider_id
INTO x_provider_id
FROM ZX_SRVC_SUBSCRIPTIONS srvc,
ZX_REGIMES_USAGES reg
WHERE reg.tax_regime_code = p_tax_regime_code
AND srvc.regime_usage_id = reg.regime_usage_id
AND srvc.enabled_flag = 'Y'
AND (ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW is null OR
srvc.prod_family_grp_code = ZX_TAX_PARTNER_PKG.G_BUSINESS_FLOW)
AND ZX_SECURITY.G_EFFECTIVE_DATE between
(srvc.effective_from) AND nvl( srvc.effective_to,ZX_SECURITY.G_EFFECTIVE_DATE)
AND reg.first_pty_org_id = ZX_SECURITY.G_FIRST_PARTY_ORG_ID;
SELECT party_name
INTO l_provider_name
FROM hz_parties pty,
zx_party_tax_profile ptp
WHERE pty.party_id = ptp.party_id
AND ptp.party_tax_profile_id = x_provider_id;
SELECT reg.service_type_id,
reg.context_ccid,
srvc.data_transfer_code
INTO l_service_type_id,
l_context_ccid,
l_data_transfer_mode
FROM ZX_API_REGISTRATIONS reg,
ZX_SERVICE_TYPES srvc,
ZX_API_CODE_COMBINATIONS api
WHERE api.code_combination_id = reg.context_ccid
AND api.segment_attribute1 = p_tax_regime_code
AND reg.api_owner_id = p_event_class_rec.first_pty_org_id
AND srvc.service_type_id = reg.service_type_id
AND srvc.service_type_code = p_service_type_code;
SELECT 1
INTO l_dummy
FROM ZX_PRVDR_HDR_EXTNS_GT
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id;
INSERT INTO ZX_PRVDR_HDR_EXTNS_GT(event_class_code,
application_id,
entity_code,
trx_id,
provider_id,
tax_regime_code,
creation_date,
created_by,
last_update_date,
last_updated_by
)
values (p_event_class_rec.EVENT_CLASS_CODE,
p_event_class_rec.APPLICATION_ID,
p_event_class_rec.ENTITY_CODE,
p_event_class_rec.TRX_ID,
p_provider_id,
p_tax_regime_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
);
INSERT INTO ZX_PRVDR_LINE_EXTNS_GT (event_class_code,
application_id,
entity_code,
trx_id,
trx_line_id,
trx_level_type,
provider_id,
tax_regime_code,
creation_date,
created_by,
last_update_date,
last_updated_by
)
SELECT lines.event_class_code,
lines.application_id,
lines.entity_code,
lines.trx_id,
lines.trx_line_id,
lines.trx_level_type,
p_provider_id,
p_tax_regime_code,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
FROM ZX_LINES_DET_FACTORS lines
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND NOT EXISTS (SELECT 1
FROM ZX_PRVDR_LINE_EXTNS_GT ext
WHERE ext.application_id = lines.application_id
AND ext.entity_code = lines.entity_code
AND ext.event_class_code = lines.event_class_code
AND ext.trx_id = lines.trx_id
AND ext.trx_line_id = lines.trx_line_id
AND ext.trx_level_type = lines.trx_level_type);
DELETE from ZX_TRX_PRE_PROC_OPTIONS_GT;
INSERT into ZX_TRX_PRE_PROC_OPTIONS_GT (INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
EVNT_CLS_MAPPING_ID,
TAX_EVENT_TYPE_CODE,
PROD_FAMILY_GRP_CODE,
TRX_ID,
TAX_REGIME_CODE,
PARTNER_PROCESSING_FLAG,
TAX_PROVIDER_ID,
EVENT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
SELECT p_event_class_rec.internal_organization_id,
p_event_class_rec.application_id,
p_event_class_rec.entity_code,
p_event_class_rec.event_class_code,
clsmap.event_class_mapping_id,
p_event_class_rec.tax_event_type_code,
clsmap.prod_family_grp_code,
p_event_class_rec.trx_id,
p_tax_regime_code,
p_ptnr_processing_flag,
p_tax_provider_id,
p_event_class_rec.event_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
FROM ZX_EVNT_CLS_MAPPINGS clsmap
WHERE clsmap.application_id = p_event_class_rec.application_id
AND clsmap.entity_code = p_event_class_rec.entity_code
AND clsmap.event_class_code = p_event_class_rec.event_class_code;
' SYNCHRONIZE_TAX_REPOSITORY: Exception while inserting into ZX_TRX_PRE_PROC_OPTIONS_GT');
SELECT line_level_action,
hq_estb_party_tax_prof_id
INTO l_line_level_action,
l_hq_estb_ptp_id
FROM zx_lines_det_factors
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND rownum = 1;
SELECT app.application_short_name
INTO l_application_short_name
FROM FND_APPLICATION app
WHERE app.application_id = p_event_class_rec.application_id;
SELECT pty.party_number,
xletb.name
INTO l_legal_entity_number,
l_establishment_name
FROM XLE_ETB_PROFILES xletb,
XLE_ENTITY_PROFILES xlent,
HZ_PARTIES pty
WHERE xletb.establishment_id = p_event_class_rec.establishment_id
AND xlent.legal_entity_id = xletb.legal_entity_id
AND pty.party_id = xlent.party_id
AND xletb.main_establishment_flag = 'Y';
SELECT pty.party_number
, pty.party_id
INTO l_establishment_number
, l_party_id
FROM HZ_PARTIES pty,
ZX_PARTY_TAX_PROFILE ptp
WHERE ptp.party_tax_profile_id = l_hq_estb_ptp_id
AND pty.party_id = ptp.party_id;
SELECT pty.party_number
INTO l_legal_entity_number
FROM XLE_ETB_PROFILES xletb,
HZ_PARTIES pty,
XLE_ENTITY_PROFILES xep
WHERE xletb.party_id = l_party_id
AND xletb.legal_entity_id = xep.legal_entity_id
AND pty.party_id = xep.party_id;
INSERT into ZX_TRX_PRE_PROC_OPTIONS_GT (INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
EVNT_CLS_MAPPING_ID,
TAX_EVENT_TYPE_CODE,
PROD_FAMILY_GRP_CODE,
TRX_ID,
TAX_REGIME_CODE,
PARTNER_PROCESSING_FLAG,
TAX_PROVIDER_ID,
EVENT_ID,
QUOTE_FLAG,
RECORD_FLAG,
RECORD_FOR_PARTNERS_FLAG,
APPLICATION_SHORT_NAME,
LEGAL_ENTITY_NUMBER,
ESTABLISHMENT_NUMBER, -- Bug 5139731
ALLOW_TAX_CALCULATION_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES (p_event_class_rec.internal_organization_id,
p_event_class_rec.application_id,
p_event_class_rec.entity_code,
p_event_class_rec.event_class_code,
p_event_class_rec.event_class_mapping_id,
p_event_class_rec.tax_event_type_code,
p_event_class_rec.prod_family_grp_code,
p_event_class_rec.trx_id,
p_tax_regime_code,
p_ptnr_processing_flag,
p_tax_provider_id,
p_event_class_rec.event_id,
nvl(p_event_class_rec.quote_flag,'N'),
p_event_class_rec.record_flag,
p_event_class_rec.record_for_partners_flag,
l_application_short_name,
l_legal_entity_number,
l_establishment_number, -- Bug 5139731
decode(l_line_level_action,
'ALLOCATE_TAX_ONLY_ADJUSTMENT', 'N',
'ALLOCATE_LINE_ONLY_ADJUSTMENT', 'N', -- Bug 5007293
'LINE_INFO_TAX_ONLY', 'N', -- Bug
p_event_class_rec.process_for_applicability_flag),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id);
' Exception while inserting into ZX_TRX_PRE_PROC_OPTIONS_GT');
SELECT count(*)
INTO l_cnt_of_options_gt
FROM ZX_TRX_PRE_PROC_OPTIONS_GT;
'The record is inserted in ZX_TRX_PRE_PROC_OPTIONS_GT = ' || l_cnt_of_options_gt);
SELECT DISTINCT tax, -- Bug#5395227
tax_currency_code,
exchange_rate_type
FROM ZX_SCO_TAXES_B_V -- Bug#5395227
WHERE tax_regime_code = p_tax_regime_code;
SELECT nvl(trx_line_currency_code,trx_currency_code) trx_currency_code
FROM ZX_LINES_DET_FACTORS
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id;
SELECT reg.service_type_id,
reg.context_ccid
INTO l_service_type_id,
l_context_ccid
FROM ZX_API_REGISTRATIONS reg,
ZX_SERVICE_TYPES srvc,
ZX_API_CODE_COMBINATIONS api
WHERE api.code_combination_id = reg.context_ccid
AND api.segment_attribute1 = p_tax_regime_code
AND (api.segment_attribute2 is null
OR api.segment_attribute2 = p_event_class_rec.prod_family_grp_code )
AND reg.api_owner_id = p_tax_provider_id
AND srvc.service_type_id = reg.service_type_id
AND srvc.service_type_code = p_service_type_code;
ZX_PTNR_SRVC_INTGRTN_PKG.G_TRX_REC.document_level_action := 'DELETE';
l_delete_flag VARCHAR2(1);
SELECT rate.tax_rate_code,
rate.rate_type_code,
rate.tax_rate_id
FROM ZX_SCO_RATES_B_V rate
WHERE rate.tax_regime_code = c_tax_regime_code
-- AND rate.default_rate_flag = 'Y'
AND rate.active_flag = 'Y'
AND rate.tax = c_tax
AND rate.tax_status_code = c_tax_status_code
AND rate.tax_jurisdiction_code is null
AND (rate.tax_class = c_tax_class or rate.tax_class IS NULL)
-- AND rate.default_flg_effective_from <= c_tax_determine_date
-- AND (rate.default_flg_effective_to >= c_tax_determine_date OR
-- rate.default_flg_effective_to IS NULL)
AND rate.effective_from <= c_tax_determine_date
AND (rate.effective_to >= c_tax_determine_date OR
rate.effective_to IS NULL)
ORDER BY rate.tax_class NULLS LAST, rate.subscription_level_code;
SELECT allow_tax_calculation_flag
INTO l_allow_tax_calculation_flag
FROM ZX_TRX_PRE_PROC_OPTIONS_GT
WHERE APPLICATION_ID = p_event_class_rec.APPLICATION_ID
AND ENTITY_CODE = p_event_class_rec.ENTITY_CODE
AND EVENT_CLASS_CODE = p_event_class_rec.EVENT_CLASS_CODE
AND TRX_ID = p_event_class_rec.TRX_ID;
p_event => 'INSERT',
p_provider_id => null,
p_tax_regime_code => null,
p_trx_line_id => null,
p_trx_level_type => null,
x_return_status => l_return_status
);
l_delete_flag := 'Y';
l_delete_flag := 'Y';
SELECT tax_precision,
minimum_accountable_unit,
rounding_rule_code,
tax_id,
tax_type_code,
exchange_rate_type
INTO l_tax_precision,
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.minimum_accountable_unit(ptnr_tax_line_index),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rounding_rule_code(ptnr_tax_line_index),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_id(ptnr_tax_line_index),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_type_code(ptnr_tax_line_index),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_type(ptnr_tax_line_index)
-- FROM ZX_SCO_TAXES -- Bug#5395227
FROM ZX_SCO_TAXES_B_V
WHERE tax_regime_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(ptnr_tax_line_index)
AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
AND rownum = 1
ORDER BY subscription_level_code; -- Bug#5395227
SELECT count(*)
INTO l_app_regimes
FROM ZX_TRX_LINE_APP_REGIMES
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(ptnr_tax_line_index)
AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(ptnr_tax_line_index);
IF p_event_class_rec.tax_event_type_code = 'UPDATE'
AND l_partner_migrated_flag is null
AND record_type_code_tbl(ptnr_tax_line_index) = 'MIGRATED'
AND partner_migrated_flag_tbl(ptnr_tax_line_index) = 'Y' THEN
l_partner_migrated_flag := 'N';
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.delete_flag(ptnr_tax_line_index) := l_delete_flag ;
SELECT status.tax_status_code,
status.tax_status_id
INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(ptnr_tax_line_index),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_id(ptnr_tax_line_index)
-- FROM ZX_SCO_STATUS status -- Bug#5395227
FROM ZX_SCO_STATUS_B_V status
WHERE status.tax_regime_code = p_tax_regime_code
AND status.default_status_flag = 'Y'
AND status.tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
AND (ZX_SECURITY.G_EFFECTIVE_DATE between status.default_flg_effective_from AND nvl(status.default_flg_effective_to,ZX_SECURITY.G_EFFECTIVE_DATE))
AND rownum = 1
ORDER BY subscription_level_code; -- Bug#5395227
SELECT rate.tax_rate_code,
rate.rate_type_code,
rate.tax_rate_id
INTO ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(ptnr_tax_line_index),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rate_type_code(ptnr_tax_line_index),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_id(ptnr_tax_line_index)
-- FROM ZX_SCO_RATES rate -- Bug#5395227
FROM ZX_SCO_RATES_B_V rate
WHERE rate.tax_regime_code = p_tax_regime_code
AND rate.default_rate_flag = 'Y'
AND rate.active_flag = 'Y'
AND rate.tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
AND rate.tax_status_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(ptnr_tax_line_index)
AND rate.tax_jurisdiction_code is null
AND (rate.tax_class = l_tax_class or rate.tax_class IS NULL)
AND (ZX_SECURITY.G_EFFECTIVE_DATE between rate.default_flg_effective_from AND nvl(rate.default_flg_effective_to,ZX_SECURITY.G_EFFECTIVE_DATE))
AND rownum = 1
ORDER BY rate.tax_class NULLS LAST, rate.subscription_level_code; -- Bug#5395227
SELECT tax.tax_rate,
tax.tax_amt,
tax.line_amt
INTO l_prepay_tax_rate,
l_prepay_tax_amt,
l_prepay_line_amt
FROM ZX_LINES tax
WHERE tax.application_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_application_id(ptnr_tax_line_index)
AND tax.entity_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_entity_code(ptnr_tax_line_index)
AND tax.event_class_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_event_class_code(ptnr_tax_line_index)
AND tax.trx_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_id(ptnr_tax_line_index)
AND tax.trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_line_id(ptnr_tax_line_index)
AND tax.trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_level_type(ptnr_tax_line_index)
AND tax.tax_regime_code = p_tax_regime_code
AND tax.tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(ptnr_tax_line_index)
AND tax.tax_apportionment_line_number = 1;
INSERT INTO ZX_DETAIL_TAX_LINES_GT (tax_line_id,
internal_organization_id,
application_id,
entity_code,
event_class_code,
event_type_code,
content_owner_id,
trx_id,
trx_line_id,
trx_level_type,
tax_regime_code,
tax_line_number,
tax,
tax_status_code,
tax_rate_code,
tax_rate_type,
tax_apportionment_line_number,
place_of_supply_type_code,
tax_jurisdiction_code,
tax_currency_code,
precision,
minimum_accountable_unit,
rounding_rule_code,
tax_amt,
unrounded_tax_amt,
unrounded_taxable_amt,
tax_amt_tax_curr,
tax_rate,
taxable_amt,
--exempt_amt,
exempt_certificate_number,
exempt_rate_modifier,
exempt_reason,
exempt_reason_code,
tax_exemption_id,
applied_from_application_id, -- Bug 5468010
applied_from_entity_code, -- Bug 5468010
applied_from_event_class_code, -- Bug 5468010
applied_from_trx_id, -- Bug 5468010
applied_from_line_id, -- Bug 5468010
applied_from_trx_level_type, -- Bug 5468010
applied_from_trx_number, -- Bug 5468010
adjusted_doc_application_id, -- Bug 5468010
adjusted_doc_entity_code, -- Bug 5468010
adjusted_doc_event_class_code, -- Bug 5468010
adjusted_doc_trx_id, -- Bug 5468010
adjusted_doc_line_id, -- Bug 5468010
adjusted_doc_trx_level_type, -- Bug 5468010
adjusted_doc_number, -- Bug 5468010
adjusted_doc_date, -- Bug 5468010
adjusted_doc_tax_line_id, -- Bug 6130978
sync_with_prvdr_flag,
tax_only_line_flag,
tax_amt_included_flag,
self_assessed_flag,
overridden_flag,
last_manual_entry,
tax_provider_id,
manually_entered_flag,
tax_registration_number, -- Bug 5288518
registration_party_type, -- Bug 5288518
cancel_flag,
delete_flag,
trx_line_number,
trx_number,
doc_event_status,
tax_event_class_code,
tax_event_type_code,
tax_regime_id,
tax_id,
tax_status_id,
tax_rate_id,
mrc_tax_line_flag, -- Bug 5162537
ledger_id,
legal_entity_id,
tax_currency_conversion_date,
tax_currency_conversion_type,
tax_currency_conversion_rate,
trx_currency_code,
trx_date,
unit_price,
line_amt,
trx_line_quantity,
offset_flag,
process_for_recovery_flag,
tax_jurisdiction_id,
tax_date,
tax_determine_date,
trx_line_date,
tax_type_code,
compounding_tax_flag,
taxable_amt_tax_curr,
tax_apportionment_flag,
historical_flag,
purge_flag,
freeze_until_overridden_flag,
copied_from_other_doc_flag,
global_attribute2,
global_attribute4,
global_attribute6,
global_attribute_category,
record_type_code,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES (--ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_line_id(i),
ZX_LINES_S.nextval,
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.internal_organization_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.application_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.entity_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.event_class_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.event_type_code(i),
p_event_class_rec.first_pty_org_id,
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.transaction_line_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_level_type(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.country_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_line_number(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rate_type_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_line_number(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.situs(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_precision(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.minimum_accountable_unit(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.rounding_rule_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_amount(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.unrounded_tax_amount(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.taxable_amount(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_curr_tax_amount(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_percentage(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.taxable_amount(i),
--ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_amt(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_certificate_number(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_rate_modifier(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.exempt_reason_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_exemption_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_application_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_entity_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_event_class_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_line_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_level_type(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.applied_from_trx_number(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_application_id(i), -- Bug 5468010
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_entity_code(i), -- Bug 5468010
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_event_class_code(i), -- Bug 5468010
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_id(i), -- Bug 5468010
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_line_id(i), -- Bug 5468010
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_trx_level_type(i), -- Bug 5468010
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_number(i), -- Bug 5468010
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_date(i), -- Bug 5468010
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.adjusted_doc_tax_line_id(i), -- Bug 6130978
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.sync_with_prvdr_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_only_line_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.inclusive_tax_line_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.use_tax_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.user_override_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.last_manual_entry(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_provider_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.manually_entered_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.registration_party_type(i), -- Bug 5288518
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.party_tax_reg_number(i), -- Bug 5288518
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.cancel_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.delete_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_number(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_number(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.doc_event_status(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_event_class_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_event_type_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_regime_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_status_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_rate_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.mrc_tax_line_flag(i), -- Bug 5162537
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.ledger_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.legal_entity_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_date(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_type(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_currency_conversion_rate(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_currency_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_date(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.unit_price(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.line_amt(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_quantity(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.offset_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.process_for_recovery_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_jurisdiction_id(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_date(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_determine_date(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.trx_line_date(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_type_code(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.compounding_tax_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.taxable_amt_tax_curr(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.tax_apportionment_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.historical_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.purge_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.freeze_until_overridden_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.copied_from_other_doc_flag(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute2(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute4(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute6(i),
ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.global_attribute_category(i),
'ETAX_CREATED',
1,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
);
UPDATE zx_lines_det_factors
SET threshold_indicator_flag = nvl(l_threshold_indicator_flag, threshold_indicator_flag),
partner_migrated_flag = nvl(l_partner_migrated_flag, partner_migrated_flag),
line_amt_includes_tax_flag = ZX_PTNR_SRVC_INTGRTN_PKG.g_tax_lines_result_tbl.line_amt_includes_tax_flag(1)
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id;
tax_tbl.DELETE;
record_type_code_tbl.DELETE;
line_level_action_tbl.DELETE;
partner_migrated_flag_tbl.DELETE;
SELECT tax.last_manual_entry
, tax.tax_line_id
INTO l_last_manual_entry
, l_tax_line_id
FROM ZX_LINES tax
WHERE tax.application_id = p_event_class_rec.application_id
AND tax.entity_code = p_event_class_rec.entity_code
AND tax.event_class_code = p_event_class_rec.event_class_code
AND tax.trx_id = p_event_class_rec.trx_id
AND tax.trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).transaction_line_id
AND tax.trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).trx_level_type
AND tax.tax_regime_code = p_tax_regime_code
AND tax.tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax
AND tax.place_of_supply_type_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).situs;
UPDATE ZX_LINES
SET tax_rate = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax_rate_percentage,
taxable_amt = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).taxable_amount
WHERE tax_line_id = l_tax_line_id;
UPDATE ZX_LINES
SET taxable_amt = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).taxable_amount
WHERE tax_line_id = l_tax_line_id;
UPDATE ZX_LINES
SET tax_rate = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax_rate_percentage,
taxable_amt = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).taxable_amount
WHERE application_id = p_event_class_rec.application_id
AND entity_code = p_event_class_rec.entity_code
AND event_class_code = p_event_class_rec.event_class_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).transaction_line_id
AND trx_level_type = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).trx_level_type
AND tax_regime_code = p_tax_regime_code
AND tax = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).tax
AND place_of_supply_type_code = ZX_PTNR_SRVC_INTGRTN_PKG.g_sync_tax_lines_tbl(i).situs;
SELECT /*+ INDEX (TAX ZX_LINES_U1) */
sum(nvl(tax.tax_amt,0))
INTO l_tax_amount
FROM ZX_LINES tax
WHERE tax.application_id = p_application_id
AND tax.entity_code = p_entity_code
AND tax.event_class_code = p_event_class_code
AND tax.trx_id = p_trx_id
AND tax.trx_line_id = p_trx_line_id
AND tax.trx_level_type = p_trx_level_type
AND tax_amt_included_flag = 'Y'
AND tax_provider_id <> p_tax_provider_id;