IEX_DELINQUENCIES_NEW

Details

Name
PAYMENT_SCHEDULE_ID CUSTOMER_TRX_ID PARTY_ID CUST_ACCOUNT_ID CLASS STATUS AMOUNT_DUE_ORIGINAL AMOUNT_DUE_REMAINING TRX_DATE GL_DATE <column 11>

Name

PAYMENT_SCHEDULE_ID

CUSTOMER_TRX_ID

PARTY_ID

CUST_ACCOUNT_ID

CLASS

STATUS

AMOUNT_DUE_ORIGINAL

AMOUNT_DUE_REMAINING

TRX_DATE

GL_DATE

SQL_Statement
SELECT PS.PAYMENT_SCHEDULE_ID ,PS.CUSTOMER_TRX_ID ,CST.PARTY_ID ,CST.CUST_ACCOUNT_ID ,PS.CLASS ,PS.STATUS ,PS.AMOUNT_DUE_ORIGINAL ,PS.AMOUNT_DUE_REMAINING ,PS.TRX_DATE ,PS.GL_DATE ,DECODE(PS.CUSTOMER_ID , -1,'' ,-2,'', -3,'', PS.DUE_DATE) FROM HZ_CUST_ACCOUNTS CST ,AR_PAYMENT_SCHEDULES PS WHERE CST.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID AND PS.CLASS IN ('DM','INV', 'CB') AND PS.AMOUNT_DUE_REMAINING > 0 AND PS.STATUS = 'OP' AND NVL(PS.EXCLUDE_FROM_DUNNING_FLAG, 'N') != 'C' AND (ABS(PS.AMOUNT_IN_DISPUTE) < PS.AMOUNT_DUE_REMAINING OR PS.AMOUNT_IN_DISPUTE IS NULL) AND PS.DUE_DATE < trunc(SYSDATE) AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL

SQL_Statement

SELECT PS.PAYMENT_SCHEDULE_ID

,PS.CUSTOMER_TRX_ID

,CST.PARTY_ID

,CST.CUST_ACCOUNT_ID

,PS.CLASS

,PS.STATUS

,PS.AMOUNT_DUE_ORIGINAL

,PS.AMOUNT_DUE_REMAINING

,PS.TRX_DATE

,PS.GL_DATE

,DECODE(PS.CUSTOMER_ID , -1,'' ,-2,'', -3,'', PS.DUE_DATE)

FROM

HZ_CUST_ACCOUNTS CST

,AR_PAYMENT_SCHEDULES PS

WHERE CST.CUST_ACCOUNT_ID(+) = PS.CUSTOMER_ID

AND PS.CLASS IN ('DM','INV', 'CB')

AND PS.AMOUNT_DUE_REMAINING > 0

AND PS.STATUS = 'OP'

AND NVL(PS.EXCLUDE_FROM_DUNNING_FLAG, 'N') != 'C'

AND (ABS(PS.AMOUNT_IN_DISPUTE) < PS.AMOUNT_DUE_REMAINING

OR PS.AMOUNT_IN_DISPUTE IS NULL)

AND PS.DUE_DATE < trunc(SYSDATE)

AND PS.SELECTED_FOR_RECEIPT_BATCH_ID IS NULL