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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Supply chain | Supply Chain Management, Commerce / Select statement is no...
Supply chain | Supply Chain Management, Commerce
Unanswered

Select statement is not working for Production Control

(0) ShareShare
ReportReport
Posted on by PG-18081310-0 28
Hello Community,
I need to fetch the Production Control-related fields from different tables to the custom report, where there are three filters too, so I have written the contract class for parameters and the DP class for the data for the report. While debugging, the contract class is working fine, but there is an issue in the select statement of the DP class. So here is the code:
public void processReport()
    {
        ProductionControlContract contract = this.parmDataContract() as ProductionControlContract;
        fromDate = contract.parmFromDate();
        toDate   = contract.parmToDate();
        selItemId = contract.parmItemNumber();
        selSiteId = contract.parmSiteId();
        ProdJournalProd      prodJournalProd;
        ProdTable            prodTable;
        InventTable          inventTable;
        InventTrans          inventTrans;
        ProdTableJour        pTJour;
        InventTransOrigin inventTransOrigin;
        ItemNumber iNum;
        InventDim inventDim;
        ttsbegin;
        //select firstonly prodJournalProd
        //    inventTable.ItemId == selItemId;

        while select prodJournalProd
        where prodJournalProd.TransDate >= fromDate
          && prodJournalProd.TransDate <= toDate
            && prodJournalProd.ItemId == selItemId
           
            join inventDim
            where inventDim.InventSiteId == selSiteId

        join prodTable
            where prodTable.ProdId == prodJournalProd.ProdId
        join inventTable
            where inventTable.ItemId == prodJournalProd.ItemId
        //    prodJournalProd.ItemId == selItemId
        join inventTrans
            where inventTransOrigin.ItemId == inventTrans.ItemId
        join pTJour
            where pTJour.ProdId == prodJournalProd.ProdId
            && pTJour.InventTransId == prodJournalProd.InventTransId
        {
       
            reportTmp.clear();
            inventDim = InventDim::find(inventTrans.InventDimId);
            reportTmp.TransDate              = prodJournalProd.TransDate;
            reportTmp.ItemNumber         = prodJournalProd.ItemId;
            reportTmp.ProductionId         = prodJournalProd.ProdId;
            reportTmp.QtyGood               = prodJournalProd.QtyGood;
            reportTmp.InventSiteId           = inventDim.InventSiteId;
           // reportTmp.ProdStatus          = prodTable.ProdStatus;
            reportTmp.TotalProductionInKG    = inventTable.NetWeight;
            // Finished goods cost – InventTrans
            reportTmp.FGCost                   = inventTrans.CostAmountPhysical;
            
            if (pTJour.RecId)
            {
                if (pTJour.JournalType == ProdJourType::Release)
                {
                    reportTmp.RMCost = pTJour.AmountPhysical;
                }
                else if (pTJour.JournalType == ProdJourType::RouteCard)
                {
                    reportTmp.ResourceCost = pTJour.AmountPhysical;
                }
            }
            reportTmp.insert();
        }
        ttscommit;
    }
I need a solution for the select statement. At present, I'm not able to get any data output in the report. So, can anyone help me with this?
 
Categories:
Dynamics 365 Supply Chain Management
I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,260 Most Valuable Professional on at
    Select statement is not working for Production Control
    Please tell us more about the problem than just that "there is an issue". Do you mean that your query returns no records although you believe you have data corresponding to the query?
     
    If so, you need to debug the query. Split it to smaller parts to see which condition is responsible for the different behavior than you expect. For example, start with checking the input parameters. If they're correct, test that ProdJournalProd can be found for the given fromDate, toDate and selItemId. If so, add some of the joins and test again. Continue until you find cause.

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…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Supply chain | Supply Chain Management, Commerce

#1
Siv Sagar Profile Picture

Siv Sagar 336 Super User 2025 Season 2

#2
Danny Bilodeau Profile Picture

Danny Bilodeau 216 Super User 2025 Season 2

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 201 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans