Inventory Onhand by warehouse for Non-WMS & WMS warehouse in D365FO

 

Table: WHSInventReserve

or

View: WHSInventReserveView

Range: 

HierarchyLevel -> 

0 (Item level)

1 (Site level)

2 (Warehouse level)

...

So on

Field to return value for On hand qty

AvailPhysical

Copy User information from one to another (Role & Assign organization permission & Usage data) from SQL in D365FO

INSERT INTO SECURITYUSERROLE (USER_, SECURITYROLE, ASSIGNMENTMODE, ASSIGNMENTSTATUS, VALIDFROM, VALIDTO, VALIDFROMTZID, VALIDTOTZID)

SELECT '<to user>',SECURITYROLE, ASSIGNMENTMODE, 

ASSIGNMENTSTATUS,VALIDFROM, VALIDTO, VALIDFROMTZID, VALIDTOTZID FROM SECURITYUSERROLE 

WHERE USER_ in ('<from user>') ;

INSERT INTO omUserRoleOrganization (USER_, OMHIERARCHYTYPE, OMINTERNALORGANIZATION, SECURITYROLE, SECURITYROLEASSIGNMENTRULE)

SELECT '<to user>',OMHIERARCHYTYPE, OMINTERNALORGANIZATION, 

SECURITYROLE,SECURITYROLEASSIGNMENTRULE FROM omUserRoleOrganization 

WHERE USER_ in ('<from user>');

INSERT INTO SYSLASTVALUE (USERID, RECORDTYPE, ELEMENTNAME, DESIGNNAME, ISKERNEL, COMPANY,VALUE,DOCUMENT)

SELECT '<to user>',RECORDTYPE, ELEMENTNAME, 

DESIGNNAME,ISKERNEL,COMPANY,VALUE,DOCUMENT FROM SYSLASTVALUE 

WHERE USERID in ('<from user>');

Confirm PO without Purchase confirmation journal in D365FO X++

 VersioningPurchaseOrder::newPurchaseOrder(<purchTable>).confirm();

Logic:

[ExtensionOf(classStr(VersioningPurchaseOrder))]

final class VersioningPurchaseOrderCls_SAN_Extension

{

    protected boolean isChangeConfirmationRequired(Common _newRecord, Common _oldRecord)

    {

        boolean ret;

        PurchTable   purchTableloc;

        TableId     tableId = (_oldRecord ? _oldRecord.TableId : _newRecord.TableId);

        ret = next isChangeConfirmationRequired(_newRecord, _oldRecord);

        switch (tableId)

        {

            case tableNum(PurchTable):

                purchTableLoc = _newRecord;

                if (purchTableLoc.IsIntLog)

                {

                    ret = false;

                }

        }

        return ret;

    }

}

Business event related tables in D365FO (For migration)

 BusinessEventsEndpoint, 

BusinessEventsAzureEndpoint (For Azure endpoint), 

BusinessEventsFlowEndpoint (For Flow endpoint), 

BusinessEventsConfiguration (Activate related with company)

Entity:

BusinessEventsConfigurationEntityV2 (Business events configuration)

BusinessEventsEndpointEntityV2 (Business events endpoint)

SQL Keywords

STRING_AGG

SQL Query:

select STRING_AGG(erc.Code, ';') as ProductLine From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Output: 

2;7;13;G;9;3;Z;11;17;D;B;F;8;6;1;05

Execute:

Execute ('

Declare @val Varchar(MAX)

Select @val = COALESCE(@val + '','' + erc.Code, erc.Code) 

        From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Group by erc.Code order by erc.Code asc

Select @val')

COALESCE

Declare @val Varchar(MAX)

Select @val = COALESCE(@val + ';' + erc.Code, erc.Code) 

        From EcoResCategory erc

join EcoResProductCategory erpc

on erc.RecId = erpc.Category 

join INVENTTABLE it

on erpc.Product  = it.PRODUCT

where  it.ITEMID  = 'TST702'  and erc.Code  != '' and it.DATAAREAID = 'USMF' 

Group by erc.Code order by erc.Code asc

Select @val

Get ER format mapping from Print management destination setting in D365FO X++

ERFormatMappingId                   formatMapping;

 str defaultReportName = PrintMgmtDocType::construct(PrintMgmtDocumentType::SAN_CommercialInvoice).getDefaultReportFormat();

        if(defaultReportName)

        {

            int prefixLength = strLen(ERPrintMgmtSetupUISettingsCtlEventHandler::PrintManagementFormatPrefix);

            var formatGuid = str2Guid(subStr(defaultReportName, prefixLength + 1, strLen(defaultReportName) - prefixLength));

            formatMapping = ERFormatMappingTable::findByGUID(formatGuid).RecId;

        }

Add fields on orders for intercompany synchronization in D365FO X++

Custom fields:

Synchronization additional fields changes to get reflect on "Intercompany chain." as well. 

Custom Fields from tables:

PurchTable , PurchLine, SalesTable, SalesLine

Method to override: interCompanyUpdateNow()

Sample method code:

    /// <summary>

    /// Determines whether an intercompany order line should be updated. COC

    /// </summary>

    /// <returns>

    /// true if the intercompany order line should be updated; otherwise, false.

    /// </returns>

    boolean interCompanyUpdateNow()

    {

        boolean     sanReturn;        

        sanReturn= next interCompanyUpdateNow();

        if(!sanReturn

            && (this.orig().san_RefTransId    != this.san_RefTransId ))

        {

            sanReturn= true;

        }

        return sanReturn;

    }

 

Add a new parm method to the class AxPurchLine, AxPurchTable, AxSalesTable, AxSalesLine for the new field to be synchronized.

Then create COC extension method for class method

Class: InterCompanySyncPurchLineType, Method ->setSalesLineData

Class: PurchLineType, Method -> syncSalesLine

Class: InterCompanySyncSalesLineType , Method -> setPurchaseOrderLineData

Class: SalesLineType , Method -> syncPurchLine

Class:  InterCompanySyncPurchTableType, Method -> setSalesTableData

Class:  PurchTableType, Method -> SyncSalesTable

Class: InterCompanySyncSalesTableType, Method -> setPurchTableData

Class: SalesTableType, Method -> SyncPurchTable

Sample:

[ExtensionOf(classStr(InterCompanySyncPurchLineType))]

final class InterCompanySyncPurchLineTypeCls_SAN_Extension

{

    protected void setSalesLineData()

    {

        next setSalesLineData();

        axSalesLine.san_RefTransId(purchLine.san_RefTransId);

    }
}

Inventory marking & UnMarking in D365FO X++

 //UnMark

InventTransOrigin::deleteMarking(InventTransOrigin::findByInventTransId(<CurPOInventTransId>).RecId,

                        InventTransOrigin::findByInventTransId(<RefSOInventTransId>).RecId,

                       <Qty>);

//Mark

InventTransOrigin::updateMarking(<CurPOInventTransId>, <RefSOInventTransId>, -1* <Qty>);

Picking qty for sales order line in D365FO X++

 Class SAN_PickingQtyOrder

{

    public static void DoPickQtyForOrderLine(SalesLine  _SalesLine, Qty  _qtyPicked)

    {

        InventTransWMS_Pick         inventTransWMS_Pick;

        TmpInventTransWMS           tmpInventTransWMS;

        InventDim                   inventDim;

        InventTrans                 inventTrans;

        Query                       query;           

 

        inventTrans = SAN_PickingQtyOrder::locateSalesLineAndInventTrans(_SalesLine.SalesId, _SalesLine.LineNum);       

 

        if(inventTrans)

        {

            query = SAN_PickingQtyOrder::initInventTransQuery(_SalesLine);

            inventTransWMS_Pick = InventTransWMS_Pick::newStandard(tmpInventTransWMS, query

            inventTrans.Qty = 0 - _qtyPicked; // Quantity needs to be negative.

            inventTransWMS_Pick.createFromInventTrans(inventTrans);

            boolean ret = inventTransWMS_Pick.updateInvent();

            if (!ret)

            {

                throw error("An error occurred when marking IC sales order lines as picked");

            }

        }

        else

        {

            throw error("@Retail:InventTransRecordNotFound");

        }

    }

 

    private static InventTrans locateSalesLineAndInventTrans(SalesId salesId, LineNum salesLineNumber)

    {

        InventTransOrigin   inventTransOrigin;

        InventTrans         inventTrans;

        SalesLine           salesLine;

 

        select firstonly inventTrans

            join RecId from inventTransOrigin where inventTransOrigin.RecId == inventTrans.InventTransOrigin && InventTrans.StatusIssue == StatusIssue::ReservPhysical

            join salesLine where salesLine.InventTransId == inventTransOrigin.InventTransId && salesLine.SalesId == salesId && salesLine.LineNum == salesLineNumber;

 

        if (!inventTrans)

        {

            select firstonly inventTrans

            join RecId from inventTransOrigin where inventTransOrigin.RecId == inventTrans.InventTransOrigin && InventTrans.StatusIssue == StatusIssue::ReservOrdered

            join  salesLine where salesLine.InventTransId == inventTransOrigin.InventTransId && salesLine.SalesId == salesId && salesLine.LineNum == salesLineNumber;

        }        

 

        if (!inventTrans)

        {

            select firstonly inventTrans

            join RecId from inventTransOrigin where inventTransOrigin.RecId == inventTrans.InventTransOrigin && InventTrans.StatusIssue == StatusIssue::OnOrder

            join salesLine where salesLine.InventTransId == inventTransOrigin.InventTransId && salesLine.SalesId == salesId && salesLine.LineNum == salesLineNumber;

        }

        return inventTrans;

    }

 

    private static Query initInventTransQuery(SalesLine salesLine)

    {

        Query                   query;

        InventDimParm           inventDimParmActive;

        InventMovement          inventMovement;

        QueryBuildDataSource    qbdsInventTrans;

        InventTransOriginId     inventTransOriginId;

 

 

 

        query = new Query();

        qbdsInventTrans = query.addDataSource(tableNum(InventTrans));

        qbdsInventTrans.clearDynalinks();

        qbdsInventTrans.clearRanges();

        qbdsInventTrans.addRange(fieldNum(InventTrans,StatusReceipt)).value(SysQuery::value(StatusReceipt::None));

        qbdsInventTrans.addRange(fieldNum(InventTrans,StatusIssue)).value(SysQuery::range(StatusIssue::Picked,StatusIssue::OnOrder));

        inventMovement      = InventTransWMS_Pick::inventMovement(salesLine);

        inventTransOriginId = inventMovement.inventTransOriginId();

 

        SAN_PickingQtyOrder::setInventTransOriginQueryRange(qbdsInventTrans, salesLine, inventMovement, inventTransOriginId);

        inventMovement.inventDimGroupSetup().inventDimParmActive(inventDimParmActive);

        return query;

    }

 

    private static void setInventTransOriginQueryRange(

            QueryBuildDataSource _qbdsInventTrans,

            Common               _callerTable,

            InventMovement       _inventMovement,

            InventTransOriginId  _inventTransOriginId = _inventMovement ? _inventMovement.inventTransOriginId() : 0)

    {

        QueryBuildRange         qbRange;

        InventTransChildType    inventTransChildType;

        InventTransChildRefId   inventTransChildRefId;

        str                     rangeStr;

        int                     inventTransChildTypeInt;

 

        if (_qbdsInventTrans)

        {

            if (_inventMovement)

            {

                if (_inventMovement.transChildType())

                {

                    inventTransChildType  = _inventMovement.transChildType();

                    inventTransChildRefId = _inventMovement.transChildRefId();

                }

                else

                {

                    inventTransChildType  = InventTransChildType::None;

                    inventTransChildRefId = '';

                }

            }

            inventTransChildTypeInt = enum2int(inventTransChildType); 

            qbRange = _qbdsInventTrans.addRange(fieldNum(InventTrans,InventTransOrigin));

            rangeStr = strFmt('((%1.%2 == %3) && (%4.%5 == %6)',

                                     _qbdsInventTrans.name(),

                                     fieldStr(InventTrans,InventTransOrigin),

                                     queryValue(_inventTransOriginId),

                                     _qbdsInventTrans.name(),

                                     fieldStr(InventTrans,TransChildType),

                                     inventTransChildTypeInt);

            if (inventTransChildRefId)

            {

                rangeStr = rangeStr + strFmt(' && (%1.%2 == \"%3\"))',

                                     _qbdsInventTrans.name(),

                                     fieldStr(InventTrans,TransChildRefId),

                                     queryValue(inventTransChildRefId));

            }

            else

            {

                rangeStr = rangeStr + strFmt(' && (%1.%2 == \"\"))',

                                     _qbdsInventTrans.name(),

                                     fieldStr(InventTrans,TransChildRefId));

            }

            qbRange.value(rangeStr);

        }

    }

}

 

New Print management related logic in D365FO X++

/// <summary>

/// Event handler class to define customize reprot list based on new print management type

/// </summary>

class SAN_PrintMgmtDocTypeCustAccStatementEventHandlers

{

    [SubscribesTo(classStr(PrintMgmtDocType), delegateStr(PrintMgmtDocType, getDefaultReportFormatDelegate))]

    public static void getDefaultReportFormatDelegate(PrintMgmtDocumentType _docType, EventHandlerResult _result)

    {

        switch(_docType)

        {

            case PrintMgmtDocumentType::SAN_CustAccountStatement:

                _result.result(ssrsReportStr(SAN_CustAccountStatementsExt, Report));

                break;

        }

    }

    /// <summary>

    /// Delegate handler for the getQueryRangeFieldsDelegate method of the <c>PrintMgmtDocType</c> class.

    /// </summary>

    /// <param name = "_docType"><c>PrintMgmtDocumentType</c> enumeration value.</param>

    /// <param name = "_result">The <c>EventHandlerResult</c> object.</param>

    [SubscribesTo(classstr(PrintMgmtDocType), delegatestr(PrintMgmtDocType, getQueryRangeFieldsDelegate))]

    public static void getQueryRangeFieldsDelegateHandler(PrintMgmtDocumentType _docType, EventHandlerResult _result)

    {

        List fields = new List(Types::Integer);

        if(_docType == PrintMgmtDocumentType::SAN_CustAccountStatement)

        {

            fields.addEnd(fieldNum(CustTable, AccountNum));

            fields.addEnd(fieldNum(CustTable, CustGroup));

            fields.addEnd(fieldNum(CustTable, AccountStatement));

            _result.result(fields);

        }

    }

    /// <summary>

    /// Delegate handler for the getPartyTypeDelegate method of the <c>PrintMgmtDocType</c> class.

    /// </summary>

    /// <param name = "_docType"><c>PrintMgmtDocumentType</c> enumeration value.</param>

    /// <param name = "_jour"><c>Common</c> object containing journal record.</param>

    /// <param name = "_result">The <c>EventHandlerResult</c> object.</param>

    [SubscribesTo(classstr(PrintMgmtDocType), delegatestr(PrintMgmtDocType, getPartyTypeDelegate))]

    public static void getPartyTypeDelegateHandler(PrintMgmtDocumentType _docType, Common _jour, EventHandlerResult _result)

    {

        if (_docType == PrintMgmtDocumentType::SAN_CustAccountStatement)

        {

            _result.result(PrintMgmtPrintDestinationPartyType::Customer);

        }

    }

    /// <summary>

    /// Delegate handler for the getQueryTableIdDelegate method of the <c>PrintMgmtDocType</c> class.

    /// </summary>

    /// <param name = "_docType"><c>PrintMgmtDocumentType</c> enumeration value.</param>

    /// <param name = "_result">The <c>EventHandlerResult</c> object.</param>

    [SubscribesTo(classstr(PrintMgmtDocType), delegatestr(PrintMgmtDocType, getQueryTableIdDelegate))]

    public static void getQueryTableIdDelegateHandler(PrintMgmtDocumentType _docType, EventHandlerResult _result)

    {

        if (_docType == PrintMgmtDocumentType::SAN_CustAccountStatement)

        {

            _result.result(tableNum(CustTable));

        }

    }

}

/// <summary>

/// The <c>FormLetterReport</c> class is used to retrieve the print management settings for the

/// <c>PrintMgmtDocumentType</c> enumeration value.

/// </summary>

[PrintMgmtDocumentTypeFactoryAttribute(PrintMgmtDocumentType::SAN_CustAccountStatement)]

class SAN_SalesFormLetterReport_CustAccountStatement extends SalesFormLetterReport

{

    /// <summary>

    /// Gets the default printer settings for the specified <c>PrintSetupOriginalCopy</c> enumeration value.

    /// </summary>

    /// <param name="_printCopyOriginal">

    /// The <c>PrintSetupOriginalCopy</c> enumeration value that specifies whether the Original or Copy

    /// destinations should be retrieved.

    /// </param>

    /// <returns>

    /// The default printer settings for the specified <c>PrintSetupOriginalCopy</c> enumeration value.

    /// </returns>

    protected container getDefaultPrintJobSettings(PrintSetupOriginalCopy _printCopyOriginal)

    {

        SRSPrintDestinationSettings printSettings = PrintMgmtSetupSettings::initDestination();

        printSettings.printMediumType(SRSPrintMediumType::Screen);

        return printSettings.pack();

    }

    /// <summary>

    /// Gets the <c>PrintMgmtDocumentType</c> enumeration value that specifies the document that this

    /// <c>FormLetterReport</c> class controls.

    /// </summary>

    /// <returns>

    /// The <c>PrintMgmtDocumentType</c> enumeration value that specifies the document that this

    /// <c>FormLetterReport</c> class controls.

    /// </returns>

    /// <remarks>

    /// This value is used to retrieve the appropriate Print Management settings for the report.

    /// </remarks>

    public PrintMgmtDocumentType getPrintMgmtDocumentType()

    {

        return PrintMgmtDocumentType::SAN_CustAccountStatement;

    }

    /// <summary>

    /// Gets the <c>PrintMgmtHierarchyType</c> enumeration value that specifies the hierarchy that this

    /// <c>FormLetterReport</c> class uses.

    /// </summary>

    /// <returns>

    /// The <c>PrintMgmtHierarchyType</c> enumeration value that specifies the hierarchy that this

    /// <c>FormLetterReport</c> class uses.

    /// </returns>

    /// <remarks>

    /// This value is used to retrieve the appropriate Print Management settings for the report.

    /// </remarks>

    protected PrintMgmtHierarchyType getPrintMgmtHierarchyType()

    {

        return PrintMgmtHierarchyType::Sales;

    }

    /// <summary>

    /// Gets the <c>PrintMgmtNodeType</c> enumeration value that specifies the node that this

    /// <c>FormLetterReport</c> class uses.

    /// </summary>

    /// <returns>

    /// The <c>PrintMgmtNodeType</c> enumeration value that specifies the node that this

    /// <c>FormLetterReport</c> class uses.

    /// </returns>

    /// <remarks>

    /// This value is used to retrieve the appropriate Print Management settings for the report.

    /// </remarks>

    protected PrintMgmtNodeType getPrintMgmtNodeType()

    {

        return PrintMgmtNodeType::CustTable;

    }

}

/// <summary>

/// Extension class for PrintMgmtDelegatesHandler to handle custom print management type

/// </summary>

[ExtensionOf(classStr(PrintMgmtDelegatesHandler))]

final class PrintMgmtDelegatesHandlerCls_SAN_Extension

{

    /// <summary>

    /// Determines the party type for a journal records and the current document type.

    /// </summary>

    /// <param name="_jour">

    /// A journal record.

    /// </param>

    /// <returns>

    /// A member of <c>PrintMgmtPrintDestinationPartyType</c>.

    /// </returns>

    /// <exception cref="Exception::Error">

    /// An invalid combination of journal record and document type was found.

    /// </exception>

    protected static PrintMgmtPrintDestinationPartyType getPartyType(PrintMgmtDocumentType _docType, Common _jour)

    {

        PrintMgmtPrintDestinationPartyType polPartyType;

        polPartyType = next getPartyType(_docType, _jour);

        switch (_docType)

        {

            case PrintMgmtDocumentType::SAN_CustAccountStatement:

                return PrintMgmtPrintDestinationPartyType::Customer;

        }

        return polPartyType;

    }

}

/// <summary>

/// Extension class for PrintMgmtNode_CustTable to incoporate new print management node as Polaris customer account statement

/// </summary>

[ExtensionOf(classStr(PrintMgmtNode_CustTable))]

final class PrintMgmtNode_CustTableCls_SAN_Extension

{

    public List getDocumentTypes()

    {

        List ret;

        ret = new List(Types::Enum);

        ret = next getDocumentTypes();

        ret.addEnd(PrintMgmtDocumentType::SAN_CustAccountStatement);

        return ret;

    }

}

/// <summary>

/// This class is used to extend the print management

/// </summary>

[ExtensionOf(classStr(PrintMgmtNode_Sales))]

final class PrintMgmtNode_SalesCls_SAN_Extension

{

    /// <summary>

    /// This class is used to extend the print management

    /// </summary>

    /// <returns>docTypes</returns>

    public List getDocumentTypes()

    {

        List docTypes; 

        docTypes = new List(Types::Enum); 

        docTypes = next getDocumentTypes();

        docTypes.addEnd(PrintMgmtDocumentType::SAN_CustAccountStatement);

        return docTypes;

    }

}

/// <summary>

/// New class to populate the data in PrintMgmtReportFormat table

/// </summary>

[ExtensionOf(classStr(PrintMgmtReportFormatPopulator))]

final class PrintMgmtReportFormatPopulatorCls_SAN_Extension

{

    #PrintMgmtSetup

    /// <summary>

    /// extension of method to populate the data in PrintMgmtReportFormat table

    /// </summary>

    protected void addDocuments()

    {

        next addDocuments();

        this.addOther(PrintMgmtDocumentType::SAN_CustAccountStatement, ssrsReportStr(SAN_CustAccountStatementsExt, Report), ssrsReportStr(SAN_CustAccountStatementsExt, Report), #NoCountryRegionId);

    }

}