Package gb_roomassignment

This package provides the Common Business interface for the Room Assignment API (gb_roomassignment).
This API provides the capability to create and maintain information with regard to an applicant's dorm room assignment. Dorm room assignments can be made for students and non-students. Room assignments are made based on term, and the internal personal identification number (PIDM) must have an active room application that includes the room assignment term. Room assignments cannot extend past the normal room capacity or the maximum room capacity, accordingly.

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.
p_calc_days_months_terms	Calculates the days, months and terms of the room assignment.
f_get_rate	Gets the rate code that applies by default, given the room, building and term.

Types

roomassignment_rec	Entity record type
roomassignment_ref	Entity cursor variable type
roomassignment_tab	Entity table type

Constants

m_entity_name

Entity name of the API.

m_entity_name

m_entity_name   CONSTANT VARCHAR2 (14) := 'ROOMASSIGNMENT';

Entity name of the API.

roomassignment_rec

TYPE roomassignment_rec IS RECORD(

   r_pidm                        slrrasg.slrrasg_pidm%TYPE,
   r_bldg_code                   slrrasg.slrrasg_bldg_code%TYPE,
   r_room_number                 slrrasg.slrrasg_room_number%TYPE,
   r_term_code                   slrrasg.slrrasg_term_code%TYPE,
   r_rrcd_code                   slrrasg.slrrasg_rrcd_code%TYPE,
   r_begin_date                  slrrasg.slrrasg_begin_date%TYPE,
   r_end_date                    slrrasg.slrrasg_end_date%TYPE,
   r_total_days                  slrrasg.slrrasg_total_days%TYPE,
   r_total_months                slrrasg.slrrasg_total_months%TYPE,
   r_total_terms                 slrrasg.slrrasg_total_terms%TYPE,
   r_ascd_code                   slrrasg.slrrasg_ascd_code%TYPE,
   r_ascd_date                   slrrasg.slrrasg_ascd_date%TYPE,
   r_onl_or_bat                  slrrasg.slrrasg_onl_or_bat%TYPE,
   r_ar_ind                      slrrasg.slrrasg_ar_ind%TYPE,
   r_overload_ind                slrrasg.slrrasg_overload_ind%TYPE,
   r_roll_ind                    slrrasg.slrrasg_roll_ind%TYPE,
   r_override_error              slrrasg.slrrasg_override_error%TYPE,
   r_assess_needed               slrrasg.slrrasg_assess_needed%TYPE,
   r_data_origin                 slrrasg.slrrasg_data_origin%TYPE,
   r_user_id                     slrrasg.slrrasg_user_id%TYPE,
   r_internal_record_id          gb_common.internal_record_id_type);

Entity record type

roomassignment_ref

TYPE roomassignment_ref IS REF CURSOR
   RETURN roomassignment_rec;

Entity cursor variable type

roomassignment_tab

TYPE roomassignment_tab IS TABLE OF roomassignment_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_rowid VARCHAR2) RETURN VARCHAR2

Checks to see if a record exists.

Parameters

p_rowid

Database ROWID of record to be selected. VARCHAR2(18) Required

Returns

Y if found, otherwise N.

f_isequal

FUNCTION f_isequal(rec_one roomassignment_rec,
                   rec_two roomassignment_rec) RETURN VARCHAR2

Compares two records for equality.

Parameters

	rec_one	First record to compare. Defined as type roomassignment_rec.
	rec_two	Second record to compare. Defined as type roomassignment_rec.

Returns

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

f_query_all

FUNCTION f_query_all(p_pidm           slrrasg.slrrasg_pidm%TYPE DEFAULT NULL,
                     p_bldg_code      slrrasg.slrrasg_bldg_code%TYPE DEFAULT NULL,
                     p_room_number    slrrasg.slrrasg_room_number%TYPE DEFAULT NULL,
                     p_term_code      slrrasg.slrrasg_term_code%TYPE DEFAULT NULL,
                     p_rrcd_code      slrrasg.slrrasg_rrcd_code%TYPE DEFAULT NULL,
                     p_begin_date     slrrasg.slrrasg_begin_date%TYPE DEFAULT NULL,
                     p_end_date       slrrasg.slrrasg_end_date%TYPE DEFAULT NULL,
                     p_total_days     slrrasg.slrrasg_total_days%TYPE DEFAULT NULL,
                     p_total_months   slrrasg.slrrasg_total_months%TYPE DEFAULT NULL,
                     p_total_terms    slrrasg.slrrasg_total_terms%TYPE DEFAULT NULL,
                     p_ascd_code      slrrasg.slrrasg_ascd_code%TYPE DEFAULT NULL,
                     p_ascd_date      slrrasg.slrrasg_ascd_date%TYPE DEFAULT NULL,
                     p_onl_or_bat     slrrasg.slrrasg_onl_or_bat%TYPE DEFAULT NULL,
                     p_ar_ind         slrrasg.slrrasg_ar_ind%TYPE DEFAULT NULL,
                     p_overload_ind   slrrasg.slrrasg_overload_ind%TYPE DEFAULT NULL,
                     p_roll_ind       slrrasg.slrrasg_roll_ind%TYPE DEFAULT NULL,
                     p_override_error slrrasg.slrrasg_override_error%TYPE DEFAULT NULL,
                     p_assess_needed  slrrasg.slrrasg_assess_needed%TYPE DEFAULT NULL,
                     p_data_origin    slrrasg.slrrasg_data_origin%TYPE DEFAULT NULL,
                     p_user_id        slrrasg.slrrasg_user_id%TYPE DEFAULT NULL)
  RETURN roomassignment_ref

Selects all records for the entity.

Parameters

	p_pidm	Internal identifier associated with the room assignments. NUMBER(8)
	p_bldg_code	Building code of the room assignment. VARCHAR2(6)
	p_room_number	Room number of the room assignment. VARCHAR2(10)
	p_term_code	Term associated with the room assignment. VARCHAR2(6)
	p_rrcd_code	Room rate code associated with the assignment. VARCHAR2(4)
	p_begin_date	First day of the room assignment. DATE
	p_end_date	Last day of the room assignment. DATE
	p_total_days	Total number of days of the room assignment. NUMBER(14)
	p_total_months	Total number of months of the room assignment. NUMBER(12)
	p_total_terms	Total number of terms of the room assignment. NUMBER(12)
	p_ascd_code	Status code of the room assignment. VARCHAR2(2)
	p_ascd_date	Date the room plan status code was last updated. DATE
	p_onl_or_bat	Specifies whether the room assignment was created online or in batch using the batch scheduler. VARCHAR2(1)
	p_ar_ind	Specifies whether the room assignment charges have been processed. VARCHAR2(1)
	p_overload_ind	Specifies whether an overload condition existed to allow the room assignment. VARCHAR2(1)
	p_roll_ind	Controls whether the room assignment can be rolled using the roll forward process. VARCHAR2(1)
	p_override_error	Specifies whether an error override condition existed to allow the room assignment. VARCHAR2(1)
	p_assess_needed	Specifies whether fee assessment is needed for the room assignment. VARCHAR2(1)
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_user_id	Oracle ID of the user who inserted or last updated the data. VARCHAR2(30)

Returns

A cursor variable that will fetch the set of records.

f_query_one

FUNCTION f_query_one(p_pidm           slrrasg.slrrasg_pidm%TYPE,
                     p_bldg_code      slrrasg.slrrasg_bldg_code%TYPE,
                     p_room_number    slrrasg.slrrasg_room_number%TYPE,
                     p_term_code      slrrasg.slrrasg_term_code%TYPE,
                     p_rrcd_code      slrrasg.slrrasg_rrcd_code%TYPE,
                     p_begin_date     slrrasg.slrrasg_begin_date%TYPE,
                     p_end_date       slrrasg.slrrasg_end_date%TYPE,
                     p_total_days     slrrasg.slrrasg_total_days%TYPE,
                     p_total_months   slrrasg.slrrasg_total_months%TYPE,
                     p_total_terms    slrrasg.slrrasg_total_terms%TYPE,
                     p_ascd_code      slrrasg.slrrasg_ascd_code%TYPE,
                     p_ascd_date      slrrasg.slrrasg_ascd_date%TYPE,
                     p_onl_or_bat     slrrasg.slrrasg_onl_or_bat%TYPE,
                     p_ar_ind         slrrasg.slrrasg_ar_ind%TYPE,
                     p_overload_ind   slrrasg.slrrasg_overload_ind%TYPE,
                     p_roll_ind       slrrasg.slrrasg_roll_ind%TYPE,
                     p_override_error slrrasg.slrrasg_override_error%TYPE,
                     p_assess_needed  slrrasg.slrrasg_assess_needed%TYPE,
                     p_data_origin    slrrasg.slrrasg_data_origin%TYPE,
                     p_user_id        slrrasg.slrrasg_user_id%TYPE)
  RETURN roomassignment_ref

Selects one record using the key.

Parameters

	p_pidm	Internal identifier associated with the room assignments. NUMBER(8) Required
	p_bldg_code	Building code of the room assignment. VARCHAR2(6) Required
	p_room_number	Room number of the room assignment. VARCHAR2(10) Required
	p_term_code	Term associated with the room assignment. VARCHAR2(6) Required
	p_rrcd_code	Room rate code associated with the assignment. VARCHAR2(4) Required
	p_begin_date	First day of the room assignment. DATE Required
	p_end_date	Last day of the room assignment. DATE Required
	p_total_days	Total number of days of the room assignment. NUMBER(14) Required
	p_total_months	Total number of months of the room assignment. NUMBER(12) Required
	p_total_terms	Total number of terms of the room assignment. NUMBER(12) Required
	p_ascd_code	Status code of the room assignment. VARCHAR2(2) Required
	p_ascd_date	Date the room plan status code was last updated. DATE Required
	p_onl_or_bat	Specifies whether the room assignment was created online or in batch using the batch scheduler. VARCHAR2(1) Required
	p_ar_ind	Specifies whether the room assignment charges have been processed. VARCHAR2(1) Required
	p_overload_ind	Specifies whether an overload condition existed to allow the room assignment. VARCHAR2(1) Required
	p_roll_ind	Controls whether the room assignment can be rolled using the roll forward process. VARCHAR2(1) Required
	p_override_error	Specifies whether an error override condition existed to allow the room assignment. VARCHAR2(1) Required
	p_assess_needed	Specifies whether fee assessment is needed for the room assignment. VARCHAR2(1) Required
	p_data_origin	Source system that generated the data. VARCHAR2(30) Required
	p_user_id	Oracle user ID who inserted or last updated the data. VARCHAR2(30) Required

Returns

A cursor variable that will fetch exactly one record.

f_query_by_rowid

FUNCTION f_query_by_rowid(p_rowid VARCHAR2) RETURN roomassignment_ref

Selects one record using the ROWID.

Parameters

p_rowid

Database ROWID of the record to selected. VARCHAR2(18)

Returns

A cursor variable that will fetch exactly one record.

f_query_one_lock

FUNCTION f_query_one_lock(p_rowid VARCHAR2) RETURN roomassignment_ref

Selects one record and locks it.

Parameters

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           slrrasg.slrrasg_pidm%TYPE,
                   p_bldg_code      slrrasg.slrrasg_bldg_code%TYPE,
                   p_room_number    slrrasg.slrrasg_room_number%TYPE,
                   p_term_code      slrrasg.slrrasg_term_code%TYPE,
                   p_rrcd_code      slrrasg.slrrasg_rrcd_code%TYPE DEFAULT NULL,
                   p_begin_date     slrrasg.slrrasg_begin_date%TYPE,
                   p_end_date       slrrasg.slrrasg_end_date%TYPE,
                   p_total_days     slrrasg.slrrasg_total_days%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_total_months   slrrasg.slrrasg_total_months%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_total_terms    slrrasg.slrrasg_total_terms%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_ascd_code      slrrasg.slrrasg_ascd_code%TYPE,
                   p_ascd_date      slrrasg.slrrasg_ascd_date%TYPE,
                   p_onl_or_bat     slrrasg.slrrasg_onl_or_bat%TYPE,
                   p_ar_ind         slrrasg.slrrasg_ar_ind%TYPE,
                   p_overload_ind   slrrasg.slrrasg_overload_ind%TYPE DEFAULT NULL,
                   p_roll_ind       slrrasg.slrrasg_roll_ind%TYPE DEFAULT NULL,
                   p_override_error slrrasg.slrrasg_override_error%TYPE DEFAULT NULL,
                   p_assess_needed  slrrasg.slrrasg_assess_needed%TYPE,
                   p_data_origin    slrrasg.slrrasg_data_origin%TYPE DEFAULT gb_common.data_origin,
                   p_user_id        slrrasg.slrrasg_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_rowid_out      OUT VARCHAR2)

Creates a record.

Parameters

	p_pidm	Internal identifier associated with the room assignments. NUMBER(8) Required
	p_bldg_code	Building code of the room assignment. VARCHAR2(6) Required
	p_room_number	Room number of the room assignment. VARCHAR2(10) Required
	p_term_code	Term associated with the room assignment. VARCHAR2(6) Required
	p_rrcd_code	Room rate code associated with the assignment. VARCHAR2(4) Required
	p_begin_date	First day of the room assignment. DATE Required
	p_end_date	Last day of the room assignment. DATE Required
	p_total_days	Total number of days of the room assignment. NUMBER(14) Required
	p_total_months	Total number of months of the room assignment. NUMBER(12) Required
	p_total_terms	Total number of terms of the room assignment. NUMBER(12) Required
	p_ascd_code	Status code of the room assignment. VARCHAR2(2) Required
	p_ascd_date	Date the room plan status code was last updated. DATE Required
	p_onl_or_bat	Specifies whether the room assignment was created online or in batch using the batch scheduler. VARCHAR2(1) Required
	p_ar_ind	Specifies whether the room assignment charges have been processed. VARCHAR2(1) Required
	p_overload_ind	Specifies whether an overload condition existed to allow the room assignment. VARCHAR2(1)
	p_roll_ind	Controls whether the room assignment can be rolled using the roll forward process. VARCHAR2(1)
	p_override_error	Specifies whether an error override condition existed to allow the room assignment. VARCHAR2(1)
	p_assess_needed	Specifies whether fee assessment is needed for the room assignment. VARCHAR2(1) Required
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_user_id	Oracle user ID who inserted or last 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_pidm          slrrasg.slrrasg_pidm%TYPE DEFAULT NULL,
                   p_term_code     slrrasg.slrrasg_term_code%TYPE DEFAULT NULL,
                   p_rowid         VARCHAR2,
                   p_purge_process VARCHAR2 DEFAULT 'N')

Deletes a record.

Parameters

	p_pidm	Internal identifier associated with the room assignment. NUMBER(8)
	p_term_code	Term associated with the room assignment. VARCHAR2(6)
	p_rowid	Database ROWID of record to be deleted. VARCHAR2(18) Required
	p_purge_process	Specifies when deletes are allowed. Y for purge, N for not a purge process. Defaults N. VARCHAR2(1)

p_lock

PROCEDURE p_lock(p_rowid_inout IN OUT VARCHAR2)

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_rowid_inout

Database ROWID of the record to be locked.

p_update

PROCEDURE p_update(p_pidm           slrrasg.slrrasg_pidm%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_bldg_code      slrrasg.slrrasg_bldg_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_room_number    slrrasg.slrrasg_room_number%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_term_code      slrrasg.slrrasg_term_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rrcd_code      slrrasg.slrrasg_rrcd_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_begin_date     slrrasg.slrrasg_begin_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_end_date       slrrasg.slrrasg_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_total_days     slrrasg.slrrasg_total_days%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_total_months   slrrasg.slrrasg_total_months%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_total_terms    slrrasg.slrrasg_total_terms%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_ascd_code      slrrasg.slrrasg_ascd_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_ascd_date      slrrasg.slrrasg_ascd_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_onl_or_bat     slrrasg.slrrasg_onl_or_bat%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_ar_ind         slrrasg.slrrasg_ar_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_overload_ind   slrrasg.slrrasg_overload_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_roll_ind       slrrasg.slrrasg_roll_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_override_error slrrasg.slrrasg_override_error%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_assess_needed  slrrasg.slrrasg_assess_needed%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin    slrrasg.slrrasg_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id        slrrasg.slrrasg_user_id%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid          VARCHAR2)

Updates a record.

Parameters

	p_pidm	Internal identifier associated with the room assignments. NUMBER(8)
	p_bldg_code	Building code of the room assignment. VARCHAR2(6)
	p_room_number	Room number of the room assignment. VARCHAR2(10)
	p_term_code	Term associated with the room assignment. VARCHAR2(6)
	p_rrcd_code	Room rate code associated with the assignment. VARCHAR2(4)
	p_begin_date	First day of the room assignment. DATE
	p_end_date	Last day of the room assignment. DATE
	p_total_days	Total number of days of the room assignment. NUMBER(14)
	p_total_months	Total number of months of the room assignment. NUMBER(12)
	p_total_terms	Total number of terms of the room assignment. NUMBER(12)
	p_ascd_code	Status code of the room assignment. VARCHAR2(2)
	p_ascd_date	Date the room plan status code was last updated. DATE
	p_onl_or_bat	Specifies whether the room assignment was created online or in batch using the batch scheduler. VARCHAR2(1)
	p_ar_ind	Specifies whether the room assignment charges have been processed. VARCHAR2(1)
	p_overload_ind	Specifies whether an overload condition existed to allow the room assignment. VARCHAR2(1)
	p_roll_ind	Controls whether the room assignment can be rolled using the roll forward process. VARCHAR2(1)
	p_override_error	Specifies whether an error override condition existed to allow the room assignment. VARCHAR2(1)
	p_assess_needed	Specifies whether fee assessment is needed for the room assignment. VARCHAR2(1)
	p_data_origin	Source system that generated the data. VARCHAR2(30)
	p_user_id	Oracle user ID who inserted or last updated the data. VARCHAR2(30)
	p_rowid	Database ROWID of the record to be updated. VARCHAR2(18) Required

p_calc_days_months_terms

PROCEDURE p_calc_days_months_terms(p_term_code        slrrasg.slrrasg_term_code%TYPE,
                                   p_rrcd_code        slrrasg.slrrasg_rrcd_code%TYPE DEFAULT NULL,
                                   p_total_days       slrrasg.slrrasg_total_days%TYPE DEFAULT dml_common.f_unspecified_number,
                                   p_total_months     slrrasg.slrrasg_total_months%TYPE DEFAULT dml_common.f_unspecified_number,
                                   p_total_terms      slrrasg.slrrasg_total_terms%TYPE DEFAULT dml_common.f_unspecified_number,
                                   p_total_days_out   OUT slrrasg.slrrasg_total_days%TYPE,
                                   p_total_months_out OUT slrrasg.slrrasg_total_months%TYPE,
                                   p_total_terms_out  OUT slrrasg.slrrasg_total_terms%TYPE,
                                   p_err_msg_out      OUT gb_common_strings.err_type)

Calculates the days, months and terms of the room assignment.

Parameters

	p_term_code	Term code of the room assignment. VARCHAR2(6) Required
	p_rrcd_code	Room assignment code. VARCHAR2(4)
	p_total_days_out	Calculated value of the total days. NUMBER(14)
	p_total_months_out	Calculated value of the total months. NUMBER(12)
	p_total_terms_out	Calculated value of the total terms. NUMBER(12)
	p_err_msg_out	Error message VARCHAR2(2000)

f_get_rate

FUNCTION f_get_rate(p_room_number slrrasg.slrrasg_room_number%TYPE,
                    p_bldg_code   slrrasg.slrrasg_bldg_code%TYPE,
                    p_term_code   slrrasg.slrrasg_term_code%TYPE)
  RETURN slrrasg.slrrasg_rrcd_code%TYPE

Gets the rate code that applies by default, given the room, building and term.

Parameters

	p_room_number	Room number of the room assignment. VARCHAR2(10) Required
	p_bldg_code	Building code of the room assignment. VARCHAR2(6) Required
	p_term_code	Term code for the room assignment. VARCHAR2(6) Required

Returns

Returns the room rate.