1. Tables and Views for Financials
  2. IEX_F_SCORE_INVOICES_QUICK_V

IEX_F_SCORE_INVOICES_QUICK_V

Details

  • Schema: FUSION

  • Object owner: IEX

  • Object type: VIEW

Columns

Name

PAYMENT_SCHEDULE_ID

Query

SQL_Statement

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 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 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 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 dll

WHERE dll.payment_schedule_id = ar.payment_schedule_id

AND dll.status = 'DELINQUENT'

)