EXCEL – 2D to flat

Input Data:-

Product VS Score	Resolution	Sound
HD TV	2	3
LCD TV	3	2

Expected output:-

Product	Category	Score
HD TV	Resolution	2
HD TV	Sound	3
LCD TV	Resolution	3
LCD TV	Sound	2

Create Excel macro and type below code. This will generate the expected output in Sheet2.

Sub Generate_Click()

    Dim oSht1, iSht1 As Worksheet
    Dim iRowNumber As Integer
    Dim iColNumber As Integer
    Dim iEndRowNumber As Integer
    Dim iEndColNumber As Integer
    Dim iOutputRowNumber As Double
    Dim i, j As Integer
    Set oSht1 = Sheet2
    Set iSht1 = Sheet1
    iRowNumber = 2
    iColNumber = 2
    iEndRowNumber = 3
    iEndColNumber = 3
    iOutputRowNumber = 2
    oSht1.Cells(1, 1).Value = "Product"
    oSht1.Cells(1, 2).Value = "Category"
    oSht1.Cells(1, 3).Value = "Score"
    For i = iColNumber To iEndColNumber
        For j = iRowNumber To iEndRowNumber
            oSht1.Cells(iOutputRowNumber, 1).Value = iSht1.Cells(j, 1).Value
            oSht1.Cells(iOutputRowNumber, 2).Value = iSht1.Cells(1, i).Value
            oSht1.Cells(iOutputRowNumber, 3).Value = iSht1.Cells(j, i).Value
             iOutputRowNumber = iOutputRowNumber + 1
        Next j
    Next i

End Sub

Salesforce :- “INVALID_SESSION_ID: Invalid Session ID found in SessionHeader”

 

We have other system integrated with Salesforce using integration users. Recently we across error message after deploying new integration process in production instance. After looking deep into new deployed code, we found that we are using logoff in newly deployed integration.

Adding log off shouldn’t cause problem but if you go thru below then you will understand it is true.

1. Organization wide we have one single IP exposed to outside world.
2. Salesforce ties up the session to user id and ip address from where it is initiated. Salesforce has setting to tie up session id to source IP address. Setup > Security Control > Session Setting. We have this setting false, still salesforce ties up session to IP as organization wide we have single IP exposed.
3. Web application side, we were logging only once and all other instances were reusing the same session id or instance.
4. We did not faced this issue in TEST environment as there wasn’t enough load happening
5. Were able to figure out the issues using below pictorial method.
    

Application Instance consist of following

1. Apps I
1. Login
2. Process
2. Apps 2 –
1. Login
2. Process
3. Log off

We resolved this issue by removing logoff from newly deployed integration. Another approach was to use separate integration user which is not realistic as we will be needing to maintain multiple user and their user id, password and security token.

Siebel - Terminating/blocking user from Siebel application without changing LOGIN ID

 

We change Login ID most of time when we need to block or terminate user from Siebel application. Whereas we can achieve this by configurations as well. In Vanilla Siebel we have field called as "Employment Status", by leveraging this field and using small configuration we can have more granular reason of blocking or termination of account.

To achieve this I have exposed this field on "Employee List More Info View" view as below.

Secondly I have added this field into "Login User" business component along with Search specification as [Employment Status]='Active' . Details are as below

With Employement Status as "Active", I was able to access Siebel properly whereas when change was made to other than "Active" got error message as below.

Note:- Please make sure that SADMIN, other proxy account and integration accounts are set to “Active” before rolling out new change.

Salesforce – Apex DataLoader and Automated migration interface

 

Scenario :- We wanted to build the automated Employee interface which will create/update/terminate User inside Salesforce when changes are done in Organizational HRMS.

 

To build we have following options -

• Use Apex Dataloader
• Build Standalong Java apps and use Salesforce Webservices
• Use of other tools like Apatar, OnDemandInformation etc.

Based on License/development cost and scalability we decided to use Apex Dataloader. If you wanted to use Apex data loader then either you can use the lastRun date to find out which records to interface or stamp status back to source data (Database table). If you decide to stamp status back to source data then dataloader does not provide the functionality to do that (atleast I am not aware :-) ). To achieve we wrote small java program which will use success file and error files generated by loader and will update the source database table.

Here is high level flow.

Flow:-
First run the data loader using configuration
once completed call java program with appropriate connection parameter and log file location. Java program will update the source records as success if found in success file and error if found in error file.

Enjoy :)

How to bypass Signal on Order Object while doing Quote to Order conversion using AutoOrderSalesQuote signal with Dynamic Pricing in Place

 

We have dynamic pricing enabled in our environment with additional custom discounting defined for Quote and we convert Quote to Order just for integration purpose. When we convert quote to order - we are seeing following error message in log file all time. Sometime this stops whole execution.

Log file Error:-
Error invoking service "Context Service" method 'GetRowSetData'
Field: 'Additional Discount Flag' does not exists in definition for business component 'Order Entry – Orders'

GUI Error:-

Note:- Additional Discount Flag is custom field added for one of additional discounting.

Upon analysis we found that CalculatePriceAll signal is invoked and this error occurred.

There are following option to bypass CalculatePriceAll function on Order Entry - Orders which we use as placeholder for integration so no changes are required/expected on Order object.

Option I - AutoOrderSalesQuote uses "QuoteToSalesOrder" as a data map. Navigate to Administration -Application > Data Map Administration and select QuoteToSalesOrder. In Data map component select header and select "Skip Order Management Signals"  on Destination Business component in Advance option.

For us this option I was configured whereas it did not worked as we had customization in Dynamic Pricing.

Option II - Add appropriate condition to skip the signal. Navigate to Administration - Order Management > Signal > Query "CalculatePriceAll" and click on "Lock" > drill down on Workspace

Option III - Modify "PSP Driver Workflow Process" workflow process with following details.
Screen shot before configuration

Configuration
1. Add "Mode" as process property in WF Property
2. I designed more add conditional step with default pointing to "End Step" and conditional branch to "". Condition to use "Mode" = "Quote"

We chose option III as first two options were not working due to customization in pricing and we did use Order object as placeholder for integration.

 

: : :   E n j o y   : : :     :- )

View Siebel Repository details in Siebel Client Application

 

IF you not aware, Siebel Tools works on Siebel's BO, BC, Screen, View concept. E.g. To find where Business component definitions are stored, select a Business component object in Tools and select Help > About View from Tools menu.

Upon selection it will popup the details wherein you will find Business object, business component, views etc as below.

If this information is available in Siebel Tools then we should be able to use this information and display within Siebel Client application. This was needed as we have Business Intelligence team who is somewhat familiar with Siebel Tools details. By making this information available in Siebel Client we are making team's job simpler.

To show one example, I have configured Application object for Siebel Client. To achieve this, I created two applets one for Application and second for screen tabs, added this applets to View,View to newly created Screen and Screen to one of Siebel Sales Enterprise (Application object). Finally add newly created view to user's responsibility. Final outcome will be similar to below screen shot.

Note:- If you use views found using Help > About view, it will not appear in Siebel Client. I tried the same and felt it is internally managed or due to repository views, secondly applet are having special class so web layout does not show anything inside Siebel Tools.

I have attached project export for reference, you job is only adding screens to existing application and view to user’s responsibility. Similarly you can built/expose all Tools objects inside Siebel Client. Only one thing to care – create applets as read only by setting “No Delete”, “No Insert”, “No Merge”, “No Update” to True as well as make views read-only at responsibility level.

Attachment:- Click here to Download Project Sif

 

: : :   E n j o y   :-)     : : : 

Assignment Manager Crash and SBL-GEN-03001: Error allocating DynArrCreate Organization Skill Item

Approximately year back we faced this issue where Assignment Manager was crashing with "SBL-GEN-03001: Error allocating DynArrCreate Organization Skill Items" as error.Upon more analysis I found that this is memory related issue caused by huge orphan records in "S_BU_SKILL_IT". In our environment we are creating Organization Assignment condition and skill items  almost on daily to support business requirement. Process we followed programmatically is delete  organization conditions and recreate organization assignment condition as per latest data.

To be very specific, orphan records are due to cascade delete was set to None on "Organization Skill/Organization Skill Item" instead of "Delete".
Since this happened in production we had following option to choose from.

• Short Term:
  Delete records in S_BU_SKILL_IT which does not have reference in S_BU_SKILL. There is vanilla configuration which does not delete records from Organization Skill Item (S_BU_SKILL_IT) when Organization Skill (S_BU_SKILL) is deleted. 
  
  • Stop Assignment Manager and Batch assignment manager.
  • Build pl/SQL script to delete records wherein there is no reference to s_bu_skill.
  • Once delete is done. Delete rule cache for assignment manager
  • Restart Assignment Manager and Batch Assignment Manager
• Long Term:
  Configuration change to set Cascade delete property to "Delete" for link "Organization Skill/Organization Skill Item"

Due to urgency we took short term approach and created pl/sql process to delete records from s_bu_skill_it and used below code to perform the operation and later on we pushed configuration change as well to resolve issue permanently.

declare
   cursor src is
              select row_id  from siebel.s_bu_skill_it a  where bu_skill_id  not in (select row_id from siebel.s_bu_skill b where b.ROW_ID = a.BU_SKILL_ID) ;
    v_cnt number :=0;
    v_recipients varchar2(200) := 'email_address';
    conn utl_smtp.connection;
    v_err_msg varchar2(200);
begin
     FOR cur1 IN src LOOP
        EXIT WHEN src  %NOTFOUND;
             delete from siebel.s_bu_skill_it where row_id = cur1.row_id;
             v_cnt := v_cnt +1 ;
             if v_cnt > 100000 then
                commit;
                v_cnt :=0;
             end if;
     end loop;
     commit;
commit;
exception
    when others then
        commit;
        v_err_msg :=substr(SQLERRM, 1, 200);
        DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;

Call stack during crash:

sslcshar +0x190dc = SSstring::operator=() +0xc
sslcshar +0x25235 = CCFMiscUtil::GetAssertOnErrorList() +0x25
sslcshar +0x37f61 = scfErrorBase::LogError() +0x91
sslcasgn +0x41a2c = GetWLObjName() +0x9d0c
sslcasgn +0x4492b = StartRules() +0xfab
sssaasgn +0x2245 = CSSAsgnBase::operator=() +0x1095
sssaasgn +0x2f5c = CreateSmiMThreadObj() +0x3bc
sssaasgn +0x57c7 = ReqCompCleanup() +0x27
siebmtsh +0x96ec = SmiCleanupDetTask() +0x118c
siebmtsh +0x273c6 = SmiRegisterThrdCleanUpFunc() +0x2546
kernel32 +0x2f23b = ProcessIdToSessionId() +0x209
Other References : Doc ID 1103948.1

Minimizing downtime during Siebel Production Go-live

 

You might not agree with me but I don’t believe in Zero downtime concept and prefer to follow below method to minimize downtime during production cut over/go-live and to avoid unknown issues that might come in future like additional maintenance of SRF.

Typically on production cutover we perform Dev2Prod which involve stopping application server, exporting repository from source, importing into target instance, DDL Synch, generating browser script and updating browser script on web server . This takes approximate 1-2hr. If there are new indexes/column level changes on big tables then this might take 2-3hrs plus additional time to create external indexes if any. This excludes additional task like EIM,data updates or administrative task.

If we follow simple steps below then we can minimize downtime on production cut-over.

1. Only SRF replacement
- go for only SRF replacement when there are not physical changes to table structure like column changes or indexes and no changes to repository based objects like Integration Object, Workflow policy etc.
- stop production environment, replace srf, generate browser script, update browser script on web server either by restarting web server or using http://hostname/eservice/start.swe?SWECmd=UpdateWebImages&SWEPassword=abcdef (Additional one time administration required)
-Total downtime required – approximately 30-45min

2. SRF replacement and SIF import
- to avoid repository migration along with SRF replacement, you can alternatively export objects sifs and then import into target instance.
- stop application server, replace srf, generate browser script, import sifs, update browser script on web server either by restarting web server or using http://hostname/eservice/start.swe?SWECmd=UpdateWebImages&SWEPassword=abcdef (Additional one time administration required)
-Total downtime required – approximately 45min

3. Repository migration and SRF replacement
- Export Siebel repository ahead of time from source environment
- go for this option when there is changes in repository based objects like Integration Object, Workflow policy, Workflows, Assignment manager changes.
- to identify whether there are changes in such objects or not you can write sql statement as below and execute against source environment to take decision. If those statement leads to DDL Sync then you should perform DDL Sync as well.

select last_upd from S_TABLE where repository_id in (select row_id from s_repository where name='Siebel Repository')
order by last_upd desc

select * from S_COLUMN where TBL_ID in (select row_id from S_TABLE where repository_id in (select row_id from s_repository where name='Siebel Repository'))
order by last_upd desc

select * from s_index where  TBL_ID in (select row_id from S_TABLE where repository_id in (select row_id from s_repository where name='Siebel Repository'))
order by last_upd desc

- to minimize production downtime you can import repository ahead of time using below method and during cut over stop application server, rename Siebel Repository1 to Siebel Repository, generate the browser script and restart web server to update browser script. Alternative you can update web server browser script using http://hostname/eservice/start.swe?SWECmd=UpdateWebImages&SWEPassword=abcdef (Additional one time administration required)

rem
rem    Usage: samp <SiebelHome> <ODBC> <LANG>
rem

set SIEBEL_HOME=D:\Siebel\Tools
set ODBC=SSD Local Db default instance
set LANG=ENU
set FLE=D:\Siebel\Tools\SAMPLE\UTF8\SiebelRepository.dat

rem Prior to New Seed Import, remove ENU records from Sample DB
echo Please press [Enter] if you want to import Repository

echo or Ctrl-C and terminate batch job if not.
pause

%SIEBEL_HOME%\bin\repimexp /a I /g %LANG% /u SADMIN /p SADMIN /c "%ODBC%" /D SIEBEL /M y /R "Siebel Repository1" /F %FLE% /L %SIEBEL_HOME%/sample/UTF8/imprep.log

- Total downtime required - approximately 45-60min

4. Full Dev2Prod process
- this is full migration process which involves export and import of repository, DDL sync, srf replacement, browser script generation and updating browser script on web server.
- Total time requires – upto 2hrs (time varies based on physical changes)

If option 4 is your choice then by keeping only max two repositories in Siebel database speed up import process.