/* $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_review_v
AS 
SELECT  xgv2.goal_id,
        xgv2.perf_plan_enrollment_id,
        xgv2.goal_type_code,
        xgv2.goal_id "id",
        'goal' "type",
        xgv2.goal,
        --xgv2.goal "title",
        CASE WHEN xprf.status_code NOT IN ('A','O') THEN xgv2.goal
                          ELSE xxas_perf_util_pkg.get_last_approved_goal(p_goal_id     => xgv2.goal_id)
        END "title",
        xprf.current_step_id "reviewStepId",
        --xgv2.goal_type_meaning||decode(xgv2.category_meaning,null,null,' - ')||xgv2.category_meaning "label",
        CASE WHEN xprf.status_code NOT IN ('A','O') 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",
        xgv2.perf_plan_id,
        xgv2.category_code,
        xgv2.access_code,
        xgv2.assignment_id,
        xgv2.parent_goal_id,
        xgv2.weighting_percent,
        xgv2.complete_percent,
        xgv2.start_date,
        xgv2.due_date,
        xprf.perf_review_form_id,
        xprf.current_step_id,
        xprf.current_step_person_id,        
        xgv2.status_code goal_status_code,
        xgv2.status_meaning goal_status_meaning,
        xgv2.goal_state goal_state,
        xgv2.perf_review_step_id,        
        xprf.status_code form_status_code,
        xprf.status_meaning form_status_meaning,
        xgv2.created_by goal_creator,
        xprf.person_id,
        CASE 
         WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_UPD_IN_REV', xgv2.goal_id, xprf.perf_review_form_id) = 'Y' THEN
	           'true'
	      ELSE
             'false'
	      END  "statusUpdateAllowed"
        ,xgv2.attribute_category
        ,xgv2.attribute1
        ,xgv2.attribute2
        ,xgv2.attribute3
        ,xgv2.attribute4
        ,xgv2.attribute5
        ,xgv2.attribute6
        ,xgv2.attribute7
        ,xgv2.attribute8
        ,xgv2.attribute9
        ,xgv2.attribute10
        ,xgv2.attribute11
        ,xgv2.attribute12
        ,xgv2.attribute13
        ,xgv2.attribute14
        ,xgv2.attribute15
        ,xgv2.attribute16
        ,xgv2.attribute17
        ,xgv2.attribute18
        ,xgv2.attribute19
        ,xgv2.attribute20
        ,xgv2.attribute21
        ,xgv2.attribute22
        ,xgv2.attribute23
        ,xgv2.attribute24
        ,xgv2.attribute25
        ,xgv2.attribute26
        ,xgv2.attribute27
        ,xgv2.attribute28
        ,xgv2.attribute29
        ,xgv2.attribute30
        ,cast(multiset
               (SELECT a.attached_document_id
                          ,a.seq_num sequence_number
                          ,a.entity_name
                          ,a.pk1_value
                          ,a.pk2_value
                          ,a.pk3_value
                          ,a.pk4_value
                          ,a.pk5_value
                          ,c.name category_code
                          ,c.user_name category_name
                          ,d.document_id
                          ,d.file_name
                          ,d.title
                          ,d.description
                          ,l.file_id
                          ,l.file_content_type mime_type
                          ,l.file_format lob_format
                          ,fnd_gfm.construct_download_url(fnd_web_config.gfm_agent, l.file_id) file_url
                          ,null wp_link
                          ,a.creation_date
                          ,xxas_wf_role_util_pkg.get_display_name_from_user(a.created_by) created_by
                          ,a.last_update_date
                          ,xxas_wf_role_util_pkg.get_display_name_from_user(a.last_updated_by) last_updated_by
                 FROM   fnd_attached_documents a
                       ,fnd_documents_vl d
                       ,fnd_lobs l
                       ,fnd_document_categories_vl c
                       ,fnd_document_datatypes_vl t
                 WHERE  t.name = 'FILE'
                 AND    d.media_id = l.file_id
                 AND    a.document_id = d.document_id
                 AND    a.category_id = c.category_id
                 AND    d.datatype_id = t.datatype_id
                 AND    a.pk1_value IS NOT NULL
                 AND    a.pk1_value = to_char(xgv2.goal_id)
                 AND    a.entity_name = 'PQH_SS_ATTACHMENT'
                 ORDER  BY a.seq_num)
                                 AS xxas_attachment_table) ATTACHMENTS  
        --('/view-goal/' ||goal_id)"href",
        --'modal' "target"
        ,XMLFOREST
          (xgv2.status_code    AS "id",
           xgv2.status_meaning AS "label")  "status"
        ,CASE WHEN xprf.status_code NOT IN ('A','O') AND xgv2.weighting_percent IS NOT NULL 
              THEN XMLForest(xxas_com_msg_pkg.get_string('XXAS_GOAL_WEIGHTING')          AS "label",
                               xgv2.weighting_percent AS "value")
              WHEN xprf.status_code IN ('A','O') 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(xxas_com_msg_pkg.get_string('XXAS_GOAL_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(xgv2.goal_id,xprf.perf_review_form_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",
                                                     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))
                    )
                  ,(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",
                                                    xprf.current_step_id AS "reviewStepId",
                                                    'note' AS "type",
                                                    'false' AS "required",
                                                    null AS "body",
                                                    'true' AS "editMode",
                                                    'true' AS "disableAutoSave",
                                                     3900   AS "maxlength",
                                                     xxas_com_msg_pkg.get_string('XXAS_PERF_REV_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_perf_util_pkg.curr_step_goal_comm_acc_flag 
                                                                  (p_perf_review_form_id  => xprf.perf_review_form_id
                                                                  ,p_access_type          =>   'R'
                                                                   ) = 'Y' THEN
                                                              XMLElement(
                                                                "rating",
                                                                      XMLElement
                                                                          ("iconSteps"
                                                                          ,XMLAttributes('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
                                                                          , (SELECT 
                                                                          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")
                                                                                       ) ORDER BY lookup_code
                                                                                 ) 
                                                                                 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-in-review/goal-review-create/' ||
                                                                      xgv2.goal_id ||
                                                                      '/' ||
                                                                      xprf.perf_review_form_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_IN_REV',xgv2.goal_id,xprf.perf_review_form_id) = 'Y'
                                          )-- Empty Item for New comment
                                        ,(SELECT XMLAGG(
                                                   XMLELEMENT("item",
                                                     XMLFOREST(xgrv.goal_review_id AS "id",
                                                               xgrv.goal_id AS "parentId",
                                                               xprf.current_step_id 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_REV_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 -- actions
                                                                     (p_goal_id => xgrv.goal_id
                                                                     ,p_goal_review_id => xgrv.goal_review_id
                                                                     ,p_perf_review_form_id => xprf.perf_review_form_id).get_xml() 
                                                                 ),
                                                               CASE WHEN xxas_perf_util_pkg.curr_step_goal_comm_acc_flag 
                                                                           (p_perf_review_form_id  => xprf.perf_review_form_id
                                                                           ,p_access_type          =>   'R'
                                                                            ) = 'Y' THEN
                                                                    XMLElement
                                                                      ("rating",
                                                                         XMLElement
                                                                          ("iconSteps"
                                                                          ,XMLAttributes('true' AS "json:force-array",'http://json.org/' AS "xmlns:json" )
                                                                          , (SELECT 
                                                                          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")
                                                                                       ) ORDER BY lookup_code
                                                                                 ) 
                                                                                 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"
                                                                                  ,xgrv.rating AS "value"
                                                                                  ,'false'     AS "required"
                                                                                 )
                                                                      ) 
                                                               END,                                                    
                                                               XMLELEMENT("links",
                                                                 XMLELEMENT("comments",
                                                                   XMLFOREST(xxas_com_rest_util_pkg.uri_from_path
                                                                     ('goals-in-review/goal-review-update/' ||
                                                                      xgrv.goal_review_id ||
                                                                      '/' ||
                                                                      xprf.perf_review_form_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_IN_REV',
                                                  xgrv.goal_review_id,xprf.perf_review_form_id) = 'Y'
                                          )
                                            )
                                    ) FROM dual
                   )
----------------------------------------------------------------------------
                  ).extract('linked/*')"linked"
        ,XMLELEMENT("status",
           XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('goals-in-review/' ||xgv2.goal_id || '/' || xprf.perf_review_form_id||'?refresh=Y')AS "href")) "links"
      FROM  xxas_goals_v xgv2,
            xxas_perf_review_forms_v xprf
      WHERE xprf.perf_plan_enrollment_id = xgv2.perf_plan_enrollment_id
        AND (  xprf.status_code NOT IN ('A','O') OR xxas_perf_util_pkg.was_goal_visible(xgv2.goal_id) = 'Y'  );
                    
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GOAL_DETAILS_REVIEW_V'
   ,p_description            => 'Applaud Performance Goal Details within performance review');
END;
/
COMMIT;
EXIT;
