New Topic/Question
Reply
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;
MultiQuote
| 