Copy of Oracle Material Structure

Navigation
2

We have the following material registration structure:

Table 

Pk            FK            Filial  Descricao material

Group 

Cod_grupo               
 1             -                1   Materiais de escritório
 2             -                1   EPIS
 3             -                1   impressos

Subgroup 

Cod_Subgrupo         Cod_grupo      
        111             1               1   Acessórios para Computador
        24              2               1   Uniformes
        203             3               1   Jornais e revistas

Class 

   Cod_classe    Cod_Subgrupo       
   1028          111                1   Insumos 
   856           203                1   Jornais e revistas
   18            24                 1   Uniformes

Material 

Cod_material    Cod_classe      
 313131          1028              1    Etiqueta
 313132          1028              1    Fotocondutor
 313133          856               1    Livros
 313134          856               1    Jornais
 313135          18                1    Bota
 313136          18                1    Luva

Based on this structure, I need to replicate the same structure to branch 2. The problem is that Fks . How could I create a script that copies this structure by following the hierarchies of grupo --> sugrupo --> classe --Material ?

    
asked by anonymous 23.05.2017 / 18:50

1 answer

1

As I do not know the structure of your table, then I implemented it as per understanding of the business rule.

I do not know how it's done in your bank to sequence each PK, so I made a sequence, based on the largest table code: 

DECLARE
 I  NUMBER(11);
BEGIN
 SELECT MAX(g.cod_grupo)+1
   INTO I
   FROM grupo g;
 EXECUTE IMMEDIATE ('create sequence seq_grupo start with '||I);   
END;
/

So I created Insert , which is based on a query, which basically takes the next code, and makes a decode in the branch, where 1 is replaced by 2. 

INSERT INTO grupo
  SELECT seq_grupo.nextval AS cod_grupo
        ,DECODE(g.filial,1,2) AS filial
        ,g.descricao
    FROM grupo g;

You would have to create the above structure for each table.

I did an example using Oracle's livesql, since the sqlfiddle site was giving trouble link

    
23.05.2017 / 22:18
How to ungroup transactions or group by day? [closed] Test access method, whether it is GET or POST [duplicate]