Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Need information on Certified status

sk
Regular Contributor
Regular Contributor

Hello experts,

Can we print Certified status , currently it printing json value , so when manager clicks works for me it is displaying as "2" and manager clicks does not works for me it is displaying as "3". Instead of that we need 

WHEN CERTIFIED = 2 THEN 'WORKS FOR ME' WHEN CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME' ELSE 'No response'

currently we are using below query 

SELECT c.campaign_name AS 'Campaign Name',CASE c.campaign_type WHEN '1' THEN 'ENTITLEMENTOWNER' WHEN '2' THEN 'USERMANAGER' WHEN '3' THEN 'SELF_CERTIFICATION' WHEN '5' THEN 'ROLEOWNER' WHEN '6' THEN 'SERVICEACCOUNT' WHEN '7' THEN 'ORGANIZATION' WHEN '8' THEN 'APPLICATION_OWNER' WHEN '9' THEN 'APPLICATIONOWNER_METADATA' END AS campaign_type,CASE WHEN cus.CERTIFIED = 2 THEN 'WORKS FOR ME' WHEN cus.CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME' ELSE 'No response' END AS 'Certified Status', k.CERT_NAME AS 'Certification Name', u2.SYSTEMUSERNAME AS 'Certifier Username', u2.FIRSTNAME AS 'Certifier First Name', u2.LASTNAME AS 'Certifier Last Name', cu.username,cu.FIRSTNAME AS 'User First Name', cu.LASTNAME AS 'User Last Name' FROM campaign c, certification k, certification_user_status cus, certification_user cu, users u, users u2 WHERE c.id = k.campaignkey AND cus.CERT_USERKEY = cu.cert_userkey AND cus.certkey = k.certkey AND u.USERKEY = cu.USERKEY AND k.CERTIFIER = u2.userkey

User import json

{
"connection": "userAuth",
"successResponses": {
"statusCode": [
200,
201,
202,
203,
204,
205
]
},
"url": "https://xxx.saviyntcloud.com/ECM/api/v5/fetchRuntimeControlsDataV2?max=500",
"httpMethod": "POST",
"httpHeaders": {
"Authorization": "${access_token}"
},
"httpParams":"{\"analyticsname\":\"Campaign\"}",
"httpContentType": "application/json",
"userResponsePath": "results",
"colsToPropsMap": {
"username": "username~#~char",
"customproperty10": "CERTIFIED~#~char"
},
"errorCode": "400",
"errorCodePath": "errorCode"
}

5 REPLIES 5

rushikeshvartak
All-Star
All-Star

Can you elaborate issue. ? Issue in query / json ? 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

sk
Regular Contributor
Regular Contributor

Hi @rushikeshvartak 

The issue is in Query, the certified status is printing as 2 and 3 but we need to print as WHEN CERTIFIED = 2 THEN 'WORKS FOR ME' WHEN CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME' ELSE 'No response' in user customproperty,

 

if we use this condition in query WHEN CERTIFIED = 2 THEN 'WORKS FOR ME' WHEN CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME' ELSE 'No response' then it is not updating in user cp10.

Is there any other way we can achieve this

 

SELECT
c.campaign_name AS 'Campaign Name',
CASE c.campaign_type
WHEN '1' THEN 'ENTITLEMENTOWNER'
WHEN '2' THEN 'USERMANAGER'
WHEN '3' THEN 'SELF_CERTIFICATION'
WHEN '5' THEN 'ROLEOWNER'
WHEN '6' THEN 'SERVICEACCOUNT'
WHEN '7' THEN 'ORGANIZATION'
WHEN '8' THEN 'APPLICATION_OWNER'
WHEN '9' THEN 'APPLICATIONOWNER_METADATA'
END AS campaign_type,
CASE
WHEN cus.CERTIFIED = 2 THEN 'WORKS FOR ME'
WHEN cus.CERTIFIED = 3 THEN 'DOES NOT WORK FOR ME'
ELSE 'No response'
END AS 'Certified Status',
k.CERT_NAME AS 'Certification Name',
u2.SYSTEMUSERNAME AS 'Certifier Username',
u2.FIRSTNAME AS 'Certifier First Name',
u2.LASTNAME AS 'Certifier Last Name',
cu.username,
cu.FIRSTNAME AS 'User First Name',
cu.LASTNAME AS 'User Last Name'
FROM
campaign c,
certification k,
certification_user_status cus,
certification_user cu,
users u,
users u2
WHERE
c.id = k.campaignkey
AND cus.CERT_USERKEY = cu.cert_userkey
AND cus.certkey = k.certkey
AND u.USERKEY = cu.USERKEY
AND k.CERTIFIER = u2.userkey;


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

sk
Regular Contributor
Regular Contributor

@rushikeshvartak , thanks for the response

Need information on , if manager clicks on works for me in user manager campaign , then in the next step (Approve or Revoke Entitlements), manager clicks on certified or remove (attached the image)

saiKrishna_0-1716544464307.png

WHen Manager clicks on certified or remove that data we need to get in the query.

Can you help me to get that query