<?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_car_clasif_ter2" language="groovy" pageWidth="380" pageHeight="572" orientation="Landscape" columnWidth="380" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
	<property name="ireport.zoom" value="3.138428376721021"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<property name="ireport.jasperserver.reportUnit" value="/reports/cartera/inf_car_clasif_tit"/>
	<property name="ireport.jasperserver.url" value="http://192.168.2.43:8090/jasperserver/services/repository"/>
	<parameter name="id_periodo" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="id_vigencia" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="whereSaldoActual" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereTercero" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereTipoIngre" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="wherePuntoVenta" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereMineral" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[select * from (
SELECT

nvl(tab0.id_tipo_novedad, 0 )as id_tipo_novedad,

nvl(tab0.desc_corta_tn, 'SN' )as desc_corta_tn,

nvl(tab0.descripcion_tn, 'SIN NOVEDAD' )as descripcion_tn,

sum(tab0.recursos_propios) as recursos_propios,

sum(tab0.aportes_nacion) as aportes_nacion,

sum(tab0.recursos_distri) as recursos_distri,

sum(tab0.recursos_propios) + sum(tab0.aportes_nacion) + sum(tab0.recursos_distri) as total,

sum(saldo_actual) as saldo_actual



FROM (



select



deb.*,

nov.id_novedad_cartera,

nov.id_ts_td,

nov.id_tipo_novedad,

nov.num_doc AS num_doc_nov,

nov.fecha_novedad,

nov.observaciones AS observaciones_nov,

nov.abierto,

nov.anulado,

nov.fecha_ce,

nov.motivo_ce,

tipnov.descripcion AS descripcion_tn,

tipnov.desc_corta  AS desc_corta_tn,

td.descripcion     AS descripcion_td,

debref.id_referencia1,

debref.id_referencia2,

mine.cod_mineral ||' - ' || mine.descripcion AS mineral,



nvl(case

  when deb.id_pr_tipo_ingre = 4 then deb.saldo_actual

  else 0

end ,0) as recursos_propios,



nvl(case

  when deb.id_pr_tipo_ingre = 5 then deb.saldo_actual

  else 0

end ,0) as aportes_nacion,

nvl(case
  when deb.id_pr_tipo_ingre = 7 then deb.saldo_actual
  else 0
end ,0) as recursos_distri



from ca_debito_saldos deb



LEFT JOIN CA_DEBITO_REF debref

ON debref.id_debito = deb.id_debito



LEFT JOIN RE_MINERAL mine

ON debref.id_referencia1 = mine.id_mineral



left JOIN ca_novedad_cartera nov

ON nov.id_debito       = deb.id_debito

AND nov.id_vencimiento = deb.id_vencimiento
and nov.abierto = 1
and nov.anulado = 0
and nov.fecha_novedad<=last_day(to_date($P{id_periodo}||'-'||$P{id_vigencia},'MM-YYYY'))


left JOIN ca_tipo_novedad tipnov

ON tipnov.id_tipo_novedad = nov.id_tipo_novedad



left JOIN ts_td td

ON td.id = nov.id_ts_td
where
deb.id_anhos_sal = $P{id_vigencia}
and deb.id_meses_sal = $P{id_periodo}
and debref.id_referencia2 is not null
and deb.id_mae_ter != 2176


)tab0
where 1 = 1
and tab0.estado_ven = 1
$P!{whereTipoIngre}
$P!{whereTercero}
$P!{whereMineral}
$P!{wherePuntoVenta}
group by
tab0.id_tipo_novedad,
tab0.descripcion_tn,
tab0.desc_corta_tn
)
tab0
where 1 = 1
$P!{whereSaldoActual}
order by
tab0.desc_corta_tn]]>
	</queryString>
	<field name="ID_TIPO_NOVEDAD" class="java.math.BigDecimal"/>
	<field name="DESC_CORTA_TN" class="java.lang.String"/>
	<field name="DESCRIPCION_TN" class="java.lang.String"/>
	<field name="RECURSOS_PROPIOS" class="java.math.BigDecimal"/>
	<field name="APORTES_NACION" class="java.math.BigDecimal"/>
	<field name="RECURSOS_DISTRI" class="java.math.BigDecimal"/>
	<field name="TOTAL" class="java.math.BigDecimal"/>
	<field name="SALDO_ACTUAL" class="java.math.BigDecimal"/>
	<background>
		<band splitType="Stretch"/>
	</background>
	<columnHeader>
		<band height="12">
			<staticText>
				<reportElement mode="Opaque" x="0" y="0" width="380" height="12" forecolor="#000000" backcolor="#88C2F9"/>
				<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="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
				</textElement>
				<text><![CDATA[Total por Tipo de Novedad]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="10" splitType="Stretch">
			<textField pattern="#,##0.00;(#,##0.00)">
				<reportElement x="280" y="0" width="100" height="10"/>
				<box leftPadding="0" 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"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{TOTAL}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Transparent" x="0" y="0" width="60" height="10" backcolor="#FEFEFE"/>
				<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 textAlignment="Center" verticalAlignment="Middle">
					<font size="6"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{DESC_CORTA_TN}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement mode="Transparent" x="60" y="0" width="220" height="10" backcolor="#FEFEFE"/>
				<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="6"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{DESCRIPCION_TN}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<summary>
		<band/>
	</summary>
</jasperReport>
