/* $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_MESSAGES_V
AS
  SELECT
    /*+ ORDERED USE_NL(PAA) */
    PAI.ACTION_CONTEXT_ID,
    PML.SOURCE_ID ORIGINAL_ASG_ACTION_ID,
    PML.LINE_TEXT MESSAGE_TEXT,
	PML.MESSAGE_LEVEL
  FROM PAY_ACTION_INFORMATION PAI,
    PAY_ASSIGNMENT_ACTIONS PAA,
    PAY_PAYROLL_ACTIONS PPA,
    PER_TIME_PERIODS PTP,
    PAY_MESSAGE_LINES PML
  WHERE PAI.ACTION_CONTEXT_TYPE       = 'AAP'
  AND PAI.ACTION_INFORMATION_CATEGORY = 'EMPLOYEE DETAILS'
  AND PTP.TIME_PERIOD_ID              = PAI.ACTION_INFORMATION16
  AND PTP.TIME_PERIOD_ID              = PPA.TIME_PERIOD_ID
  AND PPA.ACTION_TYPE                IN ('Q','R')
  AND PAA.PAYROLL_ACTION_ID           = PPA.PAYROLL_ACTION_ID
  AND PAA.ACTION_STATUS               = 'C'
  AND PAI.ASSIGNMENT_ID               = PAA.ASSIGNMENT_ID
  AND PML.SOURCE_ID                   = PAA.ASSIGNMENT_ACTION_ID;

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

COMMIT;
EXIT;
