TRUNCATE TABLE APP_CACHE_VIEW;

SET @DEFAULT_LANG = '{lang}';

INSERT INTO APP_CACHE_VIEW (
    APP_UID,
    DEL_INDEX,
    APP_NUMBER,
    APP_STATUS,
    USR_UID,
    PREVIOUS_USR_UID,
    TAS_UID,
    PRO_UID,
    DEL_DELEGATE_DATE,
    DEL_INIT_DATE,
    DEL_FINISH_DATE,
    DEL_TASK_DUE_DATE,
    DEL_RISK_DATE,
    DEL_THREAD_STATUS,
    APP_THREAD_STATUS,
    APP_TITLE,
    APP_PRO_TITLE,
    APP_TAS_TITLE,
    APP_CURRENT_USER,
    APP_DEL_PREVIOUS_USER,
    DEL_PRIORITY,
    DEL_DURATION,
    DEL_QUEUE_DURATION,
    DEL_DELAY_DURATION,
    DEL_STARTED,
    DEL_FINISHED,
    DEL_DELAYED,
    APP_CREATE_DATE,
    APP_FINISH_DATE,
    APP_UPDATE_DATE,
    APP_OVERDUE_PERCENTAGE,
    DEL_LAST_INDEX
)

SELECT
  APPLICATION.APP_UID,
  APP_DELEGATION.DEL_INDEX,
  APPLICATION.APP_NUMBER,
  APPLICATION.APP_STATUS,
  APP_DELEGATION.USR_UID,
  IF (
      APP_DELEGATION.DEL_PREVIOUS = 0 AND (SELECT SUBAPP.APP_UID FROM SUB_APPLICATION AS SUBAPP WHERE SUBAPP.APP_UID = APP_DELEGATION.APP_UID LIMIT 1) IS NOT NULL,
      (SELECT APPDEL2.USR_UID
       FROM   SUB_APPLICATION AS SUBAPP, APP_DELEGATION AS APPDEL1, APP_DELEGATION AS APPDEL2
       WHERE  SUBAPP.APP_UID = APP_DELEGATION.APP_UID AND
              SUBAPP.APP_PARENT = APPDEL1.APP_UID AND SUBAPP.DEL_INDEX_PARENT = APPDEL1.DEL_INDEX AND
              APPDEL1.APP_UID = APPDEL2.APP_UID AND APPDEL1.DEL_PREVIOUS = APPDEL2.DEL_INDEX
       LIMIT 1
      ),
      APP_LAST_USER.USR_UID
  ) AS PREVIOUS_USR_UID,
  APP_DELEGATION.TAS_UID,
  APP_DELEGATION.PRO_UID,
  substring(APP_DELEGATION.DEL_DELEGATE_DATE,1,19),
  substring(APP_DELEGATION.DEL_INIT_DATE,1,19),
  substring(APP_DELEGATION.DEL_FINISH_DATE,1,19),
  substring(APP_DELEGATION.DEL_TASK_DUE_DATE,1,19),
  substring(APP_DELEGATION.DEL_RISK_DATE,1,19),
  APP_DELEGATION.DEL_THREAD_STATUS,
  APP_THREAD.APP_THREAD_STATUS,
  APP_TITLE.CON_VALUE AS APP_TITLE,
  PRO_TITLE.CON_VALUE AS APP_PRO_TITLE,
  TAS_TITLE.CON_VALUE AS APP_TAS_TITLE,
  CONCAT(USERS.USR_LASTNAME,  ' ',  USERS.USR_FIRSTNAME) AS APP_CURRENT_USER,
  CONCAT(APP_LAST_USER.USR_LASTNAME,  ' ',  APP_LAST_USER.USR_FIRSTNAME) AS APP_DEL_PREVIOUS_USER,
  APP_DELEGATION.DEL_PRIORITY,
  APP_DELEGATION.DEL_DURATION,
  APP_DELEGATION.DEL_QUEUE_DURATION,
  APP_DELEGATION.DEL_DELAY_DURATION,
  if( APP_DELEGATION.DEL_INIT_DATE IS NULL   , 0 , 1 ),
  if( APP_DELEGATION.DEL_FINISH_DATE IS NULL , 0 , 1 ),
  APP_DELEGATION.DEL_DELAYED,
  APPLICATION.APP_CREATE_DATE,
  IF (APPLICATION.APP_STATUS = 'COMPLETED', APPLICATION.APP_FINISH_DATE, NULL),
  APPLICATION.APP_UPDATE_DATE,
  APP_DELEGATION.APP_OVERDUE_PERCENTAGE,
  APP_DELEGATION.DEL_LAST_INDEX
FROM
  APPLICATION FORCE INDEX (PRIMARY)
  LEFT JOIN APP_DELEGATION ON (APPLICATION.APP_UID=APP_DELEGATION.APP_UID)
  LEFT JOIN TASK ON (APP_DELEGATION.TAS_UID=TASK.TAS_UID)
  LEFT JOIN USERS ON (APP_DELEGATION.USR_UID=USERS.USR_UID)
  LEFT JOIN APP_THREAD ON (APPLICATION.APP_UID=APP_THREAD.APP_UID AND APP_DELEGATION.DEL_THREAD=APP_THREAD.APP_THREAD_INDEX)
  LEFT JOIN CONTENT APP_TITLE FORCE INDEX (indexUid) ON (APPLICATION.APP_UID=APP_TITLE.CON_ID AND APP_TITLE.CON_CATEGORY='APP_TITLE' AND APP_TITLE.CON_LANG = @DEFAULT_LANG)
  LEFT JOIN CONTENT PRO_TITLE FORCE INDEX (indexUid) ON (APPLICATION.PRO_UID=PRO_TITLE.CON_ID AND PRO_TITLE.CON_CATEGORY='PRO_TITLE' AND PRO_TITLE.CON_LANG = @DEFAULT_LANG)
  LEFT JOIN CONTENT TAS_TITLE FORCE INDEX (indexUid) ON (APP_DELEGATION.TAS_UID=TAS_TITLE.CON_ID AND TAS_TITLE.CON_CATEGORY='TAS_TITLE' AND TAS_TITLE.CON_LANG = @DEFAULT_LANG)
  LEFT JOIN APP_DELEGATION APP_PREV_DEL ON (APPLICATION.APP_UID=APP_PREV_DEL.APP_UID AND APP_PREV_DEL.DEL_INDEX=APP_DELEGATION.DEL_PREVIOUS)
  LEFT JOIN USERS APP_LAST_USER ON (APP_PREV_DEL.USR_UID=APP_LAST_USER.USR_UID)
WHERE
  TASK.TAS_TYPE<>'SUBPROCESS'
