OIM Certification Entity Details using SQL

Using SQL query you can find the details of certification entity by providing certification id as input. There are 3 different queries for Application Instance, Entitlement and Role.

OIM does have certification APIs for getting the entity details from certification id like:

CertificationService -> getIDCEntitlementsByBatch
CertificationService -> getIDCRolesByBatch
CertificationService -> getIDCApplicationInstancesByBatch

But many occasion, these APIs create problem during execution.

Below are the sample queries, which can easily replace the above APIs:

----------------------------------- Entitlement Certification --------------------------------------
SELECT
cert_certs.id AS certification_id,
cert_certs.cert_name AS certificationname,
certs_ent_defn.attr_value AS attribute_value,
certd_ent_defn.glossary_def AS name,
          certd_ent_defn.entity_id AS entitlement_entity_id
FROM
certd_ent_defn certd_ent_defn
INNER JOIN cert_certs cert_certs
ON certd_ent_defn.cert_id = cert_certs.id
INNER JOIN certs_ent_defn certs_ent_defn
ON certd_ent_defn.attr_val_id = certs_ent_defn.id
WHERE
         cert_certs.id = [CERT_ID]

------------------------------------ Application Instance Certification ------------------------
SELECT
certd_app_inst.cert_id AS certification_id,
certs_app_inst.iam_endpoint_id AS applicationid,
certs_app_inst.endpoint_name AS application_name
FROM
certs_app_inst, certd_app_inst,
cert_certs 
WHERE
cert_certs.id = certd_app_inst.cert_id 
AND certs_app_inst.id = certd_app_inst.endpoint_id 
        AND cert_certs.id=[CERT_ID]

------------------------------------------ Role Certification --------------------------------------
SELECT
cert_certs.id AS certification_id,
certs_role.rolename,
certs_role.entity_display_name
FROM
certs_role, certd_role,
cert_certs 
WHERE
cert_certs.id = certd_role.cert_id 
AND certd_role.role_id = certs_role.id
AND  cert_certs.id = [CERT_ID]