Replacing nested SCV stored procedures or functions in SCV view definitions
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.