Control Repository - Technical Specification

A number of objects will be built to support this sub-system:

Sequences:

• Name: RUN_NO_S
• Description: Generates Run Numbers.
  

Tables:

• Name: REPOSITORIES
• Description: Contains repository references.
• Columns:
• Name: NAME
• Description: Name of repository.
• Datatype: VARCHAR2(50)
• Null: No
• Unique: Yes
• Part of PK?: Yes
• Seed Data
• 'CONTROL'
• 'EXTERNAL FILE'
• 'EXTERNAL TABLE'
• 'EXTERNAL_HTML'
• 'ODS'
• 'STAGING'
• 'VALIDATION'
• 'WAREHOUSE'
  

• Name: MAPPING_LIBRARIES
• Description: A library of all available mappings.
• Columns:
• Name: MAPPING_NAME
• Description: Name of the mapping.
• Datatype: VARCHAR2(30)
• Null: No
• Unique: Yes
• Part of PK?: Yes
• Name: REP_FROM
• Description: The FROM repository for data movement. Foreign key from REPOSITORIES.NAME.
  
• Datatype: VARCHAR2(50)
  
• Null: No
• Unique: No
• Part of PK?: No
  
• Name: REP_TO
• Description: The TO repository for data movement. Foreign key from REPOSITORIES.NAME.
• Datatype: VARCHAR2(50)
• Null: No
• Unique: No
• Part of PK?: No

• Name: MAPPING_HISTORIES
• Description: A history of mapping runs.
• Columns:
• Name: RUN_NO
• Description: Primary key. Generated from RUN_NO_S.
• Datatype: NUMBER
• Null: No
• Unique: Yes
• Part of PK?: Yes
  
• Name: MAPPING_LIBRARY
• Description: Foreign key from MAPPING_LIBRARIES.MAPPING_NAME.
• Datatype: VARCHAR2(30)
  
• Null: No
• Unique: No
• Part of PK?: No
  
• Name: START_TSP
• Description: Timestamp of mapping run start.
• Datatype: DATE
• Null: No
• Unique: No
• Part of PK?: No
• Name: END_TSP
• Description: Timestamp of mapping run end.
• Datatype: DATE
• Null: Yes
• Unique: No
• Part of PK?: No

Processes:

• Package: CTL_GEN
  
• Description: A package for general control procedures.
• Procedure: initialise_mapping:
• Description: Generate a run number for the run. Create a mapping_histories row to start the run.
• Parameters:
• p_mapping
• Type: VARCHAR2
  
• Direction: IN
• Description: Mapping Name.
• p_run_no
• Type: NUMBER
  
• Direction: OUT
• Description: Number assigned to mapping run.
• Action:
• Generate a run number for the run.
• Create a mapping_histories row to start the run.
  

• Procedure: finalise_mapping:
• Description: Close the mapping_histories row by adding an end timestamp.
• Parameters:
• p_run_no
• Type: NUMBER
• Direction: IN
• Description: Number assigned to mapping run.
• Action:
• Close the mapping_histories row by adding an end timestamp.