Application Field shows error "invalid application" Why?

In the Change request, the application field allowed me to select the application however it did not show the value when you save it. Instead it showed "invalid application" inside the field.

The reason for this is that initially I set up the application field using the validation "KNTA-APPLICATIONS_ENABLED" which reflected well in the dashboard but only showed the hidden value (parameter1) rather than the full visible value
eg: CAS instead of Casino.

2 changes were made to rectify this problem:
1) modify the reference "c.application APPLICATION" to "c.application_meaning APPLICATION"
2) modify the reference "c" ie: instead of knta_requests, change it to knta_requests_v (the view which holds loads of values)

SELECT a.request_id REQUEST_ID, f.status_name WORKFLOW_STATUS, c.description PROJECT_TITLE, b.request_type_name REQUEST_TYPE, c.priority_code PRIORITY, a.request_id REQUEST_ID_HYPERLINK, d.parameter1 RAG, g.full_name CREATED_BY, a.creation_date CREATION_DATE, d.visible_parameter41 IDEA, d.visible_parameter20 BUSINESS_SPONSOR, c.application_meaning APPLICATION, a.visible_parameter3 PREFERRED_ETA, a.visible_parameter4 SPECIFICATION_WRITER, a.visible_parameter42 BRIEF_JUSTIFICATION, a.visible_parameter5 PROJECT_MANAGER, a.visible_parameter10 CONTRIBUTORS, a.visible_parameter6 ETA_FOR_REQUIREMENTS, a.visible_parameter11 APPROVERS, a.visible_parameter7 ETA_FOR_JUSTIFICATION, a.visible_parameter26 CHANGE_TYPE, a.visible_parameter12 ETA_FOR_PID, a.visible_parameter13 ETA_FOR_PROJECT_DELIVERY, a.visible_parameter20 ETA_ON_TEST, a.visible_parameter19 ETA_FOR_PROJ_INITIATION, d.visible_parameter2 PERCENTAGE_COMPLETE, d.parameter1 COLOUR_SOURCE
FROM kcrt_request_details a, kcrt_request_types b, kcrt_requests_v c, kcrt_req_header_details d, kcrt_statuses f, knta_users g
WHERE a.batch_number = 1
AND a.request_type_id = 30604
AND a.request_id = c.request_id
AND a.request_id = d.request_id
AND c.request_id = d.request_id
AND a.request_type_id = b.request_type_id
AND a.request_type_id = c.request_type_id
AND c.status_id = f.status_id
AND c.created_by = g.user_id
AND c.request_id in (
SELECT c.request_id
FROM kcrt_requests c
WHERE c.status_code NOT LIKE 'CLOSED%'
AND c.status_code NOT LIKE 'CANCEL%')

This resolved the issue!!

How to check audit data?

Most fields can be audited, however they are not all visible via the front end application.

1) Click into the field eg: Preferred ETA
2) Under "Attributes" tab, choose the radion button marked "Transaction History"
3) Save



The effect of this is that any changes to that field will be stored in the database table:
KCRT_TRANSACTIONS

It will show:
old_visible_column_value, new_visible_column_value

eg: Preferred ETA changes date from 20th - 30th June

Change Request Workflow

How to create a % complete progress bar against a req in a portal?

Add 2 new fields:

• percentage complete
• colour source

Need to add in another "percentage complete" field rather than OOTB field because need to add default value of zero which the OOTB field does not provide.

Colour Source must have a validation consisting of "RED, YELLOW, GREEN" values.

Modify the custom portlet to include reference to:
SELECT a.request_id REQUEST_ID, f.status_name WORKFLOW_STATUS, c.description PROJECT_TITLE, b.request_type_name REQUEST_TYPE, c.priority_code PRIORITY, a.request_id REQUEST_ID_HYPERLINK, d.parameter1 RAG, g.full_name CREATED_BY, a.creation_date CREATION_DATE, d.visible_parameter41 IDEA, d.visible_parameter20 BUSINESS_SPONSOR, c.application APPLICATION, a.visible_parameter3 PREFERRED_ETA, a.visible_parameter4 SPECIFICATION_WRITER, a.visible_parameter42 BRIEF_JUSTIFICATION, a.visible_parameter5 PROJECT_MANAGER, a.visible_parameter10 CONTRIBUTORS, a.visible_parameter6 ETA_FOR_REQUIREMENTS, a.visible_parameter11 APPROVERS, a.visible_parameter7 ETA_FOR_JUSTIFICATION, a.visible_parameter26 CHANGE_TYPE, a.visible_parameter12 ETA_FOR_PID, a.visible_parameter13 ETA_FOR_PROJECT_DELIVERY, a.visible_parameter20 ETA_ON_TEST, a.visible_parameter19 ETA_FOR_PROJ_INITIATION, d.visible_parameter2 PERCENTAGE_COMPLETE, d.parameter1 COLOUR_SOURCE
FROM kcrt_request_details a, kcrt_request_types b, kcrt_requests c, kcrt_req_header_details d, kcrt_statuses f, knta_users g
WHERE a.batch_number = 1
AND a.request_type_id = 30604
AND a.request_id = c.request_id
AND a.request_id = d.request_id
AND c.request_id = d.request_id
AND a.request_type_id = b.request_type_id
AND a.request_type_id = c.request_type_id
AND c.status_id = f.status_id
AND c.created_by = g.user_id
AND c.request_id in (
SELECT c.request_id
FROM kcrt_requests c
WHERE c.status_code NOT LIKE 'CLOSED%'
AND c.status_code NOT LIKE 'CANCEL%')

Then, modify the custom portlet so that it looks like:
Column Title: % complete
Column Type: Progress Bar
Percentage Source: Percentage Complete <-- new field added into data source
Color Source: Colour Source < -- new field added into data source

Save everything!!
The progress bar works in conjunction with the RAG status values.

An end user gives a request a RAG status of "amber", they then fill in 50% complete in the percentage complete field.
This will translate into a coloured progress bar in the portlet.

What is happening if portlet blows up??

If you get a massive error message eg: null pointer exception....blah blah

this means that the portlet definition field is different in some way to the data source definition.

Eg:

Filter Field Application in the data source referred to token APPLICATION, but then realised it should have been APPLICATION_CODE. Changed this but did not go to the portlet definition and confirm the change there, therefore dashboard blew up

Routing Rules

Field = change type which has 3 values (new project, enhancement and bug)

We want to enforce any one of 5 values:

• Proj Plan
• Req Spec
• Biz Justification
• Int PID
• Sup PID

So 3 separate rules are set up as follows:

change type field = new project

Sql query is:

select 'Y', 'Y',

'Y', 'Y',

'Y', 'Y',

'Y', 'Y'

'Y', 'Y'

from dual

This populates all of these values into the specific results which are:
Proj Plan
Req Spec
Biz Justification
Int PID
Sup PID

For a different rule, the values will be set to 'N', if not required.

To summarise:

If an end user selects the "change type" field = "new project", then the resulting document fields will all automatically be set to "Y". This result will be used in the workflow at various check steps which route based on value of the radio buttons.

Using Dummy and Token execution steps

Token execution steps are used to route end user selections eg:
If a user selects a radio button = yes for "detailed business justification required", that field has a token called "JUST_REQD".

It is possible to create an execution step (which is based on a validation of "yes" and "no") that will look at the value of that token and will route accordingly. Coming off the execution step will be a "yes" or "no" value which routes to an "or" which in turn can route to an "and" step.

So to summarise, if '[REQD.P.JUST_REQD] = yes, then route to the step "Attach Detailed Justification Document, otherwise "no" will route to the "OR" which bypasses the need to attach this document.

Dummy execution steps can be used for many reasons:
1) If the workflow has a split into 3 steps, they will all need to be completed and will be held by the "AND" step until then. When steps are split they need to have the same request status across all steps, however I might want the behaviour across these 3 steps to differ ie: might want some fields to be mandatory. So the solution is to use a dummy step that will set the status to the individual workflow step status, enforce the mandatory fields etc and will then be routed to an "OR" step.

2) I used it to trigger a notification to "assigned to " user for the "not submitted" step. I created a dummy step with all values acceptable and setup the notifications on the dummy step. I subsequently discovered that there is a tickbox that will allow notifications to be sent on submission.

Dummy execution step called "Check DATA" is of type "sql statement" and processes immediately. The sql statement is:
select 'Y'
from dual;

This will always output "Y".
This allows this result to be used for routing eg:
"Requirements Specification Entered?"
Status on this step is "pending specification completion". Against this status, the "mandatory" radio button is set, which will ensure that it is not possible to progress until this field (Requirements Document) has a value. Once it does have a value, the only routing will be moving from this step to an "OR" step. It uses the output of the sql query and routes on the value "Yes".

[If value of output matches value in the validation (sql statement), then route according to the execution step routing]

How to create notification against custom fields?

• create a field called "email cc"
• associate a validation. Use existing validation that refers to email address, but modify it so that email appears as the second field (which is the visible parameter).

P - always is #1 ie: invisible parameter eg: user_id
VP - always is #2 ie: visible parameter eg: email

The validation used and modified is knta- user id - enabled:
select user_id, full_name, username, department_meaning, email_address
from KNTA_USERS_V users
where ((UPPER(first_name) like UPPER('?' || '%')
AND (first_name like upper(substr('?',1,2)) || '%'
OR first_name like lower(substr('?',1,1)) || upper(substr('?',2,1)) || '%'
OR first_name like upper(substr('?',1,1)) || lower(substr('?',2,1)) || '%'
OR first_name like lower(substr('?',1,2)) || '%'))
OR (UPPER(last_name) like UPPER('?' || '%')
AND (last_name like upper(substr('?',1,2)) || '%'
OR last_name like lower(substr('?',1,1)) || upper(substr('?',2,1)) || '%'
OR last_name like upper(substr('?',1,1)) || lower(substr('?',2,1)) || '%'
OR last_name like lower(substr('?',1,2)) || '%'))
OR (UPPER(full_name) like UPPER('?' || '%')
AND (full_name like upper(substr('?',1,2)) || '%'
OR full_name like lower(substr('?',1,1)) || upper(substr('?',2,1)) || '%'
OR full_name like upper(substr('?',1,1)) || lower(substr('?',2,1)) || '%'
OR full_name like lower(substr('?',1,2)) || '%'))
OR (UPPER(username) like UPPER('?' || '%')
AND (username like upper(substr('?',1,2)) || '%'
OR username like lower(substr('?',1,1)) || upper(substr('?',2,1)) || '%'
OR username like upper(substr('?',1,1)) || lower(substr('?',2,1)) || '%'
OR username like lower(substr('?',1,2)) || '%')))
[FILTER_FIELD_SQL]
AND NVL(start_date, SYSDATE) <= SYSDATE
AND NVL(end_date, SYSDATE + 1) > SYSDATE
order by full_name

Change "select user_id, full_name, username, department_meaning, email_address" to
select user_id, email_address, full_name, username, department_meaning"

Modify the columns in the column headers:

• user id - invisible parameter #1
• full name - visible parameter #2
• username
• department
• email

to:

• user id- invisible parameter #1
• email - visible parameter #2
• full name
• username
• department

Go into the workflow step that you want to send a notification from and set up the following:

• Set receipient type to email address
• Add in the name of the user defined token [REQD.VP.EMAILCC]
• Add in the text of the email template.