[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
added the who columns in the insert of JAI_CMN_ERRORS_T
Dependencies Due to this bug:-
None
Bug 4870243, File version 120.5
Issue : Invoice Import Program is rejecting the Invoices.
Fix : Commented the voucher_num insert into the ap_invoices_interface table
4 23/02/07 bduvarag for bug#4716884,File version 120.7
Forward porting the changes done in 11i bug 4629783
------------------------------------------------------------------------------------------------------------------------*/
PROCEDURE cancel_invoice
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_invoice_id IN NUMBER --cbabu Bug#2448040
) IS
--cbabu Bug#2448040
lv_statement_no VARCHAR2(3); -- := '0'; --Ramananda for File.Sql.35
SELECT *
FROM ja_in_ap_inv_cancel_temp;
SELECT *
FROM ja_in_ap_inv_cancel_temp
WHERE invoice_id = p_inv_id;*/
select
invoice_id,
invoice_num,
vendor_id,
org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
from ap_invoices_all
where invoice_id = p_inv_id;
SELECT tds_invoice_num,tds_tax_id,dm_invoice_num,tds_amount,tds_tax_rate,invoice_amount -- 4333449
FROM JAI_AP_TDS_INVOICES
WHERE invoice_id = inv_id;
SELECT vendor_id
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = t_id;
SELECT payment_status_flag,invoice_amount,invoice_id, cancelled_date
FROM ap_invoices_all
WHERE invoice_num = inv_num
AND vendor_id = vend_id
AND NVL(org_id, 0) = NVL(organization, 0);
CURSOR for_distribution_insertion(inv_id NUMBER) IS
SELECT distribution_line_number,accounting_date,accrual_posted_flag,reversal_flag,
assets_addition_flag,assets_tracking_flag,cash_posted_flag,dist_code_combination_id,
accts_pay_code_combination_id,
period_name,set_of_books_id,
amount,match_status_flag,base_amount_to_post,prepay_amount_remaining,
parent_invoice_id,org_id,description
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND distribution_line_number = (SELECT MAX(distribution_line_number)
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id);
SELECT invoice_type_lookup_code,vendor_id,vendor_site_id,invoice_currency_code,
exchange_rate,exchange_rate_type,exchange_date,terms_id,payment_method_lookup_code,
pay_group_lookup_code,goods_received_date,invoice_received_date --added on 03-12-2001
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT Invoice_date
FROM Ap_Invoices_All
WHERE Invoice_Id = inv_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 -1 * invoice_amount
from ap_invoices_all
where invoice_num = p_invoice_num
and vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id;
SELECT payment_priority
FROM ap_payment_schedules_all
WHERE invoice_id = p_invoice_id;
SELECT exchange_rate
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
for_distribution_insertion_rec for_distribution_insertion%ROWTYPE;
for_insertion_invoice_id NUMBER;
insertion_amount NUMBER := 0;
v_out_last_update_date date;
select cancelled_date
from ap_invoices_all
where invoice_num = p_invoice_num
and vendor_id = p_vendor_id
and vendor_site_id = p_vendor_site_id ;
2 04-JUL-2002 Vijay Shankar: Code added to process only one invoice and delete the same
Bug#2448040
3. 29-july-2002 Aparajita for bug # 2475416
Approved TDS and CM setup was allowed at null site, so changed the code to look into null site setup
if the site setup does not exist for the vendor for pre approved TDS and CM.
This was done to find out if the approval of TDS an CM concurrent needs to be submitted.
4. 23-sep-2002 Aparajita for bug # 2503751
Populate the invoice id of the original invoice in attribute1 of the tds related invoice for
context value 'India Original Invoice for TDS'
5. 05-nov-2002 Aparajita for bug # 2586784
While generating the negative distribution line, checking for open/close accounting
period for accounting date.
Using ap_utilities_pkg procedures get_current_gl_date and get_open_gl_date.
6. 26-MAR-2003 Vijay Shankar for Bug# 2869481, FileVersion# 615.4
when an invoice is cancelled, then related TDS invoices also gets cancelled. upon cancelling the tds invoice,
ap_invoice_payments_all should get updated
with amount_remaining as 0 which is not happening previously and fixed with this bug.
An update statement is written on ap_payment_schedules_all to update amount_remaining and gross_amount fields
7. 27-apr-2003 Aparajita for bug#2906202. Version#616.1
The negative distribution line in the TDS invoice, has the period name of the original distribution line it was reversing.
Added the check to populate the period name of the current accounting period.
8. 15-oct-2003 kpvs for bug # 3109138, version # 616.2
Changed the procedure to insert v_open_gl_date instead of trunc(sysdate)
as invoice_date into ap_invoices_interface and as accounting_date into
ap_invoice_lines_interface.
This is for the invoice_date and GL date of the SI created for
supplier on cancellation of the vendor invoice.
9. 29-oct-2003 Aparajita for bug#3205957. Version#616.3
When the base invoice is getting cancelled, this procedure is cancelling the related tds
invoice. It was not updating the pay_curr_invoice_amount field in ap_invoices_all for the
tds invoice. Because of this the amount was still being displayed as before cancellation
in the payment schedule screen. Added code to update it to 0.
10. 8-apr-2004 Aparajita for bug#3536079. Version#619.1
TDS invoice was being cancelled manually, that is being manually updated
by our code. This was creating problem as in this case accounting was not
being done for the reversed line as all info like accounting event id is
not being populated. Moreoevr, with any additional change / validation
in base, we had to change the code.
To avoid all this, used base API ap_cancel_pkg.Ap_Cancel_Single_Invoice
to cancel the TDS invoice. This way,much of the task is being done by base
and hence no inconsistency.
11 12-apr-2004 Aparajita for bug#3556035. Version#619.2
When the stadard invoice being cancelled is in forex, the
cancellation invoice generated for rversing the TDS amount was
getting generated in wrong currency. Amount always in INR, but
currency same as the stadard invoice.
Added a cursor c_get_can_inv_amount to fetch the amount from
the credit memo created initially for the invoice.
12. 5-may-2004 Aparajita for bug#3607133. Version#619.3
This procedure gets invoked whenever a base invoice having TDS is cancelled.
The functionality required here is to cancel the related TDS invoice and generate
a CAN invoice to negate the credit memo.
The problem is if the credit memo is already cancelled, there is no need to
generate the CAN invoice to negate it. Added a cursor to check the status
of the credit memo before creating the CAN invoice.
13. 31-jan-2005 rchandan - bug#4149343 - File version 115.1
When an invoice is cancelled , it was not getting cancelled and instead throwing an error
cannot update null into ap_invoices_all.last_update_date. This error was captured in
JAI_CMN_ERRORS_T on the client's instance through an exception handler.
The value to be set into this column was retreived through an API call to ap_cancel_pkg.Ap_Cancel_Single_Invoice
Due to some internal exception, the out parameter corresponding to the the last_update_date is null
Spoke to lgopalsa she was of the opinion that in the ap_cancel_pkg.Ap_Cancel_Single_Invoice API ,
the standard who columns are being set anyway and there is no need to set it explicitly in the
procedure.
As part of this fix , removed the last_update_date from the columns in the update ap_invoices_all
table.
14 25/3/2005 Aparajita - Bug#4088186 . File Version # 115.3 TDS Clean up.
Removed usage of table ja_in_ap_inv_cancel_temp.
15. 02/05/2005 rchandan for bug#4333449. Version 116.1
India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
is not populated whenevr 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.
16. 24/05/2005 Ramananda for bug# 4388958 File Version: 116.1
Changed AP Lookup code from 'TDS' to 'INDIA TDS'
17. 02/05/2005 Ramananda for bug#4407165 File Version: 116.2
Added Exception block for non-compliant procedures and functions
18. 02/05/2005 Ramananda for bug# 4407184 File Version: 116.3
SQL Bind Varibale Compliance is done
19. 08-Jun-2005 Version 116.4 jai_ap_tds_old -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
20. 14-Jun-2005 rchandan for bug#4428980, Version 116.5
Modified the object to remove literals from DML statements and CURSORS.
21. 02/05/2005 Ramananda for bug# 4407184 File Version: 116.6
Re-done: SQL Bind Varibale Compliance
22. 23-Aug-2005 Bug 4559756. Added by Lakshmi Gopalsami version 120.2
Passed org_id in call to ap_utilities_pkg to get the correct gl_date and period_name
--------------------------------------------------------------------------------------------*/
-- OPEN cancelled_invoices;
UPDATE ap_invoices_all
SET cancelled_date = SYSDATE,
cancelled_amount = for_pay_status_tds_rec.invoice_amount,
cancelled_by = cancelled_invoices_rec.last_updated_by,
base_amount = 0,
invoice_amount = 0,
pay_curr_invoice_amount = 0 -- added by bug#3205957
WHERE invoice_num = tds_invoices_rec.tds_invoice_num
AND vendor_id = vendor_rec.vendor_id
AND NVL(org_id, 0) = NVL(cancelled_invoices_rec.org_id, 0);
UPDATE ap_payment_schedules_all
SET gross_amount = 0,
amount_remaining = 0
-- , inv_curr_gross_amount = 0
-- base applications is not updating this field when a standard invoice is cancelled. so not updating this field in our code also
WHERE invoice_id in (select invoice_id
from ap_invoices_all
WHERE invoice_num = tds_invoices_rec.tds_invoice_num
AND vendor_id = vendor_rec.vendor_id
AND NVL(org_id, 999999) = NVL(cancelled_invoices_rec.org_id, 999999)
);
OPEN for_distribution_insertion(for_pay_status_tds_rec.invoice_id);
FETCH for_distribution_insertion INTO for_distribution_insertion_rec;
CLOSE for_distribution_insertion;
/*UPDATE ap_invoices_all
SET invoice_amount = 0
WHERE invoice_id = for_pay_status_tds_rec.invoice_id;
for_distribution_insertion_rec.accounting_date,
cancelled_invoices_rec.org_id);
for_distribution_insertion_rec.accounting_date,
v_open_period,
v_open_gl_date,
cancelled_invoices_rec.org_id
);
raise_application_error(-20001,'No Open period ... after '||for_distribution_insertion_rec.accounting_date);
v_open_gl_date := for_distribution_insertion_rec.accounting_date;
v_open_period := for_distribution_insertion_rec.period_name; -- bug#2906202
cancelled_invoices_rec.last_updated_by,
cancelled_invoices_rec.last_update_login ,
--for_distribution_insertion_rec.set_of_books_id ,
v_open_gl_date ,
--v_open_period ,
v_out_message_name ,
v_out_invoice_amount ,
v_out_base_amount ,
--v_out_tax_amount ,
v_out_temp_cancelled_amount ,
v_out_cancelled_by ,
v_out_cancelled_amount ,
v_out_cancelled_date ,
v_out_last_update_date ,
v_out_original_prepay_amount,
--null, -- check_id ,
v_out_pay_curr_inv_amount ,
lv_token,
'India Localization - cancel TDS invoice'
);
update ap_invoices_all
set
invoice_amount = v_out_invoice_amount ,
base_amount = v_out_base_amount ,
tax_amount = v_out_tax_amount ,
temp_cancelled_amount = v_out_temp_cancelled_amount ,
cancelled_by = v_out_cancelled_by ,
cancelled_amount = v_out_cancelled_amount ,
cancelled_date = v_out_cancelled_date ,---4149343
original_prepayment_amount = v_out_original_prepay_amount,
pay_curr_invoice_amount = v_out_pay_curr_inv_amount
where invoice_id = for_pay_status_tds_rec.invoice_id;
INSERT INTO ap_invoice_distributions_all
(
accounting_date,
accrual_posted_flag,
reversal_flag,
assets_addition_flag,
assets_tracking_flag,
cash_posted_flag,
distribution_line_number,
dist_code_combination_id,
accts_pay_code_combination_id,
invoice_id,
period_name,
set_of_books_id,
amount,
match_status_flag,
base_amount_to_post,
prepay_amount_remaining,
parent_invoice_id,
line_type_lookup_code,
last_updated_by,
last_update_date,
org_id,
invoice_distribution_id, -- Added on 15-Sep-2000
description,
posted_flag
)
VALUES
(
v_open_gl_date, -- for_distribution_insertion_rec.accounting_date, commented by Aparajita on 05/11/2002 for bug # 2586784
'N', --for_distribution_insertion_rec.accrual_posted_flag,
'Y',
for_distribution_insertion_rec.assets_addition_flag,
for_distribution_insertion_rec.assets_tracking_flag,
for_distribution_insertion_rec.cash_posted_flag,
for_distribution_insertion_rec.distribution_line_number + 1,
for_distribution_insertion_rec.dist_code_combination_id,
for_distribution_insertion_rec.accts_pay_code_combination_id,
for_pay_status_tds_rec.invoice_id,
v_open_period, -- for_distribution_insertion_rec.period_name, bug#2906202
for_distribution_insertion_rec.set_of_books_id,
(-1)*for_pay_status_tds_rec.invoice_amount,
for_distribution_insertion_rec.match_status_flag,
for_distribution_insertion_rec.base_amount_to_post,
for_distribution_insertion_rec.prepay_amount_remaining,
for_distribution_insertion_rec.parent_invoice_id,
'ITEM',
cancelled_invoices_rec.last_updated_by,
cancelled_invoices_rec.last_update_date,
for_distribution_insertion_rec.org_id,
ap_invoice_distributions_s.NEXTVAL, -- Added on 15-sep-2000
for_distribution_insertion_rec.description,
'N'
);
INSERT INTO ap_invoices_interface
(
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
description,
source,
--voucher_num, Harshita for Bug 4870243
payment_method_lookup_code,
pay_group_lookup_code,
org_id,
legal_entity_id ,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
goods_received_date,
invoice_received_date,
group_id /*Bug 4716884 bduvarag*/
)
VALUES
(
ap_invoices_interface_s.NEXTVAL,
lv_invoice_num,
lv_lookup_type_code,
v_open_gl_date, -- bug 3109138 TRUNC( SYSDATE ),
for_std_invoice_rec.vendor_id,
for_std_invoice_rec.vendor_site_id,
new_std_invoice_amount,
for_std_invoice_rec.invoice_currency_code,
for_std_invoice_rec.exchange_rate,
for_std_invoice_rec.exchange_rate_type,
for_std_invoice_rec.exchange_date,
for_std_invoice_rec.terms_id,
lv_description, --rchandan for bug#4428980
lv_source, /*--'TDS', --Ramanand for bug#4388958*/ --rchandan for bug#4428980
-- lv_invoice_num, --rchandan for bug#4428980 , Harshita for Bug 4870243
for_std_invoice_rec.payment_method_lookup_code,
for_std_invoice_rec.pay_group_lookup_code,
cancelled_invoices_rec.org_id,
ln_legal_entity_id ,
cancelled_invoices_rec.created_by,
cancelled_invoices_rec.creation_date,
cancelled_invoices_rec.last_updated_by,
cancelled_invoices_rec.last_update_date,
cancelled_invoices_rec.last_update_login,
for_std_invoice_rec.goods_received_date, --Added on 03-Dec-2001
for_std_invoice_rec.invoice_received_date,
to_char(p_invoice_id) /*ug 4716884 bduvarag*/
);
INSERT INTO ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
ap_invoices_interface_s.CURRVAL,
ap_invoice_lines_interface_s.NEXTVAL,
1, --THERE WILL ALWAYS BE ONLY ONE LINE.
lv_lookup_type_code, --rchandan for bug#4428980
new_std_invoice_amount,
v_open_gl_date, -- bug 3109138 TRUNC( SYSDATE ),
lv_description, --rchandan for bug#4428980
for_distribution_insertion_rec.dist_code_combination_id,
cancelled_invoices_rec.created_by,
cancelled_invoices_rec.creation_date,
cancelled_invoices_rec.last_updated_by,
cancelled_invoices_rec.last_update_date,
cancelled_invoices_rec.last_update_login
);
jai_ap_tds_generation_pkg.insert_tds_thhold_trxs -------4333449
(
p_invoice_id => p_invoice_id,
p_tds_event => 'OLD TDS INVOICE CANCEL',
p_tax_id => tds_invoices_rec.tds_tax_id,
p_tax_rate => tds_invoices_rec.tds_tax_rate,
p_taxable_amount => tds_invoices_rec.invoice_amount,
p_tax_amount => tds_invoices_rec.tds_amount,
p_vendor_id => for_std_invoice_rec.vendor_id,
p_vendor_site_id => for_std_invoice_rec.vendor_site_id,
p_invoice_vendor_num => 'CAN/'||SUBSTR(tds_invoices_rec.dm_invoice_num,1,47),
p_invoice_vendor_type => 'STANDARD',
p_invoice_vendor_curr => for_std_invoice_rec.invoice_currency_code,
p_invoice_vendor_amt => new_std_invoice_amount,
p_parent_inv_payment_priority => ln_prnt_pay_priority,
p_parent_inv_exchange_rate => ln_prnt_exchange_rate
);
DELETE FROM ja_in_ap_inv_cancel_temp
WHERE invoice_id = p_invoice_id; --cbabu 08/07/02 Bug#2448040
cancelled_invoices_rec.last_update_login
);
SELECT NVL( Approved_Invoice_Flag, 'N' )
INTO v_Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = for_std_invoice_rec.vendor_id
AND Vendor_Site_Id = for_std_invoice_rec.vendor_site_id;
SELECT NVL( Approved_Invoice_Flag, 'N' )
INTO v_Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = for_std_invoice_rec.vendor_id
AND Vendor_Site_Id = 0;
INSERT INTO JAI_CMN_ERRORS_T
(
APPLICATION_SOURCE, error_message, additional_error_mesg, creation_date, created_by,
last_updated_by, last_update_date -- added, Harshita for Bug 4866533
)
VALUES
(
lv_procedure_name, lv_error_mesg,
'EXCEPTION captured BY WHEN OTHERS IN the PROCEDURE. STATEMENT No:' || lv_statement_no,
SYSDATE, Fnd_Global.user_id,
fnd_global.user_id, sysdate -- added, Harshita for Bug 4866533
);
COMMIT; --commit for the above insert statement
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_org_id IN NUMBER,
p_prepay_dist_id IN NUMBER,
p_inv_dist_id IN NUMBER,
p_attribute IN VARCHAR2
)
IS
/* CURSOR check_prep_amt_app(p_id NUMBER,inv_id NUMBER) IS
SELECT amount amt_app
FROM ap_invoice_distributions_all
WHERE prepay_distribution_id = p_id
AND invoice_id = inv_id
AND amount < 0 ;
SELECT -1 * amount amt_app
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND invoice_distribution_id = p_inv_dist_id
AND prepay_distribution_id = p_id
-- AND amount < 0 ;
SELECT amount amt_unapp
FROM ap_invoice_distributions_all
WHERE prepay_distribution_id = p_id
AND invoice_id = inv_id
AND amount >0 ;
SELECT tds_invoice_num,invoice_id,invoice_amount,amt_reversed,
amt_applied,tds_tax_id,
tds_amount,
tds_tax_rate,
organization_id
FROM JAI_AP_TDS_INVOICES
WHERE invoice_id =inv_id
AND source_attribute=p_att;
SELECT tds_invoice_num,invoice_id,invoice_amount,amt_reversed,
amt_applied,tds_tax_id,
tds_amount,
tds_tax_rate,
organization_id
FROM JAI_AP_TDS_INVOICES
WHERE source_attribute=p_att
AND invoice_id = (SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id=prepay_dist_id);
SELECT tds_invoice_num,invoice_id,invoice_amount,amt_reversed,amt_applied,tds_tax_id,organization_id
FROM JAI_AP_TDS_INVOICES
WHERE invoice_id = inv_id
AND source_attribute = p_attribute;*/
SELECT vendor_id
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = t_id;
SELECT payment_status_flag,invoice_amount,invoice_id
FROM ap_invoices_all
WHERE invoice_num = inv_num
AND vendor_id = vend_id
AND NVL(org_id, 0) = NVL(organization, 0);
CURSOR for_distribution_insertion(inv_id NUMBER) IS
SELECT distribution_line_number,accounting_date,accrual_posted_flag,reversal_flag,
assets_addition_flag,assets_tracking_flag,cash_posted_flag,dist_code_combination_id,
accts_pay_code_combination_id,
period_name,set_of_books_id,
amount,match_status_flag,base_amount_to_post,prepay_amount_remaining,
parent_invoice_id,org_id,description
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND distribution_line_number = (SELECT MAX(distribution_line_number)
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id);
SELECT invoice_type_lookup_code,vendor_id,vendor_site_id,invoice_currency_code,
exchange_rate,exchange_rate_type,exchange_date,terms_id,payment_method_lookup_code,
pay_group_lookup_code,invoice_num,
invoice_received_date, -- added by Aparajita on 10/07/2002 for bug # 2439034
goods_received_date -- added by Aparajita on 10/07/2002 for bug # 2439034
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT amount
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND distribution_line_number = 1;
SELECT approved_amount,amount_applicable_to_discount
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT Invoice_Num
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 NVL( Approved_Invoice_Flag, 'N' ) Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE (Vendor_Id, vendor_site_id) =
(SELECT vendor_id, vendor_site_id FROM ap_invoices_all WHERE invoice_id = p_inv_id);
SELECT amount
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND distribution_line_number = 1;
SELECT 'RTN/'|| invoice_num || '/' ||TO_CHAR(JAI_AP_TDS_INVOICE_NUM_S.CURRVAL)
FROM DUAL;
select accounting_date
from ap_invoice_distributions_all
where invoice_distribution_id = p_inv_dist_id;
SELECT payment_priority
FROM ap_payment_schedules_all
WHERE invoice_id = p_invoice_id;
SELECT exchange_rate
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
for_distribution_insertion_rec for_distribution_insertion%ROWTYPE;
for_insertion_invoice_id NUMBER;
insertion_amount NUMBER := 0;
for_dist_tds_rec for_distribution_insertion%ROWTYPE;
for_dist_rec for_distribution_insertion%ROWTYPE;
insert_amt_ap NUMBER;
insert_inv_id NUMBER;
v_insertion_amount_tds_si NUMBER := 0; -- bug#3469847
This date is inserted as invoice_date into ap_invoices_interface
and as accounting_date into ap_invoice_lines_interface.
This would ensure that the invoice date and GL date of the TDS invoices
are in sync with the date entered on Apply/Unapply prepayment screen if the
user changes this date.
8. 31/10/2003 Aparajita for bug#3205948. Version#616.2
Applied round function to the amount of the invoices that is being generated to be in sync
with apply.
9. 08/03/2004 Aparajita for bug#3469847. Version#619.1
For Prepeyment having forex, the amount for return standard invoice for TDS authority
was not getting converted though the inv is always generated in INR.
10. 29/04/2004 Aparajita for bug#3583708. Version#619.2
The Credit memo that is generated was not picking up the right dist_code_combination_id. It has to be the same as used in case of the
stadard invoice that is being generated for the TDS authority.
11. 02/05/2005 rchandan for bug#4333449. Version 116.1
India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
is not populated whenevr 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.
----------------------------------------------------------------------------------------------------*/
BEGIN
v_Approved_Invoice_Flag := 'N'; -- added by Aparajita for bug # 2441683 on 23/07/2002 --Ramananda for File.Sql.35
insertion_amount := (for_prepay_payment_amount_rec.amount - v_amt_reversed);
insertion_amount :=FLOOR(ABS(check_prep_amt_app_rec.amt_app))
* ( for_prepay_payment_amount_rec.amount / check_tds_prepayment_rec.invoice_amount);
insertion_amount := round(insertion_amount); -- added by bug#3205948
v_insertion_amount_tds_si := insertion_amount * nvl(for_std_invoice_rec.exchange_rate, 1);
v_insertion_amount_tds_si := insertion_amount;
OPEN for_distribution_insertion(for_pay_status_original_rec.invoice_id);
FETCH for_distribution_insertion INTO for_dist_tds_rec;
CLOSE for_distribution_insertion;
SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval --JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
INTO v_tds_inv_run_num
FROM dual;
INSERT INTO ap_invoices_interface
(
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
description,
source,
-- voucher_num, Harshita for Bug 4870243
payment_method_lookup_code,
pay_group_lookup_code,
org_id,
Legal_entity_id ,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
invoice_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
goods_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
group_id /*Bug 4716884*/
)
VALUES
(
ap_invoices_interface_s.NEXTVAL,
lv_rtn_tds_inv, --'RTN/'|| v_tds_inv_num ||'/'||TO_CHAR(v_tds_inv_run_num),
'STANDARD',
v_prepay_dist_date, -- bug 3112711 kpvs TRUNC( SYSDATE ), --for_std_inv_tds_rec.invoice_date,
for_std_inv_tds_rec.vendor_id,
for_std_inv_tds_rec.vendor_site_id,
v_insertion_amount_tds_si , -- insertion_amount, bug#3469847
for_std_inv_tds_rec.invoice_currency_code,
for_std_inv_tds_rec.exchange_rate,
for_std_inv_tds_rec.exchange_rate_type,
for_std_inv_tds_rec.exchange_date,
for_std_inv_tds_rec.terms_id,
lv_rtn_tds_auth, --'RTN FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_std_inv_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ,
'INDIA TDS', /*--'TDS', ----Ramanand for bug#4388958*/
-- lv_rtn_tds_inv, --'RTN/'|| v_tds_inv_num ||'/'||TO_CHAR(v_tds_inv_run_num), Harshita for Bug 4870243
for_std_inv_tds_rec.payment_method_lookup_code,
for_std_inv_tds_rec.pay_group_lookup_code,
p_org_id,
ln_legal_entity_id ,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by,
for_std_inv_tds_rec.invoice_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
for_std_inv_tds_rec.goods_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
to_char(p_invoice_id) /*Bug 4716884*/
);
INSERT INTO ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
ap_invoices_interface_s.CURRVAL,
ap_invoice_lines_interface_s.NEXTVAL,
1, --THERE WILL ALWAYS BE ONLY ONE LINE.
lv_lookup_type_code,
v_insertion_amount_tds_si , -- insertion_amount, bug#3469847
v_prepay_dist_date, -- bug 3112711 kpvs TRUNC( SYSDATE ),
lv_description,
for_dist_tds_rec.dist_code_combination_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by
);
OPEN for_distribution_insertion(p_invoice_id); --check_tds_original_rec.invoice_id);
FETCH for_distribution_insertion INTO for_dist_rec;
CLOSE for_distribution_insertion;
SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval --JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
INTO v_sup_cm_run_num
FROM dual;
INSERT INTO ap_invoices_interface
(
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
description,
source,
-- voucher_num, Harshita for Bug 4870243
payment_method_lookup_code,
pay_group_lookup_code,
org_id,
legal_entity_id ,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
invoice_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
goods_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
group_id /*Bug 4716884*/
)
VALUES
(
ap_invoices_interface_s.NEXTVAL,
lv_invoice_num, --rchandan for bug#4428980
lv_lookup_type_code, --rchandan for bug#4428980
v_prepay_dist_date, -- bug 3112711 kpvs TRUNC( SYSDATE ),
for_std_inv_rec.vendor_id,
for_std_inv_rec.vendor_site_id,
(-1) * insertion_amount,
for_std_inv_rec.invoice_currency_code,
for_std_inv_rec.exchange_rate,
for_std_inv_rec.exchange_rate_type,
for_std_inv_rec.exchange_date,
for_std_inv_rec.terms_id,
lv_description, --rchandan for bug#4428980
lv_source, /*--'TDS', --Ramanand for bug#4388958*/ --rchandan for bug#4428980
-- lv_voucher_num, --rchandan for bug#4428980 Harshita for Bug 4870243
for_std_inv_rec.payment_method_lookup_code,
for_std_inv_rec.pay_group_lookup_code,
p_org_id,
ln_legal_entity_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by,
for_std_inv_rec.invoice_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
for_std_inv_rec.goods_received_date, -- Added by aparajita on 10/08/2002 for bug # 2439034
to_char(p_invoice_id) /*Bug 4716884*/
);
INSERT INTO ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
ap_invoices_interface_s.CURRVAL,
ap_invoice_lines_interface_s.NEXTVAL,
1, --THERE WILL ALWAYS BE ONLY ONE LINE.
lv_lookup_type_code, --rchandan for bug#4428980
(-1) * insertion_amount,
v_prepay_dist_date, -- bug 3112711 kpvs TRUNC( SYSDATE ),
lv_description, --rchandan for bug#4428980
for_dist_tds_rec.dist_code_combination_id,--bug#3583708 for_dist_rec.dist_code_combination_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by);
jai_ap_tds_generation_pkg.insert_tds_thhold_trxs -------4333449
(
p_invoice_id => p_invoice_id,
p_tds_event => 'OLD TDS PREPAY UNAPPLY',
p_tax_id => v_tds_tax_id,
p_tax_rate => ln_tax_rate,
p_taxable_amount => ln_taxable_amount,
p_tax_amount => ln_tax_amount,
p_tds_authority_vendor_id => for_std_inv_tds_rec.vendor_id,
p_tds_authority_vendor_site_id => for_std_inv_tds_rec.vendor_site_id,
p_invoice_tds_authority_num => 'RTN/'|| v_tds_inv_num ||'/'||TO_CHAR(v_tds_inv_run_num),
p_invoice_tds_authority_type => 'STANDARD',
p_invoice_tds_authority_curr => for_std_inv_tds_rec.invoice_currency_code,
p_invoice_tds_authority_amt => v_insertion_amount_tds_si,
p_vendor_id => for_std_inv_rec.vendor_id,
p_vendor_site_id => for_std_inv_rec.vendor_site_id,
p_invoice_vendor_num => for_std_inv_rec.invoice_num||'CM/'||TO_CHAR(v_sup_cm_run_num),
p_invoice_vendor_type => 'CREDIT',
p_invoice_vendor_curr => for_std_inv_rec.invoice_currency_code,
p_invoice_vendor_amt => (-1) * insertion_amount,
p_parent_inv_payment_priority => ln_prnt_pay_priority,
p_parent_inv_exchange_rate => ln_prnt_exchange_rate
);
p_last_updated_by);
insert_amt_ap := ABS(check_prep_amt_app_rec.amt_app);
insert_inv_id := check_tds_prepayment_rec.invoice_id ;
UPDATE JAI_AP_TDS_INVOICES
SET amt_reversed = NVL(amt_reversed,0) - insertion_amount,
amt_applied = NVL(amt_applied,0) - insert_amt_ap
WHERE invoice_id = insert_inv_id ;
SELECT NVL( Approved_Invoice_Flag, 'N' )
INTO v_Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = for_std_inv_rec.vendor_id
AND Vendor_Site_Id = for_std_inv_rec.vendor_site_id;
SELECT NVL( Approved_Invoice_Flag, 'N' )
INTO v_Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = for_std_inv_rec.vendor_id
AND Vendor_Site_Id = 0;
p_last_updated_by IN NUMBER,
p_last_update_date IN DATE,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_org_id IN NUMBER,
p_prepay_dist_id IN NUMBER,
p_param IN VARCHAR2,
p_attribute IN VARCHAR2
)
IS
CURSOR check_for_tds_invoice_o(inv_id NUMBER, p_att VARCHAR2) IS
SELECT tds_invoice_num,invoice_id,
invoice_amount,amt_reversed,
amt_applied,
tds_tax_id,
tds_amount, -- 4333449
tds_tax_rate, -- 4333449
organization_id
FROM JAI_AP_TDS_INVOICES
WHERE invoice_id =inv_id
AND source_attribute=p_att;
SELECT tds_invoice_num,
invoice_id,
invoice_amount,
amt_reversed,
amt_applied,
tds_tax_id,
tds_amount, -- 4333449
tds_tax_rate, -- 4333449
organization_id
FROM JAI_AP_TDS_INVOICES
WHERE source_attribute=p_att
AND invoice_id = (SELECT invoice_id
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id=prepay_dist_id);
SELECT vendor_id
FROM JAI_CMN_TAXES_ALL
WHERE tax_id = t_id;
SELECT payment_status_flag,
invoice_amount,
invoice_id
FROM ap_invoices_all
WHERE invoice_num = inv_num
AND vendor_id = vend_id
AND NVL(org_id, 0) = NVL(organization, 0);
CURSOR for_distribution_insertion(inv_id NUMBER) IS
SELECT distribution_line_number,
accounting_date,
accrual_posted_flag,
reversal_flag,
assets_addition_flag,
assets_tracking_flag,
cash_posted_flag,
dist_code_combination_id,
period_name,
set_of_books_id,
accts_pay_code_combination_id,
amount,
match_status_flag,
base_amount_to_post,
prepay_amount_remaining,
parent_invoice_id,
org_id,
description
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND distribution_line_number = (SELECT MAX(distribution_line_number)
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id);
SELECT invoice_type_lookup_code,
vendor_id,
vendor_site_id,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
payment_method_lookup_code,
pay_group_lookup_code,
invoice_num,invoice_date,
goods_received_date,
invoice_received_date
--Added the above line by pavan on 06-Jun-01 to populate the goods_received_date
--column for the RTN invoice generated
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT amount
FROM ap_invoice_distributions_all
WHERE invoice_id = inv_id
AND distribution_line_number = 1;
SELECT Invoice_Num
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 'RTN/'|| invoice_num || '/' ||TO_CHAR(JAI_AP_TDS_INVOICE_NUM_S.CURRVAL)
FROM DUAL;
SELECT invoice_amount,
payment_status_flag,
invoice_type_lookup_code,
org_id
FROM ap_invoices_all
WHERE invoice_id = inv_id;
for_dist_insertion_tds_rec for_distribution_insertion%ROWTYPE;
for_distribution_insertion_rec for_distribution_insertion%ROWTYPE;
for_insertion_invoice_id NUMBER;
insertion_amount NUMBER := 0;
FOR_DIST_INSERTION_REC for_distribution_insertion %ROWTYPE ;
for_dist_inst_rec for_distribution_insertion %ROWTYPE ;
SELECT invoice_id
FROM JAI_AP_TDS_INVOICES
WHERE tds_invoice_num = v_inv_num;
SELECT exchange_rate
FROM ap_invoices_all
WHERE invoice_id = v_inv_id;
select accounting_date
from ap_invoice_distributions_all
where invoice_distribution_id = p_invoice_distribution_id;
SELECT payment_priority
FROM ap_payment_schedules_all
WHERE invoice_id = p_invoice_id;
SELECT exchange_rate
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
select amount, prepay_distribution_id, global_attribute1, global_attribute2, global_attribute3 -- rchandan for bug#4333488
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and nvl(reversal_flag, 'N') <> 'Y'
and line_type_lookup_code <> cp_line_type_lookup_code--rchandan for bug#4428980
and global_attribute_category = cp_global_attribute_category--rchandan for bug#4428980
order by invoice_distribution_id;
select si.amount amount, pp.global_attribute1 global_attribute1, pp.global_attribute2 global_attribute2, pp.global_attribute3 global_attribute3 -- rchandan for bug#4333488
from ap_invoice_distributions_all si ,
ap_invoice_distributions_all pp
where si.invoice_id= p_invoice_id
and si.invoice_distribution_id <> p_distribution_id
and si.prepay_distribution_id = pp.invoice_distribution_id;
select tax_rate
from JAI_CMN_TAXES_ALL
where tax_id = p_tax_id;
This date is inserted as invoice_date into ap_invoices_interface and as
accounting_date into ap_invoice_lines_interface. This would ensure that the CM for
TDS authority and SI for the supplier that get created on cancellation of a vendor
invoice, would bear proper invoice date and GL date instead of sysdate.
18. 22/12/2003 Aparajita Bug#3218881. Version#618.1
Added inline function f_return_inv_amount to calculate the amount for the return
invoices when the standard invoice on which the prepayment is being applied is
approved.
As per the requirements, the amount to be reversed is to be based on the standard
invoice if the same is approved at the time of application. Application is for a
distribution line of the prepayment and with this changed approach all
distribution lines of the SI with TDS attached is considered in the first line
first basis to check the amount that should be reversed.
19. 09/02/2004 Aparajita Bug#3408429. Version#618.2
Whenever the standard invoice and prepeyment are in foreign currency, the exchange rate
should be populated if the exchange rate type is 'User'. This is hard coded as 'USER',
where as the lookup code used by base is User. Changed the code to convert the rate
type to upper case and compare it with 'USER'
20. 02/05/2005 rchandan for bug#4333449. Version 116.1
India Original Invoice for TDS DFF is eliminated. So attribute1 of ap_invoices_al
is not populated whenevr 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.
21 11/05/2005 rchandan for bug#4333488. Version 116.2
The Invoice Distribution DFF is eliminated and a new global DFF is used to
maintain the functionality. From now the TDS tax, WCT tax and ESSI will not
be populated in the attribute columns of ap_invoice_distributions_all table
instead these will be populated in the global attribute columns. So the code changes are
made accordingly.
22. 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
----------------------------------------------------------------------------------------------------------------------*/
/* Ramananda for File.Sql.35*/
debug_flag := 'N' ;
insertion_amount := (for_prepay_payment_amount_rec.amount - var_amt_reversed);
insertion_amount := round(f_return_inv_amount(p_invoice_id, p_invoice_distribution_id, p_amount, p_attribute));
insertion_amount :=round (p_amount * ( for_prepay_payment_amount_rec.amount / check_tds_prepayment_rec.invoice_amount));
OPEN for_distribution_insertion(for_pay_status_original_rec.invoice_id);
FETCH for_distribution_insertion INTO for_dist_insertion_tds_rec ;
CLOSE for_distribution_insertion ;
SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval--JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
INTO v_ja_ap_invoices_interface_no
FROM dual;
SELECT ap_invoices_interface_s.NEXTVAL
INTO v_ap_invoices_interface_no
FROM dual;
'BEFORE INSERTING INTO ap_invoices_interface 1 : CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX');
INSERT INTO ap_invoices_interface (
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
description,
source,
-- voucher_num, Harshita for Bug 4870243
payment_method_lookup_code,
pay_group_lookup_code,
org_id,
legal_entity_id ,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
group_id /*Bug 4716884*/
) VALUES (
v_ap_invoices_interface_no,
--for_std_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
-- v_tds_cm_num ||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL)
lv_tds_cm_num , --v_tds_cm_num ||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
-- the line below is being commented and replaced by line above by Aparajita on 29 apr 2002 for bug 2338345
-- var_tds_invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
'CREDIT',
v_prepay_dist_date,-- bug 3112711 kpvs --TRUNC( SYSDATE ), --for_std_invoice_tds_rec.invoice_date,
for_std_invoice_tds_rec.vendor_id,
for_std_invoice_tds_rec.vendor_site_id,
--(-1)*insertion_amount, --commented on 13-Dec-2001
((-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1)), --added on 03-Jan-2002
for_std_invoice_tds_rec.invoice_currency_code,
for_std_invoice_tds_rec.exchange_rate,
for_std_invoice_tds_rec.exchange_rate_type,
for_std_invoice_tds_rec.exchange_date,
for_std_invoice_tds_rec.terms_id,
lv_credit_note_tds, --'CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_std_invoice_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ,
'INDIA TDS', /* --'TDS',--Ramanand for bug#4388958 */
-- for_std_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.CURRVAL),
-- lv_invoice_num_cm, --for_std_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no), Harshita for Bug 4870243
for_std_invoice_tds_rec.payment_method_lookup_code,
for_std_invoice_tds_rec.pay_group_lookup_code,
p_org_id,
ln_legal_entity_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by,
to_char(p_invoice_id) /*Bug 4716884*/
);
Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoice_lines_interface 1 : CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED');
INSERT INTO ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
) VALUES (
v_ap_invoices_interface_no,
ap_invoice_lines_interface_s.NEXTVAL,
1, --THERE WILL ALWAYS BE ONLY ONE LINE.
'ITEM',
--(-1)*insertion_amount,--commented on 13-Dec-2001
(-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1), --added on 03-Jan-2002
v_prepay_dist_date, -- bug 3112711 kpvsTRUNC( SYSDATE )
lv_credit_note_tds, --'CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED BY '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id),
for_dist_insertion_tds_rec.dist_code_combination_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by
);
OPEN for_distribution_insertion(p_invoice_id);
FETCH for_distribution_insertion INTO for_dist_insertion_rec;
CLOSE for_distribution_insertion;
SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval--JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
INTO v_ja_ap_invoices_interface_no
FROM dual;
SELECT ap_invoices_interface_s.NEXTVAL
INTO v_ap_invoices_interface_no
FROM dual;
Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoices_interface 2 : STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED');
INSERT INTO ap_invoices_interface (
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
description,
source,
-- voucher_num, Harshita for Bug 4870243
payment_method_lookup_code,
pay_group_lookup_code,
org_id,
legal_entity_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
goods_received_date,
invoice_received_date, --Added by pavan on 06-Jun-01
group_id /*Bug 4716884*/
) VALUES (
v_ap_invoices_interface_no,
-- 'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
lv_rtn_invoice_num, --'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
'STANDARD',
v_prepay_dist_date, --bug 3112711 kpvs -- TRUNC(SYSDATE), --for_std_invoice_rec.invoice_date,
for_std_invoice_rec.vendor_id,
for_std_invoice_rec.vendor_site_id,
insertion_amount,
for_std_invoice_rec.invoice_currency_code,
-- for_std_invoice_rec.exchange_rate, -- commented by cbabu for Bug#2508086
DECODE( upper(for_std_invoice_rec.exchange_rate_type), 'USER', for_std_invoice_rec.exchange_rate, NULL),
-- Bug#3408429, cbabu for Bug#2508086
for_std_invoice_rec.exchange_rate_type,
for_std_invoice_rec.exchange_date,
for_std_invoice_rec.terms_id,
lv_standard_invoice_num , --'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||for_std_invoice_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id), -- NEW.prepay_id replaced with p_invoice_id
'INDIA TDS', /* --'TDS', --Ramanand for bug#4388958 */
-- 'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.CURRVAL),
-- lv_rtn_invoice_num , --'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no), Harshita for Bug 4870243
for_std_invoice_rec.payment_method_lookup_code,
for_std_invoice_rec.pay_group_lookup_code,
p_org_id,
ln_legal_entity_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by,
for_std_invoice_rec.goods_received_date,
for_std_invoice_rec.invoice_received_date,
to_char(p_invoice_id) /*Bug 4716884*/
); --Added by pavan on 06-Jun-01
Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoice_lines_interface 2 : STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED');
INSERT INTO ap_invoice_lines_interface
(
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
v_ap_invoices_interface_no,
ap_invoice_lines_interface_s.NEXTVAL,
1, --THERE WILL ALWAYS BE ONLY ONE LINE.
'ITEM',
insertion_amount,
v_prepay_dist_date, --bug 3112711 kpvs TRUNC( SYSDATE ),
lv_standard_invoice_num, --'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id) ,
--for_dist_insertion_rec.dist_code_combination_id, --Commented by Ajay Sharma on 09-AUG-01
for_dist_insertion_tds_rec.dist_code_combination_id, --Added by Ajay Sharma --on 09-AUG-01
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by
);
jai_ap_tds_generation_pkg.insert_tds_thhold_trxs -------4333449
(
p_invoice_id => p_invoice_id,
p_tds_event => 'OLD TDS INVOICE PREPAY',
p_tax_id => var_tds_tax_id,
p_tax_rate => ln_tax_rate,
p_taxable_amount => ln_taxable_amount,
p_tax_amount => ln_tax_amount,
p_tds_authority_vendor_id => for_std_invoice_tds_rec.vendor_id,
p_tds_authority_vendor_site_id => for_std_invoice_tds_rec.vendor_site_id,
p_invoice_tds_authority_num => v_tds_cm_num ||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
p_invoice_tds_authority_type => 'CREDIT',
p_invoice_tds_authority_curr => for_std_invoice_tds_rec.invoice_currency_code,
p_invoice_tds_authority_amt => ((-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1)),
p_vendor_id => for_std_invoice_rec.vendor_id,
p_vendor_site_id => for_std_invoice_rec.vendor_site_id,
p_invoice_vendor_num => 'RTN/'||for_std_invoice_rec.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
p_invoice_vendor_type => 'STANDARD',
p_invoice_vendor_curr => for_std_invoice_rec.invoice_currency_code,
p_invoice_vendor_amt => insertion_amount,
p_parent_inv_payment_priority => ln_prnt_pay_priority,
p_parent_inv_exchange_rate => ln_prnt_exchange_rate
);
p_last_updated_by
);
UPDATE JAI_AP_TDS_INVOICES
SET amt_reversed = NVL(amt_reversed,0) + insertion_amount,
amt_applied = NVL(amt_applied,0) + p_amount
WHERE invoice_id = upd_inv_id ;
SELECT NVL( Approved_Invoice_Flag, 'N' )
INTO v_Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = for_std_invoice_rec.vendor_id
AND Vendor_Site_Id = for_std_invoice_rec.vendor_site_id;
SELECT NVL( Approved_Invoice_Flag, 'N' )
INTO v_Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = for_std_invoice_rec.vendor_id
AND Vendor_Site_Id = 0;
OPEN for_distribution_insertion(for_payment_status_prepay_rec.invoice_id);
FETCH for_distribution_insertion INTO for_dist_insertion_tds_rec ;
CLOSE for_distribution_insertion ;
SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval--JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
INTO v_ja_ap_invoices_interface_no
FROM dual;
SELECT ap_invoices_interface_s.NEXTVAL
INTO v_ap_invoices_interface_no
FROM dual;
Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoices_interface 3:CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX');
INSERT INTO ap_invoices_interface (
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
description,
source,
-- voucher_num, Harshita for Bug 4870243
payment_method_lookup_code,
pay_group_lookup_code,
org_id,
legal_entity_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
group_id /*Bug 4716884*/
) VALUES (
v_ap_invoices_interface_no,
-- for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
lv_invoice_num, --for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
'CREDIT',
v_prepay_dist_date, --bug 3112711 kpvs TRUNC( SYSDATE ) --for_std_invoice_tds_rec.invoice_date,
for_pre_invoice_tds_rec.vendor_id,
for_pre_invoice_tds_rec.vendor_site_id,
--(-1)*insertion_amount,
(-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1), --added on 03-Jan-2002
for_pre_invoice_tds_rec.invoice_currency_code,
for_pre_invoice_tds_rec.exchange_rate,
for_pre_invoice_tds_rec.exchange_rate_type,
for_pre_invoice_tds_rec.exchange_date,
for_pre_invoice_tds_rec.terms_id,
lv_credit_tds_auth, --'CREDIT NOTE FOR TDS AUTHORITY FOR APPLIED AMOUNT OF TAX '||for_pre_invoice_tds_rec.invoice_num||' OR '||TO_CHAR(p_invoice_id) ,
'INDIA TDS', /* --'TDS',--Ramanand for bug#4388958*/
-- for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.CURRVAL),
-- lv_invoice_num, --for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no), Harshita for Bug 4870243
for_pre_invoice_tds_rec.payment_method_lookup_code,
for_pre_invoice_tds_rec.pay_group_lookup_code,
p_org_id,
ln_legal_entity_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by,
to_char(p_invoice_id) /*Bug 4716884*/
);
Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoice_lines_interface 3 :CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED' );
INSERT INTO ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
) VALUES (
v_ap_invoices_interface_no,
ap_invoice_lines_interface_s.NEXTVAL,
1, --THERE WILL ALWAYS BE ONLY ONE LINE.
'ITEM',
--(-1)*insertion_amount,--commented on 13-Dec-2001
(-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1), --added on 03-Jan-2002
v_prepay_dist_date, --bug 3112711 kpvs TRUNC( SYSDATE )
lv_credit_note_tds, --'CREDIT NOTE FOR TDS TAX AUTHORITY FOR TAX APPLIED BY '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id),
for_dist_insertion_tds_rec.dist_code_combination_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by
);
OPEN for_distribution_insertion(var_invoice_id);
FETCH for_distribution_insertion INTO for_dist_inst_rec;
CLOSE for_distribution_insertion;
SELECT JAI_AP_TDS_THHOLD_TRXS_S1.nextval--JAI_AP_TDS_INVOICE_NUM_S.NEXTVAL changed by rchandan for bug#4487676
INTO v_ja_ap_invoices_interface_no
FROM dual;
SELECT ap_invoices_interface_s.NEXTVAL
INTO v_ap_invoices_interface_no
FROM dual;
Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoices_interface 4: STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED');
INSERT INTO ap_invoices_interface (
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
exchange_rate,
exchange_rate_type,
exchange_date,
terms_id,
description,
source,
-- voucher_num, Harshita for Bug 4870243
payment_method_lookup_code,
pay_group_lookup_code,
org_id,
legal_entity_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
goods_received_date,
invoice_received_date, --Added by pavan on 06-Jun-01
group_id /*Bug 4716884*/
) VALUES (
v_ap_invoices_interface_no,
-- 'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.NEXTVAL),
lv_rtn_invoice_num , --'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
'STANDARD',
v_prepay_dist_date, --bug 3112711 kpvs TRUNC(SYSDATE), --for_std_invoice_rec.invoice_date,
for_std_inv_rec_r.vendor_id,
for_std_inv_rec_r.vendor_site_id,
insertion_amount,
for_std_inv_rec_r.invoice_currency_code,
-- for_std_inv_rec_r.exchange_rate, -- commented by cbabu for Bug#2508086
DECODE( upper(for_std_inv_rec_r.exchange_rate_type), 'USER', for_std_inv_rec_r.exchange_rate, NULL),
-- cbabu for Bug#2508086 Bug#3408429
for_std_inv_rec_r.exchange_rate_type,
for_std_inv_rec_r.exchange_date,
for_std_inv_rec_r.terms_id,
lv_standard_return_excess, --'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||for_std_inv_rec_r.invoice_num||' OR '||TO_CHAR(p_invoice_id) ,/**** NEW.prepay_id replaced with p_invoice_id*******/
'INDIA TDS', /*--'TDS', --Ramanand for bug#4388958*/
-- 'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(ja_in_ap_invoices_interface_s1.CURRVAL),
--lv_rtn_invoice_num, --'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no), Harshita for Bug 4870243
for_std_inv_rec_r.payment_method_lookup_code,
for_std_inv_rec_r.pay_group_lookup_code,
p_org_id,
ln_legal_entity_id,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by,
for_std_inv_rec_r.goods_received_date,
for_std_inv_rec_r.invoice_received_date, --Added by RPK on 13-DEC-01
to_char(p_invoice_id) /*Bug 4716884*/
);
Fnd_File.put_line(Fnd_File.LOG,'BEFORE INSERTING INTO ap_invoice_lines_interface 4: STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED');
INSERT INTO ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
description,
dist_code_combination_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
) VALUES (
v_ap_invoices_interface_no,
ap_invoice_lines_interface_s.NEXTVAL,
1, --THERE WILL ALWAYS BE ONLY ONE LINE.
'ITEM',
insertion_amount,
v_prepay_dist_date, --bug 3112711 kpvs TRUNC( SYSDATE )
lv_standard_return_excess, --'STANDARD INVOICE FOR RETURNING EXCESS TDS DEDUCTED ON '||TO_CHAR(p_invoice_id)||' OR '||TO_CHAR(p_invoice_distribution_id) ,
-- for_dist_inst_rec.dist_code_combination_id, --Commented by Ajay Sharma on 09-AUG-01
for_dist_insertion_tds_rec.dist_code_combination_id, --Added by Ajay Sharma on 09-AUG-01
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_updated_by
);
jai_ap_tds_generation_pkg.insert_tds_thhold_trxs -------4333449
(
p_invoice_id => p_invoice_id,
p_tds_event => 'OLD TDS INVOICE PREPAY',
p_tax_id => var_tds_tax_id,
p_tax_rate => ln_tax_rate,
p_taxable_amount => ln_taxable_amount,
p_tax_amount => ln_tax_amount,
p_tds_authority_vendor_id => for_pre_invoice_tds_rec.vendor_id,
p_tds_authority_vendor_site_id => for_pre_invoice_tds_rec.vendor_site_id,
p_invoice_tds_authority_num => for_pre_invoice_tds_rec.invoice_num||'/'|| 'CM/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
p_invoice_tds_authority_type => 'CREDIT',
p_invoice_tds_authority_curr => for_pre_invoice_tds_rec.invoice_currency_code,
p_invoice_tds_authority_amt => (-1)*insertion_amount * NVL(get_exchange_rate_base_inv_rec.exchange_rate,1),
p_vendor_id => for_std_inv_rec_r.vendor_id,
p_vendor_site_id => for_std_inv_rec_r.vendor_site_id,
p_invoice_vendor_num => 'RTN/'||for_std_inv_rec_r.invoice_num|| '/' ||TO_CHAR(v_ja_ap_invoices_interface_no),
p_invoice_vendor_type => 'STANDARD',
p_invoice_vendor_curr => for_std_inv_rec_r.invoice_currency_code,
p_invoice_vendor_amt => insertion_amount,
p_parent_inv_payment_priority => ln_prnt_pay_priority,
p_parent_inv_exchange_rate => ln_prnt_exchange_rate
);
p_last_updated_by
);
SELECT NVL( Approved_Invoice_Flag, 'N' )
INTO v_Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = for_std_inv_rec_r.vendor_id
AND Vendor_Site_Id = for_std_inv_rec_r.vendor_site_id;
SELECT NVL( Approved_Invoice_Flag, 'N' )
INTO v_Approved_Invoice_Flag
FROM JAI_CMN_VENDOR_SITES
WHERE Vendor_Id = for_std_inv_rec_r.vendor_id
AND Vendor_Site_Id = 0;
INSERT INTO JAI_CMN_ERRORS_T (
APPLICATION_SOURCE,
error_message,
additional_error_mesg,
creation_date,
created_by,
-- added, Harshita for Bug 4866533
last_updated_by, last_update_date
) VALUES (
lv_app_source, --rchandan for bug#4428980
error_mesg,
lv_add_err_msg, --rchandan for bug#4428980
SYSDATE,
fnd_global.user_id, -- USER, -- Harshita for Bug 4866533
-- added, Harshita for Bug 4866533
fnd_global.user_id, sysdate
);
SELECT Set_Of_Books_Id
FROM Ap_Invoices_All
WHERE Invoice_Id = inv_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 Tds_Invoice_Num, Dm_Invoice_Num, Tds_Tax_Id
FROM JAI_AP_TDS_INVOICES
WHERE Invoice_Id = p_invoice_id;
SELECT Vendor_Id, Vendor_Site_Id
FROM JAI_CMN_TAXES_ALL
WHERE Tax_Id = v_tds_tax_id;
SELECT Invoice_Id
FROM Ap_Invoices_All
WHERE Invoice_Num = v_inv_num
AND Vendor_Id = v_vendor_id
AND Vendor_Site_Id = v_vendor_site_id;
SELECT dm_invoice_num
FROM JAI_AP_TDS_INVOICES
WHERE invoice_id = inv_id;
SELECT cancelled_date
FROM ap_invoices_all
WHERE invoice_id = inv_id;
SELECT invoice_id, invoice_distribution_id, amount, org_id,prepay_distribution_id,line_type_lookup_code,
last_updated_by,last_update_date,created_by,creation_date
FROM ap_invoice_distributions_all
WHERE invoice_id = inv
and distribution_line_number in (select max(distribution_line_number)
from ap_invoice_distributions_all
where invoice_id = inv
and line_type_lookup_code =cp_line_type_lookup_code ) ;
SELECT invoice_num
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
SELECT Invoice_Id
FROM Ap_Invoices_All inv
WHERE Invoice_Num LIKE v_inv_num
AND Vendor_Id = v_vendor_id
AND Vendor_Site_Id = v_vendor_site_id
--following added by Aparajita to avoid approval of already approved invoice on 07/07/2002.
AND NOT EXISTS (SELECT '1'
FROM ap_invoice_distributions_all
WHERE invoice_id = inv.invoice_id
AND NVL(match_status_flag, 'T') = 'A')
;
SELECT invoice_id, vendor_id
FROM ap_invoices_all
WHERE invoice_num = p_inv_num;
req_id := Fnd_Request.submit_request('JA','JAINPREP','To Insert Prepayment Distributions',
'',FALSE,check_dist_type_r.invoice_id,check_dist_type_r.invoice_distribution_id,
ABS(check_dist_type_r.amount),check_dist_type_r.last_updated_by,check_dist_type_r.last_update_date,
check_dist_type_r.created_by,check_dist_type_r.creation_date,check_dist_type_r.org_id,
check_dist_type_r.prepay_distribution_id,'U','ATTRIBUTE1');
SELECT match_status_flag
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
UNION
SELECT 'N'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND match_status_flag IS NULL
AND EXISTS
(SELECT 'There are both untested and tested lines'
FROM ap_invoice_distributions_all
WHERE invoice_id = l_invoice_id
AND match_status_flag IN ('T','A'));
SELECT NVL(purch_encumbrance_flag,'N')
INTO encumbrance_flag
FROM financials_system_params_all
-----------------------------------------------------------------------------------------------------------------------
WHERE org_id = I_org_id ;
SELECT count(*)
INTO invoice_holds
FROM ap_holds
WHERE invoice_id = l_invoice_id
AND release_lookup_code is NULL;
SELECT ai.cancelled_date
INTO cancelled_date
FROM ap_invoices_all ai
WHERE ai.invoice_id = l_invoice_id;