Oracle Analytics and AI

Kauan_Bohn

In which Payables (A/P) table can I find information related to prepayments?

I am currently developing a customized Aging report and need this information in order to mirror the prepayment date.

Please see the attached image from the application where I can view this data.

Best regards,

Kauan Bohn

Kauan_Bohn

That answer my question:

SELECT  bu, INVOICE, DATE_INVOICE, VALUE_INVOICE, 

        DATE_ACCOUNTING, amt, 

       prepay_amount_remaining, description

FROM

  (SELECT hou.name bu,

  ai.INVOICE_NUM INVOICE,

  TO_CHAR(ai.INVOICE_DATE, 'DD/MM/YYYY') DATE_INVOICE,

            TO_CHAR(AIL.accounting_date, 'DD/MM/YYYY') DATA_ACCOUNTING,


          ai.INVOICE_AMOUNT VALUE_INVOICE,

          AIL.PREPAY_LINE_NUMBER prepay_line_number,

          ai1.INVOICE_NUM applied_invoice,

          AIL.LINE_NUMBER applied_line_number,

          AIL.AMOUNT amt,

          AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING(AIL.PREPAY_INVOICE_ID, AIL.PREPAY_LINE_NUMBER) prepay_amount_remaining,

          AIL.DESCRIPTION descriptiona

   FROM ap_invoices_all ai,

        ap_invoice_lines_all ail,

        ap_invoices_all ai1,

        HR_ALL_ORGANIZATION_UNITS_TL hou



   WHERE AI.INVOICE_ID = AIL.PREPAY_INVOICE_ID

     AND AIL.INVOICE_ID = ai1.INVOICE_ID

     AND AIL.AMOUNT < 0

     AND ai.ORG_ID = hou.ORGANIZATION_ID

     AND NVL(AIL.DISCARDED_FLAG, 'N') <> 'Y'

     AND AIL.LINE_TYPE_LOOKUP_CODE = 'PREPAY'


  )

GROUP BY INVOICE, VALUE_INVOICE, prepay_line_number, applied_line_number, 

         prepay_amount_remaining, description, amt, DATA_ACCOUNTING, bu, DATE_INVOICE;