CREATE PROCEDURE sp_sql_pln_rprt_sel (
  in_action_id INTEGER)
RETURNS (
  out_id INTEGER,
  out_sys_id INTEGER,
  out_pln_frq_id INTEGER,
  out_pln_sys_id INTEGER,
  out_pln_type_id INTEGER,
  out_pln_type_sys_id SMALLINT,
  out_rc_date DATE,
  out_rc_time TIME,
  out_rc_duration NUMERIC(18, 0),
  out_usr_id SMALLINT,
  out_usr_sys_id SMALLINT,
  out_usr_name VARCHAR(98),
  out_remark BLOB SUB_TYPE 1 SEGMENT SIZE 512)
AS
  DECLARE VARIABLE var_pln_id INTEGER;
  DECLARE VARIABLE var_old_pln_id INTEGER;
  DECLARE VARIABLE var_old_pln_sys_id SMALLINT;
  DECLARE VARIABLE var_last_control TIMESTAMP;
  DECLARE VARIABLE var_usr_status VARCHAR(6);
BEGIN
  out_usr_sys_id = 0;
  var_old_pln_id = -1;
  var_old_pln_sys_id = -1;
  FOR
    SELECT
      rep."PLN_FRQ_ID",
      rep."SYS_ID",
      rep."PLN_ID",
      pln."PLN_TYPE_ID",
      pln."PLN_TYPE_SYS_ID",
      rep."DURATION",
      rep."REMARK",
      rep."USR_ID"
    FROM
      plans pln
      LEFT JOIN plan_reports rep ON (
        (pln."ID" = rep."PLN_ID") AND
        (pln."SYS_ID" = rep."SYS_ID")
      )
    WHERE
      pln."STATUS" = 4 AND  /* psExported */
      pln."ACTION_ID" = :in_action_id
    ORDER BY
      pln."SYS_ID",
      pln."ID"
    INTO
      :out_pln_frq_id,
      :out_pln_sys_id,
      :var_pln_id,
      :out_pln_type_id,
      :out_pln_type_sys_id,
      :out_rc_duration,
      :out_remark,
      :out_usr_id
  DO BEGIN
    IF (
      (:var_old_pln_id <> :var_pln_id) AND
      (:var_old_pln_sys_id <> :out_pln_sys_id)
    ) THEN BEGIN
      var_old_pln_id = :var_pln_id;
      var_old_pln_sys_id = :out_pln_sys_id;
      SELECT
        COALESCE(MAX(ets."DATE_TIME"), CURRENT_TIMESTAMP)
      FROM
        tasks tsk
        LEFT JOIN exec_tasks ets ON (
          ets."TSK_ID" = tsk."ID" AND
          ets."TSK_SYS_ID" = tsk."SYS_ID" AND
          ets."STATUS" = 3
        )
      WHERE
        tsk."PLN_ID" = :var_pln_id AND
        tsk."PLN_SYS_ID" = :out_pln_sys_id
      INTO
        :var_last_control;
      out_rc_date = CAST(:var_last_control AS DATE);
      out_rc_time = CAST(:var_last_control AS TIME);
    END
    out_id = :out_pln_frq_id;
    out_sys_id = :out_pln_sys_id;
    EXECUTE PROCEDURE sp_i_usr_get_full_name(
      :out_usr_id
    ) RETURNING_VALUES(
      :var_usr_status,
      :out_usr_name
    );
    SUSPEND;
  END
END

CREATE PROCEDURE sp_i_usr_get_full_name (
  in_id SMALLINT)
RETURNS (
  out_status VARCHAR(6),
  out_name VARCHAR(98))
AS
  DECLARE VARIABLE var_first_name VARCHAR(33);
  DECLARE VARIABLE var_last_name VARCHAR(33);
  DECLARE VARIABLE var_second_name VARCHAR(32);
BEGIN
  SELECT
    usr."STATUS",
    COALESCE(usr."LAST_NAME" || ' ', ''),
    COALESCE(usr."FIRST_NAME" || ' ', ''),
    COALESCE(usr."SECOND_NAME", '')
  FROM
    users usr
  WHERE
    usr."ID" = :in_id
  INTO
    :out_status,
    :var_last_name,
    :var_first_name,
    :var_second_name;
  out_name = :var_last_name || :var_first_name || :var_second_name;
END