Project and Portfolio Management Practitioners Forum
cancel

How to get the action names for a status?

SOLVED
Go to solution
Highlighted
TurboMan
Honored Contributor.

How to get the action names for a status?

Hi,

 

Is there any way, could be a query or a package so I can get the available actions for a partucular Status?

 

Any help is appreciated.

 

Regards

T.M.

7 REPLIES
Jason Nichols K
Acclaimed Contributor.

Re: How to get the action names for a status?

T.M.

 

I don't have the full SQL to get what you are looking for, but I can tell you what tables you will need to join together to get what I think you are looking for.

 

KCRT_STATUSES_NLS status_id, status_name

KWFL_WORKFLOW_STEPS_NLS parent_status,  step_source_id

KWFL_APPROVALS_NLS approval_id, result_validation_id

KNTA_VALIDATIONS_NLS validation_id, validation_name

KNTA_LOOKUPS_NLS lookup_type, code, meaning

 

You may also want to throw in KWFL_WORKFLOW_INSTANCES and KWFL_WORKFLOW_INSTANCE_STEPS if you are trying for a specific request workflow.

Mohit_Agrawal
Outstanding Contributor.

Re: How to get the action names for a status?

Hi TurboMan,

 

I have created your query. Please dont forget to give me KUDOS.. :)

 

 /*  To find actions available actions for a request */
SELECT VISIBLE_RESULT_VALUE
  FROM kwfl_step_transitions kst,
       kwfl_workflow_steps_nls kwsn,
       kcrt_requests kr,
       kcrt_statuses ks
 WHERE kr.request_id = 126042
   AND kr.status_id = ks.status_id
   AND kwsn.workflow_id = kr.workflow_id
   AND kwsn.step_name = ks.status_name
   AND kst.from_workflow_step_id = kwsn.workflow_step_id

 

 

Thanks!!

Mohit Agrawal

:: Please mark as ACCEPTED if this resolves your query...

TurboMan
Honored Contributor.

Re: How to get the action names for a status?

Thanks Mohit,

 

You've got my KUDOS for sure. 

 

Regards

T.M.

 

Edited:  After I post the message I just found out that, this query selects also the disabled (invisible) action buttons.

I hope you've got a solution for this, thanks :)

 

Mohit_Agrawal
Outstanding Contributor.

Re: How to get the action names for a status?

Hi TM,

 

The actions available for a request at any status are basically the OUTGOING arrows in the workflow from that workflow step. We are on PPM 8.02 and I cannot find any way to disable the arrow (action).

Could you please clarify: what do you mean by Disabled action buttons?

 

Thanks!

Mohit Agrawal

TurboMan
Honored Contributor.

Re: How to get the action names for a status?

You are in the right track Mohit,

 

We are on the PPM 9.14, there should not be any difference in WF wise.

As you know, the arrows are actually Validations.

When you Edit  a Decision Step in a WF, you see the validation , if you open up the validation and make one of the transition  Enabled = N, even though that action button is visible in the WF, will not be shown in the request for that request status. 

I hope I didn't make it as clear as mud :) .

 

Regards

T.M.

 

 

Mohit_Agrawal
Outstanding Contributor.
Solution

Re: How to get the action names for a status?

I got your point TurboMan and here is the modified query.

 

 

/* To get only Enabled actions for a request*/
SELECT kl.meaning      -- If meaning doesn't work in your case then try with lookup_code ---
  FROM kcrt_requests kr,
       kcrt_statuses ks,
       knta_lookups kl,
       kwfl_approvals ka,
       kwfl_workflow_steps kws,
       knta_validations kv
 WHERE kr.request_id = request_id
   AND kr.status_id = ks.status_id
   AND kws.step_name = ks.status_name
   AND ks.status_id = kr.status_id
   AND kws.workflow_id = kr.workflow_id
   AND ka.approval_id = kws.step_source_id
   AND kl.lookup_type = kv.lookup_type
   AND kv.validation_id = ka.result_validation_id
   AND kl.enabled_flag = 'Y'

 

I took me around an hour to get this done. Another KUDOS will be a welcome :) :)

 

Thanks!!

Mohit Agrawal

 

TurboMan
Honored Contributor.

Re: How to get the action names for a status?

Thanks Mohit,

 

you've got my another KUDOS :)

I noticed something, if you add ORDER BY kl.seq , the query result will be same as it appears on the request form.

As follows

SELECT kl.meaning      -- If meaning doesn't work in your case then try with lookup_code ---
  FROM kcrt_requests kr,
       kcrt_statuses ks,
       knta_lookups kl,
       kwfl_approvals ka,
       kwfl_workflow_steps kws,
       knta_validations kv
 WHERE kr.request_id = request_id
   AND kr.status_id = ks.status_id
   AND kws.step_name = ks.status_name
   AND ks.status_id = kr.status_id
   AND kws.workflow_id = kr.workflow_id
   AND ka.approval_id = kws.step_source_id
   AND kl.lookup_type = kv.lookup_type
   AND kv.validation_id = ka.result_validation_id
   AND kl.enabled_flag = 'Y' 

ORDER BY kl.seq