SET TERM ^ ;

CREATE OR ALTER procedure SPC_USUARIOS_RELATORIO_WEB (
    SERVIDOR varchar(50),
    BANCO varchar(50),
    USUARIO_DB varchar(50),
    SENHA_DB varchar(50),
    RELATORIO_ID integer,
    QTD integer = 0,
    PAGINA integer = 0,
    IS_ITENS_ADD integer = 0)
returns (
    ID integer,
    DESC_ID varchar(10),
    USUARIO varchar(50),
    DESC_USUARIO varchar(50),
    SETOR varchar(50),
    CARGO varchar(50),
    STATUS char(1))
as
declare variable STR_CONSULTA varchar(300);
declare variable STR_ITENS_ADD varchar(300);
BEGIN
  :STR_CONSULTA = '';
  :STR_ITENS_ADD = '';

  IF (:QTD > 0 AND :PAGINA >= 0) THEN
  BEGIN
    :STR_CONSULTA = ' FIRST ' || :QTD || ' SKIP ' || :PAGINA || '';
  END

  IF (:IS_ITENS_ADD = 1) THEN
  BEGIN
    :STR_ITENS_ADD = ' AND R.ID IS NULL';
  END

  FOR EXECUTE STATEMENT 'SELECT' || :STR_CONSULTA || '
                            U.CODIGO AS ID,
                            FN_LPAD(U.CODIGO, 4, ''0'') AS DESC_ID,
                            U.USUARIO, 
                            IIF( CHAR_LENGTH(COALESCE(U.NOME,'''')) > 0, U.NOME, U.USUARIO) AS DESC_USUARIO,
                            COALESCE(U.SETOR,'''') AS SETOR,
                            COALESCE(U.CARGO,'''') AS CARGO,
                            U.STATUS
                        FROM TBUSUARIO U LEFT JOIN TBRELATORIO_WEB_USUARIO R ON R.RELATORIO_ID = '||:RELATORIO_ID||' AND U.CODIGO = R.USUARIO_ID
                        WHERE U.STATUS = ''1''' || :STR_ITENS_ADD
          ON EXTERNAL :SERVIDOR || '/3050:' || :BANCO
          WITH AUTONOMOUS TRANSACTION
          AS USER :USUARIO_DB PASSWORD :SENHA_DB
          INTO :ID, :DESC_ID, :USUARIO, :DESC_USUARIO, :SETOR, :CARGO, :STATUS
  DO
  BEGIN
    SUSPEND;
  END
END
^

SET TERM ; ^