Package BANINST1.fb

Package BANINST1.fb_common

Utility functions and procedures for Banner Finance APIs.

Program units

f_api_version	Returns the API version number.
f_get_vendor_ap_ach_status	Returns the accounts payable ACH status of the vendor, address type and address sequence combination
f_get_vend_direct_deposit	Returns the accounts payable direct deposit record for the vendor, address type and address sequence combination
f_is_doc_posted	Returns Y if a document has an entry in the transaction history table FGBTRNH
f_is_bank_code_valid	This function returns a Y if the passed in bank code is valid and returns an N if the bank code either does not exist in the bank table or is not effective or has been terminated.
f_get_base_currency	Returns the base currency of the institution.
f_check_run_in_progress	Returns TRUE if a check run is in progress
f_is_enc_rolled	Function that returns a Y if encumbrances have been rolled for the chart and fiscal year.
p_raise_error	This procedure accepts errors stored in a PL/SQL table type format.
p_calc_pct_amt	Procedure to calculate amounts and percents.
p_calc_pct_amt	Procedure to calculate the approved, discount, additional charge and tax amounts.
p_calc_convert_amts	Procedure to calculate converted amounts.
f_set_unspecified	Function to compare the first parameter which is the old value and the second parameter which is the new value and return the new value if it is different from the old value or return dml_common.unspecified_string if both values are identical.
f_set_unspecified	Function to compare the first parameter which is the old value and the second parameter which is the new value and return the new value if it is different from the old value or return dml_common.unspecified_number if both values are identical.
f_set_unspecified	Function to compare the first parameter which is the old value and the second parameter which is the new value and return the new value if it is different from the old value or return dml_common.unspecified_date if both values are identical.
f_get_gjbprun_value	Function to return the user entered value for a job parameter from the job submission table GJBPRUN.

Types

currency_summary_type
finance_rectype
err_rectype
err_tabtype

Constants

CURRENCY_MASK

Cursors

gjbprun_c	Cursor to return all values entered by a user for a parameter during job submission.
fgbtrnh_c	Cursor to return data from the fgbtrnh table

CURRENCY_MASK

CURRENCY_MASK CONSTANT VARCHAR2(30) := '999G999G999G999G999G990D99';

currency_summary_type

SUBTYPE currency_summary_type IS NUMBER(20,2);

finance_rectype

TYPE finance_rectype IS RECORD (
  r_rucl_code         fgbtrnh.fgbtrnh_rucl_code%type,
  r_eff_date          DATE,
  r_acci_code         fgbtrnh.fgbtrnh_acci_code%type,
  r_accrual_ind       fgbtrnh.fgbtrnh_accrual_ind%type,
  r_acct_code         fgbtrnh.fgbtrnh_acct_code%type,
  r_activity_date     DATE,
  r_actv_code         fgbtrnh.fgbtrnh_actv_code%type,
  r_auto_jrnl_id      fgbtrnh.fgbtrnh_auto_jrnl_id%type,
  r_bank_code         gxvbank.gxvbank_bank_code%type,
  r_budget_period     fgbtrnh.fgbtrnh_budget_period%type,
  r_bud_dispn         fgbtrnh.fgbtrnh_bud_dispn%type,
  r_bud_id            fgbtrnh.fgbtrnh_bud_id%type,
  r_check_user_auth   VARCHAR2(1),
  r_cmt_pct           fgbtrnh.fgbtrnh_cmt_pct%type,
  r_cmt_type          fgbtrnh.fgbtrnh_cmt_type%type,
  r_coas_code         fgbtrnh.fgbtrnh_coas_code%type,
  r_curr_code         fgbtrnh.fgbtrnh_curr_code%type,
  r_dep_num           fgbtrnh.fgbtrnh_dep_num%type,
  r_dist_pct          fgbtrnh.fgbtrnh_dist_pct%type,
  r_doc_code          fgbtrnh.fgbtrnh_doc_code%type,
  r_doc_seq_code      fgbtrnh.fgbtrnh_doc_seq_code%type,
  r_doc_ref_num       fgbtrnh.fgbtrnh_doc_ref_num%type,
  r_dr_cr_ind         fgbtrnh.fgbtrnh_dr_cr_ind%type,
  r_encb_action_ind   VARCHAR2(1),
  r_encb_num          fgbtrnh.fgbtrnh_doc_code%type,
  r_encb_type         fgbtrnh.fgbtrnh_encb_type%type,
  r_encd_item_num     fgbtrnh.fgbtrnh_encd_item_num%type,
  r_encd_seq_num      fgbtrnh.fgbtrnh_seq_num%type,
  r_fsyr_code         fgbtrnh.fgbtrnh_fsyr_code%type,
  r_fund_code         fgbtrnh.fgbtrnh_fund_code%type,
  r_item_num          fgbtrnh.fgbtrnh_item_num%type,
  r_locn_code         fgbtrnh.fgbtrnh_locn_code%type,
  r_orgn_code         fgbtrnh.fgbtrnh_orgn_code%type,
  r_posting_period    fgbtrnh.fgbtrnh_posting_period%type,
  r_prjd_code         fgbtrnh.fgbtrnh_prjd_code%type,
  r_prog_code         fgbtrnh.fgbtrnh_prog_code%type,
  r_reversal_ind      fgbtrnh.fgbtrnh_reversal_ind%type,
  r_seq_num           fgbtrnh.fgbtrnh_seq_num%type,
  r_status_ind        VARCHAR2(1),
  r_submission_number fgbtrnh.fgbtrnh_submission_number%type,
  r_trans_amt         fgbtrnh.fgbtrnh_trans_amt%type,
  r_trans_date        fgbtrnh.fgbtrnh_trans_date%type,
  r_trans_desc        fgbtrnh.fgbtrnh_trans_desc%type,
  r_user_id           fgbtrnh.fgbtrnh_user_id%type,
  r_vendor_pidm       fgbtrnh.fgbtrnh_vendor_pidm%type);

err_rectype

TYPE err_rectype IS RECORD (
    r_edit_field    ftvedit.ftvedit_field%TYPE,
    r_edit_seq_num  ftvedit.ftvedit_edit_seq_num%TYPE,
    r_edit_code     ftvedit.ftvedit_edit_code%TYPE,
    r_err_desc      ftvsdat.ftvsdat_title%TYPE,
    r_error_message gb_common_strings.err_type);

err_tabtype

TYPE err_tabtype IS TABLE OF err_rectype
                      INDEX BY BINARY_INTEGER;

gjbprun_c

CURSOR gjbprun_c ( prun_job       gjbprun.gjbprun_job%TYPE,
                   prun_one_up_no gjbprun.gjbprun_one_up_no%TYPE,
                   prun_number    gjbprun.gjbprun_number%TYPE DEFAULT NULL)
RETURN gjbprun%ROWTYPE;

Cursor to return all values entered by a user for a parameter during job submission.
One row will be returned if the parameter accepts only a single value.
Multiple rows will be returned if the parameter can have multiple values.
If no parameter number is passed in, all parameter values are returned.

fgbtrnh_c

cursor fgbtrnh_c(
        p_doc_code          fgbtrnh.fgbtrnh_doc_code%type,
        p_doc_seq_code      fgbtrnh.fgbtrnh_doc_seq_code%type,
        p_item_num          fgbtrnh.FGBTRNH_ITEM_NUM%type DEFAULT NULL,
        p_seq_num           fgbtrnh.FGBTRNH_SEQ_NUM%type DEFAULT NULL,
        p_submission_number fgbtrnh.FGBTRNH_SUBMISSION_NUMBER%type DEFAULT NULL,
        p_reversal_ind      fgbtrnh.FGBTRNH_REVERSAL_IND%type DEFAULT NULL,
        p_serial_num        fgbtrnh.FGBTRNH_SERIAL_NUM%type DEFAULT NULL )
RETURN fgbtrnh%ROWTYPE;

Cursor to return data from the fgbtrnh table

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_get_vendor_ap_ach_status

Function f_get_vendor_ap_ach_status(p_vend_pidm    ftvvend.ftvvend_pidm%type,
                                    p_atyp_code    ftvvend.ftvvend_atyp_code%type DEFAULT NULL,
                                    p_atyp_seq_num ftvvend.ftvvend_addr_seqno%type DEFAULT NULL)
  RETURN gxrdird.gxrdird_status%TYPE

Returns the accounts payable ACH status of the vendor, address type and address sequence combination

Parameters

	p_vend_pidm	The internal number for this vendor
	p_atyp_code	The address type for the vendor
	p_atyp_seq_num	The address sequence for the vendor returns the gxrdird status

f_get_vend_direct_deposit

Function f_get_vend_direct_deposit(p_vend_pidm    ftvvend.ftvvend_pidm%type,
                                   p_atyp_code    ftvvend.ftvvend_atyp_code%type DEFAULT NULL,
                                   p_atyp_seq_num ftvvend.ftvvend_addr_seqno%type DEFAULT NULL)
  RETURN gxrdird%ROWTYPE

Returns the accounts payable direct deposit record for the vendor, address type and address sequence combination

Parameters

	p_vend_pidm	The internal number for this vendor
	p_atyp_code	The address type for the vendor
	p_atyp_seq_num	The address sequence for the vendor returns the gxrdird record

f_is_doc_posted

Function f_is_doc_posted(p_doc_code          fgbtrnh.fgbtrnh_doc_code%type,
                         p_doc_seq_code      fgbtrnh.fgbtrnh_doc_seq_code%type,
                         p_item_num          fgbtrnh.FGBTRNH_ITEM_NUM%type DEFAULT NULL,
                         p_seq_num           fgbtrnh.FGBTRNH_SEQ_NUM%type DEFAULT NULL,
                         p_submission_number fgbtrnh.FGBTRNH_SUBMISSION_NUMBER%type DEFAULT NULL,
                         p_reversal_ind      fgbtrnh.FGBTRNH_REVERSAL_IND%type DEFAULT NULL,
                         p_serial_num        fgbtrnh.FGBTRNH_SERIAL_NUM%type DEFAULT NULL)
  RETURN VARCHAR2

Returns Y if a document has an entry in the transaction history table FGBTRNH
and N if the entry does not exist in FGBTRNH. The document code parameter p_doc_code
and document type parameter p_doc_seq_code are required.

Parameters

	p_doc_code	The document code - required
	p_doc_seq_code	The type of the document. eg. 1 for requisition, 2 for PO, 3 for invoice. - required
	p_item_num	The item number of the document
	p_seq_num	The sequence number of the document
	p_submission_number	The submission number of the document
	p_reversal_ind	The reversal indicator. Populate if checking for a cancelled document.
	p_serial_num	The serial number

f_is_bank_code_valid

Function f_is_bank_code_valid(p_bank_code  gxvbank.gxvbank_bank_code%TYPE,
                              p_trans_date DATE,
                              p_coas_code  ftvcoas.ftvcoas_coas_code%type default NULL)
  RETURN VARCHAR2

This function returns a Y if the passed in bank code is valid and returns an N if the bank code either does not exist in the bank table or is not effective or has been terminated.

Parameters

	p_bank_code	The bank code that is to be validated.
	p_trans_date	The date against which the bank code should be validated.

f_get_base_currency

Function f_get_base_currency RETURN gubinst.gubinst_base_curr_code%type

Returns the base currency of the institution.

f_check_run_in_progress

Function f_check_run_in_progress RETURN BOOLEAN

Returns TRUE if a check run is in progress

f_is_enc_rolled

Function f_is_enc_rolled(p_coas_code FTVFSYR.FTVFSYR_COAS_CODE%TYPE,
                         p_fsyr_code FTVFSYR.FTVFSYR_FSYR_CODE%TYPE)
  RETURN VARCHAR2

Function that returns a Y if encumbrances have been rolled for the chart and fiscal year.
Returns an N if they have not been rolled.

Parameters

	p_coas_code	The chart of accounts code. Required. VARCHAR2(1).
	p_fsyr_code	The fiscal year code for which this check is being made. Required. VARCHAR2(2).

p_raise_error

Procedure p_raise_error(p_err_tab fb_common.err_tabtype)

This procedure accepts errors stored in a PL/SQL table type format.
The errors are concatenated into a string and then an oracle exception is raised with the error string.

Parameters

p_err_tab

A PL/SQL table type composed of individual error strings.

p_calc_pct_amt

Procedure p_calc_pct_amt(p_pct          IN OUT NUMBER,
                         p_amt          IN OUT NUMBER,
                         p_error_out    OUT VARCHAR2,
                         p_total_amt    NUMBER DEFAULT 0,
                         p_round_pct_to NUMBER DEFAULT 4,
                         p_round_amt_to NUMBER DEFAULT 2)

Procedure to calculate amounts and percents.

Parameters

	p_pct	The percentage value . Either percent or amount is required.
	p_amt	The amount. Either percent or amount is required.
	p_error_out	Any error message returned by the procedure
	p_total_amt	The amount used to calculate the percent..
	p_round_pct_to	The decimal number to which the percent value should be rounded to.
	p_round_amt_to	The decimal number to which the amount should be rounded to.

p_calc_pct_amt

Procedure p_calc_pct_amt(p_appr_pct     IN OUT NUMBER,
                         p_appr_amt     IN OUT NUMBER,
                         p_disc_pct     IN OUT NUMBER,
                         p_disc_amt     IN OUT NUMBER,
                         p_addl_pct     IN OUT NUMBER,
                         p_addl_amt     IN OUT NUMBER,
                         p_tax_pct      IN OUT NUMBER,
                         p_tax_amt      IN OUT NUMBER,
                         p_error_out    OUT VARCHAR2,
                         p_total_appr   NUMBER DEFAULT 0,
                         p_total_disc   NUMBER DEFAULT 0,
                         p_total_addl   NUMBER DEFAULT 0,
                         p_total_tax    NUMBER DEFAULT 0,
                         p_round_pct_to NUMBER DEFAULT 4,
                         p_round_amt_to NUMBER DEFAULT 2)

Procedure to calculate the approved, discount, additional charge and tax amounts. This procedure is used in the requisition, purchase order and invoice APIs.

Parameters

	p_appr_pct	The ratio of the approved amount to the total approved amount.
	p_appr_amt	The approved amount.
	p_disc_pct	The ratio of the discount amount to the total discount amount.
	p_disc_amt	The discount amount.
	p_addl_pct	The ratio of the additional charge amount to the total additional charge amount.
	p_addl_amt	The additional charge amount.
	p_tax_pct	The ratio of the tax amount to the total tax amount.
	p_tax_amt	The tax amount.
	p_total_appr	The total approved amount used to calculate the approved amount and percent.
	p_total_disc	The total discount amount used to calculate the discount amount and percent.
	p_total_addl	The total additional charge amount used to calculate the additional charge amount and percent.
	p_total_tax	The total tax amount used to calculate the tax amount and percent.
	p_error_out	Any error message generated by the procedure during the calculations.
	p_round_pct_to	The decimal number to which the percent value should be rounded to.
	p_round_amt_to	The decimal number to which the amount should be rounded to.

p_calc_convert_amts

Procedure p_calc_convert_amts(p_unit_price         NUMBER,
                              p_disc_amt           NUMBER,
                              p_addl_amt           NUMBER,
                              p_tax_amt            NUMBER,
                              p_convert_unit_price OUT NUMBER,
                              p_convert_disc_amt   OUT NUMBER,
                              p_convert_addl_amt   OUT NUMBER,
                              p_convert_tax_amt    OUT NUMBER,
                              p_curr_code          VARCHAR2,
                              p_trans_date         DATE DEFAULT SYSDATE)

Procedure to calculate converted amounts. This procedure is used by the requisition, purchase order and invoice APIs.

Parameters

	p_unit_price	The unit price of an item in foreign currency.
	p_disc_amt	The discount amount in foreign currency.
	p_addl_amt	The additional charge amount in foreign currency.
	p_tax_amt	The tax amount in foreign currency.
	p_convert_unit_price	The unit price in base currency.
	p_convert_disc_amt	The discount amount in base currency.
	p_convert_addl_amt	The additional charge amount in base currency.
	p_convert_tax_amt	The tax amount in base currency.
	p_curr_code	The foreign currency code.
	p_trans_date	The date based on which the currency conversion rate is to be calculated.

f_set_unspecified

Function f_set_unspecified(p_old_value VARCHAR2, p_new_value VARCHAR2)
  RETURN VARCHAR2

Function to compare the first parameter which is the old value and the second parameter which is the new value and return the new value if it is different from the old value or return dml_common.unspecified_string if both values are identical. Null is considered a unique value.

Parameters

	p_old_value	VARCHAR2
	p_new_value	VARCHAR2

f_set_unspecified

Function f_set_unspecified(p_old_value NUMBER, p_new_value NUMBER)
  RETURN NUMBER

Function to compare the first parameter which is the old value and the second parameter which is the new value and return the new value if it is different from the old value or return dml_common.unspecified_number if both values are identical. Null is considered a unique value.

Parameters

	p_old_value	NUMBER
	p_new_value	NUMBER

f_set_unspecified

Function f_set_unspecified(p_old_value DATE, p_new_value DATE) RETURN DATE

Function to compare the first parameter which is the old value and the second parameter which is the new value and return the new value if it is different from the old value or return dml_common.unspecified_date if both values are identical. Null is considered a unique value.

Parameters

	p_old_value	DATE
	p_new_value	DATE

f_get_gjbprun_value

Function f_get_gjbprun_value(p_gjbprun_job       gjbprun.gjbprun_job%TYPE,
                             p_gjbprun_one_up_no gjbprun.gjbprun_one_up_no%TYPE,
                             p_gjbprun_number    gjbprun.gjbprun_number%TYPE)
  RETURN gjbprun.gjbprun_value%TYPE

Function to return the user entered value for a job parameter from the job submission table GJBPRUN.

Parameters

	p_gjbprun_job	The name of the job that is being submitted. Must exist in the jobs table GJBJOBS. VARCHAR2
	p_gjbprun_one_up_no	The unique one up number created by the job submissions form. NUMBER
	p_gjbprun_number	The number of the parameter whose value is being selected.

Returns

The user entered value for the parameter. This value is entered by the user in the job submission form GJAPCTL.