/* $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, exceptions and variables
  --
  l_UDT            CONSTANT varchar2(22) := 'XXAS_OUT_EVENT_FORMULA';
  l_YES            CONSTANT varchar2(1)  := 'Y';
  l_EFF_DT         CONSTANT date         := to_date('1900-01-01','YYYY-MM-DD');
  l_BG_ID          CONSTANT number       := to_number('&1');
  l_EVENT_TYPE_COL CONSTANT varchar2(10) := 'Event Type';

  e_already_exists          EXCEPTION;

  l_exists                  varchar2(1);
  l_user_table_id           number;
  l_udt_ovn                 number;
  l_event_type_col_id       number;
  l_event_type_col_ovn      number;
  l_abs_seq                 number := 5;
  l_enr_seq                 number := 6;
  l_cus_seq                 number := 7;
  l_res_seq                 number := 8;
  l_abs_row_id              number;
  l_abs_row_ovn             number;
  l_abs_row_eff_start       date;
  l_abs_row_eff_end         date;
  l_enr_row_id              number;
  l_enr_row_ovn             number;
  l_enr_row_eff_start       date;
  l_enr_row_eff_end         date;


  --
  -- Gets the UDT if already created
  --

  CURSOR csr_get_udt IS
  SELECT user_Table_id
  FROM   pay_user_tables udt
  WHERE  udt.user_table_name = l_UDT
  AND    udt.business_group_id = l_BG_ID;
  
  CURSOR csr_get_user_row(p_row_low_range_or_name in varchar2, 
                          p_user_table_id in number) IS
  select l_YES 
  FROM   pay_user_rows_f purf
  where  purf.user_table_id = p_user_table_id
  and    purf.row_low_range_or_name = p_row_low_range_or_name
  and    l_eff_dt between purf.effective_start_date and purf.effective_end_date;

BEGIN


  --
  -- Check if the UDT already exists
  --
  OPEN  csr_get_udt;
  FETCH csr_get_udt INTO l_user_table_id;

  IF csr_get_udt%notfound THEN
 
    --
    -- Create the UDT
    --
    pay_user_table_api.create_user_table
      (p_validate              => FALSE
      ,p_effective_date        => l_EFF_DT
      ,p_business_group_id     => l_BG_ID
      ,p_range_or_match        => 'M'
      ,p_user_key_units        => 'T'
      ,p_user_table_name       => l_UDT
      ,p_user_table_id         => l_user_table_id
      ,p_object_version_number => l_udt_ovn);

    --
    -- Create the Event Type UDT column
    --
    pay_user_column_api.create_user_column
      (p_validate              => FALSE
      ,p_business_group_id     => l_BG_ID
      ,p_user_table_id         => l_user_table_id
      ,p_user_column_name      => l_EVENT_TYPE_COL
      ,p_user_column_id        => l_event_type_col_id
      ,p_object_version_number => l_event_type_col_ovn);
  end if; 
 
  CLOSE csr_get_udt;
  --
  -- Check if the Absence Row already exists
  --
  OPEN  csr_get_user_row('Absence', l_user_table_id);
  FETCH csr_get_user_row INTO l_exists;
  
  if csr_get_user_row%NOTFOUND Then 
    --
    -- Create the Absence Event Type Row
    --
    pay_user_row_api.create_user_row
    (p_validate              => FALSE
    ,p_effective_date        => l_EFF_DT
    ,p_user_table_id         => l_user_table_id
    ,p_row_low_range_or_name => 'Absence'
    ,p_display_sequence      => l_abs_seq
    ,p_business_group_id     => l_BG_ID
    ,p_user_row_id           => l_abs_row_id
    ,p_object_version_number => l_abs_row_ovn
    ,p_effective_start_date  => l_abs_row_eff_start
    ,p_effective_end_date    => l_abs_row_eff_end);
  end if; 
  
  CLOSE csr_get_user_row;
  --
  -- Check if the Enrollment Row already exists
  --
  OPEN  csr_get_user_row('Enrollment', l_user_table_id);
  FETCH csr_get_user_row INTO l_exists;

  if csr_get_user_row%NOTFOUND Then   
  --
  -- Create the OLM Enrollment Row
  --
    pay_user_row_api.create_user_row
      (p_validate              => FALSE
      ,p_effective_date        => l_EFF_DT
      ,p_user_table_id         => l_user_table_id
      ,p_row_low_range_or_name => 'Enrollment'
      ,p_display_sequence      => l_enr_seq
      ,p_business_group_id     => l_BG_ID
      ,p_user_row_id           => l_enr_row_id
      ,p_object_version_number => l_enr_row_ovn
      ,p_effective_start_date  => l_enr_row_eff_start
      ,p_effective_end_date    => l_enr_row_eff_end);
  end if; 

  CLOSE csr_get_user_row;
  --
  -- Check if the Customer Enrollment Row already exists
  --
  OPEN  csr_get_user_row( 'CustomerEnrollment', l_user_table_id);
  FETCH csr_get_user_row INTO l_exists;

  if csr_get_user_row%NOTFOUND Then   
  --
  -- Create the Customer Enrollment Row
  --
    pay_user_row_api.create_user_row
      (p_validate              => FALSE
      ,p_effective_date        => l_EFF_DT
      ,p_user_table_id         => l_user_table_id
      ,p_row_low_range_or_name => 'CustomerEnrollment'
      ,p_display_sequence      => l_cus_seq
      ,p_business_group_id     => l_BG_ID
      ,p_user_row_id           => l_enr_row_id
      ,p_object_version_number => l_enr_row_ovn
      ,p_effective_start_date  => l_enr_row_eff_start
      ,p_effective_end_date    => l_enr_row_eff_end);
  end if; 

  CLOSE csr_get_user_row;
  --
  -- Check if the Resource Booking Row already exists
  --
  OPEN  csr_get_user_row('ResourceBooking', l_user_table_id);
  FETCH csr_get_user_row INTO l_exists;

  if csr_get_user_row%NOTFOUND Then   
  --
  -- Create the Resource Booking Row
  --
    pay_user_row_api.create_user_row
      (p_validate              => FALSE
      ,p_effective_date        => l_EFF_DT
      ,p_user_table_id         => l_user_table_id
      ,p_row_low_range_or_name => 'ResourceBooking'
      ,p_display_sequence      => l_res_seq
      ,p_business_group_id     => l_BG_ID
      ,p_user_row_id           => l_enr_row_id
      ,p_object_version_number => l_enr_row_ovn
      ,p_effective_start_date  => l_enr_row_eff_start
      ,p_effective_end_date    => l_enr_row_eff_end);
  end if;  
  
  CLOSE csr_get_user_row;

EXCEPTION

  WHEN e_already_exists THEN
    --
    -- No action required; UDT already exists
    --
    NULL;

END;
/
COMMIT;
EXIT;
