step1.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

alter table ord_mst 
	add Customer_ID CHAR(5) NULL,
	currency CHAR(3) NOT NULL default 'HKD',
	Payterms varchar(120) NOT NULL default 'C.O.D',
	Printed char(1) not null default 'N',
	Printed_By VARCHAR(15) Not null default 'Admin',
 	Buyer_Sig Char(1) NOT NULL Default 'N',
	Signatured_by VARCHAR(15) NULL,
	Signatured_dt datetime not null default getdate(),
	Confirmed_Sig CHAR(1) NOT NULL DEFAULT 'N',
	Confirmed_by  VARCHAR(15) NULL,
	Confirmed_dt  datetime not null default getdate(),
	Approved_Sig Char(1) NOT NULL Default 'N',
	Approved_By  VARCHAR(15) NULL,
	Approved_dt  datetime not null default Getdate(),
	Closed_Flag         CHAR(1) NOT NULL Default 'N',
	Closed_By   VARCHAR(15) NULL,
	Closed_Dt   Datetime    not null default Getdate()                               
	
go


/*-------------------------------------------------------
   alter table Ord_mst
	alter Column  status char(1)  not null 

************* need to manually alter the table  ord_mst 'status field ***************
*/



alter table  ord_item
       add--rice_effective_dt datetime null,
       --ice_expiration_dt datetime null,
       mtl_type_cd char(4) not null default 'RMAT'
go

update ord_item  
set price_effective_dt  = created_dt,
    price_expiration_dt = '2012-12-31'
go


if object_id('dbo.PO_Adjust_Item') is not null
drop table dbo.PO_Adjust_Item
go

   Create table PO_Adjust_Item
 (
     Order_Number varchar(30) constraint pk_po_adjust primary key,
     Force_Completed CHAR(1) NOT NULL Default 'N',
     Completed_by    varchar(15) null,
     Completed_dt    datetime not null default getdate(),
     Completed_Reason VARCHAR(120) NULL
)
go


if object_id('dbo.PO_Adjust_Line') is not null
drop table dbo.PO_Adjust_Line
go

create table PO_Adjust_Line
(
  Order_Number  varchar(30) not null constraint fk_po_adjust_line references PO_Adjust_Item(Order_number),
  Item          int not null,
  Item_code     int,
  HPN		varchar(18) null,
  CPN           varchar(32) null,
  Description   varchar(250) not null,
  UoM           CHAR(3) NULL,
  Ord_Qty       Numeric(18,2) not null default 0.00,
  Adj_Ord_Qty   numeric(18,2) not null default 0.00,
  GR_Qty        numeric(18,2) not null default 0.00,
  Adj_GR_Qty    Numeric(18,2) not null default 0.00,
  Balance_Qty   numeric(18,2) not null default 0.00,
  Adj_Bal_QTY   numeric(18,2) not null default 0.00,
  iscom         CHAR(1)   not null default 'N',
  Adjust_dt     datetime not null default getdate(),
  Adjusted_by   varchar(15) not null
)

go