Execute sql script in OAF

// Required import packages
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;   
import oracle.jbo.domain.Number;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//


try
   {
    Connection conn = paramOAPageContext.getApplicationModule(paramOAWebBean).getOADBTransaction().getJdbcConnection();

    String Query ="SELECT empId resultId,empName resultName,empPosition resultPos from XXXXX_TABLE WHERE DeptId=:1";
                                   
    PreparedStatement stmt = conn.prepareStatement(Query);               

    stmt.setInt(1, departmentId);                                       

                                   
    for (ResultSet resultset = stmt.executeQuery(); resultset.next(); )
    {   
        int employeeId = resultset.getInt("resultId");
        String employeeName  = resultset.getstring("resultName");
        String employeePosition = resultset.getString("resultPos");
    }

    stmt.close();
                                              
   }catch(SQLException sqle)
   {
       throw new OAException("Exception Block"+sqle);
   }

PL/SQL Program unit call from OAF

Writing bigger program logic in controller will be bit complex, To resolve such cases write logic in PL/SQL program units and invoke this from OAF pages.In next lines I am writing the code to call pl/sql in oaf pages.


// Required import packages
import oracle.apps.fnd.framework.server.OADBTransaction;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;   
import oracle.jdbc.OracleCallableStatement;
import oracle.jbo.domain.Number;
//

OAApplicationModule localOAApplicationModule = paramOAPageContext.getApplicationModule(paramOAWebBean);
OADBTransaction oadbtransaction =
(OADBTransaction)((OAApplicationModuleImpl)localOAApplicationModule).getDBTransaction();
String callSQLPackage = "begin XXXXX_CUSTOM_PACKAGE.XXXXX_CUSTOM_PROCEDURE(:1,:2,:3,:4,:5);" + " end;";
oraclecallablestatement = (OracleCallableStatement)oadbtransaction.createCallableStatement(callSQLPackage,1);
try
{
      // Set IN Parameters               
      oraclecallablestatement.setString(1,in1Parameter);                               
      oraclecallablestatement.setString(2,in2Parameter);
      
      // Set OUT Parameters
      oraclecallablestatement.registerOutParameter(3,Types.INTEGER);             
      oraclecallablestatement.registerOutParameter(4,Types.INTEGER);             
      oraclecallablestatement.registerOutParameter(5,Types.VARCHAR,0,2000);
                 
      oraclecallablestatement.execute();
 
      // Extract the OUT parameter value
    int out1Param = oraclecallablestatement.getInt(3); 
    int out2Param = oraclecallablestatement.getInt(4);
    String out3Param = oraclecallablestatement.getString(5);
          
 }catch(SQLException sqle)
 {
    throw new OAException("Exception Block"+sqle);
 }

Import OAF Personalization

OAF provides 2 java program to import any personalization. This is equivalent to FNDLOAD command in oracle apps to handle any LDT Files.These java programs used to import XML and JPX files.

XML Files >> OAF personalization can be retrieve as XML files 

JPX Files >> Substitution definition stored in JPX files. 

Use below command to import OAF personalization -

 

>java oracle.jrad.tools.xml.importer.JPXImporter JpxFileName  DBConnection

>java oracle.jrad.tools.xml.importer.XMLImporter  XmlFileName DBConnection -rootdir CustomPersonalizationFolder -rootPackage StandardPersonalizationFolder

DBConnection 

  -username $APPS_USER -password  $APPS_USER_PASSWORD -dbconnection "(DESCRIPTION= (ADDRESS=(

PROTOCOL=TCP)(Host=$DB_HOST_NAME)(Port=$PORT_NUMBER))(CONNECT_DATA=(SID=$SID)))"

StandardPersonalizationFolder

/oracle/apps/icx/por/approval/webui/customizations/site/0
 

CustomPersonalizationFolde

$JAVA_TOP/xxxxx/oracle/apps/icx/por/approval/webui/customizations/site/0

VO Extention

View Object Exception -

VO extension is very useful, when need to add a new item to a region. Extend the VO and include the respective column in sql statement. Below are the steps need to follow in order to extend any VO.

1) Go to About This page and check the VO name being used for that region

2) FTP to JAVA_TOP to download the VO and dependent files.

3) De compile the class files and generate the source code

4) Create the same file structure in my projects under Jdeveloper folder

5) Create a new View Object in same structure under your custom path

6)  Include the new column in SQL query  and finish

7) Open the project properties and click on BC4J component

8) Select substitution and choose standard view as source and custom extended view as substitute

9) Project_Name.jpx file will be generated in myprojects

10) FTP back new extended view and subsequent class files to correct location on server

11) Also place the server.xml file

12) Run the JPXimporter

13) Go to Application and personalize the region

14) Create new item with viewSource = Standard VO Name and viewAttribute= newly added column name

Example -

Requirement – Introduce a new column in My Requisition Table Region to display the Item number in comma separated concatenated format  This can be achieved by extending underlying VO, Below are the steps.

Write a program unit to get required values. Input parameter must be in existing VO columns.

CREATE OR REPLACE FUNCTION APPS.Sandeep_Test_prog(param IN VARCHAR2)  
 RETURN VARCHAR2 AS

         CURSOR cur_item_desc(p_segment1 IN VARCHAR2) IS
         SELECT NVL(item_description,'Sandeep') item_desc
         FROM po_requisition_lines_all a,po_requisition_headers_all b
         WHERE a.requisition_header_id = b.requisition_header_id
         AND b.segment1=p_segment1;

         CURSOR cur_item_number(p_description IN VARCHAR2) IS
         SELECT DISTINCT NVL(segment1, 'Sandeep') itemNumber
         FROM mtl_system_items_b
         WHERE UPPER (description) = UPPER (p_description);

           p_item_number VARCHAR2 (100) := null;
 

BEGIN
        FOR c1 in cur_item_desc(param)
        LOOP
                   dbms_output.put_line('Outer Loop>>'||c1.item_desc);
                    FOR c2 in cur_item_number(c1.item_desc)
                    LOOP
                               dbms_output.put_line('Inner Loop >>'||c2.itemNumber);
                               IF c2.itemNumber<>'Sandeep' THEN
                                    IF p_item_number IS NULL THEN
                                              p_item_number := p_item_number ||c2.itemNumber ; 

                                    ELSE
                                              p_item_number := p_item_number||','|| c2.itemNumber ;
                                    END IF;
                               ELSE
                                    p_item_number := p_item_number ||',' ;
                               END IF;

                      END LOOP;
             END LOOP;

        RETURN p_item_number;
EXCEPTION
         WHEN OTHERS  THEN
             DBMS_OUTPUT.put_line ('Error');
             RETURN 'Sandeep NULL';
END;

Check the VO name required to be extended.Click on About This Page

VO being used for these beans values is MyReqsGlanceVO

Check the full path in Business Component References Details

oracle.apps.icx.por.reqmgmt.server.MyReqsGlanceVO

 

FTP VO and respective VOImpl and VORowImpl class files. Create a similar package structure in JDev myprojects forlder and place the all files and create a new view object.

Modify the SQL and include custom column

 Some time you may encounter below error message . To avoid it keep sql query as original and press next till last page. Then come back to SQL page and do the your changes and then press next. It won’t come again.

Press next till reach below page and click the checkbox Generate Java file for ViewRowClass and finish.

This will generate VOImpl and VORowImpl java files also. Now open the Project Properties.

Business Components  >> Substitutions >> Available ( Choose the standard VO ) >> Substitute (Pick the extended VO).

If this is new substitution, ADD button will be enabled else UPDATE.

This will generate a OAProject10.jpx and server.xml files. FTP all files at server and run JPX importer to upload the import.

Now Go to Application

Click on Personalize “This Table display a list…..” link

Click create Item icon for Advanced Table

Give the ID Name and Apply – It will create a new column in Advanced Table

Now click new Item icon for TestColumn – It will create a new item for that column. Also create a new item in columnHeader and give a prompt there, to display the column title.

Select the Item style and give the name of item.View Instance hold the name of standard VO and view attribute is custom column name included by us.

Now return to application

New column ColumnHeader holding Item number appears.

OAF Page Customization

First the basic difference between personalization and customization !
Personalization is oracle provided flexibility to change standard behavior as personal need. using personalization we do not require to write any custom code.while Customization is process to write custom code to fulfill our need.

Below are the 3 basic components of OAF pages.
1) View Object (VO) ,
2) Controler (CO) and
3) Application Module (AM)  , components in  MVC architecture.    

in OAF Customization we need to write custom code for any of the above 3 based on the requirement.

Now checkout the existing CO or AM name (From About this page at the left below of each html page) and download the class file then ftp to your local system.

(assume QuickUpdateCO.java is standard file)

use any decompiler and get the source java code and follow below steps -
1) Rename the standard CO/AM (QuickUpdateCO.java) with custom name XXXXXQuickUpdateCO.java
2) extend the standard CO/AM
3) import the standard package
4) define constructor for the custom program
5) define the package name (location where code resides)

package xxxxx.oracle.apps.pos.supplier.webui;
import oracle.apps.pos,suppliers.webui.QuickUpdateCO.java;
XXXXXQuickUpdateCO.java extends QuickUpdateCO.java;
{
XXXXXQuickUpdateCO{};
ProcessRequest(){
super();
};
ProcessFormRequest{
super();
};
} 

6) Write code in processRequest (if customization need to be invoked on page display) or in processFormRequest (if customization required on any event on the page).
7)FTP the new java code and compile using javac XXXXXQuickUpdateCO.java
8)Place the class file at xxxxx.oracle.apps.pos.supplier.webui (package defined at top of CO)

CO -:
a) Click on personalization link
b) Click on pencil button against page layout property
c) Give the new controller name along with package name at the desired level 
d) Tab out and apply

VO/AM-:
Oracle does not provide option to extend the VO and AM using personalization.Though there is concept of SUBSTITUTION which is being used to extend the standard VO and AM.
You can use JDeveloper to generate the substitution and then import substitution using  jpximport .

OAF Personalization & Migration

1)      OAF bean Personalization –

Objective – Make OAF bean read only through personalization

Bean Name – Search Published Negotiations available on Sourcing Page

1)      Open the page where personalization is required and Click on Personalize Page link available on right top of the page

2)      Check for the target bean and click the pencil symbol

 

3)      Check for the read only property 

4)       Set the property value for Read Only = TRUE at Responsibility level and press apply button.

5)      Return to Application and check the personalized bean 

1)      2) Migration of OAF Personalization –

Responsibility Purchasing Super User –

1)      Personalized the header detail of supplier page to make fields read only

2)      Check the personalization from back end

BEGIN

JDR_UTILS.LISTCUSTOMIZATIONS('/oracle/apps/pos/supplier/webui/QuickUpdatePG');

END;

/

/oracle/apps/pos/supplier/webui/customizations/responsibility/2007/QuickUpdatePG

** 2007 – Responsibility ID

3)      Check the xml code generated by system for personalization

BEGIN

JDR_UTILS.PRINTDOCUMENT('/oracle/apps/pos/supplier/webui/customizations/responsibility/2007/QuickUpdatePG');

END;

     <?xml version='1.0' encoding='UTF-8'?>

<customization xmlns="http://xmlns.oracle.com/jrad" version="9.0.6.0.0_35" xml:lang="en-US" customizes="/oracle/apps/pos/supplier/webui/QuickUpdatePG">

   <modifications>

      <modify element="SupplierName" readOnly="true"/>

 

</modifications>

</customization>

4)      Save the above xml code as QuickUpdatePG.xml

5)      Create the below files structure at your local disk and place the QuickUpdatePG.xml in 2007/

     xxXXX/oracle/apps/pos/supplier/webui/customizations/responsibility/2007/

6)      Create another folder under responsibility named 2008 for responsibility Payables Manager and place the QuickUpdatePG.xml

   xxXXX/oracle/apps/pos/supplier/webui/customizations/responsibility/2008/

7)      Zip the xxXXX folder to xxXXX.zip

8)      Place the xxXXX.zip file and install script (XXXXX_OAF_PERS_RESP_LEVEL.sh) to unix box

9)      Execute script - XXXXX_OAF_PERS_RESP_LEVEL.sh with following parameters

1)      APPS username

2)      APPS pwd

3)      DB IP Address – Host Name

4)      DB Port Number

5)      DB SID

6)      Responsibility Id – Target Responsibility Id

7)      Log File Name

POST Successful execution of Install script -

Responsibility XXX Co Payables Manager –

10)   Check the personalization from back end

BEGIN

JDR_UTILS.LISTCUSTOMIZATIONS('/oracle/apps/pos/supplier/webui/QuickUpdatePG');

END;

/

/oracle/apps/pos/supplier/webui/customizations/responsibility/2007/QuickUpdatePG

/oracle/apps/pos/supplier/webui/customizations/responsibility/2008/QuickUpdatePG

** 2008– Responsibility ID for XXX Co Payables Manager

11)   Check the xml code generated by system for personalization

BEGIN

JDR_UTILS.PRINTDOCUMENT('/oracle/apps/pos/supplier/webui/customizations/responsibility/2008/QuickUpdatePG');

END;

<?xml version='1.0' encoding='UTF-8'?>

<customization xmlns="http://xmlns.oracle.com/jrad" version="9.0.6.0.0_35" xml:lang="en-US" customizes="/oracle/apps/pos/supplier/webui/QuickUpdatePG">

   <modifications>

      <modify element="SupplierName" readOnly="true"/>

     

</modifications>

</customization>

12)   Sometime personalization does not reflect immediate to the target responsibility. Please

Logout and login again to application. Open the Page, click on personalization link.

Click on pencil symbol for any personalized item check the field value and press apply. 

1)    3) OA Personalization using Functional Administrator Responsibility

a)      Login to Application

b)      Select Functional Administrator Responsibility

c)       Click the Personalization Tab then Application Catalog

d)      Give the complete path with page name in Document Path field and press Go

e)      If there exist any personalization It has Personalized = Yes otherwise No.

f)       To do any personalization on this page click the pencil icon on Personalize Page, It will navigate you to same page as you click on personalization link on this page

g)      Using Manage Personalization pencil icon, you can delete or activate/deactivate the existing personalization.

Import Export of OA Personalization –

1)      Set the path to profile FND: Personalization Document Root Path (path where the exported personalization will reside)

2)      Click on Import/Export link followed by Personalization Repository link

3)      Search for your personalization and select those need to be exported

4)      Click Export To File System button

5)      Personalized file ( with xml extn) with full repository structure exported to given path

6)      To import the existing personalization place the xml file with complete file structure to the root path defined

7)      For single file import select the check box or directly check the root path for all files and click Import From File System.

SQL Script to Delete Personalization –

Begin

jdr_utils.deletedocument(p_document=>'/oracle/apps/pos/supplier/webui/customizations/site/0/QuickUpdatePG');

END;

/

XML Publisher - A Beginning ....

Basic steps to create BI Reports - 

1) Create the simple oracle report (rdf file) 

2) create concurrent program with executable as oracle report

3) set the output format as XML

4) run the program. output report will generate the xml tags

5) Now create Data Definition with code as concurrent program name in xml publisher admin(R)

6) create the rtf template (in word/excel/pdf)

7) use can xml tags generated in step 3 as data source to validate the output in rtf template

8) create the template with default output format and associate with created data definition and   attach the rtf file.  now run the concurrent program output will be in the specified format.

Better Coding Practice - To Capture log messages

Once the code in production and stuck with some issue.tough to debug.So the best practice to write custom programs is -
1) Create a profile with default value 'N'
2) Put debug statement based on profile value
       IF fnd_profile.value('Custom Profile') = 'Y' THEN
           Interface - FND_FILE.PUT_LINE(FND_FILE.LOG,'Debug Level 1' );  
           Reports -  SRW.MESSAGE(1,'Debug Level 1')     
      END IF;
3) Run interface by setting the profile value 'Y'