ARM_RECONCILIATIONS
This table stores the reconciliation and reconciliation instances of the system. The reconciliation and reconciliation instances share the same columns except: START_DATE (instance) vs START_DATE_OFFSET (reconciliation), END_DATE (instance) vs END_DATE_OFFSET (reconciliation) SCHEDULE_ID (instance), null for reconciliations.
Details
Object type: TABLE
Primary Key
| Name | Columns |
|---|---|
|
ARM_RECONCILIATIONS_PK |
RECONCILIATION_ID |
Columns
| Name | Data Type | Length | Precision | Scale | Null Allowed | Comments |
|---|---|---|---|---|---|---|
|
RECONCILIATION_ID |
NUMBER |
18 |
0 |
No |
The Reconciliation unique ID |
|
|
RECONCILIATION_ACCOUNT_ID |
VARCHAR2 |
4000 |
No |
The concatenated account segment values. |
||
|
RECONCILIATION_NAME |
VARCHAR2 |
765 |
No |
The reconciliation name |
||
|
RECONCILIATION_DESCRIPTION |
VARCHAR2 |
4000 |
Yes |
The reconciliation description |
||
|
PERIOD_ID |
NUMBER |
18 |
0 |
Yes |
The period ID of the reconciliation instance. -2 for profiles. |
|
|
STATUS_ID |
NUMBER |
2 |
0 |
Yes |
The status of the reconciliation: Null = Profiles, 32 = Pending, 6 = Open with Preparer, 10 = Open with Reviewer, 1 = Closed. |
|
|
RESPONSIBILITY_LEVEL |
NUMBER |
9 |
0 |
Yes |
The current responsibility level of the reconciliation instance. NULL for reconciliations. |
|
|
PROCESS_ID |
NUMBER |
18 |
0 |
Yes |
The process associated with the reconciliation. FK to ARM_PROCESSES. The process determine how the Completeness Check is verified |
|
|
FORMAT_ID |
NUMBER |
18 |
0 |
Yes |
The Format associated with the reconciliation. FK to ARM_FORMATS. This will determine the Reconciliation Method as well as the layout of the reconciliation as shown to the reconciler (preparer, approver, viewer, commentator). |
|
|
RECONCILIATION_ACTIONS_ID |
NUMBER |
18 |
0 |
Yes |
The ID used for reconciliation actions/summary as an alternate ID to differentiate profile objects from action objects. Used to differentiate balance summary attributes from profile attributes, and attachments from references |
|
|
ACCOUNT_TYPE_ID |
NUMBER |
18 |
0 |
Yes |
The account type ID, FK to ARM_ACCOUNT_TYPES |
|
|
NORMAL_BALANCE |
VARCHAR2 |
1 |
Yes |
D = Debit, C = Credit, U = Unassigned, E = Either Debit or Credit |
||
|
HISTORICAL_RATE |
VARCHAR2 |
1 |
Yes |
This flag determines is the object contains historical accounts and so no currency conversion is done using Fx rates |
||
|
RATE_TYPE_ID |
NUMBER |
18 |
0 |
Yes |
Rate Type ID, FK to ARM_RATE_TYPES table |
|
|
AUTOREC_THRESHOLD_PERCENT |
NUMBER |
3 |
0 |
Yes |
Percent threshold value for Auto reconciliation method Balance match (% tolerance). Value should be between 0 and 100 |
|
|
AUTO_RECONCILED |
VARCHAR2 |
1 |
Yes |
Whether the reconciliation was auto-reconciled |
||
|
MAX_AGE_ADJUSTMENTS |
NUMBER |
9 |
0 |
Yes |
This is the maximum age that an Adjustment Transaction should be for this Reconciliation Account. |
|
|
MAX_AGE_EXPLANATIONS |
NUMBER |
9 |
0 |
Yes |
This is the maximum age that a Balance Explanation Transaction should be for this Reconciliation Account. |
|
|
AGING_VIOLATION |
VARCHAR2 |
1 |
Yes |
Whether or not there is an aging violation in the reconciliation. |
||
|
EVER_BEEN_LATE |
VARCHAR2 |
1 |
Yes |
Whether the reconciliation has ever been late with any person. |
||
|
ENTER_SYSTEM_BALANCES |
VARCHAR2 |
1 |
Yes |
Flag to indicate if source system balances are manually entered by preparer or not. Values: ''Y'' or ''N''. |
||
|
ENTER_SUBSYSTEM_BALANCES |
VARCHAR2 |
1 |
Yes |
Flag to indicate if subsystem balances are manually entered by preparer or not. Values: ''Y'' or ''N''. |
||
|
NORMAL_BALANCE_VIOLATION |
VARCHAR2 |
1 |
Yes |
Whether or not there is a violation in the reconciliation's normal balance. |
||
|
EXP_BALANCE_AGING_VIOLATION |
VARCHAR2 |
1 |
Yes |
Whether or not there is a violation in the reconciliation's expected balance. |
||
|
SOURCE_SYSTEM_AGING_VIOLATION |
VARCHAR2 |
1 |
Yes |
Whether or not there is a violation in the reconciliation's source system. |
||
|
SUBSYSTEM_AGING_VIOLATION |
VARCHAR2 |
1 |
Yes |
Whether or not there is a violation in the reconciliation's subsystem balance. |
||
|
REJECTS |
NUMBER |
9 |
0 |
Yes |
The number of rejects performed |
|
|
EMAIL_SENT |
VARCHAR2 |
3 |
Yes |
''Y'' for sent and ''N'' for email not sent. Needed for orchestrating BPEL flow. |
||
|
EMAIL_SENT_DATE |
DATE |
Yes |
The date when email was sent to the user. Used for constructing email notification content. |
|||
|
START_DATE |
DATE |
Yes |
Start Date of the reconciliation instance. Same as the Start Date for the preparer. |
|||
|
END_DATE |
DATE |
Yes |
End Date of the reconciliation instance. Same date as the End Date for the last reviewer. |
|||
|
ACTUAL_END_DATE |
DATE |
Yes |
The date that the reconciliation was completed |
|||
|
START_OFFSET |
NUMBER |
9 |
0 |
Yes |
Start offset of the profile. Same as the start offset for the preparer. |
|
|
SCHEDULE_FROM |
VARCHAR2 |
1 |
Yes |
Indicates how reconciliation start date is calculated by period dates, C - Close Date and E - End Date. |
||
|
END_OFFSET |
NUMBER |
9 |
0 |
Yes |
End offset of the profile. Same as the end offset for the last reviewer. |
|
|
OBJECT_VERSION_NUMBER |
NUMBER |
9 |
0 |
No |
Used to implement optimistic locking. This number is incremented every time the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. |
|
|
LAST_UPDATE_LOGIN |
NUMBER |
Yes |
Who column. Stores the login/session ID of the user who last updated this row. |
|||
|
LAST_UPDATED_BY |
VARCHAR2 |
255 |
No |
Who column. Stores the ID of the user who last updated this row. |
||
|
LAST_UPDATE_DATE |
DATE |
No |
Who column. Stores the date when this row was last updated. |
|||
|
CREATED_BY |
VARCHAR2 |
255 |
No |
Who column. Stores the ID of the user who created this row. |
||
|
CREATION_DATE |
DATE |
No |
Who column. Stores the date when this row was created in the database. |
|||
|
SUMMARY_RECONCILIATION |
VARCHAR2 |
1 |
Yes |
Whether the profile/reconciliation is a summary profile or not. |
||
|
SUMMARY_REC_TYPE |
VARCHAR2 |
1 |
Yes |
Whether the child accounts are filter or list type. Possible values are F - Filter, L - List. |
||
|
SUMMARY_REC_FILTER_ID |
NUMBER |
18 |
0 |
Yes |
The filter ID stores the filter criteria for child accounts, used when summary reconciliation type is Filter. |
|
|
PREPARER_UPDATE_DATE |
DATE |
Yes |
Last time the preparer updated the reconciliation |
|||
|
AUTO_RECONCILIATION_METHOD |
VARCHAR2 |
50 |
Yes |
Auto Reconciliation method (code) name |
||
|
AUTOREC_BALANCE_LOW |
NUMBER |
29 |
12 |
Yes |
Lower limit for balance for Auto reconciliation methods with balance range |
|
|
AUTOREC_BALANCE_HIGH |
NUMBER |
29 |
12 |
Yes |
Upper limit for balance for Auto reconciliation methods with balance range |
|
|
AUTOREC_THRESHOLD_NUMBER |
NUMBER |
29 |
12 |
Yes |
Number threshold value for Auto reconciliation method Balance match (# tolerance) |
|
|
ORG_ID |
NUMBER |
18 |
0 |
Yes |
The organization ID, FK to FCM_ORG_UNITS |
|
|
CALENDAR_ID |
NUMBER |
18 |
0 |
Yes |
Calendar used when during copy to period. Always null for profile, but not null for recon |
|
|
ACTIVE |
VARCHAR2 |
1 |
No |
Whether the profile is marked as active |
||
|
VARIANCE_PERIOD_ID |
NUMBER |
18 |
0 |
Yes |
The variance period ID |
Indexes
| Index | Uniqueness | Columns |
|---|---|---|
|
ARM_RECONCILIATIONS_N1 |
NONUNIQUE |
PERIOD_ID, RECONCILIATION_ACCOUNT_ID |
|
ARM_RECONCILIATIONS_N2 |
NONUNIQUE |
PERIOD_ID |
|
ARM_RECONCILIATIONS_N3 |
NONUNIQUE |
RECONCILIATION_ACCOUNT_ID |
|
ARM_RECONCILIATIONS_N4 |
NONUNIQUE |
SUMMARY_RECONCILIATION, RECONCILIATION_ID |
|
ARM_RECONCILIATIONS_N5 |
NONUNIQUE |
PERIOD_ID, STATUS_ID |
|
ARM_RECONCILIATIONS_PK |
UNIQUE |
RECONCILIATION_ID |
|
ARM_RECONCILIATIONS_U1 |
UNIQUE |
PERIOD_ID, RECONCILIATION_ID |
|
ARM_RECONCILIATIONS_U2 |
UNIQUE |
RECONCILIATION_ACTIONS_ID |
|
ARM_RECONCILIATIONS_N1U |
NONUNIQUE |
PERIOD_ID, SYS_NC00056$ |