CREATE OR REPLACE PACKAGE mip_bulk_load IS

  -- Author  : HARDYA
  -- Created : 08/01/2008 14:26:14
  -- Purpose : Bulk data load routines

  g_flow_id NUMBER DEFAULT nvl(apex_application.g_flow_id
                              ,155);

  PROCEDURE load_meters;
  PROCEDURE load_modules;
  PROCEDURE load_drawings;
  PROCEDURE load_bases;
  PROCEDURE load_costs;
  PROCEDURE load_postcodes;

  PROCEDURE report_err;

  PROCEDURE load_all;

END mip_bulk_load;
/
CREATE OR REPLACE PACKAGE BODY mip_bulk_load IS

  g_unknown_manufacturer_id parties.id%TYPE;

  PROCEDURE pl(p_in IN VARCHAR2) IS
  BEGIN
    dbms_output.put_line(to_char(SYSDATE
                                ,'DD-MON-YYYY HH24:MI:SS') || '-' || p_in);
    dbms_application_info.set_client_info(p_in);
  END pl;

  PROCEDURE get_dir_list(p_directory IN VARCHAR2) AS
    LANGUAGE JAVA NAME 'DirList.getList( java.lang.String )';

  FUNCTION file_available(p_filename  IN VARCHAR2
                         ,p_directory IN VARCHAR2 DEFAULT 'WEBMIP_BULK_LOAD')
    RETURN BOOLEAN IS
    l_exists      BOOLEAN;
    l_file_length NUMBER;
    l_block_size  NUMBER;
  BEGIN
    utl_file.fgetattr(location    => p_directory
                     ,filename    => p_filename
                     ,fexists     => l_exists
                     ,file_length => l_file_length
                     ,block_size  => l_block_size);
    RETURN l_exists;
  
  END file_available;

  PROCEDURE get_globals IS
  BEGIN
  
    BEGIN
      SELECT id
        INTO g_unknown_manufacturer_id
        FROM parties
       WHERE NAME = 'UNKNOWN'
         AND prty_type = 'MANU';
    EXCEPTION
      WHEN no_data_found THEN
        INSERT INTO parties
          (id
          ,NAME
          ,manu_ref
          ,description
          ,prty_type
          ,created_on
          ,created_by)
        VALUES
          (prty_seq.NEXTVAL
          ,'UNKNOWN'
          ,'UNKNOWN'
          ,'Unknown Manufacturer - inserted for bulk load'
          ,'MANU'
          ,SYSDATE
          ,USER)
        RETURNING id INTO g_unknown_manufacturer_id;
    END;
  
    BEGIN
      INSERT INTO meter_types
        (code
        ,description)
      VALUES
        ('UNKNOWN'
        ,'Unknown Meter Type - inserted for bulk load');
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
    BEGIN
      INSERT INTO housing_types
        (code
        ,description)
      VALUES
        ('UNKNOWN'
        ,'Unknown Housing Type - inserted for bulk load');
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
    BEGIN
      INSERT INTO regulator_types
        (code
        ,description)
      VALUES
        ('UNKNOWN'
        ,'Unknown Regulator Type - inserted for bulk load');
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
    BEGIN
      INSERT INTO filter_types
        (code
        ,description)
      VALUES
        ('UNKNOWN'
        ,'Unknown Filter Type - inserted for bulk load');
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
    BEGIN
      INSERT INTO relief_valve_types
        (code
        ,description)
      VALUES
        ('UNKNOWN'
        ,'Unknown Relief Valve Type - inserted for bulk load');
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
    BEGIN
      INSERT INTO slamshut_types
        (code
        ,description)
      VALUES
        ('UNKNOWN'
        ,'Unknown Slamshut Type - inserted for bulk load');
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
    BEGIN
      INSERT INTO service_pressure_types
        (code
        ,description)
      VALUES
        ('UNKNOWN'
        ,'Unknown Service Pressure Type - inserted for bulk load');
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
    BEGIN
      INSERT INTO drawings
        (code
        ,description)
      VALUES
        ('UNKNOWN'
        ,'Unknown Drawing - inserted for bulk load');
    EXCEPTION
      WHEN dup_val_on_index THEN
        NULL;
    END;
  
  END get_globals;

  PROCEDURE load_meters IS
  BEGIN
  
    pl('load_meters');
  
    IF NOT file_available('METERS.csv') THEN
      pl('METERS.csv file missing');
      RETURN;
    END IF;
    -- Meter Types
    INSERT INTO meter_types
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_meters'
         FROM (SELECT DISTINCT upper(field_3) AS code
                 FROM ext_meters
                WHERE field_3 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM meter_types));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': meter_types');
  
    -- Manufacturers
    INSERT INTO parties
      (id
      ,manu_ref
      ,description
      ,prty_type
      ,created_on
      ,created_by)
      (SELECT prty_seq.NEXTVAL
             ,manu_ref
             ,'Inserted for load_meters'
             ,'MANU'
             ,SYSDATE
             ,USER
         FROM (SELECT DISTINCT upper(field_4) AS manu_ref
                 FROM ext_meters
                WHERE field_4 IS NOT NULL)
        WHERE manu_ref NOT IN (SELECT manu_ref
                                 FROM parties
                                WHERE prty_type = 'MANU'));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': manufacturers');
  
    -- Drawings
    INSERT INTO drawings
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_meters'
         FROM (SELECT DISTINCT upper(field_2) AS code
                 FROM ext_meters
                WHERE field_2 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM drawings));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': drawings');
  
    MERGE INTO meters metr
    USING (SELECT upper(field_1) AS field_1
                 ,upper(field_2) AS field_2
                 ,upper(field_3) AS field_3
                 ,p.id AS field_4
                 ,field_5
                 ,field_6
                 ,field_7
                 ,field_8
                 ,field_9
                 ,field_10
                 ,CASE
                    WHEN field_11 IS NULL THEN
                     SYSDATE
                    ELSE
                     to_date(field_11
                            ,'dd/mm/yyyy')
                  END AS field_11
                 ,CASE
                    WHEN field_12 IS NULL THEN
                     NULL
                    ELSE
                     to_date(field_12
                            ,'dd/mm/yyyy')
                  END AS field_12
                 ,field_13
                 ,field_14
             FROM ext_meters
                 ,parties p
            WHERE p.manu_ref = field_4
              AND p.prty_type = 'MANU') e
    ON (e.field_1 = metr.code)
    WHEN MATCHED THEN
      UPDATE
         SET drwg_code  = e.field_2
            ,mety_code  = e.field_3
            ,prty_id    = e.field_4
            ,dim_a      = e.field_5
            ,dim_b      = e.field_6
            ,centres    = e.field_7
            ,dim_c      = e.field_8
            ,weight     = e.field_9
            ,qmax       = e.field_10
            ,valid_from = e.field_11
            ,valid_to   = e.field_12
            ,qnom       = e.field_13
            ,qmin       = e.field_14
    WHEN NOT MATCHED THEN
      INSERT
        (code
        ,drwg_code
        ,mety_code
        ,prty_id
        ,dim_a
        ,dim_b
        ,centres
        ,dim_c
        ,weight
        ,qmax
        ,valid_from
        ,valid_to
        ,qnom
        ,qmin)
      VALUES
        (e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_8
        ,e.field_9
        ,e.field_10
        ,e.field_11
        ,e.field_12
        ,e.field_13
        ,e.field_14) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': meters');
  END load_meters;

  PROCEDURE load_modules IS
  BEGIN
  
    pl('load_modules');
  
    IF NOT file_available('MODULES.csv') THEN
      pl('MODULES.csv file missing');
      RETURN;
    END IF;
  
    -- Add any missing foreign key objects
  
    -- Connection types
    INSERT INTO connection_types
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_modules'
         FROM (SELECT DISTINCT code
                 FROM (SELECT upper(field_2) AS code
                         FROM v_complete_ext_modules
                       UNION
                       SELECT upper(field_3)
                         FROM v_complete_ext_modules))
        WHERE code NOT IN (SELECT code
                             FROM connection_types));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': connection_types');
    -- Drawings
    INSERT INTO drawings
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_modules'
         FROM (SELECT DISTINCT upper(field_4) AS code
                 FROM v_complete_ext_modules
                WHERE field_4 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM drawings));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': drawings');
  
    -- Manufacturers
    INSERT INTO parties
      (id
      ,manu_ref
      ,description
      ,prty_type
      ,created_on
      ,created_by)
      (SELECT prty_seq.NEXTVAL
             ,manu_ref
             ,'Inserted for load_modules'
             ,'MANU'
             ,SYSDATE
             ,USER
         FROM (SELECT DISTINCT upper(field_30) AS manu_ref
                 FROM v_complete_ext_modules)
        WHERE manu_ref NOT IN (SELECT manu_ref
                                 FROM parties
                                WHERE prty_type = 'MANU'));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': manufacturers');
  
    -- Meters
    INSERT INTO meters
      (code
      ,mety_code
      ,prty_id
      ,dim_a
      ,dim_b
      ,dim_c
      ,centres
      ,weight
      ,qmax
      ,qmin
      ,qnom
      ,valid_from)
      (SELECT code
             ,'UNKNOWN'
             ,g_unknown_manufacturer_id
             ,999
             ,999
             ,999
             ,999
             ,999
             ,.999
             ,.999
             ,.999
             ,trunc(SYSDATE)
         FROM (SELECT DISTINCT nvl(upper(field_5)
                                  ,'UNKNOWN') AS code
                 FROM v_complete_ext_modules)
        WHERE code NOT IN (SELECT code
                             FROM meters));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': meters');
  
    -- Relief valves
    INSERT INTO relief_valves
      (code
      ,rvty_code
      ,relief_valve_size
      ,relief_valve_setting)
      (SELECT code
             ,'UNKNOWN'
             ,999
             ,999
         FROM (SELECT DISTINCT upper(field_6) AS code
                 FROM v_complete_ext_modules)
        WHERE code NOT IN (SELECT code
                             FROM relief_valves));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': relief_valves');
    -- Slamshut valves
    INSERT INTO slamshut_valves
      (code
      ,slty_code
      ,slamshut_size
      ,slamshut_setting)
      (SELECT code
             ,'UNKNOWN'
             ,999
             ,999
         FROM (SELECT DISTINCT upper(field_7) AS code
                 FROM v_complete_ext_modules)
        WHERE code NOT IN (SELECT code
                             FROM slamshut_valves));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': slamshut_valves');
  
    -- Service pressures
    INSERT INTO service_pressures
      (code
      ,svcpt_code
      ,description)
      (SELECT code
             ,'UNKNOWN'
             ,'Inserted for load_modules'
         FROM (SELECT DISTINCT upper(field_8) AS code
                 FROM v_complete_ext_modules)
        WHERE code NOT IN (SELECT code
                             FROM service_pressures));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': service_pressures');
  
    -- Bases
    INSERT INTO bases
      (code
      ,drwg_code
      ,description
      ,dim_a
      ,dim_b
      ,dim_c
      ,dim_d
      ,dim_e
      ,dim_f
      ,dim_g
      ,dim_h
      ,dim_i
      ,depth)
      (SELECT code
             ,'UNKNOWN'
             ,'Inserted for load_modules'
             ,999
             ,999
             ,999
             ,999
             ,999
             ,999
             ,999
             ,999
             ,999
             ,999
         FROM (SELECT DISTINCT upper(field_15) AS code
                 FROM v_complete_ext_modules
                WHERE field_15 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM bases));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': bases');
  
    -- Housings
    INSERT INTO housings
      (code
      ,drwg_code
      ,hoty_code
      ,prty_id
      ,description
      ,dim_l
      ,dim_w
      ,dim_h
      ,weight)
      (SELECT code
             ,'UNKNOWN'
             ,'UNKNOWN'
             ,g_unknown_manufacturer_id
             ,'Inserted for load_modules'
             ,999
             ,999
             ,999
             ,999
         FROM (SELECT DISTINCT upper(field_16) AS code
                 FROM v_complete_ext_modules
                WHERE field_16 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM housings));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': housings');
  
    -- Regulators
    INSERT INTO regulators
      (code
      ,rety_code
      ,description
      ,regulator_size
      ,lock_up)
      (SELECT code
             ,'UNKNOWN'
             ,'Inserted for load_modules'
             ,999
             ,999
         FROM (SELECT DISTINCT upper(field_25) AS code
                 FROM v_complete_ext_modules
                WHERE field_25 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM regulators));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': regulators');
  
    -- Filters
    INSERT INTO filters
      (code
      ,fity_code
      ,filter_size)
      (SELECT code
             ,'UNKNOWN'
             ,999
         FROM (SELECT DISTINCT upper(field_26) AS code
                 FROM v_complete_ext_modules
                WHERE field_26 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM filters));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': filters');
  
    -- Connection orientations
    INSERT INTO connection_orientations
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_modules'
         FROM (SELECT DISTINCT code
                 FROM (SELECT upper(field_27) AS code
                         FROM v_complete_ext_modules
                       UNION
                       SELECT upper(field_28)
                         FROM v_complete_ext_modules)
                WHERE code IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM connection_orientations));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': connection_orientations');
  
    MERGE INTO modules modu
    USING (SELECT upper(field_1) AS field_1
                 ,upper(field_2) AS field_2
                 ,upper(field_3) AS field_3
                 ,upper(field_4) AS field_4 -- drwg_code
                 ,upper(field_5) AS field_5
                 ,upper(field_6) AS field_6
                 ,upper(field_7) AS field_7
                 ,upper(field_8) AS field_8
                 ,field_9
                 ,field_10
                 ,field_11
                 ,field_12
                 ,field_13
                 ,field_14
                 ,upper(field_15) AS field_15 -- base
                 ,upper(field_16) AS field_16 -- housing
                 ,field_17
                 ,field_18
                 ,field_19
                 ,field_20
                 ,field_21
                 ,field_22
                 ,CASE
                    WHEN field_23 IS NULL THEN
                     trunc(SYSDATE)
                    ELSE
                     to_date(field_23
                            ,'dd/mm/yyyy')
                  END AS field_23
                 ,CASE
                    WHEN field_24 IS NULL THEN
                     NULL
                    ELSE
                     to_date(field_24
                            ,'dd/mm/yyyy')
                  END AS field_24
                 ,upper(field_25) AS field_25 -- regu_code
                 ,upper(field_26) AS field_26 -- fltr_code
                 ,upper(field_27) AS field_27 -- inlet connection orientation
                 ,upper(field_28) AS field_28 -- outlet ...
                 ,field_29
                 ,p.id AS field_30 -- MANU_REF
             FROM v_complete_ext_modules
                 ,parties p
            WHERE p.manu_ref = upper(field_30)
              AND p.prty_type = 'MANU') e
    ON (e.field_1 = modu.code)
    WHEN MATCHED THEN
      UPDATE
         SET outlet_cnty_code = e.field_2
            ,inlet_cnty_code  = e.field_3
            ,drwg_code        = e.field_4
            ,metr_code        = e.field_5
            ,reva_code        = e.field_6
            ,slva_code        = e.field_7
            ,svcp_code        = e.field_8
            ,weight           = e.field_9
            ,inlet_height     = e.field_10
            ,inlet_size       = e.field_11
            ,outlet_pressure  = e.field_12
            ,outlet_height    = e.field_13
            ,outlet_size      = e.field_14
            ,bas_code         = e.field_15
            ,hou_code         = e.field_16
            ,dim_a            = e.field_17
            ,dim_b            = e.field_18
            ,dim_c            = e.field_19
            ,dim_e            = e.field_20
            ,dim_h            = e.field_22
            ,valid_from       = e.field_23
            ,valid_to         = e.field_24
            ,regu_code        = e.field_25
            ,fltr_code        = e.field_26
            ,inlet_cnor_code  = e.field_27
            ,outlet_cnor_code = e.field_28
            ,lead_time        = e.field_29
            ,prty_id          = e.field_30
      
    
    WHEN NOT MATCHED THEN
      INSERT
        (code
        ,drwg_code
        ,fltr_code
        ,inlet_cnor_code
        ,inlet_cnty_code
        ,metr_code
        ,outlet_cnor_code
        ,outlet_cnty_code
        ,prty_id
        ,regu_code
        ,reva_code
        ,slva_code
        ,svcp_code
        ,lead_time
        ,weight
        ,inlet_height
        ,inlet_size
        ,outlet_pressure
        ,outlet_height
        ,outlet_size
        ,dim_a
        ,dim_b
        ,dim_c
        ,dim_d
        ,dim_e
        ,dim_h
        ,valid_from
        ,bas_code
        ,hou_code
        ,valid_to)
      VALUES
        (e.field_1
        ,e.field_4
        ,e.field_26
        ,e.field_27
        ,e.field_3
        ,e.field_5
        ,e.field_28
        ,e.field_2 -- outlet_cnty_code
        ,e.field_30
        ,e.field_25
        ,e.field_6
        ,e.field_7
        ,e.field_8
        ,e.field_29
        ,e.field_9
        ,e.field_10
        ,e.field_11 -- inlet size
        ,e.field_12
        ,e.field_13
        ,e.field_14
        ,e.field_17
        ,e.field_18
        ,e.field_19
        ,e.field_20
        ,e.field_21
        ,e.field_22
        ,e.field_23
        ,e.field_15
        ,e.field_16
        ,e.field_24) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': modules');
  
  END load_modules;

  PROCEDURE load_drawings IS
    l_directory_path     all_directories.directory_path%TYPE;
    l_uri                wwv_flow_files.NAME%TYPE;
    l_source_file        BFILE;
    l_source_file_length BINARY_INTEGER;
    l_blob               BLOB;
    l_success            BOOLEAN;
    l_load_count         NUMBER := 0;
  BEGIN
  
    pl('load_drawings');
  
    dbms_lob.createtemporary(lob_loc => l_blob
                            ,cache   => TRUE);
    dbms_lob.OPEN(lob_loc   => l_blob
                 ,open_mode => dbms_lob.lob_readwrite);
  
    SELECT directory_path
      INTO l_directory_path
      FROM all_directories
     WHERE directory_name = 'WEBMIP_BULK_LOAD';
  
    get_dir_list(l_directory_path);
  
    FOR l_rec IN (SELECT filename AS filename
                        ,upper(substr(filename
                                     ,1
                                     ,regexp_instr(filename
                                                  ,'.(jpg)|.(jpeg)$'
                                                  ,1
                                                  ,1
                                                  ,0
                                                  ,'i') - 1)) AS drwg_code
                        ,substr(filename
                               ,regexp_instr(filename
                                            ,'.(jpg)|.(jpeg)$'
                                            ,1
                                            ,1
                                            ,0
                                            ,'i') + 1) AS filename_suffix
                    FROM gtt_dir_list g
                   WHERE g.filetype = 'F'
                     AND regexp_like(g.filename
                                    ,'.(jpg)|.(jpeg)$'
                                    ,'i')) LOOP
    
      l_source_file        := bfilename('WEBMIP_BULK_LOAD'
                                       ,l_rec.filename);
      l_source_file_length := dbms_lob.getlength(l_source_file);
    
      dbms_lob.OPEN(file_loc  => l_source_file
                   ,open_mode => dbms_lob.lob_readonly);
    
      dbms_lob.loadfromfile(dest_lob => l_blob
                           ,src_lob  => l_source_file
                           ,amount   => l_source_file_length);
    
      dbms_lob.fileclose(file_loc => l_source_file);
    
      BEGIN
        SAVEPOINT this_drawing_savepoint;
        BEGIN
          INSERT INTO drawings
            (code
            ,description)
          VALUES
            (l_rec.drwg_code
            ,'Inserted for load_drawings');
        EXCEPTION
          WHEN dup_val_on_index THEN
            NULL;
        END;
      
        DELETE FROM wwv_flow_files
         WHERE NAME LIKE '%/' || l_rec.filename;
      
        DELETE FROM document_roles doro
         WHERE doro.drwg_code = l_rec.drwg_code;
      
        DELETE FROM documents doco
         WHERE doco.uri = '%/' || l_rec.filename;
      
        INSERT INTO wwv_flow_files
          (NAME
          ,title
          ,mime_type
          ,flow_id
          ,doc_size
          ,description
          ,blob_content)
        VALUES
          (sys_guid() || '/' || l_rec.filename
          ,'Drawing'
          ,'image/' || l_rec.filename_suffix
          ,apex_application.g_flow_id
          ,l_source_file_length
          ,'Bulk Loaded on ' ||
           to_char(SYSDATE
                  ,'DD-MON-YYYY HH24:MI:SS')
          ,l_blob)
        RETURNING NAME INTO l_uri;
      
        l_success := mip_files.set_file_association(p_uri         => l_uri
                                                   ,p_description => 'Drawing'
                                                   ,p_docu_type   => 'INDO'
                                                   ,p_rt_code     => 'HIGH DEFINITION DRAWING'
                                                   ,p_qute_id     => NULL
                                                   ,p_enqu_id     => NULL
                                                   ,p_drwg_code   => l_rec.drwg_code
                                                   ,p_doro_type   => 'DRRO');
        IF NOT l_success THEN
          ROLLBACK TO this_drawing_savepoint;
        ELSE
          l_load_count := l_load_count + 1;
        END IF;
      END;
    END LOOP;
  
    pl(l_load_count || ': drawings');
  END load_drawings;

  PROCEDURE load_bases IS
  BEGIN
  
    pl('load_bases');
  
    IF NOT file_available('BASES.csv') THEN
      pl('BASES.csv file missing');
      RETURN;
    END IF;
  
    -- Drawings
    INSERT INTO drawings
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_bases'
         FROM (SELECT DISTINCT upper(field_2) AS code
                 FROM ext_bases
                WHERE field_2 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM drawings));
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': drawings');
    MERGE INTO bases bas
    USING (SELECT upper(field_1) AS field_1
                 ,upper(field_2) AS field_2
                 ,field_3
                 ,field_4
                 ,field_5
                 ,field_6
                 ,field_7
                 ,field_8
                 ,field_9
                 ,field_10
                 ,field_11
                 ,field_12
                 ,field_13
             FROM ext_bases) e
    ON (e.field_1 = bas.code)
    WHEN MATCHED THEN
      UPDATE
         SET drwg_code   = e.field_2
            ,description = e.field_3
            ,dim_a       = e.field_4
            ,dim_b       = e.field_5
            ,dim_c       = e.field_6
            ,dim_d       = e.field_7
            ,dim_e       = e.field_8
            ,dim_f       = e.field_9
            ,dim_g       = e.field_10
            ,dim_h       = e.field_11
            ,dim_i       = e.field_12
            ,depth       = e.field_13
    WHEN NOT MATCHED THEN
      INSERT
        (code
        ,drwg_code
        ,description
        ,dim_a
        ,dim_b
        ,dim_c
        ,dim_d
        ,dim_e
        ,dim_f
        ,dim_g
        ,dim_h
        ,dim_i
        ,depth)
      VALUES
        (e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_8
        ,e.field_9
        ,e.field_10
        ,e.field_11
        ,e.field_12
        ,e.field_13) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': bases');
  END load_bases;

  PROCEDURE load_housings IS
  BEGIN
  
    pl('load_housings');
  
    IF NOT file_available('COSTS.csv') THEN
      pl('COSTS.csv file missing');
      RETURN;
    END IF;
  
    -- Manufacturers
    INSERT INTO parties
      (id
      ,manu_ref
      ,description
      ,prty_type
      ,created_on
      ,created_by)
      (SELECT prty_seq.NEXTVAL
             ,manu_ref
             ,'Inserted for load_housings'
             ,'MANU'
             ,SYSDATE
             ,USER
         FROM (SELECT DISTINCT upper(field_2) AS manu_ref
                 FROM ext_housings
                WHERE field_2 IS NOT NULL)
        WHERE manu_ref NOT IN (SELECT manu_ref
                                 FROM parties
                                WHERE prty_type = 'MANU'));
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': manufacturers');
  
    -- Drawings
    INSERT INTO drawings
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_housings'
         FROM (SELECT DISTINCT upper(field_3) AS code
                 FROM ext_housings
                WHERE field_3 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM drawings));
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': drawings');
  
    -- Housing Types
    INSERT INTO housing_types
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_housings'
         FROM (SELECT DISTINCT upper(field_4) AS code
                 FROM ext_housings
                WHERE field_4 IS NOT NULL)
        WHERE code NOT IN (SELECT code
                             FROM housing_types));
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': housing_types');
  
    MERGE INTO housings hou
    USING (SELECT upper(field_1) AS field_1
                 ,p.id AS field_2
                 ,upper(field_3) AS field_3
                 ,upper(field_4) AS field_4
                 ,field_5
                 ,field_6
                 ,field_7
                 ,field_8
                 ,field_9
             FROM ext_housings
                 ,parties p
            WHERE p.manu_ref = upper(field_2)
              AND p.prty_type = 'MANU') e
    ON (e.field_1 = hou.code)
    WHEN MATCHED THEN
      UPDATE
         SET prty_id     = e.field_2
            ,drwg_code   = e.field_3
            ,hoty_code   = e.field_4
            ,description = e.field_5
            ,dim_w       = e.field_6
            ,dim_h       = e.field_7
            ,dim_l       = e.field_8
            ,weight      = e.field_9
    WHEN NOT MATCHED THEN
      INSERT
        (code
        ,prty_id
        ,drwg_code
        ,hoty_code
        ,description
        ,dim_w
        ,dim_h
        ,dim_l
        ,weight)
      VALUES
        (e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_8
        ,e.field_9) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': housings');
  
  END load_housings;

  PROCEDURE load_costs IS
  BEGIN
    pl('load_costs');
  
    IF NOT file_available('COSTS.csv') THEN
      pl('COSTS.csv file missing');
      RETURN;
    END IF;
    --
    -- Additional Items
    --
    MERGE INTO costs cos
    USING (SELECT upper(field_1) AS field_1
                 ,field_2
                 ,field_3
                 ,field_4
                 ,CASE
                    WHEN field_5 IS NULL THEN
                     trunc(SYSDATE)
                    ELSE
                     to_date(field_5
                            ,'dd/mm/yyyy')
                  END AS field_5
                 ,CASE
                    WHEN field_6 IS NULL THEN
                     NULL
                    ELSE
                     to_date(field_6
                            ,'dd/mm/yyyy')
                  END AS field_6
                 ,upper(field_7) AS field_7
                 ,upper(field_16) AS field_16
             FROM ext_costs
            WHERE upper(field_7) = 'AICO') e
    ON (nvl(e.field_1, 'NULL') = nvl(cos.regi_code, 'NULL') AND e.field_16 = cos.adit_code AND e.field_5 = cos.valid_from AND e.field_7 = cos.cost_type)
    WHEN MATCHED THEN
      UPDATE
         SET selling_price = e.field_2
            ,cost_price    = e.field_3
            ,delivery_cost = e.field_4
            ,valid_to      = e.field_6
    WHEN NOT MATCHED THEN
      INSERT
        (id
        ,regi_code
        ,selling_price
        ,cost_price
        ,delivery_cost
        ,valid_from
        ,valid_to
        ,cost_type
        ,adit_code)
      VALUES
        (cost_seq.NEXTVAL
        ,e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_16) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': additional_items');
    --
    -- Base costs
    --
    MERGE INTO costs cos
    USING (SELECT upper(field_1) AS field_1
                 ,field_2
                 ,field_3
                 ,field_4
                 ,CASE
                    WHEN field_5 IS NULL THEN
                     trunc(SYSDATE)
                    ELSE
                     to_date(field_5
                            ,'dd/mm/yyyy')
                  END AS field_5
                 ,CASE
                    WHEN field_6 IS NULL THEN
                     NULL
                    ELSE
                     to_date(field_6
                            ,'dd/mm/yyyy')
                  END AS field_6
                 ,upper(field_7) AS field_7
                 ,upper(field_15) AS field_15
             FROM ext_costs
            WHERE upper(field_7) = 'BACO') e
    ON (nvl(e.field_1, 'NULL') = nvl(cos.regi_code, 'NULL') AND e.field_15 = cos.bas_code AND e.field_5 = cos.valid_from AND e.field_7 = cos.cost_type)
    WHEN MATCHED THEN
      UPDATE
         SET selling_price = e.field_2
            ,cost_price    = e.field_3
            ,delivery_cost = e.field_4
            ,valid_to      = e.field_6
    WHEN NOT MATCHED THEN
      INSERT
        (id
        ,regi_code
        ,selling_price
        ,cost_price
        ,delivery_cost
        ,valid_from
        ,valid_to
        ,cost_type
        ,bas_code)
      VALUES
        (cost_seq.NEXTVAL
        ,e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_15) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': bases');
    --
    -- Housing costs
    --
    MERGE INTO costs cos
    USING (SELECT upper(field_1) AS field_1
                 ,field_2
                 ,field_3
                 ,field_4
                 ,CASE
                    WHEN field_5 IS NULL THEN
                     trunc(SYSDATE)
                    ELSE
                     to_date(field_5
                            ,'dd/mm/yyyy')
                  END AS field_5
                 ,CASE
                    WHEN field_6 IS NULL THEN
                     NULL
                    ELSE
                     to_date(field_6
                            ,'dd/mm/yyyy')
                  END AS field_6
                 ,upper(field_7) AS field_7
                 ,upper(field_14) AS field_14
             FROM ext_costs
            WHERE upper(field_7) = 'HOCO') e
    ON (((e.field_1 = cos.regi_code) OR (e.field_1 IS NULL AND cos.regi_code IS NULL)) AND e.field_14 = cos.hou_code AND e.field_5 = cos.valid_from AND e.field_7 = cos.cost_type)
    WHEN MATCHED THEN
      UPDATE
         SET selling_price = e.field_2
            ,cost_price    = e.field_3
            ,delivery_cost = e.field_4
            ,valid_to      = e.field_6
    WHEN NOT MATCHED THEN
      INSERT
        (id
        ,regi_code
        ,selling_price
        ,cost_price
        ,delivery_cost
        ,valid_from
        ,valid_to
        ,cost_type
        ,hou_code)
      VALUES
        (cost_seq.NEXTVAL
        ,e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_14) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': housings');
    --
    -- Meter costs
    --
    MERGE INTO costs cos
    USING (SELECT upper(field_1) AS field_1
                 ,field_2
                 ,field_3
                 ,field_4
                 ,CASE
                    WHEN field_5 IS NULL THEN
                     trunc(SYSDATE)
                    ELSE
                     to_date(field_5
                            ,'dd/mm/yyyy')
                  END AS field_5
                 ,CASE
                    WHEN field_6 IS NULL THEN
                     NULL
                    ELSE
                     to_date(field_6
                            ,'dd/mm/yyyy')
                  END AS field_6
                 ,upper(field_7) AS field_7
                 ,upper(field_13) AS field_13
             FROM ext_costs
            WHERE upper(field_7) = 'MECO') e
    ON (nvl(e.field_1, 'NULL') = nvl(cos.regi_code, 'NULL') AND e.field_13 = cos.metr_code AND e.field_5 = cos.valid_from AND e.field_7 = cos.cost_type)
    WHEN MATCHED THEN
      UPDATE
         SET selling_price = e.field_2
            ,cost_price    = e.field_3
            ,delivery_cost = e.field_4
            ,valid_to      = e.field_6
    WHEN NOT MATCHED THEN
      INSERT
        (id
        ,regi_code
        ,selling_price
        ,cost_price
        ,delivery_cost
        ,valid_from
        ,valid_to
        ,cost_type
        ,metr_code)
      VALUES
        (cost_seq.NEXTVAL
        ,e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_13) log errors reject LIMIT unlimited;
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': meters');
  
    --
    -- Module costs
    --
    MERGE INTO costs cos
    USING (SELECT DISTINCT upper(field_1) AS field_1
                          ,field_2
                          ,field_3
                          ,field_4
                          ,CASE
                             WHEN field_5 IS NULL THEN
                              trunc(SYSDATE)
                             ELSE
                              to_date(field_5
                                     ,'dd/mm/yyyy')
                           END AS field_5
                          ,CASE
                             WHEN field_6 IS NULL THEN
                              NULL
                             ELSE
                              to_date(field_6
                                     ,'dd/mm/yyyy')
                           END AS field_6
                          ,upper(field_7) AS field_7
                          ,upper(field_8) AS field_8
                          ,upper(field_9) AS field_9
                          ,upper(field_10) AS field_10
                          ,upper(field_12) AS field_12
             FROM ext_costs
            WHERE upper(field_7) = 'MOCO') e
    ON (nvl(e.field_1, 'NULL') = nvl(cos.regi_code, 'NULL') AND e.field_12 = cos.modu_code AND e.field_5 = cos.valid_from AND e.field_7 = cos.cost_type)
    WHEN MATCHED THEN
      UPDATE
         SET selling_price = e.field_2
            ,cost_price    = e.field_3
            ,delivery_cost = e.field_4
            ,valid_to      = e.field_6
    WHEN NOT MATCHED THEN
      INSERT
        (id
        ,regi_code
        ,selling_price
        ,cost_price
        ,delivery_cost
        ,valid_from
        ,valid_to
        ,cost_type
        ,modu_code)
      VALUES
        (cost_seq.NEXTVAL
        ,e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_12) log errors reject LIMIT unlimited;
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': modules');
    --
    -- Labour costs
    --
    MERGE INTO costs cos
    USING (SELECT upper(field_1) AS field_1
                 ,field_2
                 ,field_3
                 ,field_4
                 ,CASE
                    WHEN field_5 IS NULL THEN
                     trunc(SYSDATE)
                    ELSE
                     to_date(field_5
                            ,'dd/mm/yyyy')
                  END AS field_5
                 ,CASE
                    WHEN field_6 IS NULL THEN
                     NULL
                    ELSE
                     to_date(field_6
                            ,'dd/mm/yyyy')
                  END AS field_6
                 ,upper(field_7) AS field_7
                 ,upper(field_8) AS field_8
                 ,upper(field_9) AS field_9
                 ,upper(field_10) AS field_10
                 ,upper(field_11) AS field_11
             FROM ext_costs
            WHERE upper(field_7) = 'LACO') e
    ON (nvl(e.field_1, 'NULL') = nvl(cos.regi_code, 'NULL') AND e.field_8 = cos.enty_code AND e.field_5 = cos.valid_from AND e.field_11 = cos.mety_code AND e.field_7 = cos.cost_type AND ((e. field_9 IS NULL AND cos.mesc_code IS NULL) OR (e.field_9 = cos.mesc_code)) AND ((e.field_10 IS NULL AND cos.svcpt_code IS NULL) OR (e.field_10 = cos.svcpt_code)))
    WHEN MATCHED THEN
      UPDATE
         SET selling_price = e.field_2
            ,cost_price    = e.field_3
            ,delivery_cost = e.field_4
            ,valid_to      = e.field_6
    WHEN NOT MATCHED THEN
      INSERT
        (id
        ,regi_code
        ,selling_price
        ,cost_price
        ,delivery_cost
        ,valid_from
        ,valid_to
        ,cost_type
        ,enty_code
        ,mesc_code
        ,svcpt_code
        ,mety_code)
      VALUES
        (cost_seq.NEXTVAL
        ,e.field_1
        ,e.field_2
        ,e.field_3
        ,e.field_4
        ,e.field_5
        ,e.field_6
        ,e.field_7
        ,e.field_8
        ,e.field_9
        ,e.field_10
        ,e.field_11) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': labour');
  
  END load_costs;

  PROCEDURE load_caveats IS
  BEGIN
    pl('load_caveats');
  
    IF NOT file_available('CAVEATS.csv') THEN
      pl('CAVEATS.csv file missing');
      RETURN;
    END IF;
    --
    -- Caveats
    --
    MERGE INTO caveat_texts
    USING (SELECT upper(field_1) AS field_1
                 ,upper(field_2) AS field_2
                 ,upper(field_3) AS field_3
                 ,upper(field_4) AS field_4
                 ,upper(field_5) AS field_5
                 ,field_6
             FROM ext_caveats) e
    ON (field_1 = document_position AND field_2 = enty_code AND field_3 = mety_code AND field_4 = svcpt_code AND ((field_5 = sort_order) OR (field_5 IS NULL AND sort_order IS NULL)))
    WHEN MATCHED THEN
      UPDATE
         SET text = field_6
    WHEN NOT MATCHED THEN
      INSERT
        (id
        ,document_position
        ,enty_code
        ,mety_code
        ,svcpt_code
        ,sort_order
        ,text)
      VALUES
        (cate_seq.NEXTVAL
        ,field_1
        ,field_2
        ,field_3
        ,field_4
        ,field_5
        ,field_6) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': caveat_texts');
  END load_caveats;

  PROCEDURE load_postcodes IS
  BEGIN
    pl('load_postcodes');
  
    IF NOT file_available('POSTCODES.csv') THEN
      pl('POSTCODES.csv file missing');
      RETURN;
    END IF;
  
    --
    -- Regions
    --
    INSERT INTO regions
      (code
      ,description)
      (SELECT code
             ,'Inserted for load_postcodes'
         FROM (SELECT DISTINCT upper(field_2) AS code
                 FROM ext_postcodes)
        WHERE code NOT IN (SELECT code
                             FROM regions));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': regions');
  
    DELETE FROM postcodes
     WHERE ROWID NOT IN (SELECT p.ROWID
                           FROM postcodes p
                               ,(SELECT CASE instr(field_1
                                              ,' ')
                                          WHEN 0 THEN
                                           upper(field_1)
                                          ELSE
                                           upper(substr(field_1
                                                       ,1
                                                       ,instr(field_1
                                                             ,' ') - 1))
                                        END AS outcode
                                       ,CASE instr(field_1
                                              ,' ')
                                          WHEN 0 THEN
                                           NULL
                                          ELSE
                                           substr(field_1
                                                 ,instr(field_1
                                                       ,' ') + 1)
                                        END AS incode
                                   FROM ext_postcodes) e
                          WHERE p.outcode = e.outcode
                            AND ((p.incode IS NULL AND e.incode IS NULL) OR
                                (p.incode = e.incode)));
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': postcodes - delete');
  
    --
    -- Postcodes
    --
    MERGE INTO postcodes p
    USING (SELECT CASE instr(field_1
                        ,' ')
                    WHEN 0 THEN
                     upper(field_1)
                    ELSE
                     upper(substr(field_1
                                 ,1
                                 ,instr(field_1
                                       ,' ') - 1))
                  END AS outcode
                 ,CASE instr(field_1
                        ,' ')
                    WHEN 0 THEN
                     NULL
                    ELSE
                     substr(field_1
                           ,instr(field_1
                                 ,' ') + 1)
                  END AS incode
                 ,upper(field_2) AS regi_code
             FROM ext_postcodes) e
    ON (e.outcode = p.outcode AND ((e.incode = p.incode) OR (e.incode IS NULL AND p.incode IS NULL)))
    WHEN MATCHED THEN
      UPDATE
         SET regi_code = regi_code
    WHEN NOT MATCHED THEN
      INSERT
        (id
        ,outcode
        ,incode
        ,regi_code)
      VALUES
        (poco_seq.NEXTVAL
        ,e.outcode
        ,e.incode
        ,e.regi_code) log errors reject LIMIT unlimited;
  
    pl(to_char(SQL%ROWCOUNT
              ,'FM099G999G999') || ': postcodes - merge');
  
  END load_postcodes;

  PROCEDURE init IS
    l_sgid NUMBER;
  BEGIN
  
    SELECT wwv_flow.get_current_flow_sgid(g_flow_id)
      INTO l_sgid
      FROM dual;
  
    pl('Security group id ' || l_sgid);
  
    wwv_flow_api.set_security_group_id(l_sgid);
    apex_application.g_flow_id := g_flow_id;
  
    DELETE FROM err$_bases;
    DELETE FROM err$_caveat_texts;
    DELETE FROM err$_costs;
    DELETE FROM err$_housings;
    DELETE FROM err$_meters;
    DELETE FROM err$_modules;
    DELETE FROM err$_parties;
    DELETE FROM err$_postcodes;
  
  END init;

  PROCEDURE report_err IS
  BEGIN
  
    pl('report_err');
  
    FOR l_rec IN (SELECT *
                    FROM err$_modules) LOOP
      pl('modules:' || l_rec.ora_err_mesg$ || ':' || l_rec.ora_err_rowid$ || ':' ||
         l_rec.ora_err_optyp$);
    END LOOP;
  
    FOR l_rec IN (SELECT *
                    FROM err$_meters) LOOP
      pl('meters:' || l_rec.ora_err_mesg$ || ':' || l_rec.ora_err_rowid$ || ':' ||
         l_rec.ora_err_optyp$);
    END LOOP;
  
    FOR l_rec IN (SELECT *
                    FROM err$_bases) LOOP
      pl('bases:' || l_rec.ora_err_mesg$ || ':' || l_rec.ora_err_rowid$ || ':' ||
         l_rec.ora_err_optyp$);
    END LOOP;
  
    FOR l_rec IN (SELECT *
                    FROM err$_housings) LOOP
      pl('housings:' || l_rec.ora_err_mesg$ || ':' || l_rec.ora_err_rowid$ || ':' ||
         l_rec.ora_err_optyp$);
    END LOOP;
  
    FOR l_rec IN (SELECT *
                    FROM err$_costs) LOOP
      pl('costs:' || l_rec.ora_err_mesg$ || ':' || l_rec.ora_err_rowid$ || ':' ||
         l_rec.ora_err_optyp$);
    END LOOP;
  
    FOR l_rec IN (SELECT *
                    FROM err$_caveat_texts) LOOP
      pl('caveats:' || l_rec.ora_err_mesg$ || ':' || l_rec.ora_err_rowid$ || ':' ||
         l_rec.ora_err_optyp$);
    END LOOP;
  
    FOR l_rec IN (SELECT *
                    FROM err$_postcodes) LOOP
      pl('postcodes:' || l_rec.ora_err_mesg$ || ':' ||
         l_rec.ora_err_rowid$ || ':' || l_rec.ora_err_optyp$);
    END LOOP;
  
    pl('report_err - complete');
  END report_err;

  PROCEDURE load_all IS
  BEGIN
  
    init;
    
    load_modules;
    load_meters;
    load_bases;
    load_housings;
    load_drawings;
    load_costs;
    load_caveats;
    load_postcodes;
  
    report_err;
  
  END load_all;
BEGIN
  -- Initialization
  get_globals;
END mip_bulk_load;
/
