SHARE
TWEET

AKI-REFILL-4

mhamdani049 Jun 29th, 2020 (edited) 783 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_RESERVE_LIST'
  8.     AND ROUTINE_TYPE = N'PROCEDURE'
  9. )
  10. DROP PROCEDURE spex.SP_AMANAH_RE_FILL_4_RESERVE_LIST
  11. GO
  12.  
  13. CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_4_RESERVE_LIST
  14. (
  15.     @PART_NO NVARCHAR(15),
  16.     @ZONE_CD VARCHAR(1)
  17. )
  18. AS
  19. BEGIN
  20.     SELECT * FROM (
  21.         SELECT
  22.         a.[PART_NO],
  23.         a.[TRANSFER_NO],
  24.         a.[RACK_ADDRESS],
  25.         a.[BT_NO],
  26.         a.[SOURCE],
  27.         ISNULL(a.[QTY], 0) AS QTY,
  28.         a.START_DATE
  29.             FROM
  30.                 [spex].[TB_R_BINNING] a
  31.                 INNER JOIN  [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] b ON a.PART_NO = b.PART_NO
  32.                 LEFT JOIN [spex].[TB_R_BUCKET_TRANSFER_H] c ON a.TRANSFER_NO = c.TRANSFER_NO AND a.BT_NO = c.BT_NO
  33.                     WHERE
  34.                         a.[PART_NO] = @PART_NO
  35.                         AND (a.[SOURCE] = 'NON-DIRECT')
  36.                         AND ISNULL(b.SONDR, 0) > 0
  37.                         AND (c.[CLOSE_BY] IS NOT NULL OR c.[CLOSE_DT] IS NOT NULL)
  38.                         AND LEFT(a.[RACK_ADDRESS], 1) = @ZONE_CD
  39.     ) x WHERE x.QTY > 0 ORDER BY x.SOURCE ASC, x.START_DATE ASC
  40. END
  41. GO
  42.  
  43. SELECT 'EXECUTE SP_AMANAH_RE_FILL_4_RESERVE_LIST'
  44. EXECUTE spex.SP_AMANAH_RE_FILL_4_RESERVE_LIST '111150Y030', 'M';
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