CREATE PROC [DML].[LoadExcepMissOLSAndPAIntel]
	 @RunID INT
	,@BatchID INT
	,@PassLoggingOn BIT = 0
AS
BEGIN

DECLARE  @ErrMsg VARCHAR(1000)  
        ,@ErrNum VARCHAR(15)  
        ,@Severity INT  
        ,@State INT   
        ,@RecordCount INT 
        ,@ReturnValue SMALLINT
        ,@ProcName NVARCHAR(100) = N'LoadExcepMissOLSAndPAIntel' 
  
BEGIN TRANSACTION  
BEGIN TRY  
        
		SET NOCOUNT ON  
      
		EXEC @ReturnValue = [DML].[ProcessCurrentStatusAction] @ProcName, @BatchID, @runID
      
		--If the stored procedure is already executed for this batch,then return.
		IF @ReturnValue < 0
		BEGIN	
				COMMIT;
				RETURN 0
		END	 
        
        --DMLStats - Begin   
		EXEC DML.LogEventDMLStat @ApplicationName = 'Kobe-STAT'
								,@ProcessName = 'LoadExceptionsTracking'
								,@ProcessFunction = 'Insert'
								,@BatchID = @BatchID 
								,@PrimaryObject = 'ExceptionsTrackingTable'
								,@SubObject1 = @ProcName
								,@SubObject2 = ''
								,@Comment = 'No data from OLS and PAIntel,but CP has data' 
								,@ReportLevel = 1
								,@RecordCount = 0
								,@LoggingOn = @PassLoggingOn
								,@Verbose = 0
		

		TRUNCATE TABLE DML.ExceptMissingOLSMachine 

		ALTER INDEX NCIX_ExceptMissingOLSMachine_PartnerEntitlementID ON ExceptMissingOLSMachine disable

		INSERT INTO ExceptMissingOLSMachine
		SELECT PartnerEntitlementID 
		  FROM DML.ExceptionsTrackingTable
		 WHERE ExceptionType <> N'No OLS Machine Data' 
		   AND PartnerEntitlementID IS NOT NULL
		   AND ActivationModel  = 'Subscription'

        ALTER INDEX NCIX_ExceptMissingOLSMachine_PartnerEntitlementID ON ExceptMissingOLSMachine REBUILD 


		INSERT INTO [DML].[ExceptionsTrackingTable]
				   ([AppCode]
				   ,[LiveIDEncryptedKey]
				   ,[ActivationModel]
				   ,[DMLUpdatedTimestamp]
				   ,[ActID]
				   ,[SubscriptionID]
				   ,[SubscriptionStartDate]
				   ,[SubscriptionEndDate]
				   ,[SubscriptionLength]
				   ,[OfferType]
				   ,[IsTrial]
				   ,[ProductSKU]
				   ,[ProductSKUDescription]
				   ,[PartnerEntitlementId]
				   ,[RunID]
				   ,[BatchID]
				   ,[ExceptionReason]
				   ,[ExceptionType]
				   ,[DMLIsDeletedFlag]
				   ,[OriginalSubscriptionKey]
				   ,[OfferDuration]
				   ,[OfferDurationUOM]
				   ,[OriginalSubscriptionStartDate]
				   ,[SubscriptionStatusName]
				   ,[BillingStatusName])
         SELECT DISTINCT 'OREF' AppCode
					,OLSEntitlement.LiveIDEncryptedKey
					,'Subscription' ActivationModel
					,GETDATE() DMLUpdatedTimestamp
					,CAST(MintSubscription.CTPBDKID AS VARCHAR(50)) COLLATE  Latin1_General_CS_AS  ActID
					,MintSubscription.SubscriptionID
					,MintSubscription.SubscriptionStartDate
					,MintSubscription.SubscriptionEndDate
					,MintSubscription.SubscriptionLength
					,MintSubscription.OfferType 
					,MintSubscription.IsTrial
					,MintSubscription.ProductSKU
					,MintSubscription.ProductSKUDescription
					,MintSubscription.CTPBDKID PartnerEntitlementId
					,@RunID RunID
					,@BatchID BatchID
					,N'No data from OLS and PAIntel,but CP has data' ExceptionReason
					,N'No OLS and PAIntel data' ExceptionType
					,0 AS DMLIsDeletedFlag
					,MintSubscription.OriginalSubscriptionKey
					,MintSubscription.OfferDuration
					,MintSubscription.OfferDurationUOM
					,MintSubscription.SubscriptionStartDate AS OriginalSubscriptionStartDate
					,MintSubscription.SubscriptionStatusName 
					,MintSubscription.BillingStatusName
				FROM DML.ExtCPSubscriptionOffers MintSubscription
		   LEFT JOIN DML.ExtOLSMachineEntitlement OLSEntitlement
				  ON CAST(MintSubscription.CTPBDKID AS NVARCHAR(128)) = OLSEntitlement.PartnerEntitlementId
		   LEFT JOIN ExceptMissingOLSMachine Exceptions   
                  ON Exceptions.PartnerEntitlementID = MintSubscription.CTPBDKID
           LEFT JOIN DML.AccumulatedTransformedTable att ON MintSubscription.OriginalSubscriptionKey = att.OriginalSubscriptionKey
               WHERE OLSEntitlement.PartnerEntitlementId IS NULL
			         AND Exceptions.PartnerEntitlementID IS NULL
                     AND att.OriginalSubscriptionKey IS NULL
	                  
		SELECT @RecordCount =  @@ROWCOUNT
		

		EXEC DML.LogEventDMLStat @ApplicationName = 'Kobe-STAT'
								,@ProcessName = 'LoadExceptionsTracking'
								,@ProcessFunction = 'Insert'
								,@BatchID = @BatchID 
								,@PrimaryObject = 'ExceptionsTrackingTable'
								,@SubObject1 = @ProcName
								,@SubObject2 = ''
								,@Comment = 'No data from OLS and PAIntel,but CP has data' 
								,@ReportLevel = 1
								,@RecordCount = @RecordCount
								,@LoggingOn = @PassLoggingOn
								,@Verbose = 0
								
		--Update the process complete date on completion
		EXEC @ReturnValue = [DML].[ProcessCompleteStatus] @ProcName, @BatchID, @RunID 
		
COMMIT TRANSACTION  
END TRY  
            
BEGIN CATCH  
  
              SELECT @ErrMsg = 'Error: ' + CONVERT(VARCHAR(15), ERROR_NUMBER()) + ' - ' + ERROR_MESSAGE()  
                   ,@Severity = ERROR_SEVERITY()  
                   ,@State = ERROR_STATE()  
                   ,@ErrNum = CONVERT(VARCHAR(15), ERROR_NUMBER())  
                     
              RAISERROR(@ErrMsg, @Severity, @State)  
                
              ROLLBACK TRANSACTION  
 
END CATCH 
END