Here, I am sharing some of the Re-Usable tools/utilities.
Though I will not share the source code, Readers can request for demo versions of the same for their personal usage only.
***Worlds First*** Automatic PM/PR and PL file generator for OpenUI
Readers,
I am happy to announce the first of the kind Automatic PM,PR,CustomPL,CSS generator for OpenUI.
This tool takes the inputs from the users on the required functionality in an applet or across application and generates the required files in a jiffy… typically any new feature will take 1 or 2 days for a normal developer to code and test. With this tool, it takes less then a minute to do the same, drastically reducing the coding time.
*****Siebel Backend query on calculated fields
Another Best Practice recommendation is to have minimum number of calculated fields which contains the “Sum” or “Count” in them. These needs Re-firing at DB End and impacts the performance of the application.
Sharing the backend SQL for the same.
SELECT
B.NAME,
‘~’,
A.NAME,
‘~’,
A.CALCVAL
FROM
SIEBEL.S_FIELD A,
SIEBEL.S_BUSCOMP B
WHERE
A.BUSCOMP_ID IN (SELECT B.ROW_ID FROM SIEBEL.S_BUSCOMP B) AND
B.ROW_ID = A.BUSCOMP_ID AND
A.CALCULATED = ‘Y’ AND
(A.CALCVAL LIKE ‘%Sum%’ OR A.CALCVAL LIKE ‘%Count%’);
OUTPUT TO E:\TEMP\Calculated_Field.TXT
*****Siebel Backend Query to Find number of Force Active Fields in BC
Started looking into some best practices violation in the repository. So build a query which helps to identify the Number of Force Active Fields in a Buscomp. My personal recommendation is to have <30 Force Active fields in a Buscomp. But depends on BC to BC and Business case.
SELECT
B.NAME,
COUNT(A.FORCEACTIVE) AS NUM_OF_FORCEACTIVE
FROM
SIEBEL.S_FIELD A,
SIEBEL.S_BUSCOMP B
WHERE
A.BUSCOMP_ID = B.ROW_ID AND
A.BUSCOMP_ID IN (SELECT B.ROW_ID FROM SIEBEL.S_BUSCOMP B)
GROUP BY
A.BUSCOMP_ID,
B.NAME;
OUTPUT TO E:\TEMP\FORCEACTIVE2.TXT
****Local DBF Record Count and Size
Sharing few SQL’s that can be used to get the Record count and Tablewise Memory size usage Report.
Very handy in case u r planning to reduce the Local DBF size.
==========================================================
SELECT
T.NAME, T.TYPE,TABLE_ID, CREATOR,TABLE_NAME,TABLE_TYPE,COUNT,FIRST_PAGE,LAST_PAGE,PRIMARY_ROOT,FIRST_EXT_PAGE,
LAST_EXT_PAGE, TABLE_PAGE_COUNT, EXT_PAGE_COUNT,PAGE_MAP_START
FROM
SYS.SYSTABLE S,
SIEBEL.S_TABLE T
WHERE
TABLE_TYPE=’BASE’ AND
CREATOR=101 AND
S.TABLE_NAME = T.NAME AND
T.TYPE NOT IN (‘Interface’, ‘Repository’, ‘Virtual Table’, ‘Warehouse’)
ORDER BY
COUNT DESC,
TABLE_NAME ASC;
OUTPUT TO
E:\temp\s_tables_count_After.txt;
====================================================================================================================
SELECT
S.TABLE_NAME AS TABLE_NAME,
CAST(S.COUNT AS DOUBLE) AS COUNT,
(TABLE_PAGE_COUNT + EXT_PAGE_COUNT)*(SELECT DB_PROPERTY ( ‘PageSize’ ))/(1024*1024) as TABLESIZE,
IF COUNT > 0 THEN ((TABLE_PAGE_COUNT + EXT_PAGE_COUNT) * (SELECT DB_PROPERTY ( ‘PageSize’ )))/COUNT ELSE NULL ENDIF AS BYTESPERROW
FROM
SYS.SYSTABLE S,
SIEBEL.S_TABLE T
WHERE
TABLE_TYPE=’BASE’ AND
CREATOR=101 AND
S.TABLE_NAME = T.NAME AND
T.TYPE NOT IN (‘Interface’, ‘Repository’, ‘Virtual Table’, ‘Warehouse’)
ORDER BY
COUNT DESC,
TABLE_NAME ASC;
OUTPUT TO
E:\temp\s_tables_size_After.txt;
==============================================================
*****Siebel / DOS : Script to clone files (SPF Cloning as example )
You can use the following command to clone single file and give it different names.
The Names can be read from a .txt file
Code snippet :
@echo off
FOR /F “tokens=*” %%i IN (C:\Profile\Names.txt) DO (
@ECHO %%i
Copy “c:\Profile\AVR12345.spf” c:\Profile\New\”%%i”
)
This can be used to clone the Siebel Profile Files and replicate it to other users.
Once the SPF files are created for the new user, then copy them and replace it for all the other users. This is perticularly useful when we want to deploy this SPF for some specific set of users.
****Siebel : Load data from Excel through UI
You can import the data into Siebel by a click of a button. 😉
Basically you need to use the Excel COM Objects and import them.
Sample Excel :LoadTest.xlsx
| Product Name | Language | Code |
| Penalty Charges | Arabic (Saudi Arabia) | ARA |
| EOF |
Sample code below :
function WebApplet_PreInvokeMethod (MethodName)
{
if (MethodName == “AVRImportRecords”)
{
Try
{
var i =2;
var ExcelApp = COMCreateObject(“Excel.Application”);
ExcelApp.Workbooks.Open(“C:\\LoadTest.xlsx”);
ExcelApp.Visible = false;
var oBOLdVal = TheApplication().GetBusObject(“Admin ISS Product Definition”);
var oBCLdVal = oBOLdVal.GetBusComp(“Internal Product Translation”);
var oBCLdValParent = oBOLdVal.GetBusComp(“Internal Product – ISS Admin”);
while(ExcelApp.ActiveSheet.Cells(i,1).Value!= “EOF”)
{
var ProductName = ExcelApp.ActiveSheet.Cells(i,1).Value;
var LanguageName = ExcelApp.ActiveSheet.Cells(i,2).Value;
var Code = ExcelApp.ActiveSheet.Cells(i,3).Value;
var Description = ExcelApp.ActiveSheet.Cells(i,4).Value;
with(oBCLdValParent)
{
SetViewMode(“AllView”);
InvokeMethod(“SetAdminMode”,”TRUE”);
ActivateField(“Name”);
ClearToQuery();
SetSearchSpec(“Name”,ProductName);
ExecuteQuery();
}
if(oBCLdValParent.FirstRecord())
{
var ProductId = oBCLdValParent.GetFieldValue(“Id”);
with(oBCLdVal)
{
NewRecord(NewAfter);
var bcPickParentProd = oBCLdVal.GetPicklistBusComp(“Language Code”);
bcPickParentProd.SetViewMode(AllView);
bcPickParentProd.ActivateField(“Language Code”);
bcPickParentProd.ClearToQuery();
bcPickParentProd.SetSearchSpec(“Language Code”,Code);
bcPickParentProd.ExecuteQuery();
if(bcPickParentProd.FirstRecord())
{
bcPickParentProd.Pick();
}
bcPickParentProd = null;
SetFieldValue(“Product Id”,ProductId);
WriteRecord();
}
}
i=i+1;
}
}// try
catch(e)
{
throw(e);
return(CancelOperation);
}
finally
{
ExcelApp.Quit();
ExcelApp = null;
oBOLdVal = null;
oBCLdVal = null;
oBCLdValParent = null;
}
return(CancelOperation);
} // method end
return(ContinueOperation);
} // function end
Ragha's Siebel Blog 