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

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

AS

BEGIN

    

	SELECT A.SP_ID
		,A.EFFDT
		,A.ITEM_TYPE_CD
		,A.ITEM_CNT
		,B.DESCR ITEM_TYPE_DESCR
	FROM CI_MULT_ITEM A
		,CI_ITEM_TYPE_L B
	WHERE A.SP_ID = @P_SP_ID
	AND A.EFFDT = (SELECT MAX(C.EFFDT)
    			   FROM CI_SP_MULT_ITEM C
                   WHERE C.SP_ID = @P_SP_ID
	               AND C.EFFDT <= getDate())
	AND B.ITEM_TYPE_CD = A.ITEM_TYPE_CD
	AND B.LANGUAGE_CD = @P_LANGUAGE_CD;

RETURN
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

