Skip to content

Sync Data Between Applications Automatically

Abstract

Introduction

Check the Requirements

Check the What You Should Know and What Permissions You Need? section to verify if you have the knowledge and permissions required to complete this tutorial.

Some eLegere workflows may require to keep updated data between two applications.

Example

Business processes requiring data sync or updates between eLegere applications:

  • Adding a project's row in a project management application updates a monthly budget's row in a Budget application.
  • Updating the worked hours in the application calculating paychecks after completing an activity in a task management application.
  • Syncing information between two applications in two eLegere Domains.

This tutorial considers the following use case common to different circumstances: You have two applications and their SQL tables. Changes in the first application's table trigger changes in the second application's table.

Example

The tutorial considers the following application as the use case to show how to write a Stored Procedure.

Two applications manage purchases from an e-commerce site: Warehouse Orders and Warehouse Stock.

Warehouse Orders records the customers' orders and shipment from the warehouse.

Each Master row in Warehouse Orders is a shipment with a Detail table called "Order Items". "Order Items" lists all the requested products and their quantity.

Warehouse Stock catalogues the products and the stock available. If a product is out of stock, the application highlights its row and sends a notification to restock it.

When users add items to "Order Items" Detail table in Warehouse Orders , then they must update the items' availability in Warehouse Stock manually. (The same happens when users remove an item from an order.) The Designer should find a way to sync the two applications automatically to avoid waste of time.

In this tutorial, you will:

  1. Access the database and launch the Stored Procedure in the Configuring the Stored Procedures in the Database paragraph.
  2. Prepare the Action in the application's Design Mode to automate the execution.

Info

Each section in Configuring the Stored Procedures in the Database describes the Stored Procedure step by step through pseudocode. Code snippets of each part accompany the explanation. At the bottom of each section you can find the whole SQL Stored Procedure ready to be copied & pasted.

What You Should Know and What Permissions You Need?

Read the required list of skills, database permissions in your current eLegere installation, and database configurations .

If you satisfy the requirements below, you can undertake this tutorial.

Skills

This tutorial gives for granted the following skills:

  • Transact-SQL The eLegere database engine is Transact SQL. The level is Intermediate and the tutorial requires knowledge of:
    • CREATE PROCEDURE operation
    • ALTER PROCEDURE operation
    • Flow Control Statements
    • Parameters
    • Variables
    • JSON objects integration
  • Stored Procedures in eLegere and how they work. See Stored Procedures for more information.

Permissions

This tutorial requires:

  • Databases permissions from your DBA: CREATE PROCEDURE.
  • Account permission Application Designer in eLegere. (See What are the Design Tasks? for more information about Design permissions.)

Contact your administrator in case you lack either the Databases permissions or the eLegere Application Designer permission.

Database Configurations

Warning

The Stored Procedures require the F_APP_DATETIME2BIGINT function to work. Be sure to add it to the database if missing.

The Stored Procedures employ the function F_APP_DATETIME2BIGINT. F_APP_DATETIME2BIGINT converts a DateTime value into date expressed as a 14-digit number compatible with eLegere. During the eLegere installation, you DBA should have added it.

Add F_APP_DATETIME2BIGINT to the user-defined functions in case the database hasn't it.

You find the code below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Be sure to name the function F_APP_DATETIME2BIGINT.

USE [SMARTINTELLIGENCE]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [elegere].[F_APP_DATETIME2BIGINT](@dtDateTime datetime) 
RETURNS BIGINT
AS
BEGIN
DECLARE @biDateTime AS BIGINT

     SET @biDateTime = DATEPART(second, @dtDateTime) +
        DATEPART(minute, @dtDateTime) * 100 +
        DATEPART(hour, @dtDateTime) * 10000 +
        DATEPART(day, @dtDateTime) * 1000000 +
        DATEPART(month, @dtDateTime) * 100000000 +
        DATEPART(year, @dtDateTime) * 10000000000

    RETURN(@biDateTime)
END


GO

Configuring the Stored Procedures in the Database

This section explains each Stored Procedure that you must configure in the database.

You must first configure the Stored Procedures in the eLegere's database before the eLegere application.

Note

When a user with Application Management permission (see What are the Design Tasks? for more information) creates an application, eLegere inserts the following empty Stored Procedures in the database:

  • SP_Application_Name_PRE_INSERT
  • SP_Application_Name_POST_INSERT
  • SP_Application_Name_PRE_UPDATE
  • SP_Application_Name_POST_UPDATE
  • SP_Application_Name_PRE_DELETE
  • SP_Application_Name_POST_DELETE

Insert the code according to the role in the corresponding Stored Procedure. For instance, paste PRE INSERT in SP_Warehouse_Management_Warehouse_Orders_PRE_INSERT.

The Stored Procedures' Flow in the Application subsection describes the Stored Procedures' workflow and when the application launch them and why.

The following subsections explain with comments each Stored Procedure to be configured in the database for each case.

The Stored Procedures' Flow in the Application

This subsection describes the overall workflow in which each Stored Procedure edit data.

The flowchart below summarizes how each Stored Procedures work in the Warehouse Orders use case. Expand to view it.

Stored Procedures Flowchart 
---
title: Stored Procedures Flowchart
---
flowchart TD
    begin[[BEGIN]] --> choice1{Is the user adding products to Order Items?}
    choice1-- Yes -->preinsert[The PRE INSERT Stored Procedure checks \nif there are enough product's units in stock]
    preinsert --> choice2{Are there enough units in stock?}
    choice2-- Yes --> newrow[The application creates the new row]
    choice2-- No -->abort[The application blocks the row's creation.\nInforms the user about the reason]
    abort-->ending[[END]]
    newrow --> postinsert[The POST INSERT Stored Procedure updates \nthe units in stock after the application saves the new row]
    postinsert --> ending
    choice1-- No -->choice3{Is the user updating \na product's quantity in an order?}
    choice3-- Yes --> preupdate[The PRE UPDATE Stored Procedure checks \nif there are enough product's units in stock]
    preupdate --> choice4{Are there enough units in stock?}
    choice4-- Yes --> updatedrow[The application updates the Quantity fields]
    choice4-- No --> abort
    updatedrow --> postupdate[The POST UPDATE Stored Procedure updates \nthe units in stock after the application saves the new row]
    postupdate --> ending
    choice3 -- No --> delete{The user deletes \na product from an order or an entire order}
    delete --> predelete[The PRE DELETE updates the products' quantity]
    predelete --> deletion[The application deletes the rows]
    deletion-->ending

The key requirement is updating a product quantity in Warehouse Stock every time the user saves any change in Warehouse Orders-

A change in a product quantity can happen in four different moments:

  1. When the user in Warehouse Orders adds a product to the Detail table Order Items.
  2. When the user in Warehouse Orders removes a product to the Detail table Order Items.
  3. When the user in Warehouse Orders changes a product's quantity in the Detail table Order Items.
  4. When the user deletes a whole order's row from Warehouse Orders and its Detail table.

Each Stored Procedure covers one the cases 1-4 above.

  • The PRE INSERT and POST INSERT Stored Procedure update the quantities in case 1.
  • The PRE UPDATE and POST POST UPDATE Stored Procedure update the quantities in case 2.
  • The PRE DELETE updates the product quantities in case 3 and 4.

Why There Isn't a POST DELETE Stored Procedure?

A POST DELETE Stored Procedure is pointless for two reasons:

  • The PRE DELETE updates the product's quantity before the deletion.
  • The application would run a Stored Procedure on a non-existent row that the user has just deleted.

The Stored Procedure Before Saving a New Row

Follows the explanation of the Stored Procedure that the application will run before saving a new row in Order Items Detail table.

Step 1

The Stored Procedure begins by creating the procedure and declaring all the mandatory parameters that eLegere requires for SQL. (See Stored Procedures for more information about the parameters.)

Note

In eLegere you can retrieve the information about rows to be saved through the JSON object.

The JSON object must be passed to the Stored Procedure as value for the @jsonParameters. 

1
2
3
4
5
{
"Item": {},
"StorageName": "[SQL TABLE NAME]",
"UserId": "j.doe@vesenda.com",
}
  • The Item key in the JSON object contains the row's object with all the keys and values containing its information.
  • The StorageName key contains the eLegere Storage's SQL table Name. The name (i.e. [SQL TABLE NAME]) is the Storage's physical name in eLegere. Corresponds to the table where the user is performing the action.
  • UserId is the logon's Username.

Refer to the object "Item" within the JSON to obtain the new row's field values. Use the following command to open the new row's JSON object:

1
OPENJSON(@jsonParameters, '$.Item' )

Refer the "Item" value through the JSON_VALUE() function. It's useful when you use SET or SELECT commands.

1
JSON_VALUE(@jsonParameters, '$.Item.FIELD_NAME')

Store Procedures can use the "StorageName" key value to run queries and actions according to the table. The current use case employs quite often the "StorageName" to verify conditions.

See Stored Procedures for more information about the SQL Parameters for eLegere.

Code 
1
2
3
4
5
CREATE PROCEDURE [whrman].[SP_Warehouse_Management_Warehouse_Orders_PRE_INSERT]
        @jsonParameters NVARCHAR(MAX)
         , @OutCodeStatus NVARCHAR(4000) OUTPUT
         , @OutLevel NVARCHAR(4000) OUTPUT
         , @OutMessage NVARCHAR(4000) OUTPUT

Step 2

The next step is declaring the variables. There are two groups of variables:

  • Variables to record the tables' names.
  • Variables to record the new row's field values.

You need to declare variables to calculate the time when the new row will be recorded.

Code 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
DECLARE @ApplicationName            NVARCHAR(4000)
DECLARE @ApplicationMasterStorage   NVARCHAR(4000)
DECLARE @ApplicationDetailStorage   NVARCHAR(4000)
DECLARE @ActionTargetStorage        NVARCHAR(4000)

--------------

DECLARE @Id                     INT
DECLARE @ProductOrder           INT
DECLARE @QuantityOrder          INT
DECLARE @QuantityStock          INT

DECLARE @USERID_CREATION        NVARCHAR(4000)
DECLARE @USERID_LASTUPDATE      NVARCHAR(4000)
DECLARE @DT_CREATION            BIGINT
DECLARE @UPDATE_DATE            BIGINT
DECLARE @TIMESTAMP              VARBINARY(4)

DECLARE @Today AS BIGINT
DECLARE @dtToday AS DATETIME

Step 3

After declaring the variables, set their values. The Store Procedures assigns a value for the tables' names since they're required in the following part. It uses the GETDATE() function to obtain the current DateTime and convert it in a Numeric format. The procedure at step 4 assigns the converted date as Timestamp for the row.

Code 
1
2
3
4
5
6
7
SET @dtToday = GETDATE()

SET @Today = [dbo].F_APP_DATETIME2BIGINT( @dtToday )

SET @ApplicationName = 'Warehouse Orders'
SET @ApplicationMasterStorage = 'TBL_WAREHOUSE_ORDERS'
SET @ApplicationDetailStorage = 'TBL_ORDER_ITEMS'

Step 4

The procedure opens the JSON Object that the procedure receives for the @jsonParameters and assigns the values to the variables.

Next, the procedure runs the first check: verifying that the application is saving in the Order Items table. If the table corresponds to Order Items's table TBL_ORDER_ITEMS, the procedure goes for the second check before allowing the saving process. Otherwise, nothing happens.

If the table name's from the JSON object is TBL_ORDER_ITEMS, the Store Procedures starts the second check. The second check verifies the product units in Warehouse Stock table TBL_PRODUCTS. Records the '$.Item.Quantity' key's value from the "Items" object to compare it with the WarehouseQuantity field. If '$.Item.Quantity' value is higher than the WarehouseQuantity's value for the product on TBL_PRODUCTS, the procedure blocks the saving process.

If the procedure blocks the new row's saving process, it reports a notification error to the user.

Code 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

IF @ActionTargetStorage = @ApplicationDetailStorage
    BEGIN 
        SELECT
            @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )             
            , @QuantityOrder = JSON_VALUE(@jsonParameters, '$.Item.Quantity' )
            , @ProductOrder = JSON_VALUE(@jsonParameters, '$.Item.Product' )

            , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
            , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
            , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
            , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
            , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
        FROM 
            OPENJSON(@jsonParameters, '$.Item' )

    SELECT @QuantityStock = WarehouseQuantity
    FROM [whrman].[TBL_PRODUCTS]
    WHERE IdProduct = @ProductOrder

    IF (@QuantityOrder > @QuantityStock)
    BEGIN
        SET @OutCodeStatus = '000500'
        SET @OutMessage = 'The requested quantity exceeds the current product stock.'
        SET @OutLevel = 'Error'
        RETURN -1

    END


END

PRE INSERT

Copy the content below in the SP_Warehouse_Management_Warehouse_Orders_PRE_INSERT Stored Procedure.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
-- Creates the procedure declaring the parameters that eLegere requires
CREATE PROCEDURE [whrman].[SP_Warehouse_Management_Warehouse_Orders_PRE_INSERT]
          @jsonParameters NVARCHAR(MAX)
        , @OutCodeStatus NVARCHAR(4000) OUTPUT
        , @OutLevel NVARCHAR(4000) OUTPUT
        , @OutMessage NVARCHAR(4000) OUTPUT  
AS 
BEGIN 
    -- Variables declaration

    /* 
        The variables will record info regarding the 
        tables involved in the databases.
    */

    DECLARE @ApplicationName            NVARCHAR(4000)
    DECLARE @ApplicationMasterStorage   NVARCHAR(4000)
    DECLARE @ApplicationDetailStorage   NVARCHAR(4000)
    DECLARE @ActionTargetStorage        NVARCHAR(4000)

    --------------

    -- The following variables record the fields values

    DECLARE @Id                     INT
    DECLARE @ProductOrder           INT
    DECLARE @QuantityOrder          INT
    DECLARE @QuantityStock          INT

    DECLARE @USERID_CREATION        NVARCHAR(4000)
    DECLARE @USERID_LASTUPDATE      NVARCHAR(4000)
    DECLARE @DT_CREATION            BIGINT
    DECLARE @UPDATE_DATE            BIGINT
    DECLARE @TIMESTAMP              VARBINARY(4)

    DECLARE @Today AS BIGINT
    DECLARE @dtToday AS DATETIME


    --------------

    -- Sets the current system date as @dtToday value

    SET @dtToday = GETDATE()

    /* 
        Converts the DateTime value to integer format.
        Assigns the converted value to the @Today variable.
    */
    SET @Today = [dbo].F_APP_DATETIME2BIGINT( @dtToday )

    -- Assigns the table names on the database to the variables

    SET @ApplicationName = 'Warehouse Orders'
    SET @ApplicationMasterStorage = 'TBL_WAREHOUSE_ORDERS'
    SET @ApplicationDetailStorage = 'TBL_ORDER_ITEMS' 

    /*
        Assigns the value to the @ActionTargetStorage
        The assignment employs the row's context: checks the Storage's name 
        from the JSON containing the data to save.
        - If the Storage Name is null, the user has saved a row in the Master table. 
        It registers the @ApplicationMasterStorage value for @ActionTargetStorage.
        - If the Storage Name IS NOT null, the user has saved the row on the Detail. 
        The procedure records the Detail table's name value from the JSON.
    */

    SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

    /*  
        The procedure confronts @ActionTargetStorage and @ApplicationDetailStorage.
        If the table names are the same, the user has added a row to 
        the Order Items Detail table.
        In this case, the procedure checks if 
        the requested quantity is available in stock.
        Otherwise, the procedure is over.
    */

    IF @ActionTargetStorage = @ApplicationDetailStorage
    BEGIN 

    /* 
        The JSON contains the object Item. Item has all the row's fields and values.
        The procedure retrieves all the required values from Item.
        Retrives the Product information starting from the product's Id from the JSON.
    */
        SELECT
              @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )               
            , @QuantityOrder = JSON_VALUE(@jsonParameters, '$.Item.Quantity' )
            , @ProductOrder = JSON_VALUE(@jsonParameters, '$.Item.Product' )

            , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
            , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
            , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
            , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
            , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
        FROM 
            OPENJSON(@jsonParameters, '$.Item' )

        -- Retrives the product's quantity value from the Warehouse Stock's table
        SELECT @QuantityStock = WarehouseQuantity
        FROM [whrman].[TBL_PRODUCTS]
        WHERE IdProduct = @ProductOrder

        -- Compares the user's inserted quantity and the product's quantity from Warehouse Stock.
        IF (@QuantityOrder > @QuantityStock)
        BEGIN
            --- In case the quantity is too low, the procedure blocks the save and warns the user with a notification.
            SET @OutCodeStatus = '000500'
            SET @OutMessage = 'The requested quantity exceeds the current product stock.'
            SET @OutLevel = 'Error'
            RETURN -1

        END


    END


END
GO

The Stored Procedure After Saving the New Row

Follows the explanation of the Stored Procedure that the application will run after saving a new row in Order Items Detail table.

Step 1

This step is identical to Step 1 from The Stored Procedure Before Saving a New Row: creating the procedure and declaring the mandatory parameters that eLegere requires.

Step 2

This step is identical to Step 2 from The Stored Procedure Before Saving a New Row: declaring the variables.

Step 3

This step is identical to Step 3 from The Stored Procedure Before Saving a New Row: setting the variables' values for the record.

Step 4

The performed action differs from the PRE INSERT.

At this step, the procedure updates the product's stock after the row is saved in Warehouse Orders. At this stage, the PRE INSERT Stored Procedure has already verified that there is enough stock and didn't block the saving process.

The query retrieves the selected product's row on the Warehouse Stock table TBL_PRODUCTS.

Then the procedure updates the entry on the table for WarehouseQuantity field.

Each product in Warehouse Stock has a Boolean field called isAvailable. If the product's quantity goes to 0, the procedure changes the isAvailable Boolean field from true to false. If isAvailable is false, the users in Warehouse Orders can't choose that product for an order.

Code 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
IF @ActionTargetStorage = @ApplicationDetailStorage
BEGIN 

    SELECT
          @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )
        , @QuantityOrder = JSON_VALUE(@jsonParameters, '$.Item.Quantity' )
        , @ProductOrder = JSON_VALUE(@jsonParameters, '$.Item.Product' )

        , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
        , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
        , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
        , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
        , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
    FROM 
        OPENJSON(@jsonParameters, '$.Item' )

    UPDATE PROD
    SET PROD.WarehouseQuantity = PROD.WarehouseQuantity - @QuantityOrder
        , PROD.DT_LAST_UPDATE = @UPDATE_DATE
        , PROD.USER_ID_LAST_UPDATE = @USERID_LASTUPDATE
    FROM [whrman].[TBL_PRODUCTS] AS PROD
    WHERE PROD.IdProduct = @ProductOrder

    UPDATE [whrman].[TBL_PRODUCTS]
    SET IsAvailable = 0
        , DT_LAST_UPDATE = @UPDATE_DATE
        , USER_ID_LAST_UPDATE = @USERID_LASTUPDATE
    WHERE IdProduct = @ProductOrder AND WarehouseQuantity = 0


    END


END

POST INSERT

Copy the content below in the SP_Warehouse_Management_Warehouse_Orders_POST_INSERT Stored Procedure.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
-- Creates the procedure declaring the parameters that eLegere requires
CREATE PROCEDURE [whrman].[SP_Warehouse_Management_Warehouse_Orders_POST_INSERT]  
          @jsonParameters NVARCHAR(MAX)
        , @OutCodeStatus NVARCHAR(4000) OUTPUT
        , @OutLevel NVARCHAR(4000) OUTPUT
        , @OutMessage NVARCHAR(4000) OUTPUT  
AS 
BEGIN 
    -- Variables declaration

    /* 
        The variables will record info regarding the 
        tables involved in the databases.
    */

    DECLARE @ApplicationName            NVARCHAR(4000)
    DECLARE @ApplicationMasterStorage   NVARCHAR(4000)
    DECLARE @ApplicationDetailStorage   NVARCHAR(4000)
    DECLARE @ApplicationDetailStorage2  NVARCHAR(4000)
    DECLARE @ActionTargetStorage        NVARCHAR(4000)

    --------------

    /* 
        The following variables record the updated rows'
        fields' values. 

        @Today and @dtToday record the date in Numeric format and DateTime values. 
        The Stored Procedure requires @Today and @dtToday to update 
        the DT_CREATION field.
    */

    DECLARE @Id                     INT
    DECLARE @ProductOrder           INT
    DECLARE @QuantityOrder          INT

    DECLARE @USERID_CREATION        NVARCHAR(4000)
    DECLARE @USERID_LASTUPDATE      NVARCHAR(4000)
    DECLARE @DT_CREATION            BIGINT
    DECLARE @UPDATE_DATE            BIGINT
    DECLARE @TIMESTAMP              VARBINARY(4)


    DECLARE @Today AS BIGINT
    DECLARE @dtToday AS DATETIME

    --------------  

    -- Sets the current system date as @dtToday value
    SET @dtToday = GETDATE()

    /* 
        Converts the DateTime value to integer format.
        Assigns the converted value to the @Today variable.
    */

    SET @Today = [dbo].F_APP_DATETIME2BIGINT( @dtToday )

    -- Assigns the table names on the database to the variables.

    SET @ApplicationName = 'Warehouse Orders'
    SET @ApplicationMasterStorage = 'TBL_WAREHOUSE_ORDERS'
    SET @ApplicationDetailStorage = 'TBL_ORDER_ITEMS' 

    /* 
        Assigns the value to the @ActionTargetStorage.
        The assignment employs the row's context: checks the Storage's name 
        from the JSON containing the data to save.
        - If the Storage's name is null, the user has saved a row in the Master table.
        It registers the @ApplicationMasterStorage value for @ActionTargetStorage.
        - If the Storage's nameame IS NOT null, the user has saved the row on the Detail.
        The procedure records the Detail table's name value from the JSON.
    */

    SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

    /* 
        The procedures confronts @ActionTargetStorage and @ApplicationDetailStorage
        If the table names are the same, the user has added a row to the Order Items Detail table.
        In such case, the procedure updates Warehouse Stock.
        Otherwise, the procedure is over. 
    */

    IF @ActionTargetStorage = @ApplicationDetailStorage
    BEGIN 

    /* 
        Retrives the Product information starting from the product's Id from the JSON.
        The JSON contains the object Item. Item has all the row's fields and values.
        The procedure retrieves all the required values from Item. 
    */

        SELECT
              @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )
            , @QuantityOrder = JSON_VALUE(@jsonParameters, '$.Item.Quantity' )
            , @ProductOrder = JSON_VALUE(@jsonParameters, '$.Item.Product' )

            , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
            , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
            , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
            , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
            , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
        FROM 
            OPENJSON(@jsonParameters, '$.Item' )

        /* 
            The query updates the Warehouse Stock table with the new 
            quantity, date of update, and who has updated the quantity.  

            PROD is the alias assigned to Warehouse Stock's table (TBL_PRODUCTS).

            Also, updates DT_LAST_UPDATE and USER_ID_LAST_UPDATE fields. 

            DT_LAST_UPDATE records the latest update's date.

            USER_ID_LAST_UPDATE records the latest account that edited the row.
        */

        UPDATE PROD
        SET PROD.WarehouseQuantity = PROD.WarehouseQuantity - @QuantityOrder
            , PROD.DT_LAST_UPDATE = @UPDATE_DATE
            , PROD.USER_ID_LAST_UPDATE = @USERID_LASTUPDATE
        FROM [whrman].[TBL_PRODUCTS] AS PROD
        WHERE PROD.IdProduct = @ProductOrder

        /* 
            Each product in TBL_PRODUCTS has a Boolean field called IsAvailable.

            If isAvailable is false, the users in Warehouse Orders can't choose the product for an order. 

            When a product has the value 0 for the WarehouseQuantity field, 
            the procedure changes the isAvailable's value. 

            Also, updates DT_LAST_UPDATE and USER_ID_LAST_UPDATE fields.
        */

        UPDATE [whrman].[TBL_PRODUCTS]
        SET IsAvailable = 0
            , DT_LAST_UPDATE = @UPDATE_DATE
            , USER_ID_LAST_UPDATE = @USERID_LASTUPDATE
        WHERE IdProduct = @ProductOrder AND WarehouseQuantity = 0


    END


END
GO

The Stored Procedure Before Saving the Changes to a Row

Follows the explanation of the Stored Procedure that the application will run before saving any change to existing rows in Order Items Detail table.

Step 1

This step is identical to Step 1 from The Stored Procedure Before Saving a New Row: creating the procedure and declaring the mandatory parameters that eLegere requires.

Step 2

This step is identical to Step 2 from The Stored Procedure Before Saving a New Row: declaring the variables.

Step 3

This step is identical to Step 3 from The Stored Procedure Before Saving a New Row: setting the variables' values for the record.

Step 4

The procedure opens the JSON Object that the procedure receives for the @jsonParameters and assigns the values to the variables.

Then proceed with the first check: verifying that the table in which the application is saving is the Order Items table. If the table corresponds to Order Items's table TBL_ORDER_ITEMS, the Procedure proceeds with the second check before allowing the saving process. Otherwise, nothing happens.

If the table name's from the JSON object is TBL_ORDER_ITEMS, the Store Procedures starts the second check. The second check verifies the product units in Warehouse Stock table TBL_PRODUCTS.

The process for the second check changes from the PRE INSERT.

In the PRE UPDATE, the procedure executes a JOIN between TBL_ORDER_ITEMS and TBL_PRODUCTS to select the entries in common. The product's Id is the filter for the query.

The procedures subtracts the older product's quantity from the requested new quantity to calculate the change in quantity.

  • If the difference between actual product's stock and the change in quantity is lower than 0, there aren't enough product's units in stock. In this case, the procedure blocks the saving process and warns the user with a notification explaining the reason.
  • If the difference between actual product's stock and the change in quantity is more than 0, there are enough product's units in stock. In this case, the procedure updates the product's entry in Warehouse Stock. Then the application can save the updated row in Order Items Detail table.
Code 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
    SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

    IF @ActionTargetStorage = @ApplicationDetailStorage
    BEGIN  
        SELECT
            @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )             
            , @QuantityOrder_New = JSON_VALUE(@jsonParameters, '$.Item.Quantity' )

            , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
            , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
            , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
            , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
            , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
        FROM 
            OPENJSON(@jsonParameters, '$.Item' )

        SELECT 
            @QuantityStock = PROD.WarehouseQuantity
            , @QuantityOrder_Old = ORD.Quantity
            , @ProductOrder = ORD.Product
        FROM [whrman].[TBL_ORDER_ITEMS] AS ORD
        INNER JOIN [whrman].[TBL_PRODUCTS] AS PROD
            ON ORD.Product = PROD.IdProduct
        WHERE ORD.Id = @Id

        SELECT @DeltaQuantity =  @QuantityOrder_New - @QuantityOrder_Old 

        IF (@QuantityStock - @DeltaQuantity < 0 )
        BEGIN

            SET @OutCodeStatus = '000500'
            SET @OutMessage = 'The requested quantity exceeds the current product stock.'
            SET @OutLevel = 'Error'
            RETURN -1

        END
        ELSE 
        BEGIN
            UPDATE PROD
            SET WarehouseQuantity = WarehouseQuantity - @DeltaQuantity
                , DT_LAST_UPDATE = @UPDATE_DATE
                , USER_ID_LAST_UPDATE = @USERID_LASTUPDATE
            FROM [whrman].[TBL_PRODUCTS] AS PROD
            WHERE PROD.IdProduct = @ProductOrder 

        END

PRE UPDATE

Copy the content below in the SP_Warehouse_Management_Warehouse_Orders_PRE_UPDATE Stored Procedure.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
-- Creates the procedure declaring the parameters that eLegere requires.
CREATE PROCEDURE [whrman].[SP_Warehouse_Management_Warehouse_Orders_PRE_UPDATE] 
          @jsonParameters NVARCHAR(MAX)
        , @OutCodeStatus NVARCHAR(4000) OUTPUT
        , @OutLevel NVARCHAR(4000) OUTPUT
        , @OutMessage NVARCHAR(4000) OUTPUT  
AS 
BEGIN 
    -- Variables declaration

    /* 
        The variables will record info regarding the 
        tables involved in the databases.
    */

    DECLARE @ApplicationName            NVARCHAR(4000)
    DECLARE @ApplicationMasterStorage   NVARCHAR(4000)
    DECLARE @ApplicationDetailStorage   NVARCHAR(4000)
    DECLARE @ActionTargetStorage        NVARCHAR(4000)

    --------------

    /* 
        The following variables record the updated rows'
        fields' values. 

        @Today and @dtToday record the date in Numeric format and 
        DateTime values. The Stored Procedure requires
        @Today and @dtToday to update the DT_CREATION field.
    */

    DECLARE @Id                     INT
    DECLARE @ProductOrder           INT
    DECLARE @QuantityOrder_New      INT
    DECLARE @QuantityOrder_Old      INT
    DECLARE @QuantityStock          INT
    DECLARE @DeltaQuantity          INT

    DECLARE @USERID_CREATION        NVARCHAR(4000)
    DECLARE @USERID_LASTUPDATE      NVARCHAR(4000)
    DECLARE @DT_CREATION            BIGINT
    DECLARE @UPDATE_DATE            BIGINT
    DECLARE @TIMESTAMP              VARBINARY(4)


    DECLARE @Today AS BIGINT
    DECLARE @dtToday AS DATETIME


    --------------

    -- Sets the current system date as @dtToday value
    SET @dtToday = GETDATE()

    /* 
        Converts the DateTime value to integer format.
        Assigns the converted value to the @Today variable.
    */

    SET @Today = [dbo].F_APP_DATETIME2BIGINT( @dtToday )

    -- Assigns the table names on the database to the variables
    SET @ApplicationName = 'Warehouse Orders'
    SET @ApplicationMasterStorage = 'TBL_WAREHOUSE_ORDERS'
    SET @ApplicationDetailStorage = 'TBL_ORDER_ITEMS'  

    /* 
        Assigns the value to the @ActionTargetStorage.
        The assignment employs the row's context: checks the Storage's name 
        from the JSON containing the data to save.
        - If the Storage's name is null, the user has saved a row in the Master table.
        It registers the @ApplicationMasterStorage value for @ActionTargetStorage.
        - If the Storage's nameame IS NOT null, the user has saved the row on the Detail.
        The procedure records the Detail table's name value from the JSON.
    */

    SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

    /* 
        The procedures confronts @ActionTargetStorage and @ApplicationDetailStorage
        If the table names are the same, the user has added a row to the Order Items Detail table.
        In such case, the procedure updates Warehouse Stock.
        Otherwise, the procedure is over. 
    */

    IF @ActionTargetStorage = @ApplicationDetailStorage
    BEGIN  

    /* 
        The JSON contains the object Item. Item has all the row's fields and values.
        The procedure retrieves all the required values from Item. 
        Retrives the Product information starting from the product's Id from the JSON.
        @QuantityOrder_New is the variable recording the new desired value.
    */

        SELECT
              @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )               
            , @QuantityOrder_New = JSON_VALUE(@jsonParameters, '$.Item.Quantity' )

            , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
            , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
            , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
            , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
            , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
        FROM 
            OPENJSON(@jsonParameters, '$.Item' )

        /* 
            Assigns the values needed for the query to the varibles.
            ORD is the alias for the Warehouse Order's table. 
            PROD is the alias for Warehouse Stock's table.
            Then the query flters both the tables for the product entries that have the same product's Id. 
        */

        SELECT 
              @QuantityStock = PROD.WarehouseQuantity
            , @QuantityOrder_Old = ORD.Quantity
            , @ProductOrder = ORD.Product
        FROM [whrman].[TBL_ORDER_ITEMS] AS ORD
        INNER JOIN [whrman].[TBL_PRODUCTS] AS PROD
            ON ORD.Product = PROD.IdProduct
        WHERE ORD.Id = @Id

        -- Calculates the difference between desired quantity and the previous order's quantity.

        SELECT @DeltaQuantity =  @QuantityOrder_New - @QuantityOrder_Old 

        /* 
            Checks the stock's quantity in Warehouse Order by subtracting the difference. 
            If the difference between stock and quantity is lower tha 0, the Stored Procedure
            blocks the action and yields an error notification. 
        */

        IF (@QuantityStock - @DeltaQuantity < 0 )
        BEGIN

            SET @OutCodeStatus = '000500'
            SET @OutMessage = 'The requested quantity exceeds the current product stock.'
            SET @OutLevel = 'Error'
            RETURN -1

        END
        ELSE 
        BEGIN
            -- If there's enough stock, the procedures updates the quantity in Warehouse Stock.
            UPDATE PROD
            SET WarehouseQuantity = WarehouseQuantity - @DeltaQuantity
                , DT_LAST_UPDATE = @UPDATE_DATE
                , USER_ID_LAST_UPDATE = @USERID_LASTUPDATE
            FROM [whrman].[TBL_PRODUCTS] AS PROD
            WHERE PROD.IdProduct = @ProductOrder 

        END
    END

END
GO

The Stored Procedure After Saving the Changes to a Row

Follows the explanation of the Stored Procedure that the application will run after saving any change to existing rows in the Order Items Detail table.

Step 1

This step is identical to Step 1 from The Stored Procedure Before Saving a New Row: creating the procedure and declaring the mandatory parameters that eLegere requires.

Step 2

This step is identical to Step 2 from The Stored Procedure Before Saving a New Row: declaring the variables.

Step 3

This step is identical to Step 3 from The Stored Procedure Before Saving a New Row: setting the variables' values.

Step 4

The performed action differs from the PRE UPDATE.

At this step, the procedure updates the product's stock after the row is saved in Warehouse Orders. La PRE UPDATE Stored Procedure has already verified that there is enough stock and didn't block the saving process.

The query retrieves the selected product's row on the Warehouse Stock table TBL_PRODUCTS.

Then the procedure updates the entry on the table for WarehouseQuantity field.

Each product in Warehouse Stock has a field called isAvailable. If the product's quantity goes to 0, the procedure changes the isAvailable Boolean field from true to false. If isAvailable is false, the users in Warehouse Orders can't choose that product for an order. Otherwise, if the quantity returns above 0, the procedure sets the isAvailable field's value as true.

Code 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

IF @ActionTargetStorage = @ApplicationDetailStorage
BEGIN 
    SELECT
          @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )

        , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
        , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
        , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
        , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
        , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
    FROM 
        OPENJSON(@jsonParameters, '$.Item' )

    UPDATE PROD
    SET IsAvailable = CASE 
                            WHEN WarehouseQuantity = 0 THEN 0 
                            ELSE 1 
                      END
        , DT_LAST_UPDATE = @UPDATE_DATE
        , USER_ID_LAST_UPDATE = @USERID_LASTUPDATE
    FROM [whrman].[TBL_PRODUCTS] AS PROD
    INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
        ON PROD.IdProduct = ORD.Product
    WHERE ORD.Id = @Id


END

POST UPDATE

Copy the content below in the SP_Warehouse_Management_Warehouse_Orders_POST_UPDATE Stored Procedure.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
-- Creates the procedure declaring the parameters that eLegere requires
CREATE PROCEDURE [whrman].[SP_Warehouse_Management_Warehouse_Orders_POST_UPDATE]
          @jsonParameters NVARCHAR(MAX)
        , @OutCodeStatus NVARCHAR(4000) OUTPUT
        , @OutLevel NVARCHAR(4000) OUTPUT
        , @OutMessage NVARCHAR(4000) OUTPUT  
AS 
BEGIN 
    -- Variables declaration

    /* 
        The variables will record info regarding the 
        tables involved in the databases.
    */

    DECLARE @ApplicationName            NVARCHAR(4000)
    DECLARE @ApplicationMasterStorage   NVARCHAR(4000)
    DECLARE @ApplicationDetailStorage   NVARCHAR(4000)
    DECLARE @ActionTargetStorage        NVARCHAR(4000)

    --------------

    /* 
        The following variables record the updated rows'
        fields' values. 

        @Today and @dtToday record the date in Numeric Format 
        and the DateTime values. 
        The Stored Procedure requires @Today and @dtToday to update 
        the DT_CREATION field.
    */

    DECLARE @Id                     INT

    DECLARE @USERID_CREATION        NVARCHAR(4000)
    DECLARE @USERID_LASTUPDATE      NVARCHAR(4000)
    DECLARE @DT_CREATION            BIGINT
    DECLARE @UPDATE_DATE            BIGINT
    DECLARE @TIMESTAMP              VARBINARY(4)


    DECLARE @Today AS BIGINT
    DECLARE @dtToday AS DATETIME


    --------------

    -- Sets the current system date as @dtToday value
    SET @dtToday = GETDATE()

    /* 
        Converts the DateTime value to integer format.
        Assigns the converted value to the @Today variable.
    */

    SET @Today = [dbo].F_APP_DATETIME2BIGINT( @dtToday )

    -- Assigns the table names on the database to the variables
    SET @ApplicationName = 'Warehouse Orders'
    SET @ApplicationMasterStorage = 'TBL_WAREHOUSE_ORDERS'
    SET @ApplicationDetailStorage = 'TBL_ORDER_ITEMS'  

    /* 
        Assigns the value to the @ActionTargetStorage.
        The assignment employs the row's context: checks the Storage's name 
        from the JSON containing the data to save.
        - If the Storage's name is null, the user has saved a row in the Master table. 
        It registers the @ApplicationMasterStorage value for @ActionTargetStorage.
        - If the Storage's nameame IS NOT null, the user has saved the row on the Detail.
        The procedure records the Detail table's name value from the JSON.
    */

    SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

    /* 
        The procedures confronts @ActionTargetStorage and @ApplicationDetailStorage
        If the table names are the same, the user has added 
        a row to the Order Items Detail table
        In this case, the procedure checks if the requested 
        quantity is available in stock.
        Otherwise, the procedure is over. 
    */

    IF @ActionTargetStorage = @ApplicationDetailStorage
    BEGIN 

    /* 
        Retrives the Product information starting from the product's Id from the JSON.
        The JSON contains the object Item. Item has all the row's fields and values.
        The procedure retrieves all the required values from Item. 
    */

        SELECT
              @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )

            , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
            , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
            , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
            , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
            , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
        FROM 
            OPENJSON(@jsonParameters, '$.Item' )

        /*
            Each product in TBL_PRODUCTS has a Boolean field called isAvailable.
            If isAvailable is false, the users in Warehouse Orders can't choose the product for an order. 

            When a product has the value equal or higher than 0 for the WarehouseQuantity field, 
            the procedure changes the isAvailable's value. 

            False if equal 0 or True when the quantity is more than 0.

            Also, updates DT_LAST_UPDATE and USER_ID_LAST_UPDATE fields.
        */

        UPDATE PROD
        SET IsAvailable = CASE 
                                WHEN WarehouseQuantity = 0 THEN 0 
                                ELSE 1 
                          END
            , DT_LAST_UPDATE = @UPDATE_DATE
            , USER_ID_LAST_UPDATE = @USERID_LASTUPDATE
        FROM [whrman].[TBL_PRODUCTS] AS PROD
        INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
            ON PROD.IdProduct = ORD.Product
        WHERE ORD.Id = @Id


    END

END
GO

The Stored Procedure Before Deleting a Row

Follows the explanation of the Stored Procedure that the application will run before deleting a row either in the Master or Detail table.

Note

The data deletion requires only the PRE DELETE for the reasons explained in The Stored Procedures' Flow in the Application

This procedure updates the stock's quantity in two cases:

  • The user deletes the order's row on the Master table. The procedure deletes all the related rows from Order Items and updates the stock.
  • The user deletes an item from the Detail table Order Items. The procedure updates the stock.

This Stored Procedure has a further step since it manages deletions on both Master and Detail tables.

Step 1

This step is identical to Step 1 from The Stored Procedure Before Saving a New Row: creating the procedure and declaring the mandatory parameters that eLegere requires.

Step 2

This step is identical to Step 2 from The Stored Procedure Before Saving a New Row: declaring the variables.

Step 3

This step is identical to Step 3 from The Stored Procedure Before Saving a New Row: setting the variables' values for the record.

Step 4

At this step, the procedure updates the product's stock before the application has saved the rows' deletion in Warehouse Orders.

The Stored Procedure behaves differently according to Master and Detail table. The first check is verifying through the Storage's name if the edited table is the Master table.

If the table is the Master table, the procedure removes all the Detail table's rows (Order Items) and updates the quantities in Warehouse Stock.

Otherwise, the procedures continues to step 5.

Code 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SET @dtToday = GETDATE()
SET @Today = [dbo].F_APP_DATETIME2BIGINT( @dtToday )

SET @ApplicationName = 'Warehouse Orders'
SET @ApplicationMasterStorage = 'TBL_WAREHOUSE_ORDERS'
SET @ApplicationDetailStorage = 'TBL_ORDER_ITEMS'

SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

IF @ActionTargetStorage = @ApplicationMasterStorage
BEGIN 
    SELECT
          @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )               

        , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
        , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
        , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
        , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
        , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
    FROM 
        OPENJSON(@jsonParameters, '$.Item' )

    SELECT @IdOrder = IdWarehouseOrder
    FROM [whrman].[TBL_WAREHOUSE_ORDERS]
    WHERE Id = @Id

    UPDATE PROD
    SET WarehouseQuantity = PROD.WarehouseQuantity + ORD.Quantity
    FROM [whrman].[TBL_PRODUCTS] AS PROD
    INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
        ON PROD.IdProduct = ORD.Product
    WHERE WarehouseOrderId = @IdOrder

    UPDATE PROD
    SET IsAvailable = CASE
                            WHEN WarehouseQuantity > 0 THEN 1 
                            ELSE 0 
                      END
    FROM [whrman].[TBL_PRODUCTS] AS PROD
    INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
        ON PROD.IdProduct = ORD.Product
    WHERE WarehouseOrderId = @IdOrder

    DELETE
    FROM [whrman].[TBL_ORDER_ITEMS]
    WHERE WarehouseOrderId = @IdOrder


END

Step 5

If the Storage's name doesn't correspond to the Master table's name (TBL_WAREHOUSE_ORDERS), then the second check verifies if the user has deleted a row in Order Items' table.

Question

Why making a second check if there are only one Master and one Detail table? If table isn't the Master, then it must be TBL_PRODUCTS of the Detail.

The second check is necessary in case the Designer decides to add further Detail tables to Warehouse Orders.

In this way, the application will execute the procedure only on either the Master or the Order Items Detail table. So, if the user saves in a further Detail table, no action on Warehouse Stock's table will happen.

If the user deletes a product from an order, the procedures updates the quantity in stock in Warehouse Stock. Adds back to the order's quantity to the WarehouseQuantity's value.

If the WarehouseQuantity was 0, the procedure sets the isAvailable field's value back true. The product is back in stock and available.

Code 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
IF @ActionTargetStorage = @ApplicationDetailStorage
BEGIN 
    SELECT
          @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )               

        , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
        , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
        , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
        , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
        , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
    FROM 
        OPENJSON(@jsonParameters, '$.Item' )    

    UPDATE PROD
    SET WarehouseQuantity = PROD.WarehouseQuantity + ORD.Quantity
    FROM [whrman].[TBL_PRODUCTS] AS PROD
    INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
        ON PROD.IdProduct = ORD.Product
    WHERE ORD.Id = @Id

    UPDATE PROD
    SET IsAvailable = 1
    FROM [whrman].[TBL_PRODUCTS] AS PROD
    INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
        ON PROD.IdProduct = ORD.Product
    WHERE WarehouseQuantity > 0


END

PRE DELETE

Copy the content below in the SP_Warehouse_Management_Warehouse_Orders_PRE_DELETE Stored Procedure.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
-- Creates the procedure declaring the parameters that eLegere requires
CREATE PROCEDURE [whrman].[SP_Warehouse_Management_Warehouse_Orders_PRE_DELETE] 
          @jsonParameters NVARCHAR(MAX)
        , @OutCodeStatus NVARCHAR(4000) OUTPUT
        , @OutLevel NVARCHAR(4000) OUTPUT
        , @OutMessage NVARCHAR(4000) OUTPUT  
AS 
BEGIN 
    -- Variables declaration

    /* 
        The variables will record info regarding the 
        tables involved in the databases.
    */

    DECLARE @ApplicationName            NVARCHAR(4000)
    DECLARE @ApplicationMasterStorage   NVARCHAR(4000)
    DECLARE @ApplicationDetailStorage   NVARCHAR(4000)
    DECLARE @ActionTargetStorage        NVARCHAR(4000)

    --------------

    /*
        The following variables record the updated rows'
        fields' values. 

        @Today and @dtToday record the date in numeric format
        and DateTime values. 
        The Stored Procedure requires @Today and @dtToday to update 
        the DT_CREATION field. 
    */

    DECLARE @Id                     INT
    DECLARE @IdOrder                INT

    DECLARE @USERID_CREATION        NVARCHAR(4000)
    DECLARE @USERID_LASTUPDATE      NVARCHAR(4000)
    DECLARE @DT_CREATION            BIGINT
    DECLARE @UPDATE_DATE            BIGINT
    DECLARE @TIMESTAMP              VARBINARY(4)


    DECLARE @Today AS BIGINT
    DECLARE @dtToday AS DATETIME

    --------------

    -- Sets the current system date as @dtToday value
    SET @dtToday = GETDATE()

    /* 
        Converts the DateTime value to integer format.
        Assigns the converted value to the @Today variable.
    */

    SET @Today = [dbo].F_APP_DATETIME2BIGINT( @dtToday )

    -- Assigns the table names on the database to the variables
    SET @ApplicationName = 'Warehouse Orders'
    SET @ApplicationMasterStorage = 'TBL_WAREHOUSE_ORDERS'
    SET @ApplicationDetailStorage = 'TBL_ORDER_ITEMS'

    /* 
        Assigns the value to the @ActionTargetStorage.
        The assignment employs the row's context: checks the Storage's name 
        from the JSON containing the data to save.
        - If the Storage's name is null, the user has saved a row in the Master table. 
        It registers the @ApplicationMasterStorage value for @ActionTargetStorage.
        - If the Storage's nameame IS NOT null, the user has saved the row on the Detail.
        The procedure records the Detail table's name value from the JSON.
    */

    SET @ActionTargetStorage = ISNULL( JSON_VALUE(@jsonParameters, '$.StorageName' ), @ApplicationMasterStorage )

    -- There are two IF conditions to verify what table the deletion involves.

    /* 
        The procedure confronts @ActionTargetStorage and @ApplicationMasterStorage
        If the table names are the same, the user has deleted the whole order. So, the
        procedure will clear the Detail rows and update the stock before saving the row.
    */

    IF @ActionTargetStorage = @ApplicationMasterStorage
    BEGIN 

    /* 
        Retrives the product information starting from the product's Id from the JSON.
        The JSON contains the object Item. Item has all the row's fields and values.
        The procedure retrieves all the required values from Item. 

        For the deletion, the procedure requires ony the Id and the fields recording
        information like the Timestamp, date of creation, date of update, and so on. 
    */

        SELECT
              @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )               

            , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
            , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
            , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
            , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
            , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
        FROM 
            OPENJSON(@jsonParameters, '$.Item' )

        /*
            The procedure stores the order's Id and searches it on TBL_WAREHOUSE_ORDERS
            for the key bounding the Detail to the Master rows.
        */

        SELECT @IdOrder = IdWarehouseOrder
        FROM [whrman].[TBL_WAREHOUSE_ORDERS]
        WHERE Id = @Id

        /* 
            Finds the products involved in the order through the products' IDs.
            Assigns to WarehouseQuantity the new value.
            Updates the products' quantity in stock. 
        */

        UPDATE PROD
        SET WarehouseQuantity = PROD.WarehouseQuantity + ORD.Quantity
        FROM [whrman].[TBL_PRODUCTS] AS PROD
        INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
            ON PROD.IdProduct = ORD.Product
        WHERE WarehouseOrderId = @IdOrder

        /* 
            Each product in TBL_PRODUCTS has a Boolean field called isAvailable.
            If isAvailable is false, the users in Warehouse Orders can't choose the product for an order. 
            When a product has the value 0 for the WarehouseQuantity field, 
            the procedure changes the isAvailable's value.

            When the updated product's quantity rises again from 0, the procedure
            changes the isAvailable's value to true. 
        */  

        UPDATE PROD
        SET IsAvailable = CASE
                                WHEN WarehouseQuantity > 0 THEN 1 
                                ELSE 0 
                          END
        FROM [whrman].[TBL_PRODUCTS] AS PROD
        INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
            ON PROD.IdProduct = ORD.Product
        WHERE WarehouseOrderId = @IdOrder

        -- Deletes all the Detail rows that have the same order's Id
        DELETE
        FROM [whrman].[TBL_ORDER_ITEMS]
        WHERE WarehouseOrderId = @IdOrder


    END


    IF @ActionTargetStorage = @ApplicationDetailStorage
    BEGIN 

    /* 
        Retrives the product information starting from the product's Id from the JSON.
        The JSON contains the object Item. Item has all the row's fields and values.
        The procedure retrieves all the required values from Item. 

        For the deletion, the procedures requires ony the Id and the fields recording
        information like the Timestamp, date of creation, date of update, and so on. 
    */
        SELECT
              @Id = JSON_VALUE(@jsonParameters, '$.Item.Id' )               

            , @USERID_CREATION = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_CREATION')
            , @USERID_LASTUPDATE = JSON_VALUE(@jsonParameters, '$.Item.USER_ID_LAST_UPDATE')
            , @DT_CREATION = JSON_VALUE(@jsonParameters, '$.Item.DT_CREATION')
            , @UPDATE_DATE = JSON_VALUE(@jsonParameters, '$.Item.DT_LAST_UPDATE')
            , @TIMESTAMP = CONVERT(VARBINARY(4), ISNULL(JSON_VALUE(@jsonParameters, '$.Item.TIMESTAMP'), '0x00000001'))
        FROM 
            OPENJSON(@jsonParameters, '$.Item' )    

        /* 
            Finds the products involved in the order through the products' IDs.
            Assigns to WarehouseQuantity the new value.
            Updates the product's quantity in stock. 
        */

        UPDATE PROD
        SET WarehouseQuantity = PROD.WarehouseQuantity + ORD.Quantity
        FROM [whrman].[TBL_PRODUCTS] AS PROD
        INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
            ON PROD.IdProduct = ORD.Product
        WHERE ORD.Id = @Id

        /* 
            Each product in TBL_PRODUCTS has a Boolean field called isAvailable.
            If isAvailable is false, the users in Warehouse Orders can't choose 
            the product for an order. 

            When a product has the value 0 for the WarehouseQuantity field, 
            the procedure changes the isAvailable's value.

            When the updated product's quantity rises again from 0, the procedure
            changes the isAvailable's value to true. 
        */      

        UPDATE PROD
        SET IsAvailable = 1
        FROM [whrman].[TBL_PRODUCTS] AS PROD
        INNER JOIN [whrman].[TBL_ORDER_ITEMS] AS ORD
            ON PROD.IdProduct = ORD.Product
        WHERE WarehouseQuantity > 0


    END

END


GO

Enabling the Stored Procedure in Design Mode

Attention

This part requires the Application Designer permission from your administrator. (See What are the Design Tasks? for more information about Design permissions.)

After having saved the Stored Procedure in the database, you must enable it with all the related triggers in the Design Mode.

Follow the steps below to execute the Stored Procedure.

  1. In the Table panel of the application's Design Mode, click on the Actions tab in the left sub-panel.

  2. In the Actions sub-panel, there is a list of triggers called Action Types.

    You can specify when the application should run a Stored Procedure through the Action Types:

    • Pre Save Executes the Stored Procedure before saving the new row.
    • Post Save Executes the Stored Procedure after saving the new row.
    • Pre Update Executes the Stored Procedure before updating a row.
    • Post Update Executes the Stored Procedure after updating the row.
    • Pre Remove Executes the Stored Procedure before removing the row.
    • Post Remove Executes the Stored Procedure after removing the row.

    Turn on all the Enabled toggle switches, exception made the Post Remove.

  3. Save your changes by clicking on the Save button.

Success

Every time you will add or edit an order in Warehouse Orders, the Stored Procedure will update the items' stock in Warehouse Stock.

If the requested quantity goes above the available stock, the application doesn't save the change and warns the user.