Skip to Content
0

Reading my Query in SAP B1

Mar 10, 2017 at 01:57 PM

98

avatar image

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

10 |10000 characters needed characters left characters exceeded

Hi,

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

Regards,

Johan

0

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:

    0

    Hi,

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

    Regards,

    Johan

    0

    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]

    0

    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

    0
    * Please Login or Register to Answer, Follow or Comment.

    2 Answers

    Gonzalo Gomez Mar 17, 2017 at 12:19 PM
    0

    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]
    
    Show 1 Share
    10 |10000 characters needed characters left characters exceeded

    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'

    0
    datta phulse Mar 24, 2017 at 11:05 AM
    0

    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]

    Share
    10 |10000 characters needed characters left characters exceeded