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

(0) Share

Report

Posted on by Dineshkarlekar

1,836

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.

I have the same question (0)

All responses (8)

Answers (2)

Dineshkarlekar

1,836 on at

Like (0)

Report

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;
    }

}

Was this reply helpful? Yes No

Layan Jwei 8,060 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,974 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Dineshkarlekar 1,836 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Waed Ayyad 8,989 Super User 2025 Season 2 on at

Like (0)

Report

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

Was this reply helpful? Yes No

Verified answer

Dineshkarlekar

1,836 on at

Like (0)

Report

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

Was this reply helpful? Yes No

Martin Dráb 236,974 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Martin Dráb 236,974 Most Valuable Professional on at

Like (1)

Report

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.

The last question is not the be discussed here; it's in To insert the purch status on the base of sum of invoices amountmst of multiple purch id ..

Dineshkarlekar, you also implicitly confirmed that the question in this thread is answered, so please consider verifying the answer.

Was this reply helpful? Yes No