finally_install.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




insert into PO_Information_Record
(
item,
vendor_id,
vendor_nm,
sug_vendor_id,
sug_vendor_nm,
HPN,
MTL_TYPE_CD,
Priority,
Description,
CPN,
MPN,
MODEL,
Lead_Time,
MOQ,
MPQ,
Unit_Price,
Currency,
Basic_UoM,
Vendor_UoM,
Status,
effectived_dt,
Expiration_dt,
Created_by,
Creation_dt,
Updated_by,
Update_dt
)
select 
item,
id,
vendor_nm,
id,
vendor_nm,
partnum,
'RMAT',
'H',
material_nm,
cpn,
matr_code,
model,
lead_time,
moq,
mpq,
unitprice,
isnull(curunit,'RMB'),
isnull(Uom,'PCS'),
isnull(Uom,'PCS'),
'A',
getdate(),
convert(datetime,'2012-12-31'),
isnull(create_by,'admin'),
isnull(create_dt,getdate()),
isnull(update_by,'admin'),
isnull(last_up,getdate())
from mat_mst order by item
go




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


CREATE TABLE PO_Information_Record
(
item		int not null constraint pk_po_item_mstr primary key,
vendor_id	CHAR(8) NOT NULL,
vendor_nm	VARCHAR(120) NOT NULL,
sug_vendor_id   CHAR(8) NULL,
sug_vendor_nm	VARCHAR(120) NOT NULL,
HPN		VARCHAR(18) NULL,
MTL_TYPE_CD	CHAR(4) NOT NULL DEFAULT 'RMAT',
Priority	CHAR(1) NOT NULL DEFAULT 'H',
Description	VARCHAR(255) NOT NULL,
CPN		VARCHAR(32) NULL,
MPN		VARCHAR(32) NULL,
MODEL		VARCHAR(80) NULL,
Lead_Time	int not null default 0,
MOQ		int not null default 0,
MPQ		int not null default 0,
Unit_Price	float not null default 0.0000001,
Currency	CHAR(3) NOT NULL DEFAULT 'RMB',
Item_Class_cd	CHAR(2) NULL,
Basic_UoM	CHAR(3) NOT NULL DEFAULT 'PCS',
Vendor_UoM	CHAR(3) NOT NULL DEFAULT 'PCS',
Customer_ID	CHAR(5) NULL,
Status          CHAR(1) NOT NULL DEFAULT 'A',
effectived_dt   datetime not null default getdate(),
Expiration_dt   Datetime not null default Getdate(),
Created_by	VARCHAR(15) NOT NULL,
Creation_dt	datetime not null default getdate(),
Updated_by	VARCHAR(15) NOT NULL ,
Update_dt	Datetime not null default getdate()
)
go

delete from mat_mst where material_nm  is null
go

update mat_mst 
	set vendor_nm = b.description
from mat_mst a,ven_cust_mst b
where a.id = b.id and a.vendor_nm is null
go



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

create view mat_mst 
as
select 
item,
vendor_id as id,
vendor_Nm,
hpn as partnum,
cpn,
mpn as matr_code,
description as material_nm,
model,
unit_price as unitprice,
currency as curunit,
moq,
mpq,
basic_uom as Uom,
lead_time,
creation_dt as create_dt,
created_by as create_by,
update_dt as last_upd_dt,
update_dt as last_up,
updated_by as update_by,
mtl_type_cd as matl_typ,
item_class_cd
from po_information_record

go 

-----------------------------------------------------------------------------------------------------------------------
if object_id('dbo.trg_Source_List_new') is not null
drop trigger dbo.trg_Source_List_New
go


create trigger trg_Source_List_new
on dbo.PO_Information_Record
for insert
as
begin
	insert into po_price_mstr
(item,
 Line,
 vendor_id,
 hpn,
 cpn,
 mpn,
 Description,
 model,
 Basic_uom,
 vendor_uom,
 moq,
 mpq,
 currency,
 unit_price,
 effective_dt,
 expire_dt,
 creation_dt,
 update_dt,
 created_by,
 updated_by,
 status
)
select 
 item,
 1,
 vendor_id,
 hpn,
 cpn,
 mpn,
 Description,
 model,
 Basic_uom,
 vendor_uom,
 moq,
 mpq,
 currency,
 unit_price,
 getdate(),
 expiration_dt,
 getdate(),
 getdate(),
 created_by,
 updated_by,
 status
from inserted
end
----------------------------------------------------------------------------
go

---先更屏蔽Identity off---

insert into po_price_mstr
(
item,
Line,
Vendor_id,
HPN,
CPN,
MPN,
Description,
Model,
Basic_uom,
Vendor_uom,
MOQ,
MPQ,
Currency,
Unit_Price,
Effective_dt,
Expire_dt,
Creation_dt,
Update_dt,
Created_by,
Updated_by
)
select 

item,
1,
vendor_id,
HPN,
CPN,
MPN,
Description,
MODEL,
Basic_UoM,
Vendor_UoM,
MOQ,
MPQ,
Currency,
Unit_Price,
getdate(),
Expiration_dt,
getdate(),
getdate(),
'admin',
'admin'
from po_information_record 

go

-------------------------------------------------------------------------------------------------
/*
	change ord_mst  status from int to char(1) default 'O'
*/

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

CREATE TABLE PO_Vendor_Mstr (
	item int NOT NULL ,
	Vendor_ID char (8) Constraint pk_Vendor_id primary key  not NULL,
	Status    Char(1) null default 'A',
	title	  varchar(30) not null default 'Company',	
	Vendor_class char (1) NULL,
	Vendor_Type_cd CHAR(2) NOT NULL default '01',
	Vendor_nm varchar (255) not null,
	Vendor_nm1 varchar(255) null,	
	search_terms1 varchar(30) null,
	search_terms2 varchar(30) null,
	street 	      varchar(120) null,
	house_number  varchar(30) null,
	postal_code   varchar(7) null,
	city	      varchar(30) null,
	country	      varchar(30) null,
	language      varchar(30) null,
	TEL varchar (30)  NULL ,
	TEL1 varchar(30)   null,
	FAX varchar (30)  NULL ,
	FAX1 varchar(30) null,
	extersion   varchar(30) null,
	mobile_phone1 varchar(30) null,
	mobile_phone2 varchar(30) null,
	cont1 varchar (30)  NULL ,
	cont2 varchar (30)  NULL ,	
	address  varchar(255) not null,
	address1  varchar(255) null,
	address2  varchar(255) null,
	deft_Address varchar (255)  NULL ,
	Deft_Currency  CHAR(3) NULL,
	Mult_Currency  bit null default 0,
	Currency1      CHAR(3) null,
	Currency2      CHAR(3) null,
	inv_verify_flag bit null default 0,
	MOQ	       Numeric(18,3) default 0.00,
	Deliv_Plant    CHAR(4) null,
	Pur_org	       CHAR(4) null,
	Plant	       CHAR(4) NULL,
	Pur_grp	       CHAR(4) NULL,
	payterms varchar (125)  NULL ,
	Deliv_Local VARCHAR(80) NULL,
	Account_no	VARCHAR(8) NULL,
	discount	numeric(18,3) null default 0.00,
	handling_chrg   numeric(18,3) null default 0.00,
	website varchar (80)  NULL ,
	email varchar (80)  NULL ,
	Remark varchar (255) null,
        Controller varchar(15) null,
	creation_dt datetime NULL, 
	created_by  varchar(15) not null,
	update_dt datetime not null default getdate(),
	updated_by varchar(15) not null)
GO


insert into po_vendor_mstr
(
item,
Vendor_ID,
vendor_class,
vendor_nm,
TEL,
FAX,
cont1,
cont2,
Address,
deft_address,
payterms,
website,
email,
Remark,
creation_dt,
created_by,
update_dt,
updated_by
)
select 
item,
ID,
'V',
Description,
TEL,
FAX,
cont1,
cont2,
Address,
Address,
payterms,
website,
email,
bz,
Updatetime,
'admin',
getdate(),
'admin'
from ven_cust_mst 

go



   update ord_mst
	  set status = 'O'
	  where completed ='NO'
go

    update Ord_mst 
	   set status ='F'
	   where completed ='YES'
go


update ord_mst
	set currency = b.curunit,
	Printed ='Y'
 from ord_mst a,(select pono,curunit from ord_item  where isnull(curunit,'')<>'' 
group by pono,curunit ) b
where a.pono = b.pono

go

update ord_mst 
	set payterms = b.payterms
from ord_mst a,po_vendor_mstr b
where a.id = b.vendor_id

go

------------------------------------------------------------------------------------------------
alter table gtr_item
drop column  total_quantity,
balance,
Remark,
uom,
vendor_nm,
ord_item_seq

go







------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





ALTER     procedure Ord_mst_Rev
@pono		varchar(30)=null,
@prno		varchar(80)=null,
@discount 	numeric(4,2)=null,
@payterms	varchar(120) = null,
@deli_loc	varchar(80)=null,
@customer_ID    CHAR(5) = NULL,
@cust_nm        varchar(15)= null,
@remark		varchar(255)=null,
@rev_date	datetime=null,
@revsion_by	varchar(12) = null
as
begin
if exists(select * from ord_mst where pono=@pono)
update ord_mst
set
	prno=@prno,
	discount=@discount,
	payterms = @payterms,
	deli_loc=@deli_loc,
	customer_id = @customer_ID,
        cust_nm = @cust_nm,
	remark=@remark,
	rev_date=getdate(),
	revsion_by = @revsion_by,
	version = version + 1
where pono=@pono

if(@@error<>0)
print('update error!')
return
end










 if object_id('dbo.Proc_Get_PO_Number') is not null
drop procedure dbo.Proc_Get_PO_Number
go


create     procedure Proc_Get_PO_Number
@flag        char(1) = null,    --------temporary or post to table Y/N
@identify    varchar(4)  = null, 
@who_used    varchar(15) = null,
@Ord_num_out varchar(12) = null output
as
declare 
@Order_num varchar(12),
@ID  char(4),
@Year char(2),
@maxitem int,
@temp_num varchar(4)
begin

	
	if(upper(@identify)='POM') 
	begin
	select @id = 'POM'
	select @maxitem =  isnull(Number,1) from PO_Order_Number where IDEN='POM' 
	end
	else if (@identify ='POL')
	begin
		select @id = 'POL'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='POL' 
	end
	else if(@identify='POML')
        begin
		select @id = 'POML'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='POML' 
	end
	else
	begin
		select @id = 'PO'
		select @maxitem =  isnull(Number,1) from   PO_Order_Number where IDEN='PO' 
	end
 	select @Year = isnull(right(convert(char(4),datepart(YY,getdate())),2),'04')
	--select @id = isnull(IDEN,'PO') from order_number
	select @temp_num = convert(char(4),@maxitem)
 	
	if(@identify = 'POML') 
 	select @Order_num =rtrim(ltrim(@ID))+@year+'-'+ REPLICATE('0',4-len(@temp_num))+@temp_num
	else
	 	select @Order_num =rtrim(ltrim(@ID))+@year+'-'+ REPLICATE('0',5-len(@temp_num))+@temp_num
	select @Ord_num_out = @Order_num

    	if(upper(@flag) = 'Y') 
	begin
		begin 	transaction
	
			if(upper(@identify) ='POM')
			begin	
				update po_order_number
				set Number = Number + 1,
		 		  last_number = @ord_num_out 
				 where IDEN='POM' 
			end
			else if(@identify = 'POL')
			begin
				update po_order_number
				set Number = Number + 1,
				last_number = @ord_num_out
				where IDEN='POL'
			end
			else if(@identify = 'POML')
			begin
				update po_order_number
					set Number = Number + 1,
					last_number = @ord_num_out
					where IDEN='POML'
			end
			else
			begin
				update po_order_number
					set Number = Number + 1,
					last_number = @ord_num_out
					where IDEN='PO'
			end
		 	
			insert into po_number_list
			(order_number,who_used,used_dt)
			values(@ord_num_out,@who_used,getdate())
	
	if(@@error  =0) 
	commit
	else 
	rollback
	
	end
	select @Ord_num_out
end

go






if object_id('dbo.Proc_Ord_Qty_Add') is not null
drop procedure dbo.Proc_Ord_Qty_Add
go

Create Procedure Proc_Ord_Qty_Add
@pono varchar(30) = null,
@Qty  numeric(18,2) = 0.0,
@itemseq int	= null
as
declare
  @old_qty numeric(18,2) 

  select @old_qty = isnull(quantity,0) from ord_item
	where pono =@pono and itemseq =@itemseq and iscom ='Y'

  if(@Qty >@old_qty)
 begin
	update ord_item
		set iscom ='N'
	where pono =@pono and itemseq =@itemseq
 end

 if exists(select  pono from ord_mst where pono =@pono
 and completed ='YES' or status = 'F')
 begin
	update ord_mst 
		set completed ='NO',
		    status = 'O'
	where pono =@pono
 end

go