SELECT receivable_paid.external_receipt_nbr, 
       bp_main.business_partner_nme, 
       receivable_paid.receipt_dte, 
       receivable_paid.receipt_amt, 
       receivable_paid.unallocated_amt, 
       receivable_paid.inserted_by, 
       receivable_paid.account_nbr, 
       receivable_paid.cheque_nbr, 
       receivable_paid.refund_to, 
       receivable_paid.comment_cde, 
       receivable_paid.receipt_amt - receivable_paid.unallocated_amt    AS 
       allocated_amt, 
       (SELECT business_partner_nme 
        FROM   bp_main 
        WHERE  ( business_partner_id = receivable_paid.bp_primary_id )) AS 
       bank_nme, 
       (SELECT external_contract_nbr 
        FROM   CONTRACT 
        WHERE  ( contract_id = receivable_paid.contract_id ))           AS 
       contract_no, 
       (SELECT MAX(history_creation_dte) AS expr1 
        FROM   receivable_paid_htr 
        WHERE  ( receipt_id = receivable_paid.receipt_id ))             AS 
       allocation_dte, 
       (SELECT comment_dsc 
        FROM   comment_code 
        WHERE  ( comment_cde = receivable_paid.comment_cde ) 
               AND ( event_cde = '00016' ))                             AS 
       reason_dsc 
FROM   receivable_paid 
       LEFT JOIN bp_main 
         ON bp_main.business_partner_id = receivable_paid.business_partner_id 
WHERE 
/* [start added ] by Mehmood Ahmed on Dated : 16-july-2010 against HD22376 */ 
receivable_paid.receipt_id IN (SELECT DISTINCT misc_rep.receipt_id 
                               FROM   (SELECT r.receipt_id, 
                                              r.receipt_amt, 
                                              r.receipt_sts 
                                       FROM   receivable_paid r 
                                              LEFT JOIN receivable_paid_htr htr 
                                                ON r.receipt_id = htr.receipt_id 
                                       WHERE  r.receipt_sts = 'U' 
                                              AND htr.contract_id IS NULL 
                                       UNION 
                                       SELECT r.receipt_id, 
                                              r.receipt_amt, 
                                              htr2.receipt_sts 
                                       FROM   receivable_paid r 
                                              LEFT JOIN (SELECT 
                                              receipt_id, 
                                              MIN(history_seq) min_history_seq 
                                                         FROM 
                                              receivable_paid_htr 
                                                         GROUP  BY receipt_id) 
                                                        htr 
                                                ON r.receipt_id = htr.receipt_id 
                                              JOIN receivable_paid_htr htr2 
                                                ON htr.receipt_id = 
                                                   htr2.receipt_id 
                                                   AND htr.min_history_seq = 
                                                       htr2.history_seq 
                                       WHERE  r.receipt_sts <> 'U' 
                                              AND htr2.[receipt_sts] IN ( 'U', 
                                                  'R' ) 
                                              AND htr2.contract_id IS NULL) 
                                      misc_rep) 
/* [End added ] by Mehmood Ahmed on Dated : 16-july-2010 against HD22376 */ 
AND ( ( ( CONVERT(DATETIME, CONVERT (VARCHAR, receivable_paid.receipt_dte, 112)) 
        ) 
        >= 
              ( 
              CONVERT(DATETIME, CONVERT (VARCHAR, '2010-02-24', 112 )) ) ) 
      AND ( ( CONVERT(DATETIME, CONVERT (VARCHAR, receivable_paid.receipt_dte, 
              112)) 
            ) <= 
            ( 
                CONVERT(DATETIME, CONVERT (VARCHAR, '2010-02-24', 112 )) ) ) )