Effective sql when a BankCollectionRemitter search receipts.

bankremitter_search_findCashRemittanceDetailsForServiceAndFund.sql

SELECT
	sum(ih.instrumentamount) AS INSTRUMENTMAOUNT,
	date(ch.RECEIPTDATE) AS RECEIPTDATE,
	sd.NAME AS SERVICENAME,
	it.TYPE AS INSTRUMENTTYPE,
	fnd.name AS FUNDNAME,
	dpt.name AS DEPARTMENTNAME,
	fnd.code AS FUNDCODE,
	dpt.code AS DEPARTMENTCODE,
	us.name AS APPROVERNAME,
	us.id AS APPROVERID
FROM
	EGCL_COLLECTIONHEADER ch,
	EGF_INSTRUMENTHEADER ih,
	EGCL_COLLECTIONINSTRUMENT ci,
	EGCL_SERVICEDETAILS sd,
	EGF_INSTRUMENTTYPE it,
	EGCL_COLLECTIONMIS cm,
	FUND fnd,
	EG_DEPARTMENT dpt,
	eg_user us
WHERE
	us.id = ch.lastmodifiedby
	AND ch.id = cm.collectionheader
	AND fnd.id = cm.fund
	AND dpt.id = cm.department
	AND ci.INSTRUMENTHEADER = ih.ID
	AND ch.SERVICEDETAILS = sd.ID
	AND ch.ID = ci.COLLECTIONHEADER
	AND ih.INSTRUMENTTYPE = it.ID
	AND sd.code in('PT')
	AND fnd.code in('01')
	AND it.TYPE in('cash')
	AND ih.ID_STATUS = (
		SELECT
			id
		FROM
			egw_status
		WHERE
			moduletype = 'Instrument'
			AND description = 'New')
		AND ih.ISPAYCHEQUE = '0'
		AND ch.STATUS = (
			SELECT
				id
			FROM
				egw_status
			WHERE
				moduletype = 'ReceiptHeader'
				AND code = 'APPROVED')
			AND ch.source = 'SYSTEM'
			AND date(ch.receiptdate) BETWEEN '2019-04-01 00:00:00.0'
			AND '2020-03-31 00:00:00.0'
			AND cm.depositedbranch = 4
		GROUP BY
			date(ch.RECEIPTDATE),
			sd.NAME,
			it.TYPE,
			fnd.name,
			dpt.name,
			fnd.code,
			dpt.code,
			ch.lastmodifiedby,
			us.name,
			us.id
		ORDER BY
			RECEIPTDATE;