Find Item Sales Amount Inc. VAT

slmaluwa

Dear Gurus,
I have come to a situation where the requirement is to prepare a report or analysis get the net qty and net value of any item's sales. The value need to be the Amount Including VAT for all item sales/credit memos.

QTY = SUM_OF_IN_QTY + SUM_OF_CR_QTY for the PERIOD
VALUE = TOTAL_OF_AMOUNT_INCLUDING_VAT

I tried looking at several tables. Sales Invoice Line and Sales Credit Memo Line tables has this amount but two different tables. I don't how to combine these two and get the NET.

Item Ledger Entry and Value entry have only the NET VALUE (amount excluding VAT).

Is there any easy way to get this information? Is it possible to add a field to Item Edger Entry and fill it with Amount Including VAT also for each Item Sale? If yes, how do I do it? ](*,)

Thank you all

Maluwa

garak

you can calc the amount incl. vat based in the entries with using the vat field in entries (but then you are in trouble if the vat is changed in the vat table and the vat code is the same).
Or, easier create a new field in your entry table and fill this in cu 22 where the value entries are created (this was my solution on some customer applications).

But why not a report over Sales inv. Lines and a report over Sales Cr. Memo Lines?
So you can group this infos (this is not so easy possible when you run over Value Entries).

Or do you need this info in separate (custom made) Analyses forms which should use a calcfield (excl. vat and incl. vat fields)?

Regards

slmaluwa

Thanks for the reply
It is actually needed for a some complex calculations and analysis like monthly commissions, item performances etc.
Your suggestion of modifying the CU 22 seems to be the better method for me because all other information needed are already exists in that table. I can create the field.

But, unfortunately, I dunno exactly on which place we insert this line to add the field and where to get the value from. :-k
This value need to be added for Invoices as well as Credit Memos.

Can you help me, please?

TIA

Maluwa

garak

check CU 22. There are only some functions where the value entries created / modified.

Do you have permissions to modify this CU?

slmaluwa

Yes, I have permission.
I find two places to enter ILE and Value Entry entries.
I will give it a try and let you all know.

slmaluwa

Hello
I dunno where to get this value from? I tried creating a field "Amount Including VAT" in ILE and added this line in CU22

InitItemLedgEntry(VAR ItemLedgEntry : Record "Item Ledger Entry")
         ItemLedgEntry."Amount Including VAT" := "Amount Including VAT" ;

It's says this field not found. Any ideas?

garak

That you can store the "Sales Amount Incl, Vat" you must add in table "Item Journal Line" a field like "Sales Amount incl. Vat"
These field you fill out in the codeunit where you need the "Sales Amount incl. Vat" (like CU 80 or the CU for posting a journal)

In CU 22 you fill the field in the Value Entry based on the field from Journal Line (take a look into functions InsertCapValueEntry(), InsertValueEntry())


Regards

slmaluwa

Thank you garak
I have found the places in CU80 and CU22 and made the changes. Now the information is being recorded for all new Invoices and Credit Memos.

But, my next challenge is fill the new field "Sales Amount Incl. VAT" I added with ALL PAST DATA to Item Ledger Entry table.
I see that this information is available in Sales Invoice Line and Sales Cr Memo Line tables. But, do not see an easy link between these tables and ILE.

Any advice or suggestion would be greatly appreciated.

Thank you again

Maluwa

garak

Slmaluwa, you didn't read the post above?
For example in CU 80 there is a section where the item journal line table is filled. so on this place you fill your new field.
Then in CU 22 you fill the new field in entry based on the field from item journal line.
A little tip: store the sales amount not in the item ledger entry, store it in the value entry. Value entries are include amount infos, the ledger entries includes inventory infos.

Regards

slmaluwa

garak wrote:

Slmaluwa, you didn't read the post above?
For example in CU 80 there is a section where the item journal line table is filled. so on this place you fill your new field.
Then in CU 22 you fill the new field in entry based on the field from item journal line.
A little tip: store the sales amount not in the item ledger entry, store it in the value entry. Value entries are include amount infos, the ledger entries includes inventory infos.

Regards

Hi Garak
Actually speaking, I read and fully understood your earlier post, and completed the customisation. It works perfectly (all new transactions are recorded with required value) and thank you very much. I chosen to store the value in Item Ledger Entry due to the availability of other information needed for this purpose.

What I am now stuck with is to FILL the field with relevant value for PAST [or existing] records. I trying to find a way to link back to Sales Invoice Line or other tables where I can get the value to populate for the existing records.

Any ideas?

garak

mhm, you're really sure, that you will use the item ledger entries :?:
What is with following case:

1 Order with 20 Items to a Sales Price (incl. VAT) for 10 119 Euro
For these Order we create one Shipment with 20 Items and a price with 119 Euro. (So One Item LEdger Entry)
Now, the customer need 4 Invoices and we must change the Sales price in every Invoice.
Invoice one 5 Items with 119 Euro (No new Item Ledger Entry, but a Value Entry under the Item Ledger Entry)
Invoice two 7 Items with 100 Euro (No new Item Ledger Entry, but a Value Entry under the Item Ledger Entry)
Invoice three 6 Items with 130 Euro (you know what i type here ...)
Invoice four with 2 Items with 0 Euro.
so one Item Ledger Entry (that stores the Price incl. Vat based on the Shipment / Order) but 4 Value Entries. but where are the correct prices??????

How to fill the "Sales Price incl. Vat" for all old Invoices / Cr. Memos u must "select" the entries which are created for this posted document / line.

Regards

slmaluwa

Garak

My goodness!!. Thank you very much helping me to avoid a disaster. I simulated what you have mentioned and you are 100% correct. I am now changing it to capture in Value Entry table. Need to test the whole idea again I also need more information in Value Entry table.

Will update when this is completed

Thank you again