Configuring Database Event for Outbound Processing

In case you are using Database as a source for outbound processing, you need to use Database Event. 

Steps to create a Database Event

1. On the Adeptia Suite homepage, click the Develop tab.

2. Go to Services > Events and then click Database. The Manage Database Event screen is displayed.

   

         

3. Click the Create New link. The Create Database Event screen is displayed.

   

         

4. Enter the name and description of the new Database Event activity in the Name and Description text boxes respectively.

5. Select the Database Info Id activity from the Database Info Id drop-down list.

6. You can create the database event definition by entering an SQL Query or a database trigger command. By default, SQL Query option is selected.

   For EDI Outbound Processing you can only use SQL Query option.

7. Enter the query in the SQL Query field. For example:
   

   select * from PurchaseOrder where processingStatus='Ready' 

   Here: 
   processingStatus is a field in the source database table, which stores the status of the records. For example, If any record is already processed or not. This is important to make sure that same record should not be processed repeatedly. 
   You can use any existing field for this purpose or you can add an additional field. 
   
   In the above example, only those records, whose processingStatus is Ready, are picked for processing.

8. Select the Condition checkbox should not be enabled in case of EDI Outbound Processing.

   In case Trigger for All Record checkbox is checked, then for all the records, only one process flow is trigger and it processes all the records.

9. Enable the Execute Update Query option and enter the update query in the Update Query field. For example:
   

   update PurchaseOrder set processingStatus='%%Pass%%'

   The update query is executed for each record picked up based on select query in previous step. Database Event executed update immediately after picking up the record to update the column storing the status of the record. This ensures the records are not picked up again. For example, in the query given above, the processingStatus will be updated to Pass.

10. Enter the where condition. For example:

    where id='%%id%%'

    Where condition is used with Update Query in previous step to update only those records that satisfies this where condition. 
    It can be any column or set of columns that make the record unique. 
    In the above example id is a column in the database which uniquely identifies the records and %%id%% is value of this column in the selected record.

    Once processing of the records is completed, the status (value of processingStatus) will be updated to Processed. 
    Even if any record encounters any error while processing, their status will be changed to Processed. You have to refer EDI log to check if any record has not been processed.

11. Enter the date from which Database event will start triggering; in the Event Start Date field. The date must be in mm/dd/yyyy format. Click the Calendaricon and select the required date from the calendar.
12. Enter the start time from the Time drop-down list.
13. Enter the date on which Database event will stop triggering, in the Event Expiry Date field. The date must be in mm/dd/yyyy format. Click the Calendaricon and select the required date from the calendar.
14. Enter the expiry time from the Time drop-down list.

15. Enter the time interval, the database event checks the database Server in the Polling Frequency field. Enter the digit in the Frequency field and select the unit of time i.e. seconds, minutes or hours etc. from the Duration drop-down list.

    We recommended minimum Polling Frequency to be more than 60 seconds. 

16. Click Save. This displays a screen confirming that the database event has been created successfully. 0