/* $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_OVERVIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Assignment Overview 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_OVERVIEW
Formula Type: Applaud Assignment
Description:  Assignment Overview 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

  /* Defaults for Database Items used here */
  DEFAULT FOR PER_KNOWN_AS   IS '' ''
  DEFAULT FOR PER_FIRST_NAME IS '' ''
  DEFAULT FOR PER_LAST_NAME  IS '' ''

  /* Constants */
  UNKNOWN = ''Unknown''

  /* Default assignment information */
  Status           = UNKNOWN
  FTE              = UNKNOWN
  Job_Title        = UNKNOWN
  Worker_Type      = UNKNOWN
  Job_Name         = UNKNOWN
  Position_Name    = UNKNOWN
  Department       = UNKNOWN
  Service          = UNKNOWN
  Location         = UNKNOWN
  Location_Address = UNKNOWN
  Latitude         = 0
  Longtitude       = 0

  /* Retrieve assignment information from the assignment FF */
  result = xxas_get_assignment_details
             (Status, FTE, Job_Title, Worker_Type, Job_Name, Position_Name, Department
             ,Service, Location, Location_Address, Latitude, Longtitude)

  /* Retrieve up to 3 contact details */
  Contact_Type_Code  = UNKNOWN
  Contact_Type_Name  = UNKNOWN
  Contact_Data       = UNKNOWN
  Contact_HTML       = ''<br/>''

  /* Get the first contact detail */
  result = xxas_get_contact_details
             (1, Contact_Type_Code, Contact_Type_Name, Contact_Data)

  If result = 0 Then
  (
      If Contact_Type_Code = ''EMAIL'' THEN
         (Contact_Data = ''<a href="mailto:'' + Contact_Data + ''">'' + Contact_Data + ''</a>'')

      Contact_HTML = Contact_Data + ''<br/>''
  )

  /* Get the second contact detail */
  result = xxas_get_contact_details
             (2, Contact_Type_Code, Contact_Type_Name, Contact_Data)

  If result = 0 Then
  (
      If Contact_Type_Code = ''EMAIL'' THEN
         (Contact_Data = ''<a href="mailto:'' + Contact_Data + ''">'' + Contact_Data + ''</a>'')

      Contact_HTML = Contact_HTML + ''<b>'' + Contact_Type_Name + '': </b>'' + Contact_Data + ''<br/>''
  )

  /* Get the third contact detail */
  result = xxas_get_contact_details
             (3, Contact_Type_Code, Contact_Type_Name, Contact_Data)

  If result = 0 Then
   (
      If Contact_Type_Code = ''EMAIL'' THEN
         (Contact_Data = ''<a href="mailto:'' + Contact_Data + ''">'' + Contact_Data + ''</a>'')

      Contact_HTML = Contact_HTML + ''<b>'' + Contact_Type_Name + '': </b>'' + Contact_Data + ''<br/>''
  )

  /* Set the HTML.  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, ''HTML'')
       2. FND Message owning application (''XXAS'')
       3. FND Message Name (eg, ''XXAS_ORG_VIEW_ASG_OVERVIEW'')
       4. Up to 30 optional pairs of FND Message tokens and values
          (eg, ''JOB_TITLE'', Job_Title)

     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. */
  HTML = xxas_set_long_output
           (''HTML'', ''XXAS'', ''XXAS_ORG_VIEW_ASG_OVERVIEW''
           ,''JOB_TITLE'', Job_Title
           ,''CONTACT_HTML'', Contact_HTML
           ,''LOCATION'', Location
           ,''LOCATION_ADDRESS'', Location_Address)

  /* 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_XML''
               ,''TEXT'', Person_Name)

  Title_XML = xxas_set_long_output
               (''TITLE_XML'', ''XXAS'', ''XXAS_ORG_VIEW_EXP_SUB_XML''
               ,''TEXT'', Job_Title)

  /* Return the Outputs. All 3 of these Outputs are ignored because they''re also set
     above using the xxas_set_long_output function so are only set here for clarity. */

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