/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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('0001-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_EMP_VIEW_CHANGE_ASG_EDIT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud EmployeeCenter Change Assignment Edit';
  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_CHANGE_ASG_EDIT
Formula Type: Applaud Assignment
Description:  Edit current and historical Change Assignment Actions

==============================================================================*/
  Default for Contingent_Worker_Value_in        is 'N'
  Default for Effective_Start_Date_Value_in     is 'Unknown'
  Default for Effective_End_Date_Value_in       is 'Unknown'
  Default for Has_Future_Dated_Records_Value_in is 'N'
  Default for Change_Asg_Action_Gp_Value_in     is 'Unknown'
  Default for Effective_Date_Option_Value_in    is 'Unknown'
  Default for Effective_From_Ldw_Value_In       is '0001/01/01 00:00:00' (date)
  Default for Effective_From_Fad_Value_In       is '0001/01/01 00:00:00' (date)

  Inputs are Contingent_Worker_Value_in
            ,Effective_Start_Date_Value_in
            ,Effective_End_Date_Value_in
            ,Has_Future_Dated_Records_Value_in
            ,Change_Asg_Action_Gp_Value_in
            ,Effective_Date_Option_Value_in
            ,Effective_From_Ldw_Value_In
            ,Effective_From_Fad_Value_In

  DATE_FORMAT_MASK    = 'FMDD-Mon-YYYY'

  If Contingent_Worker_Value_in = 'Y' Then
  (
    Grade_Ladder_Pgm_Id_Visible          = 'false'
    Special_Ceiling_Step_Id_Visible      = 'false'
    Payroll_Id_Visible                   = 'false'
    Pay_Basis_Id_Visible                 = 'false'
    Sal_Review_Period_Visible            = 'false'
    Sal_Review_Period_Frequency_Visible  = 'false'
    Perf_Review_Period_Visible           = 'false'
    Perf_Review_Period_Frequency_Visible = 'false'
    Asg_Status_Visible                   = 'false'
    Asg_Category_Visible                 = 'false'
    Employee_Category_Visible            = 'false'
    ST_Grp_Visible                       = 'false'
    Probation_Period_Visible             = 'false'
    Probation_Unit_Visible               = 'false'
    Date_Probation_End_Visible           = 'false'
    Notice_Period_Visible                = 'false'
    Notice_Period_Uom_Visible            = 'false'
    Project_Title_Visible                = 'false' /* cwk worker */
    Change_Reason_Visible                = 'false'
    Cwk_Change_Reason_Visible            = 'false' /* cwk worker */
    Projected_Assignment_End_Visible     = 'false' /* cwk worker */
    Collective_Agreement_Id_Visible      = 'false'
    Bargaining_Unit_Code_Visible         = 'false'
    Labour_Union_Member_Flag_Visible     = 'false'
    Cagr_Grp_Visible                     = 'false'
  )
  Else
  (
    Cwk_Asg_Status_Visible           = 'false'
    Cwk_Asg_Category_Visible         = 'false'
    Cwk_Change_Reason_Visible        = 'false'
    Projected_Assignment_End_Visible = 'false'
    Vendor_Id_Visible                = 'false'
    Vendor_Site_Id_Visible           = 'false'
    Vendor_Employee_Number_Visible   = 'false'
    Vendor_Assignment_Number_Visible = 'false'
    Po_Header_Id_Visible             = 'false'
    Po_Line_Id_Visible               = 'false'
  )

  If Has_Future_Dated_Records_Value_in = 'Y' Then
  (
    Effective_From_Ldw_Description = 'Effective Date must be between ' + to_char(to_date(Effective_Start_Date_Value_in, 'YYYY/MM/DD HH24:MI:SS'), DATE_FORMAT_MASK) + ' and ' + to_char(to_date(Effective_End_Date_Value_in, 'YYYY/MM/DD HH24:MI:SS'), DATE_FORMAT_MASK) + '.'
    Effective_From_Fad_Description = Effective_From_Ldw_Description
  )
  Else
  (
    Effective_From_Ldw_Description = 'Effective Date cannot be before ' + to_char(to_date(Effective_Start_Date_Value_in, 'YYYY/MM/DD HH24:MI:SS'), DATE_FORMAT_MASK) + '.'
    Effective_From_Fad_Description = Effective_From_Ldw_Description
  )

  If Effective_Date_Option_Value_in was defaulted then
  (
    Effective_From_Ldw_Value = trunc(sysdate)
    Effective_From_Fad_Value = trunc(sysdate)
  )
  Else If Effective_Date_Option_Value_in = 'E' Then
  (
    If Effective_From_Ldw_Value_in was not defaulted Then
    (
      Effective_From_Ldw_Value = Effective_From_Ldw_Value_in
    )
  )
  Else If Effective_Date_Option_Value_in = 'A' Then
  (
    Effective_From_Fad_Enabled  = 'false'
    Effective_From_Fad_Value    = trunc(sysdate)
  )

  If Change_Asg_Action_Gp_Value_in = 'HOURS' Then
  (
    Normal_Hours_Visible       = 'true'
    Frequency_Visible          = 'true'
    Time_Normal_Start_Visible  = 'true'
    Time_Normal_Finish_Visible = 'true'
    Work_At_Home_Visible       = 'true'
  )
  Else If Change_Asg_Action_Gp_Value_in = 'LOCATION' Then
  (
    Location_Id_Visible = 'true'
  )
  Else If Change_Asg_Action_Gp_Value_in = 'JOB' Then
  (
    Job_Id_Visible      = 'true'
    Position_Id_Visible = 'true'

    If Contingent_Worker_Value_in = 'Y' Then
    (
      Grade_Id_Visible = 'false'
    )
    Else
    (
      Grade_Id_Visible = 'true'
    )
  )

  /* Hide all flexfield segments by default */
  Ass_Attribute_Category_Visible = 'false'
  Ass_Attribute1_Visible = 'false'
  Ass_Attribute2_Visible = 'false'
  Ass_Attribute3_Visible = 'false'
  Ass_Attribute4_Visible = 'false'
  Ass_Attribute5_Visible = 'false'
  Ass_Attribute6_Visible = 'false'
  Ass_Attribute7_Visible = 'false'
  Ass_Attribute8_Visible = 'false'
  Ass_Attribute9_Visible = 'false'
  Ass_Attribute10_Visible = 'false'
  Ass_Attribute11_Visible = 'false'
  Ass_Attribute12_Visible = 'false'
  Ass_Attribute13_Visible = 'false'
  Ass_Attribute14_Visible = 'false'
  Ass_Attribute15_Visible = 'false'
  Ass_Attribute16_Visible = 'false'
  Ass_Attribute17_Visible = 'false'
  Ass_Attribute18_Visible = 'false'
  Ass_Attribute19_Visible = 'false'
  Ass_Attribute20_Visible = 'false'
  Ass_Attribute21_Visible = 'false'
  Ass_Attribute22_Visible = 'false'
  Ass_Attribute23_Visible = 'false'
  Ass_Attribute24_Visible = 'false'
  Ass_Attribute25_Visible = 'false'
  Ass_Attribute26_Visible = 'false'
  Ass_Attribute27_Visible = 'false'
  Ass_Attribute28_Visible = 'false'
  Ass_Attribute29_Visible = 'false'
  Ass_Attribute30_Visible = 'false'

  Return Grade_Id_Visible
        ,Grade_Ladder_Pgm_Id_Visible
        ,Special_Ceiling_Step_Id_Visible
        ,Payroll_Id_Visible
        ,Pay_Basis_Id_Visible
        ,Sal_Review_Period_Visible
        ,Sal_Review_Period_Frequency_Visible
        ,Perf_Review_Period_Visible
        ,Perf_Review_Period_Frequency_Visible
        ,Asg_Status_Visible
        ,Asg_Category_Visible
        ,Employee_Category_Visible
        ,ST_Grp_Visible
        ,Probation_Period_Visible
        ,Probation_Unit_Visible
        ,Date_Probation_End_Visible
        ,Notice_Period_Visible
        ,Notice_Period_Uom_Visible
        ,Project_Title_Visible
        ,Change_Reason_Visible
        ,Cwk_Change_Reason_Visible
        ,Projected_Assignment_End_Visible
        ,Collective_Agreement_Id_Visible
        ,Bargaining_Unit_Code_Visible
        ,Labour_Union_Member_Flag_Visible
        ,Cagr_Grp_Visible
        ,Cwk_Asg_Status_Visible
        ,Cwk_Asg_Category_Visible
        ,Vendor_Id_Visible
        ,Vendor_Site_Id_Visible
        ,Vendor_Employee_Number_Visible
        ,Vendor_Assignment_Number_Visible
        ,Po_Header_Id_Visible
        ,Po_Line_Id_Visible
        ,Effective_From_Ldw_Value
        ,Effective_From_Fad_Value
        ,Effective_From_Ldw_Description
        ,Effective_From_Fad_Description
        ,Effective_From_Fad_Enabled
        ,Normal_Hours_Visible
        ,Frequency_Visible
        ,Time_Normal_Start_Visible
        ,Time_Normal_Finish_Visible
        ,Work_At_Home_Visible
        ,Location_Id_Visible
        ,Job_Id_Visible
        ,Position_Id_Visible
        ,Ass_Attribute_Category_Visible
        ,Ass_Attribute1_Visible
        ,Ass_Attribute2_Visible
        ,Ass_Attribute3_Visible
        ,Ass_Attribute4_Visible
        ,Ass_Attribute5_Visible
        ,Ass_Attribute6_Visible
        ,Ass_Attribute7_Visible
        ,Ass_Attribute8_Visible
        ,Ass_Attribute9_Visible
        ,Ass_Attribute10_Visible
        ,Ass_Attribute11_Visible
        ,Ass_Attribute12_Visible
        ,Ass_Attribute13_Visible
        ,Ass_Attribute14_Visible
        ,Ass_Attribute15_Visible
        ,Ass_Attribute16_Visible
        ,Ass_Attribute17_Visible
        ,Ass_Attribute18_Visible
        ,Ass_Attribute19_Visible
        ,Ass_Attribute20_Visible
        ,Ass_Attribute21_Visible
        ,Ass_Attribute22_Visible
        ,Ass_Attribute23_Visible
        ,Ass_Attribute24_Visible
        ,Ass_Attribute25_Visible
        ,Ass_Attribute26_Visible
        ,Ass_Attribute27_Visible
        ,Ass_Attribute28_Visible
        ,Ass_Attribute29_Visible
        ,Ass_Attribute30_Visible

]';


  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;
