Package gb

Package gb_visa

Common Business interface for the VISA API (gb_visa).

The PIDM, Sequence Number, and Visa Type are the key columns of the Visa table.

Program units

f_api_version	Returns the API version number.
f_exists	Checks to see if a record exists.
f_any_exists	Checks to see if a vtyp exists for a given pidm.
f_isequal	Compares two records for equality.
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_update	Updates a record.

Types

visa_rec	Business Entity record type
visa_ref	Entity cursor variable type
visa_tab	Entity table type

Constants

M_ENTITY_NAME

Business Entity Name

M_ENTITY_NAME

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

Business Entity Name

visa_rec

TYPE visa_rec IS RECORD (
  r_pidm                    gorvisa.gorvisa_pidm%TYPE,
  r_seq_no                  gorvisa.gorvisa_seq_no%TYPE,
  r_vtyp_code               gorvisa.gorvisa_vtyp_code%TYPE,
  r_visa_number             gorvisa.gorvisa_visa_number%TYPE,
  r_natn_code_issue         gorvisa.gorvisa_natn_code_issue%TYPE,
  r_viss_code               gorvisa.gorvisa_viss_code%TYPE,
  r_visa_start_date         gorvisa.gorvisa_visa_start_date%TYPE,
  r_visa_expire_date        gorvisa.gorvisa_visa_expire_date%TYPE,
  r_entry_ind               gorvisa.gorvisa_entry_ind%TYPE,
  r_user_id                 gorvisa.gorvisa_user_id%TYPE,
  r_visa_req_date           gorvisa.gorvisa_visa_req_date%TYPE,
  r_visa_issue_date         gorvisa.gorvisa_visa_issue_date%TYPE,
  r_pent_code               gorvisa.gorvisa_pent_code%TYPE,
  r_no_entries              gorvisa.gorvisa_no_entries%TYPE,
  r_data_origin             gorvisa.gorvisa_data_origin%TYPE,
  r_internal_record_id      gb_common.internal_record_id_type);

Business Entity record type

visa_ref

TYPE visa_ref IS REF CURSOR RETURN visa_rec;

Entity cursor variable type

visa_tab

TYPE visa_tab IS TABLE OF visa_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_pidm      gorvisa.gorvisa_pidm%TYPE,
                  p_seq_no    gorvisa.gorvisa_seq_no%TYPE,
                  p_vtyp_code gorvisa.gorvisa_vtyp_code%TYPE,
                  p_rowid     gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks to see if a record exists.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3) Required Key
	p_vtyp_code	The type of Visa issued. VARCHAR2(2) Required Key
	p_rowid	Database ROWID of the record to be selected. VARCHAR2(18)

Returns

Y if found, otherwise N.

f_any_exists

FUNCTION f_any_exists(p_pidm      gorvisa.gorvisa_pidm%TYPE,
                      p_seq_no    gorvisa.gorvisa_seq_no%TYPE DEFAULT NULL,
                      p_vtyp_code gorvisa.gorvisa_vtyp_code%TYPE,
                      p_rowid     gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2

Checks to see if a vtyp exists for a given pidm.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3)
	p_vtyp_code	The type of Visa issued. VARCHAR2(2) 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 visa_rec,
                   rec_two visa_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 visa_rec Required
	rec_two	The second record to compare. Type visa_rec Required

Returns

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

f_query_all

FUNCTION f_query_all(p_pidm      gorvisa.gorvisa_pidm%TYPE,
                     p_seq_no    gorvisa.gorvisa_seq_no%TYPE DEFAULT NULL,
                     p_vtyp_code gorvisa.gorvisa_vtyp_code%TYPE DEFAULT NULL)
  RETURN visa_ref

Selects all records for the entity.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3)
	p_vtyp_code	The type of Visa issued. VARCHAR2(2)

Returns

A cursor variable that will fetch the set of records.

f_query_one

FUNCTION f_query_one(p_pidm      gorvisa.gorvisa_pidm%TYPE,
                     p_seq_no    gorvisa.gorvisa_seq_no%TYPE,
                     p_vtyp_code gorvisa.gorvisa_vtyp_code%TYPE)
  RETURN visa_ref

Selects one record using key.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3) Required Key
	p_vtyp_code	The type of Visa issued. VARCHAR2(2) 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 visa_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_pidm      gorvisa.gorvisa_pidm%TYPE,
                          p_seq_no    gorvisa.gorvisa_seq_no%TYPE,
                          p_vtyp_code gorvisa.gorvisa_vtyp_code%TYPE,
                          p_rowid     gb_common.internal_record_id_type DEFAULT NULL)
  RETURN visa_ref

Selects one record and locks it.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3) Required Key
	p_vtyp_code	The type of Visa issued. VARCHAR2(2) 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             gorvisa.gorvisa_pidm%TYPE,
                   p_seq_no_inout     IN OUT gorvisa.gorvisa_seq_no%TYPE,
                   p_vtyp_code        gorvisa.gorvisa_vtyp_code%TYPE,
                   p_visa_number      gorvisa.gorvisa_visa_number%TYPE DEFAULT NULL,
                   p_natn_code_issue  gorvisa.gorvisa_natn_code_issue%TYPE DEFAULT NULL,
                   p_viss_code        gorvisa.gorvisa_viss_code%TYPE DEFAULT NULL,
                   p_visa_start_date  gorvisa.gorvisa_visa_start_date%TYPE DEFAULT NULL,
                   p_visa_expire_date gorvisa.gorvisa_visa_expire_date%TYPE DEFAULT NULL,
                   p_entry_ind        gorvisa.gorvisa_entry_ind%TYPE DEFAULT NULL,
                   p_user_id          gorvisa.gorvisa_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_visa_req_date    gorvisa.gorvisa_visa_req_date%TYPE DEFAULT NULL,
                   p_visa_issue_date  gorvisa.gorvisa_visa_issue_date%TYPE DEFAULT NULL,
                   p_pent_code        gorvisa.gorvisa_pent_code%TYPE DEFAULT NULL,
                   p_no_entries       gorvisa.gorvisa_no_entries%TYPE DEFAULT NULL,
                   p_data_origin      gorvisa.gorvisa_data_origin%TYPE DEFAULT NULL,
                   p_rowid_out        OUT gb_common.internal_record_id_type)

Creates a record.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3) Required Key
	p_vtyp_code	The type of Visa issued. VARCHAR2(2) Required Key
	p_visa_number	A unique identification number assigned to a Visa. VARCHAR2(18)
	p_natn_code_issue	The country code of the nation issuing the Visa. VARCHAR2(5)
	p_viss_code	The agency authorizing the Visa. VARCHAR2(6)
	p_visa_start_date	The date the Visa begins. DATE
	p_visa_expire_date	The date the Visa expires. DATE
	p_entry_ind	This field indicates whether the Visa is for entry into the country. VARCHAR2(1)
	p_user_id	The Oracle ID of the user who changed the record. VARCHAR2(30) Required
	p_visa_req_date	The date the Visa was requested. DATE
	p_visa_issue_date	The date the Visa was Issued. DATE
	p_pent_code	The port of entry into the country. VARCHAR2(3)
	p_no_entries	The number of entries into the country. VARCHAR2(2)
	p_data_origin	Source system that created or updated the row. VARCHAR2(30)
	p_rowid_out	Database ROWID of the record to be created. VARCHAR2(18) Required

p_delete

PROCEDURE p_delete(p_pidm      gorvisa.gorvisa_pidm%TYPE,
                   p_seq_no    gorvisa.gorvisa_seq_no%TYPE,
                   p_vtyp_code gorvisa.gorvisa_vtyp_code%TYPE,
                   p_rowid     gb_common.internal_record_id_type DEFAULT NULL)

Deletes a record.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3) Required Key
	p_vtyp_code	The type of Visa issued. VARCHAR2(2) Required Key
	p_rowid	Database ROWID of the record to be deleted. VARCHAR2(18)

p_lock

PROCEDURE p_lock(p_pidm        gorvisa.gorvisa_pidm%TYPE,
                 p_seq_no      gorvisa.gorvisa_seq_no%TYPE,
                 p_vtyp_code   gorvisa.gorvisa_vtyp_code%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_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3) Required Key
	p_vtyp_code	The type of Visa issued. VARCHAR2(2) Required Key
	p_rowid_inout	Database ROWID of the record to be locked. VARCHAR2(18) Required

p_update

PROCEDURE p_update(p_pidm             gorvisa.gorvisa_pidm%TYPE,
                   p_seq_no           gorvisa.gorvisa_seq_no%TYPE,
                   p_vtyp_code        gorvisa.gorvisa_vtyp_code%TYPE,
                   p_visa_number      gorvisa.gorvisa_visa_number%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_natn_code_issue  gorvisa.gorvisa_natn_code_issue%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_viss_code        gorvisa.gorvisa_viss_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_visa_start_date  gorvisa.gorvisa_visa_start_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_visa_expire_date gorvisa.gorvisa_visa_expire_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_entry_ind        gorvisa.gorvisa_entry_ind%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_user_id          gorvisa.gorvisa_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_visa_req_date    gorvisa.gorvisa_visa_req_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_visa_issue_date  gorvisa.gorvisa_visa_issue_date%TYPE DEFAULT dml_common.f_unspecified_date,
                   p_pent_code        gorvisa.gorvisa_pent_code%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_no_entries       gorvisa.gorvisa_no_entries%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_data_origin      gorvisa.gorvisa_data_origin%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid            gb_common.internal_record_id_type DEFAULT NULL)

Updates a record.

Parameters

	p_pidm	Internal identification number. NUMBER(8) Required Key
	p_seq_no	A record sequence number. NUMBER(3) Required Key
	p_vtyp_code	The type of Visa issued. VARCHAR2(2) Required Key
	p_visa_number	A unique identification number assigned to a Visa. VARCHAR2(18)
	p_natn_code_issue	The country code of the nation issuing the Visa. VARCHAR2(5)
	p_viss_code	The agency authorizing the Visa. VARCHAR2(6)
	p_visa_start_date	The date the Visa begins. DATE
	p_visa_expire_date	The date the Visa expires. DATE
	p_entry_ind	This field indicates whether the Visa is for entry into the country. VARCHAR2(1)
	p_user_id	The Oracle ID of the user who changed the record. VARCHAR2(30) Required
	p_visa_req_date	The date the Visa was requested. DATE
	p_visa_issue_date	The date the Visa was Issued. DATE
	p_pent_code	The port of entry into the country. VARCHAR2(3)
	p_no_entries	The number of entries into the country. VARCHAR2(2)
	p_data_origin	Source system that created or updated the row. VARCHAR2(30)
	p_rowid	Database ROWID of the record to be updated. VARCHAR2(18)