Oracle Form Data Entry Sample
Example is given for an emp table and other related tables. You can download Emp.fmb form from Google drive by clicking here Emp.Fmb Form
To run this form you would require some tables in Payroll schema, so create a user/schema with name Payroll and run the following script:
— start
CREATE TABLE DESIG
(
DNAME VARCHAR2(60 BYTE)
);
CREATE TABLE DEPT
(
DNAME VARCHAR2(60 BYTE)
)
;
CREATE TABLE EMP
(
ECODE VARCHAR2(10 BYTE),
ENAME VARCHAR2(60 BYTE),
DESIG VARCHAR2(60 BYTE),
DEPT VARCHAR2(60 BYTE),
ADDR1 VARCHAR2(60 BYTE),
ADDR2 VARCHAR2(60 BYTE),
CITY VARCHAR2(30 BYTE),
STATE VARCHAR2(30 BYTE),
PINCODE VARCHAR2(6 BYTE),
PHONE VARCHAR2(30 BYTE),
DOJ DATE,
DOR DATE,
DOP DATE,
PFNO VARCHAR2(30 BYTE),
ESINO VARCHAR2(60 BYTE),
BASIC NUMBER(10) DEFAULT 0,
HRA NUMBER(10) DEFAULT 0,
CONV NUMBER(10) DEFAULT 0,
MEDC NUMBER(10) DEFAULT 0,
BONUS NUMBER(10) DEFAULT 0,
TEL NUMBER(10) DEFAULT 0,
OTHA NUMBER(10) DEFAULT 0,
CL NUMBER(3) DEFAULT 0,
EL NUMBER(5) DEFAULT 0,
SL NUMBER(3) DEFAULT 0,
PFA NUMBER(10) DEFAULT 0,
TDS NUMBER(10) DEFAULT 0,
TDSMB NUMBER(2) DEFAULT 0,
ADVTOTAL NUMBER(10) DEFAULT 0,
ADVDED NUMBER(10) DEFAULT 0,
BANKAC VARCHAR2(50 BYTE),
FY VARCHAR2(9 BYTE),
CP VARCHAR2(30 BYTE),
FYCP VARCHAR2(40 BYTE),
ESI NUMBER(10,2),
TDSDED NUMBER(10,2)
)
;
CREATE TABLE FYCPT
(
FY VARCHAR2(9 BYTE),
CP VARCHAR2(30 BYTE),
FYCP VARCHAR2(40 BYTE)
)
;
CREATE TABLE MNTRANS
(
TRANID NUMBER(10),
TRANDATE DATE,
ECODE VARCHAR2(10 BYTE),
ENAME VARCHAR2(60 BYTE),
NOOFABS NUMBER(3),
NOOFWD NUMBER(3),
NETBASIC NUMBER(10,2),
NETCONV NUMBER(10,2),
NETMED NUMBER(10,2),
NETBONUS NUMBER(10,2),
NETTEL NUMBER(10,2),
NETHRA NUMBER(10),
OT_HRS NUMBER(10),
OT_HRSAMT NUMBER(10,2),
OT_SAL NUMBER(10,2),
OTHER_A NUMBER(10),
GRS_SAL NUMBER(10,2),
PFA NUMBER(10),
ESIA NUMBER(10),
TDS NUMBER(10,2),
ADVI NUMBER(10),
OTH_DED NUMBER(10),
NETPAYABLE NUMBER(10,2),
CLT NUMBER(2),
ELT NUMBER(2),
SLT NUMBER(2),
TL NUMBER(2),
CAA VARCHAR2(1 BYTE),
FY VARCHAR2(9 BYTE),
CP VARCHAR2(30 BYTE),
FYCP VARCHAR2(40 BYTE),
FOR_MNTH VARCHAR2(20 BYTE),
FOR_YEAR NUMBER(4)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
SET DEFINE OFF;
Insert into DEPT
(DNAME)
Values
(‘COMMERCIAL’);
Insert into DEPT
(DNAME)
Values
(‘MAINT & SUPPORT’);
Insert into DEPT
(DNAME)
Values
(‘SYSTEMS’);
Insert into DEPT
(DNAME)
Values
(‘OPERATIONS’);
Insert into DEPT
(DNAME)
Values
(‘QUALITY CONTROL’);
Insert into DEPT
(DNAME)
Values
(‘ACCOUNTS’);
Insert into DEPT
(DNAME)
Values
(‘PACKING & DISPATCH’);
Insert into DEPT
(DNAME)
Values
(‘ADMINISTRATION’);
Insert into DEPT
(DNAME)
Values
(‘STORE’);
Insert into DEPT
(DNAME)
Values
(‘MAINTENANCE’);
Insert into DEPT
(DNAME)
Values
(‘DESIGNING’);
Insert into DEPT
(DNAME)
Values
(‘PURCHASE & STORE’);
COMMIT;
SET DEFINE OFF;
Insert into DESIG
(DNAME)
Values
(‘AGM’);
Insert into DESIG
(DNAME)
Values
(‘CHIEF MANAGER’);
Insert into DESIG
(DNAME)
Values
(‘SR. MANAGER’);
Insert into DESIG
(DNAME)
Values
(‘MANAGER’);
Insert into DESIG
(DNAME)
Values
(‘SCY. TO CMD & COMPLIANCE OFFICER’);
Insert into DESIG
(DNAME)
Values
(‘DY. MANAGER’);
Insert into DESIG
(DNAME)
Values
(‘ASST. MANAGER’);
Insert into DESIG
(DNAME)
Values
(‘SR. EXECUTIVE’);
Insert into DESIG
(DNAME)
Values
(‘EXECUTIVE’);
Insert into DESIG
(DNAME)
Values
(‘OMR OPERATOR’);
Insert into DESIG
(DNAME)
Values
(‘SR. ASSISTANT’);
Insert into DESIG
(DNAME)
Values
(‘ASSISTANT’);
Insert into DESIG
(DNAME)
Values
(‘MANAGER SYSTEMS’);
COMMIT;
SET DEFINE OFF;
Insert into EMP
(ECODE, ENAME, DESIG, DEPT, ADDR1,
ADDR2, CITY, STATE, PINCODE, PHONE,
DOJ, DOR, DOP, PFNO, ESINO,
BASIC, HRA, CONV, MEDC, BONUS,
TEL, OTHA, CL, EL, SL,
PFA, TDS, TDSMB, ADVTOTAL, ADVDED,
BANKAC, FY, CP, FYCP, ESI,
TDSDED)
Values
(‘1000’, ‘ABC’, ‘xyz’, ‘abc’, NULL,
NULL, ‘NEW DELHI’, ‘DELHI’, ‘110020’, NULL,
TO_DATE(’08/01/2010 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), NULL, NULL, ‘12345’, ‘654123’,
8000, 2500, 500, 1000, 5000,
500, 250, 7, 31, 8,
780, 1000, 5, 10000, 500,
‘254252525632548’, ‘2010-2011’, ‘LTD.’, ‘2010-2011 INTERNATIONAL LTD.’, 210,
NULL);
Insert into EMP
(ECODE, ENAME, DESIG, DEPT, ADDR1,
ADDR2, CITY, STATE, PINCODE, PHONE,
DOJ, DOR, DOP, PFNO, ESINO,
BASIC, HRA, CONV, MEDC, BONUS,
TEL, OTHA, CL, EL, SL,
PFA, TDS, TDSMB, ADVTOTAL, ADVDED,
BANKAC, FY, CP, FYCP, ESI,
TDSDED)
Values
(’93’, ‘VINISH KAPOOR’, ‘MANAGER SYSTEMS’, ‘OPERATIONS’, ‘MARIYAM NAGAR’,
NULL, ‘GHAZIABAD’, ‘UP’, ‘110020’, ‘9540377373’,
TO_DATE(’01/10/2011 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), NULL, NULL, NULL, NULL,
10000, 4000, 1000, 1000, 5000,
1000, 2000, 7, 31, 8,
780, 2000, 2, 50000, 1000,
NULL, ‘2010-2011’, ‘INTERNATIONAL LTD.’, ‘2010-2011 INTERNATIONAL LTD.’, 0,
NULL);
Insert into EMP
(ECODE, ENAME, DESIG, DEPT, ADDR1,
ADDR2, CITY, STATE, PINCODE, PHONE,
DOJ, DOR, DOP, PFNO, ESINO,
BASIC, HRA, CONV, MEDC, BONUS,
TEL, OTHA, CL, EL, SL,
PFA, TDS, TDSMB, ADVTOTAL, ADVDED,
BANKAC, FY, CP, FYCP, ESI,
TDSDED)
Values
(‘93993’, ‘VIN’, ‘AGM’, ‘ACCOUNTS’, NULL,
NULL, ‘NEW DELHI’, ‘DELHI’, ‘110020’, NULL,
TO_DATE(’01/01/2000 00:00:00′, ‘MM/DD/YYYY HH24:MI:SS’), NULL, NULL, NULL, NULL,
33333, 0, 0, 0, 0,
0, 0, 7, 31, 8,
780, NULL, 1, NULL, NULL,
NULL, ‘FY’, ‘FY’, ‘FY’, 0,
NULL);
COMMIT;
SET DEFINE OFF;
Insert into FYCPT
(FY, CP, FYCP)
Values
(‘2010-2011’, ‘INTERNATIONAL LTD.’, ‘2010-2011 INTERNATIONAL LTD.’);
COMMIT;
— end
