Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Setting user defined token value from execution step command

Highlighted
Łukasz Czech
Collector

Setting user defined token value from execution step command

Hi,

this thread is somehow connected with http://forums13.itrc.hp.com/service/forums/questionanswer.do?threadId=1221259 however this can be general question too.

I need to create execution step with command that will set value of my field according to value gather from SQL query.
The main issue here is execution type to be selected:
- it can be PL/SQL function/SQL Statement however I don't know how to set parameter and visible parameter for a token in that case
- it can't be simply a token as no SQL can be then provided
- it can be Workflow Step commands however I need some mix of ksc_run_sql (does it return query result?) and ksc_store commands to set token value.

I would appreciate working solution or some tips.

BR
Lukasz
5 REPLIES
Sascha Mohr
Esteemed Contributor

Re: Setting user defined token value from execution step command

If you are allowed to, you can create a PL/SQL function that does this for you, then call it from an execution step passing the request id to it like this:

do_something(
p_ID => [REQ.REQUEST_ID]
)

The function the should probably have 2 local variables (one for the request and one for whatever you want to store somewher), one select statement to get the value needed and one update statement (don't forget the commit and a return value).
hth
Łukasz Czech
Collector

Re: Setting user defined token value from execution step command

Sascha,

you can also set this value using simple SQL UPDATE command invoked by ksc_run_sql command, however I thought it is possible to avoid making DB updates and use PPM engine instead (just like setting field value in request type rules).

BR
Lukasz
Tim Brandish
Frequent Visitor

Re: Setting user defined token value from execution step command

I like to use Workflow Step Commands in this format:

ksc_run_sql QUERY_STRING="select column_a from data_table where column_b = '[REQ.VP.REQUEST_TYPE]'" ENV_NAME="KINTANA_DB"
ksc_set MY_VALUE=[SQL_OUTPUT]
ksc_store TEST_TOKEN =[MY_VALUE],[MY_VALUE]
Diego Aguila
Occasional Contributor

Re: Setting user defined token value from execution step command

Tim, your commands it works!
by the way it works to in OOTB fields, this is a example in PPM 9.14

 

ksc_run_sql QUERY_STRING="select rd.visible_parameter41 from kcrt_requests r join kcrt_request_details rd on rd.request_id = r.request_id join kcrt_request_types_nls rt on rt.request_type_id = r.request_type_id and rt.request_type_name='BCH - Cambio de Ambito' WHERE R.REQUEST_ID=[REQ.REQUEST_ID]" ENV_NAME="KINTANA_SERVER_DEV"
ksc_set VALOR=[SQL_OUTPUT]
ksc_store REQ.DESCRIPTION=[VALOR],[VALOR]

 

Regards,

Diego

AlexSavencu
Honored Contributor

Re: Setting user defined token value from execution step command

Hi,

This will be applicable for text fields, but not for validated fields where the visible value is different than the hidden value.

In the latter, I use a hidden token to store the hidden value and call two SQL statements to fetch the hidden and visible values sequentially.

Cheers
Alex

--remember to kudos people who helped solve your problem
//Add this to "OnDomLoad" event