SuiteQL Sample Library

目录

Summary Rows

transaction-gl-impact

Built-In Functions

Revenue ABC Analysis

---

---

Summary Rows

-- Contributor: Tim Dietrich (timdietrich@me.com)
-- Reference: https://docs.oracle.com/cd/B13789_01/olap.101/b10339/x_reserved006.htm
SELECT
	BUILTIN.DF( Status ) AS StatusName,
	COUNT( * ) AS TransactionCount,
	SUM( ForeignTotal ) AS TotalAmount
FROM 
	Transaction 
WHERE 
	( Type = 'SalesOrd' )
GROUP BY
	ROLLUP( BUILTIN.DF( Status ) )

transaction-gl-impact

-- Contributor: Tim Dietrich (timdietrich@me.com)
-- Additional Info: https://timdietrich.me/blog/netsuite-suiteql-gl-impact/
SELECT
	Transaction.TranID,
	BUILTIN.DF( TransactionAccountingLine.AccountingBook ) AS AccountingBookName,
	BUILTIN.DF( TransactionAccountingLine.Account ) AS Account,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
	TransactionAccountingLine.Posting,
	BUILTIN.DF( Transaction.Entity ) AS EntityName,
	TransactionLine.Memo,
	BUILTIN.DF( TransactionLine.Subsidiary ) AS Subsidiary,
	BUILTIN.DF( TransactionLine.Department ) AS Department
FROM 
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	LEFT OUTER JOIN TransactionLine ON
		( TransactionLine.Transaction = TransactionAccountingLine.Transaction )
		AND ( TransactionLine.LineSequenceNumber = TransactionAccountingLine.TransactionLine )
WHERE 
	( Transaction.ID = 7910 )
	AND ( TransactionAccountingLine.Account IS NOT NULL )
ORDER BY
	TransactionAccountingLine.TransactionLine

Built-In Functions

SELECT
	Transaction.TranID,
	Transaction.trandate,
	Transaction.postingPeriod,
	TransactionAccountingLine.Account,
	BUILTIN.DF( TransactionAccountingLine.Account ) AS func_df,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
  	BUILTIN.CONSOLIDATE(TransactionAccountingLine.Credit, 'INCOME', 'DEFAULT', 'DEFAULT', 3, 263, 'DEFAULT') as func_consolidate,
	BUILTIN.CURRENCY(TransactionAccountingLine.Credit) as func_currency,
	BUILTIN.CURRENCY_CONVERT(TransactionAccountingLine.Credit, 1) as func_currency_convert,
	BUILTIN.DF( TransactionLine.Subsidiary ) AS Subsidiary,
	BUILTIN.HIERARCHY(TransactionLine.Subsidiary, 'DISPLAY_JOINED')	 AS func_hierarchy
FROM 
	Transaction
	INNER JOIN TransactionAccountingLine ON
		( TransactionAccountingLine.Transaction = Transaction.ID )
	LEFT OUTER JOIN TransactionLine ON
		( TransactionLine.Transaction = TransactionAccountingLine.Transaction )
		AND ( TransactionLine.LineSequenceNumber = TransactionAccountingLine.TransactionLine )
WHERE 
	--Transaction.trandate >= BUILTIN.RELATIVE_RANGES('TY', 'START')
	Transaction.postingPeriod  in BUILTIN.PERIOD('LFY', 'START', 'ALL', '>')  
	AND ( TransactionAccountingLine.Account IS NOT NULL )
ORDER BY
	Transaction.trandate

 ​​​​​​​

Revenue ABC Analysis

Marty Zigman on "Learn How To Craft a NetSuite ABC Analysis Report Using Advanced SuiteQL Techniques"https://blog.prolecto.com/2023/12/10/learn-how-to-craft-a-netsuite-abc-analysis-report-using-advanced-suiteql-techniques/

-- first get revenue transactions for date period; 
-- I offer more columns than actually needed as they may be valuable for other segmentation exercises;
-- the WITH syntax allows us to treat the results as a table in subsequent queries
WITH revenue AS (
	SELECT
		transaction.trandate
		,transaction.type
		,transaction.tranid
		,transactionline.accountinglinetype
		,BUILTIN.DF(customer.id) AS customer_text
		,BUILTIN.DF(transactionline.item) AS item_text 
		,NVL(transactionline.creditforeignamount,0) - NVL(transactionline.debitforeignamount,0) AS income
	FROM transaction 
	INNER JOIN transactionline ON (transaction.id = transactionline.transaction AND transactionline.accountinglinetype = 'INCOME')
	INNER JOIN customer ON transaction.entity = customer.id
	WHERE transaction.posting = 'T' AND transaction.trandate => '01-01-2011'  -- format according to account date
	ORDER BY customer.id
),

-- develop income ranking by customer using SuiteQL analytical function;
-- fortunately, DENSE_RANK is supported which will sort and provide us an important index for subsequent queries;
-- change customer_text to another value if you want 
rank AS
(
	SELECT 
		DENSE_RANK() OVER(ORDER BY ROUND(SUM(income),2) DESC) AS ranking
		,customer_text
		,ROUND(SUM(income),0) AS income
	FROM revenue
	GROUP BY customer_text
)
,
-- get the total revenue from the ranking work so we can produce a percentage
-- we need this because NetSuite SQL does not support SUM Analytical functions
total as (
	SELECT ROUND(SUM(income),2) AS income_total
	FROM rank 
)

-- get the data from rank using self-joins and the fact we have an ordered list to cumulate;
-- using a cartesian join, we get all the values from the total table as well;
-- using subselects, we gather data from the other tables
SELECT 
	r1.ranking
	,r1.customer_text
	,TO_CHAR(r1.income, '99,999,999') AS income
	,TO_CHAR(income_total, '99,999,999') as income_total
	,TO_CHAR((SELECT SUM(income) FROM rank r2 WHERE r2.ranking <= r1.ranking), '99,999,999') AS income_cumulative
	,TO_CHAR(ROUND(r1.income/income_total * 100, 2)) || '%' as percentage
	,TO_CHAR(ROUND((SELECT SUM(income) FROM rank r2 WHERE r2.ranking <= r1.ranking) / income_total * 100,2)) || '%' as percentage_cumulative
	,CASE 
		WHEN (SELECT SUM(income) FROM rank r2 WHERE r2.ranking <= r1.ranking) / income_total * 100 < 20 THEN 'A'
		WHEN (SELECT SUM(income) FROM rank r2 WHERE r2.ranking <= r1.ranking) / income_total * 100 < 80 THEN 'B'
		ELSE 'C'
	  END AS 'segment'
FROM rank r1, total
ORDER BY ranking