GL DAILY RATES Inbound Interface By Avinash
create or replace PACKAGE BODY CURR_RATES_PKG
IS
--#########################################################################
--#
--#
--# All rights reserved
--#
--#########################################################################
--#
-
--#########################################################################
PROCEDURE CURR_RATES_PROC(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER)
IS
CURSOR gl_curr_rates
IS
SELECT *
FROM curr_rates_stg;
/**********************************************************/
--Declaring the Valiables--
/**********************************************************/
l_from_currency VARCHAR2(15);
l_to_currency VARCHAR2(15);
l_flag VARCHAR2(10);
l_msg VARCHAR2(100);
l_user_conversion_type VARCHAR2(30);
l_count NUMBER DEFAULT 0;
l_error_msg VARCHAR2(1000);
BEGIN
DELETE FROM gl_daily_rates_interface;
COMMIT;
FOR gl_dr_interface IN gl_curr_rates
LOOP
l_flag := 'Y';
l_count := l_count +1 ;
/**********************************************************/
--Currency Code Validation --
/**********************************************************/
BEGIN
SELECT currency_code
INTO l_from_currency
FROM fnd_currencies
WHERE currency_code = gl_dr_interface.from_currency;
EXCEPTION
WHEN OTHERS THEN
l_flag :='N';
l_msg :='CURRENCY CODE DOES NOT EXIST';
l_error_msg:=l_error_msg||l_msg;
UPDATE curr_rates_stg SET process_flag = 'E' , error_message =l_msg WHERE rec_num=gl_dr_interface.rec_num ;
fnd_file.put_line(fnd_file.LOG,'Error In Inserting data into the Interface Table'|| '-' || l_count||' '||l_error_msg);
END;
/**********************************************************/
-- From Conversion Date And To Conversion Date Validation --
/**********************************************************/
BEGIN
IF gl_dr_interface.from_conversion_date IS NULL THEN
l_flag :='N';
l_msg :='FROM CONVERSION DATE And TO CONVERSION DATE Should Not Be Null';
l_error_msg:=l_error_msg||l_msg;
UPDATE curr_rates_stg SET process_flag = 'E' , error_message =l_msg WHERE rec_num=gl_dr_interface.rec_num ;
fnd_file.put_line(fnd_file.LOG,'Error In Inserting data into the Interface Table'|| '-'|| l_count|| ' '||l_error_msg);
END IF;
END;
/**********************************************************/
-- Conversion Rate Validation --
/**********************************************************/
BEGIN
IF gl_dr_interface.conversion_rate IS NULL THEN
l_flag :='N';
l_msg :='CONVERSION RATE Should NOT Be Null';
l_error_msg:=l_error_msg||l_msg;
UPDATE curr_rates_stg SET process_flag = 'E' , error_message =l_msg WHERE rec_num=gl_dr_interface.rec_num;
fnd_file.put_line(fnd_file.LOG,'Error In Inserting data into the Interface Table'|| '-'|| l_count|| ' '|| l_error_msg);
END IF;
END;
/**********************************************************/
--User Conversion Type Validation --
/**********************************************************/
BEGIN
SELECT user_conversion_type
INTO l_user_conversion_type
FROM gl_daily_conversion_types
WHERE user_conversion_type=gl_dr_interface.user_conversion_type;
EXCEPTION
WHEN OTHERS THEN
l_flag :='N';
l_msg :='USER CONVERSION TYPE DOES NOT EXIST';
UPDATE curr_rates_stg SET process_flag = 'E' , error_message =l_msg WHERE rec_num=gl_dr_interface.rec_num;
l_error_msg:=l_error_msg||l_msg;
fnd_file.put_line(fnd_file.LOG,'Error In Inserting data into the Interface Table' || '-' || l_count|| ' '|| l_error_msg);
END;
IF l_flag='Y' THEN
fnd_file.put_line(fnd_file.LOG,'Inserting Data into Interface Table');
/**********************************************************/
--Inserting Data into GL Daily Rates Interface Table --
/**********************************************************/
INSERT
INTO gl_daily_rates_interface
( from_currency ,
to_currency ,
from_conversion_date ,
to_conversion_date ,
user_conversion_type ,
conversion_rate ,
mode_flag
)
VALUES
(
gl_dr_interface.from_currency ,
gl_dr_interface.to_currency ,
gl_dr_interface.from_conversion_date ,
gl_dr_interface.to_conversion_date ,
gl_dr_interface.user_conversion_type ,
gl_dr_interface.conversion_rate ,
gl_dr_interface.mode_flag
);
UPDATE curr_rates_stg SET process_flag = 'P' WHERE rec_num=gl_dr_interface.rec_num ;
END IF;
l_flag:=null;
l_msg :=NULL;
END LOOP;
DELETE FROM curr_rates_stg WHERE process_flag = 'P' ;
COMMIT;
END CURR_RATES_PROC;
END CURR_RATES_PKG ;
No comments:
Post a Comment