[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. 24-Aug-2008 JMEENA for bug#7309921
Modified procedure populate_localization_inv_tax and added cursor c_check_user_deleted_tax_flag to check the
user_deleted_tax_flag.
Added the condition if user_deleted_tax_flag is Y then do not default the TDS Tax.
Called the procedure get_default_tax_from_section after process_input_dff_tds, earlier it was being called before process_input_dff_tds.
---------------------------------------------------------------------------- */
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_input_dff_value_essi in varchar2,
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
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;
cursor c_check_user_deleted_tax_flag(p_tds_inv_tax_id NUMBER) IS
select user_deleted_tax_flag
from jai_ap_tds_inv_taxes
where tds_inv_tax_id = p_tds_inv_tax_id;
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';
open c_check_user_deleted_tax_flag(ln_tds_inv_tax_id);
fetch c_check_user_deleted_tax_flag into lv_user_deleted_tax_flag;
close c_check_user_deleted_tax_flag;
if p_default_type = 'SECTION' and p_default_section_code is not null and lv_user_deleted_tax_flag <>'Y' then
p_codepath := jai_general_pkg.plot_codepath(4, p_codepath); /* 4 */
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 */
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 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
and consider_for_redefault = p_consider_for_redefault--rchandan for bug#4428980
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*/
;
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 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;