/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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_per_phones_v 
AS
SELECT pp.phone_id
,      pp.object_version_number 
,      pp.date_from
,      pp.date_to
,      pp.phone_type
,      xxas_util_pkg.decode_lookup
         ('PHONE_TYPE'
         ,pp.phone_type)                         phone_type_meaning
,      pp.phone_number
,      pp.parent_id                              person_id
,      pp.parent_id                              per_person_id /* Alternative name to avoid conflict with PERSON_ID FF context */
,      attribute_category
,      pp.attribute1    
,      pp.attribute2 
,      pp.attribute3 
,      pp.attribute4 
,      pp.attribute5 
,      pp.attribute6 
,      pp.attribute7 
,      pp.attribute8 
,      pp.attribute9 
,      pp.attribute10 
,      pp.attribute11 
,      pp.attribute12 
,      pp.attribute13 
,      pp.attribute14 
,      pp.attribute15 
,      pp.attribute16 
,      pp.attribute17 
,      pp.attribute18 
,      pp.attribute19 
,      pp.attribute20 
,      pp.attribute21 
,      pp.attribute22 
,      pp.attribute23 
,      pp.attribute24 
,      pp.attribute25
,      pp.attribute26 
,      pp.attribute27
,      pp.attribute28
,      pp.attribute29
,      pp.attribute30
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE1'
                   ,pp.attribute1)               attribute1_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE2'
                   ,pp.attribute2)               attribute2_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE3'
                   ,pp.attribute3)               attribute3_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE4'
                   ,pp.attribute4)               attribute4_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE5'
                   ,pp.attribute5)               attribute5_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE6'
                   ,pp.attribute6)               attribute6_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE7'
                   ,pp.attribute7)               attribute7_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE8'
                   ,pp.attribute8)               attribute8_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE9'
                   ,pp.attribute9)               attribute9_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE10'
                   ,pp.attribute10)               attribute10_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE11'
                   ,pp.attribute11)               attribute11_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE12'
                   ,pp.attribute12)               attribute12_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE13'
                   ,pp.attribute13)               attribute13_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE14'
                   ,pp.attribute14)               attribute14_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE15'
                   ,pp.attribute15)               attribute15_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE16'
                   ,pp.attribute16)               attribute16_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE17'
                   ,pp.attribute17)               attribute17_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE18'
                   ,pp.attribute18)               attribute18_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE19'
                   ,pp.attribute19)               attribute19_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE20'
                   ,pp.attribute20)               attribute20_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE21'
                   ,pp.attribute21)               attribute21_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE22'
                   ,pp.attribute22)               attribute22_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE23'
                   ,pp.attribute23)               attribute23_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE24'
                   ,pp.attribute24)               attribute24_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE25'
                   ,pp.attribute25)               attribute25_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE26'
                   ,pp.attribute26)               attribute26_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE27'
                   ,pp.attribute27)               attribute27_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE28'
                   ,pp.attribute28)               attribute28_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE29'
                   ,pp.attribute29)               attribute29_meaning 
,      xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PHONES'
                   ,attribute_category
                   ,'ATTRIBUTE30'
                   ,pp.attribute30)               attribute30_meaning 
,      pend.transaction_id
,      pend.transaction_step_id
,      pend.transaction_type
,      pend.hr_api_status_code
,      pend.workflow_status_code
,      xxas_selfservice_workflow_pkg.decode_status
         (pend.workflow_status_code)             workflow_status_meaning  
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.selected_person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_OBJECT_VERSION_NUMBER')
       END                                       object_version_number_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.selected_person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_DATE_FROM')
       END                                       date_from_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.selected_person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_DATE_TO')
       END                                       date_to_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.selected_person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE1')
       END                                       attribute1_P
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.selected_person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PHONE_TYPE')
       END                                       phone_type_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_util_pkg.decode_lookup
                   ('PHONE_TYPE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                     (pend.selected_person_id
                     ,pend.api_name
                     ,pend.transaction_step_id
                     ,'P_PHONE_TYPE'))
       END                                       phone_type_meaning_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.selected_person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PHONE_NUMBER')
       END                                       phone_number_p
,     'PQH_SS_ATTACHMENT'                        attachment_entity
,      pend.transaction_id                       attachment_pk1
FROM   per_phones    pp
,     (SELECT pid.number_value                                           person_id
       ,      eid.number_value                                           phone_id
        ,     oid.number_value                                           object_version_number
       ,      hats.api_name
       ,      hat.transaction_id
       ,      eid.transaction_step_id
       ,      DECODE(tid.varchar2_value, 'DELETE', 'DELETE', 'UPDATE')   transaction_type
       ,      hat.status                                                 hr_api_status_code
       ,      xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
       ,      hat.selected_person_id
       FROM   hr_api_transactions         hat
       ,      hr_api_transaction_steps    hats
       ,      hr_api_transaction_values   eid
       ,      hr_api_transaction_values   tid
       ,      hr_api_transaction_values   pid
       ,      hr_api_transaction_values   oid
       WHERE  hat.selected_person_id   = fnd_profile.value('PER_PERSON_ID')
       AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
       AND    hat.transaction_id       = hats.transaction_id
       AND    hats.api_name            = 'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
       AND    hats.transaction_step_id = eid.transaction_step_id
       AND    eid.name                 = 'P_PHONE_ID'
       AND    eid.number_value        IS NOT NULL
       AND    hats.transaction_step_id = tid.transaction_step_id
       AND    tid.name                 = 'P_PHONE_TYPE'
       AND    hats.transaction_step_id = pid.transaction_step_id
       AND    pid.name                 = 'P_PERSON_ID'
       AND    hats.transaction_step_id = oid.transaction_step_id
       AND    oid.name                 = 'P_OBJECT_VERSION_NUMBER'
       ) pend  
WHERE  pp.parent_table = 'PER_ALL_PEOPLE_F'
AND    pp.parent_id    = pend.person_id(+)
AND    pp.phone_id     = pend.phone_id(+)
AND    pp.object_version_number = pend.object_version_number (+)
UNION ALL
SELECT NULL                                       phone_id
,      NULL                                       object_version_number 
,      NULL                                       date_from
,      NULL                                       date_to
,      NULL                                       phone_type
,      NULL                                       phone_type_meaning
,      NULL                                       phone_number
,      hat.selected_person_id                     person_id
,      hat.selected_person_id                     per_person_id
,      NULL                                       attribute_category
,      NULL					  attribute1
,      NULL                                       attribute1_meaning
,      NULL					  attribute2
,      NULL                                       attribute2_meaning
,      NULL					  attribute3
,      NULL                                       attribute3_meaning
,      NULL					  attribute4
,      NULL                                       attribute4_meaning
,      NULL					  attribute5
,      NULL                                       attribute5_meaning
,      NULL					  attribute6
,      NULL                                       attribute6_meaning
,      NULL					  attribute7
,      NULL                                       attribute7_meaning
,      NULL					  attribute8
,      NULL                                       attribute8_meaning
,      NULL					  attribute9
,      NULL                                       attribute9_meaning
,      NULL					  attribute10
,      NULL                                       attribute10_meaning
,      NULL					  attribute11
,      NULL                                       attribute11_meaning
,      NULL					  attribute12
,      NULL                                       attribute12_meaning
,      NULL					  attribute13
,      NULL                                       attribute13_meaning
,      NULL					  attribute14
,      NULL                                       attribute14_meaning
,      NULL					  attribute15
,      NULL                                       attribute15_meaning
,      NULL					  attribute16
,      NULL                                       attribute16_meaning
,      NULL					  attribute17
,      NULL                                       attribute17_meaning
,      NULL					  attribute18
,      NULL                                       attribute18_meaning
,      NULL					  attribute19
,      NULL                                       attribute19_meaning
,      NULL					  attribute20
,      NULL                                       attribute20_meaning
,      NULL					  attribute21
,      NULL                                       attribute21_meaning
,      NULL					  attribute22
,      NULL                                       attribute22_meaning
,      NULL					  attribute23
,      NULL                                       attribute23_meaning
,      NULL					  attribute24
,      NULL                                       attribute24_meaning
,      NULL					  attribute25
,      NULL                                       attribute25_meaning
,      NULL					  attribute26
,      NULL                                       attribute26_meaning
,      NULL					  attribute27
,      NULL                                       attribute27_meaning
,      NULL					  attribute28
,      NULL                                       attribute28_meaning
,      NULL					  attribute29
,      NULL                                       attribute29_meaning
,      NULL					  attribute30
,      NULL                                       attribute30_meaning
,      hat.transaction_id
,      eid.transaction_step_id
,      'INSERT'
,      hat.status                                 hr_api_status_code
,      xxas_selfservice_workflow_pkg.transcode_status
         (hat.status)                             workflow_status_code 
,      xxas_selfservice_workflow_pkg.decode_status
         (xxas_selfservice_workflow_pkg.transcode_status
           (hat.status))                          workflow_status_meaning
,      xxas_hr_api_transaction_pkg.number_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_OBJECT_VERSION_NUMBER')             object_version_number_p
,      xxas_hr_api_transaction_pkg.date_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_DATE_FROM')                         date_from_p
,      xxas_hr_api_transaction_pkg.date_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_DATE_TO')                           date_to_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_ATTRIBUTE1')                        attribute1_P
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_PHONE_TYPE')                        phone_type_p
,      xxas_util_pkg.decode_lookup
         ('PHONE_TYPE'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,eid.transaction_step_id
           ,'P_PHONE_TYPE'))                     phone_type_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_PHONE_NUMBER')                      phone_number_p
,     'PQH_SS_ATTACHMENT'                        attachment_entity
,      hat.transaction_id                        attachment_pk1         
FROM   hr_api_transactions         hat
,      hr_api_transaction_steps    hats
,      hr_api_transaction_values   eid
,      hr_api_transaction_values   aid
WHERE  hat.selected_person_id    = fnd_profile.value('PER_PERSON_ID')
AND    hat.status           NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
AND    hat.transaction_id        = hats.transaction_id
AND    hats.api_name             = 'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
AND    hats.transaction_step_id  = eid.transaction_step_id
AND    eid.name                  = 'P_PHONE_ID'
AND    eid.number_value         IS NULL
AND    hats.transaction_step_id  = aid.transaction_step_id
AND    aid.name                  = 'P_PER_OR_CONTACT'
AND    aid.varchar2_value        = 'PERSON'
UNION ALL
SELECT NULL                                       phone_id
,      NULL                                       object_version_number 
,      NULL                                       date_from
,      NULL                                       date_to
,      NULL                                       phone_type
,      NULL                                       phone_type_meaning
,      NULL                                       phone_number
,      CASE WHEN pid.number_value = -1            
            THEN NULL
            ELSE pid.number_value 
       END                                        person_id
,      CASE WHEN pid.number_value = -1            
            THEN NULL
            ELSE pid.number_value 
       END                                        per_person_id
,      NULL                                       attribute_category
,      NULL                                       attribute1
,      NULL                                       attribute1_meaning
,      NULL					  attribute2
,      NULL                                       attribute2_meaning
,      NULL					  attribute3
,      NULL                                       attribute3_meaning
,      NULL					  attribute4
,      NULL                                       attribute4_meaning
,      NULL					  attribute5
,      NULL                                       attribute5_meaning
,      NULL					  attribute6
,      NULL                                       attribute6_meaning
,      NULL					  attribute7
,      NULL                                       attribute7_meaning
,      NULL					  attribute8
,      NULL                                       attribute8_meaning
,      NULL					  attribute9
,      NULL                                       attribute9_meaning
,      NULL					  attribute10
,      NULL                                       attribute10_meaning
,      NULL					  attribute11
,      NULL                                       attribute11_meaning
,      NULL					  attribute12
,      NULL                                       attribute12_meaning
,      NULL					  attribute13
,      NULL                                       attribute13_meaning
,      NULL					  attribute14
,      NULL                                       attribute14_meaning
,      NULL					  attribute15
,      NULL                                       attribute15_meaning
,      NULL					  attribute16
,      NULL                                       attribute16_meaning
,      NULL					  attribute17
,      NULL                                       attribute17_meaning
,      NULL					  attribute18
,      NULL                                       attribute18_meaning
,      NULL					  attribute19
,      NULL                                       attribute19_meaning
,      NULL					  attribute20
,      NULL                                       attribute20_meaning
,      NULL					  attribute21
,      NULL                                       attribute21_meaning
,      NULL					  attribute22
,      NULL                                       attribute22_meaning
,      NULL					  attribute23
,      NULL                                       attribute23_meaning
,      NULL					  attribute24
,      NULL                                       attribute24_meaning
,      NULL					  attribute25
,      NULL                                       attribute25_meaning
,      NULL					  attribute26
,      NULL                                       attribute26_meaning
,      NULL					  attribute27
,      NULL                                       attribute27_meaning
,      NULL					  attribute28
,      NULL                                       attribute28_meaning
,      NULL					  attribute29
,      NULL                                       attribute29_meaning
,      NULL					  attribute30
,      NULL                                       attribute30_meaning
,      hat.transaction_id
,      hats.transaction_step_id
,      'INSERT'
,      hat.status                                 hr_api_status_code
,      xxas_selfservice_workflow_pkg.transcode_status
         (hat.status)                             workflow_status_code 
,      xxas_selfservice_workflow_pkg.decode_status
         (xxas_selfservice_workflow_pkg.transcode_status
           (hat.status))                          workflow_status_meaning
,      xxas_hr_api_transaction_pkg.number_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_OBJECT_VERSION_NUMBER')             object_version_number_p
,      xxas_hr_api_transaction_pkg.date_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_DATE_FROM')                         date_from_p
,      xxas_hr_api_transaction_pkg.date_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_DATE_TO')                           date_to_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_ATTRIBUTE1')                        attribute1_P
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_PHONE_TYPE')                        phone_type_p
,      xxas_util_pkg.decode_lookup
         ('PHONE_TYPE'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,eid.transaction_step_id
           ,'P_PHONE_TYPE'))                     phone_type_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,eid.transaction_step_id
         ,'P_PHONE_NUMBER')                      phone_number_p
,     'PQH_SS_ATTACHMENT'                        attachment_entity
,      hat.transaction_id                        attachment_pk1         
FROM   hr_api_transactions         hat
,      hr_api_transaction_steps    hats
,      hr_api_transaction_values   eid
,      hr_api_transaction_values   aid
,      hr_api_transaction_values   pid
WHERE  hat.selected_person_id    = fnd_profile.value('PER_PERSON_ID')
AND    hat.status           NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
AND    hat.transaction_id        = hats.transaction_id
AND    hats.api_name             = 'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
AND    hats.transaction_step_id  = eid.transaction_step_id
AND    eid.name                  = 'P_PHONE_ID'
AND    eid.number_value         IS NULL
AND    hats.transaction_step_id  = aid.transaction_step_id
AND    aid.name                  = 'P_PER_OR_CONTACT'
AND    aid.varchar2_value       IN ('DPDNT_CR_NEW_CONT','DPDNT_OVRW_UPD')
AND    hats.transaction_step_id  = pid.transaction_step_id
AND    pid.name                  = 'P_PERSON_ID'
;


BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PER_PHONES_V'
   ,p_description            => 'Applaud Person Phones');
END;
/
  
COMMIT;
EXIT;
