/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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

CREATE OR REPLACE VIEW xxas_mss_blocking_actions_v
AS
SELECT  
      ts.creator_person_id
      ,txn.selected_person_id
      ,txn.selected_person_id person_id
      ,'flexible' "type"
      ,xxas_mss_util_pkg.get_page_href(null,txn.item_key)  AS "href"
      ,xxas_mss_util_pkg.get_page_icon(txn.item_key)  AS "icon"
      ,'modal' "target"
      ,xxas_mss_util_pkg.mss_pg_request_message(ts.transaction_step_id) "line1"  
      ,xxas_com_msg_pkg.get_message('XXAS','XXAS_MSS_REQUESTED_BY_DATE','CREATION_DATE',ts.creation_date)|| ' '||xxas_mss_util_pkg.person_full_name(ts.creator_person_id)
        || (select xxas_mss_util_pkg.get_pa_status_color(txn.transaction_id) from dual)	  "line3" 
      ,ts.transaction_id
      ,ts.transaction_step_id
      ,ts.item_key
      ,Initcap(xxas_selfservice_workflow_pkg.transcode_status(txn.status)) "status"
	  ,xxas_mss_util_pkg.get_pa_icon_color(txn.transaction_id) AS "statusColor"
      ,XMLElement
        ("data"
        ,XMLElement
           ("image"
           ,XMLElement
              ("self"
              ,xxas_com_rest_util_pkg.uri_from_path
                ('people/' || nvl(txn.selected_person_id, -1) || '/photo'))
           ,CASE WHEN txn.selected_person_id IS NOT NULL THEN
              XMLForest
                (xxas_per_image_pkg.url(txn.selected_person_id, t.last_update_date, 'SMALL') "small"
                ,xxas_per_image_pkg.url(txn.selected_person_id, t.last_update_date, 'MEDIUM') "medium"
                ,xxas_per_image_pkg.url(txn.selected_person_id, t.last_update_date, 'FULL_SIZE') "fullSize"
                )
            END
           )
       ).extract('/data/*') AS "linked"
FROM   hr_api_transaction_steps ts
      ,hr_api_transactions      txn
      ,xxas_per_thumbnails      t      
WHERE  ts.transaction_id   = txn.transaction_id
AND    txn.status         IN ('Y','YS','RO','ROS','E')
AND    txn.selected_person_id = t.person_id(+)
AND    ts.transaction_step_id = 
        (
        SELECT max(transaction_step_id)
        from hr_api_transaction_steps ts1
        where ts1.transaction_id = txn.transaction_id 
        AND ts.item_type      = 'HRSSA'
        AND ((upper(ts.api_name) IN ('HR_SUPERVISOR_SS.PROCESS_API'
                                     ,'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
                                     ,'HR_PAY_RATE_SS.PROCESS_API'
                                     ,'HR_TERMINATION_SS.PROCESS_API'
                                     ,'HR_CWK_TERMINATION_SWI.PROCESS_API')
               AND    ts.processing_order = 1
              )
              OR upper(ts.api_name) = 'PER_SSHR_CHANGE_PAY.PROCESS_API'
            )
        AND    EXISTS
               (SELECT NULL
                FROM hr_api_transaction_values tsv
                WHERE tsv.transaction_step_id = ts.transaction_step_id
                AND tsv.name                  = 'P_REVIEW_PROC_CALL'
                AND tsv.varchar2_value       IS NOT NULL
               )    
        );
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_MSS_BLOCKING_ACTIONS_V'
   ,p_description            => 'Applaud MSS Blocking Actions View');
END;
/
COMMIT;
EXIT;

