MATERIALIZED VIEW: APPS.HRI_MDP_WRKFC_ORGMGR_MV

Source

SELECT /* 12.0: bug#4526784 */  orgmgr_id                                 ORGMGR_ID      
      ,effective_start_date                      EFFECTIVE_START_DATE  
      
      ,NVL((LEAD(effective_start_date, 1) OVER
       (PARTITION BY orgmgr_id
                    ,organization_id
        ORDER BY effective_start_date)) - 1,
            to_date('31-12-4712','dd-mm-yyyy'))  EFFECTIVE_END_DATE
            
      ,organization_id                           ORGANIZATION_ID
      
      ,SUM(total_headcount) OVER
        (PARTITION BY orgmgr_id
                     ,organization_id
             ORDER BY effective_start_date
                RANGE UNBOUNDED PRECEDING)
                            AS total_headcount                              
      ,SUM(dr_headcount) OVER
         (PARTITION BY orgmgr_id
                      ,organization_id
             ORDER BY effective_start_date
                RANGE UNBOUNDED PRECEDING)
                            AS direct_headcount
FROM
 (SELECT orgmgr_id,
         effective_start_date,
         organization_id,
         SUM(total_headcount) total_headcount,
         SUM(dr_headcount)    dr_headcount
    FROM hri_map_wrkfc_orgmgr_ct
   WHERE (total_headcount <> 0 OR dr_headcount <> 0)
   GROUP BY orgmgr_id, 
            effective_start_date, 
            organization_id)