sql update insert space before caps and capitalize first charachter

I was loading up EAILoolup Values for use in a interation module I had. The external values where strings without spaces and first letter of each word was capitalized.

 

Like this

 

reopenedOnClientRequest.

 

Its equivalent Siebel value to be show was

 

Reopened On Client Request.

 

So I figured all I needed was an SQL update statement to replace a space before each capitalized letter and make the first letter caps…

 

select initcap(regexp_replace(EXT_VALUE,'([^^])([A-Z])','\1 \2')) from S_EAI_LOOKUPMAP where LOOKUP_TYPE = 'ClaimStatus’

 

This did the trick !

4th Generation CRM system

I found this page on the net….it attempts to define a 4^th generation CRM system.

Today's Customer Relationship Management (CRM) System

It's impossible to state precisely what customer relationship management (CRM) means to everyone. The term has been applied to almost every element of business that even remotely interacts with a customer.

In its infancy, CRM systems were a series of mainframe or server-based applications specific to sales, marketing and support business functions.

The applications were lightweight by today's standards and did little more than capture and file critical data. But as cultural boundaries within organizations weakened, individual collections of information gave way to sophisticated applications that could span business functions. By doing so, these applications created the vision of a single view of the customer.

For the first time, organizations could track and analyze shifting customer needs, link marketing campaigns to sales results, and monitor sales activities for improved forecasting accuracy and manufacturing demand.

CRM's Evolution
CRM has evolved since its earliest incarnation, originally driven by an inside-out focus, through three phases of evolution: technology, integration and process. Recently have we seen a major leap forward to a fourth phase: customer-driven CRM —an outside-in approach that has intriguing financial promise.

1. Technology: In its earliest incarnation, CRM meant applying automation to existing sales, marketing, support and channel processes as organizations attempted to improve communications, planning, opportunity and campaign management, forecasting, problem solving, and to share best practices. To some degree, it worked. However, automating poorly performing activities or processes rarely improves the quality of the outcome. So, for the most part, the quality of the return on investment (ROI) was meager—if measurable at all. The promise of the technology was there, but few organizations were realizing the pinnacle of performance. The metric of success was increased efficiency in sales, marketing, support and channel processes.

2. Integration: By developing cross-functional integration, supported by data warehousing and shared roles and responsibilities, organizations began to create a customized view of the customer. Support issues, Web hits, sales calls and marketing inquiries started building a deeper understanding of each customer and allowed aggressive organizations to adapt their tactics to fit individual needs. Integration focused around two primary components:

• Make it easier to do business with the seller: Expected benefits are to improve customer retention and lower support costs.
   
• Predictive modeling: Data mining of an aggregate of corporate knowledge and the customer contact experience was used to improve operational and sales performance. By applying complex algorithms to a history of purchasing or inquiry characteristics, it became practical to predict the demands of individual customers. Up-selling, cross-selling, even the ability to preempt potential problems, was now possible for all customer-facing representatives. Expected benefits are to have better cross-selling/up-selling and improved product offerings or delivery.

3. Process: By rethinking the quality and effectiveness of customer-related processes, many organizations began to eliminate unnecessary activities, improve outdated processes, and redesign activities that had failed to deliver the desired outcomes. Then, by re-creating the process through an understanding of the capabilities of the technology, the outcomes were more predictable and the promises for a meaningful ROI more substantial and realistic. The metric of success became the improved effectiveness in serving the customer.

Thus far, almost everything about CRM has focused on improving the effectiveness and efficiency of the seller's organization.

Organizations have evolved from sales representatives working from paper notebooks, or a card system, to a tightly integrated network that sees movement in sales activity, predicts product demand on manufacturing, and manages the logistics of complex teams to serve the buyer and seller.

Marketing, support services, channel management, revenue management, resource allocation/management, forecasting, manufacturing, logistics and even research and development have all seen the benefits of a well-designed CRM strategy.

However, the past decade of CRM and its associated improvements have been based on three assumptions:

1. The past would be a logical foundation to predict future customer needs and profitability.

2. Demand for traditional value propositions would remain constant.

3. Better customer relationships would deter attrition.

All three of these assumptions have failed—or at least become unstable—in a post-September 11 environment.

Today we know that:

1. Historical purchases or inquiries are not a clear indication of future needs as buyers are rapidly redefining requirements to satisfy their current business, market or shareholder demands.

2. Value propositions are changing in highly competitive markets as sellers are working aggressively to reestablish structural bonds.

3. Driven by sensitive financial markets, buyers move to whichever supplier can provide the best aligned, most cost effective solution that promises to stabilize, or improve, their business performance.

These factors are driving CRM into a fourth phase.

Customer-Driven CRM—The Fourth Phase
Today, revenue performance has become the central theme for CRM as organizations seek to achieve and maintain expected financial results. Leading executives are asking:

• Which of my customers have the potential for a high-profit, sustainable relationship?
   
• What defines profitable and unprofitable customer segments?
   
• What must change to realize that optimal potential?
   
• Where's my opportunity for growth?
   
• Where's my risk for loss?
   
• Am I making the right decisions related to balancing acquisition, cross-selling and upselling—and for the right customer groups?

The epiphany isn't in the questions themselves, but in the fact that we're asking them after a decade of CRM investments—investments intended to provide just those very answers.

It is important to understand that a disruptive change has occurred causing large segments of customer organizations to reassess many of their basic needs, values and assumptions.

Research indicates that this event was triggered by the uncertain complexities of the post-September 11th world. Organizations are now challenging everything from how they create value, to how they serve their markets, to how they meet shareholder expectations. It is the answers to these questions that create the framework for phase four CRM.

Without a deep understanding of what's going on in the customer's head—specifically what will influence buying behavior—it is difficult to establish customer strategies that mutually serve the needs and expectations of the buyer and seller communities.

Understanding the Difference

In the past, CRM has followed a basic balanced scorecard technique involving four categories: customer, financial, operations, and people.

From an inside-out perspective, organizations first analyzed the needs and capabilities of operations and their people to determine what could be delivered to the customer.

From that, they drew conclusions and predictions to determine the impact on the financial category.

As this has changed, so have the priorities. Now the focus is first on the customers:

• What will they buy, when, why and for how much?
   
• What creates value for them, and does this create a structural bond?
   
• What services can we perform that merit premium margins?
   
• Can we establish a new market segmentation strategy focused on potential profitability and willingness to purchase?
   
• Do we understand their business drivers, financial metrics, buying process and decision criteria?

Customer driven CRM means that organizations first understand the customer, then move inward to operations.

Within the context of the customer, the systems and infrastructure capabilities needed to serve those customers and segmentation-based requirements must be reassessed.

Next, it's imperative to explore the skills and competency requirements for the people component of the CRM design.

A decade of CRM has taught us that nothing happens until your people interact with the customer in a manner consistent with new CRM customer strategies and systems.

And, finally, you should be well positioned to apply predictive modeling algorithms to establish a financial model with exceptional accuracy.

Not an easy task, but case studies are proving financial predictions that can demonstrate account-level forecasting with over 80 percent accuracy.

Summary

Developing a CRM strategy isn't an easy task. Complex organizational design, comprehensive technologies and ever-changing customer demands are just the beginning. The lessons learned are monumental but we know that the promises of customer driven CRM are worth the journey.

Here's a simple framework for fourth-generation CRM:

• Focus on financial results: Learn how to identify existing profitable customer segments and determine what will establish a profit-based profile for moving forward. Then develop the business requirements to support sustained, and structurally bonded, relationships.
  
   
• Find cost effective alternatives for nonbuyers or low-margin customers: Not all customer relationships are profitable and very few companies can afford to pay to deliver an equal level of services. Control costs and save your best resources for premium accounts—while working to bring low performers into an acceptable profit portfolio.

 

 

Testing Webservices outside Siebel / Testing Inbound Siebel Webservices - using WGET

I am a Siebel EAI developer. But sometimes I feel that Siebel does not provide the freedom to properly play around with webservice settings, or to test them properly. In most projects, we use a third party tool like SOAP UI or XML SPY to test inbound Siebel webservices. Sometimes when an outbound webservice call from Siebel fails, we try to reproduce the error from SOAP UI to try and find out if there is something wrong in the Siebel configuration. While most developers love the fact that SOAP UI is free and can consume  WSDLS and generate SOAP files, I find it terribly slow. Also, when working on client machines via an RDP/MSTSC connection, I need quick results and cannot wait for SOAP UIs resource hungry interface to load up. Altova XML Spy is faster, but its not free.

 

I set out to find another way to test webservices without using and costly or resource hungry tool. And I found a solution on Unix.

 

Unix developers are well aware of and have been using a command called wget to test http and soap interfaces. Wget is a powerful unix command which can be used to download content, post Http requests, and now even SOAP requests, as I found out. If you have a valid SOAP request, all you need is to send it across to a webservice/url, you can use Wget to do this.  This command has been ported to windows here.I have been using this trick for months now, and it has saved me looaadss of time.

 

Essentially, a SOAP request contains 3 parts

 

·         SOAP Header

·         SOAP Body

·         RAW Parameters / SOAP Parameters

 

The  SOAP Header and Body is as name suggests, contains schema information and data. The SOAP parameters contains additional info as to which url is to be hit and what is the format. Also there is one parameter for length, which contains the length of the soap message (header + body) in char counts. So, the length of the soap message has to be sent.

 

At Siebelunleashed, Neel explains how to trigger outbound webservice from Siebel. I will explain how to do the same thing from outside Siebel.  The example here again uses the GeoIPservice.

 

If you go this link, you will see the actual SOAP request for the service.

 

POST /geoipservice.asmx HTTP/1.1

Host: www.webservicex.net

Content-Type: text/xml; charset=utf-8

Content-Length: length

SOAPAction: "http://www.webservicex.net/GetGeoIP"

 

<?xml version="1.0" encoding="utf-8"?>

<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

  <soap:Body>

    <GetGeoIP xmlns="http://www.webservicex.net">

      <IPAddress>string</IPAddress>

    </GetGeoIP>

  </soap:Body>

</soap:Envelope>

 

Here, the section in blue is the SOAP/RAW parameter section. And the length highlighted in bold has to contain the length of the soap request. After we frame this entire request, we have to send it across as an HTTP request.

 

Here is what we do.

 

1.     Create a folder on your desktop. Download wget for windows. Don’t worry, its free and safe J

2.     Create three files in your folder

·         Req.xml -  this will contain the soap message

·         url.txt – this will contain the url

·         send.bat – we will automate this in a bat file.

3.     Open up req.xml in notepad and enter this.

 

<?xml version="1.0" encoding="utf-8"?>

<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

  <soap:Body>

    <GetGeoIP xmlns="http://www.webservicex.net">

      <IPAddress>216.239.51.99</IPAddress>

    </GetGeoIP>

  </soap:Body>

</soap:Envelope>

 

The ip here is that of Google. We shall see where google’s servers are located.

 

4.      Open up url.txt and enter this

 

http://www.webservicex.net/geoipservice.asmx

 

You will get this address from the WSDL. Open up the WSDL and look for a section called http:address

 

<wsdl:port name="GeoIPServiceHttpPost" binding="tns:GeoIPServiceHttpPost">

  <http:address location="http://www.webservicex.net/geoipservice.asmx" />

 

 

5.     Open up the send.bat file in notepad and enter this

wget  --input-file=url.txt   -nv --post-file=req.xml  --output-document=resp.xml   --header="Content-Type: text/xml" --header="SOAPAction: "http://www.webservicex.net/GetGeoIP"

 

notepad resp.xml

 

6.     Your folder should now contain 4 files. Execute send.bat, either by double clicking or from run command.

 

In a moment, the SOAP request will be sent across and the response should open up in notepad.

 

<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetGeoIPResponse xmlns="http://www.webservicex.net"><GetGeoIPResult><ReturnCode>1</ReturnCode><IP>216.239.51.99</IP><ReturnCodeDetails>Record Found</ReturnCodeDetails><CountryName>UNITED STATES</CountryName><CountryCode>US</CountryCode></GetGeoIPResult></GetGeoIPResponse></soap:Body></soap:Envelope>

 

 

The response shows us that the 216.239.51.99  is in UNITED STATES.

 

When we run the bat file, wget takes the req.xml file and adds the header parameters we specified.

 

wget  --input-file=url.txt   -nv --post-file=req.xml  --output-document=resp.xml   --header="Content-Type: text/xml" --header="SOAPAction: "http://www.webservicex.net/GetGeoIP"

 

Here the parameters are Content-Type and SOAPAction. Inaddition, WGET also adds the content length required for the request.  WGET provides a debug option to see the complete request going out. Just add –debug in the beginning like this

 

wget  --debug --input-file=url.txt   -nv --post-file=req.xml  --output-document=resp.xml   --header="Content-Type: text/xml" --header="SOAPAction: "http://www.webservicex.net/GetGeoIP"

 

You will see the response with debug information like this.

 

C:\test>wget  --debug --input-file=url.

txt   -nv --post-file=req.xml  --output-document=resp.xml   --header="Content-Ty

pe: text/xml" --header="SOAPAction: "http://www.webservicex.net/GetGeoIP"

Setting --input-file (input) to url.txt

Setting --no (verbose) to 0

Setting --post-file (postfile) to req.xml

Setting --output-document (outputdocument) to resp.xml

Setting --header (header) to Content-Type: text/xml

Setting --header (header) to SOAPAction: http://www.webservicex.net/GetGeoIP

DEBUG output created by Wget 1.11.4 on Windows-MSVC.

 

Loaded url.txt (size 44).

seconds 0.00, Caching www.webservicex.net => 209.162.186.60

seconds 0.00, Created socket 1932.

Releasing 0x00985058 (new refcount 1).

 

---request begin---

POST /geoipservice.asmx HTTP/1.0

User-Agent: Wget/1.11.4

Accept: */*

Host: www.webservicex.net

Connection: Keep-Alive

Content-Type: text/xml

Content-Length: 370

SOAPAction: http://www.webservicex.net/GetGeoIP

 

---request end---

[writing POST file req.xml ... done]

 

---response begin---

HTTP/1.1 200 OK

Connection: keep-alive

Date: Tue, 15 Jun 2010 08:12:10 GMT

Server: Microsoft-IIS/6.0

X-Powered-By: ASP.NET

X-AspNet-Version: 2.0.50727

Cache-Control: private, max-age=0

Content-Type: text/xml; charset=utf-8

Content-Length: 519

 

---response end---

Registered socket 1932 for persistent reuse.

2010-06-15 01:12:10 URL:http://www.webservicex.net/geoipservice.asmx [519/519] -

> "resp.xml" [1]

FINISHED --2010-06-15 01:12:10--

Downloaded: 1 files, 519 in 0s (2320 GB/s)

 

C:\test>notepad resp.xml

           

      Notice the content length (370) added by WGET.

 

 

 

Conclusion : WGET is a powerful tool which can be used to test webservices without using tools like SOAP UI or XML SPY. In fact, this is exactly what SOAP UI does, but in a more fancy way. Similarily, WGET can also be used to test inbound Siebel webservices.

 

 

I discovered WGET it only because I wanted a quick and efficient way to testing webservices on windows as well as unix. Now I can test webservices without installing shareware or when I don’t have admin rights to install them. Hope this helps Siebel and non Siebel developers in their work.

 

 

May the source be with you.

You cannot execute a query using the % field.(SBL-DAT-00401)

Found a really annoying oracle limitation today. One requirement involved querying in a BC Field which had a column of type long behind it. I didn’t think twice and implemented this with a little bit of scripting. The script compile and ran fine on my local machine. But once I checked in and got it compiled, it got this error

 

You cannot execute a query using the %1 field.(SBL-DAT-00401)

 

And I found this from supportweb:

 

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=539572.1&h=Y

 

The Oracle RDBMS does not support queries on LONG columns - Siebel Technical Support testing just confirmed that this restriction that is documented for Oracle 8 already still exists in Oracle 10.

http://download-east.oracle.com/docs/cd/A58617_01/server.804/a58241/ch5.htm

Restrictions on LONG and LONG RAW Data
Although LONG (and LONG RAW; see below) columns have many uses, their use has some restrictions:
- Only one LONG column is allowed per table.
- LONG columns cannot be indexed.
- LONG columns cannot appear in integrity constraints.
- LONG columns cannot be used in WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements. ...

 

But I had a perfect working code on my local db. How could that be ? The next line in supportweb explained this.

 

The SQLAnywhere Database Engine that is used for the Siebel local database does not have this restrition, so the query is working there.

 

Great !! Now I have to find another way out. Any suggestions ?

 

How deep was deepwater horizon drilling ?

This infographic shows how deep Deepware Horizon was drilling for oil.

Finally, I created an artwork - with IOgraph

IOGraphica is a small application which creates a graph map of your mouse movements. You just have to start the app and let it run, and its ouput is  PNG file. Here is mine after one day of running.

See all developer logins in Siebel

I wanted to see all users who had localdbs on our server.

 

select * from user_role_privs

 

..will list all the users roles. The user with SSE_ROLE permissions are registered developers, and they could have local dbs.

'Transaction Updated' is a required field. Please enter a value for the field.(SBL-DAT-00498)(SBL-EAI-04389)

 

'Transaction Updated' is a required field.  Please enter a value for the field.(SBL-DAT-00498)(SBL-EAI-04389)

 

This error occurs if there are system fields explicitly specified in the Business Component used to insert data into Siebel tables. Find the BC behind the integration object, and check if fields like Created or Updated are mentioned there. Inactivate them and it should work fine.

 

Lost....last season...watching it now on Star World

Many years ago, while still in college, I got to know about the television series LOST. Apparently, the first season had finished airing in the US and he had downloaded the episodes. And I got hooked. It gave a whole new interpretation of the phrase:stranded on an island. I loved the fact that the story was happening in present time, and despite all the advancements in technology, the survivors were stuck with no outside help. I guessed that the whole saga would last 2 seasons at most, because soon they would run out of ideas, and help would come from outside.

 

 

Now I am watching the last season of the saga on star world. Somewhere in between , the show lost its focus. There are more questions still waiting to be answered. Looots of extra characters came in from nowhere . And loads of them disappeared. Its not as hooking as the first season was. My favorite character was Charlie, the character played by Dominic Monaghan. I loved his background and how he came to the island. And till date, the story of his death is the one that hurt me most on the show. The episode “Greatest Hits” is my favourite. Charlie writes down the greatest moments of his life, when he realizes that his death is near. There are moments which seem trivial to others, but for Charlie, it has changed something in his life.

 

If I make a list of my top 5 greatest moments..I dunno what would turn up. Maybe the first time I rode my cycle ? When my sister was born ? When I landed and lost my first job ? Definitely the day I finally got rid of college.

 

 

Anyway, I don’t think I will take anything else from LOST except this idea of Greatest Hits.