Group By function

hope you all having a great day. I need some help with this query. Just to explain the situation, I have to send e-mails containing the data found in this select, but I have to group by customers (TBLOTE.CEMPRES) so it would build a single e-mail for each customer found. Example, if the query finds 3+ row for customer ´John Inc.´ and 1 row for ´Jack Corp.´, it should send 2 e-mails, one containing the 3+ rows about John Inc, and the other one containing the 1 row found for Jack Corp. I hope I could be clear enough on my explanation.

create or replace PROCEDURE PR_ENVIA_EMAIL_LOTE_ESTOQUE IS


V_REMETENTE     VARCHAR2(50) := 'servidor@oracle.com.br';
V_SAUDACAO      VARCHAR2(50);
V_ASSUNTO       VARCHAR2(200);
V_ASSINATURA    VARCHAR2(100);
V_MENSAGEM      VARCHAR2(6000);
V_COD_ERRO      NUMBER(2);
V_MSG_ERRO      VARCHAR2(1);
TDESCRI_ARMAZEM VARCHAR2(50);
TDESCRI         VARCHAR2(60);

BEGIN 

    FOR C1 IN (SELECT TBPJUR.TDESCRI  EMPRESA,
                      TBLOTE.CEMPRES  COD_EMPRESA,
                      TBLOTE.NNOTFIS  NOTA_FISCAL, 
                      TBLOTE.CLOTE    LOTE, 
                      TBARMAZ.TDESCRI ARMAZEM,  
                      TBNOTF.DDTFATU  DATA_FAT_NF,  
                      TBLOTE.DRECEBTO DATA_RECEBTO, 
                      TBLOTE.CPRODUT  COD_PRODUTO, 
                      TBPRODU.TDESCRI PRODUTO,
                      TBLOTAR.QQTESTQ QTD_ESTOQUE,
                      Decode(TBLOTE.F_ARMAZENAGEM, 'S', 'SIM', 'N', 'NÃO') ARMAZENAGEM,
                      Decode(TBLOTE.SLTENCR, 'S', 'SIM', 'N', 'NÃO')  ENCERRADO,
                      TO_DATE(Sysdate, 'dd/mm/yyyy') - TO_DATE(TBLOTE.DRECEBTO, 'dd/mm/yyyy')DIAS_EM_ESTOQUE
                 FROM TBLOTE,
                      TBLOTAR,
                      TBPRODU,
                      TBARMAZ,
                      TBNOTF
                WHERE TBLOTE.CPRODUT       = TBPRODU.CPRODUT
                  AND TBLOTE.CLOTE         = TBLOTAR.CLOTE
                  AND TBLOTAR.CARMAZE      = TBARMAZ.CARMAZE
                  AND TBLOTE.NNOTFIS       = TBNOTF.NNOTFIS
                  AND TBLOTE.CEMPRES       = TBNOTF.CEMPRES
                  AND TBLOTE.CEMPRES       = TBPJUR.CEMPRES
                  AND TBLOTE.SLTENCR       = 'N'
                  AND TBLOTE.F_ARMAZENAGEM = 'S'
                  AND TBLOTE.NNOTFIS IS NOT NULL 
                  AND TO_DATE(Sysdate, 'dd/mm/yyyy') - TO_DATE(TBLOTE.DRECEBTO, 'dd/mm/yyyy') >= 165
                  ORDER BY DIAS_EM_ESTOQUE DESC)
       LOOP
    DBMS_OUTPUT.PUT_LINE('LENDO '||C1.NOTA_FISCAL|| ' - ' ||C1.LOTE|| ' - ' ||C1.ARMAZEM|| ' - ' ||C1.PRODUTO|| ' - Dias em Estoque: ' || C1.DIAS_EM_ESTOQUE);

   BEGIN
      IF TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) < 12 THEN
         V_SAUDACAO:= 'Bom Dia!';
      ELSIF TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) >= 12 AND
            TO_NUMBER(TO_CHAR(SYSDATE,'HH24'))    <  18 THEN
            V_SAUDACAO:= 'Boa tarde!';
      ELSE
         V_SAUDACAO:= 'Boa noite!';
      END IF;
        
        
        V_ASSUNTO := 'AVISO: Lotes em estoque.';

        V_MENSAGEM := ' Os seguintes lotes ja estao há pelo menos 165 dias em nosso estoque, favor verificar: '||CHR(10)||CHR(10);

      V_MENSAGEM := V_MENSAGEM ||'Nota Fiscal : '||C1.NOTA_FISCAL||CHR(10)||
                                 'Lote : '||C1.LOTE||CHR(10)||
                                 'Armazem : '||C1.ARMAZEM||CHR(10)||
                                 'Dt. Faturamento : '||To_Date(C1.DATA_FAT_NF, 'DD/MM/YYYY')||CHR(10)||
                                 'Dt. Recebimento : '||C1.DATA_RECEBTO||CHR(10)||
                                 'Produto : '||C1.PRODUTO||CHR(10)||CHR(10)|| 
                                 'Qtd. em Estoque : '||C1.QTD_ESTOQUE||CHR(10)||
                                 'Dias em Estoque : '||C1.DIAS_EM_ESTOQUE||CHR(10)||CHR(10)||
                                 'Estamos a disposiçao.'||CHR(10)||CHR(10)||CHR(10)||
                                 'Att. '||CHR(10)||CHR(10)||
                                 'Departamento de T.I'||CHR(10)||
                                 '---';

      V_MENSAGEM := V_SAUDACAO||CHR(10)||CHR(10)||
                    V_MENSAGEM||
                    CHR(10)||CHR(10)||CHR(10)||CHR(10)||
                    V_ASSINATURA;
                                                    

       FOR C2 IN (SELECT EMAIL_USUARIO
                        FROM   TBEMAIL_INTERNO
                        WHERE  F_LOTE_ESTOQUE = 'S'
                        AND    F_EXCLUSAO_LOGICA   = 'N')
              LOOP
              
            
                PR_ENVIA_EMAIL(V_REMETENTE,
                               C2.EMAIL_USUARIO,
                               V_ASSUNTO,
                               V_MENSAGEM,
                               V_COD_ERRO,
                               V_MSG_ERRO);
                                                            
       END LOOP;                                   

    END;
 
 END LOOP ;           
    

      EXCEPTION
         WHEN OTHERS THEN
            dbms_output.put_line('ATENÇÃO: ERRO AO CHAMAR ROTINA PR_ENVIA_EMAIL_LOTE_ESTOQUE: '||SQLERRM);


END PR_ENVIA_EMAIL_LOTE_ESTOQUE;

Answer

Currently you have 2 loops. The outer loop (cursor C1) loops through all records and the inner loop (cursor c2) loops through the email addresses and sends an email for every iteration. This can be written in pseudo code as:

LOOP through all records
  LOOP through email addresses and send mail

To satisfy your requirement, add a 3rd loop:

LOOP through distinct customers
  LOOP through all records for current customer
    LOOP through email addresses and send email

Advertisement

Answer