[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select invoice_id
into
Inv_Nbr
from FV_DISCOUNTED_INVOICES
where
invoice_id = X_Invoice_Id;
select invoice_id, entry_source
into
Inv_id, ent_source
from FV_ASSIGN_REASON_CODES
where
invoice_id = X_Invoice_Id
and entry_source = 'EBD';
using the orginal due date from fv_inv_selected_duedate */
begin
select org_due_date,org_discount_date
into x_due_date ,x_discount_date
from fv_inv_selected_duedate
where invoice_id = x_invoice_id;
Procedure INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id IN NUMBER,
X_Discount_Taken_Flag IN VARCHAR2,
X_Discount_Status_Code IN VARCHAR2,
X_Payment_Date IN DATE,
X_Effective_Discount_Rate IN NUMBER,
X_CVOF_Rate IN NUMBER,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
l_module_name VARCHAR2(200) := g_module_name || 'INSERT_FV_DISCOUNTED_INVOICES';
INSERT INTO
FV_DISCOUNTED_INVOICES(Invoice_Id,
Last_Update_Date,
Last_Updated_By ,
Last_Update_Login,
Creation_Date ,
Created_By ,
Discount_Taken_Flag ,
Payment_Date ,
Effective_Discount_Percent,
CURR_VALUE_OF_FUNDS_PERCENT ,
Discount_Status_Code,
Request_Id,
Program_Application_Id,
Program_Id,
Program_Update_Date)
VALUES(X_Invoice_Id ,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE ,
FND_GLOBAL.USER_ID,
X_Discount_Taken_Flag ,
X_Payment_Date ,
X_Effective_Discount_Rate ,
X_CVOF_Rate,
X_Discount_Status_Code,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
SYSDATE );
X_Err_Stage := 'Row already exists for the Invoice '||to_char(X_Invoice_Id)||'. Hence Insert failed';
X_Err_Stage := 'Insert Failed '||SQLERRM;
END INSERT_FV_DISCOUNTED_INVOICES;
Procedure INSERT_FV_ASSIGN_REASON_CODES(X_Invoice_Id IN NUMBER,
x_Batch_Name IN VARCHAR2,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
l_module_name VARCHAR2(200) := g_module_name || 'INSERT_FV_ASSIGN_REASON_CODES';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Starting insert_fv_assign_reason_codes');
INSERT INTO FV_ASSIGN_REASON_CODES(Invoice_Id,
Set_of_Books_Id,
/*--------------------------------------------------*/
-- Version 1.4 RCW.
/*--------------------------------------------------*/
Org_id,
/*-- end 1.4 RCW -------------------------------*/
Entry_Mode,
Entry_Source,
Checkrun_name,
Last_Update_Date,
Last_Updated_By,
Last_Update_Login,
Creation_Date,
Created_By)
VALUES(X_Invoice_Id ,
g_sob,
/*--------------------------------------------------*/
-- Version 1.4 RCW.
/*--------------------------------------------------*/
g_org_id,
/*-- end 1.4 RCW -------------------------------*/
'SYSTEM',
'EBD',
x_Batch_Name,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
SYSDATE,
FND_GLOBAL.USER_ID
);
Insert failed';
X_Err_Stage := 'Insert Failed '||SQLERRM;
END INSERT_FV_ASSIGN_REASON_CODES;
Procedure DELETE_FV_DISCOUNTED_INVOICES(X_Invoice_Id IN NUMBER,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
l_module_name VARCHAR2(200) := g_module_name || 'DELETE_FV_DISCOUNTED_INVOICES';
delete from FV_DISCOUNTED_INVOICES
where
invoice_id = X_Invoice_Id;
X_Err_Stage := 'There were no rows deleted from FV_DISCOUNTED_INVOICES for the Invoice '||to_char(X_Invoice_Id);
End DELETE_FV_DISCOUNTED_INVOICES;
Procedure UPDATE_FV_DISCOUNTED_INVOICES(X_Invoice_Id IN NUMBER,
X_Payment_Date IN DATE,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
l_module_name VARCHAR2(200) := g_module_name || 'UPDATE_FV_DISCOUNTED_INVOICES';
select discount_taken_flag
into Existing_Flag
from FV_DISCOUNTED_INVOICES
where Invoice_Id = X_Invoice_Id;
update FV_DISCOUNTED_INVOICES
set Payment_Date = X_Payment_Date,
Last_Update_Date = SYSDATE,
Last_Updated_By = FND_GLOBAL.USER_ID,
Last_Update_Login = FND_GLOBAL.LOGIN_ID
where Invoice_Id = X_Invoice_Id;
update FV_DISCOUNTED_INVOICES
set Payment_Date = X_Payment_Date,
Discount_Taken_Flag = 'N',
Effective_Discount_Percent = NULL,
Curr_Value_Of_Funds_Percent = NULL,
Discount_Status_Code = 'PAYMENT_DATE_PAST',
Last_Update_Date = SYSDATE,
Last_Updated_By = FND_GLOBAL.USER_ID,
Last_Update_Login = FND_GLOBAL.LOGIN_ID
where Invoice_Id = X_Invoice_Id;
X_Err_Stage := 'There were no rows updated for the Invoice '||to_char(X_Invoice_Id);
End UPDATE_FV_DISCOUNTED_INVOICES;
Procedure UPDATE_FV_ASSIGN_REASON_CODES(X_Invoice_Id IN NUMBER,
x_Batch_Name IN VARCHAR2,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION; --bug 5705668, AP Autoselect process uses EBD_CHECK as where clause of a query, which raised error. Hence made this as Autonomous
l_module_name VARCHAR2(200) := g_module_name || 'UPDATE_FV_ASSIGN_REASON_CODES';
update FV_ASSIGN_REASON_CODES
set Checkrun_name = x_Batch_Name,
Entry_mode = 'SYSTEM',
Last_Update_Date = SYSDATE,
Last_Updated_By = FND_GLOBAL.USER_ID,
Last_Update_Login = FND_GLOBAL.LOGIN_ID
where
Invoice_Id = X_Invoice_Id
and Entry_Source = 'EBD';
X_Err_Stage := 'There were no rows updated for the Invoice '||to_char(X_Invoice_Id);
End UPDATE_FV_ASSIGN_REASON_CODES;
select CURR_VALUE_OF_FUNDS_PERCENT
into X_Current_Value_Of_Funds_Rate
from fv_value_of_fund_periods
where trunc(X_Payment_Date) between trunc(effective_start_date)
and trunc(nvl(effective_end_date, X_Payment_Date));
select
ai.invoice_amount,
ai.invoice_date,
ai.invoice_received_date,
ai.goods_received_date,
ai.org_id,
ai.set_of_books_id,
ai.terms_date,
--aps.discount_amount_available, --Now passed as parameter
--aps.discount_date, --Now passed as parameter
ftt.terms_type
from
FV_TERMS_TYPES ftt,
AP_INVOICES ai
-- AP_PAYMENT_SCHEDULES aps
where
ftt.term_id = ai.terms_id and
ai.invoice_id = x_invoice_id and
--aps.invoice_id = ai.invoice_id and
-- aps.discount_amount_available > 0;
x_Due_Date AP_SELECTED_INVOICES.DUE_DATE%TYPE;
select invoice_num
into X_Invoice_Num
from ap_invoices_all
where invoice_id = X_Invoice_id;
DELETE_FV_DISCOUNTED_INVOICES(X_Invoice_Id, X_Err_Nbr, X_Err_Stage);
INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id,
'Y',
'CVOF_RATE_UNAVAILABLE',
X_Payment_Date,
X_Effective_Discount_Rate,
X_CVOF_Rate,
X_Err_Nbr ,
X_Err_Stage);
/* Insert invoice into FV_DISCOUNTED_INVOICES */
INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id,
'N',
'NOT_EBD',
X_Payment_Date,
X_Effective_Discount_Rate,
X_CVOF_Rate,
X_Err_Nbr ,
X_Err_Stage);
INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id,
'Y',
NULL,
X_Payment_Date,
X_Effective_Discount_Rate,
X_CVOF_Rate,
X_Err_Nbr ,
X_Err_Stage);
UPDATE_FV_DISCOUNTED_INVOICES(X_Invoice_Id,X_Payment_Date,
X_Err_Nbr,X_Err_Stage);
UPDATE_FV_ASSIGN_REASON_CODES(X_Invoice_Id,x_Batch_Name,
X_Err_Nbr,X_Err_Stage);
INSERT_FV_DISCOUNTED_INVOICES(X_Invoice_Id,
'N',
'PAYMENT_DATE_PAST',
X_Payment_Date,
X_Effective_Discount_Rate,
X_CVOF_Rate,
X_Err_Nbr ,
X_Err_Stage);
-- Ver 1.2 Added Procedure INSERT_FV_ASSIGN_REASON_CODES RCW
/*----------------------------------------------------------*/
IF fnd_profile.value('USE_DISCOUNT_LOST_REASON_CODES') = 'Y'
AND X_Terms_Type = 'PROMPT PAY' THEN
If ROW_EXISTS_FVRC(X_Invoice_Id,X_Err_Nbr,X_Err_Stage) then
IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Record exists in FVRC');
UPDATE_FV_ASSIGN_REASON_CODES(X_Invoice_Id,x_Batch_Name,
X_Err_Nbr,X_Err_Stage);
INSERT_FV_ASSIGN_REASON_CODES(X_Invoice_Id,
x_Batch_Name,
X_Err_Nbr,
X_Err_Stage);