SAP Business One SQL Level: Tables and Fields
When you are designing reports in Crystal or other tool or when you are exporting documents from Business One you should be familiar with SQL tables and fields. It is described in good details including relations in Software Development kit.
You can also find names of these objects directly in user interface if you switch on ‘Show System Information’ flag in View menu. We would like to walk you through and make few exercises. This paper is written on introduction level and is not intended to be complete reference. If you are programmer then next step is probably read SDK documentation. After paragraphs we will try to answer FAQ:
1. View System Information. Login client application and in menu mark ‘view system information’. At this point open one of the forms. Let’s try Purchase Quotation. Place cursor over Vendor field (where Business Partner code supposed to go) and read info in lower left section of the application window: tables is OPQT and field name is CardCode. As exercise try to find information about AR Invoice. If you were able to find Header is OINV and item lines INV1 then you are probably on the right trail
2. Details in SDK. Open Help Center (it is in HTML format) and go to Contents -> Customization Tools. Let’s now find detail info about objects from previous paragraph. By whatever reason all AR and AP documents are classified as ‘Marketing Documents’ in English interface. It might be a little bit confusing. Expand ‘Marketing Documents’. And here you see AR Invoice. Expand it and find all related files: INV1 through INV16. At this point we believe that for most of us the rest is clear and we are good to begin working on integration or reporting. Let us give you several advises
3. Crystal Reports and SQL base. In CR wizard everyone can create report in a matter of minutes, right?.. Here important is to understand the level of complexity. If all you need to do is pull your customers then wizard will do the job in a matter of minutes. However if you plan to join several tables and possible user defined ones as well then we would recommend different strategy. Create SQL View or even better stored procedure and test it prior to opening Designer tool. We have seen numerous problems when developer is in learning curve and he or she is relying on wizard only: incorrect summaries, duplicate rows, etc. CR is for ‘presentation’ and SQL is for ‘data selection’
OK we are ready to answer popular questions:
Q. In our case we need to know that structure in order to create several stored procedures to feed ongoing data from our ecommerce portal shopping carts to SAP B1. Does it make sense?
Q. We agree that for Crystal it is good idea to create View or Procedure. Our concern is about version upgrade. Does it wipe out all ‘alien’ objects from company database?
Q. We would like to export shopping carts from our Linux/PHP based custom ecommerce portal and send them to B1 as AR Invoices. Do you have any technical recommendations?
Please call us 1-866-304-3265, 1-269-605-4904, firstname.lastname@example.org. We have local presence in Chicagoland, Southern California, Atlanta Georgia, Southwest Michigan, Houston and Dallas areas of Texas. We serve customers USA and Canada nationwide and internationally via web sessions and phone conferences (Skype is welcomed). We are working on Brazilian ERP consulting market in Sao Paulo since 2004
Source: Free Articles from ArticlesFactory.com
ABOUT THE AUTHOR
Andrew Karasev is SAP BO consultant and Great Plains Certified Master, MVP, email@example.com 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project http://www.efaru.com and founder of Alba Spectrum information space