Open Forum

  • 1.  query a customer on a prschid for a certain year?

    Posted Mar 30, 2021 02:23 PM
    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
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: query a customer on a prschid for a certain year?

    GPUG ALL STAR
    Posted Apr 01, 2021 12:38 PM
    Hi @Larry Carrethers

    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: query a customer on a prschid for a certain year?

    Posted Apr 01, 2021 12:52 PM

    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
    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.




    Academy - Online Interactive Learning from Experts


  • 4.  RE: query a customer on a prschid for a certain year?

    GPUG ALL STAR
    Posted Apr 01, 2021 01:12 PM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: query a customer on a prschid for a certain year?

    Posted Apr 01, 2021 01:24 PM

    IV10402

     




    Academy - Online Interactive Learning from Experts


  • 6.  RE: query a customer on a prschid for a certain year?

    TOP CONTRIBUTOR
    Posted Apr 02, 2021 02:25 PM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: query a customer on a prschid for a certain year?

    Posted 18 days ago
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: query a customer on a prschid for a certain year?

    TOP CONTRIBUTOR
    Posted Apr 01, 2021 12:47 PM
    Hi @Larry Carrethers,

    @Jo deRuiter 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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: query a customer on a prschid for a certain year?

    TOP CONTRIBUTOR
    Posted Apr 02, 2021 12:47 PM
    Edited by Thaddeus Suter Apr 02, 2021 02:14 PM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role