Country Dimension - Process M_S2V_COUNTRIES Specification

• Package: m_s2v_countries
  
• Description: Container for all procedures and functions relating to mapping data for the Countries Dimension from STA to VAL.
• Internal Function: validate_country_name
  
• Description: Function to return a validated country name or throw an exception if invalid.
  
• Parameters:
• p_country_name:
• Datatype: VARCHAR2
  
• Direction: IN
  
• Description: The unvalidated country name.
• RETURN:
• Datatype: VARCHAR2
  
• Description: The validated country name.
• Action:
• Replace all Special Control Entity Characters, and strip leading and trailing spaces from the unvalidated country name.
• If the validated country name is longer than 100 characters...
• Throw an exception.
  
• End; If the validated country name is longer than 100 characters.
• Return the validated country code.
  
• Internal Function: validate_iso2
  
• Description: Function to return a validated ISO2 code or throw an exception if invalid.
  
• Parameters:
• p_iso2:
• Datatype: VARCHAR2
  
• Direction: IN
  
• Description: The unvalidated ISO2 code.
• RETURN:
• Datatype: VARCHAR2
  
• Description: The validated ISO2 code.
• Action:
• Replace trailing asterix's, and strip leading and trailing spaces from the unvalidated ISO2 code.
• If the validated ISO2 code is longer than 2 characters...
• Throw an exception.
  
• End; If the validated ISO2 code is longer than 2 characters.
• Return the validated ISO2 code.
  
• Internal Function: validate_iso3
  
• Description: Function to return a validated ISO3 code or throw an exception if invalid.
  
• Parameters:
• p_iso3:
• Datatype: VARCHAR2
  
• Direction: IN
  
• Description: The unvalidated ISO3 code.
• RETURN:
• Datatype: VARCHAR2
  
• Description: The validated ISO3 code.
• Action:
• Replace trailing asterix's, and strip leading and trailing spaces from the unvalidated ISO3 code.
• If the validated ISO3 code is longer than 3 characters...
• Throw an exception.
  
• End; If the validated ISO3 code is longer than 3 characters.
• Return the validated ISO3 code.
  
• Internal Function: validate_un3
  
• Description: Function to return a validated UN3 code or throw an exception if invalid.
  
• Parameters:
• p_un3:
• Datatype: VARCHAR2
  
• Direction: IN
  
• Description: The unvalidated UN3 code.
• RETURN:
• Datatype: VARCHAR2
  
• Description: The validated UN3 code.
• Action:
• Sstrip leading and trailing spaces from the unvalidated UN3 code.
• If the validated UN3 code is longer than 3 characters...
• Throw an exception.
  
• End; If the validated UN3 code is longer than 3 characters.
• Return the validated UN3 code.
  
• Procedure: map (Overload 1)
  
• Description: Wrapper to do mapping with housekeeping.
  
• Action:
• Run initialise mapping with parameters p_mapping = 'M_S2V_COUNTRIES', run_no = local variable to capture the run number.
  
• Run map (overload 2) with parameter p_run_no = the captured run number.
• Run finalise mapping with parameter run_no = the captured run number.
  
• Procedure: map (Overload 2)
  
• Description: Map STA.S_COUNTRIES to VAL.V_COUNTRIES.
  
• Parameters:
• p_run_no:
• Datatype: NUMBER
  
• Direction: IN
  
• Description: The run number for this run.
  
• Action:
• Delete all data from V_COUNTRIES - discarding the previous run.
• For each row in S_COUNTRIES...
• Validate country name using validate_country_name with parameter; p_country_name = r_country.name.
• Validate ISO2 code using validate_iso2 with parameter; p_iso2 = r_country.iso2_code
• Validate ISO3 code using validate_iso3 with parameter; p_iso3 = r_country.iso3_code
• Validate UN3 number using validate_un3 with parameter; p_un3 = r_country.un3_number
• Create a new v_countries row with the validated data.
  
• End; For each row in S_COUNTRIES.