Order Query in SQ01 using Table Joins in CRM

[david_liss]

Hello Experts,

I am trying to create a query using table joins in SQ01 in CRM because I need a report that will give me all transactions for a selected Business Partner. I am unable to display any results when running my query. I have found other threads which give a list of possible tables to join together, but the fields used in the joins were not described.

The tables I have joined in my query are as follows:

CRMD_ORDERADM_H

CRMD_ORDER_INDEX

CRMD_PARTNER

BUT_000

I have used SE16 to try to search for additional tables to use for linking the Business Partner with a transaction, but I have been unsuccessful.

Would anyone please give advice as how to proceed?

Reward points are available!

Thank you in advance,

David

[Former Member · Accepted solution]

Dear David,

Please try with TCODE: SQVI for the solution

[david_liss · Accepted solution]

Raju,

I have been using transaction SQVI = QuickViewer to create my query and this is where I am having difficulty. When I try to join fields that look like a match, I receive an error stating "illegal join" and it will not work. Are the tables I listed above able to be joined to make this type of query?

Regards,

David

[Former Member · Accepted solution]

David

The link with be the GUID.

CRMD_ORDERADM_H = Document Header Table

CRMD_ORDERADM_I = Document Line item Table

CRMD_LINK = Lists various ‘link’ GUIDs, to

access order Information

The Table CRMD_ORDERADM_H will contain you transactions , once you find the GUID of your sales transaction in the table CRMD_ORDERADM_H.

You then need to check the table CRMD_LINK.

In this table you will then find a number in 11 = sales. This refers to the table CRMD_SALES. There are also links to many other tables ie Shipping, pricing, org etc.

CRMM_BUT_SET0140 - for Sales Group, Sales Office, District

CRMM_BUT_LNK0141 - for Sales Area data

Transaction CRMD_BUS2000115 allows you to see all sales transactions for a BP. CRMD_BUS2000126 shows you activities for selected BP.

Regards

M