on 02-19-2014 6:45 PM
I'm new to SAP B1 and I have been tasked with developing Crystal Reports from it. My knowledge of SQL is rudimentary but I know Crystal quite well.
I can't seem to get an entire "order history" in the same area of a Crystal Report. For example, I want to see a customer's sales quotations, sales orders, deliveries, A/R invoices and any returns as they relate to each other in the same fashion (not necessarily graphically) as SAP B1's relationship map feature. If our customer wants a report that shows their customer's ordering history or habits, being able to see the development of their sales quotes through to deliveries would be beneficial. How many quotes failed to turn into sales orders? What are they consistently returning? Looking at each document type scattered throughout the report makes such analysis impossible. I want the same chain of sales events in the same area of the report.
In Crystal, I've brought together the header and line tables in a View. The object type defines the document type. What I retrieve is a list with no clear picture of which sales quotation led to which sales order, etc. The way SAP B1 uses DocNum, DocEntry, BaseEntry, TrgetEntry, etc. is too confusing for words. Honestly, why make this so difficult! There must be some data field or set of data fields that connects each of these documents together for the same chain of sales-related events. I've tried a subreport but that didn't yield what I expected unless I begin to concatenate fields together to create a unique identifier of some kind.
If anyone else has solved this problem, please advise. Thanks.
I need the same query to show the result like relationship map. Plz share if you (House) or anyone get the solution.
Regards,
Awais
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I abandoned this issue a while back. After more experience working with SAP I'm finding that this would be extremely difficult if not impossible to create. Since documents can turn into or be copied from other documents, it appears this effort would be rather futile. Our clients haven't asked for anything related to this so I stopped trying to accomplish it. Sorry I couldn't be more helpful to you.
There are a couple keys to get this to work.
1. There are 7 types of sales docs; quote, order, invoice, etc., so you need to map out all the possible paths you can have. (example: quote-order-Inv is one possible path) I used 5 purchasing docs (no quotes) and there are 15 paths that can occur, but only 9 that are LIKELY to occur.
2. You build one query for each of the last possible documents in the paths and trace backwards. For Purchasing, a complete or partial path would end with one of 5 docs: AP inv, AP Cred, Receipt, Return or Purchase Order. That meant I had to build 5 small queries and union all. Since I didn't use quotes, my longest possible path was 4 documents: ( PO> Receipt> Return> Credit.)
Here is the query for all paths that end with AP Credits. It was the longest at 4 levels deep.
- This will return DocEntry, DocLine, Doc type as Columns A, B, C, D.
- If you join the Doc Header tables, you can add the doc numbers or other details.
- Notice the same tables are joined more then once when there is more than one path including them. Just make sure to keep your table alias numbers strait. "P31" is Table alias "P" level 3, first occurrence.
- Repeat this for each possible ending document and the paths that can lead up to it.
SELECT --*********** RPC1 AP Credit
--LEVEL 1
DD.ItemCode AS 'AItemCode' , DD.Dscription AS 'ADescription' , DH.DocNum AS 'DocNumber'
, DD.DocEntry AS 'ADocEntry' , DD.LineNum AS 'ADocLine' , DD.ObjType AS 'Aobjtype' , DD.DocDate AS 'ADocDate'
--LEVEL 2
, ISNULL(DD.BaseEntry,0) AS 'BDocEntry' , ISNULL(DD.BaseLine,0) AS 'BDocLine' , ISNULL(DD.BaseType,0) AS 'BObjType'
--LEVEL 3 Coalesce any possible options if there are more than one
, COALESCE(P31.DocEntry, G31.DocEntry, G32.DocEntry, 0 ) AS 'CDocEntry'
, COALESCE(P31.LineNum, G31.LineNum, G32.LineNum , 0 ) AS 'CDocLine'
, COALESCE(P31.ObjType, G31.ObjType, G32.ObjType , 0 ) AS 'Cobjtype'
--LEVEL 4 Coalesce any possible options if there are more than one
, COALESCE(P41.DocEntry, P42.DocEntry, 0 ) AS 'DDocEntry'
, COALESCE(P41.LineNum, P42.LineNum, 0 ) AS 'DDocLine'
, COALESCE(P41.ObjType, P42.ObjType, 0 ) AS 'Dobjtype'
FROM RPC1 DD
LEFT OUTER JOIN ORPC DH ON DH.DocEntry = DD.DocEntry
LEFT OUTER JOIN PCH1 I2 ON I2.DocEntry = DD.BaseEntry AND I2.LineNum = DD.BaseLine
LEFT OUTER JOIN POR1 P31 ON P31.DocEntry = I2.BaseEntry AND P31.LineNum = I2.BaseLine
LEFT OUTER JOIN PDN1 G31 ON G31.DocEntry = I2.BaseEntry AND G31.LineNum = I2.BaseLine
LEFT OUTER JOIN POR1 P41 ON P41.DocEntry = G31.BaseEntry AND P41.LineNum = G31.BaseLine
LEFT OUTER JOIN RPD1 R2 ON R2.DocEntry = DD.BaseEntry AND R2.LineNum = DD.BaseLine
LEFT OUTER JOIN PDN1 G32 ON G32.DocEntry = R2.BaseEntry AND G32.LineNum = R2.BaseLine
LEFT OUTER JOIN POR1 P42 ON P42.DocEntry = G32.BaseEntry AND P42.LineNum = G32.BaseLine
LEFT OUTER JOIN OITM IM ON IM.ItemCode = DD.ItemCode --Item Desc Need to Filter by Inventory Item
WHERE DD.TrgetEntry IS NULL --Eliminates duplication when start doc is already in another path
AND IM.PrchseItem = 'Y' AND IM.InvntItem = 'Y'
The question is a little old, but since I just did a similar report with the purchasing relationships, I'll provide a few thoughts.
The only way I could connect them was through the BaseEntry and BaseType. If you start at the invoice, BaseType tells you what document the invoice was based on. For example, type 15 is a delivery note, so that doc's details are in ODLN. So, BaseEntry in OINV, (invoices) would equal DocEntry in ODLN, (delivery notes.) From that document, just repeat the process to get the next one. You have to do this on a doc line by doc line basis, since that's where there is a 1 to 1 relationship. You can't just match headers
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
1.To understand relationship between tables, please refer below document:
2. Another good source to learn SQL queries and report In SAP B1, you can refer book, Mastering SQL Queries for SAP business one Author Gordon Du.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.