Financial overview

SELECT period,
       revenue,
       cost_of_sales,
       revenue - cost_of_sales AS gross_profit,
       operating_expenses,
       other_net,
       revenue - cost_of_sales - operating_expenses + other_net AS net_income,
       (revenue - cost_of_sales) / NULLIF(revenue, 0) AS gross_margin,
       (revenue - cost_of_sales - operating_expenses + other_net) / NULLIF(revenue, 0) AS net_margin
  FROM (SELECT TO_CHAR(p.startdate, 'YYYY-MM') AS period,
               SUM(CASE WHEN a.accttype = 'Income' THEN -tal.amount ELSE 0 END) AS revenue,
               SUM(CASE WHEN a.accttype = 'COGS' THEN tal.amount ELSE 0 END) AS cost_of_sales,
               SUM(CASE WHEN a.accttype = 'Expense' THEN tal.amount ELSE 0 END) AS operating_expenses,
               SUM(CASE WHEN a.accttype IN ('OthIncome', 'OthExpense') THEN -tal.amount ELSE 0 END) AS other_net
          FROM transactionaccountingline tal
          JOIN accountingbook ab ON ab.id = tal.accountingbook AND ab.isprimary = 'T'
          JOIN transaction t ON t.id = tal.transaction
          JOIN account a ON a.id = tal.account
          JOIN accountingperiod p ON p.id = t.postingperiod
         WHERE tal.posting = 'T'
           AND a.accttype IN ('Income', 'COGS', 'Expense', 'OthIncome', 'OthExpense')
           AND p.startdate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
           AND p.startdate <= SYSDATE
         GROUP BY TO_CHAR(p.startdate, 'YYYY-MM'))
 ORDER BY period

SELECT a.id AS account__id,
       a.displaynamewithhierarchy AS account,
       SUM(tal.amount) AS balance,
       SUM(CASE WHEN t.trandate > TRUNC(SYSDATE) - 30 AND tal.amount > 0 THEN tal.amount ELSE 0 END) AS inflow_30,
       SUM(CASE WHEN t.trandate > TRUNC(SYSDATE) - 30 AND tal.amount < 0 THEN -tal.amount ELSE 0 END) AS outflow_30,
       SUM(CASE WHEN t.trandate > TRUNC(SYSDATE) - 30 THEN tal.amount ELSE 0 END) AS net_30,
       TO_CHAR(MAX(t.trandate), 'YYYY-MM-DD') AS last_posting
  FROM transactionaccountingline tal
  JOIN accountingbook ab ON ab.id = tal.accountingbook AND ab.isprimary = 'T'
  JOIN transaction t ON t.id = tal.transaction
  JOIN account a ON a.id = tal.account
 WHERE tal.posting = 'T'
   AND a.accttype = 'Bank'
   AND t.trandate <= SYSDATE
 GROUP BY a.id, a.displaynamewithhierarchy
 ORDER BY balance DESC

SELECT t.id AS journal__id,
       t.tranid AS journal,
       TO_CHAR(t.trandate, 'YYYY-MM-DD') AS tran_date,
       BUILTIN.DF(t.postingperiod) AS posting_period,
       BUILTIN.DF(t.createdby) AS created_by,
       t.memo AS memo,
       TRUNC(SYSDATE) - TRUNC(t.createddate) AS days_waiting,
       BUILTIN.DF(t.currency) AS currency,
       d.total_debits
  FROM transaction t
  JOIN (SELECT tl.transaction, SUM(NVL(tl.debitforeignamount, 0)) AS total_debits
          FROM transactionline tl
          JOIN transaction j ON j.id = tl.transaction
         WHERE j.type = 'Journal'
           AND j.status IN ('Journal:A', 'A')
         GROUP BY tl.transaction) d ON d.transaction = t.id
 ORDER BY t.createddate

SELECT TO_CHAR(p.startdate, 'YYYY-MM') AS period,
       CASE a.accttype WHEN 'Income' THEN 'Income' WHEN 'COGS' THEN 'Cost of Sales' WHEN 'Expense' THEN 'Expense'
                       WHEN 'OthIncome' THEN 'Other Income' ELSE 'Other Expense' END AS section,
       CASE a.accttype WHEN 'Income' THEN 1 WHEN 'COGS' THEN 2 WHEN 'Expense' THEN 3 WHEN 'OthIncome' THEN 4 ELSE 5 END AS section_order,
       a.acctnumber AS account_number,
       a.displaynamewithhierarchy AS account,
       SUM(-tal.amount) 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 account a ON a.id = tal.account
  JOIN accountingperiod p ON p.id = t.postingperiod
 WHERE tal.posting = 'T'
   AND a.accttype IN ('Income', 'COGS', 'Expense', 'OthIncome', 'OthExpense')
   AND p.startdate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
   AND p.startdate <= SYSDATE
 GROUP BY TO_CHAR(p.startdate, 'YYYY-MM'), a.accttype, a.acctnumber, a.displaynamewithhierarchy
 ORDER BY period, section_order, account_number, account

SELECT TO_CHAR(p.startdate, 'YYYY-MM') AS period,
       TO_CHAR(t.trandate, 'YYYY-MM-DD') AS tran_date,
       BUILTIN.DF(t.type) AS type,
       NVL(t.tranid, t.transactionnumber) AS document,
       a.displaynamewithhierarchy AS account,
       BUILTIN.DF(a.accttype) AS account_type,
       NVL(e.altname, e.entityid) AS name,
       tl.memo AS memo,
       tal.debit AS debit,
       tal.credit AS credit,
       -tal.amount 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 account a ON a.id = tal.account
  JOIN accountingperiod p ON p.id = t.postingperiod
  JOIN transactionline tl ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
  LEFT JOIN entity e ON e.id = t.entity
 WHERE tal.posting = 'T'
   AND tal.amount <> 0
   AND p.startdate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 ORDER BY t.trandate DESC, document