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

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

AS

   		DECLARE @V_LANGUAGE_CD CHAR(3)
   		DECLARE @V_FIELD_NAME CHAR(20)
                DECLARE   @P_REP_CURSOR CURSOR   		

   		SELECT @V_LANGUAGE_CD = @P_LANGUAGE_CD
		SELECT @V_FIELD_NAME = 'SP_SUBTYPE_FLG'

BEGIN

		SELECT A.SP_ID, A.SP_DESCR, A.SP_TYPE_CD, A.SP_TYPE_DESCR,A.SP_SUBTYPE_FLG
		  	  ,A.SP_SUBTYPE_DESCR, A.SP_STATUS_FLG, A.INSTALL_DT,A.SP_SRC_STATUS_FLG
			  ,A.DISCON_LOC_CD, A.DISCON_LOC_DESCR, A.MR_CYC_CD,A.MR_CYC_DESCR
			  ,A.MTR_LOC_CD, A.MTR_LOC_DESCR, A.MTR_LOC_DETAILS,A.FAC_LVL_1_CD
			  ,B.DESCR FAC_LVL_1_DESCR,A.FAC_LVL_2_CD,C.DESCR FAC_LVL_2_DESCR
			  ,A.FAC_LVL_3_CD,D.DESCR FAC_LVL_3_DESCR,A.OVRD_PREM_FLD_INFO_FLG
			  ,A.MR_WARN_CD,A.MR_INSTR_CD,A.MR_INSTR_DETAILS,A.MRW_DESCR,A.MRI_DESCR
		FROM (SELECT SP.SP_ID, SP.DESCR254 SP_DESCR, SP.SP_TYPE_CD, SPTYL.DESCR SP_TYPE_DESCR 
		            ,SPTY.SP_SUBTYPE_FLG, LU.DESCR SP_SUBTYPE_DESCR, SP.SP_STATUS_FLG, SP.INSTALL_DT
					,SP.SP_SRC_STATUS_FLG,SP.DISCON_LOC_CD, DL.DESCR DISCON_LOC_DESCR, SP.MR_CYC_CD
					,MRCY.DESCR MR_CYC_DESCR, SP.MTR_LOC_CD, MTRL.DESCR MTR_LOC_DESCR, SP.MTR_LOC_DETAILS
					,SPTY.SVC_TYPE_CD,SP.FAC_LVL_1_CD, SP.FAC_LVL_2_CD, SP.FAC_LVL_3_CD
					,SP.OVRD_PREM_FLD_INFO_FLG, SP.MR_WARN_CD, SP.MR_INSTR_CD, SP.MR_INSTR_DETAILS
					,MRWL.DESCR MRW_DESCR, MRIL.DESCR MRI_DESCR
		      FROM   	         CI_SP_TYPE SPTY
					,CI_SP_TYPE_L SPTYL
					,CI_LOOKUP LU,CI_SP SP
					,CI_MR_CYC_L MRCY
					,CI_MTR_LOC_L MTRL 
	 				,CI_DISCON_LOC_L DL
					,CI_MR_WARN_L MRWL
					,CI_MR_INSTR_L MRIL
		      WHERE SP.SP_ID = @P_SP_ID
			  AND SPTY.SP_TYPE_CD = SP.SP_TYPE_CD
			  AND SPTYL.SP_TYPE_CD = SPTY.SP_TYPE_CD
			  AND SPTYL.LANGUAGE_CD = @V_LANGUAGE_CD
			  AND LU.FIELD_NAME = @V_FIELD_NAME
			  AND LU.FIELD_VALUE = SPTY.SP_SUBTYPE_FLG
			  AND LU.LANGUAGE_CD = @V_LANGUAGE_CD
			  AND MRCY.MR_CYC_CD =* SP.MR_CYC_CD
			  AND MRCY.LANGUAGE_CD = @V_LANGUAGE_CD
			  AND MTRL.MTR_LOC_CD =* SP.MTR_LOC_CD
			  AND MTRL.LANGUAGE_CD = @V_LANGUAGE_CD
			  AND DL.DISCON_LOC_CD =* SP.DISCON_LOC_CD
			  AND DL.LANGUAGE_CD = @V_LANGUAGE_CD
			  AND MRWL.MR_WARN_CD =* SP.MR_WARN_CD
			  AND MRWL.LANGUAGE_CD = @V_LANGUAGE_CD
			  AND MRIL.MR_INSTR_CD =* SP.MR_INSTR_CD
			  AND MRIL.LANGUAGE_CD = @V_LANGUAGE_CD) A
			  ,CI_FAC_LVL_1_L B
			  ,CI_FAC_LVL_2_L C
			  ,CI_FAC_LVL_3_L D
		WHERE B.SVC_TYPE_CD =* A.SVC_TYPE_CD
		AND B.FAC_LVL_1_CD =* A.FAC_LVL_1_CD
		AND B.LANGUAGE_CD = @V_LANGUAGE_CD
		AND C.SVC_TYPE_CD =* A.SVC_TYPE_CD
		AND C.FAC_LVL_2_CD =* A.FAC_LVL_2_CD
		AND C.LANGUAGE_CD = @V_LANGUAGE_CD
		AND D.SVC_TYPE_CD =* A.SVC_TYPE_CD
		AND D.FAC_LVL_3_CD =* A.FAC_LVL_3_CD
		AND D.LANGUAGE_CD = @V_LANGUAGE_CD;


RETURN
END 

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



