Skip to Content

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()}

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply