[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* PROCEDURE Name: insert_row
*
+ This procedure insert fetched accounting info into zx_rep_actg_ext_t table
* and calculated tax/taxable amount into zx_rep_trx_jx_ext_t table
*
* @parameter: p_detail_tax_line_id_tbl
* @parameter: p_taxable_amt_tbl
* @parameter: p_taxable_amt_funcl_curr_tbl
* @parameter: p_tax_amt_tbl
* @parameter: p_tax_amt_funcl_curr_tbl
*
**/
PROCEDURE insert_row (
p_detail_tax_line_id_tbl IN ZX_EXTRACT_PKG.DETAIL_TAX_LINE_ID_TBL,
p_taxable_amt_tbl IN ZX_EXTRACT_PKG.TAXABLE_AMT_TBL,
p_taxable_amt_funcl_curr_tbl IN ZX_EXTRACT_PKG.TAXABLE_AMT_FUNCL_CURR_TBL,
p_tax_amt_tbl IN ZX_EXTRACT_PKG.TAX_AMT_TBL,
p_tax_amt_funcl_curr_tbl IN ZX_EXTRACT_PKG.TAX_AMT_FUNCL_CURR_TBL)
IS
count_tbl numtab;
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()+',
'insert_row API call ');
* Insert accounting info to TRL acct ext table
*/
FOR i in p_detail_tax_line_id_tbl.first..p_detail_tax_line_id_tbl.last LOOP
/* IF (g_level_procedure >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()+',
'For Loop : ');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()+',
'g_balancing_seg : ');
/* INSERT INTO ZX_REP_ACTG_EXT_T
(
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
actg_ext_line_id,
detail_tax_line_id,
accounting_date,
trx_taxable_account,
trx_taxable_account_desc,
trx_taxable_balancing_segment,
trx_taxable_balseg_desc,
trx_taxable_natural_account,
trx_taxable_natacct_seg_desc
)
VALUES
(
g_request_id,
g_user_id,
g_today,
g_user_id,
g_today,
g_login_id,
zx_rep_actg_ext_t_s.NEXTVAL,
p_detail_tax_line_id_tbl(i),
p_acct_date_tbl(i),
substrb(g_acct_all_tbl(p_ccid_tbl(i)),1,240),
g_acct_all_desc_tbl(p_ccid_tbl(i)),
g_bal_seg_tbl(p_ccid_tbl(i)),
g_bal_seg_desc_tbl(p_ccid_tbl(i)),
g_acct_seg_tbl(p_ccid_tbl(i)),
g_acct_seg_desc_tbl(p_ccid_tbl(i))
); */
* Insert Prorated amount into jx ext itf
*/
INSERT INTO zx_rep_trx_jx_ext_t (
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
detail_tax_line_ext_id,
detail_tax_line_id,
numeric1,
numeric2,
numeric3,
numeric4
)
VALUES (
g_request_id,
g_user_id,
g_today,
g_user_id,
g_today,
g_login_id,
zx_rep_trx_jx_ext_t_s.NEXTVAL,
p_detail_tax_line_id_tbl(i),
p_taxable_amt_tbl(i),
p_taxable_amt_funcl_curr_tbl(i),
p_tax_amt_tbl(i),
p_tax_amt_funcl_curr_tbl(i)
);
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()-',
'insert_row()-: ');
FND_LOG.STRING(g_level_procedure, 'ZX.TRL.zx_jg_extract.insert_row()-',
'insert_row(EXCEPTION)- ');
END insert_row;
SELECT precision,
minimum_accountable_unit,
decode(alc_ledger_type_code,'SOURCE','P',
'TARGET','R',
'NONE','N')
INTO l_func_precision,
l_func_min_account_unit,
l_sob_type
FROM gl_ledgers sob,
fnd_currencies curr
WHERE sob.ledger_id = p_trl_global_variables_rec.ledger_id
AND sob.currency_code = curr.currency_code;
p_detail_tax_line_id_tbl.delete(i);
p_trx_id_tbl.delete(i);
p_tax_line_id_tbl.delete(i);
p_trx_line_id_tbl.delete(i);
p_tax_dist_id_tbl.delete(i);
p_event_class_code_tbl.delete(i);
p_tax_dist_id_tbl.delete(i);
p_taxable_amt_tbl.delete(i);
p_tax_amt_tbl.delete(i);
p_tax_amt_funcl_curr_tbl.delete(i);
p_tax_rate_id_tbl.delete(i);
p_extract_source_ledger_tbl.delete(i);
p_ledger_id_tbl.delete(i);
l_detail_tax_line_id_tbl.delete(j);
l_taxable_amt_tbl.delete(j);
l_taxable_amt_funcl_curr_tbl.delete(j);
l_tax_amt_tbl.delete(j);
l_tax_amt_funcl_curr_tbl.delete(j);
l_ccid_tbl.delete(j);
l_acct_date_tbl.delete(j);
SELECT c_detail_tax_line_id detail_tax_line_id,
trx_line.code_combination_id,
header.default_effective_date accounting_date,
itf.taxable_amt,
itf.taxable_amt_funcl_curr,
itf.tax_amt,
itf.tax_amt_funcl_curr
FROM gl_je_headers header,
gl_je_lines trx_line,
gl_je_lines tax_line,
zx_rep_trx_detail_t itf
WHERE header.je_header_id = c_trx_id
AND tax_line.je_header_id = header.je_header_id
AND tax_line.je_line_num = c_tax_line_id
AND tax_line.je_header_id = trx_line.je_header_id
AND tax_line.tax_group_id = trx_line.tax_group_id
AND itf.detail_tax_line_id = c_detail_tax_line_id
AND NVL(trx_line.tax_line_flag,'N') <> 'Y';
SELECT c_detail_tax_line_id detail_tax_line_id,
ael.code_combination_id,
aeh.accounting_date,
zx_dist.taxable_amt,
zx_dist.taxable_amt_funcl_curr,
zx_dist.prd_tax_amt,
zx_dist.prd_tax_amt_funcl_curr
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.tax_line_id = c_tax_line_id
AND zx_dist.trx_line_id = c_trx_line_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST'
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND aeh.ae_header_id = ael.ae_header_id
AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
AND acs.program_code = asd.program_code
--AND asd.assignment_code = 'TAX_REPORTING_LEDGER_ACCTS'
AND asd.assignment_code = acs.assignment_code
AND asd.enabled_flag = 'Y'
AND acs.accounting_class_code = ael.accounting_class_code
FOR UPDATE;
SELECT c_detail_tax_line_id detail_tax_line_id,
ael.code_combination_id,
aeh.accounting_date,
zx_dist.taxable_amt,
zx_dist.taxable_amt_funcl_curr,
zx_dist.rec_nrec_tax_amt,
zx_dist.rec_nrec_tax_amt_funcl_curr
FROM zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE zx_dist.trx_id = c_trx_id
AND zx_dist.tax_line_id = c_tax_line_id
AND zx_dist.trx_line_id = c_trx_line_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST'
AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
AND zx_dist.rec_nrec_tax_dist_id = c_tax_dist_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND aeh.ae_header_id = ael.ae_header_id
AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
AND acs.program_code = asd.program_code
AND acs.assignment_code = asd.assignment_code
AND acs.program_owner_code = asd.program_owner_code
AND acs.assignment_owner_code = asd.assignment_owner_code
AND asd.enabled_flag = 'Y'
AND ael.ledger_id = c_ledger_id
AND acs.accounting_class_code = ael.accounting_class_code;
SELECT detail_tax_line_id,
actg_line_ccid code_combination_id,
accounting_date ,
0 taxable_amt, -- -1*zx_dist.taxable_amt taxable_amt,
0 taxable_amt_funcl_curr, -- -1*zx_dist.taxable_amt_funcl_curr taxable_amt_funcl_curr,
0 tax_amt, -- -1*zx_dist.prd_tax_amt tax_amt,
0 tax_amt_funcl_curr -- -1*zx_dist.prd_tax_amt_funcl_curr tax_amt_funcl_curr
FROM zx_rep_actg_ext_t
WHERE detail_tax_line_id = c_detail_tax_line_id;
l_cur_aradj := 'SELECT
:c_detail_tax_line_id,
Decode(:c_taxable_total, 0, 0,
(nvl(adjtxdist.amount_cr,0)+nvl(-1*adjtxdist.amount_dr,0))/:c_taxable_total) percent,
(nvl(adjtxdist.taxable_entered_cr,0) - nvl(adjtxdist.taxable_entered_dr,0)) taxable_amount,
(nvl(adjtxdist.taxable_accounted_cr,0) - nvl(adjtxdist.taxable_accounted_dr,0)) acctd_taxable_amount,
curr.precision,
curr.minimum_accountable_unit
FROM
'|| l_ar_distributions ||' adjlndist,
'|| l_ar_distributions ||' adjtxdist,
'|| l_ar_adjustments ||' adj,
'|| l_ra_customer_trx ||' trx,
fnd_currencies curr
WHERE
adj.adjustment_id = :c_trx_id AND
adjlndist.source_table = ''ADJ'' AND
adjlndist.source_type IN (''ADJ'', ''FINCHRG'') AND
adjlndist.source_id = adj.adjustment_id AND
adjtxdist.source_table = ''ADJ'' AND
adjtxdist.source_type = ''TAX'' AND
adjtxdist.source_id = adj.adjustment_id AND
nvl(adjlndist.tax_link_id,0) = nvl(adjtxdist.tax_link_id,0) AND
adjlndist.line_id = :c_trx_line_id AND
adjtxdist.tax_code_id = :c_tax_rate_id AND
trx.customer_trx_id = adj.customer_trx_id AND
trx.invoice_currency_code = curr.currency_code ';
l_cur_aradj := 'SELECT
:c_detail_tax_line_id,
ael.code_combination_id ccid,
Decode(:c_taxable_total, 0, 0,
(Nvl(adjlndist.amount_dr,0)+Nvl(-1*adjlndist.amount_cr,0))/:c_taxable_total) percent,
Nvl(adjlndist.amount_dr,0)+Nvl(-1*adjlndist.amount_cr,0) taxable_amount,
Nvl(adjlndist.acctd_amount_dr,0)+Nvl(-1*adjlndist.acctd_amount_cr,0) acctd_taxable_amount,
curr.precision,
curr.minimum_accountable_unit,
aeh.accounting_date
FROM
'|| l_ar_distributions ||' adjlndist,
'|| l_ar_distributions ||' adjtxdist,
'|| l_ar_adjustments ||' adj,
'|| l_ra_customer_trx ||' trx,
fnd_currencies curr,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE
adj.adjustment_id = :c_trx_id AND
trx.customer_trx_id = adj.customer_trx_id AND
adjlndist.source_id = adj.adjustment_id AND
adjtxdist.source_id = adj.adjustment_id AND
adjlndist.source_table = ''ADJ'' AND
adjtxdist.source_table = ''ADJ'' AND
adjlndist.source_type IN (''ADJ'', ''FINCHRG'') AND
adjtxdist.source_type = ''TAX'' AND
adjlndist.tax_link_id = adjtxdist.tax_link_id AND
adjtxdist.tax_code_id = :c_tax_rate_id AND
lnk.application_id = 222 AND
lnk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'' AND
lnk.source_distribution_id_num_1 = adjlndist.line_id AND
lnk.ae_header_id = ael.ae_header_id AND
lnk.ae_line_num = ael.ae_line_num AND
ael.application_id = lnk.application_id AND
aeh.application_id = lnk.application_id AND
aeh.ae_header_id = ael.ae_header_id AND
trx.invoice_currency_code = curr.currency_code AND
acs.program_code = ''TAX_REPORTING_LEDGER_SALES'' AND
acs.program_code = asd.program_code AND
acs.assignment_code = asd.assignment_code AND
acs.program_owner_code = asd.program_owner_code AND
acs.assignment_owner_code = asd.assignment_owner_code AND
asd.enabled_flag = ''Y'' AND
acs.accounting_class_code = ael.accounting_class_code';
/* l_cur_aradj := 'SELECT
:c_detail_tax_line_id,
ael.code_combination_id ccid,
Decode(:c_taxable_total, 0, 0,
(Nvl(adjlndist.amount_dr,0)+Nvl(-1*adjlndist.amount_cr,0))/:c_taxable_total) percent,
Nvl(adjlndist.amount_dr,0)+Nvl(-1*adjlndist.amount_cr,0) taxable_amount,
Nvl(adjlndist.acctd_amount_dr,0)+Nvl(-1*adjlndist.acctd_amount_cr,0) acctd_taxable_amount,
curr.precision,
curr.minimum_accountable_unit,
aeh.accounting_date
FROM
'|| l_ar_distributions ||' adjlndist,
'|| l_ar_distributions ||' adjtxdist,
'|| l_ar_adjustments ||' adj,
'|| l_ra_customer_trx ||' trx,
fnd_currencies curr,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE
adj.adjustment_id = :c_trx_id AND
trx.customer_trx_id = adj.customer_trx_id AND
adjlndist.source_id = adj.adjustment_id AND
adjtxdist.source_id = adj.adjustment_id AND
adjlndist.source_table = ''ADJ'' AND
adjtxdist.source_table = ''ADJ'' AND
adjlndist.source_type IN (''ADJ'', ''FINCHRG'') AND
adjtxdist.source_type = ''TAX'' AND
adjlndist.tax_link_id = adjtxdist.tax_link_id AND
adjtxdist.tax_code_id = :c_tax_rate_id AND
lnk.application_id = 222 AND
lnk.source_distribution_type = ''AR_DISTRIBUTIONS'' AND
lnk.source_distribution_id_num_1 = zx_dist.trx_line_id AND
lnk.ae_header_id = ael.ae_header_id AND
lnk.ae_line_num = ael.ae_line_num AND
aeh.ae_header_id = ael.ae_header_id AND
trx.invoice_currency_code = curr.currency_code AND
acs.program_code = ''TAX_REPORTING_LEDGER'' AND
acs.program_code = asd.program_code AND
asd.assignment_code = ''TAX_REPORTING_LEDGER_ACCTS'' AND
asd.assignment_code = acs.assignment_code AND
asd.enabled_flag = ''Y'' AND
acs.accounting_class_code = ael.accounting_class_code';
l_cur_armisc := 'SELECT
:c_detail_tax_line_id,
ael.code_combination_id ccid,
Decode(:c_taxable_total, 0, 0, (Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0))/:c_taxable_total) percent,
Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0) taxable_amount,
Nvl(d.acctd_amount_dr,0)+Nvl(-1*d.acctd_amount_cr,0) acctd_taxable_amount,
curr.precision,
curr.minimum_accountable_unit,
aeh.accounting_date
FROM
'|| l_ar_cash_receipts ||' cr,
'|| l_ar_misc_cash_distributions ||' mcd,
'|| l_ar_distributions ||' d,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael,
fnd_currencies curr,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE
cr.cash_receipt_id = :c_trx_id AND
cr.cash_receipt_id = mcd.cash_receipt_id AND
d.source_table = ''MCD'' AND
d.source_id = mcd.misc_cash_distribution_id AND
d.source_type <> ''TAX'' AND
lnk.application_id = 222 AND
lnk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'' AND
lnk.source_distribution_id_num_1 = d.line_id AND
lnk.ae_header_id = ael.ae_header_id AND
lnk.ae_line_num = ael.ae_line_num AND
ael.application_id = lnk.application_id AND
aeh.application_id = lnk.application_id AND
aeh.ae_header_id = ael.ae_header_id AND
cr.currency_code = curr.currency_code AND
acs.program_code = ''TAX_REPORTING_LEDGER_SALES'' AND
acs.program_code = asd.program_code AND
acs.assignment_code = asd.assignment_code AND
acs.program_owner_code = asd.program_owner_code AND
acs.assignment_owner_code = asd.assignment_owner_code AND
asd.enabled_flag = ''Y'' AND
acs.accounting_class_code = ael.accounting_class_code';
/*l_cur_armisc := 'SELECT
:c_detail_tax_line_id,
ael.code_combination_id ccid,
Decode(:c_taxable_total, 0, 0, (Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0))/:c_taxable_total) percent,
Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0) taxable_amount,
Nvl(d.acctd_amount_dr,0)+Nvl(-1*d.acctd_amount_cr,0) acctd_taxable_amount,
curr.precision,
curr.minimum_accountable_unit,
aeh.accounting_date
FROM
'|| l_ar_cash_receipts ||' cr,
'|| l_ar_misc_cash_distributions ||' mcd,
'|| l_ar_distributions ||' d,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael,
fnd_currencies curr,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE
cr.cash_receipt_id = :c_trx_id AND
cr.cash_receipt_id = mcd.cash_receipt_id AND
d.source_table = ''MCD'' AND
d.source_id = mcd.misc_cash_distribution_id AND
d.source_type <> ''TAX'' AND
lnk.application_id = 222 AND
lnk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL'' AND
lnk.source_distribution_id_num_1 = zx_dist.trx_line_id AND
lnk.ae_header_id = ael.ae_header_id AND
lnk.ae_line_num = ael.ae_line_num AND
aeh.ae_header_id = ael.ae_header_id AND
cr.currency_code = curr.currency_code AND
acs.program_code = ''TAX_REPORTING_LEDGER_SALES'' AND
acs.program_code = asd.program_code AND
asd.assignment_code = ''TAX_REPORTING_LEDGER_ACCTS'' AND
asd.assignment_code = acs.assignment_code AND
asd.enabled_flag = ''Y'' AND
acs.accounting_class_code = ael.accounting_class_code';
l_cur_arra := 'SELECT
:c_detail_tax_line_id,
Decode(:c_taxable_total, 0, 0, (Nvl(dtax.amount_cr,0)+Nvl(-1*dtax.amount_dr,0))/:c_taxable_total) percent,
(nvl(DTAX.TAXABLE_ENTERED_CR,0) - nvl(DTAX.TAXABLE_ENTERED_DR,0)) taxable_amount,
(nvl(DTAX.TAXABLE_ACCOUNTED_CR,0) - nvl(DTAX.TAXABLE_ACCOUNTED_DR,0)) acctd_taxable_amount,
curr.precision,
curr.minimum_accountable_unit
FROM
'|| l_ar_distributions ||' dtax,
'|| l_ar_distributions ||' d,
'|| l_ar_receivable_applications ||' ra,
'|| l_ar_cash_receipts ||' cr,
fnd_currencies curr
WHERE
cr.cash_receipt_id = :c_trx_id AND
d.source_table = ''RA'' AND
d.line_id = :c_trx_line_id AND
dtax.source_table = ''RA'' AND
dtax.source_type = ''TAX'' and
dtax.source_id = d.source_id AND
Nvl(d.tax_link_id,0) = Nvl(dtax.tax_link_id,0) AND
ra.receivable_application_id = d.source_id AND
ra.receivable_application_id = dtax.source_id AND
ra.cash_receipt_id = cr.cash_receipt_id AND
curr.currency_code = cr.currency_code ';
/* l_cur_arra := 'SELECT
:c_detail_tax_line_id,
ael.code_combination_id ccid,
Decode(c_taxable_total, 0, 0, (Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0))/:c_taxable_total) percent,
Nvl(d.amount_dr,0)+Nvl(-1*d.amount_cr,0) taxable_amount,
Nvl(d.acctd_amount_dr,0)+Nvl(-1*d.acctd_amount_cr,0) acctd_taxable_amount,
curr.precision,
curr.minimum_accountable_unit,
aeh.accounting_date
FROM
'|| l_ar_distributions ||' dtax,
'|| l_ar_distributions ||' d,
'|| l_ar_receivable_applications ||' ra,
'|| l_ar_cash_receipts ||' cr,
fnd_currencies curr,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE
-- dtax.line_id = :c_acctg_dist_id AND
cr.cash_receipt_id = :c_trx_id AND
dtax.source_table = ''RA'' AND
d.source_table = ''RA'' AND
d.source_id = dtax.source_id AND
d.source_type <> ''TAX'' and
dtax.source_type = ''TAX'' and
(d.tax_link_id = -1 OR Nvl(d.tax_link_id,0) = Nvl(dtax.tax_link_id,0)) AND
ra.receivable_application_id = dtax.source_id AND
cr.cash_receipt_id = ra.cash_receipt_id AND
lnk.application_id = 222 AND
lnk.source_distribution_type = ''AR_DISTRIBUTIONS'' AND
lnk.source_distribution_id_num_1 = zx_dist.trx_line_id AND
lnk.ae_header_id = ael.ae_header_id AND
lnk.ae_line_num = ael.ae_line_num AND
ael.application_id = lnk.application_id AND
aeh.application_id = lnk.application_id AND
aeh.ae_header_id = ael.ae_header_id AND
cr.currency_code = curr.currency_code AND
acs.program_code = ''TAX_REPORTING_LEDGER'' AND
acs.program_code = asd.program_code AND
acs.assignment_code = asd.assignment_code AND
acs.program_owner_code = asd.program_owner_code AND
acs.assignment_owner_code = asd.assignment_owner_code AND
asd.enabled_flag = ''Y'' AND
acs.accounting_class_code = ael.accounting_class_code';
SELECT detail_tax_line_id,
trx_id,
tax_line_id,
trx_line_id,
actg_source_id,
event_class_code,
taxable_amt,
taxable_amt_funcl_curr,
tax_amt,
tax_amt_funcl_curr,
tax_rate_id,
extract_source_ledger,
ledger_id
FROM zx_rep_trx_detail_t
WHERE request_id = c_request_id
AND extract_source_ledger = c_source_ledger;
insert_row(p_detail_tax_line_id_tbl,
l_taxable_amt_tbl,
l_taxable_amt_funcl_curr_tbl,
l_tax_amt_tbl,
l_tax_amt_funcl_curr_tbl,
l_ccid_tbl,
l_acct_date_tbl);
INSERT INTO zx_rep_trx_jx_ext_t (
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
detail_tax_line_ext_id,
detail_tax_line_id,
numeric1,
numeric2,
numeric3,
numeric4
)
SELECT
g_request_id,
g_user_id,
g_today,
g_user_id,
g_today,
g_login_id,
zx_rep_trx_jx_ext_t_s.NEXTVAL,
detail_tax_line_id,
taxable_amt,
taxable_amt_funcl_curr,
tax_amt,
tax_amt_funcl_curr
FROM (
SELECT itf.detail_tax_line_id,
itf.taxable_amt,
itf.taxable_amt_funcl_curr,
itf.tax_amt,
itf.tax_amt_funcl_curr
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = g_request_id
and itf.extract_source_ledger = 'GL'
and itf.application_id = 101
and itf.entity_code = 'GL_JE_LINES'
);
insert_row(l_detail_tax_line_id_tbl,
l_taxable_amt_tbl,
l_taxable_amt_funcl_curr_tbl,
l_tax_amt_tbl,
l_tax_amt_funcl_curr_tbl,
l_ccid_tbl,
l_acct_date_tbl);
INSERT INTO zx_rep_trx_jx_ext_t (
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
detail_tax_line_ext_id,
detail_tax_line_id,
numeric1,
numeric2,
numeric3,
numeric4
)
SELECT
g_request_id,
g_user_id,
g_today,
g_user_id,
g_today,
g_login_id,
zx_rep_trx_jx_ext_t_s.NEXTVAL,
detail_tax_line_id,
CASE WHEN tax_line_change= 1 OR (NVL(reverse_flag,'N') = 'Y' AND recoverable_flag = 'N') THEN taxable_amt
ELSE 0
END,
CASE WHEN tax_line_change= 1 OR (NVL(reverse_flag,'N') = 'Y' AND recoverable_flag = 'N') THEN taxable_amt_funcl_curr
ELSE 0
END,
rec_nrec_tax_amt,
rec_nrec_tax_amt_funcl_curr
FROM (
SELECT /*+ leading(trl_tmp) parallel(trl_tmp) use_nl(trl_tmp zx_dist lnk) */
trl_tmp.detail_tax_line_id,
ael.code_combination_id,
aeh.accounting_date,
zx_dist.rec_nrec_tax_amt,
NVL(zx_dist.rec_nrec_tax_amt_funcl_curr,zx_dist.rec_nrec_tax_amt) rec_nrec_tax_amt_funcl_curr,
zx_dist.taxable_amt,
NVL(zx_dist.taxable_amt_funcl_curr,zx_dist.taxable_amt) taxable_amt_funcl_curr,
zx_dist.reverse_flag,
zx_dist.recoverable_flag,
RANK() OVER (PARTITION BY zx_dist.trx_id,
zx_dist.trx_line_id,
zx_dist.trx_line_dist_id,
zx_dist.tax_rate_id
ORDER BY NVL(zx_dist.reverse_flag,'N'),
NVL(zx_dist.RECOVERABLE_FLAG,'N'),
zx_dist.rec_nrec_tax_dist_id,
trl_tmp.detail_tax_line_id
) AS tax_line_change
FROM zx_rep_trx_detail_t trl_tmp,
zx_rep_actg_ext_t act,
zx_rec_nrec_dist zx_dist,
xla_distribution_links lnk,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE trl_tmp.request_id = g_request_id
AND trl_tmp.extract_source_ledger = 'AP'
AND trl_tmp.entity_code = 'AP_INVOICES'
AND trl_tmp.detail_tax_line_id = act.detail_tax_line_id
AND zx_dist.application_id = trl_tmp.application_id
AND zx_dist.entity_code = trl_tmp.entity_code
AND zx_dist.event_class_code = trl_tmp.event_class_code
AND zx_dist.trx_id = trl_tmp.trx_id
AND zx_dist.trx_line_id = trl_tmp.trx_line_id
AND zx_dist.tax_line_id = trl_tmp.tax_line_id
AND zx_dist.rec_nrec_tax_dist_id = trl_tmp.actg_source_id
-- AND lnk.source_distribution_id_num_1 = zx_dist.trx_line_dist_id
AND lnk.tax_rec_nrec_dist_ref_id = zx_dist.rec_nrec_tax_dist_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_INV_DIST'
AND lnk.ae_header_id = act.actg_header_id
AND lnk.event_id = act.actg_event_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND ael.application_id = lnk.application_id
--AND ael.ledger_id = trl_tmp.ledger_id
AND ael.ledger_id = g_ledger_id
AND aeh.application_id = lnk.application_id
AND aeh.event_id = lnk.event_id
AND aeh.ae_header_id = ael.ae_header_id
AND acs.accounting_class_code = ael.accounting_class_code
AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
AND acs.program_owner_code = asd.program_owner_code
AND acs.program_code = asd.program_code
AND acs.assignment_owner_code = asd.assignment_owner_code
AND acs.assignment_code = asd.assignment_code
AND asd.enabled_flag = 'Y'
UNION ALL
SELECT /*+ leading(trl_tmp) parallel(trl_tmp) use_nl(trl_tmp zx_dist lnk) */
trl_tmp.detail_tax_line_id,
ael.code_combination_id,
aeh.accounting_date,
zx_dist.rec_nrec_tax_amt,
NVL(zx_dist.rec_nrec_tax_amt_funcl_curr,zx_dist.rec_nrec_tax_amt) rec_nrec_tax_amt_funcl_curr,
zx_dist.taxable_amt,
NVL(zx_dist.taxable_amt_funcl_curr,zx_dist.taxable_amt) taxable_amt_funcl_curr,
zx_dist.reverse_flag,
zx_dist.recoverable_flag,
RANK() OVER (PARTITION BY zx_dist.trx_id,
zx_dist.trx_line_id,
zx_dist.trx_line_dist_id,
zx_dist.tax_rate_id
ORDER BY NVL(zx_dist.reverse_flag,'N'),
NVL(zx_dist.RECOVERABLE_FLAG,'N'),
zx_dist.rec_nrec_tax_dist_id,
trl_tmp.detail_tax_line_id
) AS tax_line_change
FROM zx_rep_trx_detail_t trl_tmp,
zx_rep_actg_ext_t act,
zx_rec_nrec_dist zx_dist,
ap_invoice_distributions_all ap_dist,
ap_prepay_app_dists pre_dist,
xla_ae_headers aeh,
xla_distribution_links lnk,
xla_ae_lines ael,
xla_acct_class_assgns acs,
xla_assignment_defns_b asd
WHERE trl_tmp.request_id = g_request_id
AND trl_tmp.extract_source_ledger = 'AP'
AND trl_tmp.entity_code = 'AP_INVOICES'
AND trl_tmp.event_class_code = 'STANDARD INVOICES'
AND trl_tmp.detail_tax_line_id = act.detail_tax_line_id
AND zx_dist.application_id = trl_tmp.application_id
AND zx_dist.entity_code = trl_tmp.entity_code
AND zx_dist.event_class_code = trl_tmp.event_class_code
AND zx_dist.trx_id = trl_tmp.trx_id
AND zx_dist.trx_line_id = trl_tmp.trx_line_id
AND zx_dist.tax_line_id = trl_tmp.tax_line_id
AND zx_dist.trx_level_type = 'LINE'
AND zx_dist.rec_nrec_tax_dist_id = trl_tmp.actg_source_id
AND ap_dist.invoice_id = zx_dist.trx_id
AND ap_dist.line_type_lookup_code = 'ITEM'
AND pre_dist.prepay_app_distribution_id = zx_dist.trx_line_dist_id
AND pre_dist.prepay_dist_lookup_code = 'PREPAY APPL'
AND pre_dist.invoice_distribution_id = ap_dist.invoice_distribution_id
AND lnk.source_distribution_id_num_1 = pre_dist.prepay_app_dist_id
AND lnk.application_id = 200
AND lnk.source_distribution_type = 'AP_PREPAY'
AND lnk.ae_header_id = act.actg_header_id
AND lnk.event_id = act.actg_event_id
AND lnk.ae_header_id = ael.ae_header_id
AND lnk.ae_line_num = ael.ae_line_num
AND lnk.application_id = zx_dist.application_id
AND ael.application_id = lnk.application_id
--AND ael.ledger_id = trl_tmp.ledger_id
AND ael.ledger_id = g_ledger_id
AND ael.accounting_class_code <> 'LIABILITY'
AND aeh.application_id = lnk.application_id
AND aeh.event_id = lnk.event_id
AND aeh.ae_header_id = ael.ae_header_id
AND acs.accounting_class_code = ael.accounting_class_code
AND acs.program_code = 'TAX_REP_LEDGER_PROCUREMENT'
AND acs.program_owner_code = asd.program_owner_code
AND acs.program_code = asd.program_code
AND acs.assignment_owner_code = asd.assignment_owner_code
AND asd.assignment_code = acs.assignment_code
AND asd.enabled_flag = 'Y'
);
'insert_row API call ');
insert_row(l_detail_tax_line_id_tbl,
l_taxable_amt_tbl,
l_taxable_amt_funcl_curr_tbl,
l_tax_amt_tbl,
l_tax_amt_funcl_curr_tbl);