Saturday 24 December 2016

Oracle SP to Convert From UOM to To UOM

Just now written a function in oracle which is used for JD Edwards Unit of Measure Conversion. This function convert a value from one unit of measure to another. This is based on table F41002 & F41003. I'm doing this by converting from unit of measure [FromUOM] to Primary [PrimaryUOM] and later to unit of measure [ToUOM].

Following is the oracle function which is written and working properly. Do share if you had better / shorter code than this.


CREATE OR REPLACE FUNCTION FN_GET_CONVERSION(nItem NUMBER, sFromUOM VARCHAR2, sToUOM VARCHAR2, sProb VARCHAR2)
RETURN NUMBER
IS
  nPrimaryUnits NUMBER;
nToUnits NUMBER :=0;
nProbabilities NUMBER;
fExit VARCHAR2(1) := 'N';
sINFromUOM VARCHAR2(4);
sINToUOM VARCHAR2(4);
sINProb VARCHAR2(1);
sSQL Long;

nCFCONV NUMBER;
nCFCNV1 NUMBER;
nCFRCONV NUMBER;
nCTCONV NUMBER;
nCTCNV1 NUMBER;
nCTRCONV NUMBER;
--------------------------------------------------------------------------------------------------------------------------------
-- Main Processing
--------------------------------------------------------------------------------------------------------------------------------
BEGIN
--------------------------------------------------------------------------------------------------------------------------------


sINFromUOM := UPPER(sFromUOM);
sINToUOM := UPPER(sToUOM);
sINProb := UPPER(sProb);

-- If From and To UOM are same then return with 1
IF sINFromUOM = sINToUOM THEN
IF sProb = 'Y' THEN
RETURN 901;
ELSE
nProbabilities := 0;
RETURN nProbabilities;
END IF;
END IF;


-- Following is the Simple Query which will match exact data in from and to column in table F41002.
FOR cfF41002 IN
(
SELECT UMUM, UMRUM,
CASE WHEN UMCONV <> 0 THEN ROUND(UMCONV/10000000,9) ELSE 0.000000001 END CONV,
CASE WHEN UMCNV1 <> 0 THEN ROUND(UMCNV1/10000000,9) ELSE 0.000000001 END CNV1,
CASE WHEN UMCONV <> 0 THEN ROUND((1/(UMCONV/10000000)),9) ELSE 0.000000001 END RCNV1
FROM TNBIDVDT.F41002
WHERE UMITM = nItem AND
((UMUM = sINFromUOM AND UMRUM = sINToUOM) OR (UMUM = sINToUOM AND UMRUM = sINFromUOM)) AND ROWNUM=1
)
LOOP

-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.UMUM || ' FRUM (TO1)-' || cfF41002.UMRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' ||  cfF41002.CONV || ' FCNV1-' ||  cfF41002.CNV1 || ' FRCONV-' ||  cfF41002.RCNV1 );
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM  );


IF cfF41002.UMUM = sINFromUOM AND cfF41002.UMRUM = sINToUOM THEN
IF sProb = 'Y' THEN
nProbabilities := 1;
RETURN nProbabilities;
ELSE
RETURN cfF41002.CONV;
END IF;
ELSE
IF sProb = 'Y' THEN
nProbabilities := 2;
RETURN nProbabilities;
ELSE
RETURN cfF41002.RCNV1;
END IF;
END IF;
END LOOP;


-- Following is little complex one which will get data from table F41002
FOR cfF41002 IN
(
SELECT
TO_CHAR(I.IMUOM1) IMUOM1, F.UMUM FUM, F.UMRUM FRUM, ROUND(F.UMCONV/10000000,9) FCONV, ROUND(F.UMCNV1/10000000,9) FCNV1,
ROUND((1/F.UMCONV)*10000000,9) FRCONV,
TO_CHAR(T.UMUM) TUM, TO_CHAR(T.UMRUM) TRUM, ROUND(T.UMCONV/10000000,9) TCONV, ROUND(T.UMCNV1/10000000,9) TCNV1, ROUND((1/T.UMCONV)*10000000,9) TRCONV
FROM TNBIDVDT.F41002 F, TNBIDVDT.F41002 T, TNBIDVDT.F4101 I
WHERE F.UMITM = I.IMITM AND T.UMITM=I.IMITM AND T.UMITM = F.UMITM
AND (F.UMCONV <> 0 AND F.UMCNV1 <> 0 AND T.UMCONV <> 0 AND T.UMCNV1 <> 0)
AND F.UMITM = nItem
AND sINFromUOM IN (F.UMUM, F.UMRUM) -- OR sINToUOM IN (F.UMUM, F.UMRUM))
AND sINToUOM IN (T.UMUM, T.UMRUM) AND ROWNUM=1
)
LOOP
-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.FUM || ' FRUM (TO1)-' || cfF41002.FRUM || ' TUM-FROM2' || cfF41002.TUM || ' TRUM-TO2' || cfF41002.TRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' ||  cfF41002.FCONV || ' FCNV1-' ||  cfF41002.FCNV1 || ' FRCONV-' ||  cfF41002.FRCONV || ' TCONV-' ||  cfF41002.TCONV || ' TCNV1-' ||  cfF41002.TCNV1 || ' TRCONV-' ||  cfF41002.TRCONV);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM  );

nCFCONV := cfF41002.FCONV;
nCFCNV1 := cfF41002.FCNV1;
nCFRCONV := cfF41002.FRCONV;
nCTCONV := cfF41002.TCONV;
nCTCNV1 := cfF41002.TCNV1;
nCTRCONV := cfF41002.TRCONV;

CASE
WHEN cfF41002.FUM = sINFromUOM THEN

CASE
WHEN cfF41002.IMUOM1 = sINToUOM THEN
nProbabilities := 101;
nToUnits := nCFCNV1;
fExit := 'Y';

WHEN cfF41002.FRUM = sINToUOM THEN
nProbabilities := 102;
nToUnits := nCFCONV;
fExit := 'Y';

WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 THEN
nProbabilities := 103;
nToUnits := (1/nCTCNV1);
ELSE
nToUnits := (1/nCTCNV1)*nCFCNV1;
nProbabilities := 104;
END IF;


WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.TUM = sINFromUOM  THEN
nProbabilities := 105;
nToUnits := nCTCONV;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nProbabilities := 106;
nToUnits := (1/(nCTCNV1*nCTRCONV));
ELSE
nProbabilities := 107;
nToUnits := nCFCNV1/(nCTCNV1*nCTRCONV);
END IF;
END IF;
fExit := 'Y';

WHEN cfF41002.TUM = cfF41002.IMUOM1 THEN
nProbabilities := 108;
nToUnits := nCFCNV1 * nCTCONV;
fExit := 'Y';
END CASE;

WHEN cfF41002.FRUM = sINFromUOM THEN

CASE
WHEN cfF41002.FUM = sINToUOM THEN
nProbabilities := 201;
nToUnits := nCFRCONV;
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TRUM THEN
nProbabilities := 202;
nToUnits := nCTRCONV * nCFRCONV;
END IF;
IF cfF41002.FUM <> cfF41002.IMUOM1 AND cfF41002.TRUM = cfF41002.IMUOM1 THEN
nProbabilities := 203;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCONV;
ELSE
nProbabilities := 204;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCNV1;
END IF;

WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TUM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 AND cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFRCONV/nCTRCONV;
nProbabilities := 205;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nToUnits := nCTCONV * (1/nCTCNV1);
nProbabilities := 206;
ELSE
nToUnits := nCFCNV1/nCTCNV1;
nProbabilities := 207;
END IF;
END IF;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
IF cfF41002.FRUM = sINFromUOM THEN
nToUnits := (1/(nCTCNV1*nCTRCONV));
nProbabilities := 208;
ELSE
nToUnits := (1/nCTRCONV);
nProbabilities := 209;
END IF;

ELSE
nToUnits := (nCFCNV1*nCFRCONV)/(nCTCNV1*nCTRCONV);
nProbabilities := 210;
END IF;
END IF;
fExit := 'Y';
END CASE;

WHEN cfF41002.TUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTCNV1/nCFCNV1;
nProbabilities := 301;
WHEN cfF41002.FRUM = sINToUOM THEN
IF cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFCONV/nCFCNV1;
nProbabilities := 302;
ELSE
nProbabilities := 303;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
nToUnits := nCTCONV;
nProbabilities := 304;
END CASE;

WHEN cfF41002.TRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTRCONV/nCFCNV1;
nProbabilities := 401;
WHEN cfF41002.FRUM = sINToUOM THEN
nToUnits := ((1/nCFRCONV)/nCFCNV1)*nCTRCONV;
nProbabilities := 402;
WHEN cfF41002.TUM = sINToUOM THEN
nProbabilities := 403;
nToUnits := nCTRCONV;
END CASE;

END CASE;

EXIT WHEN fExit = 'Y';

END LOOP;

IF nToUnits <> 0 THEN
IF sProb = 'Y' THEN
RETURN nProbabilities;
ELSE
RETURN  nToUnits;
END IF;
END IF;


-- If there are no output from above two cursors then data will be fetched using F41003 in combining F41002
IF nToUnits = 0 THEN

-- Following is the Simple Query which will match exact data in from and to column in table F41003
FOR cfF41002 IN
(
SELECT UCUM, UCRUM,
CASE WHEN UCCONV <> 0 THEN ROUND(UCCONV/10000000,9) ELSE 0.000000001 END CONV,
CASE WHEN UCCONV <> 0 THEN ROUND((1/(UCCONV/10000000)),9) ELSE 0.000000001 END RCNV1
FROM TNBIDVDT.F41003
WHERE ((UCUM = sINFromUOM AND UCRUM = sINToUOM) OR (UCUM = sINToUOM AND UCRUM = sINFromUOM)) AND ROWNUM=1
)
LOOP

-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.UCUM || ' FRUM (TO1)-' || cfF41002.UCRUM );
-- DBMS_OUTPUT.PUT_LINE('FCONV-' ||  cfF41002.CONV || ' FRCONV-' ||  cfF41002.RCNV1);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM  );

IF cfF41002.UCUM = sINFromUOM AND cfF41002.UCRUM = sINToUOM THEN
IF sProb = 'Y' THEN
nProbabilities := 1001;
RETURN nProbabilities;
ELSE
RETURN cfF41002.CONV;
END IF;
ELSE
IF sProb = 'Y' THEN
nProbabilities := 1002;
RETURN nProbabilities;
ELSE
RETURN cfF41002.RCNV1;
END IF;
END IF;
END LOOP;


-- Following is little complex one which will get data from table F41002 and F41003
FOR cfF41002 IN
(
SELECT
F.IMUOM1 IMUOM1, F.UMUM FUM, F.UMRUM FRUM, F.UMCONV FCONV, F.UMCNV1 FCNV1, F.URCONV FRCONV,
T.UMUM TUM, T.UMRUM TRUM, T.UMCONV TCONV, T.UMCNV1 TCNV1, T.URCONV TRCONV
FROM
(SELECT UMITM, IMUOM1, UMUM, UMRUM, ROUND(UMCONV/10000000,9) UMCONV, ROUND(UMCNV1/10000000,9) UMCNV1, ROUND((1/UMCONV)*10000000,9) URCONV
FROM TNBIDVDT.F41002, TNBIDVDT.F4101 WHERE UMITM = IMITM AND UMITM = nItem
AND (UMCONV <> 0 AND UMCNV1 <> 0)
UNION ALL
SELECT nItem UMITM, IMUOM1, UCUM UMUM, UCRUM UMRUM, ROUND(UCCONV/10000000,9) UMCONV, (1/ROUND(UCCONV/10000000,9)) UMCNV1,
COALESCE( (SELECT ROUND(UCCONV/10000000,9) FROM TNBIDVDT.F41003 WHERE UCUM = UCUM AND UCRUM = IMUOM1 AND ROWNUM=1),1) URCONV
FROM TNBIDVDT.F41003, TNBIDVDT.F4101 WHERE IMITM = nItem) F,
(SELECT UMITM, IMUOM1, UMUM, UMRUM, ROUND(UMCONV/10000000,9) UMCONV, ROUND(UMCNV1/10000000,9) UMCNV1, ROUND((1/UMCONV)*10000000,9) URCONV
FROM TNBIDVDT.F41002, TNBIDVDT.F4101 WHERE UMITM = IMITM AND UMITM = nItem
AND (UMCONV <> 0 AND UMCNV1 <> 0)
UNION ALL
SELECT nItem UMITM, IMUOM1, UCUM UMUM, UCRUM UMRUM, ROUND(UCCONV/10000000,9) UMCONV, (1/ROUND(UCCONV/10000000,9)) UMCNV1,
COALESCE( (SELECT ROUND(UCCONV/10000000,9) FROM TNBIDVDT.F41003 WHERE UCUM = UCUM AND UCRUM = IMUOM1 AND ROWNUM=1),1) URCONV
FROM TNBIDVDT.F41003, TNBIDVDT.F4101 WHERE IMITM = nItem) T
WHERE F.UMITM = T.UMITM
AND sINFromUOM IN (F.UMUM, F.UMRUM)
AND sINToUOM IN (T.UMUM, T.UMRUM) AND ROWNUM=1
)
LOOP

-- Following lines will be useful during debuging of code using SET SERVEROUTPUT ON
-- DBMS_OUTPUT.PUT_LINE('FUM (FROM1)-' || cfF41002.FUM || ' FRUM (TO1)-' || cfF41002.FRUM || ' TUM-FROM2' || cfF41002.TUM || ' TRUM-TO2' || cfF41002.TRUM);
-- DBMS_OUTPUT.PUT_LINE('FCONV-' ||  cfF41002.FCONV || ' FCNV1-' ||  cfF41002.FCNV1 || ' FRCONV-' ||  cfF41002.FRCONV || ' TCONV-' ||  cfF41002.TCONV || ' TCNV1-' ||  cfF41002.TCNV1 || ' TRCONV-' ||  cfF41002.TRCONV);
-- DBMS_OUTPUT.PUT_LINE(' Fm-' || sINFromUOM || ' To-' || sINToUOM  );


nCFCONV := cfF41002.FCONV;
IF nCFCONV = 0 THEN
nCFCONV := 0.000000001;
END IF;
nCFCNV1 := cfF41002.FCNV1;
IF nCFCNV1 = 0 THEN
nCFCNV1 := 0.000000001;
END IF;
nCFRCONV := cfF41002.FRCONV;
IF nCFRCONV = 0 THEN
nCFRCONV := 0.000000001;
END IF;
nCTCONV := cfF41002.TCONV;
IF nCTCONV = 0 THEN
nCTCONV := 0.000000001;
END IF;
nCTCNV1 := cfF41002.TCNV1;
IF nCTCNV1 = 0 THEN
nCTCNV1 := 0.000000001;
END IF;
nCTRCONV := cfF41002.TRCONV;
IF nCTRCONV = 0 THEN
nCTRCONV := 0.000000001;
END IF;


CASE
WHEN cfF41002.FUM = sINFromUOM THEN

CASE
WHEN cfF41002.IMUOM1 = sINToUOM THEN
nProbabilities := 151;
nToUnits := nCFCNV1;
fExit := 'Y';

WHEN cfF41002.FRUM = sINToUOM THEN
nProbabilities := 152;
nToUnits := nCFCONV;
fExit := 'Y';

WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 THEN
nProbabilities := 153;
nToUnits := (1/nCTCNV1);
ELSE
nToUnits := (1/nCTCNV1)*nCFCNV1;
nProbabilities := 154;
END IF;


WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.TUM = sINFromUOM  THEN
nProbabilities := 155;
nToUnits := nCTCONV;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
nProbabilities := 156;
nToUnits := (1/(nCTCNV1*nCTRCONV));
ELSE
nProbabilities := 157;
nToUnits := nCFCNV1/(nCTCNV1*nCTRCONV);
END IF;
END IF;
fExit := 'Y';

WHEN cfF41002.TUM = cfF41002.IMUOM1 THEN
nProbabilities := 158;
nToUnits := nCFCNV1 * nCTCONV;
fExit := 'Y';
END CASE;

WHEN cfF41002.FRUM = sINFromUOM THEN

CASE
WHEN cfF41002.FUM = sINToUOM THEN
nProbabilities := 251;
nToUnits := nCFRCONV;
WHEN cfF41002.TUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TRUM THEN
nProbabilities := 252;
nToUnits := nCTRCONV * nCFRCONV;
END IF;
IF cfF41002.FUM <> cfF41002.IMUOM1 AND cfF41002.TRUM = cfF41002.IMUOM1 THEN
nProbabilities := 253;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCONV;
ELSE
nProbabilities := 254;
nToUnits := (nCFCNV1*nCFRCONV) / nCTCNV1;
END IF;

WHEN cfF41002.TRUM = sINToUOM THEN
IF cfF41002.FUM = cfF41002.TUM THEN
IF cfF41002.FUM = cfF41002.IMUOM1 AND cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFRCONV/nCTRCONV;
nProbabilities := 255;
ELSE
nToUnits := nCFCNV1/nCTCNV1;
nProbabilities := 256;
END IF;
ELSE
IF sINFromUOM = cfF41002.IMUOM1 THEN
IF cfF41002.FRUM = sINFromUOM THEN
nToUnits := (1/(nCTCNV1*nCTRCONV));
nProbabilities := 257;
ELSE
nToUnits := (1/nCTRCONV);
nProbabilities := 258;
END IF;

ELSE
nToUnits := (nCFCNV1*nCFRCONV)/(nCTCNV1*nCTRCONV);
nProbabilities := 259;
END IF;
END IF;
fExit := 'Y';
END CASE;

WHEN cfF41002.TUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTCNV1/nCFCNV1;
nProbabilities := 351;
WHEN cfF41002.FRUM = sINToUOM THEN
IF cfF41002.TUM = cfF41002.IMUOM1 THEN
nToUnits := nCFCONV/nCFCNV1;
nProbabilities := 352;
ELSE
nProbabilities := 353;
END IF;
WHEN cfF41002.TRUM = sINToUOM THEN
nToUnits := nCTCONV;
nProbabilities := 354;
END CASE;

WHEN cfF41002.TRUM = sINFromUOM THEN
CASE
WHEN cfF41002.FUM = sINToUOM THEN
nToUnits := nCTRCONV/nCFCNV1;
nProbabilities := 451;
WHEN cfF41002.FRUM = sINToUOM THEN
nToUnits := ((1/nCFRCONV)/nCFCNV1)*nCTRCONV;
nProbabilities := 452;
WHEN cfF41002.TUM = sINToUOM THEN
nProbabilities := 453;
nToUnits := nCTRCONV;
END CASE;

END CASE;

EXIT WHEN fExit = 'Y';

END LOOP;
END IF;
IF sProb = 'Y' THEN
RETURN nProbabilities;
ELSE
RETURN  nToUnits;
END IF;

END FN_GET_CONVERSION;
--*************************************************************************************************************
-- End PROCDURE
--*************************************************************************************************************
/
Posted by at 5 comments:

Friday 25 November 2016

ETL Process - Load Production data into BI schema

Currently we are in the process of implementation of open source BI product [SPAGO]. In which I was involed in extracting data from out production environment to BI environment. During this process I have developed following stored procedure which insert data from production schema to BI schema. This is totally dynamic stored procedure. 

Process flow
When transferring the data from JD Edwards production environment system is converted into a sequential data transfer to SpagoBI environment using oracle stored procedure [Name: SP_ETL_BI_DATA with two parameters as table name & parallel operation number]


Steps followed:

1.  Identified relevant base tables from production environment. Following table [F99BI001] is created  in which all required list of tables has been entered.
Column Name
Data Type
Description
TCOBNM
VARCHAR2(12 BYTE)
Object Name
TCDAFT
NUMBER
Number of Days from Today
TCENVSRC
VARCHAR2(10 BYTE)
Source Environment
TCGDCA
VARCHAR2(20 BYTE)
Date Column like UPMJ
TCCECB
VARCHAR2(10 BYTE)
Current Environment
TCRSTDN
VARCHAR2(20 BYTE)
Target Database Name
TCRSDN
VARCHAR2(20 BYTE)
SourceDatabase Name
TCIDXN
VARCHAR2(200 BYTE)
Index expression for merge
TCINSERT
CLOB
Insert Statement as per Merge Syntax
TCPOPR
NUMBER
Parallel Operations
TCUPMJ
DATE
Updated Date
TCWDUR
NUMBER
Time Taken in Seconds
TCPWPRCD
NUMBER
Rows
TCTDA017
VARCHAR2(200 BYTE)
Primary Key
TCRDA082
VARCHAR2(4000 BYTE)
All Column
TCUPDATE
CLOB
Update Statement as per MERGE Syntax

3.     Analyzing the table structure – At present all columns from source table has been taken.

4.    Oracle [impdp] utility has been used to create & load initial data.

5.  Following stored procedure [SP_ETL_COLUMN] created which is fetching required column, primary key for the table entered in F99BI001. Fetching is done using oracle data dictionary view.

CREATE OR REPLACE Procedure SP_ETL_COLUMN(sTableName IN VARCHAR2)
IS

sPrimaryONSQL VARCHAR2(4000);
sPrimaryColumn VARCHAR2(4000);

sUpdate1SQL VARCHAR2(4000);
sUpdate2SQL VARCHAR2(4000);

sInsert1SQL VARCHAR2(4000);
sInsert2SQL VARCHAR2(4000);

sValue1SQL VARCHAR2(4000);
sValue2SQL VARCHAR2(4000);

sTotalColumn VARCHAR2(4000);

bStatus VARCHAR2(1);


CURSOR ListOfTables
IS
SELECT TCOBNM FROM F99BI001 WHERE TCOBNM LIKE sTableName ORDER BY TCOBNM;

--------------------------------------------------------------------------------------------------------------------------------
-- Main Processing
--------------------------------------------------------------------------------------------------------------------------------
BEGIN
--------------------------------------------------------------------------------------------------------------------------------


FOR cTableList IN ListOfTables
LOOP

sInsert1SQL := 'INSERT /*+ APPEND */ ( ';
sInsert2SQL := '';
sValue1SQL := ' VALUES ( ';
sValue2SQL := '';

sTotalColumn := '';
sPrimaryONSQL   := '';
sPrimaryColumn := '';

sUpdate1SQL := 'UPDATE SET ';
sUpdate2SQL := '';

-- Following line will initialize columns in F99BI001
UPDATE F99BI001 SET TCIDXN = '', TCUPDATE = '', TCINSERT = '', TCTDA017='', TCRDA082 = '' WHERE TCOBNM = cTableList.TCOBNM;
COMMIT;


-- Following lines will fetch available column information from Oracle data dictionary
FOR cColumnList IN (SELECT COLUMN_NAME FROM all_tab_columns where table_name = cTableList.TCOBNM AND OWNER='target Schema' ORDER BY COLUMN_ID)
LOOP



-- Following lines will fetch primary key information from Oracle data dictionary
bStatus := 'N';
FOR cPrimaryCol IN 
(SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner AND TRIM(COLS.TABLE_NAME)= TRIM(cTableList.TCOBNM)
AND TRIM(COLS.COLUMN_NAME)= trim(cColumnList.column_name) AND CONS.OWNER=''target schema'')
LOOP
sPrimaryONSQL := sPrimaryONSQL || 'B.' || TRIM(cColumnList.COLUMN_NAME) || '=' || 'E.' || TRIM(cColumnList.COLUMN_NAME) || ' AND ';
sPrimaryColumn := sPrimaryColumn || TRIM(cColumnList.COLUMN_NAME) || ', ';

bStatus := 'Y';
END LOOP;

IF bStatus = 'N'  THEN
IF LENGTH(sUpdate1SQL) < 3885 THEN
sUpdate1SQL := sUpdate1SQL || 'B.' || TRIM(cColumnList.COLUMN_NAME) || '=' || 'E.' || TRIM(cColumnList.COLUMN_NAME) || ', ';
ELSE
sUpdate2SQL := sUpdate2SQL || 'B.' || TRIM(cColumnList.COLUMN_NAME) || '=' || 'E.' || TRIM(cColumnList.COLUMN_NAME) || ', ';
END IF;

END IF;

IF LENGTH(sInsert1SQL) < 3885 THEN
sInsert1SQL := sInsert1SQL || 'B.' || TRIM(cColumnList.COLUMN_NAME) || ', ';
ELSE
sInsert2SQL := sInsert2SQL || 'B.' || TRIM(cColumnList.COLUMN_NAME) || ', ';
END IF;

IF LENGTH(sValue1SQL) < 3885 THEN
sValue1SQL := sValue1SQL || 'E.' || TRIM(cColumnList.COLUMN_NAME) || ', ';
ELSE
sValue2SQL := sValue2SQL || 'E.' || TRIM(cColumnList.COLUMN_NAME) || ', ';
END IF;

sTotalColumn := sTotalColumn || TRIM(cColumnList.COLUMN_NAME) || ', ';


END LOOP;

sPrimaryColumn := SUBSTR(sPrimaryColumn, 1, LENGTH(sPrimaryColumn) -2) ;
sPrimaryONSQL := SUBSTR(sPrimaryONSQL, 1, LENGTH(sPrimaryONSQL) -4) ;

IF LENGTH(sUpdate1SQL) < 3885 THEN
sUpdate1SQL := SUBSTR(sUpdate1SQL, 1, LENGTH(sUpdate1SQL) -2) ;
ELSE
sUpdate2SQL := SUBSTR(sUpdate2SQL, 1, LENGTH(sUpdate2SQL) -2) ;
END IF;


IF LENGTH(sInsert1SQL) < 3885 THEN
sInsert1SQL := SUBSTR(sInsert1SQL, 1, LENGTH(sInsert1SQL) -2) || ')';
ELSE
sInsert2SQL := SUBSTR(sInsert2SQL, 1, LENGTH(sInsert2SQL) -2) || ')';
END IF;

IF LENGTH(sValue1SQL) < 3885 THEN
sValue1SQL := SUBSTR(sValue1SQL, 1, LENGTH(sValue1SQL) -2) || ')';
ELSE
sValue2SQL := SUBSTR(sValue2SQL, 1, LENGTH(sValue2SQL) -2) || ')';
END IF;
sTotalColumn := SUBSTR(sTotalColumn, 1, LENGTH(sTotalColumn) -2);

UPDATE F99BI001 SET TCIDXN = sPrimaryONSQL, TCRDA082 = sTotalColumn, TCTDA017 = sPrimaryColumn, 
TCUPDATE = TO_CLOB(sUpdate1SQL) || TO_CLOB(sUpdate2SQL),
TCINSERT = TO_CLOB(sInsert1SQL) || TO_CLOB(sInsert2SQL) || ' ' || TO_CLOB(sValue1SQL) || TO_CLOB(sValue2SQL)
WHERE TCOBNM = cTableList.TCOBNM;
COMMIT;


END LOOP;


--------------------------------------------------------------------------------------------------------------------------------
-- Error Handling (If Any)
--------------------------------------------------------------------------------------------------------------------------------
-- EXCEPTION


END SP_ETL_COLUMN;
--*************************************************************************************************************
-- End PROCDURE
--*************************************************************************************************************
/

6.  Created stored procedure [SP_ETL_BI_DATA] which is used to transfer production data to SpagoBI environment. Method used to transfer data is ‘MERGE INTO’ statement. Only missing or change data is select from source [production] environment & then depends on the criteria data will either insert, delete or update will happen in target [SpagoBI] environment.

CREATE OR REPLACE Procedure SP_ETL_BI_DATA(sTableName IN VARCHAR2, nParallel IN INTEGER)
IS
sSQL Long;
sWhere Long;
iFromDate  NUMBER;
iThruDate NUMBER;
NumberOfRows  NUMBER;

time_before  BINARY_INTEGER;
time_after  BINARY_INTEGER;
time_taken  BINARY_INTEGER;
v_user VARCHAR2(20);

-- Cursor for list of tables from F99BI001
CURSOR ListOfTables 
IS
SELECT * FROM F99BI001
WHERE TCOBNM LIKE sTableName AND TCPOPR = nParallel;

CURSOR UserName IS SELECT USER FROM DUAL;

--------------------------------------------------------------------------------------------------------------------------------
-- Main Processing
--------------------------------------------------------------------------------------------------------------------------------
BEGIN
--------------------------------------------------------------------------------------------------------------------------------

-- Structure of F99BI001 which is base table. This is parameterised table by which data is copied from source to target environment
-- Following are the columns of table F99BI001

-- TCOBNM  - Object Name [Name of the table to be copied]
-- TCDAFT  - Number of Days from Today [Number of days to be copied from SYSDATE - Today]
-- TCENVSRC - Source Environment [Source Environment - It is either PRODDTA/PRODCTL]
-- TCGDCA - Date Column like UPMJ [Date field name for filter condition. Based on this date condition where clause will be built]
-- TCCECB - Current Environment [Environment when data will be copied. 
-- TCRSTDN - Target Database Name [Target Instance Name. ]
-- TCRSDN - SourceDatabase Name  [Source Instance Name. ]
-- TCIDXN - Index expression for merge [Where clause expression]
-- TCUPDATE - Update Statement as per Merge into Syntax 
-- TCINSERT - Insert Statement as per Merge Syntax
-- TCPOPR - Parallel Operations - Number of Parallel operation which is going to execute thru schedular
-- TCUPMJ - Updated Date - After completion of copy job date will be updated here
-- TCWDUR - Time Taken in Seconds - After completion of copy time taken will be updated here in seconds.
-- TCPWPRCD - Rows - After completion of copy # of rows processed will be updated here
-- TCTDA017 - Primary Key - This field is required to delete records which is present in target environment and deleted from source environment
-- TCRDA082 - Total Columns in the table. SELECT MINUS quert has been built using this column
-- TCHSIFT - Filter Condition for retriving data


-- Following lines will fetch logged in User Name
OPEN UserName ;
FETCH UserName INTO v_user;
CLOSE UserName;

FOR cTableList IN ListOfTables 
LOOP

-- Building of MERGE statement
sSQL := 'MERGE INTO ' || TRIM(cTableList.TCCECB) || '.' || cTableList.TCOBNM;

IF LENGTH(TRIM(cTableList.TCRSTDN)) > 0 THEN
sSQL := sSQL || '@' || TRIM(cTableList.TCRSTDN);
END IF;


sSQL := sSQL  || ' b USING (SELECT ' || TRIM(cTableList.TCRDA082) || ' FROM ' ||  TRIM(cTableList.TCENVSRC) || '.' || cTableList.TCOBNM ;

IF LENGTH(TRIM(cTableList.TCRSDN)) > 0 THEN
sSQL := sSQL || '@' || TRIM(cTableList.TCRSDN);
END IF;

IF cTableList.TCDAFT > 0 AND cTableList.TCGDCA IS NOT NULL THEN
iFromDate := FDATETOJULIAN(SYSDATE) - cTableList.TCDAFT;
iThruDate := FDATETOJULIAN(SYSDATE);
sSQL := sSQL || ' WHERE ' || cTableList.TCGDCA || ' BETWEEN ' || iFromDate || ' AND ' || iThruDate  ;
END IF;
IF cTableList.TCHSIFT IS NOT NULL THEN
IF cTableList.TCDAFT > 0 AND cTableList.TCGDCA IS NOT NULL THEN
sSQL := sSQL || ' AND ';
ELSE
sSQL := sSQL || ' WHERE ';
END IF;
sSQL := sSQL || cTableList.TCHSIFT;
END IF;



sSQL := sSQL  || ' MINUS SELECT ' || TRIM(cTableList.TCRDA082) || ' FROM ' ||  TRIM(cTableList.TCCECB) || '.' || cTableList.TCOBNM ;

IF LENGTH(TRIM(cTableList.TCRSTDN)) > 0 THEN
sSQL := sSQL || '@' || TRIM(cTableList.TCRSTDN);
END IF;


IF cTableList.TCDAFT > 0 AND cTableList.TCGDCA IS NOT NULL THEN
iFromDate := FDATETOJULIAN(SYSDATE) - cTableList.TCDAFT;
iThruDate := FDATETOJULIAN(SYSDATE);
sSQL := sSQL || ' WHERE ' || cTableList.TCGDCA || ' BETWEEN ' || iFromDate || ' AND ' || iThruDate  ;
END IF;

IF cTableList.TCHSIFT IS NOT NULL THEN
IF cTableList.TCDAFT > 0 AND cTableList.TCGDCA IS NOT NULL THEN
sSQL := sSQL || ' AND ';
ELSE
sSQL := sSQL || ' WHERE ';
END IF;
sSQL := sSQL || cTableList.TCHSIFT;
END IF;


sSQL := sSQL || ') e ON (' || TRIM(cTableList.TCIDXN) || ') WHEN MATCHED THEN ' || TRIM(cTableList.TCUPDATE);

sSQL := sSQL || ' WHEN NOT MATCHED THEN ' || TRIM(cTableList.TCINSERT);

-- MERGE STATMENT will be executed here
EXECUTE IMMEDIATE sSQL;
COMMIT;


-- Building of DELETE MINUS Statement
sSQL := 'DELETE FROM ' || TRIM(cTableList.TCCECB) || '.' || cTableList.TCOBNM;

IF LENGTH(TRIM(cTableList.TCRSTDN)) > 0 THEN
sSQL := sSQL || '@' || TRIM(cTableList.TCRSTDN);
END IF;

sSQL := sSQL || ' WHERE (' || TRIM(cTableList.TCTDA017) || ') IN ( SELECT ' || TRIM(cTableList.TCTDA017) || ' FROM ' ;
sSQL := sSQL || TRIM(cTableList.TCCECB) || '.' || cTableList.TCOBNM;

IF LENGTH(TRIM(cTableList.TCRSTDN)) > 0 THEN
sSQL := sSQL || '@' || TRIM(cTableList.TCRSTDN);
END IF;

sSQL := sSQL || ' MINUS SELECT ' ||  TRIM(cTableList.TCTDA017) || ' FROM ' ;

sSQL := sSQL || TRIM(cTableList.TCENVSRC) || '.' || cTableList.TCOBNM;

IF LENGTH(TRIM(cTableList.TCRSTDN)) > 0 THEN
sSQL := sSQL || '@' || TRIM(cTableList.TCRSDN);
END IF;
sSQL := sSQL || ')';

-- DELETE MINUS statement will execute here
EXECUTE IMMEDIATE sSQL;
COMMIT;

EXECUTE IMMEDIATE sSQL;
COMMIT;


END LOOP;

--------------------------------------------------------------------------------------------------------------------------------
-- Error Handling (If Any)
--------------------------------------------------------------------------------------------------------------------------------
EXCEPTION


END SP_ETL_BI_DATA;
--*************************************************************************************************************
-- End PROCDURE
--*************************************************************************************************************
/ 

Posted by at 1 comment:
Subscribe to: Posts (Atom)