Best source for TotalInvoiceAmount on SO Processed

[pricerc]

I'm converting some old code for our next upgrade from Jiwa 6.

Customer has some old code for generating XML for an EDI implementation.

The meat of the code is in a stored procedure, where they are querying DB_Trans to get the invoice and GST totals.

I'm recoding this to just generate the XML in a VB.Net plugin (because VB does XML), in _salesOrder.Processed (In Jiwa 6, they have it firing in SalesOrderProcess_ProcessFinalBefore).

So the question is: if I'm exporting the EDI file in the Processed event, what're the best properties to replace the DB_Trans values with?

(current) SalesOrderHistory has HistoryTotal, but no GST amount?

I could query the DB_Trans records, but I feel like that shouldn't be necessary?

[Mike.Sheen]

So the question is: if I'm exporting the EDI file in the Processed event, what're the best properties to replace the DB_Trans values with?

(current) SalesOrderHistory has HistoryTotal, but no GST amount?

I could query the DB_Trans records, but I feel like that shouldn't be necessary?

Usually there is only one debtor transaction, but it is possible to have multiple debtor transactions for a single sales order - which I'm only mentioning because it's in defence of the Process_DebtorTransactionsEnd event's parameter being a LIST of DebtorTransaction. We let plugins mess with that to do things like split a debtor transaction into multiple transactions, for things like a payment terms solution (DEV-5665 if you're interested).

So, for your purposes you could listen to the Process_DebtorTransactionsEnd event and stash that List(Of DebtorTransaction) away for use later when the Processed event is raised. That List will typically only be a list of 1 item - but it's the exact values we use to perform our INSERT INTO DB_Trans - so it's got the properties you're after - GSTAmount and Amount.

[pricerc]

So the question is: if I'm exporting the EDI file in the Processed event, what're the best properties to replace the DB_Trans values with?

(current) SalesOrderHistory has HistoryTotal, but no GST amount?

I could query the DB_Trans records, but I feel like that shouldn't be necessary?

Usually there is only one debtor transaction, but it is possible to have multiple debtor transactions for a single sales order - which I'm only mentioning because it's in defence of the Process_DebtorTransactionsEnd event's parameter being a LIST of DebtorTransaction. We let plugins mess with that to do things like split a debtor transaction into multiple transactions, for things like a payment terms solution (DEV-5665 if you're interested).

So, for your purposes you could listen to the Process_DebtorTransactionsEnd event and stash that List(Of DebtorTransaction) away for use later when the Processed event is raised. That List will typically only be a list of 1 item - but it's the exact values we use to perform our INSERT INTO DB_Trans - so it's got the properties you're after - GSTAmount and Amount.

In this case, there's at least an assumption of only one - there's no SUM happening, and it's one 'EDI' line per 'SO' Line.

So is there anything already in the snapshot when Processed fires that I can use instead?

I've tentatively using LINQ to SUM:

Code: Select all

         Dim invoiceTotal = (Aggregate sol In lines.AsEnumerable() Into Sum(sol.InvoicePrice * sol.QuantityThisDelivery)) * If(invoice.CreditNote, -1, 1)
         Dim invoiceGST = (Aggregate sol In lines.AsEnumerable() Into Sum(sol.UnitGSTAmount * sol.QuantityThisDelivery)) * If(invoice.CreditNote, -1, 1)


(I don't know the history behind this code, it's not mine)

[Mike.Sheen]

So is there anything already in the snapshot when Processed fires that I can use instead?

Yep - use the SalesOrder.SalesOrderLines.ExGSTTotal and SalesOrder.SalesOrderLines.GSTTotal to arrive at that figure - but you'll also have to add freight to the ex-gst total:

Code: Select all

SalesOrder.SalesOrderHistorys((SalesOrder.SelectedHistoryNo)).CartageCharge1.ExGSTAmount + SalesOrder.SalesOrderHistorys((SalesOrder.SelectedHistoryNo)).CartageCharge2.ExGSTAmount + SalesOrder.SalesOrderHistorys((SalesOrder.SelectedHistoryNo)).CartageCharge3.ExGSTAmount

And the GST for the freight also:

Code: Select all

SalesOrder.SalesOrderHistorys((SalesOrder.SelectedHistoryNo)).CartageCharge1.GSTAmount + SalesOrder.SalesOrderHistorys((SalesOrder.SelectedHistoryNo)).CartageCharge2.GSTAmount + SalesOrder.SalesOrderHistorys((SalesOrder.SelectedHistoryNo)).CartageCharge3.GSTAmount

[pricerc]

Thanks Mike.

As it happens, they currently use non-stock inventory items for freight, so I wouldn't have picked that up as a bug in testing.

But I know if I don't put it in, they'll start using the freight.