Business Partner - Not Yet Invoiced
Adjust Date Accordingly.
SELECT OPDN.DocEntry, OPDN.TaxDate, OPDN.DocType,OPDN.NumAtCard,CardName,PDN1.UnitMsr, PDN1.Dscription, PDN1.Quantity, PDN1.Price, PDN1.GTotal,OPDN.Comments,OPDN.docDate,OPDN.CardCode FROM OPDN INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry and OPDN.CardCode='SU000147' 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 <= {?FromDate}
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()
Inventory Status
SELECT OITM.ItemCode, OITM.ItemName, OITM.InvntryUom, OITM.U_Category, [@CATEGORY].Name, OITB.ItmsGrpNam,
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
Purchase Price Comparison
select ItemCode ,ItemName, isnull((Select top 1 PDN1.DocEntry from PDN1 INNER JOIN OPDN ON PDN1.DocEntry = OPDN.DocEntry where ItemCode = OITM.ItemCode order by OPDN.TaxDate desc),'') as LatestGRPONo,
isnull((Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry where ItemCode = OITm.ItemCode order by opdn.TaxDate desc),'') as LatestPurchaseDate_Postingdate, isnull((Select top 1 PDN1.Price from PDN1 INNER JOIN OPDN ON PDN1.DocEntry = OPDn.DocEntry where ItemCode = OITm.ItemCode order by OPDN.TaxDate desc),0) as LastPurchasePrice,
isnull((Select top 1 OPDN.DocEntry from PDN1 INNER JOIN OPDN ON PDN1.DocEntry = OPDN.DocEntry where PDN1.ItemCode = OITM.ItemCode and OPDN.TaxDate = ((Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry
where ItemCode = OITm.ItemCode and OPDn.TaxDate < ((Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry where ItemCode = OITm.ItemCode order by opdn.TaxDate desc)) order by opdn.TaxDate desc))),'') as PreviousGRPONo,
isnull((Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry
where ItemCode = OITm.ItemCode and OPDn.TaxDate < ((Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry
where ItemCode = OITm.ItemCode order by opdn.TaxDate desc))
order by opdn.TaxDate desc),'') as PreviousPurchaseDate, isnull((Select top 1 PDN1.Price from PDN1 INNER JOIN OPDN ON PDN1.DocEntry = OPDN.DocEntry where PDN1.ItemCode = OITM.ItemCode and OPDN.TaxDate = (Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry where ItemCode = OITm.ItemCode and OPDn.TaxDate < ((Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry where ItemCode = OITm.ItemCode order by opdn.TaxDate desc)) order by opdn.TaxDate desc)),0) as PreviousPurchasePrice,
isnull((Select top 1 PDN1.Price from PDN1 INNER JOIN OPDN ON PDN1.DocEntry = OPDn.DocEntry where ItemCode = OITm.ItemCode order by OPDN.TaxDate desc) - (Select top 1 PDN1.Price from PDN1 INNER JOIN OPDN ON PDN1.DocEntry = OPDN.DocEntry where PDN1.ItemCode = OITM.ItemCode and OPDN.TaxDate = (Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry
where ItemCode = OITm.ItemCode and OPDn.TaxDate < ((Select top 1 opdn.TaxDate from PDN1 INner join OPDN ON PDN1.DocEntry = OPDN.DocEntry where ItemCode = OITm.ItemCode order by opdn.TaxDate desc)) order by opdn.TaxDate desc)),0) as PriceDifference
from OITM
Item 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
Open Purchase Order (Which are not received yet)
SELECT POR1.DocEntry as PONo, POR1.ItemCode, POR1.Dscription as ItemName, POR1.Quantity, POR1.Price, POR1.OpenCreQty as NotReceivedQty
FROM POR1 INNER JOIN OPOR ON POR1.DocEntry = OPOR.DocEntry where OPOR.CANCELED <> 'Y' and OpenCreQty > 0
Recent Purchase Order
SELECT DocEntry, DocDate, CardCode, CardName, Comments, DocTotal FROM OPOR order by DocDate Desc
Goods Issues - Current Month
select DocEntry,TaxDate as DocumentDate,DocDate as PostingDate,Comments as Description from OIGE where CANCELED <> 'Y' and Month(TaxDate) = DatePart(mm, GETDATE())
Document Count
Total Purchase Requisition
select COUNT(*) as TotalPrCountOnCurrentDate from OPRQ where DocDate = {fn curdate()}
Total Purchase Order
select COUNT(*) as TotalPOCountOnCurrentDate from OPOR where DocDate = {fn curdate()}
Total Good Received PO
select COUNT(*) as TotalGRPOCountOnCurrentDate from OPDN where DocDate = {fn curdate()}
