SET TERM ^ ;

CREATE OR ALTER procedure SPC_LISTAR_REMSSA (
    DATA1 date = null,
    DATA2 date = null,
    FAMILIAS blob sub_type 1 segment size 80 = '',
    ESTABELECIMENTO integer = 0)
returns (
    REMESSA varchar(40),
    REMESSA_ID integer)
as
declare variable OREMESSA integer;
declare variable TIPO_REMESSA integer;
begin

    for
        SELECT first 50 distinct
        
            R.REMESSA,
            R.NUMERO
        
        FROM TBPEDIDO_ITEM_PROCESSADO A, TBREMESSA R, TBMODELO M
        WHERE A.ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
        AND A.ESTABELECIMENTO_CODIGO = R.ESTABELECIMENTO_CODIGO
        AND A.REMESSA = R.NUMERO
        AND A.MODELO_CODIGO = M.CODIGO
        
        --and iif(:STATUS_TALAO > 0 , iif(:STATUS_TALAO = 2, A.SITUACAO < 3 and A.DATA_PRODUCAO is NULL, A.SITUACAO = 3 and A.DATA_PRODUCAO is NULL), true)
        and iif(:DATA1 is distinct from null and :DATA2 is distinct from null, R.DATA BETWEEN :DATA1 AND :DATA2, true)
        and iif(char_length(:FAMILIAS) > 1, M.FAMILIA_CODIGO in (select osplit from split(:FAMILIAS,',')),true)
        
    into
        REMESSA,
        REMESSA_ID
    do begin
        suspend;
    end

end
^

SET TERM ; ^

