Format DTYPE_PHONE EAI phone challenge - Part 1

This is the followup article to Strip off Number format in DTYPE_PHONE field (No Script) and also Scriptless Siebel Challenge: Phone Numbers in EAI from Siebel Essentials.

I first came across this problem 3 years ago in a production environment. There was a complaint, that the phone numbers that we were sending across to the external system had inconsistent number formatting.

In Siebel, the phone formatting is controlled by the PHONE_FORMAT LOV. If the user enters in the correct number format, the system will store the number in a compressed format, and use the PHONE_FORMAT lookup to apply to the UI.

However if the user enters in a phone number different to the LOV, the system stores the phone number + the new format string.

The root cause of our problem 3 years ago, was that we had an EIM process that created new and updated existing records. Any phone numbers that were updated by EIM, took the literal value, and not the compressed value that the UI stored.

Example
02 1234 4567 // value from EIM
+611234567 // value from UI using default LOV format
+61021234567|(00) 0000 0000 //value with custom format

All these values could exist simultaneously in the same database column, because the physical type is a varchar(40).

We raised the problem with our TAM, and were told that no declarative means exists and needed to use a custom business service.

The solution that i'm about to share, involves no scripting, and can be applied to all sorts of string challenges.

It nicely meets all of Alex's pre-requisites for a non-scripting solution, ie. It dosnt introduce any custom code to the Applets, Application, BCs, BSs or even use obscure user properties.

Here's my thoughts on this challenge

The DTYPE_PHONE field is a proprietry Siebel format, so there are no standard string functions that will single handedly apply the formatting, there are no documented Siebel user properties that will apply the formatting for us, and to add more complexity the challenge, the user may not want the country codes to be sent across.

Heres how we solve it
Conceptully we need to implement a custom string function that does the following

1. Split the TYPE_PHONE into a number and a pattern string
   
2. Have a cursor that iterates through the pattern string
   
   
   
   • If character = 0 then and place the corresponding position on the number string into an Output property
     
   • Otherwise place the non numeric character from the pattern string into an Output property
     
   
   
3. Loop through pattern string and concatenate the Output string until it has finished
   

I've highlighted key design concepts to solve this problem, now since we cannot use any scripting, the above concept seems impossible, but thats what we are all about.

In the next post i will provide the actual technical solution.

SHARE