/* $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_mss_pending_requests_v 
AS
SELECT ts.creator_person_id
      ,txn.selected_person_id
      ,txn.selected_person_id person_id
      ,'note' "type"
      ,(CASE WHEN xxas_mss_util_pkg.mss_pg_request_view_link(ts.transaction_step_id) IS NOT NULL
             THEN xxas_com_rest_util_pkg.uri_from_app_route(xxas_mss_util_pkg.mss_pg_request_view_link(ts.transaction_step_id) || '/' || to_char(txn.selected_person_id) || '_' || ts.transaction_id || '_' || ts.transaction_step_id)
             ELSE (SELECT xxas_com_rest_util_pkg.uri_from_app_route('notification/' || wf.notification_id) 
                   FROM   wf_notifications wf
                   WHERE  wf.item_key = ts.item_key
                   AND    wf.status   = 'OPEN'
                   AND    wf.recipient_role IN (SELECT role_name
                                                FROM   wf_user_roles wr
                                                WHERE  wr.user_name = fnd_global.user_name
                                                UNION
                                                SELECT role_name
                                                FROM   wf_user_roles wr
                                                WHERE  wr.user_name IN (SELECT parameter1
                                                                        FROM   fnd_grants fg
                                                                        WHERE  fg.grantee_key   = fnd_global.user_name
                                                                        AND    instr(nvl(fg.parameter2,wf.message_type),wf.message_type) > 0
                                                                        AND    fg.INSTANCE_TYPE = 'SET'
                                                                        AND    fg.object_id  IN (SELECT fo.object_id from fnd_objects fo 
                                                                                                 WHERE  fo.database_object_name = 'WF_NOTIFICATIONS'
                                                                                                )
                                                                        AND    fg.menu_id       = (SELECT fm.menu_id
                                                                                                   FROM   fnd_menus fm
                                                                                                   WHERE  fm.menu_name = 'FND_WF_WORKLIST'
                                                                                                  )
                                                                       AND fg.INSTANCE_SET_ID   = (SELECT fis.instance_set_id
                                                                                                   FROM FND_OBJECT_INSTANCE_SETS fis
                                                                                                   WHERE fis.instance_set_name = 'WL_PROXY_ACCESS'
                                                                                                  )
                                                                        AND    trunc(SYSDATE) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(SYSDATE))
                                                                       )
                                               )
                  )
        END) "href"
      ,'modal' "target"
      ,xxas_mss_util_pkg.mss_pg_request_message(ts.transaction_step_id) || ' for ' || xxas_mss_util_pkg.person_full_name(txn.selected_person_id) "author"
      ,'Requested by ' || xxas_mss_util_pkg.person_full_name(ts.creator_person_id) "body"
      ,ts.transaction_id
      ,ts.transaction_step_id
      ,ts.item_key
      /*,case when (trunc(ts.creation_date) - trunc(sysdate)) between 0 and 3 then
        'timer'
       else  null end                                                                    AS "icon"
      ,case when (trunc(ts.creation_date) - trunc(sysdate)) between 0 and 3 then
        'assertive'
       else  null end                                                                    AS "iconColor"*/
      ,'bell' "icon"
      ,Initcap(xxas_selfservice_workflow_pkg.transcode_status(txn.status)) "status"
      ,to_char(ts.creation_date,'DD-MON-YYYY') "dateCreatedFormatted"
      ,CASE WHEN xxas_selfservice_workflow_pkg.transcode_status(txn.status) = 'F'
            THEN 'positive'
            WHEN xxas_selfservice_workflow_pkg.transcode_status(txn.status) = 'ERROR'
            THEN 'assertive'
            WHEN xxas_selfservice_workflow_pkg.transcode_status(txn.status) = 'APPROVED'
            THEN 'balanced'
            WHEN xxas_selfservice_workflow_pkg.transcode_status(txn.status) = 'RFC'
            THEN 'assertive'
            WHEN xxas_selfservice_workflow_pkg.transcode_status(txn.status) = 'SFL'
            THEN 'positive'
            WHEN xxas_selfservice_workflow_pkg.transcode_status(txn.status) = 'REVIEW'
            THEN 'energized'
            WHEN xxas_selfservice_workflow_pkg.transcode_status(txn.status) = 'PENDING'
            THEN 'energized'
       END "iconColor"
      ,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
      --,wf_notifications         wf
WHERE  ts.item_type      = 'HRSSA'
--AND ts.update_person_id = :2
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'
                             --,'PER_SSHR_CHANGE_PAY.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    ts.transaction_id   = txn.transaction_id
AND    txn.status         IN ('Y','YS','RO','ROS','E')
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
       )
AND    txn.selected_person_id = t.person_id(+);
/*AND    wf.item_key = ts.item_key
AND    wf.recipient_role IN 
                 (SELECT ROLE_NAME
                  FROM   wf_user_roles wr
                  WHERE  wr.user_name = fnd_global.user_name
                  UNION
                  SELECT ROLE_NAME
                  FROM   wf_user_roles wr
                  WHERE  wr.user_name IN
                           (SELECT parameter1
                            FROM   fnd_grants fg
                            WHERE  fg.grantee_key = fnd_global.user_name
                            AND    instr(nvl(fg.parameter2,wf.message_type),wf.message_type) > 0
                            AND    fg.INSTANCE_TYPE   = 'SET'
                            AND fg.OBJECT_ID       IN  (SELECT fo.object_id from fnd_objects fo 
                                                      where  fo.database_object_name = 'WF_NOTIFICATIONS'
                                                      )
                           AND Fg.MENU_ID       = (SELECT fm.menu_id
                                                   from   fnd_menus fm
                                                   where  fm.menu_name = 'FND_WF_WORKLIST'
                                                   )
                           AND fg.INSTANCE_SET_ID = (select fis.instance_set_id
                                                     from FND_OBJECT_INSTANCE_SETS fis
                                                     where fis.instance_set_name = 'WL_PROXY_ACCESS'
                                                     )
                            AND    trunc(SYSDATE) BETWEEN trunc(start_date) AND nvl(trunc(end_date),trunc(SYSDATE))
                            )
                 )
AND wf.status = 'OPEN'*/

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_mss_pending_requests_v'
   ,p_description            => 'Applaud Emp Leaver Request View');
END;
/

COMMIT;
EXIT;

