Activate Financial Dimensions in Dynamics 365FO

 

Activate Financial Dimensions 

How to resolve the error when activating financial dimension in D365 FO – “The activation of dimensions is only allowed when the system is in maintenance mode. Maintenance mode can be enabled in this environment by running maintenance jobs from LCS or using the Deployment Setup tool locally.

So to fix this issue or to activate the financial dimension, you need to enable the maintenance mode on your targeted environment. only then you can activate the Financial Dimension.

There are 3 ways to enable maintenance mode.

• LCS
• Command Line
• Query

LCS

Login to LCS, Navigate to your environment, Go to Maintain menu and click on enable maintenance mode.

Command Line

Open the command windows as Systems Administrator and execute the below command.

K:\AOSService\PackagesLocalDirectory\Bin\Microsoft.Dynamics.AX.Deployment.Setup.exe –metadatadir K:\AOSService\PackagesLocalDirectory –bindir C:\AOSService\PackagesLocalDirectory –sqlserver . –sqldatabase axdb –sqluser AOSUser –sqlpwd AOSWebSite@123 –setupmode maintenancemode –isinmaintenancemode true

SQL Script

Please perform the following steps 

• Disable the IIS services.
• Disable the Batch job services
• Open SQL and Execute the below command on AxDB

Update SQLSystemvariables set Value=0 where parm='CONFIGURATIONMODE'

How to disable/hide report/Batch Job parameter on Dialogue

 

How to disable/hide report/Batch Job parameter on Dialogue

To hide the parameter from report/job dialogue, use the below annotation on the param method of the Report/Job data contract class.

[SysOperationControlVisibilityAttribute(false)].

Sample

Parameter will visible:

    [DataMemberAttribute,

    SysOperationLabelAttribute(literalStr("Task Size")),

    SysOperationGroupMemberAttribute("ParameterGroup"),

    SysOperationDisplayOrderAttribute("3")]

    public AD_BatchTaskSize parmBatchTaskSize(AD_BatchTaskSize _batchTaskSize = batchTaskSize)

    {

        batchTaskSize = _batchTaskSize;

        return batchTaskSize;

    }

Parameter will not visible:

    [DataMemberAttribute,

    SysOperationControlVisibilityAttribute(false)]

    public RecId parmFromRecId(RecId _fromRecId = fromRecId)

    {

        fromRecId = _fromRecId;

        return fromRecId;

    }

Database sync failed during Dimension Activation

 

Database sync failed during Dimension Activation

If you are facing the Database sync failure and below error while activating the Financial dimension in maintenance mode.

Please perform the following steps to resolve this issue

You need to fix your data. Go to the Visual studio find inventdim table then check what fields are in "DimIdx" index.

Then Open the SQL and execute the below query to confirm either you have duplicate Invent Dim or nor

select InventDimId from INVENTDIM

group by InventDimId

having count(*)>1

When records will be identified you need to make a decision on what to do with them. fix data or delete.  

Error Message

Managed Data Sync Entity Worker encountered an exception, but is continuing because ContinueOnError is true. View Name and ID: Managed Data Sync Entity Worker encountered an exception, but is continuing because ContinueOnError is true. View Name and ID: 'DimensionCombinationEntity(5502)' Exception: System.InvalidOperationException: Database execution failed: Incorrect syntax near ')'.

 Incorrect syntax near ')'.

 CREATE VIEW [DBO].[DIMENSIONCOMBINATIONENTITY] AS SELECT T1.RECID AS RECORDID, T1.MAINACCOUNTVALUE AS MAINACCOUNT, T1.MODIFIEDDATETIME AS MODIFIEDDATETIME, T1.MODIFIEDBY AS MODIFIEDBY, T1.CREATEDDATETIME AS CREATEDDATETIME, T1.CREATEDBY AS CREATEDBY, T1.RECVERSION AS RECVERSION, T1.PARTITION AS PARTITION, T1.RECID AS RECID, T2.NAME AS ACCOUNTSTRUCTURE, T2.RECVERSION AS RECVERSION#2, T2.PARTITION AS PARTITION#2, T2.RECID AS RECID#2, (CAST ((

                 SELECT (

                     REPLACE(

                         REPLACE(

                             REPLACE(

                                 CASE WHEN

                                     T1.LEDGERDIMENSIONTYPE != 1

                                 THEN

                                     (SELECT CASE WHEN 

                                         NonNullDisplayValue IS NOT NULL 

                                         THEN NonNullDisplayValue 

                                         ELSE 'No active format for data entities has been set up.

 Set up an active format for each dimension format type.' 

                                     END

                                     FROM

                                     (SELECT (

                                         -- Remove initial delimiter

                                         STUFF(

                                             (SELECT

                                                 -- Prepend with delimiter and escape internal delimiters

                                                 DIMENSIONSEGMENTDELIMITER + COALESCE(

                                                         REPLACE(REPLACE(DIMLIST.COLUMNVALUE, '\', '\\'),

                                                         DIMENSIONSEGMENTDELIMITER,

                                                         DIMENSIONSEGMENTDELIMITERESCAPED),

                                                     '')

                                                 FROM 

                                                     -- Get the dimension hierarchy

                                                     (SELECT TOP(1) DH.RECID

                                                         FROM DIMENSIONHIERARCHY DH

                                                         JOIN DIMENSIONHIERARCHYINTEGRATION DHI 

                                                                     ON DHI.DIMENSIONHIERARCHY = DH.RECID

                                                                     AND DHI.ISDEFAULT = 1

                                                                     AND DHI.PARTITION = T1.PARTITION

                                                         WHERE

                                                             DH.PARTITION = T1.PARTITION

                                                             AND DH.STRUCTURETYPE =

                                                                         CASE T1.LEDGERDIMENSIONTYPE

                                                                             WHEN 0 THEN 18

                                                                             WHEN 2 THEN 19

                                                                             WHEN 4 THEN 20

                                                                         END

                                                     ) AS DH

                                                 JOIN DIMENSIONHIERARCHYLEVEL DHL 

                                                     ON DHL.DIMENSIONHIERARCHY = DH.RECID AND DHL.PARTITION = T1.PARTITION

                                                 JOIN DIMENSIONATTRIBUTE DA 

                                                     ON DA.RECID = DHL.DIMENSIONATTRIBUTE AND DA.PARTITION = T1.PARTITION

                                                 LEFT JOIN

                                                         (SELECT DIMENSIONATTRIBUTE, COLUMNVALUE

                                                             FROM

                                                                 (SELECT * FROM DIMENSIONATTRIBUTEVALUECOMBINATION DAVC

                                                                     WHERE DAVC.PARTITION = T1.PARTITION AND DAVC.RECID = T1.RECID) P

                                                             UNPIVOT

                                                                 (COLUMNVALUE for DIMENSIONATTRIBUTE IN (MAINACCOUNTVALUE)) AS DIMENSIONVALUECOLUMNNAME

                                                         ) AS DIMLIST

                                                     ON

                                                         DIMLIST.DIMENSIONATTRIBUTE = REPLACE(DA.DIMENSIONVALUECOLUMNNAME COLLATE Database_Default, '.', '$')

                                                 ORDER BY DHL.DIMENSIONHIERARCHY, DHL.LEVEL_

                                                 FOR XML PATH('')

                                             ),

                                             1, LEN(DIMENSIONSEGMENTDELIMITER), '')

                                         ) AS NonNullDisplayValue)

                                     AS DisplayValueFormat)

                                 ELSE 

                                     -- Default or dynamic accounts

                                     (SELECT REPLACE(REPLACE(T1.DISPLAYVALUE, '\', '\\'),

                                                     DIMENSIONSEGMENTDELIMITER,

                                                     DIMENSIONSEGMENTDELIMITERESCAPED))

                                 END

                                 , '&', '&')

                             , '&lt;', '<')

                         , '&gt;', '>')

                     )

                 FROM

                     (SELECT TOP(1)

                             --Determine the segment delimiter

                             CASE DIMENSIONSEGMENTDELIMITER

                                 WHEN 0 THEN '-'

 WHEN 1 THEN '.'

 WHEN 2 THEN '_'

 WHEN 3 THEN '|'

 WHEN 7 THEN '||'

 WHEN 8 THEN '~'

 WHEN 4 THEN '--'

 WHEN 5 THEN '..'

 WHEN 6 THEN '__'

 WHEN 9 THEN '~~'

                             END AS DIMENSIONSEGMENTDELIMITER,

                             --Determine the account delimiter escaped

                                 WHEN 0 THEN '\-'

 WHEN 1 THEN '\.'

 WHEN 2 THEN '\_'

 WHEN 3 THEN '\|'

 WHEN 7 THEN '\|\|'

 WHEN 8 THEN '\~'

 WHEN 4 THEN '\-\-'

 WHEN 5 THEN '\.\.'

 WHEN 6 THEN '\_\_'

 WHEN 9 THEN '\~\~'

                             END AS DIMENSIONSEGMENTDELIMITERESCAPED

                         FROM DIMENSIONPARAMETERS

                         WHERE DIMENSIONPARAMETERS.PARTITION = T1.PARTITION

                     ) AS DIMENSIONPARAMETERS

                  ) AS NVARCHAR(500))) AS DISPLAYVALUE, (CAST ((T1.DISPLAYVALUE) AS NVARCHAR(30))) AS ACCOUNTVALUE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS BANKACCOUNTID, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS CLIENTORGANIZATIONUNIT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS DELIVERYCHANNEL, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS FINALCLIENT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS INTERCOMPANY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSKILLSET, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBINDUSTRY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBSERVICELINE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PL_PODATEK, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PROGRAMWBS, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS VENDORLEGALENTITY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALCOSTCENTER FROM DIMENSIONATTRIBUTEVALUECOMBINATION T1 LEFT OUTER JOIN DIMENSIONHIERARCHY T2 ON(( T1.ACCOUNTSTRUCTURE  =  T2.RECID)  AND ( T1.PARTITION  =  T2.PARTITION)) ---> System.Data.SqlClient.SqlException:                 ) AS NVARCHAR(500))) AS DISPLAYVALUE, (CAST ((T1.DISPLAYVALUE) AS NVARCHAR(30))) AS ACCOUNTVALUE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS BANKACCOUNTID, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS CLIENTORGANIZATIONUNIT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS DELIVERYCHANNEL, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS FINALCLIENT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS INTERCOMPANY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSKILLSET, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBINDUSTRY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBSERVICELINE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PL_PODATEK, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PROGRAMWBS, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS VENDORLEGALENTITY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALCOSTCENTER FROM DIMENSIONATTRIBUTEVALUECOMBINATION T1 LEFT OUTER JOIN DIMENSIONHIERARCHY T2 ON(( T1.ACCOUNTSTRUCTURE  =  T2.RECID)  AND ( T1.PARTITION  =  T2.PARTITION)) ---> System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)

    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()

    at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)

    at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.<ExecuteNonQuery>b__30_0(SqlCommand c)

    at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)

 --- End of stack trace from previous location where exception was thrown ---

    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

    at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.HandleException(ExceptionDispatchInfo edi, SqlExecutionInfo execInfo)

    at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(IEnumerable`1 commandTextInBatch)

    --- End of inner exception stack trace ---

    at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText)

    at Microsoft.Dynamics.AX.Data.Management.DataEntity.ManagedSyncDataEntityWorker.SyncAllAction[T](ViewSyncGraphNode viewToSync, Dictionary`2 oldViewDefinitions, StringCollection dataEntityViewList, StringCollection compositeEntityViewList, IProducerConsumerCollection`1 modifiedViewDependencies)

    at Microsoft.Dynamics.AX.Data.Management.DataEntity.ManagedSyncDataEntityWorker.DoSyncAll[T](IEnumerable`1 sortedViewList, Dictionary`2 oldViewDefinitions, IProducerConsumerCollection`1 modifiedViewDependencies)

  AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: Full sync did not complete successfully. Error: AggregateException:One or more errors occurred.

    at Microsoft.Dynamics.AX.Data.Management.ManagedSyncWorkerManager.SyncAll()

    at Microsoft.Dynamics.AX.Data.Management.ManagedSync.<SyncAll>b__21_0()

    at Microsoft.Dynamics.AX.Data.Management.ManagedSync.EnsureMetadataProviderCacheCleared(Action action)

    at Microsoft.Dynamics.AX.Data.Management.ManagedSync.SyncAll()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullManagedSync(SyncOptions options, String sqlConnectionString, IMetadataProvider metadataProvider)

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c__DisplayClass20_0.<RunFullTableSync>b__0()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler)

 Inner exceptions:

  Database execution failed: Database execution failed: Incorrect syntax near ')'.

                 ) AS NVARCHAR(500))) AS DISPLAYVALUE, (CAST ((T1.DISPLAYVALUE) AS NVARCHAR(30))) AS ACCOUNTVALUE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS BANKACCOUNTID, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS CLIENTORGANIZATIONUNIT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS DELIVERYCHANNEL, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS FINALCLIENT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS INTERCOMPANY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSKILLSET, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBINDUSTRY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBSERVICELINE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PL_PODATEK, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PROGRAMWBS, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS VENDORLEGALENTITY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALCOSTCENTER FROM DIMENSIONATTRIBUTEVALUECOMBINATION T1 LEFT OUTER JOIN DIMENSIONHIERARCHY T2 ON(( T1.ACCOUNTSTRUCTURE  =  T2.RECID)  AND ( T1.PARTITION  =  T2.PARTITION))

    at Microsoft.Dynamics.AX.Data.Management.DataEntity.ManagedSyncDataEntityWorker.DoSyncAll[T](IEnumerable`1 sortedViewList, Dictionary`2 oldViewDefinitions, IProducerConsumerCollection`1 modifiedViewDependencies).

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c.<RunFullTableSync>b__20_1(Tuple`2 result)

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullTableSync(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider)

    at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullTableViewSync()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullSync()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options)

  Process: syncengine.exe exited with code -1.

Access Fields In A Table Using This Field Id

 

How To Access Field In A Table Using This Field Id

To do this you could use the next construction:

vendTable.( filednum( vendTable, VendAccount ) ) = “XXX”;

This is the same as

vendTable.VendAccount = “XXX”;

Example:

Field VendAccount from VendTable has id 1. Then statement VendTable.(1) = “V-00014” – will initialize field VendAccount with value “V-00014”.

You could also use this feature while working with records of type Common.

Example:

    Common      common;
    VendTable    vendTable;
    ;
    select vendTablewhere vendTable.VendAccount== "V-00014";
    common = vendTable;
    info( common.( fieldnum( vendTable, VATNum) ) );

Skip/Bypass validation in Data Entity Import – D365FO

 

Skip/Bypass validation in Data Entity Import – D365FO

I had a scenario where I need to create the Tax Exempt Number dynamically when importing Vendors' data.

For instance, if the given VatNum does not exist in the input data, the system should bypass the validation and create the vendor without any error. 

So, how do we incorporate this validation? Using the COC method of persistEntity.

public void persistEntity(DataEntityRuntimeContext _entityCtx)

{   

    

        next persistEntity(_entityCtx);

     this.skipDataSourceValidateField(fieldNum(VendVendorsV2,

     VatNum),true);

    

}

Entity cannot be deleted while dependent Entities for a processing group exist.

 

An entity cannot be deleted while dependent Entities for a processing group exist.

Sometimes you need to delete a data object from the main data object. Sometimes this is annoying because it is specified in a data project and you need to delete this report before you can delete the master data. Sometimes you have to go through hundreds of data projects and it's very annoying.

So, Go to the Data entity page, find your data entity and click on processing group linked.

Now select the processing group and delete the references of the targeted entity.

You can delete the entity, Once all the references are removed.

The 'Dimension Legal Entity Context field' must be entered

 

The 'Dimension Legal Entity Context field' must be entered, when the extended data type is 'DimensionDynamicAccount' or 'DimensionDynamicDefaultAccount'.

Today, I was getting the following errors while creating one data entity, after searching over google I realized that two field-level properties on my entity should be filled.

• DimensionLegalEntityContextField
• DynamicDimensionEnumerationField

Path: [AxDataEntityView/ABCDEntity/Fields/LedgerDimension/DimensionLegalEntityContextField]:The 'Dimension Legal Entity Context field' must be entered, when the extended data type is 'DimensionDynamicAccount' or 'DimensionDynamicDefaultAccount'.

Path: [AxDataEntityView/ABCDEntity/Fields/LedgerDimension/DynamicDimensionEnumerationField]:The 'Dynamic Dimension Enumeration Field' must be entered, when the extended data type is 'DimensionDynamicAccount' or 'DimensionDynamicDefaultAccount'. Solution DimensionLegalEntityContextField This field property should be filled with Data Area Id, for reference, I also pasted the screenshot. DynamicDimensionEnumerationField This field property should be filled with either 'DimensionDynamicAccount' or 'DimensionDynamicDefaultAccount for reference, I also pasted the screenshot. Options: DimensionDynamicAccount DimensionDynamicDefaultAccount

Export All Legal entities data using One project in D365FO

Export all legal entities data 

 

In D365FO, if an export project is created for an entity, it exports the data only from the legal entity for which the user has triggered an export.

Setting the property ‘Enable cross-company in the base query of the entity will not help you here.

The behaviour is decided by one of the properties in the Data Entity.

You need to check the value of ‘Primary company context‘ in the entity. By default, it is set to ‘DataAreaId’. This means the data will be exported specifically to the company.

Clear the value from this property if the requirement is to export data from all the legal entities in a single execution.

Customer Address book relation for channel Database D365FO

Customer Address book relation for channel Database D365FO

Scenario:

Most of our D365FO Fellow are working with Retail where we sync customer with our channel database, and sometime we need to update the customer from POS as well, Sometime channel DB throw exception when while updating the customer information if AddressBook of the customer  customer is not properly set.

Its happens when we create customer using AX client or import them via DMF and didn't set the address book value.


So no need to worry using below code & query you can update the customers address book in bulk.

Image:

Code:

public void AddrssBookRelation()

    {

        DirAddressBookParty       dirAddressBookParty;

        DirAddressBook                   dirAddressBook;

        CustTable                              custTable;

        select RecId,Party from custTable

            where custTable.AccountNum==this.CustomerAccount;

        if(custTable)

        {

            select RecId from dirAddressBook

            where dirAddressBook.Name==this.CountryCode;

            if(dirAddressBook)

            {

                select RecId from dirAddressBookParty

                    where dirAddressBookParty.Party==custTable.Party && dirAddressBookParty.AddressBook==dirAddressBook.RecId;

                if(dirAddressBookParty.RecId==0)

                {

                    dirAddressBookParty.clear();

                    dirAddressBookParty.Party=custTable.Party;

                    dirAddressBookParty.AddressBook=dirAddressBook.RecId;

                    dirAddressBookParty.insert();

                }

            }

        }

    }

SQL Query:

INSERT INTO DirAddressBookParty (PARTY,ADDRESSBOOK,PARTITION)
select DISTINCT DirPartyLocation.PARTY,DirAddressBook.RECID,5637144576 from LogisticsPostalAddress
join DirPartyLocation on DirPartyLocation.LOCATION=LogisticsPostalAddress.LOCATION
Join LOGISTICSLOCATION on LOGISTICSLOCATION.RECID=LogisticsPostalAddress.LOCATION
join DirAddressBook on DirAddressBook.NAME=LogisticsPostalAddress.COUNTRYREGIONID
where DirPartyLocation.ISPRIMARY=1 and party in (select party from CUSTTABLE)
and LOGISTICSLOCATION.ISPOSTALADDRESS=1 and DirPartyLocation.ISPOSTALADDRESS=1 and DirPartyLocation.POSTALADDRESSROLES='Business'
and not exists (select 1 from DirAddressBookParty where DirAddressBookParty.PARTY=DirPartyLocation.PARTY and DirAddressBookParty.ADDRESSBOOK=DirAddressBook.RECID)
 and LogisticsPostalAddress.VALIDTO >=GETDate()

Customer Cancel Payment Transaction

Customer Cancel Payment Transaction 

Oftentimes Technical consultants receive the requirement to write some logic or feature where we can cancel the X number of payments by selecting many records at once.


class TMV_CustomerPaymentCancel
{

   

    public static void main(Args _args)
    {
        LedgerJournalTrans _ledgerJournalTrans;
        TMV_CustomerPaymentCancel objCancelPayment=new TMV_CustomerPaymentCancel();
        objCancelPayment.cancelpaymentExecution('R00007404',_ledgerJournalTrans);
    }

    public void cancelpaymentExecution(InvoiceId _invoiceid,LedgerJournalTrans _ledgerJournalTrans)
    {

        CustTrans custTransOffset;
       
        ReasonTable reasonTable;
        ReasonCode reasonCode;
        ReasonRefRecID reasonRefRecID;
     
        Args args;
 
        ;
        custTransOffset= this.findPaymentReference(_invoiceid);
        if(custTransOffset.RecId>0)
        {
            this.CancelPayment(custTransOffset, _ledgerJournalTrans);
        }

    }

    public CustTrans findPaymentReference(InvoiceId _invoiceid)
    {
        CustTrans custTrans,custTransOffset;

        custTrans = CustTrans::findFromInvoice(_invoiceid);
        select custTransOffset
            where custTransOffset.OffsetRecid==custTrans.RecId
            && custTransOffset.TransType==LedgerTransType::Payment;
        return custTransOffset;
    }

    public void CancelPayment(CustTrans custTransOffset,LedgerJournalTrans _ledgerJournalTrans)
    {
        CustVendPDCManager  custVendPDCManager;
        BankPaymCancel bankPaymCancel;
        Args    localArgs;
        if(custTransOffset.RecId)
        {
 
            infolog.clear();
            try
            {
                localArgs = new Args();

                bankPaymCancel = BankPaymCancel::newBankPaymCancel(custTransOffset);

                //  bankPaymCancel.parmReason(reasonTable.Reason);
                //   bankPaymCancel.parmReasonComment(reasonTable.Description);
                bankPaymCancel.parmTransDate(systemDateGet());
       
                localArgs.caller(bankPaymCancel);
                localArgs.record(custTransOffset);
                BankPaymCancel::serverRun(localArgs);
 
            }
            catch
            {
               


            }
           
        }
    }



    public  str getErrorStr()
    {
        SysInfologEnumerator enumerator;
        SysInfologMessageStruct msgStruct;
        Exception exception;
        str error;
        enumerator = SysInfologEnumerator::newData(infolog.cut());
        while (enumerator.moveNext())
        {
            msgStruct = new SysInfologMessageStruct(enumerator.currentMessage());
            exception = enumerator.currentException();
            error = strfmt("%1 %2", error, msgStruct.message());
        }
        return error;
    }

}

Reverse customer Posted Invoice

Reverse customer Posted Invoice 

Using below code you can reverse the posted invoice of the customer.


class DemoTransationReversal extends TransactionReversal_Cust
{
    public static DemoTransationReversal construct()
    {
        return new DemoTransationReversal();
    }

    public boolean showDialog()
    {
        return false;
    }

    public static void main(Args _args)
    {
        CustTrans custTrans;
        DemoTransationReversal demoTransationReversal;
        ReasonTable reasonTable;
        ReasonCode reasonCode;
        ReasonRefRecID reasonRefRecID;
        InvoiceId invoiceId;
        Args args;
        ;

        invoiceId = "0099";
        reasonCode = "DEMO Purpose";       
        reasonTable = ReasonTable::find(reasonCode);
        reasonRefRecID = ReasonTableRef::createReasonTableRef(
            reasonTable.Reason, reasonTable.Description);

        custTrans = CustTrans::findFromInvoice(invoiceId);
           
        if (custTrans.RecId && !custTrans.LastSettleVoucher)
        {
            args = new Args();
            args.record(custTrans);

            demoTransationReversal = DemoTransationReversal::construct();
            demoTransationReversal.parmReversalDate(systemDateGet());
            demoTransationReversal.parmReasonRefRecId(reasonRefRecID);
            demoTransationReversal.reversal(args);
           
            info(strFmt("%1 %2 %3 %4 reversed.",
                custTrans.Voucher,
                custTrans.TransDate,
                custTrans.Invoice,
                custTrans.Txt));
        }       
    }
}

SQL In Operator in D365FO

SQL In Operator in D365FO

Microsoft Introduce In Operator in X++ Syntax, but it will work with Enums only.

Following are the example how you can use this

SalesTable  salesTable;

container   con = [SalesType::Sales, SalesType::ReturnItem, SalesType::Subscription];

while select SalesId from salesTable

where salesTable.SalesType in con

{

Info(salesTable.SalesId);

}