Montagem do script de criação de tabela
Para a criação de novas colunas pede-se que seja utilizado o template abaixo:
Recomendamos que o arquivo seja salvo com o nome da tabela customizada. Caso seja utilizada a ferramenta gera-installBD o arquivo deve ser salvo na pasta TABELAS do projeto.
Montagem do script de criação de coluna
As colunas serão criadas à necessidade de criação de atributos em tabelas.
Para criação de novas colunas deve-se, obrigatoriamente, criar antes uma tabela customizada, não sendo permitido em nenhuma hipótese a criação de colunas nas tabelas do BD_STANDARD.
Exemplo: Para uma nova coluna com informações adicionais sobre o cadastro de um produto recomendamos criar uma tabela CSTM_PRODUTO baseando-se nas informações citadas para a criação das mesmas, em seguida adicionando às novas colunas.
Nos tópicos a seguir recomendamos como boas práticas para criação de novas colunas:
- Tipos de dados CHAR não deve ser utilizado para a criação de colunas em tabelas no banco de dados;
- Para tipo de dado numérico (inteiro ou fracionado) deve-se definir tamanho fixo do campo;
- Inserir comentário sobre o tipo de informação a ser populada na coluna;
- Na construção do script fazer validação da existencial da coluna/tabela no banco antes da criação para prevenir erro na execução do script;
- Evitar o uso de valor default;
Sugestão de nomenclatura para criação de novas colunas:
Nomenclatura
PREFIXO + UNDERLINE + NOME CONTEXTUAL DO ATRIBUTO
Exemplos:
COD_LOJA
COD_ITEM_TRANSACAO
COD_CAMPANHA
Para a criação de novas colunas pede-se que seja utilizado o template abaixo:
Segue a tabela com sugestões de nomenclatura para o prefixo das colunas e seu respectivo tipo de dados:
Montagem do script de criação de índice
Os índices são criados para melhoria de performance das consultas aos dados, pois realiza um processo para percorrer a estrutura em árvore para extrair apenas os registros necessários para atender a cláusula da query. Caso contrário, o SGBD lê toda a tabela para encontrar os dados desejados.
É necessário que se avalie a necessidade de criação do mesmo, pois a ocupação de espaço em disco que a criação do índice causa é alta.
Orientamos que seja evitado a criação de índices com função e índices em tabelas pequenas por não trazerem benefícios.
Características para recomendação de índices:
1 - Tabela com um grande volume de dados.
2 - Coluna que não sofrem modificações com frequências.
2- Colunas NOT NULL e que possuam dados exclusivos.
Não é permitido a criação de índices nas tabelas do BD_STANDARD.
Nomenclatura para criação de índices customizados
IX + SEQUENCIAL + NOME DA TABELA CUSTOMIZADA (separados por underline).
Onde:
Sequencial: Número sequencial do índice da tabela. Utilizar um algarismo como padrão, podendo aumentar gradativamente de acordo com a necessidade.
Nome da tabela customizada: Tabela que vai receber o índice.
Exemplos:
IX_1_HUB_CSTM_CASHBACK
IX_1_CSTM_CASHBACK
IX_2_CSTM_CASHBACK
Para a criação de novos índices orientamos que seja utilizado o template abaixo:
Teste de execução dos scripts
Para realizar os devidos testes caso utilize o padrão de geração do gera-installBD é necessário acrescentar um cabeçalho e rodapé, mas este cabeçalho e rodapé é incluso pela ferramenta gera-installBD durante o processo de geração dos scripts.
--PADRAO DE CRIAÇÃO DE SCRIPT. SET TIMING ON SET SERVEROUTPUT ON SET ECHO OFF SPOOL C:\SCRIPTS\HUB_CSTM_CASHBACK.log DECLARE NAUX NUMBER; STMT VARCHAR2(4000); BEGIN /* Script a ser executado */ COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Erro no Comando:' || SUBSTR(stmt,1,230)); dbms_output.put_line(TO_CHAR(SQLCODE)); If SQLCODE <> 0 And SQLCODE <> -20001 Then RAISE; END IF; END; / SPOOL OFF;
Conectar em uma base de desenvolvimento e conforme orientação acima, aconselhamos que seja certificado que o script pode ser executada pelo menos 2 vezes sem apresentar erro.
2.1. Na primeira execução é exibida a mensagem de criação da tabela, índices e constraints.
2.2. Na segunda execução apenas o feedback do banco de dados deve ser exibido.
Tabelas de cadastro
Para tabela de cadastro recomendamos que seja trabalhado com tabelas externas e internas: A tabela externa para ser postado os dados pelo cliente/ERP e a tabela interna para receber os dados a ser trabalhado pela aplicação customizada.
Na tabela externa, responsável por receber os dados postados pelo cliente, orientamos ter colunas para registrar: A quantidade de execução do registro realizado no dia, o status do registro, a data e horário da inclusão do registro, a data e horário do último processamento, o tipo de operação e a mensagem do erro, se ocorrer, conforme o quadro 1. E propomos utilizar o prefixo HUB_CSTM como identificação que se trata de uma tabela externa customizada.
Quadro 1 - Colunas de controle para importação.
Coluna | tipo de dado | Aceita nulo? | Valor default | Descrição |
---|---|---|---|---|
CHAVE_EXECUCAO | VARCHAR2(50) | Y | ----- | Quantidade de execucao no dia: Padrao YYYYMMDD-0000N, onde N representa a quantidade de execução da importacao. |
STATUS_REGISTRO | VARCHAR2(2) | Y | NP | Status do processamento do registro: PR (Processado), NP (Não Processado), ER (Processado com Erro). |
DATA_INCLUSAO | DATE | Y | SYSDATE | Data que o registro foi inserido na tabela externa |
TIPO_OPERACAO | VARCHAR2(1) | Y | I | Se o registro irá realizar um processo de inserção, atualização ou exclusao |
DATA_PROC | DATE | Y | SYSDATE | Data do ultimo processamento para a tabela interna |
DESC_ERRO_REG | VARCHAR2(2000) | Y | ----- | Mensagem geral de processamento do registro na integracao |
Importação de cadastro
A recomendação é utilizar uma rotina de banco de dados para realizar o processo de importação, que geralmente utilizamos um objeto do tipo PACKAGE e que proporcione ser executado mais de uma vez. Como recomendação, pode ser utilizado o prefixo PKG_CSTM, acrescentando o sufixo IMP para destacar que se trata de uma rotina de importação, para mais informações sobre nomenclatura vide documento Guideline para Criação de Objetos Customizados. Utilizando uma procedure com prefixo IMPORTA podendo ter um cursor para leitura dos dados da tabela externa, que neste cursor sugerimos a utilização de parametrização, cujo código seja o parâmetro 922 contendo o valor NP ou EP para variável do cursor identificar se os registros a ser processados são da importação periódica ou automática. Nesta rotina sugerimos que seja contemplado com 2 tipos de importação: Uma importação automática ao qual é realizada no período da madrugada ou horário com menos movimentação para uma carga com um grande volume de dados e uma importação periódica para contemplar importação de uma pequena quantidade de registros que possa ser realizado em qualquer horário. Para distinguir esses processos, pode ser utilizado o status dos registros "NP" para informar que o registro pode ser feito na importação periódica ou "EP" para importação automática, podendo ser assumido um valor default "NP" para coluna, da mesma forma a coluna referente a data e horário que pode ser assumido um valor default SYSDATE.
Nesta mesma coluna do status do registro, poderia ser utilizado para marcar com ER em caso de erro na tentativa de importação.
CREATE OR REPLACE PACKAGE pkg_cstm_imp_cstm_cashback IS PROCEDURE importa_cashback(...); END; / SHOW ERRORS; CREATE OR REPLACE PACKAGE BODY pkg_cstm_imp_cstm_cashback IS PROCEDURE importa_cashback(...) IS CURSOR c_cstm_cashback(status) is SELECT cod_cashback ,val_cashback ,tipo_operacao ,... FROM hub_cstm_cashback WHERE val_cashback <> 0 AND status_registro='NP' AND status_registro=status; v922 p2k_parametros.valor%type; BEGIN v922:= PCK_P2K_UTIL.FN_GET_P2K_PARAMETROS(922); FOR vc_cstm_cashback IN c_cstm_cashback(v922) LOOP BEGIN IF tipo_operacao = 'I' THEN INSERT INTO cstm_cashback(cod_cashback, val_cashback) VALUES(vc_cstm_cashback.cod_cashback,vc_cstm_cashback.val_cashback); UPDATE hub_cstm_cashback SET status_registro = 'PR' ,data_proc = SYSDATE WHERE cod_cashback = vc_cstm_cashback.cod_cashback; IF tipo_operacao = 'A' THEN UPDATE cstm_cashback SET val_cashback = vc_cstm_cashback.val_cashback WHERE cod_cashback = vc_cstm_cashback.cod_cashback; UPDATE hub_cstm_cashback SET status_registro = 'PR' ,data_proc = SYSDATE WHERE cod_cashback = vc_cstm_cashback.cod_cashback; IF tipo_operacao = 'E' THEN DELETE FROM cstm_cashback WHERE cod_cashback = vc_cstm_cashback.cod_cashback; UPDATE hub_cstm_cashback SET status_registro = 'PR' ,data_proc = SYSDATE WHERE cod_cashback = vc_cstm_cashback.cod_cashback; (...) ELSE UPDATE hub_cstm_cashback SET status_registro = 'ER' ,data_proc = SYSDATE ,desc_erro_reg = 'Tipo de operacao invalida' WHERE cod_cashback = vc_cstm_cashback.cod_cashback; END IF; EXCEPTION WHEN OTHERS THEN -- Em caso de erro (...) UPDATE hub_cstm_cashback SET status_registro = 'ER' ,data_proc = SYSDATE ,desc_erro_reg = SQLERRM WHERE cod_cashback = vc_cstm_cashback.cod_cashback; END; END LOOP; END; END; / SHOW ERRORS;
No tipo de operação, indica se o registro irá realizar um processo de inserção, atualização ou exclusão, a coluna com esta informação poderia assumir o valor default "I". Em caso de exclusão salienta-se haver uma preocupação em relação as dependências, pois pode ser assumido que haverá a exclusão dos registros dependentes ou marcar como erro e informar a dependência.
Orientamos que as colunas da tabela externa não sejam criadas como NOT NULL, apenas as que fazem parte da chave primária, mesmo que na tabela da aplicação customizada seja obrigatória, pois o cliente pode optar por preencher posteriormente, desta forma não seja um impedimento para a realização de uma carga inicial e se caso permanecer com o valor nulo ou indevido, a rotina realize os devidos tratamentos se for coerente. De igual forma, sugerimos não haver restrição entre as tabelas externas. No quadro 2 há um exemplo de uma tabela externa e no quadro 3 um exemplo de uma tabela interna. Aconselhamos que haja a possibilidade da importação durante o processo de carga do cliente sem causar problema de lock.
Durante o processo de importação salientamos que o problema em algum registro nunca interrompa todo o processo, apenas que seja marcado erro ocorrido e passe para o próximo.
Quadro 2 - Modelo de uma tabela externa: HUB_CSTM_CASHBACK.
Coluna | tipo de dado | Aceita nulo? | Valor default | Descrição |
---|---|---|---|---|
*COD_CASHBACK | NUMBER | N | ----- | Codigo da operacao que gerou o cashback na compra |
VAL_COMPRA | NUMBER | Y | ----- | Valor da compra |
VAL_CASHBACK | NUMBER | Y | ----- | Valor a ser creditado como cashback |
DATA_COMPRA | DATE | Y | ----- | Data da compra |
DATA_CASHBACK_CREDITADO | DATE | Y | ----- | Data que o cashback foi disponibilizado para o cliente utilizar |
IND_STATUS | NUMBER | Y | ----- | Status da operacao: (A) Em processamento, (C) Cancelado, (D) Disponibilizado |
TIPO_OPERACAO | VARCHAR2(1) | Y | I | Se o registro irá realizar um processo de inserção, atualização ou exclusao |
DATA_INCLUSAO | DATE | Y | SYSDATE | Data que o registro foi inserido na tabela externa |
DATA_PROC | DATE | Y | SYSDATE | Data do ultimo processamento para a tabela interna |
CHAVE_EXECUCAO | VARCHAR2(50) | Y | ----- | Quantidade de execucao no dia: Padrao YYYYMMDD-0000N, onde N representa a quantidade de execução da importacao. |
STATUS_REGISTRO | VARCHAR2(2) | Y | NP | Status do processamento do registro: PR (Processado), NP (Não Processado), ER (Processado com Erro). |
DESC_ERRO_REG | VARCHAR2(2000) | Y | ----- | Mensagem geral de processamento do registro na integracao |
Quadro 3 - Modelo de uma tabela interna: CSTM_CASHBACK.
Coluna | tipo de dado | Aceita nulo? | Valor default | Descrição |
---|---|---|---|---|
*COD_CASHBACK | NUMBER | N | ----- | Código da operação que gerou o cashback na compra |
VAL_COMPRA | NUMBER | N | ----- | Valor da compra |
VAL_CASHBACK | NUMBER | Y | ----- | Valor a ser creditado como cashback |
DATA_COMPRA | DATE | N | ----- | Data da compra |
DATA_CASHBACK_CREDITADO | DATE | N | ----- | Data que o cashback foi disponibilizado para o cliente utilizar |
IND_STATUS | NUMBER | Y | ----- | Status da operação: (A) Em processamento, (C) Cancelado, (D) Disponibilizado |
Conduzimos a verificação da utilização da tabela ENTRADA_LOTE no processo de geração de LOTE para que seja vista a necessidade da rotina contemplar esta etapa, se fazendo necessário a montagem em uma única linha para o PDV dentro de um padrão utilizado pela aplicação com as informações necessárias da tabela externa. Ressaltamos que o padrão a ser utilizado deve estar pré-acordado com a aplicação.
No processo para inserir a ENTRADA_LOTE é feito a concatenação das colunas de uma tabela customizada em uma única linha, separadas por um delimitador, que é interpretado pela aplicação SP. Deve-se validar o tipo de operação a ser gerado no lote (inserção, atualização ou exclusão) e registrar um número de lote para indicar a sequência da entrada. Os campos seguem uma ordem predefinida junto a aplicação para evitar erros durante o processamento do lote.
Exemplo de um registro de lote:
{CstmCash}{#$#}10{#$#}30{#$#}{CstmCash}
CREATE OR REPLACE PROCEDURE SP_CSTM_GERA_LOTE IS CURSOR c_cstm_cashback(status) is SELECT '{CstmCash}' AS CABECALHO ,'{#$#}'||cod_cashback||'{#$#}' AS cod_cashback ,'{#$#}'||val_cashback||'{#$#}' AS val_cashback ,'{CstmCash}' AS RODAPE ,tipo_operacao FROM hub_cstm_cashback WHERE val_cashback <> 0 AND status_registro='NP' AND status_registro=status; V_CSTM VARCHAR2(2000) ; BEGIN FOR vc_cstm_cashback IN c_cstm_cashback(status) LOOP -- Montagem da linha V_CSTM := vc_cstm_cashback.cab||vc_cstm_cashback.cod_cashback||vc_cstm_cashback.val_cashback||vc_cstm_cashback.rodape; INSERT INTO CSTM_ENTRADA_LOTE (COD_OPERACAO, DESC_CONTEUDO) VALUES (vc_cstm_cashback.tipo_operacao, V_CSTM); END LOOP; END; /
Tabelas de movimentação
Assim como sugerido para as tabelas de cadastro recomendamos que para as tabelas de movimentação o fluxo seja o mesmo, com tabelas internas e externas: Neste caso a tabela interna para postagem dos dados oriundos da aplicação e a tabela externa para disponibilizar os dados para o cliente/ERP.
Ao criar uma tabela interna customizada orientamos inserir algumas colunas de controle para registrar algumas informações importantes sobre os registros de movimentação:
- Chave da transação (São colunas que contém a informação da origem do registro de movimentação):
CODIGO_EMPRESA, NUMERO_LOJA, COD_COMPONENTE, DATA_TRANSACAO, NSU;
- Controle de integração (São colunas que registram o status da exportação do registro):
STATUS_INTEGRACAO, ERRO_INTEGRACAO;
Para a movimentação, atualmente é utilizada uma tabela interna de controle contendo a chave do registro a ser integrado, bem como colunas de controle de status para registro de processamento, data de inclusão do registro, data do processamento e mensagens referente ao status de sua integração.
Exemplo - Tabela interna customizada de movimentação:
Coluna | tipo de dado | Aceita nulo? | Valor default | Descrição |
---|---|---|---|---|
CODIGO_EMPRESA | CHAR(5) | N | ----- | Código da empresa |
NUM_LOJA | VARCHAR2(4) | N | ----- | Número da loja |
COD_COMPONENTE | VARCHAR2(5) | N | ----- | Numero do Componente (PDV) que realizou a transação. |
DATA_TRANSACAO | DATE | N | ----- | Data que a transação aconteceu. |
NUM_NSU | NUMBER(12) | N | ----- | Numero sequencial único da transação |
STATUS_INTEGRACAO | VARCHAR2(2) | Y | NP | (PR - Processado, NP - Não Processado, ER - Processado com Erro) |
ERRO_INTEGRACAO | VARCHAR2(2000) | Y | Mensagem de retorno do erro ao exportar o registro. |
Importação de registros de movimentação
A recomendação é utilizar uma rotina de banco de dados para realizar o processo de exportação. Assim como informado para tabelas de cadastro, sugerimos o uso de objetos do tipo PACKAGE e que proporcione a execução mais de uma vez. Para a nomenclatura sugerimos a leitura do documento Guideline para Criação de Objetos Customizados. Para exportar os registros das tabelas internas até as externas pode-se utilizar um processo semelhante ao indicado no caso de tabelas de cadastro com a utilização de um cursor para leitura dos dados da tabela interna com o status NP. Para uso de paralelismo na execução da exportação de registros sugerimos o uso do COD_PROCESSADOR. Esta coluna está presente na tabela ESTABELECIMENTO. No cadastro do estabelecimento o código do processador é informado para cada loja podendo agrupar assim um as lojas de acordo com o COD_PROCESSADOR para execução da rotina de exportação de forma paralela.
Durante o processo de exportação salientamos que em caso de problema no processamento de algum registro a rotina marque o mesmo como erro, mas não interrompa o restante do processo, seguindo para a exportação do próximo registro.
No modelo sugerido é utilizada uma package acionada a cada três minutos (podendo ser alterado) por um JOB para processamento dos registros com status NP. Esta package lê a tabela de controle para verificar quais registros serão exportados para a tabela externa. Qualquer erro indicado por alguma tabela durante a exportação dos registros deve ser gravado na tabela de controle.
Exemplo:
No fluxo abaixo temos uma tabela customizada de controle de movimentação e duas outras tabelas filhas, que podem ser criadas baseando-se em tabelas internas do projeto BD-STANDARD ou utilizar apenas as tabelas . Uma rotina customizada deverá buscar os registros nestas tabelas para serem exportadas a uma ou mais tabelas externas customizadas. Caso algum dos registros acuse erro na exportação o mesmo ficará gravado com o status 'ER' na tabela CSTM_TRANSACAO, caso a exportação do registro seja concluída com sucesso o status 'PR' deve ser gravado na tabela CSTM_TRANSACAO.
Não é permitido a criação de colunas nas tabelas internas do projeto BD-STANDARD.
Para a criação de tabela externa customizada de movimentação sugerimos o layout abaixo:
Campo | Tipo | Tamanho | Dec | Default | PK | FK | UK | Obrigatório | Descrição |
---|---|---|---|---|---|---|---|---|---|
CODIGO_LOJA | N | 05 | 1 | Sim | Código da loja | ||||
DATA_TRANSACAO | N | 08 | 2 | Sim | Data que a transação aconteceu (AAAAMMDD) | ||||
NUMERO_COMPONENTE | N | 05 | 3 | Sim | Número do Componente (PDV) que realizou a transação. | ||||
NSU_TRANSACAO | N | 06 | 4 | Sim | Número sequencial único da transação | ||||
COD_EMPRESA | A | 05 | Código da empresa | ||||||
Campos de Controle de Integração | |||||||||
TIPO_OPERACAO | A | 01 | Sim | Tipo de operação. | |||||
DATA_INCLUSAO | D | SYSDATE | Data/hora de inclusão do registro | ||||||
DATA_PROC | D | Data geral de processamento do registro na integração | |||||||
STATUS_REGISTRO | A | 02 | NP | Sim | Status geral de processamento do registro na integração | ||||
DESCR_ERRO_REG | A | 2000 | Mensagem geral de processamento do registro na integração | ||||||
CHAVE_EXECUCAO | A | 50 | Chave de execução da importação | ||||||
STATUS_PROC_X | A | 02 | NP | Status de processamento do registro pelo procedimento. | |||||
DATA_PROC_X | D | Data de processamento do registro pelo procedimento. São 10 colunas sequências onde o final do nome (X) varia de 1 a 10. | |||||||
MENSAGEM_X | A | 300 | Mensagem de processamento do registro pelo procedimento. |
No modelo acima constam colunas de controle nomeadas de STATUS_PROC_X, DATA_PROC_X e MENSAGEM_X. Estas colunas são disponibilizadas para controle de registros exportados para outros sistemas e geralmente são criadas com a numeração de 1 até 10.
Exemplo:
STATUS_PROC_1
STATUS_PROC_2
STATUS_PROC_3
O objetivo dessas colunas é o controle de status das informações acessadas das tabelas externas por sistemas externos.