Package gb_third_party

Package gb_third_party_access

This package provides the Common Business interface for Third Party Access.

In order for a person to have access to SunGard Third Party Access products such as Voice Response and Campus Pipeline/Luminis, a Personal Identification Number has to be set up in the Third Party Access table (GOBTPAC).
Each person that is set up in the table should have a Third Party ID.
This unique internal ID is used by Campus Pipeline/Luminis to route e-mail. It should be passed as a parameter to the API when a new record is created,or it will be generated by the API.
A person can have only one record in the table.
The Third Party Access record cannot be deleted through the API.

All changes related to the Personal Identification Number or to the Third Party ID are stored in Third Party Audit Table (GORPAUD).

If a ROWID is passed, it is used to locate the record for update and any other keys are ignored.

Program units

f_api_version	Returns the API version number.
f_exists	Checks to see if a Third Party Access record exists.
f_isequal	Compares two Third Party Access records for equality.
f_query_all	Selects all the Third Party Access records for the entity.
f_query_one	Selects one Third Party Access record using the key.
f_query_by_rowid	Selects one Third Party Access record using the ROWID.
f_query_one_lock	Selects one Third Party Access record and locks it.
p_create	Creates a Third Party Access record.
p_lock	Locks a Third Party Access record.
p_update	Updates a Third Party Access record.
f_get_pinhash	Returns a hashed PIN Value.
f_external_pin	Returns a hashed PIN Value.
f_validate_pin	This function returns TRUE if salted version of passed PIN matches PIN on file.
f_proc_pin	Resets PIN and returns new PIN value.

Types

third_party_access_rec
third_party_access_ref	Entity cursor variable type.
third_party_access_tab	Entity table type.

Constants

M_ENTITY_NAME

Business Entity name.

M_ENTITY_NAME

M_ENTITY_NAME    CONSTANT VARCHAR2(18) := 'THIRD_PARTY_ACCESS';

Business Entity name.

third_party_access_rec

TYPE third_party_access_rec IS RECORD (
   r_pidm                    gobtpac.gobtpac_pidm%TYPE,
   r_pin_disabled_ind        gobtpac.gobtpac_pin_disabled_ind%TYPE,
   r_usage_accept_ind        gobtpac.gobtpac_usage_accept_ind%TYPE,
   r_user                    gobtpac.gobtpac_user%TYPE,
   r_pin                     gobtpac.gobtpac_pin%TYPE,
   r_pin_exp_date            gobtpac.gobtpac_pin_exp_date%TYPE,
   r_external_user           gobtpac.gobtpac_external_user%TYPE,
   r_question                gobtpac.gobtpac_question%TYPE,
   r_response                gobtpac.gobtpac_response%TYPE,
   r_insert_source           gobtpac.gobtpac_insert_source%TYPE,
   r_ldap_user               gobtpac.gobtpac_ldap_user%TYPE,
   r_data_origin             gobtpac.gobtpac_data_origin%TYPE,
   r_salt                    gobtpac.gobtpac_salt%TYPE,
   r_internal_record_id      gb_common.internal_record_id_type);

third_party_access_ref

TYPE third_party_access_ref IS REF CURSOR RETURN third_party_access_rec;

Entity cursor variable type.

third_party_access_tab

TYPE third_party_access_tab IS TABLE OF third_party_access_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  gobtpac.gobtpac_pidm%TYPE,
                  p_rowid VARCHAR2 DEFAULT NULL) RETURN VARCHAR2

Checks to see if a Third Party Access record exists.

Parameters

	p_pidm	Banner PIDM. NUMBER(8) Required Key
	p_rowid	Database ROWID of the Third Party Access record to be selected. VARCHAR2(18)

Returns

Y if found, otherwise N.

f_isequal

FUNCTION f_isequal(rec_one third_party_access_rec,
                   rec_two third_party_access_rec) RETURN VARCHAR2

Compares two Third Party Access 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 Third Party Access record to compare. Type third_party_access_rec Required
	rec_two	The second Third Party Access record to compare. Type third_party_access_rec Required

Returns

Y if all values in the Third Party Access records are equal, otherwise N. Nulls match Nulls.

f_query_all

FUNCTION f_query_all(p_pidm gobtpac.gobtpac_pidm%TYPE)
  RETURN third_party_access_ref

Selects all the Third Party Access records for the entity.

Parameters

p_pidm

Banner PIDM. NUMBER(8) Required Key

Returns

A cursor variable that will fetch the set of Third Party Access records.

f_query_one

FUNCTION f_query_one(p_pidm gobtpac.gobtpac_pidm%TYPE)
  RETURN third_party_access_ref

Selects one Third Party Access record using the key.

Parameters

p_pidm

Banner PIDM. NUMBER(8) Required Key

Returns

A cursor variable that will fetch exactly one Third Party Access record.

f_query_by_rowid

FUNCTION f_query_by_rowid(p_rowid VARCHAR2) RETURN third_party_access_ref

Selects one Third Party Access record using the ROWID.

Parameters

p_rowid

Database ROWID of the Third Party Access record to be selected. VARCHAR2(18) Required

Returns

A cursor variable that will fetch exactly one Third Party Access record.

f_query_one_lock

FUNCTION f_query_one_lock(p_pidm  gobtpac.gobtpac_pidm%TYPE,
                          p_rowid VARCHAR2 DEFAULT NULL)
  RETURN third_party_access_ref

Selects one Third Party Access record and locks it.

Parameters

	p_pidm	Banner PIDM. NUMBER(8) Required Key
	p_rowid	Database ROWID of Third Party Access record to be selected. VARCHAR2(18)

Returns

A cursor variable for one Third Party Access record and locks the record.

p_create

PROCEDURE p_create(p_pidm                gobtpac.gobtpac_pidm%TYPE,
                   p_pin_disabled_ind    gobtpac.gobtpac_pin_disabled_ind%TYPE DEFAULT 'N',
                   p_usage_accept_ind    gobtpac.gobtpac_usage_accept_ind%TYPE DEFAULT 'N',
                   p_user                gobtpac.gobtpac_user%TYPE DEFAULT gb_common.f_sct_user,
                   p_pin                 gobtpac.gobtpac_pin%TYPE DEFAULT NULL,
                   p_pin_exp_date        gobtpac.gobtpac_pin_exp_date%TYPE DEFAULT NULL,
                   p_question            gobtpac.gobtpac_question%TYPE DEFAULT NULL,
                   p_response            gobtpac.gobtpac_response%TYPE DEFAULT NULL,
                   p_insert_source       gobtpac.gobtpac_insert_source%TYPE DEFAULT NULL,
                   p_ldap_user           gobtpac.gobtpac_ldap_user%TYPE DEFAULT NULL,
                   p_data_origin         gobtpac.gobtpac_data_origin%TYPE DEFAULT NULL,
                   p_salt                gobtpac.gobtpac_salt%TYPE DEFAULT NULL,
                   p_notification_ind    VARCHAR2 DEFAULT 'Y',
                   p_pin_reusechk_ind    VARCHAR2 DEFAULT 'N',
                   p_pin_encrypt_ind     VARCHAR2 DEFAULT 'N',
                   p_pin_validate_ind    VARCHAR2 DEFAULT 'Y',
                   p_external_user_inout IN OUT gobtpac.gobtpac_external_user%TYPE,
                   p_rowid_out           OUT VARCHAR2)

Creates a Third Party Access record.
After a record is created, the Personal Identification Number and the Third Party ID are stored in the Third Party Audit Table (GORPAUD), and the Sourced ID is generated and stored in the Sourced ID Base Table (GOBSRID). The Sourced ID is created only if it does not already exist for the person. It is a system-generated, one-up number used to synchronize the user's data with various SunGard partner systems.

If PIN notification preferences for the letter and/or e-mail generation process were set up on Enterprise PIN Preferences table (GUBPPRF) then a record for the notification preferences is saved in the Mail Table (GURMAIL).

Parameters

p_pidm

Banner PIDM. NUMBER(8) Required Key

p_pin_disabled_ind

PIN disabled indicator. VARCHAR2(1)

	Y	Access to SunGard Third Party Access products (Self-Service, VR, Kiosk) is denied.
	N	(default) Access to SunGard Third Party Access products (Self-Service, VR, Kiosk) is allowed.

p_usage_accept_ind

Terms of Usage accepted indicator. VARCHAR2(1)

	Y	The Terms of Usage page does not displays upon login to for the Self-Service products
	N	(default) The Terms of Usage page displays upon login to for the Self-Service products

p_user

Oracle User ID of the user who created or last modified the record. VARCHAR2(30)

p_pin

Personal Identification Number. It is used together with the Login ID to access SunGard Third Party products and other partner systems. It can be numeric or alphanumeric with a length between 1 and 99, depending on the institution's preference as defined on the Enterprise PIN Preferences table (GUBPPRF) VARCHAR2(255)

p_pin_exp_date

PIN expiration date. When it contains a value and that date is in the past, a user attempting to access a Third Party product will be required to change their PIN. DATE

p_question

Free-form question entered by a Web User to be used as a hint when trying to get their PIN reset. VARCHAR2(90)

p_response

Free-form response entered by a Web User to be used as an answer to the hint question when trying to get their PIN reset. VARCHAR2(30)

p_insert_source

Identifies the method used to create or update Third Party Access table. VARCHAR2(8)

	SELF	User changed the PIN record
	ADMIN	Administrator changed the PIN record
	SYSTEM	Record was changed by the logic in a process
	NULL	(default)

p_ldap_user

This will optionally store the non-LUMINIS LDAP user mapping for Banner. VARCHAR2(255)

p_data_origin

Source system that created or updated the row. VARCHAR2(30)

p_salt

The salt value used if the passed PIN is encrypted based upon p_pin_encrypt_ind. VARCHAR2(128)

p_notification_ind

Not being used. VARCHAR2(01)

p_pin_reusechk_ind

A value of 'Y' will check to make sure that the new PIN has not been used previously within the number of days as specified on GUBPPRF. VARCHAR2(01)

p_pin_encrypt_ind

A value of 'Y' will indicate that the passed PIN was encrypted using the passed salt. VARCHAR2(01)

p_pin_validate_ind

A value of 'Y' will check to make sure that the new PIN passes the validation rules as specified on GUBPPRF for character / numeric requirements, length, and reuse. VARCHAR2(01)

p_external_user_inout

Unique internal ID which may be passed to the partner systems as a login ID and/or e-mail ID. This third party ID should be lowercase letters, numbers, periods, hyphens, and underscores. If the value for an ID is not passed then system generates an ID. The ID is constructed from the first character of the user's first name and up to seven characters of the user's last name. Nonalphabetic characters other than periods, hyphens, and underscores are removed. If the ID is not unique, the last digit(s) are replaced with one-up numbers until the ID is unique. VARCHAR2(30) Required Key

p_rowid_out

Database ROWID of the record that was created. VARCHAR2(18) Required

p_lock

PROCEDURE p_lock(p_pidm        gobtpac.gobtpac_pidm%TYPE,
                 p_rowid_inout IN OUT VARCHAR2)

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

Parameters

	p_pidm	Banner PIDM. NUMBER(8) Required Key
	p_rowid_inout	Database ROWID of Third Party Access record to be locked. VARCHAR2(18) Required

p_update

PROCEDURE p_update(p_pidm             gobtpac.gobtpac_pidm%TYPE,
                   p_pin_disabled_ind gobtpac.gobtpac_pin_disabled_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_usage_accept_ind gobtpac.gobtpac_usage_accept_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user             gobtpac.gobtpac_user%TYPE DEFAULT gb_common.f_sct_user,
                   p_pin              gobtpac.gobtpac_pin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_pin_exp_date     gobtpac.gobtpac_pin_exp_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_external_user    gobtpac.gobtpac_external_user%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_question         gobtpac.gobtpac_question%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_response         gobtpac.gobtpac_response%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_insert_source    gobtpac.gobtpac_insert_source%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_ldap_user        gobtpac.gobtpac_ldap_user%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin      gobtpac.gobtpac_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_salt             gobtpac.gobtpac_salt%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_notification_ind VARCHAR2 DEFAULT 'Y',
                   p_pin_reusechk_ind VARCHAR2 DEFAULT 'N',
                   p_pin_encrypt_ind  VARCHAR2 DEFAULT 'N',
                   p_pin_validate_ind VARCHAR2 DEFAULT 'Y',
                   p_rowid            VARCHAR2 DEFAULT NULL)

Updates a Third Party Access record.
Changes related to the Personal Identification Number or to the Third Party ID are stored in Third Party Audit Table (GORPAUD).

If PIN notification preferences for the letter and/or e-mail generation process were set up on Enterprise PIN Preferences table (GUBPPRF) then a record for the preferences is saved in the Mail Table (GURMAIL).

Parameters

p_pidm

Banner PIDM. NUMBER(8) Required Key

p_pin_disabled_ind

PIN disabled indicator. VARCHAR2(1)

	Y	Access to SunGard Third Party Access products (Self-Service, VR, Kiosk) is denied.
	N	Access to SunGard Third Party Access products (Self-Service, VR, Kiosk) is allowed.

p_usage_accept_ind

Terms of Usage accepted indicator. VARCHAR2(1)

	Y	The Terms of Usage page does not displays upon login to for the Self-Service products
	N	The Terms of Usage page displays upon login to for the Self-Service products

p_user

Oracle User ID of the last the user who created or last modified the record. VARCHAR2(30)

p_pin

p_pin_exp_date

PIN expiration date. When valued and the date is in the past, a user attempting to access a Third Party product will be required to change their PIN. DATE

p_external_user

Unique internal ID which may be passed to the partner systems as a login ID and/or e-mail ID. Third party ID should be lowercase letters, numbers, periods, hyphens, and underscores. VARCHAR2(30)

p_question

Free-form question entered by a Web User to be used as a hint when trying to get their PIN reset. VARCHAR2(90)

p_response

Free-form response entered by a Web User to be used as an answer to the hint question when trying to get their PIN reset. VARCHAR2(30)

p_insert_source

Identifies the method used to create or update Third Party Access table. VARCHAR2(8)

	SELF	User changed the PIN record
	ADMIN	Administrator changed the PIN record
	SYSTEM	Record was changed by the logic in a process

p_ldap_user

This will optionally store a non-LUMINIS LDAP user mapping for Banner. VARCHAR2(255)

p_data_origin

Source system that created or last updated the row. VARCHAR2(30)

p_salt

The salt value used if the passed PIN is encrypted based upon p_pin_encrypt_ind. VARCHAR2(128)

p_notification_ind

Not being used. VARCHAR2(01)

p_pin_reusechk_ind

A value of 'Y' will check to make sure that the new PIN has not been used previously within the number of days as specified on GUBPPRF. VARCHAR2(01)

p_pin_encrypt_ind

A value of 'Y' will indicate that the passed PIN was encrypted using the passed salt. VARCHAR2(01)

p_pin_validate_ind

A value of 'Y' will check to make sure that the new PIN passes the validation rules as specified on GUBPPRF for character / numeric requirements, length, and reuse. VARCHAR2(01)

p_rowid

Database ROWID of the Third Party Access record to be updated. VARCHAR2(18)

f_get_pinhash

FUNCTION f_get_pinhash(p_pidm          IN gobtpac.gobtpac_pidm%TYPE,
                       p_cleartext_pin IN VARCHAR2) RETURN VARCHAR2

Returns a hashed PIN Value.
This function converts given PIN with associated salt to a salted hash

Parameters

	p_pidm	Banner PIDM. NUMBER(8) Required Key
	p_cleartext_PIN	PIN in cleartext. VARCHAR2

f_external_pin

FUNCTION f_external_pin(p_pidm IN gobtpac.gobtpac_pidm%TYPE)
  RETURN VARCHAR2

Returns a hashed PIN Value.
This function converts given PIN with associated salt to a salted hash in a format required for elearning

Parameters

p_pidm

Banner PIDM. NUMBER(8) Required Key

f_validate_pin

FUNCTION f_validate_pin(p_pidm        IN VARCHAR2,
                        p_pin         IN VARCHAR2,
                        p_expire_ind  OUT VARCHAR2,
                        p_disable_ind OUT VARCHAR2) RETURN BOOLEAN

This function returns TRUE if salted version of passed PIN matches PIN on file.
It also returns expiration and disabled indicators.

Parameters

	p_pidm	Banner PIDM. NUMBER(8) Required Key
	p_pin	PIN in cleartext to be validated. VARCHAR2(255)
	p_expire_ind	Returns a 'Y' if the PIN is expired, otherwise an 'N'. VARCHAR2(01)
	p_disable_ind	Returns a 'Y' if the PIN is disabled, otherwise an 'N'. VARCHAR2(01)

f_proc_pin

FUNCTION f_proc_pin(p_pidm IN NUMBER) RETURN VARCHAR2

Resets PIN and returns new PIN value.
This function will reset the PIN for the PIDM passed in based upon the reset rules in GUBPPRF.

Parameters

p_pidm

Banner PIDM. NUMBER(8) Required Key