/*
--CopyRight(C) 2003，NTT All rights reserved
--
--	XMS2データベースビューの作成
--	XMS2データベースのビュー領域にビューを新規作成します。
--		変更履歴
--			2004.01.08 新規作成 seko
--
*/

CREATE OR REPLACE VIEW v_LOList 
AS 
SELECT 
   c.LOID AS ParentID,
   c.LOTitle AS ParentName,
   c.foreignID AS ParentForeignID,
   l.RealLOID AS RealID,
   l.LOType AS Type,
   l.LOID AS ChildID,
   l.LOTitle AS ChildName,
   l.foreignID AS ChildForeignID, 
   r.sort_no AS sort_no 
 FROM 
   LORelation r, 
   LO l, 
   LO c 
 WHERE 
   l.LOID = r.childID 
  AND 
   c.LOID = r.parentID 
  AND 
   c.LOType = 0 
;

CREATE OR REPLACE VIEW v_UserList AS 
SELECT 
   g.group_id AS ParentID,
   g.group_name AS ParentName,
   g.foreignID AS ParentForeignID,
   p.user_id AS ChildID,
   p.user_name AS ChildName,
   p.foreignID AS ChildForeignID,
   r.sort_no AS sort_no 
 FROM 
   GroupAndPeople r, 
   People p, 
   Groups g 
 WHERE 
   p.user_id = r.childID 
  AND 
   g.group_id = r.parentID 
;

exit;