SQL - Evolution Sales Query - Invoice Lines Based

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

Sales / Purchase query

SELECT

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

 CASE
	WHEN InvNum.DocType IN (0,4,9) THEN IL.[fQtyLastProcessLineTotExcl]
	WHEN InvNum.DocType IN (1) THEN IL.[fQtyLastProcessLineTotExcl] * -1
	ELSE 0 END as LineTotalExcl_Sales,

--PURCHASE:

CASE
 WHEN InvNum.DocType IN (2,5) THEN fQtyProcessed*IL.[fUnitCost]
 WHEN InvNum.DocType IN (3)THEN fQtyProcessed*IL.[fUnitCost] * -1
 ELSE 0 END AS fCOGS_Purchase,
CASE
 WHEN InvNum.DocType IN (2,5) THEN fQtyProcessed
 WHEN InvNum.DocType IN (3) THEN fQtyProcessed * -1
 ELSE 0 END AS fQtyProcessed_Purchase, 
CASE
 WHEN InvNum.DocType IN (2,5) THEN IL.[fQuantity]
 WHEN InvNum.DocType IN (3) THEN IL.[fQuantity] * -1
 ELSE 0 END AS fQuantity_Purchase,

 CASE
	WHEN InvNum.DocType IN (2,5) THEN IL.[fQtyProcessedLineTotExcl]
	WHEN InvNum.DocType IN (3) THEN IL.[fQtyLastProcessLineTotExcl] * -1
	ELSE 0 END as LineTotalExcl_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,


 DocTypeDescription = 
	case DocType when 0 then 'Invoice'
		when 1 then 'Credit Note'
		when 2 then 'Goods Received Voucher'
		when 3 then 'Return To Supplier'
		when 4 then 'Sales Order'
		when 5 then 'Purchase Order'
		when 6 then 'POS Sale Invoice'
		when 7 then 'POS Return'
		else 'Unknown'  end,
  DocStateDescription = 
	case DocState when 1 then 'Unprocessed'
		when 2 then 'Quotation'
		when 3 then 'Partially Processed'
		when 4 then 'Archived'
		when 5 then 'Template'
		when 6 then 'Contract Template'
		when 7 then 'Cancelled'
		when 8 then 'Pending'
		when 9 then 'Merged'
		when 10 then 'Archived Quotation'
		else 'Unknown' end
,GrvFlagDesc =
    case
      when (InvNum.DocFlag=1) then
        ('GRV')
      when (InvNum.DocFlag=2) then
        ('Supplier Invoice')
      else
        ('NA')
    end,


 * 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,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) --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)

Sales Query - Incl "Betalingservaring"

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

Space shortcuts

Page tree

Sales / Purchase query

Sales query - including payment behavior of customers