
--------------------------------------------------------------
-- The plasma view for ADLink
--------------------------------------------------------------
CREATE VIEW Pl_ADLink AS
  SELECT
    a.*,
    m.sourceOID,
    m.sourceOVer
  FROM
    ADLink a, ManyPtrTable m
  WHERE
    m.targetOID=a.OID AND m.targetOVer=a.OVer AND
      m.classAttrib='ADViewRoot.plasma';

--------------------------------------------------------------
-- FilesOut (FHCFilesOut) information for each part version
--------------------------------------------------------------
CREATE VIEW FOForVersions AS
  SELECT
    a.OVer,
    a.OID,
    a.osrelUserId,
    a.osrelFileId,
    o.osrelWorkAreaId,
    t.OVer as versionOVer,
    t.OID as versionOID,
    t.versionId,
    c.OID as contextOID,
    c.OID as contextOVer
  FROM
    TCVersionClass t
      LEFT OUTER JOIN FHCFilesOut a ON a.osrelFileId=t.osrelFileId
      LEFT OUTER JOIN FHCOVersion o ON o.OID=a.osrelOVersionId
      LEFT OUTER JOIN FHCOVersion c ON c.osrelWorkAreaId=o.osrelWorkAreaId;


--------------------------------------------------------------
-- Updated form of PartView context extent
--------------------------------------------------------------
CREATE VIEW OIDOVers AS
 SELECT 
   a.oid,
   cast(max(cast(a.over as char(4))) as DBOVER) as OVer,
   c.name as contextRelease, 
   c.name as releaseName,
   (case when f.isTip='yes' then substr(f.sid,1,locate(':',f.sid)-1) 
       else f.sid end) as contextName,
   f.oid as contextOID,
   a.osrelReleaseId,
   a.osrelReleaseId as releaseId
 FROM 
   TCVersionClass a, 
   FHCRelease c, 
   FHCOVersion f 
 WHERE 
   a.osrelreleaseid=c.oid and f.osrelreleaseid=c.oid AND
   (   (f.osrelworkareaid is NULL and a.osrelworkareaid is NULL) OR
       (f.osrelworkareaid=a.osrelworkareaid) OR
       (f.osrelworkareaid IS NOT NULL AND a.osrelworkareaid is NULL AND
             a.osrelOVersionId <= f.branchpointid)) AND
   a.OVer <= f.myOVer 
 GROUP BY 
   a.oid, 
   c.name, 
   f.sid,
   f.oid, 
   a.osrelReleaseid, 
   f.isTip ;

CREATE VIEW MiniPV AS
    select a.*,
           b.osrelTCSourceObjct,
	   b.osrelFileId,
	   b.osrelOVersionId,
	   b.osrelCollectorId,
           b.osrelWorkAreaId,
	   b.versionId,
	   b.changeType,
	   c.name as pathName,
           d.baseName as baseName,
           d.name as nuPathName,
           e.transState,
           e.fmode,
           e.type as fileType,
           e.nuAddDate,
           e.nuDropDate,
           e.changeDate as lastUpdate,
           e.osrelNuPathId as nuPathId,
           e.tempName as temporary,
           e.osrelAncestorId as ancestorSourceId,
           f.addDate,
           f.dropDate,
           f.osrelPathId as pathId,
           f.translation,
           f.restricted,
           f.osrelCompId as compId,
           f.osrelTipVersionId as tipVersionId,
           f.osrelCompId,
           f.osrelTipVersionId
      FROM OIDOVers a, TCVersionClass b, FHCPath c, FHCPath d, TCSourceObject e,
           FHCFile f
    WHERE a.oid=b.oid and a.over=b.over AND
        f.oid=b.osrelFileId AND c.oid=f.osrelPathId AND
        e.oid=b.osrelTCSourceObjct AND d.oid=e.osrelNuPathId ;

CREATE VIEW BasePV AS 
  SELECT
    a.*,
    a.versionId as id,
    a.osrelFileId as fileId,
    f.name as compName,
    g.osrelOVersionId as tipOVer,
    h.SID as committedVersion,
    a.versionId as foForVersionId,
    i.oid as filesOutId,
    i.osrelUserId as userId,
    j.login as userLogin,
    a.osrelCollectorId as partId,
    k.typeName as partType,
    a.osrelWorkAreaId as workAreaId,
    l.name as workAreaName,
    b.osrelParserId as parserId,
    m.description as parserName,
    a.osrelOVersionId as oVersionId,
    n.SID as currentVersion,
    b.buildStatus,
    (CASE when a.osrelWorkAreaId is null then 'none' else a.changeType END) as workAreaChange,
    a.osrelTCSourceObjct as sourceId
  FROM
    MiniPV a
    INNER JOIN FHCVersion b ON a.OID=b.OID AND a.OVer=b.OVer
    INNER JOIN FHCComp f ON f.OID=a.osrelCompId
    INNER JOIN TCCollectorClass k ON k.OID=a.osrelCollectorId
    INNER JOIN FHCOVersion n ON n.OID=a.osrelOVersionId
    LEFT OUTER JOIN TCVersionClass g ON g.OIDC=a.osrelTipVersionId
    LEFT OUTER JOIN FOForVersions i ON i.versionId=a.versionId AND  i.contextOID=a.contextOID
    LEFT OUTER JOIN FHCWorkArea l ON l.OID=a.osrelWorkAreaId
    LEFT OUTER JOIN FHCParser m ON m.OID=b.osrelParserId
    LEFT OUTER JOIN FHCOVersion h ON h.OID=g.osrelOVersionId
    LEFT OUTER JOIN FHCUser j ON j.OID=i.osrelUserId
  WHERE
    k.typeName <> 'FHBuildEvent' ;

----------------------------------------------------------
-- parts visible where contextName is a release tip
-- (context is a release name)
----------------------------------------------------------
CREATE VIEW releaseTipParts AS
  SELECT
    r.name as contextName,
    r.pTipOVer as contextOID,
    r.name as contextRelease,
    r.name as releaseName,
    r.oid  as osrelReleaseId,
    r.oid  as releaseId,
    n.oid,
    n.over
  FROM
    NuVersions n,
    FHCRelease r
  WHERE
    n.contextId=r.oid;

----------------------------------------------------------
-- deltas for contexts along the release trunk
----------------------------------------------------------
CREATE VIEW trunkDelta AS
  SELECT
    o.SID as contextName,
    o.OID as contextOID,
    r.name as contextRelease,
    r.name as releaseName,
    r.OID  as osrelReleaseId,
    r.OID as releaseId,
    v.oid,
    v.previousOVer as OVer
  FROM
    FHCOVersion o,
    FHCRelease r,
    TCVersionClass v
  WHERE
    -- given an OVer on the release trunk
    o.osrelWorkAreaId is null AND
    r.oid = o.osrelReleaseId AND

    -- find a version record beyond this point
    v.contextId = r.oid AND
    v.sidSequence > o.sidSequence AND

    -- which has a predecessor at or before this point
    v.previousSidSequenc <= o.sidSequence;

----------------------------------------------------------
-- delta for workarea/driver branch tips
----------------------------------------------------------
CREATE VIEW branchTipDelta AS
  SELECT
    w.name as contextName,
    w.pTipOVer as contextOID,
    r.name as contextRelease,
    r.name as releaseName,
    r.OID  as osrelReleaseId,
    r.oid  as releaseId,
    n.oid,
    n.over
  FROM
    NuVersions n,
    FHCWorkarea w,
    FHCRelease r
  WHERE
    w.osrelReleaseId=r.oid AND
    n.contextId=w.oid;

----------------------------------------------------------
-- parts visible for workarea/driver branch tips
-- (context is a workarea/driver name)
----------------------------------------------------------
CREATE VIEW branchTipParts AS
    -- release versions which are not to be replaced by a delta
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      t.contextRelease,
      t.releaseName,
      t.osrelReleaseId,
      t.releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      FHCOVersion o,
      ReleaseTipParts t
    WHERE
      w.osrelReleaseId = o.osrelReleaseId AND
      w.osrelReleaseId = t.releaseId AND
      o.osrelWorkAreaid is null AND
      t.OID not in (select OID from trunkDelta d
                               where d.contextOID = o.OID) AND
      o.OID = w.osrelBranchPointId AND
      t.OID not in (select OID from branchTipDelta d
                               where d.contextOID = w.pTipOver)
 UNION ALL
    -- release delta from branch point to tip, unless also on branch
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      t.contextRelease,
      t.releaseName,
      t.osrelReleaseId,
      t.releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      trunkDelta t
    WHERE
      t.OVer is not null AND
      t.contextOID = w.osrelBranchPointId AND
      t.OID not in (select OID from branchTipDelta d
                               where d.contextOID = w.pTipOver)
  UNION ALL
    -- branch tip
    SELECT
      b.contextName,
      b.contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.oid,
      b.over
    FROM
      branchTipDelta b;

----------------------------------------------------------
-- Redundant copy (for performance)
----------------------------------------------------------

CREATE VIEW branchTipPartscopy AS
    -- release versions which are not to be replaced by a delta
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      t.contextRelease,
      t.releaseName,
      t.osrelReleaseId,
      t.releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      FHCOVersion o,
      ReleaseTipParts t
    WHERE
      w.osrelReleaseId = o.osrelReleaseId AND
      w.osrelReleaseId = t.releaseId AND
      o.osrelWorkAreaid is null AND
      t.OID not in (select OID from trunkDelta d
                               where d.contextOID = o.OID) AND
      o.OID = w.osrelBranchPointId AND
      t.OID not in (select OID from branchTipDelta d
                               where d.contextOID = w.pTipOver)
 UNION ALL
    -- release delta from branch point to tip, unless also on branch
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      t.contextRelease,
      t.releaseName,
      t.osrelReleaseId,
      t.releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      trunkDelta t
    WHERE
      t.OVer is not null AND
      t.contextOID = w.osrelBranchPointId AND
      t.OID not in (select OID from branchTipDelta d
                               where d.contextOID = w.pTipOver)
  UNION ALL
    -- branch tip
    SELECT
      b.contextName,
      b.contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.oid,
      b.over
    FROM
      branchTipDelta b;

----------------------------------------------------------
-- parts visible for workarea/driver branch tips
-- where the workarea/driver has a branchPoint on the release tip
-- (context is a workarea/driver name)
----------------------------------------------------------
CREATE VIEW TipBranchParts AS
    -- release versions which are not to be replaced by a delta
    SELECT
      w.name as contextName,
      w.pTipOVer as contextOID,
      t.contextRelease,
      t.releaseName,
      t.osrelReleaseId,
      t.releaseId,
      t.oid,
      t.over
    FROM
      FHCWorkarea w,
      ReleaseTipParts t
    WHERE
      w.osrelReleaseId = t.releaseId AND
      NOT EXISTS (select d.OID from branchTipDelta d
                  where d.contextOID = w.pTipOver AND d.OID = t.OID)
  UNION ALL
    -- branch tip
    SELECT
      b.contextName,
      b.contextOID,
      b.contextRelease,
      b.releaseName,
      b.osrelReleaseId,
      b.releaseId,
      b.oid,
      b.over
    FROM
      branchTipDelta b;

----------------------------------------------------------
-- deltas for contexts along any driver/workarea branch
----------------------------------------------------------
CREATE VIEW branchDelta AS
  SELECT
    o.SID as contextName,
    o.OID as contextOID,
    r.name as contextRelease,
    r.name as releaseName,
    r.OID  as osrelReleaseId,
    r.OID as releaseId,
    v.oid,
    v.previousOVer as OVer
  FROM
    FHCOVersion o,
    FHCRelease r,
    FHCWorkArea w,
    TCVersionClass v
  WHERE
    -- given an OVer on the release trunk
    o.osrelWorkAreaId = w.oid AND
    o.osrelReleaseId = r.oid AND

    -- find a version record beyond this point
    v.contextId = w.oid AND
    v.sidSequence > o.sidSequence AND

    -- which has a predecessor at or before this point
    v.previousSidSequenc <= o.sidSequence;

----------------------------------------------------------
-- The latest part versions visible from the selected context
-- NOTE: Context is selected by name
-- (all context views)
----------------------------------------------------------
CREATE VIEW PartViewVersions AS
    SELECT * from OIDOVers ;

--------------------------------------------------------------
-- Context-related Build Event version ID's
--------------------------------------------------------------
CREATE VIEW VersionBEIDs AS
  SELECT DISTINCT
    p.OVer,
    p.OID,
    v.OID as versionOID,
    v.OVer as versionOVer,
    v.versionId,
    p.contextOID
  FROM
    OIDOVers p,
      FHCVersion f,
      TCVersionClass v,
      ManyPtrTable m
  WHERE
    v.OID=f.OID AND v.OVer=f.OVer AND
        m.classAttrib='ADViewRoot.plasma' AND m.targetOID=f.generatedBy AND
        p.OID=m.sourceOID;


--------------------------------------------------------------
-- Translates for a given Build Event Version
--------------------------------------------------------------
CREATE VIEW TranslatesForBE AS
  SELECT
    b.OVer,
    b.OID,
    l.targetOID,
    l.targetOVer
  FROM
    FHBuildEvent_versn b,
    ManyPtrTable l
  WHERE
    l.sourceOID=b.OID AND
      l.sourceOVer=b.OVer AND
      l.classAttrib='FHBuildEvent_version.translates';


--------------------------------------------------------------
-- Build Event versions for each Part Version
--------------------------------------------------------------
CREATE VIEW BEsForVersions AS
  SELECT
    b.OVer,
    b.OID,
    b.osrelBuilderId,
    b.beTS,
    b.builderRC,
    b.outputFileParms,
    b.buildParms,
    (select count(*) from TranslatesForBE where
       OVer=b.OVer AND OID=b.OID) as numTranslates,
    v.versionOID,
    v.versionOVer,
    v.versionId,
    v.contextOID
  FROM
    VersionBEIDs v,
      FHBuildEvent_versn b
  WHERE
    b.OID=v.OID AND b.OVer=v.OVer;


--------------------------------------------------------------
-- Includes for a given Part Version
--------------------------------------------------------------
CREATE VIEW DependsOnForPart AS
  SELECT
    v.OVer,
    v.OID,
    l.relOID,
    l.relOVer
  FROM
    FHCVersion v,
    ADLink_source l
  WHERE
    l.sourceOID=v.OID AND
      l.sourceOVer=v.OVer AND
      l.classAttr='FHCVersion.dependsOn';


--------------------------------------------------------------
-- The dependency information for a given Part Version
--------------------------------------------------------------
CREATE VIEW InputsForVersions AS
  SELECT
    v.versionId,
    (select count(*) from DependsOnForPart where
       OVer=v.OVer AND OID=v.OID) as numIncludes
  FROM
    TCVersionClass v;


--------------------------------------------------------------
-- Inputs for a build event
--------------------------------------------------------------
CREATE VIEW InputsToBE AS
  SELECT
    t.sourceOVer as parentOVer,
    t.sourceOID as parentOID,
    i.pInput as childOID
  FROM
    ManyPtrTable t
    INNER JOIN Pl_FBrsrdyn i ON i.OID=t.targetOID AND   --FHBTransformedByInfo
          i.sourceOID=t.sourceOID AND i.sourceOVer=t.sourceOVer AND
          i.pBE=t.sourceOID
  WHERE
    t.classAttrib='FHBuildEvent_version.translates';


--------------------------------------------------------------
-- The parent information for a specified part and its parent
--------------------------------------------------------------
CREATE VIEW ParentsForPart AS
      -- DependsOn relationships
      SELECT DISTINCT
        c.contextOID,
        d.OVer as parentOVer,
        d.OID as parentOID,
        t.typeName as parentType,
        p.name as parentName,
        v.OVer as childOVer,
        v.OID as childOID,
        v.versionId as childId,
        'DependentOf' as typeOfRelation
      FROM
        DependsOnForPart d
        INNER JOIN TCCollectorClass t ON t.OID=d.OID
        INNER JOIN TCVersionClass f ON f.OID=d.OID AND f.OVer=d.OVer
        INNER JOIN TCSourceObject s ON s.OID=f.osrelTCSourceObjct
        INNER JOIN FHCPath p ON p.OID=s.osrelNuPathId
        INNER JOIN OIDOVers c ON c.OID=d.OID AND c.OVer=d.OVer
        INNER JOIN ADLink l ON l.OVer=d.relOVer AND l.OID=d.relOID
        INNER JOIN TCVersionClass v ON v.OID=l.adTarget
  UNION ALL
      -- InputTo relationships
      SELECT DISTINCT
        b.contextOID,
        b.versionOVer as parentOVer,
        b.versionOID as parentOID,
        t.typeName as parentType,
        p.name as parentName,
        v.OVer as childOVer,
        v.OID as childOID,
        v.versionId as childId,
        'InputTo' as typeOfRelation
      FROM
        BEsForVersions b
        INNER JOIN TCCollectorClass t ON t.OID=b.versionOID
        INNER JOIN TCVersionClass f ON f.OID=b.versionOID AND f.OVer=b.versionOVer
        INNER JOIN TCSourceObject s ON s.OID=f.osrelTCSourceObjct
        INNER JOIN FHCPath p ON p.OID=s.osrelNuPathId
        INNER JOIN OIDOVers c ON c.OID=f.OID AND c.OVer=f.OVer AND
              c.contextOID=b.contextOID
        INNER JOIN InputsToBE i ON b.OID=i.parentOID AND b.OVer=i.parentOVer
        INNER JOIN TCVersionClass v ON v.OID=i.childOID
  UNION ALL
      -- SiblingOutput relationships
      SELECT DISTINCT
        b.contextOID,
        b.versionOVer as parentOVer,
        b.versionOID as parentOID,
        t.typeName as parentType,
        p.name as parentName,
        v.OVer as childOVer,
        v.OID as childOID,
        v.versionId as childId,
        'SiblingOutput' as typeOfRelation
      FROM
        BEsForVersions b
        INNER JOIN TCCollectorClass t ON t.OID=b.versionOID
        INNER JOIN TCVersionClass f ON f.OID=b.versionOID AND f.OVer=b.versionOVer
        INNER JOIN TCSourceObject s ON s.OID=f.osrelTCSourceObjct
        INNER JOIN FHCPath p ON p.OID=s.osrelNuPathId
        INNER JOIN OIDOVers c ON c.OID=f.OID AND c.OVer=f.OVer AND
              c.contextOID=b.contextOID
        INNER JOIN BEsForVersions e ON e.contextOID=b.contextOID AND e.OID=b.OID
        INNER JOIN TCVersionClass v ON v.versionId=e.versionId AND v.OID!=f.OID
        INNER JOIN OIDOVers d ON d.OID=v.OID AND d.OVer=v.OVer AND
              d.contextOID=b.contextOID;


--------------------------------------------------------------
-- Plasma for a specified part
--------------------------------------------------------------
CREATE VIEW PlasmaForVersion AS
  SELECT
    v.OVer as versionOVer,
    v.OID as versionOID,
    m.targetOVer as OVer,
    m.targetOID as OID
  FROM
    TCVersionClass v, ManyPtrTable m
  WHERE
    m.sourceOVer=v.OVer AND m.sourceOID=v.OID AND
      m.classAttrib='ADViewRoot.plasma';


--------------------------------------------------------------
-- FilesOut (FHCFilesOut) information for PartFullView entries
--------------------------------------------------------------
CREATE VIEW FOForWorkArea AS
  SELECT
    f.OID,
    f.osrelUserId,
    f.osrelFileId as fileId,
    w.OID as osrelWorkAreaId
  FROM
    FHCFilesOut f
    INNER JOIN FHCOVersion o ON o.OID=f.osrelOVersionId
    INNER JOIN FHCWorkArea w ON w.OID=o.osrelWorkAreaId;


--------------------------------------------------------------
-- SourceObject info for Part Versions
--------------------------------------------------------------
CREATE VIEW VersionSourceInfo AS
  SELECT
    a.*,
    s.OID as sourceId,
    s.nuDropDate,
    s.nuAddDate,
    s.changeDate,
    s.osrelNuPathId
  FROM
    OIDOVers a
    INNER JOIN TCVersionClass v ON v.OID=a.OID and v.OVer=a.OVer
    INNER JOIN TCSourceObject s ON s.OID=v.osrelTCSourceObjct;


--------------------------------------------------------------
-- Uncommitted WorkAreas
--------------------------------------------------------------
CREATE VIEW UncommitWAs AS
    -- Track WorkAreas
    SELECT
      w.OID,
      w.name,
      w.pTipOVer as tipOVer,
      w.osrelReleaseId as releaseId,
      t.OID as trackId,
      CAST (NULL as DBOID) as driverId,
      t.state
    FROM
      FHCWorkArea w, FHCTrack t
    WHERE
      t.osrelWorkAreaId=w.OID AND
        t.state not in ('commit','complete','test')
  UNION ALL
    -- Driver WorkAreas
    SELECT
      w.OID,
      w.name,
      w.pTipOVer as tipOVer,
      w.osrelReleaseId as releaseId,
      CAST (NULL as DBOID) as trackId,
      d.OID as driverId,
      d.state
    FROM
      FHCWorkArea w, FHCLevel d
    WHERE
      d.osrelWorkAreaId=w.OID AND
        d.state not in ('commit','complete');


------------------------------------------------------
--linkObject for all the views
------------------------------------------------------
CREATE VIEW adobjectFind AS
   SELECT
     a.oid as OID, 
     a.over as OVER, 
     pvv.contextRelease, 
     pvv.contextName, 
     a.className
   FROM
     ManyPtrTable m,
     ADObject  a,
     OIDOVers pvv
   WHERE
     m.sourceOID=pvv.oid AND
     m.sourceOVER=pvv.over AND
     m.targetoid=a.OID  AND
     m.targetover=a.OVER AND
     m.classAttrib='ADViewRoot.plasma' AND
     substr(cast(a.OID as character(12)),1,4) =
         substr(cast(m.sourceOID as character(12)),1,4)  AND
     substr(cast(a.OID as character(12)),9,2) =
         substr(cast(m.sourceOID as character(12)),9,2) ;

         
--------------------------------------------------------------
-- Recursive view giving the ancestor tree for source objects
--------------------------------------------------------------
CREATE VIEW AncestorSources AS 
  WITH RECVSI (
      level,
      selectOID,
      childId,
      ancestorId)
  AS (  
        SELECT
          1,
          root.OID as selectOID,
          root.OID as childId,
          root.osrelAncestorId as ancestorId
        FROM
          TCSourceObject root
      UNION ALL
        SELECT
          child.level+1,
          child.selectOID,
          parent.OID as childId,
          parent.osrelAncestorId as ancestorId
        FROM
          RECVSI child, TCSourceObject parent
        WHERE
          child.ancestorId = parent.OID AND
            child.level < 1000
     )
  SELECT 
    a.*
  FROM
    RECVSI a;


--------------------------------------------------------------
-- Common ancestors of two versions of the same part
--------------------------------------------------------------
CREATE VIEW CommonAncestors AS
  SELECT
    v1.oid as oid1,
    v1.over as over1,
    v1.versionId as versionId1,
    v2.oid as oid2,
    v2.over as over2,
    v2.versionId as versionId2,
    a1.ancestorId as ancestorSourceId,
    a1.level,
    va.oid as ancestorOID,
    va.over as ancestorOVer,
    va.versionId as ancestorVersionId,
    o.sid as ancestorVersion,
    r.name as ancestorRelease
  FROM
    TCVersionClass v1,
    TCVersionClass v2,
    AncestorSources a1,
    AncestorSources a2,
    TCVersionClass va,
    FHCOVersion o,
    FHCRelease r
  WHERE
    a1.selectOID=v1.osrelTCSourceObjct AND
    a2.selectOID=v2.osrelTCSourceObjct AND
    a1.ancestorId=a2.ancestorId AND
    va.osrelTCSourceObjct=a1.ancestorId AND
    o.OID=va.osrelOVersionId AND
    r.OID=o.osrelReleaseId;


--------------------------------------------------------------
-- Other uncommitted versions:
--   Same Release as selected version
--   In an uncommitted work area
--   Not an ancestor of the selected version (unless checked out)
--   Not an ancestor of the release tip version
--------------------------------------------------------------
CREATE VIEW OtherUncommitted AS
  SELECT
    n.OID,
    n.OVer,
    n.contextId as workAreaId,
    w.name as workAreaName,
    v.OID as selectOID,
    v.OVer as selectOVer,
    v.osrelTCSourceObjct as selectSourceId,
    v.versionId as selectVersionId
  FROM
    NuVersions n,
      TCVersionClass nv,
      TCVersionClass v,
      UncommitWAs w,
      FHCOVersion o,
      FHCFile f,
      TCVersionClass tv
  WHERE
    n.contextId = w.OID AND
      n.OID = v.OID AND n.OVer != v.OVer AND
      o.OID = v.osrelOVersionId AND
      o.osrelReleaseId = w.releaseId AND 
      nv.OID = n.OID and nv.OVer = n.OVer AND
      f.OID = v.osrelFileId AND
      tv.OIDC = f.osrelTipVersionId AND
      nv.osrelTCSourceObjct != v.osrelTCSourceObjct AND
      nv.osrelTCSourceObjct != tv.osrelTCSourceObjct AND
      NOT EXISTS (select a.ancestorId from AncestorSources a where
          a.selectOID in (v.osrelTCSourceObjct,tv.osrelTCSourceObjct) AND 
          a.ancestorId=nv.osrelTCSourceObjct AND
          nv.osrelWorkAreaId is not null);

--------------------------------------------------------------
-- Join of OtherUncommitted with PartView
--   This allows us to find other uncommitted changes for a part
--   in a specified work area
--------------------------------------------------------------
CREATE VIEW OtherUncommittedPV AS
  SELECT
    v.OID as selectOID,
    v.OVer as selectOVer,
    v.contextName,
    v.contextOID,
    v.releaseName,
    v.releaseId,
    p.name as nuPathName,
    u.workAreaId,
    u.workAreaName,
    u.OID,
    u.OVer
  FROM
    BranchTipParts v,
      OtherUncommitted u,
      TCSourceObject s,
      FHCPath p
  WHERE
    u.selectOID=v.OID AND u.selectOVer=v.OVer AND
    s.OID=u.selectSourceId AND p.OID=s.osrelNuPathId;


--------------------------------------------------------------
-- FilesOut (FHCFilesOut) information for each part version
-- This variation is to be used for the temporary PartView
--------------------------------------------------------------
CREATE VIEW FOForVers2 AS
  SELECT
    a.OID as filesOutId,
    a.osrelUserId as userId,
    t.OID as versionOID,
    t.OVer as versionOVer,
    o.osrelWorkAreaId as foWorkAreaId,
    u.login as userLogin
  FROM
    TCVersionClass t
    LEFT OUTER JOIN FHCFilesOut a ON a.osrelFileId=t.osrelFileId
    LEFT OUTER JOIN FHCOVersion o ON o.OID=a.osrelOVersionId
    LEFT OUTER JOIN FHCUser u ON u.OID=a.osrelUserId;

--------------------------------------------------------------
-- The temp table version of PartView
--------------------------------------------------------------

CREATE VIEW BaseTmpPartView AS 
  SELECT
    b.oid as versionOID,
    b.OVer as versionOVer,
    c.versionId as id,
    c.osrelFileId as fileId,
    d.addDate,
    d.dropDate,
    d.osrelPathId as pathId,
    e.name as pathName,
    d.translation,
    d.restricted,
    d.osrelCompId as compId,
    f.name as compName,
    d.osrelTipVersionId as tipVersionId,
    g.osrelOVersionId as tipOVer,
    h.SID as committedVersion,
    c.versionId as foForVersionId,
    b.OID as partId,
    k.typeName as partType,
    c.osrelWorkAreaId as workAreaId,
    l.name as workAreaName,
    b.osrelParserId as parserId,
    m.description as parserName,
    c.osrelOVersionId as oVersionId,
    n.SID as currentVersion,
    c.changeType,
    b.buildStatus,
    (CASE when c.osrelWorkAreaId is null then 'none' else c.changeType END) as workAreaChange,
    c.osrelTCSourceObjct as sourceId,
    o.transState,
    o.fmode,
    o.type as fileType,
    o.nuAddDate,
    o.nuDropDate,
    o.changeDate as lastUpdate,
    o.osrelNuPathId as nuPathId,
    p.baseName,
    p.name as nuPathName,
    o.tempName as temporary,
    o.osrelAncestorId as ancestorSourceId,
    c.osrelReleaseId as releaseId,
    r.name as releaseName,
    CAST (NULL as VarChar(5)) as typeOfRelation,
    CAST (NULL as VarChar(5)) as parentName
  FROM
    FHCVersion b
    INNER JOIN TCVersionClass c ON c.OID=b.OID AND c.OVer=b.OVer
    INNER JOIN FHCRelease r ON r.OID=c.osrelReleaseId
    INNER JOIN FHCFile d ON d.OID=c.osrelFileId
    INNER JOIN FHCPath e ON e.OID=d.osrelPathId
    INNER JOIN FHCComp f ON f.OID=d.osrelCompId
    INNER JOIN TCCollectorClass k ON k.OID=b.OID
    INNER JOIN FHCOVersion n ON n.OID=c.osrelOVersionId
    INNER JOIN TCSourceObject o ON o.OID=c.osrelTCSourceObjct
    INNER JOIN FHCPath p ON p.OID=o.osrelNuPathId
    LEFT OUTER JOIN TCVersionClass g ON g.OIDC=d.osrelTipVersionId
    LEFT OUTER JOIN FHCWorkArea l ON l.OID=c.osrelWorkAreaId
    LEFT OUTER JOIN FHCParser m ON m.OID=b.osrelParserId
    LEFT OUTER JOIN FHCOVersion h ON h.OID=g.osrelOVersionId
  WHERE
    k.typeName<>'FHBuildEvent';

