Deployments
UAT_PCR_Deployment
Reports
Report Name: List of Cases over 30 Days - ESDC
Overview

  • Queries: 3

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 3
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    Query1
    Data Items
    20
    Date Open
    [Presentation].[Case].[DATE_OPEN]
    Case Number
    [Presentation].[Case].[ID]
    PRI
    [Presentation].[Case].[PRI]
    Case Group Type
    [Presentation].[Case].[Case Group Type]
    Work Type Name
    [Presentation].[Work Type].[NAME]
    Work Type Code
    [Presentation].[Work Type].[WORK_TYPE_CODE]
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
    Sub Type Code
    [Presentation].[Sub Type].[SUB_TYPE_CODE]
    State Name
    [Presentation].[State].[NAME]
    Date Received
    [Presentation].[Case].[DATE_RECEIVED]
    Date Effective From
    [Presentation].[Case].[DATE_EFFECTIVE_FROM]
    Pending Reason
    [Presentation].[Case].[PENDING_REASON]
    Age
    [Presentation].[Case].[CMT_AGE]
    Created By
    [Presentation].[Case].[CREATED_BY]
    Current date Run
    current_date
    Case Group Type Orginal
    CASE
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-ACTING%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-ACTING%') THEN ('Acting')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-BENEFITS%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-BENEFITS%') THEN ('Benefits')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-CHGINEMPL%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-CHGINEMPL%') THEN ('Change In Employment')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-DEDUCTIONS%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-DEDUCTIONS%') THEN ('Deductions')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-ENTL%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-ENTL%') THEN('Entitlements')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-LWOP%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-LWOP%') THEN ('Leave without Pay')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-LWP%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-LWP%') THEN ('Leave with Pay')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-LEAVE%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-LEAVE%') THEN ('Leave')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-OVERPAY%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-OVERPAY%') THEN ('Overpayment')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-TERM%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-TERM%') THEN ('Termination')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-TRANSFER%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-TRANSFER%') THEN ('Transfer')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-PROMOTION%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-PROMOTION%') THEN ('Promotion')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-PAYINC%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-PAYINC%') THEN ('Pay Increments')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-TIMESHEETS%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-TIMESHEETS%') THEN ('Timesheets')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-CASHPAY%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-CASHPAY%') THEN ('Cash Payment')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-DD%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-DD%') THEN ('Direct Deposit')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-EDP%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-EDP%') THEN ('Extra Duty Pay')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-MATANE%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-MATANE%') THEN ('EDP - MATANE')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-ESA%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-ESA%') THEN ('Emergency Salary Advance')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-GE%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-GE%') THEN ('General Enquiries')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-HIRE%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-HIRE%') THEN ('Hire')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-ACTING%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-ACTING%') THEN ('Maternity/Paternity')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-REHIRE%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-REHIRE%') THEN ('Rehire')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-RETOS%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-RETOS%') THEN ('RETOS')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-CHQ%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-CHQ%') THEN ('Cheque')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-DATAINTEGRITY%') or([Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-DATAINTEGRITY%') THEN ('Data Integrity')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-TEACHERS%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-TEACHERS%') THEN ('Teachers')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-PERFPAY%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-PERFPAY%') THEN ('Performance Pay')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-LIA%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-LIA%') THEN ('Leave with Income Averaging')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-DEMOTION%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-DEMOTION%') THEN ('Demotion')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-GARNISHMENTS%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-GARNISHMENTS%') THEN ('Garnishments')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-REHAB%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-REHAB%') THEN ('Rehab')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-ROE%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-ROE%') THEN ('Record of Employment')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-REVIEWOFPAY%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-REVIEWOFPAY%') THEN ('Review of Pay File')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-POSCHG%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-POSCHG%') THEN ('Position Change')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-SALARYPROTCT%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-SALARYPROTCT%') THEN ('Salary Protected')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-DBTCROWN%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-DBTCROWN%') THEN ('Debt to Crown')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-DATACHG%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-DATACHG%') THEN ('Data Change')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-THRDPRTY%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-THRDPRTY%') THEN ('Third Party')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-GRVSUPPRT%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-GRVSUPPRT%') THEN ('Grievance Support')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-COMPLAINT%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-COMPLAINT%') THEN ('Complaint')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-INCOMPLETE%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-INCOMPLETE%') THEN ('Incomplete')
    WHEN ( [Other Type]<> 0 and [Bundle] like '%CT-PRECONV%') or( [Other Type] is Null or [Other Type] = 0) and ([Bundle] like '%CT-PRECONV%') THEN ('Preconversion')
    WHEN ( [Work Type Code]= 'WT-052' or [Work Type Code]= 'WT-053') THEN ('Data Integrity')
    WHEN ( [Sub Type Code] is null) THEN ('Incomplete')
    ELSE ('Miscellaneous')
    END
    Case Group Type1 test French
    CASE
    WHEN ([Bundle] like '%CT-ACTING%') THEN ('Acting')
    WHEN ([Bundle] like '%CT-BENEFITS%') THEN ('Benefits')
    WHEN ([Bundle] like '%CT-CHGINEMPL%') THEN ('Change In Employment')
    WHEN ([Bundle] like '%CT-DEDUCTIONS%') THEN ('Deductions')
    WHEN ([Bundle] like '%CT-ENTL%') THEN('Entitlements')
    WHEN ([Bundle] like '%CT-LWOP%') THEN ('Leave without Pay')
    WHEN ([Bundle] like '%CT-LWP%') THEN ('Leave with Pay')
    WHEN ([Bundle] like '%CT-LEAVE%') THEN ('Leave')
    WHEN ([Bundle] like '%CT-OVERPAY%') THEN ('Overpayment')
    WHEN ([Bundle] like '%CT-TERM%') THEN ('Termination')
    WHEN ([Bundle] like '%CT-TRANSFER%') THEN ('Transfer')
    WHEN ([Bundle] like '%CT-PROMOTION%') THEN ('Promotion')
    WHEN ([Bundle] like '%CT-PAYINC%') THEN ('Pay Increments')
    WHEN ([Bundle] like '%CT-TIMESHEETS%') THEN ('Timesheets')
    WHEN ([Bundle] like '%CT-CASHPAY%') THEN ('Cash Payment')
    WHEN ([Bundle] like '%CT-DD%') THEN ('Direct Deposit')
    WHEN ([Bundle] like '%CT-EDP%') THEN ('Extra Duty Pay')
    WHEN ([Bundle] like '%CT-MATANE%') THEN ('EDP - MATANE')
    WHEN ([Bundle] like '%CT-ESA%') THEN ('Emergency Salary Advance')
    WHEN ([Bundle] like '%CT-GE%') THEN ('General Enquiries')
    WHEN ([Bundle] like '%CT-HIRE%') THEN ('Hire')
    WHEN ([Bundle] like '%CT-ACTING%') THEN ('Maternity/Paternity')
    WHEN ([Bundle] like '%CT-REHIRE%') THEN ('Rehire')
    WHEN ([Bundle] like '%CT-RETOS%') THEN ('RETOS')
    WHEN ([Bundle] like '%CT-CHQ%') THEN ('Cheque')
    WHEN ([Bundle] like '%CT-DATAINTEGRITY%') THEN ('Data Integrity')
    WHEN ([Bundle] like '%CT-TEACHERS%') THEN ('Teachers')
    WHEN ([Bundle] like '%CT-PERFPAY%') THEN ('Performance Pay')
    WHEN ([Bundle] like '%CT-LIA%') THEN ('Leave with Income Averaging')
    WHEN ([Bundle] like '%CT-DEMOTION%') THEN ('Demotion')
    WHEN ([Bundle] like '%CT-GARNISHMENTS%') THEN ('Garnishments')
    WHEN ([Bundle] like '%CT-REHAB%') THEN ('Rehab')
    WHEN ([Bundle] like '%CT-ROE%') THEN ('Record of Employment')
    WHEN ([Bundle] like '%CT-REVIEWOFPAY%') THEN ('Review of Pay File')
    WHEN ([Bundle] like '%CT-POSCHG%') THEN ('Position Change')
    WHEN ([Bundle] like '%CT-SALARYPROTCT%') THEN ('Salary Protected')
    WHEN ([Bundle] like '%CT-DBTCROWN%') THEN ('Debt to Crown')
    WHEN ([Bundle] like '%CT-DATACHG%') THEN ('Data Change')
    WHEN ([Bundle] like '%CT-THRDPRTY%') THEN ('Third Party')
    WHEN ([Bundle] like '%CT-GRVSUPPRT%') THEN ('Grievance Support')
    WHEN ([Bundle] like '%CT-COMPLAINT%') THEN ('Complaint')
    WHEN ([Bundle] like '%CT-INCOMPLETE%') THEN ('Incomplete')
    WHEN ([Bundle] like '%CT-PRECONV%') THEN ('Preconversion')
    WHEN ( [Work Type Code]= 'WT-052' or [Work Type Code]= 'WT-053') THEN ('Data Integrity')
    WHEN ( [Sub Type Code] is null) THEN ('Incomplete')
    ELSE ('Miscellaneous')
    END
    Bundle
    [Presentation].[Other Type].[BUNDLE]
    Other Type
    [Presentation].[Case].[OTHER_TYPE]
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    label
    Department
    • Detail Filters
      5
    • Optional
      [Age] > 30 and [Age] is not missing
    • Optional
      [Work Type Code] not in ('WT-041','WT-054','WT-007','WT-053','WT-052','WT-055')
    • Optional
      [State Name] in ('Assigned', 'Open','Reassigned', 'Triage_Follow-Up', 'Pending', 'Ready_To_Verify')
    • Optional
      ([Created By] not like 'Worklist / Liste de travail')
      or
      (
      ([Created By] like 'Worklist / Liste de travail')
      and
      ( [Work Type Code] <> 'WT-041')
      and
      ( [Sub Type Code] in ( 'ST-274','ST-284','ST-288','ST-289','ST-280','ST-305','ST-281','ST-330','ST-282','ST-283','ST-331','ST-307','ST-287','ST-306','ST-332','ST-279','ST-278','ST-285','ST-286','ST-601','ST-603' ) ) )
    • Required
      ( [Presentation].[VW_STG_DEPARTMENT].[NAME_1] = ?para_dept?)
  • Query
    Query2
    Data Items
    1
    Department Name
    [Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    sort
    sort: ascending ascending
    • Detail Filters
      1
    • Required
      [Department Name] = 'Employment and Social Development Canada / Emploi et Développement social Canada'
  • Query
    Load Last Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
Layouts