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

CREATE OR REPLACE FORCE VIEW ESI_LINKED_COLUMNS
 (ROW_CREATE_TIME
 ,ROW_DATE_TIME
 ,CUSER
 ,MUSER
 ,MTERM
 ,SOURCE
 ,TO_BE_ARCHIVED
 ,APPN_ROW_ID
 ,SYSTEM_ID
 ,DEST_SYSTEM
 ,SOURCE_SYSTEM
 ,SOURCE_TABLE
 ,DEST_TABLE
 ,AMENDMENT_MODE
 ,DISTRIBUTION_RULE
 ,RESYNC_ORDER
 ,LINK_TRIGGER_COLUMNS
 ,ORDER_BY_CLAUSE
 ,EXTERNAL_TABLE
 ,SOURCE_KEY_COLUMNS
 ,DEST_KEY_COLUMNS
 ,DEST_COLUMN
 ,SOURCE_COLUMNS
 ,CONVERSION_FUNCTION)
 AS SELECT GREATEST(C.ROW_CREATE_TIME,T.ROW_CREATE_TIME) ROW_CREATE_TIME,
GREATEST(C.ROW_DATE_TIME,T.ROW_DATE_TIME) ROW_DATE_TIME,
C.CUSER,
C.MUSER,
C.MTERM,
C.SOURCE,
C.TO_BE_ARCHIVED,
C.APPN_ROW_ID,
T.SYSTEM_ID,
T.DEST_SYSTEM,
NULL "SOURCE_SYSTEM",
T.SOURCE_TABLE,
T.DEST_TABLE,
T.AMENDMENT_MODE,
T.DISTRIBUTION_RULE,
T.RESYNC_ORDER,
T.LINK_TRIGGER_COLUMNS,
T.ORDER_BY_CLAUSE,
T.EXTERNAL_TABLE,
DECODE(C.KEY_MAPPING, 'Y', NULL, T.SOURCE_KEY_COLUMNS) "SOURCE_KEY_COLUMNS",
DECODE(C.KEY_MAPPING, 'Y', NULL, T.DEST_KEY_COLUMNS) "DEST_KEY_COLUMNS",
C.DEST_COLUMN,
C.SOURCE_COLUMNS,
C.CONVERSION_FUNCTION
FROM
ESI_TABLE_MAPPINGS T,
ESI_COLUMN_MAPPINGS C
  WHERE T.DEST_SYSTEM = C.DEST_SYSTEM AND
T.SOURCE_TABLE = C.SOURCE_TABLE AND
T.DEST_TABLE = C.DEST_TABLE
/

