OCT 5.10.18
Download
Applikation OCT (Version für Saxess Kunden)
Applikation CPCE (Version für Corporate Planning Kunden)
Datenbank (Skript für die Anlage der Datenbank)
MatrixConnector (Excel Add-In für Datenerfassung)
.NET 6 SDK (Systemvoraussetzung)
Informationen
Releasedatum: 12.06.2024
Releasetyp: Stabil
Release Notes
Highlights der Version 5.10 auf der Übersichtsseite
Bekanntes Problem:
Die Mandanten-ID-Mapping-Spalte in der Mandantenverwaltung wird nach dem Start des Prozesses gelöscht.
Die Lösung besteht darin, die untenstehende Prozedur global.spPOST_Company einmal auszuführen:
CODE
DROP PROCEDURE IF EXISTS global.spPOST_Company;
GO
/*
Procedure to POST a Company, it is called if
- Companies are created by query the from a sources system
- Company Properties are edited by the Company edit GUI
- all optional Parameters are keept properties if skip during sended or sended as NULL
- Companies for Datasource 0 can be created even that this datasource don't exists
Saxess Software GmbH
Last modified: 02/2024 for OCT 5.10
Testcall Procedure
DECLARE @RC INT;
EXEC @RC = global.spPOST_Company
@Username = 'SQL'
,@DataSourceID = '0'
,@CompanyID = 'B'
,@CompanyName = 'C GmbH'
,@Description = 'Das ist die C GmbH'
,@FinancialYearBegin = 1
,@ChartsOfAccountsID = 'SKR04'
,@AccountLength = 4
,@Currency = 'EUR'
,@CustomValuesJSON = '{}'
,@CustomValuesJSONUser = '{}'
,@OwnerCODE = 'INTEGRATION'
,@CompanyIDResult = ''
,@MappingPriority = 1
PRINT @RC
SELECT * FROM global.tCompanies;
SELECT * FROM system.tDataSources;
DELETE FROM global.tCompanies WHERE CompanyID = 'A';
Testcall Documentation
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'global', 'PROCEDURE', 'spPOST_Company',NULL,NULL)
UNION ALL
SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'global', 'PROCEDURE', 'spPOST_Company','PARAMETER',NULL)
*/
CREATE PROCEDURE global.spPOST_Company (
@Username NVARCHAR(255)
, @DataSourceID NVARCHAR(50)
, @CompanyID NVARCHAR(50)
, @CompanyName NVARCHAR(255) = NULL
, @Description NVARCHAR(2000) = NULL
, @FinancialYearBegin INT = NULL
, @ChartsOfAccountsID NVARCHAR(50) = NULL
, @AccountLength INT = NULL
, @Currency NVARCHAR(50) = NULL
, @CustomValuesJSON NVARCHAR(MAX) = NULL
, @CustomValuesJSONUser NVARCHAR(MAX) = NULL
, @OwnerCODE NVARCHAR(50) = NULL
, @CompanyIDResult NVARCHAR(50) = NULL
, @MappingPriority INT = NULL
)
WITH EXECUTE AS 'dbo'
AS
BEGIN
BEGIN TRY
-- Logging
DECLARE @TimestampCall DATETIME = GETUTCDATE();
DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
DECLARE @AffectedRows INT = 0;
DECLARE @ResultCode INT = 501;
DECLARE @Comment NVARCHAR(4000) = N'';
DECLARE @ParameterString NVARCHAR(MAX) = N'';
DECLARE @TransactUsername NVARCHAR(255) = N'';
EXEC system.spGET_ParameterString @ParameterString OUTPUT, 14, @Username, @DataSourceID, @CompanyID, @CompanyName, @Description, @FinancialYearBegin, @ChartsOfAccountsID, @AccountLength, @Currency, @CustomValuesJSON, @CustomValuesJSONUser, @OwnerCODE, @CompanyIDResult, @MappingPriority;
EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
-- NULL Protection
SET @Username = COALESCE(@Username, N'');
SET @DataSourceID = COALESCE(@DataSourceID, N'');
SET @CompanyID = COALESCE(@CompanyID, N'');
-- DataSourceID exists?
IF @DataSourceID NOT IN (SELECT DataSourceID FROM system.tDataSources) AND @DataSourceID <> '0'
BEGIN
SET @Comment = 'DataSourceID ' + @DataSourceID + ' doesn''t exist.';
EXEC system.spSEND_Message 'ERROR', @Comment;
END
BEGIN TRANSACTION
-- Remove linebreaks from input
SET @CompanyName = REPLACE(REPLACE(@CompanyName, CHAR(10), ''), CHAR(13), '')
SET @Description = REPLACE(REPLACE(@Description, CHAR(10), ''), CHAR(13), '')
SET @ChartsOfAccountsID = REPLACE(REPLACE(@ChartsOfAccountsID, CHAR(10), ''), CHAR(13), '')
SET @Currency = REPLACE(REPLACE(@Currency, CHAR(10), ''), CHAR(13), '')
SET @CustomValuesJSON = REPLACE(REPLACE(@CustomValuesJSON, CHAR(10), ''), CHAR(13), '')
SET @CompanyIDResult = REPLACE(REPLACE(@CompanyIDResult, CHAR(10), ''), CHAR(13), '')
-- Update MappingPriority in existing companies
IF @CompanyIDResult IS NOT NULL AND @MappingPriority IS NOT NULL
BEGIN
UPDATE global.tCompanies
SET MappingPriority = MappingPriority + 1
WHERE
DataSourceKey = @DataSourceID
AND CompanyIDResult = @CompanyIDResult
AND MappingPriority >= @MappingPriority;
END
-- Update existing data
UPDATE global.tCompanies
SET
CompanyName = COALESCE(@CompanyName, CompanyName)
, Description = COALESCE(@Description, Description)
, FinancialYearBegin = COALESCE(@FinancialYearBegin, FinancialYearBegin)
, ChartsOfAccountsID = COALESCE(@ChartsOfAccountsID, ChartsOfAccountsID)
, AccountLength = COALESCE(@AccountLength, AccountLength)
, Currency = COALESCE(@Currency, Currency)
, CustomValuesJSON = COALESCE(@CustomValuesJSON, CustomValuesJSON)
, CustomValuesJSONUser = COALESCE(@CustomValuesJSONUser, CustomValuesJSONUser)
, OwnerCODE = COALESCE(@OwnerCODE, OwnerCODE)
, CompanyIDResult = COALESCE(NULLIF(@CompanyIDResult, ''), CompanyIDResult)
, MappingPriority = COALESCE(@MappingPriority, MappingPriority)
WHERE
DataSourceKey = @DataSourceID
AND CompanyID = @CompanyID;
SET @AffectedRows = @@ROWCOUNT;
-- No rows updated because CompanyID doesn't exist -> insert new row
IF @AffectedRows = 0
BEGIN
INSERT INTO GLOBAL.tCompanies (
DataSourceKey
, CompanyID
, CompanyName
, Description
, FinancialYearBegin
, ChartsOfAccountsID
, AccountLength
, Currency
, CustomValuesJSON
, CustomValuesJSONUser
, OwnerCODE
, CompanyIDResult
, MappingPriority
)
VALUES (
@DataSourceID
, @CompanyID
, COALESCE(@CompanyName, 'UNKNOWN')
, COALESCE(@Description, '')
, COALESCE(@FinancialYearBegin, 1)
, COALESCE(@ChartsOfAccountsID, '')
, COALESCE(@AccountLength, 4)
, COALESCE(@Currency, '')
, COALESCE(@CustomValuesJSON, '')
, COALESCE(@CustomValuesJSONUser, '')
, COALESCE(@OwnerCODE, '')
, COALESCE(@CompanyIDResult, '')
, COALESCE(@MappingPriority, 1)
);
SET @AffectedRows = @@ROWCOUNT;
END;
-- Update MappingPriority to prevent duplicates
WITH Companies AS (
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY COALESCE(NULLIF(CompanyIDResult, ''), CompanyID) ORDER BY MappingPriority) AS NewPriority
FROM global.tCompanies
)
UPDATE Companies
SET MappingPriority = NewPriority;
COMMIT TRANSACTION
SET @ResultCode = 200;
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRANSACTION
END
SET @ResultCode = 500;
SET @Comment = ERROR_MESSAGE();
END CATCH
EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment;
IF @ResultCode >= 500
EXEC system.spSEND_Message 'ERROR', @Comment;
RETURN @ResultCode;
END;
-- SET documentation variables ***********************************************************************
DECLARE
@level0name NVARCHAR(255) = N'global' -- enter schema name of the table
,@level1name NVARCHAR(255) = N'spPOST_Company' -- enter procedure name
,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom)
,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...)
,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process
-- STANDARD OBJECTS
-- 1 = shiped from saxess standard without modification
-- 2 = shiped from saxess standard modified FOR customer from saxess
-- 3 = shiped from saxess standard modified FOR customer from partner
-- 4 = shiped from saxess standard modified FROM customer themself for own needs
-- CUSTOM OBJECTS
-- 10 = shiped from saxess as customer specific object
-- 11 = shiped from partner as customer specific object
-- 12 = shiped from customer as own specific object
,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage)
-- KEEP this default constants *************************************************************************
DECLARE
@name NVARCHAR(255) = N'MS_Description'
,@level0type NVARCHAR(255) = N'SCHEMA'
,@level1type NVARCHAR(255) = N'PROCEDURE'
,@level2type NVARCHAR(255) = N'PARAMETER'
,@level2name NVARCHAR(255) = N''
,@value NVARCHAR(1000) = N'';
SET @value = @SX_Owner;
EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name;
SET @value = @SX_Module;
EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name;
SET @value = @SX_ShipmentFlag;
EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name;
SET @value = @SX_UserHint;
EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name;
-- SET documententation *************************************************************************
-- SET Procedure documentation
SET @value = N'Procedure save / edit Companies.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
-- optional SET parameter documentation (only for Core / Standardmodules)
SET @level2name = N'@DataSourceID';
SET @value = N'Integer number of the DataSource.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@CompanyID';
SET @value = N'ID of the Company, must be unique in this DataSource.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@CompanyName';
SET @value = N'Name of this company.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@Description';
SET @value = N'Description of the company.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@FinancialYearBegin';
SET @value = N'Month of the start of the financial year - e.g. 1 for January.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@ChartsOfAccountsID';
SET @value = N'ID of the current Chart of Accounts, this ID must match the ID in Table global.tFIN_ChartsOfAccounts.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@AccountLength';
SET @value = N'Integer Value for the length of the AccountID - usually between 4 and 8.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@Currency';
SET @value = N'ISO Code of the currency of the company, e.g. EUR';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@CustomValuesJSON';
SET @value = N'Custom field to store any informations as JSON Array.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@CustomValuesJSONUser';
SET @value = N'Custom field to store any informations as JSON Array, may have rights difference for reading.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
SET @level2name = N'@OwnerCode';
SET @value = N'CODE Word who the owner of this company is - INTEGRATION or PLANNING.';
EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO