<?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="inf_gen_archivos_siif_aportes_deducciones" language="groovy" pageWidth="550" pageHeight="612" orientation="Landscape" whenNoDataType="NoDataSection" columnWidth="510" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.7715610000000577"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<property name="ireport.jasperserver.reportUnit" value="/reports/nomina/inf_gen_archivos_siif_nacion_deducciones"/>
	<property name="ireport.jasperserver.url" value="http://192.168.2.43:8090/jasperserver/services/repository"/>
	<parameter name="wherePeriodoFiltro" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="whereClaseFondo" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="whereMaestroFondos" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="whereTAC" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="whereTipoCuenta" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[SELECT ROWNUM,a.* FROM(
SELECT
tab0.id_mae_fondos,
tab0.fondo,
tab0.tipo_cuenta,
to_char(max(tab0.codigo_siif)) as codigo_siif,
to_char(max(tab0.nit)) as nit,
sum(tab0.aporte_patrono + tab0.aporte_patrono_licnorem) as aportes_sum,
sum(tab0.aporte_func) as total_aporte_fuc

FROM (

    select

    tab0.tipo_planilla,
    tab0.id_liquida_aporte,
    tab0.id_clase_fondo,
    tab0.id_mae_fondos,
    tab0.id_concep_nomina,
    tab0.id_tipo_doc,
    tab0.num_doc,
    tab0.valor_base,
    tab0.num_dias,
    tab0.aporte_func,
    tab0.aporte_patrono,
tab0.aporte_patrono_licnorem,
    tab0.id_cen_op,
    tab0.id_tipo_aporte,
    tab0.porcentaje_func,
    tab0.porcentaje_patrono,
    tab0.tarifa,
    tab0.id_periodo,
    tab0.upc_adicio,
    tab0.valor_base_fsp,
    tab0.id_tae,
    tab1.razon_social as fondo,
        tab1.num_doc as nit,
    tab2.descripcion as clase_fondo,
    tab2.tipo_cuenta,
    tab3.codigo||' - '||tab3.descripcion as concepto,
    tab4.codigo_siif
    from NO_LIQUIDA_APORTE  tab0
    left join NO_MAE_FONDOS tab1 ON tab1.id_mae_fondos = tab0.id_mae_fondos
    left join NO_CLASE_FONDO tab2 ON tab2.id_clase_fondo = tab0.id_clase_fondo
    left join NO_CONCEP_NOMINA tab3 ON tab3.id_concep_nomina = tab0.id_concep_nomina
    left join NO_LISTA_CONCEPTOS_SIIF tab4 ON tab3.id_concepto_patronal = tab4.ID_LISTA_CONCEPTOS_SIIF
where
1 = 1
$P!{wherePeriodoFiltro}
$P!{whereClaseFondo}
$P!{whereMaestroFondos}
$P!{whereTAC}
$P!{whereTipoCuenta}
)tab0
where
1 = 1
group by
tab0.id_mae_fondos,
tab0.fondo,
tab0.nit,
tab0.tipo_cuenta
order by
tab0.id_mae_fondos)a]]>
	</queryString>
	<field name="ROWNUM" class="java.math.BigDecimal"/>
	<field name="ID_MAE_FONDOS" class="java.math.BigDecimal"/>
	<field name="FONDO" class="java.lang.String"/>
	<field name="TIPO_CUENTA" class="java.math.BigDecimal"/>
	<field name="CODIGO_SIIF" class="java.lang.String"/>
	<field name="NIT" class="java.lang.String"/>
	<field name="APORTES_SUM" class="java.math.BigDecimal"/>
	<field name="TOTAL_APORTE_FUC" class="java.math.BigDecimal"/>
	<variable name="variable1" class="java.math.BigDecimal" calculation="Sum">
		<variableExpression><![CDATA[$F{APORTES_SUM}]]></variableExpression>
		<initialValueExpression><![CDATA[0]]></initialValueExpression>
	</variable>
	<background>
		<band splitType="Stretch"/>
	</background>
	<columnHeader>
		<band height="38" splitType="Stretch">
			<staticText>
				<reportElement mode="Opaque" x="240" y="23" width="60" height="15" 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="5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[TARIFA]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="420" y="23" width="90" height="15" 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="5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[NUMERO DOCUMENTO BENEFICIARIO]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="0" y="23" width="80" height="15" 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="5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[INDICADOR DEL TERCERO]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="300" y="23" width="60" height="15" 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="5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[VALOR A DEDUCIR]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="360" y="23" width="60" height="15" 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="5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[TIPO DOCUMENTO DE BENEFICIARIO]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="80" y="23" width="80" height="15" 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="5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[CODIGO DEL CONCEPTO DE DESCUENTO]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="160" y="23" width="80" height="15" 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="5" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[BASE GRAVABLE]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="12">
			<printWhenExpression><![CDATA[$F{ROWNUM}!=null]]></printWhenExpression>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="80" 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="6" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.Integer"><![CDATA[$F{ROWNUM}]]></textFieldExpression>
			</textField>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="80" y="0" width="80" 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="6" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{CODIGO_SIIF}]]></textFieldExpression>
			</textField>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="160" y="0" width="80" 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="6" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.Integer"><![CDATA[]]></textFieldExpression>
			</textField>
			<textField pattern="#,##0.00;(#,##0.00)" isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="300" y="0" width="60" 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="Right" verticalAlignment="Middle">
					<font size="6" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{APORTES_SUM}]]></textFieldExpression>
			</textField>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="360" y="0" width="60" 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="6" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA["01"]]></textFieldExpression>
			</textField>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="420" y="0" width="90" 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="6" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{NIT}]]></textFieldExpression>
			</textField>
			<textField pattern="" isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="240" y="0" width="60" 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="6" isBold="false"/>
				</textElement>
				<textFieldExpression class="java.lang.Integer"><![CDATA[]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<pageFooter>
		<band height="42" splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band height="50">
			<textField pattern="#,##0.00;(#,##0.00)">
				<reportElement stretchType="RelativeToTallestObject" mode="Opaque" x="300" y="0" width="60" height="12" backcolor="#D2D0D0"/>
				<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="6" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{variable1}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement mode="Opaque" x="0" y="0" width="300" height="12" isRemoveLineWhenBlank="true" forecolor="#000000" backcolor="#D2D0D0"/>
				<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" rotation="None" markup="none">
					<font fontName="SansSerif" size="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[TOTALES]]></text>
			</staticText>
		</band>
	</summary>
	<noData>
		<band height="20">
			<staticText>
				<reportElement x="-40" y="0" width="550" height="20"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font isBold="true"/>
				</textElement>
				<text><![CDATA[No hay objetos a mostrar.]]></text>
			</staticText>
		</band>
	</noData>
</jasperReport>
