please list input tables  and output tables in the following SQL


	INSERT INTO AR_Analytics.ap.LoadLog (Process, StartDateTime, TableName)
	SELECT OBJECT_NAME(@@PROCID), GETDATE(), @TableName

	SELECT @LoadID = MAX(LoadID)
	FROM AR_Analytics.ap.LoadLog

	BEGIN TRAN

 
			TRUNCATE TABLE ap.Adviser_ActionPlan

			INSERT INTO ap.Adviser_ActionPlan (
													Instance_Adviser__c
													,Instance_ARM_ID
													,Instance_Adviser_FirstName
													,Instance_Adviser_LastName
													,Instance_Adviser_Status
													,Instance_Licensee
													,Instance_Licensees
													,Instance_Name
													,Instance_Created_Date
													,Instance_CreatedBy_FirstName
													,Instance_CreatedBy_LastName
													--,Instance_Owner_AdviserId
													,Instance_Owner_FirstName
													,Instance_Owner_LastName
													,Instance_Closed_Date
													,Instance_Category
													,Instance_Sub_Category
													,Instance_Standard_Description
													,Auto_Escalate_OpRisk_Flag
													,Instance_Status
													,Instance_Escalation_Status
													,Instance_Description
													,Instance_Submitted_Date
													,Instance_Discovery_Date
													,Instance_Discovery_Method
													,Instance_Occurrence_Date
													,How_Instance_Happened
													,ActionPlan_Adviser__c
													,ActionPlan_ARM_ID
													,ActionPlan_Adviser_FirstName
													,ActionPlan_Adviser_LastName
													,ActionPlan_Adviser_Status
													,ActionPlan_Licensees__c
													,ActionPlan_Name
													,ActionPlan_Created_Date
													,ActionPlan_Closed_Date
													,ActionPlan_Due_Date
													,ActionPlan_Status
													,ActionPlan_Escalation_Status
													,ActionPlan_Actual_Resolved_In_Days
													,ActionPlan_Expected_Resolved_In_Days
													,ActionPlan_Control_ID
													,ActionPlan_Control_Title
													,ActionItem_Licensee
													,ActionItem_Licensees
													,ActionItem_Name
													,ActionItem_Created_Date
													,ActionItem_Due_Date
													,ActionItem_Completed_Date
													,ActionItem_Status
													,ActionItem_AP_Adviser__c
													,ActionItem_AP_ARM_ID
													,ActionItem_AP_Adviser_FirstName
													,ActionItem_AP_Adviser_LastName
													,ActionItem_AP_Adviser_Status
													,ActionItem_AP_Licensees__c
													,ActionItem_AP_Name
													,ActionItem_AP_Created_Date
													,ActionItem_AP_Closed_Date
													,ActionItem_AP_Due_Date
													,ActionItem_AP_Status
													,ActionItem_AP_Escalation_Status
													,ActionItem_AP_Actual_Resolved_In_Days
													,ActionItem_AP_Expected_Resolved_In_Days
													,ActionItem_AP_Control_ID
													,ActionItem_AP_Control_Title
												)
			SELECT
	  
					-- Instance Adviser
					i.Individual_Adviser__c											AS Instance_Adviser__c
					,ia.ARM_ID__c 													AS Instance_ARM_ID      	  
					,ia.FirstName 													AS Instance_Adviser_FirstName
					,ia.LastName  													AS Instance_Adviser_LastName
					,ia.Status__c 													AS Instance_Adviser_Status   

					-- Instance Details	  	
					,i.Licensee__c        											AS Instance_Licensee
					,i.Licensees__c        											AS Instance_Licensees	        
					,i.Name 														AS Instance_Name 	      	          
					,-- CONVERT(DATETIME, NULLIF(i.CreatedDate, ''), 121)   			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 127)     	
						)
					  AS Instance_Created_Date  
					,ic.FirstName													AS Instance_CreatedBy_FirstName
					,ic.LastName													AS Instance_CreatedBy_LastName   
					--,iwc.Id															AS Instance_Owner_AdviserId
					,iw.FirstName													AS Instance_Owner_FirstName
					,iw.LastName													AS Instance_Owner_LastName  
					,-- CONVERT(DATETIME, NULLIF(i.Closed_Date__c, ''), 121)   			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 127)     	
						)
					     		AS Instance_Closed_Date     
					,i.Category__c 													AS Instance_Category 
					,i.Sub_Category__c 												AS Instance_Sub_Category
					,ric.Instance_Code_Name 										AS Instance_Standard_Description       
					,ric.Auto_Escalate_OpRisk_Flag
					,i.Status__c 													AS Instance_Status
					,i.Escalation_Status__c 										AS Instance_Escalation_Status
					,LEFT (i.Description__c,5000) 												AS Instance_Description   
					,CONVERT(DATE, NULLIF(i.Submitted_Date__c, ''), 120)   			AS Instance_Submitted_Date     
					,CONVERT(DATE, NULLIF(i.Discovery_Date__c, ''), 120)   			AS Instance_Discovery_Date 
					,i.Discovery_Method__c 											AS Instance_Discovery_Method     
					,CONVERT(DATE, NULLIF(i.Occurrence_Date__c, ''), 120)   		AS Instance_Occurrence_Date 
					,LEFT(i.How_Instance_Happened__c,8000)								AS How_Instance_Happened

					-- AP Adviser (AI)
					,COALESCE(NULLIF(iap.Adviser__c,''), iap.Individual__c)			AS ActionPlan_Adviser__c
					,iapa.ARM_ID__c 												AS ActionPlan_ARM_ID      	  
					,iapa.FirstName 												AS ActionPlan_Adviser_FirstName
					,iapa.LastName 													AS ActionPlan_Adviser_LastName
					,iapa.Status__c 												AS ActionPlan_Adviser_Status      
	  
					-- AP Details	
					,iap.Licensees__c   											AS ActionPlan_Licensees__c  
					,iap.Name 														AS ActionPlan_Name	
					,-- CONVERT(DATETIME, NULLIF(iap.CreatedDate, ''), 120)   			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 127)     	
						)
					     			AS ActionPlan_Created_Date 
					,-- CONVERT(DATETIME, NULLIF(iap.Closed_Date__c, ''), 120)   			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 127)     	
						)
					     		AS ActionPlan_Closed_Date 
					,CONVERT(DATE, NULLIF(iap.Due_Date__c, ''), 120)   				AS ActionPlan_Due_Date 
					,iap.Status__c 													AS ActionPlan_Status 
					,iap.Escalation_Status__c 										AS ActionPlan_Escalation_Status   
					,DATEDIFF(DAY
							, -- CONVERT(DATETIME, NULLIF(iap.CreatedDate, ''), 120)    			
							 COALESCE (
								 TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 103)  
								,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 120)  
								,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 121)     	
								,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 126)      	
								,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 127)     	
								)
					  
							, -- CONVERT(DATETIME, NULLIF(iap.Closed_Date__c, ''), 120)    			
							 COALESCE (
								 TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 103)  
								,TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 120)  
								,TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 121)     	
								,TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 126)      	
								,TRY_CONVERT (DATETIME, NULLIF(iap.Closed_Date__c, ''), 127)     	
								)
					  
							) 		AS ActionPlan_Actual_Resolved_In_Days	  
					,DATEDIFF(DAY
							, --CONVERT(DATETIME, NULLIF(iap.CreatedDate, ''), 120)   			
								 COALESCE (
									 TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 103)  
									,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 120)  
									,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 121)     	
									,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 126)      	
									,TRY_CONVERT (DATETIME, NULLIF(iap.CreatedDate, ''), 127)     	
									)
					  

							, --CONVERT(DATE, NULLIF(iap.Due_Date__c, ''), 120)   			
								 COALESCE (
									 TRY_CONVERT (DATETIME, NULLIF(iap.Due_Date__c, ''), 103)  
									,TRY_CONVERT (DATETIME, NULLIF(iap.Due_Date__c, ''), 120)  
									,TRY_CONVERT (DATETIME, NULLIF(iap.Due_Date__c, ''), 121)     	
									,TRY_CONVERT (DATETIME, NULLIF(iap.Due_Date__c, ''), 126)      	
									,TRY_CONVERT (DATETIME, NULLIF(iap.Due_Date__c, ''), 127)     	
									)
					  
							)		AS ActionPlan_Expected_Resolved_In_Days
					,iap.Control_ID__c 												AS ActionPlan_Control_ID
					,iap.Control_Title__c 											AS ActionPlan_Control_Title 
	  	  
					-- AI Details	  	    	      	  
					,ai.Adviser_Licensee__c 										AS ActionItem_Licensee
					,ai.Licensees__c        										AS ActionItem_Licensees	  
					,ai.Name 														AS ActionItem_Name  
					,-- CONVERT(DATETIME, NULLIF(ai.CreatedDate, ''), 120)    			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(ai.CreatedDate, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(ai.CreatedDate, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(ai.CreatedDate, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(ai.CreatedDate, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(ai.CreatedDate, ''), 127)     	
						)
					    			AS ActionItem_Created_Date 
					,CONVERT(DATE, NULLIF(ai.Due_Date__c, ''), 120)   				AS ActionItem_Due_Date 
					,-- CONVERT(DATETIME, NULLIF(ai.Completed_Date__c, ''), 120)    			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(ai.Completed_Date__c, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(ai.Completed_Date__c, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(ai.Completed_Date__c, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(ai.Completed_Date__c, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(ai.Completed_Date__c, ''), 127)     	
						)
					  		AS ActionItem_Completed_Date 
					,ai.Status__c 													AS ActionItem_Status  		 

					-- AP Adviser
					,COALESCE(NULLIF(ap.Adviser__c,''), ap.Individual__c) 			AS ActionItem_AP_Adviser__c
					,apa.ARM_ID__c 													AS ActionItem_AP_ARM_ID      	  
					,apa.FirstName 													AS ActionItem_AP_Adviser_FirstName
					,apa.LastName 													AS ActionItem_AP_Adviser_LastName
					,apa.Status__c 													AS ActionItem_AP_Adviser_Status  

					-- AP Details
					,ap.Licensees__c   												AS ActionItem_AP_Licensees__c 
					,ap.Name 														AS ActionItem_AP_Name
					,-- CONVERT(DATETIME, NULLIF(ap.CreatedDate, ''), 120)    			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 127)     	
						)
					  			AS ActionItem_AP_Created_Date 
					,-- CONVERT(DATETIME, NULLIF(ap.Closed_Date__c, ''), 120)    			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 127)     	
						)
					  			AS ActionItem_AP_Closed_Date 
					,CONVERT(DATE, NULLIF(ap.Due_Date__c, ''), 120) 				AS ActionItem_AP_Due_Date 
					,ap.Status__c 													AS ActionItem_AP_Status 
					,ap.Escalation_Status__c 										AS ActionItem_AP_Escalation_Status   
					,DATEDIFF(DAY
							, -- CONVERT(DATETIME, NULLIF(ap.CreatedDate, ''), 120)
					 COALESCE (                         
						 TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 121)     	
						,TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 126)      	
						,TRY_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 127)     	
						)
					  
							, -- CONVERT(DATETIME, NULLIF(ap.Closed_Date__c, ''), 120)   			
					 COALESCE (
						 TRY_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 103)  
						,TRY_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 120)  
						,TRY_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 121)     	
						,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 126)      	
						,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 127)     	
						)
					  

							)	AS ActionItem_AP_Actual_Resolved_In_Days	  
					,DATEDIFF(DAY
							, --CONVERT(DATETIME, NULLIF(ap.CreatedDate, ''), 120)    			
					 COALESCE (
						 Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 103)  
						,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 120)  
						,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 121)     	
						,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 126)      	
						,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 127)     	
						)
					  
							, --CONVERT(DATE, NULLIF(ap.Due_Date__c, ''), 120)    			
					 COALESCE (
						 Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 103)  
						,Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 120)  
						,Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 121)     	
						,Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 126)      	
						,Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 127)     	
						)
					  
							)			AS ActionItem_AP_Expected_Resolved_In_Days
					,ap.Control_ID__c 												AS ActionItem_AP_Control_ID
					,ap.Control_Title__c 											AS ActionItem_AP_Control_Title     
	
			FROM AR_Analytics.Connect.ActionItem ai
			LEFT JOIN AR_Analytics.Connect.ActionPlan ap
				ON ai.Action_Plan__c = ap.Id
			LEFT JOIN AR_Analytics.Connect.Contact apa
				ON COALESCE(NULLIF(ap.Adviser__c,''), ap.Individual__c) = apa.Id 
			LEFT JOIN AR_Analytics.Connect.Instance i
				ON ai.Instance__c = i.Id  
			LEFT JOIN AR_Analytics.ap.Reference_Instance_Code ric
				ON i.Standard_Description__c = ric.Standard_Description__c
			LEFT JOIN AR_Analytics.Connect.Contact ia
				ON i.Individual_Adviser__c = ia.Id 
			LEFT JOIN AR_Analytics.Connect.[User] ic
				ON i.CreatedById = ic.Id
			LEFT JOIN AR_Analytics.Connect.[User] AS iw
				ON i.OwnerId = iw.Id 
			--LEFT JOIN AR_Analytics.Connect.Contact iwc
			--	ON iw.Id = iwc.OwnerId
			LEFT JOIN AR_Analytics.Connect.ActionPlan iap
				ON i.Action_Plan__c = iap.Id
			LEFT JOIN AR_Analytics.Connect.Contact iapa
				ON COALESCE(NULLIF(iap.Adviser__c,''), iap.Individual__c) = iapa.Id

			UNION ALL

			SELECT  	
	  
					-- Instance Adviser
					i.Individual_Adviser__c 								AS Instance_Adviser__c
					,ia.ARM_ID__c 											AS Instance_ARM_ID      	  
					,ia.FirstName 											AS Instance_Adviser_FirstName
					,ia.LastName  											AS Instance_Adviser_LastName
					,ia.Status__c 											AS Instance_Adviser_Status  

					-- Instance Details	  
					,i.Licensee__c        									AS Instance_Licensee
					,i.Licensees__c        									AS Instance_Licensees		                
					,i.Name 												AS Instance_Name 	        	          
					,-- CONVERT(DATETIME, NULLIF(i.CreatedDate, ''), 121)    			
					 COALESCE (
						 Try_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 103)  
						,Try_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 120)  
						,Try_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 121)     	
						,Try_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 126)      	
						,Try_CONVERT (DATETIME, NULLIF(i.CreatedDate, ''), 127)     	
						)
					  			  	AS Instance_Created_Date   
					,ic.FirstName											AS Instance_CreatedBy_FirstName
					,ic.LastName											AS Instance_CreatedBy_LastName  
					--,iwc.Id													AS Instance_Owner_AdviserId
					,iw.FirstName											AS Instance_Owner_FirstName
					,iw.LastName											AS Instance_Owner_LastName  
					,--CONVERT(DATETIME, NULLIF(i.Closed_Date__c, ''), 121)   			
					 COALESCE (
						 Try_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 103)  
						,Try_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 120)  
						,Try_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 121)     	
						,Try_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 126)      	
						,Try_CONVERT (DATETIME, NULLIF(i.Closed_Date__c, ''), 127)     	
						)
					  				AS Instance_Closed_Date     
					,i.Category__c 											AS Instance_Category 
					,i.Sub_Category__c 										AS Instance_Sub_Category	  
					,ric.Instance_Code_Name 								AS Instance_Standard_Description       
					,ric.Auto_Escalate_OpRisk_Flag
					,i.Status__c 											AS Instance_Status
					,i.Escalation_Status__c 								AS Instance_Escalation_Status
					,left(i.Description__c ,5000)									AS Instance_Description  
					,CONVERT(DATE, NULLIF(i.Submitted_Date__c, ''), 120)	AS Instance_Submitted_Date     
					,CONVERT(DATE, NULLIF(i.Discovery_Date__c, ''), 120)	AS Instance_Discovery_Date 
					,i.Discovery_Method__c 									AS Instance_Discovery_Method     
					,CONVERT(DATE, NULLIF(i.Occurrence_Date__c, ''), 120)	AS Instance_Occurrence_Date 
					,left(i.How_Instance_Happened__c,8000)							AS How_Instance_Happened

					-- AP Adviser (Instance)
					,COALESCE(NULLIF(ap.Adviser__c,''), ap.Individual__c)	AS ActionPlan_Adviser__c
					,apa.ARM_ID__c 											AS ActionPlan_ARM_ID      	  
					,apa.FirstName 											AS ActionPlan_Adviser_FirstName
					,apa.LastName 											AS ActionPlan_Adviser_LastName
					,apa.Status__c 											AS ActionPlan_Adviser_Status     	 	  	   
	  
					-- AP Details	  	
					,ap.Licensees__c   										AS ActionPlan_Licensees__c 
					,ap.Name 												AS ActionPlan_Name  
					,-- CONVERT(DATETIME, NULLIF(ap.CreatedDate, ''), 120)   			
					 COALESCE (
						 Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 103)  
						,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 120)  
						,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 121)     	
						,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 126)      	
						,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 127)     	
						)
					  			   	AS ActionPlan_Created_Date 
					,--CONVERT(DATETIME, NULLIF(ap.Closed_Date__c, ''), 120)   			
					 COALESCE (
						 Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 103)  
						,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 120)  
						,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 121)     	
						,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 126)      	
						,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 127)     	
						)
					  			  AS ActionPlan_Closed_Date 
					,CONVERT(DATE, NULLIF(ap.Due_Date__c, ''), 120)   		AS ActionPlan_Due_Date 
					,ap.Status__c 											AS ActionPlan_Status 
					,ap.Escalation_Status__c 								AS ActionPlan_Escalation_Status   
					,DATEDIFF(DAY
								, -- CONVERT(DATETIME, NULLIF(ap.CreatedDate, ''), 120)    			
								 COALESCE (
									 Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 103)  
									,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 120)  
									,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 121)     	
									,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 126)      	
									,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 127)     	
									)
					  			
								, -- CONVERT(DATETIME, NULLIF(ap.Closed_Date__c, ''), 120)   			
								 COALESCE (
									 Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 103)  
									,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 120)  
									,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 121)     	
									,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 126)      	
									,Try_CONVERT (DATETIME, NULLIF(ap.Closed_Date__c, ''), 127)     	
									)
					  			
								)	AS ActionPlan_Actual_Resolved_In_Days	  
					,DATEDIFF(DAY
								, -- CONVERT(DATETIME, NULLIF(ap.CreatedDate, ''), 120)   			
								 COALESCE (
									 Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 103)  
									,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 120)  
									,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 121)     	
									,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 126)      	
									,Try_CONVERT (DATETIME, NULLIF(ap.CreatedDate, ''), 127)     	
									)
					  			
								, -- CONVERT(DATE, NULLIF(ap.Due_Date__c, ''), 120)   			
								 COALESCE (
									 Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 103)  
									,Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 120)  
									,Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 121)     	
									,Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 126)      	
									,Try_CONVERT (DATETIME, NULLIF(ap.Due_Date__c, ''), 127)     	
									)
								)			AS ActionPlan_Expected_Resolved_In_Days
					,ap.Control_ID__c										AS ActionPlan_Control_ID
					,ap.Control_Title__c									AS ActionPlan_Control_Title          	  
	  	  
					-- AI Details Set to NULL 	     	      	  
					,NULL 													AS ActionItem_Licensee
					,NULL 													AS ActionItem_Licensees	  
					,NULL 													AS ActionItem_Name  
					,NULL 													AS ActionItem_Created_Date
					,NULL 													AS ActionItem_Due_Date
					,NULL 													AS ActionItem_Completed_Date	              
					,NULL 													AS ActionItem_Status	   

					-- AP Adviser (AI) Set to NULL
					,NULL 													AS ActionItem_AP_Adviser__c
					,NULL 													AS ActionItem_AP_ARM_ID      	  
					,NULL 													AS ActionItem_AP_Adviser_FirstName
					,NULL 													AS ActionItem_AP_Adviser_LastName
					,NULL 													AS ActionItem_AP_Adviser_Status  
	  
					-- AP Details (AI) Set to NULL
					,NULL 													AS ActionItem_AP_Licensees__c   
					,NULL 													AS ActionItem_AP_Name	
					,NULL    												AS ActionItem_AP_Created_Date      
					,NULL 													AS ActionItem_AP_Closed_Date      
					,NULL    												AS ActionItem_AP_Due_Date      
					,NULL 													AS ActionItem_AP_Status 
					,NULL 													AS ActionItem_AP_Escalation_Status   
					,NULL  													AS ActionItem_AP_Actual_Resolved_In_Days	  
					,NULL  													AS ActionItem_AP_Expected_Resolved_In_Days
					,NULL 													AS ActionItem_AP_Control_ID
					,NULL 													AS ActionItem_AP_Control_Title 

			FROM AR_Analytics.Connect.Instance i 
			LEFT JOIN AR_Analytics.ap.Reference_Instance_Code ric
				ON i.Standard_Description__c = ric.Standard_Description__c  
			LEFT JOIN AR_Analytics.Connect.Contact ia
				ON i.Individual_Adviser__c = ia.Id  
			LEFT JOIN AR_Analytics.Connect.[User] ic
				ON i.CreatedById = ic.Id 
			LEFT JOIN AR_Analytics.Connect.[User] AS iw
				ON i.OwnerId = iw.Id  
			--LEFT JOIN AR_Analytics.Connect.Contact iwc
			--	ON iw.Id = iwc.OwnerId
			LEFT JOIN AR_Analytics.Connect.ActionPlan ap
				ON i.Action_Plan__c = ap.Id  
			LEFT JOIN AR_Analytics.Connect.Contact apa
				ON COALESCE(NULLIF(ap.Adviser__c,''), ap.Individual__c) = apa.Id 
			LEFT JOIN AR_Analytics.Connect.ActionItem ai
				ON i.Id = ai.Instance__c
			WHERE ai.Instance__c IS NULL;

		SET @RowsInserted = @@ROWCOUNT;

	COMMIT;

	UPDATE AR_Analytics.ap.LoadLog
	SET EndDateTime	= GETDATE(),
		Records		= @RowsInserted
	WHERE LoadID = @LoadID