Business Intelligence (BI) - ERP - Dashboards

Here the sample code of Interface program is presented for training purpose only.

DAILY RATES INTERFACE (ORACLE GENERAL LEDGER)

USING UTL_FILE PACKAGE


DECLARE
FP UTL_FILE.FILE_TYPE;
FROM_CURR VARCHAR2(15);
TO_CURR VARCHAR2(15);
FROM_CON_DATE DATE;
TO_CONV_DATE DATE;
USER_CON_TYPE VARCHAR2(30);
CONV_RATE NUMBER;
MODE_FLG VARCHAR2(1);
MY_LINE VARCHAR2(150);
BEGIN
FP:=UTL_FILE.FOPEN('D:\ORACLE\PRODDB\9.2.0\PLSQL\TEMP','dailyrates.dat','R');
UTL_FILE.GET_LINE(FP,MY_LINE);
LOOP
UTL_FILE.GET_LINE(FP,MY_LINE);
FROM_CURR:=SUBSTR(MY_LINE,1,3);
TO_CURR:=SUBSTR(MY_LINE,6,3);
FROM_CON_DATE:=SUBSTR(MY_LINE,10,11);
TO_CONV_DATE:=RTRIM(SUBSTR(MY_LINE,24,11));
USER_CON_TYPE:=RTRIM(SUBSTR(MY_LINE,36,9));
CONV_RATE:=TO_NUMBER(RTRIM(SUBSTR(MY_LINE,50,2)));
MODE_FLG:=SUBSTR(MY_LINE,59,1);
DBMS_OUTPUT.PUT_LINE(FROM_CURR||' '||TO_CURR||' ' ||FROM_CON_DATE||' '||TO_CONV_DATE||' '||
USER_CON_TYPE||' '||CONV_RATE||' '||MODE_FLG);
INSERT INTO GL.GL_DAILY_RATES_INTERFACE(FROM_CURRENCY,TO_CURRENCY,FROM_CONVERSION_DATE,TO_CONVERSION_DATE,USER_CONVERSION_TYPE,CONVERSION_RATE,MODE_FLAG)
VALUES(FROM_CURR,TO_CURR,FROM_CON_DATE,TO_CONV_DATE,USER_CON_TYPE,CONV_RATE,MODE_FLG);
DBMS_OUTPUT.PUT_LINE('One row successfully inserted');
COMMIT;
END LOOP;
UTL_FILE.FCLOSE(FP);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

dailyrates.dat /* This file should be placed in D:\ORACLE\PRODDB\9.2.0\PLSQL\TEMP */

FROM TO FROM_CON_DATE TO_CON_DATE USER_CON_TYPE CON_RATE MODE_F
USD INR 20-AUG-2002 20-AUG-2002 Spot 47 I
USD INR 21-AUG-2002 21-AUG-2002 Corporate 48 I
______________________________________________________________________________________________

USING SQL*LOADER

LOAD DATA
INFILE *
APPEND
INTO TABLE GL_DAILY_RATES_INTERFACE
FIELDS TERMINATED BY ‘,’
(FROM_CURRENCY,TO_CURRENCY,FROM_CONVERSION_DATE,TO_CONVERSION_DATE,USER_CONVERSION_TYPE,CONVERSION_RATE,MODE_FLAG)

BEGINDATA
USD,INR,22-AUG-2002,22-AUG-2002,Spot,47,I
USD,INR,23-AUG-2002,23-AUG-2002,Corporate,48,I

TAX INTERFACE (ORACLE RECEIVABLES)

USING SQL*LOADER

LOAD DATA
INFILE *
INTO TABLE AR_TAX_INTERFACE
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
(REQUEST_ID,INTERFACE_LINE_ID,CREATED_BY,CREATION_DATE,SEGMENT_QUALIFIER,RATE_TYPE,LOCATION_ID,
LOCATION_VALUE

Filed under: DBA (DataBase Administrator), ERP, Interfaces, Interview Questions(& answers), Oracle Apps, PL/SQL, SQL

Free Dashboards