?? finally_install_bk.sql
字號(hào):
delete from ord_item
where pono=@pono and itemseq=@itemseq
select @maxitem=(select isnull(max(itemseq),0) from ord_item where pono=@pono)
select @Recount=(select isnull(count(*),0) from ord_item where pono=@pono)
if(@itemseq<=@maxitem)
begin
if(@itemseq<>@maxitem) and (@Recount>=1)
begin
update ord_item
set itemseq=@itemseq
where pono=@pono and itemseq=@maxitem
end
else
begin
update ord_item
set itemseq=1
where pono=@pono and itemseq=@itemseq
end
end
update ord_mst
set completed = case when((select isnull(count(*),0) pono from ord_item where pono =@Pono and iscom ='N')>0) then 'NO' ELSE 'YES' End,
status = case when((select isnull(count(*),0) pono from ord_item where pono =@Pono and iscom ='N')>0) then 'O' ELSE 'F' End
where pono =@pono
select @Error_Code = @@Error
end
end
else
select @Error_Code = -7 ----Invalid Operation
end
select @Error_Code
end
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if object_id('dbo.Proc_Order_Revision') is not null
drop procedure dbo.Proc_Order_Revision
go
CREATE procedure Proc_Order_Revision
@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,
@Error_Code int = null output
as
begin
select @Error_Code = -1
if not exists(select pono from ord_mst where pono=@pono)
select @Error_Code = -2 --------not record found
else
begin
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.0
where pono=@pono
select @Error_Code = @@Error
end
select @Error_Code
end
go
-------------------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if object_id('dbo.Proc_Order_Revision') is not null
drop procedure dbo.Proc_Order_Revision
go
CREATE procedure Proc_Order_Revision
@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,
@Error_Code int = null output
as
begin
select @Error_Code = -1
if not exists(select pono from ord_mst where pono=@pono)
select @Error_Code = -2 --------not record found
else
begin
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.0
where pono=@pono
select @Error_Code = @@Error
end
select @Error_Code
end
go
------------------------------------------------------------------------------------------------------------------------
if object_id('dbo.Proc_Price_upd') is not null
drop procedure dbo.Proc_Price_upd
go
create Procedure Proc_Price_upd
@action_cd char(1) = null,
@item int = null,
@line int = null,
@vendor_id char(5) = null,
@MOQ numeric(18,2) = null,
@MPQ numeric(18,3) = null,
@Unit_Price varchar(53) = null,
@Effective_dt datetime = null,
@Expire_dt datetime = null,
@created_by varchar(15) = null,
@updated_by varchar(175) = null,
@status CHAR(1) = NULL,
@Return_ID Int = null output
as
declare
@maxitem int
begin
select @Return_ID = -1 --unknown error
if (@action_cd ='N')
begin
/*if exists(select item from po_price_mstr
where item = @item and moq = @moq and mpq = @mpq and
(effective_dt in(@effective_dt) or expire_dt in(@expire_dt))
)
select @Return_ID = -2 --Date already exists in database
else*/
begin
select @maxitem = isnull(max(Line),0)+1 from po_price_mstr where item = @item
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,
@maxitem,
Vendor_ID,
HPN,
CPN,
MPN,
Description,
Model,
Basic_UoM,
Vendor_UoM,
@MOQ,
@MPQ,
Currency,
convert(float,@Unit_Price),
@Effective_dt,
@Expire_dt,
getdate(),
getdate(),
@Created_by,
@updated_by,
'A'
from po_information_Record where item = @item
end
select @Return_ID = @@error
end
else if(@action_cd ='M')
begin
begin
insert into PO_Price_Log
(item,line,hpn,status,OLD_Price,New_Price,Modify_dt,Modified_by)
select item,line,hpn,status,unit_price,@unit_Price,getdate(),updated_by
from po_price_mstr where item = @item and line = @line
update po_price_mstr
set moq = @moq,
mpq = @mpq,
unit_Price = @unit_Price,
effective_dt = @effective_dt,
expire_dt = @expire_dt,
status = @status,
update_dt = getdate(),
updated_by = @updated_by
where item = @item and line = @line
select @Return_id = @@error
end
end
select @Return_id
end
go
--sp_help po_price_log
/*
alter table po_price_Mstr
add Line int not null default 1
*/
---------------------------------------------------------------------------------------------
if object_id('dbo.Proc_SrcList_upd') is not null
drop procedure dbo.Proc_SrcList_upd
go
create procedure Proc_SrcList_Upd
@action_cd char(1) = null,
@item int = null,
@vendor_id varchar(10) = null,
@vendor_nm varchar(250) = null,
@sug_vendor_id varchar(10) = null,
@sug_vendor_nm varchar(250) = null,
@HPN varchar(18) = null,
@MTL_TYPE_CD char(4) = null,
@Priority char(1) = null,
@Description varchar(250) = null,
@CPN varchar(32) = null,
@MPN varchar(32) = null,
@MODEL varchar(30) = null,
@Lead_Time int = null,
@MOQ int = null,
@MPQ int = null,
@Unit_Price varchar(53) = null,
@Currency char(3) = null,
@Item_Class_cd char(2) = null,
@Basic_UoM char(3) = null,
@Vendor_UoM char(3) = null,
@Customer_ID char(5) = null,
@Status CHAR(1) = NULL,
@effectived_dt datetime = null,
@Expiration_dt datetime = null,
@Created_by varchar(15) = null,
@Updated_by varchar(15) = null,
@Return_ID int = null output
as
declare
@maxitem int
begin
select @Return_ID = -1
if(@action_cd ='N')
begin
select @maxitem =isnull(max(item),0)+1 from po_information_Record
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 ,
Item_Class_cd ,
Basic_UoM ,
Vendor_UoM ,
Customer_ID ,
Status ,
effectived_dt ,
Expiration_dt ,
Created_by ,
Creation_dt ,
Updated_by ,
Update_dt
)
values
(
@maxitem ,
@vendor_id ,
@vendor_nm ,
@sug_vendor_id ,
@sug_vendor_nm ,
@HPN ,
@MTL_TYPE_CD ,
@Priority ,
@Description ,
@CPN ,
@MPN ,
@MODEL ,
@Lead_Time ,
@MOQ ,
@MPQ ,
convert(float,@Unit_Price) ,
@Currency ,
@Item_Class_cd ,
@Basic_UoM ,
@Vendor_UoM ,
@Customer_ID ,
'A' ,
@effectived_dt ,
@Expiration_dt ,
@Created_by ,
getdate() ,
@Updated_by ,
getdate()
)
select @Return_ID = @@Error
end
else if(@action_cd ='M')
begin
update PO_Information_Record
set
vendor_id = @vendor_id,
vendor_nm = @vendor_nm,
sug_vendor_id = @sug_vendor_id,
sug_vendor_nm = @sug_vendor_nm,
HPN = @HPN,
MTL_TYPE_CD = @MTL_TYPE_CD,
Priority = @Priority,
Description = @Description,
CPN = @CPN,
MPN = @MPN,
MODEL = @MODEL,
Lead_Time = @Lead_Time,
MOQ = @MOQ,
MPQ = @MPQ,
--Unit_Price = @Unit_Price,
--Currency = @Currency,
Item_Class_cd = @Item_Class_cd,
Basic_UoM = @Basic_UoM,
Vendor_UoM = @Vendor_UoM,
Customer_ID = @Customer_ID,
Status = @Status,
effectived_dt = @effectived_dt,
Expiration_dt = @Expiration_dt,
Updated_by = @Updated_by,
Update_dt = getdate()
where item = @item
update po_price_mstr
set vendor_id = @vendor_id,
hpn = @hpn,
description = @description,
cpn = @cpn,
mpn = @mpn,
model = @model,
currency = @currency,
basic_uom = @basic_uom,
vendor_uom = @vendor_uom,
status = @status
where item = @item
select @Return_ID = @@error
end
select @Return_ID
end
go
-----------------------------------------------------------------------------------------------------------------------------------
if object_id('dbo.proc_Vendor_Mstr_Upd') is not null
drop procedure dbo.Proc_Vendor_Mstr_Upd
go
create procedure Proc_Vendor_Mstr_Upd
@Action_cd CHAR(1) = NULL,
@Vendor_ID CHAR(8) = NULL,
@Vendor_class char(1) = null,
@Vendor_nm VARCHAR(250) = NULL,
@TEL VARCHAR(80) = NULL,
@FAX VARCHAR(80)= NULL,
@cont1 VARCHAR(80) = NULL,
@cont2 VARCHAR(80) = NULL,
@Address VARCHAR(250) = NULL,
@payterms VARCHAR(120) = NULL,
@Currency CHAR(3) = NULL,
@Pay_Day int = null,
@website varchar(120) = null,
@email varchar(120) = null,
@Remark varchar(120) = null,
@created_by varchar(15) = null,
@updated_by varchar(15) = null
as
if(@action_cd='N')
begin
declare
@newitem int ,
@new_vendor_id char(8)
select @newitem= isnull(max(item)+1,1) from PO_vendor_mstr
exec Proc_Get_Vendor_ID 'F',@new_vendor_id output
begin
insert into PO_Vendor_Mstr(
item,
Vendor_id,
Vendor_Class,
vendor_nm,
tel,
fax,
cont1,
cont2,
address,
payterms,
deft_currency,
pay_day,
website,
email,
Remark,
creation_dt,
created_by,
update_dt,
updated_by
)
values(
@newitem,
@new_vendor_id,
@vendor_class,
@vendor_nm,
@tel,
@fax,
@cont1,
@cont2,
@address,
@payterms,
@currency,
0,
@website,
@email,
@Remark,
getdate(),
@created_by,
getdate(),
@updated_by
)
return
end
end
if(@action_cd='M')
begin
update po_vendor_mstr
set
vendor_nm=@vendor_nm ,
vendor_class = @vendor_class,
tel=@tel,
fax=@fax,
cont1=@cont1,
cont2=@cont2,
address=@address,
payterms=@payterms,
deft_currency = @currency,
website=@website,
email=@email,
update_dt=getdate(),
updated_by = @updated_by,
remark = @remark
where vendor_id=@vendor_id
return
end
if (@action_cd='D')
begin
if exists(select * from po_vendor_mstr where vendor_id = @vendor_id)
begin
if not exists(select ID from mat_mst where id = @vendor_id)
delete from po_vendor_mstr where vendor_id=@vendor_id
else
print('cannot delete current vendor')
end
return
end
go
--------------------------------------------------------------------------------------------------------------
?? 快捷鍵說(shuō)明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -