Re-Usable Tools and Utilities

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.

OpenUI_PM_PR_Generator_Blurr

*****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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: