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 Go to next message
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....
Re: problem in adding Html code in PLSQL procedure [message #669310 is a reply to message #669309] Sat, 14 April 2018 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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...

What does above mean?
Every Monday to Thursday from 1 AD until the end year of the Earth?
Calculate based upon what exactly?

What is expected & desired results?

http://www.orafaq.com/wiki/WHEN_OTHERS
Re: problem in adding Html code in PLSQL procedure [message #669311 is a reply to message #669309] Sun, 15 April 2018 00:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
If you don't know how to format the code, learn it using SQL Formatter.

And FEEDBACK to and THANK people who take time to help you.

Re: problem in adding Html code in PLSQL procedure [message #669323 is a reply to message #669310] Mon, 16 April 2018 00:34 Go to previous message
Djpats
Messages: 17
Registered: January 2018
Junior Member
It means From Monday 16-Apr-2018 to thursday 19-Apr-2018. Or like Same days with diffrent
dates.



Thnxxx for your replay...

[Updated on: Mon, 16 April 2018 00:36]

Report message to a moderator

Previous Topic: multiple selects after with clauses
Next Topic: divide string into multiple parts
Goto Forum:
  


Current Time: Thu Mar 28 15:09:07 CDT 2024