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

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

AS

BEGIN
	SELECT A.SP_ID
		,A.SP_ITEM_HIST_ID
		,C.EVENT_DTTM INSTALL_DTTM
		,B.ITEM_ID
		,B.BADGE_NBR
		,B.ITEM_TYPE_CD
		,B.ITEM_STATUS_FLG
		,B.MFG_CD
		,B.MODEL_CD
		,B.SERIAL_NBR
		,B.RECEIVE_DT
		,B.DESCR254 ITEM_DESCR
		,D.DESCR ITEM_TYPE_DESCR
		,E.DESCR MFG_DESCR
		,F.DESCR MODEL_DESCR
	FROM CI_SP_ITEM_HIST A
		,CI_ITEM B
		,CI_SP_ITEM_EVT C
		,CI_ITEM_TYPE_L D
		,CI_MFG_L E
		,CI_MODEL_L F
	WHERE A.SP_ID = @P_SP_ID
	AND A.REMOVAL_DTTM IS NULL
	AND B.ITEM_ID = A.ITEM_ID
	AND C.SP_ITEM_HIST_ID = A.SP_ITEM_HIST_ID
	AND C.SP_ITEM_EVT_FLG='I'
	AND D.ITEM_TYPE_CD = B.ITEM_TYPE_CD
	AND D.LANGUAGE_CD = @P_LANGUAGE_CD
	AND E.MFG_CD = B.MFG_CD
	AND E.LANGUAGE_CD = @P_LANGUAGE_CD
	AND F.MFG_CD = B.MFG_CD
	AND F.MODEL_CD = B.MODEL_CD
	AND F.LANGUAGE_CD = @P_LANGUAGE_CD;




RETURN
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
