Query to fetch Prepayment amount used from a Prepayment invoice

Query to fetch Prepayment used from a Prepayment invoice
  SELECT SUM(total_dist_amount -
         NVL(prepay_amount_remaining, total_dist_amount))
    FROM ap_invoice_distributions_all aid,
         ap_invoice_lines_all ail
   WHERE aid.invoice_id = &P_invoice_id
     AND aid.invoice_id = ail.invoice_id
     AND aid.invoice_line_number = ail.line_number
     AND ail.line_type_lookup_code <> 'TAX'
     AND aid.line_type_lookup_code IN
         ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
     AND NVL(reversal_flag,'N') <> 'Y';

Query to fetch Prepaid amount for a Standard Invoice

Query to fetch Prepaid amount for a Standard Invoice

 SELECT  (0 - SUM(NVL(aid.amount,0))) prepaid_amount
    FROM ap_invoice_distributions_all aid,
         ap_invoice_lines_all         ail
     WHERE ail.invoice_id = &P_invoice_id -- invoice id of std invoice
     AND   ail.invoice_id = aid.invoice_id
     AND   ail.line_number = aid.invoice_line_number
     AND   aid.line_type_lookup_code = 'PREPAY'
     AND   aid.prepay_distribution_id IS NOT NULL
     AND   NVL(ail.invoice_includes_prepay_flag, 'N') = 'N';

OR

  SELECT ABS(SUM(amount))
    FROM ap_invoice_lines_all ail
   WHERE ail.invoice_id = &P_invoice_id -- invoice id of std invoice
     AND ail.line_type_lookup_code = 'PREPAY';