/* $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
                       := 'DataObjects';

BEGIN


  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Data Objects'
    ,p_path               => '/data-objects'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindDataObject'
    ,p_description        => 'Find Data Objects matching the given criteria'
    ,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
                     ("obj"
                     ,XMLForest
                       (rslt.object_id "id"
                       ,rslt.display_name "value"
                       ,rslt.display_name "label"
                       )
                     )
                   )
                 ) "data"
        FROM  fnd_objects_vl rslt
        WHERE rslt.object_id = nvl({requestParam.q}, rslt.object_id)
        ORDER BY rslt.display_name
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DataObjectforPageAction'
    ,p_description        => 'Find Data Objects'
    ,p_methods            => 'GET'
	  ,p_path               => 'objname'
    ,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
                     ("obj"
                     ,XMLForest
                       (rslt.obj_name "id"
                       ,rslt.display_name "value"
                       ,rslt.display_name "label"
                       )
                     )
                   )
                 ) "data"
        FROM  fnd_objects_vl rslt
        WHERE upper(rslt.obj_name) = upper({requestParam.q}) 
        OR  (upper(rslt.display_name) like upper({requestParam.q}||'%'))
        ORDER BY rslt.display_name
        ]'
    );
	
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DataObjectsList'
    ,p_description        => 'List of All Data Objects'
    ,p_methods            => 'GET'
    ,p_path               => 'list'
    ,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
                     ("object"
                     ,XMLForest
                       ('flexible' "type"
                       ,rslt.object_id "id"
                       ,rslt.display_name "line1"
                       ,rslt.database_object_name||' ('||XXAS_DATA_OBJECTS_PKG.GET_PK_COLUMNS_STRING(rslt.object_id)||')' "line3"
                       ,xxas_com_rest_util_pkg.uri_from_app_route('data-objects?modal=/view-object/'||rslt.object_id) "href"
                       )
                     )
                    ORDER BY rslt.last_update_date desc 
                   )
                 ) "data"
        FROM  fnd_objects_vl rslt
        WHERE rslt.obj_name like 'XXAS%' OR 
        rslt.obj_name like fnd_profile.value('XXAS_CUSTOM_META_PREFIX')||'%'
        
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DataObjects'
    ,p_description        => 'List of All Data Objects'
    ,p_methods            => 'GET'
    ,p_path               => 'obj-list'
    ,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
                     ("object"
                     ,XMLForest
                       ('flexible' "type"
                       ,rslt.object_id "id"
                       ,rslt.display_name "line1"
                       ,rslt.display_name "label"
                       )
                     )
                   )
                 ) "data"
        FROM  fnd_objects_vl rslt
        WHERE (rslt.obj_name like 'XXAS%' OR 
        rslt.obj_name like fnd_profile.value('XXAS_CUSTOM_META_PREFIX')||'%')
        AND ({requestParam.q} IS NOT NULL AND (upper(rslt.display_name) like upper({requestParam.q})||'%' or upper(rslt.obj_name) like upper({requestParam.q})||'%'))

        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DatabaseObjectsListInOneApplication'
    ,p_description        => 'List of All Database Objects'
    ,p_methods            => 'GET'
    ,p_path               => 'database-objects/{applicationId}'
    ,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
                     ("databaseObject"
                     ,XMLForest
                       ('flexible' "type"
                       ,rslt.id "id"
                       ,rslt.code "line2"
                       ,rslt.code "label"
                       )
                     )
                   )
                 ) "data"
        FROM  xxas_database_objects_v rslt
        WHERE rslt.application_id = {pathParam.applicationId}
        AND rslt.code LIKE UPPER({requestParam.q}||'%')
        
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DatabaseObjectsList'
    ,p_description        => 'List of All Database Objects'
    ,p_methods            => 'GET'
    ,p_path               => 'database-objects'
    ,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
                     ("databaseObject"
                     ,XMLForest
                       ('flexible' "type"
                       ,rslt.id "id"
                       ,rslt.code "line2"
                       ,rslt.code "label"
                       )
                     )
                   )
                 ) "data"
        FROM  xxas_database_objects_v rslt
        WHERE {requestParam.q} IS NOT NULL AND rslt.code LIKE UPPER({requestParam.q}||'%')
        
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DatabaseObjectColumnsList'
    ,p_description        => 'List of All Database Object Columns'
    ,p_methods            => 'GET'
    ,p_path               => 'columns/{objectId}'
    ,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
                     ("databaseObject"
                     ,XMLForest
                       ('flexible' "type"
                       ,rslt.column_name "id"
                       ,rslt.column_name "line2"
                       ,rslt.column_name "label"
                       )
                     )
                   )
                 ) "data"
        FROM  (SELECT table_id id, column_name FROM FND_COLUMNS WHERE TABLE_ID =
                (SELECT TABLE_ID FROM FND_TABLES WHERE TABLE_NAME =(
                  SELECT DATABASE_OBJECT_NAME FROM FND_OBJECTS_VL
                  WHERE OBJECT_ID = {pathParam.objectId})
                )
              UNION
              SELECT view_id id, column_name FROM fnd_view_columns WHERE view_id = 
                (SELECT view_id FROM fnd_views WHERE view_name = 
                  (SELECT DATABASE_OBJECT_NAME FROM FND_OBJECTS_VL
                      WHERE OBJECT_ID = {pathParam.objectId}))) rslt
       
        
        ]'
    );    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DataObjectsInstanceSetsList'
    ,p_description        => 'List of All Instance Sets of an object'
    ,p_methods            => 'GET'
    ,p_path               => 'instance-sets/{objectId}'
    ,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
                     ("instanceSet"
                     ,XMLForest
                       ('flexible' "type"
                       ,rslt.instance_set_id "id"
                       ,rslt.display_name "line1"
                       ,rslt.formatted_predicate "line3"
                       ,xxas_com_rest_util_pkg.uri_from_app_route('view-object/'||{pathParam.objectId}||'?modal=/view-instance-set/'||rslt.instance_set_id) "href"
                       )
                     )
                   )
                 ) "data"
        FROM  xxas_object_instance_sets_v rslt
        WHERE rslt.object_id = {pathParam.objectId}
        
        ]'
    );
 
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DataObjectsColumnsList'
    ,p_description        => 'List of All Columns of an object'
    ,p_methods            => 'GET'
    ,p_path               => 'columns1/{databaseObjectName}'
    ,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
                     ("instanceSet"
                     ,XMLForest
                       ('flexible' "type"
                       ,rslt.column_name "id"
                       ,rslt.column_name "value"
                       ,rslt.column_name "label"
                       
                       )
                     )
                   )
                 ) "data"
        FROM  user_tab_cols rslt
        WHERE UPPER(rslt.table_name) = UPPER({pathParam.databaseObjectName})
        
        ]'
    ); 
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindDataObjects'
    ,p_description        => 'Find Data Objects matching the given criteria'
    ,p_methods            => 'GET'
    ,p_path               => 'objects-by-name'
    ,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
                     ("obj"
                     ,XMLForest
                       (rslt.object_id "id"
                       ,rslt.display_name "value"
                       ,rslt.display_name "label"
                       )
                     )
                   )
                 ) "data"
        FROM  fnd_objects_vl rslt
        WHERE UPPER(rslt.display_name) like UPPER('%'||{requestParam.q}||'%')
        ORDER BY rslt.display_name
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetObjectInstancesets'
    ,p_description        => 'Get Object Instance Sets for the given object id'
    ,p_methods            => 'GET'
	  ,p_path               => 'set/{objId}'
    ,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
                     ("role"
                     ,XMLForest
                       (p.instance_set_id as "id"
                       ,p.display_name      as "label"
                       ,p.instance_set_name      as "value"
                       )
                     )
                   )
                 )"data"
    FROM ( SELECT 1 instance_set_id, 'All Rows' instance_set_name, 'All Rows' display_name
           FROM dual
           UNION
           SELECT  fo.instance_set_id, fo.instance_set_name, fo.display_name 
           FROM   fnd_object_instance_sets_vl fo
           WHERE  fo.object_id = {pathParam.objId}
           UNION
           SELECT 9999 instance_set_id, 'Create a new one', 'Create a new one'
           FROM dual          
          )P         
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteObject'
    ,p_description        => 'Deletes a data object'
    ,p_path               => 'delete-object/{objectId}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
          l_obj_name    VARCHAR2(1000);
          l_resp        xxas_http_resp_obj;
        BEGIN
          l_obj_name := xxas_data_objects_pkg.get_obj_name({pathParam.objectId});
          -- Check access to delete the plan
          IF xxas_com_sec_pkg.test_instance('XXAS_BA_DELETE_DATA_OBJECT',{pathParam.objectId},l_obj_name) = 'Y' THEN
               
            xxas_data_objects_pkg.delete_data_object({pathParam.objectId});
            
            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)
                             ,'self'
                             )
                           )
                        .build();
          
          ELSE
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          
          END IF;

          {response} := l_resp;
         
        END
      ]'
    );     
    
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetApplaudRoles'
    ,p_description        => 'Get roles'
    ,p_methods            => 'GET'
	  ,p_path               => 'roles'
    ,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
                     ("pred"
                     ,XMLForest
                       (p.name as "id"
                       ,p.display_name      as "label"
                       ,p.display_name      as "value"
                       )
                     )
                   )
                 )"data"
    FROM ( SELECT r.name, r.display_name 
           FROM   xxas_roles_v r
          )P         
        ]'
    );
	

END;
/
COMMIT;
EXIT;


