Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / to create batch job fo...
Finance forum
Answered

to create batch job for all invoice sales in which contains its purchase ids .

Posted on by 1,457
I am creating a batch job which contains all the sales id that are invoiced and the , purchase id which are related to it , i have return the code the i am getting the multiple records , i think it is because of looping can any one plz help me to correct my code , i am really stuck on this . below is my code .
 
thanks ,
regards,
dinesh.
  • Martin Dráb Profile Picture
    Martin Dráb 228,135 Most Valuable Professional on at
    to create batch job for all invoice sales in which contains its purchase ids .
    Dineshkarlekar, I see you created a new thread, but you forgot to mention it here. Let me do it on your behalf this time.
     
     
    Dineshkarlekar, you also implicitly confirmed that the question in this thread is answered, so please consider verifying the answer.
  • Martin Dráb Profile Picture
    Martin Dráb 228,135 Most Valuable Professional on at
    to create batch job for all invoice sales in which contains its purchase ids .
    It sounds like the topic of this thread (to create batch job for all invoice sales in which contains its purchase ids) is resolved and now you have a different problem. If so, please create a new thread (with an appropriate title) instead of changing the topic of this one.
     
    By the way, 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.
  • Verified answer
    Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    to create batch job for all invoice sales in which contains its purchase ids .
    hi ,
    Everyone,
    thanks for reply ,
     
    I have made changes in my code , 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.
    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
  • Waed Ayyad Profile Picture
    Waed Ayyad 5,169 Super User 2024 Season 2 on at
    to create batch job for all invoice sales in which contains its purchase ids .
    Hi Dinesh,
     
    Can you explain your requirements with more details?
    From the first glance, I can see you have duplicate loops (I think you can reduce them by using the correct relations).
     
    Thanks,
    Waed Ayyad
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    to create batch job for all invoice sales in which contains its purchase ids .
    hi ,thanks for reply ,
    i have verified the first one the second one will update you soon , its still under discussion .
     
     
    thanks,
    Dinesh.
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 228,135 Most Valuable Professional on at
    to create batch job for all invoice sales in which contains its purchase ids .
    You seem to assume that a single sales order can have just a single invoice and that a single invoice may contain just a single order. Both assumptions are wrong. A sales order can have several partial invoices or invoices and credit notes, and you can have a summary invoice covering multiple orders.
     
    It means that you're using a wrong relation between SalesTable and CustInvoiceJour (use CustInvoiceSalesLink table, as I already told you several times in other threads) and you can't expect your query in processOperation() to always return an invoice or an order just once.
     
    We can ignore the rest of your code for now, because the problem is already at the beginning. There is no point in testing the rest of code until you fix this problem.
  • Layan Jwei Profile Picture
    Layan Jwei 6,882 Super User 2024 Season 2 on at
    to create batch job for all invoice sales in which contains its purchase ids .
    Hi Dinesh,

    Can you explain more what do you mean by "you are getting multiple records because of looping" because i can see multiple while loops.
    Do you mean there is duplicated records being inserted in this table "dTPaymentTrackingTbl"?

    Also can you please update us about those two questions (if your main question was answered, then please verify and close the thread)
    https://community.dynamics.com/forums/thread/details/?threadid=f4d33605-2cb1-ee11-a569-00224827e528
    https://community.dynamics.com/forums/thread/details/?threadid=41bba432-0da6-ee11-be37-00224827ed84

    Thanks,
    Layan Jweihan
  • Dineshkarlekar Profile Picture
    Dineshkarlekar 1,457 on at
    to create batch job for all invoice sales in which contains its purchase ids .
    plz , anybody help me on this . my code is below.
    class DTPaymentTrackingBatchServiceLoc extends SysOperationServiceBase
    {
        public void processOperation()
        {
            CustInvoiceJour         custInvoiceJour;
            SalesTable              salesTable;
            InterCompanyPurchSalesReference   interCompanyPurchSalesReference;
    
            while  select * from custInvoiceJour
                join salesTable
                     where  salesTable.SalesId == custInvoiceJour.SalesId
                join interCompanyPurchSalesReference
                     where interCompanyPurchSalesReference.SalesId == salesTable.SalesId
                      //&& salesTable.SalesId == 'SO09-10000540'
            {
                this.InsertInvoicesDetails(custInvoiceJour,salesTable);
            }
    
        }
    
        public void InsertInvoicesDetails(CustInvoiceJour _custInvoiceJour,SalesTable    _salesTable)
        {
            CustInvoiceJour            custInvoiceJourloc;
            SalesTable                 salestable;
            DTCustomerDetailsTable     dTCustomerDetailsTable;
            InterCompanyPurchSalesReference   InterCompanyPurchSalesReference;
            Purchtable                 purchTable,purchTableloc;
            Purchline                  purchLine;
            Custtable                  custtable;
            DirPartyTable              dirPartyTable;
            HCMWORKER                  HcmWorker;
            DTPaymentTrackingTbl       dTPaymentTrackingTbl,dTPaymentTrackingTblUpdate;
            InterCompanyPurchId        intPruchId;
            CustInvoiceId              invoiceid;
            InterCompanyCompanyId      intercompanyId;
            //SalesId                  salesId;
            str                        salesId,salesStatus;
    
         
            while select crosscompany * from salestable
                join InterCompanyPurchSalesReference
                     where InterCompanyPurchSalesReference.SalesId == _salestable.SalesId
                && salestable.SalesId ==  _custInvoiceJour.SalesId
            {
                    intPruchId = InterCompanyPurchSalesReference.PurchId;
                    invoiceid  = _custInvoiceJour.InvoiceId;
                    //intercompanyId = salestable.InterCompanyCompanyId;
                    salesId  = salestable.SalesId;
                    salesStatus = enum2Str(salestable.SalesStatus);
    
                    dTPaymentTrackingTbl.DTWorkerName =  HcmWorker::find(salesTable.WorkerSalesResponsible).name();
                    dTPaymentTrackingTbl.InvoiceAccount  = _custInvoiceJour.InvoiceAccount;
                    dTPaymentTrackingTbl.Name = _custInvoiceJour.custTable_OrderAccount().name();
                    dTPaymentTrackingTbl.CustomerRef = salestable.CustomerRef;
    
                    while select crosscompany SalesId,PackingSlipId,InvoiceId,InvoiceAmount,AmountNotSettled,
                       DTCustInvoicePaymtType,DTInvoiceStatus,DataAreaId from dTCustomerDetailsTable
                          group by SalesId,PackingSlipId,InvoiceId,InvoiceAmount,AmountNotSettled,
                          DTCustInvoicePaymtType,DTInvoiceStatus,DataAreaId
                           where  dTCustomerDetailsTable.SalesId == salesId
                    {
                        dTPaymentTrackingTbl.SalesId          = dTCustomerDetailsTable.SalesId;
                        dTPaymentTrackingTbl.PackingSlipId    = dTCustomerDetailsTable.PackingSlipId;
                        dTPaymentTrackingTbl.InvoiceId        = dTCustomerDetailsTable.InvoiceId;
                        dTPaymentTrackingTbl.InvoiceAmount    = dTCustomerDetailsTable.InvoiceAmount;
                        dTPaymentTrackingTbl.AmountNotSettled = dTCustomerDetailsTable.AmountNotSettled;
                        dTPaymentTrackingTbl.DTCustInvoicePaymtType = dTCustomerDetailsTable.DTCustInvoicePaymtType;
                        dTPaymentTrackingTbl.DTInvoiceStatus  = salesStatus;
                                
    
                        while select crosscompany CustomerRef,sum(LineAmount) from purchLine
                                join * from  purchTable
                         group by purchTable.PurchId,purchTable.PurchStatus,purchTable.InterCompanyCompanyId,purchTable.CreatedDateTime
                         where purchLine.PurchId == purchTable.PurchId
                          && purchTable.PurchId ==  intPruchId
                        {
                            dTPaymentTrackingTbl.InterCompanyPurchId = purchTable.PurchId;
                            dTPaymentTrackingTbl.PurchStatus         = purchTable.PurchStatus;
                            dTPaymentTrackingTbl.DTDateTime          = purchTable.CreatedDateTime;
                            dTPaymentTrackingTbl.LineAmount          = purchLine.LineAmount;
                            dTPaymentTrackingTbl.DTEntityName        = purchTable.InterCompanyCompanyId;
                        }
    
                        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();
                        
                        }
                    }
    
            }
         
        }
    
        public void UpdateInvoicesDetails(DTPaymentTrackingTbl  _dTPaymentTrackingTbl)
        {
            DTPaymentTrackingTbl  dTPaymentTrackingTbl;
            ttsbegin;
            select forupdate dTPaymentTrackingTbl
                where dTPaymentTrackingTbl.InvoiceId == _dTPaymentTrackingTbl.InvoiceId
                && dTPaymentTrackingTbl.PackingSlipId == _dTPaymentTrackingTbl.PackingSlipId
                && dTPaymentTrackingTbl.SalesId == _dTPaymentTrackingTbl.SalesId
                && dTPaymentTrackingTbl.InterCompanyPurchId == _dTPaymentTrackingTbl.InterCompanyPurchId;
            if(dTPaymentTrackingTbl)
            {
                dTPaymentTrackingTbl.InvoiceAccount      = _dTPaymentTrackingTbl.InvoiceAccount;
                dTPaymentTrackingTbl.Name                = _dTPaymentTrackingTbl.Name;
                dTPaymentTrackingTbl.DTWorkerName        = _dTPaymentTrackingTbl.DTWorkerName;
                dTPaymentTrackingTbl.SalesId             = _dTPaymentTrackingTbl.SalesId;
                dTPaymentTrackingTbl.PackingSlipId       = _dTPaymentTrackingTbl.PackingSlipId;
                dTPaymentTrackingTbl.InvoiceId           = _dTPaymentTrackingTbl.InvoiceId;
                dTPaymentTrackingTbl.InvoiceAmount       = _dTPaymentTrackingTbl.InvoiceAmount;
                dTPaymentTrackingTbl.AmountNotSettled    = _dTPaymentTrackingTbl.AmountNotSettled;
                dTPaymentTrackingTbl.DTCustInvoicePaymtType = _dTPaymentTrackingTbl.DTCustInvoicePaymtType;
                dTPaymentTrackingTbl.DTInvoiceStatus     = _dTPaymentTrackingTbl.DTInvoiceStatus;
                dTPaymentTrackingTbl.InterCompanyPurchId = _dTPaymentTrackingTbl.InterCompanyPurchId;
                dTPaymentTrackingTbl.PurchStatus         = _dTPaymentTrackingTbl.PurchStatus;
                dTPaymentTrackingTbl.DTDateTime          = _dTPaymentTrackingTbl.DTDateTime;
                dTPaymentTrackingTbl.CustomerRef         = _dTPaymentTrackingTbl.CustomerRef;
                dTPaymentTrackingTbl.LineAmount          = _dTPaymentTrackingTbl.LineAmount;
                dTPaymentTrackingTbl.update();
            }
            ttscommit;
        }
    
    }
    
     

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

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

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 228,135 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans