CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `forge`@`%` 
    SQL SECURITY DEFINER
VIEW `v_consultacomprobantes` AS
    SELECT 
        `receipts`.`id` AS `IdComprobante`,
        `receipt_types`.`credit` AS `NotaCredito`,
        `receipts`.`type_id` AS `IdTipoComprobante`,
        `persons`.`id` AS `IdProveedorCliente`,
        `receipts`.`person_id_owner` AS `IdEmpresa`,
        `b`.`document` AS `DocumentoEmpresa`,
        `b`.`field_name1` AS `NombreEmpresa`,
        `p`.`id` AS `IdPeriodo`,
        `p`.`date_from` AS `Desde`,
        `p`.`date_to` AS `Hasta`,
        `p`.`code` AS `Periodo`,
        `types`.`name` AS `Operacion`,
        `persons`.`field_name1` AS `Proveedor`,
        `persons`.`document_type` AS `TipoDocumento`,
        `persons`.`document` AS `Documento`,
        `zones`.`name` AS `Zona`,
        `status`.`name` AS `Estado`,
        `activities`.`name` AS `Actividad`,
        `receipts`.`receipt_date` AS `FechaComprobante`,
        `receipts`.`reference` AS `Referencia`,
        `retention_types`.`name` AS `TipoDeRetencion`,
        `receipt_types`.`name` AS `TipoComprobante`,
        `receipts`.`code_ticket` AS `Suc`,
        `receipts`.`number` AS `Numero`,
        
        SUM((CASE
            WHEN
                (`receipt_types`.`credit` = 1) THEN (`receipts`.`amount` * -(1)) ELSE `receipts`.`amount` END)) AS `Total`,
        SUM((CASE
            WHEN
                ((`receipt_taxes`.`tax_id` = 3) AND (`receipt_types`.`credit` = 1)) THEN (`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 3) THEN `receipt_taxes`.`amount` 
            ELSE 0 END)) AS `gravado_27`,
        SUM((CASE
            WHEN ((`receipt_taxes`.`tax_id` = 3) AND (`receipt_types`.`credit` = 1)) THEN(`receipt_taxes`.`tax_amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 3) THEN `receipt_taxes`.`tax_amount`
            ELSE 0
        END)) AS `IVA_27`,
        SUM((CASE
            WHEN ((`receipt_taxes`.`tax_id` = 2) AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 2) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `gravado_21`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 2 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 2) THEN `receipt_taxes`.`tax_amount`
            ELSE 0
        END)) AS `IVA_21`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `gravado_105`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1) THEN `receipt_taxes`.`tax_amount`
            ELSE 0
        END)) AS `IVA_105`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 5 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 5) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `gravado_5`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 5 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 5) THEN `receipt_taxes`.`tax_amount`
            ELSE 0
        END)) AS `IVA_5`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 6 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 6) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `gravado_0`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 6 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 6) THEN `receipt_taxes`.`tax_amount`
            ELSE 0
        END)) AS `IVA_0`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 4 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 4) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `gravado_2_5`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 4 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 4) THEN `receipt_taxes`.`tax_amount`
            ELSE 0
        END)) AS `IVA_2_5`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 7 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 7) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `no_gravado`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1000 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1000) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `percepIva`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1001 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1001) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Imps_Internos`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1002 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1002) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Imp_Gasoil`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1003 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1003) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Imp_Hidricos`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1004 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1004) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `ITC`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1005 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1005) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Buenos_Aires`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1006 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1006) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Capital_Federal`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1007 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1007) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Catamarca`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1008 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1008) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Chaco`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1009 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1009) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Chubut`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1010 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1010) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Cordoba`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1011 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1011) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Corrientes`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1012 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1012) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Entre_Rios`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1013 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1013) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Formosa`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1014 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1014) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Jujuy`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1015 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1015) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_La_Pampa`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1016 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1016) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_La_Rioja`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1017 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1017) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Mendoza`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1018 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1018) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Misiones`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1019 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1019) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Neuquen`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1020 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1020) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Pascua_Lama`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1021 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1021) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Rio_Negro`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1022 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1022) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Salta`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1023 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1023) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_San_Juan`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1024 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1024) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_San_Luis`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1025 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1025) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Santa_Cruz`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1026 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1026) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Santa_Fe`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1027 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1027) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Santiago_del_Estero`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1028 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1028) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Tierra_del_Fuego`,
        SUM((CASE
            WHEN (`receipt_taxes`.`tax_id` = 1029 AND (`receipt_types`.`credit` = 1))THEN(`receipt_taxes`.`amount` * -(1))
            WHEN (`receipt_taxes`.`tax_id` = 1029) THEN `receipt_taxes`.`amount`
            ELSE 0
        END)) AS `Perc_IIBB_Tucuman`
    FROM
        ((((((((((((`receipts`
        LEFT JOIN `persons` `b` ON ((`receipts`.`person_id_owner` = `b`.`id`)))
        LEFT JOIN `periods` `p` ON ((`receipts`.`period_id` = `p`.`id`)))
        LEFT JOIN `receipt_types` ON ((`receipts`.`type_receipt_id` = `receipt_types`.`id`)))
        LEFT JOIN `retention_types` ON ((`receipts`.`retention_type_id` = `retention_types`.`id`)))
        LEFT JOIN `status` ON ((`receipts`.`status_id` = `status`.`id`)))
        LEFT JOIN `persons` ON ((`receipts`.`person_id_relationed` = `persons`.`id`)))
        LEFT JOIN `receipt_taxes` ON ((`receipts`.`id` = `receipt_taxes`.`receipt_id`)))
        LEFT JOIN `zones` ON ((`receipts`.`zone_id` = `zones`.`id`)))
        LEFT JOIN `activities` ON ((`receipts`.`activity_id` = `activities`.`id`)))
        LEFT JOIN `types` ON ((`receipts`.`type_id` = `types`.`id`)))
        LEFT JOIN `system_taxes` ON ((`receipt_taxes`.`tax_id` = `system_taxes`.`id`)))
        LEFT JOIN `other_taxes` ON ((`receipt_taxes`.`tax_id` = `other_taxes`.`id`)))
    GROUP BY `receipts`.`id` , `receipts`.`type_id` , `persons`.`id` , `receipts`.`person_id_owner` , `b`.`document` , `b`.`field_name1` , `p`.`id` , `p`.`date_from` , `p`.`date_to` , `p`.`code` , `types`.`name` , `persons`.`field_name1` , `persons`.`document_type` , `persons`.`document` , `zones`.`name` , `status`.`name` , `activities`.`name` , `receipts`.`receipt_date` , `receipts`.`reference` , `retention_types`.`name` , `receipt_types`.`name` , `receipts`.`code_ticket` , `receipts`.`number` , `receipts`.`amount`
    ORDER BY `p`.`code` , `persons`.`document` , `receipts`.`code_ticket` , `receipts`.`number`