SAP B1 - Query Generator Introduction Vid 1

[@brianminoff6760]

Hi Gerry. Are you available to hire for training me & my staff in SAP B1?

[@deepakpoddar3097]

I'm from India

[@K11M0RE1NN]

/* **************************************** Purchase Orders for this Year, with related GRNs and Purchase Invoice Created By G.Reynolds - 25 June 2018 ************************************** */ SELECT T0.[CardCode], T0.[DocNum] as [PO #] , case when T0.[CANCELED] = 'Y' then 'Cancelled' else NULL end as 'PO Cancelled?' , T1.[ItemCode], T1.[Quantity], T1.[OpenQty] as [O/s Qty] , CASE WHEN T1.[Quantity] 0 THEN (T1.[LineTotal] / T1.[Quantity] ) * ( T1.[OpenQty] ) ELSE 0 END AS [Open PO Value] , T3.[DocNum] as [GRN #] , T2.[Quantity] as [Qty Recd] , T2.[OpenCreQty] as [UnInvoiced Qty] , CASE WHEN T2.[Quantity] 0 THEN (T2.[LineTotal] / T2.[Quantity] ) * ( T2.[OpenCreQty] ) ELSE 0 END AS [UnInvoiced Value] , T5.[DocNum] as [Inv #] , T4.[Quantity] [Qty Invd] , T4.[LineTotal] as [Total Invoiced] FROM [OPOR] T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT JOIN PDN1 T2 ON T2.BaseEntry = T1.DocEntry and T2.BaseLine = T1.LineNum and T2.[BaseType] = 22 LEFT JOIN OPDN T3 ON T2.[DocEntry] = T3.[DocEntry] LEFT JOIN PCH1 T4 ON T4.BaseEntry = T2.DocEntry and T4.BaseLine = T2.LineNum and T4.[BaseType] = 20 LEFT JOIN OPCH T5 ON T4.[DocEntry] = T5.[DocEntry] WHERE year( T0.[DocDate] ) = year(getdate()) --AND T0.[CANCELED] = 'N' ORDER BY T0.[DocNum], T1.[LineNum]

[@deepakpoddar3097]

I want to ask u, how can I generate an item group report there particular whs stock in sap b1.

[@K11M0RE1NN]

Hi Deepak, Please try below. Hope it helps. Best regards, Gerry SELECT T0.[ItemCode], T0.[ItemName], T0.[ItmsGrpCod], T1.[ItmsGrpNam], T2.[WhsCode], T3.[WhsName], T2.[OnHand], T2.[IsCommited], T2.[OnOrder] FROM OITM T0 INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod INNER JOIN OITW T2 ON T0.[ItemCode] = T2.[ItemCode] INNER JOIN OWHS T3 ON T2.WhsCode = T3.WhsCode WHERE /******* the replace functions here are not all necessary, but I copied from a much bigger query ******/ replace ( replace ( ISNULL( T2.[WhsCode] , '') , '-' , '') , ' ' , '') like replace (replace( '%[%1]%' , '-' , ''), ' ' , '') AND replace ( replace ( ISNULL( T0.[ItemCode] , '') , '-' , '') , ' ' , '') like replace (replace( '%[%2]%' , '-' , ''), ' ' , '') AND replace ( replace ( ISNULL( T1.[ItmsGrpNam] , '') , '-' , '') , ' ' , '') like replace (replace( '%[%3]%' , '-' , ''), ' ' , '')

[@deepakpoddar3097]

Thank u sir.

[@deepakpoddar3097]

@@K11M0RE1NN I didn't think you would answer my question

[@deepakpoddar3097]

@@K11M0RE1NN goodevning sir. I need ur help 9831244258 chat with me on whatsapp

[@deepakpoddar3097]

@@K11M0RE1NN report generation of active item group wise with mrp (sale price) sap b1

[@deepakpoddar3097]

Hello sir.

[@K11M0RE1NN]

SELECT T0.[CardCode], Row_Number() OVER (ORDER BY T0.[CreateDate]) as [Row Number] , --T0.[CardName], CONVERT(VARCHAR(15), T0.[CreateDate] , 103) as [Date Created] , Max(T1.[DocDate]) as [Last Inv Date], T10.[Open Pos] FROM OCRD T0 LEFT JOIN OPCH T1 ON T0.[CardCode] = T1.[CardCode] LEFT JOIN (SELECT T0.[CardCode], count( T0.[DocNum]) as [Open Pos] FROM OPOR T0 WHERE T0.[DocStatus] = 'O' and T0.[CANCELED] = 'N' GROUP BY T0.[CardCode]) T10 on T0.CardCode = T10.CardCode WHERE T0.[CardType] = 'S' GROUP BY T0.[CardCode], T0.[CardName] ,T0.[CreateDate], T10.[Open Pos] ORDER BY Max(T1.[DocDate]) , T0.[CreateDate]