[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
A new procedure to insert into jai_ap_tds_thhold_trxs table is added.
India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
is not populated whenever an invoice is generated. Instead the Invoice details are
populated into jai_ap_tds_thhold_trxs. So whenever data is inserted into interface
tables the jai_ap_tds_thhold_trxs table is also populated.
3. 11/05/2005 rchandan for bug#4323338. Version 116.2
India Org Info DFF is eliminated as a part of JA migration. A table by name JAI_AP_TDS_ORG_TANS is dropped
and a view jai_ap_tds_org_tan_v is created to retrieve the PAN NO.
4. 24/05/2005 Ramananda for bug#4388958 File Version: 116.1
Changed AP Lookup code from 'TDS' to 'INDIA TDS'
5. 02/06/2005 Ramananda for bug# 4407184 File Version: 116.2
SQL Bind variable compliance is done
6. 08-Jun-2005 File Version 116.3. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
7. 14-Jun-2005 rchandan for bug#4428980, Version 116.4
Modified the object to remove literals from DML statements and CURSORS.
8. 08-Jul-2005 Sanjikum for Bug#4482462
1) In the procedure - generate_tds_invoices, removed the column payment_method_lookup_code
from cursors - c_po_vendor_sites_all, c_po_vendors
2) In the procedure generate_tds_invoices, commented the if condition of payment_method_lookup_code
3) In the procedure generate_tds_invoices, commented the value of parameter - p_payment_method_lookup_code
while calling procedure - jai_ap_utils_pkg.insert_ap_inv_interface
Ramananda for bug# 4407184
Re-Done: SQL Bind variable compliance is done
9. 29-Jun-2005 ssumaith - bug#4448789 - removal of hr_operating_units.legal_entity_id from this trigger.
10. 14-Jul-2005 rchandan for bug#4487676.File version 117.2
Sequnece jai_ap_tds_invoice_num_s is renamed to JAI_AP_TDS_THHOLD_TRXS_S1
11. 25-Jul-2005 Bug4513458. added by Lakshmi Gopalsami version 120.2
Issue:
------
TDS tax is always rounded to 2 decimal places
Fix:
----
1) Changed the statement "ln_tax_amount :=
round(pn_tax_amount, 2);"
being considered while selecting the applicable
threshold. This has been modified to check
threshold validity date range against the GL_date of
invoice distributions.
Dependency (Functional)
-----------------------
jai_ap_tds_dflt.plb Version 120.3
14. 18-Aug-2005 Ramananda for bug#4560109 during R12 Sanity Testing. File Verion 120.5
In generate_tds_invoices procedure:
Added the WHO columns in the 'insert into JAI_AP_TDS_INVOICES' statement
15. 19-Aug-2005 Ramananda for bug#4562793. File Version 120.6
1) Moved the Cursor - c_ja_in_tax_codes, up from below the cursor c_po_vendor_sites_all
2) Changed the parameters being passed to cursor - c_po_vendors and c_po_vendor_sites_all
3) In the procedure maintain_thhold_grps, while updating the table - jai_ap_tds_thhold_grps,
changed the update for column - current_threshold_slab_id
Dependency Due to this Bug
--------------------------
No
16. 19-Aug-2005 Ramananda for bug#4562801. File Version 120.6
Following changes are done in procedure - generate_tds_invoices
1) While inserting into table ja_in_ap_tds_invoices, value of column - invoice_amount is changed
2) Calculation for the new added variable - ln_invoice_amount is done
17. 23-Aug-2005 Bug 4559756. Added by Lakshmi Gopalsami Version 120.7
Added org_id in call to ap_utilities_pkg to get the correct gl_date and period_name.
18. 02-Sep-2005 Ramananda for Bug#4584221, File Version 120.8
Made the following changes -
1) Before submitting the request - APXIIMPT,
called the jai_ap_utils_pkg.get_tds_invoice_batch(p_invoice_id) to get the batch_name.
2) In submitting the request - APXIIMPT,
changed the parameter batch_name from hardcoded value to variable - lv_batch_name
Dependency Due to this Bug (Functional)
--------------------------
jai_ap_utils.pls (120.2)
jai_ap_utils.plb (120.2)
jai_ap_tds_old.plb (120.3)
jai_constants.pls (120.3)
jaiorgdffsetup.sql (120.2)
jaivmlu.ldt (120.3)
19. 02-sep-2005 Bug 4774647. Added by Lakshmi Gopalsami version 120.9
Passed operating unit also as this parameter
has been added by base.
20. 07-Dec-2005 Bug 4870243. Added by Harshita version 120.11
Issue : Invoice Distribution Cursor has no filter based on the Invoice_distribution_id ,
line_num and tds_section.
Fix : Added the filter conditions in the filter.
21. 13-Jan-2006 Bug 4943949 Added by Lakshmi Gopalsami 120.13
Issue:
------
Wrong number of arguments while trying to validate
the standard invoice. This is due to the parameter
P_FUNDS_RETURN_CODE added by base in ap_approval_pkg.
Fix:
----
Added the parameter P_FUNDS_RETURN_CODE in call to
ap_approval_pkg.
22. 19-Jan-2006 avallabh for bug 4926736. File version 120.14
Removed the procedure process_tds_batch, since it is no longer used.
23. 27/03/2006 Hjujjuru for Bug 5096787 , File Version 120.15
Spec changes have been made in this file as a part og Bug 5096787.
Now, the r12 Procedure/Function specs is in this file are in
sync with their corrsponding 11i counterparts
24. 03/11/2006 Sanjikum for Bug#5131075, File Version 120.17
1) Changes are done for forward porting of bugs - 4722011, 4718907, 4685754, 5346558
Dependency Due to this Bug
--------------------------
Yes, as Package spec is changed and there are multiple files changed as part of current
25 23/02/07 bduvarag for bug#4716884,File version 120.18
Forward porting the changes done in 11i bug 4629783
bduvarag for bug#4667681,File version 120.18
Forward porting the changes done in 11i bug 4576084
26. 03/05/2007 Bug 5722028. Added by csahoo 120.19
Forward Porting to R12
Added parameter p_creation_date for the follownig procedures
process_tds_at_inv_validate
maintain_thhold_grps
and pd_creation_date in generate_tds_invoices.
Added global variables
gn_tds_rounding_factor
gd_tds_rounding_effective_date and function get_rnded_value
is created.
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.
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
27. 22/06/2007 Bug# 6119216, File Version 120.20
Issue: RTN DOCS ARE NOT GENERATED ON APPLICATION OF PREPAYMENT INVOICE
Fix:
1. Changed where clause of the cursor c_check_not_validate.
2. Changed import_and_approve procedure, here a call to fnd_request.submit_request was passing
p_invoice_id instead of lv_group_id
28. 11/01/2008 Changes done by nprashar for bug # 6720018.
Issue# APAC:PEN:R12:INDIA LOCALIZATION VALIDATING FUNCTION OF TDS INVOICE NOT WORKING.
29. 18/11/2008 Bgowrava for Bug#4549019, File Version 120.16.12000000.12, 120.26.12010000.4, 120.31
Changes done in procedure - generate_tds_invoices
1) Changed the condition - if lv_invoice_to_tds_num is not null and lv_invoice_to_tds_type is not null then
2) Added an if condition before calling - jai_ap_interface_pkg.insert_ap_inv_interface for Supplier invoice
30. 26-Nov-2008 Bgowrava for Bug#7389849, File Version 120.16.12000000.13, 120.26.12010000.5, 120.32
modified code to check the enddate of a tax with the invoice date of an invoice rather than sysdate
31. 02-Mar-2009 Bgowrava for Bug#8254510, file Version 120.16.12000000.15, 120.26.12010000.7, 120.34
Modified the code in the procedure process_threshold_transition to avoid calculating the TDS for
difference in the rate between the rate at which an invoice has suffered earlier and the rate applicable
at the current threshold transition. Also Added code to this procedure to calculate surcharge amount for
all previous invoices in the threshold group when the threshold with hte surcharge applicability is
breached. A TDS invoice with the namin convention of -SUR- is created for the surcharge invoice.
32. 19-May-2009 Bgowrava for Bug#8459564, File Version 120.16.12000000.17, 120.26.12010000.9, 120.36
Issue: INDIA - TDS CERTIFICATES REPORT DOES NOT DISPLAY CORRECT AMOUNT
Fix: Modified the code in the process_threshold_transition procedure to pass the calculated taxable amount
for the additional TDS invoice pair created post threshold transition to the generate_tds_invoices procedure instead of just passing null.
33. 20-Aug-2009 Bgowrava for Bug#8716477, File Version 120.16.12000000.21, 120.26.12010000.13, 120.40
Issue: TDS CR MEMO and INVOICE NOT GENERATED FOR INVOICE HAVING MORE THAN 1000 LINES
Fix: The issue was occuring due to the length of the p_codepath exceeding the maximum length of 1996 and hence the procedure failing due to
numeric or value error. Hence commented the line where codepath was assigned with data without calling the funtion jai_general_pkg.plot_codepath.
also added exception details in the exception block of process_tds_at_inv_validate procedure.
34. 14-Oct-2009 Bgowrava for Bug# 8995604 , File version 120.16.12000000.22, 120.26.12010000.14, 120.41
Issue: CREDIT MEMO INCORRECTLY TRANSLATED FOR FOREIGN CURRENCY TDS INV
Fix : Avoided calling the get_rnded_value function for the vendor credit memo in case of foreign currency. also took care to convert hte INR value of tax to
foreign currency only after the rounding is completed for the INR tax amount by calling the get_rnded_value function.
35. 09-Dec-2009 Bgowrava for Bug#9186263, File version 120.16.12000000.24, 120.26.12010000.16, 120.43
Issue : INVOICE DATE ON RTN STANDARD INVOICE SHOULD BE GL DATE OF PREPAYMENT APPLICATION
Fix : Modified the value passed to the parameter invoice_date to ld_acoounting_date instead of the invoice date of the base invoice.
36. 13-Jan-2010 Xiao for Bug#7154864
the following changes were made for this issue.
1) The cursor c_calculate_tax is modified to retreive default_tax_id also.
2) The cursor c_check_slabs_end_dated is included again. this was earlier moved
to the trigger through the bug5925513.
3) modified c_get_taxes_to_generate_tds to retrieve default tax id when actual_tax_id
null.
4) modified cursor c_get_taxes_to_generate_tds to consider invoices which have a
actual_tax_id defined for generating TDS.
5) Uncommented the code responsible for throwing error when the invoice with an
enddated tax attached is validated
37. 13-Jan-2010 Xiao for Bug#6596019
The following changes are done as per this bug
1) modified c_for_each_tds_section to consider the invoice amount after deducting the
prepayment amount.
2) modified c_get_taxes_to_generate_tds to consider the taxable_amount after
deducting the prepayment amount.
3) Added new cursor to obtain the prepayment amount applied for a particular invoice.
4) Added new cursors c_jai_ap_no_tds_trx and c_jai_no_tds_trx_amt to calculate the
TDS only on invoices which have not suffered TDS and ignore those which have already
suffered TDS, on reaching cumulative limit.
38. 13-Jan-2010 Xiao for Bug#6596019
the code to throw an error when there is no active threshold defined for a
section code and the code to throw error when there are no active slabs available
for a particular threshold, are moved to the begining of the procedure process_tds_at_inv_validate
since it was not getting executed when iside the loop, due to certain conditions not getting satisfied.
40. 13-Jan-2010 Xiao for Bug#6596019
Added code for surcharge in the procedure process_threshold_transition
also added code for creating surcharge invoices when even is SURCHARGE_CALCULATE
in generate_tds_invoices procedure. also resolved the regression created due to earlier
fixes.
41. 13-Jan-2010 Xiao Lv for Bug#8345080, related 11i bug#8333898
Issue: TDS DEDUCTING TWICE
Fix: Added a new procedure get_prepay_appln_amt with PRAGMA AUTONOMOUS TRANSACTION to calculate
the application amount for invoices within the current group which are eligible for TDS
deduction at threshold transition. and this application amount is deducted from the total
amount while calculating the threshold transition amount.
42. 13-Jan-2010 Xiao Lv For Bug#8485691, related 11i bug#8439217
Issue: TDS CALCULATION AT APPLICABLE RATES DURING THRESHOLD TRANSITION
Fix: Modified the code in the procedure process_threshold_transition in the file jai_ap_tds_gen.plb,
to calculate the tax based on the rate applicable on the invoice which had not suffered TDS
earlier.
43. 13-Jan-2010 Xiao Lv For Bug#8513550, related 11i bug#8439276
Issue: ADDITIONAL TDS INV GETTING GENERATED FOR APPLIED PREPAY INV AT THRESHOLD
Fix: Modified the code in the process_threshold_transition procedure in jai_ap_tds_gen.plb. Here
added code to call the get_prepay_appln_amt function even for the invoice which is causing
the threshold transition to detect if any prepayment is attached to this invoice which should
be ignored while calculation of the transition TDS.
44. 14-Jan-2010 Jia For Bug#7431371, related 11i bug#7419533
Issue: FINANCIALS FOR INDIA -TDS NOT WORKING IN CASE OF MULTIPLE DISTRIBUTIONS
Fixed:
1) Modified the code in process get_prepay_invoice_amt.
Handled the case where a prepayment that is getting applied on the standard invoice had a 0% TDS
tax atttached, in which case the TDS on the standard invoice should deduct TDS on difference of
std invoice amount and prepayment invoice amount.
2) Modified the code in process process_tds_at_inv_validate.
Added code in process_tds_at_inv_validate to apply the prepayment amount increamently
in all the distribution lines, till there is sufficient amount left in prepayment to be applied.
Once the prepayment amount is completely applied, TDS or WCt or ESSI invoices get created on the
remaining standard invoice amount based on the TDS, WCT or ESSi tax respectively attached.
45. 14-Jan-2010 Jia For FP Bug#7312295
Issue: This is a forward port bug for the Bug#7252683.
Cancellation of the invoice breaching the surcharge threhsold does not cancel the surcharge invoice
that got created while the transition. this results in wrong surcharge calculation.
Fixed: Modified the code in procedure process_threshold_transition.
Added the nvl conditions to the various tax rates used in the surcharge rate calculation formula.
without this a null value in any one of either cess, or sh cess was leading to no surcharge invoice
getting created, even though surcharge was applicable.
46. 14-Jan-2010 Jia For FP Bug#7368735
Issue: This is a forward port bug for the Bug#7347096.
On attaching a wct tax alone, the error 'Error - Threshold is not defined for the applicable TDS section'
was getting thrown,this is due to the entry that gets created for TDS in jai_ap_tds_inv_taxes also with
no section codes, even though there is no distribution created which has a TDS tax.
Fixed: Modified the cursor c_check_valid_tax in procedure process_tds_at_inv_validate to only loop through
the distributions which have either a default_section_code or actual_section_code defined.
47. 14-Jan-2010 Jia For FP Bug#8278439
Issue: This is a forward port bug for the Bug#8269891.
The Threshold_hdr_id value in jai_ap_tds_thhold_trxs was not being populated, which was leading to
the record missing from the 'India - TDS PAyment Review' report, because the main query of this report
is dependent on the value of threshold_hdr_id column of jai_ap_tds_thhold_trxs.
Fixed: In cases where no default_section_code was specified in the vendor additional information,
the value for default_section_code in jai_ap_tds_inv_taxes was populated as null.
Hence during the execution of the cursor c_check_valid_tax it would fetch no records as the
value for actual_section_code is also null at this point of time. Hence modified this query to
be based on actual_tax_id and default_tax_id rather than the section code.
48. 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 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.
49. 30-Jun-2010 Bug 9759709
Issue: Wrong Liability Account is fetched for the TDS CM. If the Liability account is different from the
Liability account from Supplier Setup, then the account from Setup is fetched.
Fix: Fetched the Liability Account from Standard Invoice.
50. 22-Nov-2010 Bug# 10315928 by amandali
Description:R12 : ETDS FVU CHANGES AS NOTIFIED ON 09-OCT-2010 WRT NEW VERSION OF VALIDATION
Fix: Invoice Validation should fail if PAN is any of 'PANAPPLIED' or 'PANNOTAVBL' or 'PANINVALID'
and rate is not 20% (Higher Rate)
51. 20-jan-11 Bug #11070443 by amandali
Description:WCT IS NOT CALCULATED CORRECTLY WHEN INVOICE IS APPLIED TO PREPAYMENT BEFORE VALIDATION OF SI
52. 01-feb-2011 Bug #11671779 by amandali
Description:THRESHOLD TRANSITION TDS INVOICE IS MISSING IN TDS PAYMENT REVIEW REPORT
Fix:In a call to generate_tds_invoice for Threshold Transition invoices, added the parameter ln_threshold_hdr_id.
Previously it is taking null by default and hence the query in report 'India TDS Payment Review report' is failing for threshold transition invoices.
53. 15-Feb-2011 Bug #9562876 by amandali
Issue: TDS IS NOT GETTING DEDUCTED FOR PROPER AMOUNT
Fix: modified the code in process_threshold_transition procedure. Added new cursors to check if there was a threshold rollback that occured to the old
slab and if ocurred, the threshold transition should consider the rollback amount to create the transition invoice. The cursors added for this purpose are
c_thhold_rollback_occurred and c_get_rollback_amt.
Ported the fix of 9539694
54. 17-Mar-2011 Bug # 11803135 by amandali
Issue:TDS THRESHOLD IS NOT CONSIDERED FOR MANUALLY ATTACHED TAX CODES
Fix:Commented the actual_tax_id not null condition in the cursor c_get_taxes_to_generate_tds.
This would check for the threshold limit for both manually attached and defaulted tax codes.
55. 26-May-2011 Bug 11880998
Description: In an Invoice is canceled without validation prior to Threshold Breach it results in negative lines
in jai_ap_tds_inv_taxes and the same corrupts the Threshold Transition Invoice
Fix: Since the Invoice that is canceled without validation never hits the Threshold the same
needs to be skipped when picking invoices for Threshold Transition.
Modified cursor c_jai_ap_no_tds_trx to skip invoices based on AUDIT entries.
When an Invoice is canceled without validation the Transaction amounts in Audit tables are zero.
56. 23-Aug-2011 mmurtuza for bug12858951
Description: TDS CALCULATED FOR SERVICE TAX LINES WHEN THRESHOLD CROSSED
Fix: Added the condtion nvl(consider_amt_for_tds, 'Y') = 'Y' in al the statements involving jai_ap_tds_inv_taxes table
in process_tds_at_inv_validate procedure to pick invoices for tds invoice generation.
57. 14-Oct-2011 amandali for bug 13070779
Description:Wrong taxable amount shown for prepayment application on unvalidated standard invoice.
Fix:Commented out the assignment of pn_taxable_amount to ln_invoice_Amount as it is having the total invoice amount and not the net amount.
58. 02-FEB-2012 mmurtuza for bug 13561970
Description: Threshold rollback functionality is not working properly.
Fix: Modified cursor c_threshold_passed in procedure process_threshold_transition
Commented code for cursors c_thhold_rollback_occurred and c_get_rollback_amt in procedure process_threshold_transition
Commended code for cursor get_thhold_transn as Threshold Transition amount need not be subtracted to calculate the amount for which Threshold Rollback needs to be generated
59. 20-Feb-2012 amandali for bug 13715894
Description:No validation for TDS fin year setup during validation
Fix:Added org_id condition in cursor c_get_fin_year
60. 29-Mar-2012 amandali for bug 13840176
Description: WRONG TDS CALCULATION IS HAPPENING FOR THE PO MATCHED INVOICE WHICH ARE HAVING IPV line
Fix: As the IPV line would have a negative amount, ln_application_amount is not updated and so the application amount
of previous distribution is deducted for this line leading to wrong calculation of TDS.So, initialised application_amount to zero for every distribution_id
61 27-Jul-2012 amandali for bug 14369937
Description:Threshold TDS amount is wrongly calculated
Fix:Added few conditions to the cursor c_jai_no_tds_trx_amt in procedure process_threshold_transition to calculate the correct taxable amount
62. 16-Nov-2012 amandali for bug 14709400
Description:TDS threhsold transition amount is calculated wrongly
Fix:Modified the cursor c_jai_ap_tds_thhold_taxes in procedure process_threshold_transition
63. 29-Nov-2012 amandali for Bug:15918300
Description:Wrong threshold transition amount when an invoice has both TDS and WCT taxes attached.
Fix: Added section type condition in cursor c_default_tax_id in procedure process_threshold_transition
64. 28-Feb-2013 mmurtuza for bug 16016037
Description: UNABLE TO APPLY PREPAYMENTS TO THE STANDARD INVOICES
Fix: Commented the condition of p_match_status_flag before checking for Holds in procedure status_update_chk_validate
65. 12-Mar-2013 mmurtuza for bug 16435622
Description: INDIA - TDS PAYMENT REVIEW DOES NOT RESTRICT THE DATA BASED ON SECTION CODE
Fix: Uncommented the use of cursor c_get_threshold in cursor for loop c_for_each_tds_section. Also assigned null to r_jai_ap_tds_thhold_slabs before opening cursor c_jai_ap_tds_thhold_slabs
---------------------------------------------------------------------------- */
/*Added Procedure below for Xiao for Bug#7154864*/
/*Added pn_section_type parameter to get_prepay_invoice_amt - Bug 11070443*/
procedure get_prepay_invoice_amt(pn_invoice_id NUMBER, pn_section_type VARCHAR2, pn_prepay_amt OUT NOCOPY NUMBER)
is
PRAGMA AUTONOMOUS_TRANSACTION;
select prepay_distribution_id, sum(amount) amount --Modified by Jia for FP Bug#7431371
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
--Added the following clause to select the prepayment amounts with the same section type as the distribution - Bug 11070443
and
((pn_section_type = 'TDS_SECTION' and global_attribute1 is not null)
or (pn_section_type = 'WCT_SECTION' and global_attribute2 is not null)
or (pn_section_type = 'ESSI_SECTION' and global_attribute3 is not null))
and prepay_distribution_id is not null
group by prepay_distribution_id; --Addec by Jia for FP Bug#7431371
select invoice_id
from ap_invoice_distributions_all
where invoice_distribution_id = p_prepay_dist_id;
select 1 from
jai_ap_tds_thhold_trxs where
invoice_id = p_invoice_id;
select 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 tax_rate
from jai_cmn_taxes_all
where tax_id = p_tax_id;
select sum(a.application_amount) application_amount, d.threshold_grp_id, d.invoice_id
from jai_ap_tds_prepayments a,
ap_invoice_distributions_all b,
ap_invoice_distributions_all c,
jai_ap_tds_inv_taxes d
where a.invoice_distribution_id_prepay = b.invoice_distribution_id
and b.prepay_distribution_id = c.invoice_distribution_id
and nvl(a.unapply_flag, 'N') <> 'Y'
and c.invoice_id = cp_invoice_id --modified by Xiao Lv for Bug#8513550, related 11i bug#8439276
and c.invoice_id= d.invoice_id
group by d.threshold_grp_id, d.invoice_id;
select sum(application_amount) amount
from jai_ap_tds_prepayments a,
ap_invoice_distributions_all b
where a.invoice_id = cp_invoice_id
and a.invoice_id = b.invoice_id
and a.invoice_distribution_id_prepay = b.invoice_distribution_id
and prepay_distribution_id is not null
and nvl(a.unapply_flag, 'N') <> 'Y'
and exists (select 1
from jai_ap_tds_inv_taxes jatit, ap_invoice_distributions_all aida
where aida.invoice_distribution_id = b.prepay_distribution_id
and aida.invoice_id = jatit.invoice_id
and aida.invoice_distribution_id = jatit.invoice_distribution_id
and jatit.threshold_slab_id_single is not null
and jatit.threshold_trx_id is not null
);
select abs(amount) amount, invoice_distribution_id, prepay_distribution_id
from ap_invoice_distributions_all
where invoice_id = cp_invoice_id
and line_type_lookup_code = 'PREPAY';
select threshold_grp_id
from jai_ap_tds_inv_taxes
where invoice_distribution_id = cp_invoice_dist_id;
select org_id into p_org_id
from ap_invoices_all
where invoice_id = p_invoice_id;
procedure status_update_chk_validate
(
p_invoice_id in number,
p_invoice_line_number in number default null, /* AP lines uptake */
p_invoice_distribution_id in number default null,
p_match_status_flag in varchar2 default null,
p_is_invoice_validated out nocopy varchar2,
p_process_flag out nocopy varchar2,
p_process_message out nocopy varchar2,
p_codepath in out nocopy varchar2
)
is
lv_section_type VARCHAR2(15) ;
select count(tds_inv_tax_id) total_count, sum(decode(match_status_flag, 'A', 1, 0)) validated_a_count,
sum(decode(match_status_flag, 'T', 1, 0)) validated_t_count
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
-- Harshita for Bug 4870243
and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, invoice_line_number)
and invoice_distribution_id = nvl(p_invoice_distribution_id, invoice_distribution_id) -- Bug 6119216
and section_type = p_section_type ;
select nvl(purch_encumbrance_flag, 'N')
from FINANCIALS_SYSTEM_PARAMS_ALL
where org_id = p_org_id;
/*select tds_inv_tax_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
and nvl(invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
and section_type = p_section_type; */
select count(invoice_id)
from ap_holds_all
where invoice_id = p_invoice_id
and release_reason is null;
p_codepath := jai_general_pkg.plot_codepath(1, p_codepath, 'jai_ap_tds_generation_pkg.status_update_chk_validate', 'START'); /* 1 */
update jai_ap_tds_inv_taxes
set match_status_flag = p_match_status_flag
where invoice_id = p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id;
fnd_file.put_line(FND_FILE.LOG, 'Status_update_chk_validate - Status of parent invoice '|| p_is_invoice_validated);
P_process_message := 'jai_ap_tds_generation_pkg.status_update_chk_validate :' || sqlerrm;
end status_update_chk_validate;
select count(tds_inv_tax_id)
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and (actual_tax_id is not null or default_tax_id is not null);
select count(tds_inv_tax_id)
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and process_status = p_process_status;
select tds_inv_tax_id, actual_tax_id,default_tax_id, amount, invoice_distribution_id,section_type --Xiao for Bug#7154864
/*Added Section Type - Bug 11070443*/
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and (default_tax_id is not null or actual_tax_id is not null); --Xiao for Bug#7154864
select tax_rate,
section_code,
end_date,
sysdate,
'Tax : ' || tax_name || ' is end dated as on ' || to_char(end_date, 'dd-mon-yyyy') ||
'. Setup needs modification.' tax_end_dated_message
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
/*Bug 5751783 - Selected non-rounded value for calculation*/
cursor c_for_each_tds_section(p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type, p_prepay_amt number) is--rchandan for bug#4428980 --add by xiao for bug#6596019
select actual_section_code, (sum(amount*p_exchange_rate)-p_prepay_amt) invoice_amount, sum(calc_tax_amount) section_amount,
sum(tax_amount) tax_amount_orig
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = p_section_type --rchandan for bug#4428980
and actual_section_code is not null
group by actual_section_code;
select tds_vendor_type_lookup_code
from JAI_AP_TDS_VNDR_TYPE_V
where vendor_id = p_vendor_id;
select threshold_hdr_id
from JAI_AP_TDS_TH_VSITE_V
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and section_type = p_section_type --rchandan for bug#4428980
and section_code = p_tds_section_code;
select threshold_grp_id
from jai_ap_tds_thhold_grps
where vendor_id = p_vendor_id
and section_type = p_section_type --rchandan for bug#4428980
and section_code = p_tds_section_code
and org_tan_num = p_tan_no
and vendor_pan_num = p_pan_no
and fin_year = p_fin_year;
select (
nvl(total_invoice_amount, 0) -
nvl(total_invoice_cancel_amount, 0) -
nvl(total_invoice_apply_amount, 0) +
nvl(total_invoice_unapply_amount, 0)
)
total_invoice_amount,
total_tax_paid,
total_thhold_change_tax_paid,
current_threshold_slab_id,
/*Bug 5751783. Selected non-rounded value for calculation*/
total_calc_tax_paid
from jai_ap_tds_thhold_grps
where threshold_grp_id = p_threshold_grp_id;
select threshold_slab_id, threshold_type_id, from_amount, to_amount
from jai_ap_tds_thhold_slabs
where threshold_hdr_id = p_threshold_hdr_id
and threshold_type_id in
( select threshold_type_id
from jai_ap_tds_thhold_types
where threshold_hdr_id = p_threshold_hdr_id
and threshold_type = p_threshold_type
/* Bug 4522540. Added by Lakshmi Gopalsami
Added the following date condition */
and trunc(p_accounting_Date) between from_date
and nvl(to_date, p_accounting_date + 1)
)
and p_amount between from_amount and nvl(to_amount, p_amount)
order by from_amount asc;
select 1
from jai_ap_tds_thhold_types
where threshold_hdr_id = p_threshold_hdr_id
and trunc(p_accounting_Date) between from_date and nvl(to_date, p_accounting_date + 1);
/*Bug 5751783. Selected non-rounded value for calculation*/
cursor c_get_taxes_to_generate_tds
(p_invoice_id number, p_tds_section_code varchar2, p_generate_all_invoices varchar2,
p_exchange_rate number, p_threshold_slab_id_single number,p_section_type jai_ap_tds_inv_taxes.section_type%type, p_prepay_amt number) IS --rchandan for bug#4428980--add by xiao for bug#6596019
select nvl(actual_tax_id,default_tax_id) actual_tax_id, --added nvl by Xiao for Bug#7154864
( sum(amount*p_exchange_rate)-p_prepay_amt) taxable_amount, --Xiao for bug#6596019
sum(calc_tax_amount) tax_amount,
sum(tax_amount) tax_amount_orig
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = p_section_type --rchandan for bug#4428980
and actual_section_code = p_tds_section_code
and (
(p_generate_all_invoices = 'Y' )
or
(p_threshold_slab_id_single > 0 )
-- or/*Commented the below condition for bug 11803135*/
-- (actual_tax_id is NOT NULL) --added by Xiao for Bug#7154864
/*FP of QA Bug 12431997 - Included in 11896260 - Tax to be deducted irrespective of Threshold if PAN is not available*/
OR
EXISTS (SELECT 1
FROM jai_ap_tds_vendor_hdrs
WHERE (pan_no IN ('PANAPPLIED','PANNOTAVBL','PANINVALID')
OR (pan_no IS NOT NULL AND confirm_pan_flag IS NULL))
AND vendor_site_id = 0
AND vendor_id = (SELECT vendor_id
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id
)
)
)
group by nvl(actual_tax_id, default_tax_id); --added nvl by Xiao for Bug#7154864
select c.pan_no pan_no,
d.org_tan_num tan_no,
c.confirm_pan_flag confirm_pan_flag /*Fixing QA Bug 12431997 - Included in 11896260*/
from po_vendors a,
po_vendor_sites_all b,
JAI_AP_TDS_VENDOR_HDRS c,
jai_ap_tds_org_tan_v d --rchandan for bug#4323338
where a.vendor_id = b.vendor_id
and b.vendor_id = c.vendor_id
and b.vendor_site_id = c.vendor_site_id
and b.org_id = d.organization_id
and a.vendor_id = p_vendor_id
and b.vendor_site_id = p_vendor_site_id;
select fin_year
from JAI_AP_TDS_YEARS
where tan_no in /* where clause and subquery added by ssumaith - bug# 4448789*/
(
SELECT attribute_value
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = lv_tds_regime
AND registration_type = lv_regn_type_others
AND attribute_type_code = lv_attr_type_Code
AND attribute_code = lv_attr_code
AND organization_id = p_org_id
)
and legal_entity_id = p_org_id /* Added the condition for bug 13715894 */
and p_accounting_date between start_date and end_date;
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
/*Bug 5751783. Selected non-rounded value for calculation*/
cursor c_get_non_tds_section_tax (p_invoice_id number, p_exchange_rate number,p_section_type jai_ap_tds_inv_taxes.section_type%type) IS --rchandan for bug#4428980
select section_type,
actual_tax_id,
sum(amount*p_exchange_rate) taxable_amount,
sum(calc_tax_amount) tax_amount,
sum(tax_amount) tax_amount_orig
from jai_ap_tds_inv_taxes
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type <> p_section_type --rchandan for bug#4428980
and actual_tax_id is not null
group by section_type, actual_tax_id;
select jatts.from_amount from_amount,
jatts.tax_rate tax_rate,
jattt.tax_id tax_id,
(jitc.tax_rate-(nvl(jitc.surcharge_rate,0) + nvl(jitc.cess_rate,0) + nvl(jitc.sh_cess_rate,0))) tax_rate_orig
from jai_ap_tds_thhold_slabs jatts,
jai_ap_tds_thhold_taxes jattt,
jai_cmn_taxes_all jitc
where jatts.threshold_slab_id = jattt.threshold_slab_id
and jattt.tax_id = jitc.tax_id
and jatts.threshold_slab_id = p_threshold_slab_id;
select amount_paid
from ap_invoices_all
where invoice_id = p_invoice_id;
select actual_tax_id, default_tax_id
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and section_type = 'TDS_SECTION' ;
select nvl(actual_section_code, default_section_code) section_code
from jai_ap_tds_inv_taxes
where section_type = 'TDS_SECTION'
and invoice_id = p_invoice_id
and (actual_section_code is not null or default_section_code is not null);
select jitc.section_code section_code
from jai_cmn_taxes_all jitc, jai_ap_tds_inv_taxes jatit
where jitc.tax_id = nvl(jatit.actual_tax_id, jatit.default_tax_id)
and jatit.section_type = 'TDS_SECTION'
and jatit.invoice_id = p_invoice_id
and (actual_tax_id is not null or default_tax_id is not null);
select sum(application_amount)
from jai_ap_tds_prepayments
where invoice_distribution_id = p_invoice_distribution_id
and (
(p_section_type = 'TDS_SECTION' and nvl(tds_applicable_flag, '*') = 'Y') or
(p_section_type = 'WCT_SECTION' and nvl(wct_applicable_flag, '*') = 'Y') or
(p_section_type = 'ESSI_SECTION' and nvl(essi_applicable_flag, '*') = 'Y')
)
and nvl(unapply_flag, 'N') <> 'Y';
/* Update actual value from default value if actual is null for TDS section taxes only*/
p_codepath := jai_general_pkg.plot_codepath(5, p_codepath); /* 5 */
update jai_ap_tds_inv_taxes
set actual_tax_id = default_tax_id
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and actual_tax_id is null
and user_deleted_tax_flag IS NOT NULL AND user_deleted_tax_flag <> 'Y' -- nvl(user_deleted_tax_flag, 'N') <> 'Y'
and section_type = lv_tds_section_type; --rchandan for bug#4428980
/* Update processed for those cases where NO TDS has to be deducted for TDS section taxes only */
update jai_ap_tds_inv_taxes
set process_status = 'P'
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = lv_tds_section_type --rchandan for bug#4428980
and nvl(user_deleted_tax_flag, 'N') = 'Y';
p_process_message := 'Invalid PAN, deduction at higher rate is not selected';
fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 value of tax_amount before update'||ln_tax_amount);
update jai_ap_tds_inv_taxes
set tax_amount = ln_tax_amount, -- ln_tmp_tds_amt, -- Bug 5722028
actual_section_code = r_ja_in_tax_codes.section_code,
calc_tax_amount = ln_tax_amount --Added by Bgowrava for bug#7154864
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and tds_inv_tax_id = cur_rec.tds_inv_tax_id;
/* Update the total tax amount for which invoice was raised */
/* bug 7280925. Added by Lakshmi Gopalsami
* changed from ln_tmp_tds_amt to ln_tax_amount
*/
p_codepath := jai_general_pkg.plot_codepath(23, p_codepath); /* 23 */
update jai_ap_tds_inv_taxes
set threshold_trx_id = ln_threshold_trx_id,
threshold_slab_id_single = ln_threshold_slab_id_single
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = lv_tds_section_type --rchandan for bug#4428980
and actual_section_code = cur_rec_section.actual_section_code
and nvl(actual_tax_id, default_tax_id) = cur_rec.actual_tax_id --Added nvl by Xiao for bug#7154864
and (
(lv_generate_all_invoices = 'Y' )
or
(ln_threshold_slab_id_single > 0)
or
(actual_tax_id is NOT NULL) --added by Xiao for bug#7154864
);
update jai_ap_tds_inv_taxes
set threshold_grp_id = ln_threshold_grp_id,
threshold_hdr_id = ln_threshold_hdr_id,
threshold_slab_id = ln_threshold_slab_id_after,
process_status = 'P'
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = lv_tds_section_type --rchandan for bug#4428980
and actual_section_code = cur_rec_section.actual_section_code;
update jai_ap_tds_inv_taxes
set threshold_trx_id = ln_threshold_trx_id,
process_status = 'P' /*Bug 4667681*/
where nvl(consider_amt_for_tds, 'Y') = 'Y' -- Added by mmurtuza for bug12858951
and invoice_id = p_invoice_id
and section_type = cur_non_tds_rec.section_type
and actual_tax_id = cur_non_tds_rec.actual_tax_id;
select invoice_num,
vendor_id,
vendor_site_id,
invoice_currency_code,
exchange_rate_type,
exchange_date,
terms_id,
payment_method_lookup_code,
pay_group_lookup_code,
invoice_date,
goods_received_date,
invoice_received_date,
org_id,
nvl(exchange_rate, 1) exchange_rate,
set_of_books_id,
payment_method_code, -- Bug 7109056
accts_pay_code_combination_id -- Bug 9759709
from ap_invoices_all
where invoice_id = cp_invoice_id;
select terms_id,
--payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
pay_group_lookup_code
from po_vendor_sites_all
where vendor_id = cp_vendor_id
and vendor_site_id = cp_vendor_site_id;
select terms_id,
--payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
pay_group_lookup_code
from po_vendors
where vendor_id = cp_vendor_id;
select section_code,
vendor_id,
vendor_site_id,
tax_rate,
stform_type,
tax_account_id,
section_type
from JAI_CMN_TAXES_ALL
where tax_id = pn_tax_id;
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select to_char(JAI_AP_TDS_THHOLD_TRXS_S1.nextval)--to_char(JAI_AP_TDS_INVOICE_NUM_S.nextval)commented by rchandan for bug#4487676
from dual;
select payment_priority
from ap_payment_schedules_all
where invoice_id = p_invoice_id;
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_tds_id,
p_invoice_num => lv_invoice_to_tds_num,
p_invoice_type_lookup_code => lv_invoice_to_tds_type,
p_invoice_date => ld_accounting_date, --Bug 10020606 - Replaced Base Supplier Invoice Date with GL Date
p_gl_date => ld_accounting_date, --Added by Bgowrava for Bug#9186263
p_vendor_id => r_ja_in_tax_codes.vendor_id,
p_vendor_site_id => r_ja_in_tax_codes.vendor_site_id,
p_invoice_amount => ln_invoice_to_tds_amount,
p_invoice_currency_code => r_gl_sets_of_books.currency_code,
p_exchange_rate => null,
p_exchange_rate_type => null,
p_exchange_date => null,
p_terms_id => ln_terms_id,
p_description => lv_invoice_to_tds_num,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_source => lv_source,
p_voucher_num => lv_invoice_to_tds_num,
--p_payment_method_lookup_code => lv_payment_method_lookup_code,
--commented by Sanjikum for Bug#4482462
p_pay_group_lookup_code => lv_pay_group_lookup_code,
p_org_id => r_ap_invoices_all.org_id,
p_attribute_category => 'India Original Invoice for TDS',
p_attribute1 => pn_invoice_id,
--added the below by Sanjikum for Bug#5131075(4722011)
p_group_id => lv_group_id -- Bug# 6119216, changed to lv_group_id instead of to_char(p_invoice_id)
);
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_tds_id,
p_invoice_line_id => ln_invoice_to_tds_line_id,
p_line_number => 1,
p_line_type_lookup_code => lv_invoice_to_tds_line_type,
p_amount => ln_invoice_to_tds_amount,
p_accounting_date => ld_accounting_date,
p_description => lv_invoice_to_tds_num,
p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
fnd_file.put_line(FND_FILE.LOG, ' CALL utils for inserting interface lines');
parameter p_accts_pay_ccid in jai_ap_utils_pkg.insert_ap_inv_interface would cause it to default
from setup*/
if (r_ap_invoices_all.accts_pay_code_combination_id is not NULL) then
ln_accts_pay_ccid := r_ap_invoices_all.accts_pay_code_combination_id;
jai_ap_utils_pkg.insert_ap_inv_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_vendor_id,
p_invoice_num => lv_invoice_to_vendor_num,
p_invoice_type_lookup_code => lv_invoice_to_vendor_type,
p_invoice_date => pv_invoice_date,--r_ap_invoices_all.invoice_date, --Modified to ld_accounting_date for Bug#9186263
p_gl_date => ld_accounting_date,
p_vendor_id => r_ap_invoices_all.vendor_id,
p_vendor_site_id => r_ap_invoices_all.vendor_site_id,
p_invoice_amount => ln_invoice_to_vendor_amount,
p_invoice_currency_code => r_ap_invoices_all.invoice_currency_code,
p_exchange_rate => ln_exchange_rate,
p_exchange_rate_type => r_ap_invoices_all.exchange_rate_type,
p_exchange_date => r_ap_invoices_all.exchange_date,
p_terms_id => r_ap_invoices_all.terms_id,
p_description => lv_invoice_to_vendor_num,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_source => lv_source,
p_voucher_num => lv_invoice_to_vendor_num,
-- Bug 7109056. Added by Lakshmi Gopalsami
p_payment_method_code => r_ap_invoices_all.payment_method_code,
--commented by Sanjikum for Bug#4482462
p_pay_group_lookup_code => r_ap_invoices_all.pay_group_lookup_code,
p_goods_received_date => r_ap_invoices_all.goods_received_date,
p_invoice_received_date => r_ap_invoices_all.invoice_received_date,
p_org_id => r_ap_invoices_all.org_id,
p_attribute_category => 'India Original Invoice for TDS',
p_attribute1 => pn_invoice_id,
--commented the above and added the below by Sanjikum for Bug#5131075(4722011)
p_group_id => lv_group_id,
p_accts_pay_ccid => ln_accts_pay_ccid /*Bug 9759709*/
);
jai_ap_utils_pkg.insert_ap_inv_lines_interface
(
p_jai_source => lv_this_procedure,
p_invoice_id => ln_invoice_to_vendor_id,
p_invoice_line_id => ln_invoice_to_vendor_line_id,
p_line_number => 1,
p_line_type_lookup_code => lv_invoice_to_vendor_line_type,
p_amount => ln_invoice_to_vendor_amount,
p_accounting_date => ld_accounting_date,
p_description => lv_invoice_to_vendor_num,
p_dist_code_combination_id => r_ja_in_tax_codes.tax_account_id,
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id
);
* in the insert to jai_ap_tds_thhold_trxs. This has to be derived irrespective
* of the tds event.
*/
Fnd_File.put_line(Fnd_File.LOG, 'pn_taxable_amount '||pn_taxable_amount);
insert into JAI_AP_TDS_INVOICES
(TDS_INVOICE_ID,
invoice_id,
invoice_amount,
tds_invoice_num,
dm_invoice_num,
tds_tax_id,
tds_tax_rate,
tds_amount,
tds_section,
certificate_number,
--org_id,
organization_id,
source_attribute,
/* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
( JAI_AP_TDS_INVOICES_S.nextval,
pn_invoice_id,
--round(ln_invoice_to_tds_amount * ( 100 / r_ja_in_tax_codes.tax_rate), 2),
--commented the above and added the below by Ramananda for Bug#4562801
ln_invoice_amount,
lv_invoice_to_tds_num,
lv_invoice_to_vendor_num,
pn_tax_id,
r_ja_in_tax_codes.tax_rate,
ln_invoice_to_tds_amount,
r_ja_in_tax_codes.section_code,
r_ja_in_tax_codes.stform_type,
--r_ap_invoices_all.org_id,
r_ap_invoices_all.org_id,
lv_source_attribute, --rchandan for bug#4428980
/* Ramananda for bug#4560109 during R12 Sanity Testing. Added the WHO columns */
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
fnd_file.put_line(FND_FILE.LOG, ' Bug 7280925 - value after round before insert into trxs'||ln_tds_rnded_amt);
insert into jai_ap_tds_thhold_trxs
(
threshold_trx_id ,
invoice_id ,
invoice_line_number ,
invoice_distribution_id ,
threshold_grp_id ,
threshold_hdr_id ,
tds_event ,
tax_id ,
tax_rate ,
taxable_amount ,
tax_amount ,
tds_authority_vendor_id ,
tds_authority_vendor_site_id ,
invoice_to_tds_authority_num ,
invoice_to_tds_authority_type ,
invoice_to_tds_authority_curr ,
invoice_to_tds_authority_amt ,
vendor_id ,
vendor_site_id ,
invoice_to_vendor_num ,
invoice_to_vendor_type ,
invoice_to_vendor_curr ,
invoice_to_vendor_amt ,
parent_inv_payment_priority ,
parent_inv_exchange_rate ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
calc_tax_amount , /*Bug 5751783*/
tds_rounding_factor -- Bug 5722028. Added by csahoo
)
values
(
jai_ap_tds_thhold_trxs_s.nextval ,
pn_invoice_id ,
pn_invoice_line_number ,
pn_invoice_distribution_id ,
pn_threshold_grp_id ,
pn_threshold_hdr_id ,
pv_tds_event ,
pn_tax_id ,
r_ja_in_tax_codes.tax_rate ,
/* Bug 5751783. Changed to ln_invoice_amount instead of pn_taxable_amount
* This is done as now pn_taxable_amount will always be populated irrespective
* of tds_event. Added rounding for pn_tax_amount.
*/
ln_invoice_amount ,
ln_tds_rnded_amt, --Bug 5722028. Added by csahoo
r_ja_in_tax_codes.vendor_id ,
r_ja_in_tax_codes.vendor_site_id ,
lv_invoice_to_tds_num ,
lv_invoice_to_tds_type ,
r_gl_sets_of_books.currency_code ,
ln_invoice_to_tds_amount ,
r_ap_invoices_all.vendor_id ,
r_ap_invoices_all.vendor_site_id ,
lv_invoice_to_vendor_num ,
lv_invoice_to_vendor_type ,
r_ap_invoices_all.invoice_currency_code ,
ln_invoice_to_vendor_amount ,
r_ap_payment_schedules_all.payment_priority ,
r_ap_invoices_all.exchange_rate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
decode ( pv_tds_event, 'PREPAYMENT UNAPPLICATION', pn_calc_tax_amount, pn_tax_amount), /*Added decode condition for bug 12965614 */ /*Bug 5751783*/
ln_tds_rnding_factor -- Bug 5722028. Added by csahoo
)
returning threshold_trx_id into pn_threshold_trx_id;
select tax_id
from jai_ap_tds_thhold_taxes
where threshold_slab_id = p_threshold_slab_id
and operating_unit_id = p_org_id;
select jatit.invoice_id from jai_ap_tds_inv_taxes jatit
where jatit.threshold_grp_id = cp_threshold_grp_id
and jatit.tax_amount <> 0
/*Bug 11880998 - Skip Invoices that are canceled without validation
Such Invoices do not update Threshold Audits*/
and NOT EXISTS (SELECT 1
FROM jai_ap_tds_thgrp_audits jatta
WHERE jatit.invoice_id = jatta.invoice_id
AND (jatta.trx_invoice_amount = 0 OR jatta.trx_invoice_cancel_amount = 0)
AND EXISTS (SELECT 1
FROM ap_invoices_all aia
WHERE aia.invoice_id = jatit.invoice_id
AND aia.cancelled_date IS NOT NULL)
)
minus
/* select invoice_id from jai_ap_tds_thhold_trxs
where threshold_grp_id = cp_threshold_grp_id; */ /* Commented and added below select for bug 14709400 */
select invoice_id from jai_ap_tds_inv_taxes where invoice_id=p_invoice_id
or (threshold_grp_id = cp_threshold_grp_id and nvl(threshold_slab_id_single,-99)<>-99 and threshold_trx_id is not null);
select count(*) from jai_ap_tds_thhold_trxs /*Bug 9562876*/
where threshold_grp_id = cp_threshold_grp_id
and tds_event like 'THRESHOLD%';
select sum(amount) amount, sum(tax_amount) tax_amount
from jai_ap_tds_inv_taxes
where invoice_id = cp_invoice_id
and section_type='TDS_SECTION' /* Added for bug 14369937 */
and actual_section_code is not null /* Added for bug 14369937 */
group by invoice_id;
select invoice_id, tds_event, tax_id, tax_rate, taxable_amount, tax_amount
from jai_ap_tds_thhold_trxs
where threshold_grp_id = cp_threshold_grp_id;
select nvl(cancelled_amount,0)
from ap_invoices_all
where invoice_id=cp_invoice_id;
select threshold_hdr_id
from JAI_AP_TDS_TH_VSITE_V
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and section_type = 'TDS_SECTION'
and section_code = p_tds_section_code;
select 1
from jai_ap_tds_thhold_trxs
where threshold_grp_id = cp_threshold_grp_id
and tds_event like 'SURCHARGE_CALCULATE';
select (
nvl(total_invoice_amount, 0) -
nvl(total_invoice_cancel_amount, 0) -
nvl(total_invoice_apply_amount, 0) +
nvl(total_invoice_unapply_amount, 0)
)
total_invoice_amount,
total_tax_paid,
/*Bug 5751783. Selected non-rounded value for calculation*/
total_calc_tax_paid
from jai_ap_tds_thhold_grps
where threshold_grp_id = cp_threshold_grp_id;
select tax_rate, surcharge_rate, cess_rate, sh_cess_rate, section_code --Added surcharge_rate, cess_rate, sh_cess_rate, section_code by Bgowrava for bug#8254510
from JAI_CMN_TAXES_ALL
where tax_id = cp_tax_id;
SELECT SUM (NVL (jatit.amount , 0 ) )
FROM jai_ap_tds_inv_taxes jatit
WHERE jatit.threshold_grp_id = cp_threshold_grp_id
AND match_status_flag = 'A'
AND jatit.process_status = 'P'
AND jatit.tax_amount IS NOT NULL
AND jatit.threshold_trx_id IS NULL
AND jatit.threshold_slab_id = 0
AND ( jatit.actual_tax_id IS NOT NULL OR
( jatit.actual_taX_id IS NULL
AND jatit.default_tax_id IS NOT NULL
)
)
AND EXISTS /* check whether iinvoice is not cancelled*/
( SELECT invoice_id
FROM ap_invoices_all ai
WHERE ai.invoice_id = jatit.invoice_id
AND ai.cancelled_Date IS NULL
AND ai.cancelled_amount IS NULL
);
SELECT SUM(NVL(jattt.taxable_amount,0))
FROM jai_ap_tds_thhold_trxs jattt
WHERE jattt.threshold_grp_id = cp_threshold_grp_id
AND (jattt.tds_event like 'THRESHOLD ROLLBACK%');
SELECT SUM (NVL (jatp.application_amount, 0 ))
FROM jai_ap_tds_prepayments jatp
WHERE jatp.tds_threshold_grp_id = cp_threshold_grp_id
AND jatp.tds_applicable_flag = 'Y'
AND jatp.tds_threshold_trx_id_apply IS NULL
AND (jatp.unapply_flag IS NULL OR jatp.unapply_flag = 'N') ;
select nvl(actual_tax_id, default_tax_id)
from jai_ap_tds_inv_taxes
where invoice_id = cp_invoice_id
and section_type = 'TDS_SECTION'; /* Bug 15918300 */
SELECT 1
FROM jai_ap_tds_thgrp_audits
WHERE threshold_grp_id = cp_threshold_grp_id
AND tds_Event like 'THRESHOLD ROLLBACK%'
AND new_threshold_slab_id = cp_new_thhold_slab;
select ((tax_amount/tax_rate)*100) taxable_amount, tax_amount
from jai_Ap_tds_thhold_trxs
where invoice_id = (select invoice_id
from jai_ap_tds_thgrp_audits
where threshold_grp_audit_id = (select max(threshold_grp_audit_id)
from jai_Ap_tds_thgrp_Audits
where tds_event like 'THRESHOLD ROLLBACK%'
and new_threshold_slab_id = cp_new_thhold_slab
and threshold_grp_id = cp_threshold_grp_id)
)
and tds_event like 'THRESHOLD ROLLBACK%';
/* Update the total tax amount for which invoice was raised */
ln_threshold_grp_id:= p_threshold_grp_id;
/* Update the total tax amount for which invoice was raised */
ln_threshold_grp_id:= p_threshold_grp_id;
select vendor_id,
vendor_site_id,
org_id
from ap_invoices_all
where invoice_id = p_invoice_id;
select nvl( approved_invoice_flag, 'N' ) approved_invoice_flag
from JAI_CMN_VENDOR_SITES
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id;
select invoice_to_tds_authority_id,
invoice_to_vendor_id,
invoice_to_tds_authority_num,
invoice_to_vendor_num
from jai_ap_tds_thhold_trxs
where threshold_trx_id >= p_start_thhold_trx_id
and invoice_id = p_invoice_id
and vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id;
SELECT invoice_id, org_id,
set_of_books_id -- bug 6819855. Added by Lakshmi Gopalsami
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
select threshold_trx_id,
invoice_id
from jai_ap_tds_thhold_trxs
where invoice_to_tds_authority_num = p_invoice_num
and tds_authority_vendor_id = p_vendor_id
and tds_authority_vendor_site_id = p_vendor_site_id
and invoice_to_tds_authority_id is null;
select threshold_trx_id
from jai_ap_tds_thhold_trxs
where invoice_to_vendor_num = p_invoice_num
and vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id
and invoice_to_vendor_id is null;
update jai_ap_tds_thhold_trxs
set invoice_to_tds_authority_id = p_invoice_id
where threshold_trx_id = ln_threshold_trx_id;
update jai_ap_tds_thhold_trxs
set invoice_to_vendor_id = p_invoice_id
where threshold_trx_id = ln_threshold_trx_id;
select total_invoice_amount ,
total_invoice_cancel_amount ,
total_invoice_apply_amount ,
total_invoice_unapply_amount ,
total_tax_paid ,
total_thhold_change_tax_paid ,
current_threshold_slab_id ,
total_calc_tax_paid -- Bug 5751783
from jai_ap_tds_thhold_grps
where threshold_grp_id = p_threshold_grp_id;
select threshold_grp_id
from jai_ap_tds_thhold_grps
where vendor_id = p_vendor_id and
org_tan_num = p_org_tan_num and
vendor_pan_num = p_vendor_pan_num and
section_type = p_section_type and
section_code = p_section_code and
fin_year = p_fin_year;
insert into jai_ap_tds_thhold_grps
(
threshold_grp_id ,
vendor_id ,
org_tan_num ,
vendor_pan_num ,
section_type ,
section_code ,
fin_year ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
(
jai_ap_tds_thhold_grps_s.nextval ,
p_vendor_id ,
p_org_tan_num ,
p_vendor_pan_num ,
p_section_type ,
p_section_code ,
p_fin_year ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
)
returning threshold_grp_id into ln_threshold_grp_id;
update jai_ap_tds_thhold_grps
set
total_invoice_amount = nvl(total_invoice_amount, 0) + nvl(p_trx_invoice_amount, 0),
total_invoice_cancel_amount = nvl(total_invoice_cancel_amount, 0) + nvl(p_trx_invoice_cancel_amount, 0),
total_invoice_apply_amount = nvl(total_invoice_apply_amount, 0) + nvl(p_trx_invoice_apply_amount, 0),
total_invoice_unapply_amount = nvl(total_invoice_unapply_amount, 0) + nvl(p_trx_invoice_unapply_amount, 0),
total_tax_paid = nvl(total_tax_paid, 0) + nvl(p_trx_tax_paid, 0),
total_thhold_change_tax_paid = nvl(total_thhold_change_tax_paid, 0) + nvl(p_trx_thhold_change_tax_paid, 0),
--current_threshold_slab_id = nvl( p_trx_threshold_slab_id, current_threshold_slab_id)
--commented the above and added the below by Ramananda for Bug#4562793
current_threshold_slab_id = nvl( p_trx_threshold_slab_id, 0),
/*Bug 5751783. Updated non-rounded value*/
total_calc_tax_paid = nvl(total_calc_tax_paid,0) + nvl(p_trx_tax_paid,0)
where threshold_grp_id = ln_threshold_grp_id;
/* Insert into the audite table */
p_codepath := jai_general_pkg.plot_codepath(9, p_codepath); /* 9 */
insert into jai_ap_tds_thgrp_audits
(
threshold_grp_audit_id ,
threshold_grp_id ,
old_invoice_amount ,
old_invoice_cancel_amount ,
old_invoice_apply_amount ,
old_invoice_unapply_amount ,
old_tax_paid ,
old_thhold_change_tax_paid ,
old_threshold_slab_id ,
trx_invoice_amount ,
trx_invoice_cancel_amount ,
trx_invoice_apply_amount ,
trx_invoice_unapply_amount ,
trx_tax_paid ,
trx_thhold_change_tax_paid ,
trx_threshold_slab_id ,
new_invoice_amount ,
new_invoice_cancel_amount ,
new_invoice_apply_amount ,
new_invoice_unapply_amount ,
new_tax_paid ,
new_thhold_change_tax_paid ,
new_threshold_slab_id ,
tds_event ,
invoice_id ,
invoice_line_number ,
invoice_distribution_id ,
remarks ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
/*Bug 5751783. Inserted non-rounded values also*/
calc_old_tax_paid ,
calc_trx_tax_paid ,
calc_new_tax_paid
)
values
(
jai_ap_tds_thgrp_audits_s.nextval ,
ln_threshold_grp_id ,
ln_old_invoice_amount ,
ln_old_invoice_cancel_amount ,
ln_old_invoice_apply_amount ,
ln_old_invoice_unapply_amount ,
ln_old_tax_paid ,
ln_old_thhold_change_tax_paid ,
ln_old_threshold_slab_id ,
p_trx_invoice_amount ,
p_trx_invoice_cancel_amount ,
p_trx_invoice_apply_amount ,
p_trx_invoice_unapply_amount ,
ln_tmp_tds_amt, --added for bug#5722028 csahoo
p_trx_thhold_change_tax_paid ,
p_trx_threshold_slab_id ,
ln_new_invoice_amount ,
ln_new_invoice_cancel_amount ,
ln_new_invoice_apply_amount ,
ln_new_invoice_unapply_amount ,
ln_new_tax_paid ,
ln_new_thhold_change_tax_paid ,
ln_new_threshold_slab_id ,
p_tds_event ,
p_invoice_id ,
p_invoice_line_number ,
p_invoice_distribution_id ,
p_remarks ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id ,
/*Bug 5751783*/
ln_calc_old_tax_paid ,
ln_calc_trx_tax_paid ,
ln_calc_new_tax_paid
)
returning threshold_grp_audit_id into p_threshold_grp_audit_id;
/* ******************************** insert_tds_thhold_trxs ******************************************* */
--for bug#4333449
procedure insert_tds_thhold_trxs
(
p_invoice_id in number,
p_tds_event in varchar2,
p_tax_id in number default null,
p_tax_rate in number default null,
p_taxable_amount in number default null,
p_tax_amount in number default null,
p_tds_authority_vendor_id in number default null,
p_tds_authority_vendor_site_id in number default null,
p_invoice_tds_authority_num in varchar2 default null,
p_invoice_tds_authority_type in varchar2 default null,
p_invoice_tds_authority_curr in varchar2 default null,
p_invoice_tds_authority_amt in number default null,
p_invoice_tds_authority_id in number default null,
p_vendor_id in number default null,
p_vendor_site_id in number default null,
p_invoice_vendor_num in varchar2 default null,
p_invoice_vendor_type in varchar2 default null,
p_invoice_vendor_curr in varchar2 default null,
p_invoice_vendor_amt in number default null,
p_invoice_vendor_id in number default null,
p_parent_inv_payment_priority in number default null,
p_parent_inv_exchange_rate in number default null
)
is
begin
fnd_file.put_line(FND_FILE.LOG, ' Insert -> insert_tds_thhold_trxs ');
insert into jai_ap_tds_thhold_trxs
(
threshold_trx_id ,
invoice_id ,
tds_event ,
tax_id ,
tax_rate ,
taxable_amount ,
tax_amount ,
tds_authority_vendor_id ,
tds_authority_vendor_site_id ,
invoice_to_tds_authority_num ,
invoice_to_tds_authority_type ,
invoice_to_tds_authority_curr ,
invoice_to_tds_authority_amt ,
invoice_to_tds_authority_id ,
vendor_id ,
vendor_site_id ,
invoice_to_vendor_num ,
invoice_to_vendor_type ,
invoice_to_vendor_curr ,
invoice_to_vendor_amt ,
invoice_to_vendor_id ,
parent_inv_payment_priority ,
parent_inv_exchange_rate ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
(
jai_ap_tds_thhold_trxs_s.nextval ,
p_invoice_id ,
p_tds_event ,
p_tax_id ,
p_tax_rate ,
p_taxable_amount ,
p_tax_amount ,
p_tds_authority_vendor_id ,
p_tds_authority_vendor_site_id ,
p_invoice_tds_authority_num ,
p_invoice_tds_authority_type ,
p_invoice_tds_authority_curr ,
p_invoice_tds_authority_amt ,
p_invoice_tds_authority_id ,
p_vendor_id ,
p_vendor_site_id ,
p_invoice_vendor_num ,
p_invoice_vendor_type ,
p_invoice_vendor_curr ,
p_invoice_vendor_amt ,
p_invoice_vendor_id ,
p_parent_inv_payment_priority ,
p_parent_inv_exchange_rate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
fnd_file.put_line(FND_FILE.LOG, ' Done Insert -> insert_tds_thhold_trxs ');
end insert_tds_thhold_trxs;
SELECT '1'
FROM jai_ap_tds_prepayments
WHERE invoice_distribution_id_prepay = p_invoice_distribution_id;
SELECT '1'
FROM jai_ap_tds_prepayments
WHERE invoice_distribution_id_prepay = p_invoice_distribution_id_pp
AND unapply_flag = 'Y';
SELECT nvl(tds_rounding_factor,0), tds_rounding_start_date
FROM jai_ap_tds_years
WHERE legal_entity_id = p_org_id
AND trunc (p_inv_date) between start_date and end_date ;
select creation_date
from ap_invoices_all
where invoice_id = p_invoice_id;
FOR i IN(SELECT a.invoice_id,
a.amount,
a.invoice_distribution_id,
a.parent_reversal_id,
a.prepay_distribution_id,
a.accounting_date,
a.org_id,
a.last_updated_by,
a.last_update_date,
a.created_by,
a.creation_date,
b.vendor_id,
b.vendor_site_id,
b.invoice_currency_code,
b.exchange_rate,
b.set_of_books_id
FROM ap_invoice_distributions_all a,
ap_invoices_all b
WHERE a.invoice_id = b.invoice_id
AND b.invoice_id = p_invoice_id
AND a.line_type_lookup_code = 'PREPAY'
AND b.source <> 'TDS'
AND b.cancelled_date is null
AND invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT'))
LOOP
lv_prepay_flag := NULL;
p_event => 'INSERT', --Added for Bug 8431516
p_invoice_id => i.invoice_id,
p_invoice_distribution_id => i.invoice_distribution_id,
p_prepay_distribution_id => i.prepay_distribution_id,
p_parent_reversal_id => i.parent_reversal_id,
p_prepay_amount => i.amount,
p_vendor_id => i.vendor_id,
p_vendor_site_id => i.vendor_site_id,
p_accounting_date => i.accounting_date,
p_invoice_currency_code => i.invoice_currency_code,
p_exchange_rate => i.exchange_rate,
p_set_of_books_id => i.set_of_books_id,
p_org_id => i.org_id,
p_creation_date => i.creation_date, -- Bug 5722028
p_process_flag => lv_process_flag,
p_process_message => lv_process_message,
p_codepath => lv_codepath
);
p_last_updated_by => i.last_updated_by,
p_last_update_date => i.last_update_date,
p_created_by => i.created_by,
p_creation_date => i.creation_date,
p_org_id => i.org_id,
p_process_flag => lv_process_flag,
p_process_message => lv_process_message
);
SELECT threshold_grp_id
FROM jai_ap_tds_inv_taxes
WHERE invoice_distribution_id = p_prepay_distribution_id
AND section_type = 'TDS_SECTION';
SELECT *
FROM jai_ap_tds_thhold_grps
WHERE threshold_grp_id = p_threshold_grp_id;
SELECT threshold_hdr_id
FROM jai_ap_tds_th_vsite_v
WHERE vendor_id = p_vendor_id
AND tan_no = p_org_tan_num
AND pan_no = p_pan_num
AND section_type = p_section_type
AND section_code = p_section_code;
SELECT threshold_slab_id, threshold_type_id, from_amount, to_amount, tax_rate
FROM jai_ap_tds_thhold_slabs
WHERE threshold_hdr_id = p_threshold_hdr_id
AND threshold_type_id in
( SELECT threshold_type_id
FROM jai_ap_tds_thhold_types
WHERE threshold_hdr_id = p_threshold_hdr_id
AND threshold_type = p_threshold_type
AND trunc(sysdate) between from_date and nvl(to_date, sysdate + 1)
)
AND from_amount <= p_amount
AND NVL(to_amount, p_amount) >= p_amount
ORDER BY from_amount asc;
SELECT b.tax_rate,
b.from_amount,
a.tax_id
FROM jai_ap_tds_thhold_taxes a,
jai_ap_tds_thhold_slabs b
WHERE a.threshold_slab_id = b.threshold_slab_id
AND a.operating_unit_id = p_org_id
AND b.threshold_slab_id = p_threshold_slab_id;
SELECT *
FROM jai_ap_tds_thhold_grps
WHERE threshold_grp_id = p_threshold_grp_id;
SELECT NVL(SUM(a.taxable_amount),0) taxable_amount
FROM jai_ap_tds_thhold_trxs a
WHERE a.threshold_grp_id = c_threshold_grp_id
AND a.tds_event = 'INVOICE VALIDATE'
AND a.taxable_amount >= c_single_threshold_amt
AND NOT EXISTS (SELECT '1'
FROM jai_ap_tds_inv_cancels b
WHERE a.invoice_id = b.invoice_id);
SELECT *
FROM jai_ap_tds_prepayments
WHERE tds_threshold_grp_id = c_threshold_grp_id
AND NVL(unapply_flag,'N') <> 'Y';
SELECT 'Y'
FROM jai_ap_tds_thhold_trxs a,
jai_ap_tds_inv_taxes b
WHERE a.invoice_id = b.invoice_id
AND b.invoice_distribution_id = p_invoice_distribution_id
AND a.tds_event = 'INVOICE VALIDATE'
AND a.taxable_amount >= p_single_threshold_amt;
SELECT SUM (NVL (jatit.amount , 0 ) )
FROM jai_ap_tds_inv_taxes jatit
WHERE jatit.threshold_grp_id = cp_threshold_grp_id
AND match_status_flag = 'A'
AND jatit.process_status = 'P'
AND jatit.tax_amount IS NOT NULL
AND jatit.threshold_trx_id IS NULL
AND jatit.threshold_slab_id = 0
AND ( jatit.actual_tax_id IS NOT NULL OR
( jatit.actual_taX_id IS NULL
AND jatit.default_tax_id IS NOT NULL
)
)
AND EXISTS /* check whether iinvoice is not cancelled*/
( SELECT invoice_id
FROM ap_invoices_all ai
WHERE ai.invoice_id = jatit.invoice_id
AND ai.cancelled_Date IS NULL
AND ai.cancelled_amount IS NULL
);
SELECT SUM (NVL (jatp.application_amount, 0 ))
FROM jai_ap_tds_prepayments jatp
WHERE jatp.tds_threshold_grp_id = cp_threshold_grp_id
AND jatp.tds_applicable_flag = 'Y'
AND jatp.tds_threshold_trx_id_apply IS NULL
AND jatp.unapply_flag IS NULL OR jatp.unapply_flag = 'N' ;
SELECT SUM(NVL(jattt.taxable_amount,0))
FROM jai_ap_tds_thhold_trxs jattt
WHERE jattt.threshold_grp_id = cp_threshold_grp_id
AND ( jattt.tds_event like 'THRESHOLD TRANSITION%' OR
-- Bug 5722028. Added by csahoo
-- added the following condition
jattt.tds_event like 'THRESHOLD ROLLBACK%'
);
SELECT 'Y'
FROM jai_ap_tds_thhold_trxs a
WHERE a.tds_event = 'INVOICE VALIDATE'
AND a.taxable_amount >= p_single_threshold_amt
AND a.invoice_id IN (SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = (SELECT prepay_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id = p_invoice_distribution_id));