Missing functions in eul4 Discoverer

If you are new here, you may want to subscribe to the following feeds Business Intelligence ,InfoCaptor

Missing functions in eul4 Discoverer - cloned from eul5

Written by Nilesh Jethwa

As part of the testing process for the upcoming EUL documentation feature in InfoCaptor, one of the user’s (Lance Botha - an active member of Discoverer forum ) was having trouble using the new discoverer dashboard. He was running against the EUL4 version whereas I had the opportunity to test it on only eul5 version. With some quick testing I came up with a list of functions that were newly added in EUL5 version and are missing in eul4. Below is a list of all the cloned functions for EUL4. The below functions should be created as the eul owner and should be executed after the eul4.sql and eul4_apps.sql scripts have been run. I will post detailed steps once the new release is out.


create or replace FUNCTION EUL4_GET_HIER_EXPID(HINAME IN VARCHAR2)
RETURN NUMBER IS
EXPID NUMBER(10);
–
–
HIID NUMBER(10);
HILHNID NUMBER(10);
–
–
–
CURSOR GET_HIID IS
SELECT HI_ID
FROM EUL4_HIERARCHIES
WHERE HI_NAME=HINAME;
–
–
CURSOR GET_HILHNID IS
SELECT IHS_HN_ID_CHILD
FROM EUL4_HI_SEGMENTS
WHERE IHS_HI_ID=HIID
AND IHS_HN_ID_CHILD NOT IN
(SELECT IHS_HN_ID_PARENT
FROM EUL4_HI_SEGMENTS
WHERE IHS_HI_ID=HIID);
–
–
CURSOR GET_EXPID IS
SELECT HIL_EXP_ID
FROM EUL4_IG_EXP_LINKS
WHERE HIL_HN_ID=HILHNID;
–
–
BEGIN
–
IF HINAME IS NULL THEN
EXPID:=NULL;
ELSE
–
OPEN GET_HIID;
FETCH GET_HIID INTO HIID;
IF GET_HIID%NOTFOUND THEN
EXPID:=-1;
RETURN EXPID;
END IF;
CLOSE GET_HIID;
–
OPEN GET_HILHNID;
FETCH GET_HILHNID INTO HILHNID;
IF GET_HILHNID%NOTFOUND THEN
EXPID:=-2;
RETURN EXPID;
END IF;
CLOSE GET_HILHNID;
–
OPEN GET_EXPID;
FETCH GET_EXPID INTO EXPID;
IF GET_EXPID%NOTFOUND THEN
EXPID:=-3;
END IF;
CLOSE GET_EXPID;
–
END IF;
RETURN EXPID;
END EUL4_GET_HIER_EXPID;
/

create or replace FUNCTION EUL4_GET_DATEHIER_TMPLT_NAME(IBHDBHID IN NUMBER)
RETURN VARCHAR2 IS
DATE_HIER_TMPLTE_NAME VARCHAR2(200);
–
–
–
–
–
CURSOR GET_DATEHIER_NAME IS
SELECT HI_NAME
FROM EUL4_HIERARCHIES
WHERE HI_ID=IBHDBHID
AND HI_TYPE=’DBH’;
–
–
–
–
BEGIN
–
–
IF IBHDBHID IS NULL THEN
DATE_HIER_TMPLTE_NAME:=’ERROR - NOT ASSOCIATED TO A DATE HIERARCHY TEMPLATE’;
ELSE
–
OPEN GET_DATEHIER_NAME;
FETCH GET_DATEHIER_NAME INTO DATE_HIER_TMPLTE_NAME;
IF GET_DATEHIER_NAME%NOTFOUND THEN
DATE_HIER_TMPLTE_NAME:=’Error - Unable to find Date Hierarchy Template Name’;
END IF;
CLOSE GET_DATEHIER_NAME;
END IF;
RETURN DATE_HIER_TMPLTE_NAME;
END EUL4_GET_DATEHIER_TMPLT_NAME;
/

create or replace FUNCTION EUL4_GET_FOLDERNAME(EXPID IN NUMBER)
RETURN VARCHAR2 IS
FLRNAME VARCHAR2(200);
–
–
–
–
–
CURSOR GET_FLDRNAM IS
SELECT OBJ_NAME
FROM EUL4_OBJS,EUL4_EXPRESSIONS
WHERE EUL4_OBJS.OBJ_ID=EUL4_EXPRESSIONS.IT_OBJ_ID
AND EUL4_EXPRESSIONS.EXP_ID=EXPID;
–
–
–
–
BEGIN
–
IF EXPID IS NULL THEN
FLRNAME:=NULL;
ELSE
OPEN GET_FLDRNAM;
FETCH GET_FLDRNAM INTO FLRNAME;
IF GET_FLDRNAM%NOTFOUND THEN
FLRNAME:=’Error - Unable to find Folder Name’;
END IF;
CLOSE GET_FLDRNAM;
END IF;
RETURN FLRNAME;
END EUL4_GET_FOLDERNAME;
/

create or replace FUNCTION EUL4_GET_HIERNODE_EXPID(HNID IN NUMBER)
RETURN NUMBER IS
EXPID NUMBER(10);
–
–
–
–
–
CURSOR GET_EXP_ID IS
SELECT HIL_EXP_ID
FROM EUL4_IG_EXP_LINKS
WHERE HIL_HN_ID=HNID
AND HIL_HN_ID IS NOT NULL;
–
–
–
–
BEGIN
–
OPEN GET_EXP_ID;
FETCH GET_EXP_ID INTO EXPID;
IF GET_EXP_ID%NOTFOUND THEN
EXPID:=NULL;
END IF;
CLOSE GET_EXP_ID;
RETURN EXPID;
END EUL4_GET_HIERNODE_EXPID;
/

create or replace FUNCTION EUL4_GET_AUTOGEN_ITEM_NAME(EXPID IN NUMBER)
RETURN VARCHAR2 IS
DEP_ITEM VARCHAR2(4000);
–
–
CIDEXP        NUMBER;
EXPNAME        VARCHAR2(200);
–
–
– This cursor finds out if the item is a complex item (ie. dependant on another item)
– It retuns not rows if it has no dependancies (ie. simple folder)
–
CURSOR EXPOBJ IS
SELECT CID_EXP_ID,EXP_NAME
FROM EUL4_EXP_DEPS, EUL4_EXPRESSIONS
WHERE EUL4_EXP_DEPS.CD_EXP_ID = EXPID
AND CID_EXP_ID IS NOT NULL
AND EUL4_EXP_DEPS.CD_EXP_ID = EUL4_EXPRESSIONS.EXP_ID;
–
–
–
CURSOR GET_EXP_NAME IS
SELECT EXP_NAME
FROM EUL4_EXPRESSIONS
WHERE EXP_ID=EXPID;
–
–
–
–
BEGIN
–
IF EXPID IS NULL THEN
DEP_ITEM:=NULL;
ELSE
–
OPEN EXPOBJ;
FETCH EXPOBJ INTO CIDEXP,EXPNAME;
IF EXPOBJ%NOTFOUND THEN
OPEN GET_EXP_NAME;
FETCH GET_EXP_NAME INTO EXPNAME;
CLOSE GET_EXP_NAME;
END IF;
CLOSE EXPOBJ;
IF SUBSTR(EXPNAME,1,19) <> ‘AUTO_GENERATED_NAME’ THEN
DEP_ITEM:=EXPNAME;
RETURN DEP_ITEM;
ELSE
DEP_ITEM:=EUL4_GET_AUTOGEN_ITEM_NAME(CIDEXP);
END IF;
END IF;
RETURN DEP_ITEM;
END EUL4_GET_AUTOGEN_ITEM_NAME;
/

create or replace FUNCTION EUL4_GET_JOIN(EXPID IN NUMBER,JOINAME IN VARCHAR2:=’Y')
RETURN VARCHAR2 IS
JOIN VARCHAR2(4000);
–
– If JOINAME is ‘Y’ then this returns the Autogenerated Join Name if it is ‘N’ or some other value it returns the the actual Join Details
–
–
JOIN_FORMULA    VARCHAR2(500);
JOIN_BIT    VARCHAR2(100);
JOIN_TYPE    VARCHAR2(20);
STARTPT     NUMBER :=1;
ENDPT        NUMBER :=1;
FINISHPT    NUMBER:=1;
EXPIDMSTR    NUMBER(10);
EXPIDDTL    NUMBER(10);
MASTERFLDR    VARCHAR2(4000);
DETAILFLDR    VARCHAR2(4000);
OPERATOR    VARCHAR2(10);
JOINMSTR    VARCHAR2(4000);
JOINDTL        VARCHAR2(4000);
CNTR        NUMBER :=0;
–
–
–
CURSOR GET_FORMULA IS
SELECT EXP_FORMULA1
FROM EUL4_EXPRESSIONS
WHERE EXP_TYPE=’JP’
AND EXP_ID=EXPID;
–
–
–
–
BEGIN
–
IF EXPID IS NULL THEN
JOIN:=’Name is just Too long to display’;
ELSE
OPEN GET_FORMULA;
FETCH GET_FORMULA INTO JOIN_FORMULA;
IF GET_FORMULA%NOTFOUND THEN
JOIN:=’Error - Unable to find Join’;
END IF;
JOIN_BIT:=SUBSTR(JOIN_FORMULA,3,4);
IF JOIN_BIT=’,98]’ THEN
JOIN_TYPE:=’MULTI’;
ELSE
JOIN_TYPE:=’SINGLE’;
END IF;
IF JOIN_TYPE=’MULTI’ THEN
FINISHPT:=LENGTH(JOIN_FORMULA);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,3)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,4)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
MASTERFLDR:=EUL4_GET_FOLDERNAME(EXPIDMSTR);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,5)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,6)-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
DETAILFLDR:=EUL4_GET_FOLDERNAME(EXPIDDTL);
IF upper(JOINAME)=’Y’ THEN
JOINMSTR:=MASTERFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:=DETAILFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
CNTR:=11;
WHILE INSTR(JOIN_FORMULA,CHR(44),1,CNTR)<>0 LOOP
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-3)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-2)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-1)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR)-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
JOINMSTR:= JOINMSTR||’,'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:= JOINDTL||’,'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
CNTR:=CNTR+5;
END LOOP;
CNTR:=CNTR-1;
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-2)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-1)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR)+1;
ENDPT:=FINISHPT-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
JOINMSTR:= JOINMSTR||’,'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR)||’ -> ‘;
JOINDTL:= JOINDTL||’,'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
JOIN:=JOINMSTR||JOINDTL;
ELSE
JOINMSTR:=MASTERFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:=DETAILFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
IF SUBSTR(JOIN_FORMULA,11,2) = ‘81′ THEN
OPERATOR:= ‘ = ‘;
ELSIF SUBSTR(JOIN_FORMULA,11,2) = ‘82′ THEN
OPERATOR:= ‘ <> ‘;
ELSIF SUBSTR(JOIN_FORMULA,11,2) = ‘83′ THEN
OPERATOR:= ‘ > ‘;
ELSIF SUBSTR(JOIN_FORMULA,11,2) = ‘84′ THEN
OPERATOR:= ‘ < ';
ELSIF SUBSTR(JOIN_FORMULA,11,2) = '85' THEN
OPERATOR:= ' <= ';
ELSIF SUBSTR(JOIN_FORMULA,11,2) = '86' THEN
OPERATOR:= ' >= ‘;
ELSE
OPERATOR:= ‘ ? ‘;
END IF;
JOIN:=JOINMSTR||OPERATOR||JOINDTL||CHR(10);
CNTR:=11;
WHILE INSTR(JOIN_FORMULA,CHR(44),1,CNTR)<>0 LOOP
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-3)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-2)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-1)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR)-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
JOINMSTR:= MASTERFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:= DETAILFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-4)+1;
IF SUBSTR(JOIN_FORMULA,STARTPT,2) = ‘81′ THEN
OPERATOR:= ‘ = ‘;
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = ‘82′ THEN
OPERATOR:= ‘ <> ‘;
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = ‘83′ THEN
OPERATOR:= ‘ > ‘;
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = ‘84′ THEN
OPERATOR:= ‘ < ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '85' THEN
OPERATOR:= ' <= ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '86' THEN
OPERATOR:= ' >= ‘;
ELSE
OPERATOR:= ‘ ? ‘;
END IF;
CNTR:=CNTR+5;
JOIN:=JOIN||JOINMSTR||OPERATOR||JOINDTL||CHR(10);
END LOOP;
CNTR:=CNTR-1;
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-2)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-1)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR)+1;
ENDPT:=FINISHPT-2;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
JOINMSTR:=MASTERFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:=DETAILFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,CNTR-3)+1;
IF SUBSTR(JOIN_FORMULA,STARTPT,2) = ‘81′ THEN
OPERATOR:= ‘ = ‘;
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = ‘82′ THEN
OPERATOR:= ‘ <> ‘;
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = ‘83′ THEN
OPERATOR:= ‘ > ‘;
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = ‘84′ THEN
OPERATOR:= ‘ < ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '85' THEN
OPERATOR:= ' <= ';
ELSIF SUBSTR(JOIN_FORMULA,STARTPT,2) = '86' THEN
OPERATOR:= ' >= ‘;
ELSE
OPERATOR:= ‘ ? ‘;
END IF;
JOIN:=JOIN||JOINMSTR||OPERATOR||JOINDTL;
END IF;
ELSE
FINISHPT:=LENGTH(JOIN_FORMULA);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,2)+1;
ENDPT:=INSTR(JOIN_FORMULA,CHR(44),1,3)-1;
EXPIDMSTR:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
MASTERFLDR:=EUL4_GET_FOLDERNAME(EXPIDMSTR);
STARTPT:=INSTR(JOIN_FORMULA,CHR(44),1,4)+1;
ENDPT:=FINISHPT-1;
EXPIDDTL:=TO_NUMBER(SUBSTR(JOIN_FORMULA,STARTPT,(ENDPT-STARTPT)));
DETAILFLDR:=EUL4_GET_FOLDERNAME(EXPIDDTL);
IF upper(JOINAME)=’Y’ THEN
JOINMSTR:=MASTERFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR)||’ -> ‘;
JOINDTL:=DETAILFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
JOIN:=JOINMSTR||JOINDTL;
ELSE
IF SUBSTR(JOIN_FORMULA,4,2) = ‘81′ THEN
OPERATOR:= ‘ = ‘;
ELSIF SUBSTR(JOIN_FORMULA,4,2) = ‘82′ THEN
OPERATOR:= ‘ <> ‘;
ELSIF SUBSTR(JOIN_FORMULA,4,2) = ‘83′ THEN
OPERATOR:= ‘ > ‘;
ELSIF SUBSTR(JOIN_FORMULA,4,2) = ‘84′ THEN
OPERATOR:= ‘ < ';
ELSIF SUBSTR(JOIN_FORMULA,4,2) = '85' THEN
OPERATOR:= ' <= ';
ELSIF SUBSTR(JOIN_FORMULA,4,2) = '86' THEN
OPERATOR:= ' >= ‘;
ELSE
OPERATOR:= ‘ ? ‘;
END IF;
JOINMSTR:=MASTERFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR);
JOINDTL:=DETAILFLDR||’.'||EUL4_GET_AUTOGEN_ITEM_NAME(EXPIDDTL);
JOIN:=JOINMSTR||OPERATOR||JOINDTL;
END IF;
END IF;
CLOSE GET_FORMULA;
END IF;
RETURN JOIN;
END EUL4_GET_JOIN;
/