/* $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('0001-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_PAYSLIP_DATA_US_EMP_DET';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud Payslip Data US Employee Details';
  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 := '
/*==============================================================================

Formula Name: XXAS_PAYSLIP_DATA_US_EMP_DET
Formula Type: Applaud Assignment
Description:  Applaud Payslip Data US Employee Details

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

  Default for Full_Name              is ''Unknown''
  Default for Job                    is ''Unknown''
  Default for National_Identifier    is ''Unknown''
  Default for Employee_Number        is ''Unknown''
  Default for Employee_Date_Start    is ''0001/01/01 00:00:00'' (date)
  Default for Original_Hire_Date     is ''0001/01/01 00:00:00'' (date)
  Default for Adjusted_Service_Date  is ''0001/01/01 00:00:00'' (date)
  Default for Assignment_Number      is ''Unknown''
  Default for Location_Name          is ''Unknown''
  Default for Position               is ''Unknown''
  Default for Payroll_Name           is ''Unknown''
  Default for Employee_Address1      is ''Unknown''
  Default for Employee_Address2      is ''Unknown''
  Default for Employee_Address3      is ''Unknown''
  Default for Employee_City          is ''Unknown''
  Default for Employee_State         is ''Unknown''
  Default for Employee_Zip_Code      is ''Unknown''
  Default for Gre_Name               is ''Unknown''
  Default for Employer_Phone_Number  is ''Unknown''
  Default for Organization_Name      is ''Unknown''
  Default for Pay_Calculation_Method is ''Unknown''
  Default for Pay_Basis              is ''Unknown''
  Default for Frequency              is ''Unknown''
  Default for Shift                  is ''Unknown''
  Default for Bargaining_Unit        is ''Unknown''
  Default for Collective_Agreement   is ''Unknown''
  Default for Contract               is ''Unknown''
  Default for Grade                  is ''Unknown''
  Default for Employer_Address1      is ''Unknown''
  Default for Employer_Address2      is ''Unknown''
  Default for Employer_Address3      is ''Unknown''
  Default for Employer_City          is ''Unknown''
  Default for Employer_State         is ''Unknown''
  Default for Employer_Zip_Code      is ''Unknown''

  Inputs are Full_Name, Job, National_Identifier, Employee_Number,
             Employee_Date_Start, Original_Hire_Date, Adjusted_Service_Date,
             Assignment_Number, Location_Name, Position, Payroll_Name,
             Employee_Address1, Employee_Address2, Employee_Address3,
             Employee_City, Employee_State, Employee_Zip_Code, Gre_Name,
             Employer_Phone_number, Organization_Name, Pay_Calculation_Method,
             Pay_Basis, Frequency, Shift, Bargaining_Unit, Collective_Agreement,
             Contract, Grade, Employer_Address1, Employer_Address2,
             Employer_Address3, Employer_City, Employer_State, Employer_Zip_Code

  DATE_FORMAT_MASK     = ''FMDd Mon YYYY''

  Current_Field_Num = 1

  /* Set the Employee field */
  Func_Result = xxas_emp_set_long_output
    (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
    ,''XXAS'',''XXAS_EMP_EMPLOYEE'')
  Func_Result = xxas_emp_set_long_output
    (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
    ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Full_Name)
  Current_Field_Num = Current_Field_Num + 1

  /* Set the Job field */
  If Job was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_JOB_TITLE'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Job)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the National Identifier field */
  If National_Identifier was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_NATIONAL_IDENTIFIER'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', National_Identifier)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Employee Number field */
  If Employee_Number was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_EMPLOYEE_NUMBER'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Employee_Number)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Latest Hire Date field */
  If Employee_Date_Start was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_LATEST_HIRE_DATE'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', to_char(Employee_Date_Start, 

DATE_FORMAT_MASK))
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Original Hire Date field */
  If Original_Hire_Date was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_ORIGINAL_HIRE_DATE'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', to_char(Original_Hire_Date, 

DATE_FORMAT_MASK))
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Adjusted Service Date field */
  If Adjusted_Service_Date was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_ADJUSTED_SERVICE_DATE'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', to_char(Adjusted_Service_Date, 

DATE_FORMAT_MASK))
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Assignment Number field */
  If Assignment_Number was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_ASSIGNMENT_NUMBER'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Assignment_Number)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Location field */
  If Location_Name was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_RB_EMP_LOC'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Location_Name)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Position field */
  If Position was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_RB_EMP_POSITION'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Position)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Payroll field */
  If Payroll_Name was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_PAYROLL'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Payroll_Name)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Employee Address field */
  Address = Employee_Address1 + '',''
  If Employee_Address2 was not defaulted then
    (Address = Address + Employee_Address2 + '','')
  If Employee_Address3 was not defaulted then
    (Address = Address + Employee_Address3 + '','')
  If Employee_City was not defaulted then
    (Address = Address + Employee_City + '','')
  If Employee_State was not defaulted then
    (Address = Address + Employee_State + '','')
  If Employee_Zip_Code was not defaulted then
    (Address = Address + Employee_Zip_Code)
  Address = Address

  Func_Result = xxas_emp_set_long_output
    (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
    ,''XXAS'',''XXAS_EMP_EMPLOYEE_ADDRESS'')
  Func_Result = xxas_emp_set_long_output
    (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
    ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Address)
  Current_Field_Num = Current_Field_Num + 1

  /* Set the Employer Name field */
  If GRE_Name was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_EMPLOYER_NAME'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', GRE_Name)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Employer Phone Number field */
  If Employer_Phone_Number was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_EMPLOYER_PHONE'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Employer_Phone_Number)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Organization field */
  If Organization_Name was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_ORGANIZATION'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Organization_Name)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Pay Calculation Method field */
  If Pay_Calculation_Method was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_PAY_CAL'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Pay_Calculation_Method)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Pay Basis field */
  If Pay_Basis was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_PAY_BASIS'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Pay_Basis)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Frequency field */
  If Frequency was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_FREQUENCY'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Frequency)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Shift field */
  If Shift was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_SHIFT'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Shift)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Bargaining Unit field */
  If Bargaining_Unit was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_BARGAINING_UNIT'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Bargaining_Unit)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Collective Agreement field */
  If Collective_Agreement was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_CA'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Collective_Agreement)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Contract field */
  If Contract was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_CONTRACT'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Contract)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Grade field */
  If Grade was not defaulted then (
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
      ,''XXAS'',''XXAS_EMP_GRADE'')
    Func_Result = xxas_emp_set_long_output
      (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
      ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Grade)
    Current_Field_Num = Current_Field_Num + 1
  )

  /* Set the Employer Address field */
  Address = Employer_Address1 + '',''
  If Employer_Address2 was not defaulted then
    (Address = Address + Employer_Address2 + '','')
  If Employer_Address3 was not defaulted then
    (Address = Address + Employer_Address3 + '','')
  If Employer_City was not defaulted then
    (Address = Address + Employer_City + '','')
  If Employer_State was not defaulted then
    (Address = Address + Employer_State + '','')
  If Employer_Zip_Code was not defaulted then
    (Address = Address + Employer_Zip_Code)
  Address = Address

  Func_Result = xxas_emp_set_long_output
    (''FIELD'' + to_char(Current_Field_Num) + ''_LABEL''
    ,''XXAS'',''XXAS_EMP_EMPLOYER_ADDRESS'')
  Func_Result = xxas_emp_set_long_output
    (''FIELD'' + to_char(Current_Field_Num) + ''_VALUE''
    ,''XXAS'', ''XXAS_COM_TEXT'', ''TEXT'', Address)
  Current_Field_Num = Current_Field_Num + 1

  /* The return value here isn''t actually used because the return items are set 

above instead */
  Field1_Label = ''Dummy''
  Return Field1_Label

';


  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;
