--
-- Copyright (C) 2004 Red Hat Inc. All Rights Reserved.
--
-- The contents of this file are subject to the CCM Public
-- License (the "License"); you may not use this file except in
-- compliance with the License. You may obtain a copy of the
-- License at http://www.redhat.com/licenses/ccmpl.html.
--
-- Software distributed under the License is distributed on an
-- "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express
-- or implied. See the License for the specific language governing
-- rights and limitations under the License.
--
-- $Id: //cms/dev/sql/ccm-cms/oracle-se/upgrade/6.0.1-6.1.0/denormalize-versioning.sql#3 $
-- $DateTime: 2004/04/07 16:07:11 $


declare
   TYPE itemCursor IS REF CURSOR;
   newEntry itemCursor;
   v_creation_user           integer;
   v_creation_ip             varchar(400);
   v_creation_timestamp      date;
   v_last_modified_user      integer;
   v_last_modifying_ip       varchar(400);
   v_last_modified_timestamp date;
   v_sql_stmt                varchar(4000);
begin
      for item in (
          select item_id from cms_items
      ) loop

      v_sql_stmt := 'select modifying_user,
                   timestamp,
               modifying_ip
        from vcx_txns where id = 
           (select min(txn_id)
              from vcx_obj_changes 
             where obj_id in (select object_type || '';id:1:'' || object_id 
                                from acs_objects 
                               where object_id = ' || item.item_id || '))';

      OPEN newEntry FOR v_sql_stmt;
      FETCH newEntry INTO v_creation_user, v_creation_timestamp, v_creation_ip;
      CLOSE newEntry;

      v_sql_stmt := 'select modifying_user,
                   timestamp,
               modifying_ip
        from vcx_txns where id = 
           (select max(txn_id)
              from vcx_obj_changes 
             where obj_id in (select object_type || '';id:1:'' || object_id 
                                from acs_objects 
                               where object_id = ' || item.item_id || '))';

      OPEN newEntry FOR v_sql_stmt;
      FETCH newEntry INTO v_last_modified_user, 
                          v_last_modified_timestamp, 
                          v_last_modifying_ip;
      CLOSE newEntry;

        insert into acs_auditing (
           object_id,
           creation_user,
           creation_date,
           creation_ip,
           modifying_user,
           last_modified,
           modifying_ip
        ) values (
           item.item_id,
           v_creation_user,
           v_creation_timestamp,
           v_creation_ip,
           v_last_modified_user,
           v_last_modified_timestamp,
           v_last_modifying_ip
        );           
   end loop;    
end;
/
show errors;
