Package sb_edi_acad

Package sb_edi_acad_session

This package provides the Common Business Interface for the EDI Academic Session API (sb_edi_acad_sess_sum).
For use with internal processing to produce the XML transcript.

This API is responsible for loading the academic session information from the imported XML transcript document and importing the information into the SHRASES table.

Program units

f_api_version	Returns the API version number.
f_exists	Checks to see 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.
p_create	Creates a record.
p_delete	Deletes a record.
p_lock	Locks a record.
p_update	Updates a record.

Types

edi_acad_session_rec	Business Entity record type
edi_acad_session_ref	Entity cursor variable type
edi_acad_session_tab	Entity table type

Constants

M_ENTITY_NAME

Business Entity Name

M_ENTITY_NAME

M_ENTITY_NAME    CONSTANT VARCHAR2(16) := 'EDI_ACAD_SESSION';

Business Entity Name

edi_acad_session_rec

TYPE edi_acad_session_rec IS RECORD (
  r_dcmt_seqno              shrases.shrases_dcmt_seqno%TYPE,
  r_seqno                   shrases.shrases_seqno%TYPE,
  r_start_date              shrases.shrases_start_date%TYPE,
  r_sess_no                 shrases.shrases_sess_no%TYPE,
  r_school_year             shrases.shrases_school_year%TYPE,
  r_sntp_code               shrases.shrases_sntp_code%TYPE,
  r_sess_name               shrases.shrases_sess_name%TYPE,
  r_sbql_code               shrases.shrases_sbql_code%TYPE,
  r_begin_date              shrases.shrases_begin_date%TYPE,
  r_seql_code               shrases.shrases_seql_code%TYPE,
  r_end_date                shrases.shrases_end_date%TYPE,
  r_slvl_code               shrases.shrases_slvl_code%TYPE,
  r_crql_code               shrases.shrases_crql_code%TYPE,
  r_curr_code               shrases.shrases_curr_code%TYPE,
  r_curr_name               shrases.shrases_curr_name%TYPE,
  r_honr_code               shrases.shrases_honr_code%TYPE,
  r_ovrd_code               shrases.shrases_ovrd_code%TYPE,
  r_inst_name_ovrd          shrases.shrases_inst_name_ovrd%TYPE,
  r_inql_code_ovrd          shrases.shrases_inql_code_ovrd%TYPE,
  r_inst_code_ovrd          shrases.shrases_inst_code_ovrd%TYPE,
  r_street_line1_ovrd       shrases.shrases_street_line1_ovrd%TYPE,
  r_street_line2_ovrd       shrases.shrases_street_line2_ovrd%TYPE,
  r_city_ovrd               shrases.shrases_city_ovrd%TYPE,
  r_stat_code_ovrd          shrases.shrases_stat_code_ovrd%TYPE,
  r_zip_ovrd                shrases.shrases_zip_ovrd%TYPE,
  r_natn_code_ovrd          shrases.shrases_natn_code_ovrd%TYPE,
  r_domestic_addr_ind       shrases.shrases_domestic_addr_ind%TYPE,
  r_street_line3            shrases.shrases_street_line3%TYPE,
  r_stateprovince           shrases.shrases_stateprovince%TYPE,
  r_data_origin             shrases.shrases_data_origin%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type);

Business Entity record type

edi_acad_session_ref

TYPE edi_acad_session_ref IS REF CURSOR RETURN edi_acad_session_rec;

Entity cursor variable type

edi_acad_session_tab

TYPE edi_acad_session_tab IS TABLE OF edi_acad_session_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_dcmt_seqno shrases.shrases_dcmt_seqno%TYPE,
                  p_seqno      shrases.shrases_seqno%TYPE,
                  p_rowid      gb_common.internal_record_id_type DEFAULT NULL)
   RETURN VARCHAR2;

Checks to see if a record exists.

Parameters

	p_dcmt_seqno	EDI document sequence number. NUMBER(8) Required Key.
	p_seqno	EDI academic session sequence number. NUMBER(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 edi_acad_session_rec,
                   rec_two edi_acad_session_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 edi_acad_session_rec Required.
	rec_two	The second record to compare. Type edi_acad_session_rec Required.

Returns

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

f_query_all

FUNCTION f_query_all(p_dcmt_seqno shrases.shrases_dcmt_seqno%TYPE,
                     p_seqno      shrases.shrases_seqno%TYPE)
   RETURN edi_acad_session_ref;

Selects all records for the entity.

Parameters

	p_dcmt_seqno	EDI document sequence number. NUMBER(8) Required Key.
	p_seqno	EDI academic session sequence number. NUMBER(4) Required Key.

Returns

A cursor variable that will fetch the set of records.

f_query_one

FUNCTION f_query_one(p_dcmt_seqno shrases.shrases_dcmt_seqno%TYPE,
                     p_seqno      shrases.shrases_seqno%TYPE)
   RETURN edi_acad_session_ref;

Selects one record using the key.

Parameters

	p_dcmt_seqno	EDI document sequence number. NUMBER(8) Required Key.
	p_seqno	EDI academic session sequence number. NUMBER(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 edi_acad_session_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_dcmt_seqno shrases.shrases_dcmt_seqno%TYPE,
                          p_seqno      shrases.shrases_seqno%TYPE,
                          p_rowid      gb_common.internal_record_id_type DEFAULT NULL)
   RETURN edi_acad_session_ref;

Selects one record and locks it.

Parameters

	p_dcmt_seqno	EDI document sequence number. NUMBER(8) Required Key.
	p_seqno	EDI academic session sequence number. NUMBER(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_dcmt_seqno        shrases.shrases_dcmt_seqno%TYPE,
                   p_seqno             shrases.shrases_seqno%TYPE,
                   p_start_date        shrases.shrases_start_date%TYPE DEFAULT NULL,
                   p_sess_no           shrases.shrases_sess_no%TYPE DEFAULT NULL,
                   p_school_year       shrases.shrases_school_year%TYPE DEFAULT NULL,
                   p_sntp_code         shrases.shrases_sntp_code%TYPE DEFAULT NULL,
                   p_sess_name         shrases.shrases_sess_name%TYPE DEFAULT NULL,
                   p_sbql_code         shrases.shrases_sbql_code%TYPE DEFAULT NULL,
                   p_begin_date        shrases.shrases_begin_date%TYPE DEFAULT NULL,
                   p_seql_code         shrases.shrases_seql_code%TYPE DEFAULT NULL,
                   p_end_date          shrases.shrases_end_date%TYPE DEFAULT NULL,
                   p_slvl_code         shrases.shrases_slvl_code%TYPE DEFAULT NULL,
                   p_crql_code         shrases.shrases_crql_code%TYPE DEFAULT NULL,
                   p_curr_code         shrases.shrases_curr_code%TYPE DEFAULT NULL,
                   p_curr_name         shrases.shrases_curr_name%TYPE DEFAULT NULL,
                   p_honr_code         shrases.shrases_honr_code%TYPE DEFAULT NULL,
                   p_ovrd_code         shrases.shrases_ovrd_code%TYPE DEFAULT NULL,
                   p_inst_name_ovrd    shrases.shrases_inst_name_ovrd%TYPE DEFAULT NULL,
                   p_inql_code_ovrd    shrases.shrases_inql_code_ovrd%TYPE DEFAULT NULL,
                   p_inst_code_ovrd    shrases.shrases_inst_code_ovrd%TYPE DEFAULT NULL,
                   p_street_line1_ovrd shrases.shrases_street_line1_ovrd%TYPE DEFAULT NULL,
                   p_street_line2_ovrd shrases.shrases_street_line2_ovrd%TYPE DEFAULT NULL,
                   p_city_ovrd         shrases.shrases_city_ovrd%TYPE DEFAULT NULL,
                   p_stat_code_ovrd    shrases.shrases_stat_code_ovrd%TYPE DEFAULT NULL,
                   p_zip_ovrd          shrases.shrases_zip_ovrd%TYPE DEFAULT NULL,
                   p_natn_code_ovrd    shrases.shrases_natn_code_ovrd%TYPE DEFAULT NULL,
                   p_domestic_addr_ind shrases.shrases_domestic_addr_ind%TYPE DEFAULT NULL,
                   p_street_line3      shrases.shrases_street_line3%TYPE DEFAULT NULL,
                   p_stateprovince     shrases.shrases_stateprovince%TYPE DEFAULT NULL,
                   p_data_origin       shrases.shrases_data_origin%TYPE DEFAULT NULL,
                   p_rowid_out         OUT gb_common.internal_record_id_type);

Creates a record.

Parameters

	p_dcmt_seqno	EDI document sequence number. NUMBER(8) Required Key.
	p_seqno	EDI academic session sequence number. NUMBER(4) Required Key.
	p_start_date	EDI session start month/year. VARCHAR2(6).
	p_sess_no	EDI specific session number. NUMBER(3).
	p_school_year	EDI K-12 school year. VARCHAR2(35).
	p_sntp_code	EDI type of session. VARCHAR2(1).
	p_sess_name	EDI name of session. VARCHAR2(35).
	p_sbql_code	EDI format for date session began. VARCHAR2(3).
	p_begin_date	EDI starting date. VARCHAR2(8).
	p_seql_code	EDI format of ending date. VARCHAR2(3).
	p_end_date	EDI ending date. VARCHAR2(8).
	p_slvl_code	EDI student grade level. VARCHAR2(2).
	p_crql_code	EDI curriculum code qualifier. VARCHAR2(2).
	p_curr_code	EDI curriculum code. VARCHAR2(20).
	p_curr_name	EDI name of curriculum. VARCHAR2(35).
	p_honr_code	EDI honors or delinquency code. VARCHAR2(3).
	p_ovrd_code	EDI override institution code. VARCHAR2(2).
	p_inst_name_ovrd	EDI institution/agency. VARCHAR2(35).
	p_inql_code_ovrd	EDI institution code qualifier. VARCHAR2(2).
	p_inst_code_ovrd	EDI institution code. VARCHAR2(20).
	p_street_line1_ovrd	EDI override institution address 1. VARCHAR2(35).
	p_street_line2_ovrd	EDI override institution address 2. VARCHAR2(35).
	p_city_ovrd	EDI override institution city. VARCHAR2(30).
	p_stat_code_ovrd	EDI override institution state. VARCHAR2(2).
	p_zip_ovrd	EDI override institution ZIP or postal code. VARCHAR2(9).
	p_natn_code_ovrd	EDI override institution nation code. VARCHAR2(3).
	p_domestic_addr_ind	Y if address is domestic. For PESCXML. VARCHAR2(1).
	p_street_line3	Third address line. For PESCXML. VARCHAR2(35).
	p_stateprovince	State or province name for an international address. For PESCXML. VARCHAR2(60).
	p_data_origin	Source system that created or updated the data. VARCHAR2(30).
	p_rowid_out	Database ROWID of the record to be created. VARCHAR2(18) Required.

p_delete

PROCEDURE p_delete(p_dcmt_seqno shrases.shrases_dcmt_seqno%TYPE,
                   p_seqno      shrases.shrases_seqno%TYPE,
                   p_rowid      gb_common.internal_record_id_type DEFAULT NULL);

Deletes a record.

Parameters

	p_dcmt_seqno	EDI document sequence number. NUMBER(8) Required Key.
	p_seqno	EDI academic session sequence number. NUMBER(4) Required Key.
	p_rowid	Database ROWID of the record to be deleted. VARCHAR2(18).

p_lock

PROCEDURE p_lock(p_dcmt_seqno  shrases.shrases_dcmt_seqno%TYPE,
                 p_seqno       shrases.shrases_seqno%TYPE,
                 p_rowid_inout IN OUT 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_dcmt_seqno	EDI document sequence number. NUMBER(8) Required Key.
	p_seqno	EDI academic session sequence number. NUMBER(4) Required Key.
	p_rowid_inout	Database ROWID of the record to be locked. VARCHAR2(18) Required.

p_update

PROCEDURE p_update(p_dcmt_seqno        shrases.shrases_dcmt_seqno%TYPE,
                   p_seqno             shrases.shrases_seqno%TYPE,
                   p_start_date        shrases.shrases_start_date%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_sess_no           shrases.shrases_sess_no%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_school_year       shrases.shrases_school_year%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_sntp_code         shrases.shrases_sntp_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_sess_name         shrases.shrases_sess_name%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_sbql_code         shrases.shrases_sbql_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_begin_date        shrases.shrases_begin_date%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_seql_code         shrases.shrases_seql_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_end_date          shrases.shrases_end_date%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_slvl_code         shrases.shrases_slvl_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_crql_code         shrases.shrases_crql_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_curr_code         shrases.shrases_curr_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_curr_name         shrases.shrases_curr_name%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_honr_code         shrases.shrases_honr_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_ovrd_code         shrases.shrases_ovrd_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_inst_name_ovrd    shrases.shrases_inst_name_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_inql_code_ovrd    shrases.shrases_inql_code_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_inst_code_ovrd    shrases.shrases_inst_code_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_street_line1_ovrd shrases.shrases_street_line1_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_street_line2_ovrd shrases.shrases_street_line2_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_city_ovrd         shrases.shrases_city_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_stat_code_ovrd    shrases.shrases_stat_code_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_zip_ovrd          shrases.shrases_zip_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_natn_code_ovrd    shrases.shrases_natn_code_ovrd%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_domestic_addr_ind shrases.shrases_domestic_addr_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_street_line3      shrases.shrases_street_line3%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_stateprovince     shrases.shrases_stateprovince%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin       shrases.shrases_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid             gb_common.internal_record_id_type DEFAULT NULL);

Updates a record.

Parameters

	p_dcmt_seqno	EDI document sequence number. NUMBER(8) Required Key.
	p_seqno	EDI academic session sequence number. NUMBER(4) Required Key.
	p_start_date	EDI session start month/year. VARCHAR2(6).
	p_sess_no	EDI specific session number. NUMBER(3).
	p_school_year	EDI K-12 school year. VARCHAR2(35).
	p_sntp_code	EDI type of session. VARCHAR2(1).
	p_sess_name	EDI name of session. VARCHAR2(35).
	p_sbql_code	EDI format for date session began. VARCHAR2(3).
	p_begin_date	EDI starting date. VARCHAR2(8).
	p_seql_code	EDI format of ending date. VARCHAR2(3).
	p_end_date	EDI ending date. VARCHAR2(8).
	p_slvl_code	EDI student grade level. VARCHAR2(2).
	p_crql_code	EDI curriculum code qualifier. VARCHAR2(2).
	p_curr_code	EDI curriculum code. VARCHAR2(20).
	p_curr_name	EDI name of curriculum. VARCHAR2(35).
	p_honr_code	EDI honors or delinquency code. VARCHAR2(3).
	p_ovrd_code	EDI override institution code. VARCHAR2(2).
	p_inst_name_ovrd	EDI institution/agency. VARCHAR2(35).
	p_inql_code_ovrd	EDI institution code qualifier. VARCHAR2(2).
	p_inst_code_ovrd	EDI institution code. VARCHAR2(20).
	p_street_line1_ovrd	EDI override institution address 1. VARCHAR2(35).
	p_street_line2_ovrd	EDI override institution address 2. VARCHAR2(35).
	p_city_ovrd	EDI override institution city. VARCHAR2(30).
	p_stat_code_ovrd	EDI override institution state. VARCHAR2(2).
	p_zip_ovrd	EDI override institution ZIP or postal code. VARCHAR2(9).
	p_natn_code_ovrd	EDI override institution nation code. VARCHAR2(3).
	p_domestic_addr_ind	Y if address is domestic. For PESCXML. VARCHAR2(1).
	p_street_line3	Third address line. For PESCXML. VARCHAR2(35).
	p_stateprovince	State or province name for an international address. For PESCXML. VARCHAR2(60).
	p_data_origin	Source system that created or updated the data. VARCHAR2(30).
	p_rowid	Database ROWID of the record to be updated. VARCHAR2(18).