/* $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_goals_list_review_v
AS
 SELECT xg.goal_id,
        xg.perf_plan_enrollment_id,
        xg.goal_type_code,
        xg.goal_id "id",
        'goal' "type",
        xg.goal "title",
        xg.status_code goal_status_code,
        xg.status_meaning goal_status_meaning,
        xg.goal_type_meaning,
        xg.goal_type_meaning||decode(xg.category_meaning,null,null,' - ')||xg.category_meaning "label",
        xg.creation_date,
        xg.last_update_date,
        xg.goal_state goal_state,
        xg.perf_review_step_id,
        xg.category_code,
        xg.category_meaning,
        xprf.status_code form_status_code,
        xprf.status_meaning form_status_meaning,
        xg.created_by goal_creator,
        xprf.perf_review_form_id,
        xprf.person_id,
        xprf.current_step_id,
        CASE 
         WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_UPD_IN_REV', xg.goal_id, xprf.perf_review_form_id) = 'Y' THEN
	           'true'
	      ELSE
             'false'
	      END  "statusUpdateAllowed",
        xprf.current_step_person_id,
        ('/view-goal-in-review/' ||xg.goal_id||'_'||xprf.perf_review_form_id)"href",
        'modal' "target"
        ,XMLFOREST
               (xg.status_code AS "id",
                xg.status_meaning AS "label")"status"
        ,decode(xg.weighting_percent, NULL, NULL,
                 XMLELEMENT("data",
                         XMLFOREST(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING') AS "label",
                                 xg.weighting_percent AS "value")).extract('/data/*')) "weighting"
        ,XmlElement( "linked",
                   xxas_goals_pkg.goal_comment_actions(xg.goal_id,xprf.perf_review_form_id).get_xml()
                   ,(SELECT XMLELEMENT("statuses",
                    XMLATTRIBUTES('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
                         ,XMLAGG(XMLELEMENT(NAME "statuses",XMLFOREST(Lookup_code AS "id",
                                                            meaning AS "label",
                                                            xprf.current_step_id "reviewStepId",
                                                            nvl(description,'positive') AS "color"))))
                          FROM hr_lookups hrl
                          WHERE hrl.lookup_type = 'XXAS_GOAL_STATUS'
                          AND hrl.enabled_flag = 'Y'
                          AND trunc(sysdate )
                           BETWEEN nvl(hrl.start_date_active,
                              trunc(sysdate))
                            AND nvl(hrl.end_date_active,
                              trunc(sysdate)))
                              ).extract('linked/*')"linked"
        ,XMLELEMENT("self",
                 XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('goals-in-review/' ||xg.goal_id|| '/' || xprf.perf_review_form_id)AS "href")) "links"
   FROM xxas_perf_review_forms_v xprf,
        xxas_goals_v xg
  WHERE xprf.perf_plan_enrollment_id = xg.perf_plan_enrollment_id;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GOALS_LIST_REVIEW_V'
   ,p_description            => 'Applaud Performance Goals List within a performance review');
END;
/

COMMIT;
EXIT;

