proc_imissue_list.sql

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

if object_id('dbo.Proc_ImIssue_List') is not null
drop procedure dbo.Proc_ImIssue_List
go
Create procedure Proc_ImIssue_List
@plant  char(4) = null,
@frm_dt datetime = '1900-01-01',
@end_dt datetime = null
as
begin
	if(isnull(@plant,'')<>'') and (@frm_dt<>'1900-01-01') 
	begin
		select 
a.Item,
a.Plant,
a.Version,
a.Status,
a.Priority,
a.IM_DT as [IM. Date],
a.Vendor_id as [Vendor ID],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.DN_Number as [DN No.],
a.Order_Number as [PO NO.],
a.Buyer,
a.Report_dt as [Report Date],
a.Rcv_dt as [Rcv Date],
a.Read_flag as [Read Flag],
a.Reply_flag as [Reply Flag],
a.Reply_dt as [Reply Date],
a.Reply_by as [Replied By],
a.Remark,
a.Created_by as [Created By],
a.Creation_dt as [Creation Date],
a.Updated_by as [Updated By],
a.Update_dt as [Update Date]
from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type
and a.plant=@plant
		and a.im_dt between @frm_dt and @end_dt
	end
	else if(isnull(@plant,'')<>'') and  (@frm_dt='1900-01-01') 
	begin
		select 
a.Item,
a.Plant,
a.Version,
a.Status,
a.Priority,
a.IM_DT as [IM. Date],
a.Vendor_id as [Vendor ID],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.DN_Number as [DN No.],
a.Order_Number as [PO NO.],
a.Buyer,
a.Report_dt as [Report Date],
a.Rcv_dt as [Rcv Date],
a.Read_flag as [Read Flag],
a.Reply_flag as [Reply Flag],
a.Reply_dt as [Reply Date],
a.Reply_by as [Replied By],
a.Remark,
a.Created_by as [Created By],
a.Creation_dt as [Creation Date],
a.Updated_by as [Updated By],
a.Update_dt as [Update Date]
from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type
and a.plant=@plant
	end
	else 
		if(isnull(@plant,'')='') and (isnull(@frm_dt,'')<>'') 
	begin
		select 
a.Item,
a.Plant,
a.Version,
a.Status,
a.Priority,
a.IM_DT as [IM. Date],
a.Vendor_id as [Vendor ID],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.DN_Number as [DN No.],
a.Order_Number as [PO NO.],
a.Buyer,
a.Report_dt as [Report Date],
a.Rcv_dt as [Rcv Date],
a.Read_flag as [Read Flag],
a.Reply_flag as [Reply Flag],
a.Reply_dt as [Reply Date],
a.Reply_by as [Replied By],
a.Remark,
a.Created_by as [Created By],
a.Creation_dt as [Creation Date],
a.Updated_by as [Updated By],
a.Update_dt as [Update Date]
from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type
and  a.im_dt between @frm_dt and @end_dt
	end				
	else
	  if(isnull(@plant,'')='') and  (@frm_dt='1900-01-01') 
	begin
		select 
a.Item,
a.Plant,
a.Version,
a.Status,
a.Priority,
a.IM_DT as [IM. Date],
a.Vendor_id as [Vendor ID],
c.Vendor_nm as [Vendor Name],
b.issue_Desc as [IM Issue],
a.DN_Number as [DN No.],
a.Order_Number as [PO NO.],
a.Buyer,
a.Report_dt as [Report Date],
a.Rcv_dt as [Rcv Date],
a.Read_flag as [Read Flag],
a.Reply_flag as [Reply Flag],
a.Reply_dt as [Reply Date],
a.Reply_by as [Replied By],
a.Remark,
a.Created_by as [Created By],
a.Creation_dt as [Creation Date],
a.Updated_by as [Updated By],
a.Update_dt as [Update Date]
from im_log_mstr a,im_issue_Type b,po_vendor_mstr c
where a.vendor_id = c.vendor_id and a.issue_type = b.issue_Type

	end
	
end