深圳智汇创想科技有限公司

已解决：

DECLARE @StartTime DATETIME = '2026-09-01',
@EndTime DATETIME = '2026-10-01',
@CustomerID int = 1;

SELECT
    CONVERT(VARCHAR(7),idl.OperateTime,120) AS '出库月份',
idl.OriginBillNumber '进销存手工单号',
wh.WareHouseName '仓库',
adb.SKU '系统SKU',
    (CASE WHEN iib.InOutType = 1 THEN '出库' ELSE '入库' END) '一级单据类型',
(CASE WHEN iib.OperateType = 30 THEN '盘亏出库' WHEN iib.OperateType = 68 THEN '物流索赔出库' ELSE '' END) '二级单据类型',
idl.OperateNum '出库数量',
(idl.MUnitPrice * idl.OperateNum) '管理账金额',
(ISNULL(idl.UnitPrice,0) * idl.OperateNum) '税务账金额',
mct.EstimatedAmount '索赔货值',
ep.OrderCode '备库单号',
'D'+CAST(ad.RelationId AS VARCHAR(20)) AS '包裹号',
ad.AllocationNumber AS '调拨单号',
os.OrderSourceName AS '来源渠道',
ad.AuditTime AS '调拨时间',
     adb.FinishTime AS '完结时间',
oi.ServiceAdminName AS '建单人员',
ra.UserName AS '首选业务员',
dept.DepartmentName AS '销售部门',
team.BusinessTeamName AS '销售小组',
((CASE WHEN ISNULL(adb.ReceiveFailedType,0) > 0 THEN (CASE 
WHEN adb.ReceiveFailedType = 1 THEN '查验未放行' 
WHEN adb.ReceiveFailedType = 2 THEN '查验销毁' 
WHEN adb.ReceiveFailedType = 3 THEN '物流商丢件损坏' 
WHEN adb.ReceiveFailedType = 4 THEN '运输途中' 
WHEN adb.ReceiveFailedType = 5 THEN '派送失败' 
ELSE '' END + ',') ELSE '' END) +
(CASE WHEN ISNULL(adb.ListingFailedType,0) > 0 THEN (CASE 
WHEN adb.ListingFailedType = 1 THEN '已上架_上架到其他货件'
WHEN adb.ListingFailedType = 2 THEN '已上架_其他原因'
WHEN adb.ListingFailedType = 3 THEN '未上架_丢包'
WHEN adb.ListingFailedType = 4 THEN '未上架_其他原因'
WHEN adb.ListingFailedType = 5 THEN '未上架_贴标异常_超尺寸'
WHEN adb.ListingFailedType = 6 THEN '货件接收中'
WHEN adb.ListingFailedType = 7 THEN '已索赔'
ELSE '' END + ',') ELSE '' END) +
(CASE WHEN ISNULL(adb.UnShelfRemark,'')<>'' THEN adb.UnShelfRemark ELSE '' END)) AS '核查原因',
(CASE WHEN cd.ClaimStatus = 0 THEN '待索赔' 
WHEN cd.ClaimStatus = 1 THEN '索赔中' 
WHEN cd.ClaimStatus = 2 THEN '索赔成功' 
WHEN cd.ClaimStatus = 3 THEN '索赔失败' 
WHEN cd.ClaimStatus = 4 THEN '线下截单换仓_重建货件' 
WHEN cd.ClaimStatus = 5 THEN '已签收返回' 
WHEN cd.ClaimStatus = 6 THEN '跟进中' 
WHEN cd.ClaimStatus = 7 THEN '已签收返回_不展示'
ELSE '' END) AS '索赔状态',
cd.ClaimReason '索赔原因',
cd.ClaimDeadline '索赔成功或失败的时间'
FROM RB_Inventory_LogisticsCarrierClaimDetail cd WITH (NOLOCK)
INNER JOIN RB_Inventory_InTransitMonitor itm WITH (NOLOCK) ON cd.MonitorId = itm.Id AND cd.CustomerId = itm.CustomerId
INNER JOIN RB_Inventory_AllocationBill ad WITH (NOLOCK) ON cd.AllocationBillId = ad.ID AND cd.CustomerId = ad.CustomerID
INNER JOIN RB_Inventory_AllocationDetailBill adb WITH (NOLOCK) ON cd.AllocationDetailId = adb.ID AND cd.CustomerId = adb.CustomerID
INNER JOIN RB_OrderInfo_BeikuExpand ep WITH (NOLOCK) ON ep.CustomerID = ad.CustomerID AND ep.OrderCode = ad.OrginNumber
LEFT JOIN RB_Inv_MonitoringClaimsTracking mct WITH (NOLOCK) ON mct.CustomerID = cd.CustomerId AND ISNULL(mct.SKU, '') <> ''
    AND (CASE WHEN mct.GroupSKU IS NULL OR mct.GroupSKU = '' THEN mct.SKU ELSE mct.GroupSKU END) = adb.SKU
    AND ((itm.InTransitType IN (1, 2) AND mct.PackageNumber = ad.RelationId)
        OR (itm.InTransitType IN (3, 4) AND ep.TransitOutboundOrderCode IS NOT NULL AND mct.TransitOutboundOrderCode = ep.TransitOutboundOrderCode))
INNER JOIN RB_Inventory_Monitor_Mapping imm WITH (NOLOCK) ON imm.MonitorId = itm.Id AND imm.CustomerId = itm.CustomerId AND imm.AllocationDetailId = adb.ID
INNER JOIN RB_Inventory_DetailLog idl WITH (NOLOCK) ON idl.CustomerID = cd.CustomerId AND idl.OriginBillNumber = imm.BallanceNumber 
LEFT JOIN RB_Inventory_InOutBallance iib WITH (NOLOCK) ON iib.CustomerID = cd.CustomerId AND iib.BallanceNumber = imm.BallanceNumber 
LEFT JOIN dbo.RB_WareHouse wh WITH(NOLOCK) ON cd.CustomerID = wh.CustomerID AND idl.WareHouseID = wh.ID
LEFT JOIN dbo.RB_OrderInfo oi WITH(NOLOCK) ON oi.OrderCode = ad.OrginNumber AND ad.CustomerID = oi.CustomerID
LEFT JOIN dbo.RB_OrderSource os WITH(NOLOCK) ON os.CustomerID = cd.CustomerId AND os.ID = oi.OrderSourceID
LEFT JOIN dbo.RB_Admin ra WITH(NOLOCK) ON ra.CustomerID = cd.CustomerId AND ra.ID = os.SaleAdminID 
LEFT JOIN dbo.RB_Department dept WITH(NOLOCK) ON dept.CustomerID = cd.CustomerId AND dept.ID = ra.DepartmentID    
LEFT JOIN dbo.RB_BusinessTeam team WITH(NOLOCK) ON team.CustomerID = cd.CustomerId AND team.ID = ra.BusinessTeamID    
WHERE cd.CustomerId = @CustomerID 
AND idl.OperateTime >=@StartTime AND idl.OperateTime <= @EndTime