Friday Mar 02, 2012

Refreshing One Column based on the value of Another Column in ADFdi Table

When using ADF Desktop Integration, quite frequently, we get into a situation where we would like to refresh one column based on the value of another column. In ADF Faces, we can achieve this by setting the autoSubmit property and partialTriggers property for the corresponding columns.

However, in ADFdi, we do not have such option. Though we can achieve this by using LOVs and Dependent LOVs. But, in some scenarios we would like to achieve this when using an Input Text Component.

In this article, we will simulate this Auto Refresh functionality in a ADFdi Table.

Note : Since we would be using VBA code to achieve this, we can use this only on the Macro Enabled Excel Workbooks.

Let us assume that we have a View Object based on the Emp table. We could take an example of having a transient attribute in the VO, that gives the sum of Salary and Commission attributes.


In the above example, we've added a new transient attribute (SalPlusComm) to the EmpVO, that would give the sum of Sal and Comm attributes. Since we need this attribute to get refreshed when either Sal or Comm attribute changes, we set the Sal and Comm attributes as Dependencies. Also, we set the AutoSubmit property (under UI Hints tab) for the Sal and Comm attributes.


Now, we are done with the model layer. We can now, create a jspx page and then Drag and Drop EmpView as ADF Table. After this, we create an Excel Workbook (macro enabled), enable ADF Desktop Integration for it, set the required Workbook Properties, and then add a Table based on the EmpView.


As there are no straight forward way in ADFdi to trigger a request to server when a value of a cell is changed, we will now add a DoubleClickActionSet for the Sal and Comm columns. This DoubleClickActionSet will have the Table.RowUpSync and Table.RowDownSync actions.


Above example image shows the DoubleClickActionSet for Sal column. In the same manner, we need to add the DoubleClickActionSet for the Comm column as well.

Now, we have the workbook, that would fetch the SalPlusComm attribute (after recalculation in the model), when we change the Sal / Comm attribute and then double click on that column. To do this automatically when the user tabs out / presses enter key on the cells, we'll write a bit of VBA Code on the Worksheet where we've this table (Go to Developer Tab and Click on Visual Basic).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 14 Or Target.Column = 15 Then
    Application.CommandBars("Cell").Controls("Invoke Action...").Execute
  End If
End Sub
Excel would trigger Worksheet_Change event when a cell in the worksheet is modified. So, we would code our logic in that event. The above code assumes that Sal column is present in the N (14th) column on the worksheet and Comm column is present in O (15th) column. So, we would execute our logic only when the contents in these two columns change.

ADFdi would provide a context menu (Invoke Action...) when a DoubleClickActionSet is added to a particular column. We'll make use of that context menu and invoke it programatically.


We invoke that context menu programatically using the following line of code

Application.CommandBars("Cell").Controls("Invoke Action...").Execute

Now, we run our workbook, modify the value of Sal column for any row and tab out of that field would automatically update the value of SalPlusComm column.


Here, a simple example (Transient Attribute) is taken for the explanation. In the similar fashion, we can also have a DoubleClickActionSet to contain a method in the Impl that would perform this calculation as well.

Tip: If you are not able to view the image fully, right click on the image and choose View Image option to see it completely.

Wednesday Feb 22, 2012

Dynamic Pie Graph Generation by Drag And Drop Rows from ADF Table

In normal scenarios, we display the graph and chart to get graphical representation of data. In this article, we'll see how to create graph dynamically by dragging the contents from ADF table and dropping on the graph.

Environment : JDeveloper PS5 (

Assuming that we have a table that displays the Department's details. We'll build a dynamic pie graph to display the Employee details for the departments that are dragged from the Department's table.


For our usecase, we need to drag the Departments from Table and Drop it on the Pie Chart to get the Employees details corresponding to the Depts selected.

For this, we need to add DragSource in the Dept Table and DropTarget in Emp Chart.

<af:table value="#{bindings.DeptView1.collectionModel}" var="row"
                 emptyText="#{bindings.DeptView1.viewable ? 'No data to display.' : 'Access Denied.'}"
                 rowSelection="multiple" id="t1">
            <af:dragSource discriminant="Dept" 
                  <dvt:pieGraph id="pieGraph1" subType="PIE_MULTI"
                      <af:dataFlavor flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"

Now that we've added the drag source and drop target, we need to have a method in our AM/VO's impl class, that take the department number as input and return the list of employees under it and their details as ArrayList.

    public ArrayList getEmpDetsForDept(int Deptno) {
        ArrayList empDetsAL = new ArrayList();
        getEmpView1().setWhereClause("Deptno=" + Deptno);
        if(getEmpView1().getRowCount()>0) {

            empDetsAL.add(new Object[]{""+Deptno,
                                        new Double(getEmpView1().getCurrentRow().getAttribute("Sal").toString())}

            while (getEmpView1().hasNext()){
                empDetsAL.add(new Object[]{""+Deptno,
                                            new Double(getEmpView1().getCurrentRow().getAttribute("Sal").toString())}

        return empDetsAL;

Now, the main part comes. Pie Graphs can be based on ArrayList. So, in our backing bean, we'll have an attribute of type ArrayList. Also, we'll have a variable to to bind the graph for triggering PPR.

    private List chartData=new ArrayList();
    private UIGraph empChart;

    public void setChartData(List chartData) {
        this.chartData = chartData;

    public List getChartData() {
        return chartData;

    public void setEmpChart(UIGraph empChart) {
        this.empChart = empChart;

    public UIGraph getEmpChart() {
        return empChart;

As we need to find out the Departments that are dragged from table, we'll have a method in the backing bean to get the list of dragged Departments, iterate through them, pass each to the AM / VO method created to get Emp details and then construct the ArrayList.

    public DnDAction dropDeptInPie(DropEvent dropEvent) {
        RichTable table = (RichTable) dropEvent.getDragComponent(); 
        Transferable t = dropEvent.getTransferable(); 
        DataFlavor<RowKeySet> df = DataFlavor.getDataFlavor(RowKeySet.class, "Dept"); 
        RowKeySet rks = t.getData(df); 
        Iterator iter = rks.iterator();
        if(getChartData()!=null) getChartData().clear();
        while (iter.hasNext()) { 
            List key = (List); 
            JUCtrlHierNodeBinding rowBinding = (JUCtrlHierNodeBinding) table.getRowData(); 
            Row row = (Row) rowBinding.getRow();
            String Deptno = row.getAttribute("Deptno").toString();
            BindingContainer bindings = getBindings();
            OperationBinding operationBinding = bindings.getOperationBinding("getEmpDetsForDept");
            operationBinding.getParamsMap().put("Deptno", Deptno);
            ArrayList result = (ArrayList)operationBinding.execute();

            if (operationBinding.getErrors().isEmpty()) {
                else  setChartData(result);



        return DnDAction.NONE;


    public BindingContainer getBindings() {
        return BindingContext.getCurrent().getCurrentBindingsEntry();

In order to make the Pie Graph use the ArrayList as source, we need to bind this to the tabularData Property of the pie graph. Let us also bind the chart to the variable we created in backing bean and set its dropListener to the method created above. Now, our pieGraph's source in jspx page would look like

                  <dvt:pieGraph id="pieGraph1" subType="PIE_MULTI"
                    <af:dropTarget dropListener="#{pageFlowScope.DnDBean.dropDeptInPie}">
                      <af:dataFlavor flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"

Now, let us run the page, select couple of Departments and Drop them on the Pie chart.


Here, we've dragged the departments 10 & 30, and dropped them on the chart to get the Salary of the employees belonging to those departments as slices.

Wednesday Feb 08, 2012

Passing comma separated string as bind variable for VO query's IN operator

Quite often, we want to pass a parameter to the bind variable in the VO's query with a comma separated value, for the where clause with an IN operator. However, normal SQL query that the VO contain interpret that whole comma separated value as a single String and our usecase fail to get fulfilled.

Ex. question in OTN thread :

To overcome this, Mohammad Jabr has written a blog entry with the help of  Steve Muench's example #126. This has been achieved by using a TYPE and CASTing it to get the list.

In this article, we'll see another option to alter the query using regexp_substr, without having to use a TYPE and CAST.

Let us take an example of a VO created using EMP table with the following query.



We'll add a where clause to the VO's query with a bind variable that takes comma separated string as input. Here, we would be including regexp_substr function (Oracle DB >=10g), to split the comma separated string and return them as rows.

Now, our Where clause of the VO's query would be looking like

  (select regexp_substr(:Bind_Ename_Comma_Sep_List,'[^,]+', 1, level) 
   from dual 
    connect by 
        regexp_substr(:Bind_Ename_Comma_Sep_List, '[^,]+', 1, level) 
            is not null)
Also, we'll add a Bind Variable Bind_Ename_Comma_Sep_List of String data type. Once after modifying the Where Cluase and after adding the bind variable, our VO would be looking like


To validate our query, let us run the AM tester to check the result


Validate the result by entering a comma separated ENAME list to the bind variable (SMITH,ALLEN,JONES).


Check out the query result


As it can be seen, we've passed 3 comma separated Enames to the bind variable, which in turn fetched only those records with the matching 3 Enames.

Thursday Feb 02, 2012

Drag ADF table record & drop on bar graph in Jdeveloper

In Jdeveloper,we can drag any component & drop on graph.It also supports drag/drop between graphs.

Use Case Description

Let us consider a use case wherein we will drag table row & drop that on the bar graph.

Implementation steps

Assume that we have table with Emp records & bar graph that displays Empno & Salary of Employees.

Insert drag component inside table:  <af:dragSource discriminant="tab"/>

Now insert dropTarget inside barGraph:

<af:dropTarget dropListener="#{test.handleTableDrop}">
       flavorClass="org.apache.myfaces.trinidad.model.RowKeySet"    discriminant="tab"/>

public DnDAction handleTableDrop(DropEvent dropEvent)

        // Add event code here...
        System.out.println("Dropped id is"+dropEvent.getDragClientId());
        return DnDAction.NONE;

Run the above page .Drag any table row & drop that on the bar Graph.
Note that handleTableDrop method gets executed & it prints dragged client id(i.e table id).

Wednesday Feb 01, 2012

Custom Upload options in ADF Desktop Integration


     In ADFdi enabled excel workbook while uploading the changes performed in ADF Table,standard upload options dialog appears like below.


    In this article,we will see how to customize these options in a dialog.


     Let us take a simple example of Employee table.


Assumption: An ADF Web Application with ADF Desktop Integration enabled workbook having Employee table as below screenshot is available.


Now let's create a new jspx page titled 'UploadOptions.jspx'.
The below code adds two checkboxes and buttons.Let's copy this into the Source view of jspx page.

<!-- Check box for AbortUploadOnFailure -->
<af:selectBooleanCheckbox text="Abort Upload On Failure" id="sbc1"
  value="#{requestScope.abortUploadOnFailure}" autoSubmit="true" selected="true"/>

<!--Check box for DownloadAfterUpload -->
<af:selectBooleanCheckbox text="Download After Upload" id="sbc2" autoSubmit="true"

<!--Command button for Continue action -->
<af:button text="Continue" id="b1" inlineStyle="width:100px;" partialSubmit="false">
<af:setActionListener from="continue" to="#{requestScope.action}"/>

<!-- Command button for Abort action -->
<af:button text="Abort" id="b2" inlineStyle="width:100px;" partialSubmit="false">
<af:setActionListener from="abort" to="#{requestScope.action}" />

The UploadOptions.jspx page now looks like below:


Below Span elements have to be used to perform custom upload actions in ADFdi Table.

ADFdiCloseWindow - When a web page has to be closed,this span element can be set to
Continue  - to close the web page and invoke next action in actions set or
Abort       - to close the web page and terminate the action set.
ADFdiAbortUploadOnFailure - If this element is set to True, the action set stops uploading if it encounters a failure. If the element references False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload.
ADFdiDownloadAfterUpload - If this element is set to True, the action set downloads data into the ADFdi Table component after the action set uploads modified data.

Let us add the below code at the end of tag Form in source view of jspx,to include the above mentioned span elements.

<!-- Closes the Upload Options dialog and then invokes next action in the action set-->
<f:verbatim rendered="#{requestScope.action eq 'continue'}">
<span id="ADFdiCloseWindow">Continue</span>

<!-- Closes the
Upload Options dialog but aborts next actions in action set-->
<f:verbatim rendered="#{requestScope eq 'abort'}">
<span id="ADFdi

<!-- If the span element is set to
  True, the action set stops uploading if it encounters a failure.
  False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload. -->
<span id="ADFdiAbortUploadOnFailure">${requestScope.abortUploadOnFailure}</span> 

<!--If the span element is set to
True, the action set downloads data from the Fusion web application to the ADF Table after the action set uploads modified data -->
<span id="ADFdi

In ADFdi excel workbook,let us add this new jspx file as dialog before Table.Upload action in Ribbon Commands of Worksheet Properties.


Now we run the excel workbook and download data into ADF Table as shown above.Let's update a row by changing the Sal=4000 of Martin.


On clicking Upload Ribbon command like below:


the custom upload actions dialog gets opened with 'Abort Upload On Failure' check box selected by default.

We can select 'Download After Upload' check box and click on Continue button.We can see data updated and downloaded again with new value like below:


This is a simple example of how upload options can be customized. Using this, we can add more custom options to the dialog, which is not available in the standard upload options dialog. We will see such options sooner in my next blog.

Tuesday Jan 31, 2012

What does featureOff attribute do in af:panelCollection

Panel Collection in ADF (af:panelCollection) has an attribute - featureOff, which manages the visibility of certain controls / features of panelCollection to the end user.

As per the doc guide, here are the list of valid values for this attribute.

Value Turns off
statusBar Status bar
viewMenu 'View' menu
formatMenu 'Format' menu
columnsMenuItem 'Columns' sub-menu item
columnsMenuItem:col1,col20 Columns with column ID: 'col1' and 'col20' inside 'Columns' sub-menu
freezeMenuItem 'Freeze' menu item
detachMenuItem 'Detach' menu item
sortMenuItem 'Sort' menu item
reorderColumnsMenuItem 'Reorder Columns' menu item
resizeColumnsMenuItem 'Resize Columns' menu item
wrapMenuItem 'Wrap' menu item
showAsTopMenuItem Tree/TreeTable 'Show As Top' menu item
scrollToFirstMenuItem Tree/TreeTable 'Scroll To First' menu item
scrollToLastMenuItem Tree/TreeTable 'Scroll To Last' menu item
freezeToolbarItem 'Freeze' toolbar item
detachToolbarItem 'Detach' toolbar item
wrapToolbarItem 'Wrap' toolbar item
showAsTopToolbarItem Tree/TreeTable 'Show As Top' toolbar item
wrap 'Wrap' menu and toolbar items
freeze 'Freeze' menu and toolbar items
detach 'Detach' menu and toolbar items

In this article, we will find out what happens at runtime when different values are specified for this attribute (Note : This attribute takes a space-separated list of default features to be turned off for the panelCollection - as mentioned above)

1. statusBar

 <af:panelCollection id="pc1" featuresOff="statusBar">

When we set statusBar to the featuresOff attribute, it hides panelCollection's status bar.


2. viewMenu

 <af:panelCollection id="pc1" featuresOff="viewMenu">

When we set viewMenu to the featuresOff attribute, it completely removes the View Menu.


3. formatMenu

 <af:panelCollection id="pc1" featuresOff="formatMenu">

When we set formatMenu to the featuresOff attribute, it completely removes the Format Menu.


4. columnsMenuItem

 <af:panelCollection id="pc1" featuresOff="columnsMenuItem">

When we set columnsMenuItem to the featuresOff attribute, it removes the Columns menu item under View menu. Check the next option to hide specific columns.


5. columnsMenuItem:col1,col20

<af:panelCollection id="pc1" featuresOff="columnsMenuItem:col1,col2">

When we set columnsMenuItem:.. to the featuresOff attribute, it removes the specified columns from the Columns menu item under View menu. If you want to hide the entire Columns menu item, check the previous option.


In the above example image, columns c3 and c4 belong to the columns Empno and Ename.

6. freezeMenuItem

<af:panelCollection id="pc1" featuresOff="freezeMenuItem">

When we set freezeMenuItem to the featuresOff attribute, it removes Freeze menu item under View menu .


7. detachMenuItem

<af:panelCollection id="pc1" featuresOff="detachMenuItem">

When we set detachMenuItem to the featuresOff attribute, it removes Detach menu item under View menu .


8. sortMenuItem

<af:panelCollection id="pc1" featuresOff="sortMenuItem">

When we set sortMenuItem to the featuresOff attribute, it removes entire Sort menu item under View menu .


9. reorderColumnsMenuItem

<af:panelCollection id="pc1" featuresOff="reorderColumnsMenuItem">

When we set reorderColumnsMenuItem to the featuresOff attribute, it removes Reorder Columns... menu item under View menu .


10. resizeColumnsMenuItem

<af:panelCollection id="pc1" featuresOff="resizeColumnsMenuItem">

When we set resizeColumnsMenuItem to the featuresOff attribute, it removes Resize Columns... menu item under Format menu .


11. wrapMenuItem

<af:panelCollection id="pc1" featuresOff="wrapMenuItem">

When we set wrapMenuItem to the featuresOff attribute, it removes Wrap menu item under Format menu .


12. showAsTopMenuItem

<af:panelCollection id="pc1" featuresOff="showAsTopMenuItem">

When we set showAsTopMenuItem to the featuresOff attribute, it removes Show as Top and Go to Top menu items under View menu (Applicable for the tree/treeTable) .


13. scrollToFirstMenuItem

<af:panelCollection id="pc1" featuresOff="scrollToFirstMenuItem">

When we set scrollToFirstMenuItem to the featuresOff attribute, it removes Scroll to First menu item under View menu (Applicable for the tree/treeTable) .


14. scrollToLastMenuItem

<af:panelCollection id="pc1" featuresOff="scrollToLastMenuItem">

When we set scrollToLastMenuItem to the featuresOff attribute, it removes Scroll to Last menu item under View menu (Applicable for the tree/treeTable) .


15. freezeToolbarItem

<af:panelCollection id="pc1" featuresOff="freezeToolbarItem">

When we set freezeToolbarItem to the featuresOff attribute, it removes Freeze button from the Toolbar.


16. detachToolbarItem

<af:panelCollection id="pc1" featuresOff="detachToolbarItem">

When we set detachToolbarItem to the featuresOff attribute, it removes Detach button from the Toolbar.


17. wrapToolbarItem

<af:panelCollection id="pc1" featuresOff="wrapToolbarItem">

When we set wrapToolbarItem to the featuresOff attribute, it removes Wrap button from the Toolbar.


18. showAsTopToolbarItem

<af:panelCollection id="pc1" featuresOff="showAsTopToolbarItem">

When we set showAsTopToolbarItem to the featuresOff attribute, it removes Go Up, Go to Top and Show as Top buttons from the Toolbar (Applicable for tree/treeTable).


19. wrap

<af:panelCollection id="pc1" featuresOff="wrap">

When we set wrap to the featuresOff attribute, it removes Wrap menu item from Format Menu as well as from the Toolbar. Equivalent to featuresOff="wrapMenuItem wrapToolbarItem".


20. freeze

<af:panelCollection id="pc1" featuresOff="freeze">

When we set freeze to the featuresOff attribute, it removes Freeze menu item from View Menu as well as from the Toolbar. Equivalent to featuresOff="freezeMenuItem freezeToolbarItem".


21. detach

<af:panelCollection id="pc1" featuresOff="detach">

When we set detach to the featuresOff attribute, it removes Detach menu item from View Menu as well as from the Toolbar. Equivalent to featuresOff="detachMenuItem detachToolbarItem".


These options would be useful when using a panelCollection for customizing at end user. A separate options could be provided using these, so that end user can customize the LAF of the panelCollection.

Thursday Dec 29, 2011

Business Rules Editor for View Objects in JDeveloper

JDeveloper has a new tab in the View Objects Editor - Business Rules.
This easily tend to make people misunderstand that this Editor can be used for adding validation rules for the attributes in the View Object. However, it is not true.

Lets check out what this editor is all about. As the online help for this page states
Use to create and maintain business rules based on Groovy Language expressions and declarative validation rules for this view object including:

  • Validators for transient attributes
  • Bind variable default value expressions for SQL queries
  • Bind variable value mappings for view accessors
  • Transient attribute value expressions
  • Transient attribute value expression recalculation conditions
  • Attribute default value expressions

We'll see how we can use this editor to edit different types :

Transient Attributes : 

We can use this editor for a. adding validation rules, b. edit default value expression

a. As like the Entity Attributes, we can add many validation rules for the transient attributes in VO as well.


In the above image, EmpType is a transient attribute on Emp VO.We can see there are two rules added. First one is a validation rule (List Validator), restricting the user to enter either one of the value specified for the attribute. Second one is the default value expression for the attribute. Upon selecting the node, the default value groovy expression would become editable in the Script Expression field below, wherein we can modify it and test the syntax as well.

Note : We can add the validation rule only for the transient attributes which are Updatable. Also, we can edit only the default values which are script expressions only. Not the literal values.

Bind Variables :

Similar to the transient attributes, we can use this editor for modifying and testing the default value expression for the Bind Variables of the VO as well.

Ex :

In the above example image, we can see a Bind Variable (Bind_Hiredate) added to the Query, which has the default value expression as adf.currentDate.

Using this editor, we can modify this expression and test the syntax.

View Accessor :

If we have a bind variable to the VO and use it as List Data Source for any of the attributes in the VO, we can use this editor to change the value of the bind variable in the View Accessor.

Ex :

In the above example image, we have couple of Bind Variables in the View Accessors (they are used as Lists and Dependent Lists for this View Object). Using this editor, we can edit the values of those bind variables.

We can also use this editor for modifying and testing the default value expression on the VO attributes and the re-calculation conditions.

Wednesday Nov 30, 2011

Project Gantt chart using ADF BC

This article describes simple example of using Project Gantt chart using ADF Business components.

Use Case Description

Let us create a simple Project Gantt chart using ADF Business components & try to get the selected tasks details.
Implementation steps

A project Gantt chart is used for project management. The chart lists
tasks vertically and shows the duration of each task as a bar on a
horizontal time line.

To create a basic project gantt chart,we first need to define  2 tables as below:
1)task_table with taskid,task_type,start_date & end_date
2)subtask_table with subtaskid,subtask_type,start_date, end_date &  taskid

Now we can create Business components for the above 2 tables .Then we will create new jspx page -projectGantt.jspx

Drop TaskView1 as Gantt->Project:


Select all required columns under tasks & subtasks tabs of 'create Project Gantt chart' dialog.

We have created Project Gantt chart that lists tasks & its subtasks.Now if we need to get all task details selected by the user then define taskSelectionListener for the dvt:projectGantt in jspx source page:


public void taskListener(TaskSelectionEvent taskSelectionEvent) {
// This codes gives all the tasks selected by user

System.out.println("Selected task details +taskSelectionEvent.getTask());


Run the above page & note that it shows all details of tasks nodes & expanding these tasks nodes shows its corresponding subtasks details.Now if user selects 2 tasks,we can see that it prints the complete task details for the selected tasks.

Simulating Date Picker in ADF Desktop Integration


ADF Desktop Integration doesn't have built-in Date-Picker like component to choose a date from the calendar instead of entering it manually as in ADF Faces.

In this Article, I would like to discuss how an ADF Input Text component can be made use as a Calendar component using the power of excel macros.


Let us take a simple case of Employee form with Hiredate field as date-picker


Assumption: An ADF Web Application with ADF Desktop Integration enabled workbook having Employee form as in below screenshot is readily available

Now, Our goal is to make the InputText component bound to Hiredate as Date-Picker

We can achieve this either by using Microsoft's ActiveX Calendar Control components or by building our own calendar component using macros.

The disadvantage of using Microsoft's ActiveX Calendar Control component is that we need to  register MSCAL.OCX or MSCOMCT2.OCX with every machine we are going to access this workbook and in real-time it would be difficult to maintain the control everywhere.

Here, I discuss the second approach and for which I made use of the macro code given by VBA Express to build calendar.


-  Open Microsoft Visual Basic Editor by clicking on 'View Code' button under 'Developer' Tab

-  Insert a new user form using Insert -> UserForm option and change the name of the form as CalendarFrm


- Design the form as in below screenshot and name First two combo boxes as CB_Mth and CB_Yr, Sun to Sat from Label2 to Label8 and remaining Day cells from D1 to D42 (Easiest way of designing the form is to download the workbook attached at the end and copy the form to your excel workbook so that the layout and names everything gets copied)

- Right click on the Form in Project Explorer and choose ViewCode option

- Copy below code to form to add calendar functionality

Option Explicit
Dim ThisDay As Date
Dim ThisYear, ThisMth As Date
Dim CreateCal As Boolean
Dim i As Integer

Private Sub UserForm_Initialize()
    Application.EnableEvents = False
     'starts the form on todays date
    ThisDay = Date
    ThisMth = Format(ThisDay, "mm")
    ThisYear = Format(ThisDay, "yyyy")
    For i = 1 To 12
        CB_Mth.AddItem Format(DateSerial(Year(Date), Month(Date) + i, 0), "mmmm")
    CB_Mth.ListIndex = Format(Date, "mm") - Format(Date, "mm")
    For i = -20 To 50
        If i = 1 Then CB_Yr.AddItem Format((ThisDay), "yyyy") Else CB_Yr.AddItem _
        Format((DateAdd("yyyy", (i - 1), ThisDay)), "yyyy")
    CB_Yr.ListIndex = 21
     'Builds the calendar with todays date
    CreateCal = True
    Call Build_Calendar
    Application.EnableEvents = True
End Sub

Private Sub CB_Mth_Change()
     'rebuilds the calendar when the month is changed by the user
End Sub
Private Sub CB_Yr_Change()
     'rebuilds the calendar when the year is changed by the user
End Sub
Private Sub Build_Calendar()
     'the routine that actually builds the calendar each time
    If CreateCal = True Then
        CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
        For i = 1 To 42
            If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
                Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
                Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
            ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then
                Controls("D" & (i)).Caption = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) _
                & "/1/" & (CB_Yr.Value))), ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "d")
                Controls("D" & (i)).ControlTipText = Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy")
            End If
            If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
            ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "mmmm") = ((CB_Mth.Value)) Then
                If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H80000018 '&H80000010
                Controls("D" & (i)).Font.Bold = True
                If Format(DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
                ((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), "m/d/yy") = Format(ThisDay, "m/d/yy") Then Controls("D" & (i)).SetFocus
                If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H8000000F
                Controls("D" & (i)).Font.Bold = False
            End If
    End If
End Sub
Private Sub D1_Click()
     'this sub and the ones following represent the buttons for days on the form
     'retrieves the current value of the individual controltiptext and
     'places it in the active cell
    ActiveCell.Value = D1.ControlTipText
    Unload Me
     'after unload you can call a different userform to continue data entry
     'uncomment this line and add a userform named UserForm2
End Sub
Private Sub D2_Click()
    ActiveCell.Value = D2.ControlTipText
    Unload Me
End Sub
Private Sub D3_Click()
    ActiveCell.Value = D3.ControlTipText
    Unload Me
End Sub
Private Sub D4_Click()
    ActiveCell.Value = D4.ControlTipText
    Unload Me
End Sub
Private Sub D5_Click()
    ActiveCell.Value = D5.ControlTipText
    Unload Me
End Sub
Private Sub D6_Click()
    ActiveCell.Value = D6.ControlTipText
    Unload Me
End Sub
Private Sub D7_Click()
    ActiveCell.Value = D7.ControlTipText
    Unload Me
End Sub
Private Sub D8_Click()
    ActiveCell.Value = D8.ControlTipText
    Unload Me
End Sub
Private Sub D9_Click()
    ActiveCell.Value = D9.ControlTipText
    Unload Me
End Sub
Private Sub D10_Click()
    ActiveCell.Value = D10.ControlTipText
    Unload Me
End Sub
Private Sub D11_Click()
    ActiveCell.Value = D11.ControlTipText
    Unload Me
End Sub
Private Sub D12_Click()
    ActiveCell.Value = D12.ControlTipText
    Unload Me
End Sub
Private Sub D13_Click()
    ActiveCell.Value = D13.ControlTipText
    Unload Me
End Sub
Private Sub D14_Click()
    ActiveCell.Value = D14.ControlTipText
    Unload Me
End Sub
Private Sub D15_Click()
    ActiveCell.Value = D15.ControlTipText
    Unload Me
End Sub
Private Sub D16_Click()
    ActiveCell.Value = D16.ControlTipText
    Unload Me
End Sub
Private Sub D17_Click()
    ActiveCell.Value = D17.ControlTipText
    Unload Me
End Sub
Private Sub D18_Click()
    ActiveCell.Value = D18.ControlTipText
    Unload Me
End Sub
Private Sub D19_Click()
    ActiveCell.Value = D19.ControlTipText
    Unload Me
End Sub
Private Sub D20_Click()
    ActiveCell.Value = D20.ControlTipText
    Unload Me
End Sub
Private Sub D21_Click()
    ActiveCell.Value = D21.ControlTipText
    Unload Me
End Sub
Private Sub D22_Click()
    ActiveCell.Value = D22.ControlTipText
    Unload Me
End Sub
Private Sub D23_Click()
    ActiveCell.Value = D23.ControlTipText
    Unload Me
End Sub
Private Sub D24_Click()
    ActiveCell.Value = D24.ControlTipText
    Unload Me
End Sub
Private Sub D25_Click()
    ActiveCell.Value = D25.ControlTipText
    Unload Me
End Sub
Private Sub D26_Click()
    ActiveCell.Value = D26.ControlTipText
    Unload Me
End Sub
Private Sub D27_Click()
    ActiveCell.Value = D27.ControlTipText
    Unload Me
End Sub
Private Sub D28_Click()
    ActiveCell.Value = D28.ControlTipText
    Unload Me
End Sub
Private Sub D29_Click()
    ActiveCell.Value = D29.ControlTipText
    Unload Me
End Sub
Private Sub D30_Click()
    ActiveCell.Value = D30.ControlTipText
    Unload Me
End Sub
Private Sub D31_Click()
    ActiveCell.Value = D31.ControlTipText
    Unload Me
End Sub
Private Sub D32_Click()
    ActiveCell.Value = D32.ControlTipText
    Unload Me
End Sub
Private Sub D33_Click()
    ActiveCell.Value = D33.ControlTipText
    Unload Me
End Sub
Private Sub D34_Click()
    ActiveCell.Value = D34.ControlTipText
    Unload Me
End Sub
Private Sub D35_Click()
    ActiveCell.Value = D35.ControlTipText
    Unload Me
End Sub
Private Sub D36_Click()
    ActiveCell.Value = D36.ControlTipText
    Unload Me
End Sub
Private Sub D37_Click()
    ActiveCell.Value = D37.ControlTipText
    Unload Me
End Sub
Private Sub D38_Click()
    ActiveCell.Value = D38.ControlTipText
    Unload Me
End Sub
Private Sub D39_Click()
    ActiveCell.Value = D39.ControlTipText
    Unload Me
End Sub
Private Sub D40_Click()
    ActiveCell.Value = D40.ControlTipText
    Unload Me
End Sub
Private Sub D41_Click()
    ActiveCell.Value = D41.ControlTipText
    Unload Me
End Sub
Private Sub D42_Click()
    ActiveCell.Value = D42.ControlTipText
    Unload Me
End Sub
Please note that this step doesn't have anything specific to ADF Desktop Integration it is purely excel macro so not explaining much on the code part

- Finally, add below code to the Worksheet _BeforeDoubleClick event of the sheet having employee form to invoke Calendar on double-clicking on Hiredate

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        ' $D$7 is the cell containing Hiredate
        If ActiveCell.Address = "$D$7" Then
        End If
End Sub
- Save the changes and Run the design time of workbook

- Navigate to first record, double-click on Hiredate field and notice date-picker dialog and date selected will be shown back to Hiredate field


Sample design-time workbook developed in can be downloaded Here (Right-Click on the link and choose "Save Link As..." option to download the workbook)

Monday Nov 28, 2011

Creating Custom validation rule and register it

What is Validation Rule?

A validation rule is a piece of code that performs some check ensuring that data meets given constraints.

In an enterprise application development environment, often it might require developers to have validation be performed based on some logic at several places across projects. Instead of redundant validation creation, a custom validation rule provides a library with a validation rules that can be registered and used across applications.
A custom Validation is encapsulated in a reusable component so that you do not have to write it every time when you need to do input validation.

Here is how we can easily implement a custom validation that checks for name of an employee to be "KING"

For creating a custom Validation ,

1.         Create Generic Application Workspace "CustomValidator" with the project "Model"

2.         Create an BC4J based on emp table.

3.         Create a custom validation rule.


In EmpNamerule class, update the validateValue(..) method as follows: 

public boolean validateValue(Object value) { 
        EntityImpl emp = (EntityImpl)value; 
            return false; 
        return true; 

Create ADF Library: Next step would be to create ADF library.

Create ADF library with name lets say testADFLibrary1.jar

Register ADF Library
Next step is to register the ADF library , so that its available across the applications.

Invoke the menu "Tools -> Preferences"
Select the option "Business Components -> Registered Rules" from left pane
Click on button "Pick Library". The dialog "Select Library" comes up with  the user library added
Add new library' that points to the above jar
Check the checkbox "Register" and set the name for the rule


Sample Usage

Here is how we can easily implement a validation rule that restrict the name of the employee not to be "KING".
Create new Application with BC4J based on EMP table.
Create new validation under Business rule tab for Ename & select the above custom validation rule.
Run the AppModule tester.


Monday Nov 14, 2011

Combo LOV - How to display Description for Selected Value instead of ID


The common problem that any one working with Combo LOV in a faces page faces is that the list of values component shows Id for selected value inspite of the dropdown showing Description for Options.
Unfortunately, this is a known issue with ADF faces for which there is already an enhancement request which we would expect to see in future releases.


In this article, I discuss a way to overcome this and display Description rather than Id.
In simple words, The workaround is to define LOV on a transient attribute instead of a View Attribute(Probably a Foreign Key attribute) and set the selected value back to View attribute on change of the LOV and make use of this transient attribute instead of View attribute wherever Combo LOV is needed.


I take a simple usecase with well known Emp and Dept tables to display an Emp form with Deptno Combo box List of Values with selected value of LOV being the Department name instead of Deptno

Here are the detailed steps for the same:

- Add a transient attribute to EmpView with the name "Dname"

- Define Combo LOV matching Dname and Deptno both (Deptno matching helps in setting value back to Deptno view attribute on change of Dname LOV value)
- Keep a note of List Data Source name(i.e, DeptViewAccessor in screenshot)

- Finally, Set the Department name of respective Deptno as default value to transient attribute(To show current department as selected) using below groovy expression:

Now, when you run the page you should be able to see the combo lov showing Department name for the selected value and change in LOV automatically updates Deptno attribute.

Incase, anyone don't like to have groovy, they can overwrite getDname method in RowImpl and add this code there.

Tuesday Nov 01, 2011

Create Master Detail Detail records in EJB using Train

This article describes the creation of master detail detail records using EJB Datacontrol.This is done using ADF Train component.

Use Case Description

Lets us consider that we have countries->locations->departments table with master->detail->detail relationship.We will create countries in first train step ,locations in next train step & departments in the last train step.

Implementation steps

Let us suppose that we have created Java EE Web Application with Entities from countries ,locations & departments table .Also add 'ADF Faces Components 11' under ViewController->project properties-> JSP Tag Libraries.

Then we will create Stateless Session Bean and and generate data control for the Stateless Session Bean.

Now create ADF Task Flow by enabling Train option in the Task flow Wizard


Taskflow shows 3 views that correspond to countries,locations & departments.It also has create method activity defined that creates new record for countries & this is achieved by dropping countriesFindAll->Operations->Create method from EJB Datacontrol:


Create country.jsff,location.jsff & department.jsff pages. Drop train component on all these page fragments with value "#{controllerContext.currentViewPort.taskFlowContext.trainModel}"

Open country.jsff & from datacontrol drop countriesFindAll as ADF Form without including navigation controls.Also drop persistCountries(Countries) method as ADF Button 'persistCountries' to persist the data

Once we create countries & persist the data,we would like to navigate to next train step to create locations.This navigation can be achieved by adding action property to 'persistCountries' commandButton :

<af:commandButton actionListener="#{bindings.persistCountries.execute}" text="persistCountries" 
disabled="#{!bindings.persistCountries.enabled}" id="cb1"/>

Open location.jsff & drop countriesFindAll->locationsList as ADF Master Form Detail Table on the above page Fragment.

Drop countriesFindAll->locationsList->Operations->Create as ADF button .
And drop mergeLocations(Locations)method from EJB datacontrol as ADF Button 'mergeLocations' to merge the data

Again this mergeLocations button should have action="#{controllerContext.currentViewPort.taskFlowContext.trainModel.getNext}" to navigate to next train step

Open department.jsff & drop countriesFindAll->locationsList->departmentsList as ADF Master Form ,Detail Table on the above page Fragment .
Drop countriesFindAll->locationsList->departmentsList ->Operations->Create as ADF button .
And drop mergeDepartments(Departments)method from EJB datacontrol as ADF Button'mergeDepartments' to merge the data

Now we will create a train.jspx page & drop the above created ADF Task Flow as region on this page

Run this page,it shows train with 3 steps & these train steps allow us to create master->Detail->Detail records


Friday Sep 16, 2011

Contextual events with EJB DC

This article describes contextual events with EJB Datacontrol

Use Case Description

Let us consider a scenario wherein the user wants to display deptno list & commandButton in region1 & region2 displays the Emp records corresponding to selected deptno in region1.We will try to achieve this using contextual events.

When the user selects deptno & clicks on the commandButton ,a contextual event with payLoad parameter is broadcasted by taskflow1.This event is then consumed by the  taskflow2 and the handleEvent() handler passes payLoad parameter(i.e deptno) to sessionBean.SessionBean has findByDeptno() method that returns the Emp records & taskflow2 displays the respective Emp records .

Implementation steps

Let us suppose that we have created Java EE Web Application with Entities from Emp & Dept table
.Also add 'ADF Faces Components 11' under ViewController->project properties-> JSP Tag Libraries.

Edit & create a custom named query that returns Emp Object for the respective deptno :

 @NamedQuery(name = "findEmpByDeptno", query = "select o from Emp o
 where o.dept.deptno = :deptNo")
Let us create Stateful Session Bean and expose the Named Query through the Session Facade.Create a contextual handle event that passes deptno as payload parameter:   

 public void handleEvent(Object payload) 
Modify findEmpByDeptno namedQuery as below:

 public List<Emp> findEmpByDeptno() 
 return em.createNamedQuery("findEmpByDeptno").setParameter
 ("deptNo",  this.getDepartNo()).getResultList();

Generate Data control for the above Session Bean & expose the above method through the Session Facade.

Create a ADF taskflow1 with page Fragments & drop  view activity(DeptView) on that.In the same taskflow drop Execute operation((findByDeptno()->Emp->Operations->Execute).Define control flow case 'execute' from DeptView to Execute operation in taskflow.

Drop deptno as selectOneChoice & commandButton 'showEmpRecords' on the DeptView page Fragment page.Set showEmpRecords action property  as 'execute'

In its page definition create deptno attribute value that points to deptFindAllIterator &  handler event method action binding to handle the event:

Select commandButton 'showEmpRecords' & create new contextual event on that:


Now we will create new Event subscriber in its contextual events binding editor tab:


Create taskflow2 that displays Emp table(findByDeptno()->Emp) & create main.jspx page,drop both the taskflows as region

Run the page & now if user selects deptno,table below shows Emp records corresponding to the selected deptno.


Tips and Tricks from Oracle's JDeveloper & ADF QA


« April 2014