E9.2 Prepare a message body with a list of sales order data retrieve from a database connector

[skwong]

I am preparing an orchestration to report which sales orders are created today and send an email (message) to a designated person. I am using a database connector with jruby to execute a select statement to return a list of sales order document company, document type, order number, customer, customer name.

"SELECT SHKCOO, SHDCTO, SHDOCO, SHAN8, ABALPH FROM F4201 INNER JOIN F0101 ON SHAN8 = ABAN8
WHERE SHTRDJ = TO_NUMBER(to_char(sysdate, 'YYYYDDD')) - 1900000"

I want to save the result into a message body like below:

These are the sales orders created today:

00001 SO 3450 10001 CUSTOMER 1
00001 SO 3451 10002 CUSTOMER 2
00001 SO 3452 10003 CUSTOMER 3

Is there a simple jruby codes to generate the text like the above list table? I am new to jruby development.

 

[Kevin Long]

Have a look at Oracle Support document Doc ID 2376983.1. You can use Ruby to generate HTML of a table with the data you would like to include in the Message. You just need to return the html from your database connector and pass it into the body of message.

 

[skwong]

Hi Kevin,

Have a look at Oracle Support document Doc ID 2376983.1. You can use Ruby to generate HTML of a table with the data you would like to include in the Message. You just need to return the html from your database connector and pass it into the body of message.

I am still with 9.2.6 and I could not import the zip file successfully.

What is the trick to make an orchestration output as a string for the input of the custom function?

For example, the orchestration output is like:
{
"responses": "200",
"Exceptions": [
{
"MSGS": "EAI ID: 1, STATUS: 400, MESSAGE: Successful"
},
{
"MSGS": "EAI ID: 2, STATUS: 400, MESSAGE: Successful"
},
{
"MSGS": "EAI ID: 3, STATUS: 400, MESSAGE: Successful"
},
{
"MSGS": "EAI ID: 4, STATUS: 400, MESSAGE: Successful"
},
{
"MSGS": "EAI ID: 5, STATUS: 400, MESSAGE: Vendor invalid"
}
]
}

 

[skwong]

I tried the feature 'return raw output' and it works fine now. Thanks.