Rem
Rem $Header: beacon_txn_view_pkgdef.sql 27-apr-2007.16:19:42 andyao Exp $
Rem
Rem beacon_txn_view_pkgdef.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      beacon_txn_view_pkgdef.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    andyao      04/27/07 - add query to get all platform/version
Rem    mfidanbo    08/29/05 - 
Rem    mfidanbo    07/13/05 - support timezone conversion 
Rem    andyao      06/29/05 - add procedure to get beacon status 
Rem    mfidanbo    06/23/05 - alerts sql for test 
Rem    mfidanbo    04/10/05 - add getter for marker time for tests 
Rem    mfidanbo    01/07/05 - change some signatures (guids should be raw)
Rem    mfidanbo    11/19/04 - dependency issue 
Rem    mfidanbo    11/14/04 - txn avail changes 
Rem    mfidanbo    11/10/04 - mfidanbo_mta_changes
Rem    mfidanbo    11/09/04 - Created
Rem

CREATE OR REPLACE PACKAGE MGMT_BCN_TXN_VIEW AS

TYPE CURSORTYPE IS REF CURSOR;

FUNCTION get_test_start_date( target_guid_in IN RAW,
                              test_guid_in IN RAW,
                              start_date_in IN DATE,
                              end_date_in IN DATE
                            )
RETURN DATE;

FUNCTION get_test_end_date( target_guid_in IN RAW,
                              test_guid_in IN RAW,
                              start_date_in IN DATE,
                              end_date_in IN DATE
                            )
RETURN DATE;

FUNCTION get_test_guid(
             tgt_guid IN RAW,
             test_name   IN VARCHAR2,
             test_type   IN VARCHAR2) RETURN RAW;

FUNCTION get_cur_test_status(
             target_name_in IN VARCHAR2,
             target_type_in IN VARCHAR2,
             test_name_in   IN VARCHAR2,
             test_type_in   IN VARCHAR2
                           ) RETURN NUMBER;

FUNCTION get_cur_test_status(
             tgt_guid    IN RAW,
             tst_guid   IN RAW
                           ) RETURN NUMBER;

PROCEDURE GET_TEST_AVAIL (
          target_name_in IN  VARCHAR2,
          target_type_in IN  VARCHAR2,
          test_name_in   IN  VARCHAR2,
          test_type_in   IN  VARCHAR2,
          num_days_in    IN  NUMBER,
          start_date_out OUT DATE,
          end_date_out   OUT DATE,
          avail_pct_out  OUT NUMBER,
          current_status OUT NUMBER,
          since          OUT DATE,
          avail_states   OUT CURSORTYPE);

PROCEDURE GET_SING_TEST_STATUS (
          target_name_in IN  VARCHAR2,
          target_type_in IN  VARCHAR2,
          test_guid_in   IN  RAW,
          start_date_in  IN  DATE,
          end_date_in    IN  DATE,
          num_days_in    IN  NUMBER,
          avail_pct_out  OUT NUMBER,
          current_status OUT NUMBER,
          since          OUT DATE);

PROCEDURE GET_CUR_TESTS_STATUS ( 
          target_name_in IN  VARCHAR2,
          target_type_in IN  VARCHAR2,
          tests_in       IN  SMP_EMD_NVPAIR_ARRAY,
          status_cursor  OUT CURSORTYPE);

PROCEDURE GET_TESTS_STATUS_AVAIL (
          target_name_in IN  VARCHAR2,
          target_type_in IN  VARCHAR2,
          tests_in       IN  SMP_EMD_NVPAIR_ARRAY,
          start_date_in  IN  DATE,
          end_date_in    IN  DATE,
          num_of_days_in IN  NUMBER,
          tz_used        IN  VARCHAR2,
          status_cursor  OUT CURSORTYPE);

PROCEDURE GET_BCN_TXN_STATUS (
              target_name_in IN VARCHAR2,
              target_type_in IN VARCHAR2,
              test_guids_in IN SMP_EMD_STRING_ARRAY,
              bcn_guids_in  IN SMP_EMD_STRING_ARRAY,
              status_cursor OUT CURSORTYPE);

PROCEDURE GET_CUR_BEACONS_STATUS (
          bcn_guids_in IN SMP_EMD_STRING_ARRAY,
          status_cursor OUT CURSORTYPE);

PROCEDURE GET_CUR_BEACONS_AGENT_INFO (
          bcn_guids_in      IN SMP_EMD_STRING_ARRAY,
          agent_info_cursor OUT CURSORTYPE);
              
TYPE TargetGUIDList IS VARRAY(3) OF RAW(16); 
TYPE CurrentStatusList IS VARRAY(3) OF NUMBER; 
TYPE MemberListType IS
  RECORD
  (
    targetGUID     TargetGUIDList,
    currentStatus  CurrentStatusList
  );

PROCEDURE GET_BCN_TXN_AVAIL (
              target_name_in IN VARCHAR2,
              target_type_in IN VARCHAR2,
              start_date_in  IN DATE,
              end_date_in    IN DATE,
              num_days_in    IN  NUMBER,
              test_guids_in IN SMP_EMD_STRING_ARRAY,
              bcn_guids_in  IN SMP_EMD_STRING_ARRAY,
              tz_used        IN  VARCHAR2,
              start_date_out OUT DATE,
              end_date_out   OUT DATE,
              status_cursor OUT mgmt_bcn_txn_avail_array);

PROCEDURE GET_TXN_ALERTS (
              target_name_in IN VARCHAR2,
              target_type_in IN VARCHAR2,
              test_name_in IN VARCHAR2,
              test_type_in IN VARCHAR2,
              alerts_out OUT CURSORTYPE);

-- Internal use only
FUNCTION GET_SING_BCN_TXN_STATUS(target_name_in IN VARCHAR2,
                            target_type_in IN VARCHAR2,
                            target_guid_in IN RAW,
                            txn_guid_in    IN RAW,
                            key_value_in   IN VARCHAR2)
RETURN NUMBER;


END MGMT_BCN_TXN_VIEW;
/
show errors;