1. Sql Statement
------------------

	Select 	Nvl(Sum(DeCode(TransCode,'59',NetPremium,(-1)*NetPremium)),0) NetPremium
		From	Central_Repository
		Where	CompanyCode 	=	PCompanyCode
		And	InsuranceType	=	PInsuranceType
		And	ProposerCode	=	PProposerCode
		And	(RefNo		=	PReferenceNo
		Or	(PReferenceNo	Is	Null))
		And	TransCode	In	('19','59')
		And	TransDate	<=	PToDate;
		
		
2. Indexes
-------------		



CREATE INDEX ADMIN.I_CENTRAL_REPOSITORY_PROPOSER ON ADMIN.CENTRAL_REPOSITORY
(COMPANYCODE, INSURANCETYPE, PROPOSERCODE, REFNO, TRANSCODE, 
TRANSDATE)
LOGGING
TABLESPACE REPOSITORYINDEX_TS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          104M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

-----------------------------------------

CREATE INDEX ADMIN.I_CENTRAL_REPOSITORY_TRANS ON ADMIN.CENTRAL_REPOSITORY
(COMPANYCODE, PROPOSERCODE, TRANSCODE, TRANSDATE)
LOGGING
TABLESPACE REPOSITORYINDEX_TS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          72M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

-----------------------------------------------------------------------------