Dynamics Community Forum Thread Details

Periodic Sales invoice Update summary by Invoice Account, then by delivery note - BUT no grouping of qty for similar item Ids

Hi All,

Hopefully someone can assist in this.

My company requires an invoice modification made to the periodic sales invoice update process.

They typically require sales invoices to be firstly grouped by Invoice Account - so only 1 invoice per invoice account, per invoice run.

Secondly, they require each transaction to be based on the delivered qty of each sales order line - so the focus in on the delivery note lines.

Out of the box it will do one of two things; create one invoice per account and then group all delivery note lines where the item ID is the same and sum the qty to create one line instead of multiple. I get this as it reduces the overall number of lines on an invoice and makes sense. However our customers need to see each and every delivery note line as a transaction itself - to be created as a distinct invoice transaction.

The other way, summary by journal (delivery note) - creates one invoice per delivery note - her the qtys are not summarised.

I need a combination of both - whereby the second option - each delivery note line appearing as an invoice line exists - but there is only one invoice created - per invoice account.

I had written a customisation myself, whereby the user would run their select to obtain all delivery note lines within a given timeframe. Next I would then rearrange by journal (sales delivery note) - this creates one invoice line per delivery note line.

My final stage is a mod I have written which selects loops through the created SalesParmTable records (provisioinal invoice header recs) groups them by invoice account, takes the TableRefId from the first one it finds and then a second select is done to update all salesParmLine records where the invoice account matches that of the salesParmTable. The TableRefId of each salesParmline is then updated with that of the selected salesParmTable - thereby ensuring that all salesParmLine records are linked to only one salesParmTable record or invoice per invoice account.

The result is exactly what I require, I have checked the records in SalesParmTable and SalesParmLine and all appear fine - however, when I go to post I get a message informing me under the first item code that "The Transaction is already posted."

Can someone please advise me on how I can resolve this or what is the best method?

Thanks

Quick Links

Leaderboard