select  so.cpos_po_key as pono,
        so.cpos_ref as refkey,
        so.date_order as orderdate,
        so.write_date as statusdate,
        so.id,
        so.name as sono,
        so.process_status as status,
        rp.name,
        rp.store_code
from    sale_order as so
        left join res_partner as rp 
on      rp.id = so.partner_id
where   rp.cust_type = 'zota' and so.write_date>(CURRENT_DATE - 1) AND COALESCE(so.cpos_po_key,0)!=0
       AND so.id not in (SELECT   A.id
                        FROM   (select  so.id,
                                        regexp_split_to_table(so.cpos_po_key1, E',') as POKey,
                                        so.partner_id
                               from      sale_order as so 
                                        left join res_partner as rp 
                                on      rp.id = so.partner_id
                            WHERE       rp.cust_type = 'zota' 
                                        and cpos_po_key1 like '%,%' 
                                        and so.write_date>(CURRENT_DATE - 1)
                                )AS DT 
                                INNER JOIN sale_order AS A 
                       ON      CAST(DT.POKey AS DECIMAL)=CAST(A.cpos_ref  AS DECIMAL)
                                AND cast(DT.partner_id as DECIMAL)=cast(A.partner_id as DECIMAL))
UNION ALL
SELECT  A.cpos_po_key as pono,
        DT.POKey as refkey,
        A.date_order as orderdate,
        DT.write_date as statusdate,
        DT.id,
        DT.sono as sono,
        DT.status as status,
        rp.name,
        rp.store_code
FROM   (select      rp.store_code,
                    so.write_date as statusdate,
                    so.id,
                    so.process_status as status,
                    so.write_date,
                    so.name as sono,
                    regexp_split_to_table(so.cpos_po_key1, E',') as POKey,
                    so.partner_id
          from      sale_order as so 
                    left join res_partner as rp 
            on      rp.id = so.partner_id
        WHERE       rp.cust_type = 'zota' 
                    and cpos_po_key1 like '%,%' 
                    and so.write_date>(CURRENT_DATE - 1)
       )AS DT 
        INNER JOIN sale_order AS A 
ON      CAST(DT.POKey AS DECIMAL)=CAST(A.cpos_ref  AS DECIMAL)
        AND cast(DT.partner_id as DECIMAL)=cast(A.partner_id as DECIMAL)
        left join res_partner as rp 
 on     rp.id = DT.partner_id
 WHERE   COALESCE(A.cpos_po_key,0)!=0