General Contacts for all tables

-- when designing application you will face the case that the customers, suppliers or employees that have a set of similar attiributes but repeated at unknown frequency ,

example,
you may have more than phone number or more than email address of on entity , according to normalization rules and the business logic the following design is not valid:

customer -- customer_id,customer_name,phone1,phone2,phone3

--- instead of this you can locate the set of phone number in another entity, so you can enter unlimited number of phones of the customer

----------------------------------------------------------
----- ----------contact object group ----------------------
1- create the followinng tables


CREATE TABLE GN_CONTACTS
(
CONTACT_RECID NUMBER(16),
TABLE_RECID NUMBER(16) NOT NULL,
TABLE_NAME VARCHAR2(50 BYTE) NOT NULL,
CONTACT_WAY_RECID NUMBER(16) NOT NULL,
CONTACT_DESC VARCHAR2(50 BYTE),
CONTACT_NOTES VARCHAR2(200 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE GN_CONTACT_WAY
(
CONTACT_WAY_RECID NUMBER,
CONTACT_WAY_DESC VARCHAR2(50 BYTE) NOT NULL
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX PK_CONTACT_RECID ON GN_CONTACTS
(CONTACT_RECID)
LOGGING
NOPARALLEL;

CREATE UNIQUE INDEX PK_CONTACT_WAY ON GN_CONTACT_WAY
(CONTACT_WAY_RECID)
LOGGING
NOPARALLEL;

CREATE OR REPLACE TRIGGER TRG_CONTACTS
BEFORE INSERT
ON GN_CONTACTS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RECID NUMBER;
BEGIN
SELECT SEQ_CONTACTS.NEXTVAL INTO V_RECID FROM DUAL ;
:NEW.CONTACT_RECID := V_RECID;
END TRG_CONTACTS ;
/
SHOW ERRORS;


CREATE OR REPLACE TRIGGER TRG_CONTACT_WAY_RECID
BEFORE INSERT
ON GN_CONTACT_WAY
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RECID NUMBER;
BEGIN
SELECT SEQ_contact_way.NEXTVAL INTO V_RECID FROM DUAL ;
:NEW.contact_way_recid := V_RECID;
END trg_contact_way_recid;
/
SHOW ERRORS;


ALTER TABLE GN_CONTACTS ADD (
CONSTRAINT PK_CONTACT_RECID PRIMARY KEY (CONTACT_RECID));

ALTER TABLE GN_CONTACT_WAY ADD (
CONSTRAINT PK_CONTACT_WAY PRIMARY KEY (CONTACT_WAY_RECID));

2- after that you can create an object group ( to use globally ) the object group should contain the following :

-canvas

-window

-backage

-two block (database block gn_contacts and control block)

-visual attribute [optional]

the contacts_pkg :

PACKAGE contact_pkg IS
procedure go_contact(p_view_only number default 0);

function get_block_table_name(V_BLOCK_NAME VARCHAR2)return varchar2;

PROCEDURE SUPPORT_CONTACTS(P_BLOCK_NAME VARCHAR2,P_PRIMARY_KEY_ITEM VARCHAR2);

PROCEDURE INSERT_CONTACTS;

END;

PACKAGE BODY contact_pkg IS

----------------------------------------------------------------------------------------------
PROCEDURE INSERT_CONTACTS IS ---- TO BE CALLED IN THE PRE INSERT OF CONTACT TABLE
v_pk_col varchar2(50);

v_value varchar2(50);

BEGIN
:TABLE_NAME:=NAME_IN('CONTACT_TABLE_NAME');
:table_recid:=NAME_IN(NAME_IN('CONTACT_PRIMARY_KEY'));
END ;

----------------------------------------------------------------------------------------------
PROCEDURE SUPPORT_CONTACTS(P_BLOCK_NAME VARCHAR2,P_PRIMARY_KEY_ITEM VARCHAR2) IS --- TO BE CALLED AFTER NEW FORM INSTANCE
BEGIN
set_item_property('GN_CONTACTS.CONTACT_WAY_RECID',required,property_false);

P_POPULATE_LIST('GN_CONTACTs.CONTACT_WAY_RECID','SELECT CONTACT_WAY_DESC ,TO_CHAR(CONTACT_WAY_RECID) FROM GN_CONTACT_WAY');

set_item_property('GN_CONTACTs.CONTACT_WAY_RECID',required,property_TRUE);
:CONTACT_BLOCK_NAME:=P_BLOCK_NAME;

:CONTACT_TABLE_NAME:=GET_BLOCK_TABLE_NAME(P_BLOCK_NAME);

:CONTACT_PRIMARY_KEY:=P_PRIMARY_KEY_ITEM;

END ;
---------------------------------------------------------------------------------------------function get_block_table_name(v_block_name varchar2)return varchar2 is
v_return varchar2(100);

begin

v_return:=get_block_property(v_block_name,query_data_source_name);
return(v_return);end ;
-----------------------------------------------------------------------------------------------PROCEDURE go_contact(p_view_only number default 0) IS -- IN THE CONTACT BUTTON
BEGIN
if p_view_only=1 then

set_block_property('gn_contacts',insert_allowed,property_false);

set_block_property('gn_contacts',update_allowed,property_false);

set_block_property('gn_contacts',delete_allowed,property_false); end if ;
:PK_VALUE:=TO_NUMBER(NAME_IN(NAME_IN('CONTACT_PRIMARY_KEY')));
go_block('GN_contacts');

execute_query;
END;

-------------------------------------------------------------------------------------------------- END;

3- in the when new form instance intialize the block name and its primary key column by invoking the following procedure
CONTACTS.SUPPORT_CONTACTS(P_BLOCK_NAME VARCHAR2,P_PRIMARY_KEY_ITEM VARCHAR2);

4- create a boutton that call the contacts

contact_pkg.go_contacts(p_view_only default 0);

5- note that the data in the contact_way table is system defined (email,fax,phone,website)
also you can add to it if required
6-also you can validate the data that entered in contacts according to its type