-
Notifications
You must be signed in to change notification settings - Fork 0
/
IO.sql
170 lines (168 loc) · 3.94 KB
/
IO.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
-- Create table
create table CSUF_INTERNAL_SUPPLIER
(
internal_supplier_id VARCHAR2(6),
obj_id VARCHAR2(36) default SYS_GUID(),
ver_nbr NUMBER(8) default 1,
internal_supplier_nm VARCHAR2(60),
internal_supplier_contact VARCHAR2(40),
internal_supplier_phone VARCHAR2(10),
internal_supplier_actv_ind VARCHAR2(1)
)
tablespace KFS_DATA_SPACE_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CSUF_INTERNAL_SUPPLIER
add constraint CSUF_INTRNL_SUPPL_P1 primary key (INTERNAL_SUPPLIER_ID)
using index
tablespace KFS_INDEX_SPACE_01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table CSUF_INTERNAL_SUPPLIER
add constraint CSUF_INTRNL_SUPPL_C0 unique (OBJ_ID)
using index
tablespace KFS_INDEX_SPACE_01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate check constraints
alter table CSUF_INTERNAL_SUPPLIER
add constraint CSUF_INTRNL_SUPPL_N1
check ("INTERNAL_SUPPLIER_ID" IS NOT NULL);
alter table CSUF_INTERNAL_SUPPLIER
add constraint CSUF_INTRNL_SUPPL_N2
check ("OBJ_ID" IS NOT NULL);
alter table CSUF_INTERNAL_SUPPLIER
add constraint CSUF_INTRNL_SUPPL_N3
check ("VER_NBR" IS NOT NULL);
-- Create table
create table CSUF_INT_ORDER_DOC
(
fdoc_nbr VARCHAR2(14) not null,
obj_id VARCHAR2(36) not null,
ver_nbr NUMBER(8) not null,
internal_supplier_id VARCHAR2(6),
fdoc_nxt_exp_nbr NUMBER(7),
fdoc_nxt_inc_nbr NUMBER(7),
fdoc_nxt_itm_nbr NUMBER(7),
fdoc_post_yr NUMBER(4),
io_orig_amt NUMBER(19,2)
)
tablespace KFS_DATA_SPACE_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CSUF_INT_ORDER_DOC
add constraint CSUF_INT_ORDER_DOC_P1 primary key (FDOC_NBR)
using index
tablespace KFS_INDEX_SPACE_01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table CSUF_INT_ORDER_DOC
add constraint CSUF_INT_ORDER_DOC_C0 unique (OBJ_ID)
using index
tablespace KFS_INDEX_SPACE_01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table CSUF_INT_ORDER_ITM
(
fdoc_nbr VARCHAR2(14) not null,
obj_id VARCHAR2(36) not null,
ver_nbr NUMBER(8) not null,
fdoc_itm_nbr NUMBER(7) not null,
fdoc_itm_stck_nbr VARCHAR2(9),
fdoc_itm_stck_desc VARCHAR2(40),
fdoc_itm_srvc_dt DATE,
fdoc_itm_qty NUMBER(7,2),
fdoc_itm_unit_amt NUMBER(19,2),
fdoc_unit_msr_cd VARCHAR2(2)
)
tablespace KFS_DATA_SPACE_01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CSUF_INT_ORDER_ITM
add constraint CSUF_INT_ORDER_ITM_P1 primary key (FDOC_NBR, FDOC_ITM_NBR)
using index
tablespace KFS_INDEX_SPACE_01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table CSUF_INT_ORDER_ITM
add constraint CSUF_INT_ORDER_ITM_C0 unique (OBJ_ID)
using index
tablespace KFS_INDEX_SPACE_01
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);