/* $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_goal_details_v
AS 
SELECT  goal_id,
        perf_plan_enrollment_id,
        goal_type_code,
        goal_id "id",
        'goal' "type",
         CASE WHEN xgv2.goal_state = 'A' THEN xgv2.goal
                          ELSE xxas_perf_util_pkg.get_last_approved_goal(p_goal_id     => xgv2.goal_id)
         END "title",
       -- xgv2.goal_type_meaning||decode(xgv2.category_meaning,null,null,' - ')||xgv2.category_meaning "label",
       CASE WHEN xgv2.goal_state = 'A' THEN xgv2.goal_type_meaning 
                                           || DECODE(xgv2.category_meaning,null,null,' - ')
                                           || xgv2.category_meaning
            ELSE xxas_util_pkg.decode_lookup ('XXAS_GOAL_TYPE'
                                             ,xxas_perf_util_pkg.get_last_approved_goal_info
                                               (p_goal_id => xgv2.goal_id
                                               ,p_column_name => 'GOAL_TYPE_CODE')) 
                || DECODE(xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xgv2.goal_id
                                                               ,p_column_name => 'CATEGORY_CODE') ,null,null,' - ')
                ||xxas_util_pkg.decode_lookup ('XXAS_GOAL_CATEGORY'
                                               ,xxas_perf_util_pkg.get_last_approved_goal_info
                                                 (p_goal_id => xgv2.goal_id
                                                 ,p_column_name => 'CATEGORY_CODE'))
       END "label",
          "statusUpdateAllowed",
        goal_state goal_state,
        perf_review_step_id,
        person_id
        --('/view-goal/' ||goal_id)"href",
        --'modal' "target"
        ,XMLFOREST
          (status_code    AS "id",
           status_meaning AS "label")"status"
        ,CASE WHEN xgv2.goal_state = 'A' AND xgv2.weighting_percent IS NOT NULL 
              THEN XMLForest('Weighting'          AS "label",
                               xgv2.weighting_percent AS "value")
              WHEN xgv2.goal_state != 'A' AND xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xgv2.goal_id
                                                                                     ,p_column_name => 'WEIGHTING_PERCENT') IS NOT NULL  
                             THEN XMLForest('Weighting'          AS "label",
                                               xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xgv2.goal_id
                                                                                     ,p_column_name => 'WEIGHTING_PERCENT') AS "value")                                                                           
         END "weighting"
        ,XMLELEMENT
          ("linked"
            ,xxas_goals_pkg.goal_comment_actions(p_goal_id => goal_id).get_xml() -- actions
            ,(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))
              )
            ,(SELECT XMLELEMENT("comments",
                       XMLATTRIBUTES('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" ),
                         XMLConcat(
                           (SELECT XMLELEMENT(
                              "item", -- Empty Item for New comment
                              XMLFOREST(0 AS "id",
                                        xgv2.goal_id AS "parentId",
                                        0 AS "reviewStepId",
                                        'note' AS "type",
                                        null AS "body",
                                        'false' AS "required",
                                        'true' AS "editMode",
                                        'true' AS "disableAutoSave",
                                        3900   AS "maxlength",
                                        xxas_com_msg_pkg.get_string('XXAS_PERF_PLACEHOLDER') AS "placeholder",
                                        xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                        xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                        nvl(xxas_util_pkg.get_formatted_name
                                         (papf.full_name
                                         ,papf.global_name
                                         ,papf.local_name
                                         ,papf.first_name
                                         ,papf.last_name)
                                         ,papf.last_name) "author"
                                        ),
                              XMLElement
                                     ("linked"
                                         ,XMLElement
                                         ("image"
                                         ,XMLElement
                                            ("self"
                                            ,xxas_com_rest_util_pkg.uri_from_path
                                              ('people/' || nvl(fnd_global.employee_id, -1) || '/photo')
                                            )
                                         ,CASE WHEN fnd_global.employee_id IS NOT NULL THEN
                                            XMLForest
                                              (xxas_per_image_pkg.url(fnd_global.employee_id, t.last_update_date, 'SMALL') "small"
                                              ,xxas_per_image_pkg.url(fnd_global.employee_id, t.last_update_date, 'MEDIUM') "medium"
                                              ,xxas_perf_util_pkg.get_initials(fnd_global.employee_id,sysdate) "initials"
                                              )
                                          END
                                         ) 
                                     ),
                              CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_RATING',
                                                                       xgv2.goal_id) = 'Y' THEN 
                                          XMLElement
                                           ("rating",
                                             (SELECT XMLElement
                                                       ("iconSteps"
                                                        ,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(xxas_perf_util_pkg.get_rating_icon_color(xgv2.perf_plan_enrollment_id),'energized') AS "color"
                                                                       )
                                                                     )
                                                                )
                                                        )
                                              FROM hr_lookups hrl
                                             WHERE hrl.lookup_type = xxas_perf_util_pkg.get_goal_rating_lookup(xgv2.perf_plan_enrollment_id)
                                               AND hrl.enabled_flag = 'Y'
                                               AND trunc(sysdate )
                                                   BETWEEN nvl(hrl.start_date_active,
                                                           trunc(sysdate))
                                                       AND nvl(hrl.end_date_active,
                                                           trunc(sysdate))
                                             ),
                                            -------------
                                            XMLFOREST(nvl(xxas_perf_util_pkg.get_rating_icon_name(xgv2.perf_plan_enrollment_id),'starfilled') AS "icon",
                                                      CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(xgv2.perf_plan_enrollment_id) IS NULL 
                                                                 AND xxas_perf_util_pkg.get_rating_icon_color(xgv2.perf_plan_enrollment_id) IS NULL 
                                                           THEN 'true' 
                                                           ELSE 'false'
                                                      END AS "showAsSelectSingle",
                                                      'false' AS "required")
                                           )
                              END,
                              ----------------------
                              XMLELEMENT("links",
                                 XMLELEMENT( "comments",
                                   XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('goals/goal-review-create/' ||xgv2.goal_id)AS "href"))
                                        ) "links"
                              ----------------------                        
                                             )
                                      FROM dual,
                                           xxas_per_thumbnails t,
                                      per_all_people_f papf
                                      WHERE  papf.person_id = t.person_id (+)
                                      AND fnd_global.employee_id = papf.person_id
                                      AND TRUNC ( sysdate )
                                              BETWEEN papf.effective_start_date
                                                  AND papf.effective_end_date
                                      AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_CRE',xgv2.goal_id) = 'Y'
                           ),
                          (SELECT XMLAGG(
                             XMLELEMENT("item",
                              XMLFOREST(xgrv.goal_review_id AS "id",
                                        xgrv.goal_id AS "parentId",
                                        0 AS "reviewStepId",
                                        'note' AS "type",
                                        'false' AS "required",
                                        xgrv.comments AS "body",
                                        'true' AS "disableAutoSave",
                                        3900   AS "maxlength",
                                        xxas_com_msg_pkg.get_string('XXAS_PERF_PLACEHOLDER') AS "placeholder",
                                        xxas_com_msg_pkg.get_string('XXAS_RB_COM_SAVE') AS "submitButtonText",
                                        xxas_com_msg_pkg.get_string('XXAS_RB_COM_CAN')  AS "cancelButtonText",
                                        xxas_util_pkg.days_ago(xgrv.last_update_date) 
                                        || CASE WHEN xgrv.perf_review_step_id IS NOT NULL THEN 
                                              ' (' ||xxas_perf_util_pkg.get_form_step_name(xgrv.perf_review_step_id) 
                                              || ')'
                                            END AS "dateCreatedFormatted", 
                                        nvl(xxas_util_pkg.get_formatted_name
                                         (papf.full_name
                                         ,papf.global_name
                                         ,papf.local_name
                                         ,papf.first_name
                                         ,papf.last_name)
                                         ,papf.last_name) "author")
                                         ,XMLElement
                                            ("linked"
                                            ,XMLElement
                                              ("image"
                                              ,XMLElement
                                               ("self"
                                               ,xxas_com_rest_util_pkg.uri_from_path
                                                 ('people/' || nvl(xgrv.reviewer_person_id, -1) || '/photo')
                                               )
                                            ,CASE WHEN xgrv.reviewer_person_id IS NOT NULL THEN
                                               XMLForest
                                                 (xxas_per_image_pkg.url(xgrv.reviewer_person_id, t.last_update_date, 'SMALL') "small"
                                                 ,xxas_per_image_pkg.url(xgrv.reviewer_person_id, t.last_update_date, 'MEDIUM') "medium"
                                                 ,xxas_perf_util_pkg.get_initials(xgrv.reviewer_person_id,sysdate) "initials"
                                                 )
                                             END
                                              ) 
                                            ,xxas_goal_review_pkg.goal_comment_actions(p_goal_id => xgrv.goal_id -- actions
                                                                                       ,p_goal_review_id => xgrv.goal_review_id).get_xml()
                                            )
                                         ,CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_RATING'
                                                                                  ,xgv2.goal_id) = 'Y' THEN 
                                                    XMLElement(
                                                      "rating",
                                                      (SELECT XMLElement
                                                                ("iconSteps"
                                                                ,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(xxas_perf_util_pkg.get_rating_icon_color(xgv2.perf_plan_enrollment_id),'energized') AS "color")))
                                                                  )
                                                         FROM hr_lookups hrl
                                                        WHERE hrl.lookup_type = xxas_perf_util_pkg.get_goal_rating_lookup(xgv2.perf_plan_enrollment_id)
                                                          AND hrl.enabled_flag = 'Y'
                                                          AND trunc(sysdate )
                                                              BETWEEN nvl(hrl.start_date_active,
                                                                      trunc(sysdate))
                                                                  AND nvl(hrl.end_date_active,
                                                                      trunc(sysdate))
                                                      ),
                                                      -------------
                                                      XMLFOREST(nvl(xxas_perf_util_pkg.get_rating_icon_name(xgv2.perf_plan_enrollment_id),'starfilled') AS "icon"
                                                               ,xgrv.rating AS "value"
                                                               ,'false' AS "required"
                                                               ,CASE WHEN xxas_perf_util_pkg.get_rating_icon_name(xgv2.perf_plan_enrollment_id) IS NULL 
                                                                          AND xxas_perf_util_pkg.get_rating_icon_color(xgv2.perf_plan_enrollment_id) IS NULL 
                                                                     THEN 'true' 
                                                                     ELSE 'false'
                                                                END AS "showAsSelectSingle")
                                                              )
                                          END
                                         ,XMLELEMENT("links",
                                                XMLELEMENT( "comments",
                                                          XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('goals/goal-review-update/' ||xgrv.goal_review_id)AS "href"))
                                                    ) "links"
                                       )ORDER BY xgrv.last_update_date desc
                                        )
                           FROM xxas_goal_reviews_v xgrv,
                                xxas_per_thumbnails t,
                                per_all_people_f papf
                           WHERE xgrv.goal_id = xgv2.goal_id
                           AND papf.person_id = t.person_id (+)
                           AND xgrv.reviewer_person_id = papf.person_id
                           AND trunc ( sysdate )
                               BETWEEN papf.effective_start_date
                                  AND papf.effective_end_date
                           AND xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_REV_VIEW',xgrv.goal_review_id) = 'Y'
                          )
                          )
                          ) FROM dual
              )
----------------------------------------------------------------------------
          ).extract('linked/*')"linked"
         ,XMLELEMENT("status",
                 XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('goals/' ||goal_id||'?refresh=Y')AS "href")
                    ) "links"
      FROM  xxas_goals_v xgv2;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GOAL_DETAILS_V'
   ,p_description            => 'Applaud Performance Goal Details');
END;
/

COMMIT;
EXIT;