{
   "threadgroup":600,
   "dataflowname":"IDENTIFICATIONS_ETL",
   "comment":"propagates identification changes to demographics table and identification table",
   "releasetoggle":"on",
   "comment":"Incremental data extraction via CDC for identification data",
   "workload":[
      {
         "sql":"TRUNCATE TABLE IDENTIFICATIONS_ST REUSE STORAGE IMMEDIATE",
         "tablename":"IDENTIFICATIONS_ST"
      },
      {
         "sql":"insert into IDENTIFICATIONS_ST  select concernRoleID,
          importTypeCode, importType, alternateID, preferredInd,comments from identifications_CDC_View",
         "tablename":"IDENTIFICATIONS_ST",
         "datastream":"true"
      },
      {
         "sql":"UPDATE DEMOGRAPHICS_MQT X SET (PREFERREDID,PREFERREDIDTYPE) =
           (select alternateID,importType from IDENTIFICATIONS_ST Y where X.concernroleid=Y.concernroleid
           and Y.preferredind='Y')",
         "tablename":"DEMOGRAPHICS_MQT"
      },
      {
         "sql":"delete from  IDENTIFICATIONS_MQT Y where  CONCERNROLEID in
            (select distinct concernRoleID from IDENTIFICATIONS_ST)",
         "tablename":"DEMOGRAPHICS_MQT"
      },
      {
         "sql":"insert into IDENTIFICATIONS_MQT select * from   IDENTIFICATIONS_ST",
         "tablename":"DEMOGRAPHICS_MQT"
      }
   ]
},
{
   "threadgroup":600,
   "dataflowname":"NOTE_ETL",
   "releasetoggle":"on", "comment":"Incremental data extraction via CDC for notes data",
   "workload":[
     {
        "sql":"declare global temporary table SESSION.NOTE_TT  AS (select * from note_cdc_view)
           WITH Data NOT LOGGED ON COMMIT DELETE ROWS",
        "tablename":"NOTE_TT",
        "datastream":"true"
     },
     {
         "sql":"MERGE INTO NOTE_MQT X USING SESSION.NOTE_TT Y on (X.noteid=Y.noteid)
          WHEN MATCHED then UPDATE SET X.priority=Y.priority, X.status=Y.status,X.duration=Y.duration
          WHEN NOT MATCHED THEN INSERT(noteid,username, priority, subject, notecontext, status, notetype, duration,
          modifieddatetime, concernroleid,  note)
          VALUES (Y.noteid,Y.username,Y.priority,Y.subject,Y.notecontext,Y.status,
          Y.notetype,Y.duration,Y.modifieddatetime,Y.concernroleid,Y.note)",
         "tablename":"NOTE_MQT"
      },
      {
         "sql":"delete from NOTE_MQT where status='NRS3'",
         "tablename":"NOTE_MQT",
         "comment":"delete records that are marked as cancelled"
      },
      {
         "sql":"drop table SESSION.NOTE_TT",
         "tablename":"NOTE_TT"
      }
   ]
}