SELECT DISTINCT a.trx_reference_id, a.transaction_id, a.cur_trx_history_id, a.reference_id,
                        a.transaction_type, a.transaction_subtype, a.status, a.legal_id,
                        a.limit_profile_id, a.legal_name AS lname, a.customer_name AS cname,
                        a.customer_id, a.transaction_date, a.trx_origin_country, b.user_state,
                        b.user_trx_type, c.llp_bca_ref_appr_date, c.cms_orig_country,
                        c.cms_bca_create_date, d.country_name, sp.lsp_id, mp.lmp_le_id,
                        sp.lsp_short_name AS customer_name, mp.lmp_long_name AS legal_name,
                        UPPER (mp.lmp_long_name) AS upper_legal_name, 
						a.deal_no, ' ' AS task_flag
                   FROM TRANSACTION a,
                        cms_trx_totrack b,
                        sci_lsp_lmt_profile c,
                        country d,
                        sci_le_sub_profile sp,
                        sci_le_main_profile mp,
                        sci_lsp_appr_lmts l
                  WHERE sp.cms_le_main_profile_id = mp.cms_le_main_profile_id(+)
                    AND a.customer_id = sp.cms_le_sub_profile_id(+)                    
                    AND l.cms_limit_status != 'DELETED'
                    AND a.transaction_type = b.transaction_type
                    AND a.status = b.curr_state
                    AND (a.from_state = b.from_state OR b.from_state IS NULL)
                    AND (a.transaction_subtype = b.transaction_subtype  OR b.transaction_subtype IS NULL )
					AND c.cms_lsp_lmt_profile_id = l.cms_limit_profile_id
                    AND c.cms_lsp_lmt_profile_id = a.limit_profile_id 
                    AND c.cms_orig_country = d.country_iso_code
					AND c.cms_orig_country IN ('SG')
                    AND c.cms_bca_complete_ind != 'Y'					
					AND (c.cms_orig_organisation IN ('SG')  OR l.cms_bkg_organisation IN ('SCBL')   )
                    AND a.transaction_type IN ('LIMIT')
                    AND a.status = 'ACTIVE'                    
                    AND mp.lmp_sgmnt_code_value IN ('A','B')