[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_header_rounding_curr(
p_tax_line_id IN ZX_LINES.TAX_LINE_ID%TYPE,
p_unrounded_tax_amt IN ZX_LINES.UNROUNDED_TAX_AMT%TYPE,
p_tax_amt_curr IN ZX_LINES.TAX_AMT_FUNCL_CURR%TYPE,
p_taxable_amt_curr IN ZX_LINES.TAXABLE_AMT_FUNCL_CURR%TYPE,
p_currency_conversion_rate IN ZX_LINES.CURRENCY_CONVERSION_RATE%TYPE,
p_prev_hdr_grp_rec IN OUT NOCOPY HDR_GRP_REC_TYPE,
p_curr_hdr_grp_rec IN HDR_GRP_REC_TYPE,
p_same_tax IN VARCHAR2,
p_ledger_id IN ZX_LINES.LEDGER_ID%TYPE,
p_return_status OUT NOCOPY VARCHAR2,
p_error_buffer OUT NOCOPY VARCHAR2
);
PROCEDURE update_header_rounding_info(
p_tax_line_id IN ZX_LINES.TAX_LINE_ID%TYPE,
p_tax_id IN ZX_TAXES_B.TAX_ID%TYPE,
p_Rounding_Rule_Code IN ZX_LINES.Rounding_Rule_Code%TYPE,
p_min_acct_unit IN ZX_LINES.MINIMUM_ACCOUNTABLE_UNIT%TYPE,
p_precision IN ZX_LINES.PRECISION%TYPE,
p_unrounded_tax_amt IN ZX_LINES.UNROUNDED_TAX_AMT%TYPE,
p_tax_amt IN ZX_LINES.TAX_AMT%TYPE,
p_tax_amt_tax_curr IN ZX_LINES.TAX_AMT_TAX_CURR%TYPE,
p_tax_amt_funcl_curr IN ZX_LINES.TAX_AMT_FUNCL_CURR%TYPE,
p_taxable_amt_tax_curr IN ZX_LINES.TAXABLE_AMT_TAX_CURR%TYPE,
p_taxable_amt_funcl_curr IN ZX_LINES.TAXABLE_AMT_FUNCL_CURR%TYPE,
p_tax_curr_conv_rate IN ZX_LINES.TAX_CURRENCY_CONVERSION_RATE%TYPE,
p_currency_conversion_rate IN ZX_LINES.CURRENCY_CONVERSION_RATE%TYPE,
p_prev_hdr_grp_rec IN OUT NOCOPY HDR_GRP_REC_TYPE,
p_curr_hdr_grp_rec IN HDR_GRP_REC_TYPE,
p_same_tax IN VARCHAR2,
p_sum_unrnd_tax_amt IN NUMBER,
p_sum_rnd_tax_amt IN NUMBER,
p_sum_rnd_tax_curr IN NUMBER,
p_sum_rnd_funcl_curr IN NUMBER,
p_ledger_id IN ZX_LINES.LEDGER_ID%TYPE,
p_return_status OUT NOCOPY VARCHAR2,
p_error_buffer OUT NOCOPY VARCHAR2
);
PROCEDURE update_detail_tax_lines_gt(
p_min_acct_unit_tbl IN MIN_ACCT_UNIT_TBL,
p_precision_tbl IN PRECISION_TBL,
p_tax_currency_code_tbl IN TAX_CURRENCY_CODE_TBL,
p_tax_curr_conv_rate_tbl IN TAX_CURR_CONV_RATE_TBL,
p_tax_amt_tbl IN TAX_AMT_TBL,
p_taxable_amt_tbl IN TAXABLE_AMT_TBL,
p_tax_amt_tax_curr_tbl IN TAX_AMT_TAX_CURR_TBL,
p_taxable_amt_tax_curr_tbl IN TAXABLE_AMT_TAX_CURR_TBL,
p_tax_amt_funcl_curr_tbl IN TAX_AMT_FUNCL_CURR_TBL,
p_taxable_amt_funcl_curr_tbl IN TAXABLE_AMT_FUNCL_CURR_TBL,
p_prd_total_tax_amt_tbl IN PRD_TOTAL_TAX_AMT_TBL,
p_prd_tot_tax_amt_tax_curr_tbl IN PRD_TOTAL_TAX_AMT_TAX_CURR_TBL,
p_prd_tot_tax_amt_fcl_curr_tbl IN PRD_TOTAL_TAX_AMT_FCL_CURR_TBL,
p_cal_tax_amt_funcl_curr_tbl IN CAL_TAX_AMT_FUNCL_CURR_TBL,
p_orig_tax_amt_tax_curr_tbl IN TAX_AMT_TBL,
p_orig_taxable_amt_tax_cur_tbl IN TAXABLE_AMT_TBL,
p_tax_line_id_tbl IN TAX_LINE_ID_TBL,
p_return_status OUT NOCOPY VARCHAR2,
p_error_buffer OUT NOCOPY VARCHAR2
);
PROCEDURE update_zx_lines(
p_conversion_rate IN NUMBER,
p_conversion_type IN VARCHAR2,
p_conversion_date IN DATE,
p_tax_amt_funcl_curr_tbl IN TAX_AMT_FUNCL_CURR_TBL,
p_taxable_amt_funcl_curr_tbl IN TAXABLE_AMT_FUNCL_CURR_TBL,
p_cal_tax_amt_funcl_curr_tbl IN CAL_TAX_AMT_FUNCL_CURR_TBL,
p_tax_line_id_tbl IN TAX_LINE_ID_TBL,
p_return_status OUT NOCOPY VARCHAR2,
p_error_buffer OUT NOCOPY VARCHAR2
);
SELECT decode( derive_type,
'EURO', 'EURO',
'EMU', decode( sign( trunc(p_eff_date) -
trunc(derive_effective)),
-1, 'OTHER',
'EMU'),
'OTHER' ),
decode( derive_type, 'EURO', 1,
'EMU', derive_factor,
'OTHER', -1 ),
derive_type,
derive_effective,
minimum_accountable_unit,
precision
FROM FND_CURRENCIES
WHERE currency_code = c_currency;
/* SELECT decode( derive_type,
'EURO', 'EURO',
'EMU', decode( sign( trunc(p_eff_date) -
trunc(derive_effective)),
-1, 'OTHER',
'EMU'),
'OTHER' ),
decode( derive_type, 'EURO', 1,
'EMU', derive_factor,
'OTHER', -1 ),
derive_type,
derive_effective,
minimum_accountable_unit,
precision
INTO p_currency_type,
p_conversion_rate,
p_derive_type,
p_derive_effective,
p_mau,
p_precision
FROM FND_CURRENCIES
WHERE currency_code = p_currency;*/
SELECT conversion_rate
FROM GL_DAILY_RATES
WHERE from_currency = c_from_currency
AND to_currency = c_to_currency
AND conversion_date = trunc(c_conversion_date)
AND conversion_type = c_conversion_type;
SELECT currency_code
INTO euro_code
FROM FND_CURRENCIES
WHERE derive_type = 'EURO';
SELECT /*+ INDEX ( HEADER ZX_TRX_HEADERS_GT_U1 ) INDEX ( TRXLINES ZX_TRANSACTION_LINES_GT_U1 ) */
NVL(trxlines.ship_third_pty_acct_id,
header.ship_third_pty_acct_id) ship_third_pty_acct_id,
NVL(trxlines.bill_third_pty_acct_id,
header.bill_third_pty_acct_id) bill_third_pty_acct_id,
NVL(trxlines.ship_third_pty_acct_site_id,
header.ship_third_pty_acct_site_id) ship_third_pty_acct_site_id,
NVL(trxlines.bill_third_pty_acct_site_id,
header.bill_third_pty_acct_site_id) bill_third_pty_acct_site_id,
NVL(trxlines.ship_to_cust_acct_site_use_id,
header.ship_to_cust_acct_site_use_id) ship_to_cust_acct_site_use_id,
NVL(trxlines.bill_to_cust_acct_site_use_id,
header.bill_to_cust_acct_site_use_id) bill_to_cust_acct_site_use_id,
NVL(header.related_doc_date, NVL(header.provnl_tax_determination_date,
NVL(trxlines.adjusted_doc_date,
NVL(trxlines.trx_line_date, header.trx_date)))) tax_determine_date
FROM ZX_TRANSACTION_LINES_GT trxlines, ZX_TRX_HEADERS_GT header
WHERE header.application_id = p_event_class_rec.application_id
AND header.entity_code = p_event_class_rec.entity_code
AND header.event_class_code = p_event_class_rec.event_class_code
AND header.trx_id = p_event_class_rec.trx_id
AND trxlines.application_id = header.application_id
AND trxlines.entity_code = header.entity_code
AND trxlines.event_class_code = header.event_class_code
AND trxlines.trx_id = header.trx_id
AND rownum = 1;
select SHIP_THIRD_PTY_ACCT_ID,
BILL_THIRD_PTY_ACCT_ID,
SHIP_THIRD_PTY_ACCT_SITE_ID,
BILL_THIRD_PTY_ACCT_SITE_ID,
SHIP_TO_CUST_ACCT_SITE_USE_ID,
BILL_TO_CUST_ACCT_SITE_USE_ID,
coalesce(related_doc_date,
provnl_tax_determination_date,
adjusted_doc_date,
trx_line_date,
trx_date) tax_determine_date
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 ;
update_header_rounding_curr(
p_tax_line_id,
p_unrounded_tax_amt,
p_tax_amt_curr,
p_taxable_amt_curr,
p_currency_conversion_rate,
p_prev_hdr_grp_rec,
p_curr_hdr_grp_rec,
l_same_tax,
p_ledger_id,
p_return_status,
p_error_buffer);
'update the adjustments to the largest lines ....');
UPDATE ZX_LINES
SET tax_amt_funcl_curr = l_tax_amt_curr_tbl(i),
taxable_amt_funcl_curr = l_taxable_amt_curr_tbl(i)
WHERE tax_line_id = l_tax_line_id_tbl(i);
'update the adjustments to the largest lines ....');
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
ZX_DETAIL_TAX_LINES_GT
SET tax_amt = l_tax_amt_tbl(i),
tax_amt_tax_curr = l_tax_amt_tax_curr_tbl(i),
tax_amt_funcl_curr = l_tax_amt_funcl_curr_tbl(i),
taxable_amt_tax_curr = l_taxable_amt_tax_curr_tbl(i),
taxable_amt_funcl_curr = l_taxable_amt_funcl_curr_tbl(i)
WHERE tax_line_id = l_tax_line_id_tbl(i);
PROCEDURE update_header_rounding_curr(
p_tax_line_id IN ZX_LINES.TAX_LINE_ID%TYPE,
p_unrounded_tax_amt IN ZX_LINES.UNROUNDED_TAX_AMT%TYPE,
p_tax_amt_curr IN ZX_LINES.TAX_AMT_FUNCL_CURR%TYPE,
p_taxable_amt_curr IN ZX_LINES.TAXABLE_AMT_FUNCL_CURR%TYPE,
p_currency_conversion_rate IN ZX_LINES.CURRENCY_CONVERSION_RATE%TYPE,
p_prev_hdr_grp_rec IN OUT NOCOPY HDR_GRP_REC_TYPE,
p_curr_hdr_grp_rec IN HDR_GRP_REC_TYPE,
p_same_tax IN VARCHAR2,
p_ledger_id IN ZX_LINES.LEDGER_ID%TYPE,
p_return_status OUT NOCOPY VARCHAR2,
p_error_buffer OUT NOCOPY VARCHAR2
)
IS
j BINARY_INTEGER;
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_header_rounding_curr.BEGIN',
'ZX_TDS_TAX_ROUNDING_PKG: update_header_rounding_curr(+)');
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_header_rounding_curr.END',
'ZX_TDS_TAX_ROUNDING_PKG: update_header_rounding_curr(-)');
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_header_rounding_curr',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
END update_header_rounding_curr;
PROCEDURE update_header_rounding_info(
p_tax_line_id IN ZX_LINES.TAX_LINE_ID%TYPE,
p_tax_id IN ZX_TAXES_B.TAX_ID%TYPE,
p_Rounding_Rule_Code IN ZX_LINES.Rounding_Rule_Code%TYPE,
p_min_acct_unit IN ZX_LINES.MINIMUM_ACCOUNTABLE_UNIT%TYPE,
p_precision IN ZX_LINES.PRECISION%TYPE,
p_unrounded_tax_amt IN ZX_LINES.UNROUNDED_TAX_AMT%TYPE,
p_tax_amt IN ZX_LINES.TAX_AMT%TYPE,
p_tax_amt_tax_curr IN ZX_LINES.TAX_AMT_TAX_CURR%TYPE,
p_tax_amt_funcl_curr IN ZX_LINES.TAX_AMT_FUNCL_CURR%TYPE,
p_taxable_amt_tax_curr IN ZX_LINES.TAXABLE_AMT_TAX_CURR%TYPE,
p_taxable_amt_funcl_curr IN ZX_LINES.TAXABLE_AMT_FUNCL_CURR%TYPE,
p_tax_curr_conv_rate IN ZX_LINES.TAX_CURRENCY_CONVERSION_RATE%TYPE,
p_currency_conversion_rate IN ZX_LINES.CURRENCY_CONVERSION_RATE%TYPE,
p_prev_hdr_grp_rec IN OUT NOCOPY HDR_GRP_REC_TYPE,
p_curr_hdr_grp_rec IN HDR_GRP_REC_TYPE,
p_same_tax IN VARCHAR2,
p_sum_unrnd_tax_amt IN NUMBER,
p_sum_rnd_tax_amt IN NUMBER,
p_sum_rnd_tax_curr IN NUMBER,
p_sum_rnd_funcl_curr IN NUMBER,
p_ledger_id IN ZX_LINES.LEDGER_ID%TYPE,
p_return_status OUT NOCOPY VARCHAR2,
p_error_buffer OUT NOCOPY VARCHAR2
)
IS
j BINARY_INTEGER;
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_header_rounding_info.BEGIN',
'ZX_TDS_TAX_ROUNDING_PKG: update_header_rounding_info(+)');
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_header_rounding_info.END',
'ZX_TDS_TAX_ROUNDING_PKG: update_header_rounding_info(-)');
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_header_rounding_info',
sqlcode || ': ' || SUBSTR(SQLERRM, 1, 80));
END update_header_rounding_info;
SELECT SUM(unrounded_tax_amt),
SUM(tax_amt),
SUM(tax_amt_tax_curr),
SUM(tax_amt_funcl_curr)
FROM ZX_LINES L
WHERE L.trx_id = c_trx_id AND
L.application_id = c_application_id AND
L.event_class_code = c_event_class_code AND
L.entity_code = c_entity_code AND
L.tax_regime_code = c_tax_regime_code AND
L.tax = c_tax AND
NVL(L.tax_status_code, 'X') = NVL(c_tax_status_code, 'X') AND
NVL(L.tax_rate_code, 'X') = NVL(c_tax_rate_code, 'X') AND
NVL(L.tax_rate, -999) = NVL(c_tax_rate, -999) AND
NVL(L.tax_rate_id, -999) = NVL(c_tax_rate_id, -999) AND
NVL(L.tax_jurisdiction_code, 'X') = NVL(c_tax_jurisdiction_code, 'X') AND
NVL(L.taxable_basis_formula, 'X') = NVL(c_taxable_basis_formula, 'X') AND
NVL(L.tax_calculation_formula, 'X') = NVL(c_tax_calculation_formula, 'X') AND
L.Tax_Amt_Included_Flag = c_tax_amt_included_flag AND
L.compounding_tax_flag = c_compounding_tax_flag AND
L.historical_flag = c_historical_flag AND
L.self_assessed_flag = c_self_assessed_flag AND
L.overridden_flag = c_overridden_flag AND
L.manually_entered_flag = c_manually_entered_flag AND
L.Copied_From_Other_Doc_Flag = c_copied_from_other_doc_flag AND
L.associated_child_frozen_flag = c_associated_child_frozen_flag AND
L.tax_only_line_flag = c_tax_only_line_flag AND
L.mrc_tax_line_flag = c_mrc_tax_line_flag AND
L.reporting_only_flag = c_reporting_only_flag AND
NVL(L.applied_from_application_id, -999) = NVL(c_applied_from_application_id, -999) AND
NVL(L.applied_from_event_class_code, 'X') = NVL(c_applied_from_evnt_cls_cd, 'X') AND
NVL(L.applied_from_entity_code, 'X') = NVL(c_applied_from_entity_code, 'X') AND
NVL(L.applied_from_trx_id, -999) = NVL(c_applied_from_trx_id, -999) AND
NVL(L.applied_from_line_id, -999) = NVL(c_applied_from_line_id, -999) AND
NVL(L.adjusted_doc_application_id, -999) = NVL(c_adjusted_doc_application_id, -999) AND
NVL(L.adjusted_doc_entity_code, 'X') = NVL(c_adjusted_doc_entity_code, 'X') AND
NVL(L.adjusted_doc_event_class_code, 'X') = NVL(c_adjusted_doc_evnt_cls_cd, 'X') AND
NVL(L.adjusted_doc_trx_id, -999) = NVL(c_adjusted_doc_trx_id, -999) AND
-- NVL(L.applied_to_application_id, -999) = NVL(c_applied_to_application_id, -999) AND
-- NVL(L.applied_to_event_class_code, 'X') = NVL(c_applied_to_evnt_cls_cd, 'X') AND
-- NVL(L.applied_to_entity_code, 'X') = NVL(c_applied_to_entity_code, 'X') AND
-- NVL(L.applied_to_trx_id, -999) = NVL(c_applied_to_trx_id, -999) AND
-- NVL(L.applied_to_line_id, -999) = NVL(c_applied_to_line_id, -999) AND
NVL(L.tax_exemption_id, -999) = NVL(c_tax_exemption_id, -999) AND
NVL(L.tax_rate_before_exemption, -999) = NVL(c_tax_rate_before_exemption, -999) AND
NVL(L.tax_rate_name_before_exemption, 'X') = NVL(c_rate_name_before_exemption, 'X') AND
NVL(L.exempt_rate_modifier, -999) = NVL(c_exempt_rate_modifier, -999) AND
NVL(L.exempt_certificate_number, 'X') = NVL(c_exempt_certificate_number, 'X') AND
NVL(L.exempt_reason, 'X') = NVL(c_exempt_reason, 'X') AND
NVL(L.exempt_reason_code, 'X') = NVL(c_exempt_reason_code, 'X') AND
NVL(L.tax_exception_id, -999) = NVL(c_tax_exception_id, -999) AND
NVL(L.tax_rate_before_exception, -999) = NVL(c_tax_rate_before_exception, -999) AND
NVL(L.tax_rate_name_before_exception, 'X') = NVL(c_rate_name_before_exception, 'X') AND
NVL(L.exception_rate, -999) = NVL(c_exception_rate, -999) AND
NVL(L.ledger_id, -999) = NVL(c_ledger_id, -999) AND
NVL(L.legal_entity_id, -999) = NVL(c_legal_entity_id, -999) AND
NVL(L.establishment_id, -999) = NVL(c_establishment_id, -999) AND
TRUNC(NVL(L.currency_conversion_date, SYSDATE)) = TRUNC(NVL(c_currency_conversion_date, SYSDATE)) AND
NVL(L.currency_conversion_type, 'X') = NVL(c_currency_conversion_type, 'X') AND
NVL(L.currency_conversion_rate, -999) = NVL(c_currency_conversion_rate, -999) AND
L.record_type_code = c_record_type_code AND
L.offset_link_to_tax_line_id IS NULL AND
NOT EXISTS (SELECT /*+ INDEX(G ZX_DETAIL_TAX_LINES_GT_U2) */ 1
FROM ZX_DETAIL_TAX_LINES_GT G
WHERE G.tax_line_id = L.tax_line_id);
PROCEDURE update_detail_tax_lines_gt(
p_min_acct_unit_tbl IN MIN_ACCT_UNIT_TBL,
p_precision_tbl IN PRECISION_TBL,
p_tax_currency_code_tbl IN TAX_CURRENCY_CODE_TBL,
p_tax_curr_conv_rate_tbl IN TAX_CURR_CONV_RATE_TBL,
p_tax_amt_tbl IN TAX_AMT_TBL,
p_taxable_amt_tbl IN TAXABLE_AMT_TBL,
p_tax_amt_tax_curr_tbl IN TAX_AMT_TAX_CURR_TBL,
p_taxable_amt_tax_curr_tbl IN TAXABLE_AMT_TAX_CURR_TBL,
p_tax_amt_funcl_curr_tbl IN TAX_AMT_FUNCL_CURR_TBL,
p_taxable_amt_funcl_curr_tbl IN TAXABLE_AMT_FUNCL_CURR_TBL,
p_prd_total_tax_amt_tbl IN PRD_TOTAL_TAX_AMT_TBL,
p_prd_tot_tax_amt_tax_curr_tbl IN PRD_TOTAL_TAX_AMT_TAX_CURR_TBL,
p_prd_tot_tax_amt_fcl_curr_tbl IN PRD_TOTAL_TAX_AMT_FCL_CURR_TBL,
p_cal_tax_amt_funcl_curr_tbl IN CAL_TAX_AMT_FUNCL_CURR_TBL,
p_orig_tax_amt_tax_curr_tbl IN TAX_AMT_TBL,
p_orig_taxable_amt_tax_cur_tbl IN TAXABLE_AMT_TBL,
p_tax_line_id_tbl IN TAX_LINE_ID_TBL,
p_return_status OUT NOCOPY VARCHAR2,
p_error_buffer OUT NOCOPY VARCHAR2
)
IS
i BINARY_INTEGER;
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_detail_tax_lines_gt.BEGIN',
'ZX_TDS_TAX_ROUNDING_PKG: update_detail_tax_lines_gt(+)');
UPDATE /*+ INDEX(ZX_DETAIL_TAX_LINES_GT ZX_DETAIL_TAX_LINES_GT_U2) */
ZX_DETAIL_TAX_LINES_GT
SET Recalc_Required_Flag = 'N',
minimum_accountable_unit = p_min_acct_unit_tbl(i),
precision = p_precision_tbl(i),
tax_currency_code = p_tax_currency_code_tbl(i),
tax_currency_conversion_rate = p_tax_curr_conv_rate_tbl(i),
tax_amt = p_tax_amt_tbl(i),
taxable_amt = p_taxable_amt_tbl(i),
tax_amt_tax_curr = p_tax_amt_tax_curr_tbl(i),
taxable_amt_tax_curr = p_taxable_amt_tax_curr_tbl(i),
tax_amt_funcl_curr = p_tax_amt_funcl_curr_tbl(i),
taxable_amt_funcl_curr = p_taxable_amt_funcl_curr_tbl(i),
prd_total_tax_amt = p_prd_total_tax_amt_tbl(i),
prd_total_tax_amt_tax_curr = p_prd_tot_tax_amt_tax_curr_tbl(i),
prd_total_tax_amt_funcl_curr = p_prd_tot_tax_amt_fcl_curr_tbl(i),
cal_tax_amt_funcl_curr = p_cal_tax_amt_funcl_curr_tbl(i),
orig_tax_amt_tax_curr = p_orig_tax_amt_tax_curr_tbl(i),
orig_taxable_amt_tax_curr = p_orig_taxable_amt_tax_cur_tbl(i)
WHERE tax_line_id = p_tax_line_id_tbl(i);
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_detail_tax_lines_gt.END',
'ZX_TDS_TAX_ROUNDING_PKG: update_detail_tax_lines_gt(-)'||
'p_return_status = ' || p_return_status);
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_detail_tax_lines_gt',
p_error_buffer);
END update_detail_tax_lines_gt;
PROCEDURE update_zx_lines(
p_conversion_rate IN NUMBER,
p_conversion_type IN VARCHAR2,
p_conversion_date IN DATE,
p_tax_amt_funcl_curr_tbl IN TAX_AMT_FUNCL_CURR_TBL,
p_taxable_amt_funcl_curr_tbl IN TAXABLE_AMT_FUNCL_CURR_TBL,
p_cal_tax_amt_funcl_curr_tbl IN CAL_TAX_AMT_FUNCL_CURR_TBL,
p_tax_line_id_tbl IN TAX_LINE_ID_TBL,
p_return_status OUT NOCOPY VARCHAR2,
p_error_buffer OUT NOCOPY VARCHAR2
)
IS
l_count NUMBER;
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_zx_lines.BEGIN',
'ZX_TDS_TAX_ROUNDING_PKG: update_zx_lines(+)');
select Decode(tax_currency_conversion_rate,0,2,currency_conversion_rate/tax_currency_conversion_rate),
NVL(tax.minimum_accountable_unit, power(10, (-1 * tax.tax_precision))),
tax_amt_tax_curr,
taxable_amt_tax_curr,
cal_tax_amt_tax_curr,
zxl.rounding_rule_code
into l_rate_ratio,
l_mau,
l_tax_amt_tax_curr,
l_taxable_amt_tax_curr,
l_cal_tax_amt_tax_curr,
l_rounding_rule_code
from zx_lines zxl,zx_taxes_b tax
where zxl.tax_line_id = p_tax_line_id_tbl(i)
and zxl.tax_id = tax.tax_id;
UPDATE ZX_LINES
SET currency_conversion_date = p_conversion_date,
currency_conversion_type = p_conversion_type,
currency_conversion_rate = p_conversion_rate,
tax_amt_funcl_curr = p_tax_amt_funcl_curr_tbl(i),
taxable_amt_funcl_curr = p_taxable_amt_funcl_curr_tbl(i),
cal_tax_amt_funcl_curr = p_cal_tax_amt_funcl_curr_tbl(i),
tax_currency_conversion_date = Decode(l_rate_ratio,1,p_conversion_date,
tax_currency_conversion_date),
tax_currency_conversion_type = Decode(l_rate_ratio,1,p_conversion_type,
tax_currency_conversion_type),
tax_currency_conversion_rate = Decode(l_rate_ratio,1,p_conversion_rate,
tax_currency_conversion_rate),
tax_amt_tax_curr = l_tax_amt_tax_curr,
taxable_amt_tax_curr = l_taxable_amt_tax_curr,
cal_tax_amt_tax_curr = l_cal_tax_amt_tax_curr
WHERE tax_line_id = p_tax_line_id_tbl(i);
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_zx_lines.END',
'ZX_TDS_TAX_ROUNDING_PKG: update_zx_lines(-)'||p_return_status);
'ZX.PLSQL.ZX_TDS_TAX_ROUNDING_PKG.update_zx_lines',
p_error_buffer);
END update_zx_lines;
SELECT DISTINCT
application_id,
entity_code,
event_class_code,
trx_id
FROM ZX_DETAIL_TAX_LINES_GT
WHERE offset_link_to_tax_line_id IS NULL;
SELECT /*+ dynamic_sampling(1) */
tax_line_id,
Manually_Entered_Flag,
tax_id,
tax_regime_code,
tax,
tax_status_code,
tax_rate_code,
tax_rate,
tax_rate_id,
tax_jurisdiction_code,
taxable_basis_formula,
tax_calculation_formula,
Tax_Amt_Included_Flag,
compounding_tax_flag,
historical_flag,
self_assessed_flag,
overridden_flag,
Copied_From_Other_Doc_Flag,
associated_child_frozen_flag,
tax_only_line_flag,
mrc_tax_line_flag,
reporting_only_flag,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
-- applied_to_application_id,
-- applied_to_event_class_code,
-- applied_to_entity_code,
-- applied_to_trx_id,
-- applied_to_line_id,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
ledger_id,
legal_entity_id,
establishment_id,
record_type_code,
minimum_accountable_unit,
precision,
trx_currency_code,
tax_currency_code,
tax_currency_conversion_date,
tax_currency_conversion_type,
tax_currency_conversion_rate,
tax_amt,
taxable_amt,
cal_tax_amt,
tax_amt_tax_curr,
taxable_amt_tax_curr,
cal_tax_amt_tax_curr,
prd_total_tax_amt,
prd_total_tax_amt_tax_curr,
prd_total_tax_amt_funcl_curr,
Rounding_Rule_Code,
unrounded_taxable_amt,
unrounded_tax_amt,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
tax_amt_funcl_curr,
taxable_amt_funcl_curr,
cal_tax_amt_funcl_curr,
tax_provider_id,
application_id,
internal_organization_id,
event_class_code,
entity_code,
trx_id,
rounding_level_code,
orig_tax_amt,
orig_taxable_amt,
orig_tax_amt_tax_curr,
orig_taxable_amt_tax_curr
FROM ZX_DETAIL_TAX_LINES_GT
WHERE (offset_link_to_tax_line_id IS NULL
OR
(offset_link_to_tax_line_id IS NOT NULL AND
other_doc_source IN ('APPLIED_FROM', 'ADJUSTED'))) --bug8517610
AND trx_id = c_trx_id
AND application_id = c_application_id
AND event_class_code = c_event_class_code
AND entity_code = c_entity_code
ORDER BY
ledger_id,
application_id,
event_class_code,
entity_code,
trx_id,
tax_regime_code,
tax,
tax_status_code,
tax_rate_code,
tax_rate,
tax_rate_id,
tax_jurisdiction_code,
taxable_basis_formula,
tax_calculation_formula,
Tax_Amt_Included_Flag,
compounding_tax_flag,
historical_flag,
self_assessed_flag,
overridden_flag,
manually_entered_flag,
Copied_From_Other_Doc_Flag,
associated_child_frozen_flag,
tax_only_line_flag,
mrc_tax_line_flag,
reporting_only_flag,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
-- applied_to_application_id,
-- applied_to_event_class_code,
-- applied_to_entity_code,
-- applied_to_trx_id,
-- applied_to_line_id,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
legal_entity_id,
establishment_id,
TRUNC(currency_conversion_date),
currency_conversion_type,
currency_conversion_rate,
record_type_code;
/*g_currency_tbl.DELETE;
g_tax_curr_conv_rate_tbl.DELETE;*/
g_hdr_rounding_info_tbl.DELETE;
ELSIF (p_event_class_rec.tax_event_type_code = 'UPDATE' OR
p_event_class_rec.tax_event_type_code = 'OVERRIDE_TAX') THEN
process_tax_line_upd_override(
l_curr_hdr_grp_rec,
l_sum_unrnd_tax_amt,
l_sum_rnd_tax_amt,
l_sum_rnd_tax_curr,
l_sum_rnd_funcl_curr,
p_event_class_rec,
p_return_status,
p_error_buffer);
update_header_rounding_info(
l_tax_line_id_tbl(i),
l_tax_id_tbl(i),
l_rounding_rule_tbl(i),
l_min_acct_unit_tbl(i),
l_precision_tbl(i),
l_unrounded_tax_amt_tbl(i),
l_tax_amt_tbl(i),
l_tax_amt_tax_curr_tbl(i),
l_tax_amt_funcl_curr_tbl(i),
l_taxable_amt_tax_curr_tbl(i),
l_taxable_amt_funcl_curr_tbl(i),
l_tax_curr_conv_rate_tbl(i),
l_currency_conversion_rate_tbl(i),
l_prev_hdr_grp_rec,
l_curr_hdr_grp_rec,
l_same_tax,
l_sum_unrnd_tax_amt,
l_sum_rnd_tax_amt,
l_sum_rnd_tax_curr,
l_sum_rnd_funcl_curr,
l_ledger_id_tbl(i),
p_return_status,
p_error_buffer);
update_detail_tax_lines_gt(
p_min_acct_unit_tbl => l_min_acct_unit_tbl,
p_precision_tbl => l_precision_tbl,
p_tax_currency_code_tbl => l_tax_currency_code_tbl,
p_tax_curr_conv_rate_tbl => l_tax_curr_conv_rate_tbl,
p_tax_amt_tbl => l_tax_amt_tbl,
p_taxable_amt_tbl => l_taxable_amt_tbl,
p_tax_amt_tax_curr_tbl => l_tax_amt_tax_curr_tbl,
p_taxable_amt_tax_curr_tbl => l_taxable_amt_tax_curr_tbl,
p_tax_amt_funcl_curr_tbl => l_tax_amt_funcl_curr_tbl,
p_taxable_amt_funcl_curr_tbl => l_taxable_amt_funcl_curr_tbl,
p_prd_total_tax_amt_tbl => l_prd_total_tax_amt_tbl,
p_prd_tot_tax_amt_tax_curr_tbl => l_prd_tot_tax_amt_tax_curr_tbl,
p_prd_tot_tax_amt_fcl_curr_tbl => l_prd_tot_tax_amt_fcl_curr_tbl,
p_cal_tax_amt_funcl_curr_tbl => l_cal_tax_amt_funcl_curr_tbl,
p_orig_tax_amt_tax_curr_tbl => l_orig_tax_amt_tax_curr_tbl,
p_orig_taxable_amt_tax_cur_tbl => l_orig_taxable_amt_tax_cur_tbl,
p_tax_line_id_tbl => l_tax_line_id_tbl,
p_return_status => p_return_status,
p_error_buffer => p_error_buffer);
SELECT tax_line_id,
tax_rate,
tax_rate_id,
tax_calculation_formula,
ledger_id,
trx_currency_code,
Rounding_Level_Code,
currency_conversion_rate,
tax_amt,
taxable_amt,
cal_tax_amt,
unrounded_taxable_amt,
unrounded_tax_amt,
tax_amt_funcl_curr,
taxable_amt_funcl_curr,
cal_tax_amt_funcl_curr
FROM ZX_LINES
WHERE trx_id = c_trx_id AND
application_id = c_application_id AND
event_class_code = c_event_class_code AND
entity_code = c_entity_code AND
tax_provider_id IS NULL AND
offset_link_to_tax_line_id IS NULL AND
mrc_tax_line_flag = 'N';
update_zx_lines(
p_conversion_rate,
p_conversion_type,
p_conversion_date,
l_tax_amt_funcl_curr_tbl,
l_taxable_amt_funcl_curr_tbl,
l_cal_tax_amt_funcl_curr_tbl,
l_tax_line_id_tbl,
p_return_status,
p_error_buffer);
SELECT tax_line_id,
tax_regime_code,
tax,
tax_status_code,
tax_rate_code,
tax_rate,
tax_rate_id,
tax_jurisdiction_code,
taxable_basis_formula,
tax_calculation_formula,
Tax_Amt_Included_Flag,
compounding_tax_flag,
historical_flag,
self_assessed_flag,
overridden_flag,
Copied_From_Other_Doc_Flag,
associated_child_frozen_flag,
tax_only_line_flag,
manually_entered_flag,
mrc_tax_line_flag,
reporting_only_flag,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
--applied_to_application_id,
--applied_to_event_class_code,
--applied_to_entity_code,
--applied_to_trx_id,
--applied_to_line_id,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
ledger_id,
legal_entity_id,
establishment_id,
currency_conversion_date,
currency_conversion_type,
currency_conversion_rate,
record_type_code,
trx_currency_code,
Rounding_Level_Code,
tax_amt,
taxable_amt,
cal_tax_amt,
unrounded_taxable_amt,
unrounded_tax_amt,
tax_amt_funcl_curr,
taxable_amt_funcl_curr,
cal_tax_amt_funcl_curr
FROM ZX_LINES
WHERE trx_id = c_trx_id AND
application_id = c_application_id AND
event_class_code = c_event_class_code AND
entity_code = c_entity_code AND
tax_provider_id IS NULL AND
offset_link_to_tax_line_id IS NULL AND
mrc_tax_line_flag = 'N'
ORDER BY
tax_regime_code,
tax,
tax_status_code,
tax_rate_code,
tax_rate,
tax_rate_id,
tax_jurisdiction_code,
taxable_basis_formula,
tax_calculation_formula,
Tax_Amt_Included_Flag,
compounding_tax_flag,
historical_flag,
self_assessed_flag,
overridden_flag,
manually_entered_flag,
Copied_From_Other_Doc_Flag,
associated_child_frozen_flag,
tax_only_line_flag,
mrc_tax_line_flag,
reporting_only_flag,
applied_from_application_id,
applied_from_event_class_code,
applied_from_entity_code,
applied_from_trx_id,
applied_from_line_id,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
--applied_to_application_id,
--applied_to_event_class_code,
--applied_to_entity_code,
--applied_to_trx_id,
--applied_to_line_id,
tax_exemption_id,
tax_rate_before_exemption,
tax_rate_name_before_exemption,
exempt_rate_modifier,
exempt_certificate_number,
exempt_reason,
exempt_reason_code,
tax_exception_id,
tax_rate_before_exception,
tax_rate_name_before_exception,
exception_rate,
ledger_id,
legal_entity_id,
establishment_id,
TRUNC(currency_conversion_date),
currency_conversion_type,
currency_conversion_rate,
record_type_code;
g_hdr_rounding_curr_tbl.DELETE;
update_zx_lines(
p_conversion_rate,
p_conversion_type,
p_conversion_date,
l_tax_amt_funcl_curr_tbl,
l_taxable_amt_funcl_curr_tbl,
l_cal_tax_amt_funcl_curr_tbl,
l_tax_line_id_tbl,
p_return_status,
p_error_buffer);
SELECT default_rounding_level_code,
rounding_level_hier_1_code,
rounding_level_hier_2_code,
rounding_level_hier_3_code,
rounding_level_hier_4_code
FROM ZX_EVNT_CLS_MAPPINGS
WHERE APPLICATION_ID = c_application_id AND
ENTITY_CODE = c_entity_code AND
EVENT_CLASS_CODE = c_event_class_code;
SELECT 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,
trx_currency_code,
precision,
minimum_accountable_unit
FROM ZX_LINES_DET_FACTORS
WHERE 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;
SELECT rounding_rule_code,
minimum_accountable_unit,
tax_precision
FROM ZX_TAXES_B
WHERE TAX_ID = c_tax_id;
SELECT minimum_accountable_unit,
precision
FROM FND_CURRENCIES
WHERE currency_code = c_trx_currency_code;
SELECT
decode(povs.AP_Tax_Rounding_Rule,'U','UP','D','DOWN','N','NEAREST',NULL) tax_runding_rule
,decode(nvl(povs.Auto_Tax_Calc_Flag,'Y'),'N','N','Y') Auto_Tax_Calc_Flag
,povs.VAT_Code
,povs.VAT_Registration_Num
,DECODE(povs.Auto_Tax_Calc_Flag,
'L','LINE',
'H','HEADER',
'T','HEADER',
NULL) tax_rounding_level
,povs.amount_includes_tax_flag
FROM ap_supplier_sites_all povs
WHERE povs.vendor_id = c_account_id
AND povs.vendor_site_id = c_account_site_id;
SELECT
csu.Tax_Reference,
nvl(csu.Tax_Code,caa.tax_code) tax_code,
nvl(csu.Tax_Rounding_rule,caa.tax_rounding_rule) tax_rounding_rule,
nvl(csu.tax_header_level_flag, caa.tax_header_level_flag) tax_header_level_flag,
csu.Tax_Classification
FROM hz_cust_site_uses_all csu
,hz_cust_acct_sites cas
,hz_cust_accounts caa
WHERE csu.site_use_id = c_site_use_id
AND csu.cust_acct_site_id = c_account_site_id
AND csu.cust_acct_site_id = cas.cust_acct_site_id
AND cas.cust_account_id = caa.cust_account_id
AND caa.cust_account_id = c_account_id;
SELECT
tax_code,
tax_header_level_flag,
tax_rounding_rule
FROM hz_cust_accounts
WHERE cust_account_id = c_account_id;