DROP PROCEDURE if EXISTS sp_alm_guiaremision_registrar;
DELIMITER $$
CREATE  PROCEDURE `sp_alm_guiaremision_registrar`(IN `IssueDate` VARCHAR(10), IN `Note` VARCHAR(200), IN `DescriptionReasonTransfer` VARCHAR(100), IN `TotalGrossWeightGRE` VARCHAR(20), IN `NumberPackages` INT, IN `unitCodeGrossWeightGRE` VARCHAR(10), IN `TypeDocumenttransmitter` INT, IN `transmitterName` VARCHAR(100), IN `addresseeID` VARCHAR(20), IN `TypeDocumentaddressee` INT, IN `addresseeName` VARCHAR(200), IN `motivemovedCode` VARCHAR(2), IN `transfermobility` INT, IN `LicensePlateID` VARCHAR(10), IN `DriverPersonID` VARCHAR(12), IN `DriverPersonDocumentType` INT, IN `movedstartdate` VARCHAR(11), IN `DeliveryUbi` VARCHAR(10), IN `Delivery` VARCHAR(200), IN `OriginAddressUbi` VARCHAR(200), IN `OriginAddress` VARCHAR(200), OUT `result_id` INT, OUT `result_invoice` VARCHAR(50), IN `usercreate` INT, OUT `fechaRegister` VARCHAR(50), IN `estado` INT, IN `nameTransportista` VARCHAR(200))
    NO SQL
BEGIN
    DECLARE correlativo VARCHAR(100);
    DECLARE fecharegistro DATETIME;
    
    SET correlativo := (SELECT CONCAT(SUBSTRING(referral_guide_format,1,5), REPEAT( '0', 7 - LENGTH( referral_guide_number + 1) ) , referral_guide_number + 1)  FROM tec_settings);
    
    SET fecharegistro := (select now());

    INSERT INTO `tec_referral_guide`(`serieNumero`, `IssueDate`, 
                                     `Note`, `DescriptionReasonTransfer`, `TotalGrossWeightGRE`, 
                                     `NumberPackages`, `unitCodeGrossWeightGRE`, `TypeDocumenttransmitter`, 
                                     `transmitterName`, `addresseeID`, `TypeDocumentaddressee`, 
                                     `addresseeName`, `motivemovedCode`, `transfermobility`, 
                                     `LicensePlateID`, `DriverPersonID`, `DriverPersonDocumentType`, 
                                     `movedstartdate`, `DeliveryUbi`, 
                                     `Delivery`, `OriginAddressUbi`, `OriginAddress`, `user_create`, `date_create`, `estado`,
                                     `nameTransportista`) 
                                     VALUES (correlativo, IssueDate, 
                                     Note, DescriptionReasonTransfer, TotalGrossWeightGRE, 
                                     NumberPackages, unitCodeGrossWeightGRE, TypeDocumenttransmitter, 
                                     transmitterName, addresseeID, TypeDocumentaddressee, 
                                     addresseeName, motivemovedCode, transfermobility, 
                                     LicensePlateID, DriverPersonID, DriverPersonDocumentType, 
                                     movedstartdate, DeliveryUbi, 
                                     Delivery, OriginAddressUbi, OriginAddress,usercreate, fecharegistro, estado, 
                                     nameTransportista);

    
    
    UPDATE tec_settings SET referral_guide_number = referral_guide_number + 1;

    SET result_id :=  (SELECT LAST_INSERT_ID());
    SET result_invoice := (correlativo);
    SET fechaRegister := (fecharegistro);

END$$
DELIMITER ;