Missing functions in eul4 Discoverer – cloned from eul5

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; /