-- ******************************************************************
-- *
-- * 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 = 'CIFOPRTM' AND type = 'P')
   DROP PROCEDURE CIFOPRTM
GO

CREATE  PROCEDURE CIFOPRTM
@P_LANGUAGE_CD CHAR(3),
@P_SP_ID CHAR(10)

AS 

BEGIN

    
		SELECT /*+ RULE */
		 A.SP_ID
		,A.CHAR_TYPE_CD
		,A.EFFDT
		,A.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
		,C.DESCR CHAR_TYPE_DESCR
		,B.CHAR_TYPE_FLG
		,B.FK_REF_CD
		,B.CHAR_TYPE_CD
	FROM CI_SP_CHAR A
	   , CI_CHAR_TYPE B
	   , CI_CHAR_TYPE_L C
	WHERE A.SP_ID = @P_SP_ID
	AND B.CHAR_TYPE_CD = A.CHAR_TYPE_CD
	AND C.CHAR_TYPE_CD = A.CHAR_TYPE_CD
	AND C.LANGUAGE_CD = @P_LANGUAGE_CD
	ORDER BY 1, 2, 3 DESC;

RETURN
END 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

