CREATE OR REPLACE PACKAGE BODY SnapshotDataTypeService AS

type conditionType is record (
  fromParameters CMPTypeMapping_V.fromParameters%TYPE,
  condition1 CMPTypeMapping_V.condition1%TYPE,
  condition2 CMPTypeMapping_V.condition2%TYPE,
  condition3 CMPTypeMapping_V.condition3%TYPE
);
type numListType is table of NUMBER index by PLS_INTEGER;
type txtListType is table of VARCHAR2(4000) index by PLS_INTEGER;
type mappedConditionType is table of conditionType index by VARCHAR2(32);
type mappedListType is table of numListType index by VARCHAR2(32);

conditions conditionType;
dataTypeList numListType;
dataTypeFQNPairs txtListType;
dataTypeUOIDPairs txtListType;
dataTypeP1Nam_Pairs txtListType;
dataTypeP1Typ_Pairs txtListType;
dataTypeP1Def_Pairs txtListType;
dataTypeP1Min_Pairs txtListType;
dataTypeP1Max_Pairs txtListType;
dataTypeP2Nam_Pairs txtListType;
dataTypeP2Typ_Pairs txtListType;
dataTypeP2Def_Pairs txtListType;
dataTypeP2Min_Pairs txtListType;
dataTypeP2Max_Pairs txtListType;
dataTypeAllowLPairs numListType;
dataTypeAllowPPairs numListType;
dataTypeAllowSPairs numListType;
mappedConditions mappedConditionType;
mappedPlatformPairs mappedPairType;
mappedDataTypePairs mappedPairType;
mappedDataTypePlatforms numListType;
mappedConditionLists mappedListType;
operators txtListType;

genericPlatform CMPElement_V.elementId%TYPE;

sizeKeyword CONSTANT VARCHAR2(32) := 'size';
precisionKeyword CONSTANT VARCHAR2(32) := 'precision';
scaleKeyword CONSTANT VARCHAR2(32) := 'scale';
dot CONSTANT VARCHAR2(1) := '.';

FUNCTION getGenericPlatform RETURN NUMBER IS
  BEGIN
    if (genericPlatform is null) then
      select elementId into genericPlatform from CMPPlatform_V where name = 'GENERIC';
    end if;
    return genericPlatform;
  END getGenericPlatform;

PROCEDURE mapDataTypeToPlatform(dataType IN NUMBER, platform IN NUMBER) IS
  BEGIN
    if (NOT(mappedDataTypePlatforms.EXISTS(dataType))) then
      mappedDataTypePlatforms(dataType) := platform;
    end if;
  END mapDataTypeToPlatform;

PROCEDURE mapDataTypeToFQN(dataType IN NUMBER, dtName IN VARCHAR2,
                           tsName IN VARCHAR2, plName IN VARCHAR2) IS
  BEGIN
    if (NOT(dataTypeFQNPairs.EXISTS(dataType))) then
      dataTypeFQNPairs(dataType) := 'OMB//\oracle.wh.repos.impl.platform.CMPPlatform\'
        || plName || '\oracle.wh.repos.impl.code.CMPPlatformTypeSet\'
        || tsName || '\oracle.wh.repos.impl.code.CMPDataType\'
        || dtName;
    end if;
  END mapDataTypeToFQN;

PROCEDURE mapDataTypeToUOID(dataType IN NUMBER, uoid IN VARCHAR2) IS
  BEGIN
    if (NOT(dataTypeUOIDPairs.EXISTS(dataType))) then
      dataTypeUOIDPairs(dataType) := uoid;
    end if;
  END mapDataTypeToUOID;

PROCEDURE mapDataTypeToLPS(dataType IN NUMBER,
                           p1Nam    IN VARCHAR2, p1Typ IN VARCHAR2, p1Def IN VARCHAR2, p1Min IN VARCHAR2, p1Max IN VARCHAR2,
                           p2Nam    IN VARCHAR2, p2Typ IN VARCHAR2, p2Def IN VARCHAR2, p2Min IN VARCHAR2, p2Max IN VARCHAR2,
                           alwLength IN NUMBER, alwPrecision IN NUMBER, alwScale IN NUMBER) IS
  BEGIN
    if (NOT(dataTypeP1Nam_Pairs.EXISTS(dataType))) then
      dataTypeP1Nam_Pairs(dataType) := p1Nam;
    end if;
    if (NOT(dataTypeP1Typ_Pairs.EXISTS(dataType))) then
      dataTypeP1Typ_Pairs(dataType) := p1Typ;
    end if;
    if (NOT(dataTypeP1Def_Pairs.EXISTS(dataType))) then
      dataTypeP1Def_Pairs(dataType) := p1Def;
    end if;
    if (NOT(dataTypeP1Min_Pairs.EXISTS(dataType))) then
      dataTypeP1Min_Pairs(dataType) := p1Min;
    end if;
    if (NOT(dataTypeP1Max_Pairs.EXISTS(dataType))) then
      dataTypeP1Max_Pairs(dataType) := p1Max;
    end if;
    if (NOT(dataTypeP2Nam_Pairs.EXISTS(dataType))) then
      dataTypeP2Nam_Pairs(dataType) := p2Nam;
    end if;
    if (NOT(dataTypeP2Typ_Pairs.EXISTS(dataType))) then
      dataTypeP2Typ_Pairs(dataType) := p2Typ;
    end if;
    if (NOT(dataTypeP2Def_Pairs.EXISTS(dataType))) then
      dataTypeP2Def_Pairs(dataType) := p2Def;
    end if;
    if (NOT(dataTypeP2Min_Pairs.EXISTS(dataType))) then
      dataTypeP2Min_Pairs(dataType) := p2Min;
    end if;
    if (NOT(dataTypeP2Max_Pairs.EXISTS(dataType))) then
      dataTypeP2Max_Pairs(dataType) := p2Max;
    end if;
    if (NOT(dataTypeAllowLPairs.EXISTS(dataType))) then
      dataTypeAllowLPairs(dataType) := alwLength;
    end if;
    if (NOT(dataTypeAllowPPairs.EXISTS(dataType))) then
      dataTypeAllowPPairs(dataType) := alwPrecision;
    end if;
    if (NOT(dataTypeAllowSPairs.EXISTS(dataType))) then
      dataTypeAllowSPairs(dataType) := alwScale;
    end if;
  END mapDataTypeToLPS;

PROCEDURE loadMappings(fromPlatform IN NUMBER, toPlatform IN NUMBER) IS
  hashKey VARCHAR2(32);
  condKey VARCHAR2(32);
  i BINARY_INTEGER;
  BEGIN
    hashKey := ((to_char(fromPlatform) || dot) || to_char(toPlatform));

    if (mappedPlatformPairs.EXISTS(hashKey)) then
      return;
    else
      mappedPlatformPairs(hashKey) := 1;
      genericPlatform := getGenericPlatform();
      Snapshot.debug('SnapshotDataTypeService.loadMappings: for platform pair: ', hashKey);

      for c in (
        select tm.fromDataType, tm.toDataType,
               tm.condition1, tm.condition2, tm.condition3, tm.fromParameters,
               srcdt.firstClassObject as srcPlatform, tgtdt.firstClassObject as tgtPlatform,
               srcdt.UOID as srcDataTypeUOID, tgtdt.UOID as tgtDataTypeUOID,
               srcdt.name as srcDataTypeName, tgtdt.name as tgtDataTypeName,
               srcdt.p1 as srcDataTypeP1Nam, tgtdt.p1 as tgtDataTypeP1Nam,
               srcdt.p1type as srcDataTypeP1Typ, tgtdt.p1type as tgtDataTypeP1Typ,
               srcdt.p1default as srcDataTypeP1Def, tgtdt.p1default as tgtDataTypeP1Def,
               srcdt.p1min as srcDataTypeP1Min, tgtdt.p1min as tgtDataTypeP1Min,
               srcdt.p1max as srcDataTypeP1Max, tgtdt.p1max as tgtDataTypeP1Max,
               srcdt.p2 as srcDataTypeP2Nam, tgtdt.p2 as tgtDataTypeP2Nam,
               srcdt.p2type as srcDataTypeP2Typ, tgtdt.p2type as tgtDataTypeP2Typ,
               srcdt.p2default as srcDataTypeP2Def, tgtdt.p2default as tgtDataTypeP2Def,
               srcdt.p2min as srcDataTypeP2Min, tgtdt.p2min as tgtDataTypeP2Min,
               srcdt.p2max as srcDataTypeP2Max, tgtdt.p2max as tgtDataTypeP2Max,
               srcdt.lengthallowed as srcDataTypeAllowL, tgtdt.lengthallowed as tgtDataTypeAllowL,
               srcdt.precisionallowed as srcDataTypeAllowP, tgtdt.precisionallowed as tgtDataTypeAllowP,
               srcdt.scaleallowed as srcDataTypeAllowS, tgtdt.scaleallowed as tgtDataTypeAllowS,
               srcts.name as srcTypeSetName,  tgtts.name as tgtTypeSetName,
               srcpl.name as srcPlatformName, tgtpl.name as tgtPlatformName
        from   CMPTypeMapping_V tm,
               CMPDataType_V srcdt, CMPDataType_V tgtdt,
               CMPPlatformTypeSet_V srcts, CMPPlatformTypeSet_V tgtts,
               CMPPlatform_V srcpl, CMPPlatform_V tgtpl
        where  srcdt.elementId = tm.fromDataType
         and   tgtdt.elementId = tm.toDataType
         and ((srcdt.firstclassobject = fromPlatform and
               tgtdt.firstclassobject = genericPlatform)
          or  (srcdt.firstclassobject = genericPlatform and
               tgtdt.firstclassobject = toPlatform))
         and   srcts.elementid = srcdt.owningplatformtypeset
         and   tgtts.elementid = tgtdt.owningplatformtypeset
         and   srcpl.elementid = srcts.owningplatform
         and   tgtpl.elementid = tgtts.owningplatform
        order by tm.fromDataType, tm.toDataType
      )
      loop
        -- Optimization: record parent Platform of each DataType.
        mapDataTypeToPlatform(c.fromDataType, c.srcPlatform);
        mapDataTypeToPlatform(c.toDataType, c.tgtPlatform);

        -- Optimization: record CFA FullyQualifiedName of each DataType.
        mapDataTypeToFQN(c.fromDataType, c.srcDataTypeName,
                         c.srcTypeSetName, c.srcPlatformName);
        mapDataTypeToFQN(c.toDataType, c.tgtDataTypeName,
                         c.tgtTypeSetName, c.tgtPlatformName);

        -- Optimization: record UOID of each DataType.
        mapDataTypeToUOID(c.fromDataType, c.srcDataTypeUOID);
        mapDataTypeToUOID(c.toDataType, c.tgtDataTypeUOID);

        -- Optimization: record Length/Precision/Scale attributes of each DataType.
        mapDataTypeToLPS(c.fromDataType,
                         c.srcDataTypeP1Nam, c.srcDataTypeP1Typ, c.srcDataTypeP1Def, c.srcDataTypeP1Min, c.srcDataTypeP1Max, 
                         c.srcDataTypeP2Nam, c.srcDataTypeP2Typ, c.srcDataTypeP2Def, c.srcDataTypeP2Min, c.srcDataTypeP2Max, 
                         c.srcDataTypeAllowL,c.srcDataTypeAllowP,c.srcDataTypeAllowS);
        mapDataTypeToLPS(c.toDataType,
                         c.tgtDataTypeP1Nam, c.tgtDataTypeP1Typ, c.tgtDataTypeP1Def, c.tgtDataTypeP1Min, c.tgtDataTypeP1Max, 
                         c.tgtDataTypeP2Nam, c.tgtDataTypeP2Typ, c.tgtDataTypeP2Def, c.tgtDataTypeP2Min, c.tgtDataTypeP2Max, 
                         c.tgtDataTypeAllowL,c.tgtDataTypeAllowP,c.tgtDataTypeAllowS);

        hashKey := ((to_char(c.fromDataType) || dot) || to_char(c.tgtPlatform));
        if (c.condition1 is null) then
          mappedDataTypePairs(hashKey) := c.toDataType;
          if (Snapshot.isDebug()) then
            Snapshot.debug('  >>> ' || hashKey || ' -> ', c.toDataType);
          end if;
        else
          -- Save conditional info for complex type mappings (similar to DomainCache.java)..
          condKey := ((to_char(hashKey) || dot) || to_char(c.toDataType));
          conditions.fromParameters := c.fromParameters;
          conditions.condition1 := c.condition1;
          conditions.condition2 := c.condition2;
          conditions.condition3 := c.condition3;
          mappedConditions(condKey) := conditions;
          if (Snapshot.isDebug()) then
            Snapshot.debug('  >>> ' || condKey || ' -> ', c.toDataType);
            Snapshot.debug('  >>>   fromParameters  -> ', c.fromParameters);
            Snapshot.debug('  >>>   condition1      -> ', c.condition1);
            Snapshot.debug('  >>>   condition2      -> ', c.condition2);
            Snapshot.debug('  >>>   condition3      -> ', c.condition3);
          end if;
          if (mappedConditionLists.EXISTS(hashKey)) then
            dataTypeList := mappedConditionLists(hashKey);
            i := dataTypeList.COUNT;
            dataTypeList(i) := c.toDataType;
          else
            i := 0;
            dataTypeList.DELETE;
            dataTypeList(i) := c.toDataType;
          end if;
          mappedConditionLists(hashKey) := dataTypeList;
          if (Snapshot.isDebug()) then
            Snapshot.debug('  >>> ' || hashKey || ' toDataType added -> ', c.toDataType);
            Snapshot.debug('  >>> ' || hashKey || ' toDataType count -> ', dataTypeList.COUNT);
          end if;
        end if;
      end loop;

      -- Load the operators collection if necessary.
      if (NOT(operators.EXISTS(0))) then
        operators(0) := '<=';
        operators(1) := '>=';
        operators(2) := '<';
        operators(3) := '>';
        operators(4) := '=';
      end if;
    end if;

  END loadMappings;

FUNCTION getMappedDataTypeFQN(dataType IN NUMBER) RETURN VARCHAR2 IS
  BEGIN
    if (NOT(dataTypeFQNPairs.EXISTS(dataType))) then
      return null;
    end if;
    return dataTypeFQNPairs(dataType);
  END getMappedDataTypeFQN;

FUNCTION getMappedDataTypeUOID(dataType IN NUMBER) RETURN VARCHAR2 IS
  BEGIN
    if (NOT(dataTypeUOIDPairs.EXISTS(dataType))) then
      return null;
    end if;
    return dataTypeUOIDPairs(dataType);
  END getMappedDataTypeUOID;

FUNCTION resetLength(srcDataType IN NUMBER, tgtDataType IN NUMBER, curValue IN NUMBER) RETURN NUMBER IS
  newValue NUMBER;
  BEGIN
    if (Snapshot.isDebug()) then
      Snapshot.debug('SnapshotDataTypeService.resetLength of srcDataType = ', srcDataType);
      Snapshot.debug('SnapshotDataTypeService.resetLength to tgtDataType = ', tgtDataType);
      Snapshot.debug('SnapshotDataTypeService.resetLength to sourceP1Nam = ', dataTypeP1Nam_Pairs(srcDataType));
      Snapshot.debug('SnapshotDataTypeService.resetLength to targetP1Nam = ', dataTypeP1Nam_Pairs(tgtDataType));
      Snapshot.debug('SnapshotDataTypeService.resetLength to sourceP1Typ = ', dataTypeP1Typ_Pairs(srcDataType));
      Snapshot.debug('SnapshotDataTypeService.resetLength to targetP1Typ = ', dataTypeP1Typ_Pairs(tgtDataType));
      Snapshot.debug('SnapshotDataTypeService.resetLength of sourceValue = ', curValue);
    end if;
    newValue := curValue;
    if (NOT(dataTypeAllowLPairs.EXISTS(tgtDataType))) then
      Snapshot.debug('SnapshotDataTypeService.resetLength ', 'no map to target');
    elsif (dataTypeP1Nam_Pairs(tgtDataType) is null) then
      Snapshot.debug('SnapshotDataTypeService.resetLength ', 'no override rule in target');
    elsif ((dataTypeP1Nam_Pairs(tgtDataType) = dataTypeP1Nam_Pairs(srcDataType)) and
           (dataTypeP1Typ_Pairs(tgtDataType) = dataTypeP1Typ_Pairs(srcDataType))) then
      if (dataTypeP1Typ_Pairs(tgtDataType) != 'range')  then 
        Snapshot.debug('SnapshotDataTypeService.resetLength ', 'override rule in target not a range type');
      elsif (( curValue is null ) or 
             ( curValue = dataTypeP1Def_Pairs(srcDataType) and 
              (curValue < dataTypeP1Min_Pairs(tgtDataType) or
               curValue > dataTypeP1Max_Pairs(tgtDataType)))) then
        newValue := TO_NUMBER(dataTypeP1Def_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetLength ', 'to target default value');
      elsif (curValue < dataTypeP1Min_Pairs(tgtDataType)) then 
        newValue := TO_NUMBER(dataTypeP1Min_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetLength ', 'to target minimum value');
      elsif (curValue > dataTypeP1Max_Pairs(tgtDataType)) then 
        newValue := TO_NUMBER(dataTypeP1Max_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetLength ', 'to target maximum value');
      end if;
    -- dawsun, bug 8321692
    elsif ((dataTypeP1Nam_Pairs(srcDataType) is null) and 
           (dataTypeP1Nam_Pairs(tgtDataType) = 'size') and 
           (not(dataTypeP1Def_Pairs(tgtDataType) is null))) then
      newValue := TO_NUMBER(dataTypeP1Def_Pairs(tgtDataType));
      Snapshot.debug('SnapshotDataTypeService.resetLength ', 'to target default value');
    end if;
    Snapshot.debug('SnapshotDataTypeService.resetLength to targetValue = ', newValue);
    return newValue;
  END resetLength;

FUNCTION resetPrecision(srcDataType IN NUMBER, tgtDataType IN NUMBER, curValue IN NUMBER) RETURN NUMBER IS
  newValue NUMBER;
  BEGIN
    if (Snapshot.isDebug()) then
      Snapshot.debug('SnapshotDataTypeService.resetPrecision of srcDataType = ', srcDataType);
      Snapshot.debug('SnapshotDataTypeService.resetPrecision to tgtDataType = ', tgtDataType);
      Snapshot.debug('SnapshotDataTypeService.resetPrecision of sourceValue = ', curValue);
    end if;
    newValue := curValue;
    if (NOT(dataTypeAllowPPairs.EXISTS(tgtDataType))) then
      Snapshot.debug('SnapshotDataTypeService.resetPrecision ', 'no map to target');
    elsif (dataTypeP1Nam_Pairs(tgtDataType) is null) then
      Snapshot.debug('SnapshotDataTypeService.resetPrecision ', 'no override rule in target');
    elsif ((dataTypeP1Nam_Pairs(tgtDataType) = dataTypeP1Nam_Pairs(srcDataType)) and
           (dataTypeP1Typ_Pairs(tgtDataType) = dataTypeP1Typ_Pairs(srcDataType))) then
      if (dataTypeP1Typ_Pairs(tgtDataType) != 'range')  then 
        Snapshot.debug('SnapshotDataTypeService.resetPrecision ', 'override rule in target not a range type');
      elsif (( curValue is null ) or 
             ( curValue = dataTypeP1Def_Pairs(srcDataType) and 
              (curValue < dataTypeP1Min_Pairs(tgtDataType) or
               curValue > dataTypeP1Max_Pairs(tgtDataType)))) then
        newValue := TO_NUMBER(dataTypeP1Def_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetPrecision ', 'to target default value');
      elsif (curValue < dataTypeP1Min_Pairs(tgtDataType)) then 
        newValue := TO_NUMBER(dataTypeP1Min_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetPrecision ', 'to target minimum value');
      elsif (curValue > dataTypeP1Max_Pairs(tgtDataType)) then 
        newValue := TO_NUMBER(dataTypeP1Max_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetPrecision ', 'to target maximum value');
      end if;
    -- dawsun, bug 8321692
    elsif ((dataTypeP1Nam_Pairs(srcDataType) is null) and 
            ((dataTypeP1Nam_Pairs(tgtDataType) = 'precision') or 
             (dataTypeP1Nam_Pairs(tgtDataType) = 'year_precision') or 
             (dataTypeP1Nam_Pairs(tgtDataType) = 'day_precision') or 
             (dataTypeP1Nam_Pairs(tgtDataType) = 'fractional_seconds_precision')) 
             and (not(dataTypeP1Def_Pairs(tgtDataType) is null))) then
      newValue := TO_NUMBER(dataTypeP1Def_Pairs(tgtDataType));
      Snapshot.debug('SnapshotDataTypeService.resetPrecision ', 'to target default value');
    end if;
    Snapshot.debug('SnapshotDataTypeService.resetPrecision to targetValue = ', newValue);
    return newValue;
  END resetPrecision;

FUNCTION resetScale(srcDataType IN NUMBER, tgtDataType IN NUMBER, curValue IN NUMBER) RETURN NUMBER IS
  newValue NUMBER;
  BEGIN
    if (Snapshot.isDebug()) then
      Snapshot.debug('SnapshotDataTypeService.resetScale of srcDataType = ', srcDataType);
      Snapshot.debug('SnapshotDataTypeService.resetScale to tgtDataType = ', tgtDataType);
      Snapshot.debug('SnapshotDataTypeService.resetScale of sourceValue = ', curValue);
    end if;
    newValue := curValue;
    if (NOT(dataTypeAllowSPairs.EXISTS(tgtDataType))) then
      Snapshot.debug('SnapshotDataTypeService.resetScale ', 'no map to target');
    elsif (dataTypeP2Nam_Pairs(tgtDataType) is null) then
      Snapshot.debug('SnapshotDataTypeService.resetScale ', 'no override rule in target');
    elsif ((dataTypeP2Nam_Pairs(tgtDataType) = dataTypeP2Nam_Pairs(srcDataType)) and
           (dataTypeP2Typ_Pairs(tgtDataType) = dataTypeP2Typ_Pairs(srcDataType))) then
      if (dataTypeP2Typ_Pairs(tgtDataType) != 'range')  then 
        Snapshot.debug('SnapshotDataTypeService.resetScale ', 'override rule in target not a range type');
      elsif (( curValue is null ) or 
             ( curValue = dataTypeP2Def_Pairs(srcDataType) and 
              (curValue < dataTypeP2Min_Pairs(tgtDataType) or
               curValue > dataTypeP2Max_Pairs(tgtDataType)))) then
        newValue := TO_NUMBER(dataTypeP2Def_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetScale ', 'to target default value');
      elsif (curValue < dataTypeP2Min_Pairs(tgtDataType)) then 
        newValue := TO_NUMBER(dataTypeP2Min_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetScale ', 'to target minimum value');
      elsif (curValue > dataTypeP2Max_Pairs(tgtDataType)) then 
        newValue := TO_NUMBER(dataTypeP2Max_Pairs(tgtDataType));
        Snapshot.debug('SnapshotDataTypeService.resetScale ', 'to target maximum value');
      end if;
    -- dawsun, bug 8321692
    elsif ((dataTypeP2Nam_Pairs(srcDataType) is null) and 
           ((dataTypeP2Nam_Pairs(tgtDataType) = 'scale') or 
            (dataTypeP2Nam_Pairs(tgtDataType) = 'fractional_seconds_precision')) and 
           (not(dataTypeP2Def_Pairs(tgtDataType) is null))) then
      newValue := TO_NUMBER(dataTypeP2Def_Pairs(tgtDataType));
      Snapshot.debug('SnapshotDataTypeService.resetScale ', 'to target default value');
    end if;
    Snapshot.debug('SnapshotDataTypeService.resetScale to targetValue = ', newValue);
    return newValue;
  END resetScale;

PROCEDURE resetDataTypes(elemID IN NUMBER,
                         srcPlatformId IN NUMBER, tgtPlatformId IN NUMBER) IS
  BEGIN
    loadMappings(srcPlatformId, tgtPlatformId);
    SnapshotGenerated.resetDataTypes(elemID, tgtPlatformId);
  END resetDataTypes;

PROCEDURE resetDataTypes(uoidStr IN VARCHAR2, snap IN NUMBER,
                         srcPlatformId IN NUMBER, tgtPlatformId IN NUMBER) IS
  BEGIN
    loadMappings(srcPlatformId, tgtPlatformId);
    SnapshotGenerated.resetDataTypes(uoidStr, snap, tgtPlatformId);
  END resetDataTypes;

PROCEDURE testLoadAndLookup(fromPlatform IN NUMBER, toPlatform IN NUMBER) IS
  targetDataType NUMBER;
  propertyValues mappedPairType;
  BEGIN
    loadMappings(fromPlatform, toPlatform);

    for c in (
      select distinct tm.fromDataType
      from   CMPTypeMapping_V tm, CMPDataType_V dt
      where  dt.elementId = tm.fromDataType
       and   dt.firstClassObject = fromPlatform
    )
    loop
      targetDataType := getMappedDataType(c.fromDataType, toPlatform, propertyValues);
      dbms_output.put_line(fromPlatform||dot||toPlatform||dot||c.fromDataType||dot||targetDataType);
    end loop;

  END testLoadAndLookup;

/**
 * Tahoe: Datatype Update Service for Cut, Copy, Paste Service.
 * 1) Datatype lookup code should probably remain in this package.
 * 2) Datatype update code should probably be factored out and generated during a modelgen.
 */

FUNCTION eval(operator IN VARCHAR2,
              value IN NUMBER, boundary IN NUMBER) RETURN BOOLEAN IS
  BEGIN
    if (operator = '<=') then
      return (value <= boundary);
    elsif (operator = '>=') then
      return (value >= boundary);
    elsif (operator = '<') then
      return (value < boundary);
    elsif (operator = '>') then
      return (value > boundary);
    elsif (operator = '=') then
      return (value = boundary);
    end if;

    return FALSE;
  END eval;

FUNCTION isConditionTrue(condition IN VARCHAR2,
                         parameter IN VARCHAR2, value IN NUMBER) RETURN BOOLEAN IS
  cond VARCHAR2(32);
  expr VARCHAR2(32);
  boundary NUMBER;
  i BINARY_INTEGER;
  BEGIN
    if (condition is null) then
      return TRUE;
    end if;

    for i in 0..operators.LAST loop
      cond := substr(condition,2);
      expr := (parameter || operators(i));
      cond := replace(cond, ' ', '');
      expr := replace(expr, ' ', '');
      if (instr(cond, expr) != 0) then
        cond := replace(cond, expr, '');
        boundary := TO_NUMBER(cond);
        return eval(operators(i), value, boundary);
      end if;
    end loop;

    return FALSE;
  END isConditionTrue;

FUNCTION isConditionTrueForAnyParam(condition IN VARCHAR2,
                                    valuePairs IN mappedPairType) RETURN BOOLEAN IS
  state BOOLEAN := FALSE;
  parameter VARCHAR2(32);
  value NUMBER;
  BEGIN
    parameter := valuePairs.FIRST;
    while parameter is not null loop
      value := valuePairs(parameter);
      state := ((state) or (isConditionTrue(condition, parameter, value)));
      parameter := valuePairs.NEXT(parameter);
    end loop;

    return state;
  END isConditionTrueForAnyParam;

FUNCTION getConditionalMappedDataType(hashKey IN VARCHAR2,
                                      propertyValues IN mappedPairType) RETURN NUMBER IS
  condKey VARCHAR2(32);
  parameters VARCHAR2(32);
  defaultDataType NUMBER;
  valuePairs mappedPairType;
  i BINARY_INTEGER;
  BEGIN
    if (mappedConditionLists.EXISTS(hashKey)) then
      dataTypeList := mappedConditionLists(hashKey);
    else
      return null;
    end if;

    for i in 0..dataTypeList.LAST loop
      if (defaultDataType is null) then
        defaultDataType := dataTypeList(i);
      else
        -- Find the matching conditional type, once a default is selected.
        condKey := ((to_char(hashKey) || dot) || to_char(dataTypeList(i)));
        conditions := mappedConditions(condKey);
        valuePairs := propertyValues;
        if (valuePairs.COUNT = 0) then
            parameters := conditions.fromParameters;
            if (parameters is null) then
              return null; --Seeding Error!
            end if;
            if (instr(parameters, sizeKeyword) != 0) then
              valuePairs(sizeKeyword) := 1; --e.g. VARCHAR
            end if;
            if (instr(parameters, precisionKeyword) != 0) then
              valuePairs(precisionKeyword) := 1;  --e.g. NUMERIC
            end if;
            if (instr(parameters, scaleKeyword) != 0) then
              valuePairs(scaleKeyword) := 1;
            end if;
        end if;

        if (valuePairs.COUNT = 0) then
          return null; --Seeding Error!
        end if;

        if (isConditionTrueForAnyParam(conditions.condition1, valuePairs) and
            isConditionTrueForAnyParam(conditions.condition2, valuePairs) and
            isConditionTrueForAnyParam(conditions.condition3, valuePairs)) then
          defaultDataType := dataTypeList(i);
        end if;
      end if;
    end loop;

    return defaultDataType;
  END getConditionalMappedDataType;

FUNCTION getMappedDataType(sourceDataType IN NUMBER,
                           targetPlatform IN NUMBER,
                           propertyValues IN mappedPairType) RETURN NUMBER IS
  hashKey VARCHAR2(32);
  sourcePlatform NUMBER;
  genericDataType NUMBER;
  BEGIN
    if (NOT(mappedDataTypePlatforms.EXISTS(sourceDataType))) then
      return null;
    else
      sourcePlatform := mappedDataTypePlatforms(sourceDataType);
    end if;

    hashKey := ((to_char(sourceDataType) || dot) || to_char(targetPlatform));
    if (sourcePlatform = getGenericPlatform() or
        targetPlatform = getGenericPlatform()) then
      if (mappedDataTypePairs.EXISTS(hashKey)) then
        return mappedDataTypePairs(hashKey);
      else
        return getConditionalMappedDataType(hashKey, propertyValues);
      end if;
    end if;

    genericDataType := getMappedDataType(sourceDataType, getGenericPlatform(), propertyValues);
    return getMappedDataType(genericDataType, targetPlatform, propertyValues);
  END getMappedDataType;

END SnapshotDataTypeService;
/