[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
updated jai_ap_tdS_inv_taxes and jai_ap_tds_thhold_grps
withe the rounded values. This is done in procedure
process_tds_at_inv_validate and maintain_thhold_grps.
In generate_tds_invoices derived the logic for rounding.
Added conditions in queries for fetching the taxable
amount in procedure process_threshold_transition and
process_threshold_rollback. Added the parameters p_creation_date
or pd_creation_date wherever required.
Search for bug number for complete fix.
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
--------------------------------------------------------------------------- */
procedure process_prepayment
(
p_invoice_id in number,
p_invoice_distribution_id in number,
p_prepay_distribution_id in number,
p_parent_reversal_id in number,
p_prepay_amount in number,
p_vendor_id in number,
p_vendor_site_id in number,
p_accounting_date in date,
p_invoice_currency_code in varchar2,
p_exchange_rate in number,
p_set_of_books_id in number,
p_org_id in number,
-- Bug 5722028. Added by CSahoo
p_creation_date in date,
p_process_flag out nocopy varchar2,
p_process_message out nocopy varchar2,
p_codepath in out nocopy varchar2
)
is
begin
p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_prepayemnts_pkg.process_prepayment', 'START'); /* 1 */
select sum( decode(tds_applicable_flag , 'Y', application_amount*p_exchange_rate, 0) ) tds_taxable_basis,
sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount,
sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
select tds_threshold_grp_id,
tds_threshold_trx_id_apply
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and tds_threshold_grp_id is not null;
select wct_threshold_trx_id_apply
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and wct_threshold_trx_id_apply is not null;
select essi_threshold_trx_id_apply
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and essi_threshold_trx_id_apply is not null;
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select tds_tax_id_prepay
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_prepay_distribution_id
and tds_tax_id_prepay is not null
and tds_applicable_flag = 'Y';
select wct_tax_id_prepay
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_prepay_distribution_id
and wct_tax_id_prepay is not null
and wct_applicable_flag = 'Y';
select essi_tax_id_prepay
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_prepay_distribution_id
and essi_tax_id_prepay is not null
and essi_applicable_flag = 'Y';
select invoice_to_tds_authority_num,
invoice_to_vendor_num
from jai_ap_tds_thhold_trxs
where threshold_trx_id = p_threshold_trx_id;
update JAI_AP_TDS_INVOICES
set amt_reversed = nvl(amt_reversed, 0) - r_get_total_prepayment_tax.tds_amount,
amt_applied = nvl(amt_applied, 0) - abs(p_prepay_amount)
where invoice_id = p_invoice_id;
/* Update the threshold group */
ln_threshold_grp_id:= r_tds_details_apply.tds_threshold_grp_id;
update jai_ap_tds_prepayments
set tds_threshold_trx_id_unapply = ln_threshold_trx_id_tds
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_parent_distribution_id
and tds_threshold_trx_id_apply is not null
and tds_applicable_flag = 'Y';
update jai_ap_tds_prepayments
set wct_threshold_trx_id_unapply = ln_threshold_trx_id_wct
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_parent_distribution_id
and wct_threshold_trx_id_apply is not null
and wct_applicable_flag = 'Y';
update jai_ap_tds_prepayments
set essi_threshold_trx_id_unapply = ln_threshold_trx_id_essi
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_parent_distribution_id
and essi_threshold_trx_id_apply is not null
and essi_applicable_flag = 'Y';
/* update the unapply flag for all */
update jai_ap_tds_prepayments
set unapply_flag = 'Y'
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_parent_distribution_id;
select invoice_distribution_id, amount
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and invoice_distribution_id <> p_prepay_distribution_id
and section_type = cp_section_type;
select sum(application_amount)
from jai_ap_tds_prepayments
where invoice_distribution_id = p_invoice_distribution_id
and nvl(unapply_flag, 'N') <> 'Y';
/* Insert into jai_ap_tds_prepayments */
insert into jai_ap_tds_prepayments
(
tds_prepayment_id ,
invoice_id ,
invoice_distribution_id_prepay ,
invoice_distribution_id ,
application_amount ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
(
jai_ap_tds_prepayments_s.nextval ,
p_invoice_id ,
p_invoice_distribution_id ,
cur_si_distributions_rec.invoice_distribution_id ,
ln_application_amount ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
select section_type,
actual_section_code section_code,
actual_tax_id tax_id
from jai_ap_tds_inv_taxes
where invoice_distribution_id = p_prepay_distribution_id
and actual_tax_id is not null;
select section_type,
nvl(actual_section_code, default_section_code) section_code,
nvl(actual_tax_id, default_tax_id) tax_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id;
select tds_prepayment_id,
invoice_distribution_id
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
select 'N'
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and nvl(match_status_flag, 'N') <> 'A';
/* Update jai_ap_tds_prepayments */
update jai_ap_tds_prepayments
set application_basis = lv_application_basis ,
tds_section_code_prepay = lv_tds_section_code_prepay ,
tds_section_code_other = lv_tds_section_code_other ,
tds_tax_id_prepay = ln_tds_tax_id_prepay ,
tds_tax_id_other = ln_tds_tax_id_other ,
tds_applicable_flag = lv_tds_applicable_flag ,
wct_tax_id_prepay = ln_wct_tax_id_prepay ,
wct_tax_id_other = ln_wct_tax_id_other ,
wct_applicable_flag = lv_wct_applicable_flag ,
essi_tax_id_prepay = ln_essi_tax_id_prepay ,
essi_tax_id_other = ln_essi_tax_id_other ,
essi_applicable_flag = lv_essi_applicable_flag
where tds_prepayment_id = cur_rec_pp_allocations.tds_prepayment_id;
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select tds_prepayment_id,
application_amount,
application_basis,
decode(tds_applicable_flag, 'Y',
decode(application_basis, 'STANDARD INVOICE', tds_tax_id_other, tds_tax_id_prepay),
null) tds_tax_id,
decode(wct_applicable_flag, 'Y',
decode(application_basis, 'STANDARD INVOICE', wct_tax_id_other, wct_tax_id_prepay),
null) wct_tax_id,
decode(essi_applicable_flag, 'Y',
decode(application_basis, 'STANDARD INVOICE', essi_tax_id_other, essi_tax_id_prepay),
null) essi_tax_id
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
select nvl(tax_rate, 0) tax_rate
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
select threshold_grp_id,
actual_tax_id
from jai_ap_tds_inv_taxes
where invoice_distribution_id = p_prepay_distribution_id
and section_type = cp_section_type; --rchandan for bug#4428980
select actual_tax_id
from jai_ap_tds_inv_taxes
where invoice_distribution_id = p_prepay_distribution_id
and section_type = p_section_type;
select nvl(current_threshold_slab_id, 0) current_threshold_slab_id
from jai_ap_tds_thhold_grps
where threshold_grp_id = p_threshold_grp_id;
select invoice_num
from ap_invoices_all
where invoice_id in
( select invoice_id
from jai_ap_tds_inv_taxes /* ap_invoice_distributions not used for mutation problem */
where invoice_distribution_id = p_invoice_distribution_id);
select sum( decode(tds_applicable_flag , 'Y', application_amount*p_exchange_rate, 0) ) tds_taxable_basis,
sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount,
sum( decode(wct_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) wct_taxable_basis,
sum( decode(wct_applicable_flag, 'Y', wct_application_amount, 0) ) wct_amount,
sum( decode(essi_applicable_flag, 'Y', application_amount*p_exchange_rate, 0) ) essi_taxable_basis,
sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
select threshold_trx_id
from jai_ap_tds_inv_taxes
where invoice_distribution_id = p_prepay_distribution_id
-- Bug 4754213. Added by Lakshmi Gopalsami
and section_type = 'TDS_SECTION';
select sum(tds_application_amount)
from jai_ap_tds_prepayments jatp
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and tds_applicable_flag = 'Y'
and exists (select '1'
from jai_ap_tds_inv_taxes
where invoice_distribution_id = jatp.invoice_distribution_id
-- Bug 4754213. Added by Lakshmi Gopalsami
and section_type = 'TDS_SECTION'
and threshold_trx_id is not null
);
/* update the tax amount for the prepayements */
for cur_rec in c_jai_ap_tds_prepayments(p_invoice_id, p_invoice_distribution_id)
loop
if lv_application_basis is null then
lv_application_basis := cur_rec.application_basis;
update jai_ap_tds_prepayments
set tds_application_amount = ln_tds_tmp_amt, -- Bug 5722028
calc_tds_appln_amt = cur_rec.application_amount * ln_exchange_rate * (r_ja_in_tax_codes.tax_rate/100)
where tds_prepayment_id = cur_rec.tds_prepayment_id;
update jai_ap_tds_prepayments
set wct_application_amount = ln_tds_tmp_amt, -- Bug 5722028
calc_wct_appln_amt = cur_rec.application_amount * ln_exchange_rate * (r_ja_in_tax_codes.tax_rate/100)
where tds_prepayment_id = cur_rec.tds_prepayment_id;
update jai_ap_tds_prepayments
set essi_application_amount = ln_tds_tmp_amt, --Bug 5722028
calc_essi_appln_amt = cur_rec.application_amount * ln_exchange_rate * (r_ja_in_tax_codes.tax_rate/100)
where tds_prepayment_id = cur_rec.tds_prepayment_id;
/* update the threshold with the tds amount that will be impacted because of this application */
jai_ap_tds_generation_pkg.maintain_thhold_grps
(
p_threshold_grp_id => ln_threshold_grp_id,
p_trx_invoice_apply_amount => r_get_total_prepayment_tax.tds_taxable_basis,
p_tds_event => 'PREPAYMENT APPLICATION',
p_invoice_id => p_invoice_id,
p_invoice_distribution_id => p_invoice_distribution_id,
p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
-- Bug 5722028. Added by Lakshmi Gopalsami
p_creation_Date => p_creation_date,
p_process_flag => p_process_flag,
P_process_message => p_process_message,
p_codepath => p_codepath
);
update jai_ap_tds_prepayments
set tds_threshold_grp_id = ln_threshold_grp_id
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and tds_applicable_flag = 'Y';
update JAI_AP_TDS_INVOICES
set amt_reversed = nvl(amt_reversed, 0) + r_get_total_prepayment_tax.tds_amount,
amt_applied = nvl(amt_applied, 0) + abs(p_prepay_amount)
where invoice_id = p_invoice_id;
/* Update the threshold group */
jai_ap_tds_generation_pkg.maintain_thhold_grps
(
p_threshold_grp_id => ln_threshold_grp_id,
/* Bug 4522507. Added by Lakshmi Gopalsami
Commented the following and added different assginment
p_trx_tax_paid => (-1 * r_get_total_prepayment_tax.tds_amount), */
p_trx_tax_paid => (-1 * ln_amt_tds_inv_generated_si),
p_tds_event => 'PREPAYMENT APPLICATION',
p_invoice_id => p_invoice_id,
p_invoice_distribution_id => p_invoice_distribution_id,
p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
-- Bug 5722028. Added by CSahoo
p_creation_date => p_creation_date,
p_process_flag => p_process_flag,
P_process_message => p_process_message,
p_codepath => p_codepath
);
/* Update jai_ap_tds_prepayments with threshold_trx_id_apply*/
update jai_ap_tds_prepayments
set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and tds_applicable_flag = 'Y';
update jai_ap_tds_prepayments
set wct_threshold_trx_id_apply = ln_threshold_trx_id_wct
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and wct_applicable_flag = 'Y';
update jai_ap_tds_prepayments
set essi_threshold_trx_id_apply = ln_threshold_trx_id_essi
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and essi_applicable_flag = 'Y';
p_last_updated_by in number,
p_last_update_date in date,
p_created_by in number,
p_creation_date in date,
p_org_id in number,
p_process_flag out nocopy varchar2,
p_process_message out nocopy varchar2
)
is
cursor c_tds_count(p_invoice_id number, p_source_attribute varchar2) is
select count(1)
from JAI_AP_TDS_INVOICES
where invoice_id = p_invoice_id
and source_attribute = p_source_attribute;
select count(1)
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and section_type = p_section_type; --rchandan for bug#4428980
1. ja_in_prepay_insert_trg
2. ja_in_prepay_insert_wct_trg
3. ja_in_prepay_insert_wct1_trg
*/
open c_tds_count(p_invoice_id, 'ATTRIBUTE1');
'To Insert Prepayment Distributions',
'',
FALSE,
p_invoice_id,
p_invoice_distribution_id,
abs(p_amount),
p_last_updated_by,
p_last_update_date,
p_created_by ,
p_creation_date,
p_org_id,
p_prepay_distribution_id,
'I',
'ATTRIBUTE1'
);
p_last_updated_by,
p_last_update_date,
p_created_by ,
p_creation_date,
p_org_id,
p_prepay_distribution_id,
p_invoice_distribution_id,
'ATTRIBUTE1'
);
'To Insert Prepayment Distributions',
'',
FALSE,
p_invoice_id,
p_invoice_distribution_id,
abs(p_amount),
p_last_updated_by,
p_last_update_date,
p_created_by ,
p_creation_date,
p_org_id,
p_prepay_distribution_id,
'I',
'ATTRIBUTE2'
);
p_last_updated_by,
p_last_update_date,
p_created_by ,
p_creation_date,
p_org_id,
p_prepay_distribution_id,
p_invoice_distribution_id,
'ATTRIBUTE2'
);
'To Insert Prepayment Distributions',
'',
FALSE,
p_invoice_id,
p_invoice_distribution_id,
abs(p_amount),
p_last_updated_by,
p_last_update_date,
p_created_by ,
p_creation_date,
p_org_id,
p_prepay_distribution_id,
'I',
'ATTRIBUTE3'
);
p_last_updated_by,
p_last_update_date,
p_created_by ,
p_creation_date,
p_org_id,
p_prepay_distribution_id,
p_invoice_distribution_id,
'ATTRIBUTE3'
);