Report of Available Items managed by Serial Numbers

[Former Member]

Serial No	In/Admission/Posting Date	Invoice Date
1	09/13/2012	09/13/2012
2	09/10/2012	09/13/2012
3	09/10/2012	09/10/2012
4	09/28/2012	09/28/2012
5	09/25/2012	10/15/2012
6	09/30/2012	10/08/2012
7	09/30/2012	9/30/2012

We manage certain items by serial number. this items are manufactured items by the company. So upon receipt from production, serial numbers are assigned.

So now, we would like to extract a report of all available items as of a certain date, for example as of Sept 30, 2012.

I used the OSRI status column to determine whether it is available or not. Also used the ff. tables: OSRN(for most of details), IGN1 for Item Cost.

However, there are some items that are still supposed to be available as Sept 30, 2012 as they were only sold only sometime in Oct. 2012.

So if we are to extract a report as of Sept. 30, 2012, the item should still be on that list. So if we refer to example above result would be

Item # 5 and Item #6 are still available as of Sept. 30, 2012.

Thanks.

[Former Member]

Try:

SELECT b.ItemCode,

b.DistNumber, b.MnfSerial, b.LotNumber, b.Notes, b.InDate, b.CreateDate,

d.StockPrice, d. Price,

e.DocDate

from osri a

                    inner join osrn b on (a.IntrSerial=b.DistNumber and b.MnfSerial=a.SuppSerial)

                    inner join oitm c on b.ItemCode=c.ItemCode

inner join ign1 d on a.BaseEntry=d.DocEntry and a.BaseLinNum=d.LineNum AND d.ObjType='59'

inner join oign e on d.DocEntry=e.DocEntry AND e.DocDate < [%0]

                    where a.Status<>1

order by b.DistNumber asc

[Former Member]

I tried the query, the number of results I get is the same as that of the original query.

[Former Member]

Hi,

Have you tried any report yet? If yes, post it here so that it will be easier for us to give you help.

Thanks,

Gordon

[Former Member]

Hi Gordon,

Here is the report/query I came up with

select b.ItemCode,

b.DistNumber, b.MnfSerial, b.LotNumber, b.Notes, b.U_LOT, b.InDate, b.CreateDate,

c.U_PARTNUMBER, c.U_COLOR,

d.StockPrice, d. Price,

e.DocDate

from osri a

                    inner join osrn b on (a.IntrSerial=b.DistNumber and b.MnfSerial=a.SuppSerial)

                    inner join oitm c on b.ItemCode=c.ItemCode

inner join ign1 d on a.BaseEntry=d.DocEntry and a.BaseLinNum=d.LineNum

inner join oign e on d.DocEntry=e.DocEntry

                    where a.Status<>1

order by b.DistNumber asc

Parameters are: Date From and Date To referring to the InDate field.