/* $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_EMP_VAC_TASKS_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud Vacancy Tasks View';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Person';
  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_VAC_TASKS_VIEW
Formula Type: Applaud Person
Description:  Applaud Vacancy Tasks View
==============================================================================*/

Default for Lookup_Code             is 'Unknown'
Default for Meaning                 is 'Unknown'
Default for Vacancy_Id              is 0
Default for Number_of_Openings      is 1
Default for Num_Total               is 0
Default for Num_Active              is 0
Default for Num_Term                is 0
Default for Num_Interview1          is 0
Default for Num_Interview2          is 0
Default for Num_Offer               is 0
Default for Num_Accepted            is 0
Default for Num_No                  is 0
Default for Num_Unsure              is 0
Default for Num_Yes                 is 0
Default for Transaction_Id          is 0
Default for Workflow_Status_Code    is 'Unknown'
Default for Workflow_Status_Meaning is 'Unknown'
Default for Status_Code             is 'Unknown'
Default for Status_Meaning          is 'Unknown'

Inputs are Lookup_Code, Meaning, Vacancy_Id, Number_of_Openings
          ,Num_Total, Num_Active, Num_Term, Num_Interview1
          ,Num_Interview2, Num_Offer, Num_Accepted
          ,Num_No, Num_Unsure, Num_Yes
          ,Transaction_Id, Workflow_Status_Code
          ,Workflow_Status_Meaning, Status_Code, Status_Meaning

  If Status_Code = 'APPROVED' Or
     Status_Code = 'APR' Or
     Status_Code = 'O' Then
    (Is_Active = 'Y')
  Else
    (Is_Active = 'N')
    
  If Num_Accepted >= Number_of_Openings then
    (Vacancy_Filled = 'Y')
  Else
    (Vacancy_Filled = 'N')
          
  Show_Task = 'N'
  
  /* No applicants have applied yet */
  If Lookup_Code = 'A_WAIT' Then
  (
    If Is_Active = 'Y' And Num_Total = 0 then 
    (
      Show_Task = 'Y'
      Task = 'Wait for your first applicant <b><font color="#499324">:-)</font></b>'
    )
    Else If Is_Active = 'Y' And Vacancy_Filled = 'N' and
              Num_Active = 0 and
              Num_Interview1 = 0 and
              Num_Interview2 = 0 and
              Num_Offer = 0 then
    (
      Show_Task = 'Y'
      Task = 'Wait for more applicants'
    )
  )
  
  /* Vacancy filled but remaining applicants not informed */
  Else If Lookup_Code = 'B_REJECT' Then
  (  
    If Vacancy_Filled = 'Y' And 
      (Num_Active > 0 Or
       Num_Interview1 > 0 Or 
       Num_Interview2 > 0 Or
       Num_Offer > 0) then 
    (
      Show_Task = 'Y'
      Num_Apls = Num_Active + Num_Interview1 + Num_Interview2 + Num_Offer
      If Num_Apls = 1 Then
        (Task = 'Reject <b><font color="#CC0000">'
               + to_char(Num_Apls)
               + '</font></b> applicant')
      Else
        (Task = 'Reject <b><font color="#CC0000">'
               + to_char(Num_Apls)
               + '</font></b> applicants')
      Applicant_Statuses = 'ACTIVE_APL, INTERVIEW1, INTERVIEW2, OFFER'
    )
  )
  
  /* Nothing left to do here */
  Else If Lookup_Code = 'C_DONE' Then
  (  
    If Vacancy_Filled = 'Y' And 
       Num_Active = 0 And
       Num_Interview1 = 0 And 
       Num_Interview2 = 0 And
       Num_Offer = 0 then 
    (
      Show_Task = 'Y'
      Task = 'You''re all done here <b><font color="#499324">:-)</font></b>'
    )
  )
  
  /* Reject the 'No's */
  Else If Lookup_Code = 'D_REJECT2' Then
  (  
    If Vacancy_Filled = 'N' And 
       Num_No > 0 then 
    (
      Show_Task = 'Y'
      If Num_No = 1 Then
        (Task = 'Reject <b><font color="#CC0000">'
               + to_char(Num_No)
               + '</font></b> applicant')
      Else
        (Task = 'Reject <b><font color="#CC0000">'
               + to_char(Num_No)
               + '</font></b> applicants')   
      Applicant_Interested = '1'      
    )
  )
  
  /* Screen those who are active */
  Else If Lookup_Code = 'E_REVIEW' Then
  (  
    If Vacancy_Filled = 'N' And 
       Num_Active > 0 then 
    (
      Show_Task = 'Y'
      If Num_Active = 1 Then
        (Task = 'Review <b><font color="#3366CC">'
               + to_char(Num_Active)
               + '</font></b> applicant')
      Else
        (Task = 'Review <b><font color="#3366CC">'
               + to_char(Num_Active)
               + '</font></b> applicants')   
      Applicant_Statuses = 'ACTIVE_APL'
    )
  )
  
  /* Progress interviews */
  Else If Lookup_Code = 'F_INTERVIEW' Then
  (  
    If Vacancy_Filled = 'N' And 
      (Num_Interview1 > 0 Or
       Num_Interview2 > 0) then 
    (
      Show_Task = 'Y'
      If Num_Interview1 + Num_Interview2 = 1 Then
        (Task = 'Interview <b><font color="#3366CC">'
               + to_char(Num_Interview1 + Num_Interview2)
               + '</font></b> applicant')
      Else
        (Task = 'Interview <b><font color="#3366CC">'
               + to_char(Num_Interview1 + Num_Interview2)
               + '</font></b> applicants')   
      Applicant_Statuses = 'INTERVIEW1, INTERVIEW2'
    )
  )
  
  /* Progress offers */
  Else If Lookup_Code = 'G_OFFER' Then
  (  
    If Vacancy_Filled = 'N' And 
       Num_Offer > 0 then 
    (
      Show_Task = 'Y'
      If Num_Offer = 1 Then
        (Task = 'Progress offers for <b><font color="#3366CC">'
               + to_char(Num_Offer)
               + '</font></b> applicant')
      Else
        (Task = 'Progress offer for <b><font color="#3366CC">'
               + to_char(Num_Offer)
               + '</font></b> applicants')   
      Applicant_Statuses = 'OFFER'               
    )
  )

Return Show_Task
      ,Task
      ,Applicant_Statuses
      ,Applicant_Interested

]';


  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;
