<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="no_certif_cesantias_totales" language="groovy" pageWidth="612" pageHeight="792" whenNoDataType="NoDataSection" columnWidth="552" leftMargin="40" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.6105100000000037"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="10"/>
	<property name="ireport.jasperserver.reportUnit" value="/reports/nomina/no_certif_cesantias_totales"/>
	<property name="ireport.jasperserver.url" value="http://192.168.2.43:8090/jasperserver/services/repository"/>
	<parameter name="num_doc" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="nombre_periodo" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="id_agrup_concepto" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="id_tipo_doc" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="periodo" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="tag_firma" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[1]]></defaultValueExpression>
	</parameter>
	<parameter name="id_periodo_filtro" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="comando" class="java.lang.String">
		<defaultValueExpression><![CDATA["no_certif_cesantias"]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[select distinct

nvl(valsin.valor_base,0) as valor_base_resta,

(
    select

    sum(VALOR_BASE+valor_ajuste) as valor_base

    from (

          select
          tab0.VALOR_BASE,
          tab0.valor_ajuste * 12 as valor_ajuste

          from no_cesantia tab0

          left join (
            SELECT
             aut.*,
             ROW_NUMBER() OVER (
              partition by aut.num_doc
              ORDER BY aut.actual desc
             ) AS rn
            from no_estado_func aut
            where aut.periodo = $P{id_periodo_filtro}
          )estfun
          on estfun.id_tipo_doc = tab0.id_tipo_doc
          and estfun.num_doc = tab0.num_doc
          and estfun.periodo = tab0.id_periodo
          and estfun.rn = 1

          left join no_nombramiento_v nombra
          on estfun.id_tipo_doc = nombra.id_tipo_doc
          and estfun.num_doc = nombra.num_doc

          where tab0.id_periodo = $P{id_periodo_filtro}
          and tab0.VALOR_BASE >= 1000 /*Ticket 31066, se activa que tome encuenta para el certificado*/
          and tab0.consolidado = 0
          and ( estfun.actual = 1

              or (nombra.estado = 1 or
                ( nombra.estado = 0
                  and nombra.fecha_retiro
                between to_date('01/'|| (select lpad(id_meses,2,'0') ||'/'||id_anhos from no_periodo where id_periodo = $P{id_periodo_filtro}) ,'dd/mm/yyyy')
                and trunc(last_day( TO_DATE('01/'|| (SELECT LPAD(ID_MESES,2,'0') ||'/'||ID_ANHOS FROM NO_PERIODO WHERE ID_PERIODO = $P{id_periodo_filtro}) ,'dd/mm/yyyy')  )+1)
                )
              )

          )

    )
) as valor_base_real,
agruptotal.valor as valor_base_calculo,

(to_char(sysdate,'dd/mm/yyyy')) as fecha_remision,
(select min(encabezado) from no_inf_firma where comando = 'no_certif_cesantias') as encabezado,

(select digito_verificacion(DATOS_COMPANIA.nit) as dv from DATOS_COMPANIA) as dv,
(select DATOS_COMPANIA.nit from DATOS_COMPANIA) as nit,
(select DATOS_COMPANIA.NOMBRE_EMPRESA from DATOS_COMPANIA) as empresa,

to_char (sysdate, 'fmday dd "de "fmmonth" de "yyyy') as mensaje_fecha,
(select descripcion from no_inf_firma where comando = 'no_extracto_consol_anual' and rownum = 1 ) as mensaje,

TAB0.ID_PERIODO as ID_PERIODO,
tab3.periodo,
TAB3.ID_ANHOS as ID_ANHOS,
TAB3.ID_MESES as ID_MESES, TAB3.ID_ANHOS||' '||TAB3.ID_MESES as PERIODO_MES,
INITCAP(mes.descripcion) as mes,

agrup.id_concep_nomina as id_concep_nomina_agrup, agrup.codigo,
agrup.descripcion as descripcion_agrup,
agrup.valor,
agrup.tipo_valor,
case when agrup.tipo_valor = 'V' then '12A' else '' end as parte,
case when agrup.tipo_valor = 'V' then round(agrup.valor /1 , 2) else agrup.valor end as valor_base_agrup,

(SELECT
sum(valor_liquidado) + sum(valor_ajuste) as valor
FROM no_cesantia
where
consolidado = 0
and id_periodo = $P{id_periodo_filtro}) as valor_cesantias

from NO_CESANTIA tab0

join NO_PERIODO_V TAB3 on TAB0.ID_PERIODO = TAB3.ID_PERIODO
join meses mes on TAB3.id_meses = mes.id_meses


left join(

    select
    $P{id_periodo_filtro} as id_periodo,
    sum(nvl(tab.valor_base,0)) as valor,
    tab.id_concep_nomina,
    tab.codigo,
    tab.descripcion,
    tab.tipo_valor

    from(

        select
        TAB0.id_base_liquidacion,
        TAB0.TIPO_VALOR,
        TAB0.valor_base,
        TAB0.dias,
        tab1.*
        from no_base_liquidacion tab0
        left join no_concep_nomina tab1 on tab1.id_concep_nomina = tab0.id_concep_nomina
        where
        tab0.id_periodo =  $P{id_periodo_filtro}

    )tab

    group by
    tab.id_concep_nomina,
    tab.codigo,
    tab.descripcion,
    tab.tipo_valor

)agrup
on tab0.id_periodo = agrup.id_periodo

left join(

    select
    $P{id_periodo_filtro} as id_periodo,
    /* Incidente #31369, se ajusta segun 12A segun concepto, se ajsuta con AB - 20220818, se deja sin efecto el cambio
    sum(case when tab.tipo_valor = 'V' then round(tab.valor_base /12) else tab.valor_base end) as valor*/
    sum(nvl(tab.valor_base,0)) as valor

    from(

        select
        TAB0.id_base_liquidacion,
        TAB0.TIPO_VALOR,
        TAB0.valor_base,
        TAB0.dias,
        tab1.*
        from no_base_liquidacion tab0
        left join no_concep_nomina tab1 on tab1.id_concep_nomina = tab0.id_concep_nomina
        where
        tab0.id_periodo =  $P{id_periodo_filtro}

    )tab

)agruptotal
on tab0.id_periodo = agruptotal.id_periodo

/*Para ajustar diferencia de los que no aportan valor = 0 - 20240924*/
left join(
    (SELECT
    sum(valor_base) as valor_base,
    sum(valor_liquidado) + sum(valor_ajuste) as valor
    FROM no_cesantia
    where
    consolidado = 0
    and id_periodo = $P{id_periodo_filtro}
    and valor_liquidado = 0 and valor_base >= 1000)
)valsin
on 1 = 1

where
tab0.id_periodo =  $P{id_periodo_filtro}
and tab0.consolidado = 0

order by
agrup.codigo]]>
	</queryString>
	<field name="VALOR_BASE_RESTA" class="java.math.BigDecimal"/>
	<field name="VALOR_BASE_REAL" class="java.math.BigDecimal"/>
	<field name="VALOR_BASE_CALCULO" class="java.math.BigDecimal"/>
	<field name="FECHA_REMISION" class="java.lang.String"/>
	<field name="ENCABEZADO" class="java.lang.String"/>
	<field name="DV" class="java.math.BigDecimal"/>
	<field name="NIT" class="java.lang.String"/>
	<field name="EMPRESA" class="java.lang.String"/>
	<field name="MENSAJE_FECHA" class="java.lang.String"/>
	<field name="MENSAJE" class="java.lang.String"/>
	<field name="ID_PERIODO" class="java.math.BigDecimal"/>
	<field name="PERIODO" class="java.lang.String"/>
	<field name="ID_ANHOS" class="java.math.BigDecimal"/>
	<field name="ID_MESES" class="java.math.BigDecimal"/>
	<field name="PERIODO_MES" class="java.lang.String"/>
	<field name="MES" class="java.lang.String"/>
	<field name="ID_CONCEP_NOMINA_AGRUP" class="java.math.BigDecimal"/>
	<field name="CODIGO" class="java.lang.String"/>
	<field name="DESCRIPCION_AGRUP" class="java.lang.String"/>
	<field name="VALOR" class="java.math.BigDecimal"/>
	<field name="TIPO_VALOR" class="java.lang.String"/>
	<field name="PARTE" class="java.lang.String"/>
	<field name="VALOR_BASE_AGRUP" class="java.math.BigDecimal"/>
	<field name="VALOR_CESANTIAS" class="java.math.BigDecimal"/>
	<variable name="total" class="java.math.BigDecimal" calculation="Sum">
		<variableExpression><![CDATA[( $F{ID_CONCEP_NOMINA_AGRUP} == 1
    ? $F{VALOR_BASE_AGRUP} - ($F{VALOR_BASE_CALCULO} - $F{VALOR_BASE_REAL}) - $F{VALOR_BASE_RESTA}
    : $F{VALOR_BASE_AGRUP}
)]]></variableExpression>
	</variable>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="50" splitType="Stretch">
			<image>
				<reportElement x="199" y="0" width="162" height="50"/>
				<imageExpression class="java.lang.String"><![CDATA["repo:LogoEmpresa003.jpg"]]></imageExpression>
			</image>
		</band>
	</title>
	<pageHeader>
		<band height="125" splitType="Stretch">
			<textField isBlankWhenNull="true">
				<reportElement x="0" y="105" width="276" height="20"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="11" isBold="true"/>
				</textElement>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="0" y="40" width="552" height="40"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="11" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{ENCABEZADO}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="0" y="80" width="552" height="25"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="11" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{EMPRESA}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="276" y="105" width="276" height="20"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="11" isBold="true"/>
				</textElement>
			</textField>
			<textField>
				<reportElement x="0" y="0" width="552" height="40"/>
				<box leftPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement verticalAlignment="Middle">
					<font size="11" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Fecha de remisión (" + $F{FECHA_REMISION} + ")"]]></textFieldExpression>
			</textField>
		</band>
	</pageHeader>
	<columnHeader>
		<band height="100" splitType="Stretch">
			<textField>
				<reportElement x="0" y="0" width="552" height="40"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="11" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["CERTIFICA QUE:"]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="0" y="40" width="552" height="60"/>
				<box leftPadding="0">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.0"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.0"/>
					<rightPen lineWidth="0.0"/>
				</box>
				<textElement textAlignment="Justified" verticalAlignment="Top">
					<font size="11"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["En virtud de lo dispuesto en el artículo 193 del Decreto-Ley 019 de 2012, se reportan a continuación, la totalidad de factores salariales que constituyen base para liquidar cesantías, devengados en el mes "+$F{MES}+" de "+$F{ID_ANHOS}+":"]]></textFieldExpression>
			</textField>
		</band>
	</columnHeader>
	<detail>
		<band height="10" splitType="Stretch">
			<textField>
				<reportElement x="91" y="0" width="90" height="10"/>
				<box>
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{CODIGO}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="181" y="0" width="202" height="10"/>
				<box leftPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement verticalAlignment="Middle">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[( $F{TIPO_VALOR}.equals( "V" )
    ? $F{PARTE} + " " + $F{DESCRIPCION_AGRUP}
    : $F{DESCRIPCION_AGRUP}
)]]></textFieldExpression>
			</textField>
			<textField pattern="#,##0.00;(#,##0.00)">
				<reportElement x="383" y="0" width="80" height="10"/>
				<box rightPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[( $F{ID_CONCEP_NOMINA_AGRUP} == 1
    ? $F{VALOR_BASE_AGRUP} - ($F{VALOR_BASE_CALCULO} - $F{VALOR_BASE_REAL}) - $F{VALOR_BASE_RESTA}
    : $F{VALOR_BASE_AGRUP}
)]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<pageFooter>
		<band height="12" splitType="Stretch">
			<textField evaluationTime="Report" pattern="" isBlankWhenNull="false">
				<reportElement mode="Opaque" x="524" y="0" width="28" height="12" forecolor="#575757" backcolor="#FFFFFF"/>
				<textElement verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="6" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Opaque" x="434" y="0" width="89" height="12" forecolor="#575757" backcolor="#FFFFFF"/>
				<textElement textAlignment="Right" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="6" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Página "+$V{PAGE_NUMBER}+" de "]]></textFieldExpression>
			</textField>
		</band>
	</pageFooter>
	<summary>
		<band height="157">
			<subreport>
				<reportElement stretchType="RelativeToBandHeight" x="101" y="95" width="350" height="62"/>
				<subreportParameter name="comando">
					<subreportParameterExpression><![CDATA["no_certif_cesantias"]]></subreportParameterExpression>
				</subreportParameter>
				<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
				<subreportExpression class="java.lang.String"><![CDATA["repo:firma"]]></subreportExpression>
			</subreport>
			<staticText>
				<reportElement mode="Transparent" x="101" y="34" width="175" height="12" forecolor="#000000" backcolor="#A4CFF6"/>
				<box>
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="7" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[MES]]></text>
			</staticText>
			<textField pattern="#,##0.00;(#,##0.00)">
				<reportElement x="276" y="46" width="175" height="12"/>
				<box rightPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="7" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{VALOR_CESANTIAS}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement mode="Transparent" x="276" y="34" width="175" height="12" forecolor="#000000" backcolor="#A4CFF6"/>
				<box>
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="7" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[VALOR APORTES MENSUAL CESANTIAS]]></text>
			</staticText>
			<textField>
				<reportElement mode="Transparent" x="101" y="46" width="175" height="12" forecolor="#000000" backcolor="#A4CFF6"/>
				<box>
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None" markup="none">
					<font fontName="SansSerif" size="7" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{MES} + " de " + $F{ID_ANHOS}]]></textFieldExpression>
			</textField>
			<textField pattern="#,##0.00;(#,##0.00)">
				<reportElement x="383" y="0" width="80" height="10"/>
				<box rightPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font size="7" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{total}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="91" y="0" width="292" height="10"/>
				<box leftPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.75"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement verticalAlignment="Middle">
					<font size="7" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["Total"]]></textFieldExpression>
			</textField>
		</band>
	</summary>
	<noData>
		<band height="20">
			<staticText>
				<reportElement x="0" y="0" width="552" height="20"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[No hay objetos a mostrar.]]></text>
			</staticText>
		</band>
	</noData>
</jasperReport>
