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](mailto:'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?
