SELECT
 
SUM(CASE
 WHEN InvNum.DocType IN (0,4,6,9) THEN fQtyProcessed*IL.[fUnitCost]
 WHEN InvNum.DocType IN (1,7)THEN fQtyProcessed*IL.[fUnitCost] * -1
 ELSE 0 END) AS fCOGS_Sales,
SUM(CASE
 WHEN InvNum.DocType IN (0,4,6,9) THEN fQtyProcessed
 WHEN InvNum.DocType IN (1,7) THEN fQtyProcessed * -1
 ELSE 0 END) AS fQtyProcessed_Sales,
SUM(CASE
 WHEN InvNum.DocType IN (0,4,6,9) THEN IL.[fQuantity]
 WHEN InvNum.DocType IN (1,7) THEN IL.[fQuantity] * -1
 ELSE 0 END) AS fQuantity_Sales,
 
 SUM(CASE
    WHEN InvNum.DocType IN (0,4,6,9) THEN IL.[fQtyLastProcessLineTotExcl]
    WHEN InvNum.DocType IN (1,7) THEN IL.[fQtyLastProcessLineTotExcl] * -1
    ELSE 0 END) as LineTotalExcl_Sales,

 
--PURCHASE:
 
 
 --SalesAcocunt:
 CASE WHEN IL.[iStockCodeID] = 0 THEN DocumentAccs.Master_Sub_Account + ' - ' + DocumentAccs.Description
		ELSE SalesAccs.Master_Sub_Account + ' - ' + SalesAccs.Description END as SalesAccount,
 CASE
 WHEN IL.[iStockCodeID] = 0 THEN DocumentAccs.Master_Sub_Account + ' - ' + DocumentAccs.Description
 WHEN COALESCE(StkItem.Code,'') <> '' THEN StkItem.Code + ' - ' + StkItem.Description_1
 ELSE DocumentAccs.Master_Sub_Account + ' - ' + DocumentAccs.Description  END AS ItemGLLineCode,
 
 GrpTbl.Description as GroupDescription
 ,InvNum.InvNumber
 ,InvNum.InvDate

  from
_btblInvoiceLines IL
LEFT JOIN StkItem ON iStockCodeID = StockLink
LEFT JOIN InvNum ON iInvoiceID = AutoIndex
LEFT JOIN GrpTbl ON ItemGroup = GrpTbl.StGroup
LEFT JOIN Accounts DocumentAccs ON IL.iLedgerAccountID = DocumentAccs.AccountLink
LEFT JOIN Accounts SalesAccs ON GrpTbl.SalesAccLink = SalesAccs.AccountLink
LEFT JOIN Project ON iLineProjectID = ProjectLink
    --SALES ONLY:
LEFT JOIN Client ON InvNum.AccountID = Client.DCLink AND InvNum.DocType IN (0,1,4,6,7,9)
LEFT JOIN Client ClientMaster ON Client.MainAccLink = ClientMaster.DCLink
LEFT JOIN CliClass ON Client.iClassID = idCliClass
LEFT JOIN Currency Currency_Sale ON Client.iCurrencyID = Currency_Sale.CurrencyLink
    --PURCHASE ONLY:
LEFT JOIN Vendor V ON InvNum.AccountID = V.DCLink AND InvNum.DocType IN (2,3,5)
LEFT JOIN VenClass ON V.iClassID = idVenClass
LEFT JOIN _etblGLReportCategory ON SalesAccs.iReportCategoryID = _etblGLReportCategory.idReportCategory
LEFT JOIN Currency Currency_Purchase ON V.iCurrencyID = Currency_Purchase.CurrencyLink
 
WHERE 1=1
    --Purchase only - only SINVs:
    --AND InvNum.DocFlag=2 -- only SINVs:
    --AND InvNum.DocState = 4
    --AND InvNum.DocType in (2,3,5)
    --Sales only:
    AND InvNum.DocState = 4 --Processed only
    AND InvNum.DocType in (0,1,4,6,7) --sales docs only

SELECT
CASE
WHEN (select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 2) = '' Then 0
ELSE COALESCE((select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 2),0) END AS ActualWeight, 
(
SELECT 
Avg(DateDiff(d,dFromRecDate,dToRecDate))
 FROM 
--CLIENT
--OUTER APPLY
dbo._efnAllocsARAP('AR', GETDATE(), 1, 1, CASE 
WHEN Client.MainAccLink > 0 Then [ClientMaster].DCLink
ELSE Client.DCLink END, NULL)
WHERE fAmountForeign <> 0 and DateDiff(d,dFromRecDate,dToRecDate) > 0
GROUP BY
iAccountID
) BetalingsErvaring_AVG,
(
SELECT 
sum(DateDiff(d,dFromRecDate,dToRecDate)*fAmountForeign)/sum(fAmountForeign)
 FROM 
dbo._efnAllocsARAP('AR', GETDATE(), 1, 1, CASE 
WHEN Client.MainAccLink > 0 Then [ClientMaster].DCLink
ELSE Client.DCLink END, NULL)
WHERE fAmountForeign <> 0 and DateDiff(d,dFromRecDate,dToRecDate) > 0
GROUP BY
iAccountID
) BetalingsErvaring_WA,
CASE
WHEN (select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 5) = '' Then 0
ELSE COALESCE(CAST(CAST((select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 5) as float) as DECIMAL(12,2)),0) END AS DefaultKGsPerBox,
CASE
 WHEN InvNum.DocType IN (0,4,9) THEN fQtyProcessed*[_btblInvoiceLines].[fUnitCost]
 WHEN InvNum.DocType IN (1)THEN fQtyProcessed*[_btblInvoiceLines].[fUnitCost] * -1
 ELSE 0 END AS fCOGS,
 CASE 
WHEN COALESCE((SELECT t4.fExclPrice FROM _etblPriceListPrices t4 WHERE t4.iPriceListNameID = @PriceListID@ AND t4.iStockID = _btblInvoiceLines.iStockCodeID AND t4.iWarehouseID = _btblInvoiceLines.iWarehouseID),0) - COALESCE([_btblInvoiceLines].[fUnitPriceExcl],0) > 0 THEN 1 
ELSE 0 END AS isDiscounted,
CASE
 WHEN InvNum.DocType IN (0,4,9) THEN fQtyProcessed* COALESCE([StkItem].[ufIINettoKg],0)
 WHEN InvNum.DocType IN (1) THEN fQtyProcessed* COALESCE([StkItem].[ufIINettoKg],0) * -1
 ELSE 0 END AS NettoKgPerLine,
CASE
 WHEN InvNum.DocType IN (0,4,9) THEN fQtyProcessed
 WHEN InvNum.DocType IN (1) THEN fQtyProcessed * -1
 ELSE 0 END AS QtySalesKGs,
 CASE
 WHEN InvNum.DocType IN (0,4,9) AND LEFT(StkItem.Code,2) = 'KG' THEN fQtyProcessed
 WHEN InvNum.DocType IN (1) AND LEFT(StkItem.Code,2) = 'KG' AND InvNum.AccountID <> 39 THEN fQtyProcessed * -1
 ELSE 0 END AS QtySalesKGsAF,
 CASE
 WHEN InvNum.DocType IN (0,4,9) THEN fQtyProcessed
 WHEN InvNum.DocType IN (1)THEN fQtyProcessed * -1
 ELSE 0 END AS fQtyProcessed, 
CASE
 WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.[fQuantity]
 WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.[fQuantity] * -1
 ELSE 0 END AS fQuantity,
 CASE 
 WHEN [_btblInvoiceLines].[iStockCodeID] = 0 THEN DocumentAccs.Master_Sub_Account + ' - ' + DocumentAccs.Description 
 WHEN COALESCE(StkItem.Code,'') <> '' THEN StkItem.Code + ' - ' + StkItem.Description_1
 ELSE DocumentAccs.Master_Sub_Account + ' - ' + DocumentAccs.Description  END AS ItemGLLineCode,
 CASE 
 WHEN [GrpTbl].[Description] LIKE '%Angola%' THEN 'LOCAL'
 ELSE 'IMPORT' END AS ItemGroupClassification,
 COALESCE((select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 1),'') LineDescriptionTxt,
 (SELECT t4.fExclPrice FROM _etblPriceListPrices t4 WHERE t4.iPriceListNameID = @PriceListID@ AND t4.iStockID = _btblInvoiceLines.iStockCodeID AND t4.iWarehouseID = _btblInvoiceLines.iWarehouseID) ListPrice,
 CASE
WHEN (select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 4) = '' THEN 0
ELSE COALESCE((select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 4),0) END AS NumberOfBoxes,
CASE
WHEN ((SELECT MIN(Per) FROM Period WHERE PerDate >= InvNum.InvDate) % 12) = 0 THEN
 RIGHT('0' + CAST(((SELECT MIN(Per) FROM Period WHERE PerDate >= InvNum.InvDate) % 12)+12 AS VARCHAR(2)),2) + ' - ' + DATENAME(month, InvNum.InvDate) + ' ' + CAST(YEAR(InvNum.InvDate) AS VARCHAR(4))
ELSE
 RIGHT('00' + CAST(((SELECT MIN(Per) FROM Period WHERE PerDate >= InvNum.InvDate) % 12) AS VARCHAR(2)),2) + ' - ' + DATENAME(month, InvNum.InvDate) + ' ' + CAST(YEAR(InvNum.InvDate) AS VARCHAR(4))
END AS PeriodNrName,
CASE
 WHEN InvNum.DocType IN (0,4,9) THEN fQtyProcessed
 WHEN InvNum.DocType IN (1) THEN fQtyProcessed * -1
 ELSE 0 END AS QtySalesKGs,
 CASE
 WHEN InvNum.DocType IN (0,4,9) AND LEFT(StkItem.Code,2) = 'KG' THEN fQtyProcessed
 WHEN InvNum.DocType IN (1) AND LEFT(StkItem.Code,2) = 'KG' AND InvNum.AccountID <> 39 THEN fQtyProcessed * -1
 ELSE 0 END AS QtySalesKGsAF,
 CASE  
WHEN (select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 3) = '' Then 0
ELSE COALESCE(CAST((select strName FROM dbo.fSplit([_btblInvoiceLines].[cDescription],'|') WHERE Code = 3) as DECIMAL(12,2)),0.00)
END AS VolumeWeight, 
 * from
_btblInvoiceLines
LEFT JOIN StkItem ON iStockCodeID = StockLink
LEFT JOIN InvNum ON iInvoiceID = AutoIndex
LEFT JOIN GrpTbl ON ItemGroup = GrpTbl.StGroup
LEFT JOIN Accounts DocumentAccs ON _btblInvoiceLines.iLedgerAccountID = DocumentAccs.AccountLink
LEFT JOIN Accounts SalesAccs ON GrpTbl.SalesAccLink = SalesAccs.AccountLink
LEFT JOIN Project ON iLineProjectID = ProjectLink
LEFT JOIN Client ON InvNum.AccountID = Client.DCLink AND InvNum.DocType IN (0,1,4,9)
LEFT JOIN Client ClientMaster ON Client.MainAccLink = ClientMaster.DCLink
LEFT JOIN CliClass ON Client.iClassID = idCliClass
LEFT JOIN _etblGLReportCategory ON SalesAccs.iReportCategoryID = _etblGLReportCategory.idReportCategory
LEFT JOIN Currency ON Client.iCurrencyID = Currency.CurrencyLink