-- ABS-107574 / ABS-107901
--- VERTEXTRANSACTIONLOG ---
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD2 IS 'Value of system setting VERTEXTAXINSTANCECODE';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD3 IS 'Plant code';
COMMENT ON COLUMN VERTEXTRANSACTIONLOG.FLEXIBLECODEFIELD4 IS 'Depot Code if the route of the invoice has a depot, else NULL';

--- VERTEXBATCHCONTROL ---
ALTER TABLE VERTEXBATCHCONTROL ADD (PLANT_ID NUMBER NULL);

CREATE INDEX XIF1VERTEXBATCHCONTROL ON VERTEXBATCHCONTROL (PLANT_ID ASC);

ALTER TABLE VERTEXBATCHCONTROL ADD CONSTRAINT FK_VERTEXBATCHCONTROL_PLANT FOREIGN KEY (PLANT_ID)
REFERENCES PLANT (PLANT_ID) ON DELETE SET NULL;

update vertexbatchcontrol vbc
   set vbc.plant_id =
       (select p.plant_id
          from plant p
         where p.code = (select distinct (vtl.flexiblecodefield3)
                           from vertextransactionlog vtl
                          where vtl.batchid = vbc.batchid));
commit;