M_ENTITY_NAME

M_ENTITY_NAME    CONSTANT VARCHAR2(4) := 'MEMO';

Business Entity Name

memo_rec

TYPE memo_rec IS RECORD (
  r_pidm                    tbrmemo.tbrmemo_pidm%TYPE,
  r_tran_number             tbrmemo.tbrmemo_tran_number%TYPE,
  r_term_code               tbrmemo.tbrmemo_term_code%TYPE,
  r_detail_code             tbrmemo.tbrmemo_detail_code%TYPE,
  r_amount                  tbrmemo.tbrmemo_amount%TYPE,
  r_user                    tbrmemo.tbrmemo_user%TYPE,
  r_entry_date              tbrmemo.tbrmemo_entry_date%TYPE,
  r_billing_ind             tbrmemo.tbrmemo_billing_ind%TYPE,
  r_desc                    tbrmemo.tbrmemo_desc%TYPE,
  r_release_date            tbrmemo.tbrmemo_release_date%TYPE,
  r_expiration_date         tbrmemo.tbrmemo_expiration_date%TYPE,
  r_effective_date          tbrmemo.tbrmemo_effective_date%TYPE,
  r_srce_code               tbrmemo.tbrmemo_srce_code%TYPE,
  r_crossref_pidm           tbrmemo.tbrmemo_crossref_pidm%TYPE,
  r_crossref_number         tbrmemo.tbrmemo_crossref_number%TYPE,
  r_crossref_detail_code    tbrmemo.tbrmemo_crossref_detail_code%TYPE,
  r_crossref_srce_code      tbrmemo.tbrmemo_crossref_srce_code%TYPE,
  r_atyp_code               tbrmemo.tbrmemo_atyp_code%TYPE,
  r_atyp_seqno              tbrmemo.tbrmemo_atyp_seqno%TYPE,
  r_data_origin             tbrmemo.tbrmemo_data_origin%TYPE,
  r_create_user             tbrmemo.tbrmemo_create_user%TYPE,
  r_crossref_dcat_code      tbrmemo.tbrmemo_crossref_dcat_code%TYPE,
  r_aidy_code               tbrmemo.tbrmemo_aidy_code%TYPE,
  r_period                  tbrmemo.tbrmemo_period%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type);

Business Entity record type

memo_ref

TYPE memo_ref IS REF CURSOR RETURN memo_rec;

Entity cursor variable type

memo_tab

TYPE memo_tab IS TABLE OF memo_rec INDEX BY BINARY_INTEGER;

Entity table type

memo_key_rec

TYPE memo_key_rec IS RECORD (
   r_pidm                    tbrmemo.tbrmemo_pidm%TYPE,
   r_tran_number             tbrmemo.tbrmemo_tran_number%TYPE,
   r_internal_record_id      gb_common.internal_record_id_type );

Key record type

memo_key_ref

TYPE memo_key_ref IS REF CURSOR RETURN memo_key_rec;

Key cursor variable type

f_api_version

Function f_api_version RETURN PLS_INTEGER

Returns the API version number.

f_exists

Function f_exists(p_pidm        tbrmemo.tbrmemo_pidm%TYPE,
                  p_tran_number tbrmemo.tbrmemo_tran_number%TYPE,
                  p_rowid       gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks to see if a record exists.

f_isequal

Function f_isequal(rec_one memo_rec, rec_two memo_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.

f_max_tran_number

Function f_max_tran_number(p_pidm tbrmemo.tbrmemo_pidm%TYPE)
  RETURN tbrmemo.tbrmemo_tran_number%TYPE

Return maximum memo transaction number.

f_query_all

Function f_query_all(p_pidm        tbrmemo.tbrmemo_pidm%TYPE,
                     p_term_code   tbrmemo.tbrmemo_term_code%TYPE DEFAULT NULL,
                     p_detail_code tbrmemo.tbrmemo_detail_code%TYPE DEFAULT NULL,
                     p_billing_ind tbrmemo.tbrmemo_billing_ind%TYPE DEFAULT NULL,
                     p_srce_code   tbrmemo.tbrmemo_srce_code%TYPE DEFAULT NULL)
  RETURN memo_ref

Selects all records for the entity.
Note that parameters other than PIDM default to NULL and are ignored if not populated.
AND is used when multiple criteria are given.
Wildcard (%) may be used with term and detail code.

f_query_one

Function f_query_one(p_pidm        tbrmemo.tbrmemo_pidm%TYPE,
                     p_tran_number tbrmemo.tbrmemo_tran_number%TYPE)
  RETURN memo_ref

Selects one record using key.

f_query_by_rowid

Function f_query_by_rowid(p_rowid gb_common.internal_record_id_type)
  RETURN memo_ref

Selects one record using ROWID.

f_query_one_lock

Function f_query_one_lock(p_pidm        tbrmemo.tbrmemo_pidm%TYPE,
                          p_tran_number tbrmemo.tbrmemo_tran_number%TYPE,
                          p_rowid       gb_common.internal_record_id_type DEFAULT NULL)
  RETURN memo_ref

Selects one record and locks it.

f_sum_net_amount

Function f_sum_net_amount(p_pidm        tbrmemo.tbrmemo_pidm%TYPE,
                          p_term_code   tbrmemo.tbrmemo_term_code%TYPE DEFAULT NULL,
                          p_detail_code tbrmemo.tbrmemo_detail_code%TYPE DEFAULT NULL,
                          p_dcat_code   tbbdetc.tbbdetc_dcat_code%TYPE DEFAULT NULL,
                          p_srce_code   tbrmemo.tbrmemo_srce_code%TYPE DEFAULT NULL,
                          p_expire_date tbrmemo.tbrmemo_expiration_date%TYPE DEFAULT NULL)
  RETURN NUMBER

Net the amount (charges less payments) of memos meeting the parameter criteria.
Note that parameters other than PIDM default to NULL and are ignored if not populated.
AND is used when multiple criteria are given.
Wildcard (%) may be used with Term and Detail Code.
If Expire Date is populated, records with Expiration Date NULL or greater than the Expire Date are included in the sum.
 
This function will return NULL when no records match the parameters provided.

p_create

Procedure p_create(p_pidm                 tbrmemo.tbrmemo_pidm%TYPE,
                   p_term_code            tbrmemo.tbrmemo_term_code%TYPE,
                   p_detail_code          tbrmemo.tbrmemo_detail_code%TYPE,
                   p_amount               tbrmemo.tbrmemo_amount%TYPE,
                   p_user                 tbrmemo.tbrmemo_user%TYPE,
                   p_entry_date           tbrmemo.tbrmemo_entry_date%TYPE DEFAULT SYSDATE,
                   p_billing_ind          tbrmemo.tbrmemo_billing_ind%TYPE,
                   p_desc                 tbrmemo.tbrmemo_desc%TYPE DEFAULT NULL,
                   p_release_date         tbrmemo.tbrmemo_release_date%TYPE DEFAULT NULL,
                   p_expiration_date      tbrmemo.tbrmemo_expiration_date%TYPE DEFAULT NULL,
                   p_effective_date       tbrmemo.tbrmemo_effective_date%TYPE DEFAULT trunc(SYSDATE),
                   p_srce_code            tbrmemo.tbrmemo_srce_code%TYPE DEFAULT 'T',
                   p_crossref_pidm        tbrmemo.tbrmemo_crossref_pidm%TYPE DEFAULT NULL,
                   p_crossref_number      tbrmemo.tbrmemo_crossref_number%TYPE DEFAULT NULL,
                   p_crossref_detail_code tbrmemo.tbrmemo_crossref_detail_code%TYPE DEFAULT NULL,
                   p_crossref_srce_code   tbrmemo.tbrmemo_crossref_srce_code%TYPE DEFAULT NULL,
                   p_atyp_code            tbrmemo.tbrmemo_atyp_code%TYPE DEFAULT NULL,
                   p_atyp_seqno           tbrmemo.tbrmemo_atyp_seqno%TYPE DEFAULT NULL,
                   p_data_origin          tbrmemo.tbrmemo_data_origin%TYPE DEFAULT NULL,
                   p_crossref_dcat_code   tbrmemo.tbrmemo_crossref_dcat_code%TYPE DEFAULT NULL,
                   p_override_hold        VARCHAR2 DEFAULT 'N',
                   p_aidy_code            tbrmemo.tbrmemo_aidy_code%TYPE DEFAULT NULL,
                   p_period               tbrmemo.tbrmemo_period%TYPE DEFAULT NULL,
                   p_tran_number_out      OUT tbrmemo.tbrmemo_tran_number%TYPE,
                   p_rowid_out            OUT gb_common.internal_record_id_type)

Creates a student or non-student memo.
 
* Student memos are identified by a Term Code which is valid in the Term Code validation
* table STVTERM. Non-student memos are identified by a Term Code of ARTERM.
 
* Required fields are PIDM, Term Code, Detail Code, Amount, User (cashier), and Billing Indicator.
* The Transaction Number is determined by the API and returned as an OUT parameter.
 
* Description will default from the Detail Code if not provided.
* Source code will default to T (user entered transaction).
* Address data (atyp_code and atyp_seqno valid in address table STVADDR) is required for non-student transactions,
* and not allowed on student transactions.  If both are left null, an attempt will be made to default from the Customer Profile (TBBCPRF).
* Cross Reference data (identified as _crossref_ ) is pre-validated for use in processing Third Party Contracts,
* Exemptions, and Deposits, and should not be populated for other purposes.
* Other optional parameters may also have editing to enforce Memo business rules.
*
* * Many aspects of the creation of a Memo record are governed by the Context under which the API
* is called.  Two which may be used to interface data from external systems for Package Name TB_MEMO are:
*
* * * * *

Context Name	Context Value	Purpose
ENABLE_SOURCE	Any valid Source Code (TTVSRCE).	Overrides the Transaction Entry Allowed indicator for "System Maintained" Source Codes.
SYSTEM_TRAN	TRUE	Process transactions generated from existing data and rules, such as Contract and Exemption credits * or Financial Aid Disbursement.  Bypasses Hold and User Profile Security checking.  Enables use * of inactive Detail Codes and any valid Source Code.

 
* @param p_pidm Internal Identification Number of the person or non-person account, valid in SPRIDEN. NUMBER(8) Required Key
* @param p_term_code Term associated with the memo. For student memos valid in STVTERM, or ARTERM for non-student memos. VARCHAR2(6) Required
* @param p_detail_code Detail Code associated with the memo, valid in TBBDETC. VARCHAR2(4) Required
* @param p_amount Amount of the memo. NUMBER(7,2) Required
* @param p_user Most recent user to create or update a record, and also populates create_user. VARCHAR2(30) Required
* @param p_entry_date Date the memo was created. DATE
* @param p_billing_ind Memo may be included when billing (Y/N). VARCHAR2(1) Required
* @param p_desc Free-Form description associated with the memo.  Default value comes from the Detail Code description (TBBDETC). VARCHAR2(30)
* @param p_release_date Release Date, currently not used. DATE
* @param p_expiration_date Date the memo item expires, will no longer appear on the bill, and may not be released onto an account. DATE
* @param p_effective_date Date the memo was created or released on account. DATE
* @param p_srce_code Source of the memo when created on the account, including contracts, exemptions, deposits, financial aid, and user-entered.  The default value is user-entered transaction (T), valid in TTVSRCE. VARCHAR2(1) Required
* @param p_crossref_pidm Cross Reference internal identification number for third party memos.  For the third party account, refers to the associated student.  For the student account, refers to the third party. NUMBER(8)
* @param p_crossref_number Cross Reference Number for third party credits (contract number if source code is C), exemption credits (exemption number if source code is E), or deposits released (deposit transaction number if source code is D). NUMBER(8
)
* @param p_crossref_detail_code Cross Reference Detail Code for contract or exemption credits calculated based on detail code rules, valid in TBBDETC. VARCHAR2(4)
* @param p_crossref_srce_code Cross Reference Source for contract or exemption credits, valid in TTVSRCE. VARCHAR2(1)
* @param p_atyp_code Address Type Code, which in combination with ATYP_SEQNO identifies the address associated with the memo. Required for non-student memos; not allowed for student memos. Valid in STVATYP. VARCHAR2(2)
* @param p_atyp_seqno Address Type Sequence Number, which in combination with ATYP_CODE identifies the address associated with the memo. Required for non-student memos; not allowed for student memos. Combination valid and active in SPRADDR. NUMBER(2
)
* @param p_data_origin Source system that last created or updated the data. VARCHAR2(30)
* @param p_crossref_dcat_code Cross Reference Category Code for contract or exemption credits calculated based on category code rules, valid in TTVDCAT. VARCHAR2(3)
* @param p_override_hold Parameter to override Accounts Receivable hold. VARCHAR2(1)
* {*} N Check for AR hold (default)
* {*} Y Bypass hold checking
* @param p_aidy_code The aid year associated with the Financial Aid memo disbursement. VARCHAR2(4)
* @param p_period The enrollment period code for Financial Aid disbursement. VARCHAR2(15)
* @param p_tran_number_out Returns transaction number assigned for memo on the account. NUMBER(8) Required
* @param p_rowid_out Database ROWID of the record to be created. VARCHAR2(18) Required

p_delete

Procedure p_delete(p_pidm        tbrmemo.tbrmemo_pidm%TYPE,
                   p_tran_number tbrmemo.tbrmemo_tran_number%TYPE,
                   p_rowid       gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record.

p_lock

Procedure p_lock(p_pidm        tbrmemo.tbrmemo_pidm%TYPE,
                 p_tran_number tbrmemo.tbrmemo_tran_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

p_update

Procedure p_update(p_pidm            tbrmemo.tbrmemo_pidm%TYPE,
                   p_tran_number     tbrmemo.tbrmemo_tran_number%TYPE,
                   p_amount          tbrmemo.tbrmemo_amount%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_user            tbrmemo.tbrmemo_user%TYPE,
                   p_billing_ind     tbrmemo.tbrmemo_billing_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_desc            tbrmemo.tbrmemo_desc%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_release_date    tbrmemo.tbrmemo_release_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_expiration_date tbrmemo.tbrmemo_expiration_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_effective_date  tbrmemo.tbrmemo_effective_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_atyp_code       tbrmemo.tbrmemo_atyp_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_atyp_seqno      tbrmemo.tbrmemo_atyp_seqno%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_data_origin     tbrmemo.tbrmemo_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid           gb_common.internal_record_id_type DEFAULT NULL,
                   p_aidy_code       tbrmemo.tbrmemo_aidy_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_period          tbrmemo.tbrmemo_period%TYPE DEFAULT dml_common.f_unspecified_string)

Updates a record.
All parameters which are not specified retain the value from the existing record. No defaulting occurs during update.
 
PIDM, Transaction Number, and User (cashier) are required, ROWID is used if passed.
User profile security is validated based on current p_user (not initial create_user) for updates.
Once a memo has been released (Amount is zero) no further changes are allowed.