SQL & PL/SQL

-- Create table statements

CREATE TABLE SOURCE
(
EMP_ID NUMBER NOT NULL,
DEPT_ID NUMBER NOT NULL
)

insert into SOURCE values(1, 100);
insert into SOURCE values(2, 761);
insert into SOURCE values(3, 101);
insert into SOURCE values(4, 230);
insert into SOURCE values(5, 350);
insert into SOURCE values(6, 600);

CREATE TABLE TARGET
(
EMP_ID NUMBER NOT NULL,
DEPT_ID NUMBER NOT NULL
)

insert into TARGET values(1, 100);
insert into TARGET values(2, 761);
insert into TARGET values(3, 350);
insert into TARGET values(3, 900);
insert into TARGET values(4, 600);
insert into TARGET values(4, 800);
insert into TARGET values(7, 101);

CREATE TABLE DEPT_HIER
(
DEPT_ID NUMBER NOT NULL,
DEPT_ID_PAR NUMBER NOT NULL
)

insert into DEPT_HIER values(100, 5);
insert into DEPT_HIER values(761, 100);
insert into DEPT_HIER values(101, 100);
insert into DEPT_HIER values(230, 101);
insert into DEPT_HIER values(350, 761);
insert into DEPT_HIER values(600, 5);
insert into DEPT_HIER values(900, 1000);
insert into DEPT_HIER values(800, 2222);

I have a requirement to update table TARGET based on table SOURCE. Below are the conditions for performing the update

1. IF EMP_ID from SOURCE exists on the TARGET table and the SOURCE.DEPT_ID = TARGET.DEPT_ID no update is required.

For eg. EMP_ID/DEPT_ID from source ((1,100), (2/761)) exists in TARGET, so no update is required

2. If the EMP_ID from SOURCE table does not exist on the TARGET table, then insert record into the TARGET table with EMP_ID and targeted DEPT_ID from the SOURCE table.

For eg. EMP_ID/DEPT_ID from source (5,350) does not exists in TARGET table, so it should be inserted.

3. If the EMP_ID from SOURCE table exists on the TARGET table and the SOURCE.DEPT_ID <> TARGET.DEPT_ID , then insert record into the TARGET table with EMP_ID and targeted DEPT_ID from the SOURCE table
AND
If the EMP_ID has a different record in the TARGET table whose TARGET.DEPT_ID is under top level dept = 5, and the TARGET.DEPT_ID is not equal to the targeted DEPT_ID from SOURCE, delete the record from the TARGET table where the TARGET.DEPT_ID is not equal to the targeted DEPT_ID from the SOURCE table.

For eg. Record from SOURCE table for the EMP_ID -

EMP_ID DEPT_ID
3 101

Record from TARGET table for the EMP_ID -

EMP_ID DEPT_ID
3 350
3 900

The DEPT_ID 101 has to be inserted into the TARGET table and then DEPT_IDs other that 101 that roll up to '5'(refer DEPT_HIER table) for the EMP_ID '3' has to be deleted from the TARGET table.

So the output should be as below for the EMP_ID '3'

EMP_ID DEPT_ID
3 101
3 900

NOTE: DEPT_ID '900' does not roll upto DEPT_ID '5' in DEPT_HIER table, so it should not be removed from TARGET table.

Expected Output for the above data after running update....

EMP_ID DEPT_ID
1 100
2 761
3 101
3 900
4 230
4 800
5 350
6 600
7 101

I need a pl/sql script to perform this update process. Let me know if any questions.