SELECT  'C',
		CLR_CLAIM_NO							"CLR_CLAIM_NO_MIC",
		CLR_LOCATION_CODE						"CLR_LOCATION_CODE_MIC",
		CR_POLICY_NO							"clm_policy_no_MIC",
		CR_POLICY_NO || '-' || CR_POLICY_RENEW_NO				"CR_POLICY_NO_MIC" , 
        CR_POLICY_RENEW_NO						"CR_POLICY_RENEW_NO_MIC",
		CR_LOSS_DATE							"CR_LOSS_DATE", 
		'Claim', 
		CLR_NO_OF_VEHICLES						NO_OF_VEHICLES_MIC,
		B.GTABLENAME							"Cover_Desc_MIC",
		CLR_COVERAGE_TYPE						"CLR_COVERAGE_TYPE_MIC", 
		CLR_COL_CODE							"CLR_COL_CODE_MIC",
		A.GTABLENAME							"Cause Of Loss_MIC",
		(SUM((NVL(CF_IND_RESERVE,0) + 
		NVL(CF_DED_REFUND_PAID,0) + 
		NVL(CF_IND_PAID,0)) - 
		(NVL(CF_IND_RECOVERED,0))))    "GROSS_DAMAGE_MIC",
		SUM(NVL(CF_DED_APPLIED,0)) -
		SUM(NVL(CF_DED_REFUND_PAID,0)) "DEDUCTIBLE_MIC", 
		SUM(NVL(CF_IND_PAID,0))        "PAID_LOSS_MIC",
		(((SUM(NVL(CF_SALV_RESERVE,0)) + 
		SUM(NVL(CF_SALV_RECOVERED_GROSS,0) - 
		NVL(CF_SALV_EXP_PAID_GROSS,0) - 
		NVL(CF_SALV_EXP_RETAINED_GROSS,0)))) +
		((SUM(NVL(CF_SUBRO_RESERVE,0)) + 
		SUM(NVL(CF_SUBRO_RECOVERED_GROSS,0) - 
		NVL(CF_SUBRO_EXP_PAID_GROSS,0) - 
		NVL(CF_SUBRO_EXP_RETAINED_GROSS,0)))))  "RECOVERY_MIC",
		DECODE(SIGN((
					 (SUM((NVL(CF_IND_RESERVE,0)+ 
					 NVL(CF_DED_REFUND_PAID,0) + 
					 NVL(CF_IND_PAID,0)) - 
					 (NVL(CF_IND_RECOVERED,0)))) - 
					 ((SUM(NVL(CF_SALV_RESERVE,0)) + 
					 SUM(NVL(CF_SALV_RECOVERED_GROSS,0) - 
					 NVL(CF_SALV_EXP_PAID_GROSS,0) - 
					 NVL(CF_SALV_EXP_RETAINED_GROSS,0))) + 
					 (SUM(NVL(CF_SUBRO_RESERVE,0)) + 
					 SUM(NVL(CF_SUBRO_RECOVERED_GROSS,0) - 
					 NVL(CF_SUBRO_EXP_PAID_GROSS,0) - 
					 NVL(CF_SUBRO_EXP_RETAINED_GROSS,0)))) 
					)),-1,0,(
		(SUM((NVL(CF_IND_RESERVE,0)+ 
		NVL(CF_DED_REFUND_PAID,0) + 
		NVL(CF_IND_PAID,0)) - 
		(NVL(CF_IND_RECOVERED,0)))) -
		((SUM(NVL(CF_SALV_RESERVE,0)) + 
		SUM(NVL(CF_SALV_RECOVERED_GROSS,0) - 
		NVL(CF_SALV_EXP_PAID_GROSS,0) - 
		NVL(CF_SALV_EXP_RETAINED_GROSS,0))) + 
		(SUM(NVL(CF_SUBRO_RESERVE,0)) + 
		SUM(NVL(CF_SUBRO_RECOVERED_GROSS,0) - 
		NVL(CF_SUBRO_EXP_PAID_GROSS,0) - 
		NVL(CF_SUBRO_EXP_RETAINED_GROSS,0))))) 
		                ) "NET_PAID_MIC",
		TRUNC(CR_REPORTED_DATE) 	"CR_REPORTED_DATE_MIC"
FROM    CLAIM_REGISTER,
		CLAIMANT_REGISTER,
		CLAIM_FINANCIALS,
		ANSWER_TABLE A,
		ANSWER_TABLE B
WHERE   CR_CLAIM_NO 	 	= CF_CLAIM_NO  
AND		CR_CLAIM_NO 	 	= CLR_CLAIM_NO 
AND     CLR_CLAIM_NO    	= CF_CLAIM_NO 
AND     CLR_LOC_MASTER_CODE = CF_LOCATION_CODE 
AND     CLR_COVERAGE_TYPE   = CF_COVERAGE_CODE 
AND		A.GTABLECODE1 		= 'CAUSE_OF_LOSS'
AND		B.GTABLECODE1 		= 'COVERAGE_TYPE'
AND		A.GTABLECODE2 		= CLR_COL_CODE
AND		B.GTABLECODE2 		= CLR_COVERAGE_TYPE
AND		CR_LINE 			= A.ANS_RETURNED_CHR
AND		TRUNC(CR_LOSS_DATE)            >= TO_DATE('04/30/2006 ','MM/DD/YYYY') 
AND     		TRUNC(CR_LOSS_DATE)    <= TO_DATE('02/08/2007 ','MM/DD/YYYY')
AND		TRUNC(CR_REPORTED_DATE)        <= TO_DATE('03/06/2007 ','MM/DD/YYYY')
GROUP BY  	CLR_CLAIM_NO,
			CLR_LOCATION_CODE,
			CLR_COVERAGE_TYPE,
		    CR_POLICY_NO || '-' || CR_POLICY_RENEW_NO,
	        CR_POLICY_RENEW_NO,
			CR_POLICY_NO,
		    CR_LOSS_DATE,
			'SOURCE OF CLAIM',
			CLR_NO_OF_VEHICLES,
			CLR_COL_CODE,
	       	TRUNC(CR_REPORTED_DATE),
			A.GTABLENAME,
			B.GTABLENAME
ORDER BY    CR_LOSS_DATE




Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	11  	 	      	             	 
  SORT GROUP BY		1  	241  	11  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	RENDEV.CLAIM_FINANCIALS	1  	51  	2  	 	      	             	 
      NESTED LOOPS		1  	241  	9  	 	      	             	 
        NESTED LOOPS		1  	190  	7  	 	      	             	 
          MERGE JOIN CARTESIAN		1  	145  	5  	 	      	             	 
            NESTED LOOPS		1  	98  	4  	 	      	             	 
              INDEX FAST FULL SCAN	RENDEV.DLR_SRH_RPT_IDX	1  	45  	3  	 	      	             	 
              INDEX RANGE SCAN	RENDEV.ANS_DLR_SRCH_IDX	1  	53  	1  	 	      	             	 
            BUFFER SORT		11  	517  	4  	 	      	             	 
              INDEX RANGE SCAN	RENDEV.ANS_DLR_SRCH_IDX	11  	517  	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	RENDEV.CLAIMANT_REGISTER	1  	45  	2  	 	      	             	 
            INDEX RANGE SCAN	RENDEV.CLR_CLAIM_IDX	1  	 	1  	 	      	             	 
        INDEX RANGE SCAN	RENDEV.CF_CLAIM_IDX	1  	 	1