how to call a function in a procedure?

Question

I have a concern where in I need to call the package body function using a procedure which must return value which I can use in xml publisher report where clause which will call the function value in report query and i have tried writing the procedure in the same body underneath the function but the difficulty is i'm unable to call the function and return the value.

CREATE OR REPLACE PACKAGE BODY XXMTZ_WO_STG_REP_TRI_TEST
AS
   FUNCTION before_report
      RETURN BOOLEAN
   IS
   --
   BEGIN
      --
      fnd_file.put_line (fnd_file.LOG, 'Entering the IF Block');

      --
      IF     data_source = p_datasource
         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 TRUE;
   --
   END before_report;

END XXMTZ_WO_STG_REP_TRI_TEST;

Answer 1

Two issues:

1. Be sure that your before_report function is defined in the spec portion of the package.

2. The BOOLEAN type is not available in SQL. If you want to call this function from an SQL statement you'll need to return something like 'Y' or 'N' as a VARCHAR2 value.

Edit

If you want to define a procedure to call your function you'd do something like

CREATE OR REPLACE PACKAGE XXMTZ_WO_STG_REP_TRI_TEST AS  -- package spec
  PROCEDURE PROC_TO_CALL_FUNCTION;
  FUNCTION BEFORE_REPORT RETURN BOOLEAN;
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  BOOLEAN;
  BEGIN
    bValue := XXMTZ_WO_STG_REP_TRI_TEST.BEFORE_REPORT;
  END PROC_TO_CALL_FUNCTION;

  FUNCTION BEFORE_REPORT
      RETURN BOOLEAN
  IS
  BEGIN
    -- function body goes here

    RETURN TRUE;
  END BEFORE_REPORT;
END XXMTZ_WO_STG_REP_TRI_TEST;
/