SQL Query to Mark Purchase Orders with Production Orders

Update PRODBOM set InventRefId = purchTable.PurchID , InventRefTransID  = PurchTable.InventTransID where PRODBOM.ProdID = 'xxxxx' and PRODBOM.INVENTTRANSID = 'xxxxx'

Update Purchline set InventRefId = prodBOM.ProdId , InventRefTransID = prodBOM.InventTransID where Purchline.PurchID  = 'xxxx' and PURCHLINE.INVENTTRANSID = 'xxxx'

Example:

-- Need to update InventRefId = 'PO-00396604' and InventRefTransID  = 'LOT-078665474' on ProdBOM with Purchline data (PurchId, InventTransID)
-- Need to update InventRefId = 'WO-00605046' and InventRefTransID = 'LOT-078665467' on Purchline with ProdBOM data (ProdId, InventTransID)"

Installing Platform update on a Disconnected Local Developer Virtual Machine

Steps to Install Dynamics 365 for Finance and Operations Platform Update on Local Developer or a Demo VM

Microsoft is doing a pretty good job with their docs site where they have listed the instructions on how to do a PU on Cloud and disconnected VMs. Have a look at the link below

Apply the latest platform update to your Microsoft Dynamics 365 Finance and Operations environment

My post supplements Microsoft's documentation and additionally it lists down the steps with some more details and screenshots and towards the end of this post, you will notice how I validated and verified if my PU installation was successfully done.

a.       Download the latest platform update package (zip) file from LCS

b.       Go to LCS >  Your Project and then navigate to Asset Library by clicking on the breadcrumb bar

c.       Once you are in the Asset Library page, click on Software deployable package as shown above.           will start downloading the package. Note it’s around 700 MB so it might take 5 to 10 mins 

       depending on your bandwidth, to download.

d.       Once it’s downloaded copy the Platformupdate.zip file into your local vm and Extract the zip                file to your local drive (I created a folder called PlatformUpdate11)

e.       Next, Open the DefaultTopologydata.xml file from E:\PlatformUpdate11\AXPlatformUpdate
          Replace the machine name from the localhost to your VM name

f. 

g.     Open command prompt from the VM (as Admin), and then navigate to the Extracted folder to run the command AxUpdateInstaller.exe list (This will help to determine the components and their versions which are installed on your local dev box)

h.      

    Prepare the string for <ServiceModelList> as shown below

<ServiceModelList>

        <string>AOSService</string>

        <string>ALMService</string>

        <string>BIService</string>

        <string>DevToolsService</string>

        <string>DIXFService</string>

        <string>MROneBox </string>

        <string>PayrollTaxModule</string>

        <string>PerfSDK</string>

        <string>ReportingService</string>

        <string>RetailCloudPos</string>

        <string>RetailHQConfiguration</string>

        <string>RetailSDK</string>

        <string>RetailSelfService</string>

        <string>RetailServer</string>

        <string>RetailStorefront</string>

        <string>SCMSelfService</string>

</ServiceModelList>

i.         Your final DefaultTopologyData.xml should look as below:

       

j.        Generate the runbook

AXUpdateInstaller.exe generate -runbookid="LocalDev-runbook" -topologyfile="DefaultTopologyData.xml" -servicemodelfile="DefaultServiceModelData.xml" -runbookfile="LocalDev-runbook.xml"

k.       Import the runbook file

AXUpdateInstaller.exe import -runbookfile="LocalDev-runbook.xml"

l.        Verify the runbook file

AXUpdateInstaller.exe list

       Execute the runbook

AXUpdateInstaller.exe execute -runbookid="LocalDev-runbook"

This will take some time as it starts executing each of the steps in the runbook.

The most time consuming step as per my observation is backup of the AOS Service

After 15 to 20 mins, it will come up with a message Everything is OK and will be static for more than 15 mins, but don’t abort it, still the scripts are running in the background, Let it finish.

After 2.5 hours, it progressed to the next Step 27

Once you notice, The step completed message and it comes back to the prompt, that means all the steps in runbook are executed and now you can move on to the next command

n.      Export the runbook

AXUpdateInstaller.exe export -runbookid="LocalDev-runbook" -runbookfile="LocalDev-runbook.xml"

Exporting the runbook will be useful for future references:

o    For e.g. to analyze the downtime requirements for production, and so on.

o    To send the file to Microsoft in the event of failure to install a deployable package.

o.      Verify if the components are updated correctly.

Run AxUpdateInstaller.exe list command and see the updated components. Note the difference in version numbers

Update 11:

p.       

    Also note, your Visual Studio Extension for D365 gets automatically updated as part of the Update 11

How to check new VS Extension:

Open Visual Studio -> Go to Help > About Visual Studio and check in installed Products list. We should be able to see the latest version of the VS Extension – Dynamics 365 Unified Operations Platform 7.0.4679.3516

Check the update PU 11 version from the browser:

That's all for now. Hope it gives you an idea on how to do a PU Update on a Local Developer's VM. Note, each PU Update may have slight differences here and there, so I always recommend to visit the Microsoft docs site to get the up-to-date documentation before you perform these steps.

X++ code to recalculate Item transactions

static void lan_recalcInventSumItem(Args _args)
{
InventSumRecalcItem InventSumRecalcItem;
;
InventSumRecalcItem = new InventSumRecalcItem("Item Number", true, checkfix::Fix);// enter the item number that need to be re-calc
InventSumRecalcItem.updatenow();
}

X++ code to cancel Deliver Remainder Qty on purchase orders

Below Job is used to select a date range and checks for the ended production orders. Then searches for the purchases orders that are in open status with deliver remainder qty and cancel the qty automatically.

This Job helped our Accounting user to save lot of time. Hence, sharing with you all.


static void lan_CancelingDeliveryRemainderQty(Args _args)
{

   DialogField    dialogStartDate, dialogEndDate;
   TransDate      fromDate, todate;
   PurchLine      purchLine;
    ProdTable prodTable;
    ProdBOM prodBom;
    PurchTable purchTable;
    int cnt =0;

    Dialog     dialog = new Dialog("Select date range");

    dialogStartDate = dialog.addField(extendedTypeStr(TransDate));
    dialogEndDate   = dialog.addField(extendedTypeStr(TransDate));

    dialogStartDate.label("From Date");
    dialogEndDate.label("To Date");

    if(dialog.run())
    {
        fromDate = mkDate(1, mthOfYr(dialogStartDate.value()), year(dialogStartDate.value()));
        toDate   = (mkDate(1, (mthOfYr(dialogEndDate.value()) + 1), year(dialogEndDate.value())) - 1);

        while select * from prodTable
                    join prodBom
                    where  prodTable.ProdId  == prodBom.ProdId
                    && (prodTable.SchedEnd >= fromDate && prodTable.SchedEnd <= todate)
                        && prodBom.InventRefType == 2
                        && prodTable.ProdStatus  == 7
        {
        if(prodTable)

        {
            select * from purchTable
            where purchTable.PurchId == prodBom.InventRefId
            && (purchTable.PurchStatus == 1 || purchTable.PurchStatus == 2);

            if(purchTable)
            {

                     select forupdate purchLine where purchLine.InventRefId == prodTable.ProdId
                     && purchLine.PurchId == prodBom.InventRefId
                     && purchLine.RemainPurchPhysical > 0;
                {

                   if (purchLine)
                    {
                        ttsBegin;
                        // Set remaining inventory Qty to zero
                        purchLine.RemainInventPhysical  = 0;

                        // Set remaining physical Qty to zero
                        purchLine.RemainPurchPhysical   = 0;

                        // We have to cancel the purchLine
                        // Not necessary, I did this to do exactly like AX does
                      //  purchLine.PurchStatus           = PurchStatus::Canceled;

                        // Update PurchLine
                        purchLine.update();
                       cnt++;
                            ttsCommit;
                        // This method will update the inventory transactions
                        InventMovement::bufferSetRemainQty(purchLine);
                    } // purchLine exit
                }
            } //purchTable exit
         }// pordTable exit
        }

    }

         info(strFmt("Start Month %1", fromDate));
        info(strFmt("End Month %1", todate));

    info(strFmt("Total no. of purchlines updated: %1", cnt));


}

PowerShell code to see when AOS service was started/stopped

I often need to see when an AOS was stopped for various reasons, and scanning the event viewer can be a hassle. Running this simple 1-line PowerShell script will give you that information.

You can also use this script as a guide to run other PowerShell scripts to search the event log for specific text.

Get-WinEvent -ErrorAction SilentlyContinue -FilterHashtable @{logname='system'; StartTime=(((get-date).AddDays(-30).Date)); EndTime=(((get-date).AddDays(1).Date)); id=7036} -MaxEvents 10000 | ? {$_.Message -like '*Microsoft Dynamics AX Object Server*'}