Search Results sg_bank_code

Overview

The APPS.HR_SG_BANK_ACCT_VALIDATION package body is a Singapore-localization utility within Oracle E-Business Suite that performs bank account number format validation against a fixed set of Singaporean financial institutions. As documented in the ETRM metadata, the package validates bank accounts for the following institutions:

• Bank of Singapore (Bank Code: 7117)
• Hong Kong and Shanghai Banking Corporation (Bank Code: 7232)
• Overseas Chinese Banking Corporation (Bank Code: 7339)
• UCO Bank (Bank Code: 7357)
• Malayan Banking Berhad (Bank Code: 7302)

Its purpose is to ensure that externally supplied or user-entered account numbers conform to the length and structure conventions expected by each bank before downstream payroll or payment processing relies upon them.

Key Procedures and Functions

The documented API surface consists of a single function: VALIDATE_ACCOUNT. It returns a VARCHAR2 value indicating whether the supplied account number is acceptable for the specified bank, returning 'FALSE' where the account number is invalid and 'TRUE' otherwise. The function accepts the account number and the bank name as input parameters, both of which are mandatory.

The body defines a user-defined exception named validation_failed. This exception is raised when required parameters are null or when the supplied bank name cannot be resolved against the SG_BANK_CODE lookup set. The presence of this named exception is directly relevant to searches for the string "validation_failed", as it represents the internal error signalling mechanism for rejected input rather than a distinct database error code. Callers that trap errors by name should reference it accordingly or trap the enclosing exception handler.

Tables Accessed

The only documented table read by this package is HR_LOOKUPS, accessed through a cursor named get_bank_code. The cursor resolves the supplied bank name to its bank code by querying the lookup meaning where lookup_type is 'SG_BANK_CODE', the lookup_code equals the passed bank name, application_id is 800, and enabled_flag is 'Y'. Restricting the query to the enabled flag ensures that obsolete or disabled bank entries are rejected. If the cursor returns no rows, the package raises validation_failed, treating the bank name as unrecognised. No insert, update, or delete operations are documented; the package is strictly read-only with respect to database state.

Usage Notes

Because the ETRM metadata records no inbound package references, this package is not invoked internally by other documented APIs. It is therefore typically called from external or custom code paths — most plausibly from Oracle Forms personalizations, custom Java or PL/SQL interfaces, or concurrent program wrappers that capture employee or supplier bank details for Singapore-based payees. The package is most useful at the point of data entry or import, where early rejection of malformed account numbers prevents downstream payment failures.

Version history shows the package was created in May 2002 and revised in September 2002 (bug 2590076) to permit seven-digit account numbers for Bank of Singapore, HSBC, and OCBC. Any custom code depending on this validation should account for that revised length tolerance and should trap the validation_failed exception rather than relying on the string return value alone, since null parameters raise the exception before any return value is produced.