Siebel UI Field Mapper

Often external interface 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
ADVSIT.S_APP_VIEW_RESP VIEWRESP,
ADVSIT.S_APP_VIEW APPVIEW,
ADVSIT.S_VIEW VW,
ADVSIT.S_RESP RESP,
ADVSIT.S_VIEW_WEB_TMPL VWT,
ADVSIT.S_VIEW_WTMPL_IT VWTI,
ADVSIT.S_APPLET APP,
ADVSIT.S_CONTROL CTRL,
ADVSIT.S_CONTROL_INTL CTRLOVR,
ADVSIT.S_SYM_STR_INTL CTRLSTR,
ADVSIT.S_BUSCOMP BC,
ADVSIT.S_FIELD FLD,
ADVSIT.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 ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
APP.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
(CTRLSTR.REPOSITORY_ID IS NULL OR CTRLSTR.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
BC.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
(FLD.REPOSITORY_ID IS NULL OR FLD.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
(PICK.REPOSITORY_ID IS NULL OR PICK.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.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
ADVSIT.S_APP_VIEW_RESP VIEWRESP,
ADVSIT.S_APP_VIEW APPVIEW,
ADVSIT.S_VIEW VW,
ADVSIT.S_RESP RESP,
ADVSIT.S_VIEW_WEB_TMPL VWT,
ADVSIT.S_VIEW_WTMPL_IT VWTI,
ADVSIT.S_APPLET APP,
ADVSIT.S_LIST LST,
ADVSIT.S_LIST_COLUMN LSTCOL,
ADVSIT.S_LIST_COL_INTL LSTCOLOVR,
ADVSIT.S_SYM_STR_INTL LSTCOLSTR,
ADVSIT.S_BUSCOMP BC,
ADVSIT.S_FIELD FLD,
ADVSIT.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 ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
APP.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
LST.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
(LSTCOLSTR.REPOSITORY_ID IS NULL OR LSTCOLSTR.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
(LSTCOLOVR.REPOSITORY_ID IS NULL OR LSTCOLOVR.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
BC.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository') AND
(FLD.REPOSITORY_ID IS NULL OR FLD.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.S_REPOSITORY WHERE NAME = 'Siebel Repository')) AND
(PICK.REPOSITORY_ID IS NULL OR PICK.REPOSITORY_ID = (SELECT ROW_ID FROM ADVSIT.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.