Environment: Oracle database, HPUX
Description: This is sample table creation script used first in test instance, then in production, from JIT ERP system.
Code Listing:
rem $Author: roblaw $
rem $Locker: $
rem $Date: 1996/07/07 17:38:06 $
rem $Source: /appl/jit/RCS/ddl/table_b/cre_booking_detail.sql
rem $Revision: 1.1 $ [bkd_tab.sql]
/*
===============================================================
Table
===============================================================
-schedule_no v2(6), hold_cd v2(1)
*/
drop table booking_detail;
CREATE TABLE booking_detail
(
plant VARCHAR2(2),
order_no VARCHAR2(10),
order_suffix NUMERIC(2),
line_no NUMERIC(3),
sys_date DATE,
fiscal VARCHAR2(4), -- YYMM
oracle_user_id VARCHAR2(30),
reason VARCHAR2(3), -- INS, DEL, UPD
item_no VARCHAR2(50),
cust_no VARCHAR2(10),
ext_price_d NUMERIC(15,5), -- net dollar change
order_qty_d NUMERIC(15,5),
unit_price_d NUMERIC(15,5),
unit_cost_d NUMERIC(15,5),
freight_d NUMERIC(15,5),
tax_d NUMERIC(15,5),
finish_1_o VARCHAR2(5),
finish_1_n VARCHAR2(5),
strap_1_o VARCHAR2(5),
strap_1_n VARCHAR2(5),
fabric_1_o VARCHAR2(5),
fabric_1_n VARCHAR2(5),
fabric_2_o VARCHAR2(5),
fabric_2_n VARCHAR2(5),
cush_style_o VARCHAR2(5),
cush_style_n VARCHAR2(5),
ship_zip_code_o VARCHAR2(12),
ship_zip_code_n VARCHAR2(12),
not_after_date_o DATE,
not_after_date_n DATE,
)
tablespace JITDATA
storage ( initial 1m next 100k
minextents 1 maxextents 121
pctincrease 0 );
/*
===============================================================
Index
===============================================================
*/
DROP INDEX booking_detail _idx1;
CREATE INDEX booking_detail _idx1
ON booking_detail (ORDER_NO, ORDER_SUFFIX, plant)
tablespace JITINDX
storage ( initial 500K next 50K
minextents 1 maxextents 121
pctincrease 0 );
DROP INDEX booking_detail _idx2;
CREATE INDEX booking_detail _idx2
ON BACKLOG_DEMAND (ITEM_NO, PLANT)
tablespace JITINDX
storage ( initial 500K next 50K
minextents 1 maxextents 121
pctincrease 0 );
DROP INDEX booking_detail _idx3;
CREATE INDEX booking_detail _idx2
ON BACKLOG_DEMAND (cust_no)
tablespace JITINDX
storage ( initial 500K next 50K
minextents 1 maxextents 121
pctincrease 0 );
/*
===============================================================
Security
===============================================================
*/
drop public synonym booking_detail;
create public synonym booking_detail for JIT. booking_detail;
grant all on booking_detail to public;
-- grant all on booking_detail to jituser;
-- grant select on booking_detail to readjit;
/*
===============================================================
Comments
===============================================================
*/
COMMNET ON TABLE booking_detail IS
' The booking detail captures all significant changes to booked
orders. The user id, date and reason for change are captured;
along with quantity, price and cost. Other succulent nuggets are
captured to help explain many of our corporate mystries.
The booking_detail is populated from these triggers:
oedetl_trg, oehead_trg and oesummary_trg.'
COMMENT ON COLUMN booking_detail.plant IS
'oedetl.plant. If plant code change, then there will be a
separate entry for each plant.';
COMMENT ON COLUMN booking_detailorder_no IS
'oedetl.order_no.';
COMMENT ON COLUMN booking_detail.order_suffix IS
'oedetl.order_suffix.';
COMMENT ON COLUMN booking_detail.line_no IS
'oedetl.line_no. 0=if header change, oehead or oesummary.';
COMMENT ON COLUMN booking_detail.sys_date IS
'Date change was made.';
COMMENT ON COLUMN booking_detail.fiscal IS
'Fiscal period change was made.';
COMMENT ON COLUMN booking_detail.oracle_user_id IS
'User who made change.';
COMMENT ON COLUMN booking_detail.reason IS
'Reason change happened: INSert, DELete, UPDate.';
COMMENT ON COLUMN booking_detail.item_no IS
'oedetl.item_no.';
COMMENT ON COLUMN booking_detail.cust_no IS
'oehead.cust_no.';
COMMENT ON COLUMN booking_detail.ext_price_d IS
'Net dollar change = order_qty_d * unit_price_d.';
COMMENT ON COLUMN booking_detail.order_qty_d IS
'Net quantity change = new.order_qty - old.order_qty.';
COMMENT ON COLUMN booking_detail.unit_price_d IS
'Net unit price change = new.unit_price - old.unit_price';
COMMENT ON COLUMN booking_detail.freight_d IS
'Net freight dollar change. oesummary';
COMMENT ON COLUMN booking_detail.tax_d IS
'Net tax dollar change. oesummary.';
COMMENT ON COLUMN booking_detail.finish_1_o IS
'Old oedetl.finish_1 value. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.finish_1_n IS
'New oedetl.finish_1 value. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.strap_1_o IS
'Old value of oedetl.strap_1. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.strap_1_n IS
'New value of oedetl.strap_1. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.fabric_1_o IS
'Old value of oedetl.fatric_1. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.fabric_1_n IS
'New value of oedetl.fabric_1. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.fabric_2_o IS
'Old value of oedetl.fabric_2. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.fabric_2_n IS
'New value of oedetl.fabric_2. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.cush_style_o IS
'Old value of oedetl.cush_style. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.cush_style_n IS
'New value of oedetl.cush_style. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.ship_zip_code_o IS
'Old value of oehead.ship_zip_code. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.ship_zip_code_n IS
'New value of oehead.ship_zip_code. NULL if reason is DELete.';
COMMENT ON COLUMN booking_detail.not_after_date_o IS
'Old value of oehead.not_after_date. NUll if reason is INSert.';
COMMENT ON COLUMN booking_detail.not_after_date_n IS
'New value of oehead.not_after_date. NULL if reason is DELete.';
No comments:
Post a Comment