[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select threshold_trx_id,
threshold_grp_id,
threshold_hdr_id, --Added by Sanjikum for Bug#5131075(4718907)
tax_category_id,
tds_section_code,
taxable_amount,
tax_amount,
invoice_to_tds_authority_id,
invoice_to_vendor_id,
tds_event, -- Added by Jia for FP Bug#7312295
calc_tax_amount -- Bug 5751783
from jai_ap_tds_thhold_trxs
where invoice_id = p_invoice_id
and (tds_event = 'INVOICE VALIDATE' or
tds_event like 'THRESHOLD TRANSITION%' -- Added by ChongLei for DTC Bug#13359892
--or tds_event = 'SURCHARGE_CALCULATE' --Bug 7312295 - Added condition 'or tds_event = 'SURCHARGE_CALCULATE' --Commented Surcharge_calcualte by Chong for bug#16414088
) --surcharge_calcualte should be processed in thurcharge rollback procedure. 20130319
and tds_rollbacked is NULL -- Added by ChongLei for DTC Bug#13359892
order by threshold_trx_id; -- Added by Jia for FP Bug#7312295
select vendor_id, vendor_site_id, tax_rate
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
SELECT LTRIM(UPPER(lookup_type),'JAI_') section_type
FROM ja_lookups
WHERE lookup_code = c_section_code
AND lookup_type IN(
SELECT DISTINCT 'JAI_' || b.lookup_code
FROM ja_lookups b
WHERE b.lookup_type = 'JAI_AP_TDS_SECTION_TYPES'
);
SELECT NVL(SUM(trx_invoice_amount),0) trx_invoice_amount
FROM jai_ap_tds_thgrp_audits
WHERE invoice_id = p_inv_id
AND threshold_grp_id = p_threshold_grp_id
AND tds_event = 'INVOICE VALIDATE'
AND trx_invoice_amount IS NOT NULL
ORDER BY threshold_grp_audit_id DESC;
select invoice_id,
cancelled_date,
payment_status_flag,
invoice_amount,
set_of_books_id,
invoice_num,
org_id
from ap_invoices_all
where invoice_id = p_invoice_id;
select set_of_books_id, invoice_currency_code, exchange_rate
from ap_invoices_all
where invoice_id = p_invoice_id;
select threshold_grp_id,
sum(amount) taxable_amount,
sum(tax_amount) tax_amount
from jai_ap_tds_inv_taxes jtdsi
where invoice_id = p_invoice_id
and section_type = cp_section_type --cp_section_type--rchandan for bug#4428980
and threshold_grp_id is not null
and threshold_trx_id is null
-- Added by ChongLei for DTC Bug#13359892, Begin
-------------------------------------------------------------------------------
and not exists
(select jattt.invoice_to_tds_authority_id
from jai_ap_tds_thhold_trxs jattt
,ap_invoices_all aia
where jattt.threshold_trx_id in(
select max(threshold_trx_id)
from jai_ap_tds_thhold_trxs
where invoice_id = p_invoice_id
and tds_section_code = jtdsi.actual_section_code
and tds_event not like 'THRESHOLD ROLLBACK%'
and tds_event <> 'INVOICE CANCEL' -- Added by ChongLei for DTC Bug#13859847
group by tds_section_code)
and invoice_to_tds_authority_id = aia.invoice_id
and aia.cancelled_date is not NULL
)
-------------------------------------------------------------------------------
-- Added by ChongLei for DTC Bug#13359892, End
group by threshold_grp_id;
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select jattt.invoice_id,
jattt.tds_event,
jattt.invoice_to_tds_authority_id
from jai_ap_tds_inv_taxes jatit, jai_ap_tds_thhold_trxs jattt
where jatit.invoice_id = p_invoice_id
and jatit.default_cum_threshold_stage = 'BEFORE THRESHOLD'
and jatit.threshold_grp_id = jattt.threshold_grp_id
and jattt.tds_event like 'THRESHOLD%'
and not exists (select '1'
from jai_ap_tds_thhold_trxs
where invoice_id = jatit.invoice_id); /*to ensure that no TDS is deducted*/
SELECT accounting_date
FROM ap_invoice_distributions_all
WHERE invoice_id = cp_invoice_id
AND distribution_line_number = 1;
SELECT accounting_date
FROM ap_invoice_lines_all
WHERE invoice_id = cp_invoice_id
AND line_number = 1;
SELECT *
FROM jai_ap_tds_thhold_grps
WHERE threshold_grp_id = p_threshold_grp_id;
SELECT threshold_slab_id,
threshold_type_id,
from_amount,
to_amount,
tax_rate
FROM jai_ap_tds_thhold_slabs
WHERE threshold_hdr_id = p_threshold_hdr_id
AND threshold_type_id in
(SELECT threshold_type_id
FROM jai_ap_tds_thhold_types
WHERE threshold_hdr_id = p_threshold_hdr_id
AND threshold_type = p_threshold_type
AND trunc(sysdate) between from_date and
nvl(to_date, sysdate + 1))
AND from_amount <= p_amount
AND NVL(to_amount, p_amount) >= p_amount
ORDER BY from_amount asc;
ld_out_last_update_date date;
ld_out_last_update_date := null;
p_gl_date => ld_out_accounting_date, /* out date */--Updated by Chong for ZX investigation 20120816
P_Org_Id => r_ap_invoices_all.org_id);
P_last_updated_by => fnd_global.user_id,
P_last_update_login => fnd_global.login_id,
--P_set_of_books_id => r_ap_invoices_all.set_of_books_id ,
P_accounting_date => ld_accounting_date,
--P_period_name => lv_open_period ,
P_message_name => lv_out_message_name,
P_invoice_amount => ln_out_invoice_amount,
P_base_amount => ln_out_base_amount,
--P_tax_amount => ln_out_tax_amount ,
P_temp_cancelled_amount => ln_out_temp_cancelled_amount,
P_cancelled_by => ln_out_cancelled_by,
P_cancelled_amount => ln_out_cancelled_amount,
P_cancelled_date => ld_out_cancelled_date,
P_last_update_date => ld_out_last_update_date,
P_original_prepayment_amount => ln_out_original_prepay_amount,
--P_check_id => null ,
P_pay_curr_invoice_amount => ln_out_pay_curr_inv_amount,
P_Token => lv_token,
P_calling_sequence => 'India Localization - cancel TDS invoice');
Commented the tax_amount update */
update ap_invoices_all
set invoice_amount = ln_out_invoice_amount,
base_amount = ln_out_base_amount,
--tax_amount = ln_out_tax_amount ,
temp_cancelled_amount = ln_out_temp_cancelled_amount,
cancelled_by = ln_out_cancelled_by,
cancelled_amount = ln_out_cancelled_amount,
cancelled_date = ld_out_cancelled_date,
last_update_date = ld_out_last_update_date,
original_prepayment_amount = ln_out_original_prepay_amount,
pay_curr_invoice_amount = ln_out_pay_curr_inv_amount
where invoice_id = cur_rec.invoice_to_tds_authority_id;
ld_out_last_update_date := null;
p_gl_date => ld_out_accounting_date, /* out date */ ----Updated by Chong for ZX investigation 20120816
P_Org_Id => r_ap_invoices_all.org_id);
P_last_updated_by => fnd_global.user_id,
P_last_update_login => fnd_global.login_id,
--P_set_of_books_id => r_ap_invoices_all.set_of_books_id ,
P_accounting_date => ld_accounting_date,
--P_period_name => lv_open_period ,
P_message_name => lv_out_message_name,
P_invoice_amount => ln_out_invoice_amount,
P_base_amount => ln_out_base_amount,
--P_tax_amount => ln_out_tax_amount ,
P_temp_cancelled_amount => ln_out_temp_cancelled_amount,
P_cancelled_by => ln_out_cancelled_by,
P_cancelled_amount => ln_out_cancelled_amount,
P_cancelled_date => ld_out_cancelled_date,
P_last_update_date => ld_out_last_update_date,
P_original_prepayment_amount => ln_out_original_prepay_amount,
--P_check_id => null ,
P_pay_curr_invoice_amount => ln_out_pay_curr_inv_amount,
P_token => lv_token,
P_calling_sequence => 'India Localization - cancel TDS invoice');
Commented the tax_amount update */
update ap_invoices_all
set invoice_amount = ln_out_invoice_amount,
base_amount = ln_out_base_amount,
-- tax_amount = ln_out_tax_amount ,
temp_cancelled_amount = ln_out_temp_cancelled_amount,
cancelled_by = ln_out_cancelled_by,
cancelled_amount = ln_out_cancelled_amount,
cancelled_date = ld_out_cancelled_date,
last_update_date = ld_out_last_update_date,
original_prepayment_amount = ln_out_original_prepay_amount,
pay_curr_invoice_amount = ln_out_pay_curr_inv_amount
where invoice_id = cur_rec.invoice_to_vendor_id;
pv_section_type => lv_section_type, -- 'TDS_SECTION_TYPE', Updated by Chong.Lei for Bug#13768894 on 20120222
pv_section_code => cur_rec.tds_section_code,
pd_accounting_date => ld_accounting_date,
pv_tds_event => 'INVOICE CANCEL',
pn_threshold_grp_id => cur_rec.threshold_grp_id,
pv_tds_invoice_num => lv_invoice_to_tds_num,
pv_cm_invoice_num => lv_invoice_to_vendor_num,
pn_threshold_trx_id => ln_threshold_trx_id,
p_process_flag => lv_tds_credit_memo_flag,
p_process_message => lv_tds_credit_memo_message,
pd_creation_Date => sysdate,
pn_calc_tax_amount => 0);
/* Update the total tax amount for which Cancel invoice was raised */
end if; /* Credit memo to the supplier paid / not paid */
/* Update threshold for the cancel invoice amount */
ln_threshold_grp_id := cur_rec.threshold_grp_id; /*added by rchandan for bug#5131075(4947469)*/
/* insert into JAI_AP_TDS_INV_CANCELS */
lv_codepath := jai_general_pkg.plot_codepath(19, lv_codepath); /* 19 */
, 'Insert cancel: p_invoice_id' || p_invoice_id
);
insert into jai_ap_tds_inv_cancels
(tds_inv_cancel_id,
invoice_id,
threshold_grp_id,
cancel_amount,
parent_threshold_trx_id,
tds_section_code,
tax_category_id,
tds_invoice_flag,
tds_invoice_message,
tds_credit_memo_flag,
tds_credit_memo_message,
threshold_trx_id_cancel,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values
(jai_ap_tds_inv_cancels_s.nextval,
p_invoice_id,
cur_rec.threshold_grp_id,
cur_rec.taxable_amount,
cur_rec.threshold_trx_id,
cur_rec.tds_section_code,
cur_rec.tax_category_id,
lv_tds_invoice_flag,
lv_tds_invoice_message,
lv_tds_credit_memo_flag,
lv_tds_credit_memo_message,
ln_threshold_trx_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
/*update TDS_ROLLBACKED to Y for this transaction record*/
UPDATE JAI_AP_TDS_THHOLD_TRXS
SET TDS_ROLLBACKED = 'Y'
WHERE threshold_trx_id = cur_rec.threshold_trx_id;
for tds_id in (select invoice_to_tds_authority_id
from jai_ap_tds_thhold_trxs
where invoice_id = r_threshold_breach.invoice_id) loop
open c_ap_invoices_all(tds_id.invoice_to_tds_authority_id);
, 'Insert cancel 2: p_invoice_id' || p_invoice_id
);
insert into jai_ap_tds_inv_cancels
(tds_inv_cancel_id,
invoice_id,
threshold_grp_id,
cancel_amount,
parent_threshold_trx_id,
tds_invoice_flag,
tds_invoice_message,
tds_credit_memo_flag,
tds_credit_memo_message,
threshold_trx_id_cancel,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
values
(jai_ap_tds_inv_cancels_s.nextval,
p_invoice_id,
cur_rec.threshold_grp_id,
ln_taxable_amount,
null,
null,
null,
null,
null,
null,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);