step4.sql

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

/*
	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) not  NULL,
	Vendor_class char (1) NULL,
	Vendor_nm varchar (255) not null,
	TEL varchar (30)  NULL ,
	FAX varchar (30)  NULL ,
	cont1 varchar (30)  NULL ,
	cont2 varchar (30)  NULL ,
	Address varchar (255)  NULL ,
	Deft_Currency  CHAR(3) NULL,
	payterms varchar (125)  NULL ,
	Pay_Day int NULL ,
	website varchar (80)  NULL ,
	email varchar (80)  NULL ,
	Remark varchar (255) null,
	creation_dt datetime NULL, 
	created_by  varchar(15) not null,
	update_dt datetime not null default getdate(),
	updated_by varchar(15) not null,
	CONSTRAINT [PK_po_vendor_mst] PRIMARY KEY  CLUSTERED 
	(
		[item],
		[vendor_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO


insert into po_vendor_mstr
(
item,
Vendor_ID,
vendor_class,
vendor_nm,
TEL,
FAX,
cont1,
cont2,
Address,
payterms,
Pay_Day,
website,
email,
Remark,
creation_dt,
created_by,
update_dt,
updated_by
)
select 
item,
ID,
'V',
Description,
TEL,
FAX,
cont1,
cont2,
Address,
payterms,
Pay_Day,
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


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

Create table GL_Currency_Mstr
(
 Currency	CHAR(3) NOT NULL constraint PK_GL_Currency primary key,
 Description    varchar(30) null,
 lang           CHAR(2) NOT NULL DEFAULT 'EN'
)

go

insert into GL_Currency_Mstr
(
 Currency,
 Lang
)
select 
curunit,
'EN'
from stockmanage..ord_item
where isnull(curunit,'')<>'' group by curunit
order by curunit




-------------------------------------------------------------------------------------

IF OBJECT_ID('DBO.GL_SYS_Module') is not null
drop table DBO.GL_SYS_Module
go

create table GL_SYS_Module
(
  Module_ID  CHAR(4) NOT NULL PRIMARY KEY,
  Description VARCHAR(120) NOT NULL
)

INSERT INTO GL_SYS_Module
values
(
 '2000',
 'Purchasing Application'
)
go
------------------------------------------------------------------------------------
if object_id('dbo.GL_GUI_Component') is not null
drop table dbo.GL_GUI_Component
go

create table GL_GUI_Component
(
 Item  int identity(1,1) not null primary key,
 Module_ID CHAR(4) NOT NULL constraint fk_gl_gui references GL_SYS_Module(Module_ID),
 Form_ID   VARCHAR(30) NOT NULL,
 Component_Name VARCHAR(30) NOT NULL,
 Default_caption varchar(120) not null,
 EN        VARCHAR(250) null,
 ZH        VARCHAR(250) NULL,
 CH        VARCHAR(250) NULL
)

go

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

Create table GL_UoM_Mstr
(
  UoM_cd   CHAR(3) not null constraint pk_uom_mstr primary  key,
  Dim_cd   varchar(30) null,
  iso_cd   varchar(15) null,
  lang_cd  CHAR(3) not null default 'ENG',
  Description varchar(30) null,
  alt_nm    varchar(30) null
)
go

insert into GL_UoM_Mstr
(
	uom_cd,
	lang_cd
)
select 
	uom,
	'ENG'
from stockmanage..ord_item
	group by uom
go
-----------------------------------------------------------------------------------
if object_id('dbo.PO_Signature_List') is not null
drop table dbo.PO_Signature_List
go

create table PO_Signature_List
(
  Order_number varchar(30) not null primary key,
  Status       CHAR(1) NOT NULL DEFAULT 'A',
  Signatured   CHAR(1) NOT NULL DEFAULT 'N',
  Signatured_dt datetime not null default getdate(),
  Signatured_By VARCHAR(15) NOT NULL,
  Confirmed     CHAR(1) NOT NULL DEFAULT 'N',
  Confirmed_By  VARCHAR(15) NULL,
  Confirmed_dt  Datetime not null default getdate(),
  Approved      CHAR(1) NOT NULL DEFAULT 'N',
  Approved_By   VARCHAR(15) NULL,
  Approved_Dt   datetime not null default getdate()
)

go

insert into PO_Signature_List
(
 Order_number,
 Status,
 Signatured,
 Signatured_dt,
 Signatured_by,
 Confirmed,
 Confirmed_by,
 Confirmed_dt,
 Approved
)
select 
	pono,
	'A',
	'Y',
	getdate(),
	isnull(creator,'admin'),
	'Y',
	'Ricky',
	getdate(),
	'N'
	from ord_mst


 go
---------------------------------------------------------------------------------

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

create table User_Signature_Library
(
  userid varchar(15) not null,
  user_desc varchar(30) not null,
  signature_img image null
)
go

insert into User_Signature_Library
(
 userid,
 user_desc
)
select userid,user_nm from pu_user_mstr

go
-----------------------------------------------------------------------------------------------
if object_id('dbov.viw_MIS_GR_List') is not null
 drop view viw_MIS_GR_List
go

create  view viw_MIS_GR_List
as
select a.Pono,a.dn_no,a.fir_date as gr_date,a.cher as gr_by,b.ItemNo as Hpn,
b.Depot as Location,
b.Unit as UoM,
b.Rec_Qty
 from  base_recdepot a, base_recdepotc b
where a.add_id = b.single_id

go
-----------------------------------------------------------------------------

if object_id('dbo.Viw_Ord_For_GR') is not null
 drop view Viw_Ord_For_GR
go
create view Viw_Ord_For_GR
as
select a.vendor_nm,a.pono,a.prno,b.hpn,b.cpn,b.mpn,sum(quantity) as ord_qty
from ord_mst a,ord_item b where a.pono =b.pono
group by a.vendor_nm,a.pono,a.prno,b.hpn,b.cpn,b.mpn

go


----------------------------------------------------------------------

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

Create procedure Proc_Cancel_PO
  @Order_Number varchar(30) = NULL,
  @Action_type  VARCHAR(30) = NULL,
  @Action_by    varchar(15) = null,
  @Reason       varchar(250) = null,
  @Remark       varchar(250) =null
as

begin
      
	if not exists(select order_number from  Cancel_po_log where order_number =@order_number)
      begin
	insert into Cancel_po_log
	(
	 Order_Number,
	 Action_type,
	 Action_dt,
	 Action_by,
	 Reason,
	 Remark
	)
	values
	(
	 @Order_Number,
	 @Action_type,
	 getdate(),
	 @Action_by,
	 @Reason,
	 @Remark
	)
	if(@action_type ='C')
	begin
		update ord_mst 
			set status ='C'
		where pono = @order_number
	end
	else if(@action_type ='D')
	begin
		update ord_mst 
			set status ='D'
		where pono = @order_number	
	end
	else if(@action_type ='A')
	begin
		update ord_mst 
			set status ='O'
		where pono = @order_number	
	end
   end
end

go
--------------------------------------------------------------------------------------

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

Create procedure Proc_Get_GR_Amount
@action_cd CHAR(1) = NULL,
@order_number varchar(30) = null,
@Item          int = null
as
begin
	if(@action_cd ='A')
	begin
		select   isnull(Sum(quantity),0) as Order_Total,
		 isnull(sum(Received_qty),0) as GR_Total,
		 isnull(sum(Balance_qty),0) as Bal_Total
		from ord_item     where pono =@order_number  and  iscom='N'
	end
	  else
	begin
		select   isnull(Sum(quantity),0) as Order_Total,
		 isnull(sum(Received_qty),0) as GR_Total,
		 isnull(sum(Balance_qty),0) as Bal_Total
		from ord_item     where pono =@order_number
		and item =@item and iscom ='N'
	end


end
go

------------------------------------------------------------------------------

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

Create procedure Proc_Get_PO_Amount
@order_number varchar(30) = null
as
declare
	@discount numeric(18,3),
	@currency char(3)
begin
	
	select  @discount = isnull(discount,1) from ord_mst where 
	pono =@order_number
	
	

	select @currency = isnull(curunit,'HKD') from ord_item
    where 
	pono =@order_number
	group by curunit
	
	if(@discount>0) 
	select @discount = @discount/100.0
	else
	select @discount = 0.00
	begin
		select sum(quantity*unitprice) as Sub_total,
		isnull(sum(quantity*unitprice)*@discount,0) as Discount_total,
		(sum(quantity*unitprice) - (sum(quantity*unitprice)*@discount)) as GR_Total,
		  @currency as Currency,@discount as discount
		from ord_item     where pono =@order_number
	end
	
end
go

/*

 Proc_Get_PO_Amount 'PO-0005/03' 
 
 exec proc_Get_PO_Amount 'PO-0002/04'


*/


-------------------------------------------------------------------------------------------
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_PO_GR_Upd') is not null
drop procedure dbo.Proc_PO_GR_Upd
go

Create procedure Proc_PO_GR_Upd
@Order_Number   varchar(30) = null,
@Dn_number      varchar(30) = null,
@itemseq        int = null,
@item_code  int=0,
@GR_Qty    numeric(12,3)=0.00,
@in_date	datetime=null,
@vendor_id	char(8) = null,
@created_by	varchar(10) = null,
@last_upd_by    varchar(10) = null,
@action_cd	char(1)=null

as
declare 
	@maxitem int,
	@Order_Total numeric(18,2),
	@Bal_total numeric(18,2)
	
if (@action_cd ='N') 
begin
	select @Order_total = isnull(sum(Quantity),0),@Bal_total =  isnull(sum(Balance_Qty),0) from ord_item 
	where pono =@order_number and item = @item_code

	if(@GR_Qty <=@Bal_total) 
	begin
		select @maxitem = isnull(itemseq,0)+1 from gtr_item where pono =@order_number
	
	 insert into gtr_item(
	Pono,
	dnno,
	itemseq,
	item,
	in_quantity,
	in_date,
	created_by,
	created_dt,
	last_upd_by,
	last_upd_dt,
	hpn,
	cpn,
	mpn,
	item_nm,
	vendor_id

)
select 
	@order_number,
	@dn_number,
	@maxitem,
	item,
	@gr_qty,
	@in_date,
	@created_by,
	getdate(),
	@created_by,
	getdate(),
	hpn,
	cpn,
	mpn,
	material_nm,
	@vendor_id
	from ord_item where pono =@order_number and itemseq = @itemseq
end

	if(not exists(select pono from ord_item where pono=@order_number and iscom ='N'))
	begin
		update ord_mst 
			set completed='YES',
	   		 Status ='F'
		where pono= @order_number

	end
end

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

Create Procedure Proc_PO_Status
@Order_Number varchar(18) = null
as
begin
	select  isnull(sum(quantity),0) as Qty,
	isnull(sum(received_qty),0) as GR_Qty, 
	(isnull(sum(quantity),0) - isnull(sum(received_qty),0)) as Outstd_qty,
	case when  (sum(quantity) - sum(received_qty)<=0) then 'YES' else 'NO' end as Status
	from ord_item where pono =@order_number
	
end
go

-- Proc_PO_Status 'PO04--32223'
-----------------------------------------------------------------------------------------------



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

create procedure Proc_Sign_upd
@action_cd    CHAR(1) = NULL,
@Order_number  VARCHAR(30) = NULL,
@Signatured_by VARCHAR(15) = NULL,
@Confirmed_by  VARCHAR(15) = NULL,
@Approved_by   VARCHAR(15) = NULL
as
begin
	if (@action_cd = 'S')
	begin
		if not exists(select order_number from  PO_Signature_List where order_number = @order_number)
		begin
			insert into PO_Signature_List
			(
			Order_number,
                        Status,
		        Signatured,
			Signatured_dt,
			Signatured_By,
			Confirmed,
			Confirmed_By,
			Confirmed_dt,
			Approved,
			Approved_By,
			Approved_Dt
			)
			values
			(
			 @order_number,
			 'A',
			 'Y',
			 getdate(),
			 @signatured_by,
			 'N',
			 '',
			 Getdate(),
			 'N',
			 '',
			getdate()
			)
		 end
				
	end
		else if(@action_cd ='C')
		begin
			update PO_Signature_List
				set confirmed = 'Y',
				    confirmed_by = @confirmed_by,
				    confirmed_dt = getdate()
			where order_number = @order_number
		end
	else if(@action_cd = 'A')
	begin
		update PO_Signature_List
			set approved = 'Y',
			 approved_by = @approved_by,
			 Approved_dt = getdate()
		where order_number = @order_number
	end
end
go