Skip to Content
author's profile photo datta phulse
datta phulse

Regarding a table

Posted on Mar 08, 2017 at 01:02 PM 181 Views

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
Add comment
10|10000 characters needed characters exceeded

2 Answers

  • author's profile photo Nagarajan K
    Nagarajan K
    Posted on Mar 08, 2017 at 01:46 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • author's profile photo Nagarajan K
    Nagarajan K
    Posted on Mar 09, 2017 at 01:47 PM

    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]

    Add comment
    10|10000 characters needed characters exceeded