Queries

• 21
  Expressions:

  

  Data Items

  

  Detail Filters
• Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model

• 

  

  Load Last Date Query

  Data Items

  1

  Latest Valid Data Load Date	[Presentation].[Data Load Archive].[Latest Valid Data Load Date]

• 

  

  Reporting Date Range

  Data Items

  3

  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  WeekNumber	_day_of_week (current_date,1)

• 

  

  Security Group Prompt query

  Data Items

  2

  Group Name	[Presentation].[VW_STG_GROUPS].[NAME]
  Group Sorting	character_length ([Group Name])

  • Detail Filters
    2
  • 

    [Group Name] is not null
    
  • 

    [Presentation].[Compensation Advisor]
    
• 

  

  Assigned q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('01. Attribué') ELSE ('01. Assigned')
  Action Date	[Presentation].[Case].[DATE_ASSIGNED_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	1

  • Detail Filters
    3
  • 

    [Presentation].[Case].[DATE_ASSIGNED_EST] between [Begin Date] and [End Date]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Send For Verification q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('02. Envoyé pour vérification') ELSE ('02. Sent for Verification')
  Action Date	[Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	2

  • Detail Filters
    3
  • 

    ([Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST] between [Begin Date] and [End Date])
    
    and
    ([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Ready_To_Verify')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] = 'Assigned')
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Returned from Verification q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('03. Renvoyé de la vérification') ELSE ('03: Returned from Verification')
  Action Date	[Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	3

  • Detail Filters
    3
  • 

    ([Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST] between [Begin Date] and [End Date])
    and
    ([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Reassigned')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] = 'Ready_To_Verify')
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Closed q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('04. Fermé') ELSE ('04: Closed')
  Action Date	[Presentation].[Case].[DATE_CLOSED_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	4

  • Detail Filters
    3
  • 

    [Presentation].[Case].[DATE_CLOSED_EST] between [Begin Date] and [End Date]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Cancelled q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('05. Annulé') ELSE ('05. Cancelled')
  Action Date	[Presentation].[Case].[DATE_CANCELLED_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	5

  • Detail Filters
    3
  • 

    [Presentation].[Case].[DATE_CANCELLED_EST] between [Begin Date] and [End Date]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Verified q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[VERIFIER]))
  Team Name	'Verifiers/Coaches'
  Action	IF ( #sq($runLocale)#='fr') THEN ('06. Vérifié') ELSE ('06: Verified')
  Action Date	[Presentation].[Case].[VERIFIED_DATE_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	6

  • Detail Filters
    3
  • 

    [Presentation].[Case].[VERIFIED_DATE_EST] between [Begin Date] and [End Date]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Rejected by Verifier q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[VERIFIER]))
  Team Name	'Verifiers/Coaches'
  Action	IF ( #sq($runLocale)#='fr') THEN ('07. Rejeté par le vérificateur') ELSE ('07. Rejected by Verifier')
  Action Date	[Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	7

  • Detail Filters
    3
  • 

    ([Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST] between [Begin Date] and [End Date])
    and
    ([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Reassigned')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] = 'Ready_To_Verify')
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Sent to Pending q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('08. Mis en attente') ELSE ('08. Sent to Pending')
  Action Date	[Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	8

  • Detail Filters
    3
  • 

    ([Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST] between [Begin Date] and [End Date])
    and
    ([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Pending')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] ='Assigned' )
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Reassigned q

  Data Items

  19

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	[Presentation].[VW_STG_HISTORY].[USER_NAME]
  Action	IF ( #sq($runLocale)#='fr') THEN ('09. Réaffecté') ELSE ('09: Reassigned')
  Action Date	[Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	9

  • Detail Filters
    2
  • 

    ([Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST] between [Begin Date] and [End Date])
    and
    ([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Reassigned')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] ='Assigned' )
    and
    ([Presentation].[State].[NAME] = 'Reassigned')
    and
    ([Presentation].[Case].[DATE_ASSIGNED_EST] < [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST])
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Awaiting Calc q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('10. En attente du calcul') ELSE ('10. Awaiting Calc')
  Action Date	[Presentation].[Case].[TIME_IN_ASSIGNED_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	10

  • Detail Filters
    3
  • 

    [Presentation].[Case].[TIME_IN_ASSIGNED_EST] between [Begin Date] and [End Date]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Awaiting Confirm q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('11. En attente de la confirmation') ELSE ('11. Awaiting Confirm')
  Action Date	[Presentation].[Case].[TIME_OUT_ASSIGNED_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	11

  • Detail Filters
    3
  • 

    [Presentation].[Case].[TIME_OUT_ASSIGNED_EST] between [Begin Date] and [End Date]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Awaiting Off Cycle q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('12. En attente de la période hors cycle') ELSE ('12. Awaiting Off Cycle')
  Action Date	[Presentation].[Case].[TIME_IN_REASSIGNED_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	12

  • Detail Filters
    3
  • 

    [Presentation].[Case].[TIME_IN_REASSIGNED_EST] between [Begin Date] and [End Date]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Awaiting Pay Sheet q

  Data Items

  20

  Case Number	[Presentation].[Case].[ID]
  PRI	[Presentation].[Case].[PRI]
  Processing Centre	[Presentation].[Case].[PROCESSING_CENTRE]
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	[Presentation].[Case].[DATE_OPEN_EST]
  BF Date	[Presentation].[Case].[DATE_BF_EST]
  Date Closed	[Presentation].[Case].[DATE_CLOSED_EST]
  Date Assigned	[Presentation].[Case].[DATE_ASSIGNED_EST]
  Date Cancelled	[Presentation].[Case].[DATE_CANCELLED_EST]
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assiged Agent	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Team Name	CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) END
  Action	IF ( #sq($runLocale)#='fr') THEN ('13. En attente de fiche de paye') ELSE ('13. Awaiting Pay Sheet')
  Action Date	[Presentation].[Case].[TIME_OUT_REASSIGNED_EST]
  End Date	CASE ?Date_Option_p? WHEN 0 THEN (#prompt('ParamEndDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date, -_day_of_week (current_date,1)) ) WHEN 2 THEN (_add_days (current_date,-1) ) END
  Begin Date	CASE ?Date_Option_p? WHEN 0 THEN ( #prompt('ParamStartDate', 'date', 'current_date')# ) WHEN 1 THEN ( _add_days (current_date,(-(_day_of_week (current_date,1)+6)) ) ) WHEN 2 THEN ( _add_days (current_date,-1) ) END
  Action Code	13

  • Detail Filters
    3
  • 

    [Presentation].[Case].[TIME_OUT_REASSIGNED_EST] between [Begin Date] and [End Date]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Main Query

  

  

  Assigned q

  

  Send For Verification q

  

  Returned from Verification q

  

  Closed q

  

  Cancelled q

  

  Verified q

  

  Rejected by Verifier q

  

  Sent to Pending q

  

  Final_Reassigned Q

  

  Awaiting Calc q

  

  Awaiting Confirm q

  

  Awaiting Off Cycle q

  

  Awaiting Pay Sheet q

  • Projected Data Items
  • Case Number
  • PRI
  • Processing Centre
  • Age
  • Date Open
  • BF Date
  • Date Assigned
  • Date Closed
  • Date Cancelled
  • Work Type Name
  • Sub Type Name
  • Business Unit
  • Classification
  • Assiged Agent
  • Team Name
  • Action
  • Action Date
  • End Date
  • Begin Date
  • Action Code

  Data Items

  19

  Case Number	[Union1].[Case Number]
  PRI	[Union1].[PRI]
  Processing Centre	[Union1].[Processing Centre]
  Age	[Union1].[Age]
  Date Open	[Union1].[Date Open]
  BF Date	[Union1].[BF Date]
  Date Assigned	[Union1].[Date Assigned]
  Date Closed	[Union1].[Date Closed]
  Date Cancelled	[Union1].[Date Cancelled]
  Work Type Name	[Union1].[Work Type Name]
  Sub Type Name	[Union1].[Sub Type Name]
  Business Unit	[Union1].[Business Unit]
  Classification	[Union1].[Classification]
  Assigned Agent	[Union1].[Assiged Agent]
  Team Name	[Union1].[Team Name]
  Action	[Union1].[Action]
  Action Date	[Union1].[Action Date]
  Action Code	[Union1].[Action Code]
  Total Count	total(count([Case Number]) for report)

• 

  

  Current_Team_q

  Data Items

  3

  Team Name	[Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
  ASSIGNEE_CURR_ID	[Presentation].[VW_STG_CURRENT_TEAM].[ASSIGNEE_CURR_ID]
  Action Code	9

  • Detail Filters
    2
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] in (?Security Group_p?)
    
• 

  

  User_for_Reassigned q

  Data Items

  4

  User DBID	[Presentation].[VW_STG_USERS].[DBID]
  Login Name	[Presentation].[VW_STG_USERS].[LOGIN_NAME]
  Action Code	9
  Full Name	[Presentation].[VW_STG_USERS].[FULLNAME]

  • Detail Filters
    1
  • 

    [Action Code] in (?Action Query Type Prompt?)
    
• 

  

  Reassigned_User_Group_Query

  

  [Current_Team_q].[ASSIGNEE_CURR_ID] = [User_for_Reassigned q].[User DBID]

  

  Current_Team_q

  1:1

  

  User_for_Reassigned q

  1:1

  Data Items

  3

  Login Name	[User_for_Reassigned q].[Login Name]
  Team Name	[Current_Team_q].[Team Name]
  Full Name	[User_for_Reassigned q].[Full Name]

• 

  

  Final_Reassigned Q

  

  [Reassigned q].[Assiged Agent] = [Reassigned_User_Group_Query].[Login Name]

  

  Reassigned q

  1:1

  

  Reassigned_User_Group_Query

  0:1

  • queryHints
  • executionMethod: sequential
  • queryProcessing: databaseOnly

  Data Items

  20

  Case Number	[Reassigned q].[Case Number]
  PRI	[Reassigned q].[PRI]
  Processing Centre	[Reassigned q].[Processing Centre]
  Age	[Reassigned q].[Age]
  Date Open	[Reassigned q].[Date Open]
  BF Date	[Reassigned q].[BF Date]
  Date Assigned	[Reassigned q].[Date Assigned]
  Date Closed	[Reassigned q].[Date Closed]
  Date Cancelled	[Reassigned q].[Date Cancelled]
  Work Type Name	[Reassigned q].[Work Type Name]
  Sub Type Name	[Reassigned q].[Sub Type Name]
  Business Unit	[Reassigned q].[Business Unit]
  Classification	[Reassigned q].[Classification]
  Assiged Agent	[Reassigned_User_Group_Query].[Full Name]
  Team Name	CASE(char_length ([Reassigned_User_Group_Query].[Team Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Reassigned_User_Group_Query].[Team Name],22,char_length ([Reassigned_User_Group_Query].[Team Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Reassigned_User_Group_Query].[Team Name],22,char_length ([Reassigned_User_Group_Query].[Team Name]) ) WHEN 3 THEN 'Team '+''+substring ([Reassigned_User_Group_Query].[Team Name],22,char_length ([Reassigned_User_Group_Query].[Team Name]) ) END
  Action	[Reassigned q].[Action]
  Action Date	[Reassigned q].[Action Date]
  End Date	[Reassigned q].[End Date]
  Begin Date	[Reassigned q].[Begin Date]
  Action Code	[Reassigned q].[Action Code]

  • Detail Filters
    1
  • 

    [Reassigned_User_Group_Query].[Team Name] in (?Security Group_p?)
    

Layouts