[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
In case of an insert into jai_ap_tds_payments for invoices created
prior to TDS clean up, vendor_id and vendor_site_id are passed as null.
Fix :
Called cursor c_ap_invoices_all to generate the vendor_id and vendor_site_id.
Inserted these values into the jai_ap_tds_payments table.
Dependency due to this Bug :
Yes.
3. 26/10/2005 Harshita for Bug 4692310/4640996, File Version 115.4
Issue :
In the cursors c_process_old_tds_payments, c_process_tds_payments, c_tds_invoice_paid_by_prepay,
and during deletion from jai_ap_tds_payments during regeneration,
The join < jiaot.organization_id = hou.legal_entity_id > is failing and the
Fix :
Suggested code change is as follows ..
to_char(jiaot.organization_id) = hou.DEFAULT_LEGAL_CONTEXT_ID
Dependency due to this Bug :
Yes.
4. 26/06/2006 Sanjikum for Bug#5219225, File version 115.5
1) Changes are done in procedure - process_tds_payments. Here changed the fnd log text at one place
5. 26/09/2006 rchandan for bug#4742259, File Version 115.7
Purpose: Impact due to TCS solution.
Fix : A new column by name regime_code is added in jai_ap_tds_certificate_nums
so that the same table can be used for TCS. Changes are made in this
package accordingly
6. 25/1/2007 CSahoo for BUG#5631784, File Version 120.1
Forward Porting of BUG#4742259
A new column by name regime_code is added in jai_ap_tds_certificate_nums
so that the same table can be used for TCS. Changes are made in this
package accordingly
7. 29/03/2007 bduvarag for bug#5647725,File version 120.2
Forward porting the changes done in 11i bug#5647215
8.14-may-07 kunkumar made changes for Budget and ST by IO and Build issues resolved8.14-may-07 kunkumar made changes for Budget and ST by IO and Build issues resolved8.14-may-07
9. 12-06-2007 sacsethi for bug 6119195 file version 120.6
R12RUP03-ST1: INDIA - PROCESS TDS PAYMENTS GIVES ERROR MESSAGE WHILE SUBMITTING
Probelem - After execution of Concurrent India TDS Payments , some concurrent execution
error was coming - FDPSTP failed due to ORA-01861: literal does not match format string
Solution - This problem was due to procedure process_tds_payments , Argument pd_tds_payment_from_date ,
pd_tds_payment_to_date parameter was of date type , whcih we made it as varchar2 and
create two variable with name ld_tds_payment_to_date ,ld_tds_payment_from_date
replae all pd_tds_payment_from_date , pd_tds_payment_to_date with
ld_tds_payment_from_date , ld_tds_payment_to_date with
10. 14-JUN-2007 Bgowrava for Bug#6129650, File Version 120.7
Removed the cursor c_hr_operating_units. changed the parameter of the cursor c_ja_in_tds_year_info
from r_hr_operating_units.default_legal_context_id to cur_ou.operating_unit_id.
Also removed the union codes in the cursors c_process_old_tds_payments, c_tds_invoice_paid_by_prepay,
c_process_tds_payments
11. 18-jan-2008 ssumaith - bug#6761239
prepayment applied to tds invoices was snot showing in the TDS
certificates report.
12. 21-FEB-2008 Changes done by nprashar for Bug # 6774129. Added a condition in cursor c_tds_invoice_paid_by_prepay,in order to avoid the problem of
TDS CERTIFICATE NOT GETTING GENERATED FOR PARTIAL PREPAYMENTS.
13. 7-March-2008. Changes by nprashar for Bug # 6774129. Change in cursor c_group_for_no_certificate, along with cursor
c_group_for_certificate.
14. 6-june-2008 Changes by nprashar for bug # 6195566. Forward port 11i bug # 6124751.
15. 20-Oct-2008 Bgowrava for Bug 6069891. File Version 120.7.12000000.8, 120.11.12010000.2
Created cursor c_tds_multiple_payments and
its related variables. Implemented logic for multiple
payments for single TDS invoice in procedure
process_tds_payments.
16. 05-Jan-2011 Bug 10621438
Description: TDS Certificates are generated based on TDS Invoice Date Range
Fix: TDS Certificates are generated on Quarterly basis. Start and End Date are replaced by Financial Year and Quarter parameter
17. 12-Jan-2011 Bug 11067621
Description: Derive Quarter Start and End dates based on
start date of TDS Year Info Setup and not the financial year
18. 20-Dec-2012 mmurtuza for bug 16020276
Description: WCT CERTIFICATE IS NOT GETTING GENERATING
Fix: Commented extra condition nvl(jitc.section_code,'XYZ') = nvl(pv_tds_section, section_code) in cursor c_process_tds_payments
in procedure process_tds_payments
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
----------------------------------------------------------------------------------------------------------------------------------------------------
115.2,115.4 4640996 4601658 Pls refer BCT for the list
of dependent files.
---------------------------------------------------------------------------- */
---------------------------------------------------------------------------- */
/* ******************************** process_tds_payments ******************************************* */
procedure process_tds_payments
(
errbuf out nocopy varchar2 ,
retcode out nocopy varchar2 ,
pd_tds_payment_from_date in varchar2 ,
pd_tds_payment_to_date in varchar2 ,
pv_org_tan_num in varchar2 ,
p_section_type in varchar2,/*bduvarag for Bug#5647725*/
pv_tds_section in varchar2 default null ,
pn_tds_authority_id in number default null ,
pn_tds_authority_site_id in number default null ,
pn_vendor_id in number default null ,
pn_vendor_site_id in number default null ,
pv_regenerate_flag in varchar2 default 'N'
)
is
ld_tds_payment_from_date date ; --Date 12-jun-2007 sacsethi for bug 6119195
select
aca.org_id org_id ,
aca.check_id check_id ,
aca.check_number check_number ,
aca.check_date check_date ,
aca.amount check_amount ,
aipa.invoice_payment_id invoice_payment_id ,
aipa.invoice_id invoice_id ,
aia.invoice_num invoice_num ,
aia.invoice_date invoice_date ,
aipa.amount payment_amount ,
jitc.section_code section_code ,
jattt.tax_id tax_id ,
jattt.tax_rate tax_rate ,
jattt.threshold_trx_id threshold_trx_id ,
jattt.invoice_id parent_invoice_id ,
jattt.tds_event tds_event ,
jattt.taxable_amount taxable_basis ,
jattt.invoice_to_tds_authority_amt tax_amount ,
jattt.tds_authority_vendor_id tax_authority_id ,
jattt.tds_authority_vendor_site_id tax_authority_site_id ,
jattt.vendor_id vendor_id ,
jattt.vendor_site_id vendor_site_id
from
ap_checks_all aca ,
ap_invoice_payments_all aipa ,
ap_invoices_all aia ,
jai_ap_tds_thhold_trxs jattt,
JAI_CMN_TAXES_ALL jitc
where
aca.check_id = aipa.check_id
and aipa.invoice_id = jattt.invoice_to_tds_authority_id
and aipa.invoice_id = aia.invoice_id
and jattt.tax_id = jitc.tax_id
and aia.invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
and aca.status_lookup_code NOT IN (lv_sts_lookup_code_argument1, lv_sts_lookup_code_argument2)
and ( (aia.payment_status_flag = lv_payment_status_flag)
or
( nvl( aia.invoice_amount, 0 ) = nvl(aia.amount_paid, 0 ) )
)
and aca.org_id in
(
select organization_id org_id
from JAI_AP_TDS_ORG_TAN_V
where org_tan_num = pv_org_tan_num
--Removed the union code by Bgowrava for Bug#6129650
)
and jattt.tds_authority_vendor_id = nvl(pn_tds_authority_id, jattt.tds_authority_vendor_id)
and jattt.tds_authority_vendor_site_id = nvl(pn_tds_authority_site_id, jattt.tds_authority_vendor_site_id)
and jattt.vendor_id = nvl(pn_vendor_id, jattt.vendor_id)
and jattt.vendor_site_id = nvl(pn_vendor_site_id, jattt.vendor_site_id)
and jitc.section_type = p_section_type -- 5647725, 6109941 brathod
and nvl(jitc.section_code,'XYZ') = nvl(pv_tds_section,nvl(jitc.section_code,'XYZ')) /*bduvarag for Bug#5647725*/
--and nvl(jitc.section_code,'XYZ') = nvl(pv_tds_section, section_code) /*Commented by mmurtuza for bug 16020276*/
/*bduvarag for Bug#5647725*/
and not exists (
select '1'
from JAI_AP_TDS_INV_PAYMENTS
where check_id = aca.check_id
and vendor_id = jattt.vendor_id /*Added by nprashar for bug # 6195566*/
and invoice_id = aipa.invoice_id /*Added by nprashar for bug # 6195566*/
and tds_tax_id in /*bduvarag for Bug#5647725*/
(
select tax_id from JAI_CMN_TAXES_ALL where tax_type = 'TDS'
and section_type = p_section_type)
)
;
select
aca.org_id org_id ,
aca.check_id check_id ,
aca.check_number check_number ,
aca.amount check_amount ,
aca.check_date check_date ,
aipa.invoice_payment_id invoice_payment_id ,
aipa.amount payment_amount ,
aia.invoice_id invoice_id ,
aia.invoice_num invoice_num ,
aia.invoice_date invoice_date ,
aia.invoice_amount tax_amount ,
aia.vendor_id tax_authority_id ,
aia.vendor_site_id tax_authority_site_id ,
nvl(aia.attribute_category,
lv_attribute_category) context ,
aia.attribute1 parent_invoice_id
from
ap_checks_all aca,
ap_invoice_payments_all aipa,
ap_invoices_all aia
where aca.check_id = aipa.check_id
and aipa.invoice_id = aia.invoice_id
and aia.source = lv_source
and aia.invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
and aca.status_lookup_code NOT IN (lv_sts_lookup_code_argument1, lv_sts_lookup_code_argument2)
and ( (aia.payment_status_flag = lv_payment_status_flag)
or
( nvl( aia.invoice_amount, 0 ) = nvl(aia.amount_paid, 0 ) )
)
and aia.vendor_id = nvl(pn_tds_authority_id, aia.vendor_id)
and aia.vendor_site_id = nvl(pn_tds_authority_site_id, aia.vendor_site_id)
/*Added by nprashar for bug # 6195566*/
and EXISTS ( SELECT 'Y'
FROM po_vendors pv
WHERE pv.vendor_id = aia.vendor_id
AND pv.vendor_type_lookup_code = 'INDIA TDS AUTHORITY'
)
and aca.org_id in
(
select organization_id org_id
from JAI_AP_TDS_ORG_TAN_V
where org_tan_num = pv_org_tan_num
--Removed the union code by Bgowrava for Bug#6129650
)
and not exists (
select '1'
from JAI_AP_TDS_INV_PAYMENTS
where invoice_id = aia.invoice_id
)
and not exists (
SELECT 1
FROM jai_ap_tds_thhold_trxs
WHERE invoice_to_tds_authority_id = aia.invoice_id
)/*bduvarag for Bug#5647725*/
;
select
vendor_id,
vendor_site_id,
cancelled_date
from
ap_invoices_all
where invoice_id = pn_invoice_id;
select
invoice_id parent_invoice_id ,
invoice_amount taxable_basis ,
tds_tax_id tds_tax_id ,
tds_section tds_section ,
tds_tax_rate tds_tax_rate ,
tds_amount tax_amount
from
JAI_AP_TDS_INVOICES
where invoice_id = nvl(pn_parent_invoice_id, invoice_id)
and tds_invoice_num = pv_tds_invoice_num
and source_attribute = lv_source_attribute;
select
invoice_id parent_invoice_id ,
invoice_amount taxable_basis ,
tds_tax_id tds_tax_id ,
tds_section tds_section ,
tds_tax_rate tds_tax_rate ,
tds_amount tax_amount
from
JAI_AP_TDS_INVOICES
where tds_invoice_num = pv_tds_invoice_num
and source_attribute = lv_source_attribute;
select jiati_1.tds_section
from JAI_AP_TDS_INVOICES jiati_1
where jiati_1.invoice_id = pn_invoice_id
and source_attribute = lv_source_attribute
and not exists
(
select '1'
from JAI_AP_TDS_INVOICES jiati_2
where jiati_1.rowid <> jiati_2.rowid
and source_attribute = lv_source_attribute
and jiati_1.invoice_id = jiati_2.invoice_id
and jiati_1.tds_section <> jiati_2.tds_section
);
select
jiati_1.tds_tax_id ,
jiati_1.tds_tax_rate
from JAI_AP_TDS_INVOICES jiati_1
where jiati_1.invoice_id = pn_invoice_id
and source_attribute = lv_source_attribute
and not exists
(
select '1'
from JAI_AP_TDS_INVOICES jiati_2
where jiati_1.rowid <> jiati_2.rowid
and source_attribute = lv_source_attribute
and jiati_1.invoice_id = jiati_2.invoice_id
and jiati_1.tds_tax_id <> jiati_2.tds_tax_id
);
select
aia.org_id org_id ,
aia.invoice_id invoice_id ,
aia.invoice_num invoice_num ,
aia.invoice_date invoice_date ,
aia.invoice_amount tax_amount ,
aia.vendor_id tax_authority_id ,
aia.vendor_site_id tax_authority_site_id ,
nvl(aia.attribute_category,
lv_attribute_category) context ,
aia.attribute1 parent_invoice_id ,
aida_prepayment.invoice_id prepay_invoice_id ,
-1 * sum(aida.amount) prepaid_amount
from
ap_invoices_all aia,
ap_invoice_distributions_all aida,
ap_invoice_distributions_all aida_prepayment
where aia.invoice_id = aida.invoice_id
and aida.prepay_distribution_id = aida_prepayment.invoice_distribution_id
and aida.line_type_lookup_code = lv_line_type_lookup_code
and aia.source = lv_source
and aia.invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
and ( (aia.payment_status_flag = lv_payment_status_flag)
or
( nvl( aia.invoice_amount, 0 ) = nvl(aia.amount_paid, 0 ) )
)
and aia.vendor_id = nvl(pn_tds_authority_id, aia.vendor_id)
and aia.vendor_site_id = nvl(pn_tds_authority_site_id, aia.vendor_site_id)
and aia.org_id in
(
select organization_id org_id
from JAI_AP_TDS_ORG_TAN_V
where org_tan_num = pv_org_tan_num
--Removed the union code by Bgowrava for Bug#6129650
)
and not exists (
select '1'
from JAI_AP_TDS_INV_PAYMENTS jatip
where jatip.invoice_id = aia.invoice_id
and jatip.prepay_invoice_id = aida_prepayment.invoice_id ) --Added by nprashar for Bug # 6774129
having sum(aida.amount) <> 0 -- Added by nprashar for Bug # 6774129
group by
aia.org_id ,
aia.invoice_id ,
aia.invoice_num ,
aia.invoice_date ,
aia.invoice_amount ,
aia.vendor_id ,
aia.vendor_site_id ,
nvl(aia.attribute_category, lv_attribute_category) ,
aia.attribute1 ,
aida_prepayment.invoice_id
;
select
jatt.threshold_trx_id,
jatt.invoice_id,
jatc.section_code tds_section,
jatt.tax_id,
jatt.tax_rate,
jatt.taxable_amount,
jatt.tax_amount,
jatt.vendor_id,
jatt.vendor_site_id
from
jai_ap_tds_thhold_trxs jatt,
JAI_CMN_TAXES_ALL jatc
where
jatt.invoice_to_tds_authority_id = pn_invoice_to_tds_authority_id
and jatc.tax_id = jatt.tax_id
and jatc.section_type = p_section_type /*bduvarag for Bug#5647725*/ ;
select
aca.check_id check_id,
aca.check_date check_date,
aca.amount check_amount,
aipa.invoice_payment_id invoice_payment_id
from
ap_checks_all aca,
ap_invoice_payments_all aipa
where aca.check_id = aipa.check_id
and aipa.invoice_id = pn_invoice_id;
select sum(nvl(taxable_basis,0))
from jai_ap_tds_inv_payments
where invoice_id = cp_invoice_id;
SELECT jatp.*
FROM jai_ap_tds_inv_payments jatp
WHERE (jatp.invoice_id , jatp.taxable_basis,
jatp.tax_amount, jatp.tds_tax_id ) IN
(SELECT invoice_id, taxable_basis, tax_amount , tds_tax_id
FROM jai_ap_tds_inv_payments
GROUP BY invoice_id, taxable_basis, tax_amount , tds_tax_id
having count(*) > 1
)
AND jatp.check_id NOT IN /* Filter out all voided and stop initiated checks*/
(SELECT check_id
FROM ap_checks_all ac
WHERE ac.check_id = jatp.check_id
AND status_lookup_code in ('VOIDED', 'STOP INITIATED')
)
AND TRUNC(jatp.creation_date) = TRUNC (sysdate)
AND jatp.form16_hdr_id IS NULL /*Pick up payments for which certificates are not generated */
ORDER BY tds_payment_id DESC ;
ln_last_update_login number(15);
ln_last_update_login := fnd_global.login_id ;
delete JAI_AP_TDS_INV_PAYMENTS
where check_id in
(
select
aca.check_id check_id
from
ap_checks_all aca ,
ap_invoice_payments_all aipa ,
ap_invoices_all aia ,
jai_ap_tds_thhold_trxs jattt,
JAI_CMN_TAXES_ALL jitc
where
aca.check_id = aipa.check_id
and aipa.invoice_id = jattt.invoice_to_tds_authority_id
and aipa.invoice_id = aia.invoice_id
and jattt.tax_id = jitc.tax_id
and aia.invoice_date between ld_tds_payment_from_date and ld_tds_payment_to_date
and aca.status_lookup_code NOT IN (lv_sts_lookup_code_argument1, lv_sts_lookup_code_argument2)
and aca.org_id in
(
select organization_id org_id
from JAI_AP_TDS_ORG_TAN_V
where org_tan_num = pv_org_tan_num
--Removed the union code by Bgowrava for Bug#6129650
)
and jattt.tds_authority_vendor_id = nvl(pn_tds_authority_id, jattt.tds_authority_vendor_id)
and jattt.tds_authority_vendor_site_id = nvl(pn_tds_authority_site_id, jattt.tds_authority_vendor_site_id)
and jattt.vendor_id = nvl(pn_vendor_id, jattt.vendor_id)
and jattt.vendor_site_id = nvl(pn_vendor_site_id, jattt.vendor_site_id)
and nvl(jitc.section_code,'XYZ') = nvl(pv_tds_section,nvl(jitc.section_code,'XYZ')) /*bduvarag for Bug#5647725*/
)
and form16_hdr_id is null;
insert into JAI_AP_TDS_INV_PAYMENTS
(
tds_payment_id ,
check_id ,
check_amount ,
check_date ,
invoice_payment_id ,
payment_amount ,
invoice_id ,
invoice_date ,
parent_invoice_id ,
parent_invoice_cancel_flag ,
threshold_trx_id ,
tds_section ,
tds_tax_id ,
tds_tax_rate ,
taxable_basis ,
tax_amount ,
tax_authority_id ,
tax_authority_site_id ,
vendor_id ,
vendor_site_id ,
org_tan_num ,
operating_unit_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
)
values
(
jai_ap_tds_inv_payments_s.nextval ,
cur_rec.check_id ,
cur_rec.check_amount ,
cur_rec.check_date ,
cur_rec.invoice_payment_id ,
cur_rec.payment_amount ,
cur_rec.invoice_id ,
cur_rec.invoice_date ,
cur_rec.parent_invoice_id ,
lv_parent_invoice_cancel_flag ,
cur_rec.threshold_trx_id ,
cur_rec.section_code ,
cur_rec.tax_id ,
cur_rec.tax_rate ,
ln_taxable_basis ,
cur_rec.tax_amount ,
cur_rec.tax_authority_id ,
cur_rec.tax_authority_site_id ,
cur_rec.vendor_id ,
cur_rec.vendor_site_id ,
pv_org_tan_num ,
cur_rec.org_id ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_last_update_login ,
ln_program_id ,
ln_program_login_id ,
ln_program_application_id ,
ln_request_id
);
insert into JAI_AP_TDS_INV_PAYMENTS
(
tds_payment_id ,
check_id ,
check_amount ,
check_date ,
invoice_payment_id ,
payment_amount ,
invoice_id ,
invoice_date ,
parent_invoice_id ,
parent_invoice_cancel_flag ,
threshold_trx_id ,
tds_section ,
tds_tax_id ,
tds_tax_rate ,
taxable_basis ,
tax_amount ,
tax_authority_id ,
tax_authority_site_id ,
vendor_id ,
vendor_site_id ,
org_tan_num ,
operating_unit_id ,
source ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
)
values
(
jai_ap_tds_inv_payments_s.nextval ,
cur_rec.check_id ,
cur_rec.check_amount ,
cur_rec.check_date ,
cur_rec.invoice_payment_id ,
cur_rec.payment_amount ,
cur_rec.invoice_id ,
cur_rec.invoice_date ,
ln_parent_invoice_id ,
lv_parent_invoice_cancel_flag ,
null ,
lv_section_code ,
ln_tax_id ,
ln_tax_rate ,
ln_taxable_basis ,
ln_tax_amount ,
cur_rec.tax_authority_id ,
cur_rec.tax_authority_site_id ,
r_ap_invoices_all.vendor_id, --ln_vendor_id , Harshita for Bug 4643633
r_ap_invoices_all.vendor_site_id , --ln_vendor_site_id , Harshita for Bug 4643633
pv_org_tan_num ,
cur_rec.org_id ,
'Invoice prior to threshold' ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_last_update_login ,
ln_program_id ,
ln_program_login_id ,
ln_program_application_id ,
ln_request_id
);
-- so that the difference will be updated for prepay lines.
Open c_get_total_tax_basis(cur_rec.invoice_id);
insert into JAI_AP_TDS_INV_PAYMENTS
(
tds_payment_id ,
check_id ,
check_amount ,
check_date ,
invoice_payment_id ,
prepay_invoice_id ,
payment_amount ,
invoice_id ,
invoice_date ,
parent_invoice_id ,
parent_invoice_cancel_flag ,
threshold_trx_id ,
tds_section ,
tds_tax_id ,
tds_tax_rate ,
taxable_basis ,
tax_amount ,
tax_authority_id ,
tax_authority_site_id ,
vendor_id ,
vendor_site_id ,
org_tan_num ,
operating_unit_id ,
source ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
)
values
(
jai_ap_tds_inv_payments_s.nextval ,
r_get_payment_details.check_id ,
r_get_payment_details.check_amount ,
r_get_payment_details.check_date ,
r_get_payment_details.invoice_payment_id ,
cur_rec.prepay_invoice_id ,
cur_rec.prepaid_amount ,
cur_rec.invoice_id ,
cur_rec.invoice_date ,
ln_parent_invoice_id ,
lv_parent_invoice_cancel_flag ,
ln_threshold_trx_id ,
lv_section_code ,
ln_tax_id ,
ln_tax_rate ,
ln_taxable_basis ,
ln_tax_amount ,
cur_rec.tax_authority_id ,
cur_rec.tax_authority_site_id ,
ln_vendor_id ,
ln_vendor_site_id ,
pv_org_tan_num ,
cur_rec.org_id ,
'Invoice paid by prepayment' ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_last_update_login ,
ln_program_id ,
ln_program_login_id ,
ln_program_application_id ,
ln_request_id
);
-- this will update the tax amount with the payment amount for
-- all lines which has been paid by check.
update jai_ap_tds_inv_payments
set tax_amount = payment_amount
where invoice_id = cur_rec.invoice_id
and prepay_invoice_id is null
and nvl(source,'ABC') <> 'Invoice paid by prepayment';
UPDATE jai_ap_tds_inv_payments jatp
SET jatp.taxable_basis = round(jatp.taxable_basis * jatp.payment_amount / jatp.tax_amount,2)
WHERE jatp.tds_payment_id = c_get_multiple_payments.tds_payment_id ;
UPDATE jai_ap_tds_inv_payments jatp
SET jatp.tax_amount = jatp.payment_amount
WHERE jatp.tds_payment_id = c_get_multiple_payments.tds_payment_id ;
UPDATE jai_ap_tds_inv_payments jatp
SET jatp.taxable_basis = jatp.taxable_basis +
( r_get_tds_inv_details(ind).taxable_basis -
(SELECT sum(jatp1.taxable_basis)
FROM jai_ap_tds_inv_payments jatp1
WHERE jatp1.invoice_id = r_get_tds_inv_details(ind).invoice_id
AND jatp1.check_id NOT IN
(SELECT check_id
FROM ap_checks_all ac
WHERE ac.check_id = jatp.check_id
AND status_lookup_code in ('VOIDED', 'STOP INITIATED')
)
)
)
WHERE jatp.tds_payment_id = r_get_tds_inv_details(ind).tds_payment_id
AND jatp.form16_hdr_id IS NULL ;
Fnd_File.put_line(Fnd_File.LOG, 'No of records inserted into JAI_AP_TDS_INV_PAYMENTS : ' || to_char(ln_record_count));
select distinct operating_unit_id operating_unit_id
from jai_ap_tds_inv_payments
where parent_invoice_id is not null
and tds_tax_id is not null
and tds_tax_rate is not null
and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
and form16_hdr_id is null
and nvl(tds_section,'XYZ') = nvl(pv_tds_section,nvl(tds_section,'XYZ')) /*rchandan for bug#4936956. Added nvl on left hand side and nvl within nvl on right side*/
and tax_authority_id = pn_tds_authority_id
and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
and vendor_id = nvl(pn_vendor_id, vendor_id)
and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
and org_tan_num = pv_org_tan_num
and tds_tax_id in ( SELECT tax_id
FROM JAI_CMN_TAXES_ALL
WHERE section_type = p_section_type
);/*bduvarag for Bug#5647725*/
select distinct invoice_date invoice_date
from jai_ap_tds_inv_payments
where parent_invoice_id is not null
and tds_tax_id is not null
and tds_tax_rate is not null
and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
and form16_hdr_id is null
and nvl(tds_section,'XYZ') = nvl(pv_tds_section,nvl(tds_section,'XYZ')) /*rchandan for bug#4936956. Added nvl on left hand side and nvl within nvl on right side*/
and tax_authority_id = pn_tds_authority_id
and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
and vendor_id = nvl(pn_vendor_id, vendor_id)
and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
and org_tan_num = pv_org_tan_num
and operating_unit_id = pn_operating_unit_id
and tds_tax_id in ( SELECT tax_id
FROM JAI_CMN_TAXES_ALL
WHERE section_type = p_section_type
);/*bduvarag for Bug#5647725*/
select default_legal_context_id
from hr_operating_units
where organization_id = pn_organization_id;*/
select fin_year
from jai_ap_tds_years
where legal_entity_id = pn_legal_entity_id
and pd_invoice_date between start_date and end_date;
select
fin_year,
org_tan_num,
operating_unit_id,
vendor_id,
vendor_site_id,
--tds_tax_id,
tds_section,
tax_authority_id,
parent_invoice_id
from jai_ap_tds_inv_payments /*Added by nprashar for bug 6774129*/
where parent_invoice_id is not null
and tds_tax_id is not null
and tds_tax_rate is not null
and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
and form16_hdr_id is null
and tds_section = pv_tds_section
and fin_year is not null
and tax_authority_id = pn_tds_authority_id
and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
and vendor_id = nvl(pn_vendor_id, vendor_id)
and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
and org_tan_num = pv_org_tan_num
and tds_tax_id in ( SELECT tax_id
FROM JAI_CMN_TAXES_ALL
WHERE section_type = p_section_type
)
group by
fin_year,
org_tan_num,
operating_unit_id,
vendor_id,
vendor_site_id,
--tds_tax_id,
tds_section,
tax_authority_id,
parent_invoice_id
having sum(TAX_AMOUNT) = 0;
select distinct
fin_year,
org_tan_num,
operating_unit_id,
vendor_id,
vendor_site_id,
/*tds_tax_id, commented by nprashar for Bug : 6774129*/
tds_section,
tax_authority_id
from jai_ap_tds_inv_payments
where parent_invoice_id is not null
and tds_tax_id is not null
and tds_tax_rate is not null
and invoice_date between pd_tds_payment_from_date and pd_tds_payment_to_date
and form16_hdr_id is null
and nvl(tds_section,'XYZ') = nvl(pv_tds_section,nvl(tds_section,'XYZ'))/*bduvarag for Bug#5647725*/
and fin_year is not null
and tax_authority_id = pn_tds_authority_id
and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
and vendor_id = nvl(pn_vendor_id, vendor_id)
and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
and org_tan_num = pv_org_tan_num
and tds_tax_id in ( SELECT tax_id
FROM JAI_CMN_TAXES_ALL
WHERE section_type = p_section_type
);/*bduvarag for Bug#5647725*/
select nvl(certificate_num, 0) + 1
from jai_ap_tds_cert_nums
where org_tan_num = pv_org_tan_num
and fin_yr = pn_fin_year
and regime_code = pv_regime_code/*CSahoo for Bug#5631784*/
;
select rowid row_id, parent_invoice_id, threshold_trx_id
from jai_ap_tds_inv_payments
where form16_hdr_id = cp_form16_hdr_id
order by parent_invoice_id, invoice_id
for update of certificate_line_num;
select tds_event
from jai_ap_tds_thhold_trxs
where threshold_trx_id = cp_threshold_trx_id;
select jai_ap_tds_f16_hdrs_all_s.nextval from dual;
SELECT decode(p_section_type,'TDS_SECTION','TDS','WCT_SECTION','WCT','ESSI_SECTION','ESI')
FROM dual;
SELECT max(invoice_date)
from jai_ap_tds_inv_payments
where form16_hdr_id = cp_f16_hdr_id;
select to_char(start_date, 'YYYY')
from jai_ap_tds_years
where fin_year = p_fin_year;
ln_last_update_login number(15);
ln_last_update_login := fnd_global.login_id ;
update jai_ap_tds_inv_payments
set fin_year = r_ja_in_tds_year_info.fin_year
where parent_invoice_id is not null
and tds_tax_id is not null
and tds_tax_rate is not null
and invoice_date between ld_tds_payment_from_date and ld_tds_payment_to_date
and form16_hdr_id is null
and nvl(tds_section,'XYZ') = nvl(pv_tds_section,nvl(tds_section,'XYZ'))/*bduvarag for Bug#5647725*/
and tax_authority_id = pn_tds_authority_id
and tax_authority_site_id = nvl(pn_tds_authority_site_id, tax_authority_site_id)
and vendor_id = nvl(pn_vendor_id, vendor_id)
and vendor_site_id = nvl(pn_vendor_site_id, vendor_site_id)
and org_tan_num = pv_org_tan_num
and operating_unit_id = cur_ou.operating_unit_id
and invoice_date = cur_invoice_date.invoice_date
and tds_tax_id in ( SELECT tax_id
FROM JAI_CMN_TAXES_ALL
WHERE section_type = p_section_type
);/*bduvarag for Bug#5647725*/
Fnd_File.put_line(Fnd_File.LOG, ' No of records updated with Fin year : ' || to_char(sql%rowcount) );
/* Fin year update complete */
FOR cur_rec IN /*Bug 5647725 start bduvarag*/
c_group_for_no_certificate
(
ld_tds_payment_from_date ,
ld_tds_payment_to_date ,
pv_org_tan_num ,
pv_tds_section ,
pn_tds_authority_id ,
pn_tds_authority_site_id ,
pn_vendor_id ,
pn_vendor_site_id
)
LOOP
ln_form16_hdr_id := null;
update jai_ap_tds_inv_payments /*changed by nprashar for bug 6774129 */
set form16_hdr_id = -1 * ln_form16_hdr_id
, last_update_date = sysdate
, last_update_login = ln_last_update_login
where parent_invoice_id = cur_rec.parent_invoice_id
--and tds_tax_id = cur_rec.tds_tax_id --tds_tax_id,
and tds_tax_rate is not null
and invoice_date between ld_tds_payment_from_date and ld_tds_payment_to_date
and form16_hdr_id is null
and tds_section = cur_rec.tds_section
and fin_year = cur_rec.fin_year
and tax_authority_id = cur_rec.tax_authority_id
and vendor_id = cur_rec.vendor_id
and vendor_site_id = cur_rec.vendor_site_id
and org_tan_num = pv_org_tan_num
and operating_unit_id = cur_rec.operating_unit_id
and tds_tax_id in ( SELECT tax_id
FROM JAI_CMN_TAXES_ALL
WHERE section_type = p_section_type
);
update jai_ap_tds_inv_payments
set form16_hdr_id = ln_form16_hdr_id
, certificate_num = ln_certificate_num
, last_update_date = sysdate
, last_update_login = ln_last_update_login
where parent_invoice_id is not null
/*and tds_tax_id = cur_rec.tds_tax_id commented by nprashar for bug # 6774129*/
and tds_tax_rate is not null
and invoice_date between ld_tds_payment_from_date and ld_tds_payment_to_date
and form16_hdr_id is null
and nvl(tds_section,'XYZ') = nvl(cur_rec.tds_section,'XYZ') /*bduvarag for Bug#5647725*/
and fin_year = cur_rec.fin_year
and tax_authority_id = cur_rec.tax_authority_id
and vendor_id = cur_rec.vendor_id
and vendor_site_id = cur_rec.vendor_site_id
and org_tan_num = pv_org_tan_num
and operating_unit_id = cur_rec.operating_unit_id
and tds_tax_id in ( SELECT tax_id
FROM JAI_CMN_TAXES_ALL
WHERE section_type = p_section_type
);/*bduvarag for Bug#5647725*/
insert into jai_ap_tds_cert_nums
(
fin_yr_cert_id ,
regime_code ,
org_tan_num ,
fin_yr ,
certificate_num ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
)
values
(
jai_ap_tds_cert_nums_s.nextval, /*Bgowrava for Bug#6129650*/
lv_tds_section ,/*bduvarag for Bug#5647725*/ /*CSahoo for BUG#5631784*/
pv_org_tan_num ,
cur_rec.fin_year ,
1 ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_last_update_login
);
Fnd_File.put_line(Fnd_File.LOG, 'Updated certificate number in jai_ap_tds_cert_nums');
update jai_ap_tds_cert_nums
set certificate_num = ln_certificate_num
where org_tan_num = pv_org_tan_num
and fin_yr = cur_rec.fin_year
and regime_code = lv_tds_section/*bduvarag for Bug#5647725*/ /*CSahoo for BUG#5631784*/
;
/* insert into jai_ap_tds_f16_hdrs_all */
IF lv_tds_section = 'TDS' THEN/*bduvarag for Bug#5647725*/
Fnd_File.put_line(Fnd_File.LOG, 'Inserting record in jai_ap_tds_f16_hdrs_all with form16_hdr_id : ' || to_char(ln_form16_hdr_id));
insert into jai_ap_tds_f16_hdrs_all
(
form16_hdr_id ,
fin_yr ,
org_tan_num ,
certificate_num ,
certificate_date ,
vendor_id ,
vendor_site_id ,
--tds_tax_id ,/*Commented by nprashar for bug # 6774129*/
tax_authority_id ,
from_date ,
to_date ,
print_flag ,
org_id ,
tds_tax_section ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
program_id ,
program_login_id ,
program_application_id ,
request_id
)
values
(
ln_form16_hdr_id ,
cur_rec.fin_year ,
pv_org_tan_num ,
ln_certificate_num ,
ld_cert_date , /*Bug 10621438*/
cur_rec.vendor_id ,
cur_rec.vendor_site_id ,
--cur_rec.tds_tax_id ,/*Commented by nprashar for bug # 6774129*/
cur_rec.tax_authority_id ,
ld_tds_payment_from_date ,
ld_tds_payment_to_date ,
'N' ,
cur_rec.operating_unit_id ,
cur_rec.tds_section ,
ln_user_id ,
sysdate ,
ln_user_id ,
sysdate ,
ln_last_update_login ,
ln_program_id ,
ln_program_login_id ,
ln_program_application_id ,
ln_request_id
)
;
update jai_ap_tds_inv_payments
set certificate_line_num = ln_cert_line_num
where current of c_form16_cert_lines;