DW structure changes

• add new column
  • F_Interactions table
    • DynamicRoutingData

Dataset Report_CompletedCampaignInteraction_DispositionCodeList deleted

Dataset Report_DetailedBusinessEntity:

• fields:
  • transferTo
  • consult

Dataset Report_DetailedBusinessEntity_SubDs:

• fields:
  • transferTo
  • consult

Dataset Report_AgentTransferredInteractions:

• fields:
  • EntityName

LOGIC CHANGES:

• Users db procedures changed
  • dbo.spUpload_ReportExecutionLog
  • dbo.spRequest_ForQueueData_R1
  • DW.spRpt_AgentTransferredInteractions
• Users db procedures deleted in dbo schema(clean unusued resources):

  • spRpt_AgentAssignmentToBP

  • spRpt_AgentBreaks

  • spRpt_AgentDetailedAlerts

  • spRpt_AgentInteractionSummaryReport

  • spRpt_AgentRejectedInteractions

  • spRpt_AgentTransferredInteractions

  • spRpt_BusinessProcessDetailedAlerts

  • spRpt_DestinationTrace

  • spRpt_DetailedAgentActivity

  • spRpt_DetailedAgentInteractions

  • spRpt_DetailedBusinessEntity

  • spRpt_DetailedBusinessEntity_SubDs1

  • spRpt_DispositionCodesDistribution

  • spRpt_InboundInteractionDistribution

  • spRpt_IncomingCallsByAreaCode_ByAreaCode

  • spRpt_IncomingCallsByAreaCode_ByAreaCodeGroupByDestination

  • spRpt_IncomingCallsByAreaCode_ByState

  • spRpt_IncomingCallsByAreaCode_ByStateGroupByDestination

  • spRpt_InteractionDispositionCodes

  • spRpt_MistreatedInteractions

  • spRpt_QueryInteractionsByOriginOrDestination

  • spRpt_VoiceBilling

  • spRpt_VoiceBilling_SubDs1

  • spRpt_BusinessProcessDetailedAlerts

  • spRpt_CompletedCallbackInteraction

  • spRpt_DelegatedInteractions

  • spRpt_InteractionsAnalysis

  • spRpt_VoiceBillingByBusinessProcess

  • spRpt_VoiceBillingByBusinessProcess_SubDs1

  • spRpt_VoiceBillingByBusinessProcessNoAbandonedCalls

  • spRpt_VoiceBillingByBusinessProcessNoAbandonedCalls_SubDs1

  • spRpt_VoiceBillingSummaryOnly

  • spRpt_APCSummary

  • spRpt_AgentScoringReport_Prm_InteractionTypeList

  • spRpt_BusinessProcessInteractionPerformance_Prm_InteractionTypeList

  • spRpt_CompletedCampaignInteraction_Prm_DispositionCodeList

  • spRpt_DetailedBusinessEntity_Prm_CustomerBillingCodeList

  • spRpt_InteractionDispositionCodes_Prm_DispositionCodeList

  • spRpt_CustomersComparativeVoiceMinutes

• ETL db changes:
  • changes procedures:
    • dbo.Upload_D_CounterType
    • Upload_F_Interactions

• 1.01 Detailed Agent Activity:
  • procedures changed:
    • DW.spRpt_DetailedAgentActivity
  • Interaction State: 
    • Previous:

      • case 
            when D_AgentStatus.AgentStatusName = 'Busy' and F_AgentLife.InteractionState = 'Wrap Up' and exists (F_interactionSegments.InteractionState = 'Handling' where F_interactionSegments.InteractionId = F_AgentLife.InteractionId
                and F_interactionSegments.AgentID = F_AgentLife.AgentID) then 'Handling + Wrap Up'
            when D_AgentStatus.AgentStatusName = 'Dialing' and F_AgentLife.InteractionState = 'Wrap Up' and exists (F_interactionSegments.InteractionState = 'Handling' where F_interactionSegments.InteractionId = F_AgentLife.InteractionId
                and F_interactionSegments.AgentID = F_AgentLife.AgentID) then 'Dialing + Wrap Up'
            else F_AgentLife.InteractionState
        end

    •  Current:
      • case     
            when D_AgentStatus.AgentStatusName = 'Busy' and F_AgentLife.InteractionState = 'Wrap Up' and exists (F_interactionSegments.InteractionState = 'Handling' where F_interactionSegments.InteractionId = F_AgentLife.InteractionId
                and F_interactionSegments.AgentID = F_AgentLife.AgentID) then 'Handling + Wrap Up'
            when D_AgentStatus.AgentStatusName = 'Dialing' and F_AgentLife.InteractionState = 'Wrap Up' and exists (F_interactionSegments.InteractionState = 'Handling' where F_interactionSegments.InteractionId = F_AgentLife.InteractionId
                and F_interactionSegments.AgentID = F_AgentLife.AgentID) then 'Dialing + Wrap Up'
            when D_AgentStatus.AgentStatusName = 'Busy' and F_AgentLife.InteractionState = 'Wrap Up' and exists (F_interactionSegments.InteractionState = 'Handling' where F_interactionSegments.InteractionId = F_AgentLife.InteractionId
                and F_interactionSegments.AgentID = F_AgentLife.AgentID) and exists (F_interactionSegments.AdditionalInfo(json).consultees.consult_handlerType = 2 and F_interactionSegments.InteractionState in (''Consulting'', ''Consulting'')) then iif(F_interactionSegments.InteractionState = ''Consulting'', ''Consulting'', ''In Conference'') + 'Handling + Wrap Up'
            when D_AgentStatus.AgentStatusName = 'Dialing' and F_AgentLife.InteractionState = 'Wrap Up' and exists (F_interactionSegments.InteractionState = 'Handling' where F_interactionSegments.InteractionId = F_AgentLife.InteractionId
                and F_interactionSegments.AgentID = F_AgentLife.AgentID) and exists (F_interactionSegments.AdditionalInfo(json).consultees.consult_handlerType = 2 and F_interactionSegments.InteractionState in (''Consulting'', ''Consulting'')) then iif(F_interactionSegments.InteractionState = ''Consulting'', ''Consulting'', ''In Conference'') + 'Dialing + Wrap Up'
            when F_interactionSegments.InteractionStateName not in (''Consulting'', ''In Conference'') and exists (F_interactionSegments.AdditionalInfo(json).consultees.consult_handlerType = 2 and F_interactionSegments.InteractionState in (''Consulting'', ''Consulting''))
            then iif(F_interactionSegments.InteractionState = ''Consulting'', ''Consulting'', ''In Conference'') + '' + '' + F_AgentLife.InteractionState
            else F_AgentLife.InteractionState
        end
• 4.01 Detailed Business Entity:
  • procedures changed:
    • DW.spRpt_DetailedBusinessEntity
    • DW.spRpt_DetailedBusinessEntity_SubDs
  • Consultees/Transfers: 
    • Previous:

      • -

    •  Current:
      • "Consult:"+F_interactionSegments.AdditionalInfo(json).consultees/"Transfer:"+F_interactionSegments.AdditionalInfo(json).endedInTransfer