SQL & PL/SQL

CREATE TABLE   target_device_hist
(
  DEVICE_ID             VARCHAR2(250 BYTE),
  IP_ADDRESS            VARCHAR2(250 BYTE),
  ZIPCODE               VARCHAR2(50 BYTE),
  DMA                   VARCHAR2(100 BYTE),
  OPTED_OUT             NUMBER(2),
  ACTIVE_FLG   NUMBER,
  EFFECTIVE_START_DATE  DATE,
  EFFECTIVE_END_DATE  DATE,
  CREATED_ON            DATE                    DEFAULT SYSDATE               NOT NULL
);
--insert into target
-- truncate table target_device_hist;
Insert into   target_device_hist  
   ( DEVICE_ID, IP_ADDRESS, ZIPCODE, DMA,   
    OPTED_OUT,ACTIVE_FLG   , EFFECTIVE_START_DATE, CREATED_ON)  
 Values  
   ( '|000219a9e85b68cb6', '867c6f6b0a40', '30152', 'SACRAMNTO',   
    1,1, TO_DATE('8/19/2020 5:49:22 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('8/19/2020 5:49:16 PM', 'MM/DD/YYYY HH:MI:SS AM'));  
Insert into  target_device_hist  
   (DEVICE_ID, IP_ADDRESS, ZIPCODE, DMA,   
    OPTED_OUT,ACTIVE_FLG, EFFECTIVE_START_DATE, CREATED_ON)  
 Values  
   ( '99037334', 'cdscdc', '45362', 'ORLANDO',   
    0,1, TO_DATE('8/19/2020 5:50:14 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('8/19/2020 5:49:36 PM', 'MM/DD/YYYY HH:MI:SS AM'));  
COMMIT;

CREATE TABLE   STG_DEVICE_HIST
(
  DEVICE_ID             VARCHAR2(250 BYTE),
  IP_ADDRESS            VARCHAR2(250 BYTE),
  ZIPCODE               VARCHAR2(50 BYTE),
  DMA                   VARCHAR2(100 BYTE),
  OPTED_OUT             NUMBER(2),
  EFFECTIVE_START_DATE  DATE,
  CREATED_ON            DATE                    DEFAULT SYSDATE               NOT NULL
);
Insert into stg_device_hist
   (   DEVICE_ID, IP_ADDRESS, ZIPCODE, DMA, 
    OPTED_OUT, EFFECTIVE_START_DATE, CREATED_ON)
 Values
   (  '99037334', 'cdscdc', '45362', 'JAPAN', 
    0, TO_DATE('8/19/2020 5:50:14 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('8/19/2020 5:49:36 PM', 'MM/DD/YYYY HH:MI:SS AM'));
Insert into stg_device_hist
   (  DEVICE_ID, IP_ADDRESS, ZIPCODE, DMA, 
    OPTED_OUT, EFFECTIVE_START_DATE, CREATED_ON)
 Values
   (  '45ggrg', 'efqwe4t', '67432', 'ABUJA', 
    1, TO_DATE('8/18/2020', 'MM/DD/YYYY'), TO_DATE('8/19/2020 6:28:02 PM', 'MM/DD/YYYY HH:MI:SS AM')   );
commit;
    
  Insert into stg_device_hist
   (  DEVICE_ID, IP_ADDRESS, ZIPCODE, DMA, 
    OPTED_OUT, EFFECTIVE_START_DATE, CREATED_ON )
 Values
   ( '|000219a9e85b68cb6', 'ywtw53rw5', null, 'SACRAMNTO', 
    1, TO_DATE('8/21/2020 5:49:22 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_DATE('8/21/2020 5:49:16 PM', 'MM/DD/YYYY HH:MI:SS AM'));
  commit; 

CREATE OR REPLACE procedure  Device_historical  
AS
MERGE INTO target_device_hist tar
USING ( SELECT *
                     FROM(SELECT /*+ parallel (8) */ DISTINCT  device_id,
                                 ip_address,
                                 zipcode,
                                 dma,
                                 opted_out,
                                 trunc(effective_start_date) effective_start_date
                                , row_number  () over (partition by device_id , effective_start_date 
                                                       order by created_on desc) device_rank
                           FROM stg_device_hist
                           ) 
                          WHERE device_rank = 1) stg
      ON   ( (tar.device_id = stg.device_id)
    AND (
           tar.dma <> stg.dma 
OR tar.zipcode <> stg.zipcode  
OR tar.ip_address <> stg.ip_address
OR tar.opted_out <> stg.opted_out)  
-- or stg.zipcode is not null
  )
-- AND  tar.active_flag = 1
--  AND (tar.effective_end_date IS NULL)
WHEN  MATCHED THEN UPDATE
    SET tar.effective_end_date =  stg.effective_start_date
, tar.ACTIVE_FLG = 0;
rollback;  
 ------------------------------------------------------------------------------------------------
 MERGE  INTO target_device_hist tar
USING ( SELECT *
                     FROM(SELECT/*+ parallel (8) */ DISTINCT device_id,
                                 ip_address,
                                 zipcode,
                                 dma,
                                 opted_out,
                                 trunc(effective_start_date) effective_start_date
                                , row_number  () over (partition by device_id , effective_start_date 
                                                       order by created_on desc) device_rank
                           FROM stg_device_hist) 
                          WHERE device_rank = 1) stg
      ON ((tar.device_id = stg.device_id)
   AND (tar.dma = stg.dma
OR tar.zipcode = stg.zipcode
OR tar.ip_address = stg.ip_address
OR tar.opted_out = stg.opted_out)) 
                     
WHEN NOT MATCHED THEN
 INSERT
     (tar.device_id,
     tar.ip_address,
     tar.zipcode,
     tar.dma,
     tar.opted_out,
     tar.effective_start_date,
     tar.active_flg)
     Values (  
     stg.device_id,
     stg.ip_address,
     stg.zipcode ,
     stg.dma,
     stg.opted_out,
     stg.effective_start_date,
     1) ;
  
END;