/* $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_v
AS
 SELECT goal_id,
        perf_plan_enrollment_id,
        goal_type_code,
        category_code,
        person_id,
        access_code,
        goal_id "id",
        'goal' "type",
        goal "title",
        goal_type_meaning,
        goal_type_meaning||decode(category_meaning,null,null,' - ')||category_meaning "label",
        category_meaning,
        status_meaning,
        creation_date,
        last_update_date,
        goal_state goal_state,
        perf_review_step_id,
          "statusUpdateAllowed",
        ('/view-goal/' ||goal_id)"href",
        'modal' "target"
        ,XMLFOREST
               (status_code AS "id",
                status_meaning AS "label")"status"
        ,decode(weighting_percent, NULL, NULL,
                 XMLELEMENT("data",
                         XMLFOREST(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING') AS "label",
                                 weighting_percent AS "value")).extract('/data/*')) "weighting"
        ,XmlElement( "linked",
                   xxas_goals_pkg.goal_comment_actions(p_goal_id => goal_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",
                                        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/' ||goal_id)AS "href")) "links"
   FROM  xxas_goals_v
    WHERE (perf_review_step_id IS NULL 
             OR 
           goal_state = 'A' );

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GOALS_LIST_V'
   ,p_description            => 'Applaud Performance Goals List');
END;
/

COMMIT;
EXIT;

