Sequences and Identity Columns
Many tables within the database require unique numbers to be generated for use as the Primary key within the table, or just as sequences for other purposes. In Oracle this is achieved by use of Oracle Sequences. In SQL Server, these fields are identity columns.
In the below table all min/max values are 1 and 999999999, and cycle by 1, except where stated otherwise
Oracle Sequence Name | min/max values | SQL Server Identity Column Name | Table | Used to... |
---|---|---|---|---|
activity_no | activity_no | sp100_activities | Used in packages | |
analysis_no | maxvalue 999999 | analysis_no | sp070_activity_standards | Used in |
assigned_ports_audit | sequence_no | sp415_assigned_ports_audit | Used in server code | |
audit_no | sequence | Used to generate unique identifiers for audit operations. Used in SPDB_AUDIT database package. | ||
audit_sequence_no | audit_seq_no | sp822_smaudit | ||
audit_transaction_id | transaction_id | sp599_transaction_audit | Used in TransactionDBImpl.cpp . | |
break_spec_id | break_spec_id | sp128_break_patterns | ||
bru_no | maxvalue 9999 | bru_no | sp051_brus | |
capacity_pattern_no | capacity_pattern_no | sp351_capacity_patterns | ||
component_no | component_no | sp400_components | ||
component_states_audit | sequence_no | sp403_component_states_audit | ||
dependency_group_no | dependency_group_no | sp156_job_dependency_groups | Used in JobDependencyGroupDBImpl.cpp . | |
dru_no | maxvalue 9999 | dru_no | sp053_drus | |
employee_no | employee_no | sp210_employees | ||
ganttaudit | sequence | sp500_gantt_audit |
| |
holiday_spec | maxvalue 9999 | holiday_spec | sp106_holiday_specifications |
|
job_no | job_no | sp111_jobs | ||
location_no | maxvalue 9999 | location_no | sp118_work_locations |
|
mapping_no | maxvalue 9999 | mapping_no | sp261_task_mapping |
|
message_no | message_no | sp207_dispatch_messages |
| |
monitor_audit | sequence_no | sp408_monitor_audit |
| |
optimizer_run_no | optimizer_run_no | sp530_optimizer_runs | Used in | |
region_no | region_no | sp057_regions |
| |
resource_no | resource_no | sp211_resources |
| |
resp_unit_no | maxvalue 9999 | resp_unit_no | sp058_responsibility_units | |
shift_spec | maxvalue 9999 | shift_spec | sp114_shift_specifications |
|
sp073_restrictions_seq | restriction_no | sp073_restriction_types | ||
sp150_category_id | category_id | sp150_std_act_categories | ||
sp263_journey_type | minvalue 256 | journey_type | sp263_journey_types | |
sp350_capacity_categories_seq | capacity_category_no | sp350_capacity_categories | ||
sp419_sequence-no | sequence_no | sp419_event_audit | ||
sp503_sequence_no | sequence_no | sp503_api_audit | ||
sp560_sequence_no | sequence_no | sp560_travel_status_history | ||
sp802_permission_id | permission_id | sp802_permission | Used in DB Load. | |
sp808_role_id | role_id | sp808_role_definition | ||
sp810_param_seq_no | permission_parameter | Used to generate unique identifiers for use by both | ||
specialperiod_no | period_id | sp620_special_periods | ||
system_state_audit | sequence_no | sp418_system_state_audit | ||
team_no | team_no | sp250_teams | Used to generate unique identifiers for teams (sp250 ). | |
|
this sequence is no longer valid and will be removed from future versions of this spec. |