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

CREATE  PROCEDURE  CIFOPRTA
@P_PREM_ID CHAR(10)

AS


BEGIN

	SELECT DISTINCT A.ACCT_ID
		,A.MAIN_CUST_SW
		,A.PER_ID
		,A.ACCT_REL_TYPE_CD
		,B.ENTITY_NAME
		,C.LS_SL_FLG
		,C.LS_SL_DESCR
		,E.PREM_ID
	FROM CI_ACCT_PER A
		,CI_PER_NAME B
		,CI_PER C
		,CI_SA D
		,CI_SP E
		,CI_SA_SP F
	WHERE B.PER_ID = A.PER_ID
	AND B.NAME_TYPE_FLG = 'PRIM'
	AND C.PER_ID = A.PER_ID
	AND D.ACCT_ID = A.ACCT_ID
	AND D.SA_STATUS_FLG IN ('10','20','30','50')
	AND F.SA_ID = D.SA_ID
	AND F.SP_ID = E.SP_ID
	AND E.PREM_ID = @P_PREM_ID
	ORDER BY E.PREM_ID, A.ACCT_ID, A.MAIN_CUST_SW DESC, A.PER_ID;


RETURN				
END 


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




