example_bill_from _CCR.sql

/*
  Query CCR database
  
  Get bills on cases for AGFS scheme 10 with a caclulation date > 2/1/2015
  */

select 
    c.id,
    s.psty_highest_level,
    b.bity_bill_type,
    b.bist_bill_sub_type, 
    b.calculation_date,
    bs.scenario_description,
    b.bisc_bill_scenario_id,
    b.ofty_offence_type, 
    b.trial_length,
    b.evidence_pages,
    b.no_defendants,
    b.calc_fee_exc_vat,
    b.calc_fee_vat
from bills b 
inner join cases c on (b.case_id = c.id)
inner join suppliers s on ( c.supp_account_code = s.account_code)
inner join bill_scenarios bs on (B.BISC_BILL_SCENARIO_ID = bs.id)
where b.calculation_date >TO_DATE('01-01-2015', 'DD-MM-YYYY')
    and b.fsth_fee_structure_id = 10;