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;
Two issues:
Be sure that your before_report
function is defined in the spec portion of the package.
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.
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;
/