/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2019 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('1900-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_TCM_MISSED_PUNCHES';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud Timecard Detect Missed Punches - seeded 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);

  --
  -- 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_TCM_MISSED_PUNCHES
Formula Type: Applaud Assignment
Description:  Applaud Timecard Detect Missed Punches - seeded formula

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

/* +-------------------< 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.   */

  Alias sysdate As Now

  Default for Sysdate                   is '0001/01/01 00:00:00' (date)
  Default for Assignment_Number         is 'Unknown'
  Default for Assignment_Start_Date     is '0001/01/01 00:00:00' (date)
  Default for Assignment_End_Date       is '4712/12/31 00:00:00' (date)
  Default for Person_Id                 is -1
  Default for Assignment_Type           is 'Unknown'
  Default for Primary_Flag              is 'Unknown'
  Default for Organization_Id           is -1
  Default for Organization_Name         is 'Unknown'
  Default for Organization_Hours        is 0
  Default for Business_Group_Hours      is 0
  Default for Grade_Id                  is -1
  Default for Grade_Name                is 'Unknown'
  Default for Position_Id               is -1
  Default for Position_Name             is 'Unknown'
  Default for Position_Hours            is 0
  Default for Job_Id                    is -1
  Default for Job_Name                  is 'Unknown'
  Default for Payroll_Id                is -1
  Default for Payroll_Name              is 'Unknown'
  Default for Salary_Basis_id           is -1
  Default for Salary_Basis              is 'Unknown'
  Default for Assignment_Status_Type_Id is -1
  Default for Assignment_Status         is 'Unknown'
  Default for Assignment_Category       is 'Unknown'
  Default for Employee_Category         is 'Unknown'
  Default for Normal_Hours              is 0
  Default for Frequency                 is 'Unknown'
  Default for Years_Service             is 0
  Default for Months_Service            is 0
  Default for Budget_FTE                is 0
  Default for Budget_Percent            is 0
  Default for Location_Id               is -1
  Default for Location_Name             is 'Unknown'
  Default for Address_Line_1            is 'Unknown'
  Default for Address_Line_2            is 'Unknown'
  Default for Address_Line_3            is 'Unknown'
  Default for Town_or_City              is 'Unknown'
  Default for Postal_Code               is 'Unknown'
  Default for Country                   is 'Unknown'
  Default for Last_Punch_Type           is 'Unknown'
  Default for Last_Punch_Date           is '0001/01/01 00:00:00' (date)
  Default for Last_Punch_DateTime_In    is '0001/01/01 00:00:00' (date)
  Default for Last_Punch_DateTime_Out   is '0001/01/01 00:00:00' (date)
  
  
/* +-------------------< SECTION 2: INPUTS >---------------------------------+
   This section lists all the available items that you can use in this
   formula.                                                                 */

  Inputs are Assignment_Number, Assignment_Start_Date, Assignment_End_Date
            ,Person_Id, Assignment_Type, Primary_Flag, Organization_Id
            ,Organization_Name, Organization_Hours, Business_Group_Hours
            ,Grade_Id, Grade_Name, Position_Id
            ,Position_Name, Position_Hours, Job_Id, Job_Name
            ,Payroll_Id, Payroll_Name, Salary_Basis_Id, Salary_Basis
            ,Assignment_Status_Type_Id, Assignment_Status, Assignment_Category
            ,Employee_Category, Normal_Hours, Frequency, Years_Service
            ,Months_Service, Budget_FTE, Budget_Percent, Location_Id
            ,Location_Name, Address_Line_1, Address_Line_2, Address_Line_3
            ,Town_or_City, Postal_Code, Country
            ,Last_Punch_Type, Last_Punch_Date, Last_Punch_DateTime_In
            ,Last_Punch_DateTime_Out


 /* +-------------------< SECTION 3: OUTPUTS >-------------------------------+
   This section sets the outputs of the formula.
   
     Miss:                Determines whether the last punch has been missed
                          or not.  Y if a miss, N if not.
     Miss_Date:           If it's a missed punch in, the date it was missed.
     Payment_Type:        If it's a missed punch in, the name of the payment
                          type, such as 'Regular hours' or 'Overtime'.
                                                                           */
     
     Miss = 'N'
     
     If Last_Punch_Type = 'IN' Then
       (
       
       /* If it's more than 24 hours, assume it's a missed OUT punch. 
          If you wanted to, eg, make this based on max 15 hour shift, you
          could use: >= ((1 / 24) * 15)
       */
       
       If Primary_Flag = 'Y' And
          days_between(Now, Last_Punch_DateTime_In) >= 1 Then
         (
         Miss = 'Y'
         )
       )
       
     Else If Last_Punch_Type = 'OUT' Then
       (

       Miss = 'N'
       
       /*
          If you reliably know an employee's work pattern, you can define
          logic here to detect if the employee should have punched in by now.
          
          For example, to detect a missed punch in a simple Mon-Fri pattern
          we add a day to the last punch date for the expected punch date, and
          then adjust if that falls on a Sat or Sun.  Then we check this isn't
          in the future and flag that it's a missed punch.
          
          Expected_Punch_Date = add_days(Last_Punch_Date, 1)
          If XXAS_TO_CHAR(Expected_Punch_In, 'D') = '7' Then
            (Expected_Punch_Date = add_days(Last_Punch_Date, 3))
          Else if XXAS_TO_CHAR(Expected_Punch_In, 'D') = '1' Then
            (Expected_Punch_Date = add_days(Last_Punch_Date, 2))
            
          If Primary_Flag = 'Y' And
             days_between(Now, Expected_Punch_Date) >= 1 Then
            (
            Miss = 'Y'
            Miss_Date = Expected_Punch_Date
            Payment_Type = 'Regular hours'
            )
            
       */

       )

  /* Don't forget to return Miss_Date and Payment_Type
     if detecting missed punch ins */
  
  RETURN Miss

]';


  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;
