Generating Data to UI mappings in Siebel

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.

Advertisements

Author: sercanbilgic

Current: Eproseed Position: Bigdata Senior Architect Past: Oracle Position: Engineered Systems Architect(Exadata,Exalogic,SuperCluster,Big Data Appliance) Past: Vodafone Position: Middleware Infrastructure Operations Senior Specialist Past: Accenture Position: Senior Developer/Programmer

One thought on “Generating Data to UI mappings in Siebel”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s