create sequence provider_station_seq;

create table provider_station (
   provider_station_id number  not null,
   ps_version          number  not null,
   ps_provider         number  not null,
   ps_station          char(3) not null,
   ps_active           char(1) not null,
   constraint fk_ps_provider foreign key (ps_provider) references provider on delete cascade,
   constraint fk_ps_station  foreign key (ps_station)  references station  on delete cascade
);

create unique index pk_provider_station
   on provider_station (provider_station_id);

create unique index idxu_provider_station
   on provider_station (ps_provider, ps_station);

alter table provider_station add (
   constraint pk_provider_station primary key (provider_station_id)
      using index pk_provider_station,
   constraint uq_provider_station unique (ps_provider, ps_station)
      using index idxu_provider_station
);

create table provider_station_jobtype (
   psj_type     char(3) not null,
   psj_category char(1) not null,
   psj_ps_id    number  not null,
   constraint chk_psj_category check (psj_category in ('A', 'M', 'R')),
   constraint fk_psj_psid foreign key (psj_ps_id) references provider_station
);

comment on column provider_station_jobtype.psj_category
   is 'A=All Categories, M=Mainline, R=Regional';

create unique index idxu_psj_type_psid
   on provider_station_jobtype (psj_type, psj_ps_id);

alter table provider_station_jobtype
   add constraint uq_psj_type_psid unique (psj_type, psj_ps_id)
      using index idxu_psj_type_psid;

---------------------------------------------------------------------------


create or replace force view reconcile_turn as
  select turn_summary_id reconcile_turn_id,
         ts_station rt_station,
         ts_carrier_category rt_carrier_category,
         ts_nose rt_tail,
         ts_ac_type rt_fleet,
         ts_in_flight rt_in_id,
         ts_in_flt_number rt_in_number,
         ts_in_arr_time rt_in_arr_time,
         ts_in_origin rt_in_origin,
         ts_out_flight rt_out_id,
         ts_out_flt_number rt_out_number,
         ts_out_dep_time rt_out_dep_time,
         ts_out_destination rt_out_destination
  from turn_summary;

---------------------------------------------------------------------------

create or replace force view reconcile_job as
  select
    mvj.job_id reconcile_job_id,
    case
    when             mvj.job_type in ('PRV', 'SEC', 'TUP')
      then          mvj.job_type
    else             'JOB'
    end                 rj_type,
    turn_summary_id     rj_turn,
    mvj.job_type        rj_job_type,
    mvj.status          rj_state,
    mvj.job_date        rj_date,
    mvj.create_time     rj_create_time,
    mvj.adhoc           rj_adhoc,
    mvj.adhoc_comment   rj_adhoc_comment,
    tsj_provider        rj_provider,
    mvj.security_inspection       rj_inspection,
    invoicer_rate_id    rj_invoicer_rate_id,
    case
    when invoice_import_row_matched_id is null then 'N'
    else 'Y'
    end                 rj_matched,
    reconciliation_acceptance (jr_acceptance,
                               status,
                               CASE adhoc WHEN 'Y' THEN 'A' END)
      acceptance
  from mv_job_details mvj
    left join turn_summary_job tsj on mvj.job_id = tsj.turn_summary_job_id
    left join job_reconcile jp on turn_summary_job_id = jp.job_reconcile_id
    left join
    (select *
     from invoice_import_row_matched iirm,
       invoice_import_row iir,
       v_latest_invoice_import vlii
     where iirm.invoice_import_row_matched_id = iir.invoice_import_row_id
           and iir.iir_invoice_import = vlii.invoice_import_id) last_invoice on mvj.job_id = last_invoice.iirm_job_id
    left join invoicer_rate              on mvj.station                   = ir_station
                                            and mvj.job_type      = ir_job_type
                                            and(mvj.provider_id  = ir_provider or mvj.provider_id is null)
                                            and mvj.job_date  >= ir_start_date
                                            and (mvj.job_date <= ir_end_date or ir_end_date is null)
                                            and (
                                              ir_category = 'ALL' or
                                              ir_category = nvl(
                                                  case mvj.adhoc
                                                  when 'Y' then 'A'
                                                  else 'SCH'
                                                  end,
                                                  'SCH'
                                              )
                                            )
                                            and mvj.fleet in
                                                (select ires_equipment_series
                                                 from invoicer_rate_equip_series
                                                 where invoicer_rate_id = ires_invoicer_rate)
   where mvj.job_type not in ('CAM');

---------------------------------------------------------------------------