Has anyone successfully utilized a stored funciton call from Oracle in a universe that utilizes the @Prompt at the table-owner level?
We are using an @Prompt function at the table-owner level to point the universe to multiple schemas. The Oracle stored function is used to determine the start of the fiscal year. It is used as a condition in reporting.
The problem we have encounter deals with the fact that BO returns an error message of ‘ORA-00904: invalid column name: -904’ when reporting from the universe that utilizes the @Prompt functions at the table owner level. Here’s the SQL from BO that generates the error message.
SELECT
@Prompt('Business Unit','N','support\LOV',,).ENC_REG.NAME_L || ', '|| @Prompt('Business Unit','N','support\LOV',,).ENC_REG.NAME_F || ' ' || @Prompt('Business Unit','N','support\LOV',,).ENC_REG.NAME_MI,
@Prompt('Business Unit','N','support\LOV',,).ENC_REG.MR_NBR,
(case when ENC_REG_ADMIT_DATE.ADM_DT= '11/11/1111' then '' else ENC_REG_ADMIT_DATE.ADM_DT end )
FROM
IN300.PERIOD ENC_REG_ADMIT_DATE,
@Prompt('Business Unit','N','support\LOV',,).ENC_REG
WHERE
(@Prompt('Business Unit','N','support\LOV',,).ENC_REG.ADM_DT_KEY=ENC_REG_ADMIT_DATE.DT_KEY ) AND (( case when ENC_REG_ADMIT_DATE.ADM_DT= '11/11/1111' then '' else ENC_REG_ADMIT_DATE.ADM_DT end ) > sf_get_fiscal_year_start(sysdate)) The Oracle function, which resides in each database schema, is as follows:
create or replace function sf_get_fiscal_year_start
(curr_date in date
) return date as
fiscal_year_start varchar2(10);
begin
if to_char(curr_date,'mmdd') >= '0701' and
to_char(curr_date,'mmdd') <= '1231' then
--Return 7/1/current_year
fiscal_year_start := '07/01/'||to_char(curr_date,'yyyy');
return to_date(fiscal_year_start,'mm/dd/yyyy');
else
--Return 7/1/prior_year
fiscal_year_start :=
'07/01/'||to_char(add_months(curr_date,-12),'yyyy');
return to_date(fiscal_year_start,'mm/dd/yyyy');
end if;
end sf_get_fiscal_year_start;
/I believe the issue surrounds the fact that BO doesn’t know where to find the ‘sf_get_fiscal_year_start’ function even though the user is prompted for the schema upon running the report.
Thanks in advance for your help. 
berkeyg 
(BOB member since 2002-10-17)
(BOB member since 2002-09-05)
(BOB member since 2002-10-17)
. I read that higher up but I thought that what Rob (digpen) said meant that I wouldn’t have to make that change since using type ‘N’ would allow for both char and numeric and since validation isn’t an issue, I’d be fine. Obviously not 
. I must have misunderstood.
I misread