Sales for customer

jonathanchye

Hi all,

I'm fairly new here so please bear with me

I'm writing TSQL for simple report which shows Sales (LCY) by customer. As a reference I'm using the Customer Statistics window to check if my values are correct.

Initially I went with the Value Entry table by summing the Sales Amount (Actual) column. The reason is the report also includes other fields for the report such as Item No. This method shows the correct Sales figure for most customers except some.

I've then replicated in TSQL the same logic as the Sales (LCY) flow field on the Customer table (summing the Sales (LCY) column from the Cust Ledger Entry table) which as expected consistently gives me the same figure as on the NAV window.

My question is why are the sales values different for some customers? Am I missing anything obvious here?

Below is a stripped down TSQL code I've used for testing. The results from Value Entry table seems to be less than expected.

select sum([Sales Amount (Actual)]) from [MYCOMPANY].[dbo].[MYCOMPANY$Value Entry]
where [Source No_] = '8127743'
and year([Posting Date]) = 2015

select sum([Sales (LCY)]) from [MYCOMPANY].[dbo].[MYCOMPANY$Cust_ Ledger Entry]
where [Customer No_] = '8127743'
and year([Posting Date]) = 2015

jglathe

Hi Jonathan,

Value Entries are only for amounts related to items (inventory). If you also sell services, or some revenue is posted via G/L accounts directly, then you can't have the full picture.
The Sales (LCY) field in the customer ledger entries should provide this information. There are issues, though:
1. If someone posts via general journal without the document type "invoice" or "credit memo", it won't be filled in.
2. If someone posts via general journal with multiple lines (not account - balance account), it won't be filled in, regardless of document type.
These issues can be resolved with some extra code in CU13. You can even correct this for existing postings.
With that in mind, Sales (LCY) is the better choice.

with best regards

Jens

jglathe

Yes it is.

jglathe

Hi Jonathan,

Value Entries are only for amounts related to items (inventory). If you also sell services, or some revenue is posted via G/L accounts directly, then you can't have the full picture.
The Sales (LCY) field in the customer ledger entries should provide this information. There are issues, though:
1. If someone posts via general journal without the document type "invoice" or "credit memo", it won't be filled in.
2. If someone posts via general journal with multiple lines (not account - balance account), it won't be filled in, regardless of document type.
These issues can be resolved with some extra code in CU13. You can even correct this for existing postings.
With that in mind, Sales (LCY) is the better choice.

with best regards

Jens

jonathanchye

Thanks Jen! That really clears it up. I will have to clarify with the company if they are fine to only see item only sales figures as I have created stored procedures and data warehouse tables based on Value Entries.
If not I guess it's not too much of a problem to change to Customer Ledger Entries. I will just have to figure out method to also include item level information in the report.

jonathanchye

Apologies, one more question, is the Sales Amount (Actual) column in Value Entries table in local currency?

jglathe

Yes it is.