web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SQL Query for PriceDis...
Microsoft Dynamics AX (Archived)

SQL Query for PriceDiscountJounals

(0) ShareShare
ReportReport
Posted on by richierich79 300

Hi

I am trying to write a couple SQL query regarding Price Discount Journals but I am having some issues getting it right

I want to

Select All Items

Where the items have a relationship with  specific customers

Select All PriceDiscountJournals

Where there is a relationship to a list of customers and list of items

I'm not sure if this the right place to ask this. If not any direction to an appropriate forum would be greatly appreciated.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: SQL Query for PriceDiscountJounals

    The relationship from PriceDiscAdmTrans to InventTable is straight-forward, assuming that you're talking about Price (sales) or Price (purchase) records.  It's merely PriceDiscAdmTrans.ItemCode == 0 and PriceDiscAdmtrans.ItemRelation == InventTable.ItemId.

    The relationship from PriceDiscAdmTrans to CustTable is more complicated.  When AccountCode is 0, then AccountRelation is the CustTable.AccountNum itself.  When AccountCode is 1, then AccountRelation refers to CustTable.PriceGroup, which can be multiple customers.  And when AccountCode is 2, then the record applies to all customers.  These correspond to the Table, Group, and All records as seen from the UX.

    Does that help you?

  • richierich79 Profile Picture
    richierich79 300 on at
    RE: SQL Query for PriceDiscountJounals

    Very much so. I believe the follow query should give me all ItemIds where there is a related PriceDiscCountJournal to items and the customers

    SELECT i.ITEMID

    FROM dbo.CustTable cust

    INNER JOIN dbo.PRICEDISCADMTRANS disc ON  disc.ACCOUNTRELATION = cust.ACCOUNTNUM

    INNER JOIN  dbo.InventTable i ON disc.ITEMRELATION = i.ITEMID

    WHERE disc.ACCOUNTCODE = 0

    How do PriceGroups differ from CustomerGroups?

    I also want to perform the same query for a customer group. Is this the same as priceGroup or would I have to find all the customers related to a group and just restrict the above query to those customers?

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: SQL Query for PriceDiscountJounals

    Hi,

    for the first selection this is the required query

    SELECT distinct(pdt.ITEMRELATION)

    FROM dbo.PRICEDISCADMTRANS pdt

    WHERE pdt.ITEMCODE = 0

    and pdt.ACCOUNTCODE = 0

    -- TableGroupAll EDT: Table = 0 ; Group = 1 ; All = 2

    For the second selection , you have to change the filter on the ACCOUNTCODE field to 1 (group) and you will select all the item prices related to a customer group (not specified).

    If you want to select a specific customer group you have to put it on the accountrelation field.

    SELECT distinct(pdt.ITEMRELATION)

    FROM dbo.PRICEDISCADMTRANS pdt

    WHERE pdt.ITEMCODE = 0

    and pdt.ACCOUNTCODE = 1

    and pdt.ACCOUNTRELATION = 'yourcustgroup'

    -- TableGroupAll EDT: Table = 0 ; Group = 1 ; All = 2

    About groups usage:

    - Prices defined for a Customer group will be available for all customers that have the customer group defined in his master data.

    - Prices defined for a Item group will be available for all items that have the item group defined in his master data.

    Hope this helps,

    ask freely if you need more clarifications

  • richierich79 Profile Picture
    richierich79 300 on at
    RE: SQL Query for PriceDiscountJounals

    Wow thank you Nicola

    For my own knowledge what does ItemCode represent? (Why 0)

    I also would like to return all the Item groups who have a PRICEDISCADMTRANS record and a relationship to customers. Would you be able to help me with that?

    Also If an item has a PRICEDISCADMTRANS record, and that item belonged to an itemgroup which also has a PRICEDISCADMTRANS record, which one would take priority?

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: SQL Query for PriceDiscountJounals

    "ItemCode" and "AccontCode" are fields that describe what type of fields are the "ItemRelation" and "AccountRelation" fields.

    That means:

    ItemCode = 0 = Table : that means that "ItemRelation" field is related to InventTable so the field will be filled with items.

    ItemCode = 1 = Group : that means that "ItemRelation" field is related to ItemGroups so the field will be filled with item groups (it's used if u have items like t-shirts , they have sampe price but you want them to be defined by color, so you will have 20 t-shirts with same price)

    ItemCode = 2 = All : that means that "ItemRelation" field is not filled , and the price will be available for all the Items.

    For the queries that you are requesting i am assuming that you are making investigations of the distribuition of the prices, are you sure you need the prices listed on the Price Discount Journal or do you need the prices that is actually settled for your items,customers ?

  • richierich79 Profile Picture
    richierich79 300 on at
    RE: SQL Query for PriceDiscountJounals

    Many thanks for the explanation. This is a real help in my understanding

    I'm actually trying to create a view of price discount journals where the user can filter them by customer, customergroup, item and item group. Do you think I am on the right lines?

  • Community Member Profile Picture
    Community Member on at
    RE: SQL Query for PriceDiscountJounals

    Well, if you want to give information about the journal then you are doing it right.

    But if you want to see the real prices registered and used for the orders you have to inquire the PriceDiscTable.

    The price/discount journals works like "stencils", when you register the journal the prices will be registered on the PriceDiscTable and the information in the PriceDiscAdmTrans is only there as history information of the changes made by users.

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at
    RE: SQL Query for PriceDiscountJounals

    Please don't be confused by the phrase Item group and Customer group.  When working with trade agreements the price/discount journals, these are not the Item groups and Customer groups that control ledger posting.  There are price/discount specific item groups, i.e. Item discount groups found under Procurement and sourcing, Setup, Price/discount, Item discount groups.  And there are price/discount specific vendor groups, i.e. Vendor price/discount groups found under Procurement and sourcing, Setup, Price/discount, Vendor price/discount groups.  And, of course, symmetrical groups on the customer/sales side under Sales and marketing.

    All of these groups are found in the table PriceDiscGroup.  There is no relationship from PriceDiscTable or PriceDiscAdmTrans back to the CustGroup or InventItemGroup tables, or the CustTable.CustGroup field.  

    Just to be clear.

  • richierich79 Profile Picture
    richierich79 300 on at
    RE: SQL Query for PriceDiscountJounals

    As I will no doubt have to show the prices and possibly update them I suspect I will need queries off the the PriceDiscTable as well. Are the relationships the same as the PRICEDISCADMTRANS?

  • richierich79 Profile Picture
    richierich79 300 on at
    RE: SQL Query for PriceDiscountJounals

    Brandon. Many thanks, this is something I would have definitely got confused on!

    So if I wanted to get a the relationship between PriceDiscTable and Custgroup I would need to get all the customers out of the CustGroup and then query the PriceDiscTable from teh CUSTTABLE as explained above?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#1
Community Member Profile Picture

Community Member 4

#3
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans