unable to get the function value from procedure call?

Question

I've made a package in which one procedure is defined to call the function and one function is written to return varchar2 value but i'm unable to do that not sure about the reason on why i'm not able to do. more over the function return value i.e LP_WHERE_REP should be used in xml publisher report query to have the condition added to where clause based on the scenario of parameters selected p_module & p_processing_status are parameters.

CREATE OR REPLACE PACKAGE XXMTZ_WO_STG_REP_TRI_TEST AS  -- package spec
p_datasource          VARCHAR2 (40);
p_module              VARCHAR2 (10);
p_processing_status   VARCHAR2 (5);
LP_REP_WHERE VARCHAR2(4000):= NULL;
data_source XXMTZ_AT_AR_WO_STG_TBL.data_source %type;
  PROCEDURE PROC_TO_CALL_FUNCTION;
  FUNCTION BEFORE_REPORT RETURN VARCHAR2;
END XXMTZ_WO_STG_REP_TRI_TEST;
/


CREATE OR REPLACE PACKAGE BODY XXMTZ_WO_STG_REP_TRI_TEST AS  -- package body
  PROCEDURE PROC_TO_CALL_FUNCTION
  IS
    bValue  VARCHAR2(4000);
  BEGIN
    dbms_output.put_line('Entered the Procedure');
    bValue := XXMTZ_WO_STG_REP_TRI_TEST.BEFORE_REPORT;
    dbms_output.put_line('procedure end');
  END PROC_TO_CALL_FUNCTION;

  FUNCTION BEFORE_REPORT
      RETURN VARCHAR2
  IS
  BEGIN

    -- function body goes here
    IF     data_source = p_datasource --- p_data_source is parameter value & datasource is table column
         AND p_module = 'AP'
         AND p_processing_status = 'Processed'
      THEN
          LP_REP_WHERE := 'd_process_flag = ''P'' AND d_ap_flag = ''P''';
      ELSIF     data_source = p_datasource
            AND p_module = 'AP'
            AND p_processing_status = 'Un Processed'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''P'' AND d_ap_flag = ''Y''';
      ELSIF     data_source = p_datasource
            AND p_module = 'AP'
            AND p_processing_status = 'ERROR'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''P'' AND d_ap_flag = ''E''';
      ELSIF     data_source = p_datasource
            AND p_module = 'AP'
            AND p_processing_status = 'DELETED'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''R''';
      ELSIF     data_source = p_datasource
            AND p_module = 'AR'
            AND p_processing_status = 'Processed'
      THEN

         LP_REP_WHERE := 'd_process_flag = ''P'' AND d_ar_flag = ''P''';
      ELSIF     data_source = p_datasource
            AND p_module = 'AR'
            AND p_processing_status = 'Un Processed'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''P'' AND d_ar_flag = ''Y''';
      ELSIF     data_source = p_datasource
            AND p_module = 'AR'
            AND p_processing_status = 'ERROR'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''P'' AND d_ar_flag = ''E''';
      ELSIF     data_source = p_datasource
            AND p_module = 'AR'
            AND p_processing_status = 'DELETED'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''R''';
      ELSIF     data_source = p_datasource
            AND p_module = 'MTE'
            AND p_processing_status = 'Processed'
      THEN

         LP_REP_WHERE := 'd_process_flag = ''P'' AND d_mte_flag = ''P''';
      ELSIF     data_source = p_datasource
            AND p_module = 'MTE'
            AND p_processing_status = 'Un Processed'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''P'' AND d_mte_flag = ''Y''';
      ELSIF     data_source = p_datasource
            AND p_module = 'MTE'
            AND p_processing_status = 'ERROR'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''P'' AND d_mte_flag = ''E''';
      ELSIF     data_source = p_datasource
            AND p_module = 'MTE'
            AND p_processing_status = 'DELETED'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''R''';
      ELSIF     data_source = p_datasource
            AND p_module = 'Pre Interface'
            AND p_processing_status = 'Processed'
      THEN

         LP_REP_WHERE := 'd_process_flag = ''P''';
      ELSIF     data_source = p_datasource
            AND p_module = 'Pre Interface'
            AND p_processing_status = 'Un Processed'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''N''';
      ELSIF     data_source = p_datasource
            AND p_module = 'Pre Interface'
            AND p_processing_status = 'ERROR'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''E''';
      ELSIF     data_source = p_datasource
            AND p_module = 'Pre Interface'
            AND p_processing_status = 'DELETED'
      THEN
         LP_REP_WHERE := 'd_process_flag = ''R''';
      END IF;

           fnd_file.put_line (fnd_file.LOG, 'EXIT of IF Block');

    RETURN LP_REP_WHERE;

  END BEFORE_REPORT;
END XXMTZ_WO_STG_REP_TRI_TEST;
/

Answer 1

Function uses variables, e.g. data_source, p_datasource, p_module, ... but they aren't initialized so their value is null. That's why the function returns nothing (i.e. NULL).

Perhaps your procedure should accept those parameters and pass them to the function, e.g.

procedure (p_datasource in varchar2, p_module in varchar2) is
begin
  l_where := function (p_datasource, p_module);
end;

function (p_datasource in varchar2, p_module in varchar2)
  return varchar2;