Inserting Details During Sales Order Invoice Posting

(2) Share

Report

Posted on by Ayushaman

163

Description:
I am working on a requirement where I need to insert insurance details during the invoice posting of a sales order in Dynamics 365 Finance and Operations. Specifically, I need to retrieve certain details from the CustInvoiceTrans table, such as the LineAmount and InvoiceDate, in order to insert insurance-related information.

Current Approach:
I attempted to implement the logic by extending the SalesInvoiceJournalPost class and calling my logic after the EndPost method as well as the PostJournalPost method. However, I am encountering an issue where I am not able to retrieve the desired data from the CustInvoiceTrans table. While the corresponding records are being created in the CustInvoiceJour table, I am unable to find any records in the CustInvoiceTrans table from which I can fetch the LineAmount and InvoiceDate.

Questions:

Is there a more appropriate place to insert the logic in order to ensure that I can retrieve the required details from the CustInvoiceTrans table?
Would extending the SalesFormLetter_Invoice and calling my logic after the CreatePayment() method be a correct approach, or is there a better method to achieve this goal?
should I create eventhandler for post ? if yes what i have to include in that

What is the correct sequence of actions or place where I should be inserting the logic to ensure the data is available for retrieval and insertion of insurance details?

CUSTINVOICETRANS
protected void InsertRetailInsuranceDetails()
  {
      SalesTable                   salesTable1;
      CustInvoiceJour              custInvoiceJour = this.custInvoiceJour;
      CustInvoiceTrans             _custInvoiceTrans    = custInvoiceTrans;
      real                         totalLineAmount = 0;
      real                         totalPcs = 0;
      SalesLine                         _salesLine;
      NewSalesLine                      newSalesLine;
      RetailInvoiceTable             insuranceTable;
      RetailInsuranceTable           retailInsuranceTable;
      CustInvoiceId                       invoiceno =custInvoiceJour.InvoiceId;
     definedParameter					_definedparameter = definedParameter::find();

      while select LineAmount, SalesId from _custInvoiceTrans  
        where _custInvoiceTrans.InvoiceId == custInvoiceJour.InvoiceId
            
      {
          totalLineAmount += _custInvoiceTrans.LineAmount;
      }

  
      select firstOnly salesTable1
          where salesTable1.SalesId == _custInvoiceTrans.SalesId;

    
      if (totalLineAmount > _definedparameter.MinInsuranceValue)
      {
          select sum(Pcs) from newSalesLine
           where newSalesLine.SalesId == salesTable1.SalesId;
          totalPcs = newSalesLine.Pcs;

          select firstOnly retailInsuranceTable
          where retailInsuranceTable.Store == salesTable.InventSiteId;

          insuranceTable.clear();
          insuranceTable.TransactionID = salesTable1.SalesId;
         
      
          insuranceTable.InsuranceAmount = totalLineAmount;
       
          insuranceTable.RetailInsuranceNo = salesTable1.SalesId;
          insuranceTable.InsuranceDate = _custInvoiceTrans.InvoiceDate;
         
          insuranceTable.PolicyToDate = _custInvoiceTrans.InvoiceDate + retailInsuranceTable.PeriodInsuranceDays;
          insuranceTable.MasterPolicyNo = retailInsuranceTable.MasterPolicyNo;
          
          insuranceTable.PeriodInsuranceDays = retailInsuranceTable.PeriodInsuranceDays;
         
          insuranceTable.PCS = totalPcs;

          
          ttsbegin;
          try
          {
              insuranceTable.insert();
          }
          catch
          {
              throw error("Insurance insertion failed.");
          }
          ttscommit;
      }
  }

I shared you basic snippet logic what i want to insert while posting .

Thanks,
Ayushaman

Categories:

Dynamics 365 Finance

All responses (6)

Answers (1)

Ayushaman 163 on at

Like (0)

Report

Inserting Details During Sales Order Invoice Posting

Hi Martin,

Thanks a Bunch as i got details in my custinvoicetrans as i was expecting as guided by you i wasn't reading it properly.

Beside this i also got insights on how i can write code more efficiently that a add up from your reply which i really appreciates.

Thanks,
Ayushaman
Verified answer

Martin Dráb 230,560 Most Valuable Professional on at

Like (1)

Report
Inserting Details During Sales Order Invoice Posting

I simplified your code to make is easier to understand:

protected void insertRetailInsuranceDetails() { CustInvoiceTrans transAmountSum; select sum(LineAmount) from transAmountSum where transAmountSum.InvoiceId == custInvoiceJour.InvoiceId; real totalLineAmount = transAmountSum.LineAmount(); if (totalLineAmount > DefinedParameter::find().MinInsuranceValue) { RetailInsuranceTable retailInsuranceTable; select firstOnly retailInsuranceTable where retailInsuranceTable.Store == salesTable.InventSiteId; RetailInvoiceTable insuranceTable; insuranceTable.TransactionID = custInvoiceTrans.SalesId; insuranceTable.InsuranceAmount = totalLineAmount; insuranceTable.RetailInsuranceNo = custInvoiceTrans.SalesId; insuranceTable.InsuranceDate = custInvoiceTrans.InvoiceDate; insuranceTable.PolicyToDate = custInvoiceTrans.InvoiceDate + retailInsuranceTable.PeriodInsuranceDays; insuranceTable.MasterPolicyNo = retailInsuranceTable.MasterPolicyNo; insuranceTable.PeriodInsuranceDays = retailInsuranceTable.PeriodInsuranceDays; NewSalesLine newSalesLine; select sum(Pcs) from newSalesLine where newSalesLine.SalesId == custInvoiceTrans.SalesId; real totalPcs = newSalesLine.Pcs; insuranceTable.PCS = totalPcs; ttsbegin; try { insuranceTable.insert(); } catch { throw error("Insurance insertion failed."); } ttscommit; } }

And I notice that you're completely missing logic to read CustInvoiceTrans records from database. You refer to custInvoiceTrans variable, but that can't give you all the lines.

If you aren't interested in lines (you just want the total amount, invoice date and the main SalesId, then don't worry about CustInvoiceTrans at all.

But if you need information from all lines (e.g. all the SalesIds included in the given invoice), use a while select statement to load the records.
Martin Dráb 230,560 Most Valuable Professional on at

Like (0)

Report

Inserting Details During Sales Order Invoice Posting

I'm sorry, but your analysis of the problem must be wrong. It's not true that the invoice posting process in SalesInvoiceJournalPost has access just invoice headers, not the whole invoices, and the actual posting is done somewhere else.

If you look into SalesInvoiceJournalPost class, you'll see a lot of logic working with CustInvoiceTrans records. For example, notice the select statement for newly created invoice lines in postJournalPost() method.

It's impossible that the records exist in the database when code runs in the standard class but not in your extension. The problem must be in how you're trying to get the data.

In fact, the records exist even before SalesInvoiceJournalPost class gets involved, because the journal gets created (by SalesInvoiceJournalCreate) before it gets posted (by SalesInvoiceJournalPost). CustInvoiceTrans records are inserted to the database in FormletterJournalCreate.insertRecordList(). Do you think otherwise?
Ayushaman 163 on at

Like (0)

Report
Inserting Details During Sales Order Invoice Posting

Hi Martin /Andre ,
[ExtensionOf(classStr(SalesInvoiceJournalPost))] final class AgmSalesInvoiceJournalPost_Extension { protected void endPost() { next endPost(); this.InsertRetailInsuranceDetails(); } protected void endPostLine() { next endPostLine(); this.createTrans_New(); }

Thank you for your input. Here's my situation:

I am calling my logic in the extension of SalesInvoiceJournalPost. However, I realize that I am not able to retrieve data from the CustInvoiceTrans table at that point because the data seems to only be available after the posting process is completed. At the time I am calling the method, only data in CustInvoiceJour is accessible.

To work around this, I am currently retrieving invoice details (such as the sales line) and invoice data (e.g., invoice date and amount) from CustInvoiceJour. However, If i can I specifically need the data from CustInvoiceTrans after it has been populated.

Regarding debugging, I have already debugged my code and confirmed that the issue lies in the timing of the method call. I've narrowed it down by eliminating other possibilities.

What I would like to know is:

Is there a way to call my logic at a later stage when the CustInvoiceTrans table has been populated? Or the details which i am fetching for invoice amount ,date will work fine.

Thanks for your guidance,I really appreciate it .

Thanks,
Ayushaman
André Arnaud de Cal... 291,472 Super User 2024 Season 2 on at

Like (0)

Report

Inserting Details During Sales Order Invoice Posting

Hi Ayushaman,

Next to the valuable input provided by Martin, you can check the place where you call your logic. Are at that time the lines already created? As Martin mentioned, you can discover that using debugging.
Martin Dráb 230,560 Most Valuable Professional on at

Like (1)

Report

Inserting Details During Sales Order Invoice Posting

1. Yes, SalesInvoiceJournalPost is the right class.

2. Doing it in SalesFormLetter_Invoice is possible, but not better.

3. No, you don't have to. Using CoC usually leads to simpler code.

4. What you should do depends on what you want to achieve.

You should tell us more about your problem then mere I am unable to find any records in the CustInvoiceTrans table. Please show us also your code where you're calling the method and tell us what you've found when you debugged your code, before giving up and asking here.

One obvious problem is that you're using a wrong condition to get lines for an invoice journal header. InvoiceId isn't necessarily unique; the relation includes SalesId, InvoiceDate and NumberSequenceGroup as well.