123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130 |
- -- ================================================
- -- Template generated FROM Template Explorer using:
- -- Create Procedure (New Menu).SQL
- --
- -- Use the Specify Values for Template Parameters
- -- command (Ctrl-Shift-M) to fill IN the parameter
- -- values below.
- --
- -- This block of comments will NOT be included IN
- -- the definition of the procedure.
- -- ================================================
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- -- =============================================
- -- Author: 曹嘉杰
- -- Create date: 2022/10/16
- -- Description: 查询料品存储详细信息
- -- =============================================
- IF (EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'SP_Cust_PDA_GetItemMasterWhDetail'))
- DROP PROC SP_Cust_PDA_GetItemMasterWhDetail
- GO
- CREATE PROCEDURE SP_Cust_PDA_GetItemMasterWhDetail
- -- Add the parameters for the stored procedure here
- @OrgCode NVARCHAR(50),
- @WhCode NVARCHAR(50),
- @BinCode NVARCHAR(50),
- @Filter NVARCHAR(500),
- @PageSize INT,
- @PageIndex INT
- 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('tempdb..#TempTableQoh') IS NOT NULL
- BEGIN
- DROP TABLE #TempTableQoh
- END
- select A.[ItemInfo_ItemCode] as [Item_ItemCode],
- isnull( case when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,'') as [ItemSeg_Name],
- A1.[SPECS] as [ItemID_SPECS],
- A2.[Code] as [Pro_Code],
- A7.[Name] as [Pro_Name],
- A.[SeiBanNo] as [W_SeiBanNo],
- A8.[CombineName] as [BinSeg_CombineName],
- sum((((A.[StoreQty] - A.[ResvStQty]) - A.[ResvOccupyStQty]) - case when ((((A.[IsProdCancel] = 1) or (A.[MO_EntityID] != 0)) or A.[ProductDate] is not null) or (A.[WP_EntityID] != 0)) then A.[StoreQty] else convert(decimal(24,9),0) end )) as [CanUseQty],
- A4.[Round_Precision] as [Round1_Precision],
- sum((A.[StoreQty] + A.[ToRetStQty])) as [BalQty],
- A3.[Segment1] as [BinSeg_Segment1],
- sum((A.[StoreMainQty] + A.[ToRetStMainQty])) as [BalQty_Main],
- sum((((((A.[StoreQty] - A.[ResvStQty]) - A.[ResvOccupyStQty]) - case when ((((A.[IsProdCancel] = 1) or (A.[MO_EntityID] != 0)) or A.[ProductDate] is not null) or (A.[WP_EntityID] != 0)) then A.[StoreQty] else convert(decimal(24,9),0) end ) + A.[SupplyQtySU]) - A.[DemandQtySU])) as [Temp_PAB],
- convert(bigint,0) as [Item_ItemID],
- convert(bigint,0) as [W_Uom],
- convert(bigint,0) as [MainBaseSU_ID]
- into #TempTableQoh
- from InvTrans_WhQoh as A
- left join [CBO_ItemMaster] as A1 on (A.[ItemInfo_ItemID] = A1.[ID])
- left join [CBO_Project] as A2 on (A.[Project] = A2.[ID])
- left join [CBO_Bin] as A3 on (A.[BinInfo_Bin] = A3.[ID])
- left join [Base_UOM] as A4 on (A.[StoreUOM] = A4.[ID])
- left join [Base_Organization] as A5 on (A.[LogisticOrg] = A5.[ID])
- left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])
- left join [CBO_Project_Trl] as A7 on (A7.SysMlFlag = 'zh-CN') and (A2.[ID] = A7.[ID])
- left join [CBO_Bin_Trl] as A8 on (A8.SysMlFlag = 'zh-CN') and (A3.[ID] = A8.[ID])
- where (((((A1.[Name] is not null and (A1.[Name] != ''))
- and (A5.[Code] = @OrgCode))
- and (A6.[Code] = @WhCode))
- and (A.[ItemInfo_ItemCode] LIKE N'%' + @Filter + '%'))
- and (A.[BinInfo_Code] LIKE N'%' + @BinCode + '%'))
- group by A.[ItemInfo_ItemCode],
- isnull( case when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,''),
- A1.[SPECS],
- A2.[Code],
- A7.[Name],
- A.[SeiBanNo],
- A8.[CombineName],
- A4.[Round_Precision],
- A3.[Segment1]
- having (sum((A.[StoreQty] + A.[ToRetStQty])) > '0')
- ORDER BY A.[ItemInfo_ItemCode] ASC,
- isnull( case when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,'') ASC,
- A1.[SPECS] ASC,
- A2.[Code] ASC,
- A7.[Name] ASC,
- A.[SeiBanNo] ASC,
- A3.[Segment1] ASC
- OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
- select A.Item_ItemCode,
- A.ItemSeg_Name,
- A.ItemID_SPECS,
- A.Pro_Code,
- A.Pro_Name,
- A.W_SeiBanNo,
- A.BinSeg_CombineName,
- sum(A.CanUseQty) as [CanUseQty],
- A.Round1_Precision,
- sum(A.BalQty) as [BalQty],
- A.BinSeg_Segment1,
- sum(A.BalQty_Main) as [BalQty_Main],
- sum(A.Temp_PAB) as [Temp_PAB]
- from #TempTableQoh as A
- group by A.Item_ItemCode,
- A.ItemSeg_Name,
- A.ItemID_SPECS,
- A.Pro_Code,
- A.Pro_Name,
- A.W_SeiBanNo,
- A.BinSeg_CombineName,
- A.Round1_Precision,
- A.BinSeg_Segment1
- having (sum(A.BalQty) > '0')
- order by A.Item_ItemCode asc,
- A.ItemSeg_Name asc,
- A.ItemID_SPECS asc,
- A.Pro_Code asc,
- A.Pro_Name asc,
- A.W_SeiBanNo asc,
- A.BinSeg_Segment1 asc
- END
- GO
- --EXEC SP_Cust_PDA_GetItemMasterWhDetail @OrgCode = N'110', @WhCode = N'110301', @BinCode = N'', @Filter = N'M2120601-00035', @PageSize = 15, @PageIndex = 0
|