<?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="licencia_no_rem" language="groovy" pageWidth="468" pageHeight="610" orientation="Landscape" columnWidth="468" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
	<property name="ireport.zoom" value="1.948717100000002"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<parameter name="whereFuncionarioNumDoc" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereFuncionarioTipoDoc" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="periodo" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="id_periodo" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="fecha_corte" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="numero_letras" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="destino" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="comando" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="sexo" class="java.math.BigDecimal"/>
	<queryString>
		<![CDATA[SELECT

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

case when tab0.sexo = 1 then 'a' else 'o' end as letra_a,
case when tab0.sexo = 1 then 'de la' else 'del' end as letra_del,
case when tab0.sexo = 1 then 'a' else ' ' end as letra_a_sin,
initcap(case when tab0.sexo = 1 then 'la' else 'el' end) as letra_el,
case when area2.descripcion is null then ' ' else initcap_sofhouse(initcap(area2.descripcion)) ||', ' end as DEPENDENCIA_niv,
initcap(area1.descripcion) as area1,
initcap(area2.descripcion) as area2,

'$ ' || TO_CHAR(TAB0.ASIGNACION_BASICA, 'FM9,999,999,999') as asig,
LPAD(TO_CHAR(sysdate,'dd'),2,'0') as DIA_actual,
lower(Numero_a_Texto ( to_char(sysdate,'dd'), 2, 'ESP' )) as dia_ACTUAL_letra,
trim(INITCAP(TO_CHAR(sysdate,'MONTH','nls_date_language=spanish'))) as MES_actual,
TO_CHAR(sysdate,'yyyy') as anho_actual,
lower($P{numero_letras}) as numeros_letras,
(select DATOS_COMPANIA.NOMBRE_EMPRESA from DATOS_COMPANIA) as empresa,
case when to_char(tab1.fecha_ingreso,'dd') != 1 then lower(Numero_a_Texto( to_char(tab1.fecha_ingreso,'dd'), 2, 'ESP' )) else 'primero' end as dia_ingreso_letra,
LPAD(to_char(tab1.fecha_ingreso,'dd'),2,'0') as dia_ingreso,
trim(initcap(to_char(tab1.fecha_ingreso,'MONTH','nls_date_language=spanish'))) as mes_ingreso,
TO_CHAR(TAB1.FECHA_INGRESO,'yyyy') as anho_INGRESO,
TAB0.ID_TIPO_DOC as ID_TIPO_DOC, TAB0.NUM_DOC as NUM_DOC, TAB0.NOMBRE_COMPLETO as NOMBRE_COMPLETO,
tab0.nombre_apellido as nombre_apellido, tab0.cargo as cargo,
SUBSTR(tab0.codigo_comp,2) as codigo_comp,
TAB0.GRADO_COMP as GRADO_COMP,
initcap_sofhouse(initcap(TAB0.DENOMINACION_COMP)) as DENOMINACION_COMP,
initcap_sofhouse(initcap(TAB0.DEPENDENCIA)) as DEPENDENCIA,
TAB0.ASIGNACION_BASICA as ASIGNACION_BASICA, TAB0.DEVENGADOS as DEVENGADOS, TAB0.DEDUCIDOS as DEDUCIDOS,
tab0.neto as neto, tab0.id_periodo as id_periodo, tab0.banco as banco, tab0.cuenta as cuenta,
tab0.tipper as tipper, tab0.periodo as periodo, tab0.tipper||'  '||tab0.periodo as periodo_completo,
tab0.email as email, tab0.id_dp as id_dp, to_char(tab1.fecha_ingreso, 'YYYY-MM-DD') as fecha_ingreso,
-- si tiene novedad de encargo poner en tipo_nombramiento - 20131021
--lower(tipnom1.nombre) as tipo_nombramiento
case when (select count(*) from no_novedad_personal
where id_tipo_doc = TAB0.ID_TIPO_DOC and num_doc = TAB0.NUM_DOC and estado = 1 and id_novedad = 3
and sysdate between fecha_inicial and fecha_terminacion) > 0 then 'encargo' else lower(tipnom1.nombre) end as tipo_nombramiento,
tab0.sexo,
rownum || '. ' as rn,
rhmffed.funcion as funcion,
rhmffed.codigo

FROM NO_LIQUIDA_V tab0

JOIN NO_NOMBRAMIENTO_V tab1 ON tab0.id_tipo_doc = tab1.id_tipo_doc
AND tab0.num_doc = tab1.num_doc

left join rh_sa_niv2 tipnom1
on tipnom1.id_sa_niv2 = tab1.id_sa_niv2

join rh_dp rhdp
on tab0.id_dp = rhdp.id_dp

left join rh_dp_mf rhdpmf
on tab0.id_dp = rhdpmf.id_dp

left join rh_mf_mf rhmfmf
on rhdpmf.id_mf = rhmfmf.id_mf_mf
and rhmfmf.fecha_f is null

left join rh_mf_fe rhmffe
on rhmfmf.id_mf_mf = rhmffe.id_mf

left join rh_mf_fe_d rhmffed
on rhmffe.id_mf_fe = rhmffed.id_fe

left join area area1
on area1.id_area = rhdp.id_ea

left join area area2
on area2.id_area = area1.id_dependencia

WHERE  tab0.id_periodo != '-1'
AND tab0.id_periodo =  $P{id_periodo}
$P!{whereFuncionarioNumDoc} $P!{whereFuncionarioTipoDoc}


ORDER BY tab0.num_doc, rhmffed.codigo]]>
	</queryString>
	<field name="DV" class="java.math.BigDecimal"/>
	<field name="NIT" class="java.lang.String"/>
	<field name="EMPRESA" class="java.lang.String"/>
	<field name="LETRA_A" class="java.lang.String"/>
	<field name="LETRA_DEL" class="java.lang.String"/>
	<field name="LETRA_A_SIN" class="java.lang.String"/>
	<field name="LETRA_EL" class="java.lang.String"/>
	<field name="DEPENDENCIA_NIV" class="java.lang.String"/>
	<field name="AREA1" class="java.lang.String"/>
	<field name="AREA2" class="java.lang.String"/>
	<field name="ASIG" class="java.lang.String"/>
	<field name="DIA_ACTUAL" class="java.lang.String"/>
	<field name="DIA_ACTUAL_LETRA" class="java.lang.String"/>
	<field name="MES_ACTUAL" class="java.lang.String"/>
	<field name="ANHO_ACTUAL" class="java.lang.String"/>
	<field name="NUMEROS_LETRAS" class="java.lang.String"/>
	<field name="DIA_INGRESO_LETRA" class="java.lang.String"/>
	<field name="DIA_INGRESO" class="java.lang.String"/>
	<field name="MES_INGRESO" class="java.lang.String"/>
	<field name="ANHO_INGRESO" class="java.lang.String"/>
	<field name="ID_TIPO_DOC" class="java.math.BigDecimal"/>
	<field name="NUM_DOC" class="java.lang.String"/>
	<field name="NOMBRE_COMPLETO" class="java.lang.String"/>
	<field name="NOMBRE_APELLIDO" class="java.lang.String"/>
	<field name="CARGO" class="java.lang.String"/>
	<field name="CODIGO_COMP" class="java.lang.String"/>
	<field name="GRADO_COMP" class="java.lang.String"/>
	<field name="DENOMINACION_COMP" class="java.lang.String"/>
	<field name="DEPENDENCIA" class="java.lang.String"/>
	<field name="ASIGNACION_BASICA" class="java.math.BigDecimal"/>
	<field name="DEVENGADOS" class="java.math.BigDecimal"/>
	<field name="DEDUCIDOS" class="java.math.BigDecimal"/>
	<field name="NETO" class="java.math.BigDecimal"/>
	<field name="ID_PERIODO" class="java.math.BigDecimal"/>
	<field name="BANCO" class="java.lang.String"/>
	<field name="CUENTA" class="java.lang.String"/>
	<field name="TIPPER" class="java.lang.String"/>
	<field name="PERIODO" class="java.lang.String"/>
	<field name="PERIODO_COMPLETO" class="java.lang.String"/>
	<field name="EMAIL" class="java.lang.String"/>
	<field name="ID_DP" class="java.math.BigDecimal"/>
	<field name="FECHA_INGRESO" class="java.lang.String"/>
	<field name="TIPO_NOMBRAMIENTO" class="java.lang.String"/>
	<field name="SEXO" class="java.lang.String"/>
	<field name="RN" class="java.lang.String"/>
	<field name="FUNCION" class="java.lang.String"/>
	<field name="CODIGO" class="java.math.BigDecimal"/>
	<variable name="suma_dev" class="java.math.BigDecimal" calculation="Sum">
		<variableExpression><![CDATA[]]></variableExpression>
	</variable>
	<background>
		<band splitType="Stretch"/>
	</background>
	<detail>
		<band height="40" splitType="Stretch">
			<textField>
				<reportElement x="0" y="0" width="468" height="40" isRemoveLineWhenBlank="true">
					<printWhenExpression><![CDATA[$P{estado_licnorem} == 1]]></printWhenExpression>
				</reportElement>
				<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["Del 18 al 27 de febrero de 2013 le fue concedida licencia no remunerada mediante Resolución 048 de 2013."]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band splitType="Stretch"/>
	</summary>
	<noData>
		<band/>
	</noData>
</jasperReport>
