Search Results statement_cycle_date_id

Overview

The table AR.AR_STATEMENT_CYCLE_DATES_ALL is a core data object in Oracle E-Business Suite (EBS) Release 12.1.1 and 12.2.2, residing in the Accounts Receivable (AR) schema. Its primary purpose is to store scheduled dates defined for each statement cycle. When a user sets up a statement cycle in the AR module, they designate specific dates on which statements should be automatically generated for all customers assigned to that cycle. For each such date entered, Receivables creates a single row in this table. The table thus acts as a repository of pre-defined trigger dates, enabling the system to batch-process statement generation efficiently. These dates also appear as selectable values in the Print Statements window, providing end users with a controlled list of valid generation dates. The AR_STATEMENT_CYCLES table holds the broader header-level information for each cycle, while the present table manages the granular date-level records. The primary key of the table is the STATEMENT_CYCLE_DATE_ID column.

Key Information Stored

The table contains several columns critical to the statement generation process:

• STATEMENT_CYCLE_DATE_ID (NUMBER, Mandatory): The unique system-generated identifier for each statement cycle date record. This is the primary key and is indexed by the unique index AR_STATEMENT_CYCLE_DATES_U1.
• STATEMENT_CYCLE_ID (NUMBER, Mandatory): References the parent statement cycle in the AR_STATEMENT_CYCLES table. This foreign key links each date to its specific cycle definition.
• STATEMENT_DATE (DATE): The actual calendar date scheduled for statement generation. This is the core operational date used by the system to determine when to run the statement printing process for the cycle.
• PRINTED (VARCHAR2): A flag that determines whether the statement date is available as a list of values choice in the Print Statements window. It is used to control user visibility of the date.
• PRINT_DATE (DATE): Captures the actual date on which the statement was printed for this cycle date. This column may be null until printing occurs.
• Who Columns: Standard Oracle EBS auditing columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — track row creation and modification history.
• Concurrent Program Who Columns: PROGRAM_UPDATE_DATE and REQUEST_ID record the date and concurrent request ID when a concurrent program last updated the row.

Common Use Cases and Queries

Typical usage of this table includes reporting, data validation, and troubleshooting of statement generation cycles. Below are common SQL patterns:

• Retrieve all scheduled dates for a specific cycle:
  SELECT statement_date, printed, print_date
FROM ar.ar_statement_cycle_dates_all
WHERE statement_cycle_id = :cycle_id
ORDER BY statement_date;
• Find cycles with unprinted statement dates:
  SELECT sc.statement_cycle_name, scd.statement_date, scd.printed
FROM ar.ar_statement_cycles sc, ar.ar_statement_cycle_dates_all scd
WHERE sc.statement_cycle_id = scd.statement_cycle_id
AND (scd.print_date IS NULL OR scd.printed = 'N');
• Validate that a scheduled date exists for a given date range:
  SELECT statement_cycle_id, statement_date
FROM ar.ar_statement_cycle_dates_all
WHERE statement_date BETWEEN '01-JAN-2024' AND '31-DEC-2024';
• Determine the last printed date for each cycle:
  SELECT statement_cycle_id, MAX(print_date) AS last_print_date
FROM ar.ar_statement_cycle_dates_all
GROUP BY statement_cycle_id;

Related Objects

Based on the documented foreign key and primary key relationships, the table interacts with the following objects:

• AR_STATEMENT_CYCLES: The parent table containing general information about each statement cycle (e.g., cycle name, type). The relationship is defined by the foreign key STATEMENT_CYCLE_ID referencing AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID.
• FND_USER: Referenced indirectly via the CREATED_BY and LAST_UPDATED_BY columns (foreign keys to FND_USER.USER_ID) for auditing purposes.
• FND_LOGINS: Referenced via the LAST_UPDATE_LOGIN column (foreign key to FND_LOGINS.LOGIN_ID) to track the operating system login of the last user update.
• Primary Key Index: The unique index AR_STATEMENT_CYCLE_DATES_U1 enforces uniqueness on the STATEMENT_CYCLE_DATE_ID column and is stored in the APPS_TS_TX_IDX tablespace.