his.sql

Delphi6开发的HIS程序

CREATE TABLE Inpatient(
    InpatientID         NUMBER(38, 0)    NOT NULL,
    LinkmanPhone        VARCHAR2(10),
    LinkmanAddress      VARCHAR2(64),
    LinkmanName         VARCHAR2(32),
    RPRPostcode         VARCHAR2(16),
    RPRAddress          VARCHAR2(64),
    WorkDeptPostcode    VARCHAR2(16),
    IDCardCode          VARCHAR2(32),
    Phone               VARCHAR2(32),
    WorkDepatCaption    VARCHAR2(32),
    Created             DATE              DEFAULT SYSDATE NOT NULL,
    Updated             DATE              DEFAULT SYSDATE NOT NULL,
    Memo                VARCHAR2(64),
    Prepay              NUMBER(16, 4)     DEFAULT 0 NOT NULL,
    Blance              NUMBER(16, 4)     DEFAULT 0 NOT NULL,
    FoodID              NUMBER(38, 0)    NOT NULL,
    SicknessLevelID     NUMBER(38, 0)    NOT NULL,
    BedID               NUMBER(38, 0)    NOT NULL,
    SickTypeID          NUMBER(38, 0)    NOT NULL,
    CountryID           NUMBER(38, 0)    NOT NULL,
    BloodTypeID         NUMBER(38, 0)    NOT NULL,
    PersonRelationID    NUMBER(38, 0)    NOT NULL,
    SickID              NUMBER(38, 0),
    ReckoningID         NUMBER(38, 0),
    SectionOfficeID     NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKInpatient PRIMARY KEY (InpatientID)
) 
;



-- 
-- TABLE: Job 
--

CREATE TABLE Job(
    JobID      NUMBER(38, 0)    NOT NULL,
    Caption    VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKJob PRIMARY KEY (JobID)
) 
;



-- 
-- TABLE: Manufacturer 
--

CREATE TABLE Manufacturer(
    ManufacturerID    NUMBER(38, 0)    NOT NULL,
    Caption           VARCHAR2(128)    NOT NULL,
    Address           VARCHAR2(256),
    AreaID            NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKManufacturer PRIMARY KEY (ManufacturerID)
) 
;



-- 
-- TABLE: Material 
--

CREATE TABLE Material(
    MaterialID           NUMBER(38, 0)    NOT NULL,
    Code                 VARCHAR2(32)     NOT NULL,
    Alias                VARCHAR2(32)     NOT NULL,
    Memo                 VARCHAR2(64),
    Model                VARCHAR2(32),
    PinYin               VARCHAR2(32)     NOT NULL,
    Caption              VARCHAR2(32)     NOT NULL,
    DoseID               NUMBER(38, 0),
    UnitsID              NUMBER(38, 0)    NOT NULL,
    MaterialCatalogID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKMaterial PRIMARY KEY (MaterialID)
) 
;



-- 
-- TABLE: MaterialCatalog 
--

CREATE TABLE MaterialCatalog(
    MaterialCatalogID    NUMBER(38, 0)    NOT NULL,
    Code                 VARCHAR2(16)     NOT NULL,
    ParentID             NUMBER(38, 0),
    ChargeCatalogCode    VARCHAR2(8)      NOT NULL,
    PinYin               VARCHAR2(32)     NOT NULL,
    Caption              VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKMaterialCatalog PRIMARY KEY (MaterialCatalogID)
) 
;



-- 
-- TABLE: MaterialStock 
--

CREATE TABLE MaterialStock(
    MaterialStockID    NUMBER(38, 0)    NOT NULL,
    DownQty            NUMBER(16, 4),
    TopQty             NUMBER(16, 3),
    MinAmount          NUMBER(16, 4)    NOT NULL,
    Amount             NUMBER(16, 4)    NOT NULL,
    MinModel           VARCHAR2(32)     NOT NULL,
    Created            DATE              DEFAULT SYSDATE NOT NULL,
    Updated            DATE              DEFAULT SYSDATE NOT NULL,
    MinUnitsCode       NUMBER(38, 0)    NOT NULL,
    MaterialID         NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKMaterialStock PRIMARY KEY (MaterialStockID)
) 
;



-- 
-- TABLE: MaterialStockDetail 
--

CREATE TABLE MaterialStockDetail(
    MaterialStockDetailID    NUMBER(38, 0)    NOT NULL,
    Amount                   NUMBER(16, 4)    NOT NULL,
    Created                  DATE              DEFAULT SYSDATE NOT NULL,
    Updated                  DATE              DEFAULT SYSDATE NOT NULL,
    MaterialStockID          NUMBER(38, 0)    NOT NULL,
    SIOMaterialDetailID      NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKMaterialStockDetail PRIMARY KEY (MaterialStockDetailID)
) 
;



-- 
-- TABLE: Medicine 
--

CREATE TABLE Medicine(
    MedicineID           NUMBER(38, 0)    NOT NULL,
    Code                 VARCHAR2(32)     NOT NULL,
    TopQty               NUMBER(16, 4),
    DownQty              NUMBER(16, 4),
    Memo                 VARCHAR2(64),
    Alais                VARCHAR2(32),
    Model                VARCHAR2(32),
    PinYin               VARCHAR2(32)     NOT NULL,
    Caption              VARCHAR2(32)     NOT NULL,
    DoseID               NUMBER(38, 0),
    UnitsID              NUMBER(38, 0)    NOT NULL,
    UseMethodID          NUMBER(38, 0),
    MedicineCatalogID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKMedicine PRIMARY KEY (MedicineID)
) 
;



-- 
-- TABLE: MedicineCatalog 
--

CREATE TABLE MedicineCatalog(
    MedicineCatalogID    NUMBER(38, 0)    NOT NULL,
    Code                 VARCHAR2(16)     NOT NULL,
    ParentID             NUMBER(38, 0),
    ChargeCatalogCode    VARCHAR2(8)      NOT NULL,
    PinYin               VARCHAR2(32)     NOT NULL,
    Caption              VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKMedicineCatalog PRIMARY KEY (MedicineCatalogID)
) 
;



-- 
-- TABLE: MedicineStock 
--

CREATE TABLE MedicineStock(
    MedicineStockID    NUMBER(38, 0)    NOT NULL,
    Created            DATE              DEFAULT SYSDATE NOT NULL,
    Updated            DATE              DEFAULT SYSDATE NOT NULL,
    MinModel           VARCHAR2(32)     NOT NULL,
    Amount             NUMBER(16, 4),
    MinAmount          NUMBER(16, 4),
    MinUnitsCode       NUMBER(38, 0),
    MedicineID         NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKMedicineStock PRIMARY KEY (MedicineStockID)
) 
;



-- 
-- TABLE: MedicineStockDetail 
--

CREATE TABLE MedicineStockDetail(
    MedicineStockDetailID    NUMBER(38, 0)    NOT NULL,
    Amount                   NUMBER(16, 4)    NOT NULL,
    Created                  DATE              DEFAULT SYSDATE NOT NULL,
    Updated                  DATE              DEFAULT SYSDATE NOT NULL,
    MedicineStockID          NUMBER(38, 0)    NOT NULL,
    SIOMedicineDetailID      NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKMedicineStockDetail PRIMARY KEY (MedicineStockDetailID)
) 
;



-- 
-- TABLE: Nation 
--

CREATE TABLE Nation(
    NationID    NUMBER(38, 0)    NOT NULL,
    Caption     VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKNation PRIMARY KEY (NationID)
) 
;



-- 
-- TABLE: NativePlace 
--

CREATE TABLE NativePlace(
    NativePlaceID    NUMBER(38, 0)    NOT NULL,
    ParentID         NUMBER(38, 0),
    Caption          VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKNativePlace PRIMARY KEY (NativePlaceID)
) 
;



-- 
-- TABLE: Operation 
--

CREATE TABLE Operation(
    OperationID           NUMBER(38, 0)    NOT NULL,
    Code                  VARCHAR2(32)     NOT NULL,
    PinYin                VARCHAR2(32)     NOT NULL,
    Charge                NUMBER(16, 4),
    Caption               VARCHAR2(32)     NOT NULL,
    OperationCatalogID    NUMBER(38, 0),
    CONSTRAINT PKOperation PRIMARY KEY (OperationID)
) 
;



-- 
-- TABLE: OperationCatalog 
--

CREATE TABLE OperationCatalog(
    OperationCatalogID    NUMBER(38, 0)    NOT NULL,
    ParentID              NUMBER(38, 0),
    Code                  VARCHAR2(16)     NOT NULL,
    ChargeCatalogCode     VARCHAR2(8)      NOT NULL,
    PinYin                VARCHAR2(32)     NOT NULL,
    Caption               VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKOperationCatalog PRIMARY KEY (OperationCatalogID)
) 
;



-- 
-- TABLE: Param 
--

CREATE TABLE Param(
    ParamID       NUMBER(38, 0)    NOT NULL,
    ParamName     VARCHAR2(32)     NOT NULL,
    ParamValue    VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKParam PRIMARY KEY (ParamID)
) 
;



-- 
-- TABLE: PaymentMethod 
--

CREATE TABLE PaymentMethod(
    PaymentMethodID    NUMBER(38, 0)    NOT NULL,
    Caption            VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKPaymentMethod PRIMARY KEY (PaymentMethodID)
) 
;



-- 
-- TABLE: PCOMaterial 
--

CREATE TABLE PCOMaterial(
    PCOMaterialID            NUMBER(38, 0)    NOT NULL,
    ApproveDate              DATE,
    NewPrice                 NUMBER(16, 4)    NOT NULL,
    OldPrice                 NUMBER(16, 4)    NOT NULL,
    Created                  DATE              DEFAULT SYSDATE NOT NULL,
    Updated                  DATE              DEFAULT SYSDATE NOT NULL,
    IsApprove                NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    CreateBy                 NUMBER(38, 0)    NOT NULL,
    UpdateBy                 NUMBER(38, 0),
    ApproveBy                NUMBER(38, 0),
    MaterialStockDetailID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKPCOMaterial PRIMARY KEY (PCOMaterialID)
) 
;



-- 
-- TABLE: PCOMedicine 
--

CREATE TABLE PCOMedicine(
    PCOMedicineID            NUMBER(38, 0)    NOT NULL,
    ApproveDate              DATE,
    NewPrice                 NUMBER(16, 4)    NOT NULL,
    OldPrice                 NUMBER(16, 4)    NOT NULL,
    Created                  DATE              DEFAULT SYSDATE NOT NULL,
    Updated                  DATE              DEFAULT SYSDATE NOT NULL,
    IsApprove                NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    MedicineStockDetailID    NUMBER(38, 0)    NOT NULL,
    CreateBy                 NUMBER(38, 0)    NOT NULL,
    UpdateBy                 NUMBER(38, 0),
    ApproveBy                NUMBER(38, 0),
    CONSTRAINT PKPCOMedicine PRIMARY KEY (PCOMedicineID)
) 
;



-- 
-- TABLE: PersonRelation 
--

CREATE TABLE PersonRelation(
    PersonRelationID    NUMBER(38, 0)    NOT NULL,
    Caption             VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKPersonRelation PRIMARY KEY (PersonRelationID)
) 
;



-- 
-- TABLE: POMaterialDetail 
--

CREATE TABLE POMaterialDetail(
    POMaterialDetailID    NUMBER(38, 0)    NOT NULL,
    PSN                   VARCHAR2(32),
    IsActive              NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Price                 NUMBER(16, 4)    NOT NULL,
    Amount                NUMBER(16, 4)    NOT NULL,
    CheckInAmount         NUMBER(16, 4)     DEFAULT 0 NOT NULL,
    Total                 NUMBER(16, 4)    NOT NULL,
    PurchaseOrderID       NUMBER(38, 0)    NOT NULL,
    MaterialID            NUMBER(38, 0)    NOT NULL,
    ProviderID            NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKPOMaterialDetail PRIMARY KEY (POMaterialDetailID)
) 
;



-- 
-- TABLE: POMedicineDetail 
--

CREATE TABLE POMedicineDetail(
    POMedicineDetailID    NUMBER(38, 0)    NOT NULL,
    PSN                   VARCHAR2(32),
    IsActive              NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    CheckInAmount         NUMBER(16, 4)     DEFAULT 0 NOT NULL,
    Price                 NUMBER(16, 4)    NOT NULL,
    Amount                NUMBER(16, 4)    NOT NULL,
    Total                 NUMBER(16, 4)    NOT NULL,
    PurchaseOrderID       NUMBER(38, 0)    NOT NULL,
    MedicineID            NUMBER(38, 0)    NOT NULL,
    ProviderID            NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKPOMedicineDetail PRIMARY KEY (POMedicineDetailID)
) 
;



-- 
-- TABLE: PrepayHistory 
--

CREATE TABLE PrepayHistory(
    InpatientID       NUMBER(38, 0)    NOT NULL,
    PrepayMethodID    NUMBER(38, 0)    NOT NULL,
    WarrentCode       VARCHAR2(16)     NOT NULL,
    IsPrinted         NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    Charge            NUMBER(16, 4)    NOT NULL,
    Created           DATE              DEFAULT SYSDATE NOT NULL,
    Updated           DATE              DEFAULT SYSDATE NOT NULL,
    CreateBy          NUMBER(38, 0)    NOT NULL,
    UpdateBy          NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKPrepayHistory PRIMARY KEY (InpatientID)
) 
;



-- 
-- TABLE: PrepayMethod 
--

CREATE TABLE PrepayMethod(
    PrepayMethodID    NUMBER(38, 0)    NOT NULL,
    Caption           VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKPrepayMethod PRIMARY KEY (PrepayMethodID)
) 
;



-- 
-- TABLE: Profession 
--

CREATE TABLE Profession(
    ProfessionID    NUMBER(38, 0)    NOT NULL,
    Caption         VARCHAR2(32)     NOT NULL,
    CONSTRAINT PKProfession PRIMARY KEY (ProfessionID)
) 
;



-- 
-- TABLE: Provider 
--

CREATE TABLE Provider(
    ProviderID    NUMBER(38, 0)    NOT NULL,
    Address       VARCHAR2(256),
    Caption       VARCHAR2(64)     NOT NULL,
    AreaID        NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKProvider PRIMARY KEY (ProviderID)
) 
;



-- 
-- TABLE: PSCOMedicine 
--

CREATE TABLE PSCOMedicine(
    PSCOMedicineID      NUMBER(38, 0)    NOT NULL,
    CQuantity           NUMBER(16, 4)    NOT NULL,
    OAmount             NUMBER(16, 4)    NOT NULL,
    Created             DATE              DEFAULT SYSDATE NOT NULL,
    Updated             DATE              DEFAULT SYSDATE NOT NULL,
    IsApprove           NUMBER(5, 0)      DEFAULT 0 NOT NULL,
    ApproveDate         DATE,
    PStockMedicineID    NUMBER(38, 0)    NOT NULL,
    CreateBy            NUMBER(38, 0)    NOT NULL,
    UpdateBy            NUMBER(38, 0),
    ApproveBy           NUMBER(38, 0),
    CONSTRAINT PKPSCOMedicine PRIMARY KEY (PSCOMedicineID)
) 
;



-- 
-- TABLE: PSIODetail 
--

CREATE TABLE PSIODetail(
    PSIODetailID       NUMBER(38, 0)    NOT NULL,
    Amount             NUMBER(16, 4)    NOT NULL,
    PStockInOrderID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKPSIODetail PRIMARY KEY (PSIODetailID)
) 
;



-- 
-- TABLE: PSOODetail 
--

CREATE TABLE PSOODetail(
    PSOODetailID        NUMBER(38, 0)    NOT NULL,
    Price               NUMBER(16, 4)    NOT NULL,
    Total               NUMBER(16, 4)    NOT NULL,
    Amount              NUMBER(16, 4)    NOT NULL,
    PStockOutOrderID    NUMBER(38, 0)    NOT NULL,
    CONSTRAINT PKPSOODetail PRIMARY KEY (PSOODetailID)
) 
;



-- 
-- TABLE: PStockInOrder 
--

CREATE TABLE PStockInOrder(
    PStockInOrderID     NUMBER(38, 0)    NOT NULL,