/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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('0001-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_PERF_QUESTION_EDIT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud performance questionnaire edit formula';
  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_PERF_QUESTION_EDIT 
   Formula Type: Applaud Assignment
   Description:  ELC manager rating edit formula
  ==============================================================================*/
  DEFAULT FOR Perf_Review_Form_Id_Value_In IS -1
  DEFAULT FOR Person_Id_Value_In           IS -1
  DEFAULT FOR Assignment_Id_Value_In       IS -1
  DEFAULT FOR Questionnaire_Id_Value_In    IS -1

  INPUTS ARE Perf_Review_Form_Id_Value_In
            ,Person_Id_Value_in
            ,Assignment_Id_Value_In
            ,Questionnaire_Id_Value_In

  /* 
  XXAS_PERF_QUESTION_ACCESS - Checks if the current step of the review 
     has access to the questionnaire(Overall Comments) fields, based on the review's template setup.
     Returns 'Y' if allowed and 'N' if not.
     Examples:
             1. XXAS_PERF_QUESTION_ACCESS(Perf_Review_Form_Id_Value_In,'V') 
                 - This checks if the Questionnaire(Overall comments in the review's template setup) 
                   is set to anything but 'Don't show' 
             2. XXAS_PERF_QUESTION_ACCESS(Perf_Review_Form_Id_Value_In,'R') 
                 - If review's template setup allows 'Rating' 
                    (say,'Comments and rating' or 'Comments, questionnaire and rating')
             3. XXAS_PERF_QUESTION_ACCESS(Perf_Review_Form_Id_Value_In,'C') 
                 - If review's template setup allows 'Comments' 
                    (say,'Comments only' or 'Comments and questionnaire' or 
                         'Comments and rating' or 'Comments, questionnaire and rating')
   */
   
   /* Note: 1. To set any field to visible set <field's column name>_visible = 'true'
            2. To hide any field set <field's column name>_visible = 'false' */
   
   

  IF Perf_Review_Form_Id_Value_In WAS NOT DEFAULTED AND 
     XXAS_PERF_QUESTION_ACCESS(Perf_Review_Form_Id_Value_In,'V') = 'N' THEN /* Don't show */
    (
      Rating_Visible     = 'false'
      Comments_Visible   = 'false'
      Question1_Visible  = 'false'
      Question2_Visible  = 'false'
      Question3_Visible  = 'false'
      Question4_Visible  = 'false'
      Question5_Visible  = 'false'
      Question6_Visible  = 'false'
      Question7_Visible  = 'false'
      Question8_Visible  = 'false'
      Question9_Visible  = 'false'
      Question10_Visible = 'false'
      Question11_Visible = 'false'
      Question12_Visible = 'false'
      Question13_Visible = 'false'
      Question14_Visible = 'false'
      Question15_Visible = 'false'
      Question16_Visible = 'false'
      Question17_Visible = 'false'
      Question18_Visible = 'false'
      Question19_Visible = 'false'
      Question20_Visible = 'false'
      Question21_Visible = 'false'
      Question22_Visible = 'false'
      Question23_Visible = 'false'
      Question24_Visible = 'false'
      Question25_Visible = 'false'
      Question26_Visible = 'false'
      Question27_Visible = 'false'
      Question28_Visible = 'false'
      Question29_Visible = 'false'
      Question30_Visible = 'false'
      Question31_Visible = 'false'
      Question32_Visible = 'false'
      Question33_Visible = 'false'
      Question34_Visible = 'false'
      Question35_Visible = 'false'
      Question36_Visible = 'false'
      Question37_Visible = 'false'
      Question38_Visible = 'false'
      Question39_Visible = 'false'
      Question40_Visible = 'false'
      Question41_Visible = 'false'
      Question42_Visible = 'false'
      Question43_Visible = 'false'
      Question44_Visible = 'false'
      Question45_Visible = 'false'
      Question46_Visible = 'false'
      Question47_Visible = 'false'
      Question48_Visible = 'false'
      Question49_Visible = 'false'
      Question50_Visible = 'false'
      Question51_Visible = 'false'
      Question52_Visible = 'false'
      Question53_Visible = 'false'
      Question54_Visible = 'false'
      Question55_Visible = 'false'
      Question56_Visible = 'false'
      Question57_Visible = 'false'
      Question58_Visible = 'false'
      Question59_Visible = 'false'
      Question60_Visible = 'false'    
    )
  IF Perf_Review_Form_Id_Value_In WAS NOT DEFAULTED AND 
     XXAS_PERF_QUESTION_ACCESS(Perf_Review_Form_Id_Value_In,'Q') = 'Y' THEN /* Show Questionnaire*/
    ( Question1_Visible  = 'true'
      Question2_Visible  = 'true'
      Question3_Visible  = 'true'
      Question4_Visible  = 'true'
      Question5_Visible  = 'true'
      Question6_Visible  = 'true'
      Question7_Visible  = 'true'
      Question8_Visible  = 'true'
      Question9_Visible  = 'true'
      Question10_Visible = 'true'
      Question11_Visible = 'true'
      Question12_Visible = 'true'
      Question13_Visible = 'true'
      Question14_Visible = 'true'
      Question15_Visible = 'true'
      Question16_Visible = 'true'
      Question17_Visible = 'true'
      Question18_Visible = 'true'
      Question19_Visible = 'true'
      Question20_Visible = 'true'
      Question21_Visible = 'true'
      Question22_Visible = 'true'
      Question23_Visible = 'true'
      Question24_Visible = 'true'
      Question25_Visible = 'true'
      Question26_Visible = 'true'
      Question27_Visible = 'true'
      Question28_Visible = 'true'
      Question29_Visible = 'true'
      Question30_Visible = 'true'
      Question31_Visible = 'true'
      Question32_Visible = 'true'
      Question33_Visible = 'true'
      Question34_Visible = 'true'
      Question35_Visible = 'true'
      Question36_Visible = 'true'
      Question37_Visible = 'true'
      Question38_Visible = 'true'
      Question39_Visible = 'true'
      Question40_Visible = 'true'
      Question41_Visible = 'true'
      Question42_Visible = 'true'
      Question43_Visible = 'true'
      Question44_Visible = 'true'
      Question45_Visible = 'true'
      Question46_Visible = 'true'
      Question47_Visible = 'true'
      Question48_Visible = 'true'
      Question49_Visible = 'true'
      Question50_Visible = 'true'
      Question51_Visible = 'true'
      Question52_Visible = 'true'
      Question53_Visible = 'true'
      Question54_Visible = 'true'
      Question55_Visible = 'true'
      Question56_Visible = 'true'
      Question57_Visible = 'true'
      Question58_Visible = 'true'
      Question59_Visible = 'true'
      Question60_Visible = 'true'    
    )
    ELSE
    ( Question1_Visible  = 'false'
      Question2_Visible  = 'false'
      Question3_Visible  = 'false'
      Question4_Visible  = 'false'
      Question5_Visible  = 'false'
      Question6_Visible  = 'false'
      Question7_Visible  = 'false'
      Question8_Visible  = 'false'
      Question9_Visible  = 'false'
      Question10_Visible = 'false'
      Question11_Visible = 'false'
      Question12_Visible = 'false'
      Question13_Visible = 'false'
      Question14_Visible = 'false'
      Question15_Visible = 'false'
      Question16_Visible = 'false'
      Question17_Visible = 'false'
      Question18_Visible = 'false'
      Question19_Visible = 'false'
      Question20_Visible = 'false'
      Question21_Visible = 'false'
      Question22_Visible = 'false'
      Question23_Visible = 'false'
      Question24_Visible = 'false'
      Question25_Visible = 'false'
      Question26_Visible = 'false'
      Question27_Visible = 'false'
      Question28_Visible = 'false'
      Question29_Visible = 'false'
      Question30_Visible = 'false'
      Question31_Visible = 'false'
      Question32_Visible = 'false'
      Question33_Visible = 'false'
      Question34_Visible = 'false'
      Question35_Visible = 'false'
      Question36_Visible = 'false'
      Question37_Visible = 'false'
      Question38_Visible = 'false'
      Question39_Visible = 'false'
      Question40_Visible = 'false'
      Question41_Visible = 'false'
      Question42_Visible = 'false'
      Question43_Visible = 'false'
      Question44_Visible = 'false'
      Question45_Visible = 'false'
      Question46_Visible = 'false'
      Question47_Visible = 'false'
      Question48_Visible = 'false'
      Question49_Visible = 'false'
      Question50_Visible = 'false'
      Question51_Visible = 'false'
      Question52_Visible = 'false'
      Question53_Visible = 'false'
      Question54_Visible = 'false'
      Question55_Visible = 'false'
      Question56_Visible = 'false'
      Question57_Visible = 'false'
      Question58_Visible = 'false'
      Question59_Visible = 'false'
      Question60_Visible = 'false'    
    )
    IF Perf_Review_Form_Id_Value_In WAS NOT DEFAULTED AND 
     XXAS_PERF_QUESTION_ACCESS(Perf_Review_Form_Id_Value_In,'R') = 'Y' THEN/* Show Rating*/
    (
      Rating_Visible     = 'true'
    )
    ELSE 
    (
      Rating_Visible     = 'false'
    )
    IF Perf_Review_Form_Id_Value_In WAS NOT DEFAULTED AND 
     XXAS_PERF_QUESTION_ACCESS(Perf_Review_Form_Id_Value_In,'C') = 'Y' THEN/* Show Comments*/
    (
      Comments_Visible   = 'true'
    ) 
    ELSE 
    (
      Comments_Visible     = 'false'
    )    
  
  RETURN   Rating_Visible
          ,Comments_Visible
          ,Question1_Visible
          ,Question2_Visible
          ,Question3_Visible
          ,Question4_Visible
          ,Question5_Visible
          ,Question6_Visible
          ,Question7_Visible
          ,Question8_Visible
          ,Question9_Visible
          ,Question10_Visible
          ,Question11_Visible
          ,Question12_Visible
          ,Question13_Visible
          ,Question14_Visible
          ,Question15_Visible
          ,Question16_Visible
          ,Question17_Visible
          ,Question18_Visible
          ,Question19_Visible
          ,Question20_Visible
          ,Question21_Visible
          ,Question22_Visible
          ,Question23_Visible
          ,Question24_Visible
          ,Question25_Visible
          ,Question26_Visible
          ,Question27_Visible
          ,Question28_Visible
          ,Question29_Visible
          ,Question30_Visible
          ,Question31_Visible
          ,Question32_Visible
          ,Question33_Visible
          ,Question34_Visible
          ,Question35_Visible
          ,Question36_Visible
          ,Question37_Visible
          ,Question38_Visible
          ,Question39_Visible
          ,Question40_Visible
          ,Question41_Visible
          ,Question42_Visible
          ,Question43_Visible
          ,Question44_Visible
          ,Question45_Visible
          ,Question46_Visible
          ,Question47_Visible
          ,Question48_Visible
          ,Question49_Visible
          ,Question50_Visible
          ,Question51_Visible
          ,Question52_Visible
          ,Question53_Visible
          ,Question54_Visible
          ,Question55_Visible
          ,Question56_Visible
          ,Question57_Visible
          ,Question58_Visible
          ,Question59_Visible
          ,Question60_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;
