/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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_appl_phone_numbers_v 
AS
SELECT PHN.PHONE_ID,
  PHN.DATE_FROM,
  PHN.DATE_TO,
  PHN.PHONE_NUMBER,
  PHN.VALIDITY,
  PHN.PARENT_ID,
  PHN.PARENT_TABLE,
  PHN.PARTY_ID,
  HLK.LOOKUP_TYPE,
  HLK.LOOKUP_CODE AS PHONE_TYPE,
  HLK.MEANING AS PHONE_TYPE_DISPLAY,
  PHN.PHONE_NUMBER AS PHONE_NUMBER_DISPLAY,
  PHN.VALIDITY     AS VALIDITY_DISPLAY,
  PHN.ATTRIBUTE_CATEGORY,
  PHN.ATTRIBUTE1,
  PHN.ATTRIBUTE2,
  PHN.ATTRIBUTE3,
  PHN.ATTRIBUTE4,
  PHN.ATTRIBUTE5,
  PHN.ATTRIBUTE6,
  PHN.ATTRIBUTE7,
  PHN.ATTRIBUTE8,
  PHN.ATTRIBUTE9,
  PHN.ATTRIBUTE10,
  PHN.ATTRIBUTE11,
  PHN.ATTRIBUTE12,
  PHN.ATTRIBUTE13,
  PHN.ATTRIBUTE14,
  PHN.ATTRIBUTE15,
  PHN.ATTRIBUTE16,
  PHN.ATTRIBUTE17,
  PHN.ATTRIBUTE18,
  PHN.ATTRIBUTE19,
  PHN.ATTRIBUTE20,
  PHN.ATTRIBUTE21,
  PHN.ATTRIBUTE22,
  PHN.ATTRIBUTE23,
  PHN.ATTRIBUTE24,
  PHN.ATTRIBUTE25,
  PHN.ATTRIBUTE26,
  PHN.ATTRIBUTE27,
  PHN.ATTRIBUTE28,
  PHN.ATTRIBUTE29,
  PHN.ATTRIBUTE30,
  HLK2.MEANING     AS MEANING1,
  HLK2.LOOKUP_TYPE AS LOOKUP_TYPE1,
  HLK2.LOOKUP_CODE,
  HLK2.MEANING AS TIMES_MEANING_DISPLAY
FROM HR_LOOKUPS HLK,
  PER_PHONES PHN,
  HR_LOOKUPS HLK2
WHERE HLK.LOOKUP_TYPE   = 'PHONE_TYPE'
AND PHN.PHONE_TYPE(+)   = HLK.LOOKUP_CODE
AND PHN.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND TRUNC(sysdate) BETWEEN PHN.date_from(+) AND NVL(PHN.date_to(+),TRUNC(sysdate))
AND hlk2.lookup_type(+)='IRC_CONTACT_TIMES'
AND phn.validity       =hlk2.lookup_code(+)
--AND (HLK.LOOKUP_CODE  IN ('H1','W1','M','WF'))
AND (HLK.LOOKUP_CODE IN (select * from table(xxas_util_pkg.quoted_string_to_varchar2tbl(fnd_profile.value('IRC_PHONE_TYPES')))))
and phn.phone_number is not null;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_appl_phone_numbers_v'
   ,p_description            => 'Applaud Applicant Phone Numbers');
END;
/
  
COMMIT;
EXIT;