CREATE PROCEDURE [dbo].[ExclusiveInsuranceCheckTtac] @PrescriptionId bigint 
AS 
DECLARE @CacheData AS TABLE
(
  Row INT, 
  InsuranceCode VARCHAR(3),
  GoodsCode VARCHAR(20), 
  GoodsName NVARCHAR(100),
  Qty MONEY,
  Status TINYINT
)

INSERT INTO @CacheData(Row, InsuranceCode , GoodsCode,GoodsName, Qty, Status)
SELECT Radif Row, Sazman_Code, KalaId.K_code GoodsCode, Name1 GoodsName, K_Qty1 Qty, Status FROM dbo.TmpDrugHavaleh WITH (NOLOCK)
INNER JOIN dbo.KalaId WITH (NOLOCK) ON KalaId.K_Code = TmpDrugHavaleh.K_Code
INNER JOIN dbo.TmpDrugH WITH (NOLOCK) ON TmpDrugH.Id_Havaleh = TmpDrugHavaleh.Id_Havaleh
WHERE TmpDrugH.Id_Havaleh = @PrescriptionId


SELECT Row, [@CacheData].InsuranceCode, [@CacheData].GoodsCode, [@CacheData].GoodsName, [@CacheData].Qty FROM @CacheData
INNER JOIN dbo.ExclusiveInsuranceCode WITH (NOLOCK) ON ExclusiveInsuranceCode.GoodsCode = [@CacheData].GoodsCode
AND [@CacheData].InsuranceCode = ExclusiveInsuranceCode.InsuranceCode AND ISNULL(HasTTAC,0) = 1

UNION

SELECT Row, [@CacheData].InsuranceCode, [@CacheData].GoodsCode, [@CacheData].GoodsName, [@CacheData].Qty FROM @CacheData
INNER JOIN dbo.ExclusiveInsuranceCode WITH (NOLOCK) ON ExclusiveInsuranceCode.GoodsCode = [@CacheData].GoodsCode
WHERE ISNULL(HasTTAC,0) = 1 AND Status IN(0,1,3) AND ExclusiveInsuranceCode.InsuranceCode IN('01','02')
GROUP BY Row ,[@CacheData].InsuranceCode, [@CacheData].GoodsCode, [@CacheData].GoodsName , [@CacheData].Qty

ORDER BY Row

