[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
and also commented the update for tax_amount
(2)Also raised exception if P_TOKEN is not null
5. 23-Aug-2005 Bug4559756. Added by Lakshmi Gopalsami version 120.3
(1) Added org_id in cursor c_ap_invoices_all
(2) Fetched the same before calling ap_utilities_pkg
and passed the same the package call.
To get the period name and date.
6. 22-nov-2005 Bug 47541213. Added by Lakshmi Gopalsami
Changed JAI_TDS_SECTION to TDS_SECTION
7. 03/11/2006 Sanjikum for Bug#5131075, File Version 120.5
1) Changes are done for forward porting of bugs - 4718907, 5193852, 4947469
Dependency Due to this Bug
--------------------------
Yes, as Package spec is changed and there are multiple files changed as part of current
8. 03/05/2007 Bug 5722028. Added by CSahoo 120.6
Forward porting to R12.
passed parameter pd_creation_Date to generate_tdS_invoices
changed the value to tax_amount instead of calc_tax_amount.
Depedencies:
=============
jai_ap_tds_gen.pls - 120.5
jai_ap_tds_gen.plb - 120.19
jai_ap_tds_ppay.pls - 120.2
jai_ap_tds_ppay.plb - 120.5
jai_ap_tds_can.plb - 120.6
9. 14/05/2007 bduvarag for the Bug#5722028.
Removed redundant column names that were causing error
10. 08/June/2009 Bug 8475540
AP Package AP_CANCEL_SINGLE_INVOICE was called without setting MOAC Context
resulting in multiple records being fetched when a single row is expected
Added call to mo_global.set_policy_context
11. 17-Jul-2009 Bgowrava for Bug#8682951 , File Version 120.4.12000000.4
Changed the parameter ld_accounting_date to sysdate while calling process_threshold_rollback in the loop c_jai_ap_tds_inv_taxes.
12. 25-Aug-2009 Bug 8830302
Fetch Accounting Date from AP_INVOICE_LINES_ALL if distributions is not saved.
This will prevent failure during cancellation.
13. 07-Jan-2010 Jia for FP Bug#7312295, File Version 120.4.12000000.5
Issue: This is a forward port bug for the bug7252683.
Cancellation of the invoice breaching the surcharge threhsold does not cancel the surcharge invoice that
got created while the transition. this results in wrong surcharge calculation
Fixed: Added the column 'tds_event' to the cusor c_jai_ap_tds_thhold_trxs to pick the tds_even also for cancellation,
also ordered the result based on the threshold_trx_id. Checked the slabs after and before cancellation and
if it was different then cancelled the surcharge invoice else not cancelled it.
14. 13-Jun-2010 Xiao for Bug#7154864
commented the call to jai_ap_inv_tds_generation_pkg.process_threshold_rollback as the need to create
an RTN invoice no more exists after the changes wrt to this bug have been made.
15. 21-Apr-2011 amandali for bug 11896260
Cancellation of invoices should be avoided if the theshold is breached by another invoice and payment is made.
16. 20-Jan-2012 mmurtuza for bug 13561970
Description: TDS INVOICE GOT GENERATED WITH WRONG AMOUNT FOR CUMILATIVE THRESHOLD
Fix: uncommented the call to jai_ap_inv_tds_generation_pkg.process_threshold_rollback
17. 20-Jan-2012 mmurtuza for bug 13561970
Description: TDS INVOICE GOT GENERATED WITH WRONG AMOUNT FOR CUMILATIVE THRESHOLD
Fix: uncommented the call to jai_ap_inv_tds_generation_pkg.process_threshold_rollback
Added variable lv_threshold_type_before and passed as input while calling get_tds_threshold_slab and process_threshold_rollback
18. 20-Jun-2012 amandali for bug 14185477
Description: India Cancel TDS invoice concurrent erroring out
Fix: Modified the variable to capture the p_gl_Date in call ap_utilities_pkg.get_open_gl_date.
---------------------------------------------------------------------------- */
/***********************************************************************************************/
procedure process_invoice
(
errbuf out nocopy varchar2,
retcode out nocopy varchar2,
p_invoice_id in number
)
is
cursor c_jai_ap_tds_thhold_trxs (p_invoice_id number) is--rchandan for bug#4428980
select threshold_trx_id,
threshold_grp_id,
threshold_hdr_id, --Added by Sanjikum for Bug#5131075(4718907)
tax_id,
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 = 'SURCHARGE_CALCULATE') --Bug 7312295 - Added condition 'or tds_event = 'SURCHARGE_CALCULATE'
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 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,
actual_tax_id tax_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
group by threshold_grp_id, actual_tax_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_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_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;
/* 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 into jai_ap_tds_inv_cancels
(
tds_inv_cancel_id ,
invoice_id ,
threshold_grp_id ,
cancel_amount ,
parent_threshold_trx_id ,
tax_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.tax_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
);
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 into jai_ap_tds_inv_cancels
(
tds_inv_cancel_id ,
invoice_id ,
threshold_grp_id ,
cancel_amount ,
parent_threshold_trx_id ,
tax_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 ,
cur_rec.tax_id ,
null ,
null ,
null ,
null ,
null ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);