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.fiv < prev    next >
Encoding:
Text File  |  1998-01-28  |  4.3 KB  |  129 lines
  1. /* ------------------------------------------------------------------------
  2. --    ERWATVIN.FIV                                                          --
  3. --    Copyright (C) Logic Works, Inc. 1994                                 --
  4. --                                                                       --
  5. --  Stored function to insert ERwin domain valid values                     --
  6. --  into the Oracle CASE 5.1 dictionary as attribute values                 --
  7. ------------------------------------------------------------------------- */
  8. CREATE OR REPLACE FUNCTION ERWATVIN(pDomain CI_DOMAINS.NAME%TYPE,    
  9.                             pLowVal     CI_ATTRIBUTE_VALUES.LOW_VALUE%TYPE,
  10.                             pHiVal     CI_ATTRIBUTE_VALUES.HIGH_VALUE%TYPE,
  11.                             pMeaning CI_ATTRIBUTE_VALUES.MEANING%TYPE,
  12.                             pApp     CI_APPLICATION_SYSTEMS.NAME%TYPE,
  13.                             pVersion CI_APPLICATION_SYSTEMS.VERSION%TYPE)
  14.         RETURN CI_ATTRIBUTES.ID%TYPE
  15.         AS
  16.         TYPE recAttValue IS RECORD
  17.             (DOMAIN_REFERENCE CI_ATTRIBUTE_VALUES.DOMAIN_REFERENCE%TYPE,
  18.              ID                  CI_ATTRIBUTE_VALUES.ID%TYPE,
  19.              MEANING          CI_ATTRIBUTE_VALUES.MEANING%TYPE);
  20.         TYPE recDomain IS RECORD
  21.             (APPLICATION_SYSTEM_OWNED_BY    CI_DOMAINS.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  22.              DATATYPE        CI_DOMAINS.DATATYPE%TYPE,
  23.              FORMAT            CI_DOMAINS.FORMAT%TYPE,
  24.              ID                CI_DOMAINS.ID%TYPE,
  25.              NAME            CI_DOMAINS.NAME%TYPE);
  26.         prAtt_Value        recAttValue;
  27.         stAtt_Value        cioattribute_value.data;
  28.         prDomain        recDomain;
  29.         DomainRef        CI_DOMAINS.ID%TYPE;
  30.           act_status        varchar2(100);
  31.         act_warnings    varchar2(100);
  32. BEGIN
  33.         BEGIN
  34.             IF pVersion = 0 THEN 
  35.                 SELECT APPLICATION_SYSTEM_OWNED_BY,DATATYPE,FORMAT,ID,NAME INTO prDomain
  36.                     FROM CI_DOMAINS
  37.                     WHERE    CI_DOMAINS.NAME = UPPER(pDomain) 
  38.                     AND CI_DOMAINS.APPLICATION_SYSTEM_OWNED_BY = 
  39.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  40.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  41.                     CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  42.             ELSE
  43.                 SELECT APPLICATION_SYSTEM_OWNED_BY,DATATYPE,FORMAT,ID,NAME INTO prDomain
  44.                     FROM CI_DOMAINS
  45.                     WHERE    CI_DOMAINS.NAME = UPPER(pDomain) 
  46.                     AND CI_DOMAINS.APPLICATION_SYSTEM_OWNED_BY = 
  47.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  48.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  49.                     CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  50.             END IF;
  51.             DomainRef := prDomain.ID;
  52.         EXCEPTION
  53.             WHEN OTHERS THEN
  54.                 RETURN -1;                        
  55.         END;            
  56.         stAtt_Value.v.DOMAIN_REFERENCE := DomainRef;
  57.         stAtt_Value.i.DOMAIN_REFERENCE := true;
  58.         stAtt_Value.v.MEANING := pMeaning;
  59.         stAtt_Value.i.MEANING := true;
  60.         stAtt_Value.v.LOW_VALUE := pLowVal;
  61.         stAtt_Value.i.LOW_VALUE := true;
  62.         stAtt_Value.v.HIGH_VALUE := pHiVal;
  63.         stAtt_Value.i.HIGH_VALUE := true;
  64.         cdapi.open_activity;
  65.         SELECT DOMAIN_REFERENCE, ID, MEANING INTO prAtt_Value
  66.                 FROM CI_ATTRIBUTE_VALUES
  67.                 WHERE    CI_ATTRIBUTE_VALUES.MEANING=pMeaning 
  68.                 AND CI_ATTRIBUTE_VALUES.DOMAIN_REFERENCE = DomainRef;
  69.         dbms_output.put_line('Attribute already exists');
  70.         stAtt_Value.v.ID := prAtt_Value.ID;
  71.         stAtt_Value.i.ID := true;
  72.         BEGIN
  73.             cioattribute_value.upd(prAtt_Value.ID,stAtt_Value);
  74.             dbms_output.put_line('Attribute value updated');
  75.             cdapi.validate_activity(act_status, act_warnings);
  76.             cdapi.close_activity(act_status);
  77.             if act_status != 'Y' then
  78.                 cdapi.abort_activity;
  79.                 RETURN -2;
  80.             else
  81.                 RETURN stAtt_Value.v.ID;
  82.             end if;
  83.         EXCEPTION
  84.                 WHEN OTHERS THEN
  85.                     if cdapi.stacksize > 0 then 
  86.                         if cdapi.activity is not null then 
  87.                             cdapi.abort_activity;
  88.                         end if;
  89.                     else
  90.                         if cdapi.activity is not null then 
  91.                             cdapi.abort_activity;
  92.                         end if;
  93.                     end if;
  94.                     RETURN -3;
  95.         END;
  96. EXCEPTION
  97.             WHEN NO_DATA_FOUND THEN
  98.             BEGIN
  99.                 cioattribute_value.ins(null,stAtt_Value);
  100.                 cdapi.validate_activity(act_status, act_warnings);
  101.                 cdapi.close_activity(act_status);
  102.                 if act_status != 'Y' then
  103.                     cdapi.abort_activity;
  104.                     RETURN -4;
  105.                 else
  106.                     RETURN stAtt_Value.v.ID;
  107.                 end if;
  108.             EXCEPTION
  109.                 WHEN OTHERS THEN
  110.                     if cdapi.stacksize > 0 then 
  111.                         if cdapi.activity is not null then 
  112.                             cdapi.abort_activity;
  113.                         end if;
  114.                     else
  115.                         if cdapi.activity is not null then 
  116.                             cdapi.abort_activity;
  117.                         end if;
  118.                     end if;
  119.                     RETURN -5;
  120.             END;
  121.             WHEN OTHERS THEN
  122.                 dbms_output.put_line('Unknown exception encountered');
  123.                 if cdapi.activity is not null then 
  124.                     cdapi.abort_activity;
  125.                 end if;
  126.                 RETURN -6;
  127. END ERWATVIN;
  128. /
  129.