0 Replies Latest reply on May 9, 2019 5:20 AM by davesuresh

    Email Trigger from the ORACLE Database

    davesuresh

      Dear All,

       

      I have created a Email from Oracle Database and it works well with the sample small data. But when i execute the same with the real time data which is having multiple line item, the email content is showing as below screenshot. The actual data is more the 6 rows.

       

       

      Below are the procedures:

       

      CREATE OR REPLACE PROCEDURE LIVESTG.STG_PENDING_DN_INV_MAIL AS

       

       

        --m_sms_text        clob :='';

        m_sms_text        varchar2(32717) :='';

          m_cnt number(1) := 0;

          cursor c0

          is

              select count(*) FROM STG_OV_PEND_DN_TO_INV

              WHERE PENDING_QTY>0

              AND TO_CHAR(INVOICE_DATE,'YYYY') NOT IN ('2019')

              and COMPANY_CODE = 'OSC';

       

       

          cursor c1

          is

              SELECT COMPANY_CODE, CUST_NAME, SALESMAN, COORDINATOR, TRAN_CODE||'-'||DOC_NO DN_NO, INVOICE_DATE, ITEM_CODE, ITEM_NAME,

              BRAND, GR2,  PENDING_QTY fROM STG_OV_PEND_DN_TO_INV

              WHERE PENDING_QTY>0

              AND TO_CHAR(INVOICE_DATE,'YYYY') IN ('2019')

              AND COMPANY_CODE = 'OSC';

        

      begin

           --O_DGEN_PEND_SO_QGMD;

          if c0%isopen

          then

          close c0;

          end if;

       

       

          open c0;

          fetch c0 into m_cnt;

       

       

      --   if nvl(m_cnt,0) > 0 then

       

       

          --m_sms_text := '; ' || '<font color=''red''> ' ;

          m_sms_text := m_sms_text ||  '<h4>Dear All,</h4>';

          m_sms_text := m_sms_text ||  '<h4>Please Find below table for Pending DN to INV.</h4>';

          m_sms_text := m_sms_text || '<table border=' || '"' || '2' || '"' || '>';

          m_sms_text := m_sms_text || '<tr>';

          m_sms_text := m_sms_text || '<font color=''RED''> ' ;

          m_sms_text := m_sms_text || '<th>COMPANY CODE</th>';

          m_sms_text := m_sms_text || '<th>CUSTOMER</th>';

          m_sms_text := m_sms_text || '<th>SALESMAN</th>';

          m_sms_text := m_sms_text || '<th>COORDINATOR</th>';

          m_sms_text := m_sms_text || '<th>DELIVERY NOTE</th>';

          m_sms_text := m_sms_text || '<th>DELIVERY NOTE DATE</th>';

          m_sms_text := m_sms_text || '<th>ITEM CODE</th>';

          m_sms_text := m_sms_text || '<th>ITEM NAME</th>';

          m_sms_text := m_sms_text || '<th>GRADE 1</th>';

          m_sms_text := m_sms_text || '<th>GRADE 2</th>';

          m_sms_text := m_sms_text || '<th>PENDING QTY</th>';

          m_sms_text := m_sms_text || '</tr>';

       

       

          for c1_rec in c1

          loop

          m_sms_text:= m_sms_text|| '<tr>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.COMPANY_CODE),12,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.CUST_NAME),200,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.SALESMAN),50,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.COORDINATOR),50,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.DN_NO),20,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.INVOICE_DATE),12,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.ITEM_CODE),20,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.ITEM_NAME),200,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.BRAND),50,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td>'||rpad(rtrim(c1_rec.GR2),10,' ')||'</td>';

          m_sms_text:= m_sms_text||'<td align="right">'||to_char(c1_rec.PENDING_QTY,'999,999,999.99')||'</td>';

          --m_sms_text:= m_sms_text||'<td align='||'"'||'">'||c1_rec.pso_bal_qty_bu||'</td>';

          m_sms_text:= m_sms_text|| '</tr>';

          end loop;

          m_sms_text := m_sms_text || '</table>';

          m_sms_text := m_sms_text ||  '<h4>Regards,</h4>';

          m_sms_text := m_sms_text ||  '<h4>STG - IT</h4>';

                     

          OD_HTML_MAIL_STG (p_id   => 1,

                          p_subject   => 'Pending Delivery Note for STC',

                          p_text_msg  => null,

                          p_html_msg  => m_sms_text);

      --    end if;

          end;

       

      OD_HTML_MAIL_STG

       

      CREATE OR REPLACE PROCEDURE LIVESTG.OD_HTML_MAIL_STG (p_id   IN NUMBER,

                                             p_subject   IN VARCHAR2,

                                             p_text_msg  IN VARCHAR2 DEFAULT NULL,

                                             p_html_msg  IN VARCHAR2 DEFAULT NULL

                                             )

      AS

        l_mail_conn   UTL_SMTP.connection;

        l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';

       

        M_from  VARCHAR2(50) := 'abc@abc.ae';

        M_smtp_host VARCHAR2(50) := 'mailv.emirates.net.ae';

        M_smtp_port NUMBER := 25 ;

      M_TO VARCHAR2(50);

      M_TO_LIST VARCHAR2(1000) := '';

      M_CC_LIST VARCHAR2(1000) := '';

       

       

       

       

       

       

      Cursor C1(M_TYPE VARCHAR) is

      select MI_MAIL_ID

      from OM_MAIL_INDEX_STG

      where MI_MAIL_TYPE = M_TYPE

      AND MI_SYS_ID = P_ID;

       

       

      Cursor C2 is

      select MI_MAIL_ID

      from OM_MAIL_INDEX_STG

      where MI_SYS_ID = P_ID;

       

      BEGIN

        IF C1%ISOPEN THEN CLOSE C1; END IF;

        FOR I IN C1('T') LOOP

        M_TO_LIST:=M_TO_LIST||I.MI_MAIL_ID||',';

        END LOOP;

        M_TO_LIST:=substr(M_TO_LIST,1,length(M_TO_LIST)-1);

       

        IF C1%ISOPEN THEN CLOSE C1; END IF;

        FOR J IN C1('C') LOOP

        M_CC_LIST:=M_CC_LIST||J.MI_MAIL_ID||',';

        END LOOP;

        M_CC_LIST:=substr(M_CC_LIST,1,length(M_CC_LIST)-1);

       

        l_mail_conn := UTL_SMTP.open_connection(M_smtp_host, M_smtp_port);

        UTL_SMTP.helo(l_mail_conn, M_smtp_host);

       

        IF C2%ISOPEN THEN CLOSE C2; END IF;

       

        FOR K IN C2 LOOP

       

        UTL_SMTP.mail(l_mail_conn, M_from);

        UTL_SMTP.rcpt(l_mail_conn, K.MI_MAIL_ID);

       

       

        UTL_SMTP.open_data(l_mail_conn);

       

        --UTL_SMTP.write_data(l_mail_conn, 'Date: ' || SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSTIMESTAMP, 'Dy, DD Mon YYYY HH24:MI:SS TZHTZM','NLS_DATE_LANGUAGE=ENGLISH') || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'To: ' || M_TO_LIST || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'Cc: ' || M_CC_LIST || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'From: ' || M_from || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || M_from || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

        UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

       

       

       

       

        IF p_html_msg IS NOT NULL THEN

          UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

          UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

       

       

          UTL_SMTP.write_data(l_mail_conn, p_html_msg);

          UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

        END IF;

       

       

        UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);

        UTL_SMTP.close_data(l_mail_conn);

        END LOOP;

        UTL_SMTP.quit(l_mail_conn);

      END;

      /