Translate

Montag, 10. September 2018

EMAIL_TEMPLATE - how it works

an interesting feature of APEX 18.2 (and before...) is the EMAIL_TEMPLATE; it can be found under "Shared components" --> "other components"

The Email_template consists of 2 parts:

Part 1) The template details
divided into:
- header
- body
- footer
with HTML code content.

By means of a smart click (right hand side region) using "Sample Templates"
--> Get started by loading sample email Templates...
- you can load the HTML code data for the generation of an invoice... :

























The hashtags like  #CUSTOMER_NAME# are substitions we know from other places within APEX.

Once we have defined the Email_template and adjusted the HTML for formatting we like to use it.... How ?

Part 2) pl/sql procedure

On the template_details page (as above) we will find a TAB called: "Sample API Usage" which shows the pl/sql we have to use to map the fields from a page to the hashtag substitions:

begin
    apex_mail.send (
        p_to                 => email_address_of_user,
        p_template_static_id => 'EXAMPLE01',
        p_placeholders       => '{' ||
        '    "CUSTOMER_NAME":'           || apex_json.stringify( :P1_CUST_NAME ) ||
        '   ,"ITEMS_ORDERED":'           || apex_json.stringify( :P1_ITEMS_ORD ) ||
        '   ,"MY_APPLICATION_LINK":'     || apex_json.stringify( some_value ) ||
        '   ,"ORDER_DATE":'              || apex_json.stringify( some_value ) ||
        '   ,"ORDER_NUMBER":'            || apex_json.stringify( some_value ) ||
        '   ,"ORDER_TOTAL":'             || apex_json.stringify( some_value ) ||
        '   ,"ORDER_URL":'               || apex_json.stringify( some_value ) ||
        '   ,"SHIPPING_ADDRESS_LINE_1":' || apex_json.stringify( some_value ) ||
        '   ,"SHIPPING_ADDRESS_LINE_2":' || apex_json.stringify( some_value ) ||
        '   ,"SHIP_TO":'                 || apex_json.stringify( some_value ) ||
        '}' );
end;

You just have to apply your fields from the page to "some_value".... as above.

So on your page you have to create a region of type pl/sql and insert above code... Create a Button and run the process by means of clicking the button.

The output is an HTML formatted email.

I did it even in way: The customer enters his data and clicks on "CREATE" (order or s.th.) and the data is stored in the database AND in parallel an order confirmation - email is sent to the customer.

that's it!
 

APEX 4.2, 5.1, 18,2 - send email from an APEX application

again and again the question is asked: how to send an email from an APEX application ?

sorry - english, because this is an answer to a question in the APEX community forum.

I answered:

1: Pls kindly read the user guide...:
https://docs.oracle.com/database/apex-18.1/HTMDB/sending-email-from-an-application.htm#HTMDB13006

(The whole functionality is based on the APEX_MAIL API)

2. in short:

A) from within a page/form - add procedure of type "Send_email" and use substitutions/placeholders like  &P1_NAME. or  &P1_EMAIL. in the UI properties... - this is one email as per page/data





























B) loop thru records of a table and send emails to those records like: - this is: several emails in a row....

  1. FOR m IN (SELECT VORNAME, NACHNAME, EMAIL  
  2.             FROM TABLE  
  3. Where email is not null  
  4.           and status = 'Aktiv'  
  5. and ABTEILUNG like '%'||:P3_ABTEILUNG  
  6.           )  
  7. LOOP  
  8.   apex_mail.send( p_to => m.EMAIL,  
  9.                   p_from => 'info@my_email.de',  
  10.                   p_bcc => 'info2@my_2nd_email.com',  
  11.                   p_body => 'Hallo '||m.vorname||','||chr(10)||chr(10)||:P3_TEXT||chr(10)||  
  12.                 :P3_SIGNATURE                 
  13.                 ,  
  14.                   p_subj => :P3_SUBJECT  
  15.                 );  
  16. end loop;  

:P3_TEXT, :P3_SIGNATURE, :P3_SUBJECT and :P3_ABTEILUNG are names of fields in page 3 - in SQL and pl/sql these types of substitutions are used... in the Procedure re A) "Send_email" UI we use substitutions of type: &P3_NAME.


APEX 5.1/18.2 - Update several rows in a tabular form / GRID

APEX 5.1 - 18.2

this is to answer a thread from the APEX community forum, therefore in English - sorry:

Question: How to update several rows in a tabular form:

I answered:

In 18.2 recommend to use the interactive grid... (tabular form is legacy...)




the grid has an inbuilt row-selector - even select-/deselect-all....

Create a region of type "interactive grid" and enable it to edit. - then...
steps:
1) create a button and name it "update STATUS rows"
2) create a dynamic action and name it "update_status_rows_DA" of type (WHEN...) Event: click - type: Button -
3) add a true action - action: Execute Javascript code
4) settings/code:

  1. var record;  
  2. //Identify the particular interactive grid  
  3. var ig$     = apex.region("my_region_name").widget();  
  4.   
  5. //Fetch the model for the interactive grid  
  6. var grid    = ig$.interactiveGrid("getViews","grid");  
  7.   
  8. //Fetch the model for the interactive grid  
  9. var model   = ig$.interactiveGrid("getViews","grid").model;  
  10.   
  11. //Fetch selected records  
  12. var selectedRecords = apex.region("my_region_name").widget().interactiveGrid("getViews","grid").view$.grid("getSelectedRecords");  
  13.   
  14. //Loop through selected records and update value of the STATUS column  
  15. for (idx=0; idx < selectedRecords.length; idx++)   
  16. {  
  17. //Get the record  
  18. record = model.getRecord(selectedRecords[idx][0]);  
  19.   
  20. // set Value von column STATUS on "VALID"     
  21. model.setValue(record,"STATUS"'VALID');  
  22. // optional...  
  23. model.setValue(record,"ROW_AMENDED_BY"'&APP_USER.');  
  24. model.setValue(record,"ROW_AMENDED_DATE"'&P14_DATE.');  
  25.        }  

execution options:
Event: Update_rows_DA
Fire when event result is: TRUEFire on initialisation: NO