Quando inserimos cadastros, documentos de marketing ou transações no SAP Business One, o banco de dados armazena os dados. Dessa forma é possível criar consultas no SAP B1 para extrair dados, elaborar relatórios ou automações.
Criar consultas #
Existem algumas maneiras de verificar os dados armazenados no banco de dados, o gerador de consultas é ferramenta que o SAP Business One possui para facilitar a criação de relatórios úteis para rotina dos usuários. A outra maneira é via console do SAP Hana Studio (SQL Management), mas essa forma é mais utilizada por consultores levando em consideração o nível de acesso ao banco.
Gerador de consultas #
Para utilizar o gerador de consultas e criar querys no SAP B1, o usuário deve observar o menu superior e selecionar o caminho Ferramentas – Consultas – Gerador de Consultas, conforme imagem abaixo.
Selecionando tabelas #
O usuário clica em Gerador de consultas e o sistema carrega a janela, conforme a imagem a seguir. Em seguida, o usuário preenche o campo destinado à tabela de busca e, ao selecionar TAB, o sistema carrega os campos (colunas) presentes na tabela.
Grids #
Antes de criar consultas, existem alguns parâmetros importantes que se compreendidos poderam facilitar o dia a dia em análises e relatórios, são eles:
No grid esquerdo
- No canto superior direito, o campo destinado ao preenchimento das tabelas que irão compor a consulta.
- O Botão X, remove a tabela selecionada e essa exclusão se estende ao ajuste dos joins.
- O Botão >>, carrega todos os campos para o campo selecionado à direita, por padrão o campo Selecionar vem selecionado.
Na parte central
- Nome, é o nome do campo no banco de dados.
- Descrição, é a descrição presente na tela do SAP.
No grid à direita
- No campo Selecionar (SELECT), o usuário escolhe os campos e o sistema apresenta essas informações ao executar o gerador.
- No campo De (FROM), a medida em que o usuário for selecionando as tabelas que farão parte da consulta o gerador vai criar a relação entre as tabelas inclusive ele define os Alias (apelido) de cada tabela. Atente-se à relação criada, porque em alguns casos, mesmo estabelecendo a relação, o sistema retorna resultados incorretos na consulta.
- No campo Onde (WHERE), o usuário define os filtros sempre que houver necessidade. Quanto a quantidade de condições não há limites desde que estejam coerentes.
- No campo Ordenar (ORDER BY), caso seja necessário ordenar o resultado da consulta por algum campo basta inserí-lo neste campo.
- No campo Agrupar por (GROUP BY), via de regra utilizou max, top, sum tem que agrupar. Neste campo devem estar todos os campos presentes na consulta, com exceção a soma, max ou top.
Observação: a ordem dos parâmetros anteriormente mencionados segue a estrutura do SAP HANA Studio/SQL, dessa maneira, ao criar uma consulta manualmente, atente-se a ordem dos parâmetros. Se por exemplo, você tentar inserir um group by antes do where a consulta deve retornar erro.
Condições #
- No botão Condições, o usuário seleciona a opção e o sistema exibe as informações.
- Condições, são opções de filtro que podemos utilizar.
- As variáveis funcionam como parâmetros para a emissão do relatório. Por exemplo, quando você deseja emitir o relatório de contas a receber de um cliente específico, a consulta cria uma condição em que o código do parceiro é igual à variável [%0]. Ao executar o relatório, o sistema exibe o parâmetro “código do parceiro” para que o usuário o preencha
Definições do banco de dados #
Tabelas: mães e filhas #
Uma característica importante é a identificação da tabela principal (mãe) e suas respectivas filhas. Um exemplo prático é o cadastro de parceiro de negócios, cuja tabela mãe é a OCRD e cujas filhas são CRD1, CRD2 e por aí vai. De acordo com o exemplo utilizado observe que as tabelas principais têm como prefixo o “O”, enquanto as filhas perdem esse prefixo e recebem um número como sufixo.
Relação entre tabelas #
No banco de dados, a coluna primary key estabelece a relação entre as tabelas mãe e filhas.
- OCRD (Cadastro de parceiros de negócios) – CardCode (Primary Key)
- OPCH (Nota fiscal de entrada) – DocEntry (Primary Key)
- OINV (Nota fiscal de saída)- DocEntry (Primary key)
Para encontrar mais informações relativas à relacionamento de tabelas, visito o site Biuan.
Utilizando o gerador de consultas, criei uma consulta.
Na imagem acima, selecionamos as tabelas OINV (Mãe) e INV1 (filha). No campo De, é possível identificar que o gerador de consultas relacionou (fez o join) entre as tabelas, para utilizou o DocEntry (Primary Key).
Alias (Apelido) #
Outra dúvida recorrente envolve o Alias (apelido) utilizado para relacionar as tabelas. O próprio gerador estabelece um padrão: quando o usuário seleciona a primeira tabela, o sistema define automaticamente o alias como T0. À medida que o usuário insere novas tabelas, o sistema atribui novos apelidos.
O usuário define o alias a ser utilizado. Desse modo, o cuidado que devemos ter é, não definir nenhuma palavra que seja uma função, pois isso pode gerar erros ou ainda realizar um procedimento no banco.
O alias é necessário porque um relatório ou consulta utilizam de informações de diferentes tabelas e quando juntamos essas informações alguns campos, no banco de dados, possuem nomes similares. Por exemplo, imagine um cenário em que você deseja elaborar um relatório em que retorno dentre várias informações da nota fiscal de saída o nome fantasia do cliente. Ao criar essa consulta, você vai verificar que na OINV não existe o nome fantasia do cliente, dessa forma, será necessário criar um join para poder buscar informações do cadastro de parceiros de negócios.
Exemplo #
Select T0.”CardCode”, T0.”DocTotal”, T1.”AliasName” from OINV T0
INNER JOIN OCRD T1 on T1.”CardCode” = T0.”CardCode”
Descrevendo o exemplo acima, criamos uma relação entre a OINV e OCRD onde o elo entre as duas tabelas é o “CardCode”. Observe o seguinte trecho T0.”CardCode”, T0.”DocTotal”, T1.”AliasName”, inserimos os apelidos conforme definimos. Se por ventura, decidissémos não inserir o alias do “CardCode” um erro seria retornado, esse erro se deve ao fato de possuirmos CardCode tanto na OINV quanto na OCRD. O que ocorre na prática é que o banco não entende de onde ele deve buscar o código do parceiro.
Além disso, uma vez realizada a junção entre as tabelas você pode buscar qualquer informação da tabela. Imagine o cenário que é necessário inserir o e-mail presente na aba geral do cadastro.
Select T0.”CardCode”, T0.”DocTotal”, T1.”AliasName”, T1.”E_Mail” from OINV T0
INNER JOIN OCRD T1 on T1.”CardCode” = T0.”CardCode”
Note que simplesmente inseri o campo com seu respectivo apelido como a informação está na tabela OCRD, utilizei o alias T1.”nome do campo”.
JOINS #
Para facilitar a compreensão dos joins, imagine o cenário em que foram emitidas 30 notas e durante a rotina financeira alguns desses documentos foram recebidos. Agora o usuário precisa emitir um relatório financeiro que retorne o número da nota e o valor dos recebimentos.
INNER JOIN #
O INNER JOIN é uma forma de unir informações entre diferentes tabelas, sua característica é retornar a interseção entre os dados.
Digamos que dos 30 documentos emitidos apenas 10 possuem valor baixado no financeiro, dessa forma, se utilizarmos o INNER JOIN o resultado da consulta será:
| Número da Nota | Valor Recebido |
|---|---|
| 1 | 10,00 |
| 2 | 15,00 |
| 3 | 20,00 |
| 4 | 23,00 |
| 5 | 22,00 |
| 6 | 30,00 |
| 7 | 35,00 |
| 8 | 32,00 |
| 9 | 100,00 |
| 10 | 200,00 |
LEFT JOIN #
O LEFT JOIN tem como característica retornar os dados à esquerda das tabelas. No exemplo utilizado anteriormente, o resultado utilizando o LEFT JOIN será.
| Número da nota | Valor recebido |
|---|---|
| 1 | 10,00 |
| 2 | 15,00 |
| 3 | 20,00 |
| 4 | 23,00 |
| 5 | 22,00 |
| 6 | 30,00 |
| 7 | 35,00 |
| 8 | 32,00 |
| 9 | 100,00 |
| 10 | 200,00 |
| 20+ | null (vazio) |
Observe que o resultado da consulta é igual a quantidade total de notas, e a coluna relativa a tabela do financeiro para notas que não possuem baixas retorna como null (vazio).
RIGHT JOIN #
O RIGHT JOIN tem como caraterística retornar dados à direita da junção. Para prosseguirmos utilizando o cenário, precisamos incrementar mais um detalhe. Imagine que na tabela de contas a receber temos 15 registros, dessa maneira, o resultado da consulta será.
| Número da nota | Valor recebido |
|---|---|
| 1 | 10,00 |
| 2 | 15,00 |
| 3 | 20,00 |
| 4 | 23,00 |
| 5 | 22,00 |
| 6 | 30,00 |
| 7 | 35,00 |
| 8 | 32,00 |
| 9 | 100,00 |
| 10 | 200,00 |
| null | 250,00 |
| null | 232,00 |
| null | 1000,00 |
| null | 12,00 |
| null | 13,00 |
No resultado, observe que agora possuímos 15 linhas que se refere a quantidade total de registros presentes na tabela de contas a receber. Além disso, note que as notas que possuem valor baixado no contas a receber retornaram número de nota, as outras 5 linhas retornaram null (vazio) isso pode ser explicado pelo fato de que os valores são oriúndos de outros documentos diferentes da Nota fiscal como por exemplo, adiantamentos.
Querys #
Criar a primeira query no SAP B1 #
Para buscarmos todas as informações de uma determinada tabela por meio do gerador de consultas, tomando como exemplo a tabela OCRD – Parceiros de Negócios basta inserir a tabela no campo destinado e selecionar o Botão Executar.
Observe todos os dados da tabela (Principal) de parceiros de negócios são retornados.
Busca de colunas específicas #
Para retornar campos específicos o usuário deve procurar na relação de campos e clicar duas vezes com esquerdo do mouse que o campo seja copiado para o campo de Selecionar.
Em um base de homologação ou demonstração essa query roda muito bem, mas em determinados ambientes essa consulta pode não ter a mesma performace o que vai determinar isso é o volume de dados que a consulta deve retornar
Para compreender melhor a criação de consultas, gravei um vídeo onde demonstro como utilizar o gerador de consulta, além de gerar algumas querys.
Para mais dicas se inscreva no Canal do Youtube do Vale Mentoria disponível em: http://www.youtube.com/@deivisonvale3446
Para mais conteúdos acesse: Conteúdo