Recuperar a data de faturamento a partir de uma tabela de faturamento

Aplica-se a

Produto:  NetSuite 2020.2

Cenário

Um usuário está tentando extrair a tabela Billing_schedule no ODBC.

Solução

Associe as Transações e tabelas do Transaction_lines e usar a coluna Date_closed.
 

Exemplo:

 

SELECT
  TRANSACTION_ID,
  DATE_CLOSED AS DATE,
   - SUM(AMOUNT_FOREIGN) AS AMOUNT
FROM
  TRANSACTION_LINES
  INNER JOIN
    TRANSACTIONS
    ON TRANSACTION_LINES.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID
WHERE
  TRANSACTIONS.TRANSACTION_TYPE = 'Sales Order'
  AND ACCOUNT_ID IS NOT NULL
  AND TRANSACTION_LINE_ID != 0
  AND TAX_TYPE IS NULL
  AND ITEM_COUNT != 0
  AND DATE_CLOSED IS NOT NULL
  AND
  (
    DATE_CLOSED BETWEEN TO_DATE ('2011/01/01', 'yyyy/mm/dd')AND TO_DATE ('2019/12/31', 'yyyy/mm/dd')
  )
  AND NOT EXISTS
  (
    SELECT
      0
    FROM