text: ﻿/* -------------------------------------------------------------------------- */
  /* Make PUNKTINFO data (with namespace NET). 
  /* File: MakePunktInfoNET.sql   
  /* All but NET:jessen is found using the REFGEO.NET table. Jessen punkter is
  /* found using table sub_ref and løbenummer (LBN) - see sql below
  /* -------------------------------------------------------------------------- */
  
  DELETE FROM PUNKTINFO WHERE INFOTYPEID IN (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE LIKE 'NET:%');
  -- SELECT COUNT(*) FROM PUNKTINFO WHERE INFOTYPE LIKE 'NET:%';
  -- SELECT INFOTYPE, COUNT(*) FROM PUNKTINFO WHERE INFOTYPE LIKE 'NET:%' GROUP BY INFOTYPE;
  
  -- NET:jessen
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:jessen' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM sub_ref@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.LBN >= 81000 AND net.LBN < 82000
  ;
  
  -- NET:10KM
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:10KM' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.TIKMNET = 1 
  ;
  
  -- NET:GPSNET
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:GPSNET' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.GPSNET = 1 
  ;
  
  -- NET:REFDK
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:REFDK' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.REFDK = 1 
  ;
  
  -- NET:5D
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:5D' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.NET5D = 1 
  ;
  
  
  -- NET:DMI
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:DMI' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.DMI = 1 
  ;
  
  
  -- NET:tyngde
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:tyngde' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.TYNGDE = 1 
  ;
  
  
  -- NET:FO10KM
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:FO10KM' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.FO10KMNET = 1 
  ;
  
  
  -- NET:CORS
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:CORS' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.CORS = 1 
  ;
  
  
  -- NET:EUREFDK
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:EUREFDK' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.EUREFDK = 1 
  ;
  
  -- NET:DEFVAND
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:DEFVAND' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.DEF_VAND = 1 
  ;
  
  -- NET:REFGR
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:REFGR' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.REFGR = 1 
  ;
  
  
  -- NET:GRAENSEAFM
  INSERT INTO PUNKTINFO (REGISTRERINGFRA, INFOTYPEID, SAGSEVENTFRAID, PUNKTID)
  SELECT 
      SYSDATE,
      (SELECT INFOTYPEID FROM PUNKTINFOTYPE WHERE INFOTYPE = 'NET:GRAENSEAFM' AND ROWNUM <= 1) AS INFOTYPEID,
      '15101d43-ac91-4c7c-9e58-c7a0b5367910',
      conv.ID
  FROM NET@refgeo net
  INNER JOIN CONV_PUNKT conv ON net.REFNR = conv.REFNR
  INNER JOIN PUNKT p ON p.ID = conv.ID
  WHERE net.GRAENSEAFM = 1 
  ;
  
  COMMIT;
  
  
  