VIEW: APPS.FV_AR_FIN_CHRG_INV_LINES_V

Source

View Text - Preformatted

SELECT i.invoice_id, i.invoice_number, l.gl_date, l.line_number, l.charge_id, l.line_amount, i.root_invoice_id, l.created_by, l.last_updated_by, l.creation_date, l.last_update_date, l.last_update_login, l.org_id, fcc.charge_type, SUM(NVL(arps.amount_due_original, 0)) finance_charges, nvl2(rctl.amount_due_remaining, l.line_amount-rctl.amount_due_remaining, (l.line_amount/SUM(NVL(arps.amount_due_original, 0)))*SUM(NVL(arps.amount_due_original, 0)-NVL(arps.amount_due_remaining, 0)) ) collection_amount FROM fv_ar_fin_chrg_invoices i, fv_ar_fin_chrg_inv_lines l, fv_finance_charge_controls fcc, ar_payment_schedules_all arps, ra_customer_trx_lines rctl WHERE i.invoice_id = l.invoice_id and fcc.charge_id = l.charge_id and arps.customer_trx_id = i.invoice_id and rctl.customer_trx_id = i.invoice_id and rctl.line_number = l.line_number group by i.invoice_id, i.invoice_number, l.gl_date, l.line_number, l.charge_id, l.line_amount, i.root_invoice_id, l.created_by, l.last_updated_by, l.creation_date, l.last_update_date, l.last_update_login, l.org_id, fcc.charge_type, rctl.amount_due_remaining, arps.amount_due_original

View Text - HTML Formatted

SELECT I.INVOICE_ID
, I.INVOICE_NUMBER
, L.GL_DATE
, L.LINE_NUMBER
, L.CHARGE_ID
, L.LINE_AMOUNT
, I.ROOT_INVOICE_ID
, L.CREATED_BY
, L.LAST_UPDATED_BY
, L.CREATION_DATE
, L.LAST_UPDATE_DATE
, L.LAST_UPDATE_LOGIN
, L.ORG_ID
, FCC.CHARGE_TYPE
, SUM(NVL(ARPS.AMOUNT_DUE_ORIGINAL
, 0)) FINANCE_CHARGES
, NVL2(RCTL.AMOUNT_DUE_REMAINING
, L.LINE_AMOUNT-RCTL.AMOUNT_DUE_REMAINING
, (L.LINE_AMOUNT/SUM(NVL(ARPS.AMOUNT_DUE_ORIGINAL
, 0)))*SUM(NVL(ARPS.AMOUNT_DUE_ORIGINAL
, 0)-NVL(ARPS.AMOUNT_DUE_REMAINING
, 0)) ) COLLECTION_AMOUNT
FROM FV_AR_FIN_CHRG_INVOICES I
, FV_AR_FIN_CHRG_INV_LINES L
, FV_FINANCE_CHARGE_CONTROLS FCC
, AR_PAYMENT_SCHEDULES_ALL ARPS
, RA_CUSTOMER_TRX_LINES RCTL
WHERE I.INVOICE_ID = L.INVOICE_ID
AND FCC.CHARGE_ID = L.CHARGE_ID
AND ARPS.CUSTOMER_TRX_ID = I.INVOICE_ID
AND RCTL.CUSTOMER_TRX_ID = I.INVOICE_ID
AND RCTL.LINE_NUMBER = L.LINE_NUMBER GROUP BY I.INVOICE_ID
, I.INVOICE_NUMBER
, L.GL_DATE
, L.LINE_NUMBER
, L.CHARGE_ID
, L.LINE_AMOUNT
, I.ROOT_INVOICE_ID
, L.CREATED_BY
, L.LAST_UPDATED_BY
, L.CREATION_DATE
, L.LAST_UPDATE_DATE
, L.LAST_UPDATE_LOGIN
, L.ORG_ID
, FCC.CHARGE_TYPE
, RCTL.AMOUNT_DUE_REMAINING
, ARPS.AMOUNT_DUE_ORIGINAL