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

SET VERIFY OFF
SET FEEDBACK OFF
SET ESCAPE '\'
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  e_no_bg_id          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_ORG_VIEW_ORG_OVERVIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Organization Overview Organization Chart View';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Organization';
  l_formula_text      LONG;
  l_formula_type_id   NUMBER;
  l_ff_id             NUMBER;
  l_bg_exists         VARCHAR2(1);

  --
  -- 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 FF Id
  --
  CURSOR csr_ff_id IS
  SELECT ff.formula_id
  FROM   ff_formulas_f ff
  WHERE  ff.business_group_id = l_BG_ID
  AND    ff.formula_name = l_FF_NAME;

  --
  -- 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;

  --
  -- 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;

  --
  -- Fetch the Fast Formula Id
  --
  OPEN  csr_ff_id;
  FETCH csr_ff_id INTO l_ff_id;
  CLOSE csr_ff_id;

  --
  -- 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_ORG_VIEW_ORG_OVERVIEW
Formula Type: Applaud Organization
Description:  Organization Overview Organization Chart View

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

  /* Specify defaults for all items that are used. */

  Default for Organization          is ''Unknown''
  Default for Business_Group        is ''Unknown''
  Default for Organization_Type     is ''Unknown''
  Default for Date_From             is ''0001/01/01 00:00:00'' (date)
  Default for Date_To               is ''4712/12/31 00:00:00'' (date)
  Default for Internal_External     is ''Unknown''
  Default for Location              is ''Unknown''
  Default for Internal_Address_Line is ''Unknown''
  Default for Address_Line_1        is ''Unknown''
  Default for Address_line_2        is ''Unknown''
  Default for Address_Line_3        is ''Unknown''
  Default for Town_or_City          is ''Unknown''
  Default for Postal_Code           is ''Unknown''
  Default for Country               is ''Unknown''
  Default for Latitude              is 0
  Default for Longtitude            is 0
  Default for Actual_FTE            is 0
  Default for Actual_Headcount      is 0

  /* Declare all items that are used. */

  Inputs are Organization, Business_Group, Organization_Type, Date_From (date),
             Date_To (date), Internal_External, Location, Internal_Address_Line,
			 Address_Line_1, Address_Line_2, Address_Line_3, Town_or_City,
			 Postal_Code, Country, Latitude, Longtitude,
			 Actual_FTE, Actual_Headcount

  /* You can include up to 30 list items in this view. Each item consists of
     an optional ''indicator color'' and HTML text for that item.  The HTML of
     each item can itself be multiline, so consider combining related
     information into one list item rather than a separate item for each piece
     of data.

     The ''indicator color'' can be set to one of Aqua, Black, Blue, Fuchsia,
     Gray, Green, Lime, Maroon, Navy, Olive, Purple, Red, Silver, Teal, White,
     Yellow or the HEX value of a colour.*/

  /* Here the first item''s indicator color is red if the Organization is no longer
     effective. */
  If Date_To was not defaulted then
    (Item1_Indicator_Color = ''Red'')

  /* The HTML of item 1 combines the Organization name, Type and whether it''s
     Internal or External. */
  Item1_HTML = ''<b>'' + Organization + ''</b><br/>'' + Organization_Type +
               '' ('' + Internal_External + '')''

  /* Set the Item1_HTML Output.  Fast Formula has an in-built restriction on the length of
     Fast Formula Outputs so if the text of any output might be longer than 255
     characters (including any dynamic text or message tokens) use this
     xxas_set_long_output function instead.  This works around the 255
     character limit and allows up to 4000 characters. This function only
     works for Org Chart views, not in other Fast Formula.

     The function takes the following parameters:
       1. Fast Formula Output name (eg, ''Item1_HTML'')
       2. FND Message owning application (''XXAS'')
       3. FND Message Name (eg, ''XXAS_ORG_VIEW_HTML'')
       4. Up to 30 optional pairs of FND Message tokens and values
          (eg, ''HTML'', Item1_HTML)

     When it is used, you don''t also need to include the Outputs in the ''Return''
     statement at the bottom of the formula, although they are done so here just
     for clarity. */
  Item1_HTML = xxas_set_long_output
                 (''ITEM1_HTML'', ''XXAS'', ''XXAS_ORG_VIEW_HTML'', ''HTML'', Item1_HTML)

  /* The HTML of Item 2 combines the Headcount and FTE for the Org. */
  Item2_HTML = ''<b>''+ xxas_get_message(''XXAS'',''XXAS_EMP_HEADCOUNT_FTE'') + ''</b>'' +
       xxas_get_message(''XXAS'',''XXAS_EMP_HEADCOUNT'')+'':'' + to_char(Actual_Headcount) + ''; '' +
			   xxas_get_message(''XXAS'',''XXAS_EMP_FTE'')+'':'' + to_char(Actual_FTE)
  Item2_HTML = xxas_set_long_output
                 (''ITEM2_HTML'', ''XXAS'', ''XXAS_ORG_VIEW_HTML'', ''HTML'', Item2_HTML)

  /* The HTML of Item 3 includes the location, town/city and Country of the org */
  Item3_HTML = ''<b>'' + Location + ''</b><br/>'' +
               Town_or_City + '', '' + Country
  Item3_HTML = xxas_set_long_output
                 (''ITEM3_HTML'', ''XXAS'', ''XXAS_ORG_VIEW_HTML'', ''HTML'', Item3_HTML)

  /* Set the data that appears when you export an Org Chart:

       Node_XML:  shows as the main text in each node.
       Title_XML: appears if you select ''Text + Subtext'' and it appears in the
                  small bar at the bottom-right of the node.

     The text of these outputs must be valid Open Office XML (OOXML) source code. */
  Node_XML = xxas_set_long_output
               (''NODE_XML'', ''XXAS'', ''XXAS_ORG_VIEW_EXP_NODE_XML''
               ,''TEXT'', Organization)

  Title_XML = xxas_set_long_output
               (''TITLE_XML'', ''XXAS'', ''XXAS_ORG_VIEW_EXP_SUB_XML''
               ,''TEXT'',  xxas_get_message(''XXAS'',''XXAS_EMP_HEADCOUNT'')+'':'' + to_char(Actual_Headcount))

  /* Return the Outputs. Item1_HTML, Item2_HTML, Item3_HTML, Node_XML and Title_XML
     are ignored here because they''re also set using the xxas_set_long_output
     function above; they''re just here for clarity. */

  RETURN Item1_HTML, Item1_Indicator_Color
        ,Item2_HTML
        ,Item3_HTML
        ,Node_XML, Title_XML
';


  BEGIN

    IF l_ff_id IS NULL THEN
      --
      -- 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);

    ELSE
      --
      -- Update the existing Fast Formula text
      -- (if it has multiple date-track versions the text of
      -- all versions is updated).
      --
      UPDATE ff_formulas_f
      SET    formula_text = l_formula_text
            ,description  = l_FF_DESC
      WHERE  formula_id = l_ff_id;

    END IF;

    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_bg_not_exists THEN
    RAISE no_data_found;

  WHEN e_ff_type_not_found THEN
    RAISE no_data_found;

END;
/
COMMIT;
EXIT;
