We are using Classic Workflows for our Change Management. Now in some instances we have seen the the workflow auto navigation from wait to pending is not working in 100% case. The simple solution is to take the CO in edit mode and save them again, it triggers the workflows which are stuck in WAIT status and suppose to move to PENDING status. Now we need a mechanism or preferably a SQL query to filter out the active Change Order numbers(chg.chg_ref_num) where no workflow exists with PENDING status. Can someone help me with a sample query or mechanism?
I started with the following but need some help to impose the filter condition. The following query gives me the result of active CO numbers having WAIT and PENDING status workflow. I need to get the CO numbers having only WAIT status workflow but no PENDING status workflow. Any suggestion will be great:
select chg.chg_ref_num,wf.sequence,wf.status from chg left join wf ON chg.id=wf.object_id where
chg.status NOT IN('CL','CNCL','BACK','CNCLRESCH','REJ') and
chg.active_flag=1 and chg.open_date>1483228800
and wf.status IN ('PEND','WAIT')