Replacing nested SCV database functions in SCV view definitions

SCV contains a number of utility database functions (SZ_F_xxx). These utility functions are used in the standard SCV views, for example, to concatenate string values, to format date values or to split concatenated string values back to the components. One of these functions is the SZ_F_CONCATn function, which allows users to concatenate minimum two and maximum six string values using separators in between. In a few SCV views, this concatenation function is nested, one concatenation function calling another, to concatenate more than six string values.

The nested usage of the SZ_F_CONCATn functions in SCV views is causing a runtime error with a specific Microsoft SQL Server version, namely Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64). Although it is possible to execute the view query with nested function calls, the same query when used in a view gives runtime error.

These M3 views are updated to replace the nested SZ_F_CONCATn functions:

  • ST_V_M3_CAL_PLANNING
  • ST_V_M3_CURRENCY_RATE
  • ST_V_M3_DATASET_SALES
  • ST_V_M3_INVENTORY_AGING
  • ST_V_M3_INVENTORY_EXPIRY
  • ST_V_M3_INVENTORY_SHIPMENT (not used in any Staging -> Inbox transfers)
  • ST_V_M3_ITEM_WAREHOUSE_SOURCING
  • ST_V_M3_PURCHASE_OPEN_TRX
  • ST_V_M3_PURCHASE_PLND_TRX
  • ST_V_M3_ROUTING_INPUT_ITEM
  • ST_V_M3_ROUTING_OP (not used in any Staging -> Inbox transfers)
  • ST_V_M3_ROUTING_OP_ALT
  • ST_V_M3_ROUTING_OUTPUT_ENDP
  • ST_V_M3_ROUTING_OUTPUT_MTRL
  • ST_V_M3_ROUTING_RESOURCE_ALT
  • ST_V_M3_SALES_OPEN_TRX
  • ST_V_M3_TRANSFER_OPEN_TRX

Action required when M3 integration is used: The base for M3 template for the standard M3 integration already includes the modified view definitions. Loading the base for M3 template, or the XML definitions of the above listed views, is required to prevent any future runtime errors caused by the nested function calls.

Action required when custom views are used containing nested function calls. Nested calls to SZ_F_CONCATn function in all custom view definitions that must be replaced with standard SQL functions as described below:

Case 1

Replace: SZ_F_CONCATx (separator string, 'Y', string 1, string 2, ..., string X)

With: COALESCE(string 1, '') + 'separator' + COALESCE(string 2, '') + 'separator' + ... + 'separator' + COALESCE(string X, '')

Case 2

Replace: SZ_F_CONCATx (separator string, 'N', string 1, string 2, ..., string X)

With: COALESCE(string 1, '') + COALESCE('separator' + string 2, '') + ... + COALESCE('separator' + string X, '')

No action is required when there are no view definitions containing nested calls to the SZ_F_CONCATn function.