ADF DVT - Converting Row values as Column Headers

When you want to show row data as column headers you do not need to do any coding for creating the ADF table on the fly. Instead you can use PIVOT Table available in ADF DVT components. This has inbuilt support for showing the row values as column headers. Also this will help you do the grouping based on that value and display the values for the rows.

Today, I would take the example related to my project, so that I can show you pivot table and the queries that I have used.

In this example, I want to show the count of patient based on a cancellation code and on an weekly bases. So my row values should be converted into column headers for the Cancellation Reason. First should be group by Weekly No and then group by Cancellation Code. So final look of the Pivot table would be like the below screen shot.

Here Year-Month, Week Count, Cancellation Reason are all Group By columns based on which the No. Of Patient value is got. One more advantage of using Pivot Table is user will be allowed to move the row/column headers as per their wish. If you see the following images all of them were generated at run-time by dragging the column/row headers..

Now we will go into modelling this.

We will have to have a SQL based VO with the following query which will give us the ability for using it in pivot table.

SELECT COUNT(*) AS NO_OF_PATIENTS,
  APPOINTMENT_WEEK,
  APPOINTMENT_YR_MON,
  APPOINTMENT_MONTH_YEAR,
  CANCELLATION,
  CANCEL_REASON,
FROM appointment
GROUP BY APPOINTMENT_WEEK,
  APPOINTMENT_YR_MON,
  CANCELLATION,
  APPOINTMENT_MONTH_YEAR,
  CANCEL_REASON

Count will get the No. Of Patients and all the other values are either used as a Row Header or Column Header. Once this VO is done. You can use it to create Pivot Table.

1) Add the VO to an AM of that page.
2) Drag and drop the VO into your jsff page from the DataControls and select ADF Pivot Table from the menu as shown in the below.

3) Next we need to drag and drop the attributes into Column headers and Row Headers as per our basic requirement. In the PivotTable design view we can drag and drop the columns as shown below

Here if you see the columns that are dragged and dropped into Row/Column Header(highlighted in black) with the grey background will be shown as a header and NoOfPatient(highlighted in red) column will be the data shown in the Pivot Table.

When you drop the VO in the page you would see  PivotTableDataControl getting created in the page bindings. So going forward if you want to change any of the layout or add/remove the column then you can edit this data binding. The same pivot table design view will be shown.

In the page you would only see a dvt:pivotTable tag with the dataControl binding and default values. You are now ready to test it.

In my next post, I will cover how to add drill down behavior for the pivot table to show the details for each row value.

ADF - Programmatically creating Master-Detail VO rows using a View Link

Lot of things in ADF can be done declaratively and in some cases to minimize the coding we can do half declaratively and then use that for our coding. In this case we will see how to use the view link to create rows in detail VO programmatically. When it comes to UI we do not need to do anything extra apart from creating a View Link. If you are not familiar with View Links then please go through this link for knowing more about View Links

http://www.adftips.com/2010/10/adf-model-creating-view-link.html

Now if these VOs are used in the UI with Create and Delete buttons dragged from there operations then you would not need any additional coding. But if you want to do a setup job by creating master and detail VO rows when the user comes in intially. Then you can follow the steps below to create them programmatically.

You need to have a Master and Detail VO create based on a EO which has association between them. Then you create the View Link based on the association of those EOs. 

Please check the below screen shots for an example

EOs and Association (CommnPreferenceEO - Master VO and CommnPrefDetailEO - Detail VO)

Association's Composition behavior settings

If you want it to be delete cascaded then please select Implement Cascade Delete also.

Next we will see the how VOs and View Link looks.

Apart from the EO, Association, VO and View Links you need to create a View Criteria for the master vo to query based on a entity. In our case we will have a findByPatientId view criteria. So for the logged in Patient we will execute the Master VO using this View Criteria and then ADF automatically gets the Detail VO records for that master.

If you see the View Link is based on the association. 

Now we need to add these VO's to the AM through the View Link

Once you do that it will look like the following..

Now we are ready to code for creating the master and detail rows programmatically.

ViewObjectImpl commnPrefVO = getComunicationPreferenceVO();

commnPrefVO.setNamedWhereClauseParam("pPatientId", patientId);

commnPrefVO.executeQuery();

RowSetIterator commnPrefItr = commnPrefVO.createRowSetIterator(null);

// If the Master record already exist

if(commnPrefItr.hasNext()){

    Row row = commnPrefItr.next();

    // Access the detail VO using the a Attribute that was created in the Master VO through the View      Link

    // Remeber, it would return a RowIterator for the Detail VO

    RowIterator commnPrefDetailItr = (RowIterator) row.getAttribute("CommunicationPrefDetailVO");

    

    // We can direcly create Detail VO rows using the RowIterator itself.

    Row comnPrefDetailRow = commnPrefDetailItr.createRow();

    commnPrefDetailItr.insertRow(comnPrefDetailRow);

    

    // The foreign key attribute for detail vo will be automatically set.

    // If you want to set any other attributes please do it here.

    comnPrefDetailRow.setAttribute("<AnyAttribute>", <AttributeValue>);

    }

} 

// If the Master record itself doesn't exist

else {

    Row row = commnPrefVO.createRow();

    commnPrefVO.insertRow(row);

    row.setAttribute("PatientId", patientId);

    // Following piece of code is same as above if condition.

    RowIterator commnPrefDetailItr = (RowIterator) row.getAttribute("CommunicationPrefDetailVO");

    

    Row comnPrefDetailRow = commnPrefDetailItr.createRow();

    commnPrefDetailItr.insertRow(comnPrefDetailRow);

        

    comnPrefDetailRow.setAttribute("<AnyAttribute>", <AttributeValue>);

} 

We are done with the setup. Now when this method is called before the page is loaded the VOs will be setup with data for it to be displayed in the page.

ADF- Form Layout Basics

Initally, when I started working with ADF, I found that form layout should be used for label alignment of input/output fields, when you want to display them in a 'column X row' format. This is not straight forward unless you change the properties that are available in the form component. Following properties are required to be modified to achieve a proper layout of your desire.

1. When you drag and drop a form layout following properties have the default values.
        Rows - 2147483647
        LabelAlignment - default(TOP)
2. If you want to show the components inside the form layout in  X No. of rows and  X No. of columns, then you need to override the above value by setting the No. of rows you want and No. of columns you want. e.g., If you have 10 input text then if you want them in a two column layout you need to set 'Row = 5' and 'Column = 2'.
3. LabelAlignment by default will be on top of the text field in case of Input Text. To have the label in line with the input/output text we need to change this property to 'Start'
4. Next, if you have a lengthy label and you want to wrap the label at a particular length then you need to set a value for LabelWidth property of form layout.

With the above options that are available in the form layout you would be able to change the layout as per your requriement.

ADF - How to do partial rollback for changes done inside a PopUp on Cancel.

Time to move back to ADF. In this post I put down how to do a partial rollback of changes done in a popup on popup cancel and leave the other changes in the page which are not committed. When you have a save button at the page level, and in the page if you have an editable popup and now if the user opens the popup and changes something in it and clicks on Cancel in the popup we need to just revert the changes done in the popup and all the other changes done in the page should be left as it is.

How to do it.. Here we go.

1) When the popup is launched it should always take the value from the base attribute. So we need to set the ContentDelivery of the popup to lazyUncached. 

2) When the popup is launched we need to create a savePoint so that if its cancelled we can rollback to this point. Following steps help you to do this..

• Associate an actionListner for the action component used for launching the popup
• Since we invoke popup and also want this actionListener to be called at the same time, we need to set the showPopupBehaviour's TriggerType as Click. If you set the TriggerType as Action then ActionListener will not be called.
• Have the following helper methods to create, delete and reset the SavePoint
  
  
      public void createSavePoint() {
          DCBindingContainer bindingContainer = ADFUtil.getDCBindingContainer();
          DCDataControl dcDataControl = bindingContainer.getDataControl();
          String spID = (String)dcDataControl.createSavepoint();
        AdfFacesContext.getCurrentInstance().getPageFlowScope().put("SavePointId",
                                                                      spID);
      }
  
  
  
  public void restoreSavePoint() {
      String spID =
          (String)AdfFacesContext.getCurrentInstance().getPageFlowScope().get("SavePointId");
  
      DCBindingContainer bindingContainer = ADFUtil.getDCBindingContainer();
      DCDataControl dcDataControl = bindingContainer.getDataControl();
      dcDataControl.restoreSavepoint(spID);
  }
  
  
  public void removeSavePoint() {
      AdfFacesContext.getCurrentInstance().getPageFlowScope().put("SavePointId", null);
  }
  
  

• In the actionListener method you need to call the createSavePoint method. 
• In the popupCancel action call restoreSavePoint method.
• On click of Save button you need to remove the savePoint by clearing out the SavePointId in the pageFlowScope(I could not see a better way of deleting the savePoint). So you need to call removeSavePoint method on click of Save or in the DialogListeners OK OutCome.

Thats all we need to do for implementing RollBack for changes done in the popup. All the Change done in the base page will persist.

• 
  

Oracle SOA - Sample Code for SOA Components..

I was searching for good collection of sample code to work with human work flow components. Finally found the following link

http://java.net/projects/oraclesoasuite11g/pages/Home

this has sample code for almost all the components used in SOA.

Oracle SOA - Email notification using Gmail Server and Windows OS

If you want to send email using gmail and on Windows platform. Follow this blog..

You need to follow the steps mentioned in the blow URL to set up the soa-server and creating composite.

http://technology.amis.nl/blog/6019/configure-soa-suite-11g-for-sending-email-notifications-with-google-mail

After completing the steps above we need to setup the keystore with the cert for connecting to Gmail server. Follow the steps below to do that in Windows OS. For linux you can follow the steps mentioned in Comment# 5 of the above blog

(NOTE - Before you start developing SOA Composite you can setup the server and then setup the certificates and test the email notification directly from EM without any SOA Composite. So, it is up to you either do all the setup first and make sure it works and then start with the SOA development. Steps to setup certificates and testing notification through em follows...)

1) You need to download OpenSSL to get the SSL cert key from gmail. You can download and install OpenSSL into your OS from the URL http://gnuwin32.sourceforge.net/packages/openssl.htm and download complete setup.
2) After the installation is complete go to the bin directory - C:\GnuWin32\bin and run the file openssl as admin.
3) In the command prompt that opens type the following command
s_client -connect smtp.gmail.com:465
s_client -connect imap.gmail.com:993

1st command is to get the certificate for smtp and the 2nd is for getting for imap if you want to receive mail from your composite. If you just want to send a mail then you can get cert for smtp alone.

And copy the content inbetween BEGIN CERTIFICATE and AFTER CERTIFICATE and save in two separate files called smtp.cert and imap.cert from 1st and 2nd command respectively.

You can have this file in the same folder C:\GnuWin32. Once we have the cert files we need to create a new trust store and import these certificates.
4) Open an command prompt and execute the following command after you navigate to the folder in which you have created the cert files
keytool -import -alias imap.gmail.com -keystore trusted-certificates.jks -file imap.cert
keytool -import -alias smtp.gmail.com -keystore trusted-certificates.jks -file smtp.cert

the above command will ask for a password which will be required later. So enter a new password and remember it so that we can use in the next step.

The above command will create a file called trusted-certificate.jks in the same folder which we will use in the soa server now.
5) Navigate to your user-directory path similar to the following D:\Oracle\Middleware\user_projects\domains\soa_domain\bin and edit setDomainEnv.sh, replace the existing javax.net.ssl.trustStore property setting with “-Djavax.net.ssl.trustStore=C:/GnuWin32/trusted-certificates.jks -Djavax.net.ssl.trustStorePassword=<password you used>”. Also change the setDomainEnv.cmd file with the same content with back slash - -Djavax.net.ssl.trustStore=C:\GnuWin32\trusted-certificates.jks -Djavax.net.ssl.trustStorePassword=weblogic1.
6) Restart both SOA and ADMIN server.

After completing the above steps if you want to check the mail server configuration diretly from EM instead of developing the SOA Composite. You can do that by following the below steps.
1) Navigate to your em console - http://localhost:7001/em
2) Expand SOA and click on soa-infra.
3) In the right pane you need to expand Service Engines header (Use scroll bar to navigate to the bottom of the page to see this menu).
4) Click on Human Work Flow Engine
5) Now Click on Notification Management tab and then click on Send Test Notification button.
6) Give a To address and select Email as the Channel from the dropdown and give other values and send the message. You should check the SOA-SERVER log for any error messages