Drop table if exists #content_asset_mapping;
   
   Create table #content_asset_mapping as	  
	  SELECT     contskill.scdtimestamp, skillid skill_id,assetid content_id,upper(operation)operation
     FROM       dw.dimassetskillsmapping contskill
     WHERE     intpartnerid IN   (SELECT IntegrationPartnerName FROM etl.IntegrationPartner  WHERE IntegrationPartnerID = 3)
		  --AND rowversion  > '20180801';
          AND rowversion  > '${LastProcessRowversion}'
          AND rowversion <= '${CurrentRowversion}';

	   Update report.standard_hierarchy set end_school_year =rec.end_school_year
	   ,is_Active=false
	   from ( Select   cast(case when date_part('month',contskill.scdtimestamp)>7 then date_part('year',contskill.scdtimestamp) else date_part('year',contskill.scdtimestamp)-1 end  as smallint)End_School_Year
	   		, skill_id,content_id
			from #content_asset_mapping contskill
	   where operation='DELETE')rec
	   where rec.content_id=report.standard_hierarchy.content_id;
	   
	   
	   Insert into  report.Standard_Hierarchy(platform,framework_id,standard_id,skill_id,content_id,content_type,Start_School_Year,is_Active)
Select platform,framework_id,standard_id,skill_id,content_id ,isnull(upper(cont.contenttype),'') content_type
,case when date_part('month',rec.eventdate)>7 then date_part('year',rec.eventdate) else date_part('year',rec.eventdate)-1 end Start_School_Year
,true is_Active
from (q
Select  std.intpartnerid platform,std.frameworkid framework_id,std.standardid standard_id,contskill.skill_id ,contskill.content_id
,greatest(contskill.scdtimestamp , skillstd.scdtimestamp ,std.scdtimestamp)eventdate
from
(Select cont.* from #content_asset_mapping cont where not exists 
(Select 1 from report.Standard_Hierarchy hier where hier.content_id=cont.content_id and hier.skill_id=cont.skill_id))contskill,
dw.dimskillstandardmapping  skillstd,
dw.dimstandard std
where contskill.skill_id=skillstd.skillid
and skillstd.standardid=std.standardid
and framework_id <>'')rec
,(Select contentassetid,contenttype from (Select contentassetid,contenttype , dense_rank() over(partition by contentassetid order by scdtimestamp desc)rnk from  dw.dimcontent 
where lower(operation) <>'delete') where rnk=1) cont
where rec.content_id=cont.contentassetid(+)
and length(skill_id)<65
and length(content_id)<65
and length(standard_id)<65
and length(framework_id)<65; 
	   
	     Drop table if exists #skill_standard_mapping;
   
   Create table #skill_standard_mapping as	  
	  SELECT     skillstd.scdtimestamp, skillid skill_id,standardid standard_id,upper(operation)operation
     FROM       dw.dimskillstandardmapping skillstd
     WHERE     intpartnerid IN   (SELECT IntegrationPartnerName FROM etl.IntegrationPartner  WHERE IntegrationPartnerID = 3)
		  AND rowversion  > '20180801';
       --   AND rowversion  > '${LastProcessRowversion}'
       --   AND rowversion <= '${CurrentRowversion}';

	   Update report.standard_hierarchy set end_school_year =rec.end_school_year
	   ,is_Active=false
	   from ( Select   cast(case when date_part('month',skillstd.scdtimestamp)>7 then date_part('year',skillstd.scdtimestamp) else date_part('year',skillstd.scdtimestamp)-1 end  as smallint)End_School_Year
	   		, skill_id,Standard_id
			from #skill_standard_mapping skillstd
	   where operation='DELETE')rec
	   where rec.Standard_id=report.standard_hierarchy.Standard_id;
	   
	   
	   Insert into  report.Standard_Hierarchy(platform,framework_id,standard_id, content_id,content_type,Start_School_Year,is_Active)
Select platform,framework_id,standard_id,skill_id,content_id ,isnull(upper(cont.contenttype),'') content_type
,case when date_part('month',rec.eventdate)>7 then date_part('year',rec.eventdate) else date_part('year',rec.eventdate)-1 end Start_School_Year
,true is_Active
from (
Select  std.intpartnerid platform,std.frameworkid framework_id,std.standardid standard_id,contskill.assetid content_id
,greatest(contskill.scdtimestamp , skillstd.scdtimestamp ,std.scdtimestamp)eventdate
from
dw.dimassetskillsmapping contskill,
(Select skillst.* from #skill_standard_mapping skillst where not exists 
(Select 1 from report.Standard_Hierarchy hier where hier.standard_id=skillst.standard_id and hier.skill_id=skillst.skill_id))  skillstd,
dw.dimstandard std
where skillstd.standard_id=std.standardid
and framework_id <>'')rec
,(Select contentassetid,contenttype from (Select contentassetid,contenttype , dense_rank() over(partition by contentassetid order by scdtimestamp desc)rnk from  dw.dimcontent 
where lower(operation) <>'delete') where rnk=1) cont
where rec.content_id=cont.contentassetid(+)
and length(content_id)<65
and length(standard_id)<65
and length(framework_id)<65; 
	   
	   
	   
Drop table if exists #standard;
   
   Create table #standard as	  
	  SELECT     std.scdtimestamp, standardid standard_id,frameworkid framework_id, upper(operation)operation
     FROM       dw.dimstandard std
     WHERE     intpartnerid IN   (SELECT IntegrationPartnerName FROM etl.IntegrationPartner  WHERE IntegrationPartnerID = 3)
		  AND rowversion  > '20180801';
       --   AND rowversion  > '${LastProcessRowversion}'
       --   AND rowversion <= '${CurrentRowversion}';

	   Update report.standard_hierarchy set end_school_year =rec.end_school_year
	   ,is_Active=false
	   from ( Select   cast(case when date_part('month',std.scdtimestamp)>7 then date_part('year',std.scdtimestamp) else date_part('year',std.scdtimestamp)-1 end  as smallint)End_School_Year
	   		, framework_id,Standard_id
			from #standard std
	   where operation='DELETE')rec
	   where rec.framework_id=report.standard_hierarchy.framework_id
	   and rec.Standard_id=report.standard_hierarchy.Standard_id;
	   
	   
	   Insert into  report.Standard_Hierarchy(platform,framework_id,standard_id,skill_id,content_id,content_type,Start_School_Year,is_Active)
Select platform,framework_id,standard_id,skill_id,content_id ,isnull(upper(cont.contenttype),'') content_type
,case when date_part('month',rec.eventdate)>7 then date_part('year',rec.eventdate) else date_part('year',rec.eventdate)-1 end Start_School_Year
,true is_Active
from (
Select  contskill.intpartnerid platform,std.framework_id,std.standard_id,skillstd.skillid skill_id ,contskill.assetid content_id
,greatest(contskill.scdtimestamp , skillstd.scdtimestamp ,std.scdtimestamp)eventdate
from
dw.dimassetskillsmapping contskill,
dw.dimskillstandardmapping  skillstd, 

(Select st.* from #standard st where not exists 
(Select 1 from report.Standard_Hierarchy hier where hier.standard_id=st.standard_id and hier.framework_id=st.framework_id)) std
where contskill.skillid=skillstd.skillid
and skillstd.standardid=std.standard_id
and std.framework_id <>'')rec
,(Select contentassetid,contenttype from (Select contentassetid,contenttype , dense_rank() over(partition by contentassetid order by scdtimestamp desc)rnk from  dw.dimcontent 
where lower(operation) <>'delete') where rnk=1) cont
where rec.content_id=cont.contentassetid(+)
and length(content_id)<65
and length(standard_id)<65
and length(framework_id)<65; 
	   
	   
	   Delete from report.standard_hierarchy where start_school_year=end_school_year;