Results for “cn_worksheet_bonuses_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The CN_WORKSHEET_BONUSES_ALL table resides in the CN schema and belongs to the Oracle Incentive Compensation (CN) product family. It stores bonus amounts associated with participants during the payment processing cycle. In practical terms, the table records which sales representatives received what kinds of bonuses and for what monetary amount within a given payrun, thereby linking compensation planning constructs (plans, quotas, plan assignments) to the actual disbursement worksheet generated by the payment process.

From a Data Vault modeling perspective, the relationships mined from the foreign key structure suggest this object is best treated as a link table. Its composite primary key (SALESREP_ID, PAYRUN_ID) and its role in joining sales representatives to payruns and plan assignments are characteristic of a link entity that resolves a many-to-many association between business hubs.

Key Information Stored

The table carries fifteen documented columns. The most significant are the following:

• SALESREP_ID — Identifies the sales representative receiving the bonus; part of the composite primary key CN_WORKSHEET_BONUSES_PK and a foreign key to CN_SALESREPS_API_ALL.
• PAYRUN_ID — The payrun under which the bonus is processed; the second component of the composite primary key and a foreign key to CN_PAYRUNS_ALL.
• BONUS_TYPE — Classifies the nature of the bonus awarded during the payment process.
• AMOUNT — The monetary value of the bonus credited to the participant.
• SRP_PLAN_ASSIGN_ID — References the sales representative plan assignment governing eligibility.
• COMP_PLAN_ID — References the compensation plan associated with the bonus.
• QUOTA_ID — References the quota against which performance, and therefore the bonus, was evaluated.
• PAYMENT_WORKSHEET_ID — Ties the bonus row to the generated payment worksheet.
• ORG_ID — The operating unit context for multi-org data segregation.
• SECURITY_GROUP_ID — References FND_SECURITY_GROUPS and supports row-level security partitioning.

The composite primary key CN_WORKSHEET_BONUSES_PK combines SALESREP_ID and PAYRUN_ID, making these the definitive business-key candidates for unique row identification. Standard audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) are also maintained.

Common Use Cases and Queries

Typical uses include reconciling bonus disbursements against payruns, auditing bonus amounts per representative, and feeding downstream payment or reporting processes.

Example: total bonuses per payrun.

SELECT PAYRUN_ID, SUM(AMOUNT) TOTAL_BONUS
FROM   CN.CN_WORKSHEET_BONUSES_ALL
GROUP BY PAYRUN_ID;

Example: bonus details joined to payruns for a given representative.

SELECT b.SALESREP_ID, b.BONUS_TYPE, b.AMOUNT, p.PAYRUN_ID
FROM   CN.CN_WORKSHEET_BONUSES_ALL b,
       CN.CN_PAYRUNS_ALL p
WHERE  b.PAYRUN_ID = p.PAYRUN_ID
AND    b.SALESREP_ID = :salesrep_id;

Reporting scenarios include payment worksheet validation, bonus-type trend analysis, and quota-versus-bonus performance reviews.

Related Objects

• CN_PAYRUNS_ALL — joined via PAYRUN_ID; source of payrun processing context.
• CN_SALESREPS_API_ALL — joined via SALESREP_ID; participant master.
• CN_SRP_PLAN_ASSIGNS_ALL — joined via SRP_PLAN_ASSIGN_ID; plan assignment detail.
• CN_COMP_PLANS_ALL — joined via COMP_PLAN_ID; compensation plan definition.
• CN_QUOTAS_ALL — joined via QUOTA_ID; quota definition.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; security partitioning.