CustApi_and_Plugin_zky/Sql/SP_Cust_PDA_AnalyzeBarCode.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/9/16
-- Description:	脤戙等芛沭鎢陓洘
-- =============================================
IF (EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = 'SP_Cust_PDA_AnalyzeBarCode'))
    DROP PROC SP_Cust_PDA_AnalyzeBarCode
GO

CREATE PROCEDURE SP_Cust_PDA_AnalyzeBarCode
	-- Add the parameters for the stored procedure here
	@BarCode NVARCHAR(500),
	@OrgCode 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 @OrgID AS BIGINT
	DECLARE @DefaultTableName AS NVARCHAR(50)
	DECLARE @Sql AS NVARCHAR(MAX)

	SELECT @OrgID = ID FROM Base_Organization WHERE Code = @OrgCode
	SELECT @DefaultTableName = DefaultTableName 
	FROM UBF_MD_Class 
	WHERE FullName IN (SELECT CreateEntity_EntityType FROM BC_BarCode WHERE Code = @BarCode AND Org = @OrgID AND BarCodeType = 0 AND IsCanceled = 0)

	SET @Sql = 'SELECT BarCode.Code AS BarCode, BarCode.CreateEntity_EntityID AS EntityID, BarCode.CreateEntity_EntityType AS EntityType, DefaultTable.DocNo AS DocNo, DefaultTable.WorkFlowID AS FlowID FROM BC_BarCode BarCode'
	SET @Sql = @Sql + ' LEFT JOIN ' + @DefaultTableName + ' AS DefaultTable ON DefaultTable.ID = BarCode.CreateEntity_EntityID'
	SET @Sql = @Sql + ' WHERE BarCode.Code = ''' + @BarCode + ''' AND BarCode.BarCodeType = 0 AND BarCode.IsCanceled = 0'

	EXEC(@Sql)
END
GO

EXEC SP_Cust_PDA_AnalyzeBarCode @BarCode = '110-RCV1102209160005', @OrgCode = '110'