Tuesday, 16 April 2013

XML Publisher Excel output numbers getting truncated.

For XML Publisher reports when the output is in excel it is a common issue number fields are truncated. To avoid this use the following piece of code.

--XML Publisher Excel avoid numbers being truncated
TAGfo:bidi-override direction="ltr" unicode-bidi="bidi-override"TAGTAG?YOUR_FIELD?TAGTAG/fo:bidi-overrideTAG

Note this might effect the users from SUM etc on the fields.

FRM-40654 Record Has Been Updated. Requery Block To See Change (Oracle Fix)

Blank/space characters cause a lot of issues paticularly in Oracle forms.
Most of the times when you try to update a record you get an error message

FRM-40654: Record has been updated. Requery block to see change.

Most of the times this is caused by extra chars in the string columns. There is an Oracle fix issue for this issue. Apply the fix in TEST instance before applying in PROD. The fix is as follows.

Run script $FND_TOP/sql/afchrchk.sql

Option1= fnd_flex_values_tl
Option2= description
Option3 = N
Option4 =Y

Below is the explanation for all the options from the oracle script

--Table Name and Column Name (Always better to work on one table and column if known)
prompt Enter the table and column names to check for leading or trailing
prompt spaces and control characters. Like-style expressions are ok.
prompt Leave either table or column blank to check table/column.
prompt Table name (blank for all):
define tblarg = '&1'
prompt Column name (blank for all):
define colarg = '&2'

--I would recommend to input Y with caution for the newline option as oracle stores data with new lines chars in some cases.
--Ex : Atributes info on valuesets are stored with new lines chars
--Default N checks for only leading/trailing spaces
prompt Newline characters are acceptable in columns never queried in
prompt Forms, on only queried in multi-line items.
prompt Enter Y to also look for newline characters.
prompt Check for newline characters (Y/N)?
define newlinearg = '&3'

--option to fix or just list the errors. recommend to first list and then fix errors.
prompt Enter Y to automatically strip all leading/trailing spaces
prompt and control characters found.
prompt *** WARNING ***
prompt It is highly recommended to run first without stripping
prompt to be sure all detected values really should be fixed!

prompt Automatically fix all errors found (Y/N)?
define fixarg = '&4'

Oracle Find Locked Objects (SQL)

Finding Locked in Oracle can be tricky. I use the below approach.

--Locked Objects (Get SID from this query)
select o.object_name,lo.* from v$locked_object lo , dba_objects o
where lo.object_id = o.object_id
and o.object_name like '%' ;
--and o.object_type = 'PACKAGE'

This query finds all the locked objects. You can filter on packages. Sometimes the object may not appear in the above list. Then the below query might help.

--get serial and sid from this query
select * from v$session where sid = 215 or module like 'XX%';

--sid,#serial
alter system kill session '316,25481';

Wednesday, 10 April 2013

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.

Monday, 11 February 2013

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

Integrations Lead - Lessons learnt

 Integrations have been my passion for a while but like anything tech there is no credit given when things go right but always heaps of pres...