M_ENTITY_NAME

M_ENTITY_NAME     CONSTANT VARCHAR2(7) := 'DEPOSIT';

Business Entity name

AR_INFINITY_DATE

AR_INFINITY_DATE  CONSTANT DATE := TO_DATE('31-DEC-2099','DD-MON-YYYY');

deposit_rec

TYPE deposit_rec IS RECORD (
   r_pidm                    tbrdepo.tbrdepo_pidm%TYPE,
   r_tran_number             tbrdepo.tbrdepo_tran_number%TYPE,
   r_term_code               tbrdepo.tbrdepo_term_code%TYPE,
   r_detail_code_deposit     tbrdepo.tbrdepo_detail_code_deposit%TYPE,
   r_desc                    tbrdepo.tbrdepo_desc%TYPE,
   r_amount                  tbrdepo.tbrdepo_amount%TYPE,
   r_detail_code_payment     tbrdepo.tbrdepo_detail_code_payment%TYPE,
   r_auto_release_ind        tbrdepo.tbrdepo_auto_release_ind%TYPE,
   r_acct_feed_ind           tbrdepo.tbrdepo_acct_feed_ind%TYPE,
   r_user                    tbrdepo.tbrdepo_user%TYPE,
   r_entry_date              tbrdepo.tbrdepo_entry_date%TYPE,
   r_receipt_number          tbrdepo.tbrdepo_receipt_number%TYPE,
   r_min_amount              tbrdepo.tbrdepo_min_amount%TYPE,
   r_expiration_date         tbrdepo.tbrdepo_expiration_date%TYPE,
   r_release_date            tbrdepo.tbrdepo_release_date%TYPE,
   r_effective_date          tbrdepo.tbrdepo_effective_date%TYPE,
   r_session_number          tbrdepo.tbrdepo_session_number%TYPE,
   r_cshr_end_date           tbrdepo.tbrdepo_cshr_end_date%TYPE,
   r_curr_code               tbrdepo.tbrdepo_curr_code%TYPE,
   r_trans_date              tbrdepo.tbrdepo_trans_date%TYPE,
   r_foreign_amount          tbrdepo.tbrdepo_foreign_amount%TYPE,
   r_feed_date               tbrdepo.tbrdepo_feed_date%TYPE,
   r_feed_doc_code           tbrdepo.tbrdepo_feed_doc_code%TYPE,
   r_atyp_code               tbrdepo.tbrdepo_atyp_code%TYPE,
   r_atyp_seqno              tbrdepo.tbrdepo_atyp_seqno%TYPE,
   r_data_origin             tbrdepo.tbrdepo_data_origin%TYPE,
   r_create_source           tbrdepo.tbrdepo_create_source%TYPE,
   r_document_number         tbrdepo.tbrdepo_document_number%TYPE,
   r_balance                 tbrdepo.tbrdepo_amount%TYPE,
   r_deposit_type            tbbdepc.tbbdepc_dtyp_code%TYPE,
   r_internal_record_id      gb_common.internal_record_id_type );

Entity record type

deposit_ref

TYPE deposit_ref IS REF CURSOR RETURN deposit_rec;

Entity cursor variable type

deposit_key_rec

TYPE deposit_key_rec IS RECORD (
   r_pidm                    tbrdepo.tbrdepo_pidm%TYPE,
   r_tran_number             tbrdepo.tbrdepo_tran_number%TYPE,
   r_internal_record_id      gb_common.internal_record_id_type );

Key record type

deposit_key_ref

TYPE deposit_key_ref IS REF CURSOR RETURN deposit_key_rec;

Key cursor variable type

deposit_sum_rec

TYPE deposit_sum_rec IS RECORD (
   r_pidm                    tbrdepo.tbrdepo_pidm%TYPE,
   r_deposit_type            tbbdepc.tbbdepc_dtyp_code%TYPE,
   r_term_code               tbrdepo.tbrdepo_term_code%TYPE,
   r_detail_code_deposit     tbrdepo.tbrdepo_detail_code_deposit%TYPE,
   r_desc                    tbrdepo.tbrdepo_desc%TYPE,
   r_amount                  NUMBER(9,2),
   r_balance                 NUMBER(9,2));

Summary record type

deposit_sum_ref

TYPE deposit_sum_ref IS REF CURSOR RETURN deposit_sum_rec;

Summary cursor variable type

deposit_tab

TYPE deposit_tab IS TABLE OF deposit_rec INDEX BY BINARY_INTEGER;

Entity table type

f_api_version

Function f_api_version RETURN PLS_INTEGER

Returns the API version number.

f_exists

Function f_exists(p_pidm        tbrdepo.tbrdepo_pidm%TYPE,
                  p_tran_number tbrdepo.tbrdepo_tran_number%TYPE,
                  p_rowid       VARCHAR2 DEFAULT NULL) RETURN VARCHAR2

Checks to see if a record exists.

f_isequal

Function f_isequal(rec_one deposit_rec, rec_two deposit_rec)
  RETURN VARCHAR2

Compare two records for equality.
Tests each field of rec_one against the corresponding field of rec_two. Two null values are considered equal.

f_query_all

Function f_query_all(p_pidm                tbrdepo.tbrdepo_pidm%TYPE DEFAULT NULL,
                     p_tran_number         tbrdepo.tbrdepo_tran_number%TYPE DEFAULT NULL,
                     p_term_code           tbrdepo.tbrdepo_term_code%TYPE DEFAULT NULL,
                     p_detail_code_deposit tbrdepo.tbrdepo_detail_code_deposit%TYPE DEFAULT NULL,
                     p_deposit_type        tbbdepc.tbbdepc_dtyp_code%TYPE DEFAULT NULL)
  RETURN deposit_ref

Selects all records for the entity.
Parameters default to NULL and are ignored if not populated.
AND is used when multiple criteria are given.
Wildcard (%) may be used with Term Code and Detail Code Deposit

f_query_cashier

Function f_query_cashier(p_user           tbrdepo.tbrdepo_user%TYPE,
                         p_session_number tbrdepo.tbrdepo_session_number%TYPE,
                         p_cshr_end_date  tbrdepo.tbrdepo_cshr_end_date%TYPE DEFAULT NULL)
  RETURN deposit_ref

Select all records for a cashiering session.

f_query_one

Function f_query_one(p_pidm        tbrdepo.tbrdepo_pidm%TYPE,
                     p_tran_number tbrdepo.tbrdepo_tran_number%TYPE)
  RETURN deposit_ref

Selects one record using key.

f_query_by_rowid

Function f_query_by_rowid(p_rowid VARCHAR2) RETURN deposit_ref

Selects one record using rowid.

f_query_one_lock

Function f_query_one_lock(p_pidm        tbrdepo.tbrdepo_pidm%TYPE,
                          p_tran_number tbrdepo.tbrdepo_tran_number%TYPE,
                          p_rowid       VARCHAR2 DEFAULT NULL)
  RETURN deposit_ref

Selects one record and locks it.

f_query_sum_deposit

Function f_query_sum_deposit(p_pidm                tbrdepo.tbrdepo_pidm%TYPE,
                             p_deposit_type        tbbdepc.tbbdepc_dtyp_code%TYPE DEFAULT NULL,
                             p_term_code           tbrdepo.tbrdepo_term_code%TYPE DEFAULT NULL,
                             p_detail_code_deposit tbrdepo.tbrdepo_detail_code_deposit%TYPE DEFAULT NULL)
  RETURN deposit_sum_ref

Selects summarized deposit records for the entity.
Parameters other than PIDM default to NULL and include all records if not populated.
AND is used when multiple criteria are given.
Wildcard (%) may be used with term or deposit detail code.
When the term parameter is null the term values are not returned, summing without regard to term. Use % in the term parameter to include all terms with a sum for each value found.
When the deposit detail code parameter is null, detail code and description are not returned, summing without regard to these values. Use % in the deposit detail code parameter to include a sum for each value found.
Leaving deposit type, term, and deposit detail code parameters null returns a summary by type of all deposits for the entity.

f_max_tran_number

Function f_max_tran_number(p_pidm tbrdepo.tbrdepo_pidm%TYPE)
  RETURN tbrdepo.tbrdepo_tran_number%TYPE

Returns the maximum deposit transaction number for a PIDM.

f_tbrdepo_auto_rel_exists

Function f_tbrdepo_auto_rel_exists(p_term tbrdepo.tbrdepo_term_code%TYPE,
                                   p_pidm tbrdepo.tbrdepo_pidm%TYPE)
  RETURN VARCHAR2

Returns a Y if records exist that can be released automatically to the ledger.
 
A deposit can be released automatically when the Auto Release Indicator is Y, the Release Date is null or is prior to the current date, and the remaining deposit Balance is greater than the Minimum required.  After the expiration date has passed, any remaining Balance can be released without regard to the Minimum, if the other two conditions are met.

p_create

Procedure p_create(p_pidm                tbrdepo.tbrdepo_pidm%TYPE,
                   p_term_code           tbrdepo.tbrdepo_term_code%TYPE,
                   p_detail_code_deposit tbrdepo.tbrdepo_detail_code_deposit%TYPE,
                   p_desc                tbrdepo.tbrdepo_desc%TYPE DEFAULT NULL,
                   p_amount              tbrdepo.tbrdepo_amount%TYPE DEFAULT NULL,
                   p_detail_code_payment tbrdepo.tbrdepo_detail_code_payment%TYPE DEFAULT NULL,
                   p_auto_release_ind    tbrdepo.tbrdepo_auto_release_ind%TYPE DEFAULT NULL,
                   p_acct_feed_ind       tbrdepo.tbrdepo_acct_feed_ind%TYPE DEFAULT 'Y',
                   p_user                tbrdepo.tbrdepo_user%TYPE,
                   p_entry_date          tbrdepo.tbrdepo_entry_date%TYPE DEFAULT TRUNC(SYSDATE),
                   p_receipt_number      tbrdepo.tbrdepo_receipt_number%TYPE DEFAULT NULL,
                   p_min_amount          tbrdepo.tbrdepo_min_amount%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_expiration_date     tbrdepo.tbrdepo_expiration_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_release_date        tbrdepo.tbrdepo_release_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_effective_date      tbrdepo.tbrdepo_effective_date%TYPE,
                   p_session_number      tbrdepo.tbrdepo_session_number%TYPE DEFAULT 0,
                   p_cshr_end_date       tbrdepo.tbrdepo_cshr_end_date%TYPE DEFAULT NULL,
                   p_curr_code           tbrdepo.tbrdepo_curr_code%TYPE DEFAULT NULL,
                   p_trans_date          tbrdepo.tbrdepo_trans_date%TYPE DEFAULT NULL,
                   p_foreign_amount      tbrdepo.tbrdepo_foreign_amount%TYPE DEFAULT NULL,
                   p_atyp_code           tbrdepo.tbrdepo_atyp_code%TYPE DEFAULT NULL,
                   p_atyp_seqno          tbrdepo.tbrdepo_atyp_seqno%TYPE DEFAULT NULL,
                   p_data_origin         tbrdepo.tbrdepo_data_origin%TYPE DEFAULT NULL,
                   p_document_number     tbrdepo.tbrdepo_document_number%TYPE DEFAULT NULL,
                   p_override_hold       VARCHAR2 DEFAULT 'N',
                   p_tran_number_out     OUT tbrdepo.tbrdepo_tran_number%TYPE,
                   p_rowid_out           OUT VARCHAR2)

Creates a student or a non-student deposit.
Student deposits are identified by a Term Code which is valid in the Term Code validation table STVTERM. Non-student deposits are identified by a Term Code of ARTERM.
 
Required fields are PIDM, Term Code, Detail Code Deposit, User (cashier), and Effective Date.
If the parameters for Detail Code Deposit, Auto Release indicator, Minimum, Expiration Date, or Release Date are not specified, the values in the Deposit Detail Definition table (TBBDEPC) will be used.
The Transaction Number is determined by the API and returned as an OUT parameter.
Amount is required except where it may be calculated using Currency Code and Foreign Amount. If all 3 of these parameters are populated, then the calculation (using the rate in effect for the Transaction Date) must yield the Amount provided.
 
Description will default from the Detail Code Deposit if not provided.
Accounting Feed will default to Y.
Session Number will default to 0 (current session). Any other value requires Cashier End Date, and the combination must be valid in the Cashier Session table TBBCSHR.
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).
Receipt Number is never generated by the API, it must be determined in advance and provided as a parameter.
Currency Code and Foreign Amount are both null or both provided.
Minimum and Expiration Date are both null or both provided.  The Expiration Date must be greater than or equal to the current date, and must be greater than or equal to the Release Date if it is populated.
Release Date, if populated, must be greater than the current date.
Other optional parameters may also have editing to enforce Deposit business rules.

p_delete

Procedure p_delete(p_pidm        tbrdepo.tbrdepo_pidm%TYPE,
                   p_tran_number tbrdepo.tbrdepo_tran_number%TYPE,
                   p_rowid       VARCHAR2 DEFAULT NULL)

Deletes a record.
Delete is enabled for Purge processing only, and should not be called for other purposes.

p_lock

Procedure p_lock(p_pidm        tbrdepo.tbrdepo_pidm%TYPE,
                 p_tran_number tbrdepo.tbrdepo_tran_number%TYPE,
                 p_rowid_inout IN OUT VARCHAR2)

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             tbrdepo.tbrdepo_pidm%TYPE,
                   p_tran_number      tbrdepo.tbrdepo_tran_number%TYPE,
                   p_desc             tbrdepo.tbrdepo_desc%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_auto_release_ind tbrdepo.tbrdepo_auto_release_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_acct_feed_ind    tbrdepo.tbrdepo_acct_feed_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_min_amount       tbrdepo.tbrdepo_min_amount%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_expiration_date  tbrdepo.tbrdepo_expiration_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_release_date     tbrdepo.tbrdepo_release_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_effective_date   tbrdepo.tbrdepo_effective_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_session_number   tbrdepo.tbrdepo_session_number%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_cshr_end_date    tbrdepo.tbrdepo_cshr_end_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_feed_date        tbrdepo.tbrdepo_feed_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_feed_doc_code    tbrdepo.tbrdepo_feed_doc_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_atyp_code        tbrdepo.tbrdepo_atyp_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_atyp_seqno       tbrdepo.tbrdepo_atyp_seqno%TYPE DEFAULT dml_common.f_unspecified_number,
                   p_data_origin      tbrdepo.tbrdepo_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_document_number  tbrdepo.tbrdepo_document_number%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid            VARCHAR2 DEFAULT NULL)

Updates a record.
All parameters which are not specified retain the value from the existing record. No defaulting occurs during update.
 
PIDM and Transaction Number, or ROWID required.
Many updates to the record will be made by system processes.
End user updates might include changes to -
    Minimum required
    Release date
    Expiration date
    Description
    Address of non-student transaction
    Document Number

p_release

Procedure p_release(p_pidm                tbrdepo.tbrdepo_pidm%TYPE,
                    p_tran_number         tbrdepo.tbrdepo_tran_number%TYPE DEFAULT NULL,
                    p_term_code           tbrdepo.tbrdepo_term_code%TYPE DEFAULT NULL,
                    p_detail_code_deposit tbrdepo.tbrdepo_detail_code_deposit%TYPE DEFAULT NULL,
                    p_deposit_type        tbbdepc.tbbdepc_dtyp_code%TYPE DEFAULT NULL,
                    p_user                tbrdepo.tbrdepo_user%TYPE,
                    p_receipt_number      tbrdepo.tbrdepo_receipt_number%TYPE DEFAULT NULL,
                    p_effective_date      tbrdepo.tbrdepo_effective_date%TYPE,
                    p_session_number      tbrdepo.tbrdepo_session_number%TYPE DEFAULT 0,
                    p_cshr_end_date       tbrdepo.tbrdepo_cshr_end_date%TYPE DEFAULT NULL,
                    p_release_amount      tbrdepo.tbrdepo_min_amount%TYPE DEFAULT NULL,
                    p_release_mode        VARCHAR2 DEFAULT 'M',
                    p_run_mode            VARCHAR2 DEFAULT NULL)

Releases one or more deposits.
 
PIDM, User (cashier), and Effective Date are required.
 
For Manual release -
    Transaction number and Release amount are required
    Term and Detail Code Deposit parameters may be left null
         If populated, they must match the data in the record identified by the Transaction number
 
For Automatic release -
     Term, Detail Code Deposit, and/or Deposit Type may be specified to limit the deposits released
          AND is used when multiple criteria are given
          Wildcard (%) may be used with Term Code and Detail Code Deposit
     Transaction number and Release amount should not be populated
 
A deposit is released automatically when the Auto Release Indicator is Y and the Release Date is null or is prior to the current date.  The Amount released is the remaining deposit Balance less the Minimum required.  After the expiration date has passed, any remaining Balance is released without regard to the Minimum.
 
Note that if messaging is enabled for DEPOSIT, the release procedure will generate an UPDATE message reflecting the new Balance.

p_release

Procedure p_release(p_pidm                tbrdepo.tbrdepo_pidm%TYPE,
                    p_tran_number         tbrdepo.tbrdepo_tran_number%TYPE DEFAULT NULL,
                    p_term_code           tbrdepo.tbrdepo_term_code%TYPE DEFAULT NULL,
                    p_detail_code_deposit tbrdepo.tbrdepo_detail_code_deposit%TYPE DEFAULT NULL,
                    p_deposit_type        tbbdepc.tbbdepc_dtyp_code%TYPE DEFAULT NULL,
                    p_user                tbrdepo.tbrdepo_user%TYPE,
                    p_receipt_number      tbrdepo.tbrdepo_receipt_number%TYPE DEFAULT NULL,
                    p_effective_date      tbrdepo.tbrdepo_effective_date%TYPE,
                    p_session_number      tbrdepo.tbrdepo_session_number%TYPE DEFAULT 0,
                    p_cshr_end_date       tbrdepo.tbrdepo_cshr_end_date%TYPE DEFAULT NULL,
                    p_release_amount      tbrdepo.tbrdepo_min_amount%TYPE DEFAULT NULL,
                    p_release_mode        VARCHAR2 DEFAULT 'M',
                    p_run_mode            VARCHAR2 DEFAULT NULL,
                    p_amt_released_out    OUT NUMBER,
                    p_amt_remain_out      OUT NUMBER,
                    p_release_msg_out     OUT gb_common_strings.err_type)

Releases one or more deposits, returning information regarding the amount released and remaining deposits.
 
This version of p_release will return the total amount released to the receivable account, the total of deposits which remain on record where the term and type meet the parameter selection, and a message why deposit(s) were not released in full where appropriate.
 
See additional narrative and parameter descriptions above.