Friday May 02, 2014

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.

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
« May 2014 »
SunMonTueWedThuFriSat
    
1
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
31
       
Today