Thursday, April 25, 2013

ORA-01031: insufficient privileges

ORA-01031: insufficient privileges error is caused due to invalid permission.

When happens on a Select/Insert/Delete/Update/Alter query, check if table has grants issued to the username trying to access.

For package make sure execute permission is given to username trying to access package. Even then if it does't work, use AUTHID CURRENT_USER on package spec.

Tuesday, April 16, 2013

Report doesn't return data / srw.message not working

Sometimes when the query returns data from sql but fails from oracle report.
This happens mostly in multi org structure. Make sure you have the below checks.

  1. Check if User Parameter has P_CONC_REQUEST_ID defined. No need to define this parameter in concurrent request definition.
  2. SRW.USER_EXIT('FND SRWINIT');  has to be used in AfterPForm (After Parameter) trigger.
  3. Run the report from correct responsibility.


Compile form in Oracle Applications

Use the below command to compile a form from server. Compile command is different for both 11i and R12. Pass appropriate values.
***Replace upper case in command with appropriate values.

Oracle Applications R12

frmcmp_batch userid=DBUSER/DBPWD module=FORMNAME.fmb output_file=FORMNAME.fmx module_type=form batch=no compile_all=yes


Oracle Applications11i

f60gen module=FORMNAME.fmb userid=DBUSER/DBPWD module_type=form output_file=FORMNAME.fmx compile_all=special

 

Tuesday, April 9, 2013

Distinct values in value set

There are multiple ways to define distinct values in custom value set.

Standard:
  1. Setting Allow Parent Values will make values distinct. Con is that it cannot be used in conjunction with ID in table column.


Custom
  1. Write distinct statement in the field where table name is entered, but this has a limitation to the number of characters that can be entered. so if query is long then you cannot make use of this feature.
  2. Create view based on your query with distinct values and use that view in the value set.
  3. Use of max rowid in where clause field

    SELECT ooh.order_number
    FROM oe_order_lines_all ool, oe_order_headers_all ooh
    WHERE ool.header_id = ooh.header_id
    AND ool.rowid= (SELECT max(rowid)
    FROM oe_order_lines_all oo11
    WHERE oo11.header_id = ooh.header_id);


Session Timeout Profile Oracle Applications

Oracle Apps session timeout can be set by using a profile - ICX:Session Timeout.
Login into System Administrator --> Profile and search profile "ICX:Session Timeout" at User Level and enter a value.

Value represents in minutes.