/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET FEEDBACK OFF
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
WHENEVER OSERROR EXIT FAILURE ROLLBACK

DECLARE

  l_file_name varchar2(80)  := ('&1');
  l_file_top  varchar2(30)  := ('&2');
  l_file_dir  varchar2(400) := ('&3');
  l_file_ver  number        := to_number('&4');
  l_phase     varchar2(30)  := ('&5');
  l_exists    varchar2(1)   := 'N';

  CURSOR csr_get_existing IS
  SELECT 'Y'
  FROM   xxas_com_applied_files af
  WHERE  af.file_name = l_file_name;

BEGIN

  --
  -- Determine if the file has previously been installed
  --
  OPEN  csr_get_existing;
  FETCH csr_get_existing INTO l_exists;
  CLOSE csr_get_existing;

  IF l_exists = 'Y' THEN
    --
    -- Update the file version if the file already exists
    --
    UPDATE xxas_com_applied_files
    SET    file_ver = l_file_ver
          ,last_update_date = sysdate
    WHERE  file_name = l_file_name;
  ELSE
    --
    -- Insert the file if it doesn't exist
    --
    INSERT INTO xxas_com_applied_files
      (file_name
      ,file_top
      ,file_dir
      ,file_ver
      ,phase
      ,creation_date
      ,last_update_date)
    VALUES
      (l_file_name
      ,l_file_top
      ,l_file_dir
      ,l_file_ver
      ,l_phase
      ,sysdate
      ,sysdate);
  END IF;

END;
/
COMMIT;
EXIT;
