Anyone tried to create a query for open AP transactions with an 'As of date' parameter?Basically mimicking the behaviour of the standard AP reports.

Many ways to filter on dates. You’re trying to sum the total for a specific date range?

-- query account balances based on a specified date
SELECT
    BUILTIN.DF(transactionLine.subsidiary) AS subsidiary,
    BUILTIN.DF(TransactionAccountingLine.Account) AS account,
    BUILTIN.DF(TransactionAccountingLine.accountingBook) AS book,
    SUM(
        CASE
            WHEN BUILTIN.RELATIVE_RANGES('TODAY', 'START') >= accountingPeriod.startdate THEN TransactionAccountingLine.amount
            ELSE 0
        END
    ) AS todays_balance,
    SUM(
        CASE
            WHEN BUILTIN.RELATIVE_RANGES('TM', 'START') >= accountingPeriod.startdate THEN TransactionAccountingLine.amount
            ELSE 0
        END
    ) AS this_month_beginning_balance,
    SUM(
        CASE
            WHEN BUILTIN.RELATIVE_RANGES('LM', 'START') >= accountingPeriod.startdate THEN TransactionAccountingLine.amount
            ELSE 0
        END
    ) AS last_month_beginning_balance,
    SUM(
        CASE
            WHEN TRUNC(ADD_MONTHS(sysdate, -3), 'mm') >= accountingPeriod.startdate THEN TransactionAccountingLine.amount
            ELSE 0
        END
    ) AS specific_month_difference_3_months_ago,
    SUM(
        CASE
            WHEN TO_DATE('2022-01-01', 'YYYY-MM-DD') >= accountingPeriod.startdate THEN TransactionAccountingLine.amount
            ELSE 0
        END
    ) AS specific_date,
FROM
    TransactionAccountingLine
    INNER JOIN TransactionLine ON (
        TransactionLine.Transaction = TransactionAccountingLine.Transaction
    )
    AND (
        TransactionLine.ID = TransactionAccountingLine.TransactionLine
    )
    INNER JOIN Transaction ON TransactionAccountingLine.transaction = Transaction.id
    INNER JOIN accountingPeriod ON accountingPeriod.id = transaction.postingperiod
    LEFT OUTER JOIN accountingBook ON accountingBook.id = TransactionAccountingLine.accountingBook
WHERE
    transactionAccountingLine.posting = 'T'
GROUP BY
    BUILTIN.DF(transactionLine.subsidiary),
    BUILTIN.DF(TransactionAccountingLine.Account),
    BUILTIN.DF(TransactionAccountingLine.accountingBook)

No, I'm talking about to see which bills were open as of a date. Including bills that have been paid with payments/journals dated after that date. Let's say I want to see which bills were open by end of July. Than I would include all open bills up until the end of July and also include any bills prior to that date that have prev/next transaction links to transactions after that date. But probably something smarter to handle partly bills that were paid on both sides of the cut-off.

this should be able to give you an as of date. I’m not super familiar with the AP report but this query is a bit of a bear but might give you a place to start. You can also switch out the transactionLine amounts for the transactionAccountingLine amount columns

SELECT
  (bill.transactioin_amount - bill.amount_paid) as remaining_amount,
  CASE
    WHEN (
      bill.transactioin_amount = 0
      AND bill.amount_paid = 0
    ) THEN 'NA'
    WHEN (bill.transactioin_amount - bill.amount_paid) = 0 THEN 'PAID'
    ELSE 'OPEN'
  END as status,
  *
FROM
  (
    SELECT
      transaction.id,
      transaction.trandate,
      BUILTIN.DF(transaction.postingperiod) as period,
      BUILTIN.DF(transactionLine.subsidiary) AS subsidiary,
      SUM(transactionLine.creditforeignamount) as transactioin_amount_total,
      SUM(
        CASE
          WHEN transaction.trandate <= TO_DATE('2022-9-01', 'YYYY-MM-DD') THEN transactionLine.creditforeignamount
          ELSE 0
        END
      ) AS transactioin_amount,
      NVL(payments.amount, 0) as amount_paid,
    FROM
      TransactionAccountingLine
      INNER JOIN TransactionLine ON (
        TransactionLine.Transaction = TransactionAccountingLine.Transaction
      )
      AND (
        TransactionLine.ID = TransactionAccountingLine.TransactionLine
      )
      INNER JOIN Transaction ON TransactionAccountingLine.transaction = Transaction.id
      LEFT OUTER JOIN (
        SELECT
          transactionLine.createdfrom,
          SUM(
            NVL(transactionLine.foreignAmount, 0) + NVL(voidedjournal.netamount, 0)
          ) as amount
        FROM
          TransactionAccountingLine
          INNER JOIN TransactionLine ON (
            TransactionLine.Transaction = TransactionAccountingLine.Transaction
          )
          AND (
            TransactionLine.ID = TransactionAccountingLine.TransactionLine
          )
          INNER JOIN Transaction ON TransactionAccountingLine.transaction = Transaction.id
          LEFT OUTER JOIN (
            select
              transaction.reversal,
              transactionLine.creditforeignamount,
              transactionLine.netamount
            from
              transaction
              INNER JOIN TransactionLine ON (TransactionLine.Transaction = transaction.id)
            WHERE
              transaction.reversal IS NOT NULL
              AND transaction.trandate <= TO_DATE('2022-09-01', 'YYYY-MM-DD')
              AND transactionLine.creditforeignamount IS NOT NULL
          ) as voidedjournal ON voidedjournal.reversal = transaction.id
        WHERE
          transactionLine.createdFrom IS NOT NULL
          AND transaction.trandate <= TO_DATE('2022-09-01', 'YYYY-MM-DD')
        GROUP BY
          transactionLine.createdfrom
      ) as payments ON payments.createdfrom = transaction.id
    WHERE
      transactionAccountingLine.posting = 'T'
      AND transaction.recordtype = 'vendorbill'
      AND transactionLine.creditforeignamount IS NOT NULL
    GROUP BY
      transaction.id,
      transaction.trandate,
      BUILTIN.DF(transaction.postingperiod),
      transaction.recordtype,
      BUILTIN.DF(transactionLine.subsidiary),
      payments.amount
    ORDER BY
      transaction.trandate DESC
  ) as bill

Thanks @Matt Bernstein, but that only appear to take the records that have a link through

createdfrom

field into consideration. And that isn't necessarily the case. I think you have to use NextTransactionLineLink and PreviousTransactionLineLink.

There are probably a lot of different pieces including discounts and more that you would need to account for still

SELECT
  (bill.transactioin_amount - bill.amount_paid) as remaining_amount,
  CASE
    WHEN (
      bill.transactioin_amount = 0
      AND bill.amount_paid = 0
    ) THEN 'NA'
    WHEN (bill.transactioin_amount + bill.amount_paid) = 0 THEN 'PAID'
    ELSE 'OPEN'
  END as status,
  *
FROM
  (
    SELECT
      transaction.id,
      transaction.trandate,
      BUILTIN.DF(transaction.postingperiod) as period,
      BUILTIN.DF(transactionLine.subsidiary) AS subsidiary,
      SUM(transactionLine.creditforeignamount) as transactioin_amount_total,
      SUM(
        CASE
          WHEN transaction.trandate <= TO_DATE('2022-10-01', 'YYYY-MM-DD') THEN transactionLine.creditforeignamount
          ELSE 0
        END
      ) AS transactioin_amount,
      NVL(payments.amount, 0) as amount_paid,
    FROM
      TransactionAccountingLine
      INNER JOIN TransactionLine ON (
        TransactionLine.Transaction = TransactionAccountingLine.Transaction
      )
      AND (
        TransactionLine.ID = TransactionAccountingLine.TransactionLine
      )
      INNER JOIN Transaction ON TransactionAccountingLine.transaction = Transaction.id
      LEFT OUTER JOIN (
        SELECT
          NT.source_transaction,
          SUM(NT.ForeignTotal) as amount
        FROM
          (
            --   SUBQUERY WILL PREVENT DUPLICATE LINES
            SELECT
              DISTINCT NT.ID,
              NT.TranDate,
              NT.Type,
              NT.TranID,
              NT.Status,
              NT.ForeignTotal,
              NTLL.previousdoc as source_transaction
            FROM
              NextTransactionLineLink AS NTLL
              INNER JOIN Transaction AS NT ON (NT.ID = NTLL.NextDoc)
            WHERE
              NT.trandate <= TO_DATE('2022-10-01', 'YYYY-MM-DD')
              AND NT.status != 'V'
            ORDER BY
              NT.ID
          ) as NT
        GROUP BY
          NT.source_transaction
      ) as payments ON payments.source_transaction = transaction.id
    WHERE
      transactionAccountingLine.posting = 'T'
      AND transaction.recordtype = 'vendorbill'
      AND transactionLine.creditforeignamount IS NOT NULL
    GROUP BY
      transaction.id,
      transaction.trandate,
      BUILTIN.DF(transaction.postingperiod),
      transaction.recordtype,
      BUILTIN.DF(transactionLine.subsidiary),
      payments.amount
    ORDER BY
      transaction.trandate DESC
  ) as bill