I recently cleared the Oracle HCM Cloud Operations Implemenation Specialist test.
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. cleartext.blogspot.com
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. cleartext.blogspot.com
And there is one more way.
Use the sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER function in the SQL. cleartext.blogspot.com
To get all the sys_context parameters, this SQL can be used. cleartext.blogspot.com
select
sys_context ('userenv','ACTION') ACTION,
sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID,
sys_context ('userenv','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,
sys_context ('userenv','AUTHENTICATION_DATA') AUTHENTICATION_DATA,
sys_context ('userenv','AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
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_EDITION_NAME') CURRENT_EDITION_NAME,
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','ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY,
sys_context ('userenv','FG_JOB_ID') FG_JOB_ID,
sys_context ('userenv','GLOBAL_CONTEXT_MEMORY') GLOBAL_CONTEXT_MEMORY,
sys_context ('userenv','GLOBAL_UID') GLOBAL_UID,
sys_context ('userenv','HOST') HOST,
sys_context ('userenv','IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,
sys_context ('userenv','INSTANCE') INSTANCE,
sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME, cleartext.blogspot.com
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_ENTERPRISE_IDENTITY') PROXY_ENTERPRISE_IDENTITY,
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_EDITION_NAME') SESSION_EDITION_NAME,
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, cleartext.blogspot.com
sys_context ('userenv','TERMINAL') TERMINAL
from dual
Try running this on 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
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
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.
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
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.