SELECT DISTINCT S1.CCLA1 AS CODPOLIZA,
                    S1.CCLA2 AS CODPRODUCTO,
                    S1.CCLA3 AS CODPLAN,
                    S2.CCLA3 AS CODCOBERTURA
                    --,pac_ws_zus.f_get_primaneta(P_EDAD, P_CAPITAL, S1.CCLA2, S1.CCLA3, S2.CCLA3, null, S1.CCLA1, P_PLAZOCREDITO, 0) AS PRIMACOB_UF
                    --  ,pac_ws_zus.f_get_primaneta(50    ,      1000, S1.CCLA2, S1.CCLA3, S2.CCLA3, null, S1.CCLA1,             12, 0) AS PRIMACOB_UF
                    ,GP.ICAPMIN CAPITALMIN
                    ,GP.ICAPMAX CAPITALMAX
                    ,GP.NEDAMIC EDADMIN
                    ,GP.NEDAMAC EDADMAX
                    ,S.CFORPAG PERIODICIDAD
    FROM SGT_SUBTABS_DET S1
    JOIN SGT_SUBTABS_DET S2
      ON   S1.CCLA1 = S2.CCLA4
      AND  S2.CSUBTABLA = S1.NVAL2
    JOIN GARANPRO GP
      ON   GP.CGARANT = S2.CCLA3
      AND  GP.SPRODUC = S1.CCLA2
      AND  GP.CACTIVI = S1.CCLA3
    JOIN seguros s
      ON   s.ncertif = 0 AND s.npoliza= S1.CCLA1
      AND  s.csituac =0
      AND  s.creteni =0
    WHERE S1.CSUBTABLA = 1200
     -- AND S1.CCLA2 = 10000--P_CODPRODUCTO
     -- AND  1000>=GP.ICAPMIN AND 1000<= GP.ICAPMAX
     -- AND  50>=GP.NEDAMIC AND 50<= GP.NEDAMAC
      --AND  NVL(P_PERIODICIDADPAGO,S.CFORPAG) = S.CFORPAG
      --AND  NVL(P_CODPLAN,S1.CCLA3) = S1.CCLA3;
    and  S1.CCLA1 in ( '527','539','489','494','495','5000000271','5000000272','5000000273','5000000274','5000000187')