AR Invoice Trend Report (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Receivables (AR-Fusion-SQL's)
1

This AR Invoice Trend Report query displays monthly AR invoice trends over the past 12 months by extracting the invoice date from customer transactions. It summarizes the total invoice amount and count of invoices per month, considering only standard invoice lines.

#sqlquery

SELECT
 --SQL4Fusion (An Orbit Analytics Project)
 --This query displays monthly AR invoice trends over the past 12 months
  TO_CHAR(RCTA.TRX_DATE, 'YYYY-MM') AS INVOICE_MONTH,
  COUNT(DISTINCT RCTA.TRX_NUMBER) AS INVOICE_COUNT,
  SUM(NVL(RCTLA.EXTENDED_AMOUNT, 0)) AS TOTAL_INVOICE_AMOUNT  
FROM
  RA_CUSTOMER_TRX_ALL RCTA,
  RA_CUSTOMER_TRX_LINES_ALL RCTLA,
  RA_CUST_TRX_TYPES_ALL RCTT,
  HZ_CUST_ACCOUNTS HCA,
  HZ_PARTIES HP
WHERE
  RCTA.CUSTOMER_TRX_ID = RCTLA.CUSTOMER_TRX_ID
  AND RCTA.CUST_TRX_TYPE_SEQ_ID = RCTT.CUST_TRX_TYPE_SEQ_ID
  AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
  AND HCA.PARTY_ID = HP.PARTY_ID
  AND RCTLA.LINE_TYPE = 'LINE'
  AND RCTT.TYPE = 'INV'
  AND RCTA.TRX_DATE >= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12) -- last 12 months
GROUP BY
  TO_CHAR(RCTA.TRX_DATE, 'YYYY-MM')
ORDER BY
  INVOICE_MONTH