LOAD DATA
INFILE '/u01/apps/webappl/xxt/11.5.0/bin/XXGL_DAILY.dat'
TRUNCATE INTO TABLE XTG_GL_DAILY_RATES_STG
FIELDS TERMINATED BY','
OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(
FROM_CURRENCY
,TO_CURRENCY
,FROM_CONVERSION_DATE
,TO_CONVERSION_DATE
,USER_CONVERSION_TYPE
,CONVERSION_RATE
,MODE_FLAG
--,INVERSE_CONVERSION_RATE
--,USER_ID
)
"USD","INR","01-JUL-2011","01-AUG-2011","Corporate",41.5,"I"
"USD","GBP","02-JUL-2011","02-AUG-2011","Corporate",61.3,"I"
"USD","SGD","03-JUL-2011","03-AUG-2011","Corporate",42.5,"I"
"USD","NZD","04-JUL-2011","04-AUG-2011","Corporate",62.3,"I"
"USD","AUD","05-JUL-2011","05-AUG-2011","Corporate",63.3,"I"
"USD","CAD","06-JUL-2011","06-AUG-2011","Corporate",64.3,"I"
"USD","ZAR","07-JUL-2011","07-AUG-2011","Corporate",65.3,"I"
"USD","JPY","08-JUL-2011","08-AUG-2011","Corporate",66.3,"I"
CREATE OR REPLACE PACKAGE GL_DAILY_PKG
AS
PROCEDURE GL_DAILY_PROC(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2);
END GL_DAILY_PKG;
/
CREATE OR REPLACE PACKAGE body GL_DAILY_PKG
AS
PROCEDURE GL_DAILY_PROC(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2)
IS
--CURSOR DECLARATION
CURSOR GL_DR
IS
SELECT * FROM XTG_GL_DAILY_RATES_STG;
TYPE GL_DAILY_TBL IS TABLE OF XTG_GL_DAILY_RATES_STG%ROWTYPE
INDEX BY BINARY_INTEGER;
GL_DR_INTERFACE GL_DAILY_PROC;
L_FROM_CURRENCY VARCHAR2(15);
L_TO_CURRENCY VARCHAR2(15);
L_USER_ID NUMBER(15);
L_FLAG VARCHAR2(10);
L_MSG VARCHAR2(100);
L_USER_CONVERSION_TYPE VARCHAR2(30);
BEGIN
DELETE FROM GL_DAILY_RATES_INTERFACE;
COMMIT;
OPEN GL_DR;
FETCH GL_DR BULK COLLECT INTO GL_DR_INTERFACE;
CLOSE GL_DR;
FOR GL_DR_INTERFACE IN GL_DR
LOOP
L_FLAG:='Y';
--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_MEG:='CURRENCY CODE DOES NOT EXIST';
fnd_file.put_line
(fnd_file.LOG,
'Inserting data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg
);
END;
--END OF CURRENCY CODE VALIDATION
--USER ID COLUMN VALIDATION
BEGIN
SELECT USER_ID
INTO L_USER_ID
FROM FND_USER
WHERE USER_ID = GL_DR_INTERFACE.USER_ID;
EXCEPTION
WHEN OTHERS THEN
L_FLAG:='N';
L_MSG:='USER ID DOES NOT EXIST';
fnd_file.put_line
(fnd_file.LOG,
'Inserting data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg
);
END;
--END OF USER_ID VALIDATION
--USER CONVERSION TYPE VALIDATION
BEGIN
SELECT USER_CONVERSION_TYPE
INTO L_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES
WHERE USER_CONVERSION=GL_DR_INTERFACE.USER_CONVERSION_TYPE;
EXCEPTION
WHEN OTHERS THEN
L_FLAG:='N';
L_MSG:='USER CONVERSION TYPE DOES NOT EXIST';
fnd_file.put_line
(fnd_file.LOG,
'Inserting data into the Interface TABLE'
|| '-'
|| l_count
|| ' '
|| l_error_msg
);
END;
--END OF USER CONVERSION TYPE VALIDATION
IF L_FLAG='Y'
THEN
INSERT INTO GL_DAILY_RATES_INTERFACE
(
FROM_CURRENCY
,TO_CURRENCY
,FROM_CONVERSION_DATE
,TO_CONVERSION_DATE
,USER_CONVERSION_TYPE
,CONVERSION_RATE
,MODE_FLAG
,USER_ID
)
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
,GL_DR_INTERFACE.USER_ID
);
END IF;
L_FLAG:=NULL;
L_MEG:=NULL;
END LOOP;
COMMIT;
END;
END;
