Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Larry Carrethers]

I’m looking for a query that will give me the total sales a customers made within a certain date range and the price schedule the sale was on.
Not sure if i can get a report directly out of GP or a query i can write.

——————————
Larry Carrethers
Sr. System Admin/DBA
Dolese Bros. Co.
Oklahoma City OK
——————————

[Jo deRuiter]

Hi

It depends on how you price, whether extended pricing or regular, but use the ‘SalesLine?Items’ view to build out your report. NEVER OVERWRITE AN OUT OF THE BOX VIEW, SO ADJUST IT AND CREATE A NEW VIEW

SELECT
[Customer Number]
,[Document Status]
,[Hold]
,[SOP Type]
,[Document ID]
,[SOP Number]
,[Document Date]
,[Document Amount]
,[Due Date]
,[Item Number]
,[Item Description]
,[QTY]
,[U Of M]
,[Extended Cost]
,[Extended Price]
,[Unit Cost]
,[Unit Price]
,[Current Cost] as ‘Item Current Cost’
,[Standard Cost] as ‘Item Standard Cost’
,[Item Class Code]
,[Item Code]
,[Valuation Method]
,[Item Description from Item Master]
,[Item Tracking Option]
,[Item Type]
,[PriceLevel]
,[PriceLevel from Customer Master]
,[PriceLevel from Item Master]
,[PriceLevel from Sales Transaction]
,[Price Group]
,[Price Method]
FROM [TWO].[dbo].[SalesLineItems]

——————————
Jo deRuiter
?? GPUG ALL STAR
??Granite Award Winner
Atlanta Chapter Leader
Aisling Dynamics Business Solutions
Business Applications Practice Manager
jo.deruiter@aislingdynamics.com
9518674366
aislingdynamics.com
——————————
——————————————-

[Larry Carrethers]

We do use extended pricing but I do not see the field prschid in this query

SELECT
[Customer Number]
,[Document Status]
,[Hold]
,[SOP Type]
,[Document ID]
,[SOP Number]
,[Document Date]
,[Document Amount]
,[Due Date]
,[Item Number]
,[Item Description]
,[QTY]
,[U Of M]
,[Extended Cost]
,[Extended Price]
,[Unit Cost]
,[Unit Price]
,[Current Cost] as ‘Item Current Cost’
,[Standard Cost] as ‘Item Standard Cost’
,[Item Class Code]
,[Item Code]
,[Valuation Method]
,[Item Description from Item Master]
,[Item Tracking Option]
,[Item Type]
,[PriceLevel]
,[PriceLevel from Customer Master]
,[PriceLevel from Item Master]
,[PriceLevel from Sales Transaction]
,[Price Group]
,[Price Method]
FROM [TWO].[dbo].[SalesLineItems]

 

Larry E Carrethers Administrator, Sr. System Database Information Technology – Application Services Phone: +1-405-297-8383 http://www.dolese.com

NOTICE OF CONFIDENTIALITY: The information contained in this transmission including any attached documentation is privileged and confidential. It is intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copy of this communication is strictly prohibited. If you have received this communication in error, please notify Dolese Bros. Co. immediately by replying to this email,and please delete all copies of this message and any attachments immediately.

——————————————-

[Jo deRuiter]

There are no fields in GP that are named ‘PRSCHID’, can you let us know where you are seeing that field?

But, if it is PRCSHID, here is a q that will work:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT
[Customer Number]
,[Document Status]
,[Hold]
,[SOP Type]
,[Document ID]
,[SOP Number]
,[Document Date]
,[Document Amount]
,[Due Date]
,[Item Number]
,[Item Description]
,[QTY]
,[U Of M]
,[Extended Cost]
,[Extended Price]
,[Unit Cost]
,[Unit Price]
,[Current Cost] as ‘Item Current Cost’
,[Standard Cost] as ‘Item Standard Cost’
,[Item Class Code]
,[Item Code]
,[Valuation Method]
,[Item Description from Item Master]
,[Item Tracking Option]
,[Item Type]
,[PriceLevel]
,[PriceLevel from Customer Master]
,[PriceLevel from Item Master]
,[PriceLevel from Sales Transaction]
,[Price Group]
,[Price Method]
,iv.PRCSHID
FROM [dbo].[SalesLineItems] LEFT JOIN
IV10401 IV ON [SalesLineItems].[Item Number]=iv.ITEMNMBR

——————————
Jo deRuiter
?? GPUG ALL STAR
??Granite Award Winner
Atlanta Chapter Leader
Aisling Dynamics Business Solutions
Business Applications Practice Manager
jo.deruiter@aislingdynamics.com
9518674366
aislingdynamics.com
——————————
——————————————-

[Larry Carrethers]

IV10402

 

——————————————-

[Thaddeus Suter]

This view returns the price sheet(s) assigned to an item (multiple) not the price sheet used for pricing the line item which is customer specific thru the price book. Items typically are assigned to many price sheets.

——————————
Thaddeus Suter
Retus, Inc
HELOTES TX
——————————
——————————————-

[Larry Carrethers]

The problem i have with this query is if the price schedule i’m trying to narrow this down to is prcschid = ‘A’ and i change your code to now pull for prcshid = ‘B’ alot of the same customers are coming up in the list.  There shouldn’t be a customer on 2 different price schedules.

SELECT distinct
[Customer Number]
–,[Document Status]
–,[Hold]
–,[SOP Type]
–,[Document ID]
–,[SOP Number]
–,[Document Date]
–,[Document Amount]
–,[Due Date]
–,[Item Number]
–,[Item Description]
–,[QTY]
–,[U Of M]
–,[Extended Cost]
–,[Extended Price]
–,[Unit Cost]
–,[Unit Price]
–,[Current Cost] as ‘Item Current Cost’
–,[Standard Cost] as ‘Item Standard Cost’
–,[Item Class Code]
–,[Item Code]
–,[Valuation Method]
–,[Item Description from Item Master]
–,[Item Tracking Option]
–,[Item Type]
–,[PriceLevel]
–,[PriceLevel from Customer Master]
–,[PriceLevel from Item Master]
–,[PriceLevel from Sales Transaction]
–,[Price Group]
–,[Price Method]
,iv.PRCSHID
FROM [dbo].[SalesLineItems] LEFT JOIN
IV10401 IV ON [SalesLineItems].[Item Number]=iv.ITEMNMBR
WHERE IV.PRCSHID = ‘2021 DISTRIBU’/*’2021 MASON’*/
AND [Customer Number] != ‘null’ and [Customer Number] != ‘ ‘
ORDER BY [Customer Number]

——————————
Larry Carrethers
Sr. System Admin/DBA
Dolese Bros. Co.
Oklahoma City OK
——————————
——————————————-

[Samantha Higdon]

Hi ,

gives you a great starting point on the view.  A couple of additional items to take into consideration depending how you define “sales”.

1.) The  view below will pull returns as well.  Technically those prices would want to be considered as negatives to reduce the total sale amount since the customer returned something.

2.) If you issue documents through the receivables side of the house (i.e. Sales>>Transactions>>Transaction Entry), they won’t be picked up in the view provided below.  You’ll want to do another query to pull those in.  You could start very basic with something like

Select * from [TWO].[DBO.].ReceivablesTransactions? and filter from there by fields you want, date range, etc.

Does that make sense?  Let me know if you have any questions.?

Thanks,

Samantha

——————————
Samantha Higdon ,CPA,CGMA
Consultant
Lagom, LLC
Carmel IN
——————————
——————————————-

[Thaddeus Suter]

PRSCHID is Price Sheet ID and it prices the line Item for a Customer SOP Order/Invoice so you can get a report/view summing line item sales by Customer and by Price Sheet from the SOP Line Items History table SOP30200.

The difficulty depends on your Extended Pricing setups whether this can be done reasonably direct. If you assign your Price Sheet IDs (PRSCHID) direct to the Customer reasonably direct. If you only assign Price Sheets to Price Books and then assign the Price Book to the Customer more difficult.

In any case, use the RM00500 table where LINKCODE = your CustomerID to find their possible Price Sheets (PRSCHID). Then join your IV10402 to find the ITEMNMBR for  the possible Price Sheets (PRSCHID) that match your Customers in RM00500.

Now at the SOP Invoice SOP10200, each Line Item could be from a different Price Sheet for the Customer but this value is not stored after the Price is set.
There is a fancy Price Trace Query off the Unit Price zoom that will also show you the Price Sheet (PRSCHID) used to price each line item. But you have to click it.
If you are using Extended Price Groups and not assigning the Price Sheets directly to the Items and Customers, it gets more difficult as you must then join your query through Price Books and Price Groups to find the items and their price sheet.

This should get you going looking in RM00500. If you have no CustomerIDs under LINKCODE then as I said it gets more difficult as you will need to see what PriceSheets are in the Price Book assigned to the Customer to join  your IV10402. And if the Items are in PriceGroups even more fun….

edit: PRSCHID …PRCSHID  Price Sheet ID of Extended Pricing
——————————
Thaddeus Suter
Retus, Inc
HELOTES TX
——————————
——————————————-