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

How to get the approvals list for a request id?

SOLVED
Go to solution
Highlighted
TurboMan
Member

How to get the approvals list for a request id?

Hi,

 

Is there any SQL query that gets me a list of the approver names for a request id for the current status?

Any help is appreciated

 

Regards

T.M.

 

3 REPLIES
Celil
Esteemed Contributor

Re: How to get the approvals list for a request id?

Hi TM,

 

KWFL_APPROVALS: 

The term "Approval" has changed to "Decision", but the schema still uses the
older term. A decision is represented by one row in this table. Workflow steps
can be of three types: decisions, conditions, and executions.

 

KWFL_APPROVAL_RECIPIENTS:

This table stores a row for each user authorized to vote on a multi-voter
approval step. The rows are inserted into this table when the step first becomes
Eligible. The rows are then updated with the result value chosen by the user, or
the new approval recipient if the user chose to delegate his/her vote.

 

AND

 

KWFL_STEP_TRANSACTION_HISTORY:

A row is inserted into this table each time the status of an execution or decision
step transaction changes to ELIGIBLE, COMPLETED, or ERROR. For
condition step transactions, a history row is inserted when the status becomes
PENDING or COMPLETED.

 

KWFL_STEP_TRANSACTIONS:

There is one row in the KWFL_STEP_TRANSACTIONS table for each time
that a step in a workflow is visited. This row contains the unique data for the
step transaction, and denormalized context data for quicker querying.

 

 

PS: If the post resolves your issue, please click KUDOS star icon and mark it as Accepted Solution.

 

Celil

IT Governance Professional
& PPM Solution Architect
TurboMan
Member

Re: How to get the approvals list for a request id?

Thanks Celil,

 

A descent query would have been better, anyway I try to sort it out.

If I sort it out I will post it.

 

Regards

T.M.

TurboMan
Member
Solution

Re: How to get the approvals list for a request id?

Hi All,

 

Finally, I managed to get it work for whom interested in.

By using the following query I get Username and Approver.

But, Approver might be a user defined token or a security group.

Or if you are lucky username field contains the username ???!!!

 

SELECT a.approver_type, users.username, a.approver,
a.security_group_id
FROM kwfl_approval_details_v a,
knta_users users
where a.instance_source_set_id=:request_id
and a.active_flag='Y'
AND users.full_name(+)=a.approver
;

 

If a.approver_type is a SECURITY_GROUP then you can check who is in this group by using the following query.

 

SELECT users.full_name
FROM knta_security_groups ksg, KNTA_USER_SECURITY kus, KNTA_USERS users
WHERE ksg.security_group_name = :security_group_name
and kus.security_group_id=ksg.security_group_id
and users.user_id = kus.user_id
AND NVL(users.start_date, CURRENT_DATE) <= CURRENT_DATE
AND NVL(users.end_date, CURRENT_DATE + 1) > CURRENT_DATE

;

 

If a.approver_type is a USER then these token values either in kcrt_request_details or kcrt_req_header_details table with the related field values .

 

Hope it helps

T.M.

 

//Add this to "OnDomLoad" event