-- ABS-79158 / ABS-85146

-- TO ADD THE EXISTING VALUES IN FIELDSPERDIALOG ALSO IN ACCESSRIGHTFIELDLISTS
create or replace procedure insertintoaccessrightfieldlist is
begin
merge into ACCESSRIGHTFIELDLISTS ALIST
using (select FD.FIELDSPERDIALOG_ID, AL.ACCESSRIGHTSLIST_ID
         from FIELDSPERDIALOG FD, ACCESSRIGHTSLIST AL
        where FD.DIALOG_ID = AL.DIALOG_ID
          and FD.FIELDSPERDIALOG_ID not in
              (select AFL.FIELDSPERDIALOG_ID
                 from ACCESSRIGHTFIELDLISTS AFL
                where AL.ACCESSRIGHTSLIST_ID = AFL.ACCESSRIGHTSLIST_ID)) LL
on (ALIST.FIELDSPERDIALOG_ID = LL.FIELDSPERDIALOG_ID and ALIST.ACCESSRIGHTSLIST_ID = LL.ACCESSRIGHTSLIST_ID)
when not matched then insert(ALIST.FIELDSPERDIALOG_ID, ALIST.ACCESSRIGHTSLIST_ID, ALIST.ACCESSRIGHT, ALIST.SYSTEMUSER_ID, ALIST.TIMESTAMP) values(LL.FIELDSPERDIALOG_ID, LL.ACCESSRIGHTSLIST_ID, 0, 1, sysdate);
commit;
end insertintoaccessrightfieldlist;
/

EXECUTE insertintoaccessrightfieldlist;
DROP PROCEDURE insertintoaccessrightfieldlist;

-- TRIGGER TO ADD NEW VALUES INSERTED IN FIELDSPERDIALOG IN ACCESSRIGHTFIELDLISTS
CREATE OR REPLACE TRIGGER AFTER_INSERT_FIELDSPERDIALOG
AFTER INSERT ON FIELDSPERDIALOG
FOR EACH ROW
  BEGIN
    MERGE INTO ACCESSRIGHTFIELDLISTS ALIST
USING (SELECT :new.fieldsperdialog_id, AL.ACCESSRIGHTSLIST_ID
         FROM  ACCESSRIGHTSLIST AL
        WHERE :new.dialog_id = AL.DIALOG_ID
          AND :new.fieldsperdialog_id NOT IN
              (SELECT AFL.FIELDSPERDIALOG_ID
                 FROM ACCESSRIGHTFIELDLISTS AFL
                WHERE AL.ACCESSRIGHTSLIST_ID = AFL.ACCESSRIGHTSLIST_ID)) LL
ON (ALIST.FIELDSPERDIALOG_ID = :new.fieldsperdialog_id AND ALIST.ACCESSRIGHTSLIST_ID = LL.ACCESSRIGHTSLIST_ID)
WHEN NOT MATCHED THEN INSERT(ALIST.FIELDSPERDIALOG_ID, ALIST.ACCESSRIGHTSLIST_ID, ALIST.ACCESSRIGHT, ALIST.SYSTEMUSER_ID, ALIST.TIMESTAMP) VALUES(:new.fieldsperdialog_id, LL.ACCESSRIGHTSLIST_ID, 0, 1, SYSDATE);
END AFTER_INSERT_FIELDSPERDIALOG;
/