SELECT     
 dbo.SO_Lines.*,
	dbo.SO_Main.InvoiceID as SaleID
	,dbo.SO_Main.InvoiceNo + case when SO_History.HistoryNo=1 then '' else '-D' + CASE WHEN SO_History.HistoryNo < 10 THEN '0' ELSE '' END + CAST(SO_History.HistoryNo as varchar(2)) end as InvoiceNumber
--dbo.SO_Main.SOReference AS InvoiceNumber
, dbo.SO_Lines.LineNum as LineNumber
, dbo.SO_Lines.PartNo as ItemNumber
, dbo.SO_Lines.Description as ItemName
, dbo.SO_Lines.Description as Description
, (case when dbo.SO_Main.Status=2 then dbo.SO_Lines.QuantityThisDel else QuantityBackOrd end) * (dbo.SO_Lines.ItemPriceUnDiscounted  - (dbo.SO_Lines.ItemPriceUnDiscounted * dbo.SO_Lines.DiscountPercentage/100)) as TaxExclusiveTotal
--, dbo.SO_Lines.CurrentLineTotal - dbo.SO_Lines.TaxToCharge as TaxExclusiveTotal
,  (case when dbo.SO_Main.Status=2 then dbo.SO_Lines.QuantityThisDel else QuantityBackOrd end) * dbo.SO_Lines.ItemPriceIncGST as TaxInclusiveTotal
--, dbo.SO_Lines.CurrentLineTotal as TaxInclusiveTotal
, dbo.SO_Lines.ItemPriceUnDiscounted  - (dbo.SO_Lines.ItemPriceUnDiscounted * dbo.SO_Lines.DiscountPercentage/100)  as TaxExclusiveUnitPrice --
, dbo.SO_Lines.ItemPriceIncGST as TaxInclusiveUnitPrice --  dbo.SO_Lines.ItemPrice
, case when dbo.SO_Main.Status=2 then dbo.SO_Lines.QuantityThisDel else QuantityBackOrd end as Quantity --  dbo.SO_Lines.ItemPrice - dbo.SO_Lines.ItemTaxToCharge
, dbo.SO_Lines.Units as ItemUOM
, '' as FineLine
, isnull(TX_Main.Description,'') as TaxCode
, isnull(TX_Main.TaxRate,0) as TaxPercentageRate
, 0 as DiscountPercent
, dbo.IN_Main.Aux2 AS Barcode

FROM         dbo.SO_Main INNER JOIN
                      dbo.SO_History ON dbo.SO_Main.InvoiceID = dbo.SO_History.InvoiceID INNER JOIN
                      dbo.SO_Lines ON dbo.SO_History.InvoiceHistoryID = dbo.SO_Lines.InvoiceHistoryID LEFT OUTER JOIN
                      dbo.IN_Main ON dbo.SO_Lines.InventoryID = dbo.IN_Main.InventoryID
					  left join TX_Main on TX_Main.TaxID = SO_Lines.TaxID
WHERE    
	CreditNote=0
	AND ((dbo.SO_Main.Status=2 and QuantityThisDel > 0) or (dbo.SO_Main.Status<>2 and QuantityBackOrd <> 0) )
	AND SO_Main.OrderNo = '{OrderNumber}'

          
ORDER BY InvoiceNo, HistoryNo, LineNum

/*
SELECT     

	dbo.SO_Main.InvoiceID as SaleID
	,dbo.SO_Main.InvoiceNo + case when SO_History.HistoryNo=1 then '' else '-D' + CASE WHEN SO_History.HistoryNo < 10 THEN '0' ELSE '' END + CAST(SO_History.HistoryNo as varchar(2)) end as InvoiceNumber
--dbo.SO_Main.SOReference AS InvoiceNumber
, dbo.SO_Lines.LineNum as LineNumber
, dbo.SO_Lines.PartNo as ItemNumber
, dbo.SO_Lines.Description as ItemName
, dbo.SO_Lines.Description as Description
, (case when dbo.SO_Main.Status=2 then dbo.SO_Lines.QuantityThisDel else QuantityBackOrd end) * dbo.SO_Lines.ItemPriceUnDiscounted as TaxExclusiveTotal -- dbo.SO_Lines.CurrentLineTotal - dbo.SO_Lines.TaxToCharge 
,  (case when dbo.SO_Main.Status=2 then dbo.SO_Lines.QuantityThisDel else QuantityBackOrd end) * dbo.SO_Lines.ItemPriceIncGST as TaxInclusiveTotal -- dbo.SO_Lines.CurrentLineTotal 
, dbo.SO_Lines.ItemPriceUnDiscounted as TaxExclusiveUnitPrice --
, dbo.SO_Lines.ItemPriceIncGST as TaxInclusiveUnitPrice --  dbo.SO_Lines.ItemPrice
, case when dbo.SO_Main.Status=2 then dbo.SO_Lines.QuantityThisDel else QuantityBackOrd end as Quantity --  dbo.SO_Lines.ItemPrice - dbo.SO_Lines.ItemTaxToCharge
, dbo.SO_Lines.Units as ItemUOM
, '' as FineLine
, isnull(TX_Main.Description,'') as TaxCode
, isnull(TX_Main.TaxRate,0) as TaxPercentageRate
, dbo.SO_Lines.DiscountPercentage as DiscountPercent
, dbo.IN_Main.Aux2 AS Barcode

FROM         dbo.SO_Main INNER JOIN
                      dbo.SO_History ON dbo.SO_Main.InvoiceID = dbo.SO_History.InvoiceID INNER JOIN
                      dbo.SO_Lines ON dbo.SO_History.InvoiceHistoryID = dbo.SO_Lines.InvoiceHistoryID LEFT OUTER JOIN
                      dbo.IN_Main ON dbo.SO_Lines.InventoryID = dbo.IN_Main.InventoryID
					  left join TX_Main on TX_Main.TaxID = SO_Lines.TaxID
WHERE    
	CreditNote=0
	AND ((dbo.SO_Main.Status=2 and QuantityThisDel > 0) or (dbo.SO_Main.Status<>2 and QuantityBackOrd <> 0) )
	AND SO_Main.OrderNo = '{OrderNumber}'
		--AND dbo.SO_Lines.PartNo<>'\C'
		 --((dbo.SO_Main.Status = 1) OR
              --        (dbo.SO_Main.Status = 2))
          
ORDER BY InvoiceNo, HistoryNo, LineNum */