Relation between User Equipment OINS and Goods Receipt OIGN

[former_member1338375]

When you use the SAP GUI (in my case SAP B1 9.2) you can browse the user equipment, go for the "Transactions" tab and you see Goods Receipt and Goods Issue entrys. I want that information for a given equipment id.

According to "System Informations", this is the table "RITL", but that one does not exist. It is a virtual table, which another question stated out (https://answers.sap.com/questions/9587445/can-not-find-the-table-ritl.html).

I'm not able to find the relation between User Equipment OINS and Goods Receipt OIGN. There is no insID (PK of OINS) in OIGN and neither is a reference to OIGN in OINS. Do you know if there is a matching table in between and which one it is/which column?

I searched the whole database for occurences of the used OIGN.DocNum (which I see in the GUI). I found entries in the OITL/ITL1, OILM, OIVL and OJDT tables (several logs/journals) but could'nt create a relation to the equipment.

Thanks in advance!

[agustin_marcoscividanes]

Hi

use itemcode and sysnumber to join with OSRL tables, and with serial number and itemcode join to OISN

Kind regards

Agustín

[former_member1338375]

Thanks Agustin, that one helped. I didn't know I had to query the serial number tables for that.

In my case the table's name was OSRN, and it sufficed to join its ItemCode and SysNumber with ITL1.

Just in case anyone is googling:

SELECT [DB].[dbo].[OITL].TransId 
FROM [DB].[dbo].[OSRN], [DB].[dbo].[OITL], [DB].[dbo].[ITL1]
WHERE [DB].[dbo].[OSRN].ItemCode = [DB].[dbo].[ITL1].ItemCode
AND [DB].[dbo].[OSRN].SysNumber =  [DB].[dbo].[ITL1].SysNumber
AND [DB].[dbo].[OITL].LogEntry = [DB].[dbo].[ITL1].LogEntry
AND [DB].[dbo].[OSRN].DistNumber = '$MY_EQUIPMENT_SERIAL$'

[agustin_marcoscividanes]

Hi

try with the OITL and ITL1 tables.

Kind regards

Agustín

[former_member1338375]

Hi Agustin,

thanks for your fast reply. As I stated in my last senctence, "I found entries in the OITL/ITL1 tables but could'nt create a relation to the equipment.".

So I know that the OITL.TransId equates the Transaction Number shown in the GUI.

But I can't find a relation tablewise. Say I have a Equipment-ID (OINS.insID) 1234:

SELECT TransId  
FROM [DB].[dbo].[OITL]
WHERE ??? = 1234;

What is your solution?

In ITL1 there is no more useful information for a given OITL-entry.

I show you the SQL results exported as CSV, if that helps you.

The OINS with given insID:

SELECT * 
FROM [DB].[dbo].[OINS]
WHERE insID = 16276;

insID;customer;custmrName;contactCod;directCsmr;drctCsmNam;manufSN;internalSN;warranty;wrrntyStrt;wrrntyEnd;responsVal;responsUnt;itemCode;itemName;itemGroup;manufDate;delivery;deliveryNo;invoice;invoiceNum;dlvryDate;cntctPhone;street;block;zip;city;county;country;state;instLction;contract;cntrctStrt;cntrctEnd;attachment;objType;logInstanc;userSign;createDate;userSign2;updateDate;Building;status;replcIns;repByIns;technician;territory;AtcEntry;Transfered;AddrType;Instance;StreetNo;BPType;OwnerCode

16276;114420;Beautiful Name;5830;114420;Beautiful Name;;W.45168;N;NULL;NULL;NULL;D;FMI-S30K5;Digitales Kraftmessgerät, Zug und Druck, 5000N, HR, USB, Solarpower;NULL;NULL;17569;117568;NULL;NULL;2019-10-22 00:00:00.000;NULL;beautiful street;;beautiful zip;beautiful city;;beautiful country;;NULL;NULL;NULL;NULL;NULL;176;NULL;2220;2019-10-22 00:00:00.000;2220;2019-10-22 00:00:00.000;NULL;A;NULL;NULL;NULL;NULL;NULL;N;NULL;0;NULL;R

The OITL with transID known from GUI:

SELECT *  
FROM [DB].[dbo].[OITL]
WHERE TransId = 126114

LogEntry;TransId;ItemCode;ItemName;ManagedBy;DocEntry;DocLine;DocType;DocNum;BaseEntry;BaseLine;BaseType;ApplyEntry;ApplyLine;ApplyType;DocDate;CardCode;CardName;DocQty;StockQty;DefinedQty;StockEff;CreateDate;LocType;LocCode;AppDocNum;VersionNum;Transfered;Instance;SubLineNum;BSubLineNo;AppSubLine;ActBaseTp;ActBaseEnt;ActBaseLn;ActBasSubL;AllocateTp;AllocatEnt;AllocateLn;CreateTime

126114;126114;FMI-S30K5;Digitales Kraftmessgerät, Zug und Druck, 5000N, HR, USB, Solarpower;10000045;15402;4;59;115401;21898;-1;202;15402;4;59;2019-10-22 00:00:00.000;1095;In Arbeit befindliche Aufträge;1.000000;1.000000;1.000000;1;2019-10-22 00:00:00.000;64;01-3;115401;NULL;N;0;-1;-1;-1;202;21898;-1;-1;-1;-1;-1;1402

The ITL1 with the same LogEntry as the OITL Record:

SELECT *
FROM [DB].[dbo].[ITL1]
WHERE LogEntry = 126114

LogEntry;ItemCode;SysNumber;Quantity;AllocQty;MdAbsEntry;ReleaseQty;PickedQty

126114;FMI-S30K5;28;1.000000;0.000000;17254;0.000000;0.000000