Oracle WEB ADi - Excel Macro settings in MS Office 2010

Well seting the macr settings in Office 2010 is as below. Excel Options -> Trust Center -> Trust Center Settings -> Macro Settings Trust access to the VBA project object model - check.

Project Expense Report Account Generator(PAAPWEBX)

Project Expense Report Account Generator(PAAPWEBX):

This workflow generates the account during the iExpenses process.This called from the workflow 'Expenses' (APEXP).
The process calling the account generator is
'AP Validate Expense Report' -> which calls the API AP_WEB_EXPENSE_WF.APVALIDATEEXPENSEREPORT
which internally calls various API's before calling tge account generator from pa_acc_gen_wf_pkg.ap_inv_generate_account
which calls the standard FND API's for generating the code combination for the generator.
fnd_flex_workflow.generate( l_itemtype,l_itemkey, etc
l_error_message which passes back the workflow error.

So the API calling is as follows

iExpenses
Calls AP_WEB_EXPENSE_WF.APVALIDATEEXPENSEREPORT
Calls pa_acc_gen_wf_pkg.ap_inv_generate_account
Calls fnd_flex_workflow.generate

To debug the account generator use the following profile.
Account Generator:Run in Debug Mode preferably at the user level which shows the account generator workflow in workflow admin.

ORA-01722 – Invalid number with SQL*Loader in unix

When tring to load data using SQL*Loader Invalid number error popped up.

The last column in the staging table was a number. Having tried trim and nullable etc. I found that the data file has got funny chars. The data file was copied from windows.

Solution:
dos2unix filename.dat

Oracle First day of given month or given year.

I noticed trunc has a skillful way of working with dates.

First day of the month :
select trunc(sysdate,'MM') from dual;
Gives me 01-FEB-13

First day of the year:
select trunc(sysdate,'YY') from dual;
Gives me 01-JAN-13

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.