select
  zmch.id zmch_id,
  zmch.reg_num,
  zmch.reg_date zmch_reg_date,
  zmch.source_org_name,
  zmch.product_name,
  zmch.ANS_DATE,
  ismzamech_conv(zmch.id, 'cctg') ctg,
  ismzamech_conv(zmch.id, 'stxt') stext_stext
from
  ISMZAMECH_ZAMECH zmch
where
  to_char(zmch.reg_date,'YYYY') like :P1_YEAR
  AND
  (
    :P1_STEXT is null
    OR
    (
      :P1_STEXT is not null
      AND EXISTS
      (
        SELECT
          1
        FROM
          ISMZAMECH_ZMS zms
        WHERE
          zms.zm_id=zmch.id
          AND zms.st_id=:P1_STEXT
      )
    )
  )
  and
  (
    (
      :P1_STATUS is null or :P1_STATUS=0
    )
    or
    (
      :P1_STATUS=1
      and exists (select 1 from ismzamech_zmcc c where c.zm_id=zmch.id and (c.cc_id=13 OR c.cc_id=25))
    )
    or
    (
      :P1_STATUS=2
      and NOT exists (select 1 from ismzamech_zmcc c where c.zm_id=zmch.id and (c.cc_id=13 OR c.cc_id=25))
      and zmch.ANS_DATE is null
    )
    or
    (
      :P1_STATUS=3
      and NOT exists (select 1 from ismzamech_zmcc c where c.zm_id=zmch.id and (c.cc_id=13 OR c.cc_id=25))
      and zmch.ANS_DATE is not null
    )
  )
  and
  (
    :p1_dept is null
    or
    (
      :p1_dept is not null
      and zmch.RESP_DEPT=
      (
        select
          dept.id
        from
          hr_dept0 dept
        where
          dept.name like :p1_dept
      )
    )
  )
  and
  (
    :P1_PRODUCT_NAME is null
    or
    (
      :P1_PRODUCT_NAME is not null
      and zmch.PRODUCT_NAME like :P1_PRODUCT_NAME
    )
  )

CREATE OR REPLACE FUNCTION ismzamech_conv(zmch_id number, fnt varchar2)
RETURN
  clob
AS
  outrec clob:='';
  numrows number:=0;
BEGIN
----------------------
  IF fnt='stxt'
  THEN
    FOR i IN
      (
        SELECT
          st.stext
        FROM
          ismzamech_zms zms,
          ismzamech_stexts st
        WHERE
          zms.zm_id=zmch_id
          AND zms.st_id=st.id
      )
    LOOP
      IF length(outrec)>0 THEN outrec := outrec || ';<BR> ' || i.stext;
      ELSE outrec := i.stext;
      END IF;
    END LOOP;
----------------------
  ELSIF fnt='cctg'
  THEN
    FOR i IN
      (
        SELECT
          c.text
        FROM
          ismzamech_zmcc zmcc,
          ismzamech_causes c
        WHERE
          zmcc.zm_id=zmch_id
          AND zmcc.cc_id=c.id
      )
    LOOP
      IF length(outrec)>0 THEN outrec := outrec || ';<BR> ' || i.text;
      ELSE outrec := i.text;
      END IF;
    END LOOP;
----------------------
  ELSIF fnt='rd'
  THEN
    FOR i IN
      (
        SELECT
          case when dpt.dept_lev<=1 then dpt.name else st_n.deptname(dpt.id,6)||' / '||dpt.name end text
        FROM
          ismzamech_zmrd zmrd,
          hr_dept0 dpt
        WHERE
          zmrd.zm_id=zmch_id
          AND zmrd.rd_id=dpt.id
      )
    LOOP
      IF length(outrec)>0 THEN outrec := outrec || ';<BR> ' || i.text;
      ELSE outrec := i.text;
      END IF;
    END LOOP;
----------------------
  ELSIF fnt='corrlist'
  THEN
    BEGIN
      FOR i IN
        (
          select
            CORR_TODO,
            (
              select
                pr_n.fname(cardid => card.id)
                || ' ('
                || emp.tab_n
                || ' | '
                || st_n.deptname(trans.DEPT_ID,10)
                || ' | '
                || st_n.appname(trans.APPOINT_ID,0)
                || ')'
              from
                pr_card0 card,
                pr_emp0 emp,
                pr_trans0 trans
              where
                emp.card_id=card.id
                and trans.emp_id=emp.id
                and trans.tp_abbr in ('CUR','RES')
                and trans.subst_emp_id is null
                and trans.subst_staff_id is null
                and trans.d_to=to_date('31.12.2099','dd.mm.yyyy')
                and emp.id=CORR_INCHARGE
            ) incharge,
            CORR_TERMS,
            CORR_DONE,
            CORR_RES_STAT,
            CORR_EFF_EVAL_PERIOD
          from
            ISMZAMECH_CORRECTIONS
          where
            zamech_id=zmch_id
          
         /* SELECT
            zmcr.corr_todo text
          FROM
            ismzamech_corrections zmcr
          WHERE
            zmcr.zamech_id=zmch_id*/
        )
      LOOP
        numrows:=numrows+1;
        outrec := outrec || '<tr class="highlight-row"><td headers="CORR_TODO" class="t20data">'||i.corr_todo||'</td><td headers="INCHARGE" class="t20data">'||nvl(i.incharge,'-')||'</td><td headers="CORR_TERMS" class="t20data">'||nvl(i.corr_terms,'-')||'</td><td headers="CORR_DONE" class="t20data">'||nvl(i.corr_done,'-')||'</td><td headers="CORR_EFF_EVAL_PERIOD" class="t20data">'||nvl(i.CORR_EFF_EVAL_PERIOD,'-')||'</td><td headers="CORR_RES_STAT" class="t20data">'||nvl(i.corr_res_stat,'-')||'</td></tr>';
      END LOOP;
      IF numrows>0 THEN
        outrec := '<table border="0" cellpadding="0" cellspacing="0" summary="" class="t20StandardAlternatingRowColors t20Report" style="width:100%"><tbody><tr><th class="t20ReportHeader" id="CORR_TODO">Действия</th><th class="t20ReportHeader" id="INCHARGE">Ответственный</th><th class="t20ReportHeader" id="CORR_TERMS">Сроки</th><th class="t20ReportHeader" id="CORR_DONE">Выполнение</th><th class="t20ReportHeader" id="CORR_EFF_EVAL_PERIOD">Срок оценки результативности</th><th class="t20ReportHeader" id="CORR_RES_STAT">Статус результативности</th></tr>'||outrec||'</tbody></table>';
        ELSE
          outrec:='-';
        END IF;
    END;
    
----------------------
  END IF;
  RETURN outrec;
END;