/* $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_emp_salary_history_rev_v 
AS
SELECT hr_util_misc_ss.getObjectName('JOB', paaf.job_id, papf.business_group_id, job.name) job
      ,grade.name grade
      ,c.proposed_salary_n 
      ,(c.proposed_salary_n * cpb.pay_annualization_factor) annualized_salary
      ,cpb.pay_basis pay_basis
      ,cpb.pay_basis_id
      ,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
      ,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_annual_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(gr.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
      ,paaf.grade_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
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.proposed_salary_n
       END                                        proposed_salary_n_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.annualized_salary
       END                                        annualized_salary_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.pay_basis
       END                                        pay_basis_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.pay_basis_id
       END                                        pay_basis_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.currency
       END                                        currency_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.change_date
       END                                        change_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.salary_basis
       END                                        salary_basis_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.salary_rate
       END                                        salary_rate_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.salary_frequency
       END                                        salary_frequency_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.increase_amount
       END                                        increase_amount_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.increase_percentage
       END                                        increase_percentage_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.proposal_reason
       END                                        proposal_reason_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.salary_review_period
       END                                        salary_review_period_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.salary_review_frequency
       END                                        salary_review_frequency_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.next_review_date
       END                                        next_review_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.last_annual_sal
       END                                        last_annual_sal_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.maximum
       END                                        maximum_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.minimum
       END                                        minimum_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.mid_value
       END                                        mid_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.approved
       END                                        approved_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.next_perf_review_date
       END                                        next_perf_review_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.perf_review_period
       END                                        perf_review_period_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.perf_review_period_frequency
       END                                        perf_review_period_frequency_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.pay_proposal_id
       END                                        pay_proposal_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.person_id
       END                                        person_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.assignment_id
       END                                        assignment_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.period_of_service_id
       END                                        period_of_service_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.grade_id
       END                                        grade_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute_category
       END                                        attribute_category_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute1
       END                                        attribute1_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute2
       END                                        attribute2_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute3
       END                                        attribute3_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute4
       END                                        attribute4_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute5
       END                                        attribute5_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute6
       END                                        attribute6_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute7
       END                                        attribute7_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute8
       END                                        attribute8_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute9
       END                                        attribute9_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute10
       END                                        attribute10_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute11
       END                                        attribute11_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute12
       END                                        attribute12_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute13
       END                                        attribute13_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute14
       END                                        attribute14_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute15
       END                                        attribute15_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute16
       END                                        attribute16_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute17
       END                                        attribute17_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute18
       END                                        attribute18_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute19
       END                                        attribute19_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.attribute20
       END                                        attribute20_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.multiple_components
       END                                        multiple_components_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.effective_start_date
       END                                        effective_start_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.effective_end_date
       END                                        effective_end_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.assignment_number
       END                                        assignment_number_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.primary_flag
       END                                        primary_flag_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.payroll_name
       END                                        payroll_name_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.assignment_type
       END                                        assignment_type_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.grade_ladder
       END                                        grade_ladder_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.approved_flag
       END                                        approved_flag_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.step_date
       END                                        step_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.step_value
       END                                        step_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL AND 
                 pend.pay_transaction_id IS NOT NULL
            THEN pend.point
       END                                        point_p
      ,pend.transaction_id
      ,pend.transaction_step_id
      ,pend.pay_transaction_id
      ,pend.item_type
      ,pend.item_key
      ,'PQH_SS_ATTACHMENT'                        attachment_entity
      ,pend.transaction_id                        attachment_pk1
      ,pend.transaction_id                        approvers
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
      ,(SELECT c.transaction_id
              ,c.transaction_step_id
              ,c.pay_transaction_id
              ,c.item_type
              ,c.item_key
              ,c.proposed_salary_n 
              ,(c.proposed_salary_n * cpb.pay_annualization_factor) annualized_salary
              ,cpb.pay_basis pay_basis
              ,c.pay_basis_id
              ,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
              ,c.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_annual_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(gr.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
              ,paaf.grade_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_transactions c
              ,per_pay_transactions 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
              ,hr_api_transactions       hat
              ,hr_api_transaction_steps  hats
        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(+)
        AND    papf.person_id             = hat.selected_person_id
        AND    hat.transaction_id         = hats.transaction_id
        AND    hat.status          NOT IN ('D', 'N', 'ACTIVE', 'AC')
        AND    hats.api_name              = 'PER_SSHR_CHANGE_PAY.PROCESS_API'
        AND    c.transaction_id           = hat.transaction_id
        AND    c.transaction_id           = o.transaction_id
        AND    c.transaction_step_id      = hats.transaction_step_id
        AND    c.transaction_step_id      = o.transaction_step_id
       ) pend
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(+)
AND    paaf.assignment_id          = pend.assignment_id(+)
AND    paaf.person_id              = pend.person_id(+)
/*AND    c.pay_proposal_id NOT IN(SELECT nvl(pay_proposal_id, -1) 
                                FROM   per_pay_transactions
                                WHERE  assignment_id = c.assignment_id)*/
ORDER BY pend.change_date,c.change_date DESC;

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

COMMIT;
EXIT;
