/*****************************************************************************
*
* Filename         - $Workfile:   FSU_COLS.sql  $
*
* Author           - Dave Banks
*
* Project Name     - UltraTrak Core 
*
* Source Code
* Identifier       - Database (Master Standby) 
*
* Design Spec
* Section          - 
*
* Contents         - Database script to create view FSU_COLS         
*
* Language         - Oracle SQL script      
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

CREATE OR REPLACE VIEW FSU_COLS (TABLE_NAME,COLUMN_NAME,DATA_TYPE ) AS 
SELECT U.TABLE_NAME, U.COLUMN_NAME, U.DATA_TYPE
FROM FSU_TABS F, USER_TAB_COLUMNS U
  WHERE F.TABLE_NAME = U.TABLE_NAME
UNION
SELECT 'LIVE.'||D.TABLE_NAME, D.COLUMN_NAME, D.DATA_TYPE
FROM FSU_TABS F, DBA_TAB_COLUMNS D
  WHERE F.TABLE_NAME LIKE 'LIVE.%' AND D.OWNER = 'LIVE' AND
        TRIM (SUBSTR(F.TABLE_NAME,6)) = D.TABLE_NAME
UNION
SELECT 'ARCH.'||D.TABLE_NAME, D.COLUMN_NAME, D.DATA_TYPE
FROM FSU_TABS F, DBA_TAB_COLUMNS D
  WHERE F.TABLE_NAME LIKE 'ARCH.%' AND D.OWNER = 'ARCH' AND
        TRIM (SUBSTR(F.TABLE_NAME,6)) = D.TABLE_NAME;

