th CTE_MainQuery  
 As ( select 
	distinct  part.Code as PARTNER_ID,
	orm.ReceiptDetailId,
	concat(coalesce(tit.NAME, ''), ' ', ISNULL(part.FirstName, '')+ ISNULL(part.MiddleName, '')+ ISNULL(part.LastName, '')) as FULL_NAME,
	partconm.Contact as PRIMARY_MOBILE_NO,
	partcone.Contact as EMAIL_ID,
	patraddr.ApartmentFlatDoorNumber as ADDRESS_LINE1,
	patraddr.StreetOrRoadName as ADDRESS_LINE2,
	patraddr.LandMark as ADDRESS_LINE3,
	patraddr.City as CITY,
	PCM.PinCode as PINCODE,
	patraddr.District as DISTRICT,
	'' as PROVINCE,
	st.Name as STATE_NAME,
	cnt.NAME as COUNTRY_NAME,
	partper.PanId as pan_id,
	'' as ROLES,
	RCPT.Code as RECEIPT_DISP_NUM,
	RCPT.Code as RECEIPT_ID,
	RCPT.REMARKS,
	RCPT.CreatedDateTime as CREATED_TIME,
	stfdtls.NAME as STAFF_NAME,
	'' as AMBASSTOR_NAME,
	cast(SWITCHOFFSET(orm.ReceivedDateTime, '+05:30')as Date) as 'RECEIPT_DATE',
	eve.Name as EVENT_NAME,
	concat(offtypes.Name, ' ', eft.Name) as OFFERING_NAME,
	concat(offtypes.Name, ' ', eft.Name) as offering_types,
	orm.Amount as OFFERING,
	orm.Amount as TOTAL_AMOUNT,
	--orm.Amount as OFFERING,
	offmode.Name as RECEIVED_MODE,
	case
	when (offmode.Name = 'Cash') then offmed1.Name
	else null
	end CASH_MEDIUM,
	case
	when (offmode.Name <> 'Cash') then offmed2.Name
	else null
	end BANK_MEDIUM,
	GTWAYDETLS.Name as GATEWAY_NAME,
	BANKDTLS.Name as BANK_NAME,
	BANKDTLS1.Name as BANK_NAME_RTGS,
	BANKDTLS2.Name as BANK_NAME_GTWAY,
	DBKDETLS2.Name as DRAWEE_BANK_GTWAY,
	DBKDETLS1.Name as DRAWEE_BANK_RTGS,
	DBKDETLS.Name as DRAWEE_BANK,
        GTWAY.TRANSACTION_DETAILS,
	--RCPT.REMARKS,
	PAYBANK.CHQ_DD_NUM,
	PAYBANK.DEPOS_TRANSF_DD__CHQ_ISSUE_DATE,
	(select 
		Distinct rbd.TransactionReference 		
	from
		receiptbankdetail rbd
	join ReceiptDetail r on
		rbd.ReceiptBankDetailId = r.ReceiptBankDetailId
	where
		r.TransactionTypeId = 2
		and r.IsDeleted = 0
		and r.TransactionMediumId in (2, 11, 12, 13) and
		r.ReceiptId=PAYBANKRI.ReceiptId and 
		rbd.ReceiptBankDetailId=PAYBANKRI.ReceiptBankDetailId) As NEFT_RTGS_REF_NUM,
	PAYBANKRI.RECEIVED_MEDIUM_ID,
	PAYCASH.ReceivedDateTime as MO_TRANS_DATE,
	GTWAY.PAYMENT_GATE_TRANS_DATE
from
	RECEIPT as RCPT
inner join ReceiptDetail orm on
	orm.RECEIPTID = rcpt.RECEIPTID
	and orm.IsDeleted = 0
inner join partner part on
	part.PARTNERID = RCPT.PARTNERID
left join partneraddressdetail patraddr on
	patraddr.PartnerId = RCPT.PARTNERID
left join Partnercontactdetail partconm on
	partconm.PARTNERID = part.PARTNERID
	and partconm.CommunicationModeId = 2
	and partconm.IsPrimary = 1
left join Partnercontactdetail partcone on
	partcone.PARTNERID = part.PARTNERID
	and partcone.CommunicationModeId = 1
	and partcone.IsPrimary = 1
left join PartnerPersonalDetail partper on
	partper.PartnerPersonalDetailId = part.PartnerPersonalDetailId
left join title as tit on
	part.TITLEID = tit.TITLEID
left join dbo.PinCodeMap PCM on
	PCM.PinCodeMapId = patraddr.PinCodeMapId
left join state as st on
	PCM.STATEID = st.STATEID
left join country as cnt on
	st.COUNTRYID = cnt.COUNTRYID
left join [user] as stfdtls on
	stfdtls.Userid = RCPT.CreatedByUserId
left join (
	select
		ReceivedDateTime,
		ReceiptId
	from
		ReceiptDetail
	where
		TransactionTypeId = 1
		and IsDeleted = 0
		and TransactionMediumId in (6, 9)) as PAYCASH on
	RCPT.RECEIPTID = PAYCASH.RECEIPTID
left join (
	select
		rbd.PayeeBankId,
		Cast (rbd.TransactionDateTime As Date) as DEPOS_TRANSF_DD__CHQ_ISSUE_DATE,
		rbd.TransactionReference as CHQ_DD_NUM,
		rbd.AccountId,
		r.ReceiptId
	from
		receiptbankdetail rbd
	join ReceiptDetail r on
		rbd.ReceiptBankDetailId = r.ReceiptBankDetailId
		and r.IsDeleted = 0
	where
		r.TransactionTypeId = 2
		and r.TransactionMediumId in (3, 5)) as PAYBANK on
	RCPT.ReceiptId = PAYBANK.ReceiptId
left join (
	select
		distinct rbd.PayeeBankId ,
		rbd.ReceiptBankDetailId,
		r.TransactionMediumId as RECEIVED_MEDIUM_ID,
		Cast (rbd.TransactionDateTime As Date) as DEPOS_TRANSF_DD__CHQ_ISSUE_DATE,
		rbd.TransactionReference as NEFT_RTGS_REF_NUM,
		r.ReceiptId,
		rbd.AccountId
	from
		receiptbankdetail rbd
	join ReceiptDetail r on
		rbd.ReceiptBankDetailId = r.ReceiptBankDetailId
	where
		r.TransactionTypeId = 2
		and r.IsDeleted = 0
		and r.TransactionMediumId in (2, 11, 12, 13)) as PAYBANKRI on
	RCPT.ReceiptId = PAYBANKRI.ReceiptId
left join (
	select
		Cast (rbd.TransactionDateTime As Date) as PAYMENT_GATE_TRANS_DATE ,
		rbd.TransactionReference as TRANSACTION_DETAILS,
		r.ReceiptId,
		r.TransactionTypeId,
		rbd.AccountId,
		rbd.PayeeBankId
	from
		receiptbankdetail rbd
	join ReceiptDetail r on
		rbd.ReceiptBankDetailId = r.ReceiptBankDetailId
	where
		r.TransactionTypeId = 3
		and r.IsDeleted = 0
		and r.TransactionMediumId in (7, 8, 10))as GTWAY on
	RCPT.ReceiptId = GTWAY.ReceiptId
left join dbo.Account as BANKDTLS on
	PAYBANK.AccountId = BANKDTLS.AccountId
left join dbo.Account as BANKDTLS1 on
	PAYBANKRI.AccountId = BANKDTLS1.AccountId
left join dbo.Account as BANKDTLS2 on
	GTWAY.AccountId = BANKDTLS2.AccountId
left join bank as DBKDETLS on
	PAYBANK.PayeeBankId = DBKDETLS.BankId
left join bank as DBKDETLS1 on
	PAYBANKRI.PayeeBankId = DBKDETLS1.BankId
left join bank as DBKDETLS2 on
	GTWAY.PayeeBankId = DBKDETLS2.BankId
left join PaymentGateway as GTWAYDETLS on
	GTWAY.TransactionTypeId = GTWAYDETLS.PaymentGATEWAYID
left join TransactionType as offmode on
	orm.TransactionTypeId = offmode.TransactionTypeId
left join TransactionMedium as offmed1 on
	orm.TransactionMediumId = offmed1.TransactionMediumId
left join TransactionMedium as offmed2 on
	orm.TransactionMediumId = offmed2.TransactionMediumId
left join event as eve on
	orm.EventId = eve.EventId
left join OfferingType as offtypes on
	orm.OFFERINGTYPEID = offtypes.OFFERINGTYPEID
left join EventFeesType eft on
	orm.EventFeesTypeId = eft.EventFeesTypeId

where
	RCPT.IsDeleted = 0

	)
	 
	Select TOP 10 * from CTE_MainQuery where PARTNER_ID ='81712'
	Order by CREATED_TIME Desc