titel

SELECT
  b.benef_id AS id,
  NVL(
    benef_nomesocial,
    NVL(benef_nomeafetivo, benef_nome)
  ) AS nome,
  b.benef_sexo AS sexo,
  b.benef_genero AS genero,
  b.benef_dtnasc AS dt_nascimento,
  b.benef_cns,
  b.benef_cpf,
  bp.benef_numcarteira AS num_carteira,
  bp.benef_validadecarteira AS validade_carteira,
  NVL(
    benef_dddcelular,
    NVL(
      benef_dddresid,
      NVL(benef_dddcomercial, 0)
    )
  ) AS ddd,
  NVL(
    benef_celular,
    NVL(
      benef_resid,
      NVL(benef_comercial, 0)
    )
  ) AS telefone,
  bs.munic_id,
  bs.benef_endcep,
  bs.bar_id,
  NVL(
    p.plano_id,
    (
      SELECT
        bp.pp_idplanopartic
      FROM
        benefparticular bp
        INNER JOIN planopartic pp ON pp.pp_idplano = bp.pp_idplanopartic
      WHERE
        (bp.benef_id = b.benef_id)
        AND (
          bp.super_id = 127
          /* :SUPER_ID */
          )
        AND (
          bp.bpar_ativo = 1
          /* :STATUS */
          )
        AND (
          pp.pp_status = 1
          /* :STATUS */
          )
        AND (ROWNUM = 1)
    )
  ) AS plano_id,
  NVL(
    p.plano_descricao,
    (
      SELECT
        pp.pp_descplano
      FROM
        benefparticular bp
        INNER JOIN planopartic pp ON pp.pp_idplano = bp.pp_idplanopartic
      WHERE
        (bp.benef_id = b.benef_id)
        AND (
          bp.super_id = 127
          /* :SUPER_ID */
          )
        AND (
          bp.bpar_ativo = 1
          /* :STATUS */
          )
        AND (
          pp.pp_status = 1
          /* :STATUS */
          )
        AND (ROWNUM = 1)
    )
  ) AS plano,
  NVL(conv_ans, 'particular') AS conv_ans,
  NVL(conv_sigla, 'Particular') AS convenio
FROM
  beneficiario b
  LEFT JOIN benefprofissional bp ON bp.benef_id = b.benef_id
  AND bp.benefp_ativo = 1
  LEFT JOIN benefsuper bs ON bs.benef_id = b.benef_id
  AND bs.super_id = 127
  /* :SUPER_ID */
  AND bs.benef_ativo = 1
  LEFT JOIN geral_plano p ON p.plano_id = bp.plano_id
  AND p.plano_status = 1
  LEFT JOIN geral_convenio c ON c.conv_ans = p.geral_convenio_conv_ans
WHERE
  (
    b.benef_ativo = 1
    /* :BENEF_ATIVO */
    )
  AND (
    (
      b.benef_cpf = 09548750104
      /* :BENEF_MO_CPF */
      )
    OR (
      bp.benef_numcarteira = 09548750104
      /* :BENEF_MO_CPF */
      )
  );