PE Investability Readiness Report

SELECT TO_CHAR(t.trandate,'YYYY') AS yr, TO_CHAR(t.trandate,'Q') AS qtr,
       a.accttype, ROUND(SUM(-tal.amount),2) AS amt
FROM transactionaccountingline tal
JOIN transaction t ON tal.transaction = t.id
JOIN account a ON tal.account = a.id
WHERE t.posting = 'T'
  AND t.trandate >= TO_DATE('2024-07-01','YYYY-MM-DD')
  AND t.trandate <  TO_DATE('2026-07-01','YYYY-MM-DD')
  AND a.accttype IN ('Income','OthIncome','COGS','Expense','OthExpense')
GROUP BY TO_CHAR(t.trandate,'YYYY'), TO_CHAR(t.trandate,'Q'), a.accttype
ORDER BY 1, 2, 3

SELECT COALESCE(cl.name,'(none)') AS product_class,
       ROUND(SUM(ABS(tl.netamount)),2) AS revenue, COUNT(DISTINCT t.id) AS tx_count
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline='F' AND tl.taxline='F'
LEFT JOIN classification cl ON tl.class = cl.id
WHERE t.type IN ('CustInvc','CashSale') AND t.posting='T'
  AND t.trandate >= TO_DATE('2025-07-01','YYYY-MM-DD')
  AND t.trandate <  TO_DATE('2026-07-01','YYYY-MM-DD')
  AND tl.subsidiary <> 4
GROUP BY COALESCE(cl.name,'(none)')
ORDER BY SUM(ABS(tl.netamount)) DESC
-- Channel version: replace classification join with
-- LEFT JOIN location l ON tl.location = l.id

-- Top customers (TTM)
SELECT c.entityid, ROUND(SUM(ABS(tl.netamount)),2) AS ttm_revenue, COUNT(DISTINCT t.id) AS orders
FROM transaction t
JOIN customer c ON t.entity = c.id
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline='F' AND tl.taxline='F'
WHERE t.type IN ('CustInvc','CashSale') AND t.posting='T'
  AND t.trandate >= TO_DATE('2025-07-01','YYYY-MM-DD')
  AND t.trandate <  TO_DATE('2026-07-01','YYYY-MM-DD') AND tl.subsidiary <> 4
GROUP BY c.entityid ORDER BY 2 DESC FETCH FIRST 15 ROWS ONLY

-- Retention: FULL OUTER JOIN of DISTINCT prior-year vs current-year
-- customer sets on the same filters (69 retained / 3 churned / 25 new)

SELECT CASE WHEN t.duedate >= TRUNC(SYSDATE) OR t.duedate IS NULL THEN '1_current'
            WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 30 THEN '2_1-30'
            WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 60 THEN '3_31-60'
            WHEN TRUNC(SYSDATE)-TRUNC(t.duedate) <= 90 THEN '4_61-90'
            ELSE '5_over90' END AS bucket,
       COUNT(*) AS invoices, ROUND(SUM(t.foreignamountunpaid),2) AS open_amount
FROM transaction t
WHERE t.type='CustInvc' AND t.status='A' AND t.foreignamountunpaid > 0
GROUP BY /* same CASE */ 1 ORDER BY 1
-- AP version: t.type='VendBill'
-- Worklist: add tranid, customer, duedate; filter duedate < SYSDATE-90

SELECT TO_CHAR(t.trandate,'YYYY-MM') AS mo,
       CASE WHEN t.type='Journal' THEN 'journal' ELSE 'billed' END AS src,
       ROUND(SUM(-tal.amount),2) AS income
FROM transactionaccountingline tal
JOIN transaction t ON tal.transaction = t.id
JOIN account a ON tal.account = a.id
WHERE t.posting='T' AND a.accttype IN ('Income','OthIncome')
  AND t.trandate >= TO_DATE('2025-07-01','YYYY-MM-DD')
  AND t.trandate <  TO_DATE('2026-07-01','YYYY-MM-DD')
GROUP BY TO_CHAR(t.trandate,'YYYY-MM'),
         CASE WHEN t.type='Journal' THEN 'journal' ELSE 'billed' END
ORDER BY 1, 2

-- Period locks (0 of 25 closed)
SELECT p.periodname, p.closed, p.aplocked, p.arlocked, p.alllocked
FROM accountingperiod p
WHERE p.isquarter='F' AND p.isyear='F' AND p.isadjust='F'
  AND p.startdate >= TO_DATE('2024-07-01','YYYY-MM-DD')
ORDER BY p.startdate

-- Future-dated posted transactions (209 / $596K)
SELECT COUNT(*) FROM transaction t
WHERE t.posting='T' AND t.trandate > TO_DATE('2026-08-10','YYYY-MM-DD')

-- Active Administrators (7)
SELECT COUNT(DISTINCT e.id) FROM employee e
JOIN employeerolesforsearch er ON er.entity = e.id
JOIN role r ON er.role = r.id
WHERE e.isinactive='F' AND r.name LIKE '%dministrator%'