Changes to database for Max4Sale capability
The following changes have been made to the SiriusSQL database in support of Triggered Counts for Max4Sale capability:
siriussp_GetQtyRemaining_By_DCI_base:
• Made refinements to the remaining quantity calculations on Time Only Max4Sale limits when the limits are further restricted by Sales Channels.
• An optional parameter tlBypassTC (bit, default = 0) - If passed and set to 1, it bypasses using new Triggered Counts in remaining quantity calculations (if activated in prefs_sl.UseCounts) and calculates remaining quantities as before, by summation of applicable tr_save and transact rows.
• Minor performance enhancement when using trigger counts.
• If UseCounts in prefs_sl is TRUE, stored procedure now attempts to retrieve current sold and saved quantities from the related Max4SaleCounts table for the relevant Max4Sale restrictions. Those counts which are not yet calculated and available in Max4SaleCounts is calculated as before by summing existing transact and tr_save rows. Those missing counts are then be added to the Max4SaleCounts table, thereby speeding up subsequent calls to this GetQtyRemaining stored procedure.
Note: The Max4SaleCounts table is used only for Date/Time and Time Only Max4Sale restrictions, not Dynamic Time Span restriction types.
prefs_sl:
• Added UseCounts (bit).
• Modified Insert/Update trigger to truncate the Max4SaleCounts table if the values of the UseCounts or Max4Saved columns are changed.
prefs:
• Removed triggercnt.
• Superseded by UseCounts in prefs_sl table.
Max4Sale and pts4sale:
• Removed UseCounts.
• Superseded by UseCounts in prefs_sl table.
Max4Sale:
• Modified Insert/Update trigger to delete the related Max4SaleCounts child row if one exists.
Max4SaleCounts:
• Added CurrentSavedCount
tr_save:
• Added Insert/Update/Delete triggers to update CurrentSavedCount values in all affected Max4SaleCounts rows, using the same criteria used in the siriussp_GetQtyRemaining_By_DCI_base stored procedure.
• This affects only one row inserts, updates and deletes performed through Siriusware modules.
• Multiple row batch inserts, updates and deletes done outside of Siriusware modules truncate the Max4SaleCounts table and are rebuilt as necessary by calls to the siriussp_GetQtyRemaining_By_DCI_base stored procedure.
transact:
• Added Insert/Update/Delete triggers to update CurrentCount values in all affected Max4SaleCounts rows, using the same criteria used in the siriussp_GetQtyRemaining_By_DCI_base stored procedure.
• This affects only one row inserts, updates and deletes performed through Siriusware modules.
• Multiple row batch inserts, updates and deletes done outside of Siriusware modules truncate the Max4SaleCounts table and are rebuilt as necessary by calls to the siriussp_GetQtyRemaining_By_DCI_base stored procedure.