/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'FunctionGrants';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'View and administer function grants'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetFunctionGrants'
    ,p_description        => 'Get function grants'
    ,p_path               => 'function-grants/{functionId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
      SELECT XMLElement
               ("data"
               ,XMLAttributes
                 ('true' as "json:force-array"
                 ,'http://json.org/' as "xmlns:json")
               ,XMLAgg
                 (XMLElement
                   ("item"
                   ,XMLForest
                     ('flexible' "type"
                     ,g.grant_guid "id"
                     ,xxas_com_sec_pkg.grant_item_actions
                       (decode(upper(substr(g.function_name, 1, 4))
                              ,'XXAS'
                              ,'N'
                              ,'Y')).get_xml() "linked"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') != nvl(g.grant_obj_name,'XXXX')
                           THEN '&lt;font color="#FF9900"&gt;Does not apply: Object on block is different to grant&lt;/font&gt;' 
                           WHEN trunc(sysdate) > nvl(g.end_date,trunc(sysdate))
                           THEN '&lt;font color="#FF9900"&gt;Does not apply: Grant has been ended&lt;/font&gt;'
                           ELSE null
                      END  "line2"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') = nvl(g.grant_obj_name,'XXXX') and (trunc(sysdate) between nvl(g.start_date,trunc(sysdate)) and nvl(g.end_date,trunc(sysdate)))
                           THEN '#499324'
                           ELSE '#FF9900'
                      END  "statusColor"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') = nvl(g.grant_obj_name,'XXXX') and (trunc(sysdate) between nvl(g.start_date,trunc(sysdate)) and nvl(g.end_date,trunc(sysdate))) 
                           THEN 'tick'
                           ELSE 'alert'
                      END  "icon"
                     ,decode
                       (g.role_display_name
                       ,'GLOBAL'
                       ,'All authenticated users'
                       ,g.role_display_name) "line1"
                     ,CASE WHEN g.grant_obj_name IS NULL THEN
                                null
                           WHEN g.grant_instance_type = 'GLOBAL' THEN
                                g.grant_obj_name || ' (all rows)'
                           ELSE g.grant_obj_name || ' (' || g.instance_set_name || ')'
                      END "line3")
                   )
                 )
               ) "data"
      FROM   TABLE(xxas_com_sec_pkg.get_function_grants({pathParam.functionId})) g
      ORDER BY g.role_display_name
        ]'
    );
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetEditAboumtmeFunctionGrants'
    ,p_description        => 'Get edit aboutme function grants'
    ,p_path               => 'edit-aboutme-grants'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
      SELECT XMLElement
               ("data"
               ,XMLAttributes
                 ('true' as "json:force-array"
                 ,'http://json.org/' as "xmlns:json")
               ,XMLAgg
                 (XMLElement
                   ("item"
                   ,XMLForest
                     ('flexible' "type"
                     ,g.grant_guid "id"
                     ,xxas_com_sec_pkg.grant_item_actions
                       (decode(upper(substr(g.inheritance, 1, 4))
                              ,'XXAS'
                              ,'N'
                              ,'Y')).get_xml() "linked"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') != nvl(g.grant_obj_name,'XXXX')
                           THEN '&lt;font color="#FF9900"&gt;Does not apply: Object on block is different to grant&lt;/font&gt;' 
                           WHEN trunc(sysdate) > nvl(g.end_date,trunc(sysdate))
                           THEN '&lt;font color="#FF9900"&gt;Does not apply: Grant has been ended&lt;/font&gt;'
                           ELSE null
                      END  "line2"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') = nvl(g.grant_obj_name,'XXXX') and (trunc(sysdate) between nvl(g.start_date,trunc(sysdate)) and nvl(g.end_date,trunc(sysdate)))
                           THEN '#499324'
                           ELSE '#FF9900'
                      END  "statusColor"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') = nvl(g.grant_obj_name,'XXXX') and (trunc(sysdate) between nvl(g.start_date,trunc(sysdate)) and nvl(g.end_date,trunc(sysdate))) 
                           THEN 'tick'
                           ELSE 'alert'
                      END  "icon"
                     ,decode
                       (g.role_display_name
                       ,'GLOBAL'
                       ,'All authenticated users'
                       ,g.role_display_name) "line1"
                     ,CASE WHEN g.grant_obj_name IS NULL THEN
                                null
                           WHEN g.grant_instance_type = 'GLOBAL' THEN
                                g.grant_obj_name || ' (all rows)'
                           ELSE g.grant_obj_name || ' (' || g.instance_set_name || ')'
                      END "line3")
                   )
                 )
               ) "data"
      FROM   TABLE(xxas_com_sec_pkg.get_function_grants(xxas_com_sec_pkg.get_function_id('XXAS_DATA_ABOUTME_UPDATE'))) g
      ORDER BY g.role_display_name
        ]'
    );
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetAddTagGrants'
    ,p_description        => 'Get Add Tag function grants'
    ,p_path               => 'add-tag-grants'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
      SELECT XMLElement
               ("data"
               ,XMLAttributes
                 ('true' as "json:force-array"
                 ,'http://json.org/' as "xmlns:json")
               ,XMLAgg
                 (XMLElement
                   ("item"
                   ,XMLForest
                     ('flexible' "type"
                     ,g.grant_guid "id"
                     ,xxas_com_sec_pkg.grant_item_actions
                       (decode(upper(substr(g.inheritance, 1, 4))
                              ,'XXAS'
                              ,'N'
                              ,'Y')).get_xml() "linked"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') != nvl(g.grant_obj_name,'XXXX')
                           THEN '&lt;font color="#FF9900"&gt;Does not apply: Object on block is different to grant&lt;/font&gt;' 
                           WHEN trunc(sysdate) > nvl(g.end_date,trunc(sysdate))
                           THEN '&lt;font color="#FF9900"&gt;Does not apply: Grant has been ended&lt;/font&gt;'
                           ELSE null
                      END  "line2"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') = nvl(g.grant_obj_name,'XXXX') and (trunc(sysdate) between nvl(g.start_date,trunc(sysdate)) and nvl(g.end_date,trunc(sysdate)))
                           THEN '#499324'
                           ELSE '#FF9900'
                      END  "statusColor"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') = nvl(g.grant_obj_name,'XXXX') and (trunc(sysdate) between nvl(g.start_date,trunc(sysdate)) and nvl(g.end_date,trunc(sysdate))) 
                           THEN 'tick'
                           ELSE 'alert'
                      END  "icon"
                     ,decode
                       (g.role_display_name
                       ,'GLOBAL'
                       ,'All authenticated users'
                       ,g.role_display_name) "line1"
                     ,CASE WHEN g.grant_obj_name IS NULL THEN
                                null
                           WHEN g.grant_instance_type = 'GLOBAL' THEN
                                g.grant_obj_name || ' (all rows)'
                           ELSE g.grant_obj_name || ' (' || g.instance_set_name || ')'
                      END "line3")
                   )
                 )
               ) "data"
      FROM   TABLE(xxas_com_sec_pkg.get_function_grants(xxas_com_sec_pkg.get_function_id('XXAS_DATA_PERTAG_CREATE'))) g
      ORDER BY g.role_display_name
        ]'
    );
    
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetDeleteTagGrants'
    ,p_description        => 'Get delete tag function grants'
    ,p_path               => 'del-tag-grants'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
      SELECT XMLElement
               ("data"
               ,XMLAttributes
                 ('true' as "json:force-array"
                 ,'http://json.org/' as "xmlns:json")
               ,XMLAgg
                 (XMLElement
                   ("item"
                   ,XMLForest
                     ('flexible' "type"
                     ,g.grant_guid "id"
                     ,xxas_com_sec_pkg.grant_item_actions
                       (decode(upper(substr(g.inheritance, 1, 4))
                              ,'XXAS'
                              ,'N'
                              ,'Y')).get_xml() "linked"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') != nvl(g.grant_obj_name,'XXXX')
                           THEN '&lt;font color="#FF9900"&gt;Does not apply: Object on block is different to grant&lt;/font&gt;' 
                           WHEN trunc(sysdate) > nvl(g.end_date,trunc(sysdate))
                           THEN '&lt;font color="#FF9900"&gt;Does not apply: Grant has been ended&lt;/font&gt;'
                           ELSE null
                      END  "line2"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') = nvl(g.grant_obj_name,'XXXX') and (trunc(sysdate) between nvl(g.start_date,trunc(sysdate)) and nvl(g.end_date,trunc(sysdate)))
                           THEN '#499324'
                           ELSE '#FF9900'
                      END  "statusColor"
                     ,CASE WHEN nvl(g.function_obj_name,'XXXX') = nvl(g.grant_obj_name,'XXXX') and (trunc(sysdate) between nvl(g.start_date,trunc(sysdate)) and nvl(g.end_date,trunc(sysdate))) 
                           THEN 'tick'
                           ELSE 'alert'
                      END  "icon"
                     ,decode
                       (g.role_display_name
                       ,'GLOBAL'
                       ,'All authenticated users'
                       ,g.role_display_name) "line1"
                     ,CASE WHEN g.grant_obj_name IS NULL THEN
                                null
                           WHEN g.grant_instance_type = 'GLOBAL' THEN
                                g.grant_obj_name || ' (all rows)'
                           ELSE g.grant_obj_name || ' (' || g.instance_set_name || ')'
                      END "line3")
                   )
                 )
               ) "data"
      FROM   TABLE(xxas_com_sec_pkg.get_function_grants(xxas_com_sec_pkg.get_function_id('XXAS_DATA_PERTAG_DELETE'))) g
      ORDER BY g.role_display_name
        ]'
    );
  
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetFlexibleAddRowFunctionGrants'
    ,p_description        => 'Get Flexible Add Row function grants'
    ,p_path               => 'flex-add-row-function-grants/{functionId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT xxas_com_rest_mgr_pkg.get_xml_entity
              ('function-grants/' ||
              xxas_com_sec_pkg.get_function_id(xxas_com_sec_pkg.get_parameter_value(df.parameters, 'pDataFunction')||'_CREATE')) "data"
      FROM   fnd_form_functions df
      WHERE  df.function_id = {pathParam.functionId}
        ]'
    );
	
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetContactsAddRowFunctionGrants'
    ,p_description        => 'Get Contacts Add Row function grants'
    ,p_path               => 'contacts-add-row-function-grants/{functionId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT xxas_com_rest_mgr_pkg.get_xml_entity
              ('function-grants/' ||
              xxas_com_sec_pkg.get_function_id(xxas_com_sec_pkg.get_parameter_value(df.parameters, 'pDataFunction')||'_CREATE')) "data"
      FROM   fnd_form_functions df
      WHERE  df.function_id = {pathParam.functionId}
        ]'
    );
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetDataFunctionGrants'
    ,p_description        => 'Get data function grants'
    ,p_path               => 'data-function-grants/{functionId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT xxas_com_rest_mgr_pkg.get_xml_entity
              ('function-grants/' ||
              xxas_com_sec_pkg.get_function_id(xxas_com_sec_pkg.get_parameter_value(df.parameters, 'pDataFunction'))) "data"
      FROM   fnd_form_functions df
      WHERE  df.function_id = {pathParam.functionId}
        ]'
    );
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPsDataFunctionGrants'
    ,p_description        => 'Get Payslip data function grants'
    ,p_path               => 'ps-data-function-grants/{functionId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT xxas_com_rest_mgr_pkg.get_xml_entity
              ('function-grants/' ||
              xxas_com_sec_pkg.get_function_id(xxas_com_sec_pkg.get_parameter_value(df.parameters, 'pPayslipsDataFunction'))) "data"
      FROM   fnd_form_functions df
      WHERE  df.function_id = {pathParam.functionId}
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteGrants'
    ,p_path               => 'function-grants'
    ,p_description        => 'Deletes the given grants'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
        
          l_xml         XMLType;
          l_resp        xxas_http_resp_obj;
          l_request_xml XMLType;
          
        BEGIN
          
          IF xxas_wf_role_util_pkg.user_has_role
               ('UMX|XXAS_FUNCTIONAL_CONFIG') = 'N'
          THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          
            l_request_xml := {requestBody};
          
            xxas_com_sec_pkg.delete_grants
              (p_request_body => l_request_xml);
                
            l_resp := xxas_http_response
                        .ok(xxas_com_msg_pkg.get_success_message_xml
                             (xxas_com_msg_type(xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                             )
                           )
                        .build();
          END IF;
         
         {response} := l_resp;

        END
      ]'
    );

    
END;
/
COMMIT;
EXIT;