[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_gen.plb Version 120.4
7. 21-Dec-2007 Sanjikum for Bug#6708042, Version 120.5
Obsoleted the changes done for verion 120.4 and rechecked in the version 120.3 as 120.5
8. 07/Jul/2009 Bgowrava for Bug 5911913 . File Version 120.3.12000000.9
Added two parameters
(1) p_old_input_dff_value_wct
(2) p_old_input_dff_value_essi
in procedure processs_invoice.
Added a check to set the value of lv_user_deleted_flag
for section_type in ('WCT_SECTION' and 'ESSI_SECTION')
9. 18-DEC-2009 Code modified by Eric Ma for PF bug#7340818
10. 16-Mar-2011 Bug 11830186 by amandali
Description:TDS INVOICE IS GENERATING WITHOUT CHECKING THRESHOLD LIMIT
Fix:When defaulted TDS tax id is populated after saving the distributions form, system is considering as it is a manually attached tds tax id.
So added if clause in process_input_dff_tds to check whether it is by default or manually attached one and proceed accordingly.
Also added a condition in populate_localization_inv_tax to check if it is a defaulted tax, and if yes,set user_deleted_tax_flag to Y and actual_tax_id to null.
11. 13-Jun-2011 Bug 12640899
Description: Incorrect TDS Invoice is generated if an Invoice(with multiple distributions) breaches SINGLE Threshold and
SINGLE and CUMULATIVE Slabs have different rates
Fix: Total Invoice amount needs to compared with SINGLE Threshold Slab From Amount
12. 23-Aug-2011 mmurtuza for bug12858951
Description: TDS CALCULATED FOR SERVICE TAX LINES WHEN THRESHOLD CROSSED
Fix: Modified the call and definition of procedure populate_localization_inv_tax by adding the parameter p_line_type_lookup_code
Declared two cursors c_upd_servtax_flag_n, c_upd_servtax_flag_y and two variables ln_tds_inv_tax_id_upd_n and ln_tds_inv_tax_id_upd_y
to update values of flag column consider_amt_for_tds in table jai_ap_tds_inv_taxes to 'N' or 'Y' based on section attached.
13. 13-Mar-2012 mmurtuza for bug12858951 Post review
Description: TDS CALCULATED FOR SERVICE TAX LINES WHEN THRESHOLD CROSSED
Fix; Modified the call and definition of procedure populate_localization_inv_tax by adding tow more parameters p_po_distribution_id and p_rcv_transaction_id
to update values of flag column consider_amt_for_tds in table jai_ap_tds_inv_taxes to 'N' or 'Y' based on section and tax type attached.
14. 20-Apr-2012 mmurtuza for bug 13983975
Description: SEVERE PERFORMANCE ISSUES WITH SERVICE TAX ATTACHMENT AND VALIDATION OF INVOICE
Fix: Removed nvl cluase from cursors c_no_tds_service_excise_st, c_no_tds_service_excise_po and c_no_tds_service_excise_rec
15. 04-May-2012 amandali for bug 14019234
Description:TDS not deducted on Excise tax lines
Fix:Commented the union all in cursors c_no_tds_service_excise_rec, c_no_tds_service_excise_po, c_no_tds_service_excise_st
where we have a check for excise and customs tax lines.
16. 05-Jun-2012 amandali for bug 14052883
Description:TDS not deducted on tax lines other than Service tax
Fix:Added AND clause to have a join for jai_cmn_taxes_all(jcta2) in cursors c_no_tds_service_excise_po and c_no_tds_service_excise_rec
---------------------------------------------------------------------------- */
procedure process_invoice
(
p_invoice_id in number,
p_invoice_line_number in number default null,
p_invoice_distribution_id in number default null,
p_line_type_lookup_code in varchar2,
p_distribution_line_number in number,
p_parent_reversal_id in number,
p_reversal_flag in varchar2,
p_amount in number,
p_invoice_currency_code in varchar2,
p_exchange_rate in number,
p_set_of_books_id in number,
p_po_distribution_id in number default null,
p_rcv_transaction_id in number default null,
p_vendor_id in number,
p_vendor_site_id in number,
p_input_dff_value_tds in varchar2,
p_input_dff_value_wct in varchar2,
p_old_input_dff_value_wct in varchar2, --Added by Bgowrava for Bug#5911913
p_input_dff_value_essi in varchar2,
p_old_input_dff_value_essi in varchar2, --Added by Bgowrava for Bug#5911913
p_org_id in number,
p_accounting_date in date,
p_call_from in varchar2,
p_final_tds_tax_id out nocopy number,
p_process_flag out nocopy varchar2,
p_process_message out nocopy varchar2,
p_codepath in out nocopy varchar2
)
is
cursor c_gl_sets_of_books(cp_set_of_books_id number) is
select currency_code
from gl_sets_of_books
where set_of_books_id = cp_set_of_books_id;
select 'P'
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and process_status = p_process_status;
select 'Y'
from jai_ap_tds_invoices
where invoice_id = p_invoice_id;
select shipment_header_id,
shipment_line_id
from rcv_transactions
where transaction_id = p_rcv_transaction_id;
select jtc.section_code section_code,
jrtl.tax_id tax_id
from jai_rcv_line_taxes jrtl,
jai_cmn_taxes_all jtc
where jtc.tax_id = jrtl.tax_id
and jrtl.tax_type = jai_constants.tax_type_tds
and jtc.section_type = p_section_type--rchandan for bug#4428980
and jrtl.shipment_header_id = p_shipment_header_id
and jrtl.shipment_line_id = p_shipment_line_id
order by jrtl.tax_line_no asc;
select po_header_id,
po_line_id,
line_location_id
from po_distributions_all
where po_distribution_id = p_po_distribution_id;
select jtc.section_code section_code,
jpllt.tax_id tax_id
from jai_po_taxes jpllt,
jai_cmn_taxes_all jtc
where jpllt.tax_id = jtc.tax_id
and jpllt.po_header_id = p_po_header_id
and jpllt.po_line_id = p_po_line_id
and jpllt.line_location_id = p_line_location_id
and jtc.tax_type = jai_constants.tax_type_tds
and jtc.section_type = p_section_type--rchandan for bug#4428980
order by jpllt.tax_line_no asc;
select section_code,
tax_id
from JAI_AP_TDS_VENDOR_HDRS
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id;
select nvl(confirm_pan_flag, 'N') confirm_pan_flag
from JAI_AP_TDS_VENDOR_HDRS
where vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id;
select 'Y'
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 tds_inv_tax_id, actual_tax_id --Added by Bgowrava for Bug#5911913
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 tds_inv_tax_id from
jai_ap_tds_inv_taxes jadit, jai_cmn_document_taxes jcdt, jai_cmn_taxes_all jcta,
jai_rgm_registrations jrr, jai_rgm_definitions jrd
where jadit.invoice_id=jcdt.source_doc_id
and jadit.actual_tax_id=jcta.tax_id
and (NVL(upper(nvl(actual_section_code, default_section_code)), '-XX') NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
AND upper(jcta.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE'))
AND jcdt.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime
and nvl(jadit.invoice_distribution_id, -9999) = nvl(cp_invoice_distribution_id, -9999)
and nvl(jadit.invoice_id, -9999) = nvl(cp_invoice_id, -9999);
select tds_inv_tax_id from
jai_ap_tds_inv_taxes jadit, jai_cmn_document_taxes jcdt, jai_cmn_taxes_all jcta,
jai_rgm_registrations jrr, jai_rgm_definitions jrd
where jadit.invoice_id=jcdt.source_doc_id
and jadit.actual_tax_id=jcta.tax_id
and NOT (NVL(upper(nvl(actual_section_code, default_section_code)), '-XX') NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
AND upper(jcta.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE'))
AND jcdt.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime
and nvl(jadit.invoice_distribution_id, -9999) = nvl(cp_invoice_distribution_id, -9999)
and nvl(jadit.invoice_id, -9999) = nvl(cp_invoice_id, -9999);
select jadit.tds_inv_tax_id from
jai_ap_tds_inv_taxes jadit, jai_cmn_document_taxes jcdt, jai_cmn_taxes_all jcta,
jai_rgm_registrations jrr, jai_rgm_definitions jrd
where jadit.invoice_id=jcdt.source_doc_id
and nvl(jadit.actual_tax_id, jadit.default_tax_id)=jcta.tax_id
AND upper(jcta.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
AND jcdt.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime
/*and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999)*/ /*Commented nvl clause and added below by mmurtuza for bug 13983975*/
and jadit.invoice_distribution_id = p_invoice_distribution_id
and jadit.invoice_id = p_invoice_id
/* Commented below union all for bug 14019234 */
/* union all
SELECT tds_inv_tax_id
FROM jai_ap_tds_inv_taxes jadit,
jai_cmn_document_taxes jcdt,
jai_cmn_taxes_all jcta
WHERE jcdt.tax_id = jcta.tax_id
--AND (UPPER(jcta.tax_type) LIKE '%EXCISE%' OR UPPER(jcta.tax_type) LIKE '%CUSTOMS%')
AND jcta.tax_type in (JAI_CONSTANTS.TAX_TYPE_EXCISE, JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL, JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS)
AND jadit.invoice_id=jcdt.source_doc_id
--AND nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
--and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999); --Commented nvl clause and added below by mmurtuza for bug 13983975
select jadit.tds_inv_tax_id from
jai_ap_tds_inv_taxes jadit, JAI_AP_MATCH_INV_TAXES jamit , jai_cmn_taxes_all jcta1, jai_cmn_taxes_all jcta2,
jai_rgm_registrations jrr, jai_rgm_definitions jrd
where jadit.invoice_id = jamit.invoice_id
and nvl(jadit.actual_tax_id, default_tax_id)=jcta1.tax_id
and nvl(jamit.po_distribution_id, -9999) = p_po_distribution_id
AND upper(jcta1.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
and jamit.tax_id=jcta2.tax_id /* Added AND condition for bug 14052883 */
and jadit.invoice_line_number=jamit.invoice_line_number /* Added AND condition for bug 14052883 */
AND jcta2.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime
/*and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999)*/ /*Commented nvl clause and added below by mmurtuza for bug 13983975*/
and jadit.invoice_distribution_id = p_invoice_distribution_id
and jadit.invoice_id = p_invoice_id
/* Commented below union all for bug 14019234 */
/* union all
select tds_inv_tax_id from
jai_ap_tds_inv_taxes jadit, JAI_AP_MATCH_INV_TAXES jamit, jai_cmn_taxes_all jcta
where --(upper(jcta.tax_type) like '%EXCISE%' OR UPPER(jcta.tax_type) LIKE '%CUSTOMS%')
jcta.tax_type in (JAI_CONSTANTS.TAX_TYPE_EXCISE, JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL, JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS)
and jamit.tax_id = jcta.tax_id
and nvl(jamit.po_distribution_id, -9999) = p_po_distribution_id
and jadit.invoice_distribution_id = jamit.invoice_distribution_id
--and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
--and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999);--Commented nvl clause and added below by mmurtuza for bug 13983975
select jadit.tds_inv_tax_id from
jai_ap_tds_inv_taxes jadit, JAI_AP_MATCH_INV_TAXES jamit , jai_cmn_taxes_all jcta1, jai_cmn_taxes_all jcta2,
jai_rgm_registrations jrr, jai_rgm_definitions jrd
where jadit.invoice_id = jamit.invoice_id
and nvl(jadit.actual_tax_id, default_tax_id)=jcta1.tax_id
and nvl(jamit.rcv_transaction_id, -9999) = p_rcv_transaction_id
AND upper(jcta1.section_code) NOT IN (select upper(lookup_code) from ja_lookups where lookup_type='JAI_TDS_SECTION_SERVICE')
and jamit.tax_id=jcta2.tax_id /* Added AND condition for bug 14052883 */
and jadit.invoice_line_number=jamit.invoice_line_number /* Added AND condition for bug 14052883 */
AND jcta2.tax_type = jrr.attribute_code
AND jrr.regime_id = jrd.regime_id
AND jrr.registration_type = jai_constants.regn_type_tax_types
AND jrd.regime_code = jai_constants.service_regime
/*and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999)*/ /*Commented nvl clause and added below by mmurtuza for bug 13983975*/
and jadit.invoice_distribution_id = p_invoice_distribution_id
and jadit.invoice_id = p_invoice_id
/* Commented below union all for bug 14019234 */
/* union all
select tds_inv_tax_id from
jai_ap_tds_inv_taxes jadit, JAI_AP_MATCH_INV_TAXES jamit, jai_cmn_taxes_all jcta
where --(upper(jcta.tax_type) like '%EXCISE%' OR UPPER(jcta.tax_type) LIKE '%CUSTOMS%')
jcta.tax_type in (JAI_CONSTANTS.TAX_TYPE_EXCISE, JAI_CONSTANTS.TAX_TYPE_EXC_ADDITIONAL, JAI_CONSTANTS.TAX_TYPE_EXC_OTHER,
JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_CUSTOMS,
JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS)
and jamit.tax_id = jcta.tax_id
and nvl(jamit.rcv_transaction_id, -9999) = p_rcv_transaction_id
and jadit.invoice_distribution_id = jamit.invoice_distribution_id
--and nvl(jadit.invoice_distribution_id, -9999) = nvl(p_invoice_distribution_id, -9999)
--and nvl(jadit.invoice_id, -9999) = nvl(p_invoice_id, -9999);--Commented nvl clause and added below by mmurtuza for bug 13983975
lv_user_deleted_tax_flag jai_ap_tds_inv_taxes.user_deleted_tax_flag%type; --File.Sql.35 Cbabu := 'N';
lv_user_deleted_tax_flag := 'N';
lv_user_deleted_tax_flag := 'Y';
lv_user_deleted_tax_flag := 'Y';
if ln_actual_tax_id is null and lv_user_deleted_tax_flag <> 'Y' and p_default_from not in ('PO', 'Receipt') then
/* User has not given any input, or also has not specifically deleted the defaulted value or
default is not because of PO or Receipt */
p_codepath := jai_general_pkg.plot_codepath(14, p_codepath); /* 14 */
lv_user_deleted_tax_flag := 'Y';
insert into jai_ap_tds_inv_taxes
(
tds_inv_tax_id ,
invoice_id ,
invoice_line_number ,
invoice_distribution_id ,
distribution_line_number ,
amount ,
section_type ,
default_type ,
default_section_code ,
default_tax_id ,
actual_section_code ,
actual_tax_id ,
user_deleted_tax_flag ,
default_threshold_grp_id ,
default_cum_threshold_slab_id ,
default_cum_threshold_stage ,
default_sin_threshold_slab_id ,
default_from ,
consider_for_redefault ,
process_status ,
codepath ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
(
jai_ap_tds_inv_taxes_s.nextval ,
p_invoice_id ,
p_invoice_line_number ,
p_invoice_distribution_id ,
P_distribution_line_number ,
p_amount ,
p_section_type ,
p_default_type ,
p_default_section_code ,
ln_default_tax_id ,
lv_actual_section_code ,
ln_actual_tax_id ,
lv_user_deleted_tax_flag ,
ln_default_threshold_grp_id ,
ln_default_cum_threshold_slab ,
lv_default_cum_threshold_stage ,
ln_default_sin_threshold_slab ,
p_default_from ,
lv_consider_for_redefault ,
lv_process_status ,
p_codepath ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.login_id
);
update jai_ap_tds_inv_taxes
set amount = p_amount ,
section_type = p_section_type ,
default_type = p_default_type ,
default_section_code = p_default_section_code ,
default_tax_id = ln_default_tax_id ,
actual_section_code = lv_actual_section_code ,
actual_tax_id = ln_actual_tax_id ,
user_deleted_tax_flag = lv_user_deleted_tax_flag ,
default_threshold_grp_id = ln_default_threshold_grp_id ,
default_cum_threshold_slab_id = ln_default_cum_threshold_slab ,
default_cum_threshold_stage = lv_default_cum_threshold_stage ,
default_sin_threshold_slab_id = ln_default_sin_threshold_slab ,
default_from = p_default_from ,
consider_for_redefault = lv_consider_for_redefault ,
process_status = lv_process_status ,
codepath = p_codepath ,
last_updated_by = fnd_global.user_id ,
last_update_date = sysdate
where tds_inv_tax_id = ln_tds_inv_tax_id;
update jai_ap_tds_inv_taxes
set consider_amt_for_tds = 'N'
where tds_inv_tax_id = ln_tds_inv_tax_id_upd_n;
update jai_ap_tds_inv_taxes
set consider_amt_for_tds = 'Y'
where tds_inv_tax_id = ln_tds_inv_tax_id_upd_y;
update jai_ap_tds_inv_taxes
set consider_amt_for_tds = 'N'
where tds_inv_tax_id = ln_tds_inv_tax_id_st;
update jai_ap_tds_inv_taxes
set consider_amt_for_tds = 'Y'
where invoice_id=p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id;
update jai_ap_tds_inv_taxes
set consider_amt_for_tds = 'N'
where tds_inv_tax_id = ln_tds_inv_tax_id_st;
update jai_ap_tds_inv_taxes
set consider_amt_for_tds = 'Y'
where invoice_id=p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id;
update jai_ap_tds_inv_taxes
set consider_amt_for_tds = 'N'
where tds_inv_tax_id = ln_tds_inv_tax_id_st;
update jai_ap_tds_inv_taxes
set consider_amt_for_tds = 'Y'
where invoice_id=p_invoice_id
and invoice_distribution_id = p_invoice_distribution_id;
update jai_ap_tds_inv_taxes
set default_tax_id = ln_default_tax_id
where tds_inv_tax_id <> ln_tds_inv_tax_id
and invoice_id = p_invoice_id
and nvl(invoice_line_number, -9999) = nvl(p_invoice_line_number, -9999)
and consider_for_redefault = lv_consider_for_redefault
and section_type = p_section_type;
select sum(amount)
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
--Commented out by Eric Ma for PF bug#7340818 and consider_for_redefault = p_consider_for_redefault--rchandan for bug#4428980
--Commented out by Eric Ma for PF bug#7340818 and user_deleted_tax_flag <> 'Y'
and ( (p_invoice_distribution_id is null ) or (p_invoice_distribution_id is not null and invoice_distribution_id <> p_invoice_distribution_id ) )
/*and ( (p_invoice_line_number is null ) or ( p_invoice_line_number is not null and invoice_line_number <> p_invoice_line_number) )
This is not required as we need to consider all distributions for redefaulting*/
and invoice_distribution_id < p_invoice_distribution_id; --Added by Eric Ma for PF bug#7340818
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 (
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
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 date condition */
and trunc(p_accounting_Date) between from_date
and nvl(to_date, p_accounting_date + 1)
)
and nvl(to_amount, p_amount) >= p_amount
order by from_amount asc;
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 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_gl_date between start_date and end_date;
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 --- JAI_AP_TDS_ORG_TANS is changed to view jai_ap_tds_org_tan_v 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 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 invoice_amount
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
select tds_inv_tax_id, default_tax_id, actual_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;--rchandan for bug#4428980
/* user has not provided any input or has deleted the defaulted or earlier given value */
p_codepath := jai_general_pkg.plot_codepath(3, p_codepath); /* 3 */
/* User has deleted the earlier given or defaulted value no TDS should be deducted. */
p_output_tds_dff_value := 'NO TDS';
/* ********************************************* process_delete ********************************************* */
procedure process_delete
(
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_process_flag out nocopy varchar2,
P_process_message out nocopy varchar2
)
is
/* Change History
-------------------------------------------------------------------------------
S.No Date Author and Details
-------------------------------------------------------------------------------
1. 16/05/2008 JMEENA for bug#6995295.
Added NVL for process_status
*/
begin
/* AP lines uptake - introduced line */
delete jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id
and (
(p_invoice_line_number is null ) or
(p_invoice_line_number is not null and invoice_line_number = p_invoice_line_number)
)
and (
(p_invoice_distribution_id is null ) or
(p_invoice_distribution_id is not null and invoice_distribution_id = p_invoice_distribution_id)
)
and NVL(process_status,'D') <> 'P'; -- Added NVL by JMEENA for bug#6995295
P_process_message := 'Error from jai_ap_tds_tax_defaultation.process_delete :' || sqlerrm;
end process_delete;
/* ********************************************* process_delete ********************************************* */
/* ********************************************* check_old_transaction ********************************************* */
procedure check_old_transaction
(
p_invoice_id in number default null,
p_invoice_distribution_id in number default null,
p_new_transaction out nocopy varchar2
)
is
cursor c_jai_ap_tds_inv_taxes_inv(p_invoice_id number) is
select 'Y'
from jai_ap_tds_inv_taxes
where invoice_id = p_invoice_id;
select 'Y'
from jai_ap_tds_inv_taxes
where invoice_distribution_id = p_invoice_distribution_id;