/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2011 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 and variables
  --
  l_FTYPE_NAME CONSTANT VARCHAR2(80)  := 'Applaud Organization';
  l_FTYPE_DESC CONSTANT VARCHAR2(240) := 'Absence Organization-level formula type';

  l_formula_type_id     NUMBER;

  --
  -- Tests if the Formula Type already exists
  --
  CURSOR csr_ftype_exists IS
  SELECT ftype.formula_type_id
  FROM   ff_formula_types ftype
  WHERE  ftype.formula_type_name = l_FTYPE_NAME;

  --
  -- Nested function to create an FF context usage
  --
  PROCEDURE create_context_usage
    (p_formula_type_id IN NUMBER
    ,p_context_name    IN VARCHAR2) IS

    l_context_id   NUMBER;
    l_usage_exists VARCHAR2(1);

    --
    -- Cursor to get the context
    --
    CURSOR csr_get_context IS
    SELECT c.context_id
    FROM   ff_contexts c
    WHERE  c.context_name = p_context_name;

    --
    -- Cursor to check if the usage already exists
    --
    CURSOR csr_get_usage IS
    SELECT 'Y'
    FROM   ff_ftype_context_usages u
    WHERE  u.formula_type_id = p_formula_type_id
    AND    u.context_id = l_context_id;

  BEGIN

    --
    -- Fetch the context Id
    --
    OPEN  csr_get_context;
    FETCH csr_get_context INTO l_context_id;
    CLOSE csr_get_context;

    --
    -- Only continue if the context exists
    --
    IF l_context_id IS NOT NULL THEN

      --
      -- Check if the context usage already exists
      --
      OPEN  csr_get_usage;
      FETCH csr_get_usage INTO l_usage_exists;
      CLOSE csr_get_usage;

      IF l_usage_exists IS NULL OR l_usage_exists <> 'Y' THEN

        --
        -- Create the formula type usage
        --
        INSERT INTO ff_ftype_context_usages
          (formula_type_id
          ,context_id)
        VALUES
          (p_formula_type_id
          ,l_context_id);

      END IF;

    END IF;

  END create_context_usage;

BEGIN

  --
  -- Determine if the Formula Type already exists
  --
  OPEN  csr_ftype_exists;
  FETCH csr_ftype_exists INTO l_formula_type_id;
  CLOSE csr_ftype_exists;

  --
  -- Create the Fast Formula Type
  --
  IF l_formula_type_id IS NULL THEN

    SELECT ff_formula_types_s.nextval
    INTO   l_formula_type_id
    FROM   dual;

    --
    -- Create the Fast Formula
    --
    INSERT INTO ff_formula_types
      (formula_type_id
      ,formula_type_name
      ,type_description
      ,creation_date
      ,created_by
      ,last_update_date
      ,last_updated_by
      ,last_update_login)
    VALUES
      (l_formula_type_id
      ,l_FTYPE_NAME
      ,l_FTYPE_DESC
      ,sysdate
      ,fnd_global.user_id
      ,sysdate
      ,fnd_global.user_id
      ,fnd_global.login_id);

  ELSE
    --
    -- Update the existing Fast Formula text
    -- (if it has multiple date-track versions the text of
    -- all versions is updated).
    --
    UPDATE ff_formula_types
    SET    type_description = l_FTYPE_DESC
    WHERE  formula_type_id = l_formula_type_id;
  
 END IF;

  --
  -- Create the Formula Type context usages
  --
  create_context_usage(l_formula_type_id, 'ORGANIZATION_ID');
  create_context_usage(l_formula_type_id, 'DATE_EARNED');
  create_context_usage(l_formula_type_id, 'BUSINESS_GROUP_ID');


END;
/
COMMIT;
EXIT;
