Siebel UI Field Mapper

Often Siebel integration developers, Siebel developers themselves and other people who tend to show an interest in Siebel development from time to time, would want to know the source of all mysteries or rather, the source of a control displayed on the UI. Though Siebel presents a typical n-tiered architecture and it is not that difficult to extract and document this information, typical Siebel systems do not have one and one is required to create the documentation.

Lo and behold, the below Siebel UI field mapper SQL automates part of that interesting task..

SELECT
  RESP.NAME Responsibility,
  VW.NAME ViewName,
  APP.NAME Applet,
  'Form' as AppletType,
  BC.NAME BusinessComponent,
  NVL(CTRLOVR.CAPTION, CTRLSTR.STRING_VALUE) Caption,
  CTRL.FIELD_NAME Field,
  FLD.PICKLIST_NAME Picklist,
  PICK.TYPE_VALUE LOVType
FROM
  MYDB.S_APP_VIEW_RESP VIEWRESP,
  MYDB.S_APP_VIEW APPVIEW,
  MYDB.S_VIEW VW,
  MYDB.S_RESP RESP,
  MYDB.S_VIEW_WEB_TMPL VWT,
  MYDB.S_VIEW_WTMPL_IT VWTI,
  MYDB.S_APPLET APP,
  MYDB.S_CONTROL CTRL,
  MYDB.S_CONTROL_INTL CTRLOVR,
  MYDB.S_SYM_STR_INTL CTRLSTR,
  MYDB.S_BUSCOMP BC,
  MYDB.S_FIELD FLD,
  MYDB.S_PICKLIST PICK
WHERE
  VIEWRESP.RESP_ID = RESP.ROW_ID AND
  VIEWRESP.VIEW_ID = APPVIEW.ROW_ID AND
  APPVIEW.NAME = VW.NAME AND
  VW.INACTIVE_FLG = 'N' AND
  VW.ROW_ID = VWT.VIEW_ID AND
  VWT.INACTIVE_FLG = 'N' AND
  VWTI.VIEW_WEB_TMPL_ID = VWT.ROW_ID AND
  VWTI.INACTIVE_FLG = 'N' AND
  APP.NAME = VWTI.APPLET_NAME AND
  CTRL.APPLET_ID = APP.ROW_ID AND
  CTRL.INACTIVE_FLG = 'N' AND
  CTRL.FIELD_NAME IS NOT NULL AND
  CTRL.ROW_ID = CTRLOVR.CONTROL_ID (+) AND
  CTRL.CAPTION_REF = CTRLSTR.SYM_STR_KEY (+) AND
  APP.BUSCOMP_NAME = BC.NAME AND
  CTRL.FIELD_NAME = FLD.NAME (+) AND
  FLD.BUSCOMP_ID = BC.ROW_ID AND
  FLD.PICKLIST_NAME = PICK.NAME (+) AND
  VW.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
  APP.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
  (CTRLSTR.REPOSITORY_ID IS NULL OR CTRLSTR.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
  BC.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
  (FLD.REPOSITORY_ID IS NULL OR FLD.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
  (PICK.REPOSITORY_ID IS NULL OR PICK.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
  RESP.NAME IN (:1)
UNION
  SELECT
  RESP.NAME Responsibility,
  VW.NAME ViewName,
  APP.NAME Applet,
  'List' as AppletType,
  BC.NAME BusinessComponent,
  NVL(LSTCOLOVR.DISPLAY_NAME, LSTCOLSTR.STRING_VALUE) Caption,
  LSTCOL.FIELD_NAME Field,
  FLD.PICKLIST_NAME Picklist,
  PICK.TYPE_VALUE LOVType
  FROM
  MYDB.S_APP_VIEW_RESP VIEWRESP,
  MYDB.S_APP_VIEW APPVIEW,
  MYDB.S_VIEW VW,
  MYDB.S_RESP RESP,
  MYDB.S_VIEW_WEB_TMPL VWT,
  MYDB.S_VIEW_WTMPL_IT VWTI,
  MYDB.S_APPLET APP,
  MYDB.S_LIST LST,
  MYDB.S_LIST_COLUMN LSTCOL,
  MYDB.S_LIST_COL_INTL LSTCOLOVR,
  MYDB.S_SYM_STR_INTL LSTCOLSTR,
  MYDB.S_BUSCOMP BC,
  MYDB.S_FIELD FLD,
  MYDB.S_PICKLIST PICK
WHERE
  VIEWRESP.RESP_ID = RESP.ROW_ID AND
  VIEWRESP.VIEW_ID = APPVIEW.ROW_ID AND
  APPVIEW.NAME = VW.NAME AND
  VW.INACTIVE_FLG = 'N' AND
  VW.ROW_ID = VWT.VIEW_ID AND
  VWT.INACTIVE_FLG = 'N' AND
  VWTI.VIEW_WEB_TMPL_ID = VWT.ROW_ID AND
  VWTI.INACTIVE_FLG = 'N' AND
  APP.NAME = VWTI.APPLET_NAME AND
  LST.APPLET_ID = APP.ROW_ID AND
  LST.INACTIVE_FLG = 'N' AND
  LSTCOL.LIST_ID = LST.ROW_ID AND
  LSTCOL.INACTIVE_FLG = 'N' AND
  LSTCOL.FIELD_NAME IS NOT NULL AND
  LSTCOL.ROW_ID = LSTCOLOVR.LIST_COLUMN_ID (+) AND
  LSTCOL.DISPLAY_NAME_REF = LSTCOLSTR.SYM_STR_KEY (+) AND
  APP.BUSCOMP_NAME = BC.NAME AND
  LSTCOL.FIELD_NAME = FLD.NAME (+) AND
  FLD.BUSCOMP_ID = BC.ROW_ID AND
  FLD.PICKLIST_NAME = PICK.NAME (+) AND
  VW.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
  APP.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
  LST.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
  (LSTCOLSTR.REPOSITORY_ID IS NULL OR LSTCOLSTR.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
  (LSTCOLOVR.REPOSITORY_ID IS NULL OR LSTCOLOVR.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
  BC.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
  (FLD.REPOSITORY_ID IS NULL OR FLD.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
  (PICK.REPOSITORY_ID IS NULL OR PICK.REPOSITORY_ID = (SELECT ROW_ID FROM MYDB.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
  RESP.NAME IN (:1);

Though an explanation is not necessarily needed, it allows me to show off that I do understand the SQL.

Consider only the relevant responsibilities that will be provided during execution
Get the view, applet, control, BC, field and drop-down (picklist) information
Consider form and list applets separately and do a union of the query. This is just to improve performance, and keep the individual queries simpler
Start from the responsibilities, get the views tagged to each responsibility. Get the applets in the views, and their BCs
Get the all controls/list columns in the applet, get the corresponding BC fields, and the LOV information

Note that this SQL does not obtain only the relevant controls. For example, a control can be hidden for “Siebel eMedia” application, but still shows up in the query.

Recent Articles