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!

Skype - Siebel Custom Integration

Want to call one of your contacts/prospects in Siebel without a lot of manual effort? now you can...
Here are a few simple steps of how you could integrate Skype with your siebel application.

Thank you Bernard for sharing this!

Configuration Steps - (for this example I am going to use the contacts applet)

Step 1: Create a custom Business service called 'OS Skype Integration' and assign it to a locked project.

place the below code in the PreCanInvokeMethod event of the Business Service

function Service_PreCanInvokeMethod (MethodName, &CanInvoke)
{
    if(MethodName == "GetField")
    {
        CanInvoke="TRUE";
        return(CancelOperation);
    }
    else if(MethodName == "TransformToSkype")
    {
        CanInvoke="TRUE";
        return(CancelOperation);
    }
    else
    {
        return(ContinueOperation);
    }
}

place the below code in the PreInvokeMethod event of the Business Service

function Service_PreInvokeMethod (MethodName, Inputs, Outputs)
{
    try
    {
        switch (MethodName)
        {
            case "GetField":
                this.GetField(Inputs, Outputs);
                return (CancelOperation);
              
            case "TransformToSkype":
                this.TransformToSkype(Inputs, Outputs);
                return (CancelOperation);      
              
            default:
                return (ContinueOperation);
        }
    }
    catch(e)
    {
        TheApplication().RaiseErrorText(e.toString());
    }
    finally
    {
    }
}

Now that the two methods are defined and ready for use, lets define the logic inside them


Create two new functions as below -

GetField: This method as you can see will get the properties set by the Browser script in the Contact List Applet and will make them available in the Input Property Set of the Business Service.


function GetField(Inputs, Outputs)
{
    var sfield = Inputs.GetProperty("fieldname");
    var sId = Inputs.GetProperty("Id");
    var boName = Inputs.GetProperty("boName");
    var bcName = Inputs.GetProperty("bcName");
    var bo = TheApplication().GetBusObject(boName);
    var bc = bo.GetBusComp(bcName);
    bc.ClearToQuery();
    bc.SetViewMode(AllView);
    bc.ActivateField(sfield);
    bc.SetSearchSpec("Id", sId);
    bc.ExecuteQuery(ForwardOnly);
    if (bc.FirstRecord())
    {
        var svalue = bc.GetFieldValue(sfield);
           Outputs.SetProperty("fieldvalue",svalue);
    }
    else
    {
        Outputs.SetProperty("fieldvalue","");
    }
}

TransformToSkype: This method contains the main string used to make the call. The string includes the Skype exe path and teh Phone Number of the call recipient/Siebel Contact.


So, the string being passed would look something like this "C:\Progra~1\Skype\Phone\skype" /callto:+14152065351. This string is passed to the browser script for execution.

NOTE 1: The target customer base for the Client where I implemented this was for US only, thats why the '+1'. If you wish to make it more general you could check the Country of the contact and derive the country code based on that.

NOTE 2: You could also simply use 'callto:+14152065351' in the below script skiping an extra step(Step2 - Creating the LOV) - Thanks Ranjith for pointing that out.

function TransformToSkype(Inputs, Outputs)
{
    var sWorkphone = Inputs.GetProperty("PhoneNum");
    var sFirst = sWorkphone.charAt(0);
    var sSkypeNum="";
    var sLOVText = TheApplication().InvokeMethod("LookupValue","OS_SKYPE_PATH","Path");
    if(sFirst!="+")
    {
        sSkypeNum = "\"" + sLOVText + "\"" + " /callto:+1" + sWorkphone;
    }
    else
    {
        var sLast = "\n"
        var sSubstr = Clib.strstr(sWorkphone, sLast);
        var sRtn = sWorkphone.replace(sSubstr, "");
        //sSkypeNum = "skype:" + sRtn + "?Call";
        sSkypeNum = "\"" + sLOVText + "\"" + " /callto:" + sRtn;
    }
    Outputs.SetProperty("SkypeNum",sSkypeNum);
}


Step2: Create an LOV as seen in the below screenshot - This would contain the .exe path of Skype.









Step 3: Lock the 'Contact List Applet' and create three custom buttons as shown in the screenshot.(Note: The 'InvokeMethod property for the Skype buttons should be same as the ones in the script')
Make sure the buttons are clickable by using the CanInvokeMethod Applet User property.

You could also get more creative and use a 'Skype Toolbar' in Siebel. Like I have and call the business service from the Command Invoked. The Script would change in that case.(The Browser script from the applet would then be required on the Business Service)

 









Step 4:  Place the below script in the browser script evet Applet_PreInvokeMethod of the contact list applet.

code:
function Applet_PreInvokeMethod (name, inputPropSet)
{
    if(name=="Skype" || name=="SkypeCell" || name=="SkypeHome")
    {
        try
        {
            var serv = theApplication().GetService("OS Skype Integration");
            var inp = theApplication().NewPropertySet();
            var outs = theApplication().NewPropertySet();
            inp.SetProperty("Id", this.BusComp().GetFieldValue("Id"));
            switch(name)
            {
                case "Skype":
                    inp.SetProperty("fieldname","Work Phone #");
                    break;
                case "SkypeCell":
                    inp.SetProperty("fieldname","Cellular Phone #");
                    break;
                case "SkypeHome":
                    inp.SetProperty("fieldname","Home Phone #");
                    break;
            }
            inp.SetProperty("boName", this.BusObject().Name());
            inp.SetProperty("bcName", this.BusComp().Name());
            outs = serv.InvokeMethod("GetField",inp);  
            var svalue = outs.GetProperty("fieldvalue");
           
            inp = null;
            inp = theApplication().NewPropertySet();
            inp.SetProperty("PhoneNum",svalue);
            outs = null;
            outs = theApplication().NewPropertySet();
            outs = serv.InvokeMethod("TransformToSkype",inp);
            svalue="";
            svalue = outs.GetProperty("SkypeNum");
           
            //calls the ActiveXObject and passes the Skype exe path allong with the phone number of the contact
            var wsh = new ActiveXObject("WScript.Shell");
            var oExec = wsh.Exec(svalue);
            wsh=null;
            return ("CancelOperation");
        }
        catch(e)
        {
            alert ("Error Applet_PreInvokeMethod : " + e.toString());
        }
        finally
        {
            inp=null;
            outs=null;
            serv=null;
        }
    }
    return ("ContinueOperation");
}

This script will call the 'OS Skype Integration' and pass the respective Phone numbers for transformation if required.

Step 5: Since we are invoking the Business service from the browser level. The Business service will have to be registered as a client business sevice in the Application User Props
















Step 6: Compile all objects you modified.

Step 7: Compile the browser scripts by using Genb or should I say UltraGenB(A cool new utility created by Jason Le of Impossible Siebel)


Query on any contact in the Contact List Applet and hit one of the buttons....and there you have it...TRING TRING!!!

























Happy Calling!!

Launching iHelp with eScript

Hey Folks,

I had a requirement with one of my clients to have a button on a Form Applet that opens the iHelp.

"My Oracle Support" has a few SR's that are related, and I had tried various things including trapping the method calls that are triggered when clicking on the iHelp button on the Toolbar, then repeating them on my custom button but with no luck.

Oracle support seems to confirm you can't Launch iHelp with Script in Siebel 7.7, but doesn't mention Siebel 8.1 (which probably means you can't do it!)

This however is possible through script. You could use the below code on the button to toggle the iHelp from a custom button.

Code:

function WebApplet_PreInvokeMethod (MethodName)
{

    if(MethodName == "LaunchiHelp")
    {
        var oBS= TheApplication().GetService("Task Assistant UI Service");
        var psInputs = TheApplication().NewPropertySet();
        var psOutputs = TheApplication().NewPropertySet();
        oBS.InvokeMethod("ToggleiHelp", psInputs,psOutputs);
        return (CancelOperation);
    }
}

Your custom button should now function exactly like the iHelp launch button on the toolbar.

Update (thanks to my colleague for sharing this)

I have had to add an update to the code so that it looks like this:

var psInputs = TheApplication().NewPropertySet();

var psOutputs = TheApplication().NewPropertySet();

psInputs.SetProperty("Command", "#14");

var oBS = TheApplication().GetService("Task Assistant UI Service");

oBS.InvokeMethod("ToggleiHelp", psInputs, psOutputs);

Without this parameter there are issues with opening / closing iHelp multiple time from different entities. It results in a Siebel crash and you have to log in again. With the command property it appears to work fine.

Just a word of caution: this command number is likely to be different in different version of Siebel. It is 8.1.1.2 SIA [21215] ENU that I have this working for.

Cheers!

Get NASDAQ Market updates in Siebel

Like most of you Siebel lovers I too like to integrate anything and everything with Siebel. Here is an example of how you can get NASDAQ updates within your Siebel Application.

Here is how you do it!

Steps:

1. Create an applet with a Single control.

2. Add the following code to your single control that you have in your applet.

Caption String Override: (in frame src tags) ="tickerpanel.asp?COOKIE=S~MSFT~~|S~INTC~~|S~NWSA~~|S~QQQQ~~|S~CSCO~~|
S~ATML~~|S~DELL~~|S~NVDA~~|S~RIMM~~|S~MU~~|I~IXIC|I~INDU|I~SPX" name="ticker" scrolling="no" frameborder=0 noresize marginwidth="0" marginheight="0"

3. place this applet in a view and your done!

Stay tuned for a more advanced version of this post!

Cheers!

Create an HTML email in Oracle CRM On Demand

Hey Folks,

For any business, It's very important to keep your customers happy at all times.You need to constantly keep them updated on how their service requests are being processes. Below is one such example

I recently came across a requirement in which my client wanted to have some functionality in which, when a new SR is created, a Service Representative should be able to generate a neatly formatted email with all the required information and send it across to a Customer, so they know that the SR is being looked into, something like a SR Acknowledgment.

Here's what I did for them!

Solution:

Step 1: Build the Report
Create a Narrative Report that will contain all the required fields that is required to send out an email.

In Step 2 on the reports wizard add only a Narrative View

Save the report


Step 2: Create a Weblink and add to the Layout

Create a Weblink field in the Service Request Object.
Edit the weblink properties and add it to your report, note that we are passing the SR number as a parameter in the report, so we get details specific to this record only.

Add this weblink to the Layout assigned to your user role.

Step 3: View your changes
Create  a new SR record or view an existing SR record that has all the required fields you need to successfully send out the email. You should see the email weblink that we created in Step 2 above

Click on the Weblink to launch the report. you will now see your neatly formatted email with all the required details.

Step 4: Add this in Outlook.
In your IE menu bar click File -> Send -> Page by E-mail...

The above step will create new email in Outlook with your formatted report.

 

And you're ready to go!

Cheers!