DW structure changes

• add new column 
  • F_Interactions table
    • SIPCallID

LOGIC CHANGES:

• Users db procedures changed:
  • dbo.spRpt_GetInteractionsDataset
• ETL db changes:
  • changes procedures:
    • dbo.Upload_F_Interactions

• 1.01 Detailed Agent Activity:
  • procedures changed:
    • DW.spRpt_DetailedAgentActivity
  • Interaction State: 
    • Previous:
      • F_AgentLife.InteractionState
    •  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'
            else F_AgentLife.InteractionState
        end

• 2.01 Detailed Agent Interactions:
  • procedures changed:
    • DW.spRpt_DetailedAgentInteractions
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')

• 3.01 Mistreated Interactions:

  • procedures changed:
    • DW.spRpt_MistreatedInteractions
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')

• 3.02 Query Interactions By Origin or Destination:

  • procedures changed:
    • DW.spRpt_QueryInteractionsByOriginOrDestination
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')

• 3.05 Conversations:

  • procedures changed:
    • DW.spRpt_Conversations
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')

• 4.01 Detailed Business Entity:

  • procedures changed:
    • DW.spRpt_DetailedBusinessEntity
    • DW.spRpt_DetailedBusinessEntity_SubDs1
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')

• 4.02 Destination Trace:

  • procedures changed:
    • DW.spRpt_DestinationTrace
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')

• 4.03 Interaction Disposition Codes:

  • procedures changed:
    • DW.spRpt_InteractionDispositionCodes
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')

• 5.05 Queue Interval Summary:

  • procedures changed:
    • DW.spRpt_APCSummary
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')

• 6.01 Completed Campaign Interactions:

  • procedures changed:
    • DW.spRpt_CompletedCampaignInteraction
  • Filter by report parameters - DispositionCode: 
    • Previous:
      • F_Interactions.DispositionCode
    •  Current:

      • F_Interactions.DispositionCode + '(' + isnull(F_Interactions.DispositionCodeCategory,'No Category Name') + '->' 
                + isnull(F_Interactions.DispositionCodeSubCategory, 'No Sub Category Name')