SELECT
case when inv_pos_sum.cWhCode in ('01') then replace(inv_pos_sum.cInvCode, 'M-', '') else inv_pos_sum.cInvCode end cInvCode,
inv_pos_sum.cBatch,
inv_pos_sum.cWhCode,
inv_pos_sum.cPosCode,
inv_pos_sum.cFree1,
inv_pos_sum.iQuantity,
-- rd01_summary.cInvCode,
-- rd01_summary.cFree1,
rd01_summary.cSCloser,
so_d.iSOsID,
so_d.cSCloser
FROM InvPositionSum inv_pos_sum

LEFT JOIN Inventory inv
on inv_pos_sum.cInvCode = inv.cInvCode

LEFT JOIN (
SELECT
row_number() over (partition by rd01_d.cInvCode, rd01_d.cBatch ORDER BY so_d.iSOsID asc) r,
rd01_d.cInvCode,
rd01_d.cBatch,
so_d.cFree1,
so_d.cSCloser,
so_d.iSOsID

FROM rdrecords01 rd01_d

LEFT JOIN SO_SODetails so_d
ON rd01_d.iorderdid = so_d.iSOsID

LEFT JOIN SO_SOMain so_m
ON so_d.ID = so_m.ID

-- GROUP BY rd01_d.cInvCode, rd01_d.cBatch
) rd01_summary
on inv_pos_sum.cInvCode = rd01_summary.cInvCode
AND inv_pos_sum.cBatch = rd01_summary.cBatch
and rd01_summary.r=1   -- 之前漏了这行


LEFT JOIN rdrecords10 rd10_d
ON inv_pos_sum.cInvCode = rd10_d.cInvCode
AND inv_pos_sum.cBatch = rd10_d.cBatch
AND inv_pos_sum.cFree1 = rd10_d.cFree1

LEFT JOIN SO_SODetails so_d
ON rd10_d.iorderdid = so_d.iSOsID


WHERE inv_pos_sum.iQuantity > 0
and inv_pos_sum.cFree1 != ''
-- AND inv.cInvCCode not in (
-- '01031001',
-- '03031001',
-- '03021005',
-- '03011006',
-- '01011006',
-- '01021005'
-- )
AND inv_pos_sum.cWhCode in (
'01',
'03',
'06',
'12'
)
-- 剔除有关联销售订单，且规格一致，且行关闭人为空
AND not (
    rd01_summary.iSOsID is not null
    and rd01_summary.cSCloser is null
    and inv_pos_sum.cFree1 = rd01_summary.cFree1
)
-- 根据产成品入库单，提出有关联销售订单，且行关闭人为空的
and not (
so_d.iSOsID is not null
and so_d.cSCloser is null
)

ORDER BY inv_pos_sum.iQuantity desc