Inventory Status
SELECT OITM.ItemCode, OITM.ItemName, OITM.InvntryUom, OITM.U_Category, [@CATEGORY].Name, OITB.ItmsGrpNam,
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
2 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |