Skip to Content
0

Regarding a table

Mar 08, 2017 at 01:02 PM

172

avatar image
datta phulse

hello,

can someone tell me how to make the following table

I want supplier name, supplier number, manufacturer, spend (net invoice value) and date.

Can someone put a query how to make it

SAP Business One
Answers Answers and Comments
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Sort by:
avatar image
Nagarajan K Answered Mar 08, 2017 at 01:46 PM
0

Hi,

Try this query,

SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] 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]

Regards,

Nagarajan

Show 1 Share
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
datta phulse
Mar 09, 2017 at 01:43 PM

Hi Sir,

To get spend (net invoice) value can we subtract values between OPCH table & ORPC table.

Also, Can you suggest what joint to put on OMRC table to get values for manufacturer

Thankful for your replies.

0
Comment
avatar image
Nagarajan K Answered Mar 09, 2017 at 01:47 PM
0

Try this,

SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] 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]

UNION ALL

SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], -T0.[DocTotal] FROM ORPC T0 INNER JOIN RPC1 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]

Show 6 Share
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
datta phulse
Mar 09, 2017 at 01:59 PM

Hi

When entered dates and a supplier number and which has the following columns:

  • supplier name

    • Supplier number

  • manufacturer
  • total spend = Total spend as in total net invoiced value.
  • date
    • 0
    datta phulse
    Mar 09, 2017 at 02:01 PM

    thanks but both query not woring

    0
    Nagarajan K
    Mar 09, 2017 at 02:09 PM

    Could you share your query and its result? What you mean supplier number?

    0
    datta phulse
    Mar 09, 2017 at 03:12 PM

    Thanks for reply

    T0.[CardCode] 'supplier number'....

    Hi

    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

    • Thanks


    0
    datta phulse
    Mar 10, 2017 at 12:18 PM

    Hi Sir,

    The below query is working fine in SQL but some of the values are coming in negative. Can you suggest why? Also can you suggest how to make this query work in SAPB1

    SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], T0.[DocTotal] 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 '2006-01-01' and '2017-01-01' and T0.[CardCode]= 'V1010' UNION ALL SELECT T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode],T3.[FirmName], -T0.[DocTotal] 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 '2006-01-01' and '2017-01-01' and T0.[CardCode]= 'V1010'

    0
    Nagarajan K
    Mar 10, 2017 at 02:59 PM

    Any error message while running above query in Query generator? Could you share your query result here?

    0
    Comment