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
R
UN 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




2 comments:

  1. Dear Bhaskar,

    i need one help in api how to pass bind variables in the add query procedure.please send me the below mention mail.
    mahesh891077@gmail.com

    ReplyDelete
  2. Dear Bhaskar

    can you please share the package "xx_notifications_api_pkg"
    husam21@gmail.com

    BR

    ReplyDelete

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...