Deployments
UAT_PCR_Deployment
Reports
Report Name: Daily Pay Centre Category Updates
Overview

  • Queries: 5

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 5
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    Load Last Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    Reporting Date Range
    Data Items
    3
    Begin Date
    IF (?Date_Option_p? = '0' ) THEN
    ( #prompt('ParamStartDate', 'date', 'current_date')# )
    ELSE
    ( IF ( _day_of_week (current_date,1) > 1 ) THEN
    (_add_days (current_date, -1) )
    ELSE
    (_add_days (current_date, -3) )
    )
    End Date
    IF (?Date_Option_p? = '0' ) THEN
    (#prompt('ParamEndDate', 'date', 'current_date')# )
    ELSE
    (IF ( _day_of_week (current_date,1) > 1 ) THEN
    (_add_days (current_date, -1) )
    ELSE
    (_add_days (current_date, -1) )
    )
    WeekNumber
    _day_of_week (current_date,1)
  • Query
    Work In Process Query
    Data Items
    13
    Begin Date
    IF (?Date_Option_p? = '0' ) THEN
    ( #prompt('ParamStartDate', 'date', 'current_date')# )
    ELSE
    ( IF ( _day_of_week (current_date,1) > 1 ) THEN
    (_add_days (current_date, -1) )
    ELSE
    (_add_days (current_date, -3) )
    )
    End Date
    IF (?Date_Option_p? = '0' ) THEN
    (#prompt('ParamEndDate', 'date', 'current_date')# )
    ELSE
    (IF ( _day_of_week (current_date,1) > 1 ) THEN
    (_add_days (current_date, -1) )
    ELSE
    (_add_days (current_date, -1) )
    )
    BF Date
    [Presentation].[Case].[DATE_BF_EST]
    Case Count
    [Presentation].[Case].[Case Count]
    Aggregate
    count count
    Count of Unique PRIs
    count(distinct [Presentation].[Case].[PRI] for report)
    Active_or_Unassigned
    CASE
    WHEN [Presentation].[State].[NAME] in ('Pending','Assigned','Verified', 'Ready_To_Verify') THEN (IF ( #sq($runLocale)#='fr') THEN
    ( 'Actif' )
    ELSE
    ( 'Active' )
    )
    ELSE (IF ( #sq($runLocale)#='fr') THEN
    ( 'Non attribué' )
    ELSE
    ( 'Unassigned') )
    END
    Age Group
    [Presentation].[Case].[Age Group]
    Sub Type Name
    IF ( [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-176', 'ST-254') ) THEN
    ( [Presentation].[Sub Type].[NAME]+' ('+[Presentation].[Sub Type].[SUB_TYPE_CODE] +')' )
    ELSE
    ( [Presentation].[Sub Type].[NAME] )
    Average Age
    average([Presentation].[Case].[CMT_AGE] for [Sub Type Name])
    Oldest Case
    maximum (distinct [Presentation].[Case].[CMT_AGE] for [Sub Type Name])
    Total Active and Awaiting Calc Cases
    Total(CASE
    WHEN (
    [Presentation].[State].[NAME] in ('Pending','Assigned','Verified', 'Ready_To_Verify')
    and [Presentation].[Case].[AWAITING_CALC] = 'Yes/Oui'
    )
    THEN 1
    ELSE 0
    END for report)
    Total Active and Awaiting Confirm Cases
    Total(CASE
    WHEN (
    [Presentation].[State].[NAME] in ('Pending','Assigned','Verified', 'Ready_To_Verify')
    and [Presentation].[Case].[AWAITING_CONFIRM] = 'Yes/Oui'
    )
    THEN 1
    ELSE 0
    END for report)
    Total Ready to Verify Cases
    Total(CASE
    WHEN
    [Presentation].[State].[NAME] = 'Ready_To_Verify'
    THEN 1
    ELSE 0
    END for report)
    • Detail Filters
      5
    • Required
      (
      ?Report_Selection_p? = '1' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-177','ST-037','ST-256','ST-260','ST-604','ST-185','ST-520','ST-178','ST-299','ST-321','ST-605','ST-609','ST-270','ST-608','ST-272','ST-111','ST-257',
      'ST-261','ST-519','ST-546','ST-004','ST-983','ST-984')
      )
      or
      (
      ?Report_Selection_p? = '2' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-043','ST-534','ST-327','ST-330','ST-045','ST-536','ST-046','ST-331','ST-184','ST-537','ST-535','ST-108','ST-264','ST-180','ST-263','ST-522','ST-977','ST-044')
      )
      or
      (
      ?Report_Selection_p? = '3' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-248','ST-249','ST-529','ST-342','ST-077','ST-252','ST-343','ST-109','ST-110','ST-544','ST-545','ST-173','ST-938')
      )
      or
      (
      ?Report_Selection_p? = '4' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-087','ST-239')
      )
      or
      (
      ?Report_Selection_p? = '5' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-538','ST-242','ST-170','ST-532','ST-531','ST-533','ST-530','ST-251','ST-176','ST-254','ST-540')
      )
    • Required
      [Presentation].[State].[NAME] in ('Invalid', 'Open', 'Reassigned','Assigned', 'Pending', 'Ready_To_Verify', 'Verified', 'Triage_Follow-Up')
      or
      ([Presentation].[State].[NAME] ='Bring_Forward'
      and
      [Presentation].[Case].[DATE_BF_EST] <= [End Date])
    • Required
      (?Report_Selection_p? is not null )and (?Embedded_to_Email_body_p? is not null)
    • Required
      [Presentation].[Compensation Advisor]
      or [Presentation].[VW_STG_GROUPS].[NAME] is null
    • Required
      [Presentation].[Case].[DBID] <> 0
  • Query
    Intake Query
    Data Items
    4
    Begin Date
    IF (?Date_Option_p? = '0' ) THEN
    ( #prompt('ParamStartDate', 'date', 'current_date')# )
    ELSE
    ( IF ( _day_of_week (current_date,1) > 1 ) THEN
    (_add_days (current_date, -1) )
    ELSE
    (_add_days (current_date, -3) )
    )
    End Date
    IF (?Date_Option_p? = '0' ) THEN
    (#prompt('ParamEndDate', 'date', 'current_date')# )
    ELSE
    (IF ( _day_of_week (current_date,1) > 1 ) THEN
    (_add_days (current_date, -1) )
    ELSE
    (_add_days (current_date, -1) )
    )
    Total Number of Intake Cases
    count([Presentation].[Case].[Case Count] for report)
    Total Intake Cases for Maternity-Paternity
    Total(CASE
    WHEN [Presentation].[Sub Type].[SUB_TYPE_CODE] = 'ST-529'
    THEN 1
    ELSE 0
    END for report)
    • Detail Filters
      3
    • Required
      (
      ?Report_Selection_p? = '1' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-177','ST-037','ST-256','ST-260','ST-604','ST-185','ST-520','ST-178','ST-299','ST-321','ST-605','ST-609','ST-270','ST-608','ST-272','ST-111','ST-257',
      'ST-261','ST-519','ST-546','ST-004','ST-983','ST-984')
      )
      or
      (
      ?Report_Selection_p? = '2' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-043','ST-534','ST-327','ST-330','ST-045','ST-536','ST-046','ST-331','ST-184','ST-537','ST-535','ST-108','ST-264','ST-180','ST-263','ST-522','ST-977','ST-044')
      )
      or
      (
      ?Report_Selection_p? = '3' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-248','ST-249','ST-529','ST-342','ST-077','ST-252','ST-343','ST-109','ST-110','ST-544','ST-545','ST-173','ST-938')
      )
      or
      (
      ?Report_Selection_p? = '4' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-087','ST-239')
      )
      or
      (
      ?Report_Selection_p? = '5' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-538','ST-242','ST-170','ST-532','ST-531','ST-533','ST-530','ST-251','ST-176','ST-254','ST-540')
      )
    • Required
      ([Presentation].[Case].[DATE_OPEN_EST] between [Begin Date] and [End Date]
      and [Presentation].[Case].[DATE_BF_EST] is null)
      or
      [Presentation].[Case].[DATE_BF_EST] = [End Date]
    • Required
      [Presentation].[Case].[DBID] <> 0
  • Query
    Production Query
    Data Items
    10
    Assigned
    total(count(distinct case
    when ([Presentation].[Case].[DATE_ASSIGNED_EST] between [Begin Date] and [End Date]) then [Presentation].[Case].[ID]
    else null
    end) for report)
    Sent for Verification
    total(count( distinct CASE
    WHEN (
    ([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')
    )
    THEN [Presentation].[Case].[DBID]
    ELSE null
    END
    ) for report)
    Returned from Verification
    total(count(distinct CASE
    WHEN (
    ([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')
    )
    THEN [Presentation].[Case].[DBID]
    ELSE null
    END
    ) for report)
    Closed
    total(count(distinct Case
    when ([Presentation].[Case].[DATE_CLOSED_EST] between [Begin Date] and [End Date]) then [Presentation].[Case].[ID]
    else null
    end ) for report)
    Cancelled
    total(count(distinct Case
    when ([Presentation].[Case].[DATE_CANCELLED_EST] between [Begin Date] and [End Date]) then [Presentation].[Case].[ID]
    else null
    end ) for report)
    Verified
    total(count(distinct Case
    when ([Presentation].[Case].[VERIFIED_DATE_EST] between [Begin Date] and [End Date]) then [Presentation].[Case].[ID]
    else null
    end ) for report)
    Rejected by Verifier
    total(count(distinct CASE
    WHEN (
    ([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')
    )
    THEN [Presentation].[Case].[DBID]
    ELSE null
    END ) for report)
    Sent to Pending
    total(count( distinct CASE
    WHEN (
    ([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')
    )
    THEN [Presentation].[Case].[DBID]
    ELSE null
    END
    ) for report)
    Begin Date
    IF (?Date_Option_p? = '0' ) THEN
    ( #prompt('ParamStartDate', 'date', 'current_date')# )
    ELSE
    ( IF ( _day_of_week (current_date,1) > 1 ) THEN
    (_add_days (current_date, -1) )
    ELSE
    (_add_days (current_date, -3) )
    )
    End Date
    IF (?Date_Option_p? = '0' ) THEN
    (#prompt('ParamEndDate', 'date', 'current_date')# )
    ELSE
    (IF ( _day_of_week (current_date,1) > 1 ) THEN
    (_add_days (current_date, -1) )
    ELSE
    (_add_days (current_date, -1) )
    )
    • Detail Filters
      2
    • Required
      [Presentation].[Compensation Advisor]
    • Required
      (
      ?Report_Selection_p? = '1' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-177','ST-037','ST-256','ST-260','ST-604','ST-185','ST-520','ST-178','ST-299','ST-321','ST-605','ST-609','ST-270','ST-608','ST-272','ST-111','ST-257',
      'ST-261','ST-519','ST-546','ST-004','ST-983','ST-984')
      )
      or
      (
      ?Report_Selection_p? = '2' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-043','ST-534','ST-327','ST-330','ST-045','ST-536','ST-046','ST-331','ST-184','ST-537','ST-535','ST-108','ST-264','ST-180','ST-263','ST-522','ST-977','ST-044')
      )
      or
      (
      ?Report_Selection_p? = '3' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-248','ST-249','ST-529','ST-342','ST-077','ST-252','ST-343','ST-109','ST-110','ST-544','ST-545','ST-173','ST-938')
      )
      or
      (
      ?Report_Selection_p? = '4' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-087','ST-239')
      )
      or
      (
      ?Report_Selection_p? = '5' and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in
      ('ST-538','ST-242','ST-170','ST-532','ST-531','ST-533','ST-530','ST-251','ST-176','ST-254','ST-540')
      )
Layouts