/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := '2FA';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => '2FA'
    ,p_path               => '/2FA'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPhoneNumber'
    ,p_description        => 'Get Phone Number'
    ,p_methods            => 'GET'
    ,p_path               => 'phoneNumber'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("personType"
                     ,XMLForest
                       (xppv.phone_id      "id"
                       ,xppv.phone_number  "value"
                       ,CASE WHEN xppv.phone_number = substr(fup.preference_value, length(fup.preference_value)-9, length(fup.preference_value)) 
                        THEN
                            xxas_com_2fa_pkg.mask_phone_number(fup.preference_value)
                        ELSE
                            '+' || pcc.phone_country_code || ' ' || xxas_com_2fa_pkg.mask_phone_number(xppv.phone_number)
                        END "label")
                     )
                   )
                 ) "data"
        FROM  per_all_assignments_f paaf
             ,hr_locations loc
             ,hz_phone_country_codes pcc
             ,per_phones xppv
             ,fnd_user_preferences fup
        where paaf.assignment_id = xxas_util_pkg.get_primary_worker_asg(fnd_global.employee_id, trunc(sysdate))
        and xppv.parent_id = fnd_global.employee_id
        and fup.user_name = fnd_global.user_name
        and fup.preference_name = 'XXAS_3RD_PARTY_2FA_NUMBER'
        and fup.module_name = 'XXAS.2FA'
        and loc.location_id = paaf.location_id
        and pcc.territory_code = loc.country 
        and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
        ]'
    );


  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPhoneNumberFromFUP'
    ,p_description        => 'Get Phone Number From Fnd User Preference'
    ,p_methods            => 'GET'
    ,p_path               => 'fup/phoneNumber'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLELEMENT("ROW"
                         ,XMLFOREST(max(decode(preference_name,'XXAS_3RD_PARTY_2FA_NUMBER',preference_value)) PHONE_NUMBER
                                   ,max(decode(preference_name,'XXAS_2FA_BACKUP_CODE',preference_value)) BACKUP_CODE
								   )) "data"
        FROM fnd_user_preferences
		WHERE user_name = fnd_global.user_name
		AND module_name = 'XXAS.2FA'
        ]'
    );

    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetBackupCode'
    ,p_description        => 'Get Backup Code'
    ,p_methods            => 'GET'
    ,p_path               => 'GetBackupCode'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT  XMLELEMENT("ROW"
                         ,XMLFOREST(
                                      xxas_com_2fa_pkg.create_backup_code()  BACKUP_CODE
                                    )
                          ) "data"
        FROM   dual
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'getphonedetails'
    ,p_description        => 'default verification details'
    ,p_methods            => 'GET'
    ,p_path               => '/getphonedetails'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT  XMLELEMENT("ROW"
                         ,XMLFOREST(fu.employee_id                      PERSON_ID
                                    ,pp.phone_id                      PHONE_ID
                                    ,pp.phone_type                    PHONE_TYPE
                                    ,pp.phone_type                    PHONE_TYPE_OLD
                                    ,NVL(HL.COUNTRY,PA.COUNTRY)       COUNTRY_CODE
                                    ,DECODE(xxas_com_2fa_pkg.get_phone_details(FND_GLOBAL.EMPLOYEE_ID,'W1','P'),'-',NULL,xxas_com_2fa_pkg.get_phone_details(FND_GLOBAL.EMPLOYEE_ID,'W1','P'))        PHONE_NUMBER
                                    ,DECODE(xxas_com_2fa_pkg.get_phone_details(FND_GLOBAL.EMPLOYEE_ID,'W1','P'),'-',NULL,xxas_com_2fa_pkg.get_phone_details(FND_GLOBAL.EMPLOYEE_ID,'W1','P'))        PHONE_NUMBER_OLD
                                    ,pp.attribute_category            ATTRIBUTE_category
                                    ,pp.attribute1                    ATTRIBUTE1
                                    ,pp.attribute2                    ATTRIBUTE2
                                    ,pp.attribute3                    ATTRIBUTE3
                                    ,pp.attribute4                    ATTRIBUTE4
                                    ,pp.attribute5                    ATTRIBUTE5
                                    ,pp.attribute6                    ATTRIBUTE6
                                    ,pp.attribute7                    ATTRIBUTE7
                                    ,pp.attribute8                    ATTRIBUTE8
                                    ,pp.attribute9                    ATTRIBUTE9
                                    ,pp.attribute10                   ATTRIBUTE10
                                    ,pp.attribute11                   ATTRIBUTE11
                                    ,pp.attribute12                   ATTRIBUTE12
                                    ,pp.attribute13                   ATTRIBUTE13
                                    ,pp.attribute14                   ATTRIBUTE14
                                    ,pp.attribute15                   ATTRIBUTE15
                                    ,pp.attribute16                   ATTRIBUTE16
                                    ,pp.attribute17                   ATTRIBUTE17
                                    ,pp.attribute18                   ATTRIBUTE18
                                    ,pp.attribute19                   ATTRIBUTE19
                                    ,pp.attribute20                   ATTRIBUTE20
                                    ,pp.attribute21                   ATTRIBUTE21
                                    ,pp.attribute22                   ATTRIBUTE22
                                    ,pp.attribute23                   ATTRIBUTE23
                                    ,pp.attribute24                   ATTRIBUTE24
                                    ,pp.attribute25                   ATTRIBUTE25
                                    ,pp.attribute26                   ATTRIBUTE26
                                    ,pp.attribute27                   ATTRIBUTE27
                                    ,pp.attribute28                   ATTRIBUTE28
                                    ,pp.attribute29                   ATTRIBUTE29
                                    ,pp.attribute30                   ATTRIBUTE30
								   )) "data"
               FROM    
                fnd_user fu
                ,per_phones pp
                ,per_all_assignments_f paaf
                ,per_assignment_status_types past
                ,hr_locations hl
                ,per_addresses pa
        where   fu.user_name                  = FND_GLOBAL.USER_NAME
        AND     pp.parent_id(+)               = fu.employee_id
        AND     paaf.person_id(+)             = fu.employee_id
        AND     pa.person_id(+)                = fu.employee_id 
        AND     pp.phone_type(+)              = 'W1'                  
        AND     TRUNC(SYSDATE) BETWEEN
                paaf.effective_start_date(+) AND paaf.effective_end_date(+)
        AND     paaf.assignment_type(+)          IN ('E', 'C')
        AND     paaf.primary_flag(+)       = 'Y'
        AND     paaf.assignment_status_type_id    = past.assignment_status_type_id(+)
        AND     past.per_system_status(+)            &lt;&gt; 'TERM_ASSIGN'
        AND     hl.location_id(+)                    = paaf.LOCATION_ID
        AND     TRUNC(SYSDATE) BETWEEN
                pa.date_from(+) AND nvl(pa.date_to,trunc(sysdate))
        AND     rownum  =   1
        ]'
    );

 xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetChangeNumberRadio'
    ,p_description        => 'GetChangeNumberRadio'
    ,p_methods            => 'GET'
    ,p_path               => 'GetChangeNumberRadio'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("changeNumber"
                     ,XMLForest
                       (CASE WHEN  message_name = 'XXAS_2FA_ADD_NEW_NUMBER'         
                             THEN  'INSERT'
                             WHEN  message_name = 'XXAS_2FA_CHANGE_NUM_OLD_TO_NEW'         
                             THEN  'UPDATE'
                        END "id"
                        ,CASE
                            WHEN message_name = 'XXAS_2FA_CHANGE_NUM_OLD_TO_NEW'  
                            THEN xxas_com_msg_pkg.get_decoded_string(xxas_com_msg_type(xxas_util_pkg.app
                                                ,'XXAS_2FA_CHANGE_NUM_OLD_TO_NEW'
                                                ,xxas_com_msg_token_varray
                                                            (xxas_com_msg_token_type('OLD_NUMBER',{requestParam.oldNo}), 
                                                            xxas_com_msg_token_type('NEW_NUMBER',{requestParam.newNo})
                                                            ) 
                                                    ))
                            WHEN message_name = 'XXAS_2FA_ADD_NEW_NUMBER'       
                            THEN xxas_com_msg_pkg.get_decoded_string
                            (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_2FA_ADD_NEW_NUMBER'
                         ,xxas_com_msg_token_varray
                           (xxas_com_msg_token_type('NEW_NUMBER', {requestParam.newNo}))))
                           end "label"
                        ,CASE
                            WHEN message_name = 'XXAS_2FA_CHANGE_NUM_OLD_TO_NEW' 
                            THEN xxas_com_msg_pkg.get_decoded_string(xxas_com_msg_type(xxas_util_pkg.app
                                                ,'XXAS_2FA_CHANGE_NUM_OLD_TO_NEW'
                                                ,xxas_com_msg_token_varray
                                                            (xxas_com_msg_token_type('OLD_NUMBER',{requestParam.oldNo}), 
                                                            xxas_com_msg_token_type('NEW_NUMBER',{requestParam.newNo})
                                                            ) 
                                                    ))
                            WHEN message_name = 'XXAS_2FA_ADD_NEW_NUMBER'       
                            THEN xxas_com_msg_pkg.get_decoded_string
                            (xxas_com_msg_type
                         (xxas_util_pkg.app, 'XXAS_2FA_ADD_NEW_NUMBER'
                         ,xxas_com_msg_token_varray
                           (xxas_com_msg_token_type('NEW_NUMBER', {requestParam.newNo}))))
                           end "value"
                      )
                     )
                   )
                 ) "data"
        FROM
            fnd_new_messages
        WHERE  (message_name IN (  'XXAS_2FA_CHANGE_NUM_OLD_TO_NEW', 'XXAS_2FA_ADD_NEW_NUMBER'))
        AND LANGUAGE_CODE = userenv('LANG')
        
     ]'
    );
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPhoneType'
    ,p_description        => 'GetPhoneType'
    ,p_methods            => 'GET'
    ,p_path               => 'GetPhoneType'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("PhoneType"
                     ,XMLForest
                       (HL.LOOKUP_CODE               "id"
                        ,HL.MEANING                  "label"
                        ,HL.MEANING                  "VALUE"
                      )
                     )
                   )
                 ) "data"
        FROM    HR_LOOKUPS HL
        WHERE   HL.LOOKUP_TYPE ='PHONE_TYPE'
       
        ]'
    );
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPersonPhoneNumber'
    ,p_description        => 'GetPersonPhoneNumber'
    ,p_methods            => 'GET'
    ,p_path               => 'GetPhoneNumber'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[  SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("PHONE_NUMBER"
                     ,XMLForest
                       ('PHONE_NUMBER'        as "id"
                        ,'input'               as "state"
                        ,'text'                as "type"
                        ,'true'                as "visible"
                        ,xxas_com_2fa_pkg.get_phone_details({requestParam.personId},{requestParam.personType},'P')        as "value"
							 )
                   )
                 )) "data"
        FROM dual;
         ]'
         
    );

END;
/
COMMIT;
EXIT;
