Extending the base query zone

A common request is to produce query zones which are aligned with the base product zones, including concepts such as 'traffic-light' bars and hyperlinks, and whilst the online help assists with samples of hyperlink definition, it does not provide much detail around the ability to extend the normal character display to allow users to produce a more feature-rich solution (ie. source=SPECIFIED options on the zone definition).

With that in mind, I thought it appropriate to document an example of such a zone.

The zone should support the following:

  1. A list of Open Case types allocated to the current user by type and lifecycle status.
  2. A breakdown of the numbers of transactions of these type/status combinations categorised by those entries which are less than 50 Days old (green), 50-100 days old (yellow) and > 100 days (Red).
  3. The ability to hyperlink to the oldest Case of this type/status for each row.
  4. Hover-over text, listing the numbers of cases for each graph component.

With this in mind, I have configured a sample query zone as:

Zone

CM_CASE_DZ

Description

Case Dashboard Summary

Zone Type

F1-DE-SINGLE

Application Service

F1-DFLTS

Width

Full

Zone Help Text

<blank>

Height Of Report

50

Configuration Area Display Options

FilterArea=OPEN

DragDropArea=NEVER

Display Description Area

False

Allow Column Sorting

False

No SQL Execute

nosql=IGNORE

Initial Display Columns

C1 P1 C2 C3 C4

SQL Statement

SELECT B.DESCR || '-' || A.STATUS_LBL,

MIN(

(SELECT MIN(MY.CASE_ID)

FROM CI_CASE MY,

CI_CASE_LOG ML

WHERE MY.CASE_TYPE_CD = B.CASE_TYPE_CD

AND MY.CASE_STATUS_CD = A.CASE_STATUS_CD

AND MY.CASE_ID = ML.CASE_ID

AND ML.LOG_DTTM =

(SELECT MIN(MX.LOG_DTTM)

FROM CI_CASE_LOG MX

WHERE MX.CASE_ID = MY.CASE_ID

)

)) CASE_ID,

MIN(

(SELECT COUNT(*)

FROM CI_CASE C,

CI_CASE_LOG CL

WHERE C.CASE_TYPE_CD = B.CASE_TYPE_CD

AND C.CASE_STATUS_CD = A.CASE_STATUS_CD

AND C.CASE_ID = CL.CASE_ID

AND CL.LOG_DTTM =

(SELECT MIN(MCL.LOG_DTTM)

FROM CI_CASE_LOG MCL

WHERE MCL.CASE_ID = C.CASE_ID

)

AND CL.LOG_DTTM > SYSDATE - 50

)) COUNT_CASE_50,

MIN(

(SELECT COUNT(*)

FROM CI_CASE C,

CI_CASE_LOG CL

WHERE C.CASE_TYPE_CD = B.CASE_TYPE_CD

AND C.CASE_STATUS_CD = A.CASE_STATUS_CD

AND C.CASE_ID = CL.CASE_ID

AND CL.LOG_DTTM =

(SELECT MIN(MCL.LOG_DTTM)

FROM CI_CASE_LOG MCL

WHERE MCL.CASE_ID = C.CASE_ID

)

AND CL.LOG_DTTM BETWEEN (SYSDATE - 100)

AND (SYSDATE - 50)

)) COUNT_CASE_100,

MIN(

(SELECT COUNT(*)

FROM CI_CASE C,

CI_CASE_LOG CL

WHERE C.CASE_TYPE_CD = B.CASE_TYPE_CD

AND C.CASE_STATUS_CD = A.CASE_STATUS_CD

AND C.CASE_ID = CL.CASE_ID

AND CL.LOG_DTTM =

(SELECT MIN(MCL.LOG_DTTM)

FROM CI_CASE_LOG MCL

WHERE MCL.CASE_ID = C.CASE_ID

)

AND CL.LOG_DTTM < SYSDATE - 100

)) COUNT_CASE_101

FROM CI_CASE_STATUS_L A,

CI_CASE_TYPE_L B,

CI_CASE C

WHERE C.CASE_ID =

(SELECT MIN(CASE_ID)

FROM CI_CASE MC,

CI_CASE_STATUS CCS,

SC_USER SU

WHERE MC.CASE_STATUS_CD = CCS.CASE_STATUS_CD

AND MC.CASE_TYPE_CD = CCS.CASE_TYPE_CD

AND MC.CASE_TYPE_CD = B.CASE_TYPE_CD

AND B.LANGUAGE_CD = SU.LANGUAGE_CD

AND CCS.STATUS_COND_FLG <> 'FINL'

AND A.CASE_STATUS_CD = CCS.CASE_STATUS_CD

AND MC.USER_ID = SU.USER_ID

AND SU.USER_ID = :USERID

)

AND A.CASE_TYPE_CD = B.CASE_TYPE_CD

AND A.LANGUAGE_CD = B.LANGUAGE_CD

GROUP BY B.DESCR || '-' || A.STATUS_LBL

Column 1

source=SQLCOL

sqlcol=1

label='Case'

width=150

navopt='C1_CaseByCaseId'

context=[CASE_ID=CASE_ID]

Column 2

source=SQLCOL

sqlcol=3

label='<50 Days'

Column 3

source=SQLCOL

sqlcol=4

label='50-100 Days'

Column 4

source=SQLCOL

sqlcol=5

label='>100 Days'

Column 5

source=FORMULA

formula=C2/(C2+C3+C4)*100

Column 6

source=FORMULA

formula=C3/(C2+C3+C4)*100

Column 7

source=FORMULA

formula=C4/(C2+C3+C4)*100

Post Processing Column 1

source=SPECIFIED

width=100

spec=['

<table style="border:1px solid black;" height="10" width="100%" cellpadding="0" cellspacing="0" >

<tr height="10">

<td bgcolor="#FF0000" height="10" width="' C7 '%" title="' C4 'case/s assigned to you are > 100 days old">'

' </td>

<td bgcolor="#FFFF00" height="10" width="' C6 '%" title="' C3 'case/s assigned to you are 50-100 days old">'

' </td>

<td bgcolor="#008000" height="10" width="' C5 '%" title="' C2 'case/s assigned to you are > 50 days old">'

' </td>

</tr>

</table>

']

label='Assigned'

Portal

CI_DASHBOARD

Note that this zone should work for CC&B 2.1 and above, however no guarantees are provided in relation to the new framework (and the compatibility of the <table> structure with future browser versions.

Comments:

We have been working with CCB/MWM and CSS for over two years, this is the first example I have seen where html was placed in the zone for display. Is there an unabridged set of documents that explain everything we can do with the zone focusing on these more extravagant methods to display returned results? We have created a zone with a post processing to display all the columns in a more readable format for smaller screens based on your example; But in doing so, have lost all navigation options. I may be trying to find a way to have our cake and eat it too, but it is starting to look like a really good cake.

Posted by guest on September 27, 2014 at 04:33 AM EST #

Thanks for reading, and glad that I could be of some use. The lack of documentation around this feature was the basis for my article, however it is by no means a comprehensive example of what is possible. Your best bet is to remember that the query zone related HTML is being rendered within the same framework as the application and as a result all HTML has to conform to the same rules as any other UI Map. As a result I recommend that you check out the UI Map help and samples for methods which you can employ to ensure that your navigation options align with requirements.

Note that there was an error in my earlier definition of columns 5, 6 and 7, which I have now corrected.

Posted by Stu on September 27, 2014 at 02:47 PM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Stuart Ramage

I am a Consulting Technical Director for Oracle Corporation, and a member of the OU Black Belt Team, based in Hobart Tasmania.
I have worked in the Utility arena since 1999 on the Oracle UGBU product line, in a variety of roles including Conversion, Technical and Functional Architect.

Contact me on:

Search

Categories
Archives
« September 2015
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today