/* $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_payslip_emea_pdi_v 
AS
SELECT /*+ ordered index(ppa PAY_PAYROLL_ACTIONS_PK) index(ppa1 PAY_PAYROLL_ACTIONS_PK) */
         lck.locking_action_id action_context_id
        ,pai.action_information5 earning_or_deduction
        ,pet.element_type_id
        ,piv.input_value_id
        ,pai.action_information4 narrative
        ,pai.action_information5 payment_type
        ,sum(fnd_number.canonical_to_number(prv.result_value)) numeric_value
FROM     pay_action_interlocks lck, -- archive action locking prepayment
         pay_assignment_actions paa1, -- prepayment action
         pay_assignment_actions paa2, -- archive action
         pay_payroll_actions ppa, -- prepayment
         pay_action_information pai, -- archived element/input value definition
         pay_action_interlocks pac, -- prepayment locking payroll run/quickpay
         pay_assignment_actions paa, -- payroll run/quickpay action
         pay_payroll_actions ppa1, -- payroll run/quickpay action
         pay_element_types_f pet, -- element types processed by the payroll run/quickpay
         pay_input_values_f piv, -- "Pay values" of type Money
         pay_run_results prr, -- run result created by the payroll run/quick pay
         pay_run_result_values prv -- Run Result value (Pay Value) created by the payroll run/quickpay
WHERE    lck.locking_action_id = paa2.assignment_action_id
AND      paa2.payroll_action_id = pai.action_context_id
AND      pai.action_context_type = 'PA'
AND      pai.action_information_category = 'EMEA ELEMENT DEFINITION'
AND      lck.locked_action_id = paa1.assignment_action_id
AND      paa1.source_action_id IS NULL
AND      paa1.payroll_action_id = ppa.payroll_action_id
AND      ppa.action_type IN ('P','U')
AND      ppa.payroll_action_id = nvl(pai.action_information1, ppa.payroll_action_id)
AND      paa1.assignment_action_id = pac.locking_action_id
AND      pet.element_type_id = pai.action_information2
AND      pet.element_type_id = piv.element_type_id
AND      piv.input_value_id = pai.action_information3
AND      prr.element_type_id = pet.element_type_id
AND      prr.status IN ('P', 'PA')
AND      prv.input_value_id = piv.input_value_id
AND      prv.run_result_id = prr.run_result_id
AND      piv.name = 'Pay Value'
AND      piv.uom = 'M'
AND      pac.locked_action_id = prr.assignment_action_id
AND      pac.locked_action_id = paa.assignment_action_id
AND      paa.payroll_action_id = ppa1.payroll_action_id
AND      ppa1.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND      ppa1.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
GROUP BY lck.locking_action_id
        ,pai.action_information5
        ,pet.element_type_id
        ,piv.input_value_id
        ,pai.action_information4
        ,pai.action_information5;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PAYSLIP_EMEA_PDI_V'
   ,p_description            => 'Applaud Payslip EMEA PDI');
END;
/

COMMIT;
EXIT;
