Query Custo de Produtos - recursivo

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
Tharkun
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Ter, 26 Fev 2019 1:20 pm

Boa tarde meus amigos, meu conhecimento é básico e estou com dificuldade na criação da seguinte query:

Tenho uma tabela de cadastro de produtos (cerca de 5.000 itens) chamada TAB_PRODUTO, e nessa tabela tenho as seguintes colunas: COD_PRODUTO (código do produto) e TIPO_PRODUTO (C: comprado, P: produzido). A query precisa basicamente trazer essa tabela e inserir uma coluna adicional denominada CUSTO_PRODUTO, com o custo unitário de cada produto, calculado conforme segue:

Quando o produto é tipo C (comprado), o custo dele será buscado em uma tabela que possui o custo de todos os itens comprados, denominada TAB_CUSTO. Essa parte é tranquilo, essa tabela possui as seguintes colunas: COD_PRODUTO (código do produto) e VALOR_CUSTO_COMPRA.

Quando o item é tipo P (produzido), seu custo será a soma do custo dos produtos filhos, calculados a partir de uma tabela BOM (Bill of Materials) chamada TAB_BOM, e é nessa parte que enrosquei. Essa tabela possui as seguintes colunas: COD_PRODUTO_PAI, COD_PRODUTO_FILHO e QTD_NECESSARIA.

Se um produto da TAB_PRODUTO é um item produzido e é formado, por exemplo, por 10 componentes diferentes, na TAB_BOM haverá 10 registros referentes a esse item. Os 10 registros terão o mesmo COD_PRODUTO na coluna COD_PRODUTO_PAI, e cada qual terá um COD_PRODUTO_FILHO e QTD_NECESSARIA diferentes.

O custo de um produto produzido da TAB_PRODUTO então será a soma do custo de todos seus filhos. Quando um filho é um item comprado, basta eu multiplicar a QTD_NECESSARIA da TAB_BOM pelo VALOR_CUSTO_COMPRA da tabela TAB_CUSTO. Quando um filho é produzido, eu teria que multiplicar a quantidade dele pela soma do custo dos seus próprios filhos, ou seja, netos do primeiro produto em questão.

Nos exemplos que encontrei no fórum, vi o pessoal usando connect, porém a BOM dos exemplos geralmente possuía o nível hierárquico dos itens em questão, mas na minha tabela BOM não há indicação do nível, creio que pelo fato de um item ser usado como componente de diversos produtos.


Estava desenvolvendo algo nessa linha de raciocínio, mas creio estar errado:

Selecionar tudo

SELECT T1.COD_PRODUTO,
            CASE WHEN T1.TIPO_ITEM = 'C'
            THEN T2.VALOR_CUSTO_COMPRA
            ELSE SELECT SUM(CASE WHEN(T3.COD_PRODUTO_PAI = T1.COD_PRODUTO AND T1.TIPO_ITEM = 'C') 
                    THEN T3.QTD_NECESSARIA * T4.VALOR_CUSTO_COMPRA
                    ELSE T3.QTD_NECESSARIA * SELECT...??? (LOOPING PARA CADA VEZ QUE ELE ENCONTRA UM FILHO TIPO 'P')
                    FROM TAB_BOM T3
                    INNER JOIN TAB_CUSTO T4 ON (T4.COD_PRODUTO = T3.COD_PRODUTO_FILHO)
FROM TAB_PRODUTO T1
INNER JOIN TAB_CUSTO T2 ON (T2.COD_PRODUTO = T1.COD_PRODUTO)
Simplifiquei o nome das tabelas no exemplo pra facilitar o entendimento.

Se alguém puder ajudar, ficarei muito grato.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Tem várias formas de se fazer isso.
Vamos separar os 2 problemas pra gente entender melhor:

O select abaixo busca o custo dos itens COMPRADOS:
(por favor, confirme se é isso mesmo que você precisa)

Selecionar tudo

SELECT 
  C1.COD_PRODUTO
, C1.tipo_item
, C2.custo_produto
FROM 
  TAB_PRODUTO C1
, tab_custo   C2
where C1.cod_produto = C2.cod_produto 
  and C1.tipo='C'


Este select (no meu entendimento) busca o preço dos itens PRODUZIDOS.
(por favor, veja se eu estou errado).

Selecionar tudo

SELECT 
  P1.COD_PRODUTO
, P1.tipo_item
, sum(P2.qtd_necessaria * p3.custo_produto)
FROM 
  TAB_PRODUTO P1
, TAB_BOM     P2  
, tab_custo   P3
where P1.cod_produto = P2.cod_produto 
  and p2.cod_produto_filho = p3.cod_produto
  and P1.tipo='P'
group by   
  P1.COD_PRODUTO
, P1.tipo_item
Se ambos estão corretos, basta juntar os 2 select com um UNION ALL.
Se não é isso, por favor, manda pra nós alguns exemplos de dados da tabela CUSTO e BOM.
Tharkun
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Ter, 26 Fev 2019 1:20 pm

Boa tarde Thomas, grato pelo retorno.

Quanto aos itens comprados (Tipo C), ok!

Quanto aos itens produzidos (Tipo P), eu não consigo calcular seu custo apenas buscando seus filhos na TAB_BOM e multiplicando a quantidade deles pelo custo da TAB_CUSTO, porque entre esses filhos pode haver alguns que são produzidos, e para cada um desses teria que abrir uma instância do código principal para calcular seu custo e somar ao custo total do pai..

Seria algo tipo isso:

Selecionar tudo

PRODUTO 4876 (P)
└------------2 UN PRODUTO 1132 (C)
└------------1 UN PRODUTO 2133 (C)
└------------2 UN PRODUTO 4024 (P)
- - - - - - - - - - - └------------8 UN PRODUTO 2877 (C)
- - - - - - - - - - - └------------1 UN PRODUTO 5900 (P)
- - - - - - - - - - - - - - - - - - - - - - └------------4 UN PRODUTO 2133 (C)
- - - - - - - - - - - - - - - - - - - - - - └------------1 UN PRODUTO 1760 (C)
- - - - - - - - - - - - - - - - - - - - - - └------------6 UN PRODUTO 8450 (C)
└------------5 UN PRODUTO 7177 (C)
└------------4 UN PRODUTO 9438 (P)
- - - - - - - - - - - └------------3 UN PRODUTO 6502 (C)
- - - - - - - - - - - └------------3 UN PRODUTO 2877 (C)
No exemplo acima, o custo do produto 4876 (tipo P) da TAB_PRODUTO é a soma do custo dos filhos 1132, 2133, 4024, 7177 e 9438.
O custo dos filhos 1132, 2133 e 7177 está na TAB_CUSTO.
O custo do filho 4024 é a soma do custo dos seus próprios filhos, 2877 e 5900.
O custo do 5900 será a soma do 2133, 1760 e 8450, e por aí vai...
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Neste caso, vai ter que usar CONNECT BY, ou pior ainda, vai ter que fazer um programa chamando ele próprio (recursividade).
Baseado nessas informações que você mandou, a gente pode criar uma tabela com esses dados pra simular.
Vou tentar fazer isso mais tarde, dai eu dou um retorno.
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Aqui está a criação das tabelas de exemplo E os inserts...
Vamos trabalhar nesses dados. Eu coloquei preço de tudo sendo 1.

Essa é a estrutura criada nos inserts:

Selecionar tudo

PRODUTO 4876 (P)
└------------2 UN PRODUTO 1132 (C)
└------------1 UN PRODUTO 2133 (C)
└------------2 UN PRODUTO 4024 (P)
- - - - - - - - - - - └------------8 UN PRODUTO 2877 (C)
- - - - - - - - - - - └------------1 UN PRODUTO 5900 (P)
- - - - - - - - - - - - - - - - - - - - - - └------------4 UN PRODUTO 2133 (C)
- - - - - - - - - - - - - - - - - - - - - - └------------1 UN PRODUTO 1760 (C)
- - - - - - - - - - - - - - - - - - - - - - └------------6 UN PRODUTO 8450 (C)
└------------5 UN PRODUTO 7177 (C)
└------------4 UN PRODUTO 9438 (P)
- - - - - - - - - - - └------------3 UN PRODUTO 6502 (C)
- - - - - - - - - - - └------------3 UN PRODUTO 2877 (C

Create table / inserts:

Selecionar tudo

create table tab_produto (
  cod_produto number
, tipo        varchar2(1)
);

create table tab_custo (
  cod_produto number
, custo_produto number
);

create table tab_bom (
  cod_produto_pai number
, cod_produto_filho number
, qtd_necessaria number
); 

begin
  delete from tab_produto;
  delete from tab_custo;
  delete from tab_bom;
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (4876, 'P');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (1132, 'C');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (2133, 'C');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (4024, 'P');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (2877, 'C');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (5900, 'P');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (1760, 'C');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (8450, 'C');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (7177, 'C');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (9438, 'P');
  insert into TAB_PRODUTO (COD_PRODUTO, TIPO) values (6502, 'C');

  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (4876, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (1132, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (2133, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (4024, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (2877, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (5900, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (1760, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (8450, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (7177, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (9438, 1);
  insert into TAB_CUSTO (COD_PRODUTO, CUSTO_PRODUTO) values (6502, 1);

  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 1132, 2); 
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 2133, 1);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 4024, 2);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 7177, 5);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4876, 9438, 4);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4024, 2877, 8);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (4024, 5900, 1);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (5900, 2133, 4);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (5900, 1760, 1);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (5900, 8450, 6);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (9438, 6502, 3);
  insert into TAB_BOM (COD_PRODUTO_PAI, COD_PRODUTO_FILHO, QTD_NECESSARIA) values (9438, 2877, 3);
  commit;
end;
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

E aqui a solução do seu problema.
Consegui fazer utilizando CONNECT BY: (Executar os create table acima / inserts. Tive que edita-los pois estavam errados)

Selecionar tudo

select rec.*, c.custo_produto, p.tipo
from ( 
      select 
        b.cod_produto_pai
      , b.cod_produto_filho
      , b.qtd_necessaria
      , level nivel
      , rownum ordem 
      , SYS_CONNECT_BY_PATH(cod_produto_filho, '/') TREE
      from  tab_bom     b
      start with b.cod_produto_pai = 4876
      CONNECT BY prior b.cod_produto_filho = b.cod_produto_pai
     ) rec
,    tab_custo c
,    tab_produto p
where rec.cod_produto_filho = c.cod_produto(+)
  and rec.cod_produto_filho = p.cod_produto(+)
order by rec.ordem
A saída dessa query é:

Selecionar tudo

SQL> /

COD_PRODUTO_PAI COD_PRODUTO_FILHO QTD_NECESSARIA NIVEL ORDEM TREE            CUSTO_PRODUTO TIPO
--------------- ----------------- -------------- ----- ----- --------------- ------------- ----
           4876              1132              2     1     1 /1132                       1 C
           4876              2133              1     1     2 /2133                       1 C
           4876              4024              2     1     3 /4024                       1 P
           4024              2877              8     2     4 /4024/2877                  1 C
           4024              5900              1     2     5 /4024/5900                  1 P
           5900              1760              1     3     6 /4024/5900/1760             1 C
           5900              2133              4     3     7 /4024/5900/2133             1 C
           5900              8450              6     3     8 /4024/5900/8450             1 C
           4876              7177              5     1     9 /7177                       1 C
           4876              9438              4     1    10 /9438                       1 P
           9438              2877              3     2    11 /9438/2877                  1 C
           9438              6502              3     2    12 /9438/6502                  1 C

12 rows selected

SQL> 
Aqui uma outra visão utilizando a mesma query: (Só mudando a clausula SELECT)

Selecionar tudo

select  rpad(' ',3*nivel,' ')||rec.cod_produto_filho||' ('||p.tipo||')' tree2
, rec.qtd_necessaria qtd
, c.custo_produto    preço
, rec.qtd_necessaria *  c.custo_produto TOTAL
from ( 
      select 
        b.cod_produto_pai
      , b.cod_produto_filho
      , b.qtd_necessaria
      , level nivel
      , rownum ordem 
      , SYS_CONNECT_BY_PATH(cod_produto_filho, '/') TREE
      from  tab_bom     b
      start with b.cod_produto_pai = 4876
      CONNECT BY prior b.cod_produto_filho = b.cod_produto_pai
     ) rec
,    tab_custo c
,    tab_produto p
where rec.cod_produto_filho = c.cod_produto(+)
  and rec.cod_produto_filho = p.cod_produto(+)
order by rec.ordem
Observe que TODOS itens produzidos (P) possuem filhos!

Selecionar tudo

TREE2                       QTD      preço      TOTAL
-------------------- ---------- ---------- ----------
   1132 (C)                   2          1          2
   2133 (C)                   1          1          1
   4024 (P)                   2          1          2
      2877 (C)                8          1          8
      5900 (P)                1          1          1
         1760 (C)             1          1          1
         2133 (C)             4          1          4
         8450 (C)             6          1          6
   7177 (C)                   5          1          5
   9438 (P)                   4          1          4
      2877 (C)                3          1          3
      6502 (C)                3          1          3

12 rows selected

SQL> 
:-o
Tharkun
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Ter, 26 Fev 2019 1:20 pm

Grato pelo retorno Thomas,

Executei aqui e montou a treeview corretamente, porém não sei por qual motivo todos os itens apareceram duplicados.

Para encaixar o custo obtido dessa subconsulta na query original, acredito que o código que você postou entrará com alguns ajustes no lugar do "CÓDIGO THOMAS":

Selecionar tudo

SELECT T1.COD_PRODUTO,
            CASE WHEN T1.TIPO_ITEM = 'C'
            THEN T2.VALOR_CUSTO_COMPRA
            ELSE
                "CÓDIGO THOMAS"
            END AS "CUSTO PRODUTO"
FROM TAB_PRODUTO T1
INNER JOIN TAB_CUSTO T2 ON (T2.COD_PRODUTO = T1.COD_PRODUTO)
Avatar do usuário
vitellozzi
Rank: Programador Sênior
Rank: Programador Sênior
Mensagens: 50
Registrado em: Qua, 15 Set 2010 9:53 am
Localização: Itatiba

Utilizo o LISTAGG no lugar do CONNECT BY.

A função LISTAGG do Oracle / PLSQL concatena valores de medida_coluna para cada GROUP com base na order_by_clause .

Sintaxe
A sintaxe para a função LISTAGG no Oracle / PLSQL é:

Selecionar tudo

LISTAGG (measure_column [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]
Parâmetros ou Argumentos
measure_column
A coluna ou expressão cujos valores você deseja concatenar juntos no conjunto de resultados. Valores nulos no
measure_column são ignorados.

delimiter
Opcional. É o delimitador a ser usado ao separar os valores de measure_column ao gerar os resultados.

order_by_clause
Ele determina a ordem em que os valores concatenados (isto é: measure_column ) são retornados.

Retorna
A função LISTAGG retorna um valor de string.

Aplica-se a função LISTAGG pode ser usada nas seguintes versões do Oracle / PLSQL:
Oracle 12c, Oracle 11g Release 2
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Sim, o LISTAGG serve pra colocar várias linhas na mesma coluna, por exemplo, separado por vírgula.
Mas neste caso, a gente tem uma tabela com recursividade. (Campo PAI e FILHO).
É uma mão na roda. Antigamente a gente tinha que fazer uma procedure PLSQL chamando ela própria, pra resolver esse problema.
Tharkun
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 4
Registrado em: Ter, 26 Fev 2019 1:20 pm

Boa tarde meus amigos, peço desculpas pela demora no retorno... fechamento e entrega do Bloco K me tomaram mais de 30 dias ininterruptos, pensa numa pessoa que mal conseguiu tomar café :shock:

Estarei analisando as soluções apresentadas e retorno em breve, muito obrigado pelo retorno!
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 12 visitantes