Archive for the ‘Utility/Tool’ Category

Siebel Repository Extract (SRE) Tool – A Must Have For Siebel Developers

Launching

Pic - Dashboard

Pic – Dashboard

Development Challenge

  • Manually extracting new/modified object details from Siebel repository to prepare Technical Design Document.
  • Time-consuming process – With the increase in number of objects, time taken to extract them also increases proportionally.

Solution

  • Siebel Repository Extract (SRE) Tool
  • SRE Tool will automate the process of extracting Siebel object details from Siebel repository to excel sheets.
  • It will also formats the data, making it ready to be used in your Technical Design Document.

Process Improvement

  • Eliminates the need of manual efforts.
  • Achieves time reduction of 99.29%.

Features

  • Supports 31 most commonly worked upon Siebel Object Types.
  • Supports access to Server, Local or Sample databases through ODBC connections.

Versions Available

Trial vs Full Version

Trial vs Full Version

Download Presentation/User Guide

Download Trial Version

Purchase Full Version

  • To purchase full version (Single User License) of SRE Tool, click SRE Tool – Full Version
  • Limited period 25% discount code – “CRACKERDISC”

In case of any difficulty in downloading/purchasing the tool, please leave a comment.

Siebel – How To Extract Attachments Using EIM

 

 

Scenario

During upgrade process, over 11,000 Opportunities were identified for migration. Also there were attachments associated with these opportunities.

 

Approaches

I short-listed following two ways to pull out the attachment files:

  1. Client-side Business Service
  2. EIM export

After initial POC, I settled down with EIM export option.

Reason – EIM job extracts the attachment files and place them at specified location. Now suppose if there are multiple files with the same name, what will happen? In this scenario, EIM job automatically appends file name with numerals (like 1, 2, 3 and so on) and further saves the file.

But with the client-side scripting, this scenario needs to be handled explicitly.

 

Solution

To download the required IFB file (enclosed in word document), click here >> ExtractOptyAttachments – IFB

Limitation: Only 1000 Row Ids can be specified at maximum in each IFB file. Therefore to extract attachment files for 11,000 opportunities, I had to prepare 11 IFB files and run each of them.

 

Utility Tool – ColumnArranger v1.0

 

Hello Readers

Today I have got another interesting utility – ColumnArranger. It is used to automatically re-arrange the order of columns in MS Excel to a predefined format.

For example, you receive a report for Accounts-Opportunity in MS Excel.
Provided sequence of columns – OPTY_NUM, ACCOUNT_ID, CITY, POSTAL_CODE, COUNTRY, STATE, STATUS.
Expected sequence – ACCOUNT, COUNTRY, STATE, STATUS, CURRENCY, POSTAL_CODE, OPTY_NUM.

In this scenario, ColumnArranger utility comes handy. Instead of re-arranging columns manually, only specify the column header in desired sequence and run an embedded macro.

 

Origin Of The Tool

During EIM Load, we used to receive data extracts from Data Conversion Team. They designed their own SQL and used to send the data in MS Excel sheet. And in parallel, my team was working on the design of EIM Loads and developed the CTL files with data order not in sync with select part of SQL statements. Therefore, data in received sheets could not be fed to the EIM loads directly.

At this point, I developed ColumnArranger tool. Instead of manually ordering the columns in Excel, I utilized it eliminating the manual efforts.

 

Presentation And User Guide

For additional details and user guide, please refer this document >> ColumnArranger – Presentation

 

Tool Setup

To download this tool, click here >> ColumnArranger – Setup

 

Oracle SQL – Remove Trailing Zeroes From Phone Columns

 

Requirement

I designed the sql to extract Contacts from Siebel Database (Oracle Database in this case) including phone columns. The challenge that I faced with phone columns:

  • Trailing zeroes (Used by Siebel to capture phone number format)

My extract was not supposed to contain them.

 

Concept

Siebel puts a Newline character between actual phone data and its format.

Also, in my case, Siebel database was installed on a Unix server. In Unix, Newline character is represented by LineFeed (LF) character (\n), Its ASCII code is 10

 

Solution

I put up three in-built SQL library functions to use as follow:

Select
  PC.WORK_PH_NUM “Full WorkPhone#”,
  Case When INSTR(PC.WORK_PH_NUM,CHR(10)) > 0
     Then SUBSTR(PC.WORK_PH_NUM,0,INSTR(PC.WORK_PH_NUM,CHR(10))-1)
     Else PC.WORK_PH_NUM
  End “Updated WorkPhone#”
From Siebel.S_Contact PC;

 

MS Excel – Finding Repeated Values

 

Scenario

Sometimes it is required to verify a particular column for repeated values in MS Excel sheet. It would be similar to the use of distinct clause in SQL statement.

 

Solution

We can use CountIF function to determine repeated values as shown below:

 

The syntax is: =CountIF (Range, Value to search for) > 1

where Range >> Specified as $A$2:$A$20. It means all cells from Row: 2 to 20 and Column: A.

Value to search for >> Specified as A2. It means value in the cell – A2 should be looked for in above range.

 

Press Enter. Then drag the value of cell B2 to all other cells below it. System will automatically update the formula and value of the cell. All cells with value = TRUE signifies that corresponding column A values has repetitions in the list.