Dear Team,
I have a package where i will get the data and send as attachment through email.
Actually below is the package i was using
CREATE OR REPLACE PROCEDURE GW.SALES_EMAIL IS
BEGIN
DECLARE
REQ UTL_HTTP.REQ;
RESP UTL_HTTP.RESP;
VALUE VARCHAR2(1024);
SMSLINK VARCHAR2(1024);
TOT NUMBER ;
COUNTRIES VARCHAR2(10024);
STORES NUMBER;
STORE_NAME VARCHAR2(10024);
TRANS_DATE DATE;
MESSAGE VARCHAR2(10024) ;
REP VARCHAR2(4024);
TOS VARCHAR2(600);
CCS VARCHAR2(600);
MESSSUB VARCHAR2(5000);
MESSBODY VARCHAR2(2000);
FROMNAME VARCHAR2(100);
CURSOR SA IS
SELECT SUM (DECODE (STH_ITS_CHAR_ID, 'SALE', QCF_STH_NET_VALUE_LC, 0) -
DECODE (STH_ITS_CHAR_ID, 'SALE_RETURN', QCF_STH_NET_VALUE_LC, 0)) QCS_NET_VALUE,
W.WH_COUNTRY_REF_ID COUNTRY,
C.STH_WH_ID STORES,
W.WH_ABR_NAME STORE_NAME,
TO_CHAR(STH_DATE,'DD/MON/YYYY') TRANS_DATE
FROM SL_TRX_V C ,
INV_WAREHOUSE_V W
WHERE W.WH_ID=C.STH_WH_ID
AND W.WH_COM_ID = C.STH_COM_ID
AND C.STH_COM_ID = '1'
AND STH_STATUS_REF_NUM >= 3
AND W.WH_ID <> 102 --WAREHOUS SALE
AND STH_DATE BETWEEN TO_DATE (TO_CHAR (SYSDATE,'dd-mm-yyyy')||'00:00', 'dd-mm-yyyyhh24:mi')
AND TO_DATE (TO_CHAR (SYSDATE,'dd-mm-yyyy')||'23:59', 'dd-mm-yyyyhh24:mi')
AND NVL(C.STD_CANCEL_FLAG,0)='0'
AND NVL(C.STH_CANCEL_FLAG,0)='0'
GROUP BY W.WH_COUNTRY_REF_ID,STH_WH_ID,W.WH_ABR_NAME,TO_CHAR(STH_DATE,'DD/MON/YYYY')
ORDER BY W.WH_COUNTRY_REF_ID,STH_WH_ID;
BEGIN
SMSLINK := ''; TOT := 0;COUNTRIES := '' ; STORES := '' ;STORE_NAME := '' ;TRANS_DATE := '' ;
FOR C IN SA LOOP
COUNTRIES := COUNTRIES||C.COUNTRY||','||STORES||C.STORES||'-'||C.STORE_NAME||','||(C.QCS_NET_VALUE) || '
' ;
TOT := TOT + C.QCS_NET_VALUE;
TRANS_DATE := C.TRANS_DATE;
END LOOP;
MESSAGE := 'DATE:'||
(TRANS_DATE)||'
'||
'SALES!
Total : ' || (TOT) || '
' || COUNTRIES;
MESSBODY := MESSAGE;
MESSAGE := UTL_URL.ESCAPE(MESSAGE);
BEGIN
SEND_MAIL_MULTIPLE_USERS (P_TO => 'test@test.com',
P_FROM => 'test@test.com',
P_SUBJECT => 'Sales By Country',
P_CC => null,
P_BCC => NULL,
P_TEXT_MSG => MESSBODY,
P_ATTACH_NAME =>'Allcountries.csv',
P_ATTACH_MIME => 'text/plain',
P_ATTACH_CLOB => MESSBODY,
P_SMTP_HOST => 'MAIL.test.COM' );
END;
INSERT INTO SA_SMS_LOG(REPLY,SMESSAGE) VALUES ( REP ,MESSAGE);
COMMIT;
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
Result i am getting in excel like below :
DATE:24-APR-19 |
SALES! |
Total : 10260.4125 |
Canada | 911-City | 210.75 |
canada | 912-Srs | 388.6875 |
US | 913-KRS | 42 |
US | 914-SRK | 53.625 |
US | 915-SSK | 227.25 |
US | 916-LLK | 193.5 |
US | 917-MMI | 28.5 |
But i need the above result as with bold grid lines in excel and in email text body like below. How can i achieve this?
