we are getting blank mails when it send to Ex2007 mailbox, but if the same mail go to Ex2003 its readable.
mails are sending through some scripts as scheduled task in oracle server.
here is the script
I suspect something wrong in script ..as ex2007 seven is more specific to RFCs, we may need to add space or lien or something ..
script is follwos ..
===========================================================
create or replace PROCEDURE PR_NOTIFY_DAILYDATAFIXES IS
--L_MAILHOST VARCHAR(100) := 'smtp.mydomain.com';
v_MAILHOST VARCHAR(100) := 'mailhost.mydomain.com';
v_from VARCHAR(100);
n_count NUMBER := 0;
v_subject VARCHAR(200) := 'Lawson Daily Data Fix Notification';
p_package_txt VARCHAR2 (50) := 'UNKNOWN';
p_procedure_txt VARCHAR2 (50) := 'pr_notify_dailydatafixes';
p_location_txt VARCHAR2 (100) := 'UNKNOWN';
p_text_txt VARCHAR2 (200) := 'NONE';
p_bodid NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'MMDDYYYY'));
p_msg_code NUMBER :=0;
v_mail_conn UTL_SMTP.CONNECTION;
CURSOR cur_l_mail IS
SELECT to_addr
FROM acc4wm.MY_SUPPORT_TEAM
WHERE status='Active';
BEGIN
p_location_txt := 'Find the From address for sending out email';
SELECT from_addr
INTO v_from
FROM acc4wm.MY_SUPPORT_TEAM
WHERE status='Active'
AND ROWNUM=1;
p_location_txt := 'Open connection to email host';
v_mail_conn := UTL_SMTP.OPEN_CONNECTION(v_mailhost, 25);
UTL_SMTP.HELO(v_mail_conn, v_mailhost);
UTL_SMTP.MAIL(v_mail_conn, v_from);
FOR rec_l_mail IN cur_l_mail LOOP
UTL_SMTP.RCPT(v_mail_conn, rec_l_mail.TO_ADDR);
END LOOP;
p_location_txt := 'Open data connection';
UTL_SMTP.OPEN_DATA(v_mail_conn);
p_location_txt := 'Prepare From and To email recipients list';
UTL_SMTP.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || CHR(13));
UTL_SMTP.write_data(v_mail_conn, 'From: ' || v_from || CHR(13));
UTL_SMTP.write_data(v_mail_conn, 'Subject: ' || v_subject || CHR(13));
FOR rec_l_mail IN cur_l_mail LOOP
UTL_SMTP.write_data(v_mail_conn, 'To: ' || rec_l_mail.to_addr || CHR(13));
END LOOP;
UTL_SMTP.write_data(v_mail_conn, '' || CHR(13));
/*p_location_txt := 'Open connection to email host';
V_MAIL_CONN, := UTL_SMTP.OPEN_CONNECTION(L_MAILHOST, 25);
UTL_SMTP.HELO(V_MAIL_CONN, L_MAILHOST);
UTL_SMTP.MAIL(V_MAIL_CONN, L_FROM);
UTL_SMTP.RCPT(V_MAIL_CONN, L_TO2);
UTL_SMTP.RCPT(V_MAIL_CONN, L_TO);
p_location_txt := 'Open data connection';
UTL_SMTP.OPEN_DATA(V_MAIL_CONN,);
p_location_txt := 'Prepare From and To email recipients list';
UTL_SMTP.write_data(V_MAIL_CONN, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, 'From: ' || l_from || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, 'Subject: ' || l_subject || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, 'To: ' || l_to || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, 'To: ' || l_to2 || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, 'To: ' || l_to3 || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, 'To: ' || l_to4 || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, 'To: ' || l_to5 || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, 'To: ' || l_to6 || CHR(13));
UTL_SMTP.write_data(V_MAIL_CONN, '' || CHR(13)); */
-- TRI data transfer failed because of Schedule Indicator
p_location_txt:= 'TRI data transfer failed because of Schedule Indicator';
SELECT COUNT(*) INTO n_count
FROM ac4nps.ETRESOURCE WHERE ERES_ID IN ( SELECT TL_RES_ID FROM ac4nps.ETTIMELINE WHERE TL_ID IN
( SELECT TRIM(REPLACE(ADDITIONAL_INFORMATION, ':NONE'))
FROM acc4wm.ACC4_ERROR_LOG
WHERE procedure_name='A4TimeRead'
AND execution_location LIKE 'Get Scheduled Indicator based on tl_id%'
)
)
AND ERES_TIM_DISP = 52;
UTL_SMTP.WRITE_DATA(V_MAIL_CONN, 'TRI data transfer failed because of Schedule Indicator has ' || TO_CHAR(n_count) || ' rows.' || CHR(13) || CHR(13));
-- TRI data transfer failed because of Tax Jurisdiction
p_location_txt:= 'TRI data transfer failed because of Tax Jurisdiction';
n_count := 0;
SELECT COUNT(*) INTO n_count FROM acc4wm.acc4_error_log
WHERE procedure_name='A4TimeRead'
AND execution_location LIKE 'Get Tax Jurisdiction based on tl_id%';
UTL_SMTP.WRITE_DATA(V_MAIL_CONN, 'TRI data transfer failed because of Tax Jurisdiction has ' || TO_CHAR(n_count) || ' rows.' || CHR(13) || CHR(13));
-- Resources having Assignment Date prior to their Start Date
p_location_txt:= 'Resources having Assignment Date prior to their Start Date';
n_count := 0;
SELECT COUNT(*) INTO n_count FROM ac4nps.MWEBRES a, ac4nps.MWEBAUTH b
WHERE b.AUTH_RES_ID = a.RES_ID AND a.RES_STATUS = 10
AND b.AUTH_START_DATE < a.RES_DATE_EXT6;
UTL_SMTP.WRITE_DATA(V_MAIL_CONN, 'Resources having Assignment Date prior to their Start Date has ' || TO_CHAR(n_count) || ' rows.' || CHR(13) || CHR(13));
-- Portalrole Error Issue
p_location_txt:= 'Portal Role Issue';
n_count := 0;
SELECT COUNT(*) INTO n_count FROM ac4nps.MWEBUSER WHERE user_eres_id = 0 OR user_eres_id IS NULL;
UTL_SMTP.WRITE_DATA(V_MAIL_CONN, 'Portal Role Issue has ' || TO_CHAR(n_count) || ' rows.' || CHR(13) || CHR(13));
-- Immediate-Periodic Reports Issue after Reprot Server Run
n_count := 0;
/*
SELECT COUNT(*) INTO n_count FROM ac4nps.MWEBREPQUE
WHERE REPQUE_ID IN (SELECT REPQUE_BASEDON_ID FROM ac4nps.MWEBREPQUE
WHERE REPQUE_RUNSET = 26 AND REPQUE_TYPE = 2 AND REPQUE_RUNTYPE = 1) AND REPQUE_RUNSET = 26;*/
SELECT COUNT(*) INTO n_count
FROM ac4nps.MWEBREPQUE
WHERE REPQUE_RUNSET = 26
AND REPQUE_TYPE = 2
AND REPQUE_RUNTYPE = 1;
UTL_SMTP.WRITE_DATA(V_MAIL_CONN, 'Immediate-Periodic Reports Issue after Reprot Server Run has ' || TO_CHAR(n_count) || ' rows.' || CHR(13) || CHR(13));
-- Group ID Issue
p_location_txt:= 'Group ID Issue';
n_count := 0;
SELECT COUNT(*) INTO n_count FROM ETRESOURCE WHERE eres_status = 10 AND eres_group_id = 0;
UTL_SMTP.WRITE_DATA(V_MAIL_CONN, 'Group ID Issue has ' || TO_CHAR(n_count) || ' rows.' || CHR(13) || CHR(13) );
--Time Dispay Issue - Corporate Timecard
p_location_txt:= 'Time Display Issue';
n_count := 0;
SELECT COUNT(*) INTO n_count FROM AC4NPS.ETRESOURCE WHERE eres_status = 10 AND eres_tim_disp = 47;
UTL_SMTP.WRITE_DATA(V_MAIL_CONN, 'Time Display (Corporate Timecard-Do Not Use) Issue has ' || TO_CHAR(n_count) || ' rows.' || CHR(13) || CHR(13) );
--Remove oldest Duplicate Employee Type Issue
p_location_txt:= 'Remove oldest Duplicate Employee Type Issue ';
n_count := 0;
select count(*) INTO n_count from (select count(atint_ent_id) from mwebattribint
where atint_ent_id in (select Res_id from mwebres where res_status=10)
and atint_category=261
group by atint_ent_id having count(atint_ent_id)>1);
UTL_SMTP.WRITE_DATA(V_MAIL_CONN, 'Remove oldest Duplicate Employee Type Issue has ' || TO_CHAR(n_count) || ' rows.' || CHR(13) || CHR(13) );
UTL_SMTP.CLOSE_DATA(v_mail_conn);
UTL_SMTP.QUIT(v_mail_conn);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
acc4wm.log_error (p_bodid, SQLCODE, SQLERRM,p_package_txt,
p_procedure_txt,p_location_txt,p_text_txt,p_msg_code);
END;
==========================================================
can some help me on this ???
Software/Hardware used:
ASKED:
February 14, 2009 12:50 PM
UPDATED:
February 18, 2009 2:44 AM