Package gb_pcol

Package gb_pcol_minor

This package provides the Common Business interface for the PRIOR COLLEGE MINOR API (gb_pcol_minor).

The Prior College Minor API creates information about the person's educational background, including institutions attended, degrees received at each institution and minors at each institution.
Before prior college minor information can be created, identification information must exist for the person.

A person can have many prior college minor records. Prior college minor records must have a corresponding prior college degree record in the prior college degree table (SORDEGR). Internal identification number, source background institution code, degree code and degree sequence number must exist in the prior college degree table for a minor record. The prior college degree record has a unique key index made up by internal identification number, source background institution code, degree code, degree sequence number and minor code. The degree code is nullable in the table.

To create a prior college minor record, the internal identification number, source background institution code, degree sequence number and minor code are required.

The p_update procedure will not get called when updating a prior college minor record. Since minor code is required and part of the unique key, the minor code will be updated by calling the p_delete and then the p_create procedure.

This API is called from multiple product lines. Some of its logic may be used by specific product lines only. For these cases, context variables are being used. The context variable CHECK_HR_SECURITY is used when the calling product line is Human Resources in order to perform person access checks.

Program units

f_api_version	Returns the API version number.
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 key.
f_query_by_rowid	Selects one record using ROWID.
f_query_one_lock	Selects one record and locks it.
p_create	Creates a record.
p_copy_all	Copies all child minor records to a new degree code and degree sequence number, currently called from forms when a user is calling the duplicate record functionality for a new degree record.
p_delete	Deletes a record.
p_lock	Locks a record.
p_update	Updates a record.

Types

pcol_minor_rec	Business Entity record type
pcol_minor_ref	Entity cursor variable type
minor_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(10) := 'PCOL_MINOR';

Business Entity Name

M_BASE_TABLE_NAME

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

Base table name

pcol_minor_rec

TYPE pcol_minor_rec IS RECORD (
  r_pidm                    sorminr.sorminr_pidm%TYPE,
  r_sbgi_code               sorminr.sorminr_sbgi_code%TYPE,
  r_degc_code               sorminr.sorminr_degc_code%TYPE,
  r_degr_seq_no             sorminr.sorminr_degr_seq_no%TYPE,
  r_majr_code_minor         sorminr.sorminr_majr_code_minor%TYPE,
  r_data_origin             sorminr.sorminr_data_origin%TYPE,
  r_user_id                 sorminr.sorminr_user_id%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type);

Business Entity record type

pcol_minor_ref

TYPE pcol_minor_ref IS REF CURSOR RETURN pcol_minor_rec;

Entity cursor variable type

minor_tab

TYPE minor_tab IS TABLE OF pcol_minor_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            sorminr.sorminr_pidm%TYPE,
                  p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE,
                  p_degc_code       sorminr.sorminr_degc_code%TYPE,
                  p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE,
                  p_majr_code_minor sorminr.sorminr_majr_code_minor%TYPE,
                  p_rowid           gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks if a record exists.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_sbgi_code	Prior college code. The SBGI code must be previously defined on the source/background institution code validation table. VARCHAR2(6) Required Key
	p_degc_code	Prior college degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degr_seq_no	Prior college degree sequence number. NUMBER(2) Required Key
	p_majr_code_minor	Minor code. The minor code must be previously defined as a minor on the Major, Minor, Concentration validation table. VARCHAR2(4) 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 pcol_minor_rec,
                   rec_two pcol_minor_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 pcol_minor_rec Required
	rec_two	The second record to compare. Type pcol_minor_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            sorminr.sorminr_pidm%TYPE,
                     p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE DEFAULT NULL,
                     p_degc_code       sorminr.sorminr_degc_code%TYPE DEFAULT NULL,
                     p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE DEFAULT NULL,
                     p_majr_code_minor sorminr.sorminr_majr_code_minor%TYPE DEFAULT NULL)
  RETURN pcol_minor_ref

Selects all records for the entity.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_sbgi_code	Prior college code. The SBGI code must be previously defined on the source/background institution code validation table. VARCHAR2(6) Required Key
	p_degc_code	Prior college degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degr_seq_no	Prior college degree sequence number. NUMBER(2) Required Key
	p_majr_code_minor	Minor code. The minor code must be previously defined as a minor on the Major, Minor, Concentration validation table. VARCHAR2(4) Required Key

Returns

A cursor variable that will fetch the set of records.

f_query_one

FUNCTION f_query_one(p_pidm            sorminr.sorminr_pidm%TYPE,
                     p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE,
                     p_degc_code       sorminr.sorminr_degc_code%TYPE,
                     p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE,
                     p_majr_code_minor sorminr.sorminr_majr_code_minor%TYPE)
  RETURN pcol_minor_ref

Selects one record using key.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_sbgi_code	Prior college code. The SBGI code must be previously defined on the source/background institution code validation table. VARCHAR2(6) Required Key
	p_degc_code	Prior college degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degr_seq_no	Prior college degree sequence number. NUMBER(2) Required Key
	p_majr_code_minor	Minor code. The minor code must be previously defined as a minor on the Major, Minor, Concentration validation table. VARCHAR2(4) 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 pcol_minor_ref

Selects one record using 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            sorminr.sorminr_pidm%TYPE,
                          p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE,
                          p_degc_code       sorminr.sorminr_degc_code%TYPE,
                          p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE,
                          p_majr_code_minor sorminr.sorminr_majr_code_minor%TYPE,
                          p_rowid           gb_common.internal_record_id_type DEFAULT NULL)
  RETURN pcol_minor_ref

Selects one record and locks it.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_sbgi_code	Prior college code. The SBGI code must be previously defined on the source/background institution code validation table. VARCHAR2(6) Required Key
	p_degc_code	Prior college degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degr_seq_no	Prior college degree sequence number. NUMBER(2) Required Key
	p_majr_code_minor	Minor code. The minor code must be previously defined as a minor on the Major, Minor, Concentration validation table. VARCHAR2(4) Required Key
	p_rowid	Database ROWID of the record to be selected. VARCHAR2(18)

Returns

A cursor variable for one record, locking the record.

p_create

PROCEDURE p_create(p_pidm            sorminr.sorminr_pidm%TYPE,
                   p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE,
                   p_degc_code       sorminr.sorminr_degc_code%TYPE,
                   p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE,
                   p_majr_code_minor sorminr.sorminr_majr_code_minor%TYPE,
                   p_data_origin     sorminr.sorminr_data_origin%TYPE DEFAULT NULL,
                   p_user_id         sorminr.sorminr_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_rowid_out       OUT gb_common.internal_record_id_type)

Creates a record.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_sbgi_code	Prior college code. The SBGI code must be previously defined on the source/background institution code validation table. VARCHAR2(6) Required Key
	p_degc_code	Prior college degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degr_seq_no	Prior college degree sequence number. NUMBER(2) Required Key
	p_majr_code_minor	Minor code. The minor code must be previously defined as a minor on the Major, Minor, Concentration validation table. VARCHAR2(4) Required Key
	p_data_origin	Source system that created or updated the row. VARCHAR2(30)
	p_user_id	The Oracle user ID of the user who changed the record. VARCHAR2(30)
	p_rowid_out	Database ROWID of the record to be created. VARCHAR2(18) Required

p_copy_all

PROCEDURE p_copy_all(p_pidm            sorminr.sorminr_pidm%TYPE,
                     p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE,
                     p_degc_code       sorminr.sorminr_degc_code%TYPE,
                     p_degc_code_new   sorminr.sorminr_degc_code%TYPE,
                     p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE,
                     p_degr_seq_no_new sorminr.sorminr_degr_seq_no%TYPE)

Copies all child minor records to a new degree code and degree sequence number, currently called from forms when a user is calling the duplicate record functionality for a new degree record.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_sbgi_code	Prior college code. The SBGI code must be previously defined on the source/background institution code validation table. VARCHAR2(6) Required Key
	p_degc_code	Prior college degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degc_code_new	Prior college new degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degr_seq_no	Prior college degree sequence number. NUMBER(2) Required Key
	p_degr_seq_no_new	Prior college new degree sequence number. NUMBER(2) Required Key

p_delete

PROCEDURE p_delete(p_pidm            sorminr.sorminr_pidm%TYPE,
                   p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE,
                   p_degc_code       sorminr.sorminr_degc_code%TYPE,
                   p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE,
                   p_majr_code_minor sorminr.sorminr_majr_code_minor%TYPE,
                   p_rowid           gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_sbgi_code	Prior college code. The SBGI code must be previously defined on the source/background institution code validation table. VARCHAR2(6) Required Key
	p_degc_code	Prior college degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degr_seq_no	Prior college degree sequence number. NUMBER(2) Required Key
	p_majr_code_minor	Minor code. The minor code must be previously defined as a minor on the Major, Minor, Concentration validation table. VARCHAR2(4) Required Key
	p_rowid	Database ROWID of the record to be deleted. VARCHAR2(18)

p_lock

PROCEDURE p_lock(p_pidm            sorminr.sorminr_pidm%TYPE,
                 p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE,
                 p_degc_code       sorminr.sorminr_degc_code%TYPE,
                 p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE,
                 p_majr_code_minor sorminr.sorminr_majr_code_minor%TYPE,
                 p_rowid_inout     IN OUT gb_common.internal_record_id_type)

Locks a record.
If 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. NUMBER(8) Required Key
	p_sbgi_code	Prior college code. The SBGI code must be previously defined on the source/background institution code validation table. VARCHAR2(6) Required Key
	p_degc_code	Prior college degree code. The degree code must be previously defined on the degree code validation table. VARCHAR2(6) Key
	p_degr_seq_no	Prior college degree sequence number. NUMBER(2) Required Key
	p_majr_code_minor	Minor code. The minor code must be previously defined as a minor on the Major, Minor, Concentration validation table. VARCHAR2(4) Required Key
	p_rowid_inout	Database ROWID of the record to be locked. VARCHAR2(18) Required

p_update

PROCEDURE p_update(p_pidm            sorminr.sorminr_pidm%TYPE,
                   p_sbgi_code       sorminr.sorminr_sbgi_code%TYPE,
                   p_degc_code       sorminr.sorminr_degc_code%TYPE,
                   p_degr_seq_no     sorminr.sorminr_degr_seq_no%TYPE,
                   p_majr_code_minor sorminr.sorminr_majr_code_minor%TYPE,
                   p_data_origin     sorminr.sorminr_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id         sorminr.sorminr_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_rowid           gb_common.internal_record_id_type DEFAULT NULL)

Updates a record.
The prior college minor entity (SORMINR) has a unique key index whose value cannot be updated so the p_update procedure is not supported.
Use p_delete and p_create instead.