-- ABS-33818
--------   WORKORDERLINE   -------------
ALTER TABLE WORKORDERLINE ADD (APPROVALSTATUS NUMBER DEFAULT 0 NOT NULL
				CONSTRAINT APPROVALSTATUS_VALID1_1 CHECK (APPROVALSTATUS IN (0,1,2)));

COMMENT ON COLUMN WORKORDERLINE.APPROVALSTATUS IS 'Indicates if the work order line has been approved. Possible values:
0 =  Not applicable / not approved
1 = Approved via order
2 = Approved via manual selection';

create or replace procedure update_approvalstatus as
begin
  for x in (select wo.workorder_id
              from workorder wo, status s
             where wo.status_id = s.status_id
               and s.statustype = 'WO'
               and s.code <> '05') loop
    begin
      update workorderline wol
         set wol.approvalstatus = 1
       where wol.workorder_id = x.workorder_id;
    exception
      when others then
        rollback;
    end;
    commit;
  end loop;
end update_approvalstatus;
/

execute update_approvalstatus;
drop procedure update_approvalstatus;


