/* $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_COM_BASIC_ASG';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Seeded assignment configuration formula';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'QuickPaint';
  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_COM_BASIC_ASG
Formula Type: QuickPaint
Description:  Seeded assignment configuration 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.                                                          */

  Alias sysdate As Todays_Date

  Default for Assignment_Number         is ''Unknown''
  Default for Assignment_Start_Date     is ''0001/01/01 00:00:00'' (date)
  Default for Assignment_End_Date       is ''4712/12/31 00:00:00'' (date)
  Default for Person_Id                 is -1
  Default for Assignment_Type           is ''Unknown''
  Default for Primary_Flag              is ''Unknown''
  Default for Organization_Id           is -1
  Default for Organization_Name         is ''Unknown''
  Default for Organization_Hours        is 0
  Default for Business_Group_Hours      is 0
  Default for Grade_Id                  is -1
  Default for Grade_Name                is ''Unknown''
  Default for Position_Id               is -1
  Default for Position_Name             is ''Unknown''
  Default for Position_Hours            is 0
  Default for Job_Id                    is -1
  Default for Job_Name                  is ''Unknown''
  Default for Payroll_Id                is -1
  Default for Payroll_Name              is ''Unknown''
  Default for Salary_Basis_id           is -1
  Default for Salary_Basis              is ''Unknown''
  Default for Assignment_Status_Type_Id is -1
  Default for Assignment_Status         is ''Unknown''
  Default for Assignment_Category       is ''Unknown''
  Default for Employee_Category         is ''Unknown''
  Default for Normal_Hours              is 0
  Default for Frequency                 is ''Unknown''
  Default for Years_Service             is 0
  Default for Months_Service            is 0
  Default for Budget_FTE                is 0
  Default for Budget_Percent            is 0
  Default for Location_Id               is -1
  Default for Location_Name             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 sysdate                   is ''0001/01/01 00:00:00'' (date)

  /* +-------------------< SECTION 2: INPUTS >---------------------------------+
   This formula lists all the available items that you can use in this
   type of formula.                                                           */

  Inputs are Assignment_Number, Assignment_Start_Date, Assignment_End_Date
            ,Person_id, Assignment_Type, Primary_Flag, Organization_Id
            ,Organization_Name, Organization_Hours, Business_Group_Hours
            ,Grade_Id, Grade_Name, Position_Id
            ,Position_Name, Position_Hours, Job_Id, Job_Name
            ,Payroll_Id, Payroll_Name, Salary_Basis_Id, Salary_Basis
            ,Assignment_Status_Type_Id, Assignment_Status, Assignment_Category
            ,Employee_Category, Normal_Hours, Frequency, Years_Service
            ,Months_Service, Budget_FTE, Budget_Percent, Location_Id
            ,Location_Name, Address_Line_1, Address_Line_2, Address_Line_3
            ,Town_or_City, Postal_Code, Country, Latitude, Longtitude


  /* +-------------------< SECTION 3: OTHER DEFAULTS >-------------------------+
   It is good practice to define default values for Formula Outputs that you
   always expect to have a value.  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 some are defaulted to initial values and later tweaked.               */

  FTE              = 0                  /* The full time equivalence of this
                                           assignment.                         */
  Status           = ''Unknown''          /* The status of this employee''s job. */
  Job_Title        = ''Unknown''          /* The assignment''s full job title,
                                           typically includes a department
                                           qualifier.                         */
  Department       =  Organization_Name /* The assignment''s department.       */
  Service          =  ''Unknown''         /* The amount of service this assignment
                                           has.                               */


/* +-------------------< SECTION 4: MAIN LOGIC >-----------------------------+
   This contains logic that adjusts the Outputs according to certain
   conditions.                                                              */

  /* Set the status to primary or secondary based on the primary flag */
  If Primary_Flag = ''Y'' Then
      (Status = xxas_get_message (''XXAS'',''XXAS_EMP_PRIMARY_JOB''))
  Else
      (Status = xxas_get_message (''XXAS'',''XXAS_EMP_SECONDARY_JOB''))

  /* Set the Worker Type */
  Has_Worker_Type = ''N''
  If Assignment_Start_Date > Todays_Date Then
  (
      Has_Worker_Type = ''Y''
      Worker_Type = xxas_get_message (''XXAS'',''XXAS_EMP_NOT_STARTED'')
  )
  Else If Assignment_End_Date < trunc(Todays_Date) Then
  (
      Has_Worker_Type = ''Y''
      If Assignment_Type = ''E'' Then
        (Worker_Type = xxas_get_message (''XXAS'',''XXAS_EMP_EX_EMPLOYEE''))
      Else
        (Worker_Type = xxas_get_message (''XXAS'',''XXAS_EMP_EX_CONTRACTOR''))
  )
  Else If Assignment_Type = ''C'' Then
  (
      Has_Worker_Type = ''Y''
      Worker_Type = xxas_get_message (''XXAS'',''XXAS_EMP_CONTRACTOR'')
  )

	/* Set the FTE status */
  If Budget_FTE was not defaulted Then
  (
      Status = Status + '' ('' + to_char(round(Budget_FTE * 100)) + ''%)''
      FTE = Budget_FTE
  )
  Else If Budget_Percent was not defaulted Then
  (
      Status = Status + '' ('' + to_char(round(Budget_Percent)) + ''%)''
      FTE = round(Budget_Percent / 100, 2)
  )
  Else
  (
      /* Try to derive FTE; need contractual hours first.  Check on position,
         organization and business group for FTE hours. */
      FTE_Weekly_Hours = 0

      If Position_Hours > 0 Then
          (FTE_Weekly_Hours = Position_Hours)
      Else If Organization_Hours > 0 Then
          (FTE_Weekly_Hours = Organization_Hours)
      Else If Business_Group_Hours > 0 Then
          (FTE_Weekly_Hours = Business_Group_Hours)
      Else
      (
          /* This will unlikely to ever be hard-coded and will vary contract
             by contract.  If it''s not on the position, org or BG and the
             FTE budget values are not set, this is most probably on some
             DFF so this hard-coded value would need to be replaced with
             a function call. */
          FTE_Weekly_Hours = 38
      )
      If Normal_Hours > 0 Then
          (FTE = round(Normal_Hours / FTE_Weekly_Hours, 2))
  )

  /* Avoid over-budget jobs */
  If FTE > 1 Then
    (FTE = 1)

  /* Attempt to calculate FTE if

  /* Set the job title to a combination of position/job and department */
  If Position_Name was not defaulted Then
  (
      Position = Position_Name
      Job_Title = Position + '', '' + Department
  )
  If Job_Name was not defaulted Then
  (
      Job = Job_Name
      If Position_Name was defaulted Then
          (
		   HTML = xxas_emp_set_long_output
           (''Job_Title'',''XXAS'',''XXAS_JOB_DEP'', ''JOB'',Job,''DEPT'' ,Department ))
  )
  If Position_Name was defaulted and Job_Name was defaulted Then
      (Job_Title = Department)

  /* Set the service band according to the amount of service */
  If Years_Service < 1 Then
      (Service = xxas_get_message (''XXAS'',''XXAS_EMP_SERVICE_ONE''))
  Else If Years_Service >= 1 and Years_Service < 2 Then
      (Service = xxas_get_message (''XXAS'',''XXAS_EMP_SERVICE_TWO''))
  Else If Years_Service >= 2 and Years_Service < 5 Then
      (Service = xxas_get_message (''XXAS'',''XXAS_EMP_SERVICE_THREE''))
  Else If Years_Service >= 5 and Years_Service < 10 Then
      (Service = xxas_get_message (''XXAS'',''XXAS_EMP_SERVICE_FOUR''))
  Else
      (Service = xxas_get_message (''XXAS'',''XXAS_EMP_SERVICE_FIVE''))

  /* Set the location name if set */
  If Location_Name was not defaulted Then
      (Location = Location_Name)

  /* Concatenate the location address. An ''Address_Populated'' variable is
     used to ensure the separator is only added when necessary.  */
  Address_Populated = ''N''
  If Address_Line_1 was not defaulted Then
  (
      Location_Address = Address_Line_1
      Address_Populated = ''Y''
  )
  If Address_Line_2 was not defaulted Then
  (
      If Address_Populated = ''Y'' Then
	      (Location_Address = Location_Address + '', '' + Address_Line_2)
	  Else
	  (
	      Address_Populated = ''Y''
	      Location_Address = Address_Line_2
	  )
  )
  If Address_Line_3 was not defaulted Then
  (
      If Address_Populated = ''Y'' Then
	      (Location_Address = Location_Address + '', '' + Address_Line_3)
	  Else
	  (
	      Address_Populated = ''Y''
	      Location_Address = Address_Line_3
	  )
  )
  If Town_or_City was not defaulted Then
  (
      If Address_Populated = ''Y'' Then
	      (Location_Address = Location_Address + '', '' + Town_or_City)
	  Else
	  (
	      Address_Populated = ''Y''
	      Location_Address = Town_or_City
	  )
  )
  If Country was not defaulted Then
  (
      If Address_Populated = ''Y'' Then
	      (Location_Address = Location_Address + '', '' + Country)
	  Else
	  (
	      Address_Populated = ''Y''
	      Location_Address = Country
	  )
  )


/* +-------------------< SECTION 5: RETURN OUTPUTS >-------------------------+
   This section returns the Outputs.  Not all Outputs need to be returned:
   if you don''t want something to display don''t return the Output.          */

    If Has_Worker_Type = ''Y'' Then
        (Return FTE, Status, Job_Title, Service, Location, Location_Address
               ,Job, Position, Department, Worker_Type)
    Else
        (Return FTE, Status, Job_Title, Service, Location, Location_Address
               ,Job, Position, Department)

';


  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;
