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

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

AS

BEGIN
	SELECT A.SP_ID
	    ,A.SP_MTR_HIST_ID
		,A.MTR_CONFIG_ID
		,B.EFF_DTTM
		,E.READ_DTTM INSTALL_DTTM
		,C.MTR_ID
		,C.BADGE_NBR
		,C.MTR_TYPE_CD
		,C.MTR_STATUS_FLG
		,C.MFG_CD
		,C.MODEL_CD
		,C.SERIAL_NBR
		,C.RECEIVE_DT
		,C.DESCR254 MTR_DESCR
		,H.REG_ID
		,H.READ_SEQ
		,H.UOM_CD
		,H.TOU_CD
		,H.REG_CONST
		,H.CONSUM_SUB_FLG
		,H.HOW_TO_USE_FLG
		,H.NBR_OF_DGTS_LFT
		,H.NBR_OF_DGTS_RGT
		,H.FULL_SCALE
		,H.READ_OUT_TYPE_CD
		,H.PROTOCOL_CD
		,H.TOLERANCE
		,G.READ_DTTM
		,F.REG_READ_ID
		,F.MR_ID
		,F.READ_TYPE_FLG
		,F.REG_READING
		,L.DESCR MTR_TYPE_DESCR
		,M.DESCR MFG_DESCR
		,N.DESCR MODEL_DESCR
	FROM CI_SP_MTR_HIST A
		,CI_MTR_CONFIG B
		,CI_MTR C
		,CI_SP_MTR_EVT D
		,CI_MR E
		,CI_REG_READ F
		,CI_MR G
		,CI_REG H
		,CI_MTR_TYPE I
		,CI_MFG J
		,CI_MODEL K
		,CI_MTR_TYPE_L L
		,CI_MFG_L M
		,CI_MODEL_L N
	WHERE A.SP_ID = @P_SP_ID
	AND A.REMOVAL_DTTM IS NULL
	AND B.MTR_CONFIG_ID = A.MTR_CONFIG_ID
	AND C.MTR_ID = B.MTR_ID
	AND D.SP_MTR_HIST_ID = A.SP_MTR_HIST_ID
	AND D.SP_MTR_EVT_FLG='I'
	AND E.MR_ID = D.MR_ID
	AND G.MTR_CONFIG_ID = B.MTR_CONFIG_ID
	AND G.READ_DTTM = ( SELECT MAX(READ_DTTM)
	                     FROM CI_REG_READ F2
	                     , CI_MR G2
	                     WHERE F2.REG_ID = F.REG_ID
	                     AND G2.MTR_CONFIG_ID = G.MTR_CONFIG_ID
	                     AND G2.MR_ID = F2.MR_ID
	                     AND F2.READ_TYPE_FLG IN ('50', '60','70') )
	AND F.MR_ID = G.MR_ID
	AND H.MTR_ID = B.MTR_ID
	AND CONVERT(varchar,H.EFF_DTTM,102) = CONVERT(varchar,B.EFF_DTTM,102)
	AND H.REG_ID = F.REG_ID
	AND I.MTR_TYPE_CD = C.MTR_TYPE_CD
	AND J.MFG_CD = C.MFG_CD
	AND K.MFG_CD = C.MFG_CD
	AND K.MODEL_CD = C.MODEL_CD
	AND L.MTR_TYPE_CD = I.MTR_TYPE_CD
	AND L.LANGUAGE_CD = @P_LANGUAGE_CD
	AND M.MFG_CD = J.MFG_CD
	AND M.LANGUAGE_CD = @P_LANGUAGE_CD
	AND N.MFG_CD = K.MFG_CD
	AND N.MODEL_CD = K.MODEL_CD
	AND N.LANGUAGE_CD = @P_LANGUAGE_CD
	ORDER BY 16;



RETURN
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


