/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2010 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
  --
  e_no_bg_id          EXCEPTION;
  e_ff_exists         EXCEPTION;
  e_bg_not_exists     EXCEPTION;
  e_ff_type_not_found EXCEPTION;
  l_BG_ID   CONSTANT  NUMBER        := to_number('&1');
  l_USER_ID CONSTANT  NUMBER        := to_number('&2');
  l_START   CONSTANT  DATE          := to_date('1900-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_COM_BASIC_CONTACT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Seeded personal contact detail formula';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'QuickPaint';
  l_formula_text      LONG;
  l_formula_type_id   NUMBER;
  l_ff_exists         VARCHAR2(1);
  l_bg_exists         VARCHAR2(1);

  --
  -- Tests if the formula already exists
  --
  CURSOR csr_ff_exists IS
  SELECT 'Y'
  FROM   ff_formulas_f ff
  WHERE  ff.business_group_id = l_BG_ID
  AND    ff.formula_name = l_FF_NAME;

  --
  -- Tests if the Business Group already exists
  --
  CURSOR csr_bg_exists IS
  SELECT 'Y'
  FROM   per_business_groups_perf bg
  WHERE  bg.business_group_id = l_BG_ID;

  --
  -- Gets the Formula Type Id
  --
  CURSOR csr_ff_type IS
  SELECT fft.formula_type_id
  FROM   ff_formula_types fft
  WHERE  fft.formula_type_name = l_FF_TYPE;

BEGIN

  --
  -- Check that the Business Group Id was passed in
  --
  IF l_BG_ID IS NULL THEN
    RAISE e_no_bg_id;
  END IF;

  --
  -- Finish processing if the Formula already exists for this Business Group
  --
  OPEN  csr_ff_exists;
  FETCH csr_ff_exists INTO l_ff_exists;
  CLOSE csr_ff_exists;

  IF l_ff_exists IS NOT NULL THEN
    RAISE e_ff_exists;
  END IF;

  --
  -- Check the Business Group exists
  --
  OPEN  csr_bg_exists;
  FETCH csr_bg_exists INTO l_bg_exists;
  CLOSE csr_bg_exists;

  IF l_bg_exists IS NULL THEN
    RAISE e_bg_not_exists;
  END IF;

  --
  -- Get the Formula Type; error if it cannot be found
  --
  OPEN  csr_ff_type;
  FETCH csr_ff_type INTO l_formula_type_id;
  CLOSE csr_ff_type;

  IF l_formula_type_id IS NULL THEN
    RAISE e_ff_type_not_found;
  END IF;

  --
  -- Set the Formula Text
  --
  l_formula_text := '
/*==============================================================================

Formula Name: XXAS_COM_BASIC_CONTACT
Formula Type: QuickPaint
Description:  Seeded personal contact detail formula

==============================================================================*/


/* +-------------------< SECTION 1: DEFAULTS >-------------------------------+
   Defaults must be specified for every item listed in the Inputs section. When
   a value is not set on the contact detail it will be defaulted to the default
   specified here.                                                          */

  Default for Email_or_Phone           is ''Unknown''
  Default for Contact_Data             is ''Unknown''
  Default for Phone_Id                 is 0
  Default for Phone_Type_Code          is ''Unknown''
  Default for Phone_Type               is ''Unknown''


/* +-------------------< SECTION 2: INPUTS >---------------------------------+
   This formula lists all the available items that you can use in this
   type of formula.  Email_or_Phone is set to either ''EMAIL'' or ''PHONE''.
   Phone_id, Phone_Type_Code and Phone_Type are defaulted when the
   Email_or_Phone is EMAIL.                                                 */

  Inputs are Email_or_Phone, Contact_Data, Phone_Id,
             Phone_Type_Code, Phone_Type


/* +-------------------< SECTION 3: OTHER DEFAULTS >-------------------------+
   It is good practice to define default values for the Formula Outputs and
   other variables that are used in this formula.  This helps to guard against
   empty outputs being returned and keeps the formula tidy.

   The Formula Outputs are the reason for this Formula: it gives you finite
   control to set the Outputs to whatever values you choose.  You can use
   information in the Formula Inputs to conditionally set the Outputs - you
   can see examples of this in Section 4.

   Here they are defaulted to initial values and later tweaked.             */

  Show               = ''Yes''         /* Determines whether this contact is
                                        visible or not.                     */
  Order_Sequence     =  99           /* Determines the order in which
                                        contact numbers are displayed, with
                                        1 being the highest.                */
  Contact_Type_Code  = ''Landline''    /* Choose from Email, Fax, Cell,
                                        Mobile and Landline.                */
  Contact_Type_Name  = ''Placeholder'' /* Free-text description for this type
                                        of contact.                         */
  Contact_Data       =  Contact_Data /* The actual contact data such as an
                                        email address or phone number.      */
  Contact_Data_Notes = ''Placeholder'' /* Supplementary notes for the contact
                                        data.                               */
  Preferred_Flag     = ''No''          /* Set to Yes or No to indicate if
                                        this contact is the preference.     */

/* Preferred_Notes is optional and only set if Preferred_Flag is Yes */
/* Preferred_Notes    = ''Preferred Contact Number'' */


/* +-------------------< SECTION 4: MAIN LOGIC >-----------------------------+
   This contains logic that adjusts the Outputs according to certain
   conditions.  For example, the below sets the type of contact based on
   the given details.                                                       */

  If Email_or_Phone = ''EMAIL'' Then
  (
    /* Set details for type email */
    Order_Sequence     = 1
    Contact_Type_Code  = ''Email''
    Contact_Type_Name  = xxas_get_message
                         (''XXAS'',''XXAS_EMP_EMAIL'')
    Contact_Data_Notes = Contact_Type_Name
  )
  Else
  (
    If upper(Phone_Type_Code) Like ''H%'' Or
       upper(Phone_Type) Like ''%HOME%'' Then
    (
      /* Hide all home numbers */
      Show = ''No''
    )
    Else If upper(Phone_Type_Code) Like ''%F'' Or
            upper(Phone_Type) Like ''%FAX%'' Then
    (
      /* Set details for fax numbers */
      Order_Sequence     = 4
      Contact_Type_Code  = ''Fax''
      Contact_Type_Name  = Phone_Type
      Contact_Data_Notes = Contact_Type_Name
    )
    Else If upper(Phone_Type_Code) Like ''M'' Or
            upper(Phone_Type) Like ''%CELL%'' Or
            upper(Phone_Type) Like ''%MOBILE%'' Then
    (
      /* Set details for cell/mobile numbers */
      Order_Sequence     = 3
      Contact_Type_Code  = ''Cell''
      Contact_Type_Name  = Phone_Type
      Contact_Data_Notes = Contact_Type_Name
    )
    Else
    (
      /* Assume the default of landline */
      Contact_Type_Code = ''Landline''
      Contact_Type_Name = Phone_Type
      Contact_Data_Notes = Contact_Type_Name

      If upper(Phone_Type_Code) Like ''W%'' Or
         upper(Phone_Type) Like ''%WORK%'' Then
      (
        /* set as preferred number if it''s a work number */
        Order_Sequence  = 2
        Preferred_Flag  = ''Yes''
        Preferred_Notes = ''Preferred Contact Number''
      )
      Else
      (
        Order_Sequence = 5
      )
    )
  )


/* +-------------------< SECTION 5: RETURN OUTPUTS >-------------------------+
   This section returns the Outputs.  The Preferred_Notes Output is only
   returned if Preferred_Flag is set to Yes.                                */

  If Preferred_Flag = ''Yes'' Then
  (
    RETURN Show, Order_Sequence, Contact_Type_Code, Contact_Type_Name
          ,Contact_Data, Contact_Data_Notes
          ,Preferred_Flag, Preferred_Notes
  )
  Else
  (
    RETURN Show, Order_Sequence, Contact_Type_Code, Contact_Type_Name
          ,Contact_Data, Contact_Data_Notes
          ,Preferred_Flag
  )
';


  BEGIN
    --
    -- Create the Fast Formula
    --
    INSERT INTO ff_formulas_f
      (formula_id
      ,effective_start_date
      ,effective_end_date
      ,business_group_id
      ,formula_type_id
      ,formula_name
      ,description
      ,formula_text)
    VALUES
      (ff_formulas_s.nextval
      ,l_START
      ,l_END
      ,l_BG_ID
      ,l_formula_type_id
      ,l_FF_NAME
      ,l_FF_DESC
      ,l_formula_text);

    COMMIT;

    EXCEPTION
      WHEN others THEN
        ROLLBACK;
        RAISE;
    END;

    --
    -- Compile the Fast Formula
    --
    xxas_util_pkg.compile_ff
      (p_user_id => l_USER_ID
      ,p_ff_type => l_FF_TYPE
      ,p_ff_name => l_FF_NAME);

EXCEPTION

  WHEN e_no_bg_id THEN
    RAISE no_data_found;

  WHEN e_ff_exists THEN
    NULL;

  WHEN e_bg_not_exists THEN
    RAISE no_data_found;

  WHEN e_ff_type_not_found THEN
    RAISE no_data_found;

END;
/
COMMIT;
EXIT;
