CREATE OR REPLACE FUNCTION BIOTICS_USER.value_on_date(tbl IN VARCHAR2, fld IN VARCHAR2,
                                         prkey IN NUMBER, tdate IN DATE)
-- Value_on_date
--
-- This function finds the value of a specific field on a specific date. It works only for
-- records that currently exist in the database (i.e. it is not designed to report values
-- for records that have been deleted). It also works only for data changes that have
-- triggered entry of records into the audit logs, and for tables that contain a
-- REC_CREATE_DATE column. If the field value was modified on the date of interest, the
-- value returned will be the LAST of any modifications on that date.
--
-- The value of a given field on a specified date will be one of:
--   1) the current value (if no changes have been made since the date of interest),
--   2) the value in the old_value field of the first audit log record AFTER that date, or
--   3) N/A if the record containing the field did not exist on that date
--
-- written by G. Woodsum, FNAI, 1/2008
--
-- arguments:
--     tbl        the name of the database table in which the field resides
--     fld        the name of the field for which to find the old value
--     prkey      id of the record for which you want to find the old value
--     date       date on which you want the old value (entered as text, format 'dd-MON-yy')
--
-- examples of call:  value_on_date('ELEMENT_SUBNATIONAL','S_RANK', 16350, '01-NOV-13')
--                    value_on_date('eo','eo_data', 27026, '20-FEB-05')
--
-- returns:   - last value of the field on the date you requested (note that it is a TEXT value)
--            - a text value of "null" is returned if the field value was null
--            - a text value of "N/A (new record)" is returned if the record in question
--                   had not yet been created as of the date requested
--
--*******************************************************************************************
RETURN VARCHAR2
IS
  pk VARCHAR2(30);
  rvalue VARCHAR2(4000);
  dsql VARCHAR2 (750);
  rcdate DATE;
  nchanges NUMBER;
  dtype VARCHAR2(30);
  tblname VARCHAR2(30);
  fldname VARCHAR2(30);
BEGIN
  tblname := UPPER(tbl);
  fldname := UPPER(fld);
  pk := getPK(tblname);
-- First get the data type of the field requested. This also serves
-- to perform a validity check on the tbl and fld parameter values.
-- (haven't been able to figure out yet how to check prkey)
  SELECT atc.data_type INTO dtype
  FROM all_tab_columns atc
  WHERE atc.table_name = tblname
     and atc.column_name = fldname;
-- Now handle the case where the record didn't exist
  dsql := 'SELECT ' || tblname || '.rec_create_date FROM ' ||
          tblname || ' WHERE ' || pk || ' = :1';
  EXECUTE IMMEDIATE dsql INTO rcdate USING prkey;
  IF trunc(rcdate) > tdate THEN
     rvalue := 'N/A (new record)';
  ELSE
   -- Find out how many changes have been made
      SELECT count(al.audit_log_id)
      INTO nchanges
      FROM audit_log_column alc,audit_log al
      WHERE  alc.column_name = fldname
          and alc.audit_log_id = al.audit_log_id
          and al.action_code in ('U')
          and al.table_name =  tblname
          and trunc(al.audit_date) >  tdate
          and al.primary_key_id = prkey;
      IF nchanges = 0 THEN
         -- If there haven't been any changes, then get the current value of the field.
         -- (note that we need two methods, one for CLOB fields, one for other types)
          IF dtype = 'CLOB' THEN
             dsql := 'SELECT DBMS_LOB.SUBSTR(' || fldname ||',4000,1)' ||
             ' FROM ' || tblname || ' WHERE ' || pk || ' = ' || prkey;
          ELSE
             dsql := 'SELECT ' ||  fldname ||
             ' FROM ' || tblname || ' WHERE ' || pk || ' = ' || prkey;
          END IF;
          EXECUTE IMMEDIATE dsql INTO rvalue;
      ELSE
          -- There are changes, so get the old value from the first mod after the date
          SELECT alc.old_value
          INTO rvalue
          FROM audit_log_column alc
          WHERE alc.column_name = fldname
              and alc.audit_log_id = (Select min(al.audit_log_id)
                  -- takes advantage of the fact that audit_log_ids are sequential in time
                  From audit_log al, audit_log_column alc
                  Where  alc.column_name = fldname
                     and alc.audit_log_id = al.audit_log_id
                     and al.action_code in ('U')
                     and al.table_name = tblname
                     and trunc(al.audit_date) > tdate
                     and al.primary_key_id = prkey
                     group by al.primary_key_id);
      END IF;
  END IF;
  IF rvalue IS NULL then
     RETURN('null');
  ELSE
     RETURN(rvalue);
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001,'Parameter error - bad table or field name!');
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002,'Unknown function error!');
  RETURN(null);
END;
/