Query to show items not used upto 31st December 2012

[former_member593234]

Hi All,

I want query to show item not used (SQ, SO, PO) upto 2012 Dec, Please Help Me.

[zal_parchem2 · Accepted solution]

Here you go Athul - wrote this during taxi ride to airport.

In order to fullfill your requirements, just add in whatever Line Table you want in the WHERE area of the SQL (for example, INV1 is for AR Invoices, RIN1 is for Credit Memos, IGN1 for Goods Receipts, etc.). Some of them might require DocDate - you can find that out through the usage of the Query Generator. Verification is done with the Inventory Audit Report on the Item Master Data Record...

Best Regards - Zal

SELECT DISTINCT
T0.ItemCode,
T0.ItemName,
T0.FrgnName

FROM OITM T0

WHERE
((T0.ItemCode NOT IN (SELECT T1.ItemCode FROM QUT1 T1 WHERE T1.ShipDate <= '12/31/2016'))
AND (T0.ItemCode NOT IN (SELECT T2.ItemCode FROM RDR1 T2 WHERE T2.ShipDate <= '12/31/2016'))
AND (T0.ItemCode NOT IN (SELECT T3.ItemCode FROM DLN1 T3 WHERE T3.ShipDate <= '12/31/2016')))

ORDER BY
T0.ItemCode

[zal_parchem2]

Hello Athul...with a date that long ago, is hard for me to test it out since most Customers I have are fairly new on SAP B1. So I tried it with 2016 and it worked fine (you can change the year in the code below to suit your needs). Sometimes you need to use different "T" values when using a SELECT in the WHERE area.

Let us know if this works. Regards, Zal

SELECT DISTINCT
T0.ItemCode,
T0.ItemName,
T0.FrgnName

FROM OITM T0

WHERE
T0.ItemCode NOT IN (SELECT T1.ItemCode FROM OINM T1 WHERE T1.DocDate <= '12/31/2016')

ORDER BY
T0.ItemCode

[former_member593234]

Thanks for Reply, i have changed the date, but still 2014, 2015, 2016 sq is showing

[zal_parchem2]

Morning Athul - more than welcome...

There are some questions you need to answer for everyone trying to help you for several reasons:

1. Your requirement includes Sales Quotes (SQ?) in your definition of "item not used". Normally, folks do not consider a Sales Quote or Purchse Quote as usage of an item, since the item has had no finanical or inventory impact with a Sales Quote.
2. OINM is THE area where an item's activity has had a financial and/or inventory impact
3. There is no table in SAP B1 where the recording of a Sales Quote is included with all other types of Standard SAP B1 transactions.
4. Your requirement says SQ, SO, PO. Then in your reply to Nagarajan, you say you also need Invoice - is that AR Invoice or AP Invoice or both? There are other areas where the item can be "used" - Purchase Request, Delivery, Goods Return, etc, etc, etc. What exactly is the list of documents you want checked? If you say Sales Quotes, do you also want Purchase Requests, Purchase Quotes, Inventory Movement? It would be a great help for you to provide a definitive and complete list, because your End Users can use a vast array of transactions starting and ending at many different points. It will also help us understand how we might handle your needs.
5. If you are looking to have all usage of an item in all areas to be considered, what about Closed and Canceled documents? Is the creation of a document and the resulting Close or Cancellation considered "used" also?
6. The resulting SQL might be rather long and complex, depending upon how you formalize your requirements and the list you provide. I can picture a long CASE as one alternative, but I do not think that is going to give you what you need. A UNION ALL is not going to work either. The answer might be in creating a temporary table or a User-Defined table - just a thought, not sure.

Okay - those are the questions in my mind, and I am sure others have the same ones. If you could answer these it would be excellent for those trying to help you out here in SCN.

I head to Europe this afternoon, so after you provide a bit more information, maybe the answer will be there for you...

Many Thanks and Best Regards - Zal

[kothandaraman_nagarajan]

Hi,

Run below query for sales quotation,

SELECT T0.[ItemCode], T0.[ItemName], T0.[FrgnName] FROM OITM T0 WHERE T0.[ItemCode] NOT IN ( SELECT T1.[ItemCode] FROM OQUT T0 INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[DocDate] >='2013-01-01')

Regards,

Nagarajan

[former_member593234]

Thanks for the Reply,

I want 1 query for SQ and Invoice

[zal_parchem2]

Hey Nagarajan - maybe an idea??? - after Athul provides a list, how about a large WHERE statement like

(((T0.ItemCode NOT IN (SELECT T1.ItemCode FROM QUT1 T1 WHERE T1.ShipDate <= 12/31/2012)) OR (T0.ItemCode NOT IN (SELECT T2.ItemCode FROM RDR1 T2 WHERE T2.ShipDate <= 12/31/2012)) OR....etc...etc

Just thought of this as an alternative - no time for me to test out - what do you think???

Best Regards to You - Zal

[hdolenec]

I understood you need items that were not used before 2012 Dec? In that case it should be Docdate <= '2013-12-31'

[former_member593234]

I created query,

select itemcode,itemname,frgnname

from
OITM
where
ItemCode not in (select ItemCode from OINM where Docdate<= '2013-12-31')

but still that report is wrong, aftr this date prepared SQ, SO is showing

[former_member593234]

select itemcode,itemname,frgnname

from
OITM
where
ItemCode not in (select ItemCode from OINM where Docdate>='2013-01-01')

This is Correct?