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');
---------------------------------------------------------------------------