Report Queries for SAP Business One (Part 2: Inventory)

[saad_omair]

Inventory Status

SELECT     OITM.ItemCode, OITM.ItemName, OITM.InvntryUom, OITM.U_Category, [@CATEGORY].Name, OITB.ItmsGrpNam,

1. OITM.OnHand

FROM         OITM INNER JOIN

[@CATEGORY] ON OITM.U_Category = [@CATEGORY].Code INNER JOIN

OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod

Group by [@CATEGORY].Name, OITB.ItmsGrpNam,OITM.ItemCode, OITM.ItemName, OITM.InvntryUom, OITM.U_Category,OITM.OnHand

Good Received PO Detail

SELECT     OPDN.DocEntry, format(OPDN.DocDate,'dd/MMM/yyyy') as PostingDate,format(OPDN.TaxDate,'dd/MMM/yyyy') as DocumentDate, OPDN.CardCode as VendorCode, OPDN.CardName as VendorName, OPDN.DocTotal, PDN1.ItemCode, PDN1.Dscription, PDN1.Quantity, PDN1.Price, PDN1.Rate,

                      OPDN.Comments, OPDN.DocType, PDN1.unitMsr as UoM

FROM         ORPD INNER JOIN

                      RPD1 ON ORPD.DocEntry = RPD1.DocEntry RIGHT OUTER JOIN

                      OPDN INNER JOIN

                      PDN1 ON OPDN.DocEntry = PDN1.DocEntry AND OPDN.CANCELED <> 'C' INNER JOIN

                      OITM ON PDN1.ItemCode = OITM.ItemCode INNER JOIN

                      OUSR ON OPDN.UserSign = OUSR.USERID ON RPD1.BaseLine = PDN1.LineNum AND RPD1.BaseEntry = PDN1.DocEntry AND RPD1.BaseDocNum = PDN1.DocEntry

where  OPDN.CANCELED <> 'Y'

and (PDN1.Quantity <> isnull(RPD1.Quantity,0))

Open Item Detail (Item-wise List)

SELECT     OPRQ.DocEntry as DocNum, OPRQ.DocStatus, OPRQ.ReqName as RequesterName, OPRQ.ReqDate, PRQ1.ItemCode, OPRQ.TaxDate as DocumentDate, PRQ1.unitMsr as UoM, OITM.ItemName, OITM.OnHand as OnHandQuantity, OPRQ.DocDate as PostingDate,

PRQ1.Quantity as PRQuantity, PRQ1.OpenCreQty AS RemainingPOQty, SUM(PDN1.Quantity) AS ReceivedQty, SUM(POR1.Quantity) AS OrderedQty, SUM(RPD1.Quantity)

AS ReturnQty, PRQ1.Quantity - SUM(PDN1.Quantity) + SUM(RPD1.Quantity) AS Balance, OUDP.Name DepartmentName, OITB.ItmsGrpNam as GroupName

FROM         OUDP INNER JOIN

OPRQ INNER JOIN

PRQ1 ON OPRQ.DocEntry = PRQ1.DocEntry INNER JOIN

OITM ON PRQ1.ItemCode = OITM.ItemCode ON OUDP.Code = OPRQ.Department INNER JOIN

OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod LEFT OUTER JOIN

POR1 INNER JOIN

OPOR ON POR1.DocEntry = OPOR.DocEntry LEFT OUTER JOIN

OPDN INNER JOIN

PDN1 ON OPDN.DocEntry = PDN1.DocEntry LEFT OUTER JOIN

RPD1 ON PDN1.DocEntry = RPD1.BaseDocNum AND PDN1.ItemCode = RPD1.ItemCode ON POR1.DocEntry = PDN1.BaseDocNum AND

POR1.ItemCode = PDN1.ItemCode RIGHT OUTER JOIN

PQT1 ON POR1.BaseDocNum = PQT1.DocEntry AND POR1.ItemCode = PQT1.ItemCode ON PRQ1.DocEntry = PQT1.BaseDocNum AND

PRQ1.ItemCode = PQT1.ItemCode

WHERE      isnull(OPRQ.Canceled ,'')<> 'Y' and isnull(OPOR.Canceled ,'') <> 'Y' and isnull(OPDN.Canceled ,'')<> 'Y'

GROUP BY  PRQ1.ItemCode,OITM.ItemName,OPRQ.DocEntry, OPRQ.DocStatus, OPRQ.ReqName, OPRQ.ReqDate, OPRQ.TaxDate, PRQ1.unitMsr,  OITM.OnHand,

OPRQ.DocDate, PRQ1.Quantity, PRQ1.OpenCreQty,OUDP.Name, OITB.ItmsGrpNam

Open Item (Balances) Request wise.

Select PRNo,UnitMsr,PRDate,ItemCode,ItemName,RequesterName,PostingDate, sum(PRQty) as PRQty,sum(POQty) as POQty,sum(GRPOQty) as GRPOQty,sum(GRQty) as GRQty,sum(PRQty) - SUM(GRPOQty) + SUM(GRQty) as Balance from

(

SELECT        dbo.PRQ1.DocEntry as PRNo,dbo.PRQ1.UnitMsr,OPRQ.TaxDate as PRDate, dbo.PRQ1.ItemCode as ItemCode, dbo.PRQ1.Dscription as ItemName,OPRQ.ReqName as RequesterName,OPRQ.DocDate as PostingDate, dbo.PRQ1.Quantity as PRQty,0 as POQty,0 as GRPOQty,0 as GRQty

FROM            dbo.OPRQ INNER JOIN

1. dbo.PRQ1 ON dbo.OPRQ.DocEntry = dbo.PRQ1.DocEntry

WHERE        isnull(OPRQ.Canceled ,'')<> 'Y'

UNION ALL

SELECT        dbo.PRQ1.DocEntry as PRNo,dbo.PRQ1.UnitMsr,OPRQ.TaxDate as PRDate, dbo.POR1.ItemCode as ItemCode, dbo.POR1.Dscription as ItemName,OPRQ.ReqName as RequesterName,OPRQ.DocDate as PostingDate,0 as PRQty, dbo.POR1.Quantity as POQty,0 as GRPOQty,0 as GRQty

FROM         POR1 INNER JOIN

OPOR ON POR1.DocEntry = OPOR.DocEntry RIGHT OUTER JOIN

PQT1 ON POR1.ItemCode = PQT1.ItemCode AND POR1.BaseEntry = PQT1.DocEntry AND POR1.BaseLine = PQT1.LineNum RIGHT OUTER JOIN

OPRQ INNER JOIN

PRQ1 ON OPRQ.DocEntry = PRQ1.DocEntry ON PQT1.ItemCode = PRQ1.ItemCode AND PQT1.BaseEntry = PRQ1.DocEntry AND

PQT1.BaseLine = PRQ1.LineNum

WHERE    isnull(OPOR.Canceled ,'')<> 'Y'

UNION ALL

SELECT        dbo.PRQ1.DocEntry as PRNo,dbo.PRQ1.UnitMsr,OPRQ.TaxDate as PRDate, dbo.PDN1.ItemCode as ItemCode, dbo.PDN1.Dscription as ItemName,OPRQ.ReqName as RequesterName,OPRQ.DocDate as PostingDate,0 as PRQty,0 as POQty, dbo.PDN1.Quantity as GRPOQty,0 as GRQty

FROM            dbo.OPDN INNER JOIN

dbo.PDN1 ON dbo.OPDN.DocEntry = dbo.PDN1.DocEntry RIGHT OUTER JOIN

dbo.POR1 ON dbo.PDN1.ItemCode = dbo.POR1.ItemCode AND dbo.PDN1.BaseLine = dbo.POR1.LineNum AND

dbo.PDN1.BaseEntry = dbo.POR1.DocEntry RIGHT OUTER JOIN

dbo.PQT1 ON dbo.POR1.ItemCode = dbo.PQT1.ItemCode AND dbo.POR1.BaseEntry = dbo.PQT1.DocEntry AND

dbo.POR1.BaseLine = dbo.PQT1.LineNum RIGHT OUTER JOIN

dbo.OPRQ INNER JOIN

dbo.PRQ1 ON dbo.OPRQ.DocEntry = dbo.PRQ1.DocEntry ON dbo.PQT1.ItemCode = dbo.PRQ1.ItemCode AND dbo.PQT1.BaseEntry = dbo.PRQ1.DocEntry AND

dbo.PQT1.BaseLine = dbo.PRQ1.LineNum

WHERE  isnull(OPDN.Canceled ,'')<> 'Y'  

UNION ALL

SELECT       dbo.PRQ1.DocEntry as PRNo,dbo.PRQ1.UnitMsr,OPRQ.TaxDate as PRDate, dbo.RPD1.ItemCode as ItemCode, dbo.RPD1.Dscription as ItemName,OPRQ.ReqName as RequesterName,OPRQ.DocDate as PostingDate,0 as PRQty,0 as POQty,0 as GRPOQty, dbo.RPD1.Quantity as GRQty

FROM            dbo.OPRQ INNER JOIN

dbo.PRQ1 ON dbo.OPRQ.DocEntry = dbo.PRQ1.DocEntry LEFT OUTER JOIN

dbo.OPDN INNER JOIN

dbo.PDN1 ON dbo.OPDN.DocEntry = dbo.PDN1.DocEntry LEFT OUTER JOIN

dbo.ORPD INNER JOIN

dbo.RPD1 ON dbo.ORPD.DocEntry = dbo.RPD1.DocEntry ON dbo.PDN1.LineNum = dbo.RPD1.BaseLine AND dbo.PDN1.DocEntry = dbo.RPD1.BaseEntry AND

dbo.PDN1.ItemCode = dbo.RPD1.ItemCode RIGHT OUTER JOIN

dbo.POR1 ON dbo.PDN1.ItemCode = dbo.POR1.ItemCode AND dbo.PDN1.BaseLine = dbo.POR1.LineNum AND

dbo.PDN1.BaseEntry = dbo.POR1.DocEntry RIGHT OUTER JOIN

dbo.PQT1 ON dbo.POR1.ItemCode = dbo.PQT1.ItemCode AND dbo.POR1.BaseEntry = dbo.PQT1.DocEntry AND dbo.POR1.BaseLine = dbo.PQT1.LineNum ON

dbo.PRQ1.ItemCode = dbo.PQT1.ItemCode AND dbo.PRQ1.DocEntry = dbo.PQT1.BaseEntry AND dbo.PRQ1.LineNum = dbo.PQT1.BaseLine

WHERE      

ORPD.CANCELED <> 'Y'

) Fintab

group by fintab.PRNo,UnitMsr,PRDate,ItemCode,ItemName,RequesterName,PostingDate

HAving sum(PRQty) - SUM(GRPOQty) + SUM(GRQty) > 0

Open Item Detail (With Last Purchase Price and Vendor)

Make Changes in Date Parameter According to your need.

SELECT     OPRQ.DocEntry, OPRQ.DocStatus, OPRQ.ReqName, OPRQ.ReqDate, PRQ1.ItemCode,OPRQ.TaxDate,PRQ1.UnitMsr, OITM.ItemName, OITM.OnHand, OPRQ.DocDate, PRQ1.Quantity,OpenCreQty,isnull(PRQ1.U_Remarks,'') as Remarks,OITM.LastPurPrc

,isnull((select top 1 CardName from OPOR Inner Join POR1 On OPOR.DocEntry = POR1.DocEntry where POR1.ItemCode = PRQ1.ItemCode order by OPOR.DocEntry desc),'Order is not Placed') as Supplier

FROM         OPRQ INNER JOIN

                      PRQ1 ON OPRQ.DocEntry = PRQ1.DocEntry INNER JOIN

                      OITM ON PRQ1.ItemCode = OITM.ItemCode

Where OPRQ.DocStatus = 'O' and OPRQ.DocDate between @FromDate and @ToDate and OpenCreQty <> 0

order by DocDate

Re-Order Level List

select ItemCode,ItemName,InvntryUom,MinLevel as Minimum_Level,OnHand as Current_Stock_Qty from OITM

where OnHand <= MinLevel and MinLevel > 0

Goods Received Not Yet Invoiced (Current Date)

SELECT OPDN.DocEntry, OPDN.TaxDate, OPDN.DocType,OPDN.NumAtCard,CardName,PDN1.UnitMsr, PDN1.Dscription, PDN1.Quantity, PDN1.Price, PDN1.GTotal,OPDN.Comments,OPDN.docDate

FROM         OPDN INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry and OPDN.CANCELED <> 'C'

where  (CANCELED <> 'Y') and PDN1.DocEntry not in (Select BaseRef from PCH1 where BaseRef is not null) and PDN1.DocEntry not in (Select BaseRef from RPD1 where BaseRef is not null) and PDN1.DocDate <=

getdate()

Items in Quarantine Warehouse

SELECT     OITW.ItemCode, OITW.WhsCode, OITW.OnHand as Quantity, OWHS.WhsName, OITM.ItemName

FROM         OITW INNER JOIN

                      OWHS ON OITW.WhsCode = OWHS.WhsCode INNER JOIN

                      OITM ON OITW.ItemCode = OITM.ItemCode

WHERE (OITW.WhsCode = 'QT-WH') and OITW.OnHand > 0

Item Ledger

Change the Date Parameter According to your need.

select OINM.ItemCode,OINM.Dscription,DateAdd(dd, -1, {?FromDate}) as TaxDate,'Opening Balance' as Comments,OITM.InvntryUom,'OB' as JrnlMemo,'' as Ref1, sum(InQty) as InQty, sum(OutQty) as OutQty,0 as Price,'' as Warehouse,

'' as U_Remarks,'' as CardName,'' as U_MINNo from OINM

INNER JOIN OITM ON OINM.ItemCode = OITM.ItemCode where OINM.TaxDate < {?FromDate}

group by OINM.ItemCode,OINM.Dscription,OITM.InvntryUom

UNION ALL

(

SELECT     OINM.ItemCode, OINM.Dscription, OINM.TaxDate, OINM.Comments , OITM.InvntryUom , OINM.JrnlMemo, OINM.Ref1, OINM.InQty, OINM.OutQty, OINM.Price,

                      OINM.Warehouse, IGE1.U_Remarks,CASE OINM.TransType when 20 then OINM.CardName

                                                           when 60 then U_Remarks End CardName,IGE1.U_MINNo

FROM         OINM INNER JOIN

                      OITM ON OINM.ItemCode = OITM.ItemCode LEFT OUTER JOIN

                      IGE1 INNER JOIN

                      OIGE ON IGE1.DocEntry = OIGE.DocEntry ON OINM.DocLineNum = IGE1.LineNum AND OINM.BASE_REF = OIGE.DocNum

WHERE     (OINM.TransType <> '18' and TransType <> '67')

and

OINM.TaxDate between {?FromDate} and {?ToDate}

)

order by taxdate,InQty desc

Inventory Aging Report

select b.code, b.name, b.Wh, b.Bal, b.Val,

isnull(case when b.days <=30 then b.bal end,0)'0-30 Days' ,

isnull(case when b.days between 31 and 60 then b.bal end,0) '31-60 Days',

isnull(case when b.days between 61 and 90 then b.bal end,0) '61-90 Days',

isnull(case when b.days between 91 and 120 then b.bal end,0) '91-120 Days',

isnull(case when b.days >=121 then b.bal end,0) 'Above 121 Days',b.LastPurchaseDate,b.LastIssueDate,b.InvntryUom

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days',a.LastPurchaseDate,a.LastIssueDate,a.InvntryUom

from (

select max(t0.itemcode)'Code',max(t1.ItemName)'Name',

max(t0.Warehouse)'Wh',

sum(t0.inqty-t0.outqty)'Bal',sum(t0.transvalue)'Val',max(t0.taxdate)'dt',(select top 1  TaxDate from PDN1 INNER JOIN OPDN ON PDN1.DocEntry = OPDN.DocEntry

where ItemCode = t1.itemcode

order by TaxDate desc) as LastPurchaseDate,(select top 1 TaxDate from IGE1 Inner Join OIGE On IGE1.DocEntry = OIGE.DocEntry

where ItemCode = t1.itemcode

order by TaxDate desc) as LastIssueDate,t1.InvntryUom

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

group by t1.itemcode,t1.InvntryUom

)a

)b

where b.Bal > 0

order by code