/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 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_APL_STATUS_CHANGE';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Details of applicant assignment status change communication, if any';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Business Group';
  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_APL_STATUS_CHANGE
Formula Type: Applaud Assignment
Description:  Details of applicant assignment status change communication

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


/* +-------------------< SECTION 1: DEFAULTS >-------------------------------+
   Defaults must be specified for every item listed in the Inputs section. When
   a value is not set it will be defaulted to the default specified here.   */

  Default for Vacancy_Id                  is -1
  Default for Assignment_Status_Type_Id   is -1
  Default for Status_Change_Date          is '0001/01/01 00:00:00' (date)
  Default for Status_Change_Reason        is 'Unknown'
  Default for Per_System_Status           is 'Unknown'
  Default for User_Status                 is 'Unknown'


/* +-------------------< SECTION 2: INPUTS >---------------------------------+
   This formula lists all the available items that you can use in this
   formula.                                                                 */

  Inputs are Vacancy_Id, Assignment_Status_Type_Id, Status_Change_Date,
             Status_Change_Reason, Per_System_status, User_Status


/* +-------------------< SECTION 3: OTHER DEFAULTS >-------------------------+
   It is good practice to define default values for the Formula Outputs and
   other variables that are used in this formula.  This helps to guard against
   empty outputs being returned and keeps the formula tidy.                 */


  /* The Formula Outputs are the reason for this Formula: it gives you finite
     control to set the Outputs to whatever values you choose.  You can use
     information in the Formula Inputs to conditionally set the Outputs.

     Here they are defaulted to initial values and later tweaked.           */

  Send_to_Applicants = 'Yes'                /* Set this to 'Yes' or 'No'    */

  /* Send the notification from people other than the logged on user. If you
     override this, remember that the logged on user will not see this
     notification in their Sent items.
  Override_From_Role = Updated_By_Role                                      */

  /* Specify a comma-separated list of other recipients in addition to
     the applicant (or instead of if Send_to_Applicants = No)
  Other_To_Roles     = 'SYSADMIN, POS:12345'                                */


/* +-------------------< SECTION 4: MAIN LOGIC >-----------------------------+
   This contains logic that adjusts the Outputs according to certain
   conditions.  For example, you could use Vacancy_Id to call a custom
   formula function and adjust the comms depending on vacancy details.      */

  /* Retrieve information from the vacancy */
  Vacancy_Name         = 'Unknown'
  Job_Name             = 'Unknown'
  Location_Code        = 'Unknown'
  Date_To              = '0001/01/01 00:00:00' (date)
  Hiring_Manager_Name  = 'Unknown'
  Hiring_Manager_Email = 'Unknown'

  /* DC: please create this FF function and return these details. Vacancy_Id
         should be an IN parameter; everything else is an OUT. Is there anything
         else we could return that might be useful for customers?  Org, Position? Salary? */
  /* xxas_get_vacancy_details
      (Vacancy_Id, Vacancy_Name, Job_Name, Location_Code, Date_To
      ,Hiring_Manager_Name, Hiring_Manager_Email) */

  /* Values that don''t change based on the status. These
     items appear in the notification to the applicant.  */
  /* DC: please change hard-coded text to an FND message using
         xxas_get_message('XXAS', 'XXAS_MESSAGE_NAME', TOKENS) */
  H1_Title      = 'Update to your Job Application'
  H2_Title      = Job_Name
  H2_Subtext    = 'Vacancy: ' + Vacancy_Name
  Context_Line1 = 'DC: What to show here?'
  Context_Line2 = Location_Code
  Body_Context  = to_char(Status_Change_Date, 'FMDd Mon')
  Body_Title    = User_Status
  Footer_Title  = 'The Recruiting Team'
  Footer_Text   = 'DC: What to show here?'

  /* Help text that''s only seen by the manager when changing the status. */
  General_Help  = 'Individual emails will be sent to each recipient'

  /* Help text that candidate''s see when looking at the Application Status online. */
  Aside_Title   = 'Any questions?'
  Aside_Text    = 'Please get in touch with our resourcing team and we''d be ' +
                  'very happy to help.'
/* Initialization */
Vacancy_Name = ' '
Hiring_Manager = ' '
Recruiter = ' '
Position = ' '
Location = ' '
Organization = ' '
Job = ' '
Grade = ' '
Employment_Status = ' '
Min_Salary = 0
Max_Salary = 0
Currency = ' '
Posting_Name = ' '
Posting_Job = ' '
Posting_Org = ' '

l_result = XXAS_GET_APL_VAC_DETAILS (Vacancy_Id,vacancy_Name ,hiring_manager ,recruiter ,position ,location ,organization ,job ,
grade ,employment_status ,min_salary ,max_salary ,currency ,posting_name ,posting_job ,posting_org )

  /* Dynamic text based on the status */
  If Per_System_Status = 'ACTIVE_APL' /* and
     upper(User_Status) = '<Insert your Applied status here>' */  Then
    (

      Body_Text = xxas_emp_set_long_output('Body_Text', 'XXAS', 'XXAS_REC_COM_ACT_BODY_TXT'
										,'VAC_JOB_TITLE'	,vacancy_Name)
    )

  Else If Per_System_Status = 'INTERVIEW1' Then
    (
      Body_Text = xxas_emp_set_long_output('Body_Text', 'XXAS', 'XXAS_REC_COM_1ST_BODY_TXT')
    )

  Else If Per_System_Status = 'INTERVIEW2' Then
    (
      Body_Text = xxas_emp_set_long_output('Body_Text', 'XXAS', 'XXAS_REC_COM_2ND_BODY_TXT')
    )

  Else If Per_System_Status = 'OFFER' Then
    (
      Body_Text = xxas_emp_set_long_output('Body_Text', 'XXAS', 'XXAS_REC_COM_OFF_BODY_TXT'
										,'VAC_JOB_TITLE'	,vacancy_Name
										,'VAC_LOCATION'	,location)
    )

  Else If Per_System_Status = 'ACCEPTED' Then
    (
      Body_Text = xxas_emp_set_long_output('Body_Text', 'XXAS', 'XXAS_REC_COM_ACC_BODY_TXT')
    )
  Else
    (
      Send_to_Applicants = 'No'
    )

/* +-------------------< SECTION 5: RETURN OUTPUTS >-------------------------+
   This section returns the Outputs.  The Outputs that are not used by
   default are commented out.  If you wish to use any of these you should
   uncomment them and set their values further up.                          */

RETURN Send_to_Applicants
   /* ,Override_From_Role */
   /* ,Other_To_Roles */
      ,H1_Title
      ,H2_Title
      ,H2_Subtext
      ,Context_Line1
      ,Context_Line2
      ,Body_Context
      ,Body_Title
      ,Body_Text
      ,Footer_Title
      ,Footer_Text
      ,General_Help
      ,Aside_Title
	    ,Aside_Text

]';


  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;
