Inserting Data into OAF

Create Data Entry OAF Page

1. Create a New Workspace and Project

Right click Workspaces and click create new OAworkspace and name it as Insert. Automatically a new OA Project is also created. Name the project as InsertDemo and package as prajkumar.oracle.apps.fnd.insertdemo

2. Create a New Application Module (AM)

Right Click on InsertDemo > New > ADF Business Components > Application Module

Name -- InsertAM

Package -- insert.oracle.apps.fnd.insertdemo.server

3. Enable Passivation for the Root UI Application Module (AM)

Right Click on InsertAM > Edit InsertAM > Custom Properties >

Name – RETENTION_LEVEL

Value – MANAGE_STATE

Click add > Apply > OK

4. Create Test Table in which we will insert data (For Testing Purpose)

CREATE TABLE xx_insert_demo
(        -- ---------------------
         -- Data Columns
         -- ---------------------
         column1                           VARCHAR2(100),
         column2                           VARCHAR2(100),
         -- ---------------------           
         -- Who Columns            
         -- ---------------------          
         last_update_date          DATE            NOT NULL,
         last_updated_by           NUMBER     NOT NULL,
         creation_date                 DATE            NOT NULL,
         created_by                      NUMBER     NOT NULL,
         last_update_login        NUMBER
); 

5. Create a New Entity Object (EO)

Right click on InsertDemo > New > ADF Business Components > Entity Object

Name – InsertEO

Package -- insert.oracle.apps.fnd.insertdemo.schema.server

Database Objects -- XX_INSERT_DEMO

Note – By default ROWID will be the primary key if we will not make any column to be primary key.

Check the Accessors, Create Method, Validation Method and Remove Method

6. Create a New View Object (VO)

Right click on InsertDemo > New > ADF Business Components > View Object

Name -- InsertVO

Package -- insert.oracle.apps.fnd.insertdemo.server

In Step2 in Entity Page select InsertEO and shuttle them to selected list

In Step3 in Attributes Window select columns Column1, Column2 and shuttle them to selected list

In Java page deselect Generate Java file for View Object Class: InsertVOImpl and Select Generate Java File for View Row Class: InsertVORowImpl

7. Add Your View Object to Root UI Application Module

Right click on InsertAM > Edit InsertAM > Data Model >

Select InsertVO in Available View Objects list and shuttle to Data Model list

8. Create a New Page

Right click on InsertDemo > New > Web Tier > OA Components > Page

Name -- InsertPG

Package -- insert.oracle.apps.fnd.insertdemo.webui

9. Select the InsertPG and go to the strcuture pane where a default region has been created

10. Select region1 and set the following properties:

ID -- PageLayoutRN

Region Style -- PageLayout

AM Definition -- insert.oracle.apps.fnd.insertdemo.server.InsertAM

Window Title -- Date Entry Page Window

Title -- Data Entry Page

Auto Footer -- True

11. Right click PageLayoutRN > New > Region

ID -- MainRN

Region Style -- defaultSingleColumn

12. Create Text Input Items

Right click on MainRN > New > Item

Set following properties for New Item

ID -- COLUMN1

Item Style -- messageTextInput

Maximum Length -- 100

Length -- 20

Prompt -- Column1

View Instance -- InsertVO1

View Attribute -- Column1

Again Right click on MainRN > New > Item

Set following properties for New Item 

ID -- COLUMN2

Item Style -- messageTextInput

Maximum Length -- 100

Length -- 20

Prompt -- Column2

View Instance -- InsertVO1

View Attribute – Column2

13. Add Apply and Cancel Buttons

Right click on PageLayoutRN > New > Region

         ID -- PageButtons

         Region Style -- pageButtonBar

Right click on PageButtons > New > Item

ID -- Cancel

Item Style -- submitButton

Attribute Set -- /oracle/apps/fnd/attributesets/Buttons/Cancel

Disable Server Side Validation -- True

Prompt -- Cancel

Warm About Changes -- False

Additional Text – Select to cancel this transaction.

Right click on PageButtons > New > Item

ID -- Apply

Item Style -- submitButton

Attribute Set -- /oracle/apps/fnd/attributesets/Buttons/Apply

Prompt -- Apply

Additional Text – Select to save this transaction.

14. Implement Row Initialization (Create a View Object Row)

Add createRecord method to your InsertAMImpl class

import oracle.jbo.Row;
import oracle.apps.fnd.framework.OAViewObject;
...
  
  public void createrecord()
  {
    OAViewObject vo=(OAViewObject)getinsert_VO1();
    if(!vo.isPreparedForExecution())
    {
      vo.executeQuery();
    }
    for (int i = 0; i < 10; i++)
    {
    
    OARow r;
    r=(OARow)vo.createRow();
    if(i!=0)
    {
      vo.next();
    }
    vo.insertRow(r);
    
    r.setNewRowState(Row.STATUS_INITIALIZED);
    }
    

15. Create Controller for Page  

PageLayoutRN > Set New Controller >

Package Name: insert.oracle.apps.fnd.insertdemo.webui

Class Name: InsertCO

16. Add Create Page Initialization to your Controller

Add following code to your processRequest()

import oracle.apps.fnd.framework.OAApplicationModule;
...
   public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
    
    if(!pageContext.isFormSubmission())
    {
      OAApplicationModule OAM=(OAApplicationModule)pageContext.getRootApplicationModule();
   OAM.invokeMethod("createrecord");
    }
  }

17. Add below method in InsertAMImpl Class to handle Apply Button action

import oracle.jbo.Transaction;
...
   public void apply()
  {
    getOADBTransaction().commit();
  }

18. Add below Logic in InsertCO to handle Apply Button

Add following code to your processFormRequest()

import oracle.jbo.domain.Number; 
//import oracle.apps.fnd.common.MessageToken;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAViewObject;
//import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
...

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    
    OAApplicationModule OAM=(OAApplicationModule)pageContext.getRootApplicationModule();
    
    if(pageContext.getParameter("apply")!=null)
    {
    OAM.invokeMethod("apply");
    throw new OAException("data enterd sucsessfully",OAException.CONFIRMATION);
       
        
      
    }

(1)

end

OAF Directory structure

Basically:

VO & AM are located in server

EO are located in schema/server

PG, RN & CO are located in webui

myprojects contains the files you create (.xml, .java)

myclasses contains the files build / compiled (.xml, .class)

You will find some very detailed information in the OAF Developer Guide, in the section "OA Framework File Standards (Naming, Package Structure and Standard Content)"

To Check Custom Messages in Oracle apps

SELECT * FROM  FND_NEW_MESSAGES where MESSAGE_NAME='XXXXX';

Implement PopList in OA Framework

Implement PopList in OA Framework

1. Create a New Workspace and Project

File> New > General> Workspace Configured for Oracle Applications

File Name -- PopListDemo

Project Name – PopListDemo

Default Package -- pop.oracle.apps.PO.lovdependentPRJ.webui

2. Create a New Application Module (AM)

Right Click on PopListDemo > New > ADF Business Components > Application Module

Name -- poplistdemoAM

Package -- pop.oracle.apps.PO.lovdependentPRJ.server

3. Create a New View Object (VO)

Right click on PopListDemo > New > ADF Business Components > View Object

Name -- poplistdemoVO

Package --  pop.oracle.apps.PO.lovdependentPRJ.server

Note - The VO is not based on any EO so click next and go to the query section and paste the query

select e.empno,e.ename from emp1 e,dept1 d where sal between '2000' and '5000' and  e.deptno=d.deptno and d.deptno='20';

4. Create a New Page

Right click on PopListDemo > New > Web Tier > OA Components > Page

Name -- poplistdemoPG

Package -- pop.oracle.apps.PO.lovdependentPRJ.webui

5. Select the poplistdemoPG and go to the strcuture pane where a default region has been created

6. Select region1 and set the following properties:

ID -- PageLayoutRN

AM Definition -- pop.oracle.apps.PO.lovdependentPRJ.server.poplistdemoAM

Window Title -- PopList Demo Window

Title – PopList Demo Header

7. Right click PageLayoutRN and  click new Region

ID -- MainRN

Region Style – messageComponentLayout

8. Verify Your VO attribute name

Select poplistdemoVO right click > Edit poplistdemoVO > Attributes >

Verify the Name it should be “poplist_items” if it is not then edit it and enter name “poplist_items”

9. Create the first Item (Empty Field)

MainRN > New > messageChoice

Set following properties for new item

ID – MyPopList

Required -- Yes

Picklist View Definition -- pop.oracle.apps.PO.lovdependentPRJ.server.poplistdemoVO

Picklist Display Attribute – poplist_items (Name of Attribute in  Your VO)

Picklist Value Attribute -- poplist_items

CSS Class – OraFieldText

Prompt – My PopList

(1)

(2)

End

Implement External LOV in OA Framework

Implement External LOV in OA Framework

1. Create a New Workspace and Project

Right click Workspaces and click create new OAworkspace and name it as PRajkumarLovDemo. Automatically a new OA Project is also created. Name the project as LovDemo and package as lov.oracle.apps.PO.lovdemo.webui

2. Create a New Application Module (AM)

Right Click on LovDemo > New > ADF Business Components > Application Module

Name -- LovAM

Package -- lov.oracle.apps.PO.lovdemo.server

3. Create a New View Object (VO)

Right click on LovDemo > New > ADF Business Components > View Object

Name -- LovVO

Package -- lov.oracle.apps.PO.lovdemo.server

Note - The VO is not based on any EO so click next and go to the query section and paste the query

select empno,ename

from apps.emp1

where hiredate between '17-DEC-80' and '02-APR-81'

4. Add View Object to Root UI Application Module

5. Create a New Page

Right click on LovDemo > New > Web Tier > OA Components > Page

Name -- LovPG

Package -- lov.oracle.apps.PO.lovdemo.webui

6. Select the LovPG and go to the strcuture pane where a default region has been created

7. Select region1 and set the following properties:  

ID -- PageLayoutRN

AM Definition -- lov.oracle.apps.PO.lovdemo.server.LovAM

Window Title -- List of values Demo Window

Title – List of values Demo

8. Right click PageLayoutRN and  click new Region

ID -- MainRN

Region Style – messageComponentLayout

Note - Style is given as messageComponentlayout because we are going to create only message components that is messageLovInput item in that region

9. Create a New Region

Right click on LovDemo > New > Web Tier > OA Components > Region

Name -- EmployeeLovRN

Package -- lov.oracle.apps.PO.lovdemo.webui

Region Style -- listOfValues

Scope -- Public

Note - The property Scope is the key property which makes the LOV region public and makes it usable in multiple pages

10. Select EmployeeLovRN. Right click on EmployeeLovRN in Structure pane and click table using wizard. In the wizard choose the prajkumar.oracle.apps.fnd.lovdemo.server.LovAM and select the LovVO1. Click Next

Region Id -- LovRN

Region style -- table

11. Shuttle the two attributes to the right side.That is EmployeeNumber and FullName

12. Click next, check the mapping and then finish

13. Select the field FullName and set the following properties:

Search Allowed -- True

Selective Search Criteria – True

Note - The first property lets users search on these values in the LOV, and the second property ensures that the users specify search criteria for at least one of these values to avoid a blind query

Attaching External LOV to Page:

14. Click on LovPG and right click the MainRN and click new messageLovInput

External Lov -- /prajkumar/oracle/apps/fnd/lovdemo/webui/EmployeeLovRN

Lov Region Item -- FullName

Return Item -- item1

Criteria Item -- item1

Prompt -- Employee Name

15. Congratulation you have successfully finished. Run Your page and Test Your Work 

end

Create OAF Search Page

Create OAF Search Page

1. Create a New Workspace and Project

Right click Workspaces and click create new OAworkspace and name it as  Search. Automatically a new OA Project is also created. Name the project as SearchDemo and package as cust.oracle.apps.fnd.searchdemo

2. Create a New Application Module (AM)

Right Click on SearchDemo > New > ADF Business Components > Application Module

Name -- SearchAM

Package -- cust.oracle.apps.fnd.searchdemo.server

3. Enable Passivation for the Root UI Application Module (AM)

Right Click on SearchAM > Edit SearchAM > Custom Properties >

Name – RETENTION_LEVEL

Value – MANAGE_STATE

Click add > Apply > OK

4. Create Test Table and insert data some data in it (For Testing Purpose)

CREATE TABLE xx_search_demo
(    -- --------------------
     -- Data Columns
     -- --------------------
     column1                  VARCHAR2(100),
     column2                  VARCHAR2(100),
     -- --------------------
     -- Who Columns
     -- --------------------
     last_update_date    DATE         NOT NULL,
     last_updated_by     NUMBER   NOT NULL,
     creation_date          DATE         NOT NULL,
     created_by               NUMBER   NOT NULL,
     last_update_login    NUMBER
);

INSERT INTO xx_search_demo VALUES (‘val1’, ’val2’, SYSDATE, 0, SYSDATE, 0, 0);
INSERT INTO xx_search_demo VALUES (‘val1’, ’val2’, SYSDATE, 0, SYSDATE, 0, 0);
INSERT INTO xx_search_demo VALUES (‘val3’, ’val4’, SYSDATE, 0, SYSDATE, 0, 0);
INSERT INTO xx_search_demo VALUES (‘val5’, ’val6’, SYSDATE, 0, SYSDATE, 0, 0); 

Now we have 4 records in our custom table

5. Create a New Entity Object (EO)

Right click on SearchDemo > New > ADF Business Components > Entity Object

Name – SearchEO

Package -- cust.oracle.apps.fnd.searchdemo.schema.server

Database Objects -- XX_SEARCH_DEMO

Note – By default ROWID will be the primary key if we will not make any column to be primary key Check the Accessors, Create Method, Validation Method and Remove Method

6. Create a New View Object (VO)

Right click on SearchDemo > New > ADF Business Components > View Object

Name -- SearchVO

Package -- cust.oracle.apps.fnd.searchdemo.server

In Step2 in Entity Page select SearchEO and shuttle them to selected list

In Step3 in Attributes Window select columns Column1, Column2 and shuttle them to selected list

In Java page Select Generate Java file for View Object Class: SearchVOImpl and Generate Java File for View Row Class: SearchVORowImpl

7. Add Your View Object to Root UI Application Module

Select Right click on SearchAM > Edit SearchAM > Data Model >

Select SearchVO and shuttle to Data Model list

8. Create a New Page

Right click on SearchDemo > New > Web Tier > OA Components > Page

Name -- SearchPG

Package -- cust.oracle.apps.fnd.searchdemo.webui

9. Select the SearchPG and go to the strcuture pane where a default region has been created

10. Select region1 and set the following properties:

ID -- PageLayoutRN

Region Style -- PageLayout

AM Definition -- cust.oracle.apps.fnd.searchdemo.server.SearchAM

Window Title -- Search Page Window

Title -- Search Page

Auto Footer -- True

11. Add a Query Bean to Your Page

Right click on PageLayoutRN > New > Region

Select new region region1 and set following properties

ID – QueryRN

Region Style – query

Construction Mode – resultBasedSearch

Include Simple Panel – True

Include Views Panel – True

Include Advanced Panel – True

12. Add a Result Data Table to your QueryRN

Select QueryRN right click > New > Region using Wizard

In BC4J Objects page, Select your SearchAM and then select your SearchVO1

Note – DO NOT select Use this as Application Module Definition for this region checkbox

In Region Properties page, set Region ID value to ResultsTable and Region Style to table

In view Attributes page, select attributes from Available View Attributes list and shuttle them to

Selected View Atributes list:

Column1

Column2

In Region Items Page, you can set ID, Style and Attributes Set. Currently we are going to set only Style as messageStyledText

13. Set and verify Your Results Table Region Properties

ID – ResultsTable

Region Style – table

AM – Please Donot put any AM

Rendered – True

Records Displayed – 10

Width – 100%

User Personalization – True

14. Set or Verify Column1 Item Properties

Search Allowed -- True

Sort Allowed – ascending

Initial Sort Seqence – first

Selective Search Criteria – True

User Personalization – True

15. Set or Verify Column2 Item Properties

Search Allowed – True

Sort Allowed -- ascending

Selective Search Criteria – True

User Personalization – True

16. We successfully finished Search page. Run Your SearchPG page and Test Your Work

Enter value one of them and Check

Query's to check Order Management

-- Query to check all orders in interface with details

select h.attribute1, h.attribute10,orig_sys_document_ref,h.customer_number,h.price_list_id,r.customer_name,h.order_type,h.creation_date,h.attribute6,error_flag,booked_flag,h.request_date,h.attribute2,h.ship_to_site_int,h.payment_term,h.payment_term_id
from oe_headers_iface_all h,ra_customers r
where h.customer_number= r.customer_number
--and error_flag is null
---and error_flag ='Y'
--and h.order_type like '%PRO%'
--and h.customer_po_number in ('365')
and h.customer_number  in()
--and trunc(h.creation_date)= '10-DEC-2012'
order by customer_number,creation_date desc;
*********************************************************************************

---Update the ship_to in interface using PO#

Headers::

update oe_headers_iface_all
set ship_to_site_int='xxxx'
where orig_sys_document_ref ='xxxx;

Lines::

update oe_lines_iface_all
set global_attribute2='5465A'
where orig_sys_document_ref ='xxxxx';

*********************************************************************************
---Query to Book the Orders using Script::


declare
cursor stylprc is
select product_attr_val_disp, product_attr_value, operand from qp_list_lines_v where list_header_id=XXXX and
product_attr_value in (select distinct ordered_item_id from oe_order_lines_all
where header_id in ('XXXXX') and unit_selling_price is null);

begin
for sp in stylprc loop
begin
update oe_order_lines_all set unit_selling_price=sp.operand, unit_list_price=sp.operand where header_id in ('XXXXX')
and ordered_item_id=sp.product_attr_value and unit_selling_price is null;
end;
end loop;
end;
*********************************************************************************


-- Query to get ship to details for an order.

SELECT h.orig_sys_document_ref,
h.customer_number,
l.inventory_item_id,
l.inventory_item,
h.SHIP_TO_ADDRESS1,
h.SHIP_TO_ADDRESS2,
h.SHIP_TO_ADDRESS3,
h.SHIP_TO_ADDRESS4
FROM oe_headers_iface_all h,oe_lines_iface_all l
where h.orig_sys_document_ref =l.orig_sys_document_ref
and h.orig_sys_document_ref = 'xxxx';

*********************************************************************************

---Query to get duplicate records for a customer in interface

SELECT distinct ''''||cust_po_number||''''||',',customer_name
  FROM oe_order_headers_all l,ra_customers r
WHERE l.sold_to_org_id =r.customer_id
and cust_po_number IN (SELECT orig_sys_document_ref
                                   FROM oe_headers_iface_all                              
                                   WHERE customer_number = xxxx) ;