/* $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_ASG_SALARY';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Assignment Salary Organization Chart View';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Assignment';
  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_ASG_SALARY
Formula Type: Applaud Assignment
Description:  Assignment Salary Organization Chart View

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

  /* Database Item Aliases */
  ALIAS PER_KNOWN_AS                     AS Known_As
  ALIAS PER_FIRST_NAME                   AS First_Name
  ALIAS PER_LAST_NAME                    AS Last_Name
  ALIAS SYSDATE                          AS Todays_Date
  ALIAS ASG_SALARY_BASIS_NAME            AS Salary_Basis
  ALIAS ASG_SALARY                       AS Salary
  ALIAS ASG_SALARY_REVIEW_PERIOD         AS Salary_Review_Period
  ALIAS ASG_SALARY_REVIEW_FREQUENCY      AS Salary_Review_Unit
  ALIAS ASG_LAST_SALARY_DATE             AS Last_Salary_Review
  ALIAS ASG_LAST_PROPOSED_SALARY_PERCENT AS Last_Salary_Percent_Change

  /* Defaults for Database Items used here */
  DEFAULT FOR PER_KNOWN_AS                     IS '' ''
  DEFAULT FOR PER_FIRST_NAME                   IS '' ''
  DEFAULT FOR PER_LAST_NAME                    IS '' ''
  DEFAULT FOR SYSDATE                          IS ''0001/01/01 00:00:00'' (date)
  DEFAULT FOR ASG_SALARY_BASIS_NAME            IS ''Unknown''
  DEFAULT FOR ASG_SALARY                       IS 0
  DEFAULT FOR ASG_SALARY_REVIEW_PERIOD         IS 0
  DEFAULT FOR ASG_SALARY_REVIEW_FREQUENCY      IS ''Unknown''
  DEFAULT FOR ASG_LAST_SALARY_DATE             IS ''0001/01/01 00:00:00'' (date)
  DEFAULT FOR ASG_LAST_PROPOSED_SALARY_PERCENT IS 0

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

  /* Salary */
  Item1_Prompt = xxas_get_message(''XXAS'',''XXAS_EMP_SALARY'')
  Item1_HTML   = ''$'' + to_char(Salary) +''<br>''+
                 xxas_get_message(''XXAS'',''XXAS_EMP_LAST_CHANGE'')+ ''</br>''+
				 to_char(round(Last_Salary_Percent_Change)) + ''%''+'' ''+ xxas_get_message(''XXAS'',''XXAS_EMP_ON'')+'' ''

  If to_number(to_char(Todays_Date, ''YYYY'')) -
     to_number(to_char(Last_Salary_Review, ''YYYY'')) > 2 Then
      (Item1_HTML = Item1_HTML + ''<font color="#FF4000">'' +
                    to_char(Last_Salary_Review, ''fmDD Mon YYYY'') +
					''</font>'')
  Else
      (Item1_HTML = Item1_HTML + to_char(Last_Salary_Review, ''fmDD Mon YYYY''))

  /* 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)

  Item2_Prompt = xxas_get_message(''XXAS'',''XXAS_EMP_SALARY_BASIS'')
  Item2_HTML   = Salary_Basis

  Item3_Prompt = xxas_get_message(''XXAS'',''XXAS_EMP_REVIEW_PERIOD'')
  Item3_HTML   = to_char(Salary_Review_Period) + '' '' + Salary_Review_Unit

  /* Set this person''s name */
  If Known_As Was Not Defaulted Then
      (Person_Name = Known_As + '' '' + Last_Name)
  Else
      (Person_Name = First_Name + '' '' + Last_Name)

  /* 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_2LI_XML''
               ,''TEXT1'', Person_Name
               ,''TEXT2'', ''$'' + to_char(Salary))

  Title_XML = xxas_set_long_output
               (''TITLE_XML'', ''XXAS'', ''XXAS_ORG_VIEW_EXP_SUB_XML''
              ,''TEXT'', ''Last Change: '' + to_char(round(Last_Salary_Percent_Change)) + ''%'')


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

  RETURN Item1_Prompt, Item1_HTML
        ,Item2_Prompt, Item2_HTML
        ,Item3_Prompt, 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;
