Generating Data to UI mappings in Siebel just got easier!

This is one of those tasks which is fairly simple to do. However, can be very time consuming considering you have to generate a mapping for an entire/multiple repositories. We've all have had to do this at some point, not enjoying it one bit!

Well, here is a code that will save you some time and your sanity :).

The below code generates a screen to Applet, and an Applet to BC mapping which can be then exported to excel.


Screen to Applet - 


select scr.name "Screen Name"
      ,nvl(nvl(ptabi.tab_text, scri.viewbar_text), scr.viewbar_text) "Screen"
      ,scrv.sequence "View Seq"
      ,vw.name "View Name"
      ,vwi.title "View"
      ,vw.busobj_name "Business Object"
      ,vwti.item_num "Item Num"
      ,ap.name "Applet Name"
      ,api.title "Applet"
      ,ap.buscomp_name "Business Component"
from   siebel.s_repository rep
       inner join siebel.s_screen scr on scr.repository_id = rep.row_id
       left outer join siebel.s_screen_intl scri on scri.screen_id = scr.row_id and scri.repository_id = rep.row_id and scri.name = 'ENU-STD'
       inner join siebel.s_screen_view scrv on scrv.screen_id = scr.row_id and scrv.repository_id = rep.row_id
       inner join siebel.s_application appl on rep.row_id = appl.repository_id
       left outer join siebel.s_page_tab ptab on ptab.application_id = appl.row_id and ptab.repository_id = rep.row_id and ptab.screen_name = scr.name
       left outer join siebel.s_page_tab_intl ptabi on ptabi.page_tab_id = ptab.row_id and ptabi.repository_id = rep.row_id and ptabi.name = 'ENU-STD'
       inner join siebel.s_view vw on vw.name = scrv.view_name and vw.repository_id = rep.row_id
       left outer join siebel.s_view_intl vwi on vwi.view_id = vw.row_id and vwi.repository_id = rep.row_id and vwi.name = 'ENU-STD'
       inner join siebel.s_view_web_tmpl vwt on vwt.view_id = vw.row_id and vwt.repository_id = rep.row_id
       left outer join siebel.s_view_wtmpl_it vwti on vwti.view_web_tmpl_id = vwt.row_id and vwti.repository_id = rep.row_id
       inner join siebel.s_applet ap on ap.name = vwti.applet_name and ap.repository_id = rep.row_id
       left outer join siebel.s_applet_intl api on api.applet_id = ap.row_id and api.repository_id = rep.row_id and api.name = 'ENU-STD'
where  rep.name = 'Siebel Repository'
and    appl.name = 'Siebel Power Communications'
and    nvl(rep.inactive_flg,'N') = 'N'
and    nvl(scr.inactive_flg,'N') = 'N'
and    nvl(scri.inactive_flg,'N') = 'N'
and    nvl(scrv.inactive_flg,'N') = 'N'
and    nvl(vw.inactive_flg,'N') = 'N'
and    nvl(vwi.inactive_flg,'N') = 'N'
and    nvl(vwt.inactive_flg,'N') = 'N'
and    nvl(vwti.inactive_flg,'N') = 'N'
and    nvl(ap.inactive_flg,'N') = 'N'
and    nvl(api.inactive_flg,'N') = 'N'
union
select scr.name "Screen Name"
      ,nvl(nvl(ptabi.tab_text, scri.viewbar_text), scr.viewbar_text) "Screen"
      ,scrv.sequence "View Seq"
      ,vw.name "View Name"
      ,vwi.title "View"
      ,vw.busobj_name "Business Object"
      ,vwti.item_num "Item Num"
      ,apta.name "Applet Name"
      ,api.title "Applet"
      ,apta.buscomp_name "Business Component"
from   siebel.s_repository rep
       inner join siebel.s_screen scr on scr.repository_id = rep.row_id
       left outer join siebel.s_screen_intl scri on scri.screen_id = scr.row_id and scri.repository_id = rep.row_id and scri.name = 'ENU-STD'
       inner join siebel.s_screen_view scrv on scrv.screen_id = scr.row_id and scrv.repository_id = rep.row_id
       inner join siebel.s_application appl on rep.row_id = appl.repository_id
       left outer join siebel.s_page_tab ptab on ptab.application_id = appl.row_id and ptab.repository_id = rep.row_id and ptab.screen_name = scr.name
       left outer join siebel.s_page_tab_intl ptabi on ptabi.page_tab_id = ptab.row_id and ptabi.repository_id = rep.row_id and ptabi.name = 'ENU-STD'
       inner join siebel.s_view vw on vw.name = scrv.view_name and vw.repository_id = rep.row_id
       left outer join siebel.s_view_intl vwi on vwi.view_id = vw.row_id and vwi.repository_id = rep.row_id and vwi.name = 'ENU-STD'
       inner join siebel.s_view_web_tmpl vwt on vwt.view_id = vw.row_id and vwt.repository_id = rep.row_id
       left outer join siebel.s_view_wtmpl_it vwti on vwti.view_web_tmpl_id = vwt.row_id and vwti.repository_id = rep.row_id
       inner join siebel.s_applet ap on ap.name = vwti.applet_name and ap.repository_id = rep.row_id
       inner join siebel.s_applet_toggle apt on apt.applet_id = ap.row_id and apt.repository_id = rep.row_id
       inner join siebel.s_applet apta on apta.name = apt.applet_name and apta.repository_id = rep.row_id
       left outer join siebel.s_applet_intl api on api.applet_id = apta.row_id and apta.repository_id = rep.row_id and api.name = 'ENU-STD'
where  rep.name = 'Siebel Repository'
and    appl.name = 'Siebel Power Communications'
and    nvl(rep.inactive_flg,'N') = 'N'
and    nvl(scr.inactive_flg,'N') = 'N'
and    nvl(scri.inactive_flg,'N') = 'N'
and    nvl(scrv.inactive_flg,'N') = 'N'
and    nvl(vw.inactive_flg,'N') = 'N'
and    nvl(vwi.inactive_flg,'N') = 'N'
and    nvl(vwt.inactive_flg,'N') = 'N'
and    nvl(vwti.inactive_flg,'N') = 'N'
and    nvl(ap.inactive_flg,'N') = 'N'
and    nvl(api.inactive_flg,'N') = 'N'
order by "Screen"
        ,"View Seq"
        ,"View Name"
        ,"Item Num"
        ,"Applet Name"

Output looks like -

Applet to BC mapping -

select  "Applet Name"
,"BC Name"
,"BC Field"
,"Required"
,"Calculated"
,"Calculated Value"
,"Join Name"
,"Table"
,"Column"
,"Data Type"
,"Length"
,"Multi-valued"
,"MV Link"
,"Pick List"
,"LOV Name"
,min("Caption") "Caption"
,"Display Order"
from (
select ap.name "Applet Name"
      ,bc.name "BC Name"
      ,fld.name "BC Field"
      ,fld.required "Required"
      ,fld.calculated "Calculated"
      ,fld.calcval "Calculated Value"
      ,fld.join_name "Join Name"
      ,(case when fld.mvlink_name is null then nvl(nvl(jotab.name, fld.join_name), case when fld.calculated = 'Y' then null else bc.table_name end) else null end) "Table"
      ,fld.col_name "Column"
      ,fld.type "Data Type"
      ,(case when fld.prec_num is null then to_char(fld.textlen)
             else to_char(fld.prec_num) || to_char(case when fld.scale is null or fld.scale = 0 then '' else ',' || fld.scale end)
        end) "Length"
      ,fld.multi_valued "Multi-valued"
      ,fld.mvlink_name "MV Link"
      ,pl.name "Pick List"
      ,pl.type_value "LOV Name"
      ,coi.caption "Caption"
      ,co.sequence "Display Order"
from   siebel.s_control co
       inner join siebel.s_control_intl coi on coi.control_id = co.row_id and coi.name = 'ENU-STD'
       inner join siebel.s_applet ap on co.applet_id = ap.row_id
       inner join siebel.s_buscomp bc on ap.buscomp_name = bc.name
       inner join siebel.s_field fld on fld.name = co.field_name and fld.buscomp_id = bc.row_id
       inner join siebel.s_repository rep on bc.repository_id = rep.row_id
       left outer join siebel.s_join jo on jo.buscomp_id = fld.buscomp_id and fld.join_name = jo.name
       left outer join siebel.s_table jotab on jotab.name = jo.dest_tbl_name and jotab.repository_id = rep.row_id
       left outer join siebel.s_picklist pl on fld.picklist_name = pl.name and pl.repository_id = rep.row_id
where  rep.name = 'Siebel Repository'
and    ap.repository_id = rep.row_id
and    co.repository_id = rep.row_id
and    bc.repository_id = rep.row_id
and    fld.repository_id = rep.row_id
and    nvl(co.inactive_flg,'N') = 'N'
and    nvl(ap.inactive_flg,'N') = 'N'
and    nvl(bc.inactive_flg,'N') = 'N'
and    nvl(fld.inactive_flg,'N') = 'N'
and    nvl(rep.inactive_flg,'N') = 'N'
and    nvl(jo.inactive_flg,'N') = 'N'
union all
select ap.name "Applet Name"
      ,bc.name "BC Name"
      ,fld.name "BC Field"
      ,fld.required "Required"
      ,fld.calculated "Calculated"
      ,fld.calcval "Calculated Value"
      ,fld.join_name "Join Name"
      ,(case when fld.mvlink_name is null then nvl(nvl(jotab.name, fld.join_name), case when fld.calculated = 'Y' then null else bc.table_name end) else null end) "Table"
      ,fld.col_name "Column"
      ,fld.type "Data Type"
      ,(case when fld.prec_num is null then to_char(fld.textlen)
             else to_char(fld.prec_num) || to_char(case when fld.scale is null or fld.scale = 0 then '' else ',' || fld.scale end)
        end) "Length"
      ,fld.multi_valued "Multi-valued"
      ,fld.mvlink_name "MV Link"
      ,pl.name "Pick List"
      ,pl.type_value "LOV Name"
      ,coi.display_name "Caption"
      ,co.sequence "Display Order"
from   siebel.s_list li
       inner join siebel.s_applet ap on li.applet_id = ap.row_id
       inner join siebel.s_list_column co on co.list_id = li.row_id
       left outer join siebel.s_list_col_intl coi on coi.list_column_id = co.row_id and coi.name = 'ENU-STD'
       inner join siebel.s_buscomp bc on ap.buscomp_name = bc.name
       inner join siebel.s_field fld on fld.name = co.field_name and fld.buscomp_id = bc.row_id
       inner join siebel.s_repository rep on bc.repository_id = rep.row_id
       left outer join siebel.s_join jo on jo.buscomp_id = fld.buscomp_id and fld.join_name = jo.name
       left outer join siebel.s_table jotab on jotab.name = jo.dest_tbl_name and jotab.repository_id = rep.row_id
       left outer join siebel.s_picklist pl on fld.picklist_name = pl.name and pl.repository_id = rep.row_id
where  rep.name = 'Siebel Repository'
and    li.repository_id = rep.row_id
and    ap.repository_id = rep.row_id
and    co.repository_id = rep.row_id
and    bc.repository_id = rep.row_id
and    fld.repository_id = rep.row_id
and    nvl(li.inactive_flg,'N') = 'N'
and    nvl(co.inactive_flg,'N') = 'N'
and    nvl(ap.inactive_flg,'N') = 'N'
and    nvl(bc.inactive_flg,'N') = 'N'
and    nvl(fld.inactive_flg,'N') = 'N'
and    nvl(rep.inactive_flg,'N') = 'N'
and    nvl(jo.inactive_flg,'N') = 'N'
)
group by  "Applet Name"
,"BC Name"
,"BC Field"
,"Required"
,"Calculated"
,"Calculated Value"
,"Join Name"
,"Table"
,"Column"
,"Data Type"
,"Length"
,"Multi-valued"
,"MV Link"
,"Pick List"
,"LOV Name"
,"Display Order"
order by "Applet Name"
        ,"BC Name"
        ,"MV Link" desc
        ,"Table"
  ,"Display Order"

Output looks like -

So there you go...you could later consolidate both to have a full UI to Data level mapping.

Oh! Almost forgot, Your welcome ;)

Cheers!