CREATE OR ALTER procedure SPU_NF_OBSERVACAO (
    ESTABELECIMENTO integer,
    NF_ID integer,
    ORIGEM varchar(10))
as
declare variable BASELEGAL_ID integer;
declare variable CAMPO_LIVRE integer;
declare variable JA_INSERIDO integer;
declare variable BASELEGAL_DESCRICAO varchar(8000);
declare variable BASELEGAL_DESCRICAO_ALL varchar(8000);
declare variable SCRIPT char(8000);
declare variable FISCO char(1);
BEGIN

    BASELEGAL_ID = COALESCE((SELECT FIRST 1 A.BASELEGAL_ID FROM TBESTABELECIMENTO A WHERE A.CODIGO = :ESTABELECIMENTO),0);
    IF(BASELEGAL_ID > 0)THEN
    BEGIN
        SELECT FIRST 1
            TRIM(COALESCE(A.FISCO, '0')) AS FISCO,
            A.BASELEGAL
        FROM TBBASELEGAL A
        WHERE A.CODIGO = :BASELEGAL_ID
        INTO :FISCO, :BASELEGAL_DESCRICAO_ALL;

        IF(:BASELEGAL_DESCRICAO_ALL <> '')THEN
        BEGIN
            FOR
                SELECT
                    OSPLIT
            FROM SPLIT_V2(:BASELEGAL_DESCRICAO_ALL, ASCII_CHAR(13))
            INTO :BASELEGAL_DESCRICAO
            DO BEGIN
                IF(:ORIGEM = 'NFS') THEN
                BEGIN

                    FOR SELECT FIRST 1
                        
                        CASE 
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO01,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO02,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO03,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO04,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO05,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO06,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO07,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO08,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO09,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO10,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO11,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO12,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO13,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO14,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                            WHEN TRIM(SUBSTRING(COALESCE(OBSERVACAO15,'') FROM 1 FOR 20)) = TRIM(SUBSTRING(:BASELEGAL_DESCRICAO FROM 1 FOR 20))  THEN 1
                        ELSE 0
                        END AS JA_INSERIDO,

                        CASE 
                            WHEN COALESCE(OBSERVACAO01,'') = '' AND :FISCO = '0'  THEN 1
                            WHEN COALESCE(OBSERVACAO02,'') = '' AND :FISCO = '0'  THEN 2
                            WHEN COALESCE(OBSERVACAO03,'') = '' AND :FISCO = '0'  THEN 3
                            WHEN COALESCE(OBSERVACAO04,'') = '' AND :FISCO = '0'  THEN 4
                            WHEN COALESCE(OBSERVACAO05,'') = '' AND :FISCO = '0'  THEN 5
                            WHEN COALESCE(OBSERVACAO06,'') = '' AND :FISCO = '0'  THEN 6
                            WHEN COALESCE(OBSERVACAO07,'') = '' AND :FISCO = '0'  THEN 7
                            WHEN COALESCE(OBSERVACAO08,'') = '' AND :FISCO = '0'  THEN 8
                            WHEN COALESCE(OBSERVACAO09,'') = '' AND :FISCO = '0'  THEN 9
                            WHEN COALESCE(OBSERVACAO10,'') = '' AND :FISCO = '0'  THEN 10
                            WHEN COALESCE(OBSERVACAO11,'') = '' AND :FISCO = '1'  THEN 11
                            WHEN COALESCE(OBSERVACAO12,'') = '' AND :FISCO = '1'  THEN 12
                            WHEN COALESCE(OBSERVACAO13,'') = '' AND :FISCO = '1'  THEN 13
                            WHEN COALESCE(OBSERVACAO14,'') = '' AND :FISCO = '1'  THEN 14
                            WHEN COALESCE(OBSERVACAO15,'') = '' AND :FISCO = '1'  THEN 15
                        ELSE 0
                        END AS CAMPO_LIVRE
        
                    FROM TBNFS_OBSERVACAO
                    WHERE NFS_CONTROLE = :NF_ID INTO CAMPO_LIVRE, JA_INSERIDO DO
                    BEGIN
                        IF(:CAMPO_LIVRE > 0 AND JA_INSERIDO = 0)THEN
                        BEGIN
                            :BASELEGAL_DESCRICAO = FN_ELIPSES(:BASELEGAL_DESCRICAO, 130);

                            :SCRIPT = 'UPDATE TBNFS_OBSERVACAO SET OBSERVACAO'||FN_LPAD(:CAMPO_LIVRE,2,0)||' = '''||:BASELEGAL_DESCRICAO||''' WHERE NFS_CONTROLE = '||:NF_ID;
                            EXECUTE STATEMENT :SCRIPT;


                        END
                    END
                END
             END
        END
    END

END;