with data as (
    select
        :sa_code as sa_code
        , c.sid
        , i.inv_account_name
        , m.inv_fund_unit_account as ifua
        , i.one_time_subs_flag
        , c.client_code as cif_client_code
        , m.client_code as ifua_client_code
        , to_char(i.created_date, 'yyyy-mm-dd') as created_date
        , to_char(i.last_updated_date, 'yyyy-mm-dd') as last_updated_date
    from
        mst_customers c
        join mst_inv_holders h on h.customer_id = c.customer_id and h.primary_holder_flag = -1
        join mst_investment_accounts i on h.inv_account_id = i.inv_account_id
        join mst_settlement_accounts a on h.stl_account_id = a.stl_account_id
        join mst_inv_sinvest_mapping m on i.inv_account_id = m.inv_account_id
    where 1 = 1
        and i.delete_status <> -1
    --    and (
    --        (trunc(i.created_date) between trunc(to_date(:ddmmyyyy_01_from, 'ddmmyyyy')) and trunc(to_date(:ddmmyyyy_02_to, 'ddmmyyyy')))
    --        or (trunc(i.last_updated_date) between trunc(to_date(:ddmmyyyy_01_from, 'ddmmyyyy')) and trunc(to_date(:ddmmyyyy_02_to, 'ddmmyyyy')))
    --    )
)
select
    d.sa_code
    , d.sid
    , d.inv_account_name
    , d.ifua_client_code
    , d.sa_code || '|' || d.sid || '|' || d.inv_account_name || '|' || d.ifua_client_code as text_content
from
    data d
where
    d.one_time_subs_flag = -1
    and d.ifua is null
;

-- with CIF number and inv. account number
with data as (
    select
        c.cif
        , i.inv_account_no
        , :sa_code as sa_code
        , c.sid
        , i.inv_account_name
        , m.inv_fund_unit_account as ifua
        , i.one_time_subs_flag
        , c.client_code as cif_client_code
        , m.client_code as ifua_client_code
        , to_char(i.created_date, 'yyyy-mm-dd') as created_date
        , to_char(i.last_updated_date, 'yyyy-mm-dd') as last_updated_date
    from
        mst_customers c
        join mst_inv_holders h on h.customer_id = c.customer_id and h.primary_holder_flag = -1
        join mst_investment_accounts i on h.inv_account_id = i.inv_account_id
        join mst_settlement_accounts a on h.stl_account_id = a.stl_account_id
        join mst_inv_sinvest_mapping m on i.inv_account_id = m.inv_account_id
    where 1 = 1
        and i.delete_status <> -1
    --    and (
    --        (trunc(i.created_date) between trunc(to_date(:ddmmyyyy_01_from, 'ddmmyyyy')) and trunc(to_date(:ddmmyyyy_02_to, 'ddmmyyyy')))
    --        or (trunc(i.last_updated_date) between trunc(to_date(:ddmmyyyy_01_from, 'ddmmyyyy')) and trunc(to_date(:ddmmyyyy_02_to, 'ddmmyyyy')))
    --    )
)
select
    d.cif
    , d.inv_account_no
    , d.sa_code
    , d.sid
    , d.inv_account_name
    , d.ifua_client_code
    , d.sa_code || '|' || d.sid || '|' || d.inv_account_name || '|' || d.ifua_client_code as text_content
from
    data d
where
    d.one_time_subs_flag = -1
    and d.ifua is null
;