index

Package gb_pcol_degree

Common Business interface for the PRIOR COLLEGE DEGREE API (gb_pcol_degree).
 
The Prior College Degree API creates information about the person's educational background, including institutions attended and degrees received at each institution.
Before prior college degree information can be created, identification information must exist for the person.
 
A person can have many prior college degree records.  Prior college degree records must have a parent record that exists in the prior college table (SORPCOL).  Internal identification number and source background institution code make up the prior college degree foreign key.  The prior college degree record has a unique key index made up by internal identification number, source background institution code, degree code and degree sequence number.  The degree code is nullable.  Prior college degree records can have prior college major, prior college minor and or prior college concentration child records.
 
To create a degree record, the internal identification number, source background institution code and degree sequence number are required.  The degree sequence number is an IN OUT parameter which is a one up number based on internal identification number and source background institution code.  A prior college degree record can specify an attend from date, attend to date and a graduation date.  The graduation date must be greater than or equal to the attend to date.  The attend from date must be less than the attend to date.  If specified, the prior college degree primary indicator must be an N or Y.
If specified, the prior college terminal degree indicator must be an N or Y.
 
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_delete   Deletes a record.
p_lock   Locks a record.
p_update   Updates a record.

Types
pcol_degree_rec   Business Entity record type
pcol_degree_ref   Entity cursor variable type
degree_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(11) := 'PCOL_DEGREE';

Business Entity Name

M_BASE_TABLE_NAME 

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

Base table name

pcol_degree_rec 

TYPE pcol_degree_rec IS RECORD(
  r_pidm               sordegr.sordegr_pidm%TYPE,
  r_sbgi_code          sordegr.sordegr_sbgi_code%TYPE,
  r_degc_code          sordegr.sordegr_degc_code%TYPE,
  r_degr_seq_no        sordegr.sordegr_degr_seq_no%TYPE,
  r_attend_from        sordegr.sordegr_attend_from%TYPE,
  r_attend_to          sordegr.sordegr_attend_to%TYPE,
  r_hours_transferred  sordegr.sordegr_hours_transferred%TYPE,
  r_gpa_transferred    sordegr.sordegr_gpa_transferred%TYPE,
  r_degc_date          sordegr.sordegr_degc_date%TYPE,
  r_degc_year          sordegr.sordegr_degc_year%TYPE,
  r_coll_code          sordegr.sordegr_coll_code%TYPE,
  r_honr_code          sordegr.sordegr_honr_code%TYPE,
  r_term_degree        sordegr.sordegr_term_degree%TYPE,
  r_egol_code          sordegr.sordegr_egol_code%TYPE,
  r_primary_ind        sordegr.sordegr_primary_ind%TYPE,
  r_data_origin        sordegr.sordegr_data_origin%TYPE,
  r_user_id            sordegr.sordegr_user_id%TYPE,
  r_internal_record_id gb_common.internal_record_id_type);

Business Entity record type

pcol_degree_ref 

TYPE pcol_degree_ref IS REF CURSOR RETURN pcol_degree_rec;

Entity cursor variable type

degree_tab 

TYPE degree_tab IS TABLE OF pcol_degree_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        sordegr.sordegr_pidm%TYPE,
                  p_sbgi_code   sordegr.sordegr_sbgi_code%TYPE,
                  p_degc_code   sordegr.sordegr_degc_code%TYPE,
                  p_degr_seq_no sordegr.sordegr_degr_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. NUMBER(8) Required Key
p_sbgi_code   Prior college code of the prospect or applicant. VARCHAR2(6) Required Key
p_degc_code   The degree received at the prior college. VARCHAR2(6) Key
p_degr_seq_no   A unique sequence number assigned to the prior college degree. NUMBER(2) 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_degree_rec,
                   rec_two pcol_degree_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_degree_rec Required
rec_two   The second record to compare. Type pcol_degree_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        sordegr.sordegr_pidm%TYPE,
                     p_sbgi_code   sordegr.sordegr_sbgi_code%TYPE DEFAULT NULL,
                     p_degc_code   sordegr.sordegr_degc_code%TYPE DEFAULT NULL,
                     p_degr_seq_no sordegr.sordegr_degr_seq_no%TYPE DEFAULT NULL)
  RETURN pcol_degree_ref

Selects all records for the entity.

Parameters
p_pidm   Internal identification number. NUMBER(8) Required Key
p_sbgi_code   Prior college code of the prospect or applicant. VARCHAR2(6) Required Key
p_degc_code   The degree received at the prior college. VARCHAR2(6) Key
p_degr_seq_no   A unique sequence number assigned to the prior college degree. NUMBER(2) Required Key

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

f_query_one 

FUNCTION f_query_one(p_pidm        sordegr.sordegr_pidm%TYPE,
                     p_sbgi_code   sordegr.sordegr_sbgi_code%TYPE,
                     p_degc_code   sordegr.sordegr_degc_code%TYPE,
                     p_degr_seq_no sordegr.sordegr_degr_seq_no%TYPE)
  RETURN pcol_degree_ref

Selects one record using key.

Parameters
p_pidm   Internal identification number. NUMBER(8) Required Key
p_sbgi_code   Prior college code of the prospect or applicant. VARCHAR2(6) Required Key
p_degc_code   The degree received at the prior college. VARCHAR2(6) Key
p_degr_seq_no   A unique sequence number assigned to the prior college degree. NUMBER(2) 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_degree_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        sordegr.sordegr_pidm%TYPE,
                          p_sbgi_code   sordegr.sordegr_sbgi_code%TYPE,
                          p_degc_code   sordegr.sordegr_degc_code%TYPE,
                          p_degr_seq_no sordegr.sordegr_degr_seq_no%TYPE,
                          p_rowid       gb_common.internal_record_id_type DEFAULT NULL)
  RETURN pcol_degree_ref

Selects one record and locks it.

Parameters
p_pidm   Internal identification number. NUMBER(8) Required Key
p_sbgi_code   Prior college code of the prospect or applicant. VARCHAR2(6) Required Key
p_degc_code   The degree received at the prior college. VARCHAR2(6) Key
p_degr_seq_no   A unique sequence number assigned to the prior college degree. NUMBER(2) 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              sordegr.sordegr_pidm%TYPE,
                   p_sbgi_code         sordegr.sordegr_sbgi_code%TYPE,
                   p_degc_code         sordegr.sordegr_degc_code%TYPE,
                   p_degr_seq_no_inout IN OUT sordegr.sordegr_degr_seq_no%TYPE,
                   p_attend_from       sordegr.sordegr_attend_from%TYPE DEFAULT NULL,
                   p_attend_to         sordegr.sordegr_attend_to%TYPE DEFAULT NULL,
                   p_hours_transferred sordegr.sordegr_hours_transferred%TYPE DEFAULT NULL,
                   p_gpa_transferred   sordegr.sordegr_gpa_transferred%TYPE DEFAULT NULL,
                   p_degc_date         sordegr.sordegr_degc_date%TYPE DEFAULT NULL,
                   p_degc_year         sordegr.sordegr_degc_year%TYPE DEFAULT NULL,
                   p_coll_code         sordegr.sordegr_coll_code%TYPE DEFAULT NULL,
                   p_honr_code         sordegr.sordegr_honr_code%TYPE DEFAULT NULL,
                   p_term_degree       sordegr.sordegr_term_degree%TYPE DEFAULT NULL,
                   p_egol_code         sordegr.sordegr_egol_code%TYPE DEFAULT NULL,
                   p_primary_ind       sordegr.sordegr_primary_ind%TYPE DEFAULT NULL,
                   p_data_origin       sordegr.sordegr_data_origin%TYPE DEFAULT NULL,
                   p_user_id           sordegr.sordegr_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 of the prospect or applicant. VARCHAR2(6) Required Key
p_degc_code   The degree received at the prior college. VARCHAR2(6) Key
p_degr_seq_no   A unique sequence number assigned to the prior college degree. NUMBER(2) Required Key
p_attend_from   The last date of attendance at the prior college. DATE
p_attend_to   The first date of attendance at the prior college. DATE
p_hours_transferred   The total number of hours transferred from the prior college.  This field is informational and does not update the transfer GPA in Academic History. NUMBER(11,3)
p_gpa_transferred   The transfer GPA.  This is informational and does not update the transfer GPA in Academic History. NUMBER(23,9)
p_degc_date   Prior college degree date. DATE
p_degc_year   Prior college degree year. VARCHAR2(4)
p_coll_code   Prior college code. VARCHAR2(2)
p_honr_code   Honor code. VARCHAR2(6)
p_term_degree   Terminal degree indicator. VARCHAR2(1)
p_egol_code   Education Goal Code. VARCHAR2(2)
p_primary_ind   Primary School Indicator. VARCHAR2(1)
p_data_origin   Source system that created or updated the row. VARCHAR2(30)
p_user_id   The Oracle 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_delete 

PROCEDURE p_delete(p_pidm        sordegr.sordegr_pidm%TYPE,
                   p_sbgi_code   sordegr.sordegr_sbgi_code%TYPE,
                   p_degc_code   sordegr.sordegr_degc_code%TYPE,
                   p_degr_seq_no sordegr.sordegr_degr_seq_no%TYPE,
                   p_rowid       gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record.
When deleting a prior college degree record, if any child records exist the delete will fail.

Parameters
p_pidm   Internal identification number. NUMBER(8) Required Key
p_sbgi_code   Prior college code of the prospect or applicant. VARCHAR2(6) Required Key
p_degc_code   The degree received at the prior college. VARCHAR2(6) Key
p_degr_seq_no   A unique sequence number assigned to the prior college degree. NUMBER(2) Required Key
p_rowid   Database ROWID of the record to be deleted. VARCHAR2(18)

p_lock 

PROCEDURE p_lock(p_pidm        sordegr.sordegr_pidm%TYPE,
                 p_sbgi_code   sordegr.sordegr_sbgi_code%TYPE,
                 p_degc_code   sordegr.sordegr_degc_code%TYPE,
                 p_degr_seq_no sordegr.sordegr_degr_seq_no%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 of the prospect or applicant. VARCHAR2(6) Required Key
p_degc_code   The degree received at the prior college. VARCHAR2(6) Key
p_degr_seq_no   A unique sequence number assigned to the prior college degree. NUMBER(2) Required Key
p_rowid_inout   Database ROWID of the record to be locked. VARCHAR2(18) Required

p_update 

PROCEDURE p_update(p_pidm              sordegr.sordegr_pidm%TYPE,
                   p_sbgi_code         sordegr.sordegr_sbgi_code%TYPE,
                   p_degc_code         sordegr.sordegr_degc_code%TYPE,
                   p_degr_seq_no       sordegr.sordegr_degr_seq_no%TYPE,
                   p_attend_from       sordegr.sordegr_attend_from%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_attend_to         sordegr.sordegr_attend_to%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_hours_transferred sordegr.sordegr_hours_transferred%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_gpa_transferred   sordegr.sordegr_gpa_transferred%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_degc_date         sordegr.sordegr_degc_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_degc_year         sordegr.sordegr_degc_year%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_coll_code         sordegr.sordegr_coll_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_honr_code         sordegr.sordegr_honr_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_term_degree       sordegr.sordegr_term_degree%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_egol_code         sordegr.sordegr_egol_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_primary_ind       sordegr.sordegr_primary_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin       sordegr.sordegr_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id           sordegr.sordegr_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_rowid             gb_common.internal_record_id_type DEFAULT NULL)

Updates a record.

Parameters
p_pidm   Internal identification number. NUMBER(8) Required Key
p_sbgi_code   Prior college code of the prospect or applicant. VARCHAR2(6) Required Key
p_degc_code   The degree received at the prior college. VARCHAR2(6) Key
p_degr_seq_no   A unique sequence number assigned to the prior college degree. NUMBER(2) Required Key
p_attend_from   The last date of attendance at the prior college. DATE
p_attend_to   The first date of attendance at the prior college. DATE
p_hours_transferred   The total number of hours transferred from the prior college.  This field is informational and does not update the transfer GPA in Academic History. NUMBER(11,3)
p_gpa_transferred   The transfer GPA.  This is informational and does not update the transfer GPA in Academic History. NUMBER(23,9)
p_degc_date   Prior college degree date. DATE
p_degc_year   Prior college degree year. VARCHAR2(4)
p_coll_code   Prior college code. VARCHAR2(2)
p_honr_code   Honor code. VARCHAR2(6)
p_term_degree   Terminal degree indicator. VARCHAR2(1)
p_egol_code   Education Goal Code. VARCHAR2(2)
p_primary_ind   Primary School Indicator. VARCHAR2(1)
p_data_origin   Source system that created or updated the row. VARCHAR2(30)
p_user_id   The Oracle ID of the user who changed the record. VARCHAR2(30)
p_rowid   Database ROWID of the record to be updated. VARCHAR2(18)