What is SAP B1 query by Posting date from to to date for DocNum,Customer/Vendor Code,Customer/Vendor Name,Item No,Item Description,Quantity,Price&DocTotal by using tables ODLN &DLN1 tables?

[caspian005]

Dear Experts,

What is SAP B1 query by Posting date from to to date for DocNum,Customer/Vendor, Code,Customer/Vendor Name, Item No, Item Description, Quantity, Price& Doc Total by using tables ODLN &DLN1 tables?

Thanks & Regards,

Prabhakar Patole

[former_member212181 · Accepted solution]

Hi Prabhakar,

Please try below query and revert back with results.

Select DNHead.Docnum[DN Num],DNHead.DocDate[DN Date]

  ,DNHead.CardCode [BP COde], BPMstr.CardName [BP Name]

  , DNLine.ItemCode, ItmMstr.ItemName

  , DNLine.Quantity[DN Qty], DNLine.Price[DN Price]

  , DNHead.DocTotal[DN Total]

from ODLN DNHead

  Inner Join DLN1 DNLine on DNHead.Docentry = DNLine.DocEntry

  Inner Join OCRD BPMstr on DNHead.CardCode = BPMstr.CardCode

  Left Outer Join OITM ItmMstr on DNLine.ItemCode = ItmMstr.ItemCode

Where DNHead.DocDate>=[%0] and DNHead.DocDate<=[%1]

group By DNHead.Docnum,DNHead.DocDate

  ,DNHead.CardCode, BPMstr.CardName

  , DNLine.ItemCode, ItmMstr.ItemName

  , DNLine.Quantity, DNLine.Price

  , DNHead.DocTotal

Thanks

Unnikrishnan

[caspian005 · Accepted solution]

Dear Unnikrishnan,

Thanks for the query, it works well as I desired.

Thanks & Regards,

Prabhakar Patole

[former_member188586]

hi

check bellow Query and update the Status

SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T0.[DocTotal] FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate]<=[%0] and  T0.[DocDate]>=[%1]

----RAMUDU

[former_member188586]

hi sorry try this one

SELECT T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T0.[DocTotal] FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate]>=[%0] and  T0.[DocDate]<=[%1]

[caspian005]

Dear Sir,

Thanks For the Query. it works well.

Thanks & Regards,

Prabhakar Patole