Customized serial number monitoring report

[Former Member]

hi experts,

I need to create a customized report to monitor the serialized rental equipment that have been returned and loaned to customers. Currently, the serial number transaction report can be helpful to monitor the history per serial number but the user would like to have a summary report for all serial numbers per item per given date range. I tried looking at the database tables and I found out that both RSRN and RITL are not accessible to the user. Is there a way I can create this report and use other relevant tables?

Thanks in advance.

Ana

[kothandaraman_nagarajan]

Hi Maria,

What are all the documents involved during loan and return process?

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

AR Invoice is used to record the serial number of the rental item then automatic creation of customer equipment card to record the rental period (actual rental duration) then a service contract is also created to have a recurring invoice for the succeeding / next month billing using a different item code (non-inventory item) so that there is no need to release again the same item in the warehouse then when the time comes the customer returns the rental item, we use AR Credit Memo to record the serial number back to the warehouse and have to terminate the service contract and make the status of the customer equipment card to "Returned"

Thanks,

Malu

[kothandaraman_nagarajan]

Hi Maria,

Thanks for detailed process. The tables involved  are OINV, INV1, ORIN,RIN1 and OSRN.

Let me try to create query as per your requirement.

Thanks & Regards,

Nagarajan

[Former Member]

Thanks Nagarajan!

[kothandaraman_nagarajan]

Hi Maria,

Try this query. let me know the result:

SELECT T0.[DocNum], T1.[ItemCode], T1.[Quantity], T2.[internalSN] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OINS T2 ON T0.DocEntry = T2.invoice INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode WHERE T0.[DocDate] between [%0] and [%1] and  T1.[ItemCode]  =[%2] GROUP BY T0.[DocNum], T1.[ItemCode], T1.[Quantity], T2.[internalSN]

union all

SELECT T0.[DocNum], T1.[ItemCode], T1.[Quantity], T3.[internalSN] FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OINS T3 ON T2.ItemCode = T3.itemCode WHERE T0.[DocDate] between [%0] and [%1] and  T1.[ItemCode]  =[%2]

GROUP BY T0.[DocNum], T1.[ItemCode], T1.[Quantity], T3.[internalSN]

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan,

I am getting this runtime error that the Item Number that I selected produces an "Invalid Column Name"

Thanks,

Malu

[kothandaraman_nagarajan]

Hi Maria,

Try this:

SELECT T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T2.[internalSN] FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OINS T2 ON T0.DocEntry = T2.invoice INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode WHERE T0.[DocDate] between [%0] and [%1] and  T1.[ItemCode] = [%2] GROUP BY T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T2.[internalSN]

union all

SELECT T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[internalSN] FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OINS T3 ON T2.ItemCode = T3.itemCode WHERE T0.[DocDate] between [%0] and [%1] and  T1.[ItemCode] =[%2] GROUP BY T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[internalSN]

Thanks & Regards,

Nagarajan

[Former Member]

Hello Maria

Use the data element ANZSN to search, you will get numbers of SAP tables with serial number field.

Kind Regards

[KennedyT21]

Hi Maria

Try this query which is base and bulid from that which will be easy i believe

SELECT  T0.BaseNum, T0.CreateDate, T0.WhsCode,

         T0.CardCode, T0.ItemCode, T2.IntrSerial

FROM     dbo.SRI1 T0

         INNER JOIN dbo.OSRI T2 ON T0.SysSerial=T2.SysSerial

WHERE       T0.ItemCode ='[%0]'

Hope Helpful

Regards

Kennedy

[Former Member]

Thanks Kennedy,

What if I want to have a link to my base document and how can I make the base document more descriptive to the user? (Ex. Doc # is an invoice, CN, etc) and can I also include the status of the serial number like IN warehouse, OUT of warehouse? Thanks a lot!

[KennedyT21]

Try This

SELECT  distinct T4.DocNum as 'Invoice No.', T4.DocDate, T4.NumAtCard as 'Ref. No.', T4.CardName as 'Customer Name',

  t2.ItemCode as 'Item Code',t0.ItemCode, T0.ItemName as 'Item Name', isnull(T0.[IntrSerial],0) as 'Serial No.'

FROM [dbo].[OSRI] T0

INNER JOIN SRI1 T1 ON T0.SysSerial = T1.SysSerial and T1.[BaseType]=13  and T0.[ItemCode]=T1.[ItemCode]

LEFT JOIN [dbo].[INV1] T2 ON T2.DocEntry=T1.BaseEntry and T2.LineNum=T1.BaseLinNum

LEFT JOIN [dbo].[OINV] T4 ON T4.DocEntry=T2.DocEntry

where T4.CardCode = '[%0]'

ORDER BY T0.ItemCode,isnull(T0.[IntrSerial],0)

Regards

Kennedy

[KennedyT21]

You can use the Base Type to identify the document

SELECT  T0.BaseNum, T0.CreateDate, T0.WhsCode,

         T0.CardCode, T0.ItemCode, T2.IntrSerial

,  
T1.[BaseType]

FROM     dbo.SRI1 T0

         INNER JOIN dbo.OSRI T2 ON T0.SysSerial=T2.SysSerial

WHERE       T0.ItemCode ='[%0]'

[Former Member]

Thank you Kennedy for this! 🙂

Can I also  include if there is an AR Credit Memo also for the same Customer by determining the Serial number of the Item. For Example: I have an invoice for Customer A for Item 1 with Serial Number ABC123, I also created an AR Credit Memo for Customer A for Item 1 with Serial Number ABC123 but my credit memo is not reference to the invoice that I created, the only link that I have is the unique serial number of the item. This will be very much helpful if we can link the invoice to credit memo so that we can determine if the rented item has been returned in the warehouse via the credit memo created in the system.

And also, can I filter my report to display only items for a particular item group like for example ItmsGrpCod = '102' (which is Rental Item Group)

Thanks again,

Ana

[KennedyT21]

Hi Ana...

Try This

SELECT  distinct T4.DocNum as 'Invoice No.','0' As CRNO, T4.DocDate, T4.NumAtCard as 'Ref. No.', T4.CardName as 'Customer Name',

  t2.ItemCode as 'Item Code',t0.ItemCode, T0.ItemName as 'Item Name', isnull(T0.[IntrSerial],0) as 'Serial No.'

FROM [dbo].[OSRI] T0

INNER JOIN SRI1 T1 ON T0.SysSerial = T1.SysSerial and T1.[BaseType]=13  and T0.[ItemCode]=T1.[ItemCode]

Inner join oitm c1 on c1.itemcode=T1.[ItemCode]

LEFT JOIN [dbo].[INV1] T2 ON T2.DocEntry=T1.BaseEntry and T2.LineNum=T1.BaseLinNum

LEFT JOIN [dbo].[OINV] T4 ON T4.DocEntry=T2.DocEntry

where T4.CardCode = '[%0]' and  c1.ItmsGrpCod = '[%1]'

Union all

SELECT  distinct '0' as 'Invoice No.',  T4.DocNum as CRNO, T4.DocDate, T4.NumAtCard as 'Ref. No.', T4.CardName as 'Customer Name',

  t2.ItemCode as 'Item Code',t0.ItemCode, T0.ItemName as 'Item Name', isnull(T0.[IntrSerial],0) as 'Serial No.'

FROM [dbo].[OSRI] T0

INNER JOIN SRI1 T1 ON T0.SysSerial = T1.SysSerial and T1.[BaseType]=14

and T0.[ItemCode]=T1.[ItemCode]

Inner join oitm c1 on c1.itemcode=T1.[ItemCode]

LEFT JOIN [dbo].rin1 T2 ON T2.DocEntry=T1.BaseEntry and T2.LineNum=T1.BaseLinNum

LEFT JOIN [dbo].orin  T4 ON T4.DocEntry=T2.DocEntry

where T4.CardCode = '[%0]' and  c1.ItmsGrpCod = '[%1]'

Hope Helpful

Regards

Kennedy

[Former Member]

Hi Kennedy,

I just had this run time error while executing the report: "Must specify table to select from..."

Thanks,

Malu

[KennedyT21]

try this

/* select   t2.cardcode FROM  ocrd t2   */

DECLARE @d2  AS NVARCHAR(100)

SET @d2 = /* t2.cardcode  */ '[%1]'

SELECT DISTINCT T4.DocNum AS InvoiceNo,

         '0' AS CRNO,

        T4.DocDate,

        T4.NumAtCard AS RefNo,

        T4.CardName AS CustomerName,

        t2.ItemCode AS ItemCode,

        t0.ItemCode,

        T0.ItemName AS ItemName,

         ISNULL(T0.[IntrSerial], 0) AS SerialNo

FROM   [dbo].[OSRI] T0

        INNER JOIN SRI1 T1

             ON  T0.SysSerial = T1.SysSerial

             AND T1.[BaseType] = 13

             AND T0.[ItemCode] = T1.[ItemCode]

         INNER JOIN oitm c1

             ON  c1.itemcode = T1.[ItemCode]

        LEFT JOIN [dbo].[INV1] T2

             ON  T2.DocEntry = T1.BaseEntry

             AND T2.LineNum = T1.BaseLinNum

        LEFT JOIN [dbo].[OINV] T4

              ON  T4.DocEntry = T2.DocEntry

WHERE  T4.CardCode = @d2

     UNION ALL

SELECT DISTINCT '0' AS 'Invoice No.',

        T4.DocNum AS CRNO,

        T4.DocDate,

        T4.NumAtCard AS 'Ref. No.',

         T4.CardName AS 'Customer Name',

        t2.ItemCode AS 'Item Code',

        t0.ItemCode,

        T0.ItemName AS 'Item Name',

        ISNULL(T0.[IntrSerial], 0) AS 'Serial No.'

  FROM   [dbo].[OSRI] T0

        INNER JOIN SRI1 T1

             ON  T0.SysSerial = T1.SysSerial

             AND T1.[BaseType] = 14

             AND T0.[ItemCode] = T1.[ItemCode]

        INNER JOIN oitm c1

              ON  c1.itemcode = T1.[ItemCode]

        LEFT JOIN [dbo].rin1 T2

             ON  T2.DocEntry = T1.BaseEntry

             AND T2.LineNum = T1.BaseLinNum

        LEFT JOIN [dbo].orin T4

             ON  T4.DocEntry = T2.DocEntry

  WHERE  T4.CardCode = @d2

[Former Member]

Hi Kennedy,

I encountered a runtime error when I execute this query. "Must specify table to select from"

Thanks,

Ana

[KennedyT21]

Hi ana...

Its working for me check once again

[kothandaraman_nagarajan]

Hi Maria,

RSRN, RBTN, RITL or SBDR are virtual tables and these table do not store any content and can't be queried.

Try with OSRN table.

Thanks & Regards,

Nagarajan

[KennedyT21]

Hi Maria

There are 2 SQL views in the database called OSRI for serial numbers and OIBT for batch numbers.

Have you checked with that

Hope Helpful

Regards

Kennedy