/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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
                       := 'Countries';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Find Wf participants'
    ,p_path               => '/dor'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindContries'
    ,p_description        => 'Find countries'
    ,p_methods            => 'GET'
	,p_path               => '/countries'
    ,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.territory_code as "id"
                       ,p.territory_short_name as "label"
                       ,p.territory_short_name as "value"
                       )
                     )
                   )
                 ) "data"
        FROM  FND_TERRITORIES_VL p
		WHERE ({requestParam.id} IS NOT NULL 
		       AND {requestParam.id} = p.territory_code
              )			   
		      OR regexp_like(p.territory_short_name,{requestParam.q},'i')
        ]'
     );
        
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindDocumentTypes'
    ,p_description        => 'Find document types'
    ,p_methods            => 'GET'
	,p_path               => '/documenttypes'
    ,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
                       (doc_type.document_type_id as "id"
                       ,doc_type.document_type as "label"
                       ,doc_type.document_type as "value"
                       )
                     )
                   )
                 ) "data"
            FROM xxas_document_types_v doc_type
            WHERE (doc_type.legislation_code  IS NULL
			        OR {requestParam.legislationCode} IS NULL
			        OR doc_type.legislation_code    = {requestParam.legislationCode})
		    AND   ({requestParam.q} IS NULL 
			        OR regexp_like(doc_type.document_type,{requestParam.q},'i'))
			AND   ({requestParam.id} IS NULL 
			        OR doc_type.document_type_id  = {requestParam.id}
                  )			
			AND doc_type.document_type IN 
              (SELECT distinct pitsv.information_type
                      FROM   fnd_user_resp_groups_direct   furg
                            ,fnd_user                      fu
                            ,PER_INFO_TYPE_SECURITY_V      pitsv
                      WHERE  furg.user_id = fu.user_id
                      AND    fu.employee_id = nvl({requestParam.personId},fnd_global.employee_id)
                      AND    (furg.end_date IS NULL OR furg.end_date &gt;= TRUNC(SYSDATE))
                      AND    furg.responsibility_id = pitsv.responsibility_id
                      AND    pitsv.INFO_TYPE_TABLE_NAME = 'HR_DOCUMENT_TYPES_V'
                      AND    trunc(sysdate) BETWEEN furg.start_date and nvl(furg.end_date,trunc(sysdate))
                      AND    pitsv.application_id = 800
			  )
			
        ]'
     );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindCategories'
    ,p_description        => 'Find categories'
    ,p_methods            => 'GET'
	,p_path               => '/categories'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
            SELECT XMLElement
                 ("data"
                 ,XMLAgg
                   (XMLElement
                     ("role"
                     ,XMLForest
                       (doc_type.category_code as "id"
                       ,doc_type.category_name as "label"
                       ,doc_type.category_name as "value"
                       )
                     )
                   )
                 )
            FROM xxas_document_types_v doc_type
            WHERE doc_type.document_type_id    = {requestParam.documentTypeId}
			
        ]'
     );
  
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindSubCategories'
    ,p_description        => 'Find Sub categories'
    ,p_methods            => 'GET'
	,p_path               => '/subcategories'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
            SELECT XMLElement
                 ("data"
                 ,XMLAgg
                   (XMLElement
                     ("role"
                     ,XMLForest
                       (doc_type.sub_category_code as "id"
                       ,doc_type.sub_category_name as "label"
                       ,doc_type.sub_category_name as "value"
                       )
                     )
                   )
                 )
            FROM xxas_document_types_v doc_type
            WHERE doc_type.document_type_id    = {requestParam.documentTypeId}
			
        ]'
     );
	 
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteDocExtraInfo'
    ,p_description        => 'Delete Document Extra Info'
    ,p_methods            => 'DELETE'
	,p_path               => '/{documentExtraInfoId}'
    ,p_plsql              =>
      q'[
            DECLARE
			  
			  l_response_xml  XMLType;
			  l_resp          xxas_http_resp_obj;
			  l_person_id     NUMBER;
			  l_ovn           NUMBER;
			  
			BEGIN
			  
			  SELECT hr.person_id
			        ,hr.object_version_number
			  INTO   l_person_id
			        ,l_ovn
			  FROM   hr_document_extra_info hr
			  WHERE  hr.document_extra_info_id = {pathParam.documentExtraInfoId};
			  
			  -- Check for Permission.
			  IF xxas_com_sec_pkg.test_instance
			       ('XXAS_DEL_DOR_PERM'
				   ,{pathParam.documentExtraInfoId},l_person_id) = 'Y' THEN
			  
			    xxas_dor_pkg.delete_doc_extra_info({pathParam.documentExtraInfoId},l_ovn,{requestParams},l_response_xml);
			    
				l_resp := xxas_http_response
                      .ok(l_response_xml)
                      .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        => 'GETDORREVIEWID'
    ,p_description        => 'Find dor ids required in the dor review xml'
    ,p_methods            => 'GET'
    ,p_path               => 'transaction/{transactionId}/transactionstep/{transactionStepId}/{personId}'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT  XMLELEMENT("ROW"
                         ,XMLFOREST({pathParam.transactionId}     TRANSACTION_ID
                                   ,{pathParam.transactionStepId} TRANSACTION_STEP_ID
								   ,{pathParam.personId}          PERSON_ID
								   ,{requestParam.docExtraInfoId} DOCUMENT_EXTRA_INFO_ID
                                   ,SYSDATE                       EFFECTIVE_DATE
								   ,{pathParam.transactionId}     APPROVERS
								   )) "data"
        FROM    DUAL
        ]'
    );
END;
/
COMMIT;
EXIT;
