Data Dictionary for EIC Tables

[robcivitello]

Hello - Is there a documented Data Dictionary for the EIC database tables?  I'm specifically looking for a description of the valid values for the certification_account_entitlement1_status.certified and certification_certifier.certifer_type fields.

[SumathiSomala]

@robcivitello Refer the below document

Database Schema Reference (saviyntcloud.com)

If this reply answered your question, please Accept it As Solution to help others who may have a similar problem.

[robcivitello]

Hello - i marked as solved in haste.  Looking through the document it appears that the "Fields and Descriptions of Key Tables"  section is incomplete.  Is there information available for fields and descriptions of the certification_certifier and certification_account_entitlement1_status tables?

[rushikeshvartak]

TABLE_NAME COLUMN_NAME IS_NULLABLE DATA_TYPE COLUMN_TYPE

certification_certifier	CERT_CERTIFIERKEY	NO	bigint	bigint(20)
certification_certifier	accountkey	YES	bigint	bigint(20)
certification_certifier	CERTIFIER	NO	bigint	bigint(20)
certification_certifier	CERTIFIER_TYPE	NO	bigint	bigint(20)
certification_certifier	CERTKEY	NO	bigint	bigint(20)
certification_certifier	ENTKEY	YES	bigint	bigint(20)
certification_certifier	NOTIFICATIONSENT	YES	bit	bit(1)
certification_certifier	rolekey	YES	bigint	bigint(20)
certification_certifier	STATUS	YES	bigint	bigint(20)
certification_certifier	USERKEY	NO	bigint	bigint(20)
certification_certifier	custom_property	YES	varchar	varchar(255)
certification_certifier	certification_role_user_account_key	YES	varchar	varchar(255)
certification_certifier	endpointkey	YES	bigint	bigint(20)
certification_certifier	ISSECONDARYCERTIFIER	YES	bit	bit(1)
certification_certifier	ELASTIC_ID	YES	varchar	varchar(255)
certification_certifier	PARENT_ES_ID	YES	varchar	varchar(255)
certification_certifier	PARENT_ENABLED	YES	bit	bit(1)

TABLE_NAME COLUMN_NAME IS_NULLABLE DATA_TYPE COLUMN_TYPE

certification_account_entitlement1_status	CERTKEY	NO	bigint	bigint(20)
certification_account_entitlement1_status	CERT_ENTITLEMENT_VALUEKEY	NO	bigint	bigint(20)
certification_account_entitlement1_status	CERT_ACCOUNTKEY	NO	bigint	bigint(20)
certification_account_entitlement1_status	ACCESS	YES	varchar	varchar(255)
certification_account_entitlement1_status	ARSTASKKEY	YES	bigint	bigint(20)
certification_account_entitlement1_status	ASSIGNEDFROMCOMPROLE	YES	bit	bit(1)
certification_account_entitlement1_status	ASSIGNEDFROMROLE	YES	bigint	bigint(20)
certification_account_entitlement1_status	ASSIGNEDFROMRULE	YES	varchar	varchar(255)
certification_account_entitlement1_status	ASSIGNEDFROMCHILD	YES	bit	bit(1)
certification_account_entitlement1_status	AUDIT_TRAIL	YES	longtext	longtext
certification_account_entitlement1_status	CERTIFIED	YES	bigint	bigint(20)
certification_account_entitlement1_status	COMMENTS	YES	longtext	longtext
certification_account_entitlement1_status	ENDDATE	YES	datetime	datetime
certification_account_entitlement1_status	JRMRULES	YES	varchar	varchar(255)
certification_account_entitlement1_status	LASTCERTIFIEDCAMPAIGNNAME	YES	varchar	varchar(255)
certification_account_entitlement1_status	LASTUSEDENDDATE	YES	datetime	datetime
certification_account_entitlement1_status	RISKS	YES	longtext	longtext
certification_account_entitlement1_status	STARTDATE	YES	datetime	datetime
certification_account_entitlement1_status	TASKSTATUS	NO	bigint	bigint(20)
certification_account_entitlement1_status	UPDATEDATE	YES	datetime	datetime
certification_account_entitlement1_status	UPDATEUSER	YES	bigint	bigint(20)
certification_account_entitlement1_status	LASTCERTIFIEDDATE	YES	datetime	datetime
certification_account_entitlement1_status	LASTCERTIFIEDUSER	YES	varchar	varchar(255)
certification_account_entitlement1_status	TASKID	YES	bigint	bigint(20)
certification_account_entitlement1_status	ISREASSIGNED	YES	bit	bit(1)

[robcivitello]

HI Rushikesh - what I'm looking for is a description of valid values for the following fields:

certification_certifier

CERTIFIER_TYPE

NO

bigint

bigint(20)

certification_account_entitlement1_status

CERTIFIED

YES

bigint

bigint(20)

Update: 11/08/2023

I would also like to know the valid values for :

certification_role_user_account_status.certified

I am trying to write queries to capture certification progress and the values of the certified column are confusing.  In some cases I am seeing that a value of 0 corresponds to Rejected, and in other cases a value of '2' seems to correspond to rejected. 

It would be extremely helpful if the data dictionary included valid field values.  

[DaanishJawed]

Hi @robcivitello ,

We were able to find information for certification_account_entitlement1_status and certification_role_user_account_status.

certification_account_entitlement1_status

WHEN CERTIFIED=2 THEN 'CERTIFY'
WHEN CERTIFIED=3 THEN 'REVOKE/REJECTED'
WHEN CERTIFIED=5 THEN 'CONDITIONALLY CERTIFY'
WHEN CERTIFIED=-1 THEN 'TERMINATED'

certification_role_user_account_status

WHEN CERTIFIED=2 THEN 'CERTIFY'
WHEN CERTIFIED=3 THEN 'REVOKE/REJECTED'
WHEN CERTIFIED=5 THEN 'CONDITIONALLY CERTIFY'

Can you tell me for which campaign type are you seeing the value 0 for rejected? Please share the query that results in 0 for you for a rejected response on the UI.

Thanks.

[Manu269]

@DaanishJawed  is this be going to be updated in the document?

[uthra_rahul]

@Krishnan Please update the doc as required and notify on this thread.

[robcivitello]

@DaanishJawed - you asked for a query that shows certified value of 0 as rejected and 1 as approved.  The actual query i'm using is pretty long and complicate, but you can see the values just by looking on the certification_account_entitlement1_status table.  See below query compared with actual view of the certification through the GUI:

select certified, count(*) as occurrences from certification_account_entitlement1_status where certkey = 26652 group by certified

Also on certification_role_user_account_status I've observed by comparing to the GUI that 1 seems to be approved and 2 is rejected which also doesn't line up with the case statement above.  It seemed weird to me that the field values would not be consistent which is why i started the thread.  In your example case statement above they DO appear consistent (which is what i would have expected) but that isn't matching what is actually on the tables.

note: I've tested this against 23.6 and 23.11 and get the same results in both.

[DaanishJawed]

HI @robcivitello ,

• What is the type of the campaign that you are checking for ?
• Are you checking for an old campaign that was launched in previous versions or is this a newly launched campaign?

Thanks

[robcivitello]

I'm working on queries for Entitlement Owner, Role Owner, and User Manager.  So far I've completed Entitlement Owner and Role Owner.  I am looking at currently active (In Progress) campaigns. 

The campaigns are recently launched in their respective versions (23.11 test and 23.6 prod)

[DaanishJawed · Accepted solution]

Hi @robcivitello ,

Please ignore my previous comment. Below is the list of values and their meanings along with the corresponding tables that you can refer for RO, UM and EO campaigns. All these tables are visible in Data Analyzer which will help you format your queries.

Campaign Type	Action on the UI	Certified Column Value(DB)	Table Name
UM - Step 1	Works for me	2	certification_user_status
	Does not work for me	3	certification_user_status
	Terminated	4	certification_user_status
UM - Step 2 (Base Accounts)	Certified	2	certification_user_account_status
	Revoked	3	certification_user_account_status
UM - Step 2 (Entitlements)	Certified	2	certification_account_entitlement1_status
	Revoked	3	certification_account_entitlement1_status
	Conditional Certified	5	certification_account_entitlement1_status
EO - Step 1	Belongs To Me	1	cert_ent_values_status
	Does Not Belong To Me	4	cert_ent_values_status
EO - Step 2	Approved	1	certification_account_entitlement1_status
	Rejected	0	certification_account_entitlement1_status
Role Owner	Approved	1	certification_role_user_account_status
	Rejected	2	certification_role_user_account_status

UM - User Manager

EO - Entitlement Owner

RO - Role Owner

I have tested and validated the above values. Please let me know if you see any discrepancies.

We will work with the PM team and also include other campaign types in the above dataset.

@uthra_rahul@KrishnanPlease refer this comment for DOC update.

Thanks.

[robcivitello]

Thank you, this is what I needed!