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 / erwuiein.six < prev    next >
Encoding:
Text File  |  1998-01-28  |  6.0 KB  |  172 lines
  1. /* ------------------------------------------------------------------------
  2. --    ERWUIEIN.SIX                                                         --
  3. --    Copyright (C) Logic Works, Inc. 1994                                 --
  4. --                                                                       --
  5. --  Stored function to insert ERwin primary keys or                         --
  6. --  identifying relationships into the Designer 2000 dictionary as          --
  7. --  UIEs                                                                 --
  8. ------------------------------------------------------------------------- */
  9. CREATE OR REPLACE FUNCTION ERWUIEIN(pAttRel CI_UNIQUE_IDENTIFIER_ENTRIES.ATTRIBUTE_OR_RELATION%TYPE,   
  10.                      pEntName CI_ENTITIES.NAME%TYPE,
  11.                      pEntID      CI_ENTITIES.ID%TYPE,
  12.                      pOtherEntID CI_ENTITIES.ID%TYPE,
  13.                      pAttRelName CI_UNIQUE_IDENTIFIER_ENTRIES.RELATIONSHIP_NAME%TYPE,
  14.                      pSequence    CI_UNIQUE_IDENTIFIER_ENTRIES.SEQUENCE_NUMBER%TYPE,
  15.                      pApp        CI_APPLICATION_SYSTEMS.NAME%TYPE,
  16.                      pVersion    CI_APPLICATION_SYSTEMS.VERSION%TYPE)
  17.                 RETURN CI_UNIQUE_IDENTIFIER_ENTRIES.ID%TYPE                             
  18.     AS
  19.     TYPE recEntity IS RECORD
  20.         (APPLICATION_SYSTEM_OWNED_BY   CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  21.          ID CI_ENTITIES.ID%TYPE,
  22.          NAME CI_ENTITIES.NAME%TYPE);
  23.     TYPE recAttribute IS RECORD
  24.         (ENTITY_REFERENCE CI_ATTRIBUTES.ENTITY_REFERENCE%TYPE,
  25.          ID                  CI_ATTRIBUTES.ID%TYPE,
  26.          NAME              CI_ATTRIBUTES.NAME%TYPE);
  27.      TYPE recRelend IS RECORD
  28.         (FROM_ENTITY_REFERENCE    CI_RELATIONSHIP_ENDS.FROM_ENTITY_REFERENCE%TYPE,
  29.          ID CI_RELATIONSHIP_ENDS.ID%TYPE,
  30.          NAME CI_RELATIONSHIP_ENDS.NAME%TYPE,
  31.          TO_ENTITY_REFERENCE   CI_RELATIONSHIP_ENDS.TO_ENTITY_REFERENCE%TYPE);
  32.     TYPE recUniqueIE is RECORD
  33.         (ATTRIBUTE_OR_RELATION    CI_UNIQUE_IDENTIFIER_ENTRIES.ATTRIBUTE_NAME%TYPE,
  34.          ATTRIBUTE_REFERENCE    CI_ATTRIBUTES.ID%TYPE,
  35.          ENTITY_REFERENCE        CI_ENTITIES.ID%TYPE,
  36.          ID                        CI_UNIQUE_IDENTIFIER_ENTRIES.ID%TYPE,
  37.          RELATIONSHIP_END_REFERENCE CI_UNIQUE_IDENTIFIER_ENTRIES.RELATIONSHIP_END_REFERENCE%TYPE);
  38.     prUIE            recUniqueIE;
  39.     stUIE            ciounique_identifier_entry.data;
  40.     prEntity        recEntity;
  41.     prAttribute        recAttribute;
  42.     prRelend        recRelend;
  43.     EntityRef        CI_ENTITIES.ID%TYPE;
  44.     AttRelRef        CI_ATTRIBUTES.ID%TYPE;
  45.     nSequenceNumber CI_UNIQUE_IDENTIFIER_ENTRIES.SEQUENCE_NUMBER%TYPE;
  46.       act_status        varchar2(100);
  47.     act_warnings    varchar2(100);
  48. BEGIN
  49.     if cdapi.initialized = false then
  50.         RETURN -1;
  51.     end if;
  52.         BEGIN
  53.             IF pVersion = 0 THEN 
  54.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  55.                     FROM CI_ENTITIES
  56.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntName)
  57.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  58.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  59.                     CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  60.                     CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  61.             ELSE
  62.                 SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  63.                     FROM CI_ENTITIES
  64.                     WHERE    CI_ENTITIES.NAME=UPPER(pEntName)
  65.                     AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  66.                     (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  67.                     CI_APPLICATION_SYSTEMS.NAME =UPPER(pApp) AND
  68.                     CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  69.             END IF; 
  70.             EntityRef := prEntity.ID;
  71.         EXCEPTION
  72.             WHEN OTHERS THEN
  73.                 RETURN -1;
  74.         END;
  75.         IF UPPER(pAttRel) != 'A' THEN
  76.             BEGIN
  77.                 SELECT FROM_ENTITY_REFERENCE,ID,
  78.                     NAME, TO_ENTITY_REFERENCE
  79.                     INTO prRelend
  80.                     FROM CI_RELATIONSHIP_ENDS
  81.                     WHERE FROM_ENTITY_REFERENCE=EntityRef
  82.                     AND NAME=pAttRelName;
  83.                 AttRelRef := prRelend.ID;
  84.                 BEGIN
  85.                     SELECT ATTRIBUTE_OR_RELATION,ATTRIBUTE_REFERENCE,
  86.                             ENTITY_REFERENCE,ID,RELATIONSHIP_END_REFERENCE
  87.                             INTO prUIE FROM CI_UNIQUE_IDENTIFIER_ENTRIES
  88.                             WHERE ENTITY_REFERENCE = EntityRef AND
  89.                             RELATIONSHIP_END_REFERENCE = AttRelRef AND
  90.                             SEQUENCE_NUMBER = pSequence;
  91.                     RETURN prUIE.ID;
  92.                 EXCEPTION
  93.                     WHEN NO_DATA_FOUND THEN
  94.                          stUIE.v.RELATIONSHIP_NAME := pAttRelName;
  95.                         stUIE.i.RELATIONSHIP_NAME := true;
  96.                         stUIE.v.RELATIONSHIP_END_REFERENCE := AttRelRef;
  97.                         stUIE.i.RELATIONSHIP_END_REFERENCE := true;
  98.                         stUIE.v.ENTITY_REFERENCE := EntityRef;
  99.                         stUIE.i.ENTITY_REFERENCE := true;
  100.                         stUIE.v.ATTRIBUTE_OR_RELATION := 'R';
  101.                         stUIE.i.ATTRIBUTE_OR_RELATION := true;
  102.                         stUIE.v.SEQUENCE_NUMBER := pSequence;
  103.                         stUIE.i.SEQUENCE_NUMBER := true;
  104.                     WHEN OTHERS THEN
  105.                         RETURN -1;
  106.                 END;
  107.             EXCEPTION
  108.                 WHEN OTHERS THEN
  109.                     RETURN -1;
  110.             END;
  111.         ELSE
  112.             BEGIN
  113.                 SELECT ENTITY_REFERENCE, ID, NAME INTO prAttribute
  114.                     FROM CI_ATTRIBUTES
  115.                     WHERE    CI_ATTRIBUTES.NAME=UPPER(pAttRelName)
  116.                     AND CI_ATTRIBUTES.ENTITY_REFERENCE = EntityRef;
  117.                 AttRelRef := prAttribute.ID;
  118.                 BEGIN
  119.                     SELECT ATTRIBUTE_OR_RELATION,ATTRIBUTE_REFERENCE,
  120.                             ENTITY_REFERENCE,ID,RELATIONSHIP_END_REFERENCE
  121.                             INTO prUIE FROM CI_UNIQUE_IDENTIFIER_ENTRIES
  122.                             WHERE ENTITY_REFERENCE = EntityRef AND
  123.                             ATTRIBUTE_REFERENCE = AttRelRef AND
  124.                             SEQUENCE_NUMBER = pSequence;
  125.                     RETURN prUIE.ID;
  126.                 EXCEPTION
  127.                     WHEN NO_DATA_FOUND THEN
  128.                         stUIE.v.ATTRIBUTE_NAME := pAttRelName;
  129.                         stUIE.i.ATTRIBUTE_NAME := true;
  130.                         stUIE.v.ATTRIBUTE_REFERENCE := AttRelRef;
  131.                         stUIE.i.ATTRIBUTE_REFERENCE := true;
  132.                         stUIE.v.ENTITY_REFERENCE := EntityRef;
  133.                         stUIE.i.ENTITY_REFERENCE := true;
  134.                         stUIE.v.ATTRIBUTE_OR_RELATION  := 'A';
  135.                         stUIE.i.ATTRIBUTE_OR_RELATION  := true;
  136.                         stUIE.v.SEQUENCE_NUMBER := pSequence;
  137.                         stUIE.i.SEQUENCE_NUMBER := true;
  138.                     WHEN OTHERS THEN
  139.                         RETURN -1;
  140.                 END;
  141.             EXCEPTION
  142.                 WHEN OTHERS THEN
  143.                     RETURN -1;
  144.             END;
  145.         END IF;
  146.         BEGIN
  147.             cdapi.open_activity;
  148.             ciounique_identifier_entry.ins(NULL,stUIE);
  149.             cdapi.validate_activity(act_status, act_warnings);
  150.             cdapi.close_activity(act_status);
  151.             if act_status != 'Y' then
  152.                 cdapi.abort_activity;
  153.                 RETURN -1;
  154.             else
  155.                 RETURN stUIE.v.ID;
  156.             end if;
  157.         EXCEPTION
  158.             WHEN OTHERS THEN
  159.                     if cdapi.stacksize > 0 then 
  160.                         if cdapi.activity is not null then 
  161.                             cdapi.abort_activity;
  162.                         end if;
  163.                     else
  164.                         if cdapi.activity is not null then 
  165.                             cdapi.abort_activity;
  166.                         end if;
  167.                     end if;
  168.                 RETURN -1;
  169.         END;                    
  170. END ERWUIEIN;  
  171. /
  172.