/* $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_EMP_VIEW_ASG_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Seeded work information - 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);

  --
  -- 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 := q'[
/*==============================================================================
Formula Name: XXAS_EMP_VIEW_ASG_VIEW
Formula Type: Applaud Assignment
Description:  Seeded work information - view
==============================================================================*/

  Default for PER_CURRENT_EMP       is 'Unknown'  
  Default for PER_CURRENT_CWK       is 'Unknown' 
  Default for PER_EMP_NUMBER        is 'Unknown'
  Default for PER_CWK_NUMBER        is 'Unknown'
  Default for ASG_GRADE             is 'Unknown'
  Default for ASG_SALARY            is 0
  Default for ASG_HOURS             is 0
  Default for ASG_FREQ              is 'Unknown'
  Default for ASG_SALARY_BASIS_NAME is 'Unknown'
  Default for ASG_FTE_VALUE         is 0

  UNKNOWN              = 'Unknown'
  DATE_FORMAT          = 'FmDd Month YYYY'
  CURRENCY_FORMAT_MASK = 'FM999G999G999G990D00'
  CURRENCY_SHORT_MASK  = 'FM999G999G999G990'
  
  Current_Field_Num = 1
  
  /* Set security permissions so different data is shown to
     different people. */
  If xxas_ess_mss_policy('Y', 'N') = 'Y' Then
    (Is_Self = 'Y')
  Else
    (Is_Self = 'N')

  If xxas_ess_mss_policy('N', 'Y') = 'Y' Then
    (Is_Manager = 'Y')
  Else
    (Is_Manager = 'N')
  
  /* 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)

  If Is_Self = 'Y' Or Is_Manager = 'Y' Then
  (  
    /* Set the Employee Number */
    If PER_EMP_NUMBER was not defaulted then
      (Func_Result = xxas_emp_set_text_field
        (Current_Field_Num
        ,xxas_get_message('XXAS', 'XXAS_EMP_EMPLOYEE_NUMBER')
        ,PER_EMP_NUMBER)
      )
      
    /* Set the Contractor Number */
    If PER_CWK_NUMBER was not defaulted then
      (Func_Result = xxas_emp_set_text_field
        (Current_Field_Num
        ,xxas_get_message('XXAS', 'XXAS_EMP_CONTRACTOR_NUMBER')
        ,PER_CWK_NUMBER)
      )
  )

  /* Set the Department */
  Func_Result = xxas_emp_set_text_field
    (Current_Field_Num, xxas_get_message('XXAS', 'XXAS_RB_EMP_DEPT'), Department)

  /* Set the Job */
  If IsNull(Job_Name) = 'N' then
  (
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS', 'XXAS_RB_EMP_JOB'), Job_Name)
  )

  /* Set the Position */
  If IsNull(Position_Name) = 'N' then
  (
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num,  xxas_get_message('XXAS','XXAS_RB_EMP_POSITION'), Position_Name)
  )

  If Is_Self = 'Y' Or Is_Manager = 'Y' Then
  (
    /* Set the FTE */
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_FTE_FULL'), Status)

   /* Set the working hours */
    If ASG_HOURS > 0 THEN
    (
      Func_Result = xxas_emp_set_text_field
        (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_WORK_HOURS')
        ,to_char(ASG_HOURS) + ' per ' + ASG_FREQ)
    )

    If ASG_GRADE was not defaulted then
    (
      /* Set the Grade */
      Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_GRADE'), ASG_GRADE)
    )
    
    /* Set the service */
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_RB_EMP_SERVICE'), Service)
    
  )

  If Is_Manager = 'Y' Then
  (
    /* Set the Grade Rate */
    Grade_Min_Char = XXAS_GET_GRADE_RATE_VALUE('MINIMUM')
    Grade_Max_Char = XXAS_GET_GRADE_RATE_VALUE('MAXIMUM')
    If Grade_Min_Char <> 'XXASNODATA' And Grade_Max_Char <> 'XXASNODATA' Then
    (
      Grade_Min = xxas_to_char(to_num(Grade_Min_Char), CURRENCY_SHORT_MASK)
      Grade_Max = xxas_to_char(to_num(Grade_Max_Char), CURRENCY_SHORT_MASK)
      Func_Result = xxas_emp_set_text_field
        (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_GRADE_RANGE')
        ,Grade_Min + ' to ' + Grade_Max)
    )
  )

  Dummy = 'Y'
  RETURN Dummy

]';


  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;
