How to Test or Create DBC File on Oracle Apps 11.5.10

Here is a way you can test & create DBC file from the application.

First of all Login to Oracle application for which you want to generate the DBC file referring below URL

http://hostname:port /OA_HTML/ jsp/fnd/aoljtest.jsp

like

http://anil.apps.com:8000/OA_HTML/jsp/fnd/aoljtest.jsp

In the below page enter the required details such as Apps schema name, Password, SID etc and click on Test

 

 

Navigate to end of the page and click on Enter AOL/J Setup Test

On the next screen you can click on Locate DBC File. Here you can see  DBC file you can copy these contents and make a DBC file to be used in OAF development by putting it under $JDEV_USER_HOME/dbc_files/secure folder

 

We can click on Next link to go to Verify setting in DBC file

 

We can click on next to test our Application login.

 

Click on Test Application Login.

Similarly, you have several options on left hand side that you can use as shown below.

Cheers :)

--Anil

EO based VO Extension in OAF – R12

Thanks Ajay for sharing this wonderful article on View Object extension

In the article we are going to extend QueryVO of EmployeeSearch page.

Our business requirement is to add employee Title and MiddleName column in the results table region

Analyzing the Page

click on “About this Page” link to check that which ViewObject is associated with the table region [ResultsRN]

Here we can see that results table has QueryVO attached to it. Now click on Business Component Reference details and check the path of QueryVO

So now click on QueryVO which is an EO based [FwkTbxEmployeesEO] to check whether it has those attribute or not those we want to add

Here we can see that our VO does not have Title & MiddleName attribute.

So for adding these attribute we need to perform VO Extension.

Now FTP the complete solution package (i.e. oracle.apps.ak.solution) from Application server into myclasses\oracle\apps\ak folder as well as in myprojects\oracle\apps\ak folder.


Open Jdeveloper and create a new OA workspace and project. Name workspace as Extend.jws and project as Extend.jpr.

Now right click on Extend project to open project properties Project ContentAdd to include the standard VO package in current project.

After adding BC4J package i.e. oracle.apps.ak.solution project looks like

Creating New View Object (VO): -

Right click on your project and select New-->Adf business Components-->View Object

Give the package name as oracle.apps.ak.ajay.employee.server, VO name as ExtQueryVO and in the extends property select QueryVO

The next screen allows the addition or deletion of Entity Objects. No change is required here for this extension so simply select Next.

The next pane allows additional attributes to be added or attributes to be removed. In this case add both the MIDDLE_NAMES and TITLE attributes to the selected list

Please note the new attribute you would be adding will be coming as Transient

Click on Next --> Next

Modify your sql statement to add new created attributes

SELECT FwkTbxEmployeesEO.EMPLOYEE_ID, 
       FwkTbxEmployeesEO.FULL_NAME AS EMPLOYEE_NAME, 
       FwkTbxEmployeesEO.EMAIL_ADDRESS AS EMPLOYEE_EMAIL, 
       FwkTbxEmployeesEO1.EMPLOYEE_ID AS MANAGER_ID, 
       FwkTbxEmployeesEO1.FULL_NAME AS MANAGER_NAME, 
       FwkTbxEmployeesEO1.EMAIL_ADDRESS AS MANAGER_EMAIL,
       FwkLookupCode.MEANING AS POSITION_DISPLAY,
FwkTbxEmployeesEO.Middle_Names,FwkTbxEmployeesEO.Title
FROM   FWK_TBX_EMPLOYEES FwkTbxEmployeesEO,
       FWK_TBX_EMPLOYEES FwkTbxEmployeesEO1,
       FWK_TBX_LOOKUP_CODES_VL FwkLookupCode
WHERE  FwkTbxEmployeesEO.MANAGER_ID = FwkTbxEmployeesEO1.EMPLOYEE_ID (+)
       AND FwkTbxEmployeesEO.POSITION_CODE = FwkLookupCode.LOOKUP_CODE
       AND FwkLookupCode.LOOKUP_TYPE = 'FWK_TBX_POSITIONS'

Click on nextNext

Generate RowImpl and click on finish


Now as our attributes were created as Transient hence to fix this issue we have two options:

1. We need to correct our XML file generated as it is a known error with Jdeveloper 10G & earlier versions.
2. Double click on that attribute i.e MiddleName & Title and in the View Object Attribute window, enter attribute name  in 'Expression' column i.e. Title.

Open your ExtQueryVO.xml file in some notepad editor.
Scroll to bottom of that file
Update

With

Save the xml file.
And restart your jdeveloper and check that in attributes its not showing as transient.

Substitute Your New VO for the Parent VO

Now we need to perform the VO substitution For this we will right click on Extend project and open project properties-->Business Components-->Substitutions.

Select the QueryVO in Available and ExtQueryVO in Substitute list and click on Add button and then press Ok button.

FTP VO.xml, .class, .jpx files to $JAVA_TOP appropriate folder and Run jpximporter

java oracle.jrad.tools.xml.importer.JPXImporter $JAVA_TOP/Extend.jpx -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***)(PORT=****))(CONNECT_DATA=(SID=***)))"

Personalize the page to create new items:-

On QueryPG and click on personalize page  complete view and click on Create Item on Table: Employee Table

Set the item style as messageStyledText. Give the id, prompt , View Instance and View Attribute.

Click on Apply -> return to application. You can notice that the page has new two columns.

That's All :)

AM Extension in OAF

                        Application Module Extension in OAF

Like the Controller extension, AM extension is also not supported by Oracle. However for some business needs we have to extend it sometime.

In this exercise we have taken the standard LabSolutions project (shipped in Oracle Tutorial) for extending application module i.e. EmployeeAM.

This AM can be found under below BC4J package
oracle.apps.fnd.framework.toolbox.labsolutions.server.EmployeeAM

Why we are extending AM:-

This EmployeeAM contains an apply method which subsequently commits the transaction.

//This is standard apply method in EmployeeAM that we are overriding.
public void apply()
  {
    getTransaction().commit();
  }

Our business need is to capture user name and user id at runtime and insert it into a custom table for AUDIT PURPOSE.

Here is the table script

CREATE TABLE xx_audit
(user_id VARCHAR(50),
user_name VARCHAR(50),
last_update_date DATE,
last_update_login NUMBER,
last_updated_by NUMBER,
creation_date DATE,
created_by NUMBER);

Brief Steps of AM Extension with screenshots:-

Step 1:

First create a new workspace and a project for extension

Step 2:

Create two BC4J packages as shown below

oracle.apps.fnd.framework.toolbox.labsolutions.server

This will have the standard AM. Copy the Standard AM files to the BC4J package created above.

and

xx.oracle.apps.fnd.framework.toolbox.labsolutions.server

This package will contain the extended AM.


Step 3:

Now create a new Application module in custom package and set its extends property as shown below

Now in XXEmployeeAMImpl we are overriding the apply method to store the data in custom table.

import oracle.apps.fnd.framework.OAException;
import java.sql.PreparedStatement;
import java.sql.Connection;
public class XXEmployeeAMImpl extends EmployeeAMImpl
{
public void apply()
  {
java.sql.Date d = getOADBTransaction().getCurrentDBDate().dateValue();
try 
{ 
Connection conn = getOADBTransaction().getJdbcConnection(); 
String Query = "insert into xx_audit values(:1,:2,:3,:4,:5,:6,:7)"; 
PreparedStatement stmt = conn.prepareStatement(Query); 
stmt.setInt(1, getOADBTransaction().getUserId()); 
stmt.setString(2, getOADBTransaction().getUserName()); 
stmt.setDate(3, d); 
stmt.setInt(4, getOADBTransaction().getUserId()); 
stmt.setInt(5, getOADBTransaction().getUserId()); 
stmt.setDate(6, d); 
stmt.setInt(7, getOADBTransaction().getUserId()); 
stmt.execute();
} 
catch(Exception exception) 
{  
throw new OAException("Error in Staffing Query"+exception, OAException.ERROR); 
} 
 super.apply();
  }
}

Step 4:

Perform the AM substitution
Double click on project.jpx to open below model window

Step 5:

Run jpximport command

C:\jdevbin\jdev\bin>jpximport C:\jdevhome\jdev\myprojects\LabSolutions.jpx -user
Id 1 -username apps -password apps -dbconnection "(description = (address_list =
 (address = (community = tcp.world)(protocol = tcp)(host =ANIL.apps.com)(port =
1521)))(connect_data = (sid  = VIS)))"
Imported document : /oracle/apps/fnd/framework/toolbox/labsolutions/server/custo
mizations/site/0/EmployeeAM
 Import completed successfully