The CS_ Table Schema: Navigating Callidus Commissions Data

Why the CS_ Schema Is a Mystery

SAP acquired Callidus Software in 2018 and integrated it into the SuccessFactors portfolio. But SAP doesn't publish a comprehensive schema guide for the CS_ tables. That's a problem for practitioners: you inherit a Callidus system, you need to understand the data, and you're stuck reverse-engineering a thousand-column table without an official ERD (Entity Relationship Diagram) or data dictionary.

This isn't malice — it's just the reality of enterprise software integrations. SAP owns the code, Oracle owns the database, and you're trying to piece together how they talk. The solution is to become a data archaeologist: use Oracle's data dictionary views to discover the schema yourself, document what you find, and share that knowledge with your team.

The CS_ Naming Convention

Callidus chose a consistent naming pattern. It starts with CS_ (Callidus prefix), followed by the module name, followed by the entity type. Here's how to decode table names:

• CS_COMP_PLAN → CS_ (schema) + COMP (compensation) + PLAN (entity type). This is the plan definition table.
• CS_PLAN_PERIOD → CS_ + PLAN + PERIOD. Periods within a plan.
• CS_PARTICIPANT → CS_ + PARTICIPANT. People who earn compensation.
• CS_CREDIT → CS_ + CREDIT. Transactions credited to participants.
• CS_RESULTS → CS_ + RESULTS. Calculated compensation results.
• CS_COMP_SUMMARY → CS_ + COMP (compensation) + SUMMARY. Rollup totals.
• CS_STATEMENT → CS_ + STATEMENT. Compensation statements.

Column names follow a similar pattern: descriptive underscore-separated names (e.g., START_DATE, EFFECTIVE_START, HIRE_DATE). Some are obvious (ID, NAME, EMAIL), others are cryptic (PRTT_FLAG, CALC_FLAG). Abbreviations are common to save space.

The Reverse-Engineering Queries

You don't need documentation if you can query the data dictionary. Oracle stores schema metadata in system views. Here's your toolkit:

-- List all CS_ tables in the database
SELECT table_name
FROM   all_tables
WHERE  table_name LIKE 'CS\_%' ESCAPE '\'
ORDER BY table_name;

This query returns every table prefixed with CS_. On a typical Callidus system, you'll see 50-150 tables. Write the results to a file and categorize them by module (plans, participants, results, etc.).

-- Get column definitions for a specific table
SELECT column_name, data_type, nullable, CHAR_LENGTH
FROM   all_tab_columns
WHERE  table_name = 'CS_PARTICIPANT'
ORDER BY column_id;

-- Find columns with 'ID' in the name (likely foreign keys)
SELECT table_name, column_name, data_type
FROM   all_tab_columns
WHERE  table_name LIKE 'CS\_%' ESCAPE '\'
AND    column_name LIKE '%ID'
ORDER BY table_name, column_name;

These queries tell you what columns exist, their data types, and whether they allow NULLs. Columns ending in _ID are usually foreign keys — write them down for the join analysis phase.

-- Find primary key for a table
SELECT constraint_name, column_name
FROM   all_cons_columns
WHERE  table_name = 'CS_COMP_PLAN'
AND    constraint_name IN (
  SELECT constraint_name
  FROM   all_constraints
  WHERE  table_name = 'CS_COMP_PLAN'
  AND    constraint_type = 'P'
)
ORDER BY position;

-- Find foreign keys (references to other tables)
SELECT ac.constraint_name, ac.column_name, ac.table_name,
       r_ac.table_name AS referenced_table,
       r_ac.column_name AS referenced_column
FROM   all_cons_columns ac
INNER JOIN all_constraints a ON ac.constraint_name = a.constraint_name
INNER JOIN all_constraints r_a ON a.r_constraint_name = r_a.constraint_name
INNER JOIN all_cons_columns r_ac ON r_a.constraint_name = r_ac.constraint_name
WHERE  ac.table_name = 'CS_CREDIT'
AND    a.constraint_type = 'R'
ORDER BY ac.constraint_name;

Key Tables by Module

Rather than describing all 100+ CS_ tables, let's group the critical ones by functional area. These are the tables you'll query most often.

Plan Module

• CS_COMP_PLAN: Plan definition. Columns: ID (PK), NAME, PLAN_TYPE, STATUS, EFFECTIVE_START, EFFECTIVE_END, DESCRIPTION, CREATED_DATE, LAST_MODIFIED_DATE. Join to CS_PLAN_PERIOD on ID.
• CS_PLAN_PERIOD: Period definition (month, quarter, year). Columns: ID (PK), PLAN_ID (FK), PERIOD_NAME, PERIOD_TYPE, START_DATE, END_DATE, STATUS. Always join to CS_COMP_PLAN to get the plan context.
• CS_PLAN_PARTICIPANT: Assignment of participant to plan + quota. Columns: PLAN_ID, PARTICIPANT_ID, PERIOD_ID, QUOTA_AMOUNT, EFFECTIVE_START, EFFECTIVE_END, STATUS. This is a junction table — rows here mean "participant X is in plan Y for period Z with quota Q."

Participant Module

• CS_PARTICIPANT: People who earn. Columns: ID (PK), NAME, EMPLOYEE_ID, EMAIL, POSITION_ID (FK), PARENT_ID (self-referencing FK), STATUS, EFFECTIVE_DATE, TERMINATION_DATE, CREATED_DATE, LAST_MODIFIED_DATE. The PARENT_ID field builds hierarchy.
• CS_POSITION: Role types. Columns: ID (PK), POSITION_TYPE, DESCRIPTION, STATUS. Referenced by CS_PARTICIPANT.POSITION_ID. Use to classify participants (Sales_Rep, Manager, etc.).

Pipeline & Transaction Module

• CS_TRANSACTION: Raw transaction staging. Columns: ID (PK), SOURCE_ID, SOURCE_REF, AMOUNT, TRANSACTION_DATE, STATUS, RAW_DATA (or similar). This is the first table transactions land in. Usually not queried directly after pipeline processing (too many rows), but useful for debugging import issues.
• CS_CREDIT: Classified, credited transactions. Columns: ID (PK), PARTICIPANT_ID (FK), PLAN_ID (FK), PERIOD_ID (FK), AMOUNT, CREDIT_DATE, STATUS, SOURCE_REF, ERROR_MESSAGE, CREATED_DATE. This is your main transactional table. One row per credit. Join here to find what happened to each transaction.

Results Module

• CS_RESULTS: Calculated compensation per participant per period. Columns: ID (PK), PARTICIPANT_ID (FK), PLAN_ID (FK), PERIOD_ID (FK), TOTAL_SALES, QUOTA_AMOUNT, ATTAINMENT_PCT, RESULT_AMOUNT, INCENTIVE_AMOUNT, STATUS, CREATED_DATE. One row per participant per plan per period. Always join to CS_PARTICIPANT, CS_COMP_PLAN, CS_PLAN_PERIOD for context.
• CS_COMP_SUMMARY: Rollup totals. Columns: ID (PK), PLAN_ID (FK), PERIOD_ID (FK), TOTAL_PARTICIPANTS, TOTAL_SALES, TOTAL_INCENTIVES, TOTAL_ADJUSTMENTS, NET_PAYOUT, STATUS. One row per plan per period. Used for reporting and audit.

Statement Module

• CS_STATEMENT: Compensation statements. Columns: ID (PK), PARTICIPANT_ID (FK), PERIOD_ID (FK), STATEMENT_DATE, GROSS_INCENTIVE, ADJUSTMENTS, TAXES_DEDUCTED, NET_PAYOUT, STATUS, CREATED_DATE. One row per participant per period. Final record for participant visibility and payroll export.

Building a Multi-Table Join

A typical analysis queries across multiple tables. Here's a real example: "Show me the calculated compensation for all active participants in plan 1001 for period 5001, with participant names, quotas, and attainment."

-- Get calculated results with full context
SELECT p.id, p.name, p.position_id,
       cp.name AS plan_name,
       pp.period_name,
       pp.start_date, pp.end_date,
       r.total_sales, r.quota_amount, r.attainment_pct,
       r.result_amount, r.incentive_amount,
       r.status
FROM   cs_results r
INNER JOIN cs_participant p ON r.participant_id = p.id
INNER JOIN cs_comp_plan cp ON r.plan_id = cp.id
INNER JOIN cs_plan_period pp ON r.period_id = pp.id
WHERE  cp.id = 1001
AND    pp.id = 5001
AND    p.status = 'Active'
AND    r.status = 'COMPLETE'
ORDER BY p.name;

This query starts from CS_RESULTS (the calculated data), then joins back to the dimension tables (participant, plan, period) to get human-readable context. This is the pattern you'll repeat: start from the fact table (CS_RESULTS, CS_CREDIT, CS_STATEMENT), then join to dimensions for context.

-- Get results with manager name and quota
SELECT p.id, p.name, m.name AS manager_name,
       pp.quota_amount, r.result_amount
FROM   cs_results r
INNER JOIN cs_participant p ON r.participant_id = p.id
LEFT JOIN cs_participant m ON p.parent_id = m.id
INNER JOIN cs_plan_participant pp ON r.participant_id = pp.participant_id
                                        AND r.plan_id = pp.plan_id
                                        AND r.period_id = pp.period_id
WHERE  r.plan_id = 1001
AND    r.period_id = 5001
ORDER BY m.name, p.name;

Notice the LEFT JOIN to CS_PARTICIPANT for the manager. Some participants might not have a manager (CEO), so LEFT JOIN handles NULLs gracefully. Also notice the three-field join to CS_PLAN_PARTICIPANT — the relationship is (participant_id, plan_id, period_id), not just participant_id.

-- Find all credits for a participant in a period with error details
SELECT c.id, c.amount, c.credit_date, c.source_ref,
       c.status, c.error_message,
       p.name, cp.name AS plan_name
FROM   cs_credit c
INNER JOIN cs_participant p ON c.participant_id = p.id
INNER JOIN cs_comp_plan cp ON c.plan_id = cp.id
WHERE  c.participant_id = 99999
AND    c.period_id = 5001
ORDER BY c.credit_date;

Common Undocumented Columns

The base CS_ tables have many columns that aren't explained in any manual. Here are ones you'll find in practice:

• *_FLAG columns: Boolean indicators stored as CHAR(1) with values 'Y' / 'N' or '1' / '0'. Examples: PRTT_FLAG (participant flag?), CALC_FLAG (calculated?). Query distinct values to understand.
• SYNC_STATUS / SYNC_DATE: Used for external system synchronization. If your Callidus syncs to SAP HCM or a payroll system, these columns track sync status.
• CUSTOM_* columns: Customer-specific fields for implementations with heavily customized schemas. You may find CUSTOM_FIELD_1, CUSTOM_FIELD_2, etc. Check with your admins for what these contain.
• AUDIT_* columns: Created by / last modified by / created date / modified date. Used for audit trails. Query these to find who changed what when.

When you encounter an unfamiliar column, query ALL_TAB_COLUMNS and ALL_TAB_COMMENTS to see if a comment was added by a previous admin:

-- Check if columns have comments (rare, but valuable if present)
SELECT column_name, comments
FROM   all_col_comments
WHERE  table_name = 'CS_RESULTS'
AND    comments IS NOT NULL;

Building Your Personal Data Dictionary

After reverse-engineering the schema, document it. Create a spreadsheet or wiki with:

• Table name: CS_RESULTS
• Purpose: Calculated compensation per participant per period
• Row count estimate: ~10M rows (depends on your org size and history)
• Key columns: ID (PK), PARTICIPANT_ID, PLAN_ID, PERIOD_ID (composite key), RESULT_AMOUNT, INCENTIVE_AMOUNT, STATUS
• Common joins: CS_PARTICIPANT (on PARTICIPANT_ID), CS_COMP_PLAN (on PLAN_ID), CS_PLAN_PERIOD (on PERIOD_ID), CS_CREDIT (on PARTICIPANT_ID, PLAN_ID, PERIOD_ID)
• Refresh cadence: Daily after pipeline runs
• Indexes: Expect indexes on PARTICIPANT_ID, PLAN_ID, PERIOD_ID, STATUS for query performance
• Gotchas: Multiple rows per participant if assigned to multiple plans

Repeat this for each key table. After 6 months, you'll have a comprehensive data dictionary better than anything SAP publishes.

Exploring with Sample Queries

When you encounter a new table, run a few quick queries to understand it:

-- Check table size and freshness
SELECT COUNT(*) AS row_count,
       MIN(created_date) AS oldest_record,
       MAX(created_date) AS newest_record
FROM   cs_results;

-- Check for NULLs in key columns (data quality check)
SELECT SUM(CASE WHEN participant_id IS NULL THEN 1 ELSE 0 END) AS missing_participant,
       SUM(CASE WHEN plan_id IS NULL THEN 1 ELSE 0 END) AS missing_plan,
       SUM(CASE WHEN result_amount IS NULL THEN 1 ELSE 0 END) AS missing_amount
FROM   cs_results;

-- Sample a few rows to see the actual data
SELECT * FROM cs_results WHERE ROWNUM <= 5;