https://netsuiteprofessionals.com logo
Join Slack
Powered by
<@UAE7BF7TK> which schema? Looks like this would b...
# suitetalkapi
m
@Marc Reicher which schema? Looks like this would be in the 
TRANSACTION_LINKS
table in the original/legacy schema
m
when you say which schema, can you clarify what you mean?
m
There are two data source available for ODBC - NetSuite.com (legacy) and NetSuite2.com (anayltics schema)
m
I was hoping to use the Analytics schema, but I only see transaction links in the legacy schema 😕
m
Might be 
NextTransactionLink
in the Analytics schema
m
can try that out, but not sure that's the one
m
Might be 
PreviousTransactionLink
if you are starting from the payment and trying to get to the invoice
There is definitely a link type there called payment
m
yeah hmm you might be right. I see that there is also 
NextTransactionLineLink
and 
PreviousTransactionLineLink
m
If it helps you can try building a workbook in UI and it should theoretically be the same fields
m
gonna play a bit with this in postman and then try out the UI if need be. Thanks for the help!
j
This is what we just did for our auditors (using the v1 schema) to give them aged debtors for a particular period You can put a HAVING clause on there to get rid of the zero values as it will currently give every transaction, but just zero the amount if it is allocated. It was built mainly through trial and error but it matches to our aged receivables reports Couple of notes: • I've hard keyed account 132 which is our AR control account • I've hard keyed dates in a couple of the joins, so this would need to be updated on the fly - if you're not worried about going back in time then this wouldn't be an issue • when calculating the amount, I'm using MAX(d.amount) so if you need to roll this up you'd need to keep this as a subquery in current form - the links duplicate the original amount. You need to keep d.transaction_line_id in the groupings or else the MAX breaks down on some transactions - I've got it in the [Description] column • A couple of the joins are redundant as they're not used in the query - we were looking at something else to begin with
Copy code
SELECT 
  MAX(d.amount) - SUM(CASE WHEN tl.amount_linked IS NULL OR tlp.ending IS NULL THEN 0.00 ELSE tl.amount_linked END) + SUM(CASE WHEN lt.amount_linked IS NULL OR ltp.ending IS NULL THEN 0.00 ELSE lt.amount_linked END) AS [Amount]
, MAX(d.amount_foreign) - SUM(CASE WHEN tl.amount_linked IS NULL OR tlp.ending IS NULL THEN 0.00 ELSE tl.amount_foreign_linked END) + SUM(CASE WHEN lt.amount_linked IS NULL OR ltp.ending IS NULL THEN 0.00 ELSE lt.amount_foreign_linked END) AS [Amount OC]
, COALESCE(cust.customer_id, 'Unknown') AS [Customer Identifier]
, COALESCE(h.memo, 'Unassigned') + ' ' + TO_CHAR(d.transaction_line_id) AS [Description]
, h.transaction_type AS [Document Type]
, s.subsidiary_ID AS [Entity]
, sc.currency_ID AS [Entity Currency]
, h.trandate AS [Invoice Date]
, COALESCE(h.tranid, h.transaction_number) AS [Invoice ID]
, hc.currency_ID AS [Original Currency]
FROM 
  "Company".Administrator.transaction_lines d
   INNER JOIN 
  "Company".Administrator.transactions h ON d.transaction_id = h.transaction_id
   INNER JOIN 
  "Company".Administrator.accounting_periods p ON h.accounting_period_id = p.accounting_period_id
   INNER JOIN 
  "Company".Administrator.accounts a ON d.account_id = a.account_id
   INNER JOIN
  "Company".Administrator.subsidiaries s ON d.subsidiary_id = s.subsidiary_id
   INNER JOIN
  "Company".Administrator.currencies sc ON s.base_currency_id = sc.currency_id
   INNER JOIN
  "Company".Administrator.currencies hc ON h.currency_id = hc.currency_id
   LEFT JOIN
  "Company".Administrator.transaction_links tl ON d.transaction_id = tl.original_transaction_id AND d.transaction_line_id = tl.original_transaction_line_id AND tl.link_type='Payment'
   LEFT JOIN
  "Company".Administrator.transaction_lines tld ON tl.applied_transaction_id = tld.transaction_id AND tl.applied_transaction_line_id = tld.transaction_line_id AND tld.account_id='132'
   LEFT JOIN
  "Company".Administrator.transactions tlh ON tld.transaction_id = tlh.transaction_id AND tlh.is_non_posting='No' 
   LEFT JOIN
  "Company".Administrator.accounting_periods tlp ON tlh.accounting_period_id = tlp.accounting_period_id AND tlp.ending<='2020-08-31'
   LEFT JOIN
  "Company".Administrator.currencies tlhc ON tlh.currency_id = tlhc.currency_id
   LEFT JOIN
  "Company".Administrator.customers cust ON d.company_id = cust.customer_id
   LEFT JOIN
  "Company".Administrator.transaction_links lt ON d.transaction_id = lt.applied_transaction_id AND d.transaction_line_id = lt.applied_transaction_line_id AND lt.link_type='Payment'
   LEFT JOIN
  "Company".Administrator.transaction_lines ltd ON lt.original_transaction_id = ltd.transaction_id AND lt.original_transaction_line_id = ltd.transaction_line_id AND ltd.account_id='132'
   LEFT JOIN
  "Company".Administrator.transactions lth ON ltd.transaction_id = lth.transaction_id AND lth.is_non_posting='No' 
   LEFT JOIN
  "Company".Administrator.accounting_periods ltp ON lth.accounting_period_id = ltp.accounting_period_id AND ltp.ending<='2020-08-31'
WHERE
  p.ending <= '2020-08-31' 
  AND d.account_id='132'
  AND h.is_non_posting='No' 
  AND s.subsidiary_extID IN ('ABC')
GROUP BY 
, COALESCE(cust.customer_id, 'Unknown') 
, COALESCE(h.memo, 'Unassigned') + ' ' + TO_CHAR(d.transaction_line_id) 
, h.transaction_type 
, s.subsidiary_ID 
, sc.currency_ID 
, h.trandate 
, COALESCE(h.tranid, h.transaction_number) 
, hc.currency_ID
payment transactions follow the lt. series of joins at the end - invoices follow the tl. series of joins
m
ah very cool example. Thanks for posting @Jon Kears. I will admit I am still relatively new to SQL so I am going to have to stare at this one for a bit. Also @michoel you were right about the analytics source. 
previoustransactionlinelink
and 
previoustransactionlink
work.
44 Views
Open in Slack
PreviousNext
Powered by