home *** CD-ROM | disk | FTP | other *** search
/ Programmer Plus 2007 / Programmer-Plus-2007.iso / Programming / SQL and Data Base / Logic Works ERwin 3.5 / data.1 / erwatvin.six < prev    next >
Encoding:
Text File  |  1998-01-28  |  4.7 KB  |  143 lines
  1. /* ------------------------------------------------------------------------
  2. --    ERWATVIN.SIX                                                          --
  3. --    Copyright (C) Logic Works, Inc. 1994                                 --
  4. --                                                                       --
  5. --  Stored function to insert ERwin attribute valid values                 --
  6. --  into the Designer 2000 dictionary as attribute values                 --
  7. ------------------------------------------------------------------------- */
  8. CREATE OR REPLACE FUNCTION ERWATVIN(pEntity CI_ENTITIES.NAME%TYPE,
  9.                             pAttribute    CI_ATTRIBUTES.NAME%TYPE,    
  10.                             pLowVal     CI_ATTRIBUTE_VALUES.LOW_VALUE%TYPE,
  11.                             pHiVal     CI_ATTRIBUTE_VALUES.HIGH_VALUE%TYPE,
  12.                             pMeaning CI_ATTRIBUTE_VALUES.MEANING%TYPE,
  13.                             pApp     CI_APPLICATION_SYSTEMS.NAME%TYPE,
  14.                             pVersion CI_APPLICATION_SYSTEMS.VERSION%TYPE)
  15.         RETURN CI_ATTRIBUTES.ID%TYPE
  16.         AS
  17.         TYPE recAttValue IS RECORD
  18.             (ATTRIBUTE_REFERENCE CI_ATTRIBUTE_VALUES.ATTRIBUTE_REFERENCE%TYPE,
  19.              ID                  CI_ATTRIBUTE_VALUES.ID%TYPE,
  20.              MEANING          CI_ATTRIBUTE_VALUES.MEANING%TYPE);
  21.         TYPE recEntity IS RECORD
  22.             (APPLICATION_SYSTEM_OWNED_BY   CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  23.              ID CI_ENTITIES.ID%TYPE,
  24.              NAME CI_ENTITIES.NAME%TYPE);
  25.         TYPE recAttribute IS RECORD
  26.             (ENTITY_REFERENCE CI_ATTRIBUTES.ENTITY_REFERENCE%TYPE,
  27.              ID                  CI_ATTRIBUTES.ID%TYPE,
  28.              NAME              CI_ATTRIBUTES.NAME%TYPE);
  29.         prAtt_Value        recAttValue;
  30.         stAtt_Value        cioattribute_value.data;
  31.         prAttribute        recAttribute;
  32.         prEntity        recEntity;
  33.         EntityRef        CI_ENTITIES.ID%TYPE;
  34.           act_status        varchar2(100);
  35.         act_warnings    varchar2(100);
  36. BEGIN
  37.         BEGIN
  38.             IF pVersion = 0 THEN
  39.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  40.                     FROM CI_ENTITIES
  41.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntity) 
  42.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY =
  43.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  44.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  45.                     CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  46.             ELSE
  47.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  48.                     FROM CI_ENTITIES
  49.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntity) 
  50.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY =
  51.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  52.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  53.                     CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  54.             END IF;    
  55.             EntityRef := prEntity.ID;
  56.         EXCEPTION
  57.             WHEN OTHERS THEN
  58.                 RETURN -1;                        
  59.         END;
  60.         BEGIN
  61.             SELECT ENTITY_REFERENCE, ID, NAME INTO prAttribute
  62.                 FROM CI_ATTRIBUTES
  63.                 WHERE    CI_ATTRIBUTES.NAME=UPPER(pAttribute) 
  64.                 AND CI_ATTRIBUTES.ENTITY_REFERENCE = EntityRef;
  65.         EXCEPTION
  66.             WHEN OTHERS THEN
  67.                 RETURN -1;
  68.         END;
  69.             
  70.         stAtt_Value.v.ATTRIBUTE_REFERENCE := prAttribute.ID;
  71.         stAtt_Value.i.ATTRIBUTE_REFERENCE := true;
  72.         stAtt_Value.v.MEANING := pMeaning;
  73.         stAtt_Value.i.MEANING := true;
  74.         stAtt_Value.v.LOW_VALUE := pLowVal;
  75.         stAtt_Value.i.LOW_VALUE := true;
  76.         stAtt_Value.v.HIGH_VALUE := pHiVal;
  77.         stAtt_Value.i.HIGH_VALUE := true;
  78.         cdapi.open_activity;
  79.         SELECT ATTRIBUTE_REFERENCE, ID, MEANING INTO prAtt_Value
  80.                 FROM CI_ATTRIBUTE_VALUES
  81.                 WHERE    CI_ATTRIBUTE_VALUES.MEANING=pMeaning 
  82.                 AND CI_ATTRIBUTE_VALUES.ATTRIBUTE_REFERENCE = prAttribute.ID;
  83.         dbms_output.put_line('Attribute value already exists');
  84.         stAtt_Value.v.ID := prAtt_Value.ID;
  85.         stAtt_Value.i.ID := true;
  86.         BEGIN
  87.             cioattribute_value.upd(prAtt_Value.ID,stAtt_Value);
  88.             dbms_output.put_line('Attribute value updated');
  89.             cdapi.validate_activity(act_status, act_warnings);
  90.             cdapi.close_activity(act_status);
  91.             if act_status != 'Y' then
  92.                 cdapi.abort_activity;
  93.                 RETURN -2;
  94.             else
  95.                 RETURN stAtt_Value.v.ID;
  96.             end if;
  97.         EXCEPTION
  98.                 WHEN OTHERS THEN
  99.                     if cdapi.stacksize > 0 then 
  100.                         if cdapi.activity is not null then 
  101.                             cdapi.abort_activity;
  102.                         end if;
  103.                     else
  104.                         if cdapi.activity is not null then 
  105.                             cdapi.abort_activity;
  106.                         end if;
  107.                     end if;
  108.                     RETURN -3;
  109.         END;
  110. EXCEPTION
  111.             WHEN NO_DATA_FOUND THEN
  112.             BEGIN
  113.                 cioattribute_value.ins(null,stAtt_Value);
  114.                 cdapi.validate_activity(act_status, act_warnings);
  115.                 cdapi.close_activity(act_status);
  116.                 if act_status != 'Y' then
  117.                     cdapi.abort_activity;
  118.                     RETURN -4;
  119.                 else
  120.                     RETURN stAtt_Value.v.ID;
  121.                 end if;
  122.             EXCEPTION
  123.                 WHEN OTHERS THEN
  124.                     if cdapi.stacksize > 0 then 
  125.                         if cdapi.activity is not null then 
  126.                             cdapi.abort_activity;
  127.                         end if;
  128.                     else
  129.                         if cdapi.activity is not null then 
  130.                             cdapi.abort_activity;
  131.                         end if;
  132.                     end if;
  133.                     RETURN -5;
  134.             END;
  135.             WHEN OTHERS THEN
  136.                 dbms_output.put_line('Unknown exception encountered');
  137.                 if cdapi.activity is not null then 
  138.                     cdapi.abort_activity;
  139.                 end if;
  140.                 RETURN -6;
  141. END ERWATVIN;
  142. /
  143.