WITH CTE_MainQuery AS (
    SELECT
        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,
        offmode.Name AS RECEIVED_MODE,
        CASE WHEN offmode.Name = 'Cash' THEN offmed1.Name ELSE NULL END AS CASH_MEDIUM,
        CASE WHEN offmode.Name <> 'Cash' THEN offmed2.Name ELSE NULL END AS 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,
        PAYBANK.CHQ_DD_NUM,
        PAYBANK.DEPOS_TRANSF_DD__CHQ_ISSUE_DATE,
        PAYBANKRI.NEFT_RTGS_REF_NUM,
        PAYBANKRI.RECEIVED_MEDIUM_ID,
        PAYCASH.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 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
    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 (
        SELECT
            rbd.PayeeBankId,  -- Ensure that PayeeBankId exists in the rbd table
            CAST(rbd.TransactionDateTime AS Date) AS DEPOS_TRANSF_DD__CHQ_ISSUE_DATE,
            rbd.TransactionReference AS CHQ_DD_NUM,
            rbd.AccountId,  -- Ensure that AccountId exists in the rbd table
            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  -- Ensure that AccountId exists in the rbd table
        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 (
        SELECT
            ReceivedDateTime AS MO_TRANS_DATE,
            ReceiptId
        FROM
            ReceiptDetail
        WHERE
            TransactionTypeId = 1 AND IsDeleted = 0 AND TransactionMediumId IN (6, 9)
    ) AS PAYCASH ON RCPT.RECEIPTID = PAYCASH.RECEIPTID
    WHERE
        RCPT.IsDeleted = 0
)
SELECT TOP 10 *
FROM CTE_MainQuery
WHERE PARTNER_ID = '18502'
ORDER BY CREATED_TIME DESC;