Workflow and Escalation migration where clause

--Query to get the Syncronised processnames.

select processrev,processname,mainprocess from maximo.wfrevision  where mainprocess =  'ITDCHGSAVE'  and revision =(select max(revision) from maximo.wfrevision where mainprocess =  'ITDCHGSAVE' ) order by processname for read only

--Communication template used in workflow

select templateid from maximo.commtemplate where templateid in (select templateid from maximo.wfnotification  where (processname,processrev) in (select processname,processrev  from maximo.wfrevision  where mainprocess =  'PMCHGITLV3'  and revision =(select max(revision) from maximo.wfrevision where mainprocess =  'PMCHGITLV3' ) order by processname)) or templateid in (select templateid from maximo.ESCNOTIFICATION where escalation in ('ITDCHGRAUTO','ITDCHAPRGRCOMP'))

--Action and Action groups in the workflow.

select action from maximo.action where action in (select action from maximo.wfaction where (processname,processrev) in (select processname,processrev  from maximo.wfrevision  where mainprocess =  'PMCHGITLV3'  and revision =(select max(revision) from maximo.wfrevision where mainprocess =  'PMCHGITLV3' ) order by processname)) or action in (select action from maximo.ESCREFPOINT where escalation in ('ITDCHGRAUTO','ITDCHAPRGRCOMP'));



--Role from maximo.workflows.

Select maxrole from maximo.maxrole where maxrole in (select sendtovalue from maximo.COMMTMPLTSENDTO where type='ROLE' and templateid in (select templateid from maximo.wfnotification  where (processname,processrev) in (select processname,processrev  from maximo.wfrevision  where mainprocess =  'PMCHGITLV3'  and revision =(select max(revision) from maximo.wfrevision where mainprocess =  'PMCHGITLV3' ) order by processname)) or templateid in (select templateid from maximo.ESCNOTIFICATION where escalation in ('ITDCHGRAUTO','ITDCHAPRGRCOMP'))) or maxrole in (select roleid from maximo.WFASSIGNMENT  where (processname,processrev) in (select processname,processrev  from maximo.wfrevision  where mainprocess =  'PMCHGITLV3'  and revision =(select max(revision) from maximo.wfrevision where mainprocess =  'PMCHGITLV3' ) order by processname));