ORA-00904 when creating a view

From: Odd Morten Sveås <odd.morten.sveas_at_accenture.com>
Date: 22 Oct 2002 22:47:26 -0700
Message-ID: <4306a83.0210222147.3f5b1a88_at_posting.google.com>


Hi

I want to create a view, that uses a function. If I isolate the select that creates the view and runs it, it all works fine. But when i put the select in create view statement i get ORA-00904: invalid column name, and indikating the function as the invalid column.

I bouth the select and the create view as the same user, and its the owner of the function. (Actualy the packaege thats contain the function)

I'm on 8.1.7

here is the statement, and its the fnd_profile.value() that make problems.

create or replace view xxmx.mx040v_ap_match (artskonto,dato,bilagsnummer,belop,serie,code_combination_id) as SELECT

   GL.GL_CODE_COMBINATIONS.SEGMENT1  ARTSKONTO,
   GL.GL_JE_LINES.EFFECTIVE_DATE DATO,
   GL.GL_JE_LINES.REFERENCE_4 BILAGSNUMMER,
   NVL( GL.GL_JE_LINES.ACCOUNTED_DR, 0) - NVL( GL.GL_JE_LINES.ACCOUNTED_CR, 0) BELOP,
   substr(GL.GL_JE_LINES.REFERENCE_4,length(ltrim(fnd_profile.value('BSKK_LOGINORG'),'0'))+1,2),   GL.GL_JE_LINES.CODE_COMBINATION_ID
FROM
        GL.GL_JE_LINES,
        GL.GL_CODE_COMBINATIONS,
        GL.GL_JE_HEADERS,
	ap.AP_AE_LINES_ALL aal ,
	AP.ap_checks_all aca
    WHERE
        GL.GL_JE_LINES.CODE_COMBINATION_ID =
GL.GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
        AND GL.GL_JE_LINES.JE_HEADER_ID =
GL.GL_JE_HEADERS.JE_HEADER_ID
	AND gl.gl_je_lines.GL_SL_LINK_ID = aal.GL_SL_LINK_ID
	AND aal.SOURCE_ID = aca.CHECK_ID
        AND GL.GL_CODE_COMBINATIONS.SEGMENT5 =
fnd_profile.value('BSKK_LOGINORG')
        AND GL.GL_JE_HEADERS.STATUS = 'P'
    	AND GL.GL_JE_LINES.GL_SL_LINK_TABLE = 'APECL'
Received on Wed Oct 23 2002 - 07:47:26 CEST

Original text of this message