Home » SQL & PL/SQL » SQL & PL/SQL » Problem in adding Html code in PL/SQL procedure
Problem in adding Html code in PL/SQL procedure [message #669309] |
Sat, 14 April 2018 18:21 |
Djpats
Messages: 17 Registered: January 2018
|
Junior Member |
|
|
Hi, Guys
I need to add HTML code in Pl/sql Procedure.
Blow is HTML code which I want to add in a procedure.
<html>
<style>
th, td {padding: 5px;}
th,td{text-align:center;}
</style>
<body>
<p>Dear Sir/Madam,</p>
<p style="text-align:center">Summary of Credit Note requested from Monday (DD/MM/YYY) to Thursday (DD/MM/YYYY) :</p>
<table style="width:100%">
<tr>
<th>Segment</th>
<th>Total no of Credit Notes</th>
<th>Total Value of Credit Notes (sum of CN amount)</th>
<th>Provision Amount</th>
<th>Net Impact</th>
</tr>
<tr>
<td>VGE</td>
<td>5</td>
<td>100</td>
<td>110</td>
<td>100</td>
</tr>
<tr>
<td>NC</td>
</tr>
<tr>
<td>SME</td>
</tr>
<tr>
<td>Govt</td>
</tr>
<tr>
<td>Carrier</td>
</tr>
</table><br>
<p>Please approve or return or reject the credit note request by loging in below link</p>
<p>Click on this link to login on <span><b><a href="[url]http://gmail.co.in:8000/[/url]">System</a></b></span></p>
<p style="text-align:center"><b>Note : Please do not edit the subject line of this auto generated Mail</b></p>
</body>
</html>
Below is Procedure Code:
PROCEDURE send_email_notification (p_credit_note_id IN NUMBER,
p_receiver VARCHAR2,
p_message OUT VARCHAR2)
IS
v_error VARCHAR2 (4000);
v_mail_body VARCHAR2 (4000);
l_url VARCHAR2 (150);
l_requestor VARCHAR2 (250);
l_customer_name VARCHAR2 (250);
l_circle VARCHAR2 (250);
l_business_type VARCHAR2 (10) := 'FLD';
l_priority VARCHAR2 (20);
l_credit_value NUMBER;
l_invoice_number VARCHAR2 (250);
l_remarks VARCHAR2 (250);
l_appr_remarks VARCHAR2 (250);
l_recipient VARCHAR2 (500);
l_receiver VARCHAR2 (250);
CURSOR c_role
IS
SELECT meaning user_name, attribute1 email_address
FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXVFENT_CREDIT_NOTE_APPROVERS'
AND description = P_RECEIVER
AND meaning IN (SELECT user_name
FROM apps.fnd_user
WHERE NVL (end_date, SYSDATE + 1) > SYSDATE);
v_role c_role%ROWTYPE;
BEGIN
BEGIN
SELECT meaning
INTO l_url
FROM apps.fnd_lookup_values
WHERE lookup_type = 'XXVFENT_CREDIT_NOTE_APPL_URL';
EXCEPTION
WHEN OTHERS
THEN
l_url := '[url]http://gmail.co.in:8000/[/url]';
END;
BEGIN
SELECT DECODE (NVL (description, 'NA'),
'NA', user_name,
description)
INTO l_requestor
FROM apps.fnd_user
WHERE user_id = (SELECT last_updated_by
FROM hutchcs.xxvfent_credit_note_details
WHERE crn_note_id = p_credit_note_id);
EXCEPTION
WHEN OTHERS
THEN
l_requestor := 'Requestor';
END;
BEGIN
SELECT hdr.cust_name,
hdr.cust_circle_id,
appr.crn_email_priority,
dtl.crn_open_inv_no,
dtl.crn_credit_amt,
appr.crn_email_approval_remark
INTO l_customer_name,
l_circle,
l_priority,
l_invoice_number,
l_credit_value,
l_remarks
FROM hutchcs.xxvfent_credit_note_header hdr,
hutchcs.xxvfent_credit_note_details dtl,
hutchcs.xxvfent_credit_note_email_auth appr
WHERE dtl.crn_note_id = p_credit_note_id
AND appr.crn_mail_approver_type = 'Requestor'
AND hdr.crn_note_id = dtl.crn_note_id
AND dtl.crn_note_id = appr.crn_note_id
AND hdr.crn_circuit_id = dtl.crn_circuit_id;
EXCEPTION
WHEN OTHERS
THEN
l_customer_name := 'NA';
l_circle := 'NA';
l_priority := 'Moderate';
l_invoice_number := 'NA';
l_credit_value := 0;
l_remarks := 'NA';
END;
BEGIN
SELECT appr.crn_email_approval_remark
INTO l_appr_remarks
FROM hutchcs.xxvfent_credit_note_header hdr,
hutchcs.xxvfent_credit_note_details dtl,
hutchcs.xxvfent_credit_note_email_auth appr
WHERE dtl.crn_note_id = p_credit_note_id
AND appr.crn_mail_approver_type = 'CS'
AND hdr.crn_note_id = dtl.crn_note_id
AND dtl.crn_note_id = appr.crn_note_id
AND hdr.crn_circuit_id = dtl.crn_circuit_id;
EXCEPTION
WHEN OTHERS
THEN
l_appr_remarks := 'NA';
END;
l_recipient := NULL;
OPEN c_role;
LOOP
FETCH c_role INTO v_role;
EXIT WHEN c_role%NOTFOUND;
l_recipient := l_recipient || v_role.email_address || ';';
END LOOP;
CLOSE c_role;
BEGIN
SELECT CASE
WHEN p_receiver = 'CS' THEN 'CS Team'
ELSE p_receiver
END
INTO l_receiver
FROM DUAL;
IF p_receiver = 'CS'
THEN
v_mail_body := NULL;
v_mail_body :=
END IF;
p_message := v_error;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
DBMS_OUTPUT.put_line ('Error:' || v_error);
END;
BEGIN
INSERT INTO xxvfent_email_history (sequence_no,
event,
module_name,
email_sent_to,
email_sent_cc_1,
crm_identifier,
TIMESTAMP,
created_by,
process_flag,
process_msg,
MESSAGE,
bounce_flag,
attribute1,
attribute2,
mail_body)
VALUES (
xxvfent_email_history_seq.NEXTVAL,
'Credit Note Request#'
|| p_credit_note_id
|| ' Approve/Reject Notification',
'XXVFENT_CREDIT_NOTE_PKG.SEND_EMAIL_NOTIFICATION',
l_recipient,
NULL,
'Credit Note Id: ' || p_credit_note_id,
SYSDATE,
-1,
DECODE (v_error, 0, 'SUCCESS', v_error),
NULL,
NULL,
NULL,
NULL,
NULL,
v_mail_body);
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
p_message := p_message || ' - ' || v_error;
DBMS_OUTPUT.put_line ('Error:' || v_error);
END;
EXCEPTION
WHEN OTHERS
THEN
v_error := SQLERRM;
p_message := p_message || ' - ' || v_error;
DBMS_OUTPUT.put_line ('Error:' || v_error);
END send_email_notification;
In (v_mail_body := ) variable I want to add above html code...
In a HTML body How do I calculate (from Monday (DD/MM/YYY) to Thursday (DD/MM/YYYY)), dates Using sql date function...
Any query help to calculate every Monday to Thursday...
Thank you in advance guys
any help will be appreciated....
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:40:08 CDT 2024
|