[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
9. 6/01/2010 Added by Jia for FP bug6929483
Issue: This is a forward port bug for the bug6911776
Applying prepayment to an invoice takes longer time
Fixed: 1) Add new procedure get_prepay_invoice_id to get invoice_id
2) Modified the procedure populate_section_tax and procedure process_tds_invoices.
The queries referring to the table jai_ap_tds_inv_taxes,
included invoice_id in their where clause to make use of existing index
10. 07/01/2010 Added by Xiao Lv for FP bug#8345080
Issue: This is a forward port bug for the bug8333898
TDS DEDUCTING TWICE
Fixed: 1) Add new cursor c_get_grp_details_si_inv_dist, c_get_tax_sec_det.
11. 13-Jan-2010 Xiao for Bug#6596019
Commented the code related to creation of TDS invoices for RTN generation.
This is implemented because on application of prepayment the TDS calculated is
on the net amount of standard invoice and hence the RTN need not be created.
12. 14/01/2010 Added by Jia for FP Bug#7431371
Issue: This is a forward port bug for the 11i Bug#7419533
FINANCIALS FOR INDIA -TDS NOT WORKING IN CASE OF MULTIPLE DISTRIBUTIONS
Fixed: Commented the code in procdeure process_tds_invoices for calling the procedure maintain_thhold_grps.
13. 25-Jan-2010 Bug 5751783 (Forward Port of 5721614)
-------------------------------------
Issues
+ Amount in certificates is wrong. All calculations are made based on rounded values
+ Certificates are generated with Taxable Basis as 0 but non zero tax amount
+ Certificates are generated with negative amounts.
+ During Prepayment Un-application if Threshold Transition occurs then there are no TDS Invoices generated.
+ Taxable Basis is wrong for Threshold Rollback.
+ Applying Prepayment with different rates results in negative RTN
Bug 8679964 (Forward Port of 8639011)
-------------------------------------
When attempting to unapply prepayment error message pops up 'Cannot unapply the prepayment as it was applied
before validating the standard invoice' even though the prepayment was applied after validation of std invoice.
Bug 6363056 (Forward Port of 6031679)
-------------------------------------
When prepayment from the previous year is applied on to the Standard Invoice of the current year,
it results in 'Effective Tax Amount cannot be negative'.
This issue is fixed by Invoice ID of the latest document in jai_ap_tds_thhold_trxs when inserting records
for TDS Event 'PREPAYMENT APPLICATION'. Apart from this, the threshold group which belongs latest
GL Date in the Distribution is used.
Bug 6972230 (Forward Port of 6742977)
-------------------------------------
RTN not generated for the correct amount when Prepayment Tax Rate is different from the Standard Invoice
Tax Rate
Bug 6929483 (Forward Port of 6911776)
-------------------------------------
Pending fix which was dependent in 5751783 is done here
Bug 8431516 (Forward Port of 7626202)
-------------------------------------
RTN invoice would be generated to negate the effect of TDS invoice created for a prepayment, when the prepayment
is applied to a standard invoice.
14. 21-Jun-2010 Bug - 9826422
Description: 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
Fix: Replaced p_invoice_id by ln_parent_invoice_id
15. 09-Jun-2011 Bug 12392890
Description:UNAPP OF PREPAYMENT SHOULD NOT BE ALLOWED ON VALIDATED STANDARD INVOICE AFTER AP
Fix:Modified the cursor c_get_prepay_apply
Changed the parameter from p_invoice_distribution_id to p_parent_reversal_id
16. 24-Jan-2012 mmurtuza for bug 13620923
Description: PREPAYMENT UNAPPLICATION HAS NOT HAPPENING WHERE INVOICE BEFORE BREACHING THRESHOLD are applied
Fix: Arcs'ed out the version 120.3.12000000.17 to revert the changes done for DTC ER.
Added cursor c_chk_tds_deducted to check if TDS is deducted for this bug
17. 15-Jun-2012 amandali for bug 13929793
Description:PREPAYMENT ALLOWED TO BE APPLIED ON INVOICE EVEN IF IT HAS NO TDS
Fix:In populate_section_tax procedure, added a condition to check the TDS sections on both the documents.
18. 23-Jul-12 amandali for bug 14183670
Description:RTN TDS invoice not generated when a prepayment is applied to a standard invoice
Fix: Added a new cursor c_applied_amount to calculate the applied amount to an invoice and modified the if condition for checking the application_mode
19. 08-Nov-2012 amandali for bug 14811018
Description:RTN not generated for standard invoice having a negative line without TDS
Fix:Added AND clause in cursor c_get_effective_available_amt in procedure allocate_prepayment to find the effective amount for the lines having TDS alone.
20. 20-Nov-2012 amandali for bug 15848719
Description:RTN NOT GENERATED FOR A SI HAVING A NEGATIVE LINE HAVING TDS
Fix:Made modifications to procedure allocate_prepayment
21. 09-Jan-2012 amandali for bug 14657086
Description:RTN generated for prepayment with no TDS applied to SI belonging to Single slab
Fix:Added tds applicability condition in c_get_prepay_apply and also modified c_get_tax_details_si_inv_dist and
added if condition in populate_Section_tax procedure
--------------------------------------------------------------------------- */
-- Added by Jia for FP bug6929483, Begin
-----------------------------------------------------------------------------
PROCEDURE get_prepay_invoice_id
(
p_prepay_inv_dist_id NUMBER,
p_prepay_inv_id OUT NOCOPY NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
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
and tds_applicable_flag='Y' /* Bug 14657086 */
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) ) 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) ) 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) ) 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 tds_threshold_grp_id,
tds_threshold_trx_id_apply,
-- Bug 6363056
sum(decode(tds_applicable_flag , 'Y', application_amount, 0)) 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)) 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)) 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
and tds_threshold_grp_id is not null
and nvl(unapply_flag, 'N') <> 'Y' -- Bug 6363056
group by
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 wct_threshold_trx_id_apply, invoice_distribution_id -- Bug 6363056
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, invoice_distribution_id -- Bug 6363056
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,
/* 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;
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;
if p_event = 'INSERT' then /*Added for Bug 8431516*/
jai_ap_tds_generation_pkg.maintain_thhold_grps
(
p_threshold_grp_id => ln_threshold_grp_id,
p_trx_tax_paid => r_get_total_prepayment_tax.tds_amount,
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;
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;
update JAI_AP_TDS_INVOICES
set amt_reversed = nvl(amt_reversed, 0) - r_get_total_prepayment_tax.tds_amount_orig,
amt_applied = nvl(amt_applied, 0) - abs(p_prepay_amount)
where invoice_id = p_invoice_id;
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*/
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 nvl(actual_tax_id, default_tax_id) is not null /* Added and condition for bug 14811018 */
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,
threshold_slab_id_single /* Bug 14657086 */
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;
SELECT nvl(SUM(application_amount), 0)
FROM jai_ap_tds_prepayments
WHERE invoice_id = p_invoice_id
AND tds_threshold_trx_id_unapply IS 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
/*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*/
jai_cmn_utils_pkg.write_fnd_log_msg('jai_ap_tds_prepayments_pkg', ' ln_tot_appln_amt:'||ln_tot_appln_amt);
/* 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'
);