index

Package tb_statement

The Common Business interface for the Statement API (STATEMENT).
 
This API will interact with the LARGE_OBJECT API (gb_large_object) to store a binary file.  Please refer to the documentation of LARGE_OBJECT for details regarding the parameters handling file storage. The Media Id assigned in the large object storage table is captured in the statement header table to provide the link for subsequent access to the file.

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_max_stmt_number   Selects maximum statement number for the entity.
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_purge_bill_run   Deletes all STATEMENT records for the given Bill Run.
p_purge_media   Updates Media ID to NULL while retaining the STATEMENT record.
p_update   Updates a record.

Types
statement_rec   Business Entity record type
statement_ref   Entity cursor variable type
statement_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(9) := 'STATEMENT';

Business Entity Name

M_BASE_TABLE_NAME 

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

Base table name

statement_rec 

TYPE statement_rec IS RECORD (
  r_stmt_number             tbbstmt.tbbstmt_stmt_number%TYPE,
  r_pidm                    tbbstmt.tbbstmt_pidm%TYPE,
  r_bill_date               tbbstmt.tbbstmt_bill_date%TYPE,
  r_bill_run                tbbstmt.tbbstmt_bill_run%TYPE,
  r_user_id                 tbbstmt.tbbstmt_user_id%TYPE,
  r_data_origin             tbbstmt.tbbstmt_data_origin%TYPE,
  r_type                    tbbstmt.tbbstmt_type%TYPE,
  r_sort                    tbbstmt.tbbstmt_sort%TYPE,
  r_term_code               tbbstmt.tbbstmt_term_code%TYPE,
  r_due_date                tbbstmt.tbbstmt_due_date%TYPE,
  r_atyp_code               tbbstmt.tbbstmt_atyp_code%TYPE,
  r_atyp_seqno              tbbstmt.tbbstmt_atyp_seqno%TYPE,
  r_sort_zip                tbbstmt.tbbstmt_sort_zip%TYPE,
  r_sort_id                 tbbstmt.tbbstmt_sort_id%TYPE,
  r_sort_name               tbbstmt.tbbstmt_sort_name%TYPE,
  r_sort_campus             tbbstmt.tbbstmt_sort_campus%TYPE,
  r_email_addr              tbbstmt.tbbstmt_email_addr%TYPE,
  r_balance                 tbbstmt.tbbstmt_balance%TYPE,
  r_past_due                tbbstmt.tbbstmt_past_due%TYPE,
  r_amount_due              tbbstmt.tbbstmt_amount_due%TYPE,
  r_minimum_due             tbbstmt.tbbstmt_minimum_due%TYPE,
  r_media_id                tbbstmt.tbbstmt_media_id%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type);

Business Entity record type

statement_ref 

TYPE statement_ref IS REF CURSOR RETURN statement_rec;

Entity cursor variable type

statement_tab 

TYPE statement_tab IS TABLE OF statement_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_stmt_number tbbstmt.tbbstmt_stmt_number%TYPE,
                  p_rowid       gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks to see if a record exists.

Parameters
p_stmt_number   Unique generated document number for the statement. NUMBER(38) 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 statement_rec, rec_two statement_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 statement_rec Required
rec_two   The second record to compare. Type statement_rec Required

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

f_max_stmt_number 

Function f_max_stmt_number(p_pidm           tbbstmt.tbbstmt_pidm%TYPE,
                           p_file_populated VARCHAR2) RETURN INTEGER

Selects maximum statement number for the entity.

Parameters
p_pidm   Internal Identification Number of the person or non-person account, valid in SPRIDEN. NUMBER(8) Required
p_file_populated   Select maximum only from those records that have a stored file association. VARCHAR2(1) Required
Y   Return maximum statement number where a file has been stored.
N   Return maximum statement number for the entity.


Returns
Maximum statement number for the entity.

f_query_all 

Function f_query_all(p_pidm     tbbstmt.tbbstmt_pidm%TYPE DEFAULT NULL,
                     p_bill_run tbbstmt.tbbstmt_bill_run%TYPE DEFAULT NULL)
  RETURN statement_ref

Selects all records for the entity.

Parameters
p_pidm   Internal Identification Number of the person or non-person account, valid in SPRIDEN. NUMBER(8)
p_bill_run   Run Number for bill as submitted via job submission. NUMBER

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

f_query_one 

Function f_query_one(p_stmt_number tbbstmt.tbbstmt_stmt_number%TYPE)
  RETURN statement_ref

Selects one record using key.

Parameters
p_stmt_number   Unique generated document number for the statement. NUMBER(38) 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 statement_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_stmt_number tbbstmt.tbbstmt_stmt_number%TYPE,
                          p_rowid       gb_common.internal_record_id_type DEFAULT NULL)
  RETURN statement_ref

Selects one record and locks it.

Parameters
p_stmt_number   Unique generated document number for the statement. NUMBER(38) 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            tbbstmt.tbbstmt_pidm%TYPE,
                   p_bill_date       tbbstmt.tbbstmt_bill_date%TYPE,
                   p_bill_run        tbbstmt.tbbstmt_bill_run%TYPE,
                   p_user_id         tbbstmt.tbbstmt_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_data_origin     tbbstmt.tbbstmt_data_origin%TYPE DEFAULT NULL,
                   p_type            tbbstmt.tbbstmt_type%TYPE DEFAULT NULL,
                   p_sort            tbbstmt.tbbstmt_sort%TYPE DEFAULT NULL,
                   p_term_code       tbbstmt.tbbstmt_term_code%TYPE DEFAULT NULL,
                   p_due_date        tbbstmt.tbbstmt_due_date%TYPE DEFAULT NULL,
                   p_atyp_code       tbbstmt.tbbstmt_atyp_code%TYPE DEFAULT NULL,
                   p_atyp_seqno      tbbstmt.tbbstmt_atyp_seqno%TYPE DEFAULT NULL,
                   p_sort_zip        tbbstmt.tbbstmt_sort_zip%TYPE DEFAULT NULL,
                   p_sort_id         tbbstmt.tbbstmt_sort_id%TYPE DEFAULT NULL,
                   p_sort_name       tbbstmt.tbbstmt_sort_name%TYPE DEFAULT NULL,
                   p_sort_campus     tbbstmt.tbbstmt_sort_campus%TYPE DEFAULT NULL,
                   p_email_addr      tbbstmt.tbbstmt_email_addr%TYPE DEFAULT NULL,
                   p_balance         tbbstmt.tbbstmt_balance%TYPE DEFAULT NULL,
                   p_past_due        tbbstmt.tbbstmt_past_due%TYPE DEFAULT NULL,
                   p_amount_due      tbbstmt.tbbstmt_amount_due%TYPE DEFAULT NULL,
                   p_minimum_due     tbbstmt.tbbstmt_minimum_due%TYPE DEFAULT NULL,
                   p_blot_code       gorblob.gorblob_blot_code%TYPE DEFAULT NULL,
                   p_blob            gorblob.gorblob_blob%TYPE DEFAULT NULL,
                   p_bfile           gorblob.gorblob_bfile%type DEFAULT NULL,
                   p_directory       VARCHAR2 DEFAULT NULL,
                   p_filename        VARCHAR2 DEFAULT NULL,
                   p_loadfromfile    VARCHAR2 DEFAULT 'Y',
                   p_stmt_number_out OUT tbbstmt.tbbstmt_stmt_number%TYPE,
                   p_rowid_out       OUT gb_common.internal_record_id_type)

Creates a record.
Note that sort, address, date, and term information are used during the creation of the Statements, and are not validated when calling this API.
Please refer to the documentation of the LARGE_OBJECT API (gb_large_object) for details regarding the parameters handling file storage.

Parameters
p_pidm   Internal Identification Number of the person or non-person account, valid in SPRIDEN. NUMBER(8) Required
p_bill_date   Date of the bill as specified by the parameter. DATE Required
p_bill_run   Run Number for bill as submitted via job submission. NUMBER Required
p_user_id   Oracle ID of the user who created or last updated the data. VARCHAR2(30) Required
p_data_origin   Source system that last created or updated the data. VARCHAR2(30)
p_type   Type of statement requested, ST = student statement, SB = schedule bill. VARCHAR2(2)
p_sort   Sort variable for printing, 00 - no address, 01- valid address. VARCHAR2(2)
p_term_code   Term for which the statement is produced, when processing schedule bill. Valid in STVTERM. VARCHAR2(6)
p_due_date   Due Date by which payment is requested. DATE
p_atyp_code   In combination with ATYP_SEQNO, the address where the statement will be sent. VARCHAR2(2)
p_atyp_seqno   In combination with ATYP_CODE, the address where the statement will be sent. Combination valid and active in SPRADDR. NUMBER(2)
p_sort_zip   Zip Code to which statement will be sent for sort by zip code. VARCHAR2(10)
p_sort_id   Identification Number of the entity receiving the bill for sort by Id. VARCHAR2(9)
p_sort_name   Name of the entity receiving the bill for sort by name. VARCHAR2(75)
p_sort_campus   The campus location associated with the student for the term, populated if sorting schedule bill. VARCHAR2(3)
p_email_addr   EMail address for the recipient of the bill. VARCHAR2(90)
p_balance   Balance of the account at the time of billing. NUMBER(14,2)
p_past_due   Amount Past Due at the time of billing. NUMBER(14,2)
p_amount_due   Amount currently due in response to billing. NUMBER(14,2)
p_minimum_due   Minimum payment due in response to billing if dynamic automated installment plan calculated. NUMBER(14,2)
p_blot_code   A string that describes the contents of the binary large object, valid in GTVBLOT. VARCHAR2(30)
p_blob   The binary large object to be stored in the database. BLOB
p_bfile   The locator pointing to the binary large object as a file system file. BFILE
p_directory   Logical directory name where p_filename is located. VARCHAR2(30)
p_filename   File system file name. VARCHAR2(2000).
p_loadfromfile   Load from external file. VARCHAR2(1)
N   do not load or validate an external file
V   validates that the external file exists
Y   indicates the external file should be loaded into a BLOB (validation is implied) (default)

p_stmt_number_out   Unique document number for the statement generated and returned. NUMBER(38) Required
p_rowid_out   Database ROWID of the record to be created. VARCHAR2(18) Required

p_delete 

Procedure p_delete(p_stmt_number tbbstmt.tbbstmt_stmt_number%TYPE,
                   p_rowid       gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record.

Parameters
p_stmt_number   Unique generated document number for the statement. NUMBER(38) Required Key
p_rowid   Database ROWID of the record to be deleted. VARCHAR2(18)

p_lock 

Procedure p_lock(p_stmt_number tbbstmt.tbbstmt_stmt_number%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_stmt_number   Unique generated document number for the statement. NUMBER(38) Required Key
p_rowid_inout   Database ROWID of the record to be locked. VARCHAR2(18) Required

p_purge_bill_run 

Procedure p_purge_bill_run(p_bill_run tbbstmt.tbbstmt_bill_run%TYPE)

Deletes all STATEMENT records for the given Bill Run.
This procedure deletes records from the statement header table and calls procedure p_purge of LARGE_OBJECT to remove the stored file.  Please refer to the documentation of the LARGE_OBJECT API (gb_large_object) for details regarding the purge based on file storage options.
 
Note that the procedure will commit the deletes as it processes.

Parameters
p_bill_run   Run Number for bill as submitted via job submission. NUMBER Required

p_purge_media 

Procedure p_purge_media(p_stmt_number tbbstmt.tbbstmt_stmt_number%TYPE,
                        p_user_id     tbbstmt.tbbstmt_user_id%TYPE DEFAULT gb_common.f_sct_user,
                        p_data_origin tbbstmt.tbbstmt_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                        p_rowid       gb_common.internal_record_id_type DEFAULT NULL)

Updates Media ID to NULL while retaining the STATEMENT record.
This procedure updates the statement header table to remove the link to the large object storage table, and calls procedure p_purge of LARGE_OBJECT to remove the stored file.  Please refer to the documentation of the LARGE_OBJECT API (gb_large_object) for details regarding the purge based on file storage options.

Parameters
p_stmt_number   Unique generated document number for the statement. NUMBER(38) Required Key
p_user_id   Oracle ID of the user who created or last updated the data. VARCHAR2(30) Required
p_data_origin   Source system that last created or updated the data. VARCHAR2(30)
p_rowid   Database ROWID of the record to be deleted. VARCHAR2(18)

p_update 

Procedure p_update(p_stmt_number  tbbstmt.tbbstmt_stmt_number%TYPE,
                   p_user_id      tbbstmt.tbbstmt_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_data_origin  tbbstmt.tbbstmt_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_email_addr   tbbstmt.tbbstmt_email_addr%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_balance      tbbstmt.tbbstmt_balance%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_past_due     tbbstmt.tbbstmt_past_due%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_amount_due   tbbstmt.tbbstmt_amount_due%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_minimum_due  tbbstmt.tbbstmt_minimum_due%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_blot_code    gorblob.gorblob_blot_code%TYPE DEFAULT NULL,
                   p_blob         gorblob.gorblob_blob%TYPE DEFAULT NULL,
                   p_bfile        gorblob.gorblob_bfile%type DEFAULT NULL,
                   p_directory    VARCHAR2 DEFAULT NULL,
                   p_filename     VARCHAR2 DEFAULT NULL,
                   p_loadfromfile VARCHAR2 DEFAULT 'Y',
                   p_rowid        gb_common.internal_record_id_type DEFAULT NULL)

Updates a record.
Please refer to the documentation of the LARGE_OBJECT API (gb_large_object) for details regarding the parameters handling file storage.

Parameters
p_stmt_number   Unique generated document number for the statement. NUMBER(38) Required Key
p_user_id   Oracle ID of the user who created or last updated the data. VARCHAR2(30) Required
p_data_origin   Source system that last created or updated the data. VARCHAR2(30)
p_email_addr   EMail address for the recipient of the bill. VARCHAR2(90)
p_balance   Balance of the account at the time of billing. NUMBER(14,2)
p_past_due   Amount Past Due at the time of billing. NUMBER(14,2)
p_amount_due   Amount currently due in response to billing. NUMBER(14,2)
p_minimum_due   Minimum payment due in response to billing if dynamic automated installment plan calculated. NUMBER(14,2)
p_blot_code   A string that describes the contents of the binary large object, valid in GTVBLOT. VARCHAR2(30)
p_blob   The binary large object to be stored in the database. BLOB
p_bfile   The locator pointing to the binary large object as a file system file. BFILE
p_directory   Logical directory name where p_filename is located. VARCHAR2(30)
p_filename   File system file name. VARCHAR2(2000)
p_loadfromfile   Load from external file. VARCHAR2(1)
N   do not load or validate an external file
V   validates that the external file exists
Y   indicates the external file should be loaded into a BLOB (validation is implied) (default)

p_rowid   Database ROWID of the record to be updated. VARCHAR2(18)