-- ******************************************************************
-- *
-- * Copyright (c) 2000, 2007, Oracle. All rights reserved.
-- *
-- ******************************************************************
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CITOSCEN' AND type = 'P')
   DROP PROCEDURE RPTUSER.CITOSCEN
GO

CREATE PROCEDURE CITOSCEN
@P_TOS_ID CHAR(10), 
@P_LANGUAGE_CD CHAR(3)
AS

BEGIN
            SELECT A.TOS_ID, 
                   A.CHAR_TYPE_CD,
                   A.SEQ_NUM CE_SEQ_NUM, 
                   C.DESCR CHAR_TYPE_DESCR, 
                   A.CHAR_VAL, 
                   D.DESCR CHAR_VAL_DESCR,
                   B.CHAR_TYPE_FLG, 
                   A.SRCH_CHAR_VAL,
                   A.ADHOC_CHAR_VAL,
                   A.CHAR_VAL_FK1, 
                   A.CHAR_VAL_FK2, 
                   A.CHAR_VAL_FK3,
                   A.CHAR_VAL_FK4, 
                   A.CHAR_VAL_FK5
              FROM CI_TOS_CE A
			left outer join CI_CHAR_VAL_L D on D.CHAR_TYPE_CD = A.CHAR_TYPE_CD and D.CHAR_VAL = A.CHAR_VAL and D.LANGUAGE_CD = @P_LANGUAGE_CD,
                   CI_CHAR_TYPE B,
                   CI_CHAR_TYPE_L C
             WHERE A.TOS_ID = @P_TOS_ID
                   AND B.CHAR_TYPE_CD = A.CHAR_TYPE_CD
                   AND C.CHAR_TYPE_CD = B.CHAR_TYPE_CD
                   AND C.LANGUAGE_CD = @P_LANGUAGE_CD
            ORDER  BY A.TOS_ID, A.CHAR_TYPE_CD, A.SEQ_NUM;    
RETURN
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
