<?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="distribucion_resumen_ordengiro" language="groovy" pageWidth="752" pageHeight="555" orientation="Landscape" columnWidth="752" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
	<property name="ireport.zoom" value="1.4641000000000006"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<parameter name="id_distribucion" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[select

razon_soc_obldet,
id_tipo_beneficiario,
sum(valor_girar) as valor_girar,
num_doc_ter_obldet,
num_cuenta,
descripcion_achdis,
tipo_cuenta_achdis,
observaciones_bene,
autorizado

from (


select

bene.observaciones as observaciones_bene,
nvl(bene.autorizado, 0) as autorizado,
nvl(bene.secuencia, 0) as secuencia_bene,
nvl(ach.aplica_regalias, 0) as aplica_regalias,
achdis.codigo_ach,
achdis.num_cuenta,
achdis.tipo_cuenta,

case
  when achdis.tipo_cuenta = 1 then 'Corriente'
  when achdis.tipo_cuenta =  7 then 'Ahorros'
  else ' '
end as tipo_cuenta_achdis,

achdis.id_bn as bn_achdis,
bn.codigo as codigo_achdis,
bn.descripcion as descripcion_achdis,
tab0.*

from re_distri_obl_obldet tab0

left join (
  SELECT
   aut.*,
   ROW_NUMBER() OVER (
    PARTITION BY aut.id_mae_ter
    ORDER BY aut.secuencia desc, id_bene_autoriza desc
   ) AS rn
  from re_bene_autoriza aut
)bene
on bene.id_mae_ter = tab0.id_mae_ter_obldet
and bene.rn = 1

left join (
  SELECT
   ach2.*,
   ROW_NUMBER() OVER (
    PARTITION BY ach2.id_tercero
    ORDER BY ach2.fechalog desc
   ) AS rn
  from ts_reg_ach ach2
)ach
on ach.id_tercero = tab0.id_mae_ter_obldet
and ach.aplica_regalias = 1
and ach.rn = 1
/*
ts_reg_ach ach
on ach.id_tercero = tab0.id_mae_ter_obldet
and ach.aplica_regalias = 1
*/
-- 20120823
/*
left join ts_reg_ach_distri achdis
on achdis.id_tercero = tab0.id_mae_ter_obldet
and achdis.id_reg_ach = ach.id_reg_ach
*/
left join (
  SELECT
   achdis2.*,
   ROW_NUMBER() OVER (
    partition by achdis2.id_tercero
    ORDER BY achdis2.secuencia desc
   ) as rn
  from ts_reg_ach_distri achdis2

  join ts_reg_ach regach
  on regach.id_reg_ach = achdis2.id_reg_ach
  and regach.aplica_regalias = 1
)achdis
on achdis.id_tercero = tab0.id_mae_ter_obldet
--and achdis.id_reg_ach = ach.id_reg_ach
and achdis.rn = 1

left join ts_bn bn
on bn.id_bn = achdis.id_bn

where
tab0.id_distribucion = $P{id_distribucion}
and tab0.estado_obl = 1



/*
order by
tab0.id_distribucion,
tab0.fecha_crea,
bene.autorizado asc,
tab0.id_obligacion,
tab0.secuencia
*/
)

group by
razon_soc_obldet,
id_tipo_beneficiario,
num_doc_ter_obldet,
num_cuenta,
descripcion_achdis,
tipo_cuenta_achdis,
observaciones_bene,
autorizado

order by
autorizado desc,
id_tipo_beneficiario,
num_doc_ter_obldet]]>
	</queryString>
	<field name="RAZON_SOC_OBLDET" class="java.lang.String"/>
	<field name="ID_TIPO_BENEFICIARIO" class="java.math.BigDecimal"/>
	<field name="VALOR_GIRAR" class="java.math.BigDecimal"/>
	<field name="NUM_DOC_TER_OBLDET" class="java.lang.String"/>
	<field name="NUM_CUENTA" class="java.lang.String"/>
	<field name="DESCRIPCION_ACHDIS" class="java.lang.String"/>
	<field name="TIPO_CUENTA_ACHDIS" class="java.lang.String"/>
	<field name="OBSERVACIONES_BENE" class="java.lang.String"/>
	<field name="AUTORIZADO" class="java.math.BigDecimal"/>
	<variable name="sum_valor_girar" class="java.math.BigDecimal" resetType="Group" resetGroup="AUTORIZADO" calculation="Sum">
		<variableExpression><![CDATA[$F{VALOR_GIRAR}]]></variableExpression>
		<initialValueExpression><![CDATA[0]]></initialValueExpression>
	</variable>
	<group name="AUTORIZADO" isReprintHeaderOnEachPage="true">
		<groupExpression><![CDATA[$F{AUTORIZADO}]]></groupExpression>
		<groupHeader>
			<band height="36">
				<staticText>
					<reportElement mode="Opaque" x="0" y="24" width="110" 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[Nit / Cc]]></text>
				</staticText>
				<staticText>
					<reportElement mode="Opaque" x="662" y="24" width="90" 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[Valor a Girar]]></text>
				</staticText>
				<staticText>
					<reportElement mode="Opaque" x="110" y="24" width="272" 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[Razon Social]]></text>
				</staticText>
				<textField>
					<reportElement x="0" y="0" width="752" height="12"/>
					<textElement textAlignment="Center" verticalAlignment="Middle">
						<font size="8" isBold="true"/>
					</textElement>
					<textFieldExpression class="java.lang.String"><![CDATA[( $F{AUTORIZADO} == 1
        ? "ORDEN DE GIRO"
        : "ORDEN DE NO GIRO"
)]]></textFieldExpression>
				</textField>
				<staticText>
					<reportElement mode="Opaque" x="382" y="24" width="140" height="12" forecolor="#000000" backcolor="#88C2F9">
						<printWhenExpression><![CDATA[$F{AUTORIZADO} == 1]]></printWhenExpression>
					</reportElement>
					<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[Banco]]></text>
				</staticText>
				<staticText>
					<reportElement mode="Opaque" x="522" y="24" width="70" height="12" forecolor="#000000" backcolor="#88C2F9">
						<printWhenExpression><![CDATA[$F{AUTORIZADO} == 1]]></printWhenExpression>
					</reportElement>
					<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[Tipo]]></text>
				</staticText>
				<staticText>
					<reportElement mode="Opaque" x="592" y="24" width="70" height="12" forecolor="#000000" backcolor="#88C2F9">
						<printWhenExpression><![CDATA[$F{AUTORIZADO} == 1]]></printWhenExpression>
					</reportElement>
					<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[Cuenta]]></text>
				</staticText>
				<staticText>
					<reportElement mode="Opaque" x="382" y="24" width="280" height="12" forecolor="#000000" backcolor="#88C2F9">
						<printWhenExpression><![CDATA[$F{AUTORIZADO} == 0]]></printWhenExpression>
					</reportElement>
					<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[Observaciones]]></text>
				</staticText>
			</band>
		</groupHeader>
		<groupFooter>
			<band height="25">
				<textField>
					<reportElement mode="Opaque" x="0" y="0" width="662" height="12" backcolor="#D2D0D0"/>
					<box>
						<pen lineWidth="0.75"/>
						<topPen lineWidth="0.75"/>
						<leftPen lineWidth="0.75"/>
						<bottomPen lineWidth="0.75"/>
						<rightPen lineWidth="0.75"/>
					</box>
					<textElement verticalAlignment="Middle" markup="none">
						<font size="8" isBold="true"/>
					</textElement>
					<textFieldExpression class="java.lang.String"><![CDATA[( $F{AUTORIZADO} == 1
        ? "Total Orden de Giro"
        : "Total Orden de No Giro"
)]]></textFieldExpression>
				</textField>
				<textField pattern="#,##0.00;(#,##0.00)">
					<reportElement mode="Opaque" x="662" y="0" width="90" 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{sum_valor_girar}]]></textFieldExpression>
				</textField>
			</band>
		</groupFooter>
	</group>
	<detail>
		<band height="10" splitType="Stretch">
			<textField isStretchWithOverflow="true">
				<reportElement x="110" y="0" width="272" 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="6"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{RAZON_SOC_OBLDET}]]></textFieldExpression>
			</textField>
			<textField pattern="#,##0.00;(#,##0.00)">
				<reportElement stretchType="RelativeToTallestObject" x="662" y="0" width="90" 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="6"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{VALOR_GIRAR}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement stretchType="RelativeToTallestObject" x="0" y="0" width="110" height="10"/>
				<box rightPadding="3">
					<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.lang.String"><![CDATA[$F{NUM_DOC_TER_OBLDET}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="592" y="0" width="70" height="10">
					<printWhenExpression><![CDATA[$F{AUTORIZADO} == 1]]></printWhenExpression>
				</reportElement>
				<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{NUM_CUENTA}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="382" y="0" width="140" height="10">
					<printWhenExpression><![CDATA[$F{AUTORIZADO} == 1]]></printWhenExpression>
				</reportElement>
				<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_ACHDIS}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="522" y="0" width="70" height="10">
					<printWhenExpression><![CDATA[$F{AUTORIZADO} == 1]]></printWhenExpression>
				</reportElement>
				<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{TIPO_CUENTA_ACHDIS}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement stretchType="RelativeToTallestObject" x="382" y="0" width="280" height="10">
					<printWhenExpression><![CDATA[$F{AUTORIZADO} == 0]]></printWhenExpression>
				</reportElement>
				<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{OBSERVACIONES_BENE}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
</jasperReport>
