SHARE
TWEET

AKI-REFILL-3

mhamdani049 Jun 29th, 2020 755 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE SPEX_DB;
  2.  
  3. IF EXISTS (
  4. SELECT *
  5.     FROM INFORMATION_SCHEMA.ROUTINES
  6. WHERE SPECIFIC_SCHEMA = N'spex'
  7.     AND SPECIFIC_NAME = N'SP_AMANAH_RE_FILL_4_DETAIL'
  8.     AND ROUTINE_TYPE = N'PROCEDURE'
  9. )
  10. DROP PROCEDURE spex.SP_AMANAH_RE_FILL_4_DETAIL
  11. GO
  12.  
  13. CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_4_DETAIL
  14. (
  15.   @PART_NO NVARCHAR(15),
  16.   @ZONE_CD VARCHAR(1),
  17.   @SOURCE VARCHAR(MAX) = 'LIST'
  18. )
  19. AS
  20. BEGIN
  21.  
  22.     EXEC spex.usp_CutPartNo @PART_NO OUTPUT
  23.  
  24.     SELECT
  25.         DISTINCT
  26.         R_PART.PART_NO
  27.         , ISNULL(R_PART.SOH, 0) SOH
  28.         , ISNULL(R_PART.SOR, 0) SOR
  29.         , ISNULL(R_PART.SODR, 0) SODR
  30.         , ISNULL(R_PART.SONDR, 0) SONDR
  31.         , M_PART.RACK_ADDRESS_CD AS RACK_ADDRESS
  32.         , ISNULL(M_PART.MAX_STOCK, 0) MAX_STOCK
  33.         , ISNULL((SELECT SUM(QTY) FROM dbo.TB_R_REFILL WHERE PART_NO = @PART_NO AND BT_NO = BT_NO AND STATUS <> 3), 0) AS REFILL_QTY
  34.         --, ISNULL(M_PART.[MAX_STOCK], 0) - ISNULL(R_PART.[SOR], 0) as STOCK_REQ
  35.         , ISNULL(M_PART.[MAX_STOCK], 0) - ISNULL(R_PART.[SOR], 0) - ISNULL((SELECT SUM(QTY) FROM dbo.TB_R_REFILL WHERE PART_NO = @PART_NO AND BT_NO = BT_NO AND STATUS <> 3), 0) AS STOCK_REQ
  36.             FROM [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] AS R_PART
  37.                 INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] AS M_PART
  38.                     ON R_PART.PART_NO = M_PART.PART_NO
  39.                 WHERE ISNULL(R_PART.SOR, 0) < ISNULL(M_PART.MAX_STOCK, 0)
  40.                     --AND (ISNULL(R_PART.SONDR, 0) > 0)
  41.                     AND R_PART.PART_NO = @PART_NO
  42.                     --AND LEFT(M_PART.RACK_ADDRESS_CD, 1) = @ZONE_CD
  43.  
  44.  
  45. END
  46. GO
  47.  
  48. EXECUTE spex.SP_AMANAH_RE_FILL_4_DETAIL '12261BZ140', 'M', 'LIST';
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top