Deployments
UAT_PCR_Deployment
Reports
Report Name: Interim States Extract Report
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
    Query-Main
    Data Items
    53
    DBID
    [Presentation].[Case].[DBID]
    Case Number
    [Presentation].[Case].[ID]
    Processing Centre
    '="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
    Processing Centre-for header
    [Presentation].[Case].[PROCESSING_CENTRE]
    Age
    [Presentation].[Case].[CMT_AGE]
    Date Open
    '="' + cast(_add_hours ( [Presentation].[Case].[DATE_OPEN],-4 ),varchar(19)) + '"'
    Date Open-for header
    [Presentation].[Case].[DATE_OPEN]
    BF Date
    '="' + cast(_add_hours ( [Presentation].[Case].[DATE_BF],-4 ),varchar(19)) + '"'
    BF Date-for header
    [Presentation].[Case].[DATE_BF]
    Date Effective From
    '="' + cast(_add_hours ([Presentation].[Case].[DATE_EFFECTIVE_FROM],-4 ),varchar(19)) + '"'
    Date Effective From-for header
    [Presentation].[Case].[DATE_EFFECTIVE_FROM]
    Date Effective To
    '="' + cast(_add_hours ([Presentation].[Case].[DATE_EFFECTIVE_TO],-4 ),varchar(19)) + '"'
    Date Effective To-for header
    [Presentation].[Case].[DATE_EFFECTIVE_TO]
    Work Type Name
    [Presentation].[Work Type].[NAME]
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
    Dept.
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Employee Sub-Category
    [Presentation].[Case].[CLASSIFICATION]
    Assigned To
    [Presentation].[Case].[ASSIGNEE_CURR]
    Group Name
    [Presentation].[VW_STG_GROUPS].[NAME]
    Awaiting Calc
    [Presentation].[Case].[AWAITING_CALC]
    Date Awaiting Calc Selected-for filter
    cast(cast (_add_hours ([Presentation].[Case].[TIME_IN_ASSIGNED], -4), timestamp), date)
    Date Awaiting Calc Selected-for header
    [Presentation].[Case].[TIME_IN_ASSIGNED]
    Date Awaiting Calc Selected-for rpt
    '="' + cast(cast (_add_hours ([Presentation].[Case].[TIME_IN_ASSIGNED], -4), timestamp),varchar(19)) + '"'
    Awaiting Confirm
    [Presentation].[Case].[AWAITING_CONFIRM]
    Date Awaiting Confirm Selected-for filter
    cast(cast (_add_hours ([Presentation].[Case].[TIME_OUT_ASSIGNED], -4), timestamp), date)
    Date Awaiting Confirm Selected-for header
    [Presentation].[Case].[TIME_OUT_ASSIGNED]
    Date Awaiting Confirm Selected-for rpt
    '="' + cast(cast (_add_hours ([Presentation].[Case].[TIME_OUT_ASSIGNED], -4), timestamp),varchar(19)) + '"'
    Awaiting pay sheet creation
    [Presentation].[Case].[AWAITING_PAY_SHEET_CREAT]
    Date Awaiting Pay Sheet Creation Selected-for filter
    cast(cast (_add_hours ([Presentation].[Case].[TIME_OUT_REASSIGNED], -4), timestamp), date)
    Date Awaiting Pay Sheet Creation Selected-for header
    [Presentation].[Case].[TIME_OUT_REASSIGNED]
    Date Awaiting Pay Sheet Creation Selected-for rpt
    '="' + cast(cast (_add_hours ([Presentation].[Case].[TIME_OUT_REASSIGNED], -4), timestamp),varchar(19)) + '"'
    Awaiting Off Cycle
    [Presentation].[Case].[AWAITING_OFF_CYCLE]
    Date Awaiting Off Cycle Selected-for filter
    cast(cast (_add_hours ([Presentation].[Case].[TIME_IN_REASSIGNED], -4), timestamp), date)
    Date Awaiting Off Cycle Selected-for header
    [Presentation].[Case].[TIME_IN_REASSIGNED]
    Date Awaiting Off Cycle Selected-for rpt
    '="' + cast(cast (_add_hours ([Presentation].[Case].[TIME_IN_REASSIGNED], -4), timestamp),varchar(19)) + '"'
    Waiting on documents
    [Presentation].[Case].[WAITING_ON_DOCUMENTS]
    Date Waiting on Documents Selected-for filter
    cast(cast (_add_hours ([Presentation].[Case].[TIME_IN_READY_TO_VERIFY], -4), timestamp), date)
    Date Waiting on Documents Selected-for header
    [Presentation].[Case].[TIME_IN_READY_TO_VERIFY]
    Date Waiting on Documents Selected-for rpt
    '="' + cast(cast (_add_hours ([Presentation].[Case].[TIME_IN_READY_TO_VERIFY], -4), timestamp),varchar(19)) + '"'
    Waiting on prior case completion
    [Presentation].[Case].[WAITING_ON_PRIOR_CASE_COM]
    Date Waiting on Prior Case Completion Selected-for filter
    cast(cast (_add_hours ([Presentation].[Case].[TIME_OUT_READY_TO_VERIFY], -4), timestamp), date)
    Date Waiting on Prior Case Completion Selected-for header
    [Presentation].[Case].[TIME_OUT_READY_TO_VERIFY]
    Date Waiting on Prior Case Completion Selected-for rpt
    '="' + cast(cast (_add_hours ([Presentation].[Case].[TIME_OUT_READY_TO_VERIFY], -4), timestamp),varchar(19)) + '"'
    Waiting on Comp Sector
    [Presentation].[Case].[WAITING_ON_COMP_SECTOR]
    Date Waiting on Comp Sector Selected-for filter-for filter
    cast(cast (_add_hours ([Presentation].[Case].[TIME_OUT_VERIFIED], -4), timestamp), date)
    Date Waiting on Comp Sector Selected-for header
    [Presentation].[Case].[TIME_OUT_VERIFIED]
    Date Waiting on Comp Sector Selected-for rpt
    '="' + cast(cast (_add_hours ([Presentation].[Case].[TIME_OUT_VERIFIED], -4), timestamp),varchar(19)) + '"'
    Waiting on HR
    [Presentation].[Case].[WAITING_ON_HR]
    Date Waiting on HR Selected-for filter
    cast(cast (_add_hours ([Presentation].[Case].[TIME_IN_VERIFIED], -4), timestamp), date)
    Date Waiting on HR Selected-for header
    [Presentation].[Case].[TIME_IN_VERIFIED]
    Date Waiting on HR Selected-for rpt
    '="' + cast(cast (_add_hours ([Presentation].[Case].[TIME_IN_VERIFIED], -4), timestamp),varchar(19)) + '"'
    Current Date Stamp-1day
    (_add_days ( cast(getdate(), date), - 1 ))
    label
    Current Date Stamp
    Current Date Stamp-2day
    (_add_days ( cast(getdate(), date), - 2 ))
    • Detail Filters
      4
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      Case
      when ?Param-Date-Filter? = '1'
      then
      Case
      when (?Param-Date-Daily? = 1)
      then ([Date Awaiting Calc Selected-for filter] in_range ?Param-Date-Range?)
      when (?Param-Date-Daily? = 2)
      then ([Date Awaiting Calc Selected-for filter] = [Current Date Stamp-1day])
      when (?Param-Date-Daily? = 3)
      then ([Date Awaiting Calc Selected-for filter] = [Current Date Stamp-2day])
      end
      when ?Param-Date-Filter? = '2'
      then
      Case
      when (?Param-Date-Daily? = 1)
      then ([Date Awaiting Confirm Selected-for filter] in_range ?Param-Date-Range?)
      when (?Param-Date-Daily? = 2)
      then ([Date Awaiting Confirm Selected-for filter] = [Current Date Stamp-1day])
      when (?Param-Date-Daily? = 3)
      then ([Date Awaiting Confirm Selected-for filter] = [Current Date Stamp-2day])
      end
      when ?Param-Date-Filter? = '3'
      then
      Case
      when (?Param-Date-Daily? = 1)
      then ([Date Awaiting Pay Sheet Creation Selected-for filter] in_range ?Param-Date-Range?)
      when (?Param-Date-Daily? = 2)
      then ([Date Awaiting Pay Sheet Creation Selected-for filter] = [Current Date Stamp-1day])
      when (?Param-Date-Daily? = 3)
      then ([Date Awaiting Pay Sheet Creation Selected-for filter] = [Current Date Stamp-2day])
      end
      when ?Param-Date-Filter? = '4'
      then
      Case
      when (?Param-Date-Daily? = 1)
      then ([Date Awaiting Off Cycle Selected-for filter] in_range ?Param-Date-Range?)
      when (?Param-Date-Daily? = 2)
      then ([Date Awaiting Off Cycle Selected-for filter] = [Current Date Stamp-1day])
      when (?Param-Date-Daily? = 3)
      then ([Date Awaiting Off Cycle Selected-for filter] = [Current Date Stamp-2day])
      end
      when ?Param-Date-Filter? = '5'
      then
      Case
      when (?Param-Date-Daily? = 1)
      then ([Date Waiting on Documents Selected-for filter] in_range ?Param-Date-Range?)
      when (?Param-Date-Daily? = 2)
      then ([Date Waiting on Documents Selected-for filter] = [Current Date Stamp-1day])
      when (?Param-Date-Daily? = 3)
      then ([Date Waiting on Documents Selected-for filter] = [Current Date Stamp-2day])
      end
      when ?Param-Date-Filter? = '6'
      then
      Case
      when (?Param-Date-Daily? = 1)
      then ([Date Waiting on Prior Case Completion Selected-for filter] in_range ?Param-Date-Range?)
      when (?Param-Date-Daily? = 2)
      then ([Date Waiting on Prior Case Completion Selected-for filter] = [Current Date Stamp-1day])
      when (?Param-Date-Daily? = 3)
      then ([Date Waiting on Prior Case Completion Selected-for filter] = [Current Date Stamp-2day])
      end
      when ?Param-Date-Filter? = '7'
      then
      Case
      when (?Param-Date-Daily? = 1)
      then ([Date Waiting on Comp Sector Selected-for filter-for filter] in_range ?Param-Date-Range?)
      when (?Param-Date-Daily? = 2)
      then ([Date Waiting on Comp Sector Selected-for filter-for filter] = [Current Date Stamp-1day])
      when (?Param-Date-Daily? = 3)
      then ([Date Waiting on Comp Sector Selected-for filter-for filter] = [Current Date Stamp-2day])
      end
      when ?Param-Date-Filter? = '8'
      then
      Case
      when (?Param-Date-Daily? = 1)
      then ([Date Waiting on HR Selected-for filter] in_range ?Param-Date-Range?)
      when (?Param-Date-Daily? = 2)
      then ([Date Waiting on HR Selected-for filter] = [Current Date Stamp-1day])
      when (?Param-Date-Daily? = 3)
      then ([Date Waiting on HR Selected-for filter] = [Current Date Stamp-2day])
      end
      end
    • Disabled
      [Presentation].[VW_STG_PARENT_CHILD_LINKS].[PARENT_FIELDDEF_ID] = 16777321
    • Required
      #/* Compensation Advisor FILTER
      JIRA Defect: BICCABC-2176
      By: R. Peters
      Change Details:
      Replaced original hard-coded FILTER Expression
      ie.
      [Group Name] in
      ('Compensation-Advisor-1','Compensation-Advisor-2',
      'Compensation-Advisor-3', 'Compensation-Advisor-4',
      ...
      'Compensation-Advisor-99', 'Compensation-Advisor-100',
      'Compensation-Advisor-OGD')
      With FILTER from package:
      [Presentation].[Compensation Advisor]
      */#
      [Presentation].[Compensation Advisor]

  • Query
    Load Last Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    Query-Rpt-sub-title
    Data Items
    1
    Data Item
    Case
    when ?Param-Date-Filter? = '1' and (#sq($runLocale)# = 'en')
    then 'Date Awaiting Calc selected'
    when ?Param-Date-Filter? = '1' and (#sq($runLocale)# = 'fr')
    then 'Date en attente de calcul selectionner'
    when ?Param-Date-Filter? = '2' and (#sq($runLocale)# = 'en')
    then 'Date Awaiting Calc selected'
    when ?Param-Date-Filter? = '2' and (#sq($runLocale)# = 'fr')
    then 'En attente de la confirmation selectionner'
    when ?Param-Date-Filter? = '3' and (#sq($runLocale)# = 'en')
    then 'Date Awaiting Pay Sheet Creation selected'
    when ?Param-Date-Filter? = '3' and (#sq($runLocale)# = 'fr')
    then 'En attente de la création d’une feuille de paye selectionner'
    when ?Param-Date-Filter? = '4' and (#sq($runLocale)# = 'en')
    then 'Date Awaiting off Cycle selected'
    when ?Param-Date-Filter? = '4' and (#sq($runLocale)# = 'fr')
    then 'En attente de la période hors cycle'
    when ?Param-Date-Filter? = '5' and (#sq($runLocale)# = 'en')
    then 'Date Awaiting on Documents selected'
    when ?Param-Date-Filter? = '5' and (#sq($runLocale)# = 'fr')
    then 'En attente des documents selectionner'
    when ?Param-Date-Filter? = '6' and (#sq($runLocale)# = 'en')
    then 'Date Awaiting on prior Completion selected'
    when ?Param-Date-Filter? = '6' and (#sq($runLocale)# = 'fr')
    then 'En attente du traitement d’un cas précédent selectionner'
    when ?Param-Date-Filter? = '7' and (#sq($runLocale)# = 'en')
    then 'Date Awaiting on Comp Sector selected'
    when ?Param-Date-Filter? = '7' and (#sq($runLocale)# = 'fr')
    then 'En attente du Secteur de la rémunération selectionner'
    when ?Param-Date-Filter? = '8' and (#sq($runLocale)# = 'en')
    then 'Date Awaiting on HR selected'
    when ?Param-Date-Filter? = '8' and (#sq($runLocale)# = 'fr')
    then 'En attente des RH selectionner'
    end
Layouts