增加仓位关联表

CREATE TABLE [dbo].[JD_RefshStockPlace](
[FID] [int] NOT NULL,
[FFLEXNUMBER] [nvarchar](128) NULL,
[FENTRYID] [int] NOT NULL
) ON [PRIMARY]

GO

 

create view [dbo].[JD_StockPlace] AS

select t1.FSTOCKID, -- 仓库内码
t2.FNUMBER as FStockNu -- 仓库代码
,t1.FFLEXID --仓位组内码
,t3.FFLEXNUMBER --仓位维度字段
,T4.FID --仓位集合内码
,T4.FENTRYID --仓位行内码
,T5.FNUMBER --仓位编码
,T6.FNAME --仓位名称
from T_BD_STOCKFLEXITEM t1
inner join T_BD_STOCK t2 on t1.FSTOCKID=t2.FSTOCKID
inner join T_BAS_FLEXVALUES t3 on t3.FID=t1.FFLEXID
INNER JOIN JD_RefshStockPlace T4 ON T4.FFLEXNUMBER=T3.FFLEXNUMBER AND ISNULL(T4.FENTRYID,'')<>''
INNER JOIN T_BAS_FLEXVALUESENTRY T5 ON T5.FID=t1.FFLEXID AND T4.FENTRYID=T5.FENTRYID
INNER JOIN T_BAS_FLEXVALUESENTRY_L T6 ON T5.FENTRYID=T6.FENTRYID

GO

 

CREATE TRIGGER [dbo].[JD_StockPlace_Update]
ON [dbo].[T_BAS_FLEXVALUESDETAIL]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
exec JD_RefreshStockPlace
-- Insert statements for trigger here

END

 

CREATE PROCEDURE [dbo].[JD_RefreshStockPlace]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
if object_id('JD_RefshStockPlace') is not null
drop table JD_RefshStockPlace
--根据仓位值集表获取仓位集合表的列名
DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @tableName SYSNAME --行转列表
DECLARE @row2column SYSNAME --行变列的字段
SET @tableName = 'T_BAS_FLEXVALUES'
SET @row2column = 'FFLEXNUMBER'
--从行数据中获取可能存在的列
SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])
FROM ['+@tableName+'] GROUP BY ['+@row2column+']'
--PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
--PRINT @sql_col
--获取列名后对仓位集合表列转行
SET @sql_str = N'
SELECT FID,FFLEXNUMBER,FENTRYID INTO JD_RefshStockPlace FROM T_BAS_FLEXVALUESDETAIL
unpivot (FENTRYID FOR FFLEXNUMBER IN('+@sql_col+')) t'

EXEC sp_executesql @sql_str
PRINT @sql_str
END

 

exec JD_RefreshStockPlace