EBS FORM开发全过程(2)
1.基于EBS的Forms开发过程
本小节的脚本在PL/SQL Developer中，用APPS登录，在Command Window中运行
1.1创建数据库对象
创建相关基表
点击查看代码
-- Create table
create table SECOM.DEPARTMENTS
(
DEPARTMENT_ID NUMBER(4) not null,
DEPARTMENT_NAME VARCHAR2(30) not null,
MANAGER_ID NUMBER(6),
LOCATION_ID NUMBER(4)
);
insert into SECOM.departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (10, 'Administration', 200, 1700);
insert into SECOM.departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (20, 'Marketing', 201, 1800);
insert into SECOM.departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (50, 'Shipping', 124, 1500);
insert into SECOM.departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (60, 'IT', 103, 1400);
insert into SECOM.departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (80, 'Sales', 149, 2500);
insert into SECOM.departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (90, 'Executive', 100, 1700);
insert into SECOM.departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (110, 'Accounting', 205, 1700);
insert into SECOM.departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
values (190, 'Contracting', null, 1700);
commit;
select * from SECOM.departments;
-- Create table
create table SECOM.employees
(
EMPLOYEE_ID NUMBER(6) not null,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 2600.00, null, 124, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 2600.00, null, 124, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'MK_MAN', 13000.00, null, 100, 20);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'MK_REP', 6000.00, null, 201, 20);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'HR_REP', 6500.00, null, 101, 40);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'PR_REP', 10000.00, null, 101, 70);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'AC_MGR', 12000.00, null, 101, 110);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SY_ANAL', 8300.00, null, 205, 110);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (100, 'Steven', 'King', 'SKING', '515.123.4567', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'AD_PRES', 24000.00, null, null, 90);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'AD_VP', 17000.00, null, 100, 90);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'AD_VP', 17000.00, null, 100, 90);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'IT_PROG', 9000.00, null, 102, 60);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'IT_PROG', 6000.00, null, 103, 60);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'IT_PROG', 4800.00, null, 103, 60);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SY_ANAL', 4800.00, null, 103, 60);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'IT_PROG', 4200.00, null, 103, 60);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'FI_MGR', 12000.00, null, 101, 100);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'FI_ACCOUNT', 9000.00, null, 108, 100);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (110, 'John', 'Chen', 'JCHEN', '515.124.4269', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'FI_ACCOUNT', 8200.00, null, 108, 100);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'FI_ACCOUNT', 7700.00, null, 108, 100);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'FI_ACCOUNT', 7800.00, null, 108, 100);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'FI_ACCOUNT', 6900.00, null, 108, 100);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'PU_MAN', 11000.00, null, 100, 30);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'PU_CLERK', 3100.00, null, 114, 30);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'PU_CLERK', 2900.00, null, 114, 30);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'PU_CLERK', 2800.00, null, 114, 30);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'PU_CLERK', 2600.00, null, 114, 30);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'PU_CLERK', 2500.00, null, 114, 30);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_MAN', 8000.00, null, 100, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_MAN', 8200.00, null, 100, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_MAN', 7900.00, null, 100, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_MAN', 6500.00, null, 100, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_MAN', 5800.00, null, 100, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 3200.00, null, 120, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2700.00, null, 120, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2400.00, null, 120, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2200.00, null, 120, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 3300.00, null, 121, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2800.00, null, 121, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2500.00, null, 121, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2100.00, null, 121, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 3300.00, null, 122, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2900.00, null, 122, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2400.00, null, 122, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 3200.00, null, 122, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 3600.00, null, 123, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 3200.00, null, 123, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (139, 'John', 'Seo', 'JSEO', '650.121.2019', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2700.00, null, 123, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2500.00, null, 123, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 3500.00, null, 124, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 3100.00, null, 124, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2600.00, null, 124, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'ST_CLERK', 2500.00, null, 124, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_MAN', 14000.00, 0.40, 100, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_MAN', 13500.00, 0.30, 100, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_MAN', 12000.00, 0.30, 100, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_MAN', 11000.00, 0.30, 100, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_MAN', 10500.00, 0.20, 100, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 10000.00, 0.30, 145, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 9500.00, 0.25, 145, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 9000.00, 0.25, 145, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 8000.00, 0.20, 145, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 7500.00, 0.20, 145, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 7000.00, 0.15, 145, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (156, 'Janette', 'King', 'JKING', '011.44.1345.429268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 10000.00, 0.35, 146, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 9500.00, 0.35, 146, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 9000.00, 0.35, 146, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 8000.00, 0.30, 146, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 7500.00, 0.30, 146, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', to_date('17-03-1990 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 7000.00, 0.25, 146, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', to_date('17-03-1991 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 10500.00, 0.25, 147, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 9500.00, 0.15, 147, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 7200.00, 0.10, 147, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 6800.00, 0.10, 147, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 6400.00, 0.10, 147, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 6200.00, 0.10, 147, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 11500.00, 0.25, 148, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 10000.00, 0.20, 148, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 9600.00, 0.20, 148, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 7400.00, 0.15, 148, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 7300.00, 0.15, 148, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 6100.00, 0.10, 148, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 11000.00, 0.30, 149, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 8800.00, 0.25, 149, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 8600.00, 0.20, 149, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 8400.00, 0.20, 149, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 7000.00, 0.15, 149, 10);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SA_REP', 6200.00, 0.10, 149, 80);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3200.00, null, 120, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3100.00, null, 120, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 2500.00, null, 120, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 2800.00, null, 120, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 4200.00, null, 121, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 4100.00, null, 121, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3400.00, null, 121, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3000.00, null, 121, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3800.00, null, 122, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3600.00, null, 122, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 2900.00, null, 122, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 2500.00, null, 122, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 4000.00, null, 123, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3900.00, null, 123, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3200.00, null, 123, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 2800.00, null, 123, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3100.00, null, 124, 50);
insert into SECOM.employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
values (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', to_date('17-03-2013 10:46:48', 'dd-mm-yyyy hh24:mi:ss'), 'SH_CLERK', 3000.00, null, 124, 50);
commit;
select * from SECOM.employees;
-- Create table
create table SECOM.JOBS
(
JOB_ID VARCHAR2(10) not null,
JOB_TITLE VARCHAR2(35),
MIN_SALARY NUMBER(6),
MAX_SALARY NUMBER(6)
);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('AD_PRES', 'President', 20000, 40000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('AD_VP', 'Administration Vice President', 15000, 30000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('AD_ASST', 'Administration Assistant', 3000, 6000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('FI_MGR', 'Finance Manager', 8200, 16000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('FI_ACCOUNT', 'Accountant', 4200, 9000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('AC_MGR', 'Accounting Manager', 8200, 16000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('AC_ACCOUNT', 'Public Accountant', 4200, 9000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('SA_MAN', 'Sales Manager', 10000, 20000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('SA_REP', 'Sales Representative', 6000, 12000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('PU_MAN', 'Purchasing Manager', 8000, 15000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('PU_CLERK', 'Purchasing Clerk', 2500, 5500);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('ST_MAN', 'Stock Manager', 5500, 8500);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('ST_CLERK', 'Stock Clerk', 2000, 5000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('SH_CLERK', 'Shipping Clerk', 2500, 5500);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('IT_PROG', 'Programmer', 4000, 10000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('MK_MAN', 'Marketing Manager', 9000, 15000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('MK_REP', 'Marketing Representative', 4000, 9000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('HR_REP', 'Human Resources Representative', 4000, 9000);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('PR_REP', 'Public Relations Representative', 4500, 10500);
insert into SECOM.JOBS (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
values ('SY_ANAL', 'System Analyst', 6000, 12000);
commit;
select * from SECOM.JOBS;
-- Create table
create table SECOM.locations
(
LOCATION_ID NUMBER(4) not null,
STREET_ADDRESS VARCHAR2(40),
POSTAL_CODE VARCHAR2(12),
CITY VARCHAR2(30),
STATE_PROVINCE VARCHAR2(25),
COUNTRY_ID CHAR(2)
);
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1000, '1297 Via Cola di Rie', '00989', 'Roma', '', 'IT');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1100, '93091 Calle della Testa', '10934', 'Venice', '', 'IT');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', '', 'JP');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2000, '40-5-12 Laogianggen', '190518', 'Beijing', '', 'CN');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2300, '198 Clementi North', '540198', 'Singapore', '', 'SG');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2400, '8204 Arthur St', '', 'London', '', 'UK');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2800, 'Rua Frei Caneca 1360 ', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL');
insert into SECOM.locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
values (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');
commit;
select * from SECOM.locations;
-- Create table
create table SECOM.REGIONS
(
REGION_ID NUMBER,
REGION_NAME VARCHAR2(25)
);
insert into SECOM.regions (REGION_ID, REGION_NAME)
values (1, 'Europe');
insert into SECOM.regions (REGION_ID, REGION_NAME)
values (2, 'Americas');
insert into SECOM.regions (REGION_ID, REGION_NAME)
values (3, 'Asia');
insert into SECOM.regions (REGION_ID, REGION_NAME)
values (4, 'Middle East and Africa');
commit;
select * from SECOM.regions;
创建序列
CREATE SEQUENCE SECOM.DEPARTMENTS_S START WITH 200;
CREATE SEQUENCE SECOM.EMPLOYEES_S START WITH 10001;
commit;
创建视图
(必需)包含主TABLE的所有列，包含row_id
包括外键关联的列
包括ROW_ID
--在apps下创建VIEW
--cux_departments_v
CREATE OR REPLACE VIEW cux_departments_v AS
SELECT dep.ROWID row_id,
dep.department_id,
dep.department_name,
dep.manager_id,
dep.location_id,
m.first_name || ',' || m.last_name manager_name,
loc.street_address,
loc.city
FROM SECOM.departments dep, SECOM.employees m, SECOM.locations loc
WHERE dep.manager_id = m.employee_id(+)
AND dep.location_id = loc.location_id(+);
select * from cux_departments_v;
--在apps下创建VIEW
--cux_employees_v
CREATE OR REPLACE VIEW cux_employees_v AS
SELECT emp.ROWID row_id,
emp.employee_id,
emp.first_name,
emp.last_name,
emp.email,
emp.phone_number,
emp.hire_date,
emp.job_id,
emp.salary,
emp.commission_pct,
emp.manager_id,
emp.department_id,
m.first_name || ',' || m.last_name manager_name,
job.job_title
FROM SECOM.employees emp, SECOM.employees m, SECOM.jobs job
WHERE emp.manager_id = m.employee_id(+)
AND emp.job_id = job.job_id;
头表增删改查
CREATE OR REPLACE PACKAGE departments_pkg IS
/*=====================================
** PROCEDURE: insert_row()
**=====================================*/
PROCEDURE insert_row(x_row_id IN OUT VARCHAR2,
x_department_id IN OUT NUMBER,
p_department_name IN VARCHAR2,
p_manager_id IN NUMBER DEFAULT NULL,
p_location_id IN NUMBER DEFAULT NULL);
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row(p_department_id IN NUMBER,
p_department_name IN VARCHAR2,
p_manager_id IN NUMBER,
p_location_id IN NUMBER);
/*=====================================
** PROCEDURE: update_row()
**=====================================*/
PROCEDURE update_row(p_department_id IN NUMBER,
p_department_name IN VARCHAR2,
p_manager_id IN NUMBER DEFAULT NULL,
p_location_id IN NUMBER DEFAULT NULL);
/*=====================================
** PROCEDURE: delete_row()
**=====================================*/
PROCEDURE delete_row(p_department_id IN NUMBER);
END departments_pkg;
CREATE OR REPLACE PACKAGE BODY departments_pkg IS
/*=====================================
** PROCEDURE: insert_row()
**=====================================*/
PROCEDURE insert_row(x_row_id IN OUT VARCHAR2,
x_department_id IN OUT NUMBER,
p_department_name IN VARCHAR2,
p_manager_id IN NUMBER DEFAULT NULL,
p_location_id IN NUMBER DEFAULT NULL)
IS
CURSOR c IS
SELECT ROWID
FROM secom.departments
WHERE department_id = x_department_id;
BEGIN
IF x_department_id IS NULL THEN
SELECT secom.departments_s.nextval INTO x_department_id FROM dual;
END IF;
INSERT INTO secom.departments
(department_id, department_name, manager_id, location_id)
VALUES
(x_department_id, p_department_name, p_manager_id, p_location_id);
OPEN c;
FETCH c
INTO x_row_id;
IF (c%NOTFOUND) THEN
CLOSE c;
RAISE no_data_found;
END IF;
CLOSE c;
END insert_row;
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row(p_department_id IN NUMBER,
p_department_name IN VARCHAR2,
p_manager_id IN NUMBER,
p_location_id IN NUMBER)
IS
CURSOR c IS
SELECT department_id, department_name, manager_id, location_id
FROM secom.departments
WHERE department_id = p_department_id
FOR UPDATE OF department_id NOWAIT;
rec c%ROWTYPE;
BEGIN
OPEN c;
FETCH c
INTO rec;
IF (c%NOTFOUND) THEN
CLOSE c;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
app_exception.raise_exception;
END IF;
CLOSE c;
IF ((rec.department_id = p_department_id) AND
((rec.department_name = p_department_name) OR
((rec.department_name IS NULL) AND (p_department_name IS NULL))) AND
((rec.manager_id = p_manager_id) OR
((rec.manager_id IS NULL) AND (p_manager_id IS NULL))) AND
((rec.location_id = p_location_id) OR
((rec.location_id IS NULL) AND (p_location_id IS NULL)))) THEN
NULL;
ELSE
fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
app_exception.raise_exception;
END IF;
END lock_row;
/*=====================================
** PROCEDURE: update_row()
**=====================================*/
PROCEDURE update_row(p_department_id IN NUMBER,
p_department_name IN VARCHAR2,
p_manager_id IN NUMBER DEFAULT NULL,
p_location_id IN NUMBER DEFAULT NULL)
IS
BEGIN
UPDATE secom.departments
SET department_id = p_department_id,
department_name = p_department_name,
manager_id = p_manager_id,
location_id = p_location_id
WHERE department_id = p_department_id;
IF (SQL%NOTFOUND) THEN
RAISE no_data_found;
END IF;
END update_row;
/*=====================================
** PROCEDURE: delete_row()
**=====================================*/
PROCEDURE delete_row(p_department_id IN NUMBER)
IS
BEGIN
DELETE FROM secom.departments WHERE department_id = p_department_id;
IF (SQL%NOTFOUND) THEN
RAISE no_data_found;
END IF;
END delete_row;
END departments_pkg;
行表增删改查
CREATE OR REPLACE PACKAGE employees_pkg IS
/*=====================================
** PROCEDURE: insert_row()
**=====================================*/
PROCEDURE insert_row(x_row_id IN OUT VARCHAR2,
x_employee_id IN OUT NUMBER,
p_first_name IN VARCHAR2 DEFAULT NULL,
p_last_name IN VARCHAR2 DEFAULT NULL,
p_email IN VARCHAR2 DEFAULT NULL,
p_phone_number IN VARCHAR2 DEFAULT NULL,
p_hire_date IN DATE DEFAULT NULL,
p_job_id IN VARCHAR2 DEFAULT NULL,
p_salary IN NUMBER DEFAULT NULL,
p_commission_pct IN NUMBER DEFAULT NULL,
p_manager_id IN NUMBER DEFAULT NULL,
p_department_id IN NUMBER DEFAULT NULL);
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row(p_employee_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_phone_number IN VARCHAR2,
p_hire_date IN DATE,
p_job_id IN VARCHAR2,
p_salary IN NUMBER,
p_commission_pct IN NUMBER,
p_manager_id IN NUMBER,
p_department_id IN NUMBER);
/*=====================================
** PROCEDURE: update_row()
**=====================================*/
PROCEDURE update_row(p_employee_id IN NUMBER,
p_first_name IN VARCHAR2 DEFAULT NULL,
p_last_name IN VARCHAR2 DEFAULT NULL,
p_email IN VARCHAR2 DEFAULT NULL,
p_phone_number IN VARCHAR2 DEFAULT NULL,
p_hire_date IN DATE DEFAULT NULL,
p_job_id IN VARCHAR2 DEFAULT NULL,
p_salary IN NUMBER DEFAULT NULL,
p_commission_pct IN NUMBER DEFAULT NULL,
p_manager_id IN NUMBER DEFAULT NULL,
p_department_id IN NUMBER DEFAULT NULL);
/*=====================================
** PROCEDURE: delete_row()
**=====================================*/
PROCEDURE delete_row(p_employee_id IN NUMBER);
END employees_pkg;
CREATE OR REPLACE PACKAGE BODY employees_pkg IS
/*=====================================
** PROCEDURE: insert_row()
**=====================================*/
PROCEDURE insert_row(x_row_id IN OUT VARCHAR2,
x_employee_id IN OUT NUMBER,
p_first_name IN VARCHAR2 DEFAULT NULL,
p_last_name IN VARCHAR2 DEFAULT NULL,
p_email IN VARCHAR2 DEFAULT NULL,
p_phone_number IN VARCHAR2 DEFAULT NULL,
p_hire_date IN DATE DEFAULT NULL,
p_job_id IN VARCHAR2 DEFAULT NULL,
p_salary IN NUMBER DEFAULT NULL,
p_commission_pct IN NUMBER DEFAULT NULL,
p_manager_id IN NUMBER DEFAULT NULL,
p_department_id IN NUMBER DEFAULT NULL)
IS
CURSOR c IS
SELECT ROWID FROM secom.employees WHERE employee_id = x_employee_id;
BEGIN
IF x_employee_id IS NULL THEN
SELECT secom.employees_s.nextval INTO x_employee_id FROM dual;
END IF;
INSERT INTO secom.employees
(employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id)
VALUES
(x_employee_id,
p_first_name,
p_last_name,
p_email,
p_phone_number,
p_hire_date,
p_job_id,
p_salary,
p_commission_pct,
p_manager_id,
p_department_id);
OPEN c;
FETCH c
INTO x_row_id;
IF (c%NOTFOUND) THEN
CLOSE c;
RAISE no_data_found;
END IF;
CLOSE c;
END insert_row;
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row(p_employee_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2,
p_email IN VARCHAR2,
p_phone_number IN VARCHAR2,
p_hire_date IN DATE,
p_job_id IN VARCHAR2,
p_salary IN NUMBER,
p_commission_pct IN NUMBER,
p_manager_id IN NUMBER,
p_department_id IN NUMBER)
IS
CURSOR c IS
SELECT employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
FROM secom.employees
WHERE employee_id = p_employee_id
FOR UPDATE OF employee_id NOWAIT;
rec c%ROWTYPE;
BEGIN
OPEN c;
FETCH c
INTO rec;
IF (c%NOTFOUND) THEN
CLOSE c;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
app_exception.raise_exception;
END IF;
CLOSE c;
IF ((rec.employee_id = p_employee_id) AND
((rec.first_name = p_first_name) OR
((rec.first_name IS NULL) AND (p_first_name IS NULL))) AND
((rec.last_name = p_last_name) OR
((rec.last_name IS NULL) AND (p_last_name IS NULL))) AND
((rec.email = p_email) OR
((rec.email IS NULL) AND (p_email IS NULL))) AND
((rec.phone_number = p_phone_number) OR
((rec.phone_number IS NULL) AND (p_phone_number IS NULL))) AND
((rec.hire_date = p_hire_date) OR
((rec.hire_date IS NULL) AND (p_hire_date IS NULL))) AND
((rec.job_id = p_job_id) OR
((rec.job_id IS NULL) AND (p_job_id IS NULL))) AND
((rec.salary = p_salary) OR
((rec.salary IS NULL) AND (p_salary IS NULL))) AND
((rec.commission_pct = p_commission_pct) OR
((rec.commission_pct IS NULL) AND (p_commission_pct IS NULL))) AND
((rec.manager_id = p_manager_id) OR
((rec.manager_id IS NULL) AND (p_manager_id IS NULL))) AND
((rec.department_id = p_department_id) OR
((rec.department_id IS NULL) AND (p_department_id IS NULL)))) THEN
NULL;
ELSE
fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
app_exception.raise_exception;
END IF;
END lock_row;
/*=====================================
** PROCEDURE: update_row()
**=====================================*/
PROCEDURE update_row(p_employee_id IN NUMBER,
p_first_name IN VARCHAR2 DEFAULT NULL,
p_last_name IN VARCHAR2 DEFAULT NULL,
p_email IN VARCHAR2 DEFAULT NULL,
p_phone_number IN VARCHAR2 DEFAULT NULL,
p_hire_date IN DATE DEFAULT NULL,
p_job_id IN VARCHAR2 DEFAULT NULL,
p_salary IN NUMBER DEFAULT NULL,
p_commission_pct IN NUMBER DEFAULT NULL,
p_manager_id IN NUMBER DEFAULT NULL,
p_department_id IN NUMBER DEFAULT NULL)
IS
BEGIN
UPDATE secom.employees
SET employee_id = p_employee_id,
first_name = p_first_name,
last_name = p_last_name,
email = p_email,
phone_number = p_phone_number,
hire_date = p_hire_date,
job_id = p_job_id,
salary = p_salary,
commission_pct = p_commission_pct,
manager_id = p_manager_id,
department_id = p_department_id
WHERE employee_id = p_employee_id;
IF (SQL%NOTFOUND) THEN
RAISE no_data_found;
END IF;
END update_row;
/*=====================================
** PROCEDURE: delete_row()
**=====================================*/
PROCEDURE delete_row(p_employee_id IN NUMBER)
IS
BEGIN
DELETE FROM secom.employees WHERE employee_id = p_employee_id;
IF (SQL%NOTFOUND) THEN
RAISE no_data_found;
END IF;
END delete_row;
END employees_pkg;
FROM块头操作增删改查
CREATE OR REPLACE PACKAGE departments_private IS
PROCEDURE insert_row;
PROCEDURE lock_row;
PROCEDURE update_row;
PROCEDURE delete_row;
END departments_private;
CREATE OR REPLACE PACKAGE BODY departments_private IS
/*=====================================
** PROCEDURE: insert_row()
**=====================================*/
PROCEDURE insert_row IS
BEGIN
fnd_standard.set_who;
IF :departments.department_id IS NULL THEN
SELECT secom.departments_s.nextval
INTO :departments.department_id
FROM dual;
END IF;
departments_pkg.insert_row(x_row_id => :departments.row_id,
x_department_id => :departments.department_id,
p_department_name => :departments.department_name,
p_manager_id => :departments.manager_id,
p_location_id => :departments.location_id);
END insert_row;
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row IS
i NUMBER := 0;
BEGIN
LOOP
BEGIN
i := i + 1;
departments_pkg.lock_row(p_department_id => :departments.department_id,
p_department_name => :departments.department_name,
p_manager_id => :departments.manager_id,
p_location_id => :departments.location_id);
RETURN;
EXCEPTION
WHEN app_exception.record_lock_exception THEN
app_exception.record_lock_error(i);
END;
END LOOP;
END lock_row;
/*=====================================
** PROCEDURE: update_row()
**=====================================*/
PROCEDURE update_row IS
BEGIN
fnd_standard.set_who;
departments_pkg.update_row(p_department_id => :departments.department_id,
p_department_name => :departments.department_name,
p_manager_id => :departments.manager_id,
p_location_id => :departments.location_id);
END update_row;
/*=====================================
** PROCEDURE: delete_row()
**=====================================*/
PROCEDURE delete_row IS
BEGIN
departments_pkg.delete_row(p_department_id => :departments.department_id);
END delete_row;
END departments_private;
FROM块行操作增删改查
CREATE OR REPLACE PACKAGE EMPLOYEES_PRIVATE IS
PROCEDURE insert_row;
PROCEDURE lock_row;
PROCEDURE update_row;
PROCEDURE delete_row;
END employees_private;
CREATE OR REPLACE PACKAGE BODY employees_private IS
/*=====================================
** PROCEDURE: insert_row()
**=====================================*/
PROCEDURE insert_row IS
BEGIN
fnd_standard.set_who;
IF :employees.employee_id IS NULL THEN
SELECT secom.employees_s.nextval
INTO :employees.employee_id
FROM dual;
END IF;
employees_pkg.insert_row(x_row_id => :employees.row_id,
x_employee_id => :employees.employee_id,
p_first_name => :employees.first_name,
p_last_name => :employees.last_name,
p_email => :employees.email,
p_phone_number => :employees.phone_number,
p_hire_date => :employees.hire_date,
p_job_id => :employees.job_id,
p_salary => :employees.salary,
p_commission_pct => :employees.commission_pct,
p_manager_id => :employees.manager_id,
p_department_id => :employees.department_id);
END insert_row;
/*=====================================
** PROCEDURE: lock_row()
**=====================================*/
PROCEDURE lock_row IS
i NUMBER := 0;
BEGIN
LOOP
BEGIN
i := i + 1;
employees_pkg.lock_row(p_employee_id => :employees.employee_id,
p_first_name => :employees.first_name,
p_last_name => :employees.last_name,
p_email => :employees.email,
p_phone_number => :employees.phone_number,
p_hire_date => :employees.hire_date,
p_job_id => :employees.job_id,
p_salary => :employees.salary,
p_commission_pct => :employees.commission_pct,
p_manager_id => :employees.manager_id,
p_department_id => :employees.department_id);
RETURN;
EXCEPTION
WHEN app_exception.record_lock_exception THEN
app_exception.record_lock_error(i);
END;
END LOOP;
END lock_row;
/*=====================================
** PROCEDURE: update_row()
**=====================================*/
PROCEDURE update_row IS
BEGIN
fnd_standard.set_who;
employees_pkg.update_row(p_employee_id => :employees.employee_id,
p_first_name => :employees.first_name,
p_last_name => :employees.last_name,
p_email => :employees.email,
p_phone_number => :employees.phone_number,
p_hire_date => :employees.hire_date,
p_job_id => :employees.job_id,
p_salary => :employees.salary,
p_commission_pct => :employees.commission_pct,
p_manager_id => :employees.manager_id,
p_department_id => :employees.department_id);
END update_row;
/*=====================================
** PROCEDURE: delete_row()
**=====================================*/
PROCEDURE delete_row IS
BEGIN
employees_pkg.delete_row(p_employee_id => :employees.employee_id);
END delete_row;
END employees_private;
生成表操作API:
点击查看代码
--生成表操作API
BEGIN
-- Call the procedure
hss_plsql_autocreate.table_handle_pkg
hand_plsql_autocreate.table_handle_pkg(p_table_name => 'DEPARTMENTS',
p_owner => 'APPS', --如果是在CUX或其他用户名下建立的对象，则用CUX或其他用户名
p_primary_key => 'DEPARTMENT_ID');
END;
BEGIN
-- Call the procedure
hand_plsql_autocreate.table_handle_pkg(p_table_name => 'EMPLOYEES',
p_owner => 'APPS', --如果是在CUX或其他用户名下建立的对象，则用CUX或其他用户名
p_primary_key => 'EMPLOYEE_ID');
END;
--生成FORM块操作API
--参数p_table_name：如果是在CUX或其他用户名下建立的对象，此处是APPS下的同义词，实际对应 cux.xxx_all表
BEGIN
hand_plsql_autocreate.form_table_handle(p_block_name => 'DEPARTMENTS',
p_package_name => 'DEPARTMENTS_PKG',
p_table_name => 'DEPARTMENTS',
p_primary_key => 'DEPARTMENT_ID');
END;
--生成FORM块操作API
--参数p_table_name：如果是在CUX或其他用户名下建立的对象，此处是APPS下的同义词，实际对应 cux.xxx_all表
BEGIN
hand_plsql_autocreate.form_table_handle(p_block_name => 'EMPLOYEES',
p_package_name => 'EMPLOYEES_PKG',
p_table_name => 'EMPLOYEES',
p_primary_key => 'EMPLOYEE_ID');
END;
/
2. 行指示符、主从块、滚动条、Stacked&Tab画布、多行文本
2.1.行指示符
手工新建Item，名字和子类都是CURRENT_RECORD_INDICATOR。
前台FORM显示:
2.2.Master-Detail主从块
关于主从块
单据通常都可以划分为“头”与“行”的关系，在表设计中表现为“一对多”的主外键关系，在Form中的表现形式就是“主从块”。主从块有以下一些特征：
1、 通常希望输入完主块才允许用户输入从块。
2、 删除主块记录前，必须先删除从块记录。
3、 保存的时候，系统则不一定先保存主块，而是根据块在对象浏览器中的顺序。
4、 查询出主块记录时，通常希望自动带出明细块记录。
5、 在滚动主块记录时，如果从块还未保存，则无法移动。
6、 主从块都是基于数据库的，并且至少有一个可导航的字段。
设置头、行块互为前后导航块
DEPARTMENTS属性:
EMPLOYEES属性:
创建主从关系
主从块的创建异常简单。
N: 选中主块DEPARTMENTS下的Relations，从左边工具栏点击“+”
选择Detail块为EMPLOYEES、选中Prevent Maserless Operations、输入Join Condition：DEPARTMENTS.department_id = EMPLOYEES.department_id。
确定后，自动完成如下工作：
1、创建一个名为“DEPARTMENTS_EMPLOYEES”的Relation
2、创建一个Form级触发器ON-CLEAR-DETAILS
3、创建两个主块级触发器ON-POPULATE-DETAILS和ON-CHECK-DELETE-MASTER
4、创建一个过程Query_Master_Details
5、设置从块关联Item的Copy Value from Item属性
2.3.滚动条
关于滚动条
当Item在一个指定大小的区域内无法完整显示时，需要借助滚动条；根据需要可以用水平滚动条或垂直滚动条。Form中的滚动条有两个相互独立的来源：
1、块的滚动条
可决定是否为该块显示滚动条，垂直或者水平只能任选其一，基本上都是垂直，用来滚动记录。比如实际记录有10条，而块属性设置显示8条记录，如果不借助滚动条就显得不直观，记录间的导航也不方便。
2、画布的滚动条
画布有两个“区域”，一是Canvas—画布自身的大小，所有放在该画布上的Item，不能超越Canvas的边界；二是View—画布在计算机屏幕上的固定的、可见区域，如果View小于Canvas，那么需要借助滚动条来“挪动”Canvas，使其他Item也能有机会显示在View中。
画布的滚动条可以有垂直或者水平，可以同时显示。内容画布没有滚动条，所以如果Item放置在内容画布上，必须保证水平方向能够全部显示在View中。
设置滚动条
N: 选中块（这里是EMPLOYEES），F4调出属性面板，设置显示Scrollbar属性为Yes，Scrollbar所在Canvas为选择所需显示的画布：
前端FORM显示:
2.4.Stacked&Tab画布
创建堆叠画布
在需要显示的Item比较多、内容画布显示不下的时候，我们需要使用一个或多个堆叠画布来处理。通常我们需要判断，哪些Item保留在内容画布上，不随着水平滚动条滚动，其他Item需要移到新的Stacked上，通常行指示符、弹性域是要保留的，其他字段则看情况。创建画布有两种方式。
N1: 启动画布创建向导，选择New Stacked，这个比较适用于调整布局前的首次创建：
N2: 在对象浏览器中选中Canvases，点击左边工具栏的“+”，手工创建，然后修改名字和子类如下图：
设置ITEM到新画布
除了行指示符，我们希望First Name和Last Name不随滚动条滚动。这样我们设置其他Item的画布属性为新建的STACKED_CUXPOEMP：
调整堆叠画布
堆叠画布的调整需要符合如下要求：
1、View在Canvas的位置是X=0，Y=0
2、Canvas左右都无空白区域，也就是全部被Item占满，第一个Item的X位置为0，Canvas的Width = 所有Item的Width之和
3、字段顶部基本无空白，即Item的Y位置是标准高度0.25，用来放Prompt
4、Canvas底部留出滚动条的位置，通常留0.2，这样如果有10行数据，总高是2.75+0.2 = 2.95。
5、View的高度和Canvas一致，宽度则看实际情况
6、设置画布的属性，使其仅显示水平滚动条
7、设置画布的属性，使其和主画布置于同一Window
调整堆叠画布在主画布的位置
1、切换到主画布，选择菜单View/Stacked Views，选中堆叠画布：
OK后堆叠画布显示在主画布的左上角：
这个需要我们调整到合适位置，即调整到Last Name位置后，可以手工拖动（麻烦），也可通过设置属性。
2、考察下当前Last Name的位置属性X、Y和宽度属性Width：
这样我们可以得出堆叠画布的位置X=1.366 + 1 = 2.366，Y=2.476 – 0.25 = 2.226：
前端FORM显示：
画布小结：
子类和画布
EBS通过进一步“标准化”Form Builder自身的对象，提供了丰富的“子类”，这个和Java等面向对象语言中的子类是相似的，其实际上是子对象！
对于画布来说，原始类型和子类对应如下：
| 原始类型 | 子类 | 说明 |
|---|---|---|
| Content | Canvas | 内容画布，一个Windows上有且仅有一个Content画布 |
| Stacked | Canvss_Stacked | 普通堆叠画布，属性Raise on Entry为No，意思是当导航到该画布的任一Item时，仅当该Item无法全部展示在View中，那么整个View将自动移动到所有画布的顶层，也就是全部显示 |
| Canvss_Stacked_Fixed | 固定堆叠画布，属性Raise on Entry为Yes，意思是当导航到该画布的任一Item时，那么整个View将自动移动到所有画布的顶层，也就是全部显示 | |
| Tab | Tab_Canvas | 标签页，如果Item直接放置在Tab画布上，那么点击不同的标签，将自动导航到该标签的第一个可导航的Item，反之，如果导航到某标签的Item，那么也将自动切换到该标签页；Tab Page使用起来类似内容画布，如果Item太多，需要借助Stacked画布，这个时候，就没法“自动导航”、“自动切换”了，必须用代码去响应Tab页的切换事件 |
| V&H Toolbar | 垂直和水平工具条，EBS开发中没用 |
从UI角度看对象关系
刚开始做Form，通常搞不清楚Item、块、画布、Window、Form这些对象的关系，有个简单的办法可以帮助大家了解。选择菜单View/Visual View，可以看的比较清楚：
可以看出，一个Form可以有任意个Window；一个Window上可以有任意个画布，反过来一个画布只能放置在一个Window上，另外一个这里看不出的限制是一个Windows上有且仅有一个Content画布；一个画布上可以放置任意多个Item，一个块的Item，可以分步在不同的画布上，所以块和画布没有什么必然的关系
2.5.TAB画布
创建Tab画布和标签页
创建Tab画布的步骤和其他画布一样，同样需要设置其Window属性，不过其子类为Tab_Canvas；然后在其下创建标签页，我们创建了2个，子类为Tab_Page：
设置Item到标签页并调整
把原来Header区的Department Name字段放在Headers标签页上，原来Line区的内容全部放到Lines标签页上，做如下设置：
1、设置Header区Department Name字段的物理显示属性到Headers标签页：
2、同理，把原先在内容画布上的行指示符等固定的Item也设置到Lines标签页。
3、同时，把EMPLOYEES块的滚动条也设置到Lines标签页。
4、单独调整Tab画布，大约如下
调整主画布
1、切换到主画布，先通过菜单View/Stacked Views，显示TAB_DEMO画布、隐藏CUXPOEMP_STACKED画布，这样先调整CUXPOEMP_TAB画布到合适位置：
Tips：在菜单View/Stacked Views中，按住Ctrl键来选中或取消画布。
2、再通过菜单View/Stacked Views，把CUXPOEMP_STACKED画布也显示出来，调整到合适位置：
注意：Tab+Stacked，不是所见即所得，看起来是对齐的，实际运行起来不对齐，所以需要耐心，不断运行、调整、再运行再调整…..
前端FORM显示：
可以看到，如果不加控制，即使切换到Lines页，堆叠画布也不显示，必须定位到行item用Tab键导航，才会使其Raise出来。
2.6.控制TAB画布
控制思路
实际上Tab画布及其标签页和堆叠画布完全是独立的，哪个堆叠画布和哪个标签页对应，必须通过代码来处理。
当标签页切换时，我们需要响应切换事件，决定该显示哪些堆叠画布、隐藏哪些堆叠画布；反过来，当导航到堆叠画布上的Item时，因为会导致该画布Raise出来，我们需要响应Item的导航事件，决定哪个标签页成为当前。
TAB控制代码
当切换到Headers标签页时，隐藏CUXPOEMP_STACKED画布，切换到Lines标签页时，显示CUXPOEMP_STACKED画布；要注意Form初始时，也需要默认一下当前标签页，也需要隐藏CUXPOEMP_STACKED画布。
在Form级WHEN-NEW-FORM-INSTANCE中追加：
SET_CANVAS_PROPERTY('CUXPOEMP_TAB', TOPMOST_TAB_PAGE, 'HEADERS');
hide_view('CUXPOEMP_STACKED');
新建Form级WHEN-TAB-PAGE-CHANGED触发器，该触发器是用鼠标点时才会触发；另外注意几个Form标准过程的使用：
IF :system.tab_previous_page = 'HEADERS' THEN
validate(block_scope);
IF :system.MODE = 'ENTER-QUERY' OR NOT form_success THEN
--Message here
set_canvas_property('CUXPOEMP_TAB',
topmost_tab_page,
:system.tab_previous_page);
RETURN;
END IF;
ELSIF :system.tab_previous_page = 'LINES' THEN
validate(block_scope);
IF :system.MODE = 'ENTER-QUERY' OR NOT form_success THEN
--Message here
set_canvas_property('CUXPOEMP_TAB',
topmost_tab_page,
:system.tab_previous_page);
RETURN;
END IF;
END IF;
IF :system.tab_new_page = 'LINES' THEN
show_view('CUXPOEMP_STACKED');
go_item('EMPLOYEES.FIRST_NAME');
ELSIF :system.tab_new_page = 'HEADERS' THEN
hide_view('CUXPOEMP_STACKED');
go_item('DEPARTMENTS.DEPARTMENT_NAME');
END IF;
Tips：在Forms Builder中通过Help菜单，输入过程名，可查看具体的解释和Example。
前端FORM显示:
当点击头部页时:
当点击行页时:
本例中，如果在头块中使用Shift+Pagedown切换到行块，尽管已经切换到Lines标签页了，但CUXPOEMP_STACKED还是没有显示。为解决此问题，需要响应行块的第一个可导航的Item（这里是FIRST_NAME）的WHEN-NEW-ITEM-INSTANCE事件，写入代码：
show_view('CUXPOEMP_STACKED');
Tips：通过Jinitiator的Help/Keyboard Help，可以查看常用的快捷键。
2.7.多行文本框
如果需要输入的文本比较多，单行输入可能不够又不直观，可以设置Item的Multi-Line属性为Yes，如本范例的头块DEPARTMENT_NAME字段。调整高度后运行结果如下：
3.Lov查询、块查询、Button
3.1.查询原理
Form标准功能支持F11查询，只要我们按下F11，输入条件值即可，再按Ctrl+F11就可以执行查询。
F11查询原理
F11，这个时候的Block其实是处于Enter-query状态，输入的东西Form会自动拼成Where语句（当然还要加上原来的default where，如果有Copy from item，也要加上）。
对于每个Item上输入的值，一般是用 = ；如果有%，就解析为like；如果有#，则把后边的表达式（比如between，甚至是子查询）直接作为条件。
理解其他查询
下面我们将学习基于Lov的查询和基于块的查询，其原理和F11一样。
当form内部执行堆栈Navigate到Pre-query时，block也是处于Enter-query状态，和F11一样，我们只管对Item赋值（相当于F11中人工输入），剩下的就交给Form去处理了。
需要注意的是当处于enter-query状态的block，是使用query length属性来限制输入的数据长度，而不是通常的maximum lengh，只不过query length默认是0，即等于maximum lengh，所以会出现当用app_find.query_range时长度不够的情况。
3.2.LOV查询
什么是Lov查询
当用户点击工具栏上的手电筒—查询按钮时，弹出的是一个简单的Lov，其内容通常是可以唯一确定一条记录的“主码”，比如订单号。
创建LOV查询
在基于Template的开发中，须遵循如下步骤：
1、创建一个Parameter参数，用以保存lov的返回值，这里是DEPARTMENT_ID：
2、根据实际需要创建LOV，并把条件值（通常是主键ID）返回给事先建好的Parameter，我们用如下SQL创建LOV，并把LOV改名为"DEPARTMENT_ID"：
select dep.department_id,dep.department_name from SECOM.departments dep
并把department_id返回给PARAMETER.department_id：
3、添加块级QUERY_FIND触发器（该触发器不是Form标准的，所以需要手工输入），代码如下：
app_find.query_find('DEPARTMENT_ID');
4、添加块级PRE-QUERY触发器，代码如下：
if :parameter.g_query_find = 'TRUE' then
:DEPARTMENTS.department_id:= :parameter.DEPARTMENT_ID;
:parameter.g_query_find := 'FALSE';
end if;
3.3.块查询
什么是块查询
当Form比较复杂，需要用比较多的条件才能方便查询，比如订单号范围、日期范围等，或者希望查询条件用"选"而不是"输入"的方式时，需要专门做一个非数据库块来完成。
拷贝标准查询
除了不基于数据库外，做查询块和普通的块没什么区别；不过我们可以采用比较快捷的方法：
1、打开APPSTAND.fmb，把其中的对象组“QUERY_FIND”拖到我们自己的Form中：
并选择复制而非子类，因为我们需要修改
2、这样会自动产生QUERY_FIND的块、画布、Window，然后我们就可以删除对象组QUERY_FIND，因为用的是Copy，所以上述3个对象还保留着。
修改标准查询块
1、设置块、画布、Window的子类，修改Window的Title属性；这三个对象也可以改名，本例只有一个查询块，不需要。
2、设置块的下一导航块为目标块，上一导航块为自身。
3、仔细看下QUERY_FIND的块，有个块级触发器，还有三个标准Button，它们都有一个WHEN-BUTTON-PRESSED触发器，分别处理清除查询块内容、新建目标块记录、执行查询：
我们需要将其中的'your blockname here'全部改为目标块'EMPLOYEES'。
创建查询条件ITEM
手工创建需要的查询条件Item
修改触发器
1、添加块级QUERY_FIND触发器（该触发器不是Form标准的，所以需要手工输入），代码如下，具体参数含义请直接参考app_find包：
app_find.query_find('EMPLOYEES','QUERY_FIND','QUERY_FIND');
2、添加块级PRE-QUERY触发器，代码如下，注意赋值用Copy，范围查询用app_find.query_date_range：
IF :parameter.g_query_find = 'TRUE' THEN
copy(name_in('query_find.phone_number'),'EMPLOYEES.phone_number');
app_find.query_date_range(:query_find.hire_date_from,
:query_find.hire_date_to,
'EMPLOYEES.hire_date');
:parameter.g_query_find := 'FALSE';
END IF;
修改目标ITEM 查询长度
如果是范围查询，通常Item默认的最大查询长度不够，需要设置得大点，比如200：
修改模块的的第一个导航数据块为：QUERY_FIND
前端FORM: 
结果：
3.4.按钮
按钮最重要的触发器就是WHEN-BUTTON-PRESSED
4.触发器层次关系、常用触发器编写规范
4.1.层次关系
说明
当一个事件发生的时候，Forms Engine可一并触发由低到高3个级别（Item、Block、Form）的同名事件，如何触发，由定义低级别的事件时所设置的执行层次决定。
Execution Hierarchy(执行层次)：
1、 Override模式，默认模式，不再触发高级别事件
2、 Before模式，触发完本级别的事件后，再触发高级别事件
3、 After模式，先触发高级别事件，再回来触发本级别事件
当然了，如果某一层次我们没定义事件代码，Forms Engine就跳过该级别，直接进入下一级别。
此外，Block中任何Item发生的事件，都可能引发该Block级事件，所以在Block级写本属于Item级的事件，要特别性能问题；Form级事件更有类似问题。
WHEN-VALIDATE-ITEM例子
假设：
1、Item XXX的WHEN-VALIDATE-ITEM的代码是“代码1”，模式是“After”；
2、Item XXX所在BlockWHEN-VALIDATE-ITEM的代码是“代码2”，模式是“Override”；
3、Form级WHEN-VALIDATE-ITEM的代码是“代码3”。
事件：发生Item XXX的WHEN-VALIDATE-ITEM
那么：实际先执行“代码2”，然后执行“代码1”,不会再执行"代码3"。
4.2.触发器原理
Forms的触发器模型类似Windows系统的事件驱动模型，即当某个事件，比如鼠标Click，发生的时候，Forms Engine会去找是否有相应的代码，如果有，就执行之，没有的话就忽略该事件。
学习Oracle Forms触发器，除了理解同一事件的“层次关系”外，更要理解不同事件的“先后关系”，这样才能在实际开发中选择准确合理的触发器，写出精要的代码。
触发器堆栈
当界面上某个动作发生的时候，Forms Engine会把一系列要发生的事件按照“先后关系”压入执行堆栈，然后再从堆栈中一个个事件POP出来逐一执行，任一事件遇到Raise Form-Trigger-Failure，Forms Engine就停止执行、清空堆栈、Rollback事务、设置光标回合适的Item。
比如“光标从块A点入块B”，实际将发生如下事件：
| Event | Trigger |
|---|---|
| Validate the item | When-Validate-Item |
| Leave the item | Post-Text-Item |
| Validate the record | When-Validate-Record |
| Leave the record | Post-Record |
| Leave the block | Post-Block |
| Enter the block | Pre-Block |
| Enter the record | Pre-Record |
| Enter the Item | Pre-Text-Item |
| Ready block for input | When-New-Block-Instance |
| Ready record for input | When-New-Record-Instance |
| Ready item for input | When-New-Item-Instance |
| Forms Engine会把这些事件压入执行堆栈，然后逐一执行，如果在某一事件如Pre-Block中Raise Form-Trigger-Failure，Forms Engine会停止其下面所有事件的执行，并认为“光标从块A点入块B”失败，把光标仍然留在块A中。 |
常用触发器及其顺序
触发器及其触发顺序是最需要想象力和体会的东西
4.3.基于EBS模板开发的触发器
仔细观察Template.fmb，其Form级有很多事件，统一处理各种未被明确处理的事件，统一设置界面的颜色等。其中有些是不能在低级别进行Override的，有些只能在低级别中用Before或After。
比如有些人开发的Form，按F11进入查询模式，但记录颜色是白的而不是我们习惯的浅蓝色，问题的症结在于其在Block级覆盖了when-new-record-instace。
4.3.1. ACCEPT
可以删除原代码，并代之以自行开发的代码；或者编写BLOCK 级的代码并将其执行方式设置为Override。
4.3.2. KEY–DUPREC
默认代码禁止了复制记录；我们可用自己的代码取代它。
4.3.3. KEY–CLRFRM
客户化代码必须放置在原有代码的后面。
4.3.4. KEY–LISTVAL
我们可编写BLOCK 级或ITEM 级的触发器来Override。
4.3.5. POST–FORM
客户化代码须放在原有代码的前面。
4.3.6. PRE–FORM
开发人员需要输入FORM的有关信息，并更改BLOCKNAME。
4.3.7. QUERY_FIND
我们可编写BLOCK 级的触发器来Override。
4.3.8. WHEN–NEW–FORM–INSTANCE
客户化代码须放置在原有代码的前面。
4.3.9. WHEN–NEW–BLOCK–INSTANCE
创建BLOCK 级的触发器，并设置执行方式为Before。
4.3.10. WHEN–NEW–RECORD–INSTANCE
创建BLOCK 级的触发器，并设置执行方式为Before，否则最常见的问题是F11呈白色。
4.3.11. WHEN–NEW–ITEM–INSTANCE
创建BLOCK或Item 级的触发器，并设置执行方式为Before。
4.4.对触发器的一些理解：
On-lock
对On-lock的理解，由于先入为主的缘故，开始一直很苦恼，为什么If里面只用了一个Return，Form怎么知道要锁否？后来才知道On类型的数据库触发器是替换型的，On-lock也不例外，所以只要On-lock不Raise什么东西出来，Form就认为是锁成功了，至于实际的锁，我们有Select……For Update来完成，至于If判断只是进行更加严格的判定。
Pre-Form和When-New-Form-Instance
Pre-Form可以理解为打开Form最先触发的事件，如果其失败，Form就退出。
所以变量比如Global和Parameter的初始化应该最好在Pre-Form里面。
When-New-Form-Instance则是Form初始化完毕，定位到第一个块的第一个可导航的Item的时候触发的，通常用来定义弹性域和Folder、查询某个块。
Post-Query和When-New-Record-Instance
假定数据库中有10条记录，块设置显示行数为8，那么当执行完查询，将连续触发8次Post-Query，仅触发1次When-New-Record-Instance。这是因为前者是记录被Fetch到Form中触发的，后者是光标进入行时触发的。
如果我们在记录间移动光标，包括移回原来的记录，每次都将触发When-New-Record-Instance；而只有移入第9、10条记录时才会触发Post-Query，并且移回“老”记录，是不会再触发的。
如果想给某个字段赋值，通常在Post-Query中，而想根据字段的内容控制行或者某些字段的属性，通常在When-New-Record-Instance中处理。
When-Validate-Item和When-Validate-Record
但字段被修改（包括改回原来的值）、清空，并在光标欲离开该字段，将触发When-Validate-Item，我们可以在里面编写验证代码，验证失败不允许光标离开。
当光标欲离开记录，比如滚动、保存时，将触发When-Validate-Record，我们可以在这里对记录做总体验证。
浙公网安备 33010602011771号