index

Package sb_athletic_competition

Common Business interface for the ATHLETIC_COMPETITION API (sb_athletic_competition).

Program units
f_api_version   Returns the API version number.
f_calc_seasons_remaining   Returns calculated seasons of competition remaining.
f_calc_seasons_used   Returns the calculated the number of seasons of competition used.
f_exists   Checks if a record exists.
f_isequal   Compares two records for equality.
f_query_all   Selects all records for the entity.
f_query_one   Selects one record using the key.
f_query_by_rowid   Selects one record using the ROWID.
f_query_one_lock   Selects one record and locks it.
f_current_exists   Tests to see if a current record already exists for the sport.
f_find_max_competition_term   Checks for prior or future term for a sport
f_copy_competition_validation   Checks to see if the competition record can be copied.
f_copy_competition   Copies competition data from one term to another.
f_query_current   Selects the current record for the sport.
f_same_as_current   Compares the insert to the current record.
p_create   Creates a record.
p_delete   Deletes a record.
p_lock   Locks a record.
p_update   Updates a record.

Types
athletic_competition_rec   Business Entity record type
athletic_competition_ref   Entity cursor variable type
athletic_competition_tab   Entity table type

Constants
M_ENTITY_NAME   Business Entity Name
M_BASE_TABLE_NAME   Base table name

M_ENTITY_NAME 

M_ENTITY_NAME        CONSTANT VARCHAR2(20) := 'ATHLETIC_COMPETITION';

Business Entity Name

M_BASE_TABLE_NAME 

M_BASE_TABLE_NAME    CONSTANT VARCHAR2(7) := 'SGRATHC';

Base table name

athletic_competition_rec 

TYPE athletic_competition_rec IS RECORD (
  r_pidm                    sgrathc.sgrathc_pidm%TYPE,
  r_term_code               sgrathc.sgrathc_term_code%TYPE,
  r_actc_code               sgrathc.sgrathc_actc_code%TYPE,
  r_seq_no                  sgrathc.sgrathc_seq_no%TYPE,
  r_current_ind             sgrathc.sgrathc_current_ind%TYPE,
  r_seasons_available       sgrathc.sgrathc_seasons_available%TYPE,
  r_season_used_ind         sgrathc.sgrathc_season_used_ind%TYPE,
  r_user_id                 sgrathc.sgrathc_user_id%TYPE,
  r_create_activity_date    sgrathc.sgrathc_create_activity_date%TYPE,
  r_create_user_id          sgrathc.sgrathc_create_user_id%TYPE,
  r_sare_code               sgrathc.sgrathc_sare_code%TYPE,
  r_elig_begin_term_code    sgrathc.sgrathc_elig_begin_term_code%TYPE,
  r_elig_end_term_code      sgrathc.sgrathc_elig_end_term_code%TYPE,
  r_data_origin             sgrathc.sgrathc_data_origin%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type);

Business Entity record type

athletic_competition_ref 

TYPE athletic_competition_ref IS REF CURSOR RETURN athletic_competition_rec;

Entity cursor variable type

athletic_competition_tab 

TYPE athletic_competition_tab IS TABLE OF athletic_competition_rec INDEX BY BINARY_INTEGER;

Entity table type

f_api_version 

FUNCTION f_api_version RETURN PLS_INTEGER;

Returns the API version number.

Returns
Version of the API signature. Changes only when the signature changes.

f_calc_seasons_remaining 

FUNCTION f_calc_seasons_remaining(p_seasons_avail IN SGRATHC.SGRATHC_SEASONS_AVAILABLE%TYPE DEFAULT 0,
                                  p_seasons_used  IN PLS_INTEGER DEFAULT 0)
  RETURN PLS_INTEGER;

Returns calculated seasons of competition remaining.

Parameters
p_seasons_avail   Number of seasons of competition available. NUMBER(2)
p_seasons_used   Number of seasons of competition used. NUMBER(2)

Returns
Number of seasons of competition remaining.

f_calc_seasons_used 

FUNCTION f_calc_seasons_used(p_pidm      IN SGRATHC.SGRATHC_PIDM%TYPE,
                             p_term_code IN SGRATHC.SGRATHC_TERM_CODE%TYPE,
                             p_actc_code IN SGRATHC.SGRATHC_ACTC_CODE%TYPE,
                             p_seq_no    IN SGRATHC.SGRATHC_SEQ_NO%TYPE)
  RETURN PLS_INTEGER;

Returns the calculated the number of seasons of competition used.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key

Returns
Number of seasons of competion used.

f_exists 

FUNCTION f_exists(p_pidm      sgrathc.sgrathc_pidm%TYPE,
                  p_term_code sgrathc.sgrathc_term_code%TYPE,
                  p_actc_code sgrathc.sgrathc_actc_code%TYPE,
                  p_seq_no    sgrathc.sgrathc_seq_no%TYPE,
                  p_rowid     gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2;

Checks if a record exists.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key
p_rowid   Database ROWID of the record to be selected. VARCHAR2(18)

Returns
Y if found, otherwise N.

f_isequal 

FUNCTION f_isequal(rec_one athletic_competition_rec,
                   rec_two athletic_competition_rec) RETURN VARCHAR2;

Compares two records for equality.
Tests each field of rec_one against the corresponding field of rec_two. Two null values are considered equal.

Parameters
rec_one   The first record to compare. Type athletic_competition_rec Required
rec_two   The second record to compare. Type athletic_competition_rec Required

Returns
Y if all values in records are equal, otherwise N. Nulls match Nulls.

f_query_all 

FUNCTION f_query_all(p_pidm        sgrathc.sgrathc_pidm%TYPE DEFAULT NULL,
                     p_term_code   sgrathc.sgrathc_term_code%TYPE DEFAULT NULL,
                     p_actc_code   sgrathc.sgrathc_actc_code%TYPE DEFAULT NULL,
                     p_seq_no      sgrathc.sgrathc_seq_no%TYPE DEFAULT NULL,
                     p_current_ind sgrathc.sgrathc_current_ind%TYPE DEFAULT NULL)
  RETURN athletic_competition_ref;

Selects all records for the entity.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key
p_current_ind   Indicator for whether the record is the current record (Y) or a previous record (N). VARCHAR2(1) Required

Returns
A cursor variable that will fetch a set of records.

f_query_one 

FUNCTION f_query_one(p_pidm      sgrathc.sgrathc_pidm%TYPE,
                     p_term_code sgrathc.sgrathc_term_code%TYPE,
                     p_actc_code sgrathc.sgrathc_actc_code%TYPE,
                     p_seq_no    sgrathc.sgrathc_seq_no%TYPE)
  RETURN athletic_competition_ref;

Selects one record using the key.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key

Returns
A cursor variable that will fetch exactly one record.

f_query_by_rowid 

FUNCTION f_query_by_rowid(p_rowid gb_common.internal_record_id_type)
  RETURN athletic_competition_ref;

Selects one record using the ROWID.

Parameters
p_rowid   Database ROWID of the record to be selected. VARCHAR2(18) Required

Returns
A cursor variable that will fetch exactly one record.

f_query_one_lock 

FUNCTION f_query_one_lock(p_pidm      sgrathc.sgrathc_pidm%TYPE,
                          p_term_code sgrathc.sgrathc_term_code%TYPE,
                          p_actc_code sgrathc.sgrathc_actc_code%TYPE,
                          p_seq_no    sgrathc.sgrathc_seq_no%TYPE,
                          p_rowid     gb_common.internal_record_id_type DEFAULT NULL)
  RETURN athletic_competition_ref;

Selects one record and locks it.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key
p_rowid   Database ROWID of the record to be selected. VARCHAR2(18)

Returns
A cursor variable for one record, locking the record.

f_current_exists 

FUNCTION f_current_exists(p_pidm      IN sgrathc.sgrathc_pidm%TYPE,
                          p_actc_code IN sgrathc.sgrathc_actc_code%TYPE)
  RETURN VARCHAR2;

Tests to see if a current record already exists for the sport.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key

Returns
Y if found, otherwise N.

f_find_max_competition_term 

FUNCTION f_find_max_competition_term(p_pidm      IN sgrathc.sgrathc_pidm%TYPE,
                                     p_actc_code IN sgrathc.sgrathc_actc_code%TYPE)
  RETURN VARCHAR2;

Checks for prior or future term for a sport

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key

Returns
term if found, otherwise null.

f_copy_competition_validation 

FUNCTION f_copy_competition_validation(p_pidm      IN sgrathc.sgrathc_pidm%TYPE,
                                       p_actc_code IN sgrathc.sgrathc_actc_code%TYPE,
                                       p_from_term IN sgrathc.sgrathc_term_code%TYPE,
                                       p_to_term   IN sgrathc.sgrathc_term_code%TYPE)
  RETURN VARCHAR2;

Checks to see if the competition record can be copied.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_from_term   Term code to copy the sport from. VARCHAR2(6) Required Key
p_to_term   Term code to copy the sport to. VARCHAR2(6) Required Key

Returns
Y or N.

f_copy_competition 

FUNCTION f_copy_competition(p_pidm            IN sgrathc.sgrathc_pidm%TYPE,
                            p_actc_code       IN sgrathc.sgrathc_actc_code%TYPE,
                            p_from_term_code  IN sgrathc.sgrathc_term_code%TYPE,
                            p_to_term_code    IN sgrathc.sgrathc_term_code%TYPE,
                            p_season_used_ind IN sgrathc.sgrathc_season_used_ind%TYPE DEFAULT NULL)
  RETURN VARCHAR2;

Copies competition data from one term to another.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_from_term   Term code to copy the sport from. VARCHAR2(6) Required Key
p_to_term   Term code to copy the sport to. VARCHAR2(6) Required Key
p_season_used_ind   Indicator that athlete has used a season of competition in this term. VARCHAR2(1) Optional

Returns
S(uccess), F(ailure of create), N(o record to copy exists), E(rror, cannot create competition in this term).

f_query_current 

FUNCTION f_query_current(p_pidm      IN sgrathc.sgrathc_pidm%TYPE,
                         p_actc_code IN sgrathc.sgrathc_actc_code%TYPE,
                         p_term_code IN sgrathc.sgrathc_term_code%TYPE DEFAULT NULL)
  RETURN athletic_competition_ref;

Selects the current record for the sport.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key

Returns
A cursor variable that will fetch exactly one record.

f_same_as_current 

FUNCTION f_same_as_current(p_pidm                 sgrathc.sgrathc_pidm%TYPE,
                           p_term_code            sgrathc.sgrathc_term_code%TYPE,
                           p_actc_code            sgrathc.sgrathc_actc_code%TYPE,
                           p_seasons_available    sgrathc.sgrathc_seasons_available%TYPE,
                           p_season_used_ind      sgrathc.sgrathc_season_used_ind%TYPE,
                           p_sare_code            sgrathc.sgrathc_sare_code%TYPE DEFAULT NULL,
                           p_elig_begin_term_code sgrathc.sgrathc_elig_begin_term_code%TYPE DEFAULT NULL,
                           p_elig_end_term_code   sgrathc.sgrathc_elig_end_term_code%TYPE DEFAULT NULL)
  RETURN VARCHAR2;

Compares the insert to the current record.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seasons_available   Number of seasons of competition available. NUMBER(2) Required
p_season_used_ind   Indicator that athlete has used a season of competition in this term. VARCHAR2(1) Required
p_sare_code   Reason code for a change to the number of seasons available. VARCHAR2(4)
p_elig_begin_term_code   Starting eligibility term code for the athletic competition. VARCHAR2(6)
p_elig_end_term_code   Ending eligibility term code for the athletic competition. VARCHAR2(6)

p_create 

PROCEDURE p_create(p_pidm                 sgrathc.sgrathc_pidm%TYPE,
                   p_term_code            sgrathc.sgrathc_term_code%TYPE,
                   p_actc_code            sgrathc.sgrathc_actc_code%TYPE,
                   p_seq_no               sgrathc.sgrathc_seq_no%TYPE,
                   p_current_ind          sgrathc.sgrathc_current_ind%TYPE,
                   p_seasons_available    sgrathc.sgrathc_seasons_available%TYPE,
                   p_season_used_ind      sgrathc.sgrathc_season_used_ind%TYPE,
                   p_user_id              sgrathc.sgrathc_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_create_activity_date sgrathc.sgrathc_create_activity_date%TYPE,
                   p_create_user_id       sgrathc.sgrathc_create_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_sare_code            sgrathc.sgrathc_sare_code%TYPE DEFAULT NULL,
                   p_elig_begin_term_code sgrathc.sgrathc_elig_begin_term_code%TYPE DEFAULT NULL,
                   p_elig_end_term_code   sgrathc.sgrathc_elig_end_term_code%TYPE DEFAULT NULL,
                   p_data_origin          sgrathc.sgrathc_data_origin%TYPE DEFAULT NULL,
                   p_rowid_out            OUT gb_common.internal_record_id_type);

Creates a record.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key
p_current_ind   Indicator for whether the record is the current record (Y) or a previous record (N). VARCHAR2(1) Required
p_seasons_available   Number of seasons of competition available. NUMBER(2) Required
p_season_used_ind   Indicator that athlete has used a season of competition in this term. VARCHAR2(1) Required
p_user_id   ID of the user who created or updated the row in the SGRATHC table. VARCHAR2(30) Required
p_create_activity_date   Date that the information for the row was inserted in the SGRATHC table. DATE Required
p_create_user_id   ID of the user who created the row in the SGRATHC table. VARCHAR2(30) Required
p_sare_code   Reason code for a change to the number of seasons available. VARCHAR2(4)
p_elig_begin_term_code   Starting eligibility term code for the athletic competition. VARCHAR2(6)
p_elig_end_term_code   Ending eligibility term code for the athletic competition. VARCHAR2(6)
p_data_origin   Value of the source system that inserted the row. VARCHAR2(30)
p_rowid_out   Database ROWID of the record to be created. VARCHAR2(18) Required

p_delete 

PROCEDURE p_delete(p_pidm      sgrathc.sgrathc_pidm%TYPE,
                   p_term_code sgrathc.sgrathc_term_code%TYPE,
                   p_actc_code sgrathc.sgrathc_actc_code%TYPE,
                   p_seq_no    sgrathc.sgrathc_seq_no%TYPE,
                   p_rowid     gb_common.internal_record_id_type DEFAULT NULL);

Deletes a record.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key
p_rowid   Database ROWID of the record to be deleted. VARCHAR2(18)

p_lock 

PROCEDURE p_lock(p_pidm        sgrathc.sgrathc_pidm%TYPE,
                 p_term_code   sgrathc.sgrathc_term_code%TYPE,
                 p_actc_code   sgrathc.sgrathc_actc_code%TYPE,
                 p_seq_no      sgrathc.sgrathc_seq_no%TYPE,
                 p_rowid_inout IN OUT NOCOPY gb_common.internal_record_id_type);

Locks a record.
If the ROWID is not passed in, the record is located using the key values and the ROWID of the locked row is passed in p_rowid_inout.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(4) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key
p_rowid_inout   Database ROWID of the record to be locked. VARCHAR2(18) Required

p_update 

PROCEDURE p_update(p_pidm                 sgrathc.sgrathc_pidm%TYPE,
                   p_term_code            sgrathc.sgrathc_term_code%TYPE,
                   p_actc_code            sgrathc.sgrathc_actc_code%TYPE,
                   p_seq_no               sgrathc.sgrathc_seq_no%TYPE,
                   p_current_ind          sgrathc.sgrathc_current_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_seasons_available    sgrathc.sgrathc_seasons_available%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_season_used_ind      sgrathc.sgrathc_season_used_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id              sgrathc.sgrathc_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_create_activity_date sgrathc.sgrathc_create_activity_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_create_user_id       sgrathc.sgrathc_create_user_id%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_sare_code            sgrathc.sgrathc_sare_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_elig_begin_term_code sgrathc.sgrathc_elig_begin_term_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_elig_end_term_code   sgrathc.sgrathc_elig_end_term_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin          sgrathc.sgrathc_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid                gb_common.internal_record_id_type DEFAULT NULL);

Updates a record.

Parameters
p_pidm   Internal identification number (PIDM) of the athlete for which the competition information belongs. NUMBER(8) Required Key
p_term_code   Term code for the athletic competition. VARCHAR2(6) Required Key
p_actc_code   Code for the sport activity. VARCHAR2(8) Required Key
p_seq_no   Sequence number of the competition record for the athlete for the sport. NUMBER(8) Required Key
p_current_ind   Indicator for whether the record is the current record (Y) or a previous record (N). VARCHAR2(1) Required
p_seasons_available   Number of seasons of competition available. NUMBER(2) Required
p_season_used_ind   Indicator that athlete has used a season of competition in this term. VARCHAR2(1) Required
p_user_id   ID of the user who created or updated the row in the SGRATHC table. VARCHAR2(30) Required
p_create_activity_date   Date that the information for the row was inserted in the SGRATHC table. DATE
p_create_user_id   ID of the user who created the row in the SGRATHC table. VARCHAR2(30)
p_sare_code   Reason code for a change to the number of seasons available. VARCHAR2(4)
p_elig_begin_term_code   Starting eligibility term code for the athletic competition. VARCHAR2(6)
p_elig_end_term_code   Ending eligibility term code for the athletic competition. VARCHAR2(6)
p_data_origin   Value of the source system that inserted the row. VARCHAR2(30)
p_rowid   Database ROWID of the record to be updated. VARCHAR2(18)