?? yearinit.sql
字號:
RetailQty TQty null, /*銷售數量*/
CheckQty TQty null, /*結算數量*/
CheckAmt TAMT null, /*結算金額*/
NoCheckQty TQty null,/*未結算數量*/
NoCheckAmt TAmt null,/*未結算金額*/
ID integer Identity not null primary key )
go
create index idx_import_to_check_goodsno on import_to_check(goodsno)
go
create index idx_import_to_check_vendorno on import_to_check(vendorno)
go
/*供貨商應付帳款表*/
if (select count(*) from sysobjects where name= 'payable') > 0
drop table payable
go
create table Payable
(
BusinessDate datetime not null,/*業務日期*/
VendorNo TVendor not null,/*供貨商代碼*/
BusinessType varchar(12) null,/*業務類型*/
Remark varchar(30) null,/*摘要*/
InvoiceNo TInvoice null,/*原始單據號碼*/
AddAmt TAmt null,/*增加金額*/
SubAmt TAmt null,/*減少金額*/
BalanceAmt TAmt null,/*結存金額*/
ID Integer Identity not null primary key
)
go
create index idx_payable_vendorno on Payable(vendorno)
go
/*進貨結算流水*/
if (select count(*) from sysobjects where name= 'ImportCheck') > 0
drop table ImportCheck
go
create table ImportCheck
(
CheckNO TRECEIPT not null primary key, /*結算流水*/
InputDate datetime not null,/*錄入日期*/
VendorNo TVendor not null,/*供貨商代碼*/
CheckType char(1) null,/*結算類型*/
CheckMan TStaff null,/*結算人*/
CheckDate datetime null,/*結算日期*/
InvoiceNo TINVOICE null,/*發票號碼*/
CheckAmt TAmt null,/*結算金額*/
PrepayAmt TAmt null,/*預付金額*/
DisAmt TAmt null,/*讓利金額*/
Operator TStaff null,/*操作員*/
remark TMEMO null /*備注*/
)
go
create unique index idx_importcheck_checkno on ImportCheck(CheckNo)
go
create index idx_importcheck_vendorno on ImportCheck(VendorNo)
go
/*進貨結算單據明細表*/
if (select count(*) from sysobjects where name= 'ImportCheckByReceipt') > 0
drop table ImportCheckByReceipt
go
create table ImportCheckByReceipt
(
CheckNo TRECEIPT not null, /*結算流水*/
ImportNo TRECEIPT null,/*進貨流水*/
ImportDate DateTime null ,/*進貨日期*/
VendorNo TVendor null,/*供貨商代碼*/
PrepayAmt TAmt null,/*預付金額*/
NTaxAmt TAmt null,/*無稅金額*/
TaxAmt TAmt null, /*含稅金額*/
ID int identity
)
go
create index idx_importcheckbyreceipt_checkno on ImportCheckByReceipt(CheckNo)
go
/*進貨結算單品明細表*/
if (select count(*) from sysobjects where name= 'ImportCheckByGoods') > 0
drop table ImportCheckByGoods
go
create table ImportCheckByGoods
(
CheckNO TReceipt not null,/*結算流水*/
ImportDate Datetime null ,/*進貨日期*/
ImportNo TReceipt not null,/*對應進貨_銷售關聯庫中的進貨流水*/
GoodsNo TGoods not null,/*商品代碼*/
NTaxPrice numeric(12,4) null,/*無稅進價*/
TaxPrice numeric(12,4) null,/*含稅進價*/
CheckQty TQty null,/*結算數量*/
CheckAmt TAmt null/*結算金額*/
)
go
create index idx_importcheckbygoods_checkno on ImportCheckByGoods(CheckNo)
go
/*進貨結算金額明細表*/
if (select count(*) from sysobjects where name= 'ImportCheckByAmt') > 0
drop table ImportCheckByAmt
go
create table ImportCheckByAmt
(
CheckNo TReceipt not null,/*結算流水*/
PayableAmt TAmt null,/*應付金額*/
PayingAmt TAmt null,/*結算金額*/
BalanceAmt Tamt null/*結存應付金額*/
)
go
create index idx_importcheckbyamt_checkno on ImportCheckByGoods(CheckNo)
go
/* ============================================================ */
/* Table: RetailStat */
/* ============================================================ */
create table RetailStat
(
deptno TDept not null,
GroupNo TGroup not null,
ClassNO TClass not null,
TaxRatio numeric(5,3) not null,
RetailMode char(1) not null,
RetailDate Tdate not null,
Amt TAmt null ,
CostAmt TAmt null ,
DisAmt TAmt null ,
Qty TQty null
)
go
/* ============================================================ */
/* Index: index_of_date */
/* ============================================================ */
create index index_of_date on RetailStat (RetailDate)
go
/* ============================================================ */
/* Index: index_of_group */
/* ============================================================ */
create index index_of_group on RetailStat (GroupNo)
go
/* ============================================================ */
/* Index: index_of_class */
/* ============================================================ */
create index index_of_class on RetailStat (ClassNO)
go
/* ============================================================ */
/* Table: VendorRetailStat */
/* ============================================================ */
create table VendorRetailStat
(
VendorNO TVendor not null,
RetailDate Tdate not null,
TaxRatio numeric(5,3) not null,
RetailMode char(1) not null,
Amt TAmt null ,
CostAmt TAmt null ,
DisAmt TAmt null ,
Qty TQty null
)
go
/* ============================================================ */
/* Index: index_of_date */
/* ============================================================ */
create index index_of_date on VendorRetailStat (RetailDate)
go
/* ============================================================ */
/* Index: index_of_vendor */
/* ============================================================ */
create index index_of_vendor on VendorRetailStat (VendorNO)
go
/* ============================================================ */
/* Table: CategoryRetailStat */
/* ============================================================ */
create table CategoryRetailStat
(
CategoryNo varchar(8) not null,
RetailDate Tdate not null,
RetailMode char(1) null ,
TaxRatio numeric(5,3) null ,
Amt TAmt null ,
CostAmt TAmt null,
DisAmt TAmt null ,
Qty TQty null
)
go
/* ============================================================ */
/* Index: index_of_retaildate */
/* ============================================================ */
create index index_of_retaildate on CategoryRetailStat (RetailDate)
go
/* ============================================================ */
/* Index: index_of_category */
/* ============================================================ */
create index index_of_category on CategoryRetailStat (CategoryNo)
go
/* ============================================================ */
/* Table: NewGoodsRetail */
/* ============================================================ */
create table NewGoodsRetail
(
GoodsNo TGoods not null,
RetailDate Tdate not null,
Amt TAmt null ,
CostAmt TAmt null ,
DisAmt TAmt null ,
Qty TQty null ,
constraint PK_NewGoodsRetail primary key (GoodsNo, RetailDate)
)
go
/* ============================================================ */
/* Index: index_of_date3 */
/* ============================================================ */
create index index_of_date3 on NewGoodsRetail (RetailDate)
go
/* ============================================================ */
/* Index: index_of_vendor3 */
/* ============================================================ */
create index index_of_vendor3 on NewGoodsRetail (GoodsNo)
go
/* ============================================================ */
/* Table: ImportantGoodsRetail */
/* ============================================================ */
create table ImportantGoodsRetail
(
GoodsNo TGoods not null,
RetailDate Tdate not null,
Amt TAmt null ,
CostAmt TAmt null ,
DisAmt TAmt null ,
Qty TQty null ,
constraint PK_ImportantGoodsRetail primary key (GoodsNo, RetailDate)
)
go
/* ============================================================ */
/* Index: index_of_date32 */
/* ============================================================ */
create index index_of_date32 on ImportantGoodsRetail (RetailDate)
go
/* ============================================================ */
/* Index: index_of_vendor32 */
/* ============================================================ */
create index index_of_vendor32 on ImportantGoodsRetail (GoodsNo)
go
/* ============================================================ */
/* Table: AllocStat */
/* ============================================================ */
create table AllocStat
(
DeptNo Tdept not null,
GroupNo TGroup not null,
ClassNo TClass not null,
AlloctDate Tdate not null,
Amt TAmt null ,
NTaxAmt TAmt null ,
Qty TQty null ,
constraint PK_allocstat primary key (DeptNo, GroupNo, ClassNo, AlloctDate)
)
go
if exists (select 1
from sysobjects
where id = object_id('ExpensesDetail')
and type = 'U')
drop table ExpensesDetail
go
if exists (select 1
from sysobjects
where id = object_id('ImportAdjustDetail')
and type = 'U')
drop table ImportAdjustDetail
go
if exists (select 1
from sysobjects
where id = object_id('ImportAdjust')
and type = 'U')
drop table ImportAdjust
go
/* ============================================================ */
/* Table: ImportAdjust */
/* ============================================================ */
create table ImportAdjust
(
AdjustNo TRECEIPT not null,
InputDate TDATE null
default getdate(),
AuditDate TDATE null
default getdate(),
VendorNo TVendor not null,
Operator TSTAFF null ,
ReceiptNo TINVOICE null ,
Employee TSTAFF null ,
AuditFlag char(1) not null
default '0',
AuditOperator TSTAFF null ,
Remark TMEMO null ,
constraint PK_IMPORTADJUST primary key (AdjustNo)
)
go
/* ============================================================ */
/* Index: index_of_adjust_inputdate2 */
/* =============================?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -