Oracle Workflows

.

Run Workflow 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

Anil Passi

Add comment