8AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAtGIR4s1JgEi7Qe8qlaV7EQAAAAAIAAAARABUAFMAAAAQZgAAAAEAACAAAABlBojfCtvhyUwR3RwgIAp/BKUoQLGtXDEY3W2Fw0CzVAAAAAAOgAAAAAIAACAAAADblptdhiy5VLuporOOzfCgAWxD9ELyXr9BN79u4iHnDDAAAAAt6QmW1Ukv4egrMvppN1ZIqk7rRp3huahVL5NCWjyWosLPt9J751NQvPFsKNJatLdAAAAA9C3fkmD0UvhWH4p5gny7YoDFrQtdP7qYVRRaUe4yR52LVvlvwLBGfOw7SD2M1o3gA/Odxz56UOdN924dHYKcoA0with t1 as(
select
a.id,
a.ReceiveTime[出货时间],
isnull( h.Abbreviation,h.CompanyName)[公司名],
a.CustomerOrderNo[客户单号],a.CustomerWeight/1000.0[客户下单重],a.OPWeight/1000.0[仓库收件重],a.TrackingNumber[运单号],a.SystemNo[系统单号],f.Name[线路名称],
b.ShippingFee[客户-运费],b.AttachFee[客户-操作费],g.[BillingWeight][客户-计费重],g.remark[客户-计费说明],
d.ShippingFee[成本-运费],c.ShippingFee[结算-运费],c.AttachFee[结算/成本-操作费],
j.[BillingWeight][结算-计费重],j.remark[结算-计费说明],k.remark[成本-计费说明]
from Order_Order(nolock)a
join User_Customer(nolock)h on h.id=a.CustomerId
left join Finance_Order_Charges(nolock)b on a.SystemNo=b.SystemNo and b.ChargeTarget=1 and b.Offset=0
left join Finance_Order_Charges(nolock)c on a.SystemNo=c.SystemNo and c.ChargeTarget=2 and c.Offset=0
left join Finance_Order_Charges(nolock)d on a.SystemNo=d.SystemNo and d.ChargeTarget is null and d.Offset=0
left join Finance_ChargesDetail(nolock)g on g.BillNo=a.SystemNo
and g.CustomerId = a.CustomerId and g.BillType='全程派送运费' and g.Offset=0
left join Finance_ChargesDetail(nolock)j on j.BillNo=a.SystemNo
and j.CustomerId = h.BranchCompanyId and j.BillType='全程派送运费' and j.Offset=0
left join Finance_ChargesDetail(nolock)k on k.BillNo=a.SystemNo
and k.CustomerId = 0 and k.BillType='FBA成本费用记录' and k.FeeDescription='运费成本记录' and k.Offset=0
join Logistics_Channel(nolock)e on a.CreateOrderChannelId=e.Id
join Logistics_Public(nolock)f on e.PublicCode=f.Code
where f.ChannelType=3
and (OrderStatus=2 or OrderStatus=3)
and ReceiveTime>'2022-08-01'
and a.CustomerOrderNo not like '%test%'
),
t2 as(
select OrderId,
name = ( stuff((select '|' + Name +' '+ convert(nvarchar, Fee) + case when CalculateType =0 then '/KG' else '/票' end
from FBA_Incidental ii where ii.OrderId = i.OrderId and Target=0 for xml path('')), 1, 1, '') )
from FBA_Incidental(nolock)i
where Target = 0
group by OrderId
),
t3 as
(
select OrderId,
name = ( stuff((select '|' + Name +' '+ convert(nvarchar, Fee) + case when CalculateType =0 then '/KG' else '/票' end
from FBA_Incidental ii where ii.OrderId = i.OrderId and Target=1 for xml path('')), 1, 1, '') )
from FBA_Incidental(nolock)i
where Target = 1
group by OrderId
),
t4 as
(
select a.id,count(0)[箱数] from Order_Order(nolock) a join Order_MpsOrder(nolock)b on a.id=b.OrderId
join Order_MpsOrderBox(nolock)c on c.MpsOrderId=b.Id
group by a.Id
)
select t1.*,t2.name[客户-附加费详情],t3.name[结算/成本-附加费详情],t4.箱数 from t1 left join t2 on t1.Id=t2.OrderId
left join t3 on t3.OrderId=t1.Id
left join t4 on t4.Id = t1.Id936false20[BaseETL_FBA]936false3falsefalseTABLOCK,CHECK_CONSTRAINTS2147483647DataSourceViewIDDataSourceViewIDTableInfoObjectTypeTable]]>