OraFAQ Forum: Application Express, ORDS & MOD_PLSQL

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Sending Emails based on query (APEX/ Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sending Emails based on query [message #557783]

Fri, 15 June 2012 14:00

trulysick
Messages: 5
Registered: June 2012
Location: Michigan

Junior Member

Ok I am building a job that runs daily and sends out email based on rows in table where send_date = Trunc(sysdate)....

I've got the job all set up and that runs, but I am working on the procedure it is calling..

Basically what I need is something that will pull out information from the row where send_date = TRUNC(sysdate) then send emails to right places/people based on this.

We have email.util and email.pkg all set up then we also have a process in the page called send_emails. All of these are pulling data from different tables and combining them to send the emails... I am unsure of how exactly to get my procedure to run. Do I call these other functions, etc from with my procedure? Or should I copy over the send_emails process which does call the email.util???

I'm so lost, but this is what I have so far:
This is my procedure that the job uses:

Code is right here

Procedure SEND_POSTDATED_EMAIL
is 

BEGIN
 for x in (SELECT type_id, lvl_id, title, start_date, end_date
           FROM tec_notif
           WHERE send_date = TRUNC(sysdate))
       loop
          --send mail
          
          end loop;
      end; 



--Below is the send_email process, but i'm not sure how to integrate it into my procedure above  I'm a noob and hopefully you guys can understand whats going on better then me

DECLARE 

 -- notification attribute vars
 
  l_notif_id            NUMBER;
  l_notif_seq           NUMBER;
  l_notif_type          NUMBER;
  l_lvl_id              NUMBER;
  l_start               DATE;
  l_end                 DATE;
  l_send_date           DATE;
  l_notif_title         VARCHAR2(500);
  l_resend              VARCHAR2(500);
  l_its_ids             VARCHAR2(100);
  l_its_div             NUMBER;
  --
  l_http_host           VARCHAR2(500);
  l_email_recipients    wwv_flow_global.vc_arr2;
  l_count_str           VARCHAR2(255);
  l_user_str            VARCHAR2(4000);
  --
  l_email_body          VARCHAR2(4000);
  l_parsed_body         VARCHAR2(4000);
  l_email_subj          VARCHAR2(500);
  l_parsed_subj         VARCHAR2(500);
  --
  l_toname              VARCHAR2(500);
  l_frmname             VARCHAR2(255) := 'LCC-TEC@email.lcc.edu';
  l_em_status           VARCHAR2(255);
  --
  l_emed_user_cnt       NUMBER := 0;
  l_distro_names        VARCHAR2(500);
  l_its_send_msg        VARCHAR2(1000);
  cnt                   NUMBER;


BEGIN
  l_http_host := OWA_UTIL.get_cgi_env ('HTTP_HOST');
  --
  BEGIN
    SELECT its_id INTO l_its_div
    FROM   tec_its_contacts
    WHERE  LOWER(its_contact_name) = 'its-div';
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        l_its_div := 1;
  END;
  
  -- gets rows that send date is today.


 IF INSTR(':'|| :P2_ITS_IDS ||':', ':'|| l_its_div ||':') > 0 THEN
    l_its_ids := '1';
  ELSE
    l_its_ids := :P2_ITS_IDS;
  END IF;
  -- have to get b/c page items are turned into display text after notification is created (do not want users to change static attributes)
  SELECT type_id, lvl_id, title, start_date, end_date
  INTO   l_notif_type, l_lvl_id, l_notif_title, l_start, l_end
  FROM   tec_notif
  WHERE  notif_id = :P2_NOTIF_ID;
  --  
  l_email_recipients := tec_email_util.get_email_recipients(l_notif_type,:P2_IMP_SVCS,l_lvl_id,l_its_ids,:P2_NO_SEND); 
-- get 'who' will be emailed
  -- get the email body and subject line templates
  SELECT body, subject
  INTO   l_email_body, l_email_subj
  FROM   tec_email_config
  WHERE  outage_type_id = :P2_NOTIF_TYPE_ID;
  --




 IF l_its_ids IS NOT NULL THEN
    FOR c_its_ids IN ( SELECT its.its_contact_name,its.its_id
                       FROM   tec_its_contacts its
                       WHERE  INSTR(':'|| l_its_ids ||':', ':'|| its.its_id ||':') > 0
                       ORDER BY its.its_id
                     ) LOOP
        l_distro_names := l_distro_names || ', ' || c_its_ids.its_contact_name;
    END LOOP;
    l_distro_names := substr(l_distro_names, INSTR(l_distro_names, ',', 1) + 1);
--    l_its_send_msg := '<br><br>The following distribution lists would have been emailed: ' || l_distro_names;
  END IF;
  --
  l_parsed_body := tec_email_util.parse_keywords(l_email_body,:P2_NOTIF_ID,:P2_NOTIF_SEQ,:P2_SUB_ID,l_lvl_id,:P2_STATUS_ID,l_notif_type,:P2_INFO_TYPE,'&APP_ID.',l_notif_title,:P2_IMP_SVCS,:P2_EMAIL_BODY,l_start,l_end,'SEND');
  l_parsed_subj := tec_email_util.parse_keywords(l_email_subj,:P2_NOTIF_ID,:P2_NOTIF_SEQ,:P2_SUB_ID,l_lvl_id,:P2_STATUS_ID,l_notif_type,:P2_INFO_TYPE,'&APP_ID.',l_notif_title,:P2_IMP_SVCS,NULL,l_start,l_end,'SEND');
  
  
  -- if test instance, email to user and concatenate who would actually receive emails to the body
  IF INSTR(':'|| lower(l_http_host) ||':', 'horizon') > 0 THEN -- test instance
    l_resend := :P2_RESEND_NOTE;

	IF LENGTH(l_distro_names) > 0 THEN
		l_its_send_msg := '<br><br>The following distribution lists would have been emailed: ' || l_distro_names;    
	END IF;
	--
	IF :P2_NO_SEND IS NULL THEN
		l_emed_user_cnt := l_email_recipients.count;
	END IF;
	l_count_str := '<br><br>*This was sent from the test instance of TEC. In Production this would been sent to - ' || l_emed_user_cnt || ' people*';
	l_parsed_body := l_parsed_body || l_count_str || l_its_send_msg;
	l_toname := LOWER(:APP_USER) || '@email.lcc.edu';
	
	IF :P2_IS_NEW_RECORD = 'TRUE' THEN
	

		-- send the email to the person using the test application
		lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
		
	
	ELSE	
		IF l_resend = 'Yes' THEN
			-- send the email to the person using the test application
			lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
		END IF;
	END IF;
	
	
  ELSE   -- WE ARE IN PRODUCTION
    l_resend := :P2_RESEND_NOTE;
    
     --
     IF LENGTH(l_distro_names) > 0 THEN
       l_its_send_msg := '<br><br>This message was sent to the following distribution list(s): ' || l_distro_names;    
     END IF;
     l_parsed_body := l_parsed_body || l_its_send_msg;
     --
	 IF :P2_IS_NEW_RECORD = 'TRUE' THEN
		FOR i in 1..l_email_recipients.count
		LOOP
		
			l_toname := TO_CHAR(l_email_recipients(i));
			lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
		
		END LOOP;
	ELSE	
		IF l_resend = 'Yes' THEN
			FOR i in 1..l_email_recipients.count
			LOOP
		
				l_toname := TO_CHAR(l_email_recipients(i));
				lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
		
			END LOOP;
		END IF;
    END IF;
	
   END IF;
  
  END IF;
  --
  END; 
END;

Here is the email.util package we use:

 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;

I'm unsure if anything in the email.util needs to be changed or can I just slice up the send_emails process and use it in my procedure and how??

any help is much much appreciated!

[EDITED by LF: applied [spoiler] tags]

[Updated on: Fri, 15 June 2012 15:39] by Moderator

Report message to a moderator

Re: Sending Emails based on query [message #557785 is a reply to message #557783]

Fri, 15 June 2012 14:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What exactly do you expect from us here?

Report message to a moderator

Re: Sending Emails based on query [message #557788 is a reply to message #557783]

Fri, 15 June 2012 14:21

trulysick
Messages: 5
Registered: June 2012
Location: Michigan

Junior Member

I just need to know how to get my procedure to send out emails based on that query....
Do I just stick that whole process into my Procedure?

I know it's a LOT of code up there, but I figured it may be needed

Report message to a moderator

Re: Sending Emails based on query [message #557789 is a reply to message #557788]

Fri, 15 June 2012 14:38

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

In general, you can either copy and paste your whole process to send the mail into your loop within your
send_postdated_email procedure or you can create a procedure from your whole process, then reference that
procedure within your send_postdated_email procedure. I would prefer the latter. Either way, you will
then need to reference the variables from your loop as x.type_id, x.lvl_id, x.title, x.start_date, and
x.end_date and pass then to the procedure in the loop. So, your procedure would need to accept those
parameters and substitute them in your process, something like:

create or replace procedure send_mail
  (l_type_id in tec_notif.type_id%type,
   l_lvl_id  in tec_notif.lvl_id%type,
   l_title   in tec_notif.title%type,
   l_start   in tec_notif.start_date%type,
   l_end     in tec_notif.end_date%type)
as
  -- other variables
begin
  -- send_mail process
end;
/
show errors
create or replace Procedure SEND_POSTDATED_EMAIL
is 
BEGIN
  for x in 
    (SELECT type_id, lvl_id, title, start_date, end_date
     FROM   tec_notif
     WHERE  send_date = TRUNC(sysdate))
  loop
    send_mail (x.type_id, x.lvl_id, x.title, x.start_date, x.end_date);
  end loop;
end; 
/
show errors

Report message to a moderator

Re: Sending Emails based on query [message #557791 is a reply to message #557789]

Fri, 15 June 2012 14:57

trulysick
Messages: 5
Registered: June 2012
Location: Michigan

Junior Member

Ok, cool I'll give that a shot on Monday since the workday is over.

Thanks for the help! I was really hoping I would be able to pass something... I'm not big on procedural stuff i'm a Java person myself.

Report message to a moderator

Re: Sending Emails based on query [message #557797 is a reply to message #557791]

Fri, 15 June 2012 15:35

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It is too much code for me so my answer might not help at all, but - as you use Apex, perhaps you could/should have a look at APEX_MAIL package to send an email from an Oracle Application Express application.

Report message to a moderator

Re: Sending Emails based on query [message #558009 is a reply to message #557797]

Mon, 18 June 2012 13:40

trulysick
Messages: 5
Registered: June 2012
Location: Michigan

Junior Member

OK so I tried doing this :

create or replace procedure send_postdated_email
IS

  l_notif_id            NUMBER;
   l_notif_seq           NUMBER;
   l_notif_type          NUMBER;
   l_lvl_id              NUMBER;
   l_start               DATE;
   l_end                 DATE;
   l_send_date           DATE;
   l_notif_title         VARCHAR2(500);
   l_resend              VARCHAR2(500);
   l_its_ids             VARCHAR2(100);
   l_its_div             NUMBER;
   --
   l_http_host           VARCHAR2(500);
   l_email_recipients    wwv_flow_global.vc_arr2;
   l_count_str           VARCHAR2(255);
   l_user_str            VARCHAR2(4000);
   --
   l_email_body          VARCHAR2(4000);
   l_parsed_body         VARCHAR2(4000);
   l_email_subj          VARCHAR2(500);
   l_parsed_subj         VARCHAR2(500);
   --
   l_toname              VARCHAR2(500);
   l_frmname             VARCHAR2(255) := 'LCC-TEC@email.lcc.edu';
   l_em_status           VARCHAR2(255);
   --
   l_emed_user_cnt       NUMBER := 0;
   l_distro_names        VARCHAR2(500);
   l_its_send_msg        VARCHAR2(1000);
   



BEGIN
 for x in (SELECT type_id, lvl_id, title, start_date, end_date
           FROM tec_notif
           WHERE send_date = TRUNC(sysdate))
       loop
         
          --send mail
          
        
           
         BEGIN
           l_http_host := OWA_UTIL.get_cgi_env ('HTTP_HOST');
           --
           BEGIN
             SELECT its_id INTO l_its_div
             FROM   tec_its_contacts
             WHERE  LOWER(its_contact_name) = 'its-div';
             EXCEPTION
               WHEN NO_DATA_FOUND THEN
                 l_its_div := 1;
           END;
           --
           IF INSTR(':'|| :P2_ITS_IDS ||':', ':'|| l_its_div ||':') > 0 THEN
             l_its_ids := '1';
           ELSE
             l_its_ids := :P2_ITS_IDS;
           END IF;
           -- have to get b/c page items are turned into display text after notification is created (do not want users to change static attributes)
           SELECT type_id, lvl_id, title, start_date, end_date, send_date
           INTO   l_notif_type, l_lvl_id, l_notif_title, l_start, l_end, l_send_date
           FROM   tec_notif
           WHERE  notif_id = :P2_NOTIF_ID;
           --  
           l_email_recipients := tec_email_util.get_email_recipients(l_notif_type,:P2_IMP_SVCS,l_lvl_id,l_its_ids,:P2_NO_SEND); 
         -- get 'who' will be emailed
           -- get the email body and subject line templates
           SELECT body, subject
           INTO   l_email_body, l_email_subj
           FROM   tec_email_config
           WHERE  outage_type_id = :P2_NOTIF_TYPE_ID;
           --
         
         
         
         
          IF l_its_ids IS NOT NULL THEN
             FOR c_its_ids IN ( SELECT its.its_contact_name,its.its_id
                                FROM   tec_its_contacts its
                                WHERE  INSTR(':'|| l_its_ids ||':', ':'|| its.its_id ||':') > 0
                                ORDER BY its.its_id
                              ) LOOP
                 l_distro_names := l_distro_names || ', ' || c_its_ids.its_contact_name;
             END LOOP;
             l_distro_names := substr(l_distro_names, INSTR(l_distro_names, ',', 1) + 1);
         --    l_its_send_msg := '<br><br>The following distribution lists would have been emailed: ' || l_distro_names;
           END IF;
           --
           l_parsed_body := tec_email_util.parse_keywords(l_email_body,:P2_NOTIF_ID,:P2_NOTIF_SEQ,:P2_SUB_ID,l_lvl_id,:P2_STATUS_ID,l_notif_type,:P2_INFO_TYPE,'&APP_ID.',l_notif_title,:P2_IMP_SVCS,:P2_EMAIL_BODY,l_start,l_end,'SEND');
           l_parsed_subj := tec_email_util.parse_keywords(l_email_subj,:P2_NOTIF_ID,:P2_NOTIF_SEQ,:P2_SUB_ID,l_lvl_id,:P2_STATUS_ID,l_notif_type,:P2_INFO_TYPE,'&APP_ID.',l_notif_title,:P2_IMP_SVCS,NULL,l_start,l_end,'SEND');
           
           
           -- if test instance, email to user and concatenate who would actually receive emails to the body
           IF INSTR(':'|| lower(l_http_host) ||':', 'horizon') > 0 THEN -- test instance
             l_resend := :P2_RESEND_NOTE;
         
         	IF LENGTH(l_distro_names) > 0 THEN
         		l_its_send_msg := '<br><br>The following distribution lists would have been emailed: ' || l_distro_names;    
         	END IF;
         	--
         	IF :P2_NO_SEND IS NULL THEN
         		l_emed_user_cnt := l_email_recipients.count;
         	END IF;
         	l_count_str := '<br><br>*This was sent from the test instance of TEC. In Production this would been sent to - ' || l_emed_user_cnt || ' people*';
         	l_parsed_body := l_parsed_body || l_count_str || l_its_send_msg;
         	l_toname := LOWER(:APP_USER) || '@email.lcc.edu';
         	
         	IF :P2_IS_NEW_RECORD = 'TRUE' THEN
         	
         
         		-- send the email to the person using the test application
         		lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
         		
         	
         	ELSE	
         		IF l_resend = 'Yes' THEN
         			-- send the email to the person using the test application
         			lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
         		END IF;
         	END IF;
         	
         	
           ELSE   -- WE ARE IN PRODUCTION
             l_resend := :P2_RESEND_NOTE;
             
              --
              IF LENGTH(l_distro_names) > 0 THEN
                l_its_send_msg := '<br><br>This message was sent to the following distribution list(s): ' || l_distro_names;    
              END IF;
              l_parsed_body := l_parsed_body || l_its_send_msg;
              --
         	 IF :P2_IS_NEW_RECORD = 'TRUE' THEN
         		FOR i in 1..l_email_recipients.count
         		LOOP
         		
         			l_toname := TO_CHAR(l_email_recipients(i));
         			lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
         		
         		END LOOP;
         	ELSE	
         		IF l_resend = 'Yes' THEN
         			FOR i in 1..l_email_recipients.count
         			LOOP
         		
         				l_toname := TO_CHAR(l_email_recipients(i));
         				lcc.lcc_email.send_mail(l_frmname,l_frmname,l_toname,l_toname,l_parsed_subj,l_parsed_body,l_em_status);
         		
         			END LOOP;
         		END IF;
             END IF;
         	
            END IF;
            
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                            
           -- 
          END LOOP;  
         END;

But when I force run the job it say this procedure is in an invalid state.... i'm getting errors on the :P2 stuff which are calls to page items(this is an apex app).. which i'm pretty sure i do need. Or do I have to use calls to the database only??

Report message to a moderator