<?

//En linux 
$salto_linea = "\n"; //variable global para manejo del salto de linea
//En windows 
$salto_linea = "\r\n"; //variable global para manejo del salto de linea

/*
 * Variables globales del proyecto
 */
//se deja global la letra de la planilla de aportes - 20140905
$letraPlanillaAportes = sfConfig::get('app_letraPlanillaAportes');

$scarpeta = "uploads"; //carpeta donde guardar el archivo.

$sfile = $scarpeta . "/archivos_banco/PlanillaAutoliquidacionCorrecion_" . date("d") . date("m") . date("Y") . ".txt"; //ruta del archivo a generar
$fp = fopen($sfile, "w");

$shtml = "";
$shtmlAuto = "";

$consul = Doctrine_Manager::getInstance()->connection();

//ARCHIVO R1DATGEN Datos Generales

$archivo = fopen("uploads/archivos_banco/r1datgen.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R101\tR102\tR103\tR104\tR105\tR106\tR107A\tR107B\tR107C\tR107\tR108\tR109\tR110\tR111\tR112\tR113\tR114\tR115\tR116\tR120A\tR117\t\n";
//$shtml .="01\t00001\tAGENCIA NACIONAL DE MINERIA\tNI\t900500018\t2\tE\t0000000000\t\tU\t\t\t14-25\t$vig_ini-$per_ini\t$vig_fin-$per_fin\t\t\t00180\t000517709000\t1\t00\t\n";

/**
 * Se cambia el valor de la planilla, en cuanto a que se pone todo el valor de ibc y no solo el aumento - 20150915
 * case when (sum(total.IBC_c) - sum(total.IBC_a) > 0) then sum(total.IBC_c) - sum(total.IBC_a) else 0 end as total_neg,
 */
$sql = "
select 
'01' AS C1,
'00001' AS C2,
RPAD( translate(tab0.nombre_empresa,'áéíóúÁÉÍÓÚ','aeiouAEIOU') ,200,' ') AS C3,
'NI' AS C4,
rPAD(tab0.nit,16,' ') AS C5,
digito_verificacion(tab0.nit) AS C6,
'N' AS C7,
rPAD($num_planilla,10,' ') AS C8,
rPAD('$fecha_pago',10,' ') AS C9,
'$letraPlanillaAportes' AS C10,
rPAD(' ',10,' ') as c11,
rPAD(' ',40,' ') AS C12,
rpad(riesgo.codigo, 6, ' ') as c13,
'$vig_fin-' || lpad($per_fin,2,0) as c14,
'$vig_ini-' || lpad($per_ini,2,0)  AS C15,
rPAD(' ',10,' ') AS C16,
rPAD(' ',10,' ') AS C17,
lpad(tab.cant_c - nvl(salud1.cant_ka,0),5,0) as c18,
--lpad(368,5,0) as c18,
lpad(icbf1.total,12,0) as c19,
lpad(1,1,0) as c20,
lpad(0,2,0) as c21

from datos_compania tab0

left join 
(
    select 
        sum(cant_a) as cant_a, 
        sum(cant_c) as cant_c,
        count(total.num_doc) as cant, 
        sum(total.total_a) as total_a, 
        sum(total.total_c) as total_c, 
        sum(total.total_c) - sum(total.total_a) as total, 
        sum(total.total_c) + sum(total.total_a) as total_sum 
        from (
          select 
          tab0.tipo_planilla,
          tab0.num_doc as num_doc, 
          case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_func) else 0 end as total_a, 
          case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_func) else 0 end as total_c, 
          case when tab0.tipo_planilla = 'A' then 1 else 0 end as cant_a, 
          case when tab0.tipo_planilla = 'C' then 1 else 0 end as cant_c
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo
          group by  tab0.tipo_planilla, tab0.num_doc 
       )total   
)tab ON 1 = 1

/*
Trae el maximo codigo de riesgo de las afiliaciones - 20130820
Trae el maximo codigo de riesgo de las liquida aportes - 20150724
*/
left join 
(
    select distinct nvl(codfon.codigo,' ') as codigo 
    from no_mae_fondos maefon
    left join no_cod_fondo codfon
    on maefon.num_doc = codfon.nitocc
    where maefon.id_mae_fondos = (
        select id_mae_fondos from(
            select 
            aut.id_mae_fondos,
            ROW_NUMBER() OVER (
               --partition by aut.id_mae_fondos
                ORDER BY aut.cant desc
            ) AS rn
            from(
                select 
                id_mae_fondos, count(id_mae_fondos) as cant
                from no_liquida_aporte 
                where id_clase_fondo = 3
                and id_periodo = $id_periodo 
                and TIPO_PLANILLA = 'A'
                group by
                id_mae_fondos
            )aut
            where 
            aut.cant > 0
        )
        where rn = 1
    )
)riesgo ON 1 = 1

/*
Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
*/
left join 
(
    select 
   -- tab.cant, 
    sum(tab.total_neg) as total
    from (
    
        select  
        total.num_doc,
        count(total.tipo_planilla) as cant, 
        sum(total.aporte_ibc) as total1, 
        sum(total.IBC) as total2, 
        sum(total.IBC_a) as total_a, 
        sum(total.IBC_c) as total_c, 
        sum(total.IBC_c) - sum(total.IBC_a) as total,
        case when (sum(total.IBC_c) - sum(total.IBC_a) > 0) then sum(total.IBC_c) else 0 end as total_neg,
        sum(total.IBC_c) + sum(total.IBC_a) as total_sum 
        
        from (
                select tab0.tipo_planilla,
                tab0.num_doc, sum(nvl(tab0.valor_base,0)) as aporte_IBC, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, 
                MAX(nvl(tab0.valor_base,0)) AS IBC,
                max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/, 
              case when tab0.tipo_planilla = 'A' and max(tab0.num_dias) != 0 then max(nvl(tab0.valor_base,0)) else 0 end as ibc_a, 
              case when tab0.tipo_planilla = 'C' and max(tab0.num_dias) != 0 then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC_c
                from no_liquida_aporte tab0
                --se filtra id4-CAJA DE COMPENSACION FAMILIAR en vez de id5 - 20130802
                where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 4
                /*
                Este usuario se deja quemado para que no salga - 20131022
                */
                and (
                  case when 
                  tab0.num_doc = '52992037' and
                  (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo) = '201301'
                  then 1
                  else 0
                  end  
                ) =  0
                group by tab0.tipo_planilla,tab0.num_doc
                
         )total  
         group by
        total.num_doc    
   )tab   
  -- group by   
   --tab.cant
      
)icbf1 ON 1 = 1


left join
(
    select 
    count(total.num_doc) as cant_ka, 
    sum(total.CANTIDAD) as total1_ka, 
    sum(total.CANTIDAD) as total_ka 
    from (
      select tab0.num_doc, count(tab0.id_novedad_kardex) as CANTIDAD
      from no_novedad_kardex tab0
      left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
      where tab1.afecta_antigu = 1 and tab0.estado = 1 and tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
      group by tab0.num_doc
    )total
)kardex1 ON 1 = 1


left join
(
    select 
    sum( round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0))  ,-3) ) as valor
    from no_estado_func_v tab0    
    
    --left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.num_doc
        ORDER BY aut.estado DESC
       ) AS rn
      from no_nombramiento aut
    )tab5
    on tab5.id_tipo_doc = tab0.id_tipo_doc 
    and tab5.num_doc = tab0.num_doc
    and tab5.rn = 1
    
    left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

    left join
    (
    select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD, max(tab1.suspension) as max_marca
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
        where tab1.afecta_antigu = 1 and tab0.estado = 1 
         /*   
        and (
          tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
          or
          tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
          or 
          tab0.id_periodo = $id_periodo
        )*/
        and
        (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
        and (
          to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
          or
          trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
          or 
          tab0.id_periodo = $id_periodo
        )
        group by tab0.num_doc
    )kardex1 ON kardex1.num_doc = tab0.num_doc
    
    /*
    Se cambia de id-4 por atributo en  la tabla no_tip_nov_ka de afecta_riesgo, para validar cuales afectan en los dias de ARP-ARL - 20130919
    */
    left join
    (
        select tab0.num_doc, case when sum(tab0.num_dias) > 30 then 30 else sum(tab0.num_dias) end  as CANTIDAD
        from no_novedad_kardex tab0
        left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
        where tab1.afecta_riesgo = 1 and tab0.estado = 1 and tab0.fecha_inc between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        group by tab0.num_doc
    )kardex4 ON kardex4.num_doc = tab0.num_doc

    left join 
    (
        select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
        max(tab0.porcentaje_patrono)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
        from no_liquida_aporte tab0
        where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 2
        group by tab0.num_doc
    )salud1 ON salud1.num_doc = tab0.num_doc


    WHERE tab0.periodo = $id_periodo 
    and (tab0.actual = 1 or
      ( tab0.actual = 0 
        /*
        Se cambia fecha de termina(ultimo dia de trabajo) por fecha de retiro, 
        para tener encuenta si trabajo en el periodo al menos 1 dia - 20130828                    
        */
        and tab5.fecha_retiro != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and tab5.fecha_termina
      between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') ))
      )
    )
    and kardex1.cantidad > 0    
)kardex4 on 1 = 1


left join 
(
  select 
    count(total.num_doc) as cant_ka
    from (
      select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
      max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
      from no_liquida_aporte tab0
      where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 2
      group by tab0.num_doc
    )total
    where total.dias = 0
)salud1 ON 1 = 1
   
";

//                 echo $sql;



$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R2LIQDAP Detalle     
$archivo = fopen("uploads/archivos_banco/r2liqdap.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R201\tR202\tR203\tR204\tR206\tR206A\tR206B\tR206C\tR206D\tR206E\tR207\tR208\tR209\tR210\tR211\tR212\tR213\tR214\tR215\tR216\tR217\tR218\tR219\tR220\tR221\tR222\tR223\tR224\tR225\tR226\tR227\tR228\tR229\tR230\tR231\tR232\tR233\tR234\tR239A\tR235\tR241A\tR236\tR237\tR238\tR239\tR241\tR242\tR243\tR244\tR245\tR246\tR246A\tR247\tR248\tR249\tR250\tR251\tR252\tR253\tR254\tR255\tR256\tR257\tR258\tR259\tR260\tR261\tR262\tR263\tR264\tR265\tR266\tR267\t\n";
//$shtml .= "";

/**
 * Se agregan filas para licencias que afectan antiguedad - 20150724
 * Registro 1.  El  registro original de dias laborados con la letra (A)
 * Registro 2.  El registro con la corrección para los días laborados (C)
 * Registro 3. El registro de la Licencia no remunerada como se reporto originalmente (A)
 * Registro 4. Nuevamente el registro de la Licencia no remunerada tal como se presento originalmente con los mismos valores (C)
 * Por favor actualizar  el archivo de Planilla de autoliquidación para quie en la posición 506  tenga "N" - 20141120 
 * Se agrega validacion para que los registros de licencia no remuneradas, tenga almenos dias de salud y caja de compensacion (and ( nvl(ccf1.dias,0) + nvl(salud1.dias,0) ) > 0) - 20150915
 */
$sql = "
select 
tab.c1,
lpad(rownum,5,0) as c2,
tab.c3,
case when tab.c0 = 0 then tab.c4 else tab.c4a end c4,
tab.c5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10,
case when tab.c0 = 0 then translate(tab.c11,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') else translate(tab.c11a,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') end c11,
translate(tab.c12,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') as c12,
translate(tab.c13,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') as c13,
translate(tab.c14,'áéíóúÁÉÍÓÚñÑ','aeiouAEIOUnN') as c14,
tab.c15,
tab.c16,
tab.c17,
tab.c18,
tab.c19,
tab.c20,
tab.c21,
tab.c22,
tab.c23,
tab.c24,
tab.c25,
tab.c26,
tab.c27,
tab.c28,
tab.c29,
tab.c30,
tab.c31,
tab.c32,
tab.c33,
tab.c34,
tab.c35,
case when tab.id_pension_func is not null then '00' else tab.c36 end c36,
tab.c37,
tab.c38,
tab.c39,
tab.c40,
tab.c41,
case when tab.id_pension_func is not null then '000000000' else tab.c42 end c42,
tab.c43,
tab.c44,
tab.c45,
tab.c46,
tab.c47,
tab.c48,
tab.c49,
tab.c50,
tab.c51,
tab.c52,
tab.c53,
tab.c54,
tab.c55,
tab.c56,
tab.c57,
tab.c58,
tab.c59,
tab.c60,
tab.c61,
tab.c62,
--tab.c63,
case when to_number(tab.c38) = 0 then LPAD(0,9,0) else tab.c63 end c63,
tab.c64,
tab.c65,
tab.c66,
tab.c67,
tab.c68,
tab.c69,
tab.c70,
tab.c71,
tab.c72,
tab.c73,
tab.c74,
tab.c75,
RPAD(to_char('N'),1,' ') AS C76,
LPAD(tab.c77,8,' ') AS C77--campo nuevo de tipo aporte pension - 20160407

from(

select taborder.* from (

/*
*Registro 2 - Funcionarios con reporte de fondos
*/

select 
penfun.id_pension_func,
1 as c00,--order de mensual luego licencia
0 as c0,
'02' AS C1,
LPAD(rownum,5,0) AS C2,
'CC' AS C3,
RPAD(to_char(tab0.num_doc),16,' ') AS C4,
rpad(to_char(tab0.num_doc),16,' ') as c4a,
'01' AS C5,
case when penfun.id_pension_func is not null then '01' else '00' end AS C6,--suptipo de pensionado
' ' AS C7,
' ' AS C8,
RPAD(nvl(tab2.coddepto,' '),2,' ') AS C9,
RPAD(nvl(substr(tab3.codmun_text,3),' '),3,' ') AS C10,
RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11,
RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11a,
RPAD(nvl(tab4.segundo_apellido,' '),30,' ') AS C12,
RPAD(nvl( replace( tab4.primer_nombre,'','') ,' '),20,' ') AS C13,
RPAD(nvl( replace( tab4.segundo_nombre,'',''),' '),30,' ') AS C14,
case when tab5.fecha_ingreso
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then 'X' else ' ' end AS C15,
case when tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then 'X' else ' ' end AS C16,

case when salud.fecha_ingreso   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then 'X' else ' ' end AS C17,
case when salud.fecha_retiro   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then 'X' else ' ' end AS C18,
case when pension.fecha_ingreso   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then 'X' else ' ' end AS C19,
case when pension.fecha_retiro   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then 'X' else ' ' end AS C20,

case when ascenso.cantidad>0 then 'X' else ' 'end AS C21,
'A' AS C22,
-- debe tener marca para las novedades de kardex que afecten antiguedad - 20130802
case when nvl(encargo.cantidad,0)+nvl(kardex1.cantidad,0)>0 then 'X' else ' 'end AS C23,
case when kardex1.cantidad>0 then ' ' else ' 'end AS C24,
case when kardex2.cantidad>0 then 'X' else ' 'end AS C25,--inc temp enfermedad
case when kardex3.cantidad>0 then 'X' else ' 'end AS C26,--lic maternidad
case when vaca.cantidad>0 then 'X' else ' 'end AS C27,
' ' AS C28,
' ' AS C29,
LPAD(to_char(nvl(kardex4.cantidad,0)),2,0) AS C30,--inc por accidente de trabajo
rpad(nvl(pension.codigo,' '),6,' ') as c31,
case when pension.fecha_retiro   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then rPAD( nvl( (select distinct no_cod_fondo.CODIGO from no_mae_fondos join no_cod_fondo on no_cod_fondo.nitocc = no_mae_fondos.num_doc  
  where no_mae_fondos.id_mae_fondos = pension.id_tap AND ( no_cod_fondo.tipo != 'EPS' or no_cod_fondo.tipo != 'CCF' ) ),' ') ,6,' ') 
  else rPAD(' ',6,' ') end AS C32,
--rPAD(' ',6,' ') AS C32,

rPAD(nvl(salud.codigo,' '),6,' ') AS C33,

case when salud.fecha_retiro   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then rPAD( nvl( (select distinct no_cod_fondo.CODIGO from no_mae_fondos join no_cod_fondo on no_cod_fondo.nitocc = no_mae_fondos.num_doc  
  where no_mae_fondos.id_mae_fondos = salud.id_tae  AND no_cod_fondo.tipo = 'EPS' ),' ') ,6,' ') 
  else rPAD(' ',6,' ') end AS C34,
--rpad(' ',6,' ') as c34,

rPAD(nvl(ccf.codigo,' '),6,' ') AS C35,

-- debe tener en dias 30 para las novedades de kardex que afecten antiguedad - 20130802
-- Tomar dias de la tabla - 20140724
--case when nvl(kardex1.cantidad,0)>0 then '30' else LPAD(nvl(pension1.dias,0),2,0) end AS C36,
--case when nvl(kardex1.cantidad,0)>0 then '30' else LPAD(nvl(salud1.dias,0),2,0) end AS C37,
LPAD(nvl(pension1.dias,0),2,0) AS C36,
LPAD(nvl(salud1.dias,0),2,0) AS C37,

--LPAD(nvl(salud1.dias-nvl(kardex4.cantidad,0),0),2,0) AS C38,
--LPAD(nvl(riesgo1.dias-nvl(kardex4.cantidad,0),0),2,0) AS C38,
--LPAD(nvl(riesgo1.dias,0),2,0) AS C38,
/*
Toma los dias del riesgo y resta los dias de kardex4 si supera los 30 dias, sino toma los de riesgos
*/
LPAD( case when nvl(riesgo1.dias,0) + nvl(kardex4.cantidad,0) > 30 
then 30 - nvl(kardex4.cantidad,0) else nvl(riesgo1.dias,0) end ,2,0) AS C38,

LPAD(nvl(ccf1.dias,0),2,0) AS C39,
--Se quita la novedad de kardex - 20130725
--case when nvl(kardex1.cantidad,0)>0 then '30' else LPAD( case when nvl(ccf1.dias,0) + nvl(kardex4.cantidad,0) > 30 
--then 30 - nvl(kardex4.cantidad,0) else nvl(ccf1.dias,0) end ,2,0) end AS C39,

LPAD(nvl(tab6.asignacion_basica,0),9,0) AS C40,
' ' AS C41,
LPAD(nvl(pension1.ibc,0),9,0) AS C42,
LPAD(nvl(salud1.ibc,0),9,0) AS C43,
LPAD(nvl(riesgo1.ibc,0),9,0) AS C44,
LPAD(nvl(ccf1.ibc,0),9,0) AS C45,
rpad( case when nvl(pension1.tarifa,0) = 0 then '0.0' when nvl(pension1.tarifa,0) < 1 then '0' else '' end || + nvl(pension1.tarifa,0) ,7,0) as C46,
lPAD(nvl(pension1.aporte,0) + nvl(pension1.aporte_patrono,0),9,0) AS C47,
lPAD(0,9,0) AS C48,
lpad(0,9,0) as C49,
lpad(nvl(pension1.aporte,0) + nvl(pension1.aporte_patrono,0),9,0) as C50,
lpad(nvl(pension3.aporte_fsp,0),9,0) as c51,
lpad(nvl(pension3.aporte,0) - nvl(pension3.aporte_fsp,0) ,9,0) as c52,
rpad(0,9,0) as c53,
rpad( case when nvl(salud1.tarifa,0) = 0 then '0.0' when nvl(salud1.tarifa,0) < 1 then '0' else '' end || + nvl(salud1.tarifa,0) ,7,0) as c54,
lpad( nvl(salud1.aporte,0) + nvl(salud1.aporte_patrono,0) ,9,0) as c55,
lpad( case when '01' = '40' then 0 else 0 end ,9,0) as c56,
rpad(' ',15,' ') as c57,
rpad(0,9,0) as C58,
rpad(' ',15,' ') as C59,
rpad(0,9,0) as c60,
rpad( case when nvl(riesgo1.tarifa,0) = 0 then '0.0' when nvl(riesgo1.tarifa,0) < 1 then '0' else '' end || + nvl(riesgo1.tarifa,0) ,9,0) as C61,
lpad(nvl(riesgo.centro_trabajo,0),9,0) as C62,
lpad( nvl(riesgo1.aporte_patrono,0) ,9,0) as c63,
rpad( case when nvl(ccf1.tarifa,0) = 0 then '0.0' when nvl(ccf1.tarifa,0) < 1 then '0' else '' end || + nvl(ccf1.tarifa,0) ,7,0) AS C64,
lpad( nvl(ccf1.aporte_patrono,0) ,9,0) as c65,
rpad( case when nvl(sena1.tarifa,0) = 0 then '0.0' when nvl(sena1.tarifa,0) < 1 then '0' else '' end || + nvl(sena1.tarifa,0) ,7,0) as C66,
lpad( nvl(sena1.aporte_patrono,0) ,9,0) as c67,
rpad( case when nvl(icbf1.tarifa,0) = 0 then '0.0' when nvl(icbf1.tarifa,0) < 1 then '0' else '' end || + nvl(icbf1.tarifa,0) ,7,0) as C68,
lpad( nvl(icbf1.aporte_patrono,0) ,9,0) as c69,
rpad( case when nvl(esap1.tarifa,0) = 0 then '0.0' when nvl(esap1.tarifa,0) < 1 then '0' else '' end || + nvl(esap1.tarifa,0) ,7,0) as C70,
lpad( nvl(esap1.aporte_patrono,0) ,9,0) as c71,
rpad( case when nvl(men1.tarifa,0) = 0 then '0.0' when nvl(men1.tarifa,0) < 1 then '0' else '' end || + nvl(men1.tarifa,0) ,7,0) as C72,
lpad( nvl(men1.aporte_patrono,0) ,9,0) as c73,
'  ' AS C74,
RPAD(to_char(' '),16,' ') AS C75,
case when pension.id_tipo_aporte != 1 then '1' else ' ' end  AS C77

from no_estado_func_v tab0

left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
left join departamento tab2 ON tab2.id_depto = tab1.id_depto
left join municipios tab3 ON tab3.codmun = tab1.codmun
left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

left join (
    select * 
    from no_pension_func 
    where
    estado = 1
)penfun
on penfun.id_tipo_doc = tab0.id_tipo_doc 
and penfun.num_doc = tab0.num_doc

--left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
left join (
  SELECT
   aut.*,
   ROW_NUMBER() OVER (
    partition by aut.num_doc
    ORDER BY aut.estado DESC
   ) AS rn
  from no_nombramiento aut
)tab5
on tab5.id_tipo_doc = tab0.id_tipo_doc 
and tab5.num_doc = tab0.num_doc
and tab5.rn = 1

--left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp
left join(
    
    select * from(
            select 
            concep.id_tipo_doc,
            concep.num_doc,
            concep.valor as asignacion_basica ,
            
             ROW_NUMBER() OVER (
              PARTITION BY concep.num_doc
              order by concep.fecha_hasta desc
             ) as rn
           
            from no_concepto_periodo concep
            where concep.id_concep_nomina = (select id_concep_basica from no_param_concep) 
            and concep.periodo in ( select distinct id_periodo from no_periodo where id_tipo_periodo in (1,2) and (id_anhos, id_meses) in (SELECT ID_ANHOS, id_meses FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo ))
    )where rn = 1
    
)tab6 ON tab6.id_tipo_doc = tab0.id_tipo_doc and tab6.num_doc = tab0.num_doc
and tab6.rn = 1

left join
(
  select tab0.num_doc,count(tab0.id_novedad_personal) AS CANTIDAD
  from no_novedad_personal tab0
  where tab0.id_novedad= 2 and tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  group by tab0.num_doc
) ascenso ON ascenso.num_doc = tab0.num_doc

left join
(
    select num_doc, sum(cantidad) AS CANTIDAD
    from(
    
        select tab0.num_doc,count(tab0.id_novedad_personal) AS CANTIDAD
        from no_novedad_personal tab0
        where tab0.id_novedad= 3 
        and( 
          tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          or
          tab0.fecha_terminacion  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        or
          to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_inicial and tab0.fecha_terminacion
          or
          trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_inicial and tab0.fecha_terminacion
        )
        group by tab0.num_doc
        
        union 
        
        select tab0.num_doc,count(tab0.id_bonifica_servicios) AS CANTIDAD
        from no_bonifica_servicios tab0
        where tab0.estado=0 and tab0.fecha_bonifica between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        group by tab0.num_doc
        
        union 
        
        select tab0.num_doc,count(tab0.id_hr_extra) AS CANTIDAD
        from no_hr_extra tab0
        left join no_lote_hr_extra tab1 ON tab1.id_lote_hr_extra = tab0.id_lote_hr_extra
        where tab1.estado=0 and tab1.id_periodo = $id_periodo
        group by tab0.num_doc
        
        union 
        
        --OJO:Id en el codigo para marca concep 1003 y 1011 - 20130802
        --Urgente cambiar por parametrizar agrupacion por conceptos - 20130826
        --Se cambia por agrupacion 105-CONCEPTOS DE VARIACIÓN TRANSITORIA PARA LA PLANILLA - 20130902
        --Se cambia por filtro de periodo para los tipos de periodo 1,2,5 y del periodo de retroactivo - 20140507
        select tab0.num_doc,count(tab0.id_liquida_nomina) as cantidad
        from no_liquida_nomina tab0
        where tab0.id_periodo in ( 
        select distinct id_periodo from no_periodo where id_tipo_periodo in (1,2,5) and (id_anhos, id_meses) in (SELECT ID_ANHOS, id_meses FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo )
        union
        select distinct id_periodo from no_periodo where id_tipo_periodo in (5, 6, 7) and (id_anhos) in (SELECT ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo )                   
        )
        --(select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = tab0.id_periodo) = (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = $id_periodo) 
        and id_concep_nomina in (    
            /*
            tab0.id_concep_nomina = 3 
            or
            tab0.id_concep_nomina = 21 
            or
            tab0.id_concep_nomina = 23 
            or
            tab0.id_concep_nomina = 401 
            */
            (select distinct id_concep_nomina from no_agrup_deta where id_agrup_concepto = 105) 
        )
        group by tab0.num_doc
        
        union
         
        
        --OJO: Se dejo quemado para '52717971', '79783764' en el mes de '201305' , '201304' la marca transitoria de salario - 20130805
        
        select distinct
        liq.num_doc,
        1 as cantidad
        from no_liquida_nomina liq
        where 
        liq.id_periodo = $id_periodo
        and liq.num_doc in ( '52717971', '79783764' )
        and (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = liq.id_periodo) in ( '201305' , '201304')
    
    )GROUP BY num_doc

) encargo ON encargo.num_doc = tab0.num_doc

left join
(
        select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
        from no_novedad_kardex tab0
        left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
        where tab1.afecta_antigu = 1 and tab0.estado = 1 

        /* Debe tomar la novedad sin inportar cuando se registre - 20130805
        and tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        */
        and
        (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
        and (
          to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
          or
          trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
          or 
          tab0.id_periodo = $id_periodo
        )
                
        group by tab0.num_doc
)kardex1 ON kardex1.num_doc = tab0.num_doc

left join
(
    select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
    /*
    Se cambia id_tip_nov_ka = 2 por 1 INCI
    Se deja quemado id 9 para novedad por ajuste de conceptos de reintegro ANM - 20140522
    Se cambia por id en tabla no_param_concep - 20140916
    */
    where tab1.id_tip_nov_ka in ((select distinct id_nov_ka_enfcom from no_param_concep),9) and tab0.estado = 1 
    and (
      tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
      or
      tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
      or 
      tab0.id_periodo = $id_periodo
    )
    and
    (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
    
    /*
    and (
      to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
      or
      trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
      and 
      (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
    )
    */
    group by tab0.num_doc
)kardex2 ON kardex2.num_doc = tab0.num_doc

left join
(
    select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
    /*
    Se cambia id_tip_nov_ka = 3 por 2 INCI
    Se cambia por id en tabla no_param_concep - 20140916
    */
    where tab1.id_tip_nov_ka in ((select distinct id_nov_ka_licmat from no_param_concep)) and tab0.estado = 1 
    and (
    /*
      tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
      or
      tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
    */
      to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
      or
      TRUNC(LAST_DAY( TO_DATE('01/'|| (select LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS from NO_PERIODO where ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) between TAB0.FECHA_INI and TAB0.FECHA_FIN
      or 
      tab0.id_periodo = $id_periodo
    )
    and
    (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
    
    /*
    and (
      to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
      or
      trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
      and 
      (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
    )
    */
    group by tab0.num_doc
)kardex3 ON kardex3.num_doc = tab0.num_doc

/*
Se cambia de id-4 por atributo en  la tabla no_tip_nov_ka de afecta_riesgo, para validar cuales afectan en los dias de ARP-ARL - 20130919
*/
left join
(
    select tab0.num_doc, case when sum(tab0.num_dias) > 30 then 30 else sum(tab0.num_dias) end  as CANTIDAD
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
    where tab1.afecta_riesgo = 1 and tab0.estado = 1 
     and ( 
        tab0.fecha_inc between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        /*
        Validacion para que tome el registro en el periodo de la novedad - 20130820
        */
        or tab0.id_periodo = $id_periodo
    )
    group by tab0.num_doc
)kardex4 ON kardex4.num_doc = tab0.num_doc

left join
(
    select 

       id_tipo_doc,                  
       num_doc,
       sum( nvl(CANTIDAD_vaciones,0) ) as CANTIDAD

       from( 

           select 
           tab0.id_tipo_doc, tab0.num_doc, count(tab0.id_novedad_vac) CANTIDAD,
            tipnov.operador,

            case  when tipnov.operador  = 1 
            /*then 1
            else (-1)*/
            then tab0.num_dias_vac
            else (-1 * tab0.num_dias_vac)
            --else nvl(sum( tab0.fecha_fin - tab0.fecha_ini + 1),0) * (-1)
            end as CANTIDAD_vaciones

           from no_novedad_vac tab0
           left join NO_TIPO_NOV_VAC TIPNOV on TIPNOV.ID_TIPO_NOV_VAC = tab0.ID_TIPO_NOV_VAC
           where 
           tipnov.tiempo_liqui = 1 and
           (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = $id_periodo) and
           /*
           tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
           and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
           */
           /*Marca de vacaciones para todo el tiempo de duracion - 20130917*/
          ( 
              tab0.fecha_ini  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
              or
              tab0.fecha_fin  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
            or
              to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
              or
              trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
            )
            /* Valida que no sea un dia 31, ya que no se cuenta para liquidacion, que posea dias de vac en el periodo liquidado - 20141031 */        
            --and to_char(tab0.fecha_ini,'dd') != 31
            and DIAS_RANGO_MES_SIN_TREINTAYUNO(tab0.fecha_ini,tab0.fecha_fin,(select id_meses from no_periodo where id_periodo = $id_periodo)) > 0
            
            group by tab0.id_tipo_doc, tab0.num_doc,
           tipnov.operador, tab0.num_dias_vac

       ) 

       group by id_tipo_doc, num_doc
)vaca ON vaca.num_doc = tab0.num_doc


LEFT JOIN 
(
    select distinct afilia.num_doc, codigo.codigo, afi.fecha_ingreso, afi.fecha_retiro, afilia.id_tap, afi.id_tipo_aporte
    from no_liquida_aporte afilia
    left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
    left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
    left join no_afilia_fondo afi on afi.id_tipo_doc = afilia.id_tipo_doc and afi.num_doc = afilia.num_doc and afi.id_clase_fondo = afilia.id_clase_fondo and afi.id_mae_fondos = afilia.id_mae_fondos
    where afilia.id_clase_fondo = 1 
    
    and  (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
    between afi.fecha_ingreso and nvl( afi.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
    --and afilia.estado = 1
   
    AND ( codigo.tipo != 'EPS' or codigo.tipo != 'CCF' )
    and afilia.id_periodo = $id_periodo
    and afilia.tipo_planilla = 'A'   
)pension ON pension.num_doc = tab0.num_doc

LEFT JOIN 
(
    select distinct afilia.num_doc, codigo.codigo, afi.fecha_ingreso, afi.fecha_retiro, afilia.id_tae
    from no_liquida_aporte afilia
    left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
    left join no_cod_fondo codigo ON codigo.nitocc = fondo.num_doc
    left join no_afilia_fondo afi on afi.id_tipo_doc = afilia.id_tipo_doc and afi.num_doc = afilia.num_doc and afi.id_clase_fondo = afilia.id_clase_fondo and afi.id_mae_fondos = afilia.id_mae_fondos
    where afilia.id_clase_fondo = 2  
    
    and  (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
    between afi.fecha_ingreso and nvl( afi.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
    --and afilia.estado = 1
    
    AND codigo.tipo = 'EPS'
    and afilia.id_periodo = $id_periodo
    and afilia.tipo_planilla = 'A'   
)salud ON salud.num_doc = tab0.num_doc

LEFT JOIN 
(
    select distinct afilia.num_doc, afilia.centro_trabajo
    from no_afilia_fondo afilia
    where afilia.id_clase_fondo = 3 and afilia.estado = 1
)riesgo ON riesgo.num_doc = tab0.num_doc

LEFT JOIN 
(
    select distinct afilia.num_doc, codigo.codigo
    from no_liquida_aporte afilia
    left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
    left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
    where afilia.id_clase_fondo = 4 
    --and afilia.estado = 1 
    AND codigo.tipo = 'CCF'
    and afilia.id_periodo = $id_periodo
    and afilia.tipo_planilla = 'A'   
)ccf ON ccf.num_doc = tab0.num_doc


left join 
(
    select 
    
    tab0.num_doc, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
    sum(tab0.aporte_patrono) as aporte_patrono_a,      
    sum(tab1.aporte_patrono) as aporte_patrono_c
    
    from no_liquida_aporte tab0
    
    left join no_liquida_aporte tab1
    on tab1.id_periodo = tab0.id_periodo
    and tab1.id_tipo_doc = tab0.id_tipo_doc
    and tab1.num_doc = tab0.num_doc
    and tab1.id_mae_fondos = tab0.id_mae_fondos
    and tab1.id_concep_nomina = tab0.id_concep_nomina
    and tab1.id_clase_fondo = 1
    and tab1.tipo_planilla = 'C'
    
    where tab0.id_periodo = $id_periodo 
    and tab0.id_clase_fondo = 1 and tab0.porcentaje_patrono > 0
    and tab0.tipo_planilla = 'A'   
    
    group by tab0.num_doc
)pension1 ON pension1.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa/100) as TARIFA--, tab0.aporte_func AS APORTE
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.porcentaje_patrono > 0 -- and  ( tab0.tarifa = 16 or tab0.tarifa = 26 )
    and tab0.tipo_planilla = 'A'   
    group by tab0.num_doc
)pension2 ON pension2.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, sum(tab0.valor_base_fsp) as aporte_fsp, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC/*,
    tab0.tarifa/100 as TARIFA, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.tarifa = 1
    and tab0.tipo_planilla = 'A'   
    group by tab0.num_doc
)pension3 ON pension3.num_doc = tab0.num_doc

left join 
(
    select 
    
    tab0.num_doc, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
    sum(tab0.aporte_patrono) as aporte_patrono_a,      
    sum(tab1.aporte_patrono) as aporte_patrono_c
    
    from no_liquida_aporte tab0
    
    left join no_liquida_aporte tab1
    on tab1.id_periodo = tab0.id_periodo
    and tab1.id_tipo_doc = tab0.id_tipo_doc
    and tab1.num_doc = tab0.num_doc
    and tab1.id_mae_fondos = tab0.id_mae_fondos
    and tab1.id_concep_nomina = tab0.id_concep_nomina
    and tab1.id_clase_fondo = 2
    and tab1.tipo_planilla = 'C'
    
    where tab0.id_periodo = $id_periodo 
    and tab0.id_clase_fondo = 2
    and tab0.tipo_planilla = 'A'   
    
    group by tab0.num_doc
)salud1 ON salud1.num_doc = tab0.num_doc

/*
Poner los valores de ARL  de 60267402, asi sean los mismos antes y despues. != por <= - 20150727
Se vuelve a dejar para que deje solo diferentes - 20150827
*/
left join 
(
    select 
    
    tab0.num_doc, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then sum(tab0.aporte_func) else 0 end as aporte, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then max(tab0.num_dias) else 0 end as dias,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then max(tab0.tarifa)/100 else 0 end as tarifa,
    --case when 1 != 0 then max(tab0.tarifa)/100 else 0 end as tarifa,
    sum(tab0.aporte_patrono) as aporte_patrono_a,      
    sum(tab1.aporte_patrono) as aporte_patrono_c
    
    from no_liquida_aporte tab0
    
    left join no_liquida_aporte tab1
    on tab1.id_periodo = tab0.id_periodo
    and tab1.id_tipo_doc = tab0.id_tipo_doc
    and tab1.num_doc = tab0.num_doc
    and tab1.id_mae_fondos = tab0.id_mae_fondos
    and tab1.id_concep_nomina = tab0.id_concep_nomina
    and tab1.id_clase_fondo = 3
    and tab1.tipo_planilla = 'C'
    
    where tab0.id_periodo = $id_periodo 
    and tab0.id_clase_fondo = 3
    and tab0.tipo_planilla = 'A'   
    
    group by tab0.num_doc
)riesgo1 ON riesgo1.num_doc = tab0.num_doc

/*
Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
*/
left join 
(
   select 
    
    tab0.num_doc, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
    sum(tab0.aporte_patrono) as aporte_patrono_a,      
    sum(tab1.aporte_patrono) as aporte_patrono_c
    
    from no_liquida_aporte tab0
    
    left join no_liquida_aporte tab1
    on tab1.id_periodo = tab0.id_periodo
    and tab1.id_tipo_doc = tab0.id_tipo_doc
    and tab1.num_doc = tab0.num_doc
    and tab1.id_mae_fondos = tab0.id_mae_fondos
    and tab1.id_concep_nomina = tab0.id_concep_nomina
    and tab1.id_clase_fondo = 4
    and tab1.tipo_planilla = 'C'
    
    where tab0.id_periodo = $id_periodo 
    and tab0.id_clase_fondo = 4
    and tab0.tipo_planilla = 'A'   
    
    group by tab0.num_doc
)ccf1 ON ccf1.num_doc = tab0.num_doc

/*
Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
*/
left join 
(
   select 
    
    tab0.num_doc, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
    sum(tab0.aporte_patrono) as aporte_patrono_a,      
    sum(tab1.aporte_patrono) as aporte_patrono_c
    
    from no_liquida_aporte tab0
    
    left join no_liquida_aporte tab1
    on tab1.id_periodo = tab0.id_periodo
    and tab1.id_tipo_doc = tab0.id_tipo_doc
    and tab1.num_doc = tab0.num_doc
    and tab1.id_mae_fondos = tab0.id_mae_fondos
    and tab1.id_concep_nomina = tab0.id_concep_nomina
    and tab1.id_clase_fondo = 5
    and tab1.tipo_planilla = 'C'
    
    where tab0.id_periodo = $id_periodo 
    and tab0.id_clase_fondo = 5
    and tab0.tipo_planilla = 'A'   
    
    group by tab0.num_doc
)icbf1 ON icbf1.num_doc = tab0.num_doc

/*
Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
*/
left join 
(
   select 
    
    tab0.num_doc, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
    sum(tab0.aporte_patrono) as aporte_patrono_a,      
    sum(tab1.aporte_patrono) as aporte_patrono_c
    
    from no_liquida_aporte tab0
    
    left join no_liquida_aporte tab1
    on tab1.id_periodo = tab0.id_periodo
    and tab1.id_tipo_doc = tab0.id_tipo_doc
    and tab1.num_doc = tab0.num_doc
    and tab1.id_mae_fondos = tab0.id_mae_fondos
    and tab1.id_concep_nomina = tab0.id_concep_nomina
    and tab1.id_clase_fondo = 6
    and tab1.tipo_planilla = 'C'
    
    where tab0.id_periodo = $id_periodo 
    and tab0.id_clase_fondo = 6
    and tab0.tipo_planilla = 'A'   
    
    group by tab0.num_doc
)sena1 ON sena1.num_doc = tab0.num_doc

left join 
(
   select 
    
    tab0.num_doc, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
    sum(tab0.aporte_patrono) as aporte_patrono_a,      
    sum(tab1.aporte_patrono) as aporte_patrono_c
    
    from no_liquida_aporte tab0
    
    left join no_liquida_aporte tab1
    on tab1.id_periodo = tab0.id_periodo
    and tab1.id_tipo_doc = tab0.id_tipo_doc
    and tab1.num_doc = tab0.num_doc
    and tab1.id_mae_fondos = tab0.id_mae_fondos
    and tab1.id_concep_nomina = tab0.id_concep_nomina
    and tab1.id_clase_fondo = 7
    and tab1.tipo_planilla = 'C'
    
    where tab0.id_periodo = $id_periodo 
    and tab0.id_clase_fondo = 7
    and tab0.tipo_planilla = 'A'   
    
    group by tab0.num_doc
)esap1 ON esap1.num_doc = tab0.num_doc

left join 
(
   select 
    
    tab0.num_doc, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
    case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) < sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
    sum(tab0.aporte_patrono) as aporte_patrono_a,      
    sum(tab1.aporte_patrono) as aporte_patrono_c
    
    from no_liquida_aporte tab0
    
    left join no_liquida_aporte tab1
    on tab1.id_periodo = tab0.id_periodo
    and tab1.id_tipo_doc = tab0.id_tipo_doc
    and tab1.num_doc = tab0.num_doc
    and tab1.id_mae_fondos = tab0.id_mae_fondos
    and tab1.id_concep_nomina = tab0.id_concep_nomina
    and tab1.id_clase_fondo = 8
    and tab1.tipo_planilla = 'C'
    
    where tab0.id_periodo = $id_periodo 
    and tab0.id_clase_fondo = 8
    and tab0.tipo_planilla = 'A'   
    
    group by tab0.num_doc
)men1 ON men1.num_doc = tab0.num_doc


WHERE tab0.periodo = $id_periodo 
and (tab0.actual = 1 or
  ( tab0.actual = 0 
    and tab5.fecha_retiro != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
  )
)
and ( nvl(ccf1.dias,0) + nvl(salud1.dias,0) ) > 0

union


        /*
        * Correccion Registros para afiliaciones familiares
        */
        select 
        penfun.id_pension_func,
        1 as c00,--order de mensual luego licencia
        0 as c0,
        '02' AS C1,
        LPAD(rownum,5,0) AS C2,
        'CC' AS C3,
        RPAD(to_char(tab0.num_doc),16,' ') AS C4,
        rpad(to_char(tab0.num_doc),16,' ') as c4a,
        '01' AS C5,
        case when penfun.id_pension_func is not null then '01' else '00' end AS C6,--suptipo de pensionado
        ' ' AS C7,
        ' ' AS C8,
        RPAD(nvl(tab2.coddepto,' '),2,' ') AS C9,
        RPAD(nvl(substr(tab3.codmun_text,3),' '),3,' ') AS C10,
        RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11,
        RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11a,
        RPAD(nvl(tab4.segundo_apellido,' '),30,' ') AS C12,
        RPAD(nvl( replace( tab4.primer_nombre,'','') ,' '),20,' ') AS C13,
        RPAD(nvl( replace( tab4.segundo_nombre,'',''),' '),30,' ') AS C14,
        case when tab5.fecha_ingreso
          between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then 'X' else ' ' end AS C15,
        case when tab5.fecha_termina
          between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then 'X' else ' ' end AS C16,

        case when salud.fecha_ingreso   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then 'X' else ' ' end AS C17,
        case when salud.fecha_retiro   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then 'X' else ' ' end AS C18,
        case when pension.fecha_ingreso   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then 'X' else ' ' end AS C19,
        case when pension.fecha_retiro   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then 'X' else ' ' end AS C20,

        case when ascenso.cantidad>0 then 'X' else ' 'end AS C21,
        'C' AS C22,
        -- debe tener marca para las novedades de kardex que afecten antiguedad - 20130802
        case when nvl(encargo.cantidad,0)+nvl(kardex1.cantidad,0)>0 then 'X' else ' 'end AS C23,
        case when kardex1.cantidad>0 then ' ' else ' 'end AS C24,
        case when kardex2.cantidad>0 then 'X' else ' 'end AS C25,--inc temp enfermedad
        case when kardex3.cantidad>0 then 'X' else ' 'end AS C26,--lic maternidad
        case when vaca.cantidad>0 then 'X' else ' 'end AS C27,
        ' ' AS C28,
        ' ' AS C29,
        LPAD(to_char(nvl(kardex4.cantidad,0)),2,0) AS C30,--inc por accidente de trabajo
        rpad(nvl(pension.codigo,' '),6,' ') as c31,
        case when pension.fecha_retiro   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then rPAD( nvl( (select distinct no_cod_fondo.CODIGO from no_mae_fondos join no_cod_fondo on no_cod_fondo.nitocc = no_mae_fondos.num_doc  
          where no_mae_fondos.id_mae_fondos = pension.id_tap AND ( no_cod_fondo.tipo != 'EPS' or no_cod_fondo.tipo != 'CCF' ) ),' ') ,6,' ') 
          else rPAD(' ',6,' ') end AS C32,
        --rPAD(' ',6,' ') AS C32,

        rPAD(nvl(salud.codigo,' '),6,' ') AS C33,

        case when salud.fecha_retiro   between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          then rPAD( nvl( (select distinct no_cod_fondo.CODIGO from no_mae_fondos join no_cod_fondo on no_cod_fondo.nitocc = no_mae_fondos.num_doc  
          where no_mae_fondos.id_mae_fondos = salud.id_tae  AND no_cod_fondo.tipo = 'EPS' ),' ') ,6,' ') 
          else rPAD(' ',6,' ') end AS C34,
        --rpad(' ',6,' ') as c34,

        rPAD(nvl(ccf.codigo,' '),6,' ') AS C35,
        
        -- debe tener en dias 30 para las novedades de kardex que afecten antiguedad - 20130802
        -- Tomar dias de la tabla - 20140724
        --case when nvl(kardex1.cantidad,0)>0 then '30' else LPAD(nvl(pension1.dias,0),2,0) end AS C36,
        --case when nvl(kardex1.cantidad,0)>0 then '30' else LPAD(nvl(salud1.dias,0),2,0) end AS C37,
        LPAD(nvl(pension1.dias,0),2,0) AS C36,
        LPAD(nvl(salud1.dias,0),2,0) AS C37,

        --LPAD(nvl(salud1.dias-nvl(kardex4.cantidad,0),0),2,0) AS C38,
        --LPAD(nvl(riesgo1.dias-nvl(kardex4.cantidad,0),0),2,0) AS C38,
        --LPAD(nvl(riesgo1.dias,0),2,0) AS C38,
        /*
        Toma los dias del riesgo y resta los dias de kardex4si supera los 30 dias, sino toma los de riesgos
        */
        LPAD( case when nvl(riesgo1.dias,0) + nvl(kardex4.cantidad,0) > 30 
        then 30 - nvl(kardex4.cantidad,0) else nvl(riesgo1.dias,0) end ,2,0) AS C38,
        
        LPAD(nvl(ccf1.dias,0),2,0) AS C39,
        --Se quita la novedad de kardex - 20130725
        --case when nvl(kardex1.cantidad,0)>0 then '30' else LPAD( case when nvl(ccf1.dias,0) + nvl(kardex4.cantidad,0) > 30 
        --then 30 - nvl(kardex4.cantidad,0) else nvl(ccf1.dias,0) end ,2,0) end AS C39,

        LPAD(nvl(tab6.asignacion_basica,0),9,0) AS C40,
        ' ' AS C41,
        LPAD(nvl(pension1.ibc,0),9,0) AS C42,
        LPAD(nvl(salud1.ibc,0),9,0) AS C43,
        LPAD(nvl(riesgo1.ibc,0),9,0) AS C44,
        LPAD(nvl(ccf1.ibc,0),9,0) AS C45,
        rpad( case when nvl(pension1.tarifa,0) = 0 then '0.0' when nvl(pension1.tarifa,0) < 1 then '0' else '' end || + nvl(pension1.tarifa,0) ,7,0) as C46,
        lPAD(nvl(pension1.aporte,0) + nvl(pension1.aporte_patrono,0),9,0) AS C47,
        lPAD(0,9,0) AS C48,
        lpad(0,9,0) as C49,
        lpad(nvl(pension1.aporte,0) + nvl(pension1.aporte_patrono,0),9,0) as C50,
        lpad(nvl(pension3.aporte_fsp,0),9,0) as c51,
        lpad(nvl(pension3.aporte,0) - nvl(pension3.aporte_fsp,0) ,9,0) as c52,
        rpad(0,9,0) as c53,
        rpad( case when nvl(salud1.tarifa,0) = 0 then '0.0' when nvl(salud1.tarifa,0) < 1 then '0' else '' end || + nvl(salud1.tarifa,0) ,7,0) as c54,
        lpad( nvl(salud1.aporte,0) + nvl(salud1.aporte_patrono,0) ,9,0) as c55,
        lpad( case when '01' = '40' then 0 else 0 end ,9,0) as c56,
        rpad(' ',15,' ') as c57,
        rpad(0,9,0) as C58,
        rpad(' ',15,' ') as C59,
        rpad(0,9,0) as c60,
        rpad( case when nvl(riesgo1.tarifa,0) = 0 then '0.0' when nvl(riesgo1.tarifa,0) < 1 then '0' else '' end || + nvl(riesgo1.tarifa,0) ,9,0) as C61,
        lpad(nvl(riesgo.centro_trabajo,0),9,0) as C62,
        lpad( nvl(riesgo1.aporte_patrono,0) ,9,0) as c63,
        rpad( case when nvl(ccf1.tarifa,0) = 0 then '0.0' when nvl(ccf1.tarifa,0) < 1 then '0' else '' end || + nvl(ccf1.tarifa,0) ,7,0) AS C64,
        lpad( nvl(ccf1.aporte_patrono,0) ,9,0) as c65,
        rpad( case when nvl(sena1.tarifa,0) = 0 then '0.0' when nvl(sena1.tarifa,0) < 1 then '0' else '' end || + nvl(sena1.tarifa,0) ,7,0) as C66,
        lpad( nvl(sena1.aporte_patrono,0) ,9,0) as c67,
        rpad( case when nvl(icbf1.tarifa,0) = 0 then '0.0' when nvl(icbf1.tarifa,0) < 1 then '0' else '' end || + nvl(icbf1.tarifa,0) ,7,0) as C68,
        lpad( nvl(icbf1.aporte_patrono,0) ,9,0) as c69,
        rpad( case when nvl(esap1.tarifa,0) = 0 then '0.0' when nvl(esap1.tarifa,0) < 1 then '0' else '' end || + nvl(esap1.tarifa,0) ,7,0) as C70,
        lpad( nvl(esap1.aporte_patrono,0) ,9,0) as c71,
        rpad( case when nvl(men1.tarifa,0) = 0 then '0.0' when nvl(men1.tarifa,0) < 1 then '0' else '' end || + nvl(men1.tarifa,0) ,7,0) as C72,
        lpad( nvl(men1.aporte_patrono,0) ,9,0) as c73,
        '  ' AS C74,
        RPAD(to_char(' '),16,' ') AS C75,
        case when pension.id_tipo_aporte != 1 then '1' else ' ' end  AS C77

        from no_estado_func_v tab0

        left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
        left join departamento tab2 ON tab2.id_depto = tab1.id_depto
        left join municipios tab3 ON tab3.codmun = tab1.codmun
        left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

        left join (
            select * 
            from no_pension_func 
            where
            estado = 1
        )penfun
        on penfun.id_tipo_doc = tab0.id_tipo_doc 
        and penfun.num_doc = tab0.num_doc

        --left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
        left join (
          SELECT
           aut.*,
           ROW_NUMBER() OVER (
            partition by aut.num_doc
            ORDER BY aut.estado DESC
           ) AS rn
          from no_nombramiento aut
        )tab5
        on tab5.id_tipo_doc = tab0.id_tipo_doc 
        and tab5.num_doc = tab0.num_doc
        and tab5.rn = 1

        left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

        left join
        (
          select tab0.num_doc,count(tab0.id_novedad_personal) AS CANTIDAD
          from no_novedad_personal tab0
          where tab0.id_novedad= 2 and tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          group by tab0.num_doc
        ) ascenso ON ascenso.num_doc = tab0.num_doc

        left join
        (
            select num_doc, sum(cantidad) AS CANTIDAD
            from(

                select tab0.num_doc,count(tab0.id_novedad_personal) AS CANTIDAD
                from no_novedad_personal tab0
                where tab0.id_novedad in ( 3, 4, 7) 
                and( 
                  tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
                  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
                  or
                  tab0.fecha_terminacion  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
                  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
                or
                  to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_inicial and tab0.fecha_terminacion
                  or
                  trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_inicial and tab0.fecha_terminacion
                )                
                group by tab0.num_doc

                union 

                select tab0.num_doc,count(tab0.id_bonifica_servicios) AS CANTIDAD
                from no_bonifica_servicios tab0
                where tab0.estado=0 and tab0.fecha_bonifica between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
                and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
                group by tab0.num_doc

                union 

                select tab0.num_doc,count(tab0.id_hr_extra) AS CANTIDAD
                from no_hr_extra tab0
                left join no_lote_hr_extra tab1 ON tab1.id_lote_hr_extra = tab0.id_lote_hr_extra
                where tab1.estado=0 and tab1.id_periodo = $id_periodo
                group by tab0.num_doc
        
                union 

                --OJO:Id en el codigo para marca concep 1003 y 1011 - 20130802
                --Urgente cambiar por parametrizar agrupacion por conceptos - 20130826
                --Se cambia por agrupacion 105-CONCEPTOS DE VARIACIÓN TRANSITORIA PARA LA PLANILLA - 20130902
                --Se cambia por filtro de periodo para los tipos de periodo 1,2,5 y del periodo de retroactivo - 20140507
                select tab0.num_doc,count(tab0.id_liquida_nomina) as cantidad
                from no_liquida_nomina tab0
                where tab0.id_periodo in ( 
                select distinct id_periodo from no_periodo where id_tipo_periodo in (1,2,5) and (id_anhos, id_meses) in (SELECT ID_ANHOS, id_meses FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo )
                union
                select distinct id_periodo from no_periodo where id_tipo_periodo in (5, 6, 7) and (id_anhos) in (SELECT ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo )                   
                )
                and id_concep_nomina in (    
                   /*
                   tab0.id_concep_nomina = 3 
                   or
                   tab0.id_concep_nomina = 21 
                   or
                   tab0.id_concep_nomina = 23 
                   or
                   tab0.id_concep_nomina = 401 
                   */
                   (select distinct id_concep_nomina from no_agrup_deta where id_agrup_concepto = 105) 
                )
                group by tab0.num_doc
        
                union

                --OJO: Se dejo quemado para '52717971', '79783764' en el mes de '201305' , '201304' la marca transitoria de salario - 20130805
                select distinct
                liq.num_doc,
                1 as cantidad
                from no_liquida_nomina liq
                where 
                liq.id_periodo = $id_periodo
                and liq.num_doc in ( '52717971', '79783764' )
                and (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = liq.id_periodo) in ( '201305' , '201304')

            )GROUP BY num_doc

        ) encargo ON encargo.num_doc = tab0.num_doc

        left join
        (
            select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
            from no_novedad_kardex tab0
            left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
            where tab1.afecta_antigu = 1 and tab0.estado = 1 
            
            /* Debe tomar la novedad sin inportar cuando se registre - 20130805
            and tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
            and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
            */
            and
            (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
            and (
              to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
              or
              trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
              or 
              tab0.id_periodo = $id_periodo
            )

            group by tab0.num_doc
        )kardex1 ON kardex1.num_doc = tab0.num_doc

        left join
        (
            select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
            from no_novedad_kardex tab0
            left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
            /*
            Se cambia id_tip_nov_ka = 2 por 1 INCI
            Se deja quemado id 9 para novedad por ajuste de conceptos de reintegro ANM - 20140522
            Se cambia por id en tabla no_param_concep - 20140916
            */
            where tab1.id_tip_nov_ka in ((select distinct id_nov_ka_enfcom from no_param_concep),9) and tab0.estado = 1

            and (
              tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
              or
              tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
              or 
              tab0.id_periodo = $id_periodo
            )
            and
            (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)

            /*
            and (
              to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
              or
              trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
              and 
              (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
            )
            */
            group by tab0.num_doc
        )kardex2 ON kardex2.num_doc = tab0.num_doc

        left join
        (
            select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
            from no_novedad_kardex tab0
            left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
            /*
            Se cambia id_tip_nov_ka = 3 por 2 INCI
            Se cambia por id en tabla no_param_concep - 20140916
            */
            where tab1.id_tip_nov_ka in ((select distinct id_nov_ka_licmat from no_param_concep)) and tab0.estado = 1 

            and (
            /*
              tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
              or
              tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
            */
              to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
              or
              TRUNC(LAST_DAY( TO_DATE('01/'|| (select LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS from NO_PERIODO where ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) between TAB0.FECHA_INI and TAB0.FECHA_FIN
              or 
              tab0.id_periodo = $id_periodo
            )
            and
            (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)

            /*
            and (
              to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
              or
              trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
              and 
              (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
            )
            */
            group by tab0.num_doc
        )kardex3 ON kardex3.num_doc = tab0.num_doc

        left join
        (
         /* 
            OJO:se evidencia id tipo_nov_ka (4) para incapacidad de trabajo 20130816 
        Se cambia de id-4 por atributo en  la tabla no_tip_nov_ka de afecta_riesgo, para validar cuales afectan en los dias de ARP-ARL - 20130919
        */
            select tab0.num_doc, case when sum(tab0.num_dias) > 30 then 30 else sum(tab0.num_dias) end  as CANTIDAD
            from no_novedad_kardex tab0
            left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
            where tab1.afecta_riesgo = 1 and tab0.estado = 1 
            and ( 
               tab0.fecha_inc between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
               and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
               /*
               Validacion para que tome el registro en el periodo de la novedad - 20130820
               */
               or tab0.id_periodo = $id_periodo
            )
            group by tab0.num_doc
        )kardex4 ON kardex4.num_doc = tab0.num_doc

        left join
        (
            select 

               id_tipo_doc,                  
               num_doc,
               sum( nvl(CANTIDAD_vaciones,0) ) as CANTIDAD

               from(

                    select 
                    tab0.id_tipo_doc, tab0.num_doc, count(tab0.id_novedad_vac) CANTIDAD,
                     tipnov.operador,

                     case  when tipnov.operador  = 1 
                     /*then 1
                     else (-1)*/
                     then tab0.num_dias_vac
                     else (-1 * tab0.num_dias_vac)
                     --else nvl(sum( tab0.fecha_fin - tab0.fecha_ini + 1),0) * (-1)
                     end as CANTIDAD_vaciones

                    from no_novedad_vac tab0
                    left join NO_TIPO_NOV_VAC TIPNOV on TIPNOV.ID_TIPO_NOV_VAC = tab0.ID_TIPO_NOV_VAC
                    where 
                    tipnov.tiempo_liqui = 1 and
                    (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = $id_periodo) and
                    /*
                    tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
                    and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
                    */
                    /*Marca de vacaciones para todo el tiempo de duracion - 20130917*/
                   ( 
                       tab0.fecha_ini  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
                       and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
                       or
                       tab0.fecha_fin  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
                       and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
                     or
                       to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
                       or
                       trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
                     )
                    /* Valida que no sea un dia 31, ya que no se cuenta para liquidacion, que posea dias de vac en el periodo liquidado - 20141031 */        
                    --and to_char(tab0.fecha_ini,'dd') != 31
                    and DIAS_RANGO_MES_SIN_TREINTAYUNO(tab0.fecha_ini,tab0.fecha_fin,(select id_meses from no_periodo where id_periodo = $id_periodo)) > 0
                    
                    group by tab0.id_tipo_doc, tab0.num_doc,
                    tipnov.operador, tab0.num_dias_vac

                ) 

               group by id_tipo_doc, num_doc
        )vaca ON vaca.num_doc = tab0.num_doc


        LEFT JOIN 
        (
            select distinct afilia.num_doc, codigo.codigo, afi.fecha_ingreso, afi.fecha_retiro, afilia.id_tap, afi.id_tipo_aporte
            from no_liquida_aporte afilia
            left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
            left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
            left join no_afilia_fondo afi on afi.id_tipo_doc = afilia.id_tipo_doc and afi.num_doc = afilia.num_doc and afi.id_clase_fondo = afilia.id_clase_fondo and afi.id_mae_fondos = afilia.id_mae_fondos
            where afilia.id_clase_fondo = 1 

            and  (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
            between afi.fecha_ingreso and nvl( afi.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
            --and afilia.estado = 1

            AND ( codigo.tipo != 'EPS' or codigo.tipo != 'CCF' )
            and afilia.id_periodo = $id_periodo
            and afilia.tipo_planilla = 'C'   
        )pension ON pension.num_doc = tab0.num_doc

        LEFT JOIN 
        (
            select distinct afilia.num_doc, codigo.codigo, afi.fecha_ingreso, afi.fecha_retiro, afilia.id_tae
            from no_liquida_aporte afilia
            left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
            left join no_cod_fondo codigo ON codigo.nitocc = fondo.num_doc
            left join no_afilia_fondo afi on afi.id_tipo_doc = afilia.id_tipo_doc and afi.num_doc = afilia.num_doc and afi.id_clase_fondo = afilia.id_clase_fondo and afi.id_mae_fondos = afilia.id_mae_fondos
            where afilia.id_clase_fondo = 2  

            and  (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
            between afi.fecha_ingreso and nvl( afi.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
            --and afilia.estado = 1

            AND codigo.tipo = 'EPS'
            and afilia.id_periodo = $id_periodo
            and afilia.tipo_planilla = 'C'   
        )salud ON salud.num_doc = tab0.num_doc

        LEFT JOIN 
        (
            select distinct afilia.num_doc, afilia.centro_trabajo
            from no_afilia_fondo afilia
            where afilia.id_clase_fondo = 3 and afilia.estado = 1
        )riesgo ON riesgo.num_doc = tab0.num_doc

        LEFT JOIN 
        (
            select distinct afilia.num_doc, codigo.codigo
            from no_liquida_aporte afilia
            left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
            left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
            where afilia.id_clase_fondo = 4 
            --and afilia.estado = 1 
            AND codigo.tipo = 'CCF'
            and afilia.id_periodo = $id_periodo
            and afilia.tipo_planilla = 'C'   
        )ccf ON ccf.num_doc = tab0.num_doc


        left join 
        (
                select 

                tab0.num_doc, 
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
                sum(tab0.aporte_patrono) as aporte_patrono_a,      
                sum(tab1.aporte_patrono) as aporte_patrono_c

                from no_liquida_aporte tab0

                left join no_liquida_aporte tab1
                on tab1.id_periodo = tab0.id_periodo
                and tab1.id_tipo_doc = tab0.id_tipo_doc
                and tab1.num_doc = tab0.num_doc
                and tab1.id_mae_fondos = tab0.id_mae_fondos
                and tab1.id_concep_nomina = tab0.id_concep_nomina
                and tab1.id_clase_fondo = 1
                and tab1.tipo_planilla = 'A'

                where tab0.id_periodo = $id_periodo 
                and tab0.id_clase_fondo = 1 and tab0.porcentaje_patrono > 0
                and tab0.tipo_planilla = 'C'   

                group by tab0.num_doc
        )pension1 ON pension1.num_doc = tab0.num_doc

        left join 
        (
            select tab0.num_doc, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
            max(tab0.tarifa/100) as TARIFA--, tab0.aporte_func AS APORTE
            from no_liquida_aporte tab0
            where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.porcentaje_patrono > 0 -- and  ( tab0.tarifa = 16 or tab0.tarifa = 26 )
            and tab0.tipo_planilla = 'C'   
            group by tab0.num_doc
        )pension2 ON pension2.num_doc = tab0.num_doc

        left join 
        (
            select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, sum(tab0.valor_base_fsp) as aporte_fsp, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC/*,
            tab0.tarifa/100 as TARIFA, tab0.aporte_func AS APORTE*/
            from no_liquida_aporte tab0
            where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.tarifa = 1
            and tab0.tipo_planilla = 'C'   
            group by tab0.num_doc
        )pension3 ON pension3.num_doc = tab0.num_doc

        left join 
        (
                select 

                tab0.num_doc, 
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
                case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
                sum(tab0.aporte_patrono) as aporte_patrono_a,      
                sum(tab1.aporte_patrono) as aporte_patrono_c

                from no_liquida_aporte tab0

                left join no_liquida_aporte tab1
                on tab1.id_periodo = tab0.id_periodo
                and tab1.id_tipo_doc = tab0.id_tipo_doc
                and tab1.num_doc = tab0.num_doc
                and tab1.id_mae_fondos = tab0.id_mae_fondos
                and tab1.id_concep_nomina = tab0.id_concep_nomina
                and tab1.id_clase_fondo = 2
                and tab1.tipo_planilla = 'A'

                where tab0.id_periodo = $id_periodo 
                and tab0.id_clase_fondo = 2
                and tab0.tipo_planilla = 'C'   

                group by tab0.num_doc
        )salud1 ON salud1.num_doc = tab0.num_doc

        /*
        Poner los valores de ARL  de 60267402, asi sean los mismos antes y despues. != por >= - 20150727
        Se vuelve a dejar para que deje solo diferentes - 20150827
        */
        left join 
        (
            select 

             tab0.num_doc, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then sum(tab0.aporte_func) else 0 end as aporte, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then max(tab0.num_dias) else 0 end as dias,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 then max(tab0.tarifa)/100 else 0 end as tarifa,
             --case when 1 != 0 then max(tab0.tarifa)/100 else 0 end as tarifa,
             sum(tab0.aporte_patrono) as aporte_patrono_a,      
             sum(tab1.aporte_patrono) as aporte_patrono_c

             from no_liquida_aporte tab0

             left join no_liquida_aporte tab1
             on tab1.id_periodo = tab0.id_periodo
             and tab1.id_tipo_doc = tab0.id_tipo_doc
             and tab1.num_doc = tab0.num_doc
             and tab1.id_mae_fondos = tab0.id_mae_fondos
             and tab1.id_concep_nomina = tab0.id_concep_nomina
             and tab1.id_clase_fondo = 3
             and tab1.tipo_planilla = 'A'

             where tab0.id_periodo = $id_periodo 
             and tab0.id_clase_fondo = 3
             and tab0.tipo_planilla = 'C'   

             group by tab0.num_doc
        )riesgo1 ON riesgo1.num_doc = tab0.num_doc

        /*
        Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
        */
        left join 
        (
            select 

             tab0.num_doc, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
             sum(tab0.aporte_patrono) as aporte_patrono_a,      
             sum(tab1.aporte_patrono) as aporte_patrono_c

             from no_liquida_aporte tab0

             left join no_liquida_aporte tab1
             on tab1.id_periodo = tab0.id_periodo
             and tab1.id_tipo_doc = tab0.id_tipo_doc
             and tab1.num_doc = tab0.num_doc
             and tab1.id_mae_fondos = tab0.id_mae_fondos
             and tab1.id_concep_nomina = tab0.id_concep_nomina
             and tab1.id_clase_fondo = 4
             and tab1.tipo_planilla = 'A'

             where tab0.id_periodo = $id_periodo 
             and tab0.id_clase_fondo = 4
             and tab0.tipo_planilla = 'C'   

             group by tab0.num_doc
        )ccf1 ON ccf1.num_doc = tab0.num_doc

        /*
        Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
        */
        left join 
        (
            select 

             tab0.num_doc, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
             sum(tab0.aporte_patrono) as aporte_patrono_a,      
             sum(tab1.aporte_patrono) as aporte_patrono_c

             from no_liquida_aporte tab0

             left join no_liquida_aporte tab1
             on tab1.id_periodo = tab0.id_periodo
             and tab1.id_tipo_doc = tab0.id_tipo_doc
             and tab1.num_doc = tab0.num_doc
             and tab1.id_mae_fondos = tab0.id_mae_fondos
             and tab1.id_concep_nomina = tab0.id_concep_nomina
             and tab1.id_clase_fondo = 5
             and tab1.tipo_planilla = 'A'

             where tab0.id_periodo = $id_periodo 
             and tab0.id_clase_fondo = 5
             and tab0.tipo_planilla = 'C'   

             group by tab0.num_doc
        )icbf1 ON icbf1.num_doc = tab0.num_doc

        /*
        Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
        */
        left join 
        (
            select 

             tab0.num_doc, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
             sum(tab0.aporte_patrono) as aporte_patrono_a,      
             sum(tab1.aporte_patrono) as aporte_patrono_c

             from no_liquida_aporte tab0

             left join no_liquida_aporte tab1
             on tab1.id_periodo = tab0.id_periodo
             and tab1.id_tipo_doc = tab0.id_tipo_doc
             and tab1.num_doc = tab0.num_doc
             and tab1.id_mae_fondos = tab0.id_mae_fondos
             and tab1.id_concep_nomina = tab0.id_concep_nomina
             and tab1.id_clase_fondo = 6
             and tab1.tipo_planilla = 'A'

             where tab0.id_periodo = $id_periodo 
             and tab0.id_clase_fondo = 6
             and tab0.tipo_planilla = 'C'   

             group by tab0.num_doc
        )sena1 ON sena1.num_doc = tab0.num_doc

        /*
        Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
        */
        left join 
        (
            select 

             tab0.num_doc, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
             sum(tab0.aporte_patrono) as aporte_patrono_a,      
             sum(tab1.aporte_patrono) as aporte_patrono_c

             from no_liquida_aporte tab0

             left join no_liquida_aporte tab1
             on tab1.id_periodo = tab0.id_periodo
             and tab1.id_tipo_doc = tab0.id_tipo_doc
             and tab1.num_doc = tab0.num_doc
             and tab1.id_mae_fondos = tab0.id_mae_fondos
             and tab1.id_concep_nomina = tab0.id_concep_nomina
             and tab1.id_clase_fondo = 7
             and tab1.tipo_planilla = 'A'

             where tab0.id_periodo = $id_periodo 
             and tab0.id_clase_fondo = 7
             and tab0.tipo_planilla = 'C'   

             group by tab0.num_doc
        )esap1 ON esap1.num_doc = tab0.num_doc

        /*
        Se toman valores unicamente si el valor de los registros de A es menor a C - 20140507
        */
        left join 
        (
            select 

             tab0.num_doc, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_func) else 0 end as aporte, 
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then sum(tab0.aporte_patrono) else 0 end as aporte_patrono,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.num_dias) else 0 end as dias,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then MAX(nvl(tab0.valor_base,0)) else 0 end as IBC,
             case when sum(tab0.aporte_patrono) != sum(tab1.aporte_patrono) and max(tab0.num_dias) != 0 and sum(tab0.aporte_patrono) > sum(tab1.aporte_patrono) then max(tab0.tarifa)/100 else 0 end as tarifa,
             sum(tab0.aporte_patrono) as aporte_patrono_a,      
             sum(tab1.aporte_patrono) as aporte_patrono_c

             from no_liquida_aporte tab0

             left join no_liquida_aporte tab1
             on tab1.id_periodo = tab0.id_periodo
             and tab1.id_tipo_doc = tab0.id_tipo_doc
             and tab1.num_doc = tab0.num_doc
             and tab1.id_mae_fondos = tab0.id_mae_fondos
             and tab1.id_concep_nomina = tab0.id_concep_nomina
             and tab1.id_clase_fondo = 8
             and tab1.tipo_planilla = 'A'

             where tab0.id_periodo = $id_periodo 
             and tab0.id_clase_fondo = 8
             and tab0.tipo_planilla = 'C'   

             group by tab0.num_doc
        )men1 ON men1.num_doc = tab0.num_doc


        WHERE tab0.periodo = $id_periodo 
        and (tab0.actual = 1 or
          ( tab0.actual = 0 
            and tab5.fecha_retiro != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
            and tab5.fecha_termina
          between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
          )
        )
        and ( nvl(ccf1.dias,0) + nvl(salud1.dias,0) ) > 0
        
        
        union


/*
* Funcionarios con novedades de kardex que afecte antiguedad, tipo A
*/
select 
penfun.id_pension_func,
2 as c00,--order de mensual luego licencia
0 as c0,
'02' AS C1,
LPAD(rownum,5,0) AS C2,
'CC' AS C3,
RPAD(to_char(tab0.num_doc),16,' ') AS C4,
rpad(to_char(tab0.num_doc),16,' ') as c4a,
'01' AS C5,
case when penfun.id_pension_func is not null then '01' else '00' end AS C6,--suptipo de pensionado
' ' AS C7,
' ' AS C8,
RPAD(nvl(tab2.coddepto,' '),2,' ') AS C9,
RPAD(nvl(substr(tab3.codmun_text,3),' '),3,' ') AS C10,
RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11,
RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11a,
RPAD(nvl(tab4.segundo_apellido,' '),30,' ') AS C12,
RPAD(nvl(tab4.primer_nombre,' '),20,' ') AS C13,
RPAD(nvl(tab4.segundo_nombre,' '),30,' ') AS C14,
case when tab5.fecha_ingreso
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then ' ' else ' ' end AS C15,
case when tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
  then 'X' else ' ' end AS C16,
' ' AS C17,
' ' AS C18,
' ' AS C19,
' ' AS C20,
case when ascenso.cantidad>0 then 'X' else ' 'end AS C21,
'A' AS C22,
case when encargo.cantidad>0 then 'X' else ' 'end AS C23,
case when kardex1.cantidad>0 and ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then 'X' when kardex1.cantidad>0 and  nvl(max_marca,0) = 1 and nvl(max_marca_susinc,0) = 0 then 'C' else ' ' end AS C24,
case when kardex2.cantidad>0 then 'X' else ' 'end AS C25,--inc temp enfermedad
case when kardex3.cantidad>0 then 'X' else ' 'end AS C26,--lic maternidad
case when vaca.cantidad>0 then 'X' else ' 'end AS C27,
' ' AS C28,
' ' AS C29,
LPAD(to_char(nvl(kardex4.cantidad,0)),2,0) AS C30,--inc por accidente de trabajo
rpad(nvl(pension.codigo,' '),6,' ') as c31,
rPAD(' ',6,' ') AS C32,
rPAD(nvl(salud.codigo,' '),6,' ') AS C33,
rpad(' ',6,' ') as c34,
rPAD(nvl(ccf.codigo,' '),6,' ') AS C35,
lpad(30-nvl(pension1.dias,0) - nvl(30- case when to_number(to_char(tab5.fecha_termina,'dd')) != 31 then to_number(to_char(tab5.fecha_termina,'dd')) else 30 end ,0) ,2,0) as c36,
lpad(30-nvl(salud1.dias,0) - nvl(30- case when to_number(to_char(tab5.fecha_termina,'dd')) != 31 then to_number(to_char(tab5.fecha_termina,'dd')) else 30 end ,0) ,2,0) as c37,
LPAD(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0) - nvl(30- case when to_number(to_char(tab5.fecha_termina,'dd')) != 31 then to_number(to_char(tab5.fecha_termina,'dd')) else 30 end ,0) ,2,0) AS C38,
--39 dias parafiscales
LPAD(30-nvl(salud1.dias,0) - nvl(30- case when to_number(to_char(tab5.fecha_termina,'dd')) != 31 then to_number(to_char(tab5.fecha_termina,'dd')) else 30 end ,0) ,2,0) AS C39,
LPAD(nvl(tab6.asignacion_basica,0),9,0) AS C40,
' ' AS C41,
-- Se validan los dias de retiro del funionario - 20141007
LPAD(  round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3)  ,9,0) AS C42,
LPAD(  round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3)  ,9,0) AS C43,
LPAD(  round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3)  ,9,0) AS C44,
LPAD(  round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3)  ,9,0) AS C45,
-- Se debe calcular a partir de octubre la tarifa del patrono - 20131028
-- No debe llevar registro de pension - 20140729
rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as c46,
--rpad( case when nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then pension2.tarifa else 0 end ,0) = 0 then '0.0' when nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then pension2.tarifa else 0 end ,0) < 1 then '0' else '' end || + nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then pension2.tarifa else 0 end ,0) ,7,0) as C46,
lPAD(nvl(null,0) + nvl(null,0),9,0) AS C47,
--lpad( round( round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(pension2.dias-nvl(kardex4.cantidad,0),0))  ,-3) * nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then pension2.tarifa else 0 end ,0) ,-2) ,9,0) as c47,
lPAD(0,9,0) AS C48,
lpad(0,9,0) as c49,
lpad(nvl(null,0) + nvl(null,0),9,0) as C50,
lpad(nvl(null,0),9,0) as c51,
lpad(nvl(null,0) - nvl(null,0) ,9,0) as c52,
rpad(0,9,0) as c53,
rpad( case when nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then salud1.tarifa else 0 end ,0) = 0 then '0.0' when nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then salud1.tarifa else 0 end ,0) < 1 then '0' else '' end || + nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then salud1.tarifa else 0 end ,0) ,7,0) as c54,
lpad( round( round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3) * nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then salud1.tarifa else 0 end ,0) ,-2) ,9,0) as c55,
lpad( case when '01' = '40' then 0 else 0 end ,9,0) as c56,
rpad(' ',15,' ') as c57,
rpad(0,9,0) as C58,
rpad(' ',15,' ') as C59,
rpad(0,9,0) as c60,
rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,9,0) as C61,
lpad(0,9,0) as C62,
lpad( nvl(null,0) ,9,0) as c63,
rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) AS C64,
lpad( nvl(null,0) ,9,0) as c65,
rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as C66,
lpad( nvl(null,0) ,9,0) as c67,
rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as C68,
lpad( nvl(null,0) ,9,0) as c69,
rpad('0.0',7,0) as c70,
lpad(0,9,0) as c71,
rpad('0.0',7,0) as c72,
lpad(0,9,0) as C73,
'  ' AS C74,
RPAD(to_char(' '),16,' ') AS C75,
' ' AS C77

from no_estado_func_v tab0

left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
left join departamento tab2 ON tab2.id_depto = tab1.id_depto
left join municipios tab3 ON tab3.codmun = tab1.codmun
left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

left join (
    select * 
    from no_pension_func 
    where
    estado = 1
)penfun
on penfun.id_tipo_doc = tab0.id_tipo_doc 
and penfun.num_doc = tab0.num_doc

--left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
--Se valida que la fecha de terminacion sea en el periodo y no posterior, por calculo de meses nates del retiro - 20150724
left join (
  SELECT
    case when case when  aut.fecha_termina <= (select fecha_fin + 1 from no_periodo where id_periodo = $id_periodo) then aut.fecha_termina else null end
    between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
    then (30 - to_char(case when  aut.fecha_termina <= (select fecha_fin + 1 from no_periodo where id_periodo = $id_periodo) then aut.fecha_termina else null end,'dd') ) else 0 end as dias_retiro,
    case when  aut.fecha_termina <= (select fecha_fin + 1 from no_periodo where id_periodo = $id_periodo) then aut.fecha_termina else null end as fecha_termina,
     aut.fecha_retiro,
     aut.fecha_ingreso,
     aut.id_tipo_doc,
     aut.num_doc,
   ROW_NUMBER() OVER (
    partition by aut.num_doc
    ORDER BY aut.estado DESC, aut.fecha_ingreso DESC
   ) AS rn
  from no_nombramiento aut
)tab5
on tab5.id_tipo_doc = tab0.id_tipo_doc 
and tab5.num_doc = tab0.num_doc
and tab5.rn = 1

--left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp
/*
Se cambia para tomar valor de concepto_periodo para el concepto_nomina 1001 del periodo de tipo mensual o nomina adicional - 20130827
*/
left join(
    
    select * from(
            select 
            concep.id_tipo_doc,
            concep.num_doc,
            concep.valor as asignacion_basica ,
            
             ROW_NUMBER() OVER (
              PARTITION BY concep.num_doc
              order by per.id_tipo_periodo asc, concep.fecha_hasta desc
             ) as rn
           
            from no_concepto_periodo concep
  
            join no_periodo per
            on concep.PERIODO = per.id_periodo
            
            where 
            concep.id_concep_nomina = (select id_concep_basica from no_param_concep) 
            
            and (per.id_tipo_periodo = 1 or per.id_tipo_periodo = 2)
            and (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = concep.PERIODO) = (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = $id_periodo) 
    )where rn = 1
    
)tab6 ON tab6.id_tipo_doc = tab0.id_tipo_doc and tab6.num_doc = tab0.num_doc
and tab6.rn = 1

left join
(
  /* se cambia filtro por estado activo y id_tipo_novedad = 1 - 20150422 */
  select 
  tab0.num_doc,count(tab0.id_novedad_personal) as cantidad
  from no_novedad_personal tab0
  left join no_novedades nov on tab0.id_novedad = nov.id_novedad
  where 
  tab0.estado = 1 and nov.id_tipo_novedad = 1
  and tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
  group by tab0.num_doc
) ascenso ON ascenso.num_doc = tab0.num_doc

left join
(
    select num_doc, sum(cantidad) AS CANTIDAD
    from(
    
        select tab0.num_doc,count(tab0.id_novedad_personal) AS CANTIDAD
        from no_novedad_personal tab0
        where tab0.id_novedad IN ( 3, 7 )  
        and tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        group by tab0.num_doc
        
        union 
        
        select tab0.num_doc,count(tab0.id_bonifica_servicios) AS CANTIDAD
        from no_bonifica_servicios tab0
        where tab0.estado=1 and tab0.fecha_bonifica between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        group by tab0.num_doc
        
        union 
        
        select tab0.num_doc,count(tab0.id_hr_extra) AS CANTIDAD
        from no_hr_extra tab0
        left join no_lote_hr_extra tab1 ON tab1.id_lote_hr_extra = tab0.id_lote_hr_extra
        where tab1.estado=0 and tab1.id_periodo = $id_periodo
        group by tab0.num_doc
    
    )GROUP BY num_doc

) encargo ON encargo.num_doc = tab0.num_doc

left join
(
    select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD, max(tab1.suspension) as max_marca
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
    where tab1.afecta_antigu = 1 and tab0.estado = 1 
     /*   
    and (
      tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
      or
      tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
      or 
      tab0.id_periodo = $id_periodo
    )*/
    and
    (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
    and (
      to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
      or
      trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
      or 
      tab0.id_periodo = $id_periodo
    )
    group by tab0.num_doc
)kardex1 ON kardex1.num_doc = tab0.num_doc

left join
(
    select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
    /*
    Se cambia id_tip_nov_ka = 2 por 1 INCI
    Se deja quemado id 9 para novedad por ajuste de conceptos de reintegro ANM - 20140522
    Se cambia por id en tabla no_param_concep - 20140916
    */
    where tab1.id_tip_nov_ka in ((select distinct id_nov_ka_enfcom from no_param_concep),9) and tab0.estado = 1
    
    and tab0.estado = 1 and tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
    group by tab0.num_doc
)kardex2 ON kardex2.num_doc = tab0.num_doc

left join
(
    select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
    
    /*
    Se cambia id_tip_nov_ka = 3 por 2 INCI
    Se cambia por id en tabla no_param_concep - 20140916
    */
    where tab1.id_tip_nov_ka in ((select distinct id_nov_ka_licmat from no_param_concep)) and tab0.estado = 1 
    
    and tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
    group by tab0.num_doc
)kardex3 ON kardex3.num_doc = tab0.num_doc

/*
Se cambia de id-4 por atributo en  la tabla no_tip_nov_ka de afecta_riesgo, para validar cuales afectan en los dias de ARP-ARL - 20130919
*/
left join
(
    select tab0.num_doc, sum(tab0.num_dias) CANTIDAD
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
    where tab1.afecta_riesgo = 1 and tab0.estado = 1 and tab0.fecha_inc between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
    group by tab0.num_doc
)kardex4 ON kardex4.num_doc = tab0.num_doc

/*
Incapacidad -> Suspensión por Incapacidad  - 20150312
*/
left join
(
    select tab0.num_doc, count(tab0.id_novedad_kardex) as max_marca_susinc
    from no_novedad_kardex tab0
    left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
    where tab1.afecta_antigu = 1 and tab0.estado = 1 
    and tab0.id_tip_nov_ka = (select no_param_concep.id_nov_ka_susinc from no_param_concep where no_param_concep.id_param_concep = 1)
     /*   
    and (
      tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
      or
      tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
      and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
      or 
      tab0.id_periodo = $id_periodo
    )*/
    and
    (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
    and (
      to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
      or
      trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
      or 
      tab0.id_periodo = $id_periodo
    )
    group by tab0.num_doc
)kardex5 ON kardex5.num_doc = tab0.num_doc

left join
(
    select tab0.num_doc, count(tab0.id_novedad_vac) CANTIDAD
    from no_novedad_vac tab0
    where tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
    group by tab0.num_doc
)vaca ON vaca.num_doc = tab0.num_doc


LEFT JOIN 
(
    select distinct afilia.num_doc, codigo.codigo
    from no_liquida_aporte afilia
    left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
    left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
    where afilia.id_clase_fondo = 1 
    /*
    and  (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
    between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
    */--and afilia.estado = 1
   
    AND ( codigo.tipo != 'EPS' or codigo.tipo != 'CCF' )
    and afilia.id_periodo = $id_periodo
    and afilia.tipo_planilla = 'A'       
)pension ON pension.num_doc = tab0.num_doc

LEFT JOIN 
(
    select distinct afilia.num_doc, codigo.codigo
    from no_liquida_aporte afilia
    left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
    left join no_cod_fondo codigo ON codigo.nitocc = fondo.num_doc
    where afilia.id_clase_fondo = 2 
    /*
    and  (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
    between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
    --and afilia.estado = 1
    */
    AND codigo.tipo = 'EPS'
    and afilia.id_periodo = $id_periodo
    and afilia.tipo_planilla = 'A'       
)salud ON salud.num_doc = tab0.num_doc

LEFT JOIN 
(
    select distinct afilia.num_doc, afilia.centro_trabajo
    from no_afilia_fondo afilia
    where afilia.id_clase_fondo = 3 and afilia.estado = 1     
)riesgo ON riesgo.num_doc = tab0.num_doc

LEFT JOIN 
(
    select distinct afilia.num_doc, codigo.codigo
    from no_liquida_aporte afilia
    left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
    left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
    where afilia.id_clase_fondo = 4 
    --and afilia.estado = 1 
    AND codigo.tipo = 'CCF'
    and afilia.id_periodo = $id_periodo
    and afilia.tipo_planilla = 'A'       
)ccf ON ccf.num_doc = tab0.num_doc


left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1
    and tab0.tipo_planilla = 'A'       
    group by tab0.num_doc
)pension1 ON pension1.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.porcentaje_patrono/100) as TARIFA--, tab0.aporte_func AS APORTE
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1  and tab0.porcentaje_patrono > 0 -- and  ( tab0.tarifa = 16 or tab0.tarifa = 26 )
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc
)pension2 ON pension2.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, sum(tab0.valor_base_fsp) as aporte_fsp, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC/*,
    tab0.tarifa/100 as TARIFA, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.porcentaje_patrono = 0
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc
)pension3 ON pension3.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
    max(tab0.porcentaje_patrono)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 2
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc
)salud1 ON salud1.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 3
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc
)riesgo1 ON riesgo1.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 4
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc
)ccf1 ON ccf1.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 5
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc
)icbf1 ON icbf1.num_doc = tab0.num_doc

left join 
(
    select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 6
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc
)sena1 ON sena1.num_doc = tab0.num_doc


WHERE tab0.periodo = $id_periodo  
and (tab0.actual = 1 or
  ( tab0.actual = 0 
    /*
    Se cambia fecha de termina(ultimo dia de trabajo) por fecha de retiro, 
    para tener encuenta si trabajo en el periodo al menos 1 dia - 20130828                    
    */
    and tab5.fecha_retiro != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
  )
)
and kardex1.cantidad > 0
and ( nvl(ccf1.dias,0) + nvl(salud1.dias,0) ) > 0

      union
      
      
      /*
      * Funcionarios con novedades de kardex que afecte antiguedad , tipo C
      */
      select 
      penfun.id_pension_func,      
      2 as c00,--order de mensual luego licencia
      0 as c0,
      '02' AS C1,
      LPAD(rownum,5,0) AS C2,
      'CC' AS C3,
      RPAD(to_char(tab0.num_doc),16,' ') AS C4,
      rpad(to_char(tab0.num_doc),16,' ') as c4a,
      '01' AS C5,
      case when penfun.id_pension_func is not null then '01' else '00' end AS C6,--suptipo de pensionado
      ' ' AS C7,
      ' ' AS C8,
      RPAD(nvl(tab2.coddepto,' '),2,' ') AS C9,
      RPAD(nvl(substr(tab3.codmun_text,3),' '),3,' ') AS C10,
      RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11,
      RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11a,
      RPAD(nvl(tab4.segundo_apellido,' '),30,' ') AS C12,
      RPAD(nvl(tab4.primer_nombre,' '),20,' ') AS C13,
      RPAD(nvl(tab4.segundo_nombre,' '),30,' ') AS C14,
      case when tab5.fecha_ingreso
        between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        then ' ' else ' ' end AS C15,
      case when tab5.fecha_termina
        between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        then 'X' else ' ' end AS C16,
      ' ' AS C17,
      ' ' AS C18,
      ' ' AS C19,
      ' ' AS C20,
      case when ascenso.cantidad>0 then 'X' else ' 'end AS C21,
      'C' AS C22,
      case when encargo.cantidad>0 then 'X' else ' 'end AS C23,
      case when kardex1.cantidad>0 and ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then 'X' when kardex1.cantidad>0 and  nvl(max_marca,0) = 1 and nvl(max_marca_susinc,0) = 0 then 'C' else ' ' end AS C24,
      case when kardex2.cantidad>0 then 'X' else ' 'end AS C25,--inc temp enfermedad
      case when kardex3.cantidad>0 then 'X' else ' 'end AS C26,--lic maternidad
      case when vaca.cantidad>0 then 'X' else ' 'end AS C27,
      ' ' AS C28,
      ' ' AS C29,
      LPAD(to_char(nvl(kardex4.cantidad,0)),2,0) AS C30,--inc por accidente de trabajo
      rpad(nvl(pension.codigo,' '),6,' ') as c31,
      rPAD(' ',6,' ') AS C32,
      rPAD(nvl(salud.codigo,' '),6,' ') AS C33,
      rpad(' ',6,' ') as c34,
      rPAD(nvl(ccf.codigo,' '),6,' ') AS C35,
      lpad(30-nvl(pension1.dias,0) - nvl(30- case when to_number(to_char(tab5.fecha_termina,'dd')) != 31 then to_number(to_char(tab5.fecha_termina,'dd')) else 30 end ,0) ,2,0) as c36,
      lpad(30-nvl(salud1.dias,0) - nvl(30- case when to_number(to_char(tab5.fecha_termina,'dd')) != 31 then to_number(to_char(tab5.fecha_termina,'dd')) else 30 end ,0) ,2,0) as c37,
      LPAD(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0) - nvl(30- case when to_number(to_char(tab5.fecha_termina,'dd')) != 31 then to_number(to_char(tab5.fecha_termina,'dd')) else 30 end ,0) ,2,0) AS C38,
      --39 dias parafiscales
      LPAD(30-nvl(salud1.dias,0) - nvl(30- case when to_number(to_char(tab5.fecha_termina,'dd')) != 31 then to_number(to_char(tab5.fecha_termina,'dd')) else 30 end ,0) ,2,0) AS C39,
      LPAD(nvl(tab6.asignacion_basica,0),9,0) AS C40,
      ' ' AS C41,
      -- Se validan los dias de retiro del funionario - 20141007
      LPAD(  round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3)  ,9,0) AS C42,
      LPAD(  round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3)  ,9,0) AS C43,
      LPAD(  round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3)  ,9,0) AS C44,
      LPAD(  round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3)  ,9,0) AS C45,
      -- Se debe calcular a partir de octubre la tarifa del patrono - 20131028
      -- No debe llevar registro de pension - 20140729
      rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as c46,
      --rpad( case when nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then pension2.tarifa else 0 end ,0) = 0 then '0.0' when nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then pension2.tarifa else 0 end ,0) < 1 then '0' else '' end || + nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then pension2.tarifa else 0 end ,0) ,7,0) as C46,
      lPAD(nvl(null,0) + nvl(null,0),9,0) AS C47,
      --lpad( round( round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(pension2.dias-nvl(kardex4.cantidad,0),0))  ,-3) * nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then pension2.tarifa else 0 end ,0) ,-2) ,9,0) as c47,
      lPAD(0,9,0) AS C48,
      lpad(0,9,0) as c49,
      lpad(nvl(null,0) + nvl(null,0),9,0) as C50,
      lpad(nvl(null,0),9,0) as c51,
      lpad(nvl(null,0) - nvl(null,0) ,9,0) as c52,
      rpad(0,9,0) as c53,
      rpad( case when nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then salud1.tarifa else 0 end ,0) = 0 then '0.0' when nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then salud1.tarifa else 0 end ,0) < 1 then '0' else '' end || + nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then salud1.tarifa else 0 end ,0) ,7,0) as c54,
      lpad( round( round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0)- (tab5.dias_retiro) )  ,-3) * nvl( case when ( nvl(max_marca,0) = 0 or nvl(max_marca_susinc,0) = 1 ) then salud1.tarifa else 0 end ,0) ,-2) ,9,0) as c55,
      lpad( case when '01' = '40' then 0 else 0 end ,9,0) as c56,
      rpad(' ',15,' ') as c57,
      rpad(0,9,0) as C58,
      rpad(' ',15,' ') as C59,
      rpad(0,9,0) as c60,
      rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,9,0) as C61,
      lpad(0,9,0) as C62,
      lpad( nvl(null,0) ,9,0) as c63,
      rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) AS C64,
      lpad( nvl(null,0) ,9,0) as c65,
      rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as C66,
      lpad( nvl(null,0) ,9,0) as c67,
      rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) as C68,
      lpad( nvl(null,0) ,9,0) as c69,
      rpad('0.0',7,0) as c70,
      lpad(0,9,0) as c71,
      rpad('0.0',7,0) as c72,
      lpad(0,9,0) as C73,
      '  ' AS C74,
      RPAD(to_char(' '),16,' ') AS C75,
      ' ' AS C77
      
      from no_estado_func_v tab0
      
      left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
      left join departamento tab2 ON tab2.id_depto = tab1.id_depto
      left join municipios tab3 ON tab3.codmun = tab1.codmun
      left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

        left join (
            select * 
            from no_pension_func 
            where
            estado = 1
        )penfun
        on penfun.id_tipo_doc = tab0.id_tipo_doc 
        and penfun.num_doc = tab0.num_doc
      
      --left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
      --Se valida que la fecha de terminacion sea en el periodo y no posterior, por calculo de meses nates del retiro - 20150724
      left join (
        SELECT
        case when case when  aut.fecha_termina <= (select fecha_fin + 1 from no_periodo where id_periodo = $id_periodo) then aut.fecha_termina else null end
        between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
        then (30 - to_char(case when  aut.fecha_termina <= (select fecha_fin + 1 from no_periodo where id_periodo = $id_periodo) then aut.fecha_termina else null end,'dd') ) else 0 end as dias_retiro,
        case when  aut.fecha_termina <= (select fecha_fin + 1 from no_periodo where id_periodo = $id_periodo) then aut.fecha_termina else null end as fecha_termina,
         aut.fecha_retiro,
         aut.fecha_ingreso,
         aut.id_tipo_doc,
         aut.num_doc,
         ROW_NUMBER() OVER (
          partition by aut.num_doc
          ORDER BY aut.estado DESC, aut.fecha_ingreso DESC
         ) AS rn
        from no_nombramiento aut
      )tab5
      on tab5.id_tipo_doc = tab0.id_tipo_doc 
      and tab5.num_doc = tab0.num_doc
      and tab5.rn = 1
      
      --left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp
      /*
      Se cambia para tomar valor de concepto_periodo para el concepto_nomina 1001 del periodo de tipo mensual o nomina adicional - 20130827
      */
      left join(
          
          select * from(
                  select 
                  concep.id_tipo_doc,
                  concep.num_doc,
                  concep.valor as asignacion_basica ,
                  
                   ROW_NUMBER() OVER (
                    PARTITION BY concep.num_doc
                    order by per.id_tipo_periodo asc, concep.fecha_hasta desc
                   ) as rn
                 
                  from no_concepto_periodo concep
        
                  join no_periodo per
                  on concep.PERIODO = per.id_periodo
                  
                  where 
                  concep.id_concep_nomina = (select id_concep_basica from no_param_concep) 
                  
                  and (per.id_tipo_periodo = 1 or per.id_tipo_periodo = 2)
                  and (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = concep.PERIODO) = (select id_anhos || lpad(id_meses,2,'0') from no_periodo where id_periodo = $id_periodo) 
          )where rn = 1
          
      )tab6 ON tab6.id_tipo_doc = tab0.id_tipo_doc and tab6.num_doc = tab0.num_doc
      and tab6.rn = 1
      
      left join
      (
        /* se cambia filtro por estado activo y id_tipo_novedad = 1 - 20150422 */
        select 
        tab0.num_doc,count(tab0.id_novedad_personal) as cantidad
        from no_novedad_personal tab0
        left join no_novedades nov on tab0.id_novedad = nov.id_novedad
        where 
        tab0.estado = 1 and nov.id_tipo_novedad = 1
        and tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
        group by tab0.num_doc
      ) ascenso ON ascenso.num_doc = tab0.num_doc
      
      left join
      (
          select num_doc, sum(cantidad) AS CANTIDAD
          from(
          
              select tab0.num_doc,count(tab0.id_novedad_personal) AS CANTIDAD
              from no_novedad_personal tab0
              where tab0.id_novedad IN ( 3, 7 )  
              and tab0.fecha_inicial  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
              group by tab0.num_doc
              
              union 
              
              select tab0.num_doc,count(tab0.id_bonifica_servicios) AS CANTIDAD
              from no_bonifica_servicios tab0
              where tab0.estado=1 and tab0.fecha_bonifica between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
              and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
              group by tab0.num_doc
              
              union 
              
              select tab0.num_doc,count(tab0.id_hr_extra) AS CANTIDAD
              from no_hr_extra tab0
              left join no_lote_hr_extra tab1 ON tab1.id_lote_hr_extra = tab0.id_lote_hr_extra
              where tab1.estado=0 and tab1.id_periodo = $id_periodo
              group by tab0.num_doc
          
          )GROUP BY num_doc
      
      ) encargo ON encargo.num_doc = tab0.num_doc
      
      left join
      (
          select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD, max(tab1.suspension) as max_marca
          from no_novedad_kardex tab0
          left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
          where tab1.afecta_antigu = 1 and tab0.estado = 1 
           /*   
          and (
            tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
            and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
            or
            tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
            and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
            or 
            tab0.id_periodo = $id_periodo
          )*/
          and
          (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
          and (
            to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
            or
            trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
            or 
            tab0.id_periodo = $id_periodo
          )
          group by tab0.num_doc
      )kardex1 ON kardex1.num_doc = tab0.num_doc
      
      left join
      (
          select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
          from no_novedad_kardex tab0
          left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
          /*
          Se cambia id_tip_nov_ka = 2 por 1 INCI
          Se deja quemado id 9 para novedad por ajuste de conceptos de reintegro ANM - 20140522
          Se cambia por id en tabla no_param_concep - 20140916
          */
          where tab1.id_tip_nov_ka in ((select distinct id_nov_ka_enfcom from no_param_concep),9) and tab0.estado = 1
          
          and tab0.estado = 1 and tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          group by tab0.num_doc
      )kardex2 ON kardex2.num_doc = tab0.num_doc
      
      left join
      (
          select tab0.num_doc, count(tab0.id_novedad_kardex) CANTIDAD
          from no_novedad_kardex tab0
          left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
          
          /*
          Se cambia id_tip_nov_ka = 3 por 2 INCI
          Se cambia por id en tabla no_param_concep - 20140916
          */
          where tab1.id_tip_nov_ka in ((select distinct id_nov_ka_licmat from no_param_concep)) and tab0.estado = 1 
          
          and tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          group by tab0.num_doc
      )kardex3 ON kardex3.num_doc = tab0.num_doc
      
      /*
      Se cambia de id-4 por atributo en  la tabla no_tip_nov_ka de afecta_riesgo, para validar cuales afectan en los dias de ARP-ARL - 20130919
      */
      left join
      (
          select tab0.num_doc, case when sum(tab0.num_dias) > 30 then 30 else sum(tab0.num_dias) end  as CANTIDAD
          from no_novedad_kardex tab0
          left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
          where tab1.afecta_riesgo = 1 and tab0.estado = 1 and tab0.fecha_inc between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          group by tab0.num_doc
      )kardex4 ON kardex4.num_doc = tab0.num_doc
      
      /*
      Incapacidad -> Suspensión por Incapacidad  - 20150312
      */
      left join
      (
          select tab0.num_doc, count(tab0.id_novedad_kardex) as max_marca_susinc
          from no_novedad_kardex tab0
          left join no_tip_nov_ka tab1 ON tab1.id_tip_nov_ka = tab0.id_tip_nov_ka
          where tab1.afecta_antigu = 1 and tab0.estado = 1 
          and tab0.id_tip_nov_ka = (select no_param_concep.id_nov_ka_susinc from no_param_concep where no_param_concep.id_param_concep = 1)
           /*   
          and (
            tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
            and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
            or
            tab0.fecha_fin between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
            and trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) 
            or 
            tab0.id_periodo = $id_periodo
          )*/
          and
          (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = tab0.id_periodo) <= (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo)
          and (
            to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') between tab0.fecha_ini and tab0.fecha_fin
            or
            trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) between tab0.fecha_ini and tab0.fecha_fin
            or 
            tab0.id_periodo = $id_periodo
          )
          group by tab0.num_doc
      )kardex5 ON kardex5.num_doc = tab0.num_doc
      
      left join
      (
          select tab0.num_doc, count(tab0.id_novedad_vac) CANTIDAD
          from no_novedad_vac tab0
          where tab0.fecha_ini between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo) ,'dd/mm/yyyy') )) 
          group by tab0.num_doc
      )vaca ON vaca.num_doc = tab0.num_doc
      
      
      LEFT JOIN 
      (
          select distinct afilia.num_doc, codigo.codigo
          from no_liquida_aporte afilia
          left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
          left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
          where afilia.id_clase_fondo = 1 
          /*
          and  (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
          between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
          */--and afilia.estado = 1
         
          AND ( codigo.tipo != 'EPS' or codigo.tipo != 'CCF' )
          and afilia.id_periodo = $id_periodo
          and afilia.tipo_planilla = 'A'       
      )pension ON pension.num_doc = tab0.num_doc
      
      LEFT JOIN 
      (
          select distinct afilia.num_doc, codigo.codigo
          from no_liquida_aporte afilia
          left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
          left join no_cod_fondo codigo ON codigo.nitocc = fondo.num_doc
          where afilia.id_clase_fondo = 2 
          /*
          and  (select fecha_fin from no_periodo where id_periodo =  $id_periodo)
          between afilia.fecha_ingreso and nvl( afilia.fecha_retiro, trunc(last_day( to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy') )) )
          --and afilia.estado = 1
          */
          AND codigo.tipo = 'EPS'
          and afilia.id_periodo = $id_periodo
          and afilia.tipo_planilla = 'A'       
      )salud ON salud.num_doc = tab0.num_doc
      
      LEFT JOIN 
      (
          select distinct afilia.num_doc, afilia.centro_trabajo
          from no_afilia_fondo afilia
          where afilia.id_clase_fondo = 3 and afilia.estado = 1     
      )riesgo ON riesgo.num_doc = tab0.num_doc
      
      LEFT JOIN 
      (
          select distinct afilia.num_doc, codigo.codigo
          from no_liquida_aporte afilia
          left join no_mae_fondos fondo ON fondo.id_mae_fondos = afilia.id_mae_fondos
          left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
          where afilia.id_clase_fondo = 4 
          --and afilia.estado = 1 
          AND codigo.tipo = 'CCF'
          and afilia.id_periodo = $id_periodo
          and afilia.tipo_planilla = 'A'       
      )ccf ON ccf.num_doc = tab0.num_doc
      
      
      left join 
      (
          select tab0.num_doc, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
          max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1
          and tab0.tipo_planilla = 'A'       
          group by tab0.num_doc
      )pension1 ON pension1.num_doc = tab0.num_doc
      
      left join 
      (
          select tab0.num_doc, sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
          max(tab0.porcentaje_patrono/100) as TARIFA--, tab0.aporte_func AS APORTE
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1  and tab0.porcentaje_patrono > 0 -- and  ( tab0.tarifa = 16 or tab0.tarifa = 26 )
          and tab0.tipo_planilla = 'A'     
          group by tab0.num_doc
      )pension2 ON pension2.num_doc = tab0.num_doc
      
      left join 
      (
          select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, sum(tab0.valor_base_fsp) as aporte_fsp, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC/*,
          tab0.tarifa/100 as TARIFA, tab0.aporte_func AS APORTE*/
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.porcentaje_patrono = 0
          and tab0.tipo_planilla = 'A'     
          group by tab0.num_doc
      )pension3 ON pension3.num_doc = tab0.num_doc
      
      left join 
      (
          select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
          max(tab0.porcentaje_patrono)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 2
          and tab0.tipo_planilla = 'A'     
          group by tab0.num_doc
      )salud1 ON salud1.num_doc = tab0.num_doc
      
      left join 
      (
          select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
          max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 3
          and tab0.tipo_planilla = 'A'     
          group by tab0.num_doc
      )riesgo1 ON riesgo1.num_doc = tab0.num_doc
      
      left join 
      (
          select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
          max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 4
          and tab0.tipo_planilla = 'A'     
          group by tab0.num_doc
      )ccf1 ON ccf1.num_doc = tab0.num_doc
      
      left join 
      (
          select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
          max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 5
          and tab0.tipo_planilla = 'A'     
          group by tab0.num_doc
      )icbf1 ON icbf1.num_doc = tab0.num_doc
      
      left join 
      (
          select tab0.num_doc, sum(tab0.aporte_func) AS APORTE, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) AS DIAS, MAX(nvl(tab0.valor_base,0)) AS IBC,
          max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
          from no_liquida_aporte tab0
          where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 6
          and tab0.tipo_planilla = 'A'     
          group by tab0.num_doc
      )sena1 ON sena1.num_doc = tab0.num_doc
      
      
      WHERE tab0.periodo = $id_periodo  
      and (tab0.actual = 1 or
        ( tab0.actual = 0 
          /*
          Se cambia fecha de termina(ultimo dia de trabajo) por fecha de retiro, 
          para tener encuenta si trabajo en el periodo al menos 1 dia - 20130828                    
          */
          and tab5.fecha_retiro != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
          and tab5.fecha_termina
        between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
        and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
        )
      )
      and kardex1.cantidad > 0
      and ( nvl(ccf1.dias,0) + nvl(salud1.dias,0) ) > 0

)taborder

order by taborder.c11, taborder.c12, taborder.c13, taborder.c14, taborder.c00, taborder.c22, taborder.c24, taborder.c0

)tab
";

//                 echo $sql;



$st = $consul->execute($sql);
$detalle = $st->fetchAll();

//foreach ($detalle as $detalle) {
//
//    $shtml.=$detalle['C1'] . "" . "" . $detalle['C2'] . "" . $detalle['C3'] . "" . $detalle['C4'] . "" . $detalle['C5'] . "" . $detalle['C6'] . "" . $detalle['C7'] . ""
//            . $detalle['C8'] . "" . $detalle['C9'] . "" . $detalle['C10'] . "" . $detalle['C11'] . "" . $detalle['C12'] . "" . $detalle['C13'] . "" . $detalle['C14'] . ""
//            . $detalle['C15'] . "" . $detalle['C16'] . "" . $detalle['C17'] . "" . $detalle['C18'] . "" . $detalle['C19'] . "" . $detalle['C20'] . "" . $detalle['C21'] . ""
//            . $detalle['C22'] . "" . $detalle['C23'] . "" . $detalle['C24'] . "" . $detalle['C25'] . "" . $detalle['C26'] . "" . $detalle['C27'] . "" . $detalle['C28'] . ""
//            . $detalle['C29'] . "" . $detalle['C30'] . "" . $detalle['C31'] . "" . $detalle['C32'] . "" . $detalle['C33'] . "" . $detalle['C34'] . "" . $detalle['C35'] . ""
//            . $detalle['C36'] . "" . $detalle['C37'] . "" . $detalle['C38'] . "" . $detalle['C39'] . "" . $detalle['C40'] . "" . $detalle['C41'] . "" . $detalle['C42'] . ""
//            . $detalle['C43'] . "" . $detalle['C44'] . "" . $detalle['C45'] . "\n";
//}

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}


fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R3TOTAPP Total Fondos Pension        
$archivo = fopen("uploads/archivos_banco/r3totapp.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R301\tR302\tR303\tR304\tR305\tR306\tR307\tR308\tR309\tR310A\tR310\tR311\tR312\tR313\tR314A\tR314,C,6\t\n";


$sql = "
select 

'03' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10a,
tab.c10,
tab.c11,
tab.c12,
tab.c13,
tab.c14a,
tab.c14

from (

select 

lpad(tab0.codigo,6,0) as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(lpad(tab0.nitocc,16,0)) as c5,
lpad(round(sum(tab0.aporte2),-2),10,0) as c6,--pension
lpad(0,10,0) as c7,
lpad(0,10,0) as c8,
lpad(sum(tab0.aporte3),10,0) as c9,--fsp
lpad(sum(tab0.aporte3),10,0) as c10a,
lpad(0,4,0) as c10,
lpad(0,10,0) as c11,
lpad(0,10,0) as c12,
lpad(0,10,0) as c13,--total
lpad(round(sum(tab0.aporte2) + sum(tab0.aporte3),-2),10,0) as c14a,
LPAD( count (tab0.nitocc),6,0) AS C14

from (

select 

tab0.num_doc,
rpad(NVL(pension1.codigo, ' '),6,' ') AS codigo, 
rpad(nvl(pension1.tipo, ' '),15,' ') as tipo, 
nvl(pension1.nitocc,0) as nitocc,
--nvl(pension1.aporte,0) as aporte,
--nvl(pension1.aporte_patrono,0) as aporte_patrono,
nvl(pension1.aporte_func_c - pension1.aporte_func_a,0)as aporte,
nvl(pension1.aporte_patrono_c - pension1.aporte_patrono_a,0) as aporte_patrono,
nvl(pension2.aporte,0) as aporte2,
nvl(pension2.aporte_patrono,0) as aporte_patrono2,
nvl(pension3.aporte,0) as aporte3,
nvl(pension3.aporte_patrono,0) as aporte_patrono3

from no_estado_func_v tab0

left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
left join departamento tab2 ON tab2.id_depto = tab1.id_depto
left join municipios tab3 ON tab3.codmun = tab1.codmun
left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

--left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
left join (
  SELECT
   aut.*,
   ROW_NUMBER() OVER (
    partition by aut.num_doc
    ORDER BY aut.estado DESC
   ) AS rn
  from no_nombramiento aut
)tab5
on tab5.id_tipo_doc = tab0.id_tipo_doc 
and tab5.num_doc = tab0.num_doc
and tab5.rn = 1

left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/, 
    
      case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_func) else 0 end as aporte_func_a, 
      case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_func) else 0 end as aporte_func_c,     
      case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_a, 
      case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_c, 
      case when tab0.tipo_planilla = 'C' then 1 else 0 end as nitocc_c
      
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    
    --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.nitocc
        ORDER BY aut.nitocc 
       ) as rn
      from no_cod_fondo aut
    )codigo
    on codigo.nitocc = fondo.num_doc
    and codigo.rn = 1
    
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc, tab0.tipo_planilla
)pension1 ON pension1.num_doc = tab0.num_doc

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.porcentaje_patrono > 0 -- and  ( tab0.tarifa = 16 or tab0.tarifa = 26 )
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)pension2 ON pension2.num_doc = tab0.num_doc

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 1 and tab0.tarifa = 1
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)pension3 ON pension3.num_doc = tab0.num_doc


WHERE tab0.periodo = $id_periodo 
/*
Este usuario se deja quemado para que no salga - 20131022
*/
and (
  case when 
  tab0.num_doc = '52992037' and
  (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo) = '201301'
  then 1
  else 0
  end  
) =  0
and tab0.num_doc not in (select num_doc from no_pension_func where  estado = 1)
and (tab0.actual = 1 or
  ( tab0.actual = 0 
    and tab5.fecha_termina != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
  )
)

)tab0

where tab0.aporte > 0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab

";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R4TOTAPS Total Fondos Salud        
$archivo = fopen("uploads/archivos_banco/r4totaps.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R401\tR402\tR403\tR404\tR405\tR406\tR407\tR408\tR409\tR410\tR411\tR412\tR413\tR414\tR415\tR416\tR417\tR418\tR419\tR420\tR421\tR422\tR423\tR424\tR425,C,6\t\n";

$sql = "
select 

'04' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10,
tab.c11,
tab.c12,
tab.c13,
tab.c14,
tab.c15,
tab.c16,
tab.c17,
tab.c18,
tab.c19,
tab.c20,
tab.c21,
tab.c22,
tab.c23,
tab.c24,
tab.c25

from (

select 

lpad(tab0.codigo,6,0) as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(lpad(tab0.nitocc,16,0)) as c5,
lpad(round(sum(tab0.aporte),-2),10,0) as c6,
lpad(sum(tab0.upc_adicio),10,0) as c7,
LPAD(' ',15,' ') as c8,
lpad(0,10,0) as c9,
LPAD(' ',15,' ') as c10,
lpad(0,10,0) as c11,
lpad(round(sum(tab0.aporte) + 0 + 0,-2) ,10,0) as c12,
lpad(0,4,0) as c13,
lpad(0,10,0) AS C14,
lpad(0,10,0) as c15,
lpad(round(sum(tab0.aporte),-2),10,0) as c16,
lpad(round(sum(tab0.upc_adicio),-2),10,0) as c17,
LPAD(0,10,0) as c18,
lpad(0,10,0) as c19,
lpad(0,10,0) as c20,
lpad(round(sum(tab0.aporte) + 0 + 0,-2) ,10,0) as c21,
lpad(sum(tab0.upc_adicio),10,0) as c22,
lpad(round(sum(tab0.upc_adicio) + sum(tab0.aporte) + 0 + 0,-2),10,0) as c23,
lpad( round((sum(tab0.aporte) * 1 / 100) + 0 + 0) ,10,0) as c24,
LPAD( count (tab0.nitocc),6,0) as c25

from (

select 

tab0.num_doc,
rpad(NVL(salud1.codigo, ' '),6,' ') AS codigo, 
rpad(nvl(salud1.tipo, ' '),15,' ') as tipo, 
nvl(salud1.nitocc,0) as nitocc,
--nvl(salud1.aporte,0) as aporte,
--nvl(salud1.aporte_patrono,0) as aporte_patrono,
nvl(salud1.aporte_func_c - salud1.aporte_func_a,0)as aporte,
nvl(salud1.aporte_patrono_c - salud1.aporte_patrono_a,0) as aporte_patrono,
nvl(salud1.upc_adicio,0) as upc_adicio

from no_estado_func_v tab0

left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
left join departamento tab2 ON tab2.id_depto = tab1.id_depto
left join municipios tab3 ON tab3.codmun = tab1.codmun
left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

--left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
left join (
  SELECT
   aut.*,
   ROW_NUMBER() OVER (
    partition by aut.num_doc
    ORDER BY aut.estado DESC
   ) AS rn
  from no_nombramiento aut
)tab5
on tab5.id_tipo_doc = tab0.id_tipo_doc 
and tab5.num_doc = tab0.num_doc
and tab5.rn = 1

left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA, sum(tab0.upc_adicio) as upc_adicio, 
    
      case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_func) else 0 end as aporte_func_a, 
      case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_func) else 0 end as aporte_func_c,     
      case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_a, 
      case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_c, 
      case when tab0.tipo_planilla = 'C' then 1 else 0 end as nitocc_c
    
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    
    --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.nitocc
        ORDER BY aut.nitocc 
       ) as rn
      from no_cod_fondo aut
    )codigo
    on codigo.nitocc = fondo.num_doc
    and codigo.rn = 1
    
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 2 AND codigo.tipo = 'EPS'
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc, tab0.tipo_planilla
)salud1 ON salud1.num_doc = tab0.num_doc


WHERE tab0.periodo = $id_periodo 
/*
Este usuario se deja quemado para que no salga - 20131022
*/
and (
  case when 
  tab0.num_doc = '52992037' and
  (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo) = '201301'
  then 1
  else 0
  end  
) =  0
and (tab0.actual = 1 or
  ( tab0.actual = 0 
    and tab5.fecha_termina != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
  )
)

)tab0

where tab0.aporte > 0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R5TOTAPR Total Fondos Riesgos        
$archivo = fopen("uploads/archivos_banco/r5totapr.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R501\tR502\tR503\tR504\tR505\tR506\tR507\tR508\tR509\tR510\tR511\tR512\tR513\tR514,\tR515\tR516\tR517\tR518\t\n";

/**
 * Solo muestra el registro si hay valor de descuento de autorizacion - 20141125
 */
$sql = "
select 

'05' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10,
tab.c11,
tab.c12,
tab.c13,
tab.c14,
tab.c15,
tab.c16,
tab.c17,
tab.c18

from (

select 

lpad(tab0.codigo,6,' ') as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(tab0.nitocc) as c5,
lpad(sum(tab0.aporte_patrono),10,0) as c6,
lpad(nvl(tab0.num_autorizacion,' '),15,' ') as c7,
lpad(nvl(tab0.valor_total,0),10,0) as c8,
lpad(nvl(tab0.valor_aporte,0),10,0) as c9,
lpad(sum(tab0.aporte_patrono) - nvl(tab0.valor_total,0) - nvl(tab0.valor_aporte,0),10,0) as c10,
lpad(0,4,0) as c11,
lpad(0,10,0) as c12,
lpad(sum(tab0.aporte_patrono) - nvl(tab0.valor_total,0) - nvl(tab0.valor_aporte,0),10,0) as c13,
lpad(' ',10,' ') as c14,
lpad(0,10,0) as c15,
lpad(sum(tab0.aporte_patrono) - nvl(tab0.valor_total,0) - nvl(tab0.valor_aporte,0),10,0) as c16,
lpad( round((sum(tab0.aporte_patrono) * 1 / 100) ),10,0) as c17,
LPAD( count (tab0.nitocc_c),6,0) as c18

from (

select 
autori.num_autorizacion,
autori.valor_total,
autori.valor_aporte,
tab0.num_doc,
rpad(NVL(riesgo1.codigo, ' '),6,' ') AS codigo, 
rpad(nvl(riesgo1.tipo, ' '),15,' ') as tipo, 
nvl(riesgo1.nitocc,0) as nitocc,
nvl(riesgo1.nitocc_c,0) as nitocc_c,
nvl(riesgo1.aporte,0) as aporte,
--nvl(riesgo1.aporte_patrono,0) as aporte_patrono,
nvl(riesgo1.aporte_patrono_c - riesgo1.aporte_patrono_a,0) as aporte_patrono

from no_estado_func_v tab0

left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
left join departamento tab2 ON tab2.id_depto = tab1.id_depto
left join municipios tab3 ON tab3.codmun = tab1.codmun
left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

--left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
left join (
  SELECT
   aut.*,
   ROW_NUMBER() OVER (
    partition by aut.num_doc
    ORDER BY aut.estado DESC
   ) AS rn
  from no_nombramiento aut
)tab5
on tab5.id_tipo_doc = tab0.id_tipo_doc 
and tab5.num_doc = tab0.num_doc
and tab5.rn = 1

left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/, 
    
      case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_a, 
      case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_c, 
      case when tab0.tipo_planilla = 'C' then 1 else 0 end as nitocc_c
      
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    
    --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.nitocc
        ORDER BY aut.nitocc 
       ) as rn
      from no_cod_fondo aut
    )codigo
    on codigo.nitocc = fondo.num_doc
    and codigo.rn = 1
    
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 3
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc, tab0.tipo_planilla
)riesgo1 ON riesgo1.num_doc = tab0.num_doc


left join (
select 
tab1.valor as valor_total,
tab1.aporte_eps + tab1.aporte_afp as valor_aporte,
rownum as rn, 
tab.* 

from(

  select 
  autori.* 
  from no_autorizacion autori
  where autori.id_periodo = $id_periodo
  order by 
  autori.fecha_proceso desc, autori.id_autorizacion desc

)tab

join (
  select 
  sum(nvl(aporte_eps,0)) as aporte_eps, 
  sum(nvl(aporte_afp,0)) as aporte_afp, 
  sum(nvl(valor,0)) as valor, 
  id_periodo 
  from no_autorizacion 
  group by   id_periodo
)tab1 on tab1.id_periodo = tab.id_periodo

where
rownum = 1
)autori on autori.rn = 1 

WHERE tab0.periodo = $id_periodo 
/*
Este usuario se deja quemado para que no salga - 20131022
*/
and (
  case when 
  tab0.num_doc = '52992037' and
  (select id_anhos || lpad(id_meses,2,'0') as vigper from no_periodo where id_periodo = $id_periodo) = '201301'
  then 1
  else 0
  end  
) =  0
and (tab0.actual = 1 or
  ( tab0.actual = 0 
    and tab5.fecha_termina != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
  )
)

)tab0

where tab0.aporte_patrono > 0
and tab0.valor_total != 0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc, tab0.num_autorizacion, tab0.valor_total, tab0.valor_aporte

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";


//ARCHIVO R6TOTAPC Total Fondos CCF        
$archivo = fopen("uploads/archivos_banco/r6totapc.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R601\tR602\tR603\tR604\tR605\tR606\tR607\tR608\tR609\tR610\t\n";

$sql = "
select 

'06' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10

from (

select 

lpad(tab0.codigo,6,' ') as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(tab0.nitocc) as c5,
lpad(sum(tab0.aporte_patrono),10,0) as c6,
lpad(0,4,0) as c7,
lpad(0,10,0) as c8,
lpad(sum(tab0.aporte_patrono),10,0) as c9,
LPAD( sum (tab0.nitocc_c),6,0) as c10

from (

select 

tab0.num_doc,
rpad(NVL(ccf1.codigo, ' '),6,' ') AS codigo, 
rpad(nvl(ccf1.tipo, ' '),15,' ') as tipo, 
nvl(ccf1.nitocc,0) as nitocc,
nvl(ccf1.nitocc_c,0) as nitocc_c,
nvl(ccf1.aporte,0) as aporte,
nvl(ccf1.aporte_patrono_c - ccf1.aporte_patrono_a,0) as aporte_patrono

from no_estado_func_v tab0

left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
left join departamento tab2 ON tab2.id_depto = tab1.id_depto
left join municipios tab3 ON tab3.codmun = tab1.codmun
left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

--left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
left join (
  SELECT
   aut.*,
   ROW_NUMBER() OVER (
    partition by aut.num_doc
    ORDER BY aut.estado DESC
   ) AS rn
  from no_nombramiento aut
)tab5
on tab5.id_tipo_doc = tab0.id_tipo_doc 
and tab5.num_doc = tab0.num_doc
and tab5.rn = 1

left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/, 
    
      case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_a, 
      case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_c, 
      case when tab0.tipo_planilla = 'C' then 1 else 0 end as nitocc_c
      
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    
    --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.nitocc
        ORDER BY aut.nitocc 
       ) as rn
      from no_cod_fondo aut
    )codigo
    on codigo.nitocc = fondo.num_doc
    and codigo.rn = 1
    
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 4 AND codigo.tipo = 'CCF'
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc, tab0.tipo_planilla
)ccf1 ON ccf1.num_doc = tab0.num_doc


WHERE tab0.periodo = $id_periodo 
and (tab0.actual = 1 or
  ( tab0.actual = 0 
    and tab5.fecha_termina != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
  )
)

)tab0

where tab0.aporte_patrono > 0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R7TOTAPS Total Fondos SENA        
$archivo = fopen("uploads/archivos_banco/r7totapc.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R701\tR702\tR703\tR704\tR706\tR707\tR708\tR709\tR710\t\n";

$sql = "
select 

'07' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10

from (

select 

lpad(null,6,' ') as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(tab0.nitocc) as c5,
lpad(sum(tab0.aporte_patrono),10,0) as c6,
lpad(0,4,0) as c7,
lpad(0,10,0) as c8,
lpad(sum(tab0.aporte_patrono),10,0) as c9,
LPAD( sum (tab0.nitocc_c),6,0) as c10

from (

select 

tab0.num_doc,
rpad(NVL(sena1.codigo, ' '),6,' ') AS codigo, 
rpad(nvl(sena1.tipo, ' '),15,' ') as tipo, 
nvl(sena1.nitocc,0) as nitocc,
nvl(sena1.nitocc_c,0) as nitocc_c,
nvl(sena1.aporte,0) as aporte,
nvl(sena1.aporte_patrono_c - sena1.aporte_patrono_a,0) as aporte_patrono

from no_estado_func_v tab0

left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
left join departamento tab2 ON tab2.id_depto = tab1.id_depto
left join municipios tab3 ON tab3.codmun = tab1.codmun
left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

--left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
left join (
  SELECT
   aut.*,
   ROW_NUMBER() OVER (
    partition by aut.num_doc
    ORDER BY aut.estado DESC
   ) AS rn
  from no_nombramiento aut
)tab5
on tab5.id_tipo_doc = tab0.id_tipo_doc 
and tab5.num_doc = tab0.num_doc
and tab5.rn = 1

left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/, 
    
      case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_a, 
      case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_c, 
      case when tab0.tipo_planilla = 'C' then 1 else 0 end as nitocc_c
      
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    
    --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.nitocc
        ORDER BY aut.nitocc 
       ) as rn
      from no_cod_fondo aut
    )codigo
    on codigo.nitocc = fondo.num_doc
    and codigo.rn = 1
    
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 6
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc, tab0.tipo_planilla
)sena1 ON sena1.num_doc = tab0.num_doc


WHERE tab0.periodo = $id_periodo 
and (tab0.actual = 1 or
  ( tab0.actual = 0 
    and tab5.fecha_termina != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
  )
)

)tab0

where tab0.aporte_patrono > 0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

//ARCHIVO R8TOTAPI Total Fondos ICBF        
$archivo = fopen("uploads/archivos_banco/r8totapi.csv", "w"); //generacion del nuevo documento txt.
//$shtml = "R801\tR802\tR803\tR804\tR806\tR807\tR808\tR809\tR810\t\n";

$sql = "
select 

'08' as c1,
lpad(rownum,5,0) as c2,
tab.c3,
tab.C4,
tab.C5,
tab.c6,
tab.c7,
tab.c8,
tab.c9,
tab.c10

from (

select 

lpad(null,6,' ') as c3,
lpad(tab0.nitocc,16,0) as c4,
digito_verificacion(tab0.nitocc) as c5,
lpad(sum(tab0.aporte_patrono),10,0) as c6,
lpad(0,4,0) as c7,
lpad(0,10,0) as c8,
lpad(sum(tab0.aporte_patrono),10,0) as c9,
LPAD( sum (tab0.nitocc_c),6,0) as c10

from (

select 

tab0.num_doc,
rpad(NVL(icbf1.codigo, ' '),6,' ') AS codigo, 
rpad(nvl(icbf1.tipo, ' '),15,' ') as tipo, 
nvl(icbf1.nitocc,0) as nitocc,
nvl(icbf1.nitocc_c,0) as nitocc_c,
nvl(icbf1.aporte,0) as aporte,
nvl(icbf1.aporte_patrono_c - icbf1.aporte_patrono_a,0) as aporte_patrono

from no_estado_func_v tab0

left join centro_operacion tab1 ON tab1.id_cen_op = tab0.id_cen_op
left join departamento tab2 ON tab2.id_depto = tab1.id_depto
left join municipios tab3 ON tab3.codmun = tab1.codmun
left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = tab0.id_tipo_doc and tab4.num_doc = tab0.num_doc

--left join no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
left join (
  SELECT
   aut.*,
   ROW_NUMBER() OVER (
    partition by aut.num_doc
    ORDER BY aut.estado DESC
   ) AS rn
  from no_nombramiento aut
)tab5
on tab5.id_tipo_doc = tab0.id_tipo_doc 
and tab5.num_doc = tab0.num_doc
and tab5.rn = 1

left join no_dp_v tab6 on tab6.id_dp = tab0.id_dp

left join 
(
    select 
    tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc,
    sum(tab0.aporte_func) as aporte, sum(tab0.aporte_patrono) as aporte_patrono, max(tab0.num_dias) as dias, max(nvl(tab0.valor_base,0)) as ibc,
    max(tab0.tarifa)/100 as TARIFA/*, tab0.aporte_func AS APORTE*/, 
    
      case when tab0.tipo_planilla = 'A' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_a, 
      case when tab0.tipo_planilla = 'C' then sum(tab0.aporte_patrono) else 0 end as aporte_patrono_c, 
      case when tab0.tipo_planilla = 'C' then 1 else 0 end as nitocc_c
      
    from no_liquida_aporte tab0
    left join no_mae_fondos fondo on fondo.id_mae_fondos = tab0.id_mae_fondos
    
    --left join no_cod_fondo codigo on codigo.nitocc = fondo.num_doc  
    left join (
      SELECT
       aut.*,
       ROW_NUMBER() OVER (
        partition by aut.nitocc
        ORDER BY aut.nitocc 
       ) as rn
      from no_cod_fondo aut
    )codigo
    on codigo.nitocc = fondo.num_doc
    and codigo.rn = 1
    
    where tab0.id_periodo = $id_periodo and tab0.id_clase_fondo = 5
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc, tab0.tipo_planilla
)icbf1 ON icbf1.num_doc = tab0.num_doc


WHERE tab0.periodo = $id_periodo 
and (tab0.actual = 1 or
  ( tab0.actual = 0 
    and tab5.fecha_termina != to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
    and tab5.fecha_termina
  between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $id_periodo) ,'dd/mm/yyyy')
  and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $id_periodo_actual) ,'dd/mm/yyyy') ))
  )
)

)tab0

where tab0.aporte_patrono > 0

group by 
tab0.codigo, tab0.tipo, tab0.nitocc

order by
tab0.codigo

)tab
 
";

$st = $consul->execute($sql);
$detalle = $st->fetchAll();

/*
 * recorrer arreglo de datos y convertir en csv
 */
$i = 0;
$j = 0;
$sep = ""; //variable global para manejo del separador
foreach ($detalle as $rec) {
//        $shtml.='"';
    foreach ($rec as $key => $value) {
        if ($j != 0) {
//            if ($value != '/') {
            $shtml.= $sep;
//            }
        }
//        $value = str_replace('Ñ', '#', $value);
//        $value = str_replace('ñ', '#', $value);
//        $value = str_replace('[\n|\r|\n\r]', ' ', $value);
        $shtml.= $value;
        $j++;
    }
//        $shtml.='"';
//        $this->logMessage("Linea: " . $i++);
    //salto de linea para separar totales
    $shtml.=$salto_linea;
    $j = 0;
}

fwrite($archivo, $shtml);
fclose($archivo);

$shtmlAuto .= $shtml;
$shtml = "";

////ARCHIVO R9TOTAPE ?        
//$archivo = fopen("uploads/archivos_banco/r9totape.csv", "w"); //generacion del nuevo documento txt.
//$shtml .= "R901\tR902\tR903\tR904\tR905\tR906\tR907\tR908\t\n";
//fwrite($archivo, $shtml);
//fclose($archivo);
//
////ARCHIVO R10TOTAM ?        
//$archivo = fopen("uploads/archivos_banco/r10totam.csv", "w"); //generacion del nuevo documento txt.
//$shtml .= "R1001\tR1002\tR1003\tR1004\tR1005\tR1006\tR1007\tR1008\t\n";
//fwrite($archivo, $shtml);
//fclose($archivo);
//
////ARCHIVO R11TOTPP ?       
//$archivo = fopen("uploads/archivos_banco/r11totpp.csv", "w"); //generacion del nuevo documento txt.
//$shtml .= "R1101\tR1102\tR1103\tR1104\tR1105\tR1106\tR1107\tR1108\tR1109\tR1110\tR1111\tR1112\tR1113\tR1114\t\n";
//fwrite($archivo, $shtml);
//fclose($archivo);


/*
 * guardar y cerrar archivo
 */
//$shtmlAuto.=$salto_linea;
fwrite($fp, $shtmlAuto);
fclose($fp);


/*
 * salida csv
 */
echo "<script>
    window.location='" . "/nomina/web/" . $sfile . "'</script>";
die();
?>