-- 创建临时表

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 't_er_vehiclecheck_upgback')
 CREATE TABLE t_er_vehiclecheck_upgback (
FPKID BIGINT DEFAULT 0 NOT NULL,
fsbillid BIGINT DEFAULT 0 NOT NULL,
fbillstatus VARCHAR(10) DEFAULT ' ' NOT NULL
);

EXEC P_ALTERPK 'PK_t_er_vehiclech_upgback', 't_er_vehiclecheck_upgback', 'FPKID', '1';
truncate table t_er_vehiclecheck_upgback;

insert into t_er_vehiclecheck_upgback (FPKID, fsbillid, fbillstatus)
select a.FPKID,  a.fsbillid, c.fbillstatus     from t_er_pubreimbill c
join t_er_pubreimexpdet b on c.fid = b.fid
join  T_ER_PUBREIMEXPDET_LK a   on b.fdetailid = a.fdetailid;


update  t_er_vehiclecheckingbill set   freimbursestatus = 'C'     where fid in (
select fsbillid    from t_er_vehiclecheck_upgback where fbillstatus  in ('G', 'I')
);

update  t_er_vehiclecheckingbill set   freimbursestatus = 'B'     where fid in (
select fsbillid    from t_er_vehiclecheck_upgback where fbillstatus  in  ('A', 'B', 'C','D', 'E', 'F')
);

truncate table t_er_vehiclecheck_upgback;

--
insert into t_er_vehiclecheck_upgback (FPKID, fsbillid, fbillstatus)
select a.FPKID,  a.fsbillid, c.fbillstatus  from t_er_dailyreimbursebill c
join t_er_expensedetail b  on c.fid = b.fid
join t_er_expensedetail_lk a on b.fdetailid = a.fdetailid ;


update  t_er_vehiclecheckingbill set   freimbursestatus = 'C'     where fid in (
select fsbillid    from  t_er_vehiclecheck_upgback where  fbillstatus    in  ('G', 'I')
);

update  t_er_vehiclecheckingbill set   freimbursestatus = 'B'     where fid in (
select fsbillid    from  t_er_vehiclecheck_upgback  where   fbillstatus     in ('A', 'B', 'C','D', 'E', 'F')
);


update t_er_vehiclebill  set freimbursestatus = 'B' where fordernum in (select fordernum  from t_er_vehiclecheckingbill where freimbursestatus = 'B') ;
update t_er_vehiclebill  set freimbursestatus = 'C' where fordernum in (select fordernum  from t_er_vehiclecheckingbill where freimbursestatus = 'C') ;


-- 酒店结算单


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 't_er_hotelchec_upgback')
 CREATE TABLE t_er_hotelchec_upgback (
FPKID BIGINT DEFAULT 0 NOT NULL,
fordernum NVARCHAR(100) DEFAULT ' ' NOT NULL,
forderformid VARCHAR(50) DEFAULT ' ' NOT null,
fbillstatus VARCHAR(10) DEFAULT ' ' NOT NULL
);

EXEC P_ALTERPK 'PK_t_er_hotelchec_upgback', 't_er_hotelchec_upgback', 'FPKID', '1';
truncate table t_er_hotelchec_upgback;


insert into t_er_hotelchec_upgback (FPKID, fordernum,forderformid, fbillstatus)
select  a.fdetailid,  a.fordernum , a.forderformid, c.fbillstatus  from t_er_reimbursebill c
join T_ER_ReimburseTripEntry b on c.fid = b.fid
join T_ER_ReimburseEntry a   on  (a.fordernum  != ' '   and  b.fentryid = a.fentryid  )  ;



update t_er_hotelcheckingbill  set freimbursestatus  = 'C' where fordernum in (
select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_hotelbill'  and   fbillstatus    in  ('G', 'I'));

update t_er_hotelcheckingbill  set freimbursestatus  = 'B' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_hotelbill'   and
 fbillstatus in ('A', 'B', 'C','D', 'E', 'F'));



update t_er_hotelbill  set freimbursestatus  = 'C' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_hotelbill'  and fbillstatus    in  ('G', 'I'));

update t_er_hotelbill  set freimbursestatus  = 'B' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_hotelbill'  and fbillstatus in ('A', 'B', 'C','D', 'E', 'F'));

-----

update t_er_trainbill  set freimbursestatus  = 'C' where fordernum in (

 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_trainbill'
 and fbillstatus    in  ('G', 'I'));

update t_er_trainbill  set freimbursestatus  = 'B' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_trainbill'
 and  fbillstatus in ('A', 'B', 'C','D', 'E', 'F'));


update t_er_traincheckingbill  set freimbursestatus  = 'C' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_trainbill'
 and  fbillstatus    in  ('G', 'I'));

update t_er_traincheckingbill  set freimbursestatus  = 'B' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_trainbill'
 and  fbillstatus in ('A', 'B', 'C','D', 'E', 'F'));


update t_er_vehiclebill  set freimbursestatus  = 'C' where fordernum in (

 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_vehiclebill'
 and fbillstatus    in  ('G', 'I'));

update t_er_vehiclebill  set freimbursestatus  = 'B' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_vehiclebill'
 and  fbillstatus in ('A', 'B', 'C','D', 'E', 'F'));



update t_er_vehiclecheckingbill  set freimbursestatus  = 'C' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_vehiclebill'
 and fbillstatus    in  ('G', 'I'));

update t_er_vehiclecheckingbill  set freimbursestatus  = 'B' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_vehiclebill'
 and  fbillstatus in ('A', 'B', 'C','D', 'E', 'F'));



update t_er_planebill  set freimbursestatus  = 'C' where fordernum in (

 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_planebill'
and fbillstatus    in  ('G', 'I'));

update t_er_planebill  set freimbursestatus  = 'B' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_planebill'
and fbillstatus in ('A', 'B', 'C','D', 'E', 'F'));


update t_er_planecheckingbill  set freimbursestatus  = 'C' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_planebill'
and fbillstatus    in  ('G', 'I'));

update t_er_planecheckingbill  set freimbursestatus  = 'B' where fordernum in (
 select   fordernum  from t_er_hotelchec_upgback where forderformid = 'er_planebill'
and  fbillstatus in ('A', 'B', 'C','D', 'E', 'F'));







