32 lines
919 B
MySQL
32 lines
919 B
MySQL
|
select
|
||
|
|
SOH.STOFCY_0 [Site],
|
||
|
|
SOH.SOHNUM_0 [SO#],
|
||
|
|
SOH.CUSORDREF_0 [Shopify#],
|
||
|
|
coalesce(SDH.SDHNUM_0,'') [Shipment#],
|
||
|
|
case SOH.ORDSTA_0
|
||
|
|
when 1 then 'Open'
|
||
|
|
when 2 then 'Closed'
|
||
|
|
end [Order status],
|
||
|
|
SOQ.ITMREF_0 [X3 Order Item#],
|
||
|
|
SOQ.QTYSTU_0 [X3 Order qty],
|
||
|
|
[SL_ECOMM].lot [SL lot],
|
||
|
|
SLF.AAACUMQTY_0 [X3 available stock for SL lot],
|
||
|
|
[SL_ECOMM].[tracking]
|
||
|
|
from PROD.SORDER SOH
|
||
|
|
left join PROD.SDELIVERY SDH
|
||
|
|
on SDH.SOHNUM_0 = SOH.SOHNUM_0
|
||
|
|
left join PROD.SORDERQ SOQ
|
||
|
|
on SOQ.SOHNUM_0 = SOH.SOHNUM_0
|
||
|
|
left join [analytics].[dbo].[SL_ECOMM]
|
||
|
|
on SOQ.ITMREF_0 = [SL_ECOMM].item
|
||
|
|
and SOQ.STOFCY_0 = [SL_ECOMM].site
|
||
|
|
and SOH.CUSORDREF_0 = [SL_ECOMM].order_id
|
||
|
|
left join PROD.STOLOTFCY SLF
|
||
|
|
on [SL_ECOMM].site = SLF.STOFCY_0
|
||
|
|
and [SL_ECOMM].item = SLF.ITMREF_0
|
||
|
|
and [SL_ECOMM].lot = SLF.LOT_0
|
||
|
|
where SOH.SOHTYP_0 = 'WEB'
|
||
|
|
and SOH.STOFCY_0 in ('SSB','SMB')
|
||
|
|
and SOH.ORDSTA_0 <> 2
|
||
|
|
--and SLF.AAACUMQTY_0 is null
|
||
|
|
order by 2, 3, 6
|