AR Accounting Rules Listing (SQL Script)

Enterprise Resource Planning (ERP) Account Receivables (AR)
1

This AR Accounting Rules Listing query displays revenue recognition rules along with their schedule details, frequencies, types, and status information.

#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