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_WRKFC_JX_MV

Source


SELECT /* 12.0: bug#4526784 */ 
   inner_sup.supervisor_person_id   supervisor_person_id
  ,inner_sup.effective_start_date   effective_start_date
  ,inner_sup.effective_end_date     effective_end_date
  ,inner_sup.job_id                 job_id
  ,SUM(inner_sup.headcount)         total_headcount
  ,SUM(inner_sup.anl_slry)          total_anl_slry
  ,MAX(inner_sup.anl_slry)          max_anl_slry
  ,MIN(inner_sup.anl_slry)          min_anl_slry
  ,inner_sup.input_currency_code    anl_slry_currency
FROM
(
SELECT suph.sup_person_id      supervisor_person_id
    , hr_general.start_of_time effective_start_date
    , hr_general.end_of_time   effective_end_date
    , asg.job_id               job_id
    , hri_bpl_abv.calc_abv(
                   asg.assignment_id
                  ,asg.business_group_id
                  ,'HEAD'
                  ,pro.change_date
                  ,'Y'
                  ,NULL)       headcount
    ,(ppb.pay_annualization_factor * pro.proposed_salary_n) anl_slry
    ,pet.input_currency_code   input_currency_code
FROM per_all_assignments_f  asg
    ,per_pay_bases          ppb
    ,per_pay_proposals      pro
    ,pay_input_values_f     piv
    ,pay_element_types_f    pet
    ,hri_cs_suph            suph
    ,fii_time_day           day
WHERE  1=1
AND suph.sub_person_id = asg.person_id
AND day.start_date BETWEEN suph.effective_start_date AND suph.effective_end_date
AND day.start_date BETWEEN asg.effective_start_date  AND asg.effective_end_date
AND asg.assignment_id = pro.assignment_id
AND pro.change_date = (SELECT MAX(change_date)
                       FROM per_pay_proposals pro2
                       WHERE pro2.assignment_id = pro.assignment_id
                       AND   pro2.change_date <= day.start_date
                       AND   pro2.approved = 'Y')
AND asg.pay_basis_id = ppb.pay_basis_id
AND ppb.input_value_id = piv.input_value_id
AND pro.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND pro.change_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND day.start_date = TRUNC(sysdate)
) inner_sup
GROUP BY
  inner_sup.supervisor_person_id
 ,inner_sup.effective_start_date
 ,inner_sup.effective_end_date
 ,inner_sup.job_id
 ,inner_sup.input_currency_code