--       (
--       round(
--       count(*)/
--       (
--       select count(*) from zc3050 zc3c
--       where zc3c.zc3_filial = zc3.zc3_filial and left(zc3c.zc3_endere,5) = left(zc3.zc3_endere,5)
--       and zc3c.d_E_l_E_T_ = ' '
--       )
--
--       ,7) * 100 ) as CAPACIDADE
--
select 'PE' ESTADO,
       case
         when zc3.zc3_filial = '01' then
          'MOPE'
         else
          'MOPE'
       end EMPRESA,
       substr(zc3.zc3_endere, 1, 2) SETOR,
       substr(zc3.zc3_endere, 3, 3) QUADRA,
       case
         when zc3_status = '0' then
          'DISPONIVEL'
         else
          case
            when zc3_status = '1' then
             'INDISPONIVEL'
            else
             case
               when zc3_status = '2' then
                'DIRETORIA'
               else
                case
                  when zc3_status = '3' then
                   'RESERVA CONTRATO'
                  else
                   case
                     when zc3_status = '4' then
                      'RESERVA CALLCENTER'
                     else
                      case
                        when zc3_status = '5' then
                         'RESERVA UNICO'
                        else
                         'OUTRO'
                      end
                   end
                end
             end
          end
       end STATUS,
       ZC3.zc3_status,

       count(*) QTDE_LOTES,
       case
         when zc3_tipest = 'R' then
          'CONSTRUIDA'
         else
          case
            when zc3_tipest = 'V' then
             'PLANTA'
          end
       end ESTOQUE,
       case
         when zc3_apto = 'S' then
          'Sim'
         else
          case
            when zc3_apto = 'N' then
             'Nao'
          end
       end APTO_VENDA,
       zc3.zc3_endere S_Q_L,
       sb1.b1_cod CODIGO,
       sb1.b1_desc PRODUTO,
       zc0_codven,
       sa3.a3_nome CONSULTORA,
       SA1.A1_email EMAIL,
       zc0.zc0_cgc CPF_CGC,
       zc0.zc0_nomcli CLIENTE,
       zc0.zc0_telres TELEFONE,
       zc0.zc0_telcel CELULAR,
       zc0.zc0_telout TELEFONE2,
       zc0.zc0_end ENDERECO,
       zc0.zc0_cep CEP,
       zc0.zc0_munici CIDADE,
       zc0.zc0_est ESTADO,
       trim(zc0.zc0_refend) REFERENCIA,


           case when
             (select MONTHS_BETWEEN(TO_DATE(min(zc2.zc2_dtsepu), 'YYYY/MM/DD'), TO_DATE(zc0.ZC0_EMISSA, 'YYYY/MM/DD'))/30
          from ZC2050 zc2
         where zc2.zc2_endere = zc0.zc0_lote
           and zc2.zc2_endere <> ' '
           and zc2.zc2_dtsepu <> ' '
           and zc2.d_e_l_e_t_ = ' ') <= 1 then
             'UI'

           else

             'UF'

       end

       CONDICAO_DE_USO_JAZIGO,

       ((select count(*)
          from ZC2050 zc2
         where zc2.zc2_endere = zc0.zc0_lote
           and zc2.zc2_endere <> ' '
           and zc2.zc2_tipSep = 'SEP'
           and zc2.zc2_tes = '001'
           and zc2.zc2_resmor = 'N'
           and zc2.d_e_l_e_t_ = ' ')/sb1.b1_qtdegav)*100 as OCUPACAO,

       (select count(*)
          from ZC2050 zc2
         where zc2.zc2_endere = zc0.zc0_lote
           and zc2.zc2_endere <> ' '
           and zc2.zc2_tipSep = 'SEP'
           and zc2.zc2_tes = '001'
           and zc2.zc2_resmor = 'N'
           and zc2.d_e_l_e_t_ = ' ') as QTDE_SEP,

        (select MONTHS_BETWEEN(SYSDATE,TO_DATE(max(zc2.zc2_dtsepu), 'YYYY/MM/DD'))/12
          from ZC2050 zc2
         where zc2.zc2_endere = zc0.zc0_lote
           and zc2.zc2_endere <> ' '
           and zc2.zc2_dtsepu <> ' '
           and zc2.d_e_l_e_t_ = ' ')

           Tempo_SEP_Anos,

       (select count(*)
          from ZC2050 zc2
         where zc2.zc2_endere = zc0.zc0_lote
           and zc2.zc2_endere <> ' '
           and zc2.zc2_resmor = 'S'
           and zc2.d_e_l_e_t_ = ' ') as QTDE_RM,
      CASE
         when (select sum(e1_valor)
                 from SE1050 SE1
                where SE1.e1_filial = zc0.zc0_filial
                  and SE1.e1_contrt = zc0.zc0_contrt
                  and SE1.d_e_l_e_t_ = ' '
                  and se1.e1_prefixo in ('VEN')
                  and se1.e1_saldo > 0
                  and Se1.E1_Vencrea < to_char(sysdate, 'YYYYMMDD')) > 0 then
          'NAO'
         else
          'SIM'
       end as ADIMPLENCIA_VEN,
       CASE
         when (select sum(e1_valor)
                 from SE1050 SE1
                where SE1.e1_filial = zc0.zc0_filial
                  and SE1.e1_contrt = zc0.zc0_contrt
                  and SE1.d_e_l_e_t_ = ' '
                  and se1.e1_prefixo in ('MAN')
                  and se1.e1_saldo > 0
                  and Se1.E1_Vencrea < to_char(sysdate, 'YYYYMMDD')) > 0 then
          'NAO'
         else
          'SIM'
       end as ADIMPLENCIA_MAN,

       case when (select count(*)
          from zam050 zam
         where zam.zam_cgc = zc0.zc0_cgc
           and zam.zam_status = '1'
           and zam.d_E_L_E_T_ = ' ') > 0 then 'SIM' else 'NAO' end as POSSUI_PLANO,

            (select count(*)
          from zcm050 zcm
         where zcm.zcm_codcli = zc0.zc0_codcli
          and zcm.d_E_L_E_T_ = ' '
           and zcm.zcm_status in ('A','F')) as POSSUI_FUNERAL_UF,

      (select count(*)
          from zcm050 zcm
         where zcm.zcm_codcli = zc0.zc0_codcli
           and zcm.zcm_dtutil <> ' '
           and zcm.d_E_L_E_T_ = ' '
           and zcm.zcm_status not in ('E')) as USO_FUNERAL_UF,

      (select count(*)
          from zeo050 zeo
         where zeo.zeo_codcli = zc0.zc0_codcli
           and zeo.d_E_L_E_T_ = ' '
           and zeo.zeo_status not in ('E')) as POSSUI_CREMACAO

      ,
       (select count(*)
          from zeo050 zeo
         where zeo.zeo_codcli = zc0.zc0_codcli
           and zeo.zeo_dtutil <> ' '
           and zeo.d_E_L_E_T_ = ' '
           and zeo.zeo_status not in ('E','0','4')) as USO_CREMACAO

  from zc3050 zc3
  left join sb1050 sb1
    on b1_filial = zc3_filial
   and b1_cod = zc3_codjaz
  left join zc0050 zc0
    on zc0_filial = zc3_filial
   and zc0_lote = zc3.zc3_endere
  left jOIN SA3050 SA3
    ON A3_COD = ZC0_CODVEN
  LEFT JOIN SA1010 SA1
    ON A1_COD = ZC0_CODCLI
 where zc3.d_E_L_E_T_ = ' '
   and sb1.d_e_l_e_t_ = ' '
   and zc0.d_e_l_e_t_ = ' '
   AND SA3.D_e_L_e_t_ = ' '
   AND SA1.D_e_L_e_t_ = ' '
--and left(zc3.zc3_endere,2) not in ('00','99')
 group by zc0_codven,
          sa3.a3_nome,
          SA1.A1_email,
          zc0.zc0_codcli,
          zc0.zc0_cgc,
          zc0.zc0_contrt,
          zc0.zc0_filial,
          zc0.zc0_lote,
          zc0.zc0_nomcli,
          zc0.zc0_telres,
          zc0.zc0_telcel,
          zc0.zc0_telout,
          zc0.zc0_end,
          zc0.zc0_cep,
          zc0.zc0_munici,
          zc0.zc0_est,
          zc0.zc0_refend,
          sb1.b1_cod,
          sb1.b1_desc,
          zc3.zc3_filial,
          substr(zc3.zc3_endere, 1, 2),
          substr(zc3.zc3_endere, 3, 3),
          ZC3.zc3_status,
          zc3.zc3_tipest,
          zc3.zc3_endere,
          zc0.zc0_emissa,
          sb1.b1_qtdegav,
          zc3_apto
 order by 1, 2