Open Item List

[former_member224367]

Hi Guru,

Anyone can help me how to create queries/SQL semilar with open item list for Purchase Order and filter by posting date? Also need to display additional columns for username. Please assist

Sample field Reports as below

Doc No, Vendor Code, Vendor Name, Vendor Ref No, Due Date, UserName, Amount (Currency), Amount, Net (Currency), Net, Tax (Currency), Tax, Original Amount, Posting Date, Document Date.

Thanks

Saufil

[kothandaraman_nagarajan · Accepted solution]

Hi,

If you got answer, please close this thread by marking correct answer.

Thanks &  Regards,

Nagarajan

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T0.[DocDueDate], T2.[U_NAME], T1.[Quantity], T1.[Price], T1.[Currency], T0.[DocDate], T0.[DocTotal] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSR T2 ON T0.UserSign = T2.USERID WHERE T0.[DocStatus] = 'O' and T0.[DocDate] between [%0] and [%1]

Thanks & Regards,

Nagarajan

[former_member224367]

Dear Nagarajan,

Thanks for your reply, i tried modify and make it distinct DocNum, but i can't see open amount. Actually additional columns is open amount (it's item delivered partially) and total original PO.

Sorry, i'm still new with SAP B1.

Thanks,

Saufil

[kothandaraman_nagarajan]

Hi,

Try :

SELECT T0.[DocNum] as PO#, T0.[CardCode], T0.[CardName], T0.[NumAtCard] as VendorRef, T0.[DocDueDate], T2.[U_NAME], T1.[Quantity],T1.[OpenQty] as 'Balance Quantity', T1.[Price], T1.[Currency], T0.[DocDate], T0.[DocTotal], T0.[PaidToDate] FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSR T2 ON T0.UserSign = T2.USERID WHERE T0.[DocStatus] = 'O' and T0.[DocDate] between [%0] and [%1]

Thanks & Regards,

Nagarajan

[former_member224367]

Hi Nagarajan,

I tried this code and similar with Open item list (as per SAP B1 standard report). But i still need the username for display.

SELECT T0.DocNum, T0.Project, T0.[DocStatus],T0.NumAtCard,

(SELECT (SUM(T2.OpenQty * T2.Price) * ((100-T0.DiscPrcnt)/100))  +  SUM(T2.VatSum  - T2.VatAppld )  FROM POR1 T2 WHERE T2.DocEntry = T0.DocEntry) AS 'Amount',

(SELECT SUM(T2.OpenQty * T2.Price) * ((100-T0.DiscPrcnt)/100)   FROM [dbo].[POR1] T2 WHERE T2.DocEntry = T0.DocEntry) AS 'Net',

(T0.VatSum - T0.VatPaid)AS 'Open_Vat',

T0.[DocTotal]  AS 'Original Amount ', 

T0.[DocDate] AS 'Posting_Date',

T0.[TaxDate] AS 'Document_Date',

T0.[DocDueDate] AS 'Due_Date',

T0.[CardCode], T0.[CardName]

FROM [dbo].[OPOR] T0

WHERE  T0.DocStatus = 'O' AND   (T0.[Project] = [%0]  OR [%0] = '')  AND (T0.[DocDate] >= '[%1]' OR '[%1]' = '' ) AND (T0.[DocDate] <= '[%2]' OR '[%2]' = '' )

Sorry, i'm not expert in SQL statement

Thanks

[kothandaraman_nagarajan]

Hi,

Try:

SELECT T0.DocNum, T0.Project, T0.[DocStatus],T0.NumAtCard,

(SELECT (SUM(T2.OpenQty * T2.Price) * ((100-T0.DiscPrcnt)/100))  +  SUM(T2.VatSum  - T2.VatAppld )  FROM POR1 T2 WHERE T2.DocEntry = T0.DocEntry) AS 'Amount',

(SELECT SUM(T2.OpenQty * T2.Price) * ((100-T0.DiscPrcnt)/100)   FROM [dbo].[POR1] T2 WHERE T2.DocEntry = T0.DocEntry) AS 'Net',

(T0.VatSum - T0.VatPaid)AS 'Open_Vat',

T0.[DocTotal]  AS 'Original Amount ', 

T0.[DocDate] AS 'Posting_Date',

T0.[TaxDate] AS 'Document_Date',

T0.[DocDueDate] AS 'Due_Date',

T0.[CardCode], T0.[CardName],T2.[U_NAME]

FROM [dbo].[OPOR] T0 INNER JOIN OUSR T2 ON T0.UserSign = T2.USERID

WHERE  T0.DocStatus = 'O' AND   (T0.[Project] = [%0]  OR [%0] = '')  AND (T0.[DocDate] >= '[%1]' OR '[%1]' = '' ) AND (T0.[DocDate] <= '[%2]' OR '[%2]' = '' )

Thanks & Regards,

Nagarajan