SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spPAY_ExtCheckMandatoryAttachLeave]
@COSTCENTERID BIGINT=0,
@DOCID BIGINT,
@UserID Int=1,
@LangID Int=1
AS
BEGIN

DECLARE @DOCSEQNO INT,@CNT INT,@I INT,@ErrorMessage NVARCHAR(500),@NoOfDays NVARCHAR(100),@LeaveType INT
DECLARE @TAB TABLE(ID INT IDENTITY(1,1),EMPNODE BIGINT,DOCID BIGINT,DOCSEQNO INT,NoOfDays NVARCHAR(100),LeaveType INT)

INSERT INTO @TAB
SELECT CC.DCCCNID51,I.DOCID,i.DocSeqNo,t.dcAlpha7,CC.dcCCNID52
FROM INV_DOCDETAILS I WITH(NOLOCK)
JOIN COM_DOCCCDATA CC WITH(NOLOCK) ON CC. INVDOCDETAILSID=I.INVDOCDETAILSID
JOIN COM_DOCTEXTDATA T WITH(NOLOCK) ON T.INVDOCDETAILSID=I.INVDOCDETAILSID
WHERE I.DOCID=@DOCID 

SELECT @CNT=COUNT(*) FROM @TAB
SET @I=1

IF(@CNT>0)
BEGIN
	WHILE(@I<=@CNT)
	BEGIN
		SELECT @DOCSEQNO=DOCSEQNO,@NoOfDays=NoOfDays,@LeaveType=LeaveType FROM @TAB	WHERE ID=@I

		IF((@LeaveType=106 AND @NoOfDays>2) OR (@LeaveType=70 AND @NoOfDays>2))
		BEGIN
			IF NOT EXISTS(SELECT * FROM COM_Files WITH(NOLOCK) WHERE FeatureID=@CostCenterID AND FeaturePK=@DOCID AND RowSeqNo=@DOCSEQNO)
			BEGIN
			SELECT @DOCSEQNO,@DOCID
					SET @ErrorMessage='Mandatory Attachment At RowNo :: '+ CONVERT(NVARCHAR,@DOCSEQNO)
					RAISERROR(@ErrorMessage,16,1)
			END
		END

		SET @I=@I+1
	END
END

END

--Go
--spPAY_ExtCheckMandatoryAttachLeave
--40062,
--3838,
--1,1