I need to write a query in SAP B1 which will list all the Invoices which do not have a particular item code at row level.
What I want to see is all Invoices which do not have item code C&P in any of the rows.
I have tried something like this:
SELECT T0.[DocNum], T0.[CardCode], T0.[CardName] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[ItemCode] NOT Like 'C&P%%'
Unfortunately this brings up every row in every invoice so if I try and drill down into some orders there are entries for C&P.
I am not used to using SQL but can write some basic queries. I would normally ask our SAP B1 support company to do something like this but they'll only tell me it is bespoke work and therefore chargeable which I don't have the budget for right now!