USE [Test_Acome_28062016]
GO
/****** Object:  StoredProcedure [dbo].[b1s_testautoret]    Script Date: 19-01-2017 11:35:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    ALTER procedure [dbo].[B1S_LineInfo]
	@woid int
    --with encryption
    as
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    BEGIN
		SET NOCOUNT ON;

		WITH batchs AS (

			SELECT a.DocEntry,
			b.VisOrder,
			d.BatchNum,
			c.DocEntry AS igedoc
			FROM OWOR a
			INNER JOIN WOR1 b ON a.DocEntry=b.DocEntry AND b.IssueType<>'B' and  a.DocEntry=@woid
			LEFT OUTER JOIN IGE1 c ON a.DocEntry=c.BaseEntry AND c.BaseLine=b.VisOrder AND c.BaseType =202 and a.DocEntry=@woid
			LEFT OUTER JOIN IBT1 d ON c.objtype = d.basetype AND c.DocEntry = d.baseentry AND c.LineNum = d.BaseLinNum AND c.itemcode = d.ItemCode

		)
		select * into #tempbatchestable from batchs
		--select * from #tempbatchestable
		--drop table #tempbatchestable

		SELECT
			A.DocEntry,
			a.VisOrder,
			a.LineNum,
			A.TotalIssue,
			--B.Total_Return,
			aa.BatchNum,
			--isnull(A.cmpltQty,0) as 'Completed',
			isnull(A.TotalIssue,0)-(isnull(B.Total_Return,0)+((isnull(A.cmpltQty,0)+isnull(A.RjctQty,0))*isnull(A.baseqty,0))) 'AvailQty'
			FROM (
				SELECT a.DocEntry,b.VisOrder,b.LineNum,b.itemCode,a.cmpltQty,a.RjctQty,
				--d.BatchNum,
				b.baseqty,
				SUM(c.Quantity) 'TotalIssue'
				FROM OWOR a INNER JOIN WOR1 b ON a.DocEntry=b.DocEntry
				LEFT OUTER JOIN IGE1 c ON a.DocEntry=c.BaseEntry AND c.BaseLine=b.VisOrder AND c.BaseType =202
				WHERE a.DocEntry=@woid AND b.IssueType<>'B'
				GROUP BY a.DocEntry,b.VisOrder,b.LineNum,b.itemCode,a.cmpltQty,a.RjctQty,b.baseqty--,d.BatchNum,c.DocEntry,b.IssuedQty
			)A
			LEFT OUTER JOIN
			(
				SELECT a.DocEntry,b.VisOrder, SUM(d.Quantity) 'Total_Return'
				FROM OWOR a INNER JOIN WOR1 b ON a.DocEntry=b.DocEntry
				LEFT OUTER JOIN IGN1 d ON  a.DocEntry=d.BaseEntry AND d.BaseLine=b.VisOrder AND d.BaseType =202
				LEFT OUTER JOIN IBT1 c ON d.objtype = c.basetype AND d.DocEntry = c.baseentry AND d.LineNum = c.BaseLinNum AND d.itemcode = c.ItemCode
				WHERE a.DocEntry=@woid AND b.IssueType<>'B'
				GROUP BY a.DocEntry,b.VisOrder
			) B
			ON A.DocEntry=A.DocEntry AND A.VisOrder =B.VisOrder
			JOIN
			(
				SELECT b1.*
				FROM #tempbatchestable b1
				LEFT JOIN #tempbatchestable b2 ON b1.DocEntry = b2.DocEntry AND b1.VisOrder =b2.VisOrder AND b1.igedoc < b2.igedoc
				WHERE b2.igedoc IS NULL
			) aa
			ON A.DocEntry=aa.DocEntry AND A.VisOrder =aa.VisOrder

      END