我假设这是正确的平衡，对于每个JE_HEADER_ID total cr == total dr。此外，对于每个JE_HEADER_ID，仅存在一个总计cr/dr行。将合计行替换为其非合计组件。

with t as (
select ENTITY,JE_HEADER_ID, greatest(0, NET_CR-NET_DR)NET_CR, greatest(0, NET_DR-NET_CR) NET_DR
  , sum(greatest(0, NET_CR-NET_DR)) over(partition by JE_HEADER_ID) tot
from XXTB_JE_TXN 
)
select a.*
from t
cross join lateral (
  select t.ENTITY,t.JE_HEADER_ID,t2.NET_CR,t2.NET_DR
  from t t2
  where t2.JE_HEADER_ID = t.JE_HEADER_ID and t2.tot != t2.NET_CR and t2.tot != t2.NET_DR
) a
where t.tot = t.NET_CR or t.tot = t.NET_DR
  
union all
  
select ENTITY,JE_HEADER_ID,NET_DR,NET_CR
from t
where t.tot != t.NET_CR and t.tot != t.NET_DR
order by 2, 1;

db-fiddle

试试这个：

WITH sumed AS (
  SELECT entity, je_header_id, 
  net_cr, SUM(net_cr) OVER(PARTITION BY je_header_id) AS sum_net_cr, 
  net_dr, SUM(net_dr) over(partition by je_header_id) AS sum_net_dr
  FROM xxtb_je_txn
),
tsumed as (
    SELECT entity, je_header_id, net_cr, sum_net_cr, net_dr, sum_net_dr,
    CASE 
        WHEN net_cr = sum_net_dr THEN 2
        WHEN net_dr = sum_net_cr THEN 3
        WHEN EXISTS(
                SELECT 1 FROM xxtb_je_txn x WHERE x.net_cr = s.sum_net_dr
            ) AND net_dr <> 0 AND net_dr <> sum_net_cr
        THEN 1
        WHEN EXISTS(
                    SELECT 1 FROM xxtb_je_txn x WHERE x.net_dr = s.sum_net_cr
                )
                AND net_cr <> 0 AND net_cr <> sum_net_dr
            THEN 2
        END AS flag
    FROM sumed s
)
SELECT entity, je_header_id, r_net_cr as net_cr, r_net_dr as net_dr --, flag
FROM tsumed s
MODEL
    PARTITION BY ( je_header_id )
    DIMENSION BY ( entity )
    MEASURES( 0 AS r_net_cr, 0 AS r_net_dr, net_dr as net_dr, net_cr as net_cr, flag as flag )
    RULES(
        r_net_cr[any] =
            CASE 
            WHEN flag[cv()] = 1 THEN net_dr[cv()]
            WHEN flag[cv()] = 2 THEN 0
            WHEN flag[cv()] = 3 THEN net_dr[cv()]
        END,
        r_net_dr[any] =
            CASE 
            WHEN flag[cv()] = 1 THEN net_cr[cv()]
            WHEN flag[cv()] = 2 THEN net_cr[cv()]
            WHEN flag[cv()] = 3 THEN 0
        END
    )
ORDER BY je_header_id, entity
;

301 10101   0   100
302 10101   20  0
401 10101   30  0
402 10101   50  0
301 10102   100 0
302 10102   0   20
401 10102   0   30
402 10102   0   50
101 10103   0   992,57
102 10103   400,44  0
201 10103   266,96  0
101 10104   200 0
204 10104   0   200
101 10105   0   17228,5
102 10105   0   62,5
201 10105   17291   0

实际上，缺少“交换的”CR/DR行：

WITH sumed AS (
  SELECT entity, je_header_id, 
  net_cr, SUM(net_cr) OVER(PARTITION BY je_header_id) AS sum_net_cr, 
  net_dr, SUM(net_dr) over(partition by je_header_id) AS sum_net_dr
  FROM xxtb_je_txn
),
tsumed as (
    SELECT entity, je_header_id, net_cr, sum_net_cr, net_dr, sum_net_dr,
    CASE 
        WHEN net_cr = sum_net_dr THEN CASE WHEN net_dr <> 0 THEN 5 ELSE 2 END
        WHEN net_dr = sum_net_cr THEN CASE WHEN net_cr <> 0 THEN 6 ELSE 4 END
        WHEN EXISTS(
                SELECT 1 FROM xxtb_je_txn x WHERE x.net_cr = s.sum_net_dr
            ) AND net_dr <> 0 AND net_dr <> sum_net_cr
        THEN 1
        WHEN EXISTS(
                SELECT 1 FROM xxtb_je_txn x WHERE x.net_dr = s.sum_net_cr
            )
            AND net_cr <> 0 AND net_cr <> sum_net_dr
        THEN 3
        END AS flag
    FROM sumed s
)
SELECT entity, je_header_id, net_cr, net_dr FROM (
    SELECT flag, entity, je_header_id, net_cr, net_dr
    FROM tsumed s
    MODEL
        PARTITION BY ( je_header_id )
        DIMENSION BY ( entity, flag )
        MEASURES( net_dr as net_dr, net_cr as net_cr, 
            sum_net_cr as sum_net_cr, sum_net_dr as sum_net_dr )
        RULES UPSERT ALL(
            net_cr[any,5] = net_cr[cv(),1],
            net_dr[any,5] = net_dr[cv(),1],
            net_cr[any,6] = net_dr[cv(),1],
            net_dr[any,6] = net_cr[cv(),1],
    
            net_cr[any,7] = net_cr[cv(),3],     
            net_dr[any,7] = net_dr[cv(),3],
            net_cr[any,8] = net_dr[cv(),3],     
            net_dr[any,8] = net_cr[cv(),3],
    
            net_cr[any,9] = net_cr[cv(),0],     
            net_dr[any,9] = net_dr[cv(),0],
    
            net_cr[any,10] = case when net_cr[cv(),2] = sum_net_cr[cv(),2] and net_dr[cv(),2] <> 0 then net_dr[cv(),2] end,     
            net_dr[any,10] = case when net_cr[cv(),2] = sum_net_cr[cv(),2] and net_dr[cv(),2] <> 0 then 0 end,
    
            net_cr[any,11] = case when net_dr[cv(),4] = sum_net_dr[cv(),4] and net_cr[cv(),4] <> 0 then net_cr[cv(),4] end,     
            net_dr[any,11] = case when net_dr[cv(),4] = sum_net_dr[cv(),4] and net_cr[cv(),4] <> 0 then 0 end,
            
            net_cr[any,12] = net_dr[cv(),5],        
            net_dr[any,12] = 0,
            
            net_cr[any,13] = 0,     
            net_dr[any,13] = net_dr[cv(),5],
            
            net_cr[any,14] = net_cr[cv(),6],        
            net_dr[any,14] = 0,
            
            net_cr[any,15] = 0,     
            net_dr[any,15] = net_cr[cv(),6]
        )
)
WHERE net_cr is not null AND net_dr is not null AND flag > 4 
ORDER BY je_header_id, entity
;