salix/db/routines/vn/functions/invoiceSerial.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`invoiceSerial`(
	vClientFk INT,
	vCompanyFk INT,
	vType CHAR(15)
)
	RETURNS char(2) CHARSET utf8mb3 COLLATE utf8mb3_general_ci
	DETERMINISTIC
BEGIN
/**
* Obtiene la serie de una factura
* dependiendo del area del cliente.
*
* @param vClientFk Id del cliente
* @param vCompanyFk Id de la empresa
* @param vType Tipo de factura (vn.invoiceOutSerial.type[ENUM])
* @return vSerie de la factura
*/
	DECLARE vTaxArea VARCHAR(25) COLLATE utf8mb3_general_ci;
	DECLARE vTransactionCode INT(2);
	DECLARE vSerie CHAR(2);

	IF (SELECT hasInvoiceSimplified FROM client WHERE id = vClientFk) THEN
		RETURN 'S';
	END IF;

	SELECT addressTaxArea(defaultAddressFk, vCompanyFk) INTO vTaxArea
		FROM client
		WHERE id = vClientFk;

	SELECT CodigoTransaccion INTO vTransactionCode
		FROM taxArea
		WHERE code = vTaxArea;

	SELECT ios.code INTO vSerie
		FROM invoiceOutSerial ios
			JOIN taxArea ta ON ta.code = ios.taxAreaFk
		WHERE ios.`type` = vType
			AND ta.CodigoTransaccion = vTransactionCode;

	RETURN vSerie;
END$$
DELIMITER ;