srjava.util.Vectorٗ}[;IcapacityIncrementI elementCount[ elementDatat[Ljava/lang/Object;xpur[Ljava.lang.Object;Xs)lxp(srInfoPurple.Qlet ඳcTL colDetailstLjava/util/Vector;L qletLocationtLjava/awt/Point;LqletSizetLjava/awt/Dimension;LqpValuesq~LqueryStrtLjava/lang/String;LsqlWithFiltersq~ xppppsq~(uq~(tDisco Documentor InstallationtSelectt100tManualt300t 1-JAN-1990tYsrjava.awt.Color3uFfalphaIvalueLcstLjava/awt/color/ColorSpace;[ frgbvaluet[F[fvalueq~xppppsq~pppsq~pppt1sr java.awt.Fontš5VsIfontSerializedDataVersionF pointSizeIsizeIstyleLfRequestedAttributestLjava/util/Hashtable;Lnameq~ xpA ptArialxsq~@@@pppt15q~sq~pppsq~A@ pq~xq~t dd-MMM-yyyyt #########tq0q~sq~A pq~xq~tappst No_PatterntVLETsq~pppsq~pppt UUSCRIPT_TESTsrInfoPurple.DashboardSettings ඳc۬I currPletIdxI currQletIdxxp#%tAuthor,Description etctNq~tDEFAULTq~q~0q~0srjavax.swing.JTextArea^j$oI columnWidthIcolumnsI rowHeightIrowsZwordZwrapxrjavax.swing.text.JTextComponentQDawwZcheckedInputOverrideZ dragEnabledZeditableCfocusAcceleratorZneedToSendKeyTypedEventL caretColortLjava/awt/Color;LcomposedTextAttributet%Ljavax/swing/text/SimpleAttributeSet;LcomposedTextCarett3Ljavax/swing/text/JTextComponent$ComposedTextCaret;LcomposedTextContentq~ LcomposedTextEndtLjavax/swing/text/Position;LcomposedTextStartq~7LdisabledTextColorq~4Leditort!Ljavax/swing/text/JTextComponent;LlatestCommittedTextEndq~7LlatestCommittedTextStartq~7LmargintLjava/awt/Insets;LmodeltLjavax/swing/text/Document;LnavigationFiltert#Ljavax/swing/text/NavigationFilter;LselectedTextColorq~4LselectionColorq~4xrjavax.swing.JComponentmFB*75!ZaaTextF alignmentXF alignmentYZ autoscrollsIflagsZisAlignmentXSetZisAlignmentYSetZverifyInputWhenFocusTargetLaccessibleContextt'Ljavax/accessibility/AccessibleContext;L actionMaptLjavax/swing/ActionMap;LancestorInputMaptLjavax/swing/InputMap;LbordertLjavax/swing/border/Border;L focusInputMapq~?L inputVerifiertLjavax/swing/InputVerifier;L listenerListt%Ljavax/swing/event/EventListenerList;L popupMenutLjavax/swing/JPopupMenu;LvetoableChangeSupportt"Ljava/beans/VetoableChangeSupport;LwindowInputMaptLjavax/swing/ComponentInputMap;xrjava.awt.Container@s 'IcontainerSerializedDataVersionZfocusCycleRootZfocusTraversalPolicyProviderI ncomponents[ componentt[Ljava/awt/Component;L dispatchert Ljava/awt/LightweightDispatcher;L layoutMgrtLjava/awt/LayoutManager;LmaxSizeq~xrjava.awt.ComponentY<#IboundsOpIcomponentSerializedDataVersionZenabledJ eventMaskZfocusTraversalKeysEnabledZ focusableIheightZ ignoreRepaintIisFocusTraversableOverriddenZisPackedZ maxSizeSetZ minSizeSetZnameExplicitlySetZ newEventsOnlyZ prefSizeSetZvalidZvisibleIwidthIxIyLaccessibleContextq~=L backgroundq~4L changeSupportt"Ljava/beans/PropertyChangeSupport;LcursortLjava/awt/Cursor;L dropTargettLjava/awt/dnd/DropTarget;[focusTraversalKeyst[Ljava/util/Set;LfonttLjava/awt/Font;L foregroundq~4LlocaletLjava/util/Locale;LmaxSizeq~LminSizeq~Lnameq~ LpeerFontq~OLpopupsq~LprefSizeq~xp8psr javax.swing.plaf.ColorUIResourcekSxq~pppsr java.beans.PropertyChangeSupportXdWH`I*propertyChangeSupportSerializedDataVersionLchildrenq~LsourcetLjava/lang/Object;xppq~Qpxsrjava.awt.CursorojwItypeLnameq~ xpt Text Cursorpur[Ljava.util.Set;>0gjהxpsr%java.util.Collections$UnmodifiableSetяUxr,java.util.Collections$UnmodifiableCollectionB^LctLjava/util/Collection;xpsrjava.util.HashSetD4xpw ?@srjavax.swing.KeyStrokeCŗuv >xrjava.awt.AWTKeyStroke"8|CkeyCharIkeyCodeI modifiersZ onKeyReleasexp xsq~\sq~`w ?@sq~b xppsrjavax.swing.plaf.FontUIResourceBć"Gxq~A0 ptTahomaxsq~Rpppsrjava.util.Locale~`0IhashcodeLcountryq~ Llanguageq~ Lvariantq~ xptUStentpppppptfocusLsr1javax.swing.text.JTextComponent$MutableCaretEventGY|IdotZ dragActiveImarkxrjavax.swing.event.CaretEvent [rVPxrjava.util.EventObjectL Nm}xpq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rtmouseLq~uq~~q~vq~~q~wq~~q~xq~~q~yq~~q~zq~~q~{q~~q~|q~~q~}psrjava.awt.ComponentOrientationEcI orientationxpppxur[Ljava.awt.Component; uxppppppxA(psrjavax.swing.ActionMapzLparentq~>xppwxppsrjavax.swing.InputMapf!ٟLparentq~?xpsr#javax.swing.plaf.InputMapUIResourceHMכxq~sq~pw7sq~b&At selection-upsq~b'tcaret-next-wordsq~b%tselection-previous-wordsq~bAq~sq~b(t caret-downsq~bTtprevious-link-actionsq~b%tcaret-previous-wordsq~btcut-to-clipboardsq~b#tcaret-end-linesq~b!Atselection-page-upsq~btcaret-upsq~bt delete-nextsq~b$t caret-beginsq~b%Atselection-backwardsq~b#t caret-endsq~btdelete-previoussq~b'tselection-next-wordsq~b%tcaret-backwardsq~bq~sq~bAtselection-forwardsq~b tactivate-link-actionsq~bHq~sq~b\tunselectsq~b t insert-breaksq~b$Atselection-begin-linesq~b't caret-forwardsq~b!tselection-page-leftsq~b(Atselection-downsq~b"t page-downsq~bAq~sq~bOttoggle-componentOrientationsq~bXq~sq~b"tselection-page-rightsq~bCtcopy-to-clipboardsq~bq~sq~b#Atselection-end-linesq~bq~sq~b$tcaret-begin-linesq~bVtpaste-from-clipboardsq~bq~sq~bAt select-allsq~b'Aq~sq~b#t selection-endsq~bq~sq~bq~sq~bTtnext-link-actionsq~bAq~sq~b t insert-tabsq~b&q~sq~b$tselection-beginsq~b"Atselection-page-downsq~bq~sq~bq~sq~b!tpage-upsq~bq~xwxwxpsr#javax.swing.event.EventListenerList6}Dxppxpppwxpppppppppppsrjavax.swing.text.PlainDocumentBιŅLaddedq~L defaultRoott3Ljavax/swing/text/AbstractDocument$AbstractElement;Lremovedq~xr!javax.swing.text.AbstractDocument^Lcontextt4Ljavax/swing/text/AbstractDocument$AttributeContext;Ldatat+Ljavax/swing/text/AbstractDocument$Content;LdocumentFiltert!Ljavax/swing/text/DocumentFilter;LdocumentPropertiestLjava/util/Dictionary;L listenerListq~Bxpsrjavax.swing.text.StyleContexto~{xI unusedSetsLstylestLjavax/swing/text/Style;xpsr(javax.swing.text.StyleContext$NamedStyle NL listenerListq~BLthis$0tLjavax/swing/text/StyleContext;xpsq~pxq~wtdefaultsq~sq~pxq~wt$javax.swing.text.StyleConstants.nameq~xxxsrjavax.swing.text.GapContent LL#xrjavax.swing.text.GapVector;#6VIg0Ig1Larrayq~Uxp ur[C&f]xp psrjava.util.Hashtable%!JF loadFactorI thresholdxp?@wsrjava.awt.font.TextAttributekx Fxr/java.text.AttributedCharacterIterator$Attributet&G\Lnameq~ xpt run_directionsrjava.lang.Boolean r՜ZvaluexpttabSizesrjava.lang.Integer⠤8Ivaluexrjava.lang.Number xpti18nsq~xsq~pxsq~uq~ ppppppppppxsr/javax.swing.text.AbstractDocument$BranchElementQ)f<I lastIndexI nchildren[childrent4[Ljavax/swing/text/AbstractDocument$AbstractElement;Lthis$0t#Ljavax/swing/text/AbstractDocument;xr1javax.swing.text.AbstractDocument$AbstractElementϲx7LparenttLjavax/swing/text/Element;Lthis$0q~xppq~wxur4[Ljavax.swing.text.AbstractDocument$AbstractElement;t9w:ѝxpsr-javax.swing.text.AbstractDocument$LeafElementjcLthis$0q~xq~q~q~wxq~wxpq~sq~uq~ ppppppppppxpppxq~0xppsrInfoPurple.Plet ඳcL pletLocationq~LpletSizeq~LppValuesq~LqueryStrq~ xpsrjava.awt.PointĊr4~&IxIyxplsrjava.awt.DimensionA׬_DIheightIwidthxp2sq~uq~(tEUL Owner AccountssrdbUtil.dbInstance ඳcLdbDriverq~ L dbPasswordq~ LdbURLq~ LdbUserq~ xpt* Oracle - oracle.jdbc.driver.OracleDrivertapps_dbatlink-obyone.cisco.com:1528:dbatappstALLtdistinct ownert'ID1't'ID2't'ID3't'ID4't'ID5'srInfoPurple.PletValue ඳcHL descriptionq~ L displayValueq~ Lid1q~ Lid2q~ Lid3q~ Lid4q~ Lid5q~ L internalValueq~ xpptAPPStID1tID2tID3tID4tID5psq~pppsq~pppsq~A@ ptArialxtappstNONEtPLETtp34tNq~EtAuthor,Description etctYt1tYsq~28pq~Ssq~Tpq~Jpxsq~Wt Text Cursorpuq~Zsq~\sq~`w ?@q~dxsq~\sq~`w ?@q~gxppq~iq~ksq~lq~nq~oq~pppppppq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~~q~Tq~~q~Uq~~q~Vq~~q~Wq~~q~Xq~~q~Yq~~q~Zpsq~ppxuq~pppppxA(psq~pwxppsq~sq~q~wxwxpsq~pxpppwxpppppppppppsq~sq~sq~sq~pxq~bwtdefaultsq~sq~pxq~bwq~q~exxxsq~uq~import InfoPurple.Global; import InfoPurple.Plet; Plet p= Global.getPletObject("p35"); if (p!=null) { p.refreshPletDisplay(true); } psq~?@wq~sq~ttabSizesq~ ti18nsq~xsq~pxsq~ uq~ sq~sq~pq~awx uq~q~ssq~q~tq~awxq~aw2xsq~q~tq~awxq~aw23xsq~q~tq~awxq~aw3Xxsq~q~tq~awxq~awXYxsq~q~tq~awxq~awYfxsq~q~tq~awxq~awfhxsq~q~tq~awxq~awhxsq~q~tq~awxq~awxpppppppppq~aq~awxq~awxq~vq~wq~xq~yq~zq~{q~|q~}pxq~tsq~uq~ sq~q~tq~awxq~awxpppppppppxpppxppppppppppppppppxt1from all_tables where table_name like 'EUL5_BAS' sq~sq~!osq~#2sq~uq~(tIs this APPS based EUL?q~)tALLtsteptvaluet'ID2't'ID3't'ID4't'ID5'sq~5pt0Non-Apps EUL. t0tID2tID3tID4tID5psq~pppsq~pppsq~A@ ptArialxtappstNONEtPLETtp35tNq~tAuthor,Description etctYt1tNsq~28pq~Ssq~Tpq~pxsq~Wt Text Cursorpuq~Zsq~\sq~`w ?@q~dxsq~\sq~`w ?@q~gxppq~iq~ksq~lq~nq~oq~pppppppq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~~q~q~~q~q~~q~q~~q~q~~q~psq~ppxuq~pppppxA(psq~pwxppsq~sq~q~wxwxpsq~pxpppwxpppppppppppsq~sq~sq~sq~pxq~wtdefaultsq~sq~pxq~wq~q~xxxsq~uq~import InfoPurple.Global; import InfoPurple.Plet; Plet p= Global.getPletObject("p35"); if (p!=null) { String value= ((PletValue)(p.getComboBox().getSelectedItem())).getValue('ID1'); Plet pbutton = Global.getPletObject("p33"); if (value.equals("0") ) { pbutton.getDisplayFrame().setEnabled(false); } else { pbutton.getDisplayFrame().setEnabled(true); } } psq~?@wq~sq~ttabSizesq~ ti18nsq~xsq~pxsq~uq~sq~sq~pq~wxuq~$q~sq~q~q~wxq~w2xsq~q~q~wxq~w23xsq~q~q~wxq~w3Xxsq~q~q~wxq~wXYxsq~q~q~wxq~wYfxsq~q~q~wxq~wfhxsq~q~q~wxq~whxsq~q~q~wxq~wxsq~q~q~wxq~wxsq~q~q~wxq~w xsq~q~q~wxq~w =xsq~q~q~wxq~w=Axsq~q~q~wxq~wAHxsq~q~q~wxq~wHLxsq~q~q~wxq~wL~xsq~q~q~wxq~w~xsq~q~q~wxq~wxppppppppppppppppppq~q~wxq~wxq~q~q~q~q~q~q~q~q~q~q~q~q~q~q~q~q~ppxq~sq~uq~sq~q~q~wxq~wxpppppppppppppppppppxpppxppppppppppppppppxt%from ( SELECT decode(COUNT(*) ,0,'Non-Apps EUL. ','Yes- Apps Based EUL ') step ,count(*) value FROM G_PARAM.EUL5_APP_PARAMS WHERE APP_TYPE='SP' AND APP_ID=1016 )sq~sq~!psq~#>sq~uq~(tInstall Apps related Objectsq~)tALLt1Step 2. Click to install Apps EUL related Objectst'ID1't'ID2't'ID3't'ID4't'ID5'psq~pppsq~pppsq~Apq~xtappstNONEtBLETtp33tNq~tAuthor,Description etctYt1q~sq~28pq~Ssq~Tpq~pxsq~Wt Text Cursorpuq~Zsq~\sq~`w ?@q~dxsq~\sq~`w ?@q~gxppq~iq~ksq~lq~nq~oq~pppppppq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~~q~q~~q~q~~q~q~~q~q~~q~q~~q~ q~~q~ psq~ppxuq~pppppxA(psq~pwxppsq~sq~q~wxwxpsq~pxpppwxpppppppppppsq~sq~sq~sq~pxq~wtdefaultsq~sq~pxq~wq~q~xxxsq~ uq~ psq~?@wq~sq~ttabSizesq~ ti18nsq~xsq~pxsq~uq~ ppppppppppxsq~pq~wxuq~sq~q~#q~wxq~wxpq~sq~uq~ ppppppppppxpppxppppppppppppppppxtcreate or replace view G_PARAM.EUL5_FND_USER as (select user_name,user_id from apps.fnd_user) --IC_NEW_SOURCE-- create or replace view G_PARAM.EUL5_FND_RESPONSIBILITY_VL as (select responsibility_name, responsibility_id,application_id from apps.fnd_responsibility_vl) --IC_NEW_SOURCE-- CREATE OR REPLACE FUNCTION G_PARAM.EUL5_GET_APPS_USERRESP(apps_id in varchar2,User_Resp in Varchar2:='U') RETURN VARCHAR2 IS APPS_USER VARCHAR2(100); resp varchar2(100); usr varchar2(100); appid varchar2(100); userid varchar2(100); secondhash INTEGER; -- BEGIN IF instr(apps_id,'#',1,1)=0 then APPS_USER:=apps_id; ELSE -- IF User_Resp='R' then secondhash:=instr(apps_id,'#',1,2); IF secondhash = 0 then APPS_USER:=apps_id||' - Error. This is an invalid Apps Id'; ELSE userid:=substr(apps_id,2,(secondhash - 2)); appid:= substr(apps_id,(secondhash +1),(length(apps_id)-secondhash)); -- BEGIN SELECT responsibility_name INTO resp FROM EUL5_fnd_responsibility_vl WHERE responsibility_id = userid AND application_id = appid; -- EXCEPTION WHEN OTHERS THEN resp:='*** Undefined Error 1 ***'; END; IF resp IS NULL then resp:='*** Error Cannot Find Apps Responsiblity for '||apps_id||' Is this an Apps User Id? ***'; END IF; APPS_USER:=resp; END IF; ELSE secondhash:=instr(apps_id,'#',1,2); IF secondhash <> 0 then APPS_USER:=apps_id||' - Error. Responsibility not a User? Put R in Function Argument'; ELSE userid:=substr(apps_id,2,length(apps_id)-1); -- BEGIN SELECT user_name INTO usr FROM EUL5_fnd_user WHERE user_id = userid; -- EXCEPTION WHEN OTHERS THEN usr:='*** Undefined Error 2 ***'; END; IF usr IS NULL then usr:='*** Error Cannot Find Apps User for '||apps_id||' Is this an Apps Responsibility Id? ***'; END IF; APPS_USER:=usr; END IF; END IF; END IF; RETURN APPS_USER; END EUL5_GET_APPS_USERRESP;sq~sq~!n(sq~#]sq~uq~(tInstall Objectsq~)tALLtStep 1. Click on this Buttont'ID1't'ID2't'ID3't'ID4't'ID5'psq~pppsq~pppsq~Apq~xtappstNONEtBLETtp31tNq~=tAuthor,Description etctYt1q~=sq~28pq~Ssq~Tpq~Apxsq~Wt Text Cursorpuq~Zsq~\sq~`w ?@q~dxsq~\sq~`w ?@q~gxppq~iq~ksq~lq~nq~oq~pppppppq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~~q~Kq~~q~Lq~~q~Mq~~q~Nq~~q~Oq~~q~Pq~~q~Qq~~q~Rq~~q~Sq~~q~Tpsq~ppxuq~pppppxA(psq~pwxppsq~sq~q~wxwxpsq~pxpppwxpppppppppppsq~sq~sq~sq~pxq~\wtdefaultsq~sq~pxq~\wq~q~_xxxsq~ uq~ psq~?@wq~sq~ti18nsq~ttabSizesq~ xsq~pxsq~uq~ ppppppppppxsq~pq~[wxuq~sq~q~mq~[wxq~[wxpq~[sq~uq~ ppppppppppxpppxppppppppppppppppxtcreate or replace function G_PARAM.EUL5_GET_COMPLEX_FOLDER(SOBJ_ID in number, chk in boolean:=TRUE, fldrs in varchar2:='.') return VARCHAR2 is coname varchar2(4000); -- -- objid NUMBER; objstr VARCHAR2(4000):=fldrs; chkid NUMBER:=0; chkstr VARCHAR2(4000); -- -- cursor COBJ is select od_obj_id_from from eul5_obj_deps where od_obj_id_to = SOBJ_ID; -- -- begin -- -- open COBJ; loop fetch COBJ into objid; exit when COBJ%NOTFOUND; if COBJ%FOUND then chkstr:='.'||to_char(objid)||'.'; chkid:=instr(objstr,chkstr,1,1); if chkid =0 then if length(objstr)+length(to_char(objid))>3998 then objstr:=objstr||'*.'; exit; else objstr:=objstr||to_char(objid)||'.'; objstr:=EUL5_GET_COMPLEX_FOLDER(objid,FALSE,objstr); end if; end if; end if; end loop; coname:=objstr; close COBJ; if chk = FALSE then return coname; else coname:= rtrim(coname,'.'); coname:= ltrim(coname,'.'); return coname; end if; -- end EUL5_GET_COMPLEX_FOLDER; --IC_NEW_SOURCE-- create or replace function G_PARAM.EUL5_GET_SIMPLE_FOLDER(COBJ_ID in number, chk in boolean:=TRUE, fldrs in varchar2:='.') return VARCHAR2 is soname varchar2(4000); -- -- objid NUMBER; objtyp VARCHAR2(20); chkid NUMBER:=0; chkstr VARCHAR2(4000); objstr VARCHAR2(4000):=fldrs; -- -- -- cursor SOBJ is select od_obj_id_to, obj_type from eul5_obj_deps, eul5_objs where eul5_objs.obj_id=EUL5_obj_deps.od_obj_id_to and od_obj_id_from = COBJ_ID; -- -- begin -- -- open SOBJ; loop fetch SOBJ into objid, objtyp; exit when SOBJ%NOTFOUND; if SOBJ%FOUND then chkstr:='.'||to_char(objid)||'.'; chkid:=instr(objstr,chkstr,1,1); if chkid =0 then if length(objstr)+length(to_char(objid))>3998 then objstr:=objstr||'*.'; exit; else objstr:=objstr||to_char(objid)||'.'; objstr:=EUL5_GET_SIMPLE_FOLDER(objid,FALSE,objstr); end if; end if; end if; end loop; soname:=objstr; close SOBJ; if chk = FALSE then return soname; else soname:= rtrim(soname,'.'); soname:= ltrim(soname,'.'); return soname; end if; -- -- -- -- end EUL5_GET_SIMPLE_FOLDER; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_OBJECT(EXPID IN NUMBER,CHK IN BOOLEAN:=TRUE) RETURN VARCHAR2 IS DB_ITEM VARCHAR2(4000); -- -- CIDEXP NUMBER; EXPTYPE VARCHAR2(10); OBJNAME VARCHAR2(4000); OBJTYPE VARCHAR2(10); OBJITM VARCHAR2(200); CNTR NUMBER:=0; -- -- -- This cursor finds out if the item is a complex item (ie. dependant on another item) -- CURSOR EXPOBJ IS SELECT CID_EXP_ID FROM EUL5_EXP_DEPS, EUL5_EXPRESSIONS WHERE EUL5_EXP_DEPS.CD_EXP_ID = EXPID AND CID_EXP_ID IS NOT NULL AND EUL5_EXP_DEPS.CID_EXP_ID = EUL5_EXPRESSIONS.EXP_ID; -- -- -- This cursor looks to the dependant item to see if it is also based on another dependant item -- CURSOR GET_EXP_TYPE IS SELECT EXP_TYPE FROM EUL5_EXPRESSIONS WHERE EXP_ID=CIDEXP; -- -- -- This cursor finally gets the database object the item is based upon -- CURSOR GET_DB_ITEM IS SELECT OBJ_EXT_OWNER||'.'||SOBJ_EXT_TABLE||'.', IT_EXT_COLUMN, OBJ_TYPE FROM EUL5_EXPRESSIONS, EUL5_OBJS WHERE EUL5_OBJS.OBJ_ID=EUL5_EXPRESSIONS.IT_OBJ_ID AND EXP_ID = CIDEXP; -- -- -- BEGIN -- OPEN EXPOBJ; LOOP CNTR:=CNTR+1; FETCH EXPOBJ INTO CIDEXP; IF EXPOBJ%NOTFOUND THEN IF CHK=TRUE THEN IF CNTR=1 THEN CIDEXP:=EXPID; OPEN GET_EXP_TYPE; FETCH GET_EXP_TYPE INTO EXPTYPE; IF EXPTYPE <> 'CO' THEN IF CHK=TRUE THEN OBJNAME:='This item is not based on a database column'; DB_ITEM:=OBJNAME; RETURN DB_ITEM; END IF; END IF; CLOSE GET_EXP_TYPE; ELSE EXIT; END IF; ELSE EXIT; END IF; END IF; OPEN GET_EXP_TYPE; FETCH GET_EXP_TYPE INTO EXPTYPE; IF EXPTYPE ='CI' THEN OBJNAME := EUL5_GET_OBJECT(CIDEXP,FALSE); IF LENGTH(OBJNAME)+ LENGTH(DB_ITEM)>3999 THEN DB_ITEM:=DB_ITEM||'*'; RETURN DB_ITEM; ELSE DB_ITEM := DB_ITEM||OBJNAME; END IF; ELSE OPEN GET_DB_ITEM; FETCH GET_DB_ITEM INTO OBJNAME,OBJITM,OBJTYPE; IF OBJTYPE='CUO' THEN DB_ITEM:='Custom SQL Item - '||OBJITM; ELSE DB_ITEM:=chr(10)||OBJNAME||OBJITM; END IF; CLOSE GET_DB_ITEM; END IF; CLOSE GET_EXP_TYPE; END LOOP; CLOSE EXPOBJ; IF CHK=TRUE THEN DB_ITEM:=LTRIM(DB_ITEM,chr(10)); END IF; RETURN DB_ITEM; END EUL5_GET_OBJECT; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_ITEM(EXPID IN NUMBER, CHK IN BOOLEAN:=TRUE) RETURN VARCHAR2 IS DEP_ITEM VARCHAR2(4000); -- -- CIDEXP NUMBER; EXPTYPE VARCHAR2(10); OBJNAME VARCHAR2(4000); EXPNAME VARCHAR2(200); OBJTYPE VARCHAR2(10); CNTR NUMBER:=0; -- -- -- This cursor finds out if the item is a complex item (ie. dependant on another item) -- CURSOR EXPOBJ IS SELECT CID_EXP_ID FROM EUL5_EXP_DEPS, EUL5_EXPRESSIONS WHERE EUL5_EXP_DEPS.CD_EXP_ID = EXPID AND CID_EXP_ID IS NOT NULL AND EUL5_EXP_DEPS.CD_EXP_ID = EUL5_EXPRESSIONS.EXP_ID; -- -- -- This cursor looks to the dependant item to see if it is also based on another dependant item -- CURSOR GET_EXP_TYPE IS SELECT EXP_TYPE FROM EUL5_EXPRESSIONS WHERE EXP_ID=CIDEXP; -- -- -- -- CURSOR GET_EXP_NAME IS SELECT OBJ_NAME,EXP_NAME FROM EUL5_EXPRESSIONS, EUL5_OBJS WHERE EUL5_OBJS.OBJ_ID=EUL5_EXPRESSIONS.IT_OBJ_ID AND EXP_ID=CIDEXP; -- -- BEGIN -- DEP_ITEM := ''; OPEN EXPOBJ; LOOP CNTR:=CNTR+1; FETCH EXPOBJ INTO CIDEXP; IF EXPOBJ%NOTFOUND THEN IF CHK=TRUE THEN IF CNTR=1 THEN CIDEXP:=EXPID; OPEN GET_EXP_TYPE; FETCH GET_EXP_TYPE INTO EXPTYPE; IF EXPTYPE <> 'CO' THEN IF CHK=TRUE THEN OBJNAME:=''; DEP_ITEM:=OBJNAME; RETURN DEP_ITEM; END IF; END IF; CLOSE GET_EXP_TYPE; ELSE EXIT; END IF; ELSE EXIT; END IF; END IF; OPEN GET_EXP_TYPE; FETCH GET_EXP_TYPE INTO EXPTYPE; IF EXPTYPE ='CI' THEN OBJNAME := EUL5_GET_ITEM(CIDEXP,FALSE); IF LENGTH(OBJNAME)+ LENGTH(DEP_ITEM)>3999 THEN DEP_ITEM:=DEP_ITEM||'*'; RETURN DEP_ITEM; ELSE DEP_ITEM := DEP_ITEM||OBJNAME; END IF; ELSE OPEN GET_EXP_NAME; FETCH GET_EXP_NAME INTO OBJNAME,EXPNAME; DEP_ITEM:=DEP_ITEM || chr(10)||OBJNAME||'.'||EXPNAME; CLOSE GET_EXP_NAME; END IF; CLOSE GET_EXP_TYPE; END LOOP; CLOSE EXPOBJ; IF CHK=TRUE THEN DEP_ITEM:=LTRIM(DEP_ITEM,chr(10)); END IF; RETURN DEP_ITEM; END EUL5_GET_ITEM; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_HIERORD(HIID IN NUMBER, HNID IN NUMBER, PARENT IN NUMBER:=1, ORDNO IN NUMBER:=0 ) RETURN NUMBER IS HIERORD NUMBER; -- -- TOPID NUMBER; CHILD NUMBER:=PARENT; TREEID NUMBER; IDNUM NUMBER:=0; -- -- CURSOR GET_HIERTOP IS SELECT B.HN_ID FROM EUL5_HIERARCHIES A, EUL5_HI_NODES B, EUL5_HI_SEGMENTS C WHERE A.HI_ID=B.HN_HI_ID AND B.HN_ID=C.IHS_HN_ID_CHILD(+) AND A.HI_ID=HIID AND C.IHS_HI_ID IS NULL; -- -- -- CURSOR GET_TREE IS SELECT IHS_HN_ID_CHILD FROM EUL5_HI_SEGMENTS WHERE IHS_HN_ID_PARENT=CHILD AND IHS_HI_ID=HIID; -- -- BEGIN -- IF CHILD=1 THEN OPEN GET_HIERTOP; FETCH GET_HIERTOP INTO TOPID; IDNUM:=IDNUM+1; IF TOPID=HNID THEN HIERORD:=IDNUM; RETURN HIERORD; END IF; CHILD:=TOPID; CLOSE GET_HIERTOP; -- -- OPEN GET_TREE; LOOP FETCH GET_TREE INTO TREEID; EXIT WHEN GET_TREE%NOTFOUND; IDNUM:=IDNUM+1; IF TREEID=HNID THEN HIERORD:=IDNUM; RETURN HIERORD; EXIT; ELSE IDNUM:=EUL5_GET_HIERORD(HIID,HNID,TREEID,IDNUM); IF CEIL(IDNUM)<>IDNUM THEN HIERORD:=FLOOR(IDNUM); RETURN HIERORD; EXIT; CLOSE GET_TREE; END IF; END IF; END LOOP; ELSE IDNUM:=ORDNO; OPEN GET_TREE; LOOP FETCH GET_TREE INTO TREEID; EXIT WHEN GET_TREE%NOTFOUND; IDNUM:=IDNUM+1; IF TREEID=HNID THEN IDNUM:=IDNUM+0.1; HIERORD:=IDNUM; RETURN HIERORD; EXIT; CLOSE GET_TREE; ELSE IDNUM:=EUL5_GET_HIERORD(HIID,HNID,TREEID,IDNUM); IF CEIL(IDNUM)<>IDNUM THEN HIERORD:=IDNUM; RETURN HIERORD; EXIT; CLOSE GET_TREE; END IF; -- HIERORD:=IDNUM; -- RETURN HIERORD; END IF; END LOOP; HIERORD:=IDNUM; CLOSE GET_TREE; RETURN HIERORD; END IF; CLOSE GET_TREE; -- HIERORD:=-1; RETURN HIERORD; END EUL5_GET_HIERORD; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_HIERLVL(HIID IN NUMBER, HNID IN NUMBER, LVLNO IN NUMBER:=0) RETURN NUMBER IS HIERLVL NUMBER; -- -- -- LVL NUMBER; PARENT NUMBER; -- -- -- CURSOR HIER_LEVEL IS SELECT IHS_HN_ID_PARENT FROM EUL5_HI_SEGMENTS WHERE IHS_HI_ID=HIID AND IHS_HN_ID_CHILD=HNID; -- -- BEGIN LVL:=LVLNO; LVL:=LVL+1; OPEN HIER_LEVEL; FETCH HIER_LEVEL INTO PARENT; IF HIER_LEVEL%NOTFOUND THEN HIERLVL:=LVL; RETURN HIERLVL; CLOSE HIER_LEVEL; ELSE LVL:=EUL5_GET_HIERLVL(HIID,PARENT,LVL); END IF; HIERLVL:=LVL; RETURN HIERLVL; CLOSE HIER_LEVEL; END EUL5_GET_HIERLVL; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_ADATE(OWNER_NAME IN VARCHAR2, TAB_NAME IN VARCHAR2) RETURN VARCHAR2 IS LAST_ANALYZED VARCHAR2(12); -- -- -- -- LA_DATE VARCHAR2(12):='*'; -- -- -- BEGIN -- LAST_ANALYZED:='**'; -- BEGIN -- SELECT DISTINCT TO_CHAR(TO_DATE(LAST_ANALYZED),'DD-MON-YYYY') INTO LA_DATE FROM ALL_TAB_COLUMNS WHERE OWNER=OWNER_NAME AND TABLE_NAME=TAB_NAME; -- EXCEPTION WHEN OTHERS THEN LA_DATE:='Unknown'; END; -- IF LA_DATE IS NULL THEN LA_DATE:='Not Analyzed'; END IF; -- LAST_ANALYZED:=LA_DATE; RETURN LAST_ANALYZED; -- -- END EUL5_GET_ADATE; --IC_NEW_SOURCE-- create or replace function G_PARAM.EUL5_GET_ANALYZED(COBJ_ID in number, DATE_OR_TAB in varchar2, chk in boolean:=TRUE, fldrs in varchar2:=',') return VARCHAR2 is soname varchar2(4000); -- -- objid NUMBER:=NULL; objtyp VARCHAR2(10); objowner VARCHAR2(64); objtable VARCHAR2(64); chkid NUMBER:=0; rcrds BOOLEAN:=TRUE; chkstr VARCHAR2(4000); objstr VARCHAR2(4000):=fldrs; datestr VARCHAR2(4000):=null; endown NUMBER; endtab NUMBER; stpt NUMBER; sze NUMBER; -- -- -- cursor COBJ is select od_obj_id_to, obj_type, obj_ext_owner, sobj_ext_table from EUL5_obj_deps, EUL5_objs where EUL5_objs.obj_id=EUL5_obj_deps.od_obj_id_to and od_obj_id_from = COBJ_ID; -- cursor SOBJ is select obj_ext_owner, sobj_ext_table from EUL5_objs where obj_id = COBJ_ID and obj_type = 'SOBJ'; -- begin -- -- open COBJ; loop fetch COBJ into objid, objtyp, objowner, objtable; exit when COBJ%NOTFOUND; if COBJ%FOUND then rcrds:=FALSE; chkstr:=objowner||'.'||objtable; chkid:=instr(objstr,chkstr,1,1); if chkid =0 then if length(objstr)+length(to_char(nvl(objid,0)))>3997 then objstr:=substr(objstr,1,3998)||'*'||CHR(10); exit; else if objtyp='SOBJ' then objstr:=objstr||objowner||'.'||objtable||chr(10); end if; objstr:=EUL5_GET_ANALYZED(objid,'T',FALSE,objstr); end if; end if; end if; end loop; close COBJ; if chk = FALSE then soname:=objstr; elsif rcrds=FALSE then if DATE_OR_TAB='T' then soname:=objstr; soname:= rtrim(soname,CHR(10)); soname:= ltrim(soname,','); else soname:=objstr; objstr:=ltrim(objstr,','); sze:=length(objstr); stpt:=1; if sze > 3 then loop endown:=instr(objstr,'.',stpt,1); endtab:=instr(objstr,chr(10),stpt,1)-1; datestr:=datestr||EUL5_GET_ADATE(substr(objstr,stpt,(endown-stpt)),substr(objstr,endown+1,(endtab-endown)))||chr(10); stpt:=endtab+2; exit when stpt+2 >= sze; end loop; end if; datestr:= rtrim(datestr,CHR(10)); soname:=nvl(datestr,'Unknown'); end if; elsif rcrds=TRUE then open SOBJ; fetch SOBJ into objowner, objtable; if SOBJ%FOUND then objstr:=','||objowner||'.'||objtable||chr(10); soname:=objstr; soname:= rtrim(soname,CHR(10)); soname:= ltrim(soname,','); else soname:='Unknown'; end if; close SOBJ; if DATE_OR_TAB<>'T' then if soname='Unknown' then soname:='Unknown'; else objstr:=ltrim(objstr,','); sze:=length(objstr); stpt:=1; if sze > 3 then loop endown:=instr(objstr,'.',stpt,1); endtab:=instr(objstr,chr(10),stpt,1)-1; datestr:=datestr||EUL5_GET_ADATE(substr(objstr,stpt,(endown-stpt)),substr(objstr,endown+1,(endtab-endown)))||chr(10); stpt:=endtab+2; exit when stpt+2 >= sze; end loop; end if; datestr:= rtrim(datestr,CHR(10)); soname:=nvl(datestr,'Unknown'); end if; end if; end if; return soname; -- end EUL5_GET_ANALYZED; --IC_NEW_SOURCE-- create or replace function G_PARAM.EUL5_GET_OBJECT_NAME(USEKEY in VARCHAR2, TYPEKEY in varchar2) return VARCHAR2 is oname varchar2(4000); -- -- Type Key is either 'F' for folder or 'I' for item or 'J' for Join . -- -- startpt NUMBER :=1; endpt NUMBER :=length(USEKEY); pos NUMBER :=1; ctr NUMBER :=0; chklgth NUMBER; objid NUMBER; aggtype NUMBER; objname VARCHAR2(100); checkobjid VARCHAR2(4000); joinexpid NUMBER; -- -- cursor folder is select obj_name from EUL5_objs where obj_id = objid; -- -- -- cursor item is select exp_name from EUL5_expressions where exp_id = objid; -- -- -- cursor join is select exp_id from EUL5_expressions where jp_key_id = objid; -- -- -- cursor act_join is select key_name from EUL5_key_cons where key_id = objid; -- -- -- begin -- if USEKEY is null then pos:=0; oname:='None'; end if; -- -- This bit works out the id's position in the string and moves down the string by looping -- while pos <> 0 loop aggtype:=0; ctr:=ctr+1; pos:=instr(USEKEY,'.',1,ctr); begin if pos=0 then if upper(TYPEKEY)='F' then checkobjid:=UPPER(substr(USEKEY,startpt,(endpt-startpt+1))); if substr(checkobjid,1,1)='S' then objid:=-1; else objid:=to_number(translate(checkobjid,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')); end if; else objid:= UPPER(substr(USEKEY,startpt,(endpt-startpt-1))); aggtype:=to_number(substr(USEKEY,endpt,1)); end if; else if upper(TYPEKEY)='F' then checkobjid:= upper(substr(USEKEY,startpt,(pos-startpt))); if substr(checkobjid,1,1)='S' then objid:=-1; else objid:=to_number(translate(checkobjid,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')); end if; else objid:= to_number(substr(USEKEY,startpt,(pos-startpt-2))); aggtype:= to_number(substr(USEKEY,pos-1,1)); end if; startpt:=pos+1; end if; exception when others then objid:=-1; end; -- -- if upper(TYPEKEY) ='F' then open folder; fetch folder into objname; close folder; end if; -- -- if upper(TYPEKEY)='I' then open item; fetch item into objname; close item; if substr(objname,1,19)='AUTO_GENERATED_NAME'then objname:=EUL5_GET_AUTOGEN_ITEM_NAME(objid); end if; end if; -- -- if upper(TYPEKEY)='J' then open act_join; fetch act_join into objname; close act_join; if substr(objname,1,19)='AUTO_GENERATED_NAME'then open join; fetch join into joinexpid; close join; objname:=EUL5_GET_JOIN(joinexpid); end if; end if; -- -- This bit builds up the string of folder names if it exceeds -- 4000 chars it stops then places an '*' at the end. -- if ctr=1 then if aggtype=0 then oname:=objname; elsif aggtype=1 then oname:=objname||' SUM'; elsif aggtype=2 then oname:=objname||' AVG'; elsif aggtype=3 then oname:=objname||' COUNT'; elsif aggtype=4 then oname:=objname||' MAX'; elsif aggtype=5 then oname:=objname||' MIN'; else oname:=objname; end if; else if aggtype=0 then chklgth:= length(oname)+length(objname)+2; elsif aggtype=1 then chklgth:= length(oname)+length(objname)+6; elsif aggtype=2 then chklgth:= length(oname)+length(objname)+6; elsif aggtype=3 then chklgth:= length(oname)+length(objname)+8; elsif aggtype=4 then chklgth:= length(oname)+length(objname)+6; elsif aggtype=5 then chklgth:= length(oname)+length(objname)+6; else chklgth:= length(oname)+length(objname)+2; end if; if chklgth > 3999 then oname:=oname||'*'; exit; else if aggtype = 0 then if oname is not null then oname:=oname||','||CHR(10)||objname; else oname:=objname; end if; elsif aggtype=1 then oname:=oname||','||CHR(10)||objname||' SUM'; elsif aggtype=2 then oname:=oname||','||CHR(10)||objname||' AVG'; elsif aggtype=3 then oname:=oname||','||CHR(10)||objname||' COUNT'; elsif aggtype=4 then oname:=oname||','||CHR(10)||objname||' MAX'; elsif aggtype=5 then oname:=oname||','||CHR(10)||objname||' MIN'; else if objname is not null then oname:=oname||','||CHR(10)||objname; else oname:=objname; end if; end if; end if; end if; end loop; -- -- return oname; -- end EUL5_GET_OBJECT_NAME; --IC_NEW_SOURCE-- create or replace function G_PARAM.EUL5_GET_ITEM_NAME(QSID in NUMBER) return VARCHAR2 is Itmid VARCHAR2(4000):=null; -- startpt BINARY_INTEGER :=1; BMP LONG RAW; pos BINARY_INTEGER :=0; ctr BINARY_INTEGER :=0; chklgth BINARY_INTEGER; hexstring VARCHAR2(10); hexid BINARY_INTEGER :=0; decnibble1 NUMBER; decnibble2 BINARY_INTEGER; decnibble3 BINARY_INTEGER; decnibble4 BINARY_INTEGER; decnibble5 BINARY_INTEGER; decnibble6 BINARY_INTEGER; decnibble7 BINARY_INTEGER; decnibble8 BINARY_INTEGER; decnibble9 BINARY_INTEGER; decnibble10 BINARY_INTEGER; hexchar VARCHAR2(1); expid NUMBER; aggtype BINARY_INTEGER; nibblezero BOOLEAN; -- -- Occasionally, due to a bug, the first 5 bytes of the string are not always populated with id of -- the item so rather than go all the way down the string looking a every 5 bytes until it reaches -- the end (a slow process) I count the number of empy stings I have found so far and when it -- reaches the the value held in 'noemptyblocks' it moves on to the next string -- noemptyblks BINARY_INTEGER:=10; -- -- -- -- -- This cursor finds the Dimension values -- cursor dbmp is select qs_dbmp0||qs_dbmp1||qs_dbmp2||qs_dbmp3||qs_dbmp4||qs_dbmp5||qs_dbmp6||qs_dbmp7 from EUL5_qpp_stats where qs_id = QSID; -- -- -- This cursor finds the Measure values -- cursor mbmp is select qs_mbmp0||qs_mbmp1||qs_mbmp2||qs_mbmp3||qs_mbmp4||qs_mbmp5||qs_mbmp6||qs_mbmp7 from EUL5_qpp_stats where qs_id = QSID; -- -- begin -- -- Loop Twice First loop deals with the Dimensions values the scond with the Measure Values -- for itype in 1..2 loop if itype = 1 then open dbmp; else open mbmp; end if; hexid:=0; -- -- -- --This bit takes a five byte chunk in the string. -- It then loops until it reaches the end of the string or it find no more values -- -- while hexid <> noemptyblks loop if itype = 1 then fetch dbmp into BMP; else fetch mbmp into BMP; end if; while hexid <> noemptyblks loop ctr:=ctr+1; pos:=pos+10; if pos=4090 then hexid:= noemptyblks; startpt:=1; pos:=0; else hexstring:=nvl(substr(rawtohex(BMP),startpt,10),'0000000000'); if hexstring = '0000000000' then hexid:=hexid + 1; decnibble1:=0; decnibble2:=0; decnibble3:=0; decnibble4:=0; decnibble5:=0; decnibble6:=0; decnibble7:=0; decnibble8:=0; decnibble9:=0; decnibble10:=0; nibblezero:= TRUE; if hexid = noemptyblks then startpt:=1; pos:=0; end if; -- -- -- Converts Hex Nibble into Decimal value. -- else nibblezero:=FALSE; hexchar:=substr(rawtohex(BMP),startpt,1); if hexchar = '0' then decnibble1:=0; elsif hexchar ='A' then decnibble1:=10; elsif hexchar ='B' then decnibble1:=11; elsif hexchar ='C' then decnibble1:=12; elsif hexchar ='D' then decnibble1:=13; elsif hexchar ='E' then decnibble1:=14; elsif hexchar ='F' then decnibble1:=15; else decnibble1:=to_number(hexchar); end if; hexchar:=substr(rawtohex(BMP),startpt+1,1); if hexchar = '0' then decnibble2:=0; elsif hexchar ='A' then decnibble2:=10; elsif hexchar ='B' then decnibble2:=11; elsif hexchar ='C' then decnibble2:=12; elsif hexchar ='D' then decnibble2:=13; elsif hexchar ='E' then decnibble2:=14; elsif hexchar ='F' then decnibble2:=15; else decnibble2:=to_number(hexchar); end if; hexchar:=substr(rawtohex(BMP),startpt+2,1); if hexchar = '0' then decnibble3:=0; elsif hexchar ='A' then decnibble3:=10; elsif hexchar ='B' then decnibble3:=11; elsif hexchar ='C' then decnibble3:=12; elsif hexchar ='D' then decnibble3:=13; elsif hexchar ='E' then decnibble3:=14; elsif hexchar ='F' then decnibble3:=15; else decnibble3:=to_number(hexchar); end if; hexchar:= substr(rawtohex(BMP),startpt+3,1); if hexchar = '0' then decnibble4:=0; elsif hexchar ='A' then decnibble4:=10; elsif hexchar ='B' then decnibble4:=11; elsif hexchar ='C' then decnibble4:=12; elsif hexchar ='D' then decnibble4:=13; elsif hexchar ='E' then decnibble4:=14; elsif hexchar ='F' then decnibble4:=15; else decnibble4:=to_number(hexchar); end if; hexchar :=substr(rawtohex(BMP),startpt+4,1); if hexchar = '0' then decnibble5:=0; elsif hexchar ='A' then decnibble5:=10; elsif hexchar ='B' then decnibble5:=11; elsif hexchar ='C' then decnibble5:=12; elsif hexchar ='D' then decnibble5:=13; elsif hexchar ='E' then decnibble5:=14; elsif hexchar ='F' then decnibble5:=15; else decnibble5:=to_number(hexchar ); end if; hexchar := substr(rawtohex(BMP),startpt+5,1); if hexchar = '0' then decnibble6:=0; elsif hexchar ='A' then decnibble6:=10; elsif hexchar='B' then decnibble6:=11; elsif hexchar='C' then decnibble6:=12; elsif hexchar='D' then decnibble6:=13; elsif hexchar='E' then decnibble6:=14; elsif hexchar='F' then decnibble6:=15; else decnibble6:=to_number(hexchar); end if; hexchar := substr(rawtohex(BMP),startpt+6,1); if hexchar = '0' then decnibble7:=0; elsif hexchar ='A' then decnibble7:=10; elsif hexchar='B' then decnibble7:=11; elsif hexchar='C' then decnibble7:=12; elsif hexchar='D' then decnibble7:=13; elsif hexchar='E' then decnibble7:=14; elsif hexchar='F' then decnibble7:=15; else decnibble7:=to_number(hexchar); end if; hexchar :=substr(rawtohex(BMP),startpt+7,1); if hexchar = '0' then decnibble8:=0; elsif hexchar ='A' then decnibble8:=10; elsif hexchar='B' then decnibble8:=11; elsif hexchar='C' then decnibble8:=12; elsif hexchar='D' then decnibble8:=13; elsif hexchar='E' then decnibble8:=14; elsif hexchar='F' then decnibble8:=15; else decnibble8:=to_number(hexchar); end if; hexchar:= substr(rawtohex(BMP),startpt+8,1); if hexchar = '0' then decnibble9:=0; elsif hexchar ='A' then decnibble9:=10; elsif hexchar='B' then decnibble9:=11; elsif hexchar='C' then decnibble9:=12; elsif hexchar='D' then decnibble9:=13; elsif hexchar='E' then decnibble9:=14; elsif hexchar='F' then decnibble9:=15; else decnibble9:=to_number(hexchar); end if; if itype = 2 then hexchar :=substr(rawtohex(BMP),startpt+9,1); if hexchar = '0' then decnibble10:=0; elsif hexchar ='A' then decnibble10:=10; elsif hexchar='B' then decnibble10:=11; elsif hexchar='C' then decnibble10:=12; elsif hexchar='D' then decnibble10:=13; elsif hexchar='E' then decnibble10:=14; elsif hexchar='F' then decnibble10:=15; else decnibble10:=to_number(hexchar); end if; end if; -- -- -- Off set the nibble by One Byte -- Then calculate the Item id -- if nibblezero = FALSE then decnibble1:=decnibble1*2; if decnibble2 > 7 then decnibble1:=decnibble1+1; decnibble2:=decnibble2-8; end if; decnibble1:=decnibble1 * 268435456; decnibble2:=decnibble2 * 2; if decnibble3 > 7 then decnibble2:=decnibble2+1; decnibble3:=decnibble3-8; end if; decnibble2:=decnibble2 * 16777216; decnibble3:=decnibble3*2; if decnibble4 > 7 then decnibble3:=decnibble3+1; decnibble4:=decnibble4-8; end if; decnibble3:=decnibble3 * 1048576; decnibble4:=decnibble4*2; if decnibble5 > 7 then decnibble4:=decnibble4+1; decnibble5:=decnibble5-8; end if; decnibble4:=decnibble4 * 65536; decnibble5:=decnibble5*2; if decnibble6 > 7 then decnibble5:=decnibble5+1; decnibble6:=decnibble6-8; end if; decnibble5:=decnibble5 * 4096; decnibble6:=decnibble6*2; if decnibble7 > 7 then decnibble6:=decnibble6+1; decnibble7:=decnibble7-8; end if; decnibble6:=decnibble6 * 256; decnibble7:=decnibble7*2; if decnibble8 > 7 then decnibble7:=decnibble7+1; decnibble8:=decnibble8-8; end if; decnibble7:=decnibble7 * 16; decnibble8:=decnibble8*2; if decnibble9 > 7 then decnibble8:=decnibble8+1; decnibble9:=decnibble9-8; end if; if itype=2 then if decnibble9>0 then decnibble9:=(decnibble9-2)*8; end if; decnibble10:=decnibble9+(decnibble10/2); end if; expid:= decnibble1 + decnibble2 + decnibble3 + decnibble4 + decnibble5 + decnibble6 + decnibble7 + decnibble8; end if; end if; end if; startpt:=pos+1; -- -- -- Build up the string of item ids used in the query -- if nibblezero = FALSE then if itype =1 then if nvl(length(itmid),0)=0 then itmid:=to_char(expid)||',0'; else chklgth:= length(itmid)+length(to_char(expid))+4; if chklgth > 3998 then itmid:=itmid||'*'; exit; else itmid:=itmid||'.'||to_char(expid)||',0'; end if; end if; else chklgth:= nvl(length(itmid),0)+length(to_char(expid))+4; if chklgth > 3998 then itmid:=itmid||'*'; exit; elsif chklgth =length(to_char(expid))+4 then itmid:=to_char(expid)||','||to_char(decnibble10); else itmid:=itmid||'.'||to_char(expid)||','||to_char(decnibble10); end if; end if; end if; -- -- Go get the next five bytes in the string -- end loop; -- -- -- Close the cursor on the first loop for the dimensions -- on the second for the Measures -- if itype = 1 then close dbmp; else close mbmp; end if; -- end loop; -- return itmid; -- return hexstring; -- end EUL5_GET_ITEM_NAME; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_ISITAPPS_EUL RETURN NUMBER IS ISITAPPS NUMBER; -- APPS NUMBER:=0; -- BEGIN -- ISITAPPS:=0; -- BEGIN -- SELECT COUNT(*) INTO ISITAPPS FROM EUL5_APP_PARAMS WHERE APP_TYPE='SP' AND APP_ID=1016; -- EXCEPTION WHEN OTHERS THEN ISITAPPS:=0; END; -- RETURN ISITAPPS; -- -- END EUL5_GET_ISITAPPS_EUL; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_APPS_USERRESP(apps_id in varchar2,User_Resp in Varchar2:='U') RETURN VARCHAR2 IS APPS_USER VARCHAR2(100); BEGIN APPS_USER:='EUL5_APPS.sql has not been run.'||CHR(10)||'When you run the script ensure you are the EUL owner.'; RETURN APPS_USER; END EUL5_GET_APPS_USERRESP; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_LINURL(SAPP_ID IN NUMBER) RETURN VARCHAR2 IS LINURL_SEG VARCHAR2(240); -- -- URL_SEG VARCHAR2(240); -- -- CURSOR LINURL IS SELECT SP_VALUE FROM EUL5_APP_PARAMS WHERE APP_ID=SAPP_ID; -- -- BEGIN OPEN LINURL; FETCH LINURL INTO URL_SEG; CLOSE LINURL; -- LINURL_SEG:= URL_SEG; RETURN LINURL_SEG; END EUL5_GET_LINURL; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_EUL_DETAILS(schema in varchar2,col in varchar2) RETURN VARCHAR2 IS APPS_USER VARCHAR2(240); SQL_STMT VARCHAR2(4000); NOEUL5 EXCEPTION; PRAGMA EXCEPTION_INIT(NOEUL5,-00942); BEGIN SQL_STMT:='SELECT '||col||' FROM '||schema||'.EUL5_VERSIONS'; EXECUTE IMMEDIATE SQL_STMT INTO APPS_USER; RETURN APPS_USER; EXCEPTION WHEN NOEUL5 THEN APPS_USER := NULL; RETURN APPS_USER; -- WHEN OTHERS THEN APPS_USER := NULL; RETURN APPS_USER; -- END EUL5_GET_EUL_DETAILS; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_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 EUL5_EXP_DEPS, EUL5_EXPRESSIONS WHERE EUL5_EXP_DEPS.CD_EXP_ID = EXPID AND CID_EXP_ID IS NOT NULL AND EUL5_EXP_DEPS.CD_EXP_ID = EUL5_EXPRESSIONS.EXP_ID; -- -- -- CURSOR GET_EXP_NAME IS SELECT EXP_NAME FROM EUL5_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:=EUL5_GET_AUTOGEN_ITEM_NAME(CIDEXP); END IF; END IF; RETURN DEP_ITEM; END EUL5_GET_AUTOGEN_ITEM_NAME; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_HIERNODE_EXPID(HNID IN NUMBER) RETURN NUMBER IS EXPID NUMBER(10); -- -- -- -- -- CURSOR GET_EXP_ID IS SELECT HIL_EXP_ID FROM EUL5_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 EUL5_GET_HIERNODE_EXPID; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_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 EUL5_HIERARCHIES WHERE HI_NAME=HINAME; -- -- CURSOR GET_HILHNID IS SELECT IHS_HN_ID_CHILD FROM EUL5_HI_SEGMENTS WHERE IHS_HI_ID=HIID AND IHS_HN_ID_CHILD NOT IN (SELECT IHS_HN_ID_PARENT FROM EUL5_HI_SEGMENTS WHERE IHS_HI_ID=HIID); -- -- CURSOR GET_EXPID IS SELECT HIL_EXP_ID FROM EUL5_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 EUL5_GET_HIER_EXPID; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_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 EUL5_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 EUL5_GET_DATEHIER_TMPLT_NAME; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_FOLDERNAME(EXPID IN NUMBER) RETURN VARCHAR2 IS FLRNAME VARCHAR2(200); -- -- -- -- -- CURSOR GET_FLDRNAM IS SELECT OBJ_NAME FROM EUL5_OBJS,EUL5_EXPRESSIONS WHERE EUL5_OBJS.OBJ_ID=EUL5_EXPRESSIONS.IT_OBJ_ID AND EUL5_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 EUL5_GET_FOLDERNAME; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_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 EUL5_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:=EUL5_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:=EUL5_GET_FOLDERNAME(EXPIDDTL); IF upper(JOINAME)='Y' THEN JOINMSTR:=MASTERFLDR||'.'||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR); JOINDTL:=DETAILFLDR||'.'||EUL5_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||','||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR); JOINDTL:= JOINDTL||','||EUL5_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||','||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR)||' -> '; JOINDTL:= JOINDTL||','||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDDTL); JOIN:=JOINMSTR||JOINDTL; ELSE JOINMSTR:=MASTERFLDR||'.'||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR); JOINDTL:=DETAILFLDR||'.'||EUL5_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||'.'||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR); JOINDTL:= DETAILFLDR||'.'||EUL5_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||'.'||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR); JOINDTL:=DETAILFLDR||'.'||EUL5_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:=EUL5_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:=EUL5_GET_FOLDERNAME(EXPIDDTL); IF upper(JOINAME)='Y' THEN JOINMSTR:=MASTERFLDR||'.'||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR)||' -> '; JOINDTL:=DETAILFLDR||'.'||EUL5_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||'.'||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDMSTR); JOINDTL:=DETAILFLDR||'.'||EUL5_GET_AUTOGEN_ITEM_NAME(EXPIDDTL); JOIN:=JOINMSTR||OPERATOR||JOINDTL; END IF; END IF; CLOSE GET_FORMULA; END IF; RETURN JOIN; END EUL5_GET_JOIN; --IC_NEW_SOURCE-- CREATE OR REPLACE function G_PARAM.EUL5_GET_JOIN_EXPID(JPID IN NUMBER,DEVKEY IN VARCHAR2) RETURN NUMBER IS EXPID NUMBER(10); -- -- KEYID NUMBER(10); -- -- -- -- CURSOR GET_EXPID IS SELECT EXP_ID FROM EUL5_EXPRESSIONS WHERE EXP_TYPE='JP' AND JP_KEY_ID=JPID; -- -- CURSOR GET_EXPID_FROM_DVKEY IS SELECT KEY_ID FROM EUL5_KEY_CONS WHERE KEY_DEVELOPER_KEY=DEVKEY; -- -- BEGIN -- IF JPID IS NULL THEN EXPID:=NULL; ELSE IF DEVKEY IS NULL THEN OPEN GET_EXPID; FETCH GET_EXPID INTO EXPID; IF GET_EXPID%NOTFOUND THEN EXPID:=NULL; END IF; CLOSE GET_EXPID; ELSE OPEN GET_EXPID; FETCH GET_EXPID INTO EXPID; IF GET_EXPID%NOTFOUND THEN OPEN GET_EXPID_FROM_DVKEY; FETCH GET_EXPID_FROM_DVKEY INTO KEYID; IF GET_EXPID_FROM_DVKEY%NOTFOUND THEN EXPID:=NULL; ELSE EXPID:=EUL5_GET_JOIN_EXPID(KEYID,NULL); END IF; CLOSE GET_EXPID_FROM_DVKEY; END IF; CLOSE GET_EXPID; END IF; END IF; RETURN EXPID; END EUL5_GET_JOIN_EXPID; --IC_NEW_SOURCE-- create or replace function G_PARAM.EUL5_GET_FORJ_ITEMID(QSID in NUMBER, JOIN_OR_COND VARCHAR2:='COND') return VARCHAR2 is Itmid VARCHAR2(4000):=null; -- startpt BINARY_INTEGER :=1; BMP LONG RAW; pos BINARY_INTEGER :=0; ctr BINARY_INTEGER :=0; chklgth BINARY_INTEGER; hexstring VARCHAR2(10); hexid BINARY_INTEGER :=0; decnibble1 NUMBER; decnibble2 BINARY_INTEGER; decnibble3 BINARY_INTEGER; decnibble4 BINARY_INTEGER; decnibble5 BINARY_INTEGER; decnibble6 BINARY_INTEGER; decnibble7 BINARY_INTEGER; decnibble8 BINARY_INTEGER; decnibble9 BINARY_INTEGER; decnibble10 BINARY_INTEGER; hexchar VARCHAR2(1); expid NUMBER; aggtype BINARY_INTEGER; nibblezero BOOLEAN; -- -- Occasionally, due to a bug, the first 5 bytes of the string are not always populated with id of -- the item so rather than go all the way down the string looking a every 5 bytes until it reaches -- the end (a slow process) I count the number of empy stings I have found so far and when it -- reaches the the value held in 'noemptyblocks' it moves on to the next string -- noemptyblks BINARY_INTEGER:=10; -- -- -- -- -- This cursor finds the Filter values -- cursor fbmp is select qs_fbmp0||qs_fbmp1||qs_fbmp2||qs_fbmp3||qs_fbmp4||qs_fbmp5||qs_fbmp6||qs_fbmp7 from EUL5_qpp_stats where qs_id = QSID; -- -- -- This cursor finds the Join values -- cursor jbmp is select qs_jbmp0||qs_jbmp1||qs_jbmp2||qs_jbmp3||qs_jbmp4||qs_jbmp5||qs_jbmp6||qs_jbmp7 from EUL5_qpp_stats where qs_id = QSID; -- begin -- if UPPER(JOIN_OR_COND)='JOIN' then open jbmp; else open fbmp; end if; hexid:=0; -- -- --This bit takes a five byte chunk in the string. -- It then loops until it reaches the end of the string or it find no more values -- if UPPER(JOIN_OR_COND)='JOIN' then fetch jbmp into BMP; else fetch fbmp into BMP; end if; -- while hexid <> noemptyblks loop ctr:=ctr+1; pos:=pos+10; if pos=4090 then hexid:= noemptyblks; startpt:=1; pos:=0; else hexstring:=nvl(substr(rawtohex(BMP),startpt,10),'0000000000'); if hexstring = '0000000000' then hexid:=hexid + 1; decnibble1:=0; decnibble2:=0; decnibble3:=0; decnibble4:=0; decnibble5:=0; decnibble6:=0; decnibble7:=0; decnibble8:=0; decnibble9:=0; decnibble10:=0; nibblezero:= TRUE; if hexid = noemptyblks then startpt:=1; pos:=0; end if; -- -- -- Converts Hex Nibble into Decimal value. -- else nibblezero:=FALSE; hexchar:=substr(rawtohex(BMP),startpt,1); if hexchar = '0' then decnibble1:=0; elsif hexchar ='A' then decnibble1:=10; elsif hexchar ='B' then decnibble1:=11; elsif hexchar ='C' then decnibble1:=12; elsif hexchar ='D' then decnibble1:=13; elsif hexchar ='E' then decnibble1:=14; elsif hexchar ='F' then decnibble1:=15; else decnibble1:=to_number(hexchar); end if; hexchar:=substr(rawtohex(BMP),startpt+1,1); if hexchar = '0' then decnibble2:=0; elsif hexchar ='A' then decnibble2:=10; elsif hexchar ='B' then decnibble2:=11; elsif hexchar ='C' then decnibble2:=12; elsif hexchar ='D' then decnibble2:=13; elsif hexchar ='E' then decnibble2:=14; elsif hexchar ='F' then decnibble2:=15; else decnibble2:=to_number(hexchar); end if; hexchar:=substr(rawtohex(BMP),startpt+2,1); if hexchar = '0' then decnibble3:=0; elsif hexchar ='A' then decnibble3:=10; elsif hexchar ='B' then decnibble3:=11; elsif hexchar ='C' then decnibble3:=12; elsif hexchar ='D' then decnibble3:=13; elsif hexchar ='E' then decnibble3:=14; elsif hexchar ='F' then decnibble3:=15; else decnibble3:=to_number(hexchar); end if; hexchar:= substr(rawtohex(BMP),startpt+3,1); if hexchar = '0' then decnibble4:=0; elsif hexchar ='A' then decnibble4:=10; elsif hexchar ='B' then decnibble4:=11; elsif hexchar ='C' then decnibble4:=12; elsif hexchar ='D' then decnibble4:=13; elsif hexchar ='E' then decnibble4:=14; elsif hexchar ='F' then decnibble4:=15; else decnibble4:=to_number(hexchar); end if; hexchar :=substr(rawtohex(BMP),startpt+4,1); if hexchar = '0' then decnibble5:=0; elsif hexchar ='A' then decnibble5:=10; elsif hexchar ='B' then decnibble5:=11; elsif hexchar ='C' then decnibble5:=12; elsif hexchar ='D' then decnibble5:=13; elsif hexchar ='E' then decnibble5:=14; elsif hexchar ='F' then decnibble5:=15; else decnibble5:=to_number(hexchar ); end if; hexchar := substr(rawtohex(BMP),startpt+5,1); if hexchar = '0' then decnibble6:=0; elsif hexchar ='A' then decnibble6:=10; elsif hexchar='B' then decnibble6:=11; elsif hexchar='C' then decnibble6:=12; elsif hexchar='D' then decnibble6:=13; elsif hexchar='E' then decnibble6:=14; elsif hexchar='F' then decnibble6:=15; else decnibble6:=to_number(hexchar); end if; hexchar := substr(rawtohex(BMP),startpt+6,1); if hexchar = '0' then decnibble7:=0; elsif hexchar ='A' then decnibble7:=10; elsif hexchar='B' then decnibble7:=11; elsif hexchar='C' then decnibble7:=12; elsif hexchar='D' then decnibble7:=13; elsif hexchar='E' then decnibble7:=14; elsif hexchar='F' then decnibble7:=15; else decnibble7:=to_number(hexchar); end if; hexchar :=substr(rawtohex(BMP),startpt+7,1); if hexchar = '0' then decnibble8:=0; elsif hexchar ='A' then decnibble8:=10; elsif hexchar='B' then decnibble8:=11; elsif hexchar='C' then decnibble8:=12; elsif hexchar='D' then decnibble8:=13; elsif hexchar='E' then decnibble8:=14; elsif hexchar='F' then decnibble8:=15; else decnibble8:=to_number(hexchar); end if; hexchar:= substr(rawtohex(BMP),startpt+8,1); if hexchar = '0' then decnibble9:=0; elsif hexchar ='A' then decnibble9:=10; elsif hexchar='B' then decnibble9:=11; elsif hexchar='C' then decnibble9:=12; elsif hexchar='D' then decnibble9:=13; elsif hexchar='E' then decnibble9:=14; elsif hexchar='F' then decnibble9:=15; else decnibble9:=to_number(hexchar); end if; -- -- -- Off set the nibble by One Byte -- Then calculate the Item id -- if nibblezero = FALSE then decnibble1:=decnibble1*2; if decnibble2 > 7 then decnibble1:=decnibble1+1; decnibble2:=decnibble2-8; end if; decnibble1:=decnibble1 * 268435456; decnibble2:=decnibble2 * 2; if decnibble3 > 7 then decnibble2:=decnibble2+1; decnibble3:=decnibble3-8; end if; decnibble2:=decnibble2 * 16777216; decnibble3:=decnibble3*2; if decnibble4 > 7 then decnibble3:=decnibble3+1; decnibble4:=decnibble4-8; end if; decnibble3:=decnibble3 * 1048576; decnibble4:=decnibble4*2; if decnibble5 > 7 then decnibble4:=decnibble4+1; decnibble5:=decnibble5-8; end if; decnibble4:=decnibble4 * 65536; decnibble5:=decnibble5*2; if decnibble6 > 7 then decnibble5:=decnibble5+1; decnibble6:=decnibble6-8; end if; decnibble5:=decnibble5 * 4096; decnibble6:=decnibble6*2; if decnibble7 > 7 then decnibble6:=decnibble6+1; decnibble7:=decnibble7-8; end if; decnibble6:=decnibble6 * 256; decnibble7:=decnibble7*2; if decnibble8 > 7 then decnibble7:=decnibble7+1; decnibble8:=decnibble8-8; end if; decnibble7:=decnibble7 * 16; decnibble8:=decnibble8*2; if decnibble9 > 7 then decnibble8:=decnibble8+1; decnibble9:=decnibble9-8; end if; expid:= decnibble1 + decnibble2 + decnibble3 + decnibble4 + decnibble5 + decnibble6 + decnibble7 + decnibble8; end if; end if; end if; startpt:=pos+1; -- -- -- Build up the string of item ids used in the query -- -- if nibblezero = FALSE then if nvl(length(itmid),0)=0 then itmid:=to_char(expid)||',0'; else chklgth:= nvl(length(itmid),0)+length(to_char(expid))+4; if chklgth > 3998 then itmid:=itmid||'*'; exit; elsif chklgth =length(to_char(expid))+4 then itmid:=to_char(expid)||','||'0'; else itmid:=itmid||'.'||to_char(expid)||','||'0'; end if; end if; end if; -- -- -- Go get the next five bytes in the string -- end loop; -- -- if UPPER(JOIN_OR_COND)='JOIN' then close jbmp; else close fbmp; end if; -- return itmid; -- return hexstring; -- end EUL5_GET_FORJ_ITEMID; --IC_NEW_SOURCE-- DROP TABLE G_PARAM.EUL5_WORKSHEET_SQL CASCADE CONSTRAINTS --IC_NEW_SOURCE-- create table G_PARAM.eul5_Worksheet_sql ( WORKBOOK_OWNER VARCHAR2(100), WORKBOOK_NAME VARCHAR2(100), WORKSHEET_NAME VARCHAR2(100), SEG_SEQUENCE NUMBER(10), SQL_SEGMENT VARCHAR2(4000)) --IC_NEW_SOURCE-- CREATE OR REPLACE FUNCTION G_PARAM."eul5_post_save_document" ( p_workbookowner in varchar, p_workbook in varchar, p_worksheet in varchar, p_sequence in number, p_sql_segment in varchar) return number as begin if p_sequence<=1 then delete from eul5_worksheet_sql where workbook_owner=p_workbookowner and workbook_name=p_workbook and worksheet_name=p_worksheet; end if; insert into eul5_worksheet_sql (workbook_owner, workbook_name, worksheet_name, seg_sequence, sql_segment) values (p_workbookowner, p_workbook, p_worksheet, p_sequence, p_sql_segment); return 1; end "eul5_post_save_document"; --IC_NEW_SOURCE-- commit sq~sq~uq~ srInfoPurple.ColumnPropertyValues ඳcٸL colPropValuesq~L drillValuesq~L parentQlettLInfoPurple/Qlet;xpsq~uq~t@THISDASHBOARDSIMPLIFIESTHEPROCESSOFINSTALLINGTHENEEDEDCOMPONENTSt624sq~pppsq~@@@pppsq~pppt1q~~sq~A ptArialxtNAtNt ##########t dd-MMM-yyyyq~Isq~A pq~xt@ThisdashboardsimplifiestheprocessofinstallingtheneededcomponentstDEFAULTtYpppxsq~uq~ ppppppppppxq~ssq~vsq~uq~tSTEPt715q~}q~~q~t1q~~sq~A@ ptArialxtNAtNt ##########t dd-MMM-yyyyq~sq~A@ pq~xtSteptDEFAULTtYpppxsq~uq~ ppppppppppxq~sppppppppxsq~!h9sq~#Isq~(uq~(t"Please read this before proceedingq~)t100tManualt300t 1-JAN-1990tYsq~pppq~}q~t1q~q~~t20q~sq~pppsq~A@ pq~xq~~t dd-MMM-yyyyt #########tq31q~~q~q~tappst No_PatterntQLETsq~pppsq~ppptXXDBNAMEt tAuthor,Description etctNq~tDEFAULTq~q~q~sq~28pq~Ssq~Tpq~pxsq~Wt Text Cursorpuq~Zsq~\sq~`w ?@q~dxsq~\sq~`w ?@q~gxppq~iq~ksq~lq~nq~oq~pppppppq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~qsq~rq~~q~q~~q~q~~q~q~~q~q~~q~q~~q~q~~q~q~~q~q~~q~q~~q~psq~ppxuq~pppppxA(psq~pwxppsq~sq~q~wxwxpsq~pxpppwxpppppppppppsq~sq~sq~sq~pxq~wtdefaultsq~sq~pxq~wq~q~xxxsq~ uq~ psq~?@wq~sq~ti18nsq~ttabSizesq~ xsq~pxsq~uq~ ppppppppppxsq~pq~wxuq~sq~q~q~wxq~wxpq~sq~uq~ ppppppppppxpppxq~xtselect * from ( select 1 seq, 'This dashboard simplifies the process of installing the needed components ' step from dual union select 2 ,'All the components that are installed are part of Oracle Discoverer installation' from dual union select 3, 'These objects are part of eul5.sql and eul5_apps.sql that already resides on your Desktop Discoverer installation' from dual union select 4, 'You can skip the steps mentioned below and alternatively run the eul5.sql and eul5_apps.sql independently' from dual union select 5,'Select the appropriate EUL owner from the "EUL Owner Accounts" parameter' from dual union select 6,'Click on the button below, just Once' from dual union select 7, 'If the "Is this Apps based EUL" returns a Yes, then click on the "Step 2" button' from dual union select 7, 'You may close this dashboard now and open the disco_report.icv file and generate documentation on your EUL' from dual ) order by seq asctNppppppppppppppppppppppppppppppppppx