Query for Item IN (Goods receipt PO) and OUT (Delivery) details SAP Business One

[nikunjmehta2290]

Hello all,

Kindly help me on that query like

Item code ,

Item Description

it's batch number ,

item Last inward Date (Goods receipt PO)

item dispatch date (Delivery note) ,

Warehouse.

Above details comes into single query. There is no production here. Only Goods receipt and delivery base document generated.

Version : SAP Business One HANA

Regards,

Nikunj Mehta

[PierreBrothier]

Hi,

you can start with the following tables :

• OBTN : Batch Numbers Master Data
• OITL : Inventory Transactions Log
• ITL1 : Srl & Batch Details in Transac

with the following query you can get delivery and goods receipt.

SELECT b."ItemCode",l1."ItemName", "DistNumber",l1."DocEntry",l1."DocType",l1."DocLine", l1."CreateDate" FROM 
OBTN b
INNER JOIN ITL1 l
ON
b."SysNumber" = l."SysNumber"
AND b."ItemCode" = l."ItemCode"
INNER JOIN OITL l1
ON l."LogEntry" = l1."LogEntry"
AND l."ItemCode" = l1."ItemCode" WHERE l1."DocType" in (15,20)

to get the warehouse, you'll need OBTW "Batch Attributes in Location"