6. Database Schema

6. Database Schema

Request Module

Column Name

Column Value

Joining Table

Request ID

wo.workorderid

workorder wo

Request Subject

wo.title

workorder wo

Request created time

longtodate(wo.createdtime)

workorder wo

Request category

cd.CATEGORYNAME

LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID

Request sub category

scd.NAME

LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID

Request Item

icd.NAME

LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID

Request mode

mdd.MODENAME

LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID

Request Group

qd.QUEUENAME

LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID

LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID

Requester

aau.FIRST_NAME

LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID

LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID

Department

dpt.DEPTNAME

LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID

Created by

cri.FIRST_NAME

LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID

LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID

Urgency

urgdef.NAME

LEFT JOIN UrgencyDefinition urgdef ON wos.URGENCYID=urgdef.URGENCYID

Impact

impdef.NAME

LEFT JOIN ImpactDefinition impdef ON wos.IMPACTID=impdef.IMPACTID

Request Type

rtdef.NAME

LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID

Technician

ti.FIRST_NAME

LEFT JOIN SDUser td ON wos.OWNERID=td.USERID

LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID

Description

wotodesc.FULLDESCRIPTION

LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID

Site

sdo.NAME

LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID

LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID

Region

regionDef.REGIONNAME

LEFT JOIN RegionDefinition regionDef ON siteDef.REGIONID=regionDef.REGIONID

Request Closure Code

rcode.NAME

LEFT JOIN RequestClosureCode rcode ON wos.CLOSURECODEID=rcode.CLOSURECODEID

Request Closure Comments

wos.CLOSURECOMMENTS

Workorderstates wos

service category

serdef.NAME

LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID

Priority

pd.PRIORITYNAME

LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID

Level

lvd.LEVELNAME

LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID

Request Status

std.STATUSNAME

LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID

Resolution

rrs.RESOLUTION

LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID

LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID

OnBehalfOfUser

oboau.FIRST_NAME

LEFT JOIN SDUser obosdu ON wo.OBOID=obosdu.USERID

LEFT JOIN AaaUser oboau ON obosdu.USERID=oboau.USER_ID

Asset name

ci.CINAME

LEFT JOIN CI ci ON wo.CIID=ci.CIID

Responded Date

longtodate(wo.RESPONDEDTIME)

Workorder wo

Dueby time

longtodate(wo.DUEBYTIME)

Workorder wo

Resolved time

longtodate(wo.RESOLVEDTIME)

Workorder wo

Completed time

longtodate(wo.COMPLETEDTIME)

Workorder wo

Response DueBy Time

longtodate(wo.FR_DUETIME)

Workorder wo

Lastupdated time

longtodate(wos.LAST_TECH_UPDATE)

Workorderstates wos

Time elapsed

wo.TIMESPENTONREQ

Workorder wo

Overdue status

wos.ISOVERDUE

Workorderstates wos

First Response Overdue Status

wos.IS_FR_OVERDUE

Workorderstates wos

FCR

wos.ISFCR

Workorderstates wos

Reopened

wos.REOPENED

Workorderstates wos

VIP User

sdu.ISVIPUSER

SDUser sdu

Request Template

reqtl.TEMPLATENAME

LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID

Sla Name

sla.slaname

LEFT JOIN sladefinition sla ON wo.slaid=sla.slaid

Request problem association

pi.PROBLEMID

LEFT JOIN ProblemToIncidentMapping pi ON wo.WORKORDERID=pi.WORKORDERID

Incidents caused by Change

icm.CHANGEID

LEFT JOIN IncidentCausedByChange icm ON chdt.CHANGEID=icm.CHANGEID

Shared to Technician

sa.first_name

LEFT JOIN WO_SHARE_LIST wsl ON wo.workorderid=wsl.workorderid

LEFT JOIN TECHNICIAN_SHARE_LIST tsl ON wsl.shareid=tsl.shareid

LEFT JOIN aaauser sa ON tsl.techid=sa.user_id

Request recipient email

wor.recipient_email

LEFT JOIN workorder_recipients wor ON wo.workorderid=wor.workorderid

Service catalog additional field

sereq.UDF_CHAR1

left join ServiceReq_301 sereq on wo.workorderid=sereq.workorderid

Incident additional field

wof.UDF_CHAR1

left join workorder_fields on wo.workorderid=wof.workorderid

ServiceCatalog common additional field

scf.GUDF_CHAR1

left join Servicecatalog_field scf on wo.workorderid=scf.workorderid

Resource Title

Question

Answer

CatalogResource.TITLE  

Questions.QUESTION  

ResourcesQAMapping.ANSWER

LEFT JOIN ResourcesQAMapping ON WO_Resources.UID=ResourcesQAMapping.MAPPINGID

LEFT JOIN CatalogResource ON wo_resources.RESOURCEID=CatalogResource.UID

LEFT JOIN Questions ON Questions.QUESTIONID=ResourcesQAMapping.QUESTIONID

Time spent

ct.TIMESPENT

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

Time spent Description

ct.DESCRIPTION

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

Time Spent Total_Charge

ct.TOTAL_CHARGE

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

Time Spent Created Time

longtodate(ct.CREATEDTIME)

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

Time Spent Starttime

longtodate(ct.TS_STARTTIME)

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

Time Spent Endtime

longtodate(ct.TS_ENDTIME)

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

Time Spent Other Charge

ct.OTHER_CHARGE

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

Time Spent Tech Charge

ct.TECH_CHARGE

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

Time Spent Created By

rccb.FIRST_NAME

LEFT JOIN AaaUser rccb ON ct.CREATEDBY=rccb.USER_ID

Time Spent Technician

rctd.FIRST_NAME

LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID

LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID

Worklog Type

wtd.NAME

LEFT JOIN WorkLogTypeDefinition wtd ON ct.WORKLOGTYPEID=wtd.WORKLOGTYPEID

Task id

taskdet.TASKID

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Task Title

taskdet.TITLE

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Percentage Of Completion

taskdet.PER_OF_COMPLETION

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Additional Cost

taskdet.ADDTIONAL_COST

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Task Created date

longtodate(taskdet.CREATEDDATE)

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Scheduled Start Time

longtodate(taskdet.SCHEDULEDSTARTTIME)

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Scheduled End Time

longtodate(taskdet.SCHEDULEDENDTIME)

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Actual Start Time

longtodate(taskdet.ACTUALSTARTTIME)

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Actual End Time

longtodate(taskdet.ACTUALENDTIME)

LEFT JOIN TaskDetails taskdet ON wotk.TASKID=taskdet.TASKID

Task created by

taskcreatedby.FIRST_NAME

LEFT JOIN AaaUser taskcreatedby ON taskdet.CREATEDBY=taskcreatedby.USER_ID

Task owner

taskowner.FIRST_NAME

LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID

LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID

Task Priority

taskprior.PRIORITYNAME

LEFT JOIN PriorityDefinition taskprior ON taskdet.PRIORITYID=taskprior.PRIORITYID

Task Status

taskstatus.STATUSNAME

LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID

Task Type

tasktype.TASKTYPENAME

LEFT JOIN TaskTypeDefinition tasktype ON taskdet.TASKTYPEID=tasktype.TASKTYPEID

Task Group

taskgroup.QUEUENAME

LEFT JOIN QueueDefinition taskgroup ON taskdet.GROUPID=taskgroup.QUEUEID



Workorderstates wos

LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 

WorkOrderToCharge wotoc

LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID 

WorkorderToTaskdetails wotk

LEFT JOIN WorkorderToTaskdetails wotk ON wo.WORKORDERID=wotk.WORKORDERID


 

Change Module

 

i. Change Details

 

Column Name

Column Value

Joining Table

Change ID

chdt.CHANGEID

ChangeDetails chdt

Change Subject

chdt.TITLE

ChangeDetails chdt

Change Description

chdt.DESCRIPTION

ChangeDetails chdt

Created Time

LONGTODATE
(chdt.CREATEDTIME)

ChangeDetails chdt

Schedule Start Time

LONGTODATE
(chdt.SCHEDULEDSTARTTIME)

ChangeDetails chdt

Schedule End Time

LONGTODATE
(chdt.SCHEDULEDENDTIME)

ChangeDetails chdt

Completed Time

LONGTODATE
(chdt.COMPLETEDTIME)

ChangeDetails chdt

Change Requester

requester.FIRST_NAME

LEFT JOIN AaaUser requester ON
requester.USER_ID = chdt.INITIATORID

Change Technician

owner.FIRST_NAME

LEFT JOIN AaaUser owner ON
owner.USER_ID = chdt.TECHNICIANID

Change Manager

manager.FIRST_NAME

LEFT JOIN AaaUser manager ON

manager.USER_ID = chdt.CHANGEMANAGERID

Change Type

ctDef.NAME

LEFT JOIN ChangeTypeDefinition ctDef ON  ctDef.CHANGETYPEID = chdt.CHANGETYPEID

Stage

stageDef.NAME

LEFT JOIN Change_StageDefinition stageDef ON stageDef.WFSTAGEID = chdt.WFSTAGEID

Status

statusDef.STATUSNAME

LEFT JOIN Change_StatusDefinition statusDef ON statusDef.WFSTATUSID = chdt.WFSTATUSID

Priority

priDef.PRIORITYNAME

LEFT JOIN PriorityDefinition priDef ON
priDef.PRIORITYID = chdt.PRIORITYID

Urgency

urgDef.NAME

LEFT JOIN UrgencyDefinition urgDef ON
urgDef.URGENCYID = chdt.URGENCYID

Risk

riskDef.NAME

LEFT JOIN RiskDefinition riskDef ON
riskDef.RISKID = chdt.RISKID

Impact

impDef.NAME

LEFT JOIN ImpactDefinition impDef ON
impDef.IMPACTID = chdt.IMPACTID

Change Template

temp.NAME

LEFT JOIN ChangeTemplate temp ON
temp.TEMPLATEID = chdt.TEMPLATEID

Change Workflow

wf.NAME

LEFT JOIN ChangeWF_Definition wf ON
wf.ID = chdt.WFID

Site

sdOrg.NAME

LEFT JOIN SdOrganization sdOrg ON
sdOrg.ORG_ID = chdt.SITEID

Group

QueueDef.QUEUENAME

LEFT JOIN QueueDefinition queueDef ON
queueDef.QUEUEID = chdt.GROUPID

Approval Status

appr.STATUSNAME

LEFT JOIN ApprovalStatusDefinition appr ON
appr.STATUSID = chdt.APPR_STATUSID

Category

catDef.CATEGORYNAME

LEFT JOIN CategoryDefinition catDef ON  catDef.CATEGORYID = chdt.CATEGORYID

Subcategory

subDef.NAME

LEFT JOIN SubCategoryDefinition subDef ON  subDef.SUBCATEGORYID = chdt.SUBCATEGORYID

Item

itemDef.NAME

LEFT JOIN ItemDefinition itemDef ON

itemDef.ITEMID = chdt.ITEMID

Reason For Change

rfc.NAME

LEFT JOIN ReasonForChangeDetails rfc ON

rfc.ID = chdt.REASONFORCHANGEID

Overdue

chdt.ISOVERDUE

ChangeDetails chdt

Emergency

chdt.ISEMERGENCY

ChangeDetails chdt

Retrospective

chdt.ISRETROSPECTIVE

ChangeDetails chdt

Full Description

ctd.FULL_DESCRIPTION

LEFT JOIN ChangeToDescription ctd ON

ctd.CHANGEID = chdt.CHANGEID

Services Affected

servDef.NAME

LEFT JOIN ChangeToService cserv ON

cserv.CHANGEID = chdt.CHANGEID
LEFT JOIN ServiceDefinition servDef ON

servDef.SERVICEID = cserv.SERVICEID

Assets Involved

ci.CINAME

LEFT JOIN ChangeToCI ccharge ON

ccharge.CHANGEID = chdt.CHANGEID
LEFT JOIN CI ON

ci.CIID = ccharge.CIID

 

 

 

 

 

 

 

ii. Change Task

 

Mandatory Joining Table

 

LEFT JOIN ChangeToTaskDetails ctt ON

ctt.CHANGEID = chdt.CHANGEID

LEFT JOIN TaskDetails td ON
td.TASKID =ctt.TASKID

 

 

Column Name

Column Value

Additional Joining Table

Task Subject

td.TITLE

 

Task Created By

tReq.FIRST_NAME

LEFT JOIN AaaUser tReq ON
tReq.USER_ID = td.CREATEDBY

Task Owner

tOwn.FIRST_NAME

LEFT JOIN AaaUser tOwn ON
tOwn.USER_ID = td.OWNERID

Task Site

tOrg.NAME

LEFT JOIN SdOrganization tOrg ON
tOrg.ORG_ID = td.SITEID

Task Group

tQueue.QUEUENAME

LEFT JOIN QueueDefinition tQueue ON
tQueue.QUEUEID = td.GROUPID

Task Priority

tPri.PRIORITYNAME

LEFT JOIN PriorityDefinition tPri ON
tPri.PRIORITYID = td.PRIORITYID

Task Status

tStatus.STATUSNAME

LEFT JOIN StatusDefinition tStatus ON
tStatus.STATUSID = td.STATUSID

Task Type

ttDef.TASKTYPENAME

LEFT JOIN TasktypeDefinition ttDef ON
ttDef.TASKTYPEID = td.TASKTYPEID

Estimated Effort

td.ESTIMATEDEFFORT

 

Additional Cost

td.ADDTIONAL_COST

 

Task Schedule Start Time

LONGTODATE
(td.SCHEDULEDSTARTTIME)

 

Task Schedule End Time

LONGTODATE
(td.SCHEDULEDENDTIME)

 

Task Actual Start Time

LONGTODATE
(td.ACTUALSTARTTIME)

 

Task Actual End Time

LONGTODATE
(td.ACTUALENDTIME)

 

 

 

 

 

iii. Change Downtime

 

Mandatory Joining Table

 

LEFT JOIN Change_DowntimeDetails cdd ON
cdd.CHANGEID = chdt.CHANGEID

 

 

Column Name

Column Value

Additional Joining Table

Downtime Description

cdd.DESCRIPTION

 

Downtime Start Time

cdd.STARTTIME

 

Downtime End Time

cdd.ENDTIME

 

 

 

 

iv. Change Worklog

 

Mandatory Joining Table

 

LEFT JOIN ChangeToCharge ctc ON 

ctc.CHANGEID = chdt.CHANGEID

LEFT JOIN ChargesTable ct ON 

ct.CHARGEID = ctc.CHARGEID

 

 

Column Name

Column Value

Additional Joining Table

Worklog Created By

wc.FIRST_NAME

LEFT JOIN AaaUser wc ON
wc.USER_ID = ct.CREATEDBY

Worklog Owner

wt.FIRST_NAME

LEFT JOIN AaaUser wt ON
wt.USER_ID = ct.TECHNICIANID

Worklog Type

wtd.NAME

LEFT JOIN WorklogTypeDefinition wtd ON
wtd.WORKLOGTYPEID = ct. WORKLOGTYPEID

Worklog Description

ct.DESCRIPTION

 

Tech Charge

ct.TECH_CHARGE

 

Other Charge

ct.OTHER_CHARGE

 

Total Charge

ct.TOTAL_CHARGE

 

Worklog Created Time

LONGTODATE
(ct.CREATEDTIME)

 

Worklog Start Time

LONGTODATE
(ct.TS_STARTTIME)

 

Worklog  End Time

LONGTODATE
(ct.TS_ENDTIME)

 

Time take to resolve

LONGTODATE
(ct.TIMESPENT)

 


v. Planning/Review/Close

 

Mandatory Joining Table 

 

LEFT JOIN ChangeDefaultFields cdf ON 

cdf.CHANGEID = chdt.CHANGEID

 

 

Column Name

Column Value

Additional Joining Table

Rolloutplan

roll.CONTENT

LEFT JOIN ChangeDescriptiveField roll ON

roll.ID = cdf.ROLLOUTPLAN

Rolloutplan On

LONGTODATE
(roll.UPDATED_ON)

 

Rolloutplan By

rollby.FIRST_NAME

LEFT JOIN AaaUser rollby ON

rollby.USER_ID = roll.UPDATED_BY

Backoutplan

back.CONTENT

LEFT JOIN ChangeDescriptiveField back ON

back.ID = cdf.BACKOUTPLAN

Backoutplan On

LONGTODATE
(back.UPDATED_ON)

 

Backoutplan By

backby.FIRST_NAME

LEFT JOIN AaaUser backby ON

backby.USER_ID = back.UPDATED_BY

Checklist

chk.CONTENT

LEFT JOIN ChangeDescriptiveField chk ON

chk.ID = cdf.CHECKLIST

Checklist On

LONGTODATE
(chk.UPDATED_ON)

 

Checklist By

chkby.FIRST_NAME

LEFT JOIN AaaUser chkby ON

chkby.USER_ID = chk.UPDATED_BY

Impact Description

imp.CONTENT

LEFT JOIN ChangeDescriptiveField imp ON

imp.ID = cdf.IMPACTDESC

Impact Description On

LONGTODATE
(imp.UPDATED_ON)

 

Impact Description By

impby.FIRST_NAME

LEFT JOIN AaaUser impby ON

impby.USER_ID = imp.UPDATED_BY

Review Description

rev.CONTENT

LEFT JOIN ChangeDescriptiveField rev ON

rev.ID = cdf.REVIEW

Review On

LONGTODATE
(rev.UPDATED_ON)

 

Review By

revby.FIRST_NAME

LEFT JOIN AaaUser revby ON

revby.USER_ID = rev.UPDATED_BY

Next Review On

LONGTODATE
(chdt.NEXT_REVIEW_ON)

 

Closure Comment

close.CONTENT

LEFT JOIN ChangeDescriptiveField close ON

close.ID = cdf.CLOSEDESC

Closed On

close.UPDATED_ON

 

Closed By

closeby.FIRST_NAME

LEFT JOIN AaaUser closeby ON

closeby.USER_ID = close.UPDATED_BY

Closure Code

ccode.NAME

LEFT JOIN Change_ClosureCode ccode ON

ccode.ID = chdt.CLOSURECODEID

 

 

vi. Change Status Comments

 

Mandatory Joining Table 

 

LEFT JOIN ChangeStatusComments csc ON 

csc.CHANGEID = chdt.CHANGEID

 


Column Name

Column Value

Additional Joining Table

Status Comments

csc.COMMENTS

 

Commented On

LONGTODATE

(csc.COMMENTEDON)

 

Commented By

comm.FIRST_NAME

LEFT JOIN AaaUser comm ON

comm.USER_ID = csc.COMMENTEDBY

 

 

 

vii. Additional Fields

 

 

Column Value

Joining Table

UDF_CHAR1

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR2

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR3

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR4

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR5

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR6

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR7

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR8

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR9

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR10

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR11

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_CHAR12

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_LONG1

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_LONG2

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_LONG3

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_LONG4

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_DATE1

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_DATE2

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_DATE3

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

UDF_DATE4

LEFT JOIN Change_Fields cf ON

cf.CHANGEID = chdt.CHANGEID

 

 

vii. Approvals


Joining Condition

 

<Stage Name> should be replaced with respective stage names(Submission, Plainning, Approval, Implementation, Review, Close)

 

 

 

Column Name

Column Value

Additional Joining Table



Approver Name

appr.FIRST_NAME

LEFT JOIN Change_ApprovalLevel cal ON
( cal.ENTITYID = chdt.CHANGEID

AND

cal.STAGEID = ( SELECT WFSTAGEID FROM Change_StageDefinition WHERE NAME = '<Stage_Name>' ) )

LEFT JOIN ApprovalLevel al ON

al.ID = cal.LEVELID

LEFT JOIN ApprovalDetails ad ON

ad.APPROVAL_LEVEL_ID = al.ID

LEFT JOIN AaaUser appr ON

appr.USER_ID = ad.APPROVERID



Approver Email

ad.EMAIL

LEFT JOIN Change_ApprovalLevel cal ON
( cal.ENTITYID = chdt.CHANGEID

AND

cal.STAGEID = ( SELECT WFSTAGEID FROM Change_StageDefinition WHERE NAME = '<Stage_Name>' ) )

LEFT JOIN ApprovalLevel al ON

al.ID = cal.LEVELID

LEFT JOIN ApprovalDetails ad ON

ad.APPROVAL_LEVEL_ID = al.ID

LEFT JOIN AaaUser appr ON

appr.USER_ID = ad.APPROVERID



Approval Comments

ad.COMMENTS

LEFT JOIN Change_ApprovalLevel cal ON
( cal.ENTITYID = chdt.CHANGEID

AND

cal.STAGEID = ( SELECT WFSTAGEID FROM Change_StageDefinition WHERE NAME = '<Stage_Name>' ) )

LEFT JOIN ApprovalLevel al ON

al.ID = cal.LEVELID

LEFT JOIN ApprovalDetails ad ON

ad.APPROVAL_LEVEL_ID = al.ID

LEFT JOIN AaaUser appr ON

appr.USER_ID = ad.APPROVERID



Approval Level

al.LEVEL

LEFT JOIN Change_ApprovalLevel cal ON
( cal.ENTITYID = chdt.CHANGEID

AND

cal.STAGEID = ( SELECT WFSTAGEID FROM Change_StageDefinition WHERE NAME = '<Stage_Name>' ) )

LEFT JOIN ApprovalLevel al ON

al.ID = cal.LEVELID

LEFT JOIN ApprovalDetails ad ON

ad.APPROVAL_LEVEL_ID = al.ID

LEFT JOIN AaaUser appr ON

appr.USER_ID = ad.APPROVERID



 

 

 

Change Association

 

Request Module

 

i. Change Inititated due to Requests

 

LEFT JOIN IncidentToChangeMapping ctReq ON ctReq.CHANGEID = chdt.CHANGEID 

LEFT JOIN WorkOrder woi ON woi.WORKORDERID = ctReq.WORKORDERID 

 

ii. Requested caused due to this change

 

LEFT JOIN IncidentCausedByChange cdReq ON cdReq.CHANGEID = chdt.CHANGEID 

LEFT JOIN WorkOrder wod ON wod.WORKORDERID = cdReq.WORKORDERID 

 

Problem Module

 

LEFT JOIN ProblemToChangeMapping ctProb ON ctProb.CHANGEID = chdt.CHANGEID

LEFT JOIN Problem prob ON prob.PROBLEMID = ctProb.PROBLEMID

 

Project Module

 

LEFT JOIN ChangeToProjects ctProj ON ctProj.CHANGEID = chdt.CHANGEID

LEFT JOIN ProjectDetails proj ON proj.PROJECTID = ctProj.PROJECTID




Solution Module



You can change the machine name and the port in the above link.

 

Column Name

Column Value

Joining Table

Key words

Solution_Keywords.keyword

LEFT JOIN Solution_Keywords ON Solution.SOLUTIONID=Solution_Keywords.SOLUTIONID




Problem Module




You can change the machine name and the port in the above link.

 

Column Name

Column Value

Joining Table

Additional fields

probf.udf_char1

LEFT JOIN Problem_Fields probf ON prob.PROBLEMID=probf.PROBLEMID



Contract Module




You can change the machine name and the port in the above link.


 

Column Name

Column Value

Joining Table

Additional fields

cadf.udf_char1

LEFT JOIN Contract_Fields cadf ON mcdt.CONTRACTID=cadf.CONTRACTID





Project Module




You can change the machine name and the port in the above link.


 

Column Name

Column Value

Joining Table

Additional fields

proff.udf_char1

LEFT JOIN Project_Fields prof ON mcdt.CONTRACTID=prof.CONTRACTID

Comments

co.comments

Left join projecttocomment c on c.projectid = projectdet.projectid

left join comments co on c.commentid=co.commentid 





Purchase Module





You can change the machine name and the port in the above link.

 

Column Name

Column Value

Joining Table

Additional fields

poaddfield.udf_char1

LEFT JOIN PurchaseRequest_Fields poaddfield ON pr.requestid=poaddfield.requestid 






Software Module



 

Column Name

Column Value

Joining Table

Additional fields

slf.udf_char1

LEFT JOIN SoftwareLicense_Fields slf ON resource.resourceid=slf.LICENSEID

 
        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

                      • How to change the database from MySQL/PostgreSQL to MSSQL

                        The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB.    For ...

                      • Failed to start the database

                        For the error "Failed to start the database", Check what database being used and, execute changeDBServer.bat. For Pgsql,  Start PGSQL from the command link, execute StartDB.bat 65432 under C:\Manageengine\ServiceDesk\bin 65432 is the default port ...

                      • How to connect to the AssetExplorer database?

                        To find the database connected to your installation, open Command Prompt with elevated permission, navigate to [AE-Home]\bin directory and invoke the batch file 'changeDBServer.bat'. [AE-Home] is Drive:\ManageEngine\AssetExplorer\ directory. ...

                      • How to connect to the ServiceDesk database?

                        Open command prompt on the server and change the directory to MangeEngine\ServiceDesk\bin, invoke the batch file 'changeDBServer.bat' For MSSQL database  Connect to the query analyzer of the SQL server and execute the queries. For PostgreSQL database ...

                      • Remote read only access to database for Postgres customers

                        Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...

                        Related Products