Chủ Nhật, 28 tháng 8, 2016

DbExecute step in Work flow


//Example DB2 Stored Procedures for DbExecute step in Work flow 

DbExecute system function: Use the DbExecute system function to run a stored procedure in a specified database.
The datatypes must match the types specified in the stored procedure and can be of the following types:
Designer Param Type
Oracle Param Type
SQL Server Param Type
DB2 Param Type
String
varchar
varchar
varchar
Integer
number
int
int
Boolean
number
bit
number
Float
number
float
float
Time
date
datetime
timestamp

DB2 Stored Procedure:
A stored procedure is unusable until it is defined. Use the CREATE PROCEDURE command to do this. You can either use the DB2 command prompt or place the command in a program and compile and run it. If you use the DB2 command prompt, you first connect to the DB2 LUW Server where the stored procedure will be executed. 

Syntax:
CREATE [OR REPLACE] PROCEDURE [qualifier.]<procedure_name>
([<argument_name> IN | OUT | IN OUT <datatype>
                                    [DEFAULT <default>],...])
{IS | AS}
  [<variable_name <datatype> [DEFAULT <default>];],...
BEGIN
  <procedure_body>
[EXCEPTION
  <exception_statements>]
END;

Example1: Simple data Insertion into DOC_SCHEEMA.USER_DETAILS table




Note:
 The order of Parameters and  arguments in the db2 stored procedure must be same.

CREATE PROCEDURE DOC_SCHEEMA.USER_DATA
 
(
INOUT P_NAME VARCHAR(64),
INOUT P_FIRST_NAME VARCHAR(64),
INOUT P_NUMBER INTEGER,
INOUT P_DATE_OF_JOINING TIMESTAMP,
INOUT P_CASEID INTEGER
)
    LANGUAGE SQL
BEGIN
 INSERT INTO DOC_SCHEEMA.USER_DETAILS(NAME, FIRST_NAME, NUMBER1, DATE_OF_JOINING, CASEID)  VALUES(P_NAME, P_FIRST_NAME, P_NUMBER, P_DATE_OF_JOINING, P_CASEID);
 
END
  

Example 2: updating status and number1  into DOC_SCHEEMA.CASE_DETAILS table

CREATE PROCEDURE DOC_SCHEEMA.STATUS_UPDATE
(
INOUT P_CASEID INTEGER,
INOUT P_STATUS VARCHAR(64),
INOUT P_NUMBER INTEGER
)
    LANGUAGE SQL
BEGIN
    UPDATE DOC_SCHEEMA.CASE_DETAILS set STATUS = P_STATUS, NUMBER1 = P_NUMBER WHERE CASEID=P_CASEID;
END


Example 3: Update Workflow properties from different db2 tables.

updating Workflow properties name,email_ID, location and phone number based on region and location from different db2 database tables.

CREATE PROCEDURE DOC_SCHEEMA.UPDATE_WORKFLOW_PROPERTIES
(
INOUT P_COUNTRY VARCHAR(64),
INOUT P_REGION VARCHAR(64),
OUT P_NAME VARCHAR(64),
OUT P_EMAIL_ID VARCHAR(64),
OUT P_LOCATION VARCHAR(64),
OUT P_PHONE_NO VARCHAR(64)
)

LANGUAGE SQL
BEGIN

FOR myCursor AS SELECT NAME,EMAIL_ID FROM DOC_SCHEEMA.USER_DETAILS WHERE COUNTRY = P_COUNTRY AND REGION = P_REGION

DO
--updating work flow properties: name and email_id into
 
SET P_NAME = myCursor.NAME;
SET P_EMAIL_ID = myCursor.EMAIL_ID;
 

END FOR;
 
--updating work flow properties: location and phone number

SET P_LOCATION =(SELECT LOCATION FROM DOC_SCHEEMA.LOCATION_DETAILS WHERE COUNTRY = P_COUNTRY AND REGION = P_REGION);
SET P_PHONE_NO =(SELECT PHONE_NO FROM DOC_SCHEEMA.USER_DETAILS WHERE COUNTRY = P_COUNTRY AND REGION = P_REGION);
END
 


Unlock Work Items using ICM 5.2

Author: Dave Ward
Job Title: Solution Architect
Email: dave.ward@insight2value.co.uk
Company: Insight 2 Value, IBM Business Partner based in the UK
Abstract: This article describes how to unlock work items in ICM 5.2. The following techniques are
explored:
- Adding menus to the Inbasket widget.
- Use of Event Actions.
- Use of “Show this event action” script block to limit which users have visibility to the menu
option.
o Use the BPM REST API to check the current users Role membership.
- Use of “Enable this event action” script block to enable the menu option only if the selected
work items are locked.
- Use the BPM REST API to unlock work items.
o Different API sequence for items locked by current user / another user.
o Use of ETag and If-Match header.
- Refresh the Inbasket once you are finished.

Pre-Requisites
Open Case Builder.
Create a new solution / Edit an existing solution.
Create at least 2 Roles.
Create a Case Type / Task / etc.
Create some test cases / work items.
Lock at least one work item by the current user.
Lock at least one work item by another user.
Leave at least one work item unlocked.
Upon completion your Inbasket should look similar to this…


Create the menu event action
Open Case Builder.
Edit your solution.
Click on the “Pages” tab.
Expand the “Solution Pages” group.
Open the “Work” page.


Create a new menu item similar to the following:

In the “Show this event action:” box, paste the following code:
NOTE: You need to add the name of one of your Roles to line 20 of the script (the line beginning “var
myAuthorisedRoles =”)
NOTE: The script checks the current users’ role membership against a hardcoded list of authorised
Roles in the script. This concept can be extended to query LDAP membership, or to call a bespoke
“Authorisation” REST API.
try {
var isAuthorised = false;
/*userid variables*/
var myUserid = "";
var userid = ecm.model.desktop.userId;
var displayName = ecm.model.desktop.userDisplayName;
/*REST API variables*/
var myCP = this.getActionContext("Solution")[0].connectionPoint;
var myRoles;
var serverBase;
var feedURL;
var deferred;
var myAppSpace = this.getActionContext("Role")[0].parent.id;
var xhrArgs;
/*List of authorised roles*/
/*YOU NEED TO ADD THE NAME OF ONE OF YOUR SOLUTION ROLES INTO THIS LIST*/
var myAuthorisedRoles = "|All|Administrator|Exceptions|Service Representative|";
/*Get current username*/
if (userid != null)
{
myUserid = userid;
}
else
{
if (displayName != null)
{
myUserid = displayName;
}
}
/*See if the current username was picked up*/
if (myUserid != "")
{
/*Get CASE REST API url*/
serverBase = window.location.protocol + "\/\/" + window.location.host;
feedURL = serverBase + "/CaseManager/P8BPMREST/p8/bpm/v1/appspaces/" + myAppSpace +
"/myroles?cp=" + myCP;
/*Get my Roles*/
xhrArgs = {
url: feedURL,
handleAs: "json",
sync: true,
headers: { "Content-Type": "application/json"},
load: function(data)
{
myRoles = data;
} ,
error: function(error)
{
alert ("Error Encountered..." + error);
}
};
deferred = dojo.xhrGet(xhrArgs);
/*Iterate through roles and see if authorised*/
for (myRole in myRoles) {
if (myAuthorisedRoles.indexOf("|"+myRole+"|") != -1)
{
isAuthorised = true;
break;
}
}
}
/*Return whether the current user is authorised or not*/
return isAuthorised;
}
catch (Error) {
alert ("Source Module: " + this.arguments.label + " Script
Adaptor\r\n\r\n"+Error.name+" - "+Error.description+"\r\n"+Error.message);
/*An error was thrown. Always return false*/
return false;
}
In the “Enable this event action:” box, paste the following code:
NOTE: The script returns true if all of the selected work items are locked and false otherwise. This is
one example of how you might apply “enable” logic using this new feature of ICM 5.2
try {
/*Init return variable*/
var allLocked = true;
/*Get the work items that were selected*/
var selectedWorkItems = this.getActionContext("WorkItemReference");
/*Variable declarations*/
var currentWorkItem;
var i;
/*Was an array of work items returned*/
if (dojo.isArray(selectedWorkItems))
{
/*Loop through the array*/
for (i=0; i<selectedWorkItems.length; i++)
{
/*Get a reference to the current work item*/
currentWorkItem = selectedWorkItems[i];
/*Check whether work item is locked*/
if (currentWorkItem.lockedUser == "")
{
/*If not locked, set variable to false and break out of the loop*/
allLocked = false;
break;
}
}
}
else
{
/*Get a reference to the current work item*/
currentWorkItem = selectedWorkItems;
/*Check whether work item is locked*/
if (currentWorkItem.lockedUser == "")
{
/*If not locked, set variable to false*/
allLocked = false;
}
}
/*Return whether all of the selected work items were locked or not*/
return allLocked;
}
catch (Error) {
alert ("Source Module: " + this.arguments.label + " Action Adaptor\r\n\r\n"+Error.name+" -"+Error.description+"\r\n"+Error.message);
/*An error was thrown. Always return false*/
return false;
}
Click “OK” to add the menu item.



Create the Script Adaptor

In the Page Designer area, click the “Show or Hide Hidden Widgets” toolbar button.
In the Widgets palette, expand the “Utility Widgets” group.
Drag the “Script Adaptor” widget into the Hidden widget area.

In the “Javascript:” box, paste the following code:
NOTE: The BPM REST API is used in this script and not the new model API. There are a couple of
goods reasons for this:
- The model API does not currently provide a method to unlock a work item that has been
locked by another user.
- The Inbaskets widget does not respond to a broadcast “icm.Refresh” event. If you want to
refresh the Inbasket after the script has completed, then using the dojo.xhrGet and
dojo.xhrPut commands with the “sync: true” setting allows you to synchronously unlock the
work items. This enables you to wire an outbound event from the Script Adaptor to the
Inbaskets  Refresh event.
try {
var ScriptAdapterScope = this;
/*Get the work items that were selected*/
var selectedWorkItems = payload.WorkItemReference;
/*Variable declarations*/
var currentWorkItem;
var i;
/*userid variables*/
var myUserid = "";
var userid = ecm.model.desktop.userId;
var displayName = ecm.model.desktop.userDisplayName;
/*REST API variables*/
var myCP = payload.Solution.connectionPoint;
var xhrArgs;
var myPayload;
var myETag;
var deferred;
var serverBase;
var feedURL;
/*Get current username*/
if (userid != null) {
myUserid = userid;
}
else {
if (displayName != null) {
myUserid = displayName;
}
}
/*Was an array of work items returned*/
if (dojo.isArray(selectedWorkItems)) {
/*Loop through the array*/
for (i = 0; i < selectedWorkItems.length; i++) {
/*Get a reference to the current work item*/
currentWorkItem = selectedWorkItems[i];
/*Check whether work item is locked*/
if (currentWorkItem.lockedUser != "") {
/*Check whether work item is not locked by the current user*/
if (currentWorkItem.lockedUser.toUpperCase() != myUserid.toUpperCase()) {
/*Locked by someone else, so need to override the lock*/
/*Call the overrideLock function*/
overrideLock(currentWorkItem.queueName, currentWorkItem.id);
}
/*Abort the current action on the wob*/
/*Call the abortWob function*/
abortWob(currentWorkItem.queueName, currentWorkItem.id);
}
}
}
else {
/*Get a reference to the current work item*/
currentWorkItem = selectedWorkItems;
/*Check whether work item is locked*/
if (currentWorkItem.lockedUser != "") {
/*Check whether work item is not locked by the current user*/
if (currentWorkItem.lockedUser.toUpperCase() != myUserid.toUpperCase()) {
/*Locked by someone else, so need to override the lock*/
/*Call the overrideLock function*/
overrideLock(currentWorkItem.queueName, currentWorkItem.id);
}
/*Abort the current action on the wob*/
/*Call the abortWob function*/
abortWob(currentWorkItem.queueName, currentWorkItem.id);
}
}
}
catch (Error) {
alert("Source Module: " + this.name + " Script Adaptor\r\n\r\n" + Error.name + " - " +
Error.description + "\r\n" + Error.message);
}
function overrideLock(currentQueueName, currentWobId) {
try {
/*Get CASE REST API url*/
serverBase = window.location.protocol + "\/\/" + window.location.host;
feedURL = serverBase + "/CaseManager/P8BPMREST/p8/bpm/v1/queues/" + currentQueueName +
"/stepelements/" + currentWobId + "?cp=" + myCP;
/*Get Step Element*/
xhrArgs = {
url: feedURL,
handleAs: "json",
sync: true,
headers: { "Content-Type": "application/json" },
load: function (data) {
myPayload = data;
},
error: function (error) {
alert("Error Encountered..." + error);
}
};
deferred = dojo.xhrGet(xhrArgs);
/*Get ETag from request*/
myETag = deferred.ioArgs.xhr.getResponseHeader("ETag");
/*Need to override the existing lock*/
/*Get CASE REST API url*/
feedURL = serverBase + "/CaseManager/P8BPMREST/p8/bpm/v1/queues/" + currentQueueName +
"/stepelements/" + currentWobId + "?cp=" + myCP + "&action=overrideLock";
/*You must pass in the ETag from the previous REST call into an "If-Match" header
tag*/
xhrArgs = {
url: feedURL,
handleAs: "json",
sync: true,
headers: { "Content-Type": "application/json", "If-Match": myETag },
load: function (data) {
myPayload = data;
},
error: function (error) {
alert("Error Encountered..." + error);
}
};
deferred = dojo.xhrPut(xhrArgs);
}
catch (Error) {
alert("Source Module: " + ScriptAdapterScope.name + " - overrideLock Function Script
Adaptor\r\n\r\n" + Error.name + " - " + Error.description + "\r\n" + Error.message);
}
}
function abortWob(currentQueueName, currentWobId) {
try {
/*Get CASE REST API url*/
serverBase = window.location.protocol + "\/\/" + window.location.host;
feedURL = serverBase + "/CaseManager/P8BPMREST/p8/bpm/v1/queues/" + currentQueueName +
"/stepelements/" + currentWobId + "?cp=" + myCP;
/*Get Step Element*/
xhrArgs = {
url: feedURL,
handleAs: "json",
sync: true,
headers: { "Content-Type": "application/json" },
load: function (data) {
myPayload = data;
},
error: function (error) {
alert("Error Encountered..." + error);
}
};
deferred = dojo.xhrGet(xhrArgs);
/*Get ETag from request*/
myETag = deferred.ioArgs.xhr.getResponseHeader("ETag");
/*Unlock the WOB by issuing abort action*/
/*Get CASE REST API url*/
serverBase = window.location.protocol + "\/\/" + window.location.host;
feedURL = serverBase + "/CaseManager/P8BPMREST/p8/bpm/v1/queues/" + currentQueueName +
"/stepelements/" + currentWobId + "?cp=" + myCP + "&action=abort";
/*You must pass in the ETag from the previous REST call into an "If-Match" header
tag*/
xhrArgs = {
url: feedURL,
handleAs: "json",
sync: true,
headers: { "Content-Type": "application/json", "If-Match": myETag },
load: function (data) {
myPayload = data;
},
error: function (error) {
alert("Error Encountered..." + error);
}
};
deferred = dojo.xhrPut(xhrArgs);
}
catch (Error) {
alert("Source Module: " + ScriptAdapterScope.name + " - abortWob Function Script
Adaptor\r\n\r\n" + Error.name + " - " + Error.description + "\r\n" + Error.message);
}
}
Click “OK” to close the Script Adaptor settings dialog

In the Page Designer, Click “Save” and then “Close”.
In your Solution click “Save and Close”.
In the Case Builder home page, “Deploy” your solution.

Test the Script