SQL & PL/SQL

Hi

I have a query with will take almost 12:40 min/sec. I want to tune this. Can anyone help me in this regard.

SELECT
         stgc_ccr_cc_claim.source_system_code
       , ins_home_claim_drv.id
       , DECODE(stgc_ccr_cc_claim.retired, 0, 'N', 'Y')
       , stgc_ccr_cc_claim.claimnumber
       , stgc_ccr_cc_claim.sc_tempaccommodation
       , stgc_ccr_cc_claim.policyid
       , stgc_ccr_cc_claim.id
       , stgc_ccr_cc_claim.state
       , stgc_ccr_cc_claim.sc_claimdecision
       , stgc_ccr_cc_claim.sc_claimtype
       , stgc_ccr_cc_claim.sc_closedoutcome
       , stgc_ccr_cc_claim.catastropheid
       , stgc_ccr_cc_claim.strategy
       , stgc_ccr_cc_claim.losscause
       , CASE
WHEN edw_home_claim.lodgement_date IS NOT NULL
	THEN CASE WHEN DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N'
			THEN edw_home_claim.lodgement_date
		ELSE NULL
	END
  ELSE
    CASE
      WHEN (edw_home_claim.src_id IS NULL
		AND DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N')
       THEN stgc_ccr_cc_claim.reporteddate
	WHEN (edw_home_claim.src_id IS NOT NULL
		AND DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N') = 'N')
	THEN stgc_ccr_cc_claim.updatetime
      ELSE NULL
  END
END
       , stgc_ccr_cc_policylocation.addressid
       , stgc_ccr_cc_claim.createtime
       , stgc_ccr_cc_claim.updatetime
       , stgc_ccr_cc_claim.closedate
       , DECODE(stgc_ccr_cctl_sc_yesno.typecode, 'Yes', 'Y', 'N')
       , stgc_ccr_cc_incident_latest_v.sc_totaloriginalvalue
       , stgc_ccr_cc_claim.cdc_action
       , ins_home_claim_drv.cdc_timestamp
       , stgc_ccr_cc_claim.mds_stage_acquisition_sk
       
  FROM ins_home_claim_drv ins_home_claim_drv
  JOIN stgc_ccr_cc_claim stgc_ccr_cc_claim
  ON  ins_home_claim_drv.id = stgc_ccr_cc_claim.id
  AND ins_home_claim_drv.cdc_timestamp = stgc_ccr_cc_claim.cdc_timestamp
  LEFT OUTER JOIN stgc_ccr_cc_policy stgc_ccr_cc_policy
  ON  stgc_ccr_cc_claim.policyid = stgc_ccr_cc_policy.id
  AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_policy.dss_start_date AND stgc_ccr_cc_policy.dss_end_date
  LEFT OUTER JOIN stgc_ccr_cctl_sc_lineofbusiness stgc_ccr_cctl_sc_lineofbusiness
  ON stgc_ccr_cc_policy.sc_lineofbusiness = stgc_ccr_cctl_sc_lineofbusiness.id
  AND stgc_ccr_cctl_sc_lineofbusiness.dss_current_flag = 'Y'
  LEFT OUTER JOIN stgc_ccr_cctl_sc_yesno stgc_ccr_cctl_sc_yesno
  ON  stgc_ccr_cc_claim.sc_incidentreport = stgc_ccr_cctl_sc_yesno.id
  AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_sc_yesno.dss_start_date AND stgc_ccr_cctl_sc_yesno.dss_end_date
  LEFT OUTER JOIN stgc_ccr_cctl_claimstate stgc_ccr_cctl_claimstate
  ON stgc_ccr_cc_claim.state = stgc_ccr_cctl_claimstate.id
  AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_claimstate.dss_start_date AND stgc_ccr_cctl_claimstate.dss_end_date
  LEFT OUTER JOIN stgc_ccr_cctl_losstype stgc_ccr_cctl_losstype
  ON stgc_ccr_cc_claim.losstype = stgc_ccr_cctl_losstype.id
  AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cctl_losstype.dss_start_date AND stgc_ccr_cctl_losstype.dss_end_date
  LEFT OUTER JOIN stgc_ccr_cc_policylocation stgc_ccr_cc_policylocation
  on stgc_ccr_cc_claim.sc_policylocationfk = stgc_ccr_cc_policylocation.id
  AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_policylocation.dss_start_date AND stgc_ccr_cc_policylocation.dss_end_date
  AND stgc_ccr_cc_policylocation.retired = 0
  LEFT OUTER JOIN edw_home_claim edw_home_claim
  ON stgc_ccr_cc_claim.source_system_code = edw_home_claim.source_system_code
  AND stgc_ccr_cc_claim.id = edw_home_claim.src_id
  AND edw_home_claim.dss_current_flag = 'Y'
  LEFT OUTER JOIN stgc_ccr_cc_incident_latest_v stgc_ccr_cc_incident_latest_v
  ON stgc_ccr_cc_claim.id = stgc_ccr_cc_incident_latest_v.claimid
  AND ins_home_claim_drv.cdc_timestamp BETWEEN stgc_ccr_cc_incident_latest_v.dss_start_date AND stgc_ccr_cc_incident_latest_v.dss_end_date
  WHERE stgc_ccr_cctl_sc_lineofbusiness.typecode = 'PI'
  AND stgc_ccr_cctl_claimstate.typecode != 'draft'
  AND stgc_ccr_cctl_losstype.typecode = 'PR'
 ;

Thank u
AR