[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
---------------------------------------------------------------------------- */
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_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 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 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 invoice_id = p_invoice_id
and process_status = p_process_status;
select tds_inv_tax_id, actual_tax_id, amount
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and actual_tax_id is not null;
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;
select actual_section_code, sum(amount*p_exchange_rate) invoice_amount, sum(tax_amount) section_amount
from jai_ap_tds_inv_taxes
where 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
having sum(tax_amount) <> 0;
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
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);
select actual_tax_id,
sum(amount*p_exchange_rate) taxable_amount,
sum(tax_amount) tax_amount
from jai_ap_tds_inv_taxes
where 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 )
)
group by actual_tax_id;
select c.pan_no pan_no,
d.org_tan_num tan_no
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 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;
select section_type,
actual_tax_id,
sum(amount*p_exchange_rate) taxable_amount,
sum(tax_amount) tax_amount
from jai_ap_tds_inv_taxes
where 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;
/* 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 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 invoice_id = p_invoice_id
and section_type = lv_tds_section_type --rchandan for bug#4428980
and nvl(user_deleted_tax_flag, 'N') = 'Y';
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
where 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 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 actual_tax_id = cur_rec.actual_tax_id
and (
(lv_generate_all_invoices = 'Y' )
or
(ln_threshold_slab_id_single > 0)
);
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 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 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
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,
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');
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 => r_ap_invoices_all.invoice_date,
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
);
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
);
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 ,
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 ,
pn_taxable_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 ,
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 (
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
from jai_ap_tds_thhold_grps
where threshold_grp_id = cp_threshold_grp_id;
select tax_rate
from JAI_CMN_TAXES_ALL
where tax_id = cp_tax_id;
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%' OR
-- Bug 5722028. Added by csahoo
-- added the following condition
jattt.tds_event like 'THRESHOLD TRANSITION%' );
/* Update the total tax amount for which invoice was raised */
ln_threshold_grp_id:= p_threshold_grp_id;
select vendor_id,
vendor_site_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
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)
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
)
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
)
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 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_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(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));