SAP CPQ: How to get database tables list?

[former_member611643]

Hi, friends!

I need to get a "Company Code" for the current authorized user, but in the "Users" table exists only "CompanyId" which value is Foreign Key for some Primary Key.

Does anybody know where I can get a list of database tables in SAP CPQ, especially how to find the "Companies" table?

P.S: This variants does not work.

myCompany = SqlHelper.GetList("SELECT * FROM company")
myCompanies = SqlHelper.GetList("SELECT * FROM companies")

Thanks.

[former_member611643 · Accepted solution]

Here's my issue solution (code bellow).

Unfortunately, there's no information about tables in SAP CPQ, but you can get the necessary information from "API Snippets" in Workbench/Script Workbench window!

lv_company_code = User.Company.CompanyCode
ls_promo = SqlHelper.GetSingle("SELECT MAX(promo_percent) AS discount FROM promos WHERE company_code LIKE '{0}'".format(lv_company_code))

[Yogananda]

HI dkolodin

Ok got it and thanks for quick reply.

I am not sure using database to fetch Company ID

Other Approach : Using the CPQ API to get Company ID

GET/setup/api/v1/admin/companies

[former_member611643]

Hi, yoganandamuthaiah.

Thanks a lot for your advice & feedback.
I found the solution. Here's it (marked as answer).

[Sergey_L]

When columns are linked to foreign keys, you can generate a report for those.

Setup → Report Module → Administration → Data Source → Add → Name: sys_foreign_keys (or whatever), Type: Query, Query:

SELECT 
    OBJECT_NAME(f.parent_object_id) AS table_name
    ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
    ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
    ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
FROM sys.foreign_keys AS f  
INNER JOIN sys.foreign_key_columns AS fc
    ON f.object_id = fc.constraint_object_id

→ Save → New → New Report → Report Name: sys_foreign_keys (might as well match the data source name), Report Data Source: sys_foreign_keys → Next → Select the toggle "AND" instead of "OR" → For the filter criteria, use:

• table_name | Contains | Parameter | table_name
• constraint_column_name | Contains | Parameter | constraint_column_name
• referenced_object | Contains | Parameter | referenced_object
• referenced_column_name | Contains | Parameter | referenced_column_name

→ Next → Next → Next → Select all available columns → Finish.

Now you can search for your table (Users) and see that the "COMPANYID" column is linked to table "MEMBER_FIRMS_CO_INFO" column "MEMBER_ID".

Inversely, you can search for the table in the referenced_object field and see where it's used.
This doesn't always work (the database schema structure leaves a lot to be desired), but it helps often.

Hope that helps someone.

[Former Member]

I am probably late for this answer.

But this is the query you need to make to list all backend tables (custom as well as system ones) present in your cpq tenant.

tables = SqlHelper.GetList("Select * from sys.tables")
for t in tables:
    Trace.Write(t.name)

[Yogananda]

HI dkolodin

You can see in setup - general - Companies.
Can i understand what are you trying with sql helper for your use case ?

[former_member611643]

Yogananda thanks a lot for your feedback.

I have a custom table with promo data. In this table, I have the field "Company_Code".

By SQL helper I'll find the current user "Company Code" and then get a percentage of promo from my custom table.

At the moment I know only "CompanyId" for the user, but don't know the DB Table for Companies.

If I'll find this table I can get by "CompanyId" my necessary value "Company Code".