/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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_change_manager_review_v 
AS
SELECT ppf.person_id
      ,trunc(paaf.effective_start_date) effective_start_date
      ,trunc(paaf.effective_end_date)   effective_end_date
      ,xxas_mss_util_pkg.has_future_dated_records(paaf.assignment_id,paaf.effective_end_date) has_future_dated_records
      ,null effective_date_option
      ,(SELECT count(*) FROM table(xxas_mss_util_pkg.direct_reportees_data(ppf.person_id,paaf.assignment_id))) direct_reports_count
      ,(CASE WHEN (SELECT count(*) FROM table(xxas_mss_util_pkg.direct_reportees_data(ppf.person_id,paaf.assignment_id))) >= 1
             THEN 'Y'
             ELSE 'N'
        END) is_manager
      ,ppf.person_id	new_manager_dr
      ,(SELECT mgr.person_id
        FROM   table(xxas_com_hier_pkg.mgrs_for_asg_tbl(paaf.assignment_id, trunc(sysdate))) mgr
        WHERE  rownum = 1) new_manager
      ,null effective_from_ldw
      ,paaf.assignment_id
      ,null comments_to_approver
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN 'Y'
            ELSE 'N'
       END                                 pend_req
      ,pend.effective_date_option          effective_date_option_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cast
                  (multiset
                    (SELECT xxas_direct_report
                              (pend_dr.effective_date
                              ,pend_dr.person_id
                              ,to_char(pend_dr.person_id) || '_' || to_char(pend_dr.assignment_id)
                              ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',pend_dr.global_name,pend_dr.local_name)
                              ,pend_dr.job_id
                              ,pend_dr.job_name
                              ,xxas_mss_util_pkg.person_full_name(pend.person_id)
                              ,pend.person_id
                              ,pend_dr.mgr_person_id
                              )
                     FROM    TABLE(xxas_mss_util_pkg.pend_req_direct_reports_data(pend.transaction_id)) pend_dr
                    )
                   AS xxas_direct_report_tbl) 
           ELSE  cast
                  (multiset
                    (SELECT xxas_direct_report
                              (trunc(sysdate)
                              ,hier.person_id
                              ,to_char(hier.person_id) || '_' || to_char(hier.assignment_id)
                              ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',hier.global_name,hier.local_name)
                              ,hier.job_id
                              ,hier.job_name
                              ,xxas_mss_util_pkg.person_full_name(ppf.person_id)
                              ,hier.mgr_person_id
                              ,null
                              )
                     FROM    TABLE(xxas_mss_util_pkg.direct_reportees_data(ppf.person_id,paaf.assignment_id)) hier
                    )
                   AS xxas_direct_report_tbl)
       END                                 direct_reports
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cast
                  (multiset
                    (SELECT xxas_new_direct_report
                              (pend_new_dr.effective_date
                              ,null
                              ,pend_new_dr.person_id
                              ,pend_new_dr.job_name
                              )
                     FROM    TABLE(xxas_mss_util_pkg.pend_req_new_direct_rep_data(pend.transaction_id)) pend_new_dr
                    )
                   AS xxas_new_direct_report_tbl)
            ELSE cast
                  (multiset
                    (SELECT xxas_new_direct_report
                              (trunc(sysdate)
                              ,null
                              ,null
                              ,null)
                     FROM    dual
                    )
                   AS xxas_new_direct_report_tbl)
       END                                 new_direct_reports
      ,null                                new_manager_dr_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_SELECTED_PERSON_SUP_ID')
       END                                new_manager_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PASSED_EFFECTIVE_DATE')
       END                                effective_from_ldw_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_COMMENTS')
       END                               comments_to_approver_p
      ,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
      ,'PQH_SS_ATTACHMENT'                        attachment_entity
      ,pend.transaction_id                        attachment_pk1
      ,pend.transaction_id                        approvers
FROM   per_all_people_f      ppf
      ,per_all_assignments_f paaf
      ,(SELECT hat.selected_person_id person_id
              ,hats.api_name
              ,hat.transaction_id
              ,hats.transaction_step_id
              ,'UPDATE'                                                   transaction_type
              ,hat.status                                                 hr_api_status_code
              ,xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
              ,hat.effective_date_option
        FROM   hr_api_transactions         hat
              ,hr_api_transaction_steps    hats
              ,hr_api_transaction_values   act
        WHERE  hat.transaction_id       = hats.transaction_id
        AND    hat.status          NOT IN ('D', 'N', 'ACTIVE', 'AC')
        AND    hats.api_name            = 'HR_SUPERVISOR_SS.PROCESS_API'
        AND    hats.transaction_step_id = act.transaction_step_id
        AND    act.name                 = 'P_ACTIVITY_NAME'
        AND    act.varchar2_value       = 'HR_CHANGE_MANAGER'
        AND    1 = (SELECT count(hats.transaction_step_id) 
                    FROM   hr_api_transaction_steps    hats
                    WHERE  hat.transaction_id       = hats.transaction_id
                   )
        ) pend
WHERE  xxas_mss_util_pkg.max_per_last_change_date(ppf.person_id) BETWEEN ppf.effective_start_date 
                                                                 AND     ppf.effective_end_date
AND    ppf.person_id     = paaf.person_id
AND    xxas_mss_util_pkg.max_asg_last_change_date(paaf.assignment_id) BETWEEN paaf.effective_start_date 
                                                                      AND     paaf.effective_end_date
--AND    (NVL(ppf.current_employee_flag,'N') = 'Y' OR NVL(ppf.current_npw_flag,'N') = 'Y')
AND    paaf.assignment_type IN ('E','C')
AND    ppf.person_id     = pend.person_id(+);

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_CHANGE_MANAGER_REVIEW_V'
   ,p_description            => 'Applaud Change Manager Review View');
END;
/

COMMIT;
EXIT;