BC Discrete Data Validation

[upsonp]

This issue is to dissect Biochem validation as part of issue #141

This is the validation code run by the biochem intake script on the Discrete Data Edits table (BCD). It can be found in the BioChem account on PTran under packages.

Edit: The authoritative version can be found on BiochemP

create or replace PACKAGE BODY BATCH_VALIDATION_PKG IS
FUNCTION CHECK_BATCH_MISSION_ERRORS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                    username_in_par IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
-- Create the cursor (check_mission_errs_cur) passing the batch_seq_in_par input parameter value at execution time.
-- This selects the columns for validatation.
   CURSOR check_mission_errs_cur(batch_in_cur_par BCBATCHES.batch_seq%TYPE) IS
      SELECT mission_edt_seq,data_center_code, sdate, edate, more_comment, process_flag, batch_seq, descriptor
      FROM BCMISSIONEDITS
      WHERE batch_seq = batch_in_cur_par;
   mission_edt_seq_v        BCMISSIONEDITS.mission_edt_seq%TYPE;
   data_center_code_v       BCMISSIONEDITS.data_center_code%TYPE;
   sdate_v          BCMISSIONEDITS.sdate%TYPE;
   edate_v          BCMISSIONEDITS.edate%TYPE;
   more_comment_v           BCMISSIONEDITS.more_comment%TYPE;
   process_flag_v       BCMISSIONEDITS.process_flag%TYPE;
   batch_seq_v          BCBATCHES.batch_seq%TYPE;
   descriptor_v         BCMISSIONEDITS.descriptor%TYPE;
   func_return_var          VARCHAR2(600);
   MISSION_VALIDATION_ERROR     EXCEPTION;
BEGIN
   OPEN check_mission_errs_cur(batch_seq_in_par);
   LOOP
      FETCH check_mission_errs_cur INTO mission_edt_seq_v, data_center_code_v, sdate_v,
       edate_v, more_comment_v, process_flag_v, batch_seq_v, descriptor_v;
      EXIT WHEN (check_mission_errs_cur%NOTFOUND);
      -- Loop through each retrieved record in the cursor and call the MISSION_EVENT_REC_VALID_PKG.MISSIONEDITS_VALIDATE_FORM
      -- funtion. Store the return value in the func_return_var variable.
      func_return_var := MISSION_EVENT_REC_VALID_PKG.MISSIONEDITS_VALIDATE_FORM(mission_edt_seq_v,
                            data_center_code_v,
                            batch_seq_v, to_char(sdate_v,'DD-MON-YYYY'),
                            to_char(edate_v, 'DD-MON-YYYY'),
                            more_comment_v, username_in_par, descriptor_v);
      -- Update the BCMISSIONEDITS.process_flag column with either 'ECN' or 'ERR' depending on the value of the
      -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
      -- error which fired in the called procedure, therefore RAISE the MISSION_VALIDATION_ERROR exception.
      IF func_return_var = 'ECN' THEN
         UPDATE BCMISSIONEDITS
         SET process_flag = 'ECN'
         WHERE mission_edt_seq = mission_edt_seq_v;
      ELSIF func_return_var = 'ERR' THEN
         UPDATE BCMISSIONEDITS
         SET process_flag = 'ERR'
         WHERE mission_edt_seq = mission_edt_seq_v;
      ELSE
         RAISE MISSION_VALIDATION_ERROR;
      END IF;
   END LOOP;
   CLOSE check_mission_errs_cur;
   RETURN 'OK';
EXCEPTION
   -- This exception is RAISED when the called procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in the called procedure out
   -- to the forms front end and display a message to the user.
   WHEN MISSION_VALIDATION_ERROR THEN
      IF check_mission_errs_cur%ISOPEN THEN
         CLOSE check_mission_errs_cur;
      END IF;
      RETURN func_return_var;
   -- This exception is RAISED when this procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in this procedure out
   -- to the forms front end and display a message to the user. The function name is included in the error message
   -- to easily identify the cause of the error.
   WHEN OTHERS THEN
      IF check_mission_errs_cur%ISOPEN THEN
         CLOSE check_mission_errs_cur;
      END IF;
      RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_MISSION_ERRORS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_EVENT_ERRORS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                    username_in_par IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
-- Create the cursor (validate_events_cur) passing the batch_seq_in_par input parameter value at execution time.
-- This selects the columns for validatation.
CURSOR validate_events_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE) IS
   SELECT event_edt_seq, mission_edt_seq, data_center_code, sdate, edate, stime, etime, min_lat, max_lat, min_lon, max_lon,
      UTC_offset, more_comment, process_flag, batch_seq, collector_event_id
   FROM BCEVENTEDITS
   WHERE batch_seq = batch_seq_cur_par;
   event_edt_seq_v      BCEVENTEDITS.event_edt_seq%TYPE;
   mission_edt_seq_v        BCEVENTEDITS.mission_edt_seq%TYPE;
   data_center_code_v       BCEVENTEDITS.data_center_code%TYPE;
   sdate_v          BCEVENTEDITS.sdate%TYPE;
   edate_v          BCEVENTEDITS.edate%TYPE;
   stime_v          BCEVENTEDITS.stime%TYPE;
   etime_v          BCEVENTEDITS.etime%TYPE;
   min_lat_v            BCEVENTEDITS.min_lat%TYPE;
   max_lat_v            BCEVENTEDITS.max_lat%TYPE;
   min_lon_v            BCEVENTEDITS.min_lon%TYPE;
   max_lon_v            BCEVENTEDITS.max_lon%TYPE;
   UTC_offset_v         BCEVENTEDITS.UTC_offset%TYPE;
   more_comment_v       BCEVENTEDITS.more_comment%TYPE;
   process_flag_v       BCEVENTEDITS.process_flag%TYPE;
   batch_seq_v          BCEVENTEDITS.batch_seq%TYPE;
   func_return_var          VARCHAR2(600);
   EVENT_VALIDATION_ERROR   EXCEPTION;
   collector_event_id_var   BCEVENTEDITS.collector_event_id%TYPE;
BEGIN
   OPEN validate_events_cur(batch_seq_in_par);
   LOOP
      FETCH validate_events_cur INTO event_edt_seq_v, mission_edt_seq_v, data_center_code_v, sdate_v, edate_v, stime_v, etime_v,
                min_lat_v, max_lat_v, min_lon_v, max_lon_v, UTC_offset_v, more_comment_v,
                process_flag_v, batch_seq_v, collector_event_id_var;
      EXIT WHEN (validate_events_cur %NOTFOUND);
      -- Loop through each retrieved record in the cursor and call the MISSION_EVENT_REC_VALID_PKG.EVENTEDITS_VALIDATE_FORM
      -- funtion. Store the return value in the func_return_var variable.
      func_return_var := MISSION_EVENT_REC_VALID_PKG.EVENTEDITS_VALIDATE_FORM(event_edt_seq_v, mission_edt_seq_v, data_center_code_v,
                        batch_seq_v, TO_CHAR(sdate_v,'DD-MON-YYYY'), TO_CHAR(edate_v,'DD-MON-YYYY'),
                    stime_v, etime_v, min_lat_v, max_lat_v, min_lon_v, max_lon_v,
                    UTC_offset_v, more_comment_v, username_in_par, collector_event_id_var);
      -- Update the BCEVENTEDITS.process_flag column with either 'ECN' or 'ERR' depending on the value of the
      -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
      -- error which fired in the called procedure, therefore RAISE the EVENT_VALIDATION_ERROR exception.
      IF func_return_var = 'ECN' THEN
         UPDATE BCEVENTEDITS
         SET process_flag = 'ECN'
         WHERE event_edt_seq = event_edt_seq_v;
      ELSIF func_return_var = 'ERR' THEN
         UPDATE BCEVENTEDITS
         SET process_flag = 'ERR'
         WHERE event_edt_seq = event_edt_seq_v;
      ELSE
         RAISE EVENT_VALIDATION_ERROR;
      END IF;
   END LOOP;
   CLOSE validate_events_cur;
   RETURN 'OK';
EXCEPTION
   -- This exception is RAISED when the called procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in the called procedure out
   -- to the forms front end and display a message to the user.
   WHEN EVENT_VALIDATION_ERROR THEN
      IF validate_events_cur%ISOPEN THEN
         CLOSE validate_events_cur;
      END IF;
      RETURN func_return_var;
   -- This exception is RAISED when this procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in this procedure out
   -- to the forms front end and display a message to the user. The function name is included in the error message
   -- to easily identify the cause of the error.
   WHEN OTHERS THEN
      IF validate_events_cur%ISOPEN THEN
         CLOSE validate_events_cur;
      END IF;
      RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_EVENT_ERRORS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_DISHEDR_ERRORS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                    username_in_par IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
-- Create the cursor (validate_dishedr_cur) passing the batch_seq_in_par input parameter value at execution time.
-- This selects the columns for validatation.
   CURSOR validate_dishedr_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE) IS
      SELECT dis_headr_edt_seq, event_edt_seq, activity_edt_seq, data_center_code, gear_seq, sdate, edate, stime, etime, time_qc_code, slat, elat,
         slon, elon, position_qc_code, start_depth, end_depth, sounding, collector_sample_id, process_flag, batch_seq
      FROM BCDISCRETEHEDREDITS
      WHERE batch_seq = batch_seq_cur_par;
   dis_headr_edt_seq_v      BCDISCRETEHEDREDITS.dis_headr_edt_seq%TYPE;
   event_edt_seq_v      BCDISCRETEHEDREDITS.event_edt_seq%TYPE;
   activity_edt_seq_v       BCDISCRETEHEDREDITS.activity_edt_seq%TYPE;
   data_center_code_v       BCDISCRETEHEDREDITS.data_center_code%TYPE;
   gear_seq_v           BCDISCRETEHEDREDITS.gear_seq%TYPE;
   sdate_v          BCDISCRETEHEDREDITS.sdate%TYPE;
   edate_v          BCDISCRETEHEDREDITS.edate%TYPE;
   stime_v          BCDISCRETEHEDREDITS.stime%TYPE;
   etime_v          BCDISCRETEHEDREDITS.etime%TYPE;
   time_qc_code_v       BCDISCRETEHEDREDITS.time_qc_code%TYPE;
   slat_v           BCDISCRETEHEDREDITS.slat%TYPE;
   elat_v           BCDISCRETEHEDREDITS.elat%TYPE;
   slon_v           BCDISCRETEHEDREDITS.slon%TYPE;
   elon_v           BCDISCRETEHEDREDITS.elon%TYPE;
   position_qc_code_v       BCDISCRETEHEDREDITS.position_qc_code%TYPE;
   start_depth_v        BCDISCRETEHEDREDITS.start_depth%TYPE;
   end_depth_v          BCDISCRETEHEDREDITS.end_depth%TYPE;
   collector_sample_id_v    BCDISCRETEHEDREDITS.collector_sample_id%TYPE;
   sounding_v           BCDISCRETEHEDREDITS.sounding%TYPE;
   process_flag_v       BCDISCRETEHEDREDITS.process_flag%TYPE;
   batch_seq_v          BCDISCRETEHEDREDITS.batch_seq%TYPE;
   func_return_var          VARCHAR2(600);
   HEADER_VALIDATION_ERROR  EXCEPTION;
BEGIN
   OPEN validate_dishedr_cur(batch_seq_in_par);
   LOOP
      FETCH validate_dishedr_cur INTO dis_headr_edt_seq_v, event_edt_seq_v, activity_edt_seq_v,
            data_center_code_v, gear_seq_v,
            sdate_v, edate_v, stime_v, etime_v, time_qc_code_v, slat_v, elat_v, slon_v,
            elon_v, position_qc_code_v, start_depth_v, end_depth_v, sounding_v, collector_sample_id_v,
            process_flag_v, batch_seq_v;
      EXIT WHEN (validate_dishedr_cur %NOTFOUND);
     -- Loop through each retrieved record in the cursor and call the DISCRETE_REC_VALID_PKG.VALIDATE_DISCRETEHEDREDITS_FRM
      -- funtion. Store the return value in the func_return_var variable.
      func_return_var := DISCRETE_REC_VALID_PKG.VALIDATE_DISCRETEHEDREDITS_FRM(dis_headr_edt_seq_v,
                        event_edt_seq_v, activity_edt_seq_v, data_center_code_v,
                        batch_seq_in_par, TO_CHAR(sdate_v, 'DD-MON-YYYY'),
                        TO_CHAR(edate_v, 'DD-MON-YYYY'), stime_v, etime_v, slat_v, elat_v,
                        slon_v, elon_v, time_qc_code_v, position_qc_code_v,
                        start_depth_v, end_depth_v, sounding_v,
                        gear_seq_v, collector_sample_id_v, username_in_par);
      -- Update the BCDISCRETEHEDREDITS.process_flag column with either 'ECN' or 'ERR' depending on the value of the
      -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
      -- error which fired in the called procedure, therefore RAISE the HEADER_VALIDATION_ERROR exception.
      IF func_return_var = 'ECN' THEN
         UPDATE BCDISCRETEHEDREDITS
         SET process_flag = 'ECN'
         WHERE dis_headr_edt_seq = dis_headr_edt_seq_v;
      ELSIF func_return_var = 'ERR' THEN
         UPDATE BCDISCRETEHEDREDITS
         SET process_flag = 'ERR'
         WHERE dis_headr_edt_seq = dis_headr_edt_seq_v;
      ELSE
         RAISE HEADER_VALIDATION_ERROR;
      END IF;
   END LOOP;
   CLOSE validate_dishedr_cur;
   RETURN 'OK';
EXCEPTION
   -- This exception is RAISED when the called procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in the called procedure out
   -- to the forms front end and display a message to the user.
   WHEN HEADER_VALIDATION_ERROR THEN
      IF validate_dishedr_cur%ISOPEN THEN
         CLOSE validate_dishedr_cur;
      END IF;
      RETURN func_return_var;
   -- This exception is RAISED when this procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in this procedure out
   -- to the forms front end and display a message to the user. The function name is included in the error message
   -- to easily identify the cause of the error.
   WHEN OTHERS THEN
      IF validate_dishedr_cur%ISOPEN THEN
         CLOSE validate_dishedr_cur;
      END IF;
      RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_DISHEDR_ERRORS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_DISDETAIL_ERRORS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                    username_in_par IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
-- Create the cursor (validate_discrete_detail_cur) passing the batch_seq_in_par input parameter value at execution time.
-- This selects the columns for validatation.
   CURSOR validate_discrete_detail_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE) IS
      SELECT dis_detail_edt_seq, dis_headr_edt_seq, data_center_code, data_type_seq, data_value,
             averaged_data, data_qc_code, process_flag, batch_seq, collector_sample_id
      FROM BCDISCRETEDTAILEDITS
      WHERE batch_seq = batch_seq_cur_par;
   dis_detail_edt_seq_v     BCDISCRETEDTAILEDITS.dis_detail_edt_seq%TYPE;
   dis_headr_edt_seq_v      BCDISCRETEDTAILEDITS.dis_headr_edt_seq%TYPE;
   data_center_code_v       BCDISCRETEDTAILEDITS.data_center_code%TYPE;
   data_type_seq_v      BCDISCRETEDTAILEDITS.data_type_seq%TYPE;
   data_value_v         BCDISCRETEDTAILEDITS.data_value%TYPE;
   averaged_data_v      BCDISCRETEDTAILEDITS.averaged_data%TYPE;
   data_qc_code_v       BCDISCRETEDTAILEDITS.data_qc_code%TYPE;
   process_flag_v       BCDISCRETEDTAILEDITS.process_flag%TYPE;
   batch_seq_v          BCDISCRETEDTAILEDITS.batch_seq%TYPE;
   collector_sample_id_v    BCDISCRETEDTAILEDITS.collector_sample_id%TYPE;
   func_return_var          VARCHAR2(600);
   DETAIL_VALIDATION_ERROR  EXCEPTION;
BEGIN
   OPEN validate_discrete_detail_cur(batch_seq_in_par);
   LOOP
      FETCH validate_discrete_detail_cur INTO dis_detail_edt_seq_v, dis_headr_edt_seq_v,
        data_center_code_v,
        data_type_seq_v, data_value_v, averaged_data_v, data_qc_code_v,
        process_flag_v, batch_seq_v, collector_sample_id_v;
      EXIT WHEN (validate_discrete_detail_cur %NOTFOUND);
      -- Loop through each retrieved record in the cursor and call the DISCRETE_REC_VALID_PKG.VALIDATE_DISCRETEDTAILEDIT_FRM
      -- funtion. Store the return value in the func_return_var variable.
      func_return_var := DISCRETE_REC_VALID_PKG.VALIDATE_DISCRETEDTAILEDIT_FRM(dis_detail_edt_seq_v, dis_headr_edt_seq_v, data_center_code_v,
                        batch_seq_in_par, data_type_seq_v, data_value_v,
                                            averaged_data_v, data_qc_code_v, collector_sample_id_v, username_in_par);
      -- Update the BCDISCRETEDTAILEDITS.process_flag column with either 'ECN' or 'ERR' depending on the value of the
      -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
      -- error which fired in the called procedure, therefore RAISE the DETAIL_VALIDATION_ERROR exception.
      IF func_return_var = 'ECN' THEN
         UPDATE BCDISCRETEDTAILEDITS
         SET process_flag = 'ECN'
         WHERE dis_detail_edt_seq = dis_detail_edt_seq_v;
      ELSIF func_return_var = 'ERR' THEN
         UPDATE BCDISCRETEDTAILEDITS
         SET process_flag = 'ERR'
         WHERE dis_detail_edt_seq = dis_detail_edt_seq_v;
      ELSE
         RAISE DETAIL_VALIDATION_ERROR;
      END IF;
   END LOOP;
   CLOSE validate_discrete_detail_cur;
   RETURN 'OK';
EXCEPTION
   -- This exception is RAISED when the called procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in the called procedure out
   -- to the forms front end and display a message to the user.
   WHEN DETAIL_VALIDATION_ERROR THEN
      IF validate_discrete_detail_cur%ISOPEN THEN
         CLOSE validate_discrete_detail_cur;
      END IF;
      RETURN func_return_var;
   -- This exception is RAISED when this procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in this procedure out
   -- to the forms front end and display a message to the user. The function name is included in the error message
   -- to easily identify the cause of the error.
   WHEN OTHERS THEN
      IF validate_discrete_detail_cur%ISOPEN THEN
         CLOSE validate_discrete_detail_cur;
      END IF;
      RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_DISDETAIL_ERRORS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_DISREPLIC_ERRORS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                    username_in_par IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
-- Create the cursor (val_discrete_replicate_cur) passing the batch_seq_in_par input parameter value at execution time.
-- This selects the columns for validatation.
   CURSOR val_discrete_replicate_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE) IS
      SELECT dis_repl_edt_seq, dis_detail_edt_seq, data_center_code, data_type_seq, data_value,
         data_qc_code, process_flag, batch_seq, collector_sample_id
      FROM BCDISREPLICATEDITS
      WHERE batch_seq = batch_seq_cur_par;
   dis_repl_edt_seq_v       BCDISREPLICATEDITS.dis_repl_edt_seq%TYPE;
   dis_detail_edt_seq_v     BCDISREPLICATEDITS.dis_detail_edt_seq%TYPE;
   data_center_code_v       BCDISREPLICATEDITS.data_center_code%TYPE;
   data_type_seq_v      BCDISREPLICATEDITS.data_type_seq%TYPE;
   data_value_v         BCDISREPLICATEDITS.data_value%TYPE;
   data_qc_code_v       BCDISREPLICATEDITS.data_qc_code%TYPE;
   process_flag_v       BCDISREPLICATEDITS.process_flag%TYPE;
   batch_seq_v          BCDISREPLICATEDITS.batch_seq%TYPE;
   collector_sample_id_v    BCDISREPLICATEDITS.collector_sample_id%TYPE;
   func_return_var          VARCHAR2(600);
   REPLICATE_VALIDATION_ERROR   EXCEPTION;
BEGIN
   OPEN val_discrete_replicate_cur(batch_seq_in_par);
   LOOP
      FETCH val_discrete_replicate_cur INTO dis_repl_edt_seq_v, dis_detail_edt_seq_v, data_center_code_v,
        data_type_seq_v, data_value_v, data_qc_code_v,
        process_flag_v, batch_seq_v, collector_sample_id_v;
      EXIT WHEN (val_discrete_replicate_cur%NOTFOUND);
      -- Loop through each retrieved record in the cursor and call the DISCRETE_REC_VALID_PKG.VALIDATE_DISREPLICATEDIT_FRM
      -- funtion. Store the return value in the func_return_var variable.
      func_return_var := DISCRETE_REC_VALID_PKG.VALIDATE_DISREPLICATEDIT_FRM(dis_repl_edt_seq_v,  dis_detail_edt_seq_v, data_center_code_v,
                        batch_seq_in_par, data_type_seq_v, data_value_v,
                                            NULL, data_qc_code_v,
                        collector_sample_id_v, username_in_par);
      -- Update the BCDISREPLICATEDITS.process_flag column with either 'ECN' or 'ERR' depending on the value of the
      -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
      -- error which fired in the called procedure, therefore RAISE the REPLICATE_VALIDATION_ERROR exception.
      IF func_return_var = 'ECN' THEN
         UPDATE BCDISREPLICATEDITS
         SET process_flag = 'ECN'
         WHERE dis_repl_edt_seq = dis_repl_edt_seq_v;
      ELSIF func_return_var = 'ERR' THEN
         UPDATE BCDISREPLICATEDITS
         SET process_flag = 'ERR'
         WHERE dis_repl_edt_seq = dis_repl_edt_seq_v;
      ELSE
         RAISE REPLICATE_VALIDATION_ERROR;
      END IF;
   END LOOP;
   CLOSE val_discrete_replicate_cur;
   RETURN 'OK';
EXCEPTION
   -- This exception is RAISED when the called procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in the called procedure out
   -- to the forms front end and display a message to the user.
   WHEN REPLICATE_VALIDATION_ERROR THEN
      IF val_discrete_replicate_cur%ISOPEN THEN
         CLOSE val_discrete_replicate_cur;
      END IF;
      RETURN func_return_var;
   -- This exception is RAISED when this procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in this procedure out
   -- to the forms front end and display a message to the user. The function name is included in the error message
   -- to easily identify the cause of the error.
   WHEN OTHERS THEN
      IF val_discrete_replicate_cur%ISOPEN THEN
         CLOSE val_discrete_replicate_cur;
      END IF;
      RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_DISREPLIC_ERRORS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_PLANK_HEDR_ERRORS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                    username_in_par IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
-- Create the cursor (plankton_hedredits_errs_cur) passing the batch_seq_in_par input parameter value at execution time.
-- This selects the columns for validatation.
   CURSOR plankton_hedredits_errs_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE)IS
      SELECT pl_headr_edt_seq, event_edt_seq, activity_edt_seq, data_center_code, gear_seq, sdate, edate, stime, etime,
         phase_of_daylight, slat, elat, slon, elon, time_qc_code, position_qc_code,
         start_depth, end_depth, sounding, volume_method_seq,
         large_plankton_removed, collection_method_seq, collector_sample_id, procedure_seq, preservation_seq,
         storage_seq, meters_sqd_flag, process_flag, batch_seq
      FROM BCPLANKTNHEDREDITS
      WHERE batch_seq = batch_seq_cur_par;
   pl_headr_edt_seq_v       BCPLANKTNHEDREDITS.pl_headr_edt_seq%TYPE;
   event_edt_seq_v      BCPLANKTNHEDREDITS.event_edt_seq%TYPE;
   activity_edt_seq_v       BCPLANKTNHEDREDITS.activity_edt_seq%TYPE;
   data_center_code_v       BCPLANKTNHEDREDITS.data_center_code%TYPE;
   gear_seq_v           BCPLANKTNHEDREDITS.gear_seq%TYPE;
   sdate_v          BCPLANKTNHEDREDITS.sdate%TYPE;
   edate_v          BCPLANKTNHEDREDITS.edate%TYPE;
   stime_v          BCPLANKTNHEDREDITS.stime%TYPE;
   etime_v          BCPLANKTNHEDREDITS.etime%TYPE;
   phase_of_daylight_v      BCPLANKTNHEDREDITS.phase_of_daylight%TYPE;
   slat_v           BCPLANKTNHEDREDITS.slat%TYPE;
   elat_v           BCPLANKTNHEDREDITS.elat%TYPE;
   slon_v           BCPLANKTNHEDREDITS.slon%TYPE;
   elon_v           BCPLANKTNHEDREDITS.elon%TYPE;
   time_qc_code_v       BCPLANKTNHEDREDITS.time_qc_code%TYPE;
   position_qc_code_v       BCPLANKTNHEDREDITS.position_qc_code%TYPE;
   start_depth_v        BCPLANKTNHEDREDITS.start_depth%TYPE;
   end_depth_v          BCPLANKTNHEDREDITS.end_depth%TYPE;
   sounding_v           BCPLANKTNHEDREDITS.sounding%TYPE;
   volume_method_seq_v      BCPLANKTNHEDREDITS.volume_method_seq%TYPE;
   large_plankton_removed_v BCPLANKTNHEDREDITS.large_plankton_removed%TYPE;
   collection_method_seq_v  BCPLANKTNHEDREDITS.collection_method_seq%TYPE;
   procedure_seq_v      BCPLANKTNHEDREDITS.procedure_seq%TYPE;
   preservation_seq_v       BCPLANKTNHEDREDITS.preservation_seq%TYPE;
   storage_seq_v        BCPLANKTNHEDREDITS.storage_seq%TYPE;
   meters_sqd_flag_v        BCPLANKTNHEDREDITS.meters_sqd_flag%TYPE;
   process_flag_v       BCPLANKTNHEDREDITS.process_flag%TYPE;
   batch_seq_v          BCPLANKTNHEDREDITS.batch_seq%TYPE;
   collector_sample_id_v    BCPLANKTNHEDREDITS.collector_sample_id%TYPE;
   func_return_var          VARCHAR2(600);
   HEADER_VALIDATION_ERROR      EXCEPTION;
BEGIN
   OPEN plankton_hedredits_errs_cur(batch_seq_in_par);
   LOOP
      FETCH plankton_hedredits_errs_cur INTO pl_headr_edt_seq_v, event_edt_seq_v, activity_edt_seq_v,
        data_center_code_v, gear_seq_v,
        sdate_v, edate_v, stime_v, etime_v, phase_of_daylight_v, slat_v, elat_v, slon_v,
        elon_v, time_qc_code_v, position_qc_code_v, start_depth_v, end_depth_v,
        sounding_v, volume_method_seq_v, large_plankton_removed_v, collection_method_seq_v,
        collector_sample_id_v, procedure_seq_v, preservation_seq_v, storage_seq_v, meters_sqd_flag_v, process_flag_v,
        batch_seq_v;
      EXIT WHEN (plankton_hedredits_errs_cur%NOTFOUND);
      -- Loop through each retrieved record in the cursor and call the PLANKTON_REC_VALID_PKG.VALIDATE_BCPLANKTNHEDREDIT_FRM
      -- funtion. Store the return value in the func_return_var variable.
      func_return_var := PLANKTON_REC_VALID_PKG.VALIDATE_BCPLANKTNHEDREDIT_FRM(pl_headr_edt_seq_v,
                        event_edt_seq_v, activity_edt_seq_v, data_center_code_v,
                        batch_seq_in_par, TO_CHAR(sdate_v,'DD-MON-YYYY'),
                        TO_CHAR(edate_v, 'DD-MON-YYYY'), stime_v, etime_v, slat_v,
                            elat_v, slon_v, elon_v, time_qc_code_v, position_qc_code_v,
                        start_depth_v, end_depth_v, sounding_v, gear_seq_v, phase_of_daylight_v,
                        volume_method_seq_v, large_plankton_removed_v, collection_method_seq_v, collector_sample_id_v,
                        procedure_seq_v, preservation_seq_v, storage_seq_v,
                        meters_sqd_flag_v, username_in_par);
      -- Update the BCPLANKTNHEDREDITS.process_flag column with either 'ECN' or 'ERR' depending on the value of the
      -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
      -- error which fired in the called procedure, therefore RAISE the HEADER_VALIDATION_ERROR exception.
      IF func_return_var = 'ECN' THEN
         UPDATE BCPLANKTNHEDREDITS
         SET process_flag = 'ECN'
         WHERE pl_headr_edt_seq = pl_headr_edt_seq_v;
      ELSIF func_return_var = 'ERR' THEN
         UPDATE BCPLANKTNHEDREDITS
         SET process_flag = 'ERR'
         WHERE pl_headr_edt_seq = pl_headr_edt_seq_v;
      ELSE
         RAISE HEADER_VALIDATION_ERROR;
      END IF;
   END LOOP;
   CLOSE plankton_hedredits_errs_cur;
   RETURN 'OK';
EXCEPTION
   -- This exception is RAISED when the called procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in the called procedure out
   -- to the forms front end and display a message to the user.
   WHEN HEADER_VALIDATION_ERROR THEN
      IF plankton_hedredits_errs_cur%ISOPEN THEN
         CLOSE plankton_hedredits_errs_cur;
      END IF;
      RETURN func_return_var;
   -- This exception is RAISED when this procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in this procedure out
   -- to the forms front end and display a message to the user. The function name is included in the error message
   -- to easily identify the cause of the error.
   WHEN OTHERS THEN
      IF plankton_hedredits_errs_cur%ISOPEN THEN
         CLOSE plankton_hedredits_errs_cur;
      END IF;
      RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_PLANK_HEDR_ERRORS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_PLANK_GENERL_ERRS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                    username_in_par IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
-- Create the cursor (plankton_general_errs_cur) passing the batch_seq_in_par input parameter value at execution time.
-- This selects the columns for validatation.
   CURSOR plankton_general_errs_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE) IS
      SELECT pl_general_edt_seq, pl_headr_edt_seq, data_center_code, national_taxonomic_seq, life_history_seq,
         trophic_seq, sex_seq, count_pct, presence, process_flag, batch_seq, source, counts, wet_weight,
         dry_weight, bio_volume, min_sieve, max_sieve
      FROM BCPLANKTNGENERLEDITS
      WHERE batch_seq = batch_seq_cur_par;
   pl_general_edt_seq_v     BCPLANKTNGENERLEDITS.pl_general_edt_seq%TYPE;
   pl_headr_edt_seq_v       BCPLANKTNGENERLEDITS.pl_headr_edt_seq%TYPE;
   data_center_code_v       BCPLANKTNGENERLEDITS.data_center_code%TYPE;
   national_taxonomic_seq_v BCPLANKTNGENERLEDITS.national_taxonomic_seq%TYPE;
   life_history_seq_v       BCPLANKTNGENERLEDITS.life_history_seq%TYPE;
   trophic_seq_v        BCPLANKTNGENERLEDITS.trophic_seq%TYPE;
   sex_seq_v            BCPLANKTNGENERLEDITS.sex_seq%TYPE;
   count_pct_v          BCPLANKTNGENERLEDITS.count_pct%TYPE;
   presence_v           BCPLANKTNGENERLEDITS.presence%TYPE;
   process_flag_v       BCPLANKTNGENERLEDITS.process_flag%TYPE;
   batch_seq_v          BCPLANKTNGENERLEDITS.batch_seq%TYPE;
   source_v         BCPLANKTNGENERLEDITS.source%TYPE;
   counts_v         BCPLANKTNGENERLEDITS.counts%TYPE;
   wet_weigth_v         BCPLANKTNGENERLEDITS.wet_weight%TYPE;
   dry_weight_v         BCPLANKTNGENERLEDITS.dry_weight%TYPE;
   bio_volume_v         BCPLANKTNGENERLEDITS.bio_volume%TYPE;
   min_sieve_v          BCPLANKTNGENERLEDITS.min_sieve%TYPE;
   max_sieve_v          BCPLANKTNGENERLEDITS.max_sieve%TYPE;
   func_return_var          VARCHAR2(600);
   DETAIL_VALIDATION_ERROR      EXCEPTION;
BEGIN
   OPEN plankton_general_errs_cur(batch_seq_in_par);
   LOOP
      FETCH plankton_general_errs_cur INTO pl_general_edt_seq_v, pl_headr_edt_seq_v, data_center_code_v, national_taxonomic_seq_v,
        life_history_seq_v, trophic_seq_v, sex_seq_v, count_pct_v,
        presence_v, process_flag_v, batch_seq_v, source_v, counts_v, wet_weigth_v,
        dry_weight_v, bio_volume_v, min_sieve_v, max_sieve_v;
      EXIT WHEN (plankton_general_errs_cur%NOTFOUND);
      -- Loop through each retrieved record in the cursor and call the PLANKTON_REC_VALID_PKG.PLANKTON_REC_VALID_PKG.VALIDATE_BCPLNKTNGNRLEDITS_FRM
      -- funtion. Store the return value in the func_return_var variable.
      func_return_var := PLANKTON_REC_VALID_PKG.VALIDATE_BCPLNKTNGNRLEDITS_FRM(pl_general_edt_seq_v, pl_headr_edt_seq_v,
                    data_center_code_v,
                    batch_seq_in_par, national_taxonomic_seq_v, life_history_seq_v,
                    trophic_seq_v, sex_seq_v, count_pct_v, presence_v, username_in_par, source_v,
                    counts_v, wet_weigth_v, dry_weight_v, bio_volume_v, min_sieve_v, max_sieve_v);
      -- Update the BCPLANKTNGENERLEDITS.process_flag column with either 'ECN' or 'ERR' depending on the value of the
      -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
      -- error which fired in the called procedure, therefore RAISE the DETAIL_VALIDATION_ERROR exception.
      IF func_return_var = 'ECN' THEN
         UPDATE BCPLANKTNGENERLEDITS
         SET process_flag = 'ECN'
         WHERE pl_general_edt_seq = pl_general_edt_seq_v;
      ELSIF func_return_var = 'ERR' THEN
         UPDATE BCPLANKTNGENERLEDITS
         SET process_flag = 'ERR'
         WHERE pl_general_edt_seq = pl_general_edt_seq_v;
      ELSE
         RAISE DETAIL_VALIDATION_ERROR;
      END IF;
   END LOOP;
   CLOSE plankton_general_errs_cur;
   RETURN 'OK';
EXCEPTION
   -- This exception is RAISED when the called procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in the called procedure out
   -- to the forms front end and display a message to the user.
   WHEN DETAIL_VALIDATION_ERROR THEN
      IF plankton_general_errs_cur%ISOPEN THEN
         CLOSE plankton_general_errs_cur;
      END IF;
      RETURN func_return_var;
   -- This exception is RAISED when this procedure fails due to an exception of its own. Check if the cursor is
   -- still open, if it is then close it. This is meant to propagate the error experienced in this procedure out
   -- to the forms front end and display a message to the user. The function name is included in the error message
   -- to easily identify the cause of the error.
   WHEN OTHERS THEN
      IF plankton_general_errs_cur%ISOPEN THEN
         CLOSE plankton_general_errs_cur;
      END IF;
      RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_PLANK_GENERL_ERRS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_PLANK_FREQ_ERRS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                                     username_in_par  IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
  -- Create the cursor (plankton_freq_errs_cur) passing the batch_seq_in_par input parameter value at execution time.
  -- This selects the columns for validatation.
  CURSOR plankton_freq_errs_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE) IS
  SELECT *
  FROM   bcPlanktnFreqEdits
  WHERE  batch_seq = batch_seq_cur_par;
  plankton_freq_row       bcPlanktnFreqEdits%ROWTYPE;
  func_return_var         VARCHAR2(600);
  DETAIL_VALIDATION_ERROR EXCEPTION;
BEGIN
  OPEN plankton_freq_errs_cur(batch_seq_in_par);
  LOOP
    FETCH plankton_freq_errs_cur INTO plankton_freq_row;
    EXIT WHEN (plankton_freq_errs_cur%NOTFOUND);
    -- Loop through each retrieved record in the cursor and call the PLANKTON_REC_VALID_PKG.VALIDATE_BCPLNKTNFREQEDITS_FRM
    -- funtion. Store the return value in the func_return_var variable.
    func_return_var := PLANKTON_REC_VALID_PKG.VALIDATE_BCPLNKTNFREQEDITS_FRM(
      pl_freq_edt_seq_par  => plankton_freq_row.pl_freq_edt_seq,
      pl_gen_edt_seq_par  => plankton_freq_row.pl_general_edt_seq,
      data_center_code_par => plankton_freq_row.data_center_code,
      data_type_seq_par    => plankton_freq_row.data_type_seq,
      upper_bin_size_par   => plankton_freq_row.upper_bin_size,
      lower_bin_size_par   => plankton_freq_row.lower_bin_size,
      bug_count_par        => plankton_freq_row.bug_count,
      bug_seq_par          => plankton_freq_row.bug_seq,
      data_value_par       => plankton_freq_row.data_value,
      data_qc_code_par     => plankton_freq_row.data_qc_code,
      username_par         => username_in_par,
      batch_seq_par        => batch_seq_in_par);
    -- Update the bcPlanktnFreqEdits.process_flag column with either 'ECN' or 'ERR' depending on the value of the
    -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
    -- error which fired in the called procedure, therefore RAISE the DETAIL_VALIDATION_ERROR exception.
    IF func_return_var = 'ECN' THEN
      UPDATE bcPlanktnFreqEdits
      SET process_flag = 'ECN'
      WHERE pl_freq_edt_seq = plankton_freq_row.pl_freq_edt_seq;
    ELSIF func_return_var = 'ERR' THEN
      UPDATE bcPlanktnFreqEdits
      SET process_flag = 'ERR'
      WHERE pl_freq_edt_seq = plankton_freq_row.pl_freq_edt_seq;
    ELSE
      RAISE DETAIL_VALIDATION_ERROR;
    END IF;
  END LOOP;
  CLOSE plankton_freq_errs_cur;
  RETURN 'OK';
EXCEPTION
  WHEN DETAIL_VALIDATION_ERROR THEN
    IF plankton_freq_errs_cur%ISOPEN THEN
      CLOSE plankton_freq_errs_cur;
    END IF;
    RETURN func_return_var;
  WHEN OTHERS THEN
    IF plankton_freq_errs_cur%ISOPEN THEN
      CLOSE plankton_freq_errs_cur;
    END IF;
    RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_PLANK_FREQ_ERRS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_PLANK_DTAIL_ERRS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                                      username_in_par  IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
  -- Create the cursor (plankton_dtail_errs_cur) passing the batch_seq_in_par input parameter value at execution time.
  -- This selects the columns for validatation.
  CURSOR plankton_dtail_errs_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE) IS
  SELECT *
  FROM   bcPlanktnDtailEdits
  WHERE  batch_seq = batch_seq_cur_par;
  plankton_dtail_row      bcPlanktnDtailEdits%ROWTYPE;
  func_return_var         VARCHAR2(600);
  DETAIL_VALIDATION_ERROR EXCEPTION;
BEGIN
  OPEN plankton_dtail_errs_cur(batch_seq_in_par);
  LOOP
    FETCH plankton_dtail_errs_cur INTO plankton_dtail_row;
    EXIT WHEN (plankton_dtail_errs_cur%NOTFOUND);
    -- Loop through each retrieved record in the cursor and call the PLANKTON_REC_VALID_PKG.validateBcPlnktnDtailEditsFrm
    -- funtion. Store the return value in the func_return_var variable.
    func_return_var := PLANKTON_REC_VALID_PKG.validateBcPlnktnDtailEditsFrm(
      pl_detail_edt_seq_par => plankton_dtail_row.pl_detail_edt_seq,
      pl_gen_edt_seq_par    => plankton_dtail_row.pl_general_edt_seq,
      data_center_code_par  => plankton_dtail_row.data_center_code,
      data_type_seq_par     => plankton_dtail_row.data_type_seq,
      data_value_par        => plankton_dtail_row.data_value,
      data_qc_code_par      => plankton_dtail_row.data_qc_code,
      username_par          => username_in_par,
      batch_seq_par         => batch_seq_in_par);
    -- Update the bcPlanktnDtailEdits.process_flag column with either 'ECN' or 'ERR' depending on the value of the
    -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
    -- error which fired in the called procedure, therefore RAISE the DETAIL_VALIDATION_ERROR exception.
    IF func_return_var = 'ECN' THEN
      UPDATE bcPlanktnDtailEdits
      SET process_flag = 'ECN'
      WHERE pl_detail_edt_seq = plankton_dtail_row.pl_detail_edt_seq;
    ELSIF func_return_var = 'ERR' THEN
      UPDATE bcPlanktnDtailEdits
      SET process_flag = 'ERR'
      WHERE pl_detail_edt_seq = plankton_dtail_row.pl_detail_edt_seq;
    ELSE
      RAISE DETAIL_VALIDATION_ERROR;
    END IF;
  END LOOP;
  CLOSE plankton_dtail_errs_cur;
  RETURN 'OK';
EXCEPTION
  WHEN DETAIL_VALIDATION_ERROR THEN
    IF plankton_dtail_errs_cur%ISOPEN THEN
      CLOSE plankton_dtail_errs_cur;
    END IF;
    RETURN func_return_var;
  WHEN OTHERS THEN
    IF plankton_dtail_errs_cur%ISOPEN THEN
      CLOSE plankton_dtail_errs_cur;
    END IF;
    RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_PLANK_DTAIL_ERRS;
-- ****************************************************************************************************
FUNCTION CHECK_BATCH_PLANK_INDIV_ERRS(batch_seq_in_par IN BCBATCHES.batch_seq%TYPE,
                                      username_in_par  IN BCUSERS.username%TYPE) RETURN VARCHAR2
IS
  -- Create the cursor (plankton_indiv_errs_cur) passing the batch_seq_in_par input parameter value at execution time.
  -- This selects the columns for validatation.
  CURSOR plankton_indiv_errs_cur(batch_seq_cur_par BCBATCHES.batch_seq%TYPE) IS
  SELECT *
  FROM   bcPlanktnIndivdlEdits
  WHERE  batch_seq = batch_seq_cur_par;
  plankton_indiv_row      bcPlanktnIndivdlEdits%ROWTYPE;
  func_return_var         VARCHAR2(600);
  DETAIL_VALIDATION_ERROR EXCEPTION;
BEGIN
  OPEN plankton_indiv_errs_cur(batch_seq_in_par);
  LOOP
    FETCH plankton_indiv_errs_cur INTO plankton_indiv_row;
    EXIT WHEN (plankton_indiv_errs_cur%NOTFOUND);
    -- Loop through each retrieved record in the cursor and call the PLANKTON_REC_VALID_PKG.validateBcPlanktnIndivEditsFrm
    -- funtion. Store the return value in the func_return_var variable.
    func_return_var := PLANKTON_REC_VALID_PKG.validateBcPlanktnIndivEditsFrm(
      pl_indiv_edt_seq_par => plankton_indiv_row.pl_indiv_edt_seq,
      pl_gen_edt_seq_par => plankton_indiv_row.pl_general_edt_seq,
      data_center_code_par => plankton_indiv_row.data_center_code,
      data_type_seq_par    => plankton_indiv_row.data_type_seq,
      bug_seq_par          => plankton_indiv_row.bug_seq,
      data_value_par       => plankton_indiv_row.data_value,
      data_qc_code_par     => plankton_indiv_row.data_qc_code,
      username_par         => username_in_par,
      batch_seq_par        => batch_seq_in_par);
    -- Update the bcPlanktnIndivdlEdits.process_flag column with either 'ECN' or 'ERR' depending on the value of the
    -- func_return_var variable. If func_return_var does not equal 'ECN' or 'ERR' then the value return is an exception
    -- error which fired in the called procedure, therefore RAISE the DETAIL_VALIDATION_ERROR exception.
    IF func_return_var = 'ECN' THEN
      UPDATE bcPlanktnIndivdlEdits
      SET process_flag = 'ECN'
      WHERE pl_indiv_edt_seq = plankton_indiv_row.pl_indiv_edt_seq;
    ELSIF func_return_var = 'ERR' THEN
      UPDATE bcPlanktnIndivdlEdits
      SET process_flag = 'ERR'
      WHERE pl_indiv_edt_seq = plankton_indiv_row.pl_indiv_edt_seq;
    ELSE
      RAISE DETAIL_VALIDATION_ERROR;
    END IF;
  END LOOP;
  CLOSE plankton_indiv_errs_cur;
  RETURN 'OK';
EXCEPTION
  WHEN DETAIL_VALIDATION_ERROR THEN
    IF plankton_indiv_errs_cur%ISOPEN THEN
      CLOSE plankton_indiv_errs_cur;
    END IF;
    RETURN func_return_var;
  WHEN OTHERS THEN
    IF plankton_indiv_errs_cur%ISOPEN THEN
      CLOSE plankton_indiv_errs_cur;
    END IF;
    RETURN TO_CHAR(SQLCODE)||', '||SQLERRM||'.';
END CHECK_BATCH_PLANK_INDIV_ERRS;
-- ****************************************************************************************************
FUNCTION DATA_TYPE(data_type_seq_in_par IN BCDATATYPES.data_type_seq%type, method_in_par IN BCDATATYPES.method%type) RETURN VARCHAR2
IS
   count_recs_var   NUMBER:=0;
   return_var       VARCHAR2(1) := 'F';
 BEGIN
  -- Create the cursor (data_type_cur) passing the data_type_seq_in_par and method_in_par input parameter values at execution
  -- time. This cursor select the count of the number of records which meet the criteria (where the datatype_seq and the
  -- method both refer to the same record.
  SELECT count(*) INTO count_recs_var
      FROM BCDATATYPES
      WHERE METHOD = method_in_par
      AND DATA_TYPE_SEQ = data_type_seq_in_par;
   -- If the count_recs_var is greater than 0 then both column values refer to the same datatype. Assign 'T' to the
   -- return_var variable.
   IF count_recs_var > 0 THEN
      return_var := 'T';
   END IF;
   RETURN return_var;
 END DATA_TYPE;
END BATCH_VALIDATION_PKG;

[upsonp]

Break down of the code:

Start by selecting all rows from both the Discrete Station Edits (BCS) and the Discrete Data Edits (BCD) tables that match the provided batch sequence and store the data in a 'validate' cursor to be iterated over.

FUNCTION VALIDATE_DISCRETE_DATA(batch_seq_in_par IN NUMBER) RETURN VARCHAR2
IS
   CURSOR validate IS
      SELECT S.MISSION_DESCRIPTOR, S.EVENT_COLLECTOR_EVENT_ID, S.EVENT_COLLECTOR_STN_NAME,
             S.DIS_HEADR_START_DEPTH, S.DIS_HEADR_END_DEPTH, S.DIS_HEADR_SLAT, S.DIS_HEADR_ELAT,
             S.DIS_HEADR_SLON, S.DIS_HEADR_ELON, S.DIS_HEADR_SDATE, S.DIS_HEADR_EDATE,
             S.DIS_HEADR_STIME, S.DIS_HEADR_ETIME, S.DIS_HEADR_COLLECTOR_SAMPLE_ID, S.DATA_CENTER_CODE,
             S.DIS_SAMPLE_KEY_VALUE, S.CREATED_DATE, D.MISSION_DESCRIPTOR, D.EVENT_COLLECTOR_EVENT_ID,
             D.EVENT_COLLECTOR_STN_NAME, D.DIS_HEADER_START_DEPTH, D.DIS_HEADER_END_DEPTH,
             D.DIS_HEADER_SLAT, D.DIS_HEADER_SLON, D.DIS_HEADER_SDATE, D.DIS_HEADER_STIME,
             D.DIS_DETAIL_COLLECTOR_SAMP_ID, D.DIS_SAMPLE_KEY_VALUE, D.DATA_CENTER_CODE,
             D.DIS_DETAIL_DATA_TYPE_SEQ, D.DATA_TYPE_METHOD, D.CREATED_DATE, D.DIS_DATA_NUM,
    DIS_HEADR_GEAR_SEQ, dis_headr_SOUNDING, DIS_HEADR_COLLECTOR_DEPLMT_ID
      FROM BCDISCRETESTATNEDITS S, BCDISCRETEDATAEDITS D
      WHERE S.batch_seq =D.batch_seq
      AND D.DIS_SAMPLE_KEY_VALUE = S.DIS_SAMPLE_KEY_VALUE
      AND  S.batch_seq = batch_seq_in_par
      ORDER BY D.DIS_DATA_NUM;

Create a list of variables that all the data will be read into and used for validating each column of each row.

• Variables ending in VS are from the Station (BCS) table.
• Variables ending in VD are from the data (BCD) table

   DIS_SAMPLE_KEY_VALUE_VS      BCDISCRETESTATNEDITS.dis_sample_key_value%TYPE;
   MISSION_DESCRIPTOR_vs                BCDISCRETESTATNEDITS.mission_descriptor%TYPE;
   EVENT_COLLECTOR_EVENT_ID_vs          BCDISCRETESTATNEDITS.event_collector_event_id%TYPE;
   EVENT_COLLECTOR_STN_NAME_vs          BCDISCRETESTATNEDITS.event_collector_stn_name%TYPE;
-- added sept 22, 2000
   DIS_HEADR_GEAR_SEQ_VS        BCDISCRETESTATNEDITS.dis_headr_gear_seq%TYPE;
   DIS_HEADR_SOUNDING_VS        BCDISCRETESTATNEDITS.dis_headr_SOUNDING%TYPE;
   collector_deplmt_id_VS       BCDISCRETESTATNEDITS.DIS_HEADR_COLLECTOR_DEPLMT_ID%TYPE;
--
   DIS_HEADR_SDATE_vs                   BCDISCRETESTATNEDITS.dis_headr_sdate%TYPE;
   DIS_HEADR_EDATE_vs                   BCDISCRETESTATNEDITS.dis_headr_edate%TYPE;
   DIS_HEADR_STIME_VS                   BCDISCRETESTATNEDITS.dis_headr_stime%TYPE;
   DIS_HEADR_ETIME_VS           BCDISCRETESTATNEDITS.dis_headr_etime%TYPE;
   DIS_HEADR_SLAT_VS                    BCDISCRETESTATNEDITS.dis_headr_slat%TYPE;
   DIS_HEADR_ELAT_VS                    BCDISCRETESTATNEDITS.dis_headr_elat%TYPE;
   DIS_HEADR_SLON_VS                    BCDISCRETESTATNEDITS.dis_headr_slon%TYPE;
   DIS_HEADR_ELON_VS                    BCDISCRETESTATNEDITS.dis_headr_elon%TYPE;
   DIS_HEADR_START_DEPTH_VS             BCDISCRETESTATNEDITS.dis_headr_start_depth%TYPE;
   DIS_HEADR_END_DEPTH_VS               BCDISCRETESTATNEDITS.dis_headr_end_depth%TYPE;
   DIS_HEADR_COL_SPLE_ID_VS             BCDISCRETESTATNEDITS.dis_headr_collector_sample_id%TYPE;
   DATA_CENTER_CODE_VS                  BCDISCRETESTATNEDITS.data_center_code%TYPE;
   PROCESS_FLAG_VS                      BCDISCRETESTATNEDITS.process_flag%TYPE;
   BATCH_SEQ_VS                         BCDISCRETESTATNEDITS.batch_seq%TYPE;
   DIS_SAMPLE_KEY_VALUE_VD              BCDISCRETEDATAEDITS.dis_sample_key_value%TYPE;
   MISSION_DESCRIPTOR_VD                BCDISCRETEDATAEDITS.mission_descriptor%TYPE;
   EVENT_COLLECTOR_EVENT_ID_VD          BCDISCRETEDATAEDITS.event_collector_event_id%TYPE;
   EVENT_COLLECTOR_STN_NAME_VD          BCDISCRETEDATAEDITS.event_collector_stn_name%TYPE;
   DIS_HEADER_SDATE_VD                  BCDISCRETEDATAEDITS.dis_header_sdate%TYPE;
   DIS_HEADER_STIME_VD                  BCDISCRETEDATAEDITS.dis_header_stime%TYPE;
   DIS_HEADER_SLAT_VD                   BCDISCRETEDATAEDITS.dis_header_slat%TYPE;
   DIS_HEADER_SLON_VD                   BCDISCRETEDATAEDITS.dis_header_slon%TYPE;
   DIS_HEADER_START_DEPTH_VD            BCDISCRETEDATAEDITS.dis_header_start_depth%TYPE;
   DIS_HEADER_END_DEPTH_VD              BCDISCRETEDATAEDITS.dis_header_end_depth%TYPE;
   DIS_DETAIL_COL_SAMP_ID_VD            BCDISCRETEDATAEDITS.dis_detail_collector_samp_id%TYPE;
   CREATED_DATE_VS                      BCDISCRETESTATNEDITS.created_date%TYPE;
   CREATED_DATE_VD                      BCDISCRETEDATAEDITS.created_date%TYPE;
   DATA_CENTER_CODE_VD                  BCDISCRETEDATAEDITS.data_center_code%TYPE;
   PROCESS_FLAG_VD                      BCDISCRETEDATAEDITS.process_flag%TYPE;
   BATCH_SEQ_VD                         BCDISCRETEDATAEDITS.batch_seq%TYPE;
   DIS_DETAIL_DATA_TYPE_SEQ_VD      BCDISCRETEDATAEDITS.dis_detail_data_type_seq%TYPE;
   DATA_TYPE_METHOD_VD          BCDISCRETEDATAEDITS.data_type_method%TYPE;
   METHOD_V             BCDATATYPES.method%TYPE;
   DATA_TYPE_SEQ_V          BCDATATYPES.data_type_seq%TYPE;
   DIS_DATA_NUM_var         BCDISCRETEDATAEDITS.dis_data_num%TYPE;
   NEXT_DATA_NUM_var            BCDISCRETEDATAEDITS.dis_data_num%TYPE;
   collector_sample_id_var      BCDISCRETEDATAEDITS.dis_detail_collector_samp_id%TYPE;
   valid                VARCHAR2(1);
   data_sample_key_is_null_var      VARCHAR2(1);
   return_var               VARCHAR2(600) := 'T';
   orphaned_count_var           NUMBER;
   new_data_type_seq_var        NUMBER;
   null_header_check_var        VARCHAR2(600);
  child_count_var       NUMBER;
  data_count_var        NUMBER;

Read each row from the validate cursor into the variables that were just created

BEGIN
   data_sample_key_is_null_var := 'N';
   OPEN VALIDATE;
   LOOP
      FETCH VALIDATE INTO MISSION_DESCRIPTOR_vs,EVENT_COLLECTOR_EVENT_ID_vs,
              EVENT_COLLECTOR_STN_NAME_VS,DIS_HEADR_START_DEPTH_VS,
              DIS_HEADR_END_DEPTH_VS, DIS_HEADR_SLAT_VS, DIS_HEADR_ELAT_VS,
              DIS_HEADR_SLON_VS, DIS_HEADR_ELON_VS, DIS_HEADR_SDATE_VS,
              DIS_HEADR_EDATE_VS, DIS_HEADR_STIME_VS, DIS_HEADR_ETIME_VS,
              DIS_HEADR_COL_SPLE_ID_VS, DATA_CENTER_CODE_VS,
              DIS_SAMPLE_KEY_VALUE_VS, CREATED_DATE_VS, MISSION_DESCRIPTOR_VD,
              EVENT_COLLECTOR_EVENT_ID_VD,
              EVENT_COLLECTOR_STN_NAME_VD, DIS_HEADER_START_DEPTH_VD,
              DIS_HEADER_END_DEPTH_VD, DIS_HEADER_SLAT_VD,
              DIS_HEADER_SLON_VD, DIS_HEADER_SDATE_VD,
              DIS_HEADER_STIME_VD, DIS_DETAIL_COL_SAMP_ID_VD,
              DIS_SAMPLE_KEY_VALUE_VD, DATA_CENTER_CODE_VD,
              DIS_DETAIL_DATA_TYPE_SEQ_VD,DATA_TYPE_METHOD_VD, CREATED_DATE_VD, DIS_DATA_NUM_var,
            DIS_HEADR_GEAR_SEQ_VS, DIS_HEADR_SOUNDING_VS, collector_deplmt_id_VS;
      EXIT WHEN VALIDATE%notfound;

Create a string (null_header_check_var) by concatenating a bunch of columns together as a string.

1. gear sequence
2. start date
3. end date
4. start time
5. end time
6. start lat
7. end lat
8. start lon
9. end lon
10. start depth
11. end depth
12. sounding
13. deployment id <- this is available to be filled out in the AZMP Template, but BIO doesn't use it and it was never included in Dart because... we don't use it.

I don't really understand way you would do this because the next check is to see if the string is null or blank, but if any of the queried columns exist then the null_header_check string won't be blank.

      valid := 'Y';
    -- Sept 22, 2000
    -- check for null headr on the station side
    -- If the statn header is null and the dis_sample key values match
    -- then generate a fatal error
    null_header_check_var := to_char(dis_headr_gear_seq_VS)||to_char(dis_headr_sdate_VS)||to_char(dis_headr_edate_VS)||
                to_char(dis_headr_stime_VS)||to_char(dis_headr_etime_VS)||to_char(dis_headr_slat_VS)||
                to_char(dis_headr_elat_VS)||to_char(dis_headr_slon_VS)||to_char(dis_headr_elon_VS)||
                to_char(dis_headr_start_depth_VS)||to_char(dis_headr_end_depth_VS)||
                to_char(dis_headr_sounding_VS)||collector_deplmt_id_VS;

Now we'll record columns that are null.

1. if the null_header_check_var string is null or blank and the sample ID is not null log a 4053 error in the BC Station Data Errors table.

     IF (null_header_check_var IS NULL or null_header_check_var = ' ') and DIS_SAMPLE_KEY_VALUE_VS IS NOT NULL THEN
    INSERT INTO BCSTATNDATAERRORS VALUES
           ('BCDISCRETESTATNEDITS', DIS_SAMPLE_KEY_VALUE_VS, 'DIS_SAMPLE_KEY_VALUE',
           '4053', CREATED_DATE_VD, nvl(DIS_DETAIL_COL_SAMP_ID_VD,'X'), batch_seq_in_par);
        valid := 'N';
   END IF;

2. Check the DIS_DATA_NUM column equals the NEXT_DATA_NUM column, log a 4043 if they are equal. If they're not equal set the NEXT_DATA_NUM to the value of the DIS_DATA_NUM for the next iteration.

Not really a very good way to make sure the DIS_DATA_NUM values are unique, or are in sequence 😕, all it's doing is making sure the new DIS_DATA_NUM value wasn't the same as the previous row.

      IF DIS_DATA_NUM_var = NEXT_DATA_NUM_var THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_DATA_NUM',
            '4043', CREATED_DATE_VD, nvl(DIS_DETAIL_COL_SAMP_ID_VD,'X'), batch_seq_in_par);
         valid := 'N';
      END IF;
         NEXT_DATA_NUM_var := DIS_DATA_NUM_var;

3. Check if the Mission Descriptor. a. if null log a 4001 error.

     IF MISSION_DESCRIPTOR_vD IS NULL THEN
        INSERT INTO BCSTATNDATAERRORS VALUES
           ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'MISSION_DESCRIPTOR',
            '4001', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS, 'X'), batch_seq_in_par);
        valid := 'N';
     ELSE

4. b. MISSION_DESCRIPTOR_VS is from the BCS table, MISSION_DESCRIPTOR_VD is from the BCD table. If they're not equal, there's a problem.

        IF MISSION_DESCRIPTOR_vs != MISSION_DESCRIPTOR_vD THEN
           INSERT INTO BCSTATNDATAERRORS VALUES
              ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'MISSION_DESCRIPTOR',
               '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
           valid := 'N';
        END IF;
     END IF;

5. Check the BCD event ID a. if null log a 40001 error

     IF EVENT_COLLECTOR_EVENT_ID_vD IS NULL THEN
        INSERT INTO BCSTATNDATAERRORS VALUES
           ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'EVENT_COLLECTOR_EVENT_ID',
            '4001', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS, 'X'),batch_seq_in_par);
        valid := 'N';

6. b. if not null make sure the BCS event ID matches the BCD event ID

     ELSE
        IF EVENT_COLLECTOR_EVENT_ID_vs != EVENT_COLLECTOR_EVENT_ID_vD THEN
           INSERT INTO BCSTATNDATAERRORS VALUES
              ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'EVENT_COLLECTOR_EVENT_ID',
               '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
           valid := 'N';
        END IF;
     END IF;

7. Station Name a. is it null? 4001 error

     IF EVENT_COLLECTOR_STN_NAME_VD IS NULL THEN
        INSERT INTO BCSTATNDATAERRORS VALUES
           ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'EVENT_COLLECTOR_STN_NAME',
            '4001', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS, 'X'),batch_seq_in_par);
        valid := 'N';

8. b. make sure BCD station name matches BCS Station name

     ELSE
        IF EVENT_COLLECTOR_STN_NAME_VS != EVENT_COLLECTOR_STN_NAME_VD THEN
           INSERT INTO BCSTATNDATAERRORS VALUES
              ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'EVENT_COLLECTOR_STN_NAME',
               '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
           valid := 'N';
        END IF;
     END IF;

9. Make sure the BCD starting depth matches the BCS starting Depth

     IF DIS_HEADR_START_DEPTH_VS != DIS_HEADER_START_DEPTH_VD THEN
        INSERT INTO BCSTATNDATAERRORS VALUES
           ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_HEADER_START_DEPTH',
            '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
        valid := 'N';
     END IF;

10. Make sure the BCD end depth matches the BCS end Depth

      IF DIS_HEADR_END_DEPTH_VS != DIS_HEADER_END_DEPTH_VD THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_HEADER_END_DEPTH',
             '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
         valid := 'N';
      END IF;

11. Make sure the BCD start latitude matches the BCS start latitude

      IF DIS_HEADR_SLAT_VS != DIS_HEADER_SLAT_VD THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_HEADER_SLAT',
             '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
         valid := 'N';
      END IF;

12. Make sure the BCD start longitude matches the BCS start longitude

         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_HEADER_SLON',
             '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
         valid := 'N';
      END IF;

13. Make sure the BCD start date matches the BCS start date

      IF DIS_HEADR_SDATE_VS != DIS_HEADER_SDATE_VD THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_HEADER_SDATE',
             '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
         valid := 'N';
      END IF;

14. Make sure the BCD start time matches the BCS start time

      IF DIS_HEADR_STIME_VS != DIS_HEADER_STIME_VD THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_HEADER_STIME',
             '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
         valid := 'N';
      END IF;

15. BCD sample ID a. is the sample ID null

      IF DIS_DETAIL_COL_SAMP_ID_VD IS NULL THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_DETAIL_COLLECTOR_SAMP_ID',
             '4001', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS, 'X'),batch_seq_in_par);
         valid := 'N';

16. b. does the BCD sample ID match the

      ELSE
         IF DIS_HEADR_COL_SPLE_ID_VS != DIS_DETAIL_COL_SAMP_ID_VD THEN
            INSERT INTO BCSTATNDATAERRORS VALUES
               ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DIS_DETAIL_COLLECTOR_SAMP_ID',
                '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
            valid := 'N';
         END IF;
      END IF;

17. Check that the BCS and BCD datacenter codes match

      IF DATA_CENTER_CODE_VS != DATA_CENTER_CODE_VD THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DATA_CENTER_CODE',
             '4042', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
         valid := 'N';
      END IF;

18. If the Data Type Method and the Data Type Sequence are supplied <-- This is important

    • Most other checks won't apply to Dart because Dart will ensure the BCS and BCD table values match, but this check calls another script to check the values are actually with in acceptable ranges.
    • I've also learned that the BCDataTypes table on BiochemT is out of date, so if I supply the code 90001115 with a method 'pH_CTD_cal-total' the validation fails even though this is the correct method that goes with that code.

      -- if both are supplied then make sure that the correspond to each other
      IF DATA_TYPE_METHOD_VD IS NOT NULL AND
             DIS_DETAIL_DATA_TYPE_SEQ_VD IS NOT NULL THEN

19. a. Jump to Data Type Validation #148

     IF BATCH_VALIDATION_PKG.DATA_TYPE(DIS_DETAIL_DATA_TYPE_SEQ_VD, DATA_TYPE_METHOD_VD) = 'F' THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DATA_TYPE_SEQ',
             '4051', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS,'X'),batch_seq_in_par);
            valid := 'N';
         END IF;

20. b. Dart will require a BioChem Data Type for anything it's uploading, so it's unlikely it will ever have a method and no data type, and method codes are part of the BioChem Data Type table.

      -- if only the method is supplied then look it up in the data types table and assign the data_type_seq to the current
    new_data_type_seq_var := NULL;
      ELSIF DATA_TYPE_METHOD_VD IS NOT NULL AND
               DIS_DETAIL_DATA_TYPE_SEQ_VD IS NULL THEN
            -- select the data type seq associated with the method into a variable
            select data_type_seq into new_data_type_seq_var from bcdatatypes
            where upper(method)= upper(DATA_TYPE_METHOD_VD);
        -- if the new_data_type_seq_var is still null then the method was not found in the bcdatatypes table log the error
        -- else update the associates record in the dcdiscretedataedits table with the new data_type_seq found
        if new_data_type_seq_var  is null then
            INSERT INTO BCSTATNDATAERRORS VALUES
                     ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DATA_TYPE_SEQ',
                         '4001', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS, 'X'),batch_seq_in_par);
                 valid := 'N';
            else
            update bcdiscretedataedits set DIS_DETAIL_DATA_TYPE_SEQ = new_data_type_seq_var
                where DIS_DATA_NUM = DIS_DATA_NUM_var
                and batch_seq = batch_seq_in_par;
        end if;

21. c. If there's no method and no data type record an error

      ELSIF DIS_DETAIL_DATA_TYPE_SEQ_VD IS NULL THEN
         INSERT INTO BCSTATNDATAERRORS VALUES
            ('BCDISCRETEDATAEDITS', DIS_DATA_NUM_var, 'DATA_TYPE_SEQ',
             '4001', CREATED_DATE_VD, nvl(DIS_HEADR_COL_SPLE_ID_VS, 'X'),batch_seq_in_par);
         valid := 'N';
      END IF;

22. ensure the sample ID is present

      IF DIS_SAMPLE_KEY_VALUE_VS IS NOT NULL THEN
         IF valid = 'N' THEN
            UPDATE BCDISCRETESTATNEDITS
            SET PROCESS_FLAG = 'SVE'
            WHERE batch_seq = batch_seq_in_par
            AND DIS_SAMPLE_KEY_VALUE = DIS_SAMPLE_KEY_VALUE_VS;
         END IF;
      END IF;

23. If anything failed in the previous steps mark the row as DVE (Data Value Error?), if there was no issue mark it as DVI (Data Value Insert?)

         IF valid = 'N' THEN
            return_var := 'F';
            UPDATE BCDISCRETEDATAEDITS
            SET PROCESS_FLAG = 'DVE'
            WHERE batch_seq = batch_seq_in_par
            AND DIS_DATA_NUM = DIS_DATA_NUM_var;
         ELSE
            UPDATE BCDISCRETEDATAEDITS
            SET PROCESS_FLAG = 'DVI'
            WHERE batch_seq = batch_seq_in_par
            AND DIS_DATA_NUM = DIS_DATA_NUM_var;
         END IF;
    END LOOP;

We're done with the cursor at this point close it and commit updates

   CLOSE validate;
   commit;

The following section is for reporting purposes to tell the user what has failed validation.

Kind of important here I think, they're counting DIS_DATA_NUM in the BCS and BCD table where the sample ids match or are null in the BCS table, but they're also checking that the creation date in both the BCS and BCD tables match. This must be a check for BCD rows that didn't get validated 😕.

It's basically saying get rows where BCS.sample_ID==BCD.sample_ID or where the BCS.sample_ID are null where the BCS.creation_date==BCD.creation_date

-- count the orphaned data records
Select count(dis_data_num) into child_count_var
from bcdiscretestatnedits a, bcdiscretedataedits b
where
(a.dis_sample_key_value=b.dis_sample_key_value OR a.DIS_SAMPLE_KEY_VALUE IS NULL)
and a.created_date=b.created_date
and a.batch_seq = batch_seq_in_par;

Insert 1 into the data_count_var if the BCD table has a batch_seq_id matching the requested batch_seq_id.

select count(1) into data_count_var
from bcdiscretedataedits
where batch_seq = batch_seq_in_par;

If there are zero errors then '1 - 0' will be greater than zero. If there are any errors at all then this fails. I don't really understand this logic because data_count_var is always going to be 1 and this will always be zero or less if there are any orphaned records.

• DVO must stand for Data Value Orphaned

  IF (data_count_var - child_count_var) > 0 THEN
  -- set the orphaned data record's process flag
  UPDATE BCDISCRETEDATAEDITS
  SET process_flag = 'DVO'
  WHERE (dis_sample_key_value NOT IN
  (select dis_sample_key_value from bcdiscretestatnedits
      where batch_seq = batch_seq_in_par)
  OR DIS_SAMPLE_KEY_VALUE IS NULL)
  AND batch_seq = batch_seq_in_par;

-- report orphaned data record error into the errors table
-- T Joyce Sept 20, 2000
   INSERT INTO BCSTATNDATAERRORS SELECT
            'BCDISCRETEDATAEDITS', DIS_DATA_NUM, 'DIS_SAMPLE_KEY_VALUE',
             '4052', CREATED_DATE, nvl(DIS_DETAIL_COLLECTOR_SAMP_ID,'X'), batch_seq
   FROM BCDISCRETEDATAEDITS
   WHERE process_flag = 'DVO'
         and batch_seq = batch_seq_in_par;
-- flag all the stations as errors
   UPDATE BCDISCRETESTATNEDITS
   SET process_flag = 'SVE'
   WHERE batch_seq = batch_seq_in_par;
-- flag the remaining data records as errors
   UPDATE BCDISCRETEDATAEDITS
   SET process_flag = 'DVE'
   WHERE batch_seq = batch_seq_in_par
   AND process_flag!='DVO';
   -- flag as fatal error
   return_var:='F';
END IF;

Finally commit the changes and return the value indicating if rows for the requested batch ID passed

   commit;
   RETURN return_var;
   EXCEPTION
      WHEN OTHERS THEN
         IF validate%ISOPEN THEN
            CLOSE validate;
         END IF;
         return_var := 'VALIDATE_DISCRETE_DATA proc error: '||to_char(SQLCODE)||', '||SQLERRM||'.';
         RETURN return_var;
  END VALIDATE_DISCRETE_DATA;
-- ******************************************************************************************
END VALIDATE_DISCRETE_STATN_DATA;

[upsonp]

Summary:

Not much needs to be done to Dart to apply this functions validation. Dart will ensure if values exist that the BCS and BCD tables use the same values. I guess we should run validation in dart to check that:

• Events should have a valid start/end date, time, lat, lon, depth
  • This gets a bit complicated. Dart will use bottles to get date, time, lat, lon, depth if it exists. If the data can't be found in the bottle table, it then gets lat, lon, date, time from the event.
  • Bottles require a datetime value to be 'closed', which comes from the BTL file when the bottles are loaded; they can never not have a start/end date
• Sounding
• Events should have a valid gear sequence. Currently we use a default of 90000019 - Niskin Bottle of unknown size. This is under discussion for issue #126, because samples should use 90000019 if the bottle size is unknown, but sensors on the CTD should use something else and the user should have the ability to adjust the gear type if they do know the bottle size. I think a whole equipment section should be added to Dart to manage equipment used on a mission. As it is now, it's either a CTD or a Net (76um or 202um)
• We already validate the mission descriptor exists, and if it does Dart will use the same value for both BCS and BCD tables
• Branch out to run a Data Type Validation as per issue #148