SP_Cust_PDA_GetItemMasterWhDetail.sql 4.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130
  1. -- ================================================
  2. -- Template generated FROM Template Explorer using:
  3. -- Create Procedure (New Menu).SQL
  4. --
  5. -- Use the Specify Values for Template Parameters
  6. -- command (Ctrl-Shift-M) to fill IN the parameter
  7. -- values below.
  8. --
  9. -- This block of comments will NOT be included IN
  10. -- the definition of the procedure.
  11. -- ================================================
  12. SET ANSI_NULLS ON
  13. GO
  14. SET QUOTED_IDENTIFIER ON
  15. GO
  16. -- =============================================
  17. -- Author: 曹嘉杰
  18. -- Create date: 2022/10/16
  19. -- Description: 查询料品存储详细信息
  20. -- =============================================
  21. IF (EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'SP_Cust_PDA_GetItemMasterWhDetail'))
  22. DROP PROC SP_Cust_PDA_GetItemMasterWhDetail
  23. GO
  24. CREATE PROCEDURE SP_Cust_PDA_GetItemMasterWhDetail
  25. -- Add the parameters for the stored procedure here
  26. @OrgCode NVARCHAR(50),
  27. @WhCode NVARCHAR(50),
  28. @BinCode NVARCHAR(50),
  29. @Filter NVARCHAR(500),
  30. @PageSize INT,
  31. @PageIndex INT
  32. AS
  33. BEGIN
  34. -- SET NOCOUNT ON added to prevent extra result sets FROM
  35. -- interfering with SELECT statements.
  36. SET NOCOUNT ON;
  37. -- Insert statements for procedure here
  38. IF OBJECT_ID('tempdb..#TempTableQoh') IS NOT NULL
  39. BEGIN
  40. DROP TABLE #TempTableQoh
  41. END
  42. select A.[ItemInfo_ItemCode] as [Item_ItemCode],
  43. isnull( case when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,'') as [ItemSeg_Name],
  44. A1.[SPECS] as [ItemID_SPECS],
  45. A2.[Code] as [Pro_Code],
  46. A7.[Name] as [Pro_Name],
  47. A.[SeiBanNo] as [W_SeiBanNo],
  48. A8.[CombineName] as [BinSeg_CombineName],
  49. 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],
  50. A4.[Round_Precision] as [Round1_Precision],
  51. sum((A.[StoreQty] + A.[ToRetStQty])) as [BalQty],
  52. A3.[Segment1] as [BinSeg_Segment1],
  53. sum((A.[StoreMainQty] + A.[ToRetStMainQty])) as [BalQty_Main],
  54. 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],
  55. convert(bigint,0) as [Item_ItemID],
  56. convert(bigint,0) as [W_Uom],
  57. convert(bigint,0) as [MainBaseSU_ID]
  58. into #TempTableQoh
  59. from InvTrans_WhQoh as A
  60. left join [CBO_ItemMaster] as A1 on (A.[ItemInfo_ItemID] = A1.[ID])
  61. left join [CBO_Project] as A2 on (A.[Project] = A2.[ID])
  62. left join [CBO_Bin] as A3 on (A.[BinInfo_Bin] = A3.[ID])
  63. left join [Base_UOM] as A4 on (A.[StoreUOM] = A4.[ID])
  64. left join [Base_Organization] as A5 on (A.[LogisticOrg] = A5.[ID])
  65. left join [CBO_Wh] as A6 on (A.[Wh] = A6.[ID])
  66. left join [CBO_Project_Trl] as A7 on (A7.SysMlFlag = 'zh-CN') and (A2.[ID] = A7.[ID])
  67. left join [CBO_Bin_Trl] as A8 on (A8.SysMlFlag = 'zh-CN') and (A3.[ID] = A8.[ID])
  68. where (((((A1.[Name] is not null and (A1.[Name] != ''))
  69. and (A5.[Code] = @OrgCode))
  70. and (A6.[Code] = @WhCode))
  71. and (A.[ItemInfo_ItemCode] LIKE N'%' + @Filter + '%'))
  72. and (A.[BinInfo_Code] LIKE N'%' + @BinCode + '%'))
  73. group by A.[ItemInfo_ItemCode],
  74. isnull( case when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,''),
  75. A1.[SPECS],
  76. A2.[Code],
  77. A7.[Name],
  78. A.[SeiBanNo],
  79. A8.[CombineName],
  80. A4.[Round_Precision],
  81. A3.[Segment1]
  82. having (sum((A.[StoreQty] + A.[ToRetStQty])) > '0')
  83. ORDER BY A.[ItemInfo_ItemCode] ASC,
  84. isnull( case when A1.[ItemFormAttribute] in (16, 22) then A.[ItemInfo_ItemName] else A1.[Name] end ,'') ASC,
  85. A1.[SPECS] ASC,
  86. A2.[Code] ASC,
  87. A7.[Name] ASC,
  88. A.[SeiBanNo] ASC,
  89. A3.[Segment1] ASC
  90. OFFSET @PageIndex * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
  91. select A.Item_ItemCode,
  92. A.ItemSeg_Name,
  93. A.ItemID_SPECS,
  94. A.Pro_Code,
  95. A.Pro_Name,
  96. A.W_SeiBanNo,
  97. A.BinSeg_CombineName,
  98. sum(A.CanUseQty) as [CanUseQty],
  99. A.Round1_Precision,
  100. sum(A.BalQty) as [BalQty],
  101. A.BinSeg_Segment1,
  102. sum(A.BalQty_Main) as [BalQty_Main],
  103. sum(A.Temp_PAB) as [Temp_PAB]
  104. from #TempTableQoh as A
  105. group by A.Item_ItemCode,
  106. A.ItemSeg_Name,
  107. A.ItemID_SPECS,
  108. A.Pro_Code,
  109. A.Pro_Name,
  110. A.W_SeiBanNo,
  111. A.BinSeg_CombineName,
  112. A.Round1_Precision,
  113. A.BinSeg_Segment1
  114. having (sum(A.BalQty) > '0')
  115. order by A.Item_ItemCode asc,
  116. A.ItemSeg_Name asc,
  117. A.ItemID_SPECS asc,
  118. A.Pro_Code asc,
  119. A.Pro_Name asc,
  120. A.W_SeiBanNo asc,
  121. A.BinSeg_Segment1 asc
  122. END
  123. GO
  124. --EXEC SP_Cust_PDA_GetItemMasterWhDetail @OrgCode = N'110', @WhCode = N'110301', @BinCode = N'', @Filter = N'M2120601-00035', @PageSize = 15, @PageIndex = 0