<?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_costo_total_materia_prima" language="groovy" pageWidth="1290" pageHeight="595" orientation="Landscape" columnWidth="1290" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
	<property name="ireport.zoom" value="1.3636363636363646"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<parameter name="id_vigencia" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="id_orden_trabajo" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="id_cen_op" class="java.math.BigDecimal">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="entidad_cliente" class="java.lang.String"/>
	<parameter name="fecha_ini" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="fecha_fin" class="java.lang.String">
		<defaultValueExpression><![CDATA[]]></defaultValueExpression>
	</parameter>
	<parameter name="whereCenOp" class="java.lang.String">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="whereEstado" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereAnulado" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="username" class="java.lang.String" isForPrompting="false">
		<defaultValueExpression><![CDATA[""]]></defaultValueExpression>
	</parameter>
	<parameter name="whereOrdenTrabajo" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereTipoServicio" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereTipoLabor" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<parameter name="whereTercero" class="java.lang.String">
		<defaultValueExpression><![CDATA[" "]]></defaultValueExpression>
	</parameter>
	<queryString>
		<![CDATA[select
tab0.id_orden_trabajo,
tab0.id_vigencia,
tab0.id_cen_op,
tab0.id_tipo_servicio,

LISTAGG(id_inventario_con, '; ') WITHIN GROUP (ORDER BY id_inventario_con) as id_inventario_con,

tab0.codigo_inventario_con,
tab0.desc_inventario_con,
tab0.desc_unidad_medida,

sum(tab0.cantidad) as cantidad,
sum(tab0.valor_total) / sum(tab0.cantidad) as valor_unitario,
sum(tab0.valor_total) as  valor_total

from(
    select
    tab1.id_tipo_servicio as id_tipo_servicio,
    tab0.id_orden_trabajo,
    tab0.id_vigencia,
    tab0.id_cen_op,
    tab0.id_movimientos_con,
    tab0.id_vigencia_mov,
    tab0.id_cen_op_mov,
    tab0.id_tipo_movimiento,

    det.id_inventario_con,
    tab3.codigo_elemento as codigo_inventario_con,
    inv.descripcion as desc_inventario_con,
    tab6.descripcion as desc_unidad_medida,
    det.cantidad,
    det.valor_unitario,
    det.cantidad * det.valor_unitario as  valor_total

    from in_orden_mov_con tab0

    left join in_movimientos_con_v mov
    on tab0.id_vigencia_mov = mov.id_vigencia
    and tab0.id_cen_op_mov  =  mov.id_cen_op
    and tab0.id_tipo_movimiento  =  mov.id_tipo_movimiento
    and tab0.id_movimientos_con  =  mov.id_movimientos_con

    left join in_det_mov_con det
    on det.id_vigencia = mov.id_vigencia
    and det.id_cen_op  =  mov.id_cen_op
    and det.id_tipo_movimiento  =  mov.id_tipo_movimiento
    and det.id_movimientos_con  =  mov.id_movimientos_con

    left join in_inventario_con inv
    on inv.id_inventario_con = det.id_inventario_con

    left join in_codigo_elemento_v tab3 on tab3.id_linea = det.id_linea and tab3.id_clase = det.id_clase and tab3.id_catalogo = det.id_catalogo
    left join in_unidad_medida tab6 on tab6.id_unidad_medida = tab3.id_unidad_medida

    JOIN IN_ORDEN_TRABAJO_V tab1
    ON tab0.id_orden_trabajo = tab1.id_orden_trabajo
    AND tab0.id_vigencia = tab1.id_vigencia
    AND tab0.id_cen_op = tab1.id_cen_op

    WHERE
    tab0.id_orden_trabajo != '-1'
    and tab1.fecha_orden between to_date($P{fecha_ini}, 'dd/mm/yyyy') and to_date($P{fecha_fin}, 'dd/mm/yyyy')
    and mov.estado = 0
    and mov.anulado = 0

)tab0

WHERE
1 = 1
$P!{whereOrdenTrabajo}
$P!{whereTipoServicio}

group by
tab0.id_tipo_servicio,
tab0.id_orden_trabajo,
tab0.id_vigencia,
tab0.id_cen_op,
tab0.codigo_inventario_con,
tab0.desc_inventario_con,
tab0.desc_unidad_medida

order by
tab0.id_vigencia,
tab0.id_orden_trabajo,
tab0.codigo_inventario_con]]>
	</queryString>
	<field name="ID_ORDEN_TRABAJO" class="java.math.BigDecimal"/>
	<field name="ID_VIGENCIA" class="java.math.BigDecimal"/>
	<field name="ID_CEN_OP" class="java.math.BigDecimal"/>
	<field name="ID_TIPO_SERVICIO" class="java.math.BigDecimal"/>
	<field name="ID_INVENTARIO_CON" class="java.lang.String"/>
	<field name="CODIGO_INVENTARIO_CON" class="java.lang.String"/>
	<field name="DESC_INVENTARIO_CON" class="java.lang.String"/>
	<field name="DESC_UNIDAD_MEDIDA" class="java.lang.String"/>
	<field name="CANTIDAD" class="java.math.BigDecimal"/>
	<field name="VALOR_UNITARIO" class="java.math.BigDecimal"/>
	<field name="VALOR_TOTAL" class="java.math.BigDecimal"/>
	<variable name="total" class="java.math.BigDecimal" calculation="Sum">
		<variableExpression><![CDATA[$F{VALOR_TOTAL}]]></variableExpression>
	</variable>
	<columnHeader>
		<band height="25">
			<staticText>
				<reportElement mode="Opaque" x="203" y="0" width="305" height="25" backcolor="#88C2F9"/>
				<box leftPadding="2" 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" rotation="None">
					<font size="8" isBold="true"/>
				</textElement>
				<text><![CDATA[Descripción]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="508" y="0" width="75" height="25" backcolor="#88C2F9"/>
				<box leftPadding="2" 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" rotation="None">
					<font size="8" isBold="true"/>
				</textElement>
				<text><![CDATA[Unidad de Medida]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="128" y="0" width="75" height="25" backcolor="#88C2F9"/>
				<box leftPadding="2" 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" rotation="None">
					<font size="8" isBold="true"/>
				</textElement>
				<text><![CDATA[Código]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="638" y="0" width="100" height="25" backcolor="#88C2F9"/>
				<box leftPadding="2" 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" rotation="None">
					<font size="8" isBold="true"/>
				</textElement>
				<text><![CDATA[Valor Unitario]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="583" y="0" width="55" height="25" backcolor="#88C2F9"/>
				<box leftPadding="2" 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" rotation="None">
					<font size="8" isBold="true"/>
				</textElement>
				<text><![CDATA[Cant.]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="738" y="0" width="100" height="25" backcolor="#88C2F9"/>
				<box leftPadding="2" 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" rotation="None">
					<font size="8" isBold="true"/>
				</textElement>
				<text><![CDATA[Valor Total]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="0" y="0" width="64" height="25" backcolor="#88C2F9"/>
				<box leftPadding="2" 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>
				<text><![CDATA[Vigencia]]></text>
			</staticText>
			<staticText>
				<reportElement mode="Opaque" x="64" y="0" width="64" height="25" backcolor="#88C2F9"/>
				<box leftPadding="2" 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>
				<text><![CDATA[Orden No.]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="12" splitType="Stretch">
			<textField isBlankWhenNull="true">
				<reportElement x="128" y="0" width="75" height="12"/>
				<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{CODIGO_INVENTARIO_CON}]]></textFieldExpression>
			</textField>
			<textField pattern="#,##0.00;(#,##0.00)" isBlankWhenNull="true">
				<reportElement x="638" y="0" width="100" height="12"/>
				<box>
					<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{VALOR_UNITARIO}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="508" y="0" width="75" height="12"/>
				<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{DESC_UNIDAD_MEDIDA}]]></textFieldExpression>
			</textField>
			<textField pattern="###0.00;(-###0.00)" isBlankWhenNull="true">
				<reportElement x="583" y="0" width="55" height="12"/>
				<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.math.BigDecimal"><![CDATA[$F{CANTIDAD}]]></textFieldExpression>
			</textField>
			<textField pattern="#,##0.00;(-#,##0.00)" isBlankWhenNull="true">
				<reportElement x="738" y="0" width="100" height="12"/>
				<box>
					<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{VALOR_TOTAL}]]></textFieldExpression>
			</textField>
			<textField isBlankWhenNull="true">
				<reportElement x="203" y="0" width="305" height="12"/>
				<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">
					<font size="7"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{DESC_INVENTARIO_CON}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="64" y="0" width="64" height="12"/>
				<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.math.BigDecimal"><![CDATA[$F{ID_ORDEN_TRABAJO}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="0" y="0" width="64" height="12"/>
				<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.math.BigDecimal"><![CDATA[$F{ID_VIGENCIA}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<summary>
		<band height="20">
			<staticText>
				<reportElement mode="Opaque" x="0" y="0" width="738" height="20" forecolor="#000000" backcolor="#E2D9D9"/>
				<box>
					<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 Materias Primas y Servicios Externos]]></text>
			</staticText>
			<textField pattern="#,##0.00;(#,##0.00)" isBlankWhenNull="true">
				<reportElement mode="Opaque" x="738" y="0" width="99" height="20" backcolor="#E2D9D9"/>
				<box leftPadding="0" rightPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font size="8" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[$V{total}]]></textFieldExpression>
			</textField>
		</band>
	</summary>
	<noData>
		<band height="20">
			<textField pattern="#,##0.00;(#,##0.00)" isBlankWhenNull="true">
				<reportElement mode="Opaque" x="738" y="0" width="99" height="20" backcolor="#E2D9D9"/>
				<box leftPadding="0" rightPadding="2">
					<pen lineWidth="0.75"/>
					<topPen lineWidth="0.75"/>
					<leftPen lineWidth="0.0"/>
					<bottomPen lineWidth="0.75"/>
					<rightPen lineWidth="0.75"/>
				</box>
				<textElement textAlignment="Right" verticalAlignment="Middle">
					<font size="8" isBold="true"/>
				</textElement>
				<textFieldExpression class="java.math.BigDecimal"><![CDATA[0]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement mode="Opaque" x="0" y="0" width="738" height="20" forecolor="#000000" backcolor="#E2D9D9"/>
				<box>
					<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 Materias Primas y Servicios Externos]]></text>
			</staticText>
		</band>
	</noData>
</jasperReport>
