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

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Form Block Formulas'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindFormBlockFormulas'
	,p_path               => '/form-formulas'
    ,p_description        => 'Find existing fast formulas 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
                     ("formulas"
                     ,XMLForest
                       (rslt.formula_name "id"
                       ,rslt.formula_name "value"
                       ,rslt.formula_name "label")
                     )
                   )
                 ) "data"
        FROM   (SELECT DISTINCT ff.formula_name
                FROM   ff_formulas_f    ff
                      ,ff_formula_types fft
                WHERE  trunc(sysdate) BETWEEN ff.effective_start_date 
                                      AND     ff.effective_end_date
                AND    ff.formula_type_id = fft.formula_type_id
                AND    fft.formula_type_name LIKE 'Applaud%'
                AND    {requestParam.id} IS NULL
                AND    UPPER(ff.formula_name) like UPPER('%' || {requestParam.q} || '%')
                UNION 
                SELECT DISTINCT ff.formula_name
                FROM   ff_formulas_f    ff
                      ,ff_formula_types fft
                WHERE  trunc(sysdate) BETWEEN ff.effective_start_date 
                                      AND     ff.effective_end_date
                AND    ff.formula_type_id = fft.formula_type_id
                AND    fft.formula_type_name LIKE 'Applaud%'
                AND    {requestParam.id} IS NOT NULL
                AND    UPPER(ff.formula_name) = UPPER({requestParam.id})) rslt
        ORDER BY rslt.formula_name
      ]'
    );
	
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindpayslipBlockFormulas'
	,p_path               => '/payslip-formulas'
    ,p_description        => 'Find existing payslip fast formulas 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
                     ("formulas"
                     ,XMLForest
                       (rslt.formula_name "id"
                       ,rslt.formula_name "value"
                       ,rslt.formula_name "label")
                     )
                   )
                 ) "data"
        FROM   (SELECT DISTINCT f.formula_name
                FROM   ff_formulas_f f  
						, ff_fdi_usages_f u1
						, ff_fdi_usages_f u2
						, ff_fdi_usages_f u3
						, FF_FORMULA_TYPES ft
                WHERE  trunc(sysdate) BETWEEN f.effective_start_date 
                                      AND     f.effective_end_date
                AND  u1.formula_id = f.formula_id 
				and u2.formula_id = f.formula_id
				and u3.formula_id = f.formula_id
				and u1.item_name ='PAYSLIP_ID'
				and  u2.item_name='SUMMARY_TEXT'
				and u3.item_name='TEMPLATE'
				and FT.FORMULA_TYPE_NAME = 'Applaud Assignment'
				and FT.FORMULA_TYPE_ID= f.FORMULA_TYPE_ID
               ) rslt
        ORDER BY rslt.formula_name
      ]'
    );
	
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindWorlistBlockFormulas'
	,p_path               => '/worklist-formulas'
    ,p_description        => 'Find existing wprklist fast formulas 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
                     ("formulas"
                     ,XMLForest
                       (rslt.formula_name "id"
                       ,rslt.formula_name "value"
                       ,rslt.formula_name "label")
                     )
                   )
                 ) "data"
        FROM   (SELECT DISTINCT f.formula_name
                FROM   ff_formulas_f f 
						, ff_fdi_usages_f u
						, FF_FORMULA_TYPES ft
                WHERE  trunc(sysdate) BETWEEN f.effective_start_date 
                                      AND     f.effective_end_date
                AND u.formula_id = f.formula_id 
				and u.item_name ='COLOR'
				and FT.FORMULA_TYPE_NAME = 'Applaud Person'
				and f.formula_name = 'XXAS_EMP_WL_RESP'
				and FT.FORMULA_TYPE_ID= f.FORMULA_TYPE_ID
               ) rslt
        ORDER BY rslt.formula_name
      ]'
    );

	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindContactBlockFormulas'
	,p_path               => '/contact-formulas'
    ,p_description        => 'Find existing fast formulas 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
                     ("formulas"
                     ,XMLForest
                       (rslt.formula_name "id"
                       ,rslt.formula_name "value"
                       ,rslt.formula_name "label")
                     )
                   )
                 ) "data"
        FROM   (SELECT DISTINCT ff.formula_name 
      FROM ff_formulas_f ff 
	      ,ff_formula_types fft 
          ,ff_fdi_usages_f f1
          ,ff_fdi_usages_f f2
          ,ff_fdi_usages_f f3
          ,ff_fdi_usages_f f4
	  WHERE trunc(sysdate) BETWEEN ff.effective_start_date AND ff.effective_end_date
      AND f1.formula_id = ff.formula_id 
	  AND f2.formula_id = ff.formula_id 
	  AND f3.formula_id = ff.formula_id 
	  AND f1.item_name ='DELETE_ALLOWED' 
	  AND f2.item_name='PERSON_ID' 
	  AND f3.item_name='PENDING_PERSON_ID'
      AND f4.item_name = 'MAIN_TEXT'
	  AND ff.formula_type_id = fft.formula_type_id 
	  AND fft.formula_type_name LIKE 'Applaud%' 
	  AND {requestParam.id} IS NULL 
	  AND UPPER(ff.formula_name) like UPPER('%' || {requestParam.q} || '%') 
	  UNION 
	  SELECT DISTINCT ff.formula_name 
	  FROM ff_formulas_f ff 
	      ,ff_formula_types fft 
          ,ff_fdi_usages_f f1
          ,ff_fdi_usages_f f2
          ,ff_fdi_usages_f f3
          ,ff_fdi_usages_f f4
	  WHERE trunc(sysdate) BETWEEN ff.effective_start_date AND ff.effective_end_date 
      AND f1.formula_id = ff.formula_id 
	  AND f2.formula_id = ff.formula_id 
	  AND f3.formula_id = ff.formula_id 
	  AND f1.item_name ='DELETE_ALLOWED' 
	  AND f2.item_name='PERSON_ID' 
	  AND f3.item_name='PENDING_PERSON_ID'
      AND f4.item_name = 'MAIN_TEXT'
	  AND ff.formula_type_id = fft.formula_type_id 
	  AND fft.formula_type_name LIKE 'Applaud%' 
	  AND {requestParam.id} IS NOT NULL 
	  AND UPPER(ff.formula_name) = UPPER({requestParam.id})
				) rslt
        ORDER BY rslt.formula_name
      ]'
    );
	
END;
/
COMMIT;
EXIT;
