Project and Portfolio Management Practitioners Forum
cancel

Query to retreive a list of enable request types and their associated workflows

Highlighted
Tommy Vu
Valued Contributor.

Query to retreive a list of enable request types and their associated workflows

what table should i use to query a list of request type and its associated workflows?
6 REPLIES
sirib9
Respected Contributor.

Re: Query to retreive a list of enable request types and their associated workflows

List of request types can be found in kcrt_request_types view and workflows in kwfl_workflows view.

Thanks
Siri..
Tommy Vu
Valued Contributor.

Re: Query to retreive a list of enable request types and their associated workflows

yes, but how do you join them? ex: the workflow associate with what request type?
PublicEducation
Valued Contributor.

Re: Query to retreive a list of enable request types and their associated workflows

The joining tables are easy to identify, the foreign key used to join requests to workflows is workflow ID. It is in the kcrt_request_types or Kcrt_requests table.

This structure is similar throughout the whole application. Indeed, you can walk up and down the structure of the app just by locating the main table of that entity.

ex: wp_tasks is a project table that holds the foreign keys to wp_task_schedule, wp_task_actuals, wp_task_actuals and more.


Tommy Vu
Valued Contributor.

Re: Query to retreive a list of enable request types and their associated workflows

thanks but in the kcrt_requests has workflow_id but not in the kcrt_request_types.
Shankar Gupta
New Member.

Re: Query to retreive a list of enable request types and their associated workflows

Dear Tommy,

There is no direct linkage between Request type and workflow. These are formed only when some request is created and from there you can query workflow on kcrt_requests_v. But what if no request exists?

However in Workflow we can mention the Request types in Request types tab.
But that will not help you.
Another way I have drilled around and found a way out for the purpose. Look, Workflows can be mentioned in the Rules and from there we can query the workflow of a request type.

"SELECT RT.REQUEST_TYPE_NAME, RL.WORKFLOW_NAME
FROM KNTA_PARAM_RULES_V RL,KCRT_REQUEST_TYPES_V RT
WHERE WORKFLOW_NAME IS NOT NULL
AND RT.PARAMETER_SET_CONTEXT_ID = RL.PARAMETER_SET_CONTEXT_ID
AND RT.ENABLED_FLAG = 'Y'"

This is right on target and will delight you :)
Tommy Vu
Valued Contributor.

Re: Query to retreive a list of enable request types and their associated workflows

thank you so much.