Building a Query Portal – Part 2 (Query Zone)

Note: I would like to thank Stuart Ramage for providing the Query Portal shown below

Welcome to the second part in the series about Query Portal. In the Part 1 we created a custom portal definition, attached it to the appropriate security user groups and created a UI Map to hold the filters. In this part I will take the component already built and incorporate them into a configuration definition of a Query zone.

I should point out that I am using specific configuration elements of the query zone type I have chosen to use. The zone types shipped with the product are very powerful and have lots of functionality (most of which I cannot cover in a single example). The online help and zone tips as well as active zones provided with the products give you an idea of the various techniques and features you can use with each zone type.

To create a zone navigate to the Administration -> Z -> Zone+ menu option. For this example, I will prefix the name of the zone with CM, which will help me denote it as a customization. The record owner will already be assigned to "Customer Modification" but by also implementing a prefix I can maximize the possibility it will NOT conflict with a base zone.

For this example I will use the F1-DE-QUERY zone type as it is the type that is the most appropriate. It is a simple zone type that supports filters. I chose it after looking at the zones provided with the product and liked the look of it. I will assign the generic F1-DFLTS (Default Application Service) which means any person with the appropriate permission to that service will have access to that service. I will the width of the zone to Full as I want it to be display in the whole width of the portals it will be added to.

An example of the basic definition is shown below:

image

I will leave the zone help text blank for now (I suggest you do put some help text in your zones to assist your online users). You may notice a field called "Override Zone Help Text", this field is used when you want to override the help text in base zones (i.e. zones whose owner is not "Customer Modification") as base zones are locked from editing usually.

The next phase is to set the parameter values. At first this looks daunting but people familiar with property sheets that you see in other technologies, such as java or even the dreaded .NET, will be familiar with these.

Now, most of the zone parameters will be left blank with the following exceptions:

  • Height Of Report Area - This sets the maximum height of the zone in terms of percentage of the screen. By default it will use 50 percent of the screen for the output. I will set it to 100 percent for now. I can adjust that later, if I do not like the outcome.
  • Allow Column Sorting - This allows or disallows the end user to alter the sorting at run time by allowing the user to click the column headings to change the sorting order for the list at runtime. I will allow this so set the value to true.
  • Filter Area UI Map - I will specify the UI Map I created in Part 1 here. For this example the UI Map name is CM-TodoFltr.
  • User Filter 1 - I now need to map the filters on the UI Map to the zone so the zone handler knows what to process and where to get the values. For the F1 filter, the value is the td_type element from the schema of the UI Map and its data type is STRING. I do not have to specify the format as it inherits from the UI Map schema but I do this for debugging purposes (as I might want to manipulate the content later). So the entry is type=STRING xpath=td_type.
  • User Filter 2 - 6 - I repeat the same as User Filter 1 but obviously with the relevant fields of the UI Map Schema.
    User Filter 2 type=STRING xpath=role_id
    User Filter 3 type=STRING xpath=priority
    User Filter 4 type=STRING xpath=suppressEntries
    User Filter 5 type=DATE xpath=startDate
    User Filter 6 type=DATE xpath=endDate
  • Display Columns - List of columns to display in the order you want them to be displayed. The mnemonics in this case are Cx where x is the column number to display. In this case it is C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11.
  • SQL Statement 1 - This is the SQL statement to use to get the records from the database in the format I want to get them. Now the SQL I chose is quite complex and I also chose to calculate values such as descriptions from the database. I will explain the SQL at the bottom of this entry. The SQL is below:

    SELECT TVAL.TD_TYPE_CD,
      (SELECT DESCR
      FROM CI_TD_TYPE_L
      WHERE CI_TD_TYPE_L.LANGUAGE_CD = :LANGUAGE
      AND CI_TD_TYPE_L.TD_TYPE_CD    = TVAL.TD_TYPE_CD
      ) AS TODO_DESCR,
      TVAL.ROLE_ID,
      (SELECT DESCR
      FROM CI_ROLE_L
      WHERE CI_ROLE_L.LANGUAGE_CD = :LANGUAGE
      AND CI_ROLE_L.ROLE_ID       = TVAL.ROLE_ID
      ) AS ROLE_DESCR,
      (SELECT TD_PRIORITY_FLG
      FROM CI_TD_TYPE
      WHERE CI_TD_TYPE.TD_TYPE_CD = TVAL.TD_TYPE_CD
      ) AS PRIORITY_FLG,
      (SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT,
        DUAL
      WHERE TDENT.ENTRY_STATUS_FLG = 'O'
      AND TVAL.TD_TYPE_CD          = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID             = TDENT.ROLE_ID
      AND TO_DATE(TO_CHAR(TDENT.CRE_DTTM,'YYYYMMDD'),'YYYYMMDD') BETWEEN NVL(:F5,SYSDATE) AND NVL(:F6,SYSDATE)
      ) AS OPEN_TODAY,
      (SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT,
        DUAL
      WHERE TDENT.ENTRY_STATUS_FLG = 'C'
      AND TVAL.TD_TYPE_CD          = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID             = TDENT.ROLE_ID
      AND TO_DATE(TO_CHAR(TDENT.COMPLETE_DTTM,'YYYYMMDD'),'YYYYMMDD') BETWEEN NVL(:F5,SYSDATE) AND NVL(:F6,SYSDATE)
      ) AS CLSD_TODAY,
      (SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT
      WHERE TDENT.ENTRY_STATUS_FLG = 'O'
      AND TVAL.TD_TYPE_CD          = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID             = TDENT.ROLE_ID
      ) AS OPEN_TODO,
      (SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT
      WHERE TDENT.ENTRY_STATUS_FLG = 'W'
      AND TVAL.TD_TYPE_CD          = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID             = TDENT.ROLE_ID
      ) AS WRKG_TODO,
      (SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT
      WHERE TDENT.ENTRY_STATUS_FLG = 'C'
      AND TVAL.TD_TYPE_CD          = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID             = TDENT.ROLE_ID
      ) AS CLSD_TODO,
      (SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT
      WHERE TVAL.TD_TYPE_CD = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID      = TDENT.ROLE_ID
      ) AS TOTL_TODO
    FROM CI_TD_VAL_ROLE TVAL
    WHERE (SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT
      WHERE TVAL.TD_TYPE_CD = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID      = TDENT.ROLE_ID) > 0
    AND ROWNUM             <=300
    [(F1) AND TVAL.TD_TYPE_CD     = :F1]
    [(F2) AND TVAL.ROLE_ID        = :F2]
    [(F3) AND (SELECT TD_PRIORITY_FLG
      FROM CI_TD_TYPE
      WHERE CI_TD_TYPE.TD_TYPE_CD = TVAL.TD_TYPE_CD) = :F3]
    [(F4) AND ((((SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT
      WHERE TDENT.ENTRY_STATUS_FLG = 'O'
      AND TVAL.TD_TYPE_CD          = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID             = TDENT.ROLE_ID) > 0
    OR (SELECT COUNT(*)
      FROM CI_TD_ENTRY TDENT
      WHERE TDENT.ENTRY_STATUS_FLG = 'W'
      AND TVAL.TD_TYPE_CD          = TDENT.TD_TYPE_CD
      AND TVAL.ROLE_ID             = TDENT.ROLE_ID) > 0)
    AND :F4                        = 'Y')
    OR :F4                         ='N')]

  • Columns - This is a list of sources and formats for columns used in Display Column. In each case I am using the column in the SQL statement above and specifying the label to use. I have decided that I want the zone to be able to be multilingual so I am using Meta Data tags that exist and some that have to be specified. The tag will hold the format and labels to use. You also notice that you can set foreground and background colors.
    Column 1 for SQL 1 source=SQLCOL sqlcol=1 label=TODO_LBL
    Column 2 for SQL 1 source=SQLCOL sqlcol=2 label=TYPE_DESCR
    Column 3 for SQL 1 source=SQLCOL sqlcol=3 label=ROLE_ID
    Column 4 for SQL 1 source=SQLCOL sqlcol=4 label=ROLE_DESCR
    Column 5 for SQL 1 source=SQLCOL sqlcol=5 label=PRIORITY
    Column 6 for SQL 1 source=SQLCOL sqlcol=6 label=OPEN_LBL
    Column 7 for SQL 1 source=SQLCOL sqlcol=7 label=CM_ASSIGNTODO
    Column 8 for SQL 1 source=SQLCOL sqlcol=8 label=CM_CLOSETODO color='green'
    Column 9 for SQL 1 source=SQLCOL sqlcol=9 label=CM_TOTALTODO bgcolor='gray'
    Column 10 for SQL 1 source=SQLCOL sqlcol=10 label=CM_OPENPERIOD bgcolor='yellow'
    Column 11 for SQL 1 source=SQLCOL sqlcol=11 label=CM_CLOSEPERIOD bgcolor='yellow'

Here is the setup in the Zone Maintenance screen:

image

image

That completes the configuration. The last step is to add the zone to the portal created in Part 1. This can be done from the Zone Maintenance screen or from the Portal Maintenance screen. For example:

image

To test the zone you will have to flush the cache (using the JMX call or simply using the flushAll.jsp in the URL (instead of cis.jsp). This will force the loading of the menu's again. It is not required for zone changes as they are reloaded on save.

To execute the To Do Summary Portal, Navigate to to menu item to invoke the zone.

image

image

Now for an explanation about the SQL in the zone:

  • The SQL was built in Oracle SQL Developer over time to return the values that were required. Typically a technique of hardcoding the values to see if it returned the results were used to refine the SQL. Any valid SQL statement can be used on any available table (they do not even need to exist in the product schema). Of course ensure the SQL is efficient before considering it. Putting a bad performing SQL in a zone will not make it magically become good performing.
  • Once I was happy with the SQL I then parameterized the literals as either constants or filter variables. For example, the SQL retrieves descriptions and one of the keys of the description tables is Language. the :LANGUAGE variable is used here to substitute the users active language.
  • The SQL retrieves the descriptions for the To Do Type and Role. These have been included in the SQL to save processing later. It is also valid to use the bs/bo/ss mnemonic on the Column to return the values as they are retrieved. This is just a personal preference.
  • The SQL features dynamic where clauses. Each filter is denoted by Fx where x is the filter number. If the filter is used then the WHERE clause is included. For example the [(F1) AND TVAL.TD_TYPE_CD     = :F1] tells the zone handler to include the AND TVAL.TD_TYPE_CD = :F1 only the F1 (User filter 1) is non-blank. Note: We did not have to include the "" around the :F1 host variable as the zone hander does this already.

This whole process looks complex but here are a few tips in building a zone like this:

  • Start with the SQL statement you want to use as the basis of the zone. Once you are happy with that SQL the rest can fall into place.
  • Parameterize the SQL to include filters and constants (such as :LANGUAGE).
  • Build a UI Map with the filters using a HTML fragment. Lay them out as you want them to be displayed.
  • Avoid hardcoding and use Meta data Field definitions to store labels and formats. This will help with multi-lingual.
  • Specify the columns you want to display in the query zone in the Display Column parameter.
  • For each column you want to display, specify the format and source of the information.
  • Look at examples provided with the product. There are usually lots of different approaches used and taking a look at examples is the best way for you to choose the one you prefer.
  • Look at the online documentation, there are lots of examples and lots of advice in the content.
  • When you save your zone it is validated. This tells the zone handler that it is configured correctly. You can still have runtime errors for example.
  • Use the debug feature to trace your code. The trace log will tell you exactly what is happening.

Hope you enjoyed this short series. Additional examples will be published over the next few months illustrating some examples.

Remember this example, is just one simple example which does not use the full spectrum of options available but gives you an idea of what is involved in the production of zones.

Comments:

Great article, where did you get the idea for the portal from..

Posted by Stu on April 19, 2011 at 06:57 AM EST #

Of course, Stu, you gave me the idea for the portal... I thought it would be a great example

Posted by Anthony Shorten on April 20, 2011 at 02:26 AM EST #

Great demo, thank you for posting.

I do have a question - Is there a way to conditionally format values returned from a query within a zone?

I've created a query zone within a custom Portal to give me the status of the bills that were generated (and have not generated) during the billing cycles open window.

I would like to format each bill status as follows:

If bill status = 'C' color='Green'
If bill in a 'P' status color='Yellow'
If no bill was created color='Red'

Column Description - of course the below example colors all Bill Status' returned from my query as 'red' within the Portal.

source=SQLCOL sqlcol=BILL_STATUS
label='Bill Status' color='red'
navopt='CI0000000049' context=[ACCT_ID=C1]
order=D

Not sure if this is possible...

Deanna

Posted by Deanna on June 11, 2013 at 11:23 AM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Anthony Shorten
Hi, I am Anthony Shorten, I am the Principal Product Manager for the Oracle Utilities Application Framework. I have been working for over 20+ years in the IT Business and am the author of many a technical whitepaper, manual and training material. I am one of the product managers working on strategy and designs for the next generation of the technology used for the Utilities and Tax markets. This blog is provided to announce new features, document tips and techniques and also outline features of the Oracle Utilities Application Framework based products. These products include Oracle Utilities Customer Care and Billing, Oracle Utilities Meter Data Management, Oracle Utilities Mobile Workforce Management and Oracle Enterprise Taxation and Policy Management. I am the product manager for the Management Pack for these products.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
9
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today