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 oracle.com login. That includes all customers and vendors working on their products. cleartext.blogspot.com

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

 

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. cleartext.blogspot.com

 

cleartext.blogspot.com

BIP: Distinct

 

cleartext.blogspot.com

BIP has a distinct function

xdoxslt:distinct_values

If you use it on a node of elements, it returns a space separated sequence of its distinct elements.

Eg: for this xml:

cleartext.blogspot.com

<ROWSET>
    <ROW>
        <CwaProductCode>001</CwaProductCode>
    </ROW>
    <ROW>
        <CwaProductCode>002</CwaProductCode>
    </ROW>
    <ROW>
        <CwaProductCode>001</CwaProductCode>
    </ROW>
    <ROW>
        <CwaProductCode>003</CwaProductCode>
    </ROW>
</ROWSET>

cleartext.blogspot.com

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

001 002 003

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

3

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.

cleartext.blogspot.com

<?for-each-group:ROW;./CwaProductCode?>
<?CwaProductCode?><?'-'?><?count(current-group()/.)?>
<?end for-each-group?>

Gives the answer:

001-2
002-1
003-1

cleartext.blogspot.com

cleartext.blogspot.com

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.

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.

cleartext.blogspot.com

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 

cleartext.blogspot.com

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.

cleartext.blogspot.com

This is the code to use:

<?$ava_earnings/GLB_PAY_ARCH_PR_HOURS_X_RATE[REPORTING_NAME=current()/REPORTING_NAME]/EARNINGS_HOURS?>

 

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

cleartext.blogspot.com

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.

 

 

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 cleartext.blogspot.com

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 cleartext.blogspot.com

The BIP template to modify for US is the USOnlinePayslip.

Its location is: 

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 cleartext.blogspot.com

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 cleartext.blogspot.com

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 cleartext.blogspot.com

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 cleartext.blogspot.com

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 cleartext.blogspot.com

 

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 cleartext.blogspot.com

1: Generate the WSDL from UCM.

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

3: Find the definition of the top container element int the WSDL: © 2016 cleartext.blogspot.com

4: Add this text (highlighted) : © 2016 cleartext.blogspot.com

<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 cleartext.blogspot.com

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

 

Phew !!

© 2016 cleartext.blogspot.com© 2016 cleartext.blogspot.com