AR Transaction Distributions (Oracle Fusion SQL)

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

This AR transaction distributions report retrieves detailed customer, transaction, accounting and revenue information including invoice currency, transaction types, pricing, quantities, and financial distribution amounts.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves customer, transaction, accounting, revenue and distribution details.
  HAOU.NAME 							ORGANIZATION_NAME,
  HCA.ACCOUNT_NUMBER 					CUSTOMER_ACCOUNT_NUMBER,
  HP.PARTY_NAME 						CUSTOMER_NAME,
  RCTA.TRX_NUMBER 						TRANSACTION_NUMBER,
  RCTTA.NAME 							TRANSACTION_TYPE_NAME,
  RCTLGDA.GL_DATE 						ACCOUNTING_DATE,
  RCTLGDA.GL_POSTED_DATE 				DATE_POSTED,
  RCTLGDA.ACCOUNT_CLASS,
  RCTLA.LINE_NUMBER,
  RCTLA.DESCRIPTION 					LINE_DESCRIPTION,
  RCTLA.LINE_TYPE,
  RCTA.INVOICE_CURRENCY_CODE 			CURRENCY,
  RCTLA.UNIT_SELLING_PRICE,
  SUM(NVL(RCTLA.REVENUE_AMOUNT, 0)) 	REVENUE_AMOUNT,
  SUM(NVL(RCTLGDA.AMOUNT, 0)) 			DISTRIBUTION_AMOUNT,
  SUM(NVL(RCTLA.QUANTITY_INVOICED, 0)) 	QUANTITY_INVOICED,
  SUM(NVL(RCTLA.QUANTITY_ORDERED, 0)) 	QUANTITY_ORDERED
FROM
  RA_CUST_TRX_LINE_GL_DIST_ALL 	RCTLGDA,
  RA_CUSTOMER_TRX_LINES_ALL 	RCTLA,
  RA_CUSTOMER_TRX_ALL 			RCTA,
  HZ_CUST_ACCOUNTS 				HCA,
  HR_ALL_ORGANIZATION_UNITS 	HAOU,
  RA_CUST_TRX_TYPES_ALL 		RCTTA,
  HZ_PARTIES 					HP
WHERE
  RCTLA.CUSTOMER_TRX_LINE_ID 	= RCTLGDA.CUSTOMER_TRX_LINE_ID
  AND RCTA.CUSTOMER_TRX_ID 		= RCTLGDA.CUSTOMER_TRX_ID
  AND RCTA.BILL_TO_CUSTOMER_ID 	= HCA.CUST_ACCOUNT_ID
  AND HAOU.ORGANIZATION_ID 		= RCTA.ORG_ID
  AND RCTA.CUST_TRX_TYPE_SEQ_ID = RCTTA.CUST_TRX_TYPE_SEQ_ID
  AND HCA.PARTY_ID 				= HP.PARTY_ID
GROUP BY
  HAOU.NAME,
  HP.PARTY_NAME,
  HCA.ACCOUNT_NUMBER,
  RCTA.TRX_NUMBER,
  RCTTA.NAME,
  RCTLGDA.GL_DATE,
  RCTLGDA.GL_POSTED_DATE,
  RCTLGDA.ACCOUNT_CLASS,
  RCTLA.LINE_NUMBER,
  RCTLA.DESCRIPTION,
  RCTLA.LINE_TYPE,
  RCTA.INVOICE_CURRENCY_CODE,
  RCTLA.UNIT_SELLING_PRICE