Write a PL/SQL procedure that assigns a reviewer to a paper in a certain round of review. The input includes a paper ID, reviewer ID, round, and due date. The procedure does ALL of the following: 1) First check whether there is a paper with the given paper ID, if not, print an error message 'Wrong paper ID' and stop;
2) Next check whether there is a reviewer with the input reviewer ID. If not, print an error message 'wrong reviewer ID' and stop.
3) Next check whether there is already a row in paper_review with given paper ID, reviewer ID, and round. If so, print a message 'Already assigned' and stop.
4) Now check whether there is a conflict of interest, i. e., whether any author of the paper and the input reviewer has the same affiliation. If a conflict exists, print a message 'X and Y have conflict of interest' where X and Y are the names of author and reviewer with the same affiliation and stop. Please note that a paper may have multiple authors with the same affiliation with the reviewer.
5) If none of the above happens, insert a row to paper_review with given paper ID, reviewer ID, round, due date, and give null values to receive_date, decision and comment. Please use a sequence to generate new prid and print out the new prid. Please include the drop sequence and create sequence command before the procedure creation code such that your procedure can be created correctly. Please also include exec procedure code to test all 5 cases (5 test case needed).