0042962: Error en la selección de facturas a enviar por proceso SII

Description

Analizando los datos de la aplicación del cliente, nos hemos percatado de que algunas de las facturas seleccionadas por el proceso de envío de facturas al SII no deberían serlo.
Específicamente en los siguientes casos:

    - Dada una cadencia de envió de facturas de dos días, si se ejecuta el proceso de envió de facturas, las facturas generadas y completadas hoy serán seleccionadas.
    - Dada una fecha de acogida al SII del 01-01-2019, si se ejecuta el proceso de envío de facturas, este seleccionará facturas con fecha anterior a la fecha de acogida al SII.

La consulta de selección presente en la clase SendInvoicesGroupsToAEAT.java es la siguiente:

    select i.id, i.documentNo, i.salesTransaction
            from Invoice i
            where i.aeatsiiIssent = false
            and i.organization.id = (:orgId)
            and i.processed = true
            and i.aeatsiiErrorRegistral = false
            and (
                    (i.salesTransaction = true
                     and ((i.invoiceDate <= :todayMinusCadenceSales
                          and i.invoiceDate >= :sinceSii)
                          or (month(i.aeatsiiFechaOperacion) - month(DATE(:today)) = 0
                              and (i.invoiceDate < :today
                                    or (i.invoiceDate <= :dateLimitSII
                                        and i.invoiceDate > :salesLimitCadence
                                        and DATE(i.invoiceDate) = DATE(:today))
                                  )
                         )
                         or (month(i.aeatsiiFechaOperacion) <> month(i.invoiceDate)
                           and DATE(i.invoiceDate) = DATE(:today))
                    )
                  )
                 or (i.salesTransaction = false
                        and i.posted = true
                        and ((i.aeatsiiFechaRegCont <= :todayMinusCadencePurchase
                        and i.aeatsiiFechaRegCont >= :sinceSii)
                            or (month(i.aeatsiiFechaOperacion) - month(DATE(:today)) = 0
                                and (i.aeatsiiFechaRegCont < :today
                                      or (i.aeatsiiFechaRegCont <= :dateLimitSII
                                          and i.aeatsiiFechaRegCont > :purchaseLimitCadence
                                          and DATE(i.aeatsiiFechaRegCont) = DATE(:today))
                                    )
                                )
                            )
                            or (month(i.aeatsiiFechaRegCont) <> month(i.aeatsiiFechaOperacion)
                                and DATE(i.aeatsiiFechaRegCont) = DATE(:today))
                       )
                   )

Tras analizar la consulta, hemos comprobado como existe un problema con los operadores OR y AND, que posibilita la selección de facturas que dan lugar a los dos problemas mencionados arriba.
Concretamente, creemos que el siguiente trozo de código es el causante del problema (para facturas de venta en nuestro caso):

                        (i.invoiceDate <= :todayMinusCadenceSales
                          and i.invoiceDate >= :sinceSii)
                          or (month(i.aeatsiiFechaOperacion) - month(DATE(:today)) = 0
                              and (i.invoiceDate < :today
                                    or (i.invoiceDate <= :dateLimitSII
                                        and i.invoiceDate > :salesLimitCadence
                                        and DATE(i.invoiceDate) = DATE(:today))
                                  )
                         )
                         or (month(i.aeatsiiFechaOperacion) <> month(i.invoiceDate)
                           and DATE(i.invoiceDate) = DATE(:today))

Dicho trozo de código, queda dividido por dos OR en tres sentencias distintas.
La sentencia central, la cual se detalla a continuación, posibilita la selección de facturas que no deberían ser seleccionadas dada la cadencia de envío o la fecha de acogida al SII.

                      (month(i.aeatsiiFechaOperacion) - month(DATE(:today)) = 0
                          and (i.invoiceDate < :today
                                or (i.invoiceDate <= :dateLimitSII
                                    and i.invoiceDate > :salesLimitCadence
                                    and DATE(i.invoiceDate) = DATE(:today))
                              )
                     )

Steps To Reproduce

Alta en el SII

Proposed Solution

Tras analizar la consulta, hemos comprobado como existe un problema con los operadores OR y AND, que posibilita la selección de facturas que dan lugar a los dos problemas mencionados arriba.
Concretamente, creemos que el siguiente trozo de código es el causante del problema (para facturas de venta en nuestro caso):

                        (i.invoiceDate <= :todayMinusCadenceSales
                          and i.invoiceDate >= :sinceSii)
                          or (month(i.aeatsiiFechaOperacion) - month(DATE(:today)) = 0
                              and (i.invoiceDate < :today
                                    or (i.invoiceDate <= :dateLimitSII
                                        and i.invoiceDate > :salesLimitCadence
                                        and DATE(i.invoiceDate) = DATE(:today))
                                  )
                         )
                         or (month(i.aeatsiiFechaOperacion) <> month(i.invoiceDate)
                           and DATE(i.invoiceDate) = DATE(:today))

Dicho trozo de código, queda dividido por dos OR en tres sentencias distintas.
La sentencia central, la cual se detalla a continuación, posibilita la selección de facturas que no deberían ser seleccionadas dada la cadencia de envío o la fecha de acogida al SII.

                      (month(i.aeatsiiFechaOperacion) - month(DATE(:today)) = 0
                          and (i.invoiceDate < :today
                                or (i.invoiceDate <= :dateLimitSII
                                    and i.invoiceDate > :salesLimitCadence
                                    and DATE(i.invoiceDate) = DATE(:today))
                              )
                     )

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

has duplicate

defect

0044272

closed

Mery Anelo

No se respeta días de cadencia

Notes
(0120310) Mery Anelo (developer) 2020-05-27 00:54	Test plan: - Log as Group Admin Role. - Config SII, fields "Sales Invoice SII sending Cadence" and "Purchase Invoice SII sending Cadence" with two or more days. - Create new Invoices. - Make sure that the date set as today (On Purchase Invoices set field "Fecha registro contable". On Sales Invoices set field "Fecha de la factura"). - Complete the other data and add lines. - Complete the invoice. - Program the automatic process "Proceso alta en SII de facturas agrupadas". - Verify that the process does not send the invoices with today's date.

(0120763) hgbot (developer) 2020-06-09 20:26	Repository: erp/pmods/org.openbravo.module.sii Changeset: 1f76161df5b8676716aecf78258ef3282c5237b2 Author: Mery Anelo <mery.anelo <at> smfconsulting.es> Date: Tue Jun 09 14:47:18 2020 -0300 URL: http://code.openbravo.com/erp/pmods/org.openbravo.module.sii/rev/1f76161df5b8676716aecf78258ef3282c5237b2 [^] Fixed bug 42962: HQL format --- M src/org/openbravo/module/sii/process/background/SendInvoicesGroupsToAEAT.java ---

(0120764) hgbot (developer) 2020-06-09 20:26	Repository: erp/pmods/org.openbravo.module.sii Changeset: 5dbfbc8f5f01beba8a14f985b4afeaa12b6508a1 Author: Mery Anelo <mery.anelo <at> smfconsulting.es> Date: Tue Jun 09 14:47:42 2020 -0300 URL: http://code.openbravo.com/erp/pmods/org.openbravo.module.sii/rev/5dbfbc8f5f01beba8a14f985b4afeaa12b6508a1 [^] Fixed bug 42962: Automatic process "Alta SII", considers the cadence. Regrouping of invoice registration restrictions for the process "Alta SII". --- M src/org/openbravo/module/sii/process/background/SendInvoicesGroupsToAEAT.java ---

(0120765) hgbot (developer) 2020-06-09 20:26	Repository: erp/pmods/org.openbravo.module.sii Changeset: 5ab0cb42e0b3976e530fc62a8d2c44d61e0e536a Author: Mery Anelo <mery.anelo <at> smfconsulting.es> Date: Tue Jun 09 14:48:06 2020 -0300 URL: http://code.openbravo.com/erp/pmods/org.openbravo.module.sii/rev/5ab0cb42e0b3976e530fc62a8d2c44d61e0e536a [^] Fixed bug 42962: HQL format --- M src/org/openbravo/module/sii/process/background/SendInvoicesGroupsToAEAT.java ---

Issue History
Date Modified	Username	Field	Change
2020-01-22 14:06	qualitic	New Issue
2020-01-22 14:06	qualitic	Assigned To	=> Jorge Bravo
2020-01-22 14:07	Jorge Bravo	Assigned To	Jorge Bravo => Mery Anelo
2020-05-27 00:54	Mery Anelo	Status	new => scheduled
2020-05-27 00:54	Mery Anelo	Note Added: 0120310
2020-06-03 15:27	Practics	Issue Monitored: Practics
2020-06-03 23:24	Mery Anelo	Relationship added	has duplicate 0044272
2020-06-09 20:26	hgbot	Checkin
2020-06-09 20:26	hgbot	Note Added: 0120763
2020-06-09 20:26	hgbot	Status	scheduled => resolved
2020-06-09 20:26	hgbot	Resolution	open => fixed
2020-06-09 20:26	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/pmods/org.openbravo.module.sii/rev/1f76161df5b8676716aecf78258ef3282c5237b2 [^]
2020-06-09 20:26	hgbot	Checkin
2020-06-09 20:26	hgbot	Note Added: 0120764
2020-06-09 20:26	hgbot	Fixed in SCM revision	http://code.openbravo.com/erp/pmods/org.openbravo.module.sii/rev/1f76161df5b8676716aecf78258ef3282c5237b2 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.module.sii/rev/5dbfbc8f5f01beba8a14f985b4afeaa12b6508a1 [^]
2020-06-09 20:26	hgbot	Checkin
2020-06-09 20:26	hgbot	Note Added: 0120765
2020-06-09 20:26	hgbot	Fixed in SCM revision	http://code.openbravo.com/erp/pmods/org.openbravo.module.sii/rev/5dbfbc8f5f01beba8a14f985b4afeaa12b6508a1 [^] => http://code.openbravo.com/erp/pmods/org.openbravo.module.sii/rev/5ab0cb42e0b3976e530fc62a8d2c44d61e0e536a [^]
2020-06-22 14:19	Mery Anelo	Status	resolved => closed