AKI-REFILL-3

USE SPEX_DB;

IF EXISTS (
SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'spex'
    AND SPECIFIC_NAME = N'SP_AMANAH_RE_FILL_4_DETAIL'
    AND ROUTINE_TYPE = N'PROCEDURE'
)
DROP PROCEDURE spex.SP_AMANAH_RE_FILL_4_DETAIL
GO

CREATE PROCEDURE spex.SP_AMANAH_RE_FILL_4_DETAIL
(
  @PART_NO NVARCHAR(15),
  @ZONE_CD VARCHAR(1),
  @SOURCE VARCHAR(MAX) = 'LIST'
)
AS
BEGIN

    EXEC spex.usp_CutPartNo @PART_NO OUTPUT

    SELECT
        DISTINCT
        R_PART.PART_NO
        , ISNULL(R_PART.SOH, 0) SOH
        , ISNULL(R_PART.SOR, 0) SOR
        , ISNULL(R_PART.SODR, 0) SODR
        , ISNULL(R_PART.SONDR, 0) SONDR
        , M_PART.RACK_ADDRESS_CD AS RACK_ADDRESS
        , ISNULL(M_PART.MAX_STOCK, 0) MAX_STOCK
        , 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
        --, ISNULL(M_PART.[MAX_STOCK], 0) - ISNULL(R_PART.[SOR], 0) as STOCK_REQ
        , 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
            FROM [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] AS R_PART
                INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] AS M_PART
                    ON R_PART.PART_NO = M_PART.PART_NO
                WHERE ISNULL(R_PART.SOR, 0) < ISNULL(M_PART.MAX_STOCK, 0)
                    --AND (ISNULL(R_PART.SONDR, 0) > 0)
                    AND R_PART.PART_NO = @PART_NO
                    --AND LEFT(M_PART.RACK_ADDRESS_CD, 1) = @ZONE_CD


END
GO

EXECUTE spex.SP_AMANAH_RE_FILL_4_DETAIL '12261BZ140', 'M', 'LIST';