[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT invoice_id
INTO p_prepay_inv_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_prepay_inv_dist_id;
SELECT reversal_flag
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_inv_dist_id;
select tds_threshold_trx_id_apply, count(1)
from jai_ap_tds_prepayments
where invoice_id = cp_invoice_id
and invoice_distribution_id_prepay = cp_inv_dist_id
group by tds_threshold_trx_id_apply;
select count(1) from jai_ap_tds_thhold_trxs
where invoice_id = cp_invoice_id
and tds_event = 'INVOICE VALIDATE';
if p_event = 'INSERT' and nvl(ln_prepay_apply,0) > 0 and nvl(ln_prepay_apply_trx_id, 0) = 0 and ln_cnt_thrshold <> 0 then
/*added condition of ln_cnt_thrshold by mmurtuza for bug 13620923*/
p_process_flag := 'E';
select sum( decode(tds_applicable_flag , 'Y', application_amount, 0) )* nvl(p_exchange_rate,1) tds_taxable_basis,
sum( decode(tds_applicable_flag , 'Y', calc_tds_appln_amt, 0) ) tds_amount,
sum( decode(tds_applicable_flag , 'Y', tds_application_amount, 0) ) tds_amount_orig,
sum( decode(wct_applicable_flag, 'Y', application_amount, 0) )* nvl(p_exchange_rate,1) wct_taxable_basis,
sum( decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0) ) wct_amount,
sum( decode(wct_applicable_flag , 'Y', wct_application_amount, 0) ) wct_amount_orig,
sum( decode(essi_applicable_flag, 'Y', application_amount, 0) )* nvl(p_exchange_rate,1) essi_taxable_basis,
sum( decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0) ) essi_amount,
sum( decode(essi_applicable_flag, 'Y', essi_application_amount, 0) ) essi_amount_orig
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
SELECT get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
tds_section_code_prepay section_code,
tds_threshold_grp_id,
tds_threshold_trx_id_apply,
-- Bug 6363056
sum(decode(tds_applicable_flag , 'Y', application_amount, 0))* nvl(p_exchange_rate,1) tds_taxable_basis,
sum(decode(tds_applicable_flag , 'Y', calc_tds_appln_amt, 0)) tds_amount,
sum(decode(tds_applicable_flag , 'Y', tds_application_amount, 0)) tds_amount_orig,
sum(decode(wct_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) wct_taxable_basis,
sum(decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0)) wct_amount,
sum(decode(wct_applicable_flag , 'Y', wct_application_amount, 0)) wct_amount_orig,
sum(decode(essi_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) essi_taxable_basis,
sum(decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0)) essi_amount,
sum(decode(essi_applicable_flag , 'Y', essi_application_amount, 0)) essi_amount_orig
from jai_ap_tds_prepayments jatp
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and tds_threshold_grp_id is not null
and nvl(unapply_flag, 'N') <> 'Y' -- Bug 6363056
group BY
get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)),
tds_section_code_prepay,
tds_threshold_grp_id,
tds_threshold_trx_id_apply; /*Bug 9132694 - Added Group By clause to sum the tax amounts and create a single RTN reversal entry on unapplication*/
select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
tds_section_code_prepay section_code,
wct_threshold_trx_id_apply,
sum(decode(wct_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) wct_taxable_basis,
sum(decode(wct_applicable_flag, 'Y', calc_wct_appln_amt, 0)) wct_amount
from jai_ap_tds_prepayments jatp
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and wct_threshold_trx_id_apply is not NULL
and wct_threshold_trx_id_unapply IS NULL
GROUP BY
get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)),
tds_section_code_prepay,
wct_threshold_trx_id_apply
;
select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
tds_section_code_prepay section_code,
essi_threshold_trx_id_apply,
sum(decode(essi_applicable_flag, 'Y', application_amount, 0))* nvl(p_exchange_rate,1) essi_taxable_basis,
sum(decode(essi_applicable_flag, 'Y', calc_essi_appln_amt, 0)) essi_amount
from jai_ap_tds_prepayments jatp
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and essi_threshold_trx_id_apply is not NULL
and essi_threshold_trx_id_unapply IS NULL
GROUP BY
get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)),
tds_section_code_prepay,
essi_threshold_trx_id_apply
;
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,
/* Bug 5751783
* Pass the Prepayment application invoice_id for generating the
* prepayment unapplication
*/
invoice_id,
tax_id
from jai_ap_tds_thhold_trxs
where threshold_trx_id = p_threshold_trx_id;
SELECT tt.tax_category_id, tt.actual_section_code
FROM jai_ap_tds_prepayments tp, jai_ap_tds_inv_taxes tt
WHERE tp.invoice_distribution_id_prepay = p_invoice_distribution_id AND
tp.invoice_distribution_id = tt.invoice_distribution_id AND rownum = 1;
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
--jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
p_invoice_id => p_invoice_id,
p_prepay_distribution_id => p_prepay_distribution_id,
p_threshold_grp_id => ln_temp_threshold_grp_id,
p_threshold_hdr_id => ln_temp_threshold_hdr_id,
p_threshold_slab_id => ln_threshold_slab_id,
p_threshold_type => lv_threshold_type,
p_process_flag => p_process_flag,
p_process_message => p_process_message,
p_codepath => p_codepath);
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
--jai_ap_tds_generation_pkg.maintain_thhold_grps
jai_ap_dtc_generation_pkg.maintain_thhold_grps
(
p_threshold_grp_id => ln_threshold_grp_id,
p_trx_invoice_unapply_amount => r_tds_details_apply.tds_taxable_basis,/*5751783*/
p_tds_event => 'PREPAYMENT UNAPPLICATION',
p_invoice_id => p_invoice_id,
p_invoice_distribution_id => p_invoice_distribution_id,
p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
p_process_flag => p_process_flag,
P_process_message => p_process_message,
p_codepath => p_codepath
);
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
ln_tax_category_id := r_tds_details_apply.tax_category_id;
ln_tot_tds_amt := r_tds_details_apply.tds_amount;--Update to function tds amount for bug13833254 .
/*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(r_get_total_prepayment_tax.tds_taxable_basis)
where invoice_id = p_invoice_id;*/
/* Update the threshold group */
ln_threshold_grp_id:= r_tds_details_apply.tds_threshold_grp_id;
if p_event = 'INSERT' then /*Added for Bug 8431516*/
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
--jai_ap_tds_generation_pkg.maintain_thhold_grps
jai_ap_dtc_generation_pkg.maintain_thhold_grps
(
p_threshold_grp_id => ln_threshold_grp_id,
--p_trx_tax_paid => r_get_total_prepayment_tax.tds_amount, --Commented by Zhiwei Hou for DTC enhancement Bug#13828149 on 20120312
p_trx_tax_paid => ln_tot_tds_amt,--Added by Zhiwei Hou for DTC enhancement Bug#13828149 on 20120312
p_tds_event => 'PREPAYMENT UNAPPLICATION',
p_invoice_id => p_invoice_id,
p_invoice_distribution_id => p_invoice_distribution_id,
p_threshold_grp_audit_id => ln_threshold_grp_audit_id,
p_process_flag => p_process_flag,
P_process_message => p_process_message,
p_codepath => p_codepath
);
END IF; /*if p_event = 'INSERT' then*/
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 the unapply flag for invoice distribution */
update jai_ap_tds_prepayments
set unapply_flag = 'Y'
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_parent_distribution_id;
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
--jai_ap_tds_generation_pkg.get_tds_threshold_slab(
jai_ap_dtc_generation_pkg.get_tds_threshold_slab(
p_invoice_id => p_invoice_id,
p_prepay_distribution_id => p_prepay_distribution_id,
p_threshold_grp_id => ln_temp_threshold_grp_id,
p_threshold_hdr_id => ln_temp_threshold_hdr_id,
p_threshold_slab_id => ln_after_threshold_slab_id,
p_threshold_type => lv_after_threshold_type,
p_process_flag => p_process_flag,
p_process_message => p_process_message,
p_codepath => p_codepath);
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
--jai_ap_tds_generation_pkg.process_threshold_transition
jai_ap_dtc_generation_pkg.process_threshold_transition
(
p_threshold_grp_id => ln_temp_threshold_grp_id,
p_threshold_slab_id => ln_after_threshold_slab_id,
p_invoice_id => ln_parent_pp_invoice_id,
p_vendor_id => p_vendor_id,
p_vendor_site_id => p_vendor_site_id,
p_accounting_date => p_accounting_date,
p_tds_event => lv_slab_transition_tds_event,
p_org_id => p_org_id,
pn_prepayment_inovice_id => pre_pay_inv_id, -- Added by Chong.Lei for bug#13787158
pn_unapply_amount => r_get_total_prepayment_tax.tds_taxable_basis, -- Added by Chong.Lei for bug#13787158
pv_tds_invoice_num => lv_ppu_tds_inv_num,
pv_cm_invoice_num => lv_ppu_tds_cm_num,
p_process_flag => p_process_flag,
p_process_message => p_process_message
);
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
ln_tax_category_id := r_wct_details_apply.tax_category_id;
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';
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
ln_tax_category_id := r_essi_details_apply.tax_category_id;
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;
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 * nvl(p_exchange_rate,1))
where invoice_id = p_invoice_id;
/*Updated by Wenqiong for bug 13359892
*Changed the package jai_ap_tds_generation_pkg to jai_ap_dtc_generation_pkg */
--jai_ap_tds_generation_pkg.import_and_approve
jai_ap_dtc_generation_pkg.import_and_approve
(
p_invoice_id => ln_parent_pp_invoice_id, /*Bug 5751783*/
p_start_thhold_trx_id => ln_start_threshold_trx_id,
p_tds_event => 'PREPAYMENT UNAPPLICATION',
p_process_flag => p_process_flag,
p_process_message => p_process_message
);
select invoice_distribution_id, amount, invoice_line_number, invoice_id
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 \*Commented for Bug 9494469*\
and nvl(actual_tax_id, default_tax_id) is not null \*Bug 8431516*\
and amount > 0; --Added by bgowrava for bug#9214036
select sum(application_amount)
from jai_ap_tds_prepayments
where invoice_distribution_id = p_invoice_distribution_id
and nvl(unapply_flag, 'N') <> 'Y';
select sum(amount) amount
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and invoice_line_number = p_invoice_line_num
and amount < 0;
\* 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,
nvl(actual_section_code, default_section_code) section_code, --Added NVL condition for Bug 8431516
nvl(actual_tax_id, default_tax_id) tax_id --Added NVL condition for Bug 8431516
from jai_ap_tds_inv_taxes
where invoice_id = p_pre_pay_inv_id -- Added where clause p_pre_pay_inv_id by Jia for FP bug6929483
and invoice_distribution_id = p_prepay_distribution_id
and nvl(actual_tax_id, default_tax_id) is not null; --Added NVL condition for Bug 8431516
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';
SELECT threshold_trx_id
FROM jai_ap_tds_inv_taxes
WHERE invoice_id = p_pre_pay_inv_id
AND invoice_distribution_id = p_invoice_distribution_id ;
SELECT max(nvl(threshold_trx_id, 0))
INTO lv_si_thhold_trx_id
FROM jai_ap_tds_inv_taxes
WHERE invoice_id = p_invoice_id ;
\* 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
*\
\* Bug 6363056. Commented the above
* and added the following. Need to selected the lowest rate between
* SI and PP
*\
tds_applicable_flag, tds_tax_id_other, tds_tax_id_prepay,
wct_applicable_flag, wct_tax_id_other, wct_tax_id_prepay,
essi_applicable_flag, essi_tax_id_other, essi_tax_id_prepay
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,
threshold_trx_id \*Bug 6363056*\
from jai_ap_tds_inv_taxes
where invoice_id = p_pre_pay_inv_id -- Added by Jia for FP bug6929483
and invoice_distribution_id = p_prepay_distribution_id
and section_type = cp_section_type; --rchandan for bug#4428980
select actual_tax_id, invoice_id \*Bug 5751783*\
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, invoice_id \*Bug 5751783*\
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(calc_tds_appln_amt) , sum(application_amount)
from jai_ap_tds_prepayments jatp
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
\*Bug 6363056. Added invoice_distribution_id condition also*\
and invoice_distribution_id = p_item_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
);
select invoice_num, invoice_id
from ap_invoices_all
where invoice_id = p_invoice_id;
SELECT *
FROM jai_ap_tds_thhold_grps
WHERE threshold_grp_id = p_threshold_grp_id;
select threshold_grp_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id
and section_type = 'TDS_SECTION'; --Added for bug#8855650 by JMEENA
select tds_section_code_other, tds_tax_id_other, application_amount, invoice_distribution_id
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
SELECT nvl(sum(taxable_amount), 0)
FROM jai_ap_tds_thhold_trxs
WHERE invoice_id = p_invoice_id
AND tds_event = 'INVOICE VALIDATE'
group by invoice_id;
SELECT nvl(sum(amount*nvl(p_exchange_rate, 1)), 0)
FROM jai_ap_tds_inv_taxes
WHERE invoice_id = p_invoice_id
AND nvl(actual_tax_id, default_tax_id) is not null
AND section_type = 'TDS_SECTION'
AND actual_section_code IS NOT NULL
AND threshold_trx_id IS NOT NULL
group by invoice_id;
\* 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
\*Bug 5751783. Added the update for non-rounded value also*\
calc_tds_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/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
\*Bug 5751783. Added the update for non-rounded value also*\
calc_wct_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/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
\*Bug 5751783. Added the update for non-rounded value also*\
calc_essi_appln_amt = cur_rec.application_amount * ln_exchange_rate * (ln_tax_rate_basis/100)
where tds_prepayment_id = cur_rec.tds_prepayment_id;
Threshold Groups need not be updated with the Prepayment application amount as only Effective amount i.e. Invoice - Prepay
was passed to Threshold Group during validation*\
ln_application_mode := NULL;
FOR get_si_det IN (SELECT jattt.*,
jatp.tds_prepayment_id tds_prepayment_id,
jatp.application_amount tds_taxable_basis,
jatp.invoice_distribution_id tax_dist
FROM jai_ap_tds_thhold_trxs jattt,
jai_ap_tds_prepayments jatp
WHERE jattt.invoice_id = jatp.invoice_id
AND jattt.tds_event = 'INVOICE VALIDATE'
AND jatp.tds_applicable_flag ='Y'
AND invoice_distribution_id_prepay = p_invoice_distribution_id
AND jattt.invoice_id = p_invoice_id
AND jatp.invoice_distribution_id in
(select invoice_distribution_id
from jai_ap_tdS_inv_taxes
where threshold_trx_id = jattt.threshold_trx_id
and invoice_id = p_invoice_id
and section_type ='TDS_SECTION'
)
)
LOOP
ln_temp_threshold_grp_id := get_si_det.threshold_grp_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 => get_si_det.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,
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 tds_prepayment_id = get_si_det.tds_prepayment_id; -- Bug 6363056
if p_event = 'INSERT' then
update jai_ap_tds_prepayments
set tds_threshold_trx_id_apply = -999
where tds_prepayment_id = get_si_det.tds_prepayment_id; --Bug 6031679
if p_event = 'INSERT' then \*Bug 8431516*\
\*Bug 5751783. Changed from invoice_id to ln_parent_invoice_id*\
jai_ap_tds_generation_pkg.generate_tds_invoices
(
pn_invoice_id => ln_parent_invoice_id,
pn_invoice_distribution_id => p_invoice_distribution_id,
pv_invoice_num_prepay_apply => lv_invoice_num_prepay_apply,
pn_taxable_amount => ln_tot_appln_amt, \*Bug 6363056*\
pn_tax_amount => ln_tot_tds_amt,
pn_tax_id => ln_parent_tax_id,
pd_accounting_date => p_accounting_date,
pv_tds_event => 'PREPAYMENT APPLICATION',
pn_threshold_grp_id => ln_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_tds,
pd_creation_date => p_creation_date, -- Bug 5722028. Added by Lakshmi Gopalsami
pn_calc_tax_amount => 0,\* Added for bug 12965614 *\
p_process_flag => p_process_flag,
p_process_message => p_process_message
);
\* Update the threshold group *\
jai_ap_tds_generation_pkg.maintain_thhold_grps
( p_threshold_grp_id => ln_threshold_grp_id,
p_trx_tax_paid => (-1 * ln_tot_tds_amt),
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,
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 each distribution with the threshold grp id as
-- it may vary depending on the date and the group.
-- changed invoice_distribution_id_prepay to invoice_distribution_id.
update jai_ap_tds_prepayments
set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
where tds_threshold_trx_id_apply = -999
and invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id; \*Bug 6363056*\
if p_event = 'INSERT' then --Added for Bug 8431516
\* Bug 5751783
* Changed from p_invoice_id to ln_parent_invoice_id
* Parent invoice_id should be depending on the TDS invoice
* created.
*\
jai_ap_tds_generation_pkg.process_threshold_rollback
( p_invoice_id => ln_parent_invoice_id,
p_before_threshold_type => lv_threshold_type,
p_after_threshold_type => lv_after_threshold_type,
p_before_threshold_slab_id => ln_threshold_slab_id,
p_after_threshold_slab_id => ln_after_threshold_slab_id,
p_threshold_grp_id => ln_temp_threshold_grp_id,
p_org_id => p_org_id,
p_accounting_date => p_accounting_date,
p_invoice_distribution_id => p_invoice_distribution_id,
p_prepay_distribution_id => p_prepay_distribution_id,
p_process_flag => p_process_flag,
p_process_message => p_process_message,
p_codepath => p_codepath);
end if; \*if p_event = 'INSERT' then*\ --Added for Bug 8431516
end if; \*if p_event = 'INSERT' then*\ --Added for Bug 8431516
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;
if p_event = 'INSERT' then --Added for Bug 8431516
\*Bug 5751783 - Changed from p_invoice_id to ln_pp_section_invoice_id*\
jai_ap_tds_generation_pkg.generate_tds_invoices
(
pn_invoice_id => ln_parent_invoice_id ,
pn_invoice_distribution_id => p_invoice_distribution_id ,
pn_taxable_amount => r_get_total_prepayment_tax.wct_taxable_basis ,
pn_tax_amount => r_get_total_prepayment_tax.wct_amount ,
pn_tax_id => ln_pp_section_tax_id ,
pd_accounting_date => p_accounting_date ,
pv_tds_event => 'PREPAYMENT APPLICATION' ,
pn_threshold_grp_id => null ,
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_wct ,
pd_creation_date => p_creation_date ,
pn_calc_tax_amount => 0, \*Added for bug 12965614 *\
p_process_flag => p_process_flag ,
p_process_message => p_process_message
);
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';
end if; \*if p_event = 'INSERT' then*\ --Added for Bug 8431516
IF p_event = 'INSERT' then --Added for Bug 8431516
\*Bug 5751783 - Changed from p_invoice_id to ln_pp_section_invoice_id*\
jai_ap_tds_generation_pkg.generate_tds_invoices
(
pn_invoice_id => ln_parent_invoice_id ,
pn_invoice_distribution_id => p_invoice_distribution_id ,
pn_taxable_amount => r_get_total_prepayment_tax.essi_taxable_basis ,
pn_tax_amount => r_get_total_prepayment_tax.essi_amount ,
pn_tax_id => ln_pp_section_tax_id ,
pd_accounting_date => p_accounting_date ,
pv_tds_event => 'PREPAYMENT APPLICATION' ,
pn_threshold_grp_id => null ,
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_essi ,
pd_creation_date => p_creation_date ,
pn_calc_tax_amount => 0, \*Added for bug 12965614 *\
p_process_flag => p_process_flag ,
p_process_message => p_process_message
);
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';
end if; \*IF p_event = 'INSERT' then*\ --Added for Bug 8431516
Records are inserted into AP Interface tables using Standard Invoice, but import_and_approve
was called using the Prepayment Invoice ID. Hence wrong group_id was getting passed and no
Invoices were getting improved*\
jai_ap_tds_generation_pkg.import_and_approve
(
p_invoice_id => ln_parent_invoice_id,
p_start_thhold_trx_id => ln_start_threshold_trx_id,
p_tds_event => 'PREPAYMENT APPLICATION',
p_process_flag => p_process_flag,
p_process_message => p_process_message
);
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'
);
SELECT INVOICE_DISTRIBUTION_ID,
AMOUNT,
INVOICE_LINE_NUMBER,
INVOICE_ID,
(SELECT DISTINCT ACTUAL_SECTION_CODE
FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_ID = CN_INVOICE_ID AND
INVOICE_DISTRIBUTION_ID = JATIT.INVOICE_DISTRIBUTION_ID AND
ACTUAL_SECTION_CODE IS NOT NULL AND
SECTION_TYPE = 'TDS_SECTION' AND
ACTUAL_SECTION_CODE = CV_TDS_SECTION_CODE AND
ROWNUM = 1) TDS_SECTION_CODE,
wct_essi_applicable(CN_INVOICE_ID,JATIT.INVOICE_DISTRIBUTION_ID,'WCT_SECTION') WCT_APPLICABLE,
wct_essi_applicable(CN_INVOICE_ID,JATIT.INVOICE_DISTRIBUTION_ID,'ESSI_SECTION') ESSI_APPLICABLE
FROM JAI_AP_TDS_INV_TAXES JATIT
WHERE INVOICE_ID = CN_INVOICE_ID AND
INVOICE_DISTRIBUTION_ID <> CN_PREPAY_DISTRIBUTION_ID AND
--ACTUAL_TAX_ID IS NOT NULL AND --Commented by Zhiwei Hou on 20120116
(wct_essi_applicable(CN_INVOICE_ID,JATIT.INVOICE_DISTRIBUTION_ID,'WCT_SECTION') = CV_WCT_APPLICABLE OR
CV_WCT_APPLICABLE IS NULL ) AND
(wct_essi_applicable(CN_INVOICE_ID,JATIT.INVOICE_DISTRIBUTION_ID,'ESSI_SECTION') = CV_ESSI_APPLICABLE OR
CV_ESSI_APPLICABLE IS NULL ) AND
INVOICE_DISTRIBUTION_ID <> 1 AND -- Added by Chong for bug#13802244 2012/09/19
AMOUNT > 0;
SELECT gl_date
FROM ap_invoices_all
WHERE invoice_id = cn_invoice_id;
SELECT distinct actual_section_code
FROM jai_ap_tds_inv_taxes
WHERE invoice_distribution_id = p_prepay_distribution_id;
SELECT 'Y'
FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_DISTRIBUTION_ID = cn_dist_id AND
SECTION_TYPE = cn_section_type;
SELECT nvl(sum(application_amount),0)
FROM JAI_AP_TDS_PREPAYMENTS
WHERE invoice_distribution_id = cn_invoice_distribution_id
AND nvl(unapply_flag, 'N') <> 'Y';
select sum(amount) amount
from jai_ap_tds_inv_taxes
where invoice_id = cn_invoice_id
and invoice_line_number = cn_invoice_line_num
and amount < 0;
insert into jai_ap_tds_prepayments
(
tds_prepayment_id ,
invoice_id ,
invoice_distribution_id_prepay ,
invoice_distribution_id ,
application_amount ,
application_basis ,
tds_section_code_prepay ,
tds_section_code_other ,
tds_applicable_flag ,
wct_applicable_flag ,
essi_applicable_flag ,
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 ,
lv_application_basis ,
lv_ppay_tds_section_code ,
cur_si_distributions_rec.tds_section_code ,
lv_tds_applicable ,
lv_wct_applicable ,
lv_essi_applicable ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
insert into jai_ap_tds_prepayments
(
tds_prepayment_id ,
invoice_id ,
invoice_distribution_id_prepay ,
invoice_distribution_id ,
application_amount ,
application_basis ,
tds_section_code_prepay ,
tds_section_code_other ,
tds_applicable_flag ,
wct_applicable_flag ,
essi_applicable_flag ,
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 ,
lv_application_basis ,
lv_ppay_tds_section_code ,
cur_si_distributions_rec.tds_section_code ,
lv_tds_applicable ,
lv_wct_applicable ,
lv_essi_applicable ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select tds_prepayment_id,
invoice_distribution_id,
application_amount,
application_basis,
tds_section_code_prepay,
tds_section_code_other,
tds_applicable_flag,
wct_applicable_flag,
essi_applicable_flag
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
SELECT SUM(nvl(tax_amount,0))
FROM jai_ap_tds_inv_taxes
--WHERE invoice_distribution_id = cn_invoice_distribution_id --Commented by Chong for issue120920-66 2012/10/09
WHERE (invoice_distribution_id = cn_invoice_distribution_id --Added by Chong for issue120920-66 2012/10/09
OR invoice_distribution_id = 1) --Added by Chong for issue120920-66 2012/10/09
AND invoice_id = p_invoice_id --Added by Chong for issue120920-66 2012/10/09
AND ACTUAL_SECTION_CODE = cv_section_code
AND section_type = cv_section_type;
SELECT nvl(amount,0)
FROM jai_ap_tds_inv_taxes
--WHERE invoice_distribution_id = cn_invoice_distribution_id --Commented by Chong for issue120920-66 2012/10/09
WHERE (invoice_distribution_id = cn_invoice_distribution_id --Added by Chong for issue120920-66 2012/10/09
OR invoice_distribution_id = 1) --Added by Chong for issue120920-66 2012/10/09
AND invoice_id = p_invoice_id --Added by Chong for issue120920-66 2012/10/09
AND amount > 0;
/*Update for adding exchange rate for bug13833254 */
cursor c_get_tax_sec_det(p_invoice_id number, p_invoice_distribution_id number)
is
select tds_section_code_other, application_amount * nvl(p_exchange_rate,1) application_amount, invoice_distribution_id
from jai_ap_tds_prepayments
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
select threshold_grp_id,
actual_tax_id,
threshold_trx_id
from jai_ap_tds_inv_taxes
where invoice_id = p_pre_pay_inv_id
and invoice_distribution_id = p_prepay_distribution_id
and section_type = cp_section_type;
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 sum(calc_tds_appln_amt) , sum(application_amount)
from jai_ap_tds_prepayments jatp
where invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
and invoice_distribution_id = p_item_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
and section_type = 'TDS_SECTION'
--and threshold_trx_id is not null --Commented by Chong for issue120920-66 2012/10/09
);
SELECT DISTINCT tt.actual_SECTION_CODE
FROM JAI_AP_TDS_PREPAYMENTS tp, JAI_AP_TDS_INV_TAXES tt
WHERE tp.invoice_distribution_id = tt.invoice_distribution_id
AND tp.tds_section_code_other = tt.actual_section_code
AND tp.invoice_distribution_id_prepay = p_invoice_distribution_id;
SELECT DISTINCT tt.tax_category_id
FROM JAI_AP_TDS_INV_TAXES tt
WHERE tt.invoice_distribution_id = cn_invoice_distribution_id;
/*Update for adding exchange rate for bug13833254 */
--Updated by Wenqiong for bug13787605 begin
CURSOR c_get_tds_group_amt (cv_section_code VARCHAR2)IS
-- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
--select get_tax_category(jatp.invoice_distribution_id ) tax_category_id,
select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
-- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
sum(tds_application_amount)* nvl(p_exchange_rate,1) tds_amount,
sum(application_amount)* nvl(p_exchange_rate,1) amount
from jai_ap_tds_prepayments jatp
where jatp.invoice_id = p_invoice_id
and jatp.tds_applicable_flag = 'Y'
AND jatp.tds_section_code_other= cv_section_code
AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
-- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
--GROUP BY get_tax_category(jatp.invoice_distribution_id );
/*Update for adding exchange rate for bug13833254 */
--Updated by Wenqiong for bug13787605 begin
CURSOR c_get_wct_group_amt (cv_section_code VARCHAR2)IS
-- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
--SELECT get_tax_category(jatp.invoice_distribution_id ) tax_category_id,
select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
-- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
sum(wct_application_amount)* nvl(p_exchange_rate,1) wct_amount,
sum(application_amount)* nvl(p_exchange_rate,1) amount
from jai_ap_tds_prepayments jatp
where jatp.invoice_id = p_invoice_id
and jatp.wct_applicable_flag = 'Y'
AND jatp.tds_section_code_other= cv_section_code
AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
-- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
--GROUP BY get_tax_category(jatp.invoice_distribution_id );
/*Update for adding exchange rate for bug13833254 */
CURSOR c_get_essi_group_amt (cv_section_code VARCHAR2)IS
-- modifed by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
--select get_tax_category(jatp.invoice_distribution_id ) tax_category_id,
select get_tax_category(decode(jatp.application_basis,'STANDARD',jatp.invoice_distribution_id,p_prepay_distribution_id)) tax_category_id,
-- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 end.
sum(essi_application_amount)* nvl(p_exchange_rate,1) essi_amount,
sum(application_amount)* nvl(p_exchange_rate,1) amount
from jai_ap_tds_prepayments jatp
where jatp.invoice_id = p_invoice_id
and jatp.essi_applicable_flag = 'Y'
AND jatp.tds_section_code_other= cv_section_code
AND jatp.invoice_distribution_id_prepay = p_invoice_distribution_id
-- modified by zhiwei.xin for bug 13837788 on 14-Mar-2012 begin
--GROUP BY get_tax_category(jatp.invoice_distribution_id );
select invoice_num, invoice_id
from ap_invoices_all
where invoice_id = p_invoice_id;
SELECT *
FROM jai_ap_tds_thhold_grps
WHERE threshold_grp_id = p_threshold_grp_id;
select threshold_grp_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id
and section_type = 'TDS_SECTION';
SELECT nvl(sum(taxable_amount), 0)
FROM jai_ap_tds_thhold_trxs
WHERE invoice_id = p_invoice_id
AND tds_event = 'INVOICE VALIDATE'
group by invoice_id;
SELECT nvl(sum(amount), 0)--Remove multiple rate for bug13833254
FROM jai_ap_tds_inv_taxes
WHERE invoice_id = p_invoice_id
AND nvl(actual_tax_id, default_tax_id) is not null
AND section_type = 'TDS_SECTION'
AND actual_section_code IS NOT NULL
AND threshold_trx_id IS NOT NULL
group by invoice_id;
SELECT tax_category_id,
section_type,
actual_section_code
FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_DISTRIBUTION_ID = cn_invoice_distribution_id
AND ROWNUM = 1;
/* 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,
calc_tds_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
where tds_prepayment_id = cur_rec.tds_prepayment_id;
update jai_ap_tds_prepayments
set tds_threshold_grp_id = ln_pp_thhold_grp_id
where tds_prepayment_id = cur_rec.tds_prepayment_id;
update jai_ap_tds_prepayments
set wct_application_amount = ln_tds_tmp_amt,
calc_wct_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
where tds_prepayment_id = cur_rec.tds_prepayment_id;
update jai_ap_tds_prepayments
set essi_application_amount = ln_tds_tmp_amt,
calc_essi_appln_amt = ln_tds_tmp_amt * ln_exchange_rate --updated by Wenqiong for bug 13771948
where tds_prepayment_id = cur_rec.tds_prepayment_id;
Threshold Groups need not be updated with the Prepayment application amount as only Effective amount i.e. Invoice - Prepay
was passed to Threshold Group during validation*/
ln_application_mode := NULL;
FOR get_si_det IN (SELECT jattt.*,
jatp.tds_prepayment_id tds_prepayment_id,
jatp.application_amount * nvl(p_exchange_rate,1) tds_taxable_basis,
jatp.invoice_distribution_id tax_dist
FROM jai_ap_tds_thhold_trxs jattt,
jai_ap_tds_prepayments jatp
WHERE jattt.invoice_id = jatp.invoice_id
AND (jattt.tds_event = 'INVOICE VALIDATE'
OR jattt.tds_event LIKE 'THRESHOLD TRANSITION(%' --Added threshold transition by Chong for issue120920-66 2012/10/09
)
AND jatp.tds_applicable_flag ='Y'
AND invoice_distribution_id_prepay = p_invoice_distribution_id
AND jattt.invoice_id = p_invoice_id
AND jatp.invoice_distribution_id in
(select invoice_distribution_id
from jai_ap_tdS_inv_taxes
where --threshold_trx_id = jattt.threshold_trx_id --Commented by Chong for issue120920-66 2012/10/09
actual_section_code = jattt.tds_section_code --Added by Chong for issue120920-66 2012/10/09
and invoice_id = p_invoice_id
and section_type ='TDS_SECTION'
)
AND jattt.tds_section_code IS NOT NULL --Added By Chong, Only pick up TDS section records in trx table 20130330
)
LOOP
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, 'in loop : '||get_si_det.threshold_grp_id);
\* update the threshold with the tds amount that will be impacted because of this application *\
jai_ap_dtc_generation_pkg.maintain_thhold_grps
(
p_threshold_grp_id => ln_threshold_grp_id,
p_trx_invoice_apply_amount => get_si_det.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,
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 tds_prepayment_id = get_si_det.tds_prepayment_id;
if p_event = 'INSERT' then
update jai_ap_tds_prepayments
set tds_threshold_trx_id_apply = -999
where tds_prepayment_id = get_si_det.tds_prepayment_id;
if p_event = 'INSERT' then
jai_cmn_utils_pkg.WRITE_FND_LOG(G_LEVEL_STATEMENT, G_MODULE_NAME||l_api_name, '@@@in loop generate_dtc_invoices lv_invoice_num_prepay_apply: '|| lv_invoice_num_prepay_apply);
/* Update the threshold group */
jai_ap_dtc_generation_pkg.maintain_thhold_grps
( p_threshold_grp_id => ln_threshold_grp_id,
p_trx_tax_paid => (-1 * ln_tot_tds_amt),
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,
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 each distribution with the threshold grp id as
-- it may vary depending on the date and the group.
-- changed invoice_distribution_id_prepay to invoice_distribution_id.
IF lv_application_basis = 'STANDARD' THEN
update jai_ap_tds_prepayments tds_prepay
set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
where tds_threshold_trx_id_apply = -999
and invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id
AND EXISTS (SELECT 1 FROM JAI_AP_TDS_INV_TAXES tds_tax WHERE
--tds_tax.invoice_distribution_id = tds_prepay.invoice_distribution_id --Commented by Chong for issue120920-66 2012/10/09
tds_tax.actual_section_code = tds_prepay.tds_section_code_prepay --Added by Chong for issue120920-66 2012/10/09
AND tds_tax.invoice_id = p_invoice_id
AND tds_tax.tax_category_id = ln_tax_category_id);
update jai_ap_tds_prepayments tds_prepay
set tds_threshold_trx_id_apply = ln_threshold_trx_id_tds
where tds_threshold_trx_id_apply = -999
and invoice_id = p_invoice_id
and invoice_distribution_id_prepay = p_invoice_distribution_id;
/*if p_event = 'INSERT' then
\*
* Changed from p_invoice_id to ln_parent_invoice_id
* Parent invoice_id should be depending on the TDS invoice
* created.
*\
jai_ap_dtc_generation_pkg.process_threshold_rollback
( p_invoice_id => ln_parent_invoice_id,
p_before_threshold_type => lv_threshold_type,
p_after_threshold_type => lv_after_threshold_type,
p_before_threshold_slab_id => ln_threshold_slab_id,
p_after_threshold_slab_id => ln_after_threshold_slab_id,
p_threshold_grp_id => ln_temp_threshold_grp_id,
p_org_id => p_org_id,
p_accounting_date => p_accounting_date,
p_invoice_distribution_id => p_invoice_distribution_id,
p_prepay_distribution_id => p_prepay_distribution_id,
p_called_from => 'PREPAY' ,
p_process_flag => p_process_flag,
p_process_message => p_process_message,
p_codepath => p_codepath);
end if; \*if p_event = 'INSERT' then*\ */
end if; /*if p_event = 'INSERT' then*/
if p_event = 'INSERT' then
ln_temp_threshold_grp_id := NVL(ln_temp_threshold_grp_id,ln_si_thgrp_id);
end if; /*if p_event = 'INSERT' then*/
update JAI_AP_TDS_INVOICES
set amt_reversed = nvl(amt_reversed, 0) + r_get_total_prepayment_tax.tds_amount * nvl(p_exchange_rate,1),
amt_applied = nvl(amt_applied, 0) + abs(p_prepay_amount) * nvl(p_exchange_rate,1)
where invoice_id = p_invoice_id;
if p_event = 'INSERT' then
jai_ap_dtc_generation_pkg.generate_dtc_invoices
(
pn_invoice_id => ln_parent_invoice_id ,
pn_invoice_distribution_id => p_invoice_distribution_id ,
pv_invoice_num_prepay_apply=> lv_invoice_num_prepay_apply ,
pn_taxable_amount => ln_tot_appln_amt ,
pn_tax_amount => ln_tot_tds_amt ,
pd_accounting_date => p_accounting_date ,
pv_tds_event => 'PREPAYMENT APPLICATION' ,
pn_threshold_grp_id => ln_threshold_grp_id ,
pn_threshold_hdr_id => ln_temp_threshold_hdr_id, --Added by Chong for eTDS bug#16414088 20130320
pn_tax_category_id => ln_tax_category_id ,
pv_section_type => lv_section_type ,
pv_section_code => lv_section_code ,
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_wct ,
pd_creation_date => sysdate ,
p_process_flag => p_process_flag ,
p_process_message => p_process_message
);
update jai_ap_tds_prepayments tds_prepay
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'
AND EXISTS (SELECT 1 FROM JAI_AP_TDS_INV_TAXES tds_tax WHERE
tds_tax.invoice_distribution_id = tds_prepay.invoice_distribution_id
AND tds_tax.invoice_id = p_invoice_id
AND tds_tax.tax_category_id = ln_tax_category_id);
update jai_ap_tds_prepayments tds_prepay
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';
end if; /*if p_event = 'INSERT' then*/
if p_event = 'INSERT' then
jai_ap_dtc_generation_pkg.generate_dtc_invoices
(
pn_invoice_id => ln_parent_invoice_id ,
pn_invoice_distribution_id => p_invoice_distribution_id ,
pv_invoice_num_prepay_apply=> lv_invoice_num_prepay_apply ,
pn_taxable_amount => ln_tot_appln_amt ,
pn_tax_amount => ln_tot_tds_amt ,
pd_accounting_date => p_accounting_date ,
pv_tds_event => 'PREPAYMENT APPLICATION' ,
pn_threshold_grp_id => ln_threshold_grp_id ,
pn_threshold_hdr_id => ln_temp_threshold_hdr_id, --Added by Chong for eTDS bug#16414088 20130320
pn_tax_category_id => ln_tax_category_id ,
pv_section_type => lv_section_type ,
pv_section_code => lv_section_code ,
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_wct ,
pd_creation_date => sysdate ,
p_process_flag => p_process_flag ,
p_process_message => p_process_message
);
update jai_ap_tds_prepayments tds_prepay
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'
AND EXISTS (SELECT 1 FROM JAI_AP_TDS_INV_TAXES tds_tax WHERE
tds_tax.invoice_distribution_id = tds_prepay.invoice_distribution_id
AND tds_tax.invoice_id = p_invoice_id
AND tds_tax.tax_category_id = ln_tax_category_id);
update jai_ap_tds_prepayments tds_prepay
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';
end if; /*IF p_event = 'INSERT' then*/ --Added for Bug 8431516
Records are inserted into AP Interface tables using Standard Invoice, but import_and_approve
was called using the Prepayment Invoice ID. Hence wrong group_id was getting passed and no
Invoices were getting improved*/
jai_ap_dtc_generation_pkg.import_and_approve
(
p_invoice_id => ln_parent_invoice_id,
p_start_thhold_trx_id => ln_start_threshold_trx_id,
p_tds_event => 'PREPAYMENT APPLICATION',
p_process_flag => p_process_flag,
p_process_message => p_process_message
);
SELECT accounting_date
INTO p_prepay_gl_date
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_prepay_inv_dist_id;
SELECT 'Y' FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_ID = cn_invoice_id
AND INVOICE_DISTRIBUTION_ID = cn_distribution_id
AND SECTION_TYPE = cv_section_type;
SELECT DISTINCT tax_category_id FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_DISTRIBUTION_ID = cn_distribution_id;
SELECT DISTINCT jatit.tax_category_id
FROM JAI_AP_TDS_INV_TAXES jatit
,(
SELECT DISTINCT invoice_id
,actual_section_code
FROM JAI_AP_TDS_INV_TAXES
WHERE INVOICE_DISTRIBUTION_ID = cn_distribution_id
) jatit_sct
WHERE jatit.invoice_id =jatit_sct.invoice_id
AND jatit.actual_section_code =jatit_sct.actual_section_code
AND jatit.tax_category_id IS NOT NULL;