Skip to main content

Notifications

Dynamics 365 Community / Forums / Finance forum / To insert the purch st...
Finance forum
Unanswered

To insert the purch status on the base of sum of invoices amountmst of multiple purch id .

Posted on by 1,457
hi everyone , 
 here i getting all the invoice sales id , and getting the all the related purch id to that sales id so here purch id can be multiple and in my for loop i append purch id 's , so i am inserting multiple purch id in one string , but now a single purch id can have multiple invoice so i need to insert the sum of all invoices in two vaiables and then on the base of that update the purch status of order . so i have made container for purch id 's and have written for loop for purch id transaction , can anyone plz let me know is my for loop  correct . below is my code.
 
 
thanks,
Regards,
Dinesh
 
  • Martin Dráb Profile Picture
    Martin Dráb 229,173 Most Valuable Professional on at
    To insert the purch status on the base of sum of invoices amountmst of multiple purch id .
    Yes, you need changes, because the whole calculation makes no sense to me. For example, if the order is used in summary invoice, you'll never get a correct result. You'll either ignore the invoice or you'll take amounts coming from other orders as well.
     
    I believe that there is no point in trying to fix your calculation. Using the standard logic (PurchTotals class) will be both much simpler and much safer.
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    To insert the purch status on the base of sum of invoices amountmst of multiple purch id .
    hi ,
    martin thanks for reply ,
     
    here is my for loop code , is do i need to make any change in this code , to get desire result . 
    // LOOP TO GET THE DATA OF PURCH TABLE 
                        length = conLen(intPruchId);
    
                        for (i = 1; i <= length; i++)
                        {
                            value = conPeek(intPruchId,i);
                  
                             dTPaymentTrackingTbl.InterCompanyPurchId +=  value;
    
                                select * from  purchTable
                                         where  purchTable.PurchId == value;
    
                                dTPaymentTrackingTbl.DTDateTime = purchTable.CreatedDateTime;                 
                     
                             while select sum(InvoiceAmount) from vendInvoiceJour
                                       where vendInvoiceJour.PurchId == value
                                        {
                                            amountCur += vendInvoiceJour.InvoiceAmount;
                                        }
                                 
                                dTPaymentTrackingTbl.VendInvoiceAMT      = amountCur;
    
                         select  vendInvoiceJourloc
                               where vendInvoiceJourloc.PurchId == value;
    
                         vendInvoiceId = vendInvoiceJourloc.InvoiceId;
                         vendInvoiceAccount = vendInvoiceJourloc.InvoiceAccount;
                         transDate = vendInvoiceJourloc.InvoiceDate;
                         voucher = vendInvoiceJourloc.LedgerVoucher;
                    
                            while select * from vendtrans
                                  where vendtrans.Invoice ==  vendInvoiceId
                                    && vendtrans.AccountNum == vendInvoiceAccount
                                    && vendtrans.TransDate == transDate
                                    && vendtrans.Voucher == voucher
                                    && vendtrans.TransType == 3
                             {
                                 amountMST          += vendtrans.AmountMST;
                                 remainingAmountMST += vendtrans.remainAmountMST();
                             }
    
                             if(vendtrans)
                              {
                                  if(amountMST  == remainingAmountMST && remainingAmountMST != 0 && amountMST  != 0)
                                  {                   
                                      dTPaymentTrackingTbl.PurchStatus  = DTVendInvoicePaymentType::NotPaid;
                                  }
                                  else if(amountMST  != remainingAmountMST && remainingAmountMST != 0)
                                  {                     
                                      dTPaymentTrackingTbl.PurchStatus  = DTVendInvoicePaymentType::PartiallyPaid;
                                  }
                                  else if(amountMST  != 0 &&  remainingAmountMST == 0)
                                  {
                                      dTPaymentTrackingTbl.PurchStatus  = DTVendInvoicePaymentType::FullyPaid;
                                  }
    
                               }
                        }
    
    thanks,
    regards,
    Dinesh
     
     
     
  • Martin Dráb Profile Picture
    Martin Dráb 229,173 Most Valuable Professional on at
    To insert the purch status on the base of sum of invoices amountmst of multiple purch id .
    Which of the loops is your question about? I suspect that most of your is not really relevant to your question. If you mean the loop for getting invoices from purch ID, then it's indeed wrong, because you again used a wrong relation there.
     
    You'll make your life much easier when you learn to split a problem to smaller pieces and code to methods. Then you can focus smaller problems one by one instead of having a wall of code and little idea about which part isn't working.
     
    I think you should start with designing and testing logic for a single purch ID (and ignore most of your current code). When you have that, you'll expand it to work a list of orders, and then you'll call it from your other code.
     
    As I see your code, I think it'll be much better if you don't try to calculate the invoiced amount by yourself. Instead, use PurchTotals class.
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    To insert the purch status on the base of sum of invoices amountmst of multiple purch id .
    here is my code , plz help me out on this .
    class DTPaymentTrackingBatchServiceLoc extends SysOperationServiceBase
    {
        public void processOperation()
        {
           CustInvoiceSalesLink        custInvoiceSalesLink;
            SalesTable              salesTable;
            InterCompanyPurchSalesReference   interCompanyPurchSalesReference;
    
            //TO GET RELATED PURCHASE ORDER FROM INVOICED SALES ID 
            while  select * from custInvoiceSalesLink
                join salesTable 
                     where  salesTable.SalesId == custInvoiceSalesLink.SalesId
                     
            {
               select firstonly  purchid from interCompanyPurchSalesReference 
                     where interCompanyPurchSalesReference.SalesId == salesTable.SalesId;
                if(interCompanyPurchSalesReference.PurchId != "")
                {
                    this.InsertInvoicesDetails(custInvoiceSalesLink,salesTable);
                }
            }
    
        }
    
        public void InsertInvoicesDetails(CustInvoiceSalesLink _custInvoiceSalesLink,SalesTable    _salesTable)
        {
            CustInvoiceSalesLink       custInvoiceSalesLinkloc;
            SalesTable                 salestable;
            DTCustomerDetailsTable     dTCustomerDetailsTable;
            InterCompanyPurchSalesReference   InterCompanyPurchSalesReference;
            Purchtable                 purchTable,purchTableloc;
            VendTrans                  vendTrans;
            Custtable                  custtable;
            DirPartyTable              dirPartyTable;
            HCMWORKER                  HcmWorker;
            VendInvoiceJour            vendInvoiceJour,vendInvoiceJourloc;
            DTPaymentTrackingTbl       dTPaymentTrackingTbl,dTPaymentTrackingTblUpdate;
            //InterCompanyPurchId        PruchId;
            container                  intPruchId;
            CustInvoiceId              invoiceid;
            InterCompanyCompanyId      intercompanyId;
            AmountCur                  amountCur;
            //SalesId                  salesId;
            VendInvoiceId              vendInvoiceId;
            VendInvoiceAccount         vendInvoiceAccount;
            TransDate                   transDate;
            Voucher                    voucher;
            str                        salesId,salesStatus,value;
            AmountMST                  remainingAmountMST;
            AmountMSTDebCred           amountMST;
            int                        i, length;
    
    
    
               //TO GET THE RELATED PURCHID FROM REFRENCE TABLE.
                while select PurchId from  InterCompanyPurchSalesReference
                     where InterCompanyPurchSalesReference.SalesId == _salestable.SalesId
                       && salestable.SalesId ==  _custInvoiceSalesLink.SalesId
                      {
                            intPruchId += InterCompanyPurchSalesReference.PurchId;
                      }
    
                    invoiceid  = _custInvoiceSalesLink.InvoiceId;
                    //intercompanyId = salestable.InterCompanyCompanyId;
                    salesId  =  _custInvoiceSalesLink.SalesId;
                    salesStatus = enum2Str(salestable.SalesStatus);
    
                    dTPaymentTrackingTbl.DTWorkerName =  HcmWorker::find(salesTable.WorkerSalesResponsible).name();
                    dTPaymentTrackingTbl.InvoiceAccount  = _custInvoiceSalesLink.InvoiceAccount;
                    dTPaymentTrackingTbl.Name = _custInvoiceSalesLink.custTable_OrderAccount().name();
                    dTPaymentTrackingTbl.CustomerRef = salestable.CustomerRef;
                    dTPaymentTrackingTbl.SalesId     = salesId;
                    dTPaymentTrackingTbl.InvoiceId   = invoiceid;
    
            //TO GET SALES ORDER DETAILS FROM CUSTOMER DETAILS TABLE
                 select * from dTCustomerDetailsTable
                           where  dTCustomerDetailsTable.InvoiceId == invoiceid
                             &&   dTCustomerDetailsTable.SalesId   ==   salesId
                             &&   dTCustomerDetailsTable.CustAccount == _custInvoiceSalesLink.InvoiceAccount
                             &&   dTCustomerDetailsTable.InvoiceDate == _custInvoiceSalesLink.InvoiceDate;       
                 
                        dTPaymentTrackingTbl.PackingSlipId    = dTCustomerDetailsTable.PackingSlipId;
                        dTPaymentTrackingTbl.InvoiceAmount    = dTCustomerDetailsTable.InvoiceAmount;
                        dTPaymentTrackingTbl.AmountNotSettled = dTCustomerDetailsTable.AmountNotSettled;
                        dTPaymentTrackingTbl.DTCustInvoicePaymtType = dTCustomerDetailsTable.DTCustInvoicePaymtType;
                        dTPaymentTrackingTbl.DTInvoiceStatus  = salesStatus;
    
    
            // LOOP TO GET THE DATA OF PURCH TABLE 
                        length = conLen(intPruchId);
    
                        for (i = 1; i <= length; i++)
                        {
                            value = conPeek(intPruchId,i);
                  
                             dTPaymentTrackingTbl.InterCompanyPurchId +=  value;
    
                                select * from  purchTable
                                         where  purchTable.PurchId == value;
    
                                dTPaymentTrackingTbl.DTDateTime = purchTable.CreatedDateTime;                 
                     
                             while select sum(InvoiceAmount) from vendInvoiceJour
                                       where vendInvoiceJour.PurchId == value
                                        {
                                            amountCur += vendInvoiceJour.InvoiceAmount;
                                        }
                                 
                                dTPaymentTrackingTbl.VendInvoiceAMT      = amountCur;
    
                         select  vendInvoiceJourloc
                               where vendInvoiceJourloc.PurchId == value;
    
                         vendInvoiceId = vendInvoiceJourloc.InvoiceId;
                         vendInvoiceAccount = vendInvoiceJourloc.InvoiceAccount;
                         transDate = vendInvoiceJourloc.InvoiceDate;
                         voucher = vendInvoiceJourloc.LedgerVoucher;
                    
                            while select * from vendtrans
                                  where vendtrans.Invoice ==  vendInvoiceId
                                    && vendtrans.AccountNum == vendInvoiceAccount
                                    && vendtrans.TransDate == transDate
                                    && vendtrans.Voucher == voucher
                                    && vendtrans.TransType == 3
                             {
                                 amountMST          += vendtrans.AmountMST;
                                 remainingAmountMST += vendtrans.remainAmountMST();
                             }
    
                             if(vendtrans)
                              {
                                  if(amountMST  == remainingAmountMST && remainingAmountMST != 0 && amountMST  != 0)
                                  {                   
                                      dTPaymentTrackingTbl.PurchStatus  = DTVendInvoicePaymentType::NotPaid;
                                  }
                                  else if(amountMST  != remainingAmountMST && remainingAmountMST != 0)
                                  {                     
                                      dTPaymentTrackingTbl.PurchStatus  = DTVendInvoicePaymentType::PartiallyPaid;
                                  }
                                  else if(amountMST  != 0 &&  remainingAmountMST == 0)
                                  {
                                      dTPaymentTrackingTbl.PurchStatus  = DTVendInvoicePaymentType::FullyPaid;
                                  }
    
                               }
                        }
    
    
                        select firstonly dTPaymentTrackingTblUpdate
                               where dTPaymentTrackingTblUpdate.SalesId == dTPaymentTrackingTbl.SalesId
                                 &&  dTPaymentTrackingTblUpdate.InterCompanyPurchId == dTPaymentTrackingTbl.InterCompanyPurchId
                                 && dTPaymentTrackingTblUpdate.InvoiceId == dTPaymentTrackingTbl.InvoiceId
                                 && dTPaymentTrackingTblUpdate.PackingSlipId == dTPaymentTrackingTbl.PackingSlipId;
       
        
                        if(dTPaymentTrackingTblUpdate.RecId != 0)
                        {
                            this.UpdateInvoicesDetails(dTPaymentTrackingTbl);
                        }
                        else
                        {
                            dTPaymentTrackingTbl.insert();
                        
                        }
                     
        }
    thanks 
    Regards
    Dinesh
     

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,867 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,173 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans