CustApi_and_Plugin_zky/Sql/SP_Cust_PDA_GetPackageCompleteQty.sql

-- ================================================
-- 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/8/24
-- Description:	»ñÈ¡ÆëÌ×ÊýÁ¿
-- =============================================
IF (EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'SP_Cust_PDA_GetPackageCompleteQty'))
    DROP PROC SP_Cust_PDA_GetPackageCompleteQty
GO

CREATE PROCEDURE SP_Cust_PDA_GetPackageCompleteQty
	-- Add the parameters for the stored procedure here
	@BarCodes AS NVARCHAR(MAX),
	@OrgCode AS NVARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @OrgID BIGINT
	DECLARE @PackageQty INT
	DECLARE @LackQty INT
	DECLARE @UnPackageQty INT

	SET @PackageQty = 0
	SET @LackQty = 0
	SET @UnPackageQty = 0

	SELECT @OrgID = ID FROM Base_Organization WHERE Code = @OrgCode

	IF OBJECT_ID('tempdb..#TempBarCodeTable') IS NOT NULL
	BEGIN
		DROP TABLE #TempBarCodeTable
	END

	CREATE TABLE #TempBarCodeTable (
		BarCode NVARCHAR(500)
	)

	SET @SQL = 'INSERT INTO #TempBarCodeTable SELECT ''' + REPLACE (@BarCodes,',',''' AS BarCode UNION SELECT ''') + ''''
	EXEC(@SQL)

	IF OBJECT_ID('tempdb..#TempPackageCodeTable') IS NOT NULL
	BEGIN
		DROP TABLE #TempPackageCodeTable
	END

	CREATE TABLE #TempPackageCodeTable (
		PackageCode NVARCHAR(500),
		Segment77 NVARCHAR(50)
	)

	INSERT INTO #TempPackageCodeTable
	SELECT DISTINCT 
		-- SUBSTRING(Code, 1, LEN(Code) - 5) AS PackageCode,
		CASE WHEN (SUBSTRING(Code, 1, 1) = 'C' AND LEN(Code) = 16) THEN SUBSTRING(Code, 1, 9) + '_' + SUBSTRING(Code, 12, 5) ELSE SUBSTRING(Code, 1, LEN(Code) - 5) END AS PackageCode,
		Segment77 AS Segment77
	FROM BC_BarCode 
	WHERE Code IN (SELECT BarCode FROM #TempBarCodeTable)
		-- AND Org = @OrgID
		AND ISNULL(Segment78, 1) <> 1

	--SELECT * FROM #TempPackageCodeTable

	SELECT  
		-- @UnPackageQty = COUNT(ID)
		@UnPackageQty = SUM(Qty)
	FROM BC_BarCode 
	WHERE Code IN (SELECT BarCode FROM #TempBarCodeTable)
		-- AND Org = @OrgID
		AND ISNULL(Segment78, 1) = 1

	SELECT @LackQty = Count(DISTINCT CASE WHEN (SUBSTRING(Code, 1, 1) = 'C' AND LEN(Code) = 16) THEN SUBSTRING(Code, 1, 9) + '_' + SUBSTRING(Code, 12, 5) ELSE CASE WHEN LEN(Code) > 5 THEN SUBSTRING(Code, 1, LEN(Code) - 5) ELSE '' END END)
	FROM BC_BarCode T_BarCode
		-- INNER JOIN #TempPackageCodeTable T_PackageCode ON SUBSTRING(T_BarCode.Code, 1, LEN(T_BarCode.Code) - 5) = T_PackageCode.PackageCode AND T_BarCode.Segment77 = T_PackageCode.Segment77
		INNER JOIN #TempPackageCodeTable T_PackageCode ON CASE WHEN (SUBSTRING(Code, 1, 1) = 'C' AND LEN(Code) = 16) THEN SUBSTRING(Code, 1, 9) + '_' + SUBSTRING(Code, 12, 5) ELSE CASE WHEN LEN(Code) > 5 THEN SUBSTRING(Code, 1, LEN(Code) - 5) ELSE '' END END = T_PackageCode.PackageCode 
			AND T_BarCode.Segment77 = T_PackageCode.Segment77
	WHERE T_BarCode.Code NOT IN (SELECT BarCode FROM #TempBarCodeTable)
		-- AND T_BarCode.Org = @OrgID
		AND ISNULL(T_BarCode.Segment78, 1) <> 1

	SELECT @PackageQty = COUNT(PackageCode) FROM #TempPackageCodeTable
	SELECT (ISNULL(@UnPackageQty, 0) + @PackageQty - @LackQty) AS PickQty
END
GO

-- EXEC SP_Cust_PDA_GetPackageCompleteQty @BarCodes = '11019999-00012-11000000051110-MO-110-2209140008-00001,11019999-00012-11000000051110-MO-110-2209140008-00002,11019999-00012-11000000051110-MO-110-2209140008-00003,11019999-00012-11000000051110-MO-110-2209140008-00004',  @OrgCode = '110'