0 Replies - 4260 Views - Last Post: 14 June 2012 - 12:17 PM

#1 trulysick  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 03-August 11

Sending emails on certain date over smtp server

Posted 14 June 2012 - 12:17 PM

Hey there,
Using APEX I have a date picker that inserts this date into a column of a table called send_date.
Then I have a job that runs through and finds rows where send_date = sysdate... Now I need this job to gather data from multiple tables when send_date = sysdate and send out the emails on an smtp server.

I have the job all set up to run at 8am everyday and thats all working... Now I just have to set up the procedure it's calling to actually send these emails. The place where I am working have an email package, process, and pls... It's enormous and I can't decifer it in order to use in my procedure. I was hoping to get some help on this....

My boss is currently on vaca so I got NO help!

What do I need out of this? Do I need all of it and then add in something to SELECT (columns) FROM (table) WHERE send_date = TRUNC(sysdate);???

Heres their Email package
create or replace PACKAGE BODY TEC_EMAIL_UTIL AS


FUNCTION get_app_link (
  p_app_id    IN NUMBER
)
RETURN VARCHAR2
AS
  l_link_html   VARCHAR2(2000);
   l_http_host   VARCHAR2(255);
   l_protocol    VARCHAR2(255);
   l_script_name VARCHAR2(255);
 BEGIN
   l_http_host := OWA_UTIL.get_cgi_env ('HTTP_HOST');
   l_protocol := lower(OWA_UTIL.get_cgi_env('REQUEST_PROTOCOL'));
   l_script_name := OWA_UTIL.get_cgi_env ('SCRIPT_NAME');
   l_link_html := l_protocol || '://>/' || l_http_host || l_script_name || '/f?p=' || p_app_id || ':3';
   RETURN l_link_html;
 END get_app_link;


 -- REPLACE_KEYWORD
 FUNCTION  replace_keyword (
   p_keyword         IN VARCHAR2,
   p_notif_id        IN NUMBER DEFAULT NULL,
   p_notif_seq       IN NUMBER DEFAULT NULL,
   p_sub_id          IN NUMBER DEFAULT NULL,
   p_lvl_id          IN NUMBER DEFAULT NULL,
   p_status_id       IN NUMBER DEFAULT NULL,
   p_outage_type_id  IN NUMBER DEFAULT NULL,
   p_info_type_id    IN NUMBER DEFAULT NULL,
   p_app_id          IN NUMBER DEFAULT NULL
 )
 RETURN VARCHAR2 AS
   l_var_type          VARCHAR2(100);
   l_replacement_str   VARCHAR2(2000);
   l_replaced_by       VARCHAR2(2000);
 BEGIN
   SELECT variable_type, replaced_by
   INTO   l_var_type, l_replaced_by
   FROM   tec_email_keywords
   WHERE  LOWER(keyword) = LOWER(p_keyword);
   --
   IF l_replaced_by IS NULL THEN
     RETURN NULL;
   END IF;
   --
   CASE
     -- notification ID#
     WHEN ((l_var_type = 'notif_id')AND(p_notif_id IS NOT NULL)) THEN
       EXECUTE IMMEDIATE l_replaced_by
       INTO l_replacement_str USING p_notif_id;

     -- user id
     WHEN ((l_var_type = 'sub_id')AND(p_sub_id IS NOT NULL)) THEN
       EXECUTE IMMEDIATE l_replaced_by
       INTO l_replacement_str USING p_sub_id;

     -- outage type
     WHEN ((l_var_type = 'outage_type_id')AND(p_outage_type_id IS NOT NULL)) THEN
       EXECUTE IMMEDIATE l_replaced_by
       INTO l_replacement_str USING p_outage_type_id;

     -- information release type
     WHEN ((l_var_type = 'info_type_id')AND(p_info_type_id IS NOT NULL)) THEN
       EXECUTE IMMEDIATE l_replaced_by
       INTO l_replacement_str USING p_info_type_id;

     -- outage status id
     WHEN ((l_var_type = 'status_id') AND (p_status_id IS NOT NULL)) THEN
       EXECUTE IMMEDIATE l_replaced_by
       INTO l_replacement_str USING p_status_id;

     -- replacement string is simply the outage notif_id
     WHEN (((l_var_type = 'notif_id')AND(INSTR(':'|| l_replaced_by || ':', ':SELECT:') = 0)) AND (p_notif_id IS NOT NULL)) THEN
         l_replacement_str := '#' || TO_CHAR(p_notif_id);

     -- information release type id
     WHEN ((l_var_type = 'lvl_id') AND (p_lvl_id IS NOT NULL)) THEN
       EXECUTE IMMEDIATE l_replaced_by
       INTO l_replacement_str USING p_lvl_id;
     -- user information (which requires both notif_id and notif_seq)

     WHEN ((INSTR(l_replaced_by, ':') > 0) AND ( (INSTR(':'|| l_var_type ||':', ':notif_id:') > 0) AND (INSTR(':'|| l_var_type ||':', ':notif_seq:') > 0))) THEN
       EXECUTE IMMEDIATE l_replaced_by
       INTO l_replacement_str USING p_notif_id, p_notif_seq;

     -- application hyperlink
     WHEN ((l_var_type = 'app_id')AND(p_app_id IS NOT NULL)) THEN
       EXECUTE IMMEDIATE l_replaced_by
       INTO l_replacement_str USING p_app_id;
     -- l_replacement_str := tec_email_util.get_app_link(p_app_id);

     WHEN (l_var_type = 'static') THEN
       l_replacement_str := l_replaced_by;
     ELSE
       l_replacement_str := p_keyword;
       --
   END CASE;

    RETURN l_replacement_str;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_replacement_str := p_keyword;
        RETURN  l_replacement_str;
  END replace_keyword;
  -- end of replace keyword function
  --
  -- keyword replacement for test emails
   FUNCTION replace_keyword_test (
    p_keyword         IN VARCHAR2,
    p_notif_id        IN NUMBER DEFAULT NULL,
    p_sub_id          IN NUMBER DEFAULT NULL,
    p_lvl_id          IN NUMBER DEFAULT NULL,
    p_status_id       IN NUMBER DEFAULT NULL,
    p_outage_type_id  IN NUMBER DEFAULT NULL,
    p_info_type_id    IN NUMBER DEFAULT NULL,
    p_app_id          IN NUMBER DEFAULT NULL,
    p_title           IN VARCHAR2 DEFAULT NULL,
    p_imp_svcs        IN VARCHAR2 DEFAULT NULL,
    p_user_info       IN VARCHAR2 DEFAULT NULL,
    p_start           IN DATE DEFAULT NULL,
    p_end             IN DATE DEFAULT NULL
  )
  RETURN VARCHAR2
  AS
    l_replacement_str     VARCHAR2(4000);
  BEGIN
    CASE
      --
      WHEN p_keyword = '@NOTIF_ID@' THEN
        l_replacement_str := '#' || p_notif_id;
      --
      WHEN p_keyword = '@STATUS@' THEN
        SELECT status_name INTO l_replacement_str
        FROM   tec_statuses
        WHERE  status_id = p_status_id;
      --
      WHEN p_keyword = '@SVC_LIST@' AND p_imp_svcs IS NOT NULL THEN
        l_replacement_str := get_svc_ul(p_imp_svcs);
      --
      WHEN p_keyword = '@START_TIME@' AND p_start IS NOT NULL THEN
        l_replacement_str := TO_CHAR(p_start, 'Mon DD, YYYY HH:MI AM');
      --
      WHEN p_keyword = '@END_TIME@' AND p_end IS NOT NULL THEN
        SELECT DECODE(p_end, NULL, 'UNKNOWN', TO_CHAR(p_end, 'Mon DD, YYYY HH:MI AM'))
        INTO   l_replacement_str
        FROM   DUAL;
      --
      WHEN p_keyword = '@SENDER@' AND p_sub_id IS NOT NULL THEN
        SELECT first_name || ' ' || last_name INTO l_replacement_str
        FROM   tec_subs
        WHERE  sub_id = p_sub_id;
      --
      WHEN p_keyword = '@USER_ENTRY@' AND p_user_info IS NOT NULL THEN
        l_replacement_str := p_user_info;

      WHEN p_keyword = '@NOTIF_TITLE@' AND p_title IS NOT NULL THEN
        l_replacement_str := p_title;
      --
      WHEN p_keyword = '@IMPACT_LVL@' AND p_lvl_id IS NOT NULL THEN
        SELECT DECODE(p_lvl_id, 1, 'test', 2, 'prod', 3, 'test & prod') INTO l_replacement_str
        FROM   DUAL;
      --
      WHEN p_keyword = '@OUTAGE_TYPE@' AND p_outage_type_id IS NOT NULL THEN
        SELECT type_name INTO l_replacement_str
        FROM   tec_types
        WHERE  type_id = p_outage_type_id;
      --
      WHEN p_keyword = '@INFO_RLSE_TYPE@' AND p_info_type_id IS NOT NULL THEN
        SELECT email_string
        INTO   l_replacement_str
        FROM   tec_info_types
        WHERE  info_type_id = p_info_type_id;
      --
      WHEN p_keyword = '@APPLICATION_LINK@' AND p_app_id IS NOT NULL THEN
        l_replacement_str := get_app_link(p_app_id);

      ELSE
        SELECT replaced_by INTO l_replacement_str
        FROM   tec_email_keywords
        WHERE  LOWER(keyword) = LOWER(p_keyword);
    END CASE;
    RETURN l_replacement_str;
    EXCEPTION
      WHEN OTHERS THEN
        l_replacement_str := '';
        RETURN l_replacement_str;
  END replace_keyword_test;
  --
  -- PARSE KEYWORDS
  FUNCTION parse_keywords (
    p_text            IN VARCHAR2,
    p_notif_id        IN NUMBER DEFAULT NULL,
    p_notif_seq       IN NUMBER DEFAULT NULL,
    p_sub_id          IN NUMBER DEFAULT NULL,
    p_lvl_id          IN NUMBER DEFAULT NULL,
    p_status_id       IN NUMBER DEFAULT NULL,
    p_outage_type_id  IN NUMBER DEFAULT NULL,
    p_info_type_id    IN NUMBER DEFAULT NULL,
    p_app_id          IN NUMBER DEFAULT NULL,
    p_title           IN VARCHAR2 DEFAULT NULL,
    p_imp_svcs        IN VARCHAR2 DEFAULT NULL,
    p_user_info       IN VARCHAR2 DEFAULT NULL,
    p_start           IN DATE DEFAULT NULL,
    p_end             IN DATE DEFAULT NULL,
    p_use_type        IN VARCHAR2   -- used to indicate whether use is testing email, or sending an outage
  )
  RETURN VARCHAR2 AS
   l_temp_text      VARCHAR2(4000) := p_text;
   l_text                       VARCHAR2(4000);

   l_word_count      NUMBER := 1;
   l_keyword_used    VARCHAR2(500);
   l_keywords_used       NUMBER := 1;
   l_original_length NUMBER := 0;
   l_replaced_by     VARCHAR2(500) := '';

   l_pos_plchldr    NUMBER := 0;
   l_start_pos      NUMBER := 0;
   l_end_pos        NUMBER := 0;
   l_offset                   NUMBER := 1;
   l_replace_offset NUMBER := 0;
  BEGIN
    l_original_length := length(l_temp_text);
     LOOP
      IF (l_end_pos >= l_original_length) THEN
        EXIT;
      ELSE
        -- find the starting position of the keyword
        l_start_pos := INSTR(l_temp_text, '@', l_pos_plchldr + l_offset, 1);
        -- if keyword is not found, check for remaining text
        IF l_start_pos = 0 THEN
          IF LENGTH( SUBSTR( l_temp_text, l_pos_plchldr + l_offset)) > 1 THEN
            l_text := l_text || substr(l_temp_text, l_pos_plchldr + l_offset);
            EXIT;
          END IF;
        END IF;
      -- find the ending position of the first keyword
      l_end_pos := INSTR(l_temp_text, '@', l_start_pos + l_offset, 1);
      l_keyword_used := SUBSTR(l_temp_text, l_start_pos, ( l_end_pos - l_start_pos + l_offset));
      --
      IF LOWER(p_use_type) = 'send' OR UPPER(p_use_type) = 'SEND' THEN
        -- get the keyword replacement string
        l_replaced_by := replace_keyword( l_keyword_used
                                          , p_notif_id
                                          , p_notif_seq
                                          , p_sub_id
                                          , p_lvl_id
                                          , p_status_id
                                          , p_outage_type_id
                                          , p_info_type_id
                                          , p_app_id
                                        );
      ELSIF LOWER(p_use_type) = 'test' OR UPPER(p_use_type) = 'TEST' THEN
        l_replaced_by := replace_keyword_test ( l_keyword_used
                                              , p_notif_id
                                              , p_sub_id
                                              , p_lvl_id
                                              , p_status_id
                                              , p_outage_type_id
                                              , p_info_type_id
                                              , p_app_id
                                              , p_title
                                              , p_imp_svcs
                                              , p_user_info
                                              , p_start
                                              , p_end
                                              );
      END IF;


      l_text := l_text || substr(l_temp_text, l_pos_plchldr + l_offset, (l_end_pos - l_pos_plchldr));
      -- replace all occurances of the keyword with its substitution string
      l_text := REPLACE( l_text, l_keyword_used, l_replaced_by );
      l_pos_plchldr := l_end_pos;
      -- increment the number of keywords used
      l_keywords_used := l_keywords_used + 1;
      EXIT WHEN ( (l_end_pos = l_original_length) OR (l_end_pos = 0) );
    END IF;
  END LOOP;
  RETURN l_text;
  END parse_keywords;

  FUNCTION get_svc_ul (
    p_svc_str    IN VARCHAR2 DEFAULT NULL
  )
  RETURN VARCHAR2
  IS
    l_html      VARCHAR2(4000);
    --l_svc_count NUMBER := 1;
  BEGIN
    IF p_svc_str IS NOT NULL THEN
      l_html := '<ol>';
      FOR c_svcs IN ( SELECT svc_name
                      FROM   tec_svcs
                      WHERE INSTR(':'|| p_svc_str ||':', ':'|| svc_id ||':') > 0
                      ORDER BY svc_name ASC
                    ) LOOP
            l_html := l_html || '<li>' || c_svcs.svc_name || '</li>';
            --l_svc_count := l_svc_count + 1;
      END LOOP;

      l_html := l_html || '</ol>';

    ELSE
      l_html := 'none listed';
    END IF;

    RETURN l_html;

    EXCEPTION
      WHEN OTHERS THEN
        RETURN 'none listed';
  END get_svc_ul;

  FUNCTION get_txt_recipients (
    p_outage_type   IN NUMBER,
    p_svc_str       IN VARCHAR2 DEFAULT NULL,
    p_impact_lvl    IN NUMBER,
    p_its_ids       IN VARCHAR2 DEFAULT NULL,
    p_no_send       IN VARCHAR2 DEFAULT NULL
  )
  RETURN wwv_flow_global.vc_arr2
  AS
    l_recipients    wwv_flow_global.vc_arr2;
    l_rec_count     NUMBER := 1;
    l_comp_ids      VARCHAR2(500);
    l_awrns_lvl     VARCHAR2(500);
    l_lead_team     NUMBER;
  BEGIN
    SELECT TO_CHAR(awrns_lvl) INTO l_awrns_lvl
    FROM   tec_type_map
    WHERE  type_id = p_outage_type;
    -- get any and all its users who need to be contacted if the ITS-Distro List string contains the ID # for ITS-Lead
    BEGIN
      SELECT its_id INTO l_lead_team
      FROM   tec_its_contacts
      WHERE  LOWER(its_contact_name) = 'its lead';

      EXCEPTION
        WHEN NO_DATA_FOUND THEN -- use numerical value for ITS Lead that was used during development
          l_lead_team := 2;
    END;

    -- if its lead is selected, find members and get their phone contacts
    IF INSTR(':'|| p_its_ids || ':', ':'|| l_lead_team || ':') > 0 THEN
      FOR c_lead IN ( SELECT tc.ph_em, tp.prov_address
                      FROM   tec_contacts tc, tec_providers tp
                      WHERE  tc.sub_id IN ( SELECT sub_id
                                            FROM   tec_its_distro_map
                                            WHERE  INSTR(':'|| l_lead_team ||':', ':'|| its_id || ':') > 0 )
                      AND    tc.prov_id = ( SELECT prov_id
                                            FROM   tec_providers
                                            WHERE  prov_type = 'Cell Phone' )
                      AND    tc.contact_id = ( SELECT MIN(contact_id)
                                               FROM   tec_contacts
                                               WHERE  sub_id = tc.sub_id
                                               AND    prov_id = tc.prov_id )
                      AND EXISTS ( SELECT 1
                                   FROM   tec_subs
                                   WHERE  sub_id = tc.sub_id
                                   AND    inactive_flag IS NULL
                                   AND    NVL2(leave_start, TO_DATE(leave_start, 'DD-MM-YYYY HH24:MI'), TO_DATE('01-01-2099 12:00', 'DD-MM-YYYY HH24:MI')) >= SYSDATE
                                   AND    NVL2(leave_end, TO_DATE(leave_end, 'DD-MM-YYYY HH24:MI'), TO_DATE('01-01-2001 12:00', 'DD-MM-YYYY HH24:MI')) <= SYSDATE       )
                      AND    tp.prov_id = tc.prov_id
                      ORDER BY tc.contact_id
                    ) LOOP
            l_recipients(l_rec_count) := c_lead.ph_em || '@' || c_lead.prov_address;
            l_rec_count := l_rec_count + 1;
      END LOOP;
    END IF;
    --
    IF p_no_send IS NULL THEN
      -- get the svc components affected by the outage
      FOR c_svc_comps IN ( SELECT comp_id
                           FROM   tec_svc_comps
                           WHERE  INSTR(':'|| p_svc_str ||':', ':'|| svc_id ||':') > 0
                           AND    awrns_lvl = l_awrns_lvl
                           ORDER BY comp_id
                         ) LOOP
        l_comp_ids := l_comp_ids || ':' || TO_CHAR(c_svc_comps.comp_id);
      END LOOP;
      -- remove the leading colon from the string
      l_comp_ids := substr(l_comp_ids, INSTR(l_comp_ids,':',1)+1);
      --
      FOR c_tec_usrs IN ( SELECT tc.ph_em, tp.prov_address
                          FROM   tec_contacts tc, tec_providers tp
                          WHERE  tc.sub_id IN ( SELECT sub_id
                                                FROM   tec_user_comps
                                                WHERE  INSTR(':'|| l_comp_ids ||':', ':'|| comp_id ||':') > 0
                                                AND    (lvl_id = p_impact_lvl) OR (lvl_id = 3)  ) -- get users who have selected the service and awrns level that matches the svc comp being notified
                          AND    INSTR(':'|| tc.awrns_lvl || ':', ':'|| l_awrns_lvl ||':') > 0
                          AND EXISTS ( SELECT 1
                                       FROM   tec_subs
                                       WHERE  sub_id = tc.sub_id
                                       AND    inactive_flag IS NULL
                                       AND    NVL2(leave_start, TO_DATE(leave_start, 'DD-MM-YYYY HH24:MI'), TO_DATE('01-01-2099 12:00', 'DD-MM-YYYY HH24:MI')) >= SYSDATE
                                       AND    NVL2(leave_end, TO_DATE(leave_end, 'DD-MM-YYYY HH24:MI'), TO_DATE('01-01-2001 12:00', 'DD-MM-YYYY HH24:MI')) <= SYSDATE     )
                          AND    tc.prov_id IN ( SELECT prov_id
                                                 FROM tec_providers
                                                 WHERE  prov_type = 'Cell Phone' )
                          AND    tp.prov_id = tc.prov_id
                          ORDER BY tc.contact_id
                        ) LOOP
            l_recipients(l_rec_count) := c_tec_usrs.ph_em || '@' || c_tec_usrs.prov_address;
            l_rec_count := l_rec_count + 1;
      END LOOP;
    END IF;
    RETURN l_recipients;
    EXCEPTION
      WHEN OTHERS THEN
        RETURN l_recipients;
  END get_txt_recipients;
  -- returns an array of fully concatenated email address to which to mail an outage notification
  FUNCTION get_email_recipients (
    p_outage_type   IN NUMBER,
    p_svc_str       IN VARCHAR2 DEFAULT NULL,
    p_impact_lvl    IN NUMBER,
    p_its_ids       IN VARCHAR2 DEFAULT NULL,
    p_no_send       IN VARCHAR2 DEFAULT NULL
  )
  RETURN wwv_flow_global.vc_arr2
  AS
    l_recipients     wwv_flow_global.vc_arr2;
    l_rec_count      NUMBER := 1;
    l_comp_ids       VARCHAR2(500);
    l_awrns_lvl      VARCHAR2(10);
  BEGIN
    -- outage type of 1 == scheduled
    -- outage types of 2-4 = unscheduled
    SELECT TO_CHAR(awrns_lvl) INTO l_awrns_lvl
    FROM   tec_type_map
    WHERE  type_id = p_outage_type;
    --
    IF p_its_ids IS NOT NULL THEN
      FOR c_distros IN ( SELECT its_address
                         FROM   tec_its_contacts
                         WHERE  INSTR(':'|| p_its_ids || ':', ':'|| its_id ||':') > 0
                         ORDER BY its_id
                       ) LOOP
        l_recipients(l_rec_count) := c_distros.its_address;
        l_rec_count := l_rec_count + 1;
      END LOOP;
    END IF;
    --
    IF p_no_send IS NULL THEN
      FOR c_svc_comps IN ( SELECT comp_id
                           FROM   tec_svc_comps
                           WHERE  INSTR(':'|| p_svc_str ||':', ':'|| svc_id ||':') > 0
                           AND    INSTR(':'|| l_awrns_lvl ||':', ':'|| awrns_lvl || ':') > 0
                           ORDER BY comp_id
                         ) LOOP
        l_comp_ids := l_comp_ids || ':' || c_svc_comps.comp_id;
      END LOOP;
      -- remove the leading colon from the string
      l_comp_ids := substr(l_comp_ids, INSTR(l_comp_ids,':',1)+1);
      --
      FOR c_tec_usrs IN ( SELECT tc.ph_em, tp.prov_address
                          FROM   tec_contacts tc, tec_providers tp
                          WHERE  tc.sub_id IN ( SELECT sub_id
                                                FROM   tec_user_comps
                                                WHERE  INSTR(':'|| l_comp_ids ||':', ':'|| comp_id ||':') > 0
                                                AND    ( (lvl_id = p_impact_lvl) OR (lvl_id = 3) )
                                              ) -- get users who have selected the service and awrns level that matches the svc comp being notified
                          AND    INSTR(':'|| tc.awrns_lvl || ':', ':'|| l_awrns_lvl ||':') > 0

                          AND    tc.prov_id IN ( SELECT prov_id
                                                 FROM tec_providers
                                                 WHERE  prov_type = 'Email Address' )
                          AND EXISTS ( SELECT 1
                                       FROM   tec_subs
                                       WHERE  sub_id = tc.sub_id
                                       AND    inactive_flag IS NULL
                                       AND    NVL2(leave_start, TO_DATE(leave_start, 'DD-MM-YYYY HH24:MI'), TO_DATE('01-01-2099 12:00', 'DD-MM-YYYY HH24:MI')) >= SYSDATE
                                       AND    NVL2(leave_end, TO_DATE(leave_end, 'DD-MM-YYYY HH24:MI'), TO_DATE('01-01-2001 12:00', 'DD-MM-YYYY HH24:MI')) <= SYSDATE
                                     )
                         AND    tp.prov_id = tc.prov_id
                         ORDER BY tc.contact_id
                        ) LOOP
        l_recipients(l_rec_count) := c_tec_usrs.ph_em || '@' || c_tec_usrs.prov_address;
        l_rec_count := l_rec_count + 1;
      END LOOP;
  --    INSERT INTO tec_test ("STR1","STR2","STR3","STR4") VALUES ('comp_id = ' || l_comp_ids,'awrns_lvl = ' || l_awrns_lvl,'p_svc_str = ' || p_svc_str,TO_CHAR(l_rec_count) || ' recipients found'); COMMIT;
    END IF;
    RETURN l_recipients;
    EXCEPTION
      WHEN OTHERS THEN
        RETURN l_recipients;
  END get_email_recipients;
  -- END send_emails
END TEC_EMAIL_UTIL;


Is This A Good Question/Topic? 0
  • +

Page 1 of 1