Package gb_vbs_predicate

This package provides the Common Business interface for vbs_predicate

It supports the FGAC VBS Predicate Rules Table (GORFPRD).

The FGAC VBS Predicate Rules define the WHERE clause that will be executed for the associated domain (business function area) and Group Rule as defined on the Table GOBFGAC.
Each domain is associated with a valid SQL statement. Multiple domains per group are permitted, as well as multiple predicates per domain.
Multiple predicates are each assigned a unique sequence number. If multiple predicate rules are specified for the same FGAC Rule Group, the predicates will be treated as AND conditions by FGAC processing. Predicate rules that apply to the same business profile/users for different Rule Groups will be treated as OR conditions by FGAC processing. Only VBS domains may have VBS predicate rules.

Program units

f_api_version	Returns the API version number.
f_exists	Checks to see if a predicate record exists.
f_isequal	Compare two predicate records for equality.
f_query_all	Selects all predicate records for the entity.
f_query_one	Selects one predicate record using key.
f_query_by_rowid	Selects one predicate record using ROWID.
f_query_one_lock	Selects one predicate record and locks it.
p_create	Creates a predicate record.
p_delete	Deletes a predicate record.
p_lock	Locks a predicate record.
p_update	Updates a predicate record.

Types

vbs_predicate_rec	Entity record type.
vbs_predicate_ref	Entity cursor variable type.
vbs_predicate_tab	Entity table type.

Constants

M_ENTITY_NAME

Business Entity name.

M_ENTITY_NAME

M_ENTITY_NAME    CONSTANT VARCHAR2(14) := 'VBS_PREDICATE';

Business Entity name.

vbs_predicate_rec

TYPE vbs_predicate_rec IS RECORD (
   r_fgac_code               gorfprd.gorfprd_fgac_code%TYPE,
   r_fdmn_code               gorfprd.gorfprd_fdmn_code%TYPE,
   r_seqno                   gorfprd.gorfprd_seqno%TYPE,
   r_user_id                 gorfprd.gorfprd_user_id%TYPE,
   r_predicate               gorfprd.gorfprd_predicate%TYPE,
   r_internal_record_id      gb_common.internal_record_id_type);

Entity record type.

vbs_predicate_ref

TYPE vbs_predicate_ref IS REF CURSOR RETURN vbs_predicate_rec;

Entity cursor variable type.

vbs_predicate_tab

TYPE vbs_predicate_tab IS TABLE OF vbs_predicate_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. It changes only when the signature changes.

f_exists

FUNCTION f_exists(p_fgac_code gorfprd.gorfprd_fgac_code%TYPE,
                  p_fdmn_code gorfprd.gorfprd_fdmn_code%TYPE DEFAULT NULL,
                  p_seqno     gorfprd.gorfprd_seqno%TYPE DEFAULT NULL,
                  p_rowid     VARCHAR2 DEFAULT NULL) RETURN VARCHAR2

Checks to see if a predicate record exists.

Parameters

	p_fgac_code	FGAC group code. Required.
	p_fdmn_code	FGAC domain code. Optional.
	p_seqno	One-up number that uniquely identifies each predicate if there are multiple predicates per domain and rule. Optional.
	p_rowid	Database ROWID of record to select. Optional.

Returns

Y if found, otherwise N.

f_isequal

FUNCTION f_isequal(rec_one vbs_predicate_rec,
                   rec_two vbs_predicate_rec) RETURN VARCHAR2

Compare two predicate records for equality.

Returns

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

f_query_all

FUNCTION f_query_all(p_fgac_code gorfprd.gorfprd_fgac_code%TYPE,
                     p_fdmn_code gorfprd.gorfprd_fdmn_code%TYPE DEFAULT NULL,
                     p_seqno     gorfprd.gorfprd_seqno%TYPE DEFAULT NULL)
  RETURN vbs_predicate_ref

Selects all predicate records for the entity.

Parameters

	p_fgac_code	FGAC group code. Required.
	p_fdmn_code	FGAC domain code. Optional.
	p_seqno	One-up number that uniquely identifies each predicate if there are multiple predicates per domain and rule. Optional.

Returns

A cursor variable that will fetch the set of records.

f_query_one

FUNCTION f_query_one(p_fgac_code gorfprd.gorfprd_fgac_code%TYPE,
                     p_fdmn_code gorfprd.gorfprd_fdmn_code%TYPE,
                     p_seqno     gorfprd.gorfprd_seqno%TYPE)
  RETURN vbs_predicate_ref

Selects one predicate record using key.

Parameters

	p_fgac_code	FGAC group code. Required.
	p_fdmn_code	FGAC domain code. Required.
	p_seqno	One-up number that uniquely identifies each predicate if there are multiple predicates per domain and rule. Required.

Returns

A cursor variable that will fetch exactly one record.

f_query_by_rowid

FUNCTION f_query_by_rowid(p_rowid VARCHAR2) RETURN vbs_predicate_ref

Selects one predicate record using ROWID.

Parameters

p_rowid

Database ROWID of the record to be selected. Required.

Returns

A cursor variable that will fetch exactly one record.

f_query_one_lock

FUNCTION f_query_one_lock(p_fgac_code gorfprd.gorfprd_fgac_code%TYPE,
                          p_fdmn_code gorfprd.gorfprd_fdmn_code%TYPE,
                          p_seqno     gorfprd.gorfprd_seqno%TYPE,
                          p_rowid     VARCHAR2 DEFAULT NULL)
  RETURN vbs_predicate_ref

Selects one predicate record and locks it.

Parameters

	p_fgac_code	FGAC group code. Required.
	p_fdmn_code	FGAC domain code. Required.
	p_seqno	One-up number that uniquely identifies each predicate if there are multiple predicates per domain and rule. Required.
	p_rowid	Database ROWID of the record to be selected. Optional.

Returns

A cursor variable for one record and locks the record.

p_create

PROCEDURE p_create(p_fgac_code gorfprd.gorfprd_fgac_code%TYPE,
                   p_fdmn_code gorfprd.gorfprd_fdmn_code%TYPE,
                   p_seqno     gorfprd.gorfprd_seqno%TYPE,
                   p_user_id   gorfprd.gorfprd_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_predicate gorfprd.gorfprd_predicate%TYPE DEFAULT NULL,
                   p_rowid_out OUT VARCHAR2)

Creates a predicate record.

Only VBS domains may have VBS predicate rules.

Parameters

	p_fgac_code	FGAC group code. Required.
	p_fdmn_code	FGAC domain code. Required.
	p_seqno	One-up number that uniquely identifies each predicate if there are multiple predicates per domain and rule. Required.
	p_user_id	Oracle User ID of the user who created or last updated a record. Optional.
	p_predicate	SQL statement that will be appended to originating SQL to restrict row level access. The predicate is processed by ORACLE FGAC and appended appropriately. The driver table for the predicate is the GOBFDMN_TABLE_NAME for the GORFPRD_FDMN_CODE value. Optional.
	p_rowid_out	Database ROWID of the record to be created.

p_delete

PROCEDURE p_delete(p_fgac_code gorfprd.gorfprd_fgac_code%TYPE,
                   p_fdmn_code gorfprd.gorfprd_fdmn_code%TYPE,
                   p_seqno     gorfprd.gorfprd_seqno%TYPE,
                   p_rowid     VARCHAR2 DEFAULT NULL)

Deletes a predicate record.

Record must exist to be deleted. This procedure will also delete children records if they exist on the GORFGBP and GORFGUS tables.

Parameters

	p_fgac_code	FGAC group code. Required.
	p_fdmn_code	FGAC domain code. Required.
	p_seqno	One-up number that uniquely identifies each predicate if there are multiple predicates per domain and rule. Required.
	p_rowid	Database rowid of record to delete. Optional.

p_lock

PROCEDURE p_lock(p_fgac_code   gorfprd.gorfprd_fgac_code%TYPE,
                 p_fdmn_code   gorfprd.gorfprd_fdmn_code%TYPE,
                 p_seqno       gorfprd.gorfprd_seqno%TYPE,
                 p_rowid_inout IN OUT VARCHAR2)

Locks a predicate record.

If ROWID is not passed in, the predicate record is located using the key values, and the ROWID of the locked row is passed in p_rowid_inout.

Parameters

	p_fgac_code	FGAC group code. Required.
	p_fdmn_code	FGAC domain code. Required.
	p_seqno	One-up number that uniquely identifies each predicate if there are multiple predicates per domain and rule. Required.
	p_rowid_inout	Database ROWID of record to be locked. Required.

p_update

PROCEDURE p_update(p_fgac_code gorfprd.gorfprd_fgac_code%TYPE,
                   p_fdmn_code gorfprd.gorfprd_fdmn_code%TYPE,
                   p_seqno     gorfprd.gorfprd_seqno%TYPE,
                   p_user_id   gorfprd.gorfprd_user_id%TYPE DEFAULT gb_common.f_sct_user,
                   p_predicate gorfprd.gorfprd_predicate%TYPE DEFAULT dml_common.f_unspecified_string,
                   p_rowid     VARCHAR2 DEFAULT NULL)

Updates a predicate record.

The Predicate and Oracle User ID may be updated.

Parameters

	p_fgac_code	FGAC group code. Required.
	p_fdmn_code	FGAC domain code. Required.
	p_seqno	One-up number that uniquely identifies each predicate if there are multiple predicates per domain and rule. Required.
	p_user_id	Oracle User ID of the user who created or last updated a record. Optional.
	p_predicate	SQL statement that will be appended to the originating SQL to restrict row level access. The predicate is processed by ORACLE FGAC and appended appropriately. The driver table for the predicate is the GOBFDMN_TABLE_NAME for the GORFPRD_FDMN_CODE value. Optional.
	p_rowid	Database ROWID of the record to be updated. Optional.