WITH RESOLUTIONS as
(select resolution.a_ouid, docBase.A_CREATEDATE from SMEV_RESOLUTION resolution
  LEFT JOIN SMEV_DOC_BASE docBase
    on resolution.a_ouid = docBase.a_ouid AND docBase.a_systemclass = (select ouid from sxclass where name = 'smevResolution')
)
SELECT
  petBase.A_OUID AS "ouid",
  srv.OUID AS "service",
  srv.A_FEDCODE AS "servicefedcode",
  srv.A_NAME AS "servicename",
  stat.A_TITLE AS "state",
  petBase.A_START_DATE AS "startdate",
  coalesce(resolution.A_CREATEDATE, petBase.A_PROCESS_DATE) AS "workenddate",
  orgBase.OUID AS "recipientorg",
  orgBase.A_NAME1 AS "recipientorgname",
  egOrg.A_RGU_CODE AS "recipientorgrgucode",
  sprOrgBase.OUID AS "mfc",
  sprOrgBase.A_NAME1 AS "mfcname",
  (empl.LAST_NAME + ' ' + name.A_NAME + ' ' + secondName.A_NAME) AS "operator",
  petBase.A_LOGIN AS "personalcard",
  petBase.A_NUMBER AS "number",
  petBase.A_CODE_ACTIVATE AS "codeactivate",
  reg.A_OUID AS "smevreglament",
  reg.A_NAME AS "smevreglamentname",
  reg.A_FED_CODE AS "smevreglamentfedcode",
  petBase.A_HAS_RATING AS "hasrating",
  mfcLogReady.ready,
  coalesce(mfcLogReady.ready, false) AS "readyforeval",
  petBase.guid as "petitionguid",
  mfcOrg.a_mkgu_id as "mkguid",
  --case when srv.A_TERR_INDIC = 'f' then 1 else 0 end as "federal"
  1 as "federal",
 srv.A_TERR_INDIC as "srvlevel"

FROM SMEV_PETBASE petBase
  left join SMEV_SERVICE srv ON srv.OUID = petBase.A_SERVICE
  left join SMEV_PETITION_STATUS stat ON stat.A_OUID = petBase.A_STATE
  left join SPR_ORG_BASE orgBase ON orgBase.OUID = petBase.A_RECIP_ORG
  left join EG_ORGANIZATION egOrg ON egOrg.OUID = orgBase.OUID

  left JOIN EG_ORGANIZATION mfcOrg on mfcOrg.ouid = petBase.a_mfc
  left join SD_POSITION pos ON pos.OUID = petBase.A_OPERATOR
  left join SD_EMPLOYEE empl ON empl.OUID = pos.A_EMPLOYEE

LEFT JOIN (SELECT MAX(a_ouid), 1 AS ready, a_id_reg_num_obj, A_OBJECT,A_MFC FROM MFC_LOGJOB
  WHERE A_CODE_PETITION IN (SELECT A_OUID FROM SMEV_PETITION_STATUS WHERE A_READY_FOR_EVAL = 1)
  GROUP BY a_id_reg_num_obj, A_OBJECT,A_MFC) mfcLogJob ON petBase.A_OUID = mfcLogJob.a_id_reg_num_obj
                                                          AND mfcLogJob.A_OBJECT = petBase.A_SYSTEMCLASS

  left join SPR_ORG_BASE sprOrgBase ON sprOrgBase.OUID = mfcLogReady.A_MFC
  left join SPR_FIO_NAME name ON name.OUID = empl.FIRST_NAME
  left join SPR_FIO_SECONDNAME secondName ON secondName.OUID = empl.MIDDLE_NAME
  left join SMEV_REGLAMENT reg ON reg.A_OUID = srv.A_REGLAMENT
  LEFT JOIN RESOLUTIONS resolution ON petBase.A_RESOLUTION = resolution.a_ouid
WHERE mfcLogReady.ready is true