SQL - Evolution Sales Query - Invoice Lines Based

Note: includes also fields indicating payment behavior of customers! (remove this if performance is an issue).

Sales query, including JobNum & InvNum

Sales query incl JobNum & InvNum  Expand source

CREATE OR ALTER VIEW _as_Sales_InvNumJobNum
AS
--WITH ENCRYPTION
 
SELECT
        --CLIENT:
        [Client].[Account] + ' - ' + [Client].[Name] as Customer,
             [CliClass].[IdCliClass],
        [CliClass].[Code] + ' - ' + [CliClass].[Description] as CustomerGroupCodeDesc,
        COALESCE([Currency].[CurrencyCode],'Home') as CurrencyCode,
        MasterCustomerID = COALESCE(ClientMaster.DCLink,Client.DCLink),
        ClientMasterDCLink = ClientMaster.DCLink,
        ClientDCLink = InvNum.AccountID, -- Client.DCLink,
 
        --Doc Info:
        [InvNum].[InvDate] as InvDate,
        [InvNum].[InvNumber] as InvNumber,
        [InvNum].[GrvNumber] as GrvNumber,
        [InvNum].ExtOrderNum as ExtOrderNum,
        '' as JobDescription,
        '' as JobCode,
        SerialNumbers = (select STRING_AGG(cSerialNumber,', ') FROM _btblInvoiceLineSN SN WHERE SN.iSerialInvoiceID = [_btblInvoiceLines].iInvoiceID AND SN.iSerialInvoiceLineID = [_btblInvoiceLines].idInvoiceLines),
        0 as parOutstanding,
        YEAR(InvNum.InvDate) as Year,
 
        --LINE ITEM:
        iLineID as LineNumber,
        [StkItem].[cSimpleCode] as cSimpleCode,
        [_btblInvoiceLines].[cDescription] as LineDescription,
        CASE
            WHEN [_btblInvoiceLines].[iStockCodeID] = 0 THEN DocumentAccs.Master_Sub_Account
            WHEN COALESCE(StkItem.Code,'') <> '' THEN StkItem.Code
        ELSE DocumentAccs.Master_Sub_Account END as LineItemCode,
        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 LineItemCodeDesc,
        [StkItem].[ItemGroup] + ' - ' + [GrpTbl].[Description]  as ItemGroup,
            [GrpTbl].[idGRpTbl] as idGRpTbl,
        COALESCE([Project].[ProjectCode] + ' - ' + [Project].[ProjectName],'None') as ProjectCodeDesc,
        COALESCE([SalesRep].[Code] + ' - ' + [SalesRep].[Name],'None') as SalesRep,
        COALESCE([SalesRep].idSalesRep,0) as idSalesRep,
        COALESCE([SalesAccs].[Master_Sub_Account],DocumentAccs.Master_Sub_Account) as SalesMSA,
        [WhseMst].[Code] as WHCode,
        [WhseMst].[Code] + ' - ' + [WhseMst].[Name] as WHCodeName,
            WhseMst.whseLink,
        --QTY:
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN fQtyLastProcess
            WHEN InvNum.DocType IN (1)THEN fQtyLastProcess * -1
        ELSE 0 END as fQtyLastProcess,
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.[fQuantity]
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.[fQuantity] * -1
        ELSE 0 END as Qty_Ordered,
        --COSTING:
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN fQtyLastProcess*[_btblInvoiceLines].[fUnitCost]
            WHEN InvNum.DocType IN (1)THEN fQtyLastProcess*[_btblInvoiceLines].[fUnitCost] * -1
        ELSE 0 END as fCOGS,
 
        --SALE AMOUNTS (EXCL)
            --Sales--Discounted
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN fQtyLastProcess
            WHEN InvNum.DocType IN (1) THEN fQtyLastProcess * -1
        ELSE 0 END * fUnitPriceExcl * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as LineTotalExcl_Discounted,
            --Sales - undiscounted
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.[fQtyLastProcessLineTotExcl] /*select top 100 * from _btblInvoiceLines IL left join invnum i on il.iinvoiceid = i.autoindex where i.invnumber = 'INV1454052'*/
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.[fQtyLastProcessLineTotExcl] * -1
        ELSE 0 END as LineTotalExcl,
            --Sales - foreign - undiscounted
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.[fQtyLastProcessLineTotExclNoDiscForeign]
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.[fQtyLastProcessLineTotExclNoDiscForeign] * -1
        ELSE 0 END as LineTotalExcl_Foreign,
            --Sales - foreign - discounted
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN fQtyLastProcess
            WHEN InvNum.DocType IN (1) THEN fQtyLastProcess * -1
        ELSE 0 END * fUnitPriceExclForeign * (1-COALESCE(fLineDiscount ,0)/100)* (1-COALESCE(InvDisc,0)/100) as LineTotalExcl_DiscountedForeign,
            --Sales - undiscounted
        --Tax
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.fQtyLastProcessLineTaxAmount
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.fQtyLastProcessLineTaxAmount * -1
        ELSE 0 END as LineTaxAmount,
 
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.fQtyLastProcessLineTaxAmount
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.fQtyLastProcessLineTaxAmount * -1
        ELSE 0 END as LineTaxAmountWithDisc,
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.fQtyLastProcessLineTaxAmountNoDiscForeign
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.fQtyLastProcessLineTaxAmountNoDiscForeign * -1
        ELSE 0 END as LineTaxAmountForeign,
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.fQtyLastProcessLineTaxAmountForeign
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.fQtyLastProcessLineTaxAmountForeign * -1
        ELSE 0 END as LineTaxAmountForeignWithDisc,
        --Sales included Tax
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.[fQtyLastProcessLineTotIncl]
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.[fQtyLastProcessLineTotIncl] * -1
        ELSE 0 END as LineTotalIncl,
            --Sales - foreign - undiscounted
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN _btblInvoiceLines.[fQtyLastProcessLineTotInclForeign]
            WHEN InvNum.DocType IN (1) THEN _btblInvoiceLines.[fQtyLastProcessLineTotInclForeign] * -1
        ELSE 0 END as LineTotalIncl_Foreign,
 
        --Sales included Tax discounted
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN fQtyLastProcess
            WHEN InvNum.DocType IN (1) THEN fQtyLastProcess * -1
        ELSE 0 END * fUnitPriceIncl * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as LineTotalIncl_Discounted,
 
        --Sales included Tax discounted
        CASE
            WHEN InvNum.DocType IN (0,4,9) THEN fQtyLastProcess
            WHEN InvNum.DocType IN (1) THEN fQtyLastProcess * -1
        ELSE 0 END * fUnitPriceInclForeign * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as LineTotalInclForeign_Discounted,
 
        --UNIT Pricing:
        [_btblInvoiceLines].[fTaxRate] as fTaxRate,
        [_btblInvoiceLines].[fUnitCost] as fUnitCost,
        [_btblInvoiceLines].[fUnitPriceExcl] as fUnitPriceExcl,
        fUnitPriceExcl * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as fUnitPrice_FullyDiscounted,
        [_btblInvoiceLines].[fUnitPriceExclForeign] as fUnitPriceExclForeign,
        [_btblInvoiceLines].[fUnitPriceExclForeign] * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as fUnitPrice_FullyDiscountedForeign,
        Per.idPeriod,
        Per.dPeriodDate,
        Coalesce(A.description, 'No area') as area,
        InvNum.InvNum_iCreatedAgentID as AgentID
         
    FROM _btblInvoiceLines
    LEFT JOIN StkItem ON iStockCodeID = StockLink
    LEFT JOIN InvNum ON iInvoiceID = AutoIndex
    LEFT JOIN Client ON InvNum.AccountID = Client.DCLink --AND InvNum.DocType IN (0,1,4,9)
    LEFT JOIN CliClass ON Client.iClassID = idCliClass
    LEFT JOIN Areas A on A.idAreas = Client.iAreasID
    LEFT JOIN SalesRep ON SalesRep.idSalesRep = InvNum.DocRepID
    LEFT JOIN WhseMst ON WhseMst.WhseLink = _btblInvoiceLines.iWarehouseID
    --LEFT JOIN PostAR ON InvNum.AutoIndex = PostAR.InvNumKey
    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 ClientMaster ON Client.MainAccLink = ClientMaster.DCLink
    LEFT JOIN _etblGLReportCategory ON SalesAccs.iReportCategoryID = _etblGLReportCategory.idReportCategory
    LEFT JOIN Currency ON Client.iCurrencyID = Currency.CurrencyLink
    LEFT JOIN _etblPeriod Per on  InvDate > = DATEADD(month, DATEDIFF(month, 0, Per.dPeriodDate), 0) AND  InvDate <=dPeriodDate
    ---FILTERS:
    WHERE --InvNum.InvDate >= @Fromdate AND InvNum.InvDate <= @Todate
    InvNum.DocState = 4
    AND DocType in (1,4) --CRN and SO
 
    UNION ALL
     
    SELECT
    --CLIENT:
    [Client].[Account] + ' - ' + [Client].[Name] as Customer,
         [CliClass].[IdCliClass],
    [CliClass].[Code] + ' - ' + [CliClass].[Description] as CustomerGroupCodeDesc,
    COALESCE([Currency].[CurrencyCode],'Home') as CurrencyCode,
    MasterCustomerID = COALESCE(ClientMaster.DCLink,Client.DCLink),
    ClientMasterDCLink = ClientMaster.DCLink,
    ClientDCLink = JobNum.AccountID, --Client.DCLink,
 
    --Doc Info:
    [JobNum].[InvDate] as InvDate,
    [JobNum].Invnumber as JobNumber,
    [JobNum].Invnumber as GrvNumber,
    [JobNum].cExtOrderNumber as ExtOrderNum,
    [JobNum].Description as JobDescription,
    JM.cJobCode as JobCode,
    SerialNumbers = '' ,
    0 as parOutstanding,
    YEAR(JobNum.InvDate) as Year,
 
    --LINE ITEM:
    0 as LineNumber,
    [StkItem].[cSimpleCode] as cSimpleCode,
    [_btblJCInvoiceLines].[cDescription] as LineDescription,
    CASE
        WHEN [_btblJCInvoiceLines].iStockID = 0 THEN ''
        WHEN COALESCE(StkItem.Code,'') <> '' THEN StkItem.Code
    ELSE '' END as LineItemCode,
    CASE
        WHEN [_btblJCInvoiceLines].iStockID = 0 THEN '' + ' - ' + ''
        WHEN COALESCE(StkItem.Code,'') <> '' THEN StkItem.Code + ' - ' + [StkItem].Description_1
    ELSE '' + ' - ' + '' END as LineItemCodeDesc,
    [StkItem].[ItemGroup] + ' - ' + [GrpTbl].[Description]  as ItemGroup,
        [GrpTbl].[idGRpTbl] as idGRpTbl,
    COALESCE([Project].[ProjectCode] + ' - ' + [Project].[ProjectName],'None') as ProjectCodeDesc,
    COALESCE([SalesRep].[Code] + ' - ' + [SalesRep].[Name],'None') as SalesRep,
    COALESCE([SalesRep].idSalesRep,0) as idSalesRep,
COALESCE([SalesAccs].[Master_Sub_Account],'') as SalesMSA,
    [WhseMst].[Code] as WHCode,
    [WhseMst].[Code] + ' - ' + [WhseMst].[Name] as WHCodeName,
        WhseMst.whseLink,
    --QTY:
     
fQuantity as fQtyLastProcess,
     _btblJCInvoiceLines.[fQuantity] as Qty_Ordered,
    --COSTING:
fQuantity * [fUnitCost]   as fCOGS,
 
    --SALE AMOUNTS (EXCL)
        --Sales--Discounted
    fQuantity * fUnitPriceExcl * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as LineTotalExcl_Discounted,
        --Sales - undiscounted
    [fLineTotExcl]as LineTotalExcl,
        --Sales - foreign - undiscounted
    [fLineTotExclNoDiscForeign] as LineTotalExcl_Foreign,
        --Sales - foreign - discounted
    fQuantity * fUnitPriceExclForeign * (1-COALESCE(fLineDiscount ,0)/100)* (1-COALESCE(InvDisc,0)/100) as LineTotalExcl_DiscountedForeign,
        --Sales - undiscounted
    --Tax
    fLineTotTaxAmount as LineTaxAmount,
 
    fLineTotTaxAmount as LineTaxAmountWithDisc,
    fLineTotTaxAmountNoDiscForeign as LineTaxAmountForeign,
    fLineTotTaxAmountForeign as LineTaxAmountForeignWithDisc,
    --Sales included Tax
    [fLineTotIncl]  as LineTotalIncl,
        --Sales - foreign - undiscounted
    [fLineTotInclForeign] as LineTotalIncl_Foreign,
 
    --Sales included Tax discounted
    fQuantity * fUnitPriceIncl * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as LineTotalIncl_Discounted,
 
    --Sales included Tax discounted
    fQuantity * fUnitPriceInclForeign * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as LineTotalInclForeign_Discounted,
 
    --UNIT Pricing:
    [_btblJCInvoiceLines].[fTaxRate] as fTaxRate,
    [_btblJCInvoiceLines].[fUnitCost] as fUnitCost,
    [_btblJCInvoiceLines].[fUnitPriceExcl] as fUnitPriceExcl,
    fUnitPriceExcl * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as fUnitPrice_FullyDiscounted,
    [_btblJCInvoiceLines].[fUnitPriceExclForeign] as fUnitPriceExclForeign,
    [_btblJCInvoiceLines].[fUnitPriceExclForeign] * (1-COALESCE(fLineDiscount,0)/100)* (1-COALESCE(InvDisc,0)/100) as fUnitPrice_FullyDiscountedForeign,
    Per.idPeriod,
    Per.dPeriodDate,
    Coalesce(A.description, 'No area') as area,
    JobNum.JobNum_iCreatedAgentID as AgentID
 from
_btblJCInvoiceLines
    LEFT JOIN StkItem ON iStockID = StockLink
    LEFT JOIN JobNum ON iJobNumID = AutoIndex
    LEFT JOIN _btblJCMaster JM ON JobNum.iJCMasterID = JM.IdJCMaster
    LEFT JOIN Client ON JobNum.AccountID = Client.DCLink --AND JobNum.DocType IN (0,1,4,9)
    LEFT JOIN CliClass ON Client.iClassID = idCliClass
    LEFT JOIN Areas A on A.idAreas = Client.iAreasID
    LEFT JOIN SalesRep ON SalesRep.idSalesRep = JobNum.iRepID
 
    LEFT JOIN WhseMst ON WhseMst.WhseLink = _btblJCInvoiceLines.iWarehouseID
    LEFT JOIN GrpTbl ON ItemGroup = GrpTbl.StGroup
    LEFT JOIN Accounts SalesAccs ON GrpTbl.SalesAccLink = SalesAccs.AccountLink
    LEFT JOIN Project ON iLineProjectID = ProjectLink
    LEFT JOIN Client ClientMaster ON Client.MainAccLink = ClientMaster.DCLink
    LEFT JOIN _etblGLReportCategory ON SalesAccs.iReportCategoryID = _etblGLReportCategory.idReportCategory
    LEFT JOIN Currency ON Client.iCurrencyID = Currency.CurrencyLink
    LEFT JOIN _etblPeriod Per on  InvDate > = DATEADD(month, DATEDIFF(month, 0, Per.dPeriodDate), 0) AND  InvDate <=dPeriodDate
        WHERE --JobNum.InvDate >= @Fromdate AND JobNum.InvDate <= @Todate
    JobNum.DocType = 1 --processed
GO

Sales query incl JobNum & InvNum (index)  Expand source

CREATE NONCLUSTERED INDEX [_ix_as_JobNum_View]
ON [dbo].[JobNum] ([InvDate],[AccountID],[DocType])

Sales / Purchase query

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

Sales query - including payment behavior of customers

Includes the following:

• Payment behavior (nr of days)
• Weighted avg on payment behavior
• Stock Items & GL lines
• COGS on line (calculated)

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

Ambro - sales query. Includes BETTER lookup to sales account: