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

(0) Share

Report

Posted on by Dineshkarlekar

1,836

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

I have the same question (0)

All responses (4)

Answers (0)

Dineshkarlekar

1,836 on at

Like (0)

Report

Copy link

Link copied!

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

Was this reply helpful? Yes No

Martin Dráb 239,040 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

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

Like (0)

Report
Copy link

Link copied!
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

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

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No