Crystal Reports, SAP B1 - connect OINV, OPCH (A/R Invoice, A/P Invoice)

[michaelhouse]

What I need to do is to connect the two SAP B1 v9.0 tables listed above (A/P Invoice OINV, and A/R Invoice OPCH) in a Crystal report WITHOUT SQL.  How do you connect these tables?  Purpose of report is for sales commissions.  Existing customer report combines SAP B1 data from these tables but is done manually in Excel.  Attempting to create report in Crystal for automation purposes.  Will also include INV1 and PCH1 for detailed line item marketing document information.  Thanks for any assistance.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Not possible to link directly both invoices in SQL.

Since base and target document are different for both invoices.

Thanks & Regards,

Nagarajan

[michaelhouse · Accepted solution]

But an A/R Invoice (Sales Invoice, revenue) would impact the creation of an A/P Invoice (Purchase Invoice, cost) in order to fulfill its requirements.  There has to be some connection, even if it isn't direct.

[julie_jamieson2 · Accepted solution]

Hi Michael,

In most business there is not a direct relationship between sales & purchasing as the items sold are simply stocked for sale, not a one to one purchase for a sale.

If you are just looking for the cost of the item sold, there are other tables in SAP that store this detail.

Perhaps if you explained what you are trying to achieve with your report we may be able to be more helpful?

[michaelhouse · Accepted solution]

Julie,

Thanks for responding.  At this point, I don't know specifically what the customer wants but I should have some clarification next week.  I know the purpose of the report is to track sales and costs per project by salesperson to compute/determine commissions.  Their sole guide for me is a spreadsheet that includes salesperson, project information, business partner information, and sales of items (per A/R invoice data).  Some rows also include the cost of the item (per A/P invoice data).  There is a field in the INV1 table called Stock Price that I thought would show me the cost of the item sold, but so far the data in that field is all zeros.  I'm working with a snapshot of their database in a test environment.  I was hoping to simply map A/P to A/R directly or indirectly so as to include that data in the report.  At a later point, when I had their exact specification, I was planning on filtering and summarizing what they needed.  If there is a way to circumvent this using other tables, I'm open to any and all suggestions.  Thanks.

[julie_jamieson2 · Accepted solution]

Try using the OINM / OIVL ( depending on database version) table.

This should show the cost of the item as used as time of sale and can be linked to the AR Invoice lines (INV1)  table on docentry. This will also depend on whether the salesperson recorded at the line level of the invoice is the same as that at the header level as these can be different

I am responding out of hours so don't have a database I can look at to give you field names and proper joins, sorry

[michaelhouse · Accepted solution]

Julie,

Thanks, but it looks like the OIVL table is not used in their database.  All fields are empty.

[julie_jamieson2 · Accepted solution]

So the company does not hold stock in a warehouse? What version & patch is the database at?

If your invoice documents are type 'I' (item based), you could try the field in OINV GrosProfit, which should record a value based on selections made in OADM.CostPrcList - for companies not running stock, this would usually be set as last Purchase Price - i.e. the document GP is calculated based on the last purchased value for the item.

Otherwise, I think you may be trying to do the impossible

[michaelhouse · Accepted solution]

The customer clarified the report requirements yesterday right after I had finally figured out how to pull in the requested tables (OINV, OPCH, OPOR) by linking base and target data fields.  Their "cost" information was meant to be pulled from the A/P invoice, so I didn't need to pull in any additional tables.

Thank you, Julie, for your attention and help.

*************************************************************************************************************

The answer to my question lied in finding the correct combination of base and target fields to link to each other to bring in the necessary tables.  Since I do NOT know SQL and don't use it in Crystal report development, here is how I linked the tables beginning with OPRJ and INV1:

[Former Member · Accepted solution]

You might want to consider base object type when joining on base ref so you know that you are comparing like for like reference numbers.

[michaelhouse · Accepted solution]

I see SQL as only useful for report performance enhancement.  It is too restricting in report development because you have to know the database and fields beforehand.  During the development phase, I want ALL the data from multiple tables so I can see what I'm working with and program around any obstacles.  In far less complex databases, I can see SQL being useful to the whole reporting process, but SAP B1 doesn't lend itself to easy mastery.  1,700+ tables requires considerable exploration, so using Crystal without SQL is the best and only option for me.

[michaelhouse · Accepted solution]

Since the picture doesn't show what is actually linked to what, here is a written description of the links I used between tables (with 2 alternates in case I'm wrong):

OPRJ.PrjCode left join INV1.Project

INV1.BaseEntry left join RDR1.DocEntry

INV1.BaseLine left join RDR1.LineNumber

INV1.DocEntry left join OINV.DocEntry

INV1.ObjType left join OINV.ObjType

INV1.ItemCode inner join OITM.ItemCode

OINV.CardCode inner join OCRD.CardCode

OINV.SlpCode left join OSLP.SlpCode

OITM.ItmsGrpCod left join OITB.ItmsGrpCod

RDR1.DocEntry left join ORDR.DocEntry

RDR1.DocEntry left join DLN1.BaseEntry

RDR1.LineNum left join DLN1.LineNum (s/b BaseLine?)

RDR1.DocEntry left joing POR1.BaseEntry

RDR1.LineNum left join POR1.LineNum (s/b BaseLine?)

RDR1.ObjType left join ORDR.ObjType

POR1.DocEntry left join OPOR.DocEntry

POR1.ObjType left join OPOR.ObjType

POR1.DocEntry left join PCH1.BaseEntry

POR1.LineNum left join PCH1.BaseLine

PCH1.DocEntry left join OPCH.DocEntry

PCH1.ObjType left join OPCH.ObjType

DLN1.DocEntry left join ODLN.DocEntry

DLN1.ObjType left join ODLN.ObjType

[julie_jamieson2 · Accepted solution]

HI Michael,

The links between RDR1 & DLN1 should be using base line from target doc, RDR being sales order  is the base of a delivery, but only if the delivery is drawn from the order - a delivery can also be drawn from a reserve invoice. You should also be checking that the base object type is the same (i.e. an order object type is 17 from memory, an invoice is 13 etc) to be sure that you are drawing the detail from the right base doc,

There is only a join between RDR1 and POR1 if the PO has been created from the sales Order - the target is not recorded on the sales order, but the base is recorded on the PO - again the BaseType, BaseRef and BaseLine need to be checked.

It is possible for one base doc to be drawn to multliple target docs too

[michaelhouse · Accepted solution]

Julie,

Thanks for the followup.  I added a link between RDR1.LineNum and DLN1.BaseLine as you indicated.  Reading your updated comment, I need some clarification please.  Can you explain using field names what you mean by "the target is not recorded on the sales order, but the base is recorded on the PO"?  I think my report is working fine because the customer really does create purchase orders from sales orders, not in every case, but as a general rule.

FYI, it appears that using Crystal's record selection formula will not work to limit the content to specific object types.  I think that restriction will have to be formulaic and imbedded in the detail of the report.  For example, specific report values would have to be the result of a formula along the lines of "if object type = x, then field value, else 0".  That would allow me to sum up only the appropriate records.

[Former Member · Accepted solution]

I have had moments when I have felt your frustration Michael but with more exposure I am sure you will become a little more comfortable with the product.

There is a database manual which summarizes the database table and fields, how much help this would be to you is debatable since you have a hands off approach to SQL but it is well worth having a look at.

Also if you are a regular SAP user you will have more confidence in what is happening in the database.

Best of luck with everything you are doing.

[hibaai]

Hi everyone,

How about joint ODLN - DLN1 - ORDN - RDN1 ?

Want to join these tables to calculate diffrence between Qte delivred and Qte returned for some items.

Please help !

[Former Member]

Crystal report has an option that you can connect tables without sql.

just basically determine the tables that you would like to be included. for your case it would be OPCH, OINV, PCH1 and INV1.

When you need to link these tables best option to use for key is the item code that is assuming they are item type transactions.

you can refer to this link:

regards,

khristine

[julie_jamieson2]

What are you trying to join the two tables on Michael? What are the common factore between the two tables?

[michaelhouse]

Julie,

The tables have pretty much the same fields, they are part of what are known as marketing documents in SAP B1.  Unfortunately, they have relationships that remain puzzling to me, and, apparently many other people.  Some records from OINV may not have any relationship to a record in OPCH, but for those that do, I don't have any idea how to map them to each other.

Basically, I need an SAP B1 expert here because I already have 19 years of experience with Crystal Reports.  It's the database that's tripping me up, not the reporting software.  Thanks.

(NOTE: I just realized my initial post had the A/P and A/R reversed. OINV is A/R Invoice and OPCH is A/P Invoice.)

[michaelhouse]

OK, as much as I don't want to go there... how would you do it with SQL?

I honestly don't see how SQL adds anything to report development.  It might be great in report performance, but it does nothing to help one explore the data to be included in a Crystal Report.

[Former Member]

I would invest some time to learn at least a little bit of SQL if I was you.

The most obvious benefit to report writing imo is that you can make commands that only pull back the data that you want at source.  This will benefit performance and will give you an easier ride with some of the more complicated reports.

Looking at the relationship map that you have wrote you must know what join types are it wouldn't be such a great task to learn to apply these principles in SQL.