Skip to Content
0

Find Pending Quantity

Apr 09 at 11:35 AM

26

avatar image

Hi Experts,

I'm new to SAP B1 9.2

Please help me to find out Pending Quantity for the following (need the column name)


Requirement

Report shows Purchase Orders with near delivery dates and purchase orders which were supposed to be delivered earlier.

Example: User needs to find the next deliveries within the next 30 days and deliveries which were supposed to happen earlier.

Columns

1.PR No

2.Purchase Order No

3.Supplier Code

4.Supplier Name

5.Item Code

6.Item Description

7.Pending Quantity

8.Delivery Date

the sql query i have written so far

SELECT  OPRQ.DocNum AS [PR No], ISNULL(OPOR.DocNum, '0') AS [Purchase Order No],        ISNULL(OPOR.CardCode,OPRQ.CardCode) AS [Supplier Code], ISNULL(OPOR.CardName,OPRQ.CardName) AS [Supplier Name],        ISNULL(POR1.ItemCode,PRQ1.ItemCode) AS [Item Code], ISNULL(POR1.ItemCode,PRQ1.ItemCode) AS [Item Description],        OPRQ.EndDlvDate AS [Delivery Date]FROM  POR1 INNER JOIN  OPOR ON POR1.DocEntry = OPOR.DocEntry LEFT OUTER JOIN  OPRQ INNER JOIN  PRQ1 ON OPRQ.DocEntry = PRQ1.DocEntry ON OPOR.DocEntry = PRQ1.TrgetEntry AND PRQ1.TargetType = 22

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Abdul Mannan Apr 09 at 12:18 PM
0

Hi,

Use this and add your required fields, it will not shows item which have purchase quotation and then purchase order. If you want you have to make join with quotation tables also.

Select OPOR."CardCode" From OPRQ 
Inner Join PRQ1 on OPRQ."DocEntry" = OPRQ."DocEntry"
Left Outer join POR1 on POR1."BaseEntry" = PRQ1."DocEntry" and POR1."BaseType" = OPRQ."ObjType"
Inner Join OPOR on "OPOR"."DocEntry" = POR1."DocEntry"
Share
10 |10000 characters needed characters left characters exceeded