AR Discount Projection Report (SQL Script)

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

This AR Discount Projection Report query generates the Discount Project Report to view your exposure to discounts. Accounts Receivables calculated your projected discount based on a date specified for customers to pay their outstanding debit items.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project) 
  --This query produces the Discount Project Report, allowing you to assess your discount exposure.
  HAOU.NAME ORGANIZATION_NAME,
  APSA.TRX_NUMBER TRANSACTION_NUMBER,
  TO_CHAR(APSA.TRX_DATE, 'DD-MON-RR') TRANSACTION_DATE,
  SUBSTRB (HP.PARTY_NAME, 1, 50) CUSTOMER_NAME, 
  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER, 
  APSA.INVOICE_CURRENCY_CODE CURRENCY_CODE,
  NVL(APSA.DISCOUNT_TAKEN_EARNED,0) DISCOUNT_TAKEN_EARNED,
  NVL(APSA.DISCOUNT_TAKEN_UNEARNED,0) DISCOUNT_TAKEN_UNEARNED,
  NVL(APSA.AMOUNT_LINE_ITEMS_ORIGINAL,0 ) LINE_AMOUNT_ORIGINAL,
  APSA.AMOUNT_DUE_REMAINING,
  APSA.DUE_DATE,  
  RT.NAME TERM_NAME,
  RT.CALC_DISCOUNT_ON_LINES_FLAG DISCOUNT_ON_LINES,
  RT.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT,
  APSA.TERMS_SEQUENCE_NUMBER SEQENCE_NUMBER,
  NVL(APSA.AMOUNT_DUE_ORIGINAL,0) AMOUNT_DUE_ORIGINAL,
  AL.MEANING PAYMENT_SCHEDULE_CLASS,
  NVL(HCPF.DISCOUNT_GRACE_DAYS,0) DISCOUNT_GRACE_DAYS, 
  AXCLV.ACCOUNT_CLASS,
  HCPF.ACCOUNT_STATUS,
  GCC.ACCOUNT_TYPE,
  HP.CATEGORY_CODE, 
  HCPF.CONS_INV_TYPE,
  HCPF.CREDIT_RATING,
  HCA.CUSTOMER_TYPE,
  APSA.FOLLOW_UP_CODE_LAST,
  HCPF.LOCKBOX_MATCHING_OPTION,
  HCPF.RISK_CODE,
  HCA.STATUS CUSTOMER_ACCOUNT_STATUS,
 -- APSA.STATUS PAYMENT_SCHEDULE_STATUS,
  HCPF.TAX_PRINTING_OPTION,
  HCA.TAX_ROUNDING_RULE
FROM 
  RA_CUSTOMER_TRX_ALL RCTA,
  AR_PAYMENT_SCHEDULES_ALL APSA,
  HZ_CUST_ACCOUNTS HCA,  
  HZ_CUSTOMER_PROFILES_F HCPF,
  RA_TERMS RT,
  GL_CODE_COMBINATIONS GCC,  
  AR_XLA_CTLGD_LINES_V AXCLV,
  HZ_PARTIES HP,
  AR_LOOKUPS AL,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
  AND HCA.CUST_ACCOUNT_ID = APSA.CUSTOMER_ID
  AND APSA.CUSTOMER_ID = HCPF.CUST_ACCOUNT_ID
  AND HCPF.SITE_USE_ID IS NULL
  AND APSA.TERM_ID = RT.TERM_ID(+)
  AND RCTA.CUSTOMER_TRX_ID = AXCLV.CUSTOMER_TRX_ID
  AND DECODE(RCTA.INVOICING_RULE_ID,NULL, 'N', 'Y')= AXCLV.ACCOUNT_SET_FLAG 
  AND AXCLV.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
  AND HCA.PARTY_ID = HP.PARTY_ID
  AND APSA.STATUS = 'OP'
  AND APSA.CLASS != 'GUAR'
  AND AXCLV.ACCOUNT_CLASS = 'REC'
  AND AL.LOOKUP_TYPE = 'INV/CM'
  AND AL.LOOKUP_CODE = APSA.CLASS
  AND HAOU.ORGANIZATION_ID = RCTA.ORG_ID