<?

//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');
//SGC-1, ANM-2, SGR-3, CYC-4, COL-5, INCI-6, LOCAL-7 - 20151026
$entidad_cliente = sfConfig::get('app_entidad_cliente');

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

$sfile = $scarpeta . "/archivos_banco/PlanillaAutoliquidacion_" . 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";

/**
 *Campo c8 se pone blanco en vez de 0, segun la $entidad_cliente - 20151026
 */
$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,
'E' AS C7,
case when $entidad_cliente in (4) then lpad(' ',10,' ') else lpad(0,10,0) end AS C8,
--lpad(0,10,0) AS C8,
rPAD(' ',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 + kardex1.cant_ka - salud1.cant_ka,5,0) as c18,
--lpad(kardex1.cant_ka + salud1.cant_ka,5,0) as c18,
lpad(tab.cant,5,0) as c18,
lpad(icbf1.total + nvl(kardex4.valor,0),12,0) as c19,
lpad(1,1,0) as c20,
lpad(0,2,0) as c21

from datos_compania tab0

left join 
(
    select 
    count(total.num_doc) as cant, 
    sum(total.total) as total 
    from (
      select 
      tab0.num_doc as num_doc, 
      sum(tab0.aporte_func) as total 
      from no_liquida_aporte tab0
      where tab0.id_periodo = $id_periodo
      and tab0.tipo_planilla = 'A'
      group by 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

left join 
(
    select 
    count(total.num_doc) as cant, 
    sum(total.aporte_ibc) as total1, 
    sum(total.IBC) as total 
    from (
        select 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*/
        from no_liquida_aporte tab0
        where tab0.id_periodo = $id_periodo 
        and tab0.tipo_planilla = 'A' 
        and tab0.id_clase_fondo = 5
        group by tab0.num_doc
   )total   
)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    
    nvl(sum( round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(salud1.dias-nvl(kardex4.cantidad,0),0))  ,-3) ),0) 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, 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
    (
    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, 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

    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.tipo_planilla = 'A'
        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.tipo_planilla = 'A'
      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 .= "";

/*
 * Para riesgo se valida que si no hay dias el valor de riesgo es 0 - 20130729
 * Se activan campo C74 y C75 datos UPC - 20140730
 * Con el fin de retirar de la lógica de la autoliquidación la parte donde estamos tomando el porcentaje de aporte de pension. 
 * DEBE CAMBIAR asi: --- Fondo de pension:  ID_CLASE_FONDO=1   Y  PORCENTAJE_PATRONO > 0 
 * --- FSP:  ID_CLASE_FONDO=1   Y  PORCENTAJE_PATRONO=0 -- 20140910
 * En el registro de marca  por novedad de vacaciones,  
 * no se deben tomar periodos de m,eses posteriores al mes de la liquidación.   
 * Ejemplo en septiembre 2014  registraron una novedad de vacaciones  que afecta agosto  y la está marcando.      
 * El control radica en filtrar las novedades de vacaciones registradas en periodos anterior o igual al mes de liquidación. - 20140910
 * Por favor actualizar  el archivo de Planilla de autoliquidación para quie en la posición 506  tenga "N" - 20141120 
 */
$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,
tab.c36,
tab.c37,
tab.c38,
--tab.c39, dias parafiscales
case when to_number(tab.c45) = 0 then LPAD(0,2,0) else tab.c39 end c39,
tab.c40,
tab.c41,
tab.c42,
tab.c43,
--tab.c44,
case when to_number(tab.c38) = 0 then LPAD(0,9,0) else tab.c44 end 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,
case when to_number(tab.c38) = 0 then LPAD('0.0000000',9,0) else tab.c61 end 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 

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,
' ' AS C22,
case when encargo.cantidad>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,

/*
Se pone codigo de pension de traslado si la fecha de retiro del fondo esta dentro del periodo a generar la planilla 
se toma del id de la tabla no_liquida_aporte - 20130826
*/
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,

/*
Se pone codigo de salud de traslado si la fecha de retiro del fondo esta dentro del periodo a generar la planilla
se toma del id de la tabla no_liquida_aporte - 20130826
*/
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,
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 and 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
--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) 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(pension2.tarifa,0) = 0 then '0.0' when nvl(pension2.tarifa,0) < 1 then '0' else '' end || + nvl(pension2.tarifa,0) ,7,0) as C46,
lPAD(nvl(pension2.aporte,0) + nvl(pension2.aporte_patrono,0),9,0) AS C47,
lPAD(0,9,0) AS C48,
lpad(0,9,0) as C49,
lpad(nvl(pension2.aporte,0) + nvl(pension2.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, 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

/*
Variacion de salario
*/
left join
(
    select num_doc, sum(cantidad) AS CANTIDAD
    from(
        /*
        Novedad de encargo
        Tiene en cuenta todo el tiempo de la novedad desde empieza hasta que termina - 20130917
        */
        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.estado = 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') )) 
          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 
        /*
        Identifica bonificacion por servicios
        */
        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 
        /*
        Verifica horas extras en la tabla
        */
        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, 1021, 1023 y 4001 - 20130826
        --Urgente cambiar por parametrizar agrupacion por conceptos - 20130826
        --Se cambia por agrupacion 105-CONCEPTOS DE VARIACIÓN TRANSITORIA PARA LA PLANILLA - 20130902
        select tab0.num_doc,count(tab0.id_liquida_nomina) as cantidad
        from no_liquida_nomina tab0
        where 
        --tab0.id_periodo = $id_periodo
        --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 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
    
    )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 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
)kardex1 ON kardex1.num_doc = tab0.num_doc

left join
(
    /*Se cambia validacion para tener encuenta dias realmente de la novedad segun prorroga - 20150223*/
    select tab0.num_doc, count(tab0.id_novedad_kardex) CANT,
    sum( case when tab0.num_dias > 0 and tab0.prorroga = 1 then 1 when tab0.num_dias > (tab1.dias_patrono) and tab0.prorroga = 0 then 1 else 0 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
    /*
    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, 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') )) 
        /*
        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

/*
Se coloca, tab0.id_periodo en la agrupacion, para tener encuenta todas las novedades - 20161104
*/
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.ID_TIPO_NOV_VAC = 1 and
       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, tab0.id_periodo

   ) 

   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 as id_centro_trabajo, centra.codigo as centro_trabajo
    from no_afilia_fondo afilia
    left join no_centro_trabajo centra
    on centra.id_centro_trabajo = afilia.centro_trabajo
    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.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.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.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
    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

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 = 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, 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 = 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 salud1.dias > 0

union


/*
* Funcionarios con novedades de kardex que afecte antiguedad
*/
select 

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,
'00' AS C6,
' ' 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 ' ' else ' ' end AS C16,
' ' AS C17,
' ' AS C18,
' ' AS C19,
' ' AS C20,
--No La novedad SLN no debe de combinarse con otro tipo de novedad  - 20161122
case when ascenso.cantidad>0 then ' ' else ' 'end AS C21,
' ' AS C22,
case when encargo.cantidad>0 then ' ' 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 ' ' else ' 'end AS C25,--inc temp enfermedad
case when kardex3.cantidad>0 then ' ' 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
-- Se coloca que para CYC, si debe llevar registro de pension - 20170127
--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,
case when $entidad_cliente in (4) then (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)) else rpad( case when nvl(null,0) = 0 then '0.0' when nvl(null,0) < 1 then '0' else '' end || + nvl(null,0) ,7,0) end 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,
case when $entidad_cliente in (4) then (lpad( round( round( nvl(tab6.asignacion_basica,0)/30*(30-nvl(pension2.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 pension2.tarifa else 0 end ,0) ,-2) ,9,0)) else lPAD(nvl(null,0) + nvl(null,0),9,0) end 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(nvl(aporte_patrono_licnorem,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(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,
RPAD(to_char(' '),1,' ') 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 no_nombramiento tab5 on tab5.id_tipo_doc = tab0.id_tipo_doc and tab5.num_doc = tab0.num_doc
left join (
  SELECT
  case when aut.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 (30 - to_char(aut.fecha_termina,'dd') ) else 0 end as dias_retiro,
   aut.*,
   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

/* Tomar aporte_patrono_licnorem de la tabla de aportes - 20150730*/
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*/
    , sum(tab0.aporte_patrono_licnorem) AS aporte_patrono_licnorem
    , sum(tab0.num_dias_licnorem) AS num_dias_licnorem
    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

union


/*
* UPC Registros para afiliaciones familiares
* Se deja en el campo c37 que tenga los dias de salud, antes 0 - 20150918
*/
select 

1 as c0,
'02' AS C1,
LPAD(rownum,5,0) AS C2,
'CC' AS C3,
rpad(to_char(upcadic.num_doc),16,' ') as c4,
rpad(to_char(upcadic.num_doc_bene),16,' ') as c4a,
'40' AS C5,
'00' AS C6,
' ' AS C7,
' ' as c8,
rpad('0',2,'0') as c9,
rpad('0',3,'0') as c10,
RPAD(nvl(tab4.primer_apellido,' '),20,' ') AS C11,
RPAD(nvl(upcadic.primer_apellido_bene,' '),20,' ') AS C11a,
rpad(nvl(upcadic.segundo_apellido_bene,' '),30,' ') as c12,
rpad(nvl(upcadic.primer_nombre_bene,' '),20,' ') as c13,
RPAD(nvl(upcadic.segundo_nombre_bene,' '),30,' ') AS C14,
' ' as c15,
' ' AS C16,
' ' AS C17,
' ' AS C18,
' ' AS C19,
' ' AS C20,
' ' AS C21,
' ' AS C22,
' ' AS C23,
' ' AS C24,
' ' AS C25,
' ' AS C26,
' ' AS C27,
' ' AS C28,
' ' as c29,
LPAD('0',2,'0') AS C30,
rpad(nvl(null,' '),6,' ') as c31,
rPAD(' ',6,' ') AS C32,
rPAD(nvl(salud.codigo,' '),6,' ') AS C33,
rpad(' ',6,' ') as c34,
rpad(nvl(null,' '),6,' ') as c35,
LPAD(nvl(null,0),2,0) AS C36,
--lpad(nvl(salud1.dias,0),2,0) as c37,
case when $entidad_cliente in (4) then lpad(nvl(0,0),2,0) else lpad(nvl(salud1.dias,0),2,0) end AS c37,
--lpad(nvl(0,0),2,0) as c37,
lpad(nvl(null,0),2,0) as c38,
LPAD(nvl(null,0),2,0) AS C39,
LPAD(0,9,0) AS C40,
' ' AS C41,
LPAD(0,9,0) AS C42,
LPAD(0,9,0) AS C43,
LPAD(0,9,0) AS C44,
LPAD(0,9,0) AS C45,
rpad('0.0',7,0) as c46,
lPAD(0,9,0) AS C47,
lPAD(0,9,0) AS C48,
lpad(0,9,0) as c49,
lpad(0,9,0) as C50,
lpad(0,9,0) as c51,
lpad(0,9,0) as c52,
rpad(0,9,0) as c53,
rpad('0.0',7,0) as c54,
lpad(0,9,0) as c55,
lpad( case when '40' = '40' then upcadic.valor_upc 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('0.0',9,0) as C61,
lpad(0,9,0) as C62,
lpad(0,9,0) as c63,
rpad('0.0',7,0) AS C64,
lpad(0,9,0) as c65,
rpad('0.0',7,0) as C66,
lpad(0,9,0) as c67,
rpad('0.0',7,0) as C68,
lpad(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,
'CC' AS C74,
RPAD(to_char(upcadic.num_doc),16,' ') AS C75,
RPAD(to_char(' '),1,' ') AS C77

from no_upc_adicional upcadic 

left join no_datos_funcionario_v tab4 ON tab4.id_tipo_doc = upcadic.id_tipo_doc and tab4.num_doc = upcadic.num_doc

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.id_tip_nov_ka=4 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 = upcadic.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 = upcadic.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 = upcadic.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 = 2
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc
)salud1 ON salud1.num_doc = upcadic.num_doc

where upcadic.estado = 1

)taborder

order by taborder.c11, taborder.c12, 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 = "";


/*
 * Registro tipo 3 - 20130725
 * 
 * Campo
 * R306  = Sumatoria Campo R242  Registro tipo 2
 * R307 = Ceros
 * R308 = Ceros
 * R309  = Sumatoria Campo R246  Registro tipo 2
 * R310A  = Sumatoria Campo R246A  Registro tipo 2
 * R310 = Ceros
 * R311 = Ceros
 * R312 = Ceros
 * R313 = Ceros
 * R314A  = Sumatoria Campo R306+r309+r310A
 * R314  = Numero de afiliados
 * 
 */
//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";

/*
 * Se anexa filtro de personas pensionadas - 20131129
 */
$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(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, 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

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.tipo_planilla = 'A'     
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)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 )
    and tab0.tipo_planilla = 'A'     
    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.porcentaje_patrono = 0
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)pension3 ON pension3.num_doc = tab0.num_doc


WHERE tab0.periodo = $id_periodo 
and tab0.num_doc not in (select num_doc from no_pension_func where  estado = 1)
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') ))
  )
)

)tab0

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.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, 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

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
    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 = 2 AND codigo.tipo = 'EPS'
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)salud1 ON salud1.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') ))
  )
)

)tab0

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),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.aporte,0) as aporte,
nvl(riesgo1.aporte_patrono,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, 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

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  
    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
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)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 
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') ))
  )
)

)tab0

where
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( count (tab0.nitocc),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.aporte,0) as aporte,
nvl(ccf1.aporte_patrono,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, 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

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  
    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'
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)ccf1 ON ccf1.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') ))
  )
)

)tab0

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( count (tab0.nitocc),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.aporte,0) as aporte,
nvl(sena1.aporte_patrono,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, 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

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  
    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
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)sena1 ON sena1.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') ))
  )
)

)tab0

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( count (tab0.nitocc),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.aporte,0) as aporte,
nvl(icbf1.aporte_patrono,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, 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

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  
    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
    and tab0.tipo_planilla = 'A'     
    group by tab0.num_doc, codigo.codigo, codigo.tipo, codigo.nitocc
)icbf1 ON icbf1.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') ))
  )
)

)tab0

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();
?>
