AR Accounting Rules Listing (Oracle Fusion SQL)

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

#oracle fusion sql

AR Accounting Rules Listing — Oracle Fusion SQL Query

This post provides a ready-to-use SQL query to list Accounts Receivable (AR) accounting / revenue-recognition rules in Oracle Fusion — along with their schedule, frequency, status, and rule details. The output helps you understand how revenue recognition is configured in your instance.

What the query returns

  • Rule name and description
  • Rule type (e.g. fixed, variable, etc.)
  • Frequency or schedule period (how often the rule applies)
  • Number of periods (if defined)
  • Rule status (active, inactive, etc.)
  • For each schedule: period number, percentage allocation, rule date — useful to see how revenue gets recognized across periods

Why this report is useful

  • To audit and document revenue recognition setup — ensuring that your AR revenue rules are correctly defined and active.
  • To review deferred / installment billing rules — helpful when invoices or revenue span multiple periods.
  • To provide transparency for accounting and finance teams — being able to list all rules in one consolidated output.
  • To assist with reporting, compliance checks, data migration, or reconciliation — especially when revenue recognition rules change over time or between environments.

How to use & customize the query

  • Run the SQL against your Fusion AR schema. Ensure that your table aliases (e.g. RA_RULES, RA_RULE_SCHEDULES, lookup tables) match your environment.
  • Optionally add joins/columns to include additional metadata — e.g. created by, creation date, last update, or who modified the rule.
  • Filter by rule status (active/inactive), frequency, or rule name if you only want a subset.
  • Export results to spreadsheet or reporting tool for documentation, review, or audit trail..

#sqlquery

SELECT
 -- SQL4Fusion (An Orbit Analytics Project) 
 -- This query retrieves details for various revenue recognition rules. 
  RA.NAME NAME,
  AL1.MEANING TYPE,
  NVL (
    GPT.USER_PERIOD_TYPE,
    AL2.MEANING
  ) FREQUENCY,
  RA.DESCRIPTION DESCRIPTION,
  NVL (RA.OCCURRENCES, 0) NUM_OF_PERIODS,
  AL3.MEANING STATUS,
  RRS.PERIOD_NUMBER PERIOD,
  RRS.PERCENT PERCENT,
  RRS.RULE_DATE RULE_DATE,
  RA.RULE_ID RULE_ID
 FROM
  RA_RULES RA,
  GL_PERIOD_TYPES GPT,
  RA_RULE_SCHEDULES RRS,
  AR_LOOKUPS AL1,
  AR_LOOKUPS AL2,
  AR_LOOKUPS AL3
WHERE
  1 = 1
  AND AL1.LOOKUP_CODE = RA.TYPE
  AND AL1.LOOKUP_TYPE = 'RULE_TYPE'
  AND AL2.LOOKUP_CODE(+) = RA.FREQUENCY
  AND AL2.LOOKUP_TYPE(+) = 'SCHEDULE_PERIOD'
  AND GPT.PERIOD_TYPE(+) = RA.FREQUENCY
  AND AL3.LOOKUP_CODE = RA.STATUS
  AND AL3.LOOKUP_TYPE = 'CODE_STATUS'
  AND RA.RULE_ID = RRS.RULE_ID(+)
ORDER BY
  RA.NAME,
  RRS.PERIOD_NUMBER