Oracle SUBSTR (Seperate strings) based on a delimiter

Using regexp_substr we can match a expression in the parent string the delimiter here is '.'
[^.]* --> match one or more occurences of the string [any chars]ending with . so this matches anything like abc. xyz. 123. or even .

select regexp_substr('1.00..60200....','[^.]*.',1,4) from dual;
Result: 60200.

Using the below rtrim we can trim the '.' in the end.

select rtrim('123.','.') from dual;

Final SQL:
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,5),';') seg from dual;
Result: null
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,2),';') seg from dual;
Result: 00
select rtrim(regexp_substr('1;00;123;60200;;;;','[^;]*;',1,3),';') seg from dual;
Result: 123

PIVOT (Oracle) - Convert multiple Rows to Columns

I had to convert multiple rows into columns today. Fetch balances from gl_balances and apply multiple conversions(say GBP,USD,AUD,HKD).

First approach:

select period_net_dr , period_net_dr*gt1.avg_rate gbp_value
, period_net_dr*gt2.avg_rate usd_value
, period_net_dr*gt3.avg_rate aud_value
, period_net_dr*gt4.avg_rate hkd_value
from gl_balances glb,
gl_translation_rates GT1,
gl_translation_rates GT2,
gl_translation_rates GT3,
gl_translation_rates GT4
where glb.period_name = 'AUG-12'
and GT1.period_name = 'AUG-12'
and GT2.period_name = 'AUG-12'
and GT3.period_name = 'AUG-12'
and GT4.period_name = 'AUG-12'
and GT1.to_currency = 'GBP'
and GT2.to_currency = 'USD'
and GT3.to_currency = 'AUD'
and GT4.to_currency = 'HKD';

The table gl_translation_rates appears 4 times in the query. So how we can do it better.

Using Oracle PIVOT feature this is how it can be done.

select period_net_dr, period_net_dr*gbp_rate,period_net_dr*usd_rate, period_net_dr*aud_rate,period_net_dr*hkd_rate from gl_balances glb,

(SELECT period_name,gbp_rate,usd_rate,aud_rate,hkd_rate
FROM (SELECT period_name,TO_CURRENCY_CODE, conversion_rate
FROM gl_translation_rates
where period_name = 'AUG-12' and set_of_books_id = 1)
PIVOT (sum(conversion_rate) FOR TO_CURRENCY_CODE IN ('GBP' gbp_rate,'USD' usd_rate,'EUR' eur_rate ,'HKD' hkd_rate,'SGD' sgd_rate,'AUD' aud_rate)) ) gl_rates

where glb.period_name = 'AUG-12'
and gl_rates.period_name = glb.period_name
and period_net_dr <> 0;

-----

As you can see the gl_rates pseudo table which I built in the view is the key and the beauty of pivot feature is it allows us to name the columns like gbp_rate etc.

So we have only one hit on the rates table. And the query is more readable

Unable to set NLS_LANGUAGE - Workflow Builder

When I installed workflow builder for the first time I had the following error

220: Unable to set NLS_LANGUAGE.
210: Oracle Error: ORA-00942: table or view does not exist. SQL text: SELECT NLS_LANGUAGE FROM WF_LANGUAGES WHERE :l IN (NLS_LANGUAGE, CODE)

To fix the error.
1.Open REGEDIT from cmd promt
2.Navigate to My Computer -> HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
3. Find key NLS_LANG and update it to charset value from the below query
select nls_language||'_'||nls_territory||'.'||nls_codeset from wf_languages where code = 'US';
4. Also recommended to do the same for the NLS_LANG key under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME2 (or whatever home workflow is installed.

Oracle Workflow - Standard Concurrent Manager Activities

Oracle workflow provides some standard activities for invoking concurrent programs from workflow. The follow gives a sneak peak into the features related to this.

Execute Concurrent Program Activity -> Calls FND_WF_STANDARD.EXECUTECONCPROGRAM
It submits an Oracle Applications concurrent program from your workflow process and waits for it to complete
Submit Concurrent Program Activity -> Calls FND_WF_STANDARD.SUBMITCONCPROGRAM
It submits an Oracle Applications concurrent program from your workflow process, but does not wait for it to execute or complete.
Wait for Concurrent Program Activity -> Calls FND_WF_STANDARD.WAITFORCONCPROGRAM
If you submit a concurrent program from your workflow process, you can use the Wait for Concurrent Program activity as a means of blocking the process from further execution until the concurrent program completes.

Oracle Cash Managment - Bank Statement Reconcilation Tables

Bank reconciliation is one of the important process in Oracle Cash Management.

Below are the list of some tables and views which can help in generating reports.

Statement Interface Tables:
1. CE_STATEMENT_HEADERS_INT_ALL - Statement Headers
2. CE_STATEMENT_LINES_INTERFACE - Statement Lines

Statement Data Tables

AP_BANK_ACCOUNTS - Contains Bank account details

CE_STATEMENT_HEADERS_ALL - Contains bank statement headers

CE_STATEMENT_LINES - Contains bank statement lines, The status column shows if the line is reconciled or not

CE_STATEMENT_RECONCILS_ALL - Tables linking Statement Tables and various entities ( Receipts/Payments) Columns Reference Types(PAYMENT/RECEIPT/JE_LINE etc) and Reference ID can be used for linkage.

CE_RECONCILED_TRANSACTIONS_V - All reconciled Transactions
CE_101_RECONCILED_V/CE_200_RECONCILED_V/CE_222_RECONCILED_V has the reconciled transactions from GL/AP/AR respectively.

CE_AVAILABLE_TRANSACTIONS_V - All available transactions
Unreconciled Payments - CE_200_TRANSACTIONS_V
Unreconciled Receipts(Type=CASH) - CE_222_TRANSACTIONS_V
Miscellaneous(Type=MISC) - CE_222_TRANSACTIONS_V
Journal - CE_101_TRANSACTIONS_V
Batches - CE_AVAILABLE_BATCHES_V

Package CE_AUTO_BANK_MATCH controls most of the views.procedures like set_all will set the application ids to yes for the views to pick the data.

CE: Bank Account Security Access controls the data which can be viewed

Oracle View all Workflows.

In most cases users can see only workflows which below to the particular user.

Use the following query to find out who can see all workflows.

SELECT TEXT FROM WF_RESOURCES
WHERE name like 'WF_ADMIN_ROLE%';

You will find text like 'FND_RESP|FND|FNDWF_ADMIN_WEB|STANDARD' or SYSADMIN in the output. So a System Admin or Workflow Admin can see all the workflows across users.

Incase you want to update the role to some specific responsiblity use the following SQL.

UPDATE WF_RESOURCES
SET TEXT='FND_RESP|FND|FNDWF_ADMIN|STANDARD'
WHERE NAME = 'WF_ADMIN_ROLE'

--12345 is the responsibility id.
UPDATE WF_RESOURCES
SET TEXT='FND_RESP0:12345'
WHERE NAME = 'WF_ADMIN_ROLE';

View output for concurrent programs run by other users

Its pretty common being developers we would want to see the output of programs run by other users. Set the profile 'Concurrent:Report Access Level' -> Responsibility at user level.Navigate to the responsibility where the program is run and you should be able to see the output files.

Use the below query to get the responsibility the program is run.

select frt.responsibility_name from fnd_concurrent_requests fcr, FND_RESPONSIBILITY_TL frt where frt.responsibility_id= fcr.responsibility_id and request_id = x_req_id ;