Example on DBMS_XMLGEN in oracle apps using plsql procedure

First create a table and insert values into that table and press on Commit.

Step 1. Create a table
CREATE TABLE demo_products
(  product_code   NUMBER,
   product_name   VARCHAR2 (100));

Step 2. Insert Values

INSERT INTO demo_products
     VALUES (100, ‘TEST DATA’);

Step 3.Commit



Create a PL/SQL Package

-- Package Specification

Step 1. Create a Package Spec & Body with a single Procedure


CREATE OR REPLACE PACKAGE APPS.XX_REPORT_PKG

AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER);
END XX_REPORT_PKG;



-- Package Body:

CREATE OR REPLACE PACKAGE BODY APPS.XX_REPORT_PKG
AS
   PROCEDURE REPORT (errbuf            OUT VARCHAR2,
                     retcode           OUT VARCHAR2,
                     p_product_id   IN     NUMBER)
   IS
      l_qryCtx      DBMS_XMLGEN.ctxHandle;
      l_query       VARCHAR2 (32000);
      l_length      NUMBER (10);
      l_xmlstr      VARCHAR2 (32000);
      l_offset      NUMBER (10) := 32000;
      l_retrieved   NUMBER (10) := 0;
      l_result      CLOB;
      l_no_rows     NUMBER;
   BEGIN
      l_query := ‘SELECT product_code, product_name
         FROM demo_products
       WHERE product_code = ‘ || p_product_id;
      l_qryCtx := DBMS_XMLGEN.newContext (l_query);

      — set rowset tag to PRODUCTS and row tag to PRO_DETAILS
      DBMS_XMLGEN.setRowSetTag (l_qryCtx, ‘PRODUCTS’);
      DBMS_XMLGEN.setRowTag (l_qryCtx, ‘PRO_DETAILS’);

      — now get the result
      l_result := DBMS_XMLGEN.getXML (l_qryCtx);
      l_no_rows := DBMS_XMLGEN.GETNUMROWSPROCESSED (l_qryCtx);
      FND_FILE.put_line (FND_FILE.LOG, ‘No of rows processed= ‘ || l_no_rows);

      l_length := NVL (DBMS_LOB.getlength (l_result), 0);
      FND_FILE.put_line (FND_FILE.LOG, ‘Length= ‘ || l_length);

      LOOP
         EXIT WHEN l_length = l_retrieved;

         IF (l_length – l_retrieved) < 32000
         THEN
            SELECT SUBSTR (l_result, l_retrieved + 1) INTO l_xmlstr FROM DUAL;

            l_retrieved := l_length;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         ELSE
            SELECT SUBSTR (l_result, l_retrieved + 1, l_offset)
              INTO l_xmlstr
              FROM DUAL;

            l_retrieved := l_retrieved + l_offset;
            fnd_file.put_line (fnd_file.output, l_xmlstr);
         END IF;
      END LOOP;

      DBMS_XMLGEN.closeContext (l_qryCtx);
   EXCEPTION
      WHEN OTHERS THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
         raise_application_error (-20001, ‘Error in procedure XX_REPORT_PKG.report’);
   END REPORT;
END XX_REPORT_PKG;

Step 2. Define Executable

Navigation: Application Developer > Concurrent > Executable
Provide Executable, Short Name, Application, Description & also
Execution Method: PL/SQL Stored Procedure
Execution File Name: XX_REPORT_PKG.REPORT

Step 3. Define Concurrent Program

Navigation: Application Developer > Concurrent > Program

Provide Program, Short name, Application, Description & also
– Executable Name as defined in the above step

– Output Format should be XML
– Define a Parameter p_product_id
– Associate Concurrent Program to the Request Group.

Step 4. Create Data Definition

Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
The code should be exactly same as concurrent program short name.

Step 5. Create Template. Register Template with the XML Publisher

Navigation: XML Publisher Administrator -> Templates -> Create Template

RTF can be created using the XML file from the Output of the concurrent Program.

Step 6. Run the Concurrent Program to see the output

Developing sample XML Publisher Report with Executable Method as ‘PL/SQL Stored Procedure‘

Payroll tables description

The tables that are date-tracked will have two columns:-
Effective_start_date
Effective_end_date
All the objects in Oracle HRMS or Payroll that end with _x have a where clause where sysdate between Effective_start_date AND Effective_end_date
Hence these are views that return records as of SYSDATE. The primary keys of the date tracked columns includes Effective_start_date & Effective_end_date.




PAY_ELEMENT_TYPES_F - Payroll Elements
Firstly, we create some elements, which get created in table PAY_ELEMENT_TYPES_F. The primary key is a combination of element_type_Id along with Date Track columns.
When will you join to pay_element_types_f ?
1. To display the name of Element in Reports
2. When payroll runs, the results are stored in PAY_RUN_RESULTS, which stores a reference to element_type_Id.




PAY_ELEMENT_LINKS_F - Payroll Element Links
To make payroll elements eligible to a group of people, you create Element Links.
See Elements Basics article that explains why Element Links are necessary.
The Primary key is ELEMENT_LINK_ID with date-track columns.
When will you commonly use element_link_Id ?
1. When querying on Element Entry[PAY_ELEMENT_ENTRIES_F], a join can be made using ELEMENT_LINK_ID
2. The reason Oracle uses ELEMENT_LINK_ID in Element Entry to work out Costing Segments based on Payroll Costing Hierarchy.


PER_ALL_PEOPLE_F - Employee record
It is well known that Employee records are stored in PER_ALL_PEOPLE_F. Its a date track table with primary key being person_Id. This table also has party_Id, because Oracle creates a party in TCA as soon as a record in per_all_people_f gets created.
Main usage of per_all_people_f:-
1. To get the name of the person
2. To get the date of birth or tax Id of the person
Note:- The application uses PER_PEOPLE_F, as that is a secured view layer on top of PER_ALL_PEOPLE_F



PER_ALL_ASSIGNMENTS_F - Assignment table:-
This is the most central table in Oracle Payroll. Payroll engine uses this table as the main driver.
Why so: Because Element Entries are stored against Assignment record.
This table is date-tracked, with primary key being assignment_Id
Usage of per_all_assignments_f?
1. Find position_Id, hence position, or grade, the organization for the persons assignment.
2. It has foreign key to person_id. Each person Id can have no more than one primary assignment at any given point  in time.
3. Pay run results and also the pay_assignment actions refers to this table.




PER_PERSON_TYPES - Person type
This is the master table for Person Types. Some examples of Person Types are Employees, Casuals, Applicants etc.
The primary key is person_type_id.
But please do not try joining this with person_type_id in per_all_people_f. 
Instead join that to per_person_type_usages_f
_x will give you person_type usage as of SYSDATE. 
For any other date, use the classic p_date between effective_start_date and effective_end_date. 




PAY_ELEMENT_ENTRIES_F & PAY_ELEMENT_ENTRY_VALUES_F - Tables effected when element entry is done
These two tables are inserted into when fresh Element Entries are created.
PAY_ELEMENT_ENTRIES_F
Each Element that gets attached to an Assignment will have an entry in PAY_ELEMENT_ENTRIES_F. 
For each assignment you will have one or more records in PAY_ELEMENT_ENTRIES_F table. 
It is logical that PAY_ELEMENT_ENTRIES_F has following columns
    Assignment_id
    Element_link_id
    ELEMENT_TYPE_ID
This table is date-tracked too. Please do not ask my where there was a need to store both ELEMENT_TYPE_ID and also ELEMENT_LINK_ID in this table.
Just storing the ELEMENT_LINK_ID could suffice. However, i guess Oracle did so for Performance reasons.




PAY_ELEMENT_ENTRY_VALUES_FThis table stores a reference to PAY_ELEMENT_ENTRIES_F. In plain English, this table captures the entry value for the elements. 
The Input Value is stored in SCREEN_ENTRY_VALUE. The name suggests that it stores the Formatted Screen value. However, I can assure you that SCREEN_ENTRY_VALUE stores the non formatted value. For example screen might showHH:MM as 03:30, but SCREEN_ENTRY_VALUE will have 3.5 
This table is date-tracked, and its primary key is INPUT_VALUE_ID.
Where can I commonly join INPUT_VALUE_ID to ?
To the payroll run results value table, i.e. PAY_RUN_RESULT_VALUES
You can also join to PAY_COSTS, if you wish to work out which input value contributed to a specific Payroll Costed Amount.






What is the difference between quick-pay and payroll run?
Quickpay is a functionality(available from assignment screen), by which you can run the payroll for single person assignment. It uses the same executable as that of actual Payroll run.





PAY_PAYROLL_ACTIONS - What is a payroll action?
Well, just about anything you make the Oracle Payroll engine do, it records an entry in PAY_PAYROLL_ACTIONS.

What are the possible actions?To name a few:-
     Costing 
     Quickpay
     Payroll Run
     Magnetic Transfer [synonym to EOY-End of Year run]
     Transfer to GL......etc
A column named ACTION_TYPE [validated by lookup type ACTION_TYPE] is used to store the type of action.

Why does PAY_PAYROLL_ACTIONS contain PAYROLL_ID, ELEMENT_SET_ID and ASSIGNMENT_SET_ID?
The Payroll process[conc short name PYUGEN] can be run for a specific Payroll i.e. for people enrolled to Monthly or Weekly payroll.
Hence Payroll_id is stored to capture parameter details.

A payroll action can also be restricted to a specific group of Elements, via ELEMENT_SET_ID.
A group of Elements are defined using a table PAY_ELEMENT_SETS, for example you wish to group all bonus related elements together.

Ditto with ASSIGNMENT_SET_ID, as that identifies a set of Assignment Records.
Assignment sets can be defined on the basis of criteria[HR_ASSIGNMENT_SET_CRITERIA] or assignments can be manually added to Assignment set[HR_ASSIGNMENT_SET_AMENDMENTS]

pay_payroll_actions does not store reference to the assignment record.


Which Assignment Records were included in Payroll Actions :- PAY_ASSIGNMENT_ACTIONS
pay_assignment_actions contains an entry for each Assignment_id that was eligible during Payroll Action.
For example if you run a quickpay, an entry for that specific assignment_id will be created in pay_assignment_actions. 
Obviously this table has a column named assignment_id.
You can drill down from Assignment Action screen to view Payroll Run Results and Payroll Balances, for the specific assignment.
Hence both PAY_RUN_BALANCES and PAY_RUN_RESULTS reference ASSIGNMENT_ACTION_ID.
NOTE: ASSIGNMENT_ACTION_ID is the primary key of PAY_ASSIGNMENT_ACTIONS.
Also note that entries in this table are created by Concurrent Processes, hence this table is never updated by end user from screens. Hence there is no date-tracking on this table.


What is payroll run results?
As you would know, when payroll process runs, it reads the element entries for the assignment in pay_assignment_actions. For those element entries, payroll engine either uses the entry value in "pay value" or it kicks off a fast formula if the element has a ff attached to that. The end result is that each eligible element gets a result. These results are stored in pay run result tables.


What are the payroll run results tables?
Pay_run_results
Pay_run_result_values
Obviously the values are stored per element in Pay_run_results.
The input value used/derived by payroll engine is stored in Pay_run_result_values/

How to create a custom profile with list of values

How to create a custom profile with list of values

1. Define Custom Lookup with LOV

Application Developer Responsibility

                            |

                            |

                     Lookups

                           |

                           |

Create Custom Lookup <Name>

2. Define Custom Profile

Application Developer Responsibility

                            |

                            |

                     Profiles

                           |

                           |

In the Sql Validation enter the following text

SQL = "SELECT MEANING, LOOKUP_CODE

            INTO :VISIBLE_OPTION_VALUE, :PROFILE_OPTION_VALUE

            FROM FND_LOOKUPS

WHERE LOOKUP_TYPE='<LOOKUP_NAME>'

ORDER BY LOOKUP_CODE

COLUMN ="MEANING(*)"

HEADING ="\"<FREE TEXT>\"(*)"

After creating the Custom profiles, you have to go to System administrator and can assign the profiles based on the Site level, Responsibility Level, user level etc. and give profile values based on the requirement.

PO approved supplier list details query in oracle apps

PO approved supplier list details query in oracle apps

SELECT   msib.inventory_item_id,
         msib.segment1 "ITEM_NUMBER",
         msib.description "ITEM_DESCRIPTION",
         msib.primary_uom_code "PRIMARY_UNIT_OF_MEASURE",
         DECODE (MSIB.enabled_flag, 'Y', 'Active', 'Inactive') "ITEM_STATUS",
         msib.CREATION_DATE "ITEM_CREATION_DATE",
         pv.segment1 "VENDOR_NUMBER",
         pv.vendor_name "VENDOR_NAME",
         pvsa.vendor_site_code "VENDOR_SITE_CODE",
         pvsa.INACTIVE_DATE "VENDOR_SITE_INACTIVE_DATE",
         asl.PRIMARY_VENDOR_ITEM,
         asl.DISABLE_FLAG,
         pas.status "SOURCE_ASL_STATUS",
         ood.organization_code "ORGANIZATION CODE",
         asl.CREATION_DATE,
         asl.LAST_UPDATE_DATE,
         asl.ATTRIBUTE7 "VENDOR_PRICE",
         asl.ATTRIBUTE6 "PREFERENCE"
  FROM   APPS.po_approved_supplier_list asl,
         APPS.po_vendors pv,
         APPS.po_vendor_sites_all pvsa,
         APPS.org_organization_definitions ood,
         APPS.mtl_system_items_b msib,
         APPS.po_asl_attributes paa,
         APPS.po_asl_statuses pas
 WHERE       pv.vendor_id = asl.vendor_id
         AND pvsa.vendor_site_id = asl.vendor_site_id
         AND ood.organization_id = asl.using_organization_id
         AND ood.operating_unit = pvsa.org_id
         AND asl.item_id = msib.inventory_item_id
         AND asl.using_organization_id = msib.organization_id
         AND ood.organization_id = msib.organization_id
         AND asl.asl_id = paa.asl_id
         AND asl.using_organization_id = paa.using_organization_id
         AND msib.organization_id = paa.using_organization_id
         AND ood.organization_id = paa.using_organization_id
         AND asl.asl_status_id = pas.status_id

         AND msib.segment1 = :ITEM_NUMBER

How to personalize the lov field in the form

Business Requirement:-

Customer name field is getting duplicated, 1 record with status as 'Active' and another record with status as 'InActive'. Now the requirement is to get the 'Active' records for the Customer Name field.

To Restrict Inactive Customers in GatePass Form through Personalization:-

Navigation:-

Order Management Super User GatePass Form

Query:

SELECT party_name CUSTOMER_NAME, account_number CUSTOMER_NUMBER, cust_account_id CUSTOMER_ID

FROM hz_parties hp, hz_cust_accounts hca

WHERE hp.party_id = hca.party_id AND party_type = 'ORGANIZATION'

and hca.status = 'A'

ORDER BY party_name

Added one extra condition status = ‘A’

Now the Active Customer name is displaying. Thanks for seeing my post. Share your comment.

How to prevent duplicate form submissions in OAF page submissions

How to prevent duplicate form submissions in OAF page submissions

In your processRequest  of the controller add the below code, once you submit the page, the page will get a hourclass that will avoid the user to click second time,

OAWebBean bodyBean = pageContext.getRootWebBean();
if (bodyBean instanceof OABodyBean)
{
((OABodyBean)bodyBean).setBlockOnEverySubmit(true);
}

Introduction to Oracle Application Framework (OAF)

Introduction to Oracle Application Framework (OAF)

OAF

Stands for

Oracle Application Framework

OAF is a framework developed by Oracle Corporation for application development within E-Business Suite.

With the Release of R12 and fusion, Oracle is moving from Forms based interface to Web based User Interface.

OAF is used for building web based front end pages and J2EE type of applications within Oracle EBS ERP Platform.

Tool Used to Develop web pages.

Oracle uses Jdeveloper as the tool to develop the web pages.

What is Jdeveloper

JDeveloper is a freeware IDE supplied by Oracle Corporation. It offers features for development in Java, XML, SQL and PL/SQL, HTML, JavaScript, BPEL and PHP. JDeveloper covers the full development lifecycle from design through coding, debugging, optimization and profiling to deploying

Pre-Requisites for Learning OAF

1.Knowledge of Oracle Apps 11i or higher

2.Basic Knowledge of Java or Basic knowledge of OOPS

3.Understanding of MVC Architecture

OAF follows MVC Architecture which is built using J2EE( Java Platform Enterprise Edition)

Advantages of OAF

1.Improved End user Productivity

2.Browser Look and Feel

3.SQL & Web Services

4.OAF pages are light weight components , so it loads fast when compared to forms

5.OAF pages has only Regions and Items so that we can develop the web pages faster when compared to forms.

OAF Pages are web pages, when we open any OAF Page, it is having URL at the top, copy the URL and send it to another person, so that he can also open the page without knowing from which responsibility he has opened and what is the navigation for that page

Disadvantages of OAF:- (Limitations)

1.Drag and Drop of Items Feature is not available

2.Can be used only in Oracle Apps

3.Cannot view the Pages while designing time.

4.No out-of-box integration with webservices, for example you cannot simply drag the webservice control on the page.

Differences between Forms and OAF

Forms	OAF
1. With Forms, we can create Custom Forms and Modify seeded forms using Forms Builder	1. Jdeveloper is the development tool for creating custom web pages and modify seeded web pages
2. We use SQL and PL/SQL in Oracle Forms	2. Here we use SQL statements in VO & PL/SQL Statements in Java Code
3. Template.fmb is the starting point for any custom forms. Template.fmb contains reference to libraries, properties settings and menus that will help to make custom forms look like Oracle provided forms	3. Through Jdeveloper, we create and modify the seeded objects which gives custom web pages look like as Oracle provided web pages.
4. In Forms, we are having canvases, Fields, Buttons	4. In OAF, we use the concept of Region, webpage requires a root region. Fields, Buttons, Images etc appears in the region.

How to find correct version of Jdeveloper

Go to Application Login Page

Click on “About this Page”

If you did not see the “About this Page”, we need to set the Profile Option

FND:Diagnostics to “YES”, then we will be able to see the “About this Page” link.

Go to Technology Components

There we will see the OAFramework Version

Download the Jdeveloper Patch from Oracle support and Extract into local system. After Downloading the Patch, Get the dbc file from the server using winscp and place the .dbc file in the path.

 /Jdevhome/jdev/dbc_files/secure/……

How to set Environmental Variable:-

Go to My Computer System Properites. Click on Environmental Variables.

OAF vs ADF

ADF

Stands for

Application Development Framework

Differences in Using MVC Architecture

For OAF

Modelà All BC4J Components like AM,EO,VO,VL etc

ViewàUIX

Controllerà Controller Classes

For ADF

ModelàSame as OAF

ViewàJSP,JSX with an extension of .jspx

ControlleràBacking Bean Classes

Why ADF uses JSP, when UIX is there.

UIX means User Interface XML, it is having some limitations and it is having no integration capability with other technologies to deliver most complex and extensive User Interfaces. So, this is the reason why ADF uses JSP.

MVC Architecture

M --------------------------à Model

V----------------------------à View

C----------------------------à Controller

OAF uses this features for Developing OAF Pages

Model ---------à It is the place where the applications implements its Business Logic.

                              It is responsible for maintaining the data.

                              It responds to the request from the View and it also responds to the request            from the controller also to update itself.

Model Contains three basic components

1.Application Module :- Container for BC4J Components

2.Entity Object:- Encapsulates Business rules

3.View Object:- Present data to the Framework Page.

●

In addition there are three other components.

1. Callable Statements 2. Prepare Statement 3. OADB Transaction.

Callable Statement:-

Callable statement is used to call the PL/SQL statements.

Prepare Statement:-

Prepare statements are used if we are using select statements.

OADB Transaction:-

OADB Transactions will take care of the Oracle Apps related Database Transactions.

Entity Object:-

•Entity objects can be created on database objects like Table, View etc

•Each Entity object instance represent a single row and contains attributes representing database columns.

•Entity Objects are mainly used for Inserting, Updating and deleting records from database.

•OA Framework supports both PL/Sql Based and Java Based Entity Objects

        PL/Sql Entity Objects:- If we want to perform any DML operations on the standard tables , then we go for Pl/Sql Entity Objects

        Java Based Entity Objects:- If we want to perform any DML Operations on the custom tables, then we go for Java Based Entity Objects.

•OA Framework will provide you a java class with setter and getter methods.

•EO should always have the WHO Columns

1.CreationDate

2.CreatedBy

3.LastUpdateDate

4.LastUpdatedBy

5.LastUpdateLogin

After creating EO, we should always attach the EO to VO

Entity Association

It Defines the relationship between entity objects.

It consists of a Master and Detail entity.

There are two types of Entity Associations:

1.Reference Association

2.Composition Association

Reference Association:- Used as a weak association between entities such as foreign keys for lookups.

Ex- Purchase Order ----- Supplier Association

Composition Association:- Used as a strong association between entities.

Ex:- Purchase Order Header ---- Purchase Order Line

Behaviour of Composition Association:-

1.When Child is locked, parent is locked first automatically

2.Validation order is child first, parent second

3.Insert/Update order is parent first, child second

4.Deletion order is child first, parent second

View Object

View Object represents a query result and to display them in OAF Page.

View Object can also be created from SQL statements.

There are 2 types of VO’s

1.EO Based VO’s

2.Simple VO’s

●

After creating the View Object, attach the view object to Application Module.

View Link:-

It is the link between the View Objects.

Application Module:-

Application Module is the interface between the Client transactions and Database transactions.

Types of AM:-

1.Root AM

      If the AM is attached to the main region, it is called as Root AM

2.Nested AM

      If the AM is attached to the child region, it is called as Nested AM

All pages are attached to the AM

View

•View is the actual output of the OAF Page.

•View is implemented using UIX technology, after running the page in Jdeveloper, then automatically UIX will generate web.xml file.

•The generated web.xml file  will be converted into HTML format of OAF Page

•Whatever result, we want to see in our page we can set here.

•Oracle Provided a lot of properties for everything that we want to modify the visual representation of data.

Controller

•Controller will take care of the web browser activities.

•Controller responds to user actions and directs the application flow.

•Most of the codes will write in controller.

•The most common locations of our code in controller will be:

 à Process Request

 à Process Form Request

Process Request:-

we write code here for page initiation such as HTTP Get actions.

Process Form Request:-

We write code here HTTP Post actions such as when Button Pressed.

Process Form Data:-

We will not write  any code in this.

Parameters for Passing into Controller:-

There are 2  common parameters which are used for passing into controller. They are

1.OAPageContext

2.OAWebBean

OAPageContext:-

Using this page Context we can set and get the item values.

OAWebBean:-

Using OAWebBean, we can get the position of the field in the page.

Naming Conventions in OAF

Naming Standards for OAF Page:-

The page name should represents the object it presents.

Page Name ends with PG:-<objectname>PG

Ex:-HelloworldPG

      EmployeePG

      SupplierPG

Naming Standards for a Region:-

The region name should represent the object it presents.

Region Name ends with RN:- <objectname>RN

Ex:- MainRN

       PageLayoutRN

       PoHeaderRN

Naming Standards for EntityObject:-

Entity Object name ends with EO:- <objectname>EO

Ex:- EmployeeEO

        SupplierEO

        PurchaseOrderHeaderEO

Naming Standards for EntityAssociation Object:-

Association Object should convey the relationship between the entities.

Entity association name ends with AO:- <objectname>To<objectname>AO

Ex:- PoHeaderToLinesAO

        SupplierToSitesAO

       DepartmentToEmployeeAO

Naming Standards for a View Object

The view object name should convey the nature of the query

View Object name ends with VO:- <Descriptivename>VO

Ex:- EmployeeVO

       SupplierVO

       PurchaseOrderVO

Naming Standards for a View Link

The view link name should convey the relationship between the master and detail Vos

View Link ends with VL :- <Master>To<Detail>VL

Ex:- EmployeesToDepartmentsVL

        PoHeadersToLinesVL

Naming Standards for Application Module:-

The AM name should convey the purpose the user interface module it services

Application Module name ends with AM:- <ModuleName>AM

Ex:- HelloworldAM

       EmployeesAM

       SupplierAM

Naming Standards for Controller Object:-

Controller Object ends with CO:- <objectname>CO

Ex:- SearchCO

      EmployeeCO

      SupplierCO

OAF Page Contains

1.Regions

2.Items

There are 32 different types of Region Styles and 32 different types of Items available for designing of a OAF Page (As per version 12.1.1).

Page Contains the Region and the Region contains the Items. Without Region, we cannot attach the Items to the page.

Region:-

A Region is a container that holds the items.

Different Types of Regions.

1.pageLayout Region

This is the top level region of every OAF page. It will create automatically when we create the page.

2. Header Region:-

By using the Header Region, the items in that region will have only 1 column. All the items will be in Line Order

Item1

Item2

Item3

Item4

3. Message Component Layout

Here in this region, we are having rows and columns properties. We can define how many columns and rows we want.

Suppose there are 4 items in the region, we can define them in 2 columns as

Item1                                  Item3

Item2                                  Item4

Suppose we want all the items in 1 row as below

Item1                Item2                    Item3                     Item4

4. Default Single Column

5. Default Double Column

6.rowLayout Region

7.PageButton Bar Region

8. Query Region:- Which is useful while creating the search page

9. Table Region:- which is useful for displaying the data in the table region

10.Stack Layout Region:- which is useful for displaying the data of flexfield

11.Train Region:- Which is useful in multi transaction pages.

12. Advanced Search       

13. Advanced table 

14.Bulleted list

15.Cell Format

16. Content Container

17. content Footer 

18. Default stack

19. Default Form Stack

20. Flexible layout

21. Flow Layout   

22. gantt     

23. Graph table

24. Hide show

25. Hide Show Header

26. Labeled Field Layout 27. navigation Bar        28. shuttle

29. Switcher 30. Tree      31. subTab Layout.       32.hGrid

Items in OAF

1.Message Styled Text

 Whenever the user wants to display only the text and the user should not be able to edit the text then we use the message styled text

2. Message Radio Group:

This will create Radio Buttons in your page

3. Message check Box:

This will create check boxes in your page

4. Message Text Input:

By using this we will enter the data in the text box.

5.Message Lov Input:-

Whenever the user wants to select the value, he can select from the list the values.

6. Message Choice:-

When you want to display a list of values in drop down fashion, then we use message choice.

7. Message Download:

When the user needs to download a page we use this message download

8. Message File upload:

User will browse the file from local machine and upload a file.

9. Button:

It is a general button and it is having action associated with it. You have set the property for the button.

 Action Type à Fire action

                       à Partial Fire Action

                       à None

10. Submit Button:-

By using this we will submit data to the OAF page

11. Link:-

By using this we will Create links

12. Form Value:- This is used to send the value to the OAF page but without being displayed in the OAF page

13. spacer: This is used to add space between the two items placed in the page

14. separator:- This creates horizontal line in the oaf page

15. Flex:- This is a flex field. There are 2 types of flex fields. KFF and DFF

16. urlInclude:- This help to include the HTML Content

17. tip:- Helps to give hint to the user.

Where does the EO, VO, AM, Page, CO resides ?

EO Resides in

xxx.Oracle.apps.fnd.objectname.schema.server

VO Resides in

xxx.oracle.apps.fnd.objectname.server

AM Resides in

xxx.oracle.apps.fnd.objectname.webui

Page Resides in

xxx.oracle.apps.fnd.objectname.webui

CO Resides in

xxx.oracle.apps.fnd.objectname.webui

Exceptions in OAF

There are 4 Types of Exceptions in OAF

While writing exceptions, we need to add the class

import oracle.apps.fnd.framework.OAException;

We write the exceptions in Process Form Request of Controller.

1.INFORMATION

2.CONFIRMATION

3.ERROR

4.WARNING

 throw new OAException(message, OAException.INFORMATION);

Simple Page Creation steps:-

Welcome to Dhanush Page Creation Steps.

1.First we have to connect to our oracle database and then we have to start

2.Start creating a workspace. Right click on the Application and create new OA workspace.

Give the name for the workspace as Helloworld.jws

3. Click OK and then give project name, directory name and default package and click on Next

4. Connect to the database at runtime and browse the dbc file and give the oracle applications login credentials.

5. Give the application short name and Responsibility Key

6. Click on Next and Finish

7. Create the Application Module and the package should end with webui and click on Finish

8. Create the page and give the name of the page and package should end with webui

9. It will create the .xml file in the webui

10. Select the xml file and go to the region and name it as PageLayout Region and give the window title and Page title and attach the AM Definition in the page.

11. Create another region and name it as main region and create the text fields in the main region. After add another region under it and add a button item and write the code for the Button Item in the controller by creating the new controller for the xml file.

Code Written for GO Button in Controller Process Form Request Method

And

 Validating Text Input to not allowing the special Characters

 if (pageContext.getParameter("Go") != null)

                 {

                        String userContent= pageContext.getParameter("Hello");

                         Pattern p=Pattern.compile("[^a-zA-Z0-9\\s]");

                         Matcher m=p.matcher(userContent);

                         if(m.find())

                        {

                        throw new OAException("Special Characters are not allowed",OAException.ERROR);

                        }

                        else

                        {

                          String message = "Welcome to Dhanush, " + userContent + "!";

                          throw new OAException(message, OAException.INFORMATION);

                        }                                             

                 }

Creating of Clear Button In ButtonLayout Region

1.       After the creation of GO Button add one more button Clear

Clear Button Code:-

Import the below classes

1. import oracle.apps.fnd.framework.webui.OAWebBeanConstants;

2. import oracle.apps.fnd.framework.webui.beans.message.OAMessageTextInputBean;

     if(pageContext.getParameter("clear")!=null)  //id of clear button

               {

  OAMessageTextInputBean mstb = OAMessageTextInputBean)webBean.findChildRecursive("Hello");                           mstb.setValue(pageContext,null);

               }

What is MDS?

MDS means Meta Data Service

Meta:- Meta means dictionary. Think of a web page broken into small units which are fields, buttons, list boxes. These small individual units[fields, buttons etc] are stored in a dictionary, in the database. These units when combined together, they become a webpage that gets rendered on the browser.

Data:- Those meta pieces are not stored as binary files, but as data in tables. Those tables begin with JDR, for example JDR_ATTRIBUTES, JDR_ATTRIBUTES_TRANS, JDR_COMPONENTS & JDR_PATHS. The definition and relationship of each field/region/component is stored in these JDR tables. OA framework reads that data when you request a page. The page structure is then built based on MetaData.

Service:- Meta Data is available as a service(plain service not webservice). The data is there in JDR tables, but all such data has to be co-related, all fields, regions,buttons etc have to be clubbed into a meaningful manner to make a web page. You can say that MDS provides service to store & return page definitions. MDS collates those definitions in components/fields in a meaningful manner to build a page.

what are JDR tables?

JDR_PATHS: Stores the path of the documents, OA Framework pages and their parent child relationship.

JDR_COMPONENTS: Stores components on documents and OA Framework pages.

JDR_ATTRIBUTES: Stores attributes of components on documents and OA Framework pages.

JDR_ATTRIBUTES_TRANS: Stores translated attribute values of document components or OA framework pages.

MDS is located in every module top.

Partial Page Rendering In OAF

Whatever item we place in the region, it has Action Type which is a client Action.

Action Type is of 3 Types

1.    None

2.    fireAction

3.    firePartialAction

None will not perform any action

If you want to refresh a specific part of a page without disturbing the entirepage, we go for firePartialAction.

Example on Partial Page Rendering.

1.       Suppose I entered text in the “State” field, City field should be enabled.

2.       Suppose I clear the text in the “State” field, City field should be disabled.

Steps Need to perform for this Example

1.       Create a New OAworkspace and project

2.       Create AM

3.       Create 1 VO and without Query. In the attributes pages, create 2 Transient Attributes, 1 with Primary key.

Transient Attribute means the attribute which is not a Database Attribute.

        In VO we need to create 2 transient attributes in the Attributes page.

      Click on New to Create the 1^st Attribute.

Attribute	Property
Name	RowKey
Type	Number
Updateable	Always
Key Attribute	(Checked)

Make this attribute as Primary Key.

Click on New to Create another Attribute

Attribute	Property
Name	CityRender
Type	Boolean
Updateable	Always

4.       Create 1 Page, 1^st Region is “PageLayoutRegion”

                           2^nd Region is main region which is message component layout

Under main region

I am creating the text items

(i)                  Name

(ii)                State

(iii)               City

        Based on the State, City Field should be displayed.

 For State field, Action type is firePartialAction

EventName is StateChange

In the City Field Render Property should be ${oa.PPRVO1.CityRender} and action Type is None.

Code written in Application Module:-

public void handlePPRAction() 

{ 

 Number val = 1;

 OAViewObject vo = (OAViewObject)findViewObject("PPRVO1");

 if (vo != null) 

 { 

  if (vo.getFetchedRowCount() == 0) 

  { 

   vo.setMaxFetchSize(0); 

   vo.executeQuery(); 

   vo.insertRow(vo.createRow()); 

   OARow row = (OARow)vo.first();        

   row.setAttribute("RowKey", val);

   row.setAttribute("CityRender", Boolean.FALSE);   

  }

 }

}

Code written in Process Request:-

PPRAMImpl am = (PPRAMImpl)pageContext.getApplicationModule(webBean);

   

 am.invokeMethod("handlePPRAction");

Code written in Process Form Request:-

PPRAMImpl am = (PPRAMImpl)pageContext.getApplicationModule(webBean);

 OAViewObject vo = (OAViewObject)am.findViewObject("PPRVO1");

 OARow row = (OARow)vo.getCurrentRow();

//Checking the State Field whether value is present or not    

 if ("StateChange".equals(pageContext.getParameter(EVENT_PARAM)))

 {

  if (pageContext.getParameter("State").equals(""))

  {

   row.setAttribute("CityRender", Boolean.FALSE);

  }

  else

  {

   row.setAttribute("CityRender", Boolean.TRUE);

  }

 }

Deployment of Page in Oracle Application

Deployment of OAF Page in Apps Environment

Go to folder in which you have installed the OAF Patch

D:/OAF/jdevhome/jdev/myprojects

Open winscp and go to JAVA_TOP

JAVA_TOP:- /u01/d02/apps/apps_st/comn/java/classes

Move the project folder into the Java_Top

And go to the path

D:\OAF\jdevhome\jdev\myclasses

And move the project folder into the JAVA_TOP/myclasses

JAVA_TOP:- /u01/d02/apps/apps_st/comn/java/classes/myclasses

Go to Application top and run the environmental files

Path:- /u01/d02/apps/apps_st/appl

Run ls *.env

And then . *.env

After that

Go to the JAVA_TOP path

Cd /u01/d02/apps/apps_st/comn/java/classes

Run the import script

java  oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/dhanush/oracle/apps/po/FirstPage/webui/FirstPagePG.xml -username apps -password apps -dbconnection  "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.XX.XX)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=VISION)))" -rootdir $JAVA_TOP/dhanush/oracle/apps/po

After doing XMLImporter, then we have to register the OAF Page in the Apps Environment

Login with the credentials and go to the application developer responsibility

Application ------------------------à Function

Give the Function Name:-----------------------------

Give the User Function Name:-------------------------------

Go to the properties tab:-

Type:- SSWA jsp function

In the WEB HTML tab give the HTML Call, PageName we need to mention here.

Attach the OAF Page to the menu , suppose Order Management Responsibility is there , get the menu name of the Order Management Responsibility and attach the Function Name in the Menu.

Calling D2K Form from OAF Page

1.   Create AM

2.   Create the Page and attach the AM to the Page and create one button item in the rowlayout region and the item should be submitbutton and give the attributeset as /oracle/apps/fnd/attributesets/Buttons/Go

3.   Create one Controller

In the process form request we need to call the D2k Form by clicking the button.

4.   Syntax for calling the D2K Page from OAF Page

SYNTAX: -  

form:FND:RESPONSIBILITY KEY:DATA GROUP:FUNCTION NAME(D2K FORM NAME).

For this we need to know the following

Responsibility Name: - System Administrator.

Responsibility Key: - SYSTEM_ADMINISTRATOR.

Function name: - FND_FNDCPMCP_SYS

Data Group:- STANDARD.

We need to call this syntax in the Process Form Request

"form:FND: SYSTEM_ADMINISTRATOR:STANDARD: FND_FNDCPMCP_SYS"; 

Code in the Controller under Process Form Request

if(pageContext.getParameter("item1")!=null)

    {

   

    String  FormPath = "form:FND:SYSTEM_ADMINISTRATOR:STANDARD:FND_FNDCPMCP_SYS";

   

      pageContext.forwardImmediatelyToForm(FormPath);

     

    }

While opening the Form from OAF page we need to open in the following manner

pageContext.forwardImmediatelyToForm(FormPath);

Inserting the Records into OAF Page

Before inserting the records into OAF Page, we need to know which components will effect

1.     AM needs to be created

2.     Page needs to be created and we need to attach the AM in the page

3.     As we are doing DML operations we need to create EO

4.     After creating the EO , we need to attach to the VO by creating the EO Based VO

5.     After creating the VO, need to attach this VO to AM

Steps need to Follow:-

1.     First create a workspace and then project

2.     Create 1 application module

3.     After that create 1 EO, for selecting the table for which we need to insert the records.

If we are inserting the records into custom table, we need to create the table in the database so that while creating the EO, the table will be appear, otherwise it will not appear

4.     After create the VO based on the EO, while attaching the EO select the attributes which needs to be entered while entering in the OAF Page

5.     After that Attach this VO to the Application Module

6.     After that create the Page and attach AM to the Page.

7.     Change the main region as Page Layout Region and add the AM Definition and Window title, Page title.

8.     Create the Text Input items based on your query written in the VO

9.     Create 1 button as ADD

10.                         Write the code in the Application Module for inserting the records into table

  public void InsertRecord()

  {

    insertVOImpl vo= getinsertVO1();

    OADBTransaction trans= getOADBTransaction();

    vo.executeQuery();

    Row v_row;

    v_row = (Row)vo.createRow();

    vo.insertRow((Row)v_row);

  }                            

11.                        Call this AM in Controller

In Process Request

  public void processRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    super.processRequest(pageContext, webBean);

    insertAMImpl am=(insertAMImpl)pageContext.getApplicationModule(webBean);

      am.InsertRecord();

  }

In Process Form Request

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    super.processFormRequest(pageContext, webBean);

  

    insertAMImpl am=(insertAMImpl)pageContext.getApplicationModule(webBean);

    if (pageContext.getParameter("Go") != null)

                    {

                           String userContent= pageContext.getParameter("empno");

                            Pattern p=Pattern.compile("[^0-9\\s]");

                            Matcher m=p.matcher(userContent);

                            if(m.find())

                           {

                           throw new OAException("Special Characters are not allowed",OAException.ERROR);

                           }

                           else

                           {

                               am.getOADBTransaction().commit();

                               throw new OAException("Employee Created sucsessfully",OAException.CONFIRMATION);

                           }                                              

                    }