Support for Oracle
For Oracle Do Not add a final ';' in the script.
If there is a final ";" or empty lines, the following error may occur:
Example for replacing Email addresses:
SELECT CUSTOMER.USER_1 AS emailAddress
FROM CUSTOMER
INNER JOIN CUSTOMER_ORDER
ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID
INNER JOIN SHIPPER
ON CUSTOMER_ORDER.ID = SHIPPER.CUST_ORDER_ID
WHERE SHIPPER.PACKLIST_ID = :PACKLIST_ID
Generating Html code to be used within the body of emails:
SELECT '<H2><font color=DarkBlue>SHIPPING CONFIRMATION</H2>' || '<H3><font color=DarkBlue>Dear ' || CUSTOMER.NAME || ', your order <font color=Red>' || CUSTOMER_ORDER.ID || '<font color=DarkBlue> has shipped.</H3>' || '<H3><font color=DarkBlue> <u> Details: </u>' || '<p> <b><font color=DarkBlue>Shipped via: </b> <font color=Red>' || NVL(CUSTOMER_ORDER.SHIP_VIA, '-') || '</p>' || '<p> <b><font color=DarkBlue>Ship to: </b> <font color=Red> ' || NVL(CUST_ADDRESS.NAME, '-') || '</p>' || '<p> <b><font color=DarkBlue>Address: </b> <font color=Red> ' || ( NVL(CUST_ADDRESS.ADDR_1, '-') || CHR(13) || CHR(10) || NVL(CUSTOMER.ADDR_2, '-') || CHR(13) || CHR(10) || NVL(CUSTOMER.ADDR_3, '-') || CHR(13) || CHR(10) || NVL(CUST_ADDRESS.CITY, '-') || CHR(13) || CHR(10) || NVL(CUST_ADDRESS.STATE, '-') || CHR(13) || CHR(10) || NVL(CUST_ADDRESS.ZIPCODE, '-') || CHR(13) || CHR(10) || NVL(CUST_ADDRESS.COUNTRY, '-') )|| '</p>' || '<br>'
|| '<p><button type=button>Track your package</button></p>'
|| '<p><font color=DarkBlue>Should you wish TO contact us FOR ANY reason please contact our sales team www.bms365.com</p>'
AS HTMLOUTPUT
FROM CUSTOMER
INNER JOIN CUSTOMER_ORDER
ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID
INNER JOIN SHIPPER
ON CUSTOMER_ORDER.ID = SHIPPER.CUST_ORDER_ID
LEFT OUTER JOIN CUST_ADDRESS
ON CUSTOMER_ORDER.CUSTOMER_ID = CUST_ADDRESS.CUSTOMER_ID
AND CUSTOMER_ORDER.SHIP_TO_ADDR_NO = CUST_ADDRESS.ADDR_NO
WHERE SHIPPER.PACKLIST_ID = :PACKLIST_ID
The code shown above will create a new column called HTMLOUTPUT which will be available from the module's grid (it can be made not visible if desired). Since the column is now available from the grid, it can be also used to build the email's subject or body. As any other field, it can be referenced by adding "@" as a prefix (see snapshot below)