Sunday, September 13, 2009

Open Workflow status monitor diagram page in OAF

Create a VO for the query which is given below through this you can get the current diagram url,

select WF_MONITOR.GetDiagramURL(WF_CORE.Translate('WF_WEB_AGENT'),NtfEO.MESSAGE_TYPE,NtfEO.ITEM_KEY,'NO') monitor_url from
(SELECT ITEM_KEY,MESSAGE_TYPE FROM WF_NOTIFICATIONS WHERE NOTIFICATION_ID = :1) NtfEO

First find the button component of your page.
call VO with the input parameters which will return the diagram url.
Put that url into the java script function.

Through the java script you can open a new popup window which will display the status monitor diagram

OASubmitButtonBean reAssignBea1n = (OASubmitButtonBean) paramOAWebBean.findChildRecursive("WfMonDiagramCtrl");

Serializable[] parameters1 = { NotificationId };
Serializable aa1 = am.invokeMethod("getMonitorURL", parameters1);
if (aa1 != null && !aa1.toString().equals(""))
{
String url = "window.open('" + aa1.toString() + "')";
paramOAPageContext.putJavaScriptFunction("LaunchMonitor", url);
}

Determine the transaction state that is, whether changes have been made to view objects or not

Use the following methods:

ApplicationModule.getTransaction().isDirty() - This method tells you whether the transaction contains any changes in the view objects. This works for transactions made by entity object-based view objects only.

OAViewObject.isDirty() - This method tells you whether a particular view object contains changes or not. This works for both entity object-based view objects and view objects based on OAPlsqlViewObjectImpl. For view objects based on OAPlsqlViewObjectImpl, you can also use OAPlsqlViewObjectImpl.getState() method

Always call remove() after done using a dynamic view object

Always call VO.remove() after you are done using a dynamic view object:

ViewObject voobject = null;
try
{
voobject = mTransaction.createViewObjectFromQueryStmt("sql statement");
boolean exists = ( voobject.first() != null);
}
finally
{
voobject.remove();
}
Make sure to surround your VO.remove(); with finally.
If you don't, a java runtime exception could fire before you even get to your VO.remove().

Create a run time VO in OA framework

To create a run time VO you have to follow these steps which are given below:

OAApplicationModule am = pageContext.getApplicationModule(webBean);

OADBTransaction oadbtransaction = am.getOADBTransaction();
String validateStr1 = "select column_name from table_name where column_name = :1";

ViewObject validateViewObject1 = (ViewObject)am.findViewObject("ValidateCodeVO1");

if (validateViewObject1 == null)
validateViewObject1 = (ViewObject)am.createViewObjectFromQueryStmt("ValidateCodeVO1", validateStr1);

validateViewObject1 = (ViewObject)am.findViewObject("ValidateCodeVO1");

if (validateViewObject1 != null)
{
validateViewObject1.setWhereClause(null);
validateViewObject1.setWhereClauseParam(0, pageContext.getParameter("NtfId"));
validateViewObject1.executeQuery();
validateViewObject1.reset();

oracle.jbo.Row validaterow = validateViewObject1.first();

if (validaterow != null)
message_type = validaterow.getAttribute(0).toString();
}

Monday, September 7, 2009

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

Friday, September 4, 2009

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

Tuesday, August 11, 2009

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);

Saturday, August 1, 2009

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());
}

Tuesday, July 28, 2009

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

AQ, Agent and Subscription Creation Script

------------------------------------------------------------------------
-- 1. Check whether the Qtable exist, if not then create
-------------------------------------------------------------------------
l_qtable_name:='QTABLENAME';
l_q_name:='QUEUENAME';

SELECT COUNT(*)
INTO v_q_table_count
FROM user_tables
WHERE table_name = l_qtable_name;

DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => l_qtable_name, multiple_consumers=> TRUE, queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE');
DBMS_AQADM.CREATE_QUEUE (queue_name => l_q_name, queue_table =>l_qtable_name,retention_time=> 86400);
DBMS_AQADM.START_QUEUE (queue_name => l_q_name);

v_q_subscriber := sys.aq$_agent('USERNAME', NULL, NULL);
dbms_aqadm.add_subscriber(queue_name => l_q_name, subscriber => v_q_subscriber);
DBMS_OUTPUT.PUT_LINE('Subscribed to USER = USERNAME' );

Open Cursor Per session

declare cursor c1 is
select distinct(s.sid) sid,s.serial# serial, a.value cumulative, c.value current_open
from v$sesstat a, v$statname b,v$sesstat c, v$statname d,v$session s
where a.statistic# = b.statistic#
and c.statistic# = d.statistic#
and s.sid=a.sid
and s.sid=c.sid
and b.name = 'opened cursors cumulative'--current'
and d.name = 'opened cursors current'
and s.machine like '%machine name%'
and s.program like 'JDBC%'
and s.module like 'JDBC%'
order by s.sid;
begin
for eachrow in c1 loop
dbms_output.put_line(to_char(systimestamp,'DDMMYYYYhh24mi')||' '||eachrow.sid||' '||eachrow.serial||eachrow.cumulative||' '||eachrow.current_open);
end loop;
end ;

JDR UTILITY in OAF

In Oracle OA Framework, is the MDS page/document definition stored in database or in the file system?

The MDS document details are loaded into database, in the following sets of tables.
JDR_ATTRIBUTES
JDR_ATTRIBUTES_TRANS
JDR_COMPONENTS
JDR_PATHS
The Document is loaded via XMLImporter.

and Through the JDR API you can get all this information from the database with the help of these statement..

jdr_utils.listdocuments('/oracle/apps/cs/', TRUE);
jdr_utils.listdocuments('/oracle/apps/cs/pagging//server/',TRUE);
jdr_utils.listcustomizations(p_document => '/oracle/apps/cs/pagging/webui/CustomPaggingPG');
jdr_utils.printdocument(p_document => '/oracle/apps/cs/pagging/server/CustomPageAM');
jdr_utils.printdocument(p_document => '/oracle/apps/cs/pagging/webui/CustomMailPG');
jdr_utils.deletedocument(p_document => '/oracle/apps/cs/pagging/server/CustomPageAM');

Logging in the OAF

logging in the OAF

You can log your message in OAF page using writeDiagnostics method

pageContext.writeDiagnostics(this,"building :"+s_building,OAFwkConstants.STATEMENT);
Syntax:void writeDiagnostics(Object module,String messageText,int logLevel)

Writes an entry to the diagnostics log according to a specific log level.

Parameters:module - current module, usually the "this" pointermessageText - message to be included in the log. Limit 4000 characters.logLevel - category or type of log message. Valid values are are from OAFwkConstants. (UNEXPECTED, ERROR, EXCEPTION, EVENT, PROCEDURE, STATEMENT, PERFORMANCE

To log bc4j objects in OAF
Pagecontext will not be available for AM, View Object Impl and Entity object Impl. So in this case, you can use OADBTransactionImpl.writeDiagnostics to log your messages.

oracle.apps.fnd.framework.server.OADBTransactionImpl

if (OADBTranasctionImpl.isLoggingEnabled(OAFwkConstants.PROCEDURE))OADBTransactionImpl.writeDiagnostics(this, "your message", OAFwkConstants.PROCEDURE);

Now you will be able to see the application log appened to the bottom of the page.

Then set profile option "FND: Debug Log Level" (at user-level).

Finally query FND_LOG_MESSAGES in the database for all the debugging messages:

SELECT module, message_textFROM fnd_log_messagesWHERE user_id = AND timestamp > SYSDATE - (1/24/60) -- in the past minute; this cuts down the number of recordsORDER BY timestamp;

How to see the log?

You can see the output in the OAF Screen itself.
1. Select Diagnostics button from any page
2. In the Diagnostics page select Show Log on Screen option

Pl/SQL Debugging/Logging method

What will you do If you want to debug your pl/sql code? You have to create you own debugging technique, but Oracle Applications module used its own debugging technique.

So We can also use the same.

Please read the below question and answer, and Use this If it is useful to you.
What is the use of FND Debug Log?
1. It helps you pinpoint the cause of error in standard Oracle Code, by making debug messages to appear in a centralized table named FND_LOG_MESSAGES.
2. You can design and build your custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code.

Where is the debug message stored, once the logging is turned on?
Debug messages are stored in a table called FND_LOG_MESSAGES
A program written in any technology, either form, or report, or pl/sql or java concurrent program or OAF…all their debug messages will be stored in fnd_log_messages.

How to debug the issue being faced in Oracle Application?
Step 1 Set the following profile options at your user level(your fnd_user)
FND: Debug Log Level
Following possible values are available, but I suggest you set this to "Statement" level when debugging code.
LEVEL_UNEXPECTED : Internal Level Id is 6
LEVEL_ERROR : Internal Level Id is 5
LEVEL_EXCEPTION : Internal Level Id is 4
LEVEL_EVENT : Internal Level Id is 3
LEVEL_PROCEDURE : Internal Level Id is 2
LEVEL_STATEMENT : Internal Level Id is 1
FND: Debug Log Enabled
Set this profile to Yes
FND: Debug Log Module
Set this to %
Step 2
Login to the application and reproduce the problem.
Step 3
SELECT * FROM fnd_log_messages WHERE user_id = 209122 /*your FND_USER user_id here*/
AND TIMESTAMP > SYSDATE - .3
ORDER BY log_sequence DESC /*note the order by clause here*/
The result of this select statement will provide the list of all the debug messages, on top will appear the most recent debug messages..
Why should I setup the module name to %, in profile option?
You can set this to po%, if you know for sure that the error was caused by code written in po module. However po code might be internally calling hr code which might inturn be calling fnd code.
Hence it’s best to set this profile value as %.
You may also use comma delimited values i.e po%,hr%,fnd%
Why must I bother debugging Oracle's Standard code when I can quickly raise a tar.
If the issue is with Standard Oracle Code, first thing you must do is to search into Metalink. However having the debug information on error helps your searching ability further. Uploading the debug messages upfront during Tar creation will also help Oracle speedily understand and fix your issues.
Why to set the profile option to statement level?
This profile option has following main levels.-
Error
Warning
Procedure
Statement
I like setting this to "Statement" level as it extracts debug messages at all levels, in one glance. You can latter filter those debug messages by using below SQL for example
select * from fnd_log_messages where user_id = 111 and LOG_LEVEL =5
What if the piece of code causing the error is not appearing in fnd_log_messages?
This is very much possible. The fnd_log_messages might have helped you get close to the culprit piece of code , but may not be able to pinpoint the error as there may not be enough debug messages implanted by Oracle.
You can do one of the below:-
A. Run the database sql trace for the session with bind variables and see the last meaningful SQL statement in the raw trace file.
Please note that PL/SQL statements will not appear in trace, only the SQL Statements will appear, hence you may consider option (b) below
B. Add your own debug messages to the pl/sql code that was delivered by oracle, which you suspect is causing problem. This is a temporary change, and must only be done on development environment, NEVER DO THIS CHANGE ON PRODUCTION.
The size of table FND_LOG_MESSAGES will keep on increasing?
You can run concurrent program "Purge Debug Log and System Alerts".
I have written a pl/sql concurrent process to interface Purchase Orders from 3rd Party System. How will add debug messages?
fnd_log.STRING(log_level => fnd_log.level_statement ,module => 'xxpo.packagename.procedurename' ,message => 'debug message here');
Will the above debug command create an entry into fnd_log_messages ?
Debug records will be created in fnd_log_messages if and only if you run the interface program after setting the profile options as suggested above.
What if a rollback occurs due to unhandled exception. Will the inserts done to fnd_log_messages be lost?
fnd_log.string eventually calls procedure FND_LOG.STRING_UNCHECKED_INTERNAL2. This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.
Hence your debug messages will not be lost despite a rollback in parent session.
What if an exception is incurred within the Debug Logging API itself?
Oracle takes well care of this by handling the WHEN OTHERS exception.
It is evident from below Oracle Code for debug API
PROCEDURE STRING(LOG_LEVEL IN NUMBER,
MODULE IN VARCHAR2,
MESSAGE IN VARCHAR2) is
begin
/* Short circuit if logging not turned on at this level */
if (LOG_LEVEL < log_level =""> LOG_LEVEL,
MODULE => MODULE,
MESSAGE_TEXT => MESSAGE);
end if;
exception
when others then
NULL; /* supress the exception */
end;

Monday, January 26, 2009

Helpful layer in oracle application

When developing with the Oracle eBusiness Suite, there are situations where Developers can use "hooks" in standard Applications code to insert or redirect to custom code. Oracle Applications provide some helpful layers of abstraction to make life easier for customization. Where possible it is nice for this to be done using a supported method. If a true blue customization is required, then its best to try to preserve the existing code so that any future upgrades are more likely to break your custom code by reverting to standard code, plus if you use a "hook" to independent code it is easier to maintain and upgrade the custom code. I regularly use a "copy and modify" approach to Apps Development where I use hooks to call the new code.
  • Concurrent Program Executable. Where an concurrent program is automatically submitted from a form or another concurrent process, e.g. "Print Pack Slip" in Oracle Shipping actions, as long as parameter requirements are the same, then a quick an easy method to code your own report is:
    1).Create a new executable registered under your custom application, e.g. XMODS_WSHRDPAK
    2).Query the called concurrent program and update the executable to your custom executable,e.g. Query WSHRDPAK and replace executable with XMODS_WSHRDPAK
  • Personalization - Forms and Framework (OAF) plus CUSTOM.pll. Forms PL/SQL Library. Personalization has provided functionality to cover a lot of the customizations traditionally coded to CUSTOM.pll, but both Personalization and CUSTOM.pll provide a number of hooks into the front end logic.
  • Database Triggers. Triggers on tables used carefully can provide hooks where all other methods don't dare to tread. Of course watch out for the exception and try to avoid putting triggers on fnd_concurrent_requests!
  • Menu. Provides a prominent method to call new Forms, while potentially hiding behind the same prompt, Reports, Discoverer Workbooks, external links/URLs, etc. Gives the ability to save in the "Favourites" list. If you need to customize a standard form, copy it and create a new menu entry where possible.
  • Unix Softlink. An alternative to the concurrent program executable hook.
    1). Backup an existing standard Oracle file.
    2). Remove the existing standard Oracle file.
    3). Replace with a softlink to a file under you custom application code "top" directory
  • Workflow. Workflow customizations are "allowed" and a method I use here is to take a standard function call in workflow, copy and modify the underlying package, then change the function call in workflow to the custom function. E.g. AP Remittance Advice workflow (APPEWF) has "Get Check Info" function calling AP_PAYMENT_EVENT_WF_PKG.get_check_info. Copy AP_PAYMENT_EVENT_WF_PKG to XMODS_AP_PAYMENT_EVENT_WF_PKG and change function in workflow function "Get Check Info"
  • Printer Driver. Whenever concurrent request file post processing is required, e.g. to FTP or email a file - printer drivers provide an excellent way to perform post processing. Create a new printer driver with the appropriate command. Remember to restart the concurrent manager to pickup updates to print drivers.
  • Business Events. Not a widely used mechanism, but provides supported hooks into key events such as Payment Confirmations (AP Payment event).