Reading my Query in SAP B1

[datta1990]

Hi Team,

Can you suggest why Im unable to get a proper output when we enter supplier code and date.

Select

T0.[CardName] 'Supplier Name',

T0.[CardCode] 'Supplier Number',

T1.[ItemCode], T3.[FirmName]'Manufacturer',

T0.[DocDate]'Date',

T0.[DocTotal] 'total spend'

FROM OPCH T0

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

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

INNER JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode]

WHERE T0.[DocDate] between [%0] and [%1] AND T0.[CardCode] = [%2]

Note:Total spend as in total net invoiced value

[Johan_H]

Hi,

What would be the proper output, and what output are you getting?

Regards,

Johan

[datta1990]

Good morning sir,

I have below output

Can you create a new report which allows you to enter dates and a supplier number and which has the following columns:

supplier name

Supplier number

manufacturer

total spend

date

output:

[Johan_H]

Hi,

I am sorry, but I do not understand. The query you have posted does exactly what you say you need.

Regards,

Johan

[datta1990]

Hi sir,

We would like a new report

I would like to enquiry to be based on suppliers/customer on a given date range.

I want to see in the report for the chosen business partner for that date range, the total value of items sold or purchased broken down by manufacturer. This can be as line items if you are unable just to pull off the totals.

So Search criteria:

BP Code

Date From:

Date To:

Report Headings (Example)

BP Code BP Name Manufacturer Total

C0001 ABC Cisco £1.00

C0001 ABC HP £1.00

C0001 ABC Citrix £2.00

Regards,

Datta

[datta1990]

Hi,

Good Morning,

/*Please run this Query */

SELECT T0.[CardCode] as 'Supplier Number',

T0.[CardName] as 'Supplier Name',

T3.[FirmName] as 'Manufacturer',

T1.Quantity * T1.Price as 'Total'

FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode]

WHERE T0.[DocDate] between [%0] and [%1] AND T0.[CardCode] = [%2]

Union

SELECT T0.[CardCode] as 'Supplier Number',

T0.[CardName] as 'Supplier Name', T3.[FirmName] as 'Manufacturer',

- T1.Quantity * T1.Price as 'Total' FROM ORPC T0

INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode]

WHERE T0.[DocDate] between [%0] and [%1] AND T0.[CardCode] = [%2]

[datta1990]

Hi,
The following query is working on sql but not on SAP B1. Can anyone tell me the reason

Select A.[Supplier Number], A.[Supplier Name], A.[Item Code], A.[Manufacturer], A.[Value], A.[Qty] , A.[Invoice LineTotal] as 'Line Total' From ( SELECT T0.[CardCode] as 'Supplier Number', T0.[CardName] as 'Supplier Name', t1.[Itemcode] as 'Item Code', T3.[FirmName] as 'Manufacturer', T1.[Price]as'Value', T1.[Quantity]as 'Qty', T1.[LineTotal] as 'Invoice LineTotal' FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] Left Outer JOIN RPC1 T4 ON T4.BaseEntry = T1.DocEntry and T4.ItemCode = T1.ItemCode Left OUter Join ORPC T5 On T4.DocEntry = T5.DocEntry WHERE T0.[DocDate] between '2016/06/01' and '2016/06/30' AND T0.[CardCode] between 's0072' and 's0072' UNION ALL SELECT T5.[CardCode] as 'Supplier Number', T5.[CardName] as 'Supplier Name', T4.[Itemcode] as 'Item Code', T3.[FirmName] as 'Manufacturer', T4.Price as 'Value', T4.Quantity as 'Qty', - T4.[LineTotal] as 'Invoice LineTotal'--, FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] INNER JOIN RPC1 T4 ON T4.BaseEntry = T1.DocEntry and T4.ItemCode = T1.ItemCode INNER Join ORPC T5 On T4.DocEntry = T5.DocEntry WHERE T0.[DocDate] between '2016/06/01' and '2016/06/30' AND T0.[CardCode] between 's0072' and 's0072' ) A Order BY A.[Item Code]

[gonzalogomez]

Try this...

SELECT T0.[CardCode] 'Supplier Name', T0.[CardName] 'Supplier Name', T1.[ItemCode], T3.[FirmName] 'Manufacturer', T0.[DocDate] 'Date', T0.[DocTotal] 'Total Spend' FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OMRC T3 ON T2.FirmCode = T3.FirmCode
WHERE T0.[DocDate] between [%0] and [%1] AND T0.[CardCode] = [%2]

[datta1990]

thanks for support.

I had done but not perfect output.

I have created new Query but problem ..(condition--> :When I say Range of suppliers, I mean putting in a Start and end supplier code so that I can run on multiple or all suppliers at one time)

SELECT T0.[CardCode] as 'Supplier Number', T0.[CardName] as 'Supplier Name', t1.[Itemcode] as 'Item Code',

T3.[FirmName] as 'Manufacturer', T1.Quantity as 'AP_Invoice_Quantity',

T4.[Quantity]as 'credit notes_Quantity', T1.[Price] as 'Value',

T4.Quantity * T4.Price as 'InvoiceLineTotal',

T1.Quantity * T1.Price as 'CreditLineTotal',

((T4.Quantity * T4.Price) - (T1.Quantity * T1.Price)) as 'Total' FROM ORPC T0

INNER JOIN RPC1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] LEFT JOIN OMRC T3 ON T2.[FirmCode] = T3.[FirmCode] Inner Join PCH1 T4 on T1.DocEntry = T4.BaseEntry and t1.ItemCode= t4.ItemCode Inner Join OPCH T5 On T4.DocEntry = T5.DocEntry

Where T0.DocType = 'I'