Friday, December 30, 2016

Back to Siebel

I’ve come to understand that Oracle Cloud is a joke. At least for the time being, it can only be used for medium sized companies who have standard sales and service business processes. The amount of extensibility available is very less. And although one can write their own customization on Oracle PaaS and Java Clouds, that kind of beats the purpose anyway. The whole point of modern cloud is to reduce the amount of code written.

I’m hoping to ride out this last wave of Siebel projects before I too am forced to move to the cloud for good. 

For the time being, it’s good to be back. Happy New Year !

Thursday, December 1, 2016

TCC: Date Manipulation


<quer:projection id="idEEO2bDisabledPersonAnswerNo" alias="CorrectedStartDate">
    <!--quer:customFunction name="TO_CHAR"-->
    <quer:query projectedClass="Location" alias="Corr_Start_Dt">
                    <quer:field ownerQuery="MainQuery" path="Applications,Offers,ActualStartDate"/>
                            <quer:field path="NetworkLocation,TimeZone,Offset"/>
                                    <quer:customFunction name="TO_NUMBER">
                                        <quer:customFunction name="SUBSTR">
                                            <quer:customFunction name="TZ_OFFSET">
                                                <quer:customFunction name="TO_CHAR">
                                                    <quer:field ownerQuery="MainQuery" path="Applications,Offers,ActualStartDate"/>
                                        <quer:customFunction name="TO_NUMBER">
                                            <quer:customFunction name="SUBSTR">
                                                <quer:customFunction name="TZ_OFFSET">
                                                    <quer:customFunction name="TO_CHAR">
                                                        <quer:field ownerQuery="MainQuery" path="Applications,Offers,ActualStartDate"/>
                    <quer:field path="Code"/>
                    <quer:field ownerQuery="MainQuery" path="Applications,Requisition,JobInformation,PrimaryLocation,Location.Code"/>
    <!--quer:string>yyyy-MM-dd hh:mm:ss</quer:string></quer:customFunction-->

Monday, September 19, 2016

Too Much Javascript


I can't understand this trend. After years of focus and interest in compiled languages and binary executables, now everyone is focussed  on what can be done in the humble browser. And its programming language, Javascript. And that's where the action is. Everyday I hear about new javascript based libraries and frameworks. And the language syntax is getting worse. With new conventions of invoking script and processing data, its almost as if they are re-inventing multiple wheels again. There are new graphing and charting libraries, and it is now possible to build entire standalone desktop applications running on javascript. It seems to be everyone's favourite, enjoying a position once occupied by Java. I can straightly see the advantages of moving your UI code to Javascript.


  • It is truly portable. Runs in all modern browsers, even on smartphones and tablets.
  • Flash and Java, the previous leaders in browser programming, are no longer supported by newer browsers. Maybe due to security vulnerabitlies in them.
  • None of that bytecode compilation you see in Java class files.
  • The source stays with you, so it is still modifyable.
  • It is free. Truly free. It is open source.

But I can't help noticing that it greatly increases the amount of data being downloaded into the final browser. And takes up a lot of bandwidth. Sure, many of those applications use require.js and files are loaded only conditionally. But still, they need to be downloaded. And the humongous task of executing the program is now transferred from the server to the end-user's browser. Which is definitely good for the server, lesser load balancing to take care of. But on many sites, I can actually feel the browser slowing down due to the additional processing power it needs.

I like browsing light weight. I sometimes turn off javascript completely on some news sites, I feel it gives a much faster rendering of the site, as none of those additional garbage is loaded and run. The URL links still work, because those are in the HTML.

But the bigger problem is see is un-regulated development. Everyone is writing their own libraries and packages. First it was just google and facebook. Now even new players like oracle has entered the race and are giving out their own libraries. And the tutorials of all their libraries does the same thing: render graphs and charts in the browser.

Too many tools. Too much configuration. That is the problem. Javascript experts have written better about these problems, so I will leave that there.

The bigger problem I see lines of code. After programming for more than 20 years now, I have learned to understand is that the real challenge (and fun) in programming is to achieve maximum tasks writing as little code as possible. You don't have to be Phd to understand that it is easier to debug and test a 10 line program than a 100 line program. Every extra line added to the codebase increases the difficulty in debugging it at a later stage. Not to mention the huge effort of merging code written by large teams during checkin. But all these javascript based boilerplates, linters and generators don't solve this problem, they just make it worse.

A library should be simple enough to add it into ones current code base, and the developer should not have to know how it is implemented to use it. All the real work must be transferred into the library being invoked, leaving the base code base minimal. Minimal. That should be the keyword.

Or there should be a way for RAD tools. A system of abstractions which will hide the complexity beneath and generate highly efficient code.

When I started work in Siebel, I used to hate the idea of Enterprise Application development. But I absolutely admired the way Siebel had built a layer of abstraction and user properties, and through them, indirectly invoked C++ code to achieve its functionality. Sure there is one extra layer in this packaged application method, but the final code which the consultant/developer writes is very minimal. I have seen entire projects implemented with a total of less than 1000 lines of escript code in it.

I was looking into the new Oracle JET library for javascript application development which was created by Oracle and it is completely bonkers. All they have done is to re-implement a couple of already perfecte ideas and re-do it in their own way. A lot of the final code looks exactly the same as from Angular or React. I have no idea why any enterprise customer would decided to writes thousands of lines of javascript code just to get charts rendered in the browser, when other Oracle delivered  packaged and cloud applications can do the same without ANY lines of code.

Hope somebody sits up and takes notice.

Friday, August 26, 2016

Linux is 25 today !


25 years ago , Linus Torvalds let the world know of his pet project for a new operating system. And started the revolution.


Wednesday, August 24, 2016

Oracle JET


Oracle has released the training videos of their new Java Toolkit, Oracle JET. They are calling it a MOOC, or Massive Open Online Course.

Tuesday, August 16, 2016

Importing WSDLs with cyclic references into Siebel

Siebel has some issues consuming WSDLs which had a little complicated schema structure. Like cyclic references. That is when one of the children elements of a complextype is the same complextype again.

In this example schema, element "LineItemARBOType" has a child of type "LineItemARBOType" again.
This WSDLs poses no problem when consumed into a programming language like .NET or Java, because the parser simply creates a class and references itself in it. But these kind of WSDLs were not supported in Siebel. Trying to import a WSDL with cyclic references would result in this error from tools.:
There are no web service definitions to be generated.(SBL-EAI-04340)
Following are warnings generated in the process:
Service 'xx' can not be imported, because none of its ports could be imported.(SBL-EAI-04333)
Port 'xx' can not be imported. PortType 'xx' can not be imported. The 'xx' was ignored. Error importing XML schema via method 'xx' for 'element' 'xx' in 'xx'(SBL-EAI-08009)
Cycle detected in the 'xx' schema: xx 'xx' has a cycle (SBL-EAI-09008).

But turns out, Oracle has fixed this issue in IP2014 ( release of Siebel. Via bug:Bug 10539615. WSDLs with cyclic references can be consumed into tools IP2014 and upwards. But it creates hundred of integration component entries (sometimes even thousands).

Tuesday, August 2, 2016

Oracle HCM Cloud Operations 2016 Implementation Specialist


I recently cleared the Oracle HCM Cloud Operations Implemenation Specialist test. 
















Wednesday, July 27, 2016

BIP: Report Usernames


There was a simple requirement to print the login name of the user running a BIPublisher report on that report itself. This got me hunting. There are tonnes of ways to get the login name from an oracle database, but because of the way BIP uses the DB and runs the SQL, not all of those ways would give the right answers.

Simplest way, use the  <?$XDO_USER_NAME?>  parameter n your template.

But my requirement was to enforce data security on the report, to ensure the logged in user sees only the records he should see. This meant fetching the username at the SQL itself.

One sure shot way to do this is to use :XDO_USER_NAME in the SQL. This will fetch the userid of the logged in user.

And there is one more way.

Use the sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER function in the SQL.



To get all the sys_context parameters, this SQL can be used.

  sys_context ('userenv','ACTION') ACTION,
  sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID,
  sys_context ('userenv','BG_JOB_ID') BG_JOB_ID,
  sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
  sys_context ('userenv','CLIENT_INFO') CLIENT_INFO,
  sys_context ('userenv','CURRENT_BIND') CURRENT_BIND,
  sys_context ('userenv','CURRENT_EDITION_ID') CURRENT_EDITION_ID,
  sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA,
  sys_context ('userenv','CURRENT_SCHEMAID') CURRENT_SCHEMAID,
  sys_context ('userenv','CURRENT_SQL') CURRENT_SQL,
  sys_context ('userenv','CURRENT_SQLn') CURRENT_SQLn,
  sys_context ('userenv','CURRENT_SQL_LENGTH') CURRENT_SQL_LENGTH,
  sys_context ('userenv','CURRENT_USER') CURRENT_USER,
  sys_context ('userenv','CURRENT_USERID') CURRENT_USERID,
  sys_context ('userenv','DATABASE_ROLE') DATABASE_ROLE,
  sys_context ('userenv','DB_DOMAIN') DB_DOMAIN,
  sys_context ('userenv','DB_NAME') DB_NAME,
  sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
  sys_context ('userenv','DBLINK_INFO') DBLINK_INFO,
  sys_context ('userenv','ENTRYID') ENTRYID,
  sys_context ('userenv','FG_JOB_ID') FG_JOB_ID,
  sys_context ('userenv','GLOBAL_UID') GLOBAL_UID,
  sys_context ('userenv','HOST') HOST,
  sys_context ('userenv','INSTANCE') INSTANCE,
  sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME,
  sys_context ('userenv','IP_ADDRESS') IP_ADDRESS,
  sys_context ('userenv','ISDBA') ISDBA,
  sys_context ('userenv','LANG') LANG,
  sys_context ('userenv','LANGUAGE') LANGUAGE,
  sys_context ('userenv','MODULE') MODULE,
  sys_context ('userenv','NETWORK_PROTOCOL') NETWORK_PROTOCOL,
  sys_context ('userenv','NLS_CALENDAR') NLS_CALENDAR,
  sys_context ('userenv','NLS_CURRENCY') NLS_CURRENCY,
  sys_context ('userenv','NLS_DATE_FORMAT') NLS_DATE_FORMAT,
  sys_context ('userenv','NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,
  sys_context ('userenv','NLS_SORT') NLS_SORT,
  sys_context ('userenv','NLS_TERRITORY') NLS_TERRITORY,
  sys_context ('userenv','OS_USER') OS_USER,
  sys_context ('userenv','POLICY_INVOKER') POLICY_INVOKER,
  sys_context ('userenv','PROXY_USER') PROXY_USER,
  sys_context ('userenv','PROXY_USERID') PROXY_USERID,
  sys_context ('userenv','SERVER_HOST') SERVER_HOST,
  sys_context ('userenv','SERVICE_NAME') SERVICE_NAME,
  sys_context ('userenv','SESSION_EDITION_ID') SESSION_EDITION_ID,
  sys_context ('userenv','SESSION_USER') SESSION_USER,
  sys_context ('userenv','SESSION_USERID') SESSION_USERID,
  sys_context ('userenv','SESSIONID') SESSIONID,
  sys_context ('userenv','SID') SID,
  sys_context ('userenv','STATEMENTID') STATEMENTID,
  sys_context ('userenv','TERMINAL') TERMINAL
from dual
Try running this on Oracle Live SQL

Monday, July 11, 2016

SQLFiddle (for Oracle) is broken. Try Oracle Live SQL


BI Publisher reporting work involves a lot of SQL. And often (in the Oracle world) one needs a quick way to try an Oracle function in a quick sandbox like system. For  long time, I have been using SQLFiddle, which provides a simple browser UI to create schema and run SQLs on them. But for many months now, the Oracle support on SQLFiddle has been broken. And I was looking for alternatives.

Turns out, Oracle themselves provides an excellent replacement tool to create schemas and run SQLs. Free for anyone who has an login. That includes all customers and vendors working on their products.

Go to Oracle Live SQL. The SQL worksheet page there can be used to run any SQLs, and its on a 12c DB server.



There is a simple schema designer to create schema. Or you can also use standard DDL functions. Turns out , this is actually a simple Apex application.



Tuesday, June 14, 2016

BIP: Distinct

BIP has a distinct function

If you use it on a node of elements, it returns a space separated sequence of its distinct elements.
Eg: for this xml:


Using <?xdoxslt:distinct_values(CwaProductCode)?> gives:

001 002 003

Using <?count(xdoxslt:distinct_values(CwaProductCode))?> gives:


But what if one needs to group by the distinct values, and then count the number of elements under it ? Then distinct can’t help.

<?end for-each-group?>
Gives the answer:


Wednesday, June 1, 2016

What caused the NA14 outage ?


There is a very nice indept article here explaining what happened during the SFDC outage on May 9th, 2016, when server NA14 went down. It also discusses the aftermath and lessons learnt from the outage.



Friday, May 20, 2016

HCM: Printing Hours and Rates on Checkwriter


Fusion HCM Cloud uses BIP templates as part of it payroll process, its also used for printing checks. One of the most common requirements I have received is to print the employees hours and rates on the checkprinter output.

Here is what the vanilla template looks like, its called USCheckWriterReport.rtf.


It can be seen that the earnings section in the bottom prints only current and YTD amounts. The hours and rates are not printed by default.

The hours and rates are in a different node in the Payslip xml

This is the node for earnings:


And this is the one for Hours and Rates


So what we need to do is the use the REPORTING_NAME as a foreign key, and lookup the correct HOURS_X_RATE record, and then print it in the earnings table.

This is the code to use:



PS: I am just putting this up for my own reference, and for anyone out there stuck like me.

Wednesday, May 11, 2016

Major SFDC Outage


Today there is a hackernews thread describing a major SFDC Outage.




This is the side effect of the cloud computing. An outage affects multiple businesses.









Friday, April 15, 2016

Fusion HCM: Testing Payroll templates


Oracle’s Fusion HCM cloud service uses BIP templates as part of its pay processing. When the Payoll processes are submitted, HCM does a lot of things, it generates the Pay data, and populates tables. But the final step of this process is to generate the actual payslip , and for this it invokes a BIP report. © 2016

As report designer, I often get requests to modify these reports. But to test the reports, one need not run the entire payroll process again. Simply re-running the report with the modified template will suffice. © 2016

The BIP template to modify for US is the USOnlinePayslip.


Its location is:  image

To see when it was last run, simply check the history of this report, under More-> History. Reset the query and run search.



Pick up any of the last records, and drilldown  on it. Now copy the Payroll Action Identifier from this page © 2016


Now you can go back and re-run this report with this parameter, and can see the changes of your template instantly ! Ta-da ! © 2016

Friday, April 8, 2016

Oracle Sales Cloud: Getting around OSC’s WSDL parsing , Siebel UCM


Recently working on getting Oracle Sales Cloud integrated to Siebel UCM for Accounts. Oracle Sales Cloud can read a WSDL specification from a URL, but you still have to build the request message using Groovy script (would be nice to shoot whoever designed this). So you have to use Groovy script and write a global function to build up the message. Turns out, you can only add elements and attributes to the message which is already in the parsed WSDL. Nothing new can be added. © 2016

The problem here is that for integrations to Siebel UCM, a hidden attribute named ExternalSystemId has to be populated in the incoming message. This attribute is not in the WSDL when it gets generated from Siebel UCM. But it has to be sent in the SOAP request (would also be nice to shoot whoever designed this).  © 2016

Error invoking service 'UCM Transaction Manager', method 'SOAPExecute' at step 'Transaction Manager'.(SBL-BPR-00162)
<?> Failed to find ExternalSystemId in input message(SBL-IAI-00436)


This is what you get when you consume the UCM WSDL in SOAP UI.


The actual message has to be (see highlighted changes) © 2016



If you add the groovy script to add this attribute, OSC will simply ignore it, and the attribute is not send to UCM. The only viable workaround is to Edit the WSDL AFTER it is generated, but BEFORE it is given to Sales Cloud !  © 2016

1: Generate the WSDL from UCM.

2: Open it in an XML editor , use XMLSPY if you have it. © 2016

3: Find the definition of the top container element int the WSDL: © 2016


4: Add this text (highlighted) : © 2016


<xsd:attribute name="ExternalSystemId" type="xsd:string"/>

5: Now validate, save and upload this WSDL to your public folder from where OSC can read it. OSC does not consume WSDLs, but reads the definition on the fly. © 2016

6: Now add the groovy script to populate this new attribute with the registered SystemId Name.


Phew !!


© 2016© 2016

Thursday, March 31, 2016

TCC: Handling Encoding


Aahh…multilingual. That word increases the complexity of any project intantly. When your enterprise application is multilingual, it means users will be able to add and edit data in different languages, and that the data can no longer be stored in ASII/ANSI format. East Asian, and Middle Eastern and some European languages require more than one byte for a single charachter, so it has to be stored in Unicode format. While working on Taleo’s TCC scripts, I recently hit a roadblock with multilingual data, but the fantastic folks at Taleo had already solved the problem. © 2016

TCC’s pipelines handle data in UTF-8 format, but many enterprise systems will produce output in UTF-16. So which one is better ? There is a common misconception that UTF-8 can not store all language charachters, and that UTF-16 is required. That’s not true. UTF-8 has an amazing awesome format, and it can depict every Unicode charachter, same as UTF-16. Here is a spectucular explanation of this miracle.

So if you get data in UTF-16 (or any of the other formats), how do you load them via TCC ?  In the configuration  file, TCC provides an option to set the encoding of the source file. © 2016


If you know the encoding of the source, you enter it here.  This will work for standard Import files. There is also an option to set the response encoding.


But some complicated TCC configurations, like NetChange, require the pipeline data to be in UTF-8. So how do you get around that ?

The encoding can be changed in the configuration file. Go to Pre-Processing tab, and add a new step.

There is an option to add an ‘Convert Encoding’ step. © 2016


Choose the source encoding, and set the target as UTF-8. This step has to be the first step in your NetChange configuration file.


Thats it !

Monday, March 21, 2016

Date calculations in eText templates


Aahh, eText. That most hated or Oracle templates. One area where this template really falls is in the area of date calculation, or date manipulations. Example a standard requirement is to calculate an end date, or proposed/expected end date for some transaction. Check is valid for only ..30 days from date of issue.

The official documentation does no mention any way to x number of days to dates. But if you search on supportweb, you will come across the INCREASE_DATE function.

To add 30 days to today’s date, use INCREASE_DATE(SYSDATE(),30). To substract days, use a negative number for the offset.

But that’s it, eText does not provide a way to get the month, year, or day from a date. So those will have to be calculated in whatever onpremise/cloud system is being used and the value must be present in the xml. eText also does not provide a way to add extra functions or XSLT functions in the templates, like in BIP RTF templates.



Originally published on

Thursday, March 10, 2016

Sequence Numbers in eText templates


As I said earlier, Oracle’s eText template is really painful to work with. Its does not have support variables or arrays, so when the need arises to have something temporarily stored somewhere, you are lost. The one thing eText does have, is a method to generate sequence numbers. Basically you use the DEFINE_SEQUENCE command.


The define sequence command has four subcommands: reset at level, increment basis, start at, and maximum. The increment basis subcommand specifies if the sequence should be incremented based on record or extract instances. The allowed parameters for this subcommand are RECORD and LEVEL. Enter RECORD to increment the sequence for every record. Enter LEVEL to increment the sequence for every new instance of a level.

To generate the sequnce numbers, use the SEQUENCE_NUMBER function in the template.


But what if you just want to keep count of something, and not count levels or records in the data ? Just leave out the INCREMENT_BASIS completely.


So now, whenever SEQUENCE_NUMBER function is called, the current value will be printed, and the counter will be increased by 1. This happens everytime the function is invoked.

There are some payment interfaces where the number has to be incremented in steps of 5 or 10. For example, one of the ADP checkprinter format template requires a counter to start at 30, and be incremented in steps of 5. So its 30, 35, 40, 45…

To do that the SEQUENCE_NUMBER function should be used with some mathematics.




Originally published on

Siebel IP 18 - 2018 SOD

Oracle is going ahead and staying with Siebel. For now. They have just released the statement of direction for IP 2018. At a high level th...