Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Unanswered

Need Help With Query

(0) ShareShare
ReportReport
Posted on by

/// 
/// Vendor payment Emails Code
/// 
class OABPostEmail
{
    /// 
    /// VemdEmailID
    /// 
    /// VemdEmailID
    /// Email
    static LogisticsElectronicAddressLocator VendEmailId(VendAccount _vendAccount)
    {
        VendTable vendTable;
        DirPartyTable partyTable;
        LogisticsElectronicAddress logisticsElectronicAddress;
        select Firstonly  AccountNum ,party from vendTable where vendTable.AccountNum==_vendAccount
                    join Name,RecId from partyTable where partyTable.RecId==vendTable.Party
                    join Locator from logisticsElectronicAddress where logisticsElectronicAddress.RecId==partyTable.PrimaryContactEmail;
        return logisticsElectronicAddress.Locator;
    }

    static InvoiceId VendSettleAmountInvoice(RecId _recId)
    {
        VendSettlement vendSettlement;
      //  Str1260 body;
        InvoiceId invoiceId;
        while select  vendSettlement
                         where vendSettlement.TransRecId == _recId
        {
         
        invoiceId = vendSettlement.displayInvoiceId() ;
        }
        return invoiceId;
    }

    static AmountMST SettleAmountMST(RecId _recId)
    {
        VendSettlement vendSettlement;
      //  Str1260 body;
        AmountMST amountMST;
        while select  vendSettlement
                         where vendSettlement.TransRecId == _recId
        {
        
        amountMST = vendSettlement.SettleAmountMST ;
        }

        return amountMST;
    }

    /// 
    /// VendSettleAmountMST
    /// 
    /// VendSettleAmountMST
    /// Body
    static Str1260 VendSettleAmountMST(RecId _recId)
    {
        VendSettlement vendSettlement;
        Str1260 body;
        while select  vendSettlement
                         where vendSettlement.TransRecId == _recId
        {
            body  = strFmt("@OAB:OABVendEmailInvoice",vendSettlement.displayInvoiceId(), vendSettlement.SettleAmountMST);
        }
        return body;
    }

    /// 
    /// Vendor payment Emails Code
    /// 
    /// Current Date
    static   void sendMail(TransDate _FromDate)
    {
        Str1260				 body,Tbody;
        SysEmailParameters	 EmailParameters = SysEmailParameters::find();
        DirPartyTable partyTable;
        VendTable vendTable;
        VendTrans vendTrans;
        VendAccount localvendAccount;
        FromDate fromdate = _FromDate;
        LogisticsElectronicAddress logisticsElectronicAddress;
        SysMailerMessageBuilder mailer;
        VendTrans   vendTransGroup;
        VendSettlement  vendSettlementGroup;
        boolean isinLoop;

        while select AccountNum,RecId from vendTransGroup group by AccountNum,RecId
            join RecId from vendSettlementGroup
            where vendSettlementGroup.TransRecId==vendTransGroup.RecId &&
            vendTransGroup.TransDate==fromdate
        {
            isinLoop = true;
            if (localvendAccount != vendTransGroup.AccountNum)
            {
                if(localvendAccount!=null)
                {
                   
                    mailer.setBody(tbody,true) ;
                    SysMailerFactory::getNonInteractiveMailer().sendNonInteractive(mailer.getMessage()) ;
                    body ='';
                }
                str emails = OABPostEmail::VendEmailId(vendTransGroup.AccountNum);
                mailer = new SysMailerMessageBuilder();
                mailer.setFrom(EmailParameters.SMTPUserName);
                mailer.addTo(emails);
                mailer.setSubject(strFmt("@OAB:OABVendEmailSubject",OABPostEmail::VendAccountNum(vendTransGroup.AccountNum), OABPostEmail::VendAccountName(vendTransGroup.AccountNum)));
               
                Tbody = strFmt(" %1:-   %2 
", OABPostEmail::VendAccountNum(vendTransGroup.AccountNum), OABPostEmail::VendAccountName(vendTransGroup.AccountNum)); Tbody = "
Payment DatePayment AmountSettled Against Invoice Number Settlement Amount Against Invoice Value
"; select firstonly AccountNum,TransDate,AmountMST from vendTrans where vendTrans.AccountNum==vendTransGroup.AccountNum && vendTrans.RecId==vendTransGroup.RecId; Tbody = strFmt("
%1%2%3%4
",vendTrans.TransDate,vendTrans.AmountMST,OABPostEmail::VendSettleAmountInvoice(vendTransGroup.RecId),OABPostEmail::SettleAmountMST(vendTransGroup.RecId)); } else { select firstonly AccountNum,TransDate,AmountMST from vendTrans where vendTrans.AccountNum==vendTransGroup.AccountNum && vendTrans.RecId==vendTransGroup.RecId; Tbody = strFmt("
%1%2%3%4
",vendTrans.TransDate,vendTrans.AmountMST,OABPostEmail::VendSettleAmountInvoice(vendTransGroup.RecId),OABPostEmail::SettleAmountMST(vendTransGroup.RecId)); } localvendAccount = vendTransGroup.AccountNum; } if (isinLoop) { mailer.setBody(Tbody,true) ; SysMailerFactory::getNonInteractiveMailer().sendNonInteractive(mailer.getMessage()) ; } } }

pastedimage1672910720164v1.png

Here For Same Payment Amount there Are two Invoices and two settlement Amount but with my code here it is showing only one

according to need , second row should also come with same date and payment amount but other invoice and settlement amount. Cant understand how to do that Fyi printing rows Data in Line 113 using tbody .there are two fields from amountmst and transdate from vendtrans and other two fields from vendsettlement table calling in 113 using a static function i.e vendsettleAmountInvoice and vendsettleamount

  • Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    RE: Need Help With Query

    It depends on what you want to get. If you want to get multiple rows in the table, you don't have any logic for it in sendMail(). You would need a while select there (as you have in vendSettleAmountInvoice()) and put code for adding table rows inside this while loop. You'll throw away settleAmountMST() and vendSettleAmountInvoice(), because you'll get values directly from VendSettlement. For example:

    while select vendSettlement
        where vendSettlement.TransRecId == vendTransGroup.RecId
    {
        tBody  = OABPostEmail::buildHtmlInvoiceTableRow(vendTrans, vendSettlement);
    }

  • RE: Need Help With Query

    can you help exactly sir , how it can be resolved

  • Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    RE: Need Help With Query

    You have the same bug in vendSettleAmountInvoice() too. You iterate settlements, but keep overwriting invoiceId. therefore you'll get invoiceId of the last one only.

  • RE: Need Help With Query

    pastedimage1672913030237v1.png

    like here for same date and amountMSt there are two invoices and settled amount , but its printing first invoice and settled amount only

     pastedimage1672912857755v2.png 

  • RE: Need Help With Query

    static InvoiceId VendSettleAmountInvoice(RecId _recId)
        {
            VendSettlement vendSettlement;
          //  Str1260 body;
            InvoiceId invoiceId;
            while select  vendSettlement
                             where vendSettlement.TransRecId == _recId
            {
             
            invoiceId = vendSettlement.displayInvoiceId() ;
            }
            return invoiceId;
        }
    
        static AmountMST SettleAmountMST(RecId _recId)
        {
            VendSettlement vendSettlement;
          //  Str1260 body;
            AmountMST amountMST;
            while select  vendSettlement
                             where vendSettlement.TransRecId == _recId
            {
            
            amountMST = vendSettlement.SettleAmountMST ;
            }
    
            return amountMST;
        }
    mailer = new SysMailerMessageBuilder();
                    mailer.setFrom(EmailParameters.SMTPUserName);
                    mailer.addTo(emails);
                    mailer.setSubject(strFmt("@OAB:OABVendEmailSubject",OABPostEmail::VendAccountNum(vendTransGroup.AccountNum), OABPostEmail::VendAccountName(vendTransGroup.AccountNum)));
                   
                    Tbody = strFmt(" %1:-   %2 
    ", OABPostEmail::VendAccountNum(vendTransGroup.AccountNum), OABPostEmail::VendAccountName(vendTransGroup.AccountNum)); Tbody = "
    Payment DatePayment AmountSettled Against Invoice Number Settlement Amount Against Invoice Value
    "; select firstonly AccountNum,TransDate,AmountMST from vendTrans where vendTrans.AccountNum==vendTransGroup.AccountNum && vendTrans.RecId==vendTransGroup.RecId; Tbody = strFmt("
    %1%2%3%4
    ", vendTrans.TransDate,vendTrans.AmountMST,OABPostEmail::VendSettleAmountInvoice(vendTransGroup.RecId),OABPostEmail::SettleAmountMST(vendTransGroup.RecId)); }

    i have only selected the code that needs , actually here for same amountMST and transdate there are two invoice and settlement amount but as i have added a Screenshot it is printing only one id and amount ... A second row should also come with same date and amount but differeent invoice and settle amount 

    thankyouuu

    No the amountmst is already a sum amount 

  • Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    RE: Need Help With Query

    By the way, I see a bug in settleAmountMST():

    while select vendSettlement
        where vendSettlement.TransRecId == _recId
    {
        amountMST = vendSettlement.SettleAmountMST;
    }

    I guess you wanted to summarize amount of all settlements with the given TransRecId, but you actually keep overwriting the value in the loop, therefore you'll get the value from the last record only. If you want a sum, do this instead:

    static AmountMST settleAmountMST(RecId _transRecId)
    {
    	VendSettlement vendSettlement;
    	
    	select sum(SettleAmountMST) from vendSettlement
    		where vendSettlement.TransRecId == _transRecId;
    	
    	return vendSettlement.SettleAmountMST;
    }

  • Martin Dráb Profile Picture
    Martin Dráb 230,848 Most Valuable Professional on at
    RE: Need Help With Queryy, Can you please Help!! Thankyou

    It seems that most of code you shared isn't related to the topic of this thread. Could you please trrow everything irrelevant (such as all the logic about emails and unused variables), so we can focus on the actual problem? It will make debugging easier for you too.

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

Announcing Our 2025 Season 1 Super Users!

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

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans