272 lines
6.9 KiB
SQL
272 lines
6.9 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `sage`.`invoiceIn_manager`(vYear INT, vCompanyFk INT)
|
|
BEGIN
|
|
/**
|
|
* Traslada la info de contabilidad relacionada con las facturas recibidas
|
|
*
|
|
* @vYear Año contable del que se quiere trasladar la información
|
|
* @vCompany Empresa de la que se quiere trasladar datos
|
|
*/
|
|
DECLARE vDone BOOL DEFAULT FALSE;
|
|
DECLARE vInvoiceFk INT;
|
|
DECLARE vXDiarioFk INT;
|
|
DECLARE vDatedFrom DATETIME;
|
|
DECLARE vDatedTo DATETIME;
|
|
DECLARE vSerialDua VARCHAR(1) DEFAULT 'D';
|
|
DECLARE vAccountVNL VARCHAR(10);
|
|
DECLARE vAccountTaxOutstanding VARCHAR(10);
|
|
DECLARE vInvoiceTypeSended VARCHAR(1);
|
|
DECLARE vCursor CURSOR FOR
|
|
SELECT IFNULL(x.CLAVE, x.FACTURA) invoiceInFk,
|
|
x.id XDiarioFk
|
|
FROM vn.XDiario x
|
|
JOIN(SELECT DISTINCT(x.ASIEN)
|
|
FROM vn.XDiario x
|
|
LEFT JOIN vn.invoiceIn ii ON x.CLAVE = ii.id
|
|
LEFT JOIN vn.invoiceInTax it ON it.invoiceInFk = ii.id
|
|
WHERE(it.taxTypeSageFk IS NOT NULL OR x.SERIE = vSerialDua COLLATE utf8mb3_unicode_ci)
|
|
AND x.enlazadoSage = FALSE
|
|
AND x.FECHA BETWEEN vDatedFrom AND vDatedTo
|
|
AND x.empresa_id = vCompanyFk
|
|
) sub ON sub.ASIEN = x.ASIEN
|
|
WHERE x.CLAVE IS NOT NULL;
|
|
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;
|
|
|
|
SELECT CAST(CONCAT(vYear, '-01-01') AS DATETIME), util.dayEnd(CAST(CONCAT(vYear, '-12-31') AS DATE))
|
|
INTO vDatedFrom, vDatedTo;
|
|
|
|
SELECT accountingAccount INTO vAccountVNL
|
|
FROM vn.`client`
|
|
WHERE name='VNL';
|
|
|
|
SELECT code INTO vAccountTaxOutstanding
|
|
FROM vn.pgcMaster
|
|
WHERE description = 'HP Iva pendiente';
|
|
|
|
SELECT codeSage INTO vInvoiceTypeSended
|
|
FROM invoiceType WHERE `code` ='sended';
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.invoiceDua;
|
|
CREATE TEMPORARY TABLE tmp.invoiceDua
|
|
SELECT x.id
|
|
FROM vn.XDiario x
|
|
JOIN vn.company c ON c.id = x.empresa_id
|
|
JOIN (SELECT ASIEN
|
|
FROM vn.XDiario x
|
|
WHERE x.enlazadoSage = FALSE
|
|
AND x.SUBCTA = vAccountTaxOutstanding COLLATE utf8mb3_unicode_ci
|
|
AND x.FECHA BETWEEN vDatedFrom AND vDatedTo
|
|
)sub ON sub.ASIEN = x.ASIEN
|
|
WHERE NOT x.CONTRA <=> vAccountVNL;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.invoiceIn;
|
|
CREATE TEMPORARY TABLE tmp.invoiceIn
|
|
SELECT i.id,
|
|
i.supplierRef,
|
|
i.serial COLLATE utf8mb3_unicode_ci serial,
|
|
i.supplierFk,
|
|
i.issued,
|
|
IF(expenseFkDeductible, FALSE, i.isVatDeductible) isVatDeductible,
|
|
IF(c.code = 'EUR', '',c.`code`) currencyFk
|
|
FROM vn.invoiceIn i
|
|
JOIN vn.currency c ON c.id = i.currencyFk
|
|
WHERE i.bookEntried BETWEEN vDatedFrom AND vDatedTo
|
|
UNION ALL
|
|
SELECT d.id,
|
|
d.code,
|
|
vSerialDua COLLATE utf8mb3_unicode_ci,
|
|
d.companyFk ,
|
|
d.issued,
|
|
FALSE,
|
|
'' -- EUROS
|
|
FROM vn.dua d
|
|
WHERE d.issued IS NOT NULL
|
|
AND code IS NOT NULL;
|
|
|
|
OPEN vCursor;
|
|
|
|
l: LOOP
|
|
FETCH vCursor INTO vInvoiceFk, vXDiarioFk;
|
|
|
|
IF vDone THEN
|
|
LEAVE l;
|
|
END IF;
|
|
CALL invoiceIn_add(vInvoiceFk, vXDiarioFk);
|
|
END LOOP;
|
|
|
|
CLOSE vCursor;
|
|
|
|
DROP TEMPORARY TABLE tmp.invoiceDua;
|
|
DROP TEMPORARY TABLE tmp.invoiceIn;
|
|
|
|
-- ASIENTOS CON IVA SOPORTADO 472. y 477. hay que informar 2 líneas la info de facturas una como tipo de factura emitida y otra como recibida
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.movContaIVA;
|
|
CREATE TEMPORARY TABLE tmp.movContaIVA
|
|
SELECT sub3.id,
|
|
mci.CodigoDivisa,
|
|
mci.BaseIva1,
|
|
mci.PorBaseCorrectora1,
|
|
mci.PorIva1,
|
|
mci.CuotaIva1,
|
|
mci.PorRecargoEquivalencia1,
|
|
mci.RecargoEquivalencia1,
|
|
mci.CodigoTransaccion1,
|
|
mci.CodigoIva1,
|
|
mci.BaseIva2,
|
|
mci.PorBaseCorrectora2,
|
|
mci.PorIva2,
|
|
mci.CuotaIva2,
|
|
mci.PorRecargoEquivalencia2,
|
|
mci.RecargoEquivalencia2,
|
|
mci.CodigoTransaccion2,
|
|
mci.CodigoIva2,
|
|
mci.BaseIva3,
|
|
mci.PorBaseCorrectora3,
|
|
mci.PorIva3,
|
|
mci.CuotaIva3,
|
|
mci.PorRecargoEquivalencia3,
|
|
mci.RecargoEquivalencia3,
|
|
mci.CodigoTransaccion3,
|
|
mci.CodigoIva3,
|
|
mci.BaseIva4,
|
|
mci.PorBaseCorrectora4,
|
|
mci.PorIva4,
|
|
mci.CuotaIva4,
|
|
mci.PorRecargoEquivalencia4,
|
|
mci.RecargoEquivalencia4,
|
|
mci.CodigoTransaccion4,
|
|
mci.CodigoIva4,
|
|
mci.Año,
|
|
mci.Serie,
|
|
mci.Factura,
|
|
mci.SuFacturaNo,
|
|
mci.FechaFactura,
|
|
mci.ImporteFactura,
|
|
vInvoiceTypeSended,
|
|
mci.CodigoCuentaFactura,
|
|
mci.CifDni,
|
|
mci.Nombre,
|
|
mci.CodigoRetencion,
|
|
mci.BaseRetencion,
|
|
mci.PorRetencion,
|
|
mci.ImporteRetencion,
|
|
mci.SiglaNacion,
|
|
mci.EjercicioFactura,
|
|
x.FECHA,
|
|
mci.Exclusion347,
|
|
mci.MantenerAsiento,
|
|
mci.Metalico347,
|
|
mci.ClaveOperacionFactura,
|
|
mci.TipoRectificativa,
|
|
mci.FechaFacturaOriginal,
|
|
mci.CuotaIvaOriginal,
|
|
mci.BaseImponibleOriginal,
|
|
mci.ClaseAbonoRectificativas,
|
|
mci.RecargoEquivalenciaOriginal,
|
|
mci.LibreA1,
|
|
mci.IvaDeducible1,
|
|
mci.IvaDeducible2,
|
|
mci.IvaDeducible3,
|
|
mci.IvaDeducible4,
|
|
mci.FechaGrabacion,
|
|
mci.Intracomunitaria,
|
|
mci.moveData
|
|
FROM movContaIVA mci
|
|
JOIN vn.XDiario x ON x.id = mci.id
|
|
JOIN (SELECT x.ASIEN, x.id
|
|
FROM vn.XDiario x
|
|
JOIN(SELECT DISTINCT(x.ASIEN) ASIEN
|
|
FROM vn.XDiario x
|
|
JOIN (SELECT DISTINCT(ASIEN)
|
|
FROM vn.XDiario x
|
|
WHERE SUBCTA LIKE '472%'
|
|
AND x.enlazadoSage = FALSE
|
|
AND x.empresa_id = vCompanyFk
|
|
AND x.FECHA BETWEEN vDatedFrom AND vDatedTo
|
|
) sub ON sub.ASIEN = x.ASIEN
|
|
WHERE x.SUBCTA LIKE '477%'
|
|
)sub2 ON sub2.ASIEN = x.ASIEN
|
|
WHERE x.CONTRA IS NOT NULL
|
|
AND x.SUBCTA LIKE '477%'
|
|
GROUP BY x.ASIEN
|
|
)sub3 ON sub3.ASIEN = x.ASIEN;
|
|
|
|
INSERT INTO movContaIVA
|
|
(`id`,
|
|
`CodigoDivisa`,
|
|
`BaseIva1`,
|
|
`PorBaseCorrectora1`,
|
|
`PorIva1`,
|
|
`CuotaIva1`,
|
|
`PorRecargoEquivalencia1`,
|
|
`RecargoEquivalencia1`,
|
|
`CodigoTransaccion1`,
|
|
`CodigoIva1`,
|
|
`BaseIva2`,
|
|
`PorBaseCorrectora2`,
|
|
`PorIva2`,
|
|
`CuotaIva2`,
|
|
`PorRecargoEquivalencia2`,
|
|
`RecargoEquivalencia2`,
|
|
`CodigoTransaccion2`,
|
|
`CodigoIva2`,
|
|
`BaseIva3`,
|
|
`PorBaseCorrectora3`,
|
|
`PorIva3`,
|
|
`CuotaIva3`,
|
|
`PorRecargoEquivalencia3`,
|
|
`RecargoEquivalencia3`,
|
|
`CodigoTransaccion3`,
|
|
`CodigoIva3`,
|
|
`BaseIva4`,
|
|
`PorBaseCorrectora4`,
|
|
`PorIva4`,
|
|
`CuotaIva4`,
|
|
`PorRecargoEquivalencia4`,
|
|
`RecargoEquivalencia4`,
|
|
`CodigoTransaccion4`,
|
|
`CodigoIva4`,
|
|
`Año`,
|
|
`Serie`,
|
|
`Factura`,
|
|
`SuFacturaNo`,
|
|
`FechaFactura`,
|
|
`ImporteFactura`,
|
|
`TipoFactura`,
|
|
`CodigoCuentaFactura`,
|
|
`CifDni`,
|
|
`Nombre`,
|
|
`CodigoRetencion`,
|
|
`BaseRetencion`,
|
|
`PorRetencion`,
|
|
`ImporteRetencion`,
|
|
`SiglaNacion`,
|
|
`EjercicioFactura`,
|
|
`FechaOperacion`,
|
|
`Exclusion347`,
|
|
`MantenerAsiento`,
|
|
`Metalico347`,
|
|
`ClaveOperacionFactura`,
|
|
`TipoRectificativa`,
|
|
`FechaFacturaOriginal`,
|
|
`CuotaIvaOriginal`,
|
|
`BaseImponibleOriginal`,
|
|
`ClaseAbonoRectificativas`,
|
|
`RecargoEquivalenciaOriginal`,
|
|
`LibreA1`,
|
|
`IvaDeducible1`,
|
|
`IvaDeducible2`,
|
|
`IvaDeducible3`,
|
|
`IvaDeducible4`,
|
|
`FechaGrabacion`,
|
|
`Intracomunitaria`,
|
|
`moveData`)
|
|
SELECT *
|
|
FROM tmp.movContaIVA;
|
|
|
|
DROP TEMPORARY TABLE tmp.movContaIVA;
|
|
|
|
END$$
|
|
DELIMITER ;
|