Unfortunately, I wasn’t able to open the document containing exec plan, but based on the code, it seems to me that there are quite a few problems with your procedure (and table/index structure).
I will probably not cover all of them, but what is, in my opinion, obvious is the following:
1. Indexes, these that exist, are not set up properly.
Table CR_CO_LOANEE_LEDGER has unique clustered index on fields:
Non-cluster index has clustered index included (as a pointer). This means that creating index:
IX_CR_CO_LOANEE_LEDGER_MEM_RB consisting of:
MEMBER_CODE, LOAN_APPLY_DATE,RECEIPT_BOOK,RECEIPT_NO makes not much sense.
MEMBER_CODE, TRANS_ID,LOAD_APPLY_DATE will be part of that non-clustered index anyway so you should consider designing different index.
Same with IX_CR_CO_LOANEE_LEDGER_Member.
Sorry that I am not going into detailed explanation here.
(Gail Show has great articles about indexes, check:
2. Procedure is using SET DATEFORMAT which is executed at run time, not at parse time and it affects re-usage of cached execution plan. If possible, try to move it out of procedure.
3. Add SET NOCOUNT ON to procedure
4. Almost all parameters are sent as VARCHAR (even dates). Try to change parameters to really match the type of the passed values
5. Consider possibility of sorting outside DB layer
When all of the above is covered, see how it goes and get back with new perf results.