Skip to Content
0

how to create a Open sales order report with purchasing price in SAP B1

Oct 11, 2017 at 07:24 AM

101

avatar image
Former Member

Hi Team,

We have "Open sales order" with 'selling price' report and an another report for "open purchase orders" with purchasing price. Now users would like to see the "Purchasing price" in the "Open Sales order" report.

I am quite new to SAP B1, could any one help me on this.

Regards,

Siva

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Zal Parchem
Oct 12, 2017 at 11:40 AM
1

Here Siva - give this a try...Regards, Zal

PS = whether you want to see all Sales Orders or just those Sales Orders with connected POs is dependent upon using INNER or LEFT OUTER JOINs.

SELECT

T1.DocNum, T1.CardCode, T1.DocDate,

T2.ItemCode, T2.Quantity,

T4.DocNum, T4.CardCode, T4.DocDate,

T3.ItemCode, T3.Quantity

FROM ORDR T1

LEFT OUTER JOIN RDR1 T2 ON T1.DocEntry = T2.DocEntry

LEFT OUTER JOIN POR1 T3

ON T1.DocEntry = T3.BaseEntry

AND T2.LineNum = T3.BaseLine

AND T2.ItemCode = T3.ItemCode

LEFT OUTER JOIN OPOR T4 ON T3.DocEntry = T4.DocEntry

WHERE

T1.DocType = 'I' AND T1.DocStatus = 'O' AND T2.LineStatus = 'O'

AND

T4.DocType = 'I' AND T4.DocStatus = 'O' AND T3.LineStatus = 'O'

Share
10 |10000 characters needed characters left characters exceeded
Zal Parchem
Oct 11, 2017 at 10:42 AM
0

Hello Siva - your question needs a bit more clarification, since there are several ways to do this.

1. What is the exact name and path (the way you get to the report)???

2. Is the report a standard SAP B1 Report or is it a custom SQL written within your company???

There are several ways to accomplish this:

1. You can use the "Form Settings" icon (looks like a piece of paper with a wheel on it) to set up a Sales Order to display the "Gross Profit Base Price" to show individual cost and "Gross Profit Total Base Price" to show the line's total cost (see attached).

2. You can wrtie an SQL with those fields from the Sales Order.

Let us know the answers to the questions above and we might be able to help you better.

Take care - Zal


Share
10 |10000 characters needed characters left characters exceeded
Zal Parchem
Oct 11, 2017 at 11:00 AM
0

Siva - coming back at you just in case you need an SQL. Try this as your basis. It shows the data fields you might be interested in. Again, this SQL is just an outline for you so you can modify it as you need:

SELECT

T0.[DocEntry], T0.[LineNum], T0.[ItemCode],

T1.[LastPurPrc] AS 'Last Purchase Price for Item', T0.[GrossBuyPr] AS 'Cost on Sales Order Line',

T0.[Price] AS 'Price On Sales Order After Discounts', T0.[PriceBefDi] AS 'Price on Sales Order Before Discounts'

FROM RDR1 T0

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

ORDER BY T0.[DocEntry], T0.[LineNum]

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Zal,

Thanks for the query. but i already tried this query . This query is picking the purchase price which we bought in past for other orders. Now my requirement is am pulling the Open sales order with the "selling price" (From RDR1 table), but users would like to see "purchase price" (From POR1 table) along with this. I have written separate queries for "Open sales order" and "Open purchase order". i am unable to incorporate both the queries, since there is no common field between the RDR1 & POR1 tables.

Open Sales Order Query:

SELECT T0.DOCNUM, T0.DocDate, T1.ItemCode, T1.Dscription, T1.QUANTITY, T1.U_OB_ETA, T1.OpenQty, T1.LineTotal AS [Selling Amount], T1.TOTALFRGN, T1.U_OB_Vendor FROM ORDR T0 INNER JOIN rdr1 T1 ON T1.DocEntry = T0.DocEntry WHERE T0.DocType = 'I' AND T0.DocStatus = 'O' AND T1.LineStatus = 'O'

Open Purchase Order:

SELECT T0.[DocNum] AS 'PO No', T0.[CardCode] AS 'VendorCode', T0.[CardName] AS 'VendorName', T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity] AS 'POQuantity', T1.[OpenQty] AS 'OpenQuantity', t1.[Currency], T1.[Price], T1.[LineTotal], T1.[TotalFrgn], t1.[rate], t1.[BaseRef] AS 'SO NO' FROM Opor T0 INNER JOIN por1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.LINESTATUS = 'O' AND T0.DOCSTATUS = 'O'

I am trying to pull the purchasing price"Line total" from POR1 table in to "Open sales Order query". i am unable to incorporate both the queries, since there is no common field between the RDR1 & POR1 tables.

Can any one help me on this.

Regards,

Siva

0
Zal Parchem
Oct 12, 2017 at 02:18 PM
0

Here are what the results look like:


2017-10-12-0835.png (349.4 kB)
Share
10 |10000 characters needed characters left characters exceeded