Sample Code and Output- Run Work Flow API

To get a notification/email that looks similar to below, all you need to do is to call a PL/SQL API
Sample Below Desired Notification from API is shown below

To generate such Notification, call the API

STEP 1
RUN BELOW SQL and DO COMMIT;xx_dev>>SET serveroutput on;
DECLARE
  n_not_id INTEGER;
BEGIN
  xx_notifications_api_pkg.send_notification(
     x_email_address       => 'PASSIA'
    ,x_user_name           => ''
    ,x_notification_api_id => n_not_id
    ,x_message_type        => 'TEXT_AND_QUERY'
    ,x_process_short_code  => 'DEMO-'
    ,x_message_subject     => 'ANIL TESTING ANOTHER ONE Customer Run Id 100329'
    ,x_message_text        => 'ANIL Customer Customers in AR on ' ||
    to_char(SYSDATE,'DD-Mon-RRRR HH24:MI'));

  xx_notifications_api_pkg.add_query(
     x_notification_api_id => n_not_id
    ,x_query_title_text    => 'List of TCA Customers Created During Last 24Hrs'
    ,x_from_clause         => 'hz_parties'
    ,x_where_clause        => 'creation_date > sysdate - 1'
    ,x_bind_values         => NULL
    ,x_column_title_1      => 'Party Number'
    ,x_column_name_1       => 'PARTY_NUMBER'
    ,x_column_title_2      => 'Creation Date'
    ,x_column_name_2       => 'CREATION_DATE'
    ,x_column_title_3      => 'Org System Ref'
    ,x_column_name_3       => 'orig_system_reference');

  xx_notifications_api_pkg.add_query(
     x_notification_api_id => n_not_id
    ,x_query_title_text    => 'Second List of TCA Parties in Last 48Hrs'
    ,x_from_clause         => 'hz_parties'
    ,x_where_clause        => 'creation_date > sysdate -2'
    ,x_bind_values         => NULL
    ,x_column_title_1      => 'Party Number'
    ,x_column_name_1       => 'PARTY_NUMBER'
    ,x_column_title_2      => 'Party Name'
    ,x_column_name_2       => 'PARTY_NAME'
    ,x_column_title_3      => 'Org System Ref'
    ,x_column_name_3       => 'orig_system_reference');
  dbms_output.put_line('n_not_id: ' || n_not_id);COMMIT ;
END;
/
The serveroutput displays message below
n_not_id: 1000

PL/SQL procedure successfully completed.


STEP 2
RUN BELOW THE BACKGROUND PROCESS AS BELOW
Ideally this will be scheduled to run every 15minutes or so on Production.
Hence notifications will be sent out every 15minutes by email/worklist

Step 3.
Item Key used by this wf api internally will use value in parameter x_process_short_code concatenated with Reference Number returned.
If you wish to send notification related to PO Number 1032, then pass parameter x_process_short_code=>'PO-Num-1032'

Issue:- If concurrent manager is not running then start this using command below

adcmctl.sh start apps appspassword