web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SQL Query
Microsoft Dynamics AX (Archived)

SQL Query

(0) ShareShare
ReportReport
Posted on by fgil12 2,722

Hi,

I need to find a way to retrieve all purchase orders that have one same mistake: there is an item transaction hanging, even if the lines of the PO have been deleted and the order has been canceled. The problem is that, prior to canceling the PO, one of the lines' quantity was not canceled, so the reception status of the item transactions is "on order."

I have already solved this problem, but now I need to find if there are more PO's with the same situation. I was suggested to launch a query, bu I have never used one before, and i don't know how to program it to do what I need. Any ideas?

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    Hi FGIL,

    You can use this job to check for inventtrans records with missing purchlines:

    static void Job14(Args _args)

    {

       InventTrans     inventTrans;

       PurchLine       purchLine;

       ;

       while select inventTrans

           where inventTrans.TransType == InventTransType::Purch

           && inventTrans.StatusReceipt == statusreceipt::Ordered

           notexists join purchLine

           where inventTrans.TransRefId == purchLine.PurchId

           && inventTrans.InventTransId == purchLine.InventTransId

           if (inventTrans)

           {

               info(strfmt("InventTrans recid: %1", inventTrans.RecId));

           }

    }

  • fgil12 Profile Picture
    fgil12 2,722 on at

    Hi JAN-LUUC

    Thanks for the suggestion. The thing is, I need to find canceled PO's that still generate inventory transactions  for items.

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Ah ok, thought you meant the remaining inventTrans records after deleting the PO.

    This job should inform you regarding cancelled lines which still have a connected inventtrans.

    static void Job16(Args _args)

    {

       PurchLine       purchLine;

       InventTrans     inventTrans;

       ;

       while select purchLine

       where purchLine.PurchStatus == PurchStatus::Canceled

           join inventTrans

           where purchLine.InventTransId == inventTrans.InventTransId

           {

               if (inventTrans)

               info(strfmt("PurchaseOrder: %1, Item: %2", purchLine.PurchId, purchLine.ItemId));

           }

    }

    By the way, when making corrections to the inventtrans (with which you need to be very careful of course) it's wise to run a consistency check on the onhand inventory.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans