select ss.ID,
       ss.PART_SER,
       ss.DATE_OPER,
       to_char(count(1) over(partition by ss.PART_SER, ss.DATE_OPER, ss.ID),
               '9999.99') CNT,
       case
         when not (ss.FULL_DATE_OPER > ss.DATE_CANCEL and
               ss.DATE_CANCEL is not null) and ss.PART_SER is not null then
          ss.PART_SER || ' *' ||
          to_char(count(1)
                  over(partition by ss.PART_SER, ss.DATE_OPER, ss.ID),
                  '9999.99')
         else
          null
       end PART_CNT
  from (select case
                 when t1.IS_COMPLEX is null or t1.IS_COMPLEX = 0 then
                  t.ID
                 else
                  (select min(ps.ID)
                     from D_V_MP_PRESCRIBE_SPECS ps
                    where ps.PID = t.ID
                      and ps.NOMMODIF_ID = t1.NOMMODIF_ID)
               end ID,
               nvl((select tt.PARTY_SER
                     from D_V_JURSTORE_EX tt
                    where tt.UNITID = t1.ID),
                   t1.PART_SER) PART_SER,
               nvl((select trunc(tt1.DATE_OPER)
                     from D_V_JURSTORE tt1
                    where tt1.UNITID = t1.ID),
                   t1.EX_DATE) DATE_OPER,
               t.DATE_CANCEL,
               nvl((select tt1.DATE_OPER
                     from D_V_JURSTORE tt1
                    where tt1.UNITID = t1.ID),
                   t1.EX_DATE) FULL_DATE_OPER
          from d_v_mp_prescribes t,
               d_v_hosp_histories_base hh,
               (select ps.ID,
                       ps.PID,
                       ps.PART_SER,
                       ps.IS_COMPLEX + ps.IS_EXTEMPO IS_COMPLEX,
                       trunc(ps.EX_DATE) EX_DATE,
                       ps.NOMMODIF_ID
                  from D_V_MP_PRESCRIBE_SPECS ps
                 where ps.PART_SER is not null
                    or exists
                 (select null from D_V_JURSTORE j where j.UNITID = ps.ID)) t1
         where hh.ID = :HH_ID
           and hh.LPU = :LPU
           and t.DISEASECASE = hh.DISEASECASE @if(:HH_PRESCRIBES_ID)
         {
           and instr(';' || :HH_PRESCRIBES_ID || ';', ';' || t.ID || ';') > 0
         @}
           and t1.PID(+) = t.ID
           and t.MP_TYPE_CODE != 3
           and t.MP_CONDITION_CODE in (1, 2, 3, 4)
           and (t.HH_DEP = :HH_DEP_ID or :HH_DEP_ID = 0)) ss
union
select ss1.ID,
       ss1.PART_SER,
       ss1.PLAN_DATE,
       '+' CNT,
       case
         when not (ss1.PLAN_DATE > ss1.DATE_CANCEL and
               ss1.DATE_CANCEL is not null) and ss1.PART_SER is not null then
          '+'
         else
          null
       end PART_CNT
  from (select case
                 when t1.IS_COMPLEX is null or t1.IS_COMPLEX = 0 then
                  t.ID
                 else
                  (select min(ps.ID)
                     from D_V_MP_PRESCRIBE_SPECS ps
                    where ps.PID = t.ID
                      and ps.NOMMODIF_ID = t1.NOMMODIF_ID)
               end ID,
               nvl((select tt.PARTY_SER
                     from D_V_JURSTORE_EX tt
                    where tt.UNITID = t1.ID),
                   t1.PART_SER) PART_SER,
               t1.plan_date,
               t.DATE_CANCEL
          from d_v_mp_prescribes t,
               d_v_hosp_histories_base hh,
               (select ps.ID,
                       ps.PID,
                       ps.PART_SER,
                       ps.IS_COMPLEX + ps.IS_EXTEMPO IS_COMPLEX,
                       trunc(ps.EX_DATE) EX_DATE,
                       ps.PLAN_DATE,
                       ps.NOMMODIF_ID
                  from D_V_MP_PRESCRIBE_SPECS ps
                 where ps.PART_SER is null
                    or not exists
                 (select null from D_V_JURSTORE j where j.UNITID = ps.ID)) t1
         where hh.ID = :HH_ID
           and hh.LPU = :LPU
           and t.DISEASECASE = hh.DISEASECASE @if(:HH_PRESCRIBES_ID)
         {
           and instr(';' || :HH_PRESCRIBES_ID || ';', ';' || t.ID || ';') > 0
         @}
           and t1.PID(+) = t.ID
           and t.MP_TYPE_CODE != 3
           and t.MP_CONDITION_CODE in (1, 2, 3, 4)
           and (t.HH_DEP = :HH_DEP_ID or :HH_DEP_ID = 0)) ss1