/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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_OFFER_SALARY_DETAILS_V 
AS
SELECT paybases.name         as salary_basis
      ,paybases.pay_basis    as pay_basis
      ,pay.proposed_salary_n as proposed_salary
      ,(SELECT pet.input_currency_code
        FROM   pay_input_values_f piv
              ,pay_element_types_f pet
        WHERE  piv.input_value_id = paybases.input_value_id
        AND    TRUNC(sysdate) BETWEEN piv.effective_start_date AND piv.effective_end_date
        AND    pet.element_type_id = piv.element_type_id
        AND    TRUNC(sysdate) BETWEEN pet.effective_start_date AND pet.effective_end_date
       ) as currency
      ,NVL(pgr.minimum,0.00)
       ||'-'
       || NVL(pgr.maximum,0.00)                                                                                                     as grade_range
      ,ROUND(100*(pay.proposed_salary_n * paybases.pay_annualization_factor)/(pgr.mid_value*paybases.grade_annualization_factor),2) as comparatio
      ,CASE
         WHEN pgr.minimum*paybases.grade_annualization_factor> pay.proposed_salary_n*paybases.pay_annualization_factor
         THEN fnd_message.get_string('PER','IRC_412546_SAL_BLW_RANGE')
         WHEN pgr.maximum*paybases.grade_annualization_factor < pay.proposed_salary_n*paybases.pay_annualization_factor
         THEN fnd_message.get_string('PER','IRC_412547_SAL_ABV_RANGE')
         WHEN pgr.minimum*paybases.grade_annualization_factor = pay.proposed_salary_n*paybases.pay_annualization_factor
         THEN '1'
         WHEN pgr.minimum =pgr.maximum
         THEN '4'
         ELSE TO_CHAR(CEIL(((pay.proposed_salary_n *paybases.pay_annualization_factor) - (pgr.minimum*paybases.grade_annualization_factor))/ (((pgr.maximum*paybases.grade_annualization_factor)- (pgr.minimum*paybases.grade_annualization_factor))/4)))
       END AS quartile
      ,pay.pay_proposal_id
      ,pay.change_date           as proposal_change_date
      ,pay.last_update_date      as proposal_last_update_date
      ,pay.last_updated_by       as proposal_last_updated_by
      ,pay.last_update_login     as proposal_last_update_login
      ,pay.created_by            as proposal_created_by
      ,pay.creation_date         as proposal_creation_date
      ,pay.object_version_number as proposal_obj_version_number
      ,pay.approved              as proposal_approved
      ,pay.multiple_components   as proposal_multiple_components
      ,asg.assignment_id         as assignment_id
      ,iof.offer_id
FROM   per_pay_proposals pay
      ,per_pay_bases paybases
      ,per_all_assignments_f asg
      ,pay_grade_rules_f pgr
      ,per_grades pg
      ,irc_offers iof
WHERE  iof.offer_assignment_id = pay.assignment_id
AND    pay.assignment_id      = asg.assignment_id
AND    paybases.pay_basis_id    = asg.pay_basis_id
AND    asg.effective_start_date =
       (SELECT MAX(effective_start_date)
        FROM   per_assignments_f asg2
        WHERE  asg.assignment_id = asg2.assignment_id
        AND    asg2.effective_start_date <= TRUNC(sysdate)
       )
AND    pg.grade_id = asg.grade_id
AND    pgr.grade_or_spinal_point_id(+) = pg.grade_id
AND    pgr.rate_type                   ='G'
AND    pgr.rate_id                     = paybases.rate_id
AND    TRUNC(sysdate) BETWEEN NVL(pgr.effective_start_date,TRUNC(sysdate)) AND NVL(pgr.effective_end_date,TRUNC(sysdate));

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_OFFER_SALARY_DETAILS_V'
   ,p_description            => 'Applaud Offer Salary Details');
END;
/
  
COMMIT;
EXIT;