Advanced Table Functionalities in OAF!


1] Add A Row:

To add an ‘Add a Row’ button, in the advanced table, go to

Advanced Table > Advanced Table Components > Footer > Table Footer > New > Add A Row.

In the Structure pane, select the addTableRow item that is newly created, as shown in the figure above, and use the Property Inspector to set its following properties (* Required):

ID* – specify an identifier that uniquely identifies the addTableRow item in the page.

Add Rows Label – specify text to override the “Add Another Row” default text that appears in the Add Another Row button.

Rows to Add – specify the number of rows to add each time a user chooses the Add Another Row button. The value for this property must not be greater than the value set for the Records Displayed property on the advanced table. The default is 1. Note that this property is valid only if the Insert Rows Automatically property is set to True.

Insert Rows Automatically – specify True to indicate rows are to be added automatically when the Add Another Row button is chosen. Specify False if you want to respond to the event in your controller, so that you can, for example, set the primary keys or set some default row attributes. Note: The Insert Rows Automatically property is equivalent to the ‘autoInsertion’ property that was set in your controller using the setAutoInsertion API for classic tables.

Add a Row in OAF Advanced Table

If you want to handle the Add a Row Event in the Controller, First set Insert Rows Automatically to False and then in controller, processFormRequest method, call your AM method when user click on the ‘Add a Row’ in your advanced table – ‘AdvTblRN’.

if  ("addRows".equals(pageContext.getParameter("event")) &&
"AdvTblRN".equals(pageContext.getParameter("source")) )
{
  System.out.println("The source is :"+pageContext.getParameter("source"));
  String p_header_id = (String)pageContext.getTransactionValue("p_tx_header_id");
  Serializable[] param = {p_header_id};
  am.invokeMethod("createRow", param);
}

In AMImpl wrote the method:

public void createRow( String p_header_id)
{
  OAViewObject pervo = getPriceLinesVO1();    // Advanced Table VO
  if(!pervo.isPreparedForExecution()){
    pervo.executeQuery();
  }
  pervo.setMaxFetchSize(0);
  pervo.last();   // Go to the last Row of the VO
  String line_number =  pervo.getCurrentRow().getAttribute("LineNumber").toString();
  String description =  pervo.getCurrentRow().getAttribute("Description").toString();
  Number line_num = new Number(Integer.parseInt(line_number) + 1);
  pervo.next();
  Row row = pervo.createRow(); // Create a New Row
  // Insert the values in the row.
  Number header_id = new Number(Integer.parseInt(p_header_id));
  row.setAttribute("HeaderId",header_id);
  row.setAttribute("LineId",getOADBTransaction().getSequenceValue("XX_PRICE_LINES_SEQ"));
  row.setAttribute("Description",description);
  row.setAttribute("LineNumber",line_num);
  pervo.insertRow(row);  // Insert the row in DB
  row.setNewRowState(Row.STATUS_NEW);
  getOADBTransaction().commit();
  System.out.println("Commit Done");
}

2] Delete Multiple Rows:

Create a Transient Attribute of type String with any name (say SelectFlag) in the VO. Choose updatable always check button.

To add a Delete button, in the advanced table, go to

Advanced Table > Advanced Table Components > TableSelection > MultipleSelection > New > SubmitButton.

In the MultipleSelection, add the view attribute defined above.

Multiple delete in OAF Advanced Table

In the controller, add logic as below:

if (pageContext.getParameter("DelBtn") != null)
{
  System.out.println("In Delete Button");
  System.out.println("The source is:"+pageContext.getParameter("source"));
  OAViewObject linesVO=(OAViewObject)am.findViewObject("PriceLinesVO1");
  Row[] row=linesVO.getFilteredRows("SelectFlag","Y");
  System.out.println("Total Rows: "+linesVO.getRowCount());
  System.out.println("Selected Rows: "+row.length);
  Row row[] = linesVO.getAllRowsInRange();
  for (int i=0;i<row.length;i++)
     {
       PriceLinesVORowImpl rowi = (PriceLinesVORowImpl) row[i];
       if (rowi.getSelectFlag()!= null && rowi.getSelectFlag().equals("Y"))
       {
         rowi.remove();
       }
     }
  am.getOADBTransaction().commit();
}

3] Update Multiple Rows:

Create a Transient Attribute of type String with any name (say SelectFlag) in the VO. Choose updatable always check button.

To add a Update button, in the advanced table, go to

Advanced Table > Advanced Table Components > TableSelection > MultipleSelection > New > SubmitButton.

In the MultipleSelection, add the view attribute defined above.

In the controller, add logic as below:

if (pageContext.getParameter("UpdateBtn") != null)
{
  System.out.println("In Update Button");
  Number user_id = new Number(am.getOADBTransaction().getUserId());
  String updated_by = user_id.toString();
  OAViewObject linesVO=(OAViewObject)am.findViewObject("PriceLinesVO1");
  Row row[] = linesVO.getAllRowsInRange();
  for (int i=0;i<row.length;i++)
    {
      PriceLinesVORowImpl rowi = (PriceLinesVORowImpl) row[i];
      if (rowi.getSelectFlag()!= null && rowi.getSelectFlag().equals("Y"))
        {
          String Bucket_low = rowi.getAttribute("BucketLow").toString();
          String Bucket_high = rowi.getAttribute("BucketHigh").toString();
          Number Price = (Number) rowi.getAttribute("Price");
          Serializable[] param = {rowi.getAttribute("LineId").toString(),rowi.getAttribute("BucketLow").toString(),rowi.getAttribute("BucketHigh").toString(),rowi.getAttribute("Price").toString(),updated_by};
          am.invokeMethod("UpdateLinesRecord",param);
        }
     }
  OAViewObject volns = (OAViewObject)am.findViewObject("PriceLinesVO1");
  volns.clearCache();
  volns.reset();
  volns.executeQuery();
}

In AM method, you can call PLSQL Procedure to update the data.

public void UpdateLinesRecord(String LineId, String BucketLow, String BucketHigh, String Price, String updated_by)
{
try {
     CallableStatement cs=this.getOADBTransaction().
     getJdbcConnection().prepareCall("{call APPS.XXTEST_LINES_PKG.UPDATE_LINES(?,?,?,?,?)}");
     cs.setString(1,LineId);
     cs.setString(2,BucketLow);
     cs.setString(3,BucketHigh);
     cs.setString(4,Price);
     cs.setString(5,updated_by);
     cs.execute();
     cs.close();
    }
catch(SQLException e) {
System.out.println("the exception is"+e);
    }
}

4] Delete or Update Single Row at one time.

If you want to add update and delete icons per rows do the followings:

  1. Create Columns in the advanced table say ‘Delete’ and ‘Update’.
  2. In the Delete Column, add an image item (deleteicon_enabled.gif). For all image names go to $OA_MEDIA folder.
  3. Set Action type to fire action.
  4. Give any name to Event (Say – ‘deleteLine’)
  5. In the parameter, pass the identifier of the row which user wants to delete. You can use SPEL for that.

Example>> p_header_id: ${oa.QotDtlVO1.HeaderId}

In the controller, handle the event – ‘deleteLine’.

if ("deleteLine".equals(pageContext.getParameter(EVENT_PARAM)))
{
//Handle delete logic here. You can call AM methods which eventually calls PLSQL Procedures to delete the data.
}

Similarly you can add image link for update also.

Do not forget to reset the VO after operation to reflect the new data.

Naming Standards of Commonly Used OAF Components


1] Naming Standards for a Custom Package:

If you want to create new pages, business logic, or whole applications and deploy your code with existing Oracle E-Business Suite applications, you must define a new application and use its short name for any related package and seed data definitions. For example, if you want to create a new, custom Procurement application, you might create a product with the short name XXPO (the “XX” prefix ensures that your selected product short name never conflicts with any future Oracle E-Business Suite product names).

Any objects that you create — either by extending (subclassing) existing Oracle E-Business Suite objects or by creating new objects from scratch — must be added to a package that starts with your company’s identifier:

<myCompanyName>.oracle.apps….

  • If you are creating new objects from scratch, you should put them in a package that also includes your custom application short name as follows:

<myCompanyName>.oracle.apps.<customProductShortName>….

For example, assuming your company is called ABC Corporation and your custom product short code is XXPO, any new classes and OA Components that you create should be added to the following package:

abc.oracle.apps.xxpo….

  • If you are extending existing Oracle-Business Suite objects, you may add your files to a package that corresponds directly to the original Oracle package (in this case, you don’t need to add your files to a package including a custom application short code).

2] Naming Standards for a Page (File Extension: .xml):

The page name should convey the object it presents (an employee, a supplier, an item, a purchase order, an applicant, and so on), and the function being performed (search, promote, hire, approve, view). For some pages, the object is sufficient.

<Object><Function>PG or <Object>PG

Examples:

  • SuppliersPG.xml (Supplier update)
  • SupplierCreatePG.xml (differentiated only if update and create are separate tasks)
  • SupplierViewPG.xml (view supplier info)
  • SupplierSearchPG.xml (search for supplier)
  • SupplierHomePG.xml (Supplier home page)

3] Naming Standards for a Region (File Extension: .xml):

The region name should convey the object it presents (an employee, a supplier, an item, a purchase order, an applicant, and so on), and the function being performed or the structure (search, promote, hire, approve, view, table, HGrid, Tree and so on).

<Object><Function-Structure>RN or <Object>RN

Examples:

  • SupplierDetailsRN.xml
  • PoApproveRN.xml
  • CustomerContactsRN.xml
  • ItemTypeHGridRN.xml 

4] Naming Standards for an Entity Object (File Extension: .xml, .java):

The EO should be named for the objects stored in its underlying entity. For example, the entity FWK_TBX_PO_HEADERS stores purchase order headers, so the associated EO name is PurchaseOrderHeaderEO.

<EntityName>EO

Examples:

  • EmployeeEO.xml
  • EmployeeEOImpl.java
  • SupplierEO.xml
  • SupplierEOImpl.java
  • SupplierSiteEO.xml
  • SupplierSiteEOImpl.java
  • PurchaseOrderHeaderEO.xml
  • PurchaseOrderHeaderEOImpl.java
  • PurchaseOrderLineEO.xml
  • PurchaseOrderLineEOImpl.java

5] Naming Standards for an Entity Association Object (File Extension: .xml):

The AO name should convey the relationship between a parent and its child entities.

<Parent>To<Child>AO

Examples:

  • PoHeaderToLinesAO.xml
  • SupplierToSitesAO.xml
  • EmployeeToContactsAO.xml

6] Naming Standards for a View Object (File Extension: .xml, .java):

The VO name should convey the nature of the query. VO names are always plural as they model a collection of rows.

<DescriptiveName>VO

Examples:

  • AllEmployeesVO.xml
  • AllEmployeesVOImpl.java
  • AllEmployeesVORowImpl.java
  • NewCustomersVO.xml
  • NewCustomersVOImpl.java
  • NewCustomersVORowImpl.java

7] Naming Standards for a View Link (File Extension: .xml):

The VL name should convey the relationship between the master and detail VOs.

<Master>To<Detail>VL

Examples:

  • EmployeeToDepartmenstVL.xml
  • PoHeaderToLinesVL.xml
  • ItemToApprovedSuppliersVL.xml

8] Naming Standards for an Application Module (File Extension: .xml, .java):

The AM name should convey the purpose of the UI module it services.

<ModuleName>AM

Examples:

  • EmployeesAM.xml
  • EmployeesAM.java (optional interface)
  • EmployeesAMImpl.java
  • ItemCatalogAM.xml
  • ItemCatalogAMImpl.java
  • PoSummaryAM.xml
  • PoSummaryAMImpl.java
  • ApprovedSupplierListAM.xml
  • ApprovedSupplierListAMImpl.java

WFLOAD Command for Oracle Workflow Migration


We use the Workflow Definitions Loader to save or load process definitions from a database or flat file. We can also define as it is a utility that moves workflow data between a file and a database and it is also used to upgrade, upload and download the workflow data.

Following is the syntax and the parameter details of WFLOAD.

WFLOAD <apps/pwd>@<connect_string> 0 Y {UPLOAD | UPGRADE | FORCE} <filepath>[<file_name.wft>]

Example:

WFLOAD apps/pwd@<connect_string> 0 Y UPLOAD $XXSCM_TOP/install/POAPPRV.wft

Different “Upload Modes” applicable to WFLOAD:

UPGRADE Honors both protection and customization levels of data
UPLOAD Honors only protection level of data [No respect of Customization Level]
FORCE Force upload regardless of protection or customization level

Use the following command to download workflow definition:

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD file_name.wft <Item_Type>

Example:

WFLOAD <apps_user_name>/<password>@db 0 Y DOWNLOAD POAPPRV.wft

As a standard practice, Oracle’s upgrade patches mostly run in UPGRADE mode, hence your customizations can protected if best practices for development are followed.

One can find the seeded workflow definition files under$<PRODUCT_TOP>/patch/115/import/<LANG> directory.

For example, .wft files related to Purchasing can be located in the following directory:-
$PO_TOP/patch/115/import/US

Oracle Workflow Access Protection: A Technical Note


Oracle Workflow assigns a protection and customization level to every workflow object definition stored in the database and requires every user of Oracle Workflow to operate at a certain access level.

The combination of protection, customization, and access levels build the access protection feature and determine whether a user can modify a given workflow object. The level, in all three cases, is a numeric value ranging from 0 to 1000 that indicates the relationship between different organizations as providers and consumers of seed data.

The following ranges of levels are there in Oracle Workflow:

0-9 Oracle Workflow
10-19 Oracle Application Object Library
20-99 Oracle Applications development
100-999 Customer organization. You can determine how you want this range to be interpreted. For example, 100 can represent headquarters, while 101 can represent a regional office, and so on.
1000 Public

Access Level:

Each user of Oracle Workflow operates the system at a certain access level according to the range of levels listed above. A “user of Oracle Workflow” in this case, represents someone who is operating Oracle Workflow Builder, or the Workflow Definitions Loader program, which loads workflow process definitions from a file into a database.

Navigation to view your access level:

Oracle Workflow Builder > Help > About Workflow

Workflow Access Level

Note: If you are going to run the Workflow Definitions Loader program to download workflow process definitions from the database to a file, check the value for the environment variable WF_ACCESS_LEVEL on your workflow server.

The Access tab lets you define whether:

  • Future customizations to the object are preserved during a workflow definition upgrade.
  • The object can be edited by users operating at a higher access level.

Protection Level:

Whenever you create a workflow object in Oracle Workflow Builder, you have the option of protecting the object at a certain level. An object’s protection level helps control whether other users can modify the object based on their access levels, by allowing only users with an access level equal to or lower than the object’s protection level to modify the object.

Navigation to set the protection level of an object:

Oracle Workflow Builder > object’s property page > Access tab

Notes:

  • If you protect an object against customization, you effectively assign the object a protection level equal to your current access level.
  • Objects protected against customizations are considered “seed data.”
  • Only users operating at an access level equal to or lower than the protection level of the object can modify the object.
  • Users operating at an access level greater than the protection level of the object will see a small lock on the icon for the object in the navigator tree, indicating that the object is read-only.

Customization Level:

An object’s customization level helps control whether other users can modify the object based on their access levels, by allowing only users with an access level equal to or higher than the object’s customization level to modify the object.

Navigation to set the customization level of an object:

Oracle Workflow Builder > object’s property page > Access tab

Setting the customization level ensures that a customizable object that has been customized never gets overwritten during a seed data upgrade, because the upgrade always occurs with the Workflow Definitions Loader operating at an access level below the customized object’s customization level.

Use the Options region to set the protection and customization levels of an object.

Lock at this Access Level: Protects the object at the current access level and does not allow the object to be customized by higher access levels

Preserve Customizations: Prevents customized objects from being overwritten during a workflow definition upgrade.

Situation
Access Level
Lock at this Access Level
Preserve Customization
Level Summary
1 Set as 100

No

No

A: 100, P: 1000, C: 0
2 Set as 100

Yes

No

A: 100, P: 100, C: 0
3 Set as 100

No

Yes

A: 100, P: 1000, C: 100
4 Set as 100

Yes

Yes

A: 100, P: 100, C: 100

The indicator bar provides a visual range of access levels that can edit the object:

Black vertical line: Current access level

White range: Can’t edit the object

Solid green: Can edit the object

Cross-hatch green: Usually cannot modify the object because it has been customized, but can now do so because Oracle Workflow Builder is set to upload mode so that customized objects can be modified.

1] Allow access to everyone

Workflow Protection Level-1

None: Object can be updated at any time by any access level.

2] Limit access to users with access levels equal to your own or lower

Workflow Protection Level-2

Lock at this Access Level: Protects the object at the current access level and does not allow the object to be customized, except by access levels 0-100.

3] Limit access to users with access levels equal to your own or higher

Workflow Protection Level-3

Preserve Customizations: Disallows customized objects from being overwritten during a workflow upgrade.

  • Object may be updated by access levels 100-1000.
  • If the Allow modifications of customized objects check box is selected, customized objects can also be updated by access levels 0-99 as represented by green crosshatches in the indicator bar.

4] Limit access to users with access levels equal to your own

Workflow Protection Level-4

Both: Object can only be updated by the access level at which the object is protected.

  • Object may only be updated by access level 100.
  • If the Allow Modifications of Customized Objects check box is selected, customized objects can also be updated by access levels 0-99 as represented by green crosshatches in the indicator bar.

Oracle E-Business Suite customers should select both the Preserve Customizations and Lock at this Access Level check boxes to protect your workflow objects during upgrades.

During an Oracle Workflow seed data upgrade, the Workflow Definitions Loader is always run in Upgrade mode at an access level less than 100. As a result, the upgrade will not overwrite any object with a customization level of 100 or higher.

References:

docs.oracle.com

oracle.anilpassi.com

Create XML Publisher Report using Data Templates


The data templates are useful when you need to create a XML Publisher report without using the RDF. The XML Publisher data engine enables you to rapidly generate any kind of XML data structure against any database in a scalable, efficient manner which you can easily use in your templates.

The data template is the method by which you communicate your request for data to the data engine. It is an XML document whose elements collectively define how the data engine will process the template to generate the XML.

The Data Template Definition:

The data template is an XML document that consists of four basic sections:

 Sections of Data Templates

  • define parameters,
  • define triggers,
  • define data query,
  • define data structure

 Here is a sample data template:

Sample Data Template

1] Parameters Section:

A parameter is a variable whose value you can set at runtime. Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables at runtime. However, the Parameters section of the data template is optional.

How to Define Parameters:

<parameters>
<parameter name="P_PERIOD_FROM" dataType="character" />
<parameter name="P_PERIOD_TO" dataType="character" />
</parameters>

How to Pass Parameters:

To pass parameters, (for example, to restrict the query), use bind variables in your query. For example:

SELECT *

FROM apps.gl_balances glb

WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO;

2] Data Query Section:

The <dataQuery> section of the data template is required.

How to Define SQL Queries

The <sqlStatement> element is placed between the open and close dataQuery tags. The <sqlStatement> element has a related attribute, name. It is expressed within the <sqlStatment> tag. The query is entered in the CDATA section.

<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT *
FROM apps.gl_balances glb
WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO]]>
</sqlStatement>
</dataQuery>

Lexical References:

You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING. Use a lexical reference when you want the parameter to replace multiple values at runtime.

Create a lexical reference using the following syntax:

&parametername

Define the lexical parameters as follows:

• Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters.

• Create your query containing lexical references.

<dataQuery>
<sqlStatement name="Q_Lexical">
<![CDATA[ &p_query ]]>
</sqlStatement>
</dataQuery>

<dataTrigger name="beforeReport" source="xxfin_test_pkg.beforeReport" />

In the function xxfin_test_pkg.beforeReport, you can design your query at runtime as below:

p_query VARCHAR2(2000) :=

‘SELECT * apps.gl_balances glb

WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO’;

How to Define a Data Link between Queries:

If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries.

The following example shows a query link using a bind variable:

<dataQuery>
<sqlStatement name="Q1">
<![CDATA[SELECT PARENT_COL FROM XXTEST_LINK_TABLE1]]>
</sqlStatement>
</dataQuery>

<dataQuery>
<sqlStatement name="Q2">
<![CDATA[SELECT CHILD_COL FROM XXTEST_LINK_TABLE2]]>
</sqlStatement>
</dataQuery>

<link name="TEST_LINK" parentQuery="Q1" parentColumn="PARENT_COL"
childQuery="Q2" childColumn="CHILD_COL"/>

The <link> element has a set of attributes. Use these attributes to specify the required link information. You can specify any number of links.

3] Using Data Triggers:

Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.

Data triggers are optional, and you can have as many <dataTrigger> elements as necessary. The <dataTrigger> element has a set of related attributes. These are expressed within the <dataTrigger> tag.

For example:

<dataTrigger name="beforeReport1" source=" xxfin_test_pkg.beforeReport()"/>
<dataTrigger name="beforeReport2" source=" xxfin_test_pkg.beforeReport(:Parameter)"/>
  • Name: The event name to fire this trigger.
  • Source: The PL/SQL <package name>.<function name> where the executable code resides.

4] Data Structure Section:

In the data structure section you define what the XML output will be and how it will be structured. The complete group hierarchy is available for output. You can specify all the columns within each group and break the order of those columns; you can use summaries, and placeholders to further customize within the groups.

Sample Data Structure:

<dataStructure>
<group name="GROUP_1" source="Q1">
  <element name="LEDGER_ID" value="LEDGER_ID" />
  <element name="LEDGER_SHORT_NAME" value="LEDGER_SHORT_NAME" />
  <element name="LEDGER_DESCRIPTION" value="LEDGER_DESCRIPTION" />
  <element name="LEDGER_NAME" value="LEDGER_NAME" />
  <element name="LEDGER_SUM_BAL_DR" value="ACCT_SUM_BAL_DR" function="SUM()" />
  <element name="LEDGER_SUM_BAL_CR" value="ACCT_SUM_BAL_CR" function="SUM()" />
<group name=" GROUP_2" source="Q1">
  <element name="CODE_COMBINATION_ID" value="CODE_COMBINATION_ID" />
  <element name="ACCOUNTING_CODE_COMBINATION" value="ACCOUNTING_CODE_COMBINATION" />
  <element name="CODE_COMBINATION_DESCRIPTION" value="CODE_COMBINATION_DESCRIPTION" />
  <element name="ACCT_SUM_PR_DR" value="PERIOD_NET_DR" function="SUM()" />
  <element name="ACCT_SUM_PR_CR" value="PERIOD_NET_CR" function="SUM()" />
  <element name="ACCT_SUM_BAL_DR" value="BEGIN_BALANCE_DR" function="SUM()" />
  <element name="ACCT_SUM_BAL_CR" value="BEGIN_BALANCE_CR" function="SUM()" />
<group name=" GROUP_3" source="Q1">
  <element name="PERIOD_YEAR" value="PERIOD_YEAR" />
  <element name="PERIOD_NUMBER" value="PERIOD_NUMBER" />
  <element name="PERIOD_NAME" value="PERIOD_NAME" />
  </group>
  </group>
  </group>
</dataStructure>

How to Call a Data Template:

There are two methods for calling the data engine to process your data template:

• Concurrent Manager

• Data Engine Java APIs

Before you can use either of these methods, you must first register your data template in the Template Manager as a Data Definition. After that you attached the RTF template to that data definition.

Data def for data template

Calling a Data Template from the Concurrent Manager:

To use the concurrent manager to execute your data template, you must register a Concurrent Program, using the define Concurrent Programs form:

CP Def for data template

Executable Name Enter the XML Publisher data engine executable: XDODTEXE

Output Format Select “XML” as the output format.

Note: We do not need to create a concurrent executable as we use a seeded executable XDODTEXE

Concurrent Program – Parameters:

For each parameter in the Data Template, define a parameter in the concurrent program.  The Data Template parameter name should match the concurrent program parameter token

Few tips for best practices:

Performing operations in SQL is faster than performing them in the data template or PL/SQL. It is recommended that you use SQL for the following operations:

  • Use a WHERE clause instead of a group filter to exclude records.
  • Perform calculations directly in your query rather than in the template.

 To maximize performance when building data queries in the data template: XML Publisher tests have shown that using bind variables is more efficient than using the link tag.

The dataStructure section is required for multiple queries and optional for single queries. If omitted for a single query, the data engine will generate flat XML.

How to design An Accounting Flexfield Parameter for your Report?


If you want to design similar functionality as below in your report parameter, do the below steps.

Concurrent Program

1] Design two parameters to capture Accounting Flexfield From and To and use them in your report. Also set the default values.

CP Parameters

Here use the value set: XLA_SRS_ACCOUNTING_FLEXFIELD

2] XLA_SRS_ACCOUNTING_FLEXFIELD valueset is designed as below:

XLA_SRS_ACCOUNTING_FLEXFIELD

Edit:

FND POPIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”

  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS REQUIRED=”Y”

  VALIDATE=”NONE” SEG=”:!VALUE” DESC=”:!MEANING”

  NAVIGATE=”!DIR” VDATE=””

Validate:

FND VALIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”

  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS VALIDATE=”NONE”

  REQUIRED=”Y” DESC=”:!MEANING” SEG=”:!VALUE” VDATE=””

Special Value set

How to make parameters optional in Discoverer?


When you create a parameter for your discoverer report, sometimes we need to make the parameter as optional. You cannot make parameters as optional in Discoverer Desktop. This feature is only available in Discoverer Plus.

Discoverer Parameter in desktop

Discoverer Parameter in plus

OAF MVC Architecture


OAF is a java based application framework to develop web based applications that link to Oracle Applications instance while maintaining all the security features of that apps instance. A framework is a specialized set of related classes designed to make application development easier.  In effect, a framework implements part of an application so developers don’t have to write all of its code from scratch; they can use the framework as the basis for their work and while focusing on the additional code required to implement their specific application requirements.

OA Framework follows Model, View and Controller (MVC) Architecture as described below:

1] Model

Model contains the components which handles data directly from Database. It Includes Business Components for Java (BC4J Objects) which mainly are:

Entity Objects (EO):

Entity Objects are generally based on one table which encapsulate the business rules. These objects are used by OAF page to perform update/insert/delete operations. You can join two EOs using Entity Associations.

View Objects (VO):

These objects contain a SQL query that queries the data from database and present it in the OAF page. VOs can be based on one or many EOs or a SQL query. Two VO can be linked together through a View Link.

Application Module (AM):

It is a container for related BC4J objects and provides the database connection. It also provides Transaction Context (OADBTransaction) or Transaction Management. An AM can have more nested AM contained in it along with other BC4J components. But it is mandatory to have an AM for an OAF page.

2] View

View contains the actual page items on page which user can see. The view in OAF comprises of various page level items like text fields, buttons, regions, links etc. These items are visible on any OAF page. These items can either be tied to VO attribute or having a constant value or populated at run time based through controller logic.

Please note that- View Layer is altogether different than View Object!!!!!

3] Controller

Controller handles all the user actions done on the page. OAF requires a java controller class to be defined for a page/region which handles various page level actions. The important methods in this class are:

A] ProcessRequest

     This request is called when page is rendered. Any logic to be executed during page initialization is kept here.

B] ProcessFormRequest

Any page submit action causes ProcessFormRequest to be executed. The logic put here typically is that which needs to be executed after actions like button click or any other page submit action.

The Controller class is mostly used to put logic for actions on page such as button clicks, navigation to other pages. The two objects that are passed to controller methods are OAPageContext and OAWebBean. OAPageContext provides access to objects like AM class, page parameters, session values, navigation methods. OAWebBean is generally used to get a handle of page items.

In coming posts, I will try to write more details about these components and how they works….so stay tuned!

Number Formatting in XML Publisher


While designing XML Publisher reports, many a times we need to play with numbers and change their formats as part of the client’s requirements. Here is a brief explanation of couple of techniques that should be used while doing any number format change in XML Publisher.

XML Publisher supports two methods for specifying the number format:

  • Oracle’s format-number function
  • Microsoft Word’s Native number format mask

Now…before going into details please remember few things like:

  • To use the Oracle format mask or the Microsoft format mask, the numbers in your data source (ex XML File) must be in a raw format, with no formatting applied.
  • Use only one of these methods. If the number format mask is specified using both methods, the data will be formatted twice and it may cause unexpected behavior.
  • If you are designing a template to be translatable, using currency in the Microsoft format mask is not recommended unless you want the data reported in the same currency for all translations. Using the MS format mask sets the currency in the template so that it cannot be updated at runtime.

Using the Oracle Format Mask:

To apply the Oracle format mask to a form field:

1. Open the Form Field Options dialog box for the placeholder field.

2. Set the Type to “Regular text”.

3. In the Form Field Help Text field, enter the mask definition according to the following example:

<?format-number:fieldname;’999G999D99′?>

Where fieldname is the XML tag name of the data element you are formatting and 999G999D99 is the mask definition.

Symbol Meaning
0 Digit. Each explicitly set 0 will appear, if no other number occupies the position. Format mask: 00.0000 (Ex: Change 1.234 to 01.2340)
9 Digit. Returns value with the specified number of digits with a leading space if positive or a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. Format mask: 99.9999 (Ex: Change 1.234 to 1.234)
C Returns the ISO currency symbol in the specified position.
D Determines the placement of the decimal separator. The decimal separator symbol used will be determined at runtime based on template locale. Format mask: 9G999D99 (Ex: Change 1234.56 to 1,234.56( For English locale:) or to 1.234,56 (For German locale))
EEEE Returns a value in scientific notation.
G Determines the placement of the grouping (thousands) separator. The grouping separator symbol used will be determined at runtime based on template locale. Format mask: 9G999D99 (Ex: Change 1234.56 to 1,234.56( For English locale:) or to 1.234,56 (For German locale))
L Returns the local currency symbol in the specified position.
MI Displays negative value with a trailing “-“.
PR Displays negative value enclosed by <>
PT Displays negative value enclosed by ()
S (before number) Displays positive value with a leading “+” and negative values with a leading “-“
S (after number) Displays positive value with a trailing “+” and negative value with a trailing “-“

Using the Microsoft Number Format Mask:

To format numeric values, use Microsoft Word’s field formatting features available from the Text Form Field Options dialog box. The following graphic displays an example:

To apply a number format to a form field:

  1. Go into the Word Properties for the field.
  2. Change the Type drop-down box to Number (it defaults in as Regular Text)
  3. From the Number format drop-down box choose the format you want.

Native XSL format-number function:

You can also use the native XSL format-number function to format numbers. The native XSL format-number function takes the basic format:

format-number(number,format,[decimalformat])

Where:

number (Required) – Specifies the number to be formatted.

Format (Required)  – Specifies the format pattern. Use the following characters to specify the pattern:

Symbol Meaning
# Denotes a digit. Example: ####
0 Denotes leading and following zeros. Example: 0000.00
. The position of the decimal point Example: ###.##
, The group separator for thousands. Example: ###,###.##
% Displays the number as a percentage. Example: ##%
; Pattern separator. The first pattern will be used for

positive numbers and the second for negative numbers

 

Decimal format (Optional) – For more information on the decimal format please consult any basic XSLT manual.

Currency Formatting:

This is another feature of XML Publisher that enables you to define specific currency format masks to apply to your published data at runtime.

To utilize currency formatting, you must:

1. Define your currency formats in XML Publisher’s Administration interface.

2. Assign the Currency Format Set as a configuration property at the desired level (site, data definition, or template).

3. Enter the format-currency command in your RTF template to apply the format to the field at runtime.

To use the format-currency command:

In the form field dialog of the field you want to format, enter the following syntax:

<?format-currency:ELEMENT_NAME;’currency-format-code’?>

For example:

<?format-currency:BALACE_TOTAL;’USD’?>

The currency code must correspond to a currency format that is defined in the Currency Format Set to be used with this report. The Currency Format Set can be specified at the site level, data definition level, or template level.

For more information, refer Oracle XML Publisher Administration and Developer’s Guide.

Best Implementation Practices with XML Publisher!


 

© Best Implementation Practices with Oracle Business Intelligence Publisher  – by Mohan Dutt and Brent Lowe