Financial Transaction

select
    ft.practice_id
  , pr.name as practice_name
  , date_trunc('month', ft.created_timestamp) as transaction_month
  , ft.type
  , count(*) as transactions
  , sum(ft.amount) as total_amount
from financial_transaction ft
  join practice pr on pr.id = ft.practice_id
where ft.is_deleted = false
group by ft.practice_id, pr.name, transaction_month, ft.type
order by transaction_month desc, ft.practice_id;

select
    ft.id
  , ft.type
  , ft.code
  , ft.description
  , ft.amount
  , ft.balance
  , ft.responsibility
  , ft.created_timestamp
from financial_transaction ft
where ft.is_deleted = false
  and ft.claim_id = 1234567890  -- Replace with actual claim ID
order by ft.created_timestamp;

select
    ft.id as financial_transaction_id
  , ft.payment_id
  , p.pay_type
  , p.pay_method
  , ft.charge_id
  , ft.claim_id
  , ft.amount
  , ft.balance
from financial_transaction ft
  join payment p on p.id = ft.payment_id
where ft.is_deleted = false
  and p.is_deleted = false
order by ft.created_timestamp desc;