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

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

DECLARE

  --
  -- Local constants
  --
  l_APPS_ID CONSTANT NUMBER := 900;

  --
  -- Local variables
  --
  e_app_not_found    EXCEPTION;
  e_dg_not_found     EXCEPTION;
  e_already_exists   EXCEPTION;
  e_no_app_user      EXCEPTION;
  l_application_id   NUMBER;
  l_data_group_id    NUMBER;
  l_created_by       NUMBER;
  l_last_updated_by  NUMBER;
  l_dg_exists        VARCHAR2(1);
  l_apps_exist       VARCHAR2(1);

  --
  -- Get the Product Application Id
  --
  CURSOR csr_app IS
  SELECT a.application_id
        ,a.created_by
        ,a.last_updated_by
  FROM   fnd_application a
  WHERE  a.application_short_name = 'XXAS';

  --
  -- Get the Standard Data Group Id
  --
  CURSOR csr_dg IS
  SELECT dg.data_group_id
  FROM   fnd_data_groups dg
  WHERE  dg.data_group_name = 'Standard';

  --
  -- See if the Data Group already exists
  --
  CURSOR csr_dg_exists
    (p_data_group_id  IN NUMBER
    ,p_application_id IN NUMBER) IS
  SELECT 'Y'
  FROM   fnd_data_group_units dgu
  WHERE  dgu.data_group_id = p_data_group_id
  AND    dgu.application_id = p_application_id;

  --
  -- Check the APPS User Id
  --
  CURSOR csr_app_user IS
  SELECT 'Y'
  FROM   fnd_oracle_userid u
  WHERE  u.oracle_id = l_APPS_ID;

BEGIN

  --
  -- Fetch the Product Application Id.
  --
  OPEN  csr_app;
  FETCH csr_app INTO l_application_id
                    ,l_created_by
                    ,l_last_updated_by;
  CLOSE csr_app;

  --
  -- Error if the Application could not be found.
  --
  IF l_application_id IS NULL THEN
    RAISE e_app_not_found;
  END IF;

  --
  -- Fetch the Data Group Id.
  --
  OPEN  csr_dg;
  FETCH csr_dg INTO l_data_group_id;
  CLOSE csr_dg;

  --
  -- Error if the Data Group could not be found.
  --
  IF l_data_group_id IS NULL THEN
    RAISE e_dg_not_found;
  END IF;

  --
  -- See if the Data Group already exists
  --
  OPEN  csr_dg_exists
          (p_data_group_id  => l_data_group_id
          ,p_application_id => l_application_id);
  FETCH csr_dg_exists INTO l_dg_exists;
  CLOSE csr_dg_exists;

  --
  -- Finish if the Data Group already exists
  --
  IF l_dg_exists IS NOT NULL THEN
    RAISE e_already_exists;
  END IF;

  --
  -- Check that the User 900 (APPS) exists
  --
  OPEN  csr_app_user;
  FETCH csr_app_user INTO l_apps_exist;
  CLOSE csr_app_user;

  --
  -- Error if the user cannot be found
  --
  IF l_apps_exist IS NULL THEN
    RAISE e_no_app_user;
  END IF;

  --
  -- Add the Data Group Unit to the Standard Data Group
  --
  INSERT INTO fnd_data_group_units
    (application_id
    ,data_group_id
    ,last_update_date
    ,last_updated_by
    ,creation_date
    ,created_by
    ,last_update_login
    ,oracle_id
    ,description)
  VALUES
    (l_application_id
    ,l_data_group_id
    ,sysdate
    ,-1
    ,sysdate
    ,-1
    ,0
    ,l_APPS_ID
    ,'Applaud Solutions Data Group Application');

EXCEPTION

  WHEN e_app_not_found THEN
    RAISE no_data_found;

  WHEN e_dg_not_found THEN
    RAISE no_data_found;

  WHEN e_already_exists THEN
    --
    -- No further processing required
    --
    NULL;

  WHEn e_no_app_user THEN
    RAISE no_data_found;

  WHEN others THEN
    RAISE;

END;
/
COMMIT;
EXIT;
