Event Handling In Oracle Core Contracts

Independent Conditions

Events allows you to automatically initiate actions such as creating a service request, sending a notification, and creating a task as a result of something happening or before an upcoming event.

This can be achieved using Independent Conditions. Independent Conditions are used to define the Criteria for an Event and also specify the Outcome that will occur. Process Definition is definition for the Outcome that will occur for the Independent Condition. Events can be action based or Date based events

Business cases

For example we will consider two Business Cases for Different types of Process Definition

1. Procedure based Process Definition

Event: In Oracle Core contracts when the Contract status changes or Contract is terminated or Contract is signed

Action: PLSQL Code must be called to update data into the tables.

Following is the approach for creating an Independent condition that will be triggered when Contract is terminated. In the following setup we will see how this independent conditions will be associated with our PL/SQL procedure.

Setups required for creating Process Definition:

This is Setup done for Calling Procedure TEST_PKG.TEST1

 1. Enter a unique name and a description

Navigate to Setup > Contract > Process Definition

1. Enter a unique name and a description.

2. Enter Purpose: Outcome

This defines the Purpose for which the Process will be used. The other Options available for Purpose are

          Function

          Quality Assurance

          Auto Numbering

          Approve

          Approve Change Request

3. Enter Type: PLSQL as the Process will be done by PLSQL Procedure.

4. Enter Package and Procedure Name

5. Enter Parameters:

These are additional parameters that you want to pass to the Procedure. The name and data type of these parameters must be similar to those used in the Procedure.

The parameter P_API_VERSION needs to be defined, otherwise the outcome will not be called when the event occurs. Here P_CONTRACT_ID is the additional parameter that will pass the Contract id of the Contract for which the Event is triggered.

Setups required for creating Independent Condition:

This setup is done for calling the Outcome ‘TEST TERMINATED’ when the Contract status changes from ‘Signed’ to ‘Active’.

Navigate to Setup > Contract Events> Define Independent Condition

1.    Enter Name and Description

2.    Enter the Condition Type:

The condition will be Action type if the event is required to occur when certain Action e.g. Contract Signed, Contract Terminated takes Place. If the Action is time based specify Condition type as Date, Number of Days Before/After and Date.

Select Action Type condition and then select Contract Status Change as the Action. Some of the other Actions available are

                                  i.    Contract Signed

                                 ii.    Contract Terminated

                                iii.    Contract Renewed

3.    Enter the expression:

Enter the sequence, select left value and right value from the list. Enter proper brackets, Operator and And/or. The condition in screen shot is “(Contract previous status code = Signed) And (Contract current status code = Active)”.

          Press ‘Show Condition’ to Check and Validate the Condition.

4.    Enter Outcome:

It’s the name of Process Definition previously defined. Click on ‘Parameters’ select the parameters and enter the values for the parameters. Use CTRL+Click to include multiple values of Parameter.  Value of P_API_VERSION will be 1.

5.    Enter name of User to whom Notifications will be send incase of

     Failure or Success of the Outcome.

 

PLSQL Procedure for Outcome

Following are the points that one must consider while coding the PLSQL Outcome 

a)    Procedure must include mandatory parameters with sequence for parameters being the same.

         1.  p_api_version           IN  NUMBER

          2.  p_init_msg_list         IN  VARCHAR2

          3.  x_return_status        OUT VARCHAR2        

          4.  x_msg_count            OUT NUMBER

          5.  x_msg_data              OUT VARCHAR2

b)    x_return_status variable must be set to ‘S’  for ‘Success’ and ‘E’ incase of errors.

c)    OKC_API.set_message is used to set the message for notification incase of errors.

d)    Commit cannot be used in the PLSQL Procedure as the commit is done by the Workflow background Process. Using commit may cause an error.

Sample Code

PROCEDURE test(p_api_version           IN  NUMBER,

                         p_init_msg_list         IN  VARCHAR2,

                         p_contract_id           IN  NUMBER, --Optional Parameter

                         x_return_status         OUT VARCHAR2,

                         x_msg_count             OUT NUMBER,

                         x_msg_data              OUT VARCHAR2

                        )

IS

    e_exit Exception;

     

BEGIN

    /*Intialise Varaibles*/

    x_return_status            := 'S';

   

    fnd_file.put_line

             ( fnd_file.log,

               'Start of Updating Procedure');  

    BEGIN

        UPDATE test

        SET    value = 1;

    EXCEPTION

        WHEN Others

        then

           RAISE e_exit;

    END;

   

    fnd_file.put_line

             ( fnd_file.log,

               'End of Procedure');   

EXCEPTION

      WHEN e_exit

      THEN

         okc_api.set_message

                            (p_app_name          => 'OKC'

                ,p_msg_name          => 'TEST_OKC_PROCESS_FAILED' –Name of

                                                                        Message for Process Failed

                            ,p_token1            => 'PROCESS'

                            ,p_token1_value      => 'TEST_PKG.TEST'--Package.Procedure Name

                            ,p_token2            => 'MESSAGE1'

                            ,p_token2_value      =>    'Error Message is : '

                                                    || l_chr_err_msg

                            ,p_token3            => 'MESSAGE2'

                            ,p_token3_value      =>    'SQLERRM : '

                                                    || l_chr_sqlerrm

                            );

         x_return_status            := 'E';

      WHEN Others

      THEN

         write_log('Unknow SQL Error in Create Ship to Site'||SQLERRM);

         okc_api.set_message (p_app_name          => g_app_name

                              , p_msg_name          => g_unexpected_error

                              ,p_token1            => g_sqlcode_token

                              ,p_token1_value      => SQLCODE

                              ,p_token2            => g_sqlerrm_token

                              ,p_token2_value      => SQLERRM

                              );

         x_return_status            := 'E';

END;

Exception Handling in PLSQL Outcome

Incase of any exception in the Outcome a Notification will be send to the Failure Notification user defined in the Setup of Independent Condition. Incase of any unhandled exception in the code use this API

Example:

 okc_api.set_message (p_app_name          => g_app_name

                                , p_msg_name          => g_unexpected_error

                                , p_token1            => g_sqlcode_token

                                , p_token1_value      => SQLCODE

                                , p_token2            => g_sqlerrm_token

                                , p_token2_value      => SQLERRM

                                 );

 x_return_status            := 'E';

in the WHEN OTHERS expection. For this API following global variables need to be declared

   g_sqlerrm_token      CONSTANT VARCHAR2 (200)   := 'ERROR_MESSAGE';

   g_sqlcode_token      CONSTANT VARCHAR2 (200)   := 'ERROR_CODE';

   g_unexpected_error CONSTANT VARCHAR2 (200)   :=     

                                                              'OKC_CONTRACTS_UNEXPECTED_ERROR';

   g_app_name            CONSTANT VARCHAR2 (3)      := okc_api.g_app_name;

    

In order to send the Notification incase of Custom exception the same API can be used with proper messages that will be send to the user.

      

 Example:

WHEN e_exit

THEN

         okc_api.set_message(p_app_name          => 'XXDB'

                            ,p_msg_name          => 'XXDB_OKC_SITE_CREATION_FAILED'

                            ,p_token1            => 'PROCESS'

                            ,p_token1_value      => 'Auto Creation of Ship to Site for PC# '||   

                                                               l_chr_pc_number || ' Failed.  Please create '

                            ,p_token2            => 'PARTY_DETAILS'

                            ,p_token2_value      => l_chr_party_details

                            ,p_token3            => 'ADDRESS_DETAILS'

                            ,p_token3_value      => l_chr_address_details

                            ,p_token4            => 'ERROR_MESSAGE'

                            ,p_token4_value      => l_chr_err_msg

                            ,p_token5            => 'SQLERR'

                            ,p_token5_value      => NVL(l_chr_sqlerrm,'Refer message

                                                                 above.'));

         okc_api.set_message(p_app_name          => 'XXDB'

                            ,p_msg_name          => 'XXDB_OKC_SITE_CREATION_FAILED'

                            ,p_token1            => 'PROCESS'

                            ,p_token1_value      => 'Auto Creation of Ship to Site for PC# '||

                                                            l_chr_pc_number || ' Failed'

                            ,p_token2            => 'PARTY_DETAILS'

                            ,p_token2_value      => l_chr_party_details

                            ,p_token3            => 'ADDRESS_DETAILS'

                            ,p_token3_value      => l_chr_address_details

                            ,p_token4            => 'ERROR_MESSAGE'

                            ,p_token4_value      => l_chr_err_msg

                            ,p_token5            => 'SQLERR'

                            ,p_token5_value      => NVL(l_chr_sqlerrm,'Refer message

                                                            above.'));

     In the above Example the API is used two times so as to set the message at different Message levels. First API set the Message in the Notification Subject and Second API set the Message in the Notification Body.

Verification of the Setup

a)    First let the Event defined in Independent Condition occur. In

          the above defined Setup Create a Contract and the contract status  

          must change from ‘Signed’ to ‘Active’.

b)    Run the following Request

               1. Listener for Events Queue: for the Event to occur

               2. Listener for Outcome Queue: for the Outcome to be called

c)    Go to Independent Condition Setup and click ‘Occurrence Details

Button verifies whether that event has occurred.

d)    Submit the request for ‘Workflow background process’ and check

the log for this request it should show all the log messages given in the Procedure.

      e) Check the Notification send to ‘User’ specified in Independent

          Condition setup incase failure or success of Outcome procedure.

2. Workflow based Process Definition

Event: In Oracle Core contracts when the Contract status changes or Contract is expired

Action: Custom workflow must be called to send a Notification.

Following is the approach for creating an Independent condition that will be triggered when Contract is expired. In the following setup we will see how to start a workflow process from this independent Condition.

Setups required for creating Process Definition:

This is Setup done for calling workflow TEST_WF to initiate TEST_PROCESS

Navigate to Setup > Contract > Process Definition

1. Enter a unique name and a description.

2. Enter Purpose: Outcome

3. Enter Type: Workflow.

4. Enter Workflow Name and Workflow Process which will initiated when the 

    Outcome will be called.

5. Enter Parameters:

These are additional parameters that will be defined as attributes in the workflow that will be called. The name and data type of these parameters in the workflow must be similar to those defined here.

 Setups required for creating Independent Condition:

This setup is done for calling the Outcome ‘TEST1’ when the Contract status changes and Contract is expired.

          

Navigate to Setup > Contract Events> Define Independent Condition

6.    Enter Name and Description

7.    Enter the Condition Type:

      Action type and Contract Status Change

8.    Enter the expression:

      Expression will be ‘Contract current status code = Expired’.

9.    Enter Outcome:

      Enter the value as ‘TEST1’.

10. Enter Parameters:

Contract ID and Contract status with values for the parameter as

‘Contract Id’ and ‘Contract current status code’.