CustApi_and_Plugin_zky/Sql/SP_Cust_PDA_GetItemMasterBarCodes.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/10/17
-- Description:	查询料品条码信息
-- =============================================
IF (EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'SP_Cust_PDA_GetItemMasterBarCodes'))
    DROP PROC SP_Cust_PDA_GetItemMasterBarCodes
GO

CREATE PROCEDURE SP_Cust_PDA_GetItemMasterBarCodes
	-- Add the parameters for the stored procedure here
	@ItemCode NVARCHAR(255),
	@ProjCode NVARCHAR(255),
	@SeibanNo NVARCHAR(50),
	@PageSize INT,
	@PageIndex INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets FROM
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--IF OBJECT_ID('tempdb..#TempBarCodes') IS NOT NULL
	--BEGIN
	--	DROP TABLE #TempBarCodes
	--END

	SELECT BarCode.Code AS BarCode,
		BarCode.Segment80 AS PartName,
		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
	INTO #TempBarCodes
	FROM BC_BarCode AS BarCode
		INNER JOIN BC_BarCodeRuleLine AS RuleLine ON RuleLine.BarCodeRule = BarCode.BarCodeRule AND RuleLine.SegmentValue='Project.Code' AND RuleLine.SegmentSeq = 5
	WHERE Segment1 = @ItemCode
		AND Segment2 = @SeibanNo
		AND Segment5 = @ProjCode
		AND WhQty > 0

	INSERT INTO #TempBarCodes
	SELECT BarCode.Code AS BarCode,
		BarCode.Segment80 AS PartName,
		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
	FROM BC_BarCode AS BarCode
		INNER JOIN BC_BarCodeRuleLine AS RuleLine ON RuleLine.BarCodeRule = BarCode.BarCodeRule AND RuleLine.SegmentValue='Project.Code' AND RuleLine.SegmentSeq = 6
	WHERE Segment1 = @ItemCode
		AND Segment2 = @SeibanNo
		AND Segment6 = @ProjCode
		AND WhQty > 0
	
	SELECT * FROM #TempBarCodes
END
GO

EXEC SP_Cust_PDA_GetItemMasterBarCodes @ItemCode = N'11019999-00006', @SeibanNo = N'11000000023', @ProjCode = N'RD01', @PageSize = 15, @PageIndex = 1