/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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(18) := 'XXAS_COM_APPROVALS';
  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_TRANS_TYPE_COL  CONSTANT varchar2(16)  := 'Transaction Type';
  l_APP_COL         CONSTANT varchar2(22)  := 'Application Short Code';

  e_already_exists          EXCEPTION;

  l_exists                  varchar2(1);
  l_user_table_id           number;
  l_udt_ovn                 number;
  l_trans_type_col_id       number;
  l_trans_type_col_ovn      number;
  l_app_col_id              number;
  l_app_col_ovn             number;
  l_sequence                number := 1;
  l_row_id                  number;
  l_row_ovn                 number;
  l_row_eff_start           date;
  l_row_eff_end             date;
  l_val_id                  number;
  l_val_ovn                 number;
  l_val_eff_start           date;
  l_val_eff_end             date;

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

  --
  -- Gets the UDT row if already created
  --
  CURSOR csr_get_udt_row(cp_transaction_type varchar2) IS
  SELECT l_YES
  FROM   pay_user_tables udt
        ,pay_user_rows_f paurf
  WHERE  udt.user_table_name = l_UDT
  AND    udt.business_group_id = l_BG_ID
  AND    paurf.user_table_id = udt.user_table_id
  AND    paurf.row_low_range_or_name = cp_transaction_type;

    --
  -- Gets the UDT column if already created
  --
  CURSOR csr_get_udt_column(p_column_name IN varchar2) IS
  SELECT uc.user_column_id
  FROM   pay_user_tables udt
        ,pay_user_columns  uc
  WHERE  udt.user_table_name = l_UDT
  AND    udt.business_group_id = l_BG_ID
  AND    uc.user_table_id = udt.user_table_id
  AND    uc.user_column_name = p_column_name;


BEGIN

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

  IF l_exists IS  NULL 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 Transaction 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_TRANS_TYPE_COL
    ,p_user_column_id        => l_trans_type_col_id
    ,p_object_version_number => l_trans_type_col_ovn);
    
  --
  -- Create the Application Short Code 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_APP_COL
    ,p_user_column_id        => l_app_col_id
    ,p_object_version_number => l_app_col_ovn);

  END IF;
  
  OPEN csr_get_udt_column(l_APP_COL);
  FETCH csr_get_udt_column INTO l_app_col_id;
  CLOSE csr_get_udt_column;

  OPEN csr_get_udt_column(l_TRANS_TYPE_COL);
  FETCH csr_get_udt_column INTO l_trans_type_col_id;
  CLOSE csr_get_udt_column;
  
  l_exists := 'N';
  
  OPEN  csr_get_udt_row('Timecard Entry');
  FETCH csr_get_udt_row INTO l_exists;
  CLOSE csr_get_udt_row;
  
  IF nvl(l_exists,'N') = 'N' THEN
  --
  -- Create the Timecard Entry 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 => 'Timecard Entry'
    ,p_display_sequence      => l_sequence
    ,p_business_group_id     => l_BG_ID
    ,p_user_row_id           => l_row_id
    ,p_object_version_number => l_row_ovn
    ,p_effective_start_date  => l_row_eff_start
    ,p_effective_end_date    => l_row_eff_end);

  l_sequence := l_sequence + 1;

  --
  -- Create the Timecard Entry Transation Type Value
  --
  pay_user_column_instance_api.create_user_column_instance
    (p_validate                => FALSE
    ,p_effective_date          => l_EFF_DT
    ,p_user_row_id             => l_row_id
    ,p_user_column_id          => l_trans_type_col_id
    ,p_value                   => 'XXAS_TCM'
    ,p_business_group_id       => l_BG_ID
    ,p_user_column_instance_id => l_val_id
    ,p_object_version_number   => l_val_ovn
    ,p_effective_start_date    => l_val_eff_start
    ,p_effective_end_date      => l_val_eff_end);

  --
  -- Create the Timecard Entry Application Value
  --
  pay_user_column_instance_api.create_user_column_instance
    (p_validate                => FALSE
    ,p_effective_date          => l_EFF_DT
    ,p_user_row_id             => l_row_id
    ,p_user_column_id          => l_app_col_id
    ,p_value                   => 'XXAS'
    ,p_business_group_id       => l_BG_ID
    ,p_user_column_instance_id => l_val_id
    ,p_object_version_number   => l_val_ovn
    ,p_effective_start_date    => l_val_eff_start
    ,p_effective_end_date      => l_val_eff_end);
    
  END IF;
    
  l_exists := 'N';    
  OPEN  csr_get_udt_row('Missed Punches');
  FETCH csr_get_udt_row INTO l_exists;
  CLOSE csr_get_udt_row;
  
  IF nvl(l_exists,'N') = 'N' THEN
  --
  -- Create the Missed Punch Entry 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 => 'Missed Punches'
    ,p_display_sequence      => l_sequence
    ,p_business_group_id     => l_BG_ID
    ,p_user_row_id           => l_row_id
    ,p_object_version_number => l_row_ovn
    ,p_effective_start_date  => l_row_eff_start
    ,p_effective_end_date    => l_row_eff_end);

  l_sequence := l_sequence + 1;

  --
  -- Create the Missed Punch Entry Transation Type Value
  --
  pay_user_column_instance_api.create_user_column_instance
    (p_validate                => FALSE
    ,p_effective_date          => l_EFF_DT
    ,p_user_row_id             => l_row_id
    ,p_user_column_id          => l_trans_type_col_id
    ,p_value                   => 'XXAS_MP'
    ,p_business_group_id       => l_BG_ID
    ,p_user_column_instance_id => l_val_id
    ,p_object_version_number   => l_val_ovn
    ,p_effective_start_date    => l_val_eff_start
    ,p_effective_end_date      => l_val_eff_end);

  --
  -- Create the Missed Punch Entry Application Value
  --
  pay_user_column_instance_api.create_user_column_instance
    (p_validate                => FALSE
    ,p_effective_date          => l_EFF_DT
    ,p_user_row_id             => l_row_id
    ,p_user_column_id          => l_app_col_id
    ,p_value                   => 'XXAS'
    ,p_business_group_id       => l_BG_ID
    ,p_user_column_instance_id => l_val_id
    ,p_object_version_number   => l_val_ovn
    ,p_effective_start_date    => l_val_eff_start
    ,p_effective_end_date      => l_val_eff_end);
    
    END IF;
    
EXCEPTION

  WHEN e_already_exists THEN

    NULL;
    
END;
/
COMMIT;
EXIT;
