yearinit.sql

这是用PB6开发的一个POS管理系统

use sypos2000
go

sp_dboption 'sypos','trunc. log on chkpt.',true
go
sp_dboption 'sypos','select into/bulkcopy',true
go
sp_dboption 'sypos','ANSI null default',true
go

sp_addalias 'sypos','dbo'
go
sp_addalias 'posuser','dbo'
go

Sp_AddType 'TGOODS', 'CHAR(13)'
go
Sp_AddType 'TDEPT',  'CHAR(4)'
go
Sp_AddType 'TGROUP', 'CHAR(4)'
go
Sp_AddType 'TCLASS', 'CHAR(8)'
go
Sp_AddType 'TSTAFF', 'CHAR(5)'
go
Sp_AddType 'TVENDOR','CHAR(8)'
go
Sp_AddType 'TCARD',  'VARCHAR(16)'
go
Sp_AddType 'TPAYMODE','CHAR(2)'
go
Sp_AddType 'TDATE',  'DATETIME'
go
Sp_AddType 'TUDF',    'VARCHAR(10)'
go
Sp_AddType 'TRECEIPT', 'CHAR(10)'
go
Sp_AddType 'TINVOICE','VARCHAR(12)'
go
Sp_AddType 'TBATCH',   'VARCHAR(20)'
go
Sp_AddType 'TDISCOUNT', 'NUMERIC(5,3)'
go
Sp_AddType 'TPRICE',     'FLOAT'
go
Sp_AddType 'TAMT',    'NUMERIC(12,2)'
go
Sp_AddType 'TQTY',    'NUMERIC(12,4)'
go
Sp_AddType 'TMEMO',   'VARCHAR(200)'
go

Create Default default_amt as 0
go
create default default_dis as 1
go
sp_bindefault default_amt,'TAMT'
go
SP_BINDEFAULT default_amt,'TPrice'
go
SP_BINDEFAULT default_amt,'TQTY'
go
SP_BINDEFAULT default_dis,'TDISCOUNT'
go

/* ============================================================ */
/*   Database name:  SYPOS                                      */
/*   DBMS name:      Microsoft SQL Server 7.x                   */
/*   Created on:     2000-12-15  14:23                          */
/* ============================================================ */

if exists (select 1
            from  sysobjects
           where  id = object_id('hRetail')
            and   type = 'U')
   drop table hRetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PackGoodsRetail')
            and   type = 'U')
   drop table PackGoodsRetail
go


if exists (select 1
            from  sysobjects
           where  id = object_id('AllocStat')
            and   type = 'U')
   drop table AllocStat
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ImportantGoodsRetail')
            and   type = 'U')
   drop table ImportantGoodsRetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('NewGoodsRetail')
            and   type = 'U')
   drop table NewGoodsRetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CategoryRetailStat')
            and   type = 'U')
   drop table CategoryRetailStat
go

if exists (select 1
            from  sysobjects
           where  id = object_id('VendorRetailStat')
            and   type = 'U')
   drop table VendorRetailStat
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RetailStat')
            and   type = 'U')
   drop table RetailStat
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CasherReport')
            and   type = 'U')
   drop table CasherReport
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PriceChange')
            and   type = 'U')
   drop table PriceChange
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Reqreturn')
            and   type = 'U')
   drop table Reqreturn
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DayStock')
            and   type = 'U')
   drop table DayStock
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RootFlow')
            and   type = 'U')
   drop table RootFlow
go

if exists (select 1
            from  sysobjects
           where  id = object_id('InvoiceFlow')
            and   type = 'U')
   drop table InvoiceFlow
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ChecksFlow')
            and   type = 'U')
   drop table ChecksFlow
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PosRetail')
            and   type = 'U')
   drop table PosRetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Retail')
            and   type = 'U')
   drop table Retail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DayReport')
            and   type = 'U')
   drop table DayReport
go

if exists (select 1
            from  sysobjects
           where  id = object_id('checksBackup')
            and   type = 'U')
   drop table checksBackup
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TotalStock')
            and   type = 'U')
   drop table TotalStock
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DeptStock')
            and   type = 'U')
   drop table DeptStock
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CheckLog')
            and   type = 'U')
   drop table CheckLog
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DeptRetail')
            and   type = 'U')
   drop table DeptRetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Tally')
            and   type = 'U')
   drop table Tally
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AdjustDetail')
            and   type = 'U')
   drop table AdjustDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Adjust')
            and   type = 'U')
   drop table Adjust
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SaleDetail')
            and   type = 'U')
   drop table SaleDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LossDetail')
            and   type = 'U')
   drop table LossDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Loss')
            and   type = 'U')
   drop table Loss
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Sale')
            and   type = 'U')
   drop table Sale
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AllocDetail')
            and   type = 'U')
   drop table AllocDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Alloc')
            and   type = 'U')
   drop table Alloc
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ImportDetail')
            and   type = 'U')
   drop table ImportDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Import')
            and   type = 'U')
   drop table Import
go

/* ============================================================ */
/*   Table: Import                                              */
/* ============================================================ */
create table Import
(
    ImportNo           TRECEIPT              not null,
    OrderNo            TRECEIPT              null    ,
    InputDate          TDATE                 null    
        default GETDATE(),
    AuditDate          TDATE                 null    
        default getdate(),
    DeptNO             TDEPT                 null    ,
    InputDept          TDEPT                 not null,
    Employee           TSTAFF                null    ,
    VendorNo           TVENDOR               null    ,
    InvoiceNo          TINVOICE              null    ,
    InvoiceDate        TDATE                 null    ,
    NTaxAmt            TAMT                  null    ,
    Amt                TAMT                  null    ,
    PayMode            TPAYMODE              null    ,
    Operator           TSTAFF                null    ,