/* $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_EMP_VIEW_MTD_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'My Training Details 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);

  --
  -- 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 := q'[
/*==============================================================================
Formula Name: XXAS_EMP_VIEW_MTD_VIEW
Formula Type: Applaud Assignment
Description:  My Training History
==============================================================================*/

  Default for Enrollment_Status is 'Unknown'
  Default for Course_Name       is 'Course unknown'
  Default for Course_Start_Date is '0001/01/01 00:00:00' (date)
  Default for Course_End_Date   is '0001/01/01 00:00:00' (date)
  Default for Duration          is 0
  Default for Training_Provider is 'Unknown'
  Default for Training_City     is 'Unknown'

  Inputs are Enrollment_Status, Course_Name, Course_Start_Date, Course_End_Date,
             Duration, Training_Provider, Training_City

  COLOR_GREEN      = '#499324'
  COLOR_AMBER      = '#FF9900'
  COLOR_RED        = '#CC0000'
  COLOR_DEEP_RED   = '#FF0000'
  COLOR_GREY       = '#333333'
  COLOR_BLUE       = '#3366CC'
  DATE_FORMAT_MASK = 'FMDD Mon YYYY'

  /*************************************************************************/
  /*  BLOCK #1 - Change to meet your requirements for summary rows         */
  /*************************************************************************/

   /* Derive the status color */
   Color = 'None'
   If upper(Enrollment_Status) like '%ATTENDED%' Then
     (Color = COLOR_GREEN)
   Else If upper(Enrollment_Status) like '%ENROLLED%' Then
     (Color = COLOR_AMBER)
   Else If upper(Enrollment_Status) like '%WAITLIST%' Then
     (Color = COLOR_DEEP_RED)
   Else if upper(Enrollment_Status) like '%REQUEST%' Then
     (Color = COLOR_GREY)
     
   If Color <> 'None' And Color <> COLOR_GREEN Then
   (
     Status_Color = Color
     Status_Text = Enrollment_Status
   )
  
   /* Simple (and basic) detection of whtther a class is eLearning or not */
   eLearning = 'N'
   If Duration was defaulted Then
   (
     Summary_Icon = 'laptop'
     eLearning = 'Y'
   )
   Else
     (Summary_Icon = 'teacher')
     
   Dates = 'Null'
   If Course_Start_Date was not defaulted Then
   (
     Dates = to_char(Course_Start_Date, DATE_FORMAT_MASK)
   )
   If Course_End_Date was not defaulted Then
   (
     If Dates = 'Null' Then
       (Dates = to_char(Course_End_Date, DATE_FORMAT_MASK))
     Else
       (Dates = Dates + ' - ' + to_char(Course_End_Date, DATE_FORMAT_MASK))
   )
   If Duration was not defaulted Then
   (
     If Duration = 1 Then
       (Duration_Text = xxas_get_message('XXAS', 'XXAS_EMP_ONE_DAY'))
     Else
       (Duration_Text = to_char(Duration) + ' ' + xxas_get_message('XXAS', 'XXAS_EMP_DAYS_I'))
       
     If Dates = 'Null' Then
       (Dates = Duration_Text)
     Else
       (Dates = Dates + ' (' + Duration_Text + ')')
   )
   
   If Dates <> 'Null' Then
     (Summary_Context = Dates)

   Summary_Text = Course_Name
   If upper(Enrollment_Status) Like '%CANCEL%' Then
     (Summary_Text = '<s>' + Summary_Text + '</s>')

   If Enrollment_Status was not defaulted Then
   (
     If Color <> 'None' Then
       (Status = '<font color="' + Color + '">' + Enrollment_Status + '</font>')
     Else
       (Status = Enrollment_Status)

     If Dates = 'Null' Then
       (Summary_Context = Status)
     Else
       (Summary_Context = Status + ': ' + Summary_Context)
   )
   
   Location = 'Null'
   If eLearning = 'Y' Then
   (
     Location = 'eLearning'
   )
   Else
   (
     If Training_Provider was not defaulted Then
       (Location = Training_Provider)
     If Training_City was not defaulted And Training_City <> Training_Provider Then
     (
       If Location = 'Null' Then
         (Location = Training_City)
       Else
         (Location = Location + ', ' + Training_City)
     )
   )
   
   If Location <> 'Null' Then
     (Summary_Message = Location)

     
  /*************************************************************************/
  /*  End of BLOCK #1                                                      */
  /*************************************************************************/

  /* Set the update/delete permissions */
  Update_Allowed = 'N'
  Delete_Allowed = 'N'

  /*************************************************************************/
  /*  RETURN BLOCK - Remove ItemX_Prompt/HTML attributes that aren't used  */
  /*************************************************************************/

  Return Status_Color
        ,Status_Text
        ,Summary_Icon
        ,Summary_Context
        ,Summary_Text
        ,Summary_Message
        ,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;
