Projects overview

SELECT TO_CHAR(tb.trandate, 'YYYY-MM') AS period,
       BUILTIN.DF(tb.employee) AS employee,
       SUM(tb.hours) AS hours,
       SUM(CASE WHEN tb.isbillable = 'T' THEN tb.hours ELSE 0 END) AS billable_hours,
       SUM(CASE WHEN tb.isbillable = 'T' THEN 0 ELSE tb.hours END) AS non_billable_hours,
       SUM(CASE WHEN tb.isbillable = 'T' THEN tb.hours ELSE 0 END) / NULLIF(SUM(tb.hours), 0) AS billable_share,
       COUNT(DISTINCT tb.customer) AS projects
  FROM timebill tb
 WHERE tb.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
   AND tb.trandate <= SYSDATE
 GROUP BY TO_CHAR(tb.trandate, 'YYYY-MM'), BUILTIN.DF(tb.employee)
 ORDER BY period DESC, hours DESC

SELECT TO_CHAR(tb.trandate, 'YYYY-MM') AS period,
       TO_CHAR(tb.trandate, 'YYYY-MM-DD') AS tran_date,
       BUILTIN.DF(tb.employee) AS employee,
       NVL(e.altname, e.entityid) AS customer_or_project,
       BUILTIN.DF(tb.item) AS service_item,
       tb.isbillable AS billable,
       BUILTIN.DF(tb.approvalstatus) AS approval_status,
       tb.hours AS hours,
       tb.memo AS memo
  FROM timebill tb
  LEFT JOIN entity e ON e.id = tb.customer
 WHERE tb.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
   AND tb.trandate <= SYSDATE
 ORDER BY tb.trandate DESC, employee

SELECT j.id AS project__id,
       j.altname AS project,
       NVL(cu.altname, cu.entityid) AS customer,
       NVL(BUILTIN.DF(j.entitystatus), '(no status)') AS status,
       TO_CHAR(j.startdate, 'YYYY-MM-DD') AS start_date,
       TO_CHAR(j.projectedenddate, 'YYYY-MM-DD') AS projected_end,
       NVL(h.hours_30, 0) AS hours_30,
       NVL(h.hours_total, 0) AS hours_total,
       TO_CHAR(h.last_entry, 'YYYY-MM-DD') AS last_time_entry
  FROM job j
  JOIN entity je ON je.id = j.id
  LEFT JOIN entity cu ON cu.id = je.toplevelparent
  LEFT JOIN (SELECT tb.customer,
                    SUM(CASE WHEN tb.trandate > TRUNC(SYSDATE) - 30 THEN tb.hours ELSE 0 END) AS hours_30,
                    SUM(tb.hours) AS hours_total,
                    MAX(tb.trandate) AS last_entry
               FROM timebill tb
              WHERE tb.trandate <= SYSDATE
              GROUP BY tb.customer) h ON h.customer = j.id
 WHERE j.isinactive = 'F'
 ORDER BY customer, project

SELECT j.id AS project__id,
       j.altname AS project,
       NVL(cu.altname, cu.entityid) AS customer,
       BUILTIN.DF(j.entitystatus) AS status,
       TO_CHAR(j.startdate, 'YYYY-MM-DD') AS start_date,
       NVL(f.revenue, 0) AS revenue,
       NVL(f.cost, 0) AS direct_cost,
       NVL(f.revenue, 0) - NVL(f.cost, 0) AS gross_profit,
       (NVL(f.revenue, 0) - NVL(f.cost, 0)) / NULLIF(f.revenue, 0) AS margin,
       NVL(h.hours, 0) AS hours,
       NVL(f.revenue, 0) / NULLIF(h.hours, 0) AS revenue_per_hour
  FROM job j
  JOIN entity je ON je.id = j.id
  LEFT JOIN entity cu ON cu.id = je.toplevelparent
  LEFT JOIN (SELECT p.project_id,
                    SUM(CASE WHEN p.side = 'Revenue' THEN p.amount ELSE 0 END) AS revenue,
                    SUM(CASE WHEN p.side = 'Cost' THEN p.amount ELSE 0 END) AS cost
               FROM (SELECT tl.entity AS project_id,
                       t.trandate AS tran_date,
                       t.type AS type_code,
                       NVL(t.tranid, t.transactionnumber) AS document,
                       a.displaynamewithhierarchy AS account,
                       CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN 'Revenue' ELSE 'Cost' END AS side,
                       CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN -tal.amount ELSE tal.amount END AS amount
                  FROM transactionaccountingline tal
                  JOIN accountingbook ab ON ab.id = tal.accountingbook AND ab.isprimary = 'T'
                  JOIN transaction t ON t.id = tal.transaction
                  JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
                  JOIN account a ON a.id = tal.account
                 WHERE tal.posting = 'T'
                   AND a.accttype IN ('Income', 'OthIncome', 'COGS', 'Expense', 'OthExpense')
                   AND tl.entity IN (SELECT j.id FROM job j)) p
              GROUP BY p.project_id) f ON f.project_id = j.id
  LEFT JOIN (SELECT tb.customer, SUM(tb.hours) AS hours
               FROM timebill tb
              GROUP BY tb.customer) h ON h.customer = j.id
 WHERE f.project_id IS NOT NULL OR h.customer IS NOT NULL
 ORDER BY revenue DESC

SELECT j.altname AS project,
       TO_CHAR(p.tran_date, 'YYYY-MM') AS period,
       TO_CHAR(p.tran_date, 'YYYY-MM-DD') AS tran_date,
       p.type_code AS type,
       p.document,
       p.account,
       p.side,
       p.amount
  FROM (SELECT tl.entity AS project_id,
                       t.trandate AS tran_date,
                       t.type AS type_code,
                       NVL(t.tranid, t.transactionnumber) AS document,
                       a.displaynamewithhierarchy AS account,
                       CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN 'Revenue' ELSE 'Cost' END AS side,
                       CASE WHEN a.accttype IN ('Income', 'OthIncome') THEN -tal.amount ELSE tal.amount END AS amount
                  FROM transactionaccountingline tal
                  JOIN accountingbook ab ON ab.id = tal.accountingbook AND ab.isprimary = 'T'
                  JOIN transaction t ON t.id = tal.transaction
                  JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
                  JOIN account a ON a.id = tal.account
                 WHERE tal.posting = 'T'
                   AND a.accttype IN ('Income', 'OthIncome', 'COGS', 'Expense', 'OthExpense')
                   AND tl.entity IN (SELECT j.id FROM job j)) p
  JOIN job j ON j.id = p.project_id
 WHERE p.amount <> 0
 ORDER BY project, p.tran_date DESC