[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_item_dist(
p_tax_line_rec IN zx_lines%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE insert_global_table(
p_rec_nrec_dist_tbl IN OUT NOCOPY rec_nrec_dist_tbl_type,
p_rec_nrec_dist_begin_index IN OUT NOCOPY NUMBER,
p_rec_nrec_dist_end_index IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE delete_unnecessary_tax_dists(
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2);
| were determined and then tax lines and/or item distributions are updated |
| |
| This procedure will be called directly by TSRM service. |
| |
* =============================================================================*/
PROCEDURE determine_recovery(
p_event_class_rec IN ZX_API_PUB.event_class_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR get_item_dist_csr(
c_trx_line_id zx_lines.trx_line_id%TYPE,
c_trx_level_type zx_lines.trx_level_type%TYPE) IS
SELECT /*+ INDEX(ZX_ITM_DISTRIBUTIONS_GT ZX_ITM_DISTRIBUTIONS_GT_U1 ZX_ITM_DISTRIBUTIONS_GT_U1) */
trx_line_dist_id,
trx_line_id,
trx_level_type,
dist_level_action,
item_dist_number,
dist_intended_use,
task_id,
award_id,
project_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
trx_line_dist_amt,
trx_line_dist_qty,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_trx_level_type,
ref_doc_dist_id,
ref_doc_curr_conv_rate,
trx_line_dist_tax_amt,
account_ccid,
account_string,
price_diff,
ref_doc_trx_line_dist_qty,
ref_doc_curr_conv_rate,
applied_to_doc_curr_conv_rate,
-- applied_from_application_id, commented out for bug 5580045
-- applied_from_event_class_code,
-- applied_from_entity_code,
-- applied_from_trx_id,
-- applied_from_line_id,
applied_from_dist_id, -- add for CR3066321
-- adjusted_doc_application_id,
-- adjusted_doc_event_class_code,
-- adjusted_doc_entity_code,
-- adjusted_doc_trx_id,
-- adjusted_doc_line_id,
adjusted_doc_dist_id,
overriding_recovery_rate,
-- applied_from_trx_level_type,
-- adjusted_doc_trx_level_type,
trx_line_dist_date -- AP passes account_date
FROM zx_itm_distributions_gt
WHERE trx_id = p_event_class_rec.trx_id
AND 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_line_id = c_trx_line_id
AND trx_level_type = c_trx_level_type;
SELECT trx_line_id,
trx_level_type,
line_intended_use,
line_amt,
trx_line_quantity,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_trx_level_type,
account_ccid,
account_string,
ref_doc_line_quantity,
-- applied_from_application_id, -- commented out for bug 5580045
-- applied_from_event_class_code,
-- applied_from_entity_code,
-- applied_from_trx_id,
-- applied_from_line_id,
-- adjusted_doc_application_id,
-- adjusted_doc_event_class_code,
-- adjusted_doc_entity_code,
-- adjusted_doc_trx_id,
-- adjusted_doc_line_id,
-- applied_from_trx_level_type,
-- adjusted_doc_trx_level_type,
nvl(trx_line_gl_date, trx_date) -- item dist gl date
FROM zx_lines_det_factors
WHERE application_id = p_event_class_rec.application_id
AND event_class_code = p_event_class_rec.event_class_code
AND entity_code = p_event_class_rec.entity_code
AND trx_id = p_event_class_rec.trx_id
AND trx_line_id = c_trx_line_id
AND trx_level_type = c_trx_level_type;
SELECT ZX_REC_NREC_DIST_S.nextval
INTO ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id
FROM dual;
ZX_TRL_MANAGE_TAX_PKG.Update_Item_Dist_Changed_Flag(
x_return_status,
p_event_class_rec);
l_rec_nrec_dist_tbl.delete;
-- updated or new
--
FOR j IN NVL(trx_line_dist_id_tbl.FIRST, 0) .. NVL(trx_line_dist_id_tbl.LAST, -1)
LOOP
ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.TRX_LINE_ID(1) := trx_line_id_tbl(j);
IF l_action = 'CREATE' or l_action = 'UPDATE' THEN
-- get new tax distributions for this tax line and item dist
ZX_TRD_INTERNAL_SERVICES_PVT.calc_tax_dist(
l_tax_line_tbl,
i,
1,
l_rec_nrec_dist_tbl,
l_rec_nrec_dist_begin_index,
l_rec_nrec_dist_end_index,
p_event_class_rec,
x_return_status,
p_error_buffer);
ELSE -- this is a new or updated tax line
FOR j IN NVL(trx_line_dist_id_tbl.FIRST, 0) .. NVL(trx_line_dist_id_tbl.LAST,-1)
LOOP
ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.TRX_LINE_ID(1) := trx_line_id_tbl(j);
IF l_action = 'CREATE' OR l_action = 'UPDATE' OR l_action = 'NO_ACTION'
THEN
-- get new tax distributions for this tax line and item dist
--
ZX_TRD_INTERNAL_SERVICES_PVT.calc_tax_dist(
l_tax_line_tbl,
i,
1,
l_rec_nrec_dist_tbl,
l_rec_nrec_dist_begin_index,
l_rec_nrec_dist_end_index,
p_event_class_rec,
x_return_status,
p_error_buffer);
insert_global_table(
l_rec_nrec_dist_tbl,
l_dist_tbl_begin_index,
l_rec_nrec_dist_end_index,
x_return_status);
'After calling insert_global_table x_return_status = '
|| x_return_status);
INSERT INTO zx_rec_nrec_dist_gt VALUES l_rec_nrec_dist_tbl(ctr);
delete_unnecessary_tax_dists(p_event_class_rec => p_event_class_rec,
x_return_status => x_return_status);
'After calling delete_unnecessary_tax_dists, x_return_status = '
|| x_return_status);
SELECT tax_line_id,
tax_amt,
tax_amt_tax_curr,
tax_amt_funcl_curr
FROM zx_lines
WHERE trx_id =
p_event_class_rec.trx_id
AND 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 Reporting_Only_Flag = 'N' -- do not process reporting only lines
AND Process_For_Recovery_Flag = 'Y'
AND mrc_tax_line_flag = 'N';
SELECT *
FROM zx_rec_nrec_dist
WHERE trx_id = p_event_class_rec.trx_id
AND 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 tax_line_id = l_tax_line_id
AND NVL(freeze_flag, 'N') = 'N'
AND NVL(reverse_flag, 'N') = 'N'
AND mrc_tax_dist_flag = 'N';
l_rec_nrec_dist_tbl.delete;
-- insert into global temporary table
insert_global_table(
l_rec_nrec_dist_tbl,
l_rec_nrec_dist_begin_index,
l_rec_nrec_dist_end_index,
x_return_status);
'After calling insert_global_table x_return_status = '
|| x_return_status);
INSERT INTO zx_rec_nrec_dist_gt VALUES l_rec_nrec_dist_tbl(ctr);
| p_rec_nrec_dist_tbl. UI needs to delete all the tax distributions uses want|
| to freeze and insert all the tax distributions from p_rec_nrec_dist_tbl to |
| tax reporsitory. |
| |
| This procedure will be called from the tax distribution UI. |
| |
* =============================================================================*/
PROCEDURE REVERSE_TAX_DIST(
p_rec_nrec_dist_tbl OUT NOCOPY REC_NREC_DIST_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2)
is
l_index number;
SELECT *
FROM zx_rec_nrec_dist
WHERE rec_nrec_tax_dist_id IN (SELECT tax_dist_id FROM zx_tax_dist_id_gt);
SELECT ZX_REC_NREC_DIST_S.nextval
INTO ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id
FROM dual;
p_rec_nrec_dist_tbl(l_index).last_updated_by := fnd_global.user_id;
p_rec_nrec_dist_tbl(l_index).last_update_login := fnd_global.login_id;
p_rec_nrec_dist_tbl(l_index).last_update_date := sysdate;
SELECT ZX_REC_NREC_DIST_S.nextval
INTO ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id
FROM dual;
p_rec_nrec_dist_tbl(l_index).last_updated_by := fnd_global.user_id;
p_rec_nrec_dist_tbl(l_index).last_update_login := fnd_global.login_id;
p_rec_nrec_dist_tbl(l_index).last_update_date := sysdate;
select
distinct tax_hold_code - tax_hold_released_code
from ZX_LINES
where tax_hold_code > 0
and trx_id = p_transaction_rec.trx_id
and application_id = p_transaction_rec.application_id
and entity_code = p_transaction_rec.entity_code
and event_class_code = p_transaction_rec.event_class_code
and mrc_tax_line_flag = 'N'
and nvl(cancel_flag,'N') <> 'Y' ;
x_hold_status.DELETE;
INSERT ALL
WHEN (REGIME_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
message_name,
message_text
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
'ZX_REGIME_NOT_EFFECTIVE',
l_regime_not_effective
)
WHEN (TAX_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
message_name,
message_text
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
'ZX_TAX_NOT_EFFECTIVE',
l_tax_not_effective
)
WHEN (TAX_STATUS_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
message_name,
message_text
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
'ZX_TAX_STATUS_NOT_EFFECTIVE',
l_tax_status_not_effective
)
WHEN (TAX_RATE_ID_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
message_name,
message_text
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
'ZX_TAX_RATE_NOT_EFFECTIVE',
l_tax_rate_not_effective
)
WHEN (TAX_RATE_ID_NOT_ACTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
message_name,
message_text
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
'ZX_TAX_RATE_NOT_ACTIVE',
l_tax_rate_not_active
)
WHEN (TAX_RATE_PERCENTAGE_INVALID = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
message_name,
message_text
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
'ZX_TAX_RATE_PERCENTAGE_INVALID',
l_tax_rate_percentage_invalid
)
WHEN (JUR_CODE_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
message_name,
message_text
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
trx_level_type,
'ZX_JUR_CODE_NOT_EFFECTIVE',
l_jur_code_not_effective
)
SELECT
line.application_id,
line.entity_code,
line.event_class_code,
line.trx_id,
line.trx_line_id,
line.trx_level_type,
-- Check for Regime Effectivity
CASE WHEN line.tax_determine_date
BETWEEN regime.effective_from
AND nvl(regime.effective_to, line.trx_date)
THEN 'N'
ELSE 'Y' END REGIME_NOT_EFFECTIVE,
-- Check for Tax Effectivity
CASE WHEN line.tax_determine_date
BETWEEN tax.effective_from
AND nvl(tax.effective_to, line.trx_date)
THEN 'N'
ELSE 'Y' END TAX_NOT_EFFECTIVE,
-- Check for Status Effectivity
CASE WHEN line.tax_determine_date
BETWEEN status.effective_from
AND nvl(status.effective_to, line.trx_date)
THEN 'N'
ELSE 'Y' END TAX_STATUS_NOT_EFFECTIVE,
-- Check for Rate Id Date Effectivity
CASE WHEN line.tax_determine_date
BETWEEN rate.effective_from
AND nvl(rate.effective_to, line.trx_date)
THEN 'N'
ELSE 'Y' END TAX_RATE_ID_NOT_EFFECTIVE,
-- Check Rate Id is Active
CASE WHEN rate.active_flag = 'Y'
THEN 'N'
ELSE 'Y' END TAX_RATE_ID_NOT_ACTIVE,
-- Check for Rate Percentage
CASE WHEN (rate.tax_rate_id = line.tax_rate_id
AND (line.tax_exemption_id IS NULL AND exempt_rate_modifier IS NULL)
AND line.tax_provider_id IS NULL
AND (line.tax_exception_id is NULL AND exception_rate IS NULL)
AND rate.percentage_rate <> line.tax_rate
AND rate.allow_adhoc_tax_rate_flag <> 'Y'
AND line.tax_determine_date
BETWEEN rate.effective_from
AND nvl(rate.effective_to, line.trx_date))
THEN 'Y'
ELSE 'N' END TAX_RATE_PERCENTAGE_INVALID,
-- Check for Jurisdiction Code Effectivity
CASE WHEN line.tax_determine_date
BETWEEN jur.effective_from
AND nvl(jur.effective_to, line.trx_date)
THEN 'N'
ELSE 'Y' END JUR_CODE_NOT_EFFECTIVE
FROM
ZX_LINES line ,
ZX_REGIMES_B regime ,
ZX_TAXES_B tax ,
ZX_STATUS_B status ,
ZX_RATES_B rate ,
ZX_JURISDICTIONS_B jur
WHERE line.APPLICATION_ID = p_transaction_rec.APPLICATION_ID
AND line.ENTITY_CODE = p_transaction_rec.ENTITY_CODE
AND line.EVENT_CLASS_CODE = p_transaction_rec.EVENT_CLASS_CODE
AND line.TRX_ID = p_transaction_rec.TRX_ID
and regime.tax_regime_code = line.tax_regime_code
and tax.tax_id = line.tax_id
and status.tax_status_id = line.tax_status_id
and rate.tax_rate_id = line.tax_rate_id
and jur.tax_jurisdiction_id = line.tax_jurisdiction_id
and line.mrc_tax_line_flag = 'N';
SELECT zd.rec_nrec_tax_dist_id,
gt.reversing_appln_id,
gt.reversing_entity_code,
gt.reversing_evnt_cls_code,
gt.reversing_trx_id,
gt.reversing_trx_line_id,
gt.reversing_trx_level_type,
zl.tax_line_id,
gt.reversing_trx_line_dist_id,
zl.summary_tax_line_id,
zl.trx_number,
zd.internal_organization_id,
zd.gl_date
BULK COLLECT INTO
l_rvrsed_tax_dist_id_tbl,
l_rvrsng_appln_id_tbl,
l_rvrsng_entity_code_tbl,
l_rvrsng_evnt_cls_code_tbl,
l_rvrsng_trx_id_tbl,
l_rvrsng_trx_line_id_tbl,
l_rvrsng_trx_level_type_tbl,
l_rvrsng_tax_line_id_tbl,
l_rvrsng_trx_line_dist_id_tbl,
l_summary_tax_line_id_tbl,
l_rvrsng_trx_number_tbl,
l_org_id_tbl,
l_gl_date_tbl
FROM zx_rec_nrec_dist zd, zx_reverse_dist_gt gt, zx_lines zl
WHERE zd.application_id = gt.reversed_appln_id
AND zd.entity_code = gt.reversed_entity_code
AND zd.event_class_code = gt.reversed_evnt_cls_code
AND zd.trx_id = gt.reversed_trx_id
AND zd.trx_line_id = gt.reversed_trx_line_id
AND zd.trx_level_type = gt.reversed_trx_level_type
AND zd.tax_line_id = NVL(gt.reversed_tax_line_id, zd.tax_line_id)
AND zd.trx_line_dist_id = gt.reversed_trx_line_dist_id
AND nvl(zd.Reverse_Flag, 'N') = 'N'
AND zl.application_id = gt.reversing_appln_id
AND zl.entity_code = gt.reversing_entity_code
AND zl.event_class_code = gt.reversing_evnt_cls_code
AND zl.trx_id = gt.reversing_trx_id
AND zl.trx_line_id = gt.reversing_trx_line_id
AND zl.trx_level_type = gt.reversing_trx_level_type
AND ((zl.reversed_tax_line_id IS NOT NULL AND
zl.reversed_tax_line_id = zd.tax_line_id
) OR
(zl.reversed_tax_line_id IS NULL AND
zl.tax_line_id = gt.reversing_tax_line_id
)
);
INSERT INTO ZX_REC_NREC_DIST(
REC_NREC_TAX_DIST_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
TAX_EVENT_CLASS_CODE,
TAX_EVENT_TYPE_CODE,
TRX_ID,
TRX_LINE_ID,
TRX_LEVEL_TYPE,
TRX_LINE_NUMBER,
TAX_LINE_ID,
TAX_LINE_NUMBER,
TRX_LINE_DIST_ID,
ITEM_DIST_NUMBER,
CONTENT_OWNER_ID,
REC_NREC_TAX_DIST_NUMBER,
TAX_REGIME_ID,
TAX_REGIME_CODE,
TAX_ID,
TAX,
TAX_STATUS_ID,
TAX_STATUS_CODE,
TAX_RATE_ID,
TAX_RATE_CODE,
TAX_RATE,
INCLUSIVE_FLAG,
RECOVERY_TYPE_ID,
RECOVERY_TYPE_CODE,
RECOVERY_RATE_ID,
RECOVERY_RATE_CODE,
REC_NREC_RATE,
REC_TYPE_RULE_FLAG,
NEW_REC_RATE_CODE_FLAG,
RECOVERABLE_FLAG,
REVERSE_FLAG,
HISTORICAL_FLAG,
REVERSED_TAX_DIST_ID,
REC_NREC_TAX_AMT,
REC_NREC_TAX_AMT_TAX_CURR,
REC_NREC_TAX_AMT_FUNCL_CURR,
-- INVOICE_PRICE_VARIANCE,
-- EXCHANGE_RATE_VARIANCE,
-- BASE_INVOICE_PRICE_VARIANCE,
INTENDED_USE,
PROJECT_ID,
TASK_ID,
AWARD_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE,
REC_RATE_DET_RULE_FLAG,
LEDGER_ID,
SUMMARY_TAX_LINE_ID,
RECORD_TYPE_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
TAX_CURRENCY_CONVERSION_DATE,
TAX_CURRENCY_CONVERSION_TYPE,
TAX_CURRENCY_CONVERSION_RATE,
TRX_CURRENCY_CODE,
TAX_CURRENCY_CODE,
TRX_LINE_DIST_AMT,
TRX_LINE_DIST_TAX_AMT,
ORIG_REC_NREC_RATE,
ORIG_REC_RATE_CODE,
ORIG_REC_NREC_TAX_AMT,
ORIG_REC_NREC_TAX_AMT_TAX_CURR,
UNROUNDED_REC_NREC_TAX_AMT,
APPLICABILITY_RESULT_ID,
REC_RATE_RESULT_ID,
BACKWARD_COMPATIBILITY_FLAG,
OVERRIDDEN_FLAG,
SELF_ASSESSED_FLAG,
FREEZE_FLAG,
POSTING_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GL_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
REF_DOC_APPLICATION_ID,
REF_DOC_ENTITY_CODE,
REF_DOC_EVENT_CLASS_CODE,
REF_DOC_TRX_ID,
REF_DOC_LINE_ID,
REF_DOC_TRX_LEVEL_TYPE,
REF_DOC_DIST_ID,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION,
ROUNDING_RULE_CODE,
TAXABLE_AMT,
TAXABLE_AMT_TAX_CURR,
TAXABLE_AMT_FUNCL_CURR,
TAX_ONLY_LINE_FLAG,
UNROUNDED_TAXABLE_AMT,
LEGAL_ENTITY_ID,
ACCOUNT_CCID,
ACCOUNT_STRING,
PRD_TAX_AMT,
PRD_TAX_AMT_TAX_CURR,
PRD_TAX_AMT_FUNCL_CURR,
PRD_TOTAL_TAX_AMT,
PRD_TOTAL_TAX_AMT_TAX_CURR,
PRD_TOTAL_TAX_AMT_FUNCL_CURR,
APPLIED_FROM_TAX_DIST_ID,
ADJUSTED_DOC_TAX_DIST_ID,
FUNC_CURR_ROUNDING_ADJUSTMENT,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
LAST_MANUAL_ENTRY,
TAX_APPORTIONMENT_LINE_NUMBER,
REF_DOC_TAX_DIST_ID,
MRC_TAX_DIST_FLAG,
MRC_LINK_TO_TAX_DIST_ID,
TAX_APPORTIONMENT_FLAG,
RATE_TAX_FACTOR,
REF_DOC_PER_UNIT_NREC_TAX_AMT,
PER_UNIT_NREC_TAX_AMT,
TRX_LINE_DIST_QTY,
REF_DOC_TRX_LINE_DIST_QTY,
PRICE_DIFF,
QTY_DIFF,
PER_TRX_CURR_UNIT_NR_AMT,
REF_PER_TRX_CURR_UNIT_NR_AMT,
REF_DOC_CURR_CONV_RATE,
UNIT_PRICE,
REF_DOC_UNIT_PRICE,
APPLIED_TO_DOC_CURR_CONV_RATE,
TRX_NUMBER,
OBJECT_VERSION_NUMBER,
INTERNAL_ORGANIZATION_ID,
DEF_REC_SETTLEMENT_OPTION_CODE,
TAX_JURISDICTION_ID,
ACCOUNT_SOURCE_TAX_RATE_ID
)
SELECT
ZX_REC_NREC_DIST_S.NEXTVAL,
l_rvrsng_appln_id_tbl(i), -- GT.REVERSING_APPLN_ID,
l_rvrsng_entity_code_tbl(i), -- GT.REVERSING_ENTITY_CODE,
l_rvrsng_evnt_cls_code_tbl(i), -- GT.REVERSING_EVNT_CLS_CODE,
ZD.EVENT_TYPE_CODE,
ZD.TAX_EVENT_CLASS_CODE,
ZD.TAX_EVENT_TYPE_CODE,
l_rvrsng_trx_id_tbl(i), -- GT.REVERSING_TRX_ID,
l_rvrsng_trx_line_id_tbl(i), -- GT.REVERSING_TRX_LINE_ID,
l_rvrsng_trx_level_type_tbl(i), -- GT.REVERSING_TRX_LEVEL_TYPE,
ZD.TRX_LINE_NUMBER,
l_rvrsng_tax_line_id_tbl(i), -- GT.REVERSING_TAX_LINE_ID,
ZD.TAX_LINE_NUMBER,
l_rvrsng_trx_line_dist_id_tbl(i), -- GT.REVERSING_TRX_LINE_DIST_ID,
ZD.ITEM_DIST_NUMBER,
ZD.CONTENT_OWNER_ID,
ZD.REC_NREC_TAX_DIST_NUMBER,
ZD.TAX_REGIME_ID,
ZD.TAX_REGIME_CODE,
ZD.TAX_ID,
ZD.TAX,
ZD.TAX_STATUS_ID,
ZD.TAX_STATUS_CODE,
ZD.TAX_RATE_ID,
ZD.TAX_RATE_CODE,
ZD.TAX_RATE,
ZD.INCLUSIVE_FLAG,
ZD.RECOVERY_TYPE_ID,
ZD.RECOVERY_TYPE_CODE,
ZD.RECOVERY_RATE_ID,
ZD.RECOVERY_RATE_CODE,
ZD.REC_NREC_RATE,
ZD.REC_TYPE_RULE_FLAG,
ZD.NEW_REC_RATE_CODE_FLAG,
ZD.RECOVERABLE_FLAG,
'Y', -- ZD.REVERSE_FLAG,
ZD.HISTORICAL_FLAG,
ZD.REC_NREC_TAX_DIST_ID, -- REVERSED_TAX_DIST_ID,
-ZD.REC_NREC_TAX_AMT,
-ZD.REC_NREC_TAX_AMT_TAX_CURR,
-ZD.REC_NREC_TAX_AMT_FUNCL_CURR,
-- -ZD.INVOICE_PRICE_VARIANCE,
-- -ZD.EXCHANGE_RATE_VARIANCE,
-- -ZD.BASE_INVOICE_PRICE_VARIANCE,
ZD.INTENDED_USE,
ZD.PROJECT_ID,
ZD.TASK_ID,
ZD.AWARD_ID,
ZD.EXPENDITURE_TYPE,
ZD.EXPENDITURE_ORGANIZATION_ID,
ZD.EXPENDITURE_ITEM_DATE,
ZD.REC_RATE_DET_RULE_FLAG,
ZD.LEDGER_ID,
l_summary_tax_line_id_tbl(i), -- ZL.SUMMARY_TAX_LINE_ID,
ZD.RECORD_TYPE_CODE,
ZD.CURRENCY_CONVERSION_DATE,
ZD.CURRENCY_CONVERSION_TYPE,
ZD.CURRENCY_CONVERSION_RATE,
ZD.TAX_CURRENCY_CONVERSION_DATE,
ZD.TAX_CURRENCY_CONVERSION_TYPE,
ZD.TAX_CURRENCY_CONVERSION_RATE,
ZD.TRX_CURRENCY_CODE,
ZD.TAX_CURRENCY_CODE,
-ZD.TRX_LINE_DIST_AMT,
-ZD.TRX_LINE_DIST_TAX_AMT,
ZD.ORIG_REC_NREC_RATE,
ZD.ORIG_REC_RATE_CODE,
-ZD.ORIG_REC_NREC_TAX_AMT,
-ZD.ORIG_REC_NREC_TAX_AMT_TAX_CURR,
-ZD.UNROUNDED_REC_NREC_TAX_AMT,
ZD.APPLICABILITY_RESULT_ID,
ZD.REC_RATE_RESULT_ID,
ZD.BACKWARD_COMPATIBILITY_FLAG,
ZD.OVERRIDDEN_FLAG,
ZD.SELF_ASSESSED_FLAG,
'N', -- ZD.FREEZE_FLAG
ZD.POSTING_FLAG,
ZD.ATTRIBUTE_CATEGORY,
ZD.ATTRIBUTE1,
ZD.ATTRIBUTE2,
ZD.ATTRIBUTE3,
ZD.ATTRIBUTE4,
ZD.ATTRIBUTE5,
ZD.ATTRIBUTE6,
ZD.ATTRIBUTE7,
ZD.ATTRIBUTE8,
ZD.ATTRIBUTE9,
ZD.ATTRIBUTE10,
ZD.ATTRIBUTE11,
ZD.ATTRIBUTE12,
ZD.ATTRIBUTE13,
ZD.ATTRIBUTE14,
ZD.ATTRIBUTE15,
ZD.GLOBAL_ATTRIBUTE_CATEGORY,
ZD.GLOBAL_ATTRIBUTE1,
ZD.GLOBAL_ATTRIBUTE2,
ZD.GLOBAL_ATTRIBUTE3,
ZD.GLOBAL_ATTRIBUTE4,
ZD.GLOBAL_ATTRIBUTE5,
ZD.GLOBAL_ATTRIBUTE6,
ZD.GLOBAL_ATTRIBUTE7,
ZD.GLOBAL_ATTRIBUTE8,
ZD.GLOBAL_ATTRIBUTE9,
ZD.GLOBAL_ATTRIBUTE10,
ZD.GLOBAL_ATTRIBUTE11,
ZD.GLOBAL_ATTRIBUTE12,
ZD.GLOBAL_ATTRIBUTE13,
ZD.GLOBAL_ATTRIBUTE14,
ZD.GLOBAL_ATTRIBUTE15,
l_gl_date_tbl(i), -- ZD.GL_DATE,
FND_GLOBAL.USER_ID, -- CREATED_BY,
SYSDATE, -- CREATION_DATE,
FND_GLOBAL.USER_ID, -- LAST_UPDATED_BY,
FND_GLOBAL.LOGIN_ID, -- LAST_UPDATE_LOGIN,
SYSDATE, -- LAST_UPDATE_DATE,
ZD.REF_DOC_APPLICATION_ID,
ZD.REF_DOC_ENTITY_CODE,
ZD.REF_DOC_EVENT_CLASS_CODE,
ZD.REF_DOC_TRX_ID,
ZD.REF_DOC_LINE_ID,
ZD.REF_DOC_TRX_LEVEL_TYPE,
ZD.REF_DOC_DIST_ID,
ZD.MINIMUM_ACCOUNTABLE_UNIT,
ZD.PRECISION,
ZD.ROUNDING_RULE_CODE,
-ZD.TAXABLE_AMT,
-ZD.TAXABLE_AMT_TAX_CURR,
-ZD.TAXABLE_AMT_FUNCL_CURR,
ZD.TAX_ONLY_LINE_FLAG,
-ZD.UNROUNDED_TAXABLE_AMT,
ZD.LEGAL_ENTITY_ID,
ZD.ACCOUNT_CCID,
ZD.ACCOUNT_STRING,
-ZD.PRD_TAX_AMT,
-ZD.PRD_TAX_AMT_TAX_CURR,
-ZD.PRD_TAX_AMT_FUNCL_CURR,
-ZD.PRD_TOTAL_TAX_AMT,
-ZD.PRD_TOTAL_TAX_AMT_TAX_CURR,
-ZD.PRD_TOTAL_TAX_AMT_FUNCL_CURR,
ZD.APPLIED_FROM_TAX_DIST_ID,
ZD.ADJUSTED_DOC_TAX_DIST_ID,
ZD.FUNC_CURR_ROUNDING_ADJUSTMENT,
ZD.GLOBAL_ATTRIBUTE16,
ZD.GLOBAL_ATTRIBUTE17,
ZD.GLOBAL_ATTRIBUTE18,
ZD.GLOBAL_ATTRIBUTE19,
ZD.GLOBAL_ATTRIBUTE20,
ZD.LAST_MANUAL_ENTRY,
ZD.TAX_APPORTIONMENT_LINE_NUMBER,
ZD.REF_DOC_TAX_DIST_ID,
ZD.MRC_TAX_DIST_FLAG,
ZD.MRC_LINK_TO_TAX_DIST_ID,
ZD.TAX_APPORTIONMENT_FLAG,
ZD.RATE_TAX_FACTOR,
-ZD.REF_DOC_PER_UNIT_NREC_TAX_AMT,
-ZD.PER_UNIT_NREC_TAX_AMT,
-ZD.TRX_LINE_DIST_QTY,
-ZD.REF_DOC_TRX_LINE_DIST_QTY,
ZD.PRICE_DIFF,
-ZD.QTY_DIFF,
-ZD.PER_TRX_CURR_UNIT_NR_AMT,
-ZD.REF_PER_TRX_CURR_UNIT_NR_AMT,
ZD.REF_DOC_CURR_CONV_RATE,
ZD.UNIT_PRICE,
ZD.REF_DOC_UNIT_PRICE,
ZD.APPLIED_TO_DOC_CURR_CONV_RATE,
l_rvrsng_trx_number_tbl(i),
1,
ZD.INTERNAL_ORGANIZATION_ID,
ZD.DEF_REC_SETTLEMENT_OPTION_CODE,
ZD.TAX_JURISDICTION_ID,
ZD.ACCOUNT_SOURCE_TAX_RATE_ID
FROM zx_rec_nrec_dist zd
WHERE zd.rec_nrec_tax_dist_id = l_rvrsed_tax_dist_id_tbl(i);
UPDATE ZX_REC_NREC_DIST
SET REVERSE_FLAG = 'Y'
WHERE REC_NREC_TAX_DIST_ID = l_rvrsed_tax_dist_id_tbl(i);
select interim_tax_ccid, tax_account_ccid, non_rec_account_ccid
from zx_accounts
where TAX_ACCOUNT_ENTITY_ID = c_tax_account_entity_id
AND tax_account_entity_code = c_tax_account_entity_code
AND internal_organization_id = c_org_id;
select 'x'
from gl_code_combinations
where code_combination_id = l_ccid
and enabled_flag = 'Y'
and p_gl_date between nvl(start_date_active,p_gl_date) and nvl(end_date_active, p_gl_date);
select def_rec_settlement_option_code
from zx_rates_b
where tax_rate_id = c_tax_rate_id;
select tax_account_ccid, interim_tax_ccid, adj_ccid, edisc_ccid,
unedisc_ccid, finchrg_ccid, adj_non_rec_tax_ccid, edisc_non_rec_tax_ccid,
unedisc_non_rec_tax_ccid, finchrg_non_rec_tax_ccid
from ar_location_accounts_all
where location_segment_id = p_loc_segment_id
and org_id = p_org_id;
select tax_account_ccid, interim_tax_ccid, adj_ccid, edisc_ccid,
unedisc_ccid, finchrg_ccid, adj_non_rec_tax_ccid, edisc_non_rec_tax_ccid,
unedisc_non_rec_tax_ccid, finchrg_non_rec_tax_ccid
from zx_accounts
where TAX_ACCOUNT_ENTITY_ID = c_tax_account_entity_id
and tax_account_entity_code = c_tax_account_entity_code
and internal_organization_id = c_org_id
and ledger_id = c_ledger_id;
select 'x'
from gl_code_combinations
where code_combination_id = l_ccid
and enabled_flag = 'Y'
and p_gl_date between nvl(start_date_active,p_gl_date) and nvl(end_date_active, p_gl_date);
select account_source_tax_rate_id
from zx_lines
where tax_line_id = p_tax_line_id;
select location_segment_id
from ra_customer_trx_lines_all inv, zx_lines cm, zx_lines zxinv
where cm.tax_line_id = p_tax_line_id
and cm.adjusted_doc_trx_id = inv.customer_trx_id
and cm.adjusted_doc_tax_line_id = inv.tax_line_id
and inv.line_type = 'TAX'
and cm.adjusted_doc_tax_line_id = zxinv.tax_line_id
and cm.tax_provider_id is not null
and zxinv.record_type_code = 'MIGRATED';
| PRIVATE PROCEDURE insert_item_dist |
| |
| This procedure is insert dummy item distributions into the global |
| temporary table for the tax only tax line. |
| |
* ======================================================================*/
PROCEDURE insert_item_dist(
p_tax_line_rec IN zx_lines%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
IF (g_level_procedure >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_procedure,
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.INSERT_ITEM_DIST.BEGIN',
'ZX_TRD_SERVICES_PUB_PKG.INSERT_ITEM_DIST(+)');
INSERT INTO zx_itm_distributions_gt(
--internal_organization_id,
application_id,
entity_code,
event_class_code,
-- event_type_code,
trx_id ,
trx_line_id,
trx_level_type,
trx_line_dist_id,
dist_level_action,
trx_line_dist_date,
--set_of_books_id,
--trx_currency_code,
--currency_conversion_date,
--currency_conversion_rate,
--currency_conversion_type,
--minimum_accountable_unit,
--precision,
item_dist_number,
dist_intended_use,
tax_inclusion_flag,
tax_code,
task_id ,
award_id,
project_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
trx_line_dist_amt,
trx_line_dist_qty,
trx_line_quantity,
account_ccid,
account_string,
--trx_number, -- check later
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_trx_level_type,
ref_doc_dist_id,
ref_doc_curr_conv_rate,
--content_owner_id, -- check later
--tax_event_class_code, -- check later
--tax_event_type_code,
--doc_event_status,
trx_line_dist_tax_amt,
--quote_flag,
historical_flag)
SELECT
--p_tax_line_rec.internal_organization_id,
application_id,
entity_code,
event_class_code,
-- event_type_code,
trx_id,
trx_line_id,
trx_level_type,
-99, -- p_tax_line_rec.TRX_LINE_DIST_ID
'CREATE', -- p_tax_line_rec.DIST_LEVEL_ACTION
nvl(trx_line_gl_date, trx_date), -- trx_line_dist_date
--p_tax_line_rec.ledger_id, -- set_of_books_id
--p_tax_line_rec.trx_currency_code,
--p_tax_line_rec.currency_conversion_date,
--p_tax_line_rec.currency_conversion_rate,
--p_tax_line_rec.currency_conversion_type,
--p_tax_line_rec.minimum_accountable_unit,
--p_tax_line_rec.precision,
1, -- item dist number
line_intended_use, -- copy line intended use to dist
p_tax_line_rec.tax_amt_included_flag, -- tax_inclusion_flag
p_tax_line_rec.tax_code,
NULL, -- TASK_ID
NULL, -- AWARD_ID
NULL, -- PROJECT_ID
NULL, -- EXPENDITURE_TYPE
NULL, -- EXPENDITURE_ORGANIZATION_ID
NULL, -- EXPENDITURE_ITEM_DATE
line_amt,
trx_line_quantity,
trx_line_quantity,
account_ccid,
account_string,
--p_tax_line_rec.trx_number,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_trx_level_type,
NULL, -- REF_DOC_DIST_ID
NULL, -- REF_DOC_CURR_CONV_RATE
--p_tax_line_rec.content_owner_id,
--p_tax_line_rec.tax_event_class_code,
--p_tax_line_rec.tax_event_type_code,
--p_tax_line_rec.doc_event_status,
p_tax_line_rec.tax_amt,
-- 'N', -- Quote_Flag what should it be?
Historical_Flag
FROM zx_lines_det_factors
WHERE application_id = p_tax_line_rec.application_id
AND event_class_code = p_tax_line_rec.event_class_code
AND entity_code = p_tax_line_rec.entity_code
AND trx_id = p_tax_line_rec.trx_id
AND trx_line_id = p_tax_line_rec.trx_line_id
AND trx_level_type = p_tax_line_rec.trx_level_type;
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.INSERT_ITEM_DIST.END',
'ZX_TRD_SERVICES_PUB_PKG.INSERT_ITEM_DIST(-)');
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_item_dist',
'TRL Record Already Exists');
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_item_dist',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_item_dist',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_item_dist.END',
'ZX_TRD_SERVICES_PUB_PKG.INSERT_ITEM_DIST(-)');
END insert_item_dist;
SELECT * FROM zx_lines
WHERE trx_id = p_event_class_rec.trx_id
AND 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 Reporting_Only_Flag = 'N' -- do not process reporting only lines
AND (Process_For_Recovery_Flag = 'Y' OR Item_Dist_Changed_Flag = 'Y')
AND mrc_tax_line_flag = 'N'
--6900725
ORDER BY trx_line_id, trx_level_type, account_source_tax_rate_id nulls first ;
SELECT /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U1) */
*
FROM zx_detail_tax_lines_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
AND reporting_only_flag = 'N'
AND process_for_recovery_flag = 'Y'
AND mrc_tax_line_flag = 'N'
--6900725
ORDER BY trx_line_id, trx_level_type, account_source_tax_rate_id nulls first;
p_tax_line_tbl.delete;
SELECT * FROM zx_rec_nrec_dist
WHERE trx_id =
p_event_class_rec.trx_id
AND 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 tax_line_id = p_tax_line_id
AND trx_line_dist_id = p_trx_line_dist_id
AND nvl(Reverse_Flag,'N') = 'N';
SELECT internal_organization_id,
trx_line_id,
trx_level_type,
trx_date,
ledger_id,
trx_currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
minimum_accountable_unit,
precision,
trx_shipping_date,
trx_receipt_date,
legal_entity_id,
establishment_id,
trx_line_number,
trx_line_date,
trx_business_category,
line_intended_use,
user_defined_fisc_class,
line_amt,
trx_line_quantity,
unit_price,
exempt_certificate_number,
exempt_reason,
cash_discount,
volume_discount,
trading_discount,
transfer_charge,
transportation_charge,
insurance_charge,
other_charge,
product_id,
product_fisc_classification,
product_org_id,
uom_code,
product_type,
product_code,
product_category,
account_ccid,
account_string,
total_inc_tax_amt,
ship_to_location_id,
ship_from_location_id,
bill_to_location_id,
bill_from_location_id,
default_taxation_country,
-- Start : Added columns for Bug#7008557
first_pty_org_id,
rdng_ship_to_pty_tx_prof_id,
rdng_ship_from_pty_tx_prof_id,
rdng_bill_to_pty_tx_prof_id,
rdng_bill_from_pty_tx_prof_id,
rdng_ship_to_pty_tx_p_st_id,
rdng_ship_from_pty_tx_p_st_id,
rdng_bill_to_pty_tx_p_st_id,
rdng_bill_from_pty_tx_p_st_id,
ship_to_party_tax_prof_id,
ship_from_party_tax_prof_id,
bill_to_party_tax_prof_id,
bill_from_party_tax_prof_id,
ship_to_site_tax_prof_id,
ship_from_site_tax_prof_id,
bill_to_site_tax_prof_id,
bill_from_site_tax_prof_id,
ship_third_pty_acct_id,
bill_third_pty_acct_id,
document_sub_type,
-- End : Added columns for Bug#7008557
tax_reporting_flag
FROM zx_lines_det_factors
WHERE application_id = p_tax_line_tbl(p_index).application_id
AND entity_code = p_tax_line_tbl(p_index).entity_code
AND event_class_code = p_tax_line_tbl(p_index).event_class_code
AND trx_id = p_tax_line_tbl(p_index).trx_id
AND trx_line_id = p_tax_line_tbl(p_index).trx_line_id
AND trx_level_type = p_tax_line_tbl(p_index).trx_level_type;
| PRIVATE PROCEDURE insert_global_table |
| |
| DESCRIPTION |
| This procedure is used to insert rec/non-rec tax distributions from the |
| PL/SQL table to the global tempoarary table zx_rec_nrec_dist_gt when there |
| are more than 1000 records in the PL/SQL table |
|
* =============================================================================*/
PROCEDURE insert_global_table(
p_rec_nrec_dist_tbl IN OUT NOCOPY rec_nrec_dist_tbl_type,
p_rec_nrec_dist_begin_index IN OUT NOCOPY NUMBER,
p_rec_nrec_dist_end_index IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
BEGIN
IF (g_level_procedure >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_procedure,
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_global_table.BEGIN',
'ZX_TRD_SERVICES_PUB_PKG.insert_global_table(+)');
-- insert into global temporary table when there are more than 1000 tax distributions.
-- populate mandatory columns before inserting.
populate_mandatory_columns(p_rec_nrec_dist_tbl, x_return_status);
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_global_table',
'After calling populate_mandatory_columns x_return_status = '
|| x_return_status);
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_global_table.END',
'ZX_TRD_SERVICES_PUB_PKG.insert_global_table(-)');
INSERT INTO zx_rec_nrec_dist_gt VALUES p_rec_nrec_dist_tbl(ctr);
p_rec_nrec_dist_tbl.delete;
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_global_table.END',
'ZX_TRD_SERVICES_PUB_PKG.insert_global_table(-)'||x_return_status);
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_global_table',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.insert_global_table.END',
'ZX_TRD_SERVICES_PUB_PKG.insert_global_table(-)');
END insert_global_table;
SELECT ZX_REC_NREC_DIST_S.nextval
INTO ZX_TRD_SERVICES_PUB_PKG.g_tax_dist_id
FROM dual;
p_rec_nrec_dist_tbl(i).last_updated_by := fnd_global.user_id;
p_rec_nrec_dist_tbl(i).last_update_login := fnd_global.login_id;
p_rec_nrec_dist_tbl(i).last_update_date := sysdate;
PROCEDURE update_exchange_rate (
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
p_ledger_id IN NUMBER,
p_currency_conversion_rate IN NUMBER,
p_currency_conversion_type IN VARCHAR2,
p_currency_conversion_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2 ) IS
CURSOR get_rec_nrec_tax_dists_csr IS
SELECT rec_nrec_tax_dist_id,
tax_line_id,
recoverable_flag,
rec_nrec_tax_amt,
taxable_amt,
rec_nrec_tax_amt_funcl_curr,
taxable_amt_funcl_curr,
unrounded_rec_nrec_tax_amt,
unrounded_taxable_amt,
NVL(p_ledger_id, ledger_id) ledger_id,
trx_currency_code,
tax_rate,
0
FROM zx_rec_nrec_dist
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 NVL(Reverse_Flag, 'N') = 'N'
AND NVL(mrc_tax_dist_flag, 'N') = 'N'
ORDER BY tax_line_id, unrounded_rec_nrec_tax_amt DESC;
SELECT tax_amt_funcl_curr
FROM zx_lines
WHERE tax_line_id = p_tax_line_id;
SELECT nvl( cur.minimum_accountable_unit, power( 10, (-1 * precision))),
cur.currency_code
FROM fnd_currencies cur, gl_sets_of_books sob
WHERE sob.set_of_books_id = p_ledger_id
AND cur.currency_code = sob.currency_code;
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate.BEGIN',
'ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate(+)');
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate',
'No tax distributions are fetched from ZX_REC_NREC_DIST.');
l_non_zero_rec_tax_lines_tbl.DELETE;
l_non_zero_nrec_tax_lines_tbl.DELETE;
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate',
'l_minimum_accountable_unit = ' || l_minimum_accountable_unit);
l_non_zero_rec_tax_lines_tbl.DELETE;
l_non_zero_nrec_tax_lines_tbl.DELETE;
UPDATE zx_rec_nrec_dist
SET currency_conversion_rate = p_currency_conversion_rate,
currency_conversion_type = p_currency_conversion_type,
currency_conversion_date = p_currency_conversion_date,
rec_nrec_tax_amt_funcl_curr = l_tax_amt_funcl_curr_tbl(i),
taxable_amt_funcl_curr = l_taxable_amt_funcl_curr_tbl(i),
func_curr_rounding_adjustment = l_func_curr_rnd_adjustment_tbl(i),
object_version_number = object_version_number + 1
WHERE rec_nrec_tax_dist_id = l_rec_nrec_tax_dist_id_tbl(i)
AND 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;
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate.END',
'ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate(-)');
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate',
l_error_buffer);
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate.END',
'ZX_TRD_SERVICES_PUB_PKG.update_exchange_rate(-)');
END update_exchange_rate;
SELECT nvl( cur.minimum_accountable_unit, power( 10, (-1 * precision)))
FROM fnd_currencies cur, gl_sets_of_books sob
WHERE sob.set_of_books_id = p_ledger_id
AND cur.currency_code = sob.currency_code;
SELECT *
FROM zx_rec_nrec_dist
WHERE trx_line_dist_id = c_item_expense_dist_id
AND application_id = c_application_id
AND entity_code = c_entity_code
AND event_class_code = c_event_class_code
AND trx_id = c_trx_id
AND trx_line_id = c_trx_line_id
AND trx_level_type = c_trx_level_type;
SELECT rates.recovery_rule_code
FROM zx_rates_b rates
WHERE rates.tax_rate_id = c_tax_rate_id;
SELECT rules.service_type_code,
rules.priority,
factor_dtls.determining_factor_code
FROM zx_process_results results,
zx_rules_b rules,
zx_det_factor_templ_b factors,
zx_det_factor_templ_dtl factor_dtls
WHERE results.result_id = c_rec_rate_result_id
AND rules.tax_rule_id = results.tax_rule_id
AND factors.det_factor_templ_code = rules.det_factor_templ_code
AND factor_dtls.det_factor_templ_id = factors.det_factor_templ_id;
SELECT rules.service_type_code,
rules.priority,
factor_dtls.determining_factor_code
FROM zx_sco_rules_b_v rules,
zx_lines lines,
zx_evnt_cls_mappings mappings,
zx_det_factor_templ_b factors,
zx_det_factor_templ_dtl factor_dtls
WHERE rules.service_type_code = c_service_type_code
AND rules.tax = c_tax -- In phase 1, tax and regime should not be NULL
AND rules.tax_regime_code = c_tax_regime_code
AND rules.system_default_flag <> 'Y'
AND rules.enabled_flag = 'Y'
AND rules.priority < c_priority
AND rules.recovery_type_code = c_recovery_type_code
AND EXISTS (SELECT result_id
FROM zx_process_results results
WHERE results.tax_rule_id = rules.tax_rule_id
AND results.enabled_flag = 'Y')
AND mappings.event_class_code = c_event_class_code
AND mappings.application_id = c_application_id
AND mappings.entity_code = c_entity_code
AND (rules.application_id = mappings.reference_application_id OR
rules. application_id IS NULL)
AND lines.tax_line_id = c_tax_line_id
AND lines.tax_determine_date >= effective_from
AND (lines.tax_determine_date <= effective_to OR
rules.effective_to IS NULL)
AND factors.det_factor_templ_code = rules.det_factor_templ_code
AND factor_dtls.det_factor_templ_id = factors.det_factor_templ_id;
select tax_account_ccid
from zx_accounts
where TAX_ACCOUNT_ENTITY_ID = c_tax_account_entity_id
and TAX_ACCOUNT_ENTITY_CODE = 'RATES'
and ledger_id = c_ledger_id
and internal_organization_id = c_internal_org_id;
select 'x'
from gl_code_combinations
where code_combination_id = l_ccid
and enabled_flag = 'Y'
and p_gl_date between nvl(start_date_active,p_gl_date) and nvl(end_date_active, p_gl_date);
SELECT INTERNAL_ORGANIZATION_ID
INTO l_internal_org_id
FROM ZX_LINES
WHERE tax_line_id = p_tax_line_id;
| PUBLIC PROCEDURE delete_unnecessary_tax_dists |
| |
| DESCRIPTION: |
| This procedure is called if associate_child_frozen_flag on detail tax |
| lines is 'Y' after tax dists are inserted into zx_rec_nrec_dist_gt. |
| If there is no change on tax_line and item distribution, the reversed |
| tax_distributions and the new created tax distributions, which are |
| created for the frozen taxdistributions, will be deleted. |
| |
| When there are frozen tax distributions and recovery redetermination is |
| needed for the parent tax line, we reverse the existing frozen tax |
| distributions and create new tax distributions. If there is no difference |
| between the existing frozen tax distribution and the corresponding new |
| tax distribution (excluding reverse flag and frozen flag), we should |
| honor the existing frozen tax distribution and remove the newly created |
| tax distributions. In the following example, suppose there is a frozen |
| tax distribution D1, during internal processing, we create a negative |
| D2 and a positive D3. |
| D1 frozen |
| D2 negative D1 reverse |
| D3 same as D1 |
| If D3 is exactly the same as D1 (excluding reverse flag and frozen flag), |
| we delete both D2 and D3 and simply keep D1. |
| |
| The columns used for the comparison reviewed by Helen |
| |
| application_id | |
| entity_code | |
| event_class_code | |
| trx_id | -- through tax_line_id |
| trx_line_id | |
| tax_level_type | |
| tax_regime_id | |
| tax_id | |
| tax_line_id |
| trx_line_dist_id |
| tax_status_id |
| tax_rate_id |
| inclusive_flag |
| recovery_type_id |
| recovery_type_code |
| recovery_rate_id |
| recoverable_flag |
| rec_nrec_tax_amt |
| intended_use |
| project_id | |
| task_id | |
| award_id | For reporting purpose |
| expenditure_type | |
| expenditure_organization_id | |
| expenditure_item_date | |
| currency_conversion_date |
| currency_conversion_type |
| currency_conversion_rate |
| tax_currency_conversion_date |
| tax_currency_conversion_type |
| tax_currency_conversion_rate |
| trx_currency_code |
| tax_currency_code |
| backward_compatibility_flag |
| self_assessed_flag |
| ref_doc_application_id | |
| ref_doc_entity_code | |
| ref_doc_event_class_code | through ref_doc_tax_dist_id |
| ref_doc_trx_id | |
| ref_doc_line_id | |
| ref_doc_dist_id | |
| tax_only_line_flag |
| account_ccid |
| |
* =============================================================================*/
PROCEDURE delete_unnecessary_tax_dists(
p_event_class_rec IN ZX_API_PUB.EVENT_CLASS_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
TYPE NUMERIC_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.delete_unnecessary_tax_dists.BEGIN',
'ZX_TRD_SERVICES_PUB_PKG.delete_unnecessary_tax_dists(+)');
SELECT REC_NREC_TAX_DIST_ID, REVERSED_TAX_DIST_ID
BULK COLLECT INTO l_rec_nrec_tax_Dist_id_tbl1, l_reversed_tax_dist_id_tbl
FROM ZX_REC_NREC_DIST_GT gt
WHERE ( reversed_tax_dist_id IS NOT NULL
AND EXISTS
(SELECT 1 FROM ZX_REC_NREC_DIST_GT gt1
WHERE gt1.tax_line_id = gt.tax_line_id
AND gt1.trx_line_dist_id = gt.trx_line_dist_id
AND gt1.tax_status_id = gt.tax_status_id
AND gt1.tax_rate_id = gt.tax_rate_id
AND gt1.recoverable_flag = gt.recoverable_flag
AND NVL(gt1.inclusive_flag, 'N') = NVL(gt.inclusive_flag, 'N')
AND NVL(gt1.recovery_type_id, -999) = NVL(gt.recovery_type_id, -999)
AND NVL(gt1.recovery_type_code, 'x') = NVL(gt.recovery_type_code, 'x')
AND NVL(gt1.recovery_rate_code, 'x') = NVL(gt.recovery_rate_code, 'x')
/*AND NVL(gt1.project_id, -999) = NVL(gt.project_id, -999)
AND NVL(gt1.task_id, -999) = NVL(gt.task_id, -999)
AND NVL(gt1.award_id, -999) = NVL(gt.award_id, -999)
AND NVL(gt1.expenditure_type, 'x') = NVL(gt.expenditure_type, 'x')
AND NVL(gt1.expenditure_organization_id, -999) = NVL(gt.expenditure_organization_id, -999)
AND NVL(TRUNC(gt1.expenditure_item_date), DATE_DUMMY) = NVL(TRUNC(gt.expenditure_item_date), DATE_DUMMY)*/
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.project_id, -999), -999)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.project_id, -999), -999)
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.task_id, -999), -999)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.task_id, -999), -999)
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.award_id, -999), -999)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.award_id, -999), -999)
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.expenditure_type, 'x'), 'x')
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.expenditure_type, 'x'), 'x')
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.expenditure_organization_id, -999), -999)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.expenditure_organization_id, -999), -999)
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(TRUNC(gt1.expenditure_item_date), DATE_DUMMY), DATE_DUMMY)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(TRUNC(gt.expenditure_item_date), DATE_DUMMY), DATE_DUMMY)
AND NVL(TRUNC(gt1.currency_conversion_date), DATE_DUMMY) = NVL(TRUNC(gt.currency_conversion_date), DATE_DUMMY)
AND NVL(gt1.currency_conversion_type, 'x') = NVL(gt.currency_conversion_type, 'x')
AND NVL(gt1.currency_conversion_rate, 1) = NVL(gt.currency_conversion_rate, 1)
AND NVL(TRUNC(gt1.tax_currency_conversion_date), DATE_DUMMY) = NVL(TRUNC(gt.tax_currency_conversion_date), DATE_DUMMY)
AND NVL(gt1.tax_currency_conversion_type, 'x') = NVL(gt.tax_currency_conversion_type, 'x')
AND NVL(gt1.tax_currency_conversion_rate, 1) = NVL(gt.tax_currency_conversion_rate, 1)
AND NVL(gt1.trx_currency_code, 'x') = NVL(gt.trx_currency_code, 'x')
AND NVL(gt1.tax_currency_code, 'x') = NVL(gt.tax_currency_code, 'x')
AND NVL(gt1.backward_compatibility_flag, 'x') = NVL(gt.backward_compatibility_flag, 'x')
AND NVL(gt1.self_assessed_flag, 'N') = NVL(gt.self_assessed_flag, 'N')
AND NVL(gt1.intended_use, 'x') = NVL(gt.intended_use, 'x')
AND NVL(gt1.tax_only_line_flag, 'N') = NVL(gt.tax_only_line_flag, 'N')
--AND NVL(gt1.account_ccid, -999) = NVL(gt.account_ccid, -999)
AND gt1.rec_nrec_tax_amt = -gt.rec_nrec_tax_amt
AND gt1.trx_line_dist_amt = -gt.trx_line_dist_amt -- bug 6709478
AND gt1.trx_line_dist_tax_amt = -gt.trx_line_dist_tax_amt -- bug 6709478
AND gt1.rec_nrec_tax_dist_number > gt.rec_nrec_tax_dist_number
AND gt1.freeze_flag = 'N'
AND gt1.reverse_flag = 'N'
)
);
SELECT rec_nrec_tax_dist_id
BULK COLLECT INTO l_rec_nrec_tax_Dist_id_tbl2
FROM ZX_REC_NREC_DIST_GT gt
WHERE ( gt.freeze_flag = 'N'
AND gt.reverse_flag = 'N'
AND EXISTS
(SELECT 1 FROM ZX_REC_NREC_DIST_GT gt1
WHERE gt1.tax_line_id = gt.tax_line_id
AND gt1.trx_line_dist_id = gt.trx_line_dist_id
AND gt1.tax_status_id = gt.tax_status_id
AND gt1.tax_rate_id = gt.tax_rate_id
AND gt1.recoverable_flag = gt.recoverable_flag
AND NVL(gt1.inclusive_flag, 'N') = NVL(gt.inclusive_flag, 'N')
AND NVL(gt1.recovery_type_id, -999) = NVL(gt.recovery_type_id, -999)
AND NVL(gt1.recovery_type_code, 'x') = NVL(gt.recovery_type_code, 'x')
AND NVL(gt1.recovery_rate_code, 'x') = NVL(gt.recovery_rate_code, 'x')
/*AND NVL(gt1.project_id, -999) = NVL(gt.project_id, -999)
AND NVL(gt1.task_id, -999) = NVL(gt.task_id, -999)
AND NVL(gt1.award_id, -999) = NVL(gt.award_id, -999)
AND NVL(gt1.expenditure_type, 'x') = NVL(gt.expenditure_type, 'x')
AND NVL(gt1.expenditure_organization_id, -999) = NVL(gt.expenditure_organization_id, -999)
AND NVL(TRUNC(gt1.expenditure_item_date), DATE_DUMMY) = NVL(TRUNC(gt.expenditure_item_date), DATE_DUMMY)*/
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.project_id, -999), -999)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.project_id, -999), -999)
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.task_id, -999), -999)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.task_id, -999), -999)
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.award_id, -999), -999)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.award_id, -999), -999)
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.expenditure_type, 'x'), 'x')
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.expenditure_type, 'x'), 'x')
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(gt1.expenditure_organization_id, -999), -999)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(gt.expenditure_organization_id, -999), -999)
AND DECODE(gt1.HISTORICAL_FLAG, 'N', NVL(TRUNC(gt1.expenditure_item_date), DATE_DUMMY), DATE_DUMMY)
= DECODE(gt.HISTORICAL_FLAG, 'N', NVL(TRUNC(gt.expenditure_item_date), DATE_DUMMY), DATE_DUMMY)
AND NVL(TRUNC(gt1.currency_conversion_date), DATE_DUMMY) = NVL(TRUNC(gt.currency_conversion_date), DATE_DUMMY)
AND NVL(gt1.currency_conversion_type, 'x') = NVL(gt.currency_conversion_type, 'x')
AND NVL(gt1.currency_conversion_rate, 1) = NVL(gt.currency_conversion_rate, 1)
AND NVL(TRUNC(gt1.tax_currency_conversion_date), DATE_DUMMY) = NVL(TRUNC(gt.tax_currency_conversion_date), DATE_DUMMY)
AND NVL(gt1.tax_currency_conversion_type, 'x') = NVL(gt.tax_currency_conversion_type, 'x')
AND NVL(gt1.tax_currency_conversion_rate, 1) = NVL(gt.tax_currency_conversion_rate, 1)
AND NVL(gt1.trx_currency_code, 'x') = NVL(gt.trx_currency_code, 'x')
AND NVL(gt1.tax_currency_code, 'x') = NVL(gt.tax_currency_code, 'x')
AND NVL(gt1.backward_compatibility_flag, 'x') = NVL(gt.backward_compatibility_flag, 'x')
AND NVL(gt1.self_assessed_flag, 'N') = NVL(gt.self_assessed_flag, 'N')
AND NVL(gt1.intended_use, 'x') = NVL(gt.intended_use, 'x')
AND NVL(gt1.tax_only_line_flag, 'N') = NVL(gt.tax_only_line_flag, 'N')
--AND NVL(gt1.account_ccid, -999) = NVL(gt.account_ccid, -999)
AND gt1.rec_nrec_tax_amt = gt.rec_nrec_tax_amt
AND gt1.trx_line_dist_amt = gt.trx_line_dist_amt -- bug 6709478
AND gt1.trx_line_dist_tax_amt = gt.trx_line_dist_tax_amt -- bug 6709478
AND gt1.rec_nrec_tax_dist_number < gt.rec_nrec_tax_dist_number
AND gt1.freeze_flag = 'Y'
AND gt1.reverse_flag = 'Y'
)
);
DELETE FROM zx_rec_nrec_dist_gt
WHERE rec_nrec_tax_dist_id IN l_rec_nrec_tax_Dist_id_tbl1(j);
DELETE FROM zx_rec_nrec_dist_gt
WHERE rec_nrec_tax_dist_id IN l_rec_nrec_tax_Dist_id_tbl2(j);
UPDATE zx_rec_nrec_dist_gt gt
SET reverse_flag = 'N'
WHERE rec_nrec_tax_dist_id = l_reversed_tax_dist_id_tbl(i);
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.delete_unnecessary_tax_dists.END',
'ZX_TRD_SERVICES_PUB_PKG.delete_unnecessary_tax_dists(-)');
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.delete_unnecessary_tax_dists',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.delete_unnecessary_tax_dists.END',
'ZX_TRD_SERVICES_PUB_PKG.delete_unnecessary_tax_dists(-)');
END delete_unnecessary_tax_dists;
PROCEDURE update_posting_flag(
p_tax_dist_id_tbl IN ZX_API_PUB.tax_dist_id_tbl_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_error_buffer VARCHAR2(200);
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_posting_flag.BEGIN',
'ZX_TRD_SERVICES_PUB_PKG.update_posting_flag(+)');
UPDATE ZX_Rec_Nrec_Dist
SET posting_flag = 'A'
WHERE rec_nrec_tax_dist_id = p_tax_dist_id_tbl(i);
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_posting_flag.END',
'ZX_TRD_SERVICES_PUB_PKG.update_posting_flag(-)');
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_posting_flag',
l_error_buffer);
'ZX.PLSQL.ZX_TRD_SERVICES_PUB_PKG.update_posting_flag.END',
'ZX_TRD_SERVICES_PUB_PKG.ZX_TRD_SERVICES_PUB_PKG(-)');
END update_posting_flag;
select account_source_tax_rate_id
from zx_lines
where tax_line_id = p_tax_line_id;
SELECT det.ship_to_location_id,det.ship_from_location_id,
det.bill_to_location_id,det.bill_from_location_id,det.trx_date
FROM zx_lines_det_factors det, zx_lines
WHERE det.trx_id = zx_lines.trx_id
and det.trx_line_id = zx_lines.trx_line_id
and det.application_id = zx_lines.application_id
and det.entity_code = zx_lines.entity_code
and det.event_class_code = zx_lines.event_class_code
and zx_lines.tax_line_id = c_tax_line_id;
SELECT zone_geography_type,tax,tax_regime_code
FROM ZX_SCO_TAXES_B_V
WHERE (tax_regime_code,tax) =
(SELECT tax_regime_code,tax from ZX_SCO_RATES_B_V
WHERE tax_rate_id = c_tax_rate_id);
SELECT Count(*)
FROM zx_lines
WHERE tax_line_id = c_tax_line_id
AND tax_provider_id IS NOT NULL;
SELECT count(1)
INTO l_geo_use_count
FROM hz_geography_types_b
WHERE geography_type = l_geography_type
AND geography_use <> 'MASTER_REF'
AND rownum = 1;
SELECT tax_jurisdiction_id
INTO p_tax_jurisdiction_id
FROM zx_jurisdictions_b
WHERE effective_from <= l_trx_date
AND (effective_to >= l_trx_date or effective_to is null)
AND tax = l_tax
AND tax_regime_code = l_tax_regime_code
AND zone_geography_id = l_geography_id
AND (nvl(inner_city_jurisdiction_flag,'xx') = nvl(l_inner_city_jurisdiction_flag, 'xx') OR
(inner_city_jurisdiction_flag is null and l_inner_city_jurisdiction_flag is not null) OR
(inner_city_jurisdiction_flag is not null and l_inner_city_jurisdiction_flag is null));
SELECT tax_jurisdiction_id
INTO p_tax_jurisdiction_id
FROM zx_jurisdictions_b
WHERE effective_from <= l_trx_date
AND (effective_to >= l_trx_date or effective_to is null)
AND tax = l_tax
AND tax_regime_code = l_tax_regime_code
AND zone_geography_id = l_geography_id
AND (nvl(inner_city_jurisdiction_flag,'xx') = nvl(l_inner_city_jurisdiction_flag, 'xx') OR
(inner_city_jurisdiction_flag is null and l_inner_city_jurisdiction_flag is not null) OR
(inner_city_jurisdiction_flag is not null and l_inner_city_jurisdiction_flag is null));