Moving setup from one instance to another in oracle R12

The Generic Loader (FNDLOAD) is a concurrent program that can transfer Oracle Application entity data between database and text file. The loader reads a configuration file to determine which entity to access. In simple words FNDLOAD is used to transfer entity data from one instance/database to other. for example if you want to move a concurrent program/menu/valuesets developed in DEVELOPMENT instance to PRODUCTION instance you can direct use this command.

Steps to Move a Personalization from one instance(Database) to other

Define your Personalization and save it in first instance
Connect to your UNIX box on first instance and run the following command to download the .ldt file

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME

Move the downloaded .ldf file to new instance(Use FTP)
Connect to your UNIX box on second instance and run the following command to upload the .ldt file

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

Note: Make sure you are giving proper .lct file in the commands and don’t confuse with .lct and .ldt files
These following are the other entity data types that we can move with FNDLOAD

1 - Printer Styles
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 - Lookups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND"
LOOKUP_TYPE="lookup name"

3 - Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

4 - Key Flexfield Structures
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 - Concurrent Programs
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"

6 - Value Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

7 - Value Sets with values
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

8 - Profile Options
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"

9 - Request Groups
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"

10 - Request Sets
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"

11 - Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

12 - Menus
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

13 - Forms Personalization
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME

Move Folder from one instance to another instance in oracle apps

Move Folder from one instance to another instance in oracle apps:

You can find the respected lct file under following path /u00000/oracle/developement/apps/apps_st/appl/fnd/12.0.0/patch/115/import, which are required to move anything from one instance to another instance in oracle apps.

To move folder three tables are mainly effected which are
FND_FOLDERS
FND_DEFAULT_FOLDERS
FND_FOLDER_COLUMNS

Download ldt file : FNDLOAD APPS/APPS 0 Y DOWNLOAD $FND_TOP/patch/115/import/fndfold.lct NEW_FOLDER.ldt FND_FOLDERS NAME=NEW

Upload ldt file : FNDLOAD APPS/APPS 0 Y UPLOAD $FND_TOP/patch/115/import/fndfold.lct NEW_FOLDER.ldt

Table Registration API

You register your custom application tables using a PL/SQL procedure in the AD_DD package.

Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert.

You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables. If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the registration, and then re-register the table or column. Remember, you should delete the column registration first, then the table registration. You should include calls to the table registration routines in a PL/SQL script. Though you create your tables in your own application schema, you should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API. You need not register views.
Procedures in the AD_DD Package

1. Procedure REGISTER_TABLE

procedure register_table ( p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

2. Procedure REGISTER_COLUMN

procedure register_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

3. Procedure DELETE_TABLE

procedure delete_table (p_appl_short_name in varchar2,
p_tab_name in varchar2);

4. Procedure DELETE_COLUMN

procedure delete_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);

To find out the table associated with which application, You can use these queries

select * from fnd_tables where table_name = 'CS_INCIDENTS_ALL_B'
select * from fnd_application where application_id = 170

Enable or Create User Hooks, JTF_USER_HOOKS

API User Hooks allow users to extend the business logic of the standard business rules that are executed by APIs. This is done by allowing custom procedures to be called at specific points in the standard APIs. For instance, a user may want to implement User Hooks for one of the following reasons:

To extend the validation of data beyond what the standard system provides.
To maintain data held in extra customer specific tables (not part of Oracle Applications) as it is entered.
To send alerts when particular events happen within Oracle Application
User Hooks offer similar functionality to the Custom Library, but at the database end rather than the front end.

Advantages
User Hooks allow extra business logic to be inserted in exactly the right place in the application without needing to bespoke any of the standard APIs. Upgrades are no problem as the hooks will be regenerated during the upgrade process. However, Oracle reserves the right to change the HR schema at any time, which may necessitate modifications to customized PL/SQL procedures.

The main advantages of hooks over custom library are that they only need to be implemented in one place whereas custom libary modifications could conceivably have to be made on several clients. They are also immediately available to any interface that calls the API. For example, Forms, Self Service, Data Pump, etc.


Enabling USER HOOKS
The Package CS_SERVICEREQUEST_PUB makes a call to the CS_SERVICEREQUEST_CUHK User Hook. This call is made depending on values stored in the table JTF_USER_HOOKS. For this call to be made, a row with Package Name as CS_SERVICEREQUEST_PUB and an appropriate API Name and the Enabled Flag set to 'Y' should be present in the Table.

Implementation Steps:
1) First you have to identify the particular API, which you are looking to modify/customize. The list of APIs, which support this concept, can be found in following table –
a)CRM Related APIs – Jtf_User_Hooks
You can use the following Query –
Select Pkg_Name,Api_Name ,processing_Type ,Execute_Flag From Jtf_User_Hooks

If User Hook Package has no Package Body
This is the Customer User Hook API. The Customers can add customization procedures here for Pre and Post Processing. Oracle only supplies the spec file.It is left to the customer to create the body for their customizations.

Example :You will need to make your own insert(s) into the JTF_USER_HOOKS table.

Example: Lets say you want to call your custom procedure after the charge details have been created. So, you want CS_CHARGE_DETAILS_CUHK.Create_Charge_Details_Post to be executed. To do this, you need to insert the following values into the jtf_user_hooks table

INSERT INTO jtf_user_hooks(USER_HOOK_ID

,PKG_NAME
,API_NAME
,PRODUCT_CODE
,PROCESSING_TYPE
,EXECUTE_FLAG
,USER_HOOK_TYPE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5)
VALUES(1000000 -- A large value so that no conflict
,'CS_CHARGE_DETAILS_PVT' -- Package Name
,'Create_Charge_Details' -- Procedure Name
,'CS' -- For Service
,'A' -- After, i.e. Create_charge_details_post
-- Replace A with B in the above line if you want to use Pre-User hook.
,'Y' -- Execute Flag
,'C' -- Customer User Hook
,1234 -- fnd_global.user_id
,sysdate
,1234 -- fnd_global.user_id
,sysdate
,fnd_global.login_id
,NULL
,NULL
,NULL
,NULL
,NULL);

JBO Date Conversion in OAF

Cast java.util.Date to oracle.jbo.domain.Date
1). dbDate= new oracle.jbo.domain.Date(new java.sql.Timestamp(inputDate.getTime());

2). Sample method
public oracle.jbo.domain.Date convertUtilToJboDate(java.util.Date pJavaDate)
{
return new oracle.jbo.domain.Date(new Timestamp(pJavaDate.getTime()));
}

Cast oracle.jbo.domain.Date to java.util.Date
1). Sample method
public java.util.Date convertJboToUtilDate(oracle.jbo.domain.Date pJboDate)
{
return new Date(pJboDate.timestampValue().getTime());
}

OA Framework Interview Questions

http://anuj-oaffaq.blogspot.com/

Oracle apps standard query

To Find invalid objects:
select owner, object_name, object_type,status from all_objects
where status != 'VALID' order by owner, object_type, object_name;

Validate username/password combination:
select fnd_web_sec.validate_login('AK185109','ak185109') from dual ;

If it return N then look the error message by this query
select fnd_message.get from dual;

Find the Instance:
select NODE_NAME, NODE_ID , SERVER_ID , SERVER_ADDRESS from FND_NODES;

Validate the ICX_PARAMETERS table:
select * from icx_parameters;

Find the Responsiblity :
select responsibility_id,responsibility_key,responsibility_name,description from fnd_responsibility_vl where responsibility_key = 'SERVICE'

Find the Menu, Function:
SELECT * fnd_responsibility_vl a,
fnd_menu_entries_vl b,
fnd_form_functions_vl c
where a.responsibility_id=20638
and a.menu_id=b.menu_id
and b.function_id=c.function_id