Query - Please help!!

[Former Member]

I am new to SAP B1 and am looking to create a Query if anyone could be of assistance.

Outcome: A list of all transactions for a certain Business Partner buying/supplying a certain item code (including; item description, QTY on invoice, item price). Being able to manually enter the item code and the BP number as a selection would be perfect.

Thank you,

Chris

Noosa, Australia

[former_member183582]

Select T0.[DocNum] as "Document Number", T0.[DocStatus] as "Document Status", T0.[DocDate] as "Document Date", T0.[CardCode] as "BP Code", T0.[CardName] as "BP Name", T1.[ItemCode] as "Item Code", T1.[Dscription] as "Item Description", T1.[Quantity] as "Quantity", T1.[Price] as "Price", T1.[LineTotal] as "Total" From

OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

Where T0.[CardCode] =[%0] and  T1.[ItemCode] =[%1]

Irfan

[kothandaraman_nagarajan]

Hi Chris Cole,

Welcome to SAP B1 forum.

Please try this query too.

SELECT

T0.[DocNum], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal]

FROM

OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE

T0.[CardCode] =[%0] and  T1.[ItemCode] =[%1]

Thank you

[Former Member]

Hi Chris,

Here is another query to choose from

SELECT T0.DocNum, T0.CardCode, T0.CardName, T1.ItemCode, T1.Dscription, T1.Quantity, T1.Price

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocType = 'I' AND T0.CardCode = '[%0]' AND T1.ItemCode = '[%1]'

ORDER BY T0.DocNum

Kind Regards,

Nick Lakasas

[former_member232093]

Hi Chris,

run this query:

SELECT DISTINCT

  T0.DocEntry,

  T0.DocNum AS [AP Invoice#],

  T0.DocDate,

  T0.CardCode as [Supplier Code],

  T0.CardName AS [Supplier Name],

  T1.ItemCode,

  T1.Dscription AS [Item Description],

  T1.Quantity,

  T1.Price ,

  T1.TaxCode,

  T1.LineTotal,

  T0.DocTotal

  -- Add fields From OCRD - Business Partner Master Data

  --Add Fields From OITM -- Item Master Data

  -- Add UDF's from Purchase Invoice header and lines table

FROM OPCH T0

    INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry

    INNER JOIN OCRD T2 on T0.CardCode= T2.CardCode

    INNER JOIN OITM T3 on T1.ItemCode =T3.ItemCode

WHERE t3.itemcode=[%2] and T0.cardname=[%3] and T0.DocDate >= [%0] AND T0.DocDate <= [%1]

[Former Member]

Hi Chris,

You can get  all invoices data with the help of this query .

SELECT T0.[DocNum], T0.[DocStatus], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal] FROM OINV T0  INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[CardCode] =[%0] and  T1.[ItemCode] =[%1]

Thanks

M Imran