--View to check for missing ZLSM records, eg reservations or FAs checked in and having zpos records with ledgerstatus=2, but no record in ZLSM
create or replace view zlsmfix_nozlsmrec as
  select zpos.*,
         (select yres_actarrtime from yres where yres_id=zpos_yres_id) actarrtime,
         (select zfac_actvalidfrom from zfac where zfac_id=zpos_zfac_id) actvalidfrom 
  from   zpos 
  where  zpos_ledgerstatus=2 and zpos_id not in (select zlsm_zpos_id from zlsm where zlsm_toledger=2);
  
--the same for advance postings
create or replace view zlsmfix_nozlsmrec2 as
  select zpos.*,
         (select yres_actarrtime from yres where yres_id=zpos_yres_id) actarrtime,
         (select zfac_actvalidfrom from zfac where zfac_id=zpos_zfac_id) actvalidfrom 
  from   zpos 
  where  zpos_ledgerstatus=6 and zpos_id not in (select zlsm_zpos_id from zlsm where zlsm_toledger=6);

-- Checked in resrevations and FAs with postings on prearrival ledger
create or replace view zlsmfix_missingledgermove1 as
  select yres_actarrtime actarr,  zpos.*
  from   zpos, yres 
  where  zpos_ledgerstatus=1 and
         zpos_yres_id=yres_id and yres_actarrtimenull=0 and
		 zpos_orgdate is null 
  union
  select zfac_actvalidfrom actarr,  zpos.*
  from   zpos, zfac 
  where  zpos_ledgerstatus=1 and
         zpos_zfac_id=zfac_id and zfac_actvalidfromnull=0 and
		 zpos_orgdate is null;
		 
-- reservations/FAs not checked in yet, but having postings on the guest ledger
create or replace view fixls_prearr as
  select yres_actarrtime actarr,  zpos.*
  from   zpos, yres 
  where  zpos_ledgerstatus=0 and
         zpos_yres_id=yres_id and 
		 yres_actarrtimenull=1 and
		 yres_resstatus<>3 and
		 zpos_orgdate is null
  union
  select zfac_actvalidfrom actarr,  zpos.*
  from   zpos, zfac 
  where  zpos_ledgerstatus=0 and
         zpos_zfac_id=zfac_id and zfac_actvalidfromnull=1 and
		 zpos_orgdate is null;
  
  
-- reservations canceled or no show with an open balance
create or replace view fixls_cancel as
  select yres_actarrtime actarr,  zpos.*
  from   zpos, yres 
  where  zpos_yres_id=yres_id and 
		 yres_actarrtimenull=1 and
		 yres_resstatus=3 and
		 zpos_orgdate is null;
  
  
create or replace procedure fixzlsm is
  actarr date;
  
  cursor c_lsfix1 is
    select * from fixls_prearr;
  lsfix1 c_lsfix1%rowtype;
  	
  cursor c_nozlsm is 
    select * from zlsmfix_nozlsmrec;
  fix1 c_nozlsm%rowtype;
  
  cursor c_nozlsm2 is
    select * from zlsmfix_nozlsmrec2;
  fix11 c_nozlsm2%rowtype;
	
  cursor c_nozlsmnozpos is	
    select * from zlsmfix_missingledgermove1;
  fix2 c_nozlsmnozpos%rowtype;
  
begin
   update zpos set zpos_ledgerstatus=0 where zpos_ledgerstatus is null;
   
   --reservations/FAs not checked in, but having zpos records in the guest ledger
   for lsfix1 in c_lsfix1 loop
     update zpos set zpos_ledgerstatus=1 where zpos_id=lsfix1.zpos_id;
   end loop;
   
   -- no ZLSM record present
   for fix1 in c_nozlsm loop
     actarr:=nvl(fix1.actarrtime, fix1.actvalidfrom);
     insert into zlsm (zlsm_id, zlsm_zpos_id, zlsm_date, zlsm_fromledger, zlsm_toledger, zlsm_type) values
	 			 	  (seq_zlsm.nextval, fix1.zpos_id, trunc(actarr), 1, 2, 1);
   end loop;
   
   -- no ZLSM 
   for fix2 in c_nozlsmnozpos loop
     insert into zlsm (zlsm_id, zlsm_zpos_id, zlsm_date, zlsm_fromledger, zlsm_toledger, zlsm_type) values
	 			 	  (seq_zlsm.nextval, fix2.zpos_id, trunc(fix2.actarr), 1, 2, 1);
	 update zpos set zpos_ledgerstatus=2 where zpos_id=	fix2.zpos_id;	 			  
   end loop;
   
   --AINV link exists
   --   update zpos set zpos_ledgerstatus=3 where zpos_id in (select ainv_paym_zpos_id from ainv) and zpos_ledgerstatus<>3;
   
   --Advance postings
   update zpos set zpos_ledgerstatus=5 where zpos_orgdate is not null and zpos_postdate>=(select wgbs_datevalue from fideliodate) and zpos_ledgerstatus<>5;
   update zpos set zpos_ledgerstatus=6 where zpos_orgdate is not null and zpos_postdate<(select wgbs_datevalue from fideliodate) and zpos_ledgerstatus=5;

   for fix11 in c_nozlsm2 loop
     actarr:=nvl(fix11.actarrtime, fix11.actvalidfrom);
     insert into zlsm (zlsm_id, zlsm_zpos_id, zlsm_date, zlsm_fromledger, zlsm_toledger, zlsm_type) values
	 			 	  (seq_zlsm.nextval, fix11.zpos_id, trunc(fix11.zpos_postdate), 5, 6, 2);
   end loop;
   
   --Allowances
   update zall set zall_closed=1 where zall_id in (select zall_id from zall, yres where nvl(zall_closed,0)=0 and zall_yres_id=yres_id and yres_actdeptime is not null);
   
   exception
     when no_data_found then
       null;
     when others then
       null;
end fixzlsm;
/
