AR Auto Cash Rules (Oracle Fusion SQL)

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

This AR Auto Cash Rules query provides detailed information to review the sequence of Auto Cash rules assigned in the Auto Cash Rule Sets window.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project) 
  -- This query provides a detailed AutoCash Hierarchy Rules Report
  AAH.HIERARCHY_NAME,
  AAH.DESCRIPTION DESCRIPTION,
  SUBSTRB (AL2.MEANING, 1, 18) STATUS,
  SUBSTRB (AL3.MEANING, 1, 30) DISCOUNT,
  DECODE (INCLUDE_FINANCE_CHARGES, 'Y', 'YES', 'NO') FINANCE_CHARGES,
  DECODE (INCLUDE_DISPUTE_ITEMS, 'Y', 'YES', 'NO') DISPUTE_ITEMS,
  DECODE (APPLY_PARTIAL_PAYMENTS, 'Y', 'YES', 'NO') PARTIAL_PAYMENTS,
  SUBSTRB (AL4.MEANING, 1, 30) REMAINING_AMOUNT_STATUS,
  AAR.RULE_SEQUENCE SEQUENCE,
  SUBSTRB (AL1.MEANING, 1, 60) AUTOCASH_RULE,
  AAH.AUTOCASH_HIERARCHY_ID AUTOCASH_HIERARCHY_ID
FROM
  AR_AUTOCASH_RULES AAR,
  AR_AUTOCASH_HIERARCHIES AAH,
  AR_LOOKUPS AL1,
  AR_LOOKUPS AL2,
  AR_LOOKUPS AL3,
  AR_LOOKUPS AL4
WHERE
  1 = 1
  AND AAH.AUTOCASH_HIERARCHY_ID = AAR.AUTOCASH_HIERARCHY_ID
  AND AL2.LOOKUP_TYPE = 'CODE_STATUS'
  AND AL2.LOOKUP_CODE = AAH.STATUS
  AND AL3.LOOKUP_TYPE = 'AUTOCASH_DISCOUNTS'
  AND AL3.LOOKUP_CODE = AAH.INCLUDE_DISCOUNTS
  AND AL4.LOOKUP_TYPE = 'AUTOCASH_REMAINING_AMOUNT'
  AND AL4.LOOKUP_CODE = AAH.REMAINING_AMOUNT
  AND AL1.LOOKUP_TYPE = 'AUTOCASH_RULE'
  AND AL1.LOOKUP_CODE = AAR.AUTOCASH_RULE
ORDER BY
  AAH.HIERARCHY_NAME,
  AAH.AUTOCASH_HIERARCHY_ID,
  AAR.RULE_SEQUENCE