execute immediate  ' UPDATE ODS_STAGE.CMC_BLSB_SB_DETAIL a ' ||
			  ' SET (a.LAST_MOD_DTTM,a.LAST_MOD_USER_ID)=' ||
			  ' ( SELECT b.LAST_MOD_DTTM,b.LAST_MOD_USER_ID from ODS_STAGE.STOO_CMC_BLSB_SB_DETAIL b WHERE ' ||
			    ' a.BLEI_CK  =  b.BLEI_CK ' || 
  ' and TRUNC(a.BLBL_DUE_DT)  =  TRUNC(b.BLBL_DUE_DT) ' || 
  ' and a.CSPI_ID  =  b.CSPI_ID ' || 
  ' and a.PDPD_ID  =  b.PDPD_ID ' || 
  ' and a.PDBL_ID  =  b.PDBL_ID ' || 
  ' and TRUNC(a.BLSB_COV_DUE_DT)  =  TRUNC(b.BLSB_COV_DUE_DT) ' || 
  ' and TRUNC(a.BLSB_COV_FROM_DT)  =  TRUNC(b.BLSB_COV_FROM_DT) ' || 
  ' and a.BLSB_PREM_TYPE  =  b.BLSB_PREM_TYPE ' || 
  ' and a.SBSB_CK  =  b.SBSB_CK ' || 
  ' and a.MEME_CK  =  b.MEME_CK ' || 
  ' and TRUNC(a.BLSB_CREATE_DTM)  =  TRUNC(b.BLSB_CREATE_DTM) ' || 

set serveroutput on;
DECLARE
   starting_time                                TIMESTAMP;
   ending_time                                  TIMESTAMP;
   log_date                                     DATE;
   sequence_number                              INTEGER;
   table_name                                   VARCHAR2(40);
   table_desc                                   VARCHAR2(40);
   status                                       VARCHAR2(1);

BEGIN
       DBMS_OUTPUT.put_line('Started creating table CMC_BLSB_SB_DETAIL');
       starting_time := SYSTIMESTAMP;
       ending_time := SYSTIMESTAMP;
       log_date      := SYSDATE;
       DBMS_OUTPUT.put_line('The starting time is: ' || starting_time);
       table_name := 'CMC_BLSB_SB_DETAIL';
       table_desc := 'UPDATE LASTMOD TABLES';
       status     := 'P';  

       execute immediate  'SELECT REP_LOG_SEQ.NEXTVAL FROM DUAL' into sequence_number;
       
       
       commit;

       execute immediate  ' INSERT INTO ODS_STAGE.REP_LOG ' || 
                          ' (SEQ_NO,TABLE_NAME,SCRIPT_DESC,START_DTM,END_DTM,LOG_DT,STATUS) VALUES ' ||  
                          ' (:1, :2, :3, :4, :5, :6, :7)'
                          using sequence_number, table_name, table_desc, starting_time, ending_time, log_date, status;
       commit;
       
       execute immediate  ' UPDATE ODS_STAGE.CMC_BLSB_SB_DETAIL a ' ||
			  ' SET (a.LAST_MOD_DTTM,a.LAST_MOD_USER_ID)=' ||
			  ' ( SELECT b.LAST_MOD_DTTM,b.LAST_MOD_USER_ID from ODS_STAGE.STOO_CMC_BLSB_SB_DETAIL b WHERE ' ||
			    ' a.BLEI_CK  =  b.BLEI_CK ' || 
  ' and TRUNC(a.BLBL_DUE_DT)  =  TRUNC(b.BLBL_DUE_DT) ' || 
  ' and a.CSPI_ID  =  b.CSPI_ID ' || 
  ' and a.PDPD_ID  =  b.PDPD_ID ' || 
  ' and a.PDBL_ID  =  b.PDBL_ID ' || 
  ' and TRUNC(a.BLSB_COV_DUE_DT)  =  TRUNC(b.BLSB_COV_DUE_DT) ' || 
  ' and TRUNC(a.BLSB_COV_FROM_DT)  =  TRUNC(b.BLSB_COV_FROM_DT) ' || 
  ' and a.BLSB_PREM_TYPE  =  b.BLSB_PREM_TYPE ' || 
  ' and a.SBSB_CK  =  b.SBSB_CK ' || 
  ' and a.MEME_CK  =  b.MEME_CK ' || 
  ' and TRUNC(a.BLSB_CREATE_DTM)  =  TRUNC(b.BLSB_CREATE_DTM) ' || 

			  ' ) ';

       commit; 
       
       status     := 'C'; 
       ending_time := SYSTIMESTAMP;
       execute immediate  ' UPDATE ODS_STAGE.REP_LOG  SET STATUS = :1, END_DTM = :2 WHERE SEQ_NO = :3 ' 
                            USING  status, ending_time, sequence_number;
                            
       commit;  

       DBMS_OUTPUT.put_line('The ending time is: ' || ending_time);
       DBMS_OUTPUT.put_line('Completed creating table CMC_BLSB_SB_DETAIL');

END;
/