Skip to Content
avatar image
Former Member

Find Pending Quantity

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Apr 09 at 12:18 PM

    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"
    
    Add comment
    10|10000 characters needed characters exceeded