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

BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Calculate the field values based on the triggered Fields'
    ,p_path               => 'form-field-triggers'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetFunctionData'
    ,p_description        => 'Calls the corresponsing the function to return the Data XML'
    ,p_methods            => 'POST'
    ,p_path               => '{triggerName}'
    ,p_plsql              =>
      q'[
        DECLARE

          CURSOR csr_form_handler(cp_trigger_name IN varchar2)
          IS
          SELECT xfft.function_handler
          FROM   xxas_form_field_triggers xfft
          WHERE  xfft.trigger_name = cp_trigger_name;

          l_resp             xxas_http_resp_obj;
          l_xml              XMLType;
          l_request_xml      XMLType;
          l_trigger_name     varchar2(400);
          l_function_handler varchar2(400);
          l_varchar2tbl      xxas_com_varchar2_table;

        BEGIN

          l_request_xml := {requestBody};

          l_trigger_name := {pathParam.triggerName};

          OPEN  csr_form_handler(l_trigger_name);
          FETCH csr_form_handler INTO l_function_handler;
          CLOSE csr_form_handler;

          --
          -- When Function handler is not defined - don''t do anything
          --
          IF l_function_handler is null THEN

            l_resp := xxas_http_response.ok().build();

          --
          -- When Function handler is defined - check whether it is present on db or not
          --
          ELSE 

            IF INSTR(l_function_handler,'.') > 0 THEN

              l_varchar2tbl := xxas_util_pkg.string_to_varchar2tbl(l_function_handler,'.');

              IF NOT xxas_database_metadata_pkg.procedure_exists
                       (p_package_name   => l_varchar2tbl(1)
                       ,p_procedure_name => l_varchar2tbl(2)) THEN

                l_resp := xxas_http_response.ok().build();

              ELSE

                IF NOT xxas_database_metadata_pkg.in_parameter_exists
                         (p_package_name   => l_varchar2tbl(1)
                         ,p_procedure_name => l_varchar2tbl(2)
                         ,p_parameter_name => 'p_request_xml') THEN

                  l_resp := xxas_http_response.ok().build();

                END IF;

              END IF;

            ELSE

              l_resp := xxas_http_response.ok().build();

            END IF;

          END IF;

          --
          -- Call form handler with the Request XML
          --
          IF l_resp is null THEN

            EXECUTE IMMEDIATE
                  ' DECLARE '
               || ' BEGIN '
               || '  :xml := ' || l_function_handler
               || ' (p_request_xml => :requestxml); '
               || ' END; '
            USING OUT l_xml
                 ,l_request_xml;

            IF l_xml is not null THEN

              l_resp := xxas_http_response
                          .ok(l_xml)
                          .build();

            ELSE

              l_resp := xxas_http_response.ok().build();

            END IF;

          END IF;

          {response} := l_resp;

        END
      ]'
    );

END;
/
COMMIT;
EXIT;
