/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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_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_SRC_ASSIGNMENT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Seeded assignment search criteria';
  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);

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

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

  --
  -- 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_SRC_ASSIGNMENT
Formula Type: Applaud Assignment
Description:  Seeded assignment search criteria

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


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

  Default for Person_Id_Input                is -1
  Default for Global_Name_Input              is ''Unknown''
  Default for First_Name_Input               is ''Unknown''
  Default for Known_As_Input                 is ''Unknown''
  Default for Last_Name_Input                is ''Unknown''
  Default for Employee_Number_Input          is ''Unknown''
  Default for Contingent_Worker_Number_Input is ''Unknown''
  Default for Email_Address_Input            is ''Unknown''
  Default for National_Identifier_Input      is ''Unknown''
  Default for Person_Type_Input              is ''Unknown''
  Default for Assignment_Type_Input          is ''Unknown''
  Default for Grade_Input                    is ''Unknown''
  Default for Payroll_Input                  is ''Unknown''
  Default for Assignment_Status_Input        is ''Unknown''
  Default for Assignment_Category_Input      is ''Unknown''
  Default for Employee_Category_Input        is ''Unknown''
  Default for Salary_Basis_Input             is ''Unknown''
  Default for Is_Current_Worker_Input        is ''Unknown''
  Default for Is_Ex_Worker_Input             is ''Unknown''
  Default for Is_Future_Worker_Input         is ''Unknown''
  Default for Asg_Effective_Start_Date_Input is ''Unknown''
  Default for Asg_Effective_End_Date_Input   is ''Unknown''
  Default for Assignment_Start_Date_Input    is ''0001/01/01 00:00:00'' (date)
  Default for Assignment_End_Date_Input      is ''4712/12/31 00:00:00'' (date)
  Default for Primary_Flag_Input             is ''Unknown''
  Default for Delimiter_Input                is ''Unknown''
  
/* +-------------------< SECTION 2: INPUTS >---------------------------------+
   This formula lists all the available items that you can use in this
   formula.                                                                 */

  Inputs are Person_Id_Input, Global_Name_Input, First_Name_Input,
             Known_As_Input, Last_Name_Input, Employee_Number_Input,
             Contingent_Worker_Number_Input, Email_Address_Input,
             National_Identifier_Input, Person_Type_Input,
             Assignment_Type_Input, Grade_Input, Payroll_Input,
             Assignment_Status_Input, Assignment_Category_Input,
             Employee_Category_Input, Salary_Basis_Input,
             Is_Current_Worker_Input, Is_Ex_Worker_Input,
             Is_Future_Worker_Input, Assignment_Start_Date_Input,
             Assignment_End_Date_Input, Primary_Flag_Input,
             Delimiter_Input
   
/* +-------------------< SECTION 3: INITIATE VARIABLES >---------------------+
   Provides defaults for variables used in this formula.                    */
   
  /* Constants */
  NO_VALUE = ''XXASNODATA''   
  
 /* Default assignment information */
  Status_Value           = ''Unknown''
  FTE_Value              = ''Unknown''
  Job_Title_Value        = ''Unknown''
  Worker_Type_Value      = ''Unknown''
  Job_Value              = ''Unknown''
  Position_Value         = ''Unknown''
  Department_Value       = ''Unknown''
  Service_Value          = ''Unknown''
  Location_Value         = ''Unknown''
  Location_Address_Value = ''Unknown''
  Latitude_Value         = 0
  Longtitude_Value       = 0
   
/* +-------------------< SECTION 4: RETRIEVE VALUES >------------------------+
   Retrieve additional information not passed into this formula.            */
    
  /* Retrieve assignment information from the assignment FF */
  result = xxas_get_assignment_details
             (Status_Value, FTE_Value, Job_Title_Value, Worker_Type_Value
             ,Job_Value, Position_Value, Department_Value, Service_Value
             ,Location_Value, Location_Address_Value
             ,Latitude_Value, Longtitude_Value)
          
/* +-------------------< SECTION 5: SET OUTPUTS >----------------------------+
   This section uses the Inputs and any other Database Items or Fast Formula
   Functions to set the Outputs to their desired values.                    */

   /* The Short_Name must always be set, so we set it here even if
      Global_Name_Input was defaulted (ie, is ''Unknown''). */
   If Known_As_Input was not defaulted Then
     (Short_Name = Known_As_Input + '' '' + Last_Name_Input)
   Else
     (Short_Name = First_Name_Input + '' '' + Last_Name_Input)
   Person_Name = Short_Name
   
   /* Most values can be set directly from the inputs.
      Only set the values if they were not defaulted, otherwise the text
      ''Unknown'' would be indexed and searchable. */
   If Job_Title_Value <> ''Unknown'' then Result_Text = Job_Title_Value
   If Worker_Type_Value <> ''Unknown'' then Worker_Type = Worker_Type_Value
   If Email_Address_Input was not defaulted then Email = Email_Address_Input
   If Position_Value <> ''Unknown'' then Position = Position_Value
   If Job_Value <> ''Unknown'' then Job = Job_Value
   If Department_Value <> ''Unknown'' then Department = Department_Value
   If Location_Value <> ''Unknown'' then Location = Location_Value
   If Service_Value <> ''Unknown'' then Service = Service_Value
   If Employee_Number_Input was not defaulted then EN = Employee_Number_Input
   If Contingent_Worker_Number_Input was not defaulted then
     CN = Contingent_Worker_Number_Input
   If National_Identifier_Input was not defaulted then
     NI = National_Identifier_Input
   If Person_Type_Input was not defaulted then Person_Type = Person_Type_Input
   If Payroll_Input was not defaulted then Payroll = Payroll_Input
   If Grade_Input was not defaulted then Grade = Grade_Input
   If Assignment_Category_Input was not defaulted then
     Assignment_Category = Assignment_Category_Input
   If Employee_Category_Input was not defaulted then
     Employee_Category = Employee_Category_Input
   If FTE_Value <> ''Unknown'' then FTE = FTE_Value
   
   /* Retrieve additional information, typically those that can be longer
      than 250 characters from Fast Formula functions. */
   If xxas_src_get_about_me(Person_Id_Input) <> NO_VALUE then
     (rslt = xxas_src_set_long_output
               (''ABOUT_ME'', xxas_src_get_about_me(Person_Id_Input)))

   If xxas_src_get_tags(Person_Id_Input, Delimiter_Input) <> NO_VALUE then
     (rslt = xxas_src_set_long_output
               (''TAGS'', xxas_src_get_tags(Person_Id_Input, Delimiter_Input)))

   If xxas_src_get_qualifications
        (Person_Id_Input, Delimiter_Input) <> NO_VALUE then
     (rslt = xxas_src_set_long_output
               (''QUALIFICATIONS'' 
               ,xxas_src_get_qualifications
                 (Person_Id_Input, Delimiter_Input)))

   If xxas_src_get_competencies
        (Person_Id_Input, Delimiter_Input) <> NO_VALUE then
     (rslt = xxas_src_set_long_output
              (''COMPETENCIES''
              ,xxas_src_get_competencies
                (Person_Id_Input, Delimiter_Input)))  

   /* Determine whether this assignment should be indexed or not */
   If Is_Current_Worker_Input = ''Y'' Then
       (Include_In_Search = ''Y'')
   Else       
       (Include_In_Search = ''N'')
   
/* +-------------------< SECTION 6: RETURN OUTPUTS >-------------------------+
   This section returns the Outputs.  You should always return the Short_Name
   and Result_Text Outputs because these show in the search results.
   The rest of the Outputs vary according to the searh criteria you have 
   setup for this Business Group.                                           */

RETURN Include_In_Search, Short_Name, Result_Text, Worker_Type,
       Person_Name, Email, Position, Job, Department, Location, Service,
       EN, CN, NI, Person_Type, Payroll, Grade, Assignment_Category,
       Employee_Category, FTE

';


  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;
