salix/db/routines/sage/procedures/invoiceIn_manager.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 ;