Results for “draw_ptd”
12 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
CN_PAYMENT_WORKSHEETS_ALL is a core transactional table in the Oracle EBS Incentive Compensation (CN) module. It stores the calculated payment worksheet records that determine how much compensation each sales representative is owed for a given pay run. Each row represents the earnings, adjustments, recoveries, and net payment amounts for one salesperson within a single payment run, spanning commission, bonus, draw, and guarantee components. The table is the primary staging ground between compensation calculation and the subledger posting process, meaning it carries the amounts that ultimately flow into Oracle Payables or the general ledger.
From a Data Vault modeling perspective, the FK structure suggests this table behaves as a link entity, joining a pay run (CN_PAYRUNS_ALL) to a sales representative and to credit types, quotas, and conversion factors. This classification is a heuristic suggestion rather than a documented architectural decision. The table is owned by the CN schema, is VALID in both 12.1.1 and 12.2.2, and exposes 66 documented columns. It supports multi-organization and multi-currency deployments through ORG_ID and the credit conversion columns.
Key Information Stored
The surrogate primary key, CN_PAYMENT_WORKSHEETS_PK, is composed of PAYRUN_ID and SALESREP_ID, so the natural business key is effectively the combination of pay run and salesperson. The most operationally significant columns include:
- PAYMENT_WORKSHEET_ID — a system identifier for the worksheet record, frequently used in downstream references.
- PAYRUN_ID — links the worksheet to the parent pay run in CN_PAYRUNS_ALL.
- SALESREP_ID — identifies the salesperson receiving payment.
- ORG_ID — supports multi-organization partitioning and reporting.
- COMM_DUE_BB, COMM_PTD, COMM_PAID, COMM_DRAW, COMM_NREC — the commission components: brought-forward due, period-to-date, paid, draw, and non-recoverable amounts.
- BONUS_DUE_BB, BONUS_PTD, BONUS_PAID, BONUS_GIVEN — the parallel bonus calculation components.
- DRAW_PAID, DRAW_PTD, DRAW_RECOVERABLE_BEGIN, RECOVERY_METHOD — draw and recovery tracking.
- PMT_AMOUNT_CALC, PMT_AMOUNT_ADJ, PMT_AMOUNT_RECOVERY, HELD_AMOUNT — the calculated payment, adjustments, recovery amounts, and any held funds.
- CREDIT_TYPE_ID, CREDIT_CONV_FCT_ID, CONVERT_TO_TYPE_ID, CONVERT_TO_PAID — credit classification and conversion logic.
- QUOTA_ID — ties the worksheet to the salesperson's quota.
- WORKSHEET_STATUS / POSTING_STATUS — lifecycle state of the worksheet and its subledger posting.
- CURRENT_EARNINGS, CURRENT_EARNINGS_DUE — period earnings summaries.
Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER support concurrency and traceability. SECURITY_GROUP_ID governs row-level access.
Common Use Cases and Queries
Typical scenarios include payment reconciliation, commission variance analysis, draw balance tracking, and pre-posting validation. A payroll analyst might reconcile calculated versus paid amounts for a pay run:
- SELECT PAYRUN_ID, SALESREP_ID, PMT_AMOUNT_CALC, PMT_AMOUNT_ADJ, HELD_AMOUNT FROM CN_PAYMENT_WORKSHEETS_ALL WHERE PAYRUN_ID = :p_run_id;
- Identifying worksheets pending posting: SELECT * FROM CN_PAYMENT_WORKSHEETS_ALL WHERE POSTING_STATUS <> 'POSTED';
- Analyzing recoverable draws by salesperson: SELECT SALESREP_ID, DRAW_RECOVERABLE_BEGIN, DRAW_PAID, RECOVERY_METHOD FROM CN_PAYMENT_WORKSHEETS_ALL WHERE DRAW_RECOVERABLE_BEGIN > 0;
- Commission trend reporting across pay runs joined to CN_PAYRUNS_ALL for period context.
Because the table is joined frequently to pay runs and credit types, standard reporting patterns filter by ORG_ID and PAYRUN_ID to keep result sets manageable.
Related Objects
The most significant related objects, based on documented foreign keys, include:
- CN_PAYRUNS_ALL — parent pay run, joined on PAYRUN_ID.
- CN_CREDIT_TYPES_ALL_B — referenced via CONVERT_TO_TYPE_ID.
- CN_CREDIT_CONV_FCTS_ALL — referenced via CREDIT_CONV_FCT_ID.
- IGS_FI_CR_TYPES_ALL — credit type reference via CREDIT_TYPE_ID.
- CN_QUOTAS_ALL — quota reference via QUOTA_ID.
- FND_SECURITY_GROUPS — security group reference via SECURITY_GROUP_ID.
These relationships confirm the table's role as a central link between pay runs, salespeople, and compensation configuration, and they should be used for joins when building custom reports or extensions.
-
CN_PAYMENT_WORKSHEETS contains information about
-
CN_PAYMENT_WORKSHEETS contains information about
-
eTRM - CN Tables and Views 12.1.1
-
eTRM - CN Tables and Views 12.2.2