SP-Common Function (11-09-2024)

DELIMITER $$
 
CREATE PROCEDURE adm_mst_spSequenceGid(IN pModule_Code VARCHAR(1024))
BEGIN
    DECLARE lsfinyear VARCHAR(500);
    DECLARE lsSeqNo INT;
    DECLARE lsTempGid VARCHAR(100); 
    DECLARE lsSeqflag VARCHAR(1) DEFAULT '';
    DECLARE lsSequenceCurval VARCHAR(45) DEFAULT '';
    DECLARE lsSequenceFormat VARCHAR(45) DEFAULT '';
    START TRANSACTION;
    SELECT YEAR(fyear_start) INTO lsfinyear
    FROM adm_mst_tyearendactivities
    WHERE CURDATE() BETWEEN fyear_start AND fyear_end;
    SET lsfinyear = IF(lsfinyear IS NULL OR lsfinyear = '',
                       (SELECT YEAR(fyear_start) 
                        FROM adm_mst_tyearendactivities
                        ORDER BY finyear_gid DESC
                        LIMIT 1),
                       lsfinyear);
    SELECT sequence_flag, sequence_curval, sequence_format 
    INTO lsSeqflag, lsSequenceCurval, lsSequenceFormat
    FROM adm_mst_tsequence
    WHERE sequence_code = pModule_Code AND finyear = lsfinyear
    LIMIT 1;
    SET lsSeqNo = lsSequenceCurval + 1;
    SET lsTempGid = CASE
        WHEN lsSeqflag = 'Y' THEN
            CONCAT(pModule_Code, LPAD(lsSeqNo, lsSequenceFormat, '0'))
        WHEN lsSeqflag = 'N' THEN
            CONCAT(pModule_Code, DATE_FORMAT(CURDATE(), '%Y%m%d'), lsSeqNo)
        ELSE
            'E'
    END;
    UPDATE adm_mst_tsequence
    SET sequence_curval = lsSeqNo
    WHERE sequence_code = pModule_Code AND finyear = lsfinyear;
    COMMIT;
    SELECT lsTempGid;
END$$
 
DELIMITER ;