/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW xxas_salary_history_v 
AS
SELECT *
FROM (SELECT  hr_util_misc_ss.getObjectName('JOB', paaf.job_id, papf.business_group_id, job.name) job
             ,grade.name grade
             ,(c.proposed_salary_n * cpb.pay_annualization_factor) annualized_salary
             ,cpb.pay_basis pay_basis
             ,hr_util_misc_ss.get_preferred_currency(petf.input_currency_code, c.change_date) currency
             ,c.change_date
             ,cpb.name salary_basis
             ,hr_util_misc_ss.get_in_preferred_currency_num
               (c.proposed_salary_n, petf.input_currency_code, c.change_date) salary_rate
             ,hr_general.decode_lookup('PAY_BASIS',cpb.pay_basis) salary_frequency
             ,hr_util_misc_ss.get_in_preferred_currency_num
               ((c.proposed_salary_n *
                 decode
                  (cpb.pay_annualization_factor
                  ,null, 1
                  ,0, 1
                  ,cpb.pay_annualization_factor))
                - (o.proposed_salary_n *
                   decode
                     (opb.pay_annualization_factor
                     ,null, 1
                     ,0, 1
                     ,opb.pay_annualization_factor))
                  ,petf.input_currency_code, c.change_date) increase_amount
             ,round
                (((c.proposed_salary_n *
                   decode
                     (cpb.pay_annualization_factor
                     ,null, 1
                     ,0, 1
                     ,cpb.pay_annualization_factor))
                  - (o.proposed_salary_n *
                     decode
                       (opb.pay_annualization_factor
                       ,null, 1
                       ,0, 1
                       ,opb.pay_annualization_factor)))
                 / (decode
                     (o.proposed_salary_n *
                      decode
                       (opb.pay_annualization_factor
                       ,null, 1
                       ,0, 1
                       ,opb.pay_annualization_factor)
                     ,0, null
                     ,o.proposed_salary_n *
                      decode
                        (opb.pay_annualization_factor
                        ,null, 1
                        , 0, 1
                        ,opb.pay_annualization_factor))) * 100, 2) increase_percentage
             ,hr_general.decode_lookup('PROPOSAL_REASON',c.proposal_reason) proposal_reason
             ,paaf.sal_review_period salary_review_period
             ,hr_general.decode_lookup('FREQUENCY',paaf.sal_review_period_frequency) salary_review_frequency
             ,c.next_sal_review_date next_review_date
             ,o.proposed_salary_n *
              decode
                (opb.pay_annualization_factor
                ,null, 1
                ,0, 1
                ,opb.pay_annualization_factor) last_anual_sal
             ,hr_util_misc_ss.get_in_preferred_currency_num
               (cpb.grade_annualization_factor * fnd_number.canonical_to_number(gr.maximum)
               ,nvl(gr.currency_code, petf.input_currency_code)
               ,c.change_date) maximum
             ,hr_util_misc_ss.get_in_preferred_currency_num
               (cpb.grade_annualization_factor * fnd_number.canonical_to_number(gr.minimum)
               ,nvl(gr.currency_code, petf.input_currency_code)
               ,c.change_date) minimum
             ,hr_util_misc_ss.get_in_preferred_currency_num
               (cpb.grade_annualization_factor * fnd_number.canonical_to_number(mid_value)
               ,nvl(gr.currency_code, petf.input_currency_code)
               ,c.change_date) mid_value
             ,hr_general.decode_lookup('YES_NO', c.approved) approved
             ,c.next_perf_review_date
             ,paaf.perf_review_period
             ,hr_general.decode_lookup
               ('FREQUENCY',paaf.perf_review_period_frequency) perf_review_period_frequency
             ,c.pay_proposal_id
             ,papf.person_id
             ,paaf.assignment_id
             ,paaf.period_of_service_id
             ,c.attribute_category
             ,c.attribute1
             ,c.attribute2
             ,c.attribute3
             ,c.attribute4
             ,c.attribute5
             ,c.attribute6
             ,c.attribute7
             ,c.attribute8
             ,c.attribute9
             ,c.attribute10
             ,c.attribute11
             ,c.attribute12
             ,c.attribute13
             ,c.attribute14
             ,c.attribute15
             ,c.attribute16
             ,c.attribute17
             ,c.attribute18
             ,c.attribute19
             ,c.attribute20
             ,paaf.effective_start_date
             ,paaf.effective_end_date
             ,paaf.assignment_number
             ,c.multiple_components
             ,hr_general.decode_lookup('YES_NO',paaf.primary_flag) primary_flag
             ,payroll.payroll_name
             ,paaf.assignment_type
             ,(SELECT pgm.name
               FROM   ben_pgm_f pgm
               WHERE  pgm.pgm_id = paaf.grade_ladder_pgm_id
               AND    paaf.effective_start_date BETWEEN
                      pgm.effective_start_date AND pgm.effective_end_date) grade_ladder
             ,c.approved approved_flag
             ,pspp.effective_start_date step_date
             ,hr_general.decode_step(pspp.step_id, c.change_date) step_value
             ,hr_general.decode_step(pspp.step_id, c.change_date) point
      FROM    per_all_people_f papf
             ,per_pay_proposals c
             ,per_pay_proposals o
             ,per_all_assignments_f paaf
             ,per_pay_bases cpb
             ,per_all_assignments_f opaaf
             ,per_pay_bases opb
             ,pay_all_payrolls_f payroll
             ,per_grades_tl grade
             ,pay_grade_rules_f gr
             ,per_jobs_tl job
             ,pay_input_values_f ivf
             ,pay_element_types_f petf
             ,per_spinal_point_placements_f pspp
      WHERE   c.assignment_id = o.assignment_id(+)
      AND     c.last_change_date = o.change_date(+)
      AND     o.assignment_id = opaaf.assignment_id(+) 
      AND     o.change_date BETWEEN
              opaaf.effective_start_date(+) AND opaaf.effective_end_date(+)
      AND     paaf.effective_start_date BETWEEN
              payroll.effective_start_date(+) AND payroll.effective_end_date(+)
      AND     paaf.payroll_id = payroll.payroll_id(+) 
      AND     opaaf.pay_basis_id = opb.pay_basis_id(+)
      AND     c.assignment_id = paaf.assignment_id
      AND     paaf.person_id = papf.person_id
      AND     paaf.pay_basis_id = cpb.pay_basis_id
      AND     cpb.input_value_id = ivf.input_value_id
      AND     c.change_date BETWEEN 
              ivf.effective_start_date AND ivf.effective_end_date
      AND     ivf.element_type_id = petf.element_type_id
      AND     c.change_date BETWEEN
              petf.effective_start_date AND petf.effective_end_date
      AND     gr.rowid(+) = hr_mee_views_gen.getAsgGradeRule(c.pay_proposal_id)
      AND     paaf.grade_id = grade.grade_id(+)
      AND     grade.language(+) = userenv('LANG')
      AND     paaf.job_id = job.job_id(+)
      AND     job.language(+) = userenv('LANG')
      AND     c.assignment_id = pspp.assignment_id(+)
      AND     c.change_date BETWEEN
              paaf.effective_start_date AND paaf.effective_end_date
      AND     c.change_date BETWEEN
              papf.effective_start_date AND papf.effective_end_date
      AND     c.change_date BETWEEN
              pspp.effective_start_date(+) AND pspp.effective_end_date(+)) QRSLT
WHERE change_date <= SYSDATE
AND   approved_flag = 'Y'
ORDER BY CHANGE_DATE DESC;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_SALARY_HISTORY_V'
   ,p_description            => 'Applaud Salary History');
END;
/

COMMIT;
EXIT;
