Remove intercompany chain link in D365FO

 

Remove intercompany chain link in D365FO.

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);

        }

    }

}