Assign System Administrator responsibility from backend (using API)

DECLARE
    lc_user_name              VARCHAR2(100)    := '&USER_NAME';
    lc_resp_appl_short_name   VARCHAR2(100)    := 'SYSADMIN';
    lc_responsibility_key     VARCHAR2(100)    := 'SYSTEM_ADMINISTRATOR';
    lc_security_group_key     VARCHAR2(100)    := 'STANDARD';
    ld_resp_start_date        DATE             := TO_DATE('01-JAN-2014');
    ld_resp_end_date          DATE         := NULL;

BEGIN
     fnd_user_pkg.addresp
     (   username            => lc_user_name,
        resp_app             => lc_resp_appl_short_name,
        resp_key             => lc_responsibility_key,
        security_group       => lc_security_group_key,
        description          => NULL,
        start_date           => ld_resp_start_date,
        end_date             => ld_resp_end_date
    );
 COMMIT;

EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Oracle Keywords

A list of all Oracle database keywords can be obtained by executing following query:

SELECT * FROM v$reserved_words order by 1;

and reserved keywords can be found by

SELECT * FROM v$reserved_words where reserved='Y';

In Oracle Apps, you should not create a DFF Segment Name with any of the reserved words, however Window Prompt Name can be a reserved keyword.

SELECT f1.descriptive_flexfield_name dff_name,
       descriptive_flex_context_code dff_context,
       end_user_column_name dff_segment_name,
       application_column_name dff_column, form_left_prompt
  FROM apps.fnd_descr_flex_col_usage_vl f1, applsys.fnd_descriptive_flexs f2
 WHERE f2.concatenated_segs_view_name = '&DFF_VIEW_NAME'
   AND f1.application_id = f2.application_id
   AND f1.descriptive_flexfield_name = f2.descriptive_flexfield_name
   AND f1.enabled_flag = 'Y'
   AND EXISTS (SELECT 'x'
                 FROM v$reserved_words rw
                WHERE rw.keyword = UPPER (f1.end_user_column_name))

Query to find modules installed in Oracle Apps

select

fav.application_short_name,

fav.application_name,

fpi.patch_level,

decode(fpi.status,'N','None','I','Installed','S','Shared',fpi.status)

from

fnd_application_vl fav,

fnd_product_installations fpi

where

fav.application_id=fpi.application_id 

order by 1,2;

SQL*Loader

How to avoid loading the Header (first Record) of a flat/data file?


Include the following code as part of your control file:-
OPTIONS SKIP=1


How to avoid loading the Trailer (last record) of a flat/data file?


This can be achieved by making use of WHEN condition in control file, provided the trailer contains fixed data.


For Example, if the trailer contains 'T' in the firstcolumn then:- 
LOAD DATA 
INFILE 'test.txt' 
APPEND 
INTO TABLE xxcustom_table 
WHEN (01) <> 'T' 
( 
   column1 ,
   column2 ,
   column3
   .....
)

Common Errors in Oracle Reports

Below are some of the common errors while working on oracle reports


REP-1219: 'M_BATCHES_EXP' has no size -- length or width is zero.
Cause:  In creating your layout, you created a layout object that has its Width and/or Height set to zero.
Action: In the property sheet for the object, enter a value for Width and/or Height


REP-1249: Column 'CF_1' has no PL/SQL formula.
Cause:  You created a column of Type Formula with nothing in the Formula field.
Action: Create a formula for the column or change the column's Type.


REP-1517: Column 'B_ENTERED_TOT_DR_DSP' references column 'Currency_Code', which has incompatible frequency.


REP-1401: 'r_entered_tot_cr_dspformula': Fatal PL/SQL error occurred.
ORA-06502: PL/SQL: numeric or value error


REP-1213: Field 'F_BAT_22' references column 'B_TOT_DR_DSP' at a frequency below its group.


REP-1814: Report cannot be formatted. Object 'horizontally' can never fit within 'F_BATCH_POSTED_DATE1'


REP-1212: Object 'Margin' is not fully enclosed by its enclosing object 'SET_OF_BOOKS_NAME'.

FNDLOAD commands

1. XML Publisher Data Definition and Templates
a) To download all Templates defined for a particular Data Definition


FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct ldt_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=[APPSHORTNAME] DATA_SOURCE_CODE=[DATADEFINITIONCODE]


b) To download for a particular Template and Data Definition


FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct ldt_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=[APPSHORTNAME] DATA_SOURCE_CODE=[DATADEFSHORTCODE] TMPL_APP_SHORT_NAME=[APPSHORTNAME] TEMPLATE_CODE=[TEMPLATECODE]


c) Upload RTF to XML Template

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD [APPSPWD] -JDBC_CONNECTION [DBHOST:DBPORT:SID] -LOB_TYPE TEMPLATE -APPS_SHORT_NAME [APPSHORTNAME] -LOB_CODE [TEMPLATESHORTNAME] -LANGUAGE en -TERRITORY [TERRITORYCODE] -XDO_FILE_TYPE RTF -FILE_CONTENT_TYPE 'application/rtf' -FILE_NAME [FILEPATH_ON_SERVER] -CUSTOM_MODE FORCE -LOG_FILE [LOGFILEPATH_ON_SERVER]


2. Concurrent Programs


FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ldt_name.ldt PROGRAM APPLICATION_SHORT_NAME="APPSHORTNAME" CONCURRENT_PROGRAM_NAME="PROGSHORTNAME"


3. Request Set


a) To download Request Set Definition


FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct ldt_name.ldt REQ_SET APPLICATION_SHORT_NAME="APPSHORTNAME" REQUEST_SET_NAME="REQSETSHORTNAME"


b) To download Request Set Link Stage Definition


FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct ldt_name.ldt REQ_SET_LINKS APPLICATION_SHORT_NAME="APPSHORTNAME" REQUEST_SET_NAME="REQSETSHORTNAME"

4. Flexfields

a) To download Flexfields

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ldt_name.ldt DESC_FLEX APPLICATION_SHORT_NAME= ‘APPSHORTNAME’ DESCRIPTIVE_FLEXFIELD_NAME=’FLEXFIELD_CODE’

b) To upload Flexfields

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct ldt_name.ldt  

Basic Sql Statements

Use ALTER statement  to modify table structure
 ALTER TABLE table_name
   ADD(
         column1_name column1_datatype column1_constraint,
         column2_name column2_datatype column2_constraint   );

Example 
ALTER TABLE XXTABLE_NAME
   ADD(
          creation_date DATE,
          last_update_date DATE   );


ALTER TABLE XXTABLE_NAME 
 MODIFY 
            last_name VARCHAR2(350);                    --this will make the length of existing last_name column to 350