Skip to Content
0
Jul 26, 2016 at 11:25 PM

Inventory Inquiry Query

57 Views

Hello SAP Gurus,

Can someone help me add a column to the query below?

I need to add U_TigerDirect but keep getting errors when trying to add it.

This is a User Defined Field in Item Master Data - General.

Delete from V33_SYNNEX_846

/*

CREATE TABLE V33_SYNNEX_846(

EDI nvarchar(10),

CardCode nvarchar(20),

Blank1 nvarchar(10),

DocDate DateTime,

DocDueDate DateTime,

Blank2 nvarchar(10),

TD_ItemCode nvarchar(20),

ENET_ItemCode nvarchar(20),

Blank3 nvarchar(10),

Quantity numeric (19,6),

)

*/

Insert into V33_SYNNEX_846

SELECT

'846', 'C000310', '', GetDate(), GetDate(), '',

'',

ItemCode,

''

,CONVERT(int, ROUND(isnull(Qty,0),3)) Qty

FROM

(SELECT DISTINCT

SUB.ItemCode

,w.WhsCode

,w.WhsName

,SUB.Qty

FROM

(SELECT T0.ItemCode

,T1.WhsCode

,SUM (T1.OnHand-T1.IsCommited + T1.OnOrder) Qty

FROM OSCN T0

JOIN OITW T1 on T0.Substitute=T1.ItemCode

WHERE T1.WhsCode='01' AND T0.CardCode='ENET-TD'

GROUP BY T0.ItemCode, T1.WhsCode

) SUB

JOIN OWHS w on w.WhsCode=SUB.WhsCode

) FIN

ORDER BY WhsCode

--Update V33_TD_846 Set TD_ItemCode = U_TigerDirect from V33_TD_846

--Join OITM on V33_TD_846.ENET_ItemCode = ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS

Insert into V33_SYNNEX_846

Select '846', 'C000310', '', GetDate(), GetDate(), '',

OSCN.Substitute , OSCN.ItemCode, '', (T1.OnHand-T1.IsCommited + T1.OnOrder) Qty from OSCN

-- Join OSCN o2 on OSCN.Substitute = o2.ItemCode

JOIN OITW T1 on OSCN.ItemCode =T1.ItemCode

where OSCN.CardCode = 'C000310' and WhsCode = '01' and OSCN.ItemCode not in (Select ENET_ItemCode from V33_SYNNEX_846)

Update V33_SYNNEX_846 Set TD_ItemCode = Substitute from V33_SYNNEX_846

Join OSCN on V33_SYNNEX_846.ENET_ItemCode = OSCN.ItemCode COLLATE SQL_Latin1_General_CP850_CI_AS

Where OSCN.CardCode = 'C000310'

Update V33_SYNNEX_846 Set Quantity = FLOOR(ISNULL(T1.Quantity/4,0)) from V33_SYNNEX_846 T1

WHERE T1.ENET_ItemCode IN ('M-ASR1K-1001-8GB-ENA', 'MEM-ASR1002X-8GB-ENA',

'S26361F3031L410E','S26361F3070L600E','S26361F3070L800E')

Update V33_SYNNEX_846 Set Quantity = FLOOR(ISNULL(T1.Quantity/2,0)) from V33_SYNNEX_846 T1

WHERE T1.ENET_ItemCode IN ('M-ASR1K-1001-8GB-ENA', 'MA940G/A-ENC','MEM-7835-I2-2GB-ENC',

'S26361F2762L526E','S26361F2762L625E','S26361F2762L626E',

'SSG-500-MEM-1GB-ENA','MEM-SUP2T-4GB-ENA','MEM-4400-4GU16G-ENC',

'LC2-OM4-10M-ENT','LC2-OM4-3M-ENT','SCLC-OM4-3M-ENT')

Update V33_SYNNEX_846 Set Quantity = FLOOR(ISNULL(T1.Quantity/128,0)) from V33_SYNNEX_846 T1

WHERE T1.ENET_ItemCode IN ('XBR-000158-ENC')

Update V33_SYNNEX_846 Set Quantity = FLOOR(ISNULL(T1.Quantity/10,0)) from V33_SYNNEX_846 T1

WHERE T1.ENET_ItemCode IN ('GLC-SX-MM-10PK-ENC','GLC-T-10PK-ENC','J4859C-10PK-ENC','J4858C-10PK-ENC','GLC-T-10PK-COR','GLC-SX-MM-10PK-COR','J4859C-10PK-COR','J4858C-10PK-COR')

Select V33_SYNNEX_846.* from V33_SYNNEX_846

Join OITM T2 on V33_SYNNEX_846.ENET_ItemCode = T2.ItemCode

WHERE SellItem = 'Y' and TD_ItemCode > ''

Thank you very much!!

Kind regards,

Larry T.