Required SQL to fetch the Unpaid Invoice amount with respect to Installment wise

User_5ZFCV

We need to display the gross unpaid installment amounts of the invoice totaling 1500, which consists of three installments due on May 15, 2024 (900), May 30, 2024 (300), and May 30, 2024 (300). Given a prepayment on July 15, 2024, the query should show the unpaid amounts for each installment as of June 30, 2024, formatted as follows: (900 due on May 15, 2024 - 900 unpaid), (300 due on May 30, 2024 - 300 unpaid), (300 due on May 30, 2024 - 300 unpaid).

in above image I required to fetch data based on expectation mention in image 2. kindly help me to find the query to fetch data in that format.
Navigation: Payable → Manage invoices → installment.

Sumanth V -Oracle

@User_5ZFCV - It appears you are using FUSION BIP and the screen shot is from Fusion application. Kindly confirm.

User_5ZFCV

Yes Sumanth
using FUSION BIP and the screen shot is from Fusion application in first image

Sumanth V -Oracle

@John Hung-Oracle - Please assist in moving this thread to OTBI community for better responses as the query is related to Fusion functionality. Thanks!