-- -------------------------------------------------------------
-- loadCHECKload_lix.sql:
-- This SQL script is to check the content of the loading layer
--   here: the IDs that refer to LOVs, extended version,
--   (same functionality as loadCHECKload_lid.sql, just 4 parameters:
--         LOAD_* table, column, CERA Core table, column).
--   It is called from SQL script loadCHECK.sql.
--                                                ft, 1999.07.20
-- -------------------------------------------------------------
-- 1: no NULLS allowed in the _id column
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       12, L.entry_id, LOAD_ENTRY.entry_acronym, 'LOAD_&1', 'FATNUL', '&2=NULL found: correct and run loadCHECK again!'
  from  LOAD_&1 L, LOAD_ENTRY where L.&2 is NULL 
   and  L.entry_id = LOAD_ENTRY.entry_id;
-- -------------------------------------------------------------
-- 2: id has to exist in the original LOV or in NEW_*
-- -------------------------------------------------------------
insert into LOAD_ERROR_MESSAGES select distinct 
       13, L.entry_id, LOAD_ENTRY.entry_acronym, 'LOAD_&1' ,'FATILL','&2='||L.&2||' not in table CERA.&3 nor in NEW_&3'
  from  LOAD_&1 L, LOAD_ENTRY
 where  L.&2 not IN (select &4 from     CERA.&3)
   and  L.&2 not IN (select &4 from CERA.NEW_&3) 
   and  L.entry_id = LOAD_ENTRY.entry_id;
-- The END of loadCHECKnew_lix.sql
