Using 3rd Party Tools to Access Data via ODBC

USING 3RD PARTY TOOLS TO ACCESS DATA VIA ODBC

Setting up an ODBC (Open Database Connectivity) connection to gain access to your Dynamic3i data is very simple. The section "ODBC - Data Sources" below will guide you through the required steps involved.

Table changes that may impact using DYNAMIC3i database as a data warehouse for external ODBC queries:

Version 2.6 to 3.x – Stock Master table (STKMST) changes and new Actual Costing table (ACTCOST).

To facilitate the ability of Dynamics 3i to offer and operate under an actual costing methodology, certain items that were associated with the stock master table were re assigned to a new Actual Costing table (ACTCOST). This new table is warehouse dependent in the same manner as the Stock Master table with warehouse and product being the primary and mandatory access key

Stock Master table items that were removed:

QTY_ON_HAND
QTY_AT_PHYSICAL
LAST_COST
AVE_COST
STANDARD_COST
COST_PRICE_PER
MATERIAL_COST
LABOUR_COST
SET_UP_COST
VAR_OVERHEAD_COST
FIX_OVERHEAD_COST
DUTY_COST
LAST_ISSUE_DATE

Fields that were directly moved to new Actual Cost table (ACTCOST) were:

QTY_ON_HAND
QTY_AT_PHYSICAL
AVE_COST
COST_PRICE_PER

The follow fields were removed as they were redundant in their definition on the main inventory costing table (PRCMST):

MATERAIL _COST
LABOUR_COST
SET_UP_COST
VAR_OVERHEAD_COST
FIX_OVERHEAD_COST
DUTY_COST
STANDARD_COST (as it is calculated from above items)

POHMST – order_amount PORMST – received_amount

The order amount is no longer part of the POHMST (header table)
it is directly calculated from the PO details for display:

Example:

select sum(nvl(fed_tax_amount,0)),
sum(nvl(prov_tax_amount,0)),
sum(round(nvl(qty_ordered,0) * nvl(cost_amount,0) / nvl(cost_price_per,1), 2))
from podmst
where purchase_no = :master.purchase_no;

The received amount is no longer part of the POHMST (header table) it is directly calculated from the PORMST details. 'Purchases by Vendor Inquiry - po2400' utilizes this calculation for display purposes only :

Example:

select sum(nvl(d.qty_ordered,0) *nvl(d.cost_amount,0) /nvl(d.cost_price_per,1)),
sum(nvl(r.qty_received,0)*nvl(r.cost_amount,0)/r.cost_price_per),
max(d.job_no), max(a.order_no)
from podmst d, pormst r, oehmst a
where d.purchase_no = :po240b.purchase_no
and r.purchase_no(+) = d.purchase_no
and r.line_no(+) = d.line_no
and a.order_no(+) = d.job_no;

Note. Currently there are three tables within Dynamic3i that utilize what is known as Advance Data Type fields (ADT) which are not supported by some third part ODBC connection software. In order to access information with these data types database views must first be defined. Since views are treated as tables in the database, the information can be accessed in the same manner and element selection process as if the view were an actual table.

Current tables in Dynamic3i with ADT type fields:

PRCMST - For new costs and current cost elements
RMAHMST - For address information
SADETIAL - For shipment dates

Within Dynamic3i version 3.2 and higher the following views are pre-defined in order to access the ADT type information from the main Dynamic3i costing table (PRCMST), Return Authorization table (RMAHMST) and the Sales Analysis Detail Table (SADETAIL)

Pre-Defined Views:

PRCMSTVIEW
PRCHISTVIEW
RMAHMSTVIEW
SADETAILVIEW

The elements of these views can be seen after an ODBC - Data source connection is established.

Aside from directly retrivieing data elements on the above views there are some useful built in routines in DYNAMIC 3i

They are as follows:

In order to retrive a current customer A/R Outstanding amount smiply call 'arbal' passing in the customer and invoice number. This function is part of the internal library routined 'sdcmisc1'.

Example:

select sdcmisc1.arbal('ROB0001', 18375) from dual

In order to get an outstanding Sales Order invoice amount (by order) use the following:

select sdcoe.oe_order_invoice_amount (order_no, back_order_ctr, discount_percent, cash_discount_percent,
freight_amount, cartage, insurance, legalization, handling, other_charges,
freight_fed_tax_percent, freight_prov_tax_percent, get_com_value('oe_truncate_tax'))
from oehmst

(restriction by order number can also be used.)

ODBC - Data Sources

Data Sources are defined and maintained in the “Networking and Internet Connections” - ODBC Data Sources.

To Use Microsoft Access see: Using Microsoft Access and ODBC to connect to Dynamic3i

To Use Microsoft Excel see: Using Microsoft Excel and ODBC to connect to Dynamic3i

Using Microsoft Excel and ODBC to connect to Dynamic3i

The following process uses Microsoft Excel with Microsoft Query option and assumes that you have these installed and configured without an ODBC connection to DYNAMIC 3i. Steps 1 through 3 are only required to be done once, to establish and reference the connection on your local PC.

Step 1. Choose Connection Type

Open MS Excel and using the Toolbar select the ‘Data’, ‘Get external data’ tab.

This will result in a screen asking you to select a data source for your query. It is here that you will establish the connection to the Dynamic3i database and reference it with any name that you choose for future use.

Step 2. Define Connection

1-2-3 In the following window you will have to establish the Dynamic3i Database as valid database source to connect to. This is done by selecting ‘<New Data Source>’ and supplying the required login information.

The name you choose here will be the reference name that will appear in the Database tab above. As Dynamic3i is an Oracle Database the driver chosen MUST be the ‘Microsoft ODBC for Oracle’. If this is not available in the list of available entries you must see your database administrator. You must click on the ‘Connect’ and supply the driver with your login ID (username/password) and network server location of the DYNAMIC 3i database. Again, this information can be setup and obtained from your database administrator.

Step 3. Connection!

After entering you username/password and server/database location your system will use the information to establish a connection with the database. If successful you will be able to choose a default table for you data source. To keep things simple just click ‘OK’ and you will see the source name you chose in the ‘Databases’ tab. Proceed to the ‘Using The ODBC Connection’ of this document.

Using the ODBC Connection

Now that a Dynamic3i database connection is established you can use this data source to select information from any database table that you have access to. When you choose the ‘Get External Data’ again and then choose the datasource just set up you will be asked for your username/password and then the system will connect and present you with a list of tables that you have access to. This access is defined the rights granted to your username/password. If the table you want to query data from is not listed then you must see your database administrator and ask for ‘read access’ to the particular table in question.

At this point you are using the query wizard to select table and/or columns of data to be returned to your spread sheet.

Further filtering of data can be done by the wizard. Simply choose the required parameters presented and click ‘Next’.

Upon completion of the wizard you will be asked to return data to Microsoft Excel view or create an OLAP cube. You will also have the option to ‘Save Query’. This is useful if you plan to do the same or similar queries again as they can simply be edited.

By default queries are stored in your ‘Application Data’ directory under you user ‘Documents and Settings’ directory.

When ‘Finished’ is clicked you will be asked where in the Excel spread sheet to return/populate cells with the data from the database. Upon clicking ‘OK’ you now have Dyanmci3i data within you spread sheet!

If you wish to edit your query further you can simply choose ‘Edit query’ under ‘Data’, ‘Get External Data’. You connection and query will be returned for editing.

Using Microsoft Query to create more complex data queries

If the ‘Use the Query Wizard to create/edit queries’ is selected then Microsoft Excel will invoke the simply query wizard to edit/create queries. If de-selected then the Microsoft Query) MS – Query is invoked and used. MS – Query allows you to create more complex queries and from multiple database tables utilizing extensive filtering and use of the SQL procedural query language. As external documentation on either MS -Excel or MS-Query is available their detailed use will not be covered here. Suffice to say that once an ODBC connection is established any third party tool can be used for data extraction and manipulation based upon the rights granted to the connection by your database administrator.

Using Microsoft Access and ODBC to connect to Dynamic3i

The same ODBC connection can be used and/or set-up through Microsoft Access.

Start up Access and then use/search help on “pass-through query”:

The help documents labeled “Send commands to an SQL database using a pass-through query” maps out all the required set-up and use.

Here is a quick summary:

Using Access …

Create and name a New Database. Select queries and Create query in design view:

When you get the ‘Show Table’, simply “Close”.

On the remaining “Query1” position mouse on the title bar and right click to get the properties menu then select ‘SQL Specific’ then ‘Pass-Through’.

Query1 will change to a pass-through query. Then do the same on this title bar as before, right click to get the properties of this query.

Click on the “ODBC Connect Str” and you will see a small icon appear on the right …

Click on this and you will be asked for the Data Source. These are all of the standard data sources as set-up in the “Networking and Internet Connections”, ODBC Data Sources.

You can set-up a new one by selecting New and walking through the data sources wizards.

It is the connect string that will establish and connect to the database via ODBC connectivity. After this string is created/defined you can enter the query in std. SQL format. Example: “Select * from whmst” and then ‘Run’ the query. Data will be returned.

Saving this query will make it available later on within the newly created Access database.