PTIAN EBS FORM开发（一）之 环境配置
OS:Windows XP
Form Builder:Version 10.1.2.0.2
FORMS_PATH
FORMS_PATH是用于Form Builder找到attached libraries (.pll)文件的路径
注册表>HKEY_LOCAL_MACHINE>Software>Oracle>KEY_DevSuiteHome1>FORMS_PATH
注：FORMS_PATH路径中最好不要有中文，另外你fmb所在的路径也不要有中文，否则可能会遇到Error "FRM-10043: Cannot open file. "
并且要把基本的pll文件放置到FORMS_PATH下，基础的pll包括APPCORE.pll, APPCORE2.pll, APPDAYPK.pll, APPFLDR.pll, CUSTOM.pll, FNDSQF.pll, FV.pll, GHR.pll, GLOBE.pll, GMS.pll, HRKPI.pll, IGILUTIL.pll, IGILUTIL2.pll, JA.pll, JE.pll, JL.pll, OPM.pll, PQH_GEN.pll, PSA.pll, PSAC.pll, PSB.pll, VERT.pll, VERT1.pll, VERT2.pll, VERT3.pll, VERT4.pll, VERT5.pll
从$AU_TOP/forms/US下载缺失的fmb文件。
从$AU_TOP/resource下载缺失的pll文件。
如果打开form的时候还是报文件找不到之类的错误（FRM-10102，FRM-18108...）,那索性就把所有的$AU_TOP/forms/US和$AU_TOP/resource都考到$FORMS_PATH再试试。
TNS配置
设置TNS_ADMIN
TNS_ADMIN用于Forms Builder找到你的TNSNAMES.ora和SQLNET.ora文件
设置tnsnames.ora
TNS_ADMIN对应目录下的tnsnames.ora增加目标instance，这样Form就可以通过TNS连接instance
创建DEMO数据
1.创建5张表
dem_sales_reps（sales representative，销售员表）
dem_customers（客户表）
dem_products（产品表）
dem_orders（订单）
dem_order_lines（订单行）
drop table dem_sales_reps;
create table dem_sales_reps
(sales_rep_id number(15) not null,
last_update_date date not null,
last_updated_by number(15) not null,
creation_date date not null,
created_by number(15) not null,
last_update_login number(15) not null,
last_name varchar2(50) not null,
first_name varchar2(50) not null,
start_date date,
commission_plan_code varchar2(1),
attribute_category varchar2(30),
attribute1 varchar2(150),
attribute2 varchar2(150),
attribute3 varchar2(150),
attribute4 varchar2(150),
attribute5 varchar2(150),
attribute6 varchar2(150),
attribute7 varchar2(150),
attribute8 varchar2(150),
attribute9 varchar2(150),
attribute10 varchar2(150));
create unique index dem_sales_reps_u1
on dem_sales_reps (sales_rep_id);
insert into dem_sales_reps
(sales_rep_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, last_name, first_name,
start_date, commission_plan_code)
values
(1001, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Magee\', \'Colin\', \'14-MAY-90\', \'A\');
insert into dem_sales_reps
(sales_rep_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, last_name, first_name,
start_date, commission_plan_code)
values
(1002, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Giljum\', \'Henry\', \'18-JAN-92\', \'A\');
insert into dem_sales_reps
(sales_rep_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, last_name, first_name,
start_date, commission_plan_code)
values
(1003, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Sedeghi\', \'Yasmin\', \'18-FEB-91\', \'A\');
insert into dem_sales_reps
(sales_rep_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, last_name, first_name,
start_date, commission_plan_code)
values
(1004, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Nguyen\', \'Mai\', \'22-JAN-92\', \'A\');
insert into dem_sales_reps
(sales_rep_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, last_name, first_name,
start_date, commission_plan_code)
values
(1006, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Dumas\', \'Andre\', \'09-OCT-91\', \'A\');
insert into dem_sales_reps
(sales_rep_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, last_name, first_name,
start_date, commission_plan_code)
values
(1005, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Jones\', \'Robert\', \'23-JUN-95\', \'B\');
insert into dem_sales_reps
(sales_rep_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, last_name, first_name,
start_date, commission_plan_code)
values
(1007, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Lee\', \'Vicki\', \'10-OCT-92\', \'A\');
insert into dem_sales_reps
(sales_rep_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, last_name, first_name,
start_date, commission_plan_code)
values
(1008, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Stratton\', \'Ruth\', \'27-JUN-93\', \'B\');
rem ******************************************************************
rem Create Customers table and populate it
rem ******************************************************************
drop table dem_customers;
create table dem_customers
(customer_id number(15) not null,
last_update_date date not null,
last_updated_by number(15) not null,
creation_date date not null,
created_by number(15) not null,
last_update_login number(15) not null,
name varchar2(50) not null,
phone varchar2(25),
address varchar2(400),
city varchar2(30),
state_code varchar2(20),
country varchar2(30),
postal_code varchar2(75),
credit_rating varchar2(9),
sales_rep_id number(15),
region_id number(15),
comments varchar2(255),
attribute_category varchar2(30),
attribute1 varchar2(150),
attribute2 varchar2(150),
attribute3 varchar2(150),
attribute4 varchar2(150),
attribute5 varchar2(150),
attribute6 varchar2(150),
attribute7 varchar2(150),
attribute8 varchar2(150),
attribute9 varchar2(150),
attribute10 varchar2(150));
create unique index dem_customers_u1
on dem_customers (customer_id);
insert into dem_customers
(customer_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, name, phone, address,
city, state_code, postal_code, country, credit_rating, sales_rep_id,
region_id, comments)
values
(201, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1, \'Unisports\',
\'55-2066101\', \'72 Mill Water\',\'Milpitas\', \'CA\', \'95035\', \'USA\', \'EXCELLENT\',
1001, 2, \'Brother of owner!\');
insert into dem_customers
(customer_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, name, phone, address,
city, state_code, postal_code, country, credit_rating, sales_rep_id,
region_id, comments)
values
(202, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1, \'Womansport\',
\'1-206-104-0103\', \'3281 King Street\',\'Seattle\', \'WA\', \'98101\', \'USA\', \'GOOD\',
1001, 2, \'\');
insert into dem_customers
(customer_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, name, phone, address,
city, state_code, postal_code, country, credit_rating, sales_rep_id,
region_id, comments)
values
(203, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1, \'Tornado Sports\',
\'1-913-661-9699\', \'11629 West 113th Street\',\'Overland Park\', \'KS\',
\'66210\', \'USA\', \'GOOD\', 1005, 3, \'Windy Accounts Payable Clerk\');
insert into dem_customers
(customer_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, name, phone, address,
city, state_code, postal_code, country, credit_rating, sales_rep_id,
region_id, comments)
values
(204, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1, \'Big Johns Sports\',
\'1-415-555-6281\', \'4783 18th Street\',\'San Francisco\', \'CA\', \'94117\',
\'USA\', \'EXCELLENT\', 1002, 3, \'\');
insert into dem_customers
(customer_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, name, phone, address,
city, state_code, postal_code, country, credit_rating, sales_rep_id,
region_id, comments)
values
(205, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1, \'Ski USA\',
\'1-913-555-2637\', \'10 Ward Parkway\',\'Kansas City\', \'MO\', \'66214\', \'USA\',
\'POOR\', 1004, 3, \'Insist upon cash\');
rem ******************************************************************
rem Create Products table and populate it
rem ******************************************************************
drop table dem_products;
create table dem_products
(product_id number(15) not null,
last_update_date date not null,
last_updated_by number(15) not null,
creation_date date not null,
created_by number(15) not null,
last_update_login number(15) not null,
description varchar2(255) not null,
suggested_price number(25),
unit_of_measure varchar2(25),
attribute_category varchar2(30),
attribute1 varchar2(150),
attribute2 varchar2(150),
attribute3 varchar2(150),
attribute4 varchar2(150),
attribute5 varchar2(150),
attribute6 varchar2(150),
attribute7 varchar2(150),
attribute8 varchar2(150),
attribute9 varchar2(150),
attribute10 varchar2(150));
create unique index dem_products_u1
on dem_products (product_id);
insert into dem_products
(product_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, description, suggested_price, unit_of_measure)
values
(10011, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Beginner ski boot\', 150.00, \'Pair\');
insert into dem_products
(product_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, description, suggested_price, unit_of_measure)
values
(10012, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Ace Ski Boot -- Intermediate ski boot\', 200.00, \'Pair\');
insert into dem_products
(product_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, description, suggested_price, unit_of_measure)
values
(10013, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Pro Ski Boot -- Advanced ski boot\', 410.00, \'Pair\');
insert into dem_products
(product_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, description, suggested_price, unit_of_measure)
values
(10021, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Bunny Ski Pole -- Beginners ski pole\', 16.25, \'Pair\');
insert into dem_products
(product_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, description, suggested_price, unit_of_measure)
values
(10022, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Ace Ski Pole -- Intermediate ski pole\', 21.95, \'Pair\');
insert into dem_products
(product_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, description, suggested_price, unit_of_measure)
values
(10023, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Advanced ski pole\', 40.95, \'Pair\');
insert into dem_products
(product_id, last_update_date, last_updated_by, creation_date,
created_by, last_update_login, description, suggested_price, unit_of_measure)
values
(10031, \'23-JUN-95\', 1, \'23-JUN-95\', 1, 1,
\'Ultra Ski Goggles -- UV-blocking ski goggles\', 30.95, \'Pair\');
rem ******************************************************************
rem Create Orders table and sequence
rem ******************************************************************
drop table dem_orders;
create table dem_orders
(order_id number(15) not null,
last_update_date date not null,
last_updated_by number(15) not null,
creation_date date not null,
created_by number(15) not null,
last_update_login number(15) not null,
customer_id number(15) not null,
sales_rep_id number(15),
payment_type varchar2(6) not null,
currency_code varchar2(15) not null,
order_status varchar2(1) not null,
date_ordered date,
date_shipped date,
check_number number(15),
cc_type varchar2(1),
cc_number varchar2(30),
cc_expiration varchar2(5),
cc_approval_code varchar2(15),
order_note varchar2(2000),
attribute_category varchar2(30),
attribute1 varchar2(150),
attribute2 varchar2(150),
attribute3 varchar2(150),
attribute4 varchar2(150),
attribute5 varchar2(150),
attribute6 varchar2(150),
attribute7 varchar2(150),
attribute8 varchar2(150),
attribute9 varchar2(150),
attribute10 varchar2(150));
create unique index dem_orders_u1
on dem_orders (order_id);
drop sequence dem_orders_s;
create sequence dem_orders_s start with 25;
rem ******************************************************************
rem Create Order Lines table
rem ******************************************************************
drop table dem_order_lines;
create table dem_order_lines
(order_id number(15) not null,
order_line_num number(15) not null,
last_update_date date not null,
last_updated_by number(15) not null,
creation_date date not null,
created_by number(15) not null,
last_update_login number(15) not null,
product_id number(15) not null,
gl_account_cc_id number(15),
ordered_quantity number(15),
attribute_category varchar2(30),
attribute1 varchar2(150),
attribute2 varchar2(150),
attribute3 varchar2(150),
attribute4 varchar2(150),
attribute5 varchar2(150),
attribute6 varchar2(150),
attribute7 varchar2(150),
attribute8 varchar2(150),
attribute9 varchar2(150),
attribute10 varchar2(150));
create unique index dem_order_lines_u1
on dem_order_lines (order_id, order_line_num);
rem ******************************************************************
rem Populate Orders
rem ******************************************************************
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(1, \'17-SEP-95\', -1, \'23-AUG-95\', 1236, 0, 201, 1001, \'CASH\',
\'USD\', \'F\', \'23-AUG-95\', \'\', \'\', \'\', \'\', \'\', \'\');
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(2, \'17-SEP-95\', -1, \'23-AUG-95\', 1236, 0, 202, 1001, \'CHECK\',
\'ZZZ\', \'N\', \'23-AUG-95\', \'\', \'1201\', \'\', \'\', \'\', \'\');
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(3, \'29-AUG-95\', -1, \'29-AUG-95\', -1, 0, 202, 1005, \'CHARGE\',
\'USD\', \'F\', \'02-JUL-95\', \'03-JUL-95\', \'\', \'V\', \'1234 5678 9012\',
\'05/97\', \'01\');
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(5, \'29-AUG-95\', -1, \'29-AUG-95\', -1, 0, 201, 1006, \'CASH\',
\'USD\', \'N\', \'29-AUG-95\', \'\', \'\', \'\', \'\', \'\', \'\');
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(6, \'31-AUG-95\', -1, \'31-AUG-95\', -1, 0, 201, 1004, \'CHARGE\',
\'USD\', \'N\', \'31-AUG-95\', \'\', \'\', \'E\', \'1234 5678 9012\', \'09/97\', \'\');
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(7, \'31-AUG-95\', -1, \'31-AUG-95\', -1, 0, 204, 1005, \'CASH\',
\'USD\', \'N\', \'31-AUG-95\', \'\', \'\', \'\', \'\', \'\', \'\');
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(8, \'31-AUG-95\', -1, \'31-AUG-95\', -1, 0, 202, 1008, \'CASH\',
\'USD\', \'N\', \'31-AUG-95\', \'\', \'\', \'\', \'\', \'\', \'\');
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(13, \'17-SEP-95\', -1, \'17-SEP-95\', -1, 0, 203, 1005, \'CHECK\',
\'USD\', \'F\', \'11-SEP-95\', \'17-SEP-95\', \'751\', \'\', \'\', \'\', \'\');
insert into dem_orders (ORDER_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN, CUSTOMER_ID, SALES_REP_ID, PAYMENT_TYPE,
CURRENCY_CODE, ORDER_STATUS, DATE_ORDERED, DATE_SHIPPED, CHECK_NUMBER,
CC_TYPE, CC_NUMBER, CC_EXPIRATION, CC_APPROVAL_CODE) values
(14, \'17-SEP-95\', -1, \'17-SEP-95\', -1, 0, 205, 1008, \'CHARGE\',
\'USD\', \'N\', \'12-AUG-95\', \'21-AUG-95\', \'\', \'V\', \'3456 7654 2345 9087\',
\'07/97\', \'101\');
rem ******************************************************************
rem Populate Order_lines
rem ******************************************************************
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(1, 1, \'29-AUG-95\', -1, \'23-AUG-95\', 1236, 0, 10011, 1);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(2, 1, \'30-AUG-95\', -1, \'23-AUG-95\', 1236, 0, 10021, 3);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(2, 2, \'29-AUG-95\', -1, \'23-AUG-95\', 1236, 0, 10031, 2);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(3, 1, \'29-AUG-95\', -1, \'29-AUG-95\', -1, 0, 10022, 2);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(3, 2, \'29-AUG-95\', -1, \'29-AUG-95\', -1, 0, 10013, 4);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(3, 3, \'29-AUG-95\', -1, \'29-AUG-95\', -1, 0, 10012, 7);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(5, 1, \'29-AUG-95\', -1, \'29-AUG-95\', -1, 0, 10012, 2);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(6, 1, \'31-AUG-95\', -1, \'31-AUG-95\', -1, 0, 10013, 3);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(6, 2, \'31-AUG-95\', -1, \'31-AUG-95\', -1, 0, 10011, 5);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(6, 3, \'31-AUG-95\', -1, \'31-AUG-95\', -1, 0, 10031, 1);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(7, 1, \'31-AUG-95\', -1, \'31-AUG-95\', -1, 0, 10013, 1);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(8, 1, \'31-AUG-95\', -1, \'31-AUG-95\', -1, 0, 10013, 2);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(13, 1, \'17-SEP-95\', -1, \'17-SEP-95\', -1, 0, 10031, 1);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(13, 2, \'17-SEP-95\', -1, \'17-SEP-95\', -1, 0, 10013, 3);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(14, 1, \'17-SEP-95\', -1, \'17-SEP-95\', -1, 0, 10012, 10);
insert into dem_order_lines (ORDER_ID, ORDER_LINE_NUM, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, PRODUCT_ID,
ORDERED_QUANTITY) values
(14, 2, \'17-SEP-95\', -1, \'17-SEP-95\', -1, 0, 10023, 10);
rem *******************************************************************
rem Commit everything
rem *******************************************************************
commit;
2.创建三个视图（Form可以直接基于基表，但对于复杂的表，则经常使用View）
dem_orders_v
dem_order_lines_v
dem_sales_reps_v
rem ******************************************************************
rem Create Orders View
rem ******************************************************************
drop view dem_orders_v;
create view dem_orders_v
as select
o.rowid row_id,
o.order_id,
o.last_update_date,
o.last_updated_by,
o.creation_date,
o.created_by,
o.last_update_login,
o.customer_id,
c.name customer_name,
o.sales_rep_id,
s.first_name ||\' \'|| s.last_name sales_rep_name,
o.payment_type,
o.currency_code,
o.order_status,
o.date_ordered,
o.date_shipped,
o.check_number,
o.cc_type,
o.cc_number,
o.cc_expiration,
o.cc_approval_code,
o.order_note,
o.attribute_category,
o.attribute1,
o.attribute2,
o.attribute3,
o.attribute4,
o.attribute5,
o.attribute6,
o.attribute7,
o.attribute8,
o.attribute9,
o.attribute10
from dem_orders o,
dem_customers c,
dem_sales_reps s
where o.customer_id = c.customer_id
and o.sales_rep_id = s.sales_rep_id(+);
rem (outer join picks up orders that have no associated sales_rep)
rem ******************************************************************
rem Create Order Lines View
rem ******************************************************************
drop view dem_order_lines_v;
create view dem_order_lines_v
as select
l.rowid row_id,
l.order_id,
l.order_line_num,
l.last_update_date,
l.last_updated_by,
l.creation_date,
l.created_by,
l.last_update_login,
l.product_id,
p.description product_description,
p.unit_of_measure,
p.suggested_price,
l.gl_account_cc_id,
l.ordered_quantity,
l.attribute_category,
l.attribute1,
l.attribute2,
l.attribute3,
l.attribute4,
l.attribute5,
l.attribute6,
l.attribute7,
l.attribute8,
l.attribute9,
l.attribute10
from dem_order_lines l,
dem_products p
where l.product_id = p.product_id;
rem ******************************************************************
rem Create Sales Reps View (for LOVs on Orders Block)
rem ******************************************************************
drop view dem_sales_reps_v;
create view dem_sales_reps_v
as select
rowid row_id,
sales_rep_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
first_name ||\' \'|| last_name sales_rep_name,
last_name,
first_name,
start_date,
commission_plan_code
from dem_sales_reps;
rem *******************************************************************
rem Commit everything
rem *******************************************************************
commit;
3.需要向EBS注册表和字段(DEM_ORDERS,DEM_ORDER_LINES)，这样以后就可通过标准功能设置弹性域、监控表操作。
EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ORDER_ID\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'LAST_UPDATE_DATE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'LAST_UPDATED_BY\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CREATION_DATE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CREATED_BY\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'LAST_UPDATE_LOGIN\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CUSTOMER_ID\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'SALES_REP_ID\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'PAYMENT_TYPE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CURRENCY_CODE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ORDER_STATUS\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'DATE_ORDERED\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'DATE_SHIPPED\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CHECK_NUMBER\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CC_TYPE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CC_NUMBER\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CC_EXPIRATION\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'CC_APPROVAL_CODE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ORDER_NOTE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE_CATEGORY\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE1\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE2\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE3\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE4\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE5\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE6\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE7\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE8\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE9\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE10\'); rem ****************************************************************** rem Delete registration of Orders table rem ****************************************************************** EXECUTE ad_dd.delete_table(\'DEM\', \'DEM_ORDERS\'); commit; rem ****************************************************************** rem Register Orders table and columns rem ****************************************************************** EXECUTE ad_dd.register_table(\'DEM\', \'DEM_ORDERS\',\'T\',8,10,90); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ORDER_ID\', 1, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'LAST_UPDATE_DATE\', 2, \'DATE\', 9, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'LAST_UPDATED_BY\', 3, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CREATION_DATE\', 4, \'DATE\', 9, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CREATED_BY\', 5, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'LAST_UPDATE_LOGIN\', 6, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CUSTOMER_ID\', 7, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'SALES_REP_ID\', 8, \'NUMBER\', 15, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'PAYMENT_TYPE\', 9, \'VARCHAR2\', 6, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CURRENCY_CODE\', 10, \'VARCHAR2\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ORDER_STATUS\', 11, \'VARCHAR2\', 1, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'DATE_ORDERED\', 12, \'DATE\', 9, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'DATE_SHIPPED\', 13, \'DATE\', 9, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CHECK_NUMBER\', 14, \'NUMBER\', 15, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CC_TYPE\', 15, \'VARCHAR2\', 1, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CC_NUMBER\', 16, \'VARCHAR2\', 30, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CC_EXPIRATION\', 17, \'VARCHAR2\', 5, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'CC_APPROVAL_CODE\', 18, \'VARCHAR2\', 15, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ORDER_NOTE\', 19, \'VARCHAR2\', 2000, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE_CATEGORY\', 20, \'VARCHAR2\', 30, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE1\', 21, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE2\', 22, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE3\', 23, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE4\', 24, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE5\', 25, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE6\', 26, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE7\', 27, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE8\', 28, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE9\', 29, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDERS\', \'ATTRIBUTE10\', 30, \'VARCHAR2\', 150, \'Y\', \'N\'); commit; rem ****************************************************************** rem Delete registration of columns in Order Lines table rem ****************************************************************** EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ORDER_ID\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ORDER_LINE_NUM\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'LAST_UPDATE_DATE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'LAST_UPDATED_BY\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'CREATION_DATE\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'CREATED_BY\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'LAST_UPDATE_LOGIN\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'PRODUCT_ID\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'GL_ACCOUNT_CC_ID\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ORDERED_QUANTITY\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE_CATEGORY\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE1\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE2\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE3\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE4\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE5\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE6\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE7\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE8\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE9\'); EXECUTE ad_dd.delete_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE10\'); rem ****************************************************************** rem Delete registration of Order Lines table rem ****************************************************************** EXECUTE ad_dd.delete_table(\'DEM\', \'DEM_ORDER_LINES\'); commit; rem ****************************************************************** rem Register Order Lines table and columns rem ****************************************************************** EXECUTE ad_dd.register_table(\'DEM\', \'DEM_ORDER_LINES\',\'T\',8,10,90); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ORDER_ID\', 1, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ORDER_LINE_NUM\', 2, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'LAST_UPDATE_DATE\', 3, \'DATE\', 9, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'LAST_UPDATED_BY\', 4, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'CREATION_DATE\', 5, \'DATE\', 9, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'CREATED_BY\', 6, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'LAST_UPDATE_LOGIN\', 7, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'PRODUCT_ID\', 8, \'NUMBER\', 15, \'N\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'GL_ACCOUNT_CC_ID\', 9, \'NUMBER\', 15, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ORDERED_QUANTITY\', 10, \'NUMBER\', 15, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE_CATEGORY\', 11, \'VARCHAR2\', 30, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE1\', 12, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE2\', 13, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE3\', 14, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE4\', 15, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE5\', 16, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE6\', 17, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE7\', 18, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE8\', 19, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE9\', 20, \'VARCHAR2\', 150, \'Y\', \'N\'); EXECUTE ad_dd.register_column(\'DEM\', \'DEM_ORDER_LINES\', \'ATTRIBUTE10\', 21, \'VARCHAR2\', 150, \'Y\', \'N\'); commit;
转载请注明出处：http://blog.csdn.net/pan_tian/article/details/7638111
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26687597/viewspace-1203738/ ，如需转载，请注明出处，否则将追究法律责任。
