SELECT custodian_trx.reference_id,       
       cms_checklist_item.status item_status, stage_checklist_item.status stage_item_status,
       custodian_trx.reason, custodian_trx.checklist_item_ref_id,
       cms_checklist_item.doc_item_ref checklist_item_ref_id,
       cms_checklist.cms_lsp_lmt_profile_id cms_lsp_appr_lmts_id, cms_checklist.checklist_id,
       cms_checklist.cms_pledgor_dtl_id, cms_checklist.cms_lmp_sub_profile_id,
       cms_checklist.cms_collateral_id, cms_checklist.CATEGORY, cms_checklist.sub_category,
       custodian_trx.transaction_id, NVL (custodian_trx.trxstatus, 'RECEIVED') trxstatus,
       custodian_trx.transaction_date, cms_checklist_item.document_code,
       cms_checklist_item.doc_item_ref, cms_checklist_item.doc_description,
       cms_checklist_item.last_update_date, cms_checklist_item.remarks narration,
       cms_checklist_item.doc_date doc_date, cms_checklist_item.expiry_date doc_expiry_date,
       stage_checklist_item.last_update_date stage_item_last_update,
       stage_checklist_item.cpc_cust_status stage_cpc_cust_status,
       cms_checklist_item.cpc_cust_status cpc_cust_status,
       cms_cust_doc.reversal_remarks reversal_remarks, cms_cust_doc.reversal_rmk_updated_userinfo,
       cms_cust_doc_item.last_update_date custodian_last_update,
       cms_cust_doc_item.custodian_doc_item_id, cms_checklist.status checklist_status,
       sci_le_main_profile.lmp_long_name borrower_name,
       sci_le_main_profile.lmp_le_id borrower_le_id
  FROM cms_checklist,
       sci_le_main_profile,
       sci_le_sub_profile,
       cms_checklist_item,
       stage_checklist_item,
       cms_cust_doc,
       cms_cust_doc_item,
       (SELECT cms_stage_cust_doc_item.reason, cms_stage_cust_doc_item.status,
               cms_stage_cust_doc_item.checklist_item_ref_id, cms_stage_cust_doc.checklist_id,
               TRANSACTION.transaction_id, TRANSACTION.reference_id, TRANSACTION.transaction_type,
               TRANSACTION.transaction_date, TRANSACTION.status trxstatus
          FROM TRANSACTION,
               cms_stage_cust_doc,
               cms_stage_cust_doc_item,
               (SELECT   stage_cust_doc_item.checklist_item_ref_id,
                         MAX (stage_cust_doc_item.custodian_doc_item_id) custodian_doc_item_id
                    FROM cms_stage_cust_doc stage_cust_doc,
                         cms_stage_cust_doc_item stage_cust_doc_item
                   WHERE stage_cust_doc.custodian_doc_id = stage_cust_doc_item.custodian_doc_id
                     AND stage_cust_doc.checklist_id = 20061027645550    
                GROUP BY stage_cust_doc_item.checklist_item_ref_id) max_stage_cust_doc
         WHERE TRANSACTION.transaction_type = 'CUSTODIAN'
           AND TRANSACTION.staging_reference_id = cms_stage_cust_doc.custodian_doc_id
           AND cms_stage_cust_doc.custodian_doc_id = cms_stage_cust_doc_item.custodian_doc_id
           AND cms_stage_cust_doc_item.custodian_doc_item_id = max_stage_cust_doc.custodian_doc_item_id
           AND TRANSACTION.status <> 'CLOSED') custodian_trx
 WHERE cms_checklist.checklist_id = cms_checklist_item.checklist_id
   AND cms_checklist.checklist_id = cms_cust_doc.checklist_id(+)
   AND sci_le_main_profile.cms_le_main_profile_id(+) = sci_le_sub_profile.cms_le_main_profile_id
   AND sci_le_sub_profile.cms_le_sub_profile_id(+) = cms_checklist.cms_lmp_sub_profile_id
   AND cms_cust_doc.custodian_doc_id = cms_cust_doc_item.custodian_doc_id
   AND cms_checklist_item.doc_item_ref = cms_cust_doc_item.checklist_item_ref_id(+)
   AND cms_checklist_item.doc_item_ref = custodian_trx.checklist_item_ref_id(+)
   AND cms_checklist_item.is_deleted = 'N'
   AND (   cms_checklist_item.status <> 'DELETED'
        OR (cms_checklist_item.status = 'DELETED' AND custodian_trx.status IS NOT NULL)
       )
   AND stage_checklist_item.doc_item_id = (SELECT MAX (stage_chklist_item.doc_item_id)
                             			     FROM stage_checklist_item stage_chklist_item
                            			   WHERE stage_chklist_item.doc_item_ref = cms_checklist_item.doc_item_ref)