How to add new fields to CDX

If you need to add new tables/fields to CDX seed data functionality.

- create subscription to RetailCDXSeedDataBase.registerCDXSeedDataExtension method:

/// <summary>
/// Class created to extend retail CDX Seed data functionality
/// </summary>
class SOLRetailCDXSeedDataExtension
{
    /// <summary>
    /// Delegate to add custom fields/tables to CDX seed data job and subjob definitions.
    /// </summary>
    /// <param name="originalCDXSeedDataResource"> Needed to check schema you want to apply change to </param>
    /// <param name="resources"> Needed to add new resource to the list </param>
    [SubscribesTo(classStr(RetailCDXSeedDataBase), delegateStr(RetailCDXSeedDataBase, registerCDXSeedDataExtension))]
    public static void RetailCDXSeedDataBase_registerCDXSeedDataExtension(str _originalCDXSeedDataResource, List _resources)
    {
        if(_originalCDXSeedDataResource == resourceStr(RetailCDXSeedDataAX7))
        {
            _resources.addEnd(resourceStr(SOLRetailCDXSeedDataAX7Extension));
        }
    }

}

- create new XML resource SOLRetailCDXSeedDataAX7Extension

<RetailCdxSeedData Name="AX7" ChannelDBSchema="ax" ChannelDBMajorVersion="7">
  <Jobs>
  </Jobs>
    <Subjobs>
        <Subjob Id="RetailParameters" AxTableName="RetailParameters" TargetTableName="SOLRetailParameters" TargetTableSchema="ext">
          <AxFields>
            <Field Name="SOLServiceURL" />
            <Field Name="SOLEnabled" />
          </AxFields>
        </Subjob>
        <Subjob Id="SOLFlightCodesTable" AxTableName="SOLFlightCodesTable" TargetTableName="SOLFlightCodesTable" TargetTableSchema="ext">
            <ScheduledByJobs>
                <ScheduledByJob>1010</ScheduledByJob>
            </ScheduledByJobs>
            <AxFields>
                <Field Name="FlightNum" />
                <Field Name="Airline" />
                <Field Name="RecId" />
            </AxFields>
        </Subjob>
    </Subjobs>
</RetailCdxSeedData>

There are two cases:

• First subjob adds additional fields to existing RetailParameters table and therefore there no need to add ShceduledByJobs tag nodes
• Second subjob adds new table and you have to specify under which job it will work

Please take a note that on POS side in both cases fields should be created in new tables in new schema. POS developer should prepare SQL script, which:

• Create new schema
  
• Create new table
• In case of fields added to standard table: create view, which joins data from standard table in ax schema and newly created table in extended (ext) schema
• Grand permission to newly created table to DataSyncUsersRole
  

SQL Example:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'ext')
BEGIN
EXEC('CREATE SCHEMA ext')
END
GO

--RETAIL PARAMETERS
IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'ext' 
                 AND  TABLE_NAME = 'SOLRETAILPARAMETERS'))
BEGIN
    CREATE TABLE [ext].[SOLRETAILPARAMETERS](
    [KEY] [int] NOT NULL,
    [DATAAREAID] [nvarchar](4) NOT NULL,
    [SOLENABLED] [int] NOT NULL,
    [SOLSERVICEURL] [nvarchar](255) NOT NULL,
    CONSTRAINT [PK_EXT_SOLRETAILPARAMETERS] PRIMARY KEY CLUSTERED 
    (
        [KEY] ASC,
        [DATAAREAID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


END
GO

IF NOT EXISTS(
SELECT object_definition(default_object_id) AS definition
FROM   sys.columns
WHERE  name      ='key'
AND    object_id = object_id('ext.solretailparameters')
)
BEGIN
    ALTER TABLE [ext].[SOLRETAILPARAMETERS] ADD  CONSTRAINT [DF_RETAILPARAMETERS_KEY]  DEFAULT ((0)) FOR [KEY]
END
GO

IF NOT EXISTS(SELECT object_definition(default_object_id) AS definition
FROM   sys.columns
WHERE  name      ='SOLENABLED'
AND    object_id = object_id('ext.SOLRETAILPARAMETERS'))
BEGIN
    ALTER TABLE [ext].[SOLRETAILPARAMETERS] ADD  DEFAULT ('0') FOR [SOLENABLED]
END
GO

IF NOT EXISTS(SELECT object_definition(default_object_id) AS definition
FROM   sys.columns
WHERE  name      ='SOLSERVICEURL'
AND    object_id = object_id('ext.SOLRETAILPARAMETERS'))
BEGIN
    ALTER TABLE [ext].[SOLRETAILPARAMETERS] ADD  DEFAULT ('') FOR [SOLSERVICEURL]
END
GO


IF OBJECT_ID(N'[ext].[SOLRETAILPARAMETERSVIEW]', N'V') IS NOT NULL
BEGIN
    DROP VIEW [ext].[SOLRETAILPARAMETERSVIEW]
END
GO

CREATE VIEW [ext].[SOLRETAILPARAMETERSVIEW] AS
(
    SELECT 
      rp.[RECID]
      ,rp.[MIXANDMATCHACTIVITYTYPE]
      ,rp.[AUTOMATICRETURNOFLOYALTYPAYMENT_RU]
      ,rp.[AWARDPOINTSFORPARTIALREDEMPTION_RU]
      ,rp.[CANCELLATIONCHARGE]
      ,rp.[CANCELLATIONCHARGECODE]
      ,rp.[DEFAULTCUSTOMERPOSTING]
      ,rp.[DEFAULTORDERTYPE]
      ,rp.[DISCOUNTOFFERACTIVITYTYPE]
      ,rp.[EANLICENSENO]
      ,rp.[ELECTRONICDELIVERYMODECODE]
      ,rp.[EXPIRATIONDATE]
      ,rp.[GIFTCARDITEM]
      ,rp.[ITEMLABELLASTACTION]
      ,rp.[ITEMLABELSFORNEGATIVESTOCK]
      ,rp.[ITEMSALESSTATISTICSON]
      ,rp.[KEY]
      ,rp.[MINIMUMDEPOSITFORSALESORDER]
      ,rp.[MINIMUMPASSWORDLENGTH]
      ,rp.[MULTIBUYACTIVITYTYPE]
      ,rp.[PAYMENTSTATISTICS]
      ,rp.[PICKUPDELIVERYMODECODE]
      ,rp.[POSTCUSTDISC]
      ,rp.[POSTINFOCODEDISC]
      ,rp.[POSTLINEDISC]
      ,rp.[POSTPERIODICDISC]
      ,rp.[POSTTOTALDISC]
      ,rp.[PROCESSGIFTCARDSASPREPAYMENTS_RU]
      ,rp.[PROCESSRETURNSASINORIGINALSALESHIFT_RU]
      ,rp.[PROPOSEREFUNDPAYMENTAMOUNT_RU]
      ,rp.[RECEIPTOPTION]
      ,rp.[REQUIRENUMERICCHAR]
      ,rp.[REQUIRESPECIALCHAR]
      ,rp.[REQUIREUPPERCASE]
      ,rp.[SHELFLABELLASTACTION]
      ,rp.[SHIPPINGCHARGECODE]
      ,rp.[STAFFSTATISTICS]
      ,rp.[TERMINALSTATISTICS]
      ,rp.[USEGIFTCARDPOLICIES]
      ,rp.[LOCKOUTTHRESHOLD]
      ,rp.[LOCKOUTDURATION]
      ,rp.[LOGINCOUNTERRESETDURATION]
      ,rp.[PASSWORDEXPIRYINTERVAL]
      ,rp.[PASSWORDEXPIRYNOTIFICATIONTHRESHOLD]
      ,rp.[DISCOUNTALGORITHMMODE]
      ,rp.[MAXBESTDEALSTEPCOUNT]
      ,rp.[REFUNDSHIPPINGAMTWITHOUTAPPR]
      ,rp.[REFUNDSHIPPINGCHARGES]
      ,rp.[ENABLEPRODUCTRECOMMENDATIONS]
      ,rp.[HOLDTOGETHERFORDISCOUNTROUNDING]
      ,rp.[CARRYOUTDELIVERYMODECODE]
      ,rp.[CUSTOMERATTRIBUTEGROUP]
      ,rp.[DATAAREAID]
      ,erp.[SOLENABLED]
      ,erp.[SOLSERVICEURL]
    FROM            
        ax.RETAILPARAMETERS rp
        INNER JOIN [ext].[SOLRETAILPARAMETERS] erp 
            ON rp.[KEY] = erp.[KEY]
            AND rp.[DATAAREAID] = erp.[DATAAREAID]
)
GO

GRANT SELECT ON [ext].[SOLRETAILPARAMETERSVIEW] TO [UsersRole];
GO

GRANT INSERT, DELETE, UPDATE, SELECT ON OBJECT::[ext].[SOLRetailParameters] TO [DataSyncUsersRole];
GO

- Build solution
- Go to Retail -> Headquarters setup -> Retail scheduler -> Initialize retail scheduler

Run the job

- Go to Retail -> Headquarters setup -> Retail scheduler -> Scheduler subjobs

Make sure your table or new fields exist in the list of subjobs or channel field mapping

- Go to Retail -> Retail IT -> Distribution schedule

Find required job and press "Run now" button

Then go to "History" and click "Download file"

In the downloaded zip file find the table and make sure it contain the data you want to sync.

- Wait till it gets status Applied instead of Available

- Go to SQL Management studio and check data availability in [ext].[SOLRetailParameters] table

More information can be found here.

---

Support The Author

 If you found value in what I share, I've set up a Buy Me a Coffee page as a way to show your support.

Buy Me a Coffee