DB restore production DB to DEV environment in D365 FO using Bacpac file

 

https://www.linkedin.com/pulse/restore-production-database-dev-environment-d365fo-using-abbas-tfwbe

SQLPackage link : https://www.linkedin.com/redir/redirect?url=https%3A%2F%2Fgo%2Emicrosoft%2Ecom%2Ffwlink%2F%3Flinkid%3D2261576&urlhash=8Q0l&trk=article-ssr-frontend-pulse_little-text-block

Step1. Need to download SQL package

Stop all the required services

Rename the AXDB to AXDB Old

Run the command to connect to package extracted path

Then Run the below command. To import

==

SqlPackage.exe /a:import /sf:C:\SQLPackage\carlsbergbalkanchinabackup.bacpac /tsn:vm-WE-MD09-d-1 /tdn:AxDB /p:CommandTimeout=200000 /TargetEncryptConnection:False

tsn (target server name) – The name of the SQL Server to import into.

tdn (target database name) – The name of the database to import into. The database should not already exist.

sf (source file) – The path and name of the file to import from.

/p:CommandTimeout=200000 seconds approximately 55 hours timeout for command

/TargetEncryptConnectionLFalse this is the updated script which will keep us away from certificate errors

After DB import run the below script

CREATE USER axdeployuser FROM LOGIN axdeployuser

EXEC sp_addrolemember 'db_owner', 'axdeployuser'

CREATE USER axdbadmin FROM LOGIN axdbadmin

EXEC sp_addrolemember 'db_owner', 'axdbadmin'

CREATE USER axmrruntimeuser FROM LOGIN axmrruntimeuser

EXEC sp_addrolemember 'db_datareader', 'axmrruntimeuser'

EXEC sp_addrolemember 'db_datawriter', 'axmrruntimeuser'

CREATE USER axretaildatasyncuser FROM LOGIN axretaildatasyncuser

CREATE USER axretailruntimeuser FROM LOGIN axretailruntimeuser

CREATE USER axdeployextuser FROM LOGIN axdeployextuser

CREATE USER [NT AUTHORITY\NETWORK SERVICE] FROM LOGIN [NT AUTHORITY\NETWORK SERVICE]

EXEC sp_addrolemember 'db_owner', 'NT AUTHORITY\NETWORK SERVICE'

UPDATE T1

SET T1.storageproviderid = 0

    , T1.accessinformation = ''

    , T1.modifiedby = 'Admin'

    , T1.modifieddatetime = getdate()

FROM docuvalue T1

WHERE T1.storageproviderid = 1 --Azure storage

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking

DROP PROCEDURE IF EXISTS SP_ConfigureTablesForChangeTracking_V2

GO

-- Begin Refresh Retail FullText Catalogs

DECLARE @RFTXNAME NVARCHAR(MAX);

DECLARE @RFTXSQL NVARCHAR(MAX);

DECLARE retail_ftx CURSOR FOR

SELECT OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) fullname FROM SYS.FULLTEXT_INDEXES

    WHERE FULLTEXT_CATALOG_ID = (SELECT TOP 1 FULLTEXT_CATALOG_ID FROM SYS.FULLTEXT_CATALOGS WHERE NAME = 'COMMERCEFULLTEXTCATALOG');

OPEN retail_ftx;

FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

BEGIN TRY

    WHILE @@FETCH_STATUS = 0 

    BEGIN 

        PRINT 'Refreshing Full Text Index ' + @RFTXNAME;

        EXEC SP_FULLTEXT_TABLE @RFTXNAME, 'activate';

        SET @RFTXSQL = 'ALTER FULLTEXT INDEX ON ' + @RFTXNAME + ' START FULL POPULATION';

        EXEC SP_EXECUTESQL @RFTXSQL;

        FETCH NEXT FROM retail_ftx INTO @RFTXNAME;

    END

END TRY

BEGIN CATCH

    PRINT error_message()

END CATCH

CLOSE retail_ftx; 

DEALLOCATE retail_ftx; 

-- End Refresh Retail FullText Catalogs

--Begin create retail channel database record--

declare @ExpectedDatabaseName nvarchar(64) = 'Default';

declare @DefaultDataGroupRecId BIGINT;

declare @ExpectedDatabaseRecId BIGINT; 

IF NOT EXISTS (select 1 from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName)

BEGIN 

select @DefaultDataGroupRecId = RECID from RETAILCDXDATAGROUP where NAME = 'Default'; 

insert into RETAILCONNDATABASEPROFILE (DATAGROUP, NAME, CONNECTIONSTRING, DATASTORETYPE)

values (@DefaultDataGroupRecId, @ExpectedDatabaseName, NULL, 0); 

select @ExpectedDatabaseRecId = RECID from RETAILCONNDATABASEPROFILE where NAME = @ExpectedDatabaseName; 

insert into RETAILCDXDATASTORECHANNEL (CHANNEL, DATABASEPROFILE)

select RCT.RECID, @ExpectedDatabaseRecId from RETAILCHANNELTABLE RCT

inner join RETAILCHANNELTABLEEXT RCTEX on RCTEX.CHANNEL = RCT.RECID

        update RETAILCHANNELTABLEEXT set LIVECHANNELDATABASE = @ExpectedDatabaseRecId where LIVECHANNELDATABASE = 0

END; 

---------------

Restart the required services.
Refresh models and build the Models

How to add the Display method in the form level in D365 F&O

 The below examle is to add the form -Data source level adding display method and assigning to form design control.

AXmodel file import in D365 F&O

 ModelUtil.exe -import -metadatastorepath=k:\aosservice\packageslocaldirectory -file=C:\Temp\DEST.axmodel

X++ code to get the time and add and substract using the current date time in D365 F&O

 utcdatetime dateTime = DateTimeUtil::getSystemDateTime();//DateTimeUtil::utcNow();

        utcdatetime newDateTime = DateTimeUtil::addMinutes(dateTime, -20);

        Info(strFmt("Current time: %1 redueced Time: %2 ",dateTime,newDateTime));

X++ code to create new user in D365 F&O

 public void createUser(UserId _userId,Name _userName)

{

    UserInfo userInfo;

    SecurityUserRole userRole,userRoleToSave;

    SecurityRole    securityRole;

   

    // Step 1: Create new User Info record

    ttsBegin;

    userInfo.id             = _userId;

    userInfo.Name           = _userName;

    userInfo.networkAlias   = Xine_UserAcessTable.Email;

    userInfo.networkDomain  = Xine_UserAcessTable.NetworkDomain;

    userInfo.ObjectId       = Xine_UserAcessTable.TelemetryId;        

    userInfo.enable         = Xine_UserAcessTable.Enabled;

    userInfo.insert(); // Save the user info record

    info(strFmt('User created: %1', userInfo.id));

    select securityRole

        where securityRole.Name == "System user";

    select forupdate userRoleToSave 

        where userRoleToSave.User == _userId &&

                userRoleToSave.SecurityRole == securityRole.recId;

    if ( userRoleToSave.recId)

    {

        userRoleToSave.AssignmentMode = RoleAssignmentMode::Manual;

        userRoleToSave.AssignmentStatus = RoleAssignmentStatus::Enabled;

        userRoleToSave.update();

    }

    else

    {

        userRole.User = _userId;

        userRole.SecurityRole = securityRole.recId; // Set the role of the user (System User role is used here as an example)

        userRole.AssignmentMode = RoleAssignmentMode::Manual;

        userRole.AssignmentStatus = RoleAssignmentStatus::Enabled;

        userRole.insert(); // Save user role

        info(strFmt('Role assigned to user: %1', userRole.SecurityRole));

    }

    // Commit the transaction

    ttsCommit;

}

Ledger Voucher creation Framework and x++ code to create ledger voucher

 Please click her for MS reference file

Below is the out of the box example reference and code.

SalesInvoiceJournalPostSubBill_Extension->endPost()

this.postUnbilledRevenueAndRevenueOffset();

LedgerVoucher ledgerVoucher = this.ledgerVoucher;

                LedgerVoucherObject ledgerVoucherObject = ledgerVoucher.findLedgerVoucherObject();

                LedgerDimensionAccount unbilledRevenueAccount = LedgerDimensionFacade::serviceCreateLedgerDimension(unbilledRevenueTransactionLine.SubBillUnbilledRevenueAccount, salesLine.DefaultDimension);

                LedgerDimensionAccount unbilledRevenueOffsetAccount = LedgerDimensionFacade::serviceCreateLedgerDimension(unbilledRevenueTransactionLine.SubBillUnbilledRevenueOffsetAccount, salesLine.DefaultDimension);

                CurrencyExchangeHelper exchangeRateHelper = this.getExchangeRateHelper(ledgerVoucherObject, detailLine.SubBillSchedLineRecId);

                Amount revenueOffsetAmt = abs(custInvoiceTrans.lineAmount) + custInvoiceTrans.SumLineDisc;

                Amount revenueAmt = (revenueOffsetAmt * -1);

                // Unbilled Revenue

                LedgerVoucherTransObject ledgerVoucherTransObject = LedgerVoucherTransObject::newTransactionAmountDefault(

                            ledgerVoucherObject,

                            LedgerPostingType::SalesRevenue,

                            unbilledRevenueAccount,

                            custInvoiceTrans.CurrencyCode,

                            revenueAmt,

                            exchangeRateHelper);

                ledgerVoucherTransObject.parmText("@SubBill:UnbilledRevenueAccount");

                ledgerVoucher.addTrans(ledgerVoucherTransObject);

                // Unbilled Revenue Offset

                // Dr. Accounts Receivable + Dr. Unbilled discount offset = Cr. Unbilled Revenue offset

                ledgerVoucherTransObject = LedgerVoucherTransObject::newTransactionAmountDefault(

                            ledgerVoucherObject,

                            LedgerPostingType::SalesRevenueOffset,

                            unbilledRevenueOffsetAccount,

                            custInvoiceTrans.CurrencyCode,

                            revenueOffsetAmt,

                            exchangeRateHelper);

                ledgerVoucherTransObject.parmText("@SubBill:UnbilledRevenueOffsetAccount");

                ledgerVoucher.addTrans(ledgerVoucherTransObject);

                ledgerVoucherObject.lastTransTxt();

Trial balance export to Azure Blob Storage

The file will be automatically generated from D365 using a batch job.

The trial balance file should include all dimensions. (Main-Dept-Cost center-Business unit-Value stream)

/// contract class to export file in azure blob storag

/// </summary>

[DataContractAttribute,

SysOperationContractProcessing(classStr(NPHFloQastExportUIBuilder))]

internal final class ExportContract extends SysOperationAttributedDataContractInfo

{

    private FromDate            fromDate;

    private ToDate              toDate;

    private DateCode            period;

    private Name                primaryDimensionFocus;

    private DimensionSetName    dimensionSetName;

    [DataMemberAttribute("@SYS2168")]

    public FromDate parmFromDate(FromDate _fromDate = fromDate)   

 {

        fromDate = _fromDate;

        return _fromDate;

    }

    [DataMemberAttribute("@SYS22882")]

    public ToDate parmToDate(ToDate _toDate = toDate)

    {

        toDate = _toDate;

        return _toDate;

    }

    [DataMemberAttribute("@SYS22717")]

    public DateCode parmPeriod(DateCode _period = period)

    {

        period = _period;

        return _period;

    }

    [DataMemberAttribute("@SYS83218")]

    public DimensionSetName parmDimensionSetName(DimensionSetName _dimensionSetName = dimensionSetName)

    {

        dimensionSetName = _dimensionSetName;

        return dimensionSetName;

}

}

Create Controller class

internal final class ExportController extends SysOperationServiceController

{

    public static void main(Args args)

    {

         ExportController sysOperationController = ExportController::construct();

        sysOperationController.parmClassName(classStr(ExportService));

        sysOperationController.parmMethodName(methodStr(ExportService, operation));

        sysOperationController.parmExecutionMode(SysOperationExecutionMode::Synchronous);

        sysOperationController.startOperation();

    }

    private static ExportController construct()

    {

        ExportController controller = new ExportController();

        controller.parmDialogCaption("Caption");

        return controller;

    }

    public ClassDescription caption()

    {

        return "Caption";

    }

    protected boolean canRunInNewSession()

    {

        return true;

    }

    /// <summary>

    ///

    /// </summary>

    /// <param name = "_dialogCaption"></param>

    /// <returns></returns>

    public LabelType parmDialogCaption(LabelType _dialogCaption = dialogCaption)

    {

        return "Caption";

    }

}    

Crate UI builder class 

internal final class ExportUIBuilder extends SysOperationAutomaticUIBuilder

{

    DialogField                         dialogPeriodName;

    DialogField                         dialogFromDate;

    DialogField                         dialogToDate;

    DialogField                         dialogDimset    ;

    private DateCode            period;

    private FromDate            fromDate;

    private ToDate              toDate;

    private DimensionSetName    dimensionSetName;

    ExportContract                  dataContract;

       

       public void build()

    {

        Dialog dialogLocal = dialog as Dialog;        

        dataContract = this.getContract();

        dialogPeriodName = this.addDialogField(methodstr(ExportContract,parmPeriod),dataContract);

        dialogPeriodName.label("@SYS22717");

        dialogPeriodName.lookupButton(2);

        dialogFromDate  = this.addDialogField(methodstr(ExportContract,parmFromDate),dataContract);

        dialogToDate    = this.addDialogField(methodstr(ExportContract,parmToDate),dataContract);

        dialogDimset    = this.addDialogField(methodstr(ExportContract,parmDimensionSetName),dataContract);

    }

    public boolean periodModified(FormStringControl _control)

    {

        DateCode            dateCode        ;

        LedgerPeriodCode    ledgerPeriodCode;

       

        dialogPeriodName.value(_control.valueStr());

       

        dateCode = dialogPeriodName.value();

        ledgerPeriodCode = ledgerPeriodCode::find(dateCode);

        dialogFromDate.value(ledgerPeriodCode.fromDate());

        dialogToDate.value(ledgerPeriodCode.toDate());

        return true;

    }

    [Hookable(false)]

    public void postRun()

    {

        super();

    

        dialogPeriodName.value(this.getContract().parmPeriod());

        dialogPeriodName.fieldControl().mandatory(true);

        dialogPeriodName.registerOverrideMethod(

            methodStr(FormStringControl, modified),

            methodStr(ExportUIBuilder, periodModified),

            this);

    

        dialogFromDate.value(this.getContract().parmFromDate());

        dialogFromDate.allowEdit(false);

        dialogToDate.value(this.getContract().parmToDate());

        dialogToDate.allowEdit(false);

        dialogDimset.value(this.getContract().parmDimensionSetName());

    }

    private ExportContract getContract()

    {

        if (dataContract == null)

        {

            dataContract = this.dataContractObject() as ExportContract;

        }

        return dataContract;

    }

}

/// <summary>

/// 15933 - Flocast integration

/// exports file in azure blob storage

/// </summary>

using Microsoft.WindowsAzure.Storage;

using System.Text;

create service  class 

internal final class ExportService

{

    int                 records             ;

    Filename            currentFileName     ;

    date                startDate           ;

    date                endDate             ;

    PeriodName          periodName          ;

    LedgerParameters    ledgerParameters    ;

    DimensionSetName    dimensionSetName    ;

    public void new()

    {

        LedgerPeriodCode            ledgerPeriodCode    ;

        super();

        

        ledgerPeriodCode    = ledgerPeriodCode::find("CP");

        startDate           = ledgerPeriodCode.fromDate()   ;

        endDate             = ledgerPeriodCode.toDate()     ;

        periodName          = ledgerPeriodCode.Code         ;

    }

    public void operation(ExportContract _dataContract)

    {

        System.Exception ex;

        periodName          = _dataContract.parmPeriod()            ;   

        startDate           = _dataContract.parmFromDate()          ;

        endDate             = _dataContract.parmToDate()            ;

        dimensionSetName    = _dataContract.parmDimensionSetName()  ;

        ledgerParameters = LedgerParameters::find();

        Filename    filename  = ledgerParameters.FileName ? ledgerParameters.FileName : "GLAccounts";

       

        str timestamp = strReplace(time2str(timeNow(),TimeSeparator::Colon, TimeSeparator::Colon),":","_");

        currentFileName     = strFmt("%1_%2_%3_%4.csv",filename,

                                                    date2str(startDate,321,2,DateSeparator::None,2,DateSeparator::None,4),

                                                    date2str(endDate,321,2,DateSeparator::None,2,DateSeparator::None,4),

                                                    timestamp);

                                                    //time2str(timeNow(),TimeSeparator::Colon, TimeSeparator::Colon));

        try

        {

            this.runExport();

            info(strFmt("@Newport:NPHFloQastRecords",records, currentFileName));

        }

        catch (Exception::CLRError)

        {

            ex = CLRInterop::getLastException();

            if (ex != null)

            {

                error(ex.ToString());

            }

        }

        catch (Exception::CodeAccessSecurity)

        {

            error("@Newport:NPHFloQastFileIssue");

        }

    }

    TextStreamIo openFile()

    {

        FileIOPermission perm;

        ;

        info("file: " + currentFileName);

        perm = new FileIOPermission(currentFilename, "w");

        if (perm == null)

        {

            return null;

        }

        perm.assert();

        TextStreamIo fileOut = TextStreamIo::constructForWrite();

        return fileOut;

    }

    void runExport()

    {

        DimensionHierarchy                      dimHier                 ;

        LedgerTrialBalanceTmp                   ledgerTrialBalanceTmp   ;

        LedgerTrialBalanceListPageBalanceParms  balanceParameters       ;

        List postingLayers = new List(Types::Enum);

        postingLayers.addEnd(currentOperationsTax::Current);

        dimHier = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, dimensionSetName);

        balanceParameters   = LedgerTrialBalanceListPageBalanceParms::construct();

        balanceParameters.loadDefault();

        balanceParameters.parmPostingLayers(postingLayers);

        balanceParameters.parmStartDate(startDate);

        balanceParameters.parmEndDate(endDate);

        balanceParameters.parmDimensionSetHierarchy(dimHier);

        //info(strFmt("@SYS2168" + "%1", startDate));

        //info(strFmt("@SYS22882" + "%1", endDate));

        select firstOnly RecId from LedgerTrialBalanceTmp;

        LedgerTrialBalanceTmp::calculateBalances_V2(

            ledgerTrialBalanceTmp,

            dimHier.Name,

            balanceParameters.getStartDate(),

            balanceParameters.getEndDate(),

            balanceParameters.getIncludeOpeningPeriods(),

            balanceParameters.getIncludeClosingAdjustments(),

            balanceParameters.getIncludeClosingTransactions(),

            balanceParameters.getPostingLayers(),

            true);

        this.writeFile(ledgerTrialBalanceTmp);

    }

    // 15933_Flocast integration 

    private void writeFile(LedgerTrialBalanceTmp _ledgerTrialBalanceTmp)

    {

        str                             recordStr       ;

        MainAccount                     mainAccount     ;

        AccountCategory                 accountCategory ;

        str                             hasActivity     ;

        container                       con;

        recordStr =  strFmt("CostCenter,MainAccount,BusinessUnit,ValueStrem,Department,Name,OpeningBalance,Debit,Credit,ClosingBalance");

        con += recordStr;

        while select _ledgerTrialBalanceTmp

            //join  mainAccount           where mainAccount.MainAccountId == _ledgerTrialBalanceTmp.DimensionValues[0]

        {

            accountCategory = mainAccount.accountCategory(false,"");

            if (this.isSuspended(mainAccount))

            {

                continue; 

            }

            if (_ledgerTrialBalanceTmp.AmountCredit == 0 && _ledgerTrialBalanceTmp.AmountDebit == 0)

            {

                hasActivity = "FALSE";

            }

            else

            {

                hasActivity = "TRUE";

            }

            recordStr =  strFmt("%1,%2,%3,%4,%5,%6,%7,%8,%9,%10",

                            _ledgerTrialBalanceTmp.DimensionValues[1],

                            _ledgerTrialBalanceTmp.DimensionValues[2],

                            _ledgerTrialBalanceTmp.DimensionValues[3],

                            _ledgerTrialBalanceTmp.DimensionValues[4],

                            _ledgerTrialBalanceTmp.DimensionValues[5],

                            _ledgerTrialBalanceTmp.PrimaryFocusDescription,

                            num2Str(_ledgerTrialBalanceTmp.OpeningBalance,16,2,DecimalSeparator::Dot, ThousandSeparator::None),

                            num2Str(_ledgerTrialBalanceTmp.AmountDebit,16,2,DecimalSeparator::Dot, ThousandSeparator::None),

                            num2Str(_ledgerTrialBalanceTmp.AmountCredit,16,2,DecimalSeparator::Dot, ThousandSeparator::None),

                            num2Str(_ledgerTrialBalanceTmp.EndingBalance,16,2,DecimalSeparator::Dot, ThousandSeparator::None)

                             );

                          

                             

            con += recordStr;

            records++;

            recordStr   = "";

        }

        //Info(strFmt("%1", con));

        this.uploadFileToAzureBlob(con);

    }

    Suspended isSuspended(MainAccount _mainAccount)

    {

        DimensionAttributeValue             dimAttrValue;

        DimensionAttribute                  mainAccountDimAttribute;

        Suspended                           isMainAccountSuspended;

        mainAccountDimAttribute.RecId =  DimensionAttribute::getWellKnownDimensionAttribute(DimensionAttributeType::MainAccount);

        dimAttrValue = DimensionAttributeValue::findByDimensionAttributeAndEntityInst(mainAccountDimAttribute.RecId, _mainAccount.RecId, true);

        isMainAccountSuspended = dimAttrValue.IsSuspended;

        return isMainAccountSuspended;

    }

    public void uploadFileToAzureBlob(container _lineData)

    {

        var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials(ledgerParameters.StorageAccountName, ledgerParameters.FloQastAzureStorageAccountKey);

        CloudStorageAccount storageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true);

        Microsoft.WindowsAzure.Storage.Blob.CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();

        Microsoft.WindowsAzure.Storage.Blob.CloudBlobContainer container1 = blobClient.GetContainerReference(ledgerParameters.FloQastBlobStorageEndpointContainerName);

        Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob blockBlob = container1.GetBlockBlobReference(currentFileName);

        StringBuilder csvData = new StringBuilder();

        for(int j = 1; j<= conLen(_lineData); j++)

        {

            csvData.AppendLine(con2Str(conPeek(_lineData,j)));

        }

        str dataToUpload = csvData.ToString();

        using (var ms = new System.IO.MemoryStream(System.Text.Encoding::get_UTF8().GetBytes(dataToUpload)))

        {

            blockBlob.UploadFromStream(ms,null,null,null);

        }

    }

}

Add parameters for Azure Account