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