with t3 as (
select ting_corp_num as corp_num
from corp_involved_amalgamating
union
select ted_corp_num as corp_num
from corp_involved_amalgamating
)
, account_map AS (
SELECT mca.corp_num,
array_to_string(array_agg(DISTINCT mca.account_id ORDER BY mca.account_id), ',') AS account_ids
FROM mig_corp_account mca
JOIN mig_batch b2 ON b2.id = mca.mig_batch_id
WHERE mca.target_environment = 'dev'
AND b2.id IN (112)
AND b2.mig_group_id IN (34)
GROUP BY mca.corp_num
)
SELECT c.corp_num,
c.corp_type_cd,
b.id AS mig_batch_id,
COALESCE(am.account_ids, NULL::varchar(100)) AS account_ids,
cs.state_type_cd,
cp.flow_name,
cp.processed_status,
cp.last_processed_event_id,
cp.failed_event_id,
cp.failed_event_file_type
from corporation c
left outer join corp_state cs
on cs.corp_num = c.corp_num
JOIN mig_corp_batch mcb ON mcb.corp_num = c.corp_num
JOIN mig_batch b ON b.id = mcb.mig_batch_id
JOIN mig_group g ON g.id = b.mig_group_id
LEFT JOIN account_map am ON am.corp_num = c.corp_num
left outer join corp_processing cp
on cp.corp_num = c.corp_num
and cp.flow_name = 'tombstone-flow'
and cp.environment = 'dev'
where 1=1
and not exists (
select 1
from t3
where t3.corp_num = c.corp_num
)
AND b.id IN (112) AND g.id IN (34)
and c.corp_type_cd in ('BC', 'C', 'ULC', 'CUL', 'CC', 'CCC', 'QA', 'QB', 'QC', 'QD', 'QE')
and cs.end_event_id is null
and cp.processed_status is null
and cp.flow_run_id is null
limit 1
