eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.HRI_MDP_SUP_WCNT_CHG_ASG_MV

Source


SELECT  /* 12.0: bug#4526784 */
 mv.supervisor_person_id     supervisor_person_id
,mv.effective_date           effective_date
,mv.assignment_id            assignment_id
,mv.person_id                person_id
,mv.wkth_wktyp_sk_fk         wkth_wktyp_sk_fk
,SUM((mv.worker_hire_ind + mv.post_hire_asgn_start_ind) +
     (2 * mv.transfer_in_ind) +
     (3 * mv.transfer_out_ind) +
     (4 * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind)))
                             change_type_id
,1                           direct_record_ind
,COUNT(*)                    transfer_within_count
,SUM(mv.headcount_value * (mv.worker_hire_ind + mv.post_hire_asgn_start_ind))
                             hire_hdc
,SUM(mv.headcount_value * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind))
                             termination_hdc
,SUM(mv.headcount_value * mv.transfer_in_ind)
                             transfer_in_hdc
,SUM(mv.headcount_value * mv.transfer_out_ind)
                             transfer_out_hdc
,SUM(mv.headcount_value * DECODE(mv.metric_adjust_multiplier,1,1,0) * mv.summarization_rqd_chng_ind)
                             other_gains_hdc
,SUM(mv.headcount_value * DECODE(mv.metric_adjust_multiplier,1,0,1) * mv.summarization_rqd_chng_ind)
                             other_losses_hdc
,SUM(mv.headcount_value * (mv.worker_hire_ind + mv.post_hire_asgn_start_ind))
                             hire_fte
,SUM(mv.headcount_value * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind))
                             termination_fte
,SUM(mv.headcount_value * mv.transfer_in_ind)
                             transfer_in_fte
,SUM(mv.headcount_value * mv.transfer_out_ind)
                             transfer_out_fte
,mv.term_voluntary_ind       term_voluntary_ind
,mv.term_involuntary_ind     term_involuntary_ind
,'DIRECT'                    admin_row_type
,COUNT(mv.headcount_value * (mv.worker_hire_ind + mv.post_hire_asgn_start_ind))
                             admin_count_hire_hdc
,COUNT(mv.headcount_value * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind))
                             admin_count_termination_hdc
,COUNT(mv.headcount_value * mv.term_voluntary_ind)
                             admin_count_term_vol_hdc
,COUNT(mv.headcount_value * mv.term_involuntary_ind)
                             admin_count_term_invol_hdc
,COUNT(mv.headcount_value * mv.transfer_in_ind)
                             admin_count_transfer_in_hdc
,COUNT(mv.headcount_value * mv.transfer_out_ind)
                             admin_count_transfer_out_hdc
,COUNT(mv.headcount_value * DECODE(mv.metric_adjust_multiplier,1,1,0) * mv.summarization_rqd_chng_ind)
                             admin_count_other_gains_hdc
,COUNT(mv.headcount_value * DECODE(mv.metric_adjust_multiplier,1,0,1) * mv.summarization_rqd_chng_ind)
                             admin_count_other_losses_hdc
,COUNT((mv.worker_hire_ind + mv.post_hire_asgn_start_ind) +
       (2 * mv.transfer_in_ind) +
       (3 * mv.transfer_out_ind) +
       (4 * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind)))
                             admin_change_type_id
,COUNT(*)                    admin_count
FROM
    HRI.HRI_MAP_SUP_WRKFC_ASG mv
WHERE (mv.worker_hire_ind = 1
    OR mv.post_hire_asgn_start_ind = 1
    OR mv.worker_term_ind = 1
    OR mv.pre_sprtn_asgn_end_ind = 1
    OR mv.transfer_in_ind = 1
    OR mv.transfer_out_ind = 1
    OR mv.summarization_rqd_chng_ind = 1)
AND mv.direct_ind = 1
AND mv.summarization_rqd_ind = 1
GROUP BY
 mv.supervisor_person_id
,mv.effective_date
,mv.assignment_id
,mv.person_id
,mv.wkth_wktyp_sk_fk
,mv.term_voluntary_ind
,mv.term_involuntary_ind
UNION ALL
SELECT
 mv.supervisor_person_id     supervisor_person_id
,mv.effective_date           effective_date
,mv.assignment_id            assignment_id
,mv.person_id                person_id
,mv.wkth_wktyp_sk_fk         wkth_wktyp_sk_fk
,SUM((mv.worker_hire_ind + mv.post_hire_asgn_start_ind) +
     (2 * mv.transfer_in_ind) +
     (3 * mv.transfer_out_ind) +
     (4 * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind)))
                             change_type_id
,0                           direct_record_ind
,COUNT(*)                    transfer_within_count
,SUM(mv.headcount_value * (mv.worker_hire_ind + mv.post_hire_asgn_start_ind))
                             hire_hdc
,SUM(mv.headcount_value * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind))
                             termination_hdc
,SUM(mv.headcount_value * mv.transfer_in_ind)
                             transfer_in_hdc
,SUM(mv.headcount_value * mv.transfer_out_ind)
                             transfer_out_hdc
,SUM(mv.headcount_value * DECODE(mv.metric_adjust_multiplier,1,1,0) * mv.summarization_rqd_chng_ind)
                             other_gains_hdc
,SUM(mv.headcount_value * DECODE(mv.metric_adjust_multiplier,1,0,1) * mv.summarization_rqd_chng_ind)
                             other_losses_hdc
,SUM(mv.headcount_value * (mv.worker_hire_ind + mv.post_hire_asgn_start_ind))
                             hire_fte
,SUM(mv.headcount_value * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind))
                             termination_fte
,SUM(mv.headcount_value * mv.transfer_in_ind)
                             transfer_in_fte
,SUM(mv.headcount_value * mv.transfer_out_ind)
                             transfer_out_fte
,mv.term_voluntary_ind       term_voluntary_ind
,mv.term_involuntary_ind     term_involuntary_ind
,'ROLLUP'                    admin_row_type
,COUNT(mv.headcount_value * (mv.worker_hire_ind + mv.post_hire_asgn_start_ind))
                             admin_count_hire_hdc
,COUNT(mv.headcount_value * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind))
                             admin_count_termination_hdc
,COUNT(mv.headcount_value * mv.term_voluntary_ind)
                             admin_count_term_vol_hdc
,COUNT(mv.headcount_value * mv.term_involuntary_ind)
                             admin_count_term_invol_hdc
,COUNT(mv.headcount_value * mv.transfer_in_ind)
                             admin_count_transfer_in_hdc
,COUNT(mv.headcount_value * mv.transfer_out_ind)
                             admin_count_transfer_out_hdc
,COUNT(mv.headcount_value * DECODE(mv.metric_adjust_multiplier,1,1,0) * mv.summarization_rqd_chng_ind)
                             admin_count_other_gains_hdc
,COUNT(mv.headcount_value * DECODE(mv.metric_adjust_multiplier,1,0,1) * mv.summarization_rqd_chng_ind)
                             admin_count_other_losses_hdc
,COUNT((mv.worker_hire_ind + mv.post_hire_asgn_start_ind) +
       (2 * mv.transfer_in_ind) +
       (3 * mv.transfer_out_ind) +
       (4 * (mv.worker_term_ind + mv.pre_sprtn_asgn_end_ind)))
                             admin_change_type_id
,COUNT(*)                    admin_count
FROM
    HRI.HRI_MAP_SUP_WRKFC_ASG mv
WHERE (mv.worker_hire_ind = 1
    OR mv.post_hire_asgn_start_ind = 1
    OR mv.worker_term_ind = 1
    OR mv.pre_sprtn_asgn_end_ind = 1
    OR mv.transfer_in_ind = 1
    OR mv.transfer_out_ind = 1
    OR mv.summarization_rqd_chng_ind = 1)
AND mv.summarization_rqd_ind = 1
GROUP BY
 mv.supervisor_person_id
,mv.effective_date
,mv.assignment_id
,mv.person_id
,mv.wkth_wktyp_sk_fk
,mv.term_voluntary_ind
,mv.term_involuntary_ind