/* $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_QUA_PRO_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud EmployeeCenter Professional Qualifications 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_QUA_PRO_VIEW
Formula Type: Applaud Assignment
Description:  Applaud EmployeeCenter Professional Qualifications View
==============================================================================*/

  Default for Transaction_Type           is ''NONE''
  Default for Workflow_Status_Code       is ''NONE''
  Default for Workflow_Status_Meaning    is ''NONE''
  Default for Awarding_Body              is ''Unknown''
  Default for Awarding_Body_p            is ''Unknown''
  Default for Title                      is ''Unknown''
  Default for Title_p                    is ''Unknown''
  Default for Pq_Category_Meaning        is ''Unknown''
  Default for Pq_Category_Meaning_p      is ''Unknown''
  Default for Rank                       is ''Unknown''
  Default for Rank_p                     is ''Unknown''
  Default for Qualification_Name         is ''Unknown''
  Default for Qualification_Name_p       is ''Unknown''
  Default for Grade_Attained             is ''Unknown''
  Default for Grade_Attained_p           is ''Unknown''
  Default for Status_Meaning             is ''Unknown''
  Default for Status_Meaning_p           is ''Unknown''
  Default for Start_Date                 is ''0001/01/01 00:00:00'' (date)
  Default for Start_Date_p               is ''0001/01/01 00:00:00'' (date)
  Default for End_Date                   is ''0001/01/01 00:00:00'' (date)
  Default for End_Date_p                 is ''0001/01/01 00:00:00'' (date)

  Inputs are Transaction_Type, Workflow_Status_Code, Workflow_Status_Meaning,
             Awarding_Body, Awarding_Body_p,
             Title, Title_p,
             Pq_Category_Meaning, Pq_Category_Meaning_p,
             Rank, Rank_p,
             Qualification_Name, Qualification_Name_p,
             Grade_Attained, Grade_Attained_p,
             Status_Meaning, Status_Meaning_p,
             Awarded_Date, Awarded_Date_p,
             Start_Date, Start_Date_p, End_Date, End_Date_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 Mon 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 Transaction_Type = ''INSERT'' then
       (Status_Text  = xxas_get_message(''XXAS'',''XXAS_EMP_NEW''))
     Else If Transaction_Type = ''UPDATE'' then
       (Status_Text  = xxas_get_message(''XXAS'',''XXAS_EMP_UPDATED''))
     Else
       (Status_Text  = xxas_get_message(''XXAS'',''XXAS_EMP_DELETED''))

     Status_Text = Status_Text + Workflow_Status_Meaning

     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 Summary Context */
    Rslt = xxas_emp_set_long_output
           (''STATUS_COLOR'', ''XXAS'', ''XXAS_COM_TEXT''
           ,''TEXT'', Status_Color)
    Summary_Context = ''<font color="'' + Status_Color + ''">'' + Status_Text + ''</font>''
    Detail_Header_Context = Summary_Context
  )

  Has_Grade = ''N''
  If Grade_Attained_p was not defaulted Then
    (Has_Grade = ''Y''
     Grade = Grade_Attained_p)
  Else If Grade_Attained was not defaulted Then
    (Has_Grade = ''Y''
     Grade = Grade_Attained)

  Has_Title = ''N''
  If Title_p was not defaulted Then
    (Has_Title = ''Y''
     Qual_Title = Title_p)
  Else If Title was not defaulted Then
    (Has_Title = ''Y''
     Qual_Title = Title)

  Has_Qual_Status = ''N''
  If Status_Meaning_p was not defaulted Then
    (Has_Qual_Status = ''Y''
     Qual_Status = Status_Meaning_p)
  Else If Status_Meaning was not defaulted Then
    (Has_Qual_Status = ''Y''
     Qual_Status = Status_Meaning)

  Has_Start = ''N''
  If Start_Date_p was not defaulted Then
    (Has_Start = ''Y''
     Start = Start_Date_p)
  Else If Start_Date was not defaulted Then
    (Has_Start = ''Y''
     Start = Start_Date)

  Has_End = ''N''
  If End_Date_p was not defaulted Then
    (Has_End = ''Y''
     End = End_Date_p)
  Else If End_Date was not defaulted Then
    (Has_End = ''Y''
     End = End_Date)

  If Has_Start = ''Y'' And Has_End = ''Y'' Then
    (Dates = to_Char(Start, DATE_MASK) + '' to '' + to_Char(End, DATE_MASK))
  Else If Has_Start = ''Y'' Then
     (Dates = xxas_get_message(''XXAS'',''XXAS_EMP_STARTED'',''START_DATE'',to_Char(Start, DATE_MASK)))
  Else If Has_End = ''Y'' Then
     (Dates = xxas_get_message(''XXAS'',''XXAS_EMP_FINISHED'',''END_DATE'',to_Char(End, DATE_MASK)))

  If Qualification_Name_p was not defaulted Then
    (Qualification = Qualification_Name_p)
  Else
    (Qualification = Qualification_Name)

  If Awarding_Body_p was not defaulted Then
    (Qual_Awarding_Body = Awarding_Body_p)
  Else
    (Qual_Awarding_Body = Awarding_Body)


  /* Set the Summary Text */
  Summary_Text = Qualification
  If Awarding_Body_p was not defaulted or Awarding_Body was not defaulted Then
    (Summary_Text = Summary_Text + '', '' + Qual_Awarding_Body)
  If Has_Title = ''Y'' Then
    (Summary_Text = Qual_Title + '' '' + Summary_Text )
  If Has_Grade = ''Y'' Then
    (Summary_Text = Grade + '' - '' + Summary_Text )

  /* Set the Summary Message */
  Has_Summary_Message = ''Y''
  If Has_Qual_Status = ''Y'' And (Has_Start = ''Y'' Or Has_End = ''Y'') Then
    (Summary_Message = Dates + '', '' + Qual_Status)
  Else If Has_Qual_Status = ''Y'' Then
    (Summary_Message = Qual_Status)
  Else If Has_Start = ''Y'' Or Has_End = ''Y'' Then
    (Summary_Message = Dates)
  Else
    (Has_Summary_Message = ''N'')

  Detail_Header_Text = Summary_Text
  If Has_Summary_Message = ''Y'' Then
    (Detail_Header_Message = Summary_Message)

  /* Set the Title field */
  If Title_p was not defaulted or Title was not defaulted Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_RB_EMP_EXP_JT''), Title
      ,Pending, Title_p, Status_Color,xxas_get_message(''XXAS'',''XXAS_EMP_UNKNOWN'')))

  /* Set the Grade field */
  If Grade_Attained_p was not defaulted or Grade_Attained was not defaulted Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_GRADE''), Grade_Attained
      ,Pending, Grade_Attained_p, Status_Color, xxas_get_message(''XXAS'',''XXAS_EMP_UNKNOWN'')))

  /* Set the Qualification field */
  If Qualification_Name_p was not defaulted or Qualification_Name was not defaulted Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_QUALIFICATION''),Qualification_Name
      ,Pending, Qualification_Name_p, Status_Color, xxas_get_message(''XXAS'',''XXAS_EMP_UNKNOWN'')))

  /* Set the Category field */
  If Pq_Category_Meaning_p was not defaulted or Pq_Category_Meaning was not defaulted Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_RB_TCM_GROUP''),Pq_Category_Meaning
      ,Pending, Pq_Category_Meaning_p, Status_Color, xxas_get_message(''XXAS'',''XXAS_EMP_UNKNOWN'')))

  /* Set the Awarding_Body field */
  If Awarding_Body_p was not defaulted or Awarding_Body was not defaulted Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_AWARDING_BODY''),Awarding_Body
      ,Pending, Awarding_Body_p, Status_Color, xxas_get_message(''XXAS'',''XXAS_EMP_UNKNOWN'')))

  /* Set the Rank field */
  If Rank_p was not defaulted or Rank was not defaulted Then
    (Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message(''XXAS'',''XXAS_EMP_RANK''), Rank
      ,Pending, Rank_p, Status_Color,xxas_get_message(''XXAS'',''XXAS_EMP_UNKNOWN'')))

  /* Set the update/delete permissions */
  Edit_Allowed = xxas_ess_mss_policy(''Y'', ''Y'')
  Update_Allowed = Edit_Allowed
  Delete_Allowed = Edit_Allowed

  RETURN Update_Allowed, Delete_Allowed,
         Summary_Context, Summary_Text, Summary_Message,
         Detail_Header_Context, Detail_Header_Text, Detail_Header_Message
';


  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;
