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_u1on 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 itrem ******************************************************************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_u1on 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 itrem ******************************************************************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_u1on 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 sequencerem ******************************************************************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_u1on dem_orders (order_id);drop sequence dem_orders_s;create sequence dem_orders_s start with 25;rem ******************************************************************rem Create Order Lines tablerem ******************************************************************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_u1on dem_order_lines (order_id, order_line_num);rem ******************************************************************rem Populate Ordersrem ******************************************************************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_linesrem ******************************************************************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 everythingrem *******************************************************************commit; 

2.创建三个视图（Form可以直接基于基表，但对于复杂的表，则经常使用View）

dem_orders_v
dem_order_lines_v
dem_sales_reps_v

rem ******************************************************************rem Create Orders Viewrem ******************************************************************drop view dem_orders_v;create view dem_orders_vas selecto.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.attribute10from dem_orders o,     dem_customers c,     dem_sales_reps swhere 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 Viewrem ******************************************************************drop view dem_order_lines_v;create view dem_order_lines_vas selectl.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.attribute10from dem_order_lines l,     dem_products pwhere 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_vas 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_codefrom dem_sales_reps;rem *******************************************************************rem Commit everythingrem *******************************************************************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 columnsrem ******************************************************************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 columnsrem ******************************************************************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