select xt.create_view('xt.itemsiteinfo', $$
   select i.*, item_number, item_upccode,
     case
       when i.itemsite_location_id != -1 or length(i.itemsite_location) > 0 then true
       else false
     end as "use_default_loc",
     round(itemcost(i.itemsite_id), 6) as "unit_cost",
     case when i.itemsite_supply_itemsite_id is null then false else true end as planned_transfers,
     s.itemsite_warehous_id as supply_warehous_id
   from itemsite i
     join item on item_id=i.itemsite_item_id
     left join itemsite s on s.itemsite_id = i.itemsite_supply_itemsite_id;
$$, false);

create or replace rule "_INSERT" as on insert to xt.itemsiteinfo do instead

insert into itemsite (
  itemsite_id,
  itemsite_item_id,
  itemsite_warehous_id,
  itemsite_qtyonhand,
  itemsite_reorderlevel,
  itemsite_ordertoqty,
  itemsite_cyclecountfreq,
  itemsite_datelastcount,
  itemsite_datelastused,
  itemsite_loccntrl,
  itemsite_safetystock,
  itemsite_minordqty,
  itemsite_multordqty,
  itemsite_leadtime,
  itemsite_abcclass,
  itemsite_issuemethod,
  itemsite_controlmethod,
  itemsite_active,
  itemsite_plancode_id,
  itemsite_costcat_id,
  itemsite_eventfence,
  itemsite_sold,
  itemsite_stocked,
  itemsite_freeze,
  itemsite_location_id,
  itemsite_useparams,
  itemsite_useparamsmanual,
  itemsite_soldranking,
  itemsite_createpr,
  itemsite_location,
  itemsite_location_comments,
  itemsite_notes,
  itemsite_perishable,
  itemsite_autoabcclass,
  itemsite_ordergroup,
  itemsite_disallowblankwip,
  itemsite_maxordqty,
  itemsite_mps_timefence,
  itemsite_createwo,
  itemsite_warrpurc,
  itemsite_autoreg,
  itemsite_costmethod,
  itemsite_value,
  itemsite_ordergroup_first,
  itemsite_supply_itemsite_id,
  itemsite_planning_type,
  itemsite_wosupply,
  itemsite_posupply,
  itemsite_lsseq_id,
  itemsite_cosdefault,
  itemsite_createsopr,
  itemsite_createsopo,
  itemsite_dropship,
  itemsite_recvlocation_id,
  itemsite_issuelocation_id,
  itemsite_location_dist,
  itemsite_recvlocation_dist,
  itemsite_issuelocation_dist,
  obj_uuid
) values (
  coalesce(new.itemsite_id, nextval('itemsite_itemsite_id_seq')),
  new.itemsite_item_id,
  new.itemsite_warehous_id,
  0,
  coalesce(new.itemsite_reorderlevel, 0),
  coalesce(new.itemsite_ordertoqty, 0),
  coalesce(new.itemsite_cyclecountfreq, 0),
  null,
  startoftime(),
  coalesce(new.itemsite_loccntrl, false),
  coalesce(new.itemsite_safetystock, 0),
  coalesce(new.itemsite_minordqty, 0),
  coalesce(new.itemsite_multordqty, 0),
  coalesce(new.itemsite_leadtime, 0),
  coalesce(new.itemsite_abcclass, 'A'),
  new.itemsite_issuemethod,
  coalesce(new.itemsite_controlmethod, 'N'),
  new.itemsite_active,
  new.itemsite_plancode_id,
  new.itemsite_costcat_id,
  coalesce(new.itemsite_eventfence, 10),
  coalesce(new.itemsite_sold, false),
  coalesce(new.itemsite_stocked, false),
  false,
  coalesce(new.itemsite_location_id, -1),
  coalesce(new.itemsite_useparams, false),
  coalesce(new.itemsite_useparamsmanual, false),
  coalesce(new.itemsite_soldranking, 1),
  coalesce(new.itemsite_createpr, false),
  new.itemsite_location,
  new.itemsite_location_comments,
  new.itemsite_notes,
  coalesce(new.itemsite_perishable, false),
  coalesce(new.itemsite_autoabcclass, false),
  coalesce(new.itemsite_ordergroup, 1),
  coalesce(new.itemsite_disallowblankwip, false),
  coalesce(new.itemsite_maxordqty, 0),
  coalesce(new.itemsite_mps_timefence, 0),
  coalesce(new.itemsite_createwo, false),
  coalesce(new.itemsite_warrpurc, false),
  coalesce(new.itemsite_autoreg, false),
  coalesce(new.itemsite_costmethod, 'N'),
  coalesce(new.itemsite_value, 0),
  coalesce(new.itemsite_ordergroup_first, false),
  (select itemsite_id
    from itemsite
    where itemsite_item_id = new.itemsite_item_id
      and itemsite_warehous_id = new.supply_warehous_id),
  coalesce(new.itemsite_planning_type, 'N'),
  coalesce(new.itemsite_wosupply, false),
  coalesce(new.itemsite_posupply, false),
  new.itemsite_lsseq_id,
  new.itemsite_cosdefault,
  coalesce(new.itemsite_createsopr, false),
  coalesce(new.itemsite_createsopo,false),
  coalesce(new.itemsite_dropship, false),
  coalesce(new.itemsite_recvlocation_id, -1),
  coalesce(new.itemsite_issuelocation_id, -1),
  coalesce(new.itemsite_location_dist, false),
  coalesce(new.itemsite_recvlocation_dist, false),
  coalesce(new.itemsite_issuelocation_dist, false),
  coalesce(new.obj_uuid, xt.uuid_generate_v4())
);

create or replace rule "_UPDATE" as on update to xt.itemsiteinfo do instead

update itemsite set
  itemsite_reorderlevel=new.itemsite_reorderlevel,
  itemsite_ordertoqty=new.itemsite_ordertoqty,
  itemsite_cyclecountfreq=new.itemsite_cyclecountfreq,
  itemsite_loccntrl=new.itemsite_loccntrl,
  itemsite_safetystock=new.itemsite_safetystock,
  itemsite_minordqty=new.itemsite_minordqty,
  itemsite_multordqty=new.itemsite_multordqty,
  itemsite_leadtime=new.itemsite_leadtime,
  itemsite_abcclass=new.itemsite_abcclass,
  itemsite_issuemethod=new.itemsite_issuemethod,
  itemsite_controlmethod=new.itemsite_controlmethod,
  itemsite_active=new.itemsite_active,
  itemsite_plancode_id=new.itemsite_plancode_id,
  itemsite_costcat_id=new.itemsite_costcat_id,
  itemsite_eventfence=new.itemsite_eventfence,
  itemsite_sold=new.itemsite_sold,
  itemsite_stocked=new.itemsite_stocked,
  itemsite_location_id=new.itemsite_location_id,
  itemsite_useparams=new.itemsite_useparams,
  itemsite_useparamsmanual=new.itemsite_useparamsmanual,
  itemsite_soldranking=new.itemsite_soldranking,
  itemsite_createpr=new.itemsite_createpr,
  itemsite_location=new.itemsite_location,
  itemsite_location_comments=new.itemsite_location_comments,
  itemsite_notes=new.itemsite_notes,
  itemsite_perishable=new.itemsite_perishable,
  itemsite_autoabcclass=new.itemsite_autoabcclass,
  itemsite_ordergroup=new.itemsite_ordergroup,
  itemsite_disallowblankwip=new.itemsite_disallowblankwip,
  itemsite_maxordqty=new.itemsite_maxordqty,
  itemsite_mps_timefence=new.itemsite_mps_timefence,
  itemsite_createwo=new.itemsite_createwo,
  itemsite_warrpurc=new.itemsite_warrpurc,
  itemsite_autoreg=new.itemsite_autoreg,
  itemsite_costmethod=new.itemsite_costmethod,
  itemsite_ordergroup_first=new.itemsite_ordergroup_first,
  itemsite_supply_itemsite_id=(
    select itemsite_id
    from itemsite
    where itemsite_item_id = old.itemsite_item_id
      and itemsite_warehous_id = new.supply_warehous_id),
  itemsite_planning_type=new.itemsite_planning_type,
  itemsite_wosupply=new.itemsite_wosupply,
  itemsite_posupply=new.itemsite_posupply,
  itemsite_lsseq_id=new.itemsite_lsseq_id,
  itemsite_cosdefault=new.itemsite_cosdefault,
  itemsite_createsopr=new.itemsite_createsopr,
  itemsite_createsopo=new.itemsite_createsopo,
  itemsite_dropship=new.itemsite_dropship,
  itemsite_recvlocation_id=new.itemsite_recvlocation_id,
  itemsite_issuelocation_id=new.itemsite_issuelocation_id,
  itemsite_location_dist=new.itemsite_location_dist,
  itemsite_recvlocation_dist=new.itemsite_recvlocation_dist,
  itemsite_issuelocation_dist=new.itemsite_issuelocation_dist,
  obj_uuid=new.obj_uuid
where itemsite_id = old.itemsite_id;

create or replace rule "_DELETE" as on delete to xt.itemsiteinfo do instead

select deleteitemsite(old.itemsite_id);
