VIEW: APPS.IEX_F_SCORE_INVOICES_QUICK_V

Source

View Text - Preformatted

SELECT /*+ full(ar) parallel(ar) */ payment_schedule_id FROM ar_payment_schedules ar WHERE ar.due_date <= TRUNC ( sysdate ) AND ar.status = 'OP' AND ar.amount_due_remaining > 0 AND ar.class IN ( 'INV' , 'DM' , 'CB' ) AND NOT EXISTS (SELECT 1 FROM iex_delinquencies_all dll WHERE dll.payment_schedule_id = ar.payment_schedule_id AND dll.status = 'DELINQUENT' ) UNION ALL SELECT /*+ full ( ar ) parallel ( ar ) */ payment_schedule_id FROM ar_payment_schedules ar WHERE ar.due_date <= TRUNC ( sysdate ) AND ar.status = 'OP ' AND ar.amount_due_remaining <1 AND ar.class IN ( 'INV' , 'DM' , 'CB' ) AND EXISTS (SELECT 1 FROM iex_delinquencies_all dll WHERE dll.payment_schedule_id = ar.payment_schedule_id AND dll.status = 'DELINQUENT' ) UNION ALL SELECT /*+ full ( ar ) parallel ( ar ) */ payment_schedule_id FROM ar_payment_schedules ar WHERE ar.due_date <= TRUNC ( sysdate ) AND ar.status = 'OP' AND ar.amount_in_dispute IS NOT NULL AND ar.class IN ( 'INV' , 'DM' , 'CB' ) AND EXISTS (SELECT 1 FROM iex_delinquencies_all dll WHERE dll.payment_schedule_id = ar.payment_schedule_id AND dll.status = 'DELINQUENT' ) UNION ALL SELECT /*+ full ( ar ) parallel ( ar ) */ payment_schedule_id FROM ar_payment_schedules ar WHERE ar.class IN ( 'INV' , 'DM' , 'CB' ) AND ar.actual_date_closed > TRUNC ( sysdate - 60 ) AND ar.due_date <= TRUNC ( sysdate ) AND ar.status = 'CL' AND EXISTS (SELECT 1 FROM iex_delinquencies_all dll WHERE dll.payment_schedule_id = ar.payment_schedule_id AND dll.status = 'DELINQUENT' )

View Text - HTML Formatted

SELECT /*+ FULL(AR) PARALLEL(AR) */ PAYMENT_SCHEDULE_ID
FROM AR_PAYMENT_SCHEDULES AR
WHERE AR.DUE_DATE <= TRUNC ( SYSDATE )
AND AR.STATUS = 'OP'
AND AR.AMOUNT_DUE_REMAINING > 0
AND AR.CLASS IN ( 'INV'
, 'DM'
, 'CB' )
AND NOT EXISTS (SELECT 1
FROM IEX_DELINQUENCIES_ALL DLL
WHERE DLL.PAYMENT_SCHEDULE_ID = AR.PAYMENT_SCHEDULE_ID
AND DLL.STATUS = 'DELINQUENT' ) UNION ALL SELECT /*+ FULL ( AR ) PARALLEL ( AR ) */ PAYMENT_SCHEDULE_ID
FROM AR_PAYMENT_SCHEDULES AR
WHERE AR.DUE_DATE <= TRUNC ( SYSDATE )
AND AR.STATUS = 'OP '
AND AR.AMOUNT_DUE_REMAINING <1
AND AR.CLASS IN ( 'INV'
, 'DM'
, 'CB' )
AND EXISTS (SELECT 1
FROM IEX_DELINQUENCIES_ALL DLL
WHERE DLL.PAYMENT_SCHEDULE_ID = AR.PAYMENT_SCHEDULE_ID
AND DLL.STATUS = 'DELINQUENT' ) UNION ALL SELECT /*+ FULL ( AR ) PARALLEL ( AR ) */ PAYMENT_SCHEDULE_ID
FROM AR_PAYMENT_SCHEDULES AR
WHERE AR.DUE_DATE <= TRUNC ( SYSDATE )
AND AR.STATUS = 'OP'
AND AR.AMOUNT_IN_DISPUTE IS NOT NULL
AND AR.CLASS IN ( 'INV'
, 'DM'
, 'CB' )
AND EXISTS (SELECT 1
FROM IEX_DELINQUENCIES_ALL DLL
WHERE DLL.PAYMENT_SCHEDULE_ID = AR.PAYMENT_SCHEDULE_ID
AND DLL.STATUS = 'DELINQUENT' ) UNION ALL SELECT /*+ FULL ( AR ) PARALLEL ( AR ) */ PAYMENT_SCHEDULE_ID
FROM AR_PAYMENT_SCHEDULES AR
WHERE AR.CLASS IN ( 'INV'
, 'DM'
, 'CB' )
AND AR.ACTUAL_DATE_CLOSED > TRUNC ( SYSDATE - 60 )
AND AR.DUE_DATE <= TRUNC ( SYSDATE )
AND AR.STATUS = 'CL'
AND EXISTS (SELECT 1
FROM IEX_DELINQUENCIES_ALL DLL
WHERE DLL.PAYMENT_SCHEDULE_ID = AR.PAYMENT_SCHEDULE_ID
AND DLL.STATUS = 'DELINQUENT' )