Data addition and update

Action in Comarch ERP Standard

In Comarch ERP Standard database, there is a procedure called POS.ExportCustomObjects. It should be overridden in such a way as to make it retrieve data from the database that is to be sent to a given POS workstation and return it in the XML format.

The procedure uses parameters that allow limiting and adjusting the dataset sent to a given POS workstation:

• @rowVersion (bigint) – this value makes it possible to perform differential synchronization. It is contained in the XML generated by this procedure upon the last successful synchronization (as part of the RowVersion attribute).
• @companyUnitId (int) – it is the Id of a center where the POS workstation has been defined (CompanyStructure.CompanyUnits)
• @pointOfSaleId (int) – it is the Id of the POS workstation (Synchronization.PointsOfSales)
• @languageId (int) – it is the Id of the data language (Dictionaries.Languages)

When exporting bit and datetime data from columns, use the POS.GetBitString and POS.GetDatetimeString functions.

Action in Comarch POS

The POS database contains a procedure called Synchronization.ImportCustomObjects. It should be overridden so that it updates tables in the POS database based on the XML data received from Comarch ERP Standard – and generated by the procedure mentioned in the previous chapter.

Example

The following example illustrates the differential synchronization of data from two tables; the data is exported from the Comarch ERP Standard database and imported into the POS database:

• Dic_PaymentForms → Configuration.CustomPaymentForms
• Dic_Country → Configuration.CustomCountries

a) Export procedure

ALTER PROCEDURE [POS].[ExportCustomObjects]
    @rowVersion bigint,
    @companyUnitId int,
    @pointOfSaleId int,
    @languageId int
AS
BEGIN
                                    SET NOCOUNT ON;
    declare @dbts bigint = cast(@@DBTS as bigint)
        
    select
            @dbts as [@RowVersion],

        (select
            pf.Id			as [@Id],
            pf.Name		as [@Name],
            pf.CategoryId		as [@Type],
            POS.GetBitString(pf.Active)
                        as [@IsActive]
        from SecDictionaries.Dic_PaymentForms pf
        where pf.Timestamp > @rowVersion		
        for xml path('PaymentForm'), type),
        (select
                        c.Id		as [@Id],
            c.Code		as [@Code],
            c.Name		as [@Name],
            POS.GetBitString(c.Active)
                    as [@IsActive]
        from dbo.Dic_Country c
        where c.Timestamp > @rowVersion
        
        for xml path('Country'), type)
    for xml path('CustomObjects')
END

b) Import procedure

ALTER PROCEDURE [Synchronization].[ImportCustomObjects]
    @XML xml
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Countries --
    select
        doc.col.value('@Id', 'int') Id,
        doc.col.value('@Code', 'nvarchar(50)') Code,
        doc.col.value('@Name', 'nvarchar(100)') Name,
        doc.col.value('@IsActive', 'bit') IsActive
    into #Countries
    from @XML.nodes('/DataFromERP/CustomObjects/Country') doc(col)

update pos
    set
        Code = erp.Code,
        Name = erp.Name,
        IsActive = erp.IsActive
    from Configuration.CustomCountries pos
        join #Countries erp on erp.Id = pos.Id

    insert into Configuration.CustomCountries
    (
        Id,
        Code,
        Name,
        IsActive
    )
    select
        Id,
        Code,
        Name,
        IsActive
    from #Countries erp
    where not exists (select 1 from Configuration.CustomCountries where Id = erp.Id)

    -- Payment forms --
    select
        doc.col.value('@Id', 'int') Id,
        doc.col.value('@Name', 'nvarchar(50)') Name,
        doc.col.value('@Type', 'tinyint') [Type],
        doc.col.value('@IsActive', 'bit') IsActive
    into #PaymentForms
    from @XML.nodes('/DataFromERP/CustomObjects/PaymentForm')  doc(col)

    update pos
    set
        Name = erp.Name,
        Type = erp.Type,
        IsActive = erp.IsActive
    from Configuration.CustomPaymentForms pos
        join #PaymentForms erp on erp.Id = pos.Id

    insert into Configuration.CustomPaymentForms
    (
        Id,
        Name,
        Type,
        IsActive
    )
    select
        Id,
        Name,
        Type,
        IsActive
    from #PaymentForms erp
    where not exists (select 1 from Configuration.CustomPaymentForms where Id = erp.Id)
    
END

Handling of generic directories

Update of the export procedure

In the POS.ExportGenericDirectories procedure, the WHERE clause must include the InternalName of a directory that should also be synchronized.

In the POS.ExportGenericDirectoryValues procedure, the WHERE clause must include the InternalName of a directory that should also be synchronized.

Foreign keys to Comarch ERP Standard schema

For synchronized tables

Object availability, permissions, and similar factors, may affect which data is synchronized at the row level in a given table. For instance, this refers to customers, customer groups, warehouses, accounts, payment forms, etc.

In order to avoid multiple evaluations of which data should be synchronized in derived tables (based on FK), the POS.SentObjects table should be used.

Export of tax definitions associated with a customer:

(select
        ven.Id as [@Id],
        ven.ActivityId as [@ActivityId],
        ven.VendorId as [@VendorId]
        from Implementations.VendorActivityConnectionsEcoTax ven
        inner join Implementations.ActivityEcoTax ac on ven.ActivityId = ac.Id
        inner join Implementations.SettingsEcoTax sett on ac.Id = sett.CompanyActivityId and sett.CompanyId = @companyUnitId 
        inner join POS.SentObjects so on so.ObjectId = ven.VendorId and so.SyncTypeId = 14 and so.POSId = @pointOfSaleId
        where ven.Timestamp > @rowVersion 
                        for xml path('VendorActivityConnectionsEcoTax'), type)

The value of a synchronized object type may be found in the POS.SyncTypes table.

For non-synchronized tables

You need to implement the synchronization process yourself, as if the table came from an extension.

Deleting data

Actions in Comarch ERP Standard

a) In the DeletionTypes table, add an entry with the Id >= 1000 and the unique name of the type of deleted objects.

b) In the AFTER DELETE trigger of a table whose data deletion should be synchronized in POS, add entries about the deleted objects in the DeletedObjects table. Columns to be filled in:

• DeletionTypeId – it is the identifier of a type defined in pt. a)
• Ident – it is the identifier of a deleted object. It can be a number (int), GUID (uniqueidentifier), nvarchar, or a set of values separated with the “|” character, e.g. „3428|654”. Also, see the point discussing actions in POS (IdentColumnName, IdentColumnType columns).
• POSId – the identifier of the POS workstation (Synchronization.PointsOfSales) – it should be filled in if a given object should be deleted only from a particular POS workstation; leave it as NULL if the object should be deleted from all workstations.

Actions in Comarch POS

In the Synchronization.DeletionTypes table, add a row defining the method in which the synchronization mechanism is to handle information on object deletion originating from the ERP system.

Two deletion modes are available: automatic and custom.

• In the automatic mode, the synchronization mechanism will automatically delete data from a given table, identifying rows on the basis of the entered column names (enter appropriate values in the columns TableName, IdentColumnName, and IdentColumnType; also, enter NULL in the CustomProcName column). It is used to delete almost all object types in POS that are deletable in the ERP system (see the standard entries in the Synchronization.DeletionTypes table)
• In turn, the custom mode requires defining a procedure handling the deletion of objects of a given type (the CustomProcName column must be filled in). This mode is used if a deletion condition is more complicated and the automatic mode cannot be applied or if additional operations must be performed upon deletion.

The Synchronization.DeletionTypes table contains the following columns:

• DelType – the same name as in the DeletionTypes table of the Comarch ERP Standard database
• Order – a number defining the order of deletion of object types
• TableName [automatic mode only] – the name of a table from which objects are to be automatically deleted as part of a given DelType type
• IdentColumnName [automatic mode only] – the names of columns (separated with the “|” character) according to which the automatic identification of deleted rows is to be performed, e.g. “Id”, “GUID”, “PriceTypeId|CustomerGroupId”. Their number and order must be consistent with a value entered in the POS.DeletedObjects.Ident column in the Comarch ERP Standard database.
• IdentColumnType [automatic mode only] – the types of columns defined as IdentColumnName, with the same number and order, e.g. “int”, “uniqueidentifier”, “int|int”. For the nvarchar(x) type, enter “nvarchar”.
• CustomProcName [custom mode only] – the name of a procedure responsible for deleting objects of a given type. It should use data from the temporary table #DeletedObjects. See the existing procedures Synchronization.DeleteCustomerPriceTypes and Synchronization.DeleteWarehouseDocuments.