index

Package gb_identification

Provides the Common Business interface for the IDENTIFICATION API (gb_identification).
Each person or non-person (company, vendor, etc.) in Banner has a Personal Identification Master number (PIDM).

Each PIDM is stored in the SPRIDEN table, with the Banner ID, name and other associated data. Only one record for a PIDM is considered the current active record. This record has spriden_change_ind set to NULL.

One or more names or IDs may be associated with a PIDM. These are audit records and are used to log name and ID changes.

Since ID, Name, and Name Type are all parts of the key, and are updatable, p_delete and p_update identify the current record in different ways.
See the specific procedure documentation for details.

Program units
f_api_version   Returns the API version number.
f_exists   Checks to see if a record exists.
f_get_entity   Returns the entity indicator that defines if the PIDM belongs to a Person or Company.
f_isequal   Compares two records for equality.
f_query_by_rowid   Selects one record using ROWID.
f_query_one   Selects one Identification record.
f_query_all   Selects a set of Identification records.
f_query_one_lock   Selects one record and locks it for update.
p_lock   Locks a record.
p_create   Creates an Identification record.
p_delete   Deletes a record.
p_update   Updates an Identification record.
f_idname_search   Performs an ID or Name search.
f_ssnname_search   Performs an SSN search.

Types
identification_rec   Entity record type
idname_search_rec   ID Name Search record type
identification_ref   Entity cursor variable type
idname_search_ref   ID Name Search cursor variable type
id_tab   Entity table type

Constants
M_ENTITY_NAME   Business Entity name

M_ENTITY_NAME 

M_ENTITY_NAME   CONSTANT VARCHAR2 (14) := 'IDENTIFICATION';

Business Entity name

identification_rec 

TYPE identification_rec IS RECORD (
  r_pidm                        spriden.spriden_pidm%TYPE,
  r_id                          spriden.spriden_id%TYPE,
  r_last_name                   spriden.spriden_last_name%TYPE,
  r_first_name                  spriden.spriden_first_name%TYPE,
  r_mi                          spriden.spriden_mi%TYPE,
  r_change_ind                  spriden.spriden_change_ind%TYPE,
  r_entity_ind                  spriden.spriden_entity_ind%TYPE,
  r_user                        spriden.spriden_user%TYPE,
  r_origin                      spriden.spriden_origin%TYPE,
  r_ntyp_code                   spriden.spriden_ntyp_code%TYPE,
  r_data_origin                 spriden.spriden_data_origin%TYPE,
  r_create_fdmn_code            spriden.spriden_create_fdmn_code%TYPE,
  r_surname_prefix              spriden.spriden_surname_prefix%TYPE,
  r_internal_record_id          gb_common.internal_record_id_type);

Entity record type

idname_search_rec 

TYPE idname_search_rec IS RECORD (
  r_pidm                        spriden.spriden_pidm%TYPE,
  r_id                          spriden.spriden_id%TYPE,
  r_name                        spriden.spriden_last_name%TYPE,
  r_internal_record_id          gb_common.internal_record_id_type,
  r_distinct_query_count        INTEGER);

ID Name Search record type

identification_ref 

TYPE identification_ref IS REF CURSOR
  RETURN identification_rec;

Entity cursor variable type

idname_search_ref 

TYPE idname_search_ref IS REF CURSOR
  RETURN idname_search_rec;

ID Name Search cursor variable type

id_tab 

TYPE id_tab IS TABLE OF identification_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_exists 

FUNCTION f_exists(p_pidm           spriden.spriden_pidm%TYPE,
                  p_id             spriden.spriden_id%TYPE,
                  p_last_name      spriden.spriden_last_name%TYPE,
                  p_first_name     spriden.spriden_first_name%TYPE,
                  p_mi             spriden.spriden_mi%TYPE,
                  p_change_ind     spriden.spriden_change_ind%TYPE,
                  p_ntyp_code      spriden.spriden_ntyp_code%TYPE,
                  p_surname_prefix spriden.spriden_surname_prefix%TYPE DEFAULT NULL,
                  p_rowid          gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks to see if a record exists.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Required Key
p_id   Identification number used to access the person on-line. VARCHAR2(9) Required Key
p_last_name   Last name of the person. VARCHAR2(60) Required Key
p_first_name   First name of the person. VARCHAR2(15) Key
p_mi   Middle initial of the person. VARCHAR2(15) Key
p_change_ind   The type of change made to the record. VARCHAR2(1) Required  Key
NULL   Current record
I   ID change
N   Name change

p_ntyp_code   Name Type. VARCHAR2(1)
p_rowid   Database ROWID of the record to be selected. VARCHAR2(18)

Returns
Y if found, otherwise N.

f_get_entity 

FUNCTION f_get_entity(p_pidm spriden.spriden_pidm%TYPE) RETURN VARCHAR2

Returns the entity indicator that defines if the PIDM belongs to a Person or Company.
 
Looks only at the current record for the PIDM.

Returns
C if it is a company, P if it is a person, NULL if not found.

f_isequal 

FUNCTION f_isequal(rec_one identification_rec,
                   rec_two identification_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. The Change indicator is ignored so that current and audit records can be compared.

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

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

f_query_by_rowid 

FUNCTION f_query_by_rowid(p_rowid gb_common.internal_record_id_type)
  RETURN identification_ref

Selects one record using ROWID.

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

Returns
A cursor variable that will fetch exactly one record.

f_query_one 

FUNCTION f_query_one(p_pidm  spriden.spriden_pidm%TYPE,
                     p_rowid gb_common.internal_record_id_type DEFAULT NULL)
  RETURN identification_ref

Selects one Identification record.
 
Either returns the current row for the PIDM given (change_ind IS NULL), or the row identified by the ROWID. The ROWID takes precedence.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Required Key
p_rowid   Database ROWID of the record to be selected. VARCHAR(18) Required

Returns
A cursor variable that will fetch exactly one record.

f_query_all 

FUNCTION f_query_all(p_pidm           spriden.spriden_pidm%TYPE,
                     p_id             spriden.spriden_id%TYPE DEFAULT NULL,
                     p_last_name      spriden.spriden_last_name%TYPE DEFAULT NULL,
                     p_first_name     spriden.spriden_first_name%TYPE DEFAULT NULL,
                     p_mi             spriden.spriden_mi%TYPE DEFAULT NULL,
                     p_change_ind     spriden.spriden_change_ind%TYPE DEFAULT NULL,
                     p_ntyp_code      spriden.spriden_ntyp_code%TYPE DEFAULT NULL,
                     p_surname_prefix spriden.spriden_surname_prefix%TYPE DEFAULT NULL)
  RETURN identification_ref

Selects a set of Identification records.
 
PIDM is the only required parameter.  Any other parameters constrain the set of records returned. NULL matches NULL, and % wildcards can be used because the non-PIDM parameters are matched with the LIKE operator rather than =.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Key
p_id   Identification number used to access the person on-line. VARCHAR2(9) Key
p_last_name   Last name of the person. VARCHAR2(60) Key
p_first_name   First name of the person. VARCHAR2(15) Key
p_mi   Middle initial of the person. VARCHAR2(15) Key
p_change_ind   The type of change made to the record. VARCHAR2(1)  Key
NULL   Current record
I   ID change
N   Name change

p_ntyp_code   Name type. VARCHAR2(1)

Returns
A cursor variable for record.

f_query_one_lock 

FUNCTION f_query_one_lock(p_pidm  spriden.spriden_pidm%TYPE,
                          p_rowid gb_common.internal_record_id_type DEFAULT NULL)
  RETURN identification_ref

Selects one record and locks it for update.
 
Either locks the current row for the PIDM given (change_ind IS NULL), or locks the row identified by the ROWID. The ROWID takes precedence.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Required Key
p_rowid   Database row ID of the record to be locked. VARCHAR2(18)

Returns
A cursor variable that will lock and then fetch exactly one record

p_lock 

PROCEDURE p_lock(p_pidm           spriden.spriden_pidm%TYPE,
                 p_id             spriden.spriden_id%TYPE,
                 p_last_name      spriden.spriden_last_name%TYPE,
                 p_first_name     spriden.spriden_first_name%TYPE,
                 p_mi             spriden.spriden_mi%TYPE,
                 p_change_ind     spriden.spriden_change_ind%TYPE,
                 p_ntyp_code      spriden.spriden_ntyp_code%TYPE,
                 p_surname_prefix spriden.spriden_surname_prefix%TYPE DEFAULT NULL,
                 p_rowid_inout    IN OUT gb_common.internal_record_id_type)

Locks a record.
 
If ROWID is passed, it locks that row. Otherwise, the record is located using the key values.
The ROWID of the locked row is returned in p_rowid_inout.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Required Key
p_id   Identification number used to access the person on-line. VARCHAR2(9) Required Key
p_last_name   Last name of the person. VARCHAR2(60) Required Key
p_first_name   First name of the person. VARCHAR2(15) Key
p_mi   Middle initial of the person. VARCHAR2(15) Key
p_change_ind   The type of change made to the record. VARCHAR2(1) Required  Key
NULL   Current record
I   ID change
N   Name change

p_ntyp_code   Name type. VARCHAR2(1)
p_rowid_inout   Database ROWID of the record to be locked. VARCHAR2(18) Required

p_create 

PROCEDURE p_create(p_id_inout       IN OUT spriden.spriden_id%TYPE,
                   p_last_name      spriden.spriden_last_name%TYPE,
                   p_first_name     spriden.spriden_first_name%TYPE DEFAULT NULL,
                   p_mi             spriden.spriden_mi%TYPE DEFAULT NULL,
                   p_change_ind     spriden.spriden_change_ind%TYPE DEFAULT NULL,
                   p_entity_ind     spriden.spriden_entity_ind%TYPE DEFAULT NULL,
                   p_user           spriden.spriden_user%TYPE DEFAULT gb_common.f_sct_user,
                   p_origin         spriden.spriden_origin%TYPE DEFAULT NULL,
                   p_ntyp_code      spriden.spriden_ntyp_code%TYPE DEFAULT NULL,
                   p_data_origin    spriden.spriden_data_origin%TYPE DEFAULT NULL,
                   p_surname_prefix spriden.spriden_surname_prefix%TYPE DEFAULT NULL,
                   p_pidm_inout     IN OUT spriden.spriden_pidm%TYPE,
                   p_rowid_out      OUT gb_common.internal_record_id_type)

Creates an Identification record.
 
There are two main modes of operation for this procedure, creating an initial record, and creating audit or history records.
The p_change_ind parameter controls the mode.
There are many interdependencies between parameters.

Creating a new record for the first time.

If p_change_ind is NULL, then a new record is being inserted, and tests are done to make sure any PIDM or ID passed in is not already in the database.
If ID is set to the special keyword 'GENERATED', then a new ID is generated.
If p_pidm_inout is NULL, a new PIDM is also generated. If p_pidm_inout is not null, it must at least be a positive integer not already in the database.

Creating a history record.

If p_change_ind is NOT NULL, then an audit or history record is being inserted.  A PIDM and ID are required in this case.

ID Change
If p_change_ind is 'I', the record is an ID change audit record.  The name fields must match the name fields of the current record. The p_id value cannot already exist in the database for this PIDM.

Name Change.

If p_change_ind is 'N', the record is a name change audit record. There must exist a record with p_id and p_pidm in the database. The name must not have been used by this PIDM before.

If the entity indicator specifies that the transaction is for a company (spriden_entity_ind= 'C'), the first name is forced to a null string.
Parameters
p_id_inout   Identification number used to access the person on-line. VARCHAR2(9) Required Key
p_last_name   Last name of the person. VARCHAR2(60) Required Key
p_first_name   First name of the person. Required if the entity is a person, not allowed for companies. VARCHAR2(15) Key
p_mi   Middle initial of the person. VARCHAR2(15) Key
p_change_ind   The type of change made to the record. VARCHAR2(1) Required  Key
NULL   Current record
I   ID change
N   Name change

p_entity_ind   Entity Indicator. VARCHAR2(1)
P   Person
C   Non-Person

p_user   The Oracle ID for the user that most recently updated the record. VARCHAR2(30)
p_origin   The name of the Banner Object that was used most recently to update the row in the spriden table. VARCHAR2(30)
p_ntyp_code   Name type. VARCHAR2(4) Key
p_create_user   Banner User ID which created new record. VARCHAR2(30)
p_create_date   The date the new record was created. DATE
p_data_origin   Source system that generated the data. VARCHAR2(30)
p_pidm_inout   Internal identification number of the person. NUMBER(8) Key Required
p_rowid_out   Database ROWID of the record to be created. VARCHAR(18) Required

p_delete 

PROCEDURE p_delete(p_pidm           spriden.spriden_pidm%TYPE,
                   p_id             spriden.spriden_id%TYPE,
                   p_last_name      spriden.spriden_last_name%TYPE,
                   p_first_name     spriden.spriden_first_name%TYPE,
                   p_mi             spriden.spriden_mi%TYPE,
                   p_change_ind     spriden.spriden_change_ind%TYPE,
                   p_ntyp_code      spriden.spriden_ntyp_code%TYPE,
                   p_surname_prefix spriden.spriden_surname_prefix%TYPE DEFAULT NULL,
                   p_rowid          gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record.
 
Only audit records may be deleted. Therefore, p_change_ind cannot be NULL.
If p_change_ind of row being deleted is 'I' then lock all rows with that ID for delete of 'I' row, and then update to the new ID for other rows.
Otherwise just lock the specific row to be deleted.

Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Required Key
p_id   Identification number used to access the person on-line. VARCHAR2(9) Required
p_last_name   Last name of the person. VARCHAR2(60) Required
p_first_name   First name of the person. VARCHAR2(15)
p_mi   Middle initial of the person. VARCHAR2(15)
p_change_ind   The type of change that was last made to this record. VARCHAR2(1)
I   ID change
N   Name change.

p_ntyp_code   Name type. VARCHAR2(1)
p_surname_prefix   Last name prefix.  VARCHAR2(60)
p_rowid   Database ROWID of the record to be deleted. VARCHAR(18)

p_update 

PROCEDURE p_update(p_pidm           spriden.spriden_pidm%TYPE,
                   p_id             spriden.spriden_id%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_last_name      spriden.spriden_last_name%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_first_name     spriden.spriden_first_name%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_mi             spriden.spriden_mi%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_change_ind     spriden.spriden_change_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_entity_ind     spriden.spriden_entity_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user           spriden.spriden_user%TYPE DEFAULT gb_common.f_sct_user,
                   p_origin         spriden.spriden_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_ntyp_code      spriden.spriden_ntyp_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin    spriden.spriden_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_surname_prefix spriden.spriden_surname_prefix%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid          gb_common.internal_record_id_type DEFAULT NULL)

Updates an Identification record.
 

This procedure handles updates of both current Identification records and Audit or history records.

When the current record is updated with a new ID or Name, an audit record is automatically created with the old data and with change_ind set to N or I depending on what type of update happened. In this audit record, the user and activity date are set to the current user name and system date.

The API determines on its own if the change is an ID or Name change.  The p_change_ind parameter is required for historical reasons, but is no longer used or updated in the database by this procedure.

If an audit record is being updated, only the name type can be changed. No other modifications to audit records are allowed.

Several assumptions are made in order to determine if the current record is to be updated or if an audit record is being updated.

If ROWID is passed in, then the corresponding row is updated, which could be any row. If ROWID is null, then the current record for the PIDM (where change_ind IS NULL) is the row being updated. This is the most common update.

Once the record is located, all the other values passed, ID, Name etc, are treated as values to use in the update, even though they are part of the unique key.

The following edits are performed to determine if the update can proceed.

NOTE:No audit record is created for name type changes.
Parameters
p_pidm   Internal identification number of the person. NUMBER(8) Key Required
p_id   Identification number used to access the person on-line. VARCHAR2(9) Required Key
p_last_name   Last name of the person. VARCHAR2(60) Required Key
p_first_name   First name of the person. Required if the entity is a person, not allowed for companies. VARCHAR2(15) Required Key
p_mi   Middle initial of the person. VARCHAR2(15) Required Key
p_change_ind   The type of change made to the record. This parameter is obsolete for this procedure. VARCHAR2(1) Required  Key
NULL   Current record
I   ID change
N   Name change

p_entity_ind   Entity Indicator. VARCHAR2(1)
P   Person
C   Non-Person

p_user   The Oracle ID for the user that most recently updated the record. VARCHAR2(30)
p_origin   The name of the Banner Object that was used most recently to update the row in the spriden table. VARCHAR2(30)
p_ntyp_code   Name type. VARCHAR2(4) Required Key
p_data_origin   Source system that generated the data. VARCHAR2(30)
p_surname_prefix   Last name prefix.  VARCHAR2(60)
p_rowid_out   Database ROWID of the record to be updated. VARCHAR(18)

f_idname_search 

FUNCTION f_idname_search(p_id         spriden.spriden_id%TYPE,
                         p_last_name  spriden.spriden_last_name%TYPE,
                         p_first_name spriden.spriden_first_name%TYPE,
                         p_mi         spriden.spriden_mi%TYPE,
                         p_ntyp_code  spriden.spriden_ntyp_code%TYPE,
                         p_entity_ind spriden.spriden_entity_ind%TYPE,
                         p_city       spraddr.spraddr_city%TYPE,
                         p_stat_code  spraddr.spraddr_stat_code%TYPE,
                         p_zip        spraddr.spraddr_zip%TYPE,
                         p_ssn        spbpers.spbpers_ssn%TYPE,
                         p_sex        spbpers.spbpers_sex%TYPE,
                         p_birth_date VARCHAR2) RETURN g_idname_search_nt

Performs an ID or Name search.

Parameters
p_id   Identification number used to access the person on-line. VARCHAR2(9)
p_last_name   Last name of the person. VARCHAR2(60)
p_first_name   First name of the person. VARCHAR2(15)
p_mi   Middle initial of the person. VARCHAR2(15)
p_ntyp_code   Name Type. VARCHAR2(1)
p_entity_ind   Entity Indicator. VARCHAR2(1)
P   Person
C   Non-Person

p_city   City associated with the address of the person. VARCHAR2(20)
p_stat_code   State associated with the address of the person. VARCHAR2(3)
p_zip   ZIP code associated with the address of the person. VARCHAR2(10)
p_ssn   The person's social security number. VARCHAR2(9)
p_sex   Gender of the person. VARCHAR2(1)
M   Male
F   Female
N   Unknown

p_birth_date   The person's date of birth. VARCHAR2(11)

Returns
A nested table of an object type containing result records.

f_ssnname_search 

FUNCTION f_ssnname_search(p_id         spriden.spriden_id%TYPE,
                          p_last_name  spriden.spriden_last_name%TYPE,
                          p_first_name spriden.spriden_first_name%TYPE,
                          p_mi         spriden.spriden_mi%TYPE,
                          p_ntyp_code  spriden.spriden_ntyp_code%TYPE,
                          p_entity_ind spriden.spriden_entity_ind%TYPE,
                          p_city       spraddr.spraddr_city%TYPE,
                          p_stat_code  spraddr.spraddr_stat_code%TYPE,
                          p_zip        spraddr.spraddr_zip%TYPE,
                          p_ssn        spbpers.spbpers_ssn%TYPE,
                          p_sex        spbpers.spbpers_sex%TYPE,
                          p_birth_date VARCHAR2) RETURN g_idname_search_nt

Performs an SSN search.

Parameters
p_id   SSN number that can be used to access the person or persons on-line. VARCHAR2(9)
p_last_name   Last name of the person. VARCHAR2(60)
p_first_name   First name of the person. VARCHAR2(15)
p_mi   Middle initial of the person. VARCHAR2(15)
p_ntyp_code   Name Type. VARCHAR2(1)
p_entity_ind   Entity Indicator. VARCHAR2(1)
P   Person
C   Non-Person

p_city   City associated with the address of the person. VARCHAR2(20)
p_stat_code   State associated with the address of the person. VARCHAR2(3)
p_zip   ZIP code associated with the address of the person. VARCHAR2(10)
p_ssn   The person's social security number. VARCHAR2(9)
p_sex   Gender of the person. VARCHAR2(1)
M   Male
F   Female
N   Unknown

p_birth_date   The person's date of birth. VARCHAR2(11)

Returns
A nested table of an object type containing result records.