/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2010 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_COM_BASIC_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_sequence                number := 1;
  l_cd_row_id               number;
  l_cd_row_ovn              number;
  l_cd_row_eff_start        date;
  l_cd_row_eff_end          date;
  l_asg_row_id              number;
  l_asg_row_ovn             number;
  l_asg_row_eff_start       date;
  l_asg_row_eff_end         date;
  l_cd_val_id               number;
  l_cd_val_ovn              number;
  l_cd_val_eff_start        date;
  l_cd_val_eff_end          date;
  l_asg_val_id              number;
  l_asg_val_ovn             number;
  l_asg_val_eff_start       date;
  l_asg_val_eff_end         date;

  --
  -- Gets the UDT if already created
  --
  CURSOR csr_get_udt IS
  SELECT l_YES
  FROM   pay_user_tables udt
  WHERE  udt.user_table_name = l_UDT
  AND    udt.business_group_id = l_BG_ID;

BEGIN

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

  IF l_exists IS NOT NULL THEN
    RAISE e_already_exists;
  END IF;

  --
  -- 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);


  --
  -- Create the Contact Details 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 => 'Contact Details'
    ,p_display_sequence      => l_sequence
    ,p_business_group_id     => l_BG_ID
    ,p_user_row_id           => l_cd_row_id
    ,p_object_version_number => l_cd_row_ovn
    ,p_effective_start_date  => l_cd_row_eff_start
    ,p_effective_end_date    => l_cd_row_eff_end);

  l_sequence := l_sequence + 1;

  --
  -- Create the Assignment 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 => 'Assignment'
    ,p_display_sequence      => l_sequence
    ,p_business_group_id     => l_BG_ID
    ,p_user_row_id           => l_asg_row_id
    ,p_object_version_number => l_asg_row_ovn
    ,p_effective_start_date  => l_asg_row_eff_start
    ,p_effective_end_date    => l_asg_row_eff_end);

  --
  -- Create the Contact Details Event Value
  --
  pay_user_column_instance_api.create_user_column_instance
    (p_validate                => FALSE
    ,p_effective_date          => l_EFF_DT
    ,p_user_row_id             => l_cd_row_id
    ,p_user_column_id          => l_event_type_col_id
    ,p_value                   => 'XXAS_COM_BASIC_CONTACT'
    ,p_business_group_id       => l_BG_ID
    ,p_user_column_instance_id => l_cd_val_id
    ,p_object_version_number   => l_cd_val_ovn
    ,p_effective_start_date    => l_cd_val_eff_start
    ,p_effective_end_date      => l_cd_val_eff_end);

  --
  -- Create the Assignment Event Value
  --
  pay_user_column_instance_api.create_user_column_instance
    (p_validate                => FALSE
    ,p_effective_date          => l_EFF_DT
    ,p_user_row_id             => l_asg_row_id
    ,p_user_column_id          => l_event_type_col_id
    ,p_value                   => 'XXAS_COM_BASIC_ASG'
    ,p_business_group_id       => l_BG_ID
    ,p_user_column_instance_id => l_asg_val_id
    ,p_object_version_number   => l_asg_val_ovn
    ,p_effective_start_date    => l_asg_val_eff_start
    ,p_effective_end_date      => l_asg_val_eff_end);

EXCEPTION

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

END;
/
COMMIT;
EXIT;
