/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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_PI_PER_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Personal Information Basic Details EmployeeCenter 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 := '
/*==============================================================================

Formula Name: XXAS_EMP_PI_PER_VIEW
Formula Type: Applaud Assignment
Description:  Personal Information Basic Details EmployeeCenter View

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

  Default for Workflow_Status_Code       is ''NONE''
  Default for Workflow_Status_Meaning    is ''NONE''
  Default for Datetrack_Update_Mode      is ''NONE''
  Default for Effective_Date_p           is ''0001/01/01 00:00:00'' (date)
  Default for Current_Employee_Flag      is ''N''
  Default for Current_Npw_Flag           is ''N''
  Default for Full_Name                  is ''Unknown''
  Default for Marital_Status_Meaning     is ''Unknown''
  Default for Per_Information_Category   is ''Unknown''
  Default for Per_Information1_Meaning   is ''Unknown''
  Default for Date_of_Birth              is ''0001/01/01 00:00:00'' (date)
  Default for Age                        is 0
  Default for Sex_Meaning                is ''Unknown''
  Default for National_Identifier        is ''Unknown''
  Default for Nationality                is ''Unknown''
  Default for Nationality_Meaning        is ''Unknown''
  Default for Employee_Number            is ''Unknown''
  Default for Npw_Number                 is ''Unknown''
  Default for Email_Address              is ''Unknown''
  Default for Full_Name_p                is ''Unknown''
  Default for Marital_Status_Meaning_p   is ''Unknown''
  Default for Date_of_Birth_p            is ''0001/01/01 00:00:00'' (date)
  Default for Age_p                      is 0
  Default for Sex_Meaning_p              is ''Unknown''
  Default for National_Identifier_p      is ''Unknown''
  Default for Nationality_p              is ''Unknown''
  Default for Nationality_Meaning_p      is ''Unknown''
  Default for Per_information1_Meaning_p is ''Unknown''
  Default for Employee_Number_p          is ''Unknown''
  Default for Npw_Number_p               is ''Unknown''
  Default for Email_Address_p            is ''Unknown''

  Inputs are Workflow_Status_Code, Workflow_Status_Meaning,
             Datetrack_Update_Mode, Effective_Date_p,
             Full_Name, Marital_Status_Meaning, Per_Information_Category,
             Per_Information1_Meaning, Date_of_Birth, Age, Sex_Meaning,
             National_Identifier, Nationality, Nationality_Meaning,
             Employee_Number, Npw_Number, Current_Employee_Flag, Current_Npw_Flag,
             Email_Address, Full_Name_p, Marital_Status_Meaning_p,
             Per_Information1_Meaning_p, Date_of_Birth_p, Age_p, Sex_Meaning_p,
             National_Identifier_p, Nationality_p, Nationality_Meaning_p,
             Employee_Number_p, Npw_Number_p, Email_Address_p

  COLOR_BLACK    = ''#000000''
  COLOR_GREEN    = ''#499324''
  COLOR_AMBER    = ''#FF9900''
  COLOR_RED      = ''#CC0000''
  COLOR_DEEP_RED = ''#FF0000''
  COLOR_GREY     = ''#333333''
  COLOR_BLUE     = ''#3366CC''
  START_OF_TIME  = to_date(''0001/01/01 00:00:00'', ''YYYY/MM/DD HH24:MI:SS'')
  END_OF_TIME    = to_date(''4712/12/31 00:00:00'', ''YYYY/MM/DD HH24:MI:SS'')
  DATE_MASK      = ''FmDd Month YYYY''
  MSG_APP        = ''XXAS''

  Status_Color      = COLOR_BLACK
  Current_Field_Num = 1
  Pending           = ''N''

  /* If this is pending, set the status color and context */
  If Workflow_Status_Code was not defaulted then
  (
     Pending = ''Y''
     If Workflow_Status_Code = ''RFC'' Then
       (Status_Color = COLOR_RED)
     Else If Workflow_Status_Code = ''ERROR'' Then
       (Status_Color = COLOR_DEEP_RED)
     Else If Workflow_Status_Code = ''SFL'' Then
       (Status_Color = COLOR_GREY)
     Else If Workflow_Status_Code = ''APPROVED'' Then
       (Status_Color = COLOR_GREEN)
     Else
       (Status_Color = COLOR_AMBER)

    /* Set the Status field */
    Status = xxas_get_message(MSG_APP, ''XXAS_COM_WF_STATUS_HTML''
                            ,''STATUS_COLOR'', Status_Color
                            ,''STATUS'', Workflow_Status_Meaning)

    If Datetrack_Update_Mode = ''UPDATE'' Then
      (Status = Status
              + ''<br/>''
              + xxas_get_message
                  (MSG_APP, ''XXAS_COM_DT_UPDATE_HTML''
                  ,''EFFECTIVE_DATE'', to_char(Effective_Date_p, DATE_MASK)))
    Else
      (Status = Status
              + ''<br/>''
              + xxas_get_message(MSG_APP, ''XXAS_COM_DT_CORRECT_HTML''))

    Func_Result = xxas_emp_set_text_field
    (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_RB_TCM_STATUS''), Status)
  )

  /* Set the Person Name field */
  Func_Result = xxas_emp_set_text_field
    (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_NAME''), Full_Name
    ,Pending, Full_Name_p, Status_Color, ''Unknown'')

  /* Set the Employee Number field, hidden by default
  If Current_Employee_Flag = ''Y'' Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_EMPLOYEE_NUMBER''), Employee_Number
      ,Pending, Employee_Number_p, Status_Color, ''Unknown''))
  */

  /* Set the Contractor Number field, hidden by default
  If Current_Npw_Flag = ''Y'' Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_CONTRACTOR_NUMBER''), Npw_Number
      ,Pending, Npw_Number_p, Status_Color, ''Unknown''))
  */

  /* Set the Date of Birth field */
  If Date_of_Birth was not defaulted Or Date_of_Birth_p was not defaulted Then
  (
    DoB = xxas_get_message
      (MSG_APP, ''XXAS_COM_DOB_AND_AGE''
      ,''DATE_OF_BIRTH'', to_char(Date_of_Birth, DATE_MASK)
      ,''AGE'', to_char(floor(Age)))
    DoB_p = xxas_get_message
      (MSG_APP, ''XXAS_COM_DOB_AND_AGE''
      ,''DATE_OF_BIRTH'', to_char(Date_of_Birth_p, DATE_MASK)
      ,''AGE'', to_char(floor(Age_p)))
    DoB_Default = xxas_get_message
      (MSG_APP, ''XXAS_COM_DOB_AND_AGE''
      ,''DATE_OF_BIRTH'', to_char(START_OF_TIME, DATE_MASK)
      ,''AGE'', to_char(0))
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_DOB''), DoB
      ,Pending, DoB_p, Status_Color, Dob_Default)
  )

  /* Set the Gender field */
  If Sex_Meaning was not defaulted Or
     Sex_Meaning_p was not defaulted Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_GENDER''), Sex_Meaning
      ,Pending, Sex_Meaning_p, Status_Color, ''Unknown''))

  /* Set the Nationality field. */
  If Nationality_Meaning was not defaulted Or Nationality_Meaning_p was not defaulted Then
  (
    If Pending = ''Y'' And Nationality_Meaning <> Nationality_Meaning_p Then
      (Func_Result = xxas_emp_set_text_field
        (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_NATIONALITY''), Nationality_Meaning
        ,Pending, Nationality_Meaning_p, Status_Color, ''Unknown''))
    Else
    (
      /* Get the country code of citizenship */
      Country_Code = get_lookup_meaning(''XXAS_NATIONALITY_COUNTRY_MAP'', Nationality)
      If IsNull(Country_Code) = ''N'' Then
      (
         Flag_HTML = xxas_get_message
                      (MSG_APP, ''XXAS_COM_COUNTRY_FLAG_HTML''
                      ,''COUNTRY_CODE'', Country_Code)
         Nationality_HTML = Nationality_Meaning + ''<br/>'' + Flag_HTML
      )
      Else
        (Nationality_HTML = Nationality_Meaning)

      Func_Result = xxas_emp_set_text_field
        (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_NATIONALITY''), Nationality_HTML)
    )
  )

  /* Set the National Identifier field */
  If National_Identifier was not defaulted Or
     National_Identifier_p was not defaulted Then
    (NI_Label = xxas_national_identifier_label()
     Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, NI_Label, National_Identifier
      ,Pending, National_Identifier_p, Status_Color,''Unknown''))

  /* Set the Ethnic Origin field for US */
  If per_information_category = ''US'' And
    (Per_Information1_Meaning was not defaulted Or
     Per_Information1_Meaning_p was not defaulted) Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_ETHNIC_ORIGIN''), Per_Information1_Meaning
      ,Pending, Per_Information1_Meaning_p, Status_Color,''Unknown''))

  /* Set the update/delete permissions to employee-only
     (prevent manager updates out the box)  */
  Edit_Allowed = xxas_ess_mss_policy(''Y'', ''N'')
  Update_Allowed = Edit_Allowed
  Delete_Allowed = ''N''

  Return Update_Allowed, Delete_Allowed
';


  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;
